XpressEngine Core  1.11.2
 All Classes Namespaces Files Functions Variables Pages
DBMysql.class.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) NAVER <http://www.navercorp.com> */
3 
14 class DBMysql extends DB
15 {
16 
21  var $prefix = 'xe_'; // / <
22  var $comment_syntax = '/* %s */';
23 
31  var $column_type = array(
32  'bignumber' => 'bigint',
33  'number' => 'bigint',
34  'varchar' => 'varchar',
35  'char' => 'char',
36  'text' => 'text',
37  'bigtext' => 'longtext',
38  'date' => 'varchar(14)',
39  'float' => 'float',
40  );
41 
46  function __construct($auto_connect = TRUE)
47  {
48  $this->_setDBInfo();
49  if($auto_connect) $this->_connect();
50  }
51 
56  function create()
57  {
58  return new DBMysql;
59  }
60 
68  {
69  // Ignore if no DB information exists
70  if(strpos($connection["db_hostname"], ':') === false && $connection["db_port"])
71  {
72  $connection["db_hostname"] .= ':' . $connection["db_port"];
73  }
74 
75  // Attempt to connect
76  $result = @mysql_connect($connection["db_hostname"], $connection["db_userid"], $connection["db_password"]);
77  if(!$result)
78  {
79  exit('XE cannot connect to DB.');
80  }
81 
82  if(mysql_error())
83  {
84  $this->setError(mysql_errno(), mysql_error());
85  return;
86  }
87  // Error appears if the version is lower than 4.1
88  if(version_compare(mysql_get_server_info($result), '4.1', '<'))
89  {
90  $this->setError(-1, 'XE cannot be installed under the version of mysql 4.1. Current mysql version is ' . mysql_get_server_info());
91  return;
92  }
93  // select db
94  @mysql_select_db($connection["db_database"], $result);
95  if(mysql_error())
96  {
97  $this->setError(mysql_errno(), mysql_error());
98  return;
99  }
100 
101  return $result;
102  }
103 
111  {
112  // Set utf8 if a database is MySQL
113  $this->_query("set names 'utf8'", $connection);
114  }
115 
122  function _close($connection)
123  {
124  @mysql_close($connection);
125  }
126 
132  function addQuotes($string)
133  {
134  if(version_compare(PHP_VERSION, "5.4.0", "<") && get_magic_quotes_gpc())
135  {
136  $string = stripslashes(str_replace("\\", "\\\\", $string));
137  }
138  if(!is_numeric($string))
139  {
140  $string = @mysql_real_escape_string($string);
141  }
142  return $string;
143  }
144 
150  function _begin($transactionLevel = 0)
151  {
152  return true;
153  }
154 
160  function _rollback($transactionLevel = 0)
161  {
162  return true;
163  }
164 
170  function _commit()
171  {
172  return true;
173  }
174 
183  {
184  if(!$connection)
185  {
186  exit('XE cannot handle DB connection.');
187  }
188  // Run the query statement
189  $result = mysql_query($query, $connection);
190  // Error Check
191  if(mysql_error($connection))
192  {
193  $this->setError(mysql_errno($connection), mysql_error($connection));
194  }
195  // Return result
196  return $result;
197  }
198 
205  function _fetch($result, $arrayIndexEndValue = NULL)
206  {
207  $output = array();
208  if(!$this->isConnected() || $this->isError() || !$result)
209  {
210  return $output;
211  }
212  while($tmp = $this->db_fetch_object($result))
213  {
214  if($arrayIndexEndValue)
215  {
216  $output[$arrayIndexEndValue--] = $tmp;
217  }
218  else
219  {
220  $output[] = $tmp;
221  }
222  }
223  if(count($output) == 1)
224  {
225  if(isset($arrayIndexEndValue))
226  {
227  return $output;
228  }
229  else
230  {
231  return $output[0];
232  }
233  }
234  $this->db_free_result($result);
235  return $output;
236  }
237 
243  function getNextSequence()
244  {
245  $query = sprintf("insert into `%ssequence` (seq) values ('0')", $this->prefix);
246  $this->_query($query);
247  $sequence = $this->db_insert_id();
248  if($sequence % 10000 == 0)
249  {
250  $query = sprintf("delete from `%ssequence` where seq < %d", $this->prefix, $sequence);
251  $this->_query($query);
252  }
253 
254  return $sequence;
255  }
256 
263  function isValidOldPassword($password, $saved_password)
264  {
265  $query = sprintf("select password('%s') as password, old_password('%s') as old_password", $this->addQuotes($password), $this->addQuotes($password));
266  $result = $this->_query($query);
267  $tmp = $this->_fetch($result);
268  if($tmp->password === $saved_password || $tmp->old_password === $saved_password)
269  {
270  return true;
271  }
272  return false;
273  }
274 
280  function isTableExists($target_name)
281  {
282  $query = sprintf("show tables like '%s%s'", $this->prefix, $this->addQuotes($target_name));
283  $result = $this->_query($query);
284  $tmp = $this->_fetch($result);
285  if(!$tmp)
286  {
287  return false;
288  }
289  return true;
290  }
291 
302  function addColumn($table_name, $column_name, $type = 'number', $size = '', $default = null, $notnull = false)
303  {
304  $type = $this->column_type[$type];
305  if(strtoupper($type) == 'INTEGER')
306  {
307  $size = '';
308  }
309 
310  $query = sprintf("alter table `%s%s` add `%s` ", $this->prefix, $table_name, $column_name);
311  if($size)
312  {
313  $query .= sprintf(" %s(%s) ", $type, $size);
314  }
315  else
316  {
317  $query .= sprintf(" %s ", $type);
318  }
319  if(isset($default))
320  {
321  $query .= sprintf(" default '%s' ", $default);
322  }
323  if($notnull)
324  {
325  $query .= " not null ";
326  }
327 
328  return $this->_query($query);
329  }
330 
337  function dropColumn($table_name, $column_name)
338  {
339  $query = sprintf("alter table `%s%s` drop `%s` ", $this->prefix, $table_name, $column_name);
340  $this->_query($query);
341  }
342 
349  function isColumnExists($table_name, $column_name)
350  {
351  $query = sprintf("show fields from `%s%s`", $this->prefix, $table_name);
352  $result = $this->_query($query);
353  if($this->isError())
354  {
355  return;
356  }
357  $output = $this->_fetch($result);
358  if($output)
359  {
360  $column_name = strtolower($column_name);
361  foreach($output as $key => $val)
362  {
363  $name = strtolower($val->Field);
364  if($column_name == $name)
365  {
366  return true;
367  }
368  }
369  }
370  return false;
371  }
372 
383  function addIndex($table_name, $index_name, $target_columns, $is_unique = false)
384  {
385  if(!is_array($target_columns))
386  {
387  $target_columns = array($target_columns);
388  }
389 
390  $query = sprintf("alter table `%s%s` add %s index `%s` (%s);", $this->prefix, $table_name, $is_unique ? 'unique' : '', $index_name, implode(',', $target_columns));
391  $this->_query($query);
392  }
393 
401  function dropIndex($table_name, $index_name, $is_unique = false)
402  {
403  $query = sprintf("alter table `%s%s` drop index `%s`", $this->prefix, $table_name, $index_name);
404  $this->_query($query);
405  }
406 
413  function isIndexExists($table_name, $index_name)
414  {
415  //$query = sprintf("show indexes from %s%s where key_name = '%s' ", $this->prefix, $table_name, $index_name);
416  $query = sprintf("show indexes from `%s%s`", $this->prefix, $table_name);
417  $result = $this->_query($query);
418  if($this->isError())
419  {
420  return;
421  }
422  $output = $this->_fetch($result);
423  if(!$output)
424  {
425  return;
426  }
427  if(!is_array($output))
428  {
429  $output = array($output);
430  }
431 
432  for($i = 0; $i < count($output); $i++)
433  {
434  if($output[$i]->Key_name == $index_name)
435  {
436  return true;
437  }
438  }
439  return false;
440  }
441 
447  function createTableByXml($xml_doc)
448  {
449  return $this->_createTable($xml_doc);
450  }
451 
457  function createTableByXmlFile($file_name)
458  {
459  if(!file_exists($file_name))
460  {
461  return;
462  }
463  // read xml file
464  $buff = FileHandler::readFile($file_name);
465  return $this->_createTable($buff);
466  }
467 
477  function _createTable($xml_doc)
478  {
479  // xml parsing
480  $oXml = new XmlParser();
481  $xml_obj = $oXml->parse($xml_doc);
482  // Create a table schema
483  $table_name = $xml_obj->table->attrs->name;
484  if($this->isTableExists($table_name))
485  {
486  return;
487  }
488  $table_name = $this->prefix . $table_name;
489 
490  if(!is_array($xml_obj->table->column))
491  {
492  $columns[] = $xml_obj->table->column;
493  }
494  else
495  {
496  $columns = $xml_obj->table->column;
497  }
498 
499  $primary_list = array();
500  $unique_list = array();
501  $index_list = array();
502 
503  foreach($columns as $column)
504  {
505  $name = $column->attrs->name;
506  $type = $column->attrs->type;
507  $size = $column->attrs->size;
508  $notnull = $column->attrs->notnull;
509  $primary_key = $column->attrs->primary_key;
510  $index = $column->attrs->index;
511  $unique = $column->attrs->unique;
512  $default = $column->attrs->default;
513  $auto_increment = $column->attrs->auto_increment;
514 
515  $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' : '');
516 
517  if($primary_key)
518  {
519  $primary_list[] = $name;
520  }
521  else if($unique)
522  {
523  $unique_list[$unique][] = $name;
524  }
525  else if($index)
526  {
527  $index_list[$index][] = $name;
528  }
529  }
530 
531  if(count($primary_list))
532  {
533  $column_schema[] = sprintf("primary key (%s)", '`' . implode($primary_list, '`,`') . '`');
534  }
535 
536  if(count($unique_list))
537  {
538  foreach($unique_list as $key => $val)
539  {
540  $column_schema[] = sprintf("unique %s (%s)", $key, '`' . implode($val, '`,`') . '`');
541  }
542  }
543 
544  if(count($index_list))
545  {
546  foreach($index_list as $key => $val)
547  {
548  $column_schema[] = sprintf("index %s (%s)", $key, '`' . implode($val, '`,`') . '`');
549  }
550  }
551 
552  $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");
553 
554  $output = $this->_query($schema);
555  if(!$output)
556  return false;
557  }
558 
565  function _executeInsertAct($queryObject, $with_values = true)
566  {
567  $query = $this->getInsertSql($queryObject, $with_values, true);
568  $query .= (__DEBUG_QUERY__ & 1 && $this->query_id) ? sprintf(' ' . $this->comment_syntax, $this->query_id) : '';
569  if(is_a($query, 'BaseObject'))
570  {
571  return;
572  }
573  return $this->_query($query);
574  }
575 
582  function _executeUpdateAct($queryObject, $with_values = true)
583  {
584  $query = $this->getUpdateSql($queryObject, $with_values, true);
585  if(is_a($query, 'BaseObject'))
586  {
587  if(!$query->toBool()) return $query;
588  else return;
589  }
590 
591  $query .= (__DEBUG_QUERY__ & 1 && $this->query_id) ? sprintf(' ' . $this->comment_syntax, $this->query_id) : '';
592 
593 
594  return $this->_query($query);
595  }
596 
603  function _executeDeleteAct($queryObject, $with_values = true)
604  {
605  $query = $this->getDeleteSql($queryObject, $with_values, true);
606  $query .= (__DEBUG_QUERY__ & 1 && $this->query_id) ? sprintf(' ' . $this->comment_syntax, $this->query_id) : '';
607  if(is_a($query, 'BaseObject'))
608  {
609  return;
610  }
611  return $this->_query($query);
612  }
613 
623  function _executeSelectAct($queryObject, $connection = null, $with_values = true)
624  {
625  $limit = $queryObject->getLimit();
626  $result = NULL;
627  if($limit && $limit->isPageHandler())
628  {
629  return $this->queryPageLimit($queryObject, $result, $connection, $with_values);
630  }
631  else
632  {
633  $query = $this->getSelectSql($queryObject, $with_values);
634  if(is_a($query, 'BaseObject'))
635  {
636  return;
637  }
638  $query .= (__DEBUG_QUERY__ & 1 && $queryObject->queryID) ? sprintf(' ' . $this->comment_syntax, $queryObject->queryID) : '';
639 
640  $result = $this->_query($query, $connection);
641  if($this->isError())
642  {
643  return $this->queryError($queryObject);
644  }
645 
646  $data = $this->_fetch($result);
647  $buff = new BaseObject();
648  $buff->data = $data;
649 
650  if($queryObject->usesClickCount())
651  {
652  $update_query = $this->getClickCountQuery($queryObject);
653  $this->_executeUpdateAct($update_query, $with_values);
654  }
655 
656  return $buff;
657  }
658  }
659 
666  function db_insert_id()
667  {
668  $connection = $this->_getConnection('master');
669  return mysql_insert_id($connection);
670  }
671 
678  {
679  return mysql_fetch_object($result);
680  }
681 
688  {
689  return mysql_free_result($result);
690  }
691 
697  function &getParser($force = FALSE)
698  {
699  $dbParser = new DBParser('`', '`', $this->prefix);
700  return $dbParser;
701  }
702 
708  function queryError($queryObject)
709  {
710  $limit = $queryObject->getLimit();
711  if($limit && $limit->isPageHandler())
712  {
713  $buff = new BaseObject();
714  $buff->total_count = 0;
715  $buff->total_page = 0;
716  $buff->page = 1;
717  $buff->data = array();
718  $buff->page_navigation = new PageHandler(/* $total_count */0, /* $total_page */1, /* $page */1, /* $page_count */10); //default page handler values
719  return $buff;
720  }
721  else
722  {
723  return;
724  }
725  }
726 
735  function queryPageLimit($queryObject, $result, $connection, $with_values = true)
736  {
737  $limit = $queryObject->getLimit();
738  // Total count
739  $temp_where = $queryObject->getWhereString($with_values, false);
740  $count_query = sprintf('select count(*) as "count" %s %s', 'FROM ' . $queryObject->getFromString($with_values), ($temp_where === '' ? '' : ' WHERE ' . $temp_where));
741 
742  // Check for distinct query and if found update count query structure
743  $temp_select = $queryObject->getSelectString($with_values);
744  $uses_distinct = stripos($temp_select, "distinct") !== false;
745  $uses_groupby = $queryObject->getGroupByString() != '';
746  if($uses_distinct || $uses_groupby)
747  {
748  $count_query = sprintf('select %s %s %s %s'
749  , $temp_select == '*' ? '1' : $temp_select
750  , 'FROM ' . $queryObject->getFromString($with_values)
751  , ($temp_where === '' ? '' : ' WHERE ' . $temp_where)
752  , ($uses_groupby ? ' GROUP BY ' . $queryObject->getGroupByString() : '')
753  );
754 
755  // If query uses grouping or distinct, count from original select
756  $count_query = sprintf('select count(*) as "count" from (%s) xet', $count_query);
757  }
758 
759  $count_query .= (__DEBUG_QUERY__ & 1 && $queryObject->queryID) ? sprintf(' ' . $this->comment_syntax, $queryObject->queryID) : '';
760  $result_count = $this->_query($count_query, $connection);
761  $count_output = $this->_fetch($result_count);
762  $total_count = (int) (isset($count_output->count) ? $count_output->count : NULL);
763 
764  $list_count = $limit->list_count->getValue();
765  if(!$list_count)
766  {
767  $list_count = 20;
768  }
769  $page_count = $limit->page_count->getValue();
770  if(!$page_count)
771  {
772  $page_count = 10;
773  }
774  $page = $limit->page->getValue();
775  if(!$page || $page < 1)
776  {
777  $page = 1;
778  }
779 
780  // total pages
781  if($total_count)
782  {
783  $total_page = (int) (($total_count - 1) / $list_count) + 1;
784  }
785  else
786  {
787  $total_page = 1;
788  }
789 
790  // check the page variables
791  if($page > $total_page)
792  {
793  // If requested page is bigger than total number of pages, return empty list
794  $buff = new BaseObject();
795  $buff->total_count = $total_count;
796  $buff->total_page = $total_page;
797  $buff->page = $page;
798  $buff->data = array();
799  $buff->page_navigation = new PageHandler($total_count, $total_page, $page, $page_count);
800  return $buff;
801  }
802  $start_count = ($page - 1) * $list_count;
803 
804  $query = $this->getSelectPageSql($queryObject, $with_values, $start_count, $list_count);
805 
806  $query .= (__DEBUG_QUERY__ & 1 && $queryObject->query_id) ? sprintf(' ' . $this->comment_syntax, $this->query_id) : '';
807  $result = $this->_query($query, $connection);
808  if($this->isError())
809  {
810  return $this->queryError($queryObject);
811  }
812 
813  $virtual_no = $total_count - ($page - 1) * $list_count;
814  $data = $this->_fetch($result, $virtual_no);
815 
816  $buff = new BaseObject();
817  $buff->total_count = $total_count;
818  $buff->total_page = $total_page;
819  $buff->page = $page;
820  $buff->data = $data;
821  $buff->page_navigation = new PageHandler($total_count, $total_page, $page, $page_count);
822  return $buff;
823  }
824 
833  function getSelectPageSql($query, $with_values = true, $start_count = 0, $list_count = 0)
834  {
835  $select = $query->getSelectString($with_values);
836  if($select == '')
837  {
838  return new BaseObject(-1, "Invalid query");
839  }
840  $select = 'SELECT ' . $select;
841 
842  $from = $query->getFromString($with_values);
843  if($from == '')
844  {
845  return new BaseObject(-1, "Invalid query");
846  }
847  $from = ' FROM ' . $from;
848 
849  $where = $query->getWhereString($with_values);
850  if($where != '')
851  {
852  $where = ' WHERE ' . $where;
853  }
854 
855  $groupBy = $query->getGroupByString();
856  if($groupBy != '')
857  {
858  $groupBy = ' GROUP BY ' . $groupBy;
859  }
860 
861  $orderBy = $query->getOrderByString();
862  if($orderBy != '')
863  {
864  $orderBy = ' ORDER BY ' . $orderBy;
865  }
866 
867  $limit = $query->getLimitString();
868  if($limit != '')
869  {
870  $limit = sprintf(' LIMIT %d, %d', $start_count, $list_count);
871  }
872 
873  return $select . ' ' . $from . ' ' . $where . ' ' . $groupBy . ' ' . $orderBy . ' ' . $limit;
874  }
875 
876 }
877 
878 DBMysql::$isSupported = function_exists('mysql_connect');
879 
880 /* End of file DBMysql.class.php */
881 /* Location: ./classes/db/DBMysql.class.php */
setError($errno=0, $errstr= 'success')
Definition: DB.class.php:476
_executeDeleteAct($queryObject, $with_values=true)
getUpdateSql($query, $with_values=TRUE, $with_priority=FALSE)
Definition: DB.class.php:943
getClickCountQuery($queryObject)
Definition: DB.class.php:885
static $isSupported
Definition: DB.class.php:19
_rollback($transactionLevel=0)
db_free_result(&$result)
$output
Definition: ko.install.php:193
getInsertSql($query, $with_values=TRUE, $with_priority=FALSE)
Definition: DB.class.php:975
_executeInsertAct($queryObject, $with_values=true)
__query($query, $connection)
queryError($queryObject)
isConnected($type= 'master', $indx=0)
Definition: DB.class.php:360
isValidOldPassword($password, $saved_password)
addIndex($table_name, $index_name, $target_columns, $is_unique=false)
_begin($transactionLevel=0)
_createTable($xml_doc)
queryPageLimit($queryObject, $result, $connection, $with_values=true)
getDeleteSql($query, $with_values=TRUE, $with_priority=FALSE)
Definition: DB.class.php:911
$query
Definition: DB.class.php:84
Definition: DB.class.php:16
& getParser($force=FALSE)
_close($connection)
isError()
Definition: DB.class.php:486
__connect($connection)
addQuotes($string)
_afterConnect($connection)
isColumnExists($table_name, $column_name)
addColumn($table_name, $column_name, $type= 'number', $size= '', $default=null, $notnull=false)
__construct($auto_connect=TRUE)
getSelectPageSql($query, $with_values=true, $start_count=0, $list_count=0)
_executeSelectAct($queryObject, $connection=null, $with_values=true)
readFile($filename)
_connect($type= 'master', $indx=0)
Definition: DB.class.php:1266
_getConnection($type= 'master', $indx=NULL)
Definition: DB.class.php:1001
getSelectSql($query, $with_values=TRUE)
Definition: DB.class.php:816
_query($query, $connection=NULL)
Definition: DB.class.php:1193
_fetch($result, $arrayIndexEndValue=NULL)
createTableByXmlFile($file_name)
db_fetch_object(&$result)
$result
Definition: DB.class.php:66
createTableByXml($xml_doc)
isTableExists($target_name)
dropColumn($table_name, $column_name)
_setDBInfo()
Definition: DB.class.php:1216
_executeUpdateAct($queryObject, $with_values=true)
dropIndex($table_name, $index_name, $is_unique=false)
$connection
Definition: DB.class.php:85
isIndexExists($table_name, $index_name)