<?php
/**
 * Database
 *
 * Database class presents abstract handling for any type of RDBMS or data collection(php array, xml, csv ....)
 *
 * LICENSE: This source file is subject to GNU GENERAL PUBLIC LICENSE Version 3
 * http://www.gnu.org/licenses/gpl-3.0.txt.
 *
 * @package    LabbitBox
 * @subpackage DB
 * @copyright  2010 LabbitBox Development Team.
 * @license    GPL v3 http://www.gnu.org/licenses/gpl-3.0.txt
 * @author     minotaur <minotaur@labbitbox.org>
 * @version    v0.1 alpha3
 * @since      -
 * @link       http://labbitbox.org/
 * @see        http://labbitbox.org/
 */
require_once(dirname(__FILE__).'/../common.php');
class DB{

    /**
     * Database Type Name
     * @var string $_type Database Type Name
     */
    protected $_type = 'array';

    /**
     * Database regulator object to bridge to each native driver.
     * @var DB_Driver $_dbDriver DatabaseDriver object
     */
    protected $_dbDriver = NULL;

    /**
     * Connection Handle Object
     * @var Connection $_connection Connection Handle Objerct
     */
    protected $_connection = NULL;

    /**
     * Transaction Object list
     * @var array $_transaction Transaction Object list
     */
    protected $_transaction = array();

    /**
     * list of DAO(DataAccessObject)
     */
    protected $_datasets = array();

    /**
     * デバッグモード
     *
     * デバッグモードを有効にすることで発行されたSQLを確認することが出来ます
     * @var boolean $_debug デフォルトはFALSE
     */
    protected $_debug        = FALSE;
    protected $_debugFormat  = 'detailed';
    protected $_debugId      = 'default';
    protected $_debugHtml    = FALSE;

    /**
     * QueryLog
     * @var array $_log
     */
    protected $_log = FALSE;

    /**
     * Constructor
     *
     * USAGE1 / using in LabbitBox StandardFlow: new DB($this->configById('DB');
     * USAGE2 / using Detailed information:      new DB('firebird', 'localhost', '3050', '/var/firebird/sample.fdb', 'SYSDBA', 'masterkey', 'UNICODE_FSS');
	 *
     * @param string $type         Conf object    or    Each type of datasource or RDBMS name
     * @param string $server       ServerName or IP Addresr for RDBMS
     * @param int    $port         NetworkConnectionPort for RDBMS or SSH
     * @param string $databasePath DatabasePath for RDBMS or storage directory.
     * @param string $userName     UserName for RDBMS or SSH
     * @param string $password     Password for RDBMS or SSH
     * @param string $characterSet not required
     * @return null
     */
    public function __construct($type, $server='localhost', $port=NULL, $databasePath=NULL, $userName=NULL, $password=NULL, $characterSet=NULL, $reportTo=NULL){
        $this->_log[] = array('TIME'=>microtime(TRUE), 'STATUS'=>'DB Object was created.', 'DATA'=>"================================");

        // for setting by  Config object
        if(is_array($type)){
            $config       = $type;
            $type         = $config['type'];
            $server       = $config['server'];
            $port         = $config['port'];
            $databasePath = $config['databasePath'];
            $userName     = $config['userName'];
            $password     = $config['password'];
            $characterSet = $config['characterSet'];
            $this->reportTo($config['reportTo']);
            if($config['sqlLib'] && file_exists($config['sqlLib'])) $this->loadSql($config['sqlLib']);
            $this->_config = $config;
        }else{
            $this->_config['type']         = $type         ;
            $this->_config['server']       = $server       ;
            $this->_config['port']         = $port         ;
            $this->_config['databasePath'] = $databasePath ;
            $this->_config['userName']     = $userName     ;
            $this->_config['password']     = $password     ;
            $this->_config['characterSet'] = $characterSet ;
            $this->_config['reportTo']     = $reportTo     ;

            $this->reportTo($reportTo);
        }
        $this->_type = ( $type ? $type : 'array' );
        $wkClassName = 'DB_Driver_'.$this->_type;
        require_once(dirname(__FILE__).'/'.$wkClassName.'.php');
        $this->_dbDriver = new $wkClassName($this);

        $this->_connection = $this->connect($server, $port, $databasePath, $userName, $password, $characterSet);
        $this->_log[] = array('TIME'=>microtime(TRUE), 'STATUS'=>'Connected', 'DATA'=>'');
    }


    /**
     * Destructor
     */
    public function __destruct(){
        $this->disconnect();
        if($this->_debug===TRUE){
            foreach($this->_log as $curLog){
                echo strftime('%Y/%m/%d %H:%M:%S.', floor($curLog['TIME'])).substr($curLog['TIME']-floor($curLog['TIME']), 2)." : ".$curLog['DATA']." ---- ".$curLog['STATUS']."<br />\n";
            }
        }else{
            if($this->_debug!==FALSE){
                if($this->_debugHtml) $formattedLog.='<table border="1">';
                foreach($this->_log as $curLog){
                    if(count($preLog)==0) $preLog = $curLog;
                    if($this->_debugHtml){
                        $delay = floor(($curLog['TIME'] - $preLog['TIME'])*1000)+20;
                        $formattedLog.= '<tr style="height:'.$delay.'px"><td>&nbsp;</td></tr>';
                        $formattedLog.= '<tr><td>'.strftime('%Y/%m/%d %H:%M:%S.', floor($curLog['TIME'])).substr($curLog['TIME']-floor($curLog['TIME']), 2)."</td><td><pre>".$curLog['DATA']."</pre></td><td>".$curLog['STATUS']."</td></tr>\n";
                        $preLog = $curLog;
                    }else{
                        $formattedLog.= strftime('%Y/%m/%d %H:%M:%S.', floor($curLog['TIME'])).substr($curLog['TIME']-floor($curLog['TIME']), 2)." : ".$curLog['DATA']." ---- ".$curLog['STATUS']."\n";
                    }
                }
                if($this->_debugHtml) $formattedLog.='</table>';
                $logFileName = str_replace('%i', md5($formattedLog), $this->_debug);
                file_put_contents( $logFileName, $formattedLog, FILE_APPEND).' bytes writte.';
            }
        }
    }


    /**
     * connect
     *
     * @param string $server       ServerName or IP Address for RDBMS
     * @param int    $port         NetworkConnectionPort for RDBMS or SSH
     * @param string $databasePath DatabasePath for RDBMS or storage directory.
     * @param string $userName     UserName for RDBMS or SSH
     * @param string $password     Password for RDBMS or SSH
     * @param string $characterSet not required
     * @return null
     */
    public function connect($server, $port, $databasePath, $userName, $password, $characterSet=NULL){
        if($this->_connection !== NULL){
            $this->disconnect($this->_connection);
        }
        return $this->_connection = $this->_dbDriver->connect($server, $port, $databasePath, $userName, $password, $characterSet);
    }


    /**
     * disconnect from database
     *
     * @return TRUE
     */
    public function disconnect(){
        if(count($this->_transaction)){
            foreach($this->_transaction as $curTransactionId=>$curTransaction){
                // wish to implements auto-commit or auto-rollback to Transaction_xxxx class.
                //if($curTransaction!==NULL) $this->_dbDriver->commit($curTransaction, TRUE);
                //$this->_transaction[$curTransactionId] = NULL;
            }
        }
        if($this->_connection !== NULL) $this->_dbDriver->disconnect($this->_connection);
        $this->_connection = NULL;
        return TRUE;
    }



    /**
     * reconnect database
     */
    public function reconnect(){
        $this->disconnect();
        $this->connect(
            $this->_config['server']       ,
            $this->_config['port']         ,
            $this->_config['databasePath'] ,
            $this->_config['userName']     ,
            $this->_config['password']     ,
            $this->_config['characterSet']
        );
    }


    /**
     * Start transaction
     *
     * @return int TransactionId TransactionId is for $this->_transaction[XXXX], it is NOT Transaction php-object.
     */
    public function startTransaction($transactionId=NULL){
        if(isset($this->_transaction[0])===FALSE){
            $this->_transaction[0] = $this->_dbDriver->startTransaction($this->_connection);
        }
        if($transactionId===NULL){
            $this->_transaction[] = $this->_dbDriver->startTransaction($this->_connection);
            return count($this->_transaction)-1;
        }else{
            $this->_transaction[$transactionId] = $this->_dbDriver->startTransaction($this->_connection);
            return $transactionId;
        }
    }

    /**
     * commit transaction
     *
     * @return string ErrorMessage
     */
    public function commit($transactionId=NULL){
        $transactionId = $this->_getTransactionId($transactionId);
        $this->_dbDriver->commit($transactionId);
        return TRUE; // we should write control for error.
    }

    /**
     * rollback transaction
     *
     * @return string ErrorMessage
     */
    public function rollback($transactionId=NULL){
        $transactionId = $this->_getTransactionId($transactionId);
        $this->_dbDriver->rollback($transactionId);
        return TRUE; // we should write control for error.
    }

    /**
     * checkpoint transaction
     *
     * @return string ErrorMessage
     */
    public function checkpoint($transactionId=NULL){
        $transactionId = $this->_getTransactionId($transactionId);
        $this->_dbDriver->checkpoint($transactionId);
        return TRUE; // we should write control for error.
    }



    /**
     * get certain transaction id or default transaction id
     *
     * @param int $transactionId TransactionId
     * @return int TransactionId
     */
    protected function _getTransactionId($transactionId=NULL){
        if($transactionId===NULL){
            return $this->_connection;
        }else{
            return $this->_transaction[$transactionId];
        }
    }



    /**
     * query
     *
     * @param string  $query
     * @param mixed   $params array of parameter for replacement of SQL
     * @param boolean $autoValidation TRUE:auto string or numeric validation, FALSE: validate manually
     * @param int     $transactionId
     *
     * @return mixed DriverLevelDataset
     */
    public function query($query, $params=NULL, $autoValidation=TRUE, $transactionId=NULL){
        if(strpos($query, ' ')===FALSE){
            if($this->_debug){
                $StartTime = microtime(TRUE);
                $this->_log[] = array('TIME'=>$StartTime, 'STATUS'=>'Start', 'DATA'=>$query);
            }
            $result = $this->queryById($query, $params, $autoValidation, $transactionId);
            if($this->_debug){
                $EndTime = microtime(TRUE);
                $Period  = ($EndTime - $StartTime)*1000000;
                $this->_log[] = array('TIME'=>$EndTime, 'STATUS'=>"End\n\n\n", 'DATA'=>"$query (TIME:$Period µs)");
            }
        }else{
            if($this->_debug){
                $StartTime = microtime(TRUE);
                $this->_log[] = array('TIME'=>$StartTime, 'STATUS'=>'Start', 'DATA'=>mb_substr($query, 0, 32).'....');
            }
            $result = $this->queryBySql($query, $params, $autoValidation, $transactionId);
            if($this->_debug){
                $EndTime = microtime(TRUE);
                $Period  = ($EndTime - $StartTime)*1000000;
                $this->_log[] = array('TIME'=>$EndTime, 'STATUS'=>"End\n\n\n", 'DATA'=>mb_substr($query, 0, 32).".... (TIME:$Period µs)");
            }
        }
        return $result;
    }



    /**
     * SQLのIDによるクエリー発行
     *
     * @param string  $sqlId
     * @param mixed   $params array of parameter for replacement of SQL
     * @param boolean $autoValidation TRUE:auto string or numeric validation, FALSE: validate manually
     * @param int     $transactionId
     *
     * @return mixed DriverLevelDataset
     */
    public function queryById($sqlId, $params=NULL, $autoValidation=TRUE, $transactionId=NULL){
        if($this->_sql[$sqlId]){
            return $this->queryBySql($this->_sql[$sqlId], $params, $autoValidation, $transactionId);
        }else{
            if($this->_debug){
                $this->_log[] = array('TIME'=>microtime(TRUE), 'STATUS'=>'ERROR', 'DATA'=>"SQL ID: $sqlId is not found.");
            }
        }
    }



    /**
     * SQL文によるクエリー発行
     *
     * @param string  $sql
     * @param mixed   $params array of parameter for replacement of SQL
     * @param boolean $autoValidation TRUE:auto string or numeric validation, FALSE: validate manually
     * @param int     $transactionId
     *
     * @return mixed DriverLevelDataset
     */
    public function queryBySql($sql, $params=NULL, $autoValidation=TRUE, $transactionId=NULL){
        $this->_srcSql  = $sql;
        $this->_prmV    = $params;
        $this->_prmR    = $autoValidation;
        $this->_lastSql = $this->_replaceSqlParams($sql, $params, $autoValidation);
        $transaction    = $this->_getTransactionId($transactionId);
        $nativeDataset  = $this->_dbDriver->query($this->_lastSql, $transaction);
        if($this->errCheck()){
            require_once(dirname(__FILE__).'/DB_Exception.php');
            $exception = new DB_Exception($this->errMessage(), $this->errCode(), $this->_srcSql, $this->_prmV, $this->_prmR, $this->_lastSql, $transactionId, $this->_reportTo);
            $exception->report();
            throw $exception;
        }
        $this->_preDatasetInfo = $this->_dbDriver->getInfo($nativeDataset);
        if($this->_debug && $this->_debugFormat==='detailed'){
            $this->_log[] = array('TIME'=>microtime(TRUE), 'STATUS'=>"SQL", 'DATA'=>"\n".$this->_lastSql);
        }
        return $nativeDataset;
    }


    /**
     * fetch
     *
     * @param mixed $nativeDataset
     *
     * @return array RecordData or FALSE
     */
    public function fetch($nativeDataset){
        return $this->_dbDriver->fetch($nativeDataset);
    }



    /**
     * free result
     *
     * @param mxed $nativeDataset
     *
     * @return boolean Always TRUE
     */
    public function freeResult($nativeDataset){
        return $this->_dbDriver->freeResult($nativeDataset);
    }



    /**
     * Load SQL file
     *
     * load sql from <specified.sql.xml> or <dir>/*.sql
     *
     * @param string $sqlFileName
     *
     * @return int SqlCount
     */
    public function loadSql($sqlFileName){

		// for many sql files
		if(is_dir($sqlFileName)){
			$sqlDir = dir($sqlFileName);
			while(false !== ($fileName = $sqlDir->read())){
				if((preg_match('/\.sql$/', $fileName)>0)||(preg_match('/\.xml$/', $fileName)>0)){
					if(preg_match('/^\..*/', $fileName)===0) $sqlFileNames[] = $sqlFileName.'/'.$fileName;
				}
			}
		}else{
			$sqlFileNames[] = $sqlFileName;
		}

		// process of each sql files.
		foreach($sqlFileNames as $curSqlFileName){
			if(preg_match('/\.xml$/', $curSqlFileName)>0){
				// XML file format
				$xml = new DOMDocument('1.0', 'utf-8');
                if(file_exists($curSqlFileName)){
                    $xml->load($curSqlFileName);
                }else{
                    if($this->_debug){
                        $this->_log[] = array('TIME'=>microtime(TRUE), 'STATUS'=>'ERROR', 'DATA'=>"SQL FIle: $curSqlFileName is not exists.");
                    }
                }
				$sqls = $xml->getElementsByTagName('sql');
				for($iSqls=0; $iSqls<$sqls->length; $iSqls++){
					$this->_sql[trim($sqls->item($iSqls)->attributes->getNamedItem('id')->nodeValue)] = trim($sqls->item($iSqls)->nodeValue);
				}
			}else if(preg_match('/\.sql$/', $curSqlFileName)>0){
                // SQL file format as raw text
                $sqlId = explode('/', $curSqlFileName);
                $sqlId = $sqlId[count($sqlId)-1];
                $sqlId = explode('.', $sqlId);
				$rawText  = file_get_contents($curSqlFileName);
                $this->_sql[$sqlId[0]] = trim($rawText);
			}else{
                // sql file should written in *.sql or *.xml
            }
		}
		return count($this->_sql);
    }




    /**
     * 簡単なSQLの自動生成
     *
     * 標準的なSQLについてはこの関数の呼出によりDBに自動生成させることが出来ます。
     * 生成されるSQLのsql-idは 例えば LB_SELECT_TABLE_NAME のように LB_<種別>_<テーブル名> の組み合わせとなります。
     * 置き換えパラメータの一覧など実際的なところは sql() で確認してください。
     * # 応用的なSQLはアプリケーション開発者が任意に.xmlファイル及び.sqlディレクトリで指示可能です。
     *
     * @param strimg $tableName テーブル名が明示されればそのテーブルに関するSQLを、省略された場合にはDB中の全テーブルを対象にSQL生成を行います。
     *
     * @return boolean 正常に生成が行われればTRUE、失敗した場合にはFALSEが戻り値として返されます。
     */
    public function generateSimpleSql($tableName=NULL){
        $tableNames = array();
        if($tableName===NULL){
            // @todo: $tableNames = all tables from database
        }else{
            $tableNames[] = $tableName;
        }
        foreach($tableNames as $curTableName){
            // @todo: プライマリキーの解析が必要。

            // generate SELECT SQL
            $this->_sql["LB_SELECT_$curTableName"][] = "SELECT a, b, c FROM $curTableName WHERE .... ORDER BY .... ;";

            // generate UPDATE SQL
            $this->_sql["LB_UPDATE_$curTableName"][] = "UPDATE $curTableName SET a=1, b=2 .....  WHERE .... ;";

            // generate INSERT SQL
            $this->_sql["LB_INSERT_$curTableName"][] = "INSERT INTO $curTableName( FIELDS )VALUES( VALUES );";

            // generate DELETE SQL
            $this->_sql["LB_DELETE_$curTableName"][] = "DELETE FROM $curTableName WHERE ....;";
        }
        return TRUE;
    }


    /**
     * adding new dataset to local _datasets
     *
     * e.g. prebuilt Table or SQL Dataset for ability.
     *
     * @param mixed $query  SQL string, SQL string array, TableName, or SQL ID of loadSql
     * @param mixed $params array of parameter for replacement of SQL
     * @param boolean $autoValidation TRUE:auto string or numeric validation, FALSE: validate manually
     * @param int $transactionId NULL as 0(DefaultTransaction)
     *
     * @return Dataset Dataset
     */
    public function dataset($query, $params=NULL, $autoValidation=TRUE, $transactionId=NULL){
        require_once(dirname(__FILE__).'/Dataset.php');
        return $this->_datasets[] = new Dataset($this, $query, $params, $autoValidation, $transactionId);
    }



    /**
     * clear datasets array
     *
     * @return boolean always true
     */
    public function clearDatasets(){
        unset($this->_datasets);
        return TRUE;
    }



    /**
     * replacement SQL params
     *
     * @param string  $sql
     * @param mixed   $params
     * @param boolean $autoValidation TRUE:auto string or numeric validation, FALSE: validate manually
     *
     * @return string replaced sql by params
     */
    private function _replaceSqlParams($sql, $params=NULL, $autoValidation=TRUE){
        if($params!==NULL){
            //rsort($params);
            if($autoValidation===TRUE || is_array($autoValidation)){
                // for auto validation
                foreach($params as $curKey=>$curValue){
                    $quotedString = "'".dbStr($curValue)."'";
                    $sql = str_replace("'$curKey'", $quotedString, $sql);
                }
                foreach($params as $curKey=>$curValue){
                    $sql = str_replace($curKey, dbNum($curValue), $sql);
                }
                if(is_array($autoValidation)){
                    // 第3引数による指定手動パラメータ置き換え
                    foreach($autoValidation as $curKey=>$curValue){
                        $sql = str_replace($curKey, $curValue, $sql);
                    }
                }
            }else{
                // 全パラメータ手動置き換え
                foreach($params as $curKey=>$curValue){
                    $sql = str_replace($curKey, $curValue, $sql);
                }
            }
        }
        return $sql;
    }




    /**
     * getRecordInfo
     *
     * !! IMPORTANT !! This function is to be use only from dataset. Shouldn't direct calling !!
     *
     * @param nativeDataset $nativeDataset
     *
     * @return mixed mixed and named array
     */
    public function _getRecordInfo($nativeDataset){
        return $this->_dbDriver->getRecordInfo($nativeDataset);
    }


    /**
     * get database type
     *
     * @return string type of database
     */
    public function type(){
        return $this->_type;
    }


    /**
     * テーブル一覧取得
     *
     * @return NamedArray TABLE_NAME and DESCRIPTION
     */
    public function tables(){
        return $this->_dbDriver->tables();
    }

    /**
     * フィールド一覧取得
     *
     * @param  string $tableName テーブル名
     *
     * @return NamedArray TABLE_NAME and DESCRIPTION
     */
    public function fields($tableName){
        return $this->_dbDriver->fields($tableName);
    }

    /**
     * プライマリキー一覧取得
     *
     * @param  string $tableName テーブル名
     *
     * @return NamedArray FIELD_NAME
     */
    public function primaryKeys($tableName){
        return $this->_dbDriver->primaryKeys($tableName);
    }


    /**
     * SQL取得
     *
     * _sqlにロード済みのSQLを取得・設定する。
     *
     * @param string $sqlId SQLIDを指定。省略すると全SQLを配列で取得
     * @param string $sql   SQLIDに対してSQL文を指定。省略すると取得関数扱い
     *
     * @return mixed SQL文字列、或いは配列( $xxxx[<sqlId>] = 'ANY SQL' )形式。設定失敗時($sqlがあるのに$sqlIdが指定されていない場合)にのみFALSEを返す。
     */
    public function sql($sqlId=NULL, $sql=NULL){
        if($sql===NULL){
            if($sqlId===NULL){
                return $this->_sql;
            }else{
                return $this->_sql[$sqlId];
            }
        }else{
            if($sqlId===NULL){
                return FALSE;
            }else{
                $this->_sql[$sqlId] = $sql;
                return $sql;
            } 
        }
    }


    
    /**
     * デバッグモード制御
     *
     * @param mixed   $debug  TRUE:標準出力, FALSE:デバッグ出力を中止する, その他の値:書き出しファイル名(%h)
     * @param string  $format TRUE:デバッグ出力する, FALSE:デバッグ出力を中止する
     * @param boolean $append TRUE:既存のファイルに追記する, FALSE:内容のハッシュで$debugの%h部分を置き換えたファイル名に書き出す
     * @param boolean $html   TRUE:HTMLタグを出力する, FALSE:テキスト形式で出力する
     */
    public function debug($debug=TRUE, $format='detailed', $append=TRUE, $html=FALSE){
        $this->_debug        = $debug  ;
        $this->_debugFormat  = $format ;
        $this->_debugAppend  = $append ;
        $this->_debugHtml    = $html   ;
    }



    /**
     * 発行済最終SQL取得
     *
     * @return string LastSQL
     **/
    public function lastSql(){
        return $this->_lastSql;
    }



    /**
     * set ReportTo
     *
     * @param string $reportTo
     *
     * @return string ReportTo
     */ 
    public function reportTo($reportTo=NULL){
        if($reportTo===NULL){
            return $this->_reportTo;
        }else{
            return $this->_reportTo = $reportTo;
        }
    }


    /**
     * get error code
     *
     * @return integer ErrorCode
     */
    public function errCode(){
        return $this->_dbDriver->errCode();
    }



    /**
     * get error message
     *
     * @return string ErrorMessage
     */
    public function errMessage(){
        return $this->_dbDriver->errMessage();
    }



    /**
     * check error
     *
     * @return boolean TRUE:ERROR, FALSE:NOT ERROR
     */
    public function errCheck(){
        return $this->_dbDriver->errCheck();
    }

}
?>
