/ Check-in [0248ec5e]
Login

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

Overview
Comment:Experimental changes toward "index only" tables. Add the ability to specify options on CREATE TABLE statements using the WITH clause modeled after PostgreSQL and SQL Server. Only the "omit_rowid" option is currently recognized and that option is currently a no-op.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | omit-rowid
Files: files | file ages | folders
SHA1: 0248ec5e6e3797575388f046d8c27f7445fe2a39
User & Date: drh 2013-10-19 23:31:56
Context
2013-10-21
02:14
Simplification of the syntax: Merely append "WITHOUT rowid" to the end of the table definition. check-in: 131cc6e1 user: drh tags: omit-rowid
2013-10-19
23:31
Experimental changes toward "index only" tables. Add the ability to specify options on CREATE TABLE statements using the WITH clause modeled after PostgreSQL and SQL Server. Only the "omit_rowid" option is currently recognized and that option is currently a no-op. check-in: 0248ec5e user: drh tags: omit-rowid
16:51
Improved header comment with better instructions on the vfslog.c extension. check-in: 4bd592c8 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  1552   1552   ** was called to create a table generated from a 
  1553   1553   ** "CREATE TABLE ... AS SELECT ..." statement.  The column names of
  1554   1554   ** the new table will match the result set of the SELECT.
  1555   1555   */
  1556   1556   void sqlite3EndTable(
  1557   1557     Parse *pParse,          /* Parse context */
  1558   1558     Token *pCons,           /* The ',' token after the last column defn. */
  1559         -  Token *pEnd,            /* The final ')' token in the CREATE TABLE */
         1559  +  Token *pEnd1,           /* The ')' before options in the CREATE TABLE */
         1560  +  Token *pEnd2,           /* The final ')' in the whole CREATE TABLE */
         1561  +  IdList *pOpts,          /* List of table options.  May be NULL */
  1560   1562     Select *pSelect         /* Select from a "CREATE ... AS SELECT" */
  1561   1563   ){
  1562   1564     Table *p;                 /* The new table */
  1563   1565     sqlite3 *db = pParse->db; /* The database connection */
  1564   1566     int iDb;                  /* Database in which the table lives */
  1565   1567     Index *pIdx;              /* An implied index of the table */
  1566   1568   
  1567         -  if( (pEnd==0 && pSelect==0) || db->mallocFailed ){
  1568         -    return;
         1569  +  if( (pEnd1==0 && pSelect==0) || db->mallocFailed ){
         1570  +    goto end_table_exception;
  1569   1571     }
  1570   1572     p = pParse->pNewTable;
  1571         -  if( p==0 ) return;
         1573  +  if( p==0 ) goto end_table_exception;
  1572   1574   
  1573   1575     assert( !db->init.busy || !pSelect );
         1576  +
         1577  +  if( pOpts ){
         1578  +    int i;
         1579  +    for(i=0; i<pOpts->nId; i++){
         1580  +      if( sqlite3_stricmp(pOpts->a[i].zName, "omit_rowid")==0 ){
         1581  +        p->tabFlags |= TF_WithoutRowid;
         1582  +        if( (p->tabFlags & TF_HasPrimaryKey)==0 ){
         1583  +          sqlite3ErrorMsg(pParse, "no PRIMARY KEY for table %s", p->zName);
         1584  +        }
         1585  +        continue;
         1586  +      }
         1587  +      sqlite3ErrorMsg(pParse, "unknown table option: %s", pOpts->a[i].zName);
         1588  +    }
         1589  +  }
  1574   1590   
  1575   1591     iDb = sqlite3SchemaToIndex(db, p->pSchema);
  1576   1592   
  1577   1593   #ifndef SQLITE_OMIT_CHECK
  1578   1594     /* Resolve names in all CHECK constraint expressions.
  1579   1595     */
  1580   1596     if( p->pCheck ){
................................................................................
  1608   1624       int n;
  1609   1625       Vdbe *v;
  1610   1626       char *zType;    /* "view" or "table" */
  1611   1627       char *zType2;   /* "VIEW" or "TABLE" */
  1612   1628       char *zStmt;    /* Text of the CREATE TABLE or CREATE VIEW statement */
  1613   1629   
  1614   1630       v = sqlite3GetVdbe(pParse);
  1615         -    if( NEVER(v==0) ) return;
         1631  +    if( NEVER(v==0) ) goto end_table_exception;
  1616   1632   
  1617   1633       sqlite3VdbeAddOp1(v, OP_Close, 0);
  1618   1634   
  1619   1635       /* 
  1620   1636       ** Initialize zType for the new view or table.
  1621   1637       */
  1622   1638       if( p->pSelect==0 ){
................................................................................
  1653   1669         sqlite3VdbeChangeP5(v, OPFLAG_P2ISREG);
  1654   1670         pParse->nTab = 2;
  1655   1671         sqlite3SelectDestInit(&dest, SRT_Table, 1);
  1656   1672         sqlite3Select(pParse, pSelect, &dest);
  1657   1673         sqlite3VdbeAddOp1(v, OP_Close, 1);
  1658   1674         if( pParse->nErr==0 ){
  1659   1675           pSelTab = sqlite3ResultSetOfSelect(pParse, pSelect);
  1660         -        if( pSelTab==0 ) return;
         1676  +        if( pSelTab==0 ) goto end_table_exception;
  1661   1677           assert( p->aCol==0 );
  1662   1678           p->nCol = pSelTab->nCol;
  1663   1679           p->aCol = pSelTab->aCol;
  1664   1680           pSelTab->nCol = 0;
  1665   1681           pSelTab->aCol = 0;
  1666   1682           sqlite3DeleteTable(db, pSelTab);
  1667   1683         }
  1668   1684       }
  1669   1685   
  1670   1686       /* Compute the complete text of the CREATE statement */
  1671   1687       if( pSelect ){
  1672   1688         zStmt = createTableStmt(db, p);
  1673   1689       }else{
  1674         -      n = (int)(pEnd->z - pParse->sNameToken.z) + 1;
         1690  +      n = (int)(pEnd2->z - pParse->sNameToken.z) + 1;
  1675   1691         zStmt = sqlite3MPrintf(db, 
  1676   1692             "CREATE %s %.*s", zType2, n, pParse->sNameToken.z
  1677   1693         );
  1678   1694       }
  1679   1695   
  1680   1696       /* A slot for the record has already been allocated in the 
  1681   1697       ** SQLITE_MASTER table.  We just need to update that slot with all
................................................................................
  1725   1741       Schema *pSchema = p->pSchema;
  1726   1742       assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
  1727   1743       pOld = sqlite3HashInsert(&pSchema->tblHash, p->zName,
  1728   1744                                sqlite3Strlen30(p->zName),p);
  1729   1745       if( pOld ){
  1730   1746         assert( p==pOld );  /* Malloc must have failed inside HashInsert() */
  1731   1747         db->mallocFailed = 1;
  1732         -      return;
         1748  +      goto end_table_exception;
  1733   1749       }
  1734   1750       pParse->pNewTable = 0;
  1735   1751       db->flags |= SQLITE_InternChanges;
  1736   1752   
  1737   1753   #ifndef SQLITE_OMIT_ALTERTABLE
  1738   1754       if( !p->pSelect ){
  1739   1755         const char *zName = (const char *)pParse->sNameToken.z;
  1740   1756         int nName;
  1741         -      assert( !pSelect && pCons && pEnd );
         1757  +      assert( !pSelect && pCons && pEnd1 );
  1742   1758         if( pCons->z==0 ){
  1743         -        pCons = pEnd;
         1759  +        pCons = pEnd1;
  1744   1760         }
  1745   1761         nName = (int)((const char *)pCons->z - zName);
  1746   1762         p->addColOffset = 13 + sqlite3Utf8CharLen(zName, nName);
  1747   1763       }
  1748   1764   #endif
  1749   1765     }
         1766  +
         1767  +end_table_exception:
         1768  +  sqlite3IdListDelete(db, pOpts);
         1769  +  return;
  1750   1770   }
  1751   1771   
  1752   1772   #ifndef SQLITE_OMIT_VIEW
  1753   1773   /*
  1754   1774   ** The parser calls this routine in order to create a new VIEW
  1755   1775   */
  1756   1776   void sqlite3CreateView(
................................................................................
  1815   1835     n = (int)(sEnd.z - pBegin->z);
  1816   1836     z = pBegin->z;
  1817   1837     while( ALWAYS(n>0) && sqlite3Isspace(z[n-1]) ){ n--; }
  1818   1838     sEnd.z = &z[n-1];
  1819   1839     sEnd.n = 1;
  1820   1840   
  1821   1841     /* Use sqlite3EndTable() to add the view to the SQLITE_MASTER table */
  1822         -  sqlite3EndTable(pParse, 0, &sEnd, 0);
         1842  +  sqlite3EndTable(pParse, 0, &sEnd, &sEnd, 0, 0);
  1823   1843     return;
  1824   1844   }
  1825   1845   #endif /* SQLITE_OMIT_VIEW */
  1826   1846   
  1827   1847   #if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_VIRTUALTABLE)
  1828   1848   /*
  1829   1849   ** The Table structure pTable is really a VIEW.  Fill in the names of

Changes to src/parse.y.

   159    159   ifnotexists(A) ::= .              {A = 0;}
   160    160   ifnotexists(A) ::= IF NOT EXISTS. {A = 1;}
   161    161   %type temp {int}
   162    162   %ifndef SQLITE_OMIT_TEMPDB
   163    163   temp(A) ::= TEMP.  {A = 1;}
   164    164   %endif  SQLITE_OMIT_TEMPDB
   165    165   temp(A) ::= .      {A = 0;}
   166         -create_table_args ::= LP columnlist conslist_opt(X) RP(Y). {
   167         -  sqlite3EndTable(pParse,&X,&Y,0);
          166  +create_table_args ::= LP columnlist conslist_opt(X) RP(E1). {
          167  +  sqlite3EndTable(pParse,&X,&E1,&E1,0,0);
          168  +}
          169  +create_table_args ::= LP columnlist conslist_opt(X) RP(E1)
          170  +                      WITH LP idlist(Z) RP(E2). {
          171  +  sqlite3EndTable(pParse,&X,&E1,&E2,Z,0);
   168    172   }
   169    173   create_table_args ::= AS select(S). {
   170         -  sqlite3EndTable(pParse,0,0,S);
          174  +  sqlite3EndTable(pParse,0,0,0,0,S);
   171    175     sqlite3SelectDelete(pParse->db, S);
   172    176   }
   173    177   columnlist ::= columnlist COMMA column.
   174    178   columnlist ::= column.
   175    179   
   176    180   // A "column" is a complete description of a single column in a
   177    181   // CREATE TABLE statement.  This includes the column name, its
................................................................................
   201    205   // This obviates the need for the "id" nonterminal.
   202    206   //
   203    207   %fallback ID
   204    208     ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW
   205    209     CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
   206    210     IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN
   207    211     QUERY KEY OF OFFSET PRAGMA RAISE RELEASE REPLACE RESTRICT ROW ROLLBACK
   208         -  SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL
          212  +  SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH
   209    213   %ifdef SQLITE_OMIT_COMPOUND_SELECT
   210    214     EXCEPT INTERSECT UNION
   211    215   %endif SQLITE_OMIT_COMPOUND_SELECT
   212    216     REINDEX RENAME CTIME_KW IF
   213    217     .
   214    218   %wildcard ANY.
   215    219   
................................................................................
   569    573   %type indexed_opt {Token}
   570    574   indexed_opt(A) ::= .                 {A.z=0; A.n=0;}
   571    575   indexed_opt(A) ::= INDEXED BY nm(X). {A = X;}
   572    576   indexed_opt(A) ::= NOT INDEXED.      {A.z=0; A.n=1;}
   573    577   
   574    578   %type using_opt {IdList*}
   575    579   %destructor using_opt {sqlite3IdListDelete(pParse->db, $$);}
   576         -using_opt(U) ::= USING LP inscollist(L) RP.  {U = L;}
          580  +using_opt(U) ::= USING LP idlist(L) RP.  {U = L;}
   577    581   using_opt(U) ::= .                        {U = 0;}
   578    582   
   579    583   
   580    584   %type orderby_opt {ExprList*}
   581    585   %destructor orderby_opt {sqlite3ExprListDelete(pParse->db, $$);}
   582    586   %type sortlist {ExprList*}
   583    587   %destructor sortlist {sqlite3ExprListDelete(pParse->db, $$);}
................................................................................
   736    740       A.pSelect = pRight;
   737    741     }
   738    742   }
   739    743   %endif SQLITE_OMIT_COMPOUND_SELECT
   740    744   
   741    745   %type inscollist_opt {IdList*}
   742    746   %destructor inscollist_opt {sqlite3IdListDelete(pParse->db, $$);}
   743         -%type inscollist {IdList*}
   744         -%destructor inscollist {sqlite3IdListDelete(pParse->db, $$);}
          747  +%type idlist {IdList*}
          748  +%destructor idlist {sqlite3IdListDelete(pParse->db, $$);}
   745    749   
   746    750   inscollist_opt(A) ::= .                       {A = 0;}
   747         -inscollist_opt(A) ::= LP inscollist(X) RP.    {A = X;}
   748         -inscollist(A) ::= inscollist(X) COMMA nm(Y).
          751  +inscollist_opt(A) ::= LP idlist(X) RP.    {A = X;}
          752  +idlist(A) ::= idlist(X) COMMA nm(Y).
   749    753       {A = sqlite3IdListAppend(pParse->db,X,&Y);}
   750         -inscollist(A) ::= nm(Y).
          754  +idlist(A) ::= nm(Y).
   751    755       {A = sqlite3IdListAppend(pParse->db,0,&Y);}
   752    756   
   753    757   /////////////////////////// Expression Processing /////////////////////////////
   754    758   //
   755    759   
   756    760   %type expr {ExprSpan}
   757    761   %destructor expr {sqlite3ExprDelete(pParse->db, $$.pExpr);}
................................................................................
  1223   1227   trigger_time(A) ::= INSTEAD OF.  { A = TK_INSTEAD;}
  1224   1228   trigger_time(A) ::= .            { A = TK_BEFORE; }
  1225   1229   
  1226   1230   %type trigger_event {struct TrigEvent}
  1227   1231   %destructor trigger_event {sqlite3IdListDelete(pParse->db, $$.b);}
  1228   1232   trigger_event(A) ::= DELETE|INSERT(OP).       {A.a = @OP; A.b = 0;}
  1229   1233   trigger_event(A) ::= UPDATE(OP).              {A.a = @OP; A.b = 0;}
  1230         -trigger_event(A) ::= UPDATE OF inscollist(X). {A.a = TK_UPDATE; A.b = X;}
         1234  +trigger_event(A) ::= UPDATE OF idlist(X). {A.a = TK_UPDATE; A.b = X;}
  1231   1235   
  1232   1236   foreach_clause ::= .
  1233   1237   foreach_clause ::= FOR EACH ROW.
  1234   1238   
  1235   1239   %type when_clause {Expr*}
  1236   1240   %destructor when_clause {sqlite3ExprDelete(pParse->db, $$);}
  1237   1241   when_clause(A) ::= .             { A = 0; }

Changes to src/sqliteInt.h.

  1407   1407   ** Allowed values for Tabe.tabFlags.
  1408   1408   */
  1409   1409   #define TF_Readonly        0x01    /* Read-only system table */
  1410   1410   #define TF_Ephemeral       0x02    /* An ephemeral table */
  1411   1411   #define TF_HasPrimaryKey   0x04    /* Table has a primary key */
  1412   1412   #define TF_Autoincrement   0x08    /* Integer primary key is autoincrement */
  1413   1413   #define TF_Virtual         0x10    /* Is a virtual table */
         1414  +#define TF_WithoutRowid    0x20    /* No rowid used. PRIMARY KEY is the key */
  1414   1415   
  1415   1416   
  1416   1417   /*
  1417   1418   ** Test to see whether or not a table is a virtual table.  This is
  1418   1419   ** done as a macro so that it will be optimized out when virtual
  1419   1420   ** table support is omitted from the build.
  1420   1421   */
................................................................................
  2768   2769   void sqlite3AddColumn(Parse*,Token*);
  2769   2770   void sqlite3AddNotNull(Parse*, int);
  2770   2771   void sqlite3AddPrimaryKey(Parse*, ExprList*, int, int, int);
  2771   2772   void sqlite3AddCheckConstraint(Parse*, Expr*);
  2772   2773   void sqlite3AddColumnType(Parse*,Token*);
  2773   2774   void sqlite3AddDefaultValue(Parse*,ExprSpan*);
  2774   2775   void sqlite3AddCollateType(Parse*, Token*);
  2775         -void sqlite3EndTable(Parse*,Token*,Token*,Select*);
         2776  +void sqlite3EndTable(Parse*,Token*,Token*,Token*,IdList*,Select*);
  2776   2777   int sqlite3ParseUri(const char*,const char*,unsigned int*,
  2777   2778                       sqlite3_vfs**,char**,char **);
  2778   2779   Btree *sqlite3DbNameToBtree(sqlite3*,const char*);
  2779   2780   int sqlite3CodeOnce(Parse *);
  2780   2781   
  2781   2782   Bitvec *sqlite3BitvecCreate(u32);
  2782   2783   int sqlite3BitvecTest(Bitvec*, u32);

Changes to test/alter.test.

   856    856     } [list 1 "table $tbl may not be altered"]
   857    857   
   858    858     do_test alter-15.$tn.2 {
   859    859       catchsql "ALTER TABLE $tbl ADD COLUMN xyz"
   860    860     } [list 1 "table $tbl may not be altered"]
   861    861   }
   862    862   
          863  +#------------------------------------------------------------------------
          864  +# Verify that ALTER TABLE works on tables with WITH options.
          865  +#
          866  +do_execsql_test alter-16.1 {
          867  +  CREATE TABLE t16a(a TEXT, b REAL, c INT, PRIMARY KEY(a,b)) WITH (omit_rowid);
          868  +  INSERT INTO t16a VALUES('abc',1.25,99);
          869  +  ALTER TABLE t16a ADD COLUMN d TEXT DEFAULT 'xyzzy';
          870  +  INSERT INTO t16a VALUES('cba',5.5,98,'fizzle');
          871  +  SELECT * FROM t16a ORDER BY a;
          872  +} {abc 1.25 99 xyzzy cba 5.5 98 fizzle}
          873  +do_execsql_test alter-16.2 {
          874  +  ALTER TABLE t16a RENAME TO t16a_rn;
          875  +  SELECT * FROM t16a_rn ORDER BY a;
          876  +} {abc 1.25 99 xyzzy cba 5.5 98 fizzle}
   863    877   
   864    878   finish_test

Added test/tableopts.test.

            1  +# 2013-10-19
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# Test the operation of table-options in the WITH clause of the
           13  +# CREATE TABLE statement.
           14  +#
           15  +
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +do_test tableopt-1.1 {
           21  +  catchsql {
           22  +    CREATE TABLE t1(a,b) WITH (omit_rowid);
           23  +  }
           24  +} {1 {no PRIMARY KEY for table t1}}
           25  +do_test tableopt-1.2 {
           26  +  catchsql {
           27  +    CREATE TABLE t1(a,b) WITH (unknown1, unknown2);
           28  +  }
           29  +} {1 {unknown table option: unknown2}}
           30  +do_test tableopt-1.3 {
           31  +  catchsql {
           32  +    CREATE TABLE t1(a,b,c,PRIMARY KEY(a,b)) WITH (omit_rowid, unknown3);
           33  +  }
           34  +} {1 {unknown table option: unknown3}}
           35  +do_test tableopt-1.4 {
           36  +  catchsql {
           37  +    CREATE TABLE t1(a,b,c,PRIMARY KEY(a,b)) WITH (unknown4, omit_rowid);
           38  +  }
           39  +} {1 {unknown table option: unknown4}}
           40  +
           41  +do_execsql_test tableopt-2.1 {
           42  +  CREATE TABLE t1(a, b, c, PRIMARY KEY(a,b)) WITH (omit_rowid);
           43  +  INSERT INTO t1 VALUES(1,2,3),(2,3,4);
           44  +  SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b;
           45  +} {3 4}
           46  +do_execsql_test tableopt-2.2 {
           47  +  VACUUM;
           48  +  SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b;
           49  +} {3 4}
           50  +do_test tableopt-2.3 {
           51  +  sqlite3 db2 test.db
           52  +  db2 eval {SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b;}
           53  +} {3 4}
           54  +db2 close
           55  +  
           56  +finish_test

Changes to tool/mkkeywordhash.c.

   258    258     { "UNIQUE",           "TK_UNIQUE",       ALWAYS                 },
   259    259     { "UPDATE",           "TK_UPDATE",       ALWAYS                 },
   260    260     { "USING",            "TK_USING",        ALWAYS                 },
   261    261     { "VACUUM",           "TK_VACUUM",       VACUUM                 },
   262    262     { "VALUES",           "TK_VALUES",       ALWAYS                 },
   263    263     { "VIEW",             "TK_VIEW",         VIEW                   },
   264    264     { "VIRTUAL",          "TK_VIRTUAL",      VTAB                   },
          265  +  { "WITH",             "TK_WITH",         ALWAYS                 },
   265    266     { "WHEN",             "TK_WHEN",         ALWAYS                 },
   266    267     { "WHERE",            "TK_WHERE",        ALWAYS                 },
   267    268   };
   268    269   
   269    270   /* Number of keywords */
   270    271   static int nKeyword = (sizeof(aKeywordTable)/sizeof(aKeywordTable[0]));
   271    272