From 4411f0d5f5278f70e7a5e2e1ba7f98b30aaa9e03 Mon Sep 17 00:00:00 2001 From: stronk7 Date: Thu, 30 Jul 2009 22:08:47 +0000 Subject: [PATCH] MDL-19974 postgres sql generator - simplified a lot thanks to 8.x as min req in Moodle 2.0 (alter column & defaults handling) --- lib/ddl/postgres_sql_generator.php | 118 +++++++---------------------- lib/ddl/sql_generator.php | 7 +- 2 files changed, 31 insertions(+), 94 deletions(-) diff --git a/lib/ddl/postgres_sql_generator.php b/lib/ddl/postgres_sql_generator.php index 89ef7990c7..836604e31b 100644 --- a/lib/ddl/postgres_sql_generator.php +++ b/lib/ddl/postgres_sql_generator.php @@ -180,56 +180,6 @@ class postgres_sql_generator extends sql_generator { return $results; } - /** - * Given one xmldb_table and one xmldb_field, return the SQL statements needded to add the field to the table - * PostgreSQL is pretty standard but with one severe restriction under 7.4 that forces us to overload - * this function: Default clause is not allowed when adding fields. - * - * This function can be safely removed once min req. for PG will be 8.0 - */ - public function getAddFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) { - - $skip_type_clause = is_null($skip_type_clause) ? $this->alter_column_skip_type : $skip_type_clause; - $skip_default_clause = is_null($skip_default_clause) ? $this->alter_column_skip_default : $skip_default_clause; - $skip_notnull_clause = is_null($skip_notnull_clause) ? $this->alter_column_skip_notnull : $skip_notnull_clause; - - $results = array(); - - $tablename = $this->getTableName($xmldb_table); - $fieldname = $this->getEncQuoted($xmldb_field->getName()); - - $defaultvalue = $xmldb_field->getDefault(); - - $results = parent::getAddFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, $skip_default_clause, $skip_notnull_clause); - - /// Add default (only if not skip_default) - if (!$skip_default_clause) { - $default_clause = $this->getDefaultClause($xmldb_field); - if ($default_clause) { - $sql = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' SET' . $default_clause; /// Add default clause - $results[] = $sql; - } - - /// Update default value (if exists) to all the records - if ($defaultvalue !== null) { - if (!is_numeric($defaultvalue)) { - $defaultvalue = "'".$this->addslashes($defaultvalue)."'"; - } - $sql = 'UPDATE ' . $tablename . ' SET ' . $fieldname . '=' . $defaultvalue; - $results[] = $sql; - } - } - - /// Add not null (only if no skip_notnull) - if (!$skip_notnull_clause) { - if ($xmldb_field->getNotnull()) { - $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' SET NOT NULL'; /// Add not null - } - } - - return $results; - } - /** * Given one xmldb_table and one xmldb_field, return the SQL statements needded to alter the field in the table * PostgreSQL has some severe limits: @@ -303,59 +253,43 @@ class postgres_sql_generator extends sql_generator { $tablename = $this->getTableName($xmldb_table); $fieldname = $this->getEncQuoted($xmldb_field->getName()); - /// TODO: Some combinations like - /// TODO: integer->integer - /// TODO: integer->text - /// TODO: number->text - /// TODO: text->text - /// TODO: do not require the use of temp columns, because PG 8.0 supports them automatically - /// TODO: with a simple "alter table zzz alter column yyy type new specs" - /// TODO: Must be implemented that way. Eloy 09/2007 - - /// If the type or the precision or the decimals have changed, then we need to: - /// - create one temp column with the new specs - /// - fill the new column with the values from the old one (casting if needed) - /// - drop the old column - /// - rename the temp column to the original name - if ($typechanged || $precisionchanged || $decimalchanged) { - $tempcolname = $xmldb_field->getName() . '_alter_column_tmp'; - $xmldb_field->setName($tempcolname); - /// Create the temporal column - /// Prevent temp field to have both NULL/NOT NULL and DEFAULT constraints - $results = array_merge($results, $this->getAddFieldSQL($xmldb_table, $xmldb_field, NULL, true, true)); - /// Detect some basic casting options - if ((substr($oldmetatype, 0, 1) == 'C' && $xmldb_field->getType() == XMLDB_TYPE_NUMBER) || - (substr($oldmetatype, 0, 1) == 'C' && $xmldb_field->getType() == XMLDB_TYPE_FLOAT)) { - $copyorigin = 'CAST(CAST('.$fieldname.' AS TEXT) AS REAL)'; //From char to number or float - } else if ((substr($oldmetatype, 0, 1) == 'C' && $xmldb_field->getType() == XMLDB_TYPE_INTEGER)) { - $copyorigin = 'CAST(CAST('.$fieldname.' AS TEXT) AS INTEGER)'; //From char to integer - } else { - $copyorigin = $fieldname; //Direct copy between columns + /// Decide if we have changed the column specs (type/precision/decimals) + $specschanged = $typechanged || $precisionchanged || $decimalchanged; + + /// if specs have changed, need to alter column + if ($specschanged) { + /// Always drop any exiting default before alter column (some type changes can cause casting error in default for column) + if ($olddefault !== null) { + $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' DROP DEFAULT'; /// Drop default clause + } + $alterstmt = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $this->getEncQuoted($xmldb_field->getName()) . + ' TYPE' . $this->getFieldSQL($xmldb_field, null, true, true, null, false); + /// Some castings must be performed explicity (mainly from text|char to numeric|integer) + if (($oldmetatype == 'C' || $oldmetatype == 'X') && + ($xmldb_field->getType() == XMLDB_TYPE_NUMBER || $xmldb_field->getType() == XMLDB_TYPE_FLOAT)) { + $alterstmt .= ' USING CAST('.$fieldname.' AS NUMERIC)'; // from char or text to number or float + } else if (($oldmetatype == 'C' || $oldmetatype == 'X') && + $xmldb_field->getType() == XMLDB_TYPE_INTEGER) { + $alterstmt .= ' USING CAST(CAST('.$fieldname.' AS NUMERIC) AS INTEGER)'; // From char to integer } - /// Copy contents from original col to the temporal one - $results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = ' . $copyorigin; - /// Drop the old column - $xmldb_field->setName($fieldname); //Set back the original field name - $results = array_merge($results, $this->getDropFieldSQL($xmldb_table, $xmldb_field)); - /// Rename the temp column to the original one - $results[] = 'ALTER TABLE ' . $tablename . ' RENAME COLUMN ' . $tempcolname . ' TO ' . $fieldname; - /// Mark we have performed one change based in temp fields - $from_temp_fields = true; + $results[] = $alterstmt; } - /// If the default has changed or we have used one temp field - if ($defaultchanged || $from_temp_fields) { + + /// If the default has changed or we have performed one change in specs + if ($defaultchanged || $specschanged) { $default_clause = $this->getDefaultClause($xmldb_field); if ($default_clause) { $sql = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' SET' . $default_clause; /// Add default clause $results[] = $sql; } else { - if (!$from_temp_fields) { /// Only drop default if we haven't used the temp field, i.e. old column + if (!$specschanged) { /// Only drop default if we haven't performed one specs change $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' DROP DEFAULT'; /// Drop default clause } } } - /// If the not null has changed or we have used one temp field - if ($notnullchanged || $from_temp_fields) { + + /// If the not null has changed + if ($notnullchanged) { if ($xmldb_field->getNotnull()) { $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' SET NOT NULL'; } else { diff --git a/lib/ddl/sql_generator.php b/lib/ddl/sql_generator.php index 9896f78144..44e57df6a8 100644 --- a/lib/ddl/sql_generator.php +++ b/lib/ddl/sql_generator.php @@ -401,7 +401,7 @@ abstract class sql_generator { /** * Given one correct xmldb_field, returns the complete SQL line to create it */ - public function getFieldSQL($xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL, $specify_nulls_clause = NULL) { + public function getFieldSQL($xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL, $specify_nulls_clause = NULL, $specify_field_name = true) { $skip_type_clause = is_null($skip_type_clause) ? $this->alter_column_skip_type : $skip_type_clause; $skip_default_clause = is_null($skip_default_clause) ? $this->alter_column_skip_default : $skip_default_clause; @@ -421,8 +421,11 @@ abstract class sql_generator { } } + $field = ''; // Let's accumulate the whole expression based on params and settings /// The name - $field = $this->getEncQuoted($xmldb_field->getName()); + if ($specify_field_name) { + $field .= $this->getEncQuoted($xmldb_field->getName()); + } /// The type and length only if we don't want to skip it if (!$skip_type_clause) { /// The type and length -- 2.39.5