imported trunk from sourceforge SVN

24 files changed, 1093 insertions, 0 deletions

diff --git a/tests/GNUmakefile b/tests/GNUmakefile
new file mode 100644
index 0000000..1a70297
--- /dev/null
+++ b/tests/GNUmakefile
@@ -0,0 +1,65 @@
+TOPDIR = ..
+
+SUBDIRS =
+
+INCLUDE_DIRS = \
+	$(INCLUDE_FLAGS_SQLITE3) \
+	-I$(TOPDIR)/include
+
+INCLUDE_LDFLAGS = \
+	$(LDFLAGS_SQLITE3)
+
+INCLUDE_LIBS = \
+	$(TOPDIR)/src/libsqlite3xx.a \
+	$(LIBS_SQLITE3) \
+	$(LIBS_DL) \
+	-lstdc++
+
+TEST_CPP_BINS = \
+	test1$(EXE) \
+	test2$(EXE) \
+	test3$(EXE) \
+	test4$(EXE) \
+	test5$(EXE) \
+	test6$(EXE) \
+	test7$(EXE) \
+	test8$(EXE)
+
+TEST_BINS = \
+	testc$(EXE)
+
+OBJS =
+
+-include $(TOPDIR)/makefiles/gmake/sub.mk
+
+local_all:
+
+local_clean:
+	-@rm -f *.db *.db-journal 2>/dev/null
+	-@rm -f *.RES *.DIFF
+
+local_distclean:
+
+test1$(EXE): $(TOPDIR)/src/libsqlite3xx.a
+test2$(EXE): $(TOPDIR)/src/libsqlite3xx.a
+test3$(EXE): $(TOPDIR)/src/libsqlite3xx.a
+test4$(EXE): $(TOPDIR)/src/libsqlite3xx.a
+test5$(EXE): $(TOPDIR)/src/libsqlite3xx.a
+test6$(EXE): $(TOPDIR)/src/libsqlite3xx.a
+test7$(EXE): $(TOPDIR)/src/libsqlite3xx.a
+test8$(EXE): $(TOPDIR)/src/libsqlite3xx.a
+
+# disabled tests
+#-@./exec_test test6 "STL iterators" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
+#-@./exec_test test7 "tracing" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
+
+# create the must files with ./test2 | sed 's/\(time:[^\)]*\)//g' > test2.MUST
+# to eliminate the execution time..
+local_test:
+	@./exec_test testc "test plain old C API" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
+	@./exec_test test1 "create and close database" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
+	@./exec_test test2 "create simple table, insert some data, query it" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
+	@./exec_test test3 "prepared statements with parameter binding" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
+	@./exec_test test4 "type conversion of bind parameters" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
+	@./exec_test test5 "PRAGMAs outside a transaction" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
+	@./exec_test test8 "ticket #14: to conversion fails with SQLITE_NULL" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
diff --git a/tests/Makefile.W32 b/tests/Makefile.W32
new file mode 100644
index 0000000..45dbccc
--- /dev/null
+++ b/tests/Makefile.W32
@@ -0,0 +1,64 @@
+TOPDIR = ..
+
+SUBDIRS =
+
+INCLUDE_DIRS = \
+	/I$(TOPDIR)\include /I. \
+	/I$(TOPDIR)\sqlite-3.6.22 \
+	/D_WIN32_WINNT=0x400 /I"$(PLATFORM_SDK_DIR)\Include"
+
+INCLUDE_LDFLAGS = \
+	/LIBPATH:"$(PLATFORM_SDK_DIR)\lib"
+
+INCLUDE_LIBS = \
+	$(TOPDIR)\src\sqlite3xx.lib \
+	$(TOPDIR)\sqlite-3.6.22\sqlite3.lib
+
+TEST_CPP_BINS = \
+	test1.exe \
+	test2.exe \
+	test3.exe \
+	test4.exe \
+	test5.exe \
+	test6.exe \
+	test7.exe \
+	test8.exe
+
+TEST_BINS = \
+	testc.exe
+
+!INCLUDE $(TOPDIR)\makefiles\nmake\sub.mk
+
+# temporary
+testc.exe: testc.obj
+test1.exe: test1.obj
+test2.exe: test2.obj
+test3.exe: test3.obj
+test4.exe: test4.obj
+test5.exe: test5.obj
+test6.exe: test6.obj
+test7.exe: test7.obj
+test8.exe: test8.obj
+
+local_all:
+
+local_clean:
+	@-erase *.db *.db-journal 2>NUL
+	@-erase *.RES *.DIFF 2>NUL
+
+local_distclean:
+
+# disabled tests
+#-@./exec_test test6 "STL iterators" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
+#-@./exec_test test7 "tracing" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
+
+# create the must files with ./test2 | sed 's/\(time:[^\)]*\)//g' > test2.MUST
+# to eliminate the execution time..
+local_test: $(CPP_BINS) $(BINS)
+	@exec_test.cmd testc "test plain old C API" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
+	@exec_test.cmd test1 "create and close database" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
+	@exec_test.cmd test2 "create simple table, insert some data, query it" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
+	@exec_test.cmd test3 "prepared statements with parameter binding" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
+	@exec_test.cmd test4 "type conversion of bind parameters" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
+	@exec_test.cmd test5 "PRAGMAs outside a transaction" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
+	@exec_test.cmd test8 "ticket #14: to conversion fails with SQLITE_NULL" "$(PLATFORM)" "$(LINUX_DIST)" "$(LINUX_REV)"
diff --git a/tests/README b/tests/README
new file mode 100644
index 0000000..3eb6c9d
--- /dev/null
+++ b/tests/README
@@ -0,0 +1,9 @@
+testc	- plain C sqlite test
+test1	- basic connect and disconnect in the scope of the connection object
+test2	- basic DDL, insert data, select data, size() and column() operators
+test3	- select data, accessors and associative arrays for column reference
+test4	- more complex prepare and insert statement
+test5	- exec without transaction, execution of pragmas
+test6	- basic cursor with result::const_iterator
+test7	- test tracing in sqlitexx and sqlite library
+test8	- test mapping data colums containing NULL values to various types
diff --git a/tests/exec_test b/tests/exec_test
new file mode 100755
index 0000000..b49999b
--- /dev/null
+++ b/tests/exec_test
@@ -0,0 +1,21 @@
+#!/bin/sh
+
+BINARY=$1
+TITLE=$2
+PLATFORM=$3
+LINUX_DIST=$4
+LINUX_REV=$5
+
+if test "x${PLATFORM}" = "xLINUX"; then
+SPECIAL="${PLATFORM}_${LINUX_DIST}_${LINUX_REV}"
+else
+SPECIAL="${PLATFORM}"
+fi
+
+printf "$BINARY: $TITLE .. "
+./$BINARY | sed 's/\(time: .* ms\)//g' >$BINARY.RES 2>&1
+if test -f $BINARY.MUST.$SPECIAL; then
+  diff $BINARY.MUST.$SPECIAL $BINARY.RES > $BINARY.DIFF && printf "OK\n" || printf "ERROR\n"
+else
+  diff $BINARY.MUST $BINARY.RES > $BINARY.DIFF && printf "OK\n" || printf "ERROR\n"
+fi
diff --git a/tests/exec_test.cmd b/tests/exec_test.cmd
new file mode 100755
index 0000000..25d6ea4
--- /dev/null
+++ b/tests/exec_test.cmd
@@ -0,0 +1,20 @@
+@echo off
+
+set BINARY=%1
+set TITLE=%2
+set PLATFORM=%3
+
+rem Set path so we find the sqlite3xx.dll (don't copy it around! linker paths are absolute!)
+set PATH=..\src;%PATH%
+
+%BINARY% >%BINARY.RES
+if ERRORLEVEL == 1 goto :ERROR
+echo %BINARY%: %TITLE% .. OK
+goto END
+
+:ERROR
+echo %BINARY%: %TITLE% .. ERROR
+:END
+
+rem TODO: find a way to do this with things provided by the system
+rem diff $BINARY.MUST $BINARY.RES > $BINARY.DIFF && printf "OK\n" || printf "ERROR\n"
diff --git a/tests/test1.MUST b/tests/test1.MUST
new file mode 100644
index 0000000..d0c163f
--- /dev/null
+++ b/tests/test1.MUST
@@ -0,0 +1 @@
+Connected to test1.db.
diff --git a/tests/test1.cpp b/tests/test1.cpp
new file mode 100644
index 0000000..1c8cabb
--- /dev/null
+++ b/tests/test1.cpp
@@ -0,0 +1,33 @@
+/*
+ *  sqlite3xx - sqlite3 C++ layer, following the ideas of libpqxx
+ *  Copyright (C) 2009  Andreas Baumann
+ *
+ *  This copyrighted material is made available to anyone wishing to use,
+ *  modify, copy, or redistribute it subject to the terms and conditions of
+ *  the GNU Lesser General Public License, as published by the Free Software
+ *  Foundation.
+ *
+ *  This program is distributed in the hope that it will be useful,
+ *  but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *  MERCHANTABILITY  or FITNESS FOR A PARTICULAR PURPOSE.
+ *  See the GNU Lesser General Public License for more details.
+ *
+ *  You should have received a copy of the GNU Lesser General Public License
+ *  along with this distribution; if not, write to:
+ *  Free Software Foundation, Inc.
+ *  51 Franklin Street, Fifth Floor
+ *  Boston, MA  02110-1301  USA
+ *
+ */
+
+#include "sqlite3xx/sqlite3xx"
+
+#include <iostream>
+
+using namespace sqlite3xx;
+
+int main( ) {
+	connection c( "test1.db" );
+
+	cout << "Connected to " << c.dbname( ) << "." << endl;
+}
diff --git a/tests/test2.MUST b/tests/test2.MUST
new file mode 100644
index 0000000..bde8cad
--- /dev/null
+++ b/tests/test2.MUST
@@ -0,0 +1,10 @@
+creating DB..
+starting transaction..
+create table..
+insert some data..
+querying..
+found 2 records..
+5
+18
+7
+end.
diff --git a/tests/test2.cpp b/tests/test2.cpp
new file mode 100644
index 0000000..95b0f0a
--- /dev/null
+++ b/tests/test2.cpp
@@ -0,0 +1,72 @@
+/*
+ *  sqlite3xx - sqlite3 C++ layer, following the ideas of libpqxx
+ *  Copyright (C) 2009  Andreas Baumann
+ *
+ *  This copyrighted material is made available to anyone wishing to use,
+ *  modify, copy, or redistribute it subject to the terms and conditions of
+ *  the GNU Lesser General Public License, as published by the Free Software
+ *  Foundation.
+ *
+ *  This program is distributed in the hope that it will be useful,
+ *  but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *  MERCHANTABILITY  or FITNESS FOR A PARTICULAR PURPOSE.
+ *  See the GNU Lesser General Public License for more details.
+ *
+ *  You should have received a copy of the GNU Lesser General Public License
+ *  along with this distribution; if not, write to:
+ *  Free Software Foundation, Inc.
+ *  51 Franklin Street, Fifth Floor
+ *  Boston, MA  02110-1301  USA
+ *
+ */
+
+#include <iostream>
+#include <cassert>
+#include "sqlite3xx/sqlite3xx"
+/* test needs 'unlink' from 'unistd.h' */
+#if !defined _WIN32
+#include <unistd.h>
+#endif /* !defined _WIN32 */
+
+using namespace sqlite3xx;
+using namespace std;
+
+int main( ) {
+	(void)unlink( "test2.db" );
+
+	try {
+		cout << "creating DB.." << endl;
+		connection c( "test2.db" );
+
+		cout << "starting transaction.." << endl;
+		work wc( c, "create" );
+
+		cout << "create table.." << endl;
+		wc.exec( "create table a( b integer )" );
+		wc.commit( );
+
+		work wi( c, "insert" );
+		cout << "insert some data.." << endl;
+		wi.exec( "insert into a( b ) values( 5 )" );
+		wi.exec( "insert into a( b ) values( 18 )" );
+		wi.exec( "insert into a( b ) values( 7 )" );
+		wi.commit( );
+
+		work wq( c, "query" );
+		cout << "querying.." << endl;
+		result r = wq.exec( "select * from a" );
+		// size() can't be 3 from the beginning, but must be 3 at the
+		// end. as we do a lookahead of 1 the size must be 2 at beginning
+		cout << "found " << r.size( ) << " records.." << endl;
+		assert( r.size( ) == 2 );
+	        for( result::size_type i = 0; i < r.size( ); i++ ) {
+			cout << r[i]["b"] << endl;
+		}
+		assert( r.size( ) == 3 );
+		wq.commit( );
+
+		cout << "end." << endl;
+	} catch( sql_error& e ) {
+		cerr << e.msg( ) << ": " << e.query( ) << endl;
+	}
+}
diff --git a/tests/test3.MUST b/tests/test3.MUST
new file mode 100644
index 0000000..fd0d3a4
--- /dev/null
+++ b/tests/test3.MUST
@@ -0,0 +1,23 @@
+creating DB..
+connection object is sqlite3xx::connection(test3.db)
+starting transaction..
+create table..
+insert some data..
+inserted 1 rows.
+inserted 1 rows.
+inserted 1 rows.
+querying..
+found 2 records..
+found 3 columns..
+column 0: 1
+column 1: 3
+column 2: 2
+i: 0
+(by col number) i: 5, s: bla, d: 8.933
+(by col name) i: 5, s: bla, d: 8.933
+(mapped) i: 5, s: bla, d: 8.933
+i: 1
+(by col number) i: 7, s: blu, d: -1.11111
+(by col name) i: 7, s: blu, d: -1.11111
+(mapped) i: 7, s: blu, d: -1.11111
+end.
diff --git a/tests/test3.cpp b/tests/test3.cpp
new file mode 100644
index 0000000..16ce68f
--- /dev/null
+++ b/tests/test3.cpp
@@ -0,0 +1,102 @@
+/*
+ *  sqlite3xx - sqlite3 C++ layer, following the ideas of libpqxx
+ *  Copyright (C) 2009  Andreas Baumann
+ *
+ *  This copyrighted material is made available to anyone wishing to use,
+ *  modify, copy, or redistribute it subject to the terms and conditions of
+ *  the GNU Lesser General Public License, as published by the Free Software
+ *  Foundation.
+ *
+ *  This program is distributed in the hope that it will be useful,
+ *  but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *  MERCHANTABILITY  or FITNESS FOR A PARTICULAR PURPOSE.
+ *  See the GNU Lesser General Public License for more details.
+ *
+ *  You should have received a copy of the GNU Lesser General Public License
+ *  along with this distribution; if not, write to:
+ *  Free Software Foundation, Inc.
+ *  51 Franklin Street, Fifth Floor
+ *  Boston, MA  02110-1301  USA
+ *
+ */
+
+#include <iostream>
+#include <cassert>
+#include "sqlite3xx/sqlite3xx"
+/* test needs 'unlink' from 'unistd.h' */
+#if !defined _WIN32
+#include <unistd.h>
+#endif /* !defined _WIN32 */
+
+using namespace sqlite3xx;
+using namespace std;
+
+int main( ) {
+	(void)unlink( "test3.db" );
+
+	try {
+		cout << "creating DB.." << endl;
+		connection c( "test3.db" );
+		cout << "connection object is " << c << endl;
+
+		cout << "starting transaction.." << endl;
+		work wc( c, "create" );
+
+		cout << "create table.." << endl;
+		wc.exec( "create table a( i integer, s text, d double )" );
+		wc.commit( );
+
+		work wi( c, "insert" );
+		cout << "insert some data.." << endl;
+		c.prepare( "ins", "insert into a( i, s, d ) values( $1, $2, $3 )" )
+			( "integer", prepare::treat_direct )
+			( "text", prepare::treat_direct )
+			( "double", prepare::treat_direct );
+		result r = wi.prepared( "ins" )( 5 )( "bla" )( 8.933 ).exec( );
+		cout << "inserted " << r.affected_rows( ) << " rows." << endl;
+		r = wi.prepared( "ins" )( 18 )( "bli" )( 4.7771 ).exec( );
+		cout << "inserted " << r.affected_rows( ) << " rows." << endl;
+		r = wi.prepared( "ins" )( 7 )( "blu" )( -1.11111 ).exec( );
+		cout << "inserted " << r.affected_rows( ) << " rows." << endl;
+		wi.commit( );
+
+		work wq( c, "query" );
+		cout << "querying.." << endl;
+		c.prepare( "qry", "select * from a where i>=$1 and i<=$2" )
+			( "integer", prepare::treat_direct )
+			( "integer", prepare::treat_direct );
+
+		r = wq.prepared( "qry" )( 4 )( 8 ).exec( );
+		cout << "found " << r.size( ) << " records.." << endl;
+		cout << "found " << r.columns( ) << " columns.." << endl;
+		for( result::size_type i = 0; i < r.columns( ); i++ ) {
+			cout << "column " << i << ": "  << r.column_type( i ) << endl;
+		}
+		assert( r.size( ) == 2 );
+
+	        for( result::size_type i = 0; i < r.size( ); i++ ) {
+			cout << "i: " << i << endl;
+
+			// by field number
+			cout << "(by col number) i: " << r[i][0] << ", s: " << r[i][1] << ", d: " << r[i][2] << endl;
+
+			// associative array
+			cout << "(by col name) i: " << r[i]["i"] << ", s: " << r[i]["s"] << ", d: " << r[i]["d"] << endl;
+
+			// map to variables of a given type
+			int value_i;
+			string value_s;
+			double value_d;
+			r[i]["i"].to( value_i );
+			r[i]["s"].to( value_s );
+			r[i]["d"].to( value_d );
+			cout << "(mapped) i: " << value_i << ", s: " << value_s << ", d: " << value_d << endl;
+		}
+		assert( r.size( ) == 2 );
+		wq.commit( );
+
+		cout << "end." << endl;
+	} catch( sql_error& e ) {
+		cerr << e.msg( ) << ": " << e.query( ) << endl;
+	}
+}
diff --git a/tests/test4.MUST b/tests/test4.MUST
new file mode 100644
index 0000000..478698f
--- /dev/null
+++ b/tests/test4.MUST
@@ -0,0 +1,8 @@
+creating DB..
+connection object is sqlite3xx::connection(test4.db)
+starting transaction..
+create table..
+insert some data..
+inserted with proper types 1 rows.
+inserted all as strings 1 rows.
+end.
diff --git a/tests/test4.cpp b/tests/test4.cpp
new file mode 100644
index 0000000..b9dd69e
--- /dev/null
+++ b/tests/test4.cpp
@@ -0,0 +1,78 @@
+/*
+ *  sqlite3xx - sqlite3 C++ layer, following the ideas of libpqxx
+ *  Copyright (C) 2009  Andreas Baumann
+ *
+ *  This copyrighted material is made available to anyone wishing to use,
+ *  modify, copy, or redistribute it subject to the terms and conditions of
+ *  the GNU Lesser General Public License, as published by the Free Software
+ *  Foundation.
+ *
+ *  This program is distributed in the hope that it will be useful,
+ *  but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *  MERCHANTABILITY  or FITNESS FOR A PARTICULAR PURPOSE.
+ *  See the GNU Lesser General Public License for more details.
+ *
+ *  You should have received a copy of the GNU Lesser General Public License
+ *  along with this distribution; if not, write to:
+ *  Free Software Foundation, Inc.
+ *  51 Franklin Street, Fifth Floor
+ *  Boston, MA  02110-1301  USA
+ *
+ */
+
+#include <iostream>
+#include "sqlite3xx/sqlite3xx"
+/* test needs 'unlink' from 'unistd.h' */
+#if !defined _WIN32
+#include <unistd.h>
+#endif /* !defined _WIN32 */
+
+using namespace sqlite3xx;
+using namespace std;
+
+int main( ) {
+	(void)unlink( "test4.db" );
+
+	try {
+		cout << "creating DB.." << endl;
+		connection c( "test4.db" );
+		cout << "connection object is " << c << endl;
+
+		cout << "starting transaction.." << endl;
+		work wc( c, "create" );
+
+		cout << "create table.." << endl;
+		wc.exec( "create table ally( id integer primary key, name text, tag text, members integer, villages integer, points integer, all_points integer, rank integer)" );
+		wc.commit( );
+
+		work wi( c, "insert" );
+		cout << "insert some data.." << endl;
+		c.prepare( "ins", "insert into ally( id, name, tag, members, villages, points, all_points, rank ) values( $1, $2, $3, $4, $5, $6, $7, $8 )" )
+				( "integer", sqlite3xx::prepare::treat_direct )
+				( "text", sqlite3xx::prepare::treat_direct )
+				( "text", sqlite3xx::prepare::treat_direct )
+				( "integer", sqlite3xx::prepare::treat_direct )
+				( "integer", sqlite3xx::prepare::treat_direct )
+				( "integer", sqlite3xx::prepare::treat_direct )
+				( "integer", sqlite3xx::prepare::treat_direct )
+				( "integer", sqlite3xx::prepare::treat_direct );
+		c.prepare( "ins2", "insert into ally( id, name, tag, members, villages, points, all_points, rank ) values( $1, $2, $3, $4, $5, $6, $7, $8 )" )
+				( "integer", sqlite3xx::prepare::treat_direct )
+				( "text", sqlite3xx::prepare::treat_direct )
+				( "text", sqlite3xx::prepare::treat_direct )
+				( "integer", sqlite3xx::prepare::treat_direct )
+				( "integer", sqlite3xx::prepare::treat_direct )
+				( "integer", sqlite3xx::prepare::treat_direct )
+				( "integer", sqlite3xx::prepare::treat_direct )
+				( "integer", sqlite3xx::prepare::treat_direct );
+		result r = wi.prepared( "ins" )(1350)("Die+EliTe")("EliTe")(34)(34)(11708)(11708)(92).exec( );
+		cout << "inserted with proper types " << r.affected_rows( ) << " rows." << endl;
+		r = wi.prepared( "ins2" )("1351")("Die+EliTe")("EliTe")("34")("34")("11708")("11708")("92").exec( );
+		cout << "inserted all as strings " << r.affected_rows( ) << " rows." << endl;
+		wi.commit( );
+
+		cout << "end." << endl;
+	} catch( sql_error& e ) {
+		cerr << e.msg( ) << ": " << e.query( ) << endl;
+	}
+}
diff --git a/tests/test5.MUST b/tests/test5.MUST
new file mode 100644
index 0000000..b3e0bcb
--- /dev/null
+++ b/tests/test5.MUST
@@ -0,0 +1,4 @@
+creating DB..
+connection object is sqlite3xx::connection(test5.db)
+execute pragma synchronous=0..
+end.
diff --git a/tests/test5.cpp b/tests/test5.cpp
new file mode 100644
index 0000000..e67c4ad
--- /dev/null
+++ b/tests/test5.cpp
@@ -0,0 +1,49 @@
+/*
+ *  sqlite3xx - sqlite3 C++ layer, following the ideas of libpqxx
+ *  Copyright (C) 2009  Andreas Baumann
+ *
+ *  This copyrighted material is made available to anyone wishing to use,
+ *  modify, copy, or redistribute it subject to the terms and conditions of
+ *  the GNU Lesser General Public License, as published by the Free Software
+ *  Foundation.
+ *
+ *  This program is distributed in the hope that it will be useful,
+ *  but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *  MERCHANTABILITY  or FITNESS FOR A PARTICULAR PURPOSE.
+ *  See the GNU Lesser General Public License for more details.
+ *
+ *  You should have received a copy of the GNU Lesser General Public License
+ *  along with this distribution; if not, write to:
+ *  Free Software Foundation, Inc.
+ *  51 Franklin Street, Fifth Floor
+ *  Boston, MA  02110-1301  USA
+ *
+ */
+
+#include <iostream>
+#include "sqlite3xx/sqlite3xx"
+/* test needs 'unlink' from 'unistd.h' */
+#if !defined _WIN32
+#include <unistd.h>
+#endif /* !defined _WIN32 */
+
+using namespace sqlite3xx;
+using namespace std;
+
+int main( ) {
+	(void)unlink( "test5.db" );
+
+	try {
+		cout << "creating DB.." << endl;
+		connection c( "test5.db" );
+		cout << "connection object is " << c << endl;
+
+		// switch of fdatasync to be real fast
+		cout << "execute pragma synchronous=0.." << endl;
+		c.exec( "PRAGMA synchronous=0" );
+
+		cout << "end." << endl;
+	} catch( sql_error& e ) {
+		cerr << e.msg( ) << ": " << e.query( ) << endl;
+	}
+}
diff --git a/tests/test6.MUST b/tests/test6.MUST
new file mode 100644
index 0000000..03c69b7
--- /dev/null
+++ b/tests/test6.MUST
@@ -0,0 +1,38 @@
+creating DB..
+starting transaction..
+create table..
+TRACE: prepared 'internal_sqlite3xx::connection(test6.db)_1' as 'BEGIN TRANSACTION trans_create'
+TRACE: exec 'internal_sqlite3xx::connection(test6.db)_1'
+TRACE: BEGIN TRANSACTION trans_create ()
+TRACE: prepared 'internal_sqlite3xx::connection(test6.db)_2' as 'create table a( b integer )'
+TRACE: exec 'internal_sqlite3xx::connection(test6.db)_2'
+TRACE: create table a( b integer ) ()
+TRACE: prepared 'internal_sqlite3xx::connection(test6.db)_3' as 'COMMIT TRANSACTION trans_create'
+TRACE: exec 'internal_sqlite3xx::connection(test6.db)_3'
+TRACE: COMMIT TRANSACTION trans_create ()
+insert some data..
+TRACE: prepared 'internal_sqlite3xx::connection(test6.db)_4' as 'BEGIN TRANSACTION trans_insert'
+TRACE: exec 'internal_sqlite3xx::connection(test6.db)_4'
+TRACE: BEGIN TRANSACTION trans_insert ()
+TRACE: prepared 'internal_sqlite3xx::connection(test6.db)_5' as 'insert into a( b ) values( 5 )'
+TRACE: exec 'internal_sqlite3xx::connection(test6.db)_5'
+TRACE: insert into a( b ) values( 5 ) ()
+TRACE: prepared 'internal_sqlite3xx::connection(test6.db)_6' as 'insert into a( b ) values( 18 )'
+TRACE: exec 'internal_sqlite3xx::connection(test6.db)_6'
+TRACE: insert into a( b ) values( 18 ) ()
+TRACE: prepared 'internal_sqlite3xx::connection(test6.db)_7' as 'insert into a( b ) values( 7 )'
+TRACE: exec 'internal_sqlite3xx::connection(test6.db)_7'
+TRACE: insert into a( b ) values( 7 ) ()
+TRACE: prepared 'internal_sqlite3xx::connection(test6.db)_8' as 'COMMIT TRANSACTION trans_insert'
+TRACE: exec 'internal_sqlite3xx::connection(test6.db)_8'
+TRACE: COMMIT TRANSACTION trans_insert ()
+querying..
+TRACE: prepared 'internal_sqlite3xx::connection(test6.db)_9' as 'BEGIN TRANSACTION trans_query'
+TRACE: exec 'internal_sqlite3xx::connection(test6.db)_9'
+TRACE: BEGIN TRANSACTION trans_query ()
+TRACE: prepared 'internal_sqlite3xx::connection(test6.db)_10' as 'select * from a'
+TRACE: exec 'internal_sqlite3xx::connection(test6.db)_10'
+TRACE: prepared 'internal_sqlite3xx::connection(test6.db)_11' as 'COMMIT TRANSACTION trans_query'
+TRACE: exec 'internal_sqlite3xx::connection(test6.db)_11'
+TRACE: COMMIT TRANSACTION trans_query ()
+end.
diff --git a/tests/test6.cpp b/tests/test6.cpp
new file mode 100644
index 0000000..d8837f1
--- /dev/null
+++ b/tests/test6.cpp
@@ -0,0 +1,68 @@
+/*
+ *  sqlite3xx - sqlite3 C++ layer, following the ideas of libpqxx
+ *  Copyright (C) 2009  Andreas Baumann
+ *
+ *  This copyrighted material is made available to anyone wishing to use,
+ *  modify, copy, or redistribute it subject to the terms and conditions of
+ *  the GNU Lesser General Public License, as published by the Free Software
+ *  Foundation.
+ *
+ *  This program is distributed in the hope that it will be useful,
+ *  but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *  MERCHANTABILITY  or FITNESS FOR A PARTICULAR PURPOSE.
+ *  See the GNU Lesser General Public License for more details.
+ *
+ *  You should have received a copy of the GNU Lesser General Public License
+ *  along with this distribution; if not, write to:
+ *  Free Software Foundation, Inc.
+ *  51 Franklin Street, Fifth Floor
+ *  Boston, MA  02110-1301  USA
+ *
+ */
+
+#include <iostream>
+#include "sqlite3xx/sqlite3xx"
+/* test needs 'unlink' from 'unistd.h' */
+#if !defined _WIN32
+#include <unistd.h>
+#endif /* !defined _WIN32 */
+
+using namespace sqlite3xx;
+using namespace std;
+
+int main( ) {
+	(void)unlink( "test6.db" );
+
+	try {
+		cout << "creating DB.." << endl;
+		connection c( "test6.db" );
+
+		cout << "starting transaction.." << endl;
+		work wc( c, "create" );
+
+		cout << "create table.." << endl;
+		wc.exec( "create table a( b integer )" );
+		wc.commit( );
+
+		work wi( c, "insert" );
+		cout << "insert some data.." << endl;
+		wi.exec( "insert into a( b ) values( 5 )" );
+		wi.exec( "insert into a( b ) values( 18 )" );
+		wi.exec( "insert into a( b ) values( 7 )" );
+		wi.commit( );
+
+		work wq( c, "query" );
+		cout << "querying.." << endl;
+		result r = wq.exec( "select * from a" );
+
+		// traditional STL iterator
+	        for( result::const_iterator i = r.begin( ); i < r.end( ); i++ ) {
+			cout << i->rownumber( ) << i["b"] << endl;
+		}
+		wq.commit( );
+
+		cout << "end." << endl;
+	} catch( sql_error& e ) {
+		cerr << e.msg( ) << ": " << e.query( ) << endl;
+	}
+}
diff --git a/tests/test7.cpp b/tests/test7.cpp
new file mode 100644
index 0000000..a4ad588
--- /dev/null
+++ b/tests/test7.cpp
@@ -0,0 +1,77 @@
+/*
+ *  sqlite3xx - sqlite3 C++ layer, following the ideas of libpqxx
+ *  Copyright (C) 2009  Andreas Baumann
+ *
+ *  This copyrighted material is made available to anyone wishing to use,
+ *  modify, copy, or redistribute it subject to the terms and conditions of
+ *  the GNU Lesser General Public License, as published by the Free Software
+ *  Foundation.
+ *
+ *  This program is distributed in the hope that it will be useful,
+ *  but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *  MERCHANTABILITY  or FITNESS FOR A PARTICULAR PURPOSE.
+ *  See the GNU Lesser General Public License for more details.
+ *
+ *  You should have received a copy of the GNU Lesser General Public License
+ *  along with this distribution; if not, write to:
+ *  Free Software Foundation, Inc.
+ *  51 Franklin Street, Fifth Floor
+ *  Boston, MA  02110-1301  USA
+ *
+ */
+
+#include <iostream>
+#include <cassert>
+#include "sqlite3xx/sqlite3xx"
+/* test needs 'unlink' from 'unistd.h' */
+#if !defined _WIN32
+#include <unistd.h>
+#endif /* !defined _WIN32 */
+
+using namespace sqlite3xx;
+using namespace std;
+
+int main( ) {
+	(void)unlink( "test7.db" );
+
+	try {
+		cout << "creating DB.." << endl;
+		connection c( "test7.db" );
+		c.trace( true );
+		cout << "connection object is " << c << endl;
+
+		cout << "starting transaction.." << endl;
+		work wc( c, "create" );
+
+		cout << "create table.." << endl;
+		wc.exec( "create table a( i integer, s text, d double )" );
+		wc.commit( );
+
+		work wi( c, "insert" );
+		cout << "insert some data.." << endl;
+		c.prepare( "ins", "insert into a( i, s, d ) values( $1, $2, $3 )" )
+			( "integer", prepare::treat_direct )
+			( "text", prepare::treat_direct )
+			( "double", prepare::treat_direct );
+		result r = wi.prepared( "ins" )( 5 )( "bla" )( 8.933 ).exec( );
+		cout << "inserted " << r.affected_rows( ) << " rows." << endl;
+		r = wi.prepared( "ins" )( 18 )( "bli" )( 4.7771 ).exec( );
+		cout << "inserted " << r.affected_rows( ) << " rows." << endl;
+		r = wi.prepared( "ins" )( 7 )( "blu" )( -1.11111 ).exec( );
+		cout << "inserted " << r.affected_rows( ) << " rows." << endl;
+		wi.commit( );
+
+		work wq( c, "query" );
+		cout << "querying.." << endl;
+		c.prepare( "qry", "select * from a where i>=$1 and i<=$2" )
+			( "integer", prepare::treat_direct )
+			( "integer", prepare::treat_direct );
+
+		r = wq.prepared( "qry" )( 4 )( 8 ).exec( );
+		wq.commit( );
+
+		cout << "end." << endl;
+	} catch( sql_error& e ) {
+		cerr << e.msg( ) << ": " << e.query( ) << endl;
+	}
+}
diff --git a/tests/test8.MUST b/tests/test8.MUST
new file mode 100644
index 0000000..089025e
--- /dev/null
+++ b/tests/test8.MUST
@@ -0,0 +1,28 @@
+creating DB..
+connection object is sqlite3xx::connection(test3.db)
+starting transaction..
+create table..
+insert some data..
+inserted 1 rows.
+inserted 1 rows.
+inserted 1 rows.
+querying..
+found 2 records..
+found 4 columns..
+column 0: 5
+column 1: 5
+column 2: 5
+column 3: 3
+i: 0
+(by col number) i: (NULL) d: (NULL) s: (NULL) t: bla
+(by col name) i: (NULL) d: (NULL) s: (NULL) t: bla
+(mapping) i: 0 d: 0 s:  t: bla
+i: 1
+(by col number) i: (NULL) d: (NULL) s: (NULL) t: blu
+(by col name) i: (NULL) d: (NULL) s: (NULL) t: blu
+(mapping) i: 0 d: 0 s:  t: blu
+i: 2
+(by col number) i: (NULL) d: (NULL) s: (NULL) t: bli
+(by col name) i: (NULL) d: (NULL) s: (NULL) t: bli
+(mapping) i: 0 d: 0 s:  t: bli
+end.
diff --git a/tests/test8.cpp b/tests/test8.cpp
new file mode 100644
index 0000000..427ca71
--- /dev/null
+++ b/tests/test8.cpp
@@ -0,0 +1,100 @@
+/*
+ *  sqlite3xx - sqlite3 C++ layer, following the ideas of libpqxx
+ *  Copyright (C) 2009  Andreas Baumann
+ *
+ *  This copyrighted material is made available to anyone wishing to use,
+ *  modify, copy, or redistribute it subject to the terms and conditions of
+ *  the GNU Lesser General Public License, as published by the Free Software
+ *  Foundation.
+ *
+ *  This program is distributed in the hope that it will be useful,
+ *  but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *  MERCHANTABILITY  or FITNESS FOR A PARTICULAR PURPOSE.
+ *  See the GNU Lesser General Public License for more details.
+ *
+ *  You should have received a copy of the GNU Lesser General Public License
+ *  along with this distribution; if not, write to:
+ *  Free Software Foundation, Inc.
+ *  51 Franklin Street, Fifth Floor
+ *  Boston, MA  02110-1301  USA
+ *
+ */
+
+#include <iostream>
+#include <cassert>
+#include "sqlite3xx/sqlite3xx"
+/* test needs 'unlink' from 'unistd.h' */
+#if !defined _WIN32
+#include <unistd.h>
+#endif /* !defined _WIN32 */
+
+using namespace sqlite3xx;
+using namespace std;
+
+int main( ) {
+	(void)unlink( "test3.db" );
+
+	try {
+		cout << "creating DB.." << endl;
+		connection c( "test3.db" );
+		cout << "connection object is " << c << endl;
+
+		cout << "starting transaction.." << endl;
+		work wc( c, "create" );
+
+		cout << "create table.." << endl;
+		wc.exec( "create table a( i integer, d double, s text, t text )" );
+		wc.commit( );
+
+		work wi( c, "insert" );
+		cout << "insert some data.." << endl;
+		c.prepare( "ins", "insert into a( i, d, s, t ) values( NULL, NULL, NULL, ? )" )
+			( "text", sqlite3xx::prepare::treat_direct );
+		result r = wi.prepared( "ins" )( "bla" ).exec( );
+		cout << "inserted " << r.affected_rows( ) << " rows." << endl;
+		r = wi.prepared( "ins" )( "blu" ).exec( );
+		cout << "inserted " << r.affected_rows( ) << " rows." << endl;
+		r = wi.prepared( "ins" )( "bli" ).exec( );
+		cout << "inserted " << r.affected_rows( ) << " rows." << endl;
+		wi.commit( );
+
+		work wq( c, "query" );
+		cout << "querying.." << endl;
+		c.prepare( "qry", "select * from a" );
+
+		r = wq.prepared( "qry" ).exec( );
+		cout << "found " << r.size( ) << " records.." << endl;
+		cout << "found " << r.columns( ) << " columns.." << endl;
+		for( result::size_type i = 0; i < r.columns( ); i++ ) {
+			cout << "column " << i << ": "  << r.column_type( i ) << endl;
+		}
+		assert( r.size( ) == 2 );
+
+	        for( result::size_type i = 0; i < r.size( ); i++ ) {
+			cout << "i: " << i << endl;
+
+			// by field number
+			cout << "(by col number) i: " << r[i][0] << " d: " << r[i][1] << " s: " << r[i][2] << " t: " << r[i][3] << endl;
+
+			// associative array
+			cout << "(by col name) i: " << r[i]["i"] << " d: " << r[i]["d"] << " s: " << r[i]["s"] << " t: " << r[i]["t"] << endl;
+
+			// map to variables of a given type
+			int value_i;
+			double value_d;
+			string value_s;
+			string value_t;
+			r[i]["i"].to( value_i );
+			r[i]["d"].to( value_d );
+			r[i]["s"].to( value_s );
+			r[i]["t"].to( value_t );
+			cout << "(mapping) i: " << value_i << " d: " << value_d << " s: " << value_s << " t: " << value_t << endl;
+		}
+		assert( r.size( ) == 3 );
+		wq.commit( );
+
+		cout << "end." << endl;
+	} catch( sql_error& e ) {
+		cerr << e.msg( ) << ": " << e.query( ) << endl;
+	}
+}
diff --git a/tests/testc.MUST b/tests/testc.MUST
new file mode 100644
index 0000000..0620287
--- /dev/null
+++ b/tests/testc.MUST
@@ -0,0 +1,13 @@
+thread-safe: 1
+number of rows changed: 1 (MUST: 1)
+number of rows changed: 0 (MUST: 0)
+b = 8
+s = bla
+b = 4
+s = blu
+b = 1
+s = bli
+number of bind parameters: 2 (MUST: 2)
+nof columns: 2
+b(1) = 4
+s(3) = blu
diff --git a/tests/testc.MUST.CYGWIN b/tests/testc.MUST.CYGWIN
new file mode 100644
index 0000000..7ff4182
--- /dev/null
+++ b/tests/testc.MUST.CYGWIN
@@ -0,0 +1,13 @@
+thread-safe: 0
+number of rows changed: 1 (MUST: 1)
+number of rows changed: 0 (MUST: 0)
+b = 8
+s = bla
+b = 4
+s = blu
+b = 1
+s = bli
+number of bind parameters: 2 (MUST: 2)
+nof columns: 2
+b(1) = 4
+s(3) = blu
diff --git a/tests/testc.MUST.LINUX_redhat_5 b/tests/testc.MUST.LINUX_redhat_5
new file mode 100644
index 0000000..debbe69
--- /dev/null
+++ b/tests/testc.MUST.LINUX_redhat_5
@@ -0,0 +1,13 @@
+thread-safe: no
+number of rows changed: 1 (MUST: 1)
+number of rows changed: 0 (MUST: 0)
+b = 8
+s = bla
+b = 4
+s = blu
+b = 1
+s = bli
+number of bind parameters: 2 (MUST: 2)
+nof columns: 2
+b(1) = 4
+s(3) = blu
diff --git a/tests/testc.c b/tests/testc.c
new file mode 100644
index 0000000..1bf0eef
--- /dev/null
+++ b/tests/testc.c
@@ -0,0 +1,184 @@
+/*
+ *  sqlite3xx - sqlite3 C++ layer, following the ideas of libpqxx
+ *  Copyright (C) 2009  Andreas Baumann
+ *
+ *  This copyrighted material is made available to anyone wishing to use,
+ *  modify, copy, or redistribute it subject to the terms and conditions of
+ *  the GNU Lesser General Public License, as published by the Free Software
+ *  Foundation.
+ *
+ *  This program is distributed in the hope that it will be useful,
+ *  but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *  MERCHANTABILITY  or FITNESS FOR A PARTICULAR PURPOSE.
+ *  See the GNU Lesser General Public License for more details.
+ *
+ *  You should have received a copy of the GNU Lesser General Public License
+ *  along with this distribution; if not, write to:
+ *  Free Software Foundation, Inc.
+ *  51 Franklin Street, Fifth Floor
+ *  Boston, MA  02110-1301  USA
+ *
+ */
+
+#include <stdio.h>
+#include <stdlib.h>
+#include <sqlite3.h>
+#if !defined( _WIN32 )
+#include <unistd.h>
+#endif
+
+#define UNUSED( x ) if( 0 && (x) ) { }
+
+void DirectExec( sqlite3* db, const char *sql );
+void DirectExecNoResult( sqlite3* db, const char *sql );
+
+static int callback( void *NotUsed, int argc, char **argv, char **azColName ){
+	int i;
+	UNUSED( NotUsed );
+
+	for( i = 0; i < argc; i++ ) {
+		printf( "%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL" );
+	}
+
+	return 0;
+}
+
+void DirectExec( sqlite3* db, const char *sql ) {
+	int rc;
+	char *errmsg = NULL;
+
+	rc = sqlite3_exec( db, sql, callback, 0, &errmsg );
+	if( rc != SQLITE_OK ) {
+		fprintf( stderr, "SQL error in '%s': %s\n", sql, errmsg );
+		sqlite3_free( errmsg );
+		(void)sqlite3_close( db );
+		exit( 1 );
+	}
+}
+
+void DirectExecNoResult( sqlite3* db, const char *sql ) {
+	int rc;
+	char *errmsg = NULL;
+
+	rc = sqlite3_exec( db, sql, NULL, 0, &errmsg );
+	if( rc != SQLITE_OK ) {
+		fprintf( stderr, "SQL error in '%s': %s\n", sql, errmsg );
+		sqlite3_free( errmsg );
+		(void)sqlite3_close( db );
+		exit( 1 );
+	}
+}
+
+int main( void ) {
+	sqlite3* db;
+	int rc;
+	sqlite3_stmt *stmt;
+	const char *ptr;
+	int i;
+
+	(void)unlink( "testc.db" );
+
+	rc = sqlite3_open( "testc.db", &db );
+	if( rc != SQLITE_OK ) {
+		fprintf( stderr, "Can't open database: %s\n",
+			sqlite3_errmsg( db ) );
+		(void)sqlite3_close( db );
+		exit( 1 );
+	}
+
+#if SQLITE_VERSION_NUMBER >= 3005000
+	printf( "thread-safe: %d\n", sqlite3_threadsafe( ) ); 
+#else
+	printf( "thread-safe: no\n" );
+#endif
+
+	DirectExecNoResult( db, "create table a( b integer, s text )" );
+	DirectExecNoResult( db, "insert into a(b,s) values (8,'bla')" );
+	DirectExecNoResult( db, "insert into a(b,s) values (4,'blu')" );
+	DirectExecNoResult( db, "insert into a(b,s) values (1,'bli')" );
+	printf( "number of rows changed: %d (MUST: 1)\n", sqlite3_changes( db ) );
+	DirectExecNoResult( db, "delete from a where b=47" );
+	printf( "number of rows changed: %d (MUST: 0)\n", sqlite3_changes( db ) );
+
+	/* with callbacks */
+	DirectExec( db, "select * from a" );
+
+	/* prepared statement */
+#if SQLITE_VERSION_NUMBER >= 3005000
+	rc = sqlite3_prepare_v2( db, "select * from a where b>=$1 and b<=$2",
+		-1, &stmt, &ptr );
+#else
+	rc = sqlite3_prepare( db, "select * from a where b>=$1 and b<=$2",
+		-1, &stmt, &ptr );
+#endif
+	if( rc != SQLITE_OK ) {
+		fprintf( stderr, "Unable to prepare statement: %s\n", sqlite3_errmsg( db ) );
+		(void)sqlite3_close( db );
+		exit( 1 );
+	}
+	rc = sqlite3_bind_int( stmt, 1, 3 );
+	if( rc != SQLITE_OK ) {
+		fprintf( stderr, "Unable to bind first parameter: %s\n", sqlite3_errmsg( db ) );
+		(void)sqlite3_finalize( stmt );
+		(void)sqlite3_close( db );
+		exit( 1 );
+	}
+	rc = sqlite3_bind_int( stmt, 2, 4 );
+	if( rc != SQLITE_OK ) {
+		fprintf( stderr, "Unable to bind second parameter: %s\n", sqlite3_errmsg( db ) );
+		(void)sqlite3_finalize( stmt );
+		(void)sqlite3_close( db );
+		exit( 1 );
+	}
+	printf( "number of bind parameters: %d (MUST: 2)\n", sqlite3_bind_parameter_count( stmt ) );
+
+	rc = sqlite3_step( stmt );
+	while( rc == SQLITE_ROW ) {
+		switch( rc ) {
+			case SQLITE_ROW:
+				printf( "nof columns: %d\n", sqlite3_data_count( stmt ) );
+				for( i = 0; i < sqlite3_data_count( stmt ); i++ ) {
+					const char *colname = sqlite3_column_name( stmt, i );
+					int data_type = sqlite3_column_type( stmt, i );
+					switch( data_type ) {
+						case SQLITE_INTEGER: {
+							int value = sqlite3_column_int( stmt, i );
+							printf( "%s(%d) = %d\n", colname, data_type, value );
+						}
+						break;
+
+						case SQLITE_TEXT: {
+							const unsigned char *value = sqlite3_column_text( stmt, i );
+							printf( "%s(%d) = %s\n", colname, data_type, value );
+						}
+						break;
+					}
+				}
+				break;
+	
+			default:
+				fprintf( stderr, "Unable to step through statement: %s\n", sqlite3_errmsg( db ) );
+				(void)sqlite3_finalize( stmt );
+				(void)sqlite3_close( db );
+				exit( 1 );
+		}
+		rc = sqlite3_step( stmt );
+	}
+
+	/* finalize prepared statement */
+	rc = sqlite3_finalize( stmt );
+	if( rc != SQLITE_OK ) {
+		fprintf( stderr, "Unable to finalize statement: %s\n", sqlite3_errmsg( db ) );
+		(void)sqlite3_close( db );
+		exit( 1 );
+	}
+
+	rc = sqlite3_close( db );
+	if( rc != SQLITE_OK ) {
+		fprintf( stderr, "Can't close database: %s\n",
+			sqlite3_errmsg( db ) );
+		exit( 1 );
+	}
+
+	exit( 0 );
+}