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