COUNT 로그 캐시하기
참고 : www.xpressengine.com/forum/20712872
C:\APM_Setup\htdocs\classes\db\DBMysql.class.php
<?php /* Copyright (C) NAVER <http://www.navercorp.com> */ /** * Class to use MySQL DBMS * mysql handling class * * Does not use prepared statements, since mysql driver does not support them * * @author NAVER (developers@xpressengine.com) * @package /classes/db * @version 0.1 */ class DBMysql extends DB { /** * prefix of a tablename (One or more XEs can be installed in a single DB) * @var string */ var $prefix = 'xe_'; // / < var $comment_syntax = '/* %s */'; /** * Column type used in MySQL * * Becasue a common column type in schema/query xml is used for colum_type, * it should be replaced properly for each DBMS * @var array */ var $column_type = array( 'bignumber' => 'bigint', 'number' => 'bigint', 'varchar' => 'varchar', 'char' => 'char', 'text' => 'text', 'bigtext' => 'longtext', 'date' => 'varchar(14)', 'float' => 'float', ); /** * Constructor * @return void */ function DBMysql() { $this->_setDBInfo(); $this->_connect(); } /** * Create an instance of this class * @return DBMysql return DBMysql object instance */ function create() { return new DBMysql; } /** * DB Connect * this method is private * @param array $connection connection's value is db_hostname, db_port, db_database, db_userid, db_password * @return resource */ function __connect($connection) { // Ignore if no DB information exists if(strpos($connection["db_hostname"], ':') === false && $connection["db_port"]) { $connection["db_hostname"] .= ':' . $connection["db_port"]; } // Attempt to connect $result = @mysql_connect($connection["db_hostname"], $connection["db_userid"], $connection["db_password"]); if(!$result) { exit('XE cannot connect to DB.'); } if(mysql_error()) { $this->setError(mysql_errno(), mysql_error()); return; } // Error appears if the version is lower than 4.1 if(version_compare(mysql_get_server_info($result), '4.1', '<')) { $this->setError(-1, 'XE cannot be installed under the version of mysql 4.1. Current mysql version is ' . mysql_get_server_info()); return; } // select db @mysql_select_db($connection["db_database"], $result); if(mysql_error()) { $this->setError(mysql_errno(), mysql_error()); return; } return $result; } /** * If have a task after connection, add a taks in this method * this method is private * @param resource $connection * @return void */ function _afterConnect($connection) { // Set utf8 if a database is MySQL $this->_query("set names 'utf8'", $connection); } /** * DB disconnection * this method is private * @param resource $connection * @return void */ function _close($connection) { @mysql_close($connection); } /** * Handles quatation of the string variables from the query * @param string $string * @return string */ function addQuotes($string) { if(version_compare(PHP_VERSION, "5.9.0", "<") && get_magic_quotes_gpc()) { $string = stripslashes(str_replace("\\", "\\\\", $string)); } if(!is_numeric($string)) { $string = @mysql_real_escape_string($string); } return $string; } /** * DB transaction start * this method is private * @return boolean */ function _begin() { return true; } /** * DB transaction rollback * this method is private * @return boolean */ function _rollback() { return true; } /** * DB transaction commit * this method is private * @return boolean */ function _commit() { return true; } /** * Execute the query * this method is private * @param string $query * @param resource $connection * @return resource */ function __query($query, $connection) { if(!$connection) { exit('XE cannot handle DB connection.'); } // Run the query statement $result = mysql_query($query, $connection); // Error Check if(mysql_error($connection)) { $this->setError(mysql_errno($connection), mysql_error($connection)); } // Return result return $result; } /** * Fetch the result * @param resource $result * @param int|NULL $arrayIndexEndValue * @return array */ function _fetch($result, $arrayIndexEndValue = NULL) { $output = array(); if(!$this->isConnected() || $this->isError() || !$result) { return $output; } while($tmp = $this->db_fetch_object($result)) { if($arrayIndexEndValue) { $output[$arrayIndexEndValue--] = $tmp; } else { $output[] = $tmp; } } if(count($output) == 1) { if(isset($arrayIndexEndValue)) { return $output; } else { return $output[0]; } } $this->db_free_result($result); return $output; } /** * Return the sequence value incremented by 1 * Auto_increment column only used in the sequence table * @return int */ function getNextSequence() { $query = sprintf("insert into `%ssequence` (seq) values ('0')", $this->prefix); $this->_query($query); $sequence = $this->db_insert_id(); if($sequence % 10000 == 0) { $query = sprintf("delete from `%ssequence` where seq < %d", $this->prefix, $sequence); $this->_query($query); } return $sequence; } /** * Function to obtain mysql old password(mysql only) * @param string $password input password * @param string $saved_password saved password in DBMS * @return boolean */ function isValidOldPassword($password, $saved_password) { $query = sprintf("select password('%s') as password, old_password('%s') as old_password", $this->addQuotes($password), $this->addQuotes($password)); $result = $this->_query($query); $tmp = $this->_fetch($result); if($tmp->password == $saved_password || $tmp->old_password == $saved_password) { return true; } return false; } /** * Check a table exists status * @param string $target_name * @return boolean */ function isTableExists($target_name) { $query = sprintf("show tables like '%s%s'", $this->prefix, $this->addQuotes($target_name)); $result = $this->_query($query); $tmp = $this->_fetch($result); if(!$tmp) { return false; } return true; } /** * Add a column to the table * @param string $table_name table name * @param string $column_name column name * @param string $type column type, default value is 'number' * @param int $size column size * @param string|int $default default value * @param boolean $notnull not null status, default value is false * @return void */ function addColumn($table_name, $column_name, $type = 'number', $size = '', $default = '', $notnull = false) { $type = $this->column_type[$type]; if(strtoupper($type) == 'INTEGER') { $size = ''; } $query = sprintf("alter table `%s%s` add `%s` ", $this->prefix, $table_name, $column_name); if($size) { $query .= sprintf(" %s(%s) ", $type, $size); } else { $query .= sprintf(" %s ", $type); } if($default) { $query .= sprintf(" default '%s' ", $default); } if($notnull) { $query .= " not null "; } return $this->_query($query); } /** * Drop a column from the table * @param string $table_name table name * @param string $column_name column name * @return void */ function dropColumn($table_name, $column_name) { $query = sprintf("alter table `%s%s` drop `%s` ", $this->prefix, $table_name, $column_name); $this->_query($query); } /** * Check column exist status of the table * @param string $table_name table name * @param string $column_name column name * @return boolean */ function isColumnExists($table_name, $column_name) { $query = sprintf("show fields from `%s%s`", $this->prefix, $table_name); $result = $this->_query($query); if($this->isError()) { return; } $output = $this->_fetch($result); if($output) { $column_name = strtolower($column_name); foreach($output as $key => $val) { $name = strtolower($val->Field); if($column_name == $name) { return true; } } } return false; } /** * Add an index to the table * $target_columns = array(col1, col2) * $is_unique? unique : none * @param string $table_name table name * @param string $index_name index name * @param string|array $target_columns target column or columns * @param boolean $is_unique * @return void */ function addIndex($table_name, $index_name, $target_columns, $is_unique = false) { if(!is_array($target_columns)) { $target_columns = array($target_columns); } $query = sprintf("alter table `%s%s` add %s index `%s` (%s);", $this->prefix, $table_name, $is_unique ? 'unique' : '', $index_name, implode(',', $target_columns)); $this->_query($query); } /** * Drop an index from the table * @param string $table_name table name * @param string $index_name index name * @param boolean $is_unique * @return void */ function dropIndex($table_name, $index_name, $is_unique = false) { $query = sprintf("alter table `%s%s` drop index `%s`", $this->prefix, $table_name, $index_name); $this->_query($query); } /** * Check index status of the table * @param string $table_name table name * @param string $index_name index name * @return boolean */ function isIndexExists($table_name, $index_name) { //$query = sprintf("show indexes from %s%s where key_name = '%s' ", $this->prefix, $table_name, $index_name); $query = sprintf("show indexes from `%s%s`", $this->prefix, $table_name); $result = $this->_query($query); if($this->isError()) { return; } $output = $this->_fetch($result); if(!$output) { return; } if(!is_array($output)) { $output = array($output); } for($i = 0; $i < count($output); $i++) { if($output[$i]->Key_name == $index_name) { return true; } } return false; } /** * Creates a table by using xml contents * @param string $xml_doc xml schema contents * @return void|object */ function createTableByXml($xml_doc) { return $this->_createTable($xml_doc); } /** * Creates a table by using xml file path * @param string $file_name xml schema file path * @return void|object */ function createTableByXmlFile($file_name) { if(!file_exists($file_name)) { return; } // read xml file $buff = FileHandler::readFile($file_name); return $this->_createTable($buff); } /** * Create table by using the schema xml * * type : number, varchar, tinytext, text, bigtext, char, date, \n * opt : notnull, default, size\n * index : primary key, index, unique\n * @param string $xml_doc xml schema contents * @return void|object */ function _createTable($xml_doc) { // xml parsing $oXml = new XmlParser(); $xml_obj = $oXml->parse($xml_doc); // Create a table schema $table_name = $xml_obj->table->attrs->name; if($this->isTableExists($table_name)) { return; } $table_name = $this->prefix . $table_name; if(!is_array($xml_obj->table->column)) { $columns[] = $xml_obj->table->column; } else { $columns = $xml_obj->table->column; } $primary_list = array(); $unique_list = array(); $index_list = array(); foreach($columns as $column) { $name = $column->attrs->name; $type = $column->attrs->type; $size = $column->attrs->size; $notnull = $column->attrs->notnull; $primary_key = $column->attrs->primary_key; $index = $column->attrs->index; $unique = $column->attrs->unique; $default = $column->attrs->default; $auto_increment = $column->attrs->auto_increment; $column_schema[] = sprintf('`%s` %s%s %s %s %s', $name, $this->column_type[$type], $size ? '(' . $size . ')' : '', isset($default) ? "default '" . $default . "'" : '', $notnull ? 'not null' : '', $auto_increment ? 'auto_increment' : ''); if($primary_key) { $primary_list[] = $name; } else if($unique) { $unique_list[$unique][] = $name; } else if($index) { $index_list[$index][] = $name; } } if(count($primary_list)) { $column_schema[] = sprintf("primary key (%s)", '`' . implode($primary_list, '`,`') . '`'); } if(count($unique_list)) { foreach($unique_list as $key => $val) { $column_schema[] = sprintf("unique %s (%s)", $key, '`' . implode($val, '`,`') . '`'); } } if(count($index_list)) { foreach($index_list as $key => $val) { $column_schema[] = sprintf("index %s (%s)", $key, '`' . implode($val, '`,`') . '`'); } } $schema = sprintf('create table `%s` (%s%s) %s;', $this->addQuotes($table_name), "\n", implode($column_schema, ",\n"), "ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci"); $output = $this->_query($schema); if(!$output) return false; } /** * Handles insertAct * @param Object $queryObject * @param boolean $with_values * @return resource */ function _executeInsertAct($queryObject, $with_values = true) { $query = $this->getInsertSql($queryObject, $with_values, true); $query .= (__DEBUG_QUERY__ & 1 && $this->query_id) ? sprintf(' ' . $this->comment_syntax, $this->query_id) : ''; if(is_a($query, 'Object')) { return; } return $this->_query($query); } /** * Handles updateAct * @param Object $queryObject * @param boolean $with_values * @return resource */ function _executeUpdateAct($queryObject, $with_values = true) { $query = $this->getUpdateSql($queryObject, $with_values, true); if(is_a($query, 'Object')) { if(!$query->toBool()) return $query; else return; } $query .= (__DEBUG_QUERY__ & 1 && $this->query_id) ? sprintf(' ' . $this->comment_syntax, $this->query_id) : ''; return $this->_query($query); } /** * Handles deleteAct * @param Object $queryObject * @param boolean $with_values * @return resource */ function _executeDeleteAct($queryObject, $with_values = true) { $query = $this->getDeleteSql($queryObject, $with_values, true); $query .= (__DEBUG_QUERY__ & 1 && $this->query_id) ? sprintf(' ' . $this->comment_syntax, $this->query_id) : ''; if(is_a($query, 'Object')) { return; } return $this->_query($query); } /** * Handle selectAct * In order to get a list of pages easily when selecting \n * it supports a method as navigation * @param Object $queryObject * @param resource $connection * @param boolean $with_values * @return Object */ function _executeSelectAct($queryObject, $connection = null, $with_values = true) { $limit = $queryObject->getLimit(); $result = NULL; if($limit && $limit->isPageHandler()) { return $this->queryPageLimit($queryObject, $result, $connection, $with_values); } else { $query = $this->getSelectSql($queryObject, $with_values); if(is_a($query, 'Object')) { return; } $query .= (__DEBUG_QUERY__ & 1 && $queryObject->queryID) ? sprintf(' ' . $this->comment_syntax, $queryObject->queryID) : ''; $result = $this->_query($query, $connection); if($this->isError()) { return $this->queryError($queryObject); } $data = $this->_fetch($result); $buff = new Object (); $buff->data = $data; if($queryObject->usesClickCount()) { $update_query = $this->getClickCountQuery($queryObject); $this->_executeUpdateAct($update_query, $with_values); } return $buff; } } /** * Get the ID generated in the last query * Return next sequence from sequence table * This method use only mysql * @return int */ function db_insert_id() { $connection = $this->_getConnection('master'); return mysql_insert_id($connection); } /** * Fetch a result row as an object * @param resource $result * @return object */ function db_fetch_object(&$result) { return mysql_fetch_object($result); } /** * Free result memory * @param resource $result * @return boolean Returns TRUE on success or FALSE on failure. */ function db_free_result(&$result) { return mysql_free_result($result); } /** * Return the DBParser * @param boolean $force * @return DBParser */ function &getParser($force = FALSE) { $dbParser = new DBParser('`', '`', $this->prefix); return $dbParser; } /** * If have a error, return error object * @param Object $queryObject * @return Object */ function queryError($queryObject) { $limit = $queryObject->getLimit(); if($limit && $limit->isPageHandler()) { $buff = new Object (); $buff->total_count = 0; $buff->total_page = 0; $buff->page = 1; $buff->data = array(); $buff->page_navigation = new PageHandler(/* $total_count */0, /* $total_page */1, /* $page */1, /* $page_count */10); //default page handler values return $buff; } else { return; } } /** * If select query execute, return page info * @param Object $queryObject * @param resource $result * @param resource $connection * @param boolean $with_values * @return Object Object with page info containing */ function queryPageLimit($queryObject, $result, $connection, $with_values = true) { $limit = $queryObject->getLimit(); // Total count $temp_where = $queryObject->getWhereString($with_values, false); $count_query = sprintf('select count(*) as "count" %s %s', 'FROM ' . $queryObject->getFromString($with_values), ($temp_where === '' ? '' : ' WHERE ' . $temp_where)); // Check for distinct query and if found update count query structure $temp_select = $queryObject->getSelectString($with_values); $uses_distinct = stripos($temp_select, "distinct") !== false; $uses_groupby = $queryObject->getGroupByString() != ''; if($uses_distinct || $uses_groupby) { $count_query = sprintf('select %s %s %s %s' , $temp_select == '*' ? '1' : $temp_select , 'FROM ' . $queryObject->getFromString($with_values) , ($temp_where === '' ? '' : ' WHERE ' . $temp_where) , ($uses_groupby ? ' GROUP BY ' . $queryObject->getGroupByString() : '') ); // If query uses grouping or distinct, count from original select $count_query = sprintf('select count(*) as "count" from (%s) xet', $count_query); } $count_query .= (__DEBUG_QUERY__ & 1 && $queryObject->queryID) ? sprintf(' ' . $this->comment_syntax, $queryObject->queryID) : ''; $result_count = $this->_query($count_query, $connection); $count_output = $this->_fetch($result_count); $total_count = (int) (isset($count_output->count) ? $count_output->count : NULL); $list_count = $limit->list_count->getValue(); if(!$list_count) { $list_count = 20; } $page_count = $limit->page_count->getValue(); if(!$page_count) { $page_count = 10; } $page = $limit->page->getValue(); if(!$page || $page < 1) { $page = 1; } // total pages if($total_count) { $total_page = (int) (($total_count - 1) / $list_count) + 1; } else { $total_page = 1; } // check the page variables if($page > $total_page) { // If requested page is bigger than total number of pages, return empty list $buff = new Object (); $buff->total_count = $total_count; $buff->total_page = $total_page; $buff->page = $page; $buff->data = array(); $buff->page_navigation = new PageHandler($total_count, $total_page, $page, $page_count); return $buff; } $start_count = ($page - 1) * $list_count; $query = $this->getSelectPageSql($queryObject, $with_values, $start_count, $list_count); $query .= (__DEBUG_QUERY__ & 1 && $queryObject->query_id) ? sprintf(' ' . $this->comment_syntax, $this->query_id) : ''; $result = $this->_query($query, $connection); if($this->isError()) { return $this->queryError($queryObject); } $virtual_no = $total_count - ($page - 1) * $list_count; $data = $this->_fetch($result, $virtual_no); $buff = new Object (); $buff->total_count = $total_count; $buff->total_page = $total_page; $buff->page = $page; $buff->data = $data; $buff->page_navigation = new PageHandler($total_count, $total_page, $page, $page_count); return $buff; } /** * If select query execute, return paging sql * @param object $query * @param boolean $with_values * @param int $start_count * @param int $list_count * @return string select paging sql */ function getSelectPageSql($query, $with_values = true, $start_count = 0, $list_count = 0) { $select = $query->getSelectString($with_values); if($select == '') { return new Object(-1, "Invalid query"); } $select = 'SELECT ' . $select; $from = $query->getFromString($with_values); if($from == '') { return new Object(-1, "Invalid query"); } $from = ' FROM ' . $from; $where = $query->getWhereString($with_values); if($where != '') { $where = ' WHERE ' . $where; } $groupBy = $query->getGroupByString(); if($groupBy != '') { $groupBy = ' GROUP BY ' . $groupBy; } $orderBy = $query->getOrderByString(); if($orderBy != '') { $orderBy = ' ORDER BY ' . $orderBy; } $limit = $query->getLimitString(); if($limit != '') { $limit = sprintf(' LIMIT %d, %d', $start_count, $list_count); } return $select . ' ' . $from . ' ' . $where . ' ' . $groupBy . ' ' . $orderBy . ' ' . $limit; } } DBMysql::$isSupported = function_exists('mysql_connect'); /* End of file DBMysql.class.php */ /* Location: ./classes/db/DBMysql.class.php */ |
