1: <?php namespace Laravel\Database\Schema\Grammars;
2:
3: use Laravel\Fluent;
4: use Laravel\Database\Schema\Table;
5:
6: class SQLServer extends Grammar {
7:
8: /**
9: * The keyword identifier for the database system.
10: *
11: * @var string
12: */
13: public $wrapper = '[%s]';
14:
15: /**
16: * Generate the SQL statements for a table creation command.
17: *
18: * @param Table $table
19: * @param Fluent $command
20: * @return array
21: */
22: public function create(Table $table, Fluent $command)
23: {
24: $columns = implode(', ', $this->columns($table));
25:
26: // First we will generate the base table creation statement. Other than auto
27: // incrementing keys, no indexes will be created during the first creation
28: // of the table as they're added in separate commands.
29: $sql = 'CREATE TABLE '.$this->wrap($table).' ('.$columns.')';
30:
31: return $sql;
32: }
33:
34: /**
35: * Generate the SQL statements for a table modification command.
36: *
37: * @param Table $table
38: * @param Fluent $command
39: * @return array
40: */
41: public function add(Table $table, Fluent $command)
42: {
43: $columns = $this->columns($table);
44:
45: // Once we have the array of column definitions, we need to add "add" to the
46: // front of each definition, then we'll concatenate the definitions
47: // using commas like normal and generate the SQL.
48: $columns = implode(', ', array_map(function($column)
49: {
50: return 'ADD '.$column;
51:
52: }, $columns));
53:
54: return 'ALTER TABLE '.$this->wrap($table).' '.$columns;
55: }
56:
57: /**
58: * Create the individual column definitions for the table.
59: *
60: * @param Table $table
61: * @return array
62: */
63: protected function columns(Table $table)
64: {
65: $columns = array();
66:
67: foreach ($table->columns as $column)
68: {
69: // Each of the data type's have their own definition creation method,
70: // which is responsible for creating the SQL for the type. This lets
71: // us to keep the syntax easy and fluent, while translating the
72: // types to the types used by the database.
73: $sql = $this->wrap($column).' '.$this->type($column);
74:
75: $elements = array('incrementer', 'nullable', 'defaults');
76:
77: foreach ($elements as $element)
78: {
79: $sql .= $this->$element($table, $column);
80: }
81:
82: $columns[] = $sql;
83: }
84:
85: return $columns;
86: }
87:
88: /**
89: * Get the SQL syntax for indicating if a column is nullable.
90: *
91: * @param Table $table
92: * @param Fluent $column
93: * @return string
94: */
95: protected function nullable(Table $table, Fluent $column)
96: {
97: return ($column->nullable) ? ' NULL' : ' NOT NULL';
98: }
99:
100: /**
101: * Get the SQL syntax for specifying a default value on a column.
102: *
103: * @param Table $table
104: * @param Fluent $column
105: * @return string
106: */
107: protected function defaults(Table $table, Fluent $column)
108: {
109: if ( ! is_null($column->default))
110: {
111: return " DEFAULT '".$this->default_value($column->default)."'";
112: }
113: }
114:
115: /**
116: * Get the SQL syntax for defining an auto-incrementing column.
117: *
118: * @param Table $table
119: * @param Fluent $column
120: * @return string
121: */
122: protected function incrementer(Table $table, Fluent $column)
123: {
124: if ($column->type == 'integer' and $column->increment)
125: {
126: return ' IDENTITY PRIMARY KEY';
127: }
128: }
129:
130: /**
131: * Generate the SQL statement for creating a primary key.
132: *
133: * @param Table $table
134: * @param Fluent $command
135: * @return string
136: */
137: public function primary(Table $table, Fluent $command)
138: {
139: $name = $command->name;
140:
141: $columns = $this->columnize($command->columns);
142:
143: return 'ALTER TABLE '.$this->wrap($table)." ADD CONSTRAINT {$name} PRIMARY KEY ({$columns})";
144: }
145:
146: /**
147: * Generate the SQL statement for creating a unique index.
148: *
149: * @param Table $table
150: * @param Fluent $command
151: * @return string
152: */
153: public function unique(Table $table, Fluent $command)
154: {
155: return $this->key($table, $command, true);
156: }
157:
158: /**
159: * Generate the SQL statement for creating a full-text index.
160: *
161: * @param Table $table
162: * @param Fluent $command
163: * @return string
164: */
165: public function fulltext(Table $table, Fluent $command)
166: {
167: $columns = $this->columnize($command->columns);
168:
169: $table = $this->wrap($table);
170:
171: // SQL Server requires the creation of a full-text "catalog" before creating
172: // a full-text index, so we'll first create the catalog then add another
173: // separate statement for the index.
174: $sql[] = "CREATE FULLTEXT CATALOG {$command->catalog}";
175:
176: $create = "CREATE FULLTEXT INDEX ON ".$table." ({$columns}) ";
177:
178: // Full-text indexes must specify a unique, non-null column as the index
179: // "key" and this should have been created manually by the developer in
180: // a separate column addition command.
181: $sql[] = $create .= "KEY INDEX {$command->key} ON {$command->catalog}";
182:
183: return $sql;
184: }
185:
186: /**
187: * Generate the SQL statement for creating a regular index.
188: *
189: * @param Table $table
190: * @param Fluent $command
191: * @return string
192: */
193: public function index(Table $table, Fluent $command)
194: {
195: return $this->key($table, $command);
196: }
197:
198: /**
199: * Generate the SQL statement for creating a new index.
200: *
201: * @param Table $table
202: * @param Fluent $command
203: * @param bool $unique
204: * @return string
205: */
206: protected function key(Table $table, Fluent $command, $unique = false)
207: {
208: $columns = $this->columnize($command->columns);
209:
210: $create = ($unique) ? 'CREATE UNIQUE' : 'CREATE';
211:
212: return $create." INDEX {$command->name} ON ".$this->wrap($table)." ({$columns})";
213: }
214:
215: /**
216: * Generate the SQL statement for a rename table command.
217: *
218: * @param Table $table
219: * @param Fluent $command
220: * @return string
221: */
222: public function rename(Table $table, Fluent $command)
223: {
224: return 'ALTER TABLE '.$this->wrap($table).' RENAME TO '.$this->wrap($command->name);
225: }
226:
227: /**
228: * Generate the SQL statement for a drop column command.
229: *
230: * @param Table $table
231: * @param Fluent $command
232: * @return string
233: */
234: public function drop_column(Table $table, Fluent $command)
235: {
236: $columns = array_map(array($this, 'wrap'), $command->columns);
237:
238: // Once we have the array of column names, we need to add "drop" to the front
239: // of each column, then we'll concatenate the columns using commas and
240: // generate the alter statement SQL.
241: $columns = implode(', ', array_map(function($column)
242: {
243: return 'DROP '.$column;
244:
245: }, $columns));
246:
247: return 'ALTER TABLE '.$this->wrap($table).' '.$columns;
248: }
249:
250: /**
251: * Generate the SQL statement for a drop primary key command.
252: *
253: * @param Table $table
254: * @param Fluent $command
255: * @return string
256: */
257: public function drop_primary(Table $table, Fluent $command)
258: {
259: return 'ALTER TABLE '.$this->wrap($table).' DROP CONSTRAINT '.$command->name;
260: }
261:
262: /**
263: * Generate the SQL statement for a drop unique key command.
264: *
265: * @param Table $table
266: * @param Fluent $command
267: * @return string
268: */
269: public function drop_unique(Table $table, Fluent $command)
270: {
271: return $this->drop_key($table, $command);
272: }
273:
274: /**
275: * Generate the SQL statement for a drop full-text key command.
276: *
277: * @param Table $table
278: * @param Fluent $command
279: * @return string
280: */
281: public function drop_fulltext(Table $table, Fluent $command)
282: {
283: $sql[] = "DROP FULLTEXT INDEX ".$command->name;
284:
285: $sql[] = "DROP FULLTEXT CATALOG ".$command->catalog;
286:
287: return $sql;
288: }
289:
290: /**
291: * Generate the SQL statement for a drop index command.
292: *
293: * @param Table $table
294: * @param Fluent $command
295: * @return string
296: */
297: public function drop_index(Table $table, Fluent $command)
298: {
299: return $this->drop_key($table, $command);
300: }
301:
302: /**
303: * Generate the SQL statement for a drop key command.
304: *
305: * @param Table $table
306: * @param Fluent $command
307: * @return string
308: */
309: protected function drop_key(Table $table, Fluent $command)
310: {
311: return "DROP INDEX {$command->name} ON ".$this->wrap($table);
312: }
313:
314: /**
315: * Drop a foreign key constraint from the table.
316: *
317: * @param Table $table
318: * @param Fluent $command
319: * @return string
320: */
321: public function drop_foreign(Table $table, Fluent $command)
322: {
323: return $this->drop_constraint($table, $command);
324: }
325:
326: /**
327: * Generate the data-type definition for a string.
328: *
329: * @param Fluent $column
330: * @return string
331: */
332: protected function type_string(Fluent $column)
333: {
334: return 'NVARCHAR('.$column->length.')';
335: }
336:
337: /**
338: * Generate the data-type definition for an integer.
339: *
340: * @param Fluent $column
341: * @return string
342: */
343: protected function type_integer(Fluent $column)
344: {
345: return 'INT';
346: }
347:
348: /**
349: * Generate the data-type definition for an integer.
350: *
351: * @param Fluent $column
352: * @return string
353: */
354: protected function type_float(Fluent $column)
355: {
356: return 'FLOAT';
357: }
358:
359: /**
360: * Generate the data-type definition for a decimal.
361: *
362: * @param Fluent $column
363: * @return string
364: */
365: protected function type_decimal(Fluent $column)
366: {
367: return "DECIMAL({$column->precision}, {$column->scale})";
368: }
369:
370: /**
371: * Generate the data-type definition for a boolean.
372: *
373: * @param Fluent $column
374: * @return string
375: */
376: protected function type_boolean(Fluent $column)
377: {
378: return 'TINYINT';
379: }
380:
381: /**
382: * Generate the data-type definition for a date.
383: *
384: * @param Fluent $column
385: * @return string
386: */
387: protected function type_date(Fluent $column)
388: {
389: return 'DATETIME';
390: }
391:
392: /**
393: * Generate the data-type definition for a timestamp.
394: *
395: * @param Fluent $column
396: * @return string
397: */
398: protected function type_timestamp(Fluent $column)
399: {
400: return 'TIMESTAMP';
401: }
402:
403: /**
404: * Generate the data-type definition for a text column.
405: *
406: * @param Fluent $column
407: * @return string
408: */
409: protected function type_text(Fluent $column)
410: {
411: return 'NVARCHAR(MAX)';
412: }
413:
414: /**
415: * Generate the data-type definition for a blob.
416: *
417: * @param Fluent $column
418: * @return string
419: */
420: protected function type_blob(Fluent $column)
421: {
422: return 'VARBINARY(MAX)';
423: }
424:
425: }