/**
* Applies the differences defined in $dbSchemaDiff to the database referenced by $db.
*
* This method uses {@link convertDiffToDDL} to create SQL for the
* differences and then executes the returned SQL statements on the
* database handler $db.
*
* @todo check for failed transaction
*
* @param ezcDbHandler $db
* @param ezcDbSchemaDiff $dbSchemaDiff
*/
public function applyDiffToDb(ezcDbHandler $db, ezcDbSchemaDiff $dbSchemaDiff)
{
$db->beginTransaction();
foreach ($this->convertDiffToDDL($dbSchemaDiff) as $query) {
$db->exec($query);
}
$db->commit();
}
/**
* Performs changing field in Oracle table.
* (workaround for "ALTER TABLE table MODIFY field fieldType AUTO_INCREMENT " that not alowed in Oracle ).
*
* @param ezcDbHandler $db
* @param string $tableName
* @param string $autoIncrementFieldName
* @param string $autoIncrementFieldType
*/
private function addAutoIncrementField($db, $tableName, $autoIncrementFieldName, $autoIncrementFieldType)
{
// fetching field info from Oracle, getting column position of autoincrement field
// @apichange This code piece would become orphan, with the new
// implementation. We still need it to drop the old sequences.
// Remove until --END-- to not take care of them.
$resultArray = $db->query("SELECT a.column_name AS field, " . " a.column_id AS field_pos " . "FROM user_tab_columns a " . "WHERE a.table_name = '{$tableName}' AND a.column_name = '{$autoIncrementFieldName}'" . "ORDER BY a.column_id");
$resultArray->setFetchMode(PDO::FETCH_ASSOC);
if (count($resultArray) != 1) {
return;
}
$result = $resultArray->fetch();
$fieldPos = $result['field_pos'];
// emulation of autoincrement through adding sequence, trigger and constraint
$oldName = ezcDbSchemaOracleHelper::generateSuffixCompositeIdentName($tableName, $fieldPos, "seq");
$oldNameTrigger = ezcDbSchemaOracleHelper::generateSuffixCompositeIdentName($tableName, $fieldPos, "trg");
$sequence = $db->query("SELECT sequence_name FROM user_sequences WHERE sequence_name = '{$oldName}'")->fetchAll();
if (count($sequence) > 0) {
// assuming that if the seq exists, the trigger exists too
$db->query("DROP SEQUENCE \"{$oldName}\"");
$db->query("DROP TRIGGER \"{$oldNameTrigger}\"");
}
// --END--
// New sequence names, using field names
$newName = ezcDbSchemaOracleHelper::generateSuffixCompositeIdentName($tableName, $fieldPos, "seq");
$newNameTrigger = ezcDbSchemaOracleHelper::generateSuffixCompositeIdentName($tableName, $fieldPos, "seq");
// Emulation of autoincrement through adding sequence, trigger and constraint
$sequences = $db->query("SELECT sequence_name FROM user_sequences WHERE sequence_name = '{$newName}'")->fetchAll();
if (count($sequences) > 0) {
$db->query("DROP SEQUENCE \"{$newName}\"");
}
$db->exec("CREATE SEQUENCE \"{$newName}\" start with 1 increment by 1 nomaxvalue");
$db->exec("CREATE OR REPLACE TRIGGER \"{$newNameTrigger}\" " . "before insert on \"{$tableName}\" for each row " . "begin " . "select \"{$newName}\".nextval into :new.\"{$autoIncrementFieldName}\" from dual; " . "end;");
$constraintName = ezcDbSchemaOracleHelper::generateSuffixedIdentName(array($tableName), "pkey");
$constraint = $db->query("SELECT constraint_name FROM user_cons_columns WHERE constraint_name = '{$constraintName}'")->fetchAll();
if (count($constraint) > 0) {
$db->query("ALTER TABLE \"{$tableName}\" DROP CONSTRAINT \"{$constraintName}\"");
}
$db->exec("ALTER TABLE \"{$tableName}\" ADD CONSTRAINT \"{$constraintName}\" PRIMARY KEY ( \"{$autoIncrementFieldName}\" )");
$this->context['skip_primary'] = true;
}
/**
* Sets up opened connection according to options.
*/
private function setupConnection()
{
$requiredMode = $this->options->quoteIdentifier;
if ($requiredMode == ezcDbMssqlOptions::QUOTES_GUESS) {
$result = parent::query("SELECT sessionproperty('QUOTED_IDENTIFIER')");
$rows = $result->fetchAll();
$mode = (int) $rows[0][0];
if ($mode == 0) {
$this->identifierQuoteChars = array('start' => '[', 'end' => ']');
} else {
$this->identifierQuoteChars = array('start' => '"', 'end' => '"');
}
} else {
if ($requiredMode == ezcDbMssqlOptions::QUOTES_COMPLIANT) {
parent::exec('SET QUOTED_IDENTIFIER ON');
$this->identifierQuoteChars = array('start' => '"', 'end' => '"');
} else {
if ($requiredMode == ezcDbMssqlOptions::QUOTES_LEGACY) {
parent::exec('SET QUOTED_IDENTIFIER OFF');
$this->identifierQuoteChars = array('start' => '[', 'end' => ']');
}
}
}
}
/**
* Drop specified temporary table
* in a portable way.
*
* Developers should use this method instead of dropping temporary
* tables with the appropriate SQL queries
* to maintain inter-DBMS portability.
*
* @see createTemporaryTable()
*
* @param string $tableName Name of temporary table to drop.
* @return void
*/
public function dropTemporaryTable($tableName)
{
$this->db->exec("DROP TABLE {$tableName}");
}
/**
* Performs droping field from SQLite table using temporary table
* (workaround for "ALTER TABLE table DROP field" that not alowed in SQLite ).
*
* @param ezcDbHandler $db
* @param string $tableName
* @param string $dropFieldName
*/
private function dropField($db, $tableName, $dropFieldName)
{
$tmpTableName = $tableName . '_ezcbackup';
$resultArray = $db->query("PRAGMA TABLE_INFO( {$tableName} )");
$resultArray->setFetchMode(PDO::FETCH_NUM);
$fieldsDefinitions = array();
$fieldsList = array();
foreach ($resultArray as $row) {
$fieldSql = array();
$fieldSql[] = "'{$row[1]}'";
// name
if ($row[1] == $dropFieldName) {
continue;
// don't include droped fileld in temporary table
}
$fieldSql[] = $row[2];
// type
if ($row[3] == '99') {
$fieldSql[] = 'NOT NULL';
}
$fieldDefault = null;
if ($row[4] != '') {
$fieldSql[] = "DEFAULT '{$row[4]}'";
}
if ($row[5] == '1') {
$fieldSql[] = 'PRIMARY KEY AUTOINCREMENT';
}
// FIXME: unsigned needs to be implemented
$fieldUnsigned = false;
$fieldsDefinitions[] = join(' ', $fieldSql);
$fieldsList[] = $fieldSql[0];
}
$fields = join(', ', $fieldsDefinitions);
$tmpTableCreateSql = "CREATE TEMPORARY TABLE '{$tmpTableName}'( {$fields} );";
$newTableCreateSql = "CREATE TABLE '{$tableName}'( {$fields} )";
if (count($fieldsList) > 0) {
$db->exec($tmpTableCreateSql);
$db->exec("INSERT INTO '{$tmpTableName}' SELECT " . join(', ', $fieldsList) . " FROM '{$tableName}';");
$db->exec("DROP TABLE '{$tableName}';");
$db->exec($newTableCreateSql);
$db->exec("INSERT INTO '{$tableName}' SELECT " . join(', ', $fieldsList) . " FROM '{$tmpTableName}';");
$db->exec("DROP TABLE '{$tmpTableName}';");
} else {
throw new ezcDbSchemaDropAllColumnsException("Trying to delete all columns in table: {$tableName}");
}
}
/**
* Performs adding field in PostgreSQL table.
* ( workaround for "ALTER TABLE table ADD field fieldDefinition NOT NULL"
* that not alowed in PostgreSQL 7.x but works in PostgreSQL 8.x ).
*
* @param ezcDbHandler $db
* @param string $tableName
* @param string $fieldName
* @param string $fieldType
*
*/
private function addField(ezcDbHandler $db, $tableName, $fieldName, $fieldType)
{
$db->exec("ALTER TABLE \"{$tableName}\" ADD \"{$fieldName}\" {$fieldType}");
$db->exec("ALTER TABLE \"{$tableName}\" ALTER \"{$fieldName}\" SET NOT NULL");
}
