Mercurial > dive4elements > river
comparison flys-backend/src/main/java/de/intevation/flys/utils/DgmSqlConverter.java @ 3951:89ada0b9083f
Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
flys-backend/trunk@5604 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Ingo Weinzierl <ingo.weinzierl@intevation.de> |
---|---|
date | Thu, 27 Sep 2012 07:24:07 +0000 |
parents | |
children | 5d8db3349b77 |
comparison
equal
deleted
inserted
replaced
3950:82e931f88137 | 3951:89ada0b9083f |
---|---|
1 package de.intevation.flys.utils; | |
2 | |
3 import java.io.BufferedInputStream; | |
4 import java.io.BufferedWriter; | |
5 import java.io.File; | |
6 import java.io.FileInputStream; | |
7 import java.io.FileNotFoundException; | |
8 import java.io.FileWriter; | |
9 import java.io.IOException; | |
10 import java.io.InputStream; | |
11 import java.io.InputStreamReader; | |
12 import java.io.Reader; | |
13 import java.util.ArrayList; | |
14 import java.util.List; | |
15 | |
16 import au.com.bytecode.opencsv.CSVReader; | |
17 | |
18 | |
19 /** | |
20 * A converter for CSV files with DGM information. The result of a conversion | |
21 * is an SQL file with "INSERT INTO dem ..." statements. | |
22 * <br> | |
23 * To start the converter, at least the following three system properties are | |
24 * required: | |
25 * <br> | |
26 * <ul> | |
27 * <ol><b>gew.dir</b>: This property must point to the directory where all | |
28 * rivers are stored.</ol> | |
29 * <ol><b>csv</b>: This property must point to the CSV file that contains the | |
30 * DGM information.</ol> | |
31 * <ol><b>sql</b>: This property must point to a (not yet existing) file that | |
32 * will be generated by this converter.</ol> | |
33 * </ul> | |
34 * <br> | |
35 * In addiation, the following properties are accepted to modify log messages, | |
36 * etc. | |
37 * <ul> | |
38 * <ol><b>verbose</b>: Accepts integer values (0, 1, 2, 3) to modify the log | |
39 * messages. The higher the value the more log messages are printed to STDOUT. | |
40 * </ol> | |
41 * <ol><b>full</b>: Accepts true|false values. If true is set, all rivers | |
42 * included in the CSV file are taken into account while parsing. Otherwise, | |
43 * the converter reads information for 'Saar', 'Mosel' and 'Eble' only.</ol> | |
44 * </ul> | |
45 * | |
46 * @author Ingo Weinzierl <a href="mailto:ingo.weinzierl@intevation.de"> | |
47 * ingo.weinzierl@intevation.de</a> | |
48 * | |
49 */ | |
50 public class DgmSqlConverter { | |
51 | |
52 public static final String SQL_INSERT = "INSERT INTO dem (river_id, name, lower, upper, year_from, year_to," | |
53 + "projection, elevation_state, format, border_break, resolution, description, path) VALUES (" | |
54 + "%s, '%s', %s, %s, %s, %s, '%s', '%s', '%s', %s, '%s', '%s', '%s');"; | |
55 | |
56 public static final String SQL_SELECT_RIVER = "(SELECT id from rivers WHERE name = '%s')"; | |
57 | |
58 public static final char DEFAULT_SEPERATOR = ','; | |
59 public static final char DEFAULT_QUOTE = '"'; | |
60 public static final int DEFAULT_LOG_LEVEL = 2; | |
61 | |
62 public static final boolean FULL_MODE = Boolean.getBoolean("full"); | |
63 public static final String GEW_DIR = System.getProperty("gew.dir", null); | |
64 public static final String CSV_FILE = System.getProperty("csv"); | |
65 public static final String SQL_FILE = System.getProperty("sql"); | |
66 public static final int LOG_LEVEL = Integer.getInteger("verbose", | |
67 DEFAULT_LOG_LEVEL); | |
68 | |
69 public static final int MIN_COLUMN_COUNT = 15; | |
70 | |
71 public static final int IDX_RIVERNAME = 0; | |
72 public static final int IDX_NAME = 12; | |
73 public static final int IDX_LOWER = 1; | |
74 public static final int IDX_UPPER = 2; | |
75 public static final int IDX_YEAR_FROM = 3; | |
76 public static final int IDX_YEAR_TO = 4; | |
77 public static final int IDX_PROJECTION = 7; | |
78 public static final int IDX_ELEVATION_STATE = 8; | |
79 public static final int IDX_FORMAT = 9; | |
80 public static final int IDX_BORDER_BREAK = 10; | |
81 public static final int IDX_RESOLUTION = 11; | |
82 public static final int IDX_DESCRIPTION = 14; | |
83 public static final int IDX_FILE_NAME = 5; | |
84 public static final int IDX_FILE_PATH = 6; | |
85 | |
86 private class DGM { | |
87 | |
88 public String river; | |
89 public String name; | |
90 public String projection; | |
91 public String elevationState; | |
92 public String format; | |
93 public String resolution; | |
94 public String description; | |
95 public String path; | |
96 | |
97 public double lower; | |
98 public double upper; | |
99 public Integer yearFrom; | |
100 public Integer yearTo; | |
101 | |
102 public boolean borderBreak; | |
103 | |
104 public DGM() { | |
105 borderBreak = false; | |
106 } | |
107 | |
108 public String toSQL() { | |
109 String riverId = String.format(SQL_SELECT_RIVER, river); | |
110 String lower = String.valueOf(this.lower); | |
111 String upper = String.valueOf(this.upper); | |
112 String yearFrom = this.yearFrom != null ? String | |
113 .valueOf(this.yearFrom) : "null"; | |
114 String yearTo = this.yearTo != null ? String.valueOf(this.yearTo) | |
115 : "null"; | |
116 | |
117 return String.format(SQL_INSERT, riverId, name, lower, upper, | |
118 yearFrom, yearTo, projection, elevationState, format, | |
119 borderBreak, resolution, description, path); | |
120 } | |
121 } | |
122 | |
123 private File riverDir; | |
124 private File csv; | |
125 private File sql; | |
126 | |
127 private List<DGM> dgms; | |
128 | |
129 public static void debug(String msg) { | |
130 if (LOG_LEVEL >= 3) { | |
131 System.out.println("DEBUG: " + msg); | |
132 } | |
133 } | |
134 | |
135 public static void info(String msg) { | |
136 if (LOG_LEVEL >= 2) { | |
137 System.out.println("INFO: " + msg); | |
138 } | |
139 } | |
140 | |
141 public static void warn(String msg) { | |
142 if (LOG_LEVEL >= 1) { | |
143 System.out.println("WARN: " + msg); | |
144 } | |
145 } | |
146 | |
147 public static void error(String msg) { | |
148 System.out.println("ERROR: " + msg); | |
149 } | |
150 | |
151 public static File getRiverDir(String[] args) { | |
152 if (GEW_DIR != null && GEW_DIR.length() > 0) { | |
153 return new File(GEW_DIR); | |
154 } | |
155 else if (args != null && args.length > 0) { | |
156 return new File(args[0]); | |
157 } | |
158 | |
159 return null; | |
160 } | |
161 | |
162 public static File getCSVFile(String[] args) { | |
163 if (CSV_FILE != null && CSV_FILE.length() > 0) { | |
164 return new File(CSV_FILE); | |
165 } | |
166 else if (args != null && args.length > 1) { | |
167 return new File(args[1]); | |
168 } | |
169 | |
170 return null; | |
171 } | |
172 | |
173 public static File getSQLFile(String[] args) { | |
174 if (SQL_FILE != null && SQL_FILE.length() > 0) { | |
175 return new File(SQL_FILE); | |
176 } | |
177 else if (args != null && args.length > 2) { | |
178 return new File(args[2]); | |
179 } | |
180 | |
181 return null; | |
182 } | |
183 | |
184 public static void main(String[] args) { | |
185 info("Start convering CSV -> SQL statements"); | |
186 | |
187 if (!FULL_MODE) { | |
188 info("You are running in DEMO mode; other rivers than 'Saar', 'Mosel' and 'Elbe' are ignored."); | |
189 } | |
190 | |
191 File riverDir = getRiverDir(args); | |
192 | |
193 if (riverDir == null) { | |
194 warn("No rivers directory specified!"); | |
195 return; | |
196 } | |
197 else if (!riverDir.isDirectory()) { | |
198 warn("Specified rivers directory is not a directory!"); | |
199 return; | |
200 } | |
201 else if (!riverDir.canRead()) { | |
202 warn("Unable to read '" + riverDir.toString() + "'"); | |
203 return; | |
204 } | |
205 | |
206 File csv = getCSVFile(args); | |
207 | |
208 if (csv == null) { | |
209 warn("No CSV file specified!"); | |
210 return; | |
211 } | |
212 else if (csv.isDirectory()) { | |
213 warn("Specified CSV file is a directory!"); | |
214 return; | |
215 } | |
216 else if (!csv.canRead()) { | |
217 warn("Unable to read '" + csv.toString() + "'"); | |
218 return; | |
219 } | |
220 | |
221 File sql = getSQLFile(args); | |
222 | |
223 if (sql == null) { | |
224 warn("No destination file specified!"); | |
225 return; | |
226 } | |
227 else if (sql.isDirectory()) { | |
228 warn("Specified destination file is a directory!"); | |
229 return; | |
230 } | |
231 else if (sql.exists() && !sql.canWrite()) { | |
232 warn("Unable to write to '" + sql.toString() + "'"); | |
233 return; | |
234 } | |
235 else if (!sql.exists()) { | |
236 try { | |
237 sql.createNewFile(); | |
238 } | |
239 catch (IOException ioe) { | |
240 warn("Unable to write to '" + sql.toString() + "'"); | |
241 return; | |
242 } | |
243 } | |
244 | |
245 info("Start parsing CSV file '" + csv.toString() + "'"); | |
246 | |
247 try { | |
248 DgmSqlConverter parser = new DgmSqlConverter(riverDir, csv, sql); | |
249 parser.read(); | |
250 parser.write(); | |
251 } | |
252 catch (Exception e) { | |
253 error("Unexpected error: " + e.getMessage()); | |
254 e.printStackTrace(); | |
255 } | |
256 | |
257 info("Finished converting CSV -> SQL regularly."); | |
258 } | |
259 | |
260 public DgmSqlConverter(File riverDir, File csv, File sql) { | |
261 this.riverDir = riverDir; | |
262 this.csv = csv; | |
263 this.sql = sql; | |
264 this.dgms = new ArrayList<DGM>(); | |
265 } | |
266 | |
267 public void read() { | |
268 info("Read DGM information from CSV file: " + csv.getAbsolutePath()); | |
269 | |
270 InputStream in = null; | |
271 | |
272 try { | |
273 in = new BufferedInputStream(new FileInputStream(csv)); | |
274 } | |
275 catch (FileNotFoundException e) { | |
276 error("File not found: " + e.getMessage()); | |
277 return; | |
278 } | |
279 | |
280 Reader reader = new InputStreamReader(in); | |
281 CSVReader csvReader = new CSVReader(reader, DEFAULT_SEPERATOR, | |
282 DEFAULT_QUOTE); | |
283 | |
284 List<String[]> rows = new ArrayList<String[]>(); | |
285 | |
286 int success = 0; | |
287 | |
288 try { | |
289 rows = csvReader.readAll(); | |
290 | |
291 for (int idx = 0; idx < rows.size(); idx++) { | |
292 String[] row = rows.get(idx); | |
293 if (readRow(row)) { | |
294 success++; | |
295 } | |
296 else { | |
297 warn("Unable to parse row " + (idx + 1)); | |
298 } | |
299 } | |
300 } | |
301 catch (IOException e) { | |
302 error("Error while parsing CSV: " + e.getMessage()); | |
303 return; | |
304 } | |
305 | |
306 info("Parsed CSV file: " + rows.size() + " lines."); | |
307 info("Parsed " + success + " line successful"); | |
308 } | |
309 | |
310 private boolean readRow(String[] row) { | |
311 if (row == null) { | |
312 warn("Row is null!"); | |
313 return false; | |
314 } | |
315 | |
316 if (row.length < MIN_COLUMN_COUNT) { | |
317 warn("invalid column count: " + row.length); | |
318 return false; | |
319 } | |
320 | |
321 StringBuffer rowBuffer = new StringBuffer(); | |
322 for (String col : row) { | |
323 rowBuffer.append(col); | |
324 rowBuffer.append(" | "); | |
325 } | |
326 debug(rowBuffer.toString()); | |
327 | |
328 try { | |
329 DGM dgm = new DGM(); | |
330 dgm.river = readRiver(row[IDX_RIVERNAME]); | |
331 dgm.name = row[IDX_NAME]; | |
332 dgm.projection = row[IDX_PROJECTION]; | |
333 dgm.elevationState = row[IDX_ELEVATION_STATE]; | |
334 dgm.format = row[IDX_FORMAT]; | |
335 dgm.resolution = row[IDX_RESOLUTION]; | |
336 dgm.description = row[IDX_DESCRIPTION]; | |
337 dgm.lower = readLower(row[IDX_LOWER]); | |
338 dgm.upper = readUpper(row[IDX_UPPER]); | |
339 dgm.yearFrom = readFromYear(row[IDX_YEAR_FROM]); | |
340 dgm.yearTo = readToYear(row[IDX_YEAR_TO]); | |
341 dgm.borderBreak = readBorderBreak(row[IDX_BORDER_BREAK]); | |
342 dgm.path = readPath(dgm.river, row[IDX_FILE_PATH], | |
343 row[IDX_FILE_NAME]); | |
344 | |
345 dgms.add(dgm); | |
346 | |
347 return true; | |
348 } | |
349 catch (IllegalArgumentException iae) { | |
350 warn(iae.getMessage()); | |
351 } | |
352 | |
353 return false; | |
354 } | |
355 | |
356 private String readRiver(String rivername) throws IllegalArgumentException { | |
357 if (rivername == null || rivername.length() == 0) { | |
358 throw new IllegalAccessError("Invalid rivername: " + rivername); | |
359 } | |
360 | |
361 if (!FULL_MODE | |
362 && !(rivername.equals("Saar") || rivername.equals("Mosel") || rivername | |
363 .equals("Elbe"))) { | |
364 throw new IllegalArgumentException("In DEMO mode; skip river: " | |
365 + rivername); | |
366 } | |
367 | |
368 return rivername; | |
369 } | |
370 | |
371 private Double readLower(String lower) throws IllegalArgumentException { | |
372 try { | |
373 return Double.valueOf(lower); | |
374 } | |
375 catch (NumberFormatException nfe) { | |
376 } | |
377 | |
378 throw new IllegalArgumentException("Attribute 'lower' invalid: " | |
379 + lower); | |
380 } | |
381 | |
382 private Double readUpper(String upper) throws IllegalArgumentException { | |
383 try { | |
384 return Double.valueOf(upper); | |
385 } | |
386 catch (NumberFormatException nfe) { | |
387 } | |
388 | |
389 throw new IllegalArgumentException("Attribute 'upper' invalid: " | |
390 + upper); | |
391 } | |
392 | |
393 private Integer readFromYear(String from) throws IllegalArgumentException { | |
394 try { | |
395 return Integer.valueOf(from); | |
396 } | |
397 catch (NumberFormatException nfe) { | |
398 } | |
399 | |
400 return null; | |
401 } | |
402 | |
403 private Integer readToYear(String to) throws IllegalArgumentException { | |
404 try { | |
405 return Integer.valueOf(to); | |
406 } | |
407 catch (NumberFormatException nfe) { | |
408 } | |
409 | |
410 return null; | |
411 } | |
412 | |
413 private String readPath(String rivername, String dir, String filename) | |
414 throws IllegalArgumentException { | |
415 File riverDir = new File(this.riverDir, rivername); | |
416 File dgmDir = new File(riverDir, dir); | |
417 File dgmFile = new File(dgmDir, filename); | |
418 | |
419 try { | |
420 debug("Path of DGM = " + dgmFile.getAbsolutePath()); | |
421 | |
422 if (dgmFile == null || !dgmFile.exists()) { | |
423 throw new IllegalAccessError( | |
424 "Specified DGM file does not exist: " | |
425 + dgmFile.getAbsolutePath()); | |
426 } | |
427 | |
428 if (!dgmFile.isFile()) { | |
429 throw new IllegalArgumentException( | |
430 "Specified DGM file is no file: " | |
431 + dgmFile.getAbsolutePath()); | |
432 } | |
433 } | |
434 catch (IllegalAccessError iae) { | |
435 throw new IllegalArgumentException("Cannot find DGM file (river=" | |
436 + rivername + " | directory=" + dir + " | filename=" + filename | |
437 + ")"); | |
438 } | |
439 | |
440 return dgmFile.getAbsolutePath(); | |
441 } | |
442 | |
443 private boolean readBorderBreak(String borderBreak) { | |
444 if (borderBreak == null || borderBreak.length() == 0) { | |
445 return true; | |
446 } | |
447 else if (borderBreak.toLowerCase().equals("ja")) { | |
448 return true; | |
449 } | |
450 else if (borderBreak.toLowerCase().equals("nein")) { | |
451 return false; | |
452 } | |
453 else { | |
454 return true; | |
455 } | |
456 } | |
457 | |
458 public void write() { | |
459 info("Write DEM information to SQL file: " + sql.getAbsolutePath()); | |
460 | |
461 BufferedWriter bufferedWriter = null; | |
462 try { | |
463 bufferedWriter = new BufferedWriter(new FileWriter(sql)); | |
464 | |
465 for (DGM dgm : dgms) { | |
466 bufferedWriter.write(dgm.toSQL()); | |
467 bufferedWriter.newLine(); | |
468 } | |
469 } | |
470 catch (IOException ioe) { | |
471 error(ioe.getMessage()); | |
472 } | |
473 finally { | |
474 if (bufferedWriter != null) { | |
475 try { | |
476 bufferedWriter.close(); | |
477 } | |
478 catch (IOException ioe) { | |
479 } | |
480 } | |
481 } | |
482 } | |
483 } |