Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Simplification of the syntax: Merely append "WITHOUT rowid" to the end of the table definition. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | omit-rowid |
Files: | files | file ages | folders |
SHA1: |
131cc6e152abe1a2d48e6d8d40d2c2f8 |
User & Date: | drh 2013-10-21 02:14:45.234 |
Context
2013-10-22
| ||
01:18 | Previous refactor is not going to be helpful because implied indices must be created in the same order that they appear in the CREATE TABLE statement for backwards compatibility. This is a much smaller change to clean up a few loose ends. (check-in: 824b549f9b user: drh tags: omit-rowid) | |
2013-10-21
| ||
23:55 | Minor refactoring of the PRIMARY KEY parsing logic to facilitate enhancements. (Closed-Leaf check-in: 4e69dd5f9b user: drh tags: omit-rowid) | |
02:14 | Simplification of the syntax: Merely append "WITHOUT rowid" to the end of the table definition. (check-in: 131cc6e152 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: 0248ec5e6e user: drh tags: omit-rowid) | |
Changes
Changes to src/build.c.
︙ | ︙ | |||
1552 1553 1554 1555 1556 1557 1558 | ** was called to create a table generated from a ** "CREATE TABLE ... AS SELECT ..." statement. The column names of ** the new table will match the result set of the SELECT. */ void sqlite3EndTable( Parse *pParse, /* Parse context */ Token *pCons, /* The ',' token after the last column defn. */ | | < | | | | < < < < | | | | < < < < > | 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 | ** was called to create a table generated from a ** "CREATE TABLE ... AS SELECT ..." statement. The column names of ** the new table will match the result set of the SELECT. */ void sqlite3EndTable( Parse *pParse, /* Parse context */ Token *pCons, /* The ',' token after the last column defn. */ Token *pEnd, /* The ')' before options in the CREATE TABLE */ u8 tabOpts, /* Extra table options. Usually 0. */ Select *pSelect /* Select from a "CREATE ... AS SELECT" */ ){ Table *p; /* The new table */ sqlite3 *db = pParse->db; /* The database connection */ int iDb; /* Database in which the table lives */ Index *pIdx; /* An implied index of the table */ if( (pEnd==0 && pSelect==0) || db->mallocFailed ){ return; } p = pParse->pNewTable; if( p==0 ) return; assert( !db->init.busy || !pSelect ); if( tabOpts & TF_WithoutRowid ){ if( (p->tabFlags & TF_HasPrimaryKey)==0 ){ sqlite3ErrorMsg(pParse, "no PRIMARY KEY for table %s", p->zName); } p->tabFlags |= TF_WithoutRowid; } iDb = sqlite3SchemaToIndex(db, p->pSchema); #ifndef SQLITE_OMIT_CHECK /* Resolve names in all CHECK constraint expressions. */ |
︙ | ︙ | |||
1624 1625 1626 1627 1628 1629 1630 | int n; Vdbe *v; char *zType; /* "view" or "table" */ char *zType2; /* "VIEW" or "TABLE" */ char *zStmt; /* Text of the CREATE TABLE or CREATE VIEW statement */ v = sqlite3GetVdbe(pParse); | | | 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 | int n; Vdbe *v; char *zType; /* "view" or "table" */ char *zType2; /* "VIEW" or "TABLE" */ char *zStmt; /* Text of the CREATE TABLE or CREATE VIEW statement */ v = sqlite3GetVdbe(pParse); if( NEVER(v==0) ) return; sqlite3VdbeAddOp1(v, OP_Close, 0); /* ** Initialize zType for the new view or table. */ if( p->pSelect==0 ){ |
︙ | ︙ | |||
1669 1670 1671 1672 1673 1674 1675 | sqlite3VdbeChangeP5(v, OPFLAG_P2ISREG); pParse->nTab = 2; sqlite3SelectDestInit(&dest, SRT_Table, 1); sqlite3Select(pParse, pSelect, &dest); sqlite3VdbeAddOp1(v, OP_Close, 1); if( pParse->nErr==0 ){ pSelTab = sqlite3ResultSetOfSelect(pParse, pSelect); | | | > | 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 | sqlite3VdbeChangeP5(v, OPFLAG_P2ISREG); pParse->nTab = 2; sqlite3SelectDestInit(&dest, SRT_Table, 1); sqlite3Select(pParse, pSelect, &dest); sqlite3VdbeAddOp1(v, OP_Close, 1); if( pParse->nErr==0 ){ pSelTab = sqlite3ResultSetOfSelect(pParse, pSelect); if( pSelTab==0 ) return; assert( p->aCol==0 ); p->nCol = pSelTab->nCol; p->aCol = pSelTab->aCol; pSelTab->nCol = 0; pSelTab->aCol = 0; sqlite3DeleteTable(db, pSelTab); } } /* Compute the complete text of the CREATE statement */ if( pSelect ){ zStmt = createTableStmt(db, p); }else{ n = (int)(pParse->sLastToken.z - pParse->sNameToken.z); if( pParse->sLastToken.z[0]!=';' ) n += pParse->sLastToken.n; zStmt = sqlite3MPrintf(db, "CREATE %s %.*s", zType2, n, pParse->sNameToken.z ); } /* A slot for the record has already been allocated in the ** SQLITE_MASTER table. We just need to update that slot with all |
︙ | ︙ | |||
1741 1742 1743 1744 1745 1746 1747 | Schema *pSchema = p->pSchema; assert( sqlite3SchemaMutexHeld(db, iDb, 0) ); pOld = sqlite3HashInsert(&pSchema->tblHash, p->zName, sqlite3Strlen30(p->zName),p); if( pOld ){ assert( p==pOld ); /* Malloc must have failed inside HashInsert() */ db->mallocFailed = 1; | | | | < < < < | 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 | Schema *pSchema = p->pSchema; assert( sqlite3SchemaMutexHeld(db, iDb, 0) ); pOld = sqlite3HashInsert(&pSchema->tblHash, p->zName, sqlite3Strlen30(p->zName),p); if( pOld ){ assert( p==pOld ); /* Malloc must have failed inside HashInsert() */ db->mallocFailed = 1; return; } pParse->pNewTable = 0; db->flags |= SQLITE_InternChanges; #ifndef SQLITE_OMIT_ALTERTABLE if( !p->pSelect ){ const char *zName = (const char *)pParse->sNameToken.z; int nName; assert( !pSelect && pCons && pEnd ); if( pCons->z==0 ){ pCons = pEnd; } nName = (int)((const char *)pCons->z - zName); p->addColOffset = 13 + sqlite3Utf8CharLen(zName, nName); } #endif } } #ifndef SQLITE_OMIT_VIEW /* ** The parser calls this routine in order to create a new VIEW */ void sqlite3CreateView( |
︙ | ︙ | |||
1835 1836 1837 1838 1839 1840 1841 | n = (int)(sEnd.z - pBegin->z); z = pBegin->z; while( ALWAYS(n>0) && sqlite3Isspace(z[n-1]) ){ n--; } sEnd.z = &z[n-1]; sEnd.n = 1; /* Use sqlite3EndTable() to add the view to the SQLITE_MASTER table */ | | | 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 | n = (int)(sEnd.z - pBegin->z); z = pBegin->z; while( ALWAYS(n>0) && sqlite3Isspace(z[n-1]) ){ n--; } sEnd.z = &z[n-1]; sEnd.n = 1; /* Use sqlite3EndTable() to add the view to the SQLITE_MASTER table */ sqlite3EndTable(pParse, 0, &sEnd, 0, 0); return; } #endif /* SQLITE_OMIT_VIEW */ #if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_VIRTUALTABLE) /* ** The Table structure pTable is really a VIEW. Fill in the names of |
︙ | ︙ |
Changes to src/parse.y.
︙ | ︙ | |||
159 160 161 162 163 164 165 | ifnotexists(A) ::= . {A = 0;} ifnotexists(A) ::= IF NOT EXISTS. {A = 1;} %type temp {int} %ifndef SQLITE_OMIT_TEMPDB temp(A) ::= TEMP. {A = 1;} %endif SQLITE_OMIT_TEMPDB temp(A) ::= . {A = 0;} | | | | < | > > | > | > > > | > | 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 | ifnotexists(A) ::= . {A = 0;} ifnotexists(A) ::= IF NOT EXISTS. {A = 1;} %type temp {int} %ifndef SQLITE_OMIT_TEMPDB temp(A) ::= TEMP. {A = 1;} %endif SQLITE_OMIT_TEMPDB temp(A) ::= . {A = 0;} create_table_args ::= LP columnlist conslist_opt(X) RP(E) table_options(F). { sqlite3EndTable(pParse,&X,&E,F,0); } create_table_args ::= AS select(S). { sqlite3EndTable(pParse,0,0,0,S); sqlite3SelectDelete(pParse->db, S); } %type table_options {u8} table_options(A) ::= . {A = 0;} table_options(A) ::= WITHOUT nm(X). { if( X.n==5 && sqlite3_strnicmp(X.z,"rowid",5)==0 ){ A = TF_WithoutRowid; }else{ A = 0; sqlite3ErrorMsg(pParse, "unknown table option: %.*s", X.n, X.z); } } columnlist ::= columnlist COMMA column. columnlist ::= column. // A "column" is a complete description of a single column in a // CREATE TABLE statement. This includes the column name, its // datatype, and other keywords such as PRIMARY KEY, UNIQUE, REFERENCES, |
︙ | ︙ | |||
205 206 207 208 209 210 211 | // This obviates the need for the "id" nonterminal. // %fallback ID ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN QUERY KEY OF OFFSET PRAGMA RAISE RELEASE REPLACE RESTRICT ROW ROLLBACK | | | 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 | // This obviates the need for the "id" nonterminal. // %fallback ID ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN QUERY KEY OF OFFSET PRAGMA RAISE RELEASE REPLACE RESTRICT ROW ROLLBACK SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITHOUT %ifdef SQLITE_OMIT_COMPOUND_SELECT EXCEPT INTERSECT UNION %endif SQLITE_OMIT_COMPOUND_SELECT REINDEX RENAME CTIME_KW IF . %wildcard ANY. |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2769 2770 2771 2772 2773 2774 2775 | void sqlite3AddColumn(Parse*,Token*); void sqlite3AddNotNull(Parse*, int); void sqlite3AddPrimaryKey(Parse*, ExprList*, int, int, int); void sqlite3AddCheckConstraint(Parse*, Expr*); void sqlite3AddColumnType(Parse*,Token*); void sqlite3AddDefaultValue(Parse*,ExprSpan*); void sqlite3AddCollateType(Parse*, Token*); | | | 2769 2770 2771 2772 2773 2774 2775 2776 2777 2778 2779 2780 2781 2782 2783 | void sqlite3AddColumn(Parse*,Token*); void sqlite3AddNotNull(Parse*, int); void sqlite3AddPrimaryKey(Parse*, ExprList*, int, int, int); void sqlite3AddCheckConstraint(Parse*, Expr*); void sqlite3AddColumnType(Parse*,Token*); void sqlite3AddDefaultValue(Parse*,ExprSpan*); void sqlite3AddCollateType(Parse*, Token*); void sqlite3EndTable(Parse*,Token*,Token*,u8,Select*); int sqlite3ParseUri(const char*,const char*,unsigned int*, sqlite3_vfs**,char**,char **); Btree *sqlite3DbNameToBtree(sqlite3*,const char*); int sqlite3CodeOnce(Parse *); Bitvec *sqlite3BitvecCreate(u32); int sqlite3BitvecTest(Bitvec*, u32); |
︙ | ︙ |
Changes to test/alter.test.
︙ | ︙ | |||
857 858 859 860 861 862 863 | do_test alter-15.$tn.2 { catchsql "ALTER TABLE $tbl ADD COLUMN xyz" } [list 1 "table $tbl may not be altered"] } #------------------------------------------------------------------------ | | | | 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 | do_test alter-15.$tn.2 { catchsql "ALTER TABLE $tbl ADD COLUMN xyz" } [list 1 "table $tbl may not be altered"] } #------------------------------------------------------------------------ # Verify that ALTER TABLE works on tables with the WITHOUT rowid option. # do_execsql_test alter-16.1 { CREATE TABLE t16a(a TEXT, b REAL, c INT, PRIMARY KEY(a,b)) WITHOUT rowid; INSERT INTO t16a VALUES('abc',1.25,99); ALTER TABLE t16a ADD COLUMN d TEXT DEFAULT 'xyzzy'; INSERT INTO t16a VALUES('cba',5.5,98,'fizzle'); SELECT * FROM t16a ORDER BY a; } {abc 1.25 99 xyzzy cba 5.5 98 fizzle} do_execsql_test alter-16.2 { ALTER TABLE t16a RENAME TO t16a_rn; |
︙ | ︙ |
Changes to test/tableopts.test.
︙ | ︙ | |||
15 16 17 18 19 20 21 | set testdir [file dirname $argv0] source $testdir/tester.tcl do_test tableopt-1.1 { catchsql { | | | < < < | < < < < < < < | > > > > > > > > > > | 15 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 | set testdir [file dirname $argv0] source $testdir/tester.tcl do_test tableopt-1.1 { catchsql { CREATE TABLE t1(a,b) WITHOUT rowid; } } {1 {no PRIMARY KEY for table t1}} do_test tableopt-1.2 { catchsql { CREATE TABLE t1(a,b) WITHOUT unknown2; } } {1 {unknown table option: unknown2}} do_execsql_test tableopt-2.1 { CREATE TABLE t1(a, b, c, PRIMARY KEY(a,b)) WITHOUT rowid; INSERT INTO t1 VALUES(1,2,3),(2,3,4); SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b; } {3 4} do_execsql_test tableopt-2.2 { VACUUM; SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b; } {3 4} do_test tableopt-2.3 { sqlite3 db2 test.db db2 eval {SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b;} } {3 4} db2 close # Make sure the "without" keyword is still usable as a table or # column name. # do_execsql_test tableopt-3.1 { CREATE TABLE without(x INTEGER PRIMARY KEY, without TEXT); INSERT INTO without VALUES(1, 'xyzzy'), (2, 'fizzle'); SELECT * FROM without WHERE without='xyzzy'; } {1 xyzzy} finish_test |
Changes to tool/mkkeywordhash.c.
︙ | ︙ | |||
258 259 260 261 262 263 264 | { "UNIQUE", "TK_UNIQUE", ALWAYS }, { "UPDATE", "TK_UPDATE", ALWAYS }, { "USING", "TK_USING", ALWAYS }, { "VACUUM", "TK_VACUUM", VACUUM }, { "VALUES", "TK_VALUES", ALWAYS }, { "VIEW", "TK_VIEW", VIEW }, { "VIRTUAL", "TK_VIRTUAL", VTAB }, | | | 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 | { "UNIQUE", "TK_UNIQUE", ALWAYS }, { "UPDATE", "TK_UPDATE", ALWAYS }, { "USING", "TK_USING", ALWAYS }, { "VACUUM", "TK_VACUUM", VACUUM }, { "VALUES", "TK_VALUES", ALWAYS }, { "VIEW", "TK_VIEW", VIEW }, { "VIRTUAL", "TK_VIRTUAL", VTAB }, { "WITHOUT", "TK_WITHOUT", ALWAYS }, { "WHEN", "TK_WHEN", ALWAYS }, { "WHERE", "TK_WHERE", ALWAYS }, }; /* Number of keywords */ static int nKeyword = (sizeof(aKeywordTable)/sizeof(aKeywordTable[0])); |
︙ | ︙ |