1 # 2002 March 6

     2 #

     3 # The author disclaims copyright to this source code.  In place of

     4 # a legal notice, here is a blessing:

     5 #

     6 #    May you do good and not evil.

     7 #    May you find forgiveness for yourself and forgive others.

     8 #    May you share freely, never taking more than you give.

     9 #

    10 #***********************************************************************

    11 # This file implements regression tests for SQLite library.

    12 #

    13 # This file implements tests for the PRAGMA command.

    14 #

    15 # $Id: pragma.test,v 1.66 2008/09/02 00:52:52 drh Exp $

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 # Test organization:

    21 #

    22 # pragma-1.*: Test cache_size, default_cache_size and synchronous on main db.

    23 # pragma-2.*: Test synchronous on attached db.

    24 # pragma-3.*: Test detection of table/index inconsistency by integrity_check.

    25 # pragma-4.*: Test cache_size and default_cache_size on attached db.

    26 # pragma-5.*: Test that pragma synchronous may not be used inside of a

    27 #             transaction.

    28 # pragma-6.*: Test schema-query pragmas.

    29 # pragma-7.*: Miscellaneous tests.

    30 # pragma-8.*: Test user_version and schema_version pragmas.

    31 # pragma-9.*: Test temp_store and temp_store_directory.

    32 # pragma-10.*: Test the count_changes pragma in the presence of triggers.

    33 # pragma-11.*: Test the collation_list pragma.

    34 # pragma-14.*: Test the page_count pragma.

    35 # pragma-15.*: Test that the value set using the cache_size pragma is not

    36 #              reset when the schema is reloaded.

    37 #

    38 

    39 ifcapable !pragma {

    40   finish_test

    41   return

    42 }

    43 

    44 # Delete the preexisting database to avoid the special setup

    45 # that the "all.test" script does.

    46 #

    47 db close

    48 file delete test.db test.db-journal

    49 file delete test3.db test3.db-journal

    50 sqlite3 db test.db; set DB [sqlite3_connection_pointer db]

    51 

    52 

    53 ifcapable pager_pragmas {

    54 set DFLT_CACHE_SZ [db one {PRAGMA default_cache_size}]

    55 set TEMP_CACHE_SZ [db one {PRAGMA temp.default_cache_size}]

    56 do_test pragma-1.1 {

    57   execsql {

    58     PRAGMA cache_size;

    59     PRAGMA default_cache_size;

    60     PRAGMA synchronous;

    61   }

    62 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]

    63 do_test pragma-1.2 {

    64   execsql {

    65     PRAGMA synchronous=OFF;

    66     PRAGMA cache_size=1234;

    67     PRAGMA cache_size;

    68     PRAGMA default_cache_size;

    69     PRAGMA synchronous;

    70   }

    71 } [list 1234 $DFLT_CACHE_SZ 0]

    72 do_test pragma-1.3 {

    73   db close

    74   sqlite3 db test.db

    75   execsql {

    76     PRAGMA cache_size;

    77     PRAGMA default_cache_size;

    78     PRAGMA synchronous;

    79   }

    80 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]

    81 do_test pragma-1.4 {

    82   execsql {

    83     PRAGMA synchronous=OFF;

    84     PRAGMA cache_size;

    85     PRAGMA default_cache_size;

    86     PRAGMA synchronous;

    87   }

    88 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 0]

    89 do_test pragma-1.5 {

    90   execsql {

    91     PRAGMA cache_size=4321;

    92     PRAGMA cache_size;

    93     PRAGMA default_cache_size;

    94     PRAGMA synchronous;

    95   }

    96 } [list 4321 $DFLT_CACHE_SZ 0]

    97 do_test pragma-1.6 {

    98   execsql {

    99     PRAGMA synchronous=ON;

   100     PRAGMA cache_size;

   101     PRAGMA default_cache_size;

   102     PRAGMA synchronous;

   103   }

   104 } [list 4321 $DFLT_CACHE_SZ 1]

   105 do_test pragma-1.7 {

   106   db close

   107   sqlite3 db test.db

   108   execsql {

   109     PRAGMA cache_size;

   110     PRAGMA default_cache_size;

   111     PRAGMA synchronous;

   112   }

   113 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]

   114 do_test pragma-1.8 {

   115   execsql {

   116     PRAGMA default_cache_size=123;

   117     PRAGMA cache_size;

   118     PRAGMA default_cache_size;

   119     PRAGMA synchronous;

   120   }

   121 } {123 123 2}

   122 do_test pragma-1.9.1 {

   123   db close

   124   sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db]

   125   execsql {

   126     PRAGMA cache_size;

   127     PRAGMA default_cache_size;

   128     PRAGMA synchronous;

   129   }

   130 } {123 123 2}

   131 ifcapable vacuum {

   132   do_test pragma-1.9.2 {

   133     execsql {

   134       VACUUM;

   135       PRAGMA cache_size;

   136       PRAGMA default_cache_size;

   137       PRAGMA synchronous;

   138     }

   139   } {123 123 2}

   140 }

   141 do_test pragma-1.10 {

   142   execsql {

   143     PRAGMA synchronous=NORMAL;

   144     PRAGMA cache_size;

   145     PRAGMA default_cache_size;

   146     PRAGMA synchronous;

   147   }

   148 } {123 123 1}

   149 do_test pragma-1.11 {

   150   execsql {

   151     PRAGMA synchronous=FULL;

   152     PRAGMA cache_size;

   153     PRAGMA default_cache_size;

   154     PRAGMA synchronous;

   155   }

   156 } {123 123 2}

   157 do_test pragma-1.12 {

   158   db close

   159   sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db]

   160   execsql {

   161     PRAGMA cache_size;

   162     PRAGMA default_cache_size;

   163     PRAGMA synchronous;

   164   }

   165 } {123 123 2}

   166 

   167 # Make sure the pragma handler understands numeric values in addition

   168 # to keywords like "off" and "full".

   169 #

   170 do_test pragma-1.13 {

   171   execsql {

   172     PRAGMA synchronous=0;

   173     PRAGMA synchronous;

   174   }

   175 } {0}

   176 do_test pragma-1.14 {

   177   execsql {

   178     PRAGMA synchronous=2;

   179     PRAGMA synchronous;

   180   }

   181 } {2}

   182 } ;# ifcapable pager_pragmas

   183 

   184 # Test turning "flag" pragmas on and off.

   185 #

   186 ifcapable debug {

   187   # Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG

   188   #

   189   do_test pragma-1.15 {

   190     execsql {

   191       PRAGMA vdbe_listing=YES;

   192       PRAGMA vdbe_listing;

   193     }

   194   } {1}

   195   do_test pragma-1.16 {

   196     execsql {

   197       PRAGMA vdbe_listing=NO;

   198       PRAGMA vdbe_listing;

   199     }

   200   } {0}

   201 }

   202 

   203 do_test pragma-1.17 {

   204   execsql {

   205     PRAGMA parser_trace=ON;

   206     PRAGMA parser_trace=OFF;

   207   }

   208 } {}

   209 do_test pragma-1.18 {

   210   execsql {

   211     PRAGMA bogus = -1234;  -- Parsing of negative values

   212   }

   213 } {}

   214 

   215 # Test modifying the safety_level of an attached database.

   216 ifcapable pager_pragmas&&attach {

   217   do_test pragma-2.1 {

   218     file delete -force test2.db

   219     file delete -force test2.db-journal

   220     execsql {

   221       ATTACH 'test2.db' AS aux;

   222     } 

   223   } {}

   224   do_test pragma-2.2 {

   225     execsql {

   226       pragma aux.synchronous;

   227     } 

   228   } {2}

   229   do_test pragma-2.3 {

   230     execsql {

   231       pragma aux.synchronous = OFF;

   232       pragma aux.synchronous;

   233       pragma synchronous;

   234     } 

   235   } {0 2}

   236   do_test pragma-2.4 {

   237     execsql {

   238       pragma aux.synchronous = ON;

   239       pragma synchronous;

   240       pragma aux.synchronous;

   241     } 

   242   } {2 1}

   243 } ;# ifcapable pager_pragmas

   244 

   245 # Construct a corrupted index and make sure the integrity_check

   246 # pragma finds it.

   247 #

   248 # These tests won't work if the database is encrypted

   249 #

   250 do_test pragma-3.1 {

   251   db close

   252   file delete -force test.db test.db-journal

   253   sqlite3 db test.db

   254   execsql {

   255     PRAGMA auto_vacuum=OFF;

   256     BEGIN;

   257     CREATE TABLE t2(a,b,c);

   258     CREATE INDEX i2 ON t2(a);

   259     INSERT INTO t2 VALUES(11,2,3);

   260     INSERT INTO t2 VALUES(22,3,4);

   261     COMMIT;

   262     SELECT rowid, * from t2;

   263   }

   264 } {1 11 2 3 2 22 3 4}

   265 ifcapable attach {

   266   if {![sqlite3 -has-codec] && $sqlite_options(integrityck)} {

   267     do_test pragma-3.2 {

   268       db eval {SELECT rootpage FROM sqlite_master WHERE name='i2'} break

   269       set pgsz [db eval {PRAGMA page_size}]

   270       # overwrite the header on the rootpage of the index in order to

   271       # make the index appear to be empty.

   272       #

   273       set offset [expr {$pgsz*($rootpage-1)}]

   274       hexio_write test.db $offset 0a00000000040000000000

   275       db close

   276       sqlite3 db test.db

   277       execsql {PRAGMA integrity_check}

   278     } {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}

   279     do_test pragma-3.3 {

   280       execsql {PRAGMA integrity_check=1}

   281     } {{rowid 1 missing from index i2}}

   282     do_test pragma-3.4 {

   283       execsql {

   284         ATTACH DATABASE 'test.db' AS t2;

   285         PRAGMA integrity_check

   286       }

   287     } {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}

   288     do_test pragma-3.5 {

   289       execsql {

   290         PRAGMA integrity_check=4

   291       }

   292     } {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2}}

   293     do_test pragma-3.6 {

   294       execsql {

   295         PRAGMA integrity_check=xyz

   296       }

   297     } {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}

   298     do_test pragma-3.7 {

   299       execsql {

   300         PRAGMA integrity_check=0

   301       }

   302     } {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}

   303   

   304     # Add additional corruption by appending unused pages to the end of

   305     # the database file testerr.db

   306     #

   307     do_test pragma-3.8 {

   308       execsql {DETACH t2}

   309       file delete -force testerr.db testerr.db-journal

   310       set out [open testerr.db w]

   311       fconfigure $out -translation binary

   312       set in [open test.db r]

   313       fconfigure $in -translation binary

   314       puts -nonewline $out [read $in]

   315       seek $in 0

   316       puts -nonewline $out [read $in]

   317       close $in

   318       close $out

   319       execsql {REINDEX t2}

   320       execsql {PRAGMA integrity_check}

   321     } {ok}

   322     do_test pragma-3.8.1 {

   323       execsql {PRAGMA quick_check}

   324     } {ok}

   325     do_test pragma-3.9 {

   326       execsql {

   327         ATTACH 'testerr.db' AS t2;

   328         PRAGMA integrity_check

   329       }

   330     } {{*** in database t2 ***

   331 Page 4 is never used

   332 Page 5 is never used

   333 Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}

   334     do_test pragma-3.10 {

   335       execsql {

   336         PRAGMA integrity_check=1

   337       }

   338     } {{*** in database t2 ***

   339 Page 4 is never used}}

   340     do_test pragma-3.11 {

   341       execsql {

   342         PRAGMA integrity_check=5

   343       }

   344     } {{*** in database t2 ***

   345 Page 4 is never used

   346 Page 5 is never used

   347 Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2}}

   348     do_test pragma-3.12 {

   349       execsql {

   350         PRAGMA integrity_check=4

   351       }

   352     } {{*** in database t2 ***

   353 Page 4 is never used

   354 Page 5 is never used

   355 Page 6 is never used} {rowid 1 missing from index i2}}

   356     do_test pragma-3.13 {

   357       execsql {

   358         PRAGMA integrity_check=3

   359       }

   360     } {{*** in database t2 ***

   361 Page 4 is never used

   362 Page 5 is never used

   363 Page 6 is never used}}

   364     do_test pragma-3.14 {

   365       execsql {

   366         PRAGMA integrity_check(2)

   367       }

   368     } {{*** in database t2 ***

   369 Page 4 is never used

   370 Page 5 is never used}}

   371     do_test pragma-3.15 {

   372       execsql {

   373         ATTACH 'testerr.db' AS t3;

   374         PRAGMA integrity_check

   375       }

   376     } {{*** in database t2 ***

   377 Page 4 is never used

   378 Page 5 is never used

   379 Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 ***

   380 Page 4 is never used

   381 Page 5 is never used

   382 Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}

   383     do_test pragma-3.16 {

   384       execsql {

   385         PRAGMA integrity_check(10)

   386       }

   387     } {{*** in database t2 ***

   388 Page 4 is never used

   389 Page 5 is never used

   390 Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 ***

   391 Page 4 is never used

   392 Page 5 is never used

   393 Page 6 is never used} {rowid 1 missing from index i2}}

   394     do_test pragma-3.17 {

   395       execsql {

   396         PRAGMA integrity_check=8

   397       }

   398     } {{*** in database t2 ***

   399 Page 4 is never used

   400 Page 5 is never used

   401 Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 ***

   402 Page 4 is never used

   403 Page 5 is never used}}

   404     do_test pragma-3.18 {

   405       execsql {

   406         PRAGMA integrity_check=4

   407       }

   408     } {{*** in database t2 ***

   409 Page 4 is never used

   410 Page 5 is never used

   411 Page 6 is never used} {rowid 1 missing from index i2}}

   412   }

   413   do_test pragma-3.99 {

   414     catchsql {DETACH t3}

   415     catchsql {DETACH t2}

   416     file delete -force testerr.db testerr.db-journal

   417     catchsql {DROP INDEX i2}

   418   } {0 {}}

   419 }

   420 

   421 # Test modifying the cache_size of an attached database.

   422 ifcapable pager_pragmas&&attach {

   423 do_test pragma-4.1 {

   424   execsql {

   425     ATTACH 'test2.db' AS aux;

   426     pragma aux.cache_size;

   427     pragma aux.default_cache_size;

   428   } 

   429 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]

   430 do_test pragma-4.2 {

   431   execsql {

   432     pragma aux.cache_size = 50;

   433     pragma aux.cache_size;

   434     pragma aux.default_cache_size;

   435   } 

   436 } [list 50 $DFLT_CACHE_SZ]

   437 do_test pragma-4.3 {

   438   execsql {

   439     pragma aux.default_cache_size = 456;

   440     pragma aux.cache_size;

   441     pragma aux.default_cache_size;

   442   } 

   443 } {456 456}

   444 do_test pragma-4.4 {

   445   execsql {

   446     pragma cache_size;

   447     pragma default_cache_size;

   448   } 

   449 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]

   450 do_test pragma-4.5 {

   451   execsql {

   452     DETACH aux;

   453     ATTACH 'test3.db' AS aux;

   454     pragma aux.cache_size;

   455     pragma aux.default_cache_size;

   456   } 

   457 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]

   458 do_test pragma-4.6 {

   459   execsql {

   460     DETACH aux;

   461     ATTACH 'test2.db' AS aux;

   462     pragma aux.cache_size;

   463     pragma aux.default_cache_size;

   464   } 

   465 } {456 456}

   466 } ;# ifcapable pager_pragmas

   467 

   468 # Test that modifying the sync-level in the middle of a transaction is

   469 # disallowed.

   470 ifcapable pager_pragmas {

   471 do_test pragma-5.0 {

   472   execsql {

   473     pragma synchronous;

   474   } 

   475 } {2}

   476 do_test pragma-5.1 {

   477   catchsql {

   478     BEGIN;

   479     pragma synchronous = OFF;

   480   } 

   481 } {1 {Safety level may not be changed inside a transaction}}

   482 do_test pragma-5.2 {

   483   execsql {

   484     pragma synchronous;

   485   } 

   486 } {2}

   487 catchsql {COMMIT;}

   488 } ;# ifcapable pager_pragmas

   489 

   490 # Test schema-query pragmas

   491 #

   492 ifcapable schema_pragmas {

   493 ifcapable tempdb&&attach {

   494   do_test pragma-6.1 {

   495     set res {}

   496     execsql {SELECT * FROM sqlite_temp_master}

   497     foreach {idx name file} [execsql {pragma database_list}] {

   498       lappend res $idx $name

   499     }

   500     set res

   501   } {0 main 1 temp 2 aux}

   502 }

   503 do_test pragma-6.2 {

   504   execsql {

   505     pragma table_info(t2)

   506   }

   507 } {0 a {} 0 {} 0 1 b {} 0 {} 0 2 c {} 0 {} 0}

   508 db nullvalue <<NULL>>

   509 do_test pragma-6.2.2 {

   510   execsql {

   511     CREATE TABLE t5(

   512       a TEXT DEFAULT CURRENT_TIMESTAMP, 

   513       b DEFAULT (5+3),

   514       c TEXT,

   515       d INTEGER DEFAULT NULL,

   516       e TEXT DEFAULT ''

   517     );

   518     PRAGMA table_info(t5);

   519   }

   520 } {0 a TEXT 0 CURRENT_TIMESTAMP 0 1 b {} 0 5+3 0 2 c TEXT 0 <<NULL>> 0 3 d INTEGER 0 NULL 0 4 e TEXT 0 '' 0}

   521 db nullvalue {}

   522 ifcapable {foreignkey} {

   523   do_test pragma-6.3 {

   524     execsql {

   525       CREATE TABLE t3(a int references t2(b), b UNIQUE);

   526       pragma foreign_key_list(t3);

   527     }

   528   } {0 0 t2 a b}

   529   do_test pragma-6.4 {

   530     execsql {

   531       pragma index_list(t3);

   532     }

   533   } {0 sqlite_autoindex_t3_1 1}

   534 }

   535 ifcapable {!foreignkey} {

   536   execsql {CREATE TABLE t3(a,b UNIQUE)}

   537 }

   538 do_test pragma-6.5 {

   539   execsql {

   540     CREATE INDEX t3i1 ON t3(a,b);

   541     pragma index_info(t3i1);

   542   }

   543 } {0 0 a 1 1 b}

   544 

   545 ifcapable tempdb {

   546   # Test for ticket #3320. When a temp table of the same name exists, make

   547   # sure the schema of the main table can still be queried using 

   548   # "pragma table_info":

   549   do_test pragma-6.6.1 {

   550     execsql {

   551       CREATE TABLE trial(col_main);

   552       CREATE TEMP TABLE trial(col_temp);

   553     }

   554   } {}

   555   do_test pragma-6.6.2 {

   556     execsql {

   557       PRAGMA table_info(trial);

   558     }

   559   } {0 col_temp {} 0 {} 0}

   560   do_test pragma-6.6.3 {

   561     execsql {

   562       PRAGMA temp.table_info(trial);

   563     }

   564   } {0 col_temp {} 0 {} 0}

   565   do_test pragma-6.6.4 {

   566     execsql {

   567       PRAGMA main.table_info(trial);

   568     }

   569   } {0 col_main {} 0 {} 0}

   570 }

   571 } ;# ifcapable schema_pragmas

   572 # Miscellaneous tests

   573 #

   574 ifcapable schema_pragmas {

   575 do_test pragma-7.1 {

   576   # Make sure a pragma knows to read the schema if it needs to

   577   db close

   578   sqlite3 db test.db

   579   execsql {

   580     pragma index_list(t3);

   581   }

   582 } {0 t3i1 0 1 sqlite_autoindex_t3_1 1}

   583 } ;# ifcapable schema_pragmas

   584 ifcapable {utf16} {

   585   do_test pragma-7.2 {

   586     db close

   587     sqlite3 db test.db

   588     catchsql {

   589       pragma encoding=bogus;

   590     }

   591   } {1 {unsupported encoding: bogus}}

   592 }

   593 ifcapable tempdb {

   594   do_test pragma-7.3 {

   595     db close

   596     sqlite3 db test.db

   597     execsql {

   598       pragma lock_status;

   599     }

   600   } {main unlocked temp closed}

   601 } else {

   602   do_test pragma-7.3 {

   603     db close

   604     sqlite3 db test.db

   605     execsql {

   606       pragma lock_status;

   607     }

   608   } {main unlocked}

   609 }

   610 

   611 

   612 #----------------------------------------------------------------------

   613 # Test cases pragma-8.* test the "PRAGMA schema_version" and "PRAGMA

   614 # user_version" statements.

   615 #

   616 # pragma-8.1: PRAGMA schema_version

   617 # pragma-8.2: PRAGMA user_version

   618 #

   619 

   620 ifcapable schema_version {

   621 

   622 # First check that we can set the schema version and then retrieve the

   623 # same value.

   624 do_test pragma-8.1.1 {

   625   execsql {

   626     PRAGMA schema_version = 105;

   627   }

   628 } {}

   629 do_test pragma-8.1.2 {

   630   execsql2 {

   631     PRAGMA schema_version;

   632   }

   633 } {schema_version 105}

   634 do_test pragma-8.1.3 {

   635   execsql {

   636     PRAGMA schema_version = 106;

   637   }

   638 } {}

   639 do_test pragma-8.1.4 {

   640   execsql {

   641     PRAGMA schema_version;

   642   }

   643 } 106

   644 

   645 # Check that creating a table modifies the schema-version (this is really

   646 # to verify that the value being read is in fact the schema version).

   647 do_test pragma-8.1.5 {

   648   execsql {

   649     CREATE TABLE t4(a, b, c);

   650     INSERT INTO t4 VALUES(1, 2, 3);

   651     SELECT * FROM t4;

   652   }

   653 } {1 2 3}

   654 do_test pragma-8.1.6 {

   655   execsql {

   656     PRAGMA schema_version;

   657   }

   658 } 107

   659 

   660 # Now open a second connection to the database. Ensure that changing the

   661 # schema-version using the first connection forces the second connection

   662 # to reload the schema. This has to be done using the C-API test functions,

   663 # because the TCL API accounts for SCHEMA_ERROR and retries the query.

   664 do_test pragma-8.1.7 {

   665   sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]

   666   execsql {

   667     SELECT * FROM t4;

   668   } db2

   669 } {1 2 3}

   670 do_test pragma-8.1.8 {

   671   execsql {

   672     PRAGMA schema_version = 108;

   673   }

   674 } {}

   675 do_test pragma-8.1.9 {

   676   set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM t4" -1 DUMMY]

   677   sqlite3_step $::STMT

   678 } SQLITE_ERROR

   679 do_test pragma-8.1.10 {

   680   sqlite3_finalize $::STMT

   681 } SQLITE_SCHEMA

   682 

   683 # Make sure the schema-version can be manipulated in an attached database.

   684 file delete -force test2.db

   685 file delete -force test2.db-journal

   686 ifcapable attach {

   687   do_test pragma-8.1.11 {

   688     execsql {

   689       ATTACH 'test2.db' AS aux;

   690       CREATE TABLE aux.t1(a, b, c);

   691       PRAGMA aux.schema_version = 205;

   692     }

   693   } {}

   694   do_test pragma-8.1.12 {

   695     execsql {

   696       PRAGMA aux.schema_version;

   697     }

   698   } 205

   699 }

   700 do_test pragma-8.1.13 {

   701   execsql {

   702     PRAGMA schema_version;

   703   }

   704 } 108

   705 

   706 # And check that modifying the schema-version in an attached database

   707 # forces the second connection to reload the schema.

   708 ifcapable attach {

   709   do_test pragma-8.1.14 {

   710     sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]

   711     execsql {

   712       ATTACH 'test2.db' AS aux;

   713       SELECT * FROM aux.t1;

   714     } db2

   715   } {}

   716   do_test pragma-8.1.15 {

   717     execsql {

   718       PRAGMA aux.schema_version = 206;

   719     }

   720   } {}

   721   do_test pragma-8.1.16 {

   722     set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM aux.t1" -1 DUMMY]

   723     sqlite3_step $::STMT

   724   } SQLITE_ERROR

   725   do_test pragma-8.1.17 {

   726     sqlite3_finalize $::STMT

   727   } SQLITE_SCHEMA

   728   do_test pragma-8.1.18 {

   729     db2 close

   730   } {}

   731 }

   732 

   733 # Now test that the user-version can be read and written (and that we aren't

   734 # accidentally manipulating the schema-version instead).

   735 do_test pragma-8.2.1 {

   736   execsql2 {

   737     PRAGMA user_version;

   738   }

   739 } {user_version 0}

   740 do_test pragma-8.2.2 {

   741   execsql {

   742     PRAGMA user_version = 2;

   743   }

   744 } {}

   745 do_test pragma-8.2.3.1 {

   746   execsql2 {

   747     PRAGMA user_version;

   748   }

   749 } {user_version 2}

   750 do_test pragma-8.2.3.2 {

   751   db close

   752   sqlite3 db test.db

   753   execsql {

   754     PRAGMA user_version;

   755   }

   756 } {2}

   757 do_test pragma-8.2.4.1 {

   758   execsql {

   759     PRAGMA schema_version;

   760   }

   761 } {108}

   762 ifcapable vacuum {

   763   do_test pragma-8.2.4.2 {

   764     execsql {

   765       VACUUM;

   766       PRAGMA user_version;

   767     }

   768   } {2}

   769   do_test pragma-8.2.4.3 {

   770     execsql {

   771       PRAGMA schema_version;

   772     }

   773   } {109}

   774 }

   775 

   776 ifcapable attach {

   777   db eval {ATTACH 'test2.db' AS aux}

   778   

   779   # Check that the user-version in the auxilary database can be manipulated (

   780   # and that we aren't accidentally manipulating the same in the main db).

   781   do_test pragma-8.2.5 {

   782     execsql {

   783       PRAGMA aux.user_version;

   784     }

   785   } {0}

   786   do_test pragma-8.2.6 {

   787     execsql {

   788       PRAGMA aux.user_version = 3;

   789     }

   790   } {}

   791   do_test pragma-8.2.7 {

   792     execsql {

   793       PRAGMA aux.user_version;

   794     }

   795   } {3}

   796   do_test pragma-8.2.8 {

   797     execsql {

   798       PRAGMA main.user_version;

   799     }

   800   } {2}

   801   

   802   # Now check that a ROLLBACK resets the user-version if it has been modified

   803   # within a transaction.

   804   do_test pragma-8.2.9 {

   805     execsql {

   806       BEGIN;

   807       PRAGMA aux.user_version = 10;

   808       PRAGMA user_version = 11;

   809     }

   810   } {}

   811   do_test pragma-8.2.10 {

   812     execsql {

   813       PRAGMA aux.user_version;

   814     }

   815   } {10}

   816   do_test pragma-8.2.11 {

   817     execsql {

   818       PRAGMA main.user_version;

   819     }

   820   } {11}

   821   do_test pragma-8.2.12 {

   822     execsql {

   823       ROLLBACK;

   824       PRAGMA aux.user_version;

   825     }

   826   } {3}

   827   do_test pragma-8.2.13 {

   828     execsql {

   829       PRAGMA main.user_version;

   830     }

   831   } {2}

   832 }

   833 

   834 # Try a negative value for the user-version

   835 do_test pragma-8.2.14 {

   836   execsql {

   837     PRAGMA user_version = -450;

   838   }

   839 } {}

   840 do_test pragma-8.2.15 {

   841   execsql {

   842     PRAGMA user_version;

   843   }

   844 } {-450}

   845 } ; # ifcapable schema_version

   846 

   847 # Check to see if TEMP_STORE is memory or disk.  Return strings

   848 # "memory" or "disk" as appropriate.

   849 #

   850 proc check_temp_store {} {

   851   db eval {CREATE TEMP TABLE IF NOT EXISTS a(b)}

   852   db eval {PRAGMA database_list} {

   853     if {$name=="temp"} {

   854       set bt [btree_from_db db 1]

   855       if {[btree_ismemdb $bt]} {

   856         return "memory"

   857       }

   858       return "disk"

   859     }

   860   }

   861   return "unknown"

   862 }

   863 

   864 

   865 # Test temp_store and temp_store_directory pragmas

   866 #

   867 ifcapable pager_pragmas {

   868 do_test pragma-9.1 {

   869   db close

   870   sqlite3 db test.db

   871   execsql {

   872     PRAGMA temp_store;

   873   }

   874 } {0}

   875 if {$TEMP_STORE<=1} {

   876   do_test pragma-9.1.1 {

   877     check_temp_store

   878   } {disk}

   879 } else {

   880   do_test pragma-9.1.1 {

   881     check_temp_store

   882   } {memory}

   883 }

   884 

   885 do_test pragma-9.2 {

   886   db close

   887   sqlite3 db test.db

   888   execsql {

   889     PRAGMA temp_store=file;

   890     PRAGMA temp_store;

   891   }

   892 } {1}

   893 if {$TEMP_STORE==3} {

   894   # When TEMP_STORE is 3, always use memory regardless of pragma settings.

   895   do_test pragma-9.2.1 {

   896     check_temp_store

   897   } {memory}

   898 } else {

   899   do_test pragma-9.2.1 {

   900     check_temp_store

   901   } {disk}

   902 }

   903 

   904 do_test pragma-9.3 {

   905   db close

   906   sqlite3 db test.db

   907   execsql {

   908     PRAGMA temp_store=memory;

   909     PRAGMA temp_store;

   910   }

   911 } {2}

   912 if {$TEMP_STORE==0} {

   913   # When TEMP_STORE is 0, always use the disk regardless of pragma settings.

   914   do_test pragma-9.3.1 {

   915     check_temp_store

   916   } {disk}

   917 } else {

   918   do_test pragma-9.3.1 {

   919     check_temp_store

   920   } {memory}

   921 }

   922 

   923 do_test pragma-9.4 {

   924   execsql {

   925     PRAGMA temp_store_directory;

   926   }

   927 } {}

   928 ifcapable wsd {

   929   do_test pragma-9.5 {

   930     set pwd [string map {' ''} [file nativename [pwd]]]

   931     execsql "

   932       PRAGMA temp_store_directory='$pwd';

   933     "

   934   } {}

   935   do_test pragma-9.6 {

   936     execsql { 

   937       PRAGMA temp_store_directory;

   938     }

   939   } [list [file nativename [pwd]]]

   940   do_test pragma-9.7 {

   941     catchsql { 

   942       PRAGMA temp_store_directory='/NON/EXISTENT/PATH/FOOBAR';

   943     }

   944   } {1 {not a writable directory}}

   945   do_test pragma-9.8 {

   946     execsql { 

   947       PRAGMA temp_store_directory='';

   948     }

   949   } {}

   950   if {![info exists TEMP_STORE] || $TEMP_STORE<=1} {

   951     ifcapable tempdb {

   952       do_test pragma-9.9 {

   953         execsql { 

   954           PRAGMA temp_store_directory;

   955           PRAGMA temp_store=FILE;

   956           CREATE TEMP TABLE temp_store_directory_test(a integer);

   957           INSERT INTO temp_store_directory_test values (2);

   958           SELECT * FROM temp_store_directory_test;

   959         }

   960       } {2}

   961       do_test pragma-9.10 {

   962         catchsql "

   963           PRAGMA temp_store_directory='$pwd';

   964           SELECT * FROM temp_store_directory_test;

   965         "

   966       } {1 {no such table: temp_store_directory_test}}

   967     }

   968   }

   969 }

   970 do_test pragma-9.11 {

   971   execsql {

   972     PRAGMA temp_store = 0;

   973     PRAGMA temp_store;

   974   }

   975 } {0}

   976 do_test pragma-9.12 {

   977   execsql {

   978     PRAGMA temp_store = 1;

   979     PRAGMA temp_store;

   980   }

   981 } {1}

   982 do_test pragma-9.13 {

   983   execsql {

   984     PRAGMA temp_store = 2;

   985     PRAGMA temp_store;

   986   }

   987 } {2}

   988 do_test pragma-9.14 {

   989   execsql {

   990     PRAGMA temp_store = 3;

   991     PRAGMA temp_store;

   992   }

   993 } {0}

   994 do_test pragma-9.15 {

   995   catchsql {

   996     BEGIN EXCLUSIVE;

   997     CREATE TEMP TABLE temp_table(t);

   998     INSERT INTO temp_table VALUES('valuable data');

   999     PRAGMA temp_store = 1;

  1000   }

  1001 } {1 {temporary storage cannot be changed from within a transaction}}

  1002 do_test pragma-9.16 {

  1003   execsql {

  1004     SELECT * FROM temp_table;

  1005     COMMIT;

  1006   }

  1007 } {{valuable data}}

  1008 

  1009 do_test pragma-9.17 {

  1010   execsql {

  1011     INSERT INTO temp_table VALUES('valuable data II');

  1012     SELECT * FROM temp_table;

  1013   }

  1014 } {{valuable data} {valuable data II}}

  1015 

  1016 do_test pragma-9.18 {

  1017   set rc [catch {

  1018     db eval {SELECT t FROM temp_table} {

  1019       execsql {pragma temp_store = 1}

  1020     }

  1021   } msg]

  1022   list $rc $msg

  1023 } {1 {temporary storage cannot be changed from within a transaction}}

  1024 

  1025 } ;# ifcapable pager_pragmas

  1026 

  1027 ifcapable trigger {

  1028 

  1029 do_test pragma-10.0 {

  1030   catchsql {

  1031     DROP TABLE main.t1;

  1032   }

  1033   execsql {

  1034     PRAGMA count_changes = 1;

  1035 

  1036     CREATE TABLE t1(a PRIMARY KEY);

  1037     CREATE TABLE t1_mirror(a);

  1038     CREATE TABLE t1_mirror2(a);

  1039     CREATE TRIGGER t1_bi BEFORE INSERT ON t1 BEGIN 

  1040       INSERT INTO t1_mirror VALUES(new.a);

  1041     END;

  1042     CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN 

  1043       INSERT INTO t1_mirror2 VALUES(new.a);

  1044     END;

  1045     CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN 

  1046       UPDATE t1_mirror SET a = new.a WHERE a = old.a;

  1047     END;

  1048     CREATE TRIGGER t1_au AFTER UPDATE ON t1 BEGIN 

  1049       UPDATE t1_mirror2 SET a = new.a WHERE a = old.a;

  1050     END;

  1051     CREATE TRIGGER t1_bd BEFORE DELETE ON t1 BEGIN 

  1052       DELETE FROM t1_mirror WHERE a = old.a;

  1053     END;

  1054     CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN 

  1055       DELETE FROM t1_mirror2 WHERE a = old.a;

  1056     END;

  1057   }

  1058 } {}

  1059 

  1060 do_test pragma-10.1 {

  1061   execsql {

  1062     INSERT INTO t1 VALUES(randstr(10,10));

  1063   }

  1064 } {1}

  1065 do_test pragma-10.2 {

  1066   execsql {

  1067     UPDATE t1 SET a = randstr(10,10);

  1068   }

  1069 } {1}

  1070 do_test pragma-10.3 {

  1071   execsql {

  1072     DELETE FROM t1;

  1073   }

  1074 } {1}

  1075 

  1076 } ;# ifcapable trigger

  1077 

  1078 ifcapable schema_pragmas {

  1079   do_test pragma-11.1 {

  1080     execsql2 {

  1081       pragma collation_list;

  1082     }

  1083   } {seq 0 name NOCASE seq 1 name RTRIM seq 2 name BINARY}

  1084   do_test pragma-11.2 {

  1085     db collate New_Collation blah...

  1086     execsql {

  1087       pragma collation_list;

  1088     }

  1089   } {0 New_Collation 1 NOCASE 2 RTRIM 3 BINARY}

  1090 }

  1091 

  1092 ifcapable schema_pragmas&&tempdb {

  1093   do_test pragma-12.1 {

  1094     sqlite3 db2 test.db

  1095     execsql {

  1096       PRAGMA temp.table_info('abc');

  1097     } db2

  1098   } {}

  1099   db2 close

  1100 

  1101   do_test pragma-12.2 {

  1102     sqlite3 db2 test.db

  1103     execsql {

  1104       PRAGMA temp.default_cache_size = 200;

  1105       PRAGMA temp.default_cache_size;

  1106     } db2

  1107   } {200}

  1108   db2 close

  1109 

  1110   do_test pragma-12.3 {

  1111     sqlite3 db2 test.db

  1112     execsql {

  1113       PRAGMA temp.cache_size = 400;

  1114       PRAGMA temp.cache_size;

  1115     } db2

  1116   } {400}

  1117   db2 close

  1118 }

  1119 

  1120 ifcapable bloblit {

  1121 

  1122 do_test pragma-13.1 {

  1123   execsql {

  1124     DROP TABLE IF EXISTS t4;

  1125     PRAGMA vdbe_trace=on;

  1126     PRAGMA vdbe_listing=on;

  1127     PRAGMA sql_trace=on;

  1128     CREATE TABLE t4(a INTEGER PRIMARY KEY,b);

  1129     INSERT INTO t4(b) VALUES(x'0123456789abcdef0123456789abcdef0123456789');

  1130     INSERT INTO t4(b) VALUES(randstr(30,30));

  1131     INSERT INTO t4(b) VALUES(1.23456);

  1132     INSERT INTO t4(b) VALUES(NULL);

  1133     INSERT INTO t4(b) VALUES(0);

  1134     INSERT INTO t4(b) SELECT b||b||b||b FROM t4;

  1135     SELECT * FROM t4;