SQLite3SchemaManager::alterTable() - Code Metrics - Inspection of "API Apply SilverStripe\SQLite namespace to module" - silverstripe-labs/silverstripe-sqlite3 - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#23)

by Damian

created 2016-06-29 01:56 UTC

SQLite3SchemaManager::alterTable() D

↳ Parent: SQLite3SchemaManager

Complexity

Conditions	9
Paths	24

Size

Total Lines	27
Code Lines	14

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
c	1
b	0
f	0
dl	0
loc	27
rs	4.909
cc	9
eloc	14
nc	24
nop	7

<?php

namespace SilverStripe\SQLite;

use SilverStripe\ORM\Connect\DBSchemaManager;
use Exception;
use SapphireTest;
use Debug;
use Director;

/**
 * SQLite schema manager class
 *
 * @package SQLite3
 */
class SQLite3SchemaManager extends DBSchemaManager
{

    /**
     * Instance of the database controller this schema belongs to
     *
     * @var SQLite3Database
     */
    protected $database = null;

    /**
     * Flag indicating whether or not the database has been checked and repaired
     *
     * @var boolean
     */
    protected static $checked_and_repaired = false;

    /**
     * Should schema be vacuumed during checkeAndRepairTable?
     *
     * @var boolean
     */
    public static $vacuum = true;

    public function createDatabase($name)
    {
        // Ensure that any existing database is cleared before connection
        $this->dropDatabase($name);
    }

    public function dropDatabase($name)
    {
        // No need to delete database files if operating purely within memory
        if ($this->database->getLivesInMemory()) {
            return;
        }

        // If using file based database ensure any existing file is removed
        $parameters = $this->database->getParameters();
        $fullpath = $parameters['path'] . '/' . $name;
        if (is_writable($fullpath)) {
            unlink($fullpath);
        }
    }

    public function databaseList()
    {
        $parameters = $this->database->getParameters();

        // If in-memory use the current database name only
        if ($this->database->getLivesInMemory()) {
            return array($parameters['database']);
        }

        // If using file based database enumerate files in the database directory
        $directory = $parameters['path'];
        $files = scandir($directory);

        // Filter each file in this directory
        $databases = array();
        if ($files !== false) {
            foreach ($files as $file) {

            // Filter non-files
            if (!is_file("$directory/$file")) {
                continue;
            }

            // Filter those with correct extension
            if (!SQLite3Database::is_valid_database_name($file)) {
                continue;
            }

                $databases[] = $file;
            }
        }
        return $databases;
    }

    public function databaseExists($name)
    {
        $databases = $this->databaseList();
        return in_array($name, $databases);
    }

    /**
     * Empties any cached enum values
     */
    public function flushCache()
    {
        $this->enum_map = array();
    }

    public function schemaUpdate($callback)
    {
        // Set locking mode
        $this->database->setPragma('locking_mode', 'EXCLUSIVE');
        $this->checkAndRepairTable();
        $this->flushCache();

        // Initiate schema update
        $error = null;
        try {
            parent::schemaUpdate($callback);
        } catch (Exception $ex) {
            $error = $ex;
        }

        // Revert locking mode
        $this->database->setPragma('locking_mode', SQLite3Database::$default_pragma['locking_mode']);

        if ($error) {
            throw $error;
        }
    }

    /**
     * Empty a specific table
     *
     * @param string $table
     */
    public function clearTable($table)
    {
        if ($table != 'SQLiteEnums') {
            $this->query("DELETE FROM \"$table\"");
        }
    }

    public function createTable($table, $fields = null, $indexes = null, $options = null, $advancedOptions = null)

    {
        if (!isset($fields['ID'])) {
            $fields['ID'] = $this->IdColumn();
        }

        $fieldSchemata = array();
        if ($fields) {
            foreach ($fields as $k => $v) {
                $fieldSchemata[] = "\"$k\" $v";
            }
        }
        $fieldSchemas = implode(",\n", $fieldSchemata);

        // Switch to "CREATE TEMPORARY TABLE" for temporary tables
        $temporary = empty($options['temporary']) ? "" : "TEMPORARY";
        $this->query("CREATE $temporary TABLE \"$table\" (
			$fieldSchemas
		)");

        if ($indexes) {
            foreach ($indexes as $indexName => $indexDetails) {
                $this->createIndex($table, $indexName, $indexDetails);
            }
        }

        return $table;
    }

    public function alterTable($tableName, $newFields = null, $newIndexes = null, $alteredFields = null,
        $alteredIndexes = null, $alteredOptions = null, $advancedOptions = null
    ) {
        if ($newFields) {
            foreach ($newFields as $fieldName => $fieldSpec) {
                $this->createField($tableName, $fieldName, $fieldSpec);
            }
        }

        if ($alteredFields) {
            foreach ($alteredFields as $fieldName => $fieldSpec) {
                $this->alterField($tableName, $fieldName, $fieldSpec);
            }
        }

        if ($newIndexes) {
            foreach ($newIndexes as $indexName => $indexSpec) {
                $this->createIndex($tableName, $indexName, $indexSpec);
            }
        }

        if ($alteredIndexes) {
            foreach ($alteredIndexes as $indexName => $indexSpec) {
                $this->alterIndex($tableName, $indexName, $indexSpec);
            }
        }
    }

    public function renameTable($oldTableName, $newTableName)
    {
        $this->query("ALTER TABLE \"$oldTableName\" RENAME TO \"$newTableName\"");
    }

    public function checkAndRepairTable($tableName = null)
    {
        $ok = true;

        if (!SapphireTest::using_temp_db() && !self::$checked_and_repaired) {
            $this->alterationMessage("Checking database integrity", "repaired");

            // Check for any tables with failed integrity
            if ($messages = $this->query('PRAGMA integrity_check')) {
                foreach ($messages as $message) {
                    if ($message['integrity_check'] != 'ok') {
                        Debug::show($message['integrity_check']);
                        $ok = false;
                    }
                }
            }

            // If enabled vacuum (clean and rebuild) the database
            if (self::$vacuum) {
                $this->query('VACUUM', E_USER_NOTICE);
                $message = $this->database->getConnector()->getLastError();
                if (preg_match('/authoriz/', $message)) {
                    $this->alterationMessage("VACUUM | $message", "error");
                } else {
                    $this->alterationMessage("VACUUMing", "repaired");
                }
            }
            self::$checked_and_repaired = true;
        }

        return $ok;
    }

    public function createField($table, $field, $spec)
    {
        $this->query("ALTER TABLE \"$table\" ADD \"$field\" $spec");
    }

    /**
     * Change the database type of the given field.
     * @param string $tableName The name of the tbale the field is in.
     * @param string $fieldName The name of the field to change.
     * @param string $fieldSpec The new field specification
     */
    public function alterField($tableName, $fieldName, $fieldSpec)
// Bad
class Router
{
    public function generate($path)
    {
        return $_SERVER['HOST'].$path;
    }
}

// Better
class Router
{
    private $host;

    public function __construct($host)
    {
        $this->host = $host;
    }

    public function generate($path)
    {
        return $this->host.$path;
    }
}

class Controller
{
    public function myAction(Request $request)
    {
        // Instead of
        $page = isset($_GET['page']) ? intval($_GET['page']) : 1;

        // Better (assuming you use the Symfony2 request)
        $page = $request->query->get('page', 1);
    }
}
    {
        $oldFieldList = $this->fieldList($tableName);
        $fieldNameList = '"' . implode('","', array_keys($oldFieldList)) . '"';

        if (!empty($_REQUEST['avoidConflict']) && Director::isDev()) {
            $fieldSpec = preg_replace('/\snot null\s/i', ' NOT NULL ON CONFLICT REPLACE ', $fieldSpec);
        }

        // Skip non-existing columns
        if (!array_key_exists($fieldName, $oldFieldList)) {
            return;
        }

        // Update field spec
        $newColsSpec = array();
        foreach ($oldFieldList as $name => $oldSpec) {
            $newColsSpec[] = "\"$name\" " . ($name == $fieldName ? $fieldSpec : $oldSpec);
        }

        $queries = array(
            "BEGIN TRANSACTION",
            "CREATE TABLE \"{$tableName}_alterfield_{$fieldName}\"(" . implode(',', $newColsSpec) . ")",
            "INSERT INTO \"{$tableName}_alterfield_{$fieldName}\" SELECT {$fieldNameList} FROM \"$tableName\"",
            "DROP TABLE \"$tableName\"",
            "ALTER TABLE \"{$tableName}_alterfield_{$fieldName}\" RENAME TO \"$tableName\"",
            "COMMIT"
        );

        // Remember original indexes
        $indexList = $this->indexList($tableName);

        // Then alter the table column
        foreach ($queries as $query) {
            $this->query($query.';');
        }

        // Recreate the indexes
        foreach ($indexList as $indexName => $indexSpec) {
            $this->createIndex($tableName, $indexName, $indexSpec);
        }
    }

    public function renameField($tableName, $oldName, $newName)
    {
        $oldFieldList = $this->fieldList($tableName);

        // Skip non-existing columns
        if (!array_key_exists($oldName, $oldFieldList)) {
            return;
        }

        // Determine column mappings
        $oldCols = array();
        $newColsSpec = array();
        foreach ($oldFieldList as $name => $spec) {
            $oldCols[] = "\"$name\"" . (($name == $oldName) ? " AS $newName" : '');
            $newColsSpec[] = "\"" . (($name == $oldName) ? $newName : $name) . "\" $spec";
        }

        // SQLite doesn't support direct renames through ALTER TABLE
        $queries = array(
            "BEGIN TRANSACTION",
            "CREATE TABLE \"{$tableName}_renamefield_{$oldName}\" (" . implode(',', $newColsSpec) . ")",
            "INSERT INTO \"{$tableName}_renamefield_{$oldName}\" SELECT " . implode(',', $oldCols) . " FROM \"$tableName\"",
            "DROP TABLE \"$tableName\"",
            "ALTER TABLE \"{$tableName}_renamefield_{$oldName}\" RENAME TO \"$tableName\"",
            "COMMIT"
        );

        // Remember original indexes
        $oldIndexList = $this->indexList($tableName);

        // Then alter the table column
        foreach ($queries as $query) {
            $this->query($query.';');
        }

        // Recreate the indexes
        foreach ($oldIndexList as $indexName => $indexSpec) {
            // Rename columns to new columns
            $indexSpec['value'] = preg_replace("/\"$oldName\"/i", "\"$newName\"", $indexSpec['value']);
            $this->createIndex($tableName, $indexName, $indexSpec);
        }
    }

    public function fieldList($table)
    {
        $sqlCreate = $this->preparedQuery(
            'SELECT "sql" FROM "sqlite_master" WHERE "type" = ? AND "name" = ?',
            array('table', $table)
        )->record();

        $fieldList = array();
        if ($sqlCreate && $sqlCreate['sql']) {
            preg_match('/^[\s]*CREATE[\s]+TABLE[\s]+[\'"]?[a-zA-Z0-9_\\\]+[\'"]?[\s]*\((.+)\)[\s]*$/ims',
                $sqlCreate['sql'], $matches
            );
            $fields = isset($matches[1])
                ? preg_split('/,(?=(?:[^\'"]*$)|(?:[^\'"]*[\'"][^\'"]*[\'"][^\'"]*)*$)/x', $matches[1])
                : array();
            foreach ($fields as $field) {
                $details = preg_split('/\s/', trim($field));
                $name = array_shift($details);
                $name = str_replace('"', '', trim($name));
                $fieldList[$name] = implode(' ', $details);
            }
        }
        return $fieldList;
    }

    /**
     * Create an index on a table.
     *
     * @param string $tableName The name of the table.
     * @param string $indexName The name of the index.
     * @param array $indexSpec The specification of the index, see Database::requireIndex() for more details.
     */
    public function createIndex($tableName, $indexName, $indexSpec)
    {
        $parsedSpec = $this->parseIndexSpec($indexName, $indexSpec);
        $sqliteName = $this->buildSQLiteIndexName($tableName, $indexName);
        $columns = $parsedSpec['value'];
        $unique = ($parsedSpec['type'] == 'unique') ? 'UNIQUE' : '';
        $this->query("CREATE $unique INDEX IF NOT EXISTS \"$sqliteName\" ON \"$tableName\" ($columns)");
    }

    public function alterIndex($tableName, $indexName, $indexSpec)
    {
        // Drop existing index
        $sqliteName = $this->buildSQLiteIndexName($tableName, $indexName);
        $this->query("DROP INDEX IF EXISTS \"$sqliteName\"");

        // Create the index
        $this->createIndex($tableName, $indexName, $indexSpec);
    }

    /**
     * Builds the internal SQLLite index name given the silverstripe table and index name.
     *
     * The name is built using the table and index name in order to prevent name collisions
     * between indexes of the same name across multiple tables
     *
     * @param string $tableName
     * @param string $indexName
     * @return string The SQLite3 name of the index
     */
    protected function buildSQLiteIndexName($tableName, $indexName)
    {
        return "{$tableName}_{$indexName}";
    }

    protected function parseIndexSpec($name, $spec)

    {
        $spec = parent::parseIndexSpec($name, $spec);

        // Only allow index / unique index types
        if (!in_array($spec['type'], array('index', 'unique'))) {
            $spec['type'] = 'index';
        }

        return $spec;
    }

    public function indexKey($table, $index, $spec)
    {
        return $this->buildSQLiteIndexName($table, $index);
    }

    public function indexList($table)
    {
        $indexList = array();

        // Enumerate each index and related fields
        foreach ($this->query("PRAGMA index_list(\"$table\")") as $index) {

            // The SQLite internal index name, not the actual Silverstripe name
            $indexName = $index["name"];
            $indexType = $index['unique'] ? 'unique' : 'index';

            // Determine a clean list of column names within this index
            $list = array();
            foreach ($this->query("PRAGMA index_info(\"$indexName\")") as $details) {
                $list[] = preg_replace('/^"?(.*)"?$/', '$1', $details['name']);
            }

            // Safely encode this spec
            $indexList[$indexName] = $this->parseIndexSpec($indexName, array(
                'name' => $indexName,
                'value' => $this->implodeColumnList($list),
                'type' => $indexType
            ));
        }

        return $indexList;
    }

    public function tableList()
    {
        $tables = array();
        $result = $this->preparedQuery('SELECT name FROM sqlite_master WHERE type = ?', array('table'));
        foreach ($result as $record) {
            $table = reset($record);
            $tables[strtolower($table)] = $table;
        }
        return $tables;
    }

    /**
     * Return a boolean type-formatted string
     *
     * @param array $values Contains a tokenised list of info about this data type
     * @return string
     */
    public function boolean($values)
    {
        $default = empty($values['default']) ? 0 : (int)$values['default'];
        return "BOOL NOT NULL DEFAULT $default";
    }

    /**
     * Return a date type-formatted string
     *
     * @param array $values Contains a tokenised list of info about this data type
     * @return string
     */
    public function date($values)
    {
        return "TEXT";
    }

    /**
     * Return a decimal type-formatted string
     *
     * @param array $values Contains a tokenised list of info about this data type
     * @return string
     */
    public function decimal($values)
    {
        $default = isset($values['default']) && is_numeric($values['default']) ? $values['default'] : 0;
        return "NUMERIC NOT NULL DEFAULT $default";
    }

    /**
     * Cached list of enum values indexed by table.column
     *
     * @var array
     */
    protected $enum_map = array();

    /**
     * Return a enum type-formatted string
     *
     * enums are not supported. as a workaround to store allowed values we creates an additional table
     *
     * @param array $values Contains a tokenised list of info about this data type
     * @return string
     */
    public function enum($values)
    {
        $tablefield = $values['table'] . '.' . $values['name'];
        $enumValues = implode(',', $values['enums']);

        // Ensure the cache table exists
        if (empty($this->enum_map)) {
            $this->query("CREATE TABLE IF NOT EXISTS \"SQLiteEnums\" (\"TableColumn\" TEXT PRIMARY KEY, \"EnumList\" TEXT)");
        }

        // Ensure the table row exists
        if (empty($this->enum_map[$tablefield]) || $this->enum_map[$tablefield] != $enumValues) {
            $this->preparedQuery(
                "REPLACE INTO SQLiteEnums (TableColumn, EnumList) VALUES (?, ?)",
                array($tablefield, $enumValues)
            );
            $this->enum_map[$tablefield] = $enumValues;
        }

        // Set default
        if (!empty($values['default'])) {
            $default = str_replace(array('"', "'", "\\", "\0"), "", $values['default']);
            return "TEXT DEFAULT '$default'";
        } else {
            return 'TEXT';
        }
    }

    /**
     * Return a set type-formatted string
     * This type doesn't exist in SQLite either
     *
     * @see SQLite3SchemaManager::enum()
     *
     * @param array $values Contains a tokenised list of info about this data type
     * @return string
     */
    public function set($values)
    {
        return $this->enum($values);
    }

    /**
     * Return a float type-formatted string
     *
     * @param array $values Contains a tokenised list of info about this data type
     * @return string
     */
    public function float($values)
    {
        return "REAL";
    }

    /**
     * Return a Double type-formatted string
     *
     * @param array $values Contains a tokenised list of info about this data type
     * @return string
     */
    public function double($values)
    {
        return "REAL";
    }

    /**
     * Return a int type-formatted string
     *
     * @param array $values Contains a tokenised list of info about this data type
     * @return string
     */
    public function int($values)
    {
        return "INTEGER({$values['precision']}) " . strtoupper($values['null']) . " DEFAULT " . (int)$values['default'];
    }

    /**
     * Return a bigint type-formatted string
     *
     * @param array $values Contains a tokenised list of info about this data type
     * @return string
     */
    public function bigint($values)
    {
        return $this->int($values);
    }

    /**
     * Return a datetime type-formatted string
     * For SQLite3, we simply return the word 'TEXT', no other parameters are necessary
     *
     * @param array $values Contains a tokenised list of info about this data type
     * @return string
     */
    public function datetime($values)
    {
        return "DATETIME";
    }

    /**
     * Return a text type-formatted string
     *
     * @param array $values Contains a tokenised list of info about this data type
     * @return string
     */
    public function text($values)
    {
        return 'TEXT';
    }

    /**
     * Return a time type-formatted string
     *
     * @param array $values Contains a tokenised list of info about this data type
     * @return string
     */
    public function time($values)
    {
        return "TEXT";
    }

    /**
     * Return a varchar type-formatted string
     *
     * @param array $values Contains a tokenised list of info about this data type
     * @return string
     */
    public function varchar($values)
    {
        return "VARCHAR({$values['precision']}) COLLATE NOCASE";
    }

    /*
     * Return a 4 digit numeric type.  MySQL has a proprietary 'Year' type.
     * For SQLite3 we use TEXT
     */
    public function year($values, $asDbValue = false)
    {
        return "TEXT";
    }

    public function IdColumn($asDbValue = false, $hasAutoIncPK = true)
    {
        return 'INTEGER PRIMARY KEY AUTOINCREMENT';
    }

    public function hasTable($tableName)
    {
        return (bool)$this->preparedQuery(
            'SELECT "name" FROM "sqlite_master" WHERE "type" = ? AND "name" = ?',
            array('table', $tableName)
        )->first();
    }

    /**
     * Return enum values for the given field
     *
     * @param string $tableName
     * @param string $fieldName
     * @return array
     */
    public function enumValuesForField($tableName, $fieldName)
    {
        $tablefield = "$tableName.$fieldName";

        // Check already cached values for this field
        if (!empty($this->enum_map[$tablefield])) {
            return explode(',', $this->enum_map[$tablefield]);
        }

        // Retrieve and cache these details from the database
        $classnameinfo = $this->preparedQuery(
            "SELECT EnumList FROM SQLiteEnums WHERE TableColumn = ?",
            array($tablefield)
        )->first();
        if ($classnameinfo) {
            $valueList = $classnameinfo['EnumList'];
            $this->enum_map[$tablefield] = $valueList;
            return explode(',', $valueList);
        }

        // Fallback to empty list
        return array();
    }

    public function dbDataType($type)
    {
        $values = array(
            'unsigned integer' => 'INT'
        );

        if (isset($values[$type])) {
            return $values[$type];
        } else {
            return '';
        }
    }
}


1			<?php
2
3			namespace SilverStripe\SQLite;
4
5			use SilverStripe\ORM\Connect\DBSchemaManager;
6			use Exception;
7			use SapphireTest;
8			use Debug;
9			use Director;
10
11			/**
12			* SQLite schema manager class
13			*
14			* @package SQLite3
15			*/
16			class SQLite3SchemaManager extends DBSchemaManager
17			{
18
19			/**
20			* Instance of the database controller this schema belongs to
21			*
22			* @var SQLite3Database
23			*/
24			protected $database = null;
25
26			/**
27			* Flag indicating whether or not the database has been checked and repaired
28			*
29			* @var boolean
30			*/
31			protected static $checked_and_repaired = false;
32
33			/**
34			* Should schema be vacuumed during checkeAndRepairTable?
35			*
36			* @var boolean
37			*/
38			public static $vacuum = true;
39
40			public function createDatabase($name)
41			{
42			// Ensure that any existing database is cleared before connection
43			$this->dropDatabase($name);
44			}
45
46			public function dropDatabase($name)
47			{
48			// No need to delete database files if operating purely within memory
49			if ($this->database->getLivesInMemory()) {
50			return;
51			}
52
53			// If using file based database ensure any existing file is removed
54			$parameters = $this->database->getParameters();
55			$fullpath = $parameters['path'] . '/' . $name;
56			if (is_writable($fullpath)) {
57			unlink($fullpath);
58			}
59			}
60
61			public function databaseList()
62			{
63			$parameters = $this->database->getParameters();
64
65			// If in-memory use the current database name only
66			if ($this->database->getLivesInMemory()) {
67			return array($parameters['database']);
68			}
69
70			// If using file based database enumerate files in the database directory
71			$directory = $parameters['path'];
72			$files = scandir($directory);
73
74			// Filter each file in this directory
75			$databases = array();
76			if ($files !== false) {
77			foreach ($files as $file) {
78
79			// Filter non-files
80			if (!is_file("$directory/$file")) {
81			continue;
82			}
83
84			// Filter those with correct extension
85			if (!SQLite3Database::is_valid_database_name($file)) {
86			continue;
87			}
88
89			$databases[] = $file;
90			}
91			}
92			return $databases;
93			}
94
95			public function databaseExists($name)
96			{
97			$databases = $this->databaseList();
98			return in_array($name, $databases);
99			}
100
101			/**
102			* Empties any cached enum values
103			*/
104			public function flushCache()
105			{
106			$this->enum_map = array();
107			}
108
109			public function schemaUpdate($callback)
110			{
111			// Set locking mode
112			$this->database->setPragma('locking_mode', 'EXCLUSIVE');
113			$this->checkAndRepairTable();
114			$this->flushCache();
115
116			// Initiate schema update
117			$error = null;
118			try {
119			parent::schemaUpdate($callback);
120			} catch (Exception $ex) {
121			$error = $ex;
122			}
123
124			// Revert locking mode
125			$this->database->setPragma('locking_mode', SQLite3Database::$default_pragma['locking_mode']);
126
127			if ($error) {
128			throw $error;
129			}
130			}
131
132			/**
133			* Empty a specific table
134			*
135			* @param string $table
136			*/
137			public function clearTable($table)
138			{
139			if ($table != 'SQLiteEnums') {
140			$this->query("DELETE FROM \"$table\"");
141			}
142			}
143
144			public function createTable($table, $fields = null, $indexes = null, $options = null, $advancedOptions = null)
			0 ignored issues – show Documentation introduced 2016-06-29 02:05 UTC by Report Bug Copy Issue Report The return type could not be reliably inferred; please add a `@return` annotation. Our type inference engine in quite powerful, but sometimes the code does not provide enough clues to go by. In these cases we request you to add a `@return` annotation as described here. Loading history...
145			{
146			if (!isset($fields['ID'])) {
147			$fields['ID'] = $this->IdColumn();
148			}
149
150			$fieldSchemata = array();
151			if ($fields) {
152			foreach ($fields as $k => $v) {
153			$fieldSchemata[] = "\"$k\" $v";
154			}
155			}
156			$fieldSchemas = implode(",\n", $fieldSchemata);
157
158			// Switch to "CREATE TEMPORARY TABLE" for temporary tables
159			$temporary = empty($options['temporary']) ? "" : "TEMPORARY";
160			$this->query("CREATE $temporary TABLE \"$table\" (
161			$fieldSchemas
162			)");
163
164			if ($indexes) {
165			foreach ($indexes as $indexName => $indexDetails) {
166			$this->createIndex($table, $indexName, $indexDetails);
167			}
168			}
169
170			return $table;
171			}
172
173			public function alterTable($tableName, $newFields = null, $newIndexes = null, $alteredFields = null,
174			$alteredIndexes = null, $alteredOptions = null, $advancedOptions = null
175			) {
176			if ($newFields) {
177			foreach ($newFields as $fieldName => $fieldSpec) {
178			$this->createField($tableName, $fieldName, $fieldSpec);
179			}
180			}
181
182			if ($alteredFields) {
183			foreach ($alteredFields as $fieldName => $fieldSpec) {
184			$this->alterField($tableName, $fieldName, $fieldSpec);
185			}
186			}
187
188			if ($newIndexes) {
189			foreach ($newIndexes as $indexName => $indexSpec) {
190			$this->createIndex($tableName, $indexName, $indexSpec);
191			}
192			}
193
194			if ($alteredIndexes) {
195			foreach ($alteredIndexes as $indexName => $indexSpec) {
196			$this->alterIndex($tableName, $indexName, $indexSpec);
197			}
198			}
199			}
200
201			public function renameTable($oldTableName, $newTableName)
202			{
203			$this->query("ALTER TABLE \"$oldTableName\" RENAME TO \"$newTableName\"");
204			}
205
206			public function checkAndRepairTable($tableName = null)
207			{
208			$ok = true;
209
210			if (!SapphireTest::using_temp_db() && !self::$checked_and_repaired) {
211			$this->alterationMessage("Checking database integrity", "repaired");
212
213			// Check for any tables with failed integrity
214			if ($messages = $this->query('PRAGMA integrity_check')) {
215			foreach ($messages as $message) {
216			if ($message['integrity_check'] != 'ok') {
217			Debug::show($message['integrity_check']);
218			$ok = false;
219			}
220			}
221			}
222
223			// If enabled vacuum (clean and rebuild) the database
224			if (self::$vacuum) {
225			$this->query('VACUUM', E_USER_NOTICE);
226			$message = $this->database->getConnector()->getLastError();
227			if (preg_match('/authoriz/', $message)) {
228			$this->alterationMessage("VACUUM \| $message", "error");
229			} else {
230			$this->alterationMessage("VACUUMing", "repaired");
231			}
232			}
233			self::$checked_and_repaired = true;
234			}
235
236			return $ok;
237			}
238
239			public function createField($table, $field, $spec)
240			{
241			$this->query("ALTER TABLE \"$table\" ADD \"$field\" $spec");
242			}
243
244			/**
245			* Change the database type of the given field.
246			* @param string $tableName The name of the tbale the field is in.
247			* @param string $fieldName The name of the field to change.
248			* @param string $fieldSpec The new field specification
249			*/
250			public function alterField($tableName, $fieldName, $fieldSpec)
			0 ignored issues – show Coding Style introduced 2016-03-23 12:58 UTC by Report Bug Copy Issue Report `alterField` uses the super-global variable `$_REQUEST` which is generally not recommended. Instead of super-globals, we recommend to explicitly inject the dependencies of your class. This makes your code less dependent on global state and it becomes generally more testable: // Bad class Router { public function generate($path) { return $_SERVER['HOST'].$path; } } // Better class Router { private $host; public function __construct($host) { $this->host = $host; } public function generate($path) { return $this->host.$path; } } class Controller { public function myAction(Request $request) { // Instead of $page = isset($_GET['page']) ? intval($_GET['page']) : 1; // Better (assuming you use the Symfony2 request) $page = $request->query->get('page', 1); } } Loading history...
251			{
252			$oldFieldList = $this->fieldList($tableName);
253			$fieldNameList = '"' . implode('","', array_keys($oldFieldList)) . '"';
254
255			if (!empty($_REQUEST['avoidConflict']) && Director::isDev()) {
256			$fieldSpec = preg_replace('/\snot null\s/i', ' NOT NULL ON CONFLICT REPLACE ', $fieldSpec);
257			}
258
259			// Skip non-existing columns
260			if (!array_key_exists($fieldName, $oldFieldList)) {
261			return;
262			}
263
264			// Update field spec
265			$newColsSpec = array();
266			foreach ($oldFieldList as $name => $oldSpec) {
267			$newColsSpec[] = "\"$name\" " . ($name == $fieldName ? $fieldSpec : $oldSpec);
268			}
269
270			$queries = array(
271			"BEGIN TRANSACTION",
272			"CREATE TABLE \"{$tableName}_alterfield_{$fieldName}\"(" . implode(',', $newColsSpec) . ")",
273			"INSERT INTO \"{$tableName}_alterfield_{$fieldName}\" SELECT {$fieldNameList} FROM \"$tableName\"",
274			"DROP TABLE \"$tableName\"",
275			"ALTER TABLE \"{$tableName}_alterfield_{$fieldName}\" RENAME TO \"$tableName\"",
276			"COMMIT"
277			);
278
279			// Remember original indexes
280			$indexList = $this->indexList($tableName);
281
282			// Then alter the table column
283			foreach ($queries as $query) {
284			$this->query($query.';');
285			}
286
287			// Recreate the indexes
288			foreach ($indexList as $indexName => $indexSpec) {
289			$this->createIndex($tableName, $indexName, $indexSpec);
290			}
291			}
292
293			public function renameField($tableName, $oldName, $newName)
294			{
295			$oldFieldList = $this->fieldList($tableName);
296
297			// Skip non-existing columns
298			if (!array_key_exists($oldName, $oldFieldList)) {
299			return;
300			}
301
302			// Determine column mappings
303			$oldCols = array();
304			$newColsSpec = array();
305			foreach ($oldFieldList as $name => $spec) {
306			$oldCols[] = "\"$name\"" . (($name == $oldName) ? " AS $newName" : '');
307			$newColsSpec[] = "\"" . (($name == $oldName) ? $newName : $name) . "\" $spec";
308			}
309
310			// SQLite doesn't support direct renames through ALTER TABLE
311			$queries = array(
312			"BEGIN TRANSACTION",
313			"CREATE TABLE \"{$tableName}_renamefield_{$oldName}\" (" . implode(',', $newColsSpec) . ")",
314			"INSERT INTO \"{$tableName}_renamefield_{$oldName}\" SELECT " . implode(',', $oldCols) . " FROM \"$tableName\"",
315			"DROP TABLE \"$tableName\"",
316			"ALTER TABLE \"{$tableName}_renamefield_{$oldName}\" RENAME TO \"$tableName\"",
317			"COMMIT"
318			);
319
320			// Remember original indexes
321			$oldIndexList = $this->indexList($tableName);
322
323			// Then alter the table column
324			foreach ($queries as $query) {
325			$this->query($query.';');
326			}
327
328			// Recreate the indexes
329			foreach ($oldIndexList as $indexName => $indexSpec) {
330			// Rename columns to new columns
331			$indexSpec['value'] = preg_replace("/\"$oldName\"/i", "\"$newName\"", $indexSpec['value']);
332			$this->createIndex($tableName, $indexName, $indexSpec);
333			}
334			}
335
336			public function fieldList($table)
337			{
338			$sqlCreate = $this->preparedQuery(
339			'SELECT "sql" FROM "sqlite_master" WHERE "type" = ? AND "name" = ?',
340			array('table', $table)
341			)->record();
342
343			$fieldList = array();
344			if ($sqlCreate && $sqlCreate['sql']) {
345			preg_match('/^[\s]CREATE[\s]+TABLE[\s]+[\'"]?[a-zA-Z0-9_\\\]+[\'"]?[\s]\((.+)\)[\s]*$/ims',
346			$sqlCreate['sql'], $matches
347			);
348			$fields = isset($matches[1])
349			? preg_split('/,(?=(?:[^\'"]$)\|(?:[^\'"][\'"][^\'"][\'"][^\'"])*$)/x', $matches[1])
350			: array();
351			foreach ($fields as $field) {
352			$details = preg_split('/\s/', trim($field));
353			$name = array_shift($details);
354			$name = str_replace('"', '', trim($name));
355			$fieldList[$name] = implode(' ', $details);
356			}
357			}
358			return $fieldList;
359			}
360
361			/**
362			* Create an index on a table.
363			*
364			* @param string $tableName The name of the table.
365			* @param string $indexName The name of the index.
366			* @param array $indexSpec The specification of the index, see Database::requireIndex() for more details.
367			*/
368			public function createIndex($tableName, $indexName, $indexSpec)
369			{
370			$parsedSpec = $this->parseIndexSpec($indexName, $indexSpec);
371			$sqliteName = $this->buildSQLiteIndexName($tableName, $indexName);
372			$columns = $parsedSpec['value'];
373			$unique = ($parsedSpec['type'] == 'unique') ? 'UNIQUE' : '';
374			$this->query("CREATE $unique INDEX IF NOT EXISTS \"$sqliteName\" ON \"$tableName\" ($columns)");
375			}
376
377			public function alterIndex($tableName, $indexName, $indexSpec)
378			{
379			// Drop existing index
380			$sqliteName = $this->buildSQLiteIndexName($tableName, $indexName);
381			$this->query("DROP INDEX IF EXISTS \"$sqliteName\"");
382
383			// Create the index
384			$this->createIndex($tableName, $indexName, $indexSpec);
385			}
386
387			/**
388			* Builds the internal SQLLite index name given the silverstripe table and index name.
389			*
390			* The name is built using the table and index name in order to prevent name collisions
391			* between indexes of the same name across multiple tables
392			*
393			* @param string $tableName
394			* @param string $indexName
395			* @return string The SQLite3 name of the index
396			*/
397			protected function buildSQLiteIndexName($tableName, $indexName)
398			{
399			return "{$tableName}_{$indexName}";
400			}
401
402			protected function parseIndexSpec($name, $spec)
			0 ignored issues – show Documentation introduced 2016-06-29 02:05 UTC by Report Bug Copy Issue Report The return type could not be reliably inferred; please add a `@return` annotation. Our type inference engine in quite powerful, but sometimes the code does not provide enough clues to go by. In these cases we request you to add a `@return` annotation as described here. Loading history...
403			{
404			$spec = parent::parseIndexSpec($name, $spec);
405
406			// Only allow index / unique index types
407			if (!in_array($spec['type'], array('index', 'unique'))) {
408			$spec['type'] = 'index';
409			}
410
411			return $spec;
412			}
413
414			public function indexKey($table, $index, $spec)
415			{
416			return $this->buildSQLiteIndexName($table, $index);
417			}
418
419			public function indexList($table)
420			{
421			$indexList = array();
422
423			// Enumerate each index and related fields
424			foreach ($this->query("PRAGMA index_list(\"$table\")") as $index) {
425
426			// The SQLite internal index name, not the actual Silverstripe name
427			$indexName = $index["name"];
428			$indexType = $index['unique'] ? 'unique' : 'index';
429
430			// Determine a clean list of column names within this index
431			$list = array();
432			foreach ($this->query("PRAGMA index_info(\"$indexName\")") as $details) {
433			$list[] = preg_replace('/^"?(.*)"?$/', '$1', $details['name']);
434			}
435
436			// Safely encode this spec
437			$indexList[$indexName] = $this->parseIndexSpec($indexName, array(
438			'name' => $indexName,
439			'value' => $this->implodeColumnList($list),
440			'type' => $indexType
441			));
442			}
443
444			return $indexList;
445			}
446
447			public function tableList()
448			{
449			$tables = array();
450			$result = $this->preparedQuery('SELECT name FROM sqlite_master WHERE type = ?', array('table'));
451			foreach ($result as $record) {
452			$table = reset($record);
453			$tables[strtolower($table)] = $table;
454			}
455			return $tables;
456			}
457
458			/**
459			* Return a boolean type-formatted string
460			*
461			* @param array $values Contains a tokenised list of info about this data type
462			* @return string
463			*/
464			public function boolean($values)
465			{
466			$default = empty($values['default']) ? 0 : (int)$values['default'];
467			return "BOOL NOT NULL DEFAULT $default";
468			}
469
470			/**
471			* Return a date type-formatted string
472			*
473			* @param array $values Contains a tokenised list of info about this data type
474			* @return string
475			*/
476			public function date($values)
477			{
478			return "TEXT";
479			}
480
481			/**
482			* Return a decimal type-formatted string
483			*
484			* @param array $values Contains a tokenised list of info about this data type
485			* @return string
486			*/
487			public function decimal($values)
488			{
489			$default = isset($values['default']) && is_numeric($values['default']) ? $values['default'] : 0;
490			return "NUMERIC NOT NULL DEFAULT $default";
491			}
492
493			/**
494			* Cached list of enum values indexed by table.column
495			*
496			* @var array
497			*/
498			protected $enum_map = array();
499
500			/**
501			* Return a enum type-formatted string
502			*
503			* enums are not supported. as a workaround to store allowed values we creates an additional table
504			*
505			* @param array $values Contains a tokenised list of info about this data type
506			* @return string
507			*/
508			public function enum($values)
509			{
510			$tablefield = $values['table'] . '.' . $values['name'];
511			$enumValues = implode(',', $values['enums']);
512
513			// Ensure the cache table exists
514			if (empty($this->enum_map)) {
515			$this->query("CREATE TABLE IF NOT EXISTS \"SQLiteEnums\" (\"TableColumn\" TEXT PRIMARY KEY, \"EnumList\" TEXT)");
516			}
517
518			// Ensure the table row exists
519			if (empty($this->enum_map[$tablefield]) \|\| $this->enum_map[$tablefield] != $enumValues) {
520			$this->preparedQuery(
521			"REPLACE INTO SQLiteEnums (TableColumn, EnumList) VALUES (?, ?)",
522			array($tablefield, $enumValues)
523			);
524			$this->enum_map[$tablefield] = $enumValues;
525			}
526
527			// Set default
528			if (!empty($values['default'])) {
529			$default = str_replace(array('"', "'", "\\", "\0"), "", $values['default']);
530			return "TEXT DEFAULT '$default'";
531			} else {
532			return 'TEXT';
533			}
534			}
535
536			/**
537			* Return a set type-formatted string
538			* This type doesn't exist in SQLite either
539			*
540			* @see SQLite3SchemaManager::enum()
541			*
542			* @param array $values Contains a tokenised list of info about this data type
543			* @return string
544			*/
545			public function set($values)
546			{
547			return $this->enum($values);
548			}
549
550			/**
551			* Return a float type-formatted string
552			*
553			* @param array $values Contains a tokenised list of info about this data type
554			* @return string
555			*/
556			public function float($values)
557			{
558			return "REAL";
559			}
560
561			/**
562			* Return a Double type-formatted string
563			*
564			* @param array $values Contains a tokenised list of info about this data type
565			* @return string
566			*/
567			public function double($values)
568			{
569			return "REAL";
570			}
571
572			/**
573			* Return a int type-formatted string
574			*
575			* @param array $values Contains a tokenised list of info about this data type
576			* @return string
577			*/
578			public function int($values)
579			{
580			return "INTEGER({$values['precision']}) " . strtoupper($values['null']) . " DEFAULT " . (int)$values['default'];
581			}
582
583			/**
584			* Return a bigint type-formatted string
585			*
586			* @param array $values Contains a tokenised list of info about this data type
587			* @return string
588			*/
589			public function bigint($values)
590			{
591			return $this->int($values);
592			}
593
594			/**
595			* Return a datetime type-formatted string
596			* For SQLite3, we simply return the word 'TEXT', no other parameters are necessary
597			*
598			* @param array $values Contains a tokenised list of info about this data type
599			* @return string
600			*/
601			public function datetime($values)
602			{
603			return "DATETIME";
604			}
605
606			/**
607			* Return a text type-formatted string
608			*
609			* @param array $values Contains a tokenised list of info about this data type
610			* @return string
611			*/
612			public function text($values)
613			{
614			return 'TEXT';
615			}
616
617			/**
618			* Return a time type-formatted string
619			*
620			* @param array $values Contains a tokenised list of info about this data type
621			* @return string
622			*/
623			public function time($values)
624			{
625			return "TEXT";
626			}
627
628			/**
629			* Return a varchar type-formatted string
630			*
631			* @param array $values Contains a tokenised list of info about this data type
632			* @return string
633			*/
634			public function varchar($values)
635			{
636			return "VARCHAR({$values['precision']}) COLLATE NOCASE";
637			}
638
639			/*
640			* Return a 4 digit numeric type. MySQL has a proprietary 'Year' type.
641			* For SQLite3 we use TEXT
642			*/
643			public function year($values, $asDbValue = false)
644			{
645			return "TEXT";
646			}
647
648			public function IdColumn($asDbValue = false, $hasAutoIncPK = true)
649			{
650			return 'INTEGER PRIMARY KEY AUTOINCREMENT';
651			}
652
653			public function hasTable($tableName)
654			{
655			return (bool)$this->preparedQuery(
656			'SELECT "name" FROM "sqlite_master" WHERE "type" = ? AND "name" = ?',
657			array('table', $tableName)
658			)->first();
659			}
660
661			/**
662			* Return enum values for the given field
663			*
664			* @param string $tableName
665			* @param string $fieldName
666			* @return array
667			*/
668			public function enumValuesForField($tableName, $fieldName)
669			{
670			$tablefield = "$tableName.$fieldName";
671
672			// Check already cached values for this field
673			if (!empty($this->enum_map[$tablefield])) {
674			return explode(',', $this->enum_map[$tablefield]);
675			}
676
677			// Retrieve and cache these details from the database
678			$classnameinfo = $this->preparedQuery(
679			"SELECT EnumList FROM SQLiteEnums WHERE TableColumn = ?",
680			array($tablefield)
681			)->first();
682			if ($classnameinfo) {
683			$valueList = $classnameinfo['EnumList'];
684			$this->enum_map[$tablefield] = $valueList;
685			return explode(',', $valueList);
686			}
687
688			// Fallback to empty list
689			return array();
690			}
691
692			public function dbDataType($type)
693			{
694			$values = array(
695			'unsigned integer' => 'INT'
696			);
697
698			if (isset($values[$type])) {
699			return $values[$type];
700			} else {
701			return '';
702			}
703			}
704			}
705

silverstripe-labs / silverstripe-sqlite3

Pull Request — master (#23)

SQLite3SchemaManager::alterTable() D

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like