1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace Emmanix2002\DatabaseAdapter; |
4
|
|
|
|
5
|
|
|
use Emmanix2002\DatabaseAdapter\Exception\ConfigurationException; |
6
|
|
|
use Emmanix2002\DatabaseAdapter\Exception\ConnectionException; |
7
|
|
|
use PDO; |
8
|
|
|
|
9
|
|
|
/** |
10
|
|
|
* Class MySqlAdapter |
11
|
|
|
* |
12
|
|
|
* @package Emmanix2002\DatabaseAdapter |
13
|
|
|
*/ |
14
|
|
|
class MySqlAdapter implements AdapterInterface |
15
|
|
|
{ |
16
|
|
|
/** |
17
|
|
|
* The instance of the \PDO class |
18
|
|
|
* |
19
|
|
|
* @var \PDO |
20
|
|
|
*/ |
21
|
|
|
private $connection; |
22
|
|
|
|
23
|
|
|
/** |
24
|
|
|
* The database host |
25
|
|
|
* |
26
|
|
|
* @var string |
27
|
|
|
*/ |
28
|
|
|
private $host; |
29
|
|
|
|
30
|
|
|
/** |
31
|
|
|
* The database user |
32
|
|
|
* |
33
|
|
|
* @var string |
34
|
|
|
*/ |
35
|
|
|
private $user; |
36
|
|
|
|
37
|
|
|
/** |
38
|
|
|
* The database user's password |
39
|
|
|
* |
40
|
|
|
* @var string |
41
|
|
|
*/ |
42
|
|
|
private $password; |
43
|
|
|
|
44
|
|
|
/** |
45
|
|
|
* The database schema name |
46
|
|
|
* |
47
|
|
|
* @var string |
48
|
|
|
*/ |
49
|
|
|
private $schema; |
50
|
|
|
|
51
|
|
|
/** |
52
|
|
|
* The default connection configuration used with the create() method |
53
|
|
|
* |
54
|
|
|
* @var array |
55
|
|
|
*/ |
56
|
|
|
private static $defaultConfig = ['host' => 'localhost', 'schema' => '', 'password' => '', 'user' => 'root']; |
57
|
|
|
|
58
|
|
|
/** |
59
|
|
|
* This is a utility method that returns an instance of the class; allowing you to pass an associative array |
60
|
|
|
* containing the configuration while providing defaults. |
61
|
|
|
* The available configuration keys are: |
62
|
|
|
* host the database hostname (required, default: localhost) |
63
|
|
|
* user the database user (default: root) |
64
|
|
|
* password the database user password |
65
|
|
|
* schema the database schema to connect to |
66
|
|
|
* autoconnect whether or not to automatically call the connect() method |
67
|
|
|
* |
68
|
|
|
* @param array $config |
69
|
|
|
* |
70
|
|
|
* @return MySqlAdapter |
71
|
|
|
*/ |
72
|
|
|
public static function create(array $config = []): MySqlAdapter |
73
|
|
|
{ |
74
|
|
|
$host = !empty($config['host']) ? $config['host'] : self::getDefault('host'); |
75
|
|
|
$user = !empty($config['user']) ? $config['user'] : self::getDefault('user'); |
76
|
|
|
$password = !empty($config['password']) ? $config['password'] : self::getDefault('password'); |
77
|
|
|
$schema = !empty($config['schema']) ? $config['schema'] : self::getDefault('schema'); |
78
|
|
|
$autoconnect = !empty($config['autoconnect']) ? (bool) $config['autoconnect'] : false; |
79
|
|
|
return new static($host, $schema, $user, $password, $autoconnect); |
80
|
|
|
} |
81
|
|
|
|
82
|
|
|
/** |
83
|
|
|
* Returns the value of the default configuration option identified by the key name |
84
|
|
|
* |
85
|
|
|
* @param string $key |
86
|
|
|
* |
87
|
|
|
* @return string |
88
|
|
|
*/ |
89
|
|
|
public static function getDefault(string $key): string |
90
|
|
|
{ |
91
|
|
|
return array_key_exists($key, self::$defaultConfig) ? self::$defaultConfig[$key] : ''; |
92
|
|
|
} |
93
|
|
|
|
94
|
|
|
/** |
95
|
|
|
* Sets the value for default configuration key |
96
|
|
|
* |
97
|
|
|
* @param string $key |
98
|
|
|
* @param string $value the value for the setting (for boolean keys, pass NULL or a non-empty string) |
99
|
|
|
* |
100
|
|
|
* @return void |
101
|
|
|
*/ |
102
|
|
|
public static function setDefault(string $key, string $value = null) |
103
|
|
|
{ |
104
|
|
|
if ($key !== 'autoconnect') { |
105
|
|
|
self::$defaultConfig[$key] = $value; |
106
|
|
|
} else { |
107
|
|
|
self::$defaultConfig[$key] = (bool) $value; |
108
|
|
|
} |
109
|
|
|
} |
110
|
|
|
|
111
|
|
|
/** |
112
|
|
|
* MySqlAdapter constructor. |
113
|
|
|
* |
114
|
|
|
* @param string $host |
115
|
|
|
* @param string $schema |
116
|
|
|
* @param string $user |
117
|
|
|
* @param string $password |
118
|
|
|
* @param bool $autoConnect |
119
|
|
|
*/ |
120
|
|
|
public function __construct(string $host, string $schema, string $user, string $password, bool $autoConnect = false) |
121
|
|
|
{ |
122
|
|
|
$this->setHost($host) |
123
|
|
|
->setUser($user) |
124
|
|
|
->setPassword($password) |
125
|
|
|
->setSchema($schema); |
126
|
|
|
if ($autoConnect) { |
127
|
|
|
$this->connect(); |
128
|
|
|
} |
129
|
|
|
} |
130
|
|
|
|
131
|
|
|
/** |
132
|
|
|
* Opens a connection to the database if no connection was previously opened, or if reconnect is TRUE |
133
|
|
|
* |
134
|
|
|
* @param bool $throwExceptionOnFail throws an exception if connection failed |
135
|
|
|
* @param bool $reconnect this forces a reconnect even when a connection was previously established |
136
|
|
|
* |
137
|
|
|
* @return AdapterInterface |
138
|
|
|
* @throws ConfigurationException |
139
|
|
|
*/ |
140
|
|
|
public function connect(bool $throwExceptionOnFail = true, bool $reconnect = false): AdapterInterface |
141
|
|
|
{ |
142
|
|
|
if ($this->connection instanceof \PDO && !$reconnect) { |
143
|
|
|
return $this; |
144
|
|
|
} |
145
|
|
|
$exception = null; |
146
|
|
|
try { |
147
|
|
|
if (empty($this->host) || empty($this->schema)) { |
148
|
|
|
throw new ConfigurationException('The hostname and/or schema were not set'); |
149
|
|
|
} |
150
|
|
|
$dsn = 'mysql:host='.$this->host.';dbname='.$this->schema.';charset=utf8mb4'; |
151
|
|
|
$this->connection = new PDO( |
152
|
|
|
$dsn, |
153
|
|
|
$this->user, |
154
|
|
|
$this->password, |
155
|
|
|
[ |
156
|
|
|
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, |
157
|
|
|
PDO::ATTR_PERSISTENT => false, |
158
|
|
|
PDO::ATTR_EMULATE_PREPARES => false |
159
|
|
|
] |
160
|
|
|
); |
161
|
|
|
# fix suggested by @ircmaxell see: |
162
|
|
|
# http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection/12202218#12202218 |
163
|
|
|
} catch (\PDOException $e) { |
164
|
|
|
$this->connection = null; |
165
|
|
|
$exception = $e; |
166
|
|
|
} |
167
|
|
|
if ($exception !== null && $throwExceptionOnFail) { |
168
|
|
|
throw $exception; |
169
|
|
|
} |
170
|
|
|
return $this; |
171
|
|
|
} |
172
|
|
|
|
173
|
|
|
/** |
174
|
|
|
* Returns the database hostname [to be] used for the connection |
175
|
|
|
* |
176
|
|
|
* @return string |
177
|
|
|
*/ |
178
|
|
|
public function getHost(): string |
179
|
|
|
{ |
180
|
|
|
return $this->host; |
181
|
|
|
} |
182
|
|
|
|
183
|
|
|
/** |
184
|
|
|
* Returns the connection password for the specified database username |
185
|
|
|
* |
186
|
|
|
* @return string |
187
|
|
|
*/ |
188
|
|
|
public function getPassword(): string |
189
|
|
|
{ |
190
|
|
|
return $this->password; |
191
|
|
|
} |
192
|
|
|
|
193
|
|
|
/** |
194
|
|
|
* Returns the database schema to connect to with the connection |
195
|
|
|
* |
196
|
|
|
* @return string |
197
|
|
|
*/ |
198
|
|
|
public function getSchema(): string |
199
|
|
|
{ |
200
|
|
|
return $this->schema; |
201
|
|
|
} |
202
|
|
|
|
203
|
|
|
/** |
204
|
|
|
* Returns the database username [to be] used for the connection |
205
|
|
|
* |
206
|
|
|
* @return string |
207
|
|
|
*/ |
208
|
|
|
public function getUser(): string |
209
|
|
|
{ |
210
|
|
|
return $this->user; |
211
|
|
|
} |
212
|
|
|
|
213
|
|
|
/** |
214
|
|
|
* Returns the actual \PDO object created after a successful connection or NULL |
215
|
|
|
* |
216
|
|
|
* @return \PDO |
217
|
|
|
*/ |
218
|
|
|
public function getConnection() |
219
|
|
|
{ |
220
|
|
|
$this->connect(); |
221
|
|
|
return $this->connection instanceof \PDO ? $this->connection : null; |
222
|
|
|
} |
223
|
|
|
|
224
|
|
|
/** |
225
|
|
|
* Sets the database hostname to be used in the connection |
226
|
|
|
* |
227
|
|
|
* @param string $host |
228
|
|
|
* |
229
|
|
|
* @return AdapterInterface |
230
|
|
|
*/ |
231
|
|
|
public function setHost(string $host): AdapterInterface |
232
|
|
|
{ |
233
|
|
|
if (empty($host)) { |
234
|
|
|
throw new \InvalidArgumentException('Empty database hostname'); |
235
|
|
|
} |
236
|
|
|
$this->host = $host; |
237
|
|
|
return $this; |
238
|
|
|
} |
239
|
|
|
|
240
|
|
|
/** |
241
|
|
|
* Sets the database password to be used in the connection |
242
|
|
|
* |
243
|
|
|
* @param string $password |
244
|
|
|
* |
245
|
|
|
* @return AdapterInterface |
246
|
|
|
*/ |
247
|
|
|
public function setPassword(string $password = ''): AdapterInterface |
248
|
|
|
{ |
249
|
|
|
$this->password = $password; |
250
|
|
|
return $this; |
251
|
|
|
} |
252
|
|
|
|
253
|
|
|
/** |
254
|
|
|
* Sets the database schema name to be used by the connection |
255
|
|
|
* |
256
|
|
|
* @param string $schema |
257
|
|
|
* |
258
|
|
|
* @return AdapterInterface |
259
|
|
|
*/ |
260
|
|
|
public function setSchema(string $schema): AdapterInterface |
261
|
|
|
{ |
262
|
|
|
if (empty($schema)) { |
263
|
|
|
throw new \InvalidArgumentException('Empty schema name'); |
264
|
|
|
} |
265
|
|
|
$this->schema = $schema; |
266
|
|
|
return $this; |
267
|
|
|
} |
268
|
|
|
|
269
|
|
|
/** |
270
|
|
|
* Sets the database username to be used in the connection |
271
|
|
|
* |
272
|
|
|
* @param string $user |
273
|
|
|
* |
274
|
|
|
* @return AdapterInterface |
275
|
|
|
*/ |
276
|
|
|
public function setUser(string $user): AdapterInterface |
277
|
|
|
{ |
278
|
|
|
$this->user = $user; |
279
|
|
|
return $this; |
280
|
|
|
} |
281
|
|
|
|
282
|
|
|
/** |
283
|
|
|
* Starts a new database transaction on the active connection, if any |
284
|
|
|
* |
285
|
|
|
* @return void |
286
|
|
|
* @throws ConnectionException |
287
|
|
|
*/ |
288
|
|
|
public function transactionStart() |
289
|
|
|
{ |
290
|
|
|
$this->connect(); |
291
|
|
|
if (!$this->connection) { |
292
|
|
|
throw new ConnectionException('No database connection'); |
293
|
|
|
} |
294
|
|
|
$this->connection->beginTransaction(); |
295
|
|
|
} |
296
|
|
|
|
297
|
|
|
/** |
298
|
|
|
* Commits an active database transaction |
299
|
|
|
* |
300
|
|
|
* @return void |
301
|
|
|
* @throws ConnectionException |
302
|
|
|
*/ |
303
|
|
|
public function transactionCommit() |
304
|
|
|
{ |
305
|
|
|
if (!$this->connection) { |
306
|
|
|
throw new ConnectionException('No database connection'); |
307
|
|
|
} |
308
|
|
|
$this->connection->commit(); |
309
|
|
|
} |
310
|
|
|
|
311
|
|
|
/** |
312
|
|
|
* Rolls back an active database transaction |
313
|
|
|
* |
314
|
|
|
* @return void |
315
|
|
|
* @throws ConnectionException |
316
|
|
|
*/ |
317
|
|
|
public function transactionRollback() |
318
|
|
|
{ |
319
|
|
|
if (!$this->connection) { |
320
|
|
|
throw new ConnectionException('No database connection'); |
321
|
|
|
} |
322
|
|
|
$this->connection->rollBack(); |
323
|
|
|
} |
324
|
|
|
|
325
|
|
|
/** |
326
|
|
|
* Returns the last AUTO INCREMENT id used for the current connection instance |
327
|
|
|
* |
328
|
|
|
* @return int |
329
|
|
|
*/ |
330
|
|
|
public function getInsertId(): int |
331
|
|
|
{ |
332
|
|
|
return $this->connection instanceof PDO ? (int) $this->connection->lastInsertId() : 0; |
333
|
|
|
} |
334
|
|
|
|
335
|
|
|
/** |
336
|
|
|
* Executes an SQL query against the active database connection, sending the supplied arguments with |
337
|
|
|
* the query. it will either return FALSE on failure, but for successes, depending on the value of $isSelect, |
338
|
|
|
* it will wither return TRUE or the retrieved database records. |
339
|
|
|
* |
340
|
|
|
* @param string $sql the sql query to execute |
341
|
|
|
* @param bool $isSelect whether or not to get the matching records. Useful for SELECT queries |
342
|
|
|
* @param array ...$arguments the arguments to use in the PDOStatement::execute() method call |
343
|
|
|
* |
344
|
|
|
* @return array|bool |
345
|
|
|
* @throws ConnectionException |
346
|
|
|
*/ |
347
|
|
|
public function exec(string $sql, bool $isSelect = false, ...$arguments) |
348
|
|
|
{ |
349
|
|
|
if (empty($sql)) { |
350
|
|
|
throw new \InvalidArgumentException('Empty SQL query'); |
351
|
|
|
} |
352
|
|
|
$this->connect(); |
353
|
|
|
if (!$this->connection) { |
354
|
|
|
throw new ConnectionException('No database connection'); |
355
|
|
|
} |
356
|
|
|
$stmt = $this->connection->prepare($sql); |
357
|
|
|
if (!$stmt) { |
358
|
|
|
throw new ConnectionException('Could not prepare the query on the backend'); |
359
|
|
|
} |
360
|
|
|
if (!$stmt->execute($arguments)) { |
361
|
|
|
return false; |
362
|
|
|
} |
363
|
|
|
return $isSelect ? $stmt->fetchAll(PDO::FETCH_ASSOC) : true; |
364
|
|
|
} |
365
|
|
|
|
366
|
|
|
/** |
367
|
|
|
* Executes an SQL SELECT query against the database, then copies the results of the query into an array |
368
|
|
|
* |
369
|
|
|
* @param string $sql |
370
|
|
|
* @param array ...$arguments |
371
|
|
|
* |
372
|
|
|
* @return array |
373
|
|
|
*/ |
374
|
|
|
public function selectAll(string $sql, ...$arguments): array |
375
|
|
|
{ |
376
|
|
|
$records = $this->exec($sql, true, ...$arguments); |
377
|
|
|
return !empty($records) ? $records : []; |
378
|
|
|
} |
379
|
|
|
|
380
|
|
|
/** |
381
|
|
|
* Similar to the selectAll() except that instead of returning the data as a multi-dimensional array, |
382
|
|
|
* it returns the first index of the array |
383
|
|
|
* |
384
|
|
|
* @param string $sql |
385
|
|
|
* @param array ...$arguments |
386
|
|
|
* |
387
|
|
|
* @return array |
388
|
|
|
*/ |
389
|
|
|
public function selectOne(string $sql, ...$arguments): array |
390
|
|
|
{ |
391
|
|
|
$records = $this->exec($sql, true, ...$arguments); |
392
|
|
|
return !empty($records) ? $records[0] : []; |
393
|
|
|
} |
394
|
|
|
|
395
|
|
|
/** |
396
|
|
|
* @return string |
397
|
|
|
*/ |
398
|
|
|
public function __toString() |
399
|
|
|
{ |
400
|
|
|
return __CLASS__.' Instance {Host: '.$this->host.', Schema: '.$this->schema.', User: '.$this->user.'}'; |
401
|
|
|
} |
402
|
|
|
} |
403
|
|
|
|