add db.schema

1 files changed, 62 insertions, 0 deletions

diff --git a/db/init.lua b/db/init.lua
new file mode 100644
index 0000000..5b616c2
--- /dev/null
+++ b/db/init.lua
@@ -0,0 +1,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}