Repository navigation
Expand file tree
/
Copy pathddl.sql
More file actions
36 lines (36 loc) · 1.47 KB
/
Copy pathddl.sql
File metadata and controls
36 lines (36 loc) · 1.47 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- CREATE, ALTER and DROP.
CREATE TEMP TABLE IF NOT EXISTS sch.t (
a INTEGER PRIMARY KEY DESC ON CONFLICT FAIL AUTOINCREMENT,
b TEXT NOT NULL ON CONFLICT IGNORE DEFAULT 'x' COLLATE nocase,
c REAL DEFAULT -1 CHECK (c > 0) UNIQUE,
d DEFAULT (1 + 2) REFERENCES u(v) ON DELETE CASCADE ON UPDATE SET NULL MATCH full
DEFERRABLE INITIALLY DEFERRED,
e AS (a + 1) STORED,
f GENERATED ALWAYS AS (a + 2) VIRTUAL,
CONSTRAINT pk PRIMARY KEY (a, b DESC) ON CONFLICT ABORT,
UNIQUE (c) ON CONFLICT ROLLBACK,
CHECK (d),
FOREIGN KEY (e COLLATE binary ASC) REFERENCES u (v) NOT DEFERRABLE
) WITHOUT ROWID, STRICT;
CREATE TABLE t2 AS SELECT 1;
CREATE UNIQUE INDEX IF NOT EXISTS sch.i ON t (a COLLATE nocase, b DESC) WHERE a;
CREATE VIEW v (x, y) AS SELECT 1, 2;
CREATE VIRTUAL TABLE IF NOT EXISTS sch.vt USING mod (a b c, "d e", (nested, list));
CREATE TRIGGER IF NOT EXISTS tr INSTEAD OF UPDATE OF a, b ON sch.t FOR EACH ROW WHEN x BEGIN
UPDATE t SET a = 1 WHERE b;
INSERT INTO t VALUES (1) ON CONFLICT DO NOTHING;
DELETE FROM t WHERE a;
SELECT RAISE(ROLLBACK, 'msg'), RAISE(IGNORE);
END;
ALTER TABLE t RENAME TO u;
ALTER TABLE t RENAME COLUMN a TO b;
ALTER TABLE t ADD COLUMN c TEXT NOT NULL;
ALTER TABLE t DROP COLUMN c;
ALTER TABLE t DROP CONSTRAINT k;
ALTER TABLE t ALTER COLUMN a DROP NOT NULL;
ALTER TABLE t ALTER COLUMN a SET NOT NULL ON CONFLICT FAIL;
ALTER TABLE t ADD CONSTRAINT k CHECK (a) ON CONFLICT ABORT;
DROP TABLE IF EXISTS sch.t;
DROP VIEW v;
DROP INDEX i;
DROP TRIGGER tr;