SQLite

#
#   e_insert-1.*: Test statements of the form "INSERT ... VALUES(...)".
#   
#   e_insert-2.*: Test statements of the form "INSERT ... SELECT ...".
#
#   e_insert-3.*: Test statements of the form "INSERT ... DEFAULT VALUES".
#
#   e_insert-4.*: Test statements regarding the conflict clause.
#
#   e_insert-5.*: Test that the qualified table name and "DEFAULT VALUES"
#                 syntaxes do not work in trigger bodies.
#

do_execsql_test e_insert-0.0 {
  CREATE TABLE a1(a, b);
  CREATE TABLE a2(a, b, c DEFAULT 'xyz');

  CREATE TABLE a3(x DEFAULT 1.0, y DEFAULT 'string', z);
  CREATE TABLE a4(c UNIQUE, d);
} {}

proc delete_all_data {} {
  db eval {SELECT tbl_name AS t FROM sqlite_master WHERE type = 'table'} {
    db eval "DELETE FROM '[string map {' ''} $t]'"
  }
}

    1a   "INSERT INTO a2(b, c) VALUES('b', 'c')"     {}
    1b   "SELECT * FROM a2"                          {{} b c}

    2a   "INSERT INTO a2(a, b) VALUES('a', 'b')"     {}
    2b   "SELECT * FROM a2"                          {{} b c  a b xyz}
}

# EVIDENCE-OF: R-52173-30215 A new entry is inserted into the table for
# each row of data returned by executing the SELECT statement.
#
delete_all_data
do_insert_tests e_insert-2.1 {
    0    "SELECT count(*) FROM a1"            {0}

    1a   "SELECT count(*) FROM (SELECT 1, 2)" {1}
    1b   "INSERT INTO a1 SELECT 1, 2"         {}
    1c   "SELECT count(*) FROM a1"            {1}

    2a   "SELECT count(*) FROM (SELECT b, a FROM a1)"           {1}
    2b   "INSERT INTO a1 SELECT b, a FROM a1"                   {}
    2c   "SELECT count(*) FROM a1"                              {2}

    3a   "SELECT count(*) FROM (SELECT b, a FROM a1)"           {2}
    3b   "INSERT INTO a1 SELECT b, a FROM a1"                   {}
    3c   "SELECT count(*) FROM a1"                              {4}

    4a   "SELECT count(*) FROM (SELECT b, a FROM a1)"           {4}
    4b   "INSERT INTO a1 SELECT b, a FROM a1"                   {}
    4c   "SELECT count(*) FROM a1"                              {8}

    4a   "SELECT count(*) FROM (SELECT min(b), min(a) FROM a1)" {1}
    4b   "INSERT INTO a1 SELECT min(b), min(a) FROM a1"         {}
    4c   "SELECT count(*) FROM a1"                              {9}
}


# EVIDENCE-OF: R-63614-47421 If a column-list is specified, the number
# of columns in the result of the SELECT must be the same as the number
# of items in the column-list.
#
do_insert_tests e_insert-2.2 -error {

    1    "INSERT INTO a1 SELECT a, b, c FROM a2"            {a1 2 3}
    2    "INSERT INTO a1 SELECT * FROM a2"                  {a1 2 3}
    3    "INSERT INTO a1 SELECT * FROM a2 CROSS JOIN a1"    {a1 2 5}
    4    "INSERT INTO a1 SELECT * FROM a2 NATURAL JOIN a1"  {a1 2 3}
    5    "INSERT INTO a1 SELECT a2.a FROM a2,a1"            {a1 2 1}
}

# EVIDENCE-OF: R-31074-37730 Any SELECT statement, including compound
# SELECTs and SELECT statements with ORDER BY and/or LIMIT clauses, may
# be used in an INSERT statement of this form.
#
delete_all_data
do_execsql_test e_insert-2.3.0 {
  INSERT INTO a1 VALUES('x', 'y');
} {}
do_insert_tests e_insert-2.3 {
  1  "INSERT INTO a1 SELECT a,b FROM a1 UNION SELECT b,a FROM a1 ORDER BY 1" {}
  2  "INSERT INTO a1(b, a) SELECT * FROM a1 LIMIT 1"                         {}
  3  "INSERT INTO a1 SELECT 'a'||a, 'b'||b FROM a1 LIMIT 2 OFFSET 1"         {}
  4  "INSERT INTO a1 SELECT * FROM a1 ORDER BY b, a"                         {}
  S  "SELECT * FROM a1" {
      x y 
      x y y x
      y x
      ax by ay bx 
      ay bx ax by y x y x x y x y
  }
}

# EVIDENCE-OF: R-25149-22012 The INSERT ... DEFAULT VALUES statement
# inserts a single new row into the named table.
#
delete_all_data
do_insert_tests e_insert-3.1 {
    1    "SELECT count(*) FROM a3"           {0}

    5.1    "INSERT INTO a1 DEFAULT VALUES"     {}
    5.2    "SELECT * FROM a1"                  {{} {}}

    6.1    "INSERT INTO a1 DEFAULT VALUES"     {}
    6.2    "SELECT * FROM a1"                  {{} {} {} {}}
}

# EVIDENCE-OF: R-46928-50290 The optional conflict-clause allows the
# specification of an alternative constraint conflict resolution
# algorithm to use during this one INSERT command.
#
# EVIDENCE-OF: R-23110-47146 the parser allows the use of the single
# keyword REPLACE as an alias for "INSERT OR REPLACE".
#
#    The two requirements above are tested by e_select-4.1.* and
#    e_select-4.2.*, respectively.
#
do_execsql_test e_insert-4.1.0 {
  INSERT INTO a4 VALUES(1, 'a');
  INSERT INTO a4 VALUES(2, 'a');
  INSERT INTO a4 VALUES(3, 'a');
} {}
foreach {tn sql error ac data } {
  1.1  "INSERT INTO a4 VALUES(2,'b')"  {column c is not unique}  1 {1 a 2 a 3 a}
  1.2  "INSERT OR REPLACE INTO a4 VALUES(2, 'b')"            {}  1 {1 a 3 a 2 b}
  1.3  "INSERT OR IGNORE INTO a4 VALUES(3, 'c')"             {}  1 {1 a 3 a 2 b}
  1.4  "BEGIN" {} 0 {1 a 3 a 2 b}
  1.5  "INSERT INTO a4 VALUES(1, 'd')" {column c is not unique}  0 {1 a 3 a 2 b}
  1.6  "INSERT OR ABORT INTO a4 VALUES(1, 'd')" 
        {column c is not unique}  0 {1 a 3 a 2 b}
  1.7  "INSERT OR ROLLBACK INTO a4 VALUES(1, 'd')" 
        {column c is not unique}  1 {1 a 3 a 2 b}
  1.8  "INSERT INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'"
        {column c is not unique}  1 {1 a 3 a 2 b}
  1.9  "INSERT OR FAIL INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'"
        {column c is not unique}  1 {1 a 3 a 2 b 4 e}

  2.1  "INSERT INTO a4 VALUES(2,'f')"  
        {column c is not unique}  1 {1 a 3 a 2 b 4 e}
  2.2  "REPLACE INTO a4 VALUES(2, 'f')" {}  1 {1 a 3 a 4 e 2 f}
} {
  do_catchsql_test e_insert-4.1.$tn.1 $sql [list [expr {$error!=""}] $error]
  do_execsql_test  e_insert-4.1.$tn.2 {SELECT * FROM a4} [list {*}$data]
  do_test          e_insert-4.1.$tn.3 {sqlite3_get_autocommit db} $ac
}

# EVIDENCE-OF: R-64196-02418 The optional "database-name." prefix on the
# table-name is support for top-level INSERT statements only.
#
# EVIDENCE-OF: R-05731-00924 The table name must be unqualified for
# INSERT statements that occur within CREATE TRIGGER statements.
#
set err {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}

do_catchsql_test e_insert-5.1.1 {
  CREATE TRIGGER AFTER UPDATE ON a1 BEGIN
    INSERT INTO main.a4 VALUES(new.a, new.b);
  END;
} $err
do_catchsql_test e_insert-5.1.2 {
  CREATE TEMP TABLE IF NOT EXISTS tmptable(a, b);
  CREATE TRIGGER AFTER DELETE ON a3 BEGIN
    INSERT INTO temp.tmptable VALUES(1, 2);
  END;
} $err

# EVIDENCE-OF: R-15888-36326 Similarly, the "DEFAULT VALUES" form of the
# INSERT statement is supported for top-level INSERT statements only and
# not for INSERT statements within triggers.
#
do_catchsql_test e_insert-5.2.1 {
  CREATE TRIGGER AFTER UPDATE ON a1 BEGIN
    INSERT INTO a4 DEFAULT VALUES;
  END;
} {1 {near "DEFAULT": syntax error}}


delete_all_data

finish_test