Import SQLite 3.10.2.

third_party/sqlite/src/test/pragma.test

 # 2002 March 6
 #
 # The author disclaims copyright to this source code.  In place of
 # a legal notice, here is a blessing:
 #
 #    May you do good and not evil.
 #    May you find forgiveness for yourself and forgive others.
 #    May you share freely, never taking more than you give.
 #
 #***********************************************************************
@@ skipping 33 matching lines @@
 # pragma-20.*: Test data_store_directory.
 # pragma-22.*: Test that "PRAGMA [db].integrity_check" respects the "db"
 #              directive - if it is present.
 #
 
 ifcapable !pragma {
   finish_test
   return
 }
 
+# Capture the output of a pragma in a TEMP table.
+#
+proc capture_pragma {db tabname sql} {
+  $db eval "DROP TABLE IF EXISTS temp.$tabname"
+  set once 1
+  $db eval $sql x {
+    if {$once} {
+      set once 0
+      set ins "INSERT INTO $tabname VALUES"
+      set crtab "CREATE TEMP TABLE $tabname "
+      set sep "("
+      foreach col $x(*) {
+        append ins ${sep}\$x($col)
+        append crtab ${sep}\"$col\"
+        set sep ,
+      }
+      append ins )
+      append crtab )
+      $db eval $crtab
+    }
+    $db eval $ins
+  }
+}
+
 # Delete the preexisting database to avoid the special setup
 # that the "all.test" script does.
 #
 db close
 delete_file test.db test.db-journal
 delete_file test3.db test3.db-journal
 sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
 
-
+# EVIDENCE-OF: R-13861-56665 PRAGMA schema.cache_size; PRAGMA
+# schema.cache_size = pages; PRAGMA schema.cache_size = -kibibytes;
+# Query or change the suggested maximum number of database disk pages
+# that SQLite will hold in memory at once per open database file.
+#
 ifcapable pager_pragmas {
 set DFLT_CACHE_SZ [db one {PRAGMA default_cache_size}]
 set TEMP_CACHE_SZ [db one {PRAGMA temp.default_cache_size}]
 do_test pragma-1.1 {
   execsql {
     PRAGMA cache_size;
     PRAGMA default_cache_size;
     PRAGMA synchronous;
   }
 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]
 do_test pragma-1.2 {
+  # EVIDENCE-OF: R-42059-47211 If the argument N is positive then the
+  # suggested cache size is set to N.
   execsql {
     PRAGMA synchronous=OFF;
     PRAGMA cache_size=1234;
     PRAGMA cache_size;
     PRAGMA default_cache_size;
     PRAGMA synchronous;
   }
 } [list 1234 $DFLT_CACHE_SZ 0]
 do_test pragma-1.3 {
   db close
@@ skipping 98 matching lines @@
     PRAGMA synchronous=0;
     PRAGMA synchronous;
   }
 } {0}
 do_test pragma-1.14 {
   execsql {
     PRAGMA synchronous=2;
     PRAGMA synchronous;
   }
 } {2}
+do_test pragma-1.14.1 {
+  execsql {
+    PRAGMA synchronous=4;
+    PRAGMA synchronous;
+  }
+} {0}
+do_test pragma-1.14.2 {
+  execsql {
+    PRAGMA synchronous=3;
+    PRAGMA synchronous;
+  }
+} {0}
+do_test pragma-1.14.3 {
+  execsql {
+    PRAGMA synchronous=10;
+    PRAGMA synchronous;
+  }
+} {2}
 } ;# ifcapable pager_pragmas
 
 # Test turning "flag" pragmas on and off.
 #
 ifcapable debug {
   # Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG
   #
   do_test pragma-1.15 {
     execsql {
       PRAGMA vdbe_listing=YES;
@@ skipping 245 matching lines @@
   PRAGMA writable_schema=OFF;
   ALTER TABLE t1 RENAME TO t1x;
   PRAGMA integrity_check;
 } {{non-unique entry in index t1a} {NULL value in t1x.a} {non-unique entry in index t1a} {NULL value in t1x.a}}
 do_execsql_test pragma-3.21 {
   PRAGMA integrity_check(3);
 } {{non-unique entry in index t1a} {NULL value in t1x.a} {non-unique entry in index t1a}}
 do_execsql_test pragma-3.22 {
   PRAGMA integrity_check(2);
 } {{non-unique entry in index t1a} {NULL value in t1x.a}}
-do_execsql_test pragma-3.21 {
+do_execsql_test pragma-3.23 {
   PRAGMA integrity_check(1);
 } {{non-unique entry in index t1a}}
 
+# PRAGMA integrity check (or more specifically the sqlite3BtreeCount()
+# interface) used to leave index cursors in an inconsistent state
+# which could result in an assertion fault in sqlite3BtreeKey()
+# called from saveCursorPosition() if content is removed from the
+# index while the integrity_check is still running.  This test verifies
+# that problem has been fixed.
+#
+do_test pragma-3.30 {
+  db close
+  delete_file test.db
+  sqlite3 db test.db
+  db eval {
+    CREATE TABLE t1(a,b,c);
+    WITH RECURSIVE
+      c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<100)
+    INSERT INTO t1(a,b,c) SELECT i, printf('xyz%08x',i), 2000-i FROM c;
+    CREATE INDEX t1a ON t1(a);
+    CREATE INDEX t1bc ON t1(b,c);
+  }
+  db eval {PRAGMA integrity_check} {
+     db eval {DELETE FROM t1}
+  }
+} {}
+
 # Test modifying the cache_size of an attached database.
 ifcapable pager_pragmas&&attach {
 do_test pragma-4.1 {
   execsql {
     ATTACH 'test2.db' AS aux;
     pragma aux.cache_size;
     pragma aux.default_cache_size;
   } 
 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
 do_test pragma-4.2 {
@@ skipping 118 matching lines @@
     execsql {
       pragma foreign_key_list(t3_bogus);
     }
   } {}
   do_test pragma-6.3.4 {
     execsql {
       pragma foreign_key_list(t5);
     }
   } {}
   do_test pragma-6.4 {
-    execsql {
+    capture_pragma db out {
       pragma index_list(t3);
     }
+    db eval {SELECT seq, "name", "unique" FROM out ORDER BY seq}
   } {0 sqlite_autoindex_t3_1 1}
 }
 ifcapable {!foreignkey} {
   execsql {CREATE TABLE t3(a,b UNIQUE)}
 }
 do_test pragma-6.5.1 {
   execsql {
     CREATE INDEX t3i1 ON t3(a,b);
+  }
+  capture_pragma db out {
     pragma index_info(t3i1);
   }
+  db eval {SELECT seqno, cid, name FROM out ORDER BY seqno}
 } {0 0 a 1 1 b}
+
+# EVIDENCE-OF: R-23114-21695 The auxiliary index-columns are not shown
+# by the index_info pragma, but they are listed by the index_xinfo
+# pragma.
+#
+do_test pragma-6.5.1b {
+  capture_pragma db out {PRAGMA index_xinfo(t3i1)}
+  db eval {SELECT seqno, cid, name FROM out ORDER BY seqno}
+} {0 0 a 1 1 b 2 -1 {}}
+
+
+# EVIDENCE-OF: R-29448-60346 PRAGMA schema.index_info(index-name); This
+# pragma returns one row for each key column in the named index.
+#
+# (The first column of output from PRAGMA index_info is...)
+# EVIDENCE-OF: R-34186-52914 The rank of the column within the index. (0
+# means left-most.)
+#
+# (The second column of output from PRAGMA index_info is...)
+# EVIDENCE-OF: R-65019-08383 The rank of the column within the table
+# being indexed.
+#
+# (The third column of output from PRAGMA index_info is...)
+# EVIDENCE-OF: R-09773-34266 The name of the column being indexed.
+#
+do_execsql_test pragma-6.5.1c {
+  CREATE INDEX t3i2 ON t3(b,a);
+  PRAGMA index_info='t3i2';
+  DROP INDEX t3i2;
+} {0 1 b 1 0 a}
+
 do_test pragma-6.5.2 {
   execsql {
     pragma index_info(t3i1_bogus);
   }
 } {}
 
 ifcapable tempdb {
   # Test for ticket #3320. When a temp table of the same name exists, make
   # sure the schema of the main table can still be queried using 
   # "pragma table_info":
@@ skipping 22 matching lines @@
 
 do_test pragma-6.7 {
   execsql {
     CREATE TABLE test_table(
       one INT NOT NULL DEFAULT -1, 
       two text,
       three VARCHAR(45, 65) DEFAULT 'abcde',
       four REAL DEFAULT X'abcdef',
       five DEFAULT CURRENT_TIME
     );
-    PRAGMA table_info(test_table);
   }
+  capture_pragma db out {PRAGMA table_info(test_table)}
+  db eval {SELECT cid, "name", type, "notnull", dflt_value, pk FROM out
+            ORDER BY cid}
 } [concat \
   {0 one INT 1 -1 0} \
   {1 two text 0 {} 0} \
   {2 three {VARCHAR(45, 65)} 0 'abcde' 0} \
   {3 four REAL 0 X'abcdef' 0} \
   {4 five {} 0 CURRENT_TIME 0} \
 ]
+do_test pragma-6.8 {
+  execsql {
+    CREATE TABLE t68(a,b,c,PRIMARY KEY(a,b,a,c));
+    PRAGMA table_info(t68);
+  }
+} [concat \
+  {0 a {} 0 {} 1} \
+  {1 b {} 0 {} 2} \
+  {2 c {} 0 {} 4} \
+]
 } ;# ifcapable schema_pragmas
 # Miscellaneous tests
 #
 ifcapable schema_pragmas {
+# EVIDENCE-OF: R-64103-17776 PRAGMA schema.index_list(table-name); This
+# pragma returns one row for each index associated with the given table.
+#
 do_test pragma-7.1.1 {
   # Make sure a pragma knows to read the schema if it needs to
   db close
   sqlite3 db test.db
-  execsql {
-    pragma index_list(t3);
-  }
-} {0 t3i1 0 1 sqlite_autoindex_t3_1 1}
+  capture_pragma db out "PRAGMA index_list(t3)"
+  db eval {SELECT name, "origin" FROM out ORDER BY name DESC}
+} {t3i1 c sqlite_autoindex_t3_1 u}
 do_test pragma-7.1.2 {
   execsql {
     pragma index_list(t3_bogus);
   }
 } {}
 } ;# ifcapable schema_pragmas
 ifcapable {utf16} {
   if {[permutation] == ""} {
     do_test pragma-7.2 {
       db close
@@ skipping 497 matching lines @@
   }
 } {1}
 
 } ;# ifcapable trigger
 
 ifcapable schema_pragmas {
   do_test pragma-11.1 {
     execsql2 {
       pragma collation_list;
     }
-  } {seq 0 name NOCASE seq 1 name RTRIM seq 2 name BINARY}
+  } {seq 0 name RTRIM seq 1 name NOCASE seq 2 name BINARY}
   do_test pragma-11.2 {
     db collate New_Collation blah...
     execsql {
       pragma collation_list;
     }
-  } {0 New_Collation 1 NOCASE 2 RTRIM 3 BINARY}
+  } {0 New_Collation 1 RTRIM 2 NOCASE 3 BINARY}
 }
 
 ifcapable schema_pragmas&&tempdb {
   do_test pragma-12.1 {
     sqlite3 db2 test.db
     execsql {
       PRAGMA temp.table_info('abc');
     } db2
   } {}
   db2 close
@@ skipping 40 matching lines @@
     PRAGMA sql_trace=off;
   }
 } {}
 
 } ;# ifcapable bloblit 
 
 ifcapable pager_pragmas {
   db close
   forcedelete test.db
   sqlite3 db test.db
-
+ 
+  # EVIDENCE-OF: R-15672-33611 PRAGMA schema.page_count; Return the total
+  # number of pages in the database file.
+  #
   do_test pragma-14.1 {
     execsql { pragma auto_vacuum = 0 }
-    execsql { pragma page_count }
-  } {0}
+    execsql { pragma page_count; pragma main.page_count }
+  } {0 0}
 
   do_test pragma-14.2 {
     execsql { 
       CREATE TABLE abc(a, b, c);
       PRAGMA page_count;
+      PRAGMA main.page_count;
+      PRAGMA temp.page_count;
     }
-  } {2}
+  } {2 2 0}
   do_test pragma-14.2uc {
     execsql {pragma PAGE_COUNT}
   } {2}
 
   do_test pragma-14.3 {
     execsql { 
       BEGIN;
       CREATE TABLE def(a, b, c);
       PRAGMA page_count;
     }
@@ skipping 388 matching lines @@
 
 db close
 forcedelete test.db test.db-wal test.db-journal
 sqlite3 db test.db
 sqlite3 db2 test.db
 do_test 23.1 {
   db eval {
     CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d);
     CREATE INDEX i1 ON t1(b,c);
     CREATE INDEX i2 ON t1(c,d);
+    CREATE INDEX i2x ON t1(d COLLATE nocase, c DESC);
     CREATE TABLE t2(x INTEGER REFERENCES t1);
   }
   db2 eval {SELECT name FROM sqlite_master}
-} {t1 i1 i2 t2}
-do_test 23.2 {
+} {t1 i1 i2 i2x t2}
+do_test 23.2a {
   db eval {
     DROP INDEX i2;
     CREATE INDEX i2 ON t1(c,d,b);
   }
-  db2 eval {PRAGMA index_info(i2)}
-} {0 2 c 1 3 d 2 1 b}
+  capture_pragma db2 out {PRAGMA index_info(i2)}
+  db2 eval {SELECT cid, name, '|' FROM out ORDER BY seqno}
+} {2 c | 3 d | 1 b |}
+
+# EVIDENCE-OF: R-56143-29319 PRAGMA schema.index_xinfo(index-name); This
+# pragma returns information about every column in an index.
+#
+# EVIDENCE-OF: R-45970-35618 Unlike this index_info pragma, this pragma
+# returns information about every column in the index, not just the key
+# columns.
+#
+do_test 23.2b {
+  capture_pragma db2 out {PRAGMA index_xinfo(i2)}
+  db2 eval {SELECT cid, name, "desc", coll, "key", '|' FROM out ORDER BY seqno}
+} {2 c 0 BINARY 1 | 3 d 0 BINARY 1 | 1 b 0 BINARY 1 | -1 {} 0 BINARY 0 |}
+
+# (The first column of output from PRAGMA index_xinfo is...)
+# EVIDENCE-OF: R-00197-14279 The rank of the column within the index. (0
+# means left-most. Key columns come before auxiliary columns.)
+#
+# (The second column of output from PRAGMA index_xinfo is...)
+# EVIDENCE-OF: R-40889-06838 The rank of the column within the table
+# being indexed, or -1 if the index-column is the rowid of the table
+# being indexed.
+#
+# (The third column of output from PRAGMA index_xinfo is...)
+# EVIDENCE-OF: R-22751-28901 The name of the column being indexed, or
+# NULL if the index-column is the rowid of the table being indexed.
+#
+# (The fourth column of output from PRAGMA index_xinfo is...)
+# EVIDENCE-OF: R-11847-09179 1 if the index-column is sorted in reverse
+# (DESC) order by the index and 0 otherwise.
+#
+# (The fifth column of output from PRAGMA index_xinfo is...)
+# EVIDENCE-OF: R-15313-19540 The name for the collating sequence used to
+# compare values in the index-column.
+#
+# (The sixth column of output from PRAGMA index_xinfo is...)
+# EVIDENCE-OF: R-14310-64553 1 if the index-column is a key column and 0
+# if the index-column is an auxiliary column.
+#
+do_test 23.2c {
+  db2 eval {PRAGMA index_xinfo(i2)}
+} {0 2 c 0 BINARY 1 1 3 d 0 BINARY 1 2 1 b 0 BINARY 1 3 -1 {} 0 BINARY 0}
+do_test 23.2d {
+  db2 eval {PRAGMA index_xinfo(i2x)}
+} {0 3 d 0 nocase 1 1 2 c 1 BINARY 1 2 -1 {} 0 BINARY 0}
+
+# EVIDENCE-OF: R-64103-17776 PRAGMA schema.index_list(table-name); This
+# pragma returns one row for each index associated with the given table.
+#
+# (The first column of output from PRAGMA index_list is...)
+# EVIDENCE-OF: R-02753-24748 A sequence number assigned to each index
+# for internal tracking purposes.
+#
+# (The second column of output from PRAGMA index_list is...)
+# EVIDENCE-OF: R-35496-03635 The name of the index.
+#
+# (The third column of output from PRAGMA index_list is...)
+# EVIDENCE-OF: R-57301-64506 "1" if the index is UNIQUE and "0" if not.
+#
+# (The fourth column of output from PRAGMA index_list is...)
+# EVIDENCE-OF: R-36609-39554 "c" if the index was created by a CREATE
+# INDEX statement, "u" if the index was created by a UNIQUE constraint,
+# or "pk" if the index was created by a PRIMARY KEY constraint.
+#
 do_test 23.3 {
   db eval {
     CREATE INDEX i3 ON t1(d,b,c);
   }
-  db2 eval {PRAGMA index_list(t1)}
-} {0 i3 0 1 i2 0 2 i1 0}
+  capture_pragma db2 out {PRAGMA index_list(t1)}
+  db2 eval {SELECT seq, name, "unique", origin, '|' FROM out ORDER BY seq}
+} {0 i3 0 c | 1 i2 0 c | 2 i2x 0 c | 3 i1 0 c |}
 do_test 23.4 {
   db eval {
     ALTER TABLE t1 ADD COLUMN e;
   }
   db2 eval {
     PRAGMA table_info(t1);
   }
 } {/4 e {} 0 {} 0/}
 do_test 23.5 {
   db eval {
     DROP TABLE t2;
     CREATE TABLE t2(x, y INTEGER REFERENCES t1);
   }
   db2 eval {
     PRAGMA foreign_key_list(t2);
   }
 } {0 0 t1 y {} {NO ACTION} {NO ACTION} NONE}
 
 database_never_corrupt
 finish_test