From a47c84c4bbb84aa27fdca262808e5f63fb95fa15 Mon Sep 17 00:00:00 2001 From: skodak Date: Sat, 30 Aug 2008 18:46:18 +0000 Subject: [PATCH] MDL-15635 sqlite fixes - by Andrei Bautu --- lib/ddl/sqlite_sql_generator.php | 208 ++++++++++++++++++++---- lib/dml/pdo_moodle_database.php | 12 +- lib/dml/sqlite3_pdo_moodle_database.php | 66 +++++--- 3 files changed, 223 insertions(+), 63 deletions(-) diff --git a/lib/ddl/sqlite_sql_generator.php b/lib/ddl/sqlite_sql_generator.php index 3f8c7fce48..7828688840 100644 --- a/lib/ddl/sqlite_sql_generator.php +++ b/lib/ddl/sqlite_sql_generator.php @@ -52,15 +52,9 @@ class sqlite_sql_generator extends sql_generator { public $sequence_name = 'INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL'; //Particular name for inline sequences in this generator public $unsigned_allowed = false; // To define in the generator must handle unsigned information + public $enum_inline_code = true; //Does the generator need to add inline code in the column definition public $enum_extra_code = false; //Does the generator need to add extra code to generate code for the enums in the table - public $add_after_clause = true; // Does the generator need to add the after clause for fields - - public $concat_character = null; //Characters to be used as concatenation operator. If not defined - //MySQL CONCAT function will be use - - public $alter_column_sql = 'ALTER TABLE TABLENAME MODIFY COLUMN COLUMNSPECS'; //The SQL template to alter columns - public $drop_index_sql = 'ALTER TABLE TABLENAME DROP INDEX INDEXNAME'; //SQL sentence to drop one index //TABLENAME, INDEXNAME are dinamically replaced @@ -167,13 +161,114 @@ class sqlite_sql_generator extends sql_generator { return $dbtype; } + /** + * Function to emulate full ALTER TABLE which SQLite does not support. + * The function can be used to drop a column ($xmldb_delete_field != null and + * $xmldb_add_field == null), add a column ($xmldb_delete_field == null and + * $xmldb_add_field != null), change/rename a column ($xmldb_delete_field == null + * and $xmldb_add_field == null). + * @param xmldb_table $xmldb_table table to change + * @param xmldb_field $xmldb_add_field column to create/modify (full specification is required) + * @param xmldb_field $xmldb_delete_field column to delete/modify (only name field is required) + * @return array of strings (SQL statements to alter the table structure) + */ + protected function getAlterTableSchema($xmldb_table, $xmldb_add_field=NULL, $xmldb_delete_field=NULL) { + /// Get the quoted name of the table and field + $tablename = $this->getTableName($xmldb_table); + + $oldname = $xmldb_delete_field ? $xmldb_delete_field->getName() : NULL; + $newname = $xmldb_add_field ? $xmldb_add_field->getName() : NULL; + if($xmldb_delete_field) { + $xmldb_table->deleteField($oldname); + } + if($xmldb_add_field) { + $xmldb_table->addField($xmldb_add_field); + } + if($oldname) { + // alter indexes + $indexes = $xmldb_table->getIndexes(); + foreach($indexes as $index) { + $fields = $index->getFields(); + $i = array_search($oldname, $fields); + if($i!==FALSE) { + if($newname) { + $fields[$i] = $newname; + } else { + unset($fields[$i]); + } + $xmldb_table->deleteIndex($index->getName()); + if(count($fields)) { + $index->setFields($fields); + $xmldb_table->addIndex($index); + } + } + } + // alter keys + $keys = $xmldb_table->getKeys(); + foreach($keys as $key) { + $fields = $key->getFields(); + $reffields = $key->getRefFields(); + $i = array_search($oldname, $fields); + if($i!==FALSE) { + if($newname) { + $fields[$i] = $newname; + } else { + unset($fields[$i]); + unset($reffields[$i]); + } + $xmldb_table->deleteKey($key->getName()); + if(count($fields)) { + $key->setFields($fields); + $key->setRefFields($fields); + $xmldb_table->addkey($key); + } + } + } + } + // prepare data copy + $fields = $xmldb_table->getFields(); + foreach ($fields as $key => $field) { + $fieldname = $field->getName(); + if($fieldname == $newname && $oldname && $oldname != $newname) { + // field rename operation + $fields[$key] = $this->getEncQuoted($oldname) . ' AS ' . $this->getEncQuoted($newname); + } else { + $fields[$key] = $this->getEncQuoted($field->getName()); + } + } + $fields = implode(',', $fields); + $results[] = 'BEGIN TRANSACTION'; + $results[] = 'CREATE TEMPORARY TABLE temp_data AS SELECT * FROM ' . $tablename; + $results[] = 'DROP TABLE ' . $tablename; + $results = array_merge($results, $this->getCreateTableSQL($xmldb_table)); + $results[] = 'INSERT INTO ' . $tablename . ' SELECT ' . $fields . ' FROM temp_data'; + $results[] = 'DROP TABLE temp_data'; + $results[] = 'COMMIT'; + return $results; + } + + /** + * Given one xmldb_table and one xmldb_field, return the SQL statements needded to alter the field in the table + */ + public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) { + return $this->getAlterTableSchema($xmldb_table, $xmldb_field, $xmldb_field); + } + + /** + * Given one xmldb_table and one xmldb_key, return the SQL statements needded to add the key to the table + * note that undelying indexes will be added as parametrised by $xxxx_keys and $xxxx_index parameters + */ + public function getAddKeySQL($xmldb_table, $xmldb_key) { + $xmldb_table->addKey($xmldb_key); + return $this->getAlterTableSchema($xmldb_table); + } + /** * Given one xmldb_table and one xmldb_field, return the SQL statements needded to create its enum * (usually invoked from getModifyEnumSQL() */ public function getCreateEnumSQL($xmldb_table, $xmldb_field) { - /// For MySQL, just alter the field - return $this->getAlterFieldSQL($xmldb_table, $xmldb_field); + return $this->getAlterTableSchema($xmldb_table, $xmldb_field, $xmldb_field); } /** @@ -181,8 +276,7 @@ class sqlite_sql_generator extends sql_generator { * (usually invoked from getModifyEnumSQL() */ public function getDropEnumSQL($xmldb_table, $xmldb_field) { - /// For MySQL, just alter the field - return $this->getAlterFieldSQL($xmldb_table, $xmldb_field); + return $this->getAlterTableSchema($xmldb_table, $xmldb_field, $xmldb_field); } /** @@ -190,9 +284,7 @@ class sqlite_sql_generator extends sql_generator { * (usually invoked from getModifyDefaultSQL() */ public function getCreateDefaultSQL($xmldb_table, $xmldb_field) { - /// Just a wrapper over the getAlterFieldSQL() function for MySQL that - /// is capable of handling defaults - return $this->getAlterFieldSQL($xmldb_table, $xmldb_field); + return $this->getAlterTableSchema($xmldb_table, $xmldb_field, $xmldb_field); } /** @@ -201,37 +293,72 @@ class sqlite_sql_generator extends sql_generator { * SQLite is pretty diferent from the standard to justify this oveloading */ public function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) { + $oldfield = clone($xmldb_field); + $xmldb_field->setName($newname); + return $this->getAlterTableSchema($xmldb_table, $xmldb_field, $oldfield); + } - // TODO: Add code to rename column - - /// Need a clone of xmldb_field to perform the change leaving original unmodified - $xmldb_field_clone = clone($xmldb_field); - - /// Change the name of the field to perform the change - $xmldb_field_clone->setName($xmldb_field_clone->getName() . ' ' . $newname); + /** + * Given one xmldb_table and one xmldb_index, return the SQL statements needded to rename the index in the table + */ + function getRenameIndexSQL($xmldb_table, $xmldb_index, $newname) { + /// Get the real index name + $dbindexname = $this->mdb->get_manager()->find_index_name($xmldb_table, $xmldb_index); + $xmldb_index->setName($newname); + $results = array('DROP INDEX ' . $dbindexname); + $results = array_merge($results, $this->getCreateIndexSQL($xmldb_table, $xmldb_index)); + return $results; + } - $fieldsql = $this->getFieldSQL($xmldb_field_clone); + /** + * Given one xmldb_table and one xmldb_key, return the SQL statements needded to rename the key in the table + * Experimental! Shouldn't be used at all! + */ + public function getRenameKeySQL($xmldb_table, $xmldb_key, $newname) { + $xmldb_table->deleteKey($xmldb_key->getName()); + $xmldb_key->setName($newname); + $xmldb_table->addkey($xmldb_key); + return $this->getAlterTableSchema($xmldb_table); + } - $sql = 'ALTER TABLE ' . $this->getTableName($xmldb_table) . ' CHANGE ' . $fieldsql; + /** + * Given one xmldb_table and one xmldb_field, return the SQL statements needded to drop the field from the table + */ + public function getDropFieldSQL($xmldb_table, $xmldb_field) { + return $this->getAlterTableSchema($xmldb_table, NULL, $xmldb_field); + } - return array($sql); + /** + * Given one xmldb_table and one xmldb_index, return the SQL statements needded to drop the index from the table + */ + public function getDropIndexSQL($xmldb_table, $xmldb_index) { + $xmldb_table->deleteIndex($xmldb_index->getName()); + return $this->getAlterTableSchema($xmldb_table); } + /** + * Given one xmldb_table and one xmldb_index, return the SQL statements needded to drop the index from the table + */ + public function getDropKeySQL($xmldb_table, $xmldb_key) { + $xmldb_table->deleteKey($xmldb_key->getName()); + return $this->getAlterTableSchema($xmldb_table); + } + /** * Given one xmldb_table and one xmldb_field, return the SQL statements needded to drop its default * (usually invoked from getModifyDefaultSQL() */ public function getDropDefaultSQL($xmldb_table, $xmldb_field) { - /// Just a wrapper over the getAlterFieldSQL() function for MySQL that - /// is capable of handling defaults - return $this->getAlterFieldSQL($xmldb_table, $xmldb_field); + return $this->getAlterTableSchema($xmldb_table, $xmldb_field, $xmldb_field); } /** * Given one XMLDB Field, return its enum SQL */ public function getEnumSQL($xmldb_field) { - return 'enum'; + // Enum values are between /*LISTSTART*/ and /*LISTEND*/ so that + // get_columns can easily find them + return 'enum CHECK (' . $this->getEncQuoted($xmldb_field->getName()) . ' IN (/*LISTSTART*/' . implode(',', $xmldb_field->getEnumValues()) . '/*LISTEND*/))'; } /** @@ -247,14 +374,27 @@ class sqlite_sql_generator extends sql_generator { * Optionally the function allows one xmldb_field to be specified in * order to return only the check constraints belonging to one field. * Each element contains the name of the constraint and its description - * If no check constraints are found, returns an empty array - * MySQL doesn't have check constraints in this implementation, but - * we return them based on the enum fields in the table + * If no check constraints are found, returns an empty array. */ public function getCheckConstraintsFromDB($xmldb_table, $xmldb_field = null) { - - // TODO: add code for constraints - return array(); + $tablename = $xmldb_table->getName($xmldb_table); + // Fetch all the columns in the table + if (!$columns = $this->mdb->get_columns($tablename, false)) { + return array(); + } + $results = array(); + $filter = $xmldb_field ? $xmldb_field->getName() : NULL; + // Iterate over columns searching for enums + foreach ($columns as $key => $column) { + // Enum found, let's add it to the constraints list + if (!empty($column->enums) && (!$filter || $column->name == $filter)) { + $result = new object; + $result->name = $key; + $result->description = implode(', ', $column->enums); + $results[$key] = $result; + } + } + return $results; } /** diff --git a/lib/dml/pdo_moodle_database.php b/lib/dml/pdo_moodle_database.php index 7e150ee69e..4cc0c9ecf9 100644 --- a/lib/dml/pdo_moodle_database.php +++ b/lib/dml/pdo_moodle_database.php @@ -183,6 +183,7 @@ abstract class pdo_moodle_database extends moodle_database { $this->debug_query($sql); } $this->pdb->exec($sql); + $this->reset_columns(); return true; } catch (PDOException $ex) { $this->lastError = $ex->getMessage(); @@ -404,9 +405,14 @@ abstract class pdo_moodle_database extends moodle_database { $value = (int)$value; // prevent "false" problems } if (!empty($column->enums)) { - if (!in_array((string)$value, $column->enums)) { - debugging('Enum value '.s($value).' not allowed in field '.$field.' table '.$table.'.'); - return false; + // workaround for problem with wrong enums + if (is_null($value) and !$column->not_null) { + // ok - nulls allowed + } else { + if (!in_array((string)$value, $column->enums)) { + debugging('Enum value '.s($value).' not allowed in field '.$field.' table '.$table.'.'); + return false; + } } } $cleaned[$field] = $value; diff --git a/lib/dml/sqlite3_pdo_moodle_database.php b/lib/dml/sqlite3_pdo_moodle_database.php index b1d212ad81..d85f07c495 100644 --- a/lib/dml/sqlite3_pdo_moodle_database.php +++ b/lib/dml/sqlite3_pdo_moodle_database.php @@ -75,7 +75,7 @@ class sqlite3_pdo_moodle_database extends pdo_moodle_database { * @return string driver-dependent DSN */ protected function get_dsn() { - return 'sqlite:' . $this->get_dbfilepath(); + return 'sqlite:'.$this->get_dbfilepath(); } /** @@ -88,19 +88,19 @@ class sqlite3_pdo_moodle_database extends pdo_moodle_database { */ public function get_dbfilepath() { global $CFG; - if(!empty($this->dboptions['file'])) { + if (!empty($this->dboptions['file'])) { return $this->dboptions['file']; } - if($this->dbhost && $this->dbhost != 'localhost') { + if ($this->dbhost && $this->dbhost != 'localhost') { $path = $this->dbhost; } else { $path = $CFG->dataroot; } - $path = ltrim($path, '\\/') . '/'; - if(!empty($this->dbuser)) { - $path .= $this->dbuser . '_'; + $path = rtrim($path, '\\/').'/'; + if (!empty($this->dbuser)) { + $path .= $this->dbuser.'_'; } - $path .= $this->dbname . '_' . md5($this->dbpass) . $this->database_file_extension; + $path .= $this->dbname.'_'.md5($this->dbpass).$this->database_file_extension; return $path; } @@ -110,8 +110,8 @@ class sqlite3_pdo_moodle_database extends pdo_moodle_database { */ public function get_tables() { $tables = array(); - $sql = 'SELECT name FROM sqlite_master WHERE type="table" ORDER BY name'; - if($this->debug) { + $sql = 'SELECT name FROM sqlite_master WHERE type="table" UNION ALL SELECT name FROM sqlite_temp_master WHERE type="table" ORDER BY name'; + if ($this->debug) { $this->debug_query($sql); } $rstables = $this->pdb->query($sql); @@ -132,16 +132,16 @@ class sqlite3_pdo_moodle_database extends pdo_moodle_database { */ public function get_indexes($table) { $indexes = array(); - $sql = 'PRAGMA index_list('. $this->prefix . $table . ')'; - if($this->debug) { + $sql = 'PRAGMA index_list('.$this->prefix.$table.')'; + if ($this->debug) { $this->debug_query($sql); } $rsindexes = $this->pdb->query($sql); foreach($rsindexes as $index) { $unique = (boolean)$index['unique']; $index = $index['name']; - $sql = 'PRAGMA index_info("' . $index . '")'; - if($this->debug) { + $sql = 'PRAGMA index_info("'.$index.'")'; + if ($this->debug) { $this->debug_query($sql); } $rscolumns = $this->pdb->query($sql); @@ -167,19 +167,19 @@ class sqlite3_pdo_moodle_database extends pdo_moodle_database { return $this->columns[$table]; } // get table's CREATE TABLE command (we'll need it for autoincrement fields) - $sql = 'SELECT sql FROM sqlite_master WHERE type="table" AND tbl_name="'. $this->prefix . $table . '"'; - if($this->debug) { + $sql = 'SELECT sql FROM sqlite_master WHERE type="table" AND tbl_name="'.$this->prefix.$table.'"'; + if ($this->debug) { $this->debug_query($sql); } $createsql = $this->pdb->query($sql)->fetch(); - if(!$createsql) { + if (!$createsql) { return false; } $createsql = $createsql['sql']; $columns = array(); - $sql = 'PRAGMA table_info("'. $this->prefix . $table . '")'; - if($this->debug) { + $sql = 'PRAGMA table_info("'. $this->prefix.$table.'")'; + if ($this->debug) { $this->debug_query($sql); } $rscolumns = $this->pdb->query($sql); @@ -196,10 +196,10 @@ class sqlite3_pdo_moodle_database extends pdo_moodle_database { ); $type = explode('(', $row['type']); $columninfo['type'] = strtolower($type[0]); - if(count($type) > 1) { + if (count($type) > 1) { $size = explode(',', trim($type[1], ')')); $columninfo['max_length'] = $size[0]; - if(count($size) > 1) { + if (count($size) > 1) { $columninfo['scale'] = $size[1]; } } @@ -207,7 +207,7 @@ class sqlite3_pdo_moodle_database extends pdo_moodle_database { // datatype in the CREATE TABLE command. We try to guess which type is used here switch(substr($columninfo['type'], 0, 3)) { case 'int': // int integer - if($columninfo['primary_key'] && preg_match('/' . $columninfo['name'] . '\W*integer\W*primary\W*key\W*autoincrement/im', $createsql)) { + if ($columninfo['primary_key'] && preg_match('/'.$columninfo['name'].'\W+integer\W+primary\W+key\W+autoincrement/im', $createsql)) { $columninfo['meta_type'] = 'R'; $columninfo['auto_increment'] = true; } else { @@ -224,6 +224,16 @@ class sqlite3_pdo_moodle_database extends pdo_moodle_database { case 'cha': // char $columninfo['meta_type'] = 'C'; break; + case 'enu': // enums + if (preg_match('|'.$columninfo['name'].'\W+in\W+\(/\*liststart\*/(.*?)/\*listend\*/\)|im', $createsql, $tmp)) { + $tmp = explode(',', $tmp[1]); + foreach($tmp as $value) { + $columninfo['enums'][] = trim($value, '\'"'); + } + unset($tmp); + } + $columninfo['meta_type'] = 'C'; + break; case 'tex': // text case 'clo': // clob $columninfo['meta_type'] = 'X'; @@ -246,6 +256,10 @@ class sqlite3_pdo_moodle_database extends pdo_moodle_database { $columninfo['meta_type'] = 'D'; break; } + if ($columninfo['has_default'] && ($columninfo['meta_type'] == 'X' || $columninfo['meta_type']== 'C')) { + // trim extra quotes from text default values + $columninfo['default_value'] = substr($columninfo['default_value'], 1, -1); + } $columns[$columninfo['name']] = new database_column_info($columninfo); } @@ -261,10 +275,10 @@ class sqlite3_pdo_moodle_database extends pdo_moodle_database { * @return string the SQL statement with limiting clauses */ protected function get_limit_clauses($sql, $limitfrom=0, $limitnum=0) { - if($limitnum) { - $sql .= ' LIMIT ' . $limitnum; - if($limitfrom) { - $sql .= ' OFFSET ' . $limitfrom; + if ($limitnum) { + $sql .= ' LIMIT '.$limitnum; + if ($limitfrom) { + $sql .= ' OFFSET '.$limitfrom; } } return $sql; @@ -318,7 +332,7 @@ class sqlite3_pdo_moodle_database extends pdo_moodle_database { // Add items to the array on the fly, walking it // _backwards_ splicing the elements in. The loop definition // should skip first and last positions. - for ($n=count($elements)-1; $n > 0 ; $n--) { + for ($n=count($elements)-1; $n > 0; $n--) { array_splice($elements, $n, 0, $separator); } return implode('||', $elements); -- 2.39.5