PostgreSQLDatabase   F
last analyzed

Complexity

Total Complexity 95

Size/Duplication

Total Lines 803
Duplicated Lines 0 %

Importance

Changes 5
Bugs 0 Features 0
Metric Value
eloc 279
c 5
b 0
f 0
dl 0
loc 803
rs 2
wmc 95

35 Methods

Rating   Name   Duplication   Size   Complexity  
A getDatabaseServer() 0 3 1
A supportsTimezoneOverride() 0 3 1
A allow_query_master_postgres() 0 3 1
A currentSchema() 0 3 1
A supportsCollations() 0 3 1
A model_schema_as_database() 0 3 1
A supportsTransactions() 0 3 1
B comparisonClause() 0 16 7
A datetimeIntervalClause() 0 11 3
B connect() 0 50 9
A getSelectedDatabase() 0 6 2
A supportsExtensions() 0 10 4
A transactionEnd() 0 6 2
A setSchema() 0 16 4
A selectTimezone() 0 6 2
A dropSelectedDatabase() 0 22 5
A now() 0 3 1
A schemaToDatabaseName() 0 7 2
A formattedDatetimeClause() 0 30 6
A selectDatabase() 0 28 5
A connectMaster() 0 5 1
A clearTable() 0 3 1
A random() 0 3 1
A datetimeDifferenceClause() 0 15 5
A setSchemaSearchPath() 0 7 2
A connectDefault() 0 5 1
A databaseToSchemaName() 0 7 2
A transactionDepth() 0 3 1
A transactionSavepoint() 0 3 1
A transactionRollback() 0 22 4
A default_fts_search_method() 0 3 1
A transactionStart() 0 16 4
A search_language() 0 3 1
A default_fts_cluster_method() 0 3 1
C searchEngine() 0 134 10

How to fix   Complexity   

Complex Class

Complex classes like PostgreSQLDatabase often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.

Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.

While breaking up the class, it is a good idea to analyze how other classes use PostgreSQLDatabase, and based on these observations, apply Extract Interface, too.

1
<?php
2
3
namespace SilverStripe\PostgreSQL;
4
5
use SilverStripe\Core\Config\Configurable;
6
use SilverStripe\Core\Injector\Injector;
7
use SilverStripe\ORM\DB;
8
use SilverStripe\ORM\DataObject;
9
use SilverStripe\ORM\ArrayList;
10
use SilverStripe\ORM\Connect\Database;
11
use SilverStripe\ORM\PaginatedList;
12
use ErrorException;
13
use Exception;
14
15
/**
16
 * PostgreSQL connector class.
17
 *
18
 * @package sapphire
19
 * @subpackage model
20
 */
21
class PostgreSQLDatabase extends Database
22
{
23
    use Configurable;
24
25
    /**
26
     * Database schema manager object
27
     *
28
     * @var PostgreSQLSchemaManager
29
     */
30
    protected $schemaManager;
31
32
    /**
33
     * The currently selected database schema name.
34
     *
35
     * @var string
36
     */
37
    protected $schema;
38
39
    /**
40
     * @var bool
41
     */
42
    protected $transactionNesting = 0;
43
44
    /**
45
     * Toggle if transactions are supported. Defaults to true.
46
     *
47
     * @var bool
48
     */
49
    protected $supportsTransactions = true;
50
51
    /**
52
     * Determines whether to check a database exists on the host by
53
     * querying the 'postgres' database and running createDatabase.
54
     *
55
     * Some locked down systems prevent access to the 'postgres' table in
56
     * which case you need to set this to false.
57
     *
58
     * If allow_query_master_postgres is false, and model_schema_as_database is also false,
59
     * then attempts to create or check databases beyond the initial connection will
60
     * result in a runtime error.
61
     *
62
     * @config
63
     * @var bool
64
     */
65
    private static $allow_query_master_postgres = true;
66
67
    /**
68
     * For instances where multiple databases are used beyond the initial connection
69
     * you may set this option to true to force database switches to switch schemas
70
     * instead of using databases. This may be useful if the database user does not
71
     * have cross-database permissions, and in cases where multiple databases are used
72
     * (such as in running test cases).
73
     *
74
     * If this is true then the database will only be set during the initial connection,
75
     * and attempts to change to this database will use the 'public' schema instead
76
     *
77
     * If this is false then errors may be generated during some cross database operations.
78
     */
79
    private static $model_schema_as_database = true;
80
81
    /**
82
     * Override the language that tsearch uses.  By default it is 'english, but
83
     * could be any of the supported languages that can be found in the
84
     * pg_catalog.pg_ts_config table.
85
     */
86
    private static $search_language = 'english';
87
88
    /*
89
     * Describe how T-search will work.
90
     * You can use either GiST or GIN, and '@@' (gist) or '@@@' (gin)
91
     * Combinations of these two will also work, so you'll need to pick
92
     * one which works best for you
93
     */
94
    private static $default_fts_cluster_method = 'GIN';
95
96
    /*
97
     * Describe how T-search will work.
98
     * You can use either GiST or GIN, and '@@' (gist) or '@@@' (gin)
99
     * Combinations of these two will also work, so you'll need to pick
100
     * one which works best for you
101
     */
102
    private static $default_fts_search_method = '@@@';
103
104
    const MASTER_DATABASE = 'postgres';
105
106
    const MASTER_SCHEMA = 'public';
107
108
    /**
109
     * Full text cluster method. (e.g. GIN or GiST)
110
     *
111
     * @return string
112
     */
113
    public static function default_fts_cluster_method()
114
    {
115
        return static::config()->default_fts_cluster_method;
116
    }
117
118
    /**
119
     * Full text search method.
120
     *
121
     * @return string
122
     */
123
    public static function default_fts_search_method()
124
    {
125
        return static::config()->default_fts_search_method;
126
    }
127
128
    /**
129
     * Determines whether to check a database exists on the host by
130
     * querying the 'postgres' database and running createDatabase.
131
     *
132
     * Some locked down systems prevent access to the 'postgres' table in
133
     * which case you need to set this to false.
134
     *
135
     * If allow_query_master_postgres is false, and model_schema_as_database is also false,
136
     * then attempts to create or check databases beyond the initial connection will
137
     * result in a runtime error.
138
     *
139
     * @return bool
140
     */
141
    public static function allow_query_master_postgres()
142
    {
143
        return static::config()->allow_query_master_postgres;
144
    }
145
146
    /**
147
     * For instances where multiple databases are used beyond the initial connection
148
     * you may set this option to true to force database switches to switch schemas
149
     * instead of using databases. This may be useful if the database user does not
150
     * have cross-database permissions, and in cases where multiple databases are used
151
     * (such as in running test cases).
152
     *
153
     * If this is true then the database will only be set during the initial connection,
154
     * and attempts to change to this database will use the 'public' schema instead
155
     *
156
     * @return bool
157
     */
158
    public static function model_schema_as_database()
159
    {
160
        return static::config()->model_schema_as_database;
161
    }
162
163
    /**
164
     * Override the language that tsearch uses.  By default it is 'english, but
165
     * could be any of the supported languages that can be found in the
166
     * pg_catalog.pg_ts_config table.
167
     *
168
     * @return string
169
     */
170
    public static function search_language()
171
    {
172
        return static::config()->search_language;
173
    }
174
175
    /**
176
     * The database name specified at initial connection
177
     *
178
     * @var string
179
     */
180
    protected $databaseOriginal = '';
181
182
    /**
183
     * The schema name specified at initial construction. When model_schema_as_database
184
     * is set to true selecting the $databaseOriginal database will instead reset
185
     * the schema to this
186
     *
187
     * @var string
188
     */
189
    protected $schemaOriginal = '';
190
191
    /**
192
     * Connection parameters specified at inital connection
193
     *
194
     * @var array
195
     */
196
    protected $parameters = array();
197
198
    public function connect($parameters)
199
    {
200
        // Check database name
201
        if (empty($parameters['database'])) {
202
            // Check if we can use the master database
203
            if (!self::allow_query_master_postgres()) {
204
                throw new ErrorException('PostegreSQLDatabase::connect called without a database name specified');
205
            }
206
            // Fallback to master database connection if permission allows
207
            $parameters['database'] = self::MASTER_DATABASE;
208
        }
209
        $this->databaseOriginal = $parameters['database'];
210
211
        // check schema name
212
        if (empty($parameters['schema'])) {
213
            $parameters['schema'] = self::MASTER_SCHEMA;
214
        }
215
        $this->schemaOriginal = $parameters['schema'];
216
217
        // Ensure that driver is available (required by PDO)
218
        if (empty($parameters['driver'])) {
219
            $parameters['driver'] = $this->getDatabaseServer();
220
        }
221
222
        // Ensure port number is set (required by postgres)
223
        if (empty($parameters['port'])) {
224
            $parameters['port'] = 5432;
225
        }
226
227
        $this->parameters = $parameters;
228
229
        // If allowed, check that the database exists. Otherwise naively assume
230
        // that the original database exists
231
        if (self::allow_query_master_postgres()) {
232
            // Use master connection to setup initial schema
233
            $this->connectMaster();
234
            if (!$this->schemaManager->postgresDatabaseExists($this->databaseOriginal)) {
235
                $this->schemaManager->createPostgresDatabase($this->databaseOriginal);
236
            }
237
        }
238
239
        // Connect to the actual database we're requesting
240
        $this->connectDefault();
241
242
        // Set up the schema if required
243
        $this->setSchema($this->schemaOriginal, true);
244
245
        // Set the timezone if required.
246
        if (isset($parameters['timezone'])) {
247
            $this->selectTimezone($parameters['timezone']);
248
        }
249
    }
250
251
    protected function connectMaster()
252
    {
253
        $parameters = $this->parameters;
254
        $parameters['database'] = self::MASTER_DATABASE;
255
        $this->connector->connect($parameters, true);
256
    }
257
258
    protected function connectDefault()
259
    {
260
        $parameters = $this->parameters;
261
        $parameters['database'] = $this->databaseOriginal;
262
        $this->connector->connect($parameters, true);
263
    }
264
265
    /**
266
     * Sets the system timezone for the database connection
267
     *
268
     * @param string $timezone
269
     */
270
    public function selectTimezone($timezone)
271
    {
272
        if (empty($timezone)) {
273
            return;
274
        }
275
        $this->query("SET SESSION TIME ZONE '$timezone';");
276
    }
277
278
    public function supportsCollations()
279
    {
280
        return true;
281
    }
282
283
    public function supportsTimezoneOverride()
284
    {
285
        return true;
286
    }
287
288
    public function getDatabaseServer()
289
    {
290
        return "pgsql";
291
    }
292
293
    /**
294
     * Returns the name of the current schema in use
295
     *
296
     * @return string Name of current schema
297
     */
298
    public function currentSchema()
299
    {
300
        return $this->schema;
301
    }
302
303
    /**
304
     * Utility method to manually set the schema to an alternative
305
     * Check existance & sets search path to the supplied schema name
306
     *
307
     * @param string $schema Name of the schema
308
     * @param boolean $create Flag indicating whether the schema should be created
309
     * if it doesn't exist. If $create is false and the schema doesn't exist
310
     * then an error will be raised
311
     * @param int|boolean $errorLevel The level of error reporting to enable for
312
     * the query, or false if no error should be raised
313
     * @return boolean Flag indicating success
314
     */
315
    public function setSchema($schema, $create = false, $errorLevel = E_USER_ERROR)
316
    {
317
        if (!$this->schemaManager->schemaExists($schema)) {
318
            // Check DB creation permisson
319
            if (!$create) {
320
                if ($errorLevel !== false) {
321
                    user_error("Schema $schema does not exist", $errorLevel);
0 ignored issues
show
Bug introduced by
It seems like $errorLevel can also be of type true; however, parameter $error_level of user_error() does only seem to accept integer, maybe add an additional type check? ( Ignorable by Annotation )

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

321
                    user_error("Schema $schema does not exist", /** @scrutinizer ignore-type */ $errorLevel);
Loading history...
322
                }
323
                $this->schema = null;
324
                return false;
325
            }
326
            $this->schemaManager->createSchema($schema);
327
        }
328
        $this->setSchemaSearchPath($schema);
329
        $this->schema = $schema;
330
        return true;
331
    }
332
333
    /**
334
     * Override the schema search path. Search using the arguments supplied.
335
     * NOTE: The search path is normally set through setSchema() and only
336
     * one schema is selected. The facility to add more than one schema to
337
     * the search path is provided as an advanced PostgreSQL feature for raw
338
     * SQL queries. Sapphire cannot search for datamodel tables in alternate
339
     * schemas, so be wary of using alternate schemas within the ORM environment.
340
     *
341
     * @param string ...$arg Schema name to use. Add additional schema names as extra arguments.
342
     */
343
    public function setSchemaSearchPath($arg = null)
344
    {
345
        if (!$arg) {
346
            user_error('At least one Schema must be supplied to set a search path.', E_USER_ERROR);
347
        }
348
        $schemas = array_values(func_get_args());
349
        $this->query("SET search_path TO \"" . implode("\",\"", $schemas) . "\"");
350
    }
351
352
    /**
353
     * The core search engine configuration.
354
     * @todo Properly extract the search functions out of the core.
355
     *
356
     * @param array $classesToSearch
357
     * @param string $keywords Keywords as a space separated string
358
     * @param int $start
359
     * @param int $pageLength
360
     * @param string $sortBy
361
     * @param string $extraFilter
362
     * @param bool $booleanSearch
363
     * @param string $alternativeFileFilter
364
     * @param bool $invertedMatch
365
     * @return PaginatedList List of result pages
366
     * @throws Exception
367
     */
368
    public function searchEngine($classesToSearch, $keywords, $start, $pageLength, $sortBy = "ts_rank DESC", $extraFilter = "", $booleanSearch = false, $alternativeFileFilter = "", $invertedMatch = false)
369
    {
370
        $start = (int)$start;
371
        $pageLength = (int)$pageLength;
372
373
        //Fix the keywords to be ts_query compatitble:
374
        //Spaces must have pipes
375
        //@TODO: properly handle boolean operators here.
376
        $keywords= trim($keywords);
377
        $keywords= str_replace(' ', ' | ', $keywords);
378
        $keywords= str_replace('"', "'", $keywords);
379
380
381
        $keywords = $this->quoteString(trim($keywords));
382
383
        // Get tables
384
        $tablesToSearch = [];
385
        foreach ($classesToSearch as $class) {
386
            $tablesToSearch[$class] = DataObject::getSchema()->baseDataTable($class);
387
        }
388
389
        //We can get a list of all the tsvector columns though this query:
390
        //We know what tables to search in based on the $classesToSearch variable:
391
        $classesPlaceholders = DB::placeholders($classesToSearch);
392
        $searchableColumns = $this->preparedQuery(
393
            "
394
            SELECT table_name, column_name, data_type
395
            FROM information_schema.columns
396
            WHERE data_type='tsvector' AND table_name in ($classesPlaceholders);",
397
            array_values($tablesToSearch)
398
        );
399
        if (!$searchableColumns->numRecords()) {
400
            throw new Exception('there are no full text columns to search');
401
        }
402
403
        $tables = array();
404
        $tableParameters = array();
405
406
        // Make column selection lists
407
        $pageClass = 'SilverStripe\\CMS\\Model\\SiteTree';
408
        $fileClass = 'SilverStripe\\Assets\\File';
409
        $select = array(
410
            $pageClass => array(
411
                '"ClassName"',
412
                '"' . $tablesToSearch[$pageClass] . '"."ID"',
413
                '"ParentID"',
414
                '"Title"',
415
                '"URLSegment"',
416
                '"Content"',
417
                '"LastEdited"',
418
                '"Created"',
419
                'NULL AS "Name"',
420
                '"CanViewType"'
421
            ),
422
            $fileClass => array(
423
                '"ClassName"',
424
                '"' . $tablesToSearch[$fileClass] . '"."ID"',
425
                '0 AS "ParentID"',
426
                '"Title"',
427
                'NULL AS "URLSegment"',
428
                'NULL AS "Content"',
429
                '"LastEdited"',
430
                '"Created"',
431
                '"Name"',
432
                'NULL AS "CanViewType"'
433
            )
434
        );
435
436
        foreach ($searchableColumns as $searchableColumn) {
437
            $conditions = array();
438
            $tableName = $searchableColumn['table_name'];
439
            $columnName = $searchableColumn['column_name'];
440
            $className = DataObject::getSchema()->tableClass($tableName);
441
            if (DataObject::getSchema()->fieldSpec($className, 'ShowInSearch')) {
442
                $conditions[] = array('"ShowInSearch"' => 1);
443
            }
444
445
            $method = self::default_fts_search_method();
446
            $conditions[] = "\"{$tableName}\".\"{$columnName}\" $method q ";
447
            $query = DataObject::get($className, $conditions)->dataQuery()->query();
448
449
            // Could parameterise this, but convention is only to to so for where conditions
450
            $query->addFrom(array(
451
                'q' => ", to_tsquery('" . self::search_language() . "', $keywords)"
452
            ));
453
            $query->setSelect(array());
454
455
            foreach ($select[$className] as $clause) {
456
                if (preg_match('/^(.*) +AS +"?([^"]*)"?/i', $clause, $matches)) {
457
                    $query->selectField($matches[1], $matches[2]);
458
                } else {
459
                    $query->selectField($clause);
460
                }
461
            }
462
463
            $query->selectField("ts_rank(\"{$tableName}\".\"{$columnName}\", q)", 'Relevance');
464
            $query->setOrderBy(array());
465
466
            //Add this query to the collection
467
            $tables[] = $query->sql($parameters);
468
            $tableParameters = array_merge($tableParameters, $parameters);
469
        }
470
471
        $limit = $pageLength;
472
        $offset = $start;
473
474
        if ($keywords) {
475
            $orderBy = " ORDER BY $sortBy";
476
        } else {
477
            $orderBy='';
478
        }
479
480
        $fullQuery = "SELECT *, count(*) OVER() as _fullcount FROM (" . implode(" UNION ", $tables) . ") AS q1 $orderBy LIMIT $limit OFFSET $offset";
481
482
        // Get records
483
        $records = $this->preparedQuery($fullQuery, $tableParameters);
484
        $totalCount = 0;
485
        $objects = [];
486
        foreach ($records as $record) {
487
            $objects[] = Injector::inst()->createWithArgs($record['ClassName'], [$record]);
488
            $totalCount = $record['_fullcount'];
489
        }
490
491
        if ($objects) {
0 ignored issues
show
Bug Best Practice introduced by
The expression $objects of type array is implicitly converted to a boolean; are you sure this is intended? If so, consider using ! empty($expr) instead to make it clear that you intend to check for an array without elements.

This check marks implicit conversions of arrays to boolean values in a comparison. While in PHP an empty array is considered to be equal (but not identical) to false, this is not always apparent.

Consider making the comparison explicit by using empty(..) or ! empty(...) instead.

Loading history...
introduced by
$objects is an empty array, thus is always false.
Loading history...
492
            $results = new ArrayList($objects);
493
        } else {
494
            $results = new ArrayList();
495
        }
496
        $list = new PaginatedList($results);
497
        $list->setLimitItems(false);
498
        $list->setPageStart($start);
499
        $list->setPageLength($pageLength);
500
        $list->setTotalItems($totalCount);
501
        return $list;
502
    }
503
504
    public function supportsTransactions()
505
    {
506
        return $this->supportsTransactions;
507
    }
508
509
    /*
510
     * This is a quick lookup to discover if the database supports particular extensions
511
     */
512
    public function supportsExtensions($extensions = array('partitions', 'tablespaces', 'clustering'))
513
    {
514
        if (isset($extensions['partitions'])) {
515
            return true;
516
        } elseif (isset($extensions['tablespaces'])) {
517
            return true;
518
        } elseif (isset($extensions['clustering'])) {
519
            return true;
520
        } else {
521
            return false;
522
        }
523
    }
524
525
    public function transactionStart($transaction_mode = false, $session_characteristics = false)
526
    {
527
        if ($this->transactionNesting > 0) {
528
            $this->transactionSavepoint('NESTEDTRANSACTION' . $this->transactionNesting);
529
        } else {
530
            $this->query('BEGIN;');
531
532
            if ($transaction_mode) {
533
                $this->query("SET TRANSACTION {$transaction_mode};");
534
            }
535
536
            if ($session_characteristics) {
537
                $this->query("SET SESSION CHARACTERISTICS AS TRANSACTION {$session_characteristics};");
538
            }
539
        }
540
        ++$this->transactionNesting;
541
    }
542
543
    public function transactionSavepoint($savepoint)
544
    {
545
        $this->query("SAVEPOINT {$savepoint};");
546
    }
547
548
    public function transactionRollback($savepoint = false)
549
    {
550
        // Named savepoint
551
        if ($savepoint) {
552
            $this->query('ROLLBACK TO ' . $savepoint);
553
            return true;
554
        }
555
556
        // Abort if unable to unnest, otherwise jump up a level
557
        if (!$this->transactionNesting) {
558
            return false;
559
        }
560
        --$this->transactionNesting;
561
562
        // Rollback nested
563
        if ($this->transactionNesting > 0) {
564
            return $this->transactionRollback('NESTEDTRANSACTION' . $this->transactionNesting);
0 ignored issues
show
Bug introduced by
Are you sure $this->transactionNesting of type true can be used in concatenation? ( Ignorable by Annotation )

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

564
            return $this->transactionRollback('NESTEDTRANSACTION' . /** @scrutinizer ignore-type */ $this->transactionNesting);
Loading history...
565
        }
566
567
        // Rollback top level
568
        $this->query('ROLLBACK');
569
        return true;
570
    }
571
572
    public function transactionDepth()
573
    {
574
        return $this->transactionNesting;
575
    }
576
577
    public function transactionEnd($chain = false)
578
    {
579
        --$this->transactionNesting;
580
        if ($this->transactionNesting <= 0) {
581
            $this->transactionNesting = 0;
0 ignored issues
show
Documentation Bug introduced by
The property $transactionNesting was declared of type boolean, but 0 is of type integer. Maybe add a type cast?

This check looks for assignments to scalar types that may be of the wrong type.

To ensure the code behaves as expected, it may be a good idea to add an explicit type cast.

$answer = 42;

$correct = false;

$correct = (bool) $answer;
Loading history...
582
            $this->query('COMMIT;');
583
        }
584
    }
585
586
    public function comparisonClause($field, $value, $exact = false, $negate = false, $caseSensitive = null, $parameterised = false)
587
    {
588
        if ($exact && $caseSensitive === null) {
589
            $comp = ($negate) ? '!=' : '=';
590
        } else {
591
            $comp = ($caseSensitive === true) ? 'LIKE' : 'ILIKE';
592
            if ($negate) {
593
                $comp = 'NOT ' . $comp;
594
            }
595
            $field.='::text';
596
        }
597
598
        if ($parameterised) {
599
            return sprintf("%s %s ?", $field, $comp);
600
        } else {
601
            return sprintf("%s %s '%s'", $field, $comp, $value);
602
        }
603
    }
604
605
    /**
606
     * Function to return an SQL datetime expression that can be used with Postgres
607
     * used for querying a datetime in a certain format
608
     * @param string $date to be formated, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"'
609
     * @param string $format to be used, supported specifiers:
610
     * %Y = Year (four digits)
611
     * %m = Month (01..12)
612
     * %d = Day (01..31)
613
     * %H = Hour (00..23)
614
     * %i = Minutes (00..59)
615
     * %s = Seconds (00..59)
616
     * %U = unix timestamp, can only be used on it's own
617
     * @return string SQL datetime expression to query for a formatted datetime
618
     */
619
    public function formattedDatetimeClause($date, $format)
620
    {
621
        preg_match_all('/%(.)/', $format, $matches);
622
        foreach ($matches[1] as $match) {
623
            if (array_search($match, array('Y','m','d','H','i','s','U')) === false) {
624
                user_error('formattedDatetimeClause(): unsupported format character %' . $match, E_USER_WARNING);
625
            }
626
        }
627
628
        $translate = array(
629
            '/%Y/' => 'YYYY',
630
            '/%m/' => 'MM',
631
            '/%d/' => 'DD',
632
            '/%H/' => 'HH24',
633
            '/%i/' => 'MI',
634
            '/%s/' => 'SS',
635
        );
636
        $format = preg_replace(array_keys($translate), array_values($translate), $format);
637
638
        if (preg_match('/^now$/i', $date)) {
639
            $date = "NOW()";
640
        } elseif (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date)) {
641
            $date = "TIMESTAMP '$date'";
642
        }
643
644
        if ($format == '%U') {
645
            return "FLOOR(EXTRACT(epoch FROM $date))";
646
        }
647
648
        return "to_char($date, TEXT '$format')";
649
    }
650
651
    /**
652
     * Function to return an SQL datetime expression that can be used with Postgres
653
     * used for querying a datetime addition
654
     * @param string $date, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"'
655
     * @param string $interval to be added, use the format [sign][integer] [qualifier], e.g. -1 Day, +15 minutes, +1 YEAR
656
     * supported qualifiers:
657
     * - years
658
     * - months
659
     * - days
660
     * - hours
661
     * - minutes
662
     * - seconds
663
     * This includes the singular forms as well
664
     * @return string SQL datetime expression to query for a datetime (YYYY-MM-DD hh:mm:ss) which is the result of the addition
665
     */
666
    public function datetimeIntervalClause($date, $interval)
667
    {
668
        if (preg_match('/^now$/i', $date)) {
669
            $date = "NOW()";
670
        } elseif (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date)) {
671
            $date = "TIMESTAMP '$date'";
672
        }
673
674
        // ... when being too precise becomes a pain. we need to cut of the fractions.
675
        // TIMESTAMP(0) doesn't work because it rounds instead flooring
676
        return "CAST(SUBSTRING(CAST($date + INTERVAL '$interval' AS VARCHAR) FROM 1 FOR 19) AS TIMESTAMP)";
677
    }
678
679
    /**
680
     * Function to return an SQL datetime expression that can be used with Postgres
681
     * used for querying a datetime substraction
682
     * @param string $date1, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"'
683
     * @param string $date2 to be substracted of $date1, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"'
684
     * @return string SQL datetime expression to query for the interval between $date1 and $date2 in seconds which is the result of the substraction
685
     */
686
    public function datetimeDifferenceClause($date1, $date2)
687
    {
688
        if (preg_match('/^now$/i', $date1)) {
689
            $date1 = "NOW()";
690
        } elseif (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date1)) {
691
            $date1 = "TIMESTAMP '$date1'";
692
        }
693
694
        if (preg_match('/^now$/i', $date2)) {
695
            $date2 = "NOW()";
696
        } elseif (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date2)) {
697
            $date2 = "TIMESTAMP '$date2'";
698
        }
699
700
        return "(FLOOR(EXTRACT(epoch FROM $date1)) - FLOOR(EXTRACT(epoch from $date2)))";
701
    }
702
703
    public function now()
704
    {
705
        return 'NOW()';
706
    }
707
708
    public function random()
709
    {
710
        return 'RANDOM()';
711
    }
712
713
    /**
714
     * Determines the name of the current database to be reported externally
715
     * by substituting the schema name for the database name.
716
     * Should only be used when model_schema_as_database is true
717
     *
718
     * @param string $schema Name of the schema
719
     * @return string Name of the database to report
720
     */
721
    public function schemaToDatabaseName($schema)
722
    {
723
        switch ($schema) {
724
            case $this->schemaOriginal:
725
                return $this->databaseOriginal;
726
            default:
727
                return $schema;
728
        }
729
    }
730
731
    /**
732
     * Translates a requested database name to a schema name to substitute internally.
733
     * Should only be used when model_schema_as_database is true
734
     *
735
     * @param string $database Name of the database
736
     * @return string Name of the schema to use for this database internally
737
     */
738
    public function databaseToSchemaName($database)
739
    {
740
        switch ($database) {
741
            case $this->databaseOriginal:
742
                return $this->schemaOriginal;
743
            default:
744
                return $database;
745
        }
746
    }
747
748
    public function dropSelectedDatabase()
749
    {
750
        if (self::model_schema_as_database()) {
751
            // Check current schema is valid
752
            $oldSchema = $this->schema;
753
            if (empty($oldSchema)) {
754
                return;
755
            } // Nothing selected to drop
756
757
            // Select another schema
758
            if ($oldSchema !== $this->schemaOriginal) {
759
                $this->setSchema($this->schemaOriginal);
760
            } elseif ($oldSchema !== self::MASTER_SCHEMA) {
761
                $this->setSchema(self::MASTER_SCHEMA);
762
            } else {
763
                $this->schema = null;
764
            }
765
766
            // Remove this schema
767
            $this->schemaManager->dropSchema($oldSchema);
768
        } else {
769
            parent::dropSelectedDatabase();
770
        }
771
    }
772
773
    public function getSelectedDatabase()
774
    {
775
        if (self::model_schema_as_database()) {
776
            return $this->schemaToDatabaseName($this->schema);
777
        }
778
        return parent::getSelectedDatabase();
779
    }
780
781
    public function selectDatabase($name, $create = false, $errorLevel = E_USER_ERROR)
782
    {
783
        // Substitute schema here as appropriate
784
        if (self::model_schema_as_database()) {
785
            // Selecting the database itself should be treated as selecting the public schema
786
            $schemaName = $this->databaseToSchemaName($name);
787
            return $this->setSchema($schemaName, $create, $errorLevel);
788
        }
789
790
        // Database selection requires that a new connection is established.
791
        // This is not ideal postgres practise
792
        if (!$this->schemaManager->databaseExists($name)) {
793
            // Check DB creation permisson
794
            if (!$create) {
795
                if ($errorLevel !== false) {
796
                    user_error("Attempted to connect to non-existing database \"$name\"", $errorLevel);
797
                }
798
                // Unselect database
799
                $this->connector->unloadDatabase();
800
                return false;
801
            }
802
            $this->schemaManager->createDatabase($name);
803
        }
804
805
        // New connection made here, treating the new database name as the new original
806
        $this->databaseOriginal = $name;
807
        $this->connectDefault();
808
        return true;
809
    }
810
811
    /**
812
     * Delete all entries from the table instead of truncating it.
813
     *
814
     * This gives a massive speed improvement compared to using TRUNCATE, with
815
     * the caveat that primary keys are not reset etc.
816
     *
817
     * @see DatabaseAdmin::clearAllData()
818
     *
819
     * @param string $table
820
     */
821
    public function clearTable($table)
822
    {
823
        $this->query('DELETE FROM "'.$table.'";');
824
    }
825
}
826