Lagdo\DbAdmin\Driver\Sqlite\Db\Table

Complexity

Total Complexity

117

Size/Duplication

Total Lines	454
Duplicated Lines	0 %

Importance

Changes	1
Bugs	0	Features	0

16 Methods

Rating	Name	Duplication	Size	Complexity
A	tableHelp()	0	7	3
A	triggers()	0	11	2
D	recreateTable()	0	106	37
A	triggerOptions()	0	6	1
A	tablesStatuses()	0	8	2
A	isView()	0	3	1
A	makeStatus()	0	10	1
A	alterIndexes()	0	16	6
F	fields()	0	44	13
F	alterTable()	0	41	18
A	foreignKeys()	0	13	3
A	trigger()	0	18	5
F	indexes()	0	59	20
A	supportForeignKeys()	0	3	1
A	queryStatus()	0	6	2
A	tableStatus()	0	7	2

<?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\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'];
        $status->rows = $this->connection->result("SELECT COUNT(*) FROM " . $this->driver->escapeId($row["Name"]));
        $tables[$row["Name"]] = $status;

$tables was never initialized. Although not strictly required by PHP, it is generally a good practice to add $tables = array(); before regardless.

        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 tablesStatuses(bool $fast = false)
    {
        $tables = [];
        $rows = $this->queryStatus();
        foreach ($rows as $row) {
            $tables[$row["Name"]] = $this->makeStatus($row);
        }
        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 alterTable(string $table, string $name, array $fields, array $foreign,
        string $comment, string $engine, string $collation, int $autoIncrement, string $partitioning)
    {
        $use_all_fields = ($table == "" || !empty($foreign));
        foreach ($fields as $field) {
            if ($field[0] != "" || !$field[1] || $field[2]) {
                $use_all_fields = true;
                break;
            }
        }
        $alter = [];
        $originals = [];
        foreach ($fields as $field) {
            if ($field[1]) {
                $alter[] = ($use_all_fields ? $field[1] : "ADD " . implode($field[1]));
                if ($field[0] != "") {
                    $originals[$field[0]] = $field[1][0];
                }
            }
        }
        if (!$use_all_fields) {
            foreach ($alter as $val) {
                if (!$this->driver->execute("ALTER TABLE " . $this->driver->table($table) . " $val")) {
                    return false;
                }
            }
            if ($table != $name && !$this->driver->execute("ALTER TABLE " . $this->driver->table($table) . " RENAME TO " . $this->driver->table($name))) {
                return false;
            }
        } elseif (!$this->recreateTable($table, $name, $alter, $originals, $foreign, $autoIncrement)) {
            return false;
        }
        if ($autoIncrement) {
            $this->driver->execute("BEGIN");
            $this->driver->execute("UPDATE sqlite_sequence SET seq = $autoIncrement WHERE name = " . $this->driver->quote($name)); // ignores error
            if (!$this->driver->affectedRows()) {
                $this->driver->execute("INSERT INTO sqlite_sequence (name, seq) VALUES (" . $this->driver->quote($name) . ", $autoIncrement)");
            }
            $this->driver->execute("COMMIT");
        }
        return true;
    }

    /**
     * Recreate a table
     *
     * @param string $table
     * @param string $name
     * @param array $fields
     * @param array $originals
     * @param array $foreign
     * @param integer $autoIncrement
     * @param array $indexes
     *
     * @return bool
     */
    protected function recreateTable(string $table, string $name, array $fields, array $originals,
        array $foreign, int $autoIncrement, array $indexes = [])
    {
        if ($table != "") {
            if (empty($fields)) {
                foreach ($this->fields($table) as $key => $field) {
                    if (!empty($indexes)) {
                        $field->autoIncrement = 0;
                    }
                    $fields[] = $this->util->processField($field, $field);
                    $originals[$key] = $this->driver->escapeId($key);
                }
            }
            $primary_key = false;
            foreach ($fields as $field) {
                if ($field[6]) {
                    $primary_key = true;
                }
            }
            $drop_indexes = [];
            foreach ($indexes as $key => $val) {
                if ($val[2] == "DROP") {
                    $drop_indexes[$val[1]] = true;
                    unset($indexes[$key]);
                }
            }
            foreach ($this->indexes($table) as $key_name => $index) {
                $columns = [];
                foreach ($index->columns as $key => $column) {
                    if (!$originals[$column]) {
                        continue 2;
                    }
                    $columns[] = $originals[$column] . ($index->descs[$key] ? " DESC" : "");
                }
                if (!$drop_indexes[$key_name]) {
                    if ($index->type != "PRIMARY" || !$primary_key) {
                        $indexes[] = [$index->type, $key_name, $columns];
                    }
                }
            }
            foreach ($indexes as $key => $val) {
                if ($val[0] == "PRIMARY") {
                    unset($indexes[$key]);
                    $foreign[] = "  PRIMARY KEY (" . implode(", ", $val[2]) . ")";
                }
            }
            foreach ($this->foreignKeys($table) as $key_name => $foreignKey) {
                foreach ($foreignKey->source as $key => $column) {
                    if (!$originals[$column]) {
                        continue 2;
                    }
                    $foreignKey->source[$key] = $this->driver->unescapeId($originals[$column]);
                }
                if (!isset($foreign[" $key_name"])) {
                    $foreign[] = " " . $this->driver->formatForeignKey($foreignKey);
                }
            }
            $this->driver->execute("BEGIN");
        }
        foreach ($fields as $key => $field) {
            $fields[$key] = "  " . implode($field);
        }
        $fields = array_merge($fields, array_filter($foreign));
        $tempName = ($table == $name ? "adminer_$name" : $name);
        if (!$this->driver->execute("CREATE TABLE " . $this->driver->table($tempName) .
            " (\n" . implode(",\n", $fields) . "\n)")) {
            // implicit ROLLBACK to not overwrite $this->driver->error()
            return false;
        }
        if ($table != "") {
            if ($originals && !$this->driver->execute("INSERT INTO " . $this->driver->table($tempName) .
                " (" . implode(", ", $originals) . ") SELECT " . implode(
                    ", ",
                    array_map(function ($key) {
                   return $this->driver->escapeId($key);
               }, array_keys($originals))
                ) . " FROM " . $this->driver->table($table))) {
                return false;
            }
            $triggers = [];
            foreach ($this->triggers($table) as $trigger_name => $timing_event) {
                $trigger = $this->trigger($trigger_name);
                $triggers[] = "CREATE TRIGGER " . $this->driver->escapeId($trigger_name) . " " .
                    implode(" ", $timing_event) . " ON " . $this->driver->table($name) . "\n$trigger[Statement]";
            }
            $autoIncrement = $autoIncrement ? 0 :
                $this->connection->result("SELECT seq FROM sqlite_sequence WHERE name = " .
                $this->driver->quote($table)); // if $autoIncrement is set then it will be updated later
            // drop before creating indexes and triggers to allow using old names
            if (!$this->driver->execute("DROP TABLE " . $this->driver->table($table)) ||
                ($table == $name && !$this->driver->execute("ALTER TABLE " . $this->driver->table($tempName) .
                " RENAME TO " . $this->driver->table($name))) || !$this->alterIndexes($name, $indexes)
            ) {
                return false;
            }
            if ($autoIncrement) {
                $this->driver->execute("UPDATE sqlite_sequence SET seq = $autoIncrement WHERE name = " . $this->driver->quote($name)); // ignores error
            }
            foreach ($triggers as $trigger) {
                if (!$this->driver->execute($trigger)) {
                    return false;
                }
            }
            $this->driver->execute("COMMIT");
        }
        return true;
    }

    /**
     * @inheritDoc
     */
    public function alterIndexes(string $table, array $alter)
    {
        foreach ($alter as $primary) {
            if ($primary[0] == "PRIMARY") {
                return $this->recreateTable($table, $table, [], [], [], 0, $alter);
            }
        }
        foreach (array_reverse($alter) as $val) {
            if (!$this->driver->execute($val[2] == "DROP" ?
                "DROP INDEX " . $this->driver->escapeId($val[1]) :
                $this->driver->sqlForCreateIndex($table, $val[0], $val[1], "(" . implode(", ", $val[2]) . ")")
            )) {
                return false;
            }
        }
        return true;
    }

    /**
     * @inheritDoc
     */
    public function trigger(string $trigger)
    {
        if ($trigger == "") {
            return ["Statement" => "BEGIN\n\t;\nEND"];

The expression return array('Statement' => 'BEGIN ; END') returns the type array<string,string> which is incompatible with the return type mandated by Lagdo\DbAdmin\Driver\Db\TableInterface::trigger() of Lagdo\DbAdmin\Driver\Entity\TriggerEntity.

        }
        $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($trigger)), $match);
        $of = $match[3];
        return [

The expression return array('Timing' => strtoupper($match[1]), 'Event' => strtoupper($match[2]) . $of ? ' OF' : '', 'Of' => $of[0] == '`' || $of[0] == '"' ? $this->driver->unescapeId($of) : $of, 'Trigger' => $trigger, 'Statement' => $match[4]) returns the type array<string,mixed|string> which is incompatible with the return type mandated by Lagdo\DbAdmin\Driver\Db\TableInterface::trigger() of Lagdo\DbAdmin\Driver\Entity\TriggerEntity.

            "Timing" => strtoupper($match[1]),
            "Event" => strtoupper($match[2]) . ($of ? " OF" : ""),
            "Of" => ($of[0] == '`' || $of[0] == '"' ? $this->driver->unescapeId($of) : $of),
            "Trigger" => $trigger,
            "Statement" => $match[4],
        ];
    }

    /**
     * @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 Trigger($match[1], $match[2]);

The type Lagdo\DbAdmin\Driver\Sqlite\Db\Trigger was not found. Maybe you did not declare it correctly or list all dependencies?

        }
        return $triggers;
    }

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