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: # : (, 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: