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