/** * Give one xmldb_field, returns the correct "default value" for the current configuration * * @param xmldb_field $xmldb_field The field. * @return The default value of the field. */ public function getDefaultValue($xmldb_field) { $default = null; if ($xmldb_field->getDefault() !== NULL) { if ($xmldb_field->getType() == XMLDB_TYPE_CHAR || $xmldb_field->getType() == XMLDB_TYPE_TEXT) { if ($xmldb_field->getDefault() === '') { // If passing empty default, use the $default_for_char one instead $default = "'" . $this->default_for_char . "'"; } else { $default = "'" . $this->addslashes($xmldb_field->getDefault()) . "'"; } } else { $default = $xmldb_field->getDefault(); } } else { /// We force default '' for not null char columns without proper default /// some day this should be out! if ($this->default_for_char !== NULL && $xmldb_field->getType() == XMLDB_TYPE_CHAR && $xmldb_field->getNotNull()) { $default = "'" . $this->default_for_char . "'"; } else { /// If the DB requires to explicity define some clause to drop one default, do it here /// never applying defaults to TEXT and BINARY fields if ($this->drop_default_value_required && $xmldb_field->getType() != XMLDB_TYPE_TEXT && $xmldb_field->getType() != XMLDB_TYPE_BINARY && !$xmldb_field->getNotNull()) { $default = $this->drop_default_value; } } } return $default; }
/** * Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table. * * @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 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 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; }