/ Check-in [b7544bb2]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Store the root page of the PRIMARY KEY index for a WITHOUT ROWID table in the sqlite_master entry for the main table and omit the sqlite_master entry for the PRIMARY KEY.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | omit-rowid
Files: files | file ages | folders
SHA1:b7544bb280f1c1c55135a9b35aeb85604fef94a3
User & Date: drh 2013-11-02 14:37:18
Context
2013-11-02
18:46
Update the ANALYZE logic so that it works with WITHOUT ROWID tables. check-in: 9075770e user: drh tags: omit-rowid
14:37
Store the root page of the PRIMARY KEY index for a WITHOUT ROWID table in the sqlite_master entry for the main table and omit the sqlite_master entry for the PRIMARY KEY. check-in: b7544bb2 user: drh tags: omit-rowid
11:43
Import the sqlite3_analyzer fixes from trunk. check-in: ac711459 user: drh tags: omit-rowid
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  1271   1271       if( pList ) pParse->iPkSortOrder = pList->a[0].sortOrder;
  1272   1272     }else if( autoInc ){
  1273   1273   #ifndef SQLITE_OMIT_AUTOINCREMENT
  1274   1274       sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on an "
  1275   1275          "INTEGER PRIMARY KEY");
  1276   1276   #endif
  1277   1277     }else{
         1278  +    Vdbe *v = pParse->pVdbe;
  1278   1279       Index *p;
         1280  +    if( v ) pParse->addrSkipPK = sqlite3VdbeAddOp0(v, OP_Noop);
  1279   1281       p = sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0,
  1280   1282                              0, sortOrder, 0);
  1281   1283       if( p ){
  1282   1284         p->autoIndex = 2;
         1285  +      if( v ) sqlite3VdbeJumpHere(v, pParse->addrSkipPK);
  1283   1286       }
  1284   1287       pList = 0;
  1285   1288     }
  1286   1289   
  1287   1290   primary_key_exit:
  1288   1291     sqlite3ExprListDelete(pParse->db, pList);
  1289   1292     return;
................................................................................
  1586   1589   */
  1587   1590   static int hasColumn(const i16 *aiCol, int nCol, int x){
  1588   1591     while( nCol-- > 0 ) if( x==*(aiCol++) ) return 1;
  1589   1592     return 0;
  1590   1593   }
  1591   1594   
  1592   1595   /*
  1593         -** The table pTab has a WITHOUT ROWID clause at the end.  Go through and
  1594         -** make all the changes necessary to make this a WITHOUT ROWID table.
         1596  +** This routine runs at the end of parsing a CREATE TABLE statement that
         1597  +** has a WITHOUT ROWID clause.  The job of this routine is to convert both
         1598  +** internal schema data structures and the generated VDBE code so that they
         1599  +** are appropriate for a WITHOUT ROWID table instead of a rowid table.
         1600  +** Changes include:
  1595   1601   **
  1596         -**     (1)  Convert the OP_CreateTable into an no-op.
  1597         -**     (2)  Make sure all table columns are part of the PRIMARY KEY
  1598         -**     (3)  Make sure all PRIMARY KEY columns are part of all UNIQUE
  1599         -**          indices
         1602  +**     (1)  Convert the OP_CreateTable into an OP_CreateIndex.  There is
         1603  +**          no rowid btree for a WITHOUT ROWID.  Instead, the canonical
         1604  +**          data storage is a covering index btree.
         1605  +**     (2)  Bypass the creation of the sqlite_master table entry
         1606  +**          for the PRIMARY KEY as the the primary key index is now
         1607  +**          identified by the sqlite_master table entry of the table itself.
         1608  +**     (3)  Set the Index.tnum of the PRIMARY KEY Index object in the
         1609  +**          schema to the rootpage from the main table.
         1610  +**     (4)  Set all columns of the PRIMARY KEY schema object to be NOT NULL.
         1611  +**     (5)  Add all table columns to the PRIMARY KEY Index object
         1612  +**          so that the PRIMARY KEY is a covering index.  The surplus
         1613  +**          columns are part of KeyInfo.nXField and are not used for
         1614  +**          sorting or lookup or uniqueness checks.
         1615  +**     (6)  Replace the rowid tail on all automatically generated UNIQUE
         1616  +**          indices with the PRIMARY KEY columns.
  1600   1617   */
  1601   1618   static void convertToWithoutRowidTable(Parse *pParse, Table *pTab){
  1602   1619     Index *pIdx;
  1603   1620     Index *pPk;
  1604   1621     int nPk;
  1605   1622     int i, j;
  1606   1623     sqlite3 *db = pParse->db;
         1624  +  Vdbe *v = pParse->pVdbe;
  1607   1625   
  1608   1626     /* Convert the OP_CreateTable opcode that would normally create the
  1609         -  ** root-page for the table into a OP_Null opcode.  This prevents the
  1610         -  ** allocation of the root-page (which would never been used, as all
  1611         -  ** content is stored in the primary-key index instead) and it causes
  1612         -  ** a NULL value in the sqlite_master.rootpage field of the schema.
         1627  +  ** root-page for the table into a OP_CreateIndex opcode.  The index
         1628  +  ** created will become the PRIMARY KEY index.
  1613   1629     */
  1614   1630     if( pParse->addrCrTab ){
  1615         -    sqlite3VdbeGetOp(pParse->pVdbe, pParse->addrCrTab)->opcode = OP_Null;
         1631  +    assert( v );
         1632  +    sqlite3VdbeGetOp(v, pParse->addrCrTab)->opcode = OP_CreateIndex;
         1633  +  }
         1634  +
         1635  +  /* Bypass the creation of the PRIMARY KEY btree and the sqlite_master
         1636  +  ** table entry.
         1637  +  */
         1638  +  if( pParse->addrSkipPK ){
         1639  +    assert( v );
         1640  +    sqlite3VdbeGetOp(v, pParse->addrSkipPK)->opcode = OP_Goto;
  1616   1641     }
  1617   1642   
  1618   1643     /* Locate the PRIMARY KEY index.  Or, if this table was originally
  1619   1644     ** an INTEGER PRIMARY KEY table, create a new PRIMARY KEY index. 
  1620   1645     */
  1621   1646     if( pTab->iPKey>=0 ){
  1622   1647       ExprList *pList;
................................................................................
  1636   1661     nPk = pPk->nKeyCol;
  1637   1662   
  1638   1663     /* Make sure every column of the PRIMARY KEY is NOT NULL */
  1639   1664     for(i=0; i<nPk; i++){
  1640   1665       pTab->aCol[pPk->aiColumn[i]].notNull = 1;
  1641   1666     }
  1642   1667     pPk->uniqNotNull = 1;
         1668  +
         1669  +  /* The root page of the PRIMARY KEY is the table root page */
         1670  +  pPk->tnum = pTab->tnum;
  1643   1671   
  1644   1672     /* Update the in-memory representation of all UNIQUE indices by converting
  1645   1673     ** the final rowid column into one or more columns of the PRIMARY KEY.
  1646   1674     */
  1647   1675     for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
  1648   1676       int n;
  1649   1677       if( pIdx->autoIndex==2 ) continue;
................................................................................
  1719   1747       return;
  1720   1748     }
  1721   1749     p = pParse->pNewTable;
  1722   1750     if( p==0 ) return;
  1723   1751   
  1724   1752     assert( !db->init.busy || !pSelect );
  1725   1753   
         1754  +  /* If the db->init.busy is 1 it means we are reading the SQL off the
         1755  +  ** "sqlite_master" or "sqlite_temp_master" table on the disk.
         1756  +  ** So do not write to the disk again.  Extract the root page number
         1757  +  ** for the table from the db->init.newTnum field.  (The page number
         1758  +  ** should have been put there by the sqliteOpenCb routine.)
         1759  +  */
         1760  +  if( db->init.busy ){
         1761  +    p->tnum = db->init.newTnum;
         1762  +  }
         1763  +
         1764  +  /* Special processing for WITHOUT ROWID Tables */
  1726   1765     if( tabOpts & TF_WithoutRowid ){
  1727   1766       if( (p->tabFlags & TF_HasPrimaryKey)==0 ){
  1728   1767         sqlite3ErrorMsg(pParse, "no PRIMARY KEY for table %s", p->zName);
  1729   1768       }else{
  1730   1769         p->tabFlags |= TF_WithoutRowid;
  1731   1770         convertToWithoutRowidTable(pParse, p);
  1732   1771       }
................................................................................
  1744   1783   
  1745   1784     /* Estimate the average row size for the table and for all implied indices */
  1746   1785     estimateTableWidth(p);
  1747   1786     for(pIdx=p->pIndex; pIdx; pIdx=pIdx->pNext){
  1748   1787       estimateIndexWidth(pIdx);
  1749   1788     }
  1750   1789   
  1751         -  /* If the db->init.busy is 1 it means we are reading the SQL off the
  1752         -  ** "sqlite_master" or "sqlite_temp_master" table on the disk.
  1753         -  ** So do not write to the disk again.  Extract the root page number
  1754         -  ** for the table from the db->init.newTnum field.  (The page number
  1755         -  ** should have been put there by the sqliteOpenCb routine.)
  1756         -  */
  1757         -  if( db->init.busy ){
  1758         -    p->tnum = db->init.newTnum;
  1759         -  }
  1760         -
  1761   1790     /* If not initializing, then create a record for the new table
  1762   1791     ** in the SQLITE_MASTER table of the database.
  1763   1792     **
  1764   1793     ** If this is a TEMPORARY table, write the entry into the auxiliary
  1765   1794     ** file instead of into the main database file.
  1766   1795     */
  1767   1796     if( !db->init.busy ){

Changes to src/prepare.c.

    61     61     if( db->mallocFailed ){
    62     62       corruptSchema(pData, argv[0], 0);
    63     63       return 1;
    64     64     }
    65     65   
    66     66     assert( iDb>=0 && iDb<db->nDb );
    67     67     if( argv==0 ) return 0;   /* Might happen if EMPTY_RESULT_CALLBACKS are on */
    68         -  if( argv[2] && argv[2][0] ){
           68  +  if( argv[1]==0 ){
           69  +    corruptSchema(pData, argv[0], 0);
           70  +  }else if( argv[2] && argv[2][0] ){
    69     71       /* Call the parser to process a CREATE TABLE, INDEX or VIEW.
    70     72       ** But because db->init.busy is set to 1, no VDBE code is generated
    71     73       ** or executed.  All the parser does is build the internal data
    72     74       ** structures that describe the table, index, or view.
    73     75       */
    74     76       int rc;
    75     77       sqlite3_stmt *pStmt;
    76     78       TESTONLY(int rcp);            /* Return code from sqlite3_prepare() */
    77     79   
    78     80       assert( db->init.busy );
    79     81       db->init.iDb = iDb;
    80         -    db->init.newTnum = argv[1] ? sqlite3Atoi(argv[1]) : 0;
           82  +    db->init.newTnum = sqlite3Atoi(argv[1]);
    81     83       db->init.orphanTrigger = 0;
    82     84       TESTONLY(rcp = ) sqlite3_prepare(db, argv[2], -1, &pStmt, 0);
    83     85       rc = db->errCode;
    84     86       assert( (rc&0xFF)==(rcp&0xFF) );
    85     87       db->init.iDb = 0;
    86     88       if( SQLITE_OK!=rc ){
    87     89         if( db->init.orphanTrigger ){
................................................................................
   112    114         ** has the same name as another index on a permanent index.  Since
   113    115         ** the permanent table is hidden by the TEMP table, we can also
   114    116         ** safely ignore the index on the permanent table.
   115    117         */
   116    118         /* Do Nothing */;
   117    119       }else if( sqlite3GetInt32(argv[1], &pIndex->tnum)==0 ){
   118    120         corruptSchema(pData, argv[0], "invalid rootpage");
   119         -    }else if( pIndex->autoIndex==2 
   120         -     && (pIndex->pTable->tabFlags & TF_WithoutRowid)!=0
   121         -    ){
   122         -      pIndex->pTable->tnum = pIndex->tnum;
   123    121       }
   124    122     }
   125    123     return 0;
   126    124   }
   127    125   
   128    126   /*
   129    127   ** Attempt to read the database schema and initialize internal

Changes to src/sqliteInt.h.

  2279   2279     TableLock *aTableLock; /* Required table locks for shared-cache mode */
  2280   2280   #endif
  2281   2281     AutoincInfo *pAinc;  /* Information about AUTOINCREMENT counters */
  2282   2282   
  2283   2283     /* Information used while coding trigger programs. */
  2284   2284     Parse *pToplevel;    /* Parse structure for main program (or NULL) */
  2285   2285     Table *pTriggerTab;  /* Table triggers are being coded for */
         2286  +  int addrCrTab;       /* Address of OP_CreateTable opcode on CREATE TABLE */
         2287  +  int addrSkipPK;      /* Address of instruction to skip PRIMARY KEY index */
  2286   2288     u32 nQueryLoop;      /* Est number of iterations of a query (10*log2(N)) */
  2287   2289     u32 oldmask;         /* Mask of old.* columns referenced */
  2288   2290     u32 newmask;         /* Mask of new.* columns referenced */
  2289   2291     u8 eTriggerOp;       /* TK_UPDATE, TK_INSERT or TK_DELETE */
  2290   2292     u8 eOrconf;          /* Default ON CONFLICT policy for trigger steps */
  2291   2293     u8 disableTriggers;  /* True to disable triggers */
  2292   2294   
  2293   2295     /* Above is constant between recursions.  Below is reset before and after
  2294   2296     ** each recursion */
  2295   2297   
  2296   2298     int nVar;                 /* Number of '?' variables seen in the SQL so far */
  2297   2299     int nzVar;                /* Number of available slots in azVar[] */
  2298         -  int addrCrTab;            /* Address of OP_CreateTable opcode */
  2299   2300     u8 iPkSortOrder;          /* ASC or DESC for INTEGER PRIMARY KEY */
  2300   2301     u8 explain;               /* True if the EXPLAIN flag is found on the query */
  2301   2302   #ifndef SQLITE_OMIT_VIRTUALTABLE
  2302   2303     u8 declareVtab;           /* True if inside sqlite3_declare_vtab() */
  2303   2304     int nVtabLock;            /* Number of virtual tables to lock */
  2304   2305   #endif
  2305   2306     int nAlias;               /* Number of aliased result set columns */
................................................................................
  2306   2307     int nHeight;              /* Expression tree height of current sub-select */
  2307   2308   #ifndef SQLITE_OMIT_EXPLAIN
  2308   2309     int iSelectId;            /* ID of current select for EXPLAIN output */
  2309   2310     int iNextSelectId;        /* Next available select ID for EXPLAIN output */
  2310   2311   #endif
  2311   2312     char **azVar;             /* Pointers to names of parameters */
  2312   2313     Vdbe *pReprepare;         /* VM being reprepared (sqlite3Reprepare()) */
  2313         -  int *aAlias;              /* Register used to hold aliased result */
  2314   2314     const char *zTail;        /* All SQL text past the last semicolon parsed */
  2315   2315     Table *pNewTable;         /* A table being constructed by CREATE TABLE */
  2316   2316     Trigger *pNewTrigger;     /* Trigger under construct by a CREATE TRIGGER */
  2317   2317     const char *zAuthContext; /* The 6th parameter to db->xAuth callbacks */
  2318   2318     Token sNameToken;         /* Token with unqualified schema object name */
  2319   2319     Token sLastToken;         /* The last token parsed */
  2320   2320   #ifndef SQLITE_OMIT_VIRTUALTABLE

Changes to src/tokenize.c.

   502    502       */
   503    503       sqlite3DeleteTable(db, pParse->pNewTable);
   504    504     }
   505    505   
   506    506     sqlite3DeleteTrigger(db, pParse->pNewTrigger);
   507    507     for(i=pParse->nzVar-1; i>=0; i--) sqlite3DbFree(db, pParse->azVar[i]);
   508    508     sqlite3DbFree(db, pParse->azVar);
   509         -  sqlite3DbFree(db, pParse->aAlias);
   510    509     while( pParse->pAinc ){
   511    510       AutoincInfo *p = pParse->pAinc;
   512    511       pParse->pAinc = p->pNext;
   513    512       sqlite3DbFree(db, p);
   514    513     }
   515    514     while( pParse->pZombieTab ){
   516    515       Table *p = pParse->pZombieTab;

Changes to test/corrupt2.test.

   462    462     close $fd
   463    463   } -test {
   464    464     do_test corrupt2-9.1 {
   465    465       catchsql " $::presql SELECT sql FROM sqlite_master "
   466    466     } {1 {database disk image is malformed}}
   467    467   }
   468    468   
   469         -# Since the introduction of WITHOUT ROWID tables, having a table entry in
   470         -# the sqlite_master table with a NULL rootpage is no longer a sign of 
   471         -# corruption.
   472         -#
   473         -if 0 {
   474    469   corruption_test -sqlprep {
   475    470     CREATE TABLE t1(a, b, c);
   476    471     CREATE TABLE t2(a, b, c);
   477    472     PRAGMA writable_schema = 1;
   478    473     UPDATE sqlite_master SET rootpage = NULL WHERE name = 't2';
   479    474   } -test {
   480    475     do_test corrupt2-10.1 {
   481    476       catchsql " $::presql SELECT * FROM t2 "
   482    477     } {1 {malformed database schema (t2)}}
   483    478     do_test corrupt2-10.2 {
   484    479       sqlite3_errcode db
   485    480     } {SQLITE_CORRUPT}
   486    481   }
   487         -} ;# Disabled rootpage==NULL corruption test
   488    482   
   489    483   corruption_test -sqlprep {
   490    484     PRAGMA auto_vacuum = incremental;
   491    485     CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
   492    486     CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
   493    487     INSERT INTO t1 VALUES(1, randstr(100,100));
   494    488     INSERT INTO t1 SELECT NULL, randstr(100,100) FROM t1;