aheinecke@4975: # -*- coding: utf-8 -*-
aheinecke@4975: 
aheinecke@4975: import codecs
aheinecke@4975: import utils
aheinecke@5210: import datetime
aheinecke@4975: 
aheinecke@4975: def latin(string):
aheinecke@4975:     return unicode(string, "latin1")
aheinecke@4975: 
aheinecke@5213: import logging
aheinecke@5213: logger = logging.getLogger("DGM")
aheinecke@5213: 
aheinecke@5213: 
aheinecke@4975: # <dbfield> : (<csvfield>, conversion function)
aheinecke@4975: DGM_MAP = {
aheinecke@4975:     "projection"      : "Projektion",
aheinecke@4975:     "elevation_state" : latin("Höhenstatus"),
aheinecke@4975:     "format"          : "Format",
aheinecke@4975:     "border_break"    : ("Bruchkanten",
aheinecke@4975:         lambda x: True if x.lower() == "Ja" else False),
aheinecke@4975:     "resolution"      : (latin("Auflösung"), lambda x: x),
aheinecke@4975: #   "description"     : 
aheinecke@4993:     "srid"            : "SRID",
aheinecke@4975:     "path"            : ("Pfad_Bestand", lambda x: x),
aheinecke@4975:     }
aheinecke@4975: 
aheinecke@5210: SQL_INSERT_DGT = "INSERT INTO dem (river_id, name," \
aheinecke@5210:         " time_interval_id, range_id, " + ", ".join(DGM_MAP.keys()) + \
aheinecke@5210:         ") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
aheinecke@5210: SQL_INSERT_DGT_ORA = "INSERT INTO dem (river_id, name," \
aheinecke@5210:         " time_interval_id, range_id, " + ", ".join(DGM_MAP.keys()) + \
aheinecke@5210:         ") VALUES (:s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s)"
aheinecke@5210: SQL_SELECT_TIME_ID = """
aheinecke@5210: SELECT id FROM time_intervals WHERE start_time = %s AND stop_time = %s
aheinecke@5210: """
aheinecke@5210: SQL_INSERT_TIME_ID = """
aheinecke@5210: INSERT INTO time_intervals (id, start_time, stop_time) VALUES (%s, %s, %s)
aheinecke@5210: """
aheinecke@5210: SQL_SELECT_TIME_ID_ORA = """
aheinecke@5210: SELECT id FROM time_intervals WHERE start_time = :s AND stop_time = :s
aheinecke@5210: """
aheinecke@5210: SQL_INSERT_TIME_ID_ORA = """
aheinecke@5210: INSERT INTO time_intervals (id, start_time, stop_time) VALUES (:s, :s, :s)
aheinecke@5210: """
aheinecke@5210: SQL_SELECT_RANGE_ID = """
aheinecke@5210: SELECT id FROM ranges WHERE river_id = %s AND a = %s AND b = %s
aheinecke@5210: """
aheinecke@5210: SQL_INSERT_RANGE_ID = """
aheinecke@5210: INSERT INTO ranges (id, river_id, a, b) VALUES (%s, %s, %s, %s)
aheinecke@5210: """
aheinecke@5210: SQL_SELECT_RANGE_ID_ORA = """
aheinecke@5210: SELECT id FROM ranges WHERE river_id = :s AND a = :s AND b = :s
aheinecke@5210: """
aheinecke@5210: SQL_INSERT_RANGE_ID_ORA = """
aheinecke@5210: INSERT INTO ranges (id, river_id, a, b) VALUES (:s, :s, :s, :s)
aheinecke@5210: """
aheinecke@5210: SQL_NEXT_ID     = "SELECT coalesce(max(id), -1) + 1 FROM %s"
aheinecke@5210: SQL_NEXT_ID_ORA = "SELECT coalesce(max(id), -1) + 1 FROM :s"
aheinecke@5210: 
aheinecke@5210: 
aheinecke@5210: NEXT_IDS = {}
aheinecke@5210: def next_id(cur, relation, oracle):
aheinecke@5210:     idx = NEXT_IDS.get(relation)
aheinecke@5210:     if idx is None:
aheinecke@5210:         if oracle:
aheinecke@5210:             cur.execute(SQL_NEXT_ID_ORA % relation)
aheinecke@5210:         else:
aheinecke@5210:             cur.execute(SQL_NEXT_ID % relation)
aheinecke@5210:         idx = cur.fetchone()[0]
aheinecke@5210:     NEXT_IDS[relation] = idx + 1
aheinecke@5210:     return idx
aheinecke@5210: 
aheinecke@5210: def get_range_id(cur, river_id, a, b, oracle):
aheinecke@5210:     if oracle:
aheinecke@5210:         cur.execute(SQL_SELECT_RANGE_ID_ORA, (river_id, a, b))
aheinecke@5210:     else:
aheinecke@5210:         cur.execute(SQL_SELECT_RANGE_ID, (river_id, a, b))
aheinecke@5210:     row = cur.fetchone()
aheinecke@5210:     if row: return row[0]
aheinecke@5210:     idx = next_id(cur, "ranges", oracle)
aheinecke@5210:     if oracle:
aheinecke@5210:         cur.execute(SQL_INSERT_RANGE_ID_ORA, (idx, river_id, a, b))
aheinecke@5210:     else:
aheinecke@5210:         cur.execute(SQL_INSERT_RANGE_ID, (idx, river_id, a, b))
aheinecke@5210:     cur.connection.commit()
aheinecke@5210:     return idx
aheinecke@5210: 
aheinecke@5210: def get_time_interval_id(cur, a, b, oracle):
aheinecke@5213:     if not a or not b:
aheinecke@5213:         return None
aheinecke@5210:     if oracle:
aheinecke@5210:         cur.execute(SQL_SELECT_TIME_ID_ORA, (a, b))
aheinecke@5210:     else:
aheinecke@5210:         cur.execute(SQL_SELECT_TIME_ID, (a, b))
aheinecke@5210:     row = cur.fetchone()
aheinecke@5210:     if row: return row[0]
aheinecke@5210:     idx = next_id(cur, "time_intervals", oracle)
aheinecke@5210:     if oracle:
aheinecke@5210:         cur.execute(SQL_INSERT_TIME_ID_ORA, (idx, a, b))
aheinecke@5210:     else:
aheinecke@5210:         cur.execute(SQL_INSERT_TIME_ID, (idx, a, b))
aheinecke@5210:     cur.connection.commit()
aheinecke@5210:     return idx
aheinecke@4975: 
aheinecke@5036: def insertRiverDgm(dbconn, dgmfile, river_name, dry_run, oracle):
aheinecke@4975:     with codecs.open(dgmfile, "r", "latin1") as csvfile:
aheinecke@4975:         firstline = csvfile.readline()
aheinecke@4975:         names = firstline.split(";")
aheinecke@4975:         namedict = {}
aheinecke@4975:         field_nr = 0
aheinecke@4975:         for name in names:
aheinecke@4975:             namedict[name] = field_nr
aheinecke@4975:             field_nr += 1
aheinecke@4975: 
aheinecke@5036:         river_id = utils.getRiverId(dbconn, river_name, oracle)
aheinecke@4975:         for line in csvfile:
aheinecke@4975:             fields = line.split(";")
aheinecke@4975:             if not fields: continue
aheinecke@5222:             if fields[namedict[latin("Gewässer")]] != \
aheinecke@5222:                     unicode(utils.getUTF8(river_name),'UTF-8'):
aheinecke@4975:                 continue
aheinecke@4975:             else:
aheinecke@4975:                 values=[]
aheinecke@4975:                 for key, val in DGM_MAP.items():
aheinecke@4975:                     if isinstance(val, tuple):
aheinecke@4975:                         values.append(val[1](fields[namedict[val[0]]]))
aheinecke@4975:                     else:
aheinecke@4975:                         values.append(unicode.encode(
aheinecke@4975:                             fields[namedict[val]], "UTF-8"))
aheinecke@5210:                 km_von = fields[namedict["km_von"]]
aheinecke@5210:                 km_bis = fields[namedict["km_bis"]]
aheinecke@5213:                 year_from = None
aheinecke@5213:                 year_to = None
aheinecke@5213:                 try:
aheinecke@5213:                     year_from = datetime.datetime(
aheinecke@5213:                         int(fields[namedict["Jahr_von"]]), 1, 1)
aheinecke@5213:                     year_to = datetime.datetime(
aheinecke@5213:                         int(fields[namedict["Jahr_bis"]]),1 ,1)
aheinecke@5213:                 except ValueError:
aheinecke@5213:                     logger.warn("Invalid numbers (or none) found in year_from and year_to")
aheinecke@5210: 
aheinecke@5210:                 name = "%s KM %s - %s" % (river_name, km_von, km_bis)
aheinecke@4975:                 cur = dbconn.cursor()
aheinecke@5210:                 range_id = get_range_id(cur, river_id, float(km_von),
aheinecke@5210:                     float(km_bis), oracle)
aheinecke@5210:                 time_interval_id = get_time_interval_id(cur, year_from,
aheinecke@5210:                     year_to, oracle)
aheinecke@5210: 
aheinecke@5036:                 if oracle:
aheinecke@5036:                     stmt = SQL_INSERT_DGT_ORA
aheinecke@5036:                 else:
aheinecke@5036:                     stmt = SQL_INSERT_DGT
aheinecke@5036: 
aheinecke@5210:                 cur.execute(stmt, [river_id, name, time_interval_id,
aheinecke@5210:                     range_id] + values)
aheinecke@4975: 
aheinecke@4975:         if not dry_run:
aheinecke@4975:             dbconn.commit()
aheinecke@4975: