allow adding, removing and altering repositories

This commit also temporarily breaks package import from files :/

3 files changed, 287 insertions, 16 deletions

diff --git a/src/hydrilla/proxy/state_impl/concrete_state.py b/src/hydrilla/proxy/state_impl/concrete_state.py
index 46e7827..525a702 100644
--- a/src/hydrilla/proxy/state_impl/concrete_state.py
+++ b/src/hydrilla/proxy/state_impl/concrete_state.py
@@ -446,10 +446,6 @@ class ConcreteHaketiloState(base.HaketiloStateWithFields):
     def get_payload(self, payload_id: str) -> st.PayloadRef:
         raise NotImplementedError()
 
-    def add_repo(self, name: t.Optional[str], url: t.Optional[str]) \
-        -> st.RepoRef:
-        raise NotImplementedError()
-
     def get_settings(self) -> st.HaketiloGlobalSettings:
         return st.HaketiloGlobalSettings(
             mapping_use_mode       = st.MappingUseMode.AUTO,
diff --git a/src/hydrilla/proxy/state_impl/prune_packages.py b/src/hydrilla/proxy/state_impl/prune_packages.py
new file mode 100644
index 0000000..1857188
--- /dev/null
+++ b/src/hydrilla/proxy/state_impl/prune_packages.py
@@ -0,0 +1,152 @@
+# SPDX-License-Identifier: GPL-3.0-or-later
+
+# Haketilo proxy data and configuration (removal of packages that are not used).
+#
+# This file is part of Hydrilla&Haketilo.
+#
+# Copyright (C) 2022 Wojtek Kosior
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program.  If not, see <https://www.gnu.org/licenses/>.
+#
+#
+# I, Wojtek Kosior, thereby promise not to sue for violation of this
+# file's license. Although I request that you do not make use this code
+# in a proprietary program, I am not going to enforce this in court.
+
+"""
+....
+"""
+
+# Enable using with Python 3.7.
+from __future__ import annotations
+
+import sqlite3
+
+
+_remove_mapping_versions_sql = '''
+WITH removed_mappings AS (
+        SELECT
+                iv.item_version_id
+        FROM
+                          item_versions     AS iv
+                JOIN      items             AS i
+                        USING (item_id)
+                JOIN      orphan_iterations AS oi
+                        USING (repo_iteration_id)
+                LEFT JOIN payloads          AS p
+                        ON p.mapping_item_id = iv.item_version_id
+        WHERE
+                i.type = 'M' AND p.payload_id IS NULL
+)
+DELETE FROM
+        item_versions
+WHERE
+        item_version_id IN removed_mappings;
+'''
+
+_remove_resource_versions_sql = '''
+WITH removed_resources AS (
+        SELECT
+                iv.item_version_id
+        FROM
+                          item_versions               AS iv
+                JOIN      items                       AS i
+                        USING (item_id)
+                JOIN      orphan_iterations           AS oi
+                        USING (repo_iteration_id)
+                LEFT JOIN resolved_depended_resources AS rdr
+                        ON rdr.resource_item_id = iv.item_version_id
+        WHERE
+                rdr.payload_id IS NULL
+)
+DELETE FROM
+        item_versions
+WHERE
+        item_version_id IN removed_resources;
+'''
+
+_remove_items_sql = '''
+WITH removed_items AS (
+        SELECT
+                i.item_id
+        FROM
+                          items            AS i
+                LEFT JOIN item_versions    AS iv USING (item_id)
+                LEFT JOIN mapping_statuses AS ms USING (item_id)
+        WHERE
+                 iv.item_version_id IS NULL AND
+                 i.type = 'R' OR ms.enabled = 'N'
+)
+DELETE FROM
+        items
+WHERE
+        item_id IN removed_items;
+'''
+
+_remove_files_sql = '''
+WITH removed_files AS (
+        SELECT
+                f.file_id
+        FROM
+                          files     AS f
+                LEFT JOIN file_uses AS fu USING (file_id)
+        WHERE
+                fu.file_use_id IS NULL
+)
+DELETE FROM
+        files
+WHERE
+        file_id IN removed_files;
+'''
+
+_remove_repo_iterations_sql = '''
+WITH removed_iterations AS (
+        SELECT
+                oi.repo_iteration_id
+        FROM
+                          orphan_iterations AS oi
+                LEFT JOIN item_versions     AS iv USING (repo_iteration_id)
+        WHERE
+                iv.item_version_id IS NULL
+)
+DELETE FROM
+        repo_iterations
+WHERE
+        repo_iteration_id IN removed_iterations;
+'''
+
+_remove_repos_sql = '''
+WITH removed_repos AS (
+        SELECT
+                r.repo_id
+        FROM
+                          repos           AS r
+                LEFT JOIN repo_iterations AS ri USING (repo_id)
+        WHERE
+                r.deleted AND ri.repo_iteration_id IS NULL
+)
+DELETE FROM
+        repos
+WHERE
+        repo_id IN removed_repos;
+'''
+
+def prune(cursor: sqlite3.Cursor) -> None:
+    """...."""
+    cursor.execute(_remove_mapping_versions_sql)
+    cursor.execute(_remove_resource_versions_sql)
+    cursor.execute(_remove_items_sql)
+    cursor.execute(_remove_files_sql)
+    cursor.execute(_remove_repo_iterations_sql)
+    cursor.execute(_remove_repos_sql)
diff --git a/src/hydrilla/proxy/state_impl/repos.py b/src/hydrilla/proxy/state_impl/repos.py
index be11a88..5553ec2 100644
--- a/src/hydrilla/proxy/state_impl/repos.py
+++ b/src/hydrilla/proxy/state_impl/repos.py
@@ -32,19 +32,52 @@ inside Haketilo.
 # Enable using with Python 3.7.
 from __future__ import annotations
 
+import re
 import typing as t
 import dataclasses as dc
 
+from urllib.parse import urlparse
 from datetime import datetime
 
+import sqlite3
+
 from .. import state as st
 from . import base
+from . import prune_packages
+
+
+def validate_repo_url(url: str) -> None:
+    try:
+        parsed = urlparse(url)
+    except:
+        raise st.RepoUrlInvalid()
+
+    if parsed.scheme not in ('http', 'https'):
+        raise st.RepoUrlInvalid()
+
+
+def ensure_repo_not_deleted(cursor: sqlite3.Cursor, repo_id: str) -> None:
+    cursor.execute(
+        'SELECT deleted FROM repos WHERE repo_id = ?;',
+        (repo_id,)
+    )
+
+    rows = cursor.fetchall()
+
+    if rows == []:
+        raise st.MissingItemError()
+
+    (deleted,), = rows
+
+    if deleted:
+        raise st.MissingItemError()
+
 
 def make_repo_display_info(
         ref:            st.RepoRef,
         name:           str,
-        url:            t.Optional[str],
-        deleted:        t.Optional[bool],
+        url:            str,
+        deleted:        bool,
         last_refreshed: t.Optional[int],
         resource_count: int,
         mapping_count:  int
@@ -54,13 +87,14 @@ def make_repo_display_info(
             last_refreshed_converted = datetime.fromtimestamp(last_refreshed)
 
         return st.RepoDisplayInfo(
-            ref            = ref,
-            name           = name,
-            url            = url,
-            deleted        = deleted,
-            last_refreshed = last_refreshed_converted,
-            resource_count = resource_count,
-            mapping_count  = mapping_count
+            ref               = ref,
+            is_local_semirepo = ref.id == '1',
+            name              = name,
+            url               = url,
+            deleted           = deleted,
+            last_refreshed    = last_refreshed_converted,
+            resource_count    = resource_count,
+            mapping_count     = mapping_count
         )
 
 
@@ -70,15 +104,47 @@ class ConcreteRepoRef(st.RepoRef):
     state: base.HaketiloStateWithFields = dc.field(hash=False, compare=False)
 
     def remove(self) -> None:
-        raise NotImplementedError()
+        with self.state.cursor(transaction=True) as cursor:
+            ensure_repo_not_deleted(cursor, self.id)
+
+            cursor.execute(
+                '''
+                UPDATE
+                        repos
+                SET
+                        deleted             = TRUE,
+                        url                 = '',
+                        active_iteration_id = NULL,
+                        last_refreshed      = NULL
+                WHERE
+                        repo_id = ?;
+                ''',
+                (self.id,)
+            )
 
     def update(
             self,
             *,
             name: t.Optional[str] = None,
             url:  t.Optional[str] = None
-    ) -> st.RepoRef:
-        raise NotImplementedError()
+    ) -> None:
+        if name is not None:
+            raise NotImplementedError()
+
+        if url is None:
+            return
+
+        validate_repo_url(url)
+
+        with self.state.cursor(transaction=True) as cursor:
+            ensure_repo_not_deleted(cursor, self.id)
+
+            cursor.execute(
+                'UPDATE repos SET url = ? WHERE repo_id = ?;',
+                (url, self.id)
+            )
+
+            prune_packages.prune(cursor)
 
     def refresh(self) -> st.RepoIterationRef:
         raise NotImplementedError()
@@ -108,6 +174,19 @@ class ConcreteRepoRef(st.RepoRef):
         return make_repo_display_info(self, *row)
 
 
+repo_name_regex = re.compile(r'''
+^
+(?:
+  []a-zA-Z0-9()<>^&$.!,?@#|;:%"'*{}[/_=+-]+ # allowed non-whitespace characters
+
+  (?: # optional additional words separated by single spaces
+    [ ]
+    []a-zA-Z0-9()<>^&$.!,?@#|;:%"'*{}[/_=+-]+
+  )*
+)
+$
+''', re.VERBOSE)
+
 @dc.dataclass(frozen=True)
 class ConcreteRepoStore(st.RepoStore):
     state: base.HaketiloStateWithFields
@@ -115,6 +194,50 @@ class ConcreteRepoStore(st.RepoStore):
     def get(self, id: str) -> st.RepoRef:
         return ConcreteRepoRef(id, self.state)
 
+    def add(self, name: str, url: str) -> st.RepoRef:
+        name = name.strip()
+        if repo_name_regex.match(name) is None:
+            raise st.RepoNameInvalid()
+
+        validate_repo_url(url)
+
+        with self.state.cursor(transaction=True) as cursor:
+            cursor.execute(
+                '''
+                SELECT
+                        COUNT(repo_id)
+                FROM
+                        repos
+                WHERE
+                        NOT deleted AND name = ?;
+                ''',
+                (name,)
+            )
+            (name_taken,), = cursor.fetchall()
+
+            if name_taken:
+                raise st.RepoNameTaken()
+
+            cursor.execute(
+                '''
+                INSERT INTO repos(name, url, deleted, next_iteration)
+                VALUES (?, ?, FALSE, 1)
+                ON CONFLICT (name)
+                DO UPDATE SET
+                        name           = excluded.name,
+                        url            = excluded.url,
+                        deleted        = FALSE,
+                        last_refreshed = NULL;
+                ''',
+                (name, url)
+            )
+
+            cursor.execute('SELECT repo_id FROM repos WHERE name = ?;', (name,))
+
+            (repo_id,), = cursor.fetchall()
+
+            return ConcreteRepoRef(str(repo_id), self.state)
+
     def get_display_infos(self, include_deleted: bool = False) \
         -> t.Iterable[st.RepoDisplayInfo]:
         with self.state.cursor() as cursor: