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
|
--*- mode: sql; coding: utf-8 -*--
BEGIN TRANSACTION;
CREATE TABLE users(
id SERIAL NOT NULL,
first_name VARCHAR,
middle_names VARCHAR,
last_name VARCHAR,
email VARCHAR,
website VARCHAR,
status VARCHAR,
PRIMARY KEY(id),
CHECK (status IN ('admin', 'approved', 'visitor', 'disabled'))
);
CREATE TABLE openids(
"user" INTEGER NOT NULL,
openid VARCHAR NOT NULL,
PRIMARY KEY(openid),
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 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 bookmarks(
id SERIAL NOT NULL,
owner INTEGER,
date TIMESTAMP WITH TIME ZONE DEFAULT now(),
uri VARCHAR NOT NULL,
title VARCHAR,
description VARCHAR,
visibility VARCHAR,
PRIMARY KEY(id),
FOREIGN KEY(owner) REFERENCES users,
CHECK (visibility IN ('private', 'protected', 'public'))
);
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(
id SERIAL NOT NULL,
author INTEGER,
date TIMESTAMP WITH TIME ZONE DEFAULT now(),
content VARCHAR,
visibility VARCHAR NOT NULL,
format VARCHAR NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(author) REFERENCES users,
CHECK (format IN ('markdown')),
CHECK (visibility IN ('personal', 'protected', 'public'))
);
CREATE TABLE lazychat_targets(
message INTEGER NOT NULL,
target INTEGER NOT NULL,
PRIMARY KEY(message, target),
FOREIGN KEY(message) REFERENCES lazychat_messages,
FOREIGN KEY(target) REFERENCES users
);
CREATE TABLE lazychat_references(
referrer INTEGER NOT NULL,
referee INTEGER NOT NULL,
PRIMARY KEY(referrer, referee),
FOREIGN KEY(referrer) REFERENCES lazychat_messages,
FOREIGN KEY(referee) REFERENCES lazychat_messages
);
ROLLBACK;
--COMMIT;
|
