save computed payloads into sqlite db

3 files changed, 108 insertions, 63 deletions

diff --git a/src/hydrilla/proxy/state_impl/base.py b/src/hydrilla/proxy/state_impl/base.py
index 78a50c0..788a93d 100644
--- a/src/hydrilla/proxy/state_impl/base.py
+++ b/src/hydrilla/proxy/state_impl/base.py
@@ -72,40 +72,38 @@ class HaketiloStateWithFields(state.HaketiloState):
     lock: threading.RLock = dc.field(default_factory=threading.RLock)
 
     @contextmanager
-    def cursor(self, lock: bool = False, transaction: bool = False) \
+    def cursor(self, transaction: bool = False) \
         -> t.Iterator[sqlite3.Cursor]:
         """...."""
         start_transaction = transaction and not self.connection.in_transaction
 
-        assert lock or not start_transaction
-
         try:
-            if lock:
-                self.lock.acquire()
+            self.lock.acquire()
 
             if self.current_cursor is not None:
                 yield self.current_cursor
                 return
 
-            self.current_cursor = self.connection.cursor()
-
-            if start_transaction:
-                self.current_cursor.execute('BEGIN TRANSACTION;')
-
             try:
-                yield self.current_cursor
+                self.current_cursor = self.connection.cursor()
 
                 if start_transaction:
-                    self.current_cursor.execute('COMMIT TRANSACTION;')
-            except:
-                if start_transaction:
-                    self.current_cursor.execute('ROLLBACK TRANSACTION;')
-                raise
+                    self.current_cursor.execute('BEGIN TRANSACTION;')
+
+                try:
+                    yield self.current_cursor
+
+                    if start_transaction:
+                        assert self.connection.in_transaction
+                        self.current_cursor.execute('COMMIT TRANSACTION;')
+                except:
+                    if start_transaction:
+                        self.current_cursor.execute('ROLLBACK TRANSACTION;')
+                    raise
+            finally:
+                self.current_cursor = None
         finally:
-            self.current_cursor = None
-
-            if lock:
-                self.lock.release()
+            self.lock.release()
 
     def rebuild_structures(self, cursor: sqlite3.Cursor) -> None:
         """...."""
diff --git a/src/hydrilla/proxy/state_impl/concrete_state.py b/src/hydrilla/proxy/state_impl/concrete_state.py
index 1b46ae9..ccb1269 100644
--- a/src/hydrilla/proxy/state_impl/concrete_state.py
+++ b/src/hydrilla/proxy/state_impl/concrete_state.py
@@ -313,19 +313,10 @@ def get_or_make_item_version(
         cursor:            sqlite3.Cursor,
         item_id:           int,
         repo_iteration_id: int,
-        definition:        str,
-        info:              item_infos.AnyInfo
+        version:           versions.VerTuple,
+        definition:        str
 ) -> int:
-    ver_str = versions.version_string(info.version)
-
-    values = (
-        item_id,
-        ver_str,
-        repo_iteration_id,
-        definition,
-        info.allows_eval,
-        info.allows_cors_bypass
-    )
+    ver_str = versions.version_string(version)
 
     cursor.execute(
         '''
@@ -333,13 +324,11 @@ def get_or_make_item_version(
     	        item_id,
                 version,
 	        repo_iteration_id,
-                definition,
-                eval_allowed,
-                cors_bypass_allowed
+                definition
         )
-        VALUES(?, ?, ?, ?, ?, ?);
+        VALUES(?, ?, ?, ?);
         ''',
-        values
+        (item_id, ver_str, repo_iteration_id, definition)
     )
 
     cursor.execute(
@@ -417,12 +406,12 @@ def make_file_use(
         (item_version_id, file_id, name, type, mime_type, idx)
     )
 
-def get_infos_of_type(cursor: sqlite3.Cursor, info_type: t.Type[AnyInfoVar]) \
-    -> t.Iterable[AnyInfoVar]:
+def get_infos_of_type(cursor: sqlite3.Cursor, info_type: t.Type[AnyInfoVar],) \
+    -> t.Mapping[AnyInfoVar, int]:
     cursor.execute(
         '''
         SELECT
-                iv.definition, r.name, ri.iteration
+                i.item_id, iv.definition, r.name, ri.iteration
         FROM
                      item_versions   AS iv
                 JOIN items           AS i  USING (item_id)
@@ -434,11 +423,12 @@ def get_infos_of_type(cursor: sqlite3.Cursor, info_type: t.Type[AnyInfoVar]) \
         (info_type.type_name[0].upper(),)
     )
 
-    result: list[AnyInfoVar] = []
+    result: dict[AnyInfoVar, int] = {}
 
-    for definition, repo_name, repo_iteration in cursor.fetchall():
+    for item_id, definition, repo_name, repo_iteration in cursor.fetchall():
         definition_io = io.StringIO(definition)
-        result.append(info_type.load(definition_io, repo_name, repo_iteration))
+        info = info_type.load(definition_io, repo_name, repo_iteration)
+        result[info] = item_id
 
     return result
 
@@ -449,7 +439,7 @@ class ConcreteHaketiloState(base.HaketiloStateWithFields):
 
         self._populate_database_with_stuff_from_temporary_malcontent_dir()
 
-        with self.cursor() as cursor:
+        with self.cursor(transaction=True) as cursor:
             self.rebuild_structures(cursor)
 
     def _prepare_database(self) -> None:
@@ -459,9 +449,12 @@ class ConcreteHaketiloState(base.HaketiloStateWithFields):
         try:
             cursor.execute(
                 '''
-                SELECT COUNT(name)
-                        FROM sqlite_master
-                        WHERE name = 'general' AND type = 'table';
+                SELECT
+                        COUNT(name)
+                FROM
+                        sqlite_master
+                WHERE
+                        name = 'general' AND type = 'table';
                 '''
             )
 
@@ -473,8 +466,10 @@ class ConcreteHaketiloState(base.HaketiloStateWithFields):
             else:
                 cursor.execute(
                     '''
-                    SELECT haketilo_version
-                            FROM general;
+                    SELECT
+                            haketilo_version
+                    FROM
+                            general;
                     '''
                 )
 
@@ -495,7 +490,7 @@ class ConcreteHaketiloState(base.HaketiloStateWithFields):
         malcontent_dir_path = self.store_dir / 'temporary_malcontent'
         files_by_sha256_path = malcontent_dir_path / 'file' / 'sha256'
 
-        with self.cursor(lock=True, transaction=True) as cursor:
+        with self.cursor(transaction=True) as cursor:
             for info_type in [item_infos.ResourceInfo, item_infos.MappingInfo]:
                 info: item_infos.AnyInfo
                 for info, definition in read_items(
@@ -517,8 +512,8 @@ class ConcreteHaketiloState(base.HaketiloStateWithFields):
                         cursor,
                         item_id,
                         repo_iteration_id,
-                        definition,
-                        info
+                        info.version,
+                        definition
                     )
 
                     if info_type is item_infos.MappingInfo:
@@ -574,16 +569,66 @@ class ConcreteHaketiloState(base.HaketiloStateWithFields):
                         )
 
     def rebuild_structures(self, cursor: sqlite3.Cursor) -> None:
+        assert self.connection.in_transaction
+
         resources = get_infos_of_type(cursor, item_infos.ResourceInfo)
         mappings  = get_infos_of_type(cursor, item_infos.MappingInfo)
 
-        payloads = compute_payloads(resources, mappings)
+        payloads = compute_payloads(resources.keys(), mappings.keys())
 
         payloads_data = {}
 
+        cursor.execute('DELETE FROM payloads;')
+
         for mapping_info, by_pattern in payloads.items():
             for num, (pattern, payload) in enumerate(by_pattern.items()):
-                payload_id = f'{num}@{mapping_info.identifier}'
+                print('adding payload')
+                cursor.execute(
+                    '''
+                    INSERT INTO payloads(
+                            mapping_item_id,
+                            pattern,
+	                    eval_allowed,
+                            cors_bypass_allowed
+                    )
+                    VALUES (?, ?, ?, ?);
+                    ''',
+                    (
+                        mappings[mapping_info],
+                        pattern.orig_url,
+                        payload.allows_eval,
+                        payload.allows_cors_bypass
+                    )
+                )
+
+                cursor.execute(
+                    '''
+                    SELECT
+                            payload_id
+                    FROM
+                            payloads
+                    WHERE
+                            mapping_item_id = ? AND pattern = ?;
+                    ''',
+                    (mappings[mapping_info], pattern.orig_url)
+                )
+
+                (payload_id_int,), = cursor.fetchall()
+
+                for res_num, resource_info in enumerate(payload.resources):
+                    cursor.execute(
+                        '''
+                        INSERT INTO resolved_depended_resources(
+                                payload_id,
+                                resource_item_id,
+                                idx
+                        )
+                        VALUES(?, ?, ?);
+                        ''',
+                        (payload_id_int, resources[resource_info], res_num)
+                    )
+
+                payload_id = str(payload_id_int)
 
                 ref = ConcretePayloadRef(payload_id, payload)
 
diff --git a/src/hydrilla/proxy/tables.sql b/src/hydrilla/proxy/tables.sql
index 53539a7..25493d3 100644
--- a/src/hydrilla/proxy/tables.sql
+++ b/src/hydrilla/proxy/tables.sql
@@ -141,10 +141,6 @@ CREATE TABLE item_versions(
         version             VARCHAR NOT NULL,
 	repo_iteration_id   INTEGER NOT NULL,
 	definition          TEXT    NOT NULL,
-	-- What privileges should be granted on pages where this
-	-- resource/mapping is used.
-	eval_allowed        BOOLEAN NOT NULL,
-        cors_bypass_allowed BOOLEAN NOT NULL,
 
         UNIQUE (item_id, version, repo_iteration_id),
 	-- Allow foreign key from "mapping_statuses".
@@ -157,15 +153,19 @@ CREATE TABLE item_versions(
 );
 
 CREATE TABLE payloads(
-        payload_id      INTEGER PRIMARY KEY,
+        payload_id          INTEGER PRIMARY KEY,
 
-        mapping_item_id INTEGER NOT NULL,
-        pattern         VARCHAR NOT NULL,
+        mapping_item_id     INTEGER NOT NULL,
+        pattern             VARCHAR NOT NULL,
+	-- What privileges should be granted on pages where this
+	-- resource/mapping is used.
+	eval_allowed        BOOLEAN NOT NULL,
+        cors_bypass_allowed BOOLEAN NOT NULL,
 
         UNIQUE (mapping_item_id, pattern),
 
 	FOREIGN KEY (mapping_item_id)
-                REFERENCES item_versions (versioned_item_id)
+                REFERENCES item_versions (item_version_id)
 		ON DELETE CASCADE
 );
 
@@ -179,9 +179,11 @@ CREATE TABLE resolved_depended_resources(
         PRIMARY KEY (payload_id, resource_item_id),
 
         FOREIGN KEY (payload_id)
-                REFERENCES payloads (payload_id),
+                REFERENCES payloads (payload_id)
+		ON DELETE CASCADE,
         FOREIGN KEY (resource_item_id)
                 REFERENCES item_versions (item_version_id)
+		ON DELETE CASCADE
 ) WITHOUT ROWID;
 
 -- CREATE TABLE resolved_required_mappings(