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
|
import sqlite3
import click
from flask import current_app, g
from flask.cli import with_appcontext
def get_db():
if 'db' not in g:
g.db = sqlite3.connect(
current_app.config['DATABASE'],
detect_types=sqlite3.PARSE_DECLTYPES
)
g.db.row_factory = sqlite3.Row
g.db.execute("PRAGMA foreign_keys = ON;")
return g.db
def close_db(e=None):
db = g.pop('db', None)
if db is not None:
db.close()
migrations = [
"""
CREATE TABLE users (
username TEXT PRIMARY KEY,
password TEXT NOT NULL
);""",
"""
CREATE TABLE threads (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
creator TEXT NOT NULL REFERENCES users(username),
created TIMESTAMP NOT NULL,
updated TIMESTAMP NOT NULL
);
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
content TEXT,
thread INTEGER NOT NULL REFERENCES threads(id),
author TEXT NOT NULL REFERENCES users(username),
created TIMESTAMP NOT NULL
);
CREATE INDEX posts_thread_idx ON posts (thread);
""",
"""
ALTER TABLE posts ADD COLUMN edited INT NOT NULL DEFAULT 0;
ALTER TABLE posts ADD COLUMN updated TIMESTAMP;
""",
"""
CREATE VIRTUAL TABLE posts_fts USING fts5(
content,
content=posts,
content_rowid=id,
tokenize='porter unicode61 remove_diacritics 2'
);
INSERT INTO posts_fts (rowid, content) SELECT id, content FROM posts;
CREATE TRIGGER posts_ai AFTER INSERT ON posts BEGIN
INSERT INTO posts_fts(rowid, content) VALUES (new.id, new.content);
END;
CREATE TRIGGER posts_ad AFTER DELETE ON posts BEGIN
INSERT INTO posts_fts(posts_fts, rowid, content) VALUES('delete', old.id, old.content);
END;
CREATE TRIGGER posts_au AFTER UPDATE ON posts BEGIN
INSERT INTO posts_fts(posts_fts, rowid, content) VALUES('delete', old.id, old.content);
INSERT INTO posts_fts(rowid, content) VALUES (new.id, new.content);
END;
""",
"""
CREATE TABLE tags (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
text_colour TEXT NOT NULL,
bg_colour TEXT NOT NULL
);
CREATE TABLE thread_tags (
thread INTEGER NOT NULL REFERENCES threads(id),
tag INTEGER NOT NULL REFERENCES tags(id)
);
""",
"""CREATE INDEX thread_tags_thread ON thread_tags (thread);""",
"""ALTER TABLE users ADD COLUMN admin INT NOT NULL DEFAULT 0""",
"""
ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users ADD COLUMN joined TIMESTAMP;
""",
"""
CREATE TABLE forums (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
parent INTEGER REFERENCES forums(id),
description TEXT
);
INSERT INTO forums (name,parent,description) values ('apioforum',null,'the default root forum');
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;
CREATE TABLE threads_new (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
creator TEXT NOT NULL,
created TIMESTAMP NOT NULL,
updated TIMESTAMP NOT NULL,
forum NOT NULL REFERENCES forums(id)
);
INSERT INTO threads_new (id,title,creator,created,updated,forum)
SELECT id,title,creator,created,updated,1 FROM threads;
DROP TABLE threads;
ALTER TABLE threads_new RENAME TO threads;
COMMIT;
PRAGMA foreign_keys = on;
""",
"""
CREATE VIEW most_recent_posts AS
SELECT max(id), * FROM posts GROUP BY thread;
CREATE VIEW number_of_posts AS
SELECT thread, count(*) AS num_replies FROM posts GROUP BY thread;
""",
"""
CREATE TABLE role_config (
role TEXT NOT NULL,
forum NOT NULL REFERENCES forums(id),
id INTEGER PRIMARY KEY,
p_create_threads INT NOT NULL DEFAULT 1,
p_reply_threads INT NOT NULL DEFAULT 1,
p_view_threads INT NOT NULL DEFAULT 1,
p_manage_threads INT NOT NULL DEFAULT 0,
p_vote INT NOT NULL DEFAULT 1,
p_create_polls INT NOT NULL DEFAULT 1,
p_approve INT NOT NULL DEFAULT 0,
p_create_subforum INT NOT NULL DEFAULT 0
);
INSERT INTO role_config (role,forum) VALUES ("approved",1);
INSERT INTO role_config (role,forum) VALUES ("other",1);
""",
"""
CREATE TABLE role_assignments (
user NOT NULL REFERENCES users(username),
forum NOT NULL REFERENCES forums(id)
);
"""
]
def init_db():
db = get_db()
version = db.execute("PRAGMA user_version;").fetchone()[0]
for i in range(version, len(migrations)):
db.executescript(migrations[i])
db.execute(f"PRAGMA user_version = {i+1}")
db.commit()
click.echo(f"migration {i}")
@click.command("migrate")
@with_appcontext
def migrate_command():
"""update database scheme etc"""
init_db()
click.echo("ok")
def init_app(app):
app.teardown_appcontext(close_db)
app.cli.add_command(migrate_command)
|
