From 737412ef5e3c11b9f286ba8531c073630e0793c3 Mon Sep 17 00:00:00 2001 From: drh <> Date: Wed, 9 Feb 2022 16:18:44 +0000 Subject: [PATCH] Prototype implementation of IF EXISTS and IF NOT EXISTS clauses on the various forms of ALTER TALE. FossilOrigin-Name: 460abf93ac6c47b0e3462e941d5de8e6b678dce9fac04a1e4923e2bf65cb59b2 --- manifest | 21 +++++++----- manifest.uuid | 2 +- src/alter.c | 87 ++++++++++++++++++++++++++++++++++++++++++------- src/build.c | 8 +++-- src/parse.y | 20 ++++++------ src/sqliteInt.h | 10 +++--- 6 files changed, 111 insertions(+), 37 deletions(-) diff --git a/manifest b/manifest index 31a7cb395e..27c657055b 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Writes\sto\sthe\ssubjournal\sshould\sbe\sall-or-nothing.\s\sFix\sfor\ndbsqlfuzz\sfe3c397fb90029313446c4e0f4a6cd0c81dd9621. -D 2022-02-08T15:14:18.391 +C Prototype\simplementation\sof\sIF\sEXISTS\sand\sIF\sNOT\sEXISTS\sclauses\son\sthe\nvarious\sforms\sof\sALTER\sTALE. +D 2022-02-09T16:18:44.549 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -485,7 +485,7 @@ F spec.template 86a4a43b99ebb3e75e6b9a735d5fd293a24e90ca F sqlite.pc.in 42b7bf0d02e08b9e77734a47798d1a55a9e0716b F sqlite3.1 fc7ad8990fc8409983309bb80de8c811a7506786 F sqlite3.pc.in 48fed132e7cb71ab676105d2a4dc77127d8c1f3a -F src/alter.c e31cae888bc3077e34f9a82c6b4a96e4e44d37861eeb6472d68a378f1e8e46ba +F src/alter.c 60dd7d624985ea5502aaec0852b6a516622437369d2138b1d4038a96400a95f8 F src/analyze.c 7518b99e07c5494111fe3bd867f28f804b6c5c1ad0703ec3d116de9bab3fa516 F src/attach.c f26d400f3ffe2cdca01406bca70e5f58c5488bf165b4fc37c228136dfcf1b583 F src/auth.c f4fa91b6a90bbc8e0d0f738aa284551739c9543a367071f55574681e0f24f8cf @@ -495,7 +495,7 @@ F src/btmutex.c 8acc2f464ee76324bf13310df5692a262b801808984c1b79defb2503bbafadb6 F src/btree.c ddab31c38d5f16114bc68392430556b1063fe14e0020f9a56d2c35ddd58ba7e3 F src/btree.h 74d64b8f28cfa4a894d14d4ed64fa432cd697b98b61708d4351482ae15913e22 F src/btreeInt.h ee9348c4cb9077243b049edc93a82c1f32ca48baeabf2140d41362b9f9139ff7 -F src/build.c b59ff41525c10b429adc277d3bca6e433b09d055b0df8c1529385763cea8bb04 +F src/build.c b6ab5a41941e0b1fd4caf644f52c0fff650504d0def5d6036d9696875319d4b4 F src/callback.c 4c19af69835787bfe790ac560f3071a824eb629f34e41f97b52ce5235c77de1c F src/complete.c a3634ab1e687055cd002e11b8f43eb75c17da23e F src/ctime.c 2cce39df1a13e05b7633e6d21b651f21492471f991dd7b323a4ee4e7b7f0b7f1 @@ -541,7 +541,7 @@ F src/os_win.c 77d39873836f1831a9b0b91894fec45ab0e9ca8e067dc8c549e1d1eca1566fe9 F src/os_win.h 7b073010f1451abe501be30d12f6bc599824944a F src/pager.c 29e4d6d8e0a6d092644c58109a36293d1ea6fd2e1e7a26042f5462fd819493b7 F src/pager.h 4bf9b3213a4b2bebbced5eaa8b219cf25d4a82f385d093cd64b7e93e5285f66f -F src/parse.y b34d4eb8105271ea0d577ef165bb7b2a2b70e03b2e694e68e2e43b76389bf660 +F src/parse.y 0ab7c540d99bc0860d6ef07991ff9cc78673e4f4a80f431320f8155c769bf96e F src/pcache.c 084e638432c610f95aea72b8509f0845d2791293f39d1b82f0c0a7e089c3bb6b F src/pcache.h 4f87acd914cef5016fae3030343540d75f5b85a1877eed1a2a19b9f284248586 F src/pcache1.c 54881292a9a5db202b2c0ac541c5e3ef9a5e8c4f1c1383adb2601d5499a60e65 @@ -557,7 +557,7 @@ F src/shell.c.in b800bf8e02d9b4fd97078b68ca4371048f7196fc63accaa99c3c5943f72c80a F src/sqlite.h.in b07c70b7f3b9363aeb59ead2c2ceb2748b890c0012eb8a399987331baae09d1c F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8 F src/sqlite3ext.h a95cb9ed106e3d39e2118e4dcc15a14faec3fa50d0093425083d340d9dfd96e6 -F src/sqliteInt.h b6619030ed13b2a8d49c0b5cb0525db1f727966b65ab1ec40b5f11102af7254d +F src/sqliteInt.h 5a22bd250cb65077b66cad652f6a3257547a0952f391e9f29574c4757d5b6629 F src/sqliteLimit.h d7323ffea5208c6af2734574bae933ca8ed2ab728083caa117c9738581a31657 F src/status.c 4b8bc2a6905163a38b739854a35b826c737333fab5b1f8e03fa7eb9a4799c4c1 F src/table.c 0f141b58a16de7e2fbe81c308379e7279f4c6b50eb08efeec5892794a0ba30d1 @@ -1943,8 +1943,11 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 1269206db810460e55a52e178ba3332add42a11f66c5f292f8f0d29ccd61a4b8 -R 152bd288ae589530fc2745d322f45fd2 +P 22cc55e84f67f6f39b7dba07a4ef7ae958b2d926633faec91a278922053e50c6 +R 625d7798cb6317c1febdd91af24cd0e1 +T *branch * alter-table-if-exists +T *sym-alter-table-if-exists * +T -sym-trunk * U drh -Z b4c4dbfb44e2c416fb7b7bc180b39619 +Z 4d9daece2ae14d7891d26fed97e3045c # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index ec14e4c49e..ad2ed237da 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -22cc55e84f67f6f39b7dba07a4ef7ae958b2d926633faec91a278922053e50c6 \ No newline at end of file +460abf93ac6c47b0e3462e941d5de8e6b678dce9fac04a1e4923e2bf65cb59b2 \ No newline at end of file diff --git a/src/alter.c b/src/alter.c index 59b3bf0303..da11b680fb 100644 --- a/src/alter.c +++ b/src/alter.c @@ -124,7 +124,8 @@ static void renameReloadSchema(Parse *pParse, int iDb, u16 p5){ void sqlite3AlterRenameTable( Parse *pParse, /* Parser context. */ SrcList *pSrc, /* The table to rename. */ - Token *pName /* The new table name. */ + Token *pName, /* The new table name. */ + int noErr /* Suppress "no such table" errors */ ){ int iDb; /* Database that contains the table */ char *zDb; /* Name of database iDb */ @@ -140,8 +141,16 @@ void sqlite3AlterRenameTable( assert( pSrc->nSrc==1 ); assert( sqlite3BtreeHoldsAllMutexes(pParse->db) ); + if( noErr ) db->suppressErr++; pTab = sqlite3LocateTableItem(pParse, 0, &pSrc->a[0]); - if( !pTab ) goto exit_rename_table; + if( noErr ) db->suppressErr--; + if( !pTab ){ + if( noErr ){ + sqlite3CodeVerifyNamedSchema(pParse, pSrc->a[0].zDatabase); + sqlite3ForceNotReadOnly(pParse); + } + goto exit_rename_table; + } iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema); zDb = db->aDb[iDb].zDbSName; @@ -328,7 +337,7 @@ void sqlite3AlterFinishAddColumn(Parse *pParse, Token *pColDef){ if( pParse->nErr ) return; assert( db->mallocFailed==0 ); pNew = pParse->pNewTable; - assert( pNew ); + if( pNew==0 ) return; /* ALTER TABLE IF EXISTS and it does not exist */ assert( sqlite3BtreeHoldsAllMutexes(db) ); iDb = sqlite3SchemaToIndex(db, pNew->pSchema); @@ -338,6 +347,12 @@ void sqlite3AlterFinishAddColumn(Parse *pParse, Token *pColDef){ pDflt = sqlite3ColumnExpr(pNew, pCol); pTab = sqlite3FindTable(db, zTab, zDb); assert( pTab ); + if( pParse->ifNotExists>=2 ){ + sqlite3CodeVerifyNamedSchema(pParse, zDb); + sqlite3ForceNotReadOnly(pParse); + return; + } + #ifndef SQLITE_OMIT_AUTHORIZATION /* Invoke the authorization callback. */ @@ -475,7 +490,12 @@ void sqlite3AlterFinishAddColumn(Parse *pParse, Token *pColDef){ ** Routine sqlite3AlterFinishAddColumn() will be called to complete ** coding the "ALTER TABLE ... ADD" statement. */ -void sqlite3AlterBeginAddColumn(Parse *pParse, SrcList *pSrc){ +void sqlite3AlterBeginAddColumn( + Parse *pParse, + SrcList *pSrc, + int ifExists, /* ALTER TABLE IF EXISTS... */ + int ifNotExists /* ... ADD COLUMN IF NOT EXISTS .... */ +){ Table *pNew; Table *pTab; int iDb; @@ -485,10 +505,21 @@ void sqlite3AlterBeginAddColumn(Parse *pParse, SrcList *pSrc){ /* Look up the table being altered. */ assert( pParse->pNewTable==0 ); + assert( ifExists==0 || ifExists==1 ); + assert( ifNotExists==0 || ifNotExists==1 ); assert( sqlite3BtreeHoldsAllMutexes(db) ); if( db->mallocFailed ) goto exit_begin_add_column; + db->suppressErr += ifExists; pTab = sqlite3LocateTableItem(pParse, 0, &pSrc->a[0]); - if( !pTab ) goto exit_begin_add_column; + db->suppressErr -= ifExists; + if( !pTab ){ + if( ifExists ){ + sqlite3CodeVerifyNamedSchema(pParse, pSrc->a[0].zDatabase); + sqlite3ForceNotReadOnly(pParse); + } + goto exit_begin_add_column; + } + pParse->ifNotExists = ifNotExists; #ifndef SQLITE_OMIT_VIRTUALTABLE if( IsVirtual(pTab) ){ @@ -592,7 +623,9 @@ void sqlite3AlterRenameColumn( Parse *pParse, /* Parsing context */ SrcList *pSrc, /* Table being altered. pSrc->nSrc==1 */ Token *pOld, /* Name of column being changed */ - Token *pNew /* New column name */ + Token *pNew, /* New column name */ + int ifExistsTable, /* IF EXISTS on table */ + int ifExistsCol /* IF EXISTS on the column */ ){ sqlite3 *db = pParse->db; /* Database connection */ Table *pTab; /* Table being updated */ @@ -604,8 +637,16 @@ void sqlite3AlterRenameColumn( int bQuote; /* True to quote the new name */ /* Locate the table to be altered */ + db->suppressErr += ifExistsTable; pTab = sqlite3LocateTableItem(pParse, 0, &pSrc->a[0]); - if( !pTab ) goto exit_rename_column; + db->suppressErr -= ifExistsTable; + if( !pTab ){ + if( ifExistsTable ){ + sqlite3CodeVerifyNamedSchema(pParse, pSrc->a[0].zDatabase); + sqlite3ForceNotReadOnly(pParse); + } + goto exit_rename_column; + } /* Cannot alter a system table */ if( SQLITE_OK!=isAlterableTable(pParse, pTab) ) goto exit_rename_column; @@ -631,7 +672,12 @@ void sqlite3AlterRenameColumn( if( 0==sqlite3StrICmp(pTab->aCol[iCol].zCnName, zOld) ) break; } if( iCol==pTab->nCol ){ - sqlite3ErrorMsg(pParse, "no such column: \"%T\"", pOld); + if( ifExistsCol ){ + sqlite3CodeVerifyNamedSchema(pParse, pSrc->a[0].zDatabase); + sqlite3ForceNotReadOnly(pParse); + }else{ + sqlite3ErrorMsg(pParse, "no such column: \"%T\"", pOld); + } goto exit_rename_column; } @@ -2103,7 +2149,13 @@ drop_column_done: ** statement. Argument pSrc contains the possibly qualified name of the ** table being edited, and token pName the name of the column to drop. */ -void sqlite3AlterDropColumn(Parse *pParse, SrcList *pSrc, const Token *pName){ +void sqlite3AlterDropColumn( + Parse *pParse, /* Parsing context */ + SrcList *pSrc, /* Table to be altered */ + const Token *pName, /* Name of column to be dropped */ + int ifExistsTable, /* IF EXISTS flag on the table */ + int ifExistsColumn /* IF EXISTS flags on the column */ +){ sqlite3 *db = pParse->db; /* Database handle */ Table *pTab; /* Table to modify */ int iDb; /* Index of db containing pTab in aDb[] */ @@ -2115,8 +2167,16 @@ void sqlite3AlterDropColumn(Parse *pParse, SrcList *pSrc, const Token *pName){ assert( pParse->pNewTable==0 ); assert( sqlite3BtreeHoldsAllMutexes(db) ); if( NEVER(db->mallocFailed) ) goto exit_drop_column; + db->suppressErr += ifExistsTable; pTab = sqlite3LocateTableItem(pParse, 0, &pSrc->a[0]); - if( !pTab ) goto exit_drop_column; + db->suppressErr -= ifExistsTable; + if( !pTab ){ + if( ifExistsTable ){ + sqlite3CodeVerifyNamedSchema(pParse, pSrc->a[0].zDatabase); + sqlite3ForceNotReadOnly(pParse); + } + goto exit_drop_column; + } /* Make sure this is not an attempt to ALTER a view, virtual table or ** system table. */ @@ -2131,7 +2191,12 @@ void sqlite3AlterDropColumn(Parse *pParse, SrcList *pSrc, const Token *pName){ } iCol = sqlite3ColumnIndex(pTab, zCol); if( iCol<0 ){ - sqlite3ErrorMsg(pParse, "no such column: \"%T\"", pName); + if( ifExistsColumn ){ + sqlite3CodeVerifyNamedSchema(pParse, pSrc->a[0].zDatabase); + sqlite3ForceNotReadOnly(pParse); + }else{ + sqlite3ErrorMsg(pParse, "no such column: \"%T\"", pName); + } goto exit_drop_column; } diff --git a/src/build.c b/src/build.c index 8b1a8009d6..1a49a53a0a 100644 --- a/src/build.c +++ b/src/build.c @@ -1189,7 +1189,7 @@ i16 sqlite3TableColumnToStorage(Table *pTab, i16 iCol){ ** will return false for sqlite3_stmt_readonly() even if that statement ** is a read-only no-op. */ -static void sqlite3ForceNotReadOnly(Parse *pParse){ +void sqlite3ForceNotReadOnly(Parse *pParse){ int iReg = ++pParse->nMem; Vdbe *v = sqlite3GetVdbe(pParse); if( v ){ @@ -1561,7 +1561,11 @@ void sqlite3AddColumn(Parse *pParse, Token sName, Token sType){ hName = sqlite3StrIHash(z); for(i=0; inCol; i++){ if( p->aCol[i].hName==hName && sqlite3StrICmp(z, p->aCol[i].zCnName)==0 ){ - sqlite3ErrorMsg(pParse, "duplicate column name: %s", z); + if( pParse->ifNotExists ){ + pParse->ifNotExists = 2; + }else{ + sqlite3ErrorMsg(pParse, "duplicate column name: %s", z); + } sqlite3DbFree(db, z); return; } diff --git a/src/parse.y b/src/parse.y index 6474024b61..ccd824b4b0 100644 --- a/src/parse.y +++ b/src/parse.y @@ -1637,24 +1637,24 @@ cmd ::= ANALYZE nm(X) dbnm(Y). {sqlite3Analyze(pParse, &X, &Y);} //////////////////////// ALTER TABLE table ... //////////////////////////////// %ifndef SQLITE_OMIT_ALTERTABLE %ifndef SQLITE_OMIT_VIRTUALTABLE -cmd ::= ALTER TABLE fullname(X) RENAME TO nm(Z). { - sqlite3AlterRenameTable(pParse,X,&Z); +cmd ::= ALTER TABLE ifexists(E) fullname(X) RENAME TO nm(Z). { + sqlite3AlterRenameTable(pParse,X,&Z,E); } -cmd ::= ALTER TABLE add_column_fullname - ADD kwcolumn_opt columnname(Y) carglist. { +cmd ::= ALTER TABLE add_column_fullname columnname(Y) carglist. { Y.n = (int)(pParse->sLastToken.z-Y.z) + pParse->sLastToken.n; sqlite3AlterFinishAddColumn(pParse, &Y); } -cmd ::= ALTER TABLE fullname(X) DROP kwcolumn_opt nm(Y). { - sqlite3AlterDropColumn(pParse, X, &Y); +cmd ::= ALTER TABLE ifexists(E1) fullname(X) DROP kwcolumn_opt ifexists(E2) nm(Y). { + sqlite3AlterDropColumn(pParse, X, &Y, E1, E2); } -add_column_fullname ::= fullname(X). { +add_column_fullname ::= ifexists(E1) fullname(X) ADD kwcolumn_opt ifnotexists(E2). { disableLookaside(pParse); - sqlite3AlterBeginAddColumn(pParse, X); + sqlite3AlterBeginAddColumn(pParse, X, E1, E2); } -cmd ::= ALTER TABLE fullname(X) RENAME kwcolumn_opt nm(Y) TO nm(Z). { - sqlite3AlterRenameColumn(pParse, X, &Y, &Z); +cmd ::= ALTER TABLE ifexists(E1) fullname(X) + RENAME kwcolumn_opt ifexists(E2) nm(Y) TO nm(Z). { + sqlite3AlterRenameColumn(pParse, X, &Y, &Z, E1, E2); } kwcolumn_opt ::= . diff --git a/src/sqliteInt.h b/src/sqliteInt.h index 1ed600f4dd..853ec1e2c5 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -3580,6 +3580,7 @@ struct Parse { u8 bReturning; /* Coding a RETURNING trigger */ u8 eOrconf; /* Default ON CONFLICT policy for trigger steps */ u8 disableTriggers; /* True to disable triggers */ + u8 ifNotExists; /* IF NOT EXISTS flag on ALTER TABLE ADD COLUMN */ /************************************************************************** ** Fields above must be initialized to zero. The fields that follow, @@ -4478,6 +4479,7 @@ i16 sqlite3TableColumnToIndex(Index*, i16); i16 sqlite3TableColumnToStorage(Table*, i16); i16 sqlite3StorageColumnToTable(Table*, i16); #endif +void sqlite3ForceNotReadOnly(Parse*); void sqlite3StartTable(Parse*,Token*,Token*,int,int,int,int); #if SQLITE_ENABLE_HIDDEN_COLUMNS void sqlite3ColumnPropertiesFromName(Table*, Column*); @@ -4917,8 +4919,8 @@ extern sqlite3_uint64 sqlite3NProfileCnt; void sqlite3RootPageMoved(sqlite3*, int, Pgno, Pgno); void sqlite3Reindex(Parse*, Token*, Token*); void sqlite3AlterFunctions(void); -void sqlite3AlterRenameTable(Parse*, SrcList*, Token*); -void sqlite3AlterRenameColumn(Parse*, SrcList*, Token*, Token*); +void sqlite3AlterRenameTable(Parse*, SrcList*, Token*, int); +void sqlite3AlterRenameColumn(Parse*, SrcList*, Token*, Token*,int,int); int sqlite3GetToken(const unsigned char *, int *); void sqlite3NestedParse(Parse*, const char*, ...); void sqlite3ExpirePreparedStatements(sqlite3*, int); @@ -4942,8 +4944,8 @@ int sqlite3ResolveSelfReference(Parse*,Table*,int,Expr*,ExprList*); int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*); void sqlite3ColumnDefault(Vdbe *, Table *, int, int); void sqlite3AlterFinishAddColumn(Parse *, Token *); -void sqlite3AlterBeginAddColumn(Parse *, SrcList *); -void sqlite3AlterDropColumn(Parse*, SrcList*, const Token*); +void sqlite3AlterBeginAddColumn(Parse *, SrcList *,int,int); +void sqlite3AlterDropColumn(Parse*, SrcList*, const Token*, int, int); const void *sqlite3RenameTokenMap(Parse*, const void*, const Token*); void sqlite3RenameTokenRemap(Parse*, const void *pTo, const void *pFrom); void sqlite3RenameExprUnmap(Parse*, Expr*); -- 2.39.5