[proxy] make package auto-installation work

1 files changed, 25 insertions, 0 deletions

diff --git a/src/hydrilla/proxy/state_impl/_operations/prune_orphans.py b/src/hydrilla/proxy/state_impl/_operations/prune_orphans.py
index f4ebd52..5a3f4f0 100644
--- a/src/hydrilla/proxy/state_impl/_operations/prune_orphans.py
+++ b/src/hydrilla/proxy/state_impl/_operations/prune_orphans.py
@@ -102,6 +102,30 @@ WHERE
         file_id IN removed_files;
 '''
 
+_forget_files_data_sql = '''
+WITH forgotten_files AS (
+        SELECT
+                f.file_id
+        FROM
+                          files         AS f
+                     JOIN file_uses     AS fu
+                        USING (file_id)
+                LEFT JOIN item_versions AS iv
+                        ON (fu.item_version_id = iv.item_version_id AND
+                            iv.installed = 'I')
+        GROUP BY
+                f.file_id
+        HAVING
+                COUNT(iv.item_version_id) = 0
+)
+UPDATE
+        files
+SET
+        data = NULL
+WHERE
+        file_id IN forgotten_files;
+'''
+
 _remove_repo_iterations_sql = '''
 WITH removed_iterations AS (
         SELECT
@@ -141,5 +165,6 @@ def prune_orphans(cursor: sqlite3.Cursor) -> None:
         cursor.execute(sql)
     cursor.execute(_remove_items_sql)
     cursor.execute(_remove_files_sql)
+    cursor.execute(_forget_files_data_sql)
     cursor.execute(_remove_repo_iterations_sql)
     cursor.execute(_remove_repos_sql)