-- 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}