WIP - optimize record relations

1 files changed, 91 insertions, 0 deletions

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,