[proxy] make package version info page also display other versions; fix&improve some aspects of package loading and dependency recomputing

4 files changed, 172 insertions, 131 deletions

diff --git a/src/hydrilla/proxy/state_impl/_operations/load_packages.py b/src/hydrilla/proxy/state_impl/_operations/load_packages.py
index defa9b6..76a24d1 100644
--- a/src/hydrilla/proxy/state_impl/_operations/load_packages.py
+++ b/src/hydrilla/proxy/state_impl/_operations/load_packages.py
@@ -64,18 +64,6 @@ def make_repo_iteration(cursor: sqlite3.Cursor, repo_id: int) -> int:
 
     cursor.execute(
         '''
-        UPDATE
-                repos
-        SET
-                next_iteration = ?
-        WHERE
-                repo_id = ?;
-        ''',
-        (next_iteration + 1, repo_id)
-    )
-
-    cursor.execute(
-        '''
         INSERT INTO repo_iterations(repo_id, iteration)
         VALUES(?, ?);
         ''',
@@ -96,6 +84,30 @@ def make_repo_iteration(cursor: sqlite3.Cursor, repo_id: int) -> int:
 
     (repo_iteration_id,), = cursor.fetchall()
 
+    cursor.execute(
+        '''
+        UPDATE
+                repos
+        SET
+                next_iteration = ?,
+                active_iteration_id = (
+                        CASE
+                        WHEN repo_id = 1 THEN NULL
+                        ELSE ?
+                        END
+                ),
+                last_refreshed = (
+                        CASE
+                        WHEN repo_id = 1 THEN NULL
+                        ELSE STRFTIME('%s', 'NOW')
+                        END
+                )
+        WHERE
+                repo_id = ?;
+        ''',
+        (next_iteration + 1, repo_iteration_id, repo_id)
+    )
+
     return repo_iteration_id
 
 def get_or_make_item(cursor: sqlite3.Cursor, type: str, identifier: str) -> int:
@@ -162,12 +174,9 @@ def update_or_make_item_version(
                     item_versions
             SET
                     installed = (
-                            CASE WHEN
-                                    installed = 'I' OR ? = 'I'
-                            THEN
-                                    'I'
-                            ELSE
-                                    'N'
+                            CASE
+                            WHEN installed = 'I' OR ? = 'I' THEN 'I'
+                            ELSE 'N'
                             END
                     ),
                     repo_iteration_id = ?
@@ -213,10 +222,7 @@ def update_or_make_item_version(
 
 def make_mapping_status(cursor: sqlite3.Cursor, item_id: int) -> None:
     cursor.execute(
-        '''
-        INSERT OR IGNORE INTO mapping_statuses(item_id, enabled, required)
-        VALUES(?, 'N', FALSE);
-        ''',
+        'INSERT OR IGNORE INTO mapping_statuses(item_id) VALUES(?);',
         (item_id,)
     )
 
@@ -395,6 +401,8 @@ def _load_packages_no_state_update(
                 repo_id           = repo_id
             )
 
+    prune_packages(cursor)
+
     _recompute_dependencies_no_state_update(
         cursor                 = cursor,
         semirepo_file_resolver = MalcontentFileResolver(malcontent_path)
diff --git a/src/hydrilla/proxy/state_impl/_operations/recompute_dependencies.py b/src/hydrilla/proxy/state_impl/_operations/recompute_dependencies.py
index 494d130..4b48ce8 100644
--- a/src/hydrilla/proxy/state_impl/_operations/recompute_dependencies.py
+++ b/src/hydrilla/proxy/state_impl/_operations/recompute_dependencies.py
@@ -51,12 +51,13 @@ def _get_infos_of_type(cursor: sqlite3.Cursor, info_type: t.Type[AnyInfoVar],) \
     cursor.execute(
         '''
         SELECT
-                i.item_id, iv.definition, r.name, ri.iteration
+                ive.item_version_id,
+                ive.definition,
+                ive.repo,
+                ive.repo_iteration
         FROM
-                     item_versions   AS iv
-                JOIN items           AS i  USING (item_id)
-                JOIN repo_iterations AS ri USING (repo_iteration_id)
-                JOIN repos           AS r  USING (repo_id)
+                     item_versions_extra AS ive
+                JOIN items               AS i USING (item_id)
         WHERE
                 i.type = ?;
         ''',
@@ -65,9 +66,10 @@ def _get_infos_of_type(cursor: sqlite3.Cursor, info_type: t.Type[AnyInfoVar],) \
 
     result: dict[int, AnyInfoVar] = {}
 
-    for item_id, definition, repo_name, repo_iteration in cursor.fetchall():
+    for item_version_id, definition, repo_name, repo_iteration \
+        in cursor.fetchall():
         info = info_type.load(definition, repo_name, repo_iteration)
-        result[item_id] = info
+        result[item_version_id] = info
 
     return result
 
@@ -93,11 +95,8 @@ def _recompute_dependencies_no_state_update_no_pull_files(
     ids_to_resources = _get_infos_of_type(cursor, item_infos.ResourceInfo)
     ids_to_mappings  = _get_infos_of_type(cursor, item_infos.MappingInfo)
 
-    resources = ids_to_resources.items()
-    resources_to_ids = dict((info.identifier, id) for id, info in resources)
-
-    mappings = ids_to_mappings.items()
-    mappings_to_ids = dict((info.identifier, id) for id, info in mappings)
+    resources_to_ids = dict((info, id) for id, info in ids_to_resources.items())
+    mappings_to_ids  = dict((info, id) for id, info in ids_to_mappings.items())
 
     requirements = [*extra_requirements]
 
@@ -150,17 +149,18 @@ def _recompute_dependencies_no_state_update_no_pull_files(
         UPDATE
                 mapping_statuses
         SET
-                required          = FALSE,
                 active_version_id = NULL
         WHERE
                 enabled != 'E';
         '''
     )
 
+    cursor.execute("UPDATE item_versions SET active = 'N';")
+
     cursor.execute('DELETE FROM payloads;')
 
     for choice in mapping_choices.values():
-        mapping_ver_id = mappings_to_ids[choice.info.identifier]
+        mapping_ver_id = mappings_to_ids[choice.info]
 
         if choice.required:
             _mark_version_installed(cursor, mapping_ver_id)
@@ -184,12 +184,23 @@ def _recompute_dependencies_no_state_update_no_pull_files(
             UPDATE
                     mapping_statuses
             SET
-                    required          = ?,
                     active_version_id = ?
             WHERE
                     item_id = ?;
             ''',
-            (choice.required, mapping_ver_id, mapping_item_id)
+            (mapping_ver_id, mapping_item_id)
+        )
+
+        cursor.execute(
+            '''
+            UPDATE
+                    item_versions
+            SET
+                    active = ?
+            WHERE
+                    item_version_id = ?;
+            ''',
+            (mapping_ver_id, 'R' if choice.required else 'A')
         )
 
         for num, (pattern, payload) in enumerate(choice.payloads.items()):
@@ -226,7 +237,7 @@ def _recompute_dependencies_no_state_update_no_pull_files(
             (payload_id,), = cursor.fetchall()
 
             for res_num, resource_info in enumerate(payload.resources):
-                resource_ver_id = resources_to_ids[resource_info.identifier]
+                resource_ver_id = resources_to_ids[resource_info]
 
                 if choice.required:
                     _mark_version_installed(cursor, resource_ver_id)
@@ -243,6 +254,27 @@ def _recompute_dependencies_no_state_update_no_pull_files(
                     (payload_id, resource_ver_id, res_num)
                 )
 
+                new_status = 'R' if choice.required else 'A'
+
+                cursor.execute(
+                    '''
+                    UPDATE
+                            item_versions
+                    SET
+                            active = (
+                                    CASE
+                                    WHEN active = 'R' OR ? = 'R' THEN 'R'
+                                    WHEN active = 'A' OR ? = 'A' THEN 'A'
+                                    ELSE 'N'
+                                    END
+                            )
+                    WHERE
+                            item_version_id = ?;
+                    ''',
+                    (new_status, new_status, resource_ver_id)
+                )
+
+
 def _recompute_dependencies_no_state_update(
         cursor:                 sqlite3.Cursor,
         extra_requirements:     t.Iterable[sds.MappingRequirement] = (),
diff --git a/src/hydrilla/proxy/state_impl/mappings.py b/src/hydrilla/proxy/state_impl/mappings.py
index 946df14..7d08e58 100644
--- a/src/hydrilla/proxy/state_impl/mappings.py
+++ b/src/hydrilla/proxy/state_impl/mappings.py
@@ -51,19 +51,20 @@ class ConcreteMappingRef(st.MappingRef):
             cursor.execute(
                 '''
                 SELECT
-                        item_version_id,
-                        definition,
-                        repo,
-                        repo_iteration,
-                        installed, 
-                        is_active,
-                        is_orphan,
-                        is_local,
-                        enabled
+                        ive.item_version_id,
+                        ive.definition,
+                        ive.repo,
+                        ive.repo_iteration,
+                        ive.installed,
+                        ive.active,
+                        ive.is_orphan,
+                        ive.is_local,
+                        ms.enabled
                 FROM
-                        mapping_display_infos
+                        item_versions_extra   AS ive
+                        JOIN mapping_statuses AS ms USING (item_id)
                 WHERE
-                        item_id = ?;
+                        ive.item_id = ?;
                 ''',
                 (self.id,)
             )
@@ -75,8 +76,9 @@ class ConcreteMappingRef(st.MappingRef):
 
         result = []
 
-        for (item_version_id, definition, repo, repo_iteration, installed,
-             is_active, is_orphan, is_local, status_letter) in rows:
+        for (item_version_id, definition, repo, repo_iteration,
+             installed_status, active_status, is_orphan, is_local,
+             enabled_status) in rows:
             ref = ConcreteMappingVersionRef(str(item_version_id), self.state)
 
             item_info = item_infos.MappingInfo.load(
@@ -88,11 +90,11 @@ class ConcreteMappingRef(st.MappingRef):
             display_info = st.MappingVersionDisplayInfo(
                 ref             = ref,
                 info            = item_info,
-                installed       = st.InstalledStatus(installed),
-                is_active       = is_active,
+                installed       = st.InstalledStatus(installed_status),
+                active          = st.ActiveStatus(active_status),
                 is_orphan       = is_orphan,
                 is_local        = is_local,
-                mapping_enabled = st.EnabledStatus(status_letter)
+                mapping_enabled = st.EnabledStatus(enabled_status)
             )
             result.append(display_info)
 
@@ -104,22 +106,31 @@ class ConcreteMappingStore(st.MappingStore):
     state: base.HaketiloStateWithFields
 
     def get(self, id: str) -> st.MappingRef:
-        return ConcreteMappingRef(id, self.state)
+        return ConcreteMappingRef(str(int(id)), self.state)
 
     def get_display_infos(self) -> t.Sequence[st.MappingDisplayInfo]:
         with self.state.cursor() as cursor:
             cursor.execute(
                 '''
-                SELECT DISTINCT
-                        item_id,
-                        identifier,
-                        CASE WHEN is_active THEN item_version_id ELSE NULL END,
-                        CASE WHEN is_active THEN definition      ELSE NULL END,
-                        CASE WHEN is_active THEN repo            ELSE NULL END,
-                        CASE WHEN is_active THEN repo_iteration  ELSE NULL END,
-                        enabled
+                SELECT
+                        i.item_id,
+                        i.identifier,
+                        ive.item_version_id,
+                        ive.definition,
+                        ive.repo,
+                        ive.repo_iteration,
+                        ive.installed,
+                        ive.active,
+                        ive.is_orphan,
+                        ive.is_local,
+                        ms.enabled
                 FROM
-                        mapping_display_infos;
+                                  items                 AS i
+                        JOIN      mapping_statuses      AS ms
+                                USING (item_id)
+                        LEFT JOIN item_versions_extra   AS ive
+                                ON ms.active_version_id = ive.item_version_id AND
+                                   ive.active IN ('R', 'A');
                 '''
             )
 
@@ -128,34 +139,45 @@ class ConcreteMappingStore(st.MappingStore):
         result = []
 
         for (item_id, identifier, item_version_id, definition, repo,
-             repo_iteration, status_letter) in rows:
+             repo_iteration, installed_status, active_status, is_orphan,
+             is_local, enabled_status) in rows:
             ref = ConcreteMappingRef(str(item_id), self.state)
 
-            active_version_ref: t.Optional[st.MappingVersionRef]   = None
-            item_info:          t.Optional[item_infos.MappingInfo] = None
+            active_version: t.Optional[st.MappingVersionDisplayInfo] = None
 
             if item_version_id is not None:
                 active_version_ref = ConcreteMappingVersionRef(
                     id    = str(item_version_id),
                     state = self.state
                 )
-                item_info = item_infos.MappingInfo.load(
+
+                active_version_info = item_infos.MappingInfo.load(
                     definition,
                     repo,
                     repo_iteration
                 )
 
+                active_version = st.MappingVersionDisplayInfo(
+                    ref             = active_version_ref,
+                    info            = active_version_info,
+                    installed       = st.InstalledStatus(installed_status),
+                    active          = st.ActiveStatus(active_status),
+                    is_orphan       = is_orphan,
+                    is_local        = is_local,
+                    mapping_enabled = st.EnabledStatus(enabled_status)
+                )
+
+
             display_info = st.MappingDisplayInfo(
-                ref                 = ref,
-                identifier          = identifier,
-                enabled             = st.EnabledStatus(status_letter),
-                active_version_ref  = active_version_ref,
-                active_version_info = item_info
+                ref            = ref,
+                identifier     = identifier,
+                enabled        = st.EnabledStatus(enabled_status),
+                active_version = active_version
             )
 
             result.append(display_info)
 
-        return result
+        return sorted(result, key=(lambda di: di.identifier))
 
 
 @dc.dataclass(frozen=True, unsafe_hash=True)
@@ -181,9 +203,26 @@ class ConcreteMappingVersionRef(st.MappingVersionRef):
 
     def install(self) -> None:
         with self.state.cursor(transaction=True) as cursor:
-            info = self.get_display_info()
+            cursor.execute(
+                '''
+                SELECT
+                        installed
+                FROM
+                        item_versions
+                WHERE
+                        item_version_id = ?;
+                ''',
+                (self.id,)
+            )
+
+            rows = cursor.fetchall()
+
+            if rows == []:
+                raise st.MissingItemError()
+
+            (installed_status,), = rows
 
-            if info.installed == st.InstalledStatus.INSTALLED:
+            if installed_status == 'I':
                 return
 
             self._set_installed_status(cursor, st.InstalledStatus.INSTALLED)
@@ -204,21 +243,15 @@ class ConcreteMappingVersionRef(st.MappingVersionRef):
         #
         #     ....
 
-    def get_display_info(self) -> st.MappingVersionDisplayInfo:
+    def get_all_version_display_infos(self) \
+        -> t.Sequence[st.MappingVersionDisplayInfo]:
         with self.state.cursor() as cursor:
             cursor.execute(
                 '''
                 SELECT
-                        definition,
-                        repo,
-                        repo_iteration,
-                        installed,
-                        is_orphan,
-                        is_active,
-                        is_local,
-                        enabled
+                        item_id
                 FROM
-                        mapping_display_infos
+                        item_versions
                 WHERE
                         item_version_id = ?;
                 ''',
@@ -226,28 +259,14 @@ class ConcreteMappingVersionRef(st.MappingVersionRef):
             )
 
             rows = cursor.fetchall()
+            if rows == []:
+                raise st.MissingItemError()
 
-        if rows == []:
-            raise st.MissingItemError()
-
-        (definition, repo, repo_iteration, installed, is_orphan, is_active,
-         is_local, status_letter), = rows
+            (mapping_id,), = rows
 
-        item_info = item_infos.MappingInfo.load(
-            definition,
-            repo,
-            repo_iteration
-        )
+            mapping_ref = ConcreteMappingRef(str(mapping_id), self.state)
 
-        return st.MappingVersionDisplayInfo(
-            ref             = self,
-            info            = item_info,
-            installed       = st.InstalledStatus(installed),
-            is_active       = is_active,
-            is_orphan       = is_orphan,
-            is_local        = is_local,
-            mapping_enabled = st.EnabledStatus(status_letter)
-        )
+            return mapping_ref.get_version_display_infos()
 
 
 @dc.dataclass(frozen=True)
@@ -255,4 +274,4 @@ class ConcreteMappingVersionStore(st.MappingVersionStore):
     state: base.HaketiloStateWithFields
 
     def get(self, id: str) -> st.MappingVersionRef:
-        return ConcreteMappingVersionRef(id, self.state)
+        return ConcreteMappingVersionRef(str(int(id)), self.state)
diff --git a/src/hydrilla/proxy/state_impl/repos.py b/src/hydrilla/proxy/state_impl/repos.py
index d4fbbce..346e113 100644
--- a/src/hydrilla/proxy/state_impl/repos.py
+++ b/src/hydrilla/proxy/state_impl/repos.py
@@ -202,17 +202,11 @@ class ConcreteRepoRef(st.RepoRef):
             cursor.execute(
                 '''
                 SELECT
-                        iv.definition, r.name, ri.iteration
+                        definition, repo, repo_iteration
                 FROM
-                             mapping_statuses AS ms
-                        JOIN item_versions    AS iv
-                                ON ms.active_version_id = iv.item_version_id
-                        JOIN repo_iterations  AS ri
-                                USING (repo_iteration_id)
-                        JOIN repos            AS r
-                                USING (repo_id)
+                        item_versions_extra
                 WHERE
-                        ms.required
+                        active = 'R';
                 '''
             )
 
@@ -288,18 +282,6 @@ class ConcreteRepoRef(st.RepoRef):
 
             self.state.rebuild_structures()
 
-            cursor.execute(
-                '''
-                UPDATE
-                        repos
-                SET
-                        last_refreshed = STRFTIME('%s', 'NOW')
-                WHERE
-                        repo_id = ?;
-                ''',
-                (self.id,)
-            )
-
         return ConcreteRepoIterationRef(str(new_iteration_id), self.state)
 
     def get_display_info(self) -> st.RepoDisplayInfo:
@@ -332,7 +314,7 @@ class ConcreteRepoStore(st.RepoStore):
     state: base.HaketiloStateWithFields
 
     def get(self, id: str) -> st.RepoRef:
-        return ConcreteRepoRef(id, self.state)
+        return ConcreteRepoRef(str(int(id)), self.state)
 
     def add(self, name: str, url: str) -> st.RepoRef:
         name = name.strip()
@@ -360,8 +342,8 @@ class ConcreteRepoStore(st.RepoStore):
 
             cursor.execute(
                 '''
-                INSERT INTO repos(name, url, deleted, next_iteration)
-                VALUES (?, ?, FALSE, 1)
+                INSERT INTO repos(name, url)
+                VALUES (?, ?)
                 ON CONFLICT (name)
                 DO UPDATE SET
                         name           = excluded.name,