1: <?php
  2: /**
  3:  * CakePHP(tm) : Rapid Development Framework (https://cakephp.org)
  4:  * Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
  5:  *
  6:  * Licensed under The MIT License
  7:  * For full copyright and license information, please see the LICENSE.txt
  8:  * Redistributions of files must retain the above copyright notice.
  9:  *
 10:  * @copyright     Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
 11:  * @link          https://cakephp.org CakePHP(tm) Project
 12:  * @since         3.0.0
 13:  * @license       https://opensource.org/licenses/mit-license.php MIT License
 14:  */
 15: namespace Cake\Database\Schema;
 16: 
 17: use Cake\Database\Exception;
 18: use Cake\Database\Schema\TableSchema;
 19: 
 20: /**
 21:  * Schema management/reflection features for Postgres.
 22:  */
 23: class PostgresSchema extends BaseSchema
 24: {
 25: 
 26:     /**
 27:      * {@inheritDoc}
 28:      */
 29:     public function listTablesSql($config)
 30:     {
 31:         $sql = 'SELECT table_name as name FROM information_schema.tables WHERE table_schema = ? ORDER BY name';
 32:         $schema = empty($config['schema']) ? 'public' : $config['schema'];
 33: 
 34:         return [$sql, [$schema]];
 35:     }
 36: 
 37:     /**
 38:      * {@inheritDoc}
 39:      */
 40:     public function describeColumnSql($tableName, $config)
 41:     {
 42:         $sql = 'SELECT DISTINCT table_schema AS schema,
 43:             column_name AS name,
 44:             data_type AS type,
 45:             is_nullable AS null, column_default AS default,
 46:             character_maximum_length AS char_length,
 47:             c.collation_name,
 48:             d.description as comment,
 49:             ordinal_position,
 50:             c.numeric_precision as column_precision,
 51:             c.numeric_scale as column_scale,
 52:             pg_get_serial_sequence(attr.attrelid::regclass::text, attr.attname) IS NOT NULL AS has_serial
 53:         FROM information_schema.columns c
 54:         INNER JOIN pg_catalog.pg_namespace ns ON (ns.nspname = table_schema)
 55:         INNER JOIN pg_catalog.pg_class cl ON (cl.relnamespace = ns.oid AND cl.relname = table_name)
 56:         LEFT JOIN pg_catalog.pg_index i ON (i.indrelid = cl.oid AND i.indkey[0] = c.ordinal_position)
 57:         LEFT JOIN pg_catalog.pg_description d on (cl.oid = d.objoid AND d.objsubid = c.ordinal_position)
 58:         LEFT JOIN pg_catalog.pg_attribute attr ON (cl.oid = attr.attrelid AND column_name = attr.attname)
 59:         WHERE table_name = ? AND table_schema = ? AND table_catalog = ?
 60:         ORDER BY ordinal_position';
 61: 
 62:         $schema = empty($config['schema']) ? 'public' : $config['schema'];
 63: 
 64:         return [$sql, [$tableName, $schema, $config['database']]];
 65:     }
 66: 
 67:     /**
 68:      * Convert a column definition to the abstract types.
 69:      *
 70:      * The returned type will be a type that
 71:      * Cake\Database\Type can handle.
 72:      *
 73:      * @param string $column The column type + length
 74:      * @throws \Cake\Database\Exception when column cannot be parsed.
 75:      * @return array Array of column information.
 76:      */
 77:     protected function _convertColumn($column)
 78:     {
 79:         preg_match('/([a-z\s]+)(?:\(([0-9,]+)\))?/i', $column, $matches);
 80:         if (empty($matches)) {
 81:             throw new Exception(sprintf('Unable to parse column type from "%s"', $column));
 82:         }
 83: 
 84:         $col = strtolower($matches[1]);
 85:         $length = null;
 86:         if (isset($matches[2])) {
 87:             $length = (int)$matches[2];
 88:         }
 89: 
 90:         if (in_array($col, ['date', 'time', 'boolean'])) {
 91:             return ['type' => $col, 'length' => null];
 92:         }
 93:         if (strpos($col, 'timestamp') !== false) {
 94:             return ['type' => TableSchema::TYPE_TIMESTAMP, 'length' => null];
 95:         }
 96:         if (strpos($col, 'time') !== false) {
 97:             return ['type' => TableSchema::TYPE_TIME, 'length' => null];
 98:         }
 99:         if ($col === 'serial' || $col === 'integer') {
100:             return ['type' => TableSchema::TYPE_INTEGER, 'length' => 10];
101:         }
102:         if ($col === 'bigserial' || $col === 'bigint') {
103:             return ['type' => TableSchema::TYPE_BIGINTEGER, 'length' => 20];
104:         }
105:         if ($col === 'smallint') {
106:             return ['type' => TableSchema::TYPE_SMALLINTEGER, 'length' => 5];
107:         }
108:         if ($col === 'inet') {
109:             return ['type' => TableSchema::TYPE_STRING, 'length' => 39];
110:         }
111:         if ($col === 'uuid') {
112:             return ['type' => TableSchema::TYPE_UUID, 'length' => null];
113:         }
114:         if ($col === 'char' || $col === 'character') {
115:             return ['type' => TableSchema::TYPE_STRING, 'fixed' => true, 'length' => $length];
116:         }
117:         // money is 'string' as it includes arbitrary text content
118:         // before the number value.
119:         if (strpos($col, 'char') !== false ||
120:             strpos($col, 'money') !== false
121:         ) {
122:             return ['type' => TableSchema::TYPE_STRING, 'length' => $length];
123:         }
124:         if (strpos($col, 'text') !== false) {
125:             return ['type' => TableSchema::TYPE_TEXT, 'length' => null];
126:         }
127:         if ($col === 'bytea') {
128:             return ['type' => TableSchema::TYPE_BINARY, 'length' => null];
129:         }
130:         if ($col === 'real' || strpos($col, 'double') !== false) {
131:             return ['type' => TableSchema::TYPE_FLOAT, 'length' => null];
132:         }
133:         if (strpos($col, 'numeric') !== false ||
134:             strpos($col, 'decimal') !== false
135:         ) {
136:             return ['type' => TableSchema::TYPE_DECIMAL, 'length' => null];
137:         }
138: 
139:         if (strpos($col, 'json') !== false) {
140:             return ['type' => TableSchema::TYPE_JSON, 'length' => null];
141:         }
142: 
143:         return ['type' => TableSchema::TYPE_STRING, 'length' => null];
144:     }
145: 
146:     /**
147:      * {@inheritDoc}
148:      */
149:     public function convertColumnDescription(TableSchema $schema, $row)
150:     {
151:         $field = $this->_convertColumn($row['type']);
152: 
153:         if ($field['type'] === TableSchema::TYPE_BOOLEAN) {
154:             if ($row['default'] === 'true') {
155:                 $row['default'] = 1;
156:             }
157:             if ($row['default'] === 'false') {
158:                 $row['default'] = 0;
159:             }
160:         }
161:         if (!empty($row['has_serial'])) {
162:             $field['autoIncrement'] = true;
163:         }
164: 
165:         $field += [
166:             'default' => $this->_defaultValue($row['default']),
167:             'null' => $row['null'] === 'YES',
168:             'collate' => $row['collation_name'],
169:             'comment' => $row['comment']
170:         ];
171:         $field['length'] = $row['char_length'] ?: $field['length'];
172: 
173:         if ($field['type'] === 'numeric' || $field['type'] === 'decimal') {
174:             $field['length'] = $row['column_precision'];
175:             $field['precision'] = $row['column_scale'] ?: null;
176:         }
177:         $schema->addColumn($row['name'], $field);
178:     }
179: 
180:     /**
181:      * Manipulate the default value.
182:      *
183:      * Postgres includes sequence data and casting information in default values.
184:      * We need to remove those.
185:      *
186:      * @param string|null $default The default value.
187:      * @return string|null
188:      */
189:     protected function _defaultValue($default)
190:     {
191:         if (is_numeric($default) || $default === null) {
192:             return $default;
193:         }
194:         // Sequences
195:         if (strpos($default, 'nextval') === 0) {
196:             return null;
197:         }
198: 
199:         if (strpos($default, 'NULL::') === 0) {
200:             return null;
201:         }
202: 
203:         // Remove quotes and postgres casts
204:         return preg_replace(
205:             "/^'(.*)'(?:::.*)$/",
206:             '$1',
207:             $default
208:         );
209:     }
210: 
211:     /**
212:      * {@inheritDoc}
213:      */
214:     public function describeIndexSql($tableName, $config)
215:     {
216:         $sql = 'SELECT
217:         c2.relname,
218:         a.attname,
219:         i.indisprimary,
220:         i.indisunique
221:         FROM pg_catalog.pg_namespace n
222:         INNER JOIN pg_catalog.pg_class c ON (n.oid = c.relnamespace)
223:         INNER JOIN pg_catalog.pg_index i ON (c.oid = i.indrelid)
224:         INNER JOIN pg_catalog.pg_class c2 ON (c2.oid = i.indexrelid)
225:         INNER JOIN pg_catalog.pg_attribute a ON (a.attrelid = c.oid AND i.indrelid::regclass = a.attrelid::regclass)
226:         WHERE n.nspname = ?
227:         AND a.attnum = ANY(i.indkey)
228:         AND c.relname = ?
229:         ORDER BY i.indisprimary DESC, i.indisunique DESC, c.relname, a.attnum';
230: 
231:         $schema = 'public';
232:         if (!empty($config['schema'])) {
233:             $schema = $config['schema'];
234:         }
235: 
236:         return [$sql, [$schema, $tableName]];
237:     }
238: 
239:     /**
240:      * {@inheritDoc}
241:      */
242:     public function convertIndexDescription(TableSchema $schema, $row)
243:     {
244:         $type = TableSchema::INDEX_INDEX;
245:         $name = $row['relname'];
246:         if ($row['indisprimary']) {
247:             $name = $type = TableSchema::CONSTRAINT_PRIMARY;
248:         }
249:         if ($row['indisunique'] && $type === TableSchema::INDEX_INDEX) {
250:             $type = TableSchema::CONSTRAINT_UNIQUE;
251:         }
252:         if ($type === TableSchema::CONSTRAINT_PRIMARY || $type === TableSchema::CONSTRAINT_UNIQUE) {
253:             $this->_convertConstraint($schema, $name, $type, $row);
254: 
255:             return;
256:         }
257:         $index = $schema->getIndex($name);
258:         if (!$index) {
259:             $index = [
260:                 'type' => $type,
261:                 'columns' => []
262:             ];
263:         }
264:         $index['columns'][] = $row['attname'];
265:         $schema->addIndex($name, $index);
266:     }
267: 
268:     /**
269:      * Add/update a constraint into the schema object.
270:      *
271:      * @param \Cake\Database\Schema\TableSchema $schema The table to update.
272:      * @param string $name The index name.
273:      * @param string $type The index type.
274:      * @param array $row The metadata record to update with.
275:      * @return void
276:      */
277:     protected function _convertConstraint($schema, $name, $type, $row)
278:     {
279:         $constraint = $schema->getConstraint($name);
280:         if (!$constraint) {
281:             $constraint = [
282:                 'type' => $type,
283:                 'columns' => []
284:             ];
285:         }
286:         $constraint['columns'][] = $row['attname'];
287:         $schema->addConstraint($name, $constraint);
288:     }
289: 
290:     /**
291:      * {@inheritDoc}
292:      */
293:     public function describeForeignKeySql($tableName, $config)
294:     {
295:         $sql = 'SELECT
296:         c.conname AS name,
297:         c.contype AS type,
298:         a.attname AS column_name,
299:         c.confmatchtype AS match_type,
300:         c.confupdtype AS on_update,
301:         c.confdeltype AS on_delete,
302:         c.confrelid::regclass AS references_table,
303:         ab.attname AS references_field
304:         FROM pg_catalog.pg_namespace n
305:         INNER JOIN pg_catalog.pg_class cl ON (n.oid = cl.relnamespace)
306:         INNER JOIN pg_catalog.pg_constraint c ON (n.oid = c.connamespace)
307:         INNER JOIN pg_catalog.pg_attribute a ON (a.attrelid = cl.oid AND c.conrelid = a.attrelid AND a.attnum = ANY(c.conkey))
308:         INNER JOIN pg_catalog.pg_attribute ab ON (a.attrelid = cl.oid AND c.confrelid = ab.attrelid AND ab.attnum = ANY(c.confkey))
309:         WHERE n.nspname = ?
310:         AND cl.relname = ?
311:         ORDER BY name, a.attnum, ab.attnum DESC';
312: 
313:         $schema = empty($config['schema']) ? 'public' : $config['schema'];
314: 
315:         return [$sql, [$schema, $tableName]];
316:     }
317: 
318:     /**
319:      * {@inheritDoc}
320:      */
321:     public function convertForeignKeyDescription(TableSchema $schema, $row)
322:     {
323:         $data = [
324:             'type' => TableSchema::CONSTRAINT_FOREIGN,
325:             'columns' => $row['column_name'],
326:             'references' => [$row['references_table'], $row['references_field']],
327:             'update' => $this->_convertOnClause($row['on_update']),
328:             'delete' => $this->_convertOnClause($row['on_delete']),
329:         ];
330:         $schema->addConstraint($row['name'], $data);
331:     }
332: 
333:     /**
334:      * {@inheritDoc}
335:      */
336:     protected function _convertOnClause($clause)
337:     {
338:         if ($clause === 'r') {
339:             return TableSchema::ACTION_RESTRICT;
340:         }
341:         if ($clause === 'a') {
342:             return TableSchema::ACTION_NO_ACTION;
343:         }
344:         if ($clause === 'c') {
345:             return TableSchema::ACTION_CASCADE;
346:         }
347: 
348:         return TableSchema::ACTION_SET_NULL;
349:     }
350: 
351:     /**
352:      * {@inheritDoc}
353:      */
354:     public function columnSql(TableSchema $schema, $name)
355:     {
356:         $data = $schema->getColumn($name);
357:         $out = $this->_driver->quoteIdentifier($name);
358:         $typeMap = [
359:             TableSchema::TYPE_TINYINTEGER => ' SMALLINT',
360:             TableSchema::TYPE_SMALLINTEGER => ' SMALLINT',
361:             TableSchema::TYPE_BINARY_UUID => ' UUID',
362:             TableSchema::TYPE_BOOLEAN => ' BOOLEAN',
363:             TableSchema::TYPE_FLOAT => ' FLOAT',
364:             TableSchema::TYPE_DECIMAL => ' DECIMAL',
365:             TableSchema::TYPE_DATE => ' DATE',
366:             TableSchema::TYPE_TIME => ' TIME',
367:             TableSchema::TYPE_DATETIME => ' TIMESTAMP',
368:             TableSchema::TYPE_TIMESTAMP => ' TIMESTAMP',
369:             TableSchema::TYPE_UUID => ' UUID',
370:             TableSchema::TYPE_JSON => ' JSONB'
371:         ];
372: 
373:         if (isset($typeMap[$data['type']])) {
374:             $out .= $typeMap[$data['type']];
375:         }
376: 
377:         if ($data['type'] === TableSchema::TYPE_INTEGER || $data['type'] === TableSchema::TYPE_BIGINTEGER) {
378:             $type = $data['type'] === TableSchema::TYPE_INTEGER ? ' INTEGER' : ' BIGINT';
379:             if ([$name] === $schema->primaryKey() || $data['autoIncrement'] === true) {
380:                 $type = $data['type'] === TableSchema::TYPE_INTEGER ? ' SERIAL' : ' BIGSERIAL';
381:                 unset($data['null'], $data['default']);
382:             }
383:             $out .= $type;
384:         }
385: 
386:         if ($data['type'] === TableSchema::TYPE_TEXT && $data['length'] !== TableSchema::LENGTH_TINY) {
387:             $out .= ' TEXT';
388:         }
389:         if ($data['type'] === TableSchema::TYPE_BINARY) {
390:             $out .= ' BYTEA';
391:         }
392: 
393:         if ($data['type'] === TableSchema::TYPE_STRING ||
394:             ($data['type'] === TableSchema::TYPE_TEXT && $data['length'] === TableSchema::LENGTH_TINY)
395:         ) {
396:             $isFixed = !empty($data['fixed']);
397:             $type = ' VARCHAR';
398:             if ($isFixed) {
399:                 $type = ' CHAR';
400:             }
401:             $out .= $type;
402:             if (isset($data['length'])) {
403:                 $out .= '(' . (int)$data['length'] . ')';
404:             }
405:         }
406: 
407:         $hasCollate = [TableSchema::TYPE_TEXT, TableSchema::TYPE_STRING];
408:         if (in_array($data['type'], $hasCollate, true) && isset($data['collate']) && $data['collate'] !== '') {
409:             $out .= ' COLLATE "' . $data['collate'] . '"';
410:         }
411: 
412:         if ($data['type'] === TableSchema::TYPE_FLOAT && isset($data['precision'])) {
413:             $out .= '(' . (int)$data['precision'] . ')';
414:         }
415: 
416:         if ($data['type'] === TableSchema::TYPE_DECIMAL &&
417:             (isset($data['length']) || isset($data['precision']))
418:         ) {
419:             $out .= '(' . (int)$data['length'] . ',' . (int)$data['precision'] . ')';
420:         }
421: 
422:         if (isset($data['null']) && $data['null'] === false) {
423:             $out .= ' NOT NULL';
424:         }
425: 
426:         if (isset($data['default']) &&
427:             in_array($data['type'], [TableSchema::TYPE_TIMESTAMP, TableSchema::TYPE_DATETIME]) &&
428:             strtolower($data['default']) === 'current_timestamp'
429:         ) {
430:             $out .= ' DEFAULT CURRENT_TIMESTAMP';
431:         } elseif (isset($data['default'])) {
432:             $defaultValue = $data['default'];
433:             if ($data['type'] === 'boolean') {
434:                 $defaultValue = (bool)$defaultValue;
435:             }
436:             $out .= ' DEFAULT ' . $this->_driver->schemaValue($defaultValue);
437:         } elseif (isset($data['null']) && $data['null'] !== false) {
438:             $out .= ' DEFAULT NULL';
439:         }
440: 
441:         return $out;
442:     }
443: 
444:     /**
445:      * {@inheritDoc}
446:      */
447:     public function addConstraintSql(TableSchema $schema)
448:     {
449:         $sqlPattern = 'ALTER TABLE %s ADD %s;';
450:         $sql = [];
451: 
452:         foreach ($schema->constraints() as $name) {
453:             $constraint = $schema->getConstraint($name);
454:             if ($constraint['type'] === TableSchema::CONSTRAINT_FOREIGN) {
455:                 $tableName = $this->_driver->quoteIdentifier($schema->name());
456:                 $sql[] = sprintf($sqlPattern, $tableName, $this->constraintSql($schema, $name));
457:             }
458:         }
459: 
460:         return $sql;
461:     }
462: 
463:     /**
464:      * {@inheritDoc}
465:      */
466:     public function dropConstraintSql(TableSchema $schema)
467:     {
468:         $sqlPattern = 'ALTER TABLE %s DROP CONSTRAINT %s;';
469:         $sql = [];
470: 
471:         foreach ($schema->constraints() as $name) {
472:             $constraint = $schema->getConstraint($name);
473:             if ($constraint['type'] === TableSchema::CONSTRAINT_FOREIGN) {
474:                 $tableName = $this->_driver->quoteIdentifier($schema->name());
475:                 $constraintName = $this->_driver->quoteIdentifier($name);
476:                 $sql[] = sprintf($sqlPattern, $tableName, $constraintName);
477:             }
478:         }
479: 
480:         return $sql;
481:     }
482: 
483:     /**
484:      * {@inheritDoc}
485:      */
486:     public function indexSql(TableSchema $schema, $name)
487:     {
488:         $data = $schema->getIndex($name);
489:         $columns = array_map(
490:             [$this->_driver, 'quoteIdentifier'],
491:             $data['columns']
492:         );
493: 
494:         return sprintf(
495:             'CREATE INDEX %s ON %s (%s)',
496:             $this->_driver->quoteIdentifier($name),
497:             $this->_driver->quoteIdentifier($schema->name()),
498:             implode(', ', $columns)
499:         );
500:     }
501: 
502:     /**
503:      * {@inheritDoc}
504:      */
505:     public function constraintSql(TableSchema $schema, $name)
506:     {
507:         $data = $schema->getConstraint($name);
508:         $out = 'CONSTRAINT ' . $this->_driver->quoteIdentifier($name);
509:         if ($data['type'] === TableSchema::CONSTRAINT_PRIMARY) {
510:             $out = 'PRIMARY KEY';
511:         }
512:         if ($data['type'] === TableSchema::CONSTRAINT_UNIQUE) {
513:             $out .= ' UNIQUE';
514:         }
515: 
516:         return $this->_keySql($out, $data);
517:     }
518: 
519:     /**
520:      * Helper method for generating key SQL snippets.
521:      *
522:      * @param string $prefix The key prefix
523:      * @param array $data Key data.
524:      * @return string
525:      */
526:     protected function _keySql($prefix, $data)
527:     {
528:         $columns = array_map(
529:             [$this->_driver, 'quoteIdentifier'],
530:             $data['columns']
531:         );
532:         if ($data['type'] === TableSchema::CONSTRAINT_FOREIGN) {
533:             return $prefix . sprintf(
534:                 ' FOREIGN KEY (%s) REFERENCES %s (%s) ON UPDATE %s ON DELETE %s DEFERRABLE INITIALLY IMMEDIATE',
535:                 implode(', ', $columns),
536:                 $this->_driver->quoteIdentifier($data['references'][0]),
537:                 $this->_convertConstraintColumns($data['references'][1]),
538:                 $this->_foreignOnClause($data['update']),
539:                 $this->_foreignOnClause($data['delete'])
540:             );
541:         }
542: 
543:         return $prefix . ' (' . implode(', ', $columns) . ')';
544:     }
545: 
546:     /**
547:      * {@inheritDoc}
548:      */
549:     public function createTableSql(TableSchema $schema, $columns, $constraints, $indexes)
550:     {
551:         $content = array_merge($columns, $constraints);
552:         $content = implode(",\n", array_filter($content));
553:         $tableName = $this->_driver->quoteIdentifier($schema->name());
554:         $temporary = $schema->isTemporary() ? ' TEMPORARY ' : ' ';
555:         $out = [];
556:         $out[] = sprintf("CREATE%sTABLE %s (\n%s\n)", $temporary, $tableName, $content);
557:         foreach ($indexes as $index) {
558:             $out[] = $index;
559:         }
560:         foreach ($schema->columns() as $column) {
561:             $columnData = $schema->getColumn($column);
562:             if (isset($columnData['comment'])) {
563:                 $out[] = sprintf(
564:                     'COMMENT ON COLUMN %s.%s IS %s',
565:                     $tableName,
566:                     $this->_driver->quoteIdentifier($column),
567:                     $this->_driver->schemaValue($columnData['comment'])
568:                 );
569:             }
570:         }
571: 
572:         return $out;
573:     }
574: 
575:     /**
576:      * {@inheritDoc}
577:      */
578:     public function truncateTableSql(TableSchema $schema)
579:     {
580:         $name = $this->_driver->quoteIdentifier($schema->name());
581: 
582:         return [
583:             sprintf('TRUNCATE %s RESTART IDENTITY CASCADE', $name)
584:         ];
585:     }
586: 
587:     /**
588:      * Generate the SQL to drop a table.
589:      *
590:      * @param \Cake\Database\Schema\TableSchema $schema Table instance
591:      * @return array SQL statements to drop a table.
592:      */
593:     public function dropTableSql(TableSchema $schema)
594:     {
595:         $sql = sprintf(
596:             'DROP TABLE %s CASCADE',
597:             $this->_driver->quoteIdentifier($schema->name())
598:         );
599: 
600:         return [$sql];
601:     }
602: }
603: