/ Check-in [e5b13634]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:New test cases and requirements marks for PRAGMA index_info, index_xinfo, and index_list.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e5b13634d9794e4c75378cea89b64c5ecc5aa3e5
User & Date: drh 2015-03-05 15:34:15
Context
2015-03-06
03:31
Clarification of documentation on sqlite3_backup. check-in: 31d5e9b4 user: drh tags: trunk
2015-03-05
15:34
New test cases and requirements marks for PRAGMA index_info, index_xinfo, and index_list. check-in: e5b13634 user: drh tags: trunk
14:29
Revert "PRAGMA index_info" to output only three columns, for complete compatibility with prior versions. The new "PRAGMA index_xinfo" can be used to get the extra information in 4th, 5th, and 6th columns. check-in: fc543c2c user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/index7.test.

    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   ifcapable !vtab {
    20     20     finish_test
    21     21     return
    22     22   }
           23  +
           24  +# Capture the output of a pragma in a TEMP table.
           25  +#
           26  +proc capture_pragma {db tabname sql} {
           27  +  $db eval "DROP TABLE IF EXISTS temp.$tabname"
           28  +  set once 1
           29  +  $db eval $sql x {
           30  +    if {$once} {
           31  +      set once 0
           32  +      set ins "INSERT INTO $tabname VALUES"
           33  +      set crtab "CREATE TEMP TABLE $tabname "
           34  +      set sep "("
           35  +      foreach col $x(*) {
           36  +        append ins ${sep}\$x($col)
           37  +        append crtab ${sep}\"$col\"
           38  +        set sep ,
           39  +      }
           40  +      append ins )
           41  +      append crtab )
           42  +      $db eval $crtab
           43  +    }
           44  +    $db eval $ins
           45  +  }
           46  +}
           47  +
    23     48   
    24     49   load_static_extension db wholenumber;
    25     50   do_test index7-1.1 {
    26     51     # Able to parse and manage partial indices
    27     52     execsql {
    28     53       CREATE TABLE t1(a,b,c PRIMARY KEY) WITHOUT rowid;
    29     54       CREATE INDEX t1a ON t1(a) WHERE a IS NOT NULL;
................................................................................
    32     57       INSERT INTO t1(a,b,c)
    33     58          SELECT CASE WHEN value%3!=0 THEN value END, value, value
    34     59            FROM nums WHERE value<=20;
    35     60       SELECT count(a), count(b) FROM t1;
    36     61       PRAGMA integrity_check;
    37     62     }
    38     63   } {14 20 ok}
           64  +
           65  +# (The "partial" column of the PRAGMA index_list output is...)
           66  +# EVIDENCE-OF: R-34457-09668 "1" if the index is a partial index and "0"
           67  +# if not.
           68  +#
           69  +do_test index7-1.1a {
           70  +  capture_pragma db out {PRAGMA index_list(t1)}
           71  +  db eval {SELECT "name", "partial", '|' FROM out ORDER BY "name"}
           72  +} {sqlite_autoindex_t1_1 0 | t1a 1 | t1b 1 |}
    39     73   
    40     74   # Make sure the count(*) optimization works correctly with
    41     75   # partial indices.  Ticket [a5c8ed66cae16243be6] 2013-10-03.
    42     76   #
    43     77   do_execsql_test index7-1.1.1 {
    44     78     SELECT count(*) FROM t1;
    45     79   } {20}

Changes to test/pragma.test.

   664    664       CREATE INDEX t3i1 ON t3(a,b);
   665    665     }
   666    666     capture_pragma db out {
   667    667       pragma index_info(t3i1);
   668    668     }
   669    669     db eval {SELECT seqno, cid, name FROM out ORDER BY seqno}
   670    670   } {0 0 a 1 1 b}
          671  +
          672  +# EVIDENCE-OF: R-23114-21695 The auxiliary index-columns are not shown
          673  +# by the index_info pragma, but they are listed by the index_xinfo
          674  +# pragma.
          675  +#
          676  +do_test pragma-6.5.1b {
          677  +  capture_pragma db out {PRAGMA index_xinfo(t3i1)}
          678  +  db eval {SELECT seqno, cid, name FROM out ORDER BY seqno}
          679  +} {0 0 a 1 1 b 2 -1 {}}
          680  +
          681  +
          682  +# EVIDENCE-OF: R-62725-03366 PRAGMA database.index_info(index-name);
          683  +# This pragma returns one row for each key column in the named index.
          684  +#
          685  +# (The first column of output from PRAGMA index_info is...)
          686  +# EVIDENCE-OF: R-34186-52914 The rank of the column within the index. (0
          687  +# means left-most.)
          688  +#
          689  +# (The second column of output from PRAGMA index_info is...)
          690  +# EVIDENCE-OF: R-65019-08383 The rank of the column within the table
          691  +# being indexed.
          692  +#
          693  +# (The third column of output from PRAGMA index_info is...)
          694  +# EVIDENCE-OF: R-09773-34266 The name of the column being indexed.
          695  +#
          696  +do_execsql_test pragma-6.5.1c {
          697  +  CREATE INDEX t3i2 ON t3(b,a);
          698  +  PRAGMA index_info='t3i2';
          699  +  DROP INDEX t3i2;
          700  +} {0 1 b 1 0 a}
          701  +
   671    702   do_test pragma-6.5.2 {
   672    703     execsql {
   673    704       pragma index_info(t3i1_bogus);
   674    705     }
   675    706   } {}
   676    707   
   677    708   ifcapable tempdb {
................................................................................
   721    752     {3 four REAL 0 X'abcdef' 0} \
   722    753     {4 five {} 0 CURRENT_TIME 0} \
   723    754   ]
   724    755   } ;# ifcapable schema_pragmas
   725    756   # Miscellaneous tests
   726    757   #
   727    758   ifcapable schema_pragmas {
          759  +# EVIDENCE-OF: R-63500-32024 PRAGMA database.index_list(table-name);
          760  +# This pragma returns one row for each index associated with the given
          761  +# table.
   728    762   do_test pragma-7.1.1 {
   729    763     # Make sure a pragma knows to read the schema if it needs to
   730    764     db close
   731    765     sqlite3 db test.db
   732    766     capture_pragma db out "PRAGMA index_list(t3)"
   733    767     db eval {SELECT name, "origin" FROM out ORDER BY name DESC}
   734    768   } {t3i1 c sqlite_autoindex_t3_1 u}
................................................................................
  1737   1771   sqlite3 db test.db
  1738   1772   sqlite3 db2 test.db
  1739   1773   do_test 23.1 {
  1740   1774     db eval {
  1741   1775       CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d);
  1742   1776       CREATE INDEX i1 ON t1(b,c);
  1743   1777       CREATE INDEX i2 ON t1(c,d);
         1778  +    CREATE INDEX i2x ON t1(d COLLATE nocase, c DESC);
  1744   1779       CREATE TABLE t2(x INTEGER REFERENCES t1);
  1745   1780     }
  1746   1781     db2 eval {SELECT name FROM sqlite_master}
  1747         -} {t1 i1 i2 t2}
         1782  +} {t1 i1 i2 i2x t2}
  1748   1783   do_test 23.2a {
  1749   1784     db eval {
  1750   1785       DROP INDEX i2;
  1751   1786       CREATE INDEX i2 ON t1(c,d,b);
  1752   1787     }
  1753   1788     capture_pragma db2 out {PRAGMA index_info(i2)}
  1754   1789     db2 eval {SELECT cid, name, '|' FROM out ORDER BY seqno}
  1755   1790   } {2 c | 3 d | 1 b |}
         1791  +
         1792  +# EVIDENCE-OF: R-44874-46325 PRAGMA database.index_xinfo(index-name);
         1793  +# This pragma returns information about every column in an index.
         1794  +#
         1795  +# EVIDENCE-OF: R-45970-35618 Unlike this index_info pragma, this pragma
         1796  +# returns information about every column in the index, not just the key
         1797  +# columns.
         1798  +#
  1756   1799   do_test 23.2b {
  1757         -breakpoint;
  1758   1800     capture_pragma db2 out {PRAGMA index_xinfo(i2)}
  1759   1801     db2 eval {SELECT cid, name, "desc", coll, "key", '|' FROM out ORDER BY seqno}
  1760   1802   } {2 c 0 BINARY 1 | 3 d 0 BINARY 1 | 1 b 0 BINARY 1 | -1 {} 0 BINARY 0 |}
         1803  +
         1804  +# (The first column of output from PRAGMA index_xinfo is...)
         1805  +# EVIDENCE-OF: R-00197-14279 The rank of the column within the index. (0
         1806  +# means left-most. Key columns come before auxiliary columns.)
         1807  +#
         1808  +# (The second column of output from PRAGMA index_xinfo is...)
         1809  +# EVIDENCE-OF: R-40889-06838 The rank of the column within the table
         1810  +# being indexed, or -1 if the index-column is the rowid of the table
         1811  +# being indexed.
         1812  +#
         1813  +# (The third column of output from PRAGMA index_xinfo is...)
         1814  +# EVIDENCE-OF: R-22751-28901 The name of the column being indexed, or
         1815  +# NULL if the index-column is the rowid of the table being indexed.
         1816  +#
         1817  +# (The fourth column of output from PRAGMA index_xinfo is...)
         1818  +# EVIDENCE-OF: R-11847-09179 1 if the index-column is sorted in reverse
         1819  +# (DESC) order by the index and 0 otherwise.
         1820  +#
         1821  +# (The fifth column of output from PRAGMA index_xinfo is...)
         1822  +# EVIDENCE-OF: R-15313-19540 The name for the collating sequence used to
         1823  +# compare values in the index-column.
         1824  +#
         1825  +# (The sixth column of output from PRAGMA index_xinfo is...)
         1826  +# EVIDENCE-OF: R-14310-64553 1 if the index-column is a key column and 0
         1827  +# if the index-column is an auxiliary column.
         1828  +#
         1829  +do_test 23.2c {
         1830  +  db2 eval {PRAGMA index_xinfo(i2)}
         1831  +} {0 2 c 0 BINARY 1 1 3 d 0 BINARY 1 2 1 b 0 BINARY 1 3 -1 {} 0 BINARY 0}
         1832  +do_test 23.2d {
         1833  +  db2 eval {PRAGMA index_xinfo(i2x)}
         1834  +} {0 3 d 0 nocase 1 1 2 c 1 BINARY 1 2 -1 {} 0 BINARY 0}
         1835  +
         1836  +# EVIDENCE-OF: R-63500-32024 PRAGMA database.index_list(table-name);
         1837  +# This pragma returns one row for each index associated with the given
         1838  +# table.
         1839  +#
         1840  +# (The first column of output from PRAGMA index_list is...)
         1841  +# EVIDENCE-OF: R-02753-24748 A sequence number assigned to each index
         1842  +# for internal tracking purposes.
         1843  +#
         1844  +# (The second column of output from PRAGMA index_list is...)
         1845  +# EVIDENCE-OF: R-35496-03635 The name of the index.
         1846  +#
         1847  +# (The third column of output from PRAGMA index_list is...)
         1848  +# EVIDENCE-OF: R-57301-64506 "1" if the index is UNIQUE and "0" if not.
         1849  +#
         1850  +# (The fourth column of output from PRAGMA index_list is...)
         1851  +# EVIDENCE-OF: R-36609-39554 "c" if the index was created by a CREATE
         1852  +# INDEX statement, "u" if the index was created by a UNIQUE constraint,
         1853  +# or "pk" if the index was created by a PRIMARY KEY constraint.
         1854  +#
  1761   1855   do_test 23.3 {
  1762   1856     db eval {
  1763   1857       CREATE INDEX i3 ON t1(d,b,c);
  1764   1858     }
  1765   1859     capture_pragma db2 out {PRAGMA index_list(t1)}
  1766         -  db2 eval {SELECT name, "unique", origin FROM out ORDER BY seq}
  1767         -} {i3 0 c i2 0 c i1 0 c}
         1860  +  db2 eval {SELECT seq, name, "unique", origin, '|' FROM out ORDER BY seq}
         1861  +} {0 i3 0 c | 1 i2 0 c | 2 i2x 0 c | 3 i1 0 c |}
  1768   1862   do_test 23.4 {
  1769   1863     db eval {
  1770   1864       ALTER TABLE t1 ADD COLUMN e;
  1771   1865     }
  1772   1866     db2 eval {
  1773   1867       PRAGMA table_info(t1);
  1774   1868     }