Table::getFieldTypes() - Code Metrics - Inspection of "Improved table feature." - lagdo/dbadmin-driver-pgsql - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — main ( bee40e...04eeaf )

by Thierry

created 2021-10-29 02:51 UTC

Table::getFieldTypes() A

↳ Parent: Table

Complexity

Conditions	2
Paths	2

Size

Total Lines	17
Code Lines	13

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	2
eloc	13
c	1
b	0
f	0
nc	2
nop	1
dl	0
loc	17
rs	9.8333

<?php

namespace Lagdo\DbAdmin\Driver\PgSql\Db;

use Lagdo\DbAdmin\Driver\Entity\TableFieldEntity;
use Lagdo\DbAdmin\Driver\Entity\TableEntity;
use Lagdo\DbAdmin\Driver\Entity\IndexEntity;
use Lagdo\DbAdmin\Driver\Entity\ForeignKeyEntity;
use Lagdo\DbAdmin\Driver\Entity\TriggerEntity;

use Lagdo\DbAdmin\Driver\Db\ConnectionInterface;

use Lagdo\DbAdmin\Driver\Db\Table as AbstractTable;

class Table extends AbstractTable
{
    /**
     * @param string $table
     *
     * @return array
     */
    private function queryStatus(string $table = '')
    {
        $query = "SELECT c.relname AS \"Name\", CASE c.relkind " .
            "WHEN 'r' THEN 'table' WHEN 'm' THEN 'materialized view' ELSE 'view' END AS \"Engine\", " .
            "pg_relation_size(c.oid) AS \"Data_length\", " .
            "pg_total_relation_size(c.oid) - pg_relation_size(c.oid) AS \"Index_length\", " .
            "obj_description(c.oid, 'pg_class') AS \"Comment\", " .
            ($this->driver->minVersion(12) ? "''" : "CASE WHEN c.relhasoids THEN 'oid' ELSE '' END") .
            " AS \"Oid\", c.reltuples as \"Rows\", n.nspname FROM pg_class c " .
            "JOIN pg_namespace n ON(n.nspname = current_schema() AND n.oid = c.relnamespace) " .
            "WHERE relkind IN ('r', 'm', 'v', 'f', 'p') " .
            ($table != "" ? "AND relname = " . $this->driver->quote($table) : "ORDER BY relname");
        return $this->driver->rows($query);
    }

    /**
     * @param array $row
     *
     * @return TableEntity
     */
    private function makeStatus(array $row)
    {
        $status = new TableEntity($row['Name']);
        $status->engine = $row['Engine'];
        $status->schema = $row['nspname'];
        $status->dataLength = $row['Data_length'];
        $status->indexLength = $row['Index_length'];
        $status->oid = $row['Oid'];
        $status->rows = $row['Rows'];
        $status->comment = $row['Comment'];

        return $status;
    }

    /**
     * @inheritDoc
     */
    public function tableStatus(string $table, bool $fast = false)
    {
        $rows = $this->queryStatus($table);
        if (!($row = reset($rows))) {
            return null;
        }
        return $this->makeStatus($row);
    }

    /**
     * @inheritDoc
     */
    public function tableStatuses(bool $fast = false)
    {
        $tables = [];
        $rows = $this->queryStatus();
        foreach ($rows as $row) {
            $tables[$row["Name"]] = $this->makeStatus($row);
        }
        return $tables;
    }

    /**
     * @inheritDoc
     */
    public function tableNames()
    {
        $tables = [];
        $rows = $this->queryStatus();
        foreach ($rows as $row) {
            $tables[] = $row["Name"];
        }
        return $tables;
    }

    /**
     * @inheritDoc
     */
    public function isView(TableEntity $tableStatus)
    {
        return in_array($tableStatus->engine, ["view", "materialized view"]);
    }

    /**
     * @inheritDoc
     */
    public function supportForeignKeys(TableEntity $tableStatus)
    {
        return true;
    }

    /**
     * @inheritDoc
     */
    public function referencableTables(string $table)
    {
        $fields = []; // table_name => [field]
        foreach ($this->tableNames() as $tableName) {
            if ($tableName === $table) {
                continue;
            }
            foreach ($this->fields($tableName) as $field) {
                if ($field->primary) {
                    if (!isset($fields[$tableName])) {
                        $fields[$tableName] = $field;
                    } else {
                        // No multi column primary key
                        $fields[$tableName] = null;
                    }
                }
            }
        }
        return array_filter($fields, function($field) {
            return $field !== null;
        });
    }

    /**
     * Get the primary key of a table
     * Same as indexes(), but the columns of the primary key are returned in a array
     *
     * @param string $table
     *
     * @return array
     */
    private function primaryKeyColumns(string $table)
    {
        $indexes = [];
        $table_oid = $this->connection->result("SELECT oid FROM pg_class WHERE " .
            "relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = current_schema()) " .
            "AND relname = " . $this->driver->quote($table));
        $columns = $this->driver->keyValues("SELECT attnum, attname FROM pg_attribute WHERE " .
            "attrelid = $table_oid AND attnum > 0");
        foreach ($this->driver->rows("SELECT relname, indisunique::int, indisprimary::int, indkey, " .
            "indoption, (indpred IS NOT NULL)::int as indispartial FROM pg_index i, pg_class ci " .
            "WHERE i.indrelid = $table_oid AND ci.oid = i.indexrelid") as $row)
        {
            // $relname = $row["relname"];
            if ($row["indisprimary"]) {
                foreach (explode(" ", $row["indkey"]) as $indkey) {
                    $indexes[] = $columns[$indkey];
                }
            }
        }
        return $indexes;
    }

    private function getFieldDefault(array $row)
    {
        $default = $row["default"];
        if ($row['identity'] === 'a') {
            $default = 'GENERATED ALWAYS AS IDENTITY';
        }
        if ($row['identity'] === 'd') {
            $default = 'GENERATED BY DEFAULT AS IDENTITY';
        }
        if (preg_match('~(.+)::[^,)]+(.*)~', $row["default"], $match)) {
            $match1 = $match[1] ?? '';
            $match10 = $match1[0] ?? '';
            $match2 = $match[2] ?? '';
            if ($match1 == "NULL") {
                $default = null;
            } else {
                $default = ($match10 == "'" ? $this->driver->unescapeId($match1) : $match1) . $match2;
            }
        }
        return $default;
    }

    private function getFieldTypes(array $row)
    {
        $aliases = [
            'timestamp without time zone' => 'timestamp',
            'timestamp with time zone' => 'timestamptz',
        ];
        preg_match('~([^([]+)(\((.*)\))?([a-z ]+)?((\[[0-9]*])*)$~', $row["full_type"], $match);
        list(, $type, $_length, $length, $addon, $array) = $match;
        $length .= $array;
        $checkType = $type . $addon;
        if (isset($aliases[$checkType])) {
            $type = $aliases[$checkType];
            $fullType = $type . $_length . $array;
            return [$length, $type, $fullType];
        }
        $fullType = $type . $_length . $addon . $array;
        return [$length, $type, $fullType];
    }

    /**
     * @param array $row
     * @param array $primaryKeyColumns
     *
     * @return TableFieldEntity
     */
    private function makeFieldEntity(array $row, array $primaryKeyColumns)
    {
        $field = new TableFieldEntity();

        $field->name = $row["field"];
        $field->primary = \in_array($field->name, $primaryKeyColumns);
        $field->fullType = $row["full_type"];
        $field->default = $this->getFieldDefault($row);
        $field->comment = $row["comment"];
        //! No collation, no info about primary keys
        list($field->length, $field->type, $field->fullType) = $this->getFieldTypes($row);
        $field->null = !$row["attnotnull"];
        $field->autoIncrement = $row['identity'] || preg_match('~^nextval\(~i', $row["default"]);
        $field->privileges = ["insert" => 1, "select" => 1, "update" => 1];
        return $field;
    }

    /**
     * @inheritDoc
     */
    public function fields(string $table)
    {
        $fields = [];

        // Primary keys
        $primaryKeyColumns = $this->primaryKeyColumns($table);

        $identity_column = $this->driver->minVersion(10) ? 'a.attidentity' : '0';
        $query = "SELECT a.attname AS field, format_type(a.atttypid, a.atttypmod) AS full_type, " .
            "pg_get_expr(d.adbin, d.adrelid) AS default, a.attnotnull::int, " .
            "col_description(c.oid, a.attnum) AS comment, $identity_column AS identity FROM pg_class c " .
            "JOIN pg_namespace n ON c.relnamespace = n.oid JOIN pg_attribute a ON c.oid = a.attrelid " .
            "LEFT JOIN pg_attrdef d ON c.oid = d.adrelid AND a.attnum = d.adnum WHERE c.relname = " .
            $this->driver->quote($table) .
            " AND n.nspname = current_schema() AND NOT a.attisdropped AND a.attnum > 0 ORDER BY a.attnum";
        foreach ($this->driver->rows($query) as $row)
        {
            $fields[$row["field"]] = $this->makeFieldEntity($row, $primaryKeyColumns);
        }
        return $fields;
    }

    /**
     * @param array $row
     *
     * @return string
     */
    private function getIndexType(array $row)
    {
        if ($row['indispartial']) {
            return 'INDEX';
        }
        if ($row['indisprimary']) {
            return 'PRIMARY';
        }
        if ($row['indisunique']) {
            return 'UNIQUE';
        }
        return 'INDEX';
    }

    /**
     * @param array $row
     * @param array $columns
     *
     * @return IndexEntity
     */
    private function makeIndexEntity(array $row, array $columns)
    {
        $index = new IndexEntity();

        $index->type = $this->getIndexType($row);
        $index->columns = [];
        foreach (explode(' ', $row['indkey']) as $indkey) {
            $index->columns[] = $columns[$indkey];
        }
        $index->descs = [];
        foreach (explode(' ', $row['indoption']) as $indoption) {
            $index->descs[] = ($indoption & 1 ? '1' : null); // 1 - INDOPTION_DESC
        }
        $index->lengths = [];

        return $index;
    }

    /**
     * @inheritDoc
     */
    public function indexes(string $table, ConnectionInterface $connection = null)
    {
        if (!$connection) {
            $connection = $this->connection;
        }
        $indexes = [];
        $table_oid = $connection->result("SELECT oid FROM pg_class WHERE " .
            "relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = current_schema()) " .
            "AND relname = " . $this->driver->quote($table));
        $columns = $this->driver->keyValues("SELECT attnum, attname FROM pg_attribute WHERE " .
            "attrelid = $table_oid AND attnum > 0", $connection);
        $query = "SELECT relname, indisunique::int, indisprimary::int, indkey, indoption, " .
            "(indpred IS NOT NULL)::int as indispartial FROM pg_index i, pg_class ci " .
            "WHERE i.indrelid = $table_oid AND ci.oid = i.indexrelid";
        foreach ($this->driver->rows($query, $connection) as $row)
        {
            $indexes[$row["relname"]] = $this->makeIndexEntity($row, $columns);
        }
        return $indexes;
    }

    /**
     * @param array $row
     *
     * @return ForeignKeyEntity
     */
    private function makeForeignKeyEntity(array $row)
    {
        if (!preg_match('~FOREIGN KEY\s*\((.+)\)\s*REFERENCES (.+)\((.+)\)(.*)$~iA', $row['definition'], $match)) {
            return null;
        }
        $onActions = $this->driver->actions();

        $match1 = $match[1] ?? '';
        $match2 = $match[2] ?? '';
        $match3 = $match[3] ?? '';
        $match4 = $match[4] ?? '';
        $match11 = '';

        $foreignKey = new ForeignKeyEntity();

        $foreignKey->source = array_map('trim', explode(',', $match1));
        $foreignKey->target = array_map('trim', explode(',', $match3));
        $foreignKey->onDelete = preg_match("~ON DELETE ($onActions)~", $match4, $match10) ? $match11 : 'NO ACTION';
        $foreignKey->onUpdate = preg_match("~ON UPDATE ($onActions)~", $match4, $match10) ? $match11 : 'NO ACTION';

        if (preg_match('~^(("([^"]|"")+"|[^"]+)\.)?"?("([^"]|"")+"|[^"]+)$~', $match2, $match10)) {
            // $match11 = $match10[1] ?? '';
            $match12 = $match10[2] ?? '';
            // $match13 = $match10[3] ?? '';
            $match14 = $match10[4] ?? '';
            $foreignKey->schema = str_replace('""', '"', preg_replace('~^"(.+)"$~', '\1', $match12));
            $foreignKey->table = str_replace('""', '"', preg_replace('~^"(.+)"$~', '\1', $match14));
        }

        return $foreignKey;
    }

    /**
     * @inheritDoc
     */
    public function foreignKeys(string $table)
    {
        $foreignKeys = [];
        $query = "SELECT conname, condeferrable::int AS deferrable, pg_get_constraintdef(oid) " .
            "AS definition FROM pg_constraint WHERE conrelid = (SELECT pc.oid FROM pg_class AS pc " .
            "INNER JOIN pg_namespace AS pn ON (pn.oid = pc.relnamespace) WHERE pc.relname = " .
            $this->driver->quote($table) .
            " AND pn.nspname = current_schema()) AND contype = 'f'::char ORDER BY conkey, conname";
        foreach ($this->driver->rows($query) as $row) {
            $foreignKey = $this->makeForeignKeyEntity($row);
            if ($foreignKey !== null) {
                $foreignKeys[$row['conname']] = $foreignKey;
            }
        }
        return $foreignKeys;
    }

    /**
     * @inheritDoc
     */
    public function trigger(string $name, string $table = '')
    {
        if ($name == '') {
            return new TriggerEntity('', '', 'EXECUTE PROCEDURE ()');
        }
        if ($table === '') {
            $table = $this->util->input()->getTable();
        }
        $query = 'SELECT t.trigger_name AS "Trigger", t.action_timing AS "Timing", ' .
            '(SELECT STRING_AGG(event_manipulation, \' OR \') FROM information_schema.triggers ' .
            'WHERE event_object_table = t.event_object_table AND trigger_name = t.trigger_name ) AS "Events", ' .
            't.event_manipulation AS "Event", \'FOR EACH \' || t.action_orientation AS "Type", ' .
            't.action_statement AS "Statement" FROM information_schema.triggers t WHERE t.event_object_table = ' .
            $this->driver->quote($table) . ' AND t.trigger_name = ' . $this->driver->quote($name);
        $rows = $this->driver->rows($query);
        if (!($row = reset($rows))) {
            return null;
        }
        return new TriggerEntity($row['Timing'], $row['Event'], $row['Statement'], '', $row['Trigger']);
    }

    /**
     * @inheritDoc
     */
    public function triggers(string $table)
    {
        $triggers = [];
        $query = "SELECT * FROM information_schema.triggers WHERE trigger_schema = current_schema() " .
            "AND event_object_table = " . $this->driver->quote($table);
        foreach ($this->driver->rows($query) as $row) {
            $triggers[$row["trigger_name"]] = new TriggerEntity($row["action_timing"],
                $row["event_manipulation"], '', '', $row["trigger_name"]);
        }
        return $triggers;
    }

    /**
     * @inheritDoc
     */
    public function triggerOptions()
    {
        return [
            "Timing" => ["BEFORE", "AFTER"],
            "Event" => ["INSERT", "UPDATE", "DELETE"],
            "Type" => ["FOR EACH ROW", "FOR EACH STATEMENT"],
        ];
    }

    /**
     * @inheritDoc
     */
    public function tableHelp(string $name)
    {
        $links = [
            "information_schema" => "infoschema",
            "pg_catalog" => "catalog",
        ];
        $link = $links[$this->driver->schema()];
        if ($link) {
            return "$link-" . str_replace("_", "-", $name) . ".html";
        }
    }
}


1			<?php
2
3			namespace Lagdo\DbAdmin\Driver\PgSql\Db;
4
5			use Lagdo\DbAdmin\Driver\Entity\TableFieldEntity;
6			use Lagdo\DbAdmin\Driver\Entity\TableEntity;
7			use Lagdo\DbAdmin\Driver\Entity\IndexEntity;
8			use Lagdo\DbAdmin\Driver\Entity\ForeignKeyEntity;
9			use Lagdo\DbAdmin\Driver\Entity\TriggerEntity;
10
11			use Lagdo\DbAdmin\Driver\Db\ConnectionInterface;
12
13			use Lagdo\DbAdmin\Driver\Db\Table as AbstractTable;
14
15			class Table extends AbstractTable
16			{
17			/**
18			* @param string $table
19			*
20			* @return array
21			*/
22			private function queryStatus(string $table = '')
23			{
24			$query = "SELECT c.relname AS \"Name\", CASE c.relkind " .
25			"WHEN 'r' THEN 'table' WHEN 'm' THEN 'materialized view' ELSE 'view' END AS \"Engine\", " .
26			"pg_relation_size(c.oid) AS \"Data_length\", " .
27			"pg_total_relation_size(c.oid) - pg_relation_size(c.oid) AS \"Index_length\", " .
28			"obj_description(c.oid, 'pg_class') AS \"Comment\", " .
29			($this->driver->minVersion(12) ? "''" : "CASE WHEN c.relhasoids THEN 'oid' ELSE '' END") .
30			" AS \"Oid\", c.reltuples as \"Rows\", n.nspname FROM pg_class c " .
31			"JOIN pg_namespace n ON(n.nspname = current_schema() AND n.oid = c.relnamespace) " .
32			"WHERE relkind IN ('r', 'm', 'v', 'f', 'p') " .
33			($table != "" ? "AND relname = " . $this->driver->quote($table) : "ORDER BY relname");
34			return $this->driver->rows($query);
35			}
36
37			/**
38			* @param array $row
39			*
40			* @return TableEntity
41			*/
42			private function makeStatus(array $row)
43			{
44			$status = new TableEntity($row['Name']);
45			$status->engine = $row['Engine'];
46			$status->schema = $row['nspname'];
47			$status->dataLength = $row['Data_length'];
48			$status->indexLength = $row['Index_length'];
49			$status->oid = $row['Oid'];
50			$status->rows = $row['Rows'];
51			$status->comment = $row['Comment'];
52
53			return $status;
54			}
55
56			/**
57			* @inheritDoc
58			*/
59			public function tableStatus(string $table, bool $fast = false)
60			{
61			$rows = $this->queryStatus($table);
62			if (!($row = reset($rows))) {
63			return null;
64			}
65			return $this->makeStatus($row);
66			}
67
68			/**
69			* @inheritDoc
70			*/
71			public function tableStatuses(bool $fast = false)
72			{
73			$tables = [];
74			$rows = $this->queryStatus();
75			foreach ($rows as $row) {
76			$tables[$row["Name"]] = $this->makeStatus($row);
77			}
78			return $tables;
79			}
80
81			/**
82			* @inheritDoc
83			*/
84			public function tableNames()
85			{
86			$tables = [];
87			$rows = $this->queryStatus();
88			foreach ($rows as $row) {
89			$tables[] = $row["Name"];
90			}
91			return $tables;
92			}
93
94			/**
95			* @inheritDoc
96			*/
97			public function isView(TableEntity $tableStatus)
98			{
99			return in_array($tableStatus->engine, ["view", "materialized view"]);
100			}
101
102			/**
103			* @inheritDoc
104			*/
105			public function supportForeignKeys(TableEntity $tableStatus)
106			{
107			return true;
108			}
109
110			/**
111			* @inheritDoc
112			*/
113			public function referencableTables(string $table)
114			{
115			$fields = []; // table_name => [field]
116			foreach ($this->tableNames() as $tableName) {
117			if ($tableName === $table) {
118			continue;
119			}
120			foreach ($this->fields($tableName) as $field) {
121			if ($field->primary) {
122			if (!isset($fields[$tableName])) {
123			$fields[$tableName] = $field;
124			} else {
125			// No multi column primary key
126			$fields[$tableName] = null;
127			}
128			}
129			}
130			}
131			return array_filter($fields, function($field) {
132			return $field !== null;
133			});
134			}
135
136			/**
137			* Get the primary key of a table
138			* Same as indexes(), but the columns of the primary key are returned in a array
139			*
140			* @param string $table
141			*
142			* @return array
143			*/
144			private function primaryKeyColumns(string $table)
145			{
146			$indexes = [];
147			$table_oid = $this->connection->result("SELECT oid FROM pg_class WHERE " .
148			"relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = current_schema()) " .
149			"AND relname = " . $this->driver->quote($table));
150			$columns = $this->driver->keyValues("SELECT attnum, attname FROM pg_attribute WHERE " .
151			"attrelid = $table_oid AND attnum > 0");
152			foreach ($this->driver->rows("SELECT relname, indisunique::int, indisprimary::int, indkey, " .
153			"indoption, (indpred IS NOT NULL)::int as indispartial FROM pg_index i, pg_class ci " .
154			"WHERE i.indrelid = $table_oid AND ci.oid = i.indexrelid") as $row)
155			{
156			// $relname = $row["relname"];
157			if ($row["indisprimary"]) {
158			foreach (explode(" ", $row["indkey"]) as $indkey) {
159			$indexes[] = $columns[$indkey];
160			}
161			}
162			}
163			return $indexes;
164			}
165
166			private function getFieldDefault(array $row)
167			{
168			$default = $row["default"];
169			if ($row['identity'] === 'a') {
170			$default = 'GENERATED ALWAYS AS IDENTITY';
171			}
172			if ($row['identity'] === 'd') {
173			$default = 'GENERATED BY DEFAULT AS IDENTITY';
174			}
175			if (preg_match('~(.+)::[^,)]+(.*)~', $row["default"], $match)) {
176			$match1 = $match[1] ?? '';
177			$match10 = $match1[0] ?? '';
178			$match2 = $match[2] ?? '';
179			if ($match1 == "NULL") {
180			$default = null;
181			} else {
182			$default = ($match10 == "'" ? $this->driver->unescapeId($match1) : $match1) . $match2;
183			}
184			}
185			return $default;
186			}
187
188			private function getFieldTypes(array $row)
189			{
190			$aliases = [
191			'timestamp without time zone' => 'timestamp',
192			'timestamp with time zone' => 'timestamptz',
193			];
194			preg_match('~([^([]+)(\((.)\))?([a-z ]+)?((\[[0-9]])*)$~', $row["full_type"], $match);
195			list(, $type, $_length, $length, $addon, $array) = $match;
196			$length .= $array;
197			$checkType = $type . $addon;
198			if (isset($aliases[$checkType])) {
199			$type = $aliases[$checkType];
200			$fullType = $type . $_length . $array;
201			return [$length, $type, $fullType];
202			}
203			$fullType = $type . $_length . $addon . $array;
204			return [$length, $type, $fullType];
205			}
206
207			/**
208			* @param array $row
209			* @param array $primaryKeyColumns
210			*
211			* @return TableFieldEntity
212			*/
213			private function makeFieldEntity(array $row, array $primaryKeyColumns)
214			{
215			$field = new TableFieldEntity();
216
217			$field->name = $row["field"];
218			$field->primary = \in_array($field->name, $primaryKeyColumns);
219			$field->fullType = $row["full_type"];
220			$field->default = $this->getFieldDefault($row);
221			$field->comment = $row["comment"];
222			//! No collation, no info about primary keys
223			list($field->length, $field->type, $field->fullType) = $this->getFieldTypes($row);
224			$field->null = !$row["attnotnull"];
225			$field->autoIncrement = $row['identity'] \|\| preg_match('~^nextval\(~i', $row["default"]);
226			$field->privileges = ["insert" => 1, "select" => 1, "update" => 1];
227			return $field;
228			}
229
230			/**
231			* @inheritDoc
232			*/
233			public function fields(string $table)
234			{
235			$fields = [];
236
237			// Primary keys
238			$primaryKeyColumns = $this->primaryKeyColumns($table);
239
240			$identity_column = $this->driver->minVersion(10) ? 'a.attidentity' : '0';
241			$query = "SELECT a.attname AS field, format_type(a.atttypid, a.atttypmod) AS full_type, " .
242			"pg_get_expr(d.adbin, d.adrelid) AS default, a.attnotnull::int, " .
243			"col_description(c.oid, a.attnum) AS comment, $identity_column AS identity FROM pg_class c " .
244			"JOIN pg_namespace n ON c.relnamespace = n.oid JOIN pg_attribute a ON c.oid = a.attrelid " .
245			"LEFT JOIN pg_attrdef d ON c.oid = d.adrelid AND a.attnum = d.adnum WHERE c.relname = " .
246			$this->driver->quote($table) .
247			" AND n.nspname = current_schema() AND NOT a.attisdropped AND a.attnum > 0 ORDER BY a.attnum";
248			foreach ($this->driver->rows($query) as $row)
249			{
250			$fields[$row["field"]] = $this->makeFieldEntity($row, $primaryKeyColumns);
251			}
252			return $fields;
253			}
254
255			/**
256			* @param array $row
257			*
258			* @return string
259			*/
260			private function getIndexType(array $row)
261			{
262			if ($row['indispartial']) {
263			return 'INDEX';
264			}
265			if ($row['indisprimary']) {
266			return 'PRIMARY';
267			}
268			if ($row['indisunique']) {
269			return 'UNIQUE';
270			}
271			return 'INDEX';
272			}
273
274			/**
275			* @param array $row
276			* @param array $columns
277			*
278			* @return IndexEntity
279			*/
280			private function makeIndexEntity(array $row, array $columns)
281			{
282			$index = new IndexEntity();
283
284			$index->type = $this->getIndexType($row);
285			$index->columns = [];
286			foreach (explode(' ', $row['indkey']) as $indkey) {
287			$index->columns[] = $columns[$indkey];
288			}
289			$index->descs = [];
290			foreach (explode(' ', $row['indoption']) as $indoption) {
291			$index->descs[] = ($indoption & 1 ? '1' : null); // 1 - INDOPTION_DESC
292			}
293			$index->lengths = [];
294
295			return $index;
296			}
297
298			/**
299			* @inheritDoc
300			*/
301			public function indexes(string $table, ConnectionInterface $connection = null)
302			{
303			if (!$connection) {
304			$connection = $this->connection;
305			}
306			$indexes = [];
307			$table_oid = $connection->result("SELECT oid FROM pg_class WHERE " .
308			"relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = current_schema()) " .
309			"AND relname = " . $this->driver->quote($table));
310			$columns = $this->driver->keyValues("SELECT attnum, attname FROM pg_attribute WHERE " .
311			"attrelid = $table_oid AND attnum > 0", $connection);
312			$query = "SELECT relname, indisunique::int, indisprimary::int, indkey, indoption, " .
313			"(indpred IS NOT NULL)::int as indispartial FROM pg_index i, pg_class ci " .
314			"WHERE i.indrelid = $table_oid AND ci.oid = i.indexrelid";
315			foreach ($this->driver->rows($query, $connection) as $row)
316			{
317			$indexes[$row["relname"]] = $this->makeIndexEntity($row, $columns);
318			}
319			return $indexes;
320			}
321
322			/**
323			* @param array $row
324			*
325			* @return ForeignKeyEntity
326			*/
327			private function makeForeignKeyEntity(array $row)
328			{
329			if (!preg_match('~FOREIGN KEY\s\((.+)\)\sREFERENCES (.+)\((.+)\)(.*)$~iA', $row['definition'], $match)) {
330			return null;
331			}
332			$onActions = $this->driver->actions();
333
334			$match1 = $match[1] ?? '';
335			$match2 = $match[2] ?? '';
336			$match3 = $match[3] ?? '';
337			$match4 = $match[4] ?? '';
338			$match11 = '';
339
340			$foreignKey = new ForeignKeyEntity();
341
342			$foreignKey->source = array_map('trim', explode(',', $match1));
343			$foreignKey->target = array_map('trim', explode(',', $match3));
344			$foreignKey->onDelete = preg_match("~ON DELETE ($onActions)~", $match4, $match10) ? $match11 : 'NO ACTION';
345			$foreignKey->onUpdate = preg_match("~ON UPDATE ($onActions)~", $match4, $match10) ? $match11 : 'NO ACTION';
346
347			if (preg_match('~^(("([^"]\|"")+"\|[^"]+)\.)?"?("([^"]\|"")+"\|[^"]+)$~', $match2, $match10)) {
348			// $match11 = $match10[1] ?? '';
349			$match12 = $match10[2] ?? '';
350			// $match13 = $match10[3] ?? '';
351			$match14 = $match10[4] ?? '';
352			$foreignKey->schema = str_replace('""', '"', preg_replace('~^"(.+)"$~', '\1', $match12));
353			$foreignKey->table = str_replace('""', '"', preg_replace('~^"(.+)"$~', '\1', $match14));
354			}
355
356			return $foreignKey;
357			}
358
359			/**
360			* @inheritDoc
361			*/
362			public function foreignKeys(string $table)
363			{
364			$foreignKeys = [];
365			$query = "SELECT conname, condeferrable::int AS deferrable, pg_get_constraintdef(oid) " .
366			"AS definition FROM pg_constraint WHERE conrelid = (SELECT pc.oid FROM pg_class AS pc " .
367			"INNER JOIN pg_namespace AS pn ON (pn.oid = pc.relnamespace) WHERE pc.relname = " .
368			$this->driver->quote($table) .
369			" AND pn.nspname = current_schema()) AND contype = 'f'::char ORDER BY conkey, conname";
370			foreach ($this->driver->rows($query) as $row) {
371			$foreignKey = $this->makeForeignKeyEntity($row);
372			if ($foreignKey !== null) {
373			$foreignKeys[$row['conname']] = $foreignKey;
374			}
375			}
376			return $foreignKeys;
377			}
378
379			/**
380			* @inheritDoc
381			*/
382			public function trigger(string $name, string $table = '')
383			{
384			if ($name == '') {
385			return new TriggerEntity('', '', 'EXECUTE PROCEDURE ()');
386			}
387			if ($table === '') {
388			$table = $this->util->input()->getTable();
389			}
390			$query = 'SELECT t.trigger_name AS "Trigger", t.action_timing AS "Timing", ' .
391			'(SELECT STRING_AGG(event_manipulation, \' OR \') FROM information_schema.triggers ' .
392			'WHERE event_object_table = t.event_object_table AND trigger_name = t.trigger_name ) AS "Events", ' .
393			't.event_manipulation AS "Event", \'FOR EACH \' \|\| t.action_orientation AS "Type", ' .
394			't.action_statement AS "Statement" FROM information_schema.triggers t WHERE t.event_object_table = ' .
395			$this->driver->quote($table) . ' AND t.trigger_name = ' . $this->driver->quote($name);
396			$rows = $this->driver->rows($query);
397			if (!($row = reset($rows))) {
398			return null;
399			}
400			return new TriggerEntity($row['Timing'], $row['Event'], $row['Statement'], '', $row['Trigger']);
401			}
402
403			/**
404			* @inheritDoc
405			*/
406			public function triggers(string $table)
407			{
408			$triggers = [];
409			$query = "SELECT * FROM information_schema.triggers WHERE trigger_schema = current_schema() " .
410			"AND event_object_table = " . $this->driver->quote($table);
411			foreach ($this->driver->rows($query) as $row) {
412			$triggers[$row["trigger_name"]] = new TriggerEntity($row["action_timing"],
413			$row["event_manipulation"], '', '', $row["trigger_name"]);
414			}
415			return $triggers;
416			}
417
418			/**
419			* @inheritDoc
420			*/
421			public function triggerOptions()
422			{
423			return [
424			"Timing" => ["BEFORE", "AFTER"],
425			"Event" => ["INSERT", "UPDATE", "DELETE"],
426			"Type" => ["FOR EACH ROW", "FOR EACH STATEMENT"],
427			];
428			}
429
430			/**
431			* @inheritDoc
432			*/
433			public function tableHelp(string $name)
434			{
435			$links = [
436			"information_schema" => "infoschema",
437			"pg_catalog" => "catalog",
438			];
439			$link = $links[$this->driver->schema()];
440			if ($link) {
441			return "$link-" . str_replace("_", "-", $name) . ".html";
442			}
443			}
444			}
445

lagdo / dbadmin-driver-pgsql

Push — main ( bee40e...04eeaf )

Table::getFieldTypes() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like