diff options
| -rw-r--r-- | .gitignore | 1 | ||||
| -rw-r--r-- | chimere/migrations/0015_force_creation_of_aggregated.py | 62 |
2 files changed, 63 insertions, 0 deletions
@@ -17,3 +17,4 @@ chimere/static/leaflet/ chimere/migrations_saclay/ chimere_nef /static +ppm diff --git a/chimere/migrations/0015_force_creation_of_aggregated.py b/chimere/migrations/0015_force_creation_of_aggregated.py new file mode 100644 index 0000000..8f2cc1f --- /dev/null +++ b/chimere/migrations/0015_force_creation_of_aggregated.py @@ -0,0 +1,62 @@ +# -*- coding: utf-8 -*- +# Generated by Django 1.11.5 on 2018-03-17 12:29 +from __future__ import unicode_literals + +from django.db import migrations + +SQL_POLY = """CREATE OR REPLACE VIEW chimere_aggregated_polygons + (id, subcategory_id, status, polygon) AS + SELECT + row_number() OVER ( + ORDER BY "chimere_polygon_categories"."subcategory_id", + "chimere_polygon"."status"), + "chimere_polygon_categories"."subcategory_id", + "chimere_polygon"."status", + ST_Multi(ST_Collect("chimere_polygon"."polygon")) + + FROM "chimere_polygon" + + LEFT OUTER JOIN "chimere_polygon_categories" ON ( + "chimere_polygon"."id" = "chimere_polygon_categories"."polygon_id") + WHERE ("chimere_polygon"."start_date" IS NULL + OR ("chimere_polygon"."start_date" <= CURRENT_DATE + AND "chimere_polygon"."start_date" >= CURRENT_DATE) + OR ("chimere_polygon"."start_date" <= CURRENT_DATE + AND "chimere_polygon"."end_date" >= CURRENT_DATE)) + GROUP BY "chimere_polygon_categories"."subcategory_id", + "chimere_polygon"."status"; + """ + +SQL_ROUTE = """CREATE OR REPLACE VIEW chimere_aggregated_routes + (id, subcategory_id, status, route) AS + SELECT + row_number() OVER ( + ORDER BY "chimere_route_categories"."subcategory_id", + "chimere_route"."status"), + "chimere_route_categories"."subcategory_id", "chimere_route"."status", + ST_Multi(ST_Collect("chimere_route"."route")) + + FROM "chimere_route" + + LEFT OUTER JOIN "chimere_route_categories" ON ( + "chimere_route"."id" = "chimere_route_categories"."route_id") + WHERE ("chimere_route"."start_date" IS NULL + OR ("chimere_route"."start_date" <= CURRENT_DATE + AND "chimere_route"."start_date" >= CURRENT_DATE) + OR ("chimere_route"."start_date" <= CURRENT_DATE + AND "chimere_route"."end_date" >= CURRENT_DATE)) + GROUP BY "chimere_route_categories"."subcategory_id", + "chimere_route"."status"; + """ + + +class Migration(migrations.Migration): + + dependencies = [ + ('chimere', '0014_auto_20180313_1033'), + ] + + operations = [ + migrations.RunSQL(SQL_POLY), + migrations.RunSQL(SQL_ROUTE), + ] |