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
|
# 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
DELETE = 9
INSERT = 18
UPDATE = 23
ROW = 100
DONE = 101
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(db : Database) : LibC::Int
fun update_hook = sqlite3_update_hook(db : Database,
f : (Void*, LibC::Int, 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
class Database
MIGRATIONS = [
["CREATE TABLE member (id INTEGER PRIMARY KEY,
nick TEXT NOT NULL UNIQUE,
opennic TEXT NOT NULL UNIQUE,
icann TEXT NOT NULL UNIQUE)",
"CREATE TABLE applicant (id INTEGER PRIMARY KEY,
nick TEXT NOT NULL UNIQUE,
opennic TEXT NOT NULL UNIQUE,
icann TEXT NOT NULL UNIQUE)"]]
SELECT_MEMBER_ROW = "SELECT nick, opennic, icann FROM member
WHERE rowid = %lli"
SELECT_APPLICANT_ROW = "SELECT nick, opennic, icann FROM applicant
WHERE rowid = %lli"
INSERT_MEMBER = "INSERT INTO member (nick, opennic, icann)
VALUES (%Q, %Q, %Q)"
INSERT_APPLICANT = "INSERT INTO applicant (nick, opennic, icann)
VALUES (%Q, %Q, %Q)"
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 : String, opennic, icann)
Database.check SQLite.open path, out @ref
@members = {} of Int64 => {String, String, String}
@applicants = {} of Int64 => {String, String, String}
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
if version == 0 # avoid out-of-bound when looking for neighbors
self.exec INSERT_MEMBER, "self", opennic, icann do end
end
end
rescue ex
self.finalize
raise ex
end
begin
self.exec "SELECT rowid, nick, opennic, icann FROM member" do |row|
@members[row[0].int64] = {row[1].text, row[2].text, row[3].text}
end
self.exec "SELECT rowid, nick, opennic, icann FROM applicant" do |row|
@applicants[row[0].int64] = {row[1].text, row[2].text, row[3].text}
end
rescue ex
self.finalize
raise ex
end
SQLite.update_hook @ref, ->(arg, action, db, table, rowid) {
return unless db == "main"
obj = arg.as Database
case table
when "member"
case action
when SQLite::DELETE
obj.members.delete rowid
when SQLite::INSERT, SQLite::UPDATE
obj.exec SELECT_MEMBER_ROW, rowid do |row|
obj.members[rowid] = {row[0].text, row[1].text, row[2].text}
end
end
when "applicant"
case action
when SQLite::DELETE
obj.applicants.delete rowid
when SQLite::INSERT, SQLite::UPDATE
obj.exec SELECT_APPLICANT_ROW, rowid do |row|
obj.applicants[rowid] = {row[0].text, row[1].text, row[2].text}
end
end
end
}, self.as Void*
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
else
Database.check rc
end
end
end
def transact
self.exec "BEGIN TRANSACTION" do end
yield
self.exec "COMMIT" do end
end
def members
@members
end
def applicants
@applicants
end
def add_applicant(nick, opennic, icann)
self.exec INSERT_APPLICANT, 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
|
