/**
* exportConstraints
* exports the constraints of this table into database based on option definitions
*
* @throws Doctrine_Connection_Exception if something went wrong on db level
* @return void
*/
public function exportConstraints()
{
try {
$this->_conn->beginTransaction();
foreach ($this->_options['index'] as $index => $definition) {
$this->_conn->export->createIndex($this->_options['tableName'], $index, $definition);
}
$this->_conn->commit();
} catch (Doctrine_Connection_Exception $e) {
$this->_conn->rollback();
throw $e;
}
}
/**
* Handles error as specified by {@link self::$_onErrorBehavior}
*
* @param type $message The error message from the exception thrown.
*
* @see self::handlePopulationError()
* @return void
*/
protected static function handleError($message)
{
switch (self::$_onErrorBehavior) {
case 'continue':
break;
case 'stop':
self::$conn->commit();
throw new sfFileException($message);
break;
case 'rollback':
self::$conn->rollback();
throw new sfFileException($message);
break;
default:
throw new Exception('La opcion: ' . self::$_onErrorBehavior . ' no es una opcion válida para la clave on_error_behavior');
break;
}
}
private function proceedStep1_5($exists = false)
{
if ($exists == true) {
$this->displayResultStep("Récupération des noeuds fils restant à créer.");
// Requête SQL permettant de récupérer les noeuds fils à créer.
$reqSQL = "SELECT en.* FROM ei_node en WHERE type = 'EiDataSetTemplate' AND (SELECT COUNT(*) FROM ei_node WHERE root_id = en.id) = 0;";
$results = $this->connexion->execute($reqSQL);
$noeudsFils = $results->fetchAll();
$this->displayResultStep("Il y a " . count($noeudsFils) . " noeuds fils à créer.");
// Requête SQL permettant de récupérer les noeuds fils à créer.
$reqSQL = "SELECT * FROM " . self::$TABLE_TEMPLATES;
$results = $this->connexion->execute($reqSQL);
$templatesResults = $results->fetchAll();
$templates = array();
foreach ($templatesResults as $templateResult) {
$templates[$templateResult["ei_data_set_ref_id"]] = $templateResult["id"];
}
$this->displayResultStep("Création des noeuds fils.");
$requeteToInsert = "INSERT INTO ei_node (name, type, obj_id, project_id, project_ref, position, root_id, created_at, updated_at) " . "VALUES (#{NAME}, 'EiDataSet', #{OBJ_ID}, #{PROJECT_ID}, #{PROJECT_REF}, 1, #{ROOT_ID}, NOW(), NOW());";
$requeteToUpdate = "UPDATE ei_node SET obj_id = #{OBJ_ID} WHERE id = #{EI_NODE_ID};";
$requeteToUpdate .= "UPDATE ei_data_set SET ei_data_set_template_id = #{TEMPLATE_ID} WHERE id = #{DATA_SET_ID};";
$requeteGlobale = array();
foreach ($noeudsFils as $template) {
if (isset($templates[$template["obj_id"]])) {
// Remplacement NODE ID.
$tmpRequete = str_replace("#{EI_NODE_ID}", $template["id"], $requeteToUpdate);
// Remplacement OBJ ID.
$tmpRequete = str_replace("#{OBJ_ID}", $templates[$template["obj_id"]], $tmpRequete);
// Remplacement PROJECT ID.
$tmpRequete = str_replace("#{PROJECT_ID}", $template["project_id"], $tmpRequete);
// Remplacement PROJECT REF.
$tmpRequete = str_replace("#{PROJECT_REF}", $template["project_ref"], $tmpRequete);
// Remplacement TEMPLATE ID.
$tmpRequete = str_replace("#{TEMPLATE_ID}", $templates[$template["obj_id"]], $tmpRequete);
// Remplacement DATA SET ID.
$tmpRequete = str_replace("#{DATA_SET_ID}", $template["obj_id"], $tmpRequete);
// Ajout dans la requête globale.
$requeteGlobale[] = $tmpRequete;
// Remplacement NODE ID.
$tmpRequete = str_replace("#{NAME}", $this->connexion->quote($template["name"]), $requeteToInsert);
// Remplacement OBJ ID.
$tmpRequete = str_replace("#{OBJ_ID}", $template["obj_id"], $tmpRequete);
// Remplacement ROOT ID.
$tmpRequete = str_replace("#{ROOT_ID}", $template["id"], $tmpRequete);
// Remplacement PROJECT ID.
$tmpRequete = str_replace("#{PROJECT_ID}", $template["project_id"], $tmpRequete);
// Remplacement PROJECT REF.
$tmpRequete = str_replace("#{PROJECT_REF}", $template["project_ref"], $tmpRequete);
// Ajout dans la requête globale.
$requeteGlobale[] = $tmpRequete;
} else {
$this->displayResultStep("*** Anomalie : Le template n'existe pas pour le jeu de données N°" . $template["obj_id"] . " ***");
}
}
// Préparation de la requête.
$this->displayResultStep("Préparation de la requête...");
$requete = implode(" ", $requeteGlobale);
// Exécution de la requête.
$this->displayResultStep("Exécution de la requête...");
if (strlen($requete) > 5) {
$this->connexion->beginTransaction();
try {
while (count($requeteGlobale) > 0) {
$sousRequeteGlobale = array_slice($requeteGlobale, 0, 5);
$sousRequete = implode(" ", $sousRequeteGlobale);
$this->connexion->execute($sousRequete);
array_splice($requeteGlobale, 0, 5);
}
$this->connexion->commit();
} catch (Exception $exc) {
$this->connexion->rollback();
throw $exc;
}
}
// Fin.
$this->displayResultStep("Processus terminé avec succès.");
} else {
$this->displayResultStep("Création des noeuds fils omise.");
}
return false;
}
/**
* Méthode regardant pour toutes les tables les colonnes qui ont plusieurs index. On supprime alors la/les superflus.
* La priorité est donnée aux index définis par l'utilisateur.
*
* @param Doctrine_Connection $conn
*/
private function cleanPointlessIndexes(Doctrine_Connection $conn)
{
ini_set("memory_limit", "-1");
$sqlDeleteIndex = "ALTER TABLE :table_name DROP INDEX :index_name;";
$sqlRequests = array();
$conn->beginTransaction();
try {
$table = Doctrine_Core::getTable("EiCampaignExecutionGraph");
$indexesGraphId = $conn->execute("SHOW INDEX FROM " . $table->getTableName() . " WHERE Non_unique = 1 AND Column_name = 'graph_id';")->fetchAll();
$indexesVersionId = $conn->execute("SHOW INDEX FROM " . $table->getTableName() . " WHERE Non_unique = 1 AND Column_name = 'version_id';")->fetchAll();
if (count($indexesGraphId) == 1 && $indexesGraphId[0]["Key_name"] == "ei_campaign_execution_graph_graph_id_idx") {
$sqlRequests[] = "ALTER TABLE ei_campaign_execution_graph ADD INDEX graph_id_index_idx (graph_id);";
$sqlRequests[] = "ALTER TABLE ei_campaign_execution_graph DROP INDEX ei_campaign_execution_graph_graph_id_idx;";
}
if (count($indexesVersionId) == 1 && $indexesVersionId[0]["Key_name"] == "ei_campaign_execution_graph_version_id_idx") {
$sqlRequests[] = "ALTER TABLE ei_campaign_execution_graph ADD INDEX version_id_index_idx (version_id);";
$sqlRequests[] = "ALTER TABLE ei_campaign_execution_graph DROP INDEX ei_campaign_execution_graph_version_id_idx;";
}
// On récupère tous les index de la table.
$tableIndexes = $conn->execute("SHOW INDEX FROM " . $table->getTableName() . " WHERE Non_unique = 1;")->fetchAll();
$tableIndexes = $this->groupIndexes($tableIndexes);
$relations = $table->getRelations();
$tableOptions = $table->getOptions();
$userIndexes = $tableOptions["indexes"];
$userRelationsIndexes = array();
// Parcours la liste des relations et vérifie si ce dernier est indexé par un index utilisateur.
/** @var Doctrine_Relation_LocalKey $relation */
foreach ($relations as $relation) {
$indexed = false;
foreach ($userIndexes as $index) {
if (count($index["fields"]) == 1 && $index["fields"][0] == $relation->getLocalColumnName()) {
$indexed = true;
break;
}
}
$userRelationsIndexes[$relation->getLocalColumnName()] = $indexed;
}
/**
* Pour chaque index en base de données, je vérifie s'il est superflu ou non.
*/
foreach ($tableIndexes as $indexName => $index) {
$realIndex = substr($indexName, 0, -4);
$columnIndex = $index[0][self::INDEX_COLNAME_COLUMN];
// Si index utilisateur...
if (array_key_exists($realIndex, $userIndexes)) {
// Nothing to do.
} else {
// On vérifie si la colonne fait référence à une clé étrangère.
$related = false;
/** @var Doctrine_Relation_LocalKey $relation */
foreach ($relations as $relation) {
if ($relation->getLocalColumnName() == $columnIndex) {
$related = true;
break;
}
}
// Si lié à un FK et sans index utilisateur...on garde.
if ($related && !$userRelationsIndexes[$columnIndex]) {
$userRelationsIndexes[$columnIndex] = true;
} else {
$sqlRequests[] = str_replace(":table_name", $table->getTableName(), str_replace(":index_name", $indexName, $sqlDeleteIndex));
$this->log("[INFO] Suppression de l'index " . $indexName . " de la table " . $table->getTableName() . ".");
}
}
}
if (count($sqlRequests) > 0) {
$conn->execute(implode(" ", $sqlRequests));
}
$conn->commit();
$this->log("[INFO] Nettoyage des index superflus terminé.");
} catch (Exception $exc) {
$conn->rollback();
$this->log("[ERROR] " . $exc->getMessage());
}
}
