Sqlite3でphp用データベース操作クラスの作成

標準ライブラリとして用意されている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();
?>