[proxy] facilitate manually pruning orphaned packages (including installed ones)

1 files changed, 48 insertions, 34 deletions

diff --git a/src/hydrilla/proxy/state_impl/_operations/prune_orphans.py b/src/hydrilla/proxy/state_impl/_operations/prune_orphans.py
index 7123047..5eb8cf7 100644
--- a/src/hydrilla/proxy/state_impl/_operations/prune_orphans.py
+++ b/src/hydrilla/proxy/state_impl/_operations/prune_orphans.py
@@ -37,37 +37,52 @@ import sqlite3
 from pathlib import Path
 
 
-_remove_item_versions_sqls = [
-    '''
-    CREATE TEMPORARY TABLE __removed_versions(
-            item_version_id INTEGER PRIMARY KEY
-    );
-    ''', '''
-    INSERT INTO
-            __removed_versions
-    SELECT
-            iv.item_version_id
-    FROM
-                 item_versions     AS iv
-            JOIN orphan_iterations AS oi USING (repo_iteration_id)
-    WHERE
-            iv.installed != 'I';
-    ''', '''
-    UPDATE
-            mapping_statuses
-    SET
-            active_version_id = NULL
-    WHERE
-            active_version_id IN __removed_versions;
-    ''', '''
-    DELETE FROM
-            item_versions
-    WHERE
-            item_version_id IN __removed_versions;
-    ''', '''
-    DROP TABLE __removed_versions;
-    '''
-]
+def _remove_item_versions(cursor: sqlite3.Cursor, with_installed: bool) -> None:
+    cursor.execute(
+        '''
+        CREATE TEMPORARY TABLE __removed_versions(
+                item_version_id INTEGER PRIMARY KEY
+        );
+        '''
+    )
+
+    condition = "iv.active != 'R'" if with_installed else "iv.installed != 'I'"
+
+    cursor.execute(
+        f'''
+        INSERT INTO
+                __removed_versions
+        SELECT
+                iv.item_version_id
+        FROM
+                     item_versions     AS iv
+                JOIN orphan_iterations AS oi USING (repo_iteration_id)
+        WHERE
+                {condition};
+        '''
+    )
+
+    cursor.execute(
+        '''
+        UPDATE
+                mapping_statuses
+        SET
+                active_version_id = NULL
+        WHERE
+                active_version_id IN __removed_versions;
+        '''
+    )
+
+    cursor.execute(
+        '''
+        DELETE FROM
+                item_versions
+        WHERE
+                item_version_id IN __removed_versions;
+        '''
+    )
+
+    cursor.execute('DROP TABLE __removed_versions;')
 
 _remove_items_sql = '''
 WITH removed_items AS (
@@ -159,11 +174,10 @@ WHERE
         repo_id IN removed_repos;
 '''
 
-def prune_orphans(cursor: sqlite3.Cursor) -> None:
+def prune_orphans(cursor: sqlite3.Cursor, aggressive: bool = False) -> None:
     assert cursor.connection.in_transaction
 
-    for sql in _remove_item_versions_sqls:
-        cursor.execute(sql)
+    _remove_item_versions(cursor, with_installed=aggressive)
     cursor.execute(_remove_items_sql)
     cursor.execute(_remove_files_sql)
     cursor.execute(_forget_files_data_sql)