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
|
require "./database"
module Guff
MIGRATIONS = [{
id: "0-null",
sql: %w{},
}, {
id: "1-create-metadata",
sql: [%{
CREATE TABLE metadata (
name TEXT PRIMARY KEY,
value TEXT NOT NULL
)
}],
}, {
id: "2-create-sites",
sql: [%{
CREATE TABLE sites (
-- site id
site_id INTEGER PRIMARY KEY,
-- name of site
name TEXT UNIQUE NOT NULL
CHECK (LENGTH(name) > 0),
-- is this the default site?
is_default BOOLEAN NOT NULL DEFAULT false
)
}, %{
INSERT INTO sites(name, is_default) VALUES ('Default', 1)
}, %{
CREATE TABLE domains (
-- domain
domain TEXT PRIMARY KEY CHECK (
LENGTH(domain) > 0 AND
domain = LOWER(domain)
),
-- site id
site_id INTEGER NOT NULL
REFERENCES sites(site_id)
)
}],
}, {
id: "3-create-posts",
sql: [%{
CREATE TABLE post_states (
state INTEGER PRIMARY KEY,
name TEXT UNIQUE NOT NULL
)
}, %{
INSERT INTO post_states(state, name) VALUES
(0, 'draft'),
(1, 'posted'),
(2, 'deleted')
}, %{
CREATE TABLE posts (
-- unique id
post_id INTEGER PRIMARY KEY,
site_id INTEGER NOT NULL
REFERENCES sites(site_id),
state INTEGER NOT NULL DEFAULT 0
REFERENCES post_states(state),
-- when this post was created
created_at TIMESTAMP WITH TIME ZONE
NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- when this post was posted
-- (that is, the draft status was removed)
posted_at TIMESTAMP WITH TIME ZONE
DEFAULT NULL,
-- title of post
name TEXT NOT NULL
CHECK (LENGTH(name) > 0),
-- slug of post (url fragment)
slug TEXT NOT NULL
CHECK (LENGTH(slug) > 0),
-- raw body (raw text before filters)
raw_body TEXT NOT NULL
CHECK (LENGTH(raw_body) > 0),
-- generated html (after filters)
body TEXT NOT NULL
CHECK (LENGTH(body) > 0)
)
}, %{
CREATE INDEX in_posts_site_id ON posts(site_id)
}, %{
CREATE INDEX in_posts_slug ON posts(slug)
}, %{
CREATE INDEX in_posts_state ON posts(state)
}, %{
CREATE INDEX in_posts_posted_at ON posts(posted_at)
}],
}, {
id: "4-create-tags",
sql: [%{
CREATE TABLE tags (
tag_id INTEGER PRIMARY KEY,
name TEXT UNIQUE NOT NULL
)
}, %{
CREATE INDEX in_tags_name ON tags(name)
}, %{
CREATE TABLE post_tags (
tag_id INTEGER NOT NULL
REFERENCES tags(tag_id),
post_id INTEGER NOT NULL
REFERENCES posts(post_id),
UNIQUE(tag_id, post_id)
)
}, %{
CREATE INDEX in_post_tags_tag_id ON post_tags(tag_id)
}, %{
CREATE INDEX in_post_tags_post_id ON post_tags(post_id)
}],
}, {
id: "5-sessions",
sql: [%{
CREATE TABLE sessions (
session_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 DEFAULT '{}'
CHECK (LENGTH(data) > 2)
)
}, %{
CREATE INDEX in_sessions_sid on sessions(session_id)
}],
}, {
id: "6-roles",
sql: [%{
CREATE TABLE roles (
-- unique identifier
role_id INTEGER PRIMARY KEY,
-- user-visible name of role
role_name TEXT UNIQUE NOT NULL,
-- brief description of role
role_desc TEXT NOT NULL
CHECK (LENGTH(role_desc) > 0)
)
}, %{
INSERT INTO roles(role_id, role_name, role_desc) VALUES
(0, 'guest', 'Guest account, no login.'),
(1, 'viewer', 'Login and read-only access.'),
(2, 'editor', 'Can create and edit posts.'),
(3, 'admin', 'Can create and edit posts and modify site.')
}],
}, {
id: "7-users",
sql: [%{
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
-- when was this user created?
created_at TIMESTAMP WITH TIME ZONE
NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- is this user active?
is_active BOOLEAN NOT NULL DEFAULT false,
-- role of this user
role_id INTEGER NOT NULL DEFAULT 0
REFERENCES roles(role_id),
-- display name (not the same as their login)
user_name TEXT UNIQUE NOT NULL
CHECK (LENGTH(user_name) > 0)
)
}, %{
INSERT INTO users(user_id, is_active, role_id, user_name) VALUES
(0, 1, (SELECT role_id FROM roles WHERE role_name = 'guest'), 'Guest'),
(1, 1, (SELECT role_id FROM roles WHERE role_name = 'admin'), 'Admin')
}],
}, {
id: "8-user-logins",
sql: [%{
CREATE TABLE user_logins (
user_id INTEGER UNIQUE NOT NULL
REFERENCES users(user_id),
-- email address of user
email TEXT UNIQUE NOT NULL
CHECK (email LIKE '%_@_%'),
-- bcrypt hash of password
pass_hash TEXT NOT NULL
)
}, %{
CREATE INDEX in_user_logins_email ON user_logins(email)
}],
}]
end
|
