FM2SQL/Convert.java - view

File: [Repository] / FM2SQL / Attic / Convert.java
Revision 1.63: download - view: text, annotated - select for diffs - revision graph
Tue Jul 27 10:41:36 2004 UTC (19 years, 10 months ago) by rogo
Branches: MAIN
CVS tags: HEAD

progress in synchronize mode

/* * 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.util.ArrayList; import java.util.Iterator; import java.util.List; 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 if (!creates.get(tbIndex).equals("")) { String create = creates.get(tbIndex).toString().toLowerCase(); int fromIndex = create.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 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(names.get(tbIndex).toString()); // 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]); } } } 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); } } // printContents(node3); // FM2SQL.fmInstance=new FM2SQL(); } catch (Exception e) { e.printStackTrace(); } } 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 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) 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; 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); Vector linesToAppend = new Vector(vec); // 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+" "+vecDest+" "+destTableName); System.out.println("linesToDelete "+linesToDelete+" "+vecDest+" "+destTableName); // prepare new query for next chunk if (query.indexOf("where") > 0) tempQuery = query + " and " + tempID + ">='" + vec.firstElement() + "' and "+tempID + "<='" + vec.lastElement() + "' order by "+tempID; else tempQuery = query + " where " + tempID + ">='" + vec.firstElement() + "' and "+tempID + "<='" + vec.lastElement() + "'";// order by "+tempID; System.out.println("tempQuery is now "+tempQuery+" order by "+tempID); // bean.makeQuery(tempQuery,vec.size()); Vector[] vectors=bean.getQueryData(tempQuery,vec.size()); System.out.println(vectors[0]); System.out.println( bean.getColumnNames().indexOf(idField)); System.exit(0); // 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 /** * 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(); } }