/
schema.sql
191 lines (164 loc) · 5.15 KB
/
schema.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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
BEGIN;
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS hstore;
DROP TABLE IF EXISTS webhooks;
CREATE TABLE webhooks (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
actions TEXT[],
url TEXT NOT NULL,
secret TEXT NOT NULL
);
DROP TABLE IF EXISTS meta;
CREATE TABLE meta (
key TEXT PRIMARY KEY,
value JSONB NOT NULL
);
DROP TABLE IF EXISTS tiles;
CREATE TABLE tiles (
created TIMESTAMP NOT NULL,
ref TEXT PRIMARY KEY,
tile BYTEA NOT NULL
);
DROP TABLE IF EXISTS bounds;
CREATE TABLE bounds (
id BIGSERIAL,
geom GEOMETRY(MULTIPOLYGON, 4326) NOT NULL,
name TEXT PRIMARY KEY,
props JSONB NOT NULL
);
CREATE INDEX bounds_gist ON bounds USING GIST(geom);
CREATE INDEX bounds_idx ON bounds(name);
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
access TEXT NOT NULL,
username TEXT UNIQUE NOT NULL,
password TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
meta JSONB
);
DROP TABLE IF EXISTS users_tokens;
CREATE TABLE users_tokens (
name TEXT,
uid BIGINT,
token TEXT PRIMARY KEY,
expiry TIMESTAMP,
scope TEXT
);
DROP TABLE IF EXISTS geo;
DROP INDEX IF EXISTS geo_gist;
DROP INDEX IF EXISTS geo_idx;
CREATE TABLE geo (
id BIGSERIAL UNIQUE,
key TEXT UNIQUE,
version BIGINT NOT NULL,
geom GEOMETRY(GEOMETRY, 4326) NOT NULL,
props JSONB NOT NULL,
deltas BIGINT[]
);
CREATE INDEX geo_gist ON geo USING GIST(geom);
CREATE INDEX geo_idx ON geo(id);
DROP TABLE IF EXISTS geo_history;
CREATE TABLE geo_history (
id BIGINT NOT NULL,
delta BIGINT NOT NULL,
key TEXT,
action TEXT NOT NULL,
version BIGINT NOT NULL,
geom GEOMETRY(GEOMETRY, 4326),
props JSONB,
PRIMARY KEY (id, version)
);
CREATE INDEX geo_history_gist ON geo_history USING GIST(geom);
CREATE INDEX geo_history_idx ON geo_history(id);
CREATE INDEX geo_history_deltax ON geo_history(delta);
DROP TABLE IF EXISTS styles;
CREATE TABLE styles (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
style JSONB NOT NULL,
uid BIGINT NOT NULL,
public BOOLEAN NOT NULL
);
DROP TABLE IF EXISTS deltas;
CREATE TABLE deltas (
id BIGSERIAL PRIMARY KEY,
created TIMESTAMP,
affected BIGINT[],
props JSONB,
uid BIGINT,
finalized BOOLEAN DEFAULT FALSE
);
CREATE INDEX deltas_idx ON deltas(id);
CREATE INDEX deltas_affected_idx on deltas USING GIN (affected);
-- delete_geo( id, version )
CREATE OR REPLACE FUNCTION delete_geo(BIGINT, BIGINT)
RETURNS boolean AS $$
BEGIN
DELETE FROM geo
WHERE
id = $1
AND version = $2;
IF NOT FOUND THEN
RAISE EXCEPTION 'DELETE: ID or VERSION Mismatch';
END IF;
RETURN true;
END;
$$ LANGUAGE plpgsql;
-- modify_geo( geom_str, props_str, delta, id, version, key )
CREATE OR REPLACE FUNCTION modify_geo(TEXT, TEXT, BIGINT, BIGINT, BIGINT, TEXT)
RETURNS boolean AS $$
BEGIN
UPDATE geo
SET
version = version + 1,
geom = ST_SetSRID(ST_GeomFromGeoJSON($1), 4326),
props = $2::TEXT::JSON,
deltas = array_append(deltas, $3::BIGINT),
key = $6
WHERE
id = $4
AND version = $5;
IF NOT FOUND THEN
RAISE EXCEPTION 'MODIFY: ID or VERSION Mismatch';
END IF;
RETURN true;
END;
$$ LANGUAGE plpgsql;
COMMIT;
-- ------- Hecate Read User ----------
DO $$DECLARE count int;
BEGIN
SELECT count(*) INTO count FROM pg_roles WHERE rolname = 'hecate_read';
IF count > 0 THEN
EXECUTE 'REVOKE ALL PRIVILEGES ON "geo" FROM hecate_read;';
EXECUTE 'REVOKE ALL PRIVILEGES ON "deltas" FROM hecate_read;';
EXECUTE 'DROP ROLE IF EXISTS hecate_read;';
END IF;
END$$;
CREATE ROLE hecate_read WITH LOGIN NOINHERIT;
GRANT SELECT ON geo TO hecate_read;
GRANT SELECT ON deltas TO hecate_read;
-- -----------------------------------
-- ------- Hecate User ---------------
DO $$DECLARE
owner TEXT;
count INT;
BEGIN
SELECT count(*) INTO count FROM pg_roles WHERE rolname = 'hecate';
IF count = 0 THEN
EXECUTE 'CREATE ROLE hecate WITH LOGIN NOINHERIT;';
END IF;
EXECUTE 'GRANT ALL PRIVILEGES ON DATABASE hecate TO hecate;';
EXECUTE 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO hecate;';
EXECUTE 'GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO hecate;';
SELECT u.usename INTO owner
FROM pg_database d JOIN pg_user u ON (d.datdba = u.usesysid)
WHERE d.datname = (SELECT current_database());
IF owner != 'hecate' THEN
EXECUTE 'ALTER DATABASE hecate OWNER TO "hecate";';
END IF;
END$$;
-- -----------------------------------