123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457 |
- <?php
- defined('GNUSOCIAL') || die();
- class PgsqlSchema extends Schema
- {
-
- public function getTableDef($table)
- {
- $def = [];
- $hasKeys = false;
-
- $columns = $this->fetchMetaInfo($table, 'columns', 'ordinal_position');
- if (count($columns) == 0) {
- throw new SchemaTableMissingException("No such table: $table");
- }
-
- $orderedFields = [];
- foreach ($columns as $row) {
- $name = $row['column_name'];
- $orderedFields[$row['ordinal_position']] = $name;
- $field = [];
- $field['type'] = $type = $row['udt_name'];
- if ($type == 'char' || $type == 'varchar') {
- if ($row['character_maximum_length'] !== null) {
- $field['length'] = intval($row['character_maximum_length']);
- }
- }
- if ($type == 'numeric') {
-
-
- if ($row['numeric_precision'] !== null) {
- $field['precision'] = intval($row['numeric_precision']);
- }
- if ($row['numeric_scale'] !== null) {
- $field['scale'] = intval($row['numeric_scale']);
- }
- }
- if ($row['is_nullable'] == 'NO') {
- $field['not null'] = true;
- }
- if ($row['column_default'] !== null) {
- $field['default'] = $row['column_default'];
- if ($this->isNumericType($type)) {
- $field['default'] = intval($field['default']);
- }
- }
- $def['fields'][$name] = $field;
- }
-
-
-
- $indexInfo = $this->getIndexInfo($table);
- foreach ($indexInfo as $row) {
- $keyName = $row['key_name'];
-
-
-
-
-
- $cols = [];
- $colPositions = explode(' ', $row['indkey']);
- foreach ($colPositions as $ord) {
- if ($ord == 0) {
- $cols[] = 'FUNCTION';
- } else {
- $cols[] = $orderedFields[$ord];
- }
- }
- $def['indexes'][$keyName] = $cols;
- }
-
-
- $keyColumns = $this->fetchMetaInfo($table, 'key_column_usage', 'constraint_name,ordinal_position');
- $keys = [];
- foreach ($keyColumns as $row) {
- $keyName = $row['constraint_name'];
- $keyCol = $row['column_name'];
- if (!isset($keys[$keyName])) {
- $keys[$keyName] = [];
- }
- $keys[$keyName][] = $keyCol;
- }
- foreach ($keys as $keyName => $cols) {
-
- if ($keyName == "{$table}_pkey") {
- $def['primary key'] = $cols;
- } else if (preg_match("/^{$table}_(.*)_fkey$/", $keyName, $matches)) {
- $fkey = $this->getForeignKeyInfo($table, $keyName);
- $colMap = array_combine($cols, $fkey['col_names']);
- $def['foreign keys'][$keyName] = [$fkey['table_name'], $colMap];
- } else {
- $def['unique keys'][$keyName] = $cols;
- }
- }
- return $def;
- }
-
- function fetchMetaInfo($table, $infoTable, $orderBy = null)
- {
- $query = "SELECT * FROM information_schema.%s " .
- "WHERE table_name='%s'";
- $sql = sprintf($query, $infoTable, $table);
- if ($orderBy) {
- $sql .= ' ORDER BY ' . $orderBy;
- }
- return $this->fetchQueryData($sql);
- }
-
- function getIndexInfo($table)
- {
- $query = 'SELECT ' .
- '(SELECT relname FROM pg_class WHERE oid=indexrelid) AS key_name, ' .
- '* FROM pg_index ' .
- 'WHERE indrelid=(SELECT oid FROM pg_class WHERE relname=\'%s\') ' .
- 'AND indisprimary=\'f\' AND indisunique=\'f\' ' .
- 'ORDER BY indrelid, indexrelid';
- $sql = sprintf($query, $table);
- return $this->fetchQueryData($sql);
- }
-
- function getForeignKeyInfo($table, $constraint_name)
- {
-
-
- $query = 'SELECT ' .
- '(SELECT relname FROM pg_class WHERE oid=confrelid) AS table_name, ' .
- 'confrelid AS table_id, ' .
- '(SELECT indkey FROM pg_index WHERE indexrelid=conindid) AS col_indexes ' .
- 'FROM pg_constraint ' .
- 'WHERE conrelid=(SELECT oid FROM pg_class WHERE relname=\'%s\') ' .
- 'AND conname=\'%s\' ' .
- 'AND contype=\'f\'';
- $sql = sprintf($query, $table, $constraint_name);
- $data = $this->fetchQueryData($sql);
- if (count($data) < 1) {
- throw new Exception("Could not find foreign key " . $constraint_name . " on table " . $table);
- }
- $row = $data[0];
- return [
- 'table_name' => $row['table_name'],
- 'col_names' => $this->getTableColumnNames($row['table_id'], $row['col_indexes'])
- ];
- }
-
- function getTableColumnNames($table_id, $col_indexes)
- {
- $indexes = array_map('intval', explode(' ', $col_indexes));
- $query = 'SELECT attnum AS col_index, attname AS col_name ' .
- 'FROM pg_attribute where attrelid=%d ' .
- 'AND attnum IN (%s)';
- $sql = sprintf($query, $table_id, implode(',', $indexes));
- $data = $this->fetchQueryData($sql);
- $byId = [];
- foreach ($data as $row) {
- $byId[$row['col_index']] = $row['col_name'];
- }
- $out = [];
- foreach ($indexes as $id) {
- $out[] = $byId[$id];
- }
- return $out;
- }
-
- private function _columnTypeTranslation($type)
- {
- $map = [
- 'datetime' => 'timestamp',
- ];
- if (!empty($map[$type])) {
- return $map[$type];
- }
- return $type;
- }
-
- function columnSql(array $cd)
- {
- $line = [];
- $line[] = parent::columnSql($cd);
-
- return implode(' ', $line);
- }
-
- function appendAlterModifyColumn(array &$phrase, $columnName, array $old, array $cd)
- {
- $prefix = 'ALTER COLUMN ' . $this->quoteIdentifier($columnName) . ' ';
- $oldType = $this->mapType($old);
- $newType = $this->mapType($cd);
- if ($oldType != $newType) {
- $phrase[] = $prefix . 'TYPE ' . $newType;
- }
- if (!empty($old['not null']) && empty($cd['not null'])) {
- $phrase[] = $prefix . 'DROP NOT NULL';
- } else if (empty($old['not null']) && !empty($cd['not null'])) {
- $phrase[] = $prefix . 'SET NOT NULL';
- }
- if (isset($old['default']) && !isset($cd['default'])) {
- $phrase[] = $prefix . 'DROP DEFAULT';
- } else if (!isset($old['default']) && isset($cd['default'])) {
- $phrase[] = $prefix . 'SET DEFAULT ' . $this->quoteDefaultValue($cd);
- }
- }
-
- function appendDropIndex(array &$statements, $table, $name)
- {
- $statements[] = "DROP INDEX $name";
- }
-
- function quoteIdentifier($name)
- {
- return $this->conn->quoteIdentifier($name);
- }
- function mapType($column)
- {
- $map = [
- 'serial' => 'bigserial',
- 'numeric' => 'decimal',
- 'datetime' => 'timestamp',
- 'blob' => 'bytea'
- ];
- $type = $column['type'];
- if (isset($map[$type])) {
- $type = $map[$type];
- }
- if ($type == 'int') {
- if (!empty($column['size'])) {
- $size = $column['size'];
- if ($size == 'small') {
- return 'int2';
- } else if ($size == 'big') {
- return 'int8';
- }
- }
- return 'int4';
- }
- return $type;
- }
-
- function typeAndSize($column)
- {
- if ($column['type'] == 'enum') {
- $vals = array_map([$this, 'quote'], $column['enum']);
- return "text check ($name in " . implode(',', $vals) . ')';
- } else {
- return parent::typeAndSize($column);
- }
- }
-
- function filterDef(array $tableDef)
- {
- foreach ($tableDef['fields'] as $name => &$col) {
-
- unset($col['description']);
-
- $col['type'] = $this->mapType($col);
- unset($col['size']);
- }
- if (!empty($tableDef['primary key'])) {
- $tableDef['primary key'] = $this->filterKeyDef($tableDef['primary key']);
- }
- if (!empty($tableDef['unique keys'])) {
- foreach ($tableDef['unique keys'] as $i => $def) {
- $tableDef['unique keys'][$i] = $this->filterKeyDef($def);
- }
- }
- return $tableDef;
- }
-
- function filterKeyDef(array $def)
- {
-
- foreach ($def as $i => $item) {
- if (is_array($item)) {
- $def[$i] = $item[0];
- }
- }
- return $def;
- }
- }
|