0
0
mirror of https://github.com/tursodatabase/libsql.git synced 2024-12-16 11:28:45 +00:00
libsql/libsql-wal/tests/assets/fixtures/e_createtable.test
ad hoc ad79ef48a2
libsql wal tests (#1408)
* add tests

* add bench
2024-05-24 13:59:17 +00:00

319 lines
8.3 KiB
Plaintext

ATTACH 'test.db2' AS auxa;
ATTACH 'test.db3' AS auxb;
CREATE TABLE t1(x, y);
CREATE INDEX i1 ON t1(x);
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE TABLE auxa.tbl1(x, y);
CREATE INDEX auxa.idx1 ON tbl1(x);
CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;
CREATE TABLE t1(x, y);
CREATE INDEX i1 ON t1(x);
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE TABLE auxa.tbl1(x, y);
CREATE INDEX auxa.idx1 ON tbl1(x);
CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;
CREATE TABLE t1(x, y);
CREATE TABLE auxb.t2(x, y);
CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
SELECT 1;
END;
CREATE TRIGGER auxb.tr2 AFTER INSERT ON t2 BEGIN
SELECT 1;
END;
CREATE TABLE t1(a, b);
CREATE TABLE t2(a, b);
CREATE TABLE auxa.t3(a, b);
CREATE TABLE auxa.t4(a, b);
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
SELECT * FROM t4;
SELECT name FROM sqlite_master;
SELECT name FROM auxa.sqlite_master;
SELECT name FROM sqlite_master;
SELECT name FROM auxa.sqlite_master;
CREATE TABLE t1(a, b, c);
CREATE TABLE t2(d, e, f);
CREATE TABLE t3(g BIGINT, h VARCHAR(10));
CREATE TABLE t4(i BLOB, j ANYOLDATA);
CREATE TABLE t5(k FLOAT, l INTEGER);
CREATE TABLE t6(m DEFAULT 10, n DEFAULT 5, PRIMARY KEY(m, n));
CREATE TABLE t7(x INTEGER PRIMARY KEY);
CREATE TABLE t8(o COLLATE nocase DEFAULT 'abc');
CREATE TABLE t9(p NOT NULL, q DOUBLE CHECK (q!=0), r STRING UNIQUE);
INSERT INTO x1 DEFAULT VALUES;
INSERT INTO x2 DEFAULT VALUES;
INSERT INTO x3 DEFAULT VALUES;
INSERT INTO x4 DEFAULT VALUES;
SELECT * FROM x1
SELECT * FROM x2
SELECT * FROM x3
SELECT * FROM x4
CREATE TABLE t1(x, y);
INSERT INTO t1 VALUES('i', 'one');
INSERT INTO t1 VALUES('ii', 'two');
INSERT INTO t1 VALUES('iii', 'three');
CREATE TABLE t1(x VARCHAR(10), y INTEGER, z DOUBLE);
CREATE TABLE t2(a DATETIME, b STRING, c REAL);
CREATE TABLE t3(o, t);
INSERT INTO t1 VALUES(14, 'quite a lengthy string', X'555655');
SELECT * FROM t1;
INSERT INTO t2 VALUES('not a datetime', 13, '12:41:59');
SELECT * FROM t2;
DELETE FROM t1; DELETE FROM t2;
CREATE TABLE t4(
a DEFAULT NULL,
b DEFAULT 'string constant',
c DEFAULT X'424C4F42',
d DEFAULT 1,
e DEFAULT -1,
f DEFAULT 3.14,
g DEFAULT -3.14,
h DEFAULT ( substr('abcd', 0, 2) || 'cd' ),
i DEFAULT CURRENT_TIME,
j DEFAULT CURRENT_DATE,
k DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE t5(
a DEFAULT NULL,
b DEFAULT 'text value',
c DEFAULT X'424C4F42',
d DEFAULT -45678.6,
e DEFAULT 394507
);
INSERT INTO t5 DEFAULT VALUES;
SELECT quote(a), quote(b), quote(c), quote(d), quote(e) FROM t5;
CREATE TABLE t6(a DEFAULT ( nextint() ), b DEFAULT ( nextint() ));
INSERT INTO t6 DEFAULT VALUES;
SELECT quote(a), quote(b) FROM t6;
INSERT INTO t6(a) VALUES('X');
SELECT quote(a), quote(b) FROM t6;
INSERT INTO t6(a) SELECT a FROM t6;
SELECT quote(a), quote(b) FROM t6;
CREATE TABLE t7(
a DEFAULT CURRENT_TIME,
b DEFAULT CURRENT_DATE,
c DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO t7 DEFAULT VALUES;
SELECT quote(a), quote(b), quote(c) FROM t7;
CREATE TABLE t8(a COLLATE nocase, b COLLATE rtrim, c COLLATE binary, d);
INSERT INTO t8 VALUES('abc', 'abc', 'abc', 'abc');
INSERT INTO t8 VALUES('abc ', 'abc ', 'abc ', 'abc ');
INSERT INTO t8 VALUES('ABC ', 'ABC ', 'ABC ', 'ABC ');
INSERT INTO t8 VALUES('ABC', 'ABC', 'ABC', 'ABC');
CREATE TABLE t12(a, b, c);
INSERT INTO t12 VALUES(randomblob(30000),randomblob(30000),randomblob(30000));
CREATE TABLE t1(x PRIMARY KEY, y);
INSERT INTO t1 VALUES(0, 'zero');
INSERT INTO t1 VALUES(45.5, 'one');
INSERT INTO t1 VALUES('brambles', 'two');
INSERT INTO t1 VALUES(X'ABCDEF', 'three');
CREATE TABLE t2(x, y, PRIMARY KEY(x, y));
INSERT INTO t2 VALUES(0, 'zero');
INSERT INTO t2 VALUES(45.5, 'one');
INSERT INTO t2 VALUES('brambles', 'two');
INSERT INTO t2 VALUES(X'ABCDEF', 'three');
CREATE TABLE t3(s, u INTEGER PRIMARY KEY, v);
INSERT INTO t3 VALUES(1, NULL, 2);
INSERT INTO t3 VALUES('x', NULL, 'y');
SELECT u FROM t3;
INSERT INTO t1 VALUES(1, 2);
INSERT INTO t1 VALUES(4.3, 5.5);
INSERT INTO t1 VALUES('reveal', 'variableness');
INSERT INTO t1 VALUES(X'123456', X'654321');
INSERT INTO t4 VALUES('xyx', 1, 1);
INSERT INTO t4 VALUES('xyx', 2, 1);
INSERT INTO t4 VALUES('uvw', 1, 1);
CREATE TABLE t1(a, b PRIMARY KEY);
CREATE TABLE t2(a, b, c, UNIQUE(b, c));
CREATE TABLE x1(a TEXT, b INTEGER CHECK( b>0 ));
CREATE TABLE t1(a TEXT, b INTEGER, CHECK( b>0 ));
INSERT INTO x1 VALUES('x', 'xx');
INSERT INTO x1 VALUES('y', 'yy');
INSERT INTO t1 SELECT * FROM x1;
CREATE TABLE x2(a CHECK( a||b ), b);
CREATE TABLE t2(a, b, CHECK( a||b ));
INSERT INTO x2 VALUES(1, 'xx');
INSERT INTO x2 VALUES(1, 'yy');
INSERT INTO t2 SELECT * FROM x2;
INSERT INTO t1 VALUES('x', 'y');
INSERT INTO t1 VALUES('z', NULL);
INSERT INTO t2 VALUES('x', 'y');
INSERT INTO t2 VALUES('z', NULL);
INSERT INTO t3 VALUES('x', 'y', 'z');
INSERT INTO t3 VALUES(1, 2, 3);
CREATE TABLE t1_ab(a PRIMARY KEY ON CONFLICT ABORT, b);
CREATE TABLE t1_ro(a PRIMARY KEY ON CONFLICT ROLLBACK, b);
CREATE TABLE t1_ig(a PRIMARY KEY ON CONFLICT IGNORE, b);
CREATE TABLE t1_fa(a PRIMARY KEY ON CONFLICT FAIL, b);
CREATE TABLE t1_re(a PRIMARY KEY ON CONFLICT REPLACE, b);
CREATE TABLE t1_xx(a PRIMARY KEY, b);
INSERT INTO t1_ab VALUES(1, 'one');
INSERT INTO t1_ab VALUES(2, 'two');
INSERT INTO t1_ro SELECT * FROM t1_ab;
INSERT INTO t1_ig SELECT * FROM t1_ab;
INSERT INTO t1_fa SELECT * FROM t1_ab;
INSERT INTO t1_re SELECT * FROM t1_ab;
INSERT INTO t1_xx SELECT * FROM t1_ab;
CREATE TABLE t2_ab(a, b NOT NULL ON CONFLICT ABORT);
CREATE TABLE t2_ro(a, b NOT NULL ON CONFLICT ROLLBACK);
CREATE TABLE t2_ig(a, b NOT NULL ON CONFLICT IGNORE);
CREATE TABLE t2_fa(a, b NOT NULL ON CONFLICT FAIL);
CREATE TABLE t2_re(a, b NOT NULL ON CONFLICT REPLACE);
CREATE TABLE t2_xx(a, b NOT NULL);
INSERT INTO t2_ab VALUES(1, 'one');
INSERT INTO t2_ab VALUES(2, 'two');
INSERT INTO t2_ro SELECT * FROM t2_ab;
INSERT INTO t2_ig SELECT * FROM t2_ab;
INSERT INTO t2_fa SELECT * FROM t2_ab;
INSERT INTO t2_re SELECT * FROM t2_ab;
INSERT INTO t2_xx SELECT * FROM t2_ab;
CREATE TABLE t3_ab(a, b, UNIQUE(a, b) ON CONFLICT ABORT);
CREATE TABLE t3_ro(a, b, UNIQUE(a, b) ON CONFLICT ROLLBACK);
CREATE TABLE t3_ig(a, b, UNIQUE(a, b) ON CONFLICT IGNORE);
CREATE TABLE t3_fa(a, b, UNIQUE(a, b) ON CONFLICT FAIL);
CREATE TABLE t3_re(a, b, UNIQUE(a, b) ON CONFLICT REPLACE);
CREATE TABLE t3_xx(a, b, UNIQUE(a, b));
INSERT INTO t3_ab VALUES(1, 'one');
INSERT INTO t3_ab VALUES(2, 'two');
INSERT INTO t3_ro SELECT * FROM t3_ab;
INSERT INTO t3_ig SELECT * FROM t3_ab;
INSERT INTO t3_fa SELECT * FROM t3_ab;
INSERT INTO t3_re SELECT * FROM t3_ab;
INSERT INTO t3_xx SELECT * FROM t3_ab;
CREATE TABLE t4(a, b CHECK (b!=10));
INSERT INTO t4 VALUES(1, 2);
INSERT INTO t4 VALUES(3, 4);
SELECT * FROM t4
CREATE TABLE t5(a NOT NULL ON CONFLICT IGNORE, b NOT NULL ON CONFLICT ABORT);
CREATE TABLE t1(x, y);
INSERT INTO t1 VALUES('one', 'first');
INSERT INTO t1 VALUES('two', 'second');
INSERT INTO t1 VALUES('three', 'third');
CREATE TABLE t2(oid, b);
CREATE TABLE t3(a, _rowid_);
CREATE TABLE t4(a, b, rowid);
INSERT INTO t2 VALUES('one', 'two');
INSERT INTO t2 VALUES('three', 'four');
INSERT INTO t3 VALUES('five', 'six');
INSERT INTO t3 VALUES('seven', 'eight');
INSERT INTO t4 VALUES('nine', 'ten', 'eleven');
INSERT INTO t4 VALUES('twelve', 'thirteen', 'fourteen');
CREATE TABLE t6(pk INT primary key);
CREATE TABLE t7(pk BIGINT primary key);
CREATE TABLE t8(pk SHORT INTEGER primary key);
CREATE TABLE t9(pk UNSIGNED INTEGER primary key);
INSERT INTO t6 VALUES('2.0');
INSERT INTO t7 VALUES('2.0');
INSERT INTO t8 VALUES('2.0');
INSERT INTO t9 VALUES('2.0');
SELECT typeof(pk), pk FROM t6;
SELECT typeof(pk), pk FROM t7;
SELECT typeof(pk), pk FROM t8;
SELECT typeof(pk), pk FROM t9;
CREATE TABLE t10(a, b);
INSERT INTO t10 VALUES('ten', 10);
CREATE TABLE t11(a, b INTEGER PRIMARY KEY);
INSERT INTO t11 VALUES('ten', 10);
CREATE TABLE t12(x INTEGER PRIMARY KEY, y);
INSERT INTO t12 VALUES(5, 'five');
DELETE FROM t12
DELETE FROM t12