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