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
17
18
19
20
21
22
23
24
25
26
27
28
29
..
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable !vtab {
finish_test
return
}
load_static_extension db wholenumber;
do_test index7-1.1 {
# Able to parse and manage partial indices
execsql {
CREATE TABLE t1(a,b,c PRIMARY KEY) WITHOUT rowid;
CREATE INDEX t1a ON t1(a) WHERE a IS NOT NULL;
................................................................................
INSERT INTO t1(a,b,c)
SELECT CASE WHEN value%3!=0 THEN value END, value, value
FROM nums WHERE value<=20;
SELECT count(a), count(b) FROM t1;
PRAGMA integrity_check;
}
} {14 20 ok}
# Make sure the count(*) optimization works correctly with
# partial indices. Ticket [a5c8ed66cae16243be6] 2013-10-03.
#
do_execsql_test index7-1.1.1 {
SELECT count(*) FROM t1;
} {20}
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
..
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
|
set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable !vtab {
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
}
}
load_static_extension db wholenumber;
do_test index7-1.1 {
# Able to parse and manage partial indices
execsql {
CREATE TABLE t1(a,b,c PRIMARY KEY) WITHOUT rowid;
CREATE INDEX t1a ON t1(a) WHERE a IS NOT NULL;
................................................................................
INSERT INTO t1(a,b,c)
SELECT CASE WHEN value%3!=0 THEN value END, value, value
FROM nums WHERE value<=20;
SELECT count(a), count(b) FROM t1;
PRAGMA integrity_check;
}
} {14 20 ok}
# (The "partial" column of the PRAGMA index_list output is...)
# EVIDENCE-OF: R-34457-09668 "1" if the index is a partial index and "0"
# if not.
#
do_test index7-1.1a {
capture_pragma db out {PRAGMA index_list(t1)}
db eval {SELECT "name", "partial", '|' FROM out ORDER BY "name"}
} {sqlite_autoindex_t1_1 0 | t1a 1 | t1b 1 |}
# Make sure the count(*) optimization works correctly with
# partial indices. Ticket [a5c8ed66cae16243be6] 2013-10-03.
#
do_execsql_test index7-1.1.1 {
SELECT count(*) FROM t1;
} {20}
|
Changes to test/pragma.test.
664 665 666 667 668 669 670 671 672 673 674 675 676 677 ... 721 722 723 724 725 726 727 728 729 730 731 732 733 734 .... 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 |
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}
do_test pragma-6.5.2 {
execsql {
pragma index_info(t3i1_bogus);
}
} {}
ifcapable tempdb {
................................................................................
{3 four REAL 0 X'abcdef' 0} \
{4 five {} 0 CURRENT_TIME 0} \
]
} ;# ifcapable schema_pragmas
# Miscellaneous tests
#
ifcapable schema_pragmas {
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
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}
................................................................................
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 TABLE t2(x INTEGER REFERENCES t1);
}
db2 eval {SELECT name FROM sqlite_master}
} {t1 i1 i2 t2}
do_test 23.2a {
db eval {
DROP INDEX i2;
CREATE INDEX i2 ON t1(c,d,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 |}
do_test 23.2b {
breakpoint;
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 |}
do_test 23.3 {
db eval {
CREATE INDEX i3 ON t1(d,b,c);
}
capture_pragma db2 out {PRAGMA index_list(t1)}
db2 eval {SELECT name, "unique", origin FROM out ORDER BY seq}
} {i3 0 c i2 0 c i1 0 c}
do_test 23.4 {
db eval {
ALTER TABLE t1 ADD COLUMN e;
}
db2 eval {
PRAGMA table_info(t1);
}
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > > < > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > < < > > |
664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 ... 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 .... 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 |
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-62725-03366 PRAGMA database.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 {
................................................................................
{3 four REAL 0 X'abcdef' 0} \
{4 five {} 0 CURRENT_TIME 0} \
]
} ;# ifcapable schema_pragmas
# Miscellaneous tests
#
ifcapable schema_pragmas {
# EVIDENCE-OF: R-63500-32024 PRAGMA database.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
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}
................................................................................
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 i2x t2}
do_test 23.2a {
db eval {
DROP INDEX i2;
CREATE INDEX i2 ON t1(c,d,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-44874-46325 PRAGMA database.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-63500-32024 PRAGMA database.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);
}
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);
}
|