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
|
# 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
ROW = 100
DONE = 101
type Database = Void*
type Statement = Void*
fun errstr = sqlite3_errstr(rc : LibC::Int) : LibC::Char*
fun open = sqlite3_open(filename : LibC::Char*, db : Database*) : LibC::Int
fun prepare = sqlite3_prepare(db : Database, query : LibC::Char*,
length : LibC::Int, stmt : Statement*,
query_tail : LibC::Char**) : LibC::Int
fun step = sqlite3_step(stmt : Statement) : Int32
fun column_text = sqlite3_column_text(stmt : Statement,
col : LibC::Int) : LibC::Char*
fun column_int = sqlite3_column_int(stmt : Statement,
col : LibC::Int) : LibC::Int
fun finalize = sqlite3_finalize(stmt : Statement) : LibC::Int
fun close = sqlite3_close(db : Database) : LibC::Int
end
class Database
MIGRATIONS = [""] # migration #0 is reserved for schema initialization
SCHEMA = "CREATE TABLE member (
id INTEGER PRIMARY KEY,
nick TEXT NOT NULL UNIQUE,
opennic TEXT NOT NULL UNIQUE,
icann TEXT NOT NULL UNIQUE)";
class Statement
def initialize(db, query)
bytes = query.to_slice
Database.check SQLite.prepare db, bytes, bytes.size,
out @ref, out @tail
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 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)
Database.check SQLite.open path, out @ref
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
if version == 0
self.exec SCHEMA do end
self.exec "PRAGMA user_version = #{MIGRATIONS.size}" do end
end
rescue ex
self.finalize
raise ex
end
end
def exec(query : String)
stmt = Statement.new @ref, query
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 members
result = [] of Tuple(String, String, String)
self.exec "SELECT nick, opennic, icann FROM member" do |row|
result << {row[0].text, row[1].text, row[2].text}
end
result
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
|
