limbo/testing/insert.test

#!/usr/bin/env tclsh
set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_execsql_test_on_specific_db {:memory:} basic-insert {
    create table temp (t1 integer, primary key (t1));
    insert into temp values (1);
    select * from temp;
} {1}

do_execsql_test_on_specific_db {:memory:} must-be-int-insert {
    create table temp (t1 integer, primary key (t1));
    insert into temp values (1),(2.0),('3'),('4.0');
    select * from temp;
} {1
2
3
4}

do_execsql_test_on_specific_db {:memory:} strict-basic-creation {
    CREATE TABLE test1(id INTEGER, name TEXT, price REAL) STRICT;
    INSERT INTO test1 VALUES(1, 'item1', 10.5);
    SELECT * FROM test1;
} {1|item1|10.5}

do_execsql_test_in_memory_any_error  strict-require-datatype {
    CREATE TABLE test2(id INTEGER, name) STRICT;
}

do_execsql_test_in_memory_any_error strict-valid-datatypes {
    CREATE TABLE test2(id INTEGER, value DATETIME) STRICT;
}

do_execsql_test_in_memory_any_error strict-type-enforcement {
    CREATE TABLE test3(id INTEGER, name TEXT, price REAL) STRICT;
    INSERT INTO test3 VALUES(1, 'item1', 'not-a-number');
}

do_execsql_test_on_specific_db {:memory:} strict-type-coercion {
    CREATE TABLE test4(id INTEGER, name TEXT, price REAL) STRICT;
    INSERT INTO test4 VALUES(1, 'item1', '10.5');
    SELECT typeof(price), price FROM test4;
} {real|10.5}

do_execsql_test_on_specific_db {:memory:} strict-any-flexibility {
    CREATE TABLE test5(id INTEGER, data ANY) STRICT;
    INSERT INTO test5 VALUES(1, 100);
    INSERT INTO test5 VALUES(2, 'text');
    INSERT INTO test5 VALUES(3, 3.14);
    SELECT id, typeof(data) FROM test5 ORDER BY id;
} {1|integer
2|text
3|real}

do_execsql_test_on_specific_db {:memory:} strict-any-preservation {
    CREATE TABLE test6(id INTEGER, code ANY) STRICT;
    INSERT INTO test6 VALUES(1, '000123');
    SELECT typeof(code), code FROM test6;
} {text|000123}

do_execsql_test_in_memory_any_error strict-int-vs-integer-pk {
    CREATE TABLE test8(id INT PRIMARY KEY, name TEXT) STRICT
    INSERT INTO test8 VALUES(NULL, 'test');
}

do_execsql_test_on_specific_db {:memory:} strict-integer-pk-behavior {
    CREATE TABLE test9(id INTEGER PRIMARY KEY, name TEXT) STRICT;
    INSERT INTO test9 VALUES(NULL, 'test');
    SELECT id, name FROM test9;
} {1|test}


do_execsql_test_on_specific_db {:memory:} strict-mixed-inserts {
    CREATE TABLE test11(
        id INTEGER PRIMARY KEY,
        name TEXT,
        price REAL,
        quantity INT,
        tags ANY
    ) STRICT;

    INSERT INTO test11 VALUES(1, 'item1', 10.5, 5, 'tag1');
    INSERT INTO test11 VALUES(2, 'item2', 20.75, 10, 42);

    SELECT id, name, price, quantity, typeof(tags) FROM test11 ORDER BY id;
} {1|item1|10.5|5|text
2|item2|20.75|10|integer}

do_execsql_test_on_specific_db {:memory:} strict-update-basic {
    CREATE TABLE test1(id INTEGER, name TEXT, price REAL) STRICT;
    INSERT INTO test1 VALUES(1, 'item1', 10.5);
    UPDATE test1 SET price = 15.75 WHERE id = 1;
    SELECT * FROM test1;
} {1|item1|15.75}

do_execsql_test_in_memory_any_error  strict-update-type-enforcement {
    CREATE TABLE test2(id INTEGER, name TEXT, price REAL) STRICT;
    INSERT INTO test2 VALUES(1, 'item1', 10.5);
    UPDATE test2 SET price = 'not-a-number' WHERE id = 1;
}

do_execsql_test_on_specific_db {:memory:} strict-update-type-coercion {
    CREATE TABLE test3(id INTEGER, name TEXT, price REAL) STRICT;
    INSERT INTO test3 VALUES(1, 'item1', 10.5);
    UPDATE test3 SET price = '15.75' WHERE id = 1;
    SELECT id, typeof(price), price FROM test3;
} {1|real|15.75}

do_execsql_test_on_specific_db {:memory:} strict-update-any-flexibility {
    CREATE TABLE test4(id INTEGER, data ANY) STRICT;
    INSERT INTO test4 VALUES(1, 100);
    UPDATE test4 SET data = 'text' WHERE id = 1;
    INSERT INTO test4 VALUES(2, 'original');
    UPDATE test4 SET data = 3.14 WHERE id = 2;
    SELECT id, typeof(data), data FROM test4 ORDER BY id;
} {1|text|text
2|real|3.14}

do_execsql_test_on_specific_db {:memory:} strict-update-any-preservation {
    CREATE TABLE test5(id INTEGER, code ANY) STRICT;
    INSERT INTO test5 VALUES(1, 'text');
    UPDATE test5 SET code = '000123' WHERE id = 1;
    SELECT typeof(code), code FROM test5;
} {text|000123}

do_execsql_test_in_memory_any_error strict-update-not-null-constraint {
    CREATE TABLE test7(id INTEGER, name TEXT NOT NULL) STRICT;
    INSERT INTO test7 VALUES(1, 'name');
    UPDATE test7 SET name = NULL WHERE id = 1;
}

# Uncomment following test case when unique constraint is added
do_execsql_test_in_memory_any_error strict-update-pk-constraint {
    CREATE TABLE test8(id INTEGER PRIMARY KEY, name TEXT) STRICT;
    INSERT INTO test8 VALUES(1, 'name1');
    INSERT INTO test8 VALUES(2, 'name2');
    UPDATE test8 SET id = 2 WHERE id = 1;
}

do_execsql_test_on_specific_db {:memory:} strict-update-multiple-columns {
    CREATE TABLE test9(id INTEGER, name TEXT, price REAL, quantity INT) STRICT;
    INSERT INTO test9 VALUES(1, 'item1', 10.5, 5);
    UPDATE test9 SET name = 'updated', price = 20.75, quantity = 10 WHERE id = 1;
    SELECT * FROM test9;
} {1|updated|20.75|10}

do_execsql_test_on_specific_db {:memory:} strict-update-where-clause {
    CREATE TABLE test10(id INTEGER, category TEXT, price REAL) STRICT;
    INSERT INTO test10 VALUES(1, 'A', 10);
    INSERT INTO test10 VALUES(2, 'A', 20);
    INSERT INTO test10 VALUES(3, 'B', 30);
    UPDATE test10 SET price = price * 2 WHERE category = 'A';
    SELECT id, price FROM test10 ORDER BY id;
} {1|20.0
2|40.0
3|30.0}

do_execsql_test_on_specific_db {:memory:} strict-update-expression {
    CREATE TABLE test11(id INTEGER, name TEXT, price REAL, discount REAL) STRICT;
    INSERT INTO test11 VALUES(1, 'item1', 100, 0.1);
    UPDATE test11 SET price = price - (price * discount);
    SELECT id, price FROM test11;
} {1|90.0}

do_execsql_test_on_specific_db {:memory:} named-insert-1 {
    CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, col_a TEXT, col_b TEXT, col_c TEXT, col_d TEXT);
    INSERT INTO test (col_b, col_d, col_a, col_c) VALUES ('1', '2', '3', '4');
    SELECT * FROM test;
} {1|3|1|4|2}

do_execsql_test_on_specific_db {:memory:} named-insert-2 {
    CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, col_a TEXT DEFAULT 'Empty', col_b TEXT, col_c TEXT, col_d TEXT);
    INSERT INTO test (col_b, col_d, col_c) VALUES ('1', '2', '4');
    SELECT * FROM test;
} {1|Empty|1|4|2}

do_execsql_test_on_specific_db {:memory:} multi-rows {
    CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, col);
    INSERT INTO test (col) VALUES (1),(1);
    SELECT * FROM test;
} {1|1
2|1}

if {[info exists ::env(SQLITE_EXEC)] && ($::env(SQLITE_EXEC) eq "scripts/limbo-sqlite3-index-experimental" || $::env(SQLITE_EXEC) eq "sqlite3")} {
    do_execsql_test_on_specific_db {:memory:} unique_insert_no_pkey {
        CREATE TABLE t2 (x INTEGER, y INTEGER UNIQUE);
        INSERT INTO t2 (y) VALUES (1);
        INSERT INTO t2 (y) VALUES (6);
        SELECT * FROM t2;
    } {|1
    |6}

    do_execsql_test_on_specific_db {:memory:} unique_insert_with_pkey {
        CREATE TABLE t2 (x INTEGER PRIMARY KEY, y INTEGER UNIQUE);
        INSERT INTO t2 (y) VALUES (1);
        INSERT INTO t2 (y) VALUES (6);
        SELECT * FROM t2;
    } {1|1
    2|6}
}

do_execsql_test_on_specific_db {:memory:} not_null_insert {
    CREATE TABLE t2 (y INTEGER NOT NULL);
    INSERT INTO t2 (y) VALUES (1);
    INSERT INTO t2 (y) VALUES (6);
    SELECT * FROM t2;
} {1
6}

do_execsql_test_in_memory_any_error not_null_primary_key {
    CREATE TABLE t2 (y INT PRIMARY KEY NOT NULL);
    INSERT INTO t2 (y) VALUES (1);
    INSERT INTO t2 (y) VALUES (NULL);
}

do_execsql_test_in_memory_any_error not_null_insert_select {
    CREATE TABLE t2 (y INT PRIMARY KEY NOT NULL);
    CREATE TABLE d (y INT);
    INSERT INTO d (y) VALUES (1);
    INSERT INTO d (y) VALUES (NULL);
    INSERT INTO t2 (y) SELECT y FROM d;
}

do_execsql_test_in_memory_any_error not_null_insert_multiple_values {
    CREATE TABLE t2 (y INT PRIMARY KEY NOT NULL);
    INSERT INTO t2 (y) VALUES (1), (NULL);
}

do_execsql_test_on_specific_db {:memory:} insert_from_select_1 {
    CREATE TABLE t(a);
    CREATE TABLE t2(b, c);
    INSERT INTO t2 values (1, 2), (10, 20);
    INSERT INTO t SELECT b FROM t2;
    SELECT * FROM t;
    INSERT INTO t SELECT c FROM t2;
    SELECT * FROM t;
} {1
10
1
10
2
20}

do_execsql_test_on_specific_db {:memory:} insert_from_select_where {
    CREATE TABLE t(a);
    CREATE TABLE t2(b, c);
    INSERT INTO t2 values (1, 2), (10, 20);
    INSERT INTO t SELECT b FROM t2 WHERE b < 10;
    SELECT * FROM t;
    INSERT INTO t SELECT c FROM t2 WHERE c > 2;
    SELECT * FROM t;
} {1
1
20}

do_execsql_test_on_specific_db {:memory:} insert_from_select_union_all {
    CREATE TABLE t1(x INTEGER);
    CREATE TABLE t2(x INTEGER); 
    CREATE TABLE t3(x INTEGER);
    
    INSERT INTO t1 VALUES(1),(2),(3);
    INSERT INTO t2 VALUES(4),(5),(6);
    INSERT INTO t3 VALUES(7),(8),(9);

    INSERT INTO t1 SELECT x FROM t2 UNION ALL SELECT x FROM t3;
    SELECT * FROM t1;
} {1
2
3
4
5
6
7
8
9}

do_execsql_test_on_specific_db {:memory:} insert_from_select_union_all_where {
    CREATE TABLE t1(x INTEGER);
    CREATE TABLE t2(x INTEGER); 
    CREATE TABLE t3(x INTEGER);
    
    INSERT INTO t1 VALUES(1),(2),(3);
    INSERT INTO t2 VALUES(4),(5),(6);
    INSERT INTO t3 VALUES(7),(8),(9);

    INSERT INTO t1 SELECT x FROM t2 WHERE x != 4 UNION ALL SELECT x FROM t3 WHERE x == 8;
    SELECT * FROM t1;
} {1
2
3
5
6
8}

do_execsql_test_on_specific_db {:memory:} insert_from_select_same_table {
    CREATE TABLE t(a INTEGER PRIMARY KEY, b);
    
    INSERT INTO t(b) VALUES (1),(2),(3);
    SELECT * FROM t;
    INSERT INTO t(b) SELECT b FROM t;
    SELECT * FROM t;
} {1|1
2|2
3|3
1|1
2|2
3|3
4|1
5|2
6|3}

do_execsql_test_on_specific_db {:memory:} insert_from_select_same_table_2 {
    CREATE TABLE t(a INTEGER PRIMARY KEY, b, c);
    
    INSERT INTO t(b, c) VALUES (1, 100),(2, 200),(3, 300);
    SELECT * FROM t;
    INSERT INTO t(b, c) SELECT b,c FROM t;
    SELECT * FROM t;
} {1|1|100
2|2|200
3|3|300
1|1|100
2|2|200
3|3|300
4|1|100
5|2|200
6|3|300}

if {[info exists ::env(SQLITE_EXEC)] && ($::env(SQLITE_EXEC) eq "scripts/limbo-sqlite3-index-experimental" || $::env(SQLITE_EXEC) eq "sqlite3")} {
    do_execsql_test_on_specific_db {:memory:} insert_from_select_union {
        CREATE TABLE t(a, b); 
        CREATE TABLE t2(b, c);

        INSERT INTO t2 VALUES (1, 100), (2, 200);
        INSERT INTO t SELECT * FROM t UNION SELECT * FROM t2;
        SELECT * FROM t;
    } {1|100
    2|200}

    do_execsql_test_on_specific_db {:memory:} insert_from_select_intersect {
        CREATE TABLE t(a, b);
        CREATE TABLE t1(a, b);
        CREATE TABLE t2(a, b);

        INSERT INTO t1 VALUES (1, 100), (2, 200);
        INSERT INTO t2 VALUES (2, 200), (3, 300);
        INSERT INTO t SELECT * FROM t1 INTERSECT SELECT * FROM t2;
        SELECT * FROM t;
    } {2|200}

    do_execsql_test_on_specific_db {:memory:} insert_from_select_intersect-2 {
        CREATE TABLE t(a, b);
        CREATE TABLE t1(a, b);
        CREATE TABLE t2(a, b);
        CREATE TABLE t3(a, b);

        INSERT INTO t1 VALUES (1, 100), (2, 200);
        INSERT INTO t2 VALUES (2, 200), (3, 300);
        INSERT INTO t3 VALUES (2, 200), (4, 400);
        INSERT INTO t SELECT * FROM t1 INTERSECT SELECT * FROM t2 INTERSECT SELECT * FROM t3;
        SELECT * FROM t;
    } {2|200}
}

do_execsql_test_on_specific_db {:memory:} negative-primary-integer-key {
    CREATE TABLE t(a INTEGER PRIMARY KEY);
    insert into t values (-2),(13);
    select * from t order by a asc;
} {-2
13}