Mercurial > dive4elements > river
view flys-backend/src/main/java/de/intevation/flys/utils/DgmSqlConverter.java @ 5021:bf22086747bc
StringUtil: Added containsIgnoreCase implementation.
author | Felix Wolfsteller <felix.wolfsteller@intevation.de> |
---|---|
date | Tue, 19 Feb 2013 12:03:55 +0100 |
parents | f5912365619c |
children |
line wrap: on
line source
package de.intevation.flys.utils; import java.io.BufferedInputStream; import java.io.BufferedWriter; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileWriter; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.io.Reader; import java.util.ArrayList; import java.util.List; import au.com.bytecode.opencsv.CSVReader; /** * A converter for CSV files with DGM information. The result of a conversion * is an SQL file with "INSERT INTO dem ..." statements. * <br> * To start the converter, at least the following three system properties are * required: * <br> * <ul> * <ol><b>gew.dir</b>: This property must point to the directory where all * rivers are stored.</ol> * <ol><b>csv</b>: This property must point to the CSV file that contains the * DGM information.</ol> * <ol><b>sql</b>: This property must point to a (not yet existing) file that * will be generated by this converter.</ol> * </ul> * <br> * In addiation, the following properties are accepted to modify log messages, * etc. * <ul> * <ol><b>verbose</b>: Accepts integer values (0, 1, 2, 3) to modify the log * messages. The higher the value the more log messages are printed to STDOUT. * </ol> * <ol><b>full</b>: Accepts true|false values. If true is set, all rivers * included in the CSV file are taken into account while parsing. Otherwise, * the converter reads information for 'Saar', 'Mosel' and 'Eble' only.</ol> * </ul> * * @author Ingo Weinzierl <a href="mailto:ingo.weinzierl@intevation.de"> * ingo.weinzierl@intevation.de</a> * */ public class DgmSqlConverter { public static final String SQL_INSERT = "INSERT INTO dem (river_id, name, lower, upper, year_from, year_to," + "projection, elevation_state, format, border_break, resolution, description, path) VALUES (" + "%s, '%s', %s, %s, %s, %s, '%s', '%s', '%s', %s, '%s', '%s', '%s');"; public static final String SQL_SELECT_RIVER = "(SELECT id from rivers WHERE name = '%s')"; public static final char DEFAULT_SEPERATOR = ','; public static final char DEFAULT_QUOTE = '"'; public static final int DEFAULT_LOG_LEVEL = 2; public static final boolean FULL_MODE = Boolean.getBoolean("full"); public static final String GEW_DIR = System.getProperty("gew.dir", null); public static final String CSV_FILE = System.getProperty("csv"); public static final String SQL_FILE = System.getProperty("sql"); public static final int LOG_LEVEL = Integer.getInteger("verbose", DEFAULT_LOG_LEVEL); public static final int MIN_COLUMN_COUNT = 15; public static final int IDX_RIVERNAME = 0; public static final int IDX_NAME = 12; public static final int IDX_LOWER = 1; public static final int IDX_UPPER = 2; public static final int IDX_YEAR_FROM = 3; public static final int IDX_YEAR_TO = 4; public static final int IDX_PROJECTION = 7; public static final int IDX_ELEVATION_STATE = 8; public static final int IDX_FORMAT = 9; public static final int IDX_BORDER_BREAK = 10; public static final int IDX_RESOLUTION = 11; public static final int IDX_DESCRIPTION = 14; public static final int IDX_FILE_NAME = 5; public static final int IDX_FILE_PATH = 6; private class DGM { public String river; public String name; public String projection; public String elevationState; public String format; public String resolution; public String description; public String path; public double lower; public double upper; public Integer yearFrom; public Integer yearTo; public boolean borderBreak; public DGM() { borderBreak = false; } public String toSQL() { String riverId = String.format(SQL_SELECT_RIVER, river); String lower = String.valueOf(this.lower); String upper = String.valueOf(this.upper); String yearFrom = this.yearFrom != null ? String .valueOf(this.yearFrom) : ""; String yearTo = this.yearTo != null ? String.valueOf(this.yearTo) : ""; return String.format(SQL_INSERT, riverId, name, lower, upper, yearFrom, yearTo, projection, elevationState, format, borderBreak, resolution, description, path); } } private File riverDir; private File csv; private File sql; private List<DGM> dgms; public static void debug(String msg) { if (LOG_LEVEL >= 3) { System.out.println("DEBUG: " + msg); } } public static void info(String msg) { if (LOG_LEVEL >= 2) { System.out.println("INFO: " + msg); } } public static void warn(String msg) { if (LOG_LEVEL >= 1) { System.out.println("WARN: " + msg); } } public static void error(String msg) { System.out.println("ERROR: " + msg); } public static File getRiverDir(String[] args) { if (GEW_DIR != null && GEW_DIR.length() > 0) { return new File(GEW_DIR); } else if (args != null && args.length > 0) { return new File(args[0]); } return null; } public static File getCSVFile(String[] args) { if (CSV_FILE != null && CSV_FILE.length() > 0) { return new File(CSV_FILE); } else if (args != null && args.length > 1) { return new File(args[1]); } return null; } public static File getSQLFile(String[] args) { if (SQL_FILE != null && SQL_FILE.length() > 0) { return new File(SQL_FILE); } else if (args != null && args.length > 2) { return new File(args[2]); } return null; } public static void main(String[] args) { info("Start convering CSV -> SQL statements"); if (!FULL_MODE) { info("You are running in DEMO mode; other rivers than 'Saar', 'Mosel' and 'Elbe' are ignored."); } File riverDir = getRiverDir(args); if (riverDir == null) { warn("No rivers directory specified!"); return; } else if (!riverDir.isDirectory()) { warn("Specified rivers directory is not a directory!"); return; } else if (!riverDir.canRead()) { warn("Unable to read '" + riverDir.toString() + "'"); return; } File csv = getCSVFile(args); if (csv == null) { warn("No CSV file specified!"); return; } else if (csv.isDirectory()) { warn("Specified CSV file is a directory!"); return; } else if (!csv.canRead()) { warn("Unable to read '" + csv.toString() + "'"); return; } File sql = getSQLFile(args); if (sql == null) { warn("No destination file specified!"); return; } else if (sql.isDirectory()) { warn("Specified destination file is a directory!"); return; } else if (sql.exists() && !sql.canWrite()) { warn("Unable to write to '" + sql.toString() + "'"); return; } else if (!sql.exists()) { try { sql.createNewFile(); } catch (IOException ioe) { warn("Unable to write to '" + sql.toString() + "'"); return; } } info("Start parsing CSV file '" + csv.toString() + "'"); try { DgmSqlConverter parser = new DgmSqlConverter(riverDir, csv, sql); parser.read(); parser.write(); } catch (Exception e) { error("Unexpected error: " + e.getMessage()); e.printStackTrace(); } info("Finished converting CSV -> SQL regularly."); } public DgmSqlConverter(File riverDir, File csv, File sql) { this.riverDir = riverDir; this.csv = csv; this.sql = sql; this.dgms = new ArrayList<DGM>(); } public void read() { info("Read DGM information from CSV file: " + csv.getAbsolutePath()); InputStream in = null; try { in = new BufferedInputStream(new FileInputStream(csv)); } catch (FileNotFoundException e) { error("File not found: " + e.getMessage()); return; } Reader reader = new InputStreamReader(in); CSVReader csvReader = new CSVReader(reader, DEFAULT_SEPERATOR, DEFAULT_QUOTE); List<String[]> rows = new ArrayList<String[]>(); int success = 0; try { rows = csvReader.readAll(); for (int idx = 0; idx < rows.size(); idx++) { String[] row = rows.get(idx); if (readRow(row)) { success++; } else { warn("Unable to parse row " + (idx + 1)); } } } catch (IOException e) { error("Error while parsing CSV: " + e.getMessage()); return; } info("Parsed CSV file: " + rows.size() + " lines."); info("Parsed " + success + " line successful"); } private boolean readRow(String[] row) { if (row == null) { warn("Row is null!"); return false; } if (row.length < MIN_COLUMN_COUNT) { warn("invalid column count: " + row.length); return false; } StringBuffer rowBuffer = new StringBuffer(); for (String col : row) { rowBuffer.append(col); rowBuffer.append(" | "); } debug(rowBuffer.toString()); try { DGM dgm = new DGM(); dgm.river = readRiver(row[IDX_RIVERNAME]); dgm.name = row[IDX_NAME]; dgm.projection = row[IDX_PROJECTION]; dgm.elevationState = row[IDX_ELEVATION_STATE]; dgm.format = row[IDX_FORMAT]; dgm.resolution = row[IDX_RESOLUTION]; dgm.description = row[IDX_DESCRIPTION]; dgm.lower = readLower(row[IDX_LOWER]); dgm.upper = readUpper(row[IDX_UPPER]); dgm.yearFrom = readFromYear(row[IDX_YEAR_FROM]); dgm.yearTo = readToYear(row[IDX_YEAR_TO]); dgm.borderBreak = readBorderBreak(row[IDX_BORDER_BREAK]); dgm.path = readPath(dgm.river, row[IDX_FILE_PATH], row[IDX_FILE_NAME]); dgms.add(dgm); return true; } catch (IllegalArgumentException iae) { warn(iae.getMessage()); } return false; } private String readRiver(String rivername) throws IllegalArgumentException { if (rivername == null || rivername.length() == 0) { throw new IllegalAccessError("Invalid rivername: " + rivername); } if (!FULL_MODE && !(rivername.equals("Saar") || rivername.equals("Mosel") || rivername .equals("Elbe"))) { throw new IllegalArgumentException("In DEMO mode; skip river: " + rivername); } return rivername; } private Double readLower(String lower) throws IllegalArgumentException { try { return Double.valueOf(lower); } catch (NumberFormatException nfe) { } throw new IllegalArgumentException("Attribute 'lower' invalid: " + lower); } private Double readUpper(String upper) throws IllegalArgumentException { try { return Double.valueOf(upper); } catch (NumberFormatException nfe) { } throw new IllegalArgumentException("Attribute 'upper' invalid: " + upper); } private Integer readFromYear(String from) throws IllegalArgumentException { try { return Integer.valueOf(from); } catch (NumberFormatException nfe) { } return null; } private Integer readToYear(String to) throws IllegalArgumentException { try { return Integer.valueOf(to); } catch (NumberFormatException nfe) { } return null; } private String readPath(String rivername, String dir, String filename) throws IllegalArgumentException { File riverDir = new File(this.riverDir, rivername); File dgmDir = new File(riverDir, dir); File dgmFile = new File(dgmDir, filename); try { debug("Path of DGM = " + dgmFile.getAbsolutePath()); if (dgmFile == null || !dgmFile.exists()) { throw new IllegalAccessError( "Specified DGM file does not exist: " + dgmFile.getAbsolutePath()); } if (!dgmFile.isFile()) { throw new IllegalArgumentException( "Specified DGM file is no file: " + dgmFile.getAbsolutePath()); } } catch (IllegalAccessError iae) { throw new IllegalArgumentException("Cannot find DGM file (river=" + rivername + " | directory=" + dir + " | filename=" + filename + ")"); } return dgmFile.getAbsolutePath(); } private boolean readBorderBreak(String borderBreak) { if (borderBreak == null || borderBreak.length() == 0) { return true; } else if (borderBreak.toLowerCase().equals("ja")) { return true; } else if (borderBreak.toLowerCase().equals("nein")) { return false; } else { return true; } } public void write() { info("Write DEM information to SQL file: " + sql.getAbsolutePath()); BufferedWriter bufferedWriter = null; try { bufferedWriter = new BufferedWriter(new FileWriter(sql)); for (DGM dgm : dgms) { bufferedWriter.write(dgm.toSQL()); bufferedWriter.newLine(); } } catch (IOException ioe) { error(ioe.getMessage()); } finally { if (bufferedWriter != null) { try { bufferedWriter.close(); } catch (IOException ioe) { } } } } }