function main()
{
global $G;
message('%s version %s', TITLE, VERSION);
message('bwSQLite3 version %s', bwSQLite3::VERSION);
message('file name: %s, table name: %s', DB_FILENAME, TABLE_NAME);
$query_start_time = microtime(TRUE);
// create database here
try {
message('creating the db object');
$db = new bwSQLite3(DB_FILENAME, TABLE_NAME);
$tn = TABLE_NAME;
message('creating the table');
$db->sql_do("drop table if exists {$tn}");
$db->sql_do("create table {$tn} ( id integer primary key, animal text, sound text )");
// insert some records
$db->sql_do("insert into {$tn} (animal, sound) values (?, ?)", 'cat', 'Purr');
$db->sql_do("insert into {$tn} (animal, sound) values (?, ?)", 'dog', 'Woof');
$db->sql_do("insert into {$tn} (animal, sound) values (?, ?)", 'duck', 'Quack');
$db->sql_do("insert into {$tn} (animal, sound) values (?, ?)", 'bear', 'Grrr');
message('there are %d rows in the table', $db->count_recs());
} catch (PDOException $e) {
error($e->getMessage());
}
$elapsed_time = microtime(TRUE) - $query_start_time;
message('elapsed time: %s ms', number_format($elapsed_time * 1000, 2));
}
function _init()
{
global $G;
$G['TITLE'] = TITLE;
$G['ME'] = basename($_SERVER['SCRIPT_FILENAME']);
// initialize display vars
foreach (array('MESSAGES', 'ERRORS', 'CONTENT') as $v) {
$G[$v] = "";
}
// create the table
try {
$db = new bwSQLite3(DB_FILENAME, TABLE_NAME);
$tn = TABLE_NAME;
$db->sql_do("drop table if exists {$tn}");
$db->sql_do("create table {$tn} ( id integer primary key, animal text, sound text )");
// insert some rows
$db->sql_do("insert into {$tn} (animal, sound) values (?, ?)", 'cat', 'Purr');
$db->sql_do("insert into {$tn} (animal, sound) values (?, ?)", 'dog', 'Woof');
$db->sql_do("insert into {$tn} (animal, sound) values (?, ?)", 'duck', 'Quack');
$db->sql_do("insert into {$tn} (animal, sound) values (?, ?)", 'bear', 'Grrr');
} catch (PDOException $e) {
error($e->getMessage());
}
$G['db'] = $db;
}
function main()
{
global $G;
define('DATABASE', '/Users/bweinman/sqlite3_data/test.db');
define('FILENAME', 'olives.jpg');
$fh = fopen(FILENAME, 'rb');
$blob = fread($fh, filesize(FILENAME));
try {
$db = new bwSQLite3(DATABASE);
$db->sql_do('DROP TABLE IF EXISTS blobtest');
$db->sql_do('CREATE TABLE blobtest (id INTEGER PRIMARY KEY, b BLOB)');
$db->sql_do('INSERT INTO blobtest (b) VALUES (CAST(? AS BLOB))', $blob);
$result = $db->sql_query_value('SELECT b FROM blobtest');
} catch (PDOException $e) {
error($e->getMessage());
}
header('Content-type: image/jpeg');
echo $result;
}
function main()
{
global $G;
$G['query_start_time'] = microtime(TRUE);
$tn = TABLE;
message('this is %s, filename is %s, table is %s', $G['ME'], FILENAME, TABLE);
try {
$db = new bwSQLite3(FILENAME, TABLE);
message("bwSQLite3 version %s", $db->version());
message("sqlite3 version %s", $db->version('sqlite3'));
$db->sql_do("drop table if exists {$tn}");
$db->begin_transaction();
$db->sql_do("create table {$tn} ( id integer primary key, string text, number int )");
$db->sql_do("insert into {$tn} (string, number) values (?, ?)", 'one', 1);
$db->sql_do("insert into {$tn} (string, number) values (?, ?)", 'two', 2);
$db->sql_do("insert into {$tn} (string, number) values (?, ?)", 'three', 3);
$db->commit();
} catch (PDOException $e) {
error($e->getMessage());
}
$db->timer_start();
message('table_exists %s: %s (%s ms elapsed)', TABLE, $db->table_exists(TABLE) ? 'YES' : 'NO', $db->timer());
$db->timer_start();
message('table_exists %s: %s (%s ms elapsed)', 'foo', $db->table_exists('foo') ? 'YES' : 'NO', $db->timer());
message('all rows:');
$db->timer_start();
try {
foreach ($db->sql_query("select * from {$tn}") as $row) {
message('id: %d, string: %s, number: %d', $row['id'], $row['string'], $row['number']);
}
message(' -- %s ms elapsed', $db->timer());
} catch (PDOException $e) {
error($e->getMessage());
}
try {
$row = $db->sql_query_row("select * from {$tn} where number = ?", 2);
message("query_row (select * from %s where number = 2): string: %s number: %s", TABLE, $row['string'], $row['number']);
$value = $db->sql_query_value("select id from {$tn} where number = ?", 3);
message("query_value (select id from %s where number = 3): id: %s", TABLE, $value);
} catch (PDOException $e) {
error($e->getMessage());
}
try {
$row = $db->get_rec(2);
message('get_rec 2 -- id: %d, string: %s, number: %d', $row['id'], $row['string'], $row['number']);
} catch (PDOException $e) {
error($e->getMessage());
}
$db->timer_start();
message('get_recs() ...');
try {
foreach ($db->get_recs() as $row) {
message('id: %d, string: %s, number: %d', $row['id'], $row['string'], $row['number']);
}
message(' -- %s ms elapsed', $db->timer());
} catch (PDOException $e) {
error($e->getMessage());
}
message('count_recs: ' . $db->count_recs());
try {
$new_id = $db->insert(array('number' => 4, 'string' => 'four'));
message("inserted new id " . $new_id);
} catch (PDOException $e) {
error($e->getMessage());
}
message('after insert count_recs: ' . $db->count_recs());
try {
$db->get_recs();
} catch (PDOException $e) {
error($e->getMessage());
}
foreach ($db->sth() as $row) {
message('id: %d, string: %s, number: %d', $row['id'], $row['string'], $row['number']);
}
message('update rec 2: ');
try {
$db->update(2, array('number' => 7, 'string' => 'seven'));
} catch (PDOException $e) {
error($e->getMessage());
}
$row = $db->get_rec(2);
message('get_rec 2 -- id: %d, string: %s, number: %d', $row['id'], $row['string'], $row['number']);
message('delete rec 2: ');
try {
$row = $db->delete(2);
} catch (PDOException $e) {
error($e->getMessage());
}
message('after delete count_recs: ' . $db->count_recs());
foreach ($db->get_recs() as $row) {
message('id: %d, string: %s, number: %d', $row['id'], $row['string'], $row['number']);
}
$elapsed_time = microtime(TRUE) - $G['query_start_time'];
message('whole set elapsed time: %s ms', number_format($elapsed_time * 1000, 2));
}
