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

1 files changed, 81 insertions, 14 deletions

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):