1 files changed, 53 insertions, 5 deletions

diff --git a/src/hydrilla/proxy/state_impl/_operations/load_packages.py b/src/hydrilla/proxy/state_impl/_operations/load_packages.py
index 16d1154..defa9b6 100644
--- a/src/hydrilla/proxy/state_impl/_operations/load_packages.py
+++ b/src/hydrilla/proxy/state_impl/_operations/load_packages.py
@@ -34,6 +34,7 @@ from __future__ import annotations
 import io
 import mimetypes
 import sqlite3
+import hashlib
 import dataclasses as dc
 import typing as t
 
@@ -124,16 +125,60 @@ def get_or_make_item(cursor: sqlite3.Cursor, type: str, identifier: str) -> int:
 
     return item_id
 
-def make_item_version(
+def update_or_make_item_version(
         cursor:            sqlite3.Cursor,
         item_id:           int,
         version:           versions.VerTuple,
         installed:         str,
         repo_iteration_id: int,
+        repo_id:           int,
         definition:        bytes
 ) -> int:
     ver_str = versions.version_string(version)
 
+    definition_sha256 = hashlib.sha256(definition).digest().hex()
+
+    cursor.execute(
+        '''
+        SELECT
+                item_version_id
+        FROM
+                item_versions AS iv
+                JOIN repo_iterations AS ri USING (repo_iteration_id)
+                JOIN repos           AS r  USING (repo_id)
+        WHERE
+                r.repo_id = ? AND iv.definition_sha256 = ?;
+        ''',
+        (repo_id, definition_sha256)
+    )
+
+    rows = cursor.fetchall()
+
+    if rows != []:
+        (item_version_id,), = rows
+        cursor.execute(
+            '''
+            UPDATE
+                    item_versions
+            SET
+                    installed = (
+                            CASE WHEN
+                                    installed = 'I' OR ? = 'I'
+                            THEN
+                                    'I'
+                            ELSE
+                                    'N'
+                            END
+                    ),
+                    repo_iteration_id = ?
+            WHERE
+                    item_version_id = ?;
+            ''',
+            (installed, repo_iteration_id, item_version_id)
+        )
+
+        return item_version_id
+
     cursor.execute(
         '''
         INSERT INTO item_versions(
@@ -141,11 +186,13 @@ def make_item_version(
                 version,
                 installed,
 	        repo_iteration_id,
-                definition
+                definition,
+                definition_sha256
         )
-        VALUES(?, ?, ?, ?, ?);
+        VALUES(?, ?, ?, ?, ?, ?);
         ''',
-        (item_id, ver_str, installed, repo_iteration_id, definition)
+        (item_id, ver_str, installed, repo_iteration_id, definition,
+         definition_sha256)
     )
 
     cursor.execute(
@@ -223,12 +270,13 @@ def _add_item(
     if isinstance(info, item_infos.MappingInfo):
         make_mapping_status(cursor, item_id)
 
-    item_version_id = make_item_version(
+    item_version_id = update_or_make_item_version(
         cursor            = cursor,
         item_id           = item_id,
         version           = info.version,
         installed         = 'I' if repo_id == 1 else 'N',
         repo_iteration_id = repo_iteration_id,
+        repo_id           = repo_id,
         definition        = definition
     )