diff options
| -rw-r--r-- | db/init.lua (renamed from db.lua) | 0 | ||||
| -rw-r--r-- | db/schema.lua | 58 |
2 files changed, 58 insertions, 0 deletions
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} |