|
Server : Apache System : Linux ruga7-004.fmcity.com 3.10.0-1160.119.1.el7.x86_64 #1 SMP Tue Jun 4 14:43:51 UTC 2024 x86_64 User : tkt_travelbus ( 1137) PHP Version : 7.0.0p1 Disable Function : mysql_pconnect Directory : /tkt_travelbus/www/Lib/ |
Upload File : |
<?php
/**
* Mysql DB class
*/
class DB_mysql
{
var $dbconn_write = null; // db connection resource for WRITE
var $dbconn_read = null; // db connection resource for READ
var $dbinfo = array(); // db connection info
var $schema = array(); // schema
var $currentTarget= DataObject::WRITE_DB; // 지금실행하는 쿼리의 connection대상
var $forceMaster = FALSE; // 강제로 마스터Connection에서 query처리
var $debug = FALSE; // debug flag
var $errorLog = FALSE; // errorlog flag
var $query = ''; // last query
var $errorStack = array(); // last db error
var $charset = '';
// for easy
var $table = '';
var $tableJoin = array();
var $callback = ''; // callback function
var $cacheDir = '/tmp';
var $transEnable = FALSE;
var $transDepth = 0;
var $transBegan = FALSE; // 트랜잭션이 시작되어있는가?
var $_trans_status= TRUE; // 트랜잭션 상태
/**
* constructor
*
* @param string $dsn
* @return void
*/
function __construct($dsn = '', $charset = '', $debug = FALSE, $errorLog = FALSE)
{
$this->charset = $charset;
if ( $dsn && is_array($dsn) ){
if ( $dsn[DataObject::WRITE_DB] && $this->setDsn($dsn[DataObject::WRITE_DB]) ){
$dsn[DataObject::READ_DB] && $this->setDsnForRead($dsn[DataObject::READ_DB]);
$this->connect($this->charset);
}
}
else if ( $dsn && is_string($dsn) && $this->setDsn($dsn) ){
$this->connect($this->charset);
}
$this->setDebug($debug);
$this->setErrorLog($errorLog);
}
/**
* set debug flag
*
* @param boolean $debug
* @return void
*/
function setDebug($debug = true)
{
$this->debug = $debug;
}
/**
* set log flag
*
* @param boolean $log
* @return void
*/
function setErrorLog($errorLog = true)
{
$this->errorLog = $errorLog===TRUE;
}
/**
* set read db connection info from dsn string
*
* @param string $dsn
* @return array
*/
function setDsn($dsn)
{
if (strpos($dsn, 'mysql://') !== 0) {
return false;
}
$info = parse_url($dsn);
$this->dbinfo[DataObject::WRITE_DB] = array();
$this->dbinfo[DataObject::WRITE_DB]['dsn'] = $this->dbinfo['dsn'] = $dsn;
$this->dbinfo[DataObject::WRITE_DB]['dbhost'] = $this->dbinfo['dbhost'] = $info['host'];
$this->dbinfo[DataObject::WRITE_DB]['dbuser'] = $this->dbinfo['dbuser'] = $info['user'];
$this->dbinfo[DataObject::WRITE_DB]['dbpass'] = $this->dbinfo['dbpass'] = $info['pass'];
$this->dbinfo[DataObject::WRITE_DB]['dbname'] = $this->dbinfo['dbname'] = substr($info['path'], 1);
return $this->dbinfo;
}
/**
* set read(slave) db connection info from dsn string
*
* @param string $dsnRead
* @return array
*/
function setDsnForRead($dsnRead)
{
if (strpos($dsnRead, 'mysql://') !== 0) {
return false;
}
$info = parse_url($dsnRead);
$this->dbinfo[DataObject::READ_DB] = array();
$this->dbinfo[DataObject::READ_DB]['dsn'] = $dsnRead;
$this->dbinfo[DataObject::READ_DB]['dbhost'] = $info['host'];
$this->dbinfo[DataObject::READ_DB]['dbuser'] = $info['user'];
$this->dbinfo[DataObject::READ_DB]['dbpass'] = $info['pass'];
$this->dbinfo[DataObject::READ_DB]['dbname'] = substr($info['path'], 1);
return $this->dbinfo;
}
/**
* connect to db
*
* @return boolean
*/
function connect($charset = '')
{
$charset = $charset ? $charset : $this->charset;
if ( !empty($this->dbinfo[DataObject::WRITE_DB]['dbhost']) && !empty($this->dbinfo[DataObject::READ_DB]['dbhost']) && $this->dbinfo[DataObject::WRITE_DB]['dsn']!=$this->dbinfo[DataObject::READ_DB]['dsn'] ){
$read_db_conn_result = FALSE;
$write_db_conn_result = FALSE;
// WRITE(master) DB
if ($this->dbconn_write = @mysql_connect($this->dbinfo[DataObject::WRITE_DB]['dbhost'], $this->dbinfo[DataObject::WRITE_DB]['dbuser'], $this->dbinfo[DataObject::WRITE_DB]['dbpass'])) {
if ($charset && $charset != 'sjis') {
$this->query('SET NAMES ' . $charset, DataObject::WRITE_DB);
}
$this->debug(sprintf("%s DB Connected1 : %s", DataObject::WRITE_DB, $this->dbconn_write));
$write_db_conn_result = $this->selectDB($this->dbinfo['dbname']);
} else {
$this->error(DataObject::WRITE_DB . ' DB Connection Error1', TRUE);
}
// READ(slave) DB
if ($this->dbconn_read = @mysql_connect($this->dbinfo[DataObject::READ_DB]['dbhost'], $this->dbinfo[DataObject::READ_DB]['dbuser'], $this->dbinfo[DataObject::READ_DB]['dbpass'])) {
if ($charset && $charset != 'sjis') {
$this->query('SET NAMES ' . $charset, DataObject::READ_DB);
}
$read_db_conn_result = TRUE;
$this->debug(sprintf("%s DB Connected2 : %s", DataObject::READ_DB, $this->dbconn_read));
} else {
$this->error(DataObject::READ_DB . ' DB Connection Error2', TRUE);
}
if ( $write_db_conn_result && $read_db_conn_result ){
return $this->selectDB($this->dbinfo['dbname']);
}
}
else if ( !empty($this->dbinfo[DataObject::WRITE_DB]['dbhost']) && !empty($this->dbinfo[DataObject::READ_DB]['dbhost']) && $this->dbinfo[DataObject::WRITE_DB]['dsn']==$this->dbinfo[DataObject::READ_DB]['dsn'] ){
$db_conn_result = FALSE;
// WRITE(master) DB
if ($this->dbconn_write = @mysql_connect($this->dbinfo[DataObject::WRITE_DB]['dbhost'], $this->dbinfo[DataObject::WRITE_DB]['dbuser'], $this->dbinfo[DataObject::WRITE_DB]['dbpass'])) {
if ($charset && $charset != 'sjis') {
$this->query('SET NAMES ' . $charset, DataObject::WRITE_DB);
}
$this->debug(sprintf("%s DB Connected3 : %s", DataObject::WRITE_DB, $this->dbconn_write));
$db_conn_result = $this->selectDB($this->dbinfo['dbname']);
} else {
$this->error(DataObject::WRITE_DB . ' DB Connection Error3', TRUE);
}
if ( $db_conn_result ){
$this->dbconn_read = &$this->dbconn_write;
$this->debug(sprintf("%s DB Connected4 : %s", DataObject::READ_DB, $this->dbconn_read));
return $this->selectDB($this->dbinfo['dbname']);
}
}
else{
if ($this->dbconn_write = @mysql_connect($this->dbinfo['dbhost'], $this->dbinfo['dbuser'], $this->dbinfo['dbpass'])) {
$this->dbconn_read = &$this->dbconn_write;
$this->debug(sprintf("%s DB Connected5 : %s", DataObject::WRITE_DB, $this->dbconn_write));
$this->debug(sprintf("%s DB Connected6 : %s", DataObject::READ_DB, $this->dbconn_read));
if ($charset && $charset != 'sjis') {
$this->query('SET NAMES ' . $charset, DataObject::WRITE_DB);
}
return $this->selectDB($this->dbinfo['dbname']);
} else {
$this->error(DataObject::WRITE_DB . 'DB Connection Error4', TRUE);
return false;
}
}
}
/**
* select db
*
* @param string $dbname
* @return boolean
*/
function selectDB($dbname)
{
if (@mysql_select_db($dbname, $this->dbconn_write)) {
$this->dbname = $dbname;
if ( $this->dbconn_read && $this->dbconn_read!=$this->dbconn_write ){
if ( @mysql_select_db($dbname, $this->dbconn_read) ){
return TRUE;
}else{
$this->error(DataObject::READ_DB.' DB Selection Error : '.$dbname, TRUE);
return false;
}
}
return true;
} else {
$this->error(DataObject::WRITE_DB.' DB Selection Error : '.$dbname, TRUE);
return false;
}
}
/**
* close db connection
*
* @return boolean
*/
function close()
{
if (@mysql_close($this->dbconn_write)) {
$this->dbconn_write = null;
if ( $this->dbconn_read && $this->dbconn_read!=$this->dbconn_write ){
if (@mysql_close($this->dbconn_read)) {
$this->dbconn_read = null;
return true;
}
}
return true;
} else {
return false;
}
}
/**
* check db connection & reconnect
*/
function reConnect() {
if(!@mysql_ping($this->dbconn_write) || ($this->dbconn_read!=NULL && $this->dbconn_read!=$this->dbconn_write && !@mysql_ping($this->dbconn_read)) ) {
$this->close();
$this->connect();
}
}
/**
* Begin Transaction
*
* @access public
* @return bool
*/
function transBegin()
{
if ( !$this->transEnable ){
return TRUE;
}
$this->transDepth++;
$this->debug('TRANSACTION BEGIN('.$this->transDepth.')');
$this->transBegan = TRUE;
if ( $this->transDepth > 1 ){
return TRUE;
}
$this->query('SET AUTOCOMMIT=0;', DataObject::WRITE_DB);
$this->query('START TRANSACTION;', DataObject::WRITE_DB); // can also be BEGIN or BEGIN WORK
return TRUE;
}
/**
* need to transaction complete?
*
* @access public
* @return bool
*/
function needToTransComplete()
{
return $this->transEnable && $this->transBegan;
}
/**
* Commit Transaction
*
* @access public
* @return bool
*/
function transCommit()
{
if ( !$this->transEnable ){
return TRUE;
}
$this->debug('TRANSACTION COMMIT('.$this->transDepth.')');
if ( $this->transDepth > 1 ){
$this->transDepth--;
return TRUE;
}
// depth restore
$this->transDepth--;
$this->query('COMMIT;', DataObject::WRITE_DB);
$this->query('SET AUTOCOMMIT=1;', DataObject::WRITE_DB);
$this->transBegan = FALSE;
return TRUE;
}
/**
* Rollback Transaction
*
* @access public
* @return bool
*/
function transRollback()
{
if ( !$this->transEnable ){
return TRUE;
}
$this->debug('TRANSACTION ROLLBACK('.$this->transDepth.')');
if ( $this->transDepth > 1 ){
$this->transDepth--;
return TRUE;
}
// depth restore
$this->transDepth--;
$this->query('ROLLBACK;', DataObject::WRITE_DB);
$this->query('SET AUTOCOMMIT=1;', DataObject::WRITE_DB);
$this->transBegan = FALSE;
return TRUE;
}
/**
* 자동처리 트랜잭션 완료
* transaction start
*
* @access public
* @param callable $callbackOrForceChangeTransStatus
* @param mixed params(optional)
* @return boolean
*/
function transComplete($callbackOrForceChangeTransStatus=NULL)
{
$callback_trans_status = TRUE;
if ( $callbackOrForceChangeTransStatus!=NULL && is_callable($callbackOrForceChangeTransStatus) ){
$args = func_get_args();
array_shift($args); //첫번째 인자를 제거한다
$callback_trans_status = call_user_func_array($callbackOrForceChangeTransStatus, $args);
}else if ( is_bool($callbackOrForceChangeTransStatus) ){
$callback_trans_status = $callbackOrForceChangeTransStatus;
}
if ( ! $this->transEnable ){
return $callback_trans_status;
}
// 콜백 결과와 쿼리 처리 결과가 모두 TRUE일때만 TRUE!
$this->_trans_status = $this->_trans_status && $callback_trans_status;
// The query() function will set this flag to FALSE in the event that a query failed
if ($this->_trans_status === FALSE)
{
//$this->_trans_status = TRUE;
$this->transRollback();
return FALSE;
}
$this->transCommit();
return TRUE;
}
/**
* 트랜잭션 사용여부
* set transaction enabling
*
* @access public
* @param boolean $transEnable
* @return boolean
*/
function transEnable( $transEnable = NULL )
{
if ( is_bool($transEnable) ){
$this->transEnable = $transEnable;
if ( !$this->transEnable ){
$this->query('SET AUTOCOMMIT=1;', DataObject::WRITE_DB);
}
}
return $this->transEnable;
}
/**
* 트랜잭션 상태반환
* return transaction status
*
* @access public
* @return boolean
*/
function transStatus()
{
return $this->_trans_status;
}
/**
* execute query
*
* @param string $sql
* @return resource
*/
function query($sql, $targetDb = NULL)
{
$this->query = $sql;
if ($this->debug) {
$process_start_time = round(microtime(true),4);
}
$dbconn = $this->dbconn_write;
$this->currentTarget = DataObject::WRITE_DB;
if ( $targetDb === NULL && $this->forceTarget === NULL ){
$dbconn = $this->dbconn_write;
$targetDb = DataObject::WRITE_DB;
$this->currentTarget = DataObject::WRITE_DB;
if ( !$this->transBegan && $this->dbconn_read && $this->_getTargetDb($this->query) == DataObject::READ_DB ){
$targetDb = DataObject::READ_DB;
$dbconn = $this->dbconn_read;
$this->currentTarget = DataObject::READ_DB;
}
}else if ( $this->forceMaster == TRUE ){
}else{
if ( $targetDb === DataObject::READ_DB && $this->dbconn_read ){
$dbconn = $this->dbconn_read;
$this->currentTarget = DataObject::READ_DB;
}else{
$targetDb = DataObject::WRITE_DB;
}
}
$this->reConnect();
$r = @mysql_query($sql, $dbconn);
// if ( $this->errorLog ){
// $log = $this->transEnable ? '[TRANSACTION ENABLED] ' : '';
// $log .= $sql;
// $this->errorLog($log);
// }
if ($this->debug) {
$process_end_time = round(microtime(true),4);
$process_doing_time = substr(($process_end_time - $process_start_time),0,5);
$this->debug($targetDb.' Query'.($this->transBegan?' in Transaction':'').' : /*'.$process_doing_time.'sec*/ ' . $this->query);
}
if ($r) {
return $r;
} else {
$err_msg = $targetDb.' Query Error'.($this->transBegan?' in Transaction':'').' : '.mysql_error($dbconn);
//if ( $this->debug ){
$err_msg .= ' ===> '.$this->query;
//}
$this->error($err_msg);
$this->_trans_status = FALSE;
return false;
}
}
function _getTargetDb($sql)
{
$readDbCommands = array('SELECT', 'SHOW', 'EXPLAIN', 'DESC', 'SET');
$writeDbCommands = array('INSERT', 'UPDATE', 'DELETE', 'START TRANSACTION', 'BEGIN', 'COMMIT', 'ROLLBACK', 'SET AUTOCOMMIT', 'DROP', 'ALTER TABLE', 'TRUNCATE');
// http://stackoverflow.com/questions/7100127/regex-to-match-mysql-comments
$sqlComments = '@(([\'"`]).*?[^\\\]\2)|((?:\#|--).*?$|/\*(?:[^/*]|/(?!\*)|\*(?!/)|(?R))*\*\/)\s*|(?<=;)\s+@ms';
/* Commented version
$sqlComments = '@
(([\'"`]).*?[^\\\]\2) # $1 : Skip single & double quoted + backticked expressions
|( # $3 : Match comments
(?:\#|--).*?$ # - Single line comments
| # - Multi line (nested) comments
/\* # . comment open marker
(?: [^/*] # . non comment-marker characters
|/(?!\*) # . ! not a comment open
|\*(?!/) # . ! not a comment close
|(?R) # . recursive case
)* # . repeat eventually
\*\/ # . comment close marker
)\s* # Trim after comments
|(?<=;)\s+ # Trim after semi-colon
@msx';
*/
$uncommentedSQL = trim( preg_replace( $sqlComments, '$1', trim($sql) ) );
$uncommentedSQL = preg_replace("/[\s]+/", ' ', $uncommentedSQL);
//$commands = preg_split("/[\s]+/", trim($uncommentedSQL));
//echo $uncommentedSQL."<br>";
foreach ( $writeDbCommands as $cmd ){
if ( preg_match('/^'.$cmd.'/', strtoupper($uncommentedSQL)) ){
return DataObject::WRITE_DB;
}
}
return DataObject::READ_DB;
}
function setForceMasterConnection()
{
}
/**
* row에 콜백 적용
*
* @param array $row
* @param mixed $callback
* @return array
*/
function _applyCallback($row, $callback){
if ( $row && $callback && is_array($callback) && !method_exists($callback[0], $callback[1]) ){
if ( is_array($callback) ){
foreach ( $callback as $item_of_callback ){
if ( is_callable($item_of_callback, false) ){
$row = call_user_func($item_of_callback, $row);
}
}
}
return $row;
}elseif ( $row && $callback && is_callable($callback, false) ){
return call_user_func($callback, $row);
}else{
return $row;
}
}
/**
* execute query and return fetched result
*
* @param string $sql
* @param mixed $callback
* @param string $targetDb(optional)
* @return array
*/
function fetch($sql, $callback = '', $targetDb = NULL)
{
if ($r = $this->query($sql, $targetDb)) {
$row = @mysql_fetch_assoc($r);
$this->debug($row);
return $this->_applyCallback($row, $callback);
} else {
$this->_trans_status = FALSE;
return FALSE;
}
}
/**
* execute query and return data array
*
* @param string $sql
* @param string $callback
* @return array
*/
function data($sql, $callback = '', $targetDb = NULL)
{
if ($r = $this->query($sql, $targetDb)) {
$data = array();
while ($row = @mysql_fetch_assoc($r)) {
$data[] = $this->_applyCallback($row, $callback);
}
$this->debug($data);
return $data;
} else {
$this->_trans_status = FALSE;
return FALSE;
}
}
/**
* execute query and return single column
*
* @param string $sql
* @param int $row
* @param int $col
* @param int $targetDb
* @return array
*/
function result($sql, $row = 0, $col = 0, $targetDb = NULL)
{
if ($r = $this->query($sql, $targetDb)) {
$result = @mysql_result($r, $row, $col);
$this->debug($result);
return $result;
} else {
$this->_trans_status = FALSE;
return FALSE;
}
}
/**
* get last inserted id
*
* @return int
*/
function getInsertId($new = false)
{
if ($new) {
$sql = "SHOW CREATE TABLE " . array_shift(explode(' ', $this->table));
$tmp = $this->fetch($sql);
if (preg_match('/AUTO_INCREMENT=([0-9]+)/i', $tmp['Create Table'], $match)) {
return intval($match[1]);
} else {
return 1;
}
} else {
return mysql_insert_id($this->dbconn_write);
}
}
/**
* error
*
* @param string $msg
* @return void
*/
function error($msg, $force=FALSE)
{
$this->errorLog($msg);
$this->errorStack[] = '오류가 발생했습니다.';
// 트랜잭션을 사용하지 않으면 에러 바로 출력
if ( !$this->transEnable || $force ){
$msg = implode("\n", $this->errorStack);
$this->errorStack = array();
trigger_error($msg, E_USER_ERROR);
}
}
/**
* debug
*
* @param string $msg
* @return void
*/
function debug($msg)
{
//trigger_error((string)$msg, E_USER_NOTICE);
if ( $this->debug ){
debug($msg);
}
}
/**
* errorLog
*
* @param string $msg
* @return void
*/
function errorLog($msg)
{
if ( $this->errorLog ){
Log::save(str_replace(' ===> ', PHP_EOL, $msg), '', 'database', 1);
}
}
///////////////////////////////////////////////////////////////////////////////////////////////////
// for easy
/**
* set current table
*
* @param string $table
* @return void
*/
function setTable($table)
{
$this->initJoin();
$this->table = $table;
}
/**
* init table join
*
* @return void
*/
function initJoin()
{
$this->tableJoin = array();
}
/**
* add table join
*
* @param string $table
* @param string $joinType
* @param string $on join condition
* @return void
*/
function addJoin($table, $joinType, $on)
{
$this->tableJoin[] = $joinType . ' JOIN ' . $table . ' ON ' . $on;
}
/**
* set callback function
*
* @param string $callback
* @param string $tablename
* @param string $read_or_write read|write
* @return boolean
*/
function setCallback($callback, $read_or_write, $table=NULL)
{
if (is_callable($callback, false)) {
if ( $table==NULL ) $table = $this->table;
if ( !$table ){
$this->debug('Invalid Table');
return false;
}
if ( !isset($this->callback[$table]) ){
$this->callback[$table] = array();
}
$read_or_write = strtolower($read_or_write);
if ( in_array($read_or_write, array('read','write')) ){
$this->callback[$table][$read_or_write] = $callback;
return true;
}
$this->debug("Invalid 'read_or_write' argument");
return false;
} else {
$this->debug('Invalid Callback');
return false;
}
}
/**
* get callback function
* @param string $read_or_write read|write
* @param string $tablename(optional)
*
* @return string
*/
function getCallback($read_or_write, $table=NULL)
{
if ( $table==NULL ) $table = $this->table;
if ( !$table ){
$this->debug('Invalid Table');
return false;
}
$read_or_write = strtolower($read_or_write);
list($table, $tmp) = explode(" ", $table." ", 2);
if ( $this->callback[$table] ){
return $this->callback[$table][$read_or_write];
}
return null;
}
/**
* reset callback function
* @param string $read_or_write read|write
* @param string $tablename(optional)
*
* @return string
*/
function resetCallback($read_or_write, $table=NULL)
{
if ( $table==NULL ) $table = $this->table;
if ( !$table ){
$this->debug('Invalid Table');
return false;
}
if ( $this->callback[$table][$read_or_write] ){
unset($this->callback[$table][$read_or_write]);
}
if ( count($this->callback[$table][$read_or_write])==0 ){
unset($this->callback[$table]);
}
}
/**
* set cache directory
*
* @param string $cacheDir
* @return void
*/
function setCacheDir($cacheDir)
{
if (!is_dir($cacheDir)) {
@mkdir($cacheDir);
@chmod($cacheDir, 0707);
}
if (!is_writable($cacheDir)) {
$this->error('Invalid Cache Directory : ' . $cacheDir, TRUE);
}
$this->cacheDir = $cacheDir;
}
/**
* get last executed query
*
* @return string
*/
function getQuery()
{
return $this->query;
}
/**
* execute insert query with data array
*
* @param array $data
* @param boolean $filter
* @param boolean $strict
* @return resource
*/
function insert($data, $filter = false, $strict = false)
{
if (!$this->table) {
$this->error('No Table Name');
}
// callback
$callback = $this->getCallback('write', $this->table);
if ($callback) {
$data = call_user_func($callback, $data);
}
if ($data) {
$sql = $this->_getInsertQuery($data, $filter, $strict);
if ( $sql == false ){
return false;
}
return $this->query($sql, DataObject::WRITE_DB);
} else {
return false;
}
}
/**
* execute update query with data array
*
* @param array $data
* @param string $where
* @param boolean $filter
* @param boolean $strict
* @return resource
*/
function update($data, $where, $filter = false, $strict = false)
{
if (!$this->table) {
$this->error('No Table Name');
}
// callback
$callback = $this->getCallback('write', $this->table);
if ($callback) {
$data = call_user_func($callback, $data);
}
if ($data) {
$sql = $this->_getUpdateQuery($data, $where, $filter, $strict);
if ( $sql == false ){
return false;
}
return $this->query($sql, DataObject::WRITE_DB);
} else {
return false;
}
}
/**
* execute delete query
*
* @param string $where
* @return resource
*/
function delete($where)
{
if (!$this->table) {
$this->error('No Table Name');
}
$sql = 'DELETE FROM ' . array_shift(explode(' ', $this->table)) . ' WHERE ' . $where;
return $this->query($sql, DataObject::WRITE_DB);
}
/**
* execute select query
*
* @param string $where
* @param string $column
* @param mixed $limit
* @param string $order
* @param string $group
* @return array
*/
function select($where = '', $column = '*', $limit = '', $order = '', $group = '', $targetDb = NULL)
{
if (!$this->table) {
$this->error('No Table Name');
}
$callback = $this->getCallback('read', $this->table);
if ($this->tableJoin) {
$_callbacks = array();
if ( $callback ){
$_callbacks[$this->table] = $callback;
}
foreach ( $this->tableJoin as $item_of_table ){
if ( preg_match("/.+ [jJ][oO][iI][nN] ([^ ]+)(([aA][sS])? .+)* ON /", $item_of_table, $match) ){
if ( $match[1] && $cb=$this->getCallback('read', trim($match[1])) ){
$_callbacks[trim($match[1])] = $cb;
}
}
}
if ( count($_callbacks)>0 ){
$callback = $_callbacks;
}
}
$sql = 'SELECT ' . $column . ' FROM ' . $this->table;
if ($this->tableJoin) {
$sql .= ' ' . implode(' ', $this->tableJoin);
$this->initJoin();
}
if ($where) {
$sql .= ' WHERE ' . $where;
}
if ($group) {
$sql .= ' GROUP BY ' . $group;
}
if ($order) {
$sql .= ' ORDER BY ' . $order;
}
if ($limit) {
$sql .= ' LIMIT ' . $limit;
}
if ($limit === 1 || $limit === "1") {
return $this->fetch($sql, $callback, $targetDb);
} else {
return $this->data($sql, $callback, $targetDb);
}
}
/**
* execute count query
*
* @param string $where
* @param string $column
* @return int
*/
function getCount($where = '', $column = '*', $ignoreJoin = false, $targetDb = NULL)
{
$sql = 'SELECT COUNT(' . $column .') FROM ' . $this->table;
if (!$ignoreJoin && $this->tableJoin) {
$sql .= ' ' . implode(' ', $this->tableJoin);
// DO NOT init join
}
if ($where) {
$sql .= ' WHERE ' . $where;
}
return $this->result($sql, 0, 0, $targetDb);
}
/**
* execute select query for list
*
* @param string $where
* @param string $column
* @param int $page
* @param mixed $limit
* @param string $order
* @param string $group
* @return array
*/
function getList($where = '', $column = '*', $page = 1, $limit = 20, $order = '', $group = '', $targetDb = NULL)
{
$offset = max(0, ($page - 1) * $limit);
$limit = $offset . ', ' . $limit;
return $this->select($where, $column, $limit, $order, $group, $targetDb);
}
/**
* execute select query for signle record
*
* @param string $where
* @param string $column
* @param string $order
* @param string $group
* @return array
*/
//function getOne($where = '', $column = '*', $order = '', $group = '')
//{
// return $this->select($where, $column, 1, $order, $group);
//}
/**
* get schema of table
*
* @param boolean $force
* @return array
*/
function getSchema($force = false)
{
if (!$this->table) {
$this->error('No Table Name');
}
if ($this->debug) {
$force = true;
}
$cacheFile = $this->cacheDir . '/' . md5($this->dbinfo['dbname'] . '.' . $this->table);
$flag = false;
if (!$force) {
if (!empty($this->schema[$this->table])) {
$flag = true;
} else {
if (is_readable($cacheFile) && is_writable($cacheFile)) {
$schema = unserialize(base64_decode(file_get_contents($cacheFile)));
if ($schema) {
$this->schema[$this->table] = $schema;
$flag = true;
}
}
}
}
if (!$flag) {
$sql = 'SHOW COLUMNS FROM ' . array_shift(explode(' ', $this->table));
$r = $this->query($sql);
if ( $r ){
$schema = array();
while ($row = mysql_fetch_assoc($r)) {
$schema[$row['Field']] = $row;
}
if ($schema) {
$this->schema[$this->table] = $schema;
if (!is_dir($this->cacheDir)) {
@mkdir($this->cacheDir);
@chmod($this->cacheDir, 0707);
}
file_put_contents($cacheFile, base64_encode(serialize($schema)));
@chmod($cacheFile, 0606);
$flag = true;
}
}else{
$error_msg = '';
if ( $this->currentTarget == DataObject::READ_DB ){
$error_msg = mysql_error($this->dbconn_read);
}else{
$error_msg = mysql_error($this->dbconn_write);
}
$err_msg = 'Schema Error : '.$error_msg;
$this->error($err_msg);
return FALSE;
}
}
return $this->schema[$this->table];
}
/**
* filter data array with table schema
*
* @param array $data
* @param boolean $strict
* @return array
*/
function filter($data, $strict = false)
{
$schema = $this->getSchema();
if ( $schema != FALSE ){
$data = array_intersect_key($data, $schema);
if ($strict) {
foreach ($data as $key=>$val) {
@list($type, $length, $etc) = preg_split('/[\(\) ]+/', $schema[$key]['Type']);
if (preg_match('/(int|float|double)$/', $type)) {
if (!is_numeric($val)) {
$this->debug('Invalid data type for ' . $key . '(' . $type . ') : ' . $val);
}
} else {
if ($length && strlen($val) > $length) {
$this->debug('Invalid data length for ' . $key . '(' . $length . ') : ' . $val);
}
}
}
}
return $data;
}
return FALSE;
}
/**
* make insert query
*
* @param array $data
* @param boolean $filter
* @param boolean $strict
* @return string
*/
function _getInsertQuery($data, $filter = false, $strict = false)
{
if ($filter) {
$data = $this->filter($data, $strict);
if ( $data === FALSE ){
return FALSE;
}
}
$data = $this->_handleQuote($data);
$column = implode(', ', array_keys($data));
$value = "'" . implode("', '", array_values($data)) . "'";
$sql = 'INSERT INTO ' . array_shift(explode(' ', $this->table)) . ' (' . $column . ') VALUES (' . $value . ')';
return $sql;
}
/**
* make update query
*
* @param array $data
* @param boolean $filter
* @param boolean $strict
* @return string
*/
function _getUpdateQuery($data, $where, $filter = false, $strict = false)
{
if ($filter) {
$data = $this->filter($data, $strict);
if ( $data === FALSE ){
return FALSE;
}
}
$data = $this->_handleQuote($data);
$item = array();
foreach ($data as $key=>$val) {
$item[$key] = $key." = '".$val."'";
}
$sql = 'UPDATE ' . $this->table . ' SET ' . implode(', ', $item) . ' WHERE ' . $where;
return $sql;
}
/**
* php magic quote safe process
*
* @param array $data
* @return array
*/
function _handleQuote($data)
{
foreach ($data as $key=>$val) {
//$data[$key] = str_replace("'", "''", $val);
if ( is_string($val) || is_numeric($val) ){
$data[$key] = mysql_real_escape_string($val);
}
}
return $data;
}
///////////////////////////////////////////////////////////////////////////////////////////////////
// alias
/**
* alias of query()
*/
function execute($sql)
{
return $this->query($sql);
}
/**
* alias of fetch()
*/
function getRow($sql, $callback = '', $targetDb = NULL)
{
return $this->fetch($sql, $callback, $targetDb);
}
function getOne($sql, $targetDb = NULL)
{
return $this->fetch($sql, '', $targetDb);
}
/**
* alias of data()
*/
function getAll($sql, $callback = '', $targetDb)
{
return $this->data($sql, $callback, $targetDb);
}
function SetFetchMode()
{
}
function selectLimit($sql, $limit, $offset = 0)
{
if ($offset) {
$sql .= ' LIMIT ' . $offset . ', ' . $limit;
} else {
$sql .= ' LIMIT ' . $limit;
}
return $this->data($sql, $targetDb);
}
} // class