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
|
---
sql:
init:
- |
CREATE TABLE sites (
site_id INTEGER PRIMARY KEY,
name TEXT UNIQUE NOT NULL
CHECK (LENGTH(name) > 0),
is_active BOOLEAN NOT NULL DEFAULT false,
is_default BOOLEAN NOT NULL DEFAULT false
)
- |
INSERT INTO sites(site_id, name, is_active, is_default) VALUES
(1, 'default', 1, 1)
- |
CREATE TABLE site_domains (
site_id INTEGER NOT NULL
REFERENCES sites(site_id),
domain TEXT UNIQUE NOT NULL CHECK (
LENGTH(domain) > 0 AND
domain = LOWER(domain) AND
domain NOT LIKE '% %'
)
)
- |
CREATE TABLE roles (
role_id INTEGER PRIMARY KEY,
-- internal role name
role TEXT UNIQUE NOT NULL CHECK (
LENGTH(role) > 0 AND
role = LOWER(role)
),
-- user-visible role name
name TEXT UNIQUE NOT NULL
CHECK (LENGTH(name) > 0),
sort INTEGER UNIQUE NOT NULL
)
- |
INSERT INTO roles(role_id, role, name, sort) VALUES
(1, 'viewer', 'Viewer', 1),
(2, 'editor', 'Editor', 2),
(3, 'admin', 'Admin', 3)
- |
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
role_id INTEGER NOT NULL
REFERENCES roles(role_id),
name TEXT UNIQUE NOT NULL,
email TEXT UNIQUE NOT NULL CHECK (
LENGTH(email) > 0 AND
email LIKE '%@%'
),
password TEXT NOT NULL DEFAULT '',
is_active BOOLEAN NOT NULL DEFAULT false
)
- |
CREATE TABLE states (
state_id INTEGER PRIMARY KEY,
-- internal state name
state TEXT UNIQUE NOT NULL CHECK (
LENGTH(state) > 0 AND
state = LOWER(state)
),
icon TEXT UNIQUE NOT NULL CHECK (
LENGTH(state) > 0 AND
state = LOWER(state)
),
-- user-visible state name
name TEXT UNIQUE NOT NULL
CHECK (LENGTH(name) > 0),
sort INTEGER UNIQUE NOT NULL
)
- |
INSERT INTO states(state_id, state, icon, name, sort) VALUES
(1, 'draft', 'fa-wrench', 'Draft', 1),
(2, 'public', 'fa-bullhorn', 'Public', 2),
(3, 'deleted', 'fa-trash', 'Deleted', 3)
- |
CREATE TABLE posts (
post_id INTEGER PRIMARY KEY,
site_id INTEGER NOT NULL
REFERENCES sites(site_id),
created_at TIMESTAMP WITH TIME ZONE NOT NULL
DEFAULT CURRENT_TIMESTAMP,
created_by INTEGER NOT NULL
REFERENCES users(user_id),
state_id INTEGER NOT NULL
REFERENCES states(state_id),
posted_at TIMESTAMP WITH TIME ZONE,
expires_at TIMESTAMP WITH TIME ZONE,
name TEXT NOT NULL DEFAULT '',
slug TEXT NOT NULL DEFAULT '' CHECK (
slug NOT LIKE '% %' AND
slug = LOWER(slug)
),
slug_lock BOOLEAN NOT NULL DEFAULT 1,
body TEXT NOT NULL DEFAULT ''
)
- |
CREATE INDEX in_posts_site_id ON posts(site_id)
- |
CREATE VIRTUAL TABLE posts_fts USING fts4(
name,
slug,
body
)
- |
CREATE TABLE blogs (
post_id INTEGER PRIMARY KEY
REFERENCES posts(post_id)
)
- |
CREATE TABLE projects (
post_id INTEGER PRIMARY KEY
REFERENCES posts(post_id),
repo_url TEXT NOT NULL DEFAULT ''
CHECK (repo_url NOT LIKE '% %')
)
- |
CREATE TABLE layouts (
layout_id INTEGER PRIMARY KEY,
-- internal layout name
layout TEXT UNIQUE NOT NULL CHECK (
LENGTH(layout) > 0 AND
layout = LOWER(layout)
),
-- user-visible layout name
layout_name TEXT UNIQUE NOT NULL
CHECK (LENGTH(layout_name) > 0),
is_default BOOLEAN NOT NULL
)
- |
INSERT INTO layouts(layout_id, layout, layout_name, is_default) VALUES
(1, 'blank', 'Blank', 0),
(2, 'default', 'Default', 1)
- |
CREATE TABLE pages (
post_id INTEGER PRIMARY KEY
REFERENCES posts(post_id),
layout_id INTEGER NOT NULL
REFERENCES layouts(layout_id)
)
- |
CREATE TABLE sessions (
id TEXT PRIMARY KEY,
created_at TIMESTAMP WITH TIME ZONE NOT NULL
DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE NOT NULL
DEFAULT CURRENT_TIMESTAMP,
data TEXT NOT NULL
)
add_user: |
INSERT INTO users(name, email, password, role_id, is_active) VALUES
(?, ?, ?, (SELECT role_id FROM roles WHERE role = ?), 1)
test_posts:
- |
INSERT INTO posts (
post_id,
site_id,
created_by,
state_id,
posted_at,
name,
slug,
body
) VALUES (
1,
1,
1,
(SELECT state_id FROM states WHERE state = 'public'),
CURRENT_TIMESTAMP,
'Test Page',
'test-page',
'This is the body of a test page.'
), (
2,
1,
1,
(SELECT state_id FROM states WHERE state = 'public'),
CURRENT_TIMESTAMP,
'Test Project',
'test-project',
'This is the body of a test project.'
), (
3,
1,
1,
(SELECT state_id FROM states WHERE state = 'public'),
CURRENT_TIMESTAMP,
'Test Blog',
'test-blog',
'This is the body of a test blog entry.'
)
- |
INSERT INTO posts_fts(rowid, name, slug, body)
SELECT post_id, name, slug, body FROM posts
- |
INSERT INTO pages(post_id, layout_id) VALUES (
1,
(SELECT layout_id FROM layouts WHERE layout = 'default')
)
- |
INSERT INTO projects(post_id) VALUES (2)
- |
INSERT INTO blogs(post_id) VALUES (3)
|
