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
![]() |
|||||
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
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 ![]() |
|||||
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
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
![]() |
|||||
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
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;
![]() |
|||||
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 |