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!" );
|