README.md

   1 lazy sqlite3 lua bindings
   2 =========================
   3 This library provides somewhat weird sqlite bindings.
   4
   5 To open a database:
   6
   7 `local db = require('lazylite.db').open('test.db')`
   8
   9 Executing write statements:
  10
  11 ```
  12 count = db:exec("UPDATE foo SET bar=? WHERE baz=?", bar, baz)
  13 ```
  14
  15 Number of rows modified is returned. But what if you need last rowid?
  16
  17 Then you do:
  18
  19 ```
  20 count,rowid = db:exec("INSERT INTO foo values(?); SELECT rowid FROM foo", bar, baz)
  21 ```
  22
  23 This is a concept seen throughout this library - statements can be chained in
  24 one call.
  25
  26 Subsequent values returned by `exec()` are simply more columns from next
  27 statement, pasted together as one virtual row of results. The first number
  28 returned is number of modified rows for all statements.
  29
  30 Retrieving a single row of data with named columns
  31 --------------------------------------------------
  32
  33 `local row = db:col("SELECT foo, bar FROM baz")`
  34
  35 Will fill in `row.foo` and `row.bar`. Again, you can execute multiple statements:
  36
  37 ```
  38 local row = db:col("SELECT foo, bar FROM baz; SELECT duh FROM bleh")
  39 ```
  40
  41 Will return `row.foo`, `row.bar` and `row.duh`. You might want to use AS to rename
  42 colliding fields, otherwise those get silently overwritten.
  43
  44 Finally, already existing table can be supplied where fields will be filled
  45 out, like so:
  46
  47 `db:tcol(row, "SELECT foo, bar FROM baz; SELECT duh FROM bleh")`
  48
  49 (same table is also returned)
  50
  51 This is useful if you need to modify object you cant easily change reference
  52 to (typically 'self'). You can also inspect values as they come in via
  53 `__newindex` on this table.
  54
  55 Retrieving a single row of data with columns as return values
  56 -------------------------------------------------------------
  57
  58 `local foo, bar = db:row("SELECT foo, bar FROM baz")`
  59 `local foo, bar, duh = db:row("SELECT foo, bar FROM baz; SELECT duh FROM bleh")`
  60
  61 This is just a short hand of the :col() method above, so you can assign columns
  62 to variables directly.
  63
  64 Retrieving multiple rows with named columns
  65 -------------------------------------------
  66 ```
  67 for st_idx, tab in db:cols("SELECT foo FROM bar; SELECT boo FROM baz") do
  68         print(st_idx, tab.foo, tab.boo)
  69 end
  70 ```
  71
  72 Statement chaining works differently when fetching multiple rows -
  73 each clause will be executed sequentially - once all rows are finished in
  74 one statement, the `st_idx` is bumped and rows are returned for next statement
  75 and so on.
  76 If `bar` and `baz` tables contain 2 rows each, the output of the above will be:
  77
  78 ```
  79 1       foo1    nil
  80 1       foo2    nil
  81 2       nil     boo1
  82 2       nil     boo2
  83 ```
  84
  85 Typically, you can chain multiple tables by carefully renaming the fields,
  86 like so:
  87
  88 ```
  89 for st_idx, tab in db:cols("SELECT foo FROM bar; SELECT boo As foo FROM baz") do
  90         print(st_idx, tab.foo, tab.boo)
  91 end
  92 ```
  93
  94 And then you get:
  95
  96 ```
  97 1       foo1
  98 1       foo2
  99 2       boo1
 100 2       boo2
 101 ```
 102
 103 Retrieving multiple rows with columns as return values
 104 ------------------------------------------------------
 105
 106 Finally, for direct access to columns, you use:
 107
 108 ```
 109 for st_idx, foo_or_boo in db:rows("SELECT foo FROM bar; SELECT boo FROM baz") do
 110         print(st_idx, foo_or_boo)
 111 end
 112 ```
 113
 114 The statement index works exactly same as with db:cols(). The most common
 115 usage is to simply ignore the statement index and just use:
 116
 117 ```
 118 for _, blah in db:rows("SELECT foo FROM bar; SELECT boo FROM baz") do
 119         print(blah)
 120 end
 121 ```
 122
 123 This method is particularly efficient as no new tables will be polluting
 124 the GC.
 125
 126 Named variables
 127 ---------------
 128
 129 All functions also accept named arguments, like so:
 130
 131 ```
 132 tab = { fieldfoo = "foo", tabfoo = "bar" }
 133 for _, foo, bar in db:rows("SELECT $fieldfoo FROM $tabfoo", tab) do
 134   ...
 135 end
 136 ```
 137
 138 Standard sqlite prefixes - $:#@ can be used.
 139
 140 You can also mix named and unnamed variables:
 141
 142 ```
 143 tab = {v1 = 1, v2 = 2}
 144 db:exec("INSERT INTO tab(?,?) values($v1,$v1)", tab, 'f1', 'f2)
 145 ```
 146
 147 evaluates (but with proper escaping) to:
 148
 149 ```
 150 db:exec("INSERT INTO tab(f1,f2) values("..tab.v1..","..tab.v2")")
 151 ```
 152
 153 Notice that table must be always first, so the parser knows
 154 where to look for symbolic names whenever those are encountered. All numbered
 155 ? values are then scanned positionally, ignoring the table and named arguments.
 156 Named and unnamed variables live in "separate worlds" so to speak.
 157
 158 FAQ
 159 ===
 160
 161 Why all this statement chaining madness?
 162 ----------------------------------------
 163
 164 To avoid slow/cumbersom for trivial "look all over the place" sort of queries.
 165 Simple queries are streamed on the go with sqlite, whereas joins have to
 166 wait for the whole result to complete only to throw away most of it - resulting
 167 much heavier disk IO.
 168
 169 What about statement handles, cursors?
 170 ----------------------------------------
 171
 172 This is all handled automatically behind the scenes, cached and garbage
 173 collected. Just make sure to never keep a reference to the hidden variables
 174 returned by loop iterators when fetching rows (ie don't save in a variable, but
 175 always use direct notation with the for loop).
 176
 177 Oh, and don't construct query format strings from ever changing values.
 178 Those are cache keys, and never collected - it would leak memory. Use statement
 179 variables for variation of the query, tables can be specified too.
 180
 181 I want bunch of rows in a table
 182 -------------------------------
 183
 184 Don't do this, it is slow and pollutes heap. If you absolutely must, write
 185 a simple wrapper where generator saves results in a table.
 186