elkarte /
Elkarte
Duplicate code is one of the most pungent code smells. A rule that is often used is to re-structure code once it is duplicated in three or more places.
Common duplication problems, and corresponding solutions are:
Complex classes like Database_PostgreSQL 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_PostgreSQL, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 25 | class Database_PostgreSQL extends Database_Abstract |
||
| 26 | { |
||
| 27 | /** |
||
| 28 | * Holds current instance of the class |
||
| 29 | * @var Database_PostgreSQL |
||
| 30 | */ |
||
| 31 | private static $_db = null; |
||
| 32 | |||
| 33 | /** |
||
| 34 | * Holds last query result |
||
| 35 | * @var string |
||
| 36 | */ |
||
| 37 | private $_db_last_result = null; |
||
| 38 | |||
| 39 | /** |
||
| 40 | * Since PostgreSQL doesn't support INSERT REPLACE we are using this to remember |
||
| 41 | * the rows affected by the delete |
||
| 42 | * @var int |
||
| 43 | */ |
||
| 44 | private $_db_replace_result = null; |
||
| 45 | |||
| 46 | /** |
||
| 47 | * A variable to remember if a transaction was started already or not |
||
| 48 | * @var boolean |
||
| 49 | */ |
||
| 50 | private $_in_transaction = false; |
||
| 51 | |||
| 52 | /** |
||
| 53 | * Initializes a database connection. |
||
| 54 | * It returns the connection, if successful. |
||
| 55 | * |
||
| 56 | * @param string $db_server |
||
| 57 | * @param string $db_name |
||
| 58 | * @param string $db_user |
||
| 59 | * @param string $db_passwd |
||
| 60 | * @param mixed[] $db_options |
||
| 61 | * |
||
| 62 | * @return resource |
||
| 63 | */ |
||
| 64 | public static function initiate($db_server, $db_name, $db_user, $db_passwd, $db_options = array()) |
||
| 65 | { |
||
| 66 | // initialize the instance... if not done already! |
||
| 67 | if (self::$_db === null) |
||
| 68 | self::$_db = new self(); |
||
| 69 | |||
| 70 | View Code Duplication | if (!empty($db_options['port'])) |
|
| 71 | $db_port = ' port=' . (int) $db_options['port']; |
||
| 72 | else |
||
| 73 | $db_port = ''; |
||
| 74 | |||
| 75 | if (!empty($db_options['persist'])) |
||
| 76 | $connection = @pg_pconnect('host=' . $db_server . $db_port . ' dbname=' . $db_name . ' user=\'' . $db_user . '\' password=\'' . $db_passwd . '\''); |
||
| 77 | else |
||
| 78 | $connection = @pg_connect('host=' . $db_server . $db_port . ' dbname=' . $db_name . ' user=\'' . $db_user . '\' password=\'' . $db_passwd . '\''); |
||
| 79 | |||
| 80 | // Something's wrong, show an error if its fatal (which we assume it is) |
||
| 81 | View Code Duplication | if (!$connection) |
|
| 82 | { |
||
| 83 | if (!empty($db_options['non_fatal'])) |
||
| 84 | return null; |
||
| 85 | else |
||
| 86 | Errors::instance()->display_db_error(); |
||
| 87 | } |
||
| 88 | |||
| 89 | self::$_db->_connection = $connection; |
||
| 90 | |||
| 91 | return $connection; |
||
| 92 | } |
||
| 93 | |||
| 94 | /** |
||
| 95 | * Fix the database prefix if necessary. |
||
| 96 | * Do nothing on postgreSQL |
||
| 97 | * |
||
| 98 | * @param string $db_prefix |
||
| 99 | * @param string $db_name |
||
| 100 | * |
||
| 101 | * @return string |
||
| 102 | */ |
||
| 103 | public function fix_prefix($db_prefix, $db_name) |
||
| 104 | { |
||
| 105 | return $db_prefix; |
||
| 106 | } |
||
| 107 | |||
| 108 | /** |
||
| 109 | * Do a query. Takes care of errors too. |
||
| 110 | * Special queries may need additional replacements to be appropriate |
||
| 111 | * for PostgreSQL. |
||
| 112 | * |
||
| 113 | * @param string $identifier |
||
| 114 | * @param string $db_string |
||
| 115 | * @param mixed[] $db_values |
||
| 116 | * @param resource|null $connection |
||
| 117 | * |
||
| 118 | * @return bool|resource|string |
||
| 119 | * @throws Elk_Exception |
||
| 120 | */ |
||
| 121 | public function query($identifier, $db_string, $db_values = array(), $connection = null) |
||
| 122 | { |
||
| 123 | global $db_show_debug, $time_start, $modSettings; |
||
| 124 | |||
| 125 | // Decide which connection to use. |
||
| 126 | $connection = $connection === null ? $this->_connection : $connection; |
||
| 127 | |||
| 128 | // Special queries that need processing. |
||
| 129 | $replacements = array( |
||
| 130 | 'ban_suggest_error_ips' => array( |
||
| 131 | '~RLIKE~' => '~', |
||
| 132 | '~\\.~' => '\.', |
||
| 133 | ), |
||
| 134 | 'ban_suggest_message_ips' => array( |
||
| 135 | '~RLIKE~' => '~', |
||
| 136 | '~\\.~' => '\.', |
||
| 137 | ), |
||
| 138 | 'consolidate_spider_stats' => array( |
||
| 139 | '~MONTH\(log_time\), DAYOFMONTH\(log_time\)~' => 'MONTH(CAST(CAST(log_time AS abstime) AS timestamp)), DAYOFMONTH(CAST(CAST(log_time AS abstime) AS timestamp))', |
||
| 140 | ), |
||
| 141 | 'attach_download_increase' => array( |
||
| 142 | '~LOW_PRIORITY~' => '', |
||
| 143 | ), |
||
| 144 | 'insert_log_search_topics' => array( |
||
| 145 | '~NOT RLIKE~' => '!~', |
||
| 146 | ), |
||
| 147 | 'insert_log_search_results_no_index' => array( |
||
| 148 | '~NOT RLIKE~' => '!~', |
||
| 149 | ), |
||
| 150 | 'insert_log_search_results_subject' => array( |
||
| 151 | '~NOT RLIKE~' => '!~', |
||
| 152 | ), |
||
| 153 | 'pm_conversation_list' => array( |
||
| 154 | '~ORDER\\s+BY\\s+\\{raw:sort\\}~' => 'ORDER BY ' . (isset($db_values['sort']) ? ($db_values['sort'] === 'pm.id_pm' ? 'MAX(pm.id_pm)' : $db_values['sort']) : ''), |
||
| 155 | ), |
||
| 156 | 'profile_board_stats' => array( |
||
| 157 | '~COUNT\(\*\) \/ MAX\(b.num_posts\)~' => 'CAST(COUNT(*) AS DECIMAL) / CAST(b.num_posts AS DECIMAL)', |
||
| 158 | ), |
||
| 159 | ); |
||
| 160 | |||
| 161 | if (isset($replacements[$identifier])) |
||
| 162 | $db_string = preg_replace(array_keys($replacements[$identifier]), array_values($replacements[$identifier]), $db_string); |
||
| 163 | |||
| 164 | // Limits need to be a little different. |
||
| 165 | $db_string = preg_replace('~\sLIMIT\s(\d+|{int:.+}),\s*(\d+|{int:.+})\s*$~i', 'LIMIT $2 OFFSET $1', $db_string); |
||
| 166 | |||
| 167 | if (trim($db_string) == '') |
||
| 168 | return false; |
||
| 169 | |||
| 170 | // Comments that are allowed in a query are preg_removed. |
||
| 171 | static $allowed_comments_from = array( |
||
| 172 | '~\s+~s', |
||
| 173 | '~/\*!40001 SQL_NO_CACHE \*/~', |
||
| 174 | '~/\*!40000 USE INDEX \([A-Za-z\_]+?\) \*/~', |
||
| 175 | '~/\*!40100 ON DUPLICATE KEY UPDATE id_msg = \d+ \*/~', |
||
| 176 | ); |
||
| 177 | static $allowed_comments_to = array( |
||
| 178 | ' ', |
||
| 179 | '', |
||
| 180 | '', |
||
| 181 | '', |
||
| 182 | ); |
||
| 183 | |||
| 184 | // One more query.... |
||
| 185 | $this->_query_count++; |
||
| 186 | $this->_db_replace_result = null; |
||
| 187 | |||
| 188 | View Code Duplication | if (empty($modSettings['disableQueryCheck']) && strpos($db_string, '\'') !== false && empty($db_values['security_override'])) |
|
| 189 | $this->error_backtrace('Hacking attempt...', 'Illegal character (\') used in query...', true, __FILE__, __LINE__); |
||
| 190 | |||
| 191 | View Code Duplication | if (empty($db_values['security_override']) && (!empty($db_values) || strpos($db_string, '{db_prefix}') !== false)) |
|
| 192 | { |
||
| 193 | // Store these values for use in the callback function. |
||
| 194 | $this->_db_callback_values = $db_values; |
||
| 195 | $this->_db_callback_connection = $connection; |
||
| 196 | |||
| 197 | // Inject the values passed to this function. |
||
| 198 | $db_string = preg_replace_callback('~{([a-z_]+)(?::([a-zA-Z0-9_-]+))?}~', array($this, 'replacement__callback'), $db_string); |
||
| 199 | |||
| 200 | // No need for them any longer. |
||
| 201 | $this->_db_callback_values = array(); |
||
| 202 | $this->_db_callback_connection = null; |
||
| 203 | } |
||
| 204 | |||
| 205 | // Debugging. |
||
| 206 | View Code Duplication | if ($db_show_debug === true) |
|
| 207 | { |
||
| 208 | $debug = Debug::instance(); |
||
| 209 | |||
| 210 | // Get the file and line number this function was called. |
||
| 211 | list ($file, $line) = $this->error_backtrace('', '', 'return', __FILE__, __LINE__); |
||
| 212 | |||
| 213 | if (!empty($_SESSION['debug_redirect'])) |
||
| 214 | { |
||
| 215 | $debug->merge_db($_SESSION['debug_redirect']); |
||
| 216 | // @todo this may be off by 1 |
||
| 217 | $this->_query_count += count($_SESSION['debug_redirect']); |
||
| 218 | $_SESSION['debug_redirect'] = array(); |
||
| 219 | } |
||
| 220 | |||
| 221 | // Don't overload it. |
||
| 222 | $st = microtime(true); |
||
| 223 | $db_cache = array(); |
||
| 224 | $db_cache['q'] = $this->_query_count < 50 ? $db_string : '...'; |
||
| 225 | $db_cache['f'] = $file; |
||
| 226 | $db_cache['l'] = $line; |
||
| 227 | $db_cache['s'] = $st - $time_start; |
||
| 228 | } |
||
| 229 | |||
| 230 | // First, we clean strings out of the query, reduce whitespace, lowercase, and trim - so we can check it over. |
||
| 231 | if (empty($modSettings['disableQueryCheck'])) |
||
| 232 | { |
||
| 233 | $clean = ''; |
||
| 234 | $old_pos = 0; |
||
| 235 | $pos = -1; |
||
| 236 | View Code Duplication | while (true) |
|
| 237 | { |
||
| 238 | $pos = strpos($db_string, '\'', $pos + 1); |
||
| 239 | if ($pos === false) |
||
| 240 | break; |
||
| 241 | $clean .= substr($db_string, $old_pos, $pos - $old_pos); |
||
| 242 | |||
| 243 | while (true) |
||
| 244 | { |
||
| 245 | $pos1 = strpos($db_string, '\'', $pos + 1); |
||
| 246 | $pos2 = strpos($db_string, '\'\'', $pos + 1); |
||
| 247 | |||
| 248 | if ($pos1 === false) |
||
| 249 | break; |
||
| 250 | elseif ($pos2 === false || $pos2 > $pos1) |
||
| 251 | { |
||
| 252 | $pos = $pos1; |
||
| 253 | break; |
||
| 254 | } |
||
| 255 | |||
| 256 | $pos = $pos2 + 1; |
||
| 257 | } |
||
| 258 | |||
| 259 | $clean .= ' %s '; |
||
| 260 | $old_pos = $pos + 1; |
||
| 261 | } |
||
| 262 | |||
| 263 | $clean .= substr($db_string, $old_pos); |
||
| 264 | $clean = trim(strtolower(preg_replace($allowed_comments_from, $allowed_comments_to, $clean))); |
||
| 265 | |||
| 266 | // Comments? We don't use comments in our queries, we leave 'em outside! |
||
| 267 | View Code Duplication | if (strpos($clean, '/*') > 2 || strpos($clean, '--') !== false || strpos($clean, ';') !== false) |
|
| 268 | $fail = true; |
||
| 269 | // Trying to change passwords, slow us down, or something? |
||
| 270 | elseif (strpos($clean, 'sleep') !== false && preg_match('~(^|[^a-z])sleep($|[^[_a-z])~s', $clean) != 0) |
||
| 271 | $fail = true; |
||
| 272 | elseif (strpos($clean, 'benchmark') !== false && preg_match('~(^|[^a-z])benchmark($|[^[a-z])~s', $clean) != 0) |
||
| 273 | $fail = true; |
||
| 274 | |||
| 275 | if (!empty($fail) && class_exists('Errors')) |
||
| 276 | $this->error_backtrace('Hacking attempt...', 'Hacking attempt...' . "\n" . $db_string, E_USER_ERROR, __FILE__, __LINE__); |
||
| 277 | |||
| 278 | // If we are updating something, better start a transaction so that indexes may be kept consistent |
||
| 279 | if (!$this->_in_transaction && strpos($clean, 'update') !== false) |
||
| 280 | $this->db_transaction('begin', $connection); |
||
|
|
|||
| 281 | } |
||
| 282 | |||
| 283 | $this->_db_last_result = @pg_query($connection, $db_string); |
||
| 284 | |||
| 285 | if ($this->_db_last_result === false && !$this->_skip_error) |
||
| 286 | { |
||
| 287 | $this->error($db_string, $connection); |
||
| 288 | } |
||
| 289 | |||
| 290 | // Revert not to skip errors |
||
| 291 | if ($this->_skip_error === true) |
||
| 292 | { |
||
| 293 | $this->_skip_error = false; |
||
| 294 | } |
||
| 295 | |||
| 296 | if ($this->_in_transaction) |
||
| 297 | $this->db_transaction('commit', $connection); |
||
| 298 | |||
| 299 | // Debugging. |
||
| 300 | View Code Duplication | if ($db_show_debug === true) |
|
| 301 | { |
||
| 302 | $db_cache['t'] = microtime(true) - $st; |
||
| 303 | $debug->db_query($db_cache); |
||
| 304 | } |
||
| 305 | |||
| 306 | return $this->_db_last_result; |
||
| 307 | } |
||
| 308 | |||
| 309 | /** |
||
| 310 | * Affected rows from previous operation. |
||
| 311 | * |
||
| 312 | * @param resource|null $result |
||
| 313 | * |
||
| 314 | * @return int |
||
| 315 | */ |
||
| 316 | public function affected_rows($result = null) |
||
| 317 | { |
||
| 318 | if ($this->_db_replace_result !== null) |
||
| 319 | return $this->_db_replace_result; |
||
| 320 | elseif ($result === null && !$this->_db_last_result) |
||
| 321 | return 0; |
||
| 322 | |||
| 323 | return pg_affected_rows($result === null ? $this->_db_last_result : $result); |
||
| 324 | } |
||
| 325 | |||
| 326 | /** |
||
| 327 | * Last inserted id. |
||
| 328 | * |
||
| 329 | * @param string $table |
||
| 330 | * @param string|null $field = null |
||
| 331 | * @param resource|null $connection = null |
||
| 332 | * |
||
| 333 | * @return bool|mixed |
||
| 334 | * @throws Elk_Exception |
||
| 335 | */ |
||
| 336 | public function insert_id($table, $field = null, $connection = null) |
||
| 337 | { |
||
| 338 | global $db_prefix; |
||
| 339 | |||
| 340 | $table = str_replace('{db_prefix}', $db_prefix, $table); |
||
| 341 | |||
| 342 | $connection = $connection === null ? $this->_connection : $connection; |
||
| 343 | |||
| 344 | // Try get the last ID for the auto increment field. |
||
| 345 | $request = $this->query('', 'SELECT CURRVAL(\'' . $table . '_seq\') AS insertID', |
||
| 346 | array( |
||
| 347 | ), |
||
| 348 | $connection |
||
| 349 | ); |
||
| 350 | |||
| 351 | if (!$request) |
||
| 352 | return false; |
||
| 353 | |||
| 354 | list ($lastID) = $this->fetch_row($request); |
||
| 355 | $this->free_result($request); |
||
| 356 | |||
| 357 | return $lastID; |
||
| 358 | } |
||
| 359 | |||
| 360 | /** |
||
| 361 | * Tracking the current row. |
||
| 362 | * Fetch a row from the resultset given as parameter. |
||
| 363 | * |
||
| 364 | * @param resource $request |
||
| 365 | * @param integer|bool $counter = false |
||
| 366 | * |
||
| 367 | * @return array |
||
| 368 | */ |
||
| 369 | View Code Duplication | public function fetch_row($request, $counter = false) |
|
| 370 | { |
||
| 371 | global $db_row_count; |
||
| 372 | |||
| 373 | if ($counter !== false) |
||
| 374 | return pg_fetch_row($request, $counter); |
||
| 375 | |||
| 376 | // Reset the row counter... |
||
| 377 | if (!isset($db_row_count[(int) $request])) |
||
| 378 | $db_row_count[(int) $request] = 0; |
||
| 379 | |||
| 380 | // Return the right row. |
||
| 381 | return @pg_fetch_row($request, $db_row_count[(int) $request]++); |
||
| 382 | } |
||
| 383 | |||
| 384 | /** |
||
| 385 | * Free the resultset. |
||
| 386 | * |
||
| 387 | * @param resource $result |
||
| 388 | */ |
||
| 389 | public function free_result($result) |
||
| 390 | { |
||
| 391 | // Just delegate to the native function |
||
| 392 | pg_free_result($result); |
||
| 393 | } |
||
| 394 | |||
| 395 | /** |
||
| 396 | * Get the number of rows in the result. |
||
| 397 | * |
||
| 398 | * @param resource $result |
||
| 399 | * |
||
| 400 | * @return int |
||
| 401 | */ |
||
| 402 | public function num_rows($result) |
||
| 403 | { |
||
| 404 | // simply delegate to the native function |
||
| 405 | return pg_num_rows($result); |
||
| 406 | } |
||
| 407 | |||
| 408 | /** |
||
| 409 | * Get the number of fields in the resultset. |
||
| 410 | * |
||
| 411 | * @param resource $request |
||
| 412 | * |
||
| 413 | * @return int |
||
| 414 | */ |
||
| 415 | public function num_fields($request) |
||
| 416 | { |
||
| 417 | return pg_num_fields($request); |
||
| 418 | } |
||
| 419 | |||
| 420 | /** |
||
| 421 | * Reset the internal result pointer. |
||
| 422 | * |
||
| 423 | * @param boolean $request |
||
| 424 | * @param integer $counter |
||
| 425 | * |
||
| 426 | * @return bool |
||
| 427 | */ |
||
| 428 | public function data_seek($request, $counter) |
||
| 436 | |||
| 437 | /** |
||
| 438 | * Do a transaction. |
||
| 439 | * |
||
| 440 | * @param string $type - the step to perform (i.e. 'begin', 'commit', 'rollback') |
||
| 441 | * @param resource|null $connection = null |
||
| 442 | * |
||
| 443 | * @return bool|resource |
||
| 444 | */ |
||
| 445 | public function db_transaction($type = 'commit', $connection = null) |
||
| 446 | { |
||
| 447 | // Decide which connection to use |
||
| 448 | $connection = $connection === null ? $this->_connection : $connection; |
||
| 449 | |||
| 450 | if ($type == 'begin') |
||
| 451 | { |
||
| 452 | $this->_in_transaction = true; |
||
| 453 | return @pg_query($connection, 'BEGIN'); |
||
| 454 | } |
||
| 455 | elseif ($type == 'rollback') |
||
| 456 | return @pg_query($connection, 'ROLLBACK'); |
||
| 457 | elseif ($type == 'commit') |
||
| 458 | { |
||
| 459 | $this->_in_transaction = false; |
||
| 460 | return @pg_query($connection, 'COMMIT'); |
||
| 461 | } |
||
| 462 | |||
| 463 | return false; |
||
| 464 | } |
||
| 465 | |||
| 466 | /** |
||
| 467 | * Return last error string from the database server |
||
| 468 | * |
||
| 469 | * @param resource|null $connection = null |
||
| 470 | * |
||
| 471 | * @return string |
||
| 472 | */ |
||
| 473 | public function last_error($connection = null) |
||
| 474 | { |
||
| 475 | // Decide which connection to use |
||
| 476 | $connection = $connection === null ? $this->_connection : $connection; |
||
| 477 | |||
| 478 | if (is_resource($connection)) |
||
| 479 | return pg_last_error($connection); |
||
| 480 | } |
||
| 481 | |||
| 482 | /** |
||
| 483 | * Database error. |
||
| 484 | * Backtrace, log, try to fix. |
||
| 485 | * |
||
| 486 | * @param string $db_string |
||
| 487 | * @param resource|null $connection = null |
||
| 488 | * |
||
| 489 | * @throws Elk_Exception |
||
| 490 | */ |
||
| 491 | public function error($db_string, $connection = null) |
||
| 492 | { |
||
| 493 | global $txt, $context, $modSettings, $db_show_debug; |
||
| 494 | |||
| 495 | // We'll try recovering the file and line number the original db query was called from. |
||
| 496 | list ($file, $line) = $this->error_backtrace('', '', 'return', __FILE__, __LINE__); |
||
| 497 | |||
| 498 | // Decide which connection to use |
||
| 499 | $connection = $connection === null ? $this->_connection : $connection; |
||
| 500 | |||
| 501 | // This is the error message... |
||
| 502 | $query_error = @pg_last_error($connection); |
||
| 503 | |||
| 504 | // Log the error. |
||
| 505 | View Code Duplication | if (class_exists('Errors')) |
|
| 506 | { |
||
| 507 | Errors::instance()->log_error($txt['database_error'] . ': ' . $query_error . (!empty($modSettings['enableErrorQueryLogging']) ? "\n\n" . $db_string : ''), 'database', $file, $line); |
||
| 508 | } |
||
| 509 | |||
| 510 | // Nothing's defined yet... just die with it. |
||
| 511 | if (empty($context) || empty($txt)) |
||
| 512 | die($query_error); |
||
| 513 | |||
| 514 | // Show an error message, if possible. |
||
| 515 | $context['error_title'] = $txt['database_error']; |
||
| 516 | View Code Duplication | if (allowedTo('admin_forum')) |
|
| 517 | $context['error_message'] = nl2br($query_error) . '<br />' . $txt['file'] . ': ' . $file . '<br />' . $txt['line'] . ': ' . $line; |
||
| 518 | else |
||
| 519 | $context['error_message'] = $txt['try_again']; |
||
| 520 | |||
| 521 | // Add database version that we know of, for the admin to know. (and ask for support) |
||
| 522 | View Code Duplication | if (allowedTo('admin_forum')) |
|
| 523 | $context['error_message'] .= '<br /><br />' . sprintf($txt['database_error_versions'], $modSettings['elkVersion']); |
||
| 524 | |||
| 525 | View Code Duplication | if (allowedTo('admin_forum') && $db_show_debug === true) |
|
| 526 | $context['error_message'] .= '<br /><br />' . nl2br($db_string); |
||
| 527 | |||
| 528 | // It's already been logged... don't log it again. |
||
| 529 | throw new Elk_Exception($context['error_message'], false); |
||
| 530 | } |
||
| 531 | |||
| 532 | /** |
||
| 533 | * Insert data. |
||
| 534 | * |
||
| 535 | * @param string $method - options 'replace', 'ignore', 'insert' |
||
| 536 | * @param string $table |
||
| 537 | * @param mixed[] $columns |
||
| 538 | * @param mixed[] $data |
||
| 539 | * @param mixed[] $keys |
||
| 540 | * @param bool $disable_trans = false |
||
| 541 | * @param resource|null $connection = null |
||
| 542 | * @throws Elk_Exception |
||
| 543 | */ |
||
| 544 | public function insert($method = 'replace', $table, $columns, $data, $keys, $disable_trans = false, $connection = null) |
||
| 545 | { |
||
| 546 | global $db_prefix; |
||
| 547 | |||
| 548 | $connection = $connection === null ? $this->_connection : $connection; |
||
| 549 | |||
| 550 | // With nothing to insert, simply return. |
||
| 551 | if (empty($data)) |
||
| 552 | return; |
||
| 553 | |||
| 554 | // Inserting data as a single row can be done as a single array. |
||
| 555 | if (!is_array($data[array_rand($data)])) |
||
| 556 | $data = array($data); |
||
| 557 | |||
| 558 | // Replace the prefix holder with the actual prefix. |
||
| 559 | $table = str_replace('{db_prefix}', $db_prefix, $table); |
||
| 560 | |||
| 561 | $priv_trans = false; |
||
| 562 | if ((count($data) > 1 || $method == 'replace') && !$this->_in_transaction && !$disable_trans) |
||
| 563 | { |
||
| 564 | $this->db_transaction('begin', $connection); |
||
| 565 | $priv_trans = true; |
||
| 566 | } |
||
| 567 | |||
| 568 | // PostgreSQL doesn't support replace: we implement a MySQL-compatible behavior instead |
||
| 569 | if ($method == 'replace') |
||
| 570 | { |
||
| 571 | $count = 0; |
||
| 572 | $where = ''; |
||
| 573 | $db_replace_result = 0; |
||
| 574 | foreach ($columns as $columnName => $type) |
||
| 575 | { |
||
| 576 | // Are we restricting the length? |
||
| 577 | if (strpos($type, 'string-') !== false) |
||
| 578 | $actualType = sprintf($columnName . ' = SUBSTRING({string:%1$s}, 1, ' . substr($type, 7) . '), ', $count); |
||
| 579 | else |
||
| 580 | $actualType = sprintf($columnName . ' = {%1$s:%2$s}, ', $type, $count); |
||
| 581 | |||
| 582 | // A key? That's what we were looking for. |
||
| 583 | if (in_array($columnName, $keys)) |
||
| 584 | $where .= (empty($where) ? '' : ' AND ') . substr($actualType, 0, -2); |
||
| 585 | $count++; |
||
| 586 | } |
||
| 587 | |||
| 588 | // Make it so. |
||
| 589 | if (!empty($where) && !empty($data)) |
||
| 590 | { |
||
| 591 | foreach ($data as $k => $entry) |
||
| 592 | { |
||
| 593 | $this->query('', ' |
||
| 594 | DELETE FROM ' . $table . |
||
| 595 | ' WHERE ' . $where, |
||
| 596 | $entry, $connection |
||
| 597 | ); |
||
| 598 | $db_replace_result += (!$this->_db_last_result ? 0 : pg_affected_rows($this->_db_last_result)); |
||
| 599 | } |
||
| 600 | } |
||
| 601 | } |
||
| 602 | |||
| 603 | if (!empty($data)) |
||
| 604 | { |
||
| 605 | // Create the mold for a single row insert. |
||
| 606 | $insertData = '('; |
||
| 607 | View Code Duplication | foreach ($columns as $columnName => $type) |
|
| 608 | { |
||
| 609 | // Are we restricting the length? |
||
| 610 | if (strpos($type, 'string-') !== false) |
||
| 611 | $insertData .= sprintf('SUBSTRING({string:%1$s}, 1, ' . substr($type, 7) . '), ', $columnName); |
||
| 612 | else |
||
| 613 | $insertData .= sprintf('{%1$s:%2$s}, ', $type, $columnName); |
||
| 614 | } |
||
| 615 | $insertData = substr($insertData, 0, -2) . ')'; |
||
| 616 | |||
| 617 | // Create an array consisting of only the columns. |
||
| 618 | $indexed_columns = array_keys($columns); |
||
| 619 | |||
| 620 | // Here's where the variables are injected to the query. |
||
| 621 | $insertRows = array(); |
||
| 622 | foreach ($data as $dataRow) |
||
| 623 | $insertRows[] = $this->quote($insertData, $this->_array_combine($indexed_columns, $dataRow), $connection); |
||
| 624 | |||
| 625 | $inserted_results = 0; |
||
| 626 | $skip_error = $method == 'ignore' || $table === $db_prefix . 'log_errors'; |
||
| 627 | $this->_skip_error = $skip_error; |
||
| 628 | |||
| 629 | // Do the insert. |
||
| 630 | $this->query('', ' |
||
| 631 | INSERT INTO ' . $table . '("' . implode('", "', $indexed_columns) . '") |
||
| 632 | VALUES |
||
| 633 | ' . implode(', |
||
| 634 | ', $insertRows), |
||
| 635 | array( |
||
| 636 | 'security_override' => true, |
||
| 637 | ), |
||
| 638 | $connection |
||
| 639 | ); |
||
| 640 | $inserted_results += (!$this->_db_last_result ? 0 : pg_affected_rows($this->_db_last_result)); |
||
| 641 | |||
| 642 | if (isset($db_replace_result)) |
||
| 643 | $this->_db_replace_result = $db_replace_result + $inserted_results; |
||
| 644 | } |
||
| 645 | |||
| 646 | if ($priv_trans) |
||
| 647 | $this->db_transaction('commit', $connection); |
||
| 648 | } |
||
| 649 | |||
| 650 | /** |
||
| 651 | * Unescape an escaped string! |
||
| 652 | * |
||
| 653 | * @param string $string |
||
| 654 | * |
||
| 655 | * @return string |
||
| 656 | */ |
||
| 657 | public function unescape_string($string) |
||
| 658 | { |
||
| 659 | return strtr($string, array('\'\'' => '\'')); |
||
| 660 | } |
||
| 661 | |||
| 662 | /** |
||
| 663 | * Returns whether the database system supports ignore. |
||
| 664 | * |
||
| 665 | * @return false |
||
| 666 | */ |
||
| 667 | public function support_ignore() |
||
| 668 | { |
||
| 669 | return false; |
||
| 670 | } |
||
| 671 | |||
| 672 | /** |
||
| 673 | * Gets all the necessary INSERTs for the table named table_name. |
||
| 674 | * It goes in 250 row segments. |
||
| 675 | * |
||
| 676 | * @param string $tableName - the table to create the inserts for. |
||
| 677 | * @param bool $new_table |
||
| 678 | * |
||
| 679 | * @return string the query to insert the data back in, or an empty string if the table was empty. |
||
| 680 | * @throws Elk_Exception |
||
| 681 | */ |
||
| 682 | public function insert_sql($tableName, $new_table = false) |
||
| 683 | { |
||
| 684 | global $db_prefix; |
||
| 685 | |||
| 686 | static $start = 0, $num_rows, $fields, $limit; |
||
| 687 | |||
| 688 | View Code Duplication | if ($new_table) |
|
| 689 | { |
||
| 690 | $limit = strstr($tableName, 'log_') !== false ? 500 : 250; |
||
| 691 | $start = 0; |
||
| 692 | } |
||
| 693 | |||
| 694 | $data = ''; |
||
| 695 | $tableName = str_replace('{db_prefix}', $db_prefix, $tableName); |
||
| 696 | |||
| 697 | // This will be handy... |
||
| 698 | $crlf = "\r\n"; |
||
| 699 | |||
| 700 | $result = $this->query('', ' |
||
| 701 | SELECT * |
||
| 702 | FROM ' . $tableName . ' |
||
| 703 | LIMIT ' . $start . ', ' . $limit, |
||
| 704 | array( |
||
| 705 | 'security_override' => true, |
||
| 706 | ) |
||
| 707 | ); |
||
| 708 | |||
| 709 | // The number of rows, just for record keeping and breaking INSERTs up. |
||
| 710 | $num_rows = $this->num_rows($result); |
||
| 711 | |||
| 712 | if ($num_rows == 0) |
||
| 713 | return ''; |
||
| 714 | |||
| 715 | if ($new_table) |
||
| 716 | { |
||
| 717 | $fields = array_keys($this->fetch_assoc($result)); |
||
| 718 | $this->data_seek($result, 0); |
||
| 719 | } |
||
| 720 | |||
| 721 | // Start it off with the basic INSERT INTO. |
||
| 722 | $insert_msg = 'INSERT INTO ' . $tableName . $crlf . "\t" . '(' . implode(', ', $fields) . ')' . $crlf . 'VALUES ' . $crlf . "\t"; |
||
| 723 | |||
| 724 | // Loop through each row. |
||
| 725 | View Code Duplication | while ($row = $this->fetch_assoc($result)) |
|
| 726 | { |
||
| 727 | // Get the fields in this row... |
||
| 728 | $field_list = array(); |
||
| 729 | |||
| 730 | foreach ($row as $key => $item) |
||
| 731 | { |
||
| 732 | // Try to figure out the type of each field. (NULL, number, or 'string'.) |
||
| 733 | if (!isset($item)) |
||
| 734 | $field_list[] = 'NULL'; |
||
| 735 | elseif (is_numeric($item) && (int) $item == $item) |
||
| 736 | $field_list[] = $item; |
||
| 737 | else |
||
| 738 | $field_list[] = '\'' . $this->escape_string($item) . '\''; |
||
| 739 | } |
||
| 740 | |||
| 741 | // 'Insert' the data. |
||
| 742 | $data .= $insert_msg . '(' . implode(', ', $field_list) . ');' . $crlf; |
||
| 743 | } |
||
| 744 | $this->free_result($result); |
||
| 745 | |||
| 746 | $data .= $crlf; |
||
| 747 | |||
| 748 | $start += $limit; |
||
| 749 | |||
| 750 | return $data; |
||
| 751 | } |
||
| 752 | |||
| 753 | /** |
||
| 754 | * Dumps the schema (CREATE) for a table. |
||
| 755 | * |
||
| 756 | * @param string $tableName - the table |
||
| 757 | * |
||
| 758 | * @return string - the CREATE statement as string |
||
| 759 | * @throws Elk_Exception |
||
| 760 | */ |
||
| 761 | public function db_table_sql($tableName) |
||
| 762 | { |
||
| 763 | global $db_prefix; |
||
| 764 | |||
| 765 | $tableName = str_replace('{db_prefix}', $db_prefix, $tableName); |
||
| 766 | |||
| 767 | // This will be needed... |
||
| 768 | $crlf = "\r\n"; |
||
| 769 | |||
| 770 | // Start the create table... |
||
| 771 | $schema_create = 'CREATE TABLE ' . $tableName . ' (' . $crlf; |
||
| 772 | $index_create = ''; |
||
| 773 | $seq_create = ''; |
||
| 774 | |||
| 775 | // Find all the fields. |
||
| 776 | $result = $this->query('', ' |
||
| 777 | SELECT column_name, column_default, is_nullable, data_type, character_maximum_length |
||
| 778 | FROM information_schema.columns |
||
| 779 | WHERE table_name = {string:table} |
||
| 780 | ORDER BY ordinal_position', |
||
| 781 | array( |
||
| 782 | 'table' => $tableName, |
||
| 783 | ) |
||
| 784 | ); |
||
| 785 | while ($row = $this->fetch_assoc($result)) |
||
| 786 | { |
||
| 787 | if ($row['data_type'] == 'character varying') |
||
| 788 | $row['data_type'] = 'varchar'; |
||
| 789 | elseif ($row['data_type'] == 'character') |
||
| 790 | $row['data_type'] = 'char'; |
||
| 791 | |||
| 792 | if ($row['character_maximum_length']) |
||
| 793 | $row['data_type'] .= '(' . $row['character_maximum_length'] . ')'; |
||
| 794 | |||
| 795 | // Make the CREATE for this column. |
||
| 796 | $schema_create .= ' "' . $row['column_name'] . '" ' . $row['data_type'] . ($row['is_nullable'] != 'YES' ? ' NOT NULL' : ''); |
||
| 797 | |||
| 798 | // Add a default...? |
||
| 799 | if (trim($row['column_default']) != '') |
||
| 800 | { |
||
| 801 | $schema_create .= ' default ' . $row['column_default'] . ''; |
||
| 802 | |||
| 803 | // Auto increment? |
||
| 804 | if (preg_match('~nextval\(\'(.+?)\'(.+?)*\)~i', $row['column_default'], $matches) != 0) |
||
| 805 | { |
||
| 806 | // Get to find the next variable first! |
||
| 807 | $count_req = $this->query('', ' |
||
| 808 | SELECT MAX("{raw:column}") |
||
| 809 | FROM {raw:table}', |
||
| 810 | array( |
||
| 811 | 'column' => $row['column_name'], |
||
| 812 | 'table' => $tableName, |
||
| 813 | ) |
||
| 814 | ); |
||
| 815 | list ($max_ind) = $this->fetch_row($count_req); |
||
| 816 | $this->free_result($count_req); |
||
| 817 | |||
| 818 | // Get the right bloody start! |
||
| 819 | $seq_create .= 'CREATE SEQUENCE ' . $matches[1] . ' START WITH ' . ($max_ind + 1) . ';' . $crlf . $crlf; |
||
| 820 | } |
||
| 821 | } |
||
| 822 | |||
| 823 | $schema_create .= ',' . $crlf; |
||
| 824 | } |
||
| 825 | $this->free_result($result); |
||
| 826 | |||
| 827 | // Take off the last comma. |
||
| 828 | $schema_create = substr($schema_create, 0, -strlen($crlf) - 1); |
||
| 829 | |||
| 830 | $result = $this->query('', ' |
||
| 831 | SELECT CASE WHEN i.indisprimary THEN 1 ELSE 0 END AS is_primary, pg_get_indexdef(i.indexrelid) AS inddef |
||
| 832 | FROM pg_class AS c |
||
| 833 | INNER JOIN pg_index AS i ON (i.indrelid = c.oid) |
||
| 834 | INNER JOIN pg_class AS c2 ON (c2.oid = i.indexrelid) |
||
| 835 | WHERE c.relname = {string:table}', |
||
| 836 | array( |
||
| 837 | 'table' => $tableName, |
||
| 838 | ) |
||
| 839 | ); |
||
| 840 | |||
| 841 | while ($row = $this->fetch_assoc($result)) |
||
| 842 | { |
||
| 843 | if ($row['is_primary']) |
||
| 844 | { |
||
| 845 | if (preg_match('~\(([^\)]+?)\)~i', $row['inddef'], $matches) == 0) |
||
| 846 | continue; |
||
| 847 | |||
| 848 | $index_create .= $crlf . 'ALTER TABLE ' . $tableName . ' ADD PRIMARY KEY ("' . $matches[1] . '");'; |
||
| 849 | } |
||
| 850 | else |
||
| 851 | $index_create .= $crlf . $row['inddef'] . ';'; |
||
| 852 | } |
||
| 853 | $this->free_result($result); |
||
| 854 | |||
| 855 | // Finish it off! |
||
| 856 | $schema_create .= $crlf . ');'; |
||
| 857 | |||
| 858 | return $seq_create . $schema_create . $index_create; |
||
| 859 | } |
||
| 860 | |||
| 861 | /** |
||
| 862 | * {@inheritdoc} |
||
| 863 | */ |
||
| 864 | public function db_list_tables($db_name_str = false, $filter = false) |
||
| 865 | { |
||
| 866 | $request = $this->query('', ' |
||
| 867 | SELECT tablename |
||
| 868 | FROM pg_tables |
||
| 869 | WHERE schemaname = {string:schema_public}' . ($filter === false ? '' : ' |
||
| 870 | AND tablename LIKE {string:filter}') . ' |
||
| 871 | ORDER BY tablename', |
||
| 872 | array( |
||
| 873 | 'schema_public' => 'public', |
||
| 874 | 'filter' => $filter, |
||
| 875 | ) |
||
| 876 | ); |
||
| 877 | $tables = array(); |
||
| 878 | while ($row = $this->fetch_row($request)) |
||
| 879 | $tables[] = $row[0]; |
||
| 880 | $this->free_result($request); |
||
| 881 | |||
| 882 | return $tables; |
||
| 883 | } |
||
| 884 | |||
| 885 | /** |
||
| 886 | * Backup $table to $backup_table. |
||
| 887 | * |
||
| 888 | * @param string $table |
||
| 889 | * @param string $backup_table |
||
| 890 | * @throws Elk_Exception |
||
| 891 | */ |
||
| 892 | public function db_backup_table($table, $backup_table) |
||
| 893 | { |
||
| 894 | global $db_prefix; |
||
| 895 | |||
| 896 | $table = str_replace('{db_prefix}', $db_prefix, $table); |
||
| 897 | |||
| 898 | // Do we need to drop it first? |
||
| 899 | $db_table = db_table(); |
||
| 900 | $db_table->db_drop_table($backup_table); |
||
| 901 | |||
| 902 | // @todo Should we create backups of sequences as well? |
||
| 903 | $this->query('', ' |
||
| 904 | CREATE TABLE {raw:backup_table} |
||
| 905 | ( |
||
| 906 | LIKE {raw:table} |
||
| 907 | INCLUDING DEFAULTS |
||
| 908 | )', |
||
| 909 | array( |
||
| 910 | 'backup_table' => $backup_table, |
||
| 911 | 'table' => $table, |
||
| 912 | ) |
||
| 913 | ); |
||
| 914 | |||
| 915 | $this->query('', ' |
||
| 916 | INSERT INTO {raw:backup_table} |
||
| 917 | SELECT * FROM {raw:table}', |
||
| 918 | array( |
||
| 919 | 'backup_table' => $backup_table, |
||
| 920 | 'table' => $table, |
||
| 921 | ) |
||
| 922 | ); |
||
| 923 | } |
||
| 924 | |||
| 925 | /** |
||
| 926 | * Get the server version number. |
||
| 927 | * |
||
| 928 | * @return string - the version |
||
| 929 | */ |
||
| 930 | public function db_server_version() |
||
| 931 | { |
||
| 932 | $version = pg_version(); |
||
| 933 | |||
| 934 | return $version['server']; |
||
| 935 | } |
||
| 936 | |||
| 937 | /** |
||
| 938 | * Get the name (title) of the database system. |
||
| 939 | * |
||
| 940 | * @return string |
||
| 941 | */ |
||
| 942 | public function db_title() |
||
| 943 | { |
||
| 944 | return 'PostgreSQL'; |
||
| 945 | } |
||
| 946 | |||
| 947 | /** |
||
| 948 | * Whether the database system is case sensitive. |
||
| 949 | * |
||
| 950 | * @return boolean |
||
| 951 | */ |
||
| 952 | public function db_case_sensitive() |
||
| 953 | { |
||
| 954 | return true; |
||
| 955 | } |
||
| 956 | |||
| 957 | /** |
||
| 958 | * Quotes identifiers for replacement__callback. |
||
| 959 | * |
||
| 960 | * @param mixed $replacement |
||
| 961 | * @return string |
||
| 962 | * @throws Elk_Exception |
||
| 963 | */ |
||
| 964 | View Code Duplication | protected function _replaceIdentifier($replacement) |
|
| 973 | |||
| 974 | /** |
||
| 975 | * Escape string for the database input |
||
| 976 | * |
||
| 977 | * @param string $string |
||
| 978 | * |
||
| 979 | * @return string |
||
| 980 | */ |
||
| 981 | public function escape_string($string) |
||
| 982 | { |
||
| 983 | return pg_escape_string($string); |
||
| 984 | } |
||
| 985 | |||
| 986 | /** |
||
| 987 | * Fetch next result as association. |
||
| 988 | * |
||
| 989 | * @param resource $request |
||
| 990 | * @param int|bool $counter = false |
||
| 991 | * |
||
| 992 | * @return array |
||
| 993 | */ |
||
| 994 | View Code Duplication | public function fetch_assoc($request, $counter = false) |
|
| 995 | { |
||
| 996 | global $db_row_count; |
||
| 1008 | |||
| 1009 | /** |
||
| 1010 | * Return server info. |
||
| 1011 | * |
||
| 1012 | * @return string |
||
| 1013 | */ |
||
| 1014 | public function db_server_info() |
||
| 1021 | |||
| 1022 | /** |
||
| 1023 | * Return client version. |
||
| 1024 | * |
||
| 1025 | * @return string - the version |
||
| 1026 | */ |
||
| 1027 | public function db_client_version() |
||
| 1033 | |||
| 1034 | /** |
||
| 1035 | * Dummy function really. Doesn't do anything on PostgreSQL. |
||
| 1036 | * |
||
| 1037 | * @param string|null $db_name = null |
||
| 1038 | * @param resource|null $connection = null |
||
| 1039 | * |
||
| 1040 | * @return boolean |
||
| 1041 | */ |
||
| 1042 | public function select_db($db_name = null, $connection = null) |
||
| 1046 | |||
| 1047 | /** |
||
| 1048 | * Returns a reference to the existing instance |
||
| 1049 | */ |
||
| 1050 | public static function db() |
||
| 1054 | |||
| 1055 | /** |
||
| 1056 | * Finds out if the connection is still valid. |
||
| 1057 | * |
||
| 1058 | * @param postgre|null $connection = null |
||
| 1059 | * |
||
| 1060 | * @return bool |
||
| 1061 | */ |
||
| 1062 | public function validConnection($connection = null) |
||
| 1066 | } |
||
| 1067 |
Loading history...
If a method or function can return multiple different values and unless you are sure that you only can receive a single value in this context, we recommend to add an additional type check:
If this a common case that PHP Analyzer should handle natively, please let us know by opening an issue.