vanilla /
garden
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 MySqlDb 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 MySqlDb, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 15 | class MySqlDb extends Db { |
||
| 16 | /// Properties /// |
||
| 17 | |||
| 18 | /** |
||
| 19 | * @var \PDO |
||
| 20 | */ |
||
| 21 | protected $pdo; |
||
| 22 | |||
| 23 | protected $config; |
||
| 24 | |||
| 25 | /// Methods /// |
||
| 26 | |||
| 27 | /** |
||
| 28 | * Initialize an instance of the {@link MySqlDb} class. |
||
| 29 | * |
||
| 30 | * @param array $config The database config. |
||
| 31 | */ |
||
| 32 | public function __construct(array $config = []) { |
||
| 33 | $this->config = $config; |
||
| 34 | } |
||
| 35 | |||
| 36 | /** |
||
| 37 | * {@inheritdoc} |
||
| 38 | */ |
||
| 39 | 6 | public function dropTable($tablename, array $options = []) { |
|
| 48 | |||
| 49 | /** |
||
| 50 | * Surround a field with backticks. |
||
| 51 | * |
||
| 52 | * @param string $field The field to backtick. |
||
| 53 | * @return string Returns the field properly escaped and backticked. |
||
| 54 | * @link http://www.php.net/manual/en/pdo.quote.php#112169 |
||
| 55 | */ |
||
| 56 | 62 | protected function backtick($field) { |
|
| 59 | |||
| 60 | /** |
||
| 61 | * Execute a query on the database. |
||
| 62 | * |
||
| 63 | * @param string $sql The sql query to execute. |
||
| 64 | * @param string $type One of the Db::QUERY_* constants. |
||
| 65 | * |
||
| 66 | * Db::QUERY_READ |
||
| 67 | * : The query reads from the database. |
||
| 68 | * |
||
| 69 | * Db::QUERY_WRITE |
||
| 70 | * : The query writes to the database. |
||
| 71 | * |
||
| 72 | * Db::QUERY_DEFINE |
||
| 73 | * : The query alters the structure of the datbase. |
||
| 74 | * |
||
| 75 | * @param array $options Additional options for the query. |
||
| 76 | * |
||
| 77 | * Db::OPTION_MODE |
||
| 78 | * : Override {@link Db::$mode}. |
||
| 79 | * |
||
| 80 | * @return array|string|PDOStatement|int Returns the result of the query. |
||
| 81 | * |
||
| 82 | * array |
||
| 83 | * : Returns an array when reading from the database and the mode is {@link Db::MODE_EXEC}. |
||
| 84 | * string |
||
| 85 | * : Returns the sql query when the mode is {@link Db::MODE_SQL}. |
||
| 86 | * PDOStatement |
||
| 87 | * : Returns a {@link \PDOStatement} when the mode is {@link Db::MODE_PDO}. |
||
| 88 | * int |
||
| 89 | * : Returns the number of rows affected when performing an update or an insert. |
||
| 90 | */ |
||
| 91 | 58 | public function query($sql, $type = Db::QUERY_READ, $options = []) { |
|
| 92 | 58 | $mode = val(Db::OPTION_MODE, $options, $this->mode); |
|
| 93 | |||
| 94 | 58 | if ($mode & Db::MODE_ECHO) { |
|
| 95 | echo trim($sql, "\n;").";\n\n"; |
||
| 96 | } |
||
| 97 | 58 | if ($mode & Db::MODE_SQL) { |
|
| 98 | return $sql; |
||
| 99 | } |
||
| 100 | |||
| 101 | 58 | $result = null; |
|
| 102 | 58 | if ($mode & Db::MODE_EXEC) { |
|
| 103 | 57 | $result = $this->pdo()->query($sql); |
|
| 104 | |||
| 105 | 57 | if ($type == Db::QUERY_READ) { |
|
| 106 | 47 | $result->setFetchMode(PDO::FETCH_ASSOC); |
|
| 107 | 47 | $result = $result->fetchAll(); |
|
| 108 | 47 | $this->rowCount = count($result); |
|
| 109 | 56 | } elseif (is_object($result) && method_exists($result, 'rowCount')) { |
|
| 110 | 56 | $this->rowCount = $result->rowCount(); |
|
| 111 | 57 | $result = $this->rowCount; |
|
| 112 | } |
||
| 113 | 5 | } elseif ($mode & Db::MODE_PDO) { |
|
| 114 | /* @var \PDOStatement $result */ |
||
| 115 | 5 | $result = $this->pdo()->prepare($sql); |
|
| 116 | } |
||
| 117 | |||
| 118 | 58 | return $result; |
|
| 119 | } |
||
| 120 | |||
| 121 | /** |
||
| 122 | * {@inheritdoc} |
||
| 123 | */ |
||
| 124 | 44 | public function getTableDef($tablename) { |
|
| 125 | 44 | $table = parent::getTableDef($tablename); |
|
| 126 | 44 | if ($table || $table === null) { |
|
| 127 | 44 | return $table; |
|
| 128 | } |
||
| 129 | |||
| 130 | 8 | $ltablename = strtolower($tablename); |
|
| 131 | 8 | $table = val($ltablename, $this->tables, []); |
|
| 132 | 8 | if (!isset($table['columns'])) { |
|
| 133 | 8 | $columns = $this->getColumns($tablename); |
|
| 134 | 8 | if ($columns === null) { |
|
| 135 | // A table with no columns does not exist. |
||
| 136 | 6 | $this->tables[$ltablename] = ['name' => $tablename]; |
|
| 137 | 6 | return null; |
|
| 138 | } |
||
| 139 | |||
| 140 | 8 | $table['columns'] = $columns; |
|
| 141 | } |
||
| 142 | 8 | if (!isset($table['indexes'])) { |
|
| 143 | 8 | $table['indexes'] = $this->getIndexes($tablename); |
|
| 144 | } |
||
| 145 | 8 | $table['name'] = $tablename; |
|
| 146 | 8 | $this->tables[$ltablename] = $table; |
|
| 147 | 8 | return $table; |
|
| 148 | } |
||
| 149 | |||
| 150 | /** |
||
| 151 | * Get the columns for tables and put them in {MySqlDb::$tables}. |
||
| 152 | * |
||
| 153 | * @param string $tablename The table to get the columns for or blank for all columns. |
||
| 154 | * @return array|null Returns an array of columns if {@link $tablename} is specified, or null otherwise. |
||
| 155 | */ |
||
| 156 | 5 | protected function getColumns($tablename = '') { |
|
| 157 | 5 | $ltablename = strtolower($tablename); |
|
| 158 | /* @var \PDOStatement $stmt */ |
||
| 159 | 5 | $stmt = $this->get( |
|
| 160 | 5 | 'information_schema.COLUMNS', |
|
| 161 | [ |
||
| 162 | 5 | 'TABLE_SCHEMA' => $this->getDbName(), |
|
| 163 | 5 | 'TABLE_NAME' => $tablename ? $this->px.$tablename : [Db::OP_LIKE => addcslashes($this->px, '_%').'%'] |
|
| 164 | ], |
||
| 165 | [ |
||
| 166 | 'columns' => [ |
||
| 167 | 'TABLE_NAME', |
||
| 168 | 'COLUMN_TYPE', |
||
| 169 | 'IS_NULLABLE', |
||
| 170 | 'EXTRA', |
||
| 171 | 'COLUMN_KEY', |
||
| 172 | 'COLUMN_DEFAULT', |
||
| 173 | 'COLUMN_NAME' |
||
| 174 | 5 | ], |
|
| 175 | Db::OPTION_MODE => Db::MODE_PDO, |
||
| 176 | 'escapeTable' => false, |
||
| 177 | 'order' => ['TABLE_NAME', 'ORDINAL_POSITION'] |
||
| 178 | ] |
||
| 179 | ); |
||
| 180 | |||
| 181 | 5 | $stmt->execute(); |
|
| 182 | 5 | $tablecolumns = $stmt->fetchAll(PDO::FETCH_ASSOC | PDO::FETCH_GROUP); |
|
| 183 | |||
| 184 | 5 | foreach ($tablecolumns as $ctablename => $cdefs) { |
|
| 185 | 5 | $ctablename = strtolower(ltrim_substr($ctablename, $this->px)); |
|
| 186 | 5 | $columns = []; |
|
| 187 | |||
| 188 | 5 | foreach ($cdefs as $cdef) { |
|
| 189 | $column = [ |
||
| 190 | 5 | 'type' => $this->columnTypeString($cdef['COLUMN_TYPE']), |
|
| 191 | 5 | 'required' => !force_bool($cdef['IS_NULLABLE']), |
|
| 192 | ]; |
||
| 193 | 5 | if ($cdef['EXTRA'] === 'auto_increment') { |
|
| 194 | 1 | $column['autoincrement'] = true; |
|
| 195 | } |
||
| 196 | 5 | if ($cdef['COLUMN_KEY'] === 'PRI') { |
|
| 197 | 3 | $column['primary'] = true; |
|
| 198 | } |
||
| 199 | |||
| 200 | 5 | if ($cdef['COLUMN_DEFAULT'] !== null) { |
|
| 201 | 3 | $column['default'] = $this->forceType($cdef['COLUMN_DEFAULT'], $column['type']); |
|
| 202 | } |
||
| 203 | |||
| 204 | 5 | $columns[$cdef['COLUMN_NAME']] = $column; |
|
| 205 | } |
||
| 206 | 5 | $this->tables[$ctablename]['columns'] = $columns; |
|
| 207 | } |
||
| 208 | 5 | if ($ltablename && isset($this->tables[$ltablename]['columns'])) { |
|
| 209 | 4 | return $this->tables[$ltablename]['columns']; |
|
| 210 | } |
||
| 211 | 4 | return null; |
|
| 212 | } |
||
| 213 | |||
| 214 | /** |
||
| 215 | * {@inheritdoc} |
||
| 216 | */ |
||
| 217 | 48 | public function get($tablename, array $where, array $options = []) { |
|
| 222 | |||
| 223 | /** |
||
| 224 | * Build a sql select statement. |
||
| 225 | * |
||
| 226 | * @param string $table The name of the main table. |
||
| 227 | * @param array $where The where filter. |
||
| 228 | * @param array $options An array of additional query options. |
||
| 229 | * @return string Returns the select statement as a string. |
||
| 230 | * @see Db::get() |
||
| 231 | */ |
||
| 232 | 52 | public function buildSelect($table, array $where, array $options = []) { |
|
| 233 | 52 | $sql = ''; |
|
| 234 | |||
| 235 | // Build the select clause. |
||
| 236 | 52 | if (isset($options['columns'])) { |
|
| 237 | 10 | $columns = array(); |
|
| 238 | 10 | foreach ($options['columns'] as $value) { |
|
| 239 | 10 | $columns[] = $this->backtick($value); |
|
| 240 | } |
||
| 241 | 10 | $sql .= 'select '.implode(', ', $columns); |
|
| 242 | } else { |
||
| 243 | 42 | $sql .= "select *"; |
|
| 244 | } |
||
| 245 | |||
| 246 | // Build the from clause. |
||
| 247 | 52 | if (val('escapeTable', $options, true)) { |
|
| 248 | 46 | $sql .= "\nfrom ".$this->backtick($this->px.$table); |
|
| 249 | } else { |
||
| 250 | 6 | $sql .= "\nfrom $table"; |
|
| 251 | } |
||
| 252 | |||
| 253 | // Build the where clause. |
||
| 254 | 52 | $whereString = $this->buildWhere($where, Db::OP_AND); |
|
| 255 | 52 | if ($whereString) { |
|
| 256 | 46 | $sql .= "\nwhere ".$whereString; |
|
| 257 | } |
||
| 258 | |||
| 259 | // Build the order. |
||
| 260 | 52 | if (isset($options['order'])) { |
|
| 261 | 35 | $order = array_quick($options['order'], Db::ORDER_ASC); |
|
| 262 | 35 | $orders = array(); |
|
| 263 | 35 | foreach ($order as $key => $value) { |
|
| 264 | switch ($value) { |
||
| 265 | 35 | case Db::ORDER_ASC: |
|
| 266 | case Db::ORDER_DESC: |
||
| 267 | 35 | $orders[] = $this->backtick($key)." $value"; |
|
| 268 | 35 | break; |
|
| 269 | default: |
||
| 270 | 35 | trigger_error("Invalid sort direction '$value' for column '$key'.", E_USER_WARNING); |
|
| 271 | } |
||
| 272 | } |
||
| 273 | |||
| 274 | 35 | $sql .= "\norder by ".implode(', ', $orders); |
|
| 275 | } |
||
| 276 | |||
| 277 | // Build the limit, offset. |
||
| 278 | 52 | $limit = 10; |
|
| 279 | 52 | if (isset($options['limit'])) { |
|
| 280 | 12 | $limit = (int)$options['limit']; |
|
| 281 | 12 | $sql .= "\nlimit $limit"; |
|
| 282 | } |
||
| 283 | |||
| 284 | 52 | if (isset($options['offset'])) { |
|
| 285 | $sql .= ' offset '.((int)$options['offset']); |
||
| 286 | 52 | } elseif (isset($options['page'])) { |
|
| 287 | $offset = $limit * ($options['page'] - 1); |
||
| 288 | $sql .= ' offset '.$offset; |
||
| 289 | } |
||
| 290 | |||
| 291 | 52 | return $sql; |
|
| 292 | } |
||
| 293 | |||
| 294 | /** |
||
| 295 | * Build a where clause from a where array. |
||
| 296 | * |
||
| 297 | * @param array $where There where string. |
||
| 298 | * This is an array in the form `['column' => 'value']` with more advanced options for non-equality comparisons. |
||
| 299 | * @param string $op The logical operator to join multiple field comparisons. |
||
| 300 | * @param bool $quotevals Whether or not to quote the where values. |
||
| 301 | * @return string The where string. |
||
| 302 | */ |
||
| 303 | 54 | protected function buildWhere($where, $op = Db::OP_AND, $quotevals = true) { |
|
| 304 | 54 | static $map = array(Db::OP_GT => '>', Db::OP_GTE => '>=', Db::OP_LT => '<', Db::OP_LTE => '<=', Db::OP_LIKE => 'like'); |
|
| 305 | |||
| 306 | 54 | $result = ''; |
|
| 307 | 54 | foreach ($where as $column => $value) { |
|
| 308 | 48 | $btcolumn = $this->backtick($column); |
|
| 309 | |||
| 310 | 48 | if (is_array($value)) { |
|
| 311 | 28 | if (isset($value[0])) { |
|
| 312 | // This is a short in syntax. |
||
| 313 | 2 | $value = [Db::OP_IN => $value]; |
|
| 314 | } |
||
| 315 | |||
| 316 | 28 | foreach ($value as $vop => $rval) { |
|
| 317 | 28 | if ($result) { |
|
| 318 | 6 | $result .= "\n $op "; |
|
| 319 | } |
||
| 320 | |||
| 321 | switch ($vop) { |
||
| 322 | 28 | case Db::OP_AND: |
|
| 323 | case Db::OP_OR: |
||
| 324 | 4 | $innerWhere = [$column => $rval]; |
|
| 325 | $result .= "(\n ". |
||
| 326 | 4 | $this->buildWhere($innerWhere, $vop, $quotevals). |
|
| 327 | 4 | "\n )"; |
|
| 328 | 4 | break; |
|
| 329 | View Code Duplication | case Db::OP_EQ: |
|
|
1 ignored issue
–
show
|
|||
| 330 | 6 | if ($rval === null) { |
|
| 331 | $result .= "$btcolumn is null"; |
||
| 332 | 6 | } elseif (is_array($rval)) { |
|
| 333 | 2 | $result .= "$btcolumn in ".$this->bracketList($rval); |
|
| 334 | } else { |
||
| 335 | 4 | $result .= "$btcolumn = ".$this->quoteVal($rval, $quotevals); |
|
| 336 | } |
||
| 337 | 6 | break; |
|
| 338 | case Db::OP_GT: |
||
| 339 | case Db::OP_GTE: |
||
| 340 | case Db::OP_LT: |
||
| 341 | case Db::OP_LTE: |
||
| 342 | 12 | $result .= "$btcolumn {$map[$vop]} ".$this->quoteVal($rval, $quotevals); |
|
| 343 | 12 | break; |
|
| 344 | case Db::OP_LIKE: |
||
| 345 | 2 | $result .= $this->buildLike($btcolumn, $rval, $quotevals); |
|
| 346 | 2 | break; |
|
| 347 | case Db::OP_IN: |
||
| 348 | // Quote the in values. |
||
| 349 | 4 | $rval = array_map(array($this->pdo, 'quote'), (array)$rval); |
|
| 350 | 4 | $result .= "$btcolumn in (".implode(', ', $rval).')'; |
|
| 351 | 4 | break; |
|
| 352 | View Code Duplication | case Db::OP_NE: |
|
|
1 ignored issue
–
show
|
|||
| 353 | 6 | if ($rval === null) { |
|
| 354 | 2 | $result .= "$btcolumn is not null"; |
|
| 355 | 4 | } elseif (is_array($rval)) { |
|
| 356 | 2 | $result .= "$btcolumn not in ".$this->bracketList($rval); |
|
| 357 | } else { |
||
| 358 | 2 | $result .= "$btcolumn <> ".$this->quoteVal($rval, $quotevals); |
|
| 359 | } |
||
| 360 | 28 | break; |
|
| 361 | } |
||
| 362 | } |
||
| 363 | } else { |
||
| 364 | 22 | if ($result) { |
|
| 365 | 6 | $result .= "\n $op "; |
|
| 366 | } |
||
| 367 | |||
| 368 | // This is just an equality operator. |
||
| 369 | 22 | if ($value === null) { |
|
| 370 | 2 | $result .= "$btcolumn is null"; |
|
| 371 | } else { |
||
| 372 | 48 | $result .= "$btcolumn = ".$this->quoteVal($value, $quotevals); |
|
| 373 | } |
||
| 374 | } |
||
| 375 | } |
||
| 376 | 54 | return $result; |
|
| 377 | } |
||
| 378 | |||
| 379 | /** |
||
| 380 | * Build a like expression. |
||
| 381 | * |
||
| 382 | * @param string $column The column name. |
||
| 383 | * @param mixed $value The right-hand value. |
||
| 384 | * @param bool $quotevals Whether or not to quote the values. |
||
| 385 | * @return string Returns the like expression. |
||
| 386 | */ |
||
| 387 | 1 | protected function buildLike($column, $value, $quotevals) { |
|
| 390 | |||
| 391 | /** |
||
| 392 | * Convert an array into a bracketed list suitable for MySQL clauses. |
||
| 393 | * |
||
| 394 | * @param array $row The row to expand. |
||
| 395 | * @param string $quote The quotes to surroud the items with. There are two special cases. |
||
| 396 | * ' (single quote) |
||
| 397 | * : The row will be passed through {@link PDO::quote()}. |
||
| 398 | * ` (backticks) |
||
| 399 | * : The row will be passed through {@link MySqlDb::backtick()}. |
||
| 400 | * @return string Returns the bracket list. |
||
| 401 | */ |
||
| 402 | 60 | public function bracketList($row, $quote = "'") { |
|
| 416 | |||
| 417 | /** |
||
| 418 | * Gets the {@link PDO} object for this connection. |
||
| 419 | * |
||
| 420 | * @return \PDO |
||
| 421 | */ |
||
| 422 | 31 | public function pdo() { |
|
| 423 | 31 | $dsnParts = array_translate($this->config, ['host', 'dbname', 'port']); |
|
| 424 | 31 | $dsn = 'mysql:'.implode_assoc(';', '=', $dsnParts); |
|
| 425 | |||
| 426 | 31 | if (!isset($this->pdo)) { |
|
| 427 | $this->pdo = new PDO( |
||
| 428 | $dsn, |
||
| 429 | val('username', $this->config, ''), |
||
| 430 | val('password', $this->config, ''), |
||
| 431 | [ |
||
| 432 | PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, |
||
| 433 | PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, |
||
| 434 | PDO::MYSQL_ATTR_INIT_COMMAND => 'set names utf8' |
||
| 435 | ] |
||
| 436 | ); |
||
| 437 | } |
||
| 438 | 31 | return $this->pdo; |
|
| 439 | } |
||
| 440 | |||
| 441 | /** |
||
| 442 | * Optionally quote a where value. |
||
| 443 | * |
||
| 444 | * @param mixed $value The value to quote. |
||
| 445 | * @param bool $quote Whether or not to quote the value. |
||
| 446 | * @return string Returns the value, optionally quoted. |
||
| 447 | */ |
||
| 448 | 21 | public function quoteVal($value, $quote = true) { |
|
| 449 | 21 | if ($value instanceof Literal) { |
|
| 450 | /* @var Literal $value */ |
||
| 451 | return $value->getValue('mysql'); |
||
| 452 | 21 | } elseif ($quote) { |
|
| 453 | 21 | return $this->pdo()->quote($value); |
|
| 454 | } else { |
||
| 455 | return $value; |
||
| 456 | } |
||
| 457 | } |
||
| 458 | |||
| 459 | /** |
||
| 460 | * Get the current database name. |
||
| 461 | * |
||
| 462 | * @return mixed |
||
| 463 | */ |
||
| 464 | 5 | public function getDbName() { |
|
| 467 | |||
| 468 | /** |
||
| 469 | * Parse a column type string and return it in a way that is suitible for a create/alter table statement. |
||
| 470 | * |
||
| 471 | * @param string $typeString The string to parse. |
||
| 472 | * @return string Returns a canonical typestring. |
||
| 473 | */ |
||
| 474 | 18 | protected function columnTypeString($typeString) { |
|
| 475 | 18 | $type = null; |
|
| 476 | |||
| 477 | 18 | if (substr($type, 0, 4) === 'enum') { |
|
| 478 | // This is an enum which will come in as an array. |
||
| 479 | if (preg_match_all("`'([^']+)'`", $typeString, $matches)) { |
||
| 480 | $type = $matches[1]; |
||
| 481 | } |
||
| 482 | } else { |
||
| 483 | 18 | if (preg_match('`([a-z]+)\s*(?:\((\d+(?:\s*,\s*\d+)*)\))?\s*(unsigned)?`', $typeString, $matches)) { |
|
| 484 | // var_dump($matches); |
||
| 485 | 18 | $str = $matches[1]; |
|
| 486 | 18 | $length = val(2, $matches); |
|
| 487 | 18 | $unsigned = val(3, $matches); |
|
| 488 | |||
| 489 | 18 | if (substr($str, 0, 1) == 'u') { |
|
| 490 | $unsigned = true; |
||
| 491 | $str = substr($str, 1); |
||
| 492 | } |
||
| 493 | |||
| 494 | // Remove the length from types without real lengths. |
||
| 495 | 18 | if (in_array($str, array('tinyint', 'smallint', 'mediumint', 'int', 'bigint', 'float', 'double'))) { |
|
| 496 | 17 | $length = null; |
|
| 497 | } |
||
| 498 | |||
| 499 | 18 | $type = $str; |
|
| 500 | 18 | if ($length) { |
|
| 501 | 8 | $length = str_replace(' ', '', $length); |
|
| 502 | 8 | $type .= "($length)"; |
|
| 503 | } |
||
| 504 | 18 | if ($unsigned) { |
|
| 505 | $type .= ' unsigned'; |
||
| 506 | } |
||
| 507 | } |
||
| 508 | } |
||
| 509 | |||
| 510 | 18 | if (!$type) { |
|
| 511 | debug_print_backtrace(); |
||
| 512 | trigger_error("Couldn't parse type $typeString", E_USER_ERROR); |
||
| 513 | } |
||
| 514 | |||
| 515 | 18 | return $type; |
|
| 516 | } |
||
| 517 | |||
| 518 | /** |
||
| 519 | * Get the indexes from the database. |
||
| 520 | * |
||
| 521 | * @param string $tablename The name of the table to get the indexes for or an empty string to get all indexes. |
||
| 522 | * @return array|null |
||
| 523 | */ |
||
| 524 | 5 | protected function getIndexes($tablename = '') { |
|
| 525 | 5 | $ltablename = strtolower($tablename); |
|
| 526 | /* @var \PDOStatement */ |
||
| 527 | 5 | $stmt = $this->get( |
|
| 528 | 5 | 'information_schema.STATISTICS', |
|
| 529 | [ |
||
| 530 | 5 | 'TABLE_SCHEMA' => $this->getDbName(), |
|
| 531 | 5 | 'TABLE_NAME' => $tablename ? $this->px.$tablename : [Db::OP_LIKE => addcslashes($this->px, '_%').'%'] |
|
| 532 | ], |
||
| 533 | [ |
||
| 534 | 'columns' => [ |
||
| 535 | 'INDEX_NAME', |
||
| 536 | 'TABLE_NAME', |
||
| 537 | 'NON_UNIQUE', |
||
| 538 | 'COLUMN_NAME' |
||
| 539 | 5 | ], |
|
| 540 | 'escapeTable' => false, |
||
| 541 | 'order' => ['TABLE_NAME', 'INDEX_NAME', 'SEQ_IN_INDEX'], |
||
| 542 | Db::OPTION_MODE => Db::MODE_PDO |
||
| 543 | ] |
||
| 544 | ); |
||
| 545 | |||
| 546 | 5 | $stmt->execute(); |
|
| 547 | 5 | $indexDefs = $stmt->fetchAll(PDO::FETCH_ASSOC | PDO::FETCH_GROUP); |
|
| 548 | |||
| 549 | 5 | foreach ($indexDefs as $indexName => $indexRows) { |
|
| 550 | 5 | $row = reset($indexRows); |
|
| 551 | 5 | $itablename = strtolower(ltrim_substr($row['TABLE_NAME'], $this->px)); |
|
| 552 | $index = [ |
||
| 553 | 5 | 'name' => $indexName, |
|
| 554 | 5 | 'columns' => array_column($indexRows, 'COLUMN_NAME') |
|
| 555 | ]; |
||
| 556 | |||
| 557 | 5 | if ($indexName === 'PRIMARY') { |
|
| 558 | 3 | $index['type'] = Db::INDEX_PK; |
|
| 559 | 3 | $this->tables[$itablename]['indexes'][Db::INDEX_PK] = $index; |
|
| 560 | } else { |
||
| 561 | 3 | $index['type'] = $row['NON_UNIQUE'] ? Db::INDEX_IX : Db::INDEX_UNIQUE; |
|
| 562 | 5 | $this->tables[$itablename]['indexes'][] = $index; |
|
| 563 | } |
||
| 564 | } |
||
| 565 | |||
| 566 | 5 | if ($ltablename) { |
|
| 567 | 4 | return valr([$ltablename, 'indexes'], $this->tables, []); |
|
| 568 | } |
||
| 569 | 1 | return null; |
|
| 570 | } |
||
| 571 | |||
| 572 | /** |
||
| 573 | * {@inheritdoc} |
||
| 574 | */ |
||
| 575 | 2 | public function getAllTables($withDefs = false) { |
|
| 576 | 2 | $tables = parent::getAllTables($withDefs); |
|
| 577 | 2 | if ($tables !== null) { |
|
| 578 | 2 | return $tables; |
|
| 579 | } |
||
| 580 | |||
| 581 | // Grab the tablenames first. |
||
| 582 | 2 | if ($this->allTablesFetched & Db::FETCH_TABLENAMES) { |
|
| 583 | 2 | $tablenames = array_keys($this->tables); |
|
| 584 | } else { |
||
| 585 | $tablenames = $this->getTablenames(); |
||
| 586 | $this->tables = []; |
||
| 587 | foreach ($tablenames as $tablename) { |
||
| 588 | $this->tables[strtolower($tablename)] = ['name' => $tablename]; |
||
| 589 | } |
||
| 590 | $this->allTablesFetched = Db::FETCH_TABLENAMES; |
||
| 591 | } |
||
| 592 | |||
| 593 | 2 | if (!$withDefs) { |
|
| 594 | return $tablenames; |
||
| 595 | } |
||
| 596 | |||
| 597 | 2 | $this->getColumns(); |
|
| 598 | 2 | $this->allTablesFetched |= Db::FETCH_COLUMNS; |
|
| 599 | |||
| 600 | 2 | $this->getIndexes(); |
|
| 601 | 2 | $this->allTablesFetched |= Db::FETCH_INDEXES; |
|
| 602 | |||
| 603 | 2 | return $this->tables; |
|
| 604 | } |
||
| 605 | |||
| 606 | /** |
||
| 607 | * Get the all of tablenames in the database. |
||
| 608 | * |
||
| 609 | * @return array Returns an array of table names with prefixes stripped. |
||
| 610 | */ |
||
| 611 | View Code Duplication | protected function getTablenames() { |
|
| 612 | // Get the table names. |
||
| 613 | $tables = (array)$this->get( |
||
| 614 | 'information_schema.TABLES', |
||
| 615 | [ |
||
| 616 | 'TABLE_SCHEMA' => $this->getDbName(), |
||
| 617 | 'TABLE_NAME' => [Db::OP_LIKE => addcslashes($this->px, '_%').'%'] |
||
| 618 | ], |
||
| 619 | [ |
||
| 620 | 'columns' => ['TABLE_NAME'], |
||
| 621 | 'escapeTable' => false |
||
| 622 | ] |
||
| 623 | ); |
||
| 624 | |||
| 625 | // Strip the table prefixes. |
||
| 626 | $tables = array_map(function ($name) { |
||
| 627 | return ltrim_substr($name, $this->px); |
||
| 628 | }, array_column($tables, 'TABLE_NAME')); |
||
| 629 | |||
| 630 | return $tables; |
||
| 631 | } |
||
| 632 | |||
| 633 | /** |
||
| 634 | * {@inheritdoc} |
||
| 635 | */ |
||
| 636 | 14 | public function insert($tablename, array $rows, array $options = []) { |
|
| 637 | 14 | $sql = $this->buildInsert($tablename, $rows, true, $options); |
|
| 638 | 14 | $this->query($sql, Db::QUERY_WRITE); |
|
| 639 | 14 | $id = $this->pdo()->lastInsertId(); |
|
| 640 | 14 | if (is_numeric($id)) { |
|
| 641 | 14 | return (int)$id; |
|
| 642 | } else { |
||
| 643 | return $id; |
||
| 644 | } |
||
| 645 | } |
||
| 646 | |||
| 647 | /** |
||
| 648 | * Build an insert statement. |
||
| 649 | * |
||
| 650 | * @param string $tablename The name of the table to insert to. |
||
| 651 | * @param array $row The row to insert. |
||
| 652 | * @param bool $quotevals Whether or not to quote the values. |
||
| 653 | * @param array $options An array of options for the insert. See {@link Db::insert} for the options. |
||
| 654 | * @return string Returns the the sql string of the insert statement. |
||
| 655 | */ |
||
| 656 | 24 | protected function buildInsert($tablename, array $row, $quotevals = true, $options = []) { |
|
| 657 | 24 | if (val(Db::OPTION_UPSERT, $options)) { |
|
| 658 | 2 | return $this->buildUpsert($tablename, $row, $quotevals, $options); |
|
| 659 | 24 | } elseif (val(Db::OPTION_IGNORE, $options)) { |
|
| 660 | 2 | $sql = 'insert ignore '; |
|
| 661 | 23 | } elseif (val(Db::OPTION_REPLACE, $options)) { |
|
| 662 | 2 | $sql = 'replace '; |
|
| 663 | } else { |
||
| 664 | 22 | $sql = 'insert '; |
|
| 665 | } |
||
| 666 | 24 | $sql .= $this->backtick($this->px.$tablename); |
|
| 667 | |||
| 668 | // Add the list of values. |
||
| 669 | $sql .= |
||
| 670 | 24 | "\n".$this->bracketList(array_keys($row), '`'). |
|
| 671 | 24 | "\nvalues".$this->bracketList($row, $quotevals ? "'" : ''); |
|
| 672 | |||
| 673 | 24 | return $sql; |
|
| 674 | } |
||
| 675 | |||
| 676 | /** |
||
| 677 | * Build an upsert statement. |
||
| 678 | * |
||
| 679 | * An upsert statement is an insert on duplicate key statement in MySQL. |
||
| 680 | * |
||
| 681 | * @param string $tablename The name of the table to update. |
||
| 682 | * @param array $row The row to insert or update. |
||
| 683 | * @param bool $quotevals Whether or not to quote the values in the row. |
||
| 684 | * @param array $options An array of additional query options. |
||
| 685 | * @return string Returns the upsert statement as a string. |
||
| 686 | */ |
||
| 687 | 2 | protected function buildUpsert($tablename, array $row, $quotevals = true, $options = []) { |
|
| 688 | // Build the initial insert statement first. |
||
| 689 | 2 | unset($options[Db::OPTION_UPSERT]); |
|
| 690 | 2 | $sql = $this->buildInsert($tablename, $row, $quotevals, $options); |
|
| 691 | |||
| 692 | // Add the duplicate key stuff. |
||
| 693 | 2 | $updates = []; |
|
| 694 | 2 | foreach ($row as $key => $value) { |
|
| 695 | 2 | $updates[] = $this->backtick($key).' = values('.$this->backtick($key).')'; |
|
| 696 | } |
||
| 697 | 2 | $sql .= "\non duplicate key update ".implode(', ', $updates); |
|
| 698 | |||
| 699 | 2 | return $sql; |
|
| 700 | } |
||
| 701 | |||
| 702 | /** |
||
| 703 | * {@inheritdoc} |
||
| 704 | */ |
||
| 705 | 34 | public function load($tablename, $rows, array $options = []) { |
|
| 706 | 34 | $count = 0; |
|
| 707 | 34 | $first = true; |
|
| 708 | 34 | $spec = []; |
|
| 709 | 34 | $stmt = null; |
|
| 710 | |||
| 711 | // Loop over the rows and insert them with the statement. |
||
| 712 | 34 | foreach ($rows as $row) { |
|
| 713 | 34 | if ($first) { |
|
| 714 | // Build the insert statement from the first row. |
||
| 715 | 34 | foreach ($row as $key => $value) { |
|
| 716 | 34 | $spec[$key] = $this->paramName($key); |
|
| 717 | } |
||
| 718 | |||
| 719 | 34 | $sql = $this->buildInsert($tablename, $spec, false, $options); |
|
| 720 | 34 | $stmt = $this->pdo()->prepare($sql); |
|
| 721 | 34 | $first = false; |
|
| 722 | } |
||
| 723 | |||
| 724 | 34 | $params = array_translate($row, $spec); |
|
| 725 | 34 | $stmt->execute($params); |
|
| 726 | 34 | $count += $stmt->rowCount(); |
|
| 727 | } |
||
| 728 | |||
| 729 | 34 | return $count; |
|
| 730 | } |
||
| 731 | |||
| 732 | /** |
||
| 733 | * Make a valid pdo parameter name from a string. |
||
| 734 | * |
||
| 735 | * This method replaces invalid placeholder characters with underscores. |
||
| 736 | * |
||
| 737 | * @param string $name The name to replace. |
||
| 738 | * @return string |
||
| 739 | */ |
||
| 740 | 34 | protected function paramName($name) { |
|
| 744 | |||
| 745 | /** |
||
| 746 | * {@inheritdoc} |
||
| 747 | */ |
||
| 748 | 6 | public function update($tablename, array $set, array $where, array $options = []) { |
|
| 749 | 6 | $sql = $this->buildUpdate($tablename, $set, $where, true, $options); |
|
| 750 | 6 | $result = $this->query($sql, Db::QUERY_WRITE); |
|
| 751 | |||
| 752 | 6 | if ($result instanceof \PDOStatement) { |
|
| 753 | /* @var \PDOStatement $result */ |
||
| 754 | return $result->rowCount(); |
||
| 755 | } |
||
| 756 | 6 | return $result; |
|
| 757 | } |
||
| 758 | |||
| 759 | /** |
||
| 760 | * Build a sql update statement. |
||
| 761 | * |
||
| 762 | * @param string $tablename The name of the table to update. |
||
| 763 | * @param array $set An array of columns to set. |
||
| 764 | * @param array $where The where filter. |
||
| 765 | * @param bool $quotevals Whether or not to quote the values. |
||
| 766 | * @param array $options Additional options for the query. |
||
| 767 | * @return string Returns the update statement as a string. |
||
| 768 | */ |
||
| 769 | 2 | View Code Duplication | protected function buildUpdate($tablename, array $set, array $where, $quotevals = true, array $options = []) { |
| 787 | |||
| 788 | /** |
||
| 789 | * {@inheritdoc} |
||
| 790 | */ |
||
| 791 | 30 | public function delete($tablename, array $where, array $options = []) { |
|
| 806 | |||
| 807 | /** |
||
| 808 | * {@inheritdoc} |
||
| 809 | */ |
||
| 810 | 8 | protected function createTable($tablename, array $tabledef, array $options = []) { |
|
| 835 | |||
| 836 | /** |
||
| 837 | * Construct a column definition string from an array defintion. |
||
| 838 | * |
||
| 839 | * @param string $name The name of the column. |
||
| 840 | * @param array $def The column definition. |
||
| 841 | * @return string Returns a string representing the column definition. |
||
| 842 | */ |
||
| 843 | 8 | protected function columnDefString($name, array $def) { |
|
| 860 | |||
| 861 | /** |
||
| 862 | * Return the SDL string that defines an index. |
||
| 863 | * |
||
| 864 | * @param string $tablename The name of the table that the index is on. |
||
| 865 | * @param array $def The index defintion. This definition should have the following keys. |
||
| 866 | * |
||
| 867 | * columns |
||
| 868 | * : An array of columns in the index. |
||
| 869 | * type |
||
| 870 | * : One of "index", "unique", or "primary". |
||
| 871 | * @return null|string Returns the index string or null if the index is not correct. |
||
| 872 | */ |
||
| 873 | 8 | protected function indexDefString($tablename, array $def) { |
|
| 885 | |||
| 886 | /** |
||
| 887 | * {@inheritdoc} |
||
| 888 | */ |
||
| 889 | 4 | protected function alterTable($tablename, array $alterdef, array $options = []) { |
|
| 928 | |||
| 929 | /** |
||
| 930 | * Get an array of column orders so that added columns can be slotted into their correct spot. |
||
| 931 | * |
||
| 932 | * @param array $cdefs An array of column definitions. |
||
| 933 | * @return array Returns an array of column orders suitable for an `alter table` statement. |
||
| 934 | */ |
||
| 935 | 4 | protected function getColumnOrders($cdefs) { |
|
| 945 | |||
| 946 | /** |
||
| 947 | * Force a value into the appropriate php type based on its Sqlite type. |
||
| 948 | * |
||
| 949 | * @param mixed $value The value to force. |
||
| 950 | * @param string $type The sqlite type name. |
||
| 951 | * @return mixed Returns $value cast to the appropriate type. |
||
| 952 | */ |
||
| 953 | 3 | View Code Duplication | protected function forceType($value, $type) { |
| 968 | } |
||
| 969 |
Loading history...
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.