|
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\Injection\Fragment; |
|
17
|
|
|
use Cycle\Database\Injection\FragmentInterface; |
|
18
|
|
|
use Cycle\Database\Injection\Parameter; |
|
19
|
|
|
use Cycle\Database\Query\QueryParameters; |
|
20
|
|
|
|
|
21
|
|
|
/** |
|
22
|
|
|
* Microsoft SQL server specific syntax compiler. |
|
23
|
|
|
*/ |
|
24
|
|
|
class SQLServerCompiler extends Compiler |
|
25
|
|
|
{ |
|
26
|
|
|
/** |
|
27
|
|
|
* Column to be used as ROW_NUMBER in fallback selection mechanism, attention! Amount of columns |
|
28
|
|
|
* in result set will be increaced by 1! |
|
29
|
|
|
*/ |
|
30
|
|
|
public const ROW_NUMBER = '_ROW_NUMBER_'; |
|
31
|
|
|
|
|
32
|
|
|
/** |
|
33
|
|
|
* @psalm-return non-empty-string |
|
34
|
|
|
*/ |
|
35
|
|
|
protected function insertQuery(QueryParameters $params, Quoter $q, array $tokens): string |
|
36
|
|
|
{ |
|
37
|
|
|
if (empty($tokens['return'])) { |
|
38
|
|
|
return parent::insertQuery($params, $q, $tokens); |
|
39
|
|
|
} |
|
40
|
450 |
|
|
|
41
|
|
|
$values = []; |
|
42
|
450 |
|
foreach ($tokens['values'] as $value) { |
|
43
|
450 |
|
$values[] = $this->value($params, $q, $value); |
|
44
|
|
|
} |
|
45
|
450 |
|
|
|
46
|
|
|
$output = \implode(',', \array_map( |
|
47
|
432 |
|
fn (string|FragmentInterface|null $return) => $return instanceof FragmentInterface |
|
48
|
|
|
? (string) $return |
|
49
|
|
|
: 'INSERTED.' . $this->quoteIdentifier($return), |
|
|
|
|
|
|
50
|
|
|
$tokens['return'] |
|
51
|
|
|
)); |
|
52
|
|
|
|
|
53
|
|
|
if ($tokens['columns'] === []) { |
|
54
|
|
|
return \sprintf( |
|
55
|
|
|
'INSERT INTO %s OUTPUT %s DEFAULT VALUES', |
|
56
|
24 |
|
$this->name($params, $q, $tokens['table'], true), |
|
57
|
24 |
|
$output |
|
58
|
24 |
|
); |
|
59
|
24 |
|
} |
|
60
|
|
|
|
|
61
|
|
|
return \sprintf( |
|
62
|
|
|
'INSERT INTO %s (%s) OUTPUT %s VALUES %s', |
|
63
|
24 |
|
$this->name($params, $q, $tokens['table'], true), |
|
64
|
24 |
|
$this->columns($params, $q, $tokens['columns']), |
|
65
|
|
|
$output, |
|
66
|
24 |
|
\implode(', ', $values) |
|
67
|
24 |
|
); |
|
68
|
24 |
|
} |
|
69
|
24 |
|
|
|
70
|
|
|
/** |
|
71
|
|
|
* {@inheritdoc} |
|
72
|
|
|
* |
|
73
|
|
|
* Attention, limiting and ordering UNIONS will fail in SQL SERVER < 2012. |
|
74
|
|
|
* For future upgrades: think about using top command. |
|
75
|
|
|
* |
|
76
|
|
|
* @link http://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server |
|
77
|
|
|
* @link http://stackoverflow.com/questions/971964/limit-10-20-in-sql-server |
|
78
|
|
|
*/ |
|
79
|
|
|
protected function selectQuery(QueryParameters $params, Quoter $q, array $tokens): string |
|
80
|
|
|
{ |
|
81
|
450 |
|
$limit = $tokens['limit']; |
|
82
|
|
|
$offset = $tokens['offset']; |
|
83
|
|
|
|
|
84
|
|
|
if (($limit === null && $offset === null) || $tokens['orderBy'] !== []) { |
|
85
|
|
|
//When no limits are specified we can use normal query syntax |
|
86
|
|
|
return call_user_func_array([$this, 'baseSelect'], func_get_args()); |
|
87
|
|
|
} |
|
88
|
450 |
|
|
|
89
|
446 |
|
/** |
|
90
|
|
|
* We are going to use fallback mechanism here in order to properly select limited data from |
|
91
|
|
|
* database. Avoid usage of LIMIT/OFFSET without proper ORDER BY statement. |
|
92
|
|
|
* |
|
93
|
30 |
|
* Please see set of alerts raised in SelectQuery builder. |
|
94
|
6 |
|
*/ |
|
95
|
6 |
|
$tokens['columns'][] = new Fragment( |
|
96
|
|
|
sprintf( |
|
97
|
6 |
|
'ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS %s', |
|
98
|
6 |
|
$this->name($params, $q, self::ROW_NUMBER) |
|
99
|
6 |
|
) |
|
100
|
|
|
); |
|
101
|
|
|
|
|
102
|
6 |
|
$tokens['limit'] = null; |
|
103
|
|
|
$tokens['offset'] = null; |
|
104
|
|
|
|
|
105
|
24 |
|
return sprintf( |
|
106
|
|
|
"SELECT * FROM (\n%s\n) AS [ORD_FALLBACK] %s", |
|
107
|
|
|
$this->baseSelect($params, $q, $tokens), |
|
108
|
24 |
|
$this->limit($params, $q, $limit, $offset, self::ROW_NUMBER) |
|
109
|
|
|
); |
|
110
|
24 |
|
} |
|
111
|
20 |
|
|
|
112
|
20 |
|
/** |
|
113
|
20 |
|
* {@inheritdoc} |
|
114
|
|
|
* |
|
115
|
4 |
|
* @param string $rowNumber Row used in a fallback sorting mechanism, ONLY when no ORDER BY |
|
116
|
4 |
|
* specified. |
|
117
|
|
|
* |
|
118
|
|
|
* @link http://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server |
|
119
|
24 |
|
*/ |
|
120
|
|
|
protected function limit( |
|
121
|
|
|
QueryParameters $params, |
|
122
|
|
|
Quoter $q, |
|
123
|
|
|
int $limit = null, |
|
124
|
|
|
int $offset = null, |
|
125
|
450 |
|
string $rowNumber = null |
|
126
|
|
|
): string { |
|
127
|
|
|
if ($limit === null && $offset === null) { |
|
128
|
450 |
|
return ''; |
|
129
|
450 |
|
} |
|
130
|
450 |
|
|
|
131
|
|
|
//Modern SQLServer are easier to work with |
|
132
|
450 |
|
if ($rowNumber === null) { |
|
133
|
70 |
|
$statement = 'OFFSET ? ROWS '; |
|
134
|
|
|
$params->push(new Parameter((int)$offset)); |
|
135
|
|
|
|
|
136
|
450 |
|
if ($limit !== null) { |
|
137
|
450 |
|
$statement .= 'FETCH FIRST ? ROWS ONLY'; |
|
138
|
450 |
|
$params->push(new Parameter($limit)); |
|
139
|
450 |
|
} |
|
140
|
450 |
|
|
|
141
|
450 |
|
return trim($statement); |
|
142
|
450 |
|
} |
|
143
|
450 |
|
|
|
144
|
450 |
|
$statement = "WHERE {$this->name($params, $q, $rowNumber)} "; |
|
145
|
450 |
|
|
|
146
|
450 |
|
//0 = row_number(1) |
|
147
|
450 |
|
++$offset; |
|
148
|
450 |
|
|
|
149
|
|
|
if ($limit !== null) { |
|
150
|
|
|
$statement .= 'BETWEEN ? AND ?'; |
|
151
|
|
|
$params->push(new Parameter((int)$offset)); |
|
152
|
|
|
$params->push(new Parameter($offset + $limit - 1)); |
|
153
|
|
|
} else { |
|
154
|
|
|
$statement .= '>= ?'; |
|
155
|
|
|
$params->push(new Parameter((int)$offset)); |
|
156
|
|
|
} |
|
157
|
|
|
|
|
158
|
|
|
return $statement; |
|
159
|
|
|
} |
|
160
|
|
|
|
|
161
|
|
|
/** |
|
162
|
|
|
* @inheritDoc |
|
163
|
|
|
*/ |
|
164
|
|
|
private function baseSelect(QueryParameters $params, Quoter $q, array $tokens): string |
|
165
|
|
|
{ |
|
166
|
|
|
// This statement(s) parts should be processed first to define set of table and column aliases |
|
167
|
|
|
$tables = []; |
|
168
|
|
|
foreach ($tokens['from'] as $table) { |
|
169
|
|
|
$tables[] = $this->name($params, $q, $table, true); |
|
170
|
|
|
} |
|
171
|
|
|
foreach ($tokens['join'] as $join) { |
|
172
|
|
|
$this->nameWithAlias(new QueryParameters(), $q, $join['outer'], $join['alias'], true); |
|
173
|
|
|
} |
|
174
|
|
|
|
|
175
|
|
|
return sprintf( |
|
176
|
|
|
"SELECT%s %s\nFROM %s%s%s%s%s%s%s%s%s", |
|
177
|
|
|
$this->optional(' ', $this->distinct($params, $q, $tokens['distinct'])), |
|
178
|
|
|
$this->columns($params, $q, $tokens['columns']), |
|
179
|
|
|
implode(', ', $tables), |
|
180
|
|
|
$this->optional(' ', $tokens['forUpdate'] ? 'WITH (UPDLOCK,ROWLOCK)' : '', ' '), |
|
181
|
|
|
$this->optional(' ', $this->joins($params, $q, $tokens['join']), ' '), |
|
182
|
|
|
$this->optional("\nWHERE", $this->where($params, $q, $tokens['where'])), |
|
183
|
|
|
$this->optional("\nGROUP BY", $this->groupBy($params, $q, $tokens['groupBy']), ' '), |
|
184
|
|
|
$this->optional("\nHAVING", $this->where($params, $q, $tokens['having'])), |
|
185
|
|
|
$this->optional("\n", $this->unions($params, $q, $tokens['union'])), |
|
186
|
|
|
$this->optional("\nORDER BY", $this->orderBy($params, $q, $tokens['orderBy'])), |
|
187
|
|
|
$this->optional("\n", $this->limit($params, $q, $tokens['limit'], $tokens['offset'])) |
|
188
|
|
|
); |
|
189
|
|
|
} |
|
190
|
|
|
} |
|
191
|
|
|
|
cycle /
database
Loading history...