SQLite module 0.1a

Hi

It's not finished yet, but I promised myself it would be out the door this weekend.

http://www.tinyminions.co.uk/teamonkey/files/sqlite_max.zip

Yes, folks, it's a proper embedded SQL database in BlitzMax. I chose SQLite over the others for the following reasons:
* It doesn't require an external database daemon to be installed, configured and running in the background (so scratch MySQL and PostgreSQL)
* It's fast and small (as opposed to embedded MySQL, which isn't)
* It supports tables on disk
* It supports tables entirely in memory (read this: http://www.filipdewaard.com/archives/21_SQLite_inmemory_databases.html)
* It supports prepared/compiled statements
* It's threaded so it can do its I/O stuff in the background
* It's easy to integrate and comes with full source
* It's got a very permitting licence
* It's very easy to use

To install, extract the zip in your mods directory. If you've got Linux or a Mac you'll need to use makemods.

Here's an example on how to use it:

Strict

Import tm.sqlite

' Callback function for the query
Function callback:Int(user_data:Byte Ptr, num_cols:Int, value_name:Byte Ptr Ptr, column_name:Byte Ptr Ptr)
	
	' User data - in this case it's zero if the header hasn't been passed, 1 if it has
	Local iptr:Int Ptr = Int Ptr(user_data)
	
	If(iptr[0]=0)	' Print the header
		Local h$
		
		For Local i=0 Until num_cols
			h$ :+ String.FromCString(column_name[i])+"~t~t|~t"
		Next
		
		Print ("~n"+h$+"~n")
		iptr[0] = 1
	EndIf
		
	Local h$
	For Local i=0 Until num_cols
		
		h$ :+ String.FromCString(value_name[i])+"~t|~t~t"
	Next
	Print(h$)
	Return 0
EndFunction


' Start here

Local db:Int				' Database handle
Local rc:Int				' Return code
Local errmsg:Byte Ptr		' Error message for sqlite3_exec
Local my_data:Int = 0		' User data (an integer) for callback function in sqlite3_exec


' Open the database
Print("~nOpening database")
rc = sqlite3_open("test.db", db)
If Not rc=SQLITE_OK
    Print("Error: "+String(sqlite3_errmsg(db)))
    sqlite3_close(db)
    End
EndIf

' Execute one or more SQL queries
'
' sqlite3_exec(database_handle:Int,
'              sql_query:String,
'              callback_function(user_data:Byte Ptr,
'                                num_columns:Int,
'                                value_name:Byte Ptr Ptr,
'                                column_name:Byte Ptr Ptr),
'              user_data_for_callback:Byte Ptr,
'              error_message:Byte Ptr Ptr)
'
' The last 3 parameters can be null. If used, callback_function is called for
' each row returned.

' Delete the table 'test_table' if it already exists, then create it anew
rc = sqlite3_exec(db, "DROP TABLE test_table; CREATE TABLE test_table(name TEXT, value INTEGER)", Null, Null, Null)
If Not rc=SQLITE_OK
	Print("SQL error: "+rc)
	Print("Error: "+String(sqlite3_errmsg(db)))
EndIf


' Insert a value into the database
Print("~nInserting a value")
rc = sqlite3_exec(db, "INSERT INTO test_table VALUES('my_name', 23)", Null, Null, Null)
If Not rc=SQLITE_OK
	Print("SQL error: "+rc)
	Print("Error: "+String(sqlite3_errmsg(db)))
EndIf

' Execute a bad query to demonstrate how to use the last parameter
Print("~nInserting a bad value")
rc = sqlite3_exec(db, "INSERT INTO not_a_table VALUES('my_name', 23)", Null, Null, Varptr(errmsg))
If Not rc=SQLITE_OK
	Print("errmsg: "+String.FromCString(errmsg))
	sqlite3_free(errmsg)	' Free up the memory written taken up by errmsg
EndIf



' Prepare/execute a compiled statement - this is the daddy
'
' SQL query strings have to be parsed and turned into something the database can actually
' use. This is slow. Preparing a statement lets you do the parsing once and 
'
' sqlite4_prepare(database_handle:Int,
'                 sql_query$z,
'                 query_size:Int,
'                 query_handle:Int,
'                 remainder_pointer:Byte Ptr Ptr)
'
'  - sql_query is an array of Bytes, use toCString() to convert from a Max String. It only
'    lets you prepare one SQL statement at a time. If there's more than one statement in
'    sql_query it will return a pointer to the start of the next statement in remainder so
'    you can loop over it until the whole string is performed.
'
'  - query_size is the size of sql_query in BYTES (not characters) or autodetect if <0
'
'  - remainder points to an unused portion of the input string
'
Print("~nPerforming prepared queries")

Local prepared_query:Int
Local remainder:Byte Ptr
Local query_string:Byte Ptr = "INSERT INTO test_table VALUES(?, ?); INSERT INTO test_table VALUES('prepared2', 128);".toCString()

rc = sqlite3_prepare(db, query_string, -1, prepared_query, Varptr(remainder))

Print("Query string: "+String.FromCString(query_string))
Print("Unused portion of query string: "+String.FromCString(remainder))

If Not rc=SQLITE_OK
	Print("SQL error: "+rc)
	Print("Error: "+String(sqlite3_errmsg(db)))
Else
	' Do the prepared statement 3 times with different values replacing the ?'s

	' sqlite3_bind_text(prepared_query:Int,
	'                  index:Int,
	'                  string:CString,
	'                  string_length:Int,
	'                  destructor(ptr_to_string:Byte Ptr))	
	'
	' - the destructor is called when SQLite is finished with the string or you can set it to:
	'   SQLITE_TRANSIENT = SQLite makes its own copy of it - if in doubt, use this
	'   SQLITE_STATIC    = The string is static and will not be changed or deleted while it's needed
	
	sqlite3_bind_text(prepared_query, 1, "prepared", -1, SQLITE_TRANSIENT)
	
	' sqlite3_bind_int(prepared_query:Int,
	'                  index:Int,
	'                  value:Int)
	sqlite3_bind_int(prepared_query, 2, 120)		' Replace the 2nd ? with '120'

	rc = sqlite3_step(prepared_query)				' Perform the query
	
	sqlite3_reset(prepared_query)					' Reset the statement
	sqlite3_bind_text(prepared_query, 1, "prepared2", -1, SQLITE_TRANSIENT)
	rc = sqlite3_step(prepared_query)				' Do it again
	
	sqlite3_reset(prepared_query)					' Reset the statement
	sqlite3_bind_int(prepared_query, 2, 256)		' Replace the 2nd ? with '256'
	rc = sqlite3_step(prepared_query)	

	sqlite3_finalize(prepared_query)	' Close and delete the prepared statement
EndIf

Print("~nQuerying the database~n")

' Query the database with a callback function
rc = sqlite3_exec(db, "SELECT * FROM test_table", callback, Varptr(my_data), Null)
If Not rc=SQLITE_OK
	Print("SQL error: "+rc)
	Print("Error: "+String(sqlite3_errmsg(db)))
EndIf

' Close the database and print the return code
Print "~nClosing: "+sqlite3_close(db)

Be warned that a database query is pretty slow. Prepared statements are a big performance boost, as are memory tables, but they're still "slow" as far as a realtime game is concerned so try and keep your main loop queries to a minimum.

Be also warned that this is Alpha-level code. Any or all functions may not work for you and I haven't had time to test all the functions. If you find something that doesn't work, let me know at teamonkeyATteamonkeyDOTnet.

Enjoy :)