· 8 years ago · Feb 27, 2018, 07:36 AM
1<?php
2
3namespace Illuminate\Database\Query\Grammars;
4
5use Illuminate\Support\Arr;
6use Illuminate\Database\Query\Builder;
7
8class SqlServerGrammar extends Grammar
9{
10 /**
11 * All of the available clause operators.
12 *
13 * @var array
14 */
15 protected $operators = [
16 '=', '<', '>', '<=', '>=', '!<', '!>', '<>', '!=',
17 'like', 'not like', 'ilike',
18 '&', '&=', '|', '|=', '^', '^=',
19 ];
20
21 /**
22 * Compile a select query into SQL.
23 *
24 * @param \Illuminate\Database\Query\Builder $query
25 * @return string
26 */
27 public function compileSelect(Builder $query)
28 {
29 // If order & offset is provided, we can use OFFSET...FETCH
30 if (!is_null($query->orders) && !is_null($query->offset)) {
31 return parent::compileSelect($query);
32 }
33
34 if (! $query->offset) {
35 return parent::compileSelect($query);
36 }
37
38 // If an offset is present on the query, we will need to wrap the query in
39 // a big "ANSI" offset syntax block. This is very nasty compared to the
40 // other database systems but is necessary for implementing features.
41 if (is_null($query->columns)) {
42 $query->columns = ['*'];
43 }
44
45 return $this->compileAnsiOffset(
46 $query, $this->compileComponents($query)
47 );
48 }
49
50 /**
51 * Compile the "select *" portion of the query.
52 *
53 * @param \Illuminate\Database\Query\Builder $query
54 * @param array $columns
55 * @return string|null
56 */
57 protected function compileColumns(Builder $query, $columns)
58 {
59 if (! is_null($query->aggregate)) {
60 return;
61 }
62
63 $select = $query->distinct ? 'select distinct ' : 'select ';
64
65 // If there is a limit on the query, but not an offset, we will add the top
66 // clause to the query, which serves as a "limit" type clause within the
67 // SQL Server system similar to the limit keywords available in MySQL.
68 if ($query->limit > 0 && is_null($query->offset)) {
69 $select .= 'top '.$query->limit.' ';
70 }
71
72 return $select.$this->columnize($columns);
73 }
74
75 /**
76 * Compile the "from" portion of the query.
77 *
78 * @param \Illuminate\Database\Query\Builder $query
79 * @param string $table
80 * @return string
81 */
82 protected function compileFrom(Builder $query, $table)
83 {
84 $from = parent::compileFrom($query, $table);
85
86 if (is_string($query->lock)) {
87 return $from.' '.$query->lock;
88 }
89
90 if (! is_null($query->lock)) {
91 return $from.' with(rowlock,'.($query->lock ? 'updlock,' : '').'holdlock)';
92 }
93
94 return $from;
95 }
96
97 /**
98 * Compile a "where date" clause.
99 *
100 * @param \Illuminate\Database\Query\Builder $query
101 * @param array $where
102 * @return string
103 */
104 protected function whereDate(Builder $query, $where)
105 {
106 $value = $this->parameter($where['value']);
107
108 return 'cast('.$this->wrap($where['column']).' as date) '.$where['operator'].' '.$value;
109 }
110
111 /**
112 * Create a full ANSI offset clause for the query.
113 *
114 * @param \Illuminate\Database\Query\Builder $query
115 * @param array $components
116 * @return string
117 */
118 protected function compileAnsiOffset(Builder $query, $components)
119 {
120 // An ORDER BY clause is required to make this offset query work, so if one does
121 // not exist we'll just create a dummy clause to trick the database and so it
122 // does not complain about the queries for not having an "order by" clause.
123 if (empty($components['orders'])) {
124 $components['orders'] = 'order by (select 0)';
125 }
126
127 // We need to add the row number to the query so we can compare it to the offset
128 // and limit values given for the statements. So we will add an expression to
129 // the "select" that will give back the row numbers on each of the records.
130 $components['columns'] .= $this->compileOver($components['orders']);
131
132 unset($components['orders']);
133
134 // Next we need to calculate the constraints that should be placed on the query
135 // to get the right offset and limit from our query but if there is no limit
136 // set we will just handle the offset only since that is all that matters.
137 $sql = $this->concatenate($components);
138
139 return $this->compileTableExpression($sql, $query);
140 }
141
142 /**
143 * Compile the over statement for a table expression.
144 *
145 * @param string $orderings
146 * @return string
147 */
148 protected function compileOver($orderings)
149 {
150 return ", row_number() over ({$orderings}) as row_num";
151 }
152
153 /**
154 * Compile a common table expression for a query.
155 *
156 * @param string $sql
157 * @param \Illuminate\Database\Query\Builder $query
158 * @return string
159 */
160 protected function compileTableExpression($sql, $query)
161 {
162 $constraint = $this->compileRowConstraint($query);
163
164 return "select * from ({$sql}) as temp_table where row_num {$constraint}";
165 }
166
167 /**
168 * Compile the limit / offset row constraint for a query.
169 *
170 * @param \Illuminate\Database\Query\Builder $query
171 * @return string
172 */
173 protected function compileRowConstraint($query)
174 {
175 $start = $query->offset + 1;
176
177 if ($query->limit > 0) {
178 $finish = $query->offset + $query->limit;
179
180 return "between {$start} and {$finish}";
181 }
182
183 return ">= {$start}";
184 }
185
186 /**
187 * Compile the random statement into SQL.
188 *
189 * @param string $seed
190 * @return string
191 */
192 public function compileRandom($seed)
193 {
194 return 'NEWID()';
195 }
196
197 /**
198 * Compile the "limit" portions of the query.
199 *
200 * @param \Illuminate\Database\Query\Builder $query
201 * @param int $limit
202 * @return string
203 */
204 protected function compileLimit(Builder $query, $limit)
205 {
206 // If offset is not provided, TOP syntax will handle limits
207 if (is_null($query->offset)) {
208 return '';
209 }
210
211 // If offset & orders exist, then use OFFSET...FETCH
212 if (!is_null($query->offset) && !is_null($query->orders)) {
213 return "OFFSET {$query->offset} ROWS FETCH NEXT {$limit} ROWS ONLY";
214 }
215
216 return '';
217 }
218
219 /**
220 * Compile the "offset" portions of the query.
221 *
222 * @param \Illuminate\Database\Query\Builder $query
223 * @param int $offset
224 * @return string
225 */
226 protected function compileOffset(Builder $query, $offset)
227 {
228 // If limit exists, compileLimit to handle both offset & limit.
229 if (!is_null($query->limit)) {
230 return '';
231 }
232
233 // ORDER BY has to exist for OFFSET to work
234 if (!is_null($query->orders)) {
235 return "OFFSET {$offset} ROWS";
236 }
237
238 return '';
239 }
240
241 /**
242 * Compile the lock into SQL.
243 *
244 * @param \Illuminate\Database\Query\Builder $query
245 * @param bool|string $value
246 * @return string
247 */
248 protected function compileLock(Builder $query, $value)
249 {
250 return '';
251 }
252
253 /**
254 * Compile an exists statement into SQL.
255 *
256 * @param \Illuminate\Database\Query\Builder $query
257 * @return string
258 */
259 public function compileExists(Builder $query)
260 {
261 $existsQuery = clone $query;
262
263 $existsQuery->columns = [];
264
265 return $this->compileSelect($existsQuery->selectRaw('1 [exists]')->limit(1));
266 }
267
268 /**
269 * Compile a delete statement into SQL.
270 *
271 * @param \Illuminate\Database\Query\Builder $query
272 * @return string
273 */
274 public function compileDelete(Builder $query)
275 {
276 $table = $this->wrapTable($query->from);
277
278 $where = is_array($query->wheres) ? $this->compileWheres($query) : '';
279
280 return isset($query->joins)
281 ? $this->compileDeleteWithJoins($query, $table, $where)
282 : trim("delete from {$table} {$where}");
283 }
284
285 /**
286 * Compile a delete statement with joins into SQL.
287 *
288 * @param \Illuminate\Database\Query\Builder $query
289 * @param string $table
290 * @param string $where
291 * @return string
292 */
293 protected function compileDeleteWithJoins(Builder $query, $table, $where)
294 {
295 $joins = ' '.$this->compileJoins($query, $query->joins);
296
297 $alias = strpos(strtolower($table), ' as ') !== false
298 ? explode(' as ', $table)[1] : $table;
299
300 return trim("delete {$alias} from {$table}{$joins} {$where}");
301 }
302
303 /**
304 * Compile a truncate table statement into SQL.
305 *
306 * @param \Illuminate\Database\Query\Builder $query
307 * @return array
308 */
309 public function compileTruncate(Builder $query)
310 {
311 return ['truncate table '.$this->wrapTable($query->from) => []];
312 }
313
314 /**
315 * Compile an update statement into SQL.
316 *
317 * @param \Illuminate\Database\Query\Builder $query
318 * @param array $values
319 * @return string
320 */
321 public function compileUpdate(Builder $query, $values)
322 {
323 list($table, $alias) = $this->parseUpdateTable($query->from);
324
325 // Each one of the columns in the update statements needs to be wrapped in the
326 // keyword identifiers, also a place-holder needs to be created for each of
327 // the values in the list of bindings so we can make the sets statements.
328 $columns = collect($values)->map(function ($value, $key) {
329 return $this->wrap($key).' = '.$this->parameter($value);
330 })->implode(', ');
331
332 // If the query has any "join" clauses, we will setup the joins on the builder
333 // and compile them so we can attach them to this update, as update queries
334 // can get join statements to attach to other tables when they're needed.
335 $joins = '';
336
337 if (isset($query->joins)) {
338 $joins = ' '.$this->compileJoins($query, $query->joins);
339 }
340
341 // Of course, update queries may also be constrained by where clauses so we'll
342 // need to compile the where clauses and attach it to the query so only the
343 // intended records are updated by the SQL statements we generate to run.
344 $where = $this->compileWheres($query);
345
346 if (! empty($joins)) {
347 return trim("update {$alias} set {$columns} from {$table}{$joins} {$where}");
348 }
349
350 return trim("update {$table}{$joins} set $columns $where");
351 }
352
353 /**
354 * Get the table and alias for the given table.
355 *
356 * @param string $table
357 * @return array
358 */
359 protected function parseUpdateTable($table)
360 {
361 $table = $alias = $this->wrapTable($table);
362
363 if (strpos(strtolower($table), '] as [') !== false) {
364 $alias = '['.explode('] as [', $table)[1];
365 }
366
367 return [$table, $alias];
368 }
369
370 /**
371 * Prepare the bindings for an update statement.
372 *
373 * @param array $bindings
374 * @param array $values
375 * @return array
376 */
377 public function prepareBindingsForUpdate(array $bindings, array $values)
378 {
379 // Update statements with joins in SQL Servers utilize an unique syntax. We need to
380 // take all of the bindings and put them on the end of this array since they are
381 // added to the end of the "where" clause statements as typical where clauses.
382 $bindingsWithoutJoin = Arr::except($bindings, 'join');
383
384 return array_values(
385 array_merge($values, $bindings['join'], Arr::flatten($bindingsWithoutJoin))
386 );
387 }
388
389 /**
390 * Determine if the grammar supports savepoints.
391 *
392 * @return bool
393 */
394 public function supportsSavepoints()
395 {
396 return true;
397 }
398
399 /**
400 * Compile the SQL statement to define a savepoint.
401 *
402 * @param string $name
403 * @return string
404 */
405 public function compileSavepoint($name)
406 {
407 return 'SAVE TRANSACTION '.$name;
408 }
409
410 /**
411 * Compile the SQL statement to execute a savepoint rollback.
412 *
413 * @param string $name
414 * @return string
415 */
416 public function compileSavepointRollBack($name)
417 {
418 return 'ROLLBACK TRANSACTION '.$name;
419 }
420
421 /**
422 * Get the format for database stored dates.
423 *
424 * @return string
425 */
426 public function getDateFormat()
427 {
428 return 'Y-m-d H:i:s.v';
429 }
430
431 /**
432 * Wrap a single string in keyword identifiers.
433 *
434 * @param string $value
435 * @return string
436 */
437 protected function wrapValue($value)
438 {
439 return $value === '*' ? $value : '['.str_replace(']', ']]', $value).']';
440 }
441
442 /**
443 * Wrap a table in keyword identifiers.
444 *
445 * @param \Illuminate\Database\Query\Expression|string $table
446 * @return string
447 */
448 public function wrapTable($table)
449 {
450 return $this->wrapTableValuedFunction(parent::wrapTable($table));
451 }
452
453 /**
454 * Wrap a table in keyword identifiers.
455 *
456 * @param string $table
457 * @return string
458 */
459 protected function wrapTableValuedFunction($table)
460 {
461 if (preg_match('/^(.+?)(\(.*?\))]$/', $table, $matches) === 1) {
462 $table = $matches[1].']'.$matches[2];
463 }
464
465 return $table;
466 }
467}