SQLite4  Check-in [9ce1a04efd]

/*
** Return non-zero if the table pTab in database iDb or any of its indices
** have been opened at any point in the VDBE program beginning at location
** iStartAddr throught the end of the program.  This is used to see if 
** a statement of the form  "INSERT INTO <iDb, pTab> SELECT ..." can 
** run without using temporary table for the results of the SELECT. 
**
** Also return true if the statement accesses the sqlite_kvstore table.
*/
static int readsTable(Parse *p, int iStartAddr, int iDb, Table *pTab){
  Vdbe *v = sqlite4GetVdbe(p);
  int i;
  int iEnd = sqlite4VdbeCurrentAddr(v);
#ifndef SQLITE4_OMIT_VIRTUALTABLE
  VTable *pVTab = IsVirtual(pTab) ? sqlite4GetVTable(p->db, pTab) : 0;
#endif

  for(i=iStartAddr; i<iEnd; i++){
    VdbeOp *pOp = sqlite4VdbeGetOp(v, i);
    assert( pOp!=0 );
    if( pOp->opcode==OP_OpenRead && pOp->p3==iDb ){
      Index *pIndex;
      int tnum = pOp->p2;
      if( tnum==KVSTORE_ROOT ) return 1;
      for(pIndex=pTab->pIndex; pIndex; pIndex=pIndex->pNext){
        if( tnum==pIndex->tnum ){
          return 1;
        }
      }
    }
#ifndef SQLITE4_OMIT_VIRTUALTABLE

    ** FALSE if each* row of the SELECT can be written directly into
    ** the destination table (template 3).
    **
    ** A temp table must be used if the table being updated is also one
    ** of the tables being read by the SELECT statement.  Also use a 
    ** temp table in the case of row triggers.
    */
    if( pTrigger 
     || IsKvstore(pTab) 
     || readsTable(pParse, addrSelect, iDb, pTab) 
    ){
      useTempTable = 1;
    }

    if( useTempTable ){
      /* Invoke the coroutine to extract information from the SELECT
      ** and add it to a transient table srcTab.  The code generated
      ** here is from the 4th template:

} {x'616263' x'313131'}

do_execsql_test 1.12 {
  DELETE FROM sqlite_kvstore;
  SELECT quote(key), quote(value) FROM sqlite_kvstore;
} {}

#-------------------------------------------------------------------------
# INSERT INTO SELECT statements with sqlite_kvstore.
# 
reset_db
do_execsql_test 2.1 {
  CREATE TABLE t1(x PRIMARY KEY);
  INSERT INTO t1 VALUES(1);
  INSERT INTO t1 VALUES(2);
  INSERT INTO t1 VALUES(3);
  INSERT INTO t1 VALUES(4);
}

do_execsql_test 2.2 { SELECT count(*) FROM sqlite_kvstore } 5
do_execsql_test 2.3 {
  PRAGMA writable_schema = 1;
  INSERT INTO sqlite_kvstore SELECT x,x FROM t1;
  SELECT count(*) FROM sqlite_kvstore;
} {9}

do_execsql_test 2.4 {
  INSERT INTO t1 SELECT key FROM sqlite_kvstore;
  SELECT count(*) FROM sqlite_kvstore;
} {18}

#-------------------------------------------------------------------------
# UPDATE and INSERT with various on conflict clauses.
# 
reset_db
do_execsql_test 3.1 {
  PRAGMA writable_schema = 1;
  INSERT INTO sqlite_kvstore VALUES('one', 'i');
  INSERT INTO sqlite_kvstore VALUES('two', 'ii');
  INSERT INTO sqlite_kvstore VALUES('three', 'iii');
  INSERT INTO sqlite_kvstore VALUES('four', 'iv');
  INSERT INTO sqlite_kvstore VALUES('five', 'v');
}

do_execsql_test 3.2 {
  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
} {five v four iv one i three iii two ii}

do_execsql_test 3.3 {
  UPDATE OR REPLACE sqlite_kvstore SET key = 'three' WHERE value = x'76';
  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
} {four iv one i three v two ii}

do_execsql_test 3.4 {
  UPDATE OR IGNORE sqlite_kvstore SET key = 'five';
  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
} {five iv one i three v two ii}

do_execsql_test 3.5 {
  INSERT OR IGNORE INTO sqlite_kvstore VALUES('one', 'c');
  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
} {five iv one i three v two ii}

do_execsql_test 3.6 {
  INSERT OR REPLACE INTO sqlite_kvstore VALUES('one', 'c');
  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
} {five iv one c three v two ii}

do_execsql_test 3.7 {
  CREATE TEMP TABLE t1(x, y);
  INSERT INTO t1 VALUES('six', 'vi');
  INSERT INTO t1 VALUES('seven', 'vii');
  INSERT INTO t1 VALUES('five', 'hello world');
}

do_catchsql_test 3.8 {
  INSERT INTO sqlite_kvstore SELECT * FROM t1;
} {1 {PRIMARY KEY must be unique}}

do_execsql_test 3.9 {
  INSERT OR IGNORE INTO sqlite_kvstore SELECT * FROM t1;
  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
} {five iv one c seven vii six vi three v two ii}

do_execsql_test 3.10 {
  DELETE FROM sqlite_kvstore WHERE CAST(key AS text) = 'six';
  DELETE FROM sqlite_kvstore WHERE CAST(key AS text) = 'seven';
  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
} {five iv one c three v two ii}

do_execsql_test 3.11 {
  INSERT OR REPLACE INTO sqlite_kvstore SELECT * FROM t1;
  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
} {five {hello world} one c seven vii six vi three v two ii}

#-------------------------------------------------------------------------
# Check that indexes and triggers may not be added to sqlite_kvstore.
# Regardless of the writable_schema setting.
# 
foreach {tn ws} {1 off 2 on} {
  reset_db
  execsql " PRAGMA writable_schema = $ws "

  do_catchsql_test 4.$tn.1 {
    CREATE INDEX i1 ON sqlite_kvstore(value)
  } {1 {table sqlite_kvstore may not be indexed}}

  do_catchsql_test 4.$tn.2 {
    CREATE TRIGGER tr1 AFTER INSERT ON sqlite_kvstore BEGIN;
      SELECT 1;
    END;
  } {1 {cannot create trigger on system table}}
}

finish_test