WIP - optimize record relations

2 files changed, 173 insertions, 5 deletions

diff --git a/archaeological_context_records/models.py b/archaeological_context_records/models.py
index d62dde4be..96ba16935 100644
--- a/archaeological_context_records/models.py
+++ b/archaeological_context_records/models.py
@@ -35,6 +35,7 @@ from ishtar_common.utils import (
     cached_label_changed,
     m2m_historization_changed,
     post_save_geo,
+    task
 )
 
 from ishtar_common.models import (
@@ -61,6 +62,7 @@ from ishtar_common.models import (
     DocumentItem,
     MainItem,
     QuickAction,
+    RelationsViews
 )
 from ishtar_common.models_common import HistoricalRecords
 from archaeological_operations.models import (
@@ -1282,7 +1284,7 @@ class RecordRelationView(models.Model):
         return '{} "{}"'.format(self.relation_type, self.right_record)
 
 
-class ContextRecordTree(models.Model):
+class ContextRecordTree(RelationsViews):
     CREATE_SQL = """
     CREATE VIEW cr_parent_relation_id AS
         SELECT id
@@ -1354,6 +1356,19 @@ class ContextRecordTree(models.Model):
     DROP VIEW IF EXISTS context_records_tree;
     DROP VIEW IF EXISTS cr_parent_relation_id;
     """
+
+    CREATE_TABLE_SQL = """
+    CREATE TABLE {table} (
+        key                 varchar(100) PRIMARY KEY,
+        cr_id               integer NOT NULL,
+        cr_parent_id        integer NOT NULL,
+        CONSTRAINT fk1_{table} FOREIGN KEY(cr_id)
+            REFERENCES {fk_table}(id),
+        CONSTRAINT fk2_{table} FOREIGN KEY(cr_parent_id)
+            REFERENCES {fk_table}(id)
+    );""".format(table="context_records_tree",
+                 fk_table="archaeological_context_records_contextrecord")
+
     key = models.TextField(primary_key=True)
     cr = models.ForeignKey(
         "archaeological_context_records.ContextRecord",
@@ -1369,3 +1384,100 @@ class ContextRecordTree(models.Model):
     class Meta:
         managed = False
         db_table = "context_records_tree"
+
+    @classmethod
+    def _save_tree(cls, tree):
+        keys = []
+        print("tree", tree)
+        for idx, parent_id in enumerate(tree[:-1]):
+            for child_id in tree[idx:]:
+                if child_id != parent_id:
+                    cls.objects.get_or_create(
+                        key=f"{child_id}_{parent_id}",
+                        cr_id=child_id, cr_parent_id=parent_id
+                    )
+                    keys.append((child_id, parent_id))
+        return keys
+
+    @classmethod
+    def _update_child(cls, parent_id, tree, rel_types):
+        whole_tree = set()
+        childs = RecordRelations.objects.values_list(
+            "left_record_id", flat=True).filter(
+            right_record_id=parent_id, relation_type_id__in=rel_types)
+        for c in childs[:]:
+            if c in tree:  # cyclic
+                childs.pop(c)
+        #print("childs", parent_id, childs)
+        if not childs:  # last leaf in the tree
+            return cls._save_tree(tree)
+        for c in childs:
+            whole_tree.update(cls._update_child(c, tree[:] + [c], rel_types))
+        return whole_tree
+
+    @classmethod
+    def _get_parent_trees(cls, child_id, trees, rel_types):
+        parents = RecordRelations.objects.values_list(
+            "right_record_id", flat=True).filter(
+            left_record_id=child_id, relation_type_id__in=rel_types)
+        if not parents:
+            return trees
+        new_trees = []
+        for p in parents:
+            if p == child_id or any(1 for tree in trees if p in tree):  # cyclic
+                continue
+            c_trees = list(map(lambda x: x + [p], trees))
+            new_trees += cls._get_parent_trees(p, c_trees, rel_types)
+        return new_trees
+
+    @classmethod
+    def _update(cls, item_id, cascade=True):
+        # update the whole tree
+        rel_types = RelationType.objects.filter(
+            logical_relation__in=('included', 'equal')).values_list("id", flat=True)
+
+        # get first parents
+        parent_ids = [
+            tree[-1] for tree in cls._get_parent_trees(item_id, [[item_id]], rel_types)]
+        """
+        parent_ids = []
+        current_ids = [item_id]
+        while current_ids:
+            new_ids = []
+            for current_id in current_ids:
+                parents = RecordRelations.objects.values_list(
+                    "right_record_id", flat=True).filter(
+                    left_record_id=current_id, relation_type_id__in=rel_types)
+                if not parents:
+                    continue
+                for p in parents[:]:
+                    if p == current_id or p in parent_ids:  # cyclic
+                        parents.pop(p)
+                parent_ids += parents
+                new_ids += parents
+            current_ids = new_ids
+        """
+        def get_cr(idx):
+            return ContextRecord.objects.get(pk=idx)
+        print(get_cr(item_id))
+        if not parent_ids:
+            parent_ids = [item_id]
+        print("parents", [get_cr(p) for p in parent_ids])
+
+        # get all child for parents and save trees
+        all_relations = set()
+        for parent_id in parent_ids:
+            tree = [parent_id]
+            all_relations.update(cls._update_child(parent_id, tree, rel_types))
+        #print(all_relations)
+
+        # delete old relations
+        for item_id in set([c for c, __ in all_relations] +
+                           [p for p, __ in all_relations]):
+            for rel in cls.objects.filter(cr_id=item_id).all():
+                if (rel.cr_id, rel.cr_parent_id) not in all_relations:
+                    rel.delete()
+            for rel in cls.objects.filter(cr_parent_id=item_id).all():
+                if (rel.cr_id, rel.cr_parent_id) not in all_relations:
+                    rel.delete()
+
diff --git a/archaeological_context_records/tests.py b/archaeological_context_records/tests.py
index 64950bd91..1f919b5d9 100644
--- a/archaeological_context_records/tests.py
+++ b/archaeological_context_records/tests.py
@@ -884,9 +884,8 @@ class RecordRelationsTest(ContextRecordInit, TestCase):
     model = models.ContextRecord
 
     def setUp(self):
-        # two different context records
-        self.create_context_record({"label": "CR 1"})
-        self.create_context_record({"label": "CR 2"})
+        for idx in range(1, 11):
+            self.create_context_record({"label": f"CR {idx}"})
 
     def test_relations(self):
         sym_rel_type = models.RelationType.objects.create(
@@ -933,7 +932,7 @@ class RecordRelationsTest(ContextRecordInit, TestCase):
 
         # for non-symmetrical relation, adding one relation automatically
         # adds the inverse
-        rel = models.RecordRelations.objects.create(
+        models.RecordRelations.objects.create(
             left_record=cr_1, right_record=cr_2, relation_type=rel_type_1
         )
         self.assertEqual(
@@ -943,6 +942,63 @@ class RecordRelationsTest(ContextRecordInit, TestCase):
             1,
         )
 
+    def test_relation_view(self):
+        ## TODO : branches multiples
+        ## TODO : cyclique
+        profile = get_current_profile()
+        profile.parent_relations_engine = "V"
+        profile.save()
+        profile = get_current_profile(force=True)
+        models.ContextRecordTree.check_engine()
+        crs = self.context_records
+        rel_type_1 = models.RelationType.objects.create(
+            symmetrical=False, txt_idx="rel_1",
+            logical_relation='included'
+        )
+        """
+        6  7  8  9  10 
+        |  |  |  |   |
+        -------  -----
+           |       |
+           4       5
+           |       |
+           ---------
+               |
+               3
+               |
+           ---------
+           |       |
+           1       2
+        """
+        relations = (
+            (1, 3), (2, 3), (3, 4), (3, 5), (4, 6), (4, 7), (4, 8),
+            (5, 9), (5, 10)
+        )
+        for child_idx, parent_idx in relations:
+            models.RecordRelations.objects.create(
+                left_record=crs[child_idx - 1],
+                right_record=crs[parent_idx - 1],
+                relation_type=rel_type_1
+            )
+        q = models.ContextRecordTree.objects.filter(
+            cr_parent_id=crs[2].pk, cr_id=crs[0].pk)
+        self.assertGreaterEqual(q.count(), 1)
+
+        self.assertIsNone(models.ContextRecordTree.check_engine())  # no change
+        profile.parent_relations_engine = "T"
+        profile.save()
+        profile = get_current_profile(force=True)
+        self.assertTrue(models.ContextRecordTree.check_engine())  # change to table
+        q = models.ContextRecordTree.objects.filter(cr=crs[0], cr_parent=crs[1])
+        self.assertEqual(q.count(), 0)  # empty table
+        print("~~~ CR1 - child of all")
+        models.ContextRecordTree.update(crs[0].id)
+        print("~~~ CR2")
+        models.ContextRecordTree.update(crs[1].id)
+        print("~~~ CR3 - parent of all")
+        models.ContextRecordTree.update(crs[2].id)
+        # vérifier cr1 -> cr3
+
 
 class ContextRecordWizardCreationTest(WizardTest, ContextRecordInit, TestCase):
     fixtures = OPERATION_TOWNS_FIXTURES