1: <?php namespace Laravel\Database\Schema\Grammars;
2:
3: use Laravel\Fluent;
4: use Laravel\Database\Schema\Table;
5:
6: class SQLite 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 incrementing
20: // keys, no indexes will be created during the first creation of the table since
21: // they will be added in separate commands.
22: $sql = 'CREATE TABLE '.$this->wrap($table).' ('.$columns;
23:
24: // SQLite does not allow adding a primary key as a command apart from the creation
25: // of the table, so we'll need to sniff out any primary keys here and add them to
26: // the table now during this command.
27: $primary = array_first($table->commands, function($key, $value)
28: {
29: return $value->type == 'primary';
30: });
31:
32: // If we found primary keys in the array of commands, we'll create the SQL for
33: // the key addition and append it to the SQL table creation statement for
34: // the schema table so the index is properly generated.
35: if ( ! is_null($primary))
36: {
37: $columns = $this->columnize($primary->columns);
38:
39: $sql .= ", PRIMARY KEY ({$columns})";
40: }
41:
42: return $sql .= ')';
43: }
44:
45: /**
46: * Generate the SQL statements for a table modification command.
47: *
48: * @param Table $table
49: * @param Fluent $command
50: * @return array
51: */
52: public function add(Table $table, Fluent $command)
53: {
54: $columns = $this->columns($table);
55:
56: // Once we have the array of column definitions, we need to add "add" to the
57: // front of each definition, then we'll concatenate the definitions
58: // using commas like normal and generate the SQL.
59: $columns = array_map(function($column)
60: {
61: return 'ADD COLUMN '.$column;
62:
63: }, $columns);
64:
65: // SQLite only allows one column to be added in an ALTER statement,
66: // so we will create an array of statements and return them all to
67: // the schema manager for separate execution.
68: foreach ($columns as $column)
69: {
70: $sql[] = 'ALTER TABLE '.$this->wrap($table).' '.$column;
71: }
72:
73: return (array) $sql;
74: }
75:
76: /**
77: * Create the individual column definitions for the table.
78: *
79: * @param Table $table
80: * @return array
81: */
82: protected function columns(Table $table)
83: {
84: $columns = array();
85:
86: foreach ($table->columns as $column)
87: {
88: // Each of the data type's have their own definition creation method
89: // which is responsible for creating the SQL for the type. This lets
90: // us keep the syntax easy and fluent, while translating the
91: // types to the types used by the database.
92: $sql = $this->wrap($column).' '.$this->type($column);
93:
94: $elements = array('nullable', 'defaults', 'incrementer');
95:
96: foreach ($elements as $element)
97: {
98: $sql .= $this->$element($table, $column);
99: }
100:
101: $columns[] = $sql;
102: }
103:
104: return $columns;
105: }
106:
107: /**
108: * Get the SQL syntax for indicating if a column is nullable.
109: *
110: * @param Table $table
111: * @param Fluent $column
112: * @return string
113: */
114: protected function nullable(Table $table, Fluent $column)
115: {
116: return ' NULL';
117: }
118:
119: /**
120: * Get the SQL syntax for specifying a default value on a column.
121: *
122: * @param Table $table
123: * @param Fluent $column
124: * @return string
125: */
126: protected function defaults(Table $table, Fluent $column)
127: {
128: if ( ! is_null($column->default))
129: {
130: return ' DEFAULT '.$this->wrap($this->default_value($column->default));
131: }
132: }
133:
134: /**
135: * Get the SQL syntax for defining an auto-incrementing column.
136: *
137: * @param Table $table
138: * @param Fluent $column
139: * @return string
140: */
141: protected function incrementer(Table $table, Fluent $column)
142: {
143: if ($column->type == 'integer' and $column->increment)
144: {
145: return ' PRIMARY KEY AUTOINCREMENT';
146: }
147: }
148:
149: /**
150: * Generate the SQL statement for creating a unique index.
151: *
152: * @param Table $table
153: * @param Fluent $command
154: * @return string
155: */
156: public function unique(Table $table, Fluent $command)
157: {
158: return $this->key($table, $command, true);
159: }
160:
161: /**
162: * Generate the SQL statement for creating a full-text index.
163: *
164: * @param Table $table
165: * @param Fluent $command
166: * @return string
167: */
168: public function fulltext(Table $table, Fluent $command)
169: {
170: $columns = $this->columnize($command->columns);
171:
172: return 'CREATE VIRTUAL TABLE '.$this->wrap($table)." USING fts4({$columns})";
173: }
174:
175: /**
176: * Generate the SQL statement for creating a regular index.
177: *
178: * @param Table $table
179: * @param Fluent $command
180: * @return string
181: */
182: public function index(Table $table, Fluent $command)
183: {
184: return $this->key($table, $command);
185: }
186:
187: /**
188: * Generate the SQL statement for creating a new index.
189: *
190: * @param Table $table
191: * @param Fluent $command
192: * @param bool $unique
193: * @return string
194: */
195: protected function key(Table $table, Fluent $command, $unique = false)
196: {
197: $columns = $this->columnize($command->columns);
198:
199: $create = ($unique) ? 'CREATE UNIQUE' : 'CREATE';
200:
201: return $create." INDEX {$command->name} ON ".$this->wrap($table)." ({$columns})";
202: }
203:
204: /**
205: * Generate the SQL statement for a rename table command.
206: *
207: * @param Table $table
208: * @param Fluent $command
209: * @return string
210: */
211: public function rename(Table $table, Fluent $command)
212: {
213: return 'ALTER TABLE '.$this->wrap($table).' RENAME TO '.$this->wrap($command->name);
214: }
215:
216: /**
217: * Generate the SQL statement for a drop unique key command.
218: *
219: * @param Table $table
220: * @param Fluent $command
221: * @return string
222: */
223: public function drop_unique(Table $table, Fluent $command)
224: {
225: return $this->drop_key($table, $command);
226: }
227:
228: /**
229: * Generate the SQL statement for a drop unique key command.
230: *
231: * @param Table $table
232: * @param Fluent $command
233: * @return string
234: */
235: public function drop_index(Table $table, Fluent $command)
236: {
237: return $this->drop_key($table, $command);
238: }
239:
240: /**
241: * Generate the SQL statement for a drop key command.
242: *
243: * @param Table $table
244: * @param Fluent $command
245: * @return string
246: */
247: protected function drop_key(Table $table, Fluent $command)
248: {
249: return 'DROP INDEX '.$this->wrap($command->name);
250: }
251:
252: /**
253: * Generate the data-type definition for a string.
254: *
255: * @param Fluent $column
256: * @return string
257: */
258: protected function type_string(Fluent $column)
259: {
260: return 'VARCHAR';
261: }
262:
263: /**
264: * Generate the data-type definition for an integer.
265: *
266: * @param Fluent $column
267: * @return string
268: */
269: protected function type_integer(Fluent $column)
270: {
271: return 'INTEGER';
272: }
273:
274: /**
275: * Generate the data-type definition for an integer.
276: *
277: * @param Fluent $column
278: * @return string
279: */
280: protected function type_float(Fluent $column)
281: {
282: return 'FLOAT';
283: }
284:
285: /**
286: * Generate the data-type definition for a decimal.
287: *
288: * @param Fluent $column
289: * @return string
290: */
291: protected function type_decimal(Fluent $column)
292: {
293: return 'FLOAT';
294: }
295:
296: /**
297: * Generate the data-type definition for a boolean.
298: *
299: * @param Fluent $column
300: * @return string
301: */
302: protected function type_boolean(Fluent $column)
303: {
304: return 'INTEGER';
305: }
306:
307: /**
308: * Generate the data-type definition for a date.
309: *
310: * @param Fluent $column
311: * @return string
312: */
313: protected function type_date(Fluent $column)
314: {
315: return 'DATETIME';
316: }
317:
318: /**
319: * Generate the data-type definition for a timestamp.
320: *
321: * @param Fluent $column
322: * @return string
323: */
324: protected function type_timestamp(Fluent $column)
325: {
326: return 'DATETIME';
327: }
328:
329: /**
330: * Generate the data-type definition for a text column.
331: *
332: * @param Fluent $column
333: * @return string
334: */
335: protected function type_text(Fluent $column)
336: {
337: return 'TEXT';
338: }
339:
340: /**
341: * Generate the data-type definition for a blob.
342: *
343: * @param Fluent $column
344: * @return string
345: */
346: protected function type_blob(Fluent $column)
347: {
348: return 'BLOB';
349: }
350:
351: }