[Git][gajim/gajim][master] cfix: ArchiveStorage: Cleanup orphaned records

[@lovetox]

Philipp Hörist pushed to branch master at gajim / gajim


Commits:
0190a9b4 by Philipp Hörist at 2024-05-04T09:13:01+02:00
cfix: ArchiveStorage: Cleanup orphaned records

- - - - -


2 changed files:

- gajim/common/storage/archive/migration.py
- gajim/common/storage/archive/storage.py


Changes:

=====================================
gajim/common/storage/archive/migration.py
=====================================
@@ -114,6 +114,8 @@ def __init__(self, archive: AlchemyStorage, user_version: 
int) -> None:
             self._v8()
         if user_version < 9:
             self._v9()
+        if user_version < 10:
+            self._v10()
 
         app.ged.raise_event(DBMigrationFinished())
 
@@ -226,6 +228,44 @@ def _v9(self) -> None:
 
         self._execute_multiple(statements)
 
+    def _v10(self) -> None:
+        self._execute_multiple(['PRAGMA foreign_keys=OFF'])
+
+        with self._engine.begin() as conn:
+
+            stmt = sa.select(mod.Message.fk_occupant_pk).union(
+                sa.select(mod.Moderation.fk_occupant_pk),
+                sa.select(mod.DisplayedMarker.fk_occupant_pk),
+            )
+            occupant_pks = set(conn.scalars(stmt))
+            occupant_pks.discard(None)
+
+            
conn.execute(sa.delete(mod.Occupant).where(mod.Occupant.pk.not_in(occupant_pks)))
+            conn.commit()
+
+        with self._engine.begin() as conn:
+
+            stmt = sa.select(mod.Occupant.fk_remote_pk).union(
+                sa.select(mod.Occupant.fk_real_remote_pk),
+                sa.select(mod.SecurityLabel.fk_remote_pk),
+                sa.select(mod.MessageError.fk_remote_pk),
+                sa.select(mod.Moderation.fk_remote_pk),
+                sa.select(mod.DisplayedMarker.fk_remote_pk),
+                sa.select(mod.Receipt.fk_remote_pk),
+                sa.select(mod.MAMArchiveState.fk_remote_pk),
+                sa.select(mod.Message.fk_remote_pk),
+            )
+
+            occupant_pks = set(conn.scalars(stmt))
+            occupant_pks.discard(None)
+
+            
conn.execute(sa.delete(mod.Remote).where(mod.Remote.pk.not_in(occupant_pks)))
+
+        self._execute_multiple([
+            'PRAGMA foreign_keys=ON',
+            'PRAGMA user_version=10'
+        ])
+
     def _process_archive_row(
         self,
         conn: sa.Connection,


=====================================
gajim/common/storage/archive/storage.py
=====================================
@@ -52,7 +52,7 @@
 from gajim.common.storage.base import with_session
 from gajim.common.util.datetime import FIRST_UTC_DATETIME
 
-CURRENT_USER_VERSION = 9
+CURRENT_USER_VERSION = 10
 
 
 log = logging.getLogger('gajim.c.storage.archive')



View it on GitLab: 
https://dev.gajim.org/gajim/gajim/-/commit/0190a9b462b3e08c60a93a11280f515b4746c062

-- 
View it on GitLab: 
https://dev.gajim.org/gajim/gajim/-/commit/0190a9b462b3e08c60a93a11280f515b4746c062
You're receiving this email because of your account on dev.gajim.org.



_______________________________________________
Commits mailing list -- commits@gajim.org
To unsubscribe send an email to commits-le...@gajim.org