mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-12 15:33:44 +00:00
154 lines
4.2 KiB
Plaintext
154 lines
4.2 KiB
Plaintext
|
|
SELECT * FROM t4 ORDER BY a;
|
|
|
|
|
|
SELECT * FROM t4 ORDER BY a;
|
|
|
|
|
|
CREATE TABLE t1(a, b);
|
|
CREATE TABLE t2(x, y);
|
|
CREATE TABLE t3(i, j, UNIQUE(i));
|
|
|
|
INSERT INTO t2 VALUES('a', 'b');
|
|
INSERT INTO t2 VALUES('c', 'd');
|
|
|
|
INSERT INTO t3 VALUES(4, 16);
|
|
INSERT INTO t3 VALUES(5, 25);
|
|
INSERT INTO t3 VALUES(6, 36);
|
|
|
|
SELECT rowid FROM t1
|
|
|
|
DELETE FROM t1;
|
|
DELETE FROM t2;
|
|
DELETE FROM t3;
|
|
|
|
INSERT INTO t2 VALUES('a', 'b');
|
|
INSERT INTO t2 VALUES('c', 'd');
|
|
|
|
INSERT INTO t3 VALUES(4, 16);
|
|
INSERT INTO t3 VALUES(5, 25);
|
|
INSERT INTO t3 VALUES(6, 36);
|
|
|
|
|
|
INSERT INTO t3 VALUES(10, 100);
|
|
SELECT rowid, * FROM t3;
|
|
|
|
|
|
CREATE TABLE t4(a, b);
|
|
INSERT INTO t4 VALUES('a', 1);
|
|
INSERT INTO t4 VALUES('b', 2);
|
|
INSERT INTO t4 VALUES('c', 3);
|
|
|
|
CREATE TRIGGER t4t BEFORE DELETE ON t4 BEGIN
|
|
DELETE FROM t4 WHERE b = 1;
|
|
END;
|
|
|
|
|
|
INSERT INTO t4(rowid, a, b) VALUES(1, 'a', 1);
|
|
INSERT INTO t4(rowid, a, b) VALUES(3, 'c', 3);
|
|
|
|
|
|
CREATE TABLE t5(a, b);
|
|
INSERT INTO t5 VALUES('a', 1);
|
|
INSERT INTO t5 VALUES('b', 2);
|
|
INSERT INTO t5 VALUES('c', 3);
|
|
|
|
CREATE TRIGGER t5t BEFORE UPDATE ON t5 BEGIN
|
|
DELETE FROM t5 WHERE b = 1;
|
|
END;
|
|
|
|
|
|
INSERT INTO t5(rowid, a, b) VALUES(1, 'a', 1);
|
|
|
|
|
|
CREATE TABLE t7(a, b);
|
|
INSERT INTO t7 VALUES('one', 'two');
|
|
INSERT INTO t7 VALUES('three', 'four');
|
|
ALTER TABLE t7 ADD COLUMN c DEFAULT NULL;
|
|
|
|
|
|
CREATE TABLE t8(a, b);
|
|
INSERT INTO t8 VALUES('one', 'two');
|
|
INSERT INTO t8 VALUES('three', 'four');
|
|
ALTER TABLE t8 ADD COLUMN c DEFAULT 'xxx';
|
|
|
|
CREATE TABLE t9(a, b INTEGER PRIMARY KEY, c)
|
|
|
|
CREATE TABLE t1(x PRIMARY KEY);
|
|
CREATE TABLE t2(x PRIMARY KEY);
|
|
CREATE TABLE t3(x PRIMARY KEY);
|
|
CREATE TABLE t4(x PRIMARY KEY);
|
|
|
|
CREATE TRIGGER a AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(new.x); END;
|
|
CREATE TRIGGER b AFTER INSERT ON t2 BEGIN INSERT INTO t3 VALUES(new.x); END;
|
|
CREATE TRIGGER c AFTER INSERT ON t3 BEGIN INSERT INTO t4 VALUES(new.x); END;
|
|
|
|
CREATE TRIGGER d AFTER UPDATE ON t1 BEGIN UPDATE t2 SET x = new.x; END;
|
|
CREATE TRIGGER e AFTER UPDATE ON t2 BEGIN UPDATE t3 SET x = new.x; END;
|
|
CREATE TRIGGER f AFTER UPDATE ON t3 BEGIN UPDATE t4 SET x = new.x; END;
|
|
|
|
CREATE TRIGGER g AFTER DELETE ON t1 BEGIN DELETE FROM t2 WHERE 1; END;
|
|
CREATE TRIGGER h AFTER DELETE ON t2 BEGIN DELETE FROM t3 WHERE 1; END;
|
|
CREATE TRIGGER i AFTER DELETE ON t3 BEGIN DELETE FROM t4 WHERE 1; END;
|
|
|
|
|
|
DROP TRIGGER a; DROP TRIGGER b; DROP TRIGGER c;
|
|
DROP TRIGGER d; DROP TRIGGER e; DROP TRIGGER f;
|
|
DROP TRIGGER g; DROP TRIGGER h; DROP TRIGGER i;
|
|
|
|
CREATE TRIGGER a BEFORE INSERT ON t1 BEGIN INSERT INTO t2 VALUES(new.x); END;
|
|
CREATE TRIGGER b BEFORE INSERT ON t2 BEGIN INSERT INTO t3 VALUES(new.x); END;
|
|
CREATE TRIGGER c BEFORE INSERT ON t3 BEGIN INSERT INTO t4 VALUES(new.x); END;
|
|
|
|
CREATE TRIGGER d BEFORE UPDATE ON t1 BEGIN UPDATE t2 SET x = new.x; END;
|
|
CREATE TRIGGER e BEFORE UPDATE ON t2 BEGIN UPDATE t3 SET x = new.x; END;
|
|
CREATE TRIGGER f BEFORE UPDATE ON t3 BEGIN UPDATE t4 SET x = new.x; END;
|
|
|
|
CREATE TRIGGER g BEFORE DELETE ON t1 BEGIN DELETE FROM t2 WHERE 1; END;
|
|
CREATE TRIGGER h BEFORE DELETE ON t2 BEGIN DELETE FROM t3 WHERE 1; END;
|
|
CREATE TRIGGER i BEFORE DELETE ON t3 BEGIN DELETE FROM t4 WHERE 1; END;
|
|
|
|
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
|
|
CREATE TABLE t2(a, b INTEGER PRIMARY KEY);
|
|
|
|
|
|
ALTER TABLE t1 ADD COLUMN d;
|
|
|
|
|
|
CREATE TABLE t3(a, b INTEGER PRIMARY KEY);
|
|
|
|
SELECT * FROM t3
|
|
|
|
CREATE TABLE t1(a, b);
|
|
CREATE INDEX idx1 ON t1(a);
|
|
CREATE INDEX idx2 ON t1(b);
|
|
|
|
INSERT INTO t1 VALUES(1, 2);
|
|
INSERT INTO t1 VALUES(3, 4);
|
|
INSERT INTO t1 VALUES(5, 6);
|
|
INSERT INTO t1 VALUES(7, 8);
|
|
|
|
|
|
INSERT INTO t1 VALUES(9, 10);
|
|
INSERT INTO t1 VALUES(11, 12);
|
|
INSERT INTO t1 VALUES(13, 14);
|
|
INSERT INTO t1 VALUES(15, 16);
|
|
|
|
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
|
|
CREATE TABLE t2(a INTEGER PRIMARY KEY, b) WITHOUT ROWID;
|
|
|
|
INSERT INTO t1 VALUES(1, 2);
|
|
INSERT INTO t1 VALUES(3, 4);
|
|
INSERT INTO t2 VALUES(5, 6);
|
|
INSERT INTO t2 VALUES(7, 8);
|
|
|
|
CREATE TABLE t3 (a INTEGER PRIMARY KEY, b) WITHOUT ROWID;
|
|
|
|
|
|
CREATE TABLE t4(a COLLATE nocase PRIMARY KEY, b) WITHOUT ROWID;
|
|
INSERT INTO t4 VALUES('abc', 1);
|
|
INSERT INTO t4 VALUES('DEF', 2);
|
|
|