db

1
<?php
2

3
namespace Upside\Db;
4

5
class Connection implements \Serializable
6
{
7
    /** Username */
8
    protected ?string $username;
9

10
    /** Password */
11
    protected ?string $password;
12

13
    /** Log queries flag */
14
    protected bool $log_queries = false;
15

16
    /** Logged queries */
17
    protected array $log = [];
18

19
    /** Init commands */
20
    protected array $commands = [];
21

22
    /** PDO connection options */
23
    protected array $options = [
24
        \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
25
        \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_OBJ,
26
        \PDO::ATTR_STRINGIFY_FETCHES => false,
27
        \PDO::ATTR_EMULATE_PREPARES => false,
28
    ];
29

30
    /** The PDO object associated with this connection */
31
    protected ?\PDO $pdo;
32

33
    /** The compiler associated with this connection */
34
    protected ?SQL\Compiler $compiler = null;
35

36
    /** The schema compiler associated with this connection */
37
    protected ?Schema\Compiler $schema_compiler = null;
38

39
    /** The DSN for this connection */
40
    protected ?string $dsn;
41

42
    /** Driver's name */
43
    protected ?string $driver;
44

45
    /** Schema instance */
46
    protected ?Schema $schema = null;
47

48
    /** Compiler options */
49
    protected array $compiler_options = [];
50

51
    /** Schema compiler options */
52
    protected array $schema_compiler_options = [];
53

54
    protected bool $throw_transaction_exceptions = false;
55

56
    public function __construct(
57
        string $dsn = null,
58
        string $username = null,
59
        string $password = null,
60
        string $driver = null,
61
        \PDO $pdo = null
62
    )
63
    {
64
        $this->dsn = $dsn;
65
        $this->username = $username;
66
        $this->password = $password;
67
        $this->driver = $driver;
68
        $this->pdo = $pdo;
69
    }
70

71
    public function __serialize(): array
72
    {
73
        return [
74
            'username' => $this->username,
75
            'password' => $this->password,
76
            'logQueries' => $this->log_queries,
77
            'options' => $this->options,
78
            'commands' => $this->commands,
79
            'dsn' => $this->dsn,
80
        ];
81
    }
82

83
    public function __unserialize(array $data): void
84
    {
85
        foreach ($data as $key => $value) {
86
            $this->{$key} = $value;
87
        }
88
    }
89

90
    public static function from_pdo(\PDO $pdo): static
91
    {
92
        return new static(null, null, null, null, $pdo);
93
    }
94

95
    /**
96
     * Enable or disable query logging
97
     *
98
     * @param bool $value (optional) Value
99
     */
100
    public function log_queries(bool $value = true): static
101
    {
102
        $this->log_queries = $value;
103

104
        return $this;
105
    }
106

107
    public function throw_transaction_exceptions(bool $value = true): static
108
    {
109
        $this->throw_transaction_exceptions = $value;
110

111
        return $this;
112
    }
113

114
    /**
115
     * Add an init command
116
     *
117
     * @param string $query SQL command
118
     * @param array $params (optional) Params
119
     */
120
    public function init_command(string $query, array $params = []): static
121
    {
122
        $this->commands[] = [
123
            'sql' => $query,
124
            'params' => $params,
125
        ];
126

127
        return $this;
128
    }
129

130
    /**
131
     * Set the username
132
     */
133
    public function username(string $username): static
134
    {
135
        $this->username = $username;
136

137
        return $this;
138
    }
139

140
    /**
141
     * Set the password
142
     */
143
    public function password(string $password): static
144
    {
145
        $this->password = $password;
146

147
        return $this;
148
    }
149

150
    /**
151
     * Set PDO connection options
152
     *
153
     * @param array $options PDO options
154
     */
155
    public function options(array $options): static
156
    {
157
        foreach ($options as $name => $value) {
158
            $this->option($name, $value);
159
        }
160

161
        return $this;
162
    }
163

164
    /**
165
     * Set a PDO connection option
166
     */
167
    public function option(mixed $name, mixed $value): static
168
    {
169
        $this->options[$name] = $value;
170

171
        return $this;
172
    }
173

174
    /**
175
     * Use persistent connections
176
     *
177
     * @param bool $value (optional) Value
178
     */
179
    public function persistent(bool $value = true): static
180
    {
181
        return $this->option(\PDO::ATTR_PERSISTENT, $value);
182
    }
183

184
    /**
185
     * Set date format
186
     *
187
     * @param string $format Date format
188
     */
189
    public function set_date_format(string $format): static
190
    {
191
        $this->compiler_options['dateFormat'] = $format;
192

193
        return $this;
194
    }
195

196
    /**
197
     * Set identifier wrapper
198
     *
199
     * @param string $wrapper Identifier wrapper
200
     */
201
    public function set_wrapper_format(string $wrapper): static
202
    {
203
        $this->compiler_options['wrapper'] = $wrapper;
204
        $this->schema_compiler_options['wrapper'] = $wrapper;
205

206
        return $this;
207
    }
208

209
    /**
210
     * Returns the DSN associated with this connection
211
     */
212
    public function get_dsn(): ?string
213
    {
214
        return $this->dsn;
215
    }
216

217
    /**
218
     * Returns the driver's name
219
     */
220
    public function get_driver(): string
221
    {
222
        if ($this->driver === null) {
223
            $this->driver = $this->get_pdo()->getAttribute(\PDO::ATTR_DRIVER_NAME);
224
        }
225

226
        return $this->driver;
227
    }
228

229
    /**
230
     * Returns the schema associated with this connection
231
     */
232
    public function get_schema(): Schema
233
    {
234
        if ($this->schema === null) {
235
            $this->schema = new Schema($this);
236
        }
237

238
        return $this->schema;
239
    }
240

241
    /**
242
     * Returns the PDO object associated with this connection
243
     */
244
    public function get_pdo(): \PDO
245
    {
246
        if ($this->pdo == null) {
247
            $this->pdo = new \PDO($this->get_dsn(), $this->username, $this->password, $this->options);
248

249
            foreach ($this->commands as $command) {
250
                $this->command($command['sql'], $command['params']);
251
            }
252
        }
253

254
        return $this->pdo;
255
    }
256

257
    /**
258
     * Returns an instance of the compiler associated with this connection
259
     */
260
    public function get_compiler(): SQL\Compiler
261
    {
262
        if ($this->compiler === null) {
263
            $this->compiler = match ($this->get_driver()) {
264
                'mysql' => new SQL\Compiler\MySQL(),
265
                'dblib', 'mssql', 'sqlsrv', 'sybase' => new SQL\Compiler\SQLServer(),
266
                'oci', 'oracle' => new SQL\Compiler\Oracle(),
267
                'firebird' => new SQL\Compiler\Firebird(),
268
                'db2', 'ibm', 'odbc' => new SQL\Compiler\DB2(),
269
                'nuodb' => new SQL\Compiler\NuoDB(),
270
                default => new SQL\Compiler(),
271
            };
272

273
            $this->compiler->set_options($this->compiler_options);
274
        }
275

276
        return $this->compiler;
277
    }
278

279
    /**
280
     * Returns an instance of the schema compiler associated with this connection
281
     */
282
    public function schema_compiler(): Schema\Compiler
283
    {
284
        if ($this->schema_compiler === null) {
285
            $this->schema_compiler = match ($this->get_driver()) {
286
                'mysql' => new Schema\Compiler\MySQL($this),
287
                'pgsql' => new Schema\Compiler\PostgreSQL($this),
288
                'dblib', 'mssql', 'sqlsrv', 'sybase' => new Schema\Compiler\SQLServer($this),
289
                'sqlite' => new Schema\Compiler\SQLite($this),
290
                'oci', 'oracle' => new Schema\Compiler\Oracle($this),
291
                default => throw new \Exception('Schema not supported yet'),
292
            };
293

294
            $this->schema_compiler->set_options($this->schema_compiler_options);
295
        }
296

297
        return $this->schema_compiler;
298
    }
299

300
    /**
301
     * Close the current connection by destroying the associated PDO object
302
     */
303
    public function disconnect(): void
304
    {
305
        $this->pdo = null;
306
    }
307

308
    /**
309
     * Returns the query log for this database.
310
     */
311
    public function get_log(): array
312
    {
313
        return $this->log;
314
    }
315

316
    /**
317
     * Execute a query
318
     *
319
     * @param string $sql SQL Query
320
     * @param array $params (optional) Query params
321
     */
322
    public function query(string $sql, array $params = [])
323
    {
324
        $prepared = $this->prepare($sql, $params);
325
        $this->execute($prepared);
326

327
        return new ResultSet($prepared['statement']);
328
    }
329

330
    /**
331
     * Execute a non-query SQL command
332
     *
333
     * @param string $sql SQL Command
334
     * @param array $params (optional) Command params
335
     */
336
    public function command(string $sql, array $params = [])
337
    {
338
        return $this->execute($this->prepare($sql, $params));
339
    }
340

341
    /**
342
     * Execute a query and return the number of affected rows
343
     *
344
     * @param string $sql SQL Query
345
     * @param array $params (optional) Query params
346
     */
347
    public function count(string $sql, array $params = [])
348
    {
349
        $prepared = $this->prepare($sql, $params);
350
        $this->execute($prepared);
351
        $result = $prepared['statement']->rowCount();
352
        $prepared['statement']->closeCursor();
353

354
        return $result;
355
    }
356

357
    /**
358
     * Execute a query and fetch the first column
359
     *
360
     * @param string $sql SQL Query
361
     * @param array $params (optional) Query params
362
     *
363
     * @return  mixed
364
     */
365
    public function column(string $sql, array $params = [])
366
    {
367
        $prepared = $this->prepare($sql, $params);
368
        $this->execute($prepared);
369
        $result = $prepared['statement']->fetchColumn();
370
        $prepared['statement']->closeCursor();
371

372
        return $result;
373
    }
374

375
    /**
376
     * Transaction
377
     *
378
     * @throws \PDOException
379
     */
380
    public function transaction(callable $callback, mixed $that = null, mixed $default = null): mixed
381
    {
382
        if ($that === null) {
383
            $that = $this;
384
        }
385

386
        $pdo = $this->get_pdo();
387

388
        if ($pdo->inTransaction()) {
389
            return $callback($that);
390
        }
391

392
        $result = $default;
393

394
        try {
395
            $pdo->beginTransaction();
396
            $result = $callback($that);
397
            $pdo->commit();
398
        } catch (\PDOException $exception) {
399
            $pdo->rollBack();
400
            if ($this->throw_transaction_exceptions) {
401
                throw $exception;
402
            }
403
        }
404

405
        return $result;
406
    }
407

408
    /**
409
     * Replace placeholders with parameters.
410
     *
411
     * @param string $query SQL query
412
     * @param array $params Query parameters
413
     */
414
    protected function replace_params(string $query, array $params): string
415
    {
416
        $compiler = $this->get_compiler();
417

418
        return \preg_replace_callback('/\?/', static function () use (&$params, $compiler) {
419
            $param = \array_shift($params);
420
            $param = \is_object($param) ? \get_class($param) : $param;
421

422
            if (\is_int($param) || \is_float($param)) {
423
                return $param;
424
            }
425

426
            if ($param === null) {
427
                return 'NULL';
428
            }
429

430
            if (\is_bool($param)) {
431
                return $param ? 'TRUE' : 'FALSE';
432
            }
433

434
            return $compiler->quote($param);
435
        }, $query);
436
    }
437

438
    /**
439
     * Prepares a query.
440
     *
441
     * @param string $query SQL query
442
     * @param array $params Query parameters
443
     */
444
    protected function prepare(string $query, array $params): array
445
    {
446
        try {
447
            $statement = $this->get_pdo()->prepare($query);
448
        } catch (\PDOException $e) {
449
            throw new \PDOException(
450
                $e->getMessage() . ' [ ' . $this->replace_params($query, $params) . ' ] ',
451
                (int)$e->getCode(),
452
                $e->getPrevious()
453
            );
454
        }
455

456
        return ['query' => $query, 'params' => $params, 'statement' => $statement];
457
    }
458

459
    /**
460
     * Executes a prepared query and returns TRUE on success or FALSE on failure.
461
     *
462
     * @param array $prepared Prepared query
463
     *
464
     * @return boolean
465
     */
466
    protected function execute(array $prepared)
467
    {
468
        if ($this->log_queries) {
469
            $start = \microtime(true);
470
            $log = [
471
                'query' => $this->replace_params($prepared['query'], $prepared['params']),
472
            ];
473
            $this->log[] = &$log;
474
        }
475

476
        try {
477
            if ($prepared['params']) {
478
                $this->bind_values($prepared['statement'], $prepared['params']);
479
            }
480
            $result = $prepared['statement']->execute();
481
        } catch (\PDOException $e) {
482
            throw new \PDOException(
483
                $e->getMessage() . ' [ ' . $this->replace_params($prepared['query'], $prepared['params']) . ' ] ',
484
                (int)$e->getCode(),
485
                $e->getPrevious(),
486
            );
487
        }
488

489
        if ($this->log_queries) {
490
            $log['time'] = \microtime(true) - $start;
491
        }
492

493
        return $result;
494
    }
495

496
    protected function bind_values(\PDOStatement $statement, array $values): void
497
    {
498
        foreach ($values as $key => $value) {
499
            $param = \PDO::PARAM_STR;
500

501
            if (\is_null($value)) {
502
                $param = \PDO::PARAM_NULL;
503
            } elseif (\is_int($value)) {
504
                $param = \PDO::PARAM_INT;
505
            } elseif (\is_bool($value)) {
506
                $param = \PDO::PARAM_BOOL;
507
            }
508

509
            $statement->bindValue($key + 1, $value, $param);
510
        }
511
    }
512

513
    /**
514
     * Implementation of Serializable::serialize
515
     */
516
    public function serialize(): string
517
    {
518
        return \serialize([
519
            'username' => $this->username,
520
            'password' => $this->password,
521
            'logQueries' => $this->log_queries,
522
            'options' => $this->options,
523
            'commands' => $this->commands,
524
            'dsn' => $this->dsn,
525
        ]);
526
    }
527

528
    /**
529
     * Implementation of Serializable::unserialize
530
     */
531
    public function unserialize(string $data): void
532
    {
533
        $object = \unserialize($data);
534

535
        foreach ($object as $key => $value) {
536
            $this->{$key} = $value;
537
        }
538
    }
539
}
540