AdamB7586 /
pdo-dbal
Complex classes like Database often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes. You can also have a look at the cohesion graph to spot any un-connected, or weakly-connected components.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use Database, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 14 | final class Database implements DBInterface{
|
||
| 15 | protected $db; |
||
| 16 | public $sql; |
||
| 17 | private $key; |
||
| 18 | |||
| 19 | protected $logLocation; |
||
| 20 | public $logErrors = true; |
||
| 21 | public $logQueries = false; |
||
| 22 | public $displayErrors = false; |
||
| 23 | |||
| 24 | protected $database; |
||
| 25 | protected $cacheEnabled = false; |
||
| 26 | protected $cacheObj; |
||
| 27 | protected $cacheValue; |
||
| 28 | protected $modified = false; |
||
| 29 | |||
| 30 | private $query; |
||
| 31 | private $values = []; |
||
| 32 | private $prepare = []; |
||
| 33 | |||
| 34 | private static $connectors = array( |
||
| 35 | 'cubrid' => 'cubrid:host=%s;port=%d;dbname=%s', |
||
| 36 | 'dblib' => 'dblib:host=%s:%d;dbname=%s', |
||
| 37 | 'mssql' => 'sqlsrv:Server=%s,%d;Database=%s', |
||
| 38 | 'mysql' => 'mysql:host=%s;port=%d;dbname=%s', |
||
| 39 | 'pgsql' => 'pgsql:host=%s;port=%d;dbname=%s', |
||
| 40 | 'sqlite' => 'sqlite::memory:' |
||
| 41 | ); |
||
| 42 | |||
| 43 | /** |
||
| 44 | * Connect to database using PDO connection |
||
| 45 | * @param string $hostname This should be the host of the database e.g. 'localhost' |
||
| 46 | * @param string $username This should be the username for the chosen database |
||
| 47 | * @param string $password This should be the password for the chosen database |
||
| 48 | * @param string $database This should be the database that you wish to connect to |
||
| 49 | * @param string|false $backuphost If you have a replication server set up put the hostname or IP address incase the primary server goes down |
||
| 50 | * @param object|false $cache If you want to cache the queries with Memcache(d)/Redis/APC/Xcache This should be the object else set to false |
||
| 51 | * @param boolean $persistent If you want a persistent database connection set to true |
||
| 52 | * @param string $type The type of connection that you wish to make can be 'mysql', 'cubrid', 'dblib', 'mssql', 'odbc', 'pgsql, or 'sqlite' |
||
| 53 | * @param int $port This should be the port number of the MySQL database connection |
||
| 54 | */ |
||
| 55 | 1 | public function __construct($hostname, $username, $password, $database, $backuphost = false, $cache = false, $persistent = false, $type = 'mysql', $port = 3306) {
|
|
| 70 | |||
| 71 | /** |
||
| 72 | * Closes the PDO database connection when Database object unset |
||
| 73 | */ |
||
| 74 | 1 | public function __destruct() {
|
|
| 75 | 1 | $this->closeDatabase(); |
|
| 76 | 1 | } |
|
| 77 | |||
| 78 | /** |
||
| 79 | * Connect to the database using PDO connection |
||
| 80 | * @param string $username This should be the username for the chosen database |
||
| 81 | * @param string $password This should be the password for the chosen database |
||
| 82 | * @param string $database This should be the database that you wish to connect to |
||
| 83 | * @param string $hostname The hostname for the database |
||
| 84 | * @param boolean $persistent If you want a persistent database connection set to true |
||
| 85 | * @param string $type The type of connection that you wish to make can be 'mysql', 'cubrid', 'dblib', 'mssql', 'pgsql, or 'sqlite' |
||
| 86 | * @param int $port The port number to connect to the MySQL server |
||
| 87 | */ |
||
| 88 | 2 | protected function connectToServer($username, $password, $database, $hostname, $persistent = false, $type = 'mysql', $port = 3306) {
|
|
| 100 | |||
| 101 | /** |
||
| 102 | * Enables the caching and set the caching object to the one provided |
||
| 103 | * @param object $caching This should be class of the type of caching you are using |
||
| 104 | */ |
||
| 105 | 1 | public function setCaching($caching) {
|
|
| 106 | 1 | if(is_object($caching)) {
|
|
| 107 | 1 | $this->cacheObj = $caching; |
|
| 108 | 1 | $this->cacheEnabled = true; |
|
| 109 | } |
||
| 110 | 1 | return $this; |
|
| 111 | } |
||
| 112 | |||
| 113 | /** |
||
| 114 | * This query function is used for more advanced SQL queries for which non of the other methods fit |
||
| 115 | * @param string $sql This should be the SQL query which you wish to run |
||
| 116 | * @param array $variables This should be an array of values to execute as the values in a prepared statement |
||
| 117 | * @return array Returns array of results for the query that has just been run |
||
| 118 | */ |
||
| 119 | 1 | public function query($sql, $variables = array(), $cache = true) {
|
|
| 132 | |||
| 133 | /** |
||
| 134 | * Returns a single record for a select query for the chosen table |
||
| 135 | * @param string $table This should be the table you wish to select the values from |
||
| 136 | * @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
|
||
| 137 | * @param string|array $fields This should be the records you wis to select from the table. It should be either set as '*' which is the default or set as an array in the following format array('field', 'field2', 'field3', etc).
|
||
| 138 | * @param array $order This is the order you wish the results to be ordered in should be formatted as follows array('fieldname' => 'ASC') or array("'fieldname', 'fieldname2'" => 'DESC')
|
||
| 139 | * @param boolean $cache If the query should be cached or loaded from cache set to true else set to false |
||
| 140 | * @return array Returns a single table record as the standard array when running SQL queries |
||
| 141 | */ |
||
| 142 | 1 | public function select($table, $where = array(), $fields = '*', $order = array(), $cache = true) {
|
|
| 145 | |||
| 146 | /** |
||
| 147 | * Returns a multidimensional array of the results from the selected table given the given parameters |
||
| 148 | * @param string $table This should be the table you wish to select the values from |
||
| 149 | * @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
|
||
| 150 | * @param string|array $fields This should be the records you wis to select from the table. It should be either set as '*' which is the default or set as an array in the following format array('field', 'field2', 'field3', etc).
|
||
| 151 | * @param array $order This is the order you wish the results to be ordered in should be formatted as follows array('fieldname' => 'ASC') or array("'fieldname', 'fieldname2'" => 'DESC')
|
||
| 152 | * @param integer|array $limit The number of results you want to return 0 is default and returns all results, else should be formated either as a standard integer or as an array as the start and end values e.g. array(0 => 150) |
||
| 153 | * @param boolean $cache If the query should be cached or loaded from cache set to true else set to false |
||
| 154 | * @return array Returns a multidimensional array with the chosen fields from the table |
||
| 155 | */ |
||
| 156 | 3 | public function selectAll($table, $where = array(), $fields = '*', $order = array(), $limit = 0, $cache = true) {
|
|
| 166 | |||
| 167 | /** |
||
| 168 | * Returns a single column value for a given query |
||
| 169 | * @param string $table This should be the table you wish to select the values from |
||
| 170 | * @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
|
||
| 171 | * @param array $fields This should be the records you wis to select from the table. It should be either set as '*' which is the default or set as an array in the following format array('field', 'field2', 'field3', etc).
|
||
| 172 | * @param int $colNum This should be the column number you wish to get (starts at 0) |
||
| 173 | * @param array $order This is the order you wish the results to be ordered in should be formatted as follows array('fieldname' => 'ASC') or array("'fieldname', 'fieldname2'" => 'DESC') so it can be done in both directions
|
||
| 174 | * @param boolean $cache If the query should be cached or loaded from cache set to true else set to false |
||
| 175 | * @return mixed If a result is found will return the value of the colum given else will return false |
||
| 176 | */ |
||
| 177 | 2 | public function fetchColumn($table, $where = array(), $fields = '*', $colNum = 0, $order = array(), $cache = true) {
|
|
| 187 | |||
| 188 | /** |
||
| 189 | * Inserts into database using the prepared PDO statements |
||
| 190 | * @param string $table This should be the table you wish to insert the values into |
||
| 191 | * @param array $records This should be the field names and values in the format of array('fieldname' => 'value', 'fieldname2' => 'value2', etc.)
|
||
| 192 | * @return boolean If data is inserted returns true else returns false |
||
| 193 | */ |
||
| 194 | 2 | public function insert($table, $records) {
|
|
| 201 | |||
| 202 | /** |
||
| 203 | * Updates values in a database using the provide variables |
||
| 204 | * @param string $table This should be the table you wish to update the values for |
||
| 205 | * @param array $records This should be the field names and new values in the format of array('fieldname' => 'newvalue', 'fieldname2' => 'newvalue2', etc.)
|
||
| 206 | * @param array $where Should be the field names and values you wish to update in the form of an array e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
|
||
| 207 | * @param int $limit The number of results you want to return 0 is default and will update all results that match the query, else should be formated as a standard integer |
||
| 208 | * @return boolean Returns true if update is successful else returns false |
||
| 209 | */ |
||
| 210 | 2 | public function update($table, $records, $where = array(), $limit = 0) {
|
|
| 215 | |||
| 216 | /** |
||
| 217 | * Deletes records from the given table based on the variables given |
||
| 218 | * @param string $table This should be the table you wish to delete the records from |
||
| 219 | * @param array $where This should be an array of for the where statement |
||
| 220 | * @param int $limit The number of results you want to return 0 is default and will delete all results that match the query, else should be formated as a standard integer |
||
| 221 | */ |
||
| 222 | 2 | public function delete($table, $where, $limit = 0) {
|
|
| 227 | |||
| 228 | /** |
||
| 229 | * Count the number of return results |
||
| 230 | * @param string $table The table you wish to count the result of |
||
| 231 | * @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
|
||
| 232 | * @param boolean $cache If the query should be cached or loaded from cache set to true else set to false |
||
| 233 | * @return int Returns the number of results |
||
| 234 | */ |
||
| 235 | 1 | public function count($table, $where = array(), $cache = true) {
|
|
| 246 | |||
| 247 | /** |
||
| 248 | * Truncates a given table from the selected database so there are no values in the table |
||
| 249 | * @param string $table This should be the table you wish to truncate |
||
| 250 | * @return boolean If the table is emptied returns true else returns false |
||
| 251 | */ |
||
| 252 | 1 | public function truncate($table) {
|
|
| 253 | try{
|
||
| 254 | 1 | $this->sql = sprintf("TRUNCATE TABLE `%s`", SafeString::makeSafe($table));
|
|
| 255 | 1 | $this->executeQuery(false); |
|
| 256 | } |
||
| 257 | catch(\Exception $e) {
|
||
| 258 | $this->error($e); |
||
| 259 | } |
||
| 260 | 1 | return $this->numRows() ? true : false; |
|
| 261 | } |
||
| 262 | |||
| 263 | /** |
||
| 264 | * Returns the number of rows for the last query sent |
||
| 265 | * @return int Returns the number of rows for the last query |
||
| 266 | */ |
||
| 267 | 8 | public function numRows() {
|
|
| 273 | |||
| 274 | /** |
||
| 275 | * Returns the number of rows for the last query sent (Looks a the numRows() function just added incase of habbit) |
||
| 276 | * @return int Returns the number of rows for the last query |
||
| 277 | */ |
||
| 278 | 1 | public function rowCount() {
|
|
| 281 | |||
| 282 | /** |
||
| 283 | * Returns the ID of the last record last inserted |
||
| 284 | * @param string $name This should be the name of the sequence object you wish to retrieve |
||
| 285 | * @return int|string Returns the last inserted ID of the last insert item if $name is null else returns string with sequenced object |
||
| 286 | */ |
||
| 287 | 1 | public function lastInsertId($name = null) {
|
|
| 290 | |||
| 291 | /** |
||
| 292 | * Checks to see if a connection has been made to the server |
||
| 293 | * @return boolean |
||
| 294 | */ |
||
| 295 | 2 | public function isConnected() {
|
|
| 298 | |||
| 299 | /** |
||
| 300 | * Returns the server version information |
||
| 301 | */ |
||
| 302 | 1 | public function serverVersion() {
|
|
| 303 | 1 | return $this->db->getAttribute(PDO::ATTR_SERVER_VERSION); |
|
| 304 | } |
||
| 305 | |||
| 306 | /** |
||
| 307 | * Sets the location of the log files |
||
| 308 | * @param string $location This should be where you wish the logs to be stored |
||
| 309 | * @return $this |
||
| 310 | */ |
||
| 311 | 1 | public function setLogLocation($location = false) {
|
|
| 312 | 1 | if($location === false) {
|
|
| 313 | 1 | $location = dirname(__FILE__).DIRECTORY_SEPARATOR.'logs'.DIRECTORY_SEPARATOR; |
|
| 314 | } |
||
| 315 | 1 | $this->logLocation = $location; |
|
| 316 | 1 | if (!file_exists($location)) {
|
|
| 317 | 1 | mkdir($location, 0777, true); |
|
| 318 | } |
||
| 319 | 1 | return $this; |
|
| 320 | } |
||
| 321 | |||
| 322 | /** |
||
| 323 | * Displays the error massage which occurs |
||
| 324 | * @param \Exception $error This should be an instance of Exception |
||
| 325 | */ |
||
| 326 | 4 | private function error($error) {
|
|
| 337 | |||
| 338 | /** |
||
| 339 | * Writes all queries to a log file |
||
| 340 | */ |
||
| 341 | public function writeQueryToLog() {
|
||
| 347 | |||
| 348 | /** |
||
| 349 | * Closes the PDO database connection by setting the connection to NULL |
||
| 350 | */ |
||
| 351 | 1 | public function closeDatabase() {
|
|
| 352 | 1 | $this->db = null; |
|
| 353 | 1 | } |
|
| 354 | |||
| 355 | /** |
||
| 356 | * Build the SQL query but doesn't execute it |
||
| 357 | * @param string $table This should be the table you wish to select the values from |
||
| 358 | * @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
|
||
| 359 | * @param string|array $fields This should be the records you wis to select from the table. It should be either set as '*' which is the default or set as an array in the following format array('field', 'field2', 'field3', etc).
|
||
| 360 | * @param array $order This is the order you wish the results to be ordered in should be formatted as follows array('fieldname' => 'ASC') or array("'fieldname', 'fieldname2'" => 'DESC') so it can be done in both directions
|
||
| 361 | * @param integer|array $limit The number of results you want to return 0 is default and returns all results, else should be formated either as a standard integer or as an array as the start and end values e.g. array(0 => 150) |
||
| 362 | */ |
||
| 363 | 5 | protected function buildSelectQuery($table, $where = array(), $fields = '*', $order = array(), $limit = 0) {
|
|
| 364 | 5 | if(is_array($fields)) {
|
|
| 365 | 1 | $selectfields = array(); |
|
| 366 | 1 | foreach($fields as $field => $value) {
|
|
| 367 | 1 | $selectfields[] = sprintf("`%s`", SafeString::makeSafe($value));
|
|
| 368 | } |
||
| 369 | 1 | $fieldList = implode(', ', $selectfields);
|
|
| 370 | } |
||
| 371 | 5 | else{$fieldList = '*';}
|
|
| 372 | |||
| 373 | 5 | $this->sql = sprintf("SELECT %s FROM `%s`%s%s%s;", $fieldList, SafeString::makeSafe($table), $this->where($where), $this->orderBy($order), $this->limit($limit));
|
|
| 374 | 5 | $this->key = md5($this->database.$this->sql.serialize($this->values)); |
|
| 375 | 5 | } |
|
| 376 | |||
| 377 | /** |
||
| 378 | * Execute the current query if no cache value is available |
||
| 379 | * @param boolean $cache If the cache should be checked for the checked for the values of the query set to true else set to false |
||
| 380 | * @return mixed If a cached value exists will be returned else if cache is not checked and query is executed will not return anything |
||
| 381 | */ |
||
| 382 | 13 | protected function executeQuery($cache = true) {
|
|
| 398 | |||
| 399 | /** |
||
| 400 | * This outputs the SQL where query based on a given array |
||
| 401 | * @param array $where This should be an array that you wish to create the where query for in the for array('field1' => 'test') or array('field1' => array('>=', 0))
|
||
| 402 | * @return string|false If the where query is an array will return the where string and set the values else returns false if no array sent |
||
| 403 | */ |
||
| 404 | 9 | private function where($where) {
|
|
| 405 | 9 | if(is_array($where) && !empty($where)) {
|
|
| 406 | 8 | $wherefields = array(); |
|
| 407 | 8 | foreach($where as $field => $value) {
|
|
| 408 | 8 | $wherefields[] = $this->formatValues($field, $value); |
|
| 409 | } |
||
| 410 | 8 | if(!empty($wherefields)) {
|
|
| 411 | 8 | return " WHERE ".implode(' AND ', $wherefields);
|
|
| 412 | } |
||
| 413 | } |
||
| 414 | 2 | return false; |
|
| 415 | } |
||
| 416 | |||
| 417 | /** |
||
| 418 | * Sets the order sting for the SQL query based on an array or string |
||
| 419 | * @param array|string $order This should be either set to array('fieldname' => 'ASC/DESC') or RAND()
|
||
| 420 | * @return string|false If the SQL query has an valid order by will return a string else returns false |
||
| 421 | */ |
||
| 422 | 1 | private function orderBy($order) {
|
|
| 440 | |||
| 441 | /** |
||
| 442 | * Build the field list for the query |
||
| 443 | * @param array $records This should be an array listing all of the fields |
||
| 444 | * @param boolean $insert If this is an insert statement should be set to true to create the correct amount of queries for the prepared statement |
||
| 445 | * @return string The fields list will be returned as a string to insert into the SQL query |
||
| 446 | */ |
||
| 447 | 4 | private function fields($records, $insert = false) {
|
|
| 448 | 4 | $fields = array(); |
|
| 449 | |||
| 450 | 4 | foreach($records as $field => $value) {
|
|
| 451 | 4 | if($insert === true) {
|
|
| 452 | 4 | $fields[] = sprintf("`%s`", SafeString::makeSafe($field));
|
|
| 453 | 4 | $this->prepare[] = '?'; |
|
| 454 | } |
||
| 455 | else{
|
||
| 456 | 2 | $fields[] = sprintf("`%s` = ?", SafeString::makeSafe($field));
|
|
| 457 | } |
||
| 458 | 4 | $this->values[] = $value; |
|
| 459 | } |
||
| 460 | 4 | return implode(', ', $fields);
|
|
| 461 | } |
||
| 462 | |||
| 463 | /** |
||
| 464 | * Returns the limit SQL for the current query as a string |
||
| 465 | * @param integer|array $limit This should either be set as an integer or should be set as an array with a start and end value |
||
| 466 | * @return string|false Will return the LIMIT string for the current query if it is valid else returns false |
||
| 467 | */ |
||
| 468 | 1 | private function limit($limit = 0) {
|
|
| 469 | 1 | if(is_array($limit) && !empty(array_filter($limit))) {
|
|
| 470 | 1 | foreach($limit as $start => $end) {
|
|
| 471 | 1 | return " LIMIT ".intval($start).", ".intval($end); |
|
| 472 | } |
||
| 473 | } |
||
| 474 | 1 | elseif((int)$limit > 0) {
|
|
| 475 | 1 | return " LIMIT ".intval($limit); |
|
| 476 | } |
||
| 477 | 1 | return false; |
|
| 478 | } |
||
| 479 | |||
| 480 | |||
| 481 | /** |
||
| 482 | * Set the cache with a key and value |
||
| 483 | * @param string $key The unique key to store the value against |
||
| 484 | * @param mixed $value The value of the MYSQL query |
||
| 485 | */ |
||
| 486 | public function setCache($key, $value) {
|
||
| 491 | |||
| 492 | /** |
||
| 493 | * Get the results for a given key |
||
| 494 | * @param string $key The unique key to check for stored variables |
||
| 495 | * @return mixed Returned the cached results from |
||
| 496 | */ |
||
| 497 | public function getCache($key) {
|
||
| 504 | |||
| 505 | /** |
||
| 506 | * Clears the cache |
||
| 507 | */ |
||
| 508 | public function flushDB() {
|
||
| 511 | |||
| 512 | /** |
||
| 513 | * Format the where queries and set the prepared values |
||
| 514 | * @param string $field This should be the field name in the database |
||
| 515 | * @param mixed $value This should be the value which should either be a string or an array if it contains an operator |
||
| 516 | * @return string This should be the string to add to the SQL query |
||
| 517 | */ |
||
| 518 | 8 | protected function formatValues($field, $value) {
|
|
| 519 | 8 | if(!is_array($value) && Operators::isOperatorValid($value) && !Operators::isOperatorPrepared($value)) {
|
|
| 520 | 1 | return sprintf("`%s` %s", SafeString::makeSafe($field), Operators::getOperatorFormat($value));
|
|
| 521 | } |
||
| 522 | 8 | elseif(is_array($value)) {
|
|
| 523 | 2 | if(!is_array(array_values($value)[0])) {
|
|
| 524 | 2 | $this->values[] = (isset($value[1]) ? $value[1] : array_values($value)[0]); |
|
| 525 | 2 | $operator = (isset($value[0]) ? $value[0] : key($value)); |
|
| 526 | } |
||
| 527 | else{
|
||
| 528 | 1 | foreach(array_values($value)[0] as $op => $array_value) {
|
|
| 529 | 1 | $this->values[] = $array_value; |
|
| 530 | } |
||
| 531 | 1 | $operator = key($value); |
|
| 532 | } |
||
| 533 | 2 | return sprintf("`%s` %s", SafeString::makeSafe($field), Operators::getOperatorFormat($operator));
|
|
| 534 | |||
| 535 | } |
||
| 536 | 6 | $this->values[] = $value; |
|
| 537 | 6 | return sprintf("`%s` = ?", SafeString::makeSafe($field));
|
|
| 538 | } |
||
| 539 | |||
| 540 | /** |
||
| 541 | * Band values to use in the query |
||
| 542 | * @param array $values This should be the values being used in the query |
||
| 543 | */ |
||
| 544 | 12 | protected function bindValues($values) {
|
|
| 555 | } |
||
| 556 |
