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
|
local dbi = require'DBI'
local cqueues = require'cqueues'
local function db_do(db, sql, ...)
-- print('>>',sql,...)
local stmt = assert(db:prepare(sql))
::tryagain::
local ok, err = stmt:execute(...)
if not ok and err:match"locked" then
print'still waiting'
cqueues.sleep(1)
goto tryagain
end
assert(ok, err)
return stmt
end
local extra_methods = {}
function extra_methods.fetch_one(db, sql, ...)
local stmt = db_do(db, sql, ...)
local row = stmt:fetch(true)
stmt:close()
return row
end
function extra_methods.fetch(db, sql, ...)
local stmt = db_do(db, sql, ...)
local rows, i = {}, 1
for row in stmt:rows(true) do
rows[i] = row
i = i + 1
end
stmt:close()
return rows
end
function extra_methods.exec(db, sql, ...)
local stmt = db_do(db, sql, ...)
stmt:close()
end
local function get_conn()
local c = assert(dbi.Connect('SQLite3','database.db'))
-- fuck it
local mt = getmetatable(c)
for k,v in pairs(extra_methods) do mt.__index[k] = v end
mt.__close = function(x) x:close() end
c:exec('pragma foreign_keys=ON;')
return c
end
local function apply_migrations(migrations)
local conn <close> = get_conn()
local ver = conn:fetch_one"pragma user_version".user_version
for i = ver+1, #migrations do
print("applying migration #"..i)
conn:exec(migrations[i])
end
conn:exec("pragma user_version="..#migrations)
assert(conn:commit())
end
return {conn=get_conn,apply_migrations=apply_migrations}
|
