/** * Checks if certain query allowed. * * Perform testing if table exist for DROP TABLE query * to avoid stoping execution while try to drop not existent table. * * @param ezcDbHandler $db * @param string $query * * * @return boolean false if query should not be executed. */ public function isQueryAllowed(ezcDbHandler $db, $query) { if (substr($query, 0, 10) == 'DROP TABLE') { $tableName = trim(substr($query, strlen('DROP TABLE ')), '"'); $result = $db->query("SELECT count(*) FROM pg_tables WHERE tablename='{$tableName}'")->fetchAll(); if ($result[0]['count'] == 1) { return true; } else { return false; } } if (preg_match("/ALTER TABLE (.*) ALTER (.*) TYPE (.*)/", $query, $matches) || preg_match("/ALTER TABLE (.*) ADD (.*) NOT NULL/", $query, $matches)) { return false; } return true; }
/** * 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' => ']'); } } } }
/** * 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}"); } }