標準ライブラリとして用意されているSQLite3クラスを継承したphp用ラッパークラスの作成と簡単な確認テスト。
<!--?php
include_once( "../lib/class.file.php" );
class SqLiteEx extends SQLite3
{
public $dbfile;
public $flags = SQLITE3_OPEN_READONLY;
public $encript = "";
// $iflags :
// SQLITE3_OPEN_READONLY, SQLITE3_OPEN_READWRITE, SQLITE3_OPEN_CREATE
function __construct( $idbfile = '',
$iflags = SQLITE3_OPEN_READWRITE, $iencryption = '' )
{
error_log("SqLiteEx : constructor");
$this--->name = "SqLiteEx";
$this->dbfile = $idbfile;
$this->flags = $iflags;
$this->encript = $iencryption;
}
function __destruct()
{
error_log("SqLiteEx : destructor");
$this->dbclose();
}
function dbopen2( $idbfile, $iflags, $iencryption )
{
try
{
$this->open( $idbfile, $iflags, $iencryption );
}
catch( Exception $e )
{
error_log( "SqLiteEx : !! exception !!" );
error_log( $e->getMessage() );
throw $e;
}
return;
}
function dbopen()
{
$retval = 0;
$retval = $this->dbopen2( $this->dbfile,
$this->flags, $this->encript );
return $retval;
}
function dbclose()
{
$this->close();
}
// $isql : string : sql string
function exec( $isql )
{
try
{
$retval = false;
$retval = parent::exec( $isql );
}
catch( Exception $e )
{
error_log( $isql );
error_log( $e->getMessage() );
error_log( $e->getTraceAsString() );
}
if( $retval == false )
{
throw new Exception(
"SqLiteEx : !! exception !! " );
}
return $retval;
}
// $isql : string : sql string
// $iarray : boolean : TRUE return array value, FALSE string
function querySingle( $isql, $iarray )
{
try
{
$retval = null;
$retval = parent::querySingle( $isql, $iarray );
}
catch( Exception $e )
{
error_log( "SqLiteEx : !! exception !!" );
error_log( $isql );
error_log( $e->getMessage() );
error_log( $e->getTraceAsString() );
}
return $retval;
}
// $isql : string : sql string
// return : SQLite3Result
function query( $isql )
{
try
{
$retval = null;
$retval = parent::query( $isql );
}
catch( Exception $e )
{
error_log( "SqLiteEx : !! exception !!" );
error_log( $isql );
error_log( $e->getMessage() );
error_log( $e->getTraceAsString() );
}
return $retval;
}
// $isql : string : sql string
// return : SQLite3Stmt
function prepare( $isql )
{
$retval = null;
$retval = parent::prepare( $isql );
return $retval;
}
// $itype :
// SQLITE3_INTEGER, SQLITE3_FLOAT, SQLITE3_TEXT, SQLITE3_BLOB, SQLITE3_NULL
function prepareBindValue( &$istmt, $iparam, $ival, $itype )
{
$retval = false;
if( $istmt != null )
{
$retval = $istmt->bindValue( $iparam, $ival, $itype );
}
return $retval;
}
// The bind parameter remains in the istmt.
function prepareReset( &$istmt )
{
$retval = false;
if( $istmt != null )
{
$retval = $istmt->reset();
}
return $retval;
}
// The bind parameter does not remain in the istmt.
function prepareClear( &$istmt )
{
$retval = false;
if( $istmt != null )
{
$retval = $istmt->clear();
}
return $retval;
}
// return : SQLite3Result
// 注意 ： SQLite3Stmt::execute()で取り出したデータはソートが利かない。
// Select文はSQLite3::queryを使ったほうが良い。
function prepareExecute( &$istmt )
{
$retval = false;
if( $istmt != null )
{
try
{
$retval = $istmt->execute();
}
catch( Exception $e )
{
error_log( "SqLiteEx : !! exception !!" );
error_log( $e->getTraceAsString() );
error_log( $e->getMessage() );
}
}
return $retval;
}
// $iresult : SQLite3Result
// $imode : SQLITE3_ASSOC, SQLITE3_NUM, SQLITE3_BOTH
function fetchArray( $iresult = NULL, $imode = SQLITE3_ASSOC )
{
$retval = array();
if( $iresult != null )
{
while( $res = $iresult->fetchArray( $imode ) )
{
$retval[] = $res;
}
}
else
{
error_log("sqlite result is null.");
}
return $retval;
}
function fetchJson( $iresult = NULL, $imode = SQLITE3_ASSOC )
{
return json_encode( $this->fetchArray( $iresult, $imode ) );
}
// transaction
function begin()
{
// BEGIN DEFERRED
// other user can read and write until updating query.
// BEGIN IMMEDIATE
// other user can read until updating query.
// BEGIN EXCLUSIVE
// other user can not read and write before end transaction.
$retval = false;
$retval = $this->exec("BEGIN DEFERRED;");
return $retval;
}
function commit()
{
$retval = false;
$retval = $this->exec("COMMIT;");
return $retval;
}
function rollback()
{
$retval = false;
$retval = $this->exec("ROLLBACK;");
error_log("!! rollback !!");
return $retval;
}
}
上記クラスの簡単な使い方
27行目あたりでは意図的にSQLエラーを発生させてローバックさせている。
明示的にトランザクションを開始しないとDML処理が相当遅いらしいので注意する。
sample1.jpegからバイナリデータを取得してBLOB型に格納している。
SQLite Database Browserなどで正しく画像として登録されている確認する。
<!--?php
include_once( "../lib/class.sqliteex.php" );
@unlink( "test.db" );
$status = true;
$sqex = new SqLiteEx( "test.db", SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE );
// exec, query, rollback, commitのテスト
try
{
$retval = $sqex--->dbopen();
echo "[1]".$retval.PHP_EOL;
$retval = $sqex->exec( "create table foo (bar string)" );
echo "[2]".$retval.PHP_EOL;
$retval = $sqex->begin();
echo "[3]".$retval.PHP_EOL;
$retval = $sqex->exec( "insert into foo (bar) values('aaa')" );
echo "[4]".$retval.PHP_EOL;
$retval = $sqex->commit();
echo "[5]".$retval.PHP_EOL;
$retval = $sqex->begin();
echo "[6]".$retval.PHP_EOL;
$retval = $sqex->exec( "insert into foo(bar) values('bbb')" );
echo "[7]".$retval.PHP_EOL;
$retval = $sqex->exec( "insert into foo(bar) values('ccc')a" );
echo "[8]".$retval.PHP_EOL;
}
catch( Exception $e )
{
$status = false;
error_log( $e->getMessage() );
$sqex->rollback();
}
if( $status == true )
{
$sqex->commit();
}
$result = $sqex->query( "select bar from foo" );
echo $sqex->fetchJson( $result );
$sqex->close();
echo PHP_EOL;
// SQLite3Stmtのテスト
$status = true;
try
{
$retval = $sqex->dbopen();
echo "[9]".$retval.PHP_EOL;
$retval = $sqex->exec(
"create table sample (col1 integer, col2 real, col3 text, col4 blob)" );
echo "[10]".$retval.PHP_EOL;
$istmt = $sqex->prepare(
"insert into sample (col1,col2,col3,col4) values(:v1, :v2, :v3, :v4)" );
$retval = $sqex->begin();
$retval = $sqex->prepareBindValue( $istmt, ":v1", 1, SQLITE3_INTEGER );
echo "[11]".$retval.PHP_EOL;
$retval = $sqex->prepareBindValue( $istmt, ":v2", 1.234, SQLITE3_FLOAT );
echo "[12]".$retval.PHP_EOL;
$retval = $sqex->prepareBindValue( $istmt, ":v3", "abc", SQLITE3_TEXT );
echo "[13]".$retval.PHP_EOL;
$img = file_get_contents('./sample1.jpg');
$retval = $sqex->prepareBindValue( $istmt, ":v4", $img, SQLITE3_BLOB );
echo "[14]".$retval.PHP_EOL;
$result = $sqex->prepareExecute( $istmt );
// リセットして次のレコードを登録
$retval = $sqex->prepareReset( $istmt );
echo "[15]".$retval.PHP_EOL;
$retval = $sqex->prepareBindValue( $istmt, ":v1", 2, SQLITE3_INTEGER );
$retval = $sqex->prepareBindValue( $istmt, ":v2", 9.999, SQLITE3_FLOAT );
$retval = $sqex->prepareBindValue( $istmt, ":v3", "xyz", SQLITE3_TEXT );
$retval = $sqex->prepareBindValue( $istmt, ":v4", null, SQLITE3_BLOB );
$result = $sqex->prepareExecute( $istmt );
}
catch( Exception $e )
{
$status = false;
error_log( $e->getMessage() );
$sqex->rollback();
}
if( $status == true )
{
$sqex->commit();
}
$sqex->close();
?>
