Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -1554,25 +1554,41 @@ ** 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 final ')' token in the CREATE TABLE */ + Token *pEnd1, /* The ')' before options in the CREATE TABLE */ + Token *pEnd2, /* The final ')' in the whole CREATE TABLE */ + IdList *pOpts, /* List of table options. May be NULL */ 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; + if( (pEnd1==0 && pSelect==0) || db->mallocFailed ){ + goto end_table_exception; } p = pParse->pNewTable; - if( p==0 ) return; + if( p==0 ) goto end_table_exception; assert( !db->init.busy || !pSelect ); + + if( pOpts ){ + int i; + for(i=0; inId; i++){ + if( sqlite3_stricmp(pOpts->a[i].zName, "omit_rowid")==0 ){ + p->tabFlags |= TF_WithoutRowid; + if( (p->tabFlags & TF_HasPrimaryKey)==0 ){ + sqlite3ErrorMsg(pParse, "no PRIMARY KEY for table %s", p->zName); + } + continue; + } + sqlite3ErrorMsg(pParse, "unknown table option: %s", pOpts->a[i].zName); + } + } iDb = sqlite3SchemaToIndex(db, p->pSchema); #ifndef SQLITE_OMIT_CHECK /* Resolve names in all CHECK constraint expressions. @@ -1610,11 +1626,11 @@ 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; + if( NEVER(v==0) ) goto end_table_exception; sqlite3VdbeAddOp1(v, OP_Close, 0); /* ** Initialize zType for the new view or table. @@ -1655,11 +1671,11 @@ 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; + if( pSelTab==0 ) goto end_table_exception; assert( p->aCol==0 ); p->nCol = pSelTab->nCol; p->aCol = pSelTab->aCol; pSelTab->nCol = 0; pSelTab->aCol = 0; @@ -1669,11 +1685,11 @@ /* Compute the complete text of the CREATE statement */ if( pSelect ){ zStmt = createTableStmt(db, p); }else{ - n = (int)(pEnd->z - pParse->sNameToken.z) + 1; + n = (int)(pEnd2->z - pParse->sNameToken.z) + 1; zStmt = sqlite3MPrintf(db, "CREATE %s %.*s", zType2, n, pParse->sNameToken.z ); } @@ -1727,28 +1743,32 @@ 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; + goto end_table_exception; } 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 ); + assert( !pSelect && pCons && pEnd1 ); if( pCons->z==0 ){ - pCons = pEnd; + pCons = pEnd1; } nName = (int)((const char *)pCons->z - zName); p->addColOffset = 13 + sqlite3Utf8CharLen(zName, nName); } #endif } + +end_table_exception: + sqlite3IdListDelete(db, pOpts); + return; } #ifndef SQLITE_OMIT_VIEW /* ** The parser calls this routine in order to create a new VIEW @@ -1817,11 +1837,11 @@ 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); + sqlite3EndTable(pParse, 0, &sEnd, &sEnd, 0, 0); return; } #endif /* SQLITE_OMIT_VIEW */ #if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_VIRTUALTABLE) Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -161,15 +161,19 @@ %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(Y). { - sqlite3EndTable(pParse,&X,&Y,0); +create_table_args ::= LP columnlist conslist_opt(X) RP(E1). { + sqlite3EndTable(pParse,&X,&E1,&E1,0,0); +} +create_table_args ::= LP columnlist conslist_opt(X) RP(E1) + WITH LP idlist(Z) RP(E2). { + sqlite3EndTable(pParse,&X,&E1,&E2,Z,0); } create_table_args ::= AS select(S). { - sqlite3EndTable(pParse,0,0,S); + sqlite3EndTable(pParse,0,0,0,0,S); sqlite3SelectDelete(pParse->db, S); } columnlist ::= columnlist COMMA column. columnlist ::= column. @@ -203,11 +207,11 @@ %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 + SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH %ifdef SQLITE_OMIT_COMPOUND_SELECT EXCEPT INTERSECT UNION %endif SQLITE_OMIT_COMPOUND_SELECT REINDEX RENAME CTIME_KW IF . @@ -571,11 +575,11 @@ indexed_opt(A) ::= INDEXED BY nm(X). {A = X;} indexed_opt(A) ::= NOT INDEXED. {A.z=0; A.n=1;} %type using_opt {IdList*} %destructor using_opt {sqlite3IdListDelete(pParse->db, $$);} -using_opt(U) ::= USING LP inscollist(L) RP. {U = L;} +using_opt(U) ::= USING LP idlist(L) RP. {U = L;} using_opt(U) ::= . {U = 0;} %type orderby_opt {ExprList*} %destructor orderby_opt {sqlite3ExprListDelete(pParse->db, $$);} @@ -738,18 +742,18 @@ } %endif SQLITE_OMIT_COMPOUND_SELECT %type inscollist_opt {IdList*} %destructor inscollist_opt {sqlite3IdListDelete(pParse->db, $$);} -%type inscollist {IdList*} -%destructor inscollist {sqlite3IdListDelete(pParse->db, $$);} +%type idlist {IdList*} +%destructor idlist {sqlite3IdListDelete(pParse->db, $$);} inscollist_opt(A) ::= . {A = 0;} -inscollist_opt(A) ::= LP inscollist(X) RP. {A = X;} -inscollist(A) ::= inscollist(X) COMMA nm(Y). +inscollist_opt(A) ::= LP idlist(X) RP. {A = X;} +idlist(A) ::= idlist(X) COMMA nm(Y). {A = sqlite3IdListAppend(pParse->db,X,&Y);} -inscollist(A) ::= nm(Y). +idlist(A) ::= nm(Y). {A = sqlite3IdListAppend(pParse->db,0,&Y);} /////////////////////////// Expression Processing ///////////////////////////// // @@ -1225,11 +1229,11 @@ %type trigger_event {struct TrigEvent} %destructor trigger_event {sqlite3IdListDelete(pParse->db, $$.b);} trigger_event(A) ::= DELETE|INSERT(OP). {A.a = @OP; A.b = 0;} trigger_event(A) ::= UPDATE(OP). {A.a = @OP; A.b = 0;} -trigger_event(A) ::= UPDATE OF inscollist(X). {A.a = TK_UPDATE; A.b = X;} +trigger_event(A) ::= UPDATE OF idlist(X). {A.a = TK_UPDATE; A.b = X;} foreach_clause ::= . foreach_clause ::= FOR EACH ROW. %type when_clause {Expr*} Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1409,10 +1409,11 @@ #define TF_Readonly 0x01 /* Read-only system table */ #define TF_Ephemeral 0x02 /* An ephemeral table */ #define TF_HasPrimaryKey 0x04 /* Table has a primary key */ #define TF_Autoincrement 0x08 /* Integer primary key is autoincrement */ #define TF_Virtual 0x10 /* Is a virtual table */ +#define TF_WithoutRowid 0x20 /* No rowid used. PRIMARY KEY is the key */ /* ** Test to see whether or not a table is a virtual table. This is ** done as a macro so that it will be optimized out when virtual @@ -2770,11 +2771,11 @@ 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*,Select*); +void sqlite3EndTable(Parse*,Token*,Token*,Token*,IdList*,Select*); int sqlite3ParseUri(const char*,const char*,unsigned int*, sqlite3_vfs**,char**,char **); Btree *sqlite3DbNameToBtree(sqlite3*,const char*); int sqlite3CodeOnce(Parse *); Index: test/alter.test ================================================================== --- test/alter.test +++ test/alter.test @@ -858,7 +858,21 @@ 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 WITH options. +# +do_execsql_test alter-16.1 { + CREATE TABLE t16a(a TEXT, b REAL, c INT, PRIMARY KEY(a,b)) WITH (omit_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; + SELECT * FROM t16a_rn ORDER BY a; +} {abc 1.25 99 xyzzy cba 5.5 98 fizzle} finish_test ADDED test/tableopts.test Index: test/tableopts.test ================================================================== --- /dev/null +++ test/tableopts.test @@ -0,0 +1,56 @@ +# 2013-10-19 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# Test the operation of table-options in the WITH clause of the +# CREATE TABLE statement. +# + + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +do_test tableopt-1.1 { + catchsql { + CREATE TABLE t1(a,b) WITH (omit_rowid); + } +} {1 {no PRIMARY KEY for table t1}} +do_test tableopt-1.2 { + catchsql { + CREATE TABLE t1(a,b) WITH (unknown1, unknown2); + } +} {1 {unknown table option: unknown2}} +do_test tableopt-1.3 { + catchsql { + CREATE TABLE t1(a,b,c,PRIMARY KEY(a,b)) WITH (omit_rowid, unknown3); + } +} {1 {unknown table option: unknown3}} +do_test tableopt-1.4 { + catchsql { + CREATE TABLE t1(a,b,c,PRIMARY KEY(a,b)) WITH (unknown4, omit_rowid); + } +} {1 {unknown table option: unknown4}} + +do_execsql_test tableopt-2.1 { + CREATE TABLE t1(a, b, c, PRIMARY KEY(a,b)) WITH (omit_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 + +finish_test Index: tool/mkkeywordhash.c ================================================================== --- tool/mkkeywordhash.c +++ tool/mkkeywordhash.c @@ -260,10 +260,11 @@ { "USING", "TK_USING", ALWAYS }, { "VACUUM", "TK_VACUUM", VACUUM }, { "VALUES", "TK_VALUES", ALWAYS }, { "VIEW", "TK_VIEW", VIEW }, { "VIRTUAL", "TK_VIRTUAL", VTAB }, + { "WITH", "TK_WITH", ALWAYS }, { "WHEN", "TK_WHEN", ALWAYS }, { "WHERE", "TK_WHERE", ALWAYS }, }; /* Number of keywords */