/*
* Convert.java -- Converter class - Filemaker to SQL Converter
* Copyright (C) 2003 Robert Gordesch (rogo@mpiwg-berlin.mpg.de)
* This program is free software; you can redistribute it and/or modify it
* under the terms of the GNU General Public License as published by the Free
* Software Foundation; either version 2 of the License, or (at your option)
* any later version. Please read license.txt for the full details. A copy of
* the GPL may be found at http://www.gnu.org/copyleft/lgpl.html You should
* have received a copy of the GNU General Public License along with this
* program; if not, write to the Free Software Foundation, Inc., 59 Temple
* Place, Suite 330, Boston, MA 02111-1307 USA Created on 15.09.2003 by
* rogo
*/
import java.awt.Cursor;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.PrintStream;
import java.io.UnsupportedEncodingException;
import java.net.URL;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
import java.util.StringTokenizer;
import java.util.TreeSet;
import java.util.Vector;
import com.exploringxml.xml.Node;
import com.exploringxml.xml.Xparse;
class Convert
{
static DBBean bean = new DBBean();
static DBBean beanDest = new DBBean();
static String user = "", passwd = "e1nste1n";
static String userDest = "postgres", passwdDest = "rogo";
static boolean batchRun = false;
static Vector databases = new Vector();
final static int numHits = 5000;
final static int numIntervalls = 2;
public static void main(String args[])
{
/* try
{
//byte[] b = "ö".getBytes("UTF-8");
// System.out.println("QueryString " +b[0]+" "+b[1]+(new String(b).getBytes()[0])+" "+new String(b).getBytes()[1]);
//System.out.println(new String(b,"UTF-8"));
} catch (UnsupportedEncodingException e)
{
e.printStackTrace();
}*/
FileOutputStream file = null;
if (args.length != 1)
{
System.out.println("Usage: java Convert <xml config file>");
System.exit(-1);
}
try
{
file = new FileOutputStream("./log.txt");
} catch (FileNotFoundException e1)
{
e1.printStackTrace();
}
PrintStream stream = new PrintStream(file);
// System.setOut(stream);
//System.setErr(stream);
readXMLFile(args[0]);
if (!(new File(args[0]).exists()))
System.exit(0);
System.out.println("Finished!");
//convert("jdbc:fmpro:http://141.14.237.74:8050","jdbc:postgresql://erebos/test",null,null);
}
public static void convertBatch(DBBean source, DBBean destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode, String delimiter) throws Exception
{
bean = source;
beanDest = destination;
convert(null, null, names, layouts, selects, creates, ids, mode, delimiter);
if (true)
return;
StringBuffer command = null;
try
{
bean.setConnection(source.url);
if (names == null)
names = bean.getTableNames();
//Collections.sort(names);
int tbIndex = 1;
for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
{
Vector[] result = null;
try
{
String query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
query = (selects != null) ? selects.get(tbIndex).toString() : query;
//if vectors[1].get(i) != null)
if (!layout.equals(""))
{
System.out.println("before " + query + " table" + names.get(tbIndex));
layout = " layout " + bean.getQC() + layout + bean.getQC();
String name = names.get(tbIndex).toString();
StringBuffer queryLayout = new StringBuffer(query);
queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
query = queryLayout.toString();
System.out.println("added layout " + query);
}
System.out.println(" performing query " + query);
//result = bean.getQueryData(query, null, 0);
bean.getConnection();
bean.makeQuery(query, 0);
} catch (Exception e)
{
System.out.println(e.getMessage());
e.printStackTrace();
continue;
}
//beanDest.setConnection("jdbc:postgresql://erebos/test3");
beanDest.setConnection(destination.url);
Statement stm = beanDest.getConnection().createStatement();
Vector tables = beanDest.getTableNames();
// Collections.sort(tables);
System.out.println("converting table " + names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
tables = beanDest.getTableNames();
// System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
stm = beanDest.getConnection().createStatement();
// System.exit(0);
if (mode == Convert.DataBase.CONVERT_MODE)
{
if (tables.indexOf(names.get(tbIndex)) >= 0)
{
stm.executeUpdate("drop table " + beanDest.getQC() + names.get(tbIndex) + beanDest.getQC());
tables.remove((String) names.get(tbIndex));
System.out.println("dropped table " + names.get(tbIndex));
} else if (tables.indexOf(convertText(names.get(tbIndex).toString())) >= 0)
{
stm.executeUpdate("drop table " + beanDest.getQC() + convertText((String) names.get(tbIndex)) + beanDest.getQC());
tables.remove(convertText((String) names.get(tbIndex)));
System.out.println("dropped table " + names.get(tbIndex));
}
if (tables.indexOf(names.get(tbIndex)) < 0 && tables.indexOf(convertText(names.get(tbIndex).toString())) < 0)
{
if (creates.get(tbIndex).equals("") || creates.get(tbIndex).toString().toLowerCase().indexOf("create") < 0)
{
System.out.println("Warning empty or invalid create statement - creating one for you\n");
command = new StringBuffer(50);
command.append("CREATE TABLE ");
command.append(beanDest.getQC());
command.append(convertText((String) names.get(tbIndex)));
command.append(beanDest.getQC());
command.append("(");
String type = null;
Vector columnNames = bean.getColumnNames();
for (int i = 0; i < columnNames.size() - 1; ++i)
{
type = bean.metaData.getColumnTypeName(i + 1);
// System.out.println(i+" "+result[1].get(i)+" "+type);
type = (type.equals("NUMBER")) ? "INT4" : type;
type = (type.equals("CONTAINER")) ? "TEXT" : type;
command.append(beanDest.getQC() + convertText((String) columnNames.get(i)) + beanDest.getQC() + " " + type + ", ");
}
type = bean.metaData.getColumnTypeName(columnNames.size());
type = (type.equals("NUMBER")) ? "INT4" : type;
type = (type.equals("CONTAINER")) ? "TEXT" : type;
command.append(beanDest.getQC() + convertText((String) columnNames.get(columnNames.size() - 1)) + beanDest.getQC() + " " + type);
command.append(" )");
} else
command = new StringBuffer().append(creates.get(tbIndex).toString());
System.out.println(command);
// System.exit(0);
//command.append(DBBean.getQC());
stm.executeUpdate(command.toString());
}
}
Vector row = null;
command = new StringBuffer();
command.append("INSERT INTO ");
command.append(beanDest.getQC());
command.append(convertText((String) names.get(tbIndex)));
command.append(beanDest.getQC());
command.append(" values ( ");
for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
command.append("?,");
command.append("?)");
PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
System.out.println(command);
while ((row = bean.getNextRow()) != null)
{
//print rows
Object obj = null;
for (int k = 0; k < row.size(); ++k)
{
obj = row.get(k);
if (obj instanceof ArrayList)
obj = formatFileMakerArray((List) obj, "\n");
String str = (obj == null) ? "NULL" : obj.toString();
if (!str.equals("NULL"))
pstm.setString(k + 1, str);
else
pstm.setNull(k + 1, Types.NULL);
}
pstm.execute();
} // to for loop
}
} catch (Exception e)
{
System.out.println("Error while connecting to database " + e);
//dialog.setVisible(false);
//dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
//FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
java.io.PrintStream stream = new java.io.PrintStream(b);
stream.print(command + "\n\n");
e.printStackTrace(stream);
System.err.println(b);
//FM2SQL.showErrorDialog(b.toString(), "Error occured !");
}
// dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
//FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
// dialog.setVisible(false);
}
public static String formatFileMakerArray(List list, String delimiter)
{
StringBuffer formattedString = new StringBuffer();
for (int i = 0; i < list.size(); ++i)
{
formattedString.append(list.get(i).toString());
if (i < list.size() - 1)
formattedString.append(delimiter);
}
return formattedString.toString();
}
/**
* Method for SQL UPDATE
* @param source
* @param destination
* @param names
* @param layouts
* @param selects
* @param creates
* @param ids
* @param mode
* @throws Exception
*/
public static void update(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
{
FM2SQL.ProgressDialog dialog = null;
if (FM2SQL.fmInstance != null)
{
dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
dialog.setTitle("Conversion running ...");
dialog.title.setText("Getting table data ...");
dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
dialog.thread = Thread.currentThread();
}
// setting user and passwd
bean.setUserAndPasswd(user, passwd);
// setting user and passwd
beanDest.setUserAndPasswd(userDest, passwdDest);
if (dialog != null)
dialog.setSize(400, 250);
StringBuffer command = null;
String query = null;
try
{
//bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
//bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
bean.setConnection(source);
if (names == null)
names = bean.getTableNames();
// Collections.sort(names);
int tbIndex = 1;
// System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
{
Vector[] result = null;
String destTableName = "";
try
{
query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
query = (selects != null) ? selects.get(tbIndex).toString() : query;
//if vectors[1].get(i) != null)
if (layout != "")
{
layout = " layout " + bean.getQC() + layout + bean.getQC();
String name = names.get(tbIndex).toString();
StringBuffer queryLayout = new StringBuffer(query);
queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
query = queryLayout.toString();
System.out.println("added layout " + query);
}
dialog.title.setText("Getting table data ...");
dialog.table.setText(names.get(tbIndex).toString());
dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
dialog.show();
bean.getConnection();
bean.makeQuery(query, 0);
} catch (Exception e)
{
continue;
}
// determine destTableName from createStatement or from source table name
if (!creates.get(tbIndex).equals(""))
{
String create = creates.get(tbIndex).toString().toLowerCase();
int fromIndex = create.indexOf("table") + 5;
int toIndex = create.indexOf("(");
destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
System.out.println("destTable " + destTableName);
} else
destTableName = convertText(names.get(tbIndex).toString());
//beanDest.setConnection("jdbc:postgresql://erebos/test3");
beanDest.setConnection(destination);
Statement stm = beanDest.getConnection().createStatement();
Vector tables = beanDest.getTableNames();
// Collections.sort(tables);
System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
tables = beanDest.getTableNames();
// System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
stm = beanDest.getConnection().createStatement();
// System.exit(0);
if (dialog != null)
dialog.title.setText("Updating table data ...");
int j = -1;
Vector row = null;
command = new StringBuffer();
command.append("UPDATE ");
command.append(beanDest.getQC());
command.append(destTableName);
//command.append(convertText((String) names.get(tbIndex)));
command.append(beanDest.getQC());
command.append(" SET ");
int size = bean.getColumnNames().size();
for (int i = 0; i < size - 1; ++i)
command.append(beanDest.getQC() + convertText((String) bean.getColumnNames().get(i)) + beanDest.getQC() + " = ? ,");
command.append(convertText((String) bean.getColumnNames().get(size - 1)) + " = ? ");
command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
System.out.println(command + " " + tbIndex);
int rowCount = bean.getRowCount(query);
int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
while ((row = bean.getNextRow()) != null)
{
j++;
//print rows
Object obj = null;
/* for(int k=0;k<row.size()-1;++k)
{
obj = row.get(k);
//System.out.println("row "+obj+" "+k);
if(obj!=null&&!(obj instanceof ArrayList))
command.append("'"+convertUml(obj.toString())+"',");
else if(obj!=null&& obj instanceof ArrayList)
command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',");
else command.append("NULL,");
}
obj = row.get(row.size() - 1);
if (obj != null && !(obj instanceof ArrayList))
command.append("'"+convertUml(obj.toString())+"')");
else
if(obj!=null&& obj instanceof ArrayList)
command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')"); //command.append(obj.toString()+")");
else command.append("NULL)");
*/
//command.append("'"+row.get(row.size()-1)+"')");
//command.append(" )");
// for(int k=0;k<row.size();++k)
// System.out.println();
// System.out.println(command+" "+j+" "+row.size()+" "+ ((Vector)result2[0].get(j)).size());
// System.out.println(command);
for (int k = 0; k < row.size(); ++k)
{
obj = row.get(k);
if (obj instanceof ArrayList)
obj = ((List) obj).get(0);
String str = (obj == null) ? "NULL" : obj.toString();
if (!str.equals("NULL"))
pstm.setString(k + 1, str);
else
pstm.setNull(k + 1, Types.NULL);
}
pstm.setString(row.size() + 1, row.get(idIndex).toString());
//System.out.println(pstm.toString());
// System.exit(0);
pstm.execute();
//stm.executeUpdate(command.toString());
if (dialog != null)
dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
// System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
command = null;
} // to for loop
}
} catch (Exception e)
{
System.out.println("Error while connecting to database " + e);
if (dialog != null)
{
dialog.setVisible(false);
dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
}
java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
java.io.PrintStream stream = new java.io.PrintStream(b);
stream.print(command + "\n\n");
e.printStackTrace(stream);
FM2SQL.showErrorDialog(b.toString(), "Error occured !");
}
if (dialog != null)
{
dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
dialog.setVisible(false);
}
}
/**
* transfers the specified array of tables to the destination database
and creates the table if it does not exist if it exists and mode is not append the table is dropped
* @param source
* @param destination
* @param names
* @param layouts
* @param selects
* @param creates
* @param ids
* @param mode
* @throws Exception
*/
public static void convert(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode, String delimiter) throws Exception
{
FM2SQL.ProgressDialog dialog = null;
if (FM2SQL.fmInstance != null)
{
dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
dialog.setTitle("Conversion running ...");
dialog.title.setText("Getting table data ...");
dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
dialog.thread = Thread.currentThread();
dialog.setSize(400, 250);
}
java.util.TreeSet myIds = new TreeSet();
int deltaID = 1;
String idField = "";
String destTableName = "";
String[] fieldNames = null;
if (source != null && destination != null)
{
// setting user and passwd
bean.setUserAndPasswd(user, passwd);
// setting user and passwd
beanDest.setUserAndPasswd(userDest, passwdDest);
}
StringBuffer command = null;
String query = null;
try
{
if (source != null)
bean.setConnection(source);
else
bean.setConnection(bean.url);
if (names == null)
names = bean.getTableNames();
// Collections.sort(names);
int tbIndex = 1;
// System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
{
Vector[] result = null;
try
{
query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
query = (selects != null) ? selects.get(tbIndex).toString() : query;
//if vectors[1].get(i) != null)
if (layout != "")
{
layout = " layout " + bean.getQC() + layout + bean.getQC();
String name = names.get(tbIndex).toString();
StringBuffer queryLayout = new StringBuffer(query);
queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
query = queryLayout.toString();
System.out.println("added layout " + query);
}
// if ( layout!= "")
// query += " layout " + bean.getQC() + layout + bean.getQC();
if (dialog != null)
{
dialog.title.setText("Reading table data ...");
dialog.table.setText(names.get(tbIndex).toString());
dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
dialog.show();
}
//result = bean.getQueryData(query, dialog, 0);
bean.getConnection();
bean.makeQuery(query, 50);
idField = ids.get(tbIndex).toString();
} catch (Exception e)
{
System.out.println(e);
continue;
}
if (destination != null)
beanDest.setConnection(destination);
else
beanDest.setConnection(beanDest.url);
Statement stm = beanDest.getConnection().createStatement();
Vector tables = beanDest.getTableNames();
// Collections.sort(tables);
System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
tables = beanDest.getTableNames();
// System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
stm = beanDest.getConnection().createStatement();
// System.exit(0);
// determine destTableName from createStatement or from source table name
ConversionProperties prop = getFieldNamesAndDestTableName(creates.get(tbIndex).toString(), query, names.get(tbIndex).toString());
destTableName = prop.destTableName;
fieldNames = prop.fieldNames;
if (mode == Convert.DataBase.CONVERT_MODE)
{
if (tables.indexOf(destTableName) >= 0)
{
stm.executeUpdate("drop table " + beanDest.getQC() + destTableName + beanDest.getQC());
tables.remove(destTableName);
System.out.println("dropped table" + destTableName);
}
/*
if(destTableName.equals(""))
if (tables.indexOf(names.get(tbIndex)) >= 0)
{
stm.executeUpdate("drop table " + beanDest.getQC() + names.get(tbIndex) + beanDest.getQC());
tables.remove((String) names.get(tbIndex));
System.out.println("dropped table" + names.get(tbIndex));
} else if (tables.indexOf(convertText(names.get(tbIndex).toString())) >= 0)
{
stm.executeUpdate("drop table " + beanDest.getQC() + convertText((String) names.get(tbIndex)) + beanDest.getQC());
tables.remove(convertText((String) names.get(tbIndex)));
System.out.println("dropped table" + names.get(tbIndex));
}
*/
if ((tables.indexOf(destTableName) < 0)) //&& tables.indexOf(names.get(tbIndex)) < 0 && tables.indexOf(convertText(names.get(tbIndex).toString())) < 0 )
{
if (creates.get(tbIndex).equals("") || creates.get(tbIndex).toString().toLowerCase().indexOf("create") < 0)
{
System.out.println("Warning empty or invalid create statement - creating one for you\n");
command = new StringBuffer(50);
command.append("CREATE TABLE ");
command.append(beanDest.getQC());
command.append(convertText((String) names.get(tbIndex)));
command.append(beanDest.getQC());
command.append("(");
String type = null;
Vector columnNames = bean.getColumnNames();
for (int i = 0; i < columnNames.size() - 1; ++i)
{
type = bean.metaData.getColumnTypeName(i + 1);
// System.out.println(i+" "+result[1].get(i)+" "+type);
type = (type.equals("NUMBER")) ? "INT4" : type;
type = (type.equals("CONTAINER")) ? "TEXT" : type;
command.append(beanDest.getQC() + convertText((String) columnNames.get(i)) + beanDest.getQC() + " " + type + ", ");
}
type = bean.metaData.getColumnTypeName(columnNames.size());
type = (type.equals("NUMBER")) ? "INT4" : type;
type = (type.equals("CONTAINER")) ? "TEXT" : type;
command.append(beanDest.getQC() + convertText((String) columnNames.get(columnNames.size() - 1)) + beanDest.getQC() + " " + type);
command.append(" )");
// System.out.println(command);
// System.exit(0);
//command.append(DBBean.getQC());
} else
command = new StringBuffer().append(creates.get(tbIndex).toString().toLowerCase());
stm.executeUpdate(command.toString());
}
}
if (dialog != null)
dialog.title.setText("Writing table data ...");
// prepare the insert statement
int j = -1;
Vector row = null;
command = new StringBuffer();
command.append("INSERT INTO ");
command.append(beanDest.getQC());
command.append(destTableName); //convertText((String) names.get(tbIndex)));
command.append(beanDest.getQC());
command.append(" (");
for (int i = 0; i < fieldNames.length; i++)
{
command.append(fieldNames[i]);
if (i < fieldNames.length - 1)
command.append(",");
}
command.append(") ");
command.append(" values ( ");
// add a question marks for every field
for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
command.append("?,");
command.append("?)");
PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
System.out.println(command);
int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
Vector vec = new Vector(myIds);
int endIndex = -1;
String tempQuery = query;
String tempID = bean.getQC() + idField + bean.getQC();
// if id_field not do incremental conversion else do it all at once
if (!idField.equals(""))
{
long startTime = System.currentTimeMillis();
int counter = -1;
while (true)
{
++counter;
if (counter == 0 && dialog != null)
dialog.title.setText("Check if data is available");
else if (dialog != null)
dialog.title.setText("Check if more data is available");
myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, numHits);
if (myIds.isEmpty())
break;
vec = new Vector(myIds);
rowCount = vec.size();
System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIds.size());
deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
if (vec.size() <= numIntervalls)
{
endIndex = 0;
deltaID = vec.size();
}
for (int k = 0; k < vec.size() - deltaID; k = k + deltaID)
{
System.out.println(vec.get(k) + " " + vec.get(k + deltaID) + " " + vec.lastElement());
if (query.indexOf("where") > 0)
tempQuery = query + " and " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID) + "'";
else
tempQuery = query + " where " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID) + "'";
System.out.println(tempQuery);
if (dialog != null)
dialog.title.setText("Reading table data ...");
bean.makeQuery(tempQuery, deltaID);
if (dialog != null)
dialog.title.setText("Writing table data ...");
command = writeDatainDestTable(dialog, command, k, pstm, rowCount, delimiter);
endIndex = k + deltaID;
}
System.out.println(endIndex);
//all data written ? if not write last chunk of data
if (endIndex == vec.size() - 1)
System.out.println("fits");
else
{
System.out.println(" last intervall from " + vec.get(endIndex) + " " + vec.lastElement());
if (query.indexOf("where") > 0)
tempQuery = query + " and " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement() + "'";
else
tempQuery = query + " where " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement() + "'";
System.out.println(tempQuery);
if (dialog != null)
dialog.title.setText("Reading table data ...");
bean.makeQuery(tempQuery, 0);
if (dialog != null)
dialog.title.setText("Writing table data ...");
command = writeDatainDestTable(dialog, command, endIndex, pstm, rowCount, delimiter);
}
// prepare new query for next chunk
if (query.indexOf("where") > 0)
tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
else
tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
}
long endTime = System.currentTimeMillis();
System.out.println("Time for incremental convert elapsed " + (endTime - startTime));
} else
{
// read and write all in one big chunk
long startTime = System.currentTimeMillis();
bean.makeQuery(query, 0);
command = writeDatainDestTable(dialog, command, j, pstm, rowCount, delimiter);
long endTime = System.currentTimeMillis();
System.out.println("Time for old convert elapsed " + (endTime - startTime));
}
}
} catch (Exception e)
{
System.out.println("Error while connecting to database " + e);
if (dialog != null)
{
dialog.setVisible(false);
dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
java.io.PrintStream stream = new java.io.PrintStream(b);
stream.print(command + "\n\n");
e.printStackTrace(stream);
FM2SQL.showErrorDialog(b.toString(), "Error occured !");
} else
{
e.printStackTrace();
}
}
if (dialog != null)
{
dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
dialog.setVisible(false);
}
}
/**
* Writes data to the destination table
* @param dialog progress dialog
* @param command
* @param j data index for progress bar
* @param pstm prepared statement
* @param rowCount number of datasets
* @return command
* @throws Exception
* @throws SQLException
*/
private static StringBuffer writeDatainDestTable(FM2SQL.ProgressDialog dialog, StringBuffer command, int j, PreparedStatement pstm, int rowCount, String delimiter) throws Exception, SQLException
{
Vector row;
while ((row = bean.getNextRow()) != null)
{
j++;
// row = (Vector) result[0].get(j);
/* command = new StringBuffer();
command.append("INSERT INTO ");
command.append(beanDest.getQC());
command.append(convertText((String) names.get(tbIndex)));
command.append(beanDest.getQC());
command.append(" values ( ");
*/
//print rows
Object obj = null;
/* for(int k=0;k<row.size()-1;++k)
{
obj = row.get(k);
//System.out.println("row "+obj+" "+k);
if(obj!=null&&!(obj instanceof ArrayList))
command.append("'"+convertUml(obj.toString())+"',");
else if(obj!=null&& obj instanceof ArrayList)
command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',");
else command.append("NULL,");
}
obj = row.get(row.size() - 1);
if (obj != null && !(obj instanceof ArrayList))
command.append("'"+convertUml(obj.toString())+"')");
else
if(obj!=null&& obj instanceof ArrayList)
command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')"); //command.append(obj.toString()+")");
else command.append("NULL)");
*/
//command.append("'"+row.get(row.size()-1)+"')");
//command.append(" )");
// for(int k=0;k<row.size();++k)
// System.out.println();
// System.out.println(command+" "+j+" "+row.size()+" "+ ((Vector)result2[0].get(j)).size());
// System.out.println(command);
for (int k = 0; k < row.size(); ++k)
{
obj = row.get(k);
if (obj instanceof ArrayList)
obj = formatFileMakerArray((List) obj, delimiter);
String str = (obj == null) ? "NULL" : obj.toString();
if (obj instanceof Double)
{
pstm.setDouble(k + 1, ((Double) obj).doubleValue());
} else if (!str.equals("NULL"))
pstm.setString(k + 1, str);
else
pstm.setNull(k + 1, Types.NULL);
}
pstm.execute();
//stm.executeUpdate(command.toString());
if (dialog != null)
dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
// System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
command = null;
} // to while loop
return command;
}
/**
* removes special characters from the input string as well as .fp5
* @param newName String to change
* @return
*/
public static String convertText(String newName)
{
StringBuffer alterMe = new StringBuffer(newName.trim().toLowerCase());
int length = alterMe.length();
int j = 0;
int index = alterMe.indexOf(".fp5");
if (index >= 0)
{
alterMe.delete(index, index + 4);
length = length - 4;
}
while (j < length)
{
if (alterMe.charAt(j) == ' ')
{
alterMe.setCharAt(j, '_');
// if(j<length-1) j=j+1;
} else if (alterMe.charAt(j) == '_')
{
if (alterMe.charAt(j + 1) == '_')
alterMe.deleteCharAt(j);
length = length - 1;
// if(j<length-1) j=j+1;
} else if (alterMe.charAt(j) == 'ä')
{
alterMe.setCharAt(j, 'a');
alterMe.insert(j + 1, "e");
length = length + 1;
if (j < length - 1)
j = j + 1;
} else if (alterMe.charAt(j) == 'ö')
{
alterMe.setCharAt(j, 'o');
alterMe.insert(j + 1, "e");
length = length + 1;
if (j < length - 1)
j = j + 1;
} else if (alterMe.charAt(j) == 'ü')
{
alterMe.setCharAt(j, 'u');
alterMe.insert(j + 1, "e");
length = length + 1;
if (j < length - 1)
j = j + 1;
} else if (alterMe.charAt(j) == 'ß')
{
alterMe.setCharAt(j, 's');
alterMe.insert(j + 1, "s");
length = length + 1;
if (j < length - 1)
j = j + 1;
} else if (alterMe.charAt(j) == ':')
{
if (j < length - 1)
{
if (alterMe.charAt(j + 1) == ':')
{
alterMe.setCharAt(j, '_');
alterMe.delete(j + 1, j + 2);
length = length - 1;
}
if (j < length - 1)
j = j + 1;
}
} else if (alterMe.charAt(j) == '-')
{
alterMe.setCharAt(j, '_');
} else if (alterMe.charAt(j) == '?')
{
// changed ? to _ because of update statement
alterMe.setCharAt(j, '_');
// length = length + 1;
// j=j+1;
System.out.println(alterMe);
} else if (alterMe.charAt(j) == '.')
{
if (j == length - 1)
{
alterMe.delete(j, j);
length--;
} else
alterMe.setCharAt(j, '_');
}
++j;
}
return alterMe.toString();
}
/**
* Converts > and < in an entity (> or <)
* @param newName
* @return
*/
public static String convertToEntities(String newName)
{
StringBuffer alterMe = new StringBuffer(newName.trim());
int length = alterMe.length();
int j = 0;
while (j < length)
{
if (alterMe.charAt(j) == '>')
{
alterMe.setCharAt(j, '&');
alterMe.insert(j + 1, "gt;");
length = length + 2;
if (j < length - 1)
j = j + 1;
} else if (alterMe.charAt(j) == '<')
{
alterMe.setCharAt(j, '&');
alterMe.insert(j + 1, "lt;");
length = length + 2;
if (j < length - 1)
j = j + 1;
}
++j;
}
return alterMe.toString();
}
/**
* Masks the single quote character '-->\'
* @param newName
* @return
*/
public static String convertUml(String newName)
{
StringBuffer alterMe = new StringBuffer(newName.trim());
int length = alterMe.length();
int j = 0;
while (j < length)
{
if (alterMe.charAt(j) == '\'')
{
alterMe.setCharAt(j, '\\');
alterMe.insert(j + 1, "'");
length = length + 1;
if (j < length - 1)
j = j + 1;
}
/* else
if (alterMe.charAt(j) == '"')
{
alterMe.setCharAt(j, '\\');
alterMe.insert(j + 1, "\"");
length = length + 1;
if(j<length-1) j=j+1;
}
else
if (alterMe.charAt(j) == '>')
{
alterMe.setCharAt(j, '\\');
alterMe.insert(j + 1, ">");
length = length + 1;
if(j<length-1) j=j+1;
}
else
if (alterMe.charAt(j) == '<')
{
alterMe.setCharAt(j, '\\');
alterMe.insert(j + 1, "<");
length = length + 1;
if(j<length-1) j=j+1;
}
else
if (alterMe.charAt(j) == '?')
{
alterMe.setCharAt(j, '\\');
alterMe.insert(j + 1, "?");
length = length + 1;
if(j<length-1) j=j+1;
}
else
if (alterMe.charAt(j) == '&')
{
alterMe.setCharAt(j, '\\');
alterMe.insert(j + 1, "&");
length = length + 1;
if(j<length-1) j=j+1;
}
else
if (alterMe.charAt(j) == '=')
{
alterMe.setCharAt(j, '\\');
alterMe.insert(j + 1, "=");
length = length + 1;
if(j<length-1) j=j+1;
}
else
if (alterMe.charAt(j) == ',')
{
alterMe.setCharAt(j, '\\');
alterMe.insert(j + 1, ",");
length = length + 1;
if(j<length-1) j=j+1;
}
else
if (alterMe.charAt(j) == '.')
{
alterMe.setCharAt(j, '\\');
alterMe.insert(j + 1, ".");
length = length + 1;
if(j<length-1) j=j+1;
}
else
if (alterMe.charAt(j) == '[')
{
alterMe.setCharAt(j, '\\');
alterMe.insert(j + 1, ".");
length = length + 1;
if(j<length-1) j=j+1;
}
else
if (alterMe.charAt(j) == ']')
{
alterMe.setCharAt(j, '\\');
alterMe.insert(j + 1, ".");
length = length + 1;
if(j<length-1) j=j+1;
}
else
if (alterMe.charAt(j) == '%')
{
alterMe.setCharAt(j, '\\');
alterMe.insert(j + 1, "%");
length = length + 1;
if(j<length-1) j=j+1;
}*/
++j;
}
return alterMe.toString();
}
/**
* parses the input xml file for batch conversion
* called from readXMLFile
* * @param sb
*/
public static void parseXMLConfig(StringBuffer sb)
{
boolean finished = false;
// parse string and build document tree
Xparse parser = new Xparse();
parser.changeEntities = true;
Node root = parser.parse(sb.toString());
// printContents(root);
Vector databases = new Vector();
Vector tables = new Vector();
Vector layouts = new Vector();
Vector selects = new Vector();
Vector creates = new Vector();
Vector ids = new Vector();
String delimiter = "|";
int mode = -1;
try
{
Node tempNode = root.find("convert/source", new int[] { 1, 1 });
if (tempNode == null)
throw new Error("parse error source tag missing");
System.out.println(tempNode.name);
int length = countNodes(tempNode);
for (int i = 1; i <= length; i++)
{
DBBean database = new DBBean();
tables = new Vector();
layouts = new Vector();
selects = new Vector();
creates = new Vector();
ids = new Vector();
// parse dataBase
Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
Node node3 = root.find("convert/source/database", new int[] { 1, 1, i });
Node nodeMode = root.find("convert/source/database/mode", new int[] { 1, 1, i, 1, 1 });
Node delimiterNode = root.find("convert/source/database/delimiter", new int[] { 1, 1, i, 1, 1 });
Node useNormanToUnicodeMapper = root.find("convert/source/database/usenormantounicodemapper", new int[] { 1, 1, i, 1, 1 });
if (node3 == null)
throw new Error("parse error database tag missing");
if (node == null)
throw new Error("parse error url tag missing");
if (node1 == null)
throw new Error("parse error user tag missing");
if (node2 == null)
throw new Error("parse error password tag missing");
if (delimiterNode != null)
delimiter = delimiterNode.getCharacters();
if (useNormanToUnicodeMapper != null)
{
database.setUseNormanToUnicodeMapper(Boolean.valueOf(useNormanToUnicodeMapper.getCharacters()).booleanValue());
System.out.println("useMapper " + Boolean.valueOf(useNormanToUnicodeMapper.getCharacters().trim()).booleanValue());
}
String url = node.getCharacters();
String user = node1.getCharacters();
String password = node2.getCharacters();
database.setURL(url.trim());
database.setUserAndPasswd(user.trim(), password.trim());
System.out.println(node.name + " " + node.getCharacters());
System.out.println(node1.name + " " + node1.getCharacters());
System.out.println(node2.name + " " + node2.getCharacters());
String modeString = "";
if (nodeMode == null)
modeString = "convert";
else
modeString = nodeMode.getCharacters();
if (modeString.equals("convert"))
mode = DataBase.CONVERT_MODE;
else if (modeString.equals("append"))
mode = DataBase.APPEND_MODE;
else if (modeString.equals("update"))
mode = DataBase.UPDATE_MODE;
else if (modeString.equals("delete"))
mode = DataBase.DELETE_MODE;
else if (modeString.equals("synchronize"))
mode = DataBase.SYNCHRONIZE_MODE;
// if(node3!=null)
// System.out.println(node3.name);
int length2 = countNodes(node3);
System.out.println("number of tables " + length2);
for (int j = 1; j <= length2; ++j)
{
Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
if (node4 != null)
System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
if (node5 != null)
System.out.println(node5.name + " " + node5.getCharacters());
if (node6 != null)
System.out.println(node6.name + " " + node6.getCharacters());
if (node4 == null)
throw new Error("parse error table tag missing");
// if(node5==null) throw new Error("parse error select tag missing");
// if(node6==null) throw new Error("parse error create tag missing");
String name = (String) node4.attributes.get("name");
String layout = (String) node4.attributes.get("layout");
String id = (String) node4.attributes.get("id");
System.out.println("id was " + id);
if (name == null)
throw new Error("parse error required table tag attribute name missing");
if (layout == null)
layout = "";
if (id == null)
id = "";
if (name.equals(""))
throw new Error("parse error table tag attribute must not be empty");
tables.add(name);
layouts.add(layout);
ids.add(id);
String query = (node5 == null) ? "" : node5.getCharacters();
if (query.equals(""))
System.err.println("Warning empty select tag or select tag missing !!");
query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
selects.add(query);
if (node6 != null)
creates.add(node6.getCharacters().trim());
else
creates.add("");
}
DataBase dataBase = new DataBase(database, tables, layouts, selects, creates, ids, mode);
dataBase.delimiter = delimiter;
databases.add(dataBase);
}
DBBean database = new DBBean();
// parse dataBase
Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
String url = node.getCharacters();
String user = node1.getCharacters();
String password = node2.getCharacters();
System.out.println(url);
database.setURL(url.trim());
database.setUserAndPasswd(user.trim(), password.trim());
//databases.add(database);
for (Iterator iter = databases.iterator(); iter.hasNext();)
{
DataBase db = (DataBase) iter.next();
if (mode == DataBase.CONVERT_MODE || mode == DataBase.APPEND_MODE)
convertBatch(db.bean, database, db.tables, db.layouts, db.selects, db.creates, db.ids, mode, db.delimiter);
else if (mode == DataBase.UPDATE_MODE)
update(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates, db.ids, mode);
else if (mode == DataBase.SYNCHRONIZE_MODE)
{
user = bean.user;
passwd = bean.passwd;
userDest = database.user;
passwdDest = database.passwd;
synchronize(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates, db.ids, mode, db.delimiter);
}
}
// printContents(node3);
// FM2SQL.fmInstance=new FM2SQL();
} catch (Exception e)
{
e.printStackTrace();
} finally
{
bean.closeAllConnections();
beanDest.closeAllConnections();
}
}
public static Vector getXMLConfig(String xmlFile)
{
StringBuffer sb = null;
try
{
// read XML Metadata from a file
FileInputStream fi = new FileInputStream(xmlFile);
InputStreamReader isr = new InputStreamReader(fi, "UTF-8");
BufferedReader buffr = new BufferedReader(isr);
sb = new StringBuffer();
int c = 0;
while ((c = buffr.read()) != -1)
{
char ch = (char) c;
sb.append(ch);
// System.out.print((char)c);
}
} catch (Exception e)
{
e.printStackTrace();
}
boolean finished = false;
// parse string and build document tree
Xparse parser = new Xparse();
parser.changeEntities = true;
Node root = parser.parse(sb.toString());
// printContents(root);
Vector databases = new Vector();
Vector tables = new Vector();
Vector layouts = new Vector();
Vector selects = new Vector();
Vector creates = new Vector();
Vector ids = new Vector();
String delimiter = "|";
int mode = -1;
try
{
Node tempNode = root.find("convert/source", new int[] { 1, 1 });
if (tempNode == null)
throw new Error("parse error source tag missing");
System.out.println(tempNode.name);
int length = countNodes(tempNode);
for (int i = 1; i <= length; i++)
{
DBBean database = new DBBean();
tables = new Vector();
layouts = new Vector();
selects = new Vector();
creates = new Vector();
ids = new Vector();
// parse dataBase
Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
Node node3 = root.find("convert/source/database", new int[] { 1, 1, i });
Node nodeMode = root.find("convert/source/database/mode", new int[] { 1, 1, i, 1, 1 });
Node delimiterNode = root.find("convert/source/database/delimiter", new int[] { 1, 1, i, 1, 1 });
Node useNormanToUnicodeMapper = root.find("convert/source/database/usenormantounicodemapper", new int[] { 1, 1, i, 1, 1 });
if (delimiterNode != null)
delimiter = delimiterNode.getCharacters();
if (useNormanToUnicodeMapper != null)
{
database.setUseNormanToUnicodeMapper(Boolean.valueOf(useNormanToUnicodeMapper.getCharacters()).booleanValue());
System.out.println("useMapper " + Boolean.valueOf(useNormanToUnicodeMapper.getCharacters().trim()).booleanValue());
}
if (node3 == null)
throw new Error("parse error database tag missing");
if (node == null)
throw new Error("parse error url tag missing");
if (node1 == null)
throw new Error("parse error user tag missing");
if (node2 == null)
throw new Error("parse error password tag missing");
String url = node.getCharacters();
String user = node1.getCharacters();
String password = node2.getCharacters();
database.setURL(url.trim());
database.setUserAndPasswd(user.trim(), password.trim());
System.out.println(node.name + " " + node.getCharacters());
System.out.println(node1.name + " " + node1.getCharacters());
System.out.println(node2.name + " " + node2.getCharacters());
String modeString = "";
if (nodeMode == null)
modeString = "convert";
else
modeString = nodeMode.getCharacters();
if (modeString.equals("convert"))
mode = DataBase.CONVERT_MODE;
else if (modeString.equals("append"))
mode = DataBase.APPEND_MODE;
else if (modeString.equals("update"))
mode = DataBase.UPDATE_MODE;
else if (modeString.equals("delete"))
mode = DataBase.DELETE_MODE;
// if(node3!=null)
// System.out.println(node3.name);
int length2 = countNodes(node3);
System.out.println("number of tables " + length2);
for (int j = 1; j <= length2; ++j)
{
Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
if (node4 != null)
System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
if (node5 != null)
System.out.println(node5.name + " " + node5.getCharacters());
if (node6 != null)
System.out.println(node6.name + " " + node6.getCharacters());
if (node4 == null)
throw new Error("parse error table tag missing");
// if(node5==null) throw new Error("parse error select tag missing");
// if(node6==null) throw new Error("parse error create tag missing");
String name = (String) node4.attributes.get("name");
String layout = (String) node4.attributes.get("layout");
String id = (String) node4.attributes.get("id");
System.out.println("id was " + id);
if (name == null)
throw new Error("parse error required table tag attribute name missing");
if (layout == null)
layout = "";
if (id == null)
id = "";
if (name.equals(""))
throw new Error("parse error table tag attribute must not be empty");
tables.add(name);
layouts.add(layout);
ids.add(id);
String query = (node5 == null) ? "" : node5.getCharacters();
if (query.equals(""))
System.err.println("Warning empty select tag or select tag missing !!");
query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
selects.add(query);
if (node6 != null)
creates.add(node6.getCharacters().trim());
else
creates.add("");
}
DataBase dataBase = new DataBase(database, tables, layouts, selects, creates, ids, mode);
dataBase.delimiter = delimiter;
databases.add(dataBase);
}
DBBean database = new DBBean();
// parse dataBase
Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
String url = node.getCharacters();
String user = node1.getCharacters();
String password = node2.getCharacters();
System.out.println(url);
database.setURL(url.trim());
database.setUserAndPasswd(user.trim(), password.trim());
databases.add(new DataBase(database, null, null, null, null, null, 0));
//databases.add(database);
/* for (Iterator iter = databases.iterator(); iter.hasNext();)
{
DataBase db = (DataBase) iter.next();
convertBatch(db.bean,database,db.tables,db.layouts,db.selects,db.creates);
}*/
// printContents(node3);
// FM2SQL.fmInstance=new FM2SQL();
} catch (Exception e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
return databases;
}
private static int countNodes(Node tempNode)
{
int length = 0;
for (int i = 0; i < tempNode.contents.v.size(); ++i)
{
Node node = (Node) tempNode.contents.v.elementAt(i);
if (node.type.equals("element"))
{
if (node.name.equals("database"))
length++;
if (node.name.equals("table"))
length++;
}
// System.out.println(((Node)tempNode.contents.v.elementAt(i)).attributes+" "+i);
}
return length;
}
private static void printContents(Node root)
{
Vector contents = (root.index == null) ? root.contents.v : root.index.v;
for (int i = 0; i < contents.size(); ++i)
{
Node n = (Node) contents.elementAt(i);
if (n.type.equals("element"))
{
System.out.println("tag " + n.name);
System.out.println(n.getCharacters());
//contents=n.contents.v i=0;
}
// System.out.println(n.type);
}
}
/**
* reads the specified xml file
* @param xmlFile
*/
public static void readXMLFile(String xmlFile)
{
try
{
InputStream stream = null;
if (xmlFile.indexOf("file://") >= 0 || xmlFile.indexOf("http://") >= 0)
{
URL url = new URL(xmlFile);
stream = url.openStream();
} else
// read XML Metadata from a file
stream = new FileInputStream(xmlFile);
InputStreamReader isr = new InputStreamReader(stream, "UTF-8");
BufferedReader buffr = new BufferedReader(isr);
StringBuffer sb = new StringBuffer();
int c = 0;
while ((c = buffr.read()) != -1)
{
char ch = (char) c;
sb.append(ch);
// System.out.print((char)c);
}
parseXMLConfig(sb);
} catch (Exception e)
{
e.printStackTrace();
}
}
/**
* Helper class for Conversion etc
* Holds the some data
* @author rogo
*
*/
public static class ConversionProperties
{
String destTableName;
String[] fieldNames;
public ConversionProperties()
{
}
public ConversionProperties(String destTableName, String[] fieldNames)
{
this.destTableName = destTableName;
this.fieldNames = fieldNames;
}
}
/**
* Helper class for XML-File parsing
* Holds the parsed data
* @author rogo
*
*/
public static class DataBase
{
DBBean bean;
Vector creates;
Vector selects;
Vector layouts;
Vector tables;
Vector ids;
String delimiter = "//";
boolean useNormanToUnicodeMapper = false;
final static int CONVERT_MODE = 1;
final static int APPEND_MODE = 2;
final static int UPDATE_MODE = 3;
final static int DELETE_MODE = 4;
final static int SYNCHRONIZE_MODE = 5;
int mode = -1;
public DataBase(DBBean bean, Vector tables, Vector layouts, Vector selects, Vector creates, Vector ids, int mode)
{
this.bean = bean;
this.tables = tables;
this.layouts = layouts;
this.selects = selects;
this.creates = creates;
this.ids = ids;
this.mode = mode;
this.bean.setIDVector(ids);
}
/**
* writes the data contained in this object to the buffered writer
* * @param buffr
* @throws Exception
*/
public void exportToXML(BufferedWriter buffr) throws Exception
{
// ids=bean.getIDVector();
buffr.write(" <database>\n");
buffr.write(" <url>" + bean.url + "</url>\n");
buffr.write(" <user>" + bean.user + "</user>\n");
buffr.write(" <password>" + bean.passwd + "</password>\n");
buffr.write(" <delimiter>" + delimiter + "</delimiter>\n");
String modeString = "";
if (mode == CONVERT_MODE)
modeString = "convert";
else if (mode == APPEND_MODE)
modeString = "append";
else if (mode == UPDATE_MODE)
modeString = "update";
else if (mode == DELETE_MODE)
modeString = "delete";
buffr.write(" <mode>" + modeString + "</mode>\n");
buffr.write(" <usenormantounicodemapper>" + useNormanToUnicodeMapper + "</usenormantounicodemapper>\n");
int index = 0;
while (index < tables.size())
{
String table = (String) tables.get(index);
String layout = (String) layouts.get(index);
String select = (String) selects.get(index);
String create = (String) creates.get(index);
String id = (String) ids.get(index);
buffr.write(" <table name = \"" + table + "\" layout = \"" + layout + "\" id = \"" + id + "\" >\n");
buffr.write(" <select>" + convertToEntities(select) + "</select>\n");
if (!create.equals(""))
buffr.write(" <create>" + create + " </create>\n");
buffr.write(" </table>\n");
index++;
}
buffr.write(" </database>\n");
}
public String toString()
{
return bean.url + " " + tables;
}
}
public static String convertToUTF8(Object command)
{
String str = null;
try
{
str = new String(command.toString().getBytes("UTF-8"));
} catch (UnsupportedEncodingException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
return str;
}
public static void writeConfig(String file, DataBase source, DataBase destination) throws Exception
{
if (!file.toLowerCase().endsWith(".xml"))
file += ".xml";
File f = new File(file);
FileOutputStream fout = new FileOutputStream(f);
OutputStreamWriter outsw = new OutputStreamWriter(fout, "UTF-8");
BufferedWriter buffw = new BufferedWriter(outsw);
buffw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
buffw.newLine();
buffw.write("<convert>\n");
buffw.write(" <source>\n");
source.exportToXML(buffw);
buffw.write(" </source>\n");
buffw.write("\n <destination>\n");
destination.exportToXML(buffw);
buffw.write(" </destination>\n");
buffw.write("</convert>\n");
buffw.close();
}
public static void delete(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
{
FM2SQL.ProgressDialog dialog = null;
if (FM2SQL.fmInstance != null)
{
dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
dialog.setTitle("Conversion running ...");
dialog.title.setText("Getting table data ...");
dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
dialog.thread = Thread.currentThread();
}
// setting user and passwd
bean.setUserAndPasswd(user, passwd);
// setting user and passwd
beanDest.setUserAndPasswd(userDest, passwdDest);
if (dialog != null)
dialog.setSize(400, 250);
StringBuffer command = null;
String query = null;
try
{
//bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
//bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
bean.setConnection(source);
if (names == null)
names = bean.getTableNames();
// Collections.sort(names);
int tbIndex = 1;
// System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
{
Vector[] result = null;
java.util.TreeSet myIds = new TreeSet();
java.util.TreeSet myIdsDest = new TreeSet();
int deltaID = 1;
String idField = "";
String destTableName = "";
try
{
query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
query = (selects != null) ? selects.get(tbIndex).toString() : query;
//if vectors[1].get(i) != null)
if (layout != "")
{
layout = " layout " + bean.getQC() + layout + bean.getQC();
String name = names.get(tbIndex).toString();
StringBuffer queryLayout = new StringBuffer(query);
queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
query = queryLayout.toString();
System.out.println("added layout " + query);
}
dialog.title.setText("Getting table data ...");
dialog.table.setText(names.get(tbIndex).toString());
dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
dialog.show();
bean.getConnection();
bean.makeQuery(query, 50);
idField = ids.get(tbIndex).toString();
} catch (Exception e)
{
continue;
}
// determine destTableName from createStatement or from source table name
if (!creates.get(tbIndex).equals(""))
{
String create = creates.get(tbIndex).toString().toLowerCase();
int fromIndex = create.indexOf("table") + 5;
int toIndex = create.indexOf("(");
destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
System.out.println("destTable " + destTableName);
} else
destTableName = convertText(names.get(tbIndex).toString());
// for id kram
Vector vec = null;
Vector vecDest = null;
// tempo
beanDest.setConnection(destination);
int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
String tempID = bean.getQC() + idField + bean.getQC();
String tempIDdest = beanDest.getQC() + convertText(idField) + beanDest.getQC();
int endIndex = -1;
String tempQuery = query;
String destQuery = query.replaceAll(names.get(tbIndex).toString(), destTableName);
String tempQueryDest = destQuery;
// remove extra query parts destQuery.substring(0,destQuery.lastIndexOf(destTableName)+destTableName.length()+1);
System.out.println("new Query " + tempQueryDest);
if (!idField.equals(""))
{
long startTime = System.currentTimeMillis();
int counter = -1;
while (true)
{
++counter;
if (counter == 0 && dialog != null)
dialog.title.setText("Check if data is available");
else if (dialog != null)
dialog.title.setText("Check if more data is available");
myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, numHits);
myIdsDest = beanDest.getIDVector(convertText(idField), destTableName, tempQueryDest, numHits);
if (myIds.isEmpty())
break;
vec = new Vector(myIds);
vecDest = new Vector(myIdsDest);
rowCount = vec.size();
// Deletion will work this way
Vector deleted = new Vector(vec);
Vector linesToDelete = new Vector(vecDest);
// remove all lines that should not be deleted
linesToDelete.removeAll(deleted);
// System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIdsDest.size());
/// @TODO complete delete task remove query show lines to be deleted let user choose if he wants that
System.out.println("number of lines to be deleted " + linesToDelete.size());
deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
beanDest.setConnection(destination);
Statement stm = beanDest.getConnection().createStatement();
Vector tables = beanDest.getTableNames();
// Collections.sort(tables);
System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
tables = beanDest.getTableNames();
// System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
stm = beanDest.getConnection().createStatement();
if (dialog != null)
dialog.title.setText(" Deleting table data ...");
int j = -1;
Vector row = null;
command = new StringBuffer();
command.append("DELETE FROM");
command.append(beanDest.getQC());
command.append(destTableName);
//command.append(convertText((String) names.get(tbIndex)));
command.append(beanDest.getQC());
int size = bean.getColumnNames().size();
command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
System.out.println(command + " " + tbIndex);
//int rowCount = bean.getRowCount(query);
// int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
while (true)
{
++j;
if (j == linesToDelete.size())
break;
//print rows
pstm.setString(1, linesToDelete.get(j).toString());
System.out.println(pstm.toString());
pstm.execute();
if (dialog != null)
dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
command = null;
}
// prepare new query for next chunk
if (query.indexOf("where") > 0)
tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
else
tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
} //to outer while
} // to idfield if
} // table loop
} catch (Exception e)
{
System.out.println("Error while connecting to database " + e);
if (dialog != null)
{
dialog.setVisible(false);
dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
}
java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
java.io.PrintStream stream = new java.io.PrintStream(b);
stream.print(command + "\n\n");
e.printStackTrace(stream);
FM2SQL.showErrorDialog(b.toString(), "Error occured !");
}
if (dialog != null)
{
dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
dialog.setVisible(false);
}
} // to method
/**
* synchronize method based on delete method code
*
* @param source
* @param destination
* @param names
* @param layouts
* @param selects
* @param creates
* @param ids
* @param mode
* @throws Exception
*/
// TODO implement append,update and delete in one method
// TODO using id based algorithm
public static void synchronize(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode, String delimiter) throws Exception
{
System.out.println(" bin in synchronize!!!");
FM2SQL.ProgressDialog dialog = null;
if (FM2SQL.fmInstance != null)
{
dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
dialog.setTitle("Synchronize running ...");
dialog.title.setText("Getting table data ...");
dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
dialog.thread = Thread.currentThread();
}
// setting user and passwd
bean.setUserAndPasswd(user, passwd);
// setting user and passwd
beanDest.setUserAndPasswd(userDest, passwdDest);
if (dialog != null)
dialog.setSize(400, 250);
StringBuffer command = null;
String query = null;
try
{
//bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
//bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
bean.setConnection(source);
if (names == null)
names = bean.getTableNames();
// Collections.sort(names);
int tbIndex = 1;
// System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
{
Vector[] result = null;
java.util.TreeSet myIds = new TreeSet();
java.util.TreeSet myIdsDest = new TreeSet();
int deltaID = 1;
String idField = "";
String destTableName = "";
try
{
query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
query = (selects != null) ? selects.get(tbIndex).toString() : query;
//if vectors[1].get(i) != null)
if (!layout.equals(""))
{
layout = " layout " + bean.getQC() + layout + bean.getQC();
String name = names.get(tbIndex).toString();
StringBuffer queryLayout = new StringBuffer(query);
queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
query = queryLayout.toString();
System.out.println("added layout " + query);
}
if (dialog != null)
{
dialog.title.setText("Getting table data ...");
dialog.table.setText(names.get(tbIndex).toString());
dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
dialog.show();
}
bean.getConnection();
bean.makeQuery(query, 50);
idField = ids.get(tbIndex).toString();
} catch (Exception e)
{
continue;
}
// determine destTableName from createStatement or from source table name
if (!creates.get(tbIndex).equals(""))
{
String create = creates.get(tbIndex).toString().toLowerCase();
int fromIndex = create.indexOf("table") + 5;
int toIndex = create.indexOf("(");
destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
System.out.println("destTable " + destTableName);
} else
destTableName = convertText(names.get(tbIndex).toString());
// for id kram
Vector vec = null;
Vector vecDest = null;
// tempo
beanDest.setConnection(destination);
int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
String tempID = bean.getQC() + idField + bean.getQC();
String tempIDdest = beanDest.getQC() + convertText(idField) + beanDest.getQC();
int endIndex = -1;
String tempQuery = query;
String destQuery = query.replaceAll(names.get(tbIndex).toString(), destTableName);
String tempQueryDest = destQuery;
// remove extra query parts destQuery.substring(0,destQuery.lastIndexOf(destTableName)+destTableName.length()+1);
System.out.println("new Query " + tempQueryDest);
if (!idField.equals(""))
{
long startTime = System.currentTimeMillis();
int counter = -1;
TreeSet linesToDelete = null;
PreparedStatement delPSt = null;
while (true)
{
++counter;
if (counter == 0 && dialog != null)
dialog.title.setText("Check if data is available");
else if (dialog != null)
dialog.title.setText("Check if more data is available");
myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, 0);
myIdsDest = beanDest.getIDVector(convertText(idField), destTableName, tempQueryDest, 0);
//System.out.println("status of remove "+myIds.remove("b015892"));
if (myIds.isEmpty())
break;
vec = new Vector(myIds);
vecDest = new Vector(myIdsDest);
rowCount = vec.size();
// Deletion will work this way
Vector deleted = new Vector(vec);
TreeSet linesToAppend = new TreeSet(vec);
linesToAppend.addAll(vec);
linesToDelete = new TreeSet(vecDest);
// remove all lines that are already in dest database
linesToAppend.removeAll(vecDest);
// remove all lines that should not be deleted
linesToDelete.removeAll(deleted);
System.out.println("linesToAppend " + linesToAppend + " " + destTableName);
System.out.println("linesToDelete " + linesToDelete + " " + destTableName);
System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIds.size());
deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
ConversionProperties prop = getFieldNamesAndDestTableName(creates.get(tbIndex).toString(), query, names.get(tbIndex).toString());
StringBuffer insCommand = createInsertCommand(prop.destTableName, prop.fieldNames);
StringBuffer updCommand = createUpdateCommand(prop.destTableName, prop.fieldNames, tempIDdest);
StringBuffer delCommand = createDeleteCommand(destTableName, tempIDdest);
PreparedStatement insPst = beanDest.getConnection().prepareStatement(insCommand.toString());
PreparedStatement updPst = beanDest.getConnection().prepareStatement(updCommand.toString());
delPSt = beanDest.getConnection().prepareStatement(delCommand.toString());
// delPSt.setString(1,"b015892");
// delPSt.execute();
// if (true)
// return;
if (vec.size() <= numIntervalls)
{
endIndex = 0;
deltaID = vec.size();
}
for (int k = 0; k < vec.size() - deltaID; k = k + deltaID)
{
System.out.println(vec.get(k) + " " + vec.get(k + deltaID) + " " + vec.lastElement());
if (query.indexOf("where") > 0)
tempQuery = query + " and " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID) + "'";
else
tempQuery = query + " where " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID) + "'";
System.out.println(tempQuery);
if (dialog != null)
dialog.title.setText("Reading table data ...");
// bean.makeQuery(tempQuery, deltaID);
if (dialog != null)
dialog.title.setText("Writing table data ...");
performSynchronize(idField, vec, tempQuery, linesToDelete, linesToAppend, insPst, updPst, delPSt, deltaID, delimiter);
// System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIdsDest.size());
endIndex = k + deltaID;
}
System.out.println(endIndex);
//all data written ? if not write last chunk of data
if (endIndex == vec.size() - 1)
System.out.println("fits");
else
{
System.out.println(" last intervall from " + vec.get(endIndex) + " " + vec.lastElement());
if (query.indexOf("where") > 0)
tempQuery = query + " and " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement() + "'";
else
tempQuery = query + " where " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement() + "'";
System.out.println(tempQuery);
if (dialog != null)
dialog.title.setText("Reading table data ...");
// bean.makeQuery(tempQuery, 0);
if (dialog != null)
dialog.title.setText("Writing table data ...");
performSynchronize(idField, vec, tempQuery, linesToDelete, linesToAppend, insPst, updPst, delPSt, deltaID, delimiter);
// System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIdsDest.size());
}
// prepare new query for next chunk
if (query.indexOf("where") > 0)
tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
else
tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
}
for (Iterator iter = linesToDelete.iterator(); iter.hasNext();)
{
String id = (String) iter.next();
delPSt.setString(1, id);
delPSt.execute();
}
long endTime = System.currentTimeMillis();
System.out.println("Time for incremental synchronize elapsed " + (endTime - startTime));
} // to idfield if
} // table loop
} catch (Exception e)
{
System.out.println("Error while connecting to database " + e);
e.printStackTrace();
if (dialog != null)
{
dialog.setVisible(false);
dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
}
java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
java.io.PrintStream stream = new java.io.PrintStream(b);
stream.print(command + "\n\n");
e.printStackTrace(stream);
FM2SQL.showErrorDialog(b.toString(), "Error occured !");
}
if (dialog != null)
{
dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
dialog.setVisible(false);
}
}
private static void performSynchronize(
String idField,
Vector vec,
String tempQuery,
TreeSet linesToDelete,
TreeSet linesToAppend,
PreparedStatement insPst,
PreparedStatement updPst,
PreparedStatement delPSt,
int deltaID,
String delimiter)
throws SQLException, ParseException
{
Vector[] vectors = bean.getQueryData(tempQuery, deltaID);
int idIndex = vectors[1].indexOf(idField);
// todo arraylist code has to be added
for (Iterator iter = vectors[0].iterator(); iter.hasNext();)
{
Vector line = (Vector) iter.next();
Object lineIDIndex = line.get(idIndex);
if (linesToAppend.contains(lineIDIndex))
System.out.println("line " + linesToAppend.contains(line.get(idIndex)) + " " + lineIDIndex);
if (linesToAppend.contains(lineIDIndex))
{
for (int l = 0; l < line.size(); ++l)
{
Object obj = line.get(l);
if (obj instanceof ArrayList)
obj = formatFileMakerArray((List) obj, delimiter);
if (obj != null)
insPst.setString(l + 1, obj.toString());
else
insPst.setNull(l + 1, Types.NULL);
}
insPst.execute();
} else // update
{
for (int l = 0; l < line.size(); ++l)
{
Object obj = line.get(l);
if (obj instanceof ArrayList)
obj = formatFileMakerArray((List) obj, delimiter);
if (obj != null)
updPst.setString(l + 1, obj.toString());
else
updPst.setNull(l + 1, Types.NULL);
}
updPst.setString(line.size() + 1, line.get(idIndex).toString());
updPst.execute();
}
}
} // to method
/**
* Converts input String in norman encoding to unicode
* @param inp
* @return converted String
*/
static public String normanToUnicode(String inp)
{
StringBuffer buf = new StringBuffer();
for (int i = 0; i < inp.length(); i++)
{
char c = inp.charAt(i);
// System.out.println("char "+c+" "+(int)c);
switch (c)
{
case 1 :
buf.append("\u00d0");
break; // Eth
case 2 :
buf.append("\u00f0");
break; // eth
case 3 :
buf.append("\u0141");
break; // Lslash
case 4 :
buf.append("\u0142");
break; // lslash
case 5 :
buf.append("\u0160");
break; // S caron
case 6 :
buf.append("\u0161");
break; // s caron
case 7 :
buf.append("\u00dd");
break; // Y acute
case 8 :
buf.append("\u00fd");
break; // y acute
case 11 :
buf.append("\u00de");
break; // Thorn
case 12 :
buf.append("\u00fe");
break; // thorn
case 14 :
buf.append("\u017d");
break; // Z caron
case 15 :
buf.append("\u017e");
break; // z caron
case 17 :
buf.append("\u0073");
break; // asciitilde
case 18 :
buf.append("j\u0305");
break; // j macron [does a single char exist?]
case 19 :
buf.append("^");
break; // circumflex
case 20 :
buf.append("\u0303");
break; // tilde
case 21 :
buf.append("\u00bd");
break; // onehalf
case 22 :
buf.append("\u00bc");
break; // onequarter
case 23 :
buf.append("\u00b9");
break; // onesuperior
case 24 :
buf.append("\u00be");
break; // threequarters
case 25 :
buf.append("\u00b3");
break; // threesuperior
case 26 :
buf.append("\u00b2");
break; // twosuperior
case 27 :
buf.append("\u00a6");
break; // brokenbar
case 28 :
buf.append("-");
break; // minus
case 29 :
buf.append("\u00d7");
break; // multiply
case 39 :
buf.append("'");
break; // quotesingle
case 94 :
buf.append("\u0302");
break; // circumflex
case 96 :
buf.append("\u0300");
break; // grave
case 196 :
buf.append("\u00c4");
break; // A dieresis
case 197 :
buf.append("\u00c5");
break; // A ring
case 201 :
buf.append("\u00c9");
break; // E acute
case 209 :
buf.append("\u00d1");
break; // N tilde
case 214 :
buf.append("\u00d6");
break; // O dieresis
case 220 :
buf.append("\u00dc");
break; // U dieresis
case 225 :
buf.append("\u00e1");
break; // a acute
case 224 :
buf.append("\u00e0");
break; // a grave
case 226 :
buf.append("\u00e2");
break; // a circumflex
case 228 :
buf.append("\u00e4");
break; // a dieresis
case 227 :
buf.append("\u00e3");
break; // a tilde
case 229 :
buf.append("\u0101");
break; // a macron
case 231 :
buf.append("\u00e7");
break; // c cedilla
case 233 :
buf.append("\u00e9");
break; // e acute
case 232 :
buf.append("\u00e8");
break; // e grave
case 234 :
buf.append("\u00ea");
break; // e circumflex
case 235 :
buf.append("\u00eb");
break; // e dieresis
case 237 :
buf.append("\u00ed");
break; // i acute
case 236 :
buf.append("\u00ec");
break; // i grave
case 238 :
buf.append("\u00ee");
break; // i circumflex
case 239 :
buf.append("\u00ef");
break; // i dieresis
case 241 :
buf.append("\u00f1");
break; // n tilde
case 243 :
buf.append("\u00f3");
break; // o acute
case 242 :
buf.append("\u00f2");
break; // o grave
case 244 :
buf.append("\u00f4");
break; // o circumflex
case 246 :
buf.append("\u00f6");
break; // o dieresis
case 245 :
buf.append("\u00f5");
break; // o tilde
case 250 :
buf.append("\u00fa");
break; // u acute
case 249 :
buf.append("\u00f9");
break; // u grave
case 251 :
buf.append("\u00fb");
break; // u circumflex
case 252 :
buf.append("\u00fc");
break; // u dieresis
case 8224 :
buf.append("\u1e6d");
break; // t underdot
case 176 :
buf.append("\u00b0");
break; // degree
case 162 :
buf.append("\u1ebd");
break; // e tilde
case 163 :
buf.append("\u00a3");
break; // sterling
case 167 :
buf.append("\u00a7");
break; // section
case 182 :
buf.append("\u00b6");
break; // paragraph
case 223 :
buf.append("\u015b");
break; // s acute
case 174 :
buf.append("\u1e5b");
break; // r underdot
case 169 :
buf.append("\u1e45");
break; // n overdot
case 353 :
buf.append("\u1e45");
break; // n overdot
case 180 :
buf.append("\u0301");
break; // acute
case 168 :
buf.append("\u0308");
break; // dieresis
case 8800 :
buf.append("\u1e6d");
break; // t underdot
case 198 :
buf.append("\u00c6");
break; // AE
case 216 :
buf.append("\u014d");
break; // o macron
case 8734 :
buf.append("\u0129");
break; // i tilde
case 177 :
buf.append("\u00b1");
break; // plusminus
case 165 :
buf.append("\u012b");
break; // i macron
case 181 :
buf.append("\u1e43");
break; // m underdot
case 8706 :
buf.append("\u1e0d");
break; // d underdot
case 240 :
buf.append("\u1e0d");
break; // d underdot
case 8721 :
buf.append("\u1e63");
break; // s underdot
case 960 :
buf.append("\u017a");
break; // z acute
case 8747 :
buf.append("\u1e45");
break; // n overdot
case 937 :
buf.append("\u0169");
break; // u tilde
case 230 :
buf.append("\u00e6");
break; // ae
case 248 :
buf.append("\u00f8");
break; // oslash
case 191 :
buf.append("\u0304\u0306");
break; // macron breve
case 172 :
buf.append("\u1e37");
break; //
case 8730 :
buf.append("j\u0305");
break; // j macron [does a single char exist?]
case 402 :
buf.append("\u0103");
break; // a breve
case 8776 :
buf.append("\u016d");
break; // u breve
case 187 :
buf.append("\u1e42");
break; // M underdot
case 8230 :
buf.append("\u2026");
break; // ellipsis
case 192 :
buf.append("\u00c0");
break; // A grave
case 195 :
buf.append("\u00c3");
break; // A tilde
case 213 :
buf.append("\u00d5");
break; // O tilde
case 338 :
buf.append("m\u0306");
break; // m breve
case 339 :
buf.append("\u0153");
break; // oe
case 8211 :
buf.append("\u2013");
break; // endash
case 8212 :
buf.append("\u2014");
break; // emdash
case 8220 :
buf.append("\u201c");
break; // quotedblleft
case 8221 :
buf.append("\u201d");
break; // quotedblright
case 8216 :
buf.append("\u2018");
break; // quoteleft
case 8217 :
buf.append("\u2019");
break; // quoteright
case 247 :
buf.append("\u1e37");
break; // l underring [actually underdot]
case 9674 :
buf.append("\u1e41");
break; // m overdot
case 255 :
buf.append("n\u0306");
break; // n breve
case 376 :
buf.append("\u00d7");
break; // multiply
case 8364 :
buf.append("\u1e5b");
break; // r underring [actually underdot]
case 8249 :
buf.append("\u1e44");
break; // N overdot
case 8250 :
buf.append("\u1e62");
break; // S underdot
case 64257 :
buf.append("\u1e24");
break; // H underdot
case 64258 :
buf.append("\u1e0c");
break; // D underdot
case 8225 :
buf.append("\u2021");
break; // daggerdbl
case 8218 :
buf.append("\u1e36");
break; // L underdot
case 8222 :
buf.append("\u0113");
break; // e macron
case 194 :
buf.append("\u1e5f");
break; // r underbar
case 202 :
buf.append("r\u0324");
break; // r underdieresis
case 193 :
buf.append("\u012a");
break; // I macron
case 8486 :
case 203 :
buf.append("\u016b");
break; // u macron
case 200 :
buf.append("\u1e6c");
break; // T underdot
case 205 :
buf.append("\u1e64");
break; // S acute
case 206 :
buf.append("\u2020");
break; // dagger
case 207 :
buf.append("\u0115");
break; // e breve
case 204 :
buf.append("\u014f");
break; // o breve
case 211 :
buf.append("\u0100");
break; // A macron
case 212 :
buf.append("\u1e46");
break; // N underdot
case 210 :
buf.append("\u1e3b");
break; // l underbar
case 218 :
buf.append("\u016a");
break; // U macron
case 219 :
buf.append("\u0179");
break; // Z acute
case 217 :
buf.append("\u1e5a");
break; // R underdot
case 305 :
buf.append("\u0131");
break; // dotlessi
case 710 :
buf.append("\u1e47");
break; // n underdot
case 732 :
buf.append("\u1e49");
break; // n underbar
case 175 :
buf.append("\u0304");
break; // macron
case 728 :
buf.append("\u0306");
break; // breve
case 729 :
case 215 :
buf.append("\u1e25");
break; // h underdot
case 730 :
buf.append("\u012d");
break; // i breve
case 184 :
buf.append("\u0327");
break; // cedilla
case 733 :
buf.append("\u030b");
break; // hungarumlaut
case 731 :
buf.append("\u0328");
break; // ogonek
case 711 :
buf.append("\u030c");
break; // caron
case 199 :
buf.append("\u012b\u0303");
break; // imacron tilde
case 8226 :
buf.append("\u1e5d");
break; // runderdot macron
case 8482 :
buf.append("\u016b\0306");
break; // umacron breve
case 8804 :
buf.append("\u0101\u0301");
break; // amacron acute
case 8805 :
buf.append("\u016b\u0301");
break; // umacron acute
case 8719 :
buf.append("\u0113\u0301");
break; // emacron acute
case 170 :
buf.append("\u0113\u0300");
break; // emacron breve
case 186 :
buf.append("\u014d\u0300");
break; // omacron breve
case 161 :
buf.append("\u0101\u0306");
break; // amacron breve
case 8710 :
buf.append("\u0101\u0303");
break; // amacron tilde
case 171 :
buf.append("\u012b\u0301");
break; // imacron acute
case 8260 :
buf.append("\u1e00");
break; // runderdotmacron acute
case 183 :
buf.append("\u1e5b\u0301");
break; // runderdot acute
case 8240 :
buf.append("\u012b\u0306");
break; // imacron breve
case 63743 :
buf.append("\u016b\u0303");
break; // umacron tilde
default :
buf.append(c);
if ((int) c > 127)
System.out.println("char " + c + " " + (int) c);
break;
}
}
return buf.toString();
}
static public String normanToUnicodeOld(String inp)
{
StringBuffer buf = new StringBuffer();
for (int i = 0; i < inp.length(); i++)
{
char c = inp.charAt(i);
switch (c)
{
case 1 :
buf.append("\u00d0");
break; // Eth
case 2 :
buf.append("\u00f0");
break; // eth
case 3 :
buf.append("\u0141");
break; // Lslash
case 4 :
buf.append("\u0142");
break; // lslash
case 5 :
buf.append("\u0160");
break; // S caron
case 6 :
buf.append("\u0161");
break; // s caron
case 7 :
buf.append("\u00dd");
break; // Y acute
case 8 :
buf.append("\u00fd");
break; // y acute
case 11 :
buf.append("\u00de");
break; // Thorn
case 12 :
buf.append("\u00fe");
break; // thorn
case 14 :
buf.append("\u017d");
break; // Z caron
case 15 :
buf.append("\u017e");
break; // z caron
case 17 :
buf.append("\u0073");
break; // asciitilde
case 18 :
buf.append("j\u0305");
break; // j macron [does a single char exist?]
case 19 :
buf.append("^");
break; // circumflex
case 20 :
buf.append("\u0303");
break; // tilde
case 21 :
buf.append("\u00bd");
break; // onehalf
case 22 :
buf.append("\u00bc");
break; // onequarter
case 23 :
buf.append("\u00b9");
break; // onesuperior
case 24 :
buf.append("\u00be");
break; // threequarters
case 25 :
buf.append("\u00b3");
break; // threesuperior
case 26 :
buf.append("\u00b2");
break; // twosuperior
case 27 :
buf.append("\u00a6");
break; // brokenbar
case 28 :
buf.append("-");
break; // minus
case 29 :
buf.append("\u00d7");
break; // multiply
case 39 :
buf.append("'");
break; // quotesingle
case 94 :
buf.append("\u0302");
break; // circumflex
case 96 :
buf.append("\u0300");
break; // grave
case 128 :
buf.append("\u00c4");
break; // A dieresis
case 129 :
buf.append("\u00c5");
break; // A ring
case 131 :
buf.append("\u00c9");
break; // E acute
case 132 :
buf.append("\u00d1");
break; // N tilde
case 133 :
buf.append("\u00d6");
break; // O dieresis
case 134 :
buf.append("\u00dc");
break; // U dieresis
case 135 :
buf.append("\u00e1");
break; // a acute
case 136 :
buf.append("\u00e0");
break; // a grave
case 137 :
buf.append("\u00e2");
break; // a circumflex
case 138 :
buf.append("\u00e4");
break; // a dieresis
case 139 :
buf.append("\u00e3");
break; // a tilde
case 140 :
buf.append("\u0101");
break; // a macron
case 141 :
buf.append("\u00e7");
break; // c cedilla
case 142 :
buf.append("\u00e9");
break; // e acute
case 143 :
buf.append("\u00e8");
break; // e grave
case 144 :
buf.append("\u00ea");
break; // e circumflex
case 145 :
buf.append("\u00eb");
break; // e dieresis
case 146 :
buf.append("\u00ed");
break; // i acute
case 147 :
buf.append("\u00ec");
break; // i grave
case 148 :
buf.append("\u00ee");
break; // i circumflex
case 149 :
buf.append("\u00ef");
break; // i dieresis
case 150 :
buf.append("\u00f1");
break; // n tilde
case 151 :
buf.append("\u00f3");
break; // o acute
case 152 :
buf.append("\u00f2");
break; // o grave
case 153 :
buf.append("\u00f4");
break; // o circumflex
case 154 :
buf.append("\u00f6");
break; // o dieresis
case 155 :
buf.append("\u00f5");
break; // o tilde
case 156 :
buf.append("\u00fa");
break; // u acute
case 157 :
buf.append("\u00f9");
break; // u grave
case 158 :
buf.append("\u00fb");
break; // u circumflex
case 159 :
buf.append("\u00fc");
break; // u dieresis
case 160 :
buf.append("\u1e6d");
break; // t underdot
case 161 :
buf.append("\u00b0");
break; // degree
case 162 :
buf.append("\u1ebd");
break; // e tilde
case 163 :
buf.append("\u00a3");
break; // sterling
case 164 :
buf.append("\u00a7");
break; // section
case 166 :
buf.append("\u00b6");
break; // paragraph
case 167 :
buf.append("\u015b");
break; // s acute
case 168 :
buf.append("\u1e5b");
break; // r underdot
case 169 :
buf.append("\u1e67");
break; // s caron
case 171 :
buf.append("\u0301");
break; // acute
case 172 :
buf.append("\u0308");
break; // dieresis
case 173 :
buf.append("\u1e6d");
break; // t underdot
case 174 :
buf.append("\u00c6");
break; // AE
case 175 :
buf.append("\u014d");
break; // o macron
case 176 :
buf.append("\u0129");
break; // i tilde
case 177 :
buf.append("\u00b1");
break; // plusminus
case 180 :
buf.append("\u012b");
break; // i macron
case 181 :
buf.append("\u1e43");
break; // m underdot
case 182 :
buf.append("\u1e0d");
break; // d underdot
case 183 :
buf.append("\u1e63");
break; // s underdot
case 185 :
buf.append("\u017a");
break; // z acute
case 186 :
buf.append("\u1e45");
break; // n overdot
case 189 :
buf.append("\u0169");
break; // u tilde
case 190 :
buf.append("\u00e6");
break; // ae
case 191 :
buf.append("\u00f8");
break; // oslash
case 192 :
buf.append("\u0304\u0306");
break; // macron breve
case 194 :
buf.append("\u1e37");
break; //
case 195 :
buf.append("j\u0305");
break; // j macron [does a single char exist?]
case 196 :
buf.append("\u0103");
break; // a breve
case 197 :
buf.append("\u016d");
break; // u breve
case 200 :
buf.append("\u1e42");
break; // M underdot
case 201 :
buf.append("\u2026");
break; // ellipsis
case 203 :
buf.append("\u00c0");
break; // A grave
case 204 :
buf.append("\u00c3");
break; // A tilde
case 205 :
buf.append("\u00d5");
break; // O tilde
case 206 :
buf.append("m\u0306");
break; // m breve
case 207 :
buf.append("\u0153");
break; // oe
case 208 :
buf.append("\u2013");
break; // endash
case 209 :
buf.append("\u2014");
break; // emdash
case 210 :
buf.append("\u201c");
break; // quotedblleft
case 211 :
buf.append("\u201d");
break; // quotedblright
case 212 :
buf.append("\u2018");
break; // quoteleft
case 213 :
buf.append("\u2019");
break; // quoteright
case 214 :
buf.append("\u1e37");
break; // l underring [actually underdot]
case 215 :
buf.append("\u1e41");
break; // m overdot
case 216 :
buf.append("n\u0306");
break; // n breve
case 217 :
buf.append("\u00d7");
break; // multiply
case 219 :
buf.append("\u1e5b");
break; // r underring [actually underdot]
case 220 :
buf.append("\u1e44");
break; // N overdot
case 221 :
buf.append("\u1e62");
break; // S underdot
case 222 :
buf.append("\u1e24");
break; // H underdot
case 223 :
buf.append("\u1e0c");
break; // D underdot
case 224 :
buf.append("\u2021");
break; // daggerdbl
case 226 :
buf.append("\u1e36");
break; // L underdot
case 227 :
buf.append("\u0113");
break; // e macron
case 229 :
buf.append("\u1e5f");
break; // r underbar
case 230 :
buf.append("r\u0324");
break; // r underdieresis
case 231 :
buf.append("\u012a");
break; // I macron
case 232 :
buf.append("\u016b");
break; // u macron
case 233 :
buf.append("\u01e6c");
break; // T underdot
case 234 :
buf.append("\u1e64");
break; // S acute
case 235 :
buf.append("\u2020");
break; // dagger
case 236 :
buf.append("\u0115");
break; // e breve
case 237 :
buf.append("\u014f");
break; // o breve
case 238 :
buf.append("\u0100");
break; // A macron
case 239 :
buf.append("\u1e46");
break; // N underdot
case 241 :
buf.append("\u1e3b");
break; // l underbar
case 242 :
buf.append("\u016a");
break; // U macron
case 243 :
buf.append("\u0179");
break; // Z acute
case 244 :
buf.append("\u1e5a");
break; // R underdot
case 245 :
buf.append("\u0131");
break; // dotlessi
case 246 :
buf.append("\u1e47");
break; // n underdot
case 247 :
buf.append("\u1e49");
break; // n underbar
case 248 :
buf.append("\u0304");
break; // macron
case 249 :
buf.append("\u0306");
break; // breve
case 250 :
buf.append("\u1e25");
break; // h underdot
case 251 :
buf.append("\u012d");
break; // i breve
case 252 :
buf.append("\u0327");
break; // cedilla
case 253 :
buf.append("\u030b");
break; // hungarumlaut
case 254 :
buf.append("\u0328");
break; // ogonek
case 255 :
buf.append("\u030c");
break; // caron
case 130 :
buf.append("\u012b\u0303");
break; // imacron tilde
case 165 :
buf.append("\u1e5d");
break; // runderdot macron
case 170 :
buf.append("\u016b\0306");
break; // umacron breve
case 178 :
buf.append("\u0101\u0301");
break; // amacron acute
case 179 :
buf.append("\u016b\u0301");
break; // umacron acute
case 184 :
buf.append("\u0113\u0301");
break; // emacron acute
case 187 :
buf.append("\u0113\u0300");
break; // emacron breve
case 188 :
buf.append("\u014d\u0300");
break; // omacron breve
case 193 :
buf.append("\u0101\u0306");
break; // amacron breve
case 198 :
buf.append("\u0101\u0303");
break; // amacron tilde
case 199 :
buf.append("\u012b\u0301");
break; // imacron acute
case 218 :
buf.append("\u1e00");
break; // runderdotmacron acute
case 225 :
buf.append("\u1e5b\u0301");
break; // runderdot acute
case 228 :
buf.append("\u012b\u0306");
break; // imacron breve
case 240 :
buf.append("\u016b\u0303");
break; // umacron tilde
default :
buf.append(c);
break;
}
}
return buf.toString();
}
static public String normanToUnicodeNew(String inp)
{
StringBuffer buf = new StringBuffer();
for (int i = 0; i < inp.length(); i++)
{
char c = inp.charAt(i);
switch (c)
{
case 1 :
buf.append("\u00d0");
break; // Eth
case 2 :
buf.append("\u00f0");
break; // eth
case 3 :
buf.append("\u0141");
break; // Lslash
case 4 :
buf.append("\u0142");
break; // lslash
case 5 :
buf.append("\u0160");
break; // S caron
case 6 :
buf.append("\u0161");
break; // s caron
case 7 :
buf.append("\u00dd");
break; // Y acute
case 8 :
buf.append("\u00fd");
break; // y acute
case 11 :
buf.append("\u00de");
break; // Thorn
case 12 :
buf.append("\u00fe");
break; // thorn
case 14 :
buf.append("\u017d");
break; // Z caron
case 15 :
buf.append("\u017e");
break; // z caron
case 17 :
buf.append("\u0073");
break; // asciitilde
case 18 :
buf.append("j\u0305");
break; // j macron [does a single char exist?]
case 19 :
buf.append("^");
break; // circumflex
case 20 :
buf.append("\u0303");
break; // tilde
case 21 :
buf.append("\u00bd");
break; // onehalf
case 22 :
buf.append("\u00bc");
break; // onequarter
case 23 :
buf.append("\u00b9");
break; // onesuperior
case 24 :
buf.append("\u00be");
break; // threequarters
case 25 :
buf.append("\u00b3");
break; // threesuperior
case 26 :
buf.append("\u00b2");
break; // twosuperior
case 27 :
buf.append("\u00a6");
break; // brokenbar
case 28 :
buf.append("-");
break; // minus
case 29 :
buf.append("\u00d7");
break; // multiply
case 39 :
buf.append("'");
break; // quotesingle
case 94 :
buf.append("\u0302");
break; // circumflex
case 96 :
buf.append("\u0300");
break; // grave
case 196 :
buf.append("\u00c4");
break; // A dieresis
case 197 :
buf.append("\u00c5");
break; // A ring
case 201 :
buf.append("\u00c9");
break; // E acute
case 209 :
buf.append("\u00d1");
break; // N tilde
case 214 :
buf.append("\u00d6");
break; // O dieresis
case 220 :
buf.append("\u00dc");
break; // U dieresis
case 225 :
buf.append("\u00e1");
break; // a acute
case 224 :
buf.append("\u00e0");
break; // a grave
case 226 :
buf.append("\u00e2");
break; // a circumflex
case 228 :
buf.append("\u00e4");
break; // a dieresis
case 227 :
buf.append("\u00e3");
break; // a tilde
case 229 :
buf.append("\u0101");
break; // a macron
case 231 :
buf.append("\u00e7");
break; // c cedilla
case 233 :
buf.append("\u00e9");
break; // e acute
case 232 :
buf.append("\u00e8");
break; // e grave
case 234 :
buf.append("\u00ea");
break; // e circumflex
case 235 :
buf.append("\u00eb");
break; // e dieresis
case 237 :
buf.append("\u00ed");
break; // i acute
case 236 :
buf.append("\u00ec");
break; // i grave
case 238 :
buf.append("\u00ee");
break; // i circumflex
case 239 :
buf.append("\u00ef");
break; // i dieresis
case 241 :
buf.append("\u00f1");
break; // n tilde
case 243 :
buf.append("\u00f3");
break; // o acute
case 242 :
buf.append("\u00f2");
break; // o grave
case 244 :
buf.append("\u00f4");
break; // o circumflex
case 246 :
buf.append("\u00f6");
break; // o dieresis
case 245 :
buf.append("\u00f5");
break; // o tilde
case 250 :
buf.append("\u00fa");
break; // u acute
case 249 :
buf.append("\u00f9");
break; // u grave
case 251 :
buf.append("\u00fb");
break; // u circumflex
case 252 :
buf.append("\u00fc");
break; // u dieresis
case 8224 :
buf.append("\u1e6d");
break; // t underdot
case 176 :
buf.append("\u00b0");
break; // degree
case 162 :
buf.append("\u1ebd");
break; // e tilde
case 163 :
buf.append("\u00a3");
break; // sterling
case 167 :
buf.append("\u00a7");
break; // section
case 182 :
buf.append("\u00b6");
break; // paragraph
case 223 :
buf.append("\u015b");
break; // s acute
case 174 :
buf.append("\u1e5b");
break; // r underdot
case 169 :
buf.append("\u1e45");
break; // n overdot
case 180 :
buf.append("\u0301");
break; // acute
case 168 :
buf.append("\u0308");
break; // dieresis
case 8800 :
buf.append("\u1e6d");
break; // t underdot
case 198 :
buf.append("\u00c6");
break; // AE
case 216 :
buf.append("\u014d");
break; // o macron
case 8734 :
buf.append("\u0129");
break; // i tilde
case 177 :
buf.append("\u00b1");
break; // plusminus
case 165 :
buf.append("\u012b");
break; // i macron
case 181 :
buf.append("\u1e43");
break; // m underdot
case 8706 :
buf.append("\u1e0d");
break; // d underdot
case 8721 :
buf.append("\u1e63");
break; // s underdot
case 960 :
buf.append("\u017a");
break; // z acute
case 8747 :
buf.append("\u1e45");
break; // n overdot
case 937 :
buf.append("\u0169");
break; // u tilde
case 230 :
buf.append("\u00e6");
break; // ae
case 248 :
buf.append("\u00f8");
break; // oslash
case 191 :
buf.append("\u0304\u0306");
break; // macron breve
case 172 :
buf.append("\u1e37");
break; //
case 8730 :
buf.append("j\u0305");
break; // j macron [does a single char exist?]
case 402 :
buf.append("\u0103");
break; // a breve
case 8776 :
buf.append("\u016d");
break; // u breve
case 187 :
buf.append("\u1e42");
break; // M underdot
case 8230 :
buf.append("\u2026");
break; // ellipsis
case 192 :
buf.append("\u00c0");
break; // A grave
case 195 :
buf.append("\u00c3");
break; // A tilde
case 213 :
buf.append("\u00d5");
break; // O tilde
case 338 :
buf.append("m\u0306");
break; // m breve
case 339 :
buf.append("\u0153");
break; // oe
case 8211 :
buf.append("\u2013");
break; // endash
case 8212 :
buf.append("\u2014");
break; // emdash
case 8220 :
buf.append("\u201c");
break; // quotedblleft
case 8221 :
buf.append("\u201d");
break; // quotedblright
case 8216 :
buf.append("\u2018");
break; // quoteleft
case 8217 :
buf.append("\u2019");
break; // quoteright
case 247 :
buf.append("\u1e37");
break; // l underring [actually underdot]
case 9674 :
buf.append("\u1e41");
break; // m overdot
case 255 :
buf.append("n\u0306");
break; // n breve
case 376 :
buf.append("\u00d7");
break; // multiply
case 8364 :
buf.append("\u1e5b");
break; // r underring [actually underdot]
case 8249 :
buf.append("\u1e44");
break; // N overdot
case 8250 :
buf.append("\u1e62");
break; // S underdot
case 64257 :
buf.append("\u1e24");
break; // H underdot
case 64258 :
buf.append("\u1e0c");
break; // D underdot
case 8225 :
buf.append("\u2021");
break; // daggerdbl
case 8218 :
buf.append("\u1e36");
break; // L underdot
case 8222 :
buf.append("\u0113");
break; // e macron
case 194 :
buf.append("\u1e5f");
break; // r underbar
case 202 :
buf.append("r\u0324");
break; // r underdieresis
case 193 :
buf.append("\u012a");
break; // I macron
case 203 :
buf.append("\u016b");
break; // u macron
case 200 :
buf.append("\u1e6c");
break; // T underdot
case 205 :
buf.append("\u1e64");
break; // S acute
case 206 :
buf.append("\u2020");
break; // dagger
case 207 :
buf.append("\u0115");
break; // e breve
case 204 :
buf.append("\u014f");
break; // o breve
case 211 :
buf.append("\u0100");
break; // A macron
case 212 :
buf.append("\u1e46");
break; // N underdot
case 210 :
buf.append("\u1e3b");
break; // l underbar
case 218 :
buf.append("\u016a");
break; // U macron
case 219 :
buf.append("\u0179");
break; // Z acute
case 217 :
buf.append("\u1e5a");
break; // R underdot
case 305 :
buf.append("\u0131");
break; // dotlessi
case 710 :
buf.append("\u1e47");
break; // n underdot
case 732 :
buf.append("\u1e49");
break; // n underbar
case 175 :
buf.append("\u0304");
break; // macron
case 728 :
buf.append("\u0306");
break; // breve
case 729 :
buf.append("\u1e25");
break; // h underdot
case 730 :
buf.append("\u012d");
break; // i breve
case 184 :
buf.append("\u0327");
break; // cedilla
case 733 :
buf.append("\u030b");
break; // hungarumlaut
case 731 :
buf.append("\u0328");
break; // ogonek
case 711 :
buf.append("\u030c");
break; // caron
case 199 :
buf.append("\u012b\u0303");
break; // imacron tilde
case 8226 :
buf.append("\u1e5d");
break; // runderdot macron
case 8482 :
buf.append("\u016b\0306");
break; // umacron breve
case 8804 :
buf.append("\u0101\u0301");
break; // amacron acute
case 8805 :
buf.append("\u016b\u0301");
break; // umacron acute
case 8719 :
buf.append("\u0113\u0301");
break; // emacron acute
case 170 :
buf.append("\u0113\u0300");
break; // emacron breve
case 186 :
buf.append("\u014d\u0300");
break; // omacron breve
case 161 :
buf.append("\u0101\u0306");
break; // amacron breve
case 8710 :
buf.append("\u0101\u0303");
break; // amacron tilde
case 171 :
buf.append("\u012b\u0301");
break; // imacron acute
case 8260 :
buf.append("\u1e00");
break; // runderdotmacron acute
case 183 :
buf.append("\u1e5b\u0301");
break; // runderdot acute
case 8240 :
buf.append("\u012b\u0306");
break; // imacron breve
case 63743 :
buf.append("\u016b\u0303");
break; // umacron tilde
default :
buf.append(c);
break;
}
}
return buf.toString();
}
public static ConversionProperties getFieldNamesAndDestTableName(String create, String query, String tableName)
{
String[] fieldNames = null;
String destTableName = null;
// determine destTableName from createStatement or from source table name
if (!create.equals(""))
{
int fromIndex = create.toLowerCase().indexOf("table") + 5;
int toIndex = create.indexOf("(");
int endIndex = create.indexOf(")", toIndex);
destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
System.out.println("destTable " + destTableName);
// retrieve field_names from select statement
// TODO problem with different fieldNames in create statement will overwrite them
if (query.indexOf("*") < 0)
{
int selectEndIndex = query.indexOf("from");
StringTokenizer tokenizer = new StringTokenizer(query.substring(6, selectEndIndex), ",");
int numFields = tokenizer.countTokens();
fieldNames = new String[numFields];
int fieldIndex = 0;
while (tokenizer.hasMoreTokens())
{
String fieldName = tokenizer.nextToken().trim();
fieldNames[fieldIndex] = convertText(fieldName);
System.out.println(fieldNames[fieldIndex]);
fieldIndex++;
}
} else
{
// use create statement for field names
StringTokenizer tokenizer = new StringTokenizer(create.substring(toIndex + 1, endIndex), ",");
int numFields = tokenizer.countTokens();
fieldNames = new String[numFields];
int fieldIndex = 0;
while (tokenizer.hasMoreTokens())
{
String fieldName = tokenizer.nextToken().trim();
int index = fieldName.lastIndexOf(" ");
fieldNames[fieldIndex] = fieldName.substring(0, index);
System.out.println(fieldNames[fieldIndex]);
fieldIndex++;
}
}
} else
{
destTableName = convertText(tableName);
// retrieve field_names from select statement
if (query.indexOf("*") < 0)
{
int selectEndIndex = query.indexOf("from");
StringTokenizer tokenizer = new StringTokenizer(query.substring(6, selectEndIndex), ",");
int numFields = tokenizer.countTokens();
fieldNames = new String[numFields];
int fieldIndex = 0;
while (tokenizer.hasMoreTokens())
{
String fieldName = tokenizer.nextToken().trim();
fieldNames[fieldIndex] = convertText(fieldName);
// System.out.println("field "+ fieldNames[fieldIndex]);
fieldIndex++;
}
} else
{
Vector fieldNamesVec = bean.getColumnNames();
fieldNames = new String[fieldNamesVec.size()];
int fieldIndex = -1;
for (Iterator iter = fieldNamesVec.iterator(); iter.hasNext();)
{
String element = (String) iter.next();
fieldNames[++fieldIndex] = bean.getQC() + convertText(element) + bean.getQC();
// System.out.println("field " + fieldNames[fieldIndex]);
}
}
}
return new ConversionProperties(destTableName, fieldNames);
}
/**
* creates an insert into statement for the specified table and given
* field names
* @param destTableName
* @param fieldNames
* @return
*/
public static StringBuffer createInsertCommand(String destTableName, String[] fieldNames)
{
StringBuffer command = new StringBuffer();
command.append("INSERT INTO ");
command.append(beanDest.getQC());
command.append(destTableName); //convertText((String) names.get(tbIndex)));
command.append(beanDest.getQC());
command.append(" (");
for (int i = 0; i < fieldNames.length; i++)
{
command.append(fieldNames[i]);
if (i < fieldNames.length - 1)
command.append(",");
}
command.append(") ");
command.append(" values ( ");
// add a question marks for every field
for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
command.append("?,");
command.append("?)");
return command;
}
public static StringBuffer createUpdateCommand(String destTableName, String[] fieldNames, String id)
{
StringBuffer command = new StringBuffer();
command.append("UPDATE ");
command.append(beanDest.getQC());
command.append(destTableName);
//command.append(convertText((String) names.get(tbIndex)));
command.append(beanDest.getQC());
command.append(" SET ");
int size = bean.getColumnNames().size();
for (int i = 0; i < size - 1; ++i)
command.append(fieldNames[i] + " = ? ,");
command.append(fieldNames[size - 1] + " = ? ");
command.append("WHERE " + id + " = ?");
return command;
}
public static StringBuffer createDeleteCommand(String destTableName, String idField)
{
StringBuffer command = new StringBuffer();
command.append("DELETE FROM");
command.append(beanDest.getQC());
command.append(destTableName);
//command.append(convertText((String) names.get(tbIndex)));
command.append(beanDest.getQC());
command.append("WHERE " + idField + " = ?");
return command;
}
}
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>