1<?php 2// (c) Copyright by authors of the Tiki Wiki CMS Groupware Project 3// 4// All Rights Reserved. See copyright.txt for details and a complete list of authors. 5// Licensed under the GNU LESSER GENERAL PUBLIC LICENSE. See license.txt for details. 6// $Id$ 7 8/** 9 * Class TikiDb 10 * Implemented by TikiDb_Pdo and TikiDb_Adodb 11 */ 12abstract class TikiDb 13{ 14 const ERR_DIRECT = true; 15 const ERR_NONE = false; 16 const ERR_EXCEPTION = 'exception'; 17 18 private static $instance; 19 20 private $errorHandler; 21 private $errorMessage; 22 private $serverType; 23 24 protected $savedQuery; 25 26 private $tablePrefix; 27 private $usersTablePrefix; 28 29 /** 30 * @return TikiDb 31 */ 32 public static function get() // {{{ 33 { 34 return self::$instance; 35 } // }}} 36 37 public static function set(TikiDb $instance) // {{{ 38 { 39 return self::$instance = $instance; 40 } // }}} 41 42 function startTimer() // {{{ 43 { 44 list($micro, $sec) = explode(' ', microtime()); 45 return $micro + $sec; 46 } // }}} 47 48 function stopTimer($starttime) // {{{ 49 { 50 global $elapsed_in_db; 51 list($micro, $sec) = explode(' ', microtime()); 52 $now = $micro + $sec; 53 $elapsed_in_db += $now - $starttime; 54 } // }}} 55 56 abstract function qstr($str); 57 58 /** 59 * @param null $query 60 * @param null $values 61 * @param int $numrows 62 * @param int $offset 63 * @param bool $reporterrors 64 * @return TikiDb_Pdo_Result 65 */ 66 abstract function query($query = null, $values = null, $numrows = -1, $offset = -1, $reporterrors = self::ERR_DIRECT); 67 68 function lastInsertId() // {{{ 69 { 70 return $this->getOne('SELECT LAST_INSERT_ID()'); 71 } // }}} 72 73 function queryError($query, &$error, $values = null, $numrows = -1, $offset = -1) // {{{ 74 { 75 $this->errorMessage = ''; 76 $result = $this->query($query, $values, $numrows, $offset, self::ERR_NONE); 77 $error = $this->errorMessage; 78 79 return $result; 80 } // }}} 81 82 function queryException($query, $values = null, $numrows = -1, $offset = -1) // {{{ 83 { 84 return $this->query($query, $values, $numrows, $offset, self::ERR_EXCEPTION); 85 } // }}} 86 87 function getOne($query, $values = null, $reporterrors = self::ERR_DIRECT, $offset = 0) // {{{ 88 { 89 $result = $this->query($query, $values, 1, $offset, $reporterrors); 90 91 if ($result) { 92 $res = $result->fetchRow(); 93 94 if (empty($res)) { 95 return $res; 96 } 97 98 return reset($res); 99 } 100 101 return false; 102 } // }}} 103 104 function fetchAll($query = null, $values = null, $numrows = -1, $offset = -1, $reporterrors = self::ERR_DIRECT) // {{{ 105 { 106 $result = $this->query($query, $values, $numrows, $offset, $reporterrors); 107 108 $rows = []; 109 110 if ($result) { 111 while ($row = $result->fetchRow()) { 112 $rows[] = $row; 113 } 114 } 115 return $rows; 116 } // }}} 117 118 function fetchMap($query = null, $values = null, $numrows = -1, $offset = -1, $reporterrors = self::ERR_DIRECT) // {{{ 119 { 120 $result = $this->fetchAll($query, $values, $numrows, $offset, $reporterrors); 121 122 $map = []; 123 124 foreach ($result as $row) { 125 $key = array_shift($row); 126 $value = array_shift($row); 127 128 $map[$key] = $value; 129 } 130 131 return $map; 132 } // }}} 133 134 function setErrorHandler(TikiDb_ErrorHandler $handler) // {{{ 135 { 136 $this->errorHandler = $handler; 137 } // }}} 138 139 function setTablePrefix($prefix) // {{{ 140 { 141 $this->tablePrefix = $prefix; 142 } // }}} 143 144 function setUsersTablePrefix($prefix) // {{{ 145 { 146 $this->usersTablePrefix = $prefix; 147 } // }}} 148 149 function getServerType() // {{{ 150 { 151 return $this->serverType; 152 } // }}} 153 154 function setServerType($type) // {{{ 155 { 156 $this->serverType = $type; 157 } // }}} 158 159 function getErrorMessage() // {{{ 160 { 161 return $this->errorMessage; 162 } // }}} 163 164 protected function setErrorMessage($message) // {{{ 165 { 166 $this->errorMessage = $message; 167 } // }}} 168 169 protected function handleQueryError($query, $values, $result, $mode) // {{{ 170 { 171 if ($mode === self::ERR_NONE) { 172 return null; 173 } elseif ($mode === self::ERR_DIRECT && $this->errorHandler) { 174 $this->errorHandler->handle($this, $query, $values, $result); 175 } elseif ($mode === self::ERR_EXCEPTION || ! $this->errorHandler) { 176 TikiDb_Exception::classify($this->errorMessage); 177 } 178 } // }}} 179 180 protected function convertQueryTablePrefixes(&$query) // {{{ 181 { 182 $db_table_prefix = $this->tablePrefix; 183 $common_users_table_prefix = $this->usersTablePrefix; 184 185 if (! is_null($db_table_prefix) && ! empty($db_table_prefix)) { 186 if (! is_null($common_users_table_prefix) && ! empty($common_users_table_prefix)) { 187 $query = str_replace("`users_", "`" . $common_users_table_prefix . "users_", $query); 188 } else { 189 $query = str_replace("`users_", "`" . $db_table_prefix . "users_", $query); 190 } 191 192 $query = str_replace("`tiki_", "`" . $db_table_prefix . "tiki_", $query); 193 $query = str_replace("`messu_", "`" . $db_table_prefix . "messu_", $query); 194 $query = str_replace("`sessions", "`" . $db_table_prefix . "sessions", $query); 195 } 196 } // }}} 197 198 function convertSortMode($sort_mode, $fields = null) // {{{ 199 { 200 if (! $sort_mode) { 201 return '1'; 202 } 203 // parse $sort_mode for evil stuff 204 $sort_mode = str_replace('pref:', '', $sort_mode); 205 $sort_mode = preg_replace('/[^A-Za-z_,.]/', '', $sort_mode); 206 207 // Do not process empty sort modes 208 if (empty($sort_mode)) { 209 return '1'; 210 } 211 212 if ($sort_mode == 'random') { 213 return "RAND()"; 214 } 215 216 $sorts = []; 217 foreach (explode(',', $sort_mode) as $sort) { 218 // force ending to either _asc or _desc unless it's "random" 219 $sep = strrpos($sort, '_'); 220 $dir = substr($sort, $sep); 221 if (($dir !== '_asc') && ($dir !== '_desc')) { 222 if ($sep != (strlen($sort) - 1)) { 223 $sort .= '_'; 224 } 225 $sort .= 'asc'; 226 } 227 228 // When valid fields are specified, skip those not available 229 if (is_array($fields) && preg_match('/^(.*)_(asc|desc)$/', $sort, $parts)) { 230 if (! in_array($parts[1], $fields)) { 231 continue; 232 } 233 } 234 235 $sort = preg_replace('/_asc$/', '` asc', $sort); 236 $sort = preg_replace('/_desc$/', '` desc', $sort); 237 $sort = '`' . $sort; 238 $sort = str_replace('.', '`.`', $sort); 239 $sorts[] = $sort; 240 } 241 242 if (empty($sorts)) { 243 return '1'; 244 } 245 246 $sort_mode = implode(',', $sorts); 247 return $sort_mode; 248 } // }}} 249 250 function getQuery() // {{{ 251 { 252 return $this->savedQuery; 253 } // }}} 254 255 function setQuery($sql) // {{{ 256 { 257 $this->savedQuery = $sql; 258 } // }}} 259 260 function ifNull($field, $ifNull) // {{{ 261 { 262 return " COALESCE($field, $ifNull) "; 263 } // }}} 264 265 function in($field, $values, &$bindvars) // {{{ 266 { 267 $parts = explode('.', $field); 268 foreach ($parts as &$part) { 269 $part = '`' . $part . '`'; 270 } 271 $field = implode('.', $parts); 272 $bindvars = array_merge($bindvars, $values); 273 274 if (count($values) > 0) { 275 $values = rtrim(str_repeat('?,', count($values)), ','); 276 return " $field IN( $values ) "; 277 } else { 278 return " 0 "; 279 } 280 } // }}} 281 282 function parentObjects(&$objects, $table, $childKey, $parentKey) // {{{ 283 { 284 $query = "select `$childKey`, `$parentKey` from `$table` where `$childKey` in (" . implode(',', array_fill(0, count($objects), '?')) . ')'; 285 foreach ($objects as $object) { 286 $bindvars[] = $object['itemId']; 287 } 288 $result = $this->query($query, $bindvars); 289 while ($res = $result->fetchRow()) { 290 $ret[$res[$childKey]] = $res[$parentKey]; 291 } 292 foreach ($objects as $i => $object) { 293 $objects[$i][$parentKey] = $ret[$object['itemId']]; 294 } 295 } // }}} 296 297 function concat() // {{{ 298 { 299 $arr = func_get_args(); 300 301 // suggestion by andrew005@mnogo.ru 302 $s = implode(',', $arr); 303 if (strlen($s) > 0) { 304 return "CONCAT($s)"; 305 } else { 306 return ''; 307 } 308 } // }}} 309 310 function table($tableName, $autoIncrement = true) // {{{ 311 { 312 return new TikiDb_Table($this, $tableName, $autoIncrement); 313 } // }}} 314 315 function begin() // {{{ 316 { 317 return new TikiDb_Transaction; 318 } // }}} 319 320 /** 321 * Get a list of installed engines in the MySQL instance 322 * $return array of engine names 323 */ 324 function getEngines() 325 { 326 static $engines = []; 327 if (empty($engines)) { 328 $result = $this->query('show engines'); 329 if ($result) { 330 while ($res = $result->fetchRow()) { 331 $engines[] = $res['Engine']; 332 } 333 } 334 } 335 return $engines; 336 } 337 338 /** 339 * Check if InnoDB is an avaible engine 340 * @return true if the InnoDB engine is available 341 */ 342 function hasInnoDB() 343 { 344 $engines = $this->getEngines(); 345 foreach ($engines as $engine) { 346 if (strcmp(strtoupper($engine), 'INNODB') == 0) { 347 return true; 348 } 349 } 350 return false; 351 } 352 353 /** 354 * Detect the engine used in the current schema. 355 * Assumes that all tables use the same table engine 356 * @return string identifying the current engine, or an empty string if not installed 357 */ 358 function getCurrentEngine() 359 { 360 static $engine = ''; 361 if (empty($engine)) { 362 $result = $this->query('SHOW TABLE STATUS LIKE ?', 'tiki_schema'); 363 if ($result) { 364 $res = $result->fetchRow(); 365 $engine = $res['Engine']; 366 } 367 } 368 return $engine; 369 } 370 371 /** 372 * Determine if MySQL fulltext search is supported by the current DB engine 373 * Assumes that all tables use the same table engine. 374 * Fulltext search is assumed supported if 375 * 1) engine = MyISAM 376 * 2) engine = InnoDB and MySQL version >= 5.6 377 * @return true if it is supported, otherwise false 378 */ 379 function isMySQLFulltextSearchSupported() 380 { 381 $currentEngine = $this->getCurrentEngine(); 382 if (strcasecmp($currentEngine, "MyISAM") == 0) { 383 return true; 384 } elseif (strcasecmp($currentEngine, "INNODB") == 0) { 385 $versionNr = $this->getMySQLVersionNr(); 386 if ($versionNr >= 5.6) { 387 return true; 388 } else { 389 return false; 390 } 391 } 392 return false; 393 } 394 395 396 /** 397 * Read the MySQL version string. 398 * @return version string 399 */ 400 function getMySQLVersion() 401 { 402 static $version = ''; 403 if (empty($version)) { 404 $result = $this->query('select version() as Version'); 405 if ($result) { 406 $res = $result->fetchRow(); 407 $version = $res['Version']; 408 } 409 } 410 return $version; 411 } 412 /** 413 * Read the MySQL version number, e.g. 5.5 414 * @return version float 415 */ 416 function getMySQLVersionNr() 417 { 418 $versionNr = 0.0; 419 $version = $this->getMySQLVersion(); 420 $versionNr = (float)$version; 421 return $versionNr; 422 } 423 424 function listTables() 425 { 426 $result = $this->fetchAll("show tables"); 427 $list = []; 428 429 if ($result) { 430 foreach ($result as $row) { 431 $list[] = reset($row); 432 } 433 } 434 435 return $list; 436 } 437 438 /* 439 * isMySQLConnSSL 440 * Check if MySQL is using an SSL connection 441 * @return true if MySQL uses SSL. Otherwise false; 442 */ 443 function isMySQLConnSSL() 444 { 445 if (! $this->haveMySQLSSL()) { 446 return false; 447 } 448 $result = $this->query('show status like "Ssl_cipher"'); 449 $ret = $result->fetchRow(); 450 $cypher = $ret['Value']; 451 return ! empty($cypher); 452 } 453 454 /* 455 * Check if the MySQL installation has SSL activated 456 * @return true is SSL is supported and activated on the current MySQL server 457 */ 458 function haveMySQLSSL() 459 { 460 static $haveMySQLSSL = null; 461 462 if (! isset($haveMySQLSSL)) { 463 $result = $this->query('show variables like "have_ssl"'); 464 $ret = $result->fetchRow(); 465 if (empty($ret)) { 466 $result = $this->query('show variables like "have_openssl"'); 467 $ret = $result->fetchRow(); 468 } 469 if (! isset($ret)) { 470 $haveMySQLSSL = false; 471 } 472 $ssl = $ret['Value']; 473 if (empty($ssl)) { 474 $haveMySQLSSL = false; 475 } else { 476 $haveMySQLSSL = $ssl == 'YES'; 477 } 478 } 479 return $haveMySQLSSL; 480 } 481 482 483 /** 484 * Obtain a lock with a name given by the string $str, using a $timeout of timeout seconds 485 * @param $str 486 * @param int $timeout 487 * @return bool if lock was created 488 */ 489 function getLock($str, $timeout = 1) 490 { 491 if ($this->isLocked($str)) { 492 return false; 493 } 494 $result = $this->getOne("SELECT GET_LOCK(?, ?) as isLocked", [$str, $timeout]); 495 return (bool)((int)$result); 496 } 497 498 /** 499 * Releases the lock named by the string $str 500 * @param $str 501 * @return bool 502 */ 503 function releaseLock($str) 504 { 505 $result = $this->getOne("SELECT RELEASE_LOCK(?) as isReleased", [$str]); 506 return (bool)((int)$result); 507 } 508 509 /** 510 * Checks whether the lock named $str is in use (that is, locked) 511 * @param $str 512 * @return bool 513 */ 514 function isLocked($str) 515 { 516 $result = $this->getOne("SELECT IS_USED_LOCK(?) as isLocked", [$str]); 517 return (bool)((int)$result); 518 } 519} 520