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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
e5b13634d9794e4c75378cea89b64c5e |
User & Date: | drh 2015-03-05 15:34:15.760 |
Context
2015-03-06
| ||
03:31 | Clarification of documentation on sqlite3_backup. (check-in: 31d5e9b42e 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: e5b13634d9 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: fc543c2c5c user: drh tags: trunk) | |
Changes
Changes to test/index7.test.
︙ | |||
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 | 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 55 56 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; CREATE INDEX t1b ON t1(b) WHERE b>10; CREATE VIRTUAL TABLE nums USING wholenumber; 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 | 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 | + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + | 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 { |
︙ | |||
721 722 723 724 725 726 727 728 729 730 731 732 733 734 | 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 | + + + | {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} |
︙ | |||
1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 | 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 | + - + + + + + + + + + - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + - - + + | 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} |
︙ |