1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
//pdump(DBStaticUser::getMaxId()); |
|
|
|
|
4
|
|
|
//pdump(DBStaticUser::getRecordById(67)); |
5
|
|
|
//pdump(DBStaticUser::filterIdListStringRepack('2,3,5,67')); |
6
|
|
|
|
7
|
|
|
|
8
|
|
|
/** |
9
|
|
|
* Class DbSqlStatement |
10
|
|
|
* |
11
|
|
|
* @method static DbSqlStatement fields(mixed $value, int $mergeStrategy = HelperArray::ARRAY_REPLACE) |
12
|
|
|
* @method static DbSqlStatement where(array $value, int $mergeStrategy = HelperArray::ARRAY_REPLACE) |
13
|
|
|
* @method static DbSqlStatement groupBy(array $value, int $mergeStrategy = HelperArray::ARRAY_REPLACE) |
14
|
|
|
* @method static DbSqlStatement orderBy(array $value, int $mergeStrategy = HelperArray::ARRAY_REPLACE) |
15
|
|
|
* @method static DbSqlStatement having(array $value, int $mergeStrategy = HelperArray::ARRAY_REPLACE) |
16
|
|
|
* |
17
|
|
|
*/ |
18
|
|
|
class DbSqlStatement extends DbSqlAware { |
19
|
|
|
|
20
|
|
|
const SELECT = 'SELECT'; |
21
|
|
|
|
22
|
|
|
protected static $allowedOperations = array( |
23
|
|
|
self::SELECT, |
24
|
|
|
); |
25
|
|
|
|
26
|
|
|
public $operation = ''; |
27
|
|
|
|
28
|
|
|
public $table = ''; |
29
|
|
|
public $alias = ''; |
30
|
|
|
|
31
|
|
|
public $idField = ''; |
32
|
|
|
|
33
|
|
|
/** |
34
|
|
|
* @var array |
35
|
|
|
*/ |
36
|
|
|
public $fields = array(); |
37
|
|
|
|
38
|
|
|
public $where = array(); |
39
|
|
|
public $groupBy = array(); |
40
|
|
|
public $orderBy = array(); |
41
|
|
|
public $having = array(); |
42
|
|
|
|
43
|
|
|
public $limit = 0; |
44
|
|
|
public $offset = 0; |
45
|
|
|
|
46
|
|
|
public $fetchOne = false; |
47
|
|
|
public $forUpdate = false; |
48
|
|
|
public $skipLock = false; |
49
|
|
|
|
50
|
|
|
protected $_compiledQuery = array(); |
51
|
|
|
|
52
|
|
|
/** |
53
|
|
|
* @param string $fieldName |
54
|
|
|
* |
55
|
|
|
* @return $this |
56
|
|
|
*/ |
57
|
|
|
public function setIdField($fieldName) { |
58
|
|
|
$this->idField = $fieldName; |
59
|
|
|
|
60
|
|
|
return $this; |
61
|
|
|
} |
62
|
|
|
|
63
|
|
|
/** |
64
|
|
|
* @return self |
65
|
|
|
*/ |
66
|
|
|
public function select() { |
67
|
|
|
$this->operation = DbSqlStatement::SELECT; |
68
|
|
|
if (empty($this->fields)) { |
69
|
|
|
$this->fields = array('*'); |
70
|
1 |
|
} |
71
|
1 |
|
|
72
|
1 |
|
return $this; |
73
|
1 |
|
} |
74
|
1 |
|
|
75
|
|
|
/** |
76
|
1 |
|
* @param string $alias |
77
|
|
|
* |
78
|
|
|
* @return $this |
79
|
|
|
*/ |
80
|
|
|
public function fromAlias($alias) { |
81
|
|
|
$this->alias = $alias; |
82
|
|
|
|
83
|
|
|
return $this; |
84
|
3 |
|
} |
85
|
3 |
|
|
86
|
3 |
|
/** |
87
|
|
|
* @param string $tableName |
88
|
|
|
* @param string $alias |
89
|
|
|
* |
90
|
|
|
* @return $this |
91
|
|
|
*/ |
92
|
|
|
public function from($tableName, $alias = '') { |
93
|
|
|
$this->table = $tableName; |
94
|
|
|
$this->fromAlias($alias); |
95
|
|
|
|
96
|
1 |
|
return $this; |
97
|
1 |
|
} |
98
|
1 |
|
|
99
|
1 |
|
public function __call($name, $arguments) { |
100
|
1 |
|
// TODO: Implement __call() method. |
101
|
1 |
|
if (in_array($name, array('fields', 'where', 'groupBy', 'orderBy', 'having'))) { |
102
|
1 |
|
// array_unshift($arguments, ''); |
|
|
|
|
103
|
|
|
// $arguments[0] = &$this->$name; |
104
|
1 |
|
// call_user_func_array('HelperArray::merge', $arguments); |
105
|
1 |
|
HelperArray::merge($this->$name, $arguments[0], !empty($arguments[1]) ? $arguments[1] : HelperArray::ARRAY_REPLACE); |
106
|
1 |
|
} |
107
|
1 |
|
// TODO - make all setters protected ?? |
|
|
|
|
108
|
1 |
|
// elseif(method_exists($this, $name)) { |
109
|
|
|
// call_user_func_array(array($this, $name), $arguments); |
110
|
1 |
|
// } |
111
|
1 |
|
|
112
|
|
|
return $this; |
113
|
1 |
|
} |
114
|
1 |
|
|
115
|
1 |
|
/** |
116
|
|
|
* @param int $limit |
117
|
1 |
|
* |
118
|
|
|
* @return $this |
119
|
|
|
*/ |
120
|
|
|
public function limit($limit) { |
121
|
|
|
$this->limit = is_numeric($limit) ? $limit : 0; |
|
|
|
|
122
|
|
|
|
123
|
|
|
return $this; |
124
|
|
|
} |
125
|
2 |
|
|
126
|
2 |
|
/** |
127
|
|
|
* @param int $offset |
128
|
2 |
|
* |
129
|
|
|
* @return $this |
130
|
|
|
*/ |
131
|
|
|
public function offset($offset) { |
132
|
|
|
$this->offset = is_numeric($offset) ? $offset : 0; |
|
|
|
|
133
|
|
|
|
134
|
|
|
return $this; |
135
|
|
|
} |
136
|
1 |
|
|
137
|
1 |
|
|
138
|
|
|
/** |
139
|
1 |
|
* Make statement fetch only one record |
140
|
|
|
* |
141
|
|
|
* @return $this |
142
|
|
|
*/ |
143
|
|
|
public function fetchOne($fetchOne = true) { |
144
|
|
|
$this->fetchOne = $fetchOne; |
145
|
|
|
|
146
|
|
|
return $this; |
147
|
2 |
|
} |
148
|
2 |
|
|
149
|
|
|
/** |
150
|
2 |
|
* @return $this |
151
|
|
|
*/ |
152
|
|
|
public function forUpdate($forUpdate = true) { |
153
|
|
|
$this->forUpdate = $forUpdate; |
154
|
|
|
|
155
|
|
|
return $this; |
156
|
|
|
} |
157
|
|
|
|
158
|
2 |
|
/** |
159
|
2 |
|
* @return $this |
160
|
|
|
*/ |
161
|
2 |
|
public function skipLock($skipLock = true) { |
162
|
|
|
$this->skipLock = $skipLock; |
163
|
|
|
|
164
|
|
|
return $this; |
165
|
|
|
} |
166
|
|
|
|
167
|
|
|
/** |
168
|
|
|
* @param string $className |
169
|
|
|
* |
170
|
|
|
* @return $this |
171
|
2 |
|
*/ |
172
|
2 |
|
public function getParamsFromStaticClass($className) { |
173
|
2 |
|
if (is_string($className) && $className && class_exists($className)) { |
174
|
|
|
$this->from($className::$_table); |
175
|
2 |
|
$this->setIdField($className::$_idField); |
176
|
|
|
} |
177
|
|
|
|
178
|
|
|
return $this; |
179
|
|
|
} |
180
|
|
|
|
181
|
|
|
/** |
182
|
|
|
* @param db_mysql|null $db |
183
|
2 |
|
* @param string $className |
184
|
2 |
|
* |
185
|
2 |
|
* @return static |
186
|
2 |
|
*/ |
187
|
2 |
|
public static function build($db = null, $className = '') { |
188
|
|
|
/** |
189
|
2 |
|
* @var static $result |
190
|
|
|
*/ |
191
|
|
|
$result = parent::build($db); |
192
|
|
|
if (!empty($className) && is_string($className)) { |
193
|
|
|
$result->getParamsFromStaticClass($className); |
194
|
|
|
} |
195
|
|
|
|
196
|
3 |
|
return $result; |
197
|
3 |
|
} |
198
|
3 |
|
|
199
|
3 |
|
/** |
200
|
3 |
|
* Resets statement |
201
|
|
|
* |
202
|
3 |
|
* @param bool $full |
203
|
|
|
* |
204
|
|
|
* @return static |
205
|
3 |
|
*/ |
206
|
|
|
protected function _reset($full = true) { |
207
|
3 |
|
if ($full) { |
208
|
3 |
|
$this->operation = ''; |
209
|
3 |
|
$this->table = ''; |
210
|
3 |
|
$this->alias = ''; |
211
|
3 |
|
$this->idField = ''; |
212
|
|
|
} |
213
|
3 |
|
|
214
|
|
|
$this->fields = array(); |
215
|
|
|
$this->where = array(); |
216
|
|
|
$this->groupBy = array(); |
217
|
|
|
$this->orderBy = array(); |
218
|
|
|
$this->having = array(); |
219
|
|
|
|
220
|
|
|
$this->limit = 0; |
221
|
2 |
|
$this->offset = 0; |
222
|
2 |
|
|
223
|
|
|
$this->fetchOne = false; |
224
|
2 |
|
$this->forUpdate = false; |
225
|
|
|
$this->skipLock = false; |
226
|
|
|
|
227
|
|
|
return $this; |
228
|
|
|
} |
229
|
|
|
|
230
|
1 |
|
|
231
|
1 |
|
/** |
232
|
|
|
* @param array $array |
233
|
1 |
|
* |
234
|
|
|
* @return array |
235
|
|
|
*/ |
236
|
|
|
protected function arrayEscape(&$array) { |
237
|
|
|
$result = array(); |
238
|
|
|
foreach ($array as $key => &$value) { |
239
|
1 |
|
$result[$key] = $this->stringEscape($value); |
240
|
1 |
|
} |
241
|
|
|
|
242
|
1 |
|
return $result; |
243
|
|
|
} |
244
|
|
|
|
245
|
|
|
protected function compileFrom() { |
246
|
|
|
$this->_compiledQuery[] = 'FROM `{{' . $this->stringEscape($this->table) . '}}`'; |
247
|
|
|
if (!empty($this->alias)) { |
248
|
|
|
$this->_compiledQuery[] = 'AS `' . $this->stringEscape($this->alias) . '`'; |
249
|
|
|
} |
250
|
|
|
} |
251
|
2 |
|
|
252
|
2 |
|
protected function compileJoin() { |
253
|
1 |
|
} |
254
|
|
|
|
255
|
|
|
protected function compileWhere() { |
256
|
1 |
|
// TODO - fields should be escaped !! |
257
|
1 |
|
!empty($this->where) ? $this->_compiledQuery[] = 'WHERE ' . implode(' AND ', $this->where) : false; |
258
|
|
|
} |
259
|
|
|
|
260
|
|
|
protected function compileGroupBy() { |
261
|
|
|
// TODO - fields should be escaped !! |
262
|
|
|
!empty($this->groupBy) ? $this->_compiledQuery[] = 'GROUP BY ' . implode(',', $this->arrayEscape($this->groupBy)) : false; |
263
|
|
|
} |
264
|
|
|
|
265
|
|
|
protected function compileOrderBy() { |
266
|
|
|
// TODO - fields should be escaped !! |
267
|
|
|
!empty($this->orderBy) ? $this->_compiledQuery[] = 'ORDER BY ' . implode(',', $this->arrayEscape($this->orderBy)) : false; |
268
|
|
|
} |
269
|
|
|
|
270
|
|
|
protected function compileHaving() { |
271
|
|
|
// TODO - fields should be escaped !! |
272
|
|
|
!empty($this->having) ? $this->_compiledQuery[] = 'HAVING ' . implode(' AND ', $this->having) : false; |
273
|
|
|
} |
274
|
|
|
|
275
|
|
|
protected function compileLimit() { |
276
|
|
|
// TODO - fields should be escaped !! |
277
|
|
|
if ($limit = $this->fetchOne ? 1 : $this->limit) { |
278
|
|
|
$this->_compiledQuery[] = 'LIMIT ' . $limit . (!empty($this->offset) ? ' OFFSET ' . $this->offset : ''); |
279
|
|
|
} |
280
|
|
|
} |
281
|
|
|
|
282
|
|
|
protected function compileForUpdate() { |
283
|
|
|
$this->_compiledQuery[] = |
284
|
|
|
// forUpdate flag forces select with row locking - didn't look at skipLock flag |
285
|
|
|
$this->forUpdate |
286
|
|
|
|| |
287
|
|
|
// Also row locked when transaction is up and skipLock flag is not set |
288
|
|
|
(classSupernova::db_transaction_check(false) && !$this->skipLock) ? 'FOR UPDATE' : ''; |
289
|
|
|
} |
290
|
|
|
|
291
|
|
|
/** |
292
|
|
|
* @return string |
293
|
|
|
* @throws ExceptionDbOperationEmpty |
294
|
|
|
* @throws ExceptionDbOperationRestricted |
295
|
|
|
*/ |
296
|
|
|
public function __toString() { |
297
|
|
|
if (empty($this->operation)) { |
298
|
|
|
throw new ExceptionDbOperationEmpty(); |
299
|
|
|
} |
300
|
|
|
|
301
|
|
|
if (!in_array($this->operation, self::$allowedOperations)) { |
302
|
|
|
throw new ExceptionDbOperationRestricted(); |
303
|
|
|
} |
304
|
|
|
|
305
|
16 |
|
$this->_compiledQuery = array(); |
306
|
16 |
|
|
307
|
|
|
$this->_compiledQuery[] = $this->stringEscape($this->operation); |
308
|
16 |
|
$this->_compiledQuery[] = $this->selectFieldsToString($this->fields); |
309
|
16 |
|
|
310
|
15 |
|
$this->compileFrom(); |
311
|
15 |
|
$this->compileJoin(); |
312
|
13 |
|
$this->compileWhere(); |
313
|
13 |
|
$this->compileGroupBy(); |
314
|
16 |
|
$this->compileOrderBy(); |
315
|
|
|
$this->compileHaving(); |
316
|
16 |
|
$this->compileLimit(); |
317
|
3 |
|
$this->compileForUpdate(); |
318
|
|
|
|
319
|
|
|
return implode(' ', $this->_compiledQuery); |
320
|
13 |
|
} |
321
|
|
|
|
322
|
|
|
/** |
323
|
|
|
* @param array|mixed $fields |
324
|
|
|
* |
325
|
|
|
* @return string |
326
|
|
|
* @throws ExceptionDBFieldEmpty |
327
|
|
|
*/ |
328
|
13 |
|
protected function selectFieldsToString($fields) { |
329
|
13 |
|
HelperArray::makeArrayRef($fields); |
330
|
4 |
|
|
331
|
13 |
|
$result = array(); |
332
|
2 |
|
foreach ($fields as $fieldName) { |
333
|
11 |
|
$string = $this->processField($fieldName); |
334
|
2 |
|
if ($string !== '') { |
335
|
2 |
|
$result[] = $string; |
336
|
9 |
|
} |
337
|
|
|
} |
338
|
|
|
|
339
|
13 |
|
if (empty($result)) { |
340
|
|
|
throw new ExceptionDBFieldEmpty(); |
341
|
|
|
} |
342
|
|
|
|
343
|
|
|
return implode(',', $result); |
344
|
|
|
} |
345
|
|
|
|
346
|
|
|
/** |
347
|
9 |
|
* @param mixed $fieldName |
348
|
9 |
|
* |
349
|
|
|
* @return string |
350
|
|
|
*/ |
351
|
9 |
|
protected function processFieldString($fieldName) { |
352
|
|
|
$result = (string)$fieldName; |
353
|
8 |
|
if ( |
354
|
9 |
|
$result != '' |
355
|
|
|
&& |
356
|
7 |
|
// Literals plays as they are - they do properly format by itself |
357
|
9 |
|
!($fieldName instanceof DbSqlLiteral) |
358
|
|
|
) { |
359
|
3 |
|
// Other should be formatted |
360
|
3 |
|
$result = '`' . $this->stringEscape($result) . '`'; |
361
|
|
|
} |
362
|
9 |
|
|
363
|
|
|
return $result; |
364
|
|
|
} |
365
|
|
|
|
366
|
|
|
/** |
367
|
|
|
* @param mixed $fieldName |
368
|
|
|
* |
369
|
|
|
* @return string |
370
|
|
|
*/ |
371
|
|
|
protected function processField($fieldName) { |
372
|
|
|
if (is_bool($fieldName)) { |
373
|
|
|
$result = (string)intval($fieldName); |
374
|
|
|
} elseif (is_numeric($fieldName)) { |
375
|
|
|
$result = $fieldName; |
376
|
|
|
} elseif (is_null($fieldName)) { |
377
|
|
|
$result = 'NULL'; |
378
|
|
|
} elseif ($fieldName === '*') { |
379
|
|
|
$result = '*'; |
380
|
|
|
} else { |
381
|
|
|
// Field has other type - string or should be convertible to string |
382
|
|
|
$result = $this->processFieldString($fieldName); |
383
|
|
|
} |
384
|
|
|
|
385
|
|
|
return $result; |
386
|
|
|
} |
387
|
|
|
|
388
|
|
|
} |
389
|
|
|
|
Sometimes obsolete code just ends up commented out instead of removed. In this case it is better to remove the code once you have checked you do not need it.
The code might also have been commented out for debugging purposes. In this case it is vital that someone uncomments it again or your project may behave in very unexpected ways in production.
This check looks for comments that seem to be mostly valid code and reports them.