000001  # 2002 March 6
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  # This file implements regression tests for SQLite library.
000012  #
000013  # This file implements tests for the PRAGMA command.
000014  #
000015  # $Id: pragma.test,v 1.73 2009/01/12 14:01:45 danielk1977 Exp $
000016  
000017  set testdir [file dirname $argv0]
000018  source $testdir/tester.tcl
000019  set testprefix pragma
000020  
000021  # Do not use a codec for tests in this file, as the database file is
000022  # manipulated directly using tcl scripts (using the [hexio_write] command).
000023  #
000024  do_not_use_codec
000025  
000026  # Test organization:
000027  #
000028  # pragma-1.*: Test cache_size, default_cache_size and synchronous on main db.
000029  # pragma-2.*: Test synchronous on attached db.
000030  # pragma-3.*: Test detection of table/index inconsistency by integrity_check.
000031  # pragma-4.*: Test cache_size and default_cache_size on attached db.
000032  # pragma-5.*: Test that pragma synchronous may not be used inside of a
000033  #             transaction.
000034  # pragma-6.*: Test schema-query pragmas.
000035  # pragma-7.*: Miscellaneous tests.
000036  # pragma-8.*: Test user_version and schema_version pragmas.
000037  # pragma-9.*: Test temp_store and temp_store_directory.
000038  # pragma-10.*: Test the count_changes pragma in the presence of triggers.
000039  # pragma-11.*: Test the collation_list pragma.
000040  # pragma-14.*: Test the page_count pragma.
000041  # pragma-15.*: Test that the value set using the cache_size pragma is not
000042  #              reset when the schema is reloaded.
000043  # pragma-16.*: Test proxy locking
000044  # pragma-20.*: Test data_store_directory.
000045  # pragma-22.*: Test that "PRAGMA [db].integrity_check" respects the "db"
000046  #              directive - if it is present.
000047  #
000048  
000049  ifcapable !pragma {
000050    finish_test
000051    return
000052  }
000053  
000054  # Capture the output of a pragma in a TEMP table.
000055  #
000056  proc capture_pragma {db tabname sql} {
000057    $db eval "DROP TABLE IF EXISTS temp.$tabname"
000058    set once 1
000059    $db eval $sql x {
000060      if {$once} {
000061        set once 0
000062        set ins "INSERT INTO $tabname VALUES"
000063        set crtab "CREATE TEMP TABLE $tabname "
000064        set sep "("
000065        foreach col $x(*) {
000066          append ins ${sep}\$x($col)
000067          append crtab ${sep}\"$col\"
000068          set sep ,
000069        }
000070        append ins )
000071        append crtab )
000072        $db eval $crtab
000073      }
000074      $db eval $ins
000075    }
000076  }
000077  
000078  # Delete the preexisting database to avoid the special setup
000079  # that the "all.test" script does.
000080  #
000081  db close
000082  delete_file test.db test.db-journal
000083  delete_file test3.db test3.db-journal
000084  sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
000085  
000086  # EVIDENCE-OF: R-13861-56665 PRAGMA schema.cache_size; PRAGMA
000087  # schema.cache_size = pages; PRAGMA schema.cache_size = -kibibytes;
000088  # Query or change the suggested maximum number of database disk pages
000089  # that SQLite will hold in memory at once per open database file.
000090  #
000091  ifcapable pager_pragmas {
000092  set DFLT_CACHE_SZ [db one {PRAGMA default_cache_size}]
000093  set TEMP_CACHE_SZ [db one {PRAGMA temp.default_cache_size}]
000094  do_test pragma-1.1 {
000095    execsql {
000096      PRAGMA cache_size;
000097      PRAGMA default_cache_size;
000098      PRAGMA synchronous;
000099    }
000100  } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]
000101  do_test pragma-1.2 {
000102    # EVIDENCE-OF: R-42059-47211 If the argument N is positive then the
000103    # suggested cache size is set to N.
000104    execsql {
000105      PRAGMA synchronous=OFF;
000106      PRAGMA cache_size=1234;
000107      PRAGMA cache_size;
000108      PRAGMA default_cache_size;
000109      PRAGMA synchronous;
000110    }
000111  } [list 1234 $DFLT_CACHE_SZ 0]
000112  do_test pragma-1.3 {
000113    db close
000114    sqlite3 db test.db
000115    execsql {
000116      PRAGMA cache_size;
000117      PRAGMA default_cache_size;
000118      PRAGMA synchronous;
000119    }
000120  } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]
000121  do_test pragma-1.4 {
000122    execsql {
000123      PRAGMA synchronous=OFF;
000124      PRAGMA cache_size;
000125      PRAGMA default_cache_size;
000126      PRAGMA synchronous;
000127    }
000128  } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 0]
000129  do_test pragma-1.5 {
000130    execsql {
000131      PRAGMA cache_size=-4321;
000132      PRAGMA cache_size;
000133      PRAGMA default_cache_size;
000134      PRAGMA synchronous;
000135    }
000136  } [list -4321 $DFLT_CACHE_SZ 0]
000137  do_test pragma-1.6 {
000138    execsql {
000139      PRAGMA synchronous=ON;
000140      PRAGMA cache_size;
000141      PRAGMA default_cache_size;
000142      PRAGMA synchronous;
000143    }
000144  } [list -4321 $DFLT_CACHE_SZ 1]
000145  do_test pragma-1.7 {
000146    db close
000147    sqlite3 db test.db
000148    execsql {
000149      PRAGMA cache_size;
000150      PRAGMA default_cache_size;
000151      PRAGMA synchronous;
000152    }
000153  } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]
000154  do_test pragma-1.8 {
000155    execsql {
000156      PRAGMA default_cache_size=-123;
000157      PRAGMA cache_size;
000158      PRAGMA default_cache_size;
000159      PRAGMA synchronous;
000160    }
000161  } {123 123 2}
000162  do_test pragma-1.9.1 {
000163    db close
000164    sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db]
000165    execsql {
000166      PRAGMA cache_size;
000167      PRAGMA default_cache_size;
000168      PRAGMA synchronous;
000169    }
000170  } {123 123 2}
000171  ifcapable vacuum {
000172    do_test pragma-1.9.2 {
000173      execsql {
000174        VACUUM;
000175        PRAGMA cache_size;
000176        PRAGMA default_cache_size;
000177        PRAGMA synchronous;
000178      }
000179    } {123 123 2}
000180  }
000181  do_test pragma-1.10 {
000182    execsql {
000183      PRAGMA synchronous=NORMAL;
000184      PRAGMA cache_size;
000185      PRAGMA default_cache_size;
000186      PRAGMA synchronous;
000187    }
000188  } {123 123 1}
000189  do_test pragma-1.11.1 {
000190    execsql {
000191      PRAGMA synchronous=EXTRA;
000192      PRAGMA cache_size;
000193      PRAGMA default_cache_size;
000194      PRAGMA synchronous;
000195    }
000196  } {123 123 3}
000197  do_test pragma-1.11.2 {
000198    execsql {
000199      PRAGMA synchronous=FULL;
000200      PRAGMA cache_size;
000201      PRAGMA default_cache_size;
000202      PRAGMA synchronous;
000203    }
000204  } {123 123 2}
000205  do_test pragma-1.12 {
000206    db close
000207    sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db]
000208    execsql {
000209      PRAGMA cache_size;
000210      PRAGMA default_cache_size;
000211      PRAGMA synchronous;
000212    }
000213  } {123 123 2}
000214  
000215  # Make sure the pragma handler understands numeric values in addition
000216  # to keywords like "off" and "full".
000217  #
000218  do_test pragma-1.13 {
000219    execsql {
000220      PRAGMA synchronous=0;
000221      PRAGMA synchronous;
000222    }
000223  } {0}
000224  do_test pragma-1.14 {
000225    execsql {
000226      PRAGMA synchronous=2;
000227      PRAGMA synchronous;
000228    }
000229  } {2}
000230  do_test pragma-1.14.1 {
000231    execsql {
000232      PRAGMA synchronous=4;
000233      PRAGMA synchronous;
000234    }
000235  } {4}
000236  do_test pragma-1.14.2 {
000237    execsql {
000238      PRAGMA synchronous=3;
000239      PRAGMA synchronous;
000240    }
000241  } {3}
000242  do_test pragma-1.14.3 {
000243    execsql {
000244      PRAGMA synchronous=8;
000245      PRAGMA synchronous;
000246    }
000247  } {0}
000248  do_test pragma-1.14.4 {
000249    execsql {
000250      PRAGMA synchronous=10;
000251      PRAGMA synchronous;
000252    }
000253  } {2}
000254  
000255  do_execsql_test 1.15.1 {
000256    PRAGMA default_cache_size = 0;
000257  }
000258  do_execsql_test 1.15.2 {
000259    PRAGMA default_cache_size;
000260  } $DFLT_CACHE_SZ
000261  do_execsql_test 1.15.3 {
000262    PRAGMA default_cache_size = -500;
000263  }
000264  do_execsql_test 1.15.4 {
000265    PRAGMA default_cache_size;
000266  } 500
000267  do_execsql_test 1.15.3 {
000268    PRAGMA default_cache_size = 500;
000269  }
000270  do_execsql_test 1.15.4 {
000271    PRAGMA default_cache_size;
000272  } 500
000273  db close
000274  hexio_write test.db 48 FFFFFF00
000275  sqlite3 db test.db
000276  do_execsql_test 1.15.4 {
000277    PRAGMA default_cache_size;
000278  } 256
000279  } ;# ifcapable pager_pragmas
000280  
000281  # Test turning "flag" pragmas on and off.
000282  #
000283  ifcapable debug {
000284    # Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG
000285    #
000286    do_test pragma-1.15 {
000287      execsql {
000288        PRAGMA vdbe_listing=YES;
000289        PRAGMA vdbe_listing;
000290      }
000291    } {1}
000292    do_test pragma-1.16 {
000293      execsql {
000294        PRAGMA vdbe_listing=NO;
000295        PRAGMA vdbe_listing;
000296      }
000297    } {0}
000298  }
000299  
000300  do_test pragma-1.17 {
000301    execsql {
000302      PRAGMA parser_trace=ON;
000303      PRAGMA parser_trace=OFF;
000304    }
000305  } {}
000306  do_test pragma-1.18 {
000307    execsql {
000308      PRAGMA bogus = -1234;  -- Parsing of negative values
000309    }
000310  } {}
000311  
000312  # Test modifying the safety_level of an attached database.
000313  ifcapable pager_pragmas&&attach {
000314    do_test pragma-2.1 {
000315      forcedelete test2.db
000316      forcedelete test2.db-journal
000317      execsql {
000318        ATTACH 'test2.db' AS aux;
000319      } 
000320    } {}
000321    do_test pragma-2.2 {
000322      execsql {
000323        pragma aux.synchronous;
000324      } 
000325    } {2}
000326    do_test pragma-2.3 {
000327      execsql {
000328        pragma aux.synchronous = OFF;
000329        pragma aux.synchronous;
000330        pragma synchronous;
000331      } 
000332    } {0 2}
000333    do_test pragma-2.4 {
000334      execsql {
000335        pragma aux.synchronous = ON;
000336        pragma synchronous;
000337        pragma aux.synchronous;
000338      } 
000339    } {2 1}
000340  } ;# ifcapable pager_pragmas
000341  
000342  # Construct a corrupted index and make sure the integrity_check
000343  # pragma finds it.
000344  #
000345  # These tests won't work if the database is encrypted
000346  #
000347  do_test pragma-3.1 {
000348    db close
000349    forcedelete test.db test.db-journal
000350    sqlite3 db test.db
000351    execsql {
000352      PRAGMA auto_vacuum=OFF;
000353      BEGIN;
000354      CREATE TABLE t2(a,b,c);
000355      CREATE INDEX i2 ON t2(a);
000356      INSERT INTO t2 VALUES(11,2,3);
000357      INSERT INTO t2 VALUES(22,3,4);
000358      COMMIT;
000359      SELECT rowid, * from t2;
000360    }
000361  } {1 11 2 3 2 22 3 4}
000362  ifcapable attach {
000363    if {![sqlite3 -has-codec] && $sqlite_options(integrityck)} {
000364      do_test pragma-3.2 {
000365        db eval {SELECT rootpage FROM sqlite_master WHERE name='i2'} break
000366        set pgsz [db eval {PRAGMA page_size}]
000367        # overwrite the header on the rootpage of the index in order to
000368        # make the index appear to be empty.
000369        #
000370        set offset [expr {$pgsz*($rootpage-1)}]
000371        hexio_write test.db $offset 0a00000000040000000000
000372        db close
000373        sqlite3 db test.db
000374        execsql {PRAGMA integrity_check}
000375      } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}
000376      do_test pragma-3.3 {
000377        execsql {PRAGMA integrity_check=1}
000378      } {{row 1 missing from index i2}}
000379      do_test pragma-3.4 {
000380        execsql {
000381          ATTACH DATABASE 'test.db' AS t2;
000382          PRAGMA integrity_check
000383        }
000384      } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}
000385      do_test pragma-3.5 {
000386        execsql {
000387          PRAGMA integrity_check=4
000388        }
000389      } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {row 1 missing from index i2}}
000390      do_catchsql_test pragma-3.6 {
000391        PRAGMA integrity_check=xyz
000392      } {1 {no such table: xyz}}
000393      do_catchsql_test pragma-3.6b {
000394        PRAGMA integrity_check=t2
000395      } {0 {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}}
000396      do_catchsql_test pragma-3.6c {
000397        PRAGMA integrity_check=sqlite_schema
000398      } {0 ok}
000399      do_test pragma-3.7 {
000400        execsql {
000401          PRAGMA integrity_check=0
000402        }
000403      } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}
000404    
000405      # Add additional corruption by appending unused pages to the end of
000406      # the database file testerr.db
000407      #
000408      do_test pragma-3.8 {
000409        execsql {DETACH t2}
000410        forcedelete testerr.db testerr.db-journal
000411        set out [open testerr.db w]
000412        fconfigure $out -translation binary
000413        set in [open test.db r]
000414        fconfigure $in -translation binary
000415        puts -nonewline $out [read $in]
000416        seek $in 0
000417        puts -nonewline $out [read $in]
000418        close $in
000419        close $out
000420        hexio_write testerr.db 28 00000000
000421        execsql {REINDEX t2}
000422        execsql {PRAGMA integrity_check}
000423      } {ok}
000424      do_test pragma-3.8.1 {
000425        execsql {PRAGMA quick_check}
000426      } {ok}
000427      do_test pragma-3.8.2 {
000428        execsql {PRAGMA QUICK_CHECK}
000429      } {ok}
000430      do_test pragma-3.9a {
000431        execsql {
000432          ATTACH 'testerr.db' AS t2;
000433          PRAGMA integrity_check
000434        }
000435      } {{*** in database t2 ***
000436  Page 4 is never used
000437  Page 5 is never used
000438  Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}
000439      do_execsql_test pragma-3.9b {
000440        PRAGMA t2.integrity_check=t2;
000441      } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}
000442      do_execsql_test pragma-3.9c {
000443        PRAGMA t2.integrity_check=sqlite_schema;
000444      } {ok}
000445      do_test pragma-3.10 {
000446        execsql {
000447          PRAGMA integrity_check=1
000448        }
000449      } {{*** in database t2 ***
000450  Page 4 is never used}}
000451      do_test pragma-3.11 {
000452        execsql {
000453          PRAGMA integrity_check=5
000454        }
000455      } {{*** in database t2 ***
000456  Page 4 is never used
000457  Page 5 is never used
000458  Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2}}
000459      do_test pragma-3.12 {
000460        execsql {
000461          PRAGMA integrity_check=4
000462        }
000463      } {{*** in database t2 ***
000464  Page 4 is never used
000465  Page 5 is never used
000466  Page 6 is never used} {row 1 missing from index i2}}
000467      do_test pragma-3.13 {
000468        execsql {
000469          PRAGMA integrity_check=3
000470        }
000471      } {{*** in database t2 ***
000472  Page 4 is never used
000473  Page 5 is never used
000474  Page 6 is never used}}
000475      do_test pragma-3.14 {
000476        execsql {
000477          PRAGMA integrity_check(2)
000478        }
000479      } {{*** in database t2 ***
000480  Page 4 is never used
000481  Page 5 is never used}}
000482      do_test pragma-3.15 {
000483        execsql {
000484          ATTACH 'testerr.db' AS t3;
000485          PRAGMA integrity_check
000486        }
000487      } {{*** in database t2 ***
000488  Page 4 is never used
000489  Page 5 is never used
000490  Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 ***
000491  Page 4 is never used
000492  Page 5 is never used
000493  Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}
000494      do_test pragma-3.16 {
000495        execsql {
000496          PRAGMA integrity_check(10)
000497        }
000498      } {{*** in database t2 ***
000499  Page 4 is never used
000500  Page 5 is never used
000501  Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 ***
000502  Page 4 is never used
000503  Page 5 is never used
000504  Page 6 is never used} {row 1 missing from index i2}}
000505      do_test pragma-3.17 {
000506        execsql {
000507          PRAGMA integrity_check=8
000508        }
000509      } {{*** in database t2 ***
000510  Page 4 is never used
000511  Page 5 is never used
000512  Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 ***
000513  Page 4 is never used
000514  Page 5 is never used}}
000515      do_test pragma-3.18 {
000516        execsql {
000517          PRAGMA integrity_check=4
000518        }
000519      } {{*** in database t2 ***
000520  Page 4 is never used
000521  Page 5 is never used
000522  Page 6 is never used} {row 1 missing from index i2}}
000523    }
000524    do_test pragma-3.19 {
000525      catch {db close}
000526      forcedelete test.db test.db-journal
000527      sqlite3 db test.db
000528      db eval {PRAGMA integrity_check}
000529    } {ok}
000530  }
000531  
000532  # Verify that PRAGMA integrity_check catches UNIQUE and NOT NULL
000533  # constraint violations.
000534  #
000535  ifcapable altertable {
000536    sqlite3_db_config db DEFENSIVE 0
000537      do_execsql_test pragma-3.20 {
000538        CREATE TABLE t1(a,b);
000539        CREATE INDEX t1a ON t1(a);
000540        INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(2,4),(NULL,5),(NULL,6);
000541        PRAGMA writable_schema=ON;
000542        UPDATE sqlite_master SET sql='CREATE UNIQUE INDEX t1a ON t1(a)'
000543          WHERE name='t1a';
000544        UPDATE sqlite_master SET sql='CREATE TABLE t1(a NOT NULL,b)'
000545          WHERE name='t1';
000546        PRAGMA writable_schema=OFF;
000547        ALTER TABLE t1 RENAME TO t1x;
000548        PRAGMA integrity_check;
000549      } {{non-unique entry in index t1a} {NULL value in t1x.a} {non-unique entry in index t1a} {NULL value in t1x.a}}
000550    do_execsql_test pragma-3.21 {
000551      PRAGMA integrity_check(3);
000552    } {{non-unique entry in index t1a} {NULL value in t1x.a} {non-unique entry in index t1a}}
000553    do_execsql_test pragma-3.22 {
000554      PRAGMA integrity_check(2);
000555    } {{non-unique entry in index t1a} {NULL value in t1x.a}}
000556    do_execsql_test pragma-3.23 {
000557      PRAGMA integrity_check(1);
000558    } {{non-unique entry in index t1a}}
000559  }
000560  
000561  # PRAGMA integrity check (or more specifically the sqlite3BtreeCount()
000562  # interface) used to leave index cursors in an inconsistent state
000563  # which could result in an assertion fault in sqlite3BtreeKey()
000564  # called from saveCursorPosition() if content is removed from the
000565  # index while the integrity_check is still running.  This test verifies
000566  # that problem has been fixed.
000567  #
000568  do_test pragma-3.30 {
000569    catch { db close }
000570    delete_file test.db
000571    sqlite3 db test.db
000572    db eval {
000573      CREATE TABLE t1(a,b,c);
000574      WITH RECURSIVE
000575        c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<100)
000576      INSERT INTO t1(a,b,c) SELECT i, printf('xyz%08x',i), 2000-i FROM c;
000577      CREATE INDEX t1a ON t1(a);
000578      CREATE INDEX t1bc ON t1(b,c);
000579    }
000580    db eval {PRAGMA integrity_check} {
000581       db eval {DELETE FROM t1}
000582    }
000583  } {}
000584  
000585  # Test modifying the cache_size of an attached database.
000586  ifcapable pager_pragmas&&attach {
000587  do_test pragma-4.1 {
000588    execsql {
000589      ATTACH 'test2.db' AS aux;
000590      pragma aux.cache_size;
000591      pragma aux.default_cache_size;
000592    } 
000593  } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
000594  do_test pragma-4.2 {
000595    execsql {
000596      pragma aux.cache_size = 50;
000597      pragma aux.cache_size;
000598      pragma aux.default_cache_size;
000599    } 
000600  } [list 50 $DFLT_CACHE_SZ]
000601  do_test pragma-4.3 {
000602    execsql {
000603      pragma aux.default_cache_size = 456;
000604      pragma aux.cache_size;
000605      pragma aux.default_cache_size;
000606    } 
000607  } {456 456}
000608  do_test pragma-4.4 {
000609    execsql {
000610      pragma cache_size;
000611      pragma default_cache_size;
000612    } 
000613  } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
000614  do_test pragma-4.5 {
000615    execsql {
000616      DETACH aux;
000617      ATTACH 'test3.db' AS aux;
000618      pragma aux.cache_size;
000619      pragma aux.default_cache_size;
000620    } 
000621  } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
000622  do_test pragma-4.6 {
000623    execsql {
000624      DETACH aux;
000625      ATTACH 'test2.db' AS aux;
000626      pragma aux.cache_size;
000627      pragma aux.default_cache_size;
000628    } 
000629  } {456 456}
000630  } ;# ifcapable pager_pragmas
000631  
000632  # Test that modifying the sync-level in the middle of a transaction is
000633  # disallowed.
000634  ifcapable pager_pragmas {
000635  do_test pragma-5.0 {
000636    execsql {
000637      pragma synchronous;
000638    } 
000639  } {2}
000640  do_test pragma-5.1 {
000641    catchsql {
000642      BEGIN;
000643      pragma synchronous = OFF;
000644    } 
000645  } {1 {Safety level may not be changed inside a transaction}}
000646  do_test pragma-5.2 {
000647    execsql {
000648      pragma synchronous;
000649    } 
000650  } {2}
000651  catchsql {COMMIT;}
000652  } ;# ifcapable pager_pragmas
000653  
000654  # Test schema-query pragmas
000655  #
000656  ifcapable schema_pragmas {
000657  ifcapable tempdb&&attach {
000658    do_test pragma-6.1 {
000659      set res {}
000660      execsql {SELECT * FROM sqlite_temp_master}
000661      foreach {idx name file} [execsql {pragma database_list}] {
000662        lappend res $idx $name
000663      }
000664      set res
000665    } {0 main 1 temp 2 aux}
000666  }
000667  do_test pragma-6.2 {
000668    execsql {
000669      CREATE TABLE t2(a TYPE_X, b [TYPE_Y], c "TYPE_Z");
000670      pragma table_info(t2)
000671    }
000672  } {0 a TYPE_X 0 {} 0 1 b TYPE_Y 0 {} 0 2 c TYPE_Z 0 {} 0}
000673  do_test pragma-6.2.1 {
000674    execsql {
000675      pragma table_info;
000676    }
000677  } {}
000678  db nullvalue <<NULL>>
000679  do_test pragma-6.2.2 {
000680    execsql {
000681      CREATE TABLE t5(
000682        a TEXT DEFAULT CURRENT_TIMESTAMP, 
000683        b DEFAULT (5+3),
000684        c TEXT,
000685        d INTEGER DEFAULT NULL,
000686        e TEXT DEFAULT '',
000687        UNIQUE(b,c,d),
000688        PRIMARY KEY(e,b,c)
000689      );
000690      PRAGMA table_info(t5);
000691    }
000692  } {0 a TEXT 0 CURRENT_TIMESTAMP 0 1 b {} 0 5+3 2 2 c TEXT 0 <<NULL>> 3 3 d INTEGER 0 NULL 0 4 e TEXT 0 '' 1}
000693  db nullvalue {}
000694  do_test pragma-6.2.3 {
000695    execsql {
000696      CREATE TABLE t2_3(a,b INTEGER PRIMARY KEY,c);
000697      pragma table_info(t2_3)
000698    }
000699  } {0 a {} 0 {} 0 1 b INTEGER 0 {} 1 2 c {} 0 {} 0}
000700  ifcapable {foreignkey} {
000701    do_test pragma-6.3.1 {
000702      execsql {
000703        CREATE TABLE t3(a int references t2(b), b UNIQUE);
000704        pragma foreign_key_list(t3);
000705      }
000706    } {0 0 t2 a b {NO ACTION} {NO ACTION} NONE}
000707    do_test pragma-6.3.2 {
000708      execsql {
000709        pragma foreign_key_list;
000710      }
000711    } {}
000712    do_test pragma-6.3.3 {
000713      execsql {
000714        pragma foreign_key_list(t3_bogus);
000715      }
000716    } {}
000717    do_test pragma-6.3.4 {
000718      execsql {
000719        pragma foreign_key_list(t5);
000720      }
000721    } {}
000722    do_test pragma-6.4 {
000723      capture_pragma db out {
000724        pragma index_list(t3);
000725      }
000726      db eval {SELECT seq, "name", "unique" FROM out ORDER BY seq}
000727    } {0 sqlite_autoindex_t3_1 1}
000728  }
000729  ifcapable {!foreignkey} {
000730    execsql {CREATE TABLE t3(a,b UNIQUE)}
000731  }
000732  do_test pragma-6.5.1 {
000733    execsql {
000734      CREATE INDEX t3i1 ON t3(a,b);
000735    }
000736    capture_pragma db out {
000737      pragma index_info(t3i1);
000738    }
000739    db eval {SELECT seqno, cid, name FROM out ORDER BY seqno}
000740  } {0 0 a 1 1 b}
000741  
000742  # EVIDENCE-OF: R-23114-21695 The auxiliary index-columns are not shown
000743  # by the index_info pragma, but they are listed by the index_xinfo
000744  # pragma.
000745  #
000746  do_test pragma-6.5.1b {
000747    capture_pragma db out {PRAGMA index_xinfo(t3i1)}
000748    db eval {SELECT seqno, cid, name FROM out ORDER BY seqno}
000749  } {0 0 a 1 1 b 2 -1 {}}
000750  
000751  
000752  # EVIDENCE-OF: R-29448-60346 PRAGMA schema.index_info(index-name); This
000753  # pragma returns one row for each key column in the named index.
000754  #
000755  # (The first column of output from PRAGMA index_info is...)
000756  # EVIDENCE-OF: R-34186-52914 The rank of the column within the index. (0
000757  # means left-most.)
000758  #
000759  # (The second column of output from PRAGMA index_info is...)
000760  # EVIDENCE-OF: R-65019-08383 The rank of the column within the table
000761  # being indexed.
000762  #
000763  # (The third column of output from PRAGMA index_info is...)
000764  # EVIDENCE-OF: R-09773-34266 The name of the column being indexed.
000765  #
000766  do_execsql_test pragma-6.5.1c {
000767    CREATE INDEX t3i2 ON t3(b,a);
000768    PRAGMA index_info='t3i2';
000769    DROP INDEX t3i2;
000770  } {0 1 b 1 0 a}
000771  
000772  do_test pragma-6.5.2 {
000773    execsql {
000774      pragma index_info(t3i1_bogus);
000775    }
000776  } {}
000777  
000778  ifcapable tempdb {
000779    # Test for ticket #3320. When a temp table of the same name exists, make
000780    # sure the schema of the main table can still be queried using 
000781    # "pragma table_info":
000782    do_test pragma-6.6.1 {
000783      execsql {
000784        CREATE TABLE trial(col_main);
000785        CREATE TEMP TABLE trial(col_temp);
000786      }
000787    } {}
000788    do_test pragma-6.6.2 {
000789      execsql {
000790        PRAGMA table_info(trial);
000791      }
000792    } {0 col_temp {} 0 {} 0}
000793    do_test pragma-6.6.3 {
000794      execsql {
000795        PRAGMA temp.table_info(trial);
000796      }
000797    } {0 col_temp {} 0 {} 0}
000798    do_test pragma-6.6.4 {
000799      execsql {
000800        PRAGMA main.table_info(trial);
000801      }
000802    } {0 col_main {} 0 {} 0}
000803  }
000804  
000805  do_test pragma-6.7 {
000806    execsql {
000807      CREATE TABLE test_table(
000808        one INT NOT NULL DEFAULT -1, 
000809        two text,
000810        three VARCHAR(45, 65) DEFAULT 'abcde',
000811        four REAL DEFAULT X'abcdef',
000812        five DEFAULT CURRENT_TIME
000813      );
000814    }
000815    capture_pragma db out {PRAGMA table_info(test_table)}
000816    db eval {SELECT cid, "name", type, "notnull", dflt_value, pk FROM out
000817              ORDER BY cid}
000818  } [concat \
000819    {0 one INT 1 -1 0} \
000820    {1 two TEXT 0 {} 0} \
000821    {2 three {VARCHAR(45, 65)} 0 'abcde' 0} \
000822    {3 four REAL 0 X'abcdef' 0} \
000823    {4 five {} 0 CURRENT_TIME 0} \
000824  ]
000825  do_test pragma-6.8 {
000826    execsql {
000827      CREATE TABLE t68(a,b,c,PRIMARY KEY(a,b,a,c));
000828      PRAGMA table_info(t68);
000829    }
000830  } [concat \
000831    {0 a {} 0 {} 1} \
000832    {1 b {} 0 {} 2} \
000833    {2 c {} 0 {} 4} \
000834  ]
000835  } ;# ifcapable schema_pragmas
000836  # Miscellaneous tests
000837  #
000838  ifcapable schema_pragmas {
000839  # EVIDENCE-OF: R-64103-17776 PRAGMA schema.index_list(table-name); This
000840  # pragma returns one row for each index associated with the given table.
000841  #
000842  do_test pragma-7.1.1 {
000843    # Make sure a pragma knows to read the schema if it needs to
000844    db close
000845    sqlite3 db test.db
000846    capture_pragma db out "PRAGMA index_list(t3)"
000847    db eval {SELECT name, "origin" FROM out ORDER BY name DESC}
000848  } {t3i1 c sqlite_autoindex_t3_1 u}
000849  do_test pragma-7.1.2 {
000850    execsql {
000851      pragma index_list(t3_bogus);
000852    }
000853  } {}
000854  } ;# ifcapable schema_pragmas
000855  ifcapable {utf16} {
000856    if {[permutation] == ""} {
000857      do_test pragma-7.2 {
000858        db close
000859        sqlite3 db test.db
000860        catchsql {
000861          pragma encoding=bogus;
000862        }
000863      } {1 {unsupported encoding: bogus}}
000864    }
000865  }
000866  ifcapable tempdb {
000867    do_test pragma-7.3 {
000868      db close
000869      sqlite3 db test.db
000870      execsql {
000871        pragma lock_status;
000872      }
000873    } {main unlocked temp closed}
000874  } else {
000875    do_test pragma-7.3 {
000876      db close
000877      sqlite3 db test.db
000878      execsql {
000879        pragma lock_status;
000880      }
000881    } {main unlocked}
000882  }
000883  
000884  
000885  #----------------------------------------------------------------------
000886  # Test cases pragma-8.* test the "PRAGMA schema_version" and "PRAGMA
000887  # user_version" statements.
000888  #
000889  # pragma-8.1: PRAGMA schema_version
000890  # pragma-8.2: PRAGMA user_version
000891  #
000892  
000893  ifcapable schema_version {
000894  
000895  # First check that we can set the schema version and then retrieve the
000896  # same value.
000897  do_test pragma-8.1.1 {
000898    execsql {
000899      PRAGMA schema_version = 105;
000900    }
000901  } {}
000902  do_test pragma-8.1.2 {
000903    execsql2 {
000904      PRAGMA schema_version;
000905    }
000906  } {schema_version 105}
000907  do_test pragma-8.1.3 {
000908    execsql {
000909      PRAGMA schema_version = 106;
000910    }
000911  } {}
000912  do_test pragma-8.1.4 {
000913    execsql {
000914      PRAGMA schema_version;
000915    }
000916  } 106
000917  
000918  # Check that creating a table modifies the schema-version (this is really
000919  # to verify that the value being read is in fact the schema version).
000920  do_test pragma-8.1.5 {
000921    execsql {
000922      CREATE TABLE t4(a, b, c);
000923      INSERT INTO t4 VALUES(1, 2, 3);
000924      SELECT * FROM t4;
000925    }
000926  } {1 2 3}
000927  do_test pragma-8.1.6 {
000928    execsql {
000929      PRAGMA schema_version;
000930    }
000931  } 107
000932  
000933  # Now open a second connection to the database. Ensure that changing the
000934  # schema-version using the first connection forces the second connection
000935  # to reload the schema. This has to be done using the C-API test functions,
000936  # because the TCL API accounts for SCHEMA_ERROR and retries the query.
000937  do_test pragma-8.1.7 {
000938    sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]
000939    execsql {
000940      SELECT * FROM t4;
000941    } db2
000942  } {1 2 3}
000943  do_test pragma-8.1.8 {
000944    execsql {
000945      PRAGMA schema_version = 108;
000946    }
000947  } {}
000948  do_test pragma-8.1.9 {
000949    set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM t4" -1 DUMMY]
000950    sqlite3_step $::STMT
000951  } SQLITE_ERROR
000952  do_test pragma-8.1.10 {
000953    sqlite3_finalize $::STMT
000954  } SQLITE_SCHEMA
000955  
000956  # Make sure the schema-version can be manipulated in an attached database.
000957  forcedelete test2.db
000958  forcedelete test2.db-journal
000959  ifcapable attach {
000960    do_test pragma-8.1.11 {
000961      execsql {
000962        ATTACH 'test2.db' AS aux;
000963        CREATE TABLE aux.t1(a, b, c);
000964        PRAGMA aux.schema_version = 205;
000965      }
000966    } {}
000967    do_test pragma-8.1.12 {
000968      execsql {
000969        PRAGMA aux.schema_version;
000970      }
000971    } 205
000972  }
000973  do_test pragma-8.1.13 {
000974    execsql {
000975      PRAGMA schema_version;
000976    }
000977  } 108
000978  
000979  # And check that modifying the schema-version in an attached database
000980  # forces the second connection to reload the schema.
000981  ifcapable attach {
000982    do_test pragma-8.1.14 {
000983      sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]
000984      execsql {
000985        ATTACH 'test2.db' AS aux;
000986        SELECT * FROM aux.t1;
000987      } db2
000988    } {}
000989    do_test pragma-8.1.15 {
000990      execsql {
000991        PRAGMA aux.schema_version = 206;
000992      }
000993    } {}
000994    do_test pragma-8.1.16 {
000995      set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM aux.t1" -1 DUMMY]
000996      sqlite3_step $::STMT
000997    } SQLITE_ERROR
000998    do_test pragma-8.1.17 {
000999      sqlite3_finalize $::STMT
001000    } SQLITE_SCHEMA
001001    do_test pragma-8.1.18 {
001002      db2 close
001003    } {}
001004  }
001005  
001006  # Now test that the user-version can be read and written (and that we aren't
001007  # accidentally manipulating the schema-version instead).
001008  do_test pragma-8.2.1 {
001009    execsql2 {
001010      PRAGMA user_version;
001011    }
001012  } {user_version 0}
001013  do_test pragma-8.2.2 {
001014    execsql {
001015      PRAGMA user_version = 2;
001016    }
001017  } {}
001018  do_test pragma-8.2.3.1 {
001019    execsql2 {
001020      PRAGMA user_version;
001021    }
001022  } {user_version 2}
001023  do_test pragma-8.2.3.2 {
001024    db close
001025    sqlite3 db test.db
001026    execsql {
001027      PRAGMA user_version;
001028    }
001029  } {2}
001030  do_test pragma-8.2.4.1 {
001031    execsql {
001032      PRAGMA schema_version;
001033    }
001034  } {108}
001035  ifcapable vacuum {
001036    do_test pragma-8.2.4.2 {
001037      execsql {
001038        VACUUM;
001039        PRAGMA user_version;
001040      }
001041    } {2}
001042    do_test pragma-8.2.4.3 {
001043      execsql {
001044        PRAGMA schema_version;
001045      }
001046    } {109}
001047  }
001048  
001049  ifcapable attach {
001050    db eval {ATTACH 'test2.db' AS aux}
001051    
001052    # Check that the user-version in the auxilary database can be manipulated (
001053    # and that we aren't accidentally manipulating the same in the main db).
001054    do_test pragma-8.2.5 {
001055      execsql {
001056        PRAGMA aux.user_version;
001057      }
001058    } {0}
001059    do_test pragma-8.2.6 {
001060      execsql {
001061        PRAGMA aux.user_version = 3;
001062      }
001063    } {}
001064    do_test pragma-8.2.7 {
001065      execsql {
001066        PRAGMA aux.user_version;
001067      }
001068    } {3}
001069    do_test pragma-8.2.8 {
001070      execsql {
001071        PRAGMA main.user_version;
001072      }
001073    } {2}
001074    
001075    # Now check that a ROLLBACK resets the user-version if it has been modified
001076    # within a transaction.
001077    do_test pragma-8.2.9 {
001078      execsql {
001079        BEGIN;
001080        PRAGMA aux.user_version = 10;
001081        PRAGMA user_version = 11;
001082      }
001083    } {}
001084    do_test pragma-8.2.10 {
001085      execsql {
001086        PRAGMA aux.user_version;
001087      }
001088    } {10}
001089    do_test pragma-8.2.11 {
001090      execsql {
001091        PRAGMA main.user_version;
001092      }
001093    } {11}
001094    do_test pragma-8.2.12 {
001095      execsql {
001096        ROLLBACK;
001097        PRAGMA aux.user_version;
001098      }
001099    } {3}
001100    do_test pragma-8.2.13 {
001101      execsql {
001102        PRAGMA main.user_version;
001103      }
001104    } {2}
001105  }
001106  
001107  # Try a negative value for the user-version
001108  do_test pragma-8.2.14 {
001109    execsql {
001110      PRAGMA user_version = -450;
001111    }
001112  } {}
001113  do_test pragma-8.2.15 {
001114    execsql {
001115      PRAGMA user_version;
001116    }
001117  } {-450}
001118  } ; # ifcapable schema_version
001119  
001120  # Check to see if TEMP_STORE is memory or disk.  Return strings
001121  # "memory" or "disk" as appropriate.
001122  #
001123  proc check_temp_store {} {
001124    db eval {
001125      PRAGMA temp.cache_size = 1;
001126      CREATE TEMP TABLE IF NOT EXISTS a(b);
001127      DELETE FROM a;
001128      INSERT INTO a VALUES(randomblob(1000));
001129      INSERT INTO a SELECT * FROM a;
001130      INSERT INTO a SELECT * FROM a;
001131      INSERT INTO a SELECT * FROM a;
001132      INSERT INTO a SELECT * FROM a;
001133      INSERT INTO a SELECT * FROM a;
001134      INSERT INTO a SELECT * FROM a;
001135      INSERT INTO a SELECT * FROM a;
001136      INSERT INTO a SELECT * FROM a;
001137    }
001138    db eval {PRAGMA database_list} {
001139      if {$name=="temp"} {
001140        set bt [btree_from_db db 1]
001141        if {[btree_ismemdb $bt]} {
001142          return "memory"
001143        }
001144        return "disk"
001145      }
001146    }
001147    return "unknown"
001148  }
001149  
001150  # Application_ID
001151  #
001152  do_test pragma-8.3.1 {
001153    execsql {
001154      PRAGMA application_id;
001155    }
001156  } {0}
001157  do_test pragma-8.3.2 {
001158    execsql {PRAGMA Application_ID(12345); PRAGMA application_id;}
001159  } {12345}
001160  
001161  # Test temp_store and temp_store_directory pragmas
001162  #
001163  ifcapable pager_pragmas {
001164  do_test pragma-9.1 {
001165    db close
001166    sqlite3 db test.db
001167    execsql {
001168      PRAGMA temp_store;
001169    }
001170  } {0}
001171  if {$TEMP_STORE<=1} {
001172    do_test pragma-9.1.1 {
001173      check_temp_store
001174    } {disk}
001175  } else {
001176    do_test pragma-9.1.1 {
001177      check_temp_store
001178    } {memory}
001179  }
001180  
001181  do_test pragma-9.2 {
001182    db close
001183    sqlite3 db test.db
001184    execsql {
001185      PRAGMA temp_store=file;
001186      PRAGMA temp_store;
001187    }
001188  } {1}
001189  if {$TEMP_STORE==3} {
001190    # When TEMP_STORE is 3, always use memory regardless of pragma settings.
001191    do_test pragma-9.2.1 {
001192      check_temp_store
001193    } {memory}
001194  } else {
001195    do_test pragma-9.2.1 {
001196      check_temp_store
001197    } {disk}
001198  }
001199  
001200  do_test pragma-9.3 {
001201    db close
001202    sqlite3 db test.db
001203    execsql {
001204      PRAGMA temp_store=memory;
001205      PRAGMA temp_store;
001206    }
001207  } {2}
001208  if {$TEMP_STORE==0} {
001209    # When TEMP_STORE is 0, always use the disk regardless of pragma settings.
001210    do_test pragma-9.3.1 {
001211      check_temp_store
001212    } {disk}
001213  } else {
001214    do_test pragma-9.3.1 {
001215      check_temp_store
001216    } {memory}
001217  }
001218  
001219  do_test pragma-9.4 {
001220    execsql {
001221      PRAGMA temp_store_directory;
001222    }
001223  } {}
001224  ifcapable wsd {
001225    do_test pragma-9.5 {
001226      set pwd [string map {' ''} [file nativename [get_pwd]]]
001227      execsql "
001228        PRAGMA temp_store_directory='$pwd';
001229      "
001230    } {}
001231    do_test pragma-9.6 {
001232      execsql { 
001233        PRAGMA temp_store_directory;
001234      }
001235    } [list [file nativename [get_pwd]]]
001236    do_test pragma-9.7 {
001237      catchsql { 
001238        PRAGMA temp_store_directory='/NON/EXISTENT/PATH/FOOBAR';
001239      }
001240    } {1 {not a writable directory}}
001241    do_test pragma-9.8 {
001242      execsql { 
001243        PRAGMA temp_store_directory='';
001244      }
001245    } {}
001246    if {![info exists TEMP_STORE] || $TEMP_STORE<=1} {
001247      ifcapable tempdb {
001248        do_test pragma-9.9 {
001249          execsql { 
001250            PRAGMA temp_store_directory;
001251            PRAGMA temp_store=FILE;
001252            CREATE TEMP TABLE temp_store_directory_test(a integer);
001253            INSERT INTO temp_store_directory_test values (2);
001254            SELECT * FROM temp_store_directory_test;
001255          }
001256        } {2}
001257        do_test pragma-9.10 {
001258          catchsql "
001259            PRAGMA temp_store_directory='$pwd';
001260            SELECT * FROM temp_store_directory_test;
001261          "
001262        } {1 {no such table: temp_store_directory_test}}
001263      }
001264    }
001265  }
001266  do_test pragma-9.11 {
001267    execsql {
001268      PRAGMA temp_store = 0;
001269      PRAGMA temp_store;
001270    }
001271  } {0}
001272  do_test pragma-9.12 {
001273    execsql {
001274      PRAGMA temp_store = 1;
001275      PRAGMA temp_store;
001276    }
001277  } {1}
001278  do_test pragma-9.13 {
001279    execsql {
001280      PRAGMA temp_store = 2;
001281      PRAGMA temp_store;
001282    }
001283  } {2}
001284  do_test pragma-9.14 {
001285    execsql {
001286      PRAGMA temp_store = 3;
001287      PRAGMA temp_store;
001288    }
001289  } {0}
001290  do_test pragma-9.15 {
001291    catchsql {
001292      BEGIN EXCLUSIVE;
001293      CREATE TEMP TABLE temp_table(t);
001294      INSERT INTO temp_table VALUES('valuable data');
001295      PRAGMA temp_store = 1;
001296    }
001297  } {1 {temporary storage cannot be changed from within a transaction}}
001298  do_test pragma-9.16 {
001299    execsql {
001300      SELECT * FROM temp_table;
001301      COMMIT;
001302    }
001303  } {{valuable data}}
001304  
001305  do_test pragma-9.17 {
001306    execsql {
001307      INSERT INTO temp_table VALUES('valuable data II');
001308      SELECT * FROM temp_table;
001309    }
001310  } {{valuable data} {valuable data II}}
001311  
001312  do_test pragma-9.18 {
001313    set rc [catch {
001314      db eval {SELECT t FROM temp_table} {
001315        execsql {pragma temp_store = 1}
001316      }
001317    } msg]
001318    list $rc $msg
001319  } {1 {temporary storage cannot be changed from within a transaction}}
001320  
001321  } ;# ifcapable pager_pragmas
001322  
001323  ifcapable trigger {
001324  
001325  do_test pragma-10.0 {
001326    catchsql {
001327      DROP TABLE main.t1;
001328    }
001329    execsql {
001330      PRAGMA count_changes = 1;
001331  
001332      CREATE TABLE t1(a PRIMARY KEY);
001333      CREATE TABLE t1_mirror(a);
001334      CREATE TABLE t1_mirror2(a);
001335      CREATE TRIGGER t1_bi BEFORE INSERT ON t1 BEGIN 
001336        INSERT INTO t1_mirror VALUES(new.a);
001337      END;
001338      CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN 
001339        INSERT INTO t1_mirror2 VALUES(new.a);
001340      END;
001341      CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN 
001342        UPDATE t1_mirror SET a = new.a WHERE a = old.a;
001343      END;
001344      CREATE TRIGGER t1_au AFTER UPDATE ON t1 BEGIN 
001345        UPDATE t1_mirror2 SET a = new.a WHERE a = old.a;
001346      END;
001347      CREATE TRIGGER t1_bd BEFORE DELETE ON t1 BEGIN 
001348        DELETE FROM t1_mirror WHERE a = old.a;
001349      END;
001350      CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN 
001351        DELETE FROM t1_mirror2 WHERE a = old.a;
001352      END;
001353    }
001354  } {}
001355  
001356  do_test pragma-10.1 {
001357    execsql {
001358      INSERT INTO t1 VALUES(randstr(10,10));
001359    }
001360  } {1}
001361  do_test pragma-10.2 {
001362    execsql {
001363      UPDATE t1 SET a = randstr(10,10);
001364    }
001365  } {1}
001366  do_test pragma-10.3 {
001367    execsql {
001368      DELETE FROM t1;
001369    }
001370  } {1}
001371  
001372  } ;# ifcapable trigger
001373  
001374  ifcapable schema_pragmas {
001375    do_test pragma-11.1 {
001376      execsql2 {
001377        pragma collation_list;
001378      }
001379    } {seq 0 name RTRIM seq 1 name NOCASE seq 2 name BINARY}
001380    do_test pragma-11.2 {
001381      db collate New_Collation blah...
001382      execsql {
001383        pragma collation_list;
001384      }
001385    } {0 New_Collation 1 RTRIM 2 NOCASE 3 BINARY}
001386  }
001387  
001388  ifcapable schema_pragmas&&tempdb {
001389    do_test pragma-12.1 {
001390      sqlite3 db2 test.db
001391      execsql {
001392        PRAGMA temp.table_info('abc');
001393      } db2
001394    } {}
001395    db2 close
001396  
001397    do_test pragma-12.2 {
001398      sqlite3 db2 test.db
001399      execsql {
001400        PRAGMA temp.default_cache_size = 200;
001401        PRAGMA temp.default_cache_size;
001402      } db2
001403    } {200}
001404    db2 close
001405  
001406    do_test pragma-12.3 {
001407      sqlite3 db2 test.db
001408      execsql {
001409        PRAGMA temp.cache_size = 400;
001410        PRAGMA temp.cache_size;
001411      } db2
001412    } {400}
001413    db2 close
001414  }
001415  
001416  ifcapable bloblit {
001417  
001418  do_test pragma-13.1 {
001419    execsql {
001420      DROP TABLE IF EXISTS t4;
001421      PRAGMA vdbe_trace=on;
001422      PRAGMA vdbe_listing=on;
001423      PRAGMA sql_trace=on;
001424      CREATE TABLE t4(a INTEGER PRIMARY KEY,b);
001425      INSERT INTO t4(b) VALUES(x'0123456789abcdef0123456789abcdef0123456789');
001426      INSERT INTO t4(b) VALUES(randstr(30,30));
001427      INSERT INTO t4(b) VALUES(1.23456);
001428      INSERT INTO t4(b) VALUES(NULL);
001429      INSERT INTO t4(b) VALUES(0);
001430      INSERT INTO t4(b) SELECT b||b||b||b FROM t4;
001431      SELECT * FROM t4;
001432    }
001433    execsql {
001434      PRAGMA vdbe_trace=off;
001435      PRAGMA vdbe_listing=off;
001436      PRAGMA sql_trace=off;
001437    }
001438  } {}
001439  
001440  } ;# ifcapable bloblit 
001441  
001442  ifcapable pager_pragmas {
001443    db close
001444    forcedelete test.db
001445    sqlite3 db test.db
001446   
001447    # EVIDENCE-OF: R-15672-33611 PRAGMA schema.page_count; Return the total
001448    # number of pages in the database file.
001449    #
001450    do_test pragma-14.1 {
001451      execsql { pragma auto_vacuum = 0 }
001452      execsql { pragma page_count; pragma main.page_count }
001453    } {0 0}
001454  
001455    do_test pragma-14.2 {
001456      execsql { 
001457        CREATE TABLE abc(a, b, c);
001458        PRAGMA page_count;
001459        PRAGMA main.page_count;
001460        PRAGMA temp.page_count;
001461      }
001462    } {2 2 0}
001463    do_test pragma-14.2uc {
001464      execsql {pragma PAGE_COUNT}
001465    } {2}
001466  
001467    do_test pragma-14.3 {
001468      execsql { 
001469        BEGIN;
001470        CREATE TABLE def(a, b, c);
001471        PRAGMA page_count;
001472      }
001473    } {3}
001474    do_test pragma-14.3uc {
001475      execsql {pragma PAGE_COUNT}
001476    } {3}
001477  
001478    do_test pragma-14.4 {
001479      set page_size [db one {pragma page_size}]
001480      expr [file size test.db] / $page_size
001481    } {2}
001482  
001483    do_test pragma-14.5 {
001484      execsql {
001485        ROLLBACK;
001486        PRAGMA page_count;
001487      }
001488    } {2}
001489  
001490    do_test pragma-14.6 {
001491      forcedelete test2.db
001492      sqlite3 db2 test2.db
001493      execsql {
001494        PRAGMA auto_vacuum = 0;
001495        CREATE TABLE t1(a, b, c);
001496        CREATE TABLE t2(a, b, c);
001497        CREATE TABLE t3(a, b, c);
001498        CREATE TABLE t4(a, b, c);
001499      } db2
001500      db2 close
001501      execsql {
001502        ATTACH 'test2.db' AS aux;
001503        PRAGMA aux.page_count;
001504      } 
001505    } {5}
001506    do_test pragma-14.6uc {
001507      execsql {pragma AUX.PAGE_COUNT}
001508    } {5}
001509  }
001510  
001511  # Test that the value set using the cache_size pragma is not reset when the
001512  # schema is reloaded.
001513  #
001514  ifcapable pager_pragmas {
001515    db close
001516    sqlite3 db test.db
001517    do_test pragma-15.1 {
001518      execsql {
001519        PRAGMA cache_size=59;
001520        PRAGMA cache_size;
001521      }
001522    } {59}
001523    do_test pragma-15.2 {
001524      sqlite3 db2 test.db
001525      execsql {
001526        CREATE TABLE newtable(a, b, c);
001527      } db2
001528      db2 close
001529    } {}
001530    do_test pragma-15.3 {
001531      # Evaluating this statement will cause the schema to be reloaded (because
001532      # the schema was changed by another connection in pragma-15.2). At one
001533      # point there was a bug that reset the cache_size to its default value
001534      # when this happened. 
001535      execsql { SELECT * FROM sqlite_master }
001536      execsql { PRAGMA cache_size }
001537    } {59}
001538  }
001539  
001540  # Reset the sqlite3_temp_directory variable for the next run of tests:
001541  sqlite3 dbX :memory:
001542  dbX eval {PRAGMA temp_store_directory = ""}
001543  dbX close
001544  
001545  ifcapable lock_proxy_pragmas&&prefer_proxy_locking {
001546    set sqlite_hostid_num 1
001547  
001548    set using_proxy 0
001549    foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] {
001550      set using_proxy $value
001551    }
001552  
001553    # Test the lock_proxy_file pragmas.
001554    #
001555    db close
001556    set env(SQLITE_FORCE_PROXY_LOCKING) "0"
001557  
001558    sqlite3 db test.db
001559    do_test pragma-16.1 {
001560      execsql {
001561        PRAGMA lock_proxy_file="mylittleproxy";
001562        select * from sqlite_master;
001563      }
001564      execsql {
001565        PRAGMA lock_proxy_file;
001566      } 
001567    } {mylittleproxy}
001568  
001569    do_test pragma-16.2 {
001570      sqlite3 db2 test.db
001571      execsql {
001572        PRAGMA lock_proxy_file="mylittleproxy";
001573      } db2
001574    } {}
001575  
001576    db2 close
001577    do_test pragma-16.2.1 {
001578      sqlite3 db2 test.db
001579      execsql {
001580        PRAGMA lock_proxy_file=":auto:";
001581        select * from sqlite_master;
001582      } db2
001583      execsql {
001584        PRAGMA lock_proxy_file;
001585      } db2
001586    } {mylittleproxy}
001587  
001588    db2 close
001589    do_test pragma-16.3 {
001590      sqlite3 db2 test.db
001591      execsql {
001592        PRAGMA lock_proxy_file="myotherproxy";
001593      } db2
001594      catchsql {
001595        select * from sqlite_master;
001596      } db2
001597    } {1 {database is locked}}
001598  
001599    do_test pragma-16.4 {
001600      db2 close
001601      db close
001602      sqlite3 db2 test.db
001603      execsql {
001604        PRAGMA lock_proxy_file="myoriginalproxy";
001605        PRAGMA lock_proxy_file="myotherproxy";
001606        PRAGMA lock_proxy_file;
001607      } db2
001608    } {myotherproxy}
001609  
001610    db2 close
001611    set env(SQLITE_FORCE_PROXY_LOCKING) "1"
001612    do_test pragma-16.5 {
001613      sqlite3 db2 test.db
001614      execsql {
001615        PRAGMA lock_proxy_file=":auto:";
001616        PRAGMA lock_proxy_file;
001617      } db2
001618    } {myotherproxy}
001619    
001620    do_test pragma-16.6 {
001621      db2 close
001622      sqlite3 db2 test2.db
001623      set lockpath [execsql {
001624        PRAGMA lock_proxy_file=":auto:";
001625        PRAGMA lock_proxy_file;
001626      } db2]
001627      string match "*test2.db:auto:" $lockpath
001628    } {1}
001629    
001630    set sqlite_hostid_num 2
001631    do_test pragma-16.7 {
001632      list [catch {
001633        sqlite3 db test2.db
001634        execsql { 
001635          PRAGMA lock_proxy_file=":auto:";
001636          select * from sqlite_master;
001637        }
001638      } msg] $msg
001639    } {1 {database is locked}}
001640    db close
001641    
001642    do_test pragma-16.8 {
001643      list [catch {
001644        sqlite3 db test2.db
001645        execsql { select * from sqlite_master } 
001646      } msg] $msg
001647    } {1 {database is locked}}
001648  
001649    db2 close
001650    do_test pragma-16.8.1 {
001651      execsql {
001652        PRAGMA lock_proxy_file="yetanotherproxy";
001653        PRAGMA lock_proxy_file;
001654      } 
001655    } {yetanotherproxy}
001656    do_test pragma-16.8.2 {
001657      execsql {
001658        create table mine(x);
001659      } 
001660    } {}
001661  
001662    db close
001663    do_test pragma-16.9 {
001664      sqlite3 db proxytest.db
001665      set lockpath2 [execsql {
001666        PRAGMA lock_proxy_file=":auto:";
001667        PRAGMA lock_proxy_file;
001668      } db]
001669      string match "*proxytest.db:auto:" $lockpath2
001670    } {1}
001671  
001672    set env(SQLITE_FORCE_PROXY_LOCKING) $using_proxy
001673    set sqlite_hostid_num 0
001674  }
001675  
001676  # Parsing of auto_vacuum settings.
001677  #
001678  foreach {autovac_setting val} {
001679    0 0
001680    1 1
001681    2 2
001682    3 0
001683    -1 0
001684    none 0
001685    NONE 0
001686    NoNe 0
001687    full 1
001688    FULL 1
001689    incremental 2
001690    INCREMENTAL 2
001691    -1234 0
001692    1234 0
001693  } {
001694    do_test pragma-17.1.$autovac_setting {
001695      catch {db close}
001696      sqlite3 db :memory:
001697      execsql "
001698        PRAGMA auto_vacuum=$::autovac_setting;
001699        PRAGMA auto_vacuum;
001700      "
001701    } $val
001702  }
001703  
001704  # Parsing of temp_store settings.
001705  #
001706  foreach {temp_setting val} {
001707    0 0
001708    1 1
001709    2 2
001710    3 0
001711    -1 0
001712    file 1
001713    FILE 1
001714    fIlE 1
001715    memory 2
001716    MEMORY 2
001717    MeMoRy 2
001718  } {
001719    do_test pragma-18.1.$temp_setting {
001720      catch {db close}
001721      sqlite3 db :memory:
001722      execsql "
001723        PRAGMA temp_store=$::temp_setting;
001724        PRAGMA temp_store=$::temp_setting;
001725        PRAGMA temp_store;
001726      "
001727    } $val
001728  }
001729  
001730  # The SQLITE_FCNTL_PRAGMA logic, with error handling.
001731  #
001732  db close
001733  testvfs tvfs
001734  sqlite3 db test.db -vfs tvfs
001735  do_test pragma-19.1 {
001736    catchsql {PRAGMA error}
001737  } {1 {SQL logic error}}
001738  do_test pragma-19.2 {
001739    catchsql {PRAGMA error='This is the error message'}
001740  } {1 {This is the error message}}
001741  do_test pragma-19.3 {
001742    catchsql {PRAGMA error='7 This is the error message'}
001743  } {1 {This is the error message}}
001744  do_test pragma-19.4 {
001745    catchsql {PRAGMA error=7}
001746  } {1 {out of memory}}
001747  do_test pragma-19.5 {
001748    file tail [lindex [execsql {PRAGMA filename}] 0]
001749  } {test.db}
001750  
001751  if {$tcl_platform(platform)=="windows"} {
001752  # Test data_store_directory pragma
001753  #
001754  db close
001755  sqlite3 db test.db
001756  file mkdir data_dir
001757  do_test pragma-20.1 {
001758    catchsql {PRAGMA data_store_directory}
001759  } {0 {}}
001760  do_test pragma-20.2 {
001761    set pwd [string map {' ''} [file nativename [get_pwd]]]
001762    catchsql "PRAGMA data_store_directory='$pwd';"
001763  } {0 {}}
001764  do_test pragma-20.3 {
001765    catchsql {PRAGMA data_store_directory}
001766  } [list 0 [list [file nativename [get_pwd]]]]
001767  do_test pragma-20.4 {
001768    set pwd [string map {' ''} [file nativename \
001769      [file join [get_pwd] data_dir]]]
001770    catchsql "PRAGMA data_store_directory='$pwd';"
001771  } {0 {}}
001772  do_test pragma-20.5 {
001773    sqlite3 db2 test2.db
001774    catchsql "PRAGMA database_list;" db2
001775  } [list 0 [list 0 main [file nativename \
001776      [file join [get_pwd] data_dir test2.db]]]]
001777  catch {db2 close}
001778  do_test pragma-20.6 {
001779    sqlite3 db2 [file join [get_pwd] test2.db]
001780    catchsql "PRAGMA database_list;" db2
001781  } [list 0 [list 0 main [file nativename \
001782      [file join [get_pwd] test2.db]]]]
001783  catch {db2 close}
001784  do_test pragma-20.7 {
001785    catchsql "PRAGMA data_store_directory='';"
001786  } {0 {}}
001787  do_test pragma-20.8 {
001788    catchsql {PRAGMA data_store_directory}
001789  } {0 {}}
001790  
001791  forcedelete data_dir
001792  } ;# endif windows
001793  
001794  database_may_be_corrupt
001795  if {![nonzero_reserved_bytes]} {
001796  
001797    do_test 21.1 {
001798      # Create a corrupt database in testerr.db. And a non-corrupt at test.db.
001799      #
001800      db close
001801      forcedelete test.db
001802      sqlite3 db test.db
001803      execsql { 
001804        PRAGMA page_size = 1024;
001805        PRAGMA auto_vacuum = 0;
001806        CREATE TABLE t1(a PRIMARY KEY, b);
001807        INSERT INTO t1 VALUES(1, 1);
001808      }
001809      for {set i 0} {$i < 10} {incr i} {
001810        execsql { INSERT INTO t1 SELECT a + (1 << $i), b + (1 << $i) FROM t1 }
001811      }
001812      db close
001813      forcecopy test.db testerr.db
001814      hexio_write testerr.db 15000 [string repeat 55 100]
001815    } {100}
001816    
001817    set mainerr {*** in database main ***
001818  Multiple uses for byte 672 of page 15}
001819    set auxerr {*** in database aux ***
001820  Multiple uses for byte 672 of page 15}
001821    
001822    set mainerr {/{\*\*\* in database main \*\*\*
001823  Multiple uses for byte 672 of page 15}.*/}
001824    set auxerr {/{\*\*\* in database aux \*\*\*
001825  Multiple uses for byte 672 of page 15}.*/}
001826    
001827    do_test 22.2 {
001828      catch { db close }
001829      sqlite3 db testerr.db
001830      execsql { PRAGMA integrity_check }
001831    } $mainerr
001832    
001833    do_test 22.3.1 {
001834      catch { db close }
001835      sqlite3 db test.db
001836      execsql { 
001837        ATTACH 'testerr.db' AS 'aux';
001838        PRAGMA integrity_check;
001839      }
001840    } $auxerr
001841    do_test 22.3.2 {
001842      execsql { PRAGMA main.integrity_check; }
001843    } {ok}
001844    do_test 22.3.3 {
001845      execsql { PRAGMA aux.integrity_check; }
001846    } $auxerr
001847    
001848    do_test 22.4.1 {
001849      catch { db close }
001850      sqlite3 db testerr.db
001851      execsql { 
001852        ATTACH 'test.db' AS 'aux';
001853        PRAGMA integrity_check;
001854      }
001855    } $mainerr
001856    do_test 22.4.2 {
001857      execsql { PRAGMA main.integrity_check; }
001858    } $mainerr
001859    do_test 22.4.3 {
001860      execsql { PRAGMA aux.integrity_check; }
001861    } {ok}
001862  }
001863    
001864  db close
001865  forcedelete test.db test.db-wal test.db-journal
001866  sqlite3 db test.db
001867  sqlite3 db2 test.db
001868  do_test 23.1 {
001869    db eval {
001870      CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d);
001871      CREATE INDEX i1 ON t1(b,c);
001872      CREATE INDEX i2 ON t1(c,d);
001873      CREATE INDEX i2x ON t1(d COLLATE nocase, c DESC);
001874      CREATE INDEX i3 ON t1(d,b+c,c);
001875      CREATE TABLE t2(x INTEGER REFERENCES t1);
001876    }
001877    db2 eval {SELECT name FROM sqlite_master}
001878  } {t1 i1 i2 i2x i3 t2}
001879  do_test 23.2a {
001880    db eval {
001881      DROP INDEX i2;
001882      CREATE INDEX i2 ON t1(c,d,b);
001883    }
001884    capture_pragma db2 out {PRAGMA index_info(i2)}
001885    db2 eval {SELECT cid, name, '|' FROM out ORDER BY seqno}
001886  } {2 c | 3 d | 1 b |}
001887  
001888  # EVIDENCE-OF: R-56143-29319 PRAGMA schema.index_xinfo(index-name); This
001889  # pragma returns information about every column in an index.
001890  #
001891  # EVIDENCE-OF: R-45970-35618 Unlike this index_info pragma, this pragma
001892  # returns information about every column in the index, not just the key
001893  # columns.
001894  #
001895  do_test 23.2b {
001896    capture_pragma db2 out {PRAGMA index_xinfo(i2)}
001897    db2 eval {SELECT cid, name, "desc", coll, "key", '|' FROM out ORDER BY seqno}
001898  } {2 c 0 BINARY 1 | 3 d 0 BINARY 1 | 1 b 0 BINARY 1 | -1 {} 0 BINARY 0 |}
001899  
001900  # (The first column of output from PRAGMA index_xinfo is...)
001901  # EVIDENCE-OF: R-00197-14279 The rank of the column within the index. (0
001902  # means left-most. Key columns come before auxiliary columns.)
001903  #
001904  # (The second column of output from PRAGMA index_xinfo is...)
001905  # EVIDENCE-OF: R-06603-49335 The rank of the column within the table
001906  # being indexed, or -1 if the index-column is the rowid of the table
001907  # being indexed and -2 if the index is on an expression.
001908  #
001909  # (The third column of output from PRAGMA index_xinfo is...)
001910  # EVIDENCE-OF: R-40641-22898 The name of the column being indexed, or
001911  # NULL if the index-column is the rowid of the table being indexed or an
001912  # expression.
001913  #
001914  # (The fourth column of output from PRAGMA index_xinfo is...)
001915  # EVIDENCE-OF: R-11847-09179 1 if the index-column is sorted in reverse
001916  # (DESC) order by the index and 0 otherwise.
001917  #
001918  # (The fifth column of output from PRAGMA index_xinfo is...)
001919  # EVIDENCE-OF: R-15313-19540 The name for the collating sequence used to
001920  # compare values in the index-column.
001921  #
001922  # (The sixth column of output from PRAGMA index_xinfo is...)
001923  # EVIDENCE-OF: R-14310-64553 1 if the index-column is a key column and 0
001924  # if the index-column is an auxiliary column.
001925  #
001926  do_test 23.2c {
001927    db2 eval {PRAGMA index_xinfo(i2)}
001928  } {0 2 c 0 BINARY 1 1 3 d 0 BINARY 1 2 1 b 0 BINARY 1 3 -1 {} 0 BINARY 0}
001929  do_test 23.2d {
001930    db2 eval {PRAGMA index_xinfo(i2x)}
001931  } {0 3 d 0 nocase 1 1 2 c 1 BINARY 1 2 -1 {} 0 BINARY 0}
001932  do_test 23.2e {
001933    db2 eval {PRAGMA index_xinfo(i3)}
001934  } {0 3 d 0 BINARY 1 1 -2 {} 0 BINARY 1 2 2 c 0 BINARY 1 3 -1 {} 0 BINARY 0}
001935  
001936  # EVIDENCE-OF: R-64103-17776 PRAGMA schema.index_list(table-name); This
001937  # pragma returns one row for each index associated with the given table.
001938  #
001939  # (The first column of output from PRAGMA index_list is...)
001940  # EVIDENCE-OF: R-02753-24748 A sequence number assigned to each index
001941  # for internal tracking purposes.
001942  #
001943  # (The second column of output from PRAGMA index_list is...)
001944  # EVIDENCE-OF: R-35496-03635 The name of the index.
001945  #
001946  # (The third column of output from PRAGMA index_list is...)
001947  # EVIDENCE-OF: R-57301-64506 "1" if the index is UNIQUE and "0" if not.
001948  #
001949  # (The fourth column of output from PRAGMA index_list is...)
001950  # EVIDENCE-OF: R-36609-39554 "c" if the index was created by a CREATE
001951  # INDEX statement, "u" if the index was created by a UNIQUE constraint,
001952  # or "pk" if the index was created by a PRIMARY KEY constraint.
001953  #
001954  do_test 23.3 {
001955    db eval {
001956      DROP INDEX IF EXISTS i3;
001957      CREATE INDEX i3 ON t1(d,b,c);
001958    }
001959    capture_pragma db2 out {PRAGMA index_list(t1)}
001960    db2 eval {SELECT seq, name, "unique", origin, '|' FROM out ORDER BY seq}
001961  } {0 i3 0 c | 1 i2 0 c | 2 i2x 0 c | 3 i1 0 c |}
001962  ifcapable altertable {
001963    do_test 23.4 {
001964      db eval {
001965        ALTER TABLE t1 ADD COLUMN e;
001966      }
001967      db2 eval {
001968        PRAGMA table_info(t1);
001969      }
001970    } {/4 e {} 0 {} 0/}
001971  }
001972  do_test 23.5 {
001973    db eval {
001974      DROP TABLE t2;
001975      CREATE TABLE t2(x, y INTEGER REFERENCES t1);
001976    }
001977    db2 eval {
001978      PRAGMA foreign_key_list(t2);
001979    }
001980  } {0 0 t1 y {} {NO ACTION} {NO ACTION} NONE}
001981  db2 close
001982  
001983  ifcapable !has_codec {
001984    reset_db
001985    do_execsql_test 24.0 {
001986      PRAGMA page_size = 1024;
001987      CREATE TABLE t1(a, b, c);
001988      CREATE INDEX i1 ON t1(b);
001989      INSERT INTO t1 VALUES('a', 'b', 'c');
001990      PRAGMA integrity_check;
001991    } {ok}
001992    
001993    set r [db one {SELECT rootpage FROM sqlite_master WHERE name = 't1'}]
001994    db close
001995    hexio_write test.db [expr $r*1024 - 16] 000000000000000701040f0f1f616263
001996    
001997    sqlite3 db test.db
001998    do_catchsql_test 24.1 {
001999      SELECT * FROM t1;
002000    } {1 {database disk image is malformed}}
002001    do_catchsql_test 24.2 {
002002      PRAGMA integrity_check;
002003    } {0 {{database disk image is malformed}}}
002004  }  
002005  database_never_corrupt
002006  finish_test