· 8 years ago · Feb 07, 2018, 02:50 PM
1<?php
2
3namespace Phalcon\Db\Dialect;
4
5use Phalcon\Db\Column;
6use Phalcon\Db\Exception;
7
8/**
9 * Phalcon\Db\Dialect\Sqlsrv
10 * Generates database specific SQL for the MsSQL RDBMS.
11 */
12class Sqlsrv extends \Phalcon\Db\Dialect
13{
14 /**
15 * Escape Char.
16 *
17 * @var string
18 */
19 protected $_escapeChar = '"';
20
21 /**
22 * Generates the SQL for LIMIT clause
23 * <code>
24 * $sql = $dialect->limit('SELECT * FROM robots', 10);
25 * echo $sql; // SELECT * FROM robots LIMIT 10
26 * $sql = $dialect->limit('SELECTFROM robots', [10, 50]);
27 * echo $sql; // SELECT * FROM robots OFFSET 10 ROWS FETCH NEXT 50 ROWS ONLY
28 * </code>.
29 *
30 * @param string $sqlQuery
31 * @param mixed $number
32 *
33 * @return string
34 */
35 public function limit($sqlQuery, $number)
36 {
37 $offset = 0;
38 if (is_array($number)) {
39 if (isset($number[1]) && strlen($number[1])) {
40 $offset = $number[1];
41 }
42
43 $number = $number[0];
44 }
45
46 if (strpos($sqlQuery, 'ORDER BY') === false) {
47 $sqlQuery .= ' ORDER BY 1';
48 }
49
50 $selectStart = substr($sqlQuery, 0, 7);
51 $selectRemaining = substr($sqlQuery, 6);
52 $selectLimit = "TOP {$number} ";
53 return $selectStart.$selectLimit.$selectRemaining;
54
55// return $sqlQuery."LIMIT "
56 return $sqlQuery." OFFSET {$offset} ROWS FETCH NEXT {$number} ROWS ONLY";
57 }
58
59 /**
60 * Returns a SQL modified with a FOR UPDATE clause.
61 *
62 * <code>
63 * $sql = $dialect->forUpdate('SELECT * FROM robots');
64 * echo $sql; // SELECT * FROM robots WITH (UPDLOCK)
65 * </code>
66 */
67 public function forUpdate($sqlQuery)
68 {
69 return $sqlQuery.' WITH (UPDLOCK) ';
70 }
71
72 /**
73 * Returns a SQL modified with a LOCK IN SHARE MODE clause.
74 *
75 * <code>
76 * $sql = $dialect->sharedLock('SELECT * FROM robots');
77 * echo $sql; // SELECT * FROM robots WITH (NOLOCK)
78 * </code>
79 */
80 public function sharedLock($sqlQuery)
81 {
82 return $sqlQuery.' WITH (NOLOCK) ';
83 }
84
85 /**
86 * Gets the column name in MsSQL.
87 *
88 * @param mixed $column
89 *
90 * @return string
91 */
92 public function getColumnDefinition(\Phalcon\Db\ColumnInterface $column)
93 {
94 $columnSql = '';
95 $type = $column->getType();
96 if (is_string($type)) {
97 $columnSql .= $type;
98 $type = $column->getTypeReference();
99 }
100
101 switch ($type) {
102 case Column::TYPE_INTEGER:
103 if (empty($columnSql)) {
104 $columnSql .= 'INT';
105 }
106
107// $columnSql .= '('.$column->getSize().')';
108// if ($column->isUnsigned()) {
109// $columnSql .= ' UNSIGNED';
110// }
111 break;
112
113 case Column::TYPE_DATE:
114 if (empty($columnSql)) {
115 $columnSql .= 'DATE';
116 }
117 break;
118
119 case Column::TYPE_VARCHAR:
120 if (empty($columnSql)) {
121 $columnSql .= 'NVARCHAR';
122 }
123 $columnSql .= '('.$column->getSize().')';
124 break;
125
126 case Column::TYPE_DECIMAL:
127 if (empty($columnSql)) {
128 $columnSql .= 'DECIMAL';
129 }
130 $columnSql .= '('.$column->getSize().','.$column->getScale().')';
131// if ($column->isUnsigned()) {
132// $columnSql .= ' UNSIGNED';
133// }
134 break;
135
136 case Column::TYPE_DATETIME:
137 if (empty($columnSql)) {
138 $columnSql .= 'DATETIME';
139 }
140 break;
141
142 case Column::TYPE_TIMESTAMP:
143 if (empty($columnSql)) {
144 $columnSql .= 'TIMESTAMP';
145 }
146 break;
147
148 case Column::TYPE_CHAR:
149 if (empty($columnSql)) {
150 $columnSql .= 'CHAR';
151 }
152 $columnSql .= '('.$column->getSize().')';
153 break;
154
155 case Column::TYPE_TEXT:
156 if (empty($columnSql)) {
157 $columnSql .= 'NTEXT';
158 }
159 break;
160
161 case Column::TYPE_BOOLEAN:
162 if (empty($columnSql)) {
163 $columnSql .= 'BIT';
164 }
165 break;
166
167 case Column::TYPE_FLOAT:
168 if (empty($columnSql)) {
169 $columnSql .= 'FLOAT';
170 }
171 $size = $column->getSize();
172 if ($size) {
173// $scale = $column->getScale();
174// if ($scale) {
175// $columnSql .= '('.size.','.scale.')';
176// } else {
177 $columnSql .= '('.size.')';
178// }
179 }
180// if ($column->isUnsigned()) {
181// $columnSql .= ' UNSIGNED';
182// }
183 break;
184
185 case Column::TYPE_DOUBLE:
186 if (empty($columnSql)) {
187 $columnSql .= 'NUMERIC';
188 }
189 $size = $column->getSize();
190 if ($size) {
191 $scale = $column->getScale();
192 $columnSql .= '('.$size;
193 if ($scale) {
194 $columnSql .= ','.$scale.')';
195 } else {
196 $columnSql .= ')';
197 }
198 }
199// if ($column->isUnsigned()) {
200// $columnSql .= ' UNSIGNED';
201// }
202 break;
203
204 case Column::TYPE_BIGINTEGER:
205 if (empty($columnSql)) {
206 $columnSql .= 'BIGINT';
207 }
208 $size = $column->getSize();
209 if ($size) {
210 $columnSql .= '('.$size.')';
211 }
212// if ($column->isUnsigned()) {
213// $columnSql .= ' UNSIGNED';
214// }
215 break;
216
217 case Column::TYPE_TINYBLOB:
218 if (empty($columnSql)) {
219 $columnSql .= 'VARBINARY(255)';
220 }
221 break;
222
223 case Column::TYPE_BLOB:
224 case Column::TYPE_MEDIUMBLOB:
225 case Column::TYPE_LONGBLOB:
226 if (empty($columnSql)) {
227 $columnSql .= 'VARBINARY(MAX)';
228 }
229 break;
230
231 default:
232 if (empty($columnSql)) {
233 throw new Exception('Unrecognized MsSql data type at column '.$column->getName());
234 }
235
236 $typeValues = $column->getTypeValues();
237 if (!empty($typeValues)) {
238 if (is_array($typeValues)) {
239 $valueSql = '';
240 foreach ($typeValues as $value) {
241 $valueSql .= '"'.addcslashes($value, '"').'", ';
242 }
243 $columnSql .= '('.substr(valueSql, 0, -2).')';
244 } else {
245 $columnSql .= '("'.addcslashes($typeValues, '"').'")';
246 }
247 }
248 break;
249 }
250
251 return $columnSql;
252 }
253
254 /**
255 * Generates SQL to add a column to a table.
256 *
257 * @param string $tableName
258 * @param string $schemaName
259 * @param mixed $column
260 *
261 * @return string
262 */
263 public function addColumn($tableName, $schemaName, \Phalcon\Db\ColumnInterface $column)
264 {
265 $sql = 'ALTER TABLE '.$this->prepareTable($tableName, $schemaName).' ADD ['.$column->getName().'] '.$this->getColumnDefinition($column);
266
267 if ($column->hasDefault()) {
268 $defaultValue = $column->getDefault();
269 if (strpos(strtoupper($defaultValue), 'CURRENT_TIMESTAMP') !== false) {
270 $sql .= ' DEFAULT CURRENT_TIMESTAMP';
271 } else {
272 $sql .= ' DEFAULT "'.addcslashes($defaultValue, '"').'"';
273 }
274 }
275
276 if ($column->isNotNull()) {
277 $sql .= ' NOT NULL';
278 }
279
280 if ($column->isAutoIncrement()) {
281 $sql .= ' IDENTITY(1,1)';
282 }
283
284 if ($column->isFirst()) {
285 $sql .= ' FIRST';
286 } else {
287 $afterPosition = $column->getAfterPosition();
288 if ($afterPosition) {
289 $sql .= ' AFTER '.$afterPosition;
290 }
291 }
292
293 return $sql;
294 }
295
296 /**
297 * Generates SQL to modify a column in a table.
298 *
299 * @param string $tableName
300 * @param string $schemaName
301 * @param mixed $column
302 * @param mixed $currentColumn
303 *
304 * @return string
305 */
306 public function modifyColumn($tableName, $schemaName, \Phalcon\Db\ColumnInterface $column, \Phalcon\Db\ColumnInterface $currentColumn = null)
307 {
308 $sql = 'ALTER TABLE '.$this->prepareTable($tableName, $schemaName).' ALTER COLUMN ['.$column->getName().'] '.$this->getColumnDefinition($column);
309
310 if ($column->hasDefault()) {
311 $defaultValue = $column->getDefault();
312 if (strpos(strtoupper($defaultValue), 'CURRENT_TIMESTAMP') !== false) {
313 $sql .= ' DEFAULT CURRENT_TIMESTAMP';
314 } else {
315 $sql .= ' DEFAULT "'.addcslashes($defaultValue, '"').'"';
316 }
317 }
318
319 if ($column->isNotNull()) {
320 $sql .= ' NOT NULL';
321 }
322
323 if ($column->isAutoIncrement()) {
324 $sql .= ' IDENTITY(1,1)';
325 }
326
327 return $sql;
328 }
329
330 /**
331 * Generates SQL to delete a column from a table.
332 *
333 * @param string $tableName
334 * @param string $schemaName
335 * @param string $columnName
336 *
337 * @return string
338 */
339 public function dropColumn($tableName, $schemaName, $columnName)
340 {
341 return 'ALTER TABLE '.$this->prepareTable($tableName, $schemaName).' DROP COLUMN ['.$columnName.']';
342 }
343
344 /**
345 * Generates SQL to add an index to a table.
346 *
347 * @param string $tableName
348 * @param string $schemaName
349 * @param mixed $index
350 *
351 * @return string
352 */
353 public function addIndex($tableName, $schemaName, \Phalcon\Db\IndexInterface $index)
354 {
355 $indexType = $index->getType();
356 if (!empty($indexType)) {
357 $sql = ' CREATE '.$indexType.' INDEX ';
358 } else {
359 $sql = ' CREATE INDEX ';
360 }
361
362 $sql = '['.$index->getName().'] ON '.$this->prepareTable($tableName, $schemaName).' ('.$this->getColumnList($index->getColumns()).')';
363
364 return $sql;
365 }
366
367 /**
368 * Generates SQL to delete an index from a table.
369 *
370 * @param string $tableName
371 * @param string $schemaName
372 * @param string $indexName
373 *
374 * @return string
375 */
376 public function dropIndex($tableName, $schemaName, $indexName)
377 {
378 return 'DROP INDEX ['.$indexName.'] ON '.$this->prepareTable($tableName, $schemaName);
379 }
380
381 /**
382 * Generates SQL to add the primary key to a table.
383 *
384 * @param string $tableName
385 * @param string $schemaName
386 * @param mixed $index
387 *
388 * @return string
389 */
390 public function addPrimaryKey($tableName, $schemaName, \Phalcon\Db\IndexInterface $index)
391 {
392 return 'ALTER TABLE '.$this->prepareTable($tableName, $schemaName).' ADD PRIMARY KEY ('.$this->getColumnList($index->getColumns()).')';
393 }
394
395 /**
396 * Generates SQL to delete primary key from a table.
397 *
398 * @param string $tableName
399 * @param string $schemaName
400 *
401 * @return string
402 */
403 public function dropPrimaryKey($tableName, $schemaName)
404 {
405 return 'ALTER TABLE '.$this->prepareTable($tableName, $schemaName).' DROP PRIMARY KEY';
406 }
407
408 /**
409 * Generates SQL to add an index to a table.
410 *
411 * @param string $tableName
412 * @param string $schemaName
413 * @param mixed $reference
414 *
415 * @return string
416 */
417 public function addForeignKey($tableName, $schemaName, \Phalcon\Db\ReferenceInterface $reference)
418 {
419 $sql = 'ALTER TABLE '.$this->prepareTable($tableName, $schemaName).' ADD CONSTRAINT ['.$reference->getName().'] FOREIGN KEY ('.$this->getColumnList($reference->getColumns()).') REFERENCES '.$this->prepareTable($reference->getReferencedTable(), $reference->getReferencedSchema()).'('.$this->getColumnList($reference->getReferencedColumns()).')';
420
421 $onDelete = $reference->getOnDelete();
422 if (!empty($onDelete)) {
423 $sql .= ' ON DELETE '.$onDelete;
424 }
425
426 $onUpdate = $reference->getOnUpdate();
427 if (!empty($onUpdate)) {
428 $sql .= ' ON UPDATE '.$onUpdate;
429 }
430
431 return $sql;
432 }
433
434 /**
435 * Generates SQL to delete a foreign key from a table.
436 *
437 * @param string $tableName
438 * @param string $schemaName
439 * @param string $referenceName
440 *
441 * @return string
442 */
443 public function dropForeignKey($tableName, $schemaName, $referenceName)
444 {
445 return 'ALTER TABLE '.$this->prepareTable($tableName, $schemaName).' DROP FOREIGN KEY ['.$referenceName.']';
446 }
447
448 /**
449 * Generates SQL to create a table.
450 *
451 * @param string $tableName
452 * @param string $schemaName
453 * @param array $definition
454 *
455 * @return string
456 */
457 public function createTable($tableName, $schemaName, array $definition)
458 {
459 if (isset($definition['columns']) === false) {
460 throw new Exception("The index 'columns' is required in the definition array");
461 }
462
463 $table = $this->prepareTable($tableName, $schemaName);
464
465 $temporary = false;
466 if (isset($definition['options']) === true) {
467 $temporary = (bool) $definition['options']['temporary'];
468 }
469
470 /*
471 * Create a temporary o normal table
472 */
473 if ($temporary) {
474 $sql = 'CREATE TEMPORARY TABLE '.$table." (\n\t";
475 } else {
476 $sql = 'CREATE TABLE '.$table." (\n\t";
477 }
478
479 $createLines = [];
480 foreach ($definition['columns'] as $column) {
481 $columnLine = '['.$column->getName().'] '.$this->getColumnDefinition($column);
482
483 /*
484 * Add a Default clause
485 */
486 if ($column->hasDefault()) {
487 $defaultValue = $column->getDefault();
488 if (strpos(strtoupper($defaultValue), 'CURRENT_TIMESTAMP') !== false) {
489 $columnLine .= ' DEFAULT CURRENT_TIMESTAMP';
490 } else {
491 $columnLine .= ' DEFAULT "'.addcslashes($defaultValue, '"').'"';
492 }
493 }
494
495 /*
496 * Add a NOT NULL clause
497 */
498 if ($column->isNotNull()) {
499 $columnLine .= ' NOT NULL';
500 }
501
502 /*
503 * Add an AUTO_INCREMENT clause
504 */
505 if ($column->isAutoIncrement()) {
506 $columnLine .= ' IDENTITY(1,1)';
507 }
508
509 /*
510 * Mark the column as primary key
511 */
512 if ($column->isPrimary()) {
513 $columnLine .= ' PRIMARY KEY';
514 }
515
516 $createLines[] = $columnLine;
517 }
518
519 /*
520 * Create related indexes
521 */
522 if (isset($definition['indexes']) === true) {
523 foreach ($definition['indexes'] as $index) {
524 $indexName = $index->getName();
525 $indexType = $index->getType();
526
527 /*
528 * If the index name is primary we add a primary key
529 */
530 if ($indexName == 'PRIMARY') {
531 $indexSql = 'PRIMARY KEY ('.$this->getColumnList($index->getColumns()).')';
532 } else {
533 if (!empty($indexType)) {
534 $indexSql = $indexType.' KEY ['.$indexName.'] ('.$this->getColumnList($index->getColumns()).')';
535 } else {
536 $indexSql = 'KEY ['.$indexName.'] ('.$this->getColumnList($index->getColumns()).')';
537 }
538 }
539
540 $createLines[] = $indexSql;
541 }
542 }
543
544 /*
545 * Create related references
546 */
547 if (isset($definition['references']) === true) {
548 foreach ($definition['references'] as $reference) {
549 $referenceSql = 'CONSTRAINT ['.$reference->getName().'] FOREIGN KEY ('.$this->getColumnList($reference->getColumns()).')'
550 .' REFERENCES ['.$reference->getReferencedTable().'] ('.$this->getColumnList($reference->getReferencedColumns()).')';
551
552 $onDelete = $reference->getOnDelete();
553 if (!empty($onDelete)) {
554 $referenceSql .= ' ON DELETE '.onDelete;
555 }
556
557 $onUpdate = $reference->getOnUpdate();
558 if (!empty($onUpdate)) {
559 $referenceSql .= ' ON UPDATE '.onUpdate;
560 }
561
562 $createLines[] = $referenceSql;
563 }
564 }
565
566 $sql .= implode(",\n\t", $createLines)."\n)";
567 if (isset($definition['options'])) {
568 $sql .= ' '.$this->_getTableOptions($definition);
569 }
570
571 return $sql;
572 }
573
574 /**
575 * Generates SQL to drop a table.
576 *
577 * @param string $tableName
578 * @param string $schemaName
579 * @param bool $ifExists
580 *
581 * @return string
582 */
583 public function dropTable($tableName, $schemaName = null, $ifExists = true)
584 {
585 $table = $this->prepareTable($tableName, $schemaName);
586
587 if ($ifExists) {
588 $sql = 'DROP TABLE IF EXISTS '.$table;
589 } else {
590 $sql = 'DROP TABLE '.$table;
591 }
592
593 return $sql;
594 }
595
596 /**
597 * Generates SQL to create a view.
598 *
599 * @param string $viewName
600 * @param array $definition
601 * @param string $schemaName
602 *
603 * @return string
604 */
605 public function createView($viewName, array $definition, $schemaName = null)
606 {
607 if (!isset($definition['sql'])) {
608 throw new Exception("The index 'sql' is required in the definition array");
609 }
610
611 return 'CREATE VIEW '.$this->prepareTable($viewName, $schemaName).' AS '.$definition['sql'];
612 }
613
614 /**
615 * Generates SQL to drop a view.
616 *
617 * @param string $viewName
618 * @param string $schemaName
619 * @param bool $ifExists
620 *
621 * @return string
622 */
623 public function dropView($viewName, $schemaName = null, $ifExists = true)
624 {
625 $view = $this->prepareTable($viewName, $schemaName);
626
627 if ($ifExists) {
628 $sql = 'DROP VIEW IF EXISTS '.$view;
629 } else {
630 $sql = 'DROP VIEW '.$view;
631 }
632
633 return $sql;
634 }
635
636 /**
637 * Generates SQL checking for the existence of a schema.table
638 * <code>
639 * echo $dialect->tableExists("posts", "blog");
640 * echo $dialect->tableExists("posts");
641 * </code>.
642 *
643 * @param string $tableName
644 * @param string $schemaName
645 *
646 * @return string
647 */
648 public function tableExists($tableName, $schemaName = null)
649 {
650 $sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{$tableName}'";
651
652 if ($schemaName) {
653 $sql .= " AND TABLE_SCHEMA = '{$schemaName}'";
654 }
655
656 return $sql;
657 }
658
659 /**
660 * Generates SQL checking for the existence of a schema.view.
661 *
662 * @param string $viewName
663 * @param string $schemaName
664 *
665 * @return string
666 */
667 public function viewExists($viewName, $schemaName = null)
668 {
669 $sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = '{$viewName}'";
670
671 if ($schemaName) {
672 $sql .= " AND TABLE_SCHEMA = '{$schemaName}'";
673 }
674
675 return $sql;
676 }
677
678 /**
679 * Generates SQL describing a table
680 * <code>
681 * print_r($dialect->describeColumns("posts"));
682 * </code>.
683 *
684 * @param string $table
685 * @param string $schema
686 *
687 * @return string
688 */
689 public function describeColumns($table, $schema = null)
690 {
691 $sql = "exec sp_columns @table_name = '{$table}'";
692 if ($schema) {
693 $sql .= ", @table_owner = '{$schema}'";
694 }
695
696 return $sql;
697 }
698
699 /**
700 * List all tables in database
701 * <code>
702 * print_r($dialect->listTables("blog"))
703 * </code>.
704 *
705 * @param string $schemaName
706 *
707 * @return string
708 */
709 public function listTables($schemaName = null)
710 {
711 $sql = 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES';
712 if ($schemaName) {
713 $sql .= " WHERE TABLE_SCHEMA = '{$schemaName}'";
714 }
715
716 return $sql;
717 }
718
719 /**
720 * Generates the SQL to list all views of a schema or user.
721 *
722 * @param string $schemaName
723 *
724 * @return string
725 */
726 public function listViews($schemaName = null)
727 {
728 $sql = 'SELECT TABLE_NAME AS view_name FROM INFORMATION_SCHEMA.VIEWS';
729 if ($schemaName) {
730 $sql .= " WHERE TABLE_SCHEMA = '{$schemaName}'";
731 }
732
733 return $sql.' ORDER BY view_name';
734 }
735
736 /**
737 * Generates SQL to query indexes on a table.
738 *
739 * @param string $table
740 * @param string $schema
741 *
742 * @return string
743 */
744 public function describeIndexes($table, $schema = null)
745 {
746 $sql = "SELECT * FROM sys.indexes ind INNER JOIN sys.tables t ON ind.object_id = t.object_id WHERE t.name = '{$table}'";
747 if ($schema) {
748 }
749
750 return $sql;
751 }
752
753 /**
754 * Generates SQL to query foreign keys on a table.
755 *
756 * @param string $table
757 * @param string $schema
758 *
759 * @return string
760 */
761 public function describeReferences($table, $schema = null)
762 {
763 $sql = 'SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_SCHEMA,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IS NOT NULL AND ';
764 if ($schema) {
765 $sql .= "CONSTRAINT_SCHEMA = '".$schema."' AND TABLE_NAME = '".$table."'";
766 } else {
767 $sql .= "TABLE_NAME = '".$table."'";
768 }
769
770 return $sql;
771 }
772
773 /**
774 * Generates the SQL to describe the table creation options.
775 *
776 * @param string $table
777 * @param string $schema
778 *
779 * @return string
780 */
781 public function tableOptions($table, $schema = null)
782 {
783 $sql = 'SELECT TABLES.TABLE_TYPE AS table_type,TABLES.AUTO_INCREMENT AS auto_increment,TABLES.ENGINE AS engine,TABLES.TABLE_COLLATION AS table_collation FROM INFORMATION_SCHEMA.TABLES WHERE ';
784 if ($schema) {
785 $sql .= "TABLES.TABLE_SCHEMA = '".$schema."' AND TABLES.TABLE_NAME = '".$table."'";
786 } else {
787 $sql .= "TABLES.TABLE_NAME = '".$table."'";
788 }
789
790 return $sql;
791 }
792
793 /**
794 * Generates SQL to add the table creation options.
795 *
796 * @param array $definition
797 *
798 * @return string
799 */
800 protected function _getTableOptions($definition)
801 {
802 if (isset($definition['options']) === true) {
803 $tableOptions = array();
804 $options = $definition['options'];
805
806 /*
807 * Check if there is an ENGINE option
808 */
809 if (isset($options['ENGINE']) === true &&
810 $options['ENGINE'] == true) {
811 $tableOptions[] = 'ENGINE='.$options['ENGINE'];
812 }
813
814 /*
815 * Check if there is an AUTO_INCREMENT option
816 */
817 if (isset($options['AUTO_INCREMENT']) === true &&
818 $options['AUTO_INCREMENT'] == true) {
819 $tableOptions[] = 'AUTO_INCREMENT='.$options['AUTO_INCREMENT'];
820 }
821
822 /*
823 * Check if there is a TABLE_COLLATION option
824 */
825 if (isset($options['TABLE_COLLATION']) === true &&
826 $options['TABLE_COLLATION'] == true) {
827 $collationParts = explode('_', $options['TABLE_COLLATION']);
828 $tableOptions[] = 'DEFAULT CHARSET='.$collationParts[0];
829 $tableOptions[] = 'COLLATE='.$options['TABLE_COLLATION'];
830 }
831
832 if (count($tableOptions) > 0) {
833 return implode(' ', $tableOptions);
834 }
835 }
836
837 return '';
838 }
839
840 /**
841 * Generates SQL primary key a table.
842 *
843 * @param string $table
844 * @param string $schema
845 *
846 * @return string
847 */
848 public function getPrimaryKey($table, $schema = null)
849 {
850 $sql = "exec sp_pkeys @table_name = '{$table}'";
851 if ($schema) {
852 $sql .= ", @table_owner = '{$schema}'";
853 }
854
855 return $sql;
856 }
857}