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