test-2-dump.sql (2186B)
1 PRAGMA foreign_keys=OFF; 2 BEGIN TRANSACTION; 3 CREATE TABLE config (key TEXT PRIMARY KEY, val ANY); 4 INSERT INTO config VALUES('json-prefix','test-'); 5 INSERT INTO config VALUES('server-port',9090); 6 INSERT INTO config VALUES('default_topic','default'); 7 CREATE TABLE topic (id INTEGER PRIMARY KEY, 8 name TEXT NOT NULL, 9 closed INTEGER NOT NULL DEFAULT 0); 10 INSERT INTO topic VALUES(1,'default',0); 11 CREATE TABLE subject (id INTEGER PRIMARY KEY, 12 topic_id NOT NULL REFERENCES topic(id) 13 ON UPDATE CASCADE ON DELETE CASCADE, 14 name TEXT NOT NULL, 15 hidden INTEGER NOT NULL DEFAULT 0); 16 INSERT INTO subject VALUES(1,1,'foo',0); 17 INSERT INTO subject VALUES(2,1,'bar',0); 18 INSERT INTO subject VALUES(3,1,'meow',0); 19 CREATE TABLE object (id INTEGER PRIMARY KEY, 20 topic_id NOT NULL REFERENCES topic(id) 21 ON UPDATE CASCADE ON DELETE CASCADE, 22 name TEXT NOT NULL, 23 hidden INTEGER NOT NULL DEFAULT 0); 24 INSERT INTO object VALUES(1,1,'01',0); 25 INSERT INTO object VALUES(2,1,'03',0); 26 INSERT INTO object VALUES(3,1,'02',0); 27 INSERT INTO object VALUES(4,1,'04',0); 28 CREATE TABLE pref (id INTEGER PRIMARY KEY, 29 sub_id NOT NULL REFERENCES subject(id) 30 ON UPDATE CASCADE ON DELETE CASCADE, 31 obj_id NOT NULL REFERENCES object(id) 32 ON UPDATE CASCADE ON DELETE CASCADE, 33 val INTEGER NOT NULL DEFAULT 0); 34 INSERT INTO pref VALUES(3,1,4,2); 35 INSERT INTO pref VALUES(4,1,1,4); 36 INSERT INTO pref VALUES(5,2,1,0); 37 CREATE UNIQUE INDEX topic_name ON topic(name); 38 CREATE UNIQUE INDEX sub_name ON subject(topic_id,name); 39 CREATE UNIQUE INDEX obj_name ON object(topic_id,name); 40 CREATE INDEX v_sub_name ON subject(topic_id,name) WHERE hidden=0; 41 CREATE INDEX v_obj_name ON object(topic_id,name) WHERE hidden=0; 42 CREATE UNIQUE INDEX sub_obj ON pref(sub_id,obj_id); 43 COMMIT;