Table::tableFields() - Code Metrics - Inspection of "Added a trait for Table class." - lagdo/dbadmin-driver-sqlite - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — main ( 7ae905...1918cf )

by Thierry

created 2021-12-19 03:32 UTC

Table::tableFields() A

↳ Parent: Table

Complexity

Conditions	5
Paths	5

Size

Total Lines	20
Code Lines	15

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	5
eloc	15
c	0
b	0
f	0
nc	5
nop	1
dl	0
loc	20
rs	9.4555

<?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;

use function str_replace;
use function strtolower;
use function strtoupper;
use function preg_match;
use function preg_match_all;
use function preg_replace;
use function preg_quote;
use function implode;

class Table extends AbstractTable
{
    use TableTrait;

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

    /**
     * @param string $type
     *
     * @return string
     */
    private function rowType(string $type)
    {
        if (preg_match('~int~i', $type)) {
            return 'integer';
        }
        if (preg_match('~char|clob|text~i', $type)) {
            return 'text';
        }
        if (preg_match('~blob~i', $type)) {
            return 'blob';
        }
        if (preg_match('~real|floa|doub~i', $type)) {
            return 'real';
        }
        return 'numeric';
    }

    private function defaultvalue(array $row)
    {
        $default = $row["dflt_value"];
        if (preg_match("~'(.*)'~", $default, $match)) {
            return str_replace("''", "'", $match[1]);
        }
        if ($default == "NULL") {
            return null;
        }
        return $default;
    }

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

        $type = strtolower($row["type"]);
        $field->name = $row["name"];
        $field->type = $this->rowType($type);
        $field->fullType = $type;
        $field->default = $this->defaultvalue($row);
        $field->null = !$row["notnull"];
        $field->privileges = ["select" => 1, "insert" => 1, "update" => 1];
        $field->primary = $row["pk"];
        return $field;
    }

    /**
     * @param string $table
     *
     * @return array
     */
    private function tableFields(string $table)
    {
        $fields = [];
        $rows = $this->driver->rows('PRAGMA table_info(' . $this->driver->table($table) . ')');
        $primary = "";
        foreach ($rows as $row) {
            $name = $row["name"];
            $type = strtolower($row["type"]);
            $field = $this->makeFieldEntity($row);
            if ($row["pk"]) {
                if ($primary != "") {
                    $fields[$primary]->autoIncrement = false;
                } elseif (preg_match('~^integer$~i', $type)) {
                    $field->autoIncrement = true;
                }
                $primary = $name;
            }
            $fields[$name] = $field;
        }
        return $fields;
    }

    /**
     * @inheritDoc
     */
    public function fields(string $table)
    {
        $fields = $this->tableFields($table);
        $query = "SELECT sql FROM sqlite_master WHERE type IN ('table', 'view') AND name = " . $this->driver->quote($table);
        $result = $this->connection->result($query);
        $pattern = '~(("[^"]*+")+|[a-z0-9_]+)\s+text\s+COLLATE\s+(\'[^\']+\'|\S+)~i';
        preg_match_all($pattern, $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;
    }

    /**
     * @param array $row
     * @param array $results
     * @param string $table
     * @param ConnectionInterface $connection
     *
     * @return IndexEntity
     */
    private function makeIndexEntity(array $row, array $results, string $table, ConnectionInterface $connection)
    {
        $index = new IndexEntity();

        $name = $row["name"];
        $index->type = $row["unique"] ? "UNIQUE" : "INDEX";
        $index->lengths = [];
        $index->descs = [];
        $columns = $this->driver->rows("PRAGMA index_info(" .
            $this->driver->escapeId($name) . ")", $connection);
        foreach ($columns as $column) {
            $index->columns[] = $column["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';
                }
            }
        }
        return $index;
    }

    /**
     * @inheritDoc
     */
    public function indexes(string $table, ConnectionInterface $connection = null)
    {
        if (!$connection) {
            $connection = $this->connection;
        }
        $primaryIndex = $this->makePrimaryIndex($table, $connection);
        if ($primaryIndex === null) {
            return [];
        }

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

lagdo / dbadmin-driver-sqlite

Push — main ( 7ae905...1918cf )

Table::tableFields() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like