-
Notifications
You must be signed in to change notification settings - Fork 15
/
Copy pathdatabaseschema.psql
260 lines (234 loc) · 6.98 KB
/
databaseschema.psql
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
259
260
CREATE TABLE IF NOT EXISTS actor(
type varchar(50) default '',
id varchar(100) UNIQUE PRIMARY KEY,
name varchar(50) default '',
preferedusername varchar(100) default '',
summary varchar(200) default '',
inbox varchar(100) default '',
outbox varchar(100) default '',
following varchar(100) default '',
followers varchar(100) default '',
restricted boolean default false
);
CREATE TABLE IF NOT EXISTS replies(
id varchar(100),
inreplyto varchar(100)
);
CREATE TABLE IF NOT EXISTS following(
id varchar(100),
following varchar(100)
);
CREATE TABLE IF NOT EXISTS follower(
id varchar(100),
follower varchar(100)
);
CREATE TABLE IF NOT EXISTS verification(
type varchar(50) default '',
identifier varchar(50),
code varchar(50),
created TIMESTAMP default NOW()
);
CREATE TABLE IF NOT EXISTS reported(
id varchar(100),
count int,
board varchar(100),
reason varchar(100)
);
CREATE TABLE IF NOT EXISTS verificationcooldown(
code varchar(50),
created TIMESTAMP default NOW()
);
CREATE TABLE IF NOT EXISTS boardaccess(
identifier varchar(100),
code varchar(50),
board varchar(100),
type varchar(50)
);
CREATE TABLE IF NOT EXISTS crossverification(
verificationcode varchar(50),
code varchar(50)
);
CREATE TABLE IF NOT EXISTS actorauth(
type varchar(50),
board varchar(100)
);
CREATE TABLE IF NOT EXISTS wallet(
id varchar(100) UNIQUE PRIMARY KEY,
type varchar(25),
address varchar(50)
);
CREATE TABLE IF NOT EXISTS activitystream(
actor varchar(100) default '',
attachment varchar(100) default '',
attributedTo varchar(100) default '',
audience varchar(100) default '',
bcc varchar(100) default '',
bto varchar(100) default '',
cc varchar(100) default '',
context varchar(100) default '',
current varchar(100) default '',
first varchar(100) default '',
generator varchar(100) default '',
icon varchar(100) default '',
id varchar(100) UNIQUE PRIMARY KEY,
image varchar(100) default '',
instrument varchar(100) default '',
last varchar(100) default '',
location varchar(100) default '',
items varchar(100) default '',
oneOf varchar(100) default '',
anyOf varchar(100) default '',
closed varchar(100) default '',
origin varchar(100) default '',
next varchar(100) default '',
object varchar(100),
prev varchar(100) default '',
preview varchar(100) default '',
result varchar(100) default '',
tag varchar(100) default '',
target varchar(100) default '',
type varchar(100) default '',
to_ varchar(100) default '',
url varchar(100) default '',
accuracy varchar(100) default '',
altitude varchar(100) default '',
content varchar(2000) default '',
name varchar(100) default '',
alias varchar(100) default '',
duration varchar(100) default '',
height varchar(100) default '',
href varchar(100) default '',
hreflang varchar(100) default '',
partOf varchar(100) default '',
latitude varchar(100) default '',
longitude varchar(100) default '',
mediaType varchar(100) default '',
endTime varchar(100) default '',
published TIMESTAMP default NOW(),
startTime varchar(100) default '',
radius varchar(100) default '',
rel varchar(100) default '',
startIndex varchar(100) default '',
summary varchar(100) default '',
totalItems varchar(100) default '',
units varchar(100) default '',
updated TIMESTAMP default NOW(),
deleted TIMESTAMP default NULL,
width varchar(100) default '',
subject varchar(100) default '',
relationship varchar(100) default '',
describes varchar(100) default '',
formerType varchar(100) default '',
size int default NULL,
public boolean default false,
CONSTRAINT fk_object FOREIGN KEY (object) REFERENCES activitystream(id)
);
CREATE TABLE IF NOT EXISTS cacheactivitystream(
actor varchar(100) default '',
attachment varchar(100) default '',
attributedTo varchar(100) default '',
audience varchar(100) default '',
bcc varchar(100) default '',
bto varchar(100) default '',
cc varchar(100) default '',
context varchar(100) default '',
current varchar(100) default '',
first varchar(100) default '',
generator varchar(100) default '',
icon varchar(100) default '',
id varchar(100) UNIQUE PRIMARY KEY,
image varchar(100) default '',
instrument varchar(100) default '',
last varchar(100) default '',
location varchar(100) default '',
items varchar(100) default '',
oneOf varchar(100) default '',
anyOf varchar(100) default '',
closed varchar(100) default '',
origin varchar(100) default '',
next varchar(100) default '',
object varchar(100),
prev varchar(100) default '',
preview varchar(100) default '',
result varchar(100) default '',
tag varchar(100) default '',
target varchar(100) default '',
type varchar(100) default '',
to_ varchar(100) default '',
url varchar(100) default '',
accuracy varchar(100) default '',
altitude varchar(100) default '',
content varchar(2000) default '',
name varchar(100) default '',
alias varchar(100) default '',
duration varchar(100) default '',
height varchar(100) default '',
href varchar(100) default '',
hreflang varchar(100) default '',
partOf varchar(100) default '',
latitude varchar(100) default '',
longitude varchar(100) default '',
mediaType varchar(100) default '',
endTime varchar(100) default '',
published TIMESTAMP default NOW(),
startTime varchar(100) default '',
radius varchar(100) default '',
rel varchar(100) default '',
startIndex varchar(100) default '',
summary varchar(100) default '',
totalItems varchar(100) default '',
units varchar(100) default '',
updated TIMESTAMP default NOW(),
deleted TIMESTAMP default NULL,
width varchar(100) default '',
subject varchar(100) default '',
relationship varchar(100) default '',
describes varchar(100) default '',
formerType varchar(100) default '',
size int default NULL,
public boolean default false,
CONSTRAINT fk_object FOREIGN KEY (object) REFERENCES cacheactivitystream(id)
);
CREATE TABLE IF NOT EXISTS removed(
id varchar(100),
type varchar(25)
);
ALTER TABLE activitystream ADD COLUMN IF NOT EXISTS tripcode varchar(50) default '';
ALTER TABLE cacheactivitystream ADD COLUMN IF NOT EXISTS tripcode varchar(50) default '';
CREATE TABLE IF NOT EXISTS publicKeyPem(
id varchar(100) UNIQUE,
owner varchar(100),
file varchar(100)
);
CREATE TABLE IF NOT EXISTS newsItem(
title text,
content text,
time bigint
);
ALTER TABLE actor ADD COLUMN IF NOT EXISTS publicKeyPem varchar(100) default '';
ALTER TABLE activitystream ADD COLUMN IF NOT EXISTS sensitive boolean default false;
ALTER TABLE cacheactivitystream ADD COLUMN IF NOT EXISTS sensitive boolean default false;
CREATE TABLE IF NOT EXISTS postblacklist(
id serial primary key,
regex varchar(200)
);
ALTER TABLE actor ADD COLUMN IF NOT EXISTS autosubscribe boolean default false;
CREATE TABLE IF NOT EXISTS bannedmedia(
id serial primary key,
hash varchar(200)
);
CREATE TABLE IF NOT EXISTS inactive(
instance varchar(100) primary key,
timestamp TIMESTAMP default NOW()
);
ALTER TABLE boardaccess ADD COLUMN IF NOT EXISTS label varchar(50) default 'Anon';
CREATE TABLE IF NOT EXISTS sticky(
actor_id varchar(100),
activity_id varchar(100)
);
CREATE TABLE IF NOT EXISTS locked(
actor_id varchar(100),
activity_id varchar(100)
);
ALTER TABLE activitystream ALTER COLUMN content TYPE varchar(4500);
ALTER TABLE cacheactivitystream ALTER COLUMN content TYPE varchar(4500);