/**
* Given one XMLDBTable and one XMLDBField, 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
*/
function getAddFieldSQL($xmldb_table, $xmldb_field)
{
$results = array();
$tablename = $this->getTableName($xmldb_table);
$fieldname = $this->getEncQuoted($xmldb_field->getName());
$defaultvalue = null;
/// Save old flags
$old_skip_default = $this->alter_column_skip_default;
$old_skip_notnull = $this->alter_column_skip_notnull;
/// Prevent default clause and launch parent getAddField()
$this->alter_column_skip_default = true;
$this->alter_column_skip_notnull = true;
$results = parent::getAddFieldSQL($xmldb_table, $xmldb_field);
/// Re-set old flags
$this->alter_column_skip_default = $old_skip_default;
$this->alter_column_skip_notnull = $old_skip_notnull;
/// Add default (only if not skip_default)
if (!$this->alter_column_skip_default) {
if ($defaultclause = $this->getDefaultClause($xmldb_field)) {
$defaultvalue = $this->getDefaultValue($xmldb_field);
$results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' SET' . $defaultclause;
/// Add default clause
}
/// Update default value (if exists) to all the records
if ($defaultvalue !== null) {
$results[] = 'UPDATE ' . $tablename . ' SET ' . $fieldname . '=' . $defaultvalue;
}
}
/// Add not null (only if no skip_notnull)
if (!$this->alter_column_skip_notnull) {
if ($xmldb_field->getNotnull()) {
$results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' SET NOT NULL';
/// Add not null
}
}
return $results;
}
/**
* 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;
}
