1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace vakata\database; |
4
|
|
|
|
5
|
|
|
use \vakata\collection\Collection; |
6
|
|
|
use \vakata\database\schema\Table; |
7
|
|
|
use \vakata\database\schema\TableQuery; |
8
|
|
|
use \vakata\database\schema\TableQueryMapped; |
9
|
|
|
use \vakata\database\schema\TableRelation; |
10
|
|
|
|
11
|
|
|
/** |
12
|
|
|
* A database abstraction with support for various drivers (mySQL, postgre, oracle, msSQL, sphinx, and even PDO). |
13
|
|
|
*/ |
14
|
|
|
class DB implements DBInterface |
15
|
|
|
{ |
16
|
|
|
/** |
17
|
|
|
* @var DriverInterface |
18
|
|
|
*/ |
19
|
|
|
protected $driver; |
20
|
|
|
/** |
21
|
|
|
* @var Table[] |
22
|
|
|
*/ |
23
|
|
|
protected $tables = []; |
24
|
|
|
|
25
|
|
|
/** |
26
|
|
|
* Create an instance. |
27
|
|
|
* |
28
|
|
|
* @param string $connectionString a driver instance or a connection string |
29
|
|
|
*/ |
30
|
15 |
|
public function __construct(string $connectionString) |
31
|
|
|
{ |
32
|
|
|
$connection = [ |
33
|
15 |
|
'orig' => $connectionString, |
34
|
|
|
'type' => null, |
35
|
|
|
'user' => null, |
36
|
|
|
'pass' => null, |
37
|
|
|
'host' => null, |
38
|
|
|
'port' => null, |
39
|
|
|
'name' => null, |
40
|
|
|
'opts' => [] |
41
|
|
|
]; |
42
|
|
|
$aliases = [ |
43
|
15 |
|
'my' => 'mysql', |
44
|
|
|
'mysqli' => 'mysql', |
45
|
|
|
'pg' => 'postgre', |
46
|
|
|
'oci' => 'oracle', |
47
|
|
|
'firebird' => 'ibase' |
48
|
|
|
]; |
49
|
15 |
|
$temp = parse_url($connectionString); |
50
|
15 |
|
if ($temp === false || (isset($temp['query']) && strpos($temp['query'], 'regexparser=1') !== false)) { |
51
|
6 |
|
if (!preg_match( |
52
|
6 |
|
'(^ |
53
|
|
|
(?<scheme>.*?):// |
54
|
|
|
(?:(?<user>.*?)(?:\:(?<pass>.*))?@)? |
55
|
|
|
(?<host>[a-zа-я.\-_0-9=();:]+?) # added =();: for oracle and pdo configs |
56
|
|
|
(?:\:(?<port>\d+))? |
57
|
|
|
(?<path>/.+?)? # path is optional for oracle and pdo configs |
58
|
|
|
(?:\?(?<query>.*))? |
59
|
|
|
$)xui', |
60
|
6 |
|
$connectionString, |
61
|
6 |
|
$temp |
62
|
|
|
)) { |
63
|
|
|
$temp = explode('://', $connectionString, 2); |
64
|
|
|
if (!preg_match('(^[a-z0-9_]+$)i', $temp[0])) { |
65
|
|
|
throw new DBException('Could not parse connection string'); |
66
|
|
|
} |
67
|
|
|
$temp = [ |
68
|
|
|
'scheme' => $temp[0] |
69
|
|
|
]; |
70
|
|
|
} |
71
|
|
|
} |
72
|
15 |
|
$connection['type'] = isset($temp['scheme']) && strlen($temp['scheme']) ? $temp['scheme'] : null; |
73
|
15 |
|
$connection['user'] = isset($temp['user']) && strlen($temp['user']) ? $temp['user'] : null; |
74
|
15 |
|
$connection['pass'] = isset($temp['pass']) && strlen($temp['pass']) ? $temp['pass'] : null; |
75
|
15 |
|
$connection['host'] = isset($temp['host']) && strlen($temp['host']) ? $temp['host'] : null; |
76
|
15 |
|
$connection['name'] = isset($temp['path']) && strlen($temp['path']) ? trim($temp['path'], '/') : null; |
77
|
15 |
|
$connection['port'] = isset($temp['port']) && (int)$temp['port'] ? (int)$temp['port'] : null; |
78
|
15 |
|
if (isset($temp['query']) && strlen($temp['query'])) { |
79
|
15 |
|
parse_str($temp['query'], $connection['opts']); |
80
|
|
|
} |
81
|
|
|
// create the driver |
82
|
15 |
|
$connection['type'] = $aliases[$connection['type']] ?? $connection['type']; |
83
|
15 |
|
$tmp = '\\vakata\\database\\driver\\'.strtolower($connection['type']).'\\Driver'; |
84
|
15 |
|
if (!class_exists($tmp)) { |
85
|
|
|
throw new DBException('Unknown DB backend'); |
86
|
|
|
} |
87
|
15 |
|
$this->driver = new $tmp($connection); |
88
|
15 |
|
} |
89
|
|
|
|
90
|
|
|
public function driver(): DriverInterface |
91
|
|
|
{ |
92
|
|
|
return $this->driver; |
93
|
|
|
} |
94
|
|
|
|
95
|
|
|
/** |
96
|
|
|
* Prepare a statement. |
97
|
|
|
* Use only if you need a single query to be performed multiple times with different parameters. |
98
|
|
|
* |
99
|
|
|
* @param string $sql the query to prepare - use `?` for arguments |
100
|
|
|
* @return StatementInterface the prepared statement |
101
|
|
|
*/ |
102
|
1 |
|
public function prepare(string $sql) : StatementInterface |
103
|
|
|
{ |
104
|
1 |
|
return $this->driver->prepare($sql); |
105
|
|
|
} |
106
|
|
|
/** |
107
|
|
|
* Test the connection |
108
|
|
|
* |
109
|
|
|
* @return bool |
110
|
|
|
*/ |
111
|
1 |
|
public function test() : bool |
112
|
|
|
{ |
113
|
1 |
|
return $this->driver->test(); |
114
|
|
|
} |
115
|
27 |
|
protected function expand(string $sql, $par = null) : array |
116
|
|
|
{ |
117
|
27 |
|
$new = ''; |
118
|
27 |
|
$par = array_values($par); |
119
|
27 |
|
if (substr_count($sql, '?') === 2 && !is_array($par[0])) { |
120
|
3 |
|
$par = [ $par ]; |
121
|
|
|
} |
122
|
27 |
|
$parts = explode('??', $sql); |
123
|
27 |
|
$index = 0; |
124
|
27 |
|
foreach ($parts as $part) { |
125
|
27 |
|
$tmp = explode('?', $part); |
126
|
27 |
|
$new .= $part; |
127
|
27 |
|
$index += count($tmp) - 1; |
128
|
27 |
|
if (isset($par[$index])) { |
129
|
27 |
|
if (!is_array($par[$index])) { |
130
|
|
|
$par[$index] = [ $par[$index] ]; |
131
|
|
|
} |
132
|
27 |
|
$params = $par[$index]; |
133
|
27 |
|
array_splice($par, $index, 1, $params); |
134
|
27 |
|
$index += count($params); |
135
|
27 |
|
$new .= implode(',', array_fill(0, count($params), '?')); |
136
|
|
|
} |
137
|
|
|
} |
138
|
27 |
|
return [ $new, $par ]; |
139
|
|
|
} |
140
|
|
|
/** |
141
|
|
|
* Run a query (prepare & execute). |
142
|
|
|
* @param string $sql SQL query |
143
|
|
|
* @param mixed $par parameters (optional) |
144
|
|
|
* @param bool $buff should the results be buffered (defaults to true) |
145
|
|
|
* @return ResultInterface the result of the execution |
146
|
|
|
*/ |
147
|
166 |
|
public function query(string $sql, $par = null, bool $buff = true) : ResultInterface |
148
|
|
|
{ |
149
|
166 |
|
$par = isset($par) ? (is_array($par) ? $par : [$par]) : []; |
150
|
166 |
|
if (strpos($sql, '??') && count($par)) { |
151
|
27 |
|
list($sql, $par) = $this->expand($sql, $par); |
152
|
|
|
} |
153
|
166 |
|
return $this->driver->prepare($sql)->execute($par, $buff); |
154
|
|
|
} |
155
|
|
|
/** |
156
|
|
|
* Run a SELECT query and get an array-like result. |
157
|
|
|
* When using `get` the data is kept in the database client and fetched as needed (not in PHP memory as with `all`) |
158
|
|
|
* |
159
|
|
|
* @param string $sql SQL query |
160
|
|
|
* @param array $par parameters |
161
|
|
|
* @param string $key column name to use as the array index |
162
|
|
|
* @param bool $skip do not include the column used as index in the value (defaults to `false`) |
163
|
|
|
* @param bool $opti if a single column is returned - do not use an array wrapper (defaults to `true`) |
164
|
|
|
* @param bool $buff should the results be buffered (defaults to `false`) |
165
|
|
|
* |
166
|
|
|
* @return Collection the result of the execution |
167
|
|
|
*/ |
168
|
160 |
|
public function get( |
169
|
|
|
string $sql, |
170
|
|
|
$par = null, |
171
|
|
|
string $key = null, |
172
|
|
|
bool $skip = false, |
173
|
|
|
bool $opti = true, |
174
|
|
|
bool $buff = true |
175
|
|
|
): Collection { |
176
|
160 |
|
$coll = Collection::from($this->query($sql, $par, $buff)); |
177
|
160 |
|
if (($keys = $this->driver->option('mode')) && in_array($keys, ['strtoupper', 'strtolower'])) { |
178
|
1 |
|
$coll->map(function ($v) use ($keys) { |
179
|
1 |
|
$new = []; |
180
|
1 |
|
foreach ($v as $k => $vv) { |
181
|
1 |
|
$new[call_user_func($keys, $k)] = $vv; |
182
|
|
|
} |
183
|
1 |
|
return $new; |
184
|
1 |
|
}); |
185
|
|
|
} |
186
|
160 |
|
if ($key !== null) { |
187
|
2 |
|
$coll->mapKey(function ($v) use ($key) { |
188
|
2 |
|
return $v[$key]; |
189
|
2 |
|
}); |
190
|
|
|
} |
191
|
160 |
|
if ($skip) { |
192
|
2 |
|
$coll->map(function ($v) use ($key) { |
193
|
2 |
|
unset($v[$key]); |
194
|
2 |
|
return $v; |
195
|
2 |
|
}); |
196
|
|
|
} |
197
|
160 |
|
if ($opti) { |
198
|
67 |
|
$coll->map(function ($v) { |
199
|
67 |
|
return count($v) === 1 ? current($v) : $v; |
200
|
67 |
|
}); |
201
|
|
|
} |
202
|
160 |
|
return $coll; |
203
|
|
|
} |
204
|
|
|
/** |
205
|
|
|
* Run a SELECT query and get a single row |
206
|
|
|
* @param string $sql SQL query |
207
|
|
|
* @param array $par parameters |
208
|
|
|
* @param bool $opti if a single column is returned - do not use an array wrapper (defaults to `true`) |
209
|
|
|
* @return mixed the result of the execution |
210
|
|
|
*/ |
211
|
63 |
|
public function one(string $sql, $par = null, bool $opti = true) |
212
|
|
|
{ |
213
|
63 |
|
return $this->get($sql, $par, null, false, $opti, true)->value(); |
214
|
|
|
} |
215
|
|
|
/** |
216
|
|
|
* Run a SELECT query and get an array |
217
|
|
|
* @param string $sql SQL query |
218
|
|
|
* @param array $par parameters |
219
|
|
|
* @param string $key column name to use as the array index |
220
|
|
|
* @param bool $skip do not include the column used as index in the value (defaults to `false`) |
221
|
|
|
* @param bool $opti if a single column is returned - do not use an array wrapper (defaults to `true`) |
222
|
|
|
* @return array the result of the execution |
223
|
|
|
*/ |
224
|
6 |
|
public function all(string $sql, $par = null, string $key = null, bool $skip = false, bool $opti = true) : array |
225
|
|
|
{ |
226
|
6 |
|
return $this->get($sql, $par, $key, $skip, $opti, true)->toArray(); |
227
|
|
|
} |
228
|
|
|
public function unbuffered( |
229
|
|
|
string $sql, |
230
|
|
|
$par = null, |
231
|
|
|
string $key = null, |
232
|
|
|
bool $skip = false, |
233
|
|
|
bool $opti = true |
234
|
|
|
) : Collection { |
235
|
|
|
return $this->get($sql, $par, $key, $skip, $opti, false); |
236
|
|
|
} |
237
|
|
|
/** |
238
|
|
|
* Begin a transaction. |
239
|
|
|
* @return $this |
240
|
|
|
*/ |
241
|
1 |
|
public function begin() : DBInterface |
242
|
|
|
{ |
243
|
1 |
|
if (!$this->driver->begin()) { |
244
|
|
|
throw new DBException('Could not begin'); |
245
|
|
|
} |
246
|
1 |
|
return $this; |
247
|
|
|
} |
248
|
|
|
/** |
249
|
|
|
* Commit a transaction. |
250
|
|
|
* @return $this |
251
|
|
|
*/ |
252
|
1 |
|
public function commit() : DBInterface |
253
|
|
|
{ |
254
|
1 |
|
if (!$this->driver->commit()) { |
255
|
|
|
throw new DBException('Could not commit'); |
256
|
|
|
} |
257
|
1 |
|
return $this; |
258
|
|
|
} |
259
|
|
|
/** |
260
|
|
|
* Rollback a transaction. |
261
|
|
|
* @return $this |
262
|
|
|
*/ |
263
|
1 |
|
public function rollback() : DBInterface |
264
|
|
|
{ |
265
|
1 |
|
if (!$this->driver->rollback()) { |
266
|
|
|
throw new DBException('Could not rollback'); |
267
|
|
|
} |
268
|
1 |
|
return $this; |
269
|
|
|
} |
270
|
|
|
/** |
271
|
|
|
* Get the current driver name (`"mysql"`, `"postgre"`, etc). |
272
|
|
|
* @return string the current driver name |
273
|
|
|
*/ |
274
|
16 |
|
public function driverName() : string |
275
|
|
|
{ |
276
|
16 |
|
return array_reverse(explode('\\', get_class($this->driver)))[1]; |
277
|
|
|
} |
278
|
|
|
/** |
279
|
|
|
* Get an option from the driver |
280
|
|
|
* |
281
|
|
|
* @param string $key the option name |
282
|
|
|
* @param mixed $default the default value to return if the option key is not defined |
283
|
|
|
* @return mixed the option value |
284
|
|
|
*/ |
285
|
76 |
|
public function driverOption(string $key, $default = null) |
286
|
|
|
{ |
287
|
76 |
|
return $this->driver->option($key, $default); |
288
|
|
|
} |
289
|
|
|
|
290
|
156 |
|
public function definition(string $table, bool $detectRelations = true) : Table |
291
|
|
|
{ |
292
|
156 |
|
return isset($this->tables[$table]) ? |
293
|
|
|
$this->tables[$table] : |
294
|
156 |
|
$this->driver->table($table, $detectRelations); |
295
|
|
|
} |
296
|
|
|
/** |
297
|
|
|
* Parse all tables from the database. |
298
|
|
|
* @return $this |
299
|
|
|
*/ |
300
|
|
|
public function parseSchema() |
301
|
|
|
{ |
302
|
|
|
$this->tables = $this->driver->tables(); |
303
|
|
|
return $this; |
304
|
|
|
} |
305
|
|
|
/** |
306
|
|
|
* Get the full schema as an array that you can serialize and store |
307
|
|
|
* @return array |
308
|
|
|
*/ |
309
|
|
|
public function getSchema($asPlainArray = true) |
310
|
|
|
{ |
311
|
4 |
|
return !$asPlainArray ? $this->tables : array_map(function ($table) { |
312
|
|
|
return [ |
313
|
|
|
'name' => $table->getName(), |
314
|
|
|
'pkey' => $table->getPrimaryKey(), |
315
|
|
|
'comment' => $table->getComment(), |
316
|
|
|
'columns' => array_map(function ($column) { |
317
|
|
|
return [ |
318
|
|
|
'name' => $column->getName(), |
319
|
|
|
'type' => $column->getType(), |
320
|
|
|
'length' => $column->getLength(), |
321
|
|
|
'comment' => $column->getComment(), |
322
|
|
|
'values' => $column->getValues(), |
323
|
|
|
'default' => $column->getDefault(), |
324
|
|
|
'nullable' => $column->isNullable() |
325
|
|
|
]; |
326
|
|
|
}, $table->getFullColumns()), |
327
|
|
|
'relations' => array_map(function ($rel) { |
328
|
|
|
$relation = clone $rel; |
329
|
|
|
$relation = (array)$relation; |
330
|
|
|
$relation['table'] = $rel->table->getName(); |
331
|
|
|
if ($rel->pivot) { |
332
|
|
|
$relation['pivot'] = $rel->pivot->getName(); |
333
|
|
|
} |
334
|
|
|
return $relation; |
335
|
|
|
}, $table->getRelations()) |
336
|
|
|
]; |
337
|
4 |
|
}, $this->tables); |
338
|
|
|
} |
339
|
|
|
/** |
340
|
|
|
* Load the schema data from a schema definition array (obtained from getSchema) |
341
|
|
|
* @param array $data the schema definition |
342
|
|
|
* @return $this |
343
|
|
|
*/ |
344
|
|
|
public function setSchema(array $data) |
345
|
|
|
{ |
346
|
|
|
foreach ($data as $tableData) { |
347
|
|
|
$this->tables[$tableData['name']] = (new Table($tableData['name'])) |
348
|
|
|
->setPrimaryKey($tableData['pkey']) |
349
|
|
|
->setComment($tableData['comment']) |
350
|
|
|
->addColumns($tableData['columns']); |
351
|
|
|
} |
352
|
|
|
foreach ($data as $tableData) { |
353
|
|
|
$table = $this->definition($tableData['name']); |
354
|
|
|
foreach ($tableData['relations'] as $relationName => $relationData) { |
355
|
|
|
$relationData['table'] = $this->definition($relationData['table']); |
356
|
|
|
if ($relationData['pivot']) { |
357
|
|
|
$relationData['pivot'] = $this->definition($relationData['pivot']); |
358
|
|
|
} |
359
|
|
|
$table->addRelation(new TableRelation( |
360
|
|
|
$relationData['name'], |
361
|
|
|
$relationData['table'], |
362
|
|
|
$relationData['keymap'], |
363
|
|
|
$relationData['many'], |
364
|
|
|
$relationData['pivot'] ?? null, |
365
|
|
|
$relationData['pivot_keymap'], |
366
|
|
|
$relationData['sql'], |
367
|
|
|
$relationData['par'] |
368
|
|
|
)); |
369
|
|
|
} |
370
|
|
|
} |
371
|
|
|
return $this; |
372
|
|
|
} |
373
|
|
|
|
374
|
|
|
/** |
375
|
|
|
* Initialize a table query |
376
|
|
|
* @param string $table the table to query |
377
|
|
|
* @return TableQuery |
378
|
|
|
*/ |
379
|
156 |
|
public function table(string $table, bool $mapped = false) |
380
|
|
|
{ |
381
|
156 |
|
return $mapped ? |
382
|
80 |
|
new TableQueryMapped($this, $this->definition($table)) : |
383
|
156 |
|
new TableQuery($this, $this->definition($table)); |
384
|
|
|
} |
385
|
148 |
|
public function __call($method, $args) |
386
|
|
|
{ |
387
|
148 |
|
return $this->table($method, $args[0] ?? false); |
388
|
|
|
} |
389
|
|
|
} |
390
|
|
|
|