tsukasa-mixer /
QueryBuilder
| Total Complexity | 150 |
| Total Lines | 884 |
| Duplicated Lines | 0 % |
| Changes | 6 | ||
| Bugs | 0 | Features | 1 |
Complex classes like BaseAdapter 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.
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 BaseAdapter, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 13 | abstract class BaseAdapter implements ISQLGenerator |
||
| 14 | { |
||
| 15 | /** |
||
| 16 | * @var string |
||
| 17 | */ |
||
| 18 | protected $tablePrefix; |
||
| 19 | /** |
||
| 20 | * @var null|Connection |
||
| 21 | */ |
||
| 22 | protected $driver; |
||
| 23 | |||
| 24 | public function __construct($driver = null) |
||
| 27 | } |
||
| 28 | |||
| 29 | /** |
||
| 30 | * @return string |
||
| 31 | */ |
||
| 32 | public function getTablePrefix() |
||
| 35 | } |
||
| 36 | |||
| 37 | /** |
||
| 38 | * @return BaseLookupCollection|ILookupCollection |
||
| 39 | */ |
||
| 40 | abstract public function getLookupCollection(); |
||
| 41 | |||
| 42 | /** |
||
| 43 | * Quotes a column name for use in a query. |
||
| 44 | * If the column name contains prefix, the prefix will also be properly quoted. |
||
| 45 | * If the column name is already quoted or contains '(', '[[' or '{{', then this method will do nothing. |
||
| 46 | * |
||
| 47 | * @param string $name column name |
||
| 48 | * @return string the properly quoted column name |
||
| 49 | * @see quoteSimpleColumnName() |
||
| 50 | */ |
||
| 51 | public function quoteColumn($name) |
||
| 52 | { |
||
| 53 | if (strpos($name, '(') !== false || strpos($name, '[[') !== false || strpos($name, '{{') !== false) { |
||
| 54 | return $name; |
||
| 55 | } |
||
| 56 | if (($pos = strrpos($name, '.')) !== false) { |
||
| 57 | $prefix = $this->quoteTableName(substr($name, 0, $pos)) . '.'; |
||
| 58 | $name = substr($name, $pos + 1); |
||
| 59 | } |
||
| 60 | else { |
||
| 61 | $prefix = ''; |
||
| 62 | } |
||
| 63 | return $prefix . $this->quoteSimpleColumnName($name); |
||
| 64 | } |
||
| 65 | |||
| 66 | /** |
||
| 67 | * Quotes a simple column name for use in a query. |
||
| 68 | * A simple column name should contain the column name only without any prefix. |
||
| 69 | * If the column name is already quoted or is the asterisk character '*', this method will do nothing. |
||
| 70 | * @param string $name column name |
||
| 71 | * @return string the properly quoted column name |
||
| 72 | */ |
||
| 73 | public function quoteSimpleColumnName($name) |
||
| 74 | { |
||
| 75 | return strpos($name, '"') !== false || $name === '*' ? $name : '"' . $name . '"'; |
||
| 76 | } |
||
| 77 | |||
| 78 | /** |
||
| 79 | * Returns the actual name of a given table name. |
||
| 80 | * This method will strip off curly brackets from the given table name |
||
| 81 | * and replace the percentage character '%' with [[Connection::tablePrefix]]. |
||
| 82 | * @param string $name the table name to be converted |
||
| 83 | * @return string the real name of the given table name |
||
| 84 | */ |
||
| 85 | public function getRawTableName($name) |
||
| 86 | { |
||
| 87 | if (strpos($name, '{{') !== false) { |
||
| 88 | $name = preg_replace('/\\{\\{(.*?)\\}\\}/', '\1', $name); |
||
| 89 | |||
| 90 | if (strpos($name, '%') !== false) { |
||
| 91 | return str_replace('%', $this->getTablePrefix() ?: '', $name); |
||
| 92 | } |
||
| 93 | } |
||
| 94 | |||
| 95 | return $name; |
||
| 96 | } |
||
| 97 | |||
| 98 | /** |
||
| 99 | * @return null|Connection |
||
| 100 | */ |
||
| 101 | public function getDriver() |
||
| 102 | { |
||
| 103 | return $this->driver; |
||
| 104 | } |
||
| 105 | |||
| 106 | /** |
||
| 107 | * @param Connection $driver |
||
| 108 | * @return ISQLGenerator |
||
| 109 | */ |
||
| 110 | public function setDriver(Connection $driver) |
||
| 111 | { |
||
| 112 | $this->driver = $driver; |
||
| 113 | return $this; |
||
| 114 | } |
||
| 115 | |||
| 116 | /** |
||
| 117 | * Quotes a string value for use in a query. |
||
| 118 | * Note that if the parameter is not a string, it will be returned without change. |
||
| 119 | * |
||
| 120 | * Note sqlite3: |
||
| 121 | * A string constant is formed by enclosing the string in single quotes ('). |
||
| 122 | * A single quote within the string can be encoded by putting two single |
||
| 123 | * quotes in a row - as in Pascal. C-style escapes using the backslash |
||
| 124 | * character are not supported because they are not standard SQL. |
||
| 125 | * |
||
| 126 | * @param string $value string to be quoted |
||
| 127 | * @return string the properly quoted string |
||
| 128 | * @see http://www.php.net/manual/en/function.PDO-quote.php |
||
| 129 | */ |
||
| 130 | public function quoteValue($value) |
||
| 131 | { |
||
| 132 | if ($value instanceof IToSql) { |
||
|
introduced
by
|
|||
| 133 | return $value->toSql(); |
||
| 134 | } |
||
| 135 | else if ($value === true || strtolower($value) === 'true') { |
||
| 136 | return 'TRUE'; |
||
| 137 | } |
||
| 138 | else if ($value === false || strtolower($value) === 'false') { |
||
| 139 | return 'FALSE'; |
||
| 140 | } |
||
| 141 | else if ($value === null || strtolower($value) === 'null') { |
||
| 142 | return 'NULL'; |
||
| 143 | } |
||
| 144 | else if (is_string($value) && $driver = $this->getDriver()) { |
||
| 145 | return $driver->quote($value); |
||
| 146 | } |
||
| 147 | |||
| 148 | return $value; |
||
| 149 | } |
||
| 150 | |||
| 151 | /** |
||
| 152 | * Quotes a table name for use in a query. |
||
| 153 | * If the table name contains schema prefix, the prefix will also be properly quoted. |
||
| 154 | * If the table name is already quoted or contains '(' or '{{', |
||
| 155 | * then this method will do nothing. |
||
| 156 | * @param string $name table name |
||
| 157 | * @return string the properly quoted table name |
||
| 158 | * @see quoteSimpleTableName() |
||
| 159 | */ |
||
| 160 | public function quoteTableName($name) |
||
| 161 | { |
||
| 162 | if (strpos($name, '(') !== false || strpos($name, '{{') !== false) { |
||
| 163 | return $name; |
||
| 164 | } |
||
| 165 | if (strpos($name, '.') === false) { |
||
| 166 | return $this->quoteSimpleTableName($name); |
||
| 167 | } |
||
| 168 | $parts = explode('.', $name); |
||
| 169 | foreach ($parts as $i => $part) { |
||
| 170 | $parts[$i] = $this->quoteSimpleTableName($part); |
||
| 171 | } |
||
| 172 | return implode('.', $parts); |
||
| 173 | } |
||
| 174 | |||
| 175 | /** |
||
| 176 | * Quotes a simple table name for use in a query. |
||
| 177 | * A simple table name should contain the table name only without any schema prefix. |
||
| 178 | * If the table name is already quoted, this method will do nothing. |
||
| 179 | * @param string $name table name |
||
| 180 | * @return string the properly quoted table name |
||
| 181 | */ |
||
| 182 | public function quoteSimpleTableName($name) |
||
| 183 | { |
||
| 184 | return strpos($name, "'") !== false ? $name : "'" . $name . "'"; |
||
| 185 | } |
||
| 186 | |||
| 187 | /** |
||
| 188 | * @param $sql |
||
| 189 | * @return mixed |
||
| 190 | * |
||
| 191 | */ |
||
| 192 | public function quoteSql($sql) |
||
| 193 | { |
||
| 194 | // $tablePrefix = $this->tablePrefix; |
||
| 195 | // |
||
| 196 | // if (preg_match('/\\{\\{(%?[\w\-\. ]+%?)\\}\\}|\\[\\[([\w\-\. ]+)\\]\\]|\\[\\[([\w\-\. ]+)\\]\\][\s]*=[\s]*\\@([\w\-\. \/\%\:]+)\\@/', $sql)) |
||
| 197 | // { |
||
| 198 | // return preg_replace_callback('/(\\{\\{(%?[\w\-\. ]+%?)\\}\\}|\\[\\[([\w\-\. ]+)\\]\\])|\\@([\w\-\. \/\%\:]+)\\@/', |
||
| 199 | // function ($matches) use ($tablePrefix) { |
||
| 200 | // if (isset($matches[4])) { |
||
| 201 | // return $this->quoteValue($this->convertToDbValue($matches[4])); |
||
| 202 | // } else if (isset($matches[3])) { |
||
| 203 | // return $this->quoteColumn($matches[3]); |
||
| 204 | // } else { |
||
| 205 | // return str_replace('%', $tablePrefix, $this->quoteTableName($matches[2])); |
||
| 206 | // } |
||
| 207 | // }, $sql); |
||
| 208 | // } |
||
| 209 | |||
| 210 | return $sql; |
||
| 211 | } |
||
| 212 | |||
| 213 | public function convertToDbValue($rawValue) |
||
| 214 | { |
||
| 215 | if ($rawValue === true || $rawValue === false || $rawValue === 'true' || $rawValue === 'false') { |
||
| 216 | return $this->getBoolean($rawValue); |
||
| 217 | } |
||
| 218 | |||
| 219 | if ($rawValue === 'null' || $rawValue === null) { |
||
| 220 | return 'NULL'; |
||
| 221 | } |
||
| 222 | |||
| 223 | return $rawValue; |
||
| 224 | } |
||
| 225 | |||
| 226 | /** |
||
| 227 | * Checks to see if the given limit is effective. |
||
| 228 | * @param mixed $limit the given limit |
||
| 229 | * @return boolean whether the limit is effective |
||
| 230 | */ |
||
| 231 | public function hasLimit($limit) |
||
| 232 | { |
||
| 233 | return (int)$limit > 0; |
||
| 234 | } |
||
| 235 | |||
| 236 | /** |
||
| 237 | * Checks to see if the given offset is effective. |
||
| 238 | * @param mixed $offset the given offset |
||
| 239 | * @return boolean whether the offset is effective |
||
| 240 | */ |
||
| 241 | public function hasOffset($offset) |
||
| 242 | { |
||
| 243 | return (int)$offset > 0; |
||
| 244 | } |
||
| 245 | |||
| 246 | /** |
||
| 247 | * @param integer $limit |
||
| 248 | * @param integer $offset |
||
| 249 | * @return string the LIMIT and OFFSET clauses |
||
| 250 | */ |
||
| 251 | abstract public function sqlLimitOffset($limit = null, $offset = null); |
||
| 252 | |||
| 253 | /** |
||
| 254 | * @param $columns |
||
| 255 | * @return string |
||
| 256 | */ |
||
| 257 | public function buildColumns($columns) |
||
| 258 | { |
||
| 259 | if (!is_array($columns)) { |
||
| 260 | if ($columns instanceof Aggregation) { |
||
| 261 | $columns->setFieldSql($this->buildColumns($columns->getField())); |
||
| 262 | return $columns->toSQL(); |
||
| 263 | } |
||
| 264 | |||
| 265 | if (strpos($columns, '(') !== false) { |
||
| 266 | return $columns; |
||
| 267 | } |
||
| 268 | |||
| 269 | $columns = preg_split('/\s*,\s*/', $columns, -1, PREG_SPLIT_NO_EMPTY); |
||
| 270 | if ($columns === false) { |
||
| 271 | return ''; |
||
| 272 | } |
||
| 273 | } |
||
| 274 | foreach ($columns as $i => $column) { |
||
| 275 | if ($column instanceof Expression) { |
||
| 276 | $columns[$i] = $column->toSQL(); |
||
| 277 | } |
||
| 278 | else if (strpos($column, '(') === false) { |
||
| 279 | $columns[$i] = $this->quoteColumn($column); |
||
| 280 | } |
||
| 281 | } |
||
| 282 | |||
| 283 | if (is_array($columns)) { |
||
| 284 | return implode(', ', $columns); |
||
| 285 | } |
||
| 286 | |||
| 287 | return $columns; |
||
| 288 | } |
||
| 289 | |||
| 290 | /** |
||
| 291 | * Builds a SQL statement for adding a primary key constraint to an existing table. |
||
| 292 | * @param string $name the name of the primary key constraint. |
||
| 293 | * @param string $tableName the table that the primary key constraint will be added to. |
||
| 294 | * @param string|array $columns comma separated string or array of columns that the primary key will consist of. |
||
| 295 | * @return string the SQL statement for adding a primary key constraint to an existing table. |
||
| 296 | */ |
||
| 297 | public function sqlAddPrimaryKey($tableName, $name, $columns) |
||
| 298 | { |
||
| 299 | if (is_string($columns)) { |
||
| 300 | $columns = [ |
||
| 301 | preg_split('/\s*,\s*/', $columns, -1, PREG_SPLIT_NO_EMPTY) |
||
| 302 | ]; |
||
| 303 | } |
||
| 304 | foreach ($columns as $i => $col) { |
||
| 305 | $columns[$i] = $this->quoteColumn($col); |
||
| 306 | } |
||
| 307 | return 'ALTER TABLE ' . $this->quoteTableName($tableName) . ' ADD CONSTRAINT ' |
||
| 308 | . $this->quoteColumn($name) . ' PRIMARY KEY (' . implode(', ', $columns) . ')'; |
||
| 309 | } |
||
| 310 | |||
| 311 | /** |
||
| 312 | * Builds a SQL statement for removing a primary key constraint to an existing table. |
||
| 313 | * @param string $name the name of the primary key constraint to be removed. |
||
| 314 | * @param string $tableName the table that the primary key constraint will be removed from. |
||
| 315 | * @return string the SQL statement for removing a primary key constraint from an existing table. |
||
| 316 | */ |
||
| 317 | public function sqlDropPrimaryKey($tableName, $name) |
||
| 318 | { |
||
| 319 | return 'ALTER TABLE ' . $this->quoteTableName($tableName) . ' DROP PRIMARY KEY ' . $this->quoteColumn($name); |
||
| 320 | } |
||
| 321 | |||
| 322 | public function sqlAlterColumn($tableName, $column, $type) |
||
| 323 | { |
||
| 324 | return 'ALTER TABLE ' . $this->quoteTableName($tableName) . ' CHANGE ' |
||
| 325 | . $this->quoteColumn($column) . ' ' |
||
| 326 | . $this->quoteColumn($column) . ' ' |
||
| 327 | . $type; |
||
| 328 | } |
||
| 329 | |||
| 330 | /** |
||
| 331 | * @param $tableName |
||
| 332 | * @param array $rows |
||
| 333 | * @param string $options Sql Options |
||
| 334 | * @return string |
||
| 335 | */ |
||
| 336 | public function sqlInsert($tableName, array $rows, $options = '') |
||
| 337 | { |
||
| 338 | if (!is_string($options)) { |
||
| 339 | $options = ''; |
||
| 340 | } |
||
| 341 | |||
| 342 | if ($options) { |
||
| 343 | $options = " {$options} "; |
||
| 344 | } |
||
| 345 | |||
| 346 | if (is_array($rows) && isset($rows[0])) { |
||
| 347 | $values = []; |
||
| 348 | $columns = array_map([$this, 'quoteColumn'], array_keys($rows[0])); |
||
| 349 | |||
| 350 | foreach ($rows as $row) { |
||
| 351 | $record = []; |
||
| 352 | foreach ($row as $value) { |
||
| 353 | $record[] = $value = $this->quoteValue($value); |
||
| 354 | } |
||
| 355 | $values[] = '(' . implode(', ', $record) . ')'; |
||
| 356 | } |
||
| 357 | |||
| 358 | $sql = 'INSERT' . $options . ' INTO ' . $this->quoteTableName($tableName) . ' (' . implode(', ', $columns) . ') VALUES ' . implode(', ', $values); |
||
| 359 | |||
| 360 | return $this->quoteSql($sql); |
||
| 361 | } |
||
| 362 | |||
| 363 | $values = array_map([$this, 'quoteValue'], $rows); |
||
| 364 | $columns = array_map([$this, 'quoteColumn'], array_keys($rows)); |
||
| 365 | |||
| 366 | $sql = 'INSERT' . $options . ' INTO ' . $this->quoteTableName($tableName) . ' (' . implode(', ', $columns) . ') VALUES (' . implode(', ', $values) . ')'; |
||
| 367 | |||
| 368 | return $this->quoteSql($sql); |
||
| 369 | } |
||
| 370 | |||
| 371 | public function sqlUpdate($tableName, array $columns, $options = '') |
||
| 372 | { |
||
| 373 | $tableName = $this->getRawTableName($tableName); |
||
| 374 | $parts = []; |
||
| 375 | foreach ($columns as $column => $value) { |
||
| 376 | $parts[] = $this->quoteColumn($column) . '=' . $this->quoteValue($value); |
||
| 377 | } |
||
| 378 | if ($options) { |
||
| 379 | $options = " {$options} "; |
||
| 380 | } |
||
| 381 | |||
| 382 | return 'UPDATE ' . $options . $this->quoteTableName($tableName) . ' SET ' . implode(', ', $parts); |
||
| 383 | } |
||
| 384 | |||
| 385 | /** |
||
| 386 | * @param $select |
||
| 387 | * @param $from |
||
| 388 | * @param $where |
||
| 389 | * @param $order |
||
| 390 | * @param $group |
||
| 391 | * @param $limit |
||
| 392 | * @param $offset |
||
| 393 | * @param $join |
||
| 394 | * @param $having |
||
| 395 | * @param $union |
||
| 396 | * @param string $options |
||
| 397 | * @return string |
||
| 398 | * @throws \Exception |
||
| 399 | */ |
||
| 400 | public function generateSelectSQL($select, $from, $where, $order, $group, $limit, $offset, $join, $having, $union, $options = '') |
||
| 401 | { |
||
| 402 | $where = $this->sqlWhere($where); |
||
| 403 | $orderSql = $this->sqlOrderBy($order); |
||
| 404 | $unionSql = $this->sqlUnion($union); |
||
| 405 | |||
| 406 | return strtr('{select}{from}{join}{where}{group}{having}{order}{limit_offset}{union}', [ |
||
| 407 | '{select}' => $this->sqlSelect($select, $options), |
||
| 408 | '{from}' => $this->sqlFrom($from), |
||
| 409 | '{where}' => $where, |
||
| 410 | '{group}' => $this->sqlGroupBy($group), |
||
| 411 | '{order}' => empty($union) ? $orderSql : '', |
||
| 412 | '{having}' => $this->sqlHaving($having), |
||
| 413 | '{join}' => $join, |
||
| 414 | '{limit_offset}' => $this->sqlLimitOffset($limit, $offset), |
||
| 415 | '{union}' => empty($union) ? '' : $unionSql . $orderSql |
||
| 416 | ]); |
||
| 417 | } |
||
| 418 | |||
| 419 | public function sqlCreateTable($tableName, $columns, $options = null, $ifNotExists = false) |
||
| 438 | } |
||
| 439 | |||
| 440 | /** |
||
| 441 | * @param $oldTableName |
||
| 442 | * @param $newTableName |
||
| 443 | * @return string |
||
| 444 | */ |
||
| 445 | abstract public function sqlRenameTable($oldTableName, $newTableName); |
||
| 446 | |||
| 447 | /** |
||
| 448 | * @param $tableName |
||
| 449 | * @param bool $ifExists |
||
| 450 | * @param bool $cascade |
||
| 451 | * @return string |
||
| 452 | */ |
||
| 453 | public function sqlDropTable($tableName, $ifExists = false, $cascade = false) |
||
| 457 | } |
||
| 458 | |||
| 459 | /** |
||
| 460 | * @param $tableName |
||
| 461 | * @param bool $cascade |
||
| 462 | * @return string |
||
| 463 | */ |
||
| 464 | public function sqlTruncateTable($tableName, $cascade = false) |
||
| 467 | } |
||
| 468 | |||
| 469 | /** |
||
| 470 | * @param $tableName |
||
| 471 | * @param $name |
||
| 472 | * @return string |
||
| 473 | */ |
||
| 474 | abstract public function sqlDropIndex($tableName, $name); |
||
| 475 | |||
| 476 | /** |
||
| 477 | * @param $value |
||
| 478 | * @return string |
||
| 479 | */ |
||
| 480 | public function getSqlType($value) |
||
| 481 | { |
||
| 482 | if ($value === 'true' || $value === true) { |
||
| 483 | return 'TRUE'; |
||
| 484 | } |
||
| 485 | else if ($value === null || $value === 'null') { |
||
| 486 | return 'NULL'; |
||
| 487 | } |
||
| 488 | else if ($value === false || $value === 'false') { |
||
| 489 | return 'FALSE'; |
||
| 490 | } |
||
| 491 | else { |
||
| 492 | return $value; |
||
| 493 | } |
||
| 494 | } |
||
| 495 | |||
| 496 | /** |
||
| 497 | * @param $tableName |
||
| 498 | * @param $column |
||
| 499 | * @return string |
||
| 500 | */ |
||
| 501 | public function sqlDropColumn($tableName, $column) |
||
| 504 | } |
||
| 505 | |||
| 506 | /** |
||
| 507 | * @param $tableName |
||
| 508 | * @param $oldName |
||
| 509 | * @param $newName |
||
| 510 | * @return mixed |
||
| 511 | */ |
||
| 512 | abstract public function sqlRenameColumn($tableName, $oldName, $newName); |
||
| 513 | |||
| 514 | /** |
||
| 515 | * @param $tableName |
||
| 516 | * @param $name |
||
| 517 | * @return mixed |
||
| 518 | */ |
||
| 519 | abstract public function sqlDropForeignKey($tableName, $name); |
||
| 520 | |||
| 521 | public function sqlAddForeignKey($tableName, $name, $columns, $refTable, $refColumns, $delete = null, $update = null) |
||
| 522 | { |
||
| 523 | $sql = 'ALTER TABLE ' . $this->quoteTableName($tableName) |
||
| 524 | . ' ADD CONSTRAINT ' . $this->quoteColumn($name) |
||
| 525 | . ' FOREIGN KEY (' . $this->buildColumns($columns) . ')' |
||
| 526 | . ' REFERENCES ' . $this->quoteTableName($refTable) |
||
| 527 | . ' (' . $this->buildColumns($refColumns) . ')'; |
||
| 528 | if ($delete !== null) { |
||
| 529 | $sql .= ' ON DELETE ' . $delete; |
||
| 530 | } |
||
| 531 | if ($update !== null) { |
||
| 532 | $sql .= ' ON UPDATE ' . $update; |
||
| 533 | } |
||
| 534 | return $sql; |
||
| 535 | } |
||
| 536 | |||
| 537 | /** |
||
| 538 | * @return string |
||
| 539 | */ |
||
| 540 | abstract public function getRandomOrder(); |
||
| 541 | |||
| 542 | /** |
||
| 543 | * @param $value |
||
| 544 | * @return string |
||
| 545 | */ |
||
| 546 | abstract public function getBoolean($value = null); |
||
| 547 | |||
| 548 | /** |
||
| 549 | * @param null $value |
||
| 550 | * @return string |
||
| 551 | */ |
||
| 552 | abstract public function getDateTime($value = null); |
||
| 553 | |||
| 554 | /** |
||
| 555 | * @param null $value |
||
| 556 | * @return string |
||
| 557 | */ |
||
| 558 | abstract public function getDate($value = null); |
||
| 559 | |||
| 560 | /** |
||
| 561 | * @param null $value |
||
| 562 | * @return mixed |
||
| 563 | */ |
||
| 564 | public function getTimestamp($value = null) |
||
| 565 | { |
||
| 566 | return $value instanceof \DateTime ? $value->getTimestamp() : strtotime($value); |
||
| 567 | } |
||
| 568 | |||
| 569 | /** |
||
| 570 | * @param $tableName |
||
| 571 | * @param $column |
||
| 572 | * @param $type |
||
| 573 | * @return string |
||
| 574 | */ |
||
| 575 | abstract public function sqlAddColumn($tableName, $column, $type); |
||
| 576 | |||
| 577 | /** |
||
| 578 | * @param $tableName |
||
| 579 | * @param $name |
||
| 580 | * @param array $columns |
||
| 581 | * @param bool $unique |
||
| 582 | * @return string |
||
| 583 | */ |
||
| 584 | public function sqlCreateIndex($tableName, $name, array $columns, $unique = false) |
||
| 585 | { |
||
| 586 | return ($unique ? 'CREATE UNIQUE INDEX ' : 'CREATE INDEX ') |
||
| 587 | . $this->quoteTableName($name) . ' ON ' |
||
| 588 | . $this->quoteTableName($tableName) |
||
| 589 | . ' (' . $this->buildColumns($columns) . ')'; |
||
| 590 | } |
||
| 591 | |||
| 592 | /** |
||
| 593 | * @param $tables |
||
| 594 | * @return string |
||
| 595 | */ |
||
| 596 | public function sqlFrom($tables) |
||
| 622 | } |
||
| 623 | |||
| 624 | /** |
||
| 625 | * @param $joinType string |
||
| 626 | * @param $tableName string |
||
| 627 | * @param $on string|array |
||
| 628 | * @param $alias string |
||
| 629 | * @return string |
||
| 630 | */ |
||
| 631 | public function sqlJoin($joinType, $tableName, $on = [], $alias = null, $index = null) |
||
| 632 | { |
||
| 633 | $toSql = [$joinType]; |
||
| 634 | if (is_string($tableName) && $tableName = $this->getRawTableName($tableName)) { |
||
| 635 | if (strpos($tableName, 'SELECT') !== false) { |
||
| 636 | $toSql[] = '(' . $this->quoteSql($tableName) . ')'; |
||
| 637 | } |
||
| 638 | else { |
||
| 639 | $toSql[] = $this->quoteTableName($tableName); |
||
| 640 | } |
||
| 641 | } |
||
| 642 | else if ($tableName instanceof QueryBuilder) { |
||
| 643 | $toSql[] = '(' . $this->quoteSql($tableName->toSQL()) . ')'; |
||
| 644 | } |
||
| 645 | else { |
||
| 646 | throw new QBException('Incorrect table name'); |
||
| 647 | } |
||
| 648 | |||
| 649 | if ($alias) { |
||
| 650 | $toSql[] = 'AS ' . $this->quoteColumn($alias); |
||
| 651 | } |
||
| 652 | |||
| 653 | if ($on) { |
||
| 654 | $onSQL = []; |
||
| 655 | if (is_string($on)) { |
||
| 656 | $onSQL[] = $this->quoteSql($on); |
||
| 657 | } |
||
| 658 | else { |
||
| 659 | foreach ($on as $leftColumn => $rightColumn) { |
||
| 660 | if ($rightColumn instanceof Expression) { |
||
| 661 | $onSQL[] = $this->quoteColumn($leftColumn) . '=' . $this->quoteSql($rightColumn->toSQL()); |
||
| 662 | } |
||
| 663 | else { |
||
| 664 | $onSQL[] = $this->quoteColumn($leftColumn) . '=' . $this->quoteColumn($rightColumn); |
||
| 665 | } |
||
| 666 | } |
||
| 667 | } |
||
| 668 | |||
| 669 | $toSql[] = 'ON ' . implode(' and ', $onSQL); |
||
| 670 | } |
||
| 671 | |||
| 672 | return implode(' ', $toSql); |
||
| 673 | } |
||
| 674 | |||
| 675 | /** |
||
| 676 | * @param $where string|array |
||
| 677 | * @return string |
||
| 678 | */ |
||
| 679 | public function sqlWhere($where) |
||
| 686 | } |
||
| 687 | |||
| 688 | /** |
||
| 689 | * @param $having |
||
| 690 | * @return string |
||
| 691 | */ |
||
| 692 | public function sqlHaving($having) |
||
| 693 | { |
||
| 694 | if (empty($having)) { |
||
| 695 | return ''; |
||
| 696 | } |
||
| 697 | |||
| 698 | if ($having instanceof IToSql) { |
||
| 699 | $sql = $having |
||
| 700 | ->toSql(); |
||
| 701 | } |
||
| 702 | else { |
||
| 703 | $sql = $this->quoteSql($having); |
||
| 704 | } |
||
| 705 | |||
| 706 | return empty($sql) ? '' : ' HAVING ' . $sql; |
||
| 707 | } |
||
| 708 | |||
| 709 | /** |
||
| 710 | * @param QueryBuilderInterface|string $union |
||
| 711 | * @param bool $all |
||
| 712 | * @return string |
||
| 713 | */ |
||
| 714 | public function sqlUnion($union, $all = false) |
||
| 715 | { |
||
| 716 | if (empty($union)) { |
||
| 717 | return ''; |
||
| 718 | } |
||
| 719 | |||
| 720 | if ($union instanceof QueryBuilderInterface) { |
||
| 721 | $unionSQL = $union->setOrder(null)->toSQL(); |
||
| 722 | } |
||
| 723 | else { |
||
| 724 | $unionSQL = $this->quoteSql($union); |
||
| 725 | } |
||
| 726 | |||
| 727 | $sql = 'UNION '; |
||
| 728 | |||
| 729 | if ($all) { |
||
| 730 | $sql .= 'ALL '; |
||
| 731 | } |
||
| 732 | |||
| 733 | return $sql . '(' . $unionSQL . ')'; |
||
| 734 | } |
||
| 735 | |||
| 736 | /** |
||
| 737 | * @param $tableName |
||
| 738 | * @param $sequenceName |
||
| 739 | * @return string |
||
| 740 | */ |
||
| 741 | abstract public function sqlResetSequence($tableName, $sequenceName); |
||
| 742 | |||
| 743 | /** |
||
| 744 | * @param bool $check |
||
| 745 | * @param string $schema |
||
| 746 | * @param string $table |
||
| 747 | * @return string |
||
| 748 | */ |
||
| 749 | abstract public function sqlCheckIntegrity($check = true, $schema = '', $table = ''); |
||
| 750 | |||
| 751 | /** |
||
| 752 | * @param $columns |
||
| 753 | * @return string |
||
| 754 | */ |
||
| 755 | public function sqlGroupBy($columns) |
||
| 756 | { |
||
| 757 | if (empty($columns)) { |
||
| 758 | return ''; |
||
| 759 | } |
||
| 760 | |||
| 761 | if (is_string($columns)) { |
||
| 762 | $columns = preg_split('/\s*,\s*/', $columns, -1, PREG_SPLIT_NO_EMPTY); |
||
| 763 | |||
| 764 | if ($columns) { |
||
| 765 | $quotedColumns = array_map([$this, 'quoteColumn'], (array)$columns); |
||
| 766 | return implode(', ', $quotedColumns); |
||
| 767 | } |
||
| 768 | |||
| 769 | return ''; |
||
| 770 | } |
||
| 771 | |||
| 772 | $group = []; |
||
| 773 | foreach ($columns as $column) { |
||
| 774 | $group[] = $this->quoteColumn($column); |
||
| 775 | } |
||
| 776 | |||
| 777 | return implode(', ', $group); |
||
| 778 | } |
||
| 779 | |||
| 780 | /** |
||
| 781 | * @param array $columns |
||
| 782 | * @param null $options |
||
| 783 | * @return string |
||
| 784 | */ |
||
| 785 | public function sqlOrderBy(array $columns, $options = null) |
||
| 786 | { |
||
| 787 | if (empty($columns)) { |
||
| 788 | return ''; |
||
| 789 | } |
||
| 790 | |||
| 791 | $order = []; |
||
| 792 | foreach ($columns as $column => $direction) { |
||
| 793 | |||
| 794 | $order[] = $this->quoteColumn($column) . ' ' . $direction; |
||
| 795 | } |
||
| 796 | |||
| 797 | return implode(', ', $order) . (empty($options) ? '' : ' ' . $options); |
||
| 798 | } |
||
| 799 | |||
| 800 | /** |
||
| 801 | * @param array|null|string $columns |
||
| 802 | * @param string $options |
||
| 803 | * |
||
| 804 | * @return string |
||
| 805 | */ |
||
| 806 | public function sqlSelect($columns, $options = '') |
||
| 807 | { |
||
| 808 | $selectSql = 'SELECT '; |
||
| 809 | |||
| 810 | if ($options) { |
||
| 811 | $selectSql .= $options . ' '; |
||
| 812 | } |
||
| 813 | |||
| 814 | if (empty($columns)) { |
||
| 815 | return $selectSql . '*'; |
||
| 816 | } |
||
| 817 | |||
| 818 | if (is_array($columns) === false) { |
||
| 819 | $columns = [$columns]; |
||
| 820 | } |
||
| 821 | |||
| 822 | $select = []; |
||
| 823 | foreach ($columns as $column => $expr) { |
||
| 824 | if ($expr instanceof IToSql) { |
||
| 825 | $value = $this->quoteColumn($expr->toSql()); |
||
| 826 | |||
| 827 | if (!is_numeric($column)) { |
||
| 828 | $value .= ' AS ' . $this->quoteColumn($column); |
||
| 829 | } |
||
| 830 | } |
||
| 831 | else { |
||
| 832 | $subQuery = (string)$this->quoteSql($expr); |
||
| 833 | |||
| 834 | if (is_numeric($column)) { |
||
| 835 | $column = $subQuery; |
||
| 836 | $subQuery = ''; |
||
| 837 | } |
||
| 838 | |||
| 839 | if (!empty($subQuery)) { |
||
| 840 | if (strpos($subQuery, 'SELECT') !== false) { |
||
| 841 | $value = '(' . $subQuery . ') AS ' . $this->quoteColumn($column); |
||
| 842 | } |
||
| 843 | else { |
||
| 844 | $value = $this->quoteColumn($subQuery) . ' AS ' . $this->quoteColumn($column); |
||
| 845 | } |
||
| 846 | } |
||
| 847 | else if (strpos($column, ',') === false && strpos($column, 'AS') !== false) { |
||
| 848 | |||
| 849 | list($rawColumn, $rawAlias) = explode('AS', $column); |
||
| 850 | $value = $this->quoteColumn(trim($rawColumn)); |
||
| 851 | |||
| 852 | if (!empty($rawAlias)) { |
||
| 853 | $value .= ' AS ' . $this->quoteColumn(trim($rawAlias)); |
||
| 854 | } |
||
| 855 | } |
||
| 856 | else if (strpos($column, ',') !== false) { |
||
| 857 | $newSelect = []; |
||
| 858 | |||
| 859 | foreach (explode(',', $column) as $item) { |
||
| 860 | $rawColumn = $item; |
||
| 861 | $rawAlias = ''; |
||
| 862 | |||
| 863 | if (strpos($item, 'AS') !== false) { |
||
| 864 | list($rawColumn, $rawAlias) = explode('AS', $item); |
||
| 865 | } |
||
| 866 | |||
| 867 | $_v = $this->quoteColumn(trim($rawColumn)); |
||
| 868 | |||
| 869 | if (!empty($rawAlias)) { |
||
| 870 | $_v .= ' AS ' . $this->quoteColumn(trim($rawAlias)); |
||
| 871 | } |
||
| 872 | |||
| 873 | $newSelect[] = $_v; |
||
| 874 | } |
||
| 875 | $value = implode(', ', $newSelect); |
||
| 876 | |||
| 877 | } |
||
| 878 | else { |
||
| 879 | $value = $this->quoteColumn($column); |
||
| 880 | } |
||
| 881 | } |
||
| 882 | |||
| 883 | $select[] = $value; |
||
| 884 | } |
||
| 885 | |||
| 886 | return $selectSql . implode(', ', $select); |
||
| 887 | } |
||
| 888 | |||
| 889 | /** |
||
| 890 | * Prepare value for db |
||
| 891 | * @param $value |
||
| 892 | * @return int |
||
| 893 | */ |
||
| 894 | public function prepareValue($value) |
||
| 897 | } |
||
| 898 | } |
||
| 899 |
Loading history...