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