/** * Given one XMLDBTable and one XMLDBField, return the SQL statements needded 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 */ function getAlterFieldSQL($xmldb_table, $xmldb_field) { global $db; $results = array(); /// To store all the needed SQL commands /// Get the quoted name of the table and field $tablename = $this->getTableName($xmldb_table); $fieldname = $this->getEncQuoted($xmldb_field->getName()); /// Take a look to field metadata $meta = array_change_key_case($db->MetaColumns($tablename)); $metac = $meta[$fieldname]; $oldtype = strtolower($metac->type); $oldmetatype = column_type($xmldb_table->getName(), $fieldname); $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 = get_record_sql("SELECT cname, precision\n FROM col\n WHERE tname = '{$uppertablename}'\n AND cname = '{$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 && substr($oldmetatype, 0, 1) == 'I' || $xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N' || $xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F' || $xmldb_field->getType() == XMLDB_TYPE_CHAR && substr($oldmetatype, 0, 1) == 'C' || $xmldb_field->getType() == XMLDB_TYPE_TEXT && substr($oldmetatype, 0, 1) == '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' && ($precisionchanged || $decimalchanged)) { $tempcolname = $xmldb_field->getName() . '_alter_column_tmp'; /// Prevent temp field to have both NULL/NOT NULL and DEFAULT constraints $this->alter_column_skip_notnull = true; $this->alter_column_skip_default = true; $xmldb_field->setName($tempcolname); /// Create the temporal column $results = array_merge($results, $this->getAddFieldSQL($xmldb_table, $xmldb_field)); /// Copy contents from original col to the temporal one $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 $this->alter_column_skip_notnull = false; $this->alter_column_skip_default = false; /// Dissable the type section because we have done it with the temp field $this->alter_column_skip_type = 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) { $this->alter_column_skip_type = true; } /// If NULL/NOT NULL hasn't changed /// prevent null clause to be specified if (!$notnullchanged) { $this->alter_column_skip_notnull = 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()) { $this->alter_column_skip_notnull = false; } } /// If default hasn't changed /// prevent default clause to be specified if (!$defaultchanged) { $this->alter_column_skip_default = 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))) { $this->alter_column_skip_default = false; } } /// If arriving here, something is not being skiped (type, notnull, default), calculate the standar AlterFieldSQL if (!$this->alter_column_skip_type || !$this->alter_column_skip_notnull || !$this->alter_column_skip_default) { $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field)); return $results; } /// Finally return results return $results; }
/** * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to alter the field in the table */ function getAlterFieldSQL($xmldb_table, $xmldb_field) { global $db; $results = array(); /// To store all the needed SQL commands /// Get the quoted name of the table and field $tablename = $this->getTableName($xmldb_table); $fieldname = $this->getEncQuoted($xmldb_field->getName()); /// Take a look to field metadata $meta = array_change_key_case($db->MetaColumns($tablename)); $metac = $meta[$fieldname]; $oldtype = strtolower($metac->type); $oldmetatype = column_type($xmldb_table->getName(), $fieldname); $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 && substr($oldmetatype, 0, 1) == 'I' || $xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N' || $xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F' || $xmldb_field->getType() == XMLDB_TYPE_CHAR && substr($oldmetatype, 0, 1) == 'C' || $xmldb_field->getType() == XMLDB_TYPE_TEXT && substr($oldmetatype, 0, 1) == '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 $this->alter_column_skip_default = true; $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field)); // 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; }
/** * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to alter the field in the table */ function getAlterFieldSQL($xmldb_table, $xmldb_field) { global $db; $results = array(); /// To store all the needed SQL commands /// Get the quoted name of the table and field $tablename = $this->getTableName($xmldb_table); $fieldname = $this->getEncQuoted($xmldb_field->getName()); /// Take a look to field metadata $meta = array_change_key_case($db->MetaColumns($tablename)); $metac = $meta[$fieldname]; $oldtype = strtolower($metac->type); $oldmetatype = column_type($xmldb_table->getName(), $fieldname); $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 && substr($oldmetatype, 0, 1) == 'I' || $xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N' || $xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F' || $xmldb_field->getType() == XMLDB_TYPE_CHAR && substr($oldmetatype, 0, 1) == 'C' || $xmldb_field->getType() == XMLDB_TYPE_TEXT && substr($oldmetatype, 0, 1) == 'X' || $xmldb_field->getType() == XMLDB_TYPE_BINARY && $oldmetatype == 'B') { $typechanged = false; } /// If the new (and old) field specs are for integer, let's be a bit more specific diferentiating /// types of integers. Else, some combinations can cause things like MDL-21868 if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER && substr($oldmetatype, 0, 1) == '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 (adodb metatype is buggy!) $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); } /// Just prevent default clauses in this type of sentences for mssql and launch the parent one $this->alter_column_skip_default = true; $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field)); // 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; }