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