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: never used
000437  Page 5: never used
000438  Page 6: 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: never used}}
000451      do_test pragma-3.11 {
000452        execsql {
000453          PRAGMA integrity_check=5
000454        }
000455      } {{*** in database t2 ***
000456  Page 4: never used
000457  Page 5: never used
000458  Page 6: 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: never used
000465  Page 5: never used
000466  Page 6: 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: never used
000473  Page 5: never used
000474  Page 6: never used}}
000475      do_test pragma-3.14 {
000476        execsql {
000477          PRAGMA integrity_check(2)
000478        }
000479      } {{*** in database t2 ***
000480  Page 4: never used
000481  Page 5: 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: never used
000489  Page 5: never used
000490  Page 6: 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: never used
000492  Page 5: never used
000493  Page 6: 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: never used
000500  Page 5: never used
000501  Page 6: 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: never used
000503  Page 5: never used
000504  Page 6: 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: never used
000511  Page 5: never used
000512  Page 6: 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: never used
000514  Page 5: never used}}
000515      do_test pragma-3.18 {
000516        execsql {
000517          PRAGMA integrity_check=4
000518        }
000519      } {{*** in database t2 ***
000520  Page 4: never used
000521  Page 5: never used
000522  Page 6: 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  # The values stored in indexes must be byte-for-byte identical to the
000586  # values stored in tables.
000587  #
000588  reset_db
000589  do_execsql_test pragma-3.40 {
000590    CREATE TABLE t1(
000591      a INTEGER PRIMARY KEY,
000592      b TEXT COLLATE nocase,
000593      c INT COLLATE nocase,
000594      d TEXT
000595    );
000596    INSERT INTO t1(a,b,c,d) VALUES
000597      (1, 'one','one','one'),
000598      (2, 'two','two','two'),
000599      (3, 'three','three','three'),
000600      (4, 'four','four','four'),
000601      (5, 'five','five','five');
000602    CREATE INDEX t1bcd ON t1(b,c,d);
000603    CREATE TABLE t2(
000604      a INTEGER PRIMARY KEY,
000605      b TEXT COLLATE nocase,
000606      c INT COLLATE nocase,
000607      d TEXT
000608    );
000609    INSERT INTO t2(a,b,c,d) VALUES
000610      (1, 'one','one','one'),
000611      (2, 'two','two','TWO'),
000612      (3, 'three','THREE','three'),
000613      (4, 'FOUR','four','four'),
000614      (5, 'FIVE','FIVE','five');
000615    CREATE INDEX t2bcd ON t2(b,c,d);
000616    CREATE TEMP TABLE saved_schema AS SELECT name, rootpage FROM sqlite_schema;
000617    PRAGMA writable_schema=ON;
000618    UPDATE sqlite_schema
000619       SET rootpage=(SELECT rootpage FROM saved_schema WHERE name='t2bcd')
000620     WHERE name='t1bcd';
000621    UPDATE sqlite_schema
000622       SET rootpage=(SELECT rootpage FROM saved_schema WHERE name='t1bcd')
000623     WHERE name='t2bcd';
000624    PRAGMA Writable_schema=RESET;
000625  }
000626  ifcapable vtab {
000627    do_execsql_test pragma-3.41 {
000628      SELECT integrity_check AS x FROM pragma_integrity_check ORDER BY 1;
000629    } {
000630      {row 2 missing from index t1bcd}
000631      {row 2 missing from index t2bcd}
000632      {row 3 values differ from index t1bcd}
000633      {row 3 values differ from index t2bcd}
000634      {row 4 values differ from index t1bcd}
000635      {row 4 values differ from index t2bcd}
000636      {row 5 values differ from index t1bcd}
000637      {row 5 values differ from index t2bcd}
000638    }
000639  }
000640  db eval {DROP TABLE t2}
000641  
000642  # Test modifying the cache_size of an attached database.
000643  ifcapable pager_pragmas&&attach {
000644  do_test pragma-4.1 {
000645    execsql {
000646      ATTACH 'test2.db' AS aux;
000647      pragma aux.cache_size;
000648      pragma aux.default_cache_size;
000649    } 
000650  } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
000651  do_test pragma-4.2 {
000652    execsql {
000653      pragma aux.cache_size = 50;
000654      pragma aux.cache_size;
000655      pragma aux.default_cache_size;
000656    } 
000657  } [list 50 $DFLT_CACHE_SZ]
000658  do_test pragma-4.3 {
000659    execsql {
000660      pragma aux.default_cache_size = 456;
000661      pragma aux.cache_size;
000662      pragma aux.default_cache_size;
000663    } 
000664  } {456 456}
000665  do_test pragma-4.4 {
000666    execsql {
000667      pragma cache_size;
000668      pragma default_cache_size;
000669    } 
000670  } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
000671  do_test pragma-4.5 {
000672    execsql {
000673      DETACH aux;
000674      ATTACH 'test3.db' AS aux;
000675      pragma aux.cache_size;
000676      pragma aux.default_cache_size;
000677    } 
000678  } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
000679  do_test pragma-4.6 {
000680    execsql {
000681      DETACH aux;
000682      ATTACH 'test2.db' AS aux;
000683      pragma aux.cache_size;
000684      pragma aux.default_cache_size;
000685    } 
000686  } {456 456}
000687  } ;# ifcapable pager_pragmas
000688  
000689  # Test that modifying the sync-level in the middle of a transaction is
000690  # disallowed.
000691  ifcapable pager_pragmas {
000692  do_test pragma-5.0 {
000693    execsql {
000694      pragma synchronous;
000695    } 
000696  } {2}
000697  do_test pragma-5.1 {
000698    catchsql {
000699      BEGIN;
000700      pragma synchronous = OFF;
000701    } 
000702  } {1 {Safety level may not be changed inside a transaction}}
000703  do_test pragma-5.2 {
000704    execsql {
000705      pragma synchronous;
000706    } 
000707  } {2}
000708  catchsql {COMMIT;}
000709  } ;# ifcapable pager_pragmas
000710  
000711  # Test schema-query pragmas
000712  #
000713  ifcapable schema_pragmas {
000714  ifcapable tempdb&&attach {
000715    do_test pragma-6.1 {
000716      set res {}
000717      execsql {SELECT * FROM sqlite_temp_master}
000718      foreach {idx name file} [execsql {pragma database_list}] {
000719        lappend res $idx $name
000720      }
000721      set res
000722    } {0 main 1 temp 2 aux}
000723  }
000724  do_test pragma-6.2 {
000725    execsql {
000726      CREATE TABLE t2(a TYPE_X, b [TYPE_Y], c "TYPE_Z");
000727      pragma table_info(t2)
000728    }
000729  } {0 a TYPE_X 0 {} 0 1 b TYPE_Y 0 {} 0 2 c TYPE_Z 0 {} 0}
000730  do_test pragma-6.2.1 {
000731    execsql {
000732      pragma table_info;
000733    }
000734  } {}
000735  db nullvalue <<NULL>>
000736  do_test pragma-6.2.2 {
000737    execsql {
000738      CREATE TABLE t5(
000739        a TEXT DEFAULT CURRENT_TIMESTAMP, 
000740        b DEFAULT (5+3),
000741        c TEXT,
000742        d INTEGER DEFAULT NULL,
000743        e TEXT DEFAULT '',
000744        UNIQUE(b,c,d),
000745        PRIMARY KEY(e,b,c)
000746      );
000747      PRAGMA table_info(t5);
000748    }
000749  } {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}
000750  db nullvalue {}
000751  do_test pragma-6.2.3 {
000752    execsql {
000753      CREATE TABLE t2_3(a,b INTEGER PRIMARY KEY,c);
000754      pragma table_info(t2_3)
000755    }
000756  } {0 a {} 0 {} 0 1 b INTEGER 0 {} 1 2 c {} 0 {} 0}
000757  ifcapable {foreignkey} {
000758    do_test pragma-6.3.1 {
000759      execsql {
000760        CREATE TABLE t3(a int references t2(b), b UNIQUE);
000761        pragma foreign_key_list(t3);
000762      }
000763    } {0 0 t2 a b {NO ACTION} {NO ACTION} NONE}
000764    do_test pragma-6.3.2 {
000765      execsql {
000766        pragma foreign_key_list;
000767      }
000768    } {}
000769    do_test pragma-6.3.3 {
000770      execsql {
000771        pragma foreign_key_list(t3_bogus);
000772      }
000773    } {}
000774    do_test pragma-6.3.4 {
000775      execsql {
000776        pragma foreign_key_list(t5);
000777      }
000778    } {}
000779    do_test pragma-6.4 {
000780      capture_pragma db out {
000781        pragma index_list(t3);
000782      }
000783      db eval {SELECT seq, "name", "unique" FROM out ORDER BY seq}
000784    } {0 sqlite_autoindex_t3_1 1}
000785  }
000786  ifcapable {!foreignkey} {
000787    execsql {CREATE TABLE t3(a,b UNIQUE)}
000788  }
000789  do_test pragma-6.5.1 {
000790    execsql {
000791      CREATE INDEX t3i1 ON t3(a,b);
000792    }
000793    capture_pragma db out {
000794      pragma index_info(t3i1);
000795    }
000796    db eval {SELECT seqno, cid, name FROM out ORDER BY seqno}
000797  } {0 0 a 1 1 b}
000798  
000799  # EVIDENCE-OF: R-23114-21695 The auxiliary index-columns are not shown
000800  # by the index_info pragma, but they are listed by the index_xinfo
000801  # pragma.
000802  #
000803  do_test pragma-6.5.1b {
000804    capture_pragma db out {PRAGMA index_xinfo(t3i1)}
000805    db eval {SELECT seqno, cid, name FROM out ORDER BY seqno}
000806  } {0 0 a 1 1 b 2 -1 {}}
000807  
000808  
000809  # EVIDENCE-OF: R-29448-60346 PRAGMA schema.index_info(index-name); This
000810  # pragma returns one row for each key column in the named index.
000811  #
000812  # (The first column of output from PRAGMA index_info is...)
000813  # EVIDENCE-OF: R-34186-52914 The rank of the column within the index. (0
000814  # means left-most.)
000815  #
000816  # (The second column of output from PRAGMA index_info is...)
000817  # EVIDENCE-OF: R-65019-08383 The rank of the column within the table
000818  # being indexed.
000819  #
000820  # (The third column of output from PRAGMA index_info is...)
000821  # EVIDENCE-OF: R-09773-34266 The name of the column being indexed.
000822  #
000823  do_execsql_test pragma-6.5.1c {
000824    CREATE INDEX t3i2 ON t3(b,a);
000825    PRAGMA index_info='t3i2';
000826    DROP INDEX t3i2;
000827  } {0 1 b 1 0 a}
000828  
000829  do_test pragma-6.5.2 {
000830    execsql {
000831      pragma index_info(t3i1_bogus);
000832    }
000833  } {}
000834  
000835  ifcapable tempdb {
000836    # Test for ticket #3320. When a temp table of the same name exists, make
000837    # sure the schema of the main table can still be queried using 
000838    # "pragma table_info":
000839    do_test pragma-6.6.1 {
000840      execsql {
000841        CREATE TABLE trial(col_main);
000842        CREATE TEMP TABLE trial(col_temp);
000843      }
000844    } {}
000845    do_test pragma-6.6.2 {
000846      execsql {
000847        PRAGMA table_info(trial);
000848      }
000849    } {0 col_temp {} 0 {} 0}
000850    do_test pragma-6.6.3 {
000851      execsql {
000852        PRAGMA temp.table_info(trial);
000853      }
000854    } {0 col_temp {} 0 {} 0}
000855    do_test pragma-6.6.4 {
000856      execsql {
000857        PRAGMA main.table_info(trial);
000858      }
000859    } {0 col_main {} 0 {} 0}
000860  }
000861  
000862  do_test pragma-6.7 {
000863    execsql {
000864      CREATE TABLE test_table(
000865        one INT NOT NULL DEFAULT -1, 
000866        two text,
000867        three VARCHAR(45, 65) DEFAULT 'abcde',
000868        four REAL DEFAULT X'abcdef',
000869        five DEFAULT CURRENT_TIME
000870      );
000871    }
000872    capture_pragma db out {PRAGMA table_info(test_table)}
000873    db eval {SELECT cid, "name", type, "notnull", dflt_value, pk FROM out
000874              ORDER BY cid}
000875  } [concat \
000876    {0 one INT 1 -1 0} \
000877    {1 two TEXT 0 {} 0} \
000878    {2 three {VARCHAR(45, 65)} 0 'abcde' 0} \
000879    {3 four REAL 0 X'abcdef' 0} \
000880    {4 five {} 0 CURRENT_TIME 0} \
000881  ]
000882  do_test pragma-6.8 {
000883    execsql {
000884      CREATE TABLE t68(a,b,c,PRIMARY KEY(a,b,a,c));
000885      PRAGMA table_info(t68);
000886    }
000887  } [concat \
000888    {0 a {} 0 {} 1} \
000889    {1 b {} 0 {} 2} \
000890    {2 c {} 0 {} 4} \
000891  ]
000892  } ;# ifcapable schema_pragmas
000893  # Miscellaneous tests
000894  #
000895  ifcapable schema_pragmas {
000896  # EVIDENCE-OF: R-64103-17776 PRAGMA schema.index_list(table-name); This
000897  # pragma returns one row for each index associated with the given table.
000898  #
000899  do_test pragma-7.1.1 {
000900    # Make sure a pragma knows to read the schema if it needs to
000901    db close
000902    sqlite3 db test.db
000903    capture_pragma db out "PRAGMA index_list(t3)"
000904    db eval {SELECT name, "origin" FROM out ORDER BY name DESC}
000905  } {t3i1 c sqlite_autoindex_t3_1 u}
000906  do_test pragma-7.1.2 {
000907    execsql {
000908      pragma index_list(t3_bogus);
000909    }
000910  } {}
000911  } ;# ifcapable schema_pragmas
000912  ifcapable {utf16} {
000913    if {[permutation] == ""} {
000914      do_test pragma-7.2 {
000915        db close
000916        sqlite3 db test.db
000917        catchsql {
000918          pragma encoding=bogus;
000919        }
000920      } {1 {unsupported encoding: bogus}}
000921    }
000922  }
000923  ifcapable tempdb {
000924    do_test pragma-7.3 {
000925      db close
000926      sqlite3 db test.db
000927      execsql {
000928        pragma lock_status;
000929      }
000930    } {main unlocked temp closed}
000931  } else {
000932    do_test pragma-7.3 {
000933      db close
000934      sqlite3 db test.db
000935      execsql {
000936        pragma lock_status;
000937      }
000938    } {main unlocked}
000939  }
000940  
000941  
000942  #----------------------------------------------------------------------
000943  # Test cases pragma-8.* test the "PRAGMA schema_version" and "PRAGMA
000944  # user_version" statements.
000945  #
000946  # pragma-8.1: PRAGMA schema_version
000947  # pragma-8.2: PRAGMA user_version
000948  #
000949  
000950  ifcapable schema_version {
000951  
000952  # First check that we can set the schema version and then retrieve the
000953  # same value.
000954  do_test pragma-8.1.1 {
000955    execsql {
000956      PRAGMA schema_version = 105;
000957    }
000958  } {}
000959  do_test pragma-8.1.2 {
000960    execsql2 {
000961      PRAGMA schema_version;
000962    }
000963  } {schema_version 105}
000964  sqlite3_db_config db DEFENSIVE 1
000965  do_execsql_test pragma-8.1.3 {
000966    PRAGMA schema_version = 106;
000967    PRAGMA schema_version;
000968  } 105
000969  sqlite3_db_config db DEFENSIVE 0
000970  do_execsql_test pragma-8.1.4 {
000971    PRAGMA schema_version = 106;
000972    PRAGMA schema_version;
000973  } 106
000974  
000975  # Check that creating a table modifies the schema-version (this is really
000976  # to verify that the value being read is in fact the schema version).
000977  do_test pragma-8.1.5 {
000978    execsql {
000979      CREATE TABLE t4(a, b, c);
000980      INSERT INTO t4 VALUES(1, 2, 3);
000981      SELECT * FROM t4;
000982    }
000983  } {1 2 3}
000984  do_test pragma-8.1.6 {
000985    execsql {
000986      PRAGMA schema_version;
000987    }
000988  } 107
000989  
000990  # Now open a second connection to the database. Ensure that changing the
000991  # schema-version using the first connection forces the second connection
000992  # to reload the schema. This has to be done using the C-API test functions,
000993  # because the TCL API accounts for SCHEMA_ERROR and retries the query.
000994  do_test pragma-8.1.7 {
000995    sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]
000996    execsql {
000997      SELECT * FROM t4;
000998    } db2
000999  } {1 2 3}
001000  do_test pragma-8.1.8 {
001001    execsql {
001002      PRAGMA schema_version = 108;
001003    }
001004  } {}
001005  do_test pragma-8.1.9 {
001006    set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM t4" -1 DUMMY]
001007    sqlite3_step $::STMT
001008  } SQLITE_ERROR
001009  do_test pragma-8.1.10 {
001010    sqlite3_finalize $::STMT
001011  } SQLITE_SCHEMA
001012  
001013  # Make sure the schema-version can be manipulated in an attached database.
001014  forcedelete test2.db
001015  forcedelete test2.db-journal
001016  ifcapable attach {
001017    do_test pragma-8.1.11 {
001018      execsql {
001019        ATTACH 'test2.db' AS aux;
001020        CREATE TABLE aux.t1(a, b, c);
001021        PRAGMA aux.schema_version = 205;
001022      }
001023    } {}
001024    do_test pragma-8.1.12 {
001025      execsql {
001026        PRAGMA aux.schema_version;
001027      }
001028    } 205
001029  }
001030  do_test pragma-8.1.13 {
001031    execsql {
001032      PRAGMA schema_version;
001033    }
001034  } 108
001035  
001036  # And check that modifying the schema-version in an attached database
001037  # forces the second connection to reload the schema.
001038  ifcapable attach {
001039    do_test pragma-8.1.14 {
001040      sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]
001041      execsql {
001042        ATTACH 'test2.db' AS aux;
001043        SELECT * FROM aux.t1;
001044      } db2
001045    } {}
001046    do_test pragma-8.1.15 {
001047      execsql {
001048        PRAGMA aux.schema_version = 206;
001049      }
001050    } {}
001051    do_test pragma-8.1.16 {
001052      set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM aux.t1" -1 DUMMY]
001053      sqlite3_step $::STMT
001054    } SQLITE_ERROR
001055    do_test pragma-8.1.17 {
001056      sqlite3_finalize $::STMT
001057    } SQLITE_SCHEMA
001058    do_test pragma-8.1.18 {
001059      db2 close
001060    } {}
001061  }
001062  
001063  # Now test that the user-version can be read and written (and that we aren't
001064  # accidentally manipulating the schema-version instead).
001065  do_test pragma-8.2.1 {
001066    execsql2 {
001067      PRAGMA user_version;
001068    }
001069  } {user_version 0}
001070  do_test pragma-8.2.2 {
001071    execsql {
001072      PRAGMA user_version = 2;
001073    }
001074  } {}
001075  do_test pragma-8.2.3.1 {
001076    execsql2 {
001077      PRAGMA user_version;
001078    }
001079  } {user_version 2}
001080  do_test pragma-8.2.3.2 {
001081    db close
001082    sqlite3 db test.db
001083    execsql {
001084      PRAGMA user_version;
001085    }
001086  } {2}
001087  do_test pragma-8.2.4.1 {
001088    execsql {
001089      PRAGMA schema_version;
001090    }
001091  } {108}
001092  ifcapable vacuum {
001093    do_test pragma-8.2.4.2 {
001094      execsql {
001095        VACUUM;
001096        PRAGMA user_version;
001097      }
001098    } {2}
001099    do_test pragma-8.2.4.3 {
001100      execsql {
001101        PRAGMA schema_version;
001102      }
001103    } {109}
001104  }
001105  
001106  ifcapable attach {
001107    db eval {ATTACH 'test2.db' AS aux}
001108    
001109    # Check that the user-version in the auxilary database can be manipulated (
001110    # and that we aren't accidentally manipulating the same in the main db).
001111    do_test pragma-8.2.5 {
001112      execsql {
001113        PRAGMA aux.user_version;
001114      }
001115    } {0}
001116    do_test pragma-8.2.6 {
001117      execsql {
001118        PRAGMA aux.user_version = 3;
001119      }
001120    } {}
001121    do_test pragma-8.2.7 {
001122      execsql {
001123        PRAGMA aux.user_version;
001124      }
001125    } {3}
001126    do_test pragma-8.2.8 {
001127      execsql {
001128        PRAGMA main.user_version;
001129      }
001130    } {2}
001131    
001132    # Now check that a ROLLBACK resets the user-version if it has been modified
001133    # within a transaction.
001134    do_test pragma-8.2.9 {
001135      execsql {
001136        BEGIN;
001137        PRAGMA aux.user_version = 10;
001138        PRAGMA user_version = 11;
001139      }
001140    } {}
001141    do_test pragma-8.2.10 {
001142      execsql {
001143        PRAGMA aux.user_version;
001144      }
001145    } {10}
001146    do_test pragma-8.2.11 {
001147      execsql {
001148        PRAGMA main.user_version;
001149      }
001150    } {11}
001151    do_test pragma-8.2.12 {
001152      execsql {
001153        ROLLBACK;
001154        PRAGMA aux.user_version;
001155      }
001156    } {3}
001157    do_test pragma-8.2.13 {
001158      execsql {
001159        PRAGMA main.user_version;
001160      }
001161    } {2}
001162  }
001163  
001164  # Try a negative value for the user-version
001165  do_test pragma-8.2.14 {
001166    execsql {
001167      PRAGMA user_version = -450;
001168    }
001169  } {}
001170  do_test pragma-8.2.15 {
001171    execsql {
001172      PRAGMA user_version;
001173    }
001174  } {-450}
001175  } ; # ifcapable schema_version
001176  
001177  # Check to see if TEMP_STORE is memory or disk.  Return strings
001178  # "memory" or "disk" as appropriate.
001179  #
001180  proc check_temp_store {} {
001181    db eval {
001182      PRAGMA temp.cache_size = 1;
001183      CREATE TEMP TABLE IF NOT EXISTS a(b);
001184      DELETE FROM a;
001185      INSERT INTO a VALUES(randomblob(1000));
001186      INSERT INTO a SELECT * FROM a;
001187      INSERT INTO a SELECT * FROM a;
001188      INSERT INTO a SELECT * FROM a;
001189      INSERT INTO a SELECT * FROM a;
001190      INSERT INTO a SELECT * FROM a;
001191      INSERT INTO a SELECT * FROM a;
001192      INSERT INTO a SELECT * FROM a;
001193      INSERT INTO a SELECT * FROM a;
001194    }
001195    db eval {PRAGMA database_list} {
001196      if {$name=="temp"} {
001197        set bt [btree_from_db db 1]
001198        if {[btree_ismemdb $bt]} {
001199          return "memory"
001200        }
001201        return "disk"
001202      }
001203    }
001204    return "unknown"
001205  }
001206  
001207  # Application_ID
001208  #
001209  do_test pragma-8.3.1 {
001210    execsql {
001211      PRAGMA application_id;
001212    }
001213  } {0}
001214  do_test pragma-8.3.2 {
001215    execsql {PRAGMA Application_ID(12345); PRAGMA application_id;}
001216  } {12345}
001217  
001218  # Test temp_store and temp_store_directory pragmas
001219  #
001220  ifcapable pager_pragmas {
001221  do_test pragma-9.1 {
001222    db close
001223    sqlite3 db test.db
001224    execsql {
001225      PRAGMA temp_store;
001226    }
001227  } {0}
001228  if {$TEMP_STORE<=1} {
001229    do_test pragma-9.1.1 {
001230      check_temp_store
001231    } {disk}
001232  } else {
001233    do_test pragma-9.1.1 {
001234      check_temp_store
001235    } {memory}
001236  }
001237  
001238  do_test pragma-9.2 {
001239    db close
001240    sqlite3 db test.db
001241    execsql {
001242      PRAGMA temp_store=file;
001243      PRAGMA temp_store;
001244    }
001245  } {1}
001246  if {$TEMP_STORE==3} {
001247    # When TEMP_STORE is 3, always use memory regardless of pragma settings.
001248    do_test pragma-9.2.1 {
001249      check_temp_store
001250    } {memory}
001251  } else {
001252    do_test pragma-9.2.1 {
001253      check_temp_store
001254    } {disk}
001255  }
001256  
001257  do_test pragma-9.3 {
001258    db close
001259    sqlite3 db test.db
001260    execsql {
001261      PRAGMA temp_store=memory;
001262      PRAGMA temp_store;
001263    }
001264  } {2}
001265  if {$TEMP_STORE==0} {
001266    # When TEMP_STORE is 0, always use the disk regardless of pragma settings.
001267    do_test pragma-9.3.1 {
001268      check_temp_store
001269    } {disk}
001270  } else {
001271    do_test pragma-9.3.1 {
001272      check_temp_store
001273    } {memory}
001274  }
001275  
001276  do_test pragma-9.4 {
001277    execsql {
001278      PRAGMA temp_store_directory;
001279    }
001280  } {}
001281  ifcapable wsd {
001282    do_test pragma-9.5 {
001283      set pwd [string map {' ''} [file nativename [get_pwd]]]
001284      execsql "
001285        PRAGMA temp_store_directory='$pwd';
001286      "
001287    } {}
001288    do_test pragma-9.6 {
001289      execsql { 
001290        PRAGMA temp_store_directory;
001291      }
001292    } [list [file nativename [get_pwd]]]
001293    do_test pragma-9.7 {
001294      catchsql { 
001295        PRAGMA temp_store_directory='/NON/EXISTENT/PATH/FOOBAR';
001296      }
001297    } {1 {not a writable directory}}
001298    do_test pragma-9.8 {
001299      execsql { 
001300        PRAGMA temp_store_directory='';
001301      }
001302    } {}
001303    if {![info exists TEMP_STORE] || $TEMP_STORE<=1} {
001304      ifcapable tempdb {
001305        do_test pragma-9.9 {
001306          execsql { 
001307            PRAGMA temp_store_directory;
001308            PRAGMA temp_store=FILE;
001309            CREATE TEMP TABLE temp_store_directory_test(a integer);
001310            INSERT INTO temp_store_directory_test values (2);
001311            SELECT * FROM temp_store_directory_test;
001312          }
001313        } {2}
001314        do_test pragma-9.10 {
001315          catchsql "
001316            PRAGMA temp_store_directory='$pwd';
001317            SELECT * FROM temp_store_directory_test;
001318          "
001319        } {1 {no such table: temp_store_directory_test}}
001320      }
001321    }
001322  }
001323  do_test pragma-9.11 {
001324    execsql {
001325      PRAGMA temp_store = 0;
001326      PRAGMA temp_store;
001327    }
001328  } {0}
001329  do_test pragma-9.12 {
001330    execsql {
001331      PRAGMA temp_store = 1;
001332      PRAGMA temp_store;
001333    }
001334  } {1}
001335  do_test pragma-9.13 {
001336    execsql {
001337      PRAGMA temp_store = 2;
001338      PRAGMA temp_store;
001339    }
001340  } {2}
001341  do_test pragma-9.14 {
001342    execsql {
001343      PRAGMA temp_store = 3;
001344      PRAGMA temp_store;
001345    }
001346  } {0}
001347  do_test pragma-9.15 {
001348    catchsql {
001349      BEGIN EXCLUSIVE;
001350      CREATE TEMP TABLE temp_table(t);
001351      INSERT INTO temp_table VALUES('valuable data');
001352      PRAGMA temp_store = 1;
001353    }
001354  } {1 {temporary storage cannot be changed from within a transaction}}
001355  do_test pragma-9.16 {
001356    execsql {
001357      SELECT * FROM temp_table;
001358      COMMIT;
001359    }
001360  } {{valuable data}}
001361  
001362  do_test pragma-9.17 {
001363    execsql {
001364      INSERT INTO temp_table VALUES('valuable data II');
001365      SELECT * FROM temp_table;
001366    }
001367  } {{valuable data} {valuable data II}}
001368  
001369  do_test pragma-9.18 {
001370    set rc [catch {
001371      db eval {SELECT t FROM temp_table} {
001372        execsql {pragma temp_store = 1}
001373      }
001374    } msg]
001375    list $rc $msg
001376  } {1 {temporary storage cannot be changed from within a transaction}}
001377  
001378  } ;# ifcapable pager_pragmas
001379  
001380  ifcapable trigger {
001381  
001382  do_test pragma-10.0 {
001383    catchsql {
001384      DROP TABLE main.t1;
001385    }
001386    execsql {
001387      PRAGMA count_changes = 1;
001388  
001389      CREATE TABLE t1(a PRIMARY KEY);
001390      CREATE TABLE t1_mirror(a);
001391      CREATE TABLE t1_mirror2(a);
001392      CREATE TRIGGER t1_bi BEFORE INSERT ON t1 BEGIN 
001393        INSERT INTO t1_mirror VALUES(new.a);
001394      END;
001395      CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN 
001396        INSERT INTO t1_mirror2 VALUES(new.a);
001397      END;
001398      CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN 
001399        UPDATE t1_mirror SET a = new.a WHERE a = old.a;
001400      END;
001401      CREATE TRIGGER t1_au AFTER UPDATE ON t1 BEGIN 
001402        UPDATE t1_mirror2 SET a = new.a WHERE a = old.a;
001403      END;
001404      CREATE TRIGGER t1_bd BEFORE DELETE ON t1 BEGIN 
001405        DELETE FROM t1_mirror WHERE a = old.a;
001406      END;
001407      CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN 
001408        DELETE FROM t1_mirror2 WHERE a = old.a;
001409      END;
001410    }
001411  } {}
001412  
001413  do_test pragma-10.1 {
001414    execsql {
001415      INSERT INTO t1 VALUES(randstr(10,10));
001416    }
001417  } {1}
001418  do_test pragma-10.2 {
001419    execsql {
001420      UPDATE t1 SET a = randstr(10,10);
001421    }
001422  } {1}
001423  do_test pragma-10.3 {
001424    execsql {
001425      DELETE FROM t1;
001426    }
001427  } {1}
001428  
001429  } ;# ifcapable trigger
001430  
001431  ifcapable schema_pragmas {
001432    do_test pragma-11.1 {
001433      execsql2 {
001434        pragma collation_list;
001435      }
001436    } {seq 0 name RTRIM seq 1 name NOCASE seq 2 name BINARY}
001437    do_test pragma-11.2 {
001438      db collate New_Collation blah...
001439      execsql {
001440        pragma collation_list;
001441      }
001442    } {0 New_Collation 1 RTRIM 2 NOCASE 3 BINARY}
001443  }
001444  
001445  ifcapable schema_pragmas&&tempdb {
001446    do_test pragma-12.1 {
001447      sqlite3 db2 test.db
001448      execsql {
001449        PRAGMA temp.table_info('abc');
001450      } db2
001451    } {}
001452    db2 close
001453  
001454    do_test pragma-12.2 {
001455      sqlite3 db2 test.db
001456      execsql {
001457        PRAGMA temp.default_cache_size = 200;
001458        PRAGMA temp.default_cache_size;
001459      } db2
001460    } {200}
001461    db2 close
001462  
001463    do_test pragma-12.3 {
001464      sqlite3 db2 test.db
001465      execsql {
001466        PRAGMA temp.cache_size = 400;
001467        PRAGMA temp.cache_size;
001468      } db2
001469    } {400}
001470    db2 close
001471  }
001472  
001473  ifcapable bloblit {
001474  
001475  do_test pragma-13.1 {
001476    execsql {
001477      DROP TABLE IF EXISTS t4;
001478      PRAGMA vdbe_trace=on;
001479      PRAGMA vdbe_listing=on;
001480      PRAGMA sql_trace=on;
001481      CREATE TABLE t4(a INTEGER PRIMARY KEY,b);
001482      INSERT INTO t4(b) VALUES(x'0123456789abcdef0123456789abcdef0123456789');
001483      INSERT INTO t4(b) VALUES(randstr(30,30));
001484      INSERT INTO t4(b) VALUES(1.23456);
001485      INSERT INTO t4(b) VALUES(NULL);
001486      INSERT INTO t4(b) VALUES(0);
001487      INSERT INTO t4(b) SELECT b||b||b||b FROM t4;
001488      SELECT * FROM t4;
001489    }
001490    execsql {
001491      PRAGMA vdbe_trace=off;
001492      PRAGMA vdbe_listing=off;
001493      PRAGMA sql_trace=off;
001494    }
001495  } {}
001496  
001497  } ;# ifcapable bloblit 
001498  
001499  ifcapable pager_pragmas {
001500    db close
001501    forcedelete test.db
001502    sqlite3 db test.db
001503   
001504    # EVIDENCE-OF: R-15672-33611 PRAGMA schema.page_count; Return the total
001505    # number of pages in the database file.
001506    #
001507    do_test pragma-14.1 {
001508      execsql { pragma auto_vacuum = 0 }
001509      execsql { pragma page_count; pragma main.page_count }
001510    } {0 0}
001511  
001512    do_test pragma-14.2 {
001513      execsql { 
001514        CREATE TABLE abc(a, b, c);
001515        PRAGMA page_count;
001516        PRAGMA main.page_count;
001517        PRAGMA temp.page_count;
001518      }
001519    } {2 2 0}
001520    do_test pragma-14.2uc {
001521      execsql {pragma PAGE_COUNT}
001522    } {2}
001523  
001524    do_test pragma-14.3 {
001525      execsql { 
001526        BEGIN;
001527        CREATE TABLE def(a, b, c);
001528        PRAGMA page_count;
001529      }
001530    } {3}
001531    do_test pragma-14.3uc {
001532      execsql {pragma PAGE_COUNT}
001533    } {3}
001534  
001535    do_test pragma-14.4 {
001536      set page_size [db one {pragma page_size}]
001537      expr [file size test.db] / $page_size
001538    } {2}
001539  
001540    do_test pragma-14.5 {
001541      execsql {
001542        ROLLBACK;
001543        PRAGMA page_count;
001544      }
001545    } {2}
001546  
001547    do_test pragma-14.6 {
001548      forcedelete test2.db
001549      sqlite3 db2 test2.db
001550      execsql {
001551        PRAGMA auto_vacuum = 0;
001552        CREATE TABLE t1(a, b, c);
001553        CREATE TABLE t2(a, b, c);
001554        CREATE TABLE t3(a, b, c);
001555        CREATE TABLE t4(a, b, c);
001556      } db2
001557      db2 close
001558      execsql {
001559        ATTACH 'test2.db' AS aux;
001560        PRAGMA aux.page_count;
001561      } 
001562    } {5}
001563    do_test pragma-14.6uc {
001564      execsql {pragma AUX.PAGE_COUNT}
001565    } {5}
001566  }
001567  
001568  # Test that the value set using the cache_size pragma is not reset when the
001569  # schema is reloaded.
001570  #
001571  ifcapable pager_pragmas {
001572    db close
001573    sqlite3 db test.db
001574    do_test pragma-15.1 {
001575      execsql {
001576        PRAGMA cache_size=59;
001577        PRAGMA cache_size;
001578      }
001579    } {59}
001580    do_test pragma-15.2 {
001581      sqlite3 db2 test.db
001582      execsql {
001583        CREATE TABLE newtable(a, b, c);
001584      } db2
001585      db2 close
001586    } {}
001587    do_test pragma-15.3 {
001588      # Evaluating this statement will cause the schema to be reloaded (because
001589      # the schema was changed by another connection in pragma-15.2). At one
001590      # point there was a bug that reset the cache_size to its default value
001591      # when this happened. 
001592      execsql { SELECT * FROM sqlite_master }
001593      execsql { PRAGMA cache_size }
001594    } {59}
001595  }
001596  
001597  # Reset the sqlite3_temp_directory variable for the next run of tests:
001598  sqlite3 dbX :memory:
001599  dbX eval {PRAGMA temp_store_directory = ""}
001600  dbX close
001601  
001602  ifcapable lock_proxy_pragmas&&prefer_proxy_locking {
001603    set sqlite_hostid_num 1
001604  
001605    set using_proxy 0
001606    foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] {
001607      set using_proxy $value
001608    }
001609  
001610    # Test the lock_proxy_file pragmas.
001611    #
001612    db close
001613    set env(SQLITE_FORCE_PROXY_LOCKING) "0"
001614  
001615    sqlite3 db test.db
001616    do_test pragma-16.1 {
001617      execsql {
001618        PRAGMA lock_proxy_file="mylittleproxy";
001619        select * from sqlite_master;
001620      }
001621      execsql {
001622        PRAGMA lock_proxy_file;
001623      } 
001624    } {mylittleproxy}
001625  
001626    do_test pragma-16.2 {
001627      sqlite3 db2 test.db
001628      execsql {
001629        PRAGMA lock_proxy_file="mylittleproxy";
001630      } db2
001631    } {}
001632  
001633    db2 close
001634    do_test pragma-16.2.1 {
001635      sqlite3 db2 test.db
001636      execsql {
001637        PRAGMA lock_proxy_file=":auto:";
001638        select * from sqlite_master;
001639      } db2
001640      execsql {
001641        PRAGMA lock_proxy_file;
001642      } db2
001643    } {mylittleproxy}
001644  
001645    db2 close
001646    do_test pragma-16.3 {
001647      sqlite3 db2 test.db
001648      execsql {
001649        PRAGMA lock_proxy_file="myotherproxy";
001650      } db2
001651      catchsql {
001652        select * from sqlite_master;
001653      } db2
001654    } {1 {database is locked}}
001655  
001656    do_test pragma-16.4 {
001657      db2 close
001658      db close
001659      sqlite3 db2 test.db
001660      execsql {
001661        PRAGMA lock_proxy_file="myoriginalproxy";
001662        PRAGMA lock_proxy_file="myotherproxy";
001663        PRAGMA lock_proxy_file;
001664      } db2
001665    } {myotherproxy}
001666  
001667    db2 close
001668    set env(SQLITE_FORCE_PROXY_LOCKING) "1"
001669    do_test pragma-16.5 {
001670      sqlite3 db2 test.db
001671      execsql {
001672        PRAGMA lock_proxy_file=":auto:";
001673        PRAGMA lock_proxy_file;
001674      } db2
001675    } {myotherproxy}
001676    
001677    do_test pragma-16.6 {
001678      db2 close
001679      sqlite3 db2 test2.db
001680      set lockpath [execsql {
001681        PRAGMA lock_proxy_file=":auto:";
001682        PRAGMA lock_proxy_file;
001683      } db2]
001684      string match "*test2.db:auto:" $lockpath
001685    } {1}
001686    
001687    set sqlite_hostid_num 2
001688    do_test pragma-16.7 {
001689      list [catch {
001690        sqlite3 db test2.db
001691        execsql { 
001692          PRAGMA lock_proxy_file=":auto:";
001693          select * from sqlite_master;
001694        }
001695      } msg] $msg
001696    } {1 {database is locked}}
001697    db close
001698    
001699    do_test pragma-16.8 {
001700      list [catch {
001701        sqlite3 db test2.db
001702        execsql { select * from sqlite_master } 
001703      } msg] $msg
001704    } {1 {database is locked}}
001705  
001706    db2 close
001707    do_test pragma-16.8.1 {
001708      execsql {
001709        PRAGMA lock_proxy_file="yetanotherproxy";
001710        PRAGMA lock_proxy_file;
001711      } 
001712    } {yetanotherproxy}
001713    do_test pragma-16.8.2 {
001714      execsql {
001715        create table mine(x);
001716      } 
001717    } {}
001718  
001719    db close
001720    do_test pragma-16.9 {
001721      sqlite3 db proxytest.db
001722      set lockpath2 [execsql {
001723        PRAGMA lock_proxy_file=":auto:";
001724        PRAGMA lock_proxy_file;
001725      } db]
001726      string match "*proxytest.db:auto:" $lockpath2
001727    } {1}
001728  
001729    set env(SQLITE_FORCE_PROXY_LOCKING) $using_proxy
001730    set sqlite_hostid_num 0
001731  }
001732  
001733  # Parsing of auto_vacuum settings.
001734  #
001735  foreach {autovac_setting val} {
001736    0 0
001737    1 1
001738    2 2
001739    3 0
001740    -1 0
001741    none 0
001742    NONE 0
001743    NoNe 0
001744    full 1
001745    FULL 1
001746    incremental 2
001747    INCREMENTAL 2
001748    -1234 0
001749    1234 0
001750  } {
001751    do_test pragma-17.1.$autovac_setting {
001752      catch {db close}
001753      sqlite3 db :memory:
001754      execsql "
001755        PRAGMA auto_vacuum=$::autovac_setting;
001756        PRAGMA auto_vacuum;
001757      "
001758    } $val
001759  }
001760  
001761  # Parsing of temp_store settings.
001762  #
001763  foreach {temp_setting val} {
001764    0 0
001765    1 1
001766    2 2
001767    3 0
001768    -1 0
001769    file 1
001770    FILE 1
001771    fIlE 1
001772    memory 2
001773    MEMORY 2
001774    MeMoRy 2
001775  } {
001776    do_test pragma-18.1.$temp_setting {
001777      catch {db close}
001778      sqlite3 db :memory:
001779      execsql "
001780        PRAGMA temp_store=$::temp_setting;
001781        PRAGMA temp_store=$::temp_setting;
001782        PRAGMA temp_store;
001783      "
001784    } $val
001785  }
001786  
001787  # The SQLITE_FCNTL_PRAGMA logic, with error handling.
001788  #
001789  db close
001790  testvfs tvfs
001791  sqlite3 db test.db -vfs tvfs
001792  do_test pragma-19.1 {
001793    catchsql {PRAGMA error}
001794  } {1 {SQL logic error}}
001795  do_test pragma-19.2 {
001796    catchsql {PRAGMA error='This is the error message'}
001797  } {1 {This is the error message}}
001798  do_test pragma-19.3 {
001799    catchsql {PRAGMA error='7 This is the error message'}
001800  } {1 {This is the error message}}
001801  do_test pragma-19.4 {
001802    catchsql {PRAGMA error=7}
001803  } {1 {out of memory}}
001804  do_test pragma-19.5 {
001805    file tail [lindex [execsql {PRAGMA filename}] 0]
001806  } {test.db}
001807  
001808  if {$tcl_platform(platform)=="windows"} {
001809  # Test data_store_directory pragma
001810  #
001811  db close
001812  sqlite3 db test.db
001813  file mkdir data_dir
001814  do_test pragma-20.1 {
001815    catchsql {PRAGMA data_store_directory}
001816  } {0 {}}
001817  do_test pragma-20.2 {
001818    set pwd [string map {' ''} [file nativename [get_pwd]]]
001819    catchsql "PRAGMA data_store_directory='$pwd';"
001820  } {0 {}}
001821  do_test pragma-20.3 {
001822    catchsql {PRAGMA data_store_directory}
001823  } [list 0 [list [file nativename [get_pwd]]]]
001824  do_test pragma-20.4 {
001825    set pwd [string map {' ''} [file nativename \
001826      [file join [get_pwd] data_dir]]]
001827    catchsql "PRAGMA data_store_directory='$pwd';"
001828  } {0 {}}
001829  do_test pragma-20.5 {
001830    sqlite3 db2 test2.db
001831    catchsql "PRAGMA database_list;" db2
001832  } [list 0 [list 0 main [file nativename \
001833      [file join [get_pwd] data_dir test2.db]]]]
001834  catch {db2 close}
001835  do_test pragma-20.6 {
001836    sqlite3 db2 [file join [get_pwd] test2.db]
001837    catchsql "PRAGMA database_list;" db2
001838  } [list 0 [list 0 main [file nativename \
001839      [file join [get_pwd] test2.db]]]]
001840  catch {db2 close}
001841  do_test pragma-20.7 {
001842    catchsql "PRAGMA data_store_directory='';"
001843  } {0 {}}
001844  do_test pragma-20.8 {
001845    catchsql {PRAGMA data_store_directory}
001846  } {0 {}}
001847  
001848  forcedelete data_dir
001849  } ;# endif windows
001850  
001851  database_may_be_corrupt
001852  if {![nonzero_reserved_bytes]} {
001853  
001854    do_test 21.1 {
001855      # Create a corrupt database in testerr.db. And a non-corrupt at test.db.
001856      #
001857      db close
001858      forcedelete test.db
001859      sqlite3 db test.db
001860      execsql { 
001861        PRAGMA page_size = 1024;
001862        PRAGMA auto_vacuum = 0;
001863        CREATE TABLE t1(a PRIMARY KEY, b);
001864        INSERT INTO t1 VALUES(1, 1);
001865      }
001866      for {set i 0} {$i < 10} {incr i} {
001867        execsql { INSERT INTO t1 SELECT a + (1 << $i), b + (1 << $i) FROM t1 }
001868      }
001869      db close
001870      forcecopy test.db testerr.db
001871      hexio_write testerr.db 15000 [string repeat 55 100]
001872    } {100}
001873    
001874    set mainerr {*** in database main ***
001875  Multiple uses for byte 672 of page 15}
001876    set auxerr {*** in database aux ***
001877  Multiple uses for byte 672 of page 15}
001878    
001879    set mainerr {/{\*\*\* in database main \*\*\*
001880  Multiple uses for byte 672 of page 15}.*/}
001881    set auxerr {/{\*\*\* in database aux \*\*\*
001882  Multiple uses for byte 672 of page 15}.*/}
001883    
001884    do_test 22.2 {
001885      catch { db close }
001886      sqlite3 db testerr.db
001887      execsql { PRAGMA integrity_check }
001888    } $mainerr
001889    
001890    do_test 22.3.1 {
001891      catch { db close }
001892      sqlite3 db test.db
001893      execsql { 
001894        ATTACH 'testerr.db' AS 'aux';
001895        PRAGMA integrity_check;
001896      }
001897    } $auxerr
001898    do_test 22.3.2 {
001899      execsql { PRAGMA main.integrity_check; }
001900    } {ok}
001901    do_test 22.3.3 {
001902      execsql { PRAGMA aux.integrity_check; }
001903    } $auxerr
001904    
001905    do_test 22.4.1 {
001906      catch { db close }
001907      sqlite3 db testerr.db
001908      execsql { 
001909        ATTACH 'test.db' AS 'aux';
001910        PRAGMA integrity_check;
001911      }
001912    } $mainerr
001913    do_test 22.4.2 {
001914      execsql { PRAGMA main.integrity_check; }
001915    } $mainerr
001916    do_test 22.4.3 {
001917      execsql { PRAGMA aux.integrity_check; }
001918    } {ok}
001919  }
001920    
001921  db close
001922  forcedelete test.db test.db-wal test.db-journal
001923  sqlite3 db test.db
001924  sqlite3 db2 test.db
001925  do_test 23.1 {
001926    db eval {
001927      CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d);
001928      CREATE INDEX i1 ON t1(b,c);
001929      CREATE INDEX i2 ON t1(c,d);
001930      CREATE INDEX i2x ON t1(d COLLATE nocase, c DESC);
001931      CREATE INDEX i3 ON t1(d,b+c,c);
001932      CREATE TABLE t2(x INTEGER REFERENCES t1);
001933    }
001934    db2 eval {SELECT name FROM sqlite_master}
001935  } {t1 i1 i2 i2x i3 t2}
001936  do_test 23.2a {
001937    db eval {
001938      DROP INDEX i2;
001939      CREATE INDEX i2 ON t1(c,d,b);
001940    }
001941    capture_pragma db2 out {PRAGMA index_info(i2)}
001942    db2 eval {SELECT cid, name, '|' FROM out ORDER BY seqno}
001943  } {2 c | 3 d | 1 b |}
001944  
001945  # EVIDENCE-OF: R-56143-29319 PRAGMA schema.index_xinfo(index-name); This
001946  # pragma returns information about every column in an index.
001947  #
001948  # EVIDENCE-OF: R-45970-35618 Unlike this index_info pragma, this pragma
001949  # returns information about every column in the index, not just the key
001950  # columns.
001951  #
001952  do_test 23.2b {
001953    capture_pragma db2 out {PRAGMA index_xinfo(i2)}
001954    db2 eval {SELECT cid, name, "desc", coll, "key", '|' FROM out ORDER BY seqno}
001955  } {2 c 0 BINARY 1 | 3 d 0 BINARY 1 | 1 b 0 BINARY 1 | -1 {} 0 BINARY 0 |}
001956  
001957  # (The first column of output from PRAGMA index_xinfo is...)
001958  # EVIDENCE-OF: R-00197-14279 The rank of the column within the index. (0
001959  # means left-most. Key columns come before auxiliary columns.)
001960  #
001961  # (The second column of output from PRAGMA index_xinfo is...)
001962  # EVIDENCE-OF: R-06603-49335 The rank of the column within the table
001963  # being indexed, or -1 if the index-column is the rowid of the table
001964  # being indexed and -2 if the index is on an expression.
001965  #
001966  # (The third column of output from PRAGMA index_xinfo is...)
001967  # EVIDENCE-OF: R-40641-22898 The name of the column being indexed, or
001968  # NULL if the index-column is the rowid of the table being indexed or an
001969  # expression.
001970  #
001971  # (The fourth column of output from PRAGMA index_xinfo is...)
001972  # EVIDENCE-OF: R-11847-09179 1 if the index-column is sorted in reverse
001973  # (DESC) order by the index and 0 otherwise.
001974  #
001975  # (The fifth column of output from PRAGMA index_xinfo is...)
001976  # EVIDENCE-OF: R-15313-19540 The name for the collating sequence used to
001977  # compare values in the index-column.
001978  #
001979  # (The sixth column of output from PRAGMA index_xinfo is...)
001980  # EVIDENCE-OF: R-14310-64553 1 if the index-column is a key column and 0
001981  # if the index-column is an auxiliary column.
001982  #
001983  do_test 23.2c {
001984    db2 eval {PRAGMA index_xinfo(i2)}
001985  } {0 2 c 0 BINARY 1 1 3 d 0 BINARY 1 2 1 b 0 BINARY 1 3 -1 {} 0 BINARY 0}
001986  do_test 23.2d {
001987    db2 eval {PRAGMA index_xinfo(i2x)}
001988  } {0 3 d 0 nocase 1 1 2 c 1 BINARY 1 2 -1 {} 0 BINARY 0}
001989  do_test 23.2e {
001990    db2 eval {PRAGMA index_xinfo(i3)}
001991  } {0 3 d 0 BINARY 1 1 -2 {} 0 BINARY 1 2 2 c 0 BINARY 1 3 -1 {} 0 BINARY 0}
001992  
001993  # EVIDENCE-OF: R-64103-17776 PRAGMA schema.index_list(table-name); This
001994  # pragma returns one row for each index associated with the given table.
001995  #
001996  # (The first column of output from PRAGMA index_list is...)
001997  # EVIDENCE-OF: R-02753-24748 A sequence number assigned to each index
001998  # for internal tracking purposes.
001999  #
002000  # (The second column of output from PRAGMA index_list is...)
002001  # EVIDENCE-OF: R-35496-03635 The name of the index.
002002  #
002003  # (The third column of output from PRAGMA index_list is...)
002004  # EVIDENCE-OF: R-57301-64506 "1" if the index is UNIQUE and "0" if not.
002005  #
002006  # (The fourth column of output from PRAGMA index_list is...)
002007  # EVIDENCE-OF: R-36609-39554 "c" if the index was created by a CREATE
002008  # INDEX statement, "u" if the index was created by a UNIQUE constraint,
002009  # or "pk" if the index was created by a PRIMARY KEY constraint.
002010  #
002011  do_test 23.3 {
002012    db eval {
002013      DROP INDEX IF EXISTS i3;
002014      CREATE INDEX i3 ON t1(d,b,c);
002015    }
002016    capture_pragma db2 out {PRAGMA index_list(t1)}
002017    db2 eval {SELECT seq, name, "unique", origin, '|' FROM out ORDER BY seq}
002018  } {0 i3 0 c | 1 i2 0 c | 2 i2x 0 c | 3 i1 0 c |}
002019  ifcapable altertable {
002020    do_test 23.4 {
002021      db eval {
002022        ALTER TABLE t1 ADD COLUMN e;
002023      }
002024      db2 eval {
002025        PRAGMA table_info(t1);
002026      }
002027    } {/4 e {} 0 {} 0/}
002028  }
002029  do_test 23.5 {
002030    db eval {
002031      DROP TABLE t2;
002032      CREATE TABLE t2(x, y INTEGER REFERENCES t1);
002033    }
002034    db2 eval {
002035      PRAGMA foreign_key_list(t2);
002036    }
002037  } {0 0 t1 y {} {NO ACTION} {NO ACTION} NONE}
002038  db2 close
002039  
002040  ifcapable !has_codec {
002041    reset_db
002042    do_execsql_test 24.0 {
002043      PRAGMA page_size = 1024;
002044      CREATE TABLE t1(a, b, c);
002045      CREATE INDEX i1 ON t1(b);
002046      INSERT INTO t1 VALUES('a', 'b', 'c');
002047      PRAGMA integrity_check;
002048    } {ok}
002049    
002050    set r [db one {SELECT rootpage FROM sqlite_master WHERE name = 't1'}]
002051    db close
002052    hexio_write test.db [expr $r*1024 - 16] 000000000000000701040f0f1f616263
002053    
002054    sqlite3 db test.db
002055    do_catchsql_test 24.1 {
002056      SELECT * FROM t1;
002057    } {1 {database disk image is malformed}}
002058    do_catchsql_test 24.2 {
002059      PRAGMA integrity_check;
002060    } {0 {{database disk image is malformed}}}
002061  }  
002062  database_never_corrupt
002063  
002064  # 2023-03-27.  Register allocation issue in integrity_check discovered
002065  # by new assert() statements added in [6f8b97f31a4c8552].
002066  # dbsqlfuzz dc9ab26037cf5ef797d28cd1ae0855ade584216d
002067  # tag-20230327-1
002068  #
002069  reset_db
002070  do_execsql_test 25.0 {
002071    CREATE TABLE t1(a INT, b AS (a*2) NOT NULL);
002072    CREATE TEMP TABLE t2(a PRIMARY KEY, b, c UNIQUE) WITHOUT ROWID;
002073    CREATE UNIQUE INDEX t2x ON t2(c,b);
002074    PRAGMA integrity_check;
002075  } ok
002076  finish_test