LuaSQLite3

Check-in [6337de362e]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Update documentation. Add unit tests for update_hook. Include extra fikes for deprecated make.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6337de362ebc204c5cecc7b16751ff7b67974a32
User & Date: e 2013-03-31 13:01:27
References
2013-03-31
13:15 Ticket [c7b818d353] Support for sqlite3_update_hook? status still Open with 5 other changes artifact: d0d0a3eee4 user: e
Context
2013-04-01
18:02
Add commit_hook and rollback_hook to complement update_hook. check-in: bb4d13eba2 user: e tags: trunk
2013-03-31
13:01
Update documentation. Add unit tests for update_hook. Include extra fikes for deprecated make. check-in: 6337de362e user: e tags: trunk
2013-03-30
23:33
Add update_hook example; fix examples for Lua 5.2. check-in: 2ad6aad5b6 user: e tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to doc/lsqlite3.wiki.

39
40
41
42
43
44
45
46

47
48
49
50
51
52
53
...
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
...
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
...
538
539
540
541
542
543
544













545
546
547
548
549
550
551
...
907
908
909
910
911
912
913












914
915
916
917
918
919
920
921
922
923
924
925
926
927
...
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
		<li><a href="#db_isopen">db:isopen</a></li>
		<li><a href="#db_last_insert_rowid">db:last_insert_rowid</a></li>
		<li><a href="#db_nrows">db:nrows</a></li>
		<li><a href="#db_prepare">db:prepare</a></li>
		<li><a href="#db_progress_handler">db:progress_handler</a></li>
		<li><a href="#db_rows">db:rows</a></li>
		<li><a href="#db_total_changes">db:total_changes</a></li>
		<li><a href="#db_trace">db:trace</a></li>

		<li><a href="#db_urows">db:urows</a></li>
	</ul>

	<li><a href="#methods_for_prepared_statements">Methods for prepared statements</a></li>
	<ul>

		<li><a href="#stmt_bind">stmt:bind</a></li>
................................................................................
<hr name="index" />
</div>

<p>
</p>
<hr />
<h1><a name="name">NAME</a></h1>
<p><strong>LuaSQLite 3</strong> - a Lua 5.1 wrapper for the SQLite3 library</p>
<p>
</p>
<hr />
<h1><a name="overview">OVERVIEW</a></h1>
<p><strong>LuaSQLite 3</strong> is a thin wrapper around the public domain SQLite3
database engine.</p>
<p>The <code>lsqlite3</code> module supports the creation and manipulation of 
SQLite3 databases. After a <code>require('lsqlite3')</code> the exported 
functions are called with prefix <code>sqlite3</code>. However, most sqlite3 
functions are called via an object-oriented interface to either 
database or SQL statement objects; see below for details.</p>
<p>This documentation does not attempt to describe how SQLite3 itself
works, it just describes the Lua binding and the available functions.
For more information about the SQL features supported by SQLite3 and
details about the syntax of SQL statements and queries, please see the
................................................................................
advanced features (how to use callbacks, for instance) will require 
some familiarity with the SQLite3 API.</p>
<p>
</p>
<hr />
<h1><a name="download">DOWNLOAD</a></h1>
<p><strong>LuaSQLite 3</strong> source code can be downloaded from its 
LuaForge (<a href="http://luaforge.net/projects/luasqlite/">http://luaforge.net/projects/luasqlite/</a>) page.</p>
<p>You will also need to build or obtain an SQLite3 loadable library 
(DLL or .so). See <a href="http://www.sqlite.org/">http://www.sqlite.org/</a> for obtaining SQLite3 
source code or downloading a binary SQLite3 library.</p>
<p>
</p>
<hr />
<h1><a name="installation">INSTALLATION</a></h1>
<p>A <em>Makefile</em> is provided; it assumes an SQLite3 library is already
installed.</p>
<p>
</p>
<hr />
<h1><a name="examples">EXAMPLES</a></h1>
<p>The distribution contains an <em>examples</em> directory. The unit tests
also show some example use.</p>
<p>
</p>
<hr />
<h1><a name="verification_tests">VERIFICATION TESTS</a></h1>
<p>The distribution contains some units tests using Michael Roth's
<code>lunit</code> (which is also included). Some of the tests were also derived 
from Michael's <strong>lua-sqlite3</strong> module, and more unit tests added by 
Doug Currie.</p>
<p>The distribution also contains some functional tests by Tiago.</p>
<p>This version of <code>lsqlite3</code> was tested with SQLite 3.7.3.</p>
<p>
</p>
<hr />
<h1><a name="reference">REFERENCE</a></h1>
<p>
</p>
<hr />
................................................................................
        db:trace(func,udata)</pre>
<p>This function installs a trace callback handler. <code>func</code> is a Lua
function that is called by SQLite3 just before the evaluation of an SQL
statement. This callback receives two arguments: the first is the 
<code>udata</code> argument used when the callback was installed; the second is a 
string with the SQL statement about to be executed.</p>
<p>













</p>
<h2><a name="db_urows">db:urows</a></h2>
<pre>
        db:urows(sql)</pre>
<p>Creates an iterator that returns the successive rows selected by the SQL
statement given in string <code>sql</code>. Each call to the iterator returns the
values that correspond to the columns in the currently selected row.
................................................................................
<pre>
        OK: 0          ERROR: 1       INTERNAL: 2    PERM: 3        ABORT: 4
        BUSY: 5        LOCKED: 6      NOMEM: 7       READONLY: 8    INTERRUPT: 9
        IOERR: 10      CORRUPT: 11    NOTFOUND: 12   FULL: 13       CANTOPEN: 14
        PROTOCOL: 15   EMPTY: 16      SCHEMA: 17     TOOBIG: 18     CONSTRAINT: 19
        MISMATCH: 20   MISUSE: 21     NOLFS: 22      FORMAT: 24     RANGE: 25
        NOTADB: 26     ROW: 100       DONE: 101</pre>












<p>For details about their exact meaning please see the <strong>SQLite3
documentation</strong> <a href="http://www.sqlite.org/">http://www.sqlite.org/</a>.</p>
<p>
</p>
<hr />
<h1><a name="version">VERSION</a></h1>
<p>This is <code>lsqlite3</code> subversion 8, also known as &quot;devel-0.8&quot;.</p>
<p>
</p>
<hr />
<h1><a name="credits">CREDITS</a></h1>
<p><code>lsqlite3</code> was developed by Tiago Dionizio and Doug Currie with 
contributions from Thomas Lauer and Michael Roth.</p>
<p>This documentation is based on the &quot;(very) preliminary&quot; documents
................................................................................
<p>
</p>
<hr />
<h1><a name="license">LICENSE</a></h1>
<pre>
    /************************************************************************
    * lsqlite3                                                              *
    * Copyright (C) 2002-2011 Tiago Dionizio, Doug Currie                   *
    * All rights reserved.                                                  *
    * Author    : Tiago Dionizio &lt;tiago.dionizio@ist.utl.pt&gt;                *
    * Author    : Doug Currie &lt;doug.currie@alum.mit.edu&gt;                    *
    * Library   : lsqlite3 - a SQLite 3 database binding for Lua 5          *
    *                                                                       *
    * Permission is hereby granted, free of charge, to any person obtaining *
    * a copy of this software and associated documentation files (the       *







|
>







 







|







|







 







|







|
|










|
|
|
|

|







 







>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>






|







 







|







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
...
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
...
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
...
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
...
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
...
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
		<li><a href="#db_isopen">db:isopen</a></li>
		<li><a href="#db_last_insert_rowid">db:last_insert_rowid</a></li>
		<li><a href="#db_nrows">db:nrows</a></li>
		<li><a href="#db_prepare">db:prepare</a></li>
		<li><a href="#db_progress_handler">db:progress_handler</a></li>
		<li><a href="#db_rows">db:rows</a></li>
		<li><a href="#db_total_changes">db:total_changes</a></li>
        <li><a href="#db_trace">db:trace</a></li>
        <li><a href="#db_update_hook">db:update_hook</a></li>
		<li><a href="#db_urows">db:urows</a></li>
	</ul>

	<li><a href="#methods_for_prepared_statements">Methods for prepared statements</a></li>
	<ul>

		<li><a href="#stmt_bind">stmt:bind</a></li>
................................................................................
<hr name="index" />
</div>

<p>
</p>
<hr />
<h1><a name="name">NAME</a></h1>
<p><strong>LuaSQLite 3</strong> - a Lua 5.1 and Lua 5.2 wrapper for the SQLite3 library</p>
<p>
</p>
<hr />
<h1><a name="overview">OVERVIEW</a></h1>
<p><strong>LuaSQLite 3</strong> is a thin wrapper around the public domain SQLite3
database engine.</p>
<p>The <code>lsqlite3</code> module supports the creation and manipulation of 
SQLite3 databases. After a <code>sqlite3 = require('lsqlite3')</code> the exported 
functions are called with prefix <code>sqlite3</code>. However, most sqlite3 
functions are called via an object-oriented interface to either 
database or SQL statement objects; see below for details.</p>
<p>This documentation does not attempt to describe how SQLite3 itself
works, it just describes the Lua binding and the available functions.
For more information about the SQL features supported by SQLite3 and
details about the syntax of SQL statements and queries, please see the
................................................................................
advanced features (how to use callbacks, for instance) will require 
some familiarity with the SQLite3 API.</p>
<p>
</p>
<hr />
<h1><a name="download">DOWNLOAD</a></h1>
<p><strong>LuaSQLite 3</strong> source code can be downloaded from its 
Fossil (<a href="http://lua.sqlite.org/">http://lua.sqlite.org/</a>) page.</p>
<p>You will also need to build or obtain an SQLite3 loadable library 
(DLL or .so). See <a href="http://www.sqlite.org/">http://www.sqlite.org/</a> for obtaining SQLite3 
source code or downloading a binary SQLite3 library.</p>
<p>
</p>
<hr />
<h1><a name="installation">INSTALLATION</a></h1>
<p>Luarocks (<a href="http://luarocks.org/">http://luarocks.org/</a>) is the preferred mechanism 
to build and install <code>lsqlite3</code>; it assumes an SQLite3 library is already installed.</p>
<p>
</p>
<hr />
<h1><a name="examples">EXAMPLES</a></h1>
<p>The distribution contains an <em>examples</em> directory. The unit tests
also show some example use.</p>
<p>
</p>
<hr />
<h1><a name="verification_tests">VERIFICATION TESTS</a></h1>
<p>The distribution contains a <em>tests</em> directory with some units tests using 
an enhanced version of Michael Roth's <code>lunit</code> called <code>lunitx</code>. Some of the 
tests were also derived from Michael's <strong>lua-sqlite3</strong> module, and more unit tests 
added by Doug Currie. Get <code>lunitx</code> using Luarocks.</p>
<p>The distribution also contains some functional tests by Tiago.</p>
<p>This version of <code>lsqlite3</code> was tested with SQLite 3.7.16.1.</p>
<p>
</p>
<hr />
<h1><a name="reference">REFERENCE</a></h1>
<p>
</p>
<hr />
................................................................................
        db:trace(func,udata)</pre>
<p>This function installs a trace callback handler. <code>func</code> is a Lua
function that is called by SQLite3 just before the evaluation of an SQL
statement. This callback receives two arguments: the first is the 
<code>udata</code> argument used when the callback was installed; the second is a 
string with the SQL statement about to be executed.</p>
<p>
</p>
<h2><a name="db_update_hook">db:update_hook</a></h2>
<pre>
        db:update_hook(func,udata)</pre>
<p>This function installs an update_hook Data Change Notification Callback handler. 
<code>func</code> is a Lua function that is invoked by SQLite3 whenever a row is updated, 
inserted or deleted. This callback receives five arguments: the first is the 
<code>udata</code> argument used when the callback was installed; the second is an 
integer indicating the operation that caused the callback to be invoked (one of <code>sqlite3.UPDATE</code>,
<code>sqlite3.INSERT</code>, or <code>sqlite3.DELETE</code>). The third and fourth arguments 
are the database and table name containing the affected row. The final callback parameter is 
the rowid of the row. In the case of an update, this is the rowid after the update takes place.</p>
<p>
</p>
<h2><a name="db_urows">db:urows</a></h2>
<pre>
        db:urows(sql)</pre>
<p>Creates an iterator that returns the successive rows selected by the SQL
statement given in string <code>sql</code>. Each call to the iterator returns the
values that correspond to the columns in the currently selected row.
................................................................................
<pre>
        OK: 0          ERROR: 1       INTERNAL: 2    PERM: 3        ABORT: 4
        BUSY: 5        LOCKED: 6      NOMEM: 7       READONLY: 8    INTERRUPT: 9
        IOERR: 10      CORRUPT: 11    NOTFOUND: 12   FULL: 13       CANTOPEN: 14
        PROTOCOL: 15   EMPTY: 16      SCHEMA: 17     TOOBIG: 18     CONSTRAINT: 19
        MISMATCH: 20   MISUSE: 21     NOLFS: 22      FORMAT: 24     RANGE: 25
        NOTADB: 26     ROW: 100       DONE: 101</pre>

<p>plus the Authorizer Action Codes:</p>
<pre>
  CREATE_INDEX: 1         CREATE_TABLE: 2       CREATE_TEMP_INDEX: 3  CREATE_TEMP_TABLE: 4
  CREATE_TEMP_TRIGGER: 5  CREATE_TEMP_VIEW: 6   CREATE_TRIGGER: 7     CREATE_VIEW: 8
  DELETE: 9               DROP_INDEX: 10        DROP_TABLE: 11        DROP_TEMP_INDEX: 12
  DROP_TEMP_TABLE: 13     DROP_TEMP_TRIGGER: 14 DROP_TEMP_VIEW: 15    DROP_TRIGGER: 16
  DROP_VIEW: 17           INSERT: 18            PRAGMA: 19            READ: 20
  SELECT: 21              TRANSACTION: 22       UPDATE: 23            ATTACH: 24
  DETACH: 25              ALTER_TABLE: 26       REINDEX: 27           ANALYZE: 28
  CREATE_VTABLE: 29       DROP_VTABLE: 30       FUNCTION: 31          SAVEPOINT: 32
</pre>
<p>For details about their exact meaning please see the <strong>SQLite3
documentation</strong> <a href="http://www.sqlite.org/">http://www.sqlite.org/</a>.</p>
<p>
</p>
<hr />
<h1><a name="version">VERSION</a></h1>
<p>This is <code>lsqlite3</code> version <code>fsl_9</code>, also known as &quot;devel-0.9&quot;.</p>
<p>
</p>
<hr />
<h1><a name="credits">CREDITS</a></h1>
<p><code>lsqlite3</code> was developed by Tiago Dionizio and Doug Currie with 
contributions from Thomas Lauer and Michael Roth.</p>
<p>This documentation is based on the &quot;(very) preliminary&quot; documents
................................................................................
<p>
</p>
<hr />
<h1><a name="license">LICENSE</a></h1>
<pre>
    /************************************************************************
    * lsqlite3                                                              *
    * Copyright (C) 2002-2013 Tiago Dionizio, Doug Currie                   *
    * All rights reserved.                                                  *
    * Author    : Tiago Dionizio &lt;tiago.dionizio@ist.utl.pt&gt;                *
    * Author    : Doug Currie &lt;doug.currie@alum.mit.edu&gt;                    *
    * Library   : lsqlite3 - a SQLite 3 database binding for Lua 5          *
    *                                                                       *
    * Permission is hereby granted, free of charge, to any person obtaining *
    * a copy of this software and associated documentation files (the       *

Added extras/Makefile.







































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
# Makefile for lsqlite3 library for Lua
# This file is old and crufty. 
# We recommend you make lsqlite3 using luarocks.
# A local make+install (to test source changes) can be done with:
# sudo luarocks make lsqlite3-0.9-1.rockspec 
#

# manual setup (change these to reflect your Lua installation)
#
BASE= /usr/local
LUAINC= -I$(BASE)/include
LUALIB=
SQLITE3INC= -I$(BASE)/include
SQLITE3LIB= -L$(BASE)/lib -lsqlite3
#  Windows' LUALIB is the same as the Lua executable's directory...
#  LUALIB= -L$(BASE)/bin -llua51
#
POD2HTML= perl -x -S doc/pod2html.pl

LUAEXE= lua

INSTALL= install -p
INSTALLPATH= $(LUAEXE) installpath.lua

TMP=./tmp
DISTDIR=./archive

# OS detection
#
SHFLAGS=-shared
UNAME= $(shell uname)
ifeq "$(UNAME)" "Linux"
  _SO=so
  SHFLAGS=-shared -fPIC
endif
ifneq "" "$(findstring BSD,$(UNAME))"
  _SO=so
endif
ifeq "$(UNAME)" "Darwin"
  _SO=so
  SHFLAGS=-fPIC -arch i686 -arch x86_64
# SOFLAGS=-dynamiclib -single_module -undefined dynamic_lookup -arch i686 -arch x86_64
  SOFLAGS=-dynamic -bundle -undefined dynamic_lookup -all_load -arch i686 -arch x86_64 
endif
ifneq "" "$(findstring msys,$(OSTYPE))"		# 'msys'
  _SO=dll
endif

ifndef _SO
  $(error $(UNAME))
  $(error Unknown OS)
endif

# no need to change anything below here - HAH!
CFLAGS= $(INCS) $(DEFS) $(WARN) -O2 -fomit-frame-pointer $(SHFLAGS)
WARN= -Wall #-ansi -pedantic -Wall
INCS= $(LUAINC) $(SQLITE3INC)
LIBS= $(LUALIB) $(SQLITE3LIB)

MYNAME= sqlite3
MYLIB= l$(MYNAME)

VER=$(shell svnversion -c . | sed 's/.*://')
TARFILE = $(DISTDIR)/$(MYLIB)-$(VER).tar.gz

OBJS= $(MYLIB).o
T= $(MYLIB).$(_SO)

all: $(T)

test: $(T)
	$(LUAEXE) test.lua
	$(LUAEXE) tests-sqlite3.lua

$(T):	$(OBJS)
	$(CC) $(SHFLAGS) $(SOFLAGS) -o $@ $(OBJS) $(LIBS)

install: $(T)
	$(INSTALL) $< `$(INSTALLPATH) $(MYLIB)`

clean:
	rm -f $(OBJS) $T core core.* a.out test.db

html:
	$(POD2HTML) --title="LuaSQLite 3" --infile=doc/lsqlite3.pod --outfile=doc/lsqlite3.html

dist:	html
	echo 'Exporting...'
	mkdir -p $(TMP)
	mkdir -p $(DISTDIR)
	svn export . $(TMP)/$(MYLIB)-$(VER)
	mkdir -p $(TMP)/$(MYLIB)-$(VER)/doc
	cp -p doc/lsqlite3.html $(TMP)/$(MYLIB)-$(VER)/doc
	echo 'Compressing...'
	tar -zcf $(TARFILE) -C $(TMP) $(MYLIB)-$(VER)
	rm -fr $(TMP)/$(MYLIB)-$(VER)
	echo 'Done.'

.PHONY: all test clean dist install

Added extras/installpath.lua.





























>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Script to find the install path for a C module. Public domain.

if not arg or not arg[1] then
  io.write("Usage: lua installpath.lua modulename\n")
  os.exit(1)
end
for p in string.gfind(package.cpath, "[^;]+") do
  if string.sub(p, 1, 1) ~= "." then
    local p2 = string.gsub(arg[1], "%.", string.sub(package.config, 1, 1))
    io.write(string.gsub(p, "%?", p2), "\n")
    return
  end
end
error("no suitable installation path found")

Changes to test/tests-sqlite3.lua.

22
23
24
25
26
27
28



29
30
31
32
33
34
35
...
491
492
493
494
495
496
497






























































































498
499
500
501
502
503
504
    IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
    CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,
    TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
    SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

--]]--------------------------------------------------------------------------





local sqlite3 = require "lsqlite3"

local os = os

local lunit = require "lunitx"

................................................................................
  local parameters = assert_number( stmt:bind_parameter_count() )
  assert_equal( 8, (parameters) )
  assert_nil( stmt:bind_parameter_name(1) )
end





































































































--------------------------------------------
-- Tests loop break and statement reusage --
--------------------------------------------







>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
...
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
    IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
    CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,
    TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
    SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

--]]--------------------------------------------------------------------------

-- extended for LuaSQLite3, and for Lua 5.2 (using lunitx)
-- Copyright (c) 2005-13 Doug Currie
-- Same license as above

local sqlite3 = require "lsqlite3"

local os = os

local lunit = require "lunitx"

................................................................................
  local parameters = assert_number( stmt:bind_parameter_count() )
  assert_equal( 8, (parameters) )
  assert_nil( stmt:bind_parameter_name(1) )
end




----------------------------
-- Tests for update_hook  --
----------------------------

uh = lunit_TestCase("Update Hook")

function uh.setup()
  uh.db = assert( sqlite3.open_memory() )
  uh.udtbl = {[sqlite3.INSERT]=0, [sqlite3.UPDATE]=0, [sqlite3.DELETE]=0}
  uh.uttblsz = function () local sz = 0; for _,_ in pairs(uh.udtbl) do sz = sz + 1 end return sz end
  assert_number( uh.db:exec("CREATE TABLE test ( id INTEGER PRIMARY KEY, content VARCHAR );") )
end

function uh.teardown()
  assert_number( uh.db:close() )
end

function uh.test_create()
  assert_nil(uh.db:update_hook( function(ud, op, dname, tname, rowid)
    --print("Sqlite Update Hook:", op, dname, tname, rowid)
    ud[op] = ud[op] + 1
  end, uh.udtbl))
end

function uh.test_insert1()
  assert_nil(uh.db:update_hook( function(ud, op, dname, tname, rowid)
    ud[op] = ud[op] + 1
  end, uh.udtbl))
  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") )
  --for k,v in pairs(uh.udtbl) do print(k,v) end
  assert_equal( 1, uh.udtbl[sqlite3.INSERT] )
end

function uh.test_insert3()
  assert_nil(uh.db:update_hook( function(ud, op, dname, tname, rowid)
    ud[op] = ud[op] + 1
  end, uh.udtbl))
  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") )
  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Lua');") )
  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Sqlite3');") )
  --for k,v in pairs(uh.udtbl) do print(k,v) end
  assert_equal( 3, uh.udtbl[sqlite3.INSERT] )
  assert_equal( 0, uh.udtbl[sqlite3.UPDATE] )
  assert_equal( 0, uh.udtbl[sqlite3.DELETE] )
  assert_equal( 3, uh.uttblsz() )
end

function uh.test_insert3update1()
  assert_nil(uh.db:update_hook( function(ud, op, dname, tname, rowid)
    ud[op] = ud[op] + 1
  end, uh.udtbl))
  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") )
  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Lua');") )
  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Sqlite3');") )
  assert_equal( sqlite3.OK, uh.db:exec("UPDATE test SET content = 'Hello Again World' WHERE id = 1;") )
  --for k,v in pairs(uh.udtbl) do print(k,v) end
  assert_equal( 3, uh.udtbl[sqlite3.INSERT] )
  assert_equal( 1, uh.udtbl[sqlite3.UPDATE] )
  assert_equal( 0, uh.udtbl[sqlite3.DELETE] )
  assert_equal( 3, uh.uttblsz() )
end

function uh.test_insert3delete1()
  assert_nil(uh.db:update_hook( function(ud, op, dname, tname, rowid)
    ud[op] = ud[op] + 1
  end, uh.udtbl))
  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") )
  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Lua');") )
  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Sqlite3');") )
  assert_equal( sqlite3.OK, uh.db:exec("DELETE FROM test WHERE id = 2;") )
  --for k,v in pairs(uh.udtbl) do print(k,v) end
  assert_equal( 3, uh.udtbl[sqlite3.INSERT] )
  assert_equal( 0, uh.udtbl[sqlite3.UPDATE] )
  assert_equal( 1, uh.udtbl[sqlite3.DELETE] )
  assert_equal( 3, uh.uttblsz() )
end

function uh.test_insert3update1delete1()
  assert_nil(uh.db:update_hook( function(ud, op, dname, tname, rowid)
    ud[op] = ud[op] + 1
  end, uh.udtbl))
  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") )
  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Lua');") )
  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Sqlite3');") )
  assert_equal( sqlite3.OK, uh.db:exec("UPDATE test SET content = 'Hello Again World' WHERE id = 1;") )
  assert_equal( sqlite3.OK, uh.db:exec("DELETE FROM test WHERE id = 2;") )
  --for k,v in pairs(uh.udtbl) do print(k,v) end
  assert_equal( 3, uh.udtbl[sqlite3.INSERT] )
  assert_equal( 1, uh.udtbl[sqlite3.UPDATE] )
  assert_equal( 1, uh.udtbl[sqlite3.DELETE] )
  assert_equal( 3, uh.uttblsz() )
end





--------------------------------------------
-- Tests loop break and statement reusage --
--------------------------------------------