WIP - optimize record relations

7 files changed, 390 insertions, 5 deletions

diff --git a/.gitignore b/.gitignore
index a9368a7a7..904b731d7 100644
--- a/.gitignore
+++ b/.gitignore
@@ -40,3 +40,7 @@ drassm_app
 .code-workspace
 .vscode/*
 example_project/local_settings.py.old
+scripts
+.project
+.pydevproject
+.settings
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
diff --git a/docs/fr/source/annexe-tech-5-manage-commands.rst b/docs/fr/source/annexe-tech-5-manage-commands.rst
new file mode 100644
index 000000000..463ae8bde
--- /dev/null
+++ b/docs/fr/source/annexe-tech-5-manage-commands.rst
@@ -0,0 +1,19 @@
+.. -*- coding: utf-8 -*-
+
+.. _annexe-technique-5-manage-commands:
+
+================================================
+Annexe technique 5 - Commandes de gestion en CLI
+================================================
+
+:Auteurs: Étienne Loks
+:Date: 2021-06-14
+:Copyright: CC-BY 3.0
+:Ishtar Version: v3.1.0
+
+----------------------------------
+
+relations_update_cache_tables
+-----------------------------
+
+
diff --git a/ishtar_common/management/commands/relations_update_cache_tables.py b/ishtar_common/management/commands/relations_update_cache_tables.py
new file mode 100644
index 000000000..ab7f134ff
--- /dev/null
+++ b/ishtar_common/management/commands/relations_update_cache_tables.py
@@ -0,0 +1,83 @@
+#!/usr/bin/env python
+# -*- coding: utf-8 -*-
+# Copyright (C) 2013-2018 Étienne Loks  <etienne.loks_AT_peacefrogsDOTnet>
+
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero 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 Affero General Public License for more details.
+
+# You should have received a copy of the GNU Affero General Public License
+# along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+# See the file COPYING for details.
+
+import sys
+
+from django.core.management.base import BaseCommand
+
+from django.apps import apps
+
+
+APPS = ['ishtar_common', 'archaeological_operations',
+        'archaeological_context_records', 'archaeological_finds',
+        'archaeological_warehouse']
+
+
+class Command(BaseCommand):
+    args = ''
+    help = 'Regenerate geo, cached labels and search vectors'
+
+    def add_arguments(self, parser):
+        parser.add_argument('app_name', nargs='?', default=None,
+                            choices=APPS)
+        parser.add_argument('model_name', nargs='?', default=None)
+        parser.add_argument(
+            '--quiet', dest='quiet', action='store_true',
+            help='Quiet output')
+
+    def handle(self, *args, **options):
+        quiet = options['quiet']
+        limit = options['app_name']
+        model_name = options['model_name']
+        if model_name:
+            model_name = model_name.lower()
+        for app in APPS:
+            if limit and app != limit:
+                continue
+            if not quiet:
+                print("* app: {}".format(app))
+            for model in apps.get_app_config(app).get_models():
+                if model_name and model.__name__.lower() != model_name:
+                    continue
+                if model.__name__.startswith('Historical'):
+                    continue
+                if not bool(
+                        [k for k in dir(model)
+                         if k.startswith('_generate_') or
+                            k == "search_vector"]):
+                    continue
+                msg = "-> processing {}: ".format(model._meta.verbose_name)
+                ln = model.objects.count()
+                for idx, obj_id in enumerate(model.objects.values('pk').all()):
+                    obj = model.objects.get(pk=obj_id['pk'])
+                    obj.skip_history_when_saving = True
+                    obj._no_move = True
+                    if hasattr(obj, "point_source") and obj.point_source in (
+                            "M", "T"):
+                        obj.point = None
+                        obj.point_2d = None
+                        obj.x = None
+                        obj.y = None
+                    cmsg = "\r{} {}/{}".format(msg, idx + 1, ln)
+                    if not quiet:
+                        sys.stdout.write(cmsg)
+                        sys.stdout.flush()
+                    obj.save()
+                if not quiet:
+                    sys.stdout.write("\n")
diff --git a/ishtar_common/migrations/0215_ishtarsiteprofile_parent_relations_engine.py b/ishtar_common/migrations/0215_ishtarsiteprofile_parent_relations_engine.py
new file mode 100644
index 000000000..acf410837
--- /dev/null
+++ b/ishtar_common/migrations/0215_ishtarsiteprofile_parent_relations_engine.py
@@ -0,0 +1,20 @@
+# -*- coding: utf-8 -*-
+# Generated by Django 1.11.28 on 2021-06-14 19:06
+from __future__ import unicode_literals
+
+from django.db import migrations, models
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ('ishtar_common', '0214_auto_20210308_1628'),
+    ]
+
+    operations = [
+        migrations.AddField(
+            model_name='ishtarsiteprofile',
+            name='parent_relations_engine',
+            field=models.CharField(choices=[('V', 'SQL views'), ('T', 'Cache tables')], default='V', help_text='If you experience performance problems with complex relations (for instance: complex statigraphic relations), set it to "Cache tables" in order to use static cache tables. Do not forget to update theses table with the "relations_update_cache_tables" manage.py command.', max_length=1, verbose_name='Parent relations engine'),
+        ),
+    ]
diff --git a/ishtar_common/models.py b/ishtar_common/models.py
index 94e02aafe..fdeba5f26 100644
--- a/ishtar_common/models.py
+++ b/ishtar_common/models.py
@@ -62,6 +62,7 @@ from django.core.exceptions import (
 from django.core.files.base import ContentFile
 from django.core.files.uploadedfile import SimpleUploadedFile
 from django.core.urlresolvers import reverse
+from django.db import connection
 from django.db.models import Q, Max, Count
 from django.db.models.signals import post_save, post_delete, m2m_changed
 from django.db.utils import DatabaseError
@@ -76,6 +77,8 @@ from ishtar_common.utils import (
     get_current_profile,
     duplicate_item,
     get_image_path,
+    serialize_args_for_tasks,
+    task,
 )
 from ishtar_common.utils_secretary import IshtarSecretaryRenderer
 
@@ -825,6 +828,80 @@ def post_delete_record_relation(sender, instance, **kwargs):
         q.delete()
 
 
+@task()
+def relation_view_update(sender, kwargs):
+    if isinstance(sender, (tuple, list)):
+        sender = apps.get_model(*sender)
+    sender._update(kwargs["item_id"])
+
+
+class RelationsViews(models.Model):
+    CREATE_SQL = ""  # SQL view creation
+    DELETE_SQL = ""  # SQL view deletion
+    CREATE_TABLE_SQL = ""  # SQL table creation
+
+    class Meta:
+        managed = False
+        abstract = True
+
+    @classmethod
+    def _update(cls, item_id):
+        raise NotImplemented()
+
+    @classmethod
+    def update(cls, item_id):
+        profile = get_current_profile()
+        if profile.parent_relations_engine == "V":
+            return
+        if not settings.USE_BACKGROUND_TASK:
+            return relation_view_update(cls, {"item_id": item_id})
+        else:
+            sender, kwargs = serialize_args_for_tasks(
+                cls, None, {"item_id": item_id}
+            )
+            return relation_view_update.delay(sender, kwargs)
+
+    @classmethod
+    def create_table(cls):
+        raise NotImplemented()
+
+    @classmethod
+    def check_engine(cls):
+        """
+        Check view or table properly created with settings on the profile
+        :return: True if table or view updated
+        """
+        assert cls.CREATE_SQL
+        assert cls.DELETE_SQL
+        assert cls.CREATE_TABLE_SQL
+        profile = get_current_profile(force=True)
+        table_type = ''
+        with connection.cursor() as cursor:
+            q = "select table_type from information_schema.tables WHERE " \
+                "table_name=%s;"
+            cursor.execute(q, [cls._meta.db_table])
+            q = cursor.fetchall()
+            if q:
+                table_type = q[0][0]
+
+            if profile.parent_relations_engine == "V":
+                if table_type == 'VIEW':
+                    return
+                elif 'TABLE' in table_type:
+                    q = "DROP TABLE %s"
+                    cursor.execute(q, [cls._meta.db_table])
+                cursor.execute(cls.CREATE_SQL)
+                return True
+
+            if profile.parent_relations_engine == "T":
+                if 'TABLE' in table_type:
+                    return
+                elif table_type == 'VIEW':
+                    cursor.execute(cls.DELETE_SQL)
+                cursor.execute(cls.CREATE_TABLE_SQL)
+                return True
+
+
 class SearchQuery(models.Model):
     label = models.TextField(_("Label"), blank=True, default="")
     query = models.TextField(_("Query"), blank=True, default="")
@@ -918,6 +995,20 @@ class IshtarSiteProfile(models.Model, Cached):
         _("Container - calculate weight only when all find has a weight"),
         default=False,
     )
+    parent_relations_engine = models.CharField(
+        _("Parent relations engine"),
+        choices=(
+            ("V", _("SQL views")),
+            ("T", _("Cache tables")),
+        ),
+        default="V",
+        max_length=1,
+        help_text=_("If you experience performance problems with complex relations "
+                    "(for instance: complex statigraphic relations), set it to "
+                    "\"Cache tables\" in order to use static cache tables. Do not "
+                    "forget to update theses table with the "
+                    "\"relations_update_cache_tables\" manage.py command.")
+    )
     config = models.CharField(
         _("Alternate configuration"),
         max_length=200,