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
|
require "sqlite3"
module Guff
class Database < ::SQLite3::Database
SQL = {
table_exists: "
SELECT name
FROM sqlite_master
WHERE type = 'table'
AND name = ?
",
}
def table_exists?(table : String) : Bool
one(SQL[:table_exists], [table]) == table
end
def one(
sql : String,
args = nil : Array(String) | Hash(String, String) | Nil
)
r = nil
run(sql, args) do |rs|
if rs.next
r = rs[0] as String
end
end
# return result
r
end
def row(
sql : String,
args = nil : Array(String) | Hash(String, String) | Nil
)
r = nil
# exec query
run(sql, args) do |rs|
r = to_row(rs) if rs.next
end
# return result
r
end
def all(
sql : String,
args = nil : Array(String) | Hash(String, String) | Nil,
&block : Proc(Hash(String, ::SQLite3::Value), Nil) \
)
# build statement
run(sql, args) do |rs|
# walk results
while rs.next
# build row and pass it to callback
block.call(to_row(rs))
end
end
nil
end
def query(
sql : String
)
run(sql, nil) do |rs|
# make sure query executes
rs.next
nil
end
end
#
# NOTE: if you pass a block, be sure to call rs.next at least once,
# or the query will _not_ execute!!!
#
def query(
sql : String,
args = nil : Array(String) | Hash(String, String) | Nil,
&block : Proc(::SQLite3::ResultSet, Nil) \
)
run(sql, args, &block)
end
def transaction(&block)
query("BEGIN")
block.call
query("COMMIT")
end
private def run(
sql : String,
args : Hash(String, String),
&block : Proc(::SQLite3::ResultSet, Nil) \
)
run(sql, [args], &block)
end
private def run(
sql : String,
args = nil : Array(String | Hash(String, String))?,
&block : Proc(::SQLite3::ResultSet, Nil) \
)
# build statement
st = prepare(sql)
# exec and close statement
if args && args.size > 0
st.execute(args, &block)
else
st.execute(&block)
end
# return result
nil
end
private def to_row(rs)
Hash(String, ::SQLite3::Value).zip(rs.columns, rs.to_a)
end
end
end
|
