--- a/flys-backend/contrib/shpimporter/dgm.py	Fri Mar 08 17:44:12 2013 +0100
+++ b/flys-backend/contrib/shpimporter/dgm.py	Fri Mar 08 18:15:48 2013 +0100
@@ -2,16 +2,13 @@
 
 import codecs
 import utils
+import datetime
 
 def latin(string):
     return unicode(string, "latin1")
 
 # <dbfield> : (<csvfield>, conversion function)
 DGM_MAP = {
-    "lower"           : ("km_von", lambda x: float(x)),
-    "upper"           : ("km_bis", lambda x: float(x)),
-    "year_from"       : "Jahr_von",
-    "year_to"         : "Jahr_bis",
     "projection"      : "Projektion",
     "elevation_state" : latin("Höhenstatus"),
     "format"          : "Format",
@@ -23,10 +20,81 @@
     "path"            : ("Pfad_Bestand", lambda x: x),
     }
 
-SQL_INSERT_DGT = "INSERT INTO dem (river_id, name, " + ", ".join(DGM_MAP.keys()) + \
-        ") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
-SQL_INSERT_DGT_ORA = "INSERT INTO dem (river_id, name, " + ", ".join(DGM_MAP.keys()) + \
-        ") VALUES (:s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s)"
+SQL_INSERT_DGT = "INSERT INTO dem (river_id, name," \
+        " time_interval_id, range_id, " + ", ".join(DGM_MAP.keys()) + \
+        ") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
+SQL_INSERT_DGT_ORA = "INSERT INTO dem (river_id, name," \
+        " time_interval_id, range_id, " + ", ".join(DGM_MAP.keys()) + \
+        ") VALUES (:s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s)"
+SQL_SELECT_TIME_ID = """
+SELECT id FROM time_intervals WHERE start_time = %s AND stop_time = %s
+"""
+SQL_INSERT_TIME_ID = """
+INSERT INTO time_intervals (id, start_time, stop_time) VALUES (%s, %s, %s)
+"""
+SQL_SELECT_TIME_ID_ORA = """
+SELECT id FROM time_intervals WHERE start_time = :s AND stop_time = :s
+"""
+SQL_INSERT_TIME_ID_ORA = """
+INSERT INTO time_intervals (id, start_time, stop_time) VALUES (:s, :s, :s)
+"""
+SQL_SELECT_RANGE_ID = """
+SELECT id FROM ranges WHERE river_id = %s AND a = %s AND b = %s
+"""
+SQL_INSERT_RANGE_ID = """
+INSERT INTO ranges (id, river_id, a, b) VALUES (%s, %s, %s, %s)
+"""
+SQL_SELECT_RANGE_ID_ORA = """
+SELECT id FROM ranges WHERE river_id = :s AND a = :s AND b = :s
+"""
+SQL_INSERT_RANGE_ID_ORA = """
+INSERT INTO ranges (id, river_id, a, b) VALUES (:s, :s, :s, :s)
+"""
+SQL_NEXT_ID     = "SELECT coalesce(max(id), -1) + 1 FROM %s"
+SQL_NEXT_ID_ORA = "SELECT coalesce(max(id), -1) + 1 FROM :s"
+
+
+NEXT_IDS = {}
+def next_id(cur, relation, oracle):
+    idx = NEXT_IDS.get(relation)
+    if idx is None:
+        if oracle:
+            cur.execute(SQL_NEXT_ID_ORA % relation)
+        else:
+            cur.execute(SQL_NEXT_ID % relation)
+        idx = cur.fetchone()[0]
+    NEXT_IDS[relation] = idx + 1
+    return idx
+
+def get_range_id(cur, river_id, a, b, oracle):
+    if oracle:
+        cur.execute(SQL_SELECT_RANGE_ID_ORA, (river_id, a, b))
+    else:
+        cur.execute(SQL_SELECT_RANGE_ID, (river_id, a, b))
+    row = cur.fetchone()
+    if row: return row[0]
+    idx = next_id(cur, "ranges", oracle)
+    if oracle:
+        cur.execute(SQL_INSERT_RANGE_ID_ORA, (idx, river_id, a, b))
+    else:
+        cur.execute(SQL_INSERT_RANGE_ID, (idx, river_id, a, b))
+    cur.connection.commit()
+    return idx
+
+def get_time_interval_id(cur, a, b, oracle):
+    if oracle:
+        cur.execute(SQL_SELECT_TIME_ID_ORA, (a, b))
+    else:
+        cur.execute(SQL_SELECT_TIME_ID, (a, b))
+    row = cur.fetchone()
+    if row: return row[0]
+    idx = next_id(cur, "time_intervals", oracle)
+    if oracle:
+        cur.execute(SQL_INSERT_TIME_ID_ORA, (idx, a, b))
+    else:
+        cur.execute(SQL_INSERT_TIME_ID, (idx, a, b))
+    cur.connection.commit()
+    return idx
 
 def insertRiverDgm(dbconn, dgmfile, river_name, dry_run, oracle):
     with codecs.open(dgmfile, "r", "latin1") as csvfile:
@@ -52,15 +120,27 @@
                     else:
                         values.append(unicode.encode(
                             fields[namedict[val]], "UTF-8"))
-                name = "%s KM %s - %s" % (river_name, fields[namedict["km_von"]],
-                        fields[namedict["km_bis"]])
+                km_von = fields[namedict["km_von"]]
+                km_bis = fields[namedict["km_bis"]]
+                year_from = datetime.datetime(
+                    int(fields[namedict["Jahr_von"]]), 1, 1)
+                year_to = datetime.datetime(
+                    int(fields[namedict["Jahr_bis"]]),1 ,1)
+
+                name = "%s KM %s - %s" % (river_name, km_von, km_bis)
                 cur = dbconn.cursor()
+                range_id = get_range_id(cur, river_id, float(km_von),
+                    float(km_bis), oracle)
+                time_interval_id = get_time_interval_id(cur, year_from,
+                    year_to, oracle)
+
                 if oracle:
                     stmt = SQL_INSERT_DGT_ORA
                 else:
                     stmt = SQL_INSERT_DGT
 
-                cur.execute(stmt, [river_id, name] + values)
+                cur.execute(stmt, [river_id, name, time_interval_id,
+                    range_id] + values)
 
         if not dry_run:
             dbconn.commit()