1 files changed, 44 insertions, 13 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)