/*
* #!
* Ontopia Engine
* #-
* Copyright (C) 2001 - 2013 The Ontopia Project
* #-
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
* !#
*/
package net.ontopia.persistence.rdbms;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.LineNumberReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.Types;
import java.util.Properties;
import net.ontopia.persistence.proxy.DefaultConnectionFactory;
import net.ontopia.utils.CmdlineOptions;
import net.ontopia.utils.CmdlineUtils;
import net.ontopia.utils.OntopiaRuntimeException;
import net.ontopia.utils.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* INTERNAL: Command line tool for importing comma- or semicolon
* separated files into a database.
*/
public class CSVImport {
// Define a logging category.
static Logger log = LoggerFactory.getLogger(CSVImport.class.getName());
protected Project project;
protected Connection conn;
protected String table;
protected String[] columns;
protected String separator = ",";
protected boolean cleartable = true;
protected boolean stripquotes = true;
protected boolean ignorecolumns = true;
protected int ignorelines = 0;
public CSVImport(Project project, Connection conn) {
this.project = project;
this.conn = conn;
}
public Project getProject() {
return project;
}
public void setTable(String table) {
this.table = table;
}
public void setColumns(String[] columns) {
this.columns = columns;
}
public void setSeparator(String separator) {
this.separator = separator;
}
public void setClearTable(boolean cleartable) {
this.cleartable = cleartable;
}
public void setStripQuotes(boolean stripquotes) {
this.stripquotes = stripquotes;
}
public void setIgnoreColumns(boolean ignorecolumns) {
this.ignorecolumns = ignorecolumns;
}
public void setIgnoreLines(int ignorelines) {
this.ignorelines = ignorelines;
}
public static void main(String[] argv) throws Exception {
// Initialize logging
CmdlineUtils.initializeLogging();
// Initialize command line option parser and listeners
CmdlineOptions options = new CmdlineOptions("CSVImport", argv);
OptionsListener ohandler = new OptionsListener();
// Register local options
options.addLong(ohandler, "separator", 's', true);
options.addLong(ohandler, "stripquotes", 'q');
options.addLong(ohandler, "ignorelines", 'i', true);
// Register logging options
CmdlineUtils.registerLoggingOptions(options);
// Parse command line options
try {
options.parse();
} catch (CmdlineOptions.OptionsException e) {
System.err.println("Error: " + e.getMessage());
System.exit(1);
}
// Get command line arguments
String[] args = options.getArguments();
if (args.length < 4) {
System.err.println("Error: wrong number of arguments.");
usage();
System.exit(1);
}
String schema = args[0];
String dbprops = args[1];
String csvfile = args[2];
String table = args[3];
String[] columns = StringUtils.split(args[4], ",");
// Load property file
Properties props = new Properties();
props.load(new FileInputStream(dbprops));
// Create database connection
DefaultConnectionFactory cfactory = new DefaultConnectionFactory(props, false);
Connection conn = cfactory.requestConnection();
CSVImport ci = new CSVImport(DatabaseProjectReader.loadProject(schema), conn);
ci.setTable(table);
ci.setColumns(columns);
ci.setSeparator(ohandler.separator);
ci.setClearTable(true);
ci.setStripQuotes(ohandler.stripquotes);
ci.setIgnoreColumns(true);
ci.setIgnoreLines(ohandler.ignorelines);
ci.importCSV(new FileInputStream(csvfile));
}
public void importCSV(InputStream csvfile) throws Exception {
// Execute statements
try {
String[] qmarks = new String[columns.length];
for (int i=0; i < qmarks.length; i++) {
qmarks[i] = "?";
}
if (cleartable) {
String delsql = "delete from " + table;
Statement delstm = conn.createStatement();
delstm.executeUpdate(delsql);
//! conn.commit();
}
String sql = "insert into " + table + " (" + StringUtils.join(columns, ", ")
+ ") values (" + StringUtils.join(qmarks, ", ") + ")";
log.debug("SQL: " + sql);
PreparedStatement stm = conn.prepareStatement(sql);
int datatypes[] = new int[columns.length];
for (int i=0; i < columns.length; i++) {
Table tbl = project.getTableByName(table);
if (tbl == null)
throw new OntopiaRuntimeException("Unknown table: " + table);
Column col = tbl.getColumnByName(columns[i]);
if (col == null)
throw new OntopiaRuntimeException("Unknown table column: " + columns[i]);
if (col.getType() == null)
throw new OntopiaRuntimeException("Column type is null: " + col.getType());
DataType datatype = project.getDataTypeByName(col.getType(), "generic");
if (datatype == null)
throw new OntopiaRuntimeException("Unknown column type: " + col.getType());
String dtype = datatype.getType();
if ("varchar".equals(dtype))
datatypes[i] = Types.VARCHAR;
else if ("integer".equals(dtype))
datatypes[i] = Types.INTEGER;
else
throw new OntopiaRuntimeException("Unknown datatype: "+ dtype);
}
LineNumberReader reader = new LineNumberReader(new InputStreamReader(csvfile));
// Ignore first X lines
for (int i=0; i < ignorelines; i++) {
String line = reader.readLine();
if (line == null) break;
}
// Process input
log.debug("[" + StringUtils.join(columns, ", ") + "]");
int lineno = 0;
while (true) {
lineno++;
String line = reader.readLine();
if (line == null) break;
try {
String[] cols = StringUtils.split(line, separator);
if (cols.length > columns.length && !ignorecolumns)
log.debug("Ignoring columns: " + (columns.length+1) + "-" + cols.length + " '" + line + "'");
log.debug("CVALUES: " + (columns.length+1) + "-" + cols.length + " '" + line + "'");
String dmesg = "(";
for (int i=0; i < columns.length; i++) {
String col = cols[i];
// If first column character is '"' strip quotes.
if (stripquotes) {
int len = col.length();
if (len > 1 &&
((col.charAt(0) == '"' && col.charAt(len-1) == '"') ||
(col.charAt(0) == '\''&& col.charAt(len-1) == '\'')))
col = col.substring(1,len-1);
}
if (col != null && col.equals(""))
col = null;
dmesg = dmesg + col;
if (i < columns.length-1)
dmesg = dmesg + ", ";
stm.setObject(i+1, col, datatypes[i]);
}
dmesg = dmesg + ")";
log.debug(dmesg);
stm.execute();
} catch (Exception e) {
conn.rollback();
throw new OntopiaRuntimeException("Cannot read line " + lineno + ": '" + line + "'", e);
}
}
conn.commit();
} finally {
if (conn != null) conn.close();
}
}
protected static void usage() {
System.out.println("java net.ontopia.persistence.rdbms.CSVImport [options] <schema> <dbprops> <csvfile> <table> <columns>");
System.out.println("");
System.out.println(" Tool for importing semicolon separated files into tables.");
System.out.println("");
System.out.println(" Options:");
CmdlineUtils.printLoggingOptionsUsage(System.out);
System.out.println(" --separator=<sep>: specifies the columns separator (default: ';')");
System.out.println(" --stripquotes: if specified quotes around column values will be stripped");
System.out.println(" --ignorelines=<count>: ignore the first <count> lines");
System.out.println("");
System.out.println(" <schema>: schema description file");
System.out.println(" <dbprops>: filename of database properties file");
System.out.println(" <csvfile>: semicolon separated data filename");
System.out.println(" <table>: name of table to import into");
System.out.println(" <columns>: ordered list of columns to import (comma-separated)");
}
private static class OptionsListener implements CmdlineOptions.ListenerIF {
boolean stripquotes = false;
String separator = ";";
int ignorelines = 0;
public void processOption(char option, String value) throws CmdlineOptions.OptionsException {
if (option == 's')
separator = value;
else if (option == 'q')
stripquotes = true;
else if (option == 'i')
ignorelines = Integer.parseInt(value);
}
}
}