Treatments: more work on db treatments efficiency (refs #3108)

6 files changed, 146 insertions, 31 deletions

diff --git a/archaeological_finds/migrations/0053_view_find_treatments.py b/archaeological_finds/migrations/0053_view_find_treatments.py
index ca70a7717..1e06e490b 100644
--- a/archaeological_finds/migrations/0053_view_find_treatments.py
+++ b/archaeological_finds/migrations/0053_view_find_treatments.py
@@ -1,24 +1,48 @@
 # -*- coding: utf-8 -*-
-import datetime
 from south.db import db
 from south.v2 import SchemaMigration
-from django.db import models
 
 
 class Migration(SchemaMigration):
 
     def forwards(self, orm):
         sql = """
-    CREATE VIEW find_treatments_tree AS
+    CREATE VIEW find_downtreatments_tree AS
         WITH RECURSIVE rel_tree AS (
           SELECT id AS find_id, upstream_treatment_id, downstream_treatment_id,
               1 AS level,
-              array[downstream_treatment_id] AS path_info
+              array[upstream_treatment_id] AS path_info
             FROM archaeological_finds_find
-            WHERE downstream_treatment_id is null
-          union all
+            WHERE upstream_treatment_id is null
+          UNION ALL
           SELECT c.id AS find_id, c.upstream_treatment_id,
             c.downstream_treatment_id,
+            p.level + 1, p.path_info||c.upstream_treatment_id
+            FROM archaeological_finds_find c
+          JOIN rel_tree p
+            ON c.upstream_treatment_id = p.downstream_treatment_id
+        )
+        SELECT DISTINCT find_id, path_info, level
+          FROM rel_tree ORDER BY find_id;
+
+    CREATE VIEW find_downtreatments AS
+        SELECT DISTINCT find_id,
+            path_info[nb] AS treatment_id, level - nb + 1 AS treatment_nb
+          FROM (SELECT *, generate_subscripts(path_info, 1) AS nb
+                FROM find_downtreatments_tree) y
+         WHERE path_info[nb] is not NULL
+        ORDER BY find_id, treatment_id;
+
+    CREATE VIEW find_uptreatments_tree AS
+        WITH RECURSIVE rel_tree AS (
+          SELECT id AS find_id, downstream_treatment_id, upstream_treatment_id,
+              1 AS level,
+              array[downstream_treatment_id] AS path_info
+            FROM archaeological_finds_find
+            WHERE downstream_treatment_id is null
+          UNION ALL
+          SELECT c.id AS find_id, c.downstream_treatment_id,
+            c.upstream_treatment_id,
             p.level + 1, p.path_info||c.downstream_treatment_id
             FROM archaeological_finds_find c
           JOIN rel_tree p
@@ -26,24 +50,31 @@ class Migration(SchemaMigration):
         )
         SELECT DISTINCT find_id, path_info, level
           FROM rel_tree ORDER BY find_id;
-    CREATE RULE find_treatments_tree_delete
-                        AS ON DELETE TO find_treatments_tree DO INSTEAD();
 
-    CREATE VIEW find_treatments AS
+    CREATE VIEW find_uptreatments AS
         SELECT DISTINCT find_id,
             path_info[nb] AS treatment_id, level - nb + 1 AS treatment_nb
           FROM (SELECT *, generate_subscripts(path_info, 1) AS nb
-                FROM find_treatments_tree) y
+                FROM find_uptreatments_tree) y
          WHERE path_info[nb] is not NULL
         ORDER BY find_id, treatment_id;
-    CREATE RULE find_treatments_delete
-                        AS ON DELETE TO find_treatments DO INSTEAD();
+
+    CREATE VIEW find_treatments AS
+        SELECT find_id, treatment_id, treatment_nb, TRUE as upstream
+         FROM find_uptreatments
+        UNION
+        SELECT find_id, treatment_id, treatment_nb, FALSE as upstream
+         FROM find_downtreatments
+        ORDER BY find_id, treatment_id, upstream;
         """
         db.execute(sql)
 
     def backwards(self, orm):
         sql = """DROP VIEW find_treatments;
-        DROP VIEW find_treatments_tree;
+        DROP VIEW find_uptreatments;
+        DROP VIEW find_uptreatments_tree;
+        DROP VIEW find_downtreatments;
+        DROP VIEW find_downtreatments_tree;
         """
         db.execute(sql)
 
diff --git a/archaeological_finds/models.py b/archaeological_finds/models.py
index a5168eb0e..d1f194727 100644
--- a/archaeological_finds/models.py
+++ b/archaeological_finds/models.py
@@ -758,18 +758,74 @@ class Treatment(BaseHistorizedItem, OwnPerms):
                 basket.items.add(new)
 
 
-class FindTreaments(models.Model):
+class AbsFindTreatments(object):
+    find = models.ForeignKey(Find, verbose_name=_(u"Find"),
+                             related_name='treatments')
+    treatment = models.ForeignKey(Treatment, verbose_name=_(u"Treatment"),
+                                  primary_key=True)
+    # primary_key is set to prevent django to ask for an id column
+    # treatment is not a primary key
+    treatment_nb = models.IntegerField(_(u"Order"))
+    TABLE_COLS = ['treatment__treatment_type',
+                  'treatment__start_date', 'treatment__end_date',
+                  'treatment__location', 'treatment__person',
+                  'treatment_nb']
+
+    class Meta:
+        abstract = True
+
+    def __unicode__(self):
+        return u"{} - {} [{}]".format(
+            self.find, self.treatment, self.treatment_nb)
+
+
+class FindDownstreamTreatments(AbsFindTreatments):
     """
-    CREATE VIEW find_treatments_tree AS
+    CREATE VIEW find_downtreatments_tree AS
         WITH RECURSIVE rel_tree AS (
           SELECT id AS find_id, upstream_treatment_id, downstream_treatment_id,
               1 AS level,
-              array[downstream_treatment_id] AS path_info
+              array[upstream_treatment_id] AS path_info
             FROM archaeological_finds_find
-            WHERE downstream_treatment_id is null
+            WHERE upstream_treatment_id is null
           UNION ALL
           SELECT c.id AS find_id, c.upstream_treatment_id,
             c.downstream_treatment_id,
+            p.level + 1, p.path_info||c.upstream_treatment_id
+            FROM archaeological_finds_find c
+          JOIN rel_tree p
+            ON c.upstream_treatment_id = p.downstream_treatment_id
+        )
+        SELECT DISTINCT find_id, path_info, level
+          FROM rel_tree ORDER BY find_id;
+
+    CREATE VIEW find_downtreatments AS
+        SELECT DISTINCT find_id,
+            path_info[nb] AS treatment_id, level - nb + 1 AS treatment_nb
+          FROM (SELECT *, generate_subscripts(path_info, 1) AS nb
+                FROM find_downtreatments_tree) y
+         WHERE path_info[nb] is not NULL
+        ORDER BY find_id, treatment_id;
+    """
+
+    class Meta:
+        managed = False
+        db_table = 'find_downtreatments'
+        unique_together = ('find', 'treatment')
+
+
+class FindUpstreamTreatments(AbsFindTreatments):
+    """
+    CREATE VIEW find_uptreatments_tree AS
+        WITH RECURSIVE rel_tree AS (
+          SELECT id AS find_id, downstream_treatment_id, upstream_treatment_id,
+              1 AS level,
+              array[downstream_treatment_id] AS path_info
+            FROM archaeological_finds_find
+            WHERE downstream_treatment_id is null
+          UNION ALL
+          SELECT c.id AS find_id, c.downstream_treatment_id,
+            c.upstream_treatment_id,
             p.level + 1, p.path_info||c.downstream_treatment_id
             FROM archaeological_finds_find c
           JOIN rel_tree p
@@ -778,26 +834,37 @@ class FindTreaments(models.Model):
         SELECT DISTINCT find_id, path_info, level
           FROM rel_tree ORDER BY find_id;
 
-    CREATE VIEW find_treatments AS
+    CREATE VIEW find_uptreatments AS
         SELECT DISTINCT find_id,
             path_info[nb] AS treatment_id, level - nb + 1 AS treatment_nb
           FROM (SELECT *, generate_subscripts(path_info, 1) AS nb
-                FROM find_treatments_tree) y
+                FROM find_uptreatments_tree) y
          WHERE path_info[nb] is not NULL
         ORDER BY find_id, treatment_id;
     """
-    find = models.ForeignKey(Find, verbose_name=_(u"Find"))
-    treatment = models.ForeignKey(Treatment, verbose_name=_(u"Treatment"))
-    treatment_nb = models.IntegerField(
-        _(u"Digit of the treatment for this find"))
 
     class Meta:
         managed = False
-        db_table = 'find_treatments'
+        db_table = 'find_uptreatments'
+        unique_together = ('find', 'treatment')
 
-    def __unicode__(self):
-        return u"{} - {} [{}]".format(
-            self.find, self.treatment, self.treatment_nb)
+
+class FindTreatments(AbsFindTreatments):
+    """
+    CREATE VIEW find_treatments AS
+        SELECT find_id, treatment_id, treatment_nb, TRUE as upstream
+         FROM find_uptreatments
+        UNION
+        SELECT find_id, treatment_id, treatment_nb, FALSE as upstream
+         FROM find_downtreatments
+        ORDER BY find_id, treatment_id, upstream;
+    """
+    upstream = models.BooleanField(_(u"Is upstream"))
+
+    class Meta:
+        managed = False
+        db_table = 'find_treatments'
+        unique_together = ('find', 'treatment')
 
 
 class TreatmentSource(Source):
diff --git a/archaeological_finds/templates/ishtar/sheet_find.html b/archaeological_finds/templates/ishtar/sheet_find.html
index 14e88e720..741eb0d16 100644
--- a/archaeological_finds/templates/ishtar/sheet_find.html
+++ b/archaeological_finds/templates/ishtar/sheet_find.html
@@ -1,5 +1,5 @@
 {% extends "ishtar/sheet.html" %}
-{% load i18n window_field from_dict link_to_window humanize %}
+{% load i18n window_field from_dict link_to_window window_tables humanize %}
 
 {% block head_sheet %}
 {{block.super}}
@@ -73,7 +73,7 @@
 {% field_li_multiple "Object types" item.object_types %}
 {% field_li_multiple "Integrity" item.integrities %}
 {% field_li_multiple "Remarkability" item.remarkabilities %}
-{% field_li "Estimated value" item.estimated_value|intcomma '' ' '|add:CURRENCY %}
+{% field_li "Estimated value" item.estimated_value|default_if_none:''|intcomma '' ' '|add:CURRENCY %}
 {% if item.CHECK_DICT %}
 {% field_li "Checked" item.checked|from_dict:item.CHECK_DICT %}
 {% endif%}
@@ -83,6 +83,9 @@
 {% field_li "Container" item.container %}
 </ul>
 
+{% if item.upstream_treatment or item.downstream_treatment %}
+<h3>{% trans "Associated base finds"%}</h3>
+
 {% if item.upstream_treatment %}
 <table class='simple' id='{{window_id}}-upstream'>
   <caption>{% trans "Upstream treatment" %}</caption>
@@ -129,6 +132,13 @@
 </table>
 {% endif %}
 
+{% comment %}
+{% trans "Upstream treatments" as treatments  %}
+{% dynamic_table_document treatments 'finds_treatments' 'find_id' item.pk '' output %}
+{% endcomment %}
+
+{% endif %}
+
 <h3>{% trans "Associated base finds"%}</h3>
 
 {% for base_find in item.base_finds.all %}
diff --git a/archaeological_finds/urls.py b/archaeological_finds/urls.py
index 300694268..034ec679f 100644
--- a/archaeological_finds/urls.py
+++ b/archaeological_finds/urls.py
@@ -90,6 +90,10 @@ urlpatterns = patterns(
             views.DeleteFindBasketView.as_view()), name='delete_findbasket'),
     url(r'treatment_creation/(?P<step>.+)?$',
         views.treatment_creation_wizard, name='treatment_creation'),
+    url(r'get-treatment/(?P<type>.+)?$', views.get_treatment,
+        name='get-treatment'),
+    url(r'get-treatment-full/(?P<type>.+)?$', views.get_treatment,
+        name='get-treatment-full', kwargs={'full': True}),
 )
 
 urlpatterns += patterns(
diff --git a/archaeological_finds/views.py b/archaeological_finds/views.py
index 6bad075b5..783b336f2 100644
--- a/archaeological_finds/views.py
+++ b/archaeological_finds/views.py
@@ -330,7 +330,8 @@ class DeleteFindBasketView(IshtarMixin, LoginRequiredMixin, FormView):
         return HttpResponseRedirect(self.get_success_url())
 
 get_treatment = get_item(
-    models.Treatment, 'get_treatment', 'treatment')
+    models.FindTreatments, 'get_treatment', 'treatment',
+    extra_request_keys={'find_id': 'find_id'})
 
 treatment_creation_wizard = TreatmentWizard.as_view([
     ('basetreatment-treatment_creation', BaseTreatmentForm),
diff --git a/ishtar_common/templatetags/window_tables.py b/ishtar_common/templatetags/window_tables.py
index 6710672e1..03365c207 100644
--- a/ishtar_common/templatetags/window_tables.py
+++ b/ishtar_common/templatetags/window_tables.py
@@ -16,7 +16,7 @@ from archaeological_files.models import File
 from archaeological_operations.models import OperationSource, Operation
 from archaeological_context_records.models import ContextRecord, \
     ContextRecordSource
-from archaeological_finds.models import Find, FindSource
+from archaeological_finds.models import Find, FindSource, FindTreatments
 
 register = template.Library()
 
@@ -43,6 +43,8 @@ ASSOCIATED_MODELS['finds_for_ope'] = (
     Find, 'get-find-for-ope', 'get-find-full')
 ASSOCIATED_MODELS['finds_docs'] = (
     FindSource, 'get-findsource', 'get-findsource-full')
+ASSOCIATED_MODELS['finds_treatments'] = (
+    FindTreatments, 'get-treatment', 'get-treatment-full')
 
 
 @register.simple_tag(takes_context=True)