mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-12 15:33:44 +00:00
396 lines
8.7 KiB
Plaintext
396 lines
8.7 KiB
Plaintext
|
|
CREATE TABLE t1(a, b);
|
|
CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
|
|
SELECT sum(b) OVER w FROM t1 WINDOW w AS (ORDER BY a);
|
|
END;
|
|
|
|
|
|
ALTER TABLE t1 RENAME a TO aaa;
|
|
|
|
|
|
SELECT sql FROM sqlite_master WHERE name='tr1'
|
|
|
|
|
|
INSERT INTO t1 VALUES(1, 2);
|
|
|
|
|
|
CREATE TABLE t1(a,b,c);
|
|
CREATE TABLE t2(a,b,c);
|
|
CREATE TRIGGER r1 AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN
|
|
SELECT a,b, a name FROM t1
|
|
INTERSECT
|
|
SELECT a,b,c FROM t1 WHERE b>='d' ORDER BY name;
|
|
SELECT new.c;
|
|
END;
|
|
|
|
|
|
ALTER TABLE t1 RENAME TO t1x;
|
|
SELECT sql FROM sqlite_master WHERE name = 'r1';
|
|
|
|
|
|
CREATE TABLE t1(a, b, c, d);
|
|
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a=1 OR (b IN ());
|
|
|
|
|
|
ALTER TABLE t1 RENAME b TO bbb;
|
|
|
|
|
|
SELECT sql FROM sqlite_master WHERE name = 'v1'
|
|
|
|
|
|
CREATE TABLE t1(a, b);
|
|
CREATE TABLE t3(e, f);
|
|
CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
|
|
INSERT INTO t2 VALUES(new.a, new.b);
|
|
END;
|
|
|
|
|
|
COMMIT;
|
|
|
|
|
|
SELECT type, name, tbl_name, sql
|
|
FROM sqlite_master WHERE type='table' AND name!='t1';
|
|
|
|
|
|
COMMIT;
|
|
|
|
|
|
SELECT type, name, tbl_name, sql
|
|
FROM sqlite_master WHERE type='table' AND name!='t1';
|
|
|
|
|
|
CREATE TABLE t1 (
|
|
c1 integer, c2, PRIMARY KEY(c1 collate rtrim),
|
|
UNIQUE(c2)
|
|
)
|
|
|
|
|
|
ALTER TABLE t1 RENAME c1 TO c3;
|
|
|
|
|
|
CREATE TEMPORARY TABLE Table0 (
|
|
Col0 INTEGER,
|
|
PRIMARY KEY(Col0 COLLATE RTRIM),
|
|
FOREIGN KEY (Col0) REFERENCES Table0
|
|
);
|
|
|
|
|
|
ALTER TABLE Table0 RENAME Col0 TO Col0;
|
|
|
|
|
|
CREATE TABLE t1(a,b,c);
|
|
CREATE TRIGGER AFTER INSERT ON t1 BEGIN
|
|
SELECT a, rank() OVER w1 FROM t1
|
|
WINDOW w1 AS (PARTITION BY b, percent_rank() OVER w1);
|
|
END;
|
|
|
|
|
|
ALTER TABLE t1 RENAME TO t1x;
|
|
SELECT sql FROM sqlite_master;
|
|
|
|
|
|
DROP TRIGGER after;
|
|
CREATE TRIGGER AFTER INSERT ON t1x BEGIN
|
|
SELECT a, rank() OVER w1 FROM t1x
|
|
WINDOW w1 AS (PARTITION BY b, percent_rank() OVER w1 ORDER BY d);
|
|
END;
|
|
|
|
|
|
CREATE TABLE t0(c0);
|
|
CREATE INDEX i0 ON t0('1' IN ());
|
|
|
|
|
|
ALTER TABLE t0 RENAME TO t1;
|
|
SELECT sql FROM sqlite_master;
|
|
|
|
|
|
CREATE TABLE t2 (c0);
|
|
CREATE INDEX i2 ON t2((LIKELIHOOD(c0, 100) IN ()));
|
|
ALTER TABLE t2 RENAME COLUMN c0 TO c1;
|
|
|
|
|
|
SELECT sql FROM sqlite_master WHERE tbl_name = 't2';
|
|
|
|
|
|
CREATE TABLE t1(a,b,c);
|
|
CREATE TRIGGER AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN
|
|
SELECT true WHERE (SELECT a, b FROM (t1)) IN ();
|
|
END;
|
|
|
|
|
|
ALTER TABLE t1 RENAME TO t1x;
|
|
|
|
|
|
CREATE TABLE t1(a, b, c);
|
|
CREATE TABLE t2(a, b, c);
|
|
CREATE VIEW v1 AS SELECT * FROM t1 WHERE (
|
|
SELECT t1.a FROM t1, t2
|
|
) IN () OR t1.a=5;
|
|
|
|
|
|
ALTER TABLE t2 RENAME TO t3;
|
|
SELECT sql FROM sqlite_master WHERE name='v1';
|
|
|
|
|
|
CREATE TABLE t1(
|
|
a,b,c,d,e,f,g,h,j,jj,jjb,k,aa,bb,cc,dd,ee DEFAULT 3.14,
|
|
ff DEFAULT('hiccup'),Wg NOD NULL DEFAULT(false)
|
|
);
|
|
|
|
CREATE TRIGGER b AFTER INSERT ON t1 WHEN new.a BEGIN
|
|
SELECT a, sum() w3 FROM t1
|
|
WINDOW b AS (ORDER BY NOT EXISTS(SELECT 1 FROM abc));
|
|
END;
|
|
|
|
|
|
DROP TRIGGER b;
|
|
CREATE TRIGGER b AFTER INSERT ON t1 WHEN new.a BEGIN
|
|
SELECT a, sum() w3 FROM t1
|
|
WINDOW b AS (ORDER BY NOT EXISTS(SELECT 1 FROM t1));
|
|
END;
|
|
|
|
|
|
ALTER TABLE t1 RENAME TO t1x;
|
|
SELECT sql FROM sqlite_master WHERE name = 'b';
|
|
|
|
|
|
CREATE TABLE t1(a,b,c,d,e,f,g,h,j,jj,Zjj,k,aQ,bb,cc,dd,ee DEFAULT 3.14,
|
|
ff DEFAULT('hiccup'),gg NOD NULL DEFAULT(false));
|
|
CREATE TRIGGER AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN
|
|
|
|
SELECT b () OVER , dense_rank() OVER d, d () OVER w1
|
|
FROM t1
|
|
WINDOW
|
|
w1 AS
|
|
( w1 ORDER BY d
|
|
ROWS BETWEEN 2 NOT IN(SELECT a, sum(d) w2,max(d)OVER FROM t1
|
|
WINDOW
|
|
w1 AS
|
|
(PARTITION BY d
|
|
ROWS BETWEEN '' PRECEDING AND false FOLLOWING),
|
|
d AS
|
|
(PARTITION BY b ORDER BY d
|
|
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
) PRECEDING AND 1 FOLLOWING),
|
|
w2 AS
|
|
(PARTITION BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
|
|
w3 AS
|
|
(PARTITION BY b ORDER BY d
|
|
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
|
|
;
|
|
SELECT a, sum(d) w2,max(d)OVER FROM t1
|
|
WINDOW
|
|
w1 AS
|
|
(PARTITION BY d
|
|
ROWS BETWEEN '' PRECEDING AND false FOLLOWING),
|
|
d AS
|
|
(PARTITION BY b ORDER BY d
|
|
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
;
|
|
|
|
END;
|
|
|
|
|
|
ALTER TABLE t1 RENAME TO t1x;
|
|
|
|
|
|
CREATE TABLE t1(a);
|
|
CREATE TRIGGER r1 INSERT ON t1 BEGIN
|
|
SELECT a(*) OVER (ORDER BY (SELECT 1)) FROM t1;
|
|
END;
|
|
|
|
|
|
ALTER TABLE t1 RENAME TO t1x;
|
|
|
|
|
|
CREATE TABLE t1(a);
|
|
CREATE TABLE t2(b);
|
|
CREATE TRIGGER AFTER INSERT ON t1 BEGIN
|
|
SELECT sum() FILTER (WHERE (SELECT sum() FILTER (WHERE 0)) AND a);
|
|
END;
|
|
|
|
|
|
CREATE TABLE t1(x);
|
|
CREATE TRIGGER AFTER INSERT ON t1 BEGIN
|
|
SELECT (WITH t2 AS (WITH t3 AS (SELECT true)
|
|
SELECT * FROM t3 ORDER BY true COLLATE nocase)
|
|
SELECT 11);
|
|
|
|
WITH t4 AS (SELECT * FROM t1) SELECT 33;
|
|
END;
|
|
|
|
|
|
ALTER TABLE t1 RENAME TO t1x;
|
|
|
|
|
|
CREATE TABLE t1(a,b,c);
|
|
CREATE TRIGGER AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN
|
|
SELECT a () FILTER (WHERE a>0) FROM t1;
|
|
END;
|
|
|
|
|
|
ALTER TABLE t1 RENAME TO t1x;
|
|
ALTER TABLE t1x RENAME a TO aaa;
|
|
SELECT sql FROM sqlite_master WHERE type='trigger';
|
|
|
|
|
|
CREATE TABLE t1(a,b);
|
|
CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
|
|
SELECT a, b FROM t1
|
|
INTERSECT SELECT b,a FROM t1
|
|
ORDER BY b IN (
|
|
SELECT a UNION SELECT b
|
|
FROM t1
|
|
ORDER BY b COLLATE nocase
|
|
)
|
|
;
|
|
END;
|
|
|
|
|
|
CREATE TABLE a(a,h CONSTRAINT a UNIQUE ON CONFLICT FAIL,CONSTRAINT a);
|
|
|
|
|
|
CREATE TABLE s(col);
|
|
CREATE VIEW v AS SELECT (
|
|
WITH x(a) AS(SELECT * FROM s) VALUES(RIGHT)
|
|
) IN() ;
|
|
CREATE TABLE a(a);
|
|
ALTER TABLE a RENAME a TO b;
|
|
|
|
|
|
CREATE TABLE t1(a);
|
|
CREATE VIEW v2(b) AS SELECT * FROM v2;
|
|
|
|
|
|
DROP VIEW v2;
|
|
CREATE VIEW v2(b) AS WITH t3 AS (SELECT b FROM v2) SELECT * FROM t3;
|
|
|
|
|
|
DROP VIEW v2;
|
|
CREATE VIEW v2(b) AS WITH t3 AS (SELECT b FROM v2) VALUES(1);
|
|
|
|
|
|
CREATE TABLE t1(x);
|
|
CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
|
|
UPDATE t1 SET (c,d)=((SELECT 1 FROM t1 JOIN t2 ON b=x),1);
|
|
END;
|
|
|
|
|
|
CREATE TABLE v0 (a);
|
|
CREATE VIEW v2 (v3) AS
|
|
WITH x1 AS (SELECT * FROM v2)
|
|
SELECT v3 AS x, v3 AS y FROM v2;
|
|
|
|
|
|
CREATE TABLE v0 (v1);
|
|
CREATE TABLE v2 (v3 INTEGER UNIQUE ON CONFLICT ABORT);
|
|
CREATE TRIGGER x AFTER INSERT ON v2 WHEN (
|
|
( SELECT v1 AS PROMO_REVENUE FROM v2 JOIN v0 USING ( VALUE ) ) AND 0 )
|
|
BEGIN
|
|
DELETE FROM v2;
|
|
END;
|
|
|
|
|
|
DROP TRIGGER x;
|
|
CREATE TRIGGER x AFTER INSERT ON v2 WHEN (
|
|
0 AND (SELECT rowid FROM v0)
|
|
) BEGIN
|
|
DELETE FROM v2;
|
|
END;
|
|
|
|
|
|
ALTER TABLE v0 RENAME TO xyz;
|
|
SELECT sql FROM sqlite_master WHERE type='trigger'
|
|
|
|
|
|
CREATE TABLE t1(a, b, c);
|
|
CREATE TABLE t2(a, b, c);
|
|
CREATE TRIGGER ttt AFTER INSERT ON t1 BEGIN
|
|
UPDATE t1 SET a=t2.a FROM t2 WHERE t1.a=t2.a;
|
|
END;
|
|
|
|
|
|
# ALTER TABLE t2 RENAME COLUMN a TO aaa;
|
|
#
|
|
|
|
CREATE TABLE t1(x);
|
|
|
|
CREATE TABLE t3(y);
|
|
CREATE TABLE t4(z);
|
|
|
|
CREATE TRIGGER tr1 INSERT ON t3 BEGIN
|
|
UPDATE t3 SET y=z FROM (SELECT z FROM t4);
|
|
END;
|
|
|
|
CREATE TRIGGER tr2 INSERT ON t3 BEGIN
|
|
UPDATE t3 SET y=abc FROM (SELECT x AS abc FROM t1);
|
|
END;
|
|
|
|
|
|
ALTER TABLE t1 RENAME TO t2;
|
|
|
|
|
|
ALTER TABLE t2 RENAME x TO xx;
|
|
|
|
|
|
SELECT sql FROM sqlite_schema WHERE name='tr2'
|
|
|
|
|
|
CREATE TABLE t1(xx);
|
|
CREATE TRIGGER xx INSERT ON t1 BEGIN
|
|
UPDATE t1 SET xx=xx FROM(SELECT xx);
|
|
END;
|
|
|
|
|
|
CREATE TABLE t1(a, b AS ((WITH w1 (xyz) AS ( SELECT t1.b FROM t1 ) SELECT 123) IN ()), c);
|
|
|
|
|
|
ALTER TABLE t1 DROP COLUMN c;
|
|
SELECT sql FROM sqlite_schema WHERE name = 't1';
|
|
|
|
|
|
CREATE TABLE t0(c0 , c1 AS (CASE TRUE NOT IN () WHEN NULL THEN CASE + 0xa ISNULL WHEN NOT + 0x9 THEN t0.c1 ELSE CURRENT_TIME LIKE CAST (t0.c1 REGEXP '-([1-9]\d*.\d*|0\.\d*[1-9]\d*)'ESCAPE (c1) COLLATE BINARY BETWEEN c1 AND c1 NOT IN (WITH t4 (c0) AS (WITH t3 (c0) AS NOT MATERIALIZED (WITH RECURSIVE t2 (c0) AS (WITH RECURSIVE t1 AS (VALUES (x'717171ff71717171' ) ) SELECT DISTINCT t0.c0 FROM t0 NOT INDEXED WHERE t0.c0 =t0.c0 GROUP BY 0x9 ) SELECT DISTINCT t0.c0 FROM t0 NOT INDEXED WHERE t0.c0 =t0.c1 ) SELECT DISTINCT t0.c0 FROM t0 NOT INDEXED WHERE t0.c0 =t0.c0 GROUP BY typeof(0x9 ) ) SELECT DISTINCT t0.c0 FROM t0 NOT INDEXED WHERE t0.c0 =t0.c0 GROUP BY typeof(typeof(0x9 ) ) ) IN t0 BETWEEN typeof(typeof(typeof(hex(*) FILTER (WHERE + x'5ccd1e68' ) ) ) ) AND 1 >0xa AS BLOB (+4.4E4 , -0xe ) ) END <> c1 IN () END ) VIRTUAL , c35 PRIMARY KEY , c60 , c64 NUMERIC (-6.8 , -0xE ) ) WITHOUT ROWID ;
|
|
|
|
|
|
ALTER TABLE t0 DROP COLUMN c60;
|
|
|
|
|
|
CREATE TABLE t1(a,b,c,d);
|
|
CREATE TRIGGER AFTER INSERT ON t1 BEGIN
|
|
UPDATE t1 SET (c,d)=(a,b);
|
|
END;
|
|
ALTER TABLE t1 RENAME TO t2;
|
|
|
|
|
|
SELECT sql FROM sqlite_schema WHERE type='trigger'
|
|
|
|
|
|
CREATE TABLE t1(x, y);
|
|
CREATE TRIGGER Trigger1 DELETE ON t1
|
|
BEGIN
|
|
SELECT t1.*, t1.x FROM t1 ORDER BY t1.x;
|
|
END;
|
|
|
|
|
|
ALTER TABLE t1 RENAME x TO z;
|
|
|
|
|
|
ALTER TABLE t1 RENAME TO t2;
|
|
|
|
|
|
CREATE TRIGGER tr2 AFTER DELETE ON t2 BEGIN
|
|
SELECT z, y FROM (
|
|
SELECT t2.* FROM t2
|
|
);
|
|
END;
|
|
|
|
|
|
DELETE FROM t2
|
|
|
|
|
|
ALTER TABLE t2 RENAME TO t3;
|
|
|
|
|
|
SELECT sql FROM sqlite_schema WHERE type='trigger'
|
|
|