Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add a new algorithm for handling INSERT which reduces fragmentation on a VACUUM. Ticket #2075. More testing needed. (CVS 3643) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
9f56a878cbbc715262b3a48ee696148d |
User & Date: | drh 2007-02-13 15:01:11.000 |
Context
2007-02-14
| ||
09:19 | Use OP_VColumn instead of OP_Column when querying virtual tables for values to save in aggregate context records. #2230. (CVS 3644) (check-in: cb78f7cb0f user: danielk1977 tags: trunk) | |
2007-02-13
| ||
15:01 | Add a new algorithm for handling INSERT which reduces fragmentation on a VACUUM. Ticket #2075. More testing needed. (CVS 3643) (check-in: 9f56a878cb user: drh tags: trunk) | |
14:11 | Changes to the script that generates download.html so that it recognizes FTS2 modules. (CVS 3642) (check-in: 06c22de254 user: drh tags: trunk) | |
Changes
Changes to src/insert.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle INSERT statements in SQLite. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle INSERT statements in SQLite. ** ** $Id: insert.c,v 1.173 2007/02/13 15:01:11 drh Exp $ */ #include "sqliteInt.h" /* ** Set P3 of the most recently inserted opcode to a column affinity ** string for index pIdx. A column affinity string has one character ** for each column in the table, according to the affinity of the column: |
︙ | ︙ | |||
114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 | }else{ if( pItem->pTab->pSchema==pSchema && pItem->pTab->tnum==iTab ) return 1; } } return 0; } /* ** This routine is call to handle SQL of the following forms: ** ** insert into TABLE (IDLIST) values(EXPRLIST) ** insert into TABLE (IDLIST) select ** ** The IDLIST following the table name is always optional. If omitted, ** then a list of all columns for the table is substituted. The IDLIST ** appears in the pColumn parameter. pColumn is NULL if IDLIST is omitted. ** ** The pList parameter holds EXPRLIST in the first form of the INSERT ** statement above, and pSelect is NULL. For the second form, pList is ** NULL and pSelect is a pointer to the select statement used to generate ** data for the insert. ** | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | > > > > > > > > > > > > > > > > > > > > > | | | | | 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 | }else{ if( pItem->pTab->pSchema==pSchema && pItem->pTab->tnum==iTab ) return 1; } } return 0; } #ifndef SQLITE_OMIT_AUTOINCREMENT /* ** Write out code to initialize the autoincrement logic. This code ** looks up the current autoincrement value in the sqlite_sequence ** table and stores that value in a memory cell. Code generated by ** autoIncStep() will keep that memory cell holding the largest ** rowid value. Code generated by autoIncEnd() will write the new ** largest value of the counter back into the sqlite_sequence table. ** ** This routine returns the index of the mem[] cell that contains ** the maximum rowid counter. ** ** Two memory cells are allocated. The next memory cell after the ** one returned holds the rowid in sqlite_sequence where we will ** write back the revised maximum rowid. */ static int autoIncBegin( Parse *pParse, /* Parsing context */ int iDb, /* Index of the database holding pTab */ Table *pTab /* The table we are writing to */ ){ int memId = 0; if( pTab->autoInc ){ Vdbe *v = pParse->pVdbe; Db *pDb = &pParse->db->aDb[iDb]; int iCur = pParse->nTab; int addr; assert( v ); addr = sqlite3VdbeCurrentAddr(v); memId = pParse->nMem+1; pParse->nMem += 2; sqlite3OpenTable(pParse, iCur, iDb, pDb->pSchema->pSeqTab, OP_OpenRead); sqlite3VdbeAddOp(v, OP_Rewind, iCur, addr+13); sqlite3VdbeAddOp(v, OP_Column, iCur, 0); sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0); sqlite3VdbeAddOp(v, OP_Ne, 0x100, addr+12); sqlite3VdbeAddOp(v, OP_Rowid, iCur, 0); sqlite3VdbeAddOp(v, OP_MemStore, memId-1, 1); sqlite3VdbeAddOp(v, OP_Column, iCur, 1); sqlite3VdbeAddOp(v, OP_MemStore, memId, 1); sqlite3VdbeAddOp(v, OP_Goto, 0, addr+13); sqlite3VdbeAddOp(v, OP_Next, iCur, addr+4); sqlite3VdbeAddOp(v, OP_Close, iCur, 0); } return memId; } /* ** Update the maximum rowid for an autoincrement calculation. ** ** This routine should be called when the top of the stack holds a ** new rowid that is about to be inserted. If that new rowid is ** larger than the maximum rowid in the memId memory cell, then the ** memory cell is updated. The stack is unchanged. */ static void autoIncStep(Parse *pParse, int memId){ if( memId>0 ){ sqlite3VdbeAddOp(pParse->pVdbe, OP_MemMax, memId, 0); } } /* ** After doing one or more inserts, the maximum rowid is stored ** in mem[memId]. Generate code to write this value back into the ** the sqlite_sequence table. */ static void autoIncEnd( Parse *pParse, /* The parsing context */ int iDb, /* Index of the database holding pTab */ Table *pTab, /* Table we are inserting into */ int memId /* Memory cell holding the maximum rowid */ ){ if( pTab->autoInc ){ int iCur = pParse->nTab; Vdbe *v = pParse->pVdbe; Db *pDb = &pParse->db->aDb[iDb]; int addr; assert( v ); addr = sqlite3VdbeCurrentAddr(v); sqlite3OpenTable(pParse, iCur, iDb, pDb->pSchema->pSeqTab, OP_OpenWrite); sqlite3VdbeAddOp(v, OP_MemLoad, memId-1, 0); sqlite3VdbeAddOp(v, OP_NotNull, -1, addr+7); sqlite3VdbeAddOp(v, OP_Pop, 1, 0); sqlite3VdbeAddOp(v, OP_NewRowid, iCur, 0); sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0); sqlite3VdbeAddOp(v, OP_MemLoad, memId, 0); sqlite3VdbeAddOp(v, OP_MakeRecord, 2, 0); sqlite3VdbeAddOp(v, OP_Insert, iCur, 0); sqlite3VdbeAddOp(v, OP_Close, iCur, 0); } } #else /* ** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines ** above are all no-ops */ # define autoIncBegin(A,B,C) (0) # define autoIncStep(A,B) # define autoIncEnd(A,B,C,D) #endif /* SQLITE_OMIT_AUTOINCREMENT */ /* Forward declaration */ static int xferOptimization( Parse *pParse, /* Parser context */ Table *pDest, /* The table we are inserting into */ Select *pSelect, /* A SELECT statement to use as the data source */ int onError, /* How to handle constraint errors */ int iDbDest /* The database of pDest */ ); /* ** This routine is call to handle SQL of the following forms: ** ** insert into TABLE (IDLIST) values(EXPRLIST) ** insert into TABLE (IDLIST) select ** ** The IDLIST following the table name is always optional. If omitted, ** then a list of all columns for the table is substituted. The IDLIST ** appears in the pColumn parameter. pColumn is NULL if IDLIST is omitted. ** ** The pList parameter holds EXPRLIST in the first form of the INSERT ** statement above, and pSelect is NULL. For the second form, pList is ** NULL and pSelect is a pointer to the select statement used to generate ** data for the insert. ** ** The code generated follows one of four templates. For a simple ** select with data coming from a VALUES clause, the code executes ** once straight down through. The template looks like this: ** ** open write cursor to <table> and its indices ** puts VALUES clause expressions onto the stack ** write the resulting record into <table> ** cleanup ** ** The three remaining templates assume the statement is of the form ** ** INSERT INTO <table> SELECT ... ** ** If the SELECT clause is of the restricted form "SELECT * FROM <table2>" - ** in other words if the SELECT pulls all columns from a single table ** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and ** if <table2> and <table1> are distinct tables but have identical ** schemas, including all the same indices, then a special optimization ** is invoked that copies raw records from <table2> over to <table1>. ** See the xferOptimization() function for the implementation of this ** template. This is the second template. ** ** open a write cursor to <table> ** open read cursor on <table2> ** transfer all records in <table2> over to <table> ** close cursors ** foreach index on <table> ** open a write cursor on the <table> index ** open a read cursor on the corresponding <table2> index ** transfer all records from the read to the write cursors ** close cursors ** end foreach ** ** The third template is for when the second template does not apply ** and the SELECT clause does not read from <table> at any time. ** The generated code follows this template: ** ** goto B ** A: setup for the SELECT ** loop over the rows in the SELECT ** gosub C ** end loop ** cleanup after the SELECT ** goto D ** B: open write cursor to <table> and its indices ** goto A ** C: insert the select result into <table> ** return ** D: cleanup ** ** The fourth template is used if the insert statement takes its ** values from a SELECT but the data is being inserted into a table ** that is also read as part of the SELECT. In the third form, ** we have to use a intermediate table to store the results of ** the select. The template is like this: ** ** goto B ** A: setup for the SELECT |
︙ | ︙ | |||
217 218 219 220 221 222 223 | int iDb; #ifndef SQLITE_OMIT_TRIGGER int isView; /* True if attempting to insert into a view */ int triggers_exist = 0; /* True if there are FOR EACH ROW triggers */ #endif | < < < < | 349 350 351 352 353 354 355 356 357 358 359 360 361 362 | int iDb; #ifndef SQLITE_OMIT_TRIGGER int isView; /* True if attempting to insert into a view */ int triggers_exist = 0; /* True if there are FOR EACH ROW triggers */ #endif if( pParse->nErr || sqlite3MallocFailed() ){ goto insert_cleanup; } db = pParse->db; /* Locate the table into which we will be inserting new information. */ |
︙ | ︙ | |||
287 288 289 290 291 292 293 | sqlite3BeginWriteOperation(pParse, pSelect || triggers_exist, iDb); /* if there are row triggers, allocate a temp table for new.* references. */ if( triggers_exist ){ newIdx = pParse->nTab++; } | | > > > > > > > > > > > > > > < < < < | < < < < < < < < < < < < < < | 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 | sqlite3BeginWriteOperation(pParse, pSelect || triggers_exist, iDb); /* if there are row triggers, allocate a temp table for new.* references. */ if( triggers_exist ){ newIdx = pParse->nTab++; } #ifndef SQLITE_OMIT_XFER_OPT /* If the statement is of the form ** ** INSERT INTO <table1> SELECT * FROM <table2>; ** ** Then special optimizations can be applied that make the transfer ** very fast and which reduce fragmentation of indices. */ if( pColumn==0 && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){ assert( !triggers_exist ); assert( pList==0 ); goto insert_cleanup; } #endif /* SQLITE_OMIT_XFER_OPT */ /* If this is an AUTOINCREMENT table, look up the sequence number in the ** sqlite_sequence table and store it in memory cell counterMem. Also ** remember the rowid of the sqlite_sequence table entry in memory cell ** counterRowid. */ counterMem = autoIncBegin(pParse, iDb, pTab); /* Figure out how many columns of data are supplied. If the data ** is coming from a SELECT statement, then this step also generates ** all the code to implement the SELECT statement and invoke a subroutine ** to process each row of the result. (Template 2.) If the SELECT ** statement uses the the table that is being inserted into, then the ** subroutine is also coded here. That subroutine stores the SELECT |
︙ | ︙ | |||
587 588 589 590 591 592 593 | sqlite3VdbeAddOp(v, OP_NewRowid, base, counterMem); sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0); }else if( IsVirtual(pTab) ){ sqlite3VdbeAddOp(v, OP_Null, 0, 0); }else{ sqlite3VdbeAddOp(v, OP_NewRowid, base, counterMem); } | < < | < < | 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 | sqlite3VdbeAddOp(v, OP_NewRowid, base, counterMem); sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0); }else if( IsVirtual(pTab) ){ sqlite3VdbeAddOp(v, OP_Null, 0, 0); }else{ sqlite3VdbeAddOp(v, OP_NewRowid, base, counterMem); } autoIncStep(pParse, counterMem); /* Push onto the stack, data for all columns of the new entry, beginning ** with the first column. */ for(i=0; i<pTab->nCol; i++){ if( i==pTab->iPKey ){ /* The value of the INTEGER PRIMARY KEY column is always a NULL. |
︙ | ︙ | |||
684 685 686 687 688 689 690 | /* Close all tables opened */ sqlite3VdbeAddOp(v, OP_Close, base, 0); for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ sqlite3VdbeAddOp(v, OP_Close, idx+base, 0); } } | < | < < < < < < < < < < < < < < | 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 | /* Close all tables opened */ sqlite3VdbeAddOp(v, OP_Close, base, 0); for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ sqlite3VdbeAddOp(v, OP_Close, idx+base, 0); } } /* Update the sqlite_sequence table by storing the content of the ** counter value in memory counterMem back into the sqlite_sequence ** table. */ autoIncEnd(pParse, iDb, pTab, counterMem); /* ** Return the number of rows inserted. If this routine is ** generating code because of a call to sqlite3NestedParse(), do not ** invoke the callback function. */ if( db->flags & SQLITE_CountRows && pParse->nested==0 && !pParse->trigStack ){ |
︙ | ︙ | |||
1136 1137 1138 1139 1140 1141 1142 | VdbeComment((v, "# %s", pIdx->zName)); sqlite3VdbeOp3(v, op, i+base, pIdx->tnum, (char*)pKey, P3_KEYINFO_HANDOFF); } if( pParse->nTab<=base+i ){ pParse->nTab = base+i; } } | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 | VdbeComment((v, "# %s", pIdx->zName)); sqlite3VdbeOp3(v, op, i+base, pIdx->tnum, (char*)pKey, P3_KEYINFO_HANDOFF); } if( pParse->nTab<=base+i ){ pParse->nTab = base+i; } } #ifndef SQLITE_OMIT_XFER_OPT /* ** Check to collation names to see if they are compatible. */ static int xferCompatibleCollation(const char *z1, const char *z2){ if( z1==0 ){ return z2==0; } if( z2==0 ){ return 0; } return sqlite3StrICmp(z1, z2)==0; } /* ** Check to see if index pSrc is compatible as a source of data ** for index pDest in an insert transfer optimization. The rules ** for a compatible index: ** ** * The index is over the same set of columns ** * The same DESC and ASC markings occurs on all columns ** * The same onError processing (OE_Abort, OE_Ignore, etc) ** * The same collating sequence on each column */ static int xferCompatibleIndex(Index *pDest, Index *pSrc){ int i; assert( pDest && pSrc ); assert( pDest->pTable!=pSrc->pTable ); if( pDest->nColumn!=pSrc->nColumn ){ return 0; /* Different number of columns */ } if( pDest->onError!=pSrc->onError ){ return 0; /* Different conflict resolution strategies */ } for(i=0; i<pSrc->nColumn; i++){ if( pSrc->aiColumn[i]!=pDest->aiColumn[i] ){ return 0; /* Different columns indexed */ } if( pSrc->aSortOrder[i]!=pDest->aSortOrder[i] ){ return 0; /* Different sort orders */ } if( pSrc->azColl[i]!=pDest->azColl[i] ){ return 0; /* Different sort orders */ } } /* If no test above fails then the indices must be compatible */ return 1; } /* ** Attempt the transfer optimization on INSERTs of the form ** ** INSERT INTO tab1 SELECT * FROM tab2; ** ** This optimization is only attempted if ** ** (1) tab1 and tab2 have identical schemas including all the ** same indices ** ** (2) tab1 and tab2 are different tables ** ** (3) There must be no triggers on tab1 ** ** (4) The result set of the SELECT statement is "*" ** ** (5) The SELECT statement has no WHERE, HAVING, ORDER BY, GROUP BY, ** or LIMIT clause. ** ** (6) The SELECT statement is a simple (not a compound) select that ** contains only tab2 in its FROM clause ** ** This method for implementing the INSERT transfers raw records from ** tab2 over to tab1. The columns are not decoded. Raw records from ** the indices of tab2 are transfered to tab1 as well. In so doing, ** the resulting tab1 has much less fragmentation. ** ** This routine returns TRUE if the optimization is attempted. If any ** of the conditions above fail so that the optimization should not ** be attempted, then this routine returns FALSE. */ static int xferOptimization( Parse *pParse, /* Parser context */ Table *pDest, /* The table we are inserting into */ Select *pSelect, /* A SELECT statement to use as the data source */ int onError, /* How to handle constraint errors */ int iDbDest /* The database of pDest */ ){ ExprList *pEList; /* The result set of the SELECT */ Table *pSrc; /* The table in the FROM clause of SELECT */ Index *pSrcIdx, *pDestIdx; /* Source and destination indices */ struct SrcList_item *pItem; /* An element of pSelect->pSrc */ int i; /* Loop counter */ int iDbSrc; /* The database of pSrc */ int iSrc, iDest; /* Cursors from source and destination */ int addr1, addr2; /* Loop addresses */ int emptyDestTest; /* Address of test for empty pDest */ int emptySrcTest; /* Address of test for empty pSrc */ int memRowid; /* A memcell containing a rowid from pSrc */ Vdbe *v; /* The VDBE we are building */ KeyInfo *pKey; /* Key information for an index */ int counterMem; /* Memory register used by AUTOINC */ if( pSelect==0 ){ return 0; /* Must be of the form INSERT INTO ... SELECT ... */ } if( pDest->pTrigger ){ return 0; /* tab1 must not have triggers */ } #ifndef SQLITE_OMIT_VIRTUALTABLE if( pDest->isVirtual ){ return 0; /* tab1 must not be a virtual table */ } #endif if( onError==OE_Default ){ onError = OE_Abort; } if( onError!=OE_Abort && onError!=OE_Rollback ){ return 0; /* Cannot do OR REPLACE or OR IGNORE or OR FAIL */ } if( pSelect->pSrc==0 ){ return 0; /* SELECT must have a FROM clause */ } if( pSelect->pSrc->nSrc!=1 ){ return 0; /* FROM clause must have exactly one term */ } if( pSelect->pSrc->a[0].pSelect ){ return 0; /* FROM clause cannot contain a subquery */ } if( pSelect->pWhere ){ return 0; /* SELECT may not have a WHERE clause */ } if( pSelect->pOrderBy ){ return 0; /* SELECT may not have an ORDER BY clause */ } if( pSelect->pHaving ){ return 0; /* SELECT may not have a HAVING clause */ } if( pSelect->pGroupBy ){ return 0; /* SELECT may not have a GROUP BY clause */ } if( pSelect->pLimit ){ return 0; /* SELECT may not have a LIMIT clause */ } if( pSelect->pOffset ){ return 0; /* SELECT may not have an OFFSET clause */ } if( pSelect->pPrior ){ return 0; /* SELECT may not be a compound query */ } if( pSelect->isDistinct ){ return 0; /* SELECT may not be DISTINCT */ } pEList = pSelect->pEList; assert( pEList!=0 ); if( pEList->nExpr!=1 ){ return 0; /* The result set must have exactly one column */ } assert( pEList->a[0].pExpr ); if( pEList->a[0].pExpr->op!=TK_ALL ){ return 0; /* The result set must be the special operator "*" */ } /* At this point we have established that the statement is of the ** correct syntactic form to participate in this optimization. Now ** we have to check the semantics. */ pItem = pSelect->pSrc->a; pSrc = sqlite3LocateTable(pParse, pItem->zName, pItem->zDatabase); if( pSrc==0 ){ return 0; /* FROM clause does not contain a real table */ } if( pSrc==pDest ){ return 0; /* tab1 and tab2 may not be the same table */ } #ifndef SQLITE_OMIT_VIRTUALTABLE if( pSrc->isVirtual ){ return 0; /* tab2 must not be a virtual table */ } #endif if( pSrc->pSelect ){ return 0; /* tab2 may not be a view */ } if( pDest->nCol!=pSrc->nCol ){ return 0; /* Number of columns must be the same in tab1 and tab2 */ } if( pDest->iPKey!=pSrc->iPKey ){ return 0; /* Both tables must have the same INTEGER PRIMARY KEY */ } for(i=0; i<pDest->nCol; i++){ if( pDest->aCol[i].affinity!=pSrc->aCol[i].affinity ){ return 0; /* Affinity must be the same on all columns */ } if( !xferCompatibleCollation(pDest->aCol[i].zColl, pSrc->aCol[i].zColl) ){ return 0; /* Collating sequence must be the same on all columns */ } if( pDest->aCol[i].notNull && !pSrc->aCol[i].notNull ){ return 0; /* tab2 must be NOT NULL if tab1 is */ } } for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){ for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){ if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break; } if( pSrcIdx==0 ){ return 0; /* pDestIdx has no corresponding index in pSrc */ } } /* If we get this far, it means either: ** ** * We can always do the transfer if the table contains an ** an integer primary key ** ** * We can conditionally do the transfer if the destination ** table is empty. */ iDbSrc = sqlite3SchemaToIndex(pParse->db, pSrc->pSchema); v = sqlite3GetVdbe(pParse); iSrc = pParse->nTab++; iDest = pParse->nTab++; counterMem = autoIncBegin(pParse, iDbDest, pDest); sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite); if( pDest->iPKey<0 ){ /* The tables do not have an INTEGER PRIMARY KEY so that ** transfer optimization is only allowed if the destination ** table is initially empty */ addr1 = sqlite3VdbeAddOp(v, OP_Rewind, iDest, 0); emptyDestTest = sqlite3VdbeAddOp(v, OP_Goto, 0, 0); sqlite3VdbeJumpHere(v, addr1); }else{ emptyDestTest = 0; } sqlite3OpenTable(pParse, iSrc, iDbSrc, pSrc, OP_OpenRead); emptySrcTest = sqlite3VdbeAddOp(v, OP_Rewind, iSrc, 0); memRowid = pParse->nMem++; sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0); sqlite3VdbeAddOp(v, OP_MemStore, memRowid, 1); addr1 = sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0); sqlite3VdbeAddOp(v, OP_Dup, 0, 0); addr2 = sqlite3VdbeAddOp(v, OP_NotExists, iDest, 0); sqlite3VdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, "PRIMARY KEY must be unique", P3_STATIC); sqlite3VdbeJumpHere(v, addr2); autoIncStep(pParse, counterMem); sqlite3VdbeAddOp(v, OP_RowData, iSrc, 0); sqlite3VdbeOp3(v, OP_Insert, iDest, OPFLAG_NCHANGE|OPFLAG_LASTROWID, pDest->zName, 0); sqlite3VdbeAddOp(v, OP_Next, iSrc, addr1); autoIncEnd(pParse, iDbDest, pDest, counterMem); for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){ for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){ if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break; } assert( pSrcIdx ); sqlite3VdbeAddOp(v, OP_Close, iSrc, 0); sqlite3VdbeAddOp(v, OP_Close, iDest, 0); sqlite3VdbeAddOp(v, OP_Integer, iDbSrc, 0); pKey = sqlite3IndexKeyinfo(pParse, pSrcIdx); VdbeComment((v, "# %s", pSrcIdx->zName)); sqlite3VdbeOp3(v, OP_OpenRead, iSrc, pSrcIdx->tnum, (char*)pKey, P3_KEYINFO_HANDOFF); sqlite3VdbeAddOp(v, OP_Integer, iDbDest, 0); pKey = sqlite3IndexKeyinfo(pParse, pDestIdx); VdbeComment((v, "# %s", pDestIdx->zName)); sqlite3VdbeOp3(v, OP_OpenWrite, iDest, pDestIdx->tnum, (char*)pKey, P3_KEYINFO_HANDOFF); addr1 = sqlite3VdbeAddOp(v, OP_Rewind, iSrc, 0); sqlite3VdbeAddOp(v, OP_RowKey, iSrc, 0); if( pDestIdx->onError!=OE_None ){ sqlite3VdbeAddOp(v, OP_MemLoad, memRowid, 0); addr2 = sqlite3VdbeAddOp(v, OP_IsUnique, iDest, 0); sqlite3VdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, "UNIQUE constraint failed", P3_STATIC); sqlite3VdbeJumpHere(v, addr2); } sqlite3VdbeAddOp(v, OP_IdxInsert, iDest, 0); sqlite3VdbeAddOp(v, OP_Next, iSrc, addr1+1); sqlite3VdbeJumpHere(v, addr1); } sqlite3VdbeJumpHere(v, emptySrcTest); sqlite3VdbeAddOp(v, OP_Close, iSrc, 0); sqlite3VdbeAddOp(v, OP_Close, iDest, 0); if( emptyDestTest ){ sqlite3VdbeAddOp(v, OP_Halt, SQLITE_OK, 0); sqlite3VdbeJumpHere(v, emptyDestTest); sqlite3VdbeAddOp(v, OP_Close, iDest, 0); return 0; }else{ return 1; } } #endif /* SQLITE_OMIT_XFER_OPT */ |