make Haketilo proxy recognize identical items when updating from repository

2 files changed, 60 insertions, 11 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
     )
 
diff --git a/src/hydrilla/proxy/tables.sql b/src/hydrilla/proxy/tables.sql
index aa98081..863c989 100644
--- a/src/hydrilla/proxy/tables.sql
+++ b/src/hydrilla/proxy/tables.sql
@@ -176,18 +176,19 @@ CREATE TABLE mapping_statuses(
 );
 
 CREATE TABLE item_versions(
-        item_version_id     INTEGER PRIMARY KEY,
+        item_version_id     INTEGER  PRIMARY KEY,
 
-	item_id             INTEGER NOT NULL,
-        version             VARCHAR NOT NULL,
+	item_id             INTEGER  NOT NULL,
+        version             VARCHAR  NOT NULL,
 	-- "installed" determines whether item is INSTALLED, is NOT_INSTALLED or
 	-- it FAILED_TO_INSTALL when last tried. If "required" in a row of
 	-- "mapping_statuses is set to TRUE, the mapping version and all
 	-- resource versions corresponding to it are supposed to have
 	-- "installed" set to 'I'.
-	installed           CHAR(1) NOT NULL,
-	repo_iteration_id   INTEGER NOT NULL,
-	definition          BLOB    NOT NULL,
+	installed           CHAR(1)  NOT NULL,
+	repo_iteration_id   INTEGER  NOT NULL,
+	definition          BLOB     NOT NULL,
+	definition_sha256   CHAR(64) NOT NULL,
 
         UNIQUE (item_id, version, repo_iteration_id),
 	-- Constraint below needed to allow foreign key from "mapping_statuses".