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
|
# SQLite3 wrapper
# Copyright (C) 2023 Nguyễn Gia Phong
#
# This file if part of hybring.
#
# Hybring is free software: you can redistribute it and/or modify
# it under the terms of the GNU Affero General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# Hybring is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU Affero General Public License for more details.
#
# You should have received a copy of the GNU Affero General Public License
# along with hybring. If not, see <https://www.gnu.org/licenses/>.
@[Link("sqlite3")]
lib SQLite
OK = 0
BUSY = 5
ROW = 100
DONE = 101
enum UpdateAction : LibC::Int
Delete = 9
Insert = 18
Update = 23
end
type Database = Void*
type Statement = Void*
fun errstr = sqlite3_errstr(rc : LibC::Int) : LibC::Char*
fun mprintf = sqlite3_mprintf(format : LibC::Char*, ...) : LibC::Char*
fun free = sqlite3_free(format : Void*, ...)
fun open = sqlite3_open(filename : LibC::Char*, db : Database*) : LibC::Int
fun close = sqlite3_close_v2(db : Database) : LibC::Int
fun update_hook = sqlite3_update_hook(db : Database,
f : (Void*, UpdateAction, LibC::Char*,
LibC::Char*, Int64 ->),
arg : Void*)
fun prepare = sqlite3_prepare_v2(db : Database, query : LibC::Char*,
length : LibC::Int, stmt : Statement*,
query_tail : LibC::Char**) : LibC::Int
fun finalize = sqlite3_finalize(stmt : Statement) : LibC::Int
fun step = sqlite3_step(stmt : Statement) : Int32
fun column_int = sqlite3_column_int(stmt : Statement,
col : LibC::Int) : LibC::Int
fun column_int64 = sqlite3_column_int64(stmt : Statement,
col : LibC::Int) : Int64
fun column_text = sqlite3_column_text(stmt : Statement,
col : LibC::Int) : LibC::Char*
end
MIGRATIONS = [
["CREATE TABLE member (nick TEXT NOT NULL UNIQUE, -- de facto primary
opennic TEXT NOT NULL UNIQUE,
icann TEXT NOT NULL UNIQUE,
official INTEGER NOT NULL DEFAULT 0,
left REFERENCES member(nick),
right REFERENCES member(nick))",
"CREATE TABLE change (type INTEGER, -- enum
nick REFERENCES member(nick) ON DELETE CASCADE,
time TEXT)"],
]
class Database
alias UpdateAction = SQLite::UpdateAction
class Statement
def initialize(db, query)
Database.check SQLite.prepare db, query, LibC.strlen(query),
out @ref, out _
end
def step : LibC::Int
SQLite.step @ref
end
def row
Row.new @ref
end
def finalize
Database.check SQLite.finalize @ref
end
end
class Column
def initialize(stmt : SQLite::Statement, i : LibC::Int)
@stmt = stmt
@i = i
end
def int
SQLite.column_int @stmt, @i
end
def int64
SQLite.column_int64 @stmt, @i
end
def text
String.new SQLite.column_text @stmt, @i
end
end
class Row
def initialize(stmt : SQLite::Statement)
@stmt = stmt
end
def [](i : LibC::Int)
Column.new @stmt, i
end
end
def initialize(path)
Dir.mkdir_p path.parent
Database.check SQLite.open path.to_s, out @ref
self.exec "PRAGMA foreign_keys = ON" do end
end
def initialize(path, opennic, icann)
Dir.mkdir_p path.parent
Database.check SQLite.open path.to_s, out @ref
self.exec "PRAGMA foreign_keys = ON" do end
self.exec "PRAGMA user_version" do |row|
version = row[0].int
raise "negative schema version" if version < 0
raise "schema version newer than supported" if version > MIGRATIONS.size
self.transact do
MIGRATIONS[version..].each do |migration|
migration.each do |sql|
self.exec sql do end
end
end
self.exec "PRAGMA user_version = %u", MIGRATIONS.size do end
self.exec "INSERT INTO member (nick, opennic, icann, official,
left, right)
VALUES (%Q, %Q, %Q, 1, %Q, %Q)",
"self", opennic, icann, "self", "self" do
end if version == 0 # avoid out-of-bound when looking for neighbors
end
rescue ex
self.finalize
raise ex
end
end
def update_hook(callback, arg)
SQLite.update_hook @ref, callback, arg
end
def exec(query : String, *values)
sql = SQLite.mprintf query, *values
stmt = Statement.new @ref, sql
SQLite.free sql
loop do
rc = stmt.step
case rc
when SQLite::ROW
yield stmt.row
when SQLite::DONE
break
when SQLite::BUSY
next # FIXME: rollback transaction
else
Database.check rc
end
end
end
def transact
self.exec "BEGIN" do end
yield
self.exec "COMMIT" do end
end
def add_applicant(nick, opennic, icann)
self.exec "INSERT INTO member (nick, opennic, icann, left, right)
SELECT %Q, %Q, %Q, nick, left
FROM member
WHERE official = 1
ORDER BY rowid ASC
LIMIT 1",
nick, opennic, icann do end
end
def finalize
Database.check SQLite.close @ref
end
end
def Database.check(rc : LibC::Int)
raise String.new SQLite.errstr rc if rc != SQLite::OK
end
|
