Différences

Ci-dessous, les différences entre deux révisions de la page.

--- articles:informatique:nettoyage_de_la_base_de_donnee_de_pleroma [25/04/2020 14:21] – [Suppressions des activités distantes anciennes] antoineve
+++ articles:informatique:nettoyage_de_la_base_de_donnee_de_pleroma [25/04/2020 21:33] – [Suppressions de utilisateurs] antoineve
@@ Ligne 1: / Ligne 1: @@
-====== Suppressions des activités distantes anciennes ======
+====== Suppressions des activités et objets distants anciens ======
+===== Sans tenir compte des personnes suivies =====
 <code sql>
--- Création d'une table temporaire contenant les id des activités qu'on souhaite conserver
+-- Il est important de d'abord traiter la table activities
-CREATE TABLE tmp_activities_id AS SELECT id FROM activities WHERE local = True;
+-- puisqu'ensuite on supprime les objets qui n'ont plus
--- Insertion des activités de moins de 90 jours
+-- de lien avec cette table.
-INSERT INTO tmp_activities_id SELECT id FROM activities WHERE updated_at < (CURRENT_DATE - INTERVAL '90 days');
+--
--- Suppression des doublons
+-- 1ère partie pour la table activities
-DELETE FROM tmp_activities_id WHERE id IN (SELECT id FROM tmp_activities_id GROUP BY id HAVING count(*) > 1);
+--
--- Vidange de la table des activités
+DELETE FROM activities
-DELETE FROM activities WHERE id NOT IN (SELECT id FROM tmp_activities_id);
+WHERE local = false
--- Suppression de la table temporaire
+AND updated_at < (CURRENT_DATE - INTERVAL '90 days'); -- Ici 90 jours, à ajuster si besoin
-DROP TABLE tmp_activities_id;
+--
+-- 2ème partie pour la table objects
+--
+DELETE FROM objects
+WHERE id NOT IN (SELECT CAST(data->>'context_id' AS BIGINT) FROM activities)
+AND data->>'actor' NOT LIKE '%pleroma.antoineve.me%' -- Personnaliser le nom de l'instance !
+AND updated_at < (CURRENT_DATE - INTERVAL '90 days'); -- Ici 90 jours, à ajuster si besoin
 </code>
-====== Suppresions des objects devenus inutiles ======
-====== Suppressions de utilisateurs dont on n'entend plus parler ======
-Cette requête va supprimer les utilisateurs distants qui n'ont pas commnuniqué avec l'instance depuis un certain temps (ici, 90 jours) :
+===== En tenant compte des personnes suivies =====
 <code sql>
-DELETE FROM users WHERE last_refreshed_at < (CURRENT_DATE - INTERVAL '90 days') AND local = False;
+?
 </code>
+====== Suppressions d'utilisateurs (distants) ======
+===== Ceux qui n'ont aucun lien avec les activités ou les objets =====
+<code sql>
+DELETE FROM users
+WHERE ap_id NOT IN (
+SELECT data->>'actor' AS actor FROM objects
+WHERE data->>'actor' IS NOT NULL GROUP BY actor HAVING COUNT(*) > 1) -- Pour dédupliquer 8-)
+AND ap_id NOT IN (
+SELECT actor FROM activities GROUP BY actor HAVING COUNT(*) > 1)
+AND (
+ap_id LIKE '%/u%/%'               -- Certains services ActivityPub
+OR ap_id LIKE '%/account%/%'      -- utilisent d'autres termes
+OR ap_id LIKE '%/profile/%'       -- pour l'uri du profil utilisateur.
+OR ap_id LIKE '%/@/%'             -- Ce filtre permet d'éviter d'effacer
+OR ap_id LIKE '%/author/%');      -- des relais, des collections, ...
+</code>
+===== Ceux qui n'ont aucun followers et ne suivent personnes =====
+<code sql>
+DELETE FROM users
+WHERE following_count = 0
+AND follower_count = 0;
+</code>