1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
/** |
4
|
|
|
* @author Marwan Al-Soltany <[email protected]> |
5
|
|
|
* @copyright Marwan Al-Soltany 2021 |
6
|
|
|
* For the full copyright and license information, please view |
7
|
|
|
* the LICENSE file that was distributed with this source code. |
8
|
|
|
*/ |
9
|
|
|
|
10
|
|
|
declare(strict_types=1); |
11
|
|
|
|
12
|
|
|
namespace MAKS\Velox\Backend\Model; |
13
|
|
|
|
14
|
|
|
use MAKS\Velox\Backend\Database; |
15
|
|
|
use MAKS\Velox\Helper\Misc; |
16
|
|
|
|
17
|
|
|
/** |
18
|
|
|
* An abstract class that serves as a DBAL for models. |
19
|
|
|
* |
20
|
|
|
* @since 1.5.1 |
21
|
|
|
* @internal |
22
|
|
|
*/ |
23
|
|
|
abstract class DBAL |
24
|
|
|
{ |
25
|
|
|
/** |
26
|
|
|
* Model table name. If not set, an auto-generated name will be used instead. |
27
|
|
|
* For good practice, keep the model name in singular form and make the table name in plural form. |
28
|
|
|
*/ |
29
|
|
|
protected static ?string $table = null; |
30
|
|
|
|
31
|
|
|
/** |
32
|
|
|
* Model table columns. If not set, the model will fall back to the default primary key `['id']`. |
33
|
|
|
* For good practice, keep the table columns in `snake_case`. Model attribute names match table columns. |
34
|
|
|
*/ |
35
|
|
|
protected static ?array $columns = ['id']; |
36
|
|
|
|
37
|
|
|
/** |
38
|
|
|
* Model table primary key. If not set, `id` will be used by default. |
39
|
|
|
*/ |
40
|
|
|
protected static ?string $primaryKey = 'id'; |
41
|
|
|
|
42
|
|
|
/** |
43
|
|
|
* The database instance/connection. |
44
|
|
|
*/ |
45
|
|
|
protected static ?Database $database; |
46
|
|
|
|
47
|
|
|
|
48
|
|
|
/** |
49
|
|
|
* Returns model database connection and sets `static::$database` with a default value if it's not set. |
50
|
|
|
* |
51
|
|
|
* @return Database |
52
|
|
|
*/ |
53
|
41 |
|
public static function getDatabase(): Database |
54
|
|
|
{ |
55
|
41 |
|
if (empty(static::$database)) { |
56
|
1 |
|
static::$database = Database::instance(); |
57
|
|
|
} |
58
|
|
|
|
59
|
41 |
|
return static::$database; |
60
|
|
|
} |
61
|
|
|
|
62
|
|
|
/** |
63
|
|
|
* Returns model table name and sets `static::$table` with a default value and returns it if it's not set. |
64
|
|
|
* |
65
|
|
|
* @return string |
66
|
|
|
*/ |
67
|
41 |
|
public static function getTable(): string |
68
|
|
|
{ |
69
|
41 |
|
if (empty(static::$table)) { |
70
|
1 |
|
$class = (new \ReflectionClass(static::class))->getShortName(); |
71
|
1 |
|
static::$table = Misc::transform($class . '_model_entries', 'snake'); |
72
|
|
|
} |
73
|
|
|
|
74
|
41 |
|
return static::$table; |
75
|
|
|
} |
76
|
|
|
|
77
|
|
|
/** |
78
|
|
|
* Returns model table columns and sets `static::$columns` with a default value and returns it if it's not set. |
79
|
|
|
* |
80
|
|
|
* @return array |
81
|
|
|
*/ |
82
|
41 |
|
public static function getColumns(): array |
83
|
|
|
{ |
84
|
41 |
|
if (empty(static::$columns)) { |
85
|
1 |
|
static::$columns = ['id']; |
86
|
|
|
} |
87
|
|
|
|
88
|
41 |
|
return static::$columns; |
89
|
|
|
} |
90
|
|
|
|
91
|
|
|
/** |
92
|
|
|
* Returns model table primary key and sets `static::$primaryKey` with a default value and returns it if it's not set. |
93
|
|
|
* |
94
|
|
|
* @return string |
95
|
|
|
*/ |
96
|
39 |
|
public static function getPrimaryKey(): string |
97
|
|
|
{ |
98
|
39 |
|
if (empty(static::$primaryKey)) { |
99
|
1 |
|
static::$primaryKey = 'id'; |
100
|
|
|
} |
101
|
|
|
|
102
|
39 |
|
return static::$primaryKey; |
103
|
|
|
} |
104
|
|
|
|
105
|
|
|
/** |
106
|
|
|
* The SQL code to create the model table from. Has to match `self::$table`, `self::$columns`, and `self::$primaryKey`. |
107
|
|
|
* Example: ```CREATE TABLE IF NOT EXISTS `table` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `text` VARCHAR(255));``` |
108
|
|
|
* |
109
|
|
|
* @return string |
110
|
|
|
*/ |
111
|
|
|
abstract public static function schema(): string; |
112
|
|
|
|
113
|
|
|
/** |
114
|
|
|
* Migrates model table to the database. |
115
|
|
|
* |
116
|
|
|
* @return void |
117
|
|
|
* |
118
|
|
|
* @throws \BadMethodCallException If called in an abstract class context. |
119
|
|
|
*/ |
120
|
40 |
|
final public static function migrate(): void |
121
|
|
|
{ |
122
|
40 |
|
if (self::class === static::class || (new \ReflectionClass(static::class))->isAbstract()) { |
|
|
|
|
123
|
1 |
|
throw new \BadMethodCallException(sprintf( |
124
|
1 |
|
'Cannot migrate an abstract class, "%s" methods should be used by extension only', |
125
|
1 |
|
self::class |
126
|
|
|
)); |
127
|
|
|
} |
128
|
|
|
|
129
|
40 |
|
static::getDatabase()->perform(trim(static::schema())); |
130
|
40 |
|
} |
131
|
|
|
|
132
|
|
|
/** |
133
|
|
|
* Checks whether the model table is migrated to the database or not. |
134
|
|
|
* You can override this method and return always `true` to disable auto migration. |
135
|
|
|
* |
136
|
|
|
* NOTE: For compatibility reasons, the return value of this method true if it fails to connect to the database. |
137
|
|
|
* |
138
|
|
|
* @return bool |
139
|
|
|
*/ |
140
|
41 |
|
public static function isMigrated(): bool |
141
|
|
|
{ |
142
|
41 |
|
$table = static::getTable(); |
143
|
41 |
|
$tables = [$table]; |
|
|
|
|
144
|
|
|
|
145
|
|
|
try { |
146
|
41 |
|
$tables = static::getDatabase() |
147
|
41 |
|
->query('SHOW TABLES;') |
148
|
41 |
|
->fetchAll(\PDO::FETCH_COLUMN); |
149
|
|
|
} catch (\Exception $e) { |
150
|
|
|
// ignore silently |
151
|
|
|
} |
152
|
|
|
|
153
|
41 |
|
return in_array($table, $tables); |
154
|
|
|
} |
155
|
|
|
|
156
|
|
|
|
157
|
|
|
/** |
158
|
|
|
* Executes a query (a prepared statement) and returns the result. |
159
|
|
|
* |
160
|
|
|
* @param string $query The query to execute. The `@table` can be used to inject the current model table name into the query. |
161
|
|
|
* @param array|null $variables [optional] The variables needed for the query. |
162
|
|
|
* @param bool $raw [optional] Whether fetch the models as arrays (raw) or as hydrated objects. |
163
|
|
|
* |
164
|
|
|
* @return static[]|array[] The result as an array of objects or array of arrays depending on the passed parameters. |
165
|
|
|
* |
166
|
|
|
* @throws \BadMethodCallException If called in an abstract class context. |
167
|
|
|
* |
168
|
|
|
* Example: |
169
|
|
|
* - ```Model::fetch('SELECT * FROM `users` WHERE `name` = :name OR `age` = :age', ['name' => 'Doe', 'age' => 27], true)``` |
170
|
|
|
*/ |
171
|
29 |
|
public static function fetch(string $query, ?array $variables = [], bool $raw = false): array |
172
|
|
|
{ |
173
|
29 |
|
if (static::class === self::class || (new \ReflectionClass(static::class))->isAbstract()) { |
|
|
|
|
174
|
1 |
|
throw new \BadMethodCallException(sprintf( |
175
|
1 |
|
'Cannot fetch for an abstract class, "%s" methods should be used by extension only', |
176
|
1 |
|
self::class |
177
|
|
|
)); |
178
|
|
|
} |
179
|
|
|
|
180
|
28 |
|
$table = sprintf('`%s`', static::getTable()); |
181
|
28 |
|
$query = str_ireplace(['@table', '`@table`'], $table, $query); |
182
|
28 |
|
$variables = $variables ?? []; |
183
|
|
|
|
184
|
28 |
|
$class = static::class; |
185
|
|
|
|
186
|
28 |
|
return static::getDatabase()->transactional(function () use ($query, $variables, $raw, $class) { |
187
|
|
|
/** @var Database $this */ |
188
|
28 |
|
$statement = $this->perform($query, $variables); |
|
|
|
|
189
|
28 |
|
$result = $raw |
190
|
2 |
|
? $statement->fetchAll(\PDO::FETCH_ASSOC) |
191
|
28 |
|
: $statement->fetchAll(\PDO::FETCH_CLASS|\PDO::FETCH_PROPS_LATE, $class, [/* $class constructor arguments */]); |
192
|
|
|
|
193
|
28 |
|
return $result; |
194
|
28 |
|
}); |
195
|
|
|
} |
196
|
|
|
|
197
|
|
|
/** |
198
|
|
|
* Finds a single or multiple models by the passed condition. |
199
|
|
|
* |
200
|
|
|
* @param string $column The column/attribute name. |
201
|
|
|
* @param string $operator Condition operator, can be: `=`, `!=`, `<>`, `<`, `>`, `<=`, `>=`, `LIKE`, `NOT LIKE`, `IN`, `NOT IN`. |
202
|
|
|
* @param mixed $value The value to compare to. |
203
|
|
|
* @param array[] $additional [optional] Additional conditions. Can be used to add more conditions to the `WHERE` clause. Deep nesting can be achieved by simply using a child array. |
204
|
|
|
* @param string|null $order [optional] SQL order expression (like: `id` or `id ASC`). |
205
|
|
|
* @param int|null $limit [optional] To how many items the result should be limited. |
206
|
|
|
* @param int|null $offset [optional] From which item the result should start. |
207
|
|
|
* |
208
|
|
|
* @return static[]|array[] |
209
|
|
|
* |
210
|
|
|
* @throws \InvalidArgumentException If operator is not supported or a condition is invalid. |
211
|
|
|
* @throws \BadMethodCallException If called in an abstract class context. |
212
|
|
|
* |
213
|
|
|
* Examples: |
214
|
|
|
* - `Model::where('name', '=', 'Doe')`. |
215
|
|
|
* - `Model::where('age', '>', 27, [['AND', 'name', 'LIKE', 'Doe%'], $query, ..., [$subQuery, ...]], $order, $limit, $offset)`. |
216
|
|
|
*/ |
217
|
3 |
|
public static function where( |
218
|
|
|
string $column, |
219
|
|
|
string $operator, |
220
|
|
|
$value, |
221
|
|
|
?array $additional = null, |
222
|
|
|
?string $order = null, |
223
|
|
|
?int $limit = null, |
224
|
|
|
?int $offset = null |
225
|
|
|
): array { |
226
|
3 |
|
$conditions = array_merge([['', $column, $operator, $value]], $additional ?? []); |
227
|
|
|
|
228
|
3 |
|
$where = static::buildWhereClause($conditions); |
229
|
1 |
|
$query = sprintf('SELECT * FROM @table %s', $where['query']); |
230
|
1 |
|
$variables = $where['variables']; |
231
|
|
|
|
232
|
1 |
|
if ($order !== null) { |
233
|
1 |
|
$query .= ' ORDER BY ' . $order; |
234
|
|
|
} |
235
|
|
|
|
236
|
1 |
|
if ($limit !== null) { |
237
|
1 |
|
$query .= ' LIMIT ' . $limit; |
238
|
|
|
} |
239
|
|
|
|
240
|
1 |
|
if ($offset !== null) { |
241
|
1 |
|
$query .= ' OFFSET ' . $offset; |
242
|
|
|
} |
243
|
|
|
|
244
|
1 |
|
$query .= ';'; |
245
|
|
|
|
246
|
1 |
|
return static::fetch($query, $variables); |
247
|
|
|
} |
248
|
|
|
|
249
|
3 |
|
private static function buildWhereClause(array $conditions): array |
250
|
|
|
{ |
251
|
3 |
|
$query = 'WHERE'; |
252
|
3 |
|
$variables = []; |
253
|
|
|
|
254
|
3 |
|
foreach ($conditions as $index => $condition) { |
255
|
3 |
|
$result = static::buildNestedQuery($condition, $index); |
256
|
|
|
|
257
|
2 |
|
$query = $query . $result['query']; |
258
|
2 |
|
$variables = $variables + $result['variables']; |
259
|
|
|
} |
260
|
|
|
|
261
|
|
|
return [ |
262
|
1 |
|
'query' => (string)$query, |
263
|
1 |
|
'variables' => (array)$variables, |
264
|
|
|
]; |
265
|
|
|
} |
266
|
|
|
|
267
|
|
|
/** |
268
|
|
|
* Builds a nested query. |
269
|
|
|
* |
270
|
|
|
* @param array $condition The condition in the form of `['OPERATOR1', 'COLUMN', 'OPERATOR2', 'VALUE', ], ..., [..., ...]`. |
271
|
|
|
* @param int|string $index The index of the condition. |
272
|
|
|
* |
273
|
|
|
* @return mixed[] An associative array containing the SQL `query` and its needed `variables`. |
274
|
|
|
*/ |
275
|
3 |
|
private static function buildNestedQuery(array $condition, $index): array |
276
|
|
|
{ |
277
|
3 |
|
$query = ''; |
278
|
3 |
|
$variables = []; |
279
|
3 |
|
$nested = 0; |
280
|
|
|
|
281
|
3 |
|
if (is_array($condition[$nested] ?? null)) { |
282
|
1 |
|
$nested = count($condition); |
283
|
1 |
|
$subConditions = $condition; |
284
|
|
|
|
285
|
1 |
|
foreach ($subConditions as $subIndex => $subCondition) { |
286
|
1 |
|
$result = null; |
287
|
|
|
|
288
|
1 |
|
if ($subIndex === 0) { |
289
|
1 |
|
$query .= ' ' . $subCondition[0] . ' ('; |
290
|
1 |
|
$subCondition[0] = ''; |
291
|
1 |
|
$subIndex = sprintf('%s_%s', $index, $subIndex); |
292
|
|
|
|
293
|
1 |
|
$result = static::buildNestedQuery($subCondition, $subIndex); |
294
|
|
|
} else { |
295
|
1 |
|
$result = static::buildNestedQuery($subCondition, $subIndex); |
296
|
|
|
} |
297
|
|
|
|
298
|
1 |
|
$query = $query . $result['query']; |
299
|
1 |
|
$variables = $variables + $result['variables']; |
300
|
|
|
|
301
|
1 |
|
$nested--; |
302
|
|
|
} |
303
|
|
|
|
304
|
1 |
|
$query .= ' )'; |
305
|
|
|
|
306
|
1 |
|
return compact('query', 'variables'); |
307
|
|
|
} |
308
|
|
|
|
309
|
3 |
|
[$operator1, $column, $operator2, $value] = static::validateCondition($condition, $index); |
310
|
|
|
|
311
|
3 |
|
$operator1 = static::validateOperator($operator1, $index); |
312
|
3 |
|
$operator2 = static::validateOperator($operator2, $index); |
313
|
|
|
|
314
|
2 |
|
$placeholder = sprintf('%s_%s', $column, $index); |
315
|
2 |
|
$placeholders = ''; |
316
|
|
|
|
317
|
2 |
|
if ($isInOperator = substr($operator2, -2) === 'IN') { |
318
|
1 |
|
$placeholders = array_map(function ($id) use ($placeholder) { |
319
|
1 |
|
return sprintf('%s_%s', $placeholder, $id); |
320
|
1 |
|
}, array_keys($value)); |
321
|
|
|
|
322
|
1 |
|
$keys = array_values($placeholders); |
323
|
1 |
|
$values = array_values($value); |
324
|
1 |
|
$variables = array_merge($variables, array_combine($keys, $values)); |
325
|
|
|
|
326
|
1 |
|
$placeholders = implode(', ', array_map(fn ($id) => ':' . $id, $placeholders)); |
327
|
|
|
} else { |
328
|
2 |
|
$variables[$placeholder] = $value; |
329
|
|
|
} |
330
|
|
|
|
331
|
2 |
|
$query .= ' ' . trim(vsprintf('%s `%s` %s %s', [ |
332
|
2 |
|
$operator1, |
333
|
2 |
|
$column, |
334
|
2 |
|
$operator2, |
335
|
2 |
|
$isInOperator ? "({$placeholders})" : ":{$placeholder}" |
336
|
|
|
])); |
337
|
|
|
|
338
|
2 |
|
return compact('query', 'variables'); |
339
|
|
|
} |
340
|
|
|
|
341
|
|
|
/** |
342
|
|
|
* Validates the passed condition. |
343
|
|
|
* |
344
|
|
|
* @param array $condition The condition to validate. in the form of `['OPERATOR1', 'COLUMN', 'OPERATOR2', 'VALUE']` |
345
|
|
|
* @param int|string $index The index of the condition (used to make more user-friendly exception). |
346
|
|
|
* |
347
|
|
|
* @return array An array containing the validated condition. |
348
|
|
|
* |
349
|
|
|
* @throws \InvalidArgumentException If the condition is invalid. |
350
|
|
|
*/ |
351
|
3 |
|
private static function validateCondition(array $condition, $index): array |
352
|
|
|
{ |
353
|
3 |
|
$condition = array_merge($condition, array_fill(0, 4, null)); |
354
|
3 |
|
$condition = array_splice($condition, 0, 4); |
355
|
|
|
|
356
|
|
|
// $operator1, $column, $value, $operator2 |
357
|
3 |
|
if (!is_string($condition[0]) || !is_string($condition[1]) || !is_string($condition[2]) || !isset($condition[3])) { |
358
|
1 |
|
throw new \InvalidArgumentException(sprintf( |
359
|
1 |
|
"The passed condition ['%s'] in query at index (%s) is invalid. Was expecting ['%s'], got ['%s']", |
360
|
1 |
|
implode("', '", $condition), |
361
|
|
|
$index, |
362
|
1 |
|
implode("', '", ['operator1 (string)', 'column (string)', 'operator2 (string)', 'value (mixed)']), |
363
|
1 |
|
implode("', '", array_map(fn ($var) => gettype($var), $condition)) |
364
|
|
|
)); |
365
|
|
|
} |
366
|
|
|
|
367
|
3 |
|
return $condition; |
368
|
|
|
} |
369
|
|
|
|
370
|
|
|
/** |
371
|
|
|
* Validates the passed operator. |
372
|
|
|
* |
373
|
|
|
* @param string $operator The operator to validate. |
374
|
|
|
* @param int|string $index The index of the condition (used to make more user-friendly exception). |
375
|
|
|
* |
376
|
|
|
* @return string The validated operator. |
377
|
|
|
* |
378
|
|
|
* @throws \InvalidArgumentException If the operator is invalid. |
379
|
|
|
*/ |
380
|
3 |
|
private static function validateOperator(string $operator, $index): string |
381
|
|
|
{ |
382
|
3 |
|
$operator = strtoupper(trim($operator)); |
383
|
3 |
|
$supported = ['', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'AND', 'OR', '=', '!=', '<>', '<', '>', '<=', '>=']; |
384
|
|
|
|
385
|
3 |
|
if (!in_array($operator, $supported)) { |
386
|
1 |
|
throw new \InvalidArgumentException(sprintf( |
387
|
1 |
|
"Got '%s' as an argument in query at index (%s), which is an invalid or unsupported SQL operator. Supported operators are: ['%s']", |
388
|
|
|
$operator, |
389
|
|
|
$index, |
390
|
1 |
|
implode("', '", $supported), |
391
|
|
|
)); |
392
|
|
|
} |
393
|
|
|
|
394
|
3 |
|
return $operator; |
395
|
|
|
} |
396
|
|
|
} |
397
|
|
|
|