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