SQLite

  db close
  sqlite3 db test.db
  set res [catch {db eval {
    INSERT INTO t1(b) VALUES('two');
  }} msg]
  lappend res $msg
} {1 {database disk image is malformed}}
ifcapable vtab {
  set err "database disk image is malformed"
} else {
  set err {malformed database schema (sqlite_sequence) - near "VIRTUAL": syntax error}
}
do_test autoinc-12.3 {
  db close
  forcedelete test.db
  sqlite3 db test.db
  db eval {
   CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT);
   INSERT INTO t1(b) VALUES('one');
   PRAGMA writable_schema=on;
   UPDATE sqlite_master SET
     sql='CREATE VIRTUAL TABLE sqlite_sequence USING sqlite_dbpage'
    WHERE name='sqlite_sequence';
  }
  db close
  sqlite3 db test.db
  set res [catch {db eval {
    INSERT INTO t1(b) VALUES('two');
  }} msg]
  lappend res $msg
} [list 1 $err]
do_test autoinc-12.4 {
  db close
  forcedelete test.db
  sqlite3 db test.db
  db eval {
    CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT);
    INSERT INTO t1(b) VALUES('one');

# either SQLITE_OMIT_TRIGGER or SQLITE_OMIT_FOREIGN_KEY was defined
# at build time).
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

proc eqp {sql {db db}} { 
  uplevel [subst -nocommands {
    set eqpres [list]
    $db eval "$sql" {
      lappend eqpres [set detail]
    }
    set eqpres
  }]
}

proc do_detail_test {tn sql res} {
  set normalres [list {*}$res]
  uplevel [subst -nocommands {
    do_test $tn {
      eqp { $sql }
    } {$normalres}
  }]
}

###########################################################################
### SECTION 2: Enabling Foreign Key Support
###########################################################################

#-------------------------------------------------------------------------
# EVIDENCE-OF: R-33710-56344 In order to use foreign key constraints in

      trackid     INTEGER, 
      trackname   TEXT, 
      trackartist INTEGER,
      FOREIGN KEY(trackartist) REFERENCES artist(artistid)
    );
  }
} {}
do_detail_test e_fkey-25.2 {
  PRAGMA foreign_keys = OFF;
  EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
  EXPLAIN QUERY PLAN SELECT rowid FROM track WHERE trackartist = ?;
} {
  {SCAN TABLE artist} 
  {SCAN TABLE track}
}
do_detail_test e_fkey-25.3 {
  PRAGMA foreign_keys = ON;
  EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
} {
  {SCAN TABLE artist} 
  {SCAN TABLE track}
}
do_test e_fkey-25.4 {
  execsql {
    INSERT INTO artist VALUES(5, 'artist 5');
    INSERT INTO artist VALUES(6, 'artist 6');
    INSERT INTO artist VALUES(7, 'artist 7');
    INSERT INTO track VALUES(1, 'track 1', 5);

    );
    CREATE INDEX trackindex ON track(trackartist);
  }
} {}
do_test e_fkey-27.2 {
  eqp { INSERT INTO artist VALUES(?, ?) }
} {}
do_detail_test e_fkey-27.3 {
  EXPLAIN QUERY PLAN UPDATE artist SET artistid = ?, artistname = ?
} {
  {SCAN TABLE artist} 
  {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?)} 
  {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?)}
}
do_detail_test e_fkey-27.4 {
  EXPLAIN QUERY PLAN DELETE FROM artist
} {
  {SCAN TABLE artist} 
  {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?)}
}


###########################################################################
### SECTION 4.1: Composite Foreign Key Constraints
###########################################################################

#-------------------------------------------------------------------------
# Check that parent and child keys must have the same number of columns.

db close
forcedelete test.db
forcedelete test.db-journal
sqlite3 db test.db

ifcapable explain {
  do_execsql_test misc7-14.0 {
    CREATE TABLE abc(a PRIMARY KEY, b, c);
  }
  do_eqp_test misc7-14.1 {
    SELECT * FROM abc AS t2 WHERE rowid = 1;
  } {
  QUERY PLAN
  `--SEARCH TABLE abc AS t2 USING INTEGER PRIMARY KEY (rowid=?)
}
  do_eqp_test misc7-14.2 {
    SELECT * FROM abc AS t2 WHERE a = 1;
} {
  QUERY PLAN
  `--SEARCH TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1 (a=?)
}
  do_eqp_test misc7-14.3 {
    SELECT * FROM abc AS t2 ORDER BY a;
  } {
  QUERY PLAN
  `--SCAN TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1
}
}

db close
forcedelete test.db
forcedelete test.db-journal
sqlite3 db test.db