1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* Class DialectAbstract |
4
|
|
|
* |
5
|
|
|
* @filesource DialectAbstract.php |
6
|
|
|
* @created 11.01.2018 |
7
|
|
|
* @package chillerlan\Database\Dialects |
8
|
|
|
* @author Smiley <[email protected]> |
9
|
|
|
* @copyright 2018 Smiley |
10
|
|
|
* @license MIT |
11
|
|
|
*/ |
12
|
|
|
|
13
|
|
|
namespace chillerlan\Database\Dialects; |
14
|
|
|
|
15
|
|
|
use chillerlan\Database\Query\QueryException; |
16
|
|
|
|
17
|
|
|
/** |
18
|
|
|
* please don't look at it |
19
|
|
|
* |
20
|
|
|
* @link https://en.wikibooks.org/wiki/SQL_Dialects_Reference |
21
|
|
|
* @link https://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL |
22
|
|
|
*/ |
23
|
|
|
abstract class DialectAbstract implements Dialect{ |
24
|
|
|
|
25
|
|
|
/** |
26
|
|
|
* @var string[] |
27
|
|
|
*/ |
28
|
|
|
protected array $quotes = ['"', '"']; |
29
|
|
|
|
30
|
|
|
/** |
31
|
|
|
* @var string |
32
|
|
|
*/ |
33
|
|
|
protected string $charset = 'utf8'; |
34
|
|
|
|
35
|
|
|
/** @inheritdoc */ |
36
|
|
|
public function select(array $cols, array $from, string $where = null, $limit = null, $offset = null, bool $distinct = null, array $groupby = null, array $orderby = null):array{ |
37
|
|
|
$sql = ['SELECT']; |
38
|
|
|
|
39
|
|
|
if($distinct){ |
40
|
|
|
$sql[] = 'DISTINCT'; |
41
|
|
|
} |
42
|
|
|
|
43
|
|
|
!empty($cols) |
44
|
|
|
? $sql[] = implode(', ', $cols) |
45
|
|
|
: $sql[] = '*'; |
46
|
|
|
|
47
|
|
|
$sql[] = 'FROM'; |
48
|
|
|
$sql[] = implode(', ', $from); |
49
|
|
|
$sql[] = $where; |
50
|
|
|
|
51
|
|
|
if(!empty($groupby)){ |
52
|
|
|
$sql[] = 'GROUP BY'; |
53
|
|
|
$sql[] = implode(', ', $groupby); |
54
|
|
|
} |
55
|
|
|
|
56
|
|
|
if(!empty($orderby)){ |
57
|
|
|
$sql[] = 'ORDER BY'; |
58
|
|
|
$sql[] = implode(', ', $orderby); |
59
|
|
|
} |
60
|
|
|
|
61
|
|
|
if($limit !== null){ |
62
|
|
|
$sql[] = 'LIMIT'; |
63
|
|
|
$sql[] = $offset !== null ? '?, ?' : '?'; |
64
|
|
|
} |
65
|
|
|
|
66
|
|
|
return $sql; |
67
|
|
|
} |
68
|
|
|
|
69
|
|
|
/** @inheritdoc */ |
70
|
|
|
public function insert(string $table, array $fields, string $onConflict = null, string $conflictTarget = null):array{ |
71
|
|
|
$sql = ['INSERT INTO']; |
72
|
|
|
$sql[] = $this->quote($table); |
73
|
|
|
$sql[] = '('.$this->quotes[0].implode($this->quotes[1].', '.$this->quotes[0], $fields).$this->quotes[1].')'; |
74
|
|
|
$sql[] = 'VALUES'; |
75
|
|
|
$sql[] = '('.implode(',', array_fill(0, count($fields), '?')).')'; |
76
|
|
|
|
77
|
|
|
return $sql; |
78
|
|
|
} |
79
|
|
|
|
80
|
|
|
/** @inheritdoc */ |
81
|
|
|
public function update(string $table, array $set, string $where):array{ |
82
|
|
|
$sql = ['UPDATE']; |
83
|
|
|
$sql[] = $this->quote($table); |
84
|
|
|
$sql[] = 'SET'; |
85
|
|
|
$sql[] = implode(', ', $set); |
86
|
|
|
$sql[] = $where; |
87
|
|
|
|
88
|
|
|
return $sql; |
89
|
|
|
} |
90
|
|
|
|
91
|
|
|
/** @inheritdoc */ |
92
|
|
|
public function delete(string $table, string $where):array{ |
93
|
|
|
$sql = ['DELETE FROM']; |
94
|
|
|
$sql[] = $this->quote($table); |
95
|
|
|
$sql[] = $where; |
96
|
|
|
|
97
|
|
|
return $sql; |
98
|
|
|
} |
99
|
|
|
|
100
|
|
|
/** @inheritdoc */ |
101
|
|
|
public function createDatabase(string $dbname, bool $ifNotExists = null, string $collate = null):array{ |
102
|
|
|
throw new QueryException('not supported'); |
103
|
|
|
} |
104
|
|
|
|
105
|
|
|
/** @inheritdoc */ |
106
|
|
|
public function quote(string $str):string{ // @todo: fixme |
107
|
|
|
return $this->quotes[0].implode($this->quotes[1].'.'.$this->quotes[0], explode('.', $str)).$this->quotes[1]; |
108
|
|
|
} |
109
|
|
|
|
110
|
|
|
/** @inheritdoc */ |
111
|
|
|
public function dropDatabase(string $dbname, bool $ifExists){ |
112
|
|
|
$sql = ['DROP DATABASE']; |
113
|
|
|
|
114
|
|
|
if($ifExists){ |
115
|
|
|
$sql[] = 'IF EXISTS'; |
116
|
|
|
} |
117
|
|
|
|
118
|
|
|
$sql[] = $this->quote($dbname); |
119
|
|
|
|
120
|
|
|
return $sql; |
121
|
|
|
} |
122
|
|
|
|
123
|
|
|
/** @inheritdoc */ |
124
|
|
|
public function dropTable(string $table, bool $ifExists):array{ |
125
|
|
|
$sql = ['DROP TABLE']; |
126
|
|
|
|
127
|
|
|
if($ifExists){ |
128
|
|
|
$sql[] = 'IF EXISTS'; |
129
|
|
|
} |
130
|
|
|
|
131
|
|
|
$sql[] = $this->quote($table); |
132
|
|
|
|
133
|
|
|
return $sql; |
134
|
|
|
} |
135
|
|
|
|
136
|
|
|
public function selectCount(array $from, string $where = null, bool $distinct = null, array $groupby = null){ |
137
|
|
|
$sql = ['SELECT']; |
138
|
|
|
|
139
|
|
|
if($distinct){ |
140
|
|
|
$sql[] = 'DISTINCT'; |
141
|
|
|
} |
142
|
|
|
|
143
|
|
|
$sql[] = 'COUNT(*) AS'; |
144
|
|
|
$sql[] = $this->quote('count'); |
145
|
|
|
$sql[] = 'FROM '.implode(', ', $from); |
146
|
|
|
$sql[] = $where; |
147
|
|
|
|
148
|
|
|
if(!empty($groupby)){ |
149
|
|
|
$sql[] = 'GROUP BY '; |
150
|
|
|
$sql[] = implode(', ', $groupby); |
151
|
|
|
} |
152
|
|
|
|
153
|
|
|
return $sql; |
154
|
|
|
} |
155
|
|
|
|
156
|
|
|
public function orderby(array $expressions):array { |
157
|
|
|
$orderby = []; |
158
|
|
|
|
159
|
|
|
foreach($expressions as $alias => $expression){ |
160
|
|
|
|
161
|
|
|
if(is_string($alias)){ |
162
|
|
|
|
163
|
|
|
if(is_array($expression)){ |
164
|
|
|
$dir = strtoupper($expression[0]); |
165
|
|
|
|
166
|
|
|
if(in_array($dir, ['ASC', 'DESC'], true)){ |
167
|
|
|
$orderby[] = isset($expression[1]) ? strtoupper($expression[1]).'('.$this->quote($alias).') '.$dir : $dir; |
168
|
|
|
} |
169
|
|
|
|
170
|
|
|
} |
171
|
|
|
else{ |
172
|
|
|
$dir = strtoupper($expression); |
173
|
|
|
|
174
|
|
|
if(in_array($dir, ['ASC', 'DESC'], true)){ |
175
|
|
|
$orderby[] = $this->quote($alias).' '.$dir; |
176
|
|
|
} |
177
|
|
|
|
178
|
|
|
} |
179
|
|
|
|
180
|
|
|
} |
181
|
|
|
else{ |
182
|
|
|
$orderby[] = $this->quote($expression); |
183
|
|
|
} |
184
|
|
|
|
185
|
|
|
} |
186
|
|
|
|
187
|
|
|
return $orderby; |
188
|
|
|
} |
189
|
|
|
|
190
|
|
|
/** @inheritdoc */ |
191
|
|
|
public function cols(array $expressions):array { |
192
|
|
|
|
193
|
|
|
// @todo: fixme, cleanup |
194
|
|
|
// errors on [alias => col, alias, alias => col...] |
195
|
|
|
$_col = function ($expr1, $expr2 = null, $func = null):string { |
196
|
|
|
switch(true){ |
197
|
|
|
case $expr2 && $func: |
198
|
|
|
$col = sprintf('%s(%s) AS %s', strtoupper($func), $expr1, $this->quote($expr2)); |
199
|
|
|
break; |
200
|
|
|
case $expr2 && !$func: |
201
|
|
|
$col = sprintf('%s AS %s', $this->quote($expr1), $this->quote($expr2)); |
202
|
|
|
break; |
203
|
|
|
case !$expr2 && $func: |
204
|
|
|
$col = sprintf('%s(%s)', strtoupper($func), $expr1); |
205
|
|
|
break; |
206
|
|
|
case !$expr2 && !$func: |
207
|
|
|
default: |
208
|
|
|
$col = $this->quote($expr1); |
209
|
|
|
} |
210
|
|
|
|
211
|
|
|
return $col; |
212
|
|
|
}; |
213
|
|
|
|
214
|
|
|
$r = []; |
215
|
|
|
|
216
|
|
|
foreach($expressions as $k => $ref){ |
217
|
|
|
if(is_string($k)){ |
218
|
|
|
$r[$ref[0] ?? $k] = is_array($ref) |
219
|
|
|
? $_col($ref[0], $k, $ref[1] ?? null) |
220
|
|
|
: $_col($ref, $k); |
221
|
|
|
} |
222
|
|
|
else{ |
223
|
|
|
$r[$ref] = is_array($ref) |
224
|
|
|
? $_col($ref[0], null, $ref[1] ?? null) |
225
|
|
|
: $_col($ref); |
226
|
|
|
} |
227
|
|
|
|
228
|
|
|
} |
229
|
|
|
|
230
|
|
|
return $r; |
231
|
|
|
} |
232
|
|
|
|
233
|
|
|
|
234
|
|
|
/** @inheritdoc */ |
235
|
|
|
public function from(array $expressions):array { |
236
|
|
|
|
237
|
|
|
$_from = function (string $table, string $ref = null):string { |
238
|
|
|
// @todo: quotes |
239
|
|
|
$from = $this->quote($table); |
240
|
|
|
|
241
|
|
|
if($ref){ |
242
|
|
|
$from = sprintf('%s AS %s', $this->quote($ref), $this->quote($table));// @todo: index hint |
243
|
|
|
} |
244
|
|
|
|
245
|
|
|
return $from; |
246
|
|
|
}; |
247
|
|
|
|
248
|
|
|
$r = []; |
249
|
|
|
|
250
|
|
|
foreach($expressions as $k => $ref){ |
251
|
|
|
|
252
|
|
|
if(is_string($k)){ |
253
|
|
|
$r[$ref ?? $k] = $_from($k, $ref); |
254
|
|
|
} |
255
|
|
|
else{ |
256
|
|
|
$x = explode(' ', $ref); |
257
|
|
|
|
258
|
|
|
if(count($x) === 2){ |
259
|
|
|
$r[$ref ?? $k] = $_from($x[0], $x[1]); |
260
|
|
|
} |
261
|
|
|
else{ |
262
|
|
|
$r[$ref] = $_from($ref); |
263
|
|
|
} |
264
|
|
|
} |
265
|
|
|
|
266
|
|
|
} |
267
|
|
|
|
268
|
|
|
return $r; |
269
|
|
|
} |
270
|
|
|
|
271
|
|
|
/** @inheritdoc */ |
272
|
|
|
public function enum(string $name, array $values, $defaultValue = null, bool $isNull = null):string { |
273
|
|
|
|
274
|
|
|
$field = $this->quote($name); |
275
|
|
|
$field .= 'ENUM (\''.implode('\', \'', $values).'\')'; |
276
|
|
|
|
277
|
|
|
if(is_bool($isNull)){ |
278
|
|
|
$field .= $isNull ? 'NULL' : 'NOT NULL'; |
279
|
|
|
} |
280
|
|
|
|
281
|
|
|
if(in_array($defaultValue, $values, true)){ |
282
|
|
|
$field .= 'DEFAULT '.(is_int($defaultValue) || is_float($defaultValue) ? $defaultValue : '\''.$defaultValue.'\''); |
283
|
|
|
} |
284
|
|
|
elseif($isNull && strtolower($defaultValue) === 'null'){ |
285
|
|
|
$field .= 'DEFAULT NULL'; |
286
|
|
|
} |
287
|
|
|
|
288
|
|
|
return $field; |
289
|
|
|
} |
290
|
|
|
|
291
|
|
|
/** @inheritdoc */ |
292
|
|
|
public function truncate(string $table):array{ |
293
|
|
|
$sql = ['TRUNCATE TABLE']; |
294
|
|
|
$sql[] = $this->quote($table); |
295
|
|
|
|
296
|
|
|
return $sql; |
297
|
|
|
} |
298
|
|
|
|
299
|
|
|
/** @inheritdoc */ |
300
|
|
|
public function showDatabases():array{ |
301
|
|
|
throw new QueryException('not supported'); |
302
|
|
|
} |
303
|
|
|
|
304
|
|
|
/** @inheritdoc */ |
305
|
|
|
public function showTables(string $database = null, string $pattern = null, string $where = null):array{ |
306
|
|
|
throw new QueryException('not supported'); |
307
|
|
|
} |
308
|
|
|
|
309
|
|
|
/** @inheritdoc */ |
310
|
|
|
public function showCreateTable(string $table):array{ |
311
|
|
|
throw new QueryException('not supported'); |
312
|
|
|
} |
313
|
|
|
|
314
|
|
|
} |
315
|
|
|
|