Total Complexity | 95 |
Total Lines | 803 |
Duplicated Lines | 0 % |
Changes | 5 | ||
Bugs | 0 | Features | 0 |
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 |
||
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() |
||
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() |
||
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); |
||
|
|||
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) { |
||
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() |
||
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); |
||
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; |
||
582 | $this->query('COMMIT;'); |
||
583 | } |
||
584 | } |
||
585 | |||
586 | public function comparisonClause($field, $value, $exact = false, $negate = false, $caseSensitive = null, $parameterised = false) |
||
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) |
||
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() |
||
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); |
||
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) |
||
824 | } |
||
825 | } |
||
826 |