« back to all changes in this revision
Viewing changes to src/mudlet-lua/lua/DB.lua
- browse files at revision 406
- compare with another revision
- download diff
- download tarball
- view history from revision 406
- Committer: Vadim Peretokin
- Date: 2010-08-28 18:10:10 UTC
- Revision ID: vadi@vadi-laptop-20100828181010-bew8lo0qqqdxajmu
Added missing files.
- files added:
- src/Makefile
- src/mudlet-lua
- src/mudlet-lua/lua
- src/mudlet-lua/lua/geyser
- src/mudlet-lua/lua/luadoc
- src/mudlet-lua/lua/luadoc/doclet
- src/mudlet-lua/lua/luadoc/doclet/html
- src/mudlet-lua/lua/luadoc/taglet
- src/mudlet-lua/lua/luadoc/taglet/standard
- src/mudlet-lua/mudlet-lua-doc
- src/mudlet-lua/mudlet-lua-doc/files
- src/mudlet-lua/mudlet-lua-doc/files/geyser
- src/tmp
added
removed
src/mudlet-lua/lua/DB.lua
1
----------------------------------------------------------------------------------
2
--- Mudlet DB
3
----------------------------------------------------------------------------------
4
5
6
-- TODO will be already loaded in LuaGlobal
7
-----------------------------------------------------------------------------
8
-- General-purpose useful tools that were needed during development:
9
-----------------------------------------------------------------------------
10
if package.loaded["rex_pcre"] then rex = require"rex_pcre" end
11
12
13
14
-- TODO those funciton are already definde elsewhere
15
-- Tests if a table is empty: this is useful in situations where you find
16
-- yourself wanting to do 'if my_table == {}' and such.
17
function table.is_empty(tbl)
18
for k, v in pairs(tbl) do
19
return false
20
end
21
return true
22
end
23
24
function string.starts(String,Start)
25
return string.sub(String,1,string.len(Start))==Start
26
end
27
28
function string.ends(String,End)
29
return End=='' or string.sub(String,-string.len(End))==End
30
end
31
32
33
34
35
-- TODO move to StringUtils?
36
-----------------------------------------------------------------------------
37
-- Some Date / Time parsing functions.
38
-----------------------------------------------------------------------------
39
datetime = {
40
_directives = {
41
["%b"] = "(?P<abbrev_month_name>jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)",
42
["%B"] = "(?P<month_name>january|febuary|march|april|may|june|july|august|september|october|november|december)",
43
["%d"] = "(?P<day_of_month>\\d{2})",
44
["%H"] = "(?P<hour_24>\\d{2})",
45
["%I"] = "(?P<hour_12>\\d{2})",
46
["%m"] = "(?P<month>\\d{2})",
47
["%M"] = "(?P<minute>\\d{2})",
48
["%p"] = "(?P<ampm>am|pm)",
49
["%S"] = "(?P<second>\\d{2})",
50
["%y"] = "(?P<year_half>\\d{2})",
51
["%Y"] = "(?P<year_full>\\d{4})"
52
},
53
_pattern_cache = {},
54
_month_names = {
55
["january"] = 1,
56
["febuary"] = 2,
57
["march"] = 3,
58
["april"] = 4,
59
["may"] = 5,
60
["june"] = 6,
61
["july"] = 7,
62
["august"] = 8,
63
["september"] = 9,
64
["october"] = 10,
65
["november"] = 11,
66
["december"] = 12
67
},
68
_abbrev_month_names = {
69
["jan"] = 1,
70
["feb"] = 2,
71
["mar"] = 3,
72
["apr"] = 4,
73
["may"] = 5,
74
["jun"] = 6,
75
["jul"]= 7,
76
["aug"]= 8,
77
["sep"] = 9,
78
["oct"] = 10,
79
["nov"] = 11,
80
["dec"] = 12
81
}
82
}
83
84
85
86
-- NOT LUADOC
87
-- The rex.match function does not return named patterns even if you use named capture
88
-- groups, but the r:tfind does -- but this only operates on compiled patterns. So,
89
-- we are caching the conversion of 'simple format' date patterns into a regex, and
90
-- then compiling them.
91
function datetime:_get_pattern(format)
92
if not datetime._pattern_cache[format] then
93
local fmt = rex.gsub(format, "(%[A-Za-z])",
94
function(m)
95
return datetime._directives[m] or m
96
end
97
)
98
99
datetime._pattern_cache[format] = rex.new(fmt, rex.flags().CASELESS)
100
end
101
102
return datetime._pattern_cache[format]
103
end
104
105
106
107
--- Parses the specified source string, according to the format if given, to return a representation of
108
--- the date/time. The default format if not specified is: "^%Y-%m-%d %H:%M:%S$" <br/><br/>
109
---
110
--- If as_epoch is provided and true, the return value will be a Unix epoch -- the number
111
--- of seconds since 1970. This is a useful format for exchanging date/times with other systems. If as_epoch
112
--- is false, then a Lua time table will be returned. Details of the time tables are provided
113
--- in the http://www.lua.org/pil/22.1.html. <br/><br/>
114
---
115
--- Supported Format Codes
116
--- </pre>
117
--- %b Abbreviated Month Name
118
--- %B Full Month Name
119
--- %d Day of Month
120
--- %H Hour (24-hour format)
121
--- %I Hour (12-hour format, requires %p as well)
122
--- %p AM or PM
123
--- %m 2-digit month (01-12)
124
--- %M 2-digit minutes (00-59)
125
--- %S 2-digit seconds (00-59)
126
--- %y 2-digit year (00-99), will automatically prepend 20 so 10 becomes 2010 and not 1910.
127
--- %Y 4-digit year.
128
--- </pre>
129
function datetime:parse(source, format, as_epoch)
130
if not format then
131
format = "^%Y-%m-%d %H:%M:%S$"
132
end
133
134
local fmt = datetime:_get_pattern(format)
135
local m = {fmt:tfind(source)}
136
137
if m then
138
m = m[3]
139
dt = {}
140
141
if m.year_half then
142
dt.year = tonumber("20"..m.year_half)
143
elseif m.year_full then
144
dt.year = tonumber(m.year_full)
145
end
146
147
if m.month then
148
dt.month = tonumber(m.month)
149
elseif m.month_name then
150
dt.month = datetime._month_names[m.month_name:lower()]
151
elseif m.abbrev_month_name then
152
dt.month = datetime._abbrev_month_names[m.abbrev_month_name:lower()]
153
end
154
155
dt.day = m.day_of_month
156
157
if m.hour_12 then
158
assert(m.ampm, "You must use %p (AM|PM) with %I (12-hour time)")
159
if m.ampm == "PM" then
160
dt.hour = 12 + tonumber(m.hour_12)
161
else
162
dt.hour = tonumber(m.hour_12)
163
end
164
else
165
dt.hour = tonumber(m.hour_24)
166
end
167
168
dt.min = tonumber(m.minute)
169
dt.sec = tonumber(m.second)
170
dt.isdst = false
171
172
if as_epoch then
173
return os.time(dt)
174
else
175
return dt
176
end
177
else
178
return nil
179
end
180
end
181
182
183
184
-----------------------------------------------------------------------------
185
-- The database wrapper library
186
-----------------------------------------------------------------------------
187
if package.loaded["luasql.sqlite3"] then require "luasql.sqlite3" end
188
189
db = {}
190
db.__autocommit = {}
191
db.__schema = {}
192
db.__conn = {}
193
194
db.debug_sql = false
195
196
197
198
-- NOT LUADOC
199
-- Converts the type of a lua object to the equivalent type in SQL
200
function db:_sql_type(value)
201
local t = type(value)
202
203
if t == "number" then
204
return "REAL"
205
elseif t == "nil" then
206
return "NULL"
207
elseif t == "table" and value._timestamp ~= nil then
208
return "INTEGER"
209
else
210
return "TEXT"
211
end
212
end
213
214
215
216
-- NOT LUADOC
217
-- Converts a data value in Lua to its SQL equivalent; notably it will also escape single-quotes to
218
-- prevent inadvertant SQL injection.
219
function db:_sql_convert(value)
220
local t = db:_sql_type(value)
221
222
if value == nil then
223
return "NULL"
224
elseif t == "TEXT" then
225
return '"'..value:gsub("'", "''")..'"'
226
elseif t == "NULL" then
227
return "NULL"
228
elseif t == "INTEGER" then
229
-- With db.Timestamp's, a value of false should be interpreted as nil.
230
if value._timestamp == false then
231
return "NULL"
232
end
233
return tostring(value._timestamp)
234
else
235
return tostring(value)
236
end
237
end
238
239
240
241
-- NOT LUADOC
242
-- Given a sheet name and the details of an index, this function will return a unique index name to
243
-- add to the database. The purpose of this is to create unique index names as indexes are tested
244
-- for existance on each call of db:create and not only on creation. That way new indexes can be
245
-- added after initial creation.
246
function db:_index_name(tbl_name, params)
247
local t = type(params)
248
249
if t == "string" then
250
return "idx_" .. tbl_name .. "_c_" .. params
251
elseif assert(t == "table", "Indexes must be either a string or a table.") then
252
local parts = {"idx", tbl_name, "c"}
253
for _, v in pairs(params) do
254
parts[#parts+1] = v
255
end
256
return table.concat(parts, "_")
257
end
258
end
259
260
261
262
-- NOT LUADOC
263
-- This function returns true if all of the columns referenced in index_columns also exist within
264
-- the sheet_columns table array. The purpose of this is to raise an error if someone tries to index
265
-- a column which doesn't currently exist in the schema.
266
function db:_index_valid(sheet_columns, index_columns)
267
if type(index_columns) == "string" then
268
if sheet_columns[index_columns] ~= nil then
269
return true
270
else
271
return false
272
end
273
else
274
for _, v in ipairs(index_columns) do
275
if sheet_columns[v] == nil then
276
echo("\n--> Bad index "..v)
277
return false
278
end
279
end
280
end
281
return true
282
end
283
284
285
286
-- NOT LUADOC
287
-- The column_spec is either a string or an indexed table. This function returns either "column" or
288
-- "column1", "column2" for use in the column specification of INSERT.
289
function db:_sql_columns(value)
290
local colstr = ''
291
local t = type(value)
292
293
if t == "table" then
294
col_chunks = {}
295
for _, v in ipairs(value) do
296
col_chunks[#col_chunks+1] = '"'..v:lower()..'"'
297
end
298
299
colstr = table.concat(col_chunks, ',')
300
elseif assert(t == "string",
301
"Must specify either a table array or string for index, not "..type(value)) then
302
colstr = '"'..value:lower()..'"'
303
end
304
return colstr
305
end
306
307
308
309
-- NOT LUADOC
310
-- This serves as a very similar function to db:_sql_columns, quoting column names properly but for
311
-- uses outside of INSERTs.
312
function db:_sql_fields(values)
313
local sql_fields = {}
314
315
for k, v in pairs(values) do
316
sql_fields[#sql_fields+1] = '"'..k..'"'
317
end
318
319
return "("..table.concat(sql_fields, ",")..")"
320
end
321
322
323
324
-- NOT LUADOC
325
-- This quotes values to be passed into an INSERT or UPDATE operation in a SQL list. Meaning, it turns
326
-- {x="this", y="that", z=1} into ('this', 'that', 1).
327
-- It is intelligent with data-types; strings are automatically quoted (with internal single quotes
328
-- escaped), nil turned into NULL, timestamps converted to integers, and such.
329
function db:_sql_values(values)
330
local sql_values = {}
331
332
for k, v in pairs(values) do
333
local t = type(v)
334
local s = ""
335
336
if t == "string" then
337
s = "'"..v:gsub("'", "''").."'"
338
elseif t == "nil" then
339
s = "NULL"
340
elseif t == "table" and t._timestamp ~= nil then
341
if not t._timestamp then
342
return "NULL"
343
else
344
s = tostring(t._timestamp)
345
end
346
else
347
s = tostring(v)
348
end
349
350
sql_values[#sql_values+1] = s
351
end
352
353
return "("..table.concat(sql_values, ",")..")"
354
end
355
356
357
358
--- <b><u>TODO</u></b> db:safe_name(name)
359
-- On a filesystem level, names are restricted to being alphanumeric only. So, "my_database" becomes
360
-- "mydatabase", and "../../../../etc/passwd" becomes "etcpasswd". This prevents any possible
361
-- security issues with database names.
362
function db:safe_name(name)
363
name = name:gsub("[^%ad]", "")
364
name = name:lower()
365
return name
366
end
367
368
369
370
--- Creates and/or modifies an existing database. This function is safe to define at a top-level of a Mudlet
371
--- script: in fact it is reccommended you run this function at a top-level without any kind of guards.
372
--- If the named database does not exist it will create it. If the database does exist then it will add
373
--- any columns or indexes which didn't exist before to that database. If the database already has all the
374
--- specified columns and indexes, it will do nothing. <br/><br/>
375
---
376
--- The database will be called Database_<sanitized database name>.db and will be stored in the
377
--- Mudlet configuration directory. <br/><br/>
378
---
379
--- Database 'tables' are called 'sheets' consistently throughout this documentation, to avoid confusion
380
--- with Lua tables. <br/><br/>
381
---
382
--- The schema table must be a Lua table array containing table dictionaries that define the structure and
383
--- layout of each sheet. <br/><br/>
384
---
385
--- For sheets with unique indexes, you may specify a _violations key to indicate how the db layer handle
386
--- cases where the unique index is violated. The options you may use are:
387
--- <pre>
388
--- FAIL - the default. A hard error is thrown, cancelling the script.
389
--- IGNORE - The command that would add a record that violates uniqueness just fails silently.
390
--- REPLACE - The old record which matched the unique index is dropped, and the new one is added to replace it.
391
--- </pre>
392
---
393
--- @usage Example bellow will create a database with two sheets; the first is kills and is used to track every successful kill,
394
--- with both where and when the kill happened. It has one index, a compound index tracking the combination of name and area.
395
--- The second sheet has two indexes, but one is unique: it isn't possible to add two items to the enemies sheet with the same name.
396
--- <pre>
397
--- local mydb = db:create("combat_log",
398
--- {
399
--- kills = {
400
--- name = "",
401
--- area = "",
402
--- killed = db:Timestamp("CURRENT_TIMESTAMP"),
403
--- _index = {{"name", "area"}}
404
--- },
405
--- enemies = {
406
--- name = "",
407
--- city = "",
408
--- reason = "",
409
--- enemied = db:Timestamp("CURRENT_TIMESTAMP"),
410
--- _index = { "city" },
411
--- _unique = { "name" },
412
--- _violations = "IGNORE"
413
--- }
414
--- }
415
--- )
416
--- </pre>
417
--- Note that you have to use double {{ }} if you have composite index/unique constrain.
418
function db:create(db_name, sheets)
419
if not db.__env then
420
db.__env = luasql.sqlite3()
421
end
422
423
db_name = db:safe_name(db_name)
424
425
if not db.__conn[db_name] then
426
db.__conn[db_name] = db.__env:connect(getMudletHomeDir() .. "/Database_" .. db_name .. ".db")
427
db.__conn[db_name]:setautocommit(false)
428
db.__autocommit[db_name] = true
429
end
430
431
db.__schema[db_name] = {}
432
433
-- We need to separate the actual column configuration from the meta-configuration of the desired
434
-- sheet. {sheet={"column"}} verses {sheet={"column"}, _index={"column"}}. In the former we are
435
-- creating a database with a single field; in the latter we are also adding an index on that
436
-- field. The db package reserves any key that begins with an underscore to be special and syntax
437
-- for its own use.
438
for s_name, sht in pairs(sheets) do
439
options = {}
440
441
if sht[1] ~= nil then
442
t = {}
443
for k, v in pairs(sht) do
444
t[v] = nil
445
end
446
sht = t
447
else
448
for k, v in pairs(sht) do
449
if string.starts(k, "_") then
450
options[k] = v
451
sht[k] = nil
452
end
453
end
454
end
455
456
if not options._violations then
457
options._violations = "FAIL"
458
end
459
460
db.__schema[db_name][s_name] = {columns=sht, options=options}
461
db:_migrate(db_name, s_name)
462
end
463
return db:get_database(db_name)
464
end
465
466
467
468
-- NOT LUADOC
469
-- The migrate function is meant to upgrade an existing database live, to maintain a consistant
470
-- and correct set of sheets and fields, along with their indexes. It should be safe to run
471
-- at any time, and must not cause any data loss. It simply adds to what is there: in perticular
472
-- it is not capable of removing indexes, columns, or sheets after they have been defined.
473
function db:_migrate(db_name, s_name)
474
local conn = db.__conn[db_name]
475
local schema = db.__schema[db_name][s_name]
476
477
local current_columns = {}
478
479
-- The PRAGMA table_info command is a query which returns all of the columns currently
480
-- defined in the specified table. The purpose of this section is to see if any new columns
481
-- have been added.
482
local cur = conn:execute("PRAGMA table_info('"..s_name.."')")
483
484
if cur ~= 0 then
485
local row = cur:fetch({}, "a")
486
487
while row do
488
current_columns[row.name:lower()] = row.type
489
row = cur:fetch({}, "a")
490
end
491
cur:close()
492
end
493
494
-- The SQL definition of a column is:
495
-- "column_name" column_type NULL
496
-- The db module does not presently support columns that are required. Everything is optional,
497
-- everything may be NULL / nil.
498
-- If you specify a column's type, you also specify its default value.
499
local sql_column = ', "%s" %s NULL'
500
local sql_column_default = sql_column..' DEFAULT %s'
501
502
if table.is_empty(current_columns) then
503
-- At this point, we know that the specified table does not exist in the database and so we
504
-- should create it.
505
506
-- Every sheet has an implicit _row_id column. It is not presently (and likely never will be)
507
-- supported to define the primary key of any sheet.
508
local sql_chunks = {"CREATE TABLE ", s_name, '("_row_id" INTEGER PRIMARY KEY AUTOINCREMENT'}
509
510
-- We iterate over every defined column, and add a line which creates it.
511
for key, value in pairs(schema.columns) do
512
local sql = ""
513
if value == nil then
514
sql = sql_column:format(key:lower(), db:_sql_type(value))
515
else
516
sql = sql_column_default:format(key:lower(), db:_sql_type(value), db:_sql_convert(value))
517
end
518
sql_chunks[#sql_chunks+1] = sql
519
end
520
521
sql_chunks[#sql_chunks+1] = ")"
522
523
local sql = table.concat(sql_chunks, "")
524
db:echo_sql(sql)
525
conn:execute(sql)
526
527
else
528
-- At this point we know that the sheet already exists, but we are concerned if the current
529
-- definition includes columns which may be added.
530
531
local sql_chunks = {}
532
local sql_add = 'ALTER TABLE %s ADD COLUMN "%s" %s NULL DEFAULT %s'
533
534
for k, v in pairs(schema.columns) do
535
k = k:lower()
536
t = db:_sql_type(v)
537
v = db:_sql_convert(v)
538
539
-- Here we test it a given column exists in the sheet already, and if not, we add that
540
-- column.
541
if not current_columns[k] then
542
local sql = sql_add:format(s_name, k, t, v)
543
conn:execute(sql)
544
db:echo_sql(sql)
545
end
546
end
547
end
548
549
-- On every invocation of db:create we run the code that creates indexes, as that code will
550
-- do nothing if the specific indexes already exist. This is enforced by the db:_index_name
551
-- function creating a unique index.
552
--
553
-- Note that in no situation will an existing index be deleted.
554
db:_migrate_indexes(conn, s_name, schema, current_columns)
555
db:echo_sql("COMMIT")
556
conn:commit()
557
conn:execute("VACUUM")
558
end
559
560
561
562
-- NOT LUADOC
563
-- Creates any indexes which do not yet exist in the given database.
564
function db:_migrate_indexes(conn, s_name, schema, current_columns)
565
local sql_create_index = "CREATE %s IF NOT EXISTS %s ON %s (%s);"
566
local opt = {_unique = "UNIQUE INDEX", _index = "INDEX"} -- , _check = "CHECK"}
567
568
for option_type, options in pairs(schema.options) do
569
if option_type == "_unique" or option_type == "_index" then
570
for _, value in pairs(options) do
571
572
-- If an index references a column which does not presently exist within the schema
573
-- this will fail.
574
575
if db:_index_valid(current_columns, value) then
576
--assert(db:_index_valid(current_columns, value),
577
-- "In sheet "..s_name.." an index field is specified that does not exist.")
578
579
sql = sql_create_index:format(
580
opt[option_type], db:_index_name(s_name, value), s_name, db:_sql_columns(value)
581
)
582
db:echo_sql(sql)
583
conn:execute(sql)
584
end
585
end
586
end
587
end
588
end
589
590
591
592
--- Adds one or more new rows to the specified sheet. If any of these rows would violate a UNIQUE index,
593
--- a lua error will be thrown and execution will cancel. As such it is advisable that if you use a UNIQUE
594
--- index, you test those values before you attempt to insert a new row. <br/><br/>
595
---
596
--- Each table is a series of key-value pairs to set the values of the sheet, but if any keys do not exist
597
--- then they will be set to nil or the default value. As you can see, all fields are optional.
598
---
599
--- @usage Adding one record.
600
--- <pre>
601
--- db:add(mydb.enemies, {name="Bob Smith", city="San Francisco"})
602
--- </pre>
603
--- @usage Adding multiple records.
604
--- <pre>
605
--- db:add(mydb.enemies,
606
--- {name="John Smith", city="San Francisco"},
607
--- {name="Jane Smith", city="San Francisco"},
608
--- {name="Richard Clark"}
609
--- )
610
--- </pre>
611
function db:add(sheet, ...)
612
local db_name = sheet._db_name
613
local s_name = sheet._sht_name
614
assert(s_name, "First argument to db:add must be a proper Sheet object.")
615
616
local conn = db.__conn[db_name]
617
local sql_insert = "INSERT OR %s INTO %s %s VALUES %s"
618
619
for _, t in ipairs({...}) do
620
if t._row_id then
621
-- You are not permitted to change a _row_id
622
t._row_id = nil
623
end
624
625
local sql = sql_insert:format(db.__schema[db_name][s_name].options._violations, s_name, db:_sql_fields(t), db:_sql_values(t))
626
db:echo_sql(sql)
627
assert(conn:execute(sql), "Failed to add item: this is probably a violation of a UNIQUE index or other constraint.")
628
end
629
if db.__autocommit[db_name] then
630
conn:commit()
631
end
632
end
633
634
635
636
--- Execute SQL select query against database. This only useful for some very specific cases. <br/>
637
--- Use db:fetch if possible instead - this function should not be normally used!
638
---
639
--- @release post Mudlet 1.1.1 (<b><u>TODO update before release</u></b>)
640
---
641
--- @usage Following will select all distinct area from my kills DB.
642
--- <pre>
643
--- db:fetch_sql(mydb.kills, "SELECT distinct area FROM kills")
644
--- </pre>
645
---
646
--- @see db:fetch
647
function db:fetch_sql(sheet, sql)
648
local db_name = sheet._db_name
649
local conn = db.__conn[db_name]
650
651
db:echo_sql(sql)
652
local cur = conn:execute(sql)
653
654
if cur ~= 0 then
655
local results = {}
656
local row = cur:fetch({}, "a")
657
658
while row do
659
results[#results+1] = db:_coerce_sheet(sheet, row)
660
row = cur:fetch({}, "a")
661
end
662
cur:close()
663
return results
664
else
665
return nil
666
end
667
end
668
669
670
671
--- Returns a table array containing a table for each matching row in the specified sheet. All arguments
672
--- but sheet are optional. If query is nil, the entire contents of the sheet will be returned. <br/><br/>
673
---
674
--- Query is a string which should be built by calling the various db: expression functions, such as db:eq,
675
--- db:AND, and such. You may pass a SQL WHERE clause here if you wish, but doing so is very dangerous.
676
--- If you don't know SQL well, its best to build the expression.<br/><br/>
677
---
678
--- Query may also be a table array of such expressions, if so they will be AND'd together implicitly.<br/><br/>
679
---
680
--- The results that are returned are not in any guaranteed order, though they are usually the same order
681
--- as the records were inserted. If you want to rely on the order in any way, you must pass a value to the
682
--- order_by field. This must be a table array listing the columns you want to sort by.
683
--- It can be { "column1" }, or { "column1", "column2" } <br/><br/>
684
---
685
--- The results are returned in ascending (smallest to largest) order; to reverse this pass true into the final field.
686
---
687
--- @usage The first will fetch all of your enemies, sorted first by the city they reside in and then by their name.
688
--- <pre>
689
--- db:fetch(mydb.enemies, nil, {"city", "name"})
690
--- </pre>
691
--- @usage The second will fetch only the enemies which are in San Francisco.
692
--- <pre>
693
--- db:fetch(mydb.enemies, db:eq(mydb.enemies.city, "San Francisco"))
694
--- </pre>
695
--- @usage The third will fetch all the things you've killed in Undervault which have Drow in their name.
696
--- <pre>
697
--- db:fetch(mydb.kills,
698
--- {
699
--- db:eq(mydb.kills.area, "Undervault"),
700
--- db:like(mydb.kills.name, "%Drow%")
701
--- }
702
--- )
703
--- </pre>
704
---
705
--- @see db:fetch_sql
706
function db:fetch(sheet, query, order_by, descending)
707
local s_name = sheet._sht_name
708
709
local sql = "SELECT * FROM "..s_name
710
711
if query then
712
if type(query) == "table" then
713
sql = sql.." WHERE "..db:AND(unpack(query))
714
else
715
sql = sql.." WHERE "..query
716
end
717
end
718
719
if order_by then
720
local o = {}
721
for _, v in ipairs(order_by) do
722
assert(v.name, "You must pass field instances (as obtained from yourdb.yoursheet.yourfield) to sort.")
723
o[#o+1] = v.name
724
end
725
726
sql = sql.." ORDER BY "..db:_sql_columns(o)
727
728
if descending then
729
sql = sql.." DESC"
730
end
731
end
732
733
return db:fetch_sql(sheet, sql)
734
end
735
736
737
738
--- Returns the result of calling the specified aggregate function on the field and its sheet. <br/><br/>
739
---
740
--- The supported aggregate functions are:
741
--- <pre>
742
--- COUNT - Returns the total number of records that are in the sheet or match the query.
743
--- AVG - Returns the average of all the numbers in the specified field.
744
--- MAX - Returns the highest number in the specified field.
745
--- MIN - Returns the lowest number in the specified field.
746
--- TOTAL - Returns the value of adding all the contents of the specified field.
747
--- </pre>
748
---
749
--- @param query optional
750
---
751
--- @usage Example:
752
--- <pre>
753
--- local mydb = db:get_database("my database")
754
--- echo(db:aggregate(mydb.enemies.name, "count"))
755
--- </pre>
756
function db:aggregate(field, fn, query)
757
local db_name = field.database
758
local s_name = field.sheet
759
local conn = db.__conn[db_name]
760
761
assert(type(field) == "table", "Field must be a field reference.")
762
assert(field.name, "Field must be a real field reference.")
763
764
local sql_chunks = {"SELECT", fn, "(", field.name, ")", "AS", fn, "FROM", s_name}
765
766
if query then
767
if type(query) == "table" then
768
sql_chunks[#sql_chunks+1] = db:AND(unpack(query))
769
else
770
sql_chunks[#sql_chunks+1] = query
771
end
772
end
773
774
if order_by then
775
local o = {}
776
for _, v in ipairs(order_by) do
777
assert(v.name, "You must pass field instances (as obtained from yourdb.yoursheet.yourfield) to sort.")
778
o[#o+1] = v.name
779
end
780
781
sql_chunks[#sql_chunks+1] = "ORDER BY"
782
sql_chunks[#sql_chunks+1] = db:_sql_columns(o)
783
784
if descending then
785
sql_chunks[#sql_chunks+1] = "DESC"
786
end
787
end
788
789
local sql = table.concat(sql_chunks, " ")
790
791
db:echo_sql(sql)
792
local cur = conn:execute(sql)
793
794
if cur ~= 0 then
795
local row = cur:fetch({}, "a")
796
local count = row[fn]
797
cur:close()
798
return count
799
else
800
return 0
801
end
802
end
803
804
805
806
--- Deletes rows from the specified sheet. The argument for query tries to be intelligent: <br/>
807
--- * if it is a simple number, it deletes a specific row by _row_id <br/>
808
--- * if it is a table that contains a _row_id (e.g., a table returned by db:get) it deletes just that record. <br/>
809
--- * Otherwise, it deletes every record which matches the query pattern which is specified as with db:get. <br/>
810
--- * If the query is simply true, then it will truncate the entire contents of the sheet. <br/>
811
---
812
--- @usage When passed an actual result table that was obtained from db:fetch, it will delete the record for that table.
813
--- <pre>
814
--- enemies = db:fetch(mydb.enemies)
815
--- db:delete(mydb.enemies, enemies[1])
816
--- </pre>
817
--- @usage When passed a number, will delete the record for that _row_id. This example shows getting the row id from a table.
818
--- <pre>
819
--- enemies = db:fetch(mydb.enemies)
820
--- db:delete(mydb.enemies, enemies[1]._row_id)
821
--- </pre>
822
--- @usage As above, but this example just passes in the row id directly.
823
--- <pre>
824
--- db:delete(mydb.enemies, 5)
825
--- </pre>
826
--- @usage Here, we will delete anything which matches the same kind of query as db:fetch uses - namely,
827
--- anyone who is in the city of San Francisco.
828
--- <pre>
829
--- db:delete(mydb.enemies, db:eq(mydb.enemies.city, "San Francisco"))
830
--- </pre>
831
--- @usage And finally, we will delete the entire contents of the enemies table.
832
--- <pre>
833
--- db:delete(mydb.enemies, true)
834
--- </pre>
835
function db:delete(sheet, query)
836
local db_name = sheet._db_name
837
local s_name = sheet._sht_name
838
839
local conn = db.__conn[db_name]
840
841
assert(query, "must pass a query argument to db:delete()")
842
if type(query) == "number" then
843
query = "_row_id = "..tostring(query)
844
elseif type(query) == "table" then
845
assert(query._row_id, "Passed a non-result table to db:delete, need a _row_id field to continue.")
846
query = "_row_id = "..tostring(query._row_id)
847
end
848
849
local sql = "DELETE FROM "..s_name
850
851
if query ~= true then
852
sql = sql.." WHERE "..query
853
end
854
855
db:echo_sql(sql)
856
assert(conn:execute(sql))
857
if db.__autocommit[db_name] then
858
conn:commit()
859
end
860
end
861
862
863
864
--- Merges the specified table array into the sheet, modifying any existing rows and adding any that don't exist.
865
---
866
--- This function is a convenience utility that allows you to quickly modify a sheet, changing
867
--- existing rows and add new ones as appropriate. It ONLY works on sheets which have a unique
868
--- index, and only when that unique index is only on a single field. For more complex situations
869
--- you'll have to do the logic yourself.
870
---
871
--- The table array may contain tables that were either returned previously by db:fetch, or new tables
872
--- that you've constructed with the correct fields, or any mix of both. Each table must have a value
873
--- for the unique key that has been set on this sheet.
874
---
875
--- @usage For example, consider this database:
876
--- <pre>
877
--- local mydb = db:create("peopledb",
878
--- {
879
--- friends = {
880
--- name = "",
881
--- race = "",
882
--- level = 0,
883
--- city = "",
884
--- _index = { "city" },
885
--- _unique = { "name" }
886
--- }
887
--- }
888
--- )
889
--- </pre>
890
---
891
--- Here you have a database with one sheet, which contains your friends, their race, level,
892
--- and what city they live in. Let's say you want to fetch everyone who lives in San Francisco, you could do:
893
--- <pre>
894
--- local results = db:fetch(mydb.friends, db:eq(mydb.friends.city, "San Francisco"))
895
--- </pre>
896
---
897
--- The tables in results are static, any changes to them are not saved back to the database.
898
--- But after a major radioactive cataclysm rendered everyone in San Francisco a mutant,
899
--- you could make changes to the tables as so:
900
--- <pre>
901
--- for _, friend in ipairs(results) do
902
--- friend.race = "Mutant"
903
--- end
904
--- </pre>
905
---
906
--- If you are also now aware of a new arrival in San Francisco, you could add them to that existing table array:
907
--- <pre>
908
--- results[#results+1] = {name="Bobette", race="Mutant", city="San Francisco"}
909
--- </pre>
910
---
911
--- And commit all of these changes back to the database at once with:
912
--- <pre>
913
--- db:merge_unique(mydb.friends, results)
914
--- </pre>
915
---
916
--- The db:merge_unique function will change the 'city' values for all the people who we previously fetched, but then add a new record as well.
917
function db:merge_unique(sheet, tables)
918
local db_name = sheet._db_name
919
local s_name = sheet._sht_name
920
921
local unique_options = db.__schema[db_name][s_name].options._unique
922
assert(unique_options, "db:merge_unique only works on a sheet with a unique index.")
923
assert(#unique_options == 1, "db:merge_unique only works on a sheet with a single unique index.")
924
925
local unique_index = unique_options[1]
926
local unique_key = ""
927
if type(unique_index) == "table" then
928
assert(#unique_index == 1, "db:merge_unique currently only supports sheets with a single unique index with a single column.")
929
unique_key = unique_index[1]
930
else
931
unique_key = unique_index
932
end
933
934
db:echo_sql(":: Unique index = "..unique_key)
935
936
local conn = db.__conn[db_name]
937
local mydb = db:get_database(db_name)
938
mydb:_begin()
939
940
for _, tbl in ipairs(tables) do
941
assert(tbl[unique_key], "attempting to db:merge_unique with a table that does not have the unique key.")
942
943
local results = db:fetch(sheet, db:eq(sheet[unique_key], tbl[unique_key]))
944
if results and results[1] then
945
local t = results[1]
946
for k, v in pairs(tbl) do
947
t[k] = v
948
end
949
950
db:update(sheet, t)
951
else
952
db:add(sheet, tbl)
953
end
954
end
955
956
mydb:_commit()
957
mydb:_end()
958
end
959
960
961
962
--- This function updates a row in the specified sheet, but only accepts a row which has been previously
963
--- obtained by db:fetch. Its primary purpose is that if you do a db:fetch, then change the value of a field
964
--- or tow, you can save back that table.
965
---
966
--- @usage This obtains a database reference, and queries the friends sheet for someone named Bob. As this
967
--- returns a table array containing only one item, it assigns that one item to the local variable named bob.
968
--- We then change the notes on Bob, and pass it into db:update() to save the changes back.
969
--- <pre>
970
--- local mydb = db:get_database("my database")
971
--- local bob = db:fetch(mydb.friends, db:eq(mydb.friends.name, "Bob"))[1]
972
--- bob.notes = "He's a really awesome guy."
973
--- db:update(mydb.friends, bob)
974
--- </pre>
975
function db:update(sheet, tbl)
976
assert(tbl._row_id, "Can only update a table with a _row_id")
977
assert(not table.is_empty(tbl), "An empty table was passed to db:update")
978
979
local db_name = sheet._db_name
980
local s_name = sheet._sht_name
981
982
local conn = db.__conn[db_name]
983
984
local sql_chunks = {"UPDATE OR", db.__schema[db_name][s_name].options._violations, s_name, "SET"}
985
986
local set_chunks = {}
987
local set_block = [["%s" = %s]]
988
989
for k, v in pairs(db.__schema[db_name][s_name]['columns']) do
990
if tbl[k] then
991
local field = sheet[k]
992
set_chunks[#set_chunks+1] = set_block:format(k, db:_coerce(field, tbl[k]))
993
end
994
end
995
996
sql_chunks[#sql_chunks+1] = table.concat(set_chunks, ",")
997
sql_chunks[#sql_chunks+1] = "WHERE _row_id = "..tbl._row_id
998
999
local sql = table.concat(sql_chunks, " ")
1000
db:echo_sql(sql)
1001
assert(conn:execute(sql))
1002
if db.__autocommit[db_name] then
1003
conn:commit()
1004
end
1005
end
1006
1007
1008
1009
--- The db:set function allows you to set a certain field to a certain value across an entire sheet.
1010
--- Meaning, you can change all of the last_read fields in the sheet to a certain value, or possibly only
1011
--- the last_read fields which are in a certain city. The query argument can be any value which is appropriate
1012
--- for db:fetch, even nil which will change the value for the specified column for EVERY row in the sheet.
1013
---
1014
--- For example, consider a situation in which you are tracking how many times you find a certain
1015
--- type of egg during Easter. You start by setting up your database and adding an Eggs sheet, and
1016
--- then adding a record for each type of egg.
1017
--- <pre>
1018
--- local mydb = db:create("egg database", {eggs = {color = "", last_found = db.Timestamp(false), found = 0}})
1019
--- db:add(mydb.eggs,
1020
--- {color = "Red"},
1021
--- {color = "Blue"},
1022
--- {color = "Green"},
1023
--- {color = "Yellow"},
1024
--- {color = "Black"}
1025
--- )
1026
--- </pre>
1027
---
1028
--- Now, you have three columns. One is a string, one a timestamp (that ends up as nil in the database),
1029
--- and one is a number. <br/><br/>
1030
---
1031
--- You can then set up a trigger to capture from the mud the string, "You pick up a (.*) egg!", and you
1032
--- end up arranging to store the value of that expression in a variable called "myegg". <br/><br/>
1033
---
1034
--- To increment how many we found, we will do this:
1035
--- <pre>
1036
--- myegg = "Red" -- We will pretend a trigger set this.
1037
--- db:set(mydb.eggs.found, db:exp("found + 1"), db:eq(mydb.eggs.color, myegg))
1038
--- db:set(mydb.eggs.last_found, db.Timestamp("CURRENT_TIMESTAMP"), db:eq(mydb.eggs.color, myegg))
1039
--- </pre>
1040
---
1041
--- This will go out and set two fields in the Red egg sheet; the first is the found field, which will
1042
--- increment the value of that field (using the special db:exp function). The second will update the
1043
--- last_found field with the current time. <br/><br/>
1044
---
1045
--- Once this contest is over, you may wish to reset this data but keep the database around.
1046
--- To do that, you may use a more broad use of db:set as such:
1047
--- <pre>
1048
--- db:set(mydb.eggs.found, 0)
1049
--- db:set(mydb.eggs.last_found, nil)
1050
--- </pre>
1051
function db:set(field, value, query)
1052
local db_name = sheet._db_name
1053
local s_name = sheet._sht_name
1054
1055
local conn = db.__conn[db_name]
1056
1057
local sql_update = [[UPDATE OR %s %s SET "%s" = %s]]
1058
if query then
1059
sql_update = sql_update .. [[ WHERE %s]]
1060
end
1061
1062
local sql = sql_update:format(db.__schema[db_name][s_name].options._violations, s_name, field.name, db:_coerce(field, value), query)
1063
1064
db:echo_sql(sql)
1065
assert(conn:execute(sql))
1066
if db.__autocommit[db_name] then
1067
conn:commit()
1068
end
1069
end
1070
1071
1072
1073
--- This is a debugging function, which echos any SQL commands if db.debug_sql is true.
1074
--- You should not call this function directly from Mudlet.
1075
---
1076
--- @usage Set following lua variable to enable SQL echos.
1077
--- <pre>
1078
--- db.debug_sql=true
1079
--- </pre>
1080
function db:echo_sql(sql)
1081
if db.debug_sql then
1082
echo("\n"..sql.."\n")
1083
end
1084
end
1085
1086
1087
1088
-- NOT LUADOC
1089
-- After a table so retrieved from the database, this function coerces values to
1090
-- their proper types. Specifically, numbers and datetimes become the proper
1091
-- types.
1092
function db:_coerce_sheet(sheet, tbl)
1093
if tbl then
1094
tbl._row_id = tonumber(tbl._row_id)
1095
1096
for k, v in pairs(tbl) do
1097
if k ~= "_row_id" then
1098
local field = sheet[k]
1099
if field.type == "number" then
1100
tbl[k] = tonumber(tbl[k]) or tbl[k]
1101
elseif field.type == "datetime" then
1102
tbl[k] = db:Timestamp(datetime:parse(tbl[k], nil, true))
1103
end
1104
end
1105
end
1106
return tbl
1107
end
1108
end
1109
1110
1111
1112
-- NOT LUADOC
1113
-- The function converts a Lua value into its SQL representation, depending on the
1114
-- type of the specified field. Strings will be single-quoted (and single-quotes
1115
-- within will be properly escaped), numbers will be rendered properly, and such.
1116
function db:_coerce(field, value)
1117
if field.type == "number" then
1118
return tonumber(value) or "'"..value.."'"
1119
elseif field.type == "datetime" then
1120
if value._timestamp == false then
1121
return "NULL"
1122
else
1123
return tonumber(value._timestamp) or "'"..value.."'"
1124
end
1125
else
1126
return "'"..tostring(value):gsub("'", "''").."'"
1127
end
1128
end
1129
1130
1131
1132
--- Returns a database expression to test if the field in the sheet is equal to the value.
1133
---
1134
--- @see db:fetch
1135
function db:eq(field, value, case_insensitive)
1136
if case_insensitive then
1137
local v = db:_coerce(field, value):lower()
1138
return "lower("..field.name..") == "..v
1139
else
1140
local v = db:_coerce(field, value)
1141
return field.name.." == "..v
1142
end
1143
end
1144
1145
1146
1147
--- Returns a database expression to test if the field in the sheet is NOT equal to the value.
1148
---
1149
--- @see db:fetch
1150
function db:not_eq(field, value, case_insensitive)
1151
if case_insensitive then
1152
local v = db:_coerce(field, value):lower()
1153
return "lower("..field.name..") != "..v
1154
else
1155
local v = db:_coerce(field, value)
1156
return field.name.." != "..v
1157
end
1158
end
1159
1160
1161
1162
--- Returns a database expression to test if the field in the sheet is less than the value.
1163
---
1164
--- @see db:fetch
1165
function db:lt(field, value)
1166
local v = db:_coerce(field, value)
1167
return field.name.." < "..v
1168
end
1169
1170
1171
1172
--- Returns a database expression to test if the field in the sheet is less than or equal to the value.
1173
---
1174
--- @see db:fetch
1175
function db:lte(field, value)
1176
local v = db:_coerce(field, value)
1177
return field.name.." <= "..v
1178
end
1179
1180
1181
1182
--- Returns a database expression to test if the field in the sheet is greater than to the value.
1183
---
1184
--- @see db:fetch
1185
function db:gt(field, value)
1186
local v = db:_coerce(field, value)
1187
return field.name.." > "..v
1188
end
1189
1190
1191
1192
--- Returns a database expression to test if the field in the sheet is greater than or equal to the value.
1193
---
1194
--- @see db:fetch
1195
function db:gte(field, value)
1196
local v = db:_coerce(field, value)
1197
return field.name.." >= "..v
1198
end
1199
1200
1201
1202
--- Returns a database expression to test if the field in the sheet is nil.
1203
---
1204
--- @see db:fetch
1205
function db:is_nil(field)
1206
return field.name.." IS NULL"
1207
end
1208
1209
1210
1211
--- Returns a database expression to test if the field in the sheet is not nil.
1212
---
1213
--- @see db:fetch
1214
function db:is_not_nil(field)
1215
return field.name.." IS NOT NULL"
1216
end
1217
1218
1219
1220
--- Returns a database expression to test if the field in the sheet matches the specified pattern. <br/><br/>
1221
---
1222
--- LIKE patterns are not case-sensitive, and allow two wild cards. The first is an underscore which matches
1223
--- any single one character. The second is a percent symbol which matches zero or more of any character.
1224
--- <pre>
1225
--- LIKE with "_" is therefore the same as the "." regular expression.
1226
--- LIKE with "%" is therefore the same as ".*" regular expression.
1227
--- </pre>
1228
---
1229
--- @see db:not_like
1230
--- @see db:fetch
1231
function db:like(field, value)
1232
local v = db:_coerce(field, value)
1233
return field.name.." LIKE "..v
1234
end
1235
1236
1237
1238
--- Returns a database expression to test if the field in the sheet does not match the specified pattern.
1239
---
1240
--- LIKE patterns are not case-sensitive, and allow two wild cards. The first is an underscore which matches
1241
--- any single one character. The second is a percent symbol which matches zero or more of any character.
1242
--- <pre>
1243
--- LIKE with "_" is therefore the same as the "." regular expression.
1244
--- LIKE with "%" is therefore the same as ".*" regular expression.
1245
--- </pre>
1246
---
1247
--- @see db:like
1248
--- @see db:fetch
1249
function db:not_like(field, value)
1250
local v = db:_coerce(field, value)
1251
return field.name.." NOT LIKE "..v
1252
end
1253
1254
1255
1256
--- Returns a database expression to test if the field in the sheet is a value between lower_bound and upper_bound.
1257
--- This only really makes sense for numbers and Timestamps.
1258
---
1259
--- @see db:not_between
1260
--- @see db:fetch
1261
function db:between(field, left_bound, right_bound)
1262
local x = db:_coerce(field, left_bound)
1263
local y = db:_coerce(field, right_bound)
1264
return field.name.." BETWEEN "..x.." AND "..y
1265
end
1266
1267
1268
1269
--- Returns a database expression to test if the field in the sheet is NOT a value between lower_bound and upper_bound.
1270
--- This only really makes sense for numbers and Timestamps.
1271
---
1272
--- @see db:between
1273
--- @see db:fetch
1274
function db:not_between(field, left_bound, right_bound)
1275
local x = db:_coerce(field, left_bound)
1276
local y = db:_coerce(field, right_bound)
1277
return field.name.." NOT BETWEEN "..x.." AND "..y
1278
end
1279
1280
1281
1282
--- Returns a database expression to test if the field in the sheet is one of the values in the table array. <br/><br/>
1283
---
1284
--- First, note the trailing underscore carefully! It is required.
1285
---
1286
--- @usage The following example illustrates the use of <b>in_</b>:
1287
--- This will obtain all of your kills which happened in the Undervault, Hell or Purgatory. Every db:in_ expression
1288
--- can be written as a db:OR, but that quite often gets very complex.
1289
--- <pre>
1290
--- local mydb = db:get_database("my database")
1291
--- local areas = {"Undervault", "Hell", "Purgatory"}
1292
--- db:fetch(mydb.kills, db:in_(mydb.kills.area, areas))
1293
--- </pre>
1294
---
1295
--- @see db:fetch
1296
function db:in_(field, tbl)
1297
local parts = {}
1298
for _, v in ipairs(tbl) do
1299
parts[#parts+1] = db:_coerce(field, v)
1300
end
1301
1302
return field.name.." IN ("..table.concat(parts, ",")..")"
1303
end
1304
1305
1306
1307
--- Returns a database expression to test if the field in the sheet is not one of the values in the table array.
1308
---
1309
--- @see db:in_
1310
--- @see db:fetch
1311
function db:not_in(field, tbl)
1312
local parts = {}
1313
for _, v in ipairs(tbl) do
1314
parts[#parts+1] = db:_coerce(field, v)
1315
end
1316
1317
return field.name.." NOT IN ("..table.concat(parts, ",")..")"
1318
end
1319
1320
1321
1322
--- Returns the string as-is to the database. <br/><br/>
1323
---
1324
--- Use this function with caution, but it is very useful in some circumstances. One of the most
1325
--- common of such is incrementing an existing field in a db:set() operation, as so:
1326
--- <pre>
1327
--- db:set(mydb.enemies, db:exp("kills + 1"), db:eq(mydb.enemies.name, "Ixokai"))
1328
--- </pre>
1329
---
1330
--- This will increment the value of the kills field for the row identified by the name Ixokai. <br/><br/>
1331
---
1332
--- But there are other uses, as the underlining database layer provides many functions you can call
1333
--- to do certain things. If you want to get a list of all your enemies who have a name longer then
1334
--- 10 characters, you may do:
1335
--- <pre>
1336
--- db:fetch(mydb.enemies, db:exp("length(name) > 10"))
1337
--- </pre>
1338
---
1339
--- Again, take special care with this, as you are doing SQL syntax directly and the library can't
1340
--- help you get things right.
1341
---
1342
--- @see db:fetch
1343
function db:exp(text)
1344
return text
1345
end
1346
1347
1348
1349
--- Returns a compound database expression that combines all of the simple expressions passed into it.
1350
--- These expressions should be generated with other db: functions such as db:eq, db:like, db:lt and the like. <br/><br/>
1351
---
1352
--- This compound expression will only find items in the sheet if all sub-expressions match.
1353
---
1354
--- @see db:fetch
1355
function db:AND(...)
1356
local parts = {}
1357
1358
for _, expression in ipairs({...}) do
1359
parts[#parts+1] = "("..expression..")"
1360
end
1361
1362
return "("..table.concat(parts, " AND ")..")"
1363
end
1364
1365
1366
1367
--- Returns a compound database expression that combines both of the simple expressions passed into it.
1368
--- These expressions should be generated with other db: functions such as db:eq, db:like, db:lt and the like. <br/><br/>
1369
---
1370
--- This compound expression will find any item that matches either the first or the second sub-expression.
1371
---
1372
--- @see db:fetch
1373
function db:OR(left, right)
1374
if not string.starts(left, "(") then
1375
left = "("..left..")"
1376
end
1377
1378
if not string.starts(right, "(") then
1379
right = "("..right..")"
1380
end
1381
1382
return left.." OR "..right
1383
end
1384
1385
1386
1387
--- <b><u>TODO</u></b>
1388
function db:close()
1389
for _, c in pairs(db.__conn) do
1390
c:close()
1391
end
1392
db.__env:close()
1393
end
1394
1395
1396
1397
-- Timestamp support
1398
db.__Timestamp = {}
1399
1400
1401
1402
db.__TimestampMT = {
1403
__index = db.__Timestamp
1404
}
1405
1406
1407
1408
function db.__Timestamp:as_string(format)
1409
if not format then
1410
format = "%m-%d-%Y %H:%M:%S"
1411
end
1412
1413
return os.date(format, self._timestamp)
1414
end
1415
1416
1417
1418
function db.__Timestamp:as_table()
1419
return os.date("*t", self._timestamp)
1420
end
1421
1422
1423
1424
function db.__Timestamp:as_number()
1425
return self._timestamp
1426
end
1427
1428
1429
1430
--- <b><u>TODO</u></b>
1431
function db:Timestamp(ts, fmt)
1432
local dt = {}
1433
if type(ts) == "table" then
1434
dt._timestamp = os.time(ts)
1435
elseif type(ts) == "number" then
1436
dt._timestamp = ts
1437
elseif type(ts) == "string" and
1438
assert(ts == "CURRENT_TIMESTAMP", "The only strings supported by db.DateTime:new is CURRENT_TIMESTAMP") then
1439
dt._timestamp = "CURRENT_TIMESTAMP"
1440
elseif ts == nil then
1441
dt._timestamp = false
1442
else
1443
assert(nil, "Invalid value passed to db.Timestamp()")
1444
end
1445
return setmetatable(dt, db.__TimestampMT)
1446
end
1447
1448
1449
1450
-- function db.Timestamp:new(ts, fmt)
1451
-- local dt = {}
1452
-- if type(ts) == "table" then
1453
-- dt._timestamp = os.time(ts)
1454
-- elseif type(ts) == "number" then
1455
-- dt._timestamp = ts
1456
-- elseif assert(ts == "CURRENT_TIMESTAMP", "The only strings supported by db.DateTime:new is CURRENT_TIMESTAMP") then
1457
-- dt._timestamp = "CURRENT_TIMESTAMP"
1458
-- end
1459
-- return setmetatable(dt, db.__TimestampMT)
1460
-- end
1461
1462
1463
1464
db.Field = {}
1465
db.__FieldMT = {
1466
__index = db.Field
1467
}
1468
1469
1470
1471
db.Sheet = {}
1472
db.__SheetMT = {
1473
__index = function(t, k)
1474
local v = rawget(db.Sheet, k)
1475
if v then
1476
return v
1477
end
1478
1479
local db_name = rawget(t, "_db_name")
1480
local sht_name = rawget(t, "_sht_name")
1481
local f_name = k:lower()
1482
1483
local errormsg = "Attempt to access field %s in sheet %s in database %s that does not exist."
1484
1485
local field = db.__schema[db_name][sht_name]['columns'][f_name]
1486
if assert(field, errormsg:format(k, sht_name, db_name)) then
1487
type_ = type(field)
1488
if type_ == "table" and field._timestamp then
1489
type_ = "datetime"
1490
end
1491
1492
rt = setmetatable({database=db_name, sheet=sht_name, type=type_, name=f_name}, db.__FieldMT)
1493
rawset(t,k,rt)
1494
return rt
1495
end
1496
1497
end
1498
}
1499
1500
1501
1502
db.Database = {}
1503
db.__DatabaseMT = {
1504
__index = function(t, k)
1505
local v = rawget(t, k)
1506
if v then
1507
return v
1508
end
1509
1510
local v = rawget(db.Database, k)
1511
if v then
1512
return v
1513
end
1514
1515
local db_name = rawget(t, "_db_name")
1516
if assert(db.__schema[db_name][k:lower()], "Attempt to access sheet '"..k:lower().."'in db '"..db_name.."' that does not exist.") then
1517
rt = setmetatable({_db_name = db_name, _sht_name = k:lower()}, db.__SheetMT)
1518
rawset(t,k,rt)
1519
return rt
1520
end
1521
end
1522
}
1523
1524
1525
1526
function db.Database:_begin()
1527
db.__autocommit[self._db_name] = false
1528
end
1529
1530
1531
1532
function db.Database:_commit()
1533
local conn = db.__conn[self._db_name]
1534
conn:commit()
1535
end
1536
1537
1538
1539
function db.Database:_rollback()
1540
local conn = db.__conn[self._db_name]
1541
conn:rollback()
1542
end
1543
1544
1545
1546
function db.Database:_end()
1547
db.__autocommit[self._db_name] = true
1548
end
1549
1550
1551
1552
function db.Database._drop(s_name)
1553
local conn = db.__conn[self._db_name]
1554
local schema = db.__schema[self._db_name]
1555
1556
if schema.options._index then
1557
for _, value in schema.options._index do
1558
conn:execute("DROP INDEX IF EXISTS " .. db:_index_name(s_name, value))
1559
end
1560
end
1561
1562
if schema.options._unique then
1563
for _, value in schema.options._unique do
1564
conn:execute("DROP INDEX IF EXISTS " .. db:_index_name(s_name, value))
1565
end
1566
end
1567
1568
conn:execute("DROP TABLE IF EXISTS "..s_name)
1569
conn:commit()
1570
end
1571
1572
1573
1574
--- Returns a reference of an already existing database. This instance can be used to get references
1575
--- to the sheets (and from there, fields) that are defined within the database. You use these
1576
--- references to construct queries. <br/><br/>
1577
---
1578
--- These references do not contain any actual data, they only point to parts of the database structure.
1579
---
1580
--- @usage If a database has a sheet named enemies, you can obtain a reference to that sheet by simply doing:
1581
--- <pre>
1582
--- local mydb = db:get_database("my database")
1583
--- local enemies_ref = mydb.enemies
1584
--- local name_ref = mydb.enemies.name
1585
--- </pre>
1586
function db:get_database(db_name)
1587
db_name = db:safe_name(db_name)
1588
assert(db.__schema[db_name], "Attempt to access database that does not exist.")
1589
1590
db_inst = {_db_name = db_name}
1591
return setmetatable(db_inst, db.__DatabaseMT)
1592
end
1593
Loggerhead is a web-based interface for Breezy
Version: 2.0.1