Grammar::queryRegex() - Code Metrics - Inspection of "Parse queries from an SQL command." - lagdo/dbadmin-driver-pgsql - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Branch — main (524208)

by Thierry

created 2021-11-14 05:12 UTC

Grammar::queryRegex() A

↳ Parent: Grammar

Complexity

Conditions	1
Paths	1

Size

Total Lines	3
Code Lines	1

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	1
c	1
b	0
f	0
nc	1
nop	0
dl	0
loc	3
rs	10

<?php

namespace Lagdo\DbAdmin\Driver\PgSql\Db;

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

use Lagdo\DbAdmin\Driver\Entity\TableEntity;

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

    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);
    }

    /**
     * @param array $fields
     * @param boolean $autoIncrement
     * @param string $style
     *
     * @return array
     */
    private function _sequences(array $fields, bool $autoIncrement, string $style)
    {
        $sequences = [];
        // Fields definitions
        foreach ($fields as $field_name => $field) {
            // 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];";
            }
        }
        return $sequences;
    }

    /**
     * @param string $table
     * @param array $fields
     * @param array $indexes
     *
     * @return array
     */
    private function _clauses(string $table, array $fields, array $indexes)
    {
        $clauses = [];
        // Fields definitions
        foreach ($fields as $field_name => $field) {
            $clauses[] = $this->escapeId($field->name) . ' ' . $field->fullType .
                $this->driver->defaultValue($field) . ($field->attnotnull ? " NOT NULL" : "");
        }
        // 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;
            }
        }
        // Constraints
        $constraints = $this->constraints($table);
        foreach ($constraints as $conname => $consrc) {
            $clauses[] = "CONSTRAINT " . $this->escapeId($conname) . " CHECK $consrc";
        }

        return $clauses;
    }

    /**
     * @param array $indexes
     * @param TableEntity $status
     *
     * @return string
     */
    private function _indexQueries(array $indexes, TableEntity $status)
    {
        $query = '';
        // 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) . ");";
            }
        }
        return $query;
    }

    /**
     * @param array $fields
     * @param TableEntity $status
     *
     * @return string
     */
    private function _commentQueries(array $fields, TableEntity $status)
    {
        $query = '';
        $table = $this->escapeId($status->schema) . '.' . $this->escapeId($status->name);
        // Comments for table & fields
        if ($status->comment) {
            $query .= "\n\nCOMMENT ON TABLE $table IS " . $this->driver->quote($status->comment) . ";";
        }
        foreach ($fields as $name => $field) {
            if ($field->comment) {
                $query .= "\n\nCOMMENT ON COLUMN $table." . $this->escapeId($name) .
                    " IS " . $this->driver->quote($field->comment) . ";";
            }
        }
        return $query;
    }

    /**
     * @inheritDoc
     */
    public function sqlForCreateTable(string $table, bool $autoIncrement, string $style)
    {
        $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);
        if (empty($status) || empty($fields)) {
            return '';
        }

        $sequences = $this->_sequences($fields, $autoIncrement, $style);
        $indexes = $this->driver->indexes($table);
        ksort($indexes);
        $clauses = $this->_clauses($table, $fields, $indexes);
        // Adding sequences before table definition
        $query = '';
        if (!empty($sequences)) {
            $query = implode("\n\n", $sequences) . "\n\n";
        }
        $query .= 'CREATE TABLE ' . $this->escapeId($status->schema) . '.' . $this->escapeId($status->name) . " (\n    ";
        $query .= implode(",\n    ", $clauses) . "\n) WITH (oids = " . ($status->oid ? 'true' : 'false') . ");";
        $query .= $this->_indexQueries($indexes, $status);
        $query .= $this->_commentQueries($fields, $status);

        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);
    }

    /**
     * @inheritDoc
     */
    protected function queryRegex()
    {
        return '\\s*|[\'"]|/\*|-- |$|\$[^$]*\$';
    }
}


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

lagdo / dbadmin-driver-pgsql

Branch — main (524208)

Grammar::queryRegex() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like