PHP Doctrine_Connection::execute方法代碼示例

 public static function rechargerUserProjet($usersprojets, Doctrine_Connection $conn = null)
 {
     $conn->execute("TRUNCATE TABLE ei_project_user");
     //On vide la table pour purger les utilisateurs
     $userprojet = $usersprojets->getElementsByTagName("user_projet");
     if ($userprojet->length > 0) {
         //s'il ya au moins une balise trouvé
         $stmt = $conn->prepare("INSERT INTO ei_project_user (project_id, user_id, project_ref,user_ref,role) " . "VALUES (:project_id, :user_id, :project_ref,:user_ref,:role) " . "ON DUPLICATE KEY UPDATE project_id=project_id,user_id=user_id,project_ref=project_ref,user_ref=user_ref,role=:role");
         foreach ($userprojet as $u) {
             $project_id = $u->getAttribute("project_id");
             $user_id = $u->getAttribute("user_id");
             $project_ref = $u->getAttribute("project_ref");
             $user_ref = $u->getAttribute("user_ref");
             $role = $u->getElementsByTagName("role")->item(0)->nodeValue;
             //recherche du projet en base
             if ($project_id != null && $project_ref != null) {
                 $q = Doctrine_Core::getTable('EiProjet')->findOneByProjectIdAndRefId($project_id, $project_ref);
                 if ($q && $q != null) {
                     //si le projet existe
                     $stmt->bindValue("project_id", $project_id);
                     $stmt->bindValue("user_id", $user_id);
                     $stmt->bindValue("project_ref", $project_ref);
                     $stmt->bindValue("user_ref", $user_ref);
                     $stmt->bindValue("role", $role);
                     $stmt->execute(array());
                 }
             }
         }
     }
 }

 private function proceedStep1_6($exists = false)
 {
     if ($exists == true) {
         $reqSQL = "SELECT * FROM ei_node WHERE type='EiDataSet';";
         $results = $this->connexion->execute($reqSQL);
         $noeudsFils = $results->fetchAll();
         $requeteToUpdate = "UPDATE ei_data_set SET ei_node_id = #{EI_NODE_ID} WHERE id = #{ID};";
         $requeteGlobale = array();
         foreach ($noeudsFils as $noeud) {
             // Remplacement NODE ID.
             $tmpRequete = str_replace("#{EI_NODE_ID}", $noeud["id"], $requeteToUpdate);
             // Remplacement OBJ ID.
             $tmpRequete = str_replace("#{ID}", $noeud["obj_id"], $tmpRequete);
             // Ajout dans la requête globale.
             $requeteGlobale[] = $tmpRequete;
         }
         // 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->execute($requete);
         }
         // Fin.
         $this->displayResultStep("Processus terminé avec succès.");
     } else {
         $this->displayResultStep("Mise à jour du node_id du jeu de données omise.");
     }
     return false;
 }

 /**
  * drops this view from the database
  *
  * @throws Doctrine_View_Exception
  * @return void
  */
 public function drop()
 {
     try {
         $this->_conn->execute(sprintf(self::DROP, $this->_name));
     } catch (Doctrine_Exception $e) {
         throw new Doctrine_View_Exception($e->__toString());
     }
 }

 /**
  * @param $id                       database row id
  * @throws Doctrine_Find_Exception
  */
 public final function getProxy($id = null)
 {
     if ($id !== null) {
         $identifierColumnNames = $this->getIdentifierColumnNames();
         $query = 'SELECT ' . implode(', ', (array) $identifierColumnNames) . ' FROM ' . $this->getTableName() . ' WHERE ' . implode(' = ? && ', (array) $identifierColumnNames) . ' = ?';
         $query = $this->applyInheritance($query);
         $params = array_merge(array($id), array_values($this->_options['inheritanceMap']));
         $this->_data = $this->_conn->execute($query, $params)->fetch(PDO::FETCH_ASSOC);
         if ($this->_data === false) {
             return false;
         }
     }
     return $this->getRecord();
 }

 public function _execute($params)
 {
     $params = $this->_conn->convertBooleans(array_merge($this->_params, $params));
     if (!$this->_view) {
         $query = $this->getQuery($params);
     } else {
         $query = $this->_view->getSelectSql();
     }
     $params = $this->convertEnums($params);
     if ($this->isLimitSubqueryUsed() && $this->_conn->getAttribute(Doctrine::ATTR_DRIVER_NAME) !== 'mysql') {
         $params = array_merge($params, $params);
     }
     if ($this->type !== self::SELECT) {
         return $this->_conn->exec($query, $params);
     }
     $stmt = $this->_conn->execute($query, $params);
     return $stmt;
 }

 /**
  * @param Doctrine_Connection $conn
  */
 private function transformDataSetTreeFromRecursiveToNested(Doctrine_Connection $conn)
 {
     ini_set("memory_limit", "-1");
     /** @var EiDataSetTable $tableDs */
     $tableDs = Doctrine_Core::getTable("EiDataSet");
     // Requête permettant de supprimer les lignes de JDD orphelines.
     $sqlRemoveOrphans = "DELETE FROM ei_data_line WHERE ei_data_line_parent_id IS NULL AND ei_data_set_structure_id IS NULL;";
     // Requêtes permettant de récupérer les arbres des JDD à traiter.
     $sqlGetDS = "SELECT id FROM ei_data_set WHERE id IN (SELECT ei_data_set_id FROM ei_data_line WHERE lft IS NULL OR rgt IS NULL GROUP BY ei_data_set_id HAVING COUNT(ei_data_set_id) > 0);";
     // Update level from structure.
     $sqlUpdateLevel = "UPDATE ei_data_line dl, ei_data_set_structure dss SET dl.level = dss.level WHERE dl.ei_data_set_structure_id = dss.id";
     // Update root_id from root elements.
     $updateRootId = "UPDATE ei_data_line SET root_id = id WHERE root_id IS NULL AND level = 0;";
     try {
         $this->log("[INFO] ---   Début MAJ des arbres de JDD   ---");
         $this->log("[INFO] Suppression des orphelins.");
         $conn->execute($sqlRemoveOrphans);
         $this->log("[INFO] Récupération de la liste des jeux de données à mettre à jour.");
         $dataSets = $conn->execute($sqlGetDS)->fetchAll();
         $this->log("[INFO] " . count($dataSets) . " jeu(x) de données à mettre à jour.");
         if (count($dataSets) > 0) {
             foreach ($dataSets as $dataSet) {
                 /** @var EiDataSet $ds */
                 $ds = $tableDs->find($dataSet["id"]);
                 $ds->updateDataLines($ds->generateOldXML());
             }
         }
         $this->log("[INFO] Jeux de données mis à jour.");
         $conn->execute($sqlUpdateLevel);
         $this->log("[INFO] Mise à jour des niveaux de chaque ligne relativement à la structure.");
         $conn->execute($updateRootId);
         $this->log("[INFO] Mise à jour du root_id pour chaque élément root.");
         $this->log("[INFO] ---   Fin MAJ des arbres de JDD   ---");
     } catch (Exception $e) {
         $this->log("[ERROR] " . $e->getMessage());
     }
 }

 /**
  * count
  *
  * @return integer
  */
 public function count()
 {
     $a = $this->_conn->execute('SELECT COUNT(1) FROM ' . $this->_options['tableName'])->fetch(Doctrine::FETCH_NUM);
     return current($a);
 }

 private function proceedStep6()
 {
     $sql = "ALTER TABLE ei_version_structure MODIFY name varchar(255) NOT NULL";
     $this->connexion->execute($sql);
     $this->displayResultStep("La colonne name de la table ei_version_structure a été fixée à not null.");
 }

 public function emptyTmpTable(Doctrine_Connection $conn = null)
 {
     //On vide la table temporaire avant toute opération
     $conn->execute("TRUNCATE TABLE script_ei_function_has_param");
 }

 /**
  * 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());
     }
 }