diff options
| -rw-r--r-- | manifest | 24 | ||||
| -rw-r--r-- | manifest.uuid | 2 | ||||
| -rw-r--r-- | src/alter.c | 207 | ||||
| -rw-r--r-- | src/parse.y | 4 | ||||
| -rw-r--r-- | src/select.c | 6 | ||||
| -rw-r--r-- | src/sqliteInt.h | 2 | ||||
| -rw-r--r-- | test/alterdropcol.test | 113 |
7 files changed, 332 insertions, 26 deletions
@@ -1,5 +1,5 @@ -C Fix\san\sissue\swith\sthe\sLIKE\soperator\swhen\sit\sincludes\sthe\s"ESCAPE\s'_'"\sclause.\nTicket\s[c0aeea67d58ae0fd]. -D 2021-02-15T17:02:01.949 +C Add\sexperimental\simplementation\sof\sALTER\sTABLE\sDROP\sCOLUMN.\sOnly\ssome\scases\swork\sso\sfar. +D 2021-02-16T20:43:36.239 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -475,7 +475,7 @@ F spec.template 86a4a43b99ebb3e75e6b9a735d5fd293a24e90ca F sqlite.pc.in 42b7bf0d02e08b9e77734a47798d1a55a9e0716b F sqlite3.1 fc7ad8990fc8409983309bb80de8c811a7506786 F sqlite3.pc.in 48fed132e7cb71ab676105d2a4dc77127d8c1f3a -F src/alter.c c4a973d7fc68f81fbc5d1d028a88ddb2cb212faef8c6f63433175b8d5ecd83dc +F src/alter.c e912280eed81e2df936cc03af00a59abfcbfc24a27c3e5db8e8bd96022e2bf79 F src/analyze.c 01c6c6765cb4d40b473b71d85535093730770bb186f2f473abac25f07fcdee5c F src/attach.c e80162a47411f296bea550ed8fafd730481f4aa71e89ece23ba9c957eed15d4a F src/auth.c 08954fdc4cc2da5264ba5b75cfd90b67a6fc7d1710a02ccf917c38eadec77853 @@ -530,7 +530,7 @@ F src/os_win.c 77d39873836f1831a9b0b91894fec45ab0e9ca8e067dc8c549e1d1eca1566fe9 F src/os_win.h 7b073010f1451abe501be30d12f6bc599824944a F src/pager.c c49952ac5e9cc536778eff528091d79d38b3e45cbeeed4695dc05e207dc6547d F src/pager.h 4bf9b3213a4b2bebbced5eaa8b219cf25d4a82f385d093cd64b7e93e5285f66f -F src/parse.y 67ba503780de64b967ae195b7e14c33531329228e1bc0b83d63324beb733680b +F src/parse.y 8170885f22f4815c2adba3430ac7c990a39b60e04a0bcc4d02cce54e33475975 F src/pcache.c 385ff064bca69789d199a98e2169445dc16e4291fa807babd61d4890c3b34177 F src/pcache.h 4f87acd914cef5016fae3030343540d75f5b85a1877eed1a2a19b9f284248586 F src/pcache1.c 6596e10baf3d8f84cc1585d226cf1ab26564a5f5caf85a15757a281ff977d51a @@ -541,12 +541,12 @@ F src/printf.c 30e92b638fac71dcd85cdea1d12ecfae354c9adee2c71e8e1ae4727cde7c91ed F src/random.c 80f5d666f23feb3e6665a6ce04c7197212a88384 F src/resolve.c 89e4faf6171e179edf279905e8e45c4f9dd108777dc60716396729fbd7cb045e F src/rowset.c ba9515a922af32abe1f7d39406b9d35730ed65efab9443dc5702693b60854c92 -F src/select.c 9b4c84fd2703ee3c8b5d4b189387482a84c26acf2c38ca4835db5b48c68a09d4 +F src/select.c 4bd3b7d21d0d58dfdb4134f71d0330915e962a036ae1e1c8cde2f7f8b45de7e9 F src/shell.c.in 9ebc74e4f05cfbd0f4a36060fdaeff1da4e9af4458358722bc08c5a1ab9a0879 F src/sqlite.h.in 8855a19f37ade8dad189a9e48233a2ebe1b46faf469c7eb0906a654e252dcc57 F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8 F src/sqlite3ext.h 61b38c073d5e1e96a3d45271b257aef27d0d13da2bea5347692ae579475cd95e -F src/sqliteInt.h 4cb469678a0dbf814e4efbde4488a0161a5398e9a63141830d9f676b4e9fb0cc +F src/sqliteInt.h 514b41619e2c6b49188571df50249db2600ec33e4bfd1a6efb21f7e1c352f400 F src/sqliteLimit.h d7323ffea5208c6af2734574bae933ca8ed2ab728083caa117c9738581a31657 F src/status.c 4b8bc2a6905163a38b739854a35b826c737333fab5b1f8e03fa7eb9a4799c4c1 F src/table.c 0f141b58a16de7e2fbe81c308379e7279f4c6b50eb08efeec5892794a0ba30d1 @@ -647,6 +647,7 @@ F test/alter4.test dfd6086faf461b27ca2d2999848dcd207edf23352fc1592d0005c0844f3f0 F test/alterauth.test 63442ba61ceb0c1eeb63aac1f4f5cebfa509d352276059d27106ae256bafc959 F test/alterauth2.test c0a1ddf5b93d93cb0d15ba7acaf0c5c6fb515bbe861ede75b2d3fabad33b6499 F test/altercol.test 1d6a6fe698b81e626baea4881f5717f9bc53d7d07f1cd23ee7ad1b931f117ddf +F test/alterdropcol.test 77539861deb0f1a04fb938f33984d7ccac8618337492611ac41dffea4b316863 F test/alterlegacy.test 82022721ce0de29cedc9a7af63bc9fcc078b0ee000f8283b4b6ea9c3eab2f44b F test/altermalloc.test 167a47de41b5c638f5f5c6efb59784002b196fff70f98d9b4ed3cd74a3fb80c9 F test/altermalloc2.test fa7b1c1139ea39b8dec407cf1feb032ca8e0076bd429574969b619175ad0174b @@ -1900,7 +1901,10 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 7cc65ae57183b3c16f1102fca5603a36acda432e5d45e22a2996e5ebe069fc6a -R 3ed1929e1ca13bbf9ff7ef7c6dc85f4d -U drh -Z b9fb1ccb5459387ee6a14bca5dee2b45 +P 27d4117980d125975b5e70eeea58a6ab07bcf066e71b5fcb81b822e05afdbab0 +R af9ecd1afb3809621548f4fa239dc0d6 +T *branch * alter-table-drop-column +T *sym-alter-table-drop-column * +T -sym-trunk * +U dan +Z e426edd9d98bb43cdac675b225a7f76a diff --git a/manifest.uuid b/manifest.uuid index 1137531a2..031aa0ca6 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -27d4117980d125975b5e70eeea58a6ab07bcf066e71b5fcb81b822e05afdbab0
\ No newline at end of file +f0217937d7306fb595727e61e871e8b03d8c881d339a0865bfd0117d90d42e4e
\ No newline at end of file diff --git a/src/alter.c b/src/alter.c index 0f50f195e..4b00d4c38 100644 --- a/src/alter.c +++ b/src/alter.c @@ -870,23 +870,33 @@ static void renameTokenFree(sqlite3 *db, RenameToken *pToken){ /* ** Search the Parse object passed as the first argument for a RenameToken -** object associated with parse tree element pPtr. If found, remove it -** from the Parse object and add it to the list maintained by the -** RenameCtx object passed as the second argument. +** object associated with parse tree element pPtr. If found, return a pointer +** to it. Otherwise, return NULL. +** +** If the second argument passed to this function is not NULL and a matching +** RenameToken object is found, remove it from the Parse object and add it to +** the list maintained by the RenameCtx object. */ -static void renameTokenFind(Parse *pParse, struct RenameCtx *pCtx, void *pPtr){ +static RenameToken *renameTokenFind( + Parse *pParse, + struct RenameCtx *pCtx, + void *pPtr +){ RenameToken **pp; assert( pPtr!=0 ); for(pp=&pParse->pRename; (*pp); pp=&(*pp)->pNext){ if( (*pp)->p==pPtr ){ RenameToken *pToken = *pp; - *pp = pToken->pNext; - pToken->pNext = pCtx->pList; - pCtx->pList = pToken; - pCtx->nList++; - break; + if( pCtx ){ + *pp = pToken->pNext; + pToken->pNext = pCtx->pList; + pCtx->pList = pToken; + pCtx->nList++; + } + return pToken; } } + return 0; } /* @@ -1752,13 +1762,186 @@ static void renameTableTest( } /* +** Arguments: +** +** argv[0]: An integer - the index of the schema containing the table +** argv[1]: CREATE TABLE statement to modify. +** argv[2]: An integer - the index of the column to remove. +** argv[3]: Byte offset of first byte after last column definition in argv[1] +*/ +static void dropColumnFunc( + sqlite3_context *context, + int NotUsed, + sqlite3_value **argv +){ + sqlite3 *db = sqlite3_context_db_handle(context); + int iSchema = sqlite3_value_int(argv[0]); + const char *zSql = (const char*)sqlite3_value_text(argv[1]); + int iCol = sqlite3_value_int(argv[2]); + int iAddColOffset = sqlite3_value_int(argv[3]); + const char *zDb = db->aDb[iSchema].zDbSName; + int rc; + Parse sParse; + RenameToken *pCol; + Table *pTab; + const char *zEnd; + char *zNew = 0; + + rc = renameParseSql(&sParse, zDb, db, zSql, iSchema==1); + if( rc!=SQLITE_OK ) goto drop_column_done; + pTab = sParse.pNewTable; + + pCol = renameTokenFind(&sParse, 0, (void*)pTab->aCol[iCol].zName); + if( iCol<pTab->nCol-1 ){ + RenameToken *pEnd; + pEnd = renameTokenFind(&sParse, 0, (void*)pTab->aCol[iCol+1].zName); + zEnd = (const char*)pEnd->t.z; + }else{ + zEnd = (const char*)&zSql[iAddColOffset]; + while( pCol->t.z[0]!=',' && pCol->t.z[1]!='(' ) pCol->t.z--; + } + + zNew = sqlite3_mprintf("%.*s%s", pCol->t.z-zSql, zSql, zEnd); + sqlite3_result_text(context, zNew, -1, SQLITE_TRANSIENT); + sqlite3_free(zNew); + +drop_column_done: + renameParseCleanup(&sParse); +} + +void sqlite3AlterDropColumn(Parse *pParse, SrcList *pSrc, Token *pName){ + Table *pTab; + int i; + int iSchema = 0; + const char *zDb = 0; + sqlite3 *db = pParse->db; + char *zCol = 0; + int iCol = 0; + Vdbe *v; + int iCur; + int addr; + int reg; + int regRec; + Index *pIdx; + Index *pPk = 0; + + /* Look up the table being altered. */ + assert( pParse->pNewTable==0 ); + assert( sqlite3BtreeHoldsAllMutexes(db) ); + if( db->mallocFailed ) goto exit_drop_column; + pTab = sqlite3LocateTableItem(pParse, 0, &pSrc->a[0]); + if( !pTab ) goto exit_drop_column; + + /* Which schema holds the table to be altered */ + iSchema = sqlite3SchemaToIndex(db, pTab->pSchema); + assert( iSchema>=0 ); + zDb = db->aDb[iSchema].zDbSName; + +#ifndef SQLITE_OMIT_VIRTUALTABLE + if( IsVirtual(pTab) ){ + sqlite3ErrorMsg(pParse, "virtual tables may not be altered"); + goto exit_drop_column; + } +#endif + + /* Make sure this is not an attempt to ALTER a view. */ + if( pTab->pSelect ){ + sqlite3ErrorMsg(pParse, "cannot drop a column from a view"); + goto exit_drop_column; + } + if( SQLITE_OK!=isAlterableTable(pParse, pTab) ){ + goto exit_drop_column; + } + + /* Find the index of the column being dropped. */ + zCol = sqlite3NameFromToken(db, pName); + if( zCol==0 ){ + assert( db->mallocFailed ); + goto exit_drop_column; + } + iCol = sqlite3ColumnIndex(pTab, zCol); + if( iCol<0 ){ + sqlite3ErrorMsg(pParse, "no such column: \"%s\"", zCol); + goto exit_drop_column; + } + + /* Do not allow the user to drop a PRIMARY KEY column or a column + ** constrained by a UNIQUE constraint. */ + if( pTab->iPKey==iCol ){ + sqlite3ErrorMsg(pParse, "cannot drop PRIMARY KEY column: \"%s\"", zCol); + goto exit_drop_column; + } + for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ + int ii; + for(ii=0; ii<pIdx->nKeyCol; ii++){ + if( iCol==pIdx->aiColumn[ii] + && sqlite3_strnicmp("sqlite_", pIdx->zName, 7)==0 + ){ + sqlite3ErrorMsg(pParse, "cannot drop %s column: \"%s\"", + pIdx->idxType==2 ? "PRIMARY KEY" : "UNIQUE", zCol + ); + goto exit_drop_column; + } + } + } + + sqlite3NestedParse(pParse, + "UPDATE \"%w\"." DFLT_SCHEMA_TABLE " SET " + "sql = sqlite_drop_column(%d, sql, %d, %d) " + "WHERE (type=='table' AND tbl_name=%Q COLLATE nocase)" + , zDb, iSchema, iCol, pTab->addColOffset, pTab->zName + ); + + /* Drop and reload the database schema. */ + renameReloadSchema(pParse, iSchema); + renameTestSchema(pParse, zDb, iSchema==1); + + /* Edit rows of table on disk */ + v = sqlite3GetVdbe(pParse); + iCur = pParse->nTab++; + sqlite3OpenTable(pParse, iCur, iSchema, pTab, OP_OpenWrite); + addr = sqlite3VdbeAddOp1(v, OP_Rewind, iCur); + reg = ++pParse->nMem; + pParse->nMem += pTab->nCol; + if( HasRowid(pTab) ){ + sqlite3VdbeAddOp2(v, OP_Rowid, iCur, reg); + }else{ + pPk = sqlite3PrimaryKeyIndex(pTab); + } + for(i=0; i<pTab->nCol; i++){ + if( i!=iCol ){ + int iPos = (pPk ? sqlite3TableColumnToIndex(pPk, i) : i); + int iColPos = (pPk ? sqlite3TableColumnToIndex(pPk, iCol) : iCol); + int regOut = reg+1+iPos-(iPos>iColPos); + sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, i, regOut); + } + } + regRec = reg + pTab->nCol; + sqlite3VdbeAddOp3(v, OP_MakeRecord, reg+1, pTab->nCol-1, regRec); + if( HasRowid(pTab) ){ + sqlite3VdbeAddOp3(v, OP_Insert, iCur, regRec, reg); + }else{ + sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iCur, regRec, reg+1, pPk->nKeyCol); + } + + sqlite3VdbeAddOp2(v, OP_Next, iCur, addr+1); + sqlite3VdbeJumpHere(v, addr); + +exit_drop_column: + sqlite3DbFree(db, zCol); + sqlite3SrcListDelete(db, pSrc); + return; +} + +/* ** Register built-in functions used to help implement ALTER TABLE */ void sqlite3AlterFunctions(void){ static FuncDef aAlterTableFuncs[] = { - INTERNAL_FUNCTION(sqlite_rename_column, 9, renameColumnFunc), - INTERNAL_FUNCTION(sqlite_rename_table, 7, renameTableFunc), - INTERNAL_FUNCTION(sqlite_rename_test, 5, renameTableTest), + INTERNAL_FUNCTION(sqlite_rename_column, 9, renameColumnFunc), + INTERNAL_FUNCTION(sqlite_rename_table, 7, renameTableFunc), + INTERNAL_FUNCTION(sqlite_rename_test, 5, renameTableTest), + INTERNAL_FUNCTION(sqlite_drop_column, 4, dropColumnFunc), }; sqlite3InsertBuiltinFuncs(aAlterTableFuncs, ArraySize(aAlterTableFuncs)); } diff --git a/src/parse.y b/src/parse.y index 591cde3b9..92626f2c1 100644 --- a/src/parse.y +++ b/src/parse.y @@ -1621,6 +1621,10 @@ cmd ::= ALTER TABLE add_column_fullname Y.n = (int)(pParse->sLastToken.z-Y.z) + pParse->sLastToken.n; sqlite3AlterFinishAddColumn(pParse, &Y); } +cmd ::= ALTER TABLE fullname(X) DROP COLUMNKW nm(Y). { + sqlite3AlterDropColumn(pParse, X, &Y); +} + add_column_fullname ::= fullname(X). { disableLookaside(pParse); sqlite3AlterBeginAddColumn(pParse, X); diff --git a/src/select.c b/src/select.c index 1ab2ec367..04c29e6d2 100644 --- a/src/select.c +++ b/src/select.c @@ -262,7 +262,7 @@ int sqlite3JoinType(Parse *pParse, Token *pA, Token *pB, Token *pC){ ** Return the index of a column in a table. Return -1 if the column ** is not contained in the table. */ -static int columnIndex(Table *pTab, const char *zCol){ +int sqlite3ColumnIndex(Table *pTab, const char *zCol){ int i; u8 h = sqlite3StrIHash(zCol); Column *pCol; @@ -294,7 +294,7 @@ static int tableAndColumnIndex( assert( (piTab==0)==(piCol==0) ); /* Both or neither are NULL */ for(i=0; i<N; i++){ - iCol = columnIndex(pSrc->a[i].pTab, zCol); + iCol = sqlite3ColumnIndex(pSrc->a[i].pTab, zCol); if( iCol>=0 && (bIgnoreHidden==0 || IsHiddenColumn(&pSrc->a[i].pTab->aCol[iCol])==0) ){ @@ -504,7 +504,7 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){ int iRightCol; /* Column number of matching column on the right */ zName = pList->a[j].zName; - iRightCol = columnIndex(pRightTab, zName); + iRightCol = sqlite3ColumnIndex(pRightTab, zName); if( iRightCol<0 || !tableAndColumnIndex(pSrc, i+1, zName, &iLeft, &iLeftCol, 0) ){ diff --git a/src/sqliteInt.h b/src/sqliteInt.h index 17b1cb415..9c09b24ac 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -4531,6 +4531,7 @@ void sqlite3MaterializeView(Parse*, Table*, Expr*, ExprList*,Expr*,int); #endif int sqlite3JoinType(Parse*, Token*, Token*, Token*); +int sqlite3ColumnIndex(Table *pTab, const char *zCol); void sqlite3SetJoinExpr(Expr*,int); void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int); void sqlite3DeferForeignKey(Parse*, int); @@ -4715,6 +4716,7 @@ 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*, Token*); void *sqlite3RenameTokenMap(Parse*, void*, Token*); void sqlite3RenameTokenRemap(Parse*, void *pTo, void *pFrom); void sqlite3RenameExprUnmap(Parse*, Expr*); diff --git a/test/alterdropcol.test b/test/alterdropcol.test new file mode 100644 index 000000000..7bb597705 --- /dev/null +++ b/test/alterdropcol.test @@ -0,0 +1,113 @@ +# 2021 February 16 +# +# 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. +# +#************************************************************************* +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix alterdropcol + +# If SQLITE_OMIT_ALTERTABLE is defined, omit this file. +ifcapable !altertable { + finish_test + return +} + +do_execsql_test 1.0 { + CREATE TABLE t1(a, b, c); + CREATE VIEW v1 AS SELECT * FROM t1; + + CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z UNIQUE); + CREATE INDEX t2y ON t2(y); +} + +do_catchsql_test 1.1 { + ALTER TABLE nosuch DROP COLUMN z; +} {1 {no such table: nosuch}} + +do_catchsql_test 1.2 { + ALTER TABLE v1 DROP COLUMN c; +} {1 {cannot drop a column from a view}} + +ifcapable fts5 { + do_execsql_test 1.3.1 { + CREATE VIRTUAL TABLE ft1 USING fts5(one, two); + } + do_catchsql_test 1.3.2 { + ALTER TABLE ft1 DROP COLUMN two; + } {1 {virtual tables may not be altered}} +} + +do_catchsql_test 1.4 { + ALTER TABLE sqlite_schema DROP COLUMN sql; +} {1 {table sqlite_master may not be altered}} + +do_catchsql_test 1.5 { + ALTER TABLE t1 DROP COLUMN d; +} {1 {no such column: "d"}} + +do_execsql_test 1.6.1 { + ALTER TABLE t1 DROP COLUMN b; +} +do_execsql_test 1.6.2 { + SELECT sql FROM sqlite_schema WHERE name = 't1' +} {{CREATE TABLE t1(a, c)}} + +do_execsql_test 1.7.1 { + ALTER TABLE t1 DROP COLUMN c; +} +do_execsql_test 1.7.2 { + SELECT sql FROM sqlite_schema WHERE name = 't1' +} {{CREATE TABLE t1(a)}} + + +do_catchsql_test 1.8 { + ALTER TABLE t2 DROP COLUMN z +} {1 {cannot drop UNIQUE column: "z"}} + +do_catchsql_test 1.9 { + ALTER TABLE t2 DROP COLUMN x +} {1 {cannot drop PRIMARY KEY column: "x"}} + +do_catchsql_test 1.10 { + ALTER TABLE t2 DROP COLUMN y +} {1 {cannot drop indexed column: "y"}} + +#------------------------------------------------------------------------- + +foreach {tn wo} { + 1 {} + 2 {WITHOUT ROWID} +} { eval [string map [list %TN% $tn %WO% $wo] { + + reset_db + do_execsql_test 2.%TN%.0 { + CREATE TABLE t1(x, y INTEGER PRIMARY KEY, z) %WO% ; + INSERT INTO t1 VALUES(1, 2, 3); + INSERT INTO t1 VALUES(4, 5, 6); + INSERT INTO t1 VALUES(7, 8, 9); + } + + do_execsql_test 2.%TN%.1 { + ALTER TABLE t1 DROP COLUMN x; + SELECT * FROM t1; + } { + 2 3 5 6 8 9 + } + do_execsql_test 2.%TN%.2 { + ALTER TABLE t1 DROP COLUMN z; + SELECT * FROM t1; + } { + 2 5 8 + } +}]} + +finish_test + |