1 2 #pragma ident "%Z%%M% %I% %E% SMI" 3 4 /* 5 ** 2001 September 15 6 ** 7 ** The author disclaims copyright to this source code. In place of 8 ** a legal notice, here is a blessing: 9 ** 10 ** May you do good and not evil. 11 ** May you find forgiveness for yourself and forgive others. 12 ** May you share freely, never taking more than you give. 13 ** 14 ************************************************************************* 15 ** This file contains C code routines that are called by the parser 16 ** to handle DELETE FROM statements. 17 ** 18 ** $Id: delete.c,v 1.61 2004/02/24 01:05:32 drh Exp $ 19 */ 20 #include "sqliteInt.h" 21 22 /* 23 ** Look up every table that is named in pSrc. If any table is not found, 24 ** add an error message to pParse->zErrMsg and return NULL. If all tables 25 ** are found, return a pointer to the last table. 26 */ 27 Table *sqliteSrcListLookup(Parse *pParse, SrcList *pSrc){ 28 Table *pTab = 0; 29 int i; 30 for(i=0; i<pSrc->nSrc; i++){ 31 const char *zTab = pSrc->a[i].zName; 32 const char *zDb = pSrc->a[i].zDatabase; 33 pTab = sqliteLocateTable(pParse, zTab, zDb); 34 pSrc->a[i].pTab = pTab; 35 } 36 return pTab; 37 } 38 39 /* 40 ** Check to make sure the given table is writable. If it is not 41 ** writable, generate an error message and return 1. If it is 42 ** writable return 0; 43 */ 44 int sqliteIsReadOnly(Parse *pParse, Table *pTab, int viewOk){ 45 if( pTab->readOnly ){ 46 sqliteErrorMsg(pParse, "table %s may not be modified", pTab->zName); 47 return 1; 48 } 49 if( !viewOk && pTab->pSelect ){ 50 sqliteErrorMsg(pParse, "cannot modify %s because it is a view",pTab->zName); 51 return 1; 52 } 53 return 0; 54 } 55 56 /* 57 ** Process a DELETE FROM statement. 58 */ 59 void sqliteDeleteFrom( 60 Parse *pParse, /* The parser context */ 61 SrcList *pTabList, /* The table from which we should delete things */ 62 Expr *pWhere /* The WHERE clause. May be null */ 63 ){ 64 Vdbe *v; /* The virtual database engine */ 65 Table *pTab; /* The table from which records will be deleted */ 66 const char *zDb; /* Name of database holding pTab */ 67 int end, addr; /* A couple addresses of generated code */ 68 int i; /* Loop counter */ 69 WhereInfo *pWInfo; /* Information about the WHERE clause */ 70 Index *pIdx; /* For looping over indices of the table */ 71 int iCur; /* VDBE Cursor number for pTab */ 72 sqlite *db; /* Main database structure */ 73 int isView; /* True if attempting to delete from a view */ 74 AuthContext sContext; /* Authorization context */ 75 76 int row_triggers_exist = 0; /* True if any triggers exist */ 77 int before_triggers; /* True if there are BEFORE triggers */ 78 int after_triggers; /* True if there are AFTER triggers */ 79 int oldIdx = -1; /* Cursor for the OLD table of AFTER triggers */ 80 81 sContext.pParse = 0; 82 if( pParse->nErr || sqlite_malloc_failed ){ 83 pTabList = 0; 84 goto delete_from_cleanup; 85 } 86 db = pParse->db; 87 assert( pTabList->nSrc==1 ); 88 89 /* Locate the table which we want to delete. This table has to be 90 ** put in an SrcList structure because some of the subroutines we 91 ** will be calling are designed to work with multiple tables and expect 92 ** an SrcList* parameter instead of just a Table* parameter. 93 */ 94 pTab = sqliteSrcListLookup(pParse, pTabList); 95 if( pTab==0 ) goto delete_from_cleanup; 96 before_triggers = sqliteTriggersExist(pParse, pTab->pTrigger, 97 TK_DELETE, TK_BEFORE, TK_ROW, 0); 98 after_triggers = sqliteTriggersExist(pParse, pTab->pTrigger, 99 TK_DELETE, TK_AFTER, TK_ROW, 0); 100 row_triggers_exist = before_triggers || after_triggers; 101 isView = pTab->pSelect!=0; 102 if( sqliteIsReadOnly(pParse, pTab, before_triggers) ){ 103 goto delete_from_cleanup; 104 } 105 assert( pTab->iDb<db->nDb ); 106 zDb = db->aDb[pTab->iDb].zName; 107 if( sqliteAuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0, zDb) ){ 108 goto delete_from_cleanup; 109 } 110 111 /* If pTab is really a view, make sure it has been initialized. 112 */ 113 if( isView && sqliteViewGetColumnNames(pParse, pTab) ){ 114 goto delete_from_cleanup; 115 } 116 117 /* Allocate a cursor used to store the old.* data for a trigger. 118 */ 119 if( row_triggers_exist ){ 120 oldIdx = pParse->nTab++; 121 } 122 123 /* Resolve the column names in all the expressions. 124 */ 125 assert( pTabList->nSrc==1 ); 126 iCur = pTabList->a[0].iCursor = pParse->nTab++; 127 if( pWhere ){ 128 if( sqliteExprResolveIds(pParse, pTabList, 0, pWhere) ){ 129 goto delete_from_cleanup; 130 } 131 if( sqliteExprCheck(pParse, pWhere, 0, 0) ){ 132 goto delete_from_cleanup; 133 } 134 } 135 136 /* Start the view context 137 */ 138 if( isView ){ 139 sqliteAuthContextPush(pParse, &sContext, pTab->zName); 140 } 141 142 /* Begin generating code. 143 */ 144 v = sqliteGetVdbe(pParse); 145 if( v==0 ){ 146 goto delete_from_cleanup; 147 } 148 sqliteBeginWriteOperation(pParse, row_triggers_exist, pTab->iDb); 149 150 /* If we are trying to delete from a view, construct that view into 151 ** a temporary table. 152 */ 153 if( isView ){ 154 Select *pView = sqliteSelectDup(pTab->pSelect); 155 sqliteSelect(pParse, pView, SRT_TempTable, iCur, 0, 0, 0); 156 sqliteSelectDelete(pView); 157 } 158 159 /* Initialize the counter of the number of rows deleted, if 160 ** we are counting rows. 161 */ 162 if( db->flags & SQLITE_CountRows ){ 163 sqliteVdbeAddOp(v, OP_Integer, 0, 0); 164 } 165 166 /* Special case: A DELETE without a WHERE clause deletes everything. 167 ** It is easier just to erase the whole table. Note, however, that 168 ** this means that the row change count will be incorrect. 169 */ 170 if( pWhere==0 && !row_triggers_exist ){ 171 if( db->flags & SQLITE_CountRows ){ 172 /* If counting rows deleted, just count the total number of 173 ** entries in the table. */ 174 int endOfLoop = sqliteVdbeMakeLabel(v); 175 int addr; 176 if( !isView ){ 177 sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0); 178 sqliteVdbeAddOp(v, OP_OpenRead, iCur, pTab->tnum); 179 } 180 sqliteVdbeAddOp(v, OP_Rewind, iCur, sqliteVdbeCurrentAddr(v)+2); 181 addr = sqliteVdbeAddOp(v, OP_AddImm, 1, 0); 182 sqliteVdbeAddOp(v, OP_Next, iCur, addr); 183 sqliteVdbeResolveLabel(v, endOfLoop); 184 sqliteVdbeAddOp(v, OP_Close, iCur, 0); 185 } 186 if( !isView ){ 187 sqliteVdbeAddOp(v, OP_Clear, pTab->tnum, pTab->iDb); 188 for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ 189 sqliteVdbeAddOp(v, OP_Clear, pIdx->tnum, pIdx->iDb); 190 } 191 } 192 } 193 194 /* The usual case: There is a WHERE clause so we have to scan through 195 ** the table and pick which records to delete. 196 */ 197 else{ 198 /* Begin the database scan 199 */ 200 pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 1, 0); 201 if( pWInfo==0 ) goto delete_from_cleanup; 202 203 /* Remember the key of every item to be deleted. 204 */ 205 sqliteVdbeAddOp(v, OP_ListWrite, 0, 0); 206 if( db->flags & SQLITE_CountRows ){ 207 sqliteVdbeAddOp(v, OP_AddImm, 1, 0); 208 } 209 210 /* End the database scan loop. 211 */ 212 sqliteWhereEnd(pWInfo); 213 214 /* Open the pseudo-table used to store OLD if there are triggers. 215 */ 216 if( row_triggers_exist ){ 217 sqliteVdbeAddOp(v, OP_OpenPseudo, oldIdx, 0); 218 } 219 220 /* Delete every item whose key was written to the list during the 221 ** database scan. We have to delete items after the scan is complete 222 ** because deleting an item can change the scan order. 223 */ 224 sqliteVdbeAddOp(v, OP_ListRewind, 0, 0); 225 end = sqliteVdbeMakeLabel(v); 226 227 /* This is the beginning of the delete loop when there are 228 ** row triggers. 229 */ 230 if( row_triggers_exist ){ 231 addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end); 232 sqliteVdbeAddOp(v, OP_Dup, 0, 0); 233 if( !isView ){ 234 sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0); 235 sqliteVdbeAddOp(v, OP_OpenRead, iCur, pTab->tnum); 236 } 237 sqliteVdbeAddOp(v, OP_MoveTo, iCur, 0); 238 239 sqliteVdbeAddOp(v, OP_Recno, iCur, 0); 240 sqliteVdbeAddOp(v, OP_RowData, iCur, 0); 241 sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0); 242 if( !isView ){ 243 sqliteVdbeAddOp(v, OP_Close, iCur, 0); 244 } 245 246 sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_BEFORE, pTab, -1, 247 oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default, 248 addr); 249 } 250 251 if( !isView ){ 252 /* Open cursors for the table we are deleting from and all its 253 ** indices. If there are row triggers, this happens inside the 254 ** OP_ListRead loop because the cursor have to all be closed 255 ** before the trigger fires. If there are no row triggers, the 256 ** cursors are opened only once on the outside the loop. 257 */ 258 pParse->nTab = iCur + 1; 259 sqliteOpenTableAndIndices(pParse, pTab, iCur); 260 261 /* This is the beginning of the delete loop when there are no 262 ** row triggers */ 263 if( !row_triggers_exist ){ 264 addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end); 265 } 266 267 /* Delete the row */ 268 sqliteGenerateRowDelete(db, v, pTab, iCur, pParse->trigStack==0); 269 } 270 271 /* If there are row triggers, close all cursors then invoke 272 ** the AFTER triggers 273 */ 274 if( row_triggers_exist ){ 275 if( !isView ){ 276 for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){ 277 sqliteVdbeAddOp(v, OP_Close, iCur + i, pIdx->tnum); 278 } 279 sqliteVdbeAddOp(v, OP_Close, iCur, 0); 280 } 281 sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_AFTER, pTab, -1, 282 oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default, 283 addr); 284 } 285 286 /* End of the delete loop */ 287 sqliteVdbeAddOp(v, OP_Goto, 0, addr); 288 sqliteVdbeResolveLabel(v, end); 289 sqliteVdbeAddOp(v, OP_ListReset, 0, 0); 290 291 /* Close the cursors after the loop if there are no row triggers */ 292 if( !row_triggers_exist ){ 293 for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){ 294 sqliteVdbeAddOp(v, OP_Close, iCur + i, pIdx->tnum); 295 } 296 sqliteVdbeAddOp(v, OP_Close, iCur, 0); 297 pParse->nTab = iCur; 298 } 299 } 300 sqliteVdbeAddOp(v, OP_SetCounts, 0, 0); 301 sqliteEndWriteOperation(pParse); 302 303 /* 304 ** Return the number of rows that were deleted. 305 */ 306 if( db->flags & SQLITE_CountRows ){ 307 sqliteVdbeAddOp(v, OP_ColumnName, 0, 1); 308 sqliteVdbeChangeP3(v, -1, "rows deleted", P3_STATIC); 309 sqliteVdbeAddOp(v, OP_Callback, 1, 0); 310 } 311 312 delete_from_cleanup: 313 sqliteAuthContextPop(&sContext); 314 sqliteSrcListDelete(pTabList); 315 sqliteExprDelete(pWhere); 316 return; 317 } 318 319 /* 320 ** This routine generates VDBE code that causes a single row of a 321 ** single table to be deleted. 322 ** 323 ** The VDBE must be in a particular state when this routine is called. 324 ** These are the requirements: 325 ** 326 ** 1. A read/write cursor pointing to pTab, the table containing the row 327 ** to be deleted, must be opened as cursor number "base". 328 ** 329 ** 2. Read/write cursors for all indices of pTab must be open as 330 ** cursor number base+i for the i-th index. 331 ** 332 ** 3. The record number of the row to be deleted must be on the top 333 ** of the stack. 334 ** 335 ** This routine pops the top of the stack to remove the record number 336 ** and then generates code to remove both the table record and all index 337 ** entries that point to that record. 338 */ 339 void sqliteGenerateRowDelete( 340 sqlite *db, /* The database containing the index */ 341 Vdbe *v, /* Generate code into this VDBE */ 342 Table *pTab, /* Table containing the row to be deleted */ 343 int iCur, /* Cursor number for the table */ 344 int count /* Increment the row change counter */ 345 ){ 346 int addr; 347 addr = sqliteVdbeAddOp(v, OP_NotExists, iCur, 0); 348 sqliteGenerateRowIndexDelete(db, v, pTab, iCur, 0); 349 sqliteVdbeAddOp(v, OP_Delete, iCur, 350 (count?OPFLAG_NCHANGE:0) | OPFLAG_CSCHANGE); 351 sqliteVdbeChangeP2(v, addr, sqliteVdbeCurrentAddr(v)); 352 } 353 354 /* 355 ** This routine generates VDBE code that causes the deletion of all 356 ** index entries associated with a single row of a single table. 357 ** 358 ** The VDBE must be in a particular state when this routine is called. 359 ** These are the requirements: 360 ** 361 ** 1. A read/write cursor pointing to pTab, the table containing the row 362 ** to be deleted, must be opened as cursor number "iCur". 363 ** 364 ** 2. Read/write cursors for all indices of pTab must be open as 365 ** cursor number iCur+i for the i-th index. 366 ** 367 ** 3. The "iCur" cursor must be pointing to the row that is to be 368 ** deleted. 369 */ 370 void sqliteGenerateRowIndexDelete( 371 sqlite *db, /* The database containing the index */ 372 Vdbe *v, /* Generate code into this VDBE */ 373 Table *pTab, /* Table containing the row to be deleted */ 374 int iCur, /* Cursor number for the table */ 375 char *aIdxUsed /* Only delete if aIdxUsed!=0 && aIdxUsed[i]!=0 */ 376 ){ 377 int i; 378 Index *pIdx; 379 380 for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){ 381 int j; 382 if( aIdxUsed!=0 && aIdxUsed[i-1]==0 ) continue; 383 sqliteVdbeAddOp(v, OP_Recno, iCur, 0); 384 for(j=0; j<pIdx->nColumn; j++){ 385 int idx = pIdx->aiColumn[j]; 386 if( idx==pTab->iPKey ){ 387 sqliteVdbeAddOp(v, OP_Dup, j, 0); 388 }else{ 389 sqliteVdbeAddOp(v, OP_Column, iCur, idx); 390 } 391 } 392 sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0); 393 if( db->file_format>=4 ) sqliteAddIdxKeyType(v, pIdx); 394 sqliteVdbeAddOp(v, OP_IdxDelete, iCur+i, 0); 395 } 396 } 397