summaryrefslogtreecommitdiff
path: root/schema.sql
blob: ad773af30509be21f234697a34e7a1901c9f2a07 (plain)
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
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
--*- mode: sql; coding: utf-8 -*--

BEGIN TRANSACTION;

CREATE TABLE roles(
  id SERIAL NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE users(
  id           SERIAL    NOT NULL,
  first_name   VARCHAR,
  middle_names VARCHAR,
  last_name    VARCHAR,
  email        VARCHAR,
  website      VARCHAR,
  status       VARCHAR,
  "role"       INTEGER   NOT NULL,
  PRIMARY KEY(id),
  CHECK (status IN ('admin', 'approved', 'visitor', 'disabled')),
  FOREIGN KEY("role") REFERENCES roles
);

CREATE TABLE openids(
  "user"       INTEGER   NOT NULL,
  openid       VARCHAR   NOT NULL,
  PRIMARY KEY(openid),
  FOREIGN KEY("user") REFERENCES users
);

CREATE TABLE webids(
  "user"       INTEGER   NOT NULL,
  webid        VARCHAR   NOT NULL,
  PRIMARY KEY(webid),
  FOREIGN KEY("user") REFERENCES users
);

CREATE TABLE rsa_keys(
  modulus  NUMERIC   NOT NULL,
  exponent NUMERIC   NOT NULL,
  PRIMARY KEY(modulus, exponent)
);

CREATE TABLE user_rsa_keys(
  "user"   INTEGER   NOT NULL,
  modulus  NUMERIC   NOT NULL,
  exponent NUMERIC   NOT NULL,
  PRIMARY KEY("user", modulus, exponent),
  FOREIGN KEY("user") REFERENCES users,
  FOREIGN KEY(modulus, exponent) REFERENCES rsa_keys
);

CREATE TABLE user_email_addresses(
  "user"       INTEGER   NOT NULL,
  email        VARCHAR   NOT NULL,
  PRIMARY KEY(email),
  FOREIGN KEY("user") REFERENCES users
);

CREATE TABLE user_nicknames(
  "user"   INTEGER NOT NULL,
  nickname VARCHAR NOT NULL,
  PRIMARY KEY(nickname),
  FOREIGN KEY("user") REFERENCES users
);
CREATE INDEX user_nicknames_user ON user_nicknames ("user");

CREATE TABLE user_jids(
  "user" INTEGER NOT NULL,
  jid    VARCHAR NOT NULL,
  PRIMARY KEY("user", jid),
  FOREIGN KEY("user") REFERENCES users
);
CREATE INDEX user_jids_user ON user_jids ("user");

CREATE TABLE role_subroles(
  "superrole" INTEGER NOT NULL,
  "subrole"   INTEGER NOT NULL,
  PRIMARY KEY("superrole", "subrole"),
  FOREIGN KEY("superrole") REFERENCES roles,
  FOREIGN KEY("subrole") REFERENCES roles
);

CREATE TABLE user_roles(
  "user" INTEGER NOT NULL,
  "role" INTEGER NOT NULL,
  PRIMARY KEY("user", "role"),
  FOREIGN KEY("user") REFERENCES users,
  FOREIGN KEY("role") REFERENCES roles
);

CREATE TABLE page_keys(
  "user" INTEGER NOT NULL,
  page   VARCHAR NOT NULL,
  "key"  DECIMAL NOT NULL,   -- (~ NUMERIC DECIMAL)
  PRIMARY KEY(page, "key"),
  FOREIGN KEY("user") REFERENCES users
);

CREATE TABLE wiki_pages(
  id           SERIAL    NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE wiki_page_revisions(
  id           SERIAL    NOT NULL,
  page         INTEGER   NOT NULL,
  date         TIMESTAMP WITH TIME ZONE DEFAULT now(),
  title        VARCHAR,
  content      VARCHAR,
  author       INTEGER,
  format       VARCHAR,
  PRIMARY KEY(id),
  FOREIGN KEY(page) REFERENCES wiki_pages,
  FOREIGN KEY(author) REFERENCES users,
  CHECK (format IN ('mulkwiki', 'html5', 'xhtml5', 'markdown', 'textile', 'muse', 'bbcode'))
);


CREATE TABLE posts(
  id          SERIAL    NOT NULL,
  owner       INTEGER,
  date        TIMESTAMP WITH TIME ZONE DEFAULT now(),
  PRIMARY KEY(id),
  FOREIGN KEY(owner) REFERENCES users
);

CREATE TABLE bookmarks INHERITS posts (
  uri         VARCHAR   NOT NULL,
  title       VARCHAR,
  description VARCHAR,
  PRIMARY KEY(id),
  FOREIGN KEY(owner) REFERENCES users
);

CREATE TABLE bookmark_tags(
  bookmark INTEGER NOT NULL,
  tag      VARCHAR NOT NULL,
  PRIMARY KEY(bookmark, tag),
  FOREIGN KEY(bookmark) REFERENCES bookmarks
);

CREATE TABLE lazychat_messages INHERITS posts (
  content     VARCHAR,
  format      VARCHAR NOT NULL,
  PRIMARY KEY(id),
  CHECK (format IN ('markdown'))
);

CREATE TABLE post_targets(
  message INTEGER NOT NULL,
  post    INTEGER NOT NULL,
  PRIMARY KEY(message, target),
  --FOREIGN KEY(message) REFERENCES posts AND CHILDREN,
  FOREIGN KEY(target)  REFERENCES roles
);

CREATE TABLE lazychat_references(
  referrer INTEGER NOT NULL,
  referee  INTEGER NOT NULL,
  PRIMARY KEY(referrer, referee),
  --FOREIGN KEY(referee)  REFERENCES posts AND CHILDREN,
  FOREIGN KEY(referrer) REFERENCES lazychat_messages
);

CREATE TABLE user_default_target(
  "user" INTEGER NOT NULL,
  target INTEGER NOT NULL,
  PRIMARY KEY("user", target),
  FOREIGN KEY("user") REFERENCES users,
  FOREIGN KEY(target) REFERENCES roles
);

CREATE VIEW effective_role_subroles AS
  WITH RECURSIVE t(superrole, subrole) AS (
      SELECT id, id
        FROM roles
    UNION
      SELECT t.superrole, rs.subrole
        FROM t t
       INNER JOIN role_subroles rs
          ON (rs.superrole = t.subrole)
  )
  SELECT * FROM t;

CREATE VIEW effective_user_roles AS
    (SELECT ur."user", er.subrole AS role
       FROM user_roles ur
      INNER JOIN effective_role_subroles er
         ON (er.superrole = ur.role))
  UNION
    (SELECT u.id AS "user", rt.role FROM users u, role_tags rt WHERE rt.tag IN ('everyone', 'world'))
  UNION
    (SELECT NULL, rt.role FROM role_tags rt WHERE rt.tag = 'world');

CREATE FUNCTION new_user_put_in_universal_role() RETURNS TRIGGER AS $$
DECLARE
  universal_role INTEGER;
BEGIN
  SELECT "role" FROM role_tags WHERE tag = 'everyone' INTO universal_role;
  INSERT INTO user_roles VALUES (NEW.id, universal_role);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER new_user_put_in_universal_role
  AFTER INSERT ON users
  FOR EACH ROW
  EXECUTE PROCEDURE put_new_user_in_universal_role();

CREATE FUNCTION new_user_put_user_in_user_role() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO user_roles VALUES (NEW."role", user_role);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER new_user_put_user_in_user_role
  AFTER INSERT ON users
  FOR EACH ROW
  EXECUTE PROCEDURE new_user_put_user_in_user_role();

CREATE FUNCTION new_user_create_user_role() RETURNS TRIGGER AS $$
DECLARE
  new_role INTEGER;
BEGIN
  INSERT INTO roles("name") VALUES (concat(NEW.first_name, ' ', NEW.last_name))
    RETURNING id INTO STRICT new_role;
  UPDATE users SET "role" = new_role WHERE id = NEW.id;
  NEW.role := new_role;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER new_user_create_user_role
  BEFORE INSERT ON users
  FOR EACH ROW
  EXECUTE PROCEDURE new_user_create_user_role();

CREATE VIEW user_visible_posts AS
   SELECT eur.user, t.message
     FROM effective_user_roles eur, post_targets t
    WHERE t.target = eur.role
  UNION
    SELECT m.owner, m.message
    FROM posts m;

CREATE VIEW user_visible_lazychat_messages AS
  SELECT uvp.user, uvp.message
    FROM user_visible_posts uvp
    INNER JOIN lazychat_messages lm ON lm.id = uvp.message;

CREATE VIEW user_visible_bookmarks AS
  SELECT uvp.user, uvp.message
    FROM user_visible_posts uvp
    INNER JOIN bookmarks bm ON bm.id = uvp.message;

ROLLBACK;
--COMMIT;