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: |
e5b13634d9794e4c75378cea89b64c5e |
| 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
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 }