DatabaseSource::sqlLoadCount()   A
last analyzed

Complexity

Conditions 2
Paths 2

Size

Total Lines 13
Code Lines 7

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
cc 2
eloc 7
nc 2
nop 0
dl 0
loc 13
rs 10
c 0
b 0
f 0
1
<?php
2
3
namespace Charcoal\Source;
4
5
use PDO;
6
use PDOException;
7
use InvalidArgumentException;
8
use RuntimeException;
9
use UnexpectedValueException;
10
11
// From 'charcoal-property'
12
use Charcoal\Property\PropertyField;
13
14
// From 'charcoal-core'
15
use Charcoal\Model\ModelInterface;
16
use Charcoal\Source\AbstractSource;
17
use Charcoal\Source\DatabaseSourceConfig;
18
use Charcoal\Source\DatabaseSourceInterface;
19
use Charcoal\Source\Database\DatabaseFilter;
20
use Charcoal\Source\Database\DatabaseOrder;
21
use Charcoal\Source\Database\DatabasePagination;
22
use Charcoal\Source\Expression;
23
24
/**
25
 * Database Source Handler, through PDO.
26
 */
27
class DatabaseSource extends AbstractSource implements
28
    DatabaseSourceInterface
29
{
30
    const DEFAULT_DB_HOSTNAME = 'localhost';
31
32
    const DEFAULT_TABLE_ALIAS = 'objTable';
33
34
    const MYSQL_DRIVER_NAME   = 'mysql';
35
    const SQLITE_DRIVER_NAME  = 'sqlite';
36
37
    /**
38
     * The database connector.
39
     *
40
     * @var PDO
41
     */
42
    private $pdo;
43
44
    /**
45
     * The {@see self::$model}'s table name.
46
     *
47
     * @var string
48
     */
49
    private $table;
50
51
    /**
52
     * Create a new database handler.
53
     *
54
     * @param array $data Class dependencies.
55
     */
56
    public function __construct(array $data)
57
    {
58
        $this->pdo = $data['pdo'];
59
60
        parent::__construct($data);
61
    }
62
63
    /**
64
     * Retrieve the database connector.
65
     *
66
     * @throws RuntimeException If the datahase was not set.
67
     * @return PDO
68
     */
69
    public function db()
70
    {
71
        if ($this->pdo === null) {
72
            throw new RuntimeException(
73
                'Database Connector was not set.'
74
            );
75
        }
76
        return $this->pdo;
77
    }
78
79
    /**
80
     * Set the database's table to use.
81
     *
82
     * @param  string $table The source table.
83
     * @throws InvalidArgumentException If argument is not a string or alphanumeric/underscore.
84
     * @return self
85
     */
86
    public function setTable($table)
87
    {
88
        if (!is_string($table)) {
0 ignored issues
show
introduced by
The condition is_string($table) is always true.
Loading history...
89
            throw new InvalidArgumentException(sprintf(
90
                'DatabaseSource::setTable() expects a string as table. (%s given). [%s]',
91
                gettype($table),
92
                get_class($this->model())
93
            ));
94
        }
95
96
        /**
97
         * For security reason, only alphanumeric characters (+ underscores)
98
         * are valid table names; Although SQL can support more,
99
         * there's really no reason to.
100
         */
101
        if (!preg_match('/[A-Za-z0-9_]/', $table)) {
102
            throw new InvalidArgumentException(sprintf(
103
                'Table name "%s" is invalid: must be alphanumeric / underscore.',
104
                $table
105
            ));
106
        }
107
108
        $this->table = $table;
109
        return $this;
110
    }
111
112
    /**
113
     * Determine if a table is assigned.
114
     *
115
     * @return boolean
116
     */
117
    public function hasTable()
118
    {
119
        return !empty($this->table);
120
    }
121
122
    /**
123
     * Get the database's current table.
124
     *
125
     * @throws RuntimeException If the table was not set.
126
     * @return string
127
     */
128
    public function table()
129
    {
130
        if ($this->table === null) {
131
            throw new RuntimeException(
132
                'Table was not set.'
133
            );
134
        }
135
        return $this->table;
136
    }
137
138
    /**
139
     * Create a table from a model's metadata.
140
     *
141
     * @return boolean TRUE if the table was created, otherwise FALSE.
142
     */
143
    public function createTable()
144
    {
145
        if ($this->tableExists() === true) {
146
            return true;
147
        }
148
149
        $dbh      = $this->db();
150
        $driver   = $dbh->getAttribute(PDO::ATTR_DRIVER_NAME);
151
        $model    = $this->model();
152
        $metadata = $model->metadata();
153
154
        $table   = $this->table();
155
        $fields  = $this->getModelFields($model);
156
        $columns = [];
157
        foreach ($fields as $field) {
158
            $columns[] = $field->sql();
159
        }
160
161
        $query  = 'CREATE TABLE  `'.$table.'` ('."\n";
162
        $query .= implode(',', $columns);
163
164
        $key = $model->key();
165
        if ($key) {
166
            $query .= ', PRIMARY KEY (`'.$key.'`) '."\n";
167
        }
168
169
        /** @todo Add indexes for all defined list constraints (yea... tough job...) */
170
        if ($driver === self::MYSQL_DRIVER_NAME) {
171
            $engine = 'InnoDB';
172
            $query .= ') ENGINE='.$engine.' DEFAULT CHARSET=utf8 COMMENT="'.addslashes($metadata['name']).'";';
173
        } else {
174
            $query .= ');';
175
        }
176
177
        $this->logger->debug($query);
178
        $dbh->query($query);
179
180
        $this->setTableExists();
181
182
        return true;
183
    }
184
185
    /**
186
     * Alter an existing table to match the model's metadata.
187
     *
188
     * @return boolean TRUE if the table was altered, otherwise FALSE.
189
     */
190
    public function alterTable()
191
    {
192
        if ($this->tableExists() === false) {
193
            return false;
194
        }
195
196
        $dbh    = $this->db();
197
        $table  = $this->table();
198
        $fields = $this->getModelFields($this->model());
199
        $cols   = $this->tableStructure();
200
        foreach ($fields as $field) {
201
            $ident = $field->ident();
202
203
            if (!array_key_exists($ident, $cols)) {
204
                // The key does not exist at all.
205
                $query = 'ALTER TABLE `'.$table.'` ADD '.$field->sql();
206
                $this->logger->debug($query);
207
                $dbh->query($query);
208
            } else {
209
                // The key exists. Validate.
210
                $col   = $cols[$ident];
211
                $alter = true;
212
                if (strtolower($col['Type']) !== strtolower($field->sqlType())) {
213
                    $alter = true;
214
                }
215
216
                if ((strtolower($col['Null']) === 'no') && !$field->allowNull()) {
217
                    $alter = true;
218
                }
219
220
                if ((strtolower($col['Null']) !== 'no') && $field->allowNull()) {
221
                    $alter = true;
222
                }
223
224
                if ($col['Default'] !== $field->defaultVal()) {
225
                    $alter = true;
226
                }
227
228
                if ($alter === true) {
229
                    $query = 'ALTER TABLE `'.$table.'` CHANGE `'.$ident.'` '.$field->sql();
230
                    $this->logger->debug($query);
231
                    $dbh->query($query);
232
                }
233
            }
234
        }
235
236
        return true;
237
    }
238
239
    /**
240
     * Determine if the source table exists.
241
     *
242
     * @return boolean TRUE if the table exists, otherwise FALSE.
243
     */
244
    public function tableExists()
245
    {
246
        $dbh    = $this->db();
247
        $table  = $this->table();
248
249
        if (isset($dbh->tableExists, $dbh->tableExists[$table])) {
0 ignored issues
show
Bug introduced by
The property tableExists does not seem to exist on PDO.
Loading history...
250
            return $dbh->tableExists[$table];
251
        }
252
253
        $exists = $this->performTableExists();
254
        $this->setTableExists($exists);
255
256
        return $exists;
257
    }
258
259
    /**
260
     * Perform a source table exists operation.
261
     *
262
     * @return boolean TRUE if the table exists, otherwise FALSE.
263
     */
264
    protected function performTableExists()
265
    {
266
        $dbh    = $this->db();
267
        $table  = $this->table();
268
269
        $driver = $dbh->getAttribute(PDO::ATTR_DRIVER_NAME);
270
        if ($driver === self::SQLITE_DRIVER_NAME) {
271
            $query = sprintf('SELECT name FROM sqlite_master WHERE type = "table" AND name = "%s";', $table);
272
        } else {
273
            $query = sprintf('SHOW TABLES LIKE "%s"', $table);
274
        }
275
276
        $this->logger->debug($query);
277
        $sth    = $dbh->query($query);
278
        $exists = $sth->fetchColumn(0);
279
280
        return (bool)$exists;
281
    }
282
283
    /**
284
     * Store a reminder whether the source's database table exists.
285
     *
286
     * @param  boolean $exists Whether the table exists or not.
287
     * @return void
288
     */
289
    protected function setTableExists($exists = true)
290
    {
291
        $dbh   = $this->db();
292
        $table = $this->table();
293
294
        if (!isset($dbh->tableExists)) {
295
            $dbh->tableExists = [];
0 ignored issues
show
Bug introduced by
The property tableExists does not seem to exist on PDO.
Loading history...
296
        }
297
298
        $dbh->tableExists[$table] = $exists;
299
    }
300
301
    /**
302
     * Get the table columns information.
303
     *
304
     * @return array An associative array.
305
     */
306
    public function tableStructure()
307
    {
308
        $dbh    = $this->db();
309
        $table  = $this->table();
310
        $driver = $dbh->getAttribute(PDO::ATTR_DRIVER_NAME);
311
        if ($driver === self::SQLITE_DRIVER_NAME) {
312
            $query = sprintf('PRAGMA table_info("%s") ', $table);
313
        } else {
314
            $query = sprintf('SHOW COLUMNS FROM `%s`', $table);
315
        }
316
317
        $this->logger->debug($query);
318
        $sth = $dbh->query($query);
319
320
        $cols = $sth->fetchAll((PDO::FETCH_GROUP | PDO::FETCH_UNIQUE | PDO::FETCH_ASSOC));
321
        if ($driver === self::SQLITE_DRIVER_NAME) {
322
            $struct = [];
323
            foreach ($cols as $col) {
324
                // Normalize SQLite's result (PRAGMA) with mysql's (SHOW COLUMNS)
325
                $struct[$col['name']] = [
326
                    'Type'      => $col['type'],
327
                    'Null'      => !!$col['notnull'] ? 'NO' : 'YES',
328
                    'Default'   => $col['dflt_value'],
329
                    'Key'       => !!$col['pk'] ? 'PRI' : '',
330
                    'Extra'     => ''
331
                ];
332
            }
333
            return $struct;
334
        } else {
335
            return $cols;
336
        }
337
    }
338
339
    /**
340
     * Determine if the source table is empty or not.
341
     *
342
     * @return boolean TRUE if the table has no data, otherwise FALSE.
343
     */
344
    public function tableIsEmpty()
345
    {
346
        $table = $this->table();
347
        $query = sprintf('SELECT NULL FROM `%s` LIMIT 1', $table);
348
        $this->logger->debug($query);
349
        $sth = $this->db()->query($query);
350
        return ($sth->rowCount() === 0);
351
    }
352
353
    /**
354
     * Retrieve all fields from a model.
355
     *
356
     * @todo   Move this method in StorableTrait or AbstractModel
357
     * @param  ModelInterface $model      The model to get fields from.
358
     * @param  array|null     $properties Optional list of properties to get.
359
     *     If NULL, retrieve all (from metadata).
360
     * @return PropertyField[]
361
     */
362
    private function getModelFields(ModelInterface $model, $properties = null)
363
    {
364
        if ($properties === null) {
365
            // No custom properties; use all (from model metadata)
366
            $properties = array_keys($model->metadata()->properties());
367
        } else {
368
            // Ensure the key is always in the required fields.
369
            $properties = array_unique(array_merge([ $model->key() ], $properties));
370
        }
371
372
        $fields = [];
373
        foreach ($properties as $propertyIdent) {
374
            $prop = $model->property($propertyIdent);
375
            if (!$prop || !$prop['active'] || !$prop['storable']) {
376
                continue;
377
            }
378
379
            $val = $model->propertyValue($propertyIdent);
0 ignored issues
show
Bug introduced by
The method propertyValue() does not exist on Charcoal\Model\ModelInterface. Did you maybe mean property()? ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-call  annotation

379
            /** @scrutinizer ignore-call */ 
380
            $val = $model->propertyValue($propertyIdent);

This check looks for calls to methods that do not seem to exist on a given type. It looks for the method on the type itself as well as in inherited classes or implemented interfaces.

This is most likely a typographical error or the method has been renamed.

Loading history...
380
            foreach ($prop->fields($val) as $fieldIdent => $field) {
381
                $fields[$field->ident()] = $field;
382
            }
383
        }
384
385
        return $fields;
386
    }
387
388
    /**
389
     * Load item by the primary column.
390
     *
391
     * @param  mixed             $ident Ident can be any scalar value.
392
     * @param  StorableInterface $item  Optional item to load into.
393
     * @return StorableInterface
394
     */
395
    public function loadItem($ident, StorableInterface $item = null)
396
    {
397
        $key = $this->model()->key();
398
399
        return $this->loadItemFromKey($key, $ident, $item);
400
    }
401
402
    /**
403
     * Load item by the given column.
404
     *
405
     * @param  string                 $key   Column name.
406
     * @param  mixed                  $ident Value of said column.
407
     * @param  StorableInterface|null $item  Optional. Item (storable object) to load into.
408
     * @throws \Exception If the query fails.
409
     * @return StorableInterface
410
     */
411
    public function loadItemFromKey($key, $ident, StorableInterface $item = null)
412
    {
413
        if ($item !== null) {
414
            $this->setModel($item);
415
        } else {
416
            $class = get_class($this->model());
417
            $item  = new $class;
418
        }
419
420
        $key = preg_replace('/[^\w-]+/', '', $key);
421
        // Missing parameters
422
        if (!$key || !$ident) {
423
            return $item;
424
        }
425
426
        $table = $this->table();
427
        $query = sprintf('
428
            SELECT
429
                *
430
            FROM
431
               `%s`
432
            WHERE
433
               `%s` = :ident
434
            LIMIT
435
               1', $table, $key);
436
437
        $binds = [
438
            'ident' => $ident
439
        ];
440
441
        return $this->loadItemFromQuery($query, $binds, $item);
442
    }
443
444
    /**
445
     * Load item by the given query statement.
446
     *
447
     * @param  string            $query The SQL SELECT statement.
448
     * @param  array             $binds Optional. The query parameters.
449
     * @param  StorableInterface $item  Optional. Item (storable object) to load into.
450
     * @throws PDOException If there is a query error.
451
     * @return StorableInterface
452
     */
453
    public function loadItemFromQuery($query, array $binds = [], StorableInterface $item = null)
454
    {
455
        if ($item !== null) {
456
            $this->setModel($item);
457
        } else {
458
            $class = get_class($this->model());
459
            $item = new $class;
460
        }
461
462
        // Missing parameters
463
        if (!$query) {
464
            return $item;
465
        }
466
467
        $sth = $this->dbQuery($query, $binds);
468
        if ($sth === false) {
469
            throw new PDOException('Could not load item.');
470
        }
471
472
        $data = $sth->fetch(PDO::FETCH_ASSOC);
473
        if ($data) {
474
            $item->setFlatData($data);
0 ignored issues
show
Bug introduced by
The method setFlatData() does not exist on Charcoal\Source\StorableInterface. It seems like you code against a sub-type of Charcoal\Source\StorableInterface such as Charcoal\Model\ModelInterface. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-call  annotation

474
            $item->/** @scrutinizer ignore-call */ 
475
                   setFlatData($data);
Loading history...
475
        }
476
477
        return $item;
478
    }
479
480
    /**
481
     * Load items for the given model.
482
     *
483
     * @param  StorableInterface|null $item Optional model.
484
     * @return StorableInterface[]
485
     */
486
    public function loadItems(StorableInterface $item = null)
487
    {
488
        if ($item !== null) {
489
            $this->setModel($item);
490
        }
491
492
        $query = $this->sqlLoad();
493
        return $this->loadItemsFromQuery($query, [], $item);
494
    }
495
496
    /**
497
     * Load items for the given query statement.
498
     *
499
     * @param  string                 $query The SQL SELECT statement.
500
     * @param  array                  $binds This has to be done.
501
     * @param  StorableInterface|null $item  Model Item.
502
     * @return StorableInterface[]
503
     */
504
    public function loadItemsFromQuery($query, array $binds = [], StorableInterface $item = null)
505
    {
506
        if ($item !== null) {
507
            $this->setModel($item);
508
        }
509
510
        $items = [];
511
512
        $model = $this->model();
513
        $dbh   = $this->db();
514
515
        $this->logger->debug($query);
516
        $sth = $dbh->prepare($query);
517
518
        // @todo Binds
519
        if (!empty($binds)) {
520
            unset($binds);
521
        }
522
523
        $sth->execute();
524
        $sth->setFetchMode(PDO::FETCH_ASSOC);
525
526
        $className = get_class($model);
527
        while ($objData = $sth->fetch()) {
528
            $obj = new $className;
529
            $obj->setFlatData($objData);
530
            $items[] = $obj;
531
        }
532
533
        return $items;
534
    }
535
536
    /**
537
     * Save an item (create a new row) in storage.
538
     *
539
     * @param  StorableInterface $item The object to save.
540
     * @throws PDOException If a database error occurs.
541
     * @return mixed The created item ID, otherwise FALSE.
542
     */
543
    public function saveItem(StorableInterface $item)
544
    {
545
        if ($this->tableExists() === false) {
546
            /** @todo Optionnally turn off for some models */
547
            $this->createTable();
548
        }
549
550
        if ($item !== null) {
551
            $this->setModel($item);
552
        }
553
        $model  = $this->model();
554
        $table  = $this->table();
555
        $struct = array_keys($this->tableStructure());
556
        $fields = $this->getModelFields($model);
557
558
        $keys   = [];
559
        $values = [];
560
        $binds  = [];
561
        $types  = [];
562
        foreach ($fields as $field) {
563
            $key = $field->ident();
564
            if (in_array($key, $struct)) {
565
                $keys[]      = '`'.$key.'`';
566
                $values[]    = ':'.$key.'';
567
                $binds[$key] = $field->val();
568
                $types[$key] = $field->sqlPdoType();
569
            }
570
        }
571
572
        $query = '
573
            INSERT
574
                INTO
575
            `'.$table.'`
576
                ('.implode(', ', $keys).')
577
            VALUES
578
                ('.implode(', ', $values).')';
579
580
        $result = $this->dbQuery($query, $binds, $types);
581
582
        if ($result === false) {
583
            throw new PDOException('Could not save item.');
584
        } else {
585
            if ($model->id()) {
586
                return $model->id();
587
            } else {
588
                return $this->db()->lastInsertId();
589
            }
590
        }
591
    }
592
593
    /**
594
     * Update an item in storage.
595
     *
596
     * @param  StorableInterface $item       The object to update.
597
     * @param  array             $properties The list of properties to update, if not all.
598
     * @return boolean TRUE if the item was updated, otherwise FALSE.
599
     */
600
    public function updateItem(StorableInterface $item, array $properties = null)
601
    {
602
        if ($item !== null) {
603
            $this->setModel($item);
604
        }
605
        $model  = $this->model();
606
        $table  = $this->table();
607
        $struct = array_keys($this->tableStructure());
608
        $fields = $this->getModelFields($model, $properties);
609
610
        $updates = [];
611
        $binds   = [];
612
        $types   = [];
613
        foreach ($fields as $field) {
614
            $key = $field->ident();
615
            if (in_array($key, $struct)) {
616
                if ($key !== $model->key()) {
617
                    $param = ':'.$key;
618
                    $updates[] = '`'.$key.'` = '.$param;
619
                }
620
                $binds[$key] = $field->val();
621
                $types[$key] = $field->sqlPdoType();
622
            } else {
623
                $this->logger->debug(
624
                    sprintf('Field "%s" not in table structure', $key)
625
                );
626
            }
627
        }
628
        if (empty($updates)) {
629
            $this->logger->warning(
630
                'Could not update items. No valid fields were set / available in database table.',
631
                [
632
                    'properties' => $properties,
633
                    'structure'  => $struct
634
                ]
635
            );
636
            return false;
637
        }
638
639
        $binds[$model->key()] = $model->id();
640
        $types[$model->key()] = PDO::PARAM_STR;
641
642
        $query = '
643
            UPDATE
644
                `'.$table.'`
645
            SET
646
                '.implode(", \n\t", $updates).'
647
            WHERE
648
                `'.$model->key().'`=:'.$model->key().'';
649
650
        $driver = $this->db()->getAttribute(PDO::ATTR_DRIVER_NAME);
651
        if ($driver == self::MYSQL_DRIVER_NAME) {
652
            $query .= "\n".'LIMIT 1';
653
        }
654
655
        $result = $this->dbQuery($query, $binds, $types);
656
657
        if ($result === false) {
658
            return false;
659
        } else {
660
            return true;
661
        }
662
    }
663
664
    /**
665
     * Delete an item from storage.
666
     *
667
     * @param  StorableInterface $item Optional item to delete. If none, the current model object will be used.
668
     * @throws UnexpectedValueException If the item does not have an ID.
669
     * @return boolean TRUE if the item was deleted, otherwise FALSE.
670
     */
671
    public function deleteItem(StorableInterface $item = null)
672
    {
673
        if ($item !== null) {
674
            $this->setModel($item);
675
        }
676
677
        $model = $this->model();
678
679
        if (!$model->id()) {
680
            throw new UnexpectedValueException(
681
                sprintf('Can not delete "%s" item. No ID.', get_class($model))
682
            );
683
        }
684
685
        $key   = $model->key();
686
        $table = $this->table();
687
        $query = '
688
            DELETE FROM
689
                `'.$table.'`
690
            WHERE
691
                `'.$key.'` = :id';
692
693
        $driver = $this->db()->getAttribute(PDO::ATTR_DRIVER_NAME);
694
        if ($driver == self::MYSQL_DRIVER_NAME) {
695
            $query .= "\n".'LIMIT 1';
696
        }
697
698
        $binds = [
699
            'id' => $model->id()
700
        ];
701
702
        $result = $this->dbQuery($query, $binds);
703
704
        if ($result === false) {
705
            return false;
706
        } else {
707
            return true;
708
        }
709
    }
710
711
    /**
712
     * Execute a SQL query, with PDO, and returns the PDOStatement.
713
     *
714
     * If the query fails, this method will return false.
715
     *
716
     * @param  string $query The SQL query to executed.
717
     * @param  array  $binds Optional. Query parameter binds.
718
     * @param  array  $types Optional. Types of parameter bindings.
719
     * @return \PDOStatement|false The PDOStatement, otherwise FALSE.
720
     */
721
    public function dbQuery($query, array $binds = [], array $types = [])
722
    {
723
        $this->logger->debug($query, $binds);
724
725
        $sth = $this->dbPrepare($query, $binds, $types);
726
        if ($sth === false) {
0 ignored issues
show
introduced by
The condition $sth === false is always false.
Loading history...
727
            return false;
728
        }
729
730
        $result = $sth->execute();
731
        if ($result === false) {
732
            return false;
733
        }
734
735
        return $sth;
736
    }
737
738
    /**
739
     * Prepare an SQL query, with PDO, and return the PDOStatement.
740
     *
741
     * If the preparation fails, this method will return false.
742
     *
743
     * @param  string $query The SQL query to executed.
744
     * @param  array  $binds Optional. Query parameter binds.
745
     * @param  array  $types Optional. Types of parameter bindings.
746
     * @return \PDOStatement|false The PDOStatement, otherwise FALSE.
747
     */
748
    public function dbPrepare($query, array $binds = [], array $types = [])
749
    {
750
        $sth = $this->db()->prepare($query);
751
        if (!$sth) {
752
            return false;
753
        }
754
755
        if (!empty($binds)) {
756
            foreach ($binds as $key => $val) {
757
                if ($binds[$key] === null) {
758
                    $types[$key] = PDO::PARAM_NULL;
759
                } elseif (!is_scalar($binds[$key])) {
760
                    $binds[$key] = json_encode($binds[$key]);
761
                }
762
                $type  = (isset($types[$key]) ? $types[$key] : PDO::PARAM_STR);
763
                $param = ':'.$key;
764
                $sth->bindParam($param, $binds[$key], $type);
765
            }
766
        }
767
768
        return $sth;
0 ignored issues
show
Bug Best Practice introduced by
The expression return $sth also could return the type true which is incompatible with the documented return type PDOStatement|false.
Loading history...
769
    }
770
771
    /**
772
     * Compile the SELECT statement for fetching one or more objects.
773
     *
774
     * @throws UnexpectedValueException If the source does not have a table defined.
775
     * @return string
776
     */
777
    public function sqlLoad()
778
    {
779
        if (!$this->hasTable()) {
780
            throw new UnexpectedValueException(
781
                'Can not get SQL SELECT clause. No table defined.'
782
            );
783
        }
784
785
        $selects = $this->sqlSelect();
786
        $tables  = $this->sqlFrom();
787
        $filters = $this->sqlFilters();
788
        $orders  = $this->sqlOrders();
789
        $limits  = $this->sqlPagination();
790
791
        $query = 'SELECT '.$selects.' FROM '.$tables.$filters.$orders.$limits;
792
        return $query;
793
    }
794
795
    /**
796
     * Compile the SELECT statement for fetching the number of objects.
797
     *
798
     * @throws UnexpectedValueException If the source does not have a table defined.
799
     * @return string
800
     */
801
    public function sqlLoadCount()
802
    {
803
        if (!$this->hasTable()) {
804
            throw new UnexpectedValueException(
805
                'Can not get SQL count. No table defined.'
806
            );
807
        }
808
809
        $tables  = $this->sqlFrom();
810
        $filters = $this->sqlFilters();
811
812
        $query = 'SELECT COUNT(*) FROM '.$tables.$filters;
813
        return $query;
814
    }
815
816
    /**
817
     * Compile the SELECT clause.
818
     *
819
     * @throws UnexpectedValueException If the clause has no selectable fields.
820
     * @return string
821
     */
822
    public function sqlSelect()
823
    {
824
        $properties = $this->properties();
825
        if (empty($properties)) {
826
            return self::DEFAULT_TABLE_ALIAS.'.*';
827
        }
828
829
        $parts = [];
830
        foreach ($properties as $key) {
831
            $parts[] = Expression::quoteIdentifier($key, self::DEFAULT_TABLE_ALIAS);
832
        }
833
834
        if (empty($parts)) {
835
            throw new UnexpectedValueException(
836
                'Can not get SQL SELECT clause. No valid properties.'
837
            );
838
        }
839
840
        $clause = implode(', ', $parts);
841
842
        return $clause;
843
    }
844
845
    /**
846
     * Compile the FROM clause.
847
     *
848
     * @throws UnexpectedValueException If the source does not have a table defined.
849
     * @return string
850
     */
851
    public function sqlFrom()
852
    {
853
        if (!$this->hasTable()) {
854
            throw new UnexpectedValueException(
855
                'Can not get SQL FROM clause. No table defined.'
856
            );
857
        }
858
859
        $table = $this->table();
860
        return '`'.$table.'` AS `'.self::DEFAULT_TABLE_ALIAS.'`';
861
    }
862
863
    /**
864
     * Compile the WHERE clause.
865
     *
866
     * @todo   [2016-02-19] Use bindings for filters value
867
     * @return string
868
     */
869
    public function sqlFilters()
870
    {
871
        if (!$this->hasFilters()) {
872
            return '';
873
        }
874
875
        $criteria = $this->createFilter([
876
            'filters' => $this->filters()
877
        ]);
878
879
        $sql = $criteria->sql();
880
        if (strlen($sql) > 0) {
881
            $sql = ' WHERE '.$sql;
882
        }
883
884
        return $sql;
885
    }
886
887
    /**
888
     * Compile the ORDER BY clause.
889
     *
890
     * @return string
891
     */
892
    public function sqlOrders()
893
    {
894
        if (!$this->hasOrders()) {
895
            return '';
896
        }
897
898
        $parts = [];
899
        foreach ($this->orders() as $order) {
900
            if (!$order instanceof DatabaseOrder) {
901
                $order = $this->createOrder($order->data());
0 ignored issues
show
Bug introduced by
The method data() does not exist on Charcoal\Source\OrderInterface. Since it exists in all sub-types, consider adding an abstract or default implementation to Charcoal\Source\OrderInterface. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-call  annotation

901
                $order = $this->createOrder($order->/** @scrutinizer ignore-call */ data());
Loading history...
902
            }
903
904
            $sql = $order->sql();
905
            if (strlen($sql) > 0) {
906
                $parts[] = $sql;
907
            }
908
        }
909
910
        if (empty($parts)) {
911
            return '';
912
        }
913
914
        return ' ORDER BY '.implode(', ', $parts);
915
    }
916
917
    /**
918
     * Compile the LIMIT clause.
919
     *
920
     * @return string
921
     */
922
    public function sqlPagination()
923
    {
924
        $pager = $this->pagination();
925
        if (!$pager instanceof DatabasePagination) {
926
            $pager = $this->createPagination($pager->data());
0 ignored issues
show
Bug introduced by
The method data() does not exist on Charcoal\Source\PaginationInterface. Since it exists in all sub-types, consider adding an abstract or default implementation to Charcoal\Source\PaginationInterface. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-call  annotation

926
            $pager = $this->createPagination($pager->/** @scrutinizer ignore-call */ data());
Loading history...
927
        }
928
929
        $sql = $pager->sql();
930
        if (strlen($sql) > 0) {
931
            $sql = ' '.$sql;
932
        }
933
934
        return $sql;
935
    }
936
937
    /**
938
     * Create a new filter expression.
939
     *
940
     * @param  array $data Optional expression data.
941
     * @return DatabaseFilter
942
     */
943
    protected function createFilter(array $data = null)
944
    {
945
        $filter = new DatabaseFilter();
946
        if ($data !== null) {
947
            $filter->setData($data);
948
        }
949
        return $filter;
950
    }
951
952
    /**
953
     * Create a new order expression.
954
     *
955
     * @param  array $data Optional expression data.
956
     * @return DatabaseOrder
957
     */
958
    protected function createOrder(array $data = null)
959
    {
960
        $order = new DatabaseOrder();
961
        if ($data !== null) {
962
            $order->setData($data);
963
        }
964
        return $order;
965
    }
966
967
    /**
968
     * Create a new pagination clause.
969
     *
970
     * @param  array $data Optional clause data.
971
     * @return DatabasePagination
972
     */
973
    protected function createPagination(array $data = null)
974
    {
975
        $pagination = new DatabasePagination();
976
        if ($data !== null) {
977
            $pagination->setData($data);
978
        }
979
        return $pagination;
980
    }
981
982
    /**
983
     * Create a new database source config.
984
     *
985
     * @see    \Charcoal\Config\ConfigurableTrait
986
     * @param  array $data Optional data.
987
     * @return DatabaseSourceConfig
988
     */
989
    public function createConfig(array $data = null)
990
    {
991
        $config = new DatabaseSourceConfig($data);
992
        return $config;
993
    }
994
}
995