Nettoyage des bases de données tb_cel et tb_del

use tb_cel;
-- trouver les images telles que :
SELECT count(distinct ci.id_image) FROM cel_images ci
LEFT JOIN tb_del.del_observation do ON ci.ce_observation = do.id_observation
-- contient le mot-clé 'plantnet'
WHERE do.mots_cles_texte LIKE '%plantnet%'
-- a été postée par quelqu'un qui n'est pas inscrit à TB
AND do.ce_utilisateur = 0
-- n'a pas de proposition / commentaire
AND do.id_observation NOT IN (SELECT ce_observation FROM tb_del.del_commentaire)
-- n'a pas de vote, même sur la proposition initiale
-- (traité au dessus, cf. matérialisation de la proposition initiale)
-- dont aucune des images n'a :
--     de vote sur aucun protocole
AND ci.id_image NOT IN (SELECT ce_image FROM tb_del.del_image_vote)
--     de tag
AND ci.id_image NOT IN (SELECT ce_image FROM tb_del.del_image_tag)
;

use tb_cel;
-- trouver les obs telles que :
SELECT count(distinct co.id_observation) FROM cel_obs co
LEFT JOIN tb_del.del_image di ON di.ce_observation = co.id_observation
-- contient le mot-clé 'plantnet'
WHERE co.mots_cles_texte LIKE '%plantnet%'
AND co.transmission = 1
-- a été postée par quelqu'un qui n'est pas inscrit à TB
AND co.ce_utilisateur = 0
-- n'a pas de proposition / commentaire
AND co.id_observation NOT IN (SELECT ce_observation FROM tb_del.del_commentaire)
-- n'a pas de vote, même sur la proposition initiale
-- (traité au dessus, cf. matérialisation de la proposition initiale)
-- dont aucune des images n'a :
--     de vote sur aucun protocole
AND di.id_image NOT IN (SELECT ce_image FROM tb_del.del_image_vote)
--     de tag
AND di.id_image NOT IN (SELECT ce_image FROM tb_del.del_image_tag)
;

CREATE TABLE tmp_images_a_dezinguer (id integer primary key);

INSERT INTO tmp_images_a_dezinguer
SELECT distinct ci.id_image FROM cel_images ci
INNER JOIN tb_del.del_observation do ON ci.ce_observation = do.id_observation
WHERE do.mots_cles_texte LIKE '%plantnet%'
AND do.ce_utilisateur = 0
AND do.id_observation NOT IN (SELECT ce_observation FROM tb_del.del_commentaire)
AND ci.id_image NOT IN (SELECT ce_image FROM tb_del.del_image_vote)
AND ci.id_image NOT IN (SELECT ce_image FROM tb_del.del_image_tag);

CREATE TABLE tmp_obs_a_dezinguer (id integer primary key);

INSERT INTO tmp_obs_a_dezinguer
SELECT distinct co.id_observation FROM cel_obs co
INNER JOIN tb_del.del_image di ON di.ce_observation = co.id_observation
WHERE co.mots_cles_texte LIKE '%plantnet%'
AND co.transmission = 1
AND co.ce_utilisateur = 0
AND co.id_observation NOT IN (SELECT ce_observation FROM tb_del.del_commentaire)
AND di.id_image NOT IN (SELECT ce_image FROM tb_del.del_image_vote)
AND di.id_image NOT IN (SELECT ce_image FROM tb_del.del_image_tag);

DELETE FROM cel_images WHERE id_image IN (SELECT id FROM tmp_images_a_dezinguer);

DELETE FROM cel_obs WHERE id_observation IN (SELECT id FROM tmp_obs_a_dezinguer);

<?php
$db = new PDO('mysql:dbname=tb_cel;host=127.0.0.1;port=3306', 'apitela', 'bennes-y-donc-ton-mot-de-passe-la-bas-d-dans');

$req_ids_images = $db->query("SELECT id FROM tmp_images_a_dezinguer");
$ids_images = $req_ids_images->fetchAll(PDO::FETCH_COLUMN);
dezing_tranchette($ids_images, 'cel_images', 'id_image', 'images', 50000);

echo "\n";

$req_ids_obs = $db->query("SELECT id FROM tmp_obs_a_dezinguer");
$ids_obs = $req_ids_obs->fetchAll(PDO::FETCH_COLUMN);
dezing_tranchette($ids_obs, 'cel_obs', 'id_observation', 'observations', 50000);

echo "\n";
echo "À bientôt sur nos lignes :)\n";

function dezing_tranchette(&$ids, $table, $colonne, $truc_qu_on_dezingue="machins", $taille_tranche=10000) {
    global $db;
    echo "NB " . $truc_qu_on_dezingue . " à dézinguer: " . count($ids) . "\n";
    while (!empty($ids)) {
        $tranche = array_slice($ids, 0, $taille_tranche);
        array_splice($ids, 0, $taille_tranche);
        echo "- tranche : " . count($tranche) . " IDs; " . $truc_qu_on_dezingue . " restant à traiter : " . count($ids) . "\n";
        $ids_imploses = implode(',', $tranche);
        $req_del = "DELETE FROM $table WHERE $colonne IN(" . $ids_imploses . ")";
        echo "- dézingage de la tranche...\n";
        $db->query($req_del);
        echo "> tranche dézinguée !\n";
    }
}

-- champs étendus
DELETE FROM cel_obs_etendues WHERE id_observation NOT IN (SELECT id_observation FROM cel_obs);
-- mots-clés obs
DELETE FROM  cel_mots_cles_obs_liaison WHERE id_element_lie NOT IN (SELECT id_observation FROM cel_obs);
-- mots-clés images
DELETE FROM  cel_mots_cles_images_liaison WHERE id_element_lie NOT IN (SELECT id_image FROM cel_images);

DROP TABLE tmp_images_a_dezinguer;
DROP TABLE tmp_obs_a_dezinguer;