/*
* 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.IOException;
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.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.StringTokenizer;
import java.util.TreeSet;
import java.util.Vector;
import javax.swing.JDialog;
import javax.swing.JLabel;
import javax.swing.JPanel;
import com.exploringxml.xml.Node;
import com.exploringxml.xml.Xparse;
class Convert
{
/**
* Helper class for index creation
*
* @author rogo
*
*/
public static class IndexList extends Vector
{
public String toString()
{
StringBuffer buff = new StringBuffer(1000);
int count = 0;
for (Iterator iter = this.iterator(); iter.hasNext();)
{
String element = (String) iter.next();
if (count < elementCount - 1)
{
buff.append(element).append(", ");
count++;
}
else
buff.append(element);
}
return buff.toString();
}
}
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 = 4;
static boolean debug = false;
static boolean isGUI = true;
public static void main(String args[]) throws IOException
{
/*
* 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(); }
*/
isGUI = false;
FileOutputStream file = null;
if (args.length != 1)
{
System.out.println("Usage: java Convert <xml config file>");
System.exit(-1);
}
try
{
File temp = File.createTempFile("fm2sql", ".txt");
file = new FileOutputStream(temp);
}
catch (FileNotFoundException e1)
{
e1.printStackTrace();
}
PrintStream stream = new PrintStream(file, true);
if (!debug)
{
System.setOut(stream);
System.setErr(stream);
}
StringBuffer sb = readXMLFile(args[0]);
parseXMLConfig(sb);
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);
}
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 (isGUI)
{
dialog = initDialog();
}
// setting user and passwd
bean.setUserAndPasswd(user, passwd);
// setting user and passwd
beanDest.setUserAndPasswd(userDest, passwdDest);
StringBuffer command = null;
String query = null;
try
{
bean.setConnection(source);
if (names == null)
names = bean.getTableNames();
// Collections.sort(names);
int tbIndex = 1;
System.out.println("Start at table " + names.firstElement());
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 != "")
{
query = addLayoutToQuery(names, query, tbIndex, layout);
}
if (dialog != null)
{
prepareDialogforUse(names, dialog, tbIndex);
}
bean.getConnection();
bean.makeQuery(query, 0);
}
catch (Exception e)
{
System.out.println("Warning exception occured \n " + 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(destination);
Statement stm = beanDest.getConnection().createStatement();
Vector tables = beanDest.getTableNames();
System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
tables = beanDest.getTableNames();
stm = beanDest.getConnection().createStatement();
if (dialog != null)
dialog.title.setText("Updating table data ...");
else
System.out.println("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(); ++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());
pstm.execute();
if (dialog != null)
dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
command = null;
} // to for loop
}
}
catch (Exception e)
{
System.out.println("Error while connecting to database " + e);
if (isGUI)
{
showExceptionDialog(dialog, command, e);
}
else
{
e.printStackTrace();
}
} finally
{
if (isGUI)
{
resetGUI(dialog);
}
}
}
/**
* @param dialog
*/
private static void resetGUI(FM2SQL.ProgressDialog dialog)
{
dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
dialog.setVisible(false);
}
/**
* @param dialog
* @param command
* @param e
*/
private static void showExceptionDialog(FM2SQL.ProgressDialog dialog, StringBuffer command, Exception 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);
FM2SQL.showErrorDialog(b.toString(), "Error occured !");
}
/**
* @return
*/
private static FM2SQL.ProgressDialog initDialog()
{
FM2SQL.ProgressDialog dialog;
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(420, 250);
return dialog;
}
/**
* 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 (isGUI)
{
dialog = initDialog();
}
System.out.println("connection established " + source + " " + bean.url);
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();
int tbIndex = 1;
for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
{
Vector[] result = null;
try
{
String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
query = (selects != null) ? selects.get(tbIndex).toString() : "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
if (layout != "")
{
query = addLayoutToQuery(names, query, tbIndex, layout);
}
if (dialog != null)
{
prepareDialogforUse(names, dialog, tbIndex);
}
bean.getConnection();
bean.makeQuery(query, 50);
idField = ids.get(tbIndex).toString();
}
catch (Exception e)
{
System.out.println("Warning exception occured \n " + 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 ((tables.indexOf(destTableName) < 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().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);
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));
}
if (isGUI)
resetGUI(dialog);
}
}
catch (Exception e)
{
System.out.println("Error while connecting to database " + e);
if (isGUI)
{
showExceptionDialog(dialog, command, e);
resetGUI(dialog);
}
else
{
e.printStackTrace();
}
}
}
/**
* @param names
* @param dialog
* @param tbIndex
*/
private static void prepareDialogforUse(Vector names, FM2SQL.ProgressDialog dialog, int tbIndex)
{
dialog.title.setText("Reading table data ...");
dialog.table.setText(names.get(tbIndex).toString());
dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
dialog.setVisible(true);
}
/**
* @param names
* @param query
* @param tbIndex
* @param layout
* @return
*/
private static String addLayoutToQuery(Vector names, String query, int tbIndex, String 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);
return query;
}
/**
* 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++;
Object obj = null;
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();
if (isGUI)
dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
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;
Vector databases = new Vector();
try
{
databases = getXMLConfig(sb);
DBBean database = ((DataBase) databases.lastElement()).bean;
databases.remove(databases.size() - 1);
// databases.add(database);
for (Iterator iter = databases.iterator(); iter.hasNext();)
{
DataBase db = (DataBase) iter.next();
int mode = db.mode;
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)
{
Convert.user = db.bean.user;
Convert.passwd = db.bean.passwd;
userDest = database.user;
passwdDest = database.passwd;
update(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates, db.ids, mode);
}
else if (mode == DataBase.SYNCHRONIZE_MODE)
{
Convert.user = db.bean.user;
Convert.passwd = db.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, new Vector(db.htIndex.values()));
}
}
}
catch (Exception e)
{
e.printStackTrace();
} finally
{
bean.closeAllConnections();
beanDest.closeAllConnections();
}
}
public static Vector getXMLConfig(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 (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;
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(new DataBase(database, null, null, null, null, null, 0));
}
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 StringBuffer readXMLFile(String xmlFile)
{
InputStream stream = null;
StringBuffer sb = new StringBuffer();
try
{
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);
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();
} finally
{
try
{
stream.close();
}
catch (IOException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
return sb;
}
/**
* Helper class for Conversion etc. Contains data needed for the conversion
*
* @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 = "//";
/**
* maps table name to index fields
*/
Hashtable htIndex = new Hashtable();
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);
}
/**
* @param indexListVec
*/
public void buildIndexTable(Vector indexListVec)
{
for (int i = 0; i < tables.size(); i++)
{
fillIndexList((String) tables.get(i), (String) indexListVec.get(i));
}
}
/**
* 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";
else if (mode == SYNCHRONIZE_MODE)
modeString = "synchronize";
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);
IndexList indexList = (IndexList) htIndex.get(table);
if (indexList == null)
indexList = new IndexList();
buffr.write(" <table name = \"" + table + "\" layout = \"" + layout + "\" id = \"" + id + "\" indexList =\"" + indexList + "\">\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 void fillIndexList(String table, String list)
{
IndexList indexList = new IndexList();
StringTokenizer tokenizer = new StringTokenizer(list, ",");
while (tokenizer.hasMoreTokens())
{
indexList.add(tokenizer.nextToken());
}
System.out.println(indexList);
htIndex.put(table, indexList);
}
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 (isGUI)
{
dialog = initDialog();
}
// setting user and passwd
bean.setUserAndPasswd(user, passwd);
// setting user and passwd
beanDest.setUserAndPasswd(userDest, passwdDest);
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.setVisible(true);
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(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);
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 (isGUI)
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 (isGUI)
{
showExceptionDialog(dialog, command, e);
resetGUI(dialog);
}
else
{
e.printStackTrace();
}
}
} // 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, Vector indexList)
throws Exception
{
System.out.println(" bin in synchronize!!!");
FM2SQL.ProgressDialog dialog = null;
if (isGUI)
{
dialog = initDialog();
dialog.setTitle("Synchronize running ...");
}
// setting user and passwd
bean.setUserAndPasswd(user, passwd);
// setting user and passwd
beanDest.setUserAndPasswd(userDest, passwdDest);
StringBuffer command = null;
String query = null;
try
{
bean.setConnection(source);
if (names == null)
names = bean.getTableNames();
int tbIndex = 1;
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(""))
{
query = addLayoutToQuery(names, query, tbIndex, layout);
}
if (dialog != null)
{
prepareDialogforUse(names, dialog, tbIndex);
}
bean.getConnection();
bean.makeQuery(query, 50);
idField = ids.get(tbIndex).toString();
}
catch (Exception e)
{
System.out.println("Warning exception occured \n " + 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);
destQuery = destQuery.replaceAll(bean.getQC(), beanDest.getQC());
destQuery = removeLayoutPartFromQuery(destQuery, layouts.get(tbIndex).toString());
// TODO remove layout part for destQuery
String tempQueryDest = destQuery;
// remove extra query parts
// destQuery.substring(0,destQuery.lastIndexOf(destTableName)+destTableName.length()+1);
System.out.println("new Query " + tempQueryDest);
System.out.println("idfield " + idField + " " + ids.get(tbIndex).toString());
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"));
System.out.println("ids found for " + idField + " " + !myIds.isEmpty());
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.size() + " " + destTableName);
System.out.println("linesToDelete " + linesToDelete.size() + " " + 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, dialog);
// 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, dialog);
// 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() + "'";
}
String tableName = names.get(tbIndex).toString();
if (!indexList.isEmpty())
{
IndexList idList = (IndexList) indexList.get(0);
System.out.println("found list " + idList);
Statement stm = beanDest.getConnection().createStatement();
Vector destTables = beanDest.getTableNames();
System.out.println("tempQueryDest" + tempQueryDest);
beanDest.makeQuery(tempQueryDest, 0);
for (Iterator iter = idList.iterator(); iter.hasNext();)
{
String indexField = (String) iter.next();
indexField = convertText(indexField);
String indexName = destTableName + "_" + indexField;
if (destTables.contains(indexName))
{
stm.execute("DROP INDEX " + destTableName + "_" + indexField);
// continue;
}
// stm.execute("DROP INDEX
// "+destTableName+"_"+indexField);
String type = beanDest.getColumnType(indexField).toLowerCase();
// System.out.println(indexField+" "+type+"
// "+(type.indexOf("text") >= 0 ||
// type.indexOf("varchar") >= 0 || type.indexOf("char")
// >= 0));
if (type.indexOf("text") >= 0 || type.indexOf("varchar") >= 0 || type.indexOf("char") >= 0)
{
if (beanDest.url.indexOf("mysql") >= 0)
{
// System.out.println("CREATE INDEX " +
// indexName + " ON " + destTableName + " (" +
// indexField + "(10))");
// TODO problem if index exist !!!
stm.execute("CREATE INDEX " + indexName + " ON " + destTableName + " (" + indexField + "(10))");
}
else
{
stm.execute("CREATE INDEX " + indexName + " ON " + destTableName + " (lower( " + indexField + "))");
}
}
else
{
stm.execute("CREATE INDEX " + destTableName + "_" + indexField + " ON " + destTableName + "(" + indexField + ")");
}
// stm.execute("DROP INDEX
// "+destTableName+"_"+indexField);
}
}
// CREATE UNIQUE INDEX title_idx ON films (title);
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 (isGUI)
showExceptionDialog(dialog, command, e);
}
if (isGUI)
{
resetGUI(dialog);
}
}
/**
* @param destQuery
* @param string
* @return
*/
private static String removeLayoutPartFromQuery(String destQuery, String layoutName)
{
String removeString = "layout " + beanDest.getQC() + layoutName + beanDest.getQC();
destQuery = destQuery.replaceFirst(removeString, "");
System.out.println("destQuery change to " + destQuery);
return destQuery;
}
private static void performSynchronize(String idField, Vector vec, String tempQuery, TreeSet linesToDelete, TreeSet linesToAppend, PreparedStatement insPst, PreparedStatement updPst,
PreparedStatement delPSt, int deltaID, String delimiter, FM2SQL.ProgressDialog dialog) throws SQLException, ParseException, Exception
{
if (dialog != null)
{
dialog.progress.setValue(0);
dialog.title.setText("Retrieving new data");
}
Vector[] vectors = bean.getQueryData(tempQuery, deltaID);
int count = 0, size = vectors[0].size();
int idIndex = vectors[1].indexOf(idField);
// System.out.println(idIndex + " " + vectors[1] + " " + idField);
// todo arraylist code has to be added
if (dialog != null)
dialog.title.setText("Synchronize with new data");
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.addBatch();
// updPst.execute();
}
if (dialog != null)
{
int value = (int) Math.round(((double) count / (double) size) * 100.0);
dialog.progress.setValue(value);
count++;
}
}
// updPst.executeBatch();
} // 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] = beanDest.getQC() + convertText(fieldName) + beanDest.getQC();
// 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] = beanDest.getQC() + convertText(element) + beanDest.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 < fieldNames.length - 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(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;
}
public void makeTest(String table, String idField, String tempQuery) throws Exception
{
int counter = 0;
// ****** test code *****
bean.getConnection();
ResultSet resultSet = null;
String lastResult = "P227634.11";// "P227625.79554";//"P227625.77391";//"P116034.970998";
String myQuery = "select " + bean.getQC() + idField + bean.getQC() + ",serial " + " from " + bean.getQC() + table + bean.getQC();
System.out.println("Query is now " + myQuery);
JDialog statusDialog = new JDialog();
statusDialog.setTitle("Status Information");
JLabel status = new JLabel("actual DataSet : ");
JLabel status2 = new JLabel(Integer.toString(++counter));
JLabel status3 = new JLabel(lastResult);
JPanel statusPanel = new JPanel();
JPanel statusPanel2 = new JPanel();
statusPanel.add(status);
statusPanel.add(status2);
statusPanel2.add(status3);
statusDialog.getContentPane().add(statusPanel, "North");
statusDialog.getContentPane().add(statusPanel2, "Center");
statusDialog.setLocation(400, 500);
statusDialog.setSize(300, 150);
statusDialog.setVisible(true);
while (true)
{
if (!statusDialog.isVisible())
statusDialog.setVisible(true);
tempQuery = myQuery + " where " + bean.getQC() + idField + bean.getQC() + ">'" + lastResult + "'";
resultSet = bean.makeQuery(tempQuery, 1);
if (resultSet == null)
{
System.out.println("lastResult was " + lastResult + " counter was " + counter);
break;
}
else
{
resultSet.next();
lastResult = resultSet.getString(1);
counter++;
status2.setText(Integer.toString(counter));
status3.setText(lastResult + " " + resultSet.getString(2));
if (counter % 100 == 0)
{
System.out.println("actual Result was " + lastResult + " counter was " + counter);
// break;
}
}
resultSet = null;
}
System.exit(0);
// ****** end Test ******
}
}
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/cvs/cvsweb/icons/back.gif){kind=icon}
Return to Convert.java CVS log ![[TXT]](/cvs/cvsweb/icons/text.gif){kind=icon}
![[DIR]](/cvs/cvsweb/icons/dir.gif){kind=icon}
Up to [Repository] / FM2SQL / src