Mercurial > dive4elements > river
diff 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 |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-backend/src/main/java/de/intevation/flys/utils/DgmSqlConverter.java Thu Sep 27 07:24:07 2012 +0000 @@ -0,0 +1,483 @@ +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) : "null"; + String yearTo = this.yearTo != null ? String.valueOf(this.yearTo) + : "null"; + + 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) { + } + } + } + } +}