-
Notifications
You must be signed in to change notification settings - Fork 1
/
schema_sqlite.sql
executable file
·33 lines (28 loc) · 1005 Bytes
/
schema_sqlite.sql
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
PRAGMA journal_mode = WAL;
PRAGMA synchronous = OFF;
CREATE TABLE properties (
propertyName VARCHAR(32),
propertyValue VARCHAR(255)
);
INSERT INTO properties VALUES ( 'version', 1 );
CREATE TABLE rrsets (
revision INTEGER NOT NULL DEFAULT 0,
sigage TIMESTAMP DEFAULT NULL,
dname VARCHAR(256) NOT NULL,
dcontent TEXT
);
CREATE INDEX IF NOT EXISTS rrsets_idx_revision ON rrsets ( revision, dname );
CREATE INDEX IF NOT EXISTS rrsets_idx_dname ON rrsets ( dname );
CREATE TABLE refrrsets (
dname VARCHAR(256) NOT NULL,
revision INTEGER NOT NULL
);
CREATE index IF NOT EXISTS refrrsets_idx_dname ON refrrsets ( dname );
CREATE VIEW currrsets AS SELECT MAX(revision) AS revision, dname AS dname
FROM rrsets
GROUP BY dname;
CREATE TRIGGER rrsetinsert INSERT ON rrsets
BEGIN
REPLACE INTO refrrsets VALUES ( NEW.dname, NEW.revision );
END;
INSERT INTO refrrsets SELECT dname, max(revision) FROM rrsets GROUP BY dname;