Grammar::constraints() - Code Metrics - Inspection of "New classes for database features." - lagdo/dbadmin-driver-pgsql - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Branch — main (a6702e)

by Thierry

created 2021-10-28 21:29 UTC

Grammar::constraints() A

↳ Parent: Grammar

Complexity

Conditions	2
Paths	2

Size

Total Lines	15
Code Lines	10

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	2
eloc	10
c	0
b	0
f	0
nc	2
nop	1
dl	0
loc	15
rs	9.9332

<?php

namespace Lagdo\DbAdmin\Driver\PgSql\Db;

use Lagdo\DbAdmin\Driver\Db\Grammar as AbstractGrammar;

class Grammar extends AbstractGrammar
{
    /**
     * @inheritDoc
     */
    public function escapeId($idf)
    {
        return '"' . str_replace('"', '""', $idf) . '"';
    }

    /**
     * @inheritDoc
     */
    public function limit(string $query, string $where, int $limit, int $offset = 0, string $separator = " ")
    {
        return " $query$where" . ($limit !== 0 ? $separator . "LIMIT $limit" .
            ($offset ? " OFFSET $offset" : "") : "");
    }

    private function constraints(string $table)
    {
        $constraints = [];
        $query = "SELECT conname, consrc FROM pg_catalog.pg_constraint " .
            "INNER JOIN pg_catalog.pg_namespace ON pg_constraint.connamespace = pg_namespace.oid " .
            "INNER JOIN pg_catalog.pg_class ON pg_constraint.conrelid = pg_class.oid " .
            "AND pg_constraint.connamespace = pg_class.relnamespace WHERE pg_constraint.contype = 'c' " .
            // "-- handle only CONSTRAINTs here, not TYPES " .
            "AND conrelid != 0  AND nspname = current_schema() AND relname = " .
            $this->driver->quote($table) . "ORDER BY connamespace, conname";
        foreach ($this->driver->rows($query) as $row)
        {
            $constraints[$row['conname']] = $row['consrc'];
        }
        return $constraints;
    }

    /**
     * @inheritDoc
     */
    public function sqlForForeignKeys(string $table)
    {
        $query = "";

        $status = $this->driver->tableStatus($table);
        $fkeys = $this->driver->foreignKeys($table);
        ksort($fkeys);

        foreach ($fkeys as $fkey_name => $fkey) {
            $query .= "ALTER TABLE ONLY " . $this->escapeId($status->schema) . "." .
                $this->escapeId($status->name) . " ADD CONSTRAINT " . $this->escapeId($fkey_name) .
                " {$fkey->definition} " . ($fkey->deferrable ? 'DEFERRABLE' : 'NOT DEFERRABLE') . ";\n";
        }

        return ($query ? "$query\n" : $query);
    }

    /**
     * @inheritDoc
     */
    public function sqlForCreateTable(string $table, bool $autoIncrement, string $style)
    {
        $clauses = [];
        $sequences = [];

        $status = $this->driver->tableStatus($table);
        if ($status !== null && $this->driver->isView($status)) {
            $view = $this->driver->view($table);
            return rtrim("CREATE VIEW " . $this->escapeId($table) . " AS $view[select]", ";");
        }
        $fields = $this->driver->fields($table);
        $indexes = $this->driver->indexes($table);
        ksort($indexes);
        $constraints = $this->constraints($table);

        if (empty($status) || empty($fields)) {
            return '';
        }

        $query = "CREATE TABLE " . $this->escapeId($status->schema) . "." .
            $this->escapeId($status->name) . " (\n    ";

        // fields' definitions
        foreach ($fields as $field_name => $field) {
            $part = $this->escapeId($field->name) . ' ' . $field->fullType .
                $this->driver->defaultValue($field) . ($field->attnotnull ? " NOT NULL" : "");
            $clauses[] = $part;

            // sequences for fields
            if (preg_match('~nextval\(\'([^\']+)\'\)~', $field->default, $matches)) {
                $sequence_name = $matches[1];
                $sq = reset($this->driver->rows($this->driver->minVersion(10) ?
                    "SELECT *, cache_size AS cache_value FROM pg_sequences " .
                    "WHERE schemaname = current_schema() AND sequencename = " .
                    $this->driver->quote($sequence_name) : "SELECT * FROM $sequence_name"));
                $sequences[] = ($style == "DROP+CREATE" ? "DROP SEQUENCE IF EXISTS $sequence_name;\n" : "") .
                    "CREATE SEQUENCE $sequence_name INCREMENT $sq[increment_by] MINVALUE $sq[min_value] MAXVALUE $sq[max_value]" .
                    ($autoIncrement && $sq['last_value'] ? " START $sq[last_value]" : "") . " CACHE $sq[cache_value];";
            }
        }

        // adding sequences before table definition
        if (!empty($sequences)) {
            $query = implode("\n\n", $sequences) . "\n\n$query";
        }

        // primary + unique keys
        foreach ($indexes as $index_name => $index) {
            switch ($index->type) {
                case 'UNIQUE':
                    $clauses[] = "CONSTRAINT " . $this->escapeId($index_name) .
                        " UNIQUE (" . implode(', ', array_map(function ($column) {
                            return $this->escapeId($column);
                        }, $index->columns)) . ")";
                    break;
                case 'PRIMARY':
                    $clauses[] = "CONSTRAINT " . $this->escapeId($index_name) .
                        " PRIMARY KEY (" . implode(', ', array_map(function ($column) {
                            return $this->escapeId($column);
                        }, $index->columns)) . ")";
                    break;
            }
        }

        foreach ($constraints as $conname => $consrc) {
            $clauses[] = "CONSTRAINT " . $this->escapeId($conname) . " CHECK $consrc";
        }

        $query .= implode(",\n    ", $clauses) . "\n) WITH (oids = " . ($status->oid ? 'true' : 'false') . ");";

        // "basic" indexes after table definition
        foreach ($indexes as $index_name => $index) {
            if ($index->type == 'INDEX') {
                $columns = [];
                foreach ($index->columns as $key => $val) {
                    $columns[] = $this->escapeId($val) . ($index->descs[$key] ? " DESC" : "");
                }
                $query .= "\n\nCREATE INDEX " . $this->escapeId($index_name) . " ON " .
                    $this->escapeId($status->schema) . "." . $this->escapeId($status->name) .
                    " USING btree (" . implode(', ', $columns) . ");";
            }
        }

        // coments for table & fields
        if ($status->comment) {
            $query .= "\n\nCOMMENT ON TABLE " . $this->escapeId($status->schema) . "." .
                $this->escapeId($status->name) . " IS " . $this->driver->quote($status->comment) . ";";
        }

        foreach ($fields as $field_name => $field) {
            if ($field->comment) {
                $query .= "\n\nCOMMENT ON COLUMN " . $this->escapeId($status->schema) . "." .
                    $this->escapeId($status->name) . "." . $this->escapeId($field_name) .
                    " IS " . $this->driver->quote($field->comment) . ";";
            }
        }

        return rtrim($query, ';');
    }

    /**
     * @inheritDoc
     */
    public function sqlForTruncateTable(string $table)
    {
        return "TRUNCATE " . $this->table($table);
    }

    /**
     * @inheritDoc
     */
    public function sqlForCreateTrigger(string $table)
    {
        $status = $this->driver->tableStatus($table);
        $query = "";
        foreach ($this->driver->triggers($table) as $trg_id => $trg) {
            $trigger = $this->driver->trigger($trg_id, $status->name);
            $query .= "\nCREATE TRIGGER " . $this->escapeId($trigger['Trigger']) .
                " $trigger[Timing] $trigger[Events] ON " . $this->escapeId($status->schema) . "." .
                $this->escapeId($status->name) . " $trigger[Type] $trigger[Statement];;\n";
        }
        return $query;
    }


    /**
     * @inheritDoc
     */
    public function sqlForUseDatabase(string $database)
    {
        return "\connect " . $this->escapeId($database);
    }
}


1			<?php
2
3			namespace Lagdo\DbAdmin\Driver\PgSql\Db;
4
5			use Lagdo\DbAdmin\Driver\Db\Grammar as AbstractGrammar;
6
7			class Grammar extends AbstractGrammar
8			{
9			/**
10			* @inheritDoc
11			*/
12			public function escapeId($idf)
13			{
14			return '"' . str_replace('"', '""', $idf) . '"';
15			}
16
17			/**
18			* @inheritDoc
19			*/
20			public function limit(string $query, string $where, int $limit, int $offset = 0, string $separator = " ")
21			{
22			return " $query$where" . ($limit !== 0 ? $separator . "LIMIT $limit" .
23			($offset ? " OFFSET $offset" : "") : "");
24			}
25
26			private function constraints(string $table)
27			{
28			$constraints = [];
29			$query = "SELECT conname, consrc FROM pg_catalog.pg_constraint " .
30			"INNER JOIN pg_catalog.pg_namespace ON pg_constraint.connamespace = pg_namespace.oid " .
31			"INNER JOIN pg_catalog.pg_class ON pg_constraint.conrelid = pg_class.oid " .
32			"AND pg_constraint.connamespace = pg_class.relnamespace WHERE pg_constraint.contype = 'c' " .
33			// "-- handle only CONSTRAINTs here, not TYPES " .
34			"AND conrelid != 0 AND nspname = current_schema() AND relname = " .
35			$this->driver->quote($table) . "ORDER BY connamespace, conname";
36			foreach ($this->driver->rows($query) as $row)
37			{
38			$constraints[$row['conname']] = $row['consrc'];
39			}
40			return $constraints;
41			}
42
43			/**
44			* @inheritDoc
45			*/
46			public function sqlForForeignKeys(string $table)
47			{
48			$query = "";
49
50			$status = $this->driver->tableStatus($table);
51			$fkeys = $this->driver->foreignKeys($table);
52			ksort($fkeys);
53
54			foreach ($fkeys as $fkey_name => $fkey) {
55			$query .= "ALTER TABLE ONLY " . $this->escapeId($status->schema) . "." .
56			$this->escapeId($status->name) . " ADD CONSTRAINT " . $this->escapeId($fkey_name) .
57			" {$fkey->definition} " . ($fkey->deferrable ? 'DEFERRABLE' : 'NOT DEFERRABLE') . ";\n";
58			}
59
60			return ($query ? "$query\n" : $query);
61			}
62
63			/**
64			* @inheritDoc
65			*/
66			public function sqlForCreateTable(string $table, bool $autoIncrement, string $style)
67			{
68			$clauses = [];
69			$sequences = [];
70
71			$status = $this->driver->tableStatus($table);
72			if ($status !== null && $this->driver->isView($status)) {
73			$view = $this->driver->view($table);
74			return rtrim("CREATE VIEW " . $this->escapeId($table) . " AS $view[select]", ";");
75			}
76			$fields = $this->driver->fields($table);
77			$indexes = $this->driver->indexes($table);
78			ksort($indexes);
79			$constraints = $this->constraints($table);
80
81			if (empty($status) \|\| empty($fields)) {
82			return '';
83			}
84
85			$query = "CREATE TABLE " . $this->escapeId($status->schema) . "." .
86			$this->escapeId($status->name) . " (\n ";
87
88			// fields' definitions
89			foreach ($fields as $field_name => $field) {
90			$part = $this->escapeId($field->name) . ' ' . $field->fullType .
91			$this->driver->defaultValue($field) . ($field->attnotnull ? " NOT NULL" : "");
92			$clauses[] = $part;
93
94			// sequences for fields
95			if (preg_match('~nextval\(\'([^\']+)\'\)~', $field->default, $matches)) {
96			$sequence_name = $matches[1];
97			$sq = reset($this->driver->rows($this->driver->minVersion(10) ?
98			"SELECT *, cache_size AS cache_value FROM pg_sequences " .
99			"WHERE schemaname = current_schema() AND sequencename = " .
100			$this->driver->quote($sequence_name) : "SELECT * FROM $sequence_name"));
101			$sequences[] = ($style == "DROP+CREATE" ? "DROP SEQUENCE IF EXISTS $sequence_name;\n" : "") .
102			"CREATE SEQUENCE $sequence_name INCREMENT $sq[increment_by] MINVALUE $sq[min_value] MAXVALUE $sq[max_value]" .
103			($autoIncrement && $sq['last_value'] ? " START $sq[last_value]" : "") . " CACHE $sq[cache_value];";
104			}
105			}
106
107			// adding sequences before table definition
108			if (!empty($sequences)) {
109			$query = implode("\n\n", $sequences) . "\n\n$query";
110			}
111
112			// primary + unique keys
113			foreach ($indexes as $index_name => $index) {
114			switch ($index->type) {
115			case 'UNIQUE':
116			$clauses[] = "CONSTRAINT " . $this->escapeId($index_name) .
117			" UNIQUE (" . implode(', ', array_map(function ($column) {
118			return $this->escapeId($column);
119			}, $index->columns)) . ")";
120			break;
121			case 'PRIMARY':
122			$clauses[] = "CONSTRAINT " . $this->escapeId($index_name) .
123			" PRIMARY KEY (" . implode(', ', array_map(function ($column) {
124			return $this->escapeId($column);
125			}, $index->columns)) . ")";
126			break;
127			}
128			}
129
130			foreach ($constraints as $conname => $consrc) {
131			$clauses[] = "CONSTRAINT " . $this->escapeId($conname) . " CHECK $consrc";
132			}
133
134			$query .= implode(",\n ", $clauses) . "\n) WITH (oids = " . ($status->oid ? 'true' : 'false') . ");";
135
136			// "basic" indexes after table definition
137			foreach ($indexes as $index_name => $index) {
138			if ($index->type == 'INDEX') {
139			$columns = [];
140			foreach ($index->columns as $key => $val) {
141			$columns[] = $this->escapeId($val) . ($index->descs[$key] ? " DESC" : "");
142			}
143			$query .= "\n\nCREATE INDEX " . $this->escapeId($index_name) . " ON " .
144			$this->escapeId($status->schema) . "." . $this->escapeId($status->name) .
145			" USING btree (" . implode(', ', $columns) . ");";
146			}
147			}
148
149			// coments for table & fields
150			if ($status->comment) {
151			$query .= "\n\nCOMMENT ON TABLE " . $this->escapeId($status->schema) . "." .
152			$this->escapeId($status->name) . " IS " . $this->driver->quote($status->comment) . ";";
153			}
154
155			foreach ($fields as $field_name => $field) {
156			if ($field->comment) {
157			$query .= "\n\nCOMMENT ON COLUMN " . $this->escapeId($status->schema) . "." .
158			$this->escapeId($status->name) . "." . $this->escapeId($field_name) .
159			" IS " . $this->driver->quote($field->comment) . ";";
160			}
161			}
162
163			return rtrim($query, ';');
164			}
165
166			/**
167			* @inheritDoc
168			*/
169			public function sqlForTruncateTable(string $table)
170			{
171			return "TRUNCATE " . $this->table($table);
172			}
173
174			/**
175			* @inheritDoc
176			*/
177			public function sqlForCreateTrigger(string $table)
178			{
179			$status = $this->driver->tableStatus($table);
180			$query = "";
181			foreach ($this->driver->triggers($table) as $trg_id => $trg) {
182			$trigger = $this->driver->trigger($trg_id, $status->name);
183			$query .= "\nCREATE TRIGGER " . $this->escapeId($trigger['Trigger']) .
184			" $trigger[Timing] $trigger[Events] ON " . $this->escapeId($status->schema) . "." .
185			$this->escapeId($status->name) . " $trigger[Type] $trigger[Statement];;\n";
186			}
187			return $query;
188			}
189
190
191			/**
192			* @inheritDoc
193			*/
194			public function sqlForUseDatabase(string $database)
195			{
196			return "\connect " . $this->escapeId($database);
197			}
198			}
199

lagdo / dbadmin-driver-pgsql

Branch — main (a6702e)

Grammar::constraints() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like