SQLite

Check-in [4932f22848]
Login

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: 4932f22848b3d15a2b6dc5fa2cd69ce19182e2a4
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
Unified Diff Ignore Whitespace Patch
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
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->nErr++;
          goto insert_cleanup;
        }
      }
    }
  }

  /* If there is no IDLIST term but the table has an integer primary







|







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