Skip to content

Commit 25748dd

Browse files
kkharjikkharji
authored
✨ auto alter table key definition (#103)
Support for modifying schema key definitions without wasting the table content. It has little support for renaming, in fact, renaming should be avoided for the time being. ✨ New: - `db:execute` for executing statement without any return. - emmylua classes`SqlSchemaKeyDefinition` and `SqliteActions`. - when a key has default, then all the columns with nulls will be replaced with the default. - support for auto altering key to reference a foreign key. 🐛 Fixes - when a foreign_keys is enabled on a connection, closing and opening disables it. ♻️ Changes - rename `db.sqlite_opts` to `db.opts`. ✅ Added Tests - auto alter: simple rename with idetnical number of keys - auto alter: simple rename with idetnical number of keys with a key turned to be required - auto alter: more than one rename with idetnical number of keys - auto alter: more than one rename with idetnical number of keys + default without required = true - auto alter: transform to foreign key - auto alter: pass sqlite.org tests
1 parent 3afb8d2 commit 25748dd

9 files changed

Lines changed: 600 additions & 59 deletions

File tree

‎doc/sql.txt‎

Lines changed: 34 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,21 @@ SQLDatabase *SQLDatabase*
1616
{conn} (sqlite3_blob) sqlite connection c object.
1717

1818

19+
SqlSchemaKeyDefinition *SqlSchemaKeyDefinition*
20+
21+
22+
Fields: ~
23+
{cid} (number) column index
24+
{name} (string) column key
25+
{type} (string) column type
26+
{required} (boolean) whether the column key is required or not
27+
{primary} (boolean) whether the column is a primary key
28+
{default} (string) the default value of the column
29+
{reference} (string) table_name.column
30+
{on_update} (SqliteActions) what to do when the key gets updated
31+
{on_delete} (SqliteActions) what to do when the key gets deleted
32+
33+
1934
SQLQuerySpec *SQLQuerySpec*
2035
Query spec that are passed to a number of db: methods.
2136

@@ -200,6 +215,17 @@ DB:eval({statement}, {params}) *DB:eval()*
200215
evaluate with named arguments.
201216

202217

218+
DB:execute({statement}) *DB:execute()*
219+
Execute statement without any return
220+
221+
222+
Parameters: ~
223+
{statement} (string) statement to be executed
224+
225+
Return: ~
226+
boolean: true if successful, error out if not.
227+
228+
203229
DB:exists({tbl_name}) *DB:exists()*
204230
Check if a table with {tbl_name} exists in sqlite db
205231

@@ -221,8 +247,8 @@ DB:create({tbl_name}, {schema}) *DB:create()*
221247

222248

223249
Parameters: ~
224-
{tbl_name} (string) table name
225-
{schema} (table) the table keys/column and their types
250+
{tbl_name} (string) table name
251+
{schema} (table<string, SqlSchemaKeyDefinition>)
226252

227253
Return: ~
228254
boolean
@@ -255,7 +281,7 @@ DB:schema({tbl_name}) *DB:schema()*
255281
{tbl_name} (string) the table name.
256282

257283
Return: ~
258-
table list of keys or keys and their type.
284+
table<string, SqlSchemaKeyDefinition>
259285

260286

261287
DB:insert({tbl_name}, {rows}) *DB:insert()*
@@ -375,8 +401,8 @@ tbl:new({db}, {name}, {schema}) *tbl:new()*
375401

376402
Parameters: ~
377403
{db} (SQLDatabase)
378-
{name} (string) table name
379-
{schema} (table) table schema
404+
{name} (string) table name
405+
{schema} (table<string, SqlSchemaKeyDefinition>)
380406

381407
Return: ~
382408
SQLTable
@@ -391,7 +417,7 @@ tbl:extend({db}, {name}, {schema}) *tbl:extend()*
391417
Parameters: ~
392418
{db} (SQLDatabase)
393419
{name} (string)
394-
{schema} (table)
420+
{schema} (table<string, SqlSchemaKeyDefinition>)
395421

396422
Return: ~
397423
SQLTableExt
@@ -404,10 +430,10 @@ tbl:schema({schema}) *tbl:schema()*
404430

405431

406432
Parameters: ~
407-
{schema} (table) table schema definition
433+
{schema} (table<string, SqlSchemaKeyDefinition>)
408434

409435
Return: ~
410-
table table | boolean
436+
table<string, SqlSchemaKeyDefinition> | boolean
411437

412438
Usage: ~
413439
`projects:schema()` get project table schema.

‎lua/sql.lua‎

Lines changed: 33 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -24,10 +24,28 @@ local flags = clib.flags
2424
local DB = {}
2525
DB.__index = DB
2626

27+
---@class SqlSchemaKeyDefinition
28+
---@field cid number: column index
29+
---@field name string: column key
30+
---@field type string: column type
31+
---@field required boolean: whether the column key is required or not
32+
---@field primary boolean: whether the column is a primary key
33+
---@field default string: the default value of the column
34+
---@field reference string: table_name.column
35+
---@field on_update SqliteActions: what to do when the key gets updated
36+
---@field on_delete SqliteActions: what to do when the key gets deleted
37+
2738
---@class SQLQuerySpec @Query spec that are passed to a number of db: methods.
2839
---@field where table: key and value
2940
---@field values table: key and value to updated.
3041

42+
---@alias SqliteActions
43+
---| '"no action"' : when a parent key is modified or deleted from the database, no special action is taken.
44+
---| '"restrict"' : prohibites from deleting/modifying a parent key when a child key is mapped to it.
45+
---| '"null"' : when a parent key is deleted/modified, the child key that mapped to the parent key gets set to null.
46+
---| '"default"' : similar to "null", except that sets to the column's default value instead of NULL.
47+
---| '"cascade"' : propagates the delete or update operation on the parent key to each dependent child key.
48+
3149
---Get a table schema, or execute a given function to get it
3250
---@param schema table|nil
3351
---@param self SQLDatabase
@@ -69,14 +87,14 @@ function DB:open(uri, opts, noconn)
6987
uri = uri,
7088
conn = not noconn and clib.connect(uri, opts) or nil,
7189
closed = noconn and true or false,
72-
sqlite_opts = opts,
90+
opts = opts or {},
7391
modified = false,
7492
created = not noconn and os.date "%Y-%m-%d %H:%M:%S" or nil,
7593
tbl_schemas = {},
7694
}, self)
7795
else
7896
if self.closed or self.closed == nil then
79-
self.conn = clib.connect(self.uri, self.sqlite_opts)
97+
self.conn = clib.connect(self.uri, self.opts)
8098
self.created = os.date "%Y-%m-%d %H:%M:%S"
8199
self.closed = false
82100
end
@@ -239,6 +257,14 @@ function DB:eval(statement, params)
239257
return res
240258
end
241259

260+
---Execute statement without any return
261+
---@param statement string: statement to be executed
262+
---@return boolean: true if successful, error out if not.
263+
function DB:execute(statement)
264+
local succ = clib.exec_stmt(self.conn, statement) == 0
265+
return succ and succ or error(clib.last_errmsg(self.conn))
266+
end
267+
242268
---Check if a table with {tbl_name} exists in sqlite db
243269
---@param tbl_name string: the table name.
244270
---@usage `if not db:exists("todo_tbl") then error("...") end`
@@ -251,13 +277,14 @@ end
251277
---Create a new sqlite db table with {name} based on {schema}. if {schema.ensure} then
252278
---create only when it does not exists. similar to 'create if not exists'.
253279
---@param tbl_name string: table name
254-
---@param schema table: the table keys/column and their types
280+
---@param schema table<string, SqlSchemaKeyDefinition>
255281
---@usage `db:create("todos", {id = {"int", "primary", "key"}, title = "text"})` create table with the given schema.
256282
---@return boolean
257283
function DB:create(tbl_name, schema)
258284
local req = P.create(tbl_name, schema)
259285
if req:match "reference" then
260-
self:eval "pragma foreign_keys = ON"
286+
self:execute "pragma foreign_keys = ON"
287+
self.opts.foreign_keys = true
261288
end
262289
return self:eval(req)
263290
end
@@ -273,7 +300,7 @@ end
273300

274301
---Get {name} table schema, if table does not exist then return an empty table.
275302
---@param tbl_name string: the table name.
276-
---@return table list of keys or keys and their type.
303+
---@return table<string, SqlSchemaKeyDefinition>
277304
function DB:schema(tbl_name)
278305
local sch = self:eval(("pragma table_info(%s)"):format(tbl_name))
279306
local schema = {}
@@ -377,7 +404,7 @@ function DB:delete(tbl_name, where)
377404
a.is_sqltbl(self, tbl_name, "delete")
378405

379406
if not where then
380-
return clib.exec_stmt(self.conn, P.delete(tbl_name)) == 0 and true or clib.last_errmsg(self.conn)
407+
return self:execute(P.delete(tbl_name))
381408
end
382409

383410
where = u.is_nested(where) and where or { where }

‎lua/sql/assert.lua‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,7 @@ local errors = {
1212
missing_req_key = "(insert) missing a required key: %s",
1313
missing_db_object = "'%s' db object is not set. please set it with `tbl.set_db(db)` and try again.",
1414
outdated_schema = "`%s` does not exists in {`%s`}, schema is outdateset `self.db.tbl_schemas[table_name]` or reload",
15+
auto_alter_more_less_keys = "schema defined ~= db schema. Please drop `%s` table first or set ensure to false.",
1516
}
1617

1718
for key, value in pairs(errors) do
@@ -73,4 +74,10 @@ M.should_have_db_object = function(db, name)
7374
return true
7475
end
7576

77+
M.auto_alter_should_have_equal_len = function(len_new, len_old, tname)
78+
if len_new - len_old ~= 0 then
79+
error(errors.auto_alter_more_less_keys:format(tname))
80+
end
81+
end
82+
7683
return M

‎lua/sql/parser.lua‎

Lines changed: 105 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -341,7 +341,12 @@ local opts_to_str = function(tbl)
341341
end
342342
end,
343343
default = function(v)
344-
return "default " .. v
344+
local str = "default "
345+
if tbl["required"] then
346+
return "on conflict replace " .. str .. v
347+
else
348+
return str .. v
349+
end
345350
end,
346351
reference = function(v)
347352
return ("references %s"):format(v:gsub("%.", "(") .. ")")
@@ -386,13 +391,13 @@ end
386391
---@param tbl string: table name
387392
---@param defs table: keys and type pairs
388393
---@return string: the create sql statement.
389-
M.create = function(tbl, defs)
394+
M.create = function(tbl, defs, ignore_ensure)
390395
if not defs then
391396
return
392397
end
393398
local items = {}
394399

395-
tbl = defs.ensure and "if not exists " .. tbl or tbl
400+
tbl = (defs.ensure and not ignore_ensure) and "if not exists " .. tbl or tbl
396401

397402
for k, v in u.opairs(defs) do
398403
if k ~= "ensure" then
@@ -410,7 +415,7 @@ M.create = function(tbl, defs)
410415
end
411416
end
412417
end
413-
return ("create table %s(%s)"):format(tbl, tconcat(items, ", "))
418+
return ("CREATE TABLE %s(%s)"):format(tbl, tconcat(items, ", "))
414419
end
415420

416421
---Parse table drop statement
@@ -420,7 +425,102 @@ M.drop = function(tbl)
420425
return "drop table " .. tbl
421426
end
422427

423-
---Preporcess data insert to sql db.
428+
-- local same_type = function(new, old)
429+
-- if not new or not old then
430+
-- return false
431+
-- end
432+
433+
-- local tnew, told = type(new), type(old)
434+
435+
-- if tnew == told then
436+
-- if tnew == "string" then
437+
-- return new == old
438+
-- elseif tnew == "table" then
439+
-- if new[1] and old[1] then
440+
-- return (new[1] == old[1])
441+
-- elseif new.type and old.type then
442+
-- return (new.type == old.type)
443+
-- elseif new.type and old[1] then
444+
-- return (new.type == old[1])
445+
-- elseif new[1] and old.type then
446+
-- return (new[1] == old.type)
447+
-- end
448+
-- end
449+
-- else
450+
-- if tnew == "table" and told == "string" then
451+
-- if new.type == old then
452+
-- return true
453+
-- elseif new[1] == old then
454+
-- return true
455+
-- end
456+
-- elseif tnew == "string" and told == "table" then
457+
-- return old.type == new or old[1] == new
458+
-- end
459+
-- end
460+
-- -- return false
461+
-- end
462+
463+
---Alter a given table, only support changing key definition
464+
---@param tname string
465+
---@param new table<string, SqlSchemaKeyDefinition>
466+
---@param old table<string, SqlSchemaKeyDefinition>
467+
M.table_alter_key_defs = function(tname, new, old, dry)
468+
local tmpname = tname .. "_new"
469+
local create = M.create(tmpname, new, true)
470+
local drop = M.drop(tname)
471+
local move = "INSERT INTO %s(%s) SELECT %s FROM %s"
472+
local rename = ("ALTER TABLE %s RENAME TO %s"):format(tmpname, tname)
473+
local with_foregin_key = false
474+
475+
for _, def in pairs(new) do
476+
if def.reference then
477+
with_foregin_key = true
478+
end
479+
end
480+
481+
local stmt = "PRAGMA foreign_keys=off; BEGIN TRANSACTION; %s; COMMIT;"
482+
if not with_foregin_key then
483+
stmt = stmt .. " PRAGMA foreign_keys=on"
484+
end
485+
486+
local keys = { new = u.okeys(new), old = u.okeys(old) }
487+
local idx = { new = {}, old = {} }
488+
local len = { new = #keys.new, old = #keys.old }
489+
-- local facts = { extra_key = len.new > len.old, drop_key = len.old > len.new }
490+
491+
a.auto_alter_should_have_equal_len(len.new, len.old, tname)
492+
493+
for _, varient in ipairs { "new", "old" } do
494+
for k, v in pairs(keys[varient]) do
495+
idx[varient][v] = k
496+
end
497+
end
498+
499+
for i, v in ipairs(keys.new) do
500+
if idx.old[v] and idx.old[v] ~= i then
501+
local tmp = keys.old[i]
502+
keys.old[i] = v
503+
keys.old[idx.old[v]] = tmp
504+
end
505+
end
506+
507+
local update_null_vals = {}
508+
local update_null_stmt = "UPDATE %s SET %s=%s where %s IS NULL"
509+
for key, def in pairs(new) do
510+
if def.default and not def.required then
511+
tinsert(update_null_vals, update_null_stmt:format(tmpname, key, def.default, key))
512+
end
513+
end
514+
update_null_vals = #update_null_vals == 0 and "" or tconcat(update_null_vals, "; ")
515+
516+
local new_keys, old_keys = tconcat(keys.new, ", "), tconcat(keys.old, ", ")
517+
local insert = move:format(tmpname, new_keys, old_keys, tname)
518+
stmt = stmt:format(tconcat({ create, insert, update_null_vals, drop, rename }, "; "))
519+
520+
return not dry and stmt or insert
521+
end
522+
523+
---Pre-process data insert to sql db.
424524
---for now it's mainly used to for parsing lua tables and boolean values.
425525
---It throws when a schema key is required and doesn't exists.
426526
---@param rows tinserted row.

0 commit comments

Comments
 (0)