1: <?php
  2: /**
  3:  * CakePHP(tm) : Rapid Development Framework (https://cakephp.org)
  4:  * Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
  5:  *
  6:  * Licensed under The MIT License
  7:  * For full copyright and license information, please see the LICENSE.txt
  8:  * Redistributions of files must retain the above copyright notice.
  9:  *
 10:  * @copyright     Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
 11:  * @link          https://cakephp.org CakePHP(tm) Project
 12:  * @since         3.0.0
 13:  * @license       https://opensource.org/licenses/mit-license.php MIT License
 14:  */
 15: namespace Cake\Database\Schema;
 16: 
 17: use Cake\Database\Exception;
 18: use Cake\Database\Schema\TableSchema;
 19: 
 20: /**
 21:  * Schema generation/reflection features for MySQL
 22:  */
 23: class MysqlSchema extends BaseSchema
 24: {
 25:     /**
 26:      * The driver instance being used.
 27:      *
 28:      * @var \Cake\Database\Driver\Mysql
 29:      */
 30:     protected $_driver;
 31: 
 32:     /**
 33:      * {@inheritDoc}
 34:      */
 35:     public function listTablesSql($config)
 36:     {
 37:         return ['SHOW TABLES FROM ' . $this->_driver->quoteIdentifier($config['database']), []];
 38:     }
 39: 
 40:     /**
 41:      * {@inheritDoc}
 42:      */
 43:     public function describeColumnSql($tableName, $config)
 44:     {
 45:         return ['SHOW FULL COLUMNS FROM ' . $this->_driver->quoteIdentifier($tableName), []];
 46:     }
 47: 
 48:     /**
 49:      * {@inheritDoc}
 50:      */
 51:     public function describeIndexSql($tableName, $config)
 52:     {
 53:         return ['SHOW INDEXES FROM ' . $this->_driver->quoteIdentifier($tableName), []];
 54:     }
 55: 
 56:     /**
 57:      * {@inheritDoc}
 58:      */
 59:     public function describeOptionsSql($tableName, $config)
 60:     {
 61:         return ['SHOW TABLE STATUS WHERE Name = ?', [$tableName]];
 62:     }
 63: 
 64:     /**
 65:      * {@inheritDoc}
 66:      */
 67:     public function convertOptionsDescription(TableSchema $schema, $row)
 68:     {
 69:         $schema->setOptions([
 70:             'engine' => $row['Engine'],
 71:             'collation' => $row['Collation'],
 72:         ]);
 73:     }
 74: 
 75:     /**
 76:      * Convert a MySQL column type into an abstract type.
 77:      *
 78:      * The returned type will be a type that Cake\Database\Type can handle.
 79:      *
 80:      * @param string $column The column type + length
 81:      * @return array Array of column information.
 82:      * @throws \Cake\Database\Exception When column type cannot be parsed.
 83:      */
 84:     protected function _convertColumn($column)
 85:     {
 86:         preg_match('/([a-z]+)(?:\(([0-9,]+)\))?\s*([a-z]+)?/i', $column, $matches);
 87:         if (empty($matches)) {
 88:             throw new Exception(sprintf('Unable to parse column type from "%s"', $column));
 89:         }
 90: 
 91:         $col = strtolower($matches[1]);
 92:         $length = $precision = null;
 93:         if (isset($matches[2]) && strlen($matches[2])) {
 94:             $length = $matches[2];
 95:             if (strpos($matches[2], ',') !== false) {
 96:                 list($length, $precision) = explode(',', $length);
 97:             }
 98:             $length = (int)$length;
 99:             $precision = (int)$precision;
100:         }
101: 
102:         if (in_array($col, ['date', 'time', 'datetime', 'timestamp'])) {
103:             return ['type' => $col, 'length' => null];
104:         }
105:         if (($col === 'tinyint' && $length === 1) || $col === 'boolean') {
106:             return ['type' => TableSchema::TYPE_BOOLEAN, 'length' => null];
107:         }
108: 
109:         $unsigned = (isset($matches[3]) && strtolower($matches[3]) === 'unsigned');
110:         if (strpos($col, 'bigint') !== false || $col === 'bigint') {
111:             return ['type' => TableSchema::TYPE_BIGINTEGER, 'length' => $length, 'unsigned' => $unsigned];
112:         }
113:         if ($col === 'tinyint') {
114:             return ['type' => TableSchema::TYPE_TINYINTEGER, 'length' => $length, 'unsigned' => $unsigned];
115:         }
116:         if ($col === 'smallint') {
117:             return ['type' => TableSchema::TYPE_SMALLINTEGER, 'length' => $length, 'unsigned' => $unsigned];
118:         }
119:         if (in_array($col, ['int', 'integer', 'mediumint'])) {
120:             return ['type' => TableSchema::TYPE_INTEGER, 'length' => $length, 'unsigned' => $unsigned];
121:         }
122:         if ($col === 'char' && $length === 36) {
123:             return ['type' => TableSchema::TYPE_UUID, 'length' => null];
124:         }
125:         if ($col === 'char') {
126:             return ['type' => TableSchema::TYPE_STRING, 'length' => $length, 'fixed' => true];
127:         }
128:         if (strpos($col, 'char') !== false) {
129:             return ['type' => TableSchema::TYPE_STRING, 'length' => $length];
130:         }
131:         if (strpos($col, 'text') !== false) {
132:             $lengthName = substr($col, 0, -4);
133:             $length = isset(TableSchema::$columnLengths[$lengthName]) ? TableSchema::$columnLengths[$lengthName] : null;
134: 
135:             return ['type' => TableSchema::TYPE_TEXT, 'length' => $length];
136:         }
137:         if ($col === 'binary' && $length === 16) {
138:             return ['type' => TableSchema::TYPE_BINARY_UUID, 'length' => null];
139:         }
140:         if (strpos($col, 'blob') !== false || in_array($col, ['binary', 'varbinary'])) {
141:             $lengthName = substr($col, 0, -4);
142:             $length = isset(TableSchema::$columnLengths[$lengthName]) ? TableSchema::$columnLengths[$lengthName] : $length;
143: 
144:             return ['type' => TableSchema::TYPE_BINARY, 'length' => $length];
145:         }
146:         if (strpos($col, 'float') !== false || strpos($col, 'double') !== false) {
147:             return [
148:                 'type' => TableSchema::TYPE_FLOAT,
149:                 'length' => $length,
150:                 'precision' => $precision,
151:                 'unsigned' => $unsigned
152:             ];
153:         }
154:         if (strpos($col, 'decimal') !== false) {
155:             return [
156:                 'type' => TableSchema::TYPE_DECIMAL,
157:                 'length' => $length,
158:                 'precision' => $precision,
159:                 'unsigned' => $unsigned
160:             ];
161:         }
162: 
163:         if (strpos($col, 'json') !== false) {
164:             return ['type' => TableSchema::TYPE_JSON, 'length' => null];
165:         }
166: 
167:         return ['type' => TableSchema::TYPE_STRING, 'length' => null];
168:     }
169: 
170:     /**
171:      * {@inheritDoc}
172:      */
173:     public function convertColumnDescription(TableSchema $schema, $row)
174:     {
175:         $field = $this->_convertColumn($row['Type']);
176:         $field += [
177:             'null' => $row['Null'] === 'YES',
178:             'default' => $row['Default'],
179:             'collate' => $row['Collation'],
180:             'comment' => $row['Comment'],
181:         ];
182:         if (isset($row['Extra']) && $row['Extra'] === 'auto_increment') {
183:             $field['autoIncrement'] = true;
184:         }
185:         $schema->addColumn($row['Field'], $field);
186:     }
187: 
188:     /**
189:      * {@inheritDoc}
190:      */
191:     public function convertIndexDescription(TableSchema $schema, $row)
192:     {
193:         $type = null;
194:         $columns = $length = [];
195: 
196:         $name = $row['Key_name'];
197:         if ($name === 'PRIMARY') {
198:             $name = $type = TableSchema::CONSTRAINT_PRIMARY;
199:         }
200: 
201:         $columns[] = $row['Column_name'];
202: 
203:         if ($row['Index_type'] === 'FULLTEXT') {
204:             $type = TableSchema::INDEX_FULLTEXT;
205:         } elseif ($row['Non_unique'] == 0 && $type !== 'primary') {
206:             $type = TableSchema::CONSTRAINT_UNIQUE;
207:         } elseif ($type !== 'primary') {
208:             $type = TableSchema::INDEX_INDEX;
209:         }
210: 
211:         if (!empty($row['Sub_part'])) {
212:             $length[$row['Column_name']] = $row['Sub_part'];
213:         }
214:         $isIndex = (
215:             $type === TableSchema::INDEX_INDEX ||
216:             $type === TableSchema::INDEX_FULLTEXT
217:         );
218:         if ($isIndex) {
219:             $existing = $schema->getIndex($name);
220:         } else {
221:             $existing = $schema->getConstraint($name);
222:         }
223: 
224:         // MySQL multi column indexes come back as multiple rows.
225:         if (!empty($existing)) {
226:             $columns = array_merge($existing['columns'], $columns);
227:             $length = array_merge($existing['length'], $length);
228:         }
229:         if ($isIndex) {
230:             $schema->addIndex($name, [
231:                 'type' => $type,
232:                 'columns' => $columns,
233:                 'length' => $length
234:             ]);
235:         } else {
236:             $schema->addConstraint($name, [
237:                 'type' => $type,
238:                 'columns' => $columns,
239:                 'length' => $length
240:             ]);
241:         }
242:     }
243: 
244:     /**
245:      * {@inheritDoc}
246:      */
247:     public function describeForeignKeySql($tableName, $config)
248:     {
249:         $sql = 'SELECT * FROM information_schema.key_column_usage AS kcu
250:             INNER JOIN information_schema.referential_constraints AS rc
251:             ON (
252:                 kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
253:                 AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
254:             )
255:             WHERE kcu.TABLE_SCHEMA = ? AND kcu.TABLE_NAME = ? AND rc.TABLE_NAME = ?';
256: 
257:         return [$sql, [$config['database'], $tableName, $tableName]];
258:     }
259: 
260:     /**
261:      * {@inheritDoc}
262:      */
263:     public function convertForeignKeyDescription(TableSchema $schema, $row)
264:     {
265:         $data = [
266:             'type' => TableSchema::CONSTRAINT_FOREIGN,
267:             'columns' => [$row['COLUMN_NAME']],
268:             'references' => [$row['REFERENCED_TABLE_NAME'], $row['REFERENCED_COLUMN_NAME']],
269:             'update' => $this->_convertOnClause($row['UPDATE_RULE']),
270:             'delete' => $this->_convertOnClause($row['DELETE_RULE']),
271:         ];
272:         $name = $row['CONSTRAINT_NAME'];
273:         $schema->addConstraint($name, $data);
274:     }
275: 
276:     /**
277:      * {@inheritDoc}
278:      */
279:     public function truncateTableSql(TableSchema $schema)
280:     {
281:         return [sprintf('TRUNCATE TABLE `%s`', $schema->name())];
282:     }
283: 
284:     /**
285:      * {@inheritDoc}
286:      */
287:     public function createTableSql(TableSchema $schema, $columns, $constraints, $indexes)
288:     {
289:         $content = implode(",\n", array_merge($columns, $constraints, $indexes));
290:         $temporary = $schema->isTemporary() ? ' TEMPORARY ' : ' ';
291:         $content = sprintf("CREATE%sTABLE `%s` (\n%s\n)", $temporary, $schema->name(), $content);
292:         $options = $schema->getOptions();
293:         if (isset($options['engine'])) {
294:             $content .= sprintf(' ENGINE=%s', $options['engine']);
295:         }
296:         if (isset($options['charset'])) {
297:             $content .= sprintf(' DEFAULT CHARSET=%s', $options['charset']);
298:         }
299:         if (isset($options['collate'])) {
300:             $content .= sprintf(' COLLATE=%s', $options['collate']);
301:         }
302: 
303:         return [$content];
304:     }
305: 
306:     /**
307:      * {@inheritDoc}
308:      */
309:     public function columnSql(TableSchema $schema, $name)
310:     {
311:         $data = $schema->getColumn($name);
312:         $out = $this->_driver->quoteIdentifier($name);
313:         $nativeJson = $this->_driver->supportsNativeJson();
314: 
315:         $typeMap = [
316:             TableSchema::TYPE_TINYINTEGER => ' TINYINT',
317:             TableSchema::TYPE_SMALLINTEGER => ' SMALLINT',
318:             TableSchema::TYPE_INTEGER => ' INTEGER',
319:             TableSchema::TYPE_BIGINTEGER => ' BIGINT',
320:             TableSchema::TYPE_BINARY_UUID => ' BINARY(16)',
321:             TableSchema::TYPE_BOOLEAN => ' BOOLEAN',
322:             TableSchema::TYPE_FLOAT => ' FLOAT',
323:             TableSchema::TYPE_DECIMAL => ' DECIMAL',
324:             TableSchema::TYPE_DATE => ' DATE',
325:             TableSchema::TYPE_TIME => ' TIME',
326:             TableSchema::TYPE_DATETIME => ' DATETIME',
327:             TableSchema::TYPE_TIMESTAMP => ' TIMESTAMP',
328:             TableSchema::TYPE_UUID => ' CHAR(36)',
329:             TableSchema::TYPE_JSON => $nativeJson ? ' JSON' : ' LONGTEXT'
330:         ];
331:         $specialMap = [
332:             'string' => true,
333:             'text' => true,
334:             'binary' => true,
335:         ];
336:         if (isset($typeMap[$data['type']])) {
337:             $out .= $typeMap[$data['type']];
338:         }
339:         if (isset($specialMap[$data['type']])) {
340:             switch ($data['type']) {
341:                 case TableSchema::TYPE_STRING:
342:                     $out .= !empty($data['fixed']) ? ' CHAR' : ' VARCHAR';
343:                     if (!isset($data['length'])) {
344:                         $data['length'] = 255;
345:                     }
346:                     break;
347:                 case TableSchema::TYPE_TEXT:
348:                     $isKnownLength = in_array($data['length'], TableSchema::$columnLengths);
349:                     if (empty($data['length']) || !$isKnownLength) {
350:                         $out .= ' TEXT';
351:                         break;
352:                     }
353: 
354:                     if ($isKnownLength) {
355:                         $length = array_search($data['length'], TableSchema::$columnLengths);
356:                         $out .= ' ' . strtoupper($length) . 'TEXT';
357:                     }
358: 
359:                     break;
360:                 case TableSchema::TYPE_BINARY:
361:                     $isKnownLength = in_array($data['length'], TableSchema::$columnLengths);
362:                     if ($isKnownLength) {
363:                         $length = array_search($data['length'], TableSchema::$columnLengths);
364:                         $out .= ' ' . strtoupper($length) . 'BLOB';
365:                         break;
366:                     }
367: 
368:                     if (empty($data['length'])) {
369:                         $out .= ' BLOB';
370:                         break;
371:                     }
372: 
373:                     if ($data['length'] > 2) {
374:                         $out .= ' VARBINARY(' . $data['length'] . ')';
375:                     } else {
376:                         $out .= ' BINARY(' . $data['length'] . ')';
377:                     }
378:                     break;
379:             }
380:         }
381:         $hasLength = [
382:             TableSchema::TYPE_INTEGER,
383:             TableSchema::TYPE_SMALLINTEGER,
384:             TableSchema::TYPE_TINYINTEGER,
385:             TableSchema::TYPE_STRING
386:         ];
387:         if (in_array($data['type'], $hasLength, true) && isset($data['length'])) {
388:             $out .= '(' . (int)$data['length'] . ')';
389:         }
390: 
391:         $hasPrecision = [TableSchema::TYPE_FLOAT, TableSchema::TYPE_DECIMAL];
392:         if (in_array($data['type'], $hasPrecision, true) && isset($data['length'])) {
393:             if (isset($data['precision'])) {
394:                 $out .= '(' . (int)$data['length'] . ',' . (int)$data['precision'] . ')';
395:             } else {
396:                 $out .= '(' . (int)$data['length'] . ')';
397:             }
398:         }
399: 
400:         $hasUnsigned = [
401:             TableSchema::TYPE_TINYINTEGER,
402:             TableSchema::TYPE_SMALLINTEGER,
403:             TableSchema::TYPE_INTEGER,
404:             TableSchema::TYPE_BIGINTEGER,
405:             TableSchema::TYPE_FLOAT,
406:             TableSchema::TYPE_DECIMAL
407:         ];
408:         if (in_array($data['type'], $hasUnsigned, true) &&
409:             isset($data['unsigned']) && $data['unsigned'] === true
410:         ) {
411:             $out .= ' UNSIGNED';
412:         }
413: 
414:         $hasCollate = [
415:             TableSchema::TYPE_TEXT,
416:             TableSchema::TYPE_STRING,
417:         ];
418:         if (in_array($data['type'], $hasCollate, true) && isset($data['collate']) && $data['collate'] !== '') {
419:             $out .= ' COLLATE ' . $data['collate'];
420:         }
421: 
422:         if (isset($data['null']) && $data['null'] === false) {
423:             $out .= ' NOT NULL';
424:         }
425:         $addAutoIncrement = (
426:             [$name] == (array)$schema->primaryKey() &&
427:             !$schema->hasAutoincrement() &&
428:             !isset($data['autoIncrement'])
429:         );
430:         if (in_array($data['type'], [TableSchema::TYPE_INTEGER, TableSchema::TYPE_BIGINTEGER]) &&
431:             ($data['autoIncrement'] === true || $addAutoIncrement)
432:         ) {
433:             $out .= ' AUTO_INCREMENT';
434:         }
435:         if (isset($data['null']) && $data['null'] === true && $data['type'] === TableSchema::TYPE_TIMESTAMP) {
436:             $out .= ' NULL';
437:             unset($data['default']);
438:         }
439:         if (isset($data['default']) &&
440:             in_array($data['type'], [TableSchema::TYPE_TIMESTAMP, TableSchema::TYPE_DATETIME]) &&
441:             in_array(strtolower($data['default']), ['current_timestamp', 'current_timestamp()'])
442:         ) {
443:             $out .= ' DEFAULT CURRENT_TIMESTAMP';
444:             unset($data['default']);
445:         }
446:         if (isset($data['default'])) {
447:             $out .= ' DEFAULT ' . $this->_driver->schemaValue($data['default']);
448:             unset($data['default']);
449:         }
450:         if (isset($data['comment']) && $data['comment'] !== '') {
451:             $out .= ' COMMENT ' . $this->_driver->schemaValue($data['comment']);
452:         }
453: 
454:         return $out;
455:     }
456: 
457:     /**
458:      * {@inheritDoc}
459:      */
460:     public function constraintSql(TableSchema $schema, $name)
461:     {
462:         $data = $schema->getConstraint($name);
463:         if ($data['type'] === TableSchema::CONSTRAINT_PRIMARY) {
464:             $columns = array_map(
465:                 [$this->_driver, 'quoteIdentifier'],
466:                 $data['columns']
467:             );
468: 
469:             return sprintf('PRIMARY KEY (%s)', implode(', ', $columns));
470:         }
471: 
472:         $out = '';
473:         if ($data['type'] === TableSchema::CONSTRAINT_UNIQUE) {
474:             $out = 'UNIQUE KEY ';
475:         }
476:         if ($data['type'] === TableSchema::CONSTRAINT_FOREIGN) {
477:             $out = 'CONSTRAINT ';
478:         }
479:         $out .= $this->_driver->quoteIdentifier($name);
480: 
481:         return $this->_keySql($out, $data);
482:     }
483: 
484:     /**
485:      * {@inheritDoc}
486:      */
487:     public function addConstraintSql(TableSchema $schema)
488:     {
489:         $sqlPattern = 'ALTER TABLE %s ADD %s;';
490:         $sql = [];
491: 
492:         foreach ($schema->constraints() as $name) {
493:             $constraint = $schema->getConstraint($name);
494:             if ($constraint['type'] === TableSchema::CONSTRAINT_FOREIGN) {
495:                 $tableName = $this->_driver->quoteIdentifier($schema->name());
496:                 $sql[] = sprintf($sqlPattern, $tableName, $this->constraintSql($schema, $name));
497:             }
498:         }
499: 
500:         return $sql;
501:     }
502: 
503:     /**
504:      * {@inheritDoc}
505:      */
506:     public function dropConstraintSql(TableSchema $schema)
507:     {
508:         $sqlPattern = 'ALTER TABLE %s DROP FOREIGN KEY %s;';
509:         $sql = [];
510: 
511:         foreach ($schema->constraints() as $name) {
512:             $constraint = $schema->getConstraint($name);
513:             if ($constraint['type'] === TableSchema::CONSTRAINT_FOREIGN) {
514:                 $tableName = $this->_driver->quoteIdentifier($schema->name());
515:                 $constraintName = $this->_driver->quoteIdentifier($name);
516:                 $sql[] = sprintf($sqlPattern, $tableName, $constraintName);
517:             }
518:         }
519: 
520:         return $sql;
521:     }
522: 
523:     /**
524:      * {@inheritDoc}
525:      */
526:     public function indexSql(TableSchema $schema, $name)
527:     {
528:         $data = $schema->getIndex($name);
529:         $out = '';
530:         if ($data['type'] === TableSchema::INDEX_INDEX) {
531:             $out = 'KEY ';
532:         }
533:         if ($data['type'] === TableSchema::INDEX_FULLTEXT) {
534:             $out = 'FULLTEXT KEY ';
535:         }
536:         $out .= $this->_driver->quoteIdentifier($name);
537: 
538:         return $this->_keySql($out, $data);
539:     }
540: 
541:     /**
542:      * Helper method for generating key SQL snippets.
543:      *
544:      * @param string $prefix The key prefix
545:      * @param array $data Key data.
546:      * @return string
547:      */
548:     protected function _keySql($prefix, $data)
549:     {
550:         $columns = array_map(
551:             [$this->_driver, 'quoteIdentifier'],
552:             $data['columns']
553:         );
554:         foreach ($data['columns'] as $i => $column) {
555:             if (isset($data['length'][$column])) {
556:                 $columns[$i] .= sprintf('(%d)', $data['length'][$column]);
557:             }
558:         }
559:         if ($data['type'] === TableSchema::CONSTRAINT_FOREIGN) {
560:             return $prefix . sprintf(
561:                 ' FOREIGN KEY (%s) REFERENCES %s (%s) ON UPDATE %s ON DELETE %s',
562:                 implode(', ', $columns),
563:                 $this->_driver->quoteIdentifier($data['references'][0]),
564:                 $this->_convertConstraintColumns($data['references'][1]),
565:                 $this->_foreignOnClause($data['update']),
566:                 $this->_foreignOnClause($data['delete'])
567:             );
568:         }
569: 
570:         return $prefix . ' (' . implode(', ', $columns) . ')';
571:     }
572: }
573: