/ Check-in [f9b6dc77]
Login

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: f9b6dc77021ee421bffd5697d5d337d3bbd07eb9
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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