Mercurial > dive4elements > river

comparison flys-backend/contrib/shpimporter/dgm.py @ 5351:292f00337015

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Revert 5301:7d29c277000e which introduced inconsitencies in the sequences
author Andre Heinecke <aheinecke@intevation.de>
date Wed, 20 Mar 2013 17:07:52 +0100
parents 7d29c277000e
children f1009aa7dcd1
comparison
equal deleted inserted replaced
5350:2f6e6ae2fc56 5351:292f00337015
32 ") VALUES (:s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s)" 32 ") VALUES (:s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s)"
33 SQL_SELECT_TIME_ID = """ 33 SQL_SELECT_TIME_ID = """
34 SELECT id FROM time_intervals WHERE start_time = %s AND stop_time = %s 34 SELECT id FROM time_intervals WHERE start_time = %s AND stop_time = %s
35 """ 35 """
36 SQL_INSERT_TIME_ID = """ 36 SQL_INSERT_TIME_ID = """
37 INSERT INTO time_intervals (start_time, stop_time) VALUES (%s, %s) 37 INSERT INTO time_intervals (id, start_time, stop_time) VALUES (%s, %s, %s)
38 """ 38 """
39 SQL_SELECT_TIME_ID_ORA = """ 39 SQL_SELECT_TIME_ID_ORA = """
40 SELECT id FROM time_intervals WHERE start_time = :s AND stop_time = :s 40 SELECT id FROM time_intervals WHERE start_time = :s AND stop_time = :s
41 """ 41 """
42 SQL_INSERT_TIME_ID_ORA = """ 42 SQL_INSERT_TIME_ID_ORA = """
43 INSERT INTO time_intervals (start_time, stop_time) VALUES (:s, :s) 43 INSERT INTO time_intervals (id, start_time, stop_time) VALUES (:s, :s, :s)
44 """ 44 """
45 SQL_SELECT_RANGE_ID = """ 45 SQL_SELECT_RANGE_ID = """
46 SELECT id FROM ranges WHERE river_id = %s AND a = %s AND b = %s 46 SELECT id FROM ranges WHERE river_id = %s AND a = %s AND b = %s
47 """ 47 """
48 SQL_INSERT_RANGE_ID = """ 48 SQL_INSERT_RANGE_ID = """
49 INSERT INTO ranges (river_id, a, b) VALUES (%s, %s, %s) 49 INSERT INTO ranges (id, river_id, a, b) VALUES (%s, %s, %s, %s)
50 """ 50 """
51 SQL_SELECT_RANGE_ID_ORA = """ 51 SQL_SELECT_RANGE_ID_ORA = """
52 SELECT id FROM ranges WHERE river_id = :s AND a = :s AND b = :s 52 SELECT id FROM ranges WHERE river_id = :s AND a = :s AND b = :s
53 """ 53 """
54 SQL_INSERT_RANGE_ID_ORA = """ 54 SQL_INSERT_RANGE_ID_ORA = """
55 INSERT INTO ranges (river_id, a, b) VALUES (:s, :s, :s) 55 INSERT INTO ranges (id, river_id, a, b) VALUES (:s, :s, :s, :s)
56 """ 56 """
57 SQL_NEW_ID = "SELECT max(id) FROM %s" 57 SQL_NEXT_ID = "SELECT coalesce(max(id), -1) + 1 FROM %s"
58 58
59 59
60 NEXT_IDS = {} 60 NEXT_IDS = {}
61 def next_id(cur, relation): 61 def next_id(cur, relation):
62 idx = NEXT_IDS.get(relation) 62 idx = NEXT_IDS.get(relation)
63 if idx is None: 63 if idx is None:
64 cur.execute(SQL_NEW_ID % relation) 64 cur.execute(SQL_NEXT_ID % relation)
65 idx = cur.fetchone()[0] 65 idx = cur.fetchone()[0]
66 NEXT_IDS[relation] = idx + 1 66 NEXT_IDS[relation] = idx + 1
67 return idx 67 return idx
68 68
69 def get_range_id(cur, river_id, a, b, oracle): 69 def get_range_id(cur, river_id, a, b, oracle):
71 cur.execute(SQL_SELECT_RANGE_ID_ORA, (river_id, a, b)) 71 cur.execute(SQL_SELECT_RANGE_ID_ORA, (river_id, a, b))
72 else: 72 else:
73 cur.execute(SQL_SELECT_RANGE_ID, (river_id, a, b)) 73 cur.execute(SQL_SELECT_RANGE_ID, (river_id, a, b))
74 row = cur.fetchone() 74 row = cur.fetchone()
75 if row: return row[0] 75 if row: return row[0]
76 idx = next_id(cur, "ranges")
76 if oracle: 77 if oracle:
77 cur.execute(SQL_INSERT_RANGE_ID_ORA, (river_id, a, b)) 78 cur.execute(SQL_INSERT_RANGE_ID_ORA, (idx, river_id, a, b))
78 else: 79 else:
79 cur.execute(SQL_INSERT_RANGE_ID, (river_id, a, b)) 80 cur.execute(SQL_INSERT_RANGE_ID, (idx, river_id, a, b))
80 cur.connection.commit() 81 cur.connection.commit()
81 return next_id(cur, "ranges") 82 return idx
82 83
83 def get_time_interval_id(cur, a, b, oracle): 84 def get_time_interval_id(cur, a, b, oracle):
84 if not a or not b: 85 if not a or not b:
85 return None 86 return None
86 if oracle: 87 if oracle:
87 cur.execute(SQL_SELECT_TIME_ID_ORA, (a, b)) 88 cur.execute(SQL_SELECT_TIME_ID_ORA, (a, b))
88 else: 89 else:
89 cur.execute(SQL_SELECT_TIME_ID, (a, b)) 90 cur.execute(SQL_SELECT_TIME_ID, (a, b))
90 row = cur.fetchone() 91 row = cur.fetchone()
91 if row: return row[0] 92 if row: return row[0]
93 idx = next_id(cur, "time_intervals")
92 if oracle: 94 if oracle:
93 cur.execute(SQL_INSERT_TIME_ID_ORA, (a, b)) 95 cur.execute(SQL_INSERT_TIME_ID_ORA, (idx, a, b))
94 else: 96 else:
95 cur.execute(SQL_INSERT_TIME_ID, (a, b)) 97 cur.execute(SQL_INSERT_TIME_ID, (idx, a, b))
96 cur.connection.commit() 98 cur.connection.commit()
97 return next_id(cur, "time_intervals") 99 return idx
98 100
99 def insertRiverDgm(dbconn, dgmfile, river_name, dry_run, oracle): 101 def insertRiverDgm(dbconn, dgmfile, river_name, dry_run, oracle):
100 with codecs.open(dgmfile, "r", "latin1") as csvfile: 102 with codecs.open(dgmfile, "r", "latin1") as csvfile:
101 firstline = csvfile.readline() 103 firstline = csvfile.readline()
102 names = firstline.split(";") 104 names = firstline.split(";")
http://dive4elements.wald.intevation.org