/** * Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table. * * PostgreSQL has some severe limits: * - Any change of type or precision requires a new temporary column to be created, values to * be transfered potentially casting them, to apply defaults if the column is not null and * finally, to rename it * - Changes in null/not null require the SET/DROP NOT NULL clause * - Changes in default require the SET/DROP DEFAULT clause * * @param xmldb_table $xmldb_table The table related to $xmldb_field. * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from. * @param string $skip_type_clause The type clause on alter columns, NULL by default. * @param string $skip_default_clause The default clause on alter columns, NULL by default. * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default. * @return string The field altering SQL statement. */ public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) { $results = array(); // To store all the needed SQL commands // Get the normal names of the table and field $tablename = $xmldb_table->getName(); $fieldname = $xmldb_field->getName(); // Take a look to field metadata $meta = $this->mdb->get_columns($tablename); $metac = $meta[$xmldb_field->getName()]; $oldmetatype = $metac->meta_type; $oldlength = $metac->max_length; $olddecimals = empty($metac->scale) ? null : $metac->scale; $oldnotnull = empty($metac->not_null) ? false : $metac->not_null; $olddefault = empty($metac->has_default) ? null : $metac->default_value; $typechanged = true; //By default, assume that the column type has changed $precisionchanged = true; //By default, assume that the column precision has changed $decimalchanged = true; //By default, assume that the column decimal has changed $defaultchanged = true; //By default, assume that the column default has changed $notnullchanged = true; //By default, assume that the column notnull has changed // Detect if we are changing the type of the column if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I' || $xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N' || $xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F' || $xmldb_field->getType() == XMLDB_TYPE_CHAR && $oldmetatype == 'C' || $xmldb_field->getType() == XMLDB_TYPE_TEXT && $oldmetatype == 'X' || $xmldb_field->getType() == XMLDB_TYPE_BINARY && $oldmetatype == 'B') { $typechanged = false; } // Detect if we are changing the precision if ($xmldb_field->getType() == XMLDB_TYPE_TEXT || $xmldb_field->getType() == XMLDB_TYPE_BINARY || $oldlength == -1 || $xmldb_field->getLength() == $oldlength) { $precisionchanged = false; } // Detect if we are changing the decimals if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER || $xmldb_field->getType() == XMLDB_TYPE_CHAR || $xmldb_field->getType() == XMLDB_TYPE_TEXT || $xmldb_field->getType() == XMLDB_TYPE_BINARY || !$xmldb_field->getDecimals() || !$olddecimals || $xmldb_field->getDecimals() == $olddecimals) { $decimalchanged = false; } // Detect if we are changing the default if ($xmldb_field->getDefault() === null && $olddefault === null || $xmldb_field->getDefault() === $olddefault) { $defaultchanged = false; } // Detect if we are changing the nullability if ($xmldb_field->getNotnull() === $oldnotnull) { $notnullchanged = false; } // Get the quoted name of the table and field $tablename = $this->getTableName($xmldb_table); $fieldname = $this->getEncQuoted($xmldb_field->getName()); // 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_table, $xmldb_field, null, true, true, null, false); // Some castings must be performed explicitly (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 } } $results[] = $alterstmt; } // 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 (!$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 if ($notnullchanged) { if ($xmldb_field->getNotnull()) { $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' SET NOT NULL'; } else { $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' DROP NOT NULL'; } } // Return the results return $results; }
/** * Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table. * * Oracle has some severe limits: * - clob and blob fields doesn't allow type to be specified * - error is dropped if the null/not null clause is specified and hasn't changed * - changes in precision/decimals of numeric fields drop an ORA-1440 error * * @param xmldb_table $xmldb_table The table related to $xmldb_field. * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from. * @param string $skip_type_clause The type clause on alter columns, NULL by default. * @param string $skip_default_clause The default clause on alter columns, NULL by default. * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default. * @return string The field altering SQL statement. */ public function getAlterFieldSQL($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(); // To store all the needed SQL commands // Get the quoted name of the table and field $tablename = $this->getTableName($xmldb_table); $fieldname = $xmldb_field->getName(); // Take a look to field metadata $meta = $this->mdb->get_columns($xmldb_table->getName()); $metac = $meta[$fieldname]; $oldmetatype = $metac->meta_type; $oldlength = $metac->max_length; // To calculate the oldlength if the field is numeric, we need to perform one extra query // because ADOdb has one bug here. http://phplens.com/lens/lensforum/msgs.php?id=15883 if ($oldmetatype == 'N') { $uppertablename = strtoupper($tablename); $upperfieldname = strtoupper($fieldname); if ($col = $this->mdb->get_record_sql("SELECT cname, precision\n FROM col\n WHERE tname = ? AND cname = ?", array($uppertablename, $upperfieldname))) { $oldlength = $col->precision; } } $olddecimals = empty($metac->scale) ? null : $metac->scale; $oldnotnull = empty($metac->not_null) ? false : $metac->not_null; $olddefault = empty($metac->default_value) || strtoupper($metac->default_value) == 'NULL' ? null : $metac->default_value; $typechanged = true; //By default, assume that the column type has changed $precisionchanged = true; //By default, assume that the column precision has changed $decimalchanged = true; //By default, assume that the column decimal has changed $defaultchanged = true; //By default, assume that the column default has changed $notnullchanged = true; //By default, assume that the column notnull has changed $from_temp_fields = false; //By default don't assume we are going to use temporal fields // Detect if we are changing the type of the column if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I' || $xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N' || $xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F' || $xmldb_field->getType() == XMLDB_TYPE_CHAR && $oldmetatype == 'C' || $xmldb_field->getType() == XMLDB_TYPE_TEXT && $oldmetatype == 'X' || $xmldb_field->getType() == XMLDB_TYPE_BINARY && $oldmetatype == 'B') { $typechanged = false; } // Detect if precision has changed if ($xmldb_field->getType() == XMLDB_TYPE_TEXT || $xmldb_field->getType() == XMLDB_TYPE_BINARY || $oldlength == -1 || $xmldb_field->getLength() == $oldlength) { $precisionchanged = false; } // Detect if decimal has changed if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER || $xmldb_field->getType() == XMLDB_TYPE_CHAR || $xmldb_field->getType() == XMLDB_TYPE_TEXT || $xmldb_field->getType() == XMLDB_TYPE_BINARY || !$xmldb_field->getDecimals() || !$olddecimals || $xmldb_field->getDecimals() == $olddecimals) { $decimalchanged = false; } // Detect if we are changing the default if ($xmldb_field->getDefault() === null && $olddefault === null || $xmldb_field->getDefault() === $olddefault || "'" . $xmldb_field->getDefault() . "'" === $olddefault) { //Equality with quotes because ADOdb returns the default with quotes $defaultchanged = false; } // Detect if we are changing the nullability if ($xmldb_field->getNotnull() === $oldnotnull) { $notnullchanged = false; } // If type has changed or precision or decimal has changed and we are in one numeric field // - create one temp column with the new specs // - fill the new column with the values from the old one // - drop the old column // - rename the temp column to the original name if ($typechanged || ($oldmetatype == 'N' || $oldmetatype == 'I') && ($precisionchanged || $decimalchanged)) { $tempcolname = $xmldb_field->getName() . '___tmp'; // Short tmp name, surely not conflicting ever if (strlen($tempcolname) > 30) { // Safeguard we don't excess the 30cc limit $tempcolname = 'ongoing_alter_column_tmp'; } // Prevent temp field to have both NULL/NOT NULL and DEFAULT constraints $skip_notnull_clause = true; $skip_default_clause = true; $xmldb_field->setName($tempcolname); // Drop the temp column, in case it exists (due to one previous failure in conversion) // really ugly but we cannot enclose DDL into transaction :-( if (isset($meta[$tempcolname])) { $results = array_merge($results, $this->getDropFieldSQL($xmldb_table, $xmldb_field)); } // Create the temporal column $results = array_merge($results, $this->getAddFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, $skip_type_clause, $skip_notnull_clause)); // Copy contents from original col to the temporal one // From TEXT to integer/number we need explicit conversion if ($oldmetatype == 'X' && $xmldb_field->GetType() == XMLDB_TYPE_INTEGER) { $results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = CAST(' . $this->mdb->sql_compare_text($fieldname) . ' AS INT)'; } else { if ($oldmetatype == 'X' && $xmldb_field->GetType() == XMLDB_TYPE_NUMBER) { $results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = CAST(' . $this->mdb->sql_compare_text($fieldname) . ' AS NUMBER)'; // Normal cases, implicit conversion } else { $results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = ' . $fieldname; } } // 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; // Re-enable the notnull and default sections so the general AlterFieldSQL can use it $skip_notnull_clause = false; $skip_default_clause = false; // Disable the type section because we have done it with the temp field $skip_type_clause = true; // If new field is nullable, nullability hasn't changed if (!$xmldb_field->getNotnull()) { $notnullchanged = false; } // If new field hasn't default, default hasn't changed if ($xmldb_field->getDefault() === null) { $defaultchanged = false; } } // If type and precision and decimals hasn't changed, prevent the type clause if (!$typechanged && !$precisionchanged && !$decimalchanged) { $skip_type_clause = true; } // If NULL/NOT NULL hasn't changed // prevent null clause to be specified if (!$notnullchanged) { $skip_notnull_clause = true; // Initially, prevent the notnull clause // But, if we have used the temp field and the new field is not null, then enforce the not null clause if ($from_temp_fields && $xmldb_field->getNotnull()) { $skip_notnull_clause = false; } } // If default hasn't changed // prevent default clause to be specified if (!$defaultchanged) { $skip_default_clause = true; // Initially, prevent the default clause // But, if we have used the temp field and the new field has default clause, then enforce the default clause if ($from_temp_fields) { $default_clause = $this->getDefaultClause($xmldb_field); if ($default_clause) { $skip_notnull_clause = false; } } } // If arriving here, something is not being skipped (type, notnull, default), calculate the standard AlterFieldSQL if (!$skip_type_clause || !$skip_notnull_clause || !$skip_default_clause) { $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, $skip_default_clause, $skip_notnull_clause)); return $results; } // Finally return results return $results; }