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