SQLite4
Check-in [9ce1a04efd]
Not logged in

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

Overview
Comment:Extra tests for sqlite_kvstore.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9ce1a04efdeb7fbf95975eaf509e37439cb9aab3
User & Date: dan 2013-07-31 17:43:18
Context
2013-09-16
18:05
Add the beginnings of a simpler embedded b-tree backend. Doesn't do anything yet. check-in: c4135bffd7 user: dan tags: trunk
2013-07-31
17:43
Extra tests for sqlite_kvstore. check-in: 9ce1a04efd user: dan tags: trunk
15:32
Enhance kvmem to honor the SQLITE4_KVOPEN_NO_TRANSACTIONS flag. This makes kvmem significantly faster when used to implement ORDER BY with LIMIT. check-in: 3cb223f975 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

   192    192   
   193    193   /*
   194    194   ** Return non-zero if the table pTab in database iDb or any of its indices
   195    195   ** have been opened at any point in the VDBE program beginning at location
   196    196   ** iStartAddr throught the end of the program.  This is used to see if 
   197    197   ** a statement of the form  "INSERT INTO <iDb, pTab> SELECT ..." can 
   198    198   ** run without using temporary table for the results of the SELECT. 
          199  +**
          200  +** Also return true if the statement accesses the sqlite_kvstore table.
   199    201   */
   200    202   static int readsTable(Parse *p, int iStartAddr, int iDb, Table *pTab){
   201    203     Vdbe *v = sqlite4GetVdbe(p);
   202    204     int i;
   203    205     int iEnd = sqlite4VdbeCurrentAddr(v);
   204    206   #ifndef SQLITE4_OMIT_VIRTUALTABLE
   205    207     VTable *pVTab = IsVirtual(pTab) ? sqlite4GetVTable(p->db, pTab) : 0;
................................................................................
   207    209   
   208    210     for(i=iStartAddr; i<iEnd; i++){
   209    211       VdbeOp *pOp = sqlite4VdbeGetOp(v, i);
   210    212       assert( pOp!=0 );
   211    213       if( pOp->opcode==OP_OpenRead && pOp->p3==iDb ){
   212    214         Index *pIndex;
   213    215         int tnum = pOp->p2;
          216  +      if( tnum==KVSTORE_ROOT ) return 1;
   214    217         for(pIndex=pTab->pIndex; pIndex; pIndex=pIndex->pNext){
   215    218           if( tnum==pIndex->tnum ){
   216    219             return 1;
   217    220           }
   218    221         }
   219    222       }
   220    223   #ifndef SQLITE4_OMIT_VIRTUALTABLE
................................................................................
   678    681       ** FALSE if each* row of the SELECT can be written directly into
   679    682       ** the destination table (template 3).
   680    683       **
   681    684       ** A temp table must be used if the table being updated is also one
   682    685       ** of the tables being read by the SELECT statement.  Also use a 
   683    686       ** temp table in the case of row triggers.
   684    687       */
   685         -    if( pTrigger || readsTable(pParse, addrSelect, iDb, pTab) ){
          688  +    if( pTrigger 
          689  +     || IsKvstore(pTab) 
          690  +     || readsTable(pParse, addrSelect, iDb, pTab) 
          691  +    ){
   686    692         useTempTable = 1;
   687    693       }
   688    694   
   689    695       if( useTempTable ){
   690    696         /* Invoke the coroutine to extract information from the SELECT
   691    697         ** and add it to a transient table srcTab.  The code generated
   692    698         ** here is from the 4th template:

Changes to test/kvstore2.test.

    70     70   } {x'616263' x'313131'}
    71     71   
    72     72   do_execsql_test 1.12 {
    73     73     DELETE FROM sqlite_kvstore;
    74     74     SELECT quote(key), quote(value) FROM sqlite_kvstore;
    75     75   } {}
    76     76   
           77  +#-------------------------------------------------------------------------
           78  +# INSERT INTO SELECT statements with sqlite_kvstore.
           79  +# 
           80  +reset_db
           81  +do_execsql_test 2.1 {
           82  +  CREATE TABLE t1(x PRIMARY KEY);
           83  +  INSERT INTO t1 VALUES(1);
           84  +  INSERT INTO t1 VALUES(2);
           85  +  INSERT INTO t1 VALUES(3);
           86  +  INSERT INTO t1 VALUES(4);
           87  +}
           88  +
           89  +do_execsql_test 2.2 { SELECT count(*) FROM sqlite_kvstore } 5
           90  +do_execsql_test 2.3 {
           91  +  PRAGMA writable_schema = 1;
           92  +  INSERT INTO sqlite_kvstore SELECT x,x FROM t1;
           93  +  SELECT count(*) FROM sqlite_kvstore;
           94  +} {9}
           95  +
           96  +do_execsql_test 2.4 {
           97  +  INSERT INTO t1 SELECT key FROM sqlite_kvstore;
           98  +  SELECT count(*) FROM sqlite_kvstore;
           99  +} {18}
          100  +
          101  +#-------------------------------------------------------------------------
          102  +# UPDATE and INSERT with various on conflict clauses.
          103  +# 
          104  +reset_db
          105  +do_execsql_test 3.1 {
          106  +  PRAGMA writable_schema = 1;
          107  +  INSERT INTO sqlite_kvstore VALUES('one', 'i');
          108  +  INSERT INTO sqlite_kvstore VALUES('two', 'ii');
          109  +  INSERT INTO sqlite_kvstore VALUES('three', 'iii');
          110  +  INSERT INTO sqlite_kvstore VALUES('four', 'iv');
          111  +  INSERT INTO sqlite_kvstore VALUES('five', 'v');
          112  +}
          113  +
          114  +do_execsql_test 3.2 {
          115  +  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
          116  +} {five v four iv one i three iii two ii}
          117  +
          118  +do_execsql_test 3.3 {
          119  +  UPDATE OR REPLACE sqlite_kvstore SET key = 'three' WHERE value = x'76';
          120  +  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
          121  +} {four iv one i three v two ii}
          122  +
          123  +do_execsql_test 3.4 {
          124  +  UPDATE OR IGNORE sqlite_kvstore SET key = 'five';
          125  +  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
          126  +} {five iv one i three v two ii}
          127  +
          128  +do_execsql_test 3.5 {
          129  +  INSERT OR IGNORE INTO sqlite_kvstore VALUES('one', 'c');
          130  +  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
          131  +} {five iv one i three v two ii}
          132  +
          133  +do_execsql_test 3.6 {
          134  +  INSERT OR REPLACE INTO sqlite_kvstore VALUES('one', 'c');
          135  +  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
          136  +} {five iv one c three v two ii}
          137  +
          138  +do_execsql_test 3.7 {
          139  +  CREATE TEMP TABLE t1(x, y);
          140  +  INSERT INTO t1 VALUES('six', 'vi');
          141  +  INSERT INTO t1 VALUES('seven', 'vii');
          142  +  INSERT INTO t1 VALUES('five', 'hello world');
          143  +}
          144  +
          145  +do_catchsql_test 3.8 {
          146  +  INSERT INTO sqlite_kvstore SELECT * FROM t1;
          147  +} {1 {PRIMARY KEY must be unique}}
          148  +
          149  +do_execsql_test 3.9 {
          150  +  INSERT OR IGNORE INTO sqlite_kvstore SELECT * FROM t1;
          151  +  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
          152  +} {five iv one c seven vii six vi three v two ii}
          153  +
          154  +do_execsql_test 3.10 {
          155  +  DELETE FROM sqlite_kvstore WHERE CAST(key AS text) = 'six';
          156  +  DELETE FROM sqlite_kvstore WHERE CAST(key AS text) = 'seven';
          157  +  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
          158  +} {five iv one c three v two ii}
          159  +
          160  +do_execsql_test 3.11 {
          161  +  INSERT OR REPLACE INTO sqlite_kvstore SELECT * FROM t1;
          162  +  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
          163  +} {five {hello world} one c seven vii six vi three v two ii}
          164  +
          165  +#-------------------------------------------------------------------------
          166  +# Check that indexes and triggers may not be added to sqlite_kvstore.
          167  +# Regardless of the writable_schema setting.
          168  +# 
          169  +foreach {tn ws} {1 off 2 on} {
          170  +  reset_db
          171  +  execsql " PRAGMA writable_schema = $ws "
          172  +
          173  +  do_catchsql_test 4.$tn.1 {
          174  +    CREATE INDEX i1 ON sqlite_kvstore(value)
          175  +  } {1 {table sqlite_kvstore may not be indexed}}
          176  +
          177  +  do_catchsql_test 4.$tn.2 {
          178  +    CREATE TRIGGER tr1 AFTER INSERT ON sqlite_kvstore BEGIN;
          179  +      SELECT 1;
          180  +    END;
          181  +  } {1 {cannot create trigger on system table}}
          182  +}
    77    183   
    78    184   finish_test
    79    185