diff --git a/storymap/init.sql b/storymap/init.sql new file mode 100644 index 0000000..766f81e --- /dev/null +++ b/storymap/init.sql @@ -0,0 +1,27 @@ +CREATE TABLE IF NOT EXISTS mapitem( + ename PRIMARY KEY, name TEXT NOT NULL, addr TEXT, lon REAL, lat REAL +); + +CREATE TABLE IF NOT EXISTS maptype( + ename, maptype TEXT, + FOREIGN KEY(ename) REFERENCES mapitem(ename) + ON DELETE CASCADE ON UPDATE CASCADE, + UNIQUE(ename, maptype) +); + +CREATE TABLE IF NOT EXISTS attribute( + ename, maptype TEXT, + FOREIGN KEY(ename) REFERENCES mapitem(ename) + ON DELETE CASCADE ON UPDATE CASCADE, + UNIQUE(ename, maptype) +); + +CREATE TABLE IF NOT EXISTS object( + ename, objtype TEXT, jsonval TEXT, + FOREIGN KEY(ename) REFERENCES mapitem(ename) + ON DELETE CASCADE ON UPDATE CASCADE, + CHECK (objtype IN ( + 'point', 'multipoint', 'linestring', + 'multilinestring', 'polygon', 'multipolygon')), + UNIQUE(ename, objtype, jsonval) +);