-
Notifications
You must be signed in to change notification settings - Fork 34
Expand file tree
/
Copy pathdb.lua
More file actions
669 lines (612 loc) · 19.5 KB
/
db.lua
File metadata and controls
669 lines (612 loc) · 19.5 KB
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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
---@brief [[
---Main sqlite.lua object and methods.
---@brief ]]
---@tag sqlite.db.lua
local u = require "sqlite.utils"
local require = u.require_on_index
local sqlite = {}
---@class sqlite_db @Main sqlite.lua object.
---@field uri string: database uri. it can be an environment variable or an absolute path. default ":memory:"
---@field opts sqlite_opts: see https://www.sqlite.org/pragma.html |sqlite_opts|
---@field conn sqlite_blob: sqlite connection c object.
---@field db sqlite_db: reference to fallback to when overwriting |sqlite_db| methods (extended only).
sqlite.db = {}
sqlite.db.__index = sqlite.db
sqlite.db.__version = "v1.2.2"
local clib = require "sqlite.defs"
local s = require "sqlite.stmt"
local h = require "sqlite.helpers"
local a = require "sqlite.assert"
local p = require "sqlite.parser"
local tbl = require "sqlite.tbl"
---Creates a new sqlite.lua object, without creating a connection to uri.
---|sqlite.new| is identical to |sqlite.db:open| but it without opening sqlite db
---connection (unless opts.keep_open). Its most suited for cases where the
---database might be -acccess from multiple places. For neovim use cases, this
---mean from different -neovim instances.
---
---<pre>
---```lua
--- local db = sqlite.new("path/to/db" or "$env_var", { ... } or nil)
--- -- configure open mode through opts.open_mode = "ro", "rw", "rwc", default "rwc"
--- -- for more customize behaviour, set opts.open_mode to a list of db.flags
--- -- see https://sqlite.org/c3ref/open.html#urifilenamesinsqlite3open
---```
---</pre>
---@param uri string: uri to db file.
---@param opts sqlite_opts: (optional) see |sqlite_opts|
---@return sqlite_db
function sqlite.db.new(uri, opts)
opts = opts or {}
local keep_open = opts.keep_open
opts.keep_open = nil
uri = type(uri) == "string" and u.expand(uri) or ":memory:"
local o = setmetatable({
uri = uri,
conn = nil,
closed = true,
opts = opts,
modified = false,
created = nil,
tbl_schemas = {},
}, sqlite.db)
if keep_open then
o:open()
end
return o
end
---Extend |sqlite_db| object with extra sugar syntax and api. This is recommended
---for all sqlite use case as it provide convenience. This method is super lazy.
---it try its best to doing any ffi calls until the first operation done on a table.
---
---In the case you want to keep db connection open and not on invocation bases.
---Run |sqlite.db:open()| right after creating the object or when you
---intend to use it.
---
---Like |sqlite_tbl| original methods can be access through pre-appending "__"
---when user overwrites it.
---
---if { conf.opts.lazy } then only return a logical object with self-dependent
--tables, e.g. a table exists and other not because the one that
---exists, it's method was called. (default false).
---if { conf.opts.keep_open } then the sqlite extend db object will be returned
---with an open connection (default false)
---<pre>
---```lua
--- local db = sqlite { -- or sqlite_db:extend
--- uri = "path/to/db", -- path to db file
--- entries = require'entries', -- a pre-made |sqlite_tbl| object.
--- category = { title = { "text", unique = true, primary = true} },
--- opts = {} or nil -- custom sqlite3 options, see |sqlite_opts|
--- --- if opts.keep_open, make connection and keep it open.
--- --- if opts.lazy, then just provide logical object
--- --- configure open mode through opts.open_mode = "ro", "rw", "rwc", default "rwc"
--- --- for more customize behaviour, set opts.open_mode to a list of db.flags
--- --- see https://sqlite.org/c3ref/open.html#urifilenamesinsqlite3open
--- }
--- --- Overwrite method and access it using through pre-appending "__"
--- db.select = function(...) db:__select(...) end
---```
---</pre>
---@param conf table: see 'Fields'
---@field uri string: path to db file.
---@field opts sqlite_opts: (optional) see |sqlite_opts| + lazy (default false), open (default false)
---@field tname1 string: pointing to |sqlite_etbl| or |sqlite_schema_dict|
---@field tnameN string: pointing to |sqlite_etbl| or |sqlite_schema_dict|
---@see sqlite_tbl:extend
---@return sqlite_db
function sqlite.db:extend(conf)
conf.opts = conf.opts or {}
local lazy = conf.opts.lazy
conf.opts.lazy = nil
local db = self.new(conf.uri, conf.opts)
local cls = setmetatable({ db = db }, {
__index = function(_, key, ...)
if type(key) == "string" then
key = key:sub(1, 2) == "__" and key:sub(3, -1) or key
if db[key] then
return db[key]
end
end
end,
})
for tbl_name, schema in pairs(conf) do
if tbl_name ~= "uri" and tbl_name ~= "opts" and tbl_name ~= "lazy" and u.is_tbl(schema) then
local name = schema._name and schema._name or tbl_name
cls[tbl_name] = schema.set_db and schema or require("sqlite.tbl").new(name, schema, not lazy and db or nil)
if not cls[tbl_name].db then
(cls[tbl_name]):set_db(db)
end
end
end
return cls
end
---Creates and connect to new sqlite db object, either in memory or via a {uri}.
---If it is called on pre-made |sqlite_db| object, than it should open it. otherwise ignore.
---
---<pre>
---```lua
--- -- Open db file at path or environment variable, otherwise open in memory.
--- local db = sqlite.db:open("./pathto/dbfile" or "$ENV_VARABLE" or nil, {...})
--- -- reopen connection if closed.
--- db:open()
--- -- configure open mode through opts.open_mode = "ro", "rw", "rwc", default "rwc"
--- -- for more customize behaviour, set opts.open_mode to a list of db.flags
--- -- see https://sqlite.org/c3ref/open.html#urifilenamesinsqlite3open
---```
---</pre>
---@param uri string: (optional) {uri} == {nil} then in-memory db.
---@param opts sqlite_opts|nil: see |sqlite_opts|
---@return sqlite_db
function sqlite.db:open(uri, opts, noconn)
local d = self
if not d.uri then
d = sqlite.db.new(uri, opts)
end
if d.closed or d.closed == nil then
d.conn = clib.connect(d.uri, d.opts)
d.created = os.date "%Y-%m-%d %H:%M:%S"
d.closed = false
end
return d
end
---Close sqlite db connection. returns true if closed, error otherwise.
---
---<pre>
---```lua
--- local db = sqlite.db:open()
--- db:close() -- close connection
---```
---</pre>
---@return boolean
function sqlite.db:close()
self.closed = self.closed or clib.close(self.conn) == 0
a.should_close(self.conn, self.closed)
return self.closed
end
---Same as |sqlite.db:open| but execute {func} then closes db connection.
---If the function is called as a method to db object e.g. 'db:with_open', then
---{args[1]} must be a function. Else {args[1]} need to be the uri and {args[2]} the function.
---
---<pre>
---```lua
--- -- as a function
--- local entries = sqlite.with_open("path/to/db", function(db)
--- return db:select("todos", { where = { status = "done" } })
--- end)
--- -- as a method
--- local exists = db:with_open(function()
--- return db:exists("projects")
--- end)
---```
---</pre>
---
---@varargs If used as db method, then the {args[1]} should be a function, else
---{args[1]} is uri and {args[2]} is function.
---@see sqlite.db:open
---@return any
function sqlite.db:with_open(...)
local args = { ... }
if type(self) == "string" or not self then
self = sqlite.db:open(self)
end
local func = type(args[1]) == "function" and args[1] or args[2]
if self:isclose() then
self:open()
end
local success, result = pcall(function()
return func(self)
end)
self:close()
if not success then
error(result)
end
return result
end
---Predict returning true if db connection is active.
---
---<pre>
---```lua
--- if db:isopen() then
--- db:close()
--- end
---```
---</pre>
---@return boolean
function sqlite.db:isopen()
return not self.closed
end
---Predict returning true if db connection is indeed closed.
---
---<pre>
---```lua
--- if db:isclose() then
--- error("db is closed")
--- end
---```
---</pre>
---@return boolean
function sqlite.db:isclose()
return self.closed
end
---Returns current connection status
---Get last error code
---
---<pre>
---```lua
--- print(db:status().msg) -- get last error msg
--- print(db:status().code) -- get last error code.
---```
---</pre>
---@return sqlite_db_status
function sqlite.db:status()
return {
msg = clib.last_errmsg(self.conn),
code = clib.last_errcode(self.conn),
}
end
---Evaluates a sql {statement} and if there are results from evaluation it
---returns list of rows. Otherwise it returns a boolean indecating
---whether the evaluation was successful.
---
---<pre>
---```lua
--- -- evaluate without any extra arguments.
--- db:eval("drop table if exists todos")
--- -- evaluate with unamed value.
--- db:eval("select * from todos where id = ?", 1)
--- -- evaluate with named arguments.
--- db:eval("insert into t(a, b) values(:a, :b)", {a = "1", b = 3})
---```
---</pre>
---@param statement string: SQL statement.
---@param params table|nil: params to be bind to {statement}
---@return boolean|table
function sqlite.db:eval(statement, params)
local res = {}
local stmt = s:parse(self.conn, statement)
-- when the user provide simple sql statements
if not params then
stmt:each(function()
table.insert(res, stmt:kv())
end)
stmt:reset()
-- when the user run eval("select * from ?", "tbl_name")
elseif type(params) ~= "table" and statement:match "%?" then
local value = p.sqlvalue(params)
stmt:bind { value }
stmt:each(function(stm)
table.insert(res, stm:kv())
end)
stmt:reset()
stmt:bind_clear()
-- when the user provided named keys
elseif params and type(params) == "table" then
params = type(params[1]) == "table" and params or { params }
for _, v in ipairs(params) do
stmt:bind(v)
stmt:each(function(stm)
table.insert(res, stm:kv())
end)
stmt:reset()
stmt:bind_clear()
end
end
-- clear out the parsed statement.
stmt:finalize()
-- if no rows is returned, then check return the result of errcode == flags.ok
res = rawequal(next(res), nil) and clib.last_errcode(self.conn) == clib.flags.ok or res
-- fix res of its table, so that select all doesn't return { [1] = {[1] = { row }} }
if type(res) == "table" and res[2] == nil and u.is_nested(res[1]) then
res = res[1]
end
a.should_eval(self.conn)
self.modified = true
return res
end
---Execute statement without any return
---
---<pre>
---```lua
--- db:execute("drop table if exists todos")
--- db:execute("pragma foreign_keys=on")
---```
---</pre>
---@param statement string: statement to be executed
---@return boolean: true if successful, error out if not.
function sqlite.db:execute(statement)
local succ = clib.exec_stmt(self.conn, statement) == 0
return succ and succ or error(clib.last_errmsg(self.conn))
end
---Check if a table with {tbl_name} exists in sqlite db
---<pre>
---```lua
--- if not db:exists("todo_tbl") then
--- error("Table doesn't exists!!!")
--- end
---```
---</pre>
---@param tbl_name string: the table name.
---@return boolean
function sqlite.db:exists(tbl_name)
local q = self:eval("select name from sqlite_master where name= ?", tbl_name)
return type(q) == "table" and true or false
end
---Create a new sqlite db table with {name} based on {schema}. if {schema.ensure} then
---create only when it does not exists. similar to 'create if not exists'.
---
---<pre>
---```lua
--- db:create("todos", {
--- id = {"int", "primary", "key"},
--- title = "text",
--- name = { type = "string", reference = "sometbl.id" },
--- ensure = true -- create table if it doesn't already exists (THIS IS DEFUAULT)
--- })
---```
---</pre>
---@param tbl_name string: table name
---@param schema sqlite_schema_dict
---@return boolean
function sqlite.db:create(tbl_name, schema)
local req = p.create(tbl_name, schema)
if req:match "reference" then
self:execute "pragma foreign_keys = ON"
self.opts.foreign_keys = true
end
return self:eval(req)
end
---Remove {tbl_name} from database
---
---<pre>
---```lua
--- if db:exists("todos") then
--- db:drop("todos")
--- end
---```
---</pre>
---@param tbl_name string: table name
---@return boolean
function sqlite.db:drop(tbl_name)
self.tbl_schemas[tbl_name] = nil
return self:eval(p.drop(tbl_name))
end
---Get {name} table schema, if table does not exist then return an empty table.
---
---<pre>
---```lua
--- if db:exists("todos") then
--- inspect(db:schema("todos").project)
--- else
--- print("create me")
--- end
---```
---</pre>
---@param tbl_name string: the table name.
---@return sqlite_schema_dict
function sqlite.db:schema(tbl_name)
local sch = self:eval(("pragma table_info(%s)"):format(tbl_name))
local schema = {}
for _, v in ipairs(type(sch) == "boolean" and {} or sch) do
schema[v.name] = {
cid = v.cid,
required = v.notnull == 1,
primary = v.pk == 1,
type = v.type,
default = v.dflt_value,
}
end
return schema
end
---Insert lua table into sqlite database table.
---
---<pre>
---```lua
--- --- single item.
--- db:insert("todos", { title = "new todo" })
--- --- insert multiple items.
--- db:insert("items", { { name = "a"}, { name = "b" }, { name = "c" } })
---```
---</pre>
---@param tbl_name string: the table name
---@param rows table: rows to insert to the table.
---@return boolean|integer: boolean (true == success), and the last inserted row id.
function sqlite.db:insert(tbl_name, rows, schema)
a.is_sqltbl(self, tbl_name, "insert")
local ret_vals = {}
schema = schema and schema or h.get_schema(tbl_name, self)
local items = p.pre_insert(rows, schema)
local last_rowid
clib.wrap_stmts(self.conn, function()
for _, v in ipairs(items) do
local stmt = s:parse(self.conn, p.insert(tbl_name, { values = v }))
stmt:bind(v)
stmt:step()
stmt:bind_clear()
table.insert(ret_vals, stmt:finalize())
end
last_rowid = tonumber(clib.last_insert_rowid(self.conn))
end)
local succ = u.all(ret_vals, function(_, v)
return v
end)
if succ then
self.modified = true
end
return succ, last_rowid
end
---Update table row with where closure and list of values
---returns true incase the table was updated successfully.
---
---<pre>
---```lua
--- --- update todos status linked to project "lua-hello-world" or "rewrite-neoivm-in-rust"
--- db:update("todos", {
--- where = { project = {"lua-hello-world", "rewrite-neoivm-in-rust"} },
--- set = { status = "later" }
--- })
---
--- --- pass custom statement and boolean
--- db:update("timestamps", {
--- where = { id = "<" .. 4 }, -- mimcs WHERE id < 4
--- set = { seen = true } -- will be converted to 0.
--- })
---```
---</pre>
---@param tbl_name string: sqlite table name.
---@param specs sqlite_query_update | sqlite_query_update[]
---@return boolean
function sqlite.db:update(tbl_name, specs, schema)
a.is_sqltbl(self, tbl_name, "update")
if not specs then
return false
end
return clib.wrap_stmts(self.conn, function()
specs = u.is_nested(specs) and specs or { specs }
schema = schema and schema or h.get_schema(tbl_name, self)
local ret_val = nil
for _, v in ipairs(specs) do
v.set = v.set and v.set or v.values
if self:select(tbl_name, { where = v.where })[1] then
local stmt = s:parse(self.conn, p.update(tbl_name, { set = v.set, where = v.where }))
stmt:bind(p.pre_insert(v.set, schema)[1])
stmt:step()
stmt:reset()
stmt:bind_clear()
stmt:finalize()
a.should_modify(self:status())
ret_val = true
else
ret_val = self:insert(tbl_name, u.tbl_extend("keep", v.set, v.where))
a.should_modify(self:status())
end
end
self.modified = true
return ret_val
end)
end
---Delete a {tbl_name} row/rows based on the {where} closure. If {where == nil}
---then all the {tbl_name} content will be deleted.
---
---<pre>
---```lua
--- --- delete todos table content
--- db:delete("todos")
--- --- delete row that has id as 1
--- db:delete("todos", { id = 1 })
--- --- delete all rows that has value of id 1 or 2 or 3
--- db:delete("todos", { id = {1,2,3} })
--- --- matching ids or greater than 5
--- db:delete("todos", { id = {"<", 5} }) -- or {id = "<5"}
---```
---</pre>
---@param tbl_name string: sqlite table name
---@param where sqlite_query_delete: key value pair to where delete operation should effect.
---@todo support querys with `and`
---@return boolean: true if operation is successfully, false otherwise.
function sqlite.db:delete(tbl_name, where)
a.is_sqltbl(self, tbl_name, "delete")
if not where then
return self:execute(p.delete(tbl_name))
end
where = u.is_nested(where) and where or { where }
clib.wrap_stmts(self.conn, function()
for _, spec in ipairs(where) do
local _where = spec.where and spec.where or spec
local stmt = s:parse(self.conn, p.delete(tbl_name, { where = _where }))
stmt:step()
stmt:reset()
stmt:finalize()
a.should_modify(self:status())
end
end)
self.modified = true
return true
end
---Query from a table with where and join options
---
---<pre>
---```lua
--- db:select("todos") get everything
--- --- get row with id of 1
--- db:select("todos", { where = { id = 1 })
--- --- get row with status value of later or paused
--- db:select("todos", { where = { status = {"later", "paused"} })
--- --- get 5 items from todos table
--- db:select("todos", { limit = 5 })
--- --- select a set of keys with computed one
--- db:select("timestamps", {
--- select = {
--- age = (strftime("%s", "now") - strftime("%s", "timestamp")) * 24 * 60,
--- "id",
--- "timestamp",
--- "entry",
--- },
--- })
---```
---</pre>
---@param tbl_name string: the name of the db table to select on
---@param spec sqlite_query_select
---@return table[]
function sqlite.db:select(tbl_name, spec, schema)
a.is_sqltbl(self, tbl_name, "select")
return clib.wrap_stmts(self.conn, function()
local ret = {}
schema = schema and schema or h.get_schema(tbl_name, self)
spec = spec or {}
spec.select = spec.keys and spec.keys or spec.select
local stmt = s:parse(self.conn, p.select(tbl_name, spec))
s.each(stmt, function()
table.insert(ret, s.kv(stmt))
end)
s.reset(stmt)
if s.finalize(stmt) then
self.modified = false
end
return p.post_select(ret, schema)
end)
end
---Create new sqlite_tbl object.
---If {opts}.ensure = false, then on each run it will drop the table and recreate it.
---NOTE: this might change someday to alter the table instead. For now
---alteration is auto and limited to field schema edits and renames
---
---<pre>
---```lua
--- local tbl = db:tbl("todos", {
--- id = true, -- same as { type = "integer", required = true, primary = true }
--- title = "text",
--- category = {
--- type = "text",
--- reference = "category.id",
--- on_update = "cascade", -- means when category get updated update
--- on_delete = "null", -- means when category get deleted, set to null
--- },
--- })
--- --- or without db injected and use as wrapper for |sqlite.tbl.new|
--- local tbl = db.tbl("name", ...)
---```
---</pre>
---@param tbl_name string: the name of the table. can be new or existing one.
---@param schema sqlite_schema_dict: {schema, ensure (defalut true)}
---@see |sqlite.tbl.new|
---@return sqlite_tbl
function sqlite.db:tbl(tbl_name, schema)
if type(self) == "string" then
schema = tbl_name
return tbl.new(self, schema)
end
return tbl.new(tbl_name, schema, self)
end
---DEPRECATED
function sqlite.db:table(tbl_name, opts)
print "sqlite.lua sqlite:table is deprecated use sqlite:tbl instead"
return self:tbl(tbl_name, opts)
end
---Sqlite functions sugar wrappers. See `sql/strfun`
sqlite.db.lib = require "sqlite.strfun"
sqlite.db = setmetatable(sqlite.db, {
__call = sqlite.db.extend,
})
sqlite.db.flags = clib.flags
return sqlite.db