SQLite3 plugin: sqlite3 bindings for SpiderApe


The SQLite3 plugin provides two completely different wrappers around the sqlite3 embedded database API. One API is object-oriented and makes heavy use of exceptions to report errors. The other API is nearly a 1-to-1 mapping of much of the sqlite3 C API. The C-style API currently wraps approximately 80 sqlite3 functions (including a handful of non-standard utility functions). The OO- and non-OO APIs can be used together, so when the OO layer is missing a feature you need, you can normally use the appropriate low-level sqlite3_xxx() call.

These APIs are documented fairly well in the plugins manual. They do not cover every single possible usage of sqlite3, but do cover the most important capabilities. Implementing custom collating sequences in JS code is not supported, but there is support for creating custom SQL-bound functions in JS. (You can of course add collators from native code if you really need to.) See the manual for more details.

Here's a JS script which kind of randomly shows various features of the C-like wrapper:
dll_path( '.', dll_path() );
assert( open_dll('SQLite3') );
//assert( open_dll('/home/stephan/cvs/ApePlugin/SQLite3.so') );

print("Using sqlite3 version",sqlite3_libversion(),'???');
print( sqlite3_convert_macro(SQLITE_DONE), '==',sqlite3_convert_macro('SQLITE_DONE') );
print( 'SQLITE_TRANSIENT ==',SQLITE_TRANSIENT);
print( 'SQLITE_STATIC ==',SQLITE_STATIC);
print( 'SQLITE_FLOAT ==',SQLITE_FLOAT );
var dbf = 'c3qs.db';
dbf = ':memory:'; 
var dbh = sqlite3_open(dbf);
print( "dbh ==", dbh );
assert( dbh );

assert( SQLITE_OK == sqlite3_extended_result_codes(dbh,true) );
print( "autocommit on? ==", sqlite3_get_autocommit(dbh) );
sqlite3_exec(dbh,"BEGIN");
print( "autocommit on in transaction? ==", sqlite3_get_autocommit(dbh) );
sqlite3_exec(dbh,"END");
var rc = sqlite3_exec(dbh,"drop table if exists foo");
rc = sqlite3_exec(dbh,"create table if not exists foo(a)");
print("exec create ==",sqlite3_convert_macro(rc));
rc = sqlite3_exec(dbh,"insert into foo (a) values('hi')");
print("exec insert ==",sqlite3_convert_macro(rc));
assert( "not an error" == sqlite3_errmsg(dbh) );

if( 1 ) {
	
	function my_sql_callback(data,row,cols) {
		if( ! is_void(data) ) {
			if( data.enabled )
			{
				print("my_sql_callback message:",data.msg);
				data.enabled = false;
			}
			print('row #',++data.count);
		}
		var buf = '';
		for( var i = 0; i < row.length; ++i ) {
			buf += cols[i]+'='+row[i]+'\t';
		}
		print( buf );
		return 0;
	}
	rc = sqlite3_exec( dbh, "select rowid,* from foo limit 5",
			my_sql_callback,
			{ msg:"hi, world",enabled:true,count:0 } );
	print ("exec with callback ==",sqlite3_convert_macro(rc ? rc : 0) );
	assert( SQLITE_OK == rc );
}


var sth = sqlite3_prepare( dbh, "insert into foo (a) values(?)" );

print('sth ==',sth,', dbh ==',dbh,' errmsg:',sqlite3_errmsg(dbh));
assert( sth && (dbh == sqlite3_db_handle( sth )) );

// This is one way of inserting lines one at a time:
rc = sqlite3_bind( sth, 1, 7 );
print( 'bind int rc ==',rc );
rc = sqlite3_step( sth );
assert( SQLITE_OK == sqlite3_reset( sth ) );
print('last_insert_rowid ==',sqlite3_last_insert_rowid(dbh));

rc = sqlite3_bind_text( sth, 1, 'hi, world' );
assert( SQLITE_DONE == sqlite3_step( sth ) );
assert( SQLITE_OK == sqlite3_reset( sth ) );
print('last_insert_rowid ==',sqlite3_last_insert_rowid(dbh));

rc = sqlite3_bind( sth, 1, 42.24 );
assert( SQLITE_DONE == sqlite3_step( sth ) );
assert( SQLITE_OK == sqlite3_reset( sth ) );

rc = sqlite3_bind_int64( sth, 1, 0xeffffffff /* base10==64424509439 */);
assert( SQLITE_DONE == sqlite3_step( sth ) );
assert( SQLITE_OK == sqlite3_reset( sth ) );


assert( SQLITE_OK == sqlite3_finalize( sth ) );


print( "Insertions done. Stepping through result set using sqlite3_step()...");
sth = sqlite3_prepare( dbh, "select rowid,a from foo limit 10" );
assert( sth );
var colnames = new Array();
var colcount = sqlite3_column_count(sth);
print('sth ==',sth, 'colcount ==',colcount);
for( var i = 0; i < colcount; ++i ) {
	colnames[i] = sqlite3_column_name( sth, i );
}
print('sth ==',sth, 'colcount ==',colcount);
var atrow = 0;
var tab = '|';
while( SQLITE_ROW == sqlite3_step( sth ) )
{
	var id = sqlite3_column_int64( sth, 0 );
	var val = sqlite3_column_text( sth, 1 );
	print( 'row', atrow++,":\t",
	       colnames[0]+"=="+id+tab+colnames[1]+"=="+val );
}
sqlite3_finalize( sth );

print( 'multi-statement exec():', sqlite3_exec( dbh,
						"insert into foo values('FIRST');"+
						"insert into foo values('SECOND');"+
						"insert into foo values('THIRD');"
						)
       );

print( 'sqlite3_total_changes() ==',sqlite3_total_changes(dbh));

{
	sth = sqlite3_prepare( dbh, 'pragma encoding' ); assert(sth);
	rc = sqlite3_step( sth );
	print( 'pragma_encoding step() ==',rc );
	sqlite3_finalize(sth);
}




if( 1 ) {
	// Try adding new SQL functions with sqlite3_create_function()

	function my_sql_func(cx,userdata,values) {
		var argc = values ? values.length : 0;
 		if( userdata ) ++userdata.count;
		if( 0 ) {
			print( values.join('|') );
		} else {
			var buf = '';
			var tab='|';
			for( var i = 0; i < argc; ++i )
			{
				buf += sqlite3_value_text(values[i])+tab;
			}
			print( buf );
		}
		sqlite3_result_null( cx );
		return 0;
	}


	function my_sql_step_func(cx,userdata,values)
	{
		//print('my_sql_step_func() value count==',values.length);
		userdata.counter += values.length;
		//print( 'step_func',userdata.counter += values.length );
		//sqlite3_result_int( cx, userdata.counter );
	}

	function my_sql_final_func(cx,userdata)
	{
		userdata.total = userdata.counter;
		userdata.counter = 0;
		//print('my_sql_final_func() userdata.total ==',userdata.total);
		sqlite3_result_int( cx, userdata.total );
	}

	print('**************************************** sqlite3_create_function() tests...');
	rc = sqlite3_create_function(
		      dbh, // db handle
		      'eek', // function name
		      -1, // number of args it takes, or -1 for "any number"
		      SQLITE_ANY, // encoding preference for argument strings
		      {count:0,msg:"hi."}, // arbitrary data, passed on as-is to your func
		      my_sql_func // function reference
		      );
	if( SQLITE_OK != rc )
	{
		print('db error '+rc+':',sqlite3_errmsg(dbh));
		assert(0);
	}
	rc = sqlite3_create_function(
				     dbh, // db handle
				     'ah', // function name
				     -1, // number of args it takes, or -1 for "any number"
				     SQLITE_ANY, // encoding preference for argument strings
				     {counter:0,total:0}, // arbitrary data, passed on as-is to your func
				     null, // base func
				     my_sql_step_func, // step function
				     my_sql_final_func // finalize function
				     );
	if( SQLITE_OK != rc )
	{
		print('db error '+rc+':',sqlite3_errmsg(dbh));
		assert(0);
	}
	sth = sqlite3_prepare( dbh,
	    "select eek(),eek(11,13,17),eek('one arg'),eek(current_timestamp,7),ah(1,2,3,4)" );
	assert( sth );
	for( var i = 0; i < 2; ++i ) {
		sqlite3_dump_statement( sth );
		sqlite3_reset( sth );
	}
   	sqlite3_dump_statement( sth );
  	sqlite3_finalize( sth );
	sth = sqlite3_prepare( dbh, 'select ah(foo.a,foo.a) from foo' );
	if( ! sth ) { print(sqlite3_errmsg(dbh)); assert( 0 ); }
	rc = sqlite3_step( sth );
	assert( SQLITE_ROW == rc );
	print( "ah() returned", sqlite3_column_int(sth,0) );
	print('**************************************** end sqlite3_create_function() tests');
	sqlite3_finalize( sth );
}


if( 1 ) {
	// Do a more elaborate set of functions, where one function is used to configure another...
	print('**************************************** start SQL print() test...');
	/** SQL print() function. */
	function sql_print_func(cx,userdata,values) {
		if( ! userdata.enabled )
		{
			//sqlite3_result(cx,null);
			return;
		}
		//print('print().verbose ==',userdata.verbose);
		//print('print(): argc==',values.length,' is_array values ==',is_array(values));
		if( values )
		{
			for( var i = 0; i < values.length; ++i )
			{
				values[i] = sqlite3_value_text(values[i]);
			}
			var xx = userdata.prefix+values.join(userdata.separator)+userdata.suffix;
			sqlite3_result_text(cx,xx);
			print(xx);
		}
		return SQLITE_OK;
	}
	/**
	   SQL func print_ctl() sets some config settings for the print() func.
	*/
	function sql_print_ctl_func(cx,userdata,values) {
		//print( 'print_ctl() cx ==',cx);
		if( (! values) || (!values.length) )
		{
			//throw new Error("print_ctl() requires arguments!");
			return SQLITE_MISUSE;
		}
		var msgpre = 'print_ctl():';
 		if( 0 )
		for( var i = 0; i < values.length; ++i )
 		{
			var x = sqlite3_value_deduce(values[i]);
			print(msgpre, 'typeof sqlite3_value_deduce[ndx='+i
				      +':valh='+values[i]+'] ==',typeof x,'==',x );
 		}
		var argpos = 0;
		var cmd = sqlite3_value_text( values[argpos++] );
		if( 'on' == cmd )
		{
 			userdata.enabled = true;
  			sqlite3_result_deduce( cx, 1, SQLITE_INTEGER );
 			if( userdata.verbose ) print(msgpre, 'print() enabled' );
		}
		else if( 'off' == cmd )
		{
			userdata.enabled = false;
			sqlite3_result_deduce( cx, 0 );
			if( userdata.verbose ) print(msgpre, 'print() disabled' );
		}
		else if( 'toggle' == cmd )
		{
			userdata.enabled = !userdata.enabled;
			sqlite3_result_deduce( cx, userdata.enabled ? 1 : 0 );
		}
		else if( 'separator' == cmd )
		{
			if( values.length <= argpos )
			{
				sqlite3_result_deduce( cx, userdata.separator );
				print(msgpre, "separator = ["+userdata.separator+"]" );
			}
			else
			{
				userdata.separator = sqlite3_value_text( values[argpos++] );
				sqlite3_result_deduce( cx, userdata.separator );
				if( userdata.verbose ) {
					print( msgpre,
					       'Setting print separator to ['
						+userdata.separator+']');
				}
			}
		}
		else if( 'verbose' == cmd )
		{
			if( values.length <= argpos )
			{
				userdata.verbose = true;
			}
			else
			{
				userdata.verbose = (sqlite3_value_int(values[argpos++])
						 ? true : false);
			}
			sqlite3_result_int( cx, userdata.verbose ? 1 : 0 );
			if( userdata.verbose ) {
			    print(msgpre, "verbose = ["
				    +userdata.verbose+"]" );
			}
		}
		else
		{
			print(msgpre,'print_ctl() unknown command "'+cmd+'".');
			return SQLITE_ERROR;
		}
		return SQLITE_OK;
	}
	var print_func_data = {separator:' ',
			       enabled:true,
			       verbose:false,
			       prefix:'', // prepended to print() output
			       suffix:'' // appended to print() output
	};
	rc = sqlite3_create_function(
				     dbh,
				     'print',
				     -1,
				     SQLITE_ANY,
				     print_func_data,
				     sql_print_func
				     );
	assert( SQLITE_OK == rc );
	rc = sqlite3_create_function(
				     dbh,
				     'print_ctl',
				     -1,
				     SQLITE_ANY,
				     print_func_data,
				     sql_print_ctl_func
				     );
	if( 1 ) {
		var ex = function(sql) {
		    //print( "ex() ",sql );
		    if( SQLITE_OK != sqlite3_exec( dbh, sql )) {

		    }
		};
		ex( "select print_ctl('verbose',1)" );
		ex( "select print_ctl('on');" );
		ex( "select print_ctl('separator','\t|\t') as SEPARATOR");
		ex( "select print('foo.a ==',a) as 'FOO.A' from foo limit 5" );
		ex( "select print('you should','see this')" );
		ex( "select print_ctl('toggle');" );
		ex( "select print('you should not','see this')" );
		ex( "select print('you should not','see this')" );
		ex( "select print('you should not','see this')" );
		ex( "select print_ctl('toggle')" );
		ex( "select print('you should','see this')" );
		ex( "select print_ctl('toggle')" );
		ex( "select print('you should not','see this')" );
		ex( "select print('you should not','see this')" );
		ex( "select print('you should not','see this')" );
		ex( "select print_ctl('toggle')" );
		ex( "select print('you should','see this')" );
	}
	print('**************************************** end SQL print() test');
}


rc = sqlite3_close(dbh);
print( "db close rc ==", sqlite3_convert_macro(rc) );

print( "Test done!" );