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
|
require "./database"
module Guff
class DatabaseUpdater
CURRENT_VERSION = 2_i64
SQL = {
:get_version => "
SELECT value
FROM metadata
WHERE name = 'version'
",
}
MIGRATION_IDS = %w{
0-null
1-create-tables
2-create-indices
}
MIGRATIONS = {
"1-create-tables": {
backup: false,
sql: [%{
CREATE TABLE metadata (
name TEXT PRIMARY KEY,
value TEXT NOT NULL
)
}, %{
INSERT INTO metadata(name, value) VALUES ('version', '1')
}, %{
CREATE TABLE tags (
tag_id INTEGER PRIMARY KEY,
name TEXT UNIQUE NOT NULL
)
}, %{
CREATE TABLE posts (
-- unique id
post_id INTEGER PRIMARY KEY,
-- false if this post has been deleted
is_active BOOLEAN NOT NULL
DEFAULT true,
-- 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
NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- title of post
name TEXT NOT NULL
CHECK (LENGTH(name) > 0)
-- slug of post (url fragment)
slug TEXT NOT NULL
CHECK (LENGTH(slug) > 0)
-- body (raw text before filters)
body TEXT NOT NULL
CHECK (LENGTH(body) > 0)
-- generated html (after filters)
html TEXT NOT NULL
CHECK (LENGTH(html) > 0)
)
}],
},
"2-create-indices": {
backup: false,
sql: [%{
CREATE INDEX ON tags(name)
}, %{
CREATE INDEX ON posts(slug)
}, %{
UPDATE metadata
SET value = '2'
WHERE name = 'version'
}],
},
}
def self.run(path, config)
new(path, config).run
end
def initialize(
@path : String,
@config : Config
)
@db = Database.new(path)
end
def run
version = 0_i64
if @db.table_exists?("metadata")
version = @db.one(SQL[:get_version]) as Int64
end
puts "versions: db = %d, code = %d" % [
version,
CURRENT_VERSION,
]
if version < CURRENT_VERSION
(version + 1).upto(CURRENT_VERSION) do |v|
migrate_to(v)
end
end
end
private def migrate_to(version : Int64)
id = MIGRATION_IDS[version]
m = MIGRATIONS[id]
puts "migrating: %s" % [id]
(m[:sql] as Array(String)).each do |sql|
@db.query(sql)
end
end
private def get_version
@db.one(:get_version) as Int64
end
end
end
|
