From 425290f7a1813ccf768cb6ccdfc3bbb27b059025 Mon Sep 17 00:00:00 2001
From: rebecca <ubq323@ubq323.website>
Date: Sun, 20 Sep 2026 11:41:01 +0100
Subject: add db.schema

---
 db/init.lua   | 62 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 db/schema.lua | 58 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 120 insertions(+)
 create mode 100644 db/init.lua
 create mode 100644 db/schema.lua

(limited to 'db')

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}
diff --git a/db/schema.lua b/db/schema.lua
new file mode 100644
index 0000000..6cee99f
--- /dev/null
+++ b/db/schema.lua
@@ -0,0 +1,58 @@
+-- this will add columns and tables that don't exist yet,
+-- and synchronise notnull constraints; but it won't delete anything,
+-- or update types, or deal with indexes or triggers, or anything like that
+-- this is an intentional scope limitation; more features will be added only if i need them
+
+local db = require'r.db'
+local pprint = require'pprint'
+
+local create_table,add_column,set_notnull
+local function schemafy(schema)
+	local conn <close> = db.conn()
+	for tablename, want_cols_t in pairs(schema) do
+		local want_cols = {} local col_order = {}
+		for i,v in ipairs(want_cols_t) do
+			local name,type,notnull = table.unpack(v)
+			assert(not want_cols[name],"duplicate column name "..name)
+			assert(name ~= "id","id column is reserved")
+			want_cols[name] = {name=name,type=type,notnull=not not notnull}
+			col_order[i] = name
+		end
+		local have_cols_r =
+			conn:fetch("pragma table_info("..tablename..")")
+		if #have_cols_r == 0 then create_table(conn,tablename,want_cols,col_order)
+		else
+			local have_cols = {}
+			for i,v in ipairs(have_cols_r) do
+				v.notnull = v.notnull ~= 0
+				have_cols[v.name] = v
+				if not want_cols[v.name] then
+					print("warning, extraneous column:",tablename,v.name)
+				end
+			end
+			for k,col in pairs(want_cols) do
+				local existing = have_cols[k]
+				if not existing then add_column(conn,tablename,k,col)
+				else
+					local t1,t2 = col.type:lower(), existing.type:lower()
+					assert(t1==t2,"type mismatch "..t1..' '..t2)
+					if col.notnull ~= existing.notnull then
+						set_notnull(conn,tablename,k,col.notnull)
+	end end end end end
+	conn:commit()
+end
+local function run(conn,sql) print(sql) conn:exec(sql) end
+local function column_desc(col)
+	return col.name..' '..col.type..(col.notnull and ' not null' or '') end
+function create_table(conn,tablename,columns,order)
+	local coldescs = {} for i,v in ipairs(order) do coldescs[i]=column_desc(columns[v]) end
+	table.insert(coldescs,"id integer primary key")
+	run(conn,"create table "..tablename.."("..table.concat(coldescs,",\n")..")") end
+function add_column(conn,tablename,columnname,col)
+	run(conn,("alter table %s add column %s")
+		:format(tablename,column_desc(col))) end
+function set_notnull(conn,tablename,columnname,notnull)
+	run(conn,("alter table %s alter column %s %s not null")
+		:format(tablename,columnname,notnull and 'set' or 'drop')) end
+
+return {schemafy=schemafy}
-- 
cgit v1.2.3