/**
* 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
*/
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() . '_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;
/// Dissable 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;
}
/**
* Given one xmldb_table and one xmldb_field, return the SQL statements needed 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)
{
$results = array();
/// To store all the needed SQL commands
/// Get the quoted name 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[$fieldname];
$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 : strtok($metac->default_value, ':');
$typechanged = true;
//By default, assume that the column type has changed
$lengthchanged = true;
//By default, assume that the column length 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;
}
/// If the new field (and old) specs are for integer, let's be a bit more specific differentiating
/// types of integers. Else, some combinations can cause things like MDL-21868
if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I') {
if ($xmldb_field->getLength() > 9) {
// Convert our new lenghts to detailed meta types
$newmssqlinttype = 'I8';
} else {
if ($xmldb_field->getLength() > 4) {
$newmssqlinttype = 'I';
} else {
$newmssqlinttype = 'I2';
}
}
if ($metac->type == 'bigint') {
// Convert current DB type to detailed meta type (our metatype is not enough!)
$oldmssqlinttype = 'I8';
} else {
if ($metac->type == 'smallint') {
$oldmssqlinttype = 'I2';
} else {
$oldmssqlinttype = 'I';
}
}
if ($newmssqlinttype != $oldmssqlinttype) {
// Compare new and old meta types
$typechanged = true;
// Change in meta type means change in type at all effects
}
}
/// Detect if we are changing the length of the column, not always necessary to drop defaults
/// if only the length changes, but it's safe to do it always
if ($xmldb_field->getLength() == $oldlength) {
$lengthchanged = false;
}
/// If type or length have changed drop the default if exists
if ($typechanged || $lengthchanged) {
$results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field);
}
/// Some changes of type require multiple alter statements, because mssql lacks direct implicit cast between such types
/// Here it is the matrix: http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx
/// Going to store such intermediate alters in array of objects, storing all the info needed
$multiple_alter_stmt = array();
$targettype = $xmldb_field->getType();
if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'I') {
// integer to text
$multiple_alter_stmt[0] = new stdClass();
// needs conversion to varchar
$multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
$multiple_alter_stmt[0]->length = 255;
} else {
if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'N') {
// decimal to text
$multiple_alter_stmt[0] = new stdClass();
// needs conversion to varchar
$multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
$multiple_alter_stmt[0]->length = 255;
} else {
if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'F') {
// float to text
$multiple_alter_stmt[0] = new stdClass();
// needs conversion to varchar
$multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
$multiple_alter_stmt[0]->length = 255;
} else {
if ($targettype == XMLDB_TYPE_INTEGER && $oldmetatype == 'X') {
// text to integer
$multiple_alter_stmt[0] = new stdClass();
// needs conversion to varchar
$multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
$multiple_alter_stmt[0]->length = 255;
$multiple_alter_stmt[1] = new stdClass();
// and also needs conversion to decimal
$multiple_alter_stmt[1]->type = XMLDB_TYPE_NUMBER;
// without decimal positions
$multiple_alter_stmt[1]->length = 10;
} else {
if ($targettype == XMLDB_TYPE_NUMBER && $oldmetatype == 'X') {
// text to decimal
$multiple_alter_stmt[0] = new stdClass();
// needs conversion to varchar
$multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
$multiple_alter_stmt[0]->length = 255;
} else {
if ($targettype == XMLDB_TYPE_FLOAT && $oldmetatype == 'X') {
// text to float
$multiple_alter_stmt[0] = new stdClass();
// needs conversion to varchar
$multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
$multiple_alter_stmt[0]->length = 255;
}
}
}
}
}
}
/// Just prevent default clauses in this type of sentences for mssql and launch the parent one
if (empty($multiple_alter_stmt)) {
// Direct implicit conversion allowed, launch it
$results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL));
} else {
// Direct implicit conversion forbidden, use the intermediate ones
$final_type = $xmldb_field->getType();
// Save final type and length
$final_length = $xmldb_field->getLength();
foreach ($multiple_alter_stmt as $alter) {
$xmldb_field->setType($alter->type);
// Put our intermediate type and length and alter to it
$xmldb_field->setLength($alter->length);
$results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL));
}
$xmldb_field->setType($final_type);
// Set the final type and length and alter to it
$xmldb_field->setLength($final_length);
$results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL));
}
/// Finally, process the default clause to add it back if necessary
if ($typechanged || $lengthchanged) {
$results = array_merge($results, $this->getCreateDefaultSQL($xmldb_table, $xmldb_field));
}
/// Return results
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)
{
$results = array();
/// To store all the needed SQL commands
/// Get the quoted name 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, false);
$metac = $meta[$fieldname];
$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 : strtok($metac->default_value, ':');
$typechanged = true;
//By default, assume that the column type has changed
$lengthchanged = true;
//By default, assume that the column length 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 length of the column, not always necessary to drop defaults
/// if only the length changes, but it's safe to do it always
if ($xmldb_field->getLength() == $oldlength) {
$lengthchanged = false;
}
/// If type or length have changed drop the default if exists
if ($typechanged || $lengthchanged) {
$results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field);
}
/// Just prevent default clauses in this type of sentences for mssql and launch the parent one
$results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL));
// Call parent
/// Finally, process the default clause to add it back if necessary
if ($typechanged || $lengthchanged) {
$results = array_merge($results, $this->getCreateDefaultSQL($xmldb_table, $xmldb_field));
}
/// Return results
return $results;
}
