Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Currently, if SQLite cannot find a table or index referred to by a query, it reloads the database schema from disk to see if the table or index has been added since the schema was cached in memory. Extend this behaviour to columns (which may have been added using ALTER TABLE) and fix some obscure cases related to tables and indexes (INDEXED BY, DROP TABLE etc.). |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
4932f22848b3d15a2b6dc5fa2cd69ce1 |
User & Date: | dan 2010-06-28 10:15:20.000 |
Context
2010-06-28
| ||
11:06 | Remove debugging code from test script. (check-in: a85ae33246 user: dan tags: trunk) | |
10:15 | Currently, if SQLite cannot find a table or index referred to by a query, it reloads the database schema from disk to see if the table or index has been added since the schema was cached in memory. Extend this behaviour to columns (which may have been added using ALTER TABLE) and fix some obscure cases related to tables and indexes (INDEXED BY, DROP TABLE etc.). (check-in: 4932f22848 user: dan tags: trunk) | |
2010-06-26
| ||
22:16 | Make walIndexTryHdr() a private function. Fix an issue with SQLITE_MUTEX_NOOP. (check-in: ec65bbd06b user: drh tags: trunk) | |
Changes
Changes to src/build.c.
︙ | ︙ | |||
2610 2611 2612 2613 2614 2615 2616 2617 2618 2619 2620 2621 2622 2623 | for(j=0, pTabCol=pTab->aCol; j<pTab->nCol; j++, pTabCol++){ if( sqlite3StrICmp(zColName, pTabCol->zName)==0 ) break; } if( j>=pTab->nCol ){ sqlite3ErrorMsg(pParse, "table %s has no column named %s", pTab->zName, zColName); goto exit_create_index; } pIndex->aiColumn[i] = j; /* Justification of the ALWAYS(pListItem->pExpr->pColl): Because of ** the way the "idxlist" non-terminal is constructed by the parser, ** if pListItem->pExpr is not null then either pListItem->pExpr->pColl ** must exist or else there must have been an OOM error. But if there | > | 2610 2611 2612 2613 2614 2615 2616 2617 2618 2619 2620 2621 2622 2623 2624 | for(j=0, pTabCol=pTab->aCol; j<pTab->nCol; j++, pTabCol++){ if( sqlite3StrICmp(zColName, pTabCol->zName)==0 ) break; } if( j>=pTab->nCol ){ sqlite3ErrorMsg(pParse, "table %s has no column named %s", pTab->zName, zColName); pParse->checkSchema = 1; goto exit_create_index; } pIndex->aiColumn[i] = j; /* Justification of the ALWAYS(pListItem->pExpr->pColl): Because of ** the way the "idxlist" non-terminal is constructed by the parser, ** if pListItem->pExpr is not null then either pListItem->pExpr->pColl ** must exist or else there must have been an OOM error. But if there |
︙ | ︙ |
Changes to src/insert.c.
︙ | ︙ | |||
723 724 725 726 727 728 729 | } if( j>=pTab->nCol ){ if( sqlite3IsRowid(pColumn->a[i].zName) ){ keyColumn = i; }else{ sqlite3ErrorMsg(pParse, "table %S has no column named %s", pTabList, 0, pColumn->a[i].zName); | | | 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 | } if( j>=pTab->nCol ){ if( sqlite3IsRowid(pColumn->a[i].zName) ){ keyColumn = i; }else{ sqlite3ErrorMsg(pParse, "table %S has no column named %s", pTabList, 0, pColumn->a[i].zName); pParse->checkSchema = 1; goto insert_cleanup; } } } } /* If there is no IDLIST term but the table has an integer primary |
︙ | ︙ |
Changes to src/resolve.c.
︙ | ︙ | |||
353 354 355 356 357 358 359 360 361 362 363 364 365 366 | if( zDb ){ sqlite3ErrorMsg(pParse, "%s: %s.%s.%s", zErr, zDb, zTab, zCol); }else if( zTab ){ sqlite3ErrorMsg(pParse, "%s: %s.%s", zErr, zTab, zCol); }else{ sqlite3ErrorMsg(pParse, "%s: %s", zErr, zCol); } pTopNC->nErr++; } /* If a column from a table in pSrcList is referenced, then record ** this fact in the pSrcList.a[].colUsed bitmask. Column 0 causes ** bit 0 to be set. Column 1 sets bit 1. And so forth. If the ** column number is greater than the number of bits in the bitmask | > | 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 | if( zDb ){ sqlite3ErrorMsg(pParse, "%s: %s.%s.%s", zErr, zDb, zTab, zCol); }else if( zTab ){ sqlite3ErrorMsg(pParse, "%s: %s.%s", zErr, zTab, zCol); }else{ sqlite3ErrorMsg(pParse, "%s: %s", zErr, zCol); } pParse->checkSchema = 1; pTopNC->nErr++; } /* If a column from a table in pSrcList is referenced, then record ** this fact in the pSrcList.a[].colUsed bitmask. Column 0 causes ** bit 0 to be set. Column 1 sets bit 1. And so forth. If the ** column number is greater than the number of bits in the bitmask |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
3016 3017 3018 3019 3020 3021 3022 3023 3024 3025 3026 3027 3028 3029 | Index *pIdx; for(pIdx=pTab->pIndex; pIdx && sqlite3StrICmp(pIdx->zName, zIndex); pIdx=pIdx->pNext ); if( !pIdx ){ sqlite3ErrorMsg(pParse, "no such index: %s", zIndex, 0); return SQLITE_ERROR; } pFrom->pIndex = pIdx; } return SQLITE_OK; } | > | 3016 3017 3018 3019 3020 3021 3022 3023 3024 3025 3026 3027 3028 3029 3030 | Index *pIdx; for(pIdx=pTab->pIndex; pIdx && sqlite3StrICmp(pIdx->zName, zIndex); pIdx=pIdx->pNext ); if( !pIdx ){ sqlite3ErrorMsg(pParse, "no such index: %s", zIndex, 0); pParse->checkSchema = 1; return SQLITE_ERROR; } pFrom->pIndex = pIdx; } return SQLITE_OK; } |
︙ | ︙ |
Changes to src/trigger.c.
︙ | ︙ | |||
492 493 494 495 496 497 498 499 500 501 502 503 504 505 | pTrigger = sqlite3HashFind(&(db->aDb[j].pSchema->trigHash), zName, nName); if( pTrigger ) break; } if( !pTrigger ){ if( !noErr ){ sqlite3ErrorMsg(pParse, "no such trigger: %S", pName, 0); } goto drop_trigger_cleanup; } sqlite3DropTriggerPtr(pParse, pTrigger); drop_trigger_cleanup: sqlite3SrcListDelete(db, pName); } | > | 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 | pTrigger = sqlite3HashFind(&(db->aDb[j].pSchema->trigHash), zName, nName); if( pTrigger ) break; } if( !pTrigger ){ if( !noErr ){ sqlite3ErrorMsg(pParse, "no such trigger: %S", pName, 0); } pParse->checkSchema = 1; goto drop_trigger_cleanup; } sqlite3DropTriggerPtr(pParse, pTrigger); drop_trigger_cleanup: sqlite3SrcListDelete(db, pName); } |
︙ | ︙ |
Changes to src/update.c.
︙ | ︙ | |||
208 209 210 211 212 213 214 215 216 217 218 219 220 221 | } if( j>=pTab->nCol ){ if( sqlite3IsRowid(pChanges->a[i].zName) ){ chngRowid = 1; pRowidExpr = pChanges->a[i].pExpr; }else{ sqlite3ErrorMsg(pParse, "no such column: %s", pChanges->a[i].zName); goto update_cleanup; } } #ifndef SQLITE_OMIT_AUTHORIZATION { int rc; rc = sqlite3AuthCheck(pParse, SQLITE_UPDATE, pTab->zName, | > | 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 | } if( j>=pTab->nCol ){ if( sqlite3IsRowid(pChanges->a[i].zName) ){ chngRowid = 1; pRowidExpr = pChanges->a[i].pExpr; }else{ sqlite3ErrorMsg(pParse, "no such column: %s", pChanges->a[i].zName); pParse->checkSchema = 1; goto update_cleanup; } } #ifndef SQLITE_OMIT_AUTHORIZATION { int rc; rc = sqlite3AuthCheck(pParse, SQLITE_UPDATE, pTab->zName, |
︙ | ︙ |
Added test/schema3.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 | # 2010 Jun 28 # # 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. # #*********************************************************************** # This file implements regression tests for SQLite library. # set testdir [file dirname $argv0] source $testdir/tester.tcl source $testdir/malloc_common.tcl source $testdir/lock_common.tcl # This block tests that if one client modifies the database schema, a # second client updates its internal cache of the database schema before # executing any queries. Specifically, it does not return a "no such column" # or "no such table" error if the table or column in question does exist # but was added after the second client loaded its cache of the database # schema. # # Types of schema modifications: # # 1. Adding a database table. # 2. Adding a database view. # 3. Adding a database index. # 4. Adding a database trigger. # 5. Adding a column to an existing table (ALTER TABLE). # do_multiclient_test tn { # Have connections [db1] and [db2] load the current database schema. # sql1 { SELECT * FROM sqlite_master } sql2 { SELECT * FROM sqlite_master } foreach {tn2 c1 c2} { 1 { CREATE TABLE t1(a, b) } { SELECT * FROM t1 } 2 { CREATE TABLE t2(a, b) } { UPDATE t2 SET a = b } 3 { CREATE TABLE t3(a, b) } { DELETE FROM t3 } 4 { CREATE TABLE t4(a, b) } { INSERT INTO t4 VALUES(1, 2) } 5 { CREATE TABLE t5(a, b) } { DROP TABLE t5 } 6 { CREATE TABLE t6(a, b) } { CREATE INDEX i1 ON t6(a) } 7 { ALTER TABLE t1 ADD COLUMN c } { SELECT a, b, c FROM t1 } 8 { ALTER TABLE t2 ADD COLUMN c } { UPDATE t2 SET a = c } 9 { ALTER TABLE t2 ADD COLUMN d } { UPDATE t2 SET d = c } 10 { ALTER TABLE t3 ADD COLUMN c } { DELETE FROM t3 WHERE c>10 } 11 { ALTER TABLE t4 ADD COLUMN c } { INSERT INTO t4(a,b,c) VALUES(1,2,3) } 12 { ALTER TABLE t6 ADD COLUMN c } { CREATE INDEX i2 ON t6(c) } 13 { ALTER TABLE t6 ADD COLUMN d } { CREATE TRIGGER tr1 AFTER UPDATE OF d ON t6 BEGIN SELECT 1, 2, 3; END; } 14 { CREATE INDEX i3 ON t1(a) } { DROP INDEX i3 } 15 { CREATE INDEX i4 ON t2(a) } { SELECT * FROM t2 INDEXED BY i4 ORDER BY a } 16 { CREATE TRIGGER tr2 AFTER INSERT ON t3 BEGIN SELECT 1 ; END } { DROP TRIGGER tr2 } 17 { CREATE VIEW v1 AS SELECT * FROM t1 } { SELECT a,b,c FROM v1 } 18 { ALTER TABLE t1 ADD COLUMN d } { SELECT a,b,c,d FROM v1 } 19 { CREATE TABLE t7(a, b) } { DROP TABLE IF EXISTS t7; CREATE TABLE t7(c, d); } 20 { CREATE INDEX i5 ON t7(c, d) } { DROP INDEX IF EXISTS i5; CREATE INDEX i5 ON t7(c) } 21 { CREATE TRIGGER tr3 BEFORE DELETE ON t7 BEGIN SELECT 1, 2, 3 ; END } { DROP TRIGGER IF EXISTS tr3; CREATE TRIGGER tr3 AFTER INSERT ON t7 BEGIN SELECT 1, 2, 3 ; END } } { do_test schema3-1.$tn.$tn2 { sql1 $c1 sql2 $c2 } {} } } finish_test |