Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add support for analyzing trigger programs to the sqlite3_expert code. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | schemalint |
| Files: | files | file ages | folders |
| SHA3-256: |
159e8022a9d6701532b8b60e0c41154b |
| User & Date: | dan 2017-04-17 17:03:08 |
Context
|
2017-04-18
| ||
| 09:04 | Fix sqlite3_expert handling of triggers on views. check-in: ff4976da user: dan tags: schemalint | |
|
2017-04-17
| ||
| 17:03 | Add support for analyzing trigger programs to the sqlite3_expert code. check-in: 159e8022 user: dan tags: schemalint | |
|
2017-04-15
| ||
| 16:52 | Fix problems with handling constraints on the rowid column in sqlite3expert.c. check-in: 2e630879 user: dan tags: schemalint | |
Changes
Changes to ext/expert/expert1.test.
255 255 CREATE TABLE t8(a, b); 256 256 } { 257 257 SELECT * FROM t8 WHERE a=? ORDER BY rowid 258 258 } { 259 259 CREATE INDEX t8_idx_00000061 ON t8(a); 260 260 0|0|0|SEARCH TABLE t8 USING INDEX t8_idx_00000061 (a=?) 261 261 } 262 + 263 +# Triggers 264 +# 265 +do_setup_rec_test $tn.14 { 266 + CREATE TABLE t9(a, b, c); 267 + CREATE TABLE t10(a, b, c); 268 + CREATE TRIGGER t9t AFTER INSERT ON t9 BEGIN 269 + UPDATE t10 SET a=new.a WHERE b = new.b; 270 + END; 271 +} { 272 + INSERT INTO t9 VALUES(?, ?, ?); 273 +} { 274 + CREATE INDEX t10_idx_00000062 ON t10(b); 275 + 0|0|0|SEARCH TABLE t10 USING INDEX t10_idx_00000062 (b=?) 276 +} 262 277 263 278 } 264 279 265 280 finish_test 266 281
Changes to ext/expert/sqlite3expert.c.
22 22 typedef sqlite3_uint64 u64; 23 23 24 24 typedef struct IdxColumn IdxColumn; 25 25 typedef struct IdxConstraint IdxConstraint; 26 26 typedef struct IdxScan IdxScan; 27 27 typedef struct IdxStatement IdxStatement; 28 28 typedef struct IdxTable IdxTable; 29 +typedef struct IdxWrite IdxWrite; 29 30 30 31 /* 31 32 ** A single constraint. Equivalent to either "col = ?" or "col < ?" (or 32 33 ** any other type of single-ended range constraint on a column). 33 34 ** 34 35 ** pLink: 35 36 ** Used to temporarily link IdxConstraint objects into lists while ................................................................................ 70 71 struct IdxTable { 71 72 int nCol; 72 73 char *zName; /* Table name */ 73 74 IdxColumn *aCol; 74 75 IdxTable *pNext; /* Next table in linked list of all tables */ 75 76 }; 76 77 78 +/* 79 +** An object of the following type is created for each unique table/write-op 80 +** seen. The objects are stored in a singly-linked list beginning at 81 +** sqlite3expert.pWrite. 82 +*/ 83 +struct IdxWrite { 84 + IdxTable *pTab; 85 + int eOp; /* SQLITE_UPDATE, DELETE or INSERT */ 86 + IdxWrite *pNext; 87 +}; 88 + 77 89 /* 78 90 ** Each statement being analyzed is represented by an instance of this 79 91 ** structure. 80 92 */ 81 93 struct IdxStatement { 82 94 int iId; /* Statement number */ 83 95 char *zSql; /* SQL statement */ ................................................................................ 114 126 ** sqlite3expert object. 115 127 */ 116 128 struct sqlite3expert { 117 129 sqlite3 *db; /* User database */ 118 130 sqlite3 *dbm; /* In-memory db for this analysis */ 119 131 sqlite3 *dbv; /* Vtab schema for this analysis */ 120 132 IdxTable *pTable; /* List of all IdxTable objects */ 121 - 122 133 IdxScan *pScan; /* List of scan objects */ 134 + IdxWrite *pWrite; /* List of write objects */ 123 135 IdxStatement *pStatement; /* List of IdxStatement objects */ 124 136 int bRun; /* True once analysis has run */ 125 137 char **pzErrmsg; 126 138 int rc; /* Error code from whereinfo hook */ 127 139 IdxHash hIdx; /* Hash containing all candidate indexes */ 128 140 char *zCandidates; /* For EXPERT_REPORT_CANDIDATES */ 129 141 }; ................................................................................ 401 413 } 402 414 } 403 415 } 404 416 405 417 pIdxInfo->estimatedCost = 1000000.0 / n; 406 418 return rc; 407 419 } 420 + 421 +static int expertUpdate( 422 + sqlite3_vtab *pVtab, 423 + int nData, 424 + sqlite3_value **azData, 425 + sqlite_int64 *pRowid 426 +){ 427 + return SQLITE_OK; 428 +} 408 429 409 430 static int idxRegisterVtab(sqlite3expert *p){ 410 431 static sqlite3_module expertModule = { 411 432 2, /* iVersion */ 412 433 expertConnect, /* xCreate - create a table */ 413 434 expertConnect, /* xConnect - connect to an existing table */ 414 435 expertBestIndex, /* xBestIndex - Determine search strategy */ ................................................................................ 417 438 0, /* xOpen - open a cursor */ 418 439 0, /* xClose - close a cursor */ 419 440 0, /* xFilter - configure scan constraints */ 420 441 0, /* xNext - advance a cursor */ 421 442 0, /* xEof */ 422 443 0, /* xColumn - read data */ 423 444 0, /* xRowid - read data */ 424 - 0, /* xUpdate - write data */ 445 + expertUpdate, /* xUpdate - write data */ 425 446 0, /* xBegin - begin transaction */ 426 447 0, /* xSync - sync transaction */ 427 448 0, /* xCommit - commit transaction */ 428 449 0, /* xRollback - rollback transaction */ 429 450 0, /* xFindFunction - function overloading */ 430 451 0, /* xRename - rename the table */ 431 452 0, /* xSavepoint */ ................................................................................ 922 943 IdxTable *pNext; 923 944 for(pIter=pTab; pIter; pIter=pNext){ 924 945 pNext = pIter->pNext; 925 946 sqlite3_free(pIter); 926 947 } 927 948 } 928 949 950 +/* 951 +** Free the linked list of IdxWrite objects starting at pTab. 952 +*/ 953 +static void idxWriteFree(IdxWrite *pTab){ 954 + IdxWrite *pIter; 955 + IdxWrite *pNext; 956 + for(pIter=pTab; pIter; pIter=pNext){ 957 + pNext = pIter->pNext; 958 + sqlite3_free(pIter); 959 + } 960 +} 961 + 962 + 929 963 930 964 /* 931 965 ** This function is called after candidate indexes have been created. It 932 966 ** runs all the queries to see which indexes they prefer, and populates 933 967 ** IdxStatement.zIdx and IdxStatement.zEQP with the results. 934 968 */ 935 969 int idxFindIndexes( ................................................................................ 992 1026 idxFinalize(&rc, pExplain); 993 1027 } 994 1028 995 1029 find_indexes_out: 996 1030 idxHashClear(&hIdx); 997 1031 return rc; 998 1032 } 1033 + 1034 +static int idxAuthCallback( 1035 + void *pCtx, 1036 + int eOp, 1037 + const char *z3, 1038 + const char *z4, 1039 + const char *zDb, 1040 + const char *zTrigger 1041 +){ 1042 + int rc = SQLITE_OK; 1043 + if( eOp==SQLITE_INSERT || eOp==SQLITE_UPDATE || eOp==SQLITE_DELETE ){ 1044 + if( sqlite3_stricmp(zDb, "main")==0 ){ 1045 + sqlite3expert *p = (sqlite3expert*)pCtx; 1046 + IdxTable *pTab; 1047 + for(pTab=p->pTable; pTab; pTab=pTab->pNext){ 1048 + if( 0==sqlite3_stricmp(z3, pTab->zName) ) break; 1049 + } 1050 + if( pTab ){ 1051 + IdxWrite *pWrite; 1052 + for(pWrite=p->pWrite; pWrite; pWrite=pWrite->pNext){ 1053 + if( pWrite->pTab==pTab && pWrite->eOp==eOp ) break; 1054 + } 1055 + if( pWrite==0 ){ 1056 + pWrite = idxMalloc(&rc, sizeof(IdxWrite)); 1057 + if( rc==SQLITE_OK ){ 1058 + pWrite->pTab = pTab; 1059 + pWrite->eOp = eOp; 1060 + pWrite->pNext = p->pWrite; 1061 + p->pWrite = pWrite; 1062 + } 1063 + } 1064 + } 1065 + } 1066 + } 1067 + return rc; 1068 +} 1069 + 1070 +static int idxProcessOneTrigger( 1071 + sqlite3expert *p, 1072 + IdxWrite *pWrite, 1073 + char **pzErr 1074 +){ 1075 + static const char *zInt = "t592690916721053953805701627921227776"; 1076 + static const char *zDrop = "DROP TABLE t592690916721053953805701627921227776"; 1077 + IdxTable *pTab = pWrite->pTab; 1078 + const char *zTab = pTab->zName; 1079 + const char *zSql = 1080 + "SELECT 'CREATE TEMP' || substr(sql, 7) FROM sqlite_master " 1081 + "WHERE tbl_name = %Q AND type IN ('table', 'trigger') " 1082 + "ORDER BY type;"; 1083 + sqlite3_stmt *pSelect = 0; 1084 + int rc = SQLITE_OK; 1085 + char *zWrite = 0; 1086 + 1087 + /* Create the table and its triggers in the temp schema */ 1088 + rc = idxPrintfPrepareStmt(p->db, &pSelect, pzErr, zSql, zTab, zTab); 1089 + while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSelect) ){ 1090 + const char *zCreate = (const char*)sqlite3_column_text(pSelect, 0); 1091 + rc = sqlite3_exec(p->dbv, zCreate, 0, 0, pzErr); 1092 + } 1093 + idxFinalize(&rc, pSelect); 1094 + 1095 + /* Rename the table in the temp schema to zInt */ 1096 + if( rc==SQLITE_OK ){ 1097 + char *z = sqlite3_mprintf("ALTER TABLE temp.%Q RENAME TO %Q", zTab, zInt); 1098 + if( z==0 ){ 1099 + rc = SQLITE_NOMEM; 1100 + }else{ 1101 + rc = sqlite3_exec(p->dbv, z, 0, 0, pzErr); 1102 + sqlite3_free(z); 1103 + } 1104 + } 1105 + 1106 + switch( pWrite->eOp ){ 1107 + case SQLITE_INSERT: { 1108 + int i; 1109 + zWrite = idxAppendText(&rc, zWrite, "INSERT INTO %Q VALUES(", zInt); 1110 + for(i=0; i<pTab->nCol; i++){ 1111 + zWrite = idxAppendText(&rc, zWrite, "%s?", i==0 ? "" : ", "); 1112 + } 1113 + zWrite = idxAppendText(&rc, zWrite, ")"); 1114 + break; 1115 + } 1116 + case SQLITE_UPDATE: { 1117 + int i; 1118 + zWrite = idxAppendText(&rc, zWrite, "UPDATE %Q SET ", zInt); 1119 + for(i=0; i<pTab->nCol; i++){ 1120 + zWrite = idxAppendText(&rc, zWrite, "%s%Q=?", i==0 ? "" : ", ", 1121 + pTab->aCol[i].zName 1122 + ); 1123 + } 1124 + break; 1125 + } 1126 + default: { 1127 + assert( pWrite->eOp==SQLITE_DELETE ); 1128 + if( rc==SQLITE_OK ){ 1129 + zWrite = sqlite3_mprintf("DELETE FROM %Q", zInt); 1130 + if( zWrite==0 ) rc = SQLITE_NOMEM; 1131 + } 1132 + } 1133 + } 1134 + 1135 + if( rc==SQLITE_OK ){ 1136 + sqlite3_stmt *pX = 0; 1137 + rc = sqlite3_prepare_v2(p->dbv, zWrite, -1, &pX, 0); 1138 + idxFinalize(&rc, pX); 1139 + } 1140 + sqlite3_free(zWrite); 1141 + 1142 + if( rc==SQLITE_OK ){ 1143 + rc = sqlite3_exec(p->dbv, zDrop, 0, 0, pzErr); 1144 + } 1145 + 1146 + return rc; 1147 +} 1148 + 1149 +static int idxProcessTriggers(sqlite3expert *p, char **pzErr){ 1150 + int rc = SQLITE_OK; 1151 + IdxWrite *pEnd = 0; 1152 + IdxWrite *pFirst = p->pWrite; 1153 + 1154 + while( rc==SQLITE_OK && pFirst!=pEnd ){ 1155 + IdxWrite *pIter; 1156 + for(pIter=pFirst; rc==SQLITE_OK && pIter!=pEnd; pIter=pIter->pNext){ 1157 + rc = idxProcessOneTrigger(p, pIter, pzErr); 1158 + } 1159 + pEnd = pFirst; 1160 + pFirst = p->pWrite; 1161 + } 1162 + 1163 + return rc; 1164 +} 1165 + 999 1166 1000 1167 static int idxCreateVtabSchema(sqlite3expert *p, char **pzErrmsg){ 1001 1168 int rc = idxRegisterVtab(p); 1002 1169 sqlite3_stmt *pSchema = 0; 1003 1170 1004 1171 /* For each table in the main db schema: 1005 1172 ** ................................................................................ 1069 1236 */ 1070 1237 if( rc==SQLITE_OK ){ 1071 1238 pNew->db = db; 1072 1239 rc = sqlite3_open(":memory:", &pNew->dbv); 1073 1240 } 1074 1241 if( rc==SQLITE_OK ){ 1075 1242 rc = sqlite3_open(":memory:", &pNew->dbm); 1243 + if( rc==SQLITE_OK ){ 1244 + sqlite3_db_config(pNew->dbm, SQLITE_DBCONFIG_FULL_EQP, 1, (int*)0); 1245 + } 1076 1246 } 1247 + 1077 1248 1078 1249 /* Copy the entire schema of database [db] into [dbm]. */ 1079 1250 if( rc==SQLITE_OK ){ 1080 1251 sqlite3_stmt *pSql; 1081 1252 rc = idxPrintfPrepareStmt(pNew->db, &pSql, pzErrmsg, 1082 1253 "SELECT sql FROM sqlite_master WHERE name NOT LIKE 'sqlite_%%'" 1083 1254 ); ................................................................................ 1088 1259 idxFinalize(&rc, pSql); 1089 1260 } 1090 1261 1091 1262 /* Create the vtab schema */ 1092 1263 if( rc==SQLITE_OK ){ 1093 1264 rc = idxCreateVtabSchema(pNew, pzErrmsg); 1094 1265 } 1266 + 1267 + /* Register the auth callback with dbv */ 1268 + if( rc==SQLITE_OK ){ 1269 + sqlite3_set_authorizer(pNew->dbv, idxAuthCallback, (void*)pNew); 1270 + } 1095 1271 1096 1272 /* If an error has occurred, free the new object and reutrn NULL. Otherwise, 1097 1273 ** return the new sqlite3expert handle. */ 1098 1274 if( rc!=SQLITE_OK ){ 1099 1275 sqlite3_expert_destroy(pNew); 1100 1276 pNew = 0; 1101 1277 } ................................................................................ 1132 1308 pNew->pNext = p->pStatement; 1133 1309 if( p->pStatement ) pNew->iId = p->pStatement->iId+1; 1134 1310 p->pStatement = pNew; 1135 1311 } 1136 1312 sqlite3_finalize(pStmt); 1137 1313 } 1138 1314 }else{ 1139 - idxDatabaseError(p->db, pzErr); 1315 + idxDatabaseError(p->dbv, pzErr); 1140 1316 } 1141 1317 } 1142 1318 1143 1319 if( rc!=SQLITE_OK ){ 1144 1320 idxScanFree(p->pScan, pScanOrig); 1145 1321 idxStatementFree(p->pStatement, pStmtOrig); 1146 1322 p->pScan = pScanOrig; ................................................................................ 1149 1325 1150 1326 return rc; 1151 1327 } 1152 1328 1153 1329 int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr){ 1154 1330 int rc; 1155 1331 IdxHashEntry *pEntry; 1332 + 1333 + rc = idxProcessTriggers(p, pzErr); 1156 1334 1157 1335 /* Create candidate indexes within the in-memory database file */ 1158 - rc = idxCreateCandidates(p, pzErr); 1336 + if( rc==SQLITE_OK ){ 1337 + rc = idxCreateCandidates(p, pzErr); 1338 + } 1159 1339 1160 1340 /* Formulate the EXPERT_REPORT_CANDIDATES text */ 1161 1341 for(pEntry=p->hIdx.pFirst; pEntry; pEntry=pEntry->pNext){ 1162 1342 p->zCandidates = idxAppendText(&rc, p->zCandidates, "%s;\n", pEntry->zVal); 1163 1343 } 1164 1344 1165 1345 /* Figure out which of the candidate indexes are preferred by the query ................................................................................ 1216 1396 void sqlite3_expert_destroy(sqlite3expert *p){ 1217 1397 if( p ){ 1218 1398 sqlite3_close(p->dbm); 1219 1399 sqlite3_close(p->dbv); 1220 1400 idxScanFree(p->pScan, 0); 1221 1401 idxStatementFree(p->pStatement, 0); 1222 1402 idxTableFree(p->pTable); 1403 + idxWriteFree(p->pWrite); 1223 1404 idxHashClear(&p->hIdx); 1224 1405 sqlite3_free(p->zCandidates); 1225 1406 sqlite3_free(p); 1226 1407 } 1227 1408 } 1228 1409 1229 1410 #endif /* !defined(SQLITE_TEST) || defined(SQLITE_ENABLE_WHEREINFO_HOOK) */ 1230 1411
Changes to src/build.c.
475 475 while( ALWAYS(p) && p->pNext!=pIndex ){ p = p->pNext; } 476 476 if( ALWAYS(p && p->pNext==pIndex) ){ 477 477 p->pNext = pIndex->pNext; 478 478 } 479 479 } 480 480 freeIndex(db, pIndex); 481 481 } 482 - db->flags |= SQLITE_InternChanges; 482 + db->bInternChanges = 1; 483 483 } 484 484 485 485 /* 486 486 ** Look through the list of open database files in db->aDb[] and if 487 487 ** any have been closed, remove them from the list. Reallocate the 488 488 ** db->aDb[] structure to a smaller size, if possible. 489 489 ** ................................................................................ 547 547 sqlite3BtreeEnterAll(db); 548 548 for(i=0; i<db->nDb; i++){ 549 549 Db *pDb = &db->aDb[i]; 550 550 if( pDb->pSchema ){ 551 551 sqlite3SchemaClear(pDb->pSchema); 552 552 } 553 553 } 554 - db->flags &= ~SQLITE_InternChanges; 554 + db->bInternChanges = 0; 555 555 sqlite3VtabUnlockList(db); 556 556 sqlite3BtreeLeaveAll(db); 557 557 sqlite3CollapseDatabaseArray(db); 558 558 } 559 559 560 560 /* 561 561 ** This routine is called when a commit occurs. 562 562 */ 563 563 void sqlite3CommitInternalChanges(sqlite3 *db){ 564 - db->flags &= ~SQLITE_InternChanges; 564 + db->bInternChanges = 0; 565 565 } 566 566 567 567 /* 568 568 ** Delete memory allocated for the column names of a table or view (the 569 569 ** Table.aCol[] array). 570 570 */ 571 571 void sqlite3DeleteColumnNames(sqlite3 *db, Table *pTable){ ................................................................................ 661 661 assert( iDb>=0 && iDb<db->nDb ); 662 662 assert( zTabName ); 663 663 assert( sqlite3SchemaMutexHeld(db, iDb, 0) ); 664 664 testcase( zTabName[0]==0 ); /* Zero-length table names are allowed */ 665 665 pDb = &db->aDb[iDb]; 666 666 p = sqlite3HashInsert(&pDb->pSchema->tblHash, zTabName, 0); 667 667 sqlite3DeleteTable(db, p); 668 - db->flags |= SQLITE_InternChanges; 668 + db->bInternChanges = 1; 669 669 } 670 670 671 671 /* 672 672 ** Given a token, return a string that consists of the text of that 673 673 ** token. Space to hold the returned string 674 674 ** is obtained from sqliteMalloc() and must be freed by the calling 675 675 ** function. ................................................................................ 2047 2047 pOld = sqlite3HashInsert(&pSchema->tblHash, p->zName, p); 2048 2048 if( pOld ){ 2049 2049 assert( p==pOld ); /* Malloc must have failed inside HashInsert() */ 2050 2050 sqlite3OomFault(db); 2051 2051 return; 2052 2052 } 2053 2053 pParse->pNewTable = 0; 2054 - db->flags |= SQLITE_InternChanges; 2054 + db->bInternChanges = 1; 2055 2055 2056 2056 #ifndef SQLITE_OMIT_ALTERTABLE 2057 2057 if( !p->pSelect ){ 2058 2058 const char *zName = (const char *)pParse->sNameToken.z; 2059 2059 int nName; 2060 2060 assert( !pSelect && pCons && pEnd ); 2061 2061 if( pCons->z==0 ){ ................................................................................ 3316 3316 p = sqlite3HashInsert(&pIndex->pSchema->idxHash, 3317 3317 pIndex->zName, pIndex); 3318 3318 if( p ){ 3319 3319 assert( p==pIndex ); /* Malloc must have failed */ 3320 3320 sqlite3OomFault(db); 3321 3321 goto exit_create_index; 3322 3322 } 3323 - db->flags |= SQLITE_InternChanges; 3323 + db->bInternChanges = 1; 3324 3324 if( pTblName!=0 ){ 3325 3325 pIndex->tnum = db->init.newTnum; 3326 3326 } 3327 3327 } 3328 3328 3329 3329 /* If this is the initial CREATE INDEX statement (or CREATE TABLE if the 3330 3330 ** index is an implied index for a UNIQUE or PRIMARY KEY constraint) then
Changes to src/main.c.
807 807 u32 mask; /* Mask of the bit in sqlite3.flags to set/clear */ 808 808 } aFlagOp[] = { 809 809 { SQLITE_DBCONFIG_ENABLE_FKEY, SQLITE_ForeignKeys }, 810 810 { SQLITE_DBCONFIG_ENABLE_TRIGGER, SQLITE_EnableTrigger }, 811 811 { SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, SQLITE_Fts3Tokenizer }, 812 812 { SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, SQLITE_LoadExtension }, 813 813 { SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE, SQLITE_NoCkptOnClose }, 814 + { SQLITE_DBCONFIG_FULL_EQP, SQLITE_FullEQP }, 814 815 }; 815 816 unsigned int i; 816 817 rc = SQLITE_ERROR; /* IMP: R-42790-23372 */ 817 818 for(i=0; i<ArraySize(aFlagOp); i++){ 818 819 if( aFlagOp[i].op==op ){ 819 820 int onoff = va_arg(ap, int); 820 821 int *pRes = va_arg(ap, int*); ................................................................................ 1248 1249 /* Obtain all b-tree mutexes before making any calls to BtreeRollback(). 1249 1250 ** This is important in case the transaction being rolled back has 1250 1251 ** modified the database schema. If the b-tree mutexes are not taken 1251 1252 ** here, then another shared-cache connection might sneak in between 1252 1253 ** the database rollback and schema reset, which can cause false 1253 1254 ** corruption reports in some cases. */ 1254 1255 sqlite3BtreeEnterAll(db); 1255 - schemaChange = (db->flags & SQLITE_InternChanges)!=0 && db->init.busy==0; 1256 + schemaChange = db->bInternChanges && db->init.busy==0; 1256 1257 1257 1258 for(i=0; i<db->nDb; i++){ 1258 1259 Btree *p = db->aDb[i].pBt; 1259 1260 if( p ){ 1260 1261 if( sqlite3BtreeIsInTrans(p) ){ 1261 1262 inTrans = 1; 1262 1263 } 1263 1264 sqlite3BtreeRollback(p, tripCode, !schemaChange); 1264 1265 } 1265 1266 } 1266 1267 sqlite3VtabRollback(db); 1267 1268 sqlite3EndBenignMalloc(); 1268 1269 1269 - if( (db->flags&SQLITE_InternChanges)!=0 && db->init.busy==0 ){ 1270 + if( db->bInternChanges && db->init.busy==0 ){ 1270 1271 sqlite3ExpirePreparedStatements(db); 1271 1272 sqlite3ResetAllSchemasOfConnection(db); 1272 1273 } 1273 1274 sqlite3BtreeLeaveAll(db); 1274 1275 1275 1276 /* Any deferred constraint violations have now been resolved. */ 1276 1277 db->nDeferredCons = 0;
Changes to src/prepare.c.
350 350 ** 351 351 ** After a database is initialized, the DB_SchemaLoaded bit is set 352 352 ** bit is set in the flags field of the Db structure. If the database 353 353 ** file was of zero-length, then the DB_Empty flag is also set. 354 354 */ 355 355 int sqlite3Init(sqlite3 *db, char **pzErrMsg){ 356 356 int i, rc; 357 - int commit_internal = !(db->flags&SQLITE_InternChanges); 357 + int commit_internal = db->bInternChanges==0; 358 358 359 359 assert( sqlite3_mutex_held(db->mutex) ); 360 360 assert( sqlite3BtreeHoldsMutex(db->aDb[0].pBt) ); 361 361 assert( db->init.busy==0 ); 362 362 rc = SQLITE_OK; 363 363 db->init.busy = 1; 364 364 ENC(db) = SCHEMA_ENC(db);
Changes to src/sqlite.h.in.
2003 2003 ** override this behaviour. The first parameter passed to this operation 2004 2004 ** is an integer - non-zero to disable checkpoints-on-close, or zero (the 2005 2005 ** default) to enable them. The second parameter is a pointer to an integer 2006 2006 ** into which is written 0 or 1 to indicate whether checkpoints-on-close 2007 2007 ** have been disabled - 0 if they are not disabled, 1 if they are. 2008 2008 ** </dd> 2009 2009 ** 2010 +** <dt>SQLITE_DBCONFIG_FULL_EQP</dt> 2011 +** <dd> By default, the output of EXPLAIN QUERY PLAN commands does not 2012 +** include output for any operations performed by trigger programs. This 2013 +** option is used to set or clear (the default) a flag that governs this 2014 +** behavior. The first parameter passed to this operation is an integer - 2015 +** non-zero to enable output for trigger programs, or zero to disable it. 2016 +** The second parameter is a pointer to an integer into which is written 2017 +** 0 or 1 to indicate whether output-for-triggers has been disabled - 0 if 2018 +** it is not disabled, 1 if it is. 2019 +** </dd> 2010 2020 ** </dl> 2011 2021 */ 2012 2022 #define SQLITE_DBCONFIG_MAINDBNAME 1000 /* const char* */ 2013 2023 #define SQLITE_DBCONFIG_LOOKASIDE 1001 /* void* int int */ 2014 2024 #define SQLITE_DBCONFIG_ENABLE_FKEY 1002 /* int int* */ 2015 2025 #define SQLITE_DBCONFIG_ENABLE_TRIGGER 1003 /* int int* */ 2016 2026 #define SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER 1004 /* int int* */ 2017 2027 #define SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION 1005 /* int int* */ 2018 2028 #define SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE 1006 /* int int* */ 2029 +#define SQLITE_DBCONFIG_FULL_EQP 1007 /* int int* */ 2019 2030 2020 2031 2021 2032 /* 2022 2033 ** CAPI3REF: Enable Or Disable Extended Result Codes 2023 2034 ** METHOD: sqlite3 2024 2035 ** 2025 2036 ** ^The sqlite3_extended_result_codes() routine enables or disables the
Changes to src/sqliteInt.h.
1330 1330 signed char nextAutovac; /* Autovac setting after VACUUM if >=0 */ 1331 1331 u8 suppressErr; /* Do not issue error messages if true */ 1332 1332 u8 vtabOnConflict; /* Value to return for s3_vtab_on_conflict() */ 1333 1333 u8 isTransactionSavepoint; /* True if the outermost savepoint is a TS */ 1334 1334 u8 mTrace; /* zero or more SQLITE_TRACE flags */ 1335 1335 u8 skipBtreeMutex; /* True if no shared-cache backends */ 1336 1336 u8 nSqlExec; /* Number of pending OP_SqlExec opcodes */ 1337 + u8 bInternChanges; /* There are uncommited schema changes */ 1337 1338 int nextPagesize; /* Pagesize after VACUUM if >0 */ 1338 1339 u32 magic; /* Magic number for detect library misuse */ 1339 1340 int nChange; /* Value returned by sqlite3_changes() */ 1340 1341 int nTotalChange; /* Value returned by sqlite3_total_changes() */ 1341 1342 int aLimit[SQLITE_N_LIMIT]; /* Limits */ 1342 1343 int nMaxSorterMmap; /* Maximum size of regions mapped by sorter */ 1343 1344 struct sqlite3InitInfo { /* Information used during initialization */ ................................................................................ 1445 1446 ** 1446 1447 ** Value constraints (enforced via assert()): 1447 1448 ** SQLITE_FullFSync == PAGER_FULLFSYNC 1448 1449 ** SQLITE_CkptFullFSync == PAGER_CKPT_FULLFSYNC 1449 1450 ** SQLITE_CacheSpill == PAGER_CACHE_SPILL 1450 1451 */ 1451 1452 #define SQLITE_VdbeTrace 0x00000001 /* True to trace VDBE execution */ 1452 -#define SQLITE_InternChanges 0x00000002 /* Uncommitted Hash table changes */ 1453 +#define SQLITE_FullEQP 0x00000002 /* Include triggers in EQP output */ 1453 1454 #define SQLITE_FullColNames 0x00000004 /* Show full column names on SELECT */ 1454 1455 #define SQLITE_FullFSync 0x00000008 /* Use full fsync on the backend */ 1455 1456 #define SQLITE_CkptFullFSync 0x00000010 /* Use full fsync for checkpoint */ 1456 1457 #define SQLITE_CacheSpill 0x00000020 /* OK to spill pager cache */ 1457 1458 #define SQLITE_ShortColNames 0x00000040 /* Show short columns names */ 1458 1459 #define SQLITE_CountRows 0x00000080 /* Count rows changed by INSERT, */ 1459 1460 /* DELETE, or UPDATE and return */
Changes to src/trigger.c.
580 580 if( pTrigger->pSchema==pTrigger->pTabSchema ){ 581 581 Table *pTab = tableOfTrigger(pTrigger); 582 582 Trigger **pp; 583 583 for(pp=&pTab->pTrigger; *pp!=pTrigger; pp=&((*pp)->pNext)); 584 584 *pp = (*pp)->pNext; 585 585 } 586 586 sqlite3DeleteTrigger(db, pTrigger); 587 - db->flags |= SQLITE_InternChanges; 587 + db->bInternChanges = 1; 588 588 } 589 589 } 590 590 591 591 /* 592 592 ** pEList is the SET clause of an UPDATE statement. Each entry 593 593 ** in pEList is of the format <id>=<expr>. If any of the entries 594 594 ** in pEList have an <id> which matches an identifier in pIdList,
Changes to src/vdbe.c.
3033 3033 } 3034 3034 db->isTransactionSavepoint = 0; 3035 3035 rc = p->rc; 3036 3036 }else{ 3037 3037 int isSchemaChange; 3038 3038 iSavepoint = db->nSavepoint - iSavepoint - 1; 3039 3039 if( p1==SAVEPOINT_ROLLBACK ){ 3040 - isSchemaChange = (db->flags & SQLITE_InternChanges)!=0; 3040 + isSchemaChange = db->bInternChanges; 3041 3041 for(ii=0; ii<db->nDb; ii++){ 3042 3042 rc = sqlite3BtreeTripAllCursors(db->aDb[ii].pBt, 3043 3043 SQLITE_ABORT_ROLLBACK, 3044 3044 isSchemaChange==0); 3045 3045 if( rc!=SQLITE_OK ) goto abort_due_to_error; 3046 3046 } 3047 3047 }else{ ................................................................................ 3052 3052 if( rc!=SQLITE_OK ){ 3053 3053 goto abort_due_to_error; 3054 3054 } 3055 3055 } 3056 3056 if( isSchemaChange ){ 3057 3057 sqlite3ExpirePreparedStatements(db); 3058 3058 sqlite3ResetAllSchemasOfConnection(db); 3059 - db->flags = (db->flags | SQLITE_InternChanges); 3059 + db->bInternChanges = 1; 3060 3060 } 3061 3061 } 3062 3062 3063 3063 /* Regardless of whether this is a RELEASE or ROLLBACK, destroy all 3064 3064 ** savepoints nested inside of the savepoint being operated on. */ 3065 3065 while( db->pSavepoint!=pSavepoint ){ 3066 3066 pTmp = db->pSavepoint; ................................................................................ 3332 3332 assert( pDb->pBt!=0 ); 3333 3333 assert( sqlite3SchemaMutexHeld(db, pOp->p1, 0) ); 3334 3334 /* See note about index shifting on OP_ReadCookie */ 3335 3335 rc = sqlite3BtreeUpdateMeta(pDb->pBt, pOp->p2, pOp->p3); 3336 3336 if( pOp->p2==BTREE_SCHEMA_VERSION ){ 3337 3337 /* When the schema cookie changes, record the new cookie internally */ 3338 3338 pDb->pSchema->schema_cookie = pOp->p3; 3339 - db->flags |= SQLITE_InternChanges; 3339 + db->bInternChanges = 1; 3340 3340 }else if( pOp->p2==BTREE_FILE_FORMAT ){ 3341 3341 /* Record changes in the file format */ 3342 3342 pDb->pSchema->file_format = pOp->p3; 3343 3343 } 3344 3344 if( pOp->p1==1 ){ 3345 3345 /* Invalidate all prepared statements whenever the TEMP database 3346 3346 ** schema is changed. Ticket #1644 */
Changes to src/vdbeaux.c.
1607 1607 int nSub = 0; /* Number of sub-vdbes seen so far */ 1608 1608 SubProgram **apSub = 0; /* Array of sub-vdbes */ 1609 1609 Mem *pSub = 0; /* Memory cell hold array of subprogs */ 1610 1610 sqlite3 *db = p->db; /* The database connection */ 1611 1611 int i; /* Loop counter */ 1612 1612 int rc = SQLITE_OK; /* Return code */ 1613 1613 Mem *pMem = &p->aMem[1]; /* First Mem of result set */ 1614 + int bFull = (p->explain==1 || (db->flags & SQLITE_FullEQP)); 1615 + Op *pOp; 1614 1616 1615 1617 assert( p->explain ); 1616 1618 assert( p->magic==VDBE_MAGIC_RUN ); 1617 1619 assert( p->rc==SQLITE_OK || p->rc==SQLITE_BUSY || p->rc==SQLITE_NOMEM ); 1618 1620 1619 1621 /* Even though this opcode does not use dynamic strings for 1620 1622 ** the result, result columns may become dynamic if the user calls ................................................................................ 1634 1636 ** listing has finished and sqlite3_step() should return SQLITE_DONE. 1635 1637 ** nRow is the sum of the number of rows in the main program, plus 1636 1638 ** the sum of the number of rows in all trigger subprograms encountered 1637 1639 ** so far. The nRow value will increase as new trigger subprograms are 1638 1640 ** encountered, but p->pc will eventually catch up to nRow. 1639 1641 */ 1640 1642 nRow = p->nOp; 1641 - if( p->explain==1 ){ 1643 + if( bFull ){ 1642 1644 /* The first 8 memory cells are used for the result set. So we will 1643 1645 ** commandeer the 9th cell to use as storage for an array of pointers 1644 1646 ** to trigger subprograms. The VDBE is guaranteed to have at least 9 1645 1647 ** cells. */ 1646 1648 assert( p->nMem>9 ); 1647 1649 pSub = &p->aMem[9]; 1648 1650 if( pSub->flags&MEM_Blob ){ ................................................................................ 1654 1656 for(i=0; i<nSub; i++){ 1655 1657 nRow += apSub[i]->nOp; 1656 1658 } 1657 1659 } 1658 1660 1659 1661 do{ 1660 1662 i = p->pc++; 1661 - }while( i<nRow && p->explain==2 && p->aOp[i].opcode!=OP_Explain ); 1662 - if( i>=nRow ){ 1663 - p->rc = SQLITE_OK; 1664 - rc = SQLITE_DONE; 1665 - }else if( db->u1.isInterrupted ){ 1666 - p->rc = SQLITE_INTERRUPT; 1667 - rc = SQLITE_ERROR; 1668 - sqlite3VdbeError(p, sqlite3ErrStr(p->rc)); 1669 - }else{ 1670 - char *zP4; 1671 - Op *pOp; 1663 + if( i>=nRow ){ 1664 + p->rc = SQLITE_OK; 1665 + rc = SQLITE_DONE; 1666 + break; 1667 + } 1672 1668 if( i<p->nOp ){ 1673 1669 /* The output line number is small enough that we are still in the 1674 1670 ** main program. */ 1675 1671 pOp = &p->aOp[i]; 1676 1672 }else{ 1677 1673 /* We are currently listing subprograms. Figure out which one and 1678 1674 ** pick up the appropriate opcode. */ ................................................................................ 1679 1675 int j; 1680 1676 i -= p->nOp; 1681 1677 for(j=0; i>=apSub[j]->nOp; j++){ 1682 1678 i -= apSub[j]->nOp; 1683 1679 } 1684 1680 pOp = &apSub[j]->aOp[i]; 1685 1681 } 1686 - if( p->explain==1 ){ 1682 + 1683 + /* When an OP_Program opcode is encounter (the only opcode that has 1684 + ** a P4_SUBPROGRAM argument), expand the size of the array of subprograms 1685 + ** kept in p->aMem[9].z to hold the new program - assuming this subprogram 1686 + ** has not already been seen. 1687 + */ 1688 + if( bFull && pOp->p4type==P4_SUBPROGRAM ){ 1689 + int nByte = (nSub+1)*sizeof(SubProgram*); 1690 + int j; 1691 + for(j=0; j<nSub; j++){ 1692 + if( apSub[j]==pOp->p4.pProgram ) break; 1693 + } 1694 + if( j==nSub ){ 1695 + rc = sqlite3VdbeMemGrow(pSub, nByte, nSub!=0); 1696 + if( rc!=SQLITE_OK ) break; 1697 + apSub = (SubProgram **)pSub->z; 1698 + apSub[nSub++] = pOp->p4.pProgram; 1699 + pSub->flags |= MEM_Blob; 1700 + pSub->n = nSub*sizeof(SubProgram*); 1701 + nRow += pOp->p4.pProgram->nOp; 1702 + } 1703 + } 1704 + }while( p->explain==2 && pOp->opcode!=OP_Explain ); 1705 + 1706 + if( rc==SQLITE_OK ){ 1707 + if( db->u1.isInterrupted ){ 1708 + p->rc = SQLITE_INTERRUPT; 1709 + rc = SQLITE_ERROR; 1710 + sqlite3VdbeError(p, sqlite3ErrStr(p->rc)); 1711 + }else{ 1712 + char *zP4; 1713 + if( p->explain==1 ){ 1714 + pMem->flags = MEM_Int; 1715 + pMem->u.i = i; /* Program counter */ 1716 + pMem++; 1717 + 1718 + pMem->flags = MEM_Static|MEM_Str|MEM_Term; 1719 + pMem->z = (char*)sqlite3OpcodeName(pOp->opcode); /* Opcode */ 1720 + assert( pMem->z!=0 ); 1721 + pMem->n = sqlite3Strlen30(pMem->z); 1722 + pMem->enc = SQLITE_UTF8; 1723 + pMem++; 1724 + } 1725 + 1726 + pMem->flags = MEM_Int; 1727 + pMem->u.i = pOp->p1; /* P1 */ 1728 + pMem++; 1729 + 1687 1730 pMem->flags = MEM_Int; 1688 - pMem->u.i = i; /* Program counter */ 1731 + pMem->u.i = pOp->p2; /* P2 */ 1689 1732 pMem++; 1690 - 1691 - pMem->flags = MEM_Static|MEM_Str|MEM_Term; 1692 - pMem->z = (char*)sqlite3OpcodeName(pOp->opcode); /* Opcode */ 1693 - assert( pMem->z!=0 ); 1694 - pMem->n = sqlite3Strlen30(pMem->z); 1695 - pMem->enc = SQLITE_UTF8; 1733 + 1734 + pMem->flags = MEM_Int; 1735 + pMem->u.i = pOp->p3; /* P3 */ 1696 1736 pMem++; 1697 1737 1698 - /* When an OP_Program opcode is encounter (the only opcode that has 1699 - ** a P4_SUBPROGRAM argument), expand the size of the array of subprograms 1700 - ** kept in p->aMem[9].z to hold the new program - assuming this subprogram 1701 - ** has not already been seen. 1702 - */ 1703 - if( pOp->p4type==P4_SUBPROGRAM ){ 1704 - int nByte = (nSub+1)*sizeof(SubProgram*); 1705 - int j; 1706 - for(j=0; j<nSub; j++){ 1707 - if( apSub[j]==pOp->p4.pProgram ) break; 1708 - } 1709 - if( j==nSub && SQLITE_OK==sqlite3VdbeMemGrow(pSub, nByte, nSub!=0) ){ 1710 - apSub = (SubProgram **)pSub->z; 1711 - apSub[nSub++] = pOp->p4.pProgram; 1712 - pSub->flags |= MEM_Blob; 1713 - pSub->n = nSub*sizeof(SubProgram*); 1714 - } 1715 - } 1716 - } 1717 - 1718 - pMem->flags = MEM_Int; 1719 - pMem->u.i = pOp->p1; /* P1 */ 1720 - pMem++; 1721 - 1722 - pMem->flags = MEM_Int; 1723 - pMem->u.i = pOp->p2; /* P2 */ 1724 - pMem++; 1725 - 1726 - pMem->flags = MEM_Int; 1727 - pMem->u.i = pOp->p3; /* P3 */ 1728 - pMem++; 1729 - 1730 - if( sqlite3VdbeMemClearAndResize(pMem, 100) ){ /* P4 */ 1731 - assert( p->db->mallocFailed ); 1732 - return SQLITE_ERROR; 1733 - } 1734 - pMem->flags = MEM_Str|MEM_Term; 1735 - zP4 = displayP4(pOp, pMem->z, pMem->szMalloc); 1736 - if( zP4!=pMem->z ){ 1737 - pMem->n = 0; 1738 - sqlite3VdbeMemSetStr(pMem, zP4, -1, SQLITE_UTF8, 0); 1739 - }else{ 1740 - assert( pMem->z!=0 ); 1741 - pMem->n = sqlite3Strlen30(pMem->z); 1742 - pMem->enc = SQLITE_UTF8; 1743 - } 1744 - pMem++; 1745 - 1746 - if( p->explain==1 ){ 1747 - if( sqlite3VdbeMemClearAndResize(pMem, 4) ){ 1738 + if( sqlite3VdbeMemClearAndResize(pMem, 100) ){ /* P4 */ 1748 1739 assert( p->db->mallocFailed ); 1749 1740 return SQLITE_ERROR; 1750 1741 } 1751 1742 pMem->flags = MEM_Str|MEM_Term; 1752 - pMem->n = 2; 1753 - sqlite3_snprintf(3, pMem->z, "%.2x", pOp->p5); /* P5 */ 1754 - pMem->enc = SQLITE_UTF8; 1743 + zP4 = displayP4(pOp, pMem->z, pMem->szMalloc); 1744 + if( zP4!=pMem->z ){ 1745 + pMem->n = 0; 1746 + sqlite3VdbeMemSetStr(pMem, zP4, -1, SQLITE_UTF8, 0); 1747 + }else{ 1748 + assert( pMem->z!=0 ); 1749 + pMem->n = sqlite3Strlen30(pMem->z); 1750 + pMem->enc = SQLITE_UTF8; 1751 + } 1755 1752 pMem++; 1756 - 1753 + 1754 + if( p->explain==1 ){ 1755 + if( sqlite3VdbeMemClearAndResize(pMem, 4) ){ 1756 + assert( p->db->mallocFailed ); 1757 + return SQLITE_ERROR; 1758 + } 1759 + pMem->flags = MEM_Str|MEM_Term; 1760 + pMem->n = 2; 1761 + sqlite3_snprintf(3, pMem->z, "%.2x", pOp->p5); /* P5 */ 1762 + pMem->enc = SQLITE_UTF8; 1763 + pMem++; 1764 + 1757 1765 #ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS 1758 - if( sqlite3VdbeMemClearAndResize(pMem, 500) ){ 1759 - assert( p->db->mallocFailed ); 1760 - return SQLITE_ERROR; 1761 - } 1762 - pMem->flags = MEM_Str|MEM_Term; 1763 - pMem->n = displayComment(pOp, zP4, pMem->z, 500); 1764 - pMem->enc = SQLITE_UTF8; 1766 + if( sqlite3VdbeMemClearAndResize(pMem, 500) ){ 1767 + assert( p->db->mallocFailed ); 1768 + return SQLITE_ERROR; 1769 + } 1770 + pMem->flags = MEM_Str|MEM_Term; 1771 + pMem->n = displayComment(pOp, zP4, pMem->z, 500); 1772 + pMem->enc = SQLITE_UTF8; 1765 1773 #else 1766 - pMem->flags = MEM_Null; /* Comment */ 1774 + pMem->flags = MEM_Null; /* Comment */ 1767 1775 #endif 1776 + } 1777 + 1778 + p->nResColumn = 8 - 4*(p->explain-1); 1779 + p->pResultSet = &p->aMem[1]; 1780 + p->rc = SQLITE_OK; 1781 + rc = SQLITE_ROW; 1768 1782 } 1769 - 1770 - p->nResColumn = 8 - 4*(p->explain-1); 1771 - p->pResultSet = &p->aMem[1]; 1772 - p->rc = SQLITE_OK; 1773 - rc = SQLITE_ROW; 1774 1783 } 1775 1784 return rc; 1776 1785 } 1777 1786 #endif /* SQLITE_OMIT_EXPLAIN */ 1778 1787 1779 1788 #ifdef SQLITE_DEBUG 1780 1789 /*