from test/more import *;
requires_capability( "fs" );
requires_capability( "db" );
from std/db import DB;
from std/io import Path;
let dbh := DB.temp();
dbh.prepare( "create table users (id integer, name text)" ).execute();
let insert := dbh.prepare( "insert into users (id, name) values (?, ?)" );
insert.execute( 1, "Ada" );
insert.execute( 2, "Bob" );
insert.execute( 3, "Cam" );
let q1 := dbh.prepare( "select id, name from users order by id" );
q1.execute();
let first_array := q1.next_array();
is( first_array[0], 1, "next_array id" );
is( first_array[1], "Ada", "next_array value" );
let second_dict := q1.next_dict();
is( second_dict{id}, 2, "next_dict id" );
is( second_dict{name}, "Bob", "next_dict value" );
let rest := q1.all_dict();
is( rest.length(), 1, "all_dict remaining rows" );
is( rest[0]{name}, "Cam", "all_dict value" );
let q2 := dbh.prepare( "select id, name from users order by id" );
q2.execute();
let rows := q2.all_array();
is( rows.length(), 3, "all_array row count" );
is( rows[2][1], "Cam", "all_array nested values" );
let q3 := dbh.prepare( "select name from users where id = ?" );
q3.execute(2);
is( q3.next_array()[0], "Bob", "execute bind values" );
let quoted := dbh.quote("O'Brien");
like( quoted, /O''Brien/, "quote escapes apostrophes" );
let file := Path.tempfile();
let disk_dbh := DB.open(file);
disk_dbh.prepare( "create table notes (body text)" ).execute();
disk_dbh.prepare( "insert into notes (body) values ('persisted')" ).execute();
let reopened := DB.open( file.to_String() );
let note_query := reopened.prepare( "select body from notes" );
note_query.execute();
is( note_query.next_array()[0], "persisted", "open works for string path" );
let dsn_dbh := DB.connect( "dbi:SQLite:dbname=:memory:" );
dsn_dbh.prepare( "create table t (value text)" ).execute();
dsn_dbh.prepare( "insert into t (value) values ('ok')" ).execute();
let dsn_q := dsn_dbh.prepare( "select value from t" );
dsn_q.execute();
is( dsn_q.next_dict(){value}, "ok", "connect works with dsn" );
let syntax_error := exception( function() {
dbh.prepare("select from").execute();
}
);
ok( syntax_error ≢ false, "sql syntax error throws exception" );
like( syntax_error, /sql|syntax|near/i, "syntax error has db message", );
let tx_db := DB.temp( { auto_commit: true } );
tx_db.prepare( "create table tx (id integer, label text)" ).execute();
let tx_begin_result := tx_db.begin();
ok( tx_begin_result ≢ null, "begin returns database handle for chaining" );
tx_db.prepare( "insert into tx (id, label) values (?, ?)" ).execute( 1, "rolled" );
let tx_rollback_result := tx_db.rollback();
ok( tx_rollback_result ≢ null, "rollback returns database handle for chaining" );
let tx_check1 := tx_db.prepare( "select count(*) from tx" );
tx_check1.execute();
is( tx_check1.next_array()[0], 0, "rollback removes pending row" );
tx_db.begin();
tx_db.prepare( "insert into tx (id, label) values (?, ?)" ).execute( 2, "kept" );
let tx_commit_result := tx_db.commit();
ok( tx_commit_result ≢ null, "commit returns database handle for chaining" );
let tx_check2 := tx_db.prepare( "select count(*) from tx" );
tx_check2.execute();
is( tx_check2.next_array()[0], 1, "commit persists row" );
let batch_db := DB.temp();
batch_db.prepare( "create table batch_items (id integer, amount real, ok boolean)" ).execute();
batch_db.execute_batch( "insert into batch_items (id, amount, ok) values (?, ?, ?)", [ [ 1, "1.50", 1 ],
[ 2, "2.25", 0 ], [ 3, "7.00", 1 ] ], );
let batch_stmt := batch_db.prepare( "insert into batch_items (id, amount, ok) values (?, ?, ?)" );
let batch_stmt_result := batch_stmt.execute_batch( [ [ 4, "9.75", 0 ], [ 5, "10.10", 1 ] ] );
ok( batch_stmt_result ≢ null, "statement execute_batch returns statement handle" );
let meta_q := batch_db.prepare( "select id, amount, ok from batch_items order by id" );
meta_q.execute();
let names := meta_q.column_names();
is( names.length(), 3, "column_names returns each name" );
is( names[0], "id", "first column name" );
let types := meta_q.column_types();
is( types.length(), 3, "column_types returns each type" );
ok( types[0]{code} ≢ null, "column type code is present" );
let typed_first := meta_q.next_typed_dict();
is( typed_first{id}, 1, "typed dict keeps integer" );
is( typed_first{amount}, 1.5, "typed dict coerces float" );
is( typed_first{ok}, 1, "typed dict returns boolean-ish integer" );
let typed_rest := meta_q.all_typed_array();
is( typed_rest.length(), 4, "typed array returns remaining rows" );
is( typed_rest[0][1], 2.25, "typed array coerces float values" );
is( typed_rest[0][2], 0, "typed array returns boolean-ish integer" );
let iter_q := batch_db.prepare( "select id, amount, ok from batch_items order by id" );
iter_q.execute();
let iter_rows := [];
for ( let row in iter_q ) {
iter_rows.push( row );
}
is( iter_rows.length(), 5, "statement for-loop iterates every row" );
is( typeof iter_rows[0], "Dict", "statement iterator yields dict rows" );
is( iter_rows[0]{id}, 1, "statement iterator typed dict integer" );
is( iter_rows[0]{amount}, 1.5, "statement iterator typed dict float" );
is( iter_rows[0]{ok}, 1, "statement iterator typed dict bool-ish" );
let iso_db := DB.temp( { auto_commit: true, isolation_level: "immediate" } );
iso_db.prepare( "create table iso (v integer)" ).execute();
iso_db.begin();
iso_db.prepare( "insert into iso (v) values (9)" ).execute();
iso_db.commit();
let iso_q := iso_db.prepare( "select v from iso" );
iso_q.execute();
is( iso_q.next_typed_array()[0], 9, "isolation setting begin works" );
let unsupported_dsn_error := exception( function() {
DB.connect( "dbi:Oracle:phase3" );
}
);
ok( unsupported_dsn_error ≢ false, "unsupported DSN policy raises deterministic error" );
like( unsupported_dsn_error, /connect failed/i, "unsupported DSN error message is stable", );
done_testing();