bindhelper.php

<?php

//###define
define( "TD_NAME", 0 );//テーブル名
define( "TD_N", 1 );//col数
define( "TD_KEY", 2 );//wherekeyの数
define( "TD_COLNAME0", 3 );//col名先頭

define( "TD_AUTO", -1 );//通常
define( "TD_NUM", 0 );//num
define( "TD_STR", 1 );//str
define( "TD_DATE", 2 );//date
define( "TD_DATE_I", 3 );//insert時オートsysdate
define( "TD_DATE_U", 4 );//insert/update時オートsysdate
define( "TD_NOBIND", 5 );//バインドしない

//###val
$debug_buf = "";

//###Bindhelper_Con
class Bindhelper_Con {
    var $con;//OCI

    function &Init( $id, $pass, $dbstr )
    {
        $this->con = OCIPLogon( $id, $pass, $dbstr );

        return $this->con;
    }
};


//###Bindhelper_Con_EX
class Bindhelper_Con_EX extends Bindhelper_Con {
    var $dateformat_old;

    function &Init( $id, $pass, $dbstr )
    {
        $this->con = OCIPLogon( $id, $pass, $dbstr );
        bh_AddDebugBuf("Init Bindhelper_Con_EX");

        $this->dateformat_old = $this->GetNLSDATEFORMAT();
        $this->ChangeNLSDATEFORMAT( "YYYY/MM/DD HH24:MI:SS" );

        return $this->con;
    }


    function End()
    {
        if( $this->con )
        {
//          $this->ChangeNLSDATEFORMAT("RR-MM-DD");
            $this->ChangeNLSDATEFORMAT($this->dateformat_old);//"RR-MM-DD");
            bh_Rollback();
            bh_AddDebugBuf("End Bindhelper_Con_EX");
            $this->con = NULL;
        }
    }

    function ChangeNLSDATEFORMAT( $str )
    {
        $req = "ALTER SESSION SET NLS_DATE_FORMAT='" . $str . "'";
    
    //  bh_Request($req,false);
    
        bh_AddDebugBuf($req);
        $stmt = OCIParse( $this->con, $req );
        OCIExecute( $stmt );
        OCIFreeStatement( $stmt );
    }

    function GetNLSDATEFORMAT()
    {
        $stmt = OCIParse( $this->con, "SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT'" );
        OCIExecute( $stmt );
        OCIFetchInto( $stmt,$row );
    // bh_AddDebugBuf($row[0]);
        OCIFreeStatement( $stmt );
    
        return $row[0];
    }
};

$bhc = new Bindhelper_Con_EX();//globalに投げ出す


//###Bindhelper
class Bindhelper {
//##protected:
        var $TD;//テーブル定義への参照
        var $pkupdate;
        var $bLoad;//データロードされてるフラグ
        var $Value;//データ本体リスト
        var $pkshadow;//$pkupdate用。。
        var $bUpdate;//更新フラグリスト


        //内部処理用
        function Fill( &$stmt )//フェッチ済stmtからデータもらう
        {
            $this->bLoad = false;

            for( $i = 0; $i < $this->TD[TD_N]; $i++ )
            {
                $this->Value[$i] = OCIResult($stmt,$i+1);

                if( $i < $this->TD[TD_KEY] )
                {
                    $this->pkshadow[$i] = $this->Value[$i];//プライマリキーコピー。。
                }

                $this->Value[$i] = trim( $this->Value[$i] );

                $this->bUpdate[$i] = false;
            }

            $this->bLoad = true;
        }


//##public:
        //コンストラクタ
        function Bindhelper( &$td, $pkupdate=false )
        {
            $this->TD =& $td;//テーブル定義への参照もらう
            $this->pkupdate = $pkupdate;
            $this->bLoad = false;
            $this->Value = array();
            $this->pkshadow = array();
            $this->bUpdate = array();
        }


        function Load( $binddata, $forupdate=false )//標準load
        {
            global $bhc;

            $update = 0;//:V?

            $colstr = bh_CatColName( $this->TD );//colm名ならべる

            $req = "SELECT $colstr FROM " . $this->TD[TD_NAME] . " WHERE ";

            for( $i = 0; $i < $this->TD[TD_KEY]; $i++ )
            {
                $req .= sprintf( "%s%s=:V%d", (0<$i)?" AND ":"", $this->TD[ TD_COLNAME0+$i*2 ], $update );//:Vx
                $update++;
            }
    
            //おしまい
            if( $forupdate )
            {
                $req .= " FOR UPDATE";
            }

bh_AddDebugBuf($req);
            $stmt = OCIParse( $bhc->con, $req );

            //バインド
            for( $i = 0; $i < $update; $i++ )
            {
                $buf = sprintf( ":V%d", $i );
                OCIBindByName( $stmt, $buf, $binddata[$i], -1 );
bh_AddDebugBuf("bind:V".$i."=".$binddata[$i]);
            }

            @OCIExecute( $stmt, ($forupdate)? OCI_DEFAULT:OCI_COMMIT_ON_SUCCESS );//$forupdateでロックするならコミットするな
            if( $err = OCIError($stmt) )
            {
                foreach( $err as $key=>$val )
                {
                    bh_AddDebugBuf("$key=>$val");
                }
                return false;
            }

            $ret = OCIFetch($stmt);//先頭1行もらう
            if( $ret )
            {
                $this->Fill( $stmt );//データfill
            }
            OCIFreeStatement($stmt);

            if( $ret )
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        function Save( $autocommit=true )//標準Save
        {
            global $bhc;

            if( !$this->bLoad )
            {
                return false;
            }

            $update = 0;//:V?
            $autosysdate = false;
            $binddata = array();//bind送りするデータ
            $req = "UPDATE " . $this->TD[TD_NAME] . " SET ";

            $i0 = ($this->pkupdate)? 0:$this->TD[TD_KEY];//プライマリキーのupdate有り無し

            for( $i = $i0; $i < $this->TD[TD_N]; $i++ )
            {
                if( $this->TD[ TD_COLNAME0+$i*2+1 ] == TD_DATE_U )//fixme
                {   //auto sysdate
                    $req .= sprintf( "%s%s=SYSDATE", (0<$update||$autosysdate)?",":"", $this->TD[ TD_COLNAME0+$i*2 ] );
                    $autosysdate = true;
                }
                else if( $this->bUpdate[$i] )
                {   //request文構成
                    $req .= sprintf( "%s%s=:V%d", (0<$update||$autosysdate)?",":"", $this->TD[ TD_COLNAME0+$i*2 ], $update );
                    $binddata[$update] = $this->Value[$i];
                    $update++;
                }
            }
            //検索キーとフッタ追加
            if( 0 < $update || $autosysdate )
            {
                $req .= " WHERE ";

                for( $i = 0; $i < $this->TD[TD_KEY]; $i++ )
                {
                    $req .= sprintf( "%s%s=:V%d", (0<$i)?" AND ":"", $this->TD[ TD_COLNAME0+$i*2 ], $update );//:Vx
                    $binddata[$update] = $this->pkshadow[$i];
                    $update++;
                }

bh_AddDebugBuf($req);
                $stmt = OCIParse( $bhc->con, $req );
                //バインド
                for( $i = 0; $i < $update; $i++ )
                {
                    OCIBindByName( $stmt, ":V$i", $binddata[$i], -1 );
//                  $binddata[$i] = stripslashes($binddata[$i]);//余計なお世話
bh_AddDebugBuf("bind:V".$i."=".$binddata[$i]);
                }

                @OCIExecute($stmt, ($autocommit)? OCI_COMMIT_ON_SUCCESS:OCI_DEFAULT );
                if( $err = OCIError($stmt) )
                {
                    foreach( $err as $key=>$val )
                    {
                        bh_AddDebugBuf("$key=>$val");
                    }
                    return false;
                }

                OCIFreeStatement($stmt);

                for( $i = 0; $i < count($this->bUpdate); $i++ )
                {
                    $this->bUpdate[$i] = false;
                }

                return true;
            }

            return true;
        }


        function Insert( $binddata, $autocommit=true )//標準insert
        {
            global $bhc;

            $colstr = "(".bh_CatColName($this->TD).")";//colm名ならべる

            $update = 0;//:V?
            $autosysdate = false;
            $req = "INSERT INTO " . $this->TD[TD_NAME] . $colstr . " VALUES(";


            for( $i = 0; $i < $this->TD[TD_N]; $i++ )
            {   //request文構成
                switch( $this->TD[ TD_COLNAME0+$i*2+1 ] )
                {
                    case TD_DATE_I://auto sysdate
                    case TD_DATE_U:
                        $req .= sprintf( "%sSYSDATE", (0<$update||$autosysdate)?",":"" );
                        $autosysdate = true;
                        break;

                    case TD_NOBIND:
                        $req .= sprintf( "%s%s", (0<$update||$autosysdate)?",":"", $binddata[$i] );
                        $update++;
                        $autosysdate = true;
                        break;

                    default:
                        $req .= sprintf( "%s:V%d", (0<$update)?",":"", $update );
                        $update++;
                        break;
                }
            }

            $req .= ")";//おしまい

bh_AddDebugBuf($req);
            $stmt = OCIParse( $bhc->con, $req );
            //バインド
            for( $i = 0; $i < $update; $i++ )
            {
                if( $this->TD[ TD_COLNAME0+$i*2+1 ] != TD_NOBIND )
                {
                    OCIBindByName( $stmt, ":V$i", $binddata[$i], -1 );
//                  $binddata[$i] = stripslashes($binddata[$i]);//余計なお世話
bh_AddDebugBuf("bind:V".$i."=".$binddata[$i]);
                }
            }

            @OCIExecute($stmt, ($autocommit)? OCI_COMMIT_ON_SUCCESS:OCI_DEFAULT );
            if( $err = OCIError($stmt) )
            {
                foreach( $err as $key=>$val )
                {
                    bh_AddDebugBuf("$key=>$val");
                }
                return false;
            }

            OCIFreeStatement($stmt);

            return true;
        }


        function Delete( $binddata, $autocommit=true )//標準delete
        {
            global $bhc;

            $update = 0;//:V?
            $req = "DELETE FROM " . $this->TD[TD_NAME] . " WHERE ";

            for( $i = 0; $i < $this->TD[TD_KEY]; $i++ )
            {
                $req .= sprintf( "%s%s=:V%d", (0<$i)?" AND ":"", $this->TD[ TD_COLNAME0+$i*2 ], $update );//:Vx
                $update++;
            }


bh_AddDebugBuf($req);
            $stmt = OCIParse( $bhc->con, $req );
            //バインド
            for( $i = 0; $i < $update; $i++ )
            {
                OCIBindByName( $stmt, ":V$i", $binddata[$i], -1 );
bh_AddDebugBuf("bind:V".$i."=".$binddata[$i]);
            }

            @OCIExecute($stmt, ($autocommit)? OCI_COMMIT_ON_SUCCESS:OCI_DEFAULT );
            if( $err = OCIError($stmt) )
            {
                foreach( $err as $key=>$val )
                {
                    bh_AddDebugBuf("$key=>$val");
                }
                return false;
            }

            OCIFreeStatement($stmt);

            return true;
        }


        function Merge( $binddata, $autocommit=true )//update/insert
        {
            global $bhc;

            $wherekeystr = " WHERE ";
            for( $i = 0; $i < $this->TD[TD_KEY]; $i++ )
            {
                $wherekeystr .= sprintf( "%s%s=:V%d", (0<$i)?" AND ":"", $this->TD[TD_COLNAME0+$i*2], $update );//:Vx
                $update++;
            }

            $update = 0;//:V?
            $autosysdate = false;
            $req_update .= "UPDATE ".$this->TD[TD_NAME]." SET ";
            for( $i = 0; $i < $this->TD[TD_N]; $i++ )
            {
                if( $this->TD[ TD_COLNAME0+$i*2+1 ] == TD_DATE_I )
                {   //null
                }
                else if( $this->TD[ TD_COLNAME0+$i*2+1 ] == TD_DATE_U )
                {   //auto sysdate
                    $req_update .= sprintf( "%s%s=SYSDATE", (0<$update||$autosysdate)?",":"", $this->TD[ TD_COLNAME0+$i*2 ] );
                    $autosysdate = true;
                }
                else
                {   //request文構成
                    $req_update .= sprintf( "%s%s=:V%d", (0<$update||$autosysdate)?",":"", $this->TD[ TD_COLNAME0+$i*2 ], $update );
                    $update++;
                }
            }
            $req_update .= " $wherekeystr;";


            $update = 0;//:V?
            $autosysdate = false;
            $req_insert .= "INSERT INTO ".$this->TD[TD_NAME]."(".bh_CatColName($this->TD).")"." VALUES(";
            for( $i = 0; $i < $this->TD[TD_N]; $i++ )
            {   //request文構成
                switch( $this->TD[ TD_COLNAME0+$i*2+1 ] )
                {
                    case TD_DATE_I://auto sysdate
                    case TD_DATE_U:
                        $req_insert .= sprintf( "%sSYSDATE", (0<$update||$autosysdate)?",":"" );
                        $autosysdate = true;
                        break;

                    case TD_NOBIND:
                        $req_insert .= sprintf( "%s%s", (0<$update||$autosysdate)?",":"", $binddata[$i] );
                        $update++;
                        $autosysdate = true;
                        break;

                    default:
                        $req_insert .= sprintf( "%s:V%d", (0<$update)?",":"", $update );
                        $update++;
                        break;
                }
            }
            $req_insert .= ");";//おしまい


            $req = ""
                ." DECLARE"
                ."  TMP_NUM NUMBER(10);"
                ." BEGIN"
                ."  SELECT COUNT(*) INTO TMP_NUM FROM ".$this->TD[TD_NAME].$wherekeystr.";"
                ."  IF 0<TMP_NUM THEN"
                ."   $req_update"
                ."  ELSE"
                ."   $req_insert"
                ."  END IF;"
                ." END;";


bh_AddDebugBuf($req);
            $stmt = OCIParse( $bhc->con, $req );
            //バインド
            for( $i = 0; $i < $update; $i++ )
            {
                if( $this->TD[ TD_COLNAME0+$i*2+1 ] != TD_NOBIND )
                {
                    OCIBindByName( $stmt, ":V$i", $binddata[$i], -1 );
//                  $binddata[$i] = stripslashes($binddata[$i]);//余計なお世話
bh_AddDebugBuf("bind:V".$i."=".$binddata[$i]);
                }
            }

            @OCIExecute($stmt, ($autocommit)? OCI_COMMIT_ON_SUCCESS:OCI_DEFAULT );
            if( $err = OCIError($stmt) )
            {
                foreach( $err as $key=>$val )
                {
                    bh_AddDebugBuf("$key=>$val");
                }
                return false;
            }

            OCIFreeStatement($stmt);

            return true;
        }

        function GetValue( $x )
        {
            return $this->Value[$x];
        }


        function SetValue( $index, $value )
        {
            if( $this->Value[$index] != $value )
            {
                $this->Value[$index] = $value;
                $this->bUpdate[$index] = true;
            }
        }


        //丸ごと返す
        function GetValueAll()
        {
            return $this->Value;
        }
};


//###BindHelperList
class BindHelperList {
//##protected:
    var $rows;//array
    var $row_classname;//classname
    var $TD;//テーブル定義への参照

//##public:
        //コンストラクタ
    function BindHelperList( &$td, $row_classname=null )
    {
        $this->TD =& $td;//テーブル定義への参照もらう
        $this->row_classname = $row_classname;//arrayクラス名
        $this->$rows = array();
    }


    function Load( $binddata=null, $forupdate=false )
    {
        global $bhc;

        $update = 0;//:V?

        $colstr = bh_CatColName( $this->TD );//colm名ならべる

        $req = "SELECT $colstr FROM " . $this->TD[TD_NAME];

        if( $binddata != null )//pk指定あり
        {
            $req .= " WHERE ";
            for( $i = 0; $i < count($binddata); $i++ )
            {
                $req .= sprintf( "%s%s=:V%d", (0<$i)?" AND ":"", $this->TD[ TD_COLNAME0+$i*2 ], $update );//where列追加//:Vx
                $update++;
            }
        }

        $req .= " ORDER BY ";
        for( $i = 0; $i < $this->TD[TD_KEY]; $i++ )
        {
            $req .= sprintf( "%s%s ASC", (0<$i)?",":"", $this->TD[ TD_COLNAME0+$i*2 ] );//order列追加
        }

        //おしまい
        if( $forupdate )
        {
            $req .= " FOR UPDATE";
        }

bh_AddDebugBuf($req);
        $stmt = OCIParse( $bhc->con, $req );

            //バインド
            for( $i = 0; $i < $update; $i++ )
            {
                $buf = sprintf( ":V%d", $i );
                OCIBindByName( $stmt, $buf, $binddata[$i], -1 );
bh_AddDebugBuf("bind:V".$i."=".$binddata[$i]);
            }

        @OCIExecute( $stmt, ($forupdate)? OCI_DEFAULT:OCI_COMMIT_ON_SUCCESS );//$forupdateでロックするならコミットするな
        if( $err = OCIError($stmt) )
        {
            foreach( $err as $key=>$val )
            {
                bh_AddDebugBuf("$key=>$val");
            }
            return false;
        }

        $i = 0;
        while( OCIFetch($stmt) )
        {
            if( $this->row_classname == null )
            {   //子クラス名を指定しないと標準のBindhelperにTDを渡して生成するが
                //子クラスでextendしたメソッドは使えないしpkshadowの設定もできない
                $this->rows[$i] = new BindHelper($this->TD);
            }
            else
            {
                $this->rows[$i] = new $this->row_classname();
            }

            $this->rows[$i]->Fill( $stmt );//データfill
            $i++;
        }

        OCIFreeStatement($stmt);
        return true;
    }


    function Save( $autocommit=true )
    {
        for( $i = 0; $i < count($this->rows); $i++ )
        {
            if( !$this->rows[$i]->Save($autocommit) )
            {
                return false;
            }
        }
        return true;
    }
}

//###util
function bh_CatColName( &$td )
{
    $colstr = "";//colm名ならべる
    for( $i = 0; $i < $td[TD_N]; $i++ )
    {
        $colstr .= $td[ TD_COLNAME0+$i*2 ];
        if( $i+1 < $td[TD_N] )
        {
            $colstr .= ",";
        }
    }

    return $colstr;
}


function bh_Request($req,$autocommit=false)
{
    global $bhc;

bh_AddDebugBuf($req);
    $stmt = OCIParse( $bhc->con, $req );
    @OCIExecute($stmt, ($autocommit)? OCI_COMMIT_ON_SUCCESS:OCI_DEFAULT );
    if( $err = OCIError($stmt) )
    {
        foreach( $err as $key=>$val )
        {
            bh_AddDebugBuf("$key=>$val");
        }
        return false;
    }

    while( OCIFetchInto( $stmt, $row, OCI_NUM|OCI_RETURN_NULLS ) )
    {
        for( $i = 0; $i < count($row); $i++ )
        {
            $row[$i] = trim($row[$i]);
        }

        $ret[] = $row;
    }

    OCIFreeStatement($stmt);


    if( is_array($ret) )
    {
        return $ret;
    }
    else
    {
        return true;
    }
}


function bh_RequestBind($req,&$binddata,$autocommit=false)
{
    global $bhc;

bh_AddDebugBuf($req);
    $stmt = OCIParse( $bhc->con, $req );

    for( $i = 0; $i < count($binddata); $i++ )
    {
        OCIBindByName( $stmt, sprintf(":V%d",$i), $binddata[$i], 1024 );
//      OCIBindByName( $stmt, sprintf(":V%d",$i), $binddata[$i], -1, OCI_B_SQLT_NTY );
bh_AddDebugBuf("bind:V".$i."=".$binddata[$i]);
    }


    @OCIExecute($stmt, ($autocommit)? OCI_COMMIT_ON_SUCCESS:OCI_DEFAULT );
    if( $err = OCIError($stmt) )
    {
        foreach( $err as $key=>$val )
        {
            bh_AddDebugBuf("$key=>$val");
        }
        return false;
    }

    while( @OCIFetchInto( $stmt, $row, OCI_NUM|OCI_RETURN_NULLS ) )
    {
        for( $i = 0; $i < count($row); $i++ )
        {
            $row[$i] = trim($row[$i]);
        }
        $ret[] = $row;
    }

    OCIFreeStatement($stmt);


    if( is_array($ret) )
    {
        return $ret;
    }
    else
    {
        return true;
    }
}


function bh_Commit()
{
    global $bhc;
    return OCICommit( $bhc->con );
}


function bh_Rollback()
{
    global $bhc;
    return OCIRollback( $bhc->con );
}


//##debug
function bh_AddDebugBuf($req)
{
    global $debug_buf;
    $debug_buf .= htmlspecialchars($req);
    $debug_buf .= "<br>";
}

function bh_PrintDebugBuf( $mode=0 )
{
    global $debug_buf;

    if( mode == 0 )
    {
        echo $debug_buf;
    }
    else
    {
        echo "<!--";
        echo $debug_buf;
        echo ">";
    }
}

function bh_SetDebugBuf($req)
{
    global $debug_buf;
    $debug_buf = $req;
}

?>

return to index

---

ファイルの終端に到達した.