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: /**
 18:  * Schema management/reflection features for SQLServer.
 19:  */
 20: class SqlserverSchema extends BaseSchema
 21: {
 22:     const DEFAULT_SCHEMA_NAME = 'dbo';
 23: 
 24:     /**
 25:      * {@inheritDoc}
 26:      */
 27:     public function listTablesSql($config)
 28:     {
 29:         $sql = "SELECT TABLE_NAME
 30:             FROM INFORMATION_SCHEMA.TABLES
 31:             WHERE TABLE_SCHEMA = ?
 32:             AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW')
 33:             ORDER BY TABLE_NAME";
 34:         $schema = empty($config['schema']) ? static::DEFAULT_SCHEMA_NAME : $config['schema'];
 35: 
 36:         return [$sql, [$schema]];
 37:     }
 38: 
 39:     /**
 40:      * {@inheritDoc}
 41:      */
 42:     public function describeColumnSql($tableName, $config)
 43:     {
 44:         $sql = 'SELECT DISTINCT
 45:             AC.column_id AS [column_id],
 46:             AC.name AS [name],
 47:             TY.name AS [type],
 48:             AC.max_length AS [char_length],
 49:             AC.precision AS [precision],
 50:             AC.scale AS [scale],
 51:             AC.is_identity AS [autoincrement],
 52:             AC.is_nullable AS [null],
 53:             OBJECT_DEFINITION(AC.default_object_id) AS [default],
 54:             AC.collation_name AS [collation_name]
 55:             FROM sys.[objects] T
 56:             INNER JOIN sys.[schemas] S ON S.[schema_id] = T.[schema_id]
 57:             INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
 58:             INNER JOIN sys.[types] TY ON TY.[user_type_id] = AC.[user_type_id]
 59:             WHERE T.[name] = ? AND S.[name] = ?
 60:             ORDER BY column_id';
 61: 
 62:         $schema = empty($config['schema']) ? static::DEFAULT_SCHEMA_NAME : $config['schema'];
 63: 
 64:         return [$sql, [$tableName, $schema]];
 65:     }
 66: 
 67:     /**
 68:      * Convert a column definition to the abstract types.
 69:      *
 70:      * The returned type will be a type that
 71:      * Cake\Database\Type can handle.
 72:      *
 73:      * @param string $col The column type
 74:      * @param int|null $length the column length
 75:      * @param int|null $precision The column precision
 76:      * @param int|null $scale The column scale
 77:      * @return array Array of column information.
 78:      * @link https://technet.microsoft.com/en-us/library/ms187752.aspx
 79:      */
 80:     protected function _convertColumn($col, $length = null, $precision = null, $scale = null)
 81:     {
 82:         $col = strtolower($col);
 83:         $length = $length !== null ? (int)$length : $length;
 84:         $precision = $precision !== null ? (int)$precision : $precision;
 85:         $scale = $scale !== null ? (int)$scale : $scale;
 86: 
 87:         if (in_array($col, ['date', 'time'])) {
 88:             return ['type' => $col, 'length' => null];
 89:         }
 90:         if (strpos($col, 'datetime') !== false) {
 91:             return ['type' => TableSchema::TYPE_TIMESTAMP, 'length' => null];
 92:         }
 93: 
 94:         if ($col === 'tinyint') {
 95:             return ['type' => TableSchema::TYPE_TINYINTEGER, 'length' => $precision ?: 3];
 96:         }
 97:         if ($col === 'smallint') {
 98:             return ['type' => TableSchema::TYPE_SMALLINTEGER, 'length' => $precision ?: 5];
 99:         }
100:         if ($col === 'int' || $col === 'integer') {
101:             return ['type' => TableSchema::TYPE_INTEGER, 'length' => $precision ?: 10];
102:         }
103:         if ($col === 'bigint') {
104:             return ['type' => TableSchema::TYPE_BIGINTEGER, 'length' => $precision ?: 20];
105:         }
106:         if ($col === 'bit') {
107:             return ['type' => TableSchema::TYPE_BOOLEAN, 'length' => null];
108:         }
109:         if (strpos($col, 'numeric') !== false ||
110:             strpos($col, 'money') !== false ||
111:             strpos($col, 'decimal') !== false
112:         ) {
113:             return ['type' => TableSchema::TYPE_DECIMAL, 'length' => $precision, 'precision' => $scale];
114:         }
115: 
116:         if ($col === 'real' || $col === 'float') {
117:             return ['type' => TableSchema::TYPE_FLOAT, 'length' => null];
118:         }
119:         // SqlServer schema reflection returns double length for unicode
120:         // columns because internally it uses UTF16/UCS2
121:         if ($col === 'nvarchar' || $col === 'nchar' || $col === 'ntext') {
122:             $length /= 2;
123:         }
124:         if (strpos($col, 'varchar') !== false && $length < 0) {
125:             return ['type' => TableSchema::TYPE_TEXT, 'length' => null];
126:         }
127: 
128:         if (strpos($col, 'varchar') !== false) {
129:             return ['type' => TableSchema::TYPE_STRING, 'length' => $length ?: 255];
130:         }
131: 
132:         if (strpos($col, 'char') !== false) {
133:             return ['type' => TableSchema::TYPE_STRING, 'fixed' => true, 'length' => $length];
134:         }
135: 
136:         if (strpos($col, 'text') !== false) {
137:             return ['type' => TableSchema::TYPE_TEXT, 'length' => null];
138:         }
139: 
140:         if ($col === 'image' || strpos($col, 'binary') !== false) {
141:             // -1 is the value for MAX which we treat as a 'long' binary
142:             if ($length == -1) {
143:                 $length = TableSchema::LENGTH_LONG;
144:             }
145: 
146:             return ['type' => TableSchema::TYPE_BINARY, 'length' => $length];
147:         }
148: 
149:         if ($col === 'uniqueidentifier') {
150:             return ['type' => TableSchema::TYPE_UUID];
151:         }
152: 
153:         return ['type' => TableSchema::TYPE_STRING, 'length' => null];
154:     }
155: 
156:     /**
157:      * {@inheritDoc}
158:      */
159:     public function convertColumnDescription(TableSchema $schema, $row)
160:     {
161:         $field = $this->_convertColumn(
162:             $row['type'],
163:             $row['char_length'],
164:             $row['precision'],
165:             $row['scale']
166:         );
167:         if (!empty($row['default'])) {
168:             $row['default'] = trim($row['default'], '()');
169:         }
170:         if (!empty($row['autoincrement'])) {
171:             $field['autoIncrement'] = true;
172:         }
173:         if ($field['type'] === TableSchema::TYPE_BOOLEAN) {
174:             $row['default'] = (int)$row['default'];
175:         }
176: 
177:         $field += [
178:             'null' => $row['null'] === '1',
179:             'default' => $this->_defaultValue($row['default']),
180:             'collate' => $row['collation_name'],
181:         ];
182:         $schema->addColumn($row['name'], $field);
183:     }
184: 
185:     /**
186:      * Manipulate the default value.
187:      *
188:      * Sqlite includes quotes and bared NULLs in default values.
189:      * We need to remove those.
190:      *
191:      * @param string|null $default The default value.
192:      * @return string|null
193:      */
194:     protected function _defaultValue($default)
195:     {
196:         if ($default === 'NULL') {
197:             return null;
198:         }
199: 
200:         // Remove quotes
201:         if (preg_match("/^N?'(.*)'/", $default, $matches)) {
202:             return str_replace("''", "'", $matches[1]);
203:         }
204: 
205:         return $default;
206:     }
207: 
208:     /**
209:      * {@inheritDoc}
210:      */
211:     public function describeIndexSql($tableName, $config)
212:     {
213:         $sql = "SELECT
214:                 I.[name] AS [index_name],
215:                 IC.[index_column_id] AS [index_order],
216:                 AC.[name] AS [column_name],
217:                 I.[is_unique], I.[is_primary_key],
218:                 I.[is_unique_constraint]
219:             FROM sys.[tables] AS T
220:             INNER JOIN sys.[schemas] S ON S.[schema_id] = T.[schema_id]
221:             INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
222:             INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] AND I.[index_id] = IC.[index_id]
223:             INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
224:             WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP' AND T.[name] = ? AND S.[name] = ?
225:             ORDER BY I.[index_id], IC.[index_column_id]";
226: 
227:         $schema = empty($config['schema']) ? static::DEFAULT_SCHEMA_NAME : $config['schema'];
228: 
229:         return [$sql, [$tableName, $schema]];
230:     }
231: 
232:     /**
233:      * {@inheritDoc}
234:      */
235:     public function convertIndexDescription(TableSchema $schema, $row)
236:     {
237:         $type = TableSchema::INDEX_INDEX;
238:         $name = $row['index_name'];
239:         if ($row['is_primary_key']) {
240:             $name = $type = TableSchema::CONSTRAINT_PRIMARY;
241:         }
242:         if ($row['is_unique_constraint'] && $type === TableSchema::INDEX_INDEX) {
243:             $type = TableSchema::CONSTRAINT_UNIQUE;
244:         }
245: 
246:         if ($type === TableSchema::INDEX_INDEX) {
247:             $existing = $schema->getIndex($name);
248:         } else {
249:             $existing = $schema->getConstraint($name);
250:         }
251: 
252:         $columns = [$row['column_name']];
253:         if (!empty($existing)) {
254:             $columns = array_merge($existing['columns'], $columns);
255:         }
256: 
257:         if ($type === TableSchema::CONSTRAINT_PRIMARY || $type === TableSchema::CONSTRAINT_UNIQUE) {
258:             $schema->addConstraint($name, [
259:                 'type' => $type,
260:                 'columns' => $columns
261:             ]);
262: 
263:             return;
264:         }
265:         $schema->addIndex($name, [
266:             'type' => $type,
267:             'columns' => $columns
268:         ]);
269:     }
270: 
271:     /**
272:      * {@inheritDoc}
273:      */
274:     public function describeForeignKeySql($tableName, $config)
275:     {
276:         $sql = 'SELECT FK.[name] AS [foreign_key_name], FK.[delete_referential_action_desc] AS [delete_type],
277:                 FK.[update_referential_action_desc] AS [update_type], C.name AS [column], RT.name AS [reference_table],
278:                 RC.name AS [reference_column]
279:             FROM sys.foreign_keys FK
280:             INNER JOIN sys.foreign_key_columns FKC ON FKC.constraint_object_id = FK.object_id
281:             INNER JOIN sys.tables T ON T.object_id = FKC.parent_object_id
282:             INNER JOIN sys.tables RT ON RT.object_id = FKC.referenced_object_id
283:             INNER JOIN sys.schemas S ON S.schema_id = T.schema_id AND S.schema_id = RT.schema_id
284:             INNER JOIN sys.columns C ON C.column_id = FKC.parent_column_id AND C.object_id = FKC.parent_object_id
285:             INNER JOIN sys.columns RC ON RC.column_id = FKC.referenced_column_id AND RC.object_id = FKC.referenced_object_id
286:             WHERE FK.is_ms_shipped = 0 AND T.name = ? AND S.name = ?';
287: 
288:         $schema = empty($config['schema']) ? static::DEFAULT_SCHEMA_NAME : $config['schema'];
289: 
290:         return [$sql, [$tableName, $schema]];
291:     }
292: 
293:     /**
294:      * {@inheritDoc}
295:      */
296:     public function convertForeignKeyDescription(TableSchema $schema, $row)
297:     {
298:         $data = [
299:             'type' => TableSchema::CONSTRAINT_FOREIGN,
300:             'columns' => [$row['column']],
301:             'references' => [$row['reference_table'], $row['reference_column']],
302:             'update' => $this->_convertOnClause($row['update_type']),
303:             'delete' => $this->_convertOnClause($row['delete_type']),
304:         ];
305:         $name = $row['foreign_key_name'];
306:         $schema->addConstraint($name, $data);
307:     }
308: 
309:     /**
310:      * {@inheritDoc}
311:      */
312:     protected function _foreignOnClause($on)
313:     {
314:         $parent = parent::_foreignOnClause($on);
315: 
316:         return $parent === 'RESTRICT' ? parent::_foreignOnClause(TableSchema::ACTION_SET_NULL) : $parent;
317:     }
318: 
319:     /**
320:      * {@inheritDoc}
321:      */
322:     protected function _convertOnClause($clause)
323:     {
324:         switch ($clause) {
325:             case 'NO_ACTION':
326:                 return TableSchema::ACTION_NO_ACTION;
327:             case 'CASCADE':
328:                 return TableSchema::ACTION_CASCADE;
329:             case 'SET_NULL':
330:                 return TableSchema::ACTION_SET_NULL;
331:             case 'SET_DEFAULT':
332:                 return TableSchema::ACTION_SET_DEFAULT;
333:         }
334: 
335:         return TableSchema::ACTION_SET_NULL;
336:     }
337: 
338:     /**
339:      * {@inheritDoc}
340:      */
341:     public function columnSql(TableSchema $schema, $name)
342:     {
343:         $data = $schema->getColumn($name);
344:         $out = $this->_driver->quoteIdentifier($name);
345:         $typeMap = [
346:             TableSchema::TYPE_TINYINTEGER => ' TINYINT',
347:             TableSchema::TYPE_SMALLINTEGER => ' SMALLINT',
348:             TableSchema::TYPE_INTEGER => ' INTEGER',
349:             TableSchema::TYPE_BIGINTEGER => ' BIGINT',
350:             TableSchema::TYPE_BINARY_UUID => ' UNIQUEIDENTIFIER',
351:             TableSchema::TYPE_BOOLEAN => ' BIT',
352:             TableSchema::TYPE_FLOAT => ' FLOAT',
353:             TableSchema::TYPE_DECIMAL => ' DECIMAL',
354:             TableSchema::TYPE_DATE => ' DATE',
355:             TableSchema::TYPE_TIME => ' TIME',
356:             TableSchema::TYPE_DATETIME => ' DATETIME',
357:             TableSchema::TYPE_TIMESTAMP => ' DATETIME',
358:             TableSchema::TYPE_UUID => ' UNIQUEIDENTIFIER',
359:             TableSchema::TYPE_JSON => ' NVARCHAR(MAX)',
360:         ];
361: 
362:         if (isset($typeMap[$data['type']])) {
363:             $out .= $typeMap[$data['type']];
364:         }
365: 
366:         if ($data['type'] === TableSchema::TYPE_INTEGER || $data['type'] === TableSchema::TYPE_BIGINTEGER) {
367:             if ([$name] === $schema->primaryKey() || $data['autoIncrement'] === true) {
368:                 unset($data['null'], $data['default']);
369:                 $out .= ' IDENTITY(1, 1)';
370:             }
371:         }
372: 
373:         if ($data['type'] === TableSchema::TYPE_TEXT && $data['length'] !== TableSchema::LENGTH_TINY) {
374:             $out .= ' NVARCHAR(MAX)';
375:         }
376: 
377:         if ($data['type'] === TableSchema::TYPE_BINARY) {
378:             if (!isset($data['length'])
379:                 || in_array($data['length'], [TableSchema::LENGTH_MEDIUM, TableSchema::LENGTH_LONG], true)) {
380:                 $data['length'] = 'MAX';
381:             }
382: 
383:             if ($data['length'] === 1) {
384:                 $out .= ' BINARY(1)';
385:             } else {
386:                 $out .= ' VARBINARY';
387: 
388:                 $out .= sprintf('(%s)', $data['length']);
389:             }
390:         }
391: 
392:         if ($data['type'] === TableSchema::TYPE_STRING ||
393:             ($data['type'] === TableSchema::TYPE_TEXT && $data['length'] === TableSchema::LENGTH_TINY)
394:         ) {
395:             $type = ' NVARCHAR';
396: 
397:             if (!empty($data['fixed'])) {
398:                 $type = ' NCHAR';
399:             }
400: 
401:             if (!isset($data['length'])) {
402:                 $data['length'] = 255;
403:             }
404: 
405:             $out .= sprintf('%s(%d)', $type, $data['length']);
406:         }
407: 
408:         $hasCollate = [TableSchema::TYPE_TEXT, TableSchema::TYPE_STRING];
409:         if (in_array($data['type'], $hasCollate, true) && isset($data['collate']) && $data['collate'] !== '') {
410:             $out .= ' COLLATE ' . $data['collate'];
411:         }
412: 
413:         if ($data['type'] === TableSchema::TYPE_FLOAT && isset($data['precision'])) {
414:             $out .= '(' . (int)$data['precision'] . ')';
415:         }
416: 
417:         if ($data['type'] === TableSchema::TYPE_DECIMAL &&
418:             (isset($data['length']) || isset($data['precision']))
419:         ) {
420:             $out .= '(' . (int)$data['length'] . ',' . (int)$data['precision'] . ')';
421:         }
422: 
423:         if (isset($data['null']) && $data['null'] === false) {
424:             $out .= ' NOT NULL';
425:         }
426: 
427:         if (isset($data['default']) &&
428:             in_array($data['type'], [TableSchema::TYPE_TIMESTAMP, TableSchema::TYPE_DATETIME]) &&
429:             strtolower($data['default']) === 'current_timestamp'
430:         ) {
431:             $out .= ' DEFAULT CURRENT_TIMESTAMP';
432:         } elseif (isset($data['default'])) {
433:             $default = is_bool($data['default']) ? (int)$data['default'] : $this->_driver->schemaValue($data['default']);
434:             $out .= ' DEFAULT ' . $default;
435:         } elseif (isset($data['null']) && $data['null'] !== false) {
436:             $out .= ' DEFAULT NULL';
437:         }
438: 
439:         return $out;
440:     }
441: 
442:     /**
443:      * {@inheritDoc}
444:      */
445:     public function addConstraintSql(TableSchema $schema)
446:     {
447:         $sqlPattern = 'ALTER TABLE %s ADD %s;';
448:         $sql = [];
449: 
450:         foreach ($schema->constraints() as $name) {
451:             $constraint = $schema->getConstraint($name);
452:             if ($constraint['type'] === TableSchema::CONSTRAINT_FOREIGN) {
453:                 $tableName = $this->_driver->quoteIdentifier($schema->name());
454:                 $sql[] = sprintf($sqlPattern, $tableName, $this->constraintSql($schema, $name));
455:             }
456:         }
457: 
458:         return $sql;
459:     }
460: 
461:     /**
462:      * {@inheritDoc}
463:      */
464:     public function dropConstraintSql(TableSchema $schema)
465:     {
466:         $sqlPattern = 'ALTER TABLE %s DROP CONSTRAINT %s;';
467:         $sql = [];
468: 
469:         foreach ($schema->constraints() as $name) {
470:             $constraint = $schema->getConstraint($name);
471:             if ($constraint['type'] === TableSchema::CONSTRAINT_FOREIGN) {
472:                 $tableName = $this->_driver->quoteIdentifier($schema->name());
473:                 $constraintName = $this->_driver->quoteIdentifier($name);
474:                 $sql[] = sprintf($sqlPattern, $tableName, $constraintName);
475:             }
476:         }
477: 
478:         return $sql;
479:     }
480: 
481:     /**
482:      * {@inheritDoc}
483:      */
484:     public function indexSql(TableSchema $schema, $name)
485:     {
486:         $data = $schema->getIndex($name);
487:         $columns = array_map(
488:             [$this->_driver, 'quoteIdentifier'],
489:             $data['columns']
490:         );
491: 
492:         return sprintf(
493:             'CREATE INDEX %s ON %s (%s)',
494:             $this->_driver->quoteIdentifier($name),
495:             $this->_driver->quoteIdentifier($schema->name()),
496:             implode(', ', $columns)
497:         );
498:     }
499: 
500:     /**
501:      * {@inheritDoc}
502:      */
503:     public function constraintSql(TableSchema $schema, $name)
504:     {
505:         $data = $schema->getConstraint($name);
506:         $out = 'CONSTRAINT ' . $this->_driver->quoteIdentifier($name);
507:         if ($data['type'] === TableSchema::CONSTRAINT_PRIMARY) {
508:             $out = 'PRIMARY KEY';
509:         }
510:         if ($data['type'] === TableSchema::CONSTRAINT_UNIQUE) {
511:             $out .= ' UNIQUE';
512:         }
513: 
514:         return $this->_keySql($out, $data);
515:     }
516: 
517:     /**
518:      * Helper method for generating key SQL snippets.
519:      *
520:      * @param string $prefix The key prefix
521:      * @param array $data Key data.
522:      * @return string
523:      */
524:     protected function _keySql($prefix, $data)
525:     {
526:         $columns = array_map(
527:             [$this->_driver, 'quoteIdentifier'],
528:             $data['columns']
529:         );
530:         if ($data['type'] === TableSchema::CONSTRAINT_FOREIGN) {
531:             return $prefix . sprintf(
532:                 ' FOREIGN KEY (%s) REFERENCES %s (%s) ON UPDATE %s ON DELETE %s',
533:                 implode(', ', $columns),
534:                 $this->_driver->quoteIdentifier($data['references'][0]),
535:                 $this->_convertConstraintColumns($data['references'][1]),
536:                 $this->_foreignOnClause($data['update']),
537:                 $this->_foreignOnClause($data['delete'])
538:             );
539:         }
540: 
541:         return $prefix . ' (' . implode(', ', $columns) . ')';
542:     }
543: 
544:     /**
545:      * {@inheritDoc}
546:      */
547:     public function createTableSql(TableSchema $schema, $columns, $constraints, $indexes)
548:     {
549:         $content = array_merge($columns, $constraints);
550:         $content = implode(",\n", array_filter($content));
551:         $tableName = $this->_driver->quoteIdentifier($schema->name());
552:         $out = [];
553:         $out[] = sprintf("CREATE TABLE %s (\n%s\n)", $tableName, $content);
554:         foreach ($indexes as $index) {
555:             $out[] = $index;
556:         }
557: 
558:         return $out;
559:     }
560: 
561:     /**
562:      * {@inheritDoc}
563:      */
564:     public function truncateTableSql(TableSchema $schema)
565:     {
566:         $name = $this->_driver->quoteIdentifier($schema->name());
567:         $queries = [
568:             sprintf('DELETE FROM %s', $name)
569:         ];
570: 
571:         // Restart identity sequences
572:         $pk = $schema->primaryKey();
573:         if (count($pk) === 1) {
574:             $column = $schema->getColumn($pk[0]);
575:             if (in_array($column['type'], ['integer', 'biginteger'])) {
576:                 $queries[] = sprintf(
577:                     "DBCC CHECKIDENT('%s', RESEED, 0)",
578:                     $schema->name()
579:                 );
580:             }
581:         }
582: 
583:         return $queries;
584:     }
585: }
586: