Prototype implementation of IF EXISTS and IF NOT EXISTS clauses on the
various forms of ALTER TALE.
FossilOrigin-Name: 460abf93ac6c47b0e3462e941d5de8e6b678dce9fac04a1e4923e2bf65cb59b2
diff --git a/manifest b/manifest
index 31a7cb3..27c6570 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 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/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.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/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.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 ec14e4c..ad2ed23 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 59b3bf0..da11b68 100644
--- a/src/alter.c
+++ b/src/alter.c
@@ -124,7 +124,8 @@
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 @@
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 @@
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 @@
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 @@
** 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 @@
/* 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 @@
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 @@
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 @@
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 @@
** 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 @@
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 @@
}
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 8b1a800..1a49a53 100644
--- a/src/build.c
+++ b/src/build.c
@@ -1189,7 +1189,7 @@
** 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 @@
hName = sqlite3StrIHash(z);
for(i=0; i<p->nCol; 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 6474024..ccd824b 100644
--- a/src/parse.y
+++ b/src/parse.y
@@ -1637,24 +1637,24 @@
//////////////////////// 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 1ed600f..853ec1e 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -3580,6 +3580,7 @@
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 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 @@
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 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*);