« back to all changes in this revision
Viewing changes to src/mudlet-lua/mudlet-lua-doc/files/DB.html
- 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/mudlet-lua-doc/files/DB.html
1
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
2
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
3
<html>
4
<head>
5
<title>Reference</title>
6
<link rel="stylesheet" href="../luadoc.css" type="text/css" />
7
<!--meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/-->
8
</head>
9
10
<body>
11
<div id="container">
12
13
<div id="product">
14
<div id="product_logo"></div>
15
<div id="product_name"><big><b></b></big></div>
16
<div id="product_description"></div>
17
</div> <!-- id="product" -->
18
19
<div id="main">
20
21
<div id="navigation">
22
23
24
<h1>LuaDoc</h1>
25
<ul>
26
27
<li><a href="../index.html">Index</a></li>
28
29
</ul>
30
31
32
<!-- Module list -->
33
34
35
36
<!-- File list -->
37
38
<h1>Files</h1>
39
<ul>
40
41
<li>
42
<a href="../files/CoreMudlet.html">CoreMudlet.lua</a>
43
</li>
44
45
<li><strong>DB.lua</strong></li>
46
47
<li>
48
<a href="../files/DebugTools.html">DebugTools.lua</a>
49
</li>
50
51
<li>
52
<a href="../files/GUIUtils.html">GUIUtils.lua</a>
53
</li>
54
55
<li>
56
<a href="../files/Logging.html">Logging.lua</a>
57
</li>
58
59
<li>
60
<a href="../files/LuaGlobal.html">LuaGlobal.lua</a>
61
</li>
62
63
<li>
64
<a href="../files/Other.html">Other.lua</a>
65
</li>
66
67
<li>
68
<a href="../files/StringUtils.html">StringUtils.lua</a>
69
</li>
70
71
<li>
72
<a href="../files/TableUtils.html">TableUtils.lua</a>
73
</li>
74
75
<li>
76
<a href="../files/geyser/Geyser.html">geyser/Geyser.lua</a>
77
</li>
78
79
<li>
80
<a href="../files/geyser/GeyserColor.html">geyser/GeyserColor.lua</a>
81
</li>
82
83
<li>
84
<a href="../files/geyser/GeyserContainer.html">geyser/GeyserContainer.lua</a>
85
</li>
86
87
<li>
88
<a href="../files/geyser/GeyserGauge.html">geyser/GeyserGauge.lua</a>
89
</li>
90
91
<li>
92
<a href="../files/geyser/GeyserGeyser.html">geyser/GeyserGeyser.lua</a>
93
</li>
94
95
<li>
96
<a href="../files/geyser/GeyserLabel.html">geyser/GeyserLabel.lua</a>
97
</li>
98
99
<li>
100
<a href="../files/geyser/GeyserMiniConsole.html">geyser/GeyserMiniConsole.lua</a>
101
</li>
102
103
<li>
104
<a href="../files/geyser/GeyserReposition.html">geyser/GeyserReposition.lua</a>
105
</li>
106
107
<li>
108
<a href="../files/geyser/GeyserSetConstraints.html">geyser/GeyserSetConstraints.lua</a>
109
</li>
110
111
<li>
112
<a href="../files/geyser/GeyserTests.html">geyser/GeyserTests.lua</a>
113
</li>
114
115
<li>
116
<a href="../files/geyser/GeyserUtil.html">geyser/GeyserUtil.lua</a>
117
</li>
118
119
<li>
120
<a href="../files/geyser/GeyserWindow.html">geyser/GeyserWindow.lua</a>
121
</li>
122
123
</ul>
124
125
126
127
128
129
</div> <!-- id="navigation" -->
130
131
<div id="content">
132
133
<h1>File <code>DB.lua</code></h1>
134
135
136
137
138
139
140
141
<h2>Functions</h2>
142
<table class="function_list">
143
144
<tr>
145
<td class="name" nowrap><a href="#datetime:parse">datetime:parse</a> (source, format, as_epoch)</td>
146
<td class="summary">Parses the specified source string, according to the format if given, to return a representation of the date/time.</td>
147
</tr>
148
149
<tr>
150
<td class="name" nowrap><a href="#db:AND">db:AND</a> (...)</td>
151
<td class="summary">Returns a compound database expression that combines all of the simple expressions passed into it.</td>
152
</tr>
153
154
<tr>
155
<td class="name" nowrap><a href="#db:OR">db:OR</a> (left, right)</td>
156
<td class="summary">Returns a compound database expression that combines both of the simple expressions passed into it.</td>
157
</tr>
158
159
<tr>
160
<td class="name" nowrap><a href="#db:Timestamp">db:Timestamp</a> (ts, fmt)</td>
161
<td class="summary"><b><u>TODO</u></b> </td>
162
</tr>
163
164
<tr>
165
<td class="name" nowrap><a href="#db:add">db:add</a> (sheet, ...)</td>
166
<td class="summary">Adds one or more new rows to the specified sheet.</td>
167
</tr>
168
169
<tr>
170
<td class="name" nowrap><a href="#db:aggregate">db:aggregate</a> (field, fn, query)</td>
171
<td class="summary">Returns the result of calling the specified aggregate function on the field and its sheet.</td>
172
</tr>
173
174
<tr>
175
<td class="name" nowrap><a href="#db:between">db:between</a> (field, left_bound, right_bound)</td>
176
<td class="summary">Returns a database expression to test if the field in the sheet is a value between lower_bound and upper_bound.</td>
177
</tr>
178
179
<tr>
180
<td class="name" nowrap><a href="#db:close">db:close</a> ()</td>
181
<td class="summary"><b><u>TODO</u></b> </td>
182
</tr>
183
184
<tr>
185
<td class="name" nowrap><a href="#db:create">db:create</a> (db_name, sheets)</td>
186
<td class="summary">Creates and/or modifies an existing database.</td>
187
</tr>
188
189
<tr>
190
<td class="name" nowrap><a href="#db:delete">db:delete</a> (sheet, query)</td>
191
<td class="summary">Deletes rows from the specified sheet.</td>
192
</tr>
193
194
<tr>
195
<td class="name" nowrap><a href="#db:echo_sql">db:echo_sql</a> (sql)</td>
196
<td class="summary">This is a debugging function, which echos any SQL commands if db.debug_sql is true.</td>
197
</tr>
198
199
<tr>
200
<td class="name" nowrap><a href="#db:eq">db:eq</a> (field, value, case_insensitive)</td>
201
<td class="summary">Returns a database expression to test if the field in the sheet is equal to the value.</td>
202
</tr>
203
204
<tr>
205
<td class="name" nowrap><a href="#db:exp">db:exp</a> (text)</td>
206
<td class="summary">Returns the string as-is to the database.</td>
207
</tr>
208
209
<tr>
210
<td class="name" nowrap><a href="#db:fetch">db:fetch</a> (sheet, query, order_by, descending)</td>
211
<td class="summary">Returns a table array containing a table for each matching row in the specified sheet.</td>
212
</tr>
213
214
<tr>
215
<td class="name" nowrap><a href="#db:fetch_sql">db:fetch_sql</a> (sheet, sql)</td>
216
<td class="summary">Execute SQL select query against database.</td>
217
</tr>
218
219
<tr>
220
<td class="name" nowrap><a href="#db:get_database">db:get_database</a> (db_name)</td>
221
<td class="summary">Returns a reference of an already existing database.</td>
222
</tr>
223
224
<tr>
225
<td class="name" nowrap><a href="#db:gt">db:gt</a> (field, value)</td>
226
<td class="summary">Returns a database expression to test if the field in the sheet is greater than to the value.</td>
227
</tr>
228
229
<tr>
230
<td class="name" nowrap><a href="#db:gte">db:gte</a> (field, value)</td>
231
<td class="summary">Returns a database expression to test if the field in the sheet is greater than or equal to the value.</td>
232
</tr>
233
234
<tr>
235
<td class="name" nowrap><a href="#db:in_">db:in_</a> (field, tbl)</td>
236
<td class="summary">Returns a database expression to test if the field in the sheet is one of the values in the table array.</td>
237
</tr>
238
239
<tr>
240
<td class="name" nowrap><a href="#db:is_nil">db:is_nil</a> (field)</td>
241
<td class="summary">Returns a database expression to test if the field in the sheet is nil.</td>
242
</tr>
243
244
<tr>
245
<td class="name" nowrap><a href="#db:is_not_nil">db:is_not_nil</a> (field)</td>
246
<td class="summary">Returns a database expression to test if the field in the sheet is not nil.</td>
247
</tr>
248
249
<tr>
250
<td class="name" nowrap><a href="#db:like">db:like</a> (field, value)</td>
251
<td class="summary">Returns a database expression to test if the field in the sheet matches the specified pattern.</td>
252
</tr>
253
254
<tr>
255
<td class="name" nowrap><a href="#db:lt">db:lt</a> (field, value)</td>
256
<td class="summary">Returns a database expression to test if the field in the sheet is less than the value.</td>
257
</tr>
258
259
<tr>
260
<td class="name" nowrap><a href="#db:lte">db:lte</a> (field, value)</td>
261
<td class="summary">Returns a database expression to test if the field in the sheet is less than or equal to the value.</td>
262
</tr>
263
264
<tr>
265
<td class="name" nowrap><a href="#db:merge_unique">db:merge_unique</a> (sheet, tables)</td>
266
<td class="summary">Merges the specified table array into the sheet, modifying any existing rows and adding any that don't exist.</td>
267
</tr>
268
269
<tr>
270
<td class="name" nowrap><a href="#db:not_between">db:not_between</a> (field, left_bound, right_bound)</td>
271
<td class="summary">Returns a database expression to test if the field in the sheet is NOT a value between lower_bound and upper_bound.</td>
272
</tr>
273
274
<tr>
275
<td class="name" nowrap><a href="#db:not_eq">db:not_eq</a> (field, value, case_insensitive)</td>
276
<td class="summary">Returns a database expression to test if the field in the sheet is NOT equal to the value.</td>
277
</tr>
278
279
<tr>
280
<td class="name" nowrap><a href="#db:not_in">db:not_in</a> (field, tbl)</td>
281
<td class="summary">Returns a database expression to test if the field in the sheet is not one of the values in the table array.</td>
282
</tr>
283
284
<tr>
285
<td class="name" nowrap><a href="#db:not_like">db:not_like</a> (field, value)</td>
286
<td class="summary">Returns a database expression to test if the field in the sheet does not match the specified pattern.</td>
287
</tr>
288
289
<tr>
290
<td class="name" nowrap><a href="#db:safe_name">db:safe_name</a> (name)</td>
291
<td class="summary"><b><u>TODO</u></b> db:safe_name(name) On a filesystem level, names are restricted to being alphanumeric only.</td>
292
</tr>
293
294
<tr>
295
<td class="name" nowrap><a href="#db:set">db:set</a> (field, value, query)</td>
296
<td class="summary">The db:set function allows you to set a certain field to a certain value across an entire sheet.</td>
297
</tr>
298
299
<tr>
300
<td class="name" nowrap><a href="#db:update">db:update</a> (sheet, tbl)</td>
301
<td class="summary">This function updates a row in the specified sheet, but only accepts a row which has been previously obtained by db:fetch.</td>
302
</tr>
303
304
</table>
305
306
307
308
309
310
311
<br/>
312
<br/>
313
314
315
316
317
<h2><a name="functions"></a>Functions</h2>
318
<dl class="function">
319
320
321
322
<dt><a name="datetime:parse"></a>
323
<b><big>datetime:parse </big></b>(source, format, as_epoch)</dt>
324
<dd>
325
326
Parses the specified source string, according to the format if given, to return a representation of the date/time. The default format if not specified is: "^%Y-%m-%d %H:%M:%S$" <br/><br/> If as_epoch is provided and true, the return value will be a Unix epoch -- the number of seconds since 1970. This is a useful format for exchanging date/times with other systems. If as_epoch is false, then a Lua time table will be returned. Details of the time tables are provided in the http://www.lua.org/pil/22.1.html. <br/><br/> Supported Format Codes </pre> %b Abbreviated Month Name %B Full Month Name %d Day of Month %H Hour (24-hour format) %I Hour (12-hour format, requires %p as well) %p AM or PM %m 2-digit month (01-12) %M 2-digit minutes (00-59) %S 2-digit seconds (00-59) %y 2-digit year (00-99), will automatically prepend 20 so 10 becomes 2010 and not 1910. %Y 4-digit year. </pre>
327
328
329
330
<br/>
331
332
333
<b>Parameters</b>
334
<ul>
335
336
<li>
337
source:
338
</li>
339
340
<li>
341
format:
342
</li>
343
344
<li>
345
as_epoch:
346
</li>
347
348
</ul>
349
350
351
352
353
354
355
356
357
</dd>
358
359
360
361
362
<dt><a name="db:AND"></a>
363
<b><big>db:AND </big></b>(...)</dt>
364
<dd>
365
366
Returns a compound database expression that combines all of the simple expressions passed into it. These expressions should be generated with other db: functions such as db:eq, db:like, db:lt and the like. <br/><br/> This compound expression will only find items in the sheet if all sub-expressions match.
367
368
369
370
<br/>
371
372
373
<b>Parameters</b>
374
<ul>
375
376
<li>
377
...:
378
</li>
379
380
</ul>
381
382
383
384
385
386
387
388
<b>See also:</b>
389
<ul>
390
391
<li><a href='DB.html#db:fetch'>db:fetch</a>
392
393
</ul>
394
395
396
</dd>
397
398
399
400
401
<dt><a name="db:OR"></a>
402
<b><big>db:OR </big></b>(left, right)</dt>
403
<dd>
404
405
Returns a compound database expression that combines both of the simple expressions passed into it. These expressions should be generated with other db: functions such as db:eq, db:like, db:lt and the like. <br/><br/> This compound expression will find any item that matches either the first or the second sub-expression.
406
407
408
409
<br/>
410
411
412
<b>Parameters</b>
413
<ul>
414
415
<li>
416
left:
417
</li>
418
419
<li>
420
right:
421
</li>
422
423
</ul>
424
425
426
427
428
429
430
431
<b>See also:</b>
432
<ul>
433
434
<li><a href='DB.html#db:fetch'>db:fetch</a>
435
436
</ul>
437
438
439
</dd>
440
441
442
443
444
<dt><a name="db:Timestamp"></a>
445
<b><big>db:Timestamp </big></b>(ts, fmt)</dt>
446
<dd>
447
448
<b><u>TODO</u></b>
449
450
451
452
<br/>
453
454
455
<b>Parameters</b>
456
<ul>
457
458
<li>
459
ts:
460
</li>
461
462
<li>
463
fmt:
464
</li>
465
466
</ul>
467
468
469
470
471
472
473
474
475
</dd>
476
477
478
479
480
<dt><a name="db:add"></a>
481
<b><big>db:add </big></b>(sheet, ...)</dt>
482
<dd>
483
484
Adds one or more new rows to the specified sheet. If any of these rows would violate a UNIQUE index, a lua error will be thrown and execution will cancel. As such it is advisable that if you use a UNIQUE index, you test those values before you attempt to insert a new row. <br/><br/> Each table is a series of key-value pairs to set the values of the sheet, but if any keys do not exist then they will be set to nil or the default value. As you can see, all fields are optional.
485
486
487
488
<br/>
489
490
491
<b>Parameters</b>
492
<ul>
493
494
<li>
495
sheet:
496
</li>
497
498
<li>
499
...:
500
</li>
501
502
</ul>
503
504
505
506
<b>Usage</b>
507
<ul>
508
509
<li>Adding one record. <pre> db:add(mydb.enemies, {name="Bob Smith", city="San Francisco"})
510
</pre>
511
512
<li>Adding multiple records. <pre> db:add(mydb.enemies,
513
{name="John Smith", city="San Francisco"},
514
{name="Jane Smith", city="San Francisco"},
515
{name="Richard Clark"}
516
)
517
</pre>
518
519
</ul>
520
521
522
523
524
525
526
</dd>
527
528
529
530
531
<dt><a name="db:aggregate"></a>
532
<b><big>db:aggregate </big></b>(field, fn, query)</dt>
533
<dd>
534
535
Returns the result of calling the specified aggregate function on the field and its sheet. <br/><br/> The supported aggregate functions are: <pre> COUNT - Returns the total number of records that are in the sheet or match the query.
536
AVG - Returns the average of all the numbers in the specified field.
537
MAX - Returns the highest number in the specified field.
538
MIN - Returns the lowest number in the specified field.
539
TOTAL - Returns the value of adding all the contents of the specified field.
540
</pre>
541
542
543
544
<br/>
545
546
547
<b>Parameters</b>
548
<ul>
549
550
<li>
551
field:
552
</li>
553
554
<li>
555
fn:
556
</li>
557
558
<li>
559
query: optional
560
</li>
561
562
</ul>
563
564
565
566
<b>Usage:</b>
567
<ul>
568
<li>Example: <pre> local mydb = db:get_database("my database")
569
echo(db:aggregate(mydb.enemies.name, "count"))
570
</pre>
571
</ul>
572
573
574
575
576
577
578
</dd>
579
580
581
582
583
<dt><a name="db:between"></a>
584
<b><big>db:between </big></b>(field, left_bound, right_bound)</dt>
585
<dd>
586
587
Returns a database expression to test if the field in the sheet is a value between lower_bound and upper_bound. This only really makes sense for numbers and Timestamps.
588
589
590
591
<br/>
592
593
594
<b>Parameters</b>
595
<ul>
596
597
<li>
598
field:
599
</li>
600
601
<li>
602
left_bound:
603
</li>
604
605
<li>
606
right_bound:
607
</li>
608
609
</ul>
610
611
612
613
614
615
616
617
<b>See also:</b>
618
<ul>
619
620
<li><a href='DB.html#db:not_between'>db:not_between</a>
621
622
<li><a href='DB.html#db:fetch'>db:fetch</a>
623
624
</ul>
625
626
627
</dd>
628
629
630
631
632
<dt><a name="db:close"></a>
633
<b><big>db:close </big></b>()</dt>
634
<dd>
635
636
<b><u>TODO</u></b>
637
638
639
640
<br/>
641
642
643
644
645
646
647
648
649
650
</dd>
651
652
653
654
655
<dt><a name="db:create"></a>
656
<b><big>db:create </big></b>(db_name, sheets)</dt>
657
<dd>
658
659
Creates and/or modifies an existing database. This function is safe to define at a top-level of a Mudlet script: in fact it is reccommended you run this function at a top-level without any kind of guards. If the named database does not exist it will create it. If the database does exist then it will add any columns or indexes which didn't exist before to that database. If the database already has all the specified columns and indexes, it will do nothing. <br/><br/> The database will be called Database_<sanitized database name>.db and will be stored in the Mudlet configuration directory. <br/><br/> Database 'tables' are called 'sheets' consistently throughout this documentation, to avoid confusion with Lua tables. <br/><br/> The schema table must be a Lua table array containing table dictionaries that define the structure and layout of each sheet. <br/><br/> For sheets with unique indexes, you may specify a _violations key to indicate how the db layer handle cases where the unique index is violated. The options you may use are: <pre> FAIL - the default. A hard error is thrown, cancelling the script.
660
IGNORE - The command that would add a record that violates uniqueness just fails silently.
661
REPLACE - The old record which matched the unique index is dropped, and the new one is added to replace it.
662
</pre>
663
664
665
666
<br/>
667
668
669
<b>Parameters</b>
670
<ul>
671
672
<li>
673
db_name:
674
</li>
675
676
<li>
677
sheets:
678
</li>
679
680
</ul>
681
682
683
684
<b>Usage:</b>
685
<ul>
686
<li>Example bellow will create a database with two sheets; the first is kills and is used to track every successful kill, with both where and when the kill happened. It has one index, a compound index tracking the combination of name and area. 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. <pre> local mydb = db:create("combat_log",
687
{
688
kills = {
689
name = "",
690
area = "",
691
killed = db:Timestamp("CURRENT_TIMESTAMP"),
692
_index = {{"name", "area"}}
693
},
694
enemies = {
695
name = "",
696
city = "",
697
reason = "",
698
enemied = db:Timestamp("CURRENT_TIMESTAMP"),
699
_index = { "city" },
700
_unique = { "name" },
701
_violations = "IGNORE"
702
}
703
}
704
)
705
</pre> Note that you have to use double {{ }} if you have composite index/unique constrain.
706
</ul>
707
708
709
710
711
712
713
</dd>
714
715
716
717
718
<dt><a name="db:delete"></a>
719
<b><big>db:delete </big></b>(sheet, query)</dt>
720
<dd>
721
722
Deletes rows from the specified sheet. The argument for query tries to be intelligent: <br/> * if it is a simple number, it deletes a specific row by _row_id <br/> * if it is a table that contains a _row_id (e.g., a table returned by db:get) it deletes just that record. <br/> * Otherwise, it deletes every record which matches the query pattern which is specified as with db:get. <br/> * If the query is simply true, then it will truncate the entire contents of the sheet. <br/>
723
724
725
726
<br/>
727
728
729
<b>Parameters</b>
730
<ul>
731
732
<li>
733
sheet:
734
</li>
735
736
<li>
737
query:
738
</li>
739
740
</ul>
741
742
743
744
<b>Usage</b>
745
<ul>
746
747
<li>When passed an actual result table that was obtained from db:fetch, it will delete the record for that table. <pre> enemies = db:fetch(mydb.enemies)
748
db:delete(mydb.enemies, enemies[1])
749
</pre>
750
751
<li>When passed a number, will delete the record for that _row_id. This example shows getting the row id from a table. <pre> enemies = db:fetch(mydb.enemies)
752
db:delete(mydb.enemies, enemies[1]._row_id)
753
</pre>
754
755
<li>As above, but this example just passes in the row id directly. <pre> db:delete(mydb.enemies, 5)
756
</pre>
757
758
<li>Here, we will delete anything which matches the same kind of query as db:fetch uses - namely, anyone who is in the city of San Francisco. <pre> db:delete(mydb.enemies, db:eq(mydb.enemies.city, "San Francisco"))
759
</pre>
760
761
<li>And finally, we will delete the entire contents of the enemies table. <pre> db:delete(mydb.enemies, true)
762
</pre>
763
764
</ul>
765
766
767
768
769
770
771
</dd>
772
773
774
775
776
<dt><a name="db:echo_sql"></a>
777
<b><big>db:echo_sql </big></b>(sql)</dt>
778
<dd>
779
780
This is a debugging function, which echos any SQL commands if db.debug_sql is true. You should not call this function directly from Mudlet.
781
782
783
784
<br/>
785
786
787
<b>Parameters</b>
788
<ul>
789
790
<li>
791
sql:
792
</li>
793
794
</ul>
795
796
797
798
<b>Usage:</b>
799
<ul>
800
<li>Set following lua variable to enable SQL echos. <pre> db.debug_sql=true
801
</pre>
802
</ul>
803
804
805
806
807
808
809
</dd>
810
811
812
813
814
<dt><a name="db:eq"></a>
815
<b><big>db:eq </big></b>(field, value, case_insensitive)</dt>
816
<dd>
817
818
Returns a database expression to test if the field in the sheet is equal to the value.
819
820
821
822
<br/>
823
824
825
<b>Parameters</b>
826
<ul>
827
828
<li>
829
field:
830
</li>
831
832
<li>
833
value:
834
</li>
835
836
<li>
837
case_insensitive:
838
</li>
839
840
</ul>
841
842
843
844
845
846
847
848
<b>See also:</b>
849
<ul>
850
851
<li><a href='DB.html#db:fetch'>db:fetch</a>
852
853
</ul>
854
855
856
</dd>
857
858
859
860
861
<dt><a name="db:exp"></a>
862
<b><big>db:exp </big></b>(text)</dt>
863
<dd>
864
865
Returns the string as-is to the database. <br/><br/> Use this function with caution, but it is very useful in some circumstances. One of the most common of such is incrementing an existing field in a db:set() operation, as so: <pre> db:set(mydb.enemies, db:exp("kills + 1"), db:eq(mydb.enemies.name, "Ixokai"))
866
</pre> This will increment the value of the kills field for the row identified by the name Ixokai. <br/><br/> But there are other uses, as the underlining database layer provides many functions you can call to do certain things. If you want to get a list of all your enemies who have a name longer then 10 characters, you may do: <pre> db:fetch(mydb.enemies, db:exp("length(name) > 10"))
867
</pre> Again, take special care with this, as you are doing SQL syntax directly and the library can't help you get things right.
868
869
870
871
<br/>
872
873
874
<b>Parameters</b>
875
<ul>
876
877
<li>
878
text:
879
</li>
880
881
</ul>
882
883
884
885
886
887
888
889
<b>See also:</b>
890
<ul>
891
892
<li><a href='DB.html#db:fetch'>db:fetch</a>
893
894
</ul>
895
896
897
</dd>
898
899
900
901
902
<dt><a name="db:fetch"></a>
903
<b><big>db:fetch </big></b>(sheet, query, order_by, descending)</dt>
904
<dd>
905
906
Returns a table array containing a table for each matching row in the specified sheet. All arguments but sheet are optional. If query is nil, the entire contents of the sheet will be returned. <br/><br/> Query is a string which should be built by calling the various db: expression functions, such as db:eq, db:AND, and such. You may pass a SQL WHERE clause here if you wish, but doing so is very dangerous. If you don't know SQL well, its best to build the expression.<br/><br/> Query may also be a table array of such expressions, if so they will be AND'd together implicitly.<br/><br/> The results that are returned are not in any guaranteed order, though they are usually the same order as the records were inserted. If you want to rely on the order in any way, you must pass a value to the order_by field. This must be a table array listing the columns you want to sort by. It can be { "column1" }, or { "column1", "column2" } <br/><br/> The results are returned in ascending (smallest to largest) order; to reverse this pass true into the final field.
907
908
909
910
<br/>
911
912
913
<b>Parameters</b>
914
<ul>
915
916
<li>
917
sheet:
918
</li>
919
920
<li>
921
query:
922
</li>
923
924
<li>
925
order_by:
926
</li>
927
928
<li>
929
descending:
930
</li>
931
932
</ul>
933
934
935
936
<b>Usage</b>
937
<ul>
938
939
<li>The first will fetch all of your enemies, sorted first by the city they reside in and then by their name. <pre> db:fetch(mydb.enemies, nil, {"city", "name"})
940
</pre>
941
942
<li>The second will fetch only the enemies which are in San Francisco. <pre> db:fetch(mydb.enemies, db:eq(mydb.enemies.city, "San Francisco"))
943
</pre>
944
945
<li>The third will fetch all the things you've killed in Undervault which have Drow in their name. <pre> db:fetch(mydb.kills,
946
{
947
db:eq(mydb.kills.area, "Undervault"),
948
db:like(mydb.kills.name, "%Drow%")
949
}
950
)
951
</pre>
952
953
</ul>
954
955
956
957
958
959
<b>See also:</b>
960
<ul>
961
962
<li><a href='DB.html#db:fetch_sql'>db:fetch_sql</a>
963
964
</ul>
965
966
967
</dd>
968
969
970
971
972
<dt><a name="db:fetch_sql"></a>
973
<b><big>db:fetch_sql </big></b>(sheet, sql)</dt>
974
<dd>
975
976
Execute SQL select query against database. This only useful for some very specific cases. <br/> Use db:fetch if possible instead - this function should not be normally used!
977
978
979
<br/><b>Release:</b> post Mudlet 1.1.1 (<b><u>TODO update before release</u></b>)
980
981
982
<br/>
983
984
985
<b>Parameters</b>
986
<ul>
987
988
<li>
989
sheet:
990
</li>
991
992
<li>
993
sql:
994
</li>
995
996
</ul>
997
998
999
1000
<b>Usage:</b>
1001
<ul>
1002
<li>Following will select all distinct area from my kills DB. <pre> db:fetch_sql(mydb.kills, "SELECT distinct area FROM kills")
1003
</pre>
1004
</ul>
1005
1006
1007
1008
1009
1010
<b>See also:</b>
1011
<ul>
1012
1013
<li><a href='DB.html#db:fetch'>db:fetch</a>
1014
1015
</ul>
1016
1017
1018
</dd>
1019
1020
1021
1022
1023
<dt><a name="db:get_database"></a>
1024
<b><big>db:get_database </big></b>(db_name)</dt>
1025
<dd>
1026
1027
Returns a reference of an already existing database. This instance can be used to get references to the sheets (and from there, fields) that are defined within the database. You use these references to construct queries. <br/><br/> These references do not contain any actual data, they only point to parts of the database structure.
1028
1029
1030
1031
<br/>
1032
1033
1034
<b>Parameters</b>
1035
<ul>
1036
1037
<li>
1038
db_name:
1039
</li>
1040
1041
</ul>
1042
1043
1044
1045
<b>Usage:</b>
1046
<ul>
1047
<li>If a database has a sheet named enemies, you can obtain a reference to that sheet by simply doing: <pre> local mydb = db:get_database("my database")
1048
local enemies_ref = mydb.enemies
1049
local name_ref = mydb.enemies.name
1050
</pre>
1051
</ul>
1052
1053
1054
1055
1056
1057
1058
</dd>
1059
1060
1061
1062
1063
<dt><a name="db:gt"></a>
1064
<b><big>db:gt </big></b>(field, value)</dt>
1065
<dd>
1066
1067
Returns a database expression to test if the field in the sheet is greater than to the value.
1068
1069
1070
1071
<br/>
1072
1073
1074
<b>Parameters</b>
1075
<ul>
1076
1077
<li>
1078
field:
1079
</li>
1080
1081
<li>
1082
value:
1083
</li>
1084
1085
</ul>
1086
1087
1088
1089
1090
1091
1092
1093
<b>See also:</b>
1094
<ul>
1095
1096
<li><a href='DB.html#db:fetch'>db:fetch</a>
1097
1098
</ul>
1099
1100
1101
</dd>
1102
1103
1104
1105
1106
<dt><a name="db:gte"></a>
1107
<b><big>db:gte </big></b>(field, value)</dt>
1108
<dd>
1109
1110
Returns a database expression to test if the field in the sheet is greater than or equal to the value.
1111
1112
1113
1114
<br/>
1115
1116
1117
<b>Parameters</b>
1118
<ul>
1119
1120
<li>
1121
field:
1122
</li>
1123
1124
<li>
1125
value:
1126
</li>
1127
1128
</ul>
1129
1130
1131
1132
1133
1134
1135
1136
<b>See also:</b>
1137
<ul>
1138
1139
<li><a href='DB.html#db:fetch'>db:fetch</a>
1140
1141
</ul>
1142
1143
1144
</dd>
1145
1146
1147
1148
1149
<dt><a name="db:in_"></a>
1150
<b><big>db:in_ </big></b>(field, tbl)</dt>
1151
<dd>
1152
1153
Returns a database expression to test if the field in the sheet is one of the values in the table array. <br/><br/> First, note the trailing underscore carefully! It is required.
1154
1155
1156
1157
<br/>
1158
1159
1160
<b>Parameters</b>
1161
<ul>
1162
1163
<li>
1164
field:
1165
</li>
1166
1167
<li>
1168
tbl:
1169
</li>
1170
1171
</ul>
1172
1173
1174
1175
<b>Usage:</b>
1176
<ul>
1177
<li>The following example illustrates the use of <b>in_</b>: This will obtain all of your kills which happened in the Undervault, Hell or Purgatory. Every db:in_ expression can be written as a db:OR, but that quite often gets very complex. <pre> local mydb = db:get_database("my database")
1178
local areas = {"Undervault", "Hell", "Purgatory"}
1179
db:fetch(mydb.kills, db:in_(mydb.kills.area, areas))
1180
</pre>
1181
</ul>
1182
1183
1184
1185
1186
1187
<b>See also:</b>
1188
<ul>
1189
1190
<li><a href='DB.html#db:fetch'>db:fetch</a>
1191
1192
</ul>
1193
1194
1195
</dd>
1196
1197
1198
1199
1200
<dt><a name="db:is_nil"></a>
1201
<b><big>db:is_nil </big></b>(field)</dt>
1202
<dd>
1203
1204
Returns a database expression to test if the field in the sheet is nil.
1205
1206
1207
1208
<br/>
1209
1210
1211
<b>Parameters</b>
1212
<ul>
1213
1214
<li>
1215
field:
1216
</li>
1217
1218
</ul>
1219
1220
1221
1222
1223
1224
1225
1226
<b>See also:</b>
1227
<ul>
1228
1229
<li><a href='DB.html#db:fetch'>db:fetch</a>
1230
1231
</ul>
1232
1233
1234
</dd>
1235
1236
1237
1238
1239
<dt><a name="db:is_not_nil"></a>
1240
<b><big>db:is_not_nil </big></b>(field)</dt>
1241
<dd>
1242
1243
Returns a database expression to test if the field in the sheet is not nil.
1244
1245
1246
1247
<br/>
1248
1249
1250
<b>Parameters</b>
1251
<ul>
1252
1253
<li>
1254
field:
1255
</li>
1256
1257
</ul>
1258
1259
1260
1261
1262
1263
1264
1265
<b>See also:</b>
1266
<ul>
1267
1268
<li><a href='DB.html#db:fetch'>db:fetch</a>
1269
1270
</ul>
1271
1272
1273
</dd>
1274
1275
1276
1277
1278
<dt><a name="db:like"></a>
1279
<b><big>db:like </big></b>(field, value)</dt>
1280
<dd>
1281
1282
Returns a database expression to test if the field in the sheet matches the specified pattern. <br/><br/> LIKE patterns are not case-sensitive, and allow two wild cards. The first is an underscore which matches any single one character. The second is a percent symbol which matches zero or more of any character. <pre> LIKE with "_" is therefore the same as the "." regular expression.
1283
LIKE with "%" is therefore the same as ".*" regular expression.
1284
</pre>
1285
1286
1287
1288
<br/>
1289
1290
1291
<b>Parameters</b>
1292
<ul>
1293
1294
<li>
1295
field:
1296
</li>
1297
1298
<li>
1299
value:
1300
</li>
1301
1302
</ul>
1303
1304
1305
1306
1307
1308
1309
1310
<b>See also:</b>
1311
<ul>
1312
1313
<li><a href='DB.html#db:not_like'>db:not_like</a>
1314
1315
<li><a href='DB.html#db:fetch'>db:fetch</a>
1316
1317
</ul>
1318
1319
1320
</dd>
1321
1322
1323
1324
1325
<dt><a name="db:lt"></a>
1326
<b><big>db:lt </big></b>(field, value)</dt>
1327
<dd>
1328
1329
Returns a database expression to test if the field in the sheet is less than the value.
1330
1331
1332
1333
<br/>
1334
1335
1336
<b>Parameters</b>
1337
<ul>
1338
1339
<li>
1340
field:
1341
</li>
1342
1343
<li>
1344
value:
1345
</li>
1346
1347
</ul>
1348
1349
1350
1351
1352
1353
1354
1355
<b>See also:</b>
1356
<ul>
1357
1358
<li><a href='DB.html#db:fetch'>db:fetch</a>
1359
1360
</ul>
1361
1362
1363
</dd>
1364
1365
1366
1367
1368
<dt><a name="db:lte"></a>
1369
<b><big>db:lte </big></b>(field, value)</dt>
1370
<dd>
1371
1372
Returns a database expression to test if the field in the sheet is less than or equal to the value.
1373
1374
1375
1376
<br/>
1377
1378
1379
<b>Parameters</b>
1380
<ul>
1381
1382
<li>
1383
field:
1384
</li>
1385
1386
<li>
1387
value:
1388
</li>
1389
1390
</ul>
1391
1392
1393
1394
1395
1396
1397
1398
<b>See also:</b>
1399
<ul>
1400
1401
<li><a href='DB.html#db:fetch'>db:fetch</a>
1402
1403
</ul>
1404
1405
1406
</dd>
1407
1408
1409
1410
1411
<dt><a name="db:merge_unique"></a>
1412
<b><big>db:merge_unique </big></b>(sheet, tables)</dt>
1413
<dd>
1414
1415
Merges the specified table array into the sheet, modifying any existing rows and adding any that don't exist. This function is a convenience utility that allows you to quickly modify a sheet, changing existing rows and add new ones as appropriate. It ONLY works on sheets which have a unique index, and only when that unique index is only on a single field. For more complex situations you'll have to do the logic yourself. The table array may contain tables that were either returned previously by db:fetch, or new tables that you've constructed with the correct fields, or any mix of both. Each table must have a value for the unique key that has been set on this sheet.
1416
1417
1418
1419
<br/>
1420
1421
1422
<b>Parameters</b>
1423
<ul>
1424
1425
<li>
1426
sheet:
1427
</li>
1428
1429
<li>
1430
tables:
1431
</li>
1432
1433
</ul>
1434
1435
1436
1437
<b>Usage:</b>
1438
<ul>
1439
<li>For example, consider this database: <pre> local mydb = db:create("peopledb",
1440
{
1441
friends = {
1442
name = "",
1443
race = "",
1444
level = 0,
1445
city = "",
1446
_index = { "city" },
1447
_unique = { "name" }
1448
}
1449
}
1450
)
1451
</pre> Here you have a database with one sheet, which contains your friends, their race, level, and what city they live in. Let's say you want to fetch everyone who lives in San Francisco, you could do: <pre> local results = db:fetch(mydb.friends, db:eq(mydb.friends.city, "San Francisco"))
1452
</pre> The tables in results are static, any changes to them are not saved back to the database. But after a major radioactive cataclysm rendered everyone in San Francisco a mutant, you could make changes to the tables as so: <pre> for _, friend in ipairs(results) do
1453
friend.race = "Mutant"
1454
end
1455
</pre> If you are also now aware of a new arrival in San Francisco, you could add them to that existing table array: <pre> results[#results+1] = {name="Bobette", race="Mutant", city="San Francisco"}
1456
</pre> And commit all of these changes back to the database at once with: <pre> db:merge_unique(mydb.friends, results)
1457
</pre> 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.
1458
</ul>
1459
1460
1461
1462
1463
1464
1465
</dd>
1466
1467
1468
1469
1470
<dt><a name="db:not_between"></a>
1471
<b><big>db:not_between </big></b>(field, left_bound, right_bound)</dt>
1472
<dd>
1473
1474
Returns a database expression to test if the field in the sheet is NOT a value between lower_bound and upper_bound. This only really makes sense for numbers and Timestamps.
1475
1476
1477
1478
<br/>
1479
1480
1481
<b>Parameters</b>
1482
<ul>
1483
1484
<li>
1485
field:
1486
</li>
1487
1488
<li>
1489
left_bound:
1490
</li>
1491
1492
<li>
1493
right_bound:
1494
</li>
1495
1496
</ul>
1497
1498
1499
1500
1501
1502
1503
1504
<b>See also:</b>
1505
<ul>
1506
1507
<li><a href='DB.html#db:between'>db:between</a>
1508
1509
<li><a href='DB.html#db:fetch'>db:fetch</a>
1510
1511
</ul>
1512
1513
1514
</dd>
1515
1516
1517
1518
1519
<dt><a name="db:not_eq"></a>
1520
<b><big>db:not_eq </big></b>(field, value, case_insensitive)</dt>
1521
<dd>
1522
1523
Returns a database expression to test if the field in the sheet is NOT equal to the value.
1524
1525
1526
1527
<br/>
1528
1529
1530
<b>Parameters</b>
1531
<ul>
1532
1533
<li>
1534
field:
1535
</li>
1536
1537
<li>
1538
value:
1539
</li>
1540
1541
<li>
1542
case_insensitive:
1543
</li>
1544
1545
</ul>
1546
1547
1548
1549
1550
1551
1552
1553
<b>See also:</b>
1554
<ul>
1555
1556
<li><a href='DB.html#db:fetch'>db:fetch</a>
1557
1558
</ul>
1559
1560
1561
</dd>
1562
1563
1564
1565
1566
<dt><a name="db:not_in"></a>
1567
<b><big>db:not_in </big></b>(field, tbl)</dt>
1568
<dd>
1569
1570
Returns a database expression to test if the field in the sheet is not one of the values in the table array.
1571
1572
1573
1574
<br/>
1575
1576
1577
<b>Parameters</b>
1578
<ul>
1579
1580
<li>
1581
field:
1582
</li>
1583
1584
<li>
1585
tbl:
1586
</li>
1587
1588
</ul>
1589
1590
1591
1592
1593
1594
1595
1596
<b>See also:</b>
1597
<ul>
1598
1599
<li><a href='DB.html#db:in_'>db:in_</a>
1600
1601
<li><a href='DB.html#db:fetch'>db:fetch</a>
1602
1603
</ul>
1604
1605
1606
</dd>
1607
1608
1609
1610
1611
<dt><a name="db:not_like"></a>
1612
<b><big>db:not_like </big></b>(field, value)</dt>
1613
<dd>
1614
1615
Returns a database expression to test if the field in the sheet does not match the specified pattern. LIKE patterns are not case-sensitive, and allow two wild cards. The first is an underscore which matches any single one character. The second is a percent symbol which matches zero or more of any character. <pre> LIKE with "_" is therefore the same as the "." regular expression.
1616
LIKE with "%" is therefore the same as ".*" regular expression.
1617
</pre>
1618
1619
1620
1621
<br/>
1622
1623
1624
<b>Parameters</b>
1625
<ul>
1626
1627
<li>
1628
field:
1629
</li>
1630
1631
<li>
1632
value:
1633
</li>
1634
1635
</ul>
1636
1637
1638
1639
1640
1641
1642
1643
<b>See also:</b>
1644
<ul>
1645
1646
<li><a href='DB.html#db:like'>db:like</a>
1647
1648
<li><a href='DB.html#db:fetch'>db:fetch</a>
1649
1650
</ul>
1651
1652
1653
</dd>
1654
1655
1656
1657
1658
<dt><a name="db:safe_name"></a>
1659
<b><big>db:safe_name </big></b>(name)</dt>
1660
<dd>
1661
1662
<b><u>TODO</u></b> db:safe_name(name) On a filesystem level, names are restricted to being alphanumeric only. So, "my_database" becomes "mydatabase", and "../../../../etc/passwd" becomes "etcpasswd". This prevents any possible security issues with database names.
1663
1664
1665
1666
<br/>
1667
1668
1669
<b>Parameters</b>
1670
<ul>
1671
1672
<li>
1673
name:
1674
</li>
1675
1676
</ul>
1677
1678
1679
1680
1681
1682
1683
1684
1685
</dd>
1686
1687
1688
1689
1690
<dt><a name="db:set"></a>
1691
<b><big>db:set </big></b>(field, value, query)</dt>
1692
<dd>
1693
1694
The db:set function allows you to set a certain field to a certain value across an entire sheet. Meaning, you can change all of the last_read fields in the sheet to a certain value, or possibly only the last_read fields which are in a certain city. The query argument can be any value which is appropriate for db:fetch, even nil which will change the value for the specified column for EVERY row in the sheet. For example, consider a situation in which you are tracking how many times you find a certain type of egg during Easter. You start by setting up your database and adding an Eggs sheet, and then adding a record for each type of egg. <pre> local mydb = db:create("egg database", {eggs = {color = "", last_found = db.Timestamp(false), found = 0}})
1695
db:add(mydb.eggs,
1696
{color = "Red"},
1697
{color = "Blue"},
1698
{color = "Green"},
1699
{color = "Yellow"},
1700
{color = "Black"}
1701
)
1702
</pre> Now, you have three columns. One is a string, one a timestamp (that ends up as nil in the database), and one is a number. <br/><br/> You can then set up a trigger to capture from the mud the string, "You pick up a (.*) egg!", and you end up arranging to store the value of that expression in a variable called "myegg". <br/><br/> To increment how many we found, we will do this: <pre> myegg = "Red" -- We will pretend a trigger set this.
1703
db:set(mydb.eggs.found, db:exp("found + 1"), db:eq(mydb.eggs.color, myegg))
1704
db:set(mydb.eggs.last_found, db.Timestamp("CURRENT_TIMESTAMP"), db:eq(mydb.eggs.color, myegg))
1705
</pre> This will go out and set two fields in the Red egg sheet; the first is the found field, which will increment the value of that field (using the special db:exp function). The second will update the last_found field with the current time. <br/><br/> Once this contest is over, you may wish to reset this data but keep the database around. To do that, you may use a more broad use of db:set as such: <pre> db:set(mydb.eggs.found, 0)
1706
db:set(mydb.eggs.last_found, nil)
1707
</pre>
1708
1709
1710
1711
<br/>
1712
1713
1714
<b>Parameters</b>
1715
<ul>
1716
1717
<li>
1718
field:
1719
</li>
1720
1721
<li>
1722
value:
1723
</li>
1724
1725
<li>
1726
query:
1727
</li>
1728
1729
</ul>
1730
1731
1732
1733
1734
1735
1736
1737
1738
</dd>
1739
1740
1741
1742
1743
<dt><a name="db:update"></a>
1744
<b><big>db:update </big></b>(sheet, tbl)</dt>
1745
<dd>
1746
1747
This function updates a row in the specified sheet, but only accepts a row which has been previously obtained by db:fetch. Its primary purpose is that if you do a db:fetch, then change the value of a field or tow, you can save back that table.
1748
1749
1750
1751
<br/>
1752
1753
1754
<b>Parameters</b>
1755
<ul>
1756
1757
<li>
1758
sheet:
1759
</li>
1760
1761
<li>
1762
tbl:
1763
</li>
1764
1765
</ul>
1766
1767
1768
1769
<b>Usage:</b>
1770
<ul>
1771
<li>This obtains a database reference, and queries the friends sheet for someone named Bob. As this returns a table array containing only one item, it assigns that one item to the local variable named bob. We then change the notes on Bob, and pass it into db:update() to save the changes back. <pre> local mydb = db:get_database("my database")
1772
local bob = db:fetch(mydb.friends, db:eq(mydb.friends.name, "Bob"))[1]
1773
bob.notes = "He's a really awesome guy."
1774
db:update(mydb.friends, bob)
1775
</pre>
1776
</ul>
1777
1778
1779
1780
1781
1782
1783
</dd>
1784
1785
1786
</dl>
1787
1788
1789
1790
1791
1792
1793
1794
</div> <!-- id="content" -->
1795
1796
</div> <!-- id="main" -->
1797
1798
<div id="about">
1799
<p><a href="http://validator.w3.org/check?uri=referer"><img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" /></a></p>
1800
</div> <!-- id="about" -->
1801
1802
</div> <!-- id="container" -->
1803
</body>
1804
</html>
b'\\ No newline at end of file'
Loggerhead is a web-based interface for Breezy
Version: 2.0.1