I have a hard problem to solve and I don't know how to do it.
I want to archive some entities and then delete them from initial tables. Problem is these entities are linked together. I have a code which is close to working but I think it's not a clean way to do it. It's composed of SQL queries which copy rows with their IDs to new tables. Another problem is that I don't need some fields to archive so archive entities are not exactly the same as initial entities. I'm using raw SQL query and not DQL because of the size of my tables.
I want to archive these entities : Colle, ColleQC, QC, PasserColle, Reponse, ReponseQC, StatistiqueColle, StatistiqueQuestion, RepartitionColle, RepartitionQuestion, Tuteur TO BanqueColle, BanqueColleQC, BanqueQC, BanquePasserColle, BanqueReponse, BanqueReponseQC, BanqueStatistiqueColle, BanqueStatistiqueQuestion, BanqueRepartitionColle, BanqueRepartitionQuestion, AncienAdherent.
I'll use these archive for another part of my app.
Sample of table structure :
(Tuteur and AncienAdherent extend User)
Here's a part of the code I made to archive but I don't think it's a clean way to do it :
public function archiveTuteurs() {
$db = $this->em->getConnection();
$query = "INSERT INTO ancien_adherent (id)
SELECT u.id
FROM user u
WHERE discr = 'tuteur'";
$stmt = $db->prepare($query);
$stmt->execute();
$query2 = "UPDATE user
SET user.discr = 'ancien'
WHERE discr = 'tuteur'";
$stmt = $db->prepare($query2);
$stmt->execute();
return true;
}
public function archiveColles() {
$db = $this->em->getConnection();
$query = "INSERT INTO banque_colle (id, typeColle, nom, temps_epreuve, matiere_id, dateCreation, ordre, discr)
SELECT colle.id, colle.typeColle, colle.nom, colle.temps_epreuve, colle.matiere_id, colle.dateCreation, colle.ordre, colle.discr
FROM colle";
$stmt = $db->prepare($query);
$stmt->execute();
$query2 = "INSERT INTO banque_colle_qc (id)
SELECT colle_qc.id
FROM colle_qc";
$stmt = $db->prepare($query2);
$stmt->execute();
return true;
}
public function archiveQC() {
$db = $this->em->getConnection();
$query = "INSERT INTO banque_qc (id, titre, id_colle, ordre, qcPere, enonce, donnees, item1, item2, item3, item4,
item5, corrige_item1, corrige_item2, corrige_item3, corrige_item4, corrige_item5, item1_vrai,
item2_vrai, item3_vrai, item4_vrai, item5_vrai, item1_annule, item2_annule, item3_annule,
item4_annule, item5_annule, multiple_choices, inclu)
SELECT qc.id, qc.titre, qc.id_colle, qc.ordre, qc.qcPere, qc.enonce, qc.donnees, qc.item1, qc.item2,
qc.item3, qc.item4, qc.item5, qc.corrige_item1, qc.corrige_item2, qc.corrige_item3, qc.corrige_item4,
qc.corrige_item5, qc.item1_vrai, qc.item2_vrai, qc.item3_vrai, qc.item4_vrai, qc.item5_vrai,
qc.item1_annule, qc.item2_annule, qc.item3_annule, qc.item4_annule, qc.item5_annule,
qc.multiple_choices, qc.inclu
FROM qc
ORDER BY qc.qcPere ASC";
$stmt = $db->prepare($query);
$stmt->execute();
return true;
}
public function archivePassages() {
$db = $this->em->getConnection();
$query = "INSERT INTO banque_passer_colle (colle_id, dateDebut, note)
SELECT passer_colle.colle_id, passer_colle.dateDebut, passer_colle.note
FROM passer_colle";
$stmt = $db->prepare($query);
$stmt->execute();
return true;
}
public function archiveReponses() {
$db = $this->em->getConnection();
$query = "INSERT INTO banque_reponse (id, discr)
SELECT reponse.id, reponse.discr
FROM reponse
WHERE discr='reponseQC'";
$stmt = $db->prepare($query);
$stmt->execute();
$query2 = "INSERT INTO banque_reponse_qc (id, question, A, B, C, D, E, note)
SELECT reponse_qc.id, reponse_qc.question, reponse_qc.A, reponse_qc.B, reponse_qc.C, reponse_qc.D,
reponse_qc.E, reponse_qc.note
FROM reponse_qc";
$stmt = $db->prepare($query2);
$stmt->execute();
return true;
}
public function archiveStats() {
$db = $this->em->getConnection();
$query = "INSERT INTO banque_statistiquecolle (id, colle_id, effectif, moyenne, mediane, note100, major, minor)
SELECT sc.id, sc.colle_id, sc.effectif, sc.moyenne, sc.mediane, sc.note100, sc.major, sc.minor
FROM statistiquecolle_groupe scg
LEFT JOIN statistiquecolle sc ON sc.id = scg.statistiquecolle_id
WHERE scg.groupe_id = 1
AND sc.id NOT IN (SELECT sc1.id
FROM statistiquecolle_groupe scg1
LEFT JOIN statistiquecolle sc1 ON sc1.id = scg1.statistiquecolle_id
WHERE scg1.groupe_id != 1)";
$stmt = $db->prepare($query);
$stmt->execute();
$query2 = "INSERT INTO banque_statistiquequestion (id, question_id, moyenne, nbReponseTot, nbReponseA, nbReponseB,
nbReponseC, nbReponseD, nbReponseE)
SELECT sq.id, sq.question_id, sq.moyenne, sq.nbReponseTot, sq.nbReponseA, sq.nbReponseB, sq.nbReponseC,
sq.nbReponseD, sq.nbReponseE
FROM statistiquequestion_groupe sqg
LEFT JOIN statistiquequestion sq ON sq.id = sqg.statistiquequestion_id
WHERE sqg.groupe_id = 1
AND sq.id NOT IN (SELECT sq1.id
FROM statistiquequestion_groupe sqg1
LEFT JOIN statistiquequestion sq1 ON sq1.id = sqg1.statistiquequestion_id
WHERE sqg1.groupe_id != 1)";
$stmt = $db->prepare($query2);
$stmt->execute();
$query3 = "INSERT INTO banque_repartitioncolle (id, statColle_id, note, nombre, percentOfEffectif)
SELECT rc.id, rc.statColle_id, rc.note, rc.nombre, rc.percentOfEffectif
FROM repartitioncolle rc
WHERE rc.statColle_id IN (SELECT bsc.id
FROM banque_statistiquecolle bsc)";
$stmt = $db->prepare($query3);
$stmt->execute();
$query4 = "INSERT INTO banque_repartitionquestion (id, statQuestion_id, note, nombre, percentOfEffectif)
SELECT rq.id, rq.statQuestion_id, rq.note, rq.nombre, rq.percentOfEffectif
FROM repartitionquestion rq
WHERE rq.statQuestion_id IN (SELECT bsq.id
FROM banque_statistiquequestion bsq)";
$stmt = $db->prepare($query4);
$stmt->execute();
return true;
}