Upgrade script to create table subcategory_areas if not available

1 files changed, 24 insertions, 0 deletions

diff --git a/chimere/scripts/upgrade.py b/chimere/scripts/upgrade.py
index a850acf..b8cbec8 100755
--- a/chimere/scripts/upgrade.py
+++ b/chimere/scripts/upgrade.py
@@ -57,6 +57,30 @@ UNIQUE"
     transaction.commit_unless_managed()
     print " * urn field has now the constraint NOT NULL"
 
+# early versions before 0.1: subcategory_areas table doesn't exist
+
+query = """SELECT c.relname FROM pg_class c
+WHERE c.relname = 'subcategory_areas';"""
+cursor.execute(query)
+transaction.commit_unless_managed()
+
+row = cursor.fetchone()
+if not row:
+    query_create = """
+CREATE TABLE "subcategory_areas" (
+    "id" serial NOT NULL PRIMARY KEY,
+    "subcategory_id" integer NOT NULL REFERENCES "main_subcategory" ("id")
+                                                  DEFERRABLE INITIALLY DEFERRED,
+    "area_id" integer NOT NULL REFERENCES "main_area" ("id")
+                                                  DEFERRABLE INITIALLY DEFERRED,
+    UNIQUE ("subcategory_id", "area_id"));
+
+"""
+    cursor.execute(query_create)
+    transaction.commit_unless_managed()
+    print " * subcategory_areas created"
+
+
 # early versions before 0.1: save area with wrong SRID
 # only errors with default SRID is managed adapt the script for your SRID