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

Hi teamonkey,

any chance to compile your sqlite mod with BMax 1.20.
As far as I remember I was not able to compile with 1.18. So I think it won't compile on 1.20 either.

Thanks in advance.

No it won't work ... it uses Ptr to variables which is deprecated since 1.18

Rene, I've re-compiled teamonkey's module to 1.18. You can get it here. Seems to work OK with 1.20

I've also downloaded and recompiled the new sqlite version 3.3.5. You can download it here

Not that you need it but you can check out my sqlite3 tutorial http://www.blitzmax.com/Community/posts.php?topic=59000

@Dreamora, I think Mark has re-fixed the ptr problem