Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Merge in support for the index_xinfo pragma. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | ota-update |
| Files: | files | file ages | folders |
| SHA1: | f9b6dc77021ee421bffd5697d5d337d3 |
| User & Date: | drh 2015-01-31 02:34:23 |
Context
|
2015-01-31
| ||
| 20:42 | Have ota use imposter tables to write to indexes instead of the sqlite3_index_writer() interface. The error handling in this version is broken in a few small ways. check-in: cdaeab46 user: dan tags: ota-update | |
| 02:34 | Merge in support for the index_xinfo pragma. check-in: f9b6dc77 user: drh tags: ota-update | |
| 02:00 | Add the "index_xinfo" pragma. Add new columns to the "index_info" and "index_list" pragmas. check-in: 30f51d7b user: drh tags: index_xinfo | |
|
2015-01-30
| ||
| 21:00 | Merge the SQLITE_TESTCTRL_IMPOSTER changes from trunk. check-in: 3ed6eb2f user: drh tags: ota-update | |
Changes
Changes to src/pragma.c.
258 258 /* ePragTyp: */ PragTyp_INDEX_INFO, 259 259 /* ePragFlag: */ PragFlag_NeedSchema, 260 260 /* iArg: */ 0 }, 261 261 { /* zName: */ "index_list", 262 262 /* ePragTyp: */ PragTyp_INDEX_LIST, 263 263 /* ePragFlag: */ PragFlag_NeedSchema, 264 264 /* iArg: */ 0 }, 265 + { /* zName: */ "index_xinfo", 266 + /* ePragTyp: */ PragTyp_INDEX_INFO, 267 + /* ePragFlag: */ PragFlag_NeedSchema, 268 + /* iArg: */ 1 }, 265 269 #endif 266 270 #if !defined(SQLITE_OMIT_INTEGRITY_CHECK) 267 271 { /* zName: */ "integrity_check", 268 272 /* ePragTyp: */ PragTyp_INTEGRITY_CHECK, 269 273 /* ePragFlag: */ PragFlag_NeedSchema, 270 274 /* iArg: */ 0 }, 271 275 #endif ................................................................................ 488 492 #if !defined(SQLITE_OMIT_FLAG_PRAGMAS) 489 493 { /* zName: */ "writable_schema", 490 494 /* ePragTyp: */ PragTyp_FLAG, 491 495 /* ePragFlag: */ 0, 492 496 /* iArg: */ SQLITE_WriteSchema|SQLITE_RecoveryMode }, 493 497 #endif 494 498 }; 495 -/* Number of pragmas: 58 on by default, 73 total. */ 499 +/* Number of pragmas: 59 on by default, 74 total. */ 496 500 /* End of the automatically generated pragma table. 497 501 ***************************************************************************/ 498 502 499 503 /* 500 504 ** Interpret the given string as a safety level. Return 0 for OFF, 501 505 ** 1 for ON or NORMAL and 2 for FULL. Return 1 for an empty or 502 506 ** unrecognized string argument. The FULL option is disallowed ................................................................................ 743 747 char *aFcntl[4]; /* Argument to SQLITE_FCNTL_PRAGMA */ 744 748 int iDb; /* Database index for <database> */ 745 749 int lwr, upr, mid = 0; /* Binary search bounds */ 746 750 int rc; /* return value form SQLITE_FCNTL_PRAGMA */ 747 751 sqlite3 *db = pParse->db; /* The database connection */ 748 752 Db *pDb; /* The specific database being pragmaed */ 749 753 Vdbe *v = sqlite3GetVdbe(pParse); /* Prepared statement */ 754 + const struct sPragmaNames *pPragma; 750 755 751 756 if( v==0 ) return; 752 757 sqlite3VdbeRunOnlyOnce(v); 753 758 pParse->nMem = 2; 754 759 755 760 /* Interpret the [database.] part of the pragma statement. iDb is the 756 761 ** index of the database this pragma is being applied to in db.aDb[]. */ ................................................................................ 820 825 if( rc<0 ){ 821 826 upr = mid - 1; 822 827 }else{ 823 828 lwr = mid + 1; 824 829 } 825 830 } 826 831 if( lwr>upr ) goto pragma_out; 832 + pPragma = &aPragmaNames[mid]; 827 833 828 834 /* Make sure the database schema is loaded if the pragma requires that */ 829 - if( (aPragmaNames[mid].mPragFlag & PragFlag_NeedSchema)!=0 ){ 835 + if( (pPragma->mPragFlag & PragFlag_NeedSchema)!=0 ){ 830 836 if( sqlite3ReadSchema(pParse) ) goto pragma_out; 831 837 } 832 838 833 839 /* Jump to the appropriate pragma handler */ 834 - switch( aPragmaNames[mid].ePragTyp ){ 840 + switch( pPragma->ePragTyp ){ 835 841 836 842 #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) && !defined(SQLITE_OMIT_DEPRECATED) 837 843 /* 838 844 ** PRAGMA [database.]default_cache_size 839 845 ** PRAGMA [database.]default_cache_size=N 840 846 ** 841 847 ** The first form reports the current persistent setting for the ................................................................................ 1450 1456 break; 1451 1457 } 1452 1458 #endif /* SQLITE_OMIT_PAGER_PRAGMAS */ 1453 1459 1454 1460 #ifndef SQLITE_OMIT_FLAG_PRAGMAS 1455 1461 case PragTyp_FLAG: { 1456 1462 if( zRight==0 ){ 1457 - returnSingleInt(pParse, aPragmaNames[mid].zName, 1458 - (db->flags & aPragmaNames[mid].iArg)!=0 ); 1463 + returnSingleInt(pParse, pPragma->zName, (db->flags & pPragma->iArg)!=0 ); 1459 1464 }else{ 1460 - int mask = aPragmaNames[mid].iArg; /* Mask of bits to set or clear. */ 1465 + int mask = pPragma->iArg; /* Mask of bits to set or clear. */ 1461 1466 if( db->autoCommit==0 ){ 1462 1467 /* Foreign key support may not be enabled or disabled while not 1463 1468 ** in auto-commit mode. */ 1464 1469 mask &= ~(SQLITE_ForeignKeys); 1465 1470 } 1466 1471 #if SQLITE_USER_AUTHENTICATION 1467 1472 if( db->auth.authLevel==UAUTH_User ){ ................................................................................ 1585 1590 1586 1591 case PragTyp_INDEX_INFO: if( zRight ){ 1587 1592 Index *pIdx; 1588 1593 Table *pTab; 1589 1594 pIdx = sqlite3FindIndex(db, zRight, zDb); 1590 1595 if( pIdx ){ 1591 1596 int i; 1597 + int mx = pPragma->iArg ? pIdx->nColumn : pIdx->nKeyCol; 1592 1598 pTab = pIdx->pTable; 1593 - sqlite3VdbeSetNumCols(v, 3); 1594 - pParse->nMem = 3; 1599 + sqlite3VdbeSetNumCols(v, 6); 1600 + pParse->nMem = 6; 1595 1601 sqlite3CodeVerifySchema(pParse, iDb); 1596 1602 sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "seqno", SQLITE_STATIC); 1597 1603 sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "cid", SQLITE_STATIC); 1598 1604 sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "name", SQLITE_STATIC); 1599 - for(i=0; i<pIdx->nKeyCol; i++){ 1605 + sqlite3VdbeSetColName(v, 3, COLNAME_NAME, "desc", SQLITE_STATIC); 1606 + sqlite3VdbeSetColName(v, 4, COLNAME_NAME, "coll", SQLITE_STATIC); 1607 + sqlite3VdbeSetColName(v, 5, COLNAME_NAME, "key", SQLITE_STATIC); 1608 + for(i=0; i<mx; i++){ 1600 1609 i16 cnum = pIdx->aiColumn[i]; 1601 1610 sqlite3VdbeAddOp2(v, OP_Integer, i, 1); 1602 1611 sqlite3VdbeAddOp2(v, OP_Integer, cnum, 2); 1603 - assert( pTab->nCol>cnum ); 1604 - sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, pTab->aCol[cnum].zName, 0); 1605 - sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 3); 1612 + if( cnum<0 ){ 1613 + sqlite3VdbeAddOp2(v, OP_Null, 0, 3); 1614 + }else{ 1615 + sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, pTab->aCol[cnum].zName, 0); 1616 + } 1617 + sqlite3VdbeAddOp2(v, OP_Integer, pIdx->aSortOrder[i], 4); 1618 + sqlite3VdbeAddOp4(v, OP_String8, 0, 5, 0, pIdx->azColl[i], 0); 1619 + sqlite3VdbeAddOp2(v, OP_Integer, i<pIdx->nKeyCol, 6); 1620 + sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 6); 1606 1621 } 1607 1622 } 1608 1623 } 1609 1624 break; 1610 1625 1611 1626 case PragTyp_INDEX_LIST: if( zRight ){ 1612 1627 Index *pIdx; 1613 1628 Table *pTab; 1614 1629 int i; 1615 1630 pTab = sqlite3FindTable(db, zRight, zDb); 1616 1631 if( pTab ){ 1617 1632 v = sqlite3GetVdbe(pParse); 1618 - sqlite3VdbeSetNumCols(v, 3); 1619 - pParse->nMem = 3; 1633 + sqlite3VdbeSetNumCols(v, 5); 1634 + pParse->nMem = 5; 1620 1635 sqlite3CodeVerifySchema(pParse, iDb); 1621 1636 sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "seq", SQLITE_STATIC); 1622 1637 sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "name", SQLITE_STATIC); 1623 1638 sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "unique", SQLITE_STATIC); 1639 + sqlite3VdbeSetColName(v, 3, COLNAME_NAME, "origin", SQLITE_STATIC); 1640 + sqlite3VdbeSetColName(v, 4, COLNAME_NAME, "partial", SQLITE_STATIC); 1624 1641 for(pIdx=pTab->pIndex, i=0; pIdx; pIdx=pIdx->pNext, i++){ 1642 + const char *azOrigin[] = { "c", "u", "pk" }; 1625 1643 sqlite3VdbeAddOp2(v, OP_Integer, i, 1); 1626 1644 sqlite3VdbeAddOp4(v, OP_String8, 0, 2, 0, pIdx->zName, 0); 1627 1645 sqlite3VdbeAddOp2(v, OP_Integer, IsUniqueIndex(pIdx), 3); 1628 - sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 3); 1646 + sqlite3VdbeAddOp4(v, OP_String8, 0, 4, 0, azOrigin[pIdx->idxType], 0); 1647 + sqlite3VdbeAddOp2(v, OP_Integer, pIdx->pPartIdxWhere!=0, 5); 1648 + sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 5); 1629 1649 } 1630 1650 } 1631 1651 } 1632 1652 break; 1633 1653 1634 1654 case PragTyp_DATABASE_LIST: { 1635 1655 int i; ................................................................................ 2191 2211 ** the schema-version is potentially dangerous and may lead to program 2192 2212 ** crashes or database corruption. Use with caution! 2193 2213 ** 2194 2214 ** The user-version is not used internally by SQLite. It may be used by 2195 2215 ** applications for any purpose. 2196 2216 */ 2197 2217 case PragTyp_HEADER_VALUE: { 2198 - int iCookie = aPragmaNames[mid].iArg; /* Which cookie to read or write */ 2218 + int iCookie = pPragma->iArg; /* Which cookie to read or write */ 2199 2219 sqlite3VdbeUsesBtree(v, iDb); 2200 - if( zRight && (aPragmaNames[mid].mPragFlag & PragFlag_ReadOnly)==0 ){ 2220 + if( zRight && (pPragma->mPragFlag & PragFlag_ReadOnly)==0 ){ 2201 2221 /* Write the specified cookie value */ 2202 2222 static const VdbeOpList setCookie[] = { 2203 2223 { OP_Transaction, 0, 1, 0}, /* 0 */ 2204 2224 { OP_Integer, 0, 1, 0}, /* 1 */ 2205 2225 { OP_SetCookie, 0, 0, 1}, /* 2 */ 2206 2226 }; 2207 2227 int addr = sqlite3VdbeAddOpList(v, ArraySize(setCookie), setCookie, 0); ................................................................................ 2313 2333 ** 2314 2334 ** Call sqlite3_busy_timeout(db, N). Return the current timeout value 2315 2335 ** if one is set. If no busy handler or a different busy handler is set 2316 2336 ** then 0 is returned. Setting the busy_timeout to 0 or negative 2317 2337 ** disables the timeout. 2318 2338 */ 2319 2339 /*case PragTyp_BUSY_TIMEOUT*/ default: { 2320 - assert( aPragmaNames[mid].ePragTyp==PragTyp_BUSY_TIMEOUT ); 2340 + assert( pPragma->ePragTyp==PragTyp_BUSY_TIMEOUT ); 2321 2341 if( zRight ){ 2322 2342 sqlite3_busy_timeout(db, sqlite3Atoi(zRight)); 2323 2343 } 2324 2344 returnSingleInt(pParse, "timeout", db->busyTimeout); 2325 2345 break; 2326 2346 } 2327 2347
Changes to test/pragma.test.
46 46 # directive - if it is present. 47 47 # 48 48 49 49 ifcapable !pragma { 50 50 finish_test 51 51 return 52 52 } 53 + 54 +# Capture the output of a pragma in a TEMP table. 55 +# 56 +proc capture_pragma {db tabname sql} { 57 + $db eval "DROP TABLE IF EXISTS temp.$tabname" 58 + set once 1 59 + $db eval $sql x { 60 + if {$once} { 61 + set once 0 62 + set ins "INSERT INTO $tabname VALUES" 63 + set crtab "CREATE TEMP TABLE $tabname " 64 + set sep "(" 65 + foreach col $x(*) { 66 + append ins ${sep}\$x($col) 67 + append crtab ${sep}\"$col\" 68 + set sep , 69 + } 70 + append ins ) 71 + append crtab ) 72 + $db eval $crtab 73 + } 74 + $db eval $ins 75 + } 76 +} 53 77 54 78 # Delete the preexisting database to avoid the special setup 55 79 # that the "all.test" script does. 56 80 # 57 81 db close 58 82 delete_file test.db test.db-journal 59 83 delete_file test3.db test3.db-journal ................................................................................ 616 640 } {} 617 641 do_test pragma-6.3.4 { 618 642 execsql { 619 643 pragma foreign_key_list(t5); 620 644 } 621 645 } {} 622 646 do_test pragma-6.4 { 623 - execsql { 647 + capture_pragma db out { 624 648 pragma index_list(t3); 625 649 } 650 + db eval {SELECT seq, "name", "unique" FROM out ORDER BY seq} 626 651 } {0 sqlite_autoindex_t3_1 1} 627 652 } 628 653 ifcapable {!foreignkey} { 629 654 execsql {CREATE TABLE t3(a,b UNIQUE)} 630 655 } 631 656 do_test pragma-6.5.1 { 632 657 execsql { 633 658 CREATE INDEX t3i1 ON t3(a,b); 659 + } 660 + capture_pragma db out { 634 661 pragma index_info(t3i1); 635 662 } 663 + db eval {SELECT seqno, cid, name FROM out ORDER BY seqno} 636 664 } {0 0 a 1 1 b} 637 665 do_test pragma-6.5.2 { 638 666 execsql { 639 667 pragma index_info(t3i1_bogus); 640 668 } 641 669 } {} 642 670 ................................................................................ 672 700 CREATE TABLE test_table( 673 701 one INT NOT NULL DEFAULT -1, 674 702 two text, 675 703 three VARCHAR(45, 65) DEFAULT 'abcde', 676 704 four REAL DEFAULT X'abcdef', 677 705 five DEFAULT CURRENT_TIME 678 706 ); 679 - PRAGMA table_info(test_table); 680 707 } 708 + capture_pragma db out {PRAGMA table_info(test_table)} 709 + db eval {SELECT cid, "name", type, "notnull", dflt_value, pk FROM out 710 + ORDER BY cid} 681 711 } [concat \ 682 712 {0 one INT 1 -1 0} \ 683 713 {1 two text 0 {} 0} \ 684 714 {2 three {VARCHAR(45, 65)} 0 'abcde' 0} \ 685 715 {3 four REAL 0 X'abcdef' 0} \ 686 716 {4 five {} 0 CURRENT_TIME 0} \ 687 717 ] ................................................................................ 689 719 # Miscellaneous tests 690 720 # 691 721 ifcapable schema_pragmas { 692 722 do_test pragma-7.1.1 { 693 723 # Make sure a pragma knows to read the schema if it needs to 694 724 db close 695 725 sqlite3 db test.db 696 - execsql { 697 - pragma index_list(t3); 698 - } 699 -} {0 t3i1 0 1 sqlite_autoindex_t3_1 1} 726 + capture_pragma db out "PRAGMA index_list(t3)" 727 + db eval {SELECT name, "origin" FROM out ORDER BY name DESC} 728 +} {t3i1 c sqlite_autoindex_t3_1 u} 700 729 do_test pragma-7.1.2 { 701 730 execsql { 702 731 pragma index_list(t3_bogus); 703 732 } 704 733 } {} 705 734 } ;# ifcapable schema_pragmas 706 735 ifcapable {utf16} { ................................................................................ 1701 1730 CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d); 1702 1731 CREATE INDEX i1 ON t1(b,c); 1703 1732 CREATE INDEX i2 ON t1(c,d); 1704 1733 CREATE TABLE t2(x INTEGER REFERENCES t1); 1705 1734 } 1706 1735 db2 eval {SELECT name FROM sqlite_master} 1707 1736 } {t1 i1 i2 t2} 1708 -do_test 23.2 { 1737 +do_test 23.2a { 1709 1738 db eval { 1710 1739 DROP INDEX i2; 1711 1740 CREATE INDEX i2 ON t1(c,d,b); 1712 1741 } 1713 - db2 eval {PRAGMA index_info(i2)} 1714 -} {0 2 c 1 3 d 2 1 b} 1742 + capture_pragma db2 out {PRAGMA index_info(i2)} 1743 + db2 eval {SELECT cid, name, "desc", coll, "key", '|' FROM out ORDER BY seqno} 1744 +} {2 c 0 BINARY 1 | 3 d 0 BINARY 1 | 1 b 0 BINARY 1 |} 1745 +do_test 23.2b { 1746 + capture_pragma db2 out {PRAGMA index_xinfo(i2)} 1747 + db2 eval {SELECT cid, name, "desc", coll, "key", '|' FROM out ORDER BY seqno} 1748 +} {2 c 0 BINARY 1 | 3 d 0 BINARY 1 | 1 b 0 BINARY 1 | -1 {} 0 BINARY 0 |} 1715 1749 do_test 23.3 { 1716 1750 db eval { 1717 1751 CREATE INDEX i3 ON t1(d,b,c); 1718 1752 } 1719 - db2 eval {PRAGMA index_list(t1)} 1720 -} {0 i3 0 1 i2 0 2 i1 0} 1753 + capture_pragma db2 out {PRAGMA index_list(t1)} 1754 + db2 eval {SELECT name, "unique", origin FROM out ORDER BY seq} 1755 +} {i3 0 c i2 0 c i1 0 c} 1721 1756 do_test 23.4 { 1722 1757 db eval { 1723 1758 ALTER TABLE t1 ADD COLUMN e; 1724 1759 } 1725 1760 db2 eval { 1726 1761 PRAGMA table_info(t1); 1727 1762 }
Changes to tool/mkpragmatab.tcl.
201 201 IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) 202 202 203 203 NAME: stats 204 204 FLAG: NeedSchema 205 205 IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) 206 206 207 207 NAME: index_info 208 + TYPE: INDEX_INFO 209 + ARG: 0 210 + FLAG: NeedSchema 211 + IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) 212 + 213 + NAME: index_xinfo 214 + TYPE: INDEX_INFO 215 + ARG: 1 208 216 FLAG: NeedSchema 209 217 IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) 210 218 211 219 NAME: index_list 212 220 FLAG: NeedSchema 213 221 IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) 214 222