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

Passed

Branch — main (2a1ad3)

by Thierry

created 2021-10-28 21:30 UTC

Table::alterIndexes() A

↳ Parent: Project

Complexity

Conditions	6
Paths	7

Size

Total Lines	16
Code Lines	9

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	6
eloc	9
nc	7
nop	2
dl	0
loc	16
rs	9.2222
c	1
b	0
f	0

<?php

namespace Lagdo\DbAdmin\Driver\Sqlite\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
{
    /**
     * @inheritDoc
     */
    public function tableHelp(string $name)
    {
        if ($name == "sqlite_sequence") {
            return "fileformat2.html#seqtab";
        }
        if ($name == "sqlite_master") {
            return "fileformat2.html#$name";
        }
    }

    /**
     * @param string $table
     *
     * @return array
     */
    private function queryStatus(string $table = '')
    {
        $query = "SELECT name AS Name, type AS Engine, 'rowid' AS Oid, '' AS Auto_increment " .
            "FROM sqlite_master WHERE type IN ('table', 'view') " .
            ($table != "" ? "AND name = " . $this->driver->quote($table) : "ORDER BY name");
        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->oid = $row['Oid'];
        // $status->Auto_increment = $row['Auto_increment'];
        $query = 'SELECT COUNT(*) FROM ' . $this->driver->escapeId($row['Name']);
        $status->rows = $this->connection->result($query);

        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 $tableStatus->engine == 'view';
    }

    /**
     * @inheritDoc
     */
    public function supportForeignKeys(TableEntity $tableStatus)
    {
        return !$this->connection->result("SELECT sqlite_compileoption_used('OMIT_FOREIGN_KEY')");
    }

    /**
     * @inheritDoc
     */
    public function fields(string $table)
    {
        $fields = [];
        $primary = "";
        foreach ($this->driver->rows("PRAGMA table_info(" . $this->driver->table($table) . ")") as $row) {
            $name = $row["name"];
            $type = strtolower($row["type"]);
            $default = $row["dflt_value"];

            $field = new TableFieldEntity();

            $field->name = $name;
            $field->type = (preg_match('~int~i', $type) ? "integer" : (preg_match('~char|clob|text~i', $type) ?
                "text" : (preg_match('~blob~i', $type) ? "blob" : (preg_match('~real|floa|doub~i', $type) ?
                "real" : "numeric"))));
            $field->fullType = $type;
            $field->default = (preg_match("~'(.*)'~", $default, $match) ? str_replace("''", "'", $match[1]) :
                ($default == "NULL" ? null : $default));
            $field->null = !$row["notnull"];
            $field->privileges = ["select" => 1, "insert" => 1, "update" => 1];
            $field->primary = $row["pk"];

            if ($row["pk"]) {
                if ($primary != "") {
                    $fields[$primary]->autoIncrement = false;
                } elseif (preg_match('~^integer$~i', $type)) {
                    $field->autoIncrement = true;
                }
                $primary = $name;
            }

            $fields[$field->name] = $field;
        }
        $query = "SELECT sql FROM sqlite_master WHERE type IN ('table', 'view') AND name = " . $this->driver->quote($table);
        $result = $this->connection->result($query);
        preg_match_all('~(("[^"]*+")+|[a-z0-9_]+)\s+text\s+COLLATE\s+(\'[^\']+\'|\S+)~i',
            $result, $matches, PREG_SET_ORDER);
        foreach ($matches as $match) {
            $name = str_replace('""', '"', preg_replace('~^"|"$~', '', $match[1]));
            if (isset($fields[$name])) {
                $fields[$name]->collation = trim($match[3], "'");
            }
        }
        return $fields;
    }

    /**
     * @inheritDoc
     */
    public function indexes(string $table, ConnectionInterface $connection = null)
    {
        if (!is_object($connection)) {
            $connection = $this->connection;
        }
        $indexes = [];
        $query = "SELECT sql FROM sqlite_master WHERE type = 'table' AND name = " . $this->driver->quote($table);
        $result = $connection->result($query);
        if (preg_match('~\bPRIMARY\s+KEY\s*\((([^)"]+|"[^"]*"|`[^`]*`)++)~i', $result, $match)) {
            $indexes[""] = new IndexEntity();
            $indexes[""]->type = "PRIMARY";
            preg_match_all('~((("[^"]*+")+|(?:`[^`]*+`)+)|(\S+))(\s+(ASC|DESC))?(,\s*|$)~i',
                $match[1], $matches, PREG_SET_ORDER);
            foreach ($matches as $match) {
                $indexes[""]->columns[] = $this->driver->unescapeId($match[2]) . $match[4];
                $indexes[""]->descs[] = (preg_match('~DESC~i', $match[5]) ? '1' : null);
            }
        }
        if (!$indexes) {
            foreach ($this->fields($table) as $name => $field) {
                if ($field->primary) {
                    if (!isset($indexes[""])) {
                        $indexes[""] = new IndexEntity();
                    }
                    $indexes[""]->type = "PRIMARY";
                    $indexes[""]->columns = [$name];
                    $indexes[""]->lengths = [];
                    $indexes[""]->descs = [null];
                }
            }
        }
        $query = "SELECT name, sql FROM sqlite_master WHERE type = 'index' AND tbl_name = " . $this->driver->quote($table);
        $results = $this->driver->keyValues($query, $connection);
        foreach ($this->driver->rows("PRAGMA index_list(" . $this->driver->table($table) . ")", $connection) as $row) {
            $index = new IndexEntity();

            $name = $row["name"];
            $index->type = $row["unique"] ? "UNIQUE" : "INDEX";
            $index->lengths = [];
            $index->descs = [];
            foreach ($this->driver->rows("PRAGMA index_info(" . $this->driver->escapeId($name) . ")", $connection) as $row1) {
                $index->columns[] = $row1["name"];
                $index->descs[] = null;
            }
            if (preg_match('~^CREATE( UNIQUE)? INDEX ' . preg_quote($this->driver->escapeId($name) . ' ON ' .
                $this->driver->escapeId($table), '~') . ' \((.*)\)$~i', $results[$name], $regs)) {
                preg_match_all('/("[^"]*+")+( DESC)?/', $regs[2], $matches);
                foreach ($matches[2] as $key => $val) {
                    if ($val) {
                        $index->descs[$key] = '1';
                    }
                }
            }
            if (!$indexes[""] || $index->type != "UNIQUE" || $index->columns != $indexes[""]->columns ||
                $index->descs != $indexes[""]->descs || !preg_match("~^sqlite_~", $name)) {
                $indexes[$name] = $index;
            }
        }
        return $indexes;
    }

    /**
     * @inheritDoc
     */
    public function foreignKeys(string $table)
    {
        $foreignKeys = [];
        foreach ($this->driver->rows("PRAGMA foreign_key_list(" . $this->driver->table($table) . ")") as $row) {
            $name = $row["id"];
            if (!isset($foreignKeys[$name])) {
                $foreignKeys[$name] = new ForeignKeyEntity();
            }
            //! idf_unescape in SQLite2
            $foreignKeys[$name]->source[] = $row["from"];
            $foreignKeys[$name]->target[] = $row["to"];
        }
        return $foreignKeys;
    }

    /**
     * @inheritDoc
     */
    public function trigger(string $name, string $table = '')
    {
        if ($name == "") {
            return new TriggerEntity('', '', "BEGIN\n\t;\nEND");
        }
        $idf = '(?:[^`"\s]+|`[^`]*`|"[^"]*")+';
        $options = $this->triggerOptions();
        preg_match("~^CREATE\\s+TRIGGER\\s*$idf\\s*(" . implode("|", $options["Timing"]) .
            ")\\s+([a-z]+)(?:\\s+OF\\s+($idf))?\\s+ON\\s*$idf\\s*(?:FOR\\s+EACH\\s+ROW\\s)?(.*)~is",
            $this->connection->result("SELECT sql FROM sqlite_master WHERE type = 'trigger' AND name = " .
            $this->driver->quote($name)), $match);
        $of = $match[3];
        return new TriggerEntity(strtoupper($match[1]), strtoupper($match[2]), $match[4],
            ($of[0] == '`' || $of[0] == '"' ? $this->driver->unescapeId($of) : $of), $name);
    }

    /**
     * @inheritDoc
     */
    public function triggers(string $table)
    {
        $triggers = [];
        $options = $this->triggerOptions();
        $query = "SELECT * FROM sqlite_master WHERE type = 'trigger' AND tbl_name = " . $this->driver->quote($table);
        foreach ($this->driver->rows($query) as $row) {
            preg_match('~^CREATE\s+TRIGGER\s*(?:[^`"\s]+|`[^`]*`|"[^"]*")+\s*(' .
                implode("|", $options["Timing"]) . ')\s*(.*?)\s+ON\b~i', $row["sql"], $match);
            $triggers[$row["name"]] = new TriggerEntity($match[1], $match[2], '', '', $row["name"]);
        }
        return $triggers;
    }

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


1			<?php
2
3			namespace Lagdo\DbAdmin\Driver\Sqlite\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			* @inheritDoc
19			*/
20			public function tableHelp(string $name)
21			{
22			if ($name == "sqlite_sequence") {
23			return "fileformat2.html#seqtab";
24			}
25			if ($name == "sqlite_master") {
26			return "fileformat2.html#$name";
27			}
28			}
29
30			/**
31			* @param string $table
32			*
33			* @return array
34			*/
35			private function queryStatus(string $table = '')
36			{
37			$query = "SELECT name AS Name, type AS Engine, 'rowid' AS Oid, '' AS Auto_increment " .
38			"FROM sqlite_master WHERE type IN ('table', 'view') " .
39			($table != "" ? "AND name = " . $this->driver->quote($table) : "ORDER BY name");
40			return $this->driver->rows($query);
41			}
42
43			/**
44			* @param array $row
45			*
46			* @return TableEntity
47			*/
48			private function makeStatus(array $row)
49			{
50			$status = new TableEntity($row['Name']);
51			$status->engine = $row['Engine'];
52			$status->oid = $row['Oid'];
53			// $status->Auto_increment = $row['Auto_increment'];
54			$query = 'SELECT COUNT(*) FROM ' . $this->driver->escapeId($row['Name']);
55			$status->rows = $this->connection->result($query);
56
57			return $status;
58			}
59
60			/**
61			* @inheritDoc
62			*/
63			public function tableStatus(string $table, bool $fast = false)
64			{
65			$rows = $this->queryStatus($table);
66			if (!($row = reset($rows))) {
67			return null;
68			}
69			return $this->makeStatus($row);
70			}
71
72			/**
73			* @inheritDoc
74			*/
75			public function tableStatuses(bool $fast = false)
76			{
77			$tables = [];
78			$rows = $this->queryStatus();
79			foreach ($rows as $row) {
80			$tables[$row['Name']] = $this->makeStatus($row);
81			}
82			return $tables;
83			}
84
85			/**
86			* @inheritDoc
87			*/
88			public function tableNames()
89			{
90			$tables = [];
91			$rows = $this->queryStatus();
92			foreach ($rows as $row) {
93			$tables[] = $row['Name'];
94			}
95			return $tables;
96			}
97
98			/**
99			* @inheritDoc
100			*/
101			public function isView(TableEntity $tableStatus)
102			{
103			return $tableStatus->engine == 'view';
104			}
105
106			/**
107			* @inheritDoc
108			*/
109			public function supportForeignKeys(TableEntity $tableStatus)
110			{
111			return !$this->connection->result("SELECT sqlite_compileoption_used('OMIT_FOREIGN_KEY')");
112			}
113
114			/**
115			* @inheritDoc
116			*/
117			public function fields(string $table)
118			{
119			$fields = [];
120			$primary = "";
121			foreach ($this->driver->rows("PRAGMA table_info(" . $this->driver->table($table) . ")") as $row) {
122			$name = $row["name"];
123			$type = strtolower($row["type"]);
124			$default = $row["dflt_value"];
125
126			$field = new TableFieldEntity();
127
128			$field->name = $name;
129			$field->type = (preg_match('~int~i', $type) ? "integer" : (preg_match('~char\|clob\|text~i', $type) ?
130			"text" : (preg_match('~blob~i', $type) ? "blob" : (preg_match('~real\|floa\|doub~i', $type) ?
131			"real" : "numeric"))));
132			$field->fullType = $type;
133			$field->default = (preg_match("~'(.*)'~", $default, $match) ? str_replace("''", "'", $match[1]) :
134			($default == "NULL" ? null : $default));
135			$field->null = !$row["notnull"];
136			$field->privileges = ["select" => 1, "insert" => 1, "update" => 1];
137			$field->primary = $row["pk"];
138
139			if ($row["pk"]) {
140			if ($primary != "") {
141			$fields[$primary]->autoIncrement = false;
142			} elseif (preg_match('~^integer$~i', $type)) {
143			$field->autoIncrement = true;
144			}
145			$primary = $name;
146			}
147
148			$fields[$field->name] = $field;
149			}
150			$query = "SELECT sql FROM sqlite_master WHERE type IN ('table', 'view') AND name = " . $this->driver->quote($table);
151			$result = $this->connection->result($query);
152			preg_match_all('~(("[^"]*+")+\|[a-z0-9_]+)\s+text\s+COLLATE\s+(\'[^\']+\'\|\S+)~i',
153			$result, $matches, PREG_SET_ORDER);
154			foreach ($matches as $match) {
155			$name = str_replace('""', '"', preg_replace('~^"\|"$~', '', $match[1]));
156			if (isset($fields[$name])) {
157			$fields[$name]->collation = trim($match[3], "'");
158			}
159			}
160			return $fields;
161			}
162
163			/**
164			* @inheritDoc
165			*/
166			public function indexes(string $table, ConnectionInterface $connection = null)
167			{
168			if (!is_object($connection)) {
169			$connection = $this->connection;
170			}
171			$indexes = [];
172			$query = "SELECT sql FROM sqlite_master WHERE type = 'table' AND name = " . $this->driver->quote($table);
173			$result = $connection->result($query);
174			if (preg_match('~\bPRIMARY\s+KEY\s\((([^)"]+\|"[^"]"\|`[^`]*`)++)~i', $result, $match)) {
175			$indexes[""] = new IndexEntity();
176			$indexes[""]->type = "PRIMARY";
177			preg_match_all('~((("[^"]+")+\|(?:`[^`]+`)+)\|(\S+))(\s+(ASC\|DESC))?(,\s*\|$)~i',
178			$match[1], $matches, PREG_SET_ORDER);
179			foreach ($matches as $match) {
180			$indexes[""]->columns[] = $this->driver->unescapeId($match[2]) . $match[4];
181			$indexes[""]->descs[] = (preg_match('~DESC~i', $match[5]) ? '1' : null);
182			}
183			}
184			if (!$indexes) {
185			foreach ($this->fields($table) as $name => $field) {
186			if ($field->primary) {
187			if (!isset($indexes[""])) {
188			$indexes[""] = new IndexEntity();
189			}
190			$indexes[""]->type = "PRIMARY";
191			$indexes[""]->columns = [$name];
192			$indexes[""]->lengths = [];
193			$indexes[""]->descs = [null];
194			}
195			}
196			}
197			$query = "SELECT name, sql FROM sqlite_master WHERE type = 'index' AND tbl_name = " . $this->driver->quote($table);
198			$results = $this->driver->keyValues($query, $connection);
199			foreach ($this->driver->rows("PRAGMA index_list(" . $this->driver->table($table) . ")", $connection) as $row) {
200			$index = new IndexEntity();
201
202			$name = $row["name"];
203			$index->type = $row["unique"] ? "UNIQUE" : "INDEX";
204			$index->lengths = [];
205			$index->descs = [];
206			foreach ($this->driver->rows("PRAGMA index_info(" . $this->driver->escapeId($name) . ")", $connection) as $row1) {
207			$index->columns[] = $row1["name"];
208			$index->descs[] = null;
209			}
210			if (preg_match('~^CREATE( UNIQUE)? INDEX ' . preg_quote($this->driver->escapeId($name) . ' ON ' .
211			$this->driver->escapeId($table), '~') . ' \((.*)\)$~i', $results[$name], $regs)) {
212			preg_match_all('/("[^"]*+")+( DESC)?/', $regs[2], $matches);
213			foreach ($matches[2] as $key => $val) {
214			if ($val) {
215			$index->descs[$key] = '1';
216			}
217			}
218			}
219			if (!$indexes[""] \|\| $index->type != "UNIQUE" \|\| $index->columns != $indexes[""]->columns \|\|
220			$index->descs != $indexes[""]->descs \|\| !preg_match("~^sqlite_~", $name)) {
221			$indexes[$name] = $index;
222			}
223			}
224			return $indexes;
225			}
226
227			/**
228			* @inheritDoc
229			*/
230			public function foreignKeys(string $table)
231			{
232			$foreignKeys = [];
233			foreach ($this->driver->rows("PRAGMA foreign_key_list(" . $this->driver->table($table) . ")") as $row) {
234			$name = $row["id"];
235			if (!isset($foreignKeys[$name])) {
236			$foreignKeys[$name] = new ForeignKeyEntity();
237			}
238			//! idf_unescape in SQLite2
239			$foreignKeys[$name]->source[] = $row["from"];
240			$foreignKeys[$name]->target[] = $row["to"];
241			}
242			return $foreignKeys;
243			}
244
245			/**
246			* @inheritDoc
247			*/
248			public function trigger(string $name, string $table = '')
249			{
250			if ($name == "") {
251			return new TriggerEntity('', '', "BEGIN\n\t;\nEND");
252			}
253			$idf = '(?:[^`"\s]+\|`[^`]`\|"[^"]")+';
254			$options = $this->triggerOptions();
255			preg_match("~^CREATE\\s+TRIGGER\\s$idf\\s(" . implode("\|", $options["Timing"]) .
256			")\\s+([a-z]+)(?:\\s+OF\\s+($idf))?\\s+ON\\s$idf\\s(?:FOR\\s+EACH\\s+ROW\\s)?(.*)~is",
257			$this->connection->result("SELECT sql FROM sqlite_master WHERE type = 'trigger' AND name = " .
258			$this->driver->quote($name)), $match);
259			$of = $match[3];
260			return new TriggerEntity(strtoupper($match[1]), strtoupper($match[2]), $match[4],
261			($of[0] == '`' \|\| $of[0] == '"' ? $this->driver->unescapeId($of) : $of), $name);
262			}
263
264			/**
265			* @inheritDoc
266			*/
267			public function triggers(string $table)
268			{
269			$triggers = [];
270			$options = $this->triggerOptions();
271			$query = "SELECT * FROM sqlite_master WHERE type = 'trigger' AND tbl_name = " . $this->driver->quote($table);
272			foreach ($this->driver->rows($query) as $row) {
273			preg_match('~^CREATE\s+TRIGGER\s(?:[^`"\s]+\|`[^`]`\|"[^"]")+\s(' .
274			implode("\|", $options["Timing"]) . ')\s(.?)\s+ON\b~i', $row["sql"], $match);
275			$triggers[$row["name"]] = new TriggerEntity($match[1], $match[2], '', '', $row["name"]);
276			}
277			return $triggers;
278			}
279
280			/**
281			* @inheritDoc
282			*/
283			public function triggerOptions()
284			{
285			return [
286			"Timing" => ["BEFORE", "AFTER", "INSTEAD OF"],
287			"Event" => ["INSERT", "UPDATE", "UPDATE OF", "DELETE"],
288			"Type" => ["FOR EACH ROW"],
289			];
290			}
291			}
292

lagdo / dbadmin-driver-sqlite

Branch — main (2a1ad3)

Table::alterIndexes() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like