1<?php 2/* 3 * vim:set softtabstop=4 shiftwidth=4 expandtab: 4 * 5 * LICENSE: GNU Affero General Public License, version 3 (AGPL-3.0-or-later) 6 * Copyright 2001 - 2020 Ampache.org 7 * 8 * This program is free software: you can redistribute it and/or modify 9 * it under the terms of the GNU Affero General Public License as published by 10 * the Free Software Foundation, either version 3 of the License, or 11 * (at your option) any later version. 12 * 13 * This program is distributed in the hope that it will be useful, 14 * but WITHOUT ANY WARRANTY; without even the implied warranty of 15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 16 * GNU Affero General Public License for more details. 17 * 18 * You should have received a copy of the GNU Affero General Public License 19 * along with this program. If not, see <https://www.gnu.org/licenses/>. 20 * 21 */ 22 23declare(strict_types=0); 24 25namespace Ampache\Module\System; 26 27use Ampache\Config\AmpConfig; 28use PDO; 29use PDOException; 30use PDOStatement; 31 32/** 33 * This is the database abstraction class 34 * It duplicates the functionality of mysql_??? 35 * with a few exceptions, the row and assoc will always 36 * return an array, simplifying checking on the far end 37 * it will also auto-connect as needed, and has a default 38 * database simplifying queries in most cases. 39 */ 40class Dba 41{ 42 public static $stats = array('query' => 0); 43 44 private static $_sql; 45 private static $_error; 46 47 /** 48 * query 49 * @param string $sql 50 * @param array $params 51 * @return PDOStatement|boolean 52 */ 53 public static function query($sql, $params = array()) 54 { 55 // json_encode throws errors about UTF-8 cleanliness, which we don't care about here. 56 debug_event(__CLASS__, $sql . ' ' . json_encode($params), 6); 57 58 // Be aggressive, be strong, be dumb 59 $tries = 0; 60 do { 61 $stmt = self::_query($sql, $params); 62 } while (!$stmt && $tries++ < 3); 63 64 return $stmt; 65 } 66 67 /** 68 * _query 69 * @param string $sql 70 * @param array $params 71 * @return PDOStatement|boolean 72 */ 73 private static function _query($sql, $params) 74 { 75 $dbh = self::dbh(); 76 if (!$dbh) { 77 debug_event(__CLASS__, 'Error: failed to get database handle', 1); 78 79 return false; 80 } 81 82 // Run the query 83 if (!empty($params)) { 84 $stmt = $dbh->prepare($sql); 85 $stmt->execute($params); 86 } else { 87 $stmt = $dbh->query($sql); 88 } 89 90 // Save the query, to make debug easier 91 self::$_sql = $sql; 92 self::$stats['query']++; 93 94 if (!$stmt) { 95 self::$_error = json_encode($dbh->errorInfo()); 96 debug_event(__CLASS__, 'Error_query SQL: ' . $sql, 5); 97 debug_event(__CLASS__, 'Error_query MSG: ' . json_encode($dbh->errorInfo()), 1); 98 self::disconnect(); 99 } else { 100 if ($stmt->errorCode() && $stmt->errorCode() != '00000') { 101 self::$_error = json_encode($stmt->errorInfo()); 102 debug_event(__CLASS__, 'Error_query SQL: ' . $sql, 5); 103 debug_event(__CLASS__, 'Error_query MSG: ' . json_encode($stmt->errorInfo()), 1); 104 self::finish($stmt); 105 self::disconnect(); 106 107 return false; 108 } 109 } 110 111 return $stmt; 112 } 113 114 /** 115 * read 116 * @param string $sql 117 * @param array $params 118 * @return PDOStatement|boolean 119 */ 120 public static function read($sql, $params = array()) 121 { 122 return self::query($sql, $params); 123 } 124 125 /** 126 * write 127 * @param string $sql 128 * @param array $params 129 * @return PDOStatement|boolean 130 */ 131 public static function write($sql, $params = array()) 132 { 133 return self::query($sql, $params); 134 } 135 136 /** 137 * escape 138 * 139 * This runs an escape on a variable so that it can be safely inserted 140 * into the sql 141 * @param $var 142 * @return string 143 */ 144 public static function escape($var) 145 { 146 $dbh = self::dbh(); 147 if (!$dbh) { 148 debug_event(__CLASS__, 'Wrong dbh.', 1); 149 150 return ''; 151 } 152 $out_var = $dbh->quote(filter_var($var, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES)); 153 // This is slightly less ugly than it was, but still ugly 154 return substr($out_var, 1, -1); 155 } 156 157 /** 158 * fetch_assoc 159 * 160 * This emulates the mysql_fetch_assoc. 161 * We force it to always return an array, albeit an empty one 162 * The optional finish parameter affects whether we automatically clean 163 * up the result set after the last row is read. 164 * @param $resource 165 * @param boolean $finish 166 * @return array 167 */ 168 public static function fetch_assoc($resource, $finish = true) 169 { 170 if (!$resource) { 171 return array(); 172 } 173 174 $result = $resource->fetch(PDO::FETCH_ASSOC); 175 176 if (!$result) { 177 if ($finish) { 178 self::finish($resource); 179 } 180 181 return array(); 182 } 183 184 return $result; 185 } 186 187 /** 188 * fetch_row 189 * 190 * This emulates the mysql_fetch_row 191 * we force it to always return an array, albeit an empty one 192 * The optional finish parameter affects whether we automatically clean 193 * up the result set after the last row is read. 194 * @param $resource 195 * @param boolean $finish 196 * @return array 197 */ 198 public static function fetch_row($resource, $finish = true) 199 { 200 if (!$resource) { 201 return array(); 202 } 203 204 $result = $resource->fetch(PDO::FETCH_NUM); 205 206 if (!$result) { 207 if ($finish) { 208 self::finish($resource); 209 } 210 211 return array(); 212 } 213 214 return $result; 215 } 216 217 /** 218 * @param $resource 219 * @param string $class 220 * @param boolean $finish 221 * @return array 222 */ 223 public static function fetch_object($resource, $class = 'stdClass', $finish = true) 224 { 225 if (!$resource) { 226 return array(); 227 } 228 229 $result = $resource->fetchObject($class); 230 231 if (!$result) { 232 if ($finish) { 233 self::finish($resource); 234 } 235 236 return array(); 237 } 238 239 return $result; 240 } 241 242 /** 243 * num_rows 244 * 245 * This emulates the mysql_num_rows function which is really 246 * just a count of rows returned by our select statement, this 247 * doesn't work for updates or inserts. 248 * @param $resource 249 * @return integer 250 */ 251 public static function num_rows($resource) 252 { 253 if ($resource) { 254 $result = $resource->rowCount(); 255 if ($result) { 256 return $result; 257 } 258 } 259 260 return 0; 261 } 262 263 /** 264 * finish 265 * 266 * This closes a result handle and clears the memory associated with it 267 * @param $resource 268 */ 269 public static function finish($resource) 270 { 271 if ($resource) { 272 $resource->closeCursor(); 273 } 274 } 275 276 /** 277 * affected_rows 278 * 279 * This emulates the mysql_affected_rows function 280 * @param $resource 281 * @return integer 282 */ 283 public static function affected_rows($resource) 284 { 285 if ($resource) { 286 $result = $resource->rowCount(); 287 if ($result) { 288 return $result; 289 } 290 } 291 292 return 0; 293 } 294 295 /** 296 * _connect 297 * 298 * This connects to the database, used by the DBH function 299 * @return PDO|null 300 */ 301 private static function _connect() 302 { 303 $username = AmpConfig::get('database_username'); 304 $hostname = AmpConfig::get('database_hostname', ''); 305 $password = AmpConfig::get('database_password'); 306 $port = AmpConfig::get('database_port'); 307 308 if ($hostname === '') { 309 return null; 310 } 311 312 // Build the data source name 313 if (strpos($hostname, '/') === 0) { 314 $dsn = 'mysql:unix_socket=' . $hostname; 315 } else { 316 $dsn = 'mysql:host=' . $hostname; 317 } 318 if ($port) { 319 $dsn .= ';port=' . (int)($port); 320 } 321 322 try { 323 debug_event(__CLASS__, 'Database connection...', 5); 324 $dbh = new PDO($dsn, $username, $password); 325 } catch (PDOException $error) { 326 self::$_error = $error->getMessage(); 327 debug_event(__CLASS__, 'Connection failed: ' . $error->getMessage(), 1); 328 329 return null; 330 } 331 332 return $dbh; 333 } 334 335 /** 336 * _setup_dbh 337 * @param null|PDO $dbh 338 * @param string $database 339 * @return boolean 340 */ 341 private static function _setup_dbh($dbh, $database) 342 { 343 if (!$dbh) { 344 return false; 345 } 346 347 $charset = self::translate_to_mysqlcharset(AmpConfig::get('site_charset')); 348 $charset = $charset['charset']; 349 if ($dbh->exec('SET NAMES ' . $charset) === false) { 350 debug_event(__CLASS__, 'Unable to set connection charset to ' . $charset, 1); 351 } 352 353 if ($dbh->exec('USE `' . $database . '`') === false) { 354 self::$_error = json_encode($dbh->errorInfo()); 355 debug_event(__CLASS__, 'Unable to select database ' . $database . ': ' . json_encode($dbh->errorInfo()), 356 1); 357 } 358 359 if (AmpConfig::get('sql_profiling')) { 360 $dbh->exec('SET profiling=1'); 361 $dbh->exec('SET profiling_history_size=50'); 362 $dbh->exec('SET query_cache_type=0'); 363 } 364 365 return true; 366 } 367 368 /** 369 * check_database 370 * 371 * Make sure that we can connect to the database 372 * @return boolean 373 */ 374 public static function check_database() 375 { 376 $dbh = self::_connect(); 377 378 if (!$dbh || $dbh->errorCode()) { 379 if ($dbh) { 380 self::$_error = json_encode($dbh->errorInfo()); 381 } 382 383 return false; 384 } 385 386 return true; 387 } 388 389 /** 390 * check_database_inserted 391 * 392 * Checks to make sure that you have inserted the database 393 * and that the user you are using has access to it. 394 * @return boolean 395 */ 396 public static function check_database_inserted() 397 { 398 $sql = "DESCRIBE session"; 399 $db_results = self::read($sql); 400 401 if (!$db_results) { 402 return false; 403 } 404 405 // Make sure the table is there 406 if (self::num_rows($db_results) < 1) { 407 return false; 408 } 409 410 return true; 411 } 412 413 /** 414 * show_profile 415 * 416 * This function is used for debug, helps with profiling 417 */ 418 public static function show_profile() 419 { 420 if (AmpConfig::get('sql_profiling')) { 421 print '<br/>Profiling data: <br/>'; 422 $res = self::read('SHOW PROFILES'); 423 print '<table>'; 424 while ($row = self::fetch_row($res)) { 425 print '<tr><td>' . implode('</td><td>', $row) . '</td></tr>'; 426 } 427 print '</table>'; 428 } 429 } 430 431 /** 432 * dbh 433 * 434 * This is called by the class to return the database handle 435 * for the specified database, if none is found it connects 436 * @param string $database 437 * @return mixed|PDO|null 438 */ 439 public static function dbh($database = '') 440 { 441 if (!$database) { 442 $database = AmpConfig::get('database_name'); 443 } 444 445 // Assign the Handle name that we are going to store 446 $handle = 'dbh_' . $database; 447 448 if (!is_object(AmpConfig::get($handle))) { 449 $dbh = self::_connect(); 450 self::_setup_dbh($dbh, $database); 451 AmpConfig::set($handle, $dbh, true); 452 453 return $dbh; 454 } else { 455 return AmpConfig::get($handle); 456 } 457 } 458 459 /** 460 * disconnect 461 * 462 * This nukes the dbh connection, this isn't used very often... 463 * @param string $database 464 * @return boolean 465 */ 466 public static function disconnect($database = '') 467 { 468 if (!$database) { 469 $database = AmpConfig::get('database_name'); 470 } 471 472 $handle = 'dbh_' . $database; 473 474 // Nuke it 475 debug_event(__CLASS__, 'Database disconnection.', 6); 476 AmpConfig::set($handle, null, true); 477 478 return true; 479 } 480 481 /** 482 * insert_id 483 * @return string|null 484 */ 485 public static function insert_id() 486 { 487 $dbh = self::dbh(); 488 if ($dbh) { 489 return $dbh->lastInsertId(); 490 } 491 492 return null; 493 } 494 495 /** 496 * error 497 * this returns the error of the db 498 */ 499 public static function error() 500 { 501 return self::$_error; 502 } 503 504 /** 505 * translate_to_mysqlcharset 506 * 507 * This translates the specified charset to a mysql charset. 508 * @param $charset 509 * @return array 510 */ 511 public static function translate_to_mysqlcharset($charset) 512 { 513 // Translate real charset names into fancy MySQL land names 514 switch (strtoupper((string)$charset)) { 515 case 'CP1250': 516 case 'WINDOWS-1250': 517 $target_charset = AmpConfig::get('database_charset', 'cp1250'); 518 $target_collation = AmpConfig::get('database_collation', 'cp1250_general_ci'); 519 break; 520 case 'ISO-8859': 521 case 'ISO-8859-2': 522 $target_charset = AmpConfig::get('database_charset', 'latin2'); 523 $target_collation = AmpConfig::get('database_collation', 'latin2_general_ci'); 524 break; 525 case 'ISO-8859-1': 526 case 'CP1252': 527 case 'WINDOWS-1252': 528 $target_charset = AmpConfig::get('database_charset', 'latin1'); 529 $target_collation = AmpConfig::get('database_collation', 'latin1_general_ci'); 530 break; 531 case 'EUC-KR': 532 $target_charset = AmpConfig::get('database_charset', 'euckr'); 533 $target_collation = AmpConfig::get('database_collation', 'euckr_korean_ci'); 534 break; 535 case 'CP932': 536 $target_charset = AmpConfig::get('database_charset', 'sjis'); 537 $target_collation = AmpConfig::get('database_collation', 'sjis_japanese_ci'); 538 break; 539 case 'KOI8-U': 540 $target_charset = AmpConfig::get('database_charset', 'koi8u'); 541 $target_collation = AmpConfig::get('database_collation', 'koi8u_general_ci'); 542 break; 543 case 'KOI8-R': 544 $target_charset = AmpConfig::get('database_charset', 'koi8r'); 545 $target_collation = AmpConfig::get('database_collation', 'koi8r_general_ci'); 546 break; 547 case 'UTF-8': 548 default: 549 $target_charset = AmpConfig::get('database_charset', 'utf8mb4'); 550 $target_collation = AmpConfig::get('database_collation', 'utf8mb4_unicode_ci'); 551 break; 552 } 553 554 return array( 555 'charset' => $target_charset, 556 'collation' => $target_collation 557 ); 558 } 559 560 /** 561 * optimize_tables 562 * 563 * This runs an optimize on the tables and updates the stats to improve 564 * join speed. 565 * This can be slow, but is a good idea to do from time to time. We do 566 * it in case the dba isn't doing it... which we're going to assume they 567 * aren't. 568 */ 569 public static function optimize_tables() 570 { 571 $sql = "SHOW TABLES"; 572 $db_results = self::read($sql); 573 574 while ($row = self::fetch_row($db_results)) { 575 $sql = "OPTIMIZE TABLE `" . $row[0] . "`"; 576 self::write($sql); 577 578 $sql = "ANALYZE TABLE `" . $row[0] . "`"; 579 self::write($sql); 580 } 581 } 582} 583