|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
/** |
|
4
|
|
|
* This file is part of Cycle ORM package. |
|
5
|
|
|
* |
|
6
|
|
|
* For the full copyright and license information, please view the LICENSE |
|
7
|
|
|
* file that was distributed with this source code. |
|
8
|
|
|
*/ |
|
9
|
|
|
|
|
10
|
|
|
declare(strict_types=1); |
|
11
|
|
|
|
|
12
|
|
|
namespace Cycle\Database\Driver\SQLServer; |
|
13
|
|
|
|
|
14
|
|
|
use Cycle\Database\Driver\Compiler; |
|
15
|
|
|
use Cycle\Database\Driver\Quoter; |
|
16
|
|
|
use Cycle\Database\Driver\SQLServer\Injection\CompileJson; |
|
17
|
|
|
use Cycle\Database\Exception\CompilerException; |
|
18
|
|
|
use Cycle\Database\Injection\Fragment; |
|
19
|
|
|
use Cycle\Database\Injection\FragmentInterface; |
|
20
|
|
|
use Cycle\Database\Injection\Parameter; |
|
21
|
|
|
use Cycle\Database\Query\QueryParameters; |
|
22
|
|
|
|
|
23
|
|
|
/** |
|
24
|
|
|
* Microsoft SQL server specific syntax compiler. |
|
25
|
|
|
*/ |
|
26
|
|
|
class SQLServerCompiler extends Compiler |
|
27
|
|
|
{ |
|
28
|
|
|
/** |
|
29
|
|
|
* Column to be used as ROW_NUMBER in fallback selection mechanism, attention! Amount of columns |
|
30
|
|
|
* in result set will be increaced by 1! |
|
31
|
|
|
*/ |
|
32
|
|
|
public const ROW_NUMBER = '_ROW_NUMBER_'; |
|
33
|
|
|
|
|
34
|
|
|
/** |
|
35
|
|
|
* @psalm-return non-empty-string |
|
36
|
|
|
*/ |
|
37
|
|
|
protected function insertQuery(QueryParameters $params, Quoter $q, array $tokens): string |
|
38
|
|
|
{ |
|
39
|
|
|
if (empty($tokens['return'])) { |
|
40
|
450 |
|
return parent::insertQuery($params, $q, $tokens); |
|
41
|
|
|
} |
|
42
|
450 |
|
|
|
43
|
450 |
|
$values = []; |
|
44
|
|
|
foreach ($tokens['values'] as $value) { |
|
45
|
450 |
|
$values[] = $this->value($params, $q, $value); |
|
46
|
|
|
} |
|
47
|
432 |
|
|
|
48
|
|
|
$output = \implode(',', \array_map( |
|
49
|
|
|
fn(string|FragmentInterface|null $return) => $return instanceof FragmentInterface |
|
50
|
|
|
? $this->fragment($params, $q, $return) |
|
51
|
|
|
: 'INSERTED.' . $this->quoteIdentifier($return), |
|
|
|
|
|
|
52
|
|
|
$tokens['return'], |
|
53
|
|
|
)); |
|
54
|
|
|
|
|
55
|
|
|
if ($tokens['columns'] === []) { |
|
56
|
24 |
|
return \sprintf( |
|
57
|
24 |
|
'INSERT INTO %s OUTPUT %s DEFAULT VALUES', |
|
58
|
24 |
|
$this->name($params, $q, $tokens['table'], true), |
|
59
|
24 |
|
$output, |
|
60
|
|
|
); |
|
61
|
|
|
} |
|
62
|
|
|
|
|
63
|
24 |
|
return \sprintf( |
|
64
|
24 |
|
'INSERT INTO %s (%s) OUTPUT %s VALUES %s', |
|
65
|
|
|
$this->name($params, $q, $tokens['table'], true), |
|
66
|
24 |
|
$this->columns($params, $q, $tokens['columns']), |
|
67
|
24 |
|
$output, |
|
68
|
24 |
|
\implode(', ', $values), |
|
69
|
24 |
|
); |
|
70
|
|
|
} |
|
71
|
|
|
|
|
72
|
|
|
/** |
|
73
|
|
|
* @psalm-return non-empty-string |
|
74
|
|
|
*/ |
|
75
|
|
|
protected function upsertQuery(QueryParameters $params, Quoter $q, array $tokens): string |
|
76
|
|
|
{ |
|
77
|
|
|
if (\count($tokens['conflicts']) === 0) { |
|
78
|
|
|
throw new CompilerException('Upsert query must define conflicting index column names'); |
|
79
|
|
|
} |
|
80
|
|
|
|
|
81
|
450 |
|
if (\count($tokens['columns']) === 0) { |
|
82
|
|
|
throw new CompilerException('Upsert query must define at least one column'); |
|
83
|
|
|
} |
|
84
|
|
|
|
|
85
|
|
|
$values = []; |
|
86
|
|
|
|
|
87
|
|
|
foreach ($tokens['values'] as $value) { |
|
88
|
450 |
|
$values[] = $this->value($params, $q, $value); |
|
89
|
446 |
|
} |
|
90
|
|
|
|
|
91
|
|
|
$target = 'target'; |
|
92
|
|
|
$source = 'source'; |
|
93
|
30 |
|
|
|
94
|
6 |
|
$conflicts = \array_map( |
|
95
|
6 |
|
function (string $column) use ($params, $q, $target, $source) { |
|
96
|
|
|
$name = $this->name($params, $q, $column); |
|
97
|
6 |
|
$target = $this->name($params, $q, $target); |
|
98
|
6 |
|
$source = $this->name($params, $q, $source); |
|
99
|
6 |
|
return \sprintf('%s.%s = %s.%s', $target, $name, $source, $name); |
|
100
|
|
|
}, |
|
101
|
|
|
$tokens['conflicts'], |
|
102
|
6 |
|
); |
|
103
|
|
|
|
|
104
|
|
|
$updates = \array_map( |
|
105
|
24 |
|
function (string $column) use ($params, $q, $target, $source) { |
|
106
|
|
|
$name = $this->name($params, $q, $column); |
|
107
|
|
|
$target = $this->name($params, $q, $target); |
|
108
|
24 |
|
$source = $this->name($params, $q, $source); |
|
109
|
|
|
return \sprintf('%s.%s = %s.%s', $target, $name, $source, $name); |
|
110
|
24 |
|
}, |
|
111
|
20 |
|
$tokens['columns'], |
|
112
|
20 |
|
); |
|
113
|
20 |
|
|
|
114
|
|
|
$inserts = \array_map( |
|
115
|
4 |
|
function (string $column) use ($params, $q, $source) { |
|
116
|
4 |
|
$name = $this->name($params, $q, $column); |
|
117
|
|
|
$source = $this->name($params, $q, $source); |
|
118
|
|
|
return \sprintf('%s.%s', $source, $name); |
|
119
|
24 |
|
}, |
|
120
|
|
|
$tokens['columns'], |
|
121
|
|
|
); |
|
122
|
|
|
|
|
123
|
|
|
$query = \sprintf( |
|
124
|
|
|
'MERGE INTO %s WITH (holdlock) AS %s USING ( VALUES %s) AS %s (%s) ON %s WHEN MATCHED THEN UPDATE SET %s WHEN NOT MATCHED THEN INSERT (%s) VALUES (%s)', |
|
125
|
450 |
|
$this->name($params, $q, $tokens['table'], true), |
|
126
|
|
|
$this->name($params, $q, $target), |
|
127
|
|
|
\implode(', ', $values), |
|
128
|
450 |
|
$this->name($params, $q, 'source'), |
|
129
|
450 |
|
$this->columns($params, $q, $tokens['columns']), |
|
130
|
450 |
|
\implode(' AND ', $conflicts), |
|
131
|
|
|
\implode(', ', $updates), |
|
132
|
450 |
|
$this->columns($params, $q, $tokens['columns']), |
|
133
|
70 |
|
\implode(', ', $inserts), |
|
134
|
|
|
); |
|
135
|
|
|
|
|
136
|
450 |
|
if (empty($tokens['return'])) { |
|
137
|
450 |
|
return $query . ';'; |
|
138
|
450 |
|
} |
|
139
|
450 |
|
|
|
140
|
450 |
|
$output = \implode(', ', \array_map( |
|
141
|
450 |
|
function (string|FragmentInterface|null $return) use ($params, $q) { |
|
142
|
450 |
|
return $return instanceof FragmentInterface |
|
143
|
450 |
|
? $this->fragment($params, $q, $return) |
|
144
|
450 |
|
: 'INSERTED.' . $this->name($params, $q, $return); |
|
145
|
450 |
|
}, |
|
146
|
450 |
|
$tokens['return'], |
|
147
|
450 |
|
)); |
|
148
|
450 |
|
|
|
149
|
|
|
return \sprintf( |
|
150
|
|
|
'%s OUTPUT %s;', |
|
151
|
|
|
$query, |
|
152
|
|
|
$output, |
|
153
|
|
|
); |
|
154
|
|
|
} |
|
155
|
|
|
|
|
156
|
|
|
/** |
|
157
|
|
|
* {@inheritDoc} |
|
158
|
|
|
* |
|
159
|
|
|
* Attention, limiting and ordering UNIONS will fail in SQL SERVER < 2012. |
|
160
|
|
|
* For future upgrades: think about using top command. |
|
161
|
|
|
* |
|
162
|
|
|
* @link http://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server |
|
163
|
|
|
* @link http://stackoverflow.com/questions/971964/limit-10-20-in-sql-server |
|
164
|
|
|
*/ |
|
165
|
|
|
protected function selectQuery(QueryParameters $params, Quoter $q, array $tokens): string |
|
166
|
|
|
{ |
|
167
|
|
|
$limit = $tokens['limit']; |
|
168
|
|
|
$offset = $tokens['offset']; |
|
169
|
|
|
|
|
170
|
|
|
if (($limit === null && $offset === null) || $tokens['orderBy'] !== []) { |
|
171
|
|
|
//When no limits are specified we can use normal query syntax |
|
172
|
|
|
return \call_user_func_array([$this, 'baseSelect'], \func_get_args()); |
|
173
|
|
|
} |
|
174
|
|
|
|
|
175
|
|
|
/** |
|
176
|
|
|
* We are going to use fallback mechanism here in order to properly select limited data from |
|
177
|
|
|
* database. Avoid usage of LIMIT/OFFSET without proper ORDER BY statement. |
|
178
|
|
|
* |
|
179
|
|
|
* Please see set of alerts raised in SelectQuery builder. |
|
180
|
|
|
*/ |
|
181
|
|
|
$tokens['columns'][] = new Fragment( |
|
182
|
|
|
\sprintf( |
|
183
|
|
|
'ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS %s', |
|
184
|
|
|
$this->name($params, $q, self::ROW_NUMBER), |
|
185
|
|
|
), |
|
186
|
|
|
); |
|
187
|
|
|
|
|
188
|
|
|
$tokens['limit'] = null; |
|
189
|
|
|
$tokens['offset'] = null; |
|
190
|
|
|
|
|
191
|
|
|
return \sprintf( |
|
192
|
|
|
"SELECT * FROM (\n%s\n) AS [ORD_FALLBACK] %s", |
|
193
|
|
|
$this->baseSelect($params, $q, $tokens), |
|
194
|
|
|
$this->limit($params, $q, $limit, $offset, self::ROW_NUMBER), |
|
195
|
|
|
); |
|
196
|
|
|
} |
|
197
|
|
|
|
|
198
|
|
|
/** |
|
199
|
|
|
* |
|
200
|
|
|
* |
|
201
|
|
|
* @param string $rowNumber Row used in a fallback sorting mechanism, ONLY when no ORDER BY |
|
202
|
|
|
* specified. |
|
203
|
|
|
* |
|
204
|
|
|
* @link http://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server |
|
205
|
|
|
*/ |
|
206
|
|
|
protected function limit( |
|
207
|
|
|
QueryParameters $params, |
|
208
|
|
|
Quoter $q, |
|
209
|
|
|
?int $limit = null, |
|
210
|
|
|
?int $offset = null, |
|
211
|
|
|
?string $rowNumber = null, |
|
212
|
|
|
): string { |
|
213
|
|
|
if ($limit === null && $offset === null) { |
|
214
|
|
|
return ''; |
|
215
|
|
|
} |
|
216
|
|
|
|
|
217
|
|
|
//Modern SQLServer are easier to work with |
|
218
|
|
|
if ($rowNumber === null) { |
|
219
|
|
|
$statement = 'OFFSET ? ROWS '; |
|
220
|
|
|
$params->push(new Parameter((int) $offset)); |
|
221
|
|
|
|
|
222
|
|
|
if ($limit !== null) { |
|
223
|
|
|
$statement .= 'FETCH FIRST ? ROWS ONLY'; |
|
224
|
|
|
$params->push(new Parameter($limit)); |
|
225
|
|
|
} |
|
226
|
|
|
|
|
227
|
|
|
return \trim($statement); |
|
228
|
|
|
} |
|
229
|
|
|
|
|
230
|
|
|
$statement = "WHERE {$this->name($params, $q, $rowNumber)} "; |
|
231
|
|
|
|
|
232
|
|
|
//0 = row_number(1) |
|
233
|
|
|
++$offset; |
|
234
|
|
|
|
|
235
|
|
|
if ($limit !== null) { |
|
236
|
|
|
$statement .= 'BETWEEN ? AND ?'; |
|
237
|
|
|
$params->push(new Parameter((int) $offset)); |
|
238
|
|
|
$params->push(new Parameter($offset + $limit - 1)); |
|
239
|
|
|
} else { |
|
240
|
|
|
$statement .= '>= ?'; |
|
241
|
|
|
$params->push(new Parameter((int) $offset)); |
|
242
|
|
|
} |
|
243
|
|
|
|
|
244
|
|
|
return $statement; |
|
245
|
|
|
} |
|
246
|
|
|
|
|
247
|
|
|
protected function compileJsonOrderBy(string $path): FragmentInterface |
|
248
|
|
|
{ |
|
249
|
|
|
return new CompileJson($path); |
|
250
|
|
|
} |
|
251
|
|
|
|
|
252
|
|
|
private function baseSelect(QueryParameters $params, Quoter $q, array $tokens): string |
|
253
|
|
|
{ |
|
254
|
|
|
// This statement(s) parts should be processed first to define set of table and column aliases |
|
255
|
|
|
$tables = []; |
|
256
|
|
|
foreach ($tokens['from'] as $table) { |
|
257
|
|
|
$tables[] = $this->name($params, $q, $table, true); |
|
258
|
|
|
} |
|
259
|
|
|
foreach ($tokens['join'] as $join) { |
|
260
|
|
|
$this->nameWithAlias(new QueryParameters(), $q, $join['outer'], $join['alias'], true); |
|
261
|
|
|
} |
|
262
|
|
|
|
|
263
|
|
|
return \sprintf( |
|
264
|
|
|
"SELECT%s %s\nFROM %s%s%s%s%s%s%s%s%s%s%s", |
|
265
|
|
|
$this->optional(' ', $this->distinct($params, $q, $tokens['distinct'])), |
|
266
|
|
|
$this->columns($params, $q, $tokens['columns']), |
|
267
|
|
|
\implode(', ', $tables), |
|
268
|
|
|
$this->optional(' ', $tokens['forUpdate'] ? 'WITH (UPDLOCK,ROWLOCK)' : '', ' '), |
|
269
|
|
|
$this->optional(' ', $this->joins($params, $q, $tokens['join']), ' '), |
|
270
|
|
|
$this->optional("\nWHERE", $this->where($params, $q, $tokens['where'])), |
|
271
|
|
|
$this->optional("\nGROUP BY", $this->groupBy($params, $q, $tokens['groupBy']), ' '), |
|
272
|
|
|
$this->optional("\nHAVING", $this->where($params, $q, $tokens['having'])), |
|
273
|
|
|
$this->optional("\n", $this->unions($params, $q, $tokens['union'])), |
|
274
|
|
|
$this->optional("\n", $this->intersects($params, $q, $tokens['intersect'])), |
|
275
|
|
|
$this->optional("\n", $this->excepts($params, $q, $tokens['except'])), |
|
276
|
|
|
$this->optional("\nORDER BY", $this->orderBy($params, $q, $tokens['orderBy'])), |
|
277
|
|
|
$this->optional("\n", $this->limit($params, $q, $tokens['limit'], $tokens['offset'])), |
|
278
|
|
|
); |
|
279
|
|
|
} |
|
280
|
|
|
} |
|
281
|
|
|
|
cycle /
database
Loading history...