1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
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}
|
