/ Check-in [b243681a]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:More test cases to do with invalidating precompiled statements. (CVS 2273)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b243681a0e328ee0bbf1140abfb60d65d2102ad5
User & Date: danielk1977 2005-01-24 13:03:32
Context
2005-01-24
23:27
Clarify documentation of DEFAULT CURRENT_TIME etc. (version 3.1.0 and greater only). (CVS 2274) check-in: 557eb2ec user: danielk1977 tags: trunk
13:03
More test cases to do with invalidating precompiled statements. (CVS 2273) check-in: b243681a user: danielk1977 tags: trunk
12:46
Use the cache with loading a large table in select2-2.0. (CVS 2272) check-in: bd65b180 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/trigger.c.

   185    185   ** in order to complete the process of building the trigger.
   186    186   */
   187    187   void sqlite3FinishTrigger(
   188    188     Parse *pParse,          /* Parser context */
   189    189     TriggerStep *pStepList, /* The triggered program */
   190    190     Token *pAll             /* Token that describes the complete CREATE TRIGGER */
   191    191   ){
   192         -  Trigger *nt = 0;          /* The trigger whose construction is finishing up */
          192  +  Trigger *pTrig = 0;     /* The trigger whose construction is finishing up */
   193    193     sqlite3 *db = pParse->db;  /* The database */
   194    194     DbFixer sFix;
   195    195   
   196    196     if( pParse->nErr || pParse->pNewTrigger==0 ) goto triggerfinish_cleanup;
   197         -  nt = pParse->pNewTrigger;
          197  +  pTrig = pParse->pNewTrigger;
   198    198     pParse->pNewTrigger = 0;
   199         -  nt->step_list = pStepList;
          199  +  pTrig->step_list = pStepList;
   200    200     while( pStepList ){
   201         -    pStepList->pTrig = nt;
          201  +    pStepList->pTrig = pTrig;
   202    202       pStepList = pStepList->pNext;
   203    203     }
   204         -  if( sqlite3FixInit(&sFix, pParse, nt->iDb, "trigger", &nt->nameToken) 
   205         -          && sqlite3FixTriggerStep(&sFix, nt->step_list) ){
          204  +  if( sqlite3FixInit(&sFix, pParse, pTrig->iDb, "trigger", &pTrig->nameToken) 
          205  +          && sqlite3FixTriggerStep(&sFix, pTrig->step_list) ){
   206    206       goto triggerfinish_cleanup;
   207    207     }
   208    208   
   209    209     /* if we are not initializing, and this trigger is not on a TEMP table, 
   210    210     ** build the sqlite_master entry
   211    211     */
   212    212     if( !db->init.busy ){
................................................................................
   224    224       };
   225    225       int addr;
   226    226       Vdbe *v;
   227    227   
   228    228       /* Make an entry in the sqlite_master table */
   229    229       v = sqlite3GetVdbe(pParse);
   230    230       if( v==0 ) goto triggerfinish_cleanup;
   231         -    sqlite3BeginWriteOperation(pParse, 0, nt->iDb);
   232         -    sqlite3OpenMasterTable(v, nt->iDb);
          231  +    sqlite3BeginWriteOperation(pParse, 0, pTrig->iDb);
          232  +    sqlite3OpenMasterTable(v, pTrig->iDb);
   233    233       addr = sqlite3VdbeAddOpList(v, ArraySize(insertTrig), insertTrig);
   234         -    sqlite3VdbeChangeP3(v, addr+2, nt->name, 0); 
   235         -    sqlite3VdbeChangeP3(v, addr+3, nt->table, 0); 
          234  +    sqlite3VdbeChangeP3(v, addr+2, pTrig->name, 0); 
          235  +    sqlite3VdbeChangeP3(v, addr+3, pTrig->table, 0); 
   236    236       sqlite3VdbeChangeP3(v, addr+6, pAll->z, pAll->n);
   237         -    if( nt->iDb!=0 ){
   238         -      sqlite3ChangeCookie(db, v, nt->iDb);
   239         -    }
          237  +    sqlite3ChangeCookie(db, v, pTrig->iDb);
   240    238       sqlite3VdbeAddOp(v, OP_Close, 0, 0);
   241         -    sqlite3VdbeOp3(v, OP_ParseSchema, nt->iDb, 0, 
   242         -       sqlite3MPrintf("type='trigger' AND name='%q'", nt->name), P3_DYNAMIC);
          239  +    sqlite3VdbeOp3(v, OP_ParseSchema, pTrig->iDb, 0, 
          240  +       sqlite3MPrintf("type='trigger' AND name='%q'", pTrig->name), P3_DYNAMIC);
   243    241     }
   244    242   
   245    243     if( db->init.busy ){
   246    244       Table *pTab;
   247         -    sqlite3HashInsert(&db->aDb[nt->iDb].trigHash, 
   248         -                     nt->name, strlen(nt->name)+1, nt);
   249         -    pTab = sqlite3LocateTable(pParse, nt->table, db->aDb[nt->iTabDb].zName);
          245  +    sqlite3HashInsert(&db->aDb[pTrig->iDb].trigHash, 
          246  +                     pTrig->name, strlen(pTrig->name)+1, pTrig);
          247  +    pTab = sqlite3LocateTable(pParse,pTrig->table,db->aDb[pTrig->iTabDb].zName);
   250    248       assert( pTab!=0 );
   251         -    nt->pNext = pTab->pTrigger;
   252         -    pTab->pTrigger = nt;
   253         -    nt = 0;
          249  +    pTrig->pNext = pTab->pTrigger;
          250  +    pTab->pTrigger = pTrig;
          251  +    pTrig = 0;
   254    252     }
   255    253   
   256    254   triggerfinish_cleanup:
   257         -  sqlite3DeleteTrigger(nt);
   258         -  sqlite3DeleteTrigger(pParse->pNewTrigger);
   259         -  pParse->pNewTrigger = 0;
          255  +  sqlite3DeleteTrigger(pTrig);
          256  +  assert( !pParse->pNewTrigger );
   260    257     sqlite3DeleteTriggerStep(pStepList);
   261    258   }
   262    259   
   263    260   /*
   264    261   ** Make a copy of all components of the given trigger step.  This has
   265    262   ** the effect of copying all Expr.token.z values into memory obtained
   266    263   ** from sqliteMalloc().  As initially created, the Expr.token.z values

Changes to test/schema.test.

     1         -# 2004 Jun 27
            1  +# 2004 Jan 24
     2      2   #
     3      3   # The author disclaims copyright to this source code.  In place of
     4      4   # a legal notice, here is a blessing:
     5      5   #
     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
................................................................................
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.
    12     12   #
    13     13   # This file tests the various conditions under which an SQLITE_SCHEMA
    14     14   # error should be returned.
    15     15   #
    16         -# $Id: schema.test,v 1.1 2005/01/24 10:26:00 danielk1977 Exp $
           16  +# $Id: schema.test,v 1.2 2005/01/24 13:03:32 danielk1977 Exp $
           17  +
           18  +#---------------------------------------------------------------------
           19  +# When any of the following types of SQL statements or actions are 
           20  +# executed, all pre-compiled statements are invalidated. An attempt
           21  +# to execute an invalidated statement always returns SQLITE_SCHEMA.
           22  +#
           23  +# CREATE/DROP TABLE...................................schema-1.*
           24  +# CREATE/DROP VIEW....................................schema-2.*
           25  +# CREATE/DROP TRIGGER.................................schema-3.*
           26  +# CREATE/DROP INDEX...................................schema-4.*
           27  +# DETACH..............................................schema-5.*
           28  +# Deleting a user-function............................schema-6.*
           29  +# Deleting a collation sequence.......................schema-7.*
           30  +# Setting or changing the authorization function......schema-8.*
           31  +#
           32  +# Note: Test cases schema-6.* are missing right now.
           33  +#
    17     34   
    18     35   set testdir [file dirname $argv0]
    19     36   source $testdir/tester.tcl
    20     37   
    21         -# schema-1.1: Test that if a table is dropped by one database connection, 
    22         -#             other database connections are aware of the schema change.
    23         -# schema-1.2: Test that if a view is dropped by one database connection,
    24         -#             other database connections are aware of the schema change.
    25         -#
    26     38   do_test schema-1.1 {
           39  +  set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
    27     40     execsql {
    28     41       CREATE TABLE abc(a, b, c);
    29     42     }
    30         -  sqlite3 db2 test.db
           43  +  sqlite3_step $::STMT
           44  +} {SQLITE_ERROR}
           45  +do_test schema-1.2 {
           46  +  sqlite3_finalize $::STMT
           47  +} {SQLITE_SCHEMA}
           48  +do_test schema-1.3 {
           49  +  set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
    31     50     execsql {
    32     51       DROP TABLE abc;
    33         -  } db2
    34         -  catchsql {
    35         -    SELECT * FROM abc;
    36         -  }
    37         -} {1 {no such table: abc}}
    38         -do_test schema-1.2 {
    39         -  execsql {
    40         -    CREATE TABLE abc(a, b, c);
    41         -    CREATE VIEW abcview AS SELECT * FROM abc;
    42         -  }
    43         -  sqlite3 db2 test.db
    44         -  execsql {
    45         -    DROP VIEW abcview;
    46         -  } db2
    47         -  db2 close
    48         -  catchsql {
    49         -    SELECT * FROM abcview;
    50         -  }
    51         -} {1 {no such table: abcview}}
           52  +  }
           53  +  sqlite3_step $::STMT
           54  +} {SQLITE_ERROR}
           55  +do_test schema-1.4 {
           56  +  sqlite3_finalize $::STMT
           57  +} {SQLITE_SCHEMA}
           58  +
           59  +ifcapable view {
           60  +  do_test schema-2.1 {
           61  +    set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
           62  +    execsql {
           63  +      CREATE VIEW v1 AS SELECT * FROM sqlite_master;
           64  +    }
           65  +    sqlite3_step $::STMT
           66  +  } {SQLITE_ERROR}
           67  +  do_test schema-2.2 {
           68  +    sqlite3_finalize $::STMT
           69  +  } {SQLITE_SCHEMA}
           70  +  do_test schema-2.3 {
           71  +    set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
           72  +    execsql {
           73  +      DROP VIEW v1;
           74  +    }
           75  +    sqlite3_step $::STMT
           76  +  } {SQLITE_ERROR}
           77  +  do_test schema-2.4 {
           78  +    sqlite3_finalize $::STMT
           79  +  } {SQLITE_SCHEMA}
           80  +}
           81  +
           82  +ifcapable trigger {
           83  +  do_test schema-3.1 {
           84  +    execsql {
           85  +      CREATE TABLE abc(a, b, c);
           86  +    }
           87  +    set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
           88  +    execsql {
           89  +      CREATE TRIGGER abc_trig AFTER INSERT ON abc BEGIN
           90  +        SELECT 1, 2, 3;
           91  +      END;
           92  +    }
           93  +    sqlite3_step $::STMT
           94  +  } {SQLITE_ERROR}
           95  +  do_test schema-3.2 {
           96  +    sqlite3_finalize $::STMT
           97  +  } {SQLITE_SCHEMA}
           98  +  do_test schema-3.3 {
           99  +    set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
          100  +    execsql {
          101  +      DROP TRIGGER abc_trig;
          102  +    }
          103  +    sqlite3_step $::STMT
          104  +  } {SQLITE_ERROR}
          105  +  do_test schema-3.4 {
          106  +    sqlite3_finalize $::STMT
          107  +  } {SQLITE_SCHEMA}
          108  +}
    52    109   
    53         -# Tests 2.1 to 2.4 check that prepared statements are invalidated when
    54         -# a collation sequence is deleted (but not when one is added).
    55         -#
    56         -do_test schema-2.1 {
    57         -  set sql {SELECT * FROM abc;}
    58         -  set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
    59         -  add_test_collate $::DB 1 1 1
          110  +do_test schema-4.1 {
          111  +  set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
          112  +  execsql {
          113  +    CREATE INDEX abc_index ON abc(a);
          114  +  }
    60    115     sqlite3_step $::STMT
    61         -} {SQLITE_DONE}
    62         -do_test schema-2.2 {
    63         -  sqlite3_reset $::STMT
    64         -} {SQLITE_OK}
    65         -do_test schema-2.3 {
    66         -  add_test_collate $::DB 0 0 0 
          116  +} {SQLITE_ERROR}
          117  +do_test schema-4.2 {
          118  +  sqlite3_finalize $::STMT
          119  +} {SQLITE_SCHEMA}
          120  +do_test schema-4.3 {
          121  +  set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
          122  +  execsql {
          123  +    DROP INDEX abc_index;
          124  +  }
    67    125     sqlite3_step $::STMT
    68    126   } {SQLITE_ERROR}
    69         -do_test schema-2.4 {
          127  +do_test schema-4.4 {
    70    128     sqlite3_finalize $::STMT
    71    129   } {SQLITE_SCHEMA}
    72    130   
    73         -# Tests 3.1 to 3.4 check that prepared statements are invalidated when
          131  +#---------------------------------------------------------------------
          132  +# Tests 5.1 to 5.4 check that prepared statements are invalidated when
    74    133   # a database is DETACHed (but not when one is ATTACHed).
    75    134   #
    76         -do_test schema-3.1 {
          135  +do_test schema-5.1 {
    77    136     set sql {SELECT * FROM abc;}
    78    137     set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
    79    138     execsql {
    80    139       ATTACH 'test2.db' AS aux;
    81    140     }
    82    141     sqlite3_step $::STMT
    83    142   } {SQLITE_DONE}
    84         -do_test schema-3.2 {
          143  +do_test schema-5.2 {
    85    144     sqlite3_reset $::STMT
    86    145   } {SQLITE_OK}
    87         -do_test schema-3.3 {
          146  +do_test schema-5.3 {
    88    147     execsql {
    89    148       DETACH aux;
    90    149     }
    91    150     sqlite3_step $::STMT
    92    151   } {SQLITE_ERROR}
    93         -do_test schema-3.4 {
          152  +do_test schema-5.4 {
          153  +  sqlite3_finalize $::STMT
          154  +} {SQLITE_SCHEMA}
          155  +
          156  +#---------------------------------------------------------------------
          157  +# Tests 7.* check that prepared statements are invalidated when
          158  +# a collation sequence is deleted (but not when one is added).
          159  +#
          160  +do_test schema-7.1 {
          161  +  set sql {SELECT * FROM abc;}
          162  +  set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
          163  +  add_test_collate $::DB 1 1 1
          164  +  sqlite3_step $::STMT
          165  +} {SQLITE_DONE}
          166  +do_test schema-7.2 {
          167  +  sqlite3_reset $::STMT
          168  +} {SQLITE_OK}
          169  +do_test schema-7.3 {
          170  +  add_test_collate $::DB 0 0 0 
          171  +  sqlite3_step $::STMT
          172  +} {SQLITE_ERROR}
          173  +do_test schema-7.4 {
    94    174     sqlite3_finalize $::STMT
    95    175   } {SQLITE_SCHEMA}
    96    176   
          177  +#---------------------------------------------------------------------
          178  +# Tests 8.1 and 8.2 check that prepared statements are invalidated when
          179  +# the authorization function is set.
          180  +#
          181  +ifcapable auth {
          182  +  do_test schema-8.1 {
          183  +    set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
          184  +    db auth {}
          185  +    sqlite3_step $::STMT
          186  +  } {SQLITE_ERROR}
          187  +  do_test schema-8.3 {
          188  +    sqlite3_finalize $::STMT
          189  +  } {SQLITE_SCHEMA}
          190  +}
          191  +
          192  +#---------------------------------------------------------------------
          193  +# schema-9.1: Test that if a table is dropped by one database connection, 
          194  +#             other database connections are aware of the schema change.
          195  +# schema-9.2: Test that if a view is dropped by one database connection,
          196  +#             other database connections are aware of the schema change.
          197  +#
          198  +do_test schema-9.1 {
          199  +  sqlite3 db2 test.db
          200  +  execsql {
          201  +    DROP TABLE abc;
          202  +  } db2
          203  +  db2 close
          204  +  catchsql {
          205  +    SELECT * FROM abc;
          206  +  }
          207  +} {1 {no such table: abc}}
          208  +execsql {
          209  +  CREATE TABLE abc(a, b, c);
          210  +}
          211  +ifcapable view {
          212  +  do_test schema-9.2 {
          213  +    execsql {
          214  +      CREATE VIEW abcview AS SELECT * FROM abc;
          215  +    }
          216  +    sqlite3 db2 test.db
          217  +    execsql {
          218  +      DROP VIEW abcview;
          219  +    } db2
          220  +    db2 close
          221  +    catchsql {
          222  +      SELECT * FROM abcview;
          223  +    }
          224  +  } {1 {no such table: abcview}}
          225  +}
    97    226   
          227  +#---------------------------------------------------------------------
    98    228   # Test that if a CREATE TABLE statement fails because there are other
    99    229   # btree cursors open on the same database file it does not corrupt
   100    230   # the sqlite_master table.
   101    231   #
   102         -do_test schema-4.1 {
          232  +do_test schema-10.1 {
   103    233     execsql {
   104    234       INSERT INTO abc VALUES(1, 2, 3);
   105    235     }
   106    236     set sql {SELECT * FROM abc}
   107    237     set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
   108    238     sqlite3_step $::STMT
   109    239   } {SQLITE_ROW}
   110         -do_test schema-4.2 {
          240  +do_test schema-10.2 {
   111    241     catchsql {
   112    242       CREATE TABLE t2(a, b, c);
   113    243     }
   114    244   } {1 {database table is locked}}
   115         -do_test schema-4.3 {
          245  +do_test schema-10.3 {
   116    246     sqlite3_finalize $::STMT
   117    247   } {SQLITE_OK}
   118         -do_test schema-4.4 {
          248  +do_test schema-10.4 {
   119    249     sqlite3 db2 test.db
   120    250     execsql {
   121    251       SELECT * FROM abc
   122    252     } db2
   123    253   } {1 2 3}
   124         -do_test schema-4.5 {
          254  +do_test schema-10.5 {
   125    255     db2 close
   126    256   } {}
   127    257   
   128    258   finish_test
   129         -
   130         -
   131         -
   132         -
   133    259