mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-12 15:33:44 +00:00
103 lines
2.6 KiB
Plaintext
103 lines
2.6 KiB
Plaintext
|
|
CREATE TABLE t11a AS SELECT '123456789' AS x;
|
|
CREATE TABLE t11b (a INTEGER PRIMARY KEY, b, c);
|
|
INSERT INTO t11b SELECT x, x, x FROM t11a;
|
|
SELECT quote(a), quote(b), quote(c) FROM t11b;
|
|
|
|
|
|
CREATE TABLE t12a(a,b,c,d,e,f,g);
|
|
INSERT INTO t12a VALUES(101,102,103,104,105,106,107);
|
|
CREATE TABLE t12b(x);
|
|
INSERT INTO t12b(x,rowid,x,x,x,x,x) SELECT * FROM t12a;
|
|
SELECT rowid, x FROM t12b;
|
|
|
|
|
|
CREATE TABLE tab1( value INTEGER);
|
|
INSERT INTO tab1 (value, _rowid_) values( 11, 1);
|
|
INSERT INTO tab1 (value, _rowid_) SELECT 22,999;
|
|
SELECT * FROM tab1;
|
|
|
|
|
|
CREATE TABLE t12c(a, b DEFAULT 'xyzzy', c);
|
|
INSERT INTO t12c(a, rowid, c) SELECT 'one', 999, 'two';
|
|
SELECT * FROM t12c;
|
|
|
|
|
|
DROP TABLE IF EXISTS t13;
|
|
CREATE TABLE t13(a INTEGER PRIMARY KEY,b UNIQUE);
|
|
CREATE INDEX t13x1 ON t13(-b=b);
|
|
INSERT INTO t13 VALUES(1,5),(6,2);
|
|
REPLACE INTO t13 SELECT b,0 FROM t13;
|
|
SELECT * FROM t13 ORDER BY +b;
|
|
|
|
|
|
DROP TABLE IF EXISTS t14;
|
|
CREATE TABLE t14(x INTEGER PRIMARY KEY);
|
|
INSERT INTO t14 VALUES(CASE WHEN 1 THEN null END);
|
|
SELECT x FROM t14;
|
|
|
|
|
|
DROP TABLE IF EXISTS t1;
|
|
DROP TABLE IF EXISTS t2;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
|
|
CREATE INDEX i1 ON t1(b);
|
|
CREATE TABLE t2(a, b);
|
|
INSERT INTO t2 VALUES(4, randomblob(31000));
|
|
INSERT INTO t2 VALUES(4, randomblob(32000));
|
|
INSERT INTO t2 VALUES(4, randomblob(33000));
|
|
REPLACE INTO t1 SELECT a, b FROM t2;
|
|
SELECT a, length(b) FROM t1;
|
|
|
|
|
|
SELECT * FROM t0;
|
|
|
|
|
|
CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
|
|
CREATE UNIQUE INDEX t2b ON t2(b);
|
|
INSERT INTO t2(a,b) VALUES(1,1),(2,2),(3,3),(4,4);
|
|
CREATE TABLE fire(x);
|
|
CREATE TRIGGER t2r1 AFTER DELETE ON t2 BEGIN
|
|
INSERT INTO fire VALUES(old.a);
|
|
END;
|
|
UPDATE OR REPLACE t2 SET a=4, b=3 WHERE a=1;
|
|
SELECT *, 'x' FROM t2 ORDER BY a;
|
|
|
|
|
|
SELECT x FROM fire ORDER BY x;
|
|
|
|
|
|
DELETE FROM t2;
|
|
DELETE FROM fire;
|
|
INSERT INTO t2(a,b) VALUES(1,1),(2,2),(3,3),(4,4);
|
|
UPDATE OR REPLACE t2 SET a=1, b=3 WHERE a=1;
|
|
SELECT *, 'x' FROM t2 ORDER BY a;
|
|
|
|
|
|
SELECT x FROM fire ORDER BY x;
|
|
|
|
|
|
CREATE TABLE t3(a INTEGER PRIMARY KEY, b INT, c INT, d INT);
|
|
CREATE UNIQUE INDEX t3bpi ON t3(b) WHERE c<=d;
|
|
CREATE UNIQUE INDEX t3d ON t3(d);
|
|
INSERT INTO t3(a,b,c,d) VALUES(1,1,1,1),(2,1,3,2),(3,4,5,6);
|
|
CREATE TRIGGER t3r1 AFTER DELETE ON t3 BEGIN
|
|
SELECT 'hi';
|
|
END;
|
|
REPLACE INTO t3(a,b,c,d) VALUES(4,4,8,9);
|
|
|
|
|
|
SELECT *, 'x' FROM t3 ORDER BY a;
|
|
|
|
|
|
REPLACE INTO t3(a,b,c,d) VALUES(5,1,11,2);
|
|
SELECT *, 'x' FROM t3 ORDER BY a;
|
|
|
|
|
|
DELETE FROM t3;
|
|
INSERT INTO t3(a,b,c,d) VALUES(1,1,1,1),(2,1,3,2),(3,4,5,6);
|
|
DROP TRIGGER t3r1;
|
|
CREATE TRIGGER t3r1 AFTER DELETE ON t3 BEGIN
|
|
INSERT INTO t3(b,c,d) VALUES(old.b,old.c,old.d);
|
|
END;
|
|
|