add db.schema

2 files changed, 58 insertions, 0 deletions

diff --git a/db.lua b/db/init.lua
index 5b616c2..5b616c2 100644
--- a/db.lua
+++ b/db/init.lua
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}