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