LuaSQLite3

Check-in [340683bc64]
Login

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

Overview
Comment:Generalize Makefile. Update dist target for Subversion. Add examples to repo. Update HISTORY file and add to repo.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 340683bc649c6fe2a659fdeb11114e6f7f70c974
User & Date: e@ecd9bf9a-ecc1-ed47-8102-3ce978d4cc14 2006-10-02 21:29:53
Context
2007-08-17
21:42
Add collation support, tests, and doco from Thomas Lauer Convert doco to POD format Test with SQLite 3.4.2 Add html target to Makefile for converting POD doco Update HISTORY and README check-in: 7666e33cec user: e@ecd9bf9a-ecc1-ed47-8102-3ce978d4cc14 tags: trunk
2006-10-02
21:29
Generalize Makefile. Update dist target for Subversion. Add examples to repo. Update HISTORY file and add to repo. check-in: 340683bc64 user: e@ecd9bf9a-ecc1-ed47-8102-3ce978d4cc14 tags: trunk
2006-09-04
20:24
Reset persistent statements when for-loop terminates. check-in: 0da9110fc8 user: e@ecd9bf9a-ecc1-ed47-8102-3ce978d4cc14 tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Added HISTORY.





































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
2006-October-02 e

Since the "0.1-devel" release of this Lua library...
- updated for Lua 5.1
- provide automatic re-preparation of queries after schema changes
- made prepared statements with bindings work with for-loops
- added some compatibility names
- added many test cases, and ported Mike Roth's tests and examples

-=-

Below is a header comment from the 2004 "0.1" version of the library...

/************************************************************************
$Id: lsqlite3.c,v 1.3 2004/09/05 17:50:32 tngd Exp $

To consider:
------------

EXPERIMENTAL APIs

* sqlite3_progress_handler (implemented)
* sqlite3_commit_hook

TODO?

* sqlite3_create_collation

Changes:
04-09-2004
----------
    * changed second return value of db:compile to be the rest of the
    sql statement that was not processed instead of the number of
    characters of sql not processed (situation in case of success).
    * progress callback register function parameter order changed.
    number of opcodes is given before the callback now.

29-08-2004 e
------------
    * added version() (now supported in sqlite 3.0.5)
    * added db:errmsg db:errcode db:total_changes
    * rename vm:get_column to vm:get_value
    * merge in Tiago's v1.11 change in dbvm_tostring

23-06-2004 e
------------
    * heavily revised for SQLite3 C API
    * row values now returned as native type (not always text)
    * added db:nrows (named rows)
    * added vm:bind_blob
    * added vm:get_column
    * removed encode_binary decode_binary (no longer needed or supported)
    * removed version encoding error_string (unsupported in v 3.0.1 -- soon?)

09-04-2004
----------
    * renamed db:rows to db:urows
    * renamed db:prows to db:rows

    * added vm:get_unames()
    * added vm:get_utypes()
    * added vm:get_uvalues()

08-04-2004
----------
    * changed db:encoding() and db:version() to use sqlite_libencoding() and
    sqlite_libversion()

    * added vm:columns()
    * added vm:get_named_types()
    * added vm:get_named_values()

    * added db:prows - like db:rows but returns a table with the column values
    instead of returning multiple columns seperatly on each iteration

    * added compatibility functions idata,iname,itype,data,type

    * added luaopen_sqlite_module. allow the library to be loaded without
    setting a global variable. does the same as luaopen_sqlite, but does not
    set the global name "sqlite".

    * vm:bind now also returns an error string in case of error

31-03-2004 - 01-04-2004
-----------------------
    * changed most of the internals. now using references (luaL_ref) in
    most of the places

    * make the virtual machine interface seperate from the database
    handle. db:compile now returns a vm handle

    * added db:rows [for ... in db:rows(...) do ... end]

    * added db:close_vm

    * added sqlite.encode_binary and sqlite.decode_binary

    * attempt to do a strict checking on the return type of the user
    defined functions returned values

18-01-2004
----------
    * add check on sql function callback to ensure there is enough stack
    space to pass column values as parameters

03-12-2003
----------
    * callback functions now have to return boolean values to abort or
    continue operation instead of a zero or non-zero value

06-12-2003
----------
    * make version member of sqlite table a function instead of a string
************************************************************************/

Changes to 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


# makefile for lsqlite3 library for Lua

# dist location
DISTDIR=$(HOME)/dist
TMP=/tmp












# change these to reflect your Lua installation

LUA= /usr/local
LUAINC= $(LUA)/include
LUALIB= $(LUA)/lib
LUABIN= $(LUA)/bin
LUALUA= $(LUA)/lua








SQLITE= . #../sqlite































# no need to change anything below here
CFLAGS= $(INCS) $(DEFS) $(WARN) -O2 $(SHFLAGS)
#SHFLAGS= -fPIC
SHFLAGS=
WARN= -Wall #-ansi -pedantic -Wall
INCS= -I$(LUAINC) -I$(SQLITE)
#LIBS= -L$(LUALIB) -L$(SQLITE) -lsqlite3 -llualib5 -llua5
LIBS= -L$(LUALIB) -L$(SQLITE) $(LUABIN)/sqlite3.dll -llua51



MYNAME= sqlite3
MYLIB= l$(MYNAME)
VER=0.1-devel

TARFILE = $(DISTDIR)/$(MYLIB)-$(VER).tar.gz

OBJS= $(MYLIB).o
#T= $(MYLIB).so
T= $(MYLIB).dll

all: $T

test: $T
	$(LUABIN)/lua.exe test.lua


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




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

dist:
	@echo 'Exporting...'


	@cvs export -r HEAD -d $(TMP)/$(MYLIB)-$(VER) $(MYLIB)
	@echo 'Compressing...'
	@tar -zcf $(TARFILE) -C $(TMP) $(MYLIB)-$(VER)
	@rm -fr $(TMP)/$(MYLIB)-$(VER)
	@lsum $(TARFILE) $(DISTDIR)/md5sums.txt
	@echo 'Done.'




|
|
<
|
>
>
>
>
>
>
>
>
>
>
>
|
>
|
<
<
<
<
>
>
>
>
>
>
>
|
<
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|

<
<

<
<
<
>
>



|
>



|
<




|
>


|

>
>
>

|

|
|
>
>
|
|
|
|
<
|
>
>
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

ifneq "$(shell pkg-config --version)" ""
  # automagic setup (OS X fink, Linux apt-get, ..)

  #
  LUAINC= $(shell pkg-config --cflags lua)
  LUALIB= $(shell pkg-config --libs lua)
  LUAEXE= lua
  # Now, we actually want to _not_ push in stuff to the distro Lua CMOD directory,
  # way better to play within /usr/local/lib/lua/5.1/
  #LUACMOD= $(shell pkg-config --variable=INSTALL_CMOD lua)
  LUACMOD= /usr/local/lib/lua/5.1/
  #
  SQLITE3INC= $(shell pkg-config --cflags sqlite3)
  SQLITE3LIB= $(shell pkg-config --libs sqlite3)
else
  # manual setup (change these to reflect your Lua installation)
  #
  BASE= /usr/local




  LUAINC= -I$(BASE)/include
  LUAEXE= $(BASE)/bin/lua.exe
#  LUALIB= -L$(BASE)/lib -llua51
#  LUACMOD= $(BASE)/lib/lua/5.1/
#  Windows' LUA_CDIR and LUALIB are both the same as the Lua executable's directory...
  LUALIB= -L$(BASE)/bin -llua51
  LUACMOD= $(BASE)/bin
  #

  SQLITE3INC= -I$(BASE)/include
  SQLITE3LIB= -L$(BASE)/bin -lsqlite3
endif

TMP=./tmp
DISTDIR=./archive

# OS detection
#
SHFLAGS=-shared
UNAME= $(shell uname)
ifeq "$(UNAME)" "Linux"
  _SO=so
  SHFLAGS= -fPIC
endif
ifneq "" "$(findstring BSD,$(UNAME))"
  _SO=so
endif
ifeq "$(UNAME)" "Darwin"
  _SO=bundle
  SHFLAGS= -bundle
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 $(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) -o $@ $(OBJS) $(LIBS)

install:
	cp $T $(LUACMOD)

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

dist:
	echo 'Exporting...'
	mkdir -p $(TMP)
	mkdir -p $(DISTDIR)
	svn export -r HEAD . $(TMP)/$(MYLIB)-$(VER)
	echo 'Compressing...'
	tar -zcf $(TARFILE) -C $(TMP) $(MYLIB)-$(VER)
	rm -fr $(TMP)/$(MYLIB)-$(VER)

	echo 'Done.'

.PHONY: all test clean dist install

Added examples/aggregate.lua.







































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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

require("lsqlite3")

local db = sqlite3.open_memory()

assert( db:exec "CREATE TABLE test (col1, col2)" )
assert( db:exec "INSERT INTO test VALUES (1, 2)" )
assert( db:exec "INSERT INTO test VALUES (2, 4)" )
assert( db:exec "INSERT INTO test VALUES (3, 6)" )
assert( db:exec "INSERT INTO test VALUES (4, 8)" )
assert( db:exec "INSERT INTO test VALUES (5, 10)" )

do

  local square_error_sum = 0

  local function step(ctx, a, b)
    local error        = a - b
    local square_error = error * error
    square_error_sum   = square_error_sum + square_error
  end

  local function final(ctx)
    ctx:result_number( square_error_sum / ctx:aggregate_count() )
  end

  assert( db:create_aggregate("my_stats", 2, step, final) )

end

--for a,b in db:urows("SELECT col1, col2 FROM test")
--do print("a b: ", a, b) end

for my_stats in db:urows("SELECT my_stats(col1, col2) FROM test")
do print("my_stats:", my_stats) end

Added examples/function.lua.











































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

require("lsqlite3")


local db = sqlite3.open_memory()

assert( db:exec "CREATE TABLE test (col1, col2)" )
assert( db:exec "INSERT INTO test VALUES (1, 2)" )
assert( db:exec "INSERT INTO test VALUES (2, 4)" )
assert( db:exec "INSERT INTO test VALUES (3, 6)" )
assert( db:exec "INSERT INTO test VALUES (4, 8)" )
assert( db:exec "INSERT INTO test VALUES (5, 10)" )

assert( db:create_function("my_sum", 2, function(ctx, a, b)
  ctx:result_number( a + b )
end))


for col1, col2, sum in db:urows("SELECT *, my_sum(col1, col2) FROM test") do
  print(col1, col2, sum)
end

Added examples/order.lua.





















































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122

require("lsqlite3")

local db = assert( sqlite3:open_memory() )

assert( db:exec[[

  CREATE TABLE customer (
    id		INTEGER PRIMARY KEY, 
    name	VARCHAR(40)
  );

  CREATE TABLE invoice (
    id		INTEGER PRIMARY KEY,
    customer	INTEGER NOT NULL,
    title	VARCHAR(80) NOT NULL,
    article1	VARCHAR(40) NOT NULL,
    price1	REAL NOT NULL,
    article2	VARCHAR(40),
    price2	REAL
  );

  CREATE TABLE invoice_overflow (
    id		INTEGER PRIMARY KEY,
    invoice	INTEGER NOT NULL,
    article	VARCHAR(40) NOT NULL,
    price	REAL NOT NULL
  );

  INSERT INTO customer VALUES(
    1, "Michael" );

  INSERT INTO invoice VALUES(
    1, 1, "Computer parts", "harddisc", 89.90, "floppy", 9.99 );

  INSERT INTO customer VALUES(
    2, "John" );

  INSERT INTO invoice VALUES(
    2, 2, "Somme food", "apples", 2.79, "pears", 5.99 );

  INSERT INTO invoice_overflow VALUES(
    NULL, 2, "grapes", 6.34 );

  INSERT INTO invoice_overflow VALUES(
    NULL, 2, "strawberries", 4.12 );

  INSERT INTO invoice_overflow VALUES(
    NULL, 2, "tomatoes", 6.17 );

  INSERT INTO invoice VALUES(
    3, 2, "A new car", "Cybercar XL-1000", 65000.00, NULL, NULL );

]] )


local function customer_name(id)
  local stmt = db:prepare("SELECT name FROM customer WHERE id = ?")
  stmt:bind_values(id)
  stmt:step()
  local r = stmt:get_uvalues()
  stmt:finalize()
  return r
end


local function all_invoices()
  return db:nrows("SELECT id, customer, title FROM invoice")
end


local function all_articles(invoice)

  local function iterator()
    local stmt, row

    -- Get the articles that are contained in the invoice table itself.
    stmt = db:prepare("SELECT article1, price1, article2, price2 FROM invoice WHERE id = ?")
    stmt:bind_values(invoice)
    stmt:step()
    row = stmt:get_named_values()

    -- Every Invoice has at least one article.
    coroutine.yield(row.article1, row.price1)

    -- Maybe the Invoice has a second article?
    if row.article2 then

      -- Yes, there is a second article, so return it.
      coroutine.yield(row.article2, row.price2)

      -- When there was an second article, maybe there are even
      -- more articles in the overflow table? We will see...

      stmt = db:prepare("SELECT article, price FROM invoice_overflow WHERE invoice = ? ORDER BY id")
      stmt:bind_values(invoice)
      
      for row in stmt:nrows() do
        coroutine.yield(row.article, row.price)
      end
    end
  end

  return coroutine.wrap(iterator)
end


for invoice in all_invoices() do
  local id    = invoice.id
  local name  = customer_name(invoice.customer)
  local title = invoice.title

  print()
  print("Invoice #"..id..", "..name..": '"..title.."'")
  print("----------------------------------------")

  for article, price in all_articles(id) do
    print( string.format("%20s  %8.2f", article, price) )
  end

  print()
end

Added examples/simple.lua.

































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

require("lsqlite3")

local db = sqlite3.open_memory()

db:exec[[
  CREATE TABLE test (id INTEGER PRIMARY KEY, content);

  INSERT INTO test VALUES (NULL, 'Hello World');
  INSERT INTO test VALUES (NULL, 'Hello Lua');
  INSERT INTO test VALUES (NULL, 'Hello Sqlite3')
]]

for row in db:nrows("SELECT * FROM test") do
  print(row.id, row.content)
end

Added examples/smart.lua.













































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

require("lsqlite3")

local db = sqlite3.open_memory()

db:exec[[ CREATE TABLE test (id INTEGER PRIMARY KEY, content) ]]

local stmt = db:prepare[[ INSERT INTO test VALUES (:key, :value) ]]

stmt:bind_names{  key = 1,  value = "Hello World"    }
stmt:step()
stmt:reset()
stmt:bind_names{  key = 2,  value = "Hello Lua"      }
stmt:step()
stmt:reset()
stmt:bind_names{  key = 3,  value = "Hello Sqlite3"  }
stmt:step()
stmt:finalize()

for row in db:nrows("SELECT * FROM test") do
  print(row.id, row.content)
end

Added examples/statement.lua.















































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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

require("lsqlite3")

local db = sqlite3.open_memory()

db:exec[[
  CREATE TABLE test (
    id        INTEGER PRIMARY KEY,
    content   VARCHAR
  );
]]

local insert_stmt = assert( db:prepare("INSERT INTO test VALUES (NULL, ?)") )

local function insert(data)
  insert_stmt:bind_values(data)
  insert_stmt:step()
  insert_stmt:reset()
end

local select_stmt = assert( db:prepare("SELECT * FROM test") )

local function select()
  for row in select_stmt:nrows() do
    print(row.id, row.content)
  end
end

insert("Hello World")
print("First:")
select()

insert("Hello Lua")
print("Second:")
select()

insert("Hello Sqlite3")
print("Third:")
select()

Added examples/tracing.lua.









































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

require("lsqlite3")

local db = sqlite3.open_memory()

db:trace( function(ud, sql)
  print("Sqlite Trace:", sql)
end )

db:exec[[
  CREATE TABLE test ( id INTEGER PRIMARY KEY, content VARCHAR );

  INSERT INTO test VALUES (NULL, 'Hello World');
  INSERT INTO test VALUES (NULL, 'Hello Lua');
  INSERT INTO test VALUES (NULL, 'Hello Sqlite3');
]]

for row in db:rows("SELECT * FROM test") do
  -- NOP
end