2 // This file is part of GNU social - https://www.gnu.org/software/social
4 // GNU social is free software: you can redistribute it and/or modify
5 // it under the terms of the GNU Affero General Public License as published by
6 // the Free Software Foundation, either version 3 of the License, or
7 // (at your option) any later version.
9 // GNU social is distributed in the hope that it will be useful,
10 // but WITHOUT ANY WARRANTY; without even the implied warranty of
11 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 // GNU Affero General Public License for more details.
14 // You should have received a copy of the GNU Affero General Public License
15 // along with GNU social. If not, see <http://www.gnu.org/licenses/>.
18 * Database schema for PostgreSQL
22 * @author Evan Prodromou <evan@status.net>
23 * @author Brenda Wallace <shiny@cpan.org>
24 * @author Brion Vibber <brion@status.net>
25 * @copyright 2019 Free Software Foundation, Inc http://www.fsf.org
26 * @license https://www.gnu.org/licenses/agpl.html GNU AGPL v3 or later
29 defined('GNUSOCIAL') || die();
32 * Class representing the database schema for PostgreSQL
34 * A class representing the database schema. Can be used to
35 * manipulate the schema -- especially for plugins and upgrade
38 * @copyright 2019 Free Software Foundation, Inc http://www.fsf.org
39 * @license https://www.gnu.org/licenses/agpl.html GNU AGPL v3 or later
41 class PgsqlSchema extends Schema
45 * Returns a table definition array for the table
46 * in the schema with the given name.
48 * Throws an exception if the table is not found.
50 * @param string $table Name of the table to get
52 * @return array tabledef for that table.
53 * @throws SchemaTableMissingException
56 public function getTableDef($table)
61 // Pull column data from INFORMATION_SCHEMA
62 $columns = $this->fetchMetaInfo($table, 'columns', 'ordinal_position');
63 if (count($columns) == 0) {
64 throw new SchemaTableMissingException("No such table: $table");
67 // We'll need to match up fields by ordinal reference
70 foreach ($columns as $row) {
72 $name = $row['column_name'];
73 $orderedFields[$row['ordinal_position']] = $name;
76 $field['type'] = $type = $row['udt_name'];
78 if ($type == 'char' || $type == 'varchar') {
79 if ($row['character_maximum_length'] !== null) {
80 $field['length'] = intval($row['character_maximum_length']);
83 if ($type == 'numeric') {
84 // Other int types may report these values, but they're irrelevant.
86 if ($row['numeric_precision'] !== null) {
87 $field['precision'] = intval($row['numeric_precision']);
89 if ($row['numeric_scale'] !== null) {
90 $field['scale'] = intval($row['numeric_scale']);
93 if ($row['is_nullable'] == 'NO') {
94 $field['not null'] = true;
96 if ($row['column_default'] !== null) {
97 $field['default'] = $row['column_default'];
98 if ($this->isNumericType($type)) {
99 $field['default'] = intval($field['default']);
103 $def['fields'][$name] = $field;
106 // Pulling index info from pg_class & pg_index
107 // This can give us primary & unique key info, but not foreign key constraints
108 // so we exclude them and pick them up later.
109 $indexInfo = $this->getIndexInfo($table);
110 foreach ($indexInfo as $row) {
111 $keyName = $row['key_name'];
113 // Dig the column references out!
115 // These are inconvenient arrays with partial references to the
116 // pg_att table, but since we've already fetched up the column
117 // info on the current table, we can look those up locally.
119 $colPositions = explode(' ', $row['indkey']);
120 foreach ($colPositions as $ord) {
122 $cols[] = 'FUNCTION'; // @fixme
124 $cols[] = $orderedFields[$ord];
128 $def['indexes'][$keyName] = $cols;
131 // Pull constraint data from INFORMATION_SCHEMA:
132 // Primary key, unique keys, foreign keys
133 $keyColumns = $this->fetchMetaInfo($table, 'key_column_usage', 'constraint_name,ordinal_position');
136 foreach ($keyColumns as $row) {
137 $keyName = $row['constraint_name'];
138 $keyCol = $row['column_name'];
139 if (!isset($keys[$keyName])) {
140 $keys[$keyName] = [];
142 $keys[$keyName][] = $keyCol;
145 foreach ($keys as $keyName => $cols) {
146 // name hack -- is this reliable?
147 if ($keyName == "{$table}_pkey") {
148 $def['primary key'] = $cols;
149 } else if (preg_match("/^{$table}_(.*)_fkey$/", $keyName, $matches)) {
150 $fkey = $this->getForeignKeyInfo($table, $keyName);
151 $colMap = array_combine($cols, $fkey['col_names']);
152 $def['foreign keys'][$keyName] = [$fkey['table_name'], $colMap];
154 $def['unique keys'][$keyName] = $cols;
161 * Pull some INFORMATION.SCHEMA data for the given table.
163 * @param string $table
165 * @param null $orderBy
166 * @return array of arrays
167 * @throws PEAR_Exception
169 function fetchMetaInfo($table, $infoTable, $orderBy = null)
171 $query = "SELECT * FROM information_schema.%s " .
172 "WHERE table_name='%s'";
173 $sql = sprintf($query, $infoTable, $table);
175 $sql .= ' ORDER BY ' . $orderBy;
177 return $this->fetchQueryData($sql);
181 * Pull some PG-specific index info
182 * @param string $table
183 * @return array of arrays
184 * @throws PEAR_Exception
186 function getIndexInfo($table)
189 '(SELECT relname FROM pg_class WHERE oid=indexrelid) AS key_name, ' .
191 'WHERE indrelid=(SELECT oid FROM pg_class WHERE relname=\'%s\') ' .
192 'AND indisprimary=\'f\' AND indisunique=\'f\' ' .
193 'ORDER BY indrelid, indexrelid';
194 $sql = sprintf($query, $table);
195 return $this->fetchQueryData($sql);
199 * Column names from the foreign table can be resolved with a call to getTableColumnNames()
200 * @param string $table
201 * @param $constraint_name
202 * @return array array of rows with keys: fkey_name, table_name, table_id, col_names (array of strings)
203 * @throws PEAR_Exception
205 function getForeignKeyInfo($table, $constraint_name)
207 // In a sane world, it'd be easier to query the column names directly.
208 // But it's pretty hard to work with arrays such as col_indexes in direct SQL here.
210 '(SELECT relname FROM pg_class WHERE oid=confrelid) AS table_name, ' .
211 'confrelid AS table_id, ' .
212 '(SELECT indkey FROM pg_index WHERE indexrelid=conindid) AS col_indexes ' .
213 'FROM pg_constraint ' .
214 'WHERE conrelid=(SELECT oid FROM pg_class WHERE relname=\'%s\') ' .
215 'AND conname=\'%s\' ' .
217 $sql = sprintf($query, $table, $constraint_name);
218 $data = $this->fetchQueryData($sql);
219 if (count($data) < 1) {
220 throw new Exception("Could not find foreign key " . $constraint_name . " on table " . $table);
225 'table_name' => $row['table_name'],
226 'col_names' => $this->getTableColumnNames($row['table_id'], $row['col_indexes'])
232 * @param int $table_id
233 * @param array $col_indexes
234 * @return array of strings
235 * @throws PEAR_Exception
237 function getTableColumnNames($table_id, $col_indexes)
239 $indexes = array_map('intval', explode(' ', $col_indexes));
240 $query = 'SELECT attnum AS col_index, attname AS col_name ' .
241 'FROM pg_attribute where attrelid=%d ' .
242 'AND attnum IN (%s)';
243 $sql = sprintf($query, $table_id, implode(',', $indexes));
244 $data = $this->fetchQueryData($sql);
247 foreach ($data as $row) {
248 $byId[$row['col_index']] = $row['col_name'];
252 foreach ($indexes as $id) {
259 * Translate the (mostly) mysql-ish column types into somethings more standard
260 * @param string column type
262 * @return string postgres happy column type
264 private function _columnTypeTranslation($type)
267 'datetime' => 'timestamp',
269 if (!empty($map[$type])) {
276 * Return the proper SQL for creating or
279 * Appropriate for use in CREATE TABLE or
280 * ALTER TABLE statements.
282 * @param array $cd column to create
284 * @return string correct SQL for that column
287 function columnSql(array $cd)
290 $line[] = parent::columnSql($cd);
293 if ($table['foreign keys'][$name]) {
294 foreach ($table['foreign keys'][$name] as $foreignTable => $foreignColumn) {
295 $line[] = 'references';
296 $line[] = $this->quoteIdentifier($foreignTable);
297 $line[] = '(' . $this->quoteIdentifier($foreignColumn) . ')';
302 return implode(' ', $line);
306 * Append phrase(s) to an array of partial ALTER TABLE chunks in order
307 * to alter the given column from its old state to a new one.
309 * @param array $phrase
310 * @param string $columnName
311 * @param array $old previous column definition as found in DB
312 * @param array $cd current column definition
314 function appendAlterModifyColumn(array &$phrase, $columnName, array $old, array $cd)
316 $prefix = 'ALTER COLUMN ' . $this->quoteIdentifier($columnName) . ' ';
318 $oldType = $this->mapType($old);
319 $newType = $this->mapType($cd);
320 if ($oldType != $newType) {
321 $phrase[] = $prefix . 'TYPE ' . $newType;
324 if (!empty($old['not null']) && empty($cd['not null'])) {
325 $phrase[] = $prefix . 'DROP NOT NULL';
326 } else if (empty($old['not null']) && !empty($cd['not null'])) {
327 $phrase[] = $prefix . 'SET NOT NULL';
330 if (isset($old['default']) && !isset($cd['default'])) {
331 $phrase[] = $prefix . 'DROP DEFAULT';
332 } else if (!isset($old['default']) && isset($cd['default'])) {
333 $phrase[] = $prefix . 'SET DEFAULT ' . $this->quoteDefaultValue($cd);
338 * Append an SQL statement to drop an index from a table.
339 * Note that in PostgreSQL, index names are DB-unique.
341 * @param array $statements
342 * @param string $table
343 * @param string $name
345 function appendDropIndex(array &$statements, $table, $name)
347 $statements[] = "DROP INDEX $name";
351 * Quote a db/table/column identifier if necessary.
353 * @param string $name
356 function quoteIdentifier($name)
358 return $this->conn->quoteIdentifier($name);
361 function mapType($column)
364 'serial' => 'bigserial', // FIXME: creates the wrong name for the sequence for some internal sequence-lookup function, so better fix this to do the real 'create sequence' dance.
365 'numeric' => 'decimal',
366 'datetime' => 'timestamp',
370 $type = $column['type'];
371 if (isset($map[$type])) {
375 if ($type == 'int') {
376 if (!empty($column['size'])) {
377 $size = $column['size'];
378 if ($size == 'small') {
380 } else if ($size == 'big') {
390 // @fixme need name... :P
391 function typeAndSize($column)
393 if ($column['type'] == 'enum') {
394 $vals = array_map([$this, 'quote'], $column['enum']);
395 return "text check ($name in " . implode(',', $vals) . ')';
397 return parent::typeAndSize($column);
402 * Filter the given table definition array to match features available
405 * This lets us strip out unsupported things like comments, foreign keys,
406 * or type variants that we wouldn't get back from getTableDef().
408 * @param array $tableDef
411 function filterDef(array $tableDef)
413 foreach ($tableDef['fields'] as $name => &$col) {
414 // No convenient support for field descriptions
415 unset($col['description']);
418 if (isset($col['size'])) {
419 // Don't distinguish between tinyint and int.
420 if ($col['size'] == 'tiny' && $col['type'] == 'int') {
425 $col['type'] = $this->mapType($col);
428 if (!empty($tableDef['primary key'])) {
429 $tableDef['primary key'] = $this->filterKeyDef($tableDef['primary key']);
431 if (!empty($tableDef['unique keys'])) {
432 foreach ($tableDef['unique keys'] as $i => $def) {
433 $tableDef['unique keys'][$i] = $this->filterKeyDef($def);
440 * Filter the given key/index definition to match features available
446 function filterKeyDef(array $def)
448 // PostgreSQL doesn't like prefix lengths specified on keys...?
449 foreach ($def as $i => $item) {
450 if (is_array($item)) {