Annotation of FM2SQL/src/Convert.java, revision 1.15
1.1 rogo 1: /*
2: * Convert.java -- Converter class - Filemaker to SQL Converter Copyright (C)
3: * 2003 Robert Gordesch (rogo@mpiwg-berlin.mpg.de) This program is free
4: * software; you can redistribute it and/or modify it under the terms of the GNU
5: * General Public License as published by the Free Software Foundation; either
6: * version 2 of the License, or (at your option) any later version. Please read
7: * license.txt for the full details. A copy of the GPL may be found at
8: * http://www.gnu.org/copyleft/lgpl.html You should have received a copy of the
9: * GNU General Public License along with this program; if not, write to the Free
10: * Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
11: * USA Created on 15.09.2003 by rogo
12: */
13:
14: import java.awt.Cursor;
15: import java.io.BufferedReader;
16: import java.io.BufferedWriter;
17: import java.io.File;
18: import java.io.FileInputStream;
19: import java.io.FileNotFoundException;
20: import java.io.FileOutputStream;
21: import java.io.IOException;
22: import java.io.InputStream;
23: import java.io.InputStreamReader;
24: import java.io.OutputStreamWriter;
25: import java.io.PrintStream;
26: import java.net.URL;
27: import java.sql.PreparedStatement;
28: import java.sql.ResultSet;
29: import java.sql.SQLException;
30: import java.sql.Statement;
31: import java.sql.Types;
1.11 rogo 32: import java.text.DateFormat;
1.1 rogo 33: import java.text.ParseException;
34: import java.util.ArrayList;
1.11 rogo 35: import java.util.Date;
1.1 rogo 36: import java.util.Hashtable;
37: import java.util.Iterator;
38: import java.util.List;
39: import java.util.StringTokenizer;
40: import java.util.TreeSet;
41: import java.util.Vector;
42:
43: import javax.swing.JDialog;
44: import javax.swing.JLabel;
45: import javax.swing.JPanel;
46:
47: import com.exploringxml.xml.Node;
48: import com.exploringxml.xml.Xparse;
49:
50: class Convert
51: {
1.11 rogo 52: private static final String _TEMP = "_temp";
53:
1.4 rogo 54: /**
55: * Helper class for index creation
56: *
57: * @author rogo
58: *
59: */
60: public static class IndexList extends Vector
61: {
62: public String toString()
63: {
64: StringBuffer buff = new StringBuffer(1000);
65: int count = 0;
66: for (Iterator iter = this.iterator(); iter.hasNext();)
67: {
68: String element = (String) iter.next();
69: if (count < elementCount - 1)
70: {
71: buff.append(element).append(", ");
72: count++;
73: } else
74: buff.append(element);
75:
76: }
77:
78: return buff.toString();
79: }
80: }
81:
82: /**
83: * Helper class for pre/or post execution of SQLCommands
84: * @author rogo
85: *
86: */
87: public static class SQLCommand
88: {
89: private DBBean bean;
90: private String command;
91:
92: public SQLCommand(DBBean bean, String command)
93: {
94: this.bean = bean;
95: this.command = command;
96: }
97:
98: public void executeCommand() throws SQLException, Exception
99: {
1.6 rogo 100: System.out.println("Executing command: \n");
101: System.out.println(command);
102:
1.4 rogo 103: java.sql.Connection con = bean.getConnection();
104: Statement stm = con.createStatement();
105: stm.execute(command);
1.6 rogo 106: stm.close();
1.4 rogo 107: }
108: }
109:
110: static DBBean bean = new DBBean();
111:
112: static DBBean beanDest = new DBBean();
113:
114: static String user = "", passwd = "e1nste1n";
115:
116: static String userDest = "postgres", passwdDest = "rogo";
117:
118: static boolean batchRun = false;
119:
120: static Vector databases = new Vector();
121:
122: final static int numHits = 5000;
123:
124: final static int numIntervalls = 4;
125:
1.12 rogo 126: static boolean debug = false;
1.4 rogo 127:
128: static boolean isGUI = true;
129:
130: /**
131: * Vector for all SQLCommands to executed before any conversion action is performed
132: */
133: static Vector preSQLCommands = new Vector();
134: /**
135: * Vector for all SQLCommands to executed after any conversion action has been performed
136: */
137:
138: static Vector postSQLCommands = new Vector();
139:
1.15 ! rogo 140: static final String versionID = new String("FM2SQL Version 0.9.9b\n");
1.10 rogo 141:
142: private static boolean noError = false;
1.7 rogo 143:
1.4 rogo 144: public static void main(String args[]) throws IOException
145: {
1.12 rogo 146: // DateFormat d = DateFormat.getDateTimeInstance(DateFormat.SHORT, DateFormat.SHORT,
147: // Locale.GERMANY);
148: // System.out.println(convertText("rogoistSuper" + "_" + d.format(new Date()).replace(':', '_')));
149:
1.4 rogo 150: /*
151: * try { //byte[] b = "�".getBytes("UTF-8"); //
152: * System.out.println("QueryString " +b[0]+" "+b[1]+(new
153: * String(b).getBytes()[0])+" "+new String(b).getBytes()[1]);
154: * //System.out.println(new String(b,"UTF-8")); } catch
155: * (UnsupportedEncodingException e) { e.printStackTrace(); }
156: */
1.8 rogo 157: File tmpPath = new File(System.getProperty("java.io.tmpdir"));
1.4 rogo 158: isGUI = false;
159: FileOutputStream file = null;
160: if (args.length != 1)
161: {
1.7 rogo 162: System.out.println(versionID);
1.4 rogo 163: System.out.println("Usage: java Convert <xml config file>");
164: System.exit(-1);
165: }
1.8 rogo 166: File temp = null;
1.4 rogo 167: try
168: {
1.10 rogo 169: temp = new File(tmpPath, "fm2sql.txt");
1.8 rogo 170: int count = 1;
171: while (temp.exists())
172: {
173: temp = new File(tmpPath, "fm2sql" + generateSuffix(count++) + ".txt");
174: }
1.4 rogo 175: file = new FileOutputStream(temp);
176: } catch (FileNotFoundException e1)
177: {
178: e1.printStackTrace();
179: }
180: PrintStream stream = new PrintStream(file, true);
1.8 rogo 181: // write info for user to stdout
182: System.out.println(versionID);
1.10 rogo 183: System.out.println("Loading " + args[0] + "....");
184: System.out.println("Log will be written to " + temp.getCanonicalPath());
1.8 rogo 185:
1.4 rogo 186: if (!debug)
187: {
188: System.setOut(stream);
189: System.setErr(stream);
190: }
1.7 rogo 191: System.out.println(versionID);
1.10 rogo 192: System.out.println("Using config file : " + args[0] + "....");
193:
1.4 rogo 194: StringBuffer sb = readXMLFile(args[0]);
195: parseXMLConfig(sb);
196: if (!(new File(args[0]).exists()))
1.7 rogo 197: {
198:
1.4 rogo 199: System.exit(0);
1.7 rogo 200: }
1.4 rogo 201: System.out.println("Finished!");
202: // convert("jdbc:fmpro:http://141.14.237.74:8050","jdbc:postgresql://erebos/test",null,null);
203: }
204:
205: public static void convertBatch(DBBean source, DBBean destination, Vector names, Vector layouts,
206: Vector selects, Vector creates, Vector ids, int mode, String delimiter) throws Exception
207: {
208: bean = source;
209: beanDest = destination;
210: convert(null, null, names, layouts, selects, creates, ids, mode, delimiter);
211: }
212:
213: public static String formatFileMakerArray(List list, String delimiter)
214: {
215: StringBuffer formattedString = new StringBuffer();
216: for (int i = 0; i < list.size(); ++i)
217: {
218: formattedString.append(list.get(i).toString());
219: if (i < list.size() - 1)
220: formattedString.append(delimiter);
221: }
222: return formattedString.toString();
223: }
224:
225: /**
226: * Method for SQL UPDATE
227: *
228: * @param source
229: * @param destination
230: * @param names
231: * @param layouts
232: * @param selects
233: * @param creates
234: * @param ids
235: * @param mode
236: * @throws Exception
237: */
238: public static void update(String source, String destination, Vector names, Vector layouts,
239: Vector selects, Vector creates, Vector ids, int mode) throws Exception
240: {
241:
242: FM2SQL.ProgressDialog dialog = null;
243: if (isGUI)
244: {
245: dialog = initDialog();
246: }
247: // setting user and passwd
248: bean.setUserAndPasswd(user, passwd);
249: // setting user and passwd
250: beanDest.setUserAndPasswd(userDest, passwdDest);
251:
252: StringBuffer command = null;
253: String query = null;
254: try
255: {
256:
257: bean.setConnection(source);
258:
259: if (names == null)
260: names = bean.getTableNames();
261: // Collections.sort(names);
262: int tbIndex = 1;
263:
264: System.out.println("Start at table " + names.firstElement());
265: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
266: {
267: Vector[] result = null;
268: String destTableName = "";
269: try
270: {
271: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
272: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
273: query = (selects != null) ? selects.get(tbIndex).toString() : query;
274: // if vectors[1].get(i) != null)
1.10 rogo 275: if (!layout.equals(""))
1.4 rogo 276: {
1.11 rogo 277:
1.4 rogo 278: query = addLayoutToQuery(names, query, tbIndex, layout);
279:
280: }
281: if (dialog != null)
282: {
283: prepareDialogforUse(names, dialog, tbIndex);
284: }
285: bean.getConnection();
286: bean.makeQuery(query, 0);
287:
288: } catch (Exception e)
289: {
290: System.out.println("Warning exception occured \n " + e);
291:
292: continue;
293: }
294: // determine destTableName from createStatement or from source
295: // table name
296: if (!creates.get(tbIndex).equals(""))
297: {
298: String create = creates.get(tbIndex).toString().toLowerCase();
299: int fromIndex = create.indexOf("table") + 5;
300: int toIndex = create.indexOf("(");
301: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "")
302: .trim();
303: System.out.println("destTable " + destTableName);
304:
305: } else
306: destTableName = convertText(names.get(tbIndex).toString());
307:
308: beanDest.setConnection(destination);
309:
310: Statement stm = beanDest.getConnection().createStatement();
311:
312: Vector tables = beanDest.getTableNames();
313:
314: System.out.println(names.get(tbIndex) + " "
315: + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
316: tables = beanDest.getTableNames();
317: stm = beanDest.getConnection().createStatement();
318:
319: if (dialog != null)
320: dialog.title.setText("Updating table data ...");
321: else
322: System.out.println("Updating table data ...");
323: int j = -1;
324:
325: Vector row = null;
326: command = new StringBuffer();
327:
328: command.append("UPDATE ");
329: command.append(beanDest.getQC());
330: command.append(destTableName);
331: // command.append(convertText((String) names.get(tbIndex)));
332: command.append(beanDest.getQC());
333: command.append(" SET ");
334:
335: int size = bean.getColumnNames().size();
336: for (int i = 0; i < size - 1; ++i)
337: command.append(beanDest.getQC() + convertText((String) bean.getColumnNames().get(i))
338: + beanDest.getQC() + " = ? ,");
339: command.append(convertText((String) bean.getColumnNames().get(size - 1)) + " = ? ");
340: command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
341: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
342: System.out.println(command + " " + tbIndex);
343: int rowCount = bean.getRowCount(query);
344: int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
345: while ((row = bean.getNextRow()) != null)
346: {
347: j++;
348: // print rows
349: Object obj = null;
350: for (int k = 0; k < row.size(); ++k)
351: {
352: obj = row.get(k);
353: if (obj instanceof ArrayList)
354: obj = ((List) obj).get(0);
355: String str = (obj == null) ? "NULL" : obj.toString();
356: if (!str.equals("NULL"))
357: pstm.setString(k + 1, str);
358: else
359: pstm.setNull(k + 1, Types.NULL);
360: }
361: pstm.setString(row.size() + 1, row.get(idIndex).toString());
362: pstm.execute();
363: if (dialog != null)
364: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
365: command = null;
366: } // to for loop
367:
368: }
369: } catch (Exception e)
370: {
371: System.out.println("Error while connecting to database " + e);
372: if (isGUI)
373: {
374: showExceptionDialog(dialog, command, e);
375: } else
376: {
377: e.printStackTrace();
378:
379: }
380: } finally
381: {
382: if (isGUI)
383: {
384: resetGUI(dialog);
385: }
386: }
387: }
388:
389: /**
390: * @param dialog
391: */
392: private static void resetGUI(FM2SQL.ProgressDialog dialog)
393: {
394: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
395: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
396: dialog.setVisible(false);
397: }
398:
399: /**
400: * @param dialog
401: * @param command
402: * @param e
403: */
404: private static void showExceptionDialog(FM2SQL.ProgressDialog dialog, StringBuffer command,
405: Exception e)
406: {
407: dialog.setVisible(false);
408: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
409: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
410: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
411: java.io.PrintStream stream = new java.io.PrintStream(b);
412: stream.print(command + "\n\n");
413: e.printStackTrace(stream);
414: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
415: }
416:
417: /**
418: * @return
419: */
420: private static FM2SQL.ProgressDialog initDialog()
421: {
422: FM2SQL.ProgressDialog dialog;
423: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
424: dialog.setTitle("Conversion running ...");
425: dialog.title.setText("Getting table data ...");
426: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x
427: + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y
428: + (FM2SQL.fmInstance.getHeight() - 250) / 2);
429: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
430: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
431: dialog.thread = Thread.currentThread();
432: dialog.setSize(420, 250);
433: return dialog;
434: }
435:
436: /**
437: * transfers the specified array of tables to the destination database and
438: * creates the table if it does not exist if it exists and mode is not append
439: * the table is dropped
440: *
441: * @param source
442: * @param destination
443: * @param names
444: * @param layouts
445: * @param selects
446: * @param creates
447: * @param ids
448: * @param mode
449: * @throws Exception
450: */
451:
452: public static void convert(String source, String destination, Vector names, Vector layouts,
453: Vector selects, Vector creates, Vector ids, int mode, String delimiter) throws Exception
454: {
1.7 rogo 455: // TODO implement convert_temp mode!!!
1.4 rogo 456: FM2SQL.ProgressDialog dialog = null;
457:
458: if (isGUI)
459: {
460: dialog = initDialog();
461: }
462:
463: System.out.println("connection established " + source + " " + bean.url);
464:
465: java.util.TreeSet myIds = new TreeSet();
466: int deltaID = 1;
467: String idField = "";
468: String destTableName = "";
469: String[] fieldNames = null;
470:
471: if (source != null && destination != null)
472: {
473: // setting user and passwd
474: bean.setUserAndPasswd(user, passwd);
475: // setting user and passwd
476: beanDest.setUserAndPasswd(userDest, passwdDest);
477: }
478:
479: StringBuffer command = null;
480: String query = null;
481: try
482: {
483:
484: if (source != null)
485: bean.setConnection(source);
486: else
487: bean.setConnection(bean.url);
488: if (names == null)
489: names = bean.getTableNames();
490: int tbIndex = 1;
491:
492: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
493: {
494: Vector[] result = null;
495: try
496: {
497: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
498: query = (selects != null) ? selects.get(tbIndex).toString() : "select * from "
499: + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
1.10 rogo 500: if (layout.intern() != "")
1.4 rogo 501: {
502: query = addLayoutToQuery(names, query, tbIndex, layout);
503:
504: }
505: if (dialog != null)
506: {
507: prepareDialogforUse(names, dialog, tbIndex);
508: }
509:
510: bean.getConnection();
511: bean.makeQuery(query, 50);
512: idField = ids.get(tbIndex).toString();
513:
514: } catch (Exception e)
515: {
516: System.out.println("Warning exception occured \n " + e);
517:
518: continue;
519: }
520: if (destination != null)
521: beanDest.setConnection(destination);
522: else
523: beanDest.setConnection(beanDest.url);
524: Statement stm = beanDest.getConnection().createStatement();
525:
526: Vector tables = beanDest.getTableNames();
527: // Collections.sort(tables);
528: System.out.println(names.get(tbIndex) + " "
529: + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
530: tables = beanDest.getTableNames();
531: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
532: stm = beanDest.getConnection().createStatement();
533: // System.exit(0);
534:
535: // determine destTableName from createStatement or from source
536: // table name
537: ConversionProperties prop = getFieldNamesAndDestTableName(creates.get(tbIndex).toString(),
538: query, names.get(tbIndex).toString());
539: destTableName = prop.destTableName;
1.7 rogo 540: if (mode == Convert.DataBase.CONVERT_TEMP_MODE)
541: {
1.15 ! rogo 542: String tableNameSave=destTableName;
1.11 rogo 543: destTableName += _TEMP;
1.15 ! rogo 544: if(creates.get(tbIndex)!="")
! 545: {
! 546: System.out.println("Changed tablename of create statement to "+destTableName);
! 547: StringBuffer buffer=new StringBuffer(creates.get(tbIndex).toString());
! 548: int startIndex = buffer.indexOf(tableNameSave);
! 549: buffer.replace(startIndex,startIndex+tableNameSave.length(),destTableName);
! 550: creates.set(tbIndex,buffer.toString());
! 551: }
1.7 rogo 552: }
1.15 ! rogo 553: System.out.println("destTableName is now "+destTableName);
! 554:
1.4 rogo 555: fieldNames = prop.fieldNames;
1.7 rogo 556: if (mode == Convert.DataBase.CONVERT_MODE || mode == Convert.DataBase.CONVERT_TEMP_MODE)
1.4 rogo 557: {
558: if (tables.indexOf(destTableName) >= 0)
559: {
560: stm.executeUpdate("drop table " + beanDest.getQC() + destTableName + beanDest.getQC());
561: tables.remove(destTableName);
562: System.out.println("dropped table" + destTableName);
563: }
564: if ((tables.indexOf(destTableName) < 0))
565: {
566:
567: if (creates.get(tbIndex).equals("")
568: || creates.get(tbIndex).toString().toLowerCase().indexOf("create") < 0)
569: {
570: System.out
571: .println("Warning empty or invalid create statement - creating one for you\n");
572:
573: command = new StringBuffer(50);
574: command.append("CREATE TABLE ");
575: command.append(beanDest.getQC());
1.7 rogo 576: command.append(destTableName);//convertText((String) names.get(tbIndex)));
1.4 rogo 577: command.append(beanDest.getQC());
578: command.append("(");
579: String type = null;
580: Vector columnNames = bean.getColumnNames();
581: for (int i = 0; i < columnNames.size() - 1; ++i)
582: {
583: type = bean.metaData.getColumnTypeName(i + 1);
584: // System.out.println(i+" "+result[1].get(i)+"
585: // "+type);
586: type = (type.equals("NUMBER")) ? "INT4" : type;
587: type = (type.equals("CONTAINER")) ? "TEXT" : type;
588:
589: command.append(beanDest.getQC() + convertText((String) columnNames.get(i))
590: + beanDest.getQC() + " " + type + ", ");
591: }
592: type = bean.metaData.getColumnTypeName(columnNames.size());
593: type = (type.equals("NUMBER")) ? "INT4" : type;
594: type = (type.equals("CONTAINER")) ? "TEXT" : type;
595: command.append(beanDest.getQC()
596: + convertText((String) columnNames.get(columnNames.size() - 1))
597: + beanDest.getQC() + " " + type);
598: command.append(" )");
599:
600: } else
1.15 ! rogo 601: {
! 602: command = new StringBuffer().append(creates.get(tbIndex).toString());
! 603: }
! 604: stm.executeUpdate(command.toString());
1.4 rogo 605:
606: }
607: }
608: if (dialog != null)
609: dialog.title.setText("Writing table data ...");
610:
611: // prepare the insert statement
612: int j = -1;
613: Vector row = null;
614: command = new StringBuffer();
615:
616: command.append("INSERT INTO ");
617: command.append(beanDest.getQC());
618: command.append(destTableName);
619: command.append(beanDest.getQC());
620: command.append(" (");
621: for (int i = 0; i < fieldNames.length; i++)
622: {
623: command.append(fieldNames[i]);
624: if (i < fieldNames.length - 1)
625: command.append(",");
626: }
627: command.append(") ");
628:
629: command.append(" values ( ");
630: // add a question marks for every field
631: for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
632: command.append("?,");
633: command.append("?)");
634: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
635: System.out.println(command);
636: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
637: Vector vec = new Vector(myIds);
638: int endIndex = -1;
639: String tempQuery = query;
640: String tempID = bean.getQC() + idField + bean.getQC();
641: // if id_field not do incremental conversion else do it all at
642: // once
643: if (!idField.equals(""))
644: {
645: long startTime = System.currentTimeMillis();
646: int counter = -1;
647: while (true)
648: {
649: ++counter;
650: if (counter == 0 && dialog != null)
651: dialog.title.setText("Check if data is available");
652: else if (dialog != null)
653: dialog.title.setText("Check if more data is available");
654: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex),
655: tempQuery, numHits);
656: if (myIds.isEmpty())
657: break;
658: vec = new Vector(myIds);
659: rowCount = vec.size();
660: System.out.println("ID LIST SIZE "
661: + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIds.size());
662: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
663: if (vec.size() <= numIntervalls)
664: {
665: endIndex = 0;
666: deltaID = vec.size();
667: }
668: for (int k = 0; k < vec.size() - deltaID; k = k + deltaID)
669: {
670: System.out.println(vec.get(k) + " " + vec.get(k + deltaID) + " " + vec.lastElement());
1.14 rogo 671: if (query.toLowerCase().indexOf("where") > 0)
1.4 rogo 672: tempQuery = query + " and " + tempID + ">='" + vec.get(k) + "' and " + tempID
673: + "<='" + vec.get(k + deltaID) + "'";
674: else
675: tempQuery = query + " where " + tempID + ">='" + vec.get(k) + "' and " + tempID
676: + "<='" + vec.get(k + deltaID) + "'";
677: System.out.println(tempQuery);
678: if (dialog != null)
679: dialog.title.setText("Reading table data ...");
680:
681: bean.makeQuery(tempQuery, deltaID);
682: if (dialog != null)
683: dialog.title.setText("Writing table data ...");
684:
685: command = writeDatainDestTable(dialog, command, k, pstm, rowCount, delimiter);
686: endIndex = k + deltaID;
687: }
688: System.out.println(endIndex);
689: // all data written ? if not write last chunk of data
690: if (endIndex == vec.size() - 1)
691: System.out.println("fits");
692: else
693: {
694: System.out.println(" last intervall from " + vec.get(endIndex) + " "
695: + vec.lastElement());
696:
1.14 rogo 697: if (query.toLowerCase().indexOf("where") > 0)
1.4 rogo 698: tempQuery = query + " and " + tempID + ">='" + vec.get(endIndex) + "' and "
699: + tempID + "<='" + vec.lastElement() + "'";
700: else
701: tempQuery = query + " where " + tempID + ">='" + vec.get(endIndex) + "' and "
702: + tempID + "<='" + vec.lastElement() + "'";
703: System.out.println(tempQuery);
704: if (dialog != null)
705: dialog.title.setText("Reading table data ...");
706: bean.makeQuery(tempQuery, 0);
707: if (dialog != null)
708: dialog.title.setText("Writing table data ...");
709: command = writeDatainDestTable(dialog, command, endIndex, pstm, rowCount, delimiter);
710: }
711: // prepare new query for next chunk
1.14 rogo 712: if (query.toLowerCase().indexOf("where") > 0)
1.4 rogo 713: tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
714: else
715: tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
716:
717: }
718: long endTime = System.currentTimeMillis();
719: System.out.println("Time for incremental convert elapsed " + (endTime - startTime));
720: } else
721: {
722: // read and write all in one big chunk
723: long startTime = System.currentTimeMillis();
724:
725: bean.makeQuery(query, 0);
1.10 rogo 726: System.err.println("query for whole table done");
1.4 rogo 727: command = writeDatainDestTable(dialog, command, j, pstm, rowCount, delimiter);
728: long endTime = System.currentTimeMillis();
729: System.out.println("Time for old convert elapsed " + (endTime - startTime));
730:
731: }
732: if (isGUI)
733: resetGUI(dialog);
1.10 rogo 734: noError = true;
1.11 rogo 735: if (mode == Convert.DataBase.CONVERT_TEMP_MODE)
736: {
737: String originalName = destTableName.split(_TEMP)[0];
738: // TODO
739: if (beanDest.getTableNames().contains(originalName))
740: {
1.13 rogo 741: DateFormat d = DateFormat.getDateTimeInstance(DateFormat.SHORT, DateFormat.MEDIUM);
1.11 rogo 742: d.setTimeZone(java.util.TimeZone.getTimeZone("CET"));
1.13 rogo 743:
1.11 rogo 744: String date = d.format(new Date());
745: date = date.replace(':', '_');
1.13 rogo 746: String newName = addQuotes(beanDest, convertText(originalName + "_" + date));
747: // rename table to newName
748: renameTable(beanDest, addQuotes(beanDest, originalName), newName);
1.11 rogo 749: }
1.13 rogo 750: // rename Table_temp to originalName
1.11 rogo 751: renameTable(beanDest, addQuotes(beanDest, destTableName), addQuotes(beanDest,
752: originalName));
753: }
1.4 rogo 754: }
755: } catch (Exception e)
756: {
1.10 rogo 757: System.out.println("Error while connecting to database " + e.getMessage());
758: noError = false;
1.4 rogo 759: if (isGUI)
760: {
761: showExceptionDialog(dialog, command, e);
762: resetGUI(dialog);
763: } else
764: {
765: e.printStackTrace();
766:
767: }
1.10 rogo 768: } catch (Error e)
769: {
1.11 rogo 770: noError = false;
771:
1.10 rogo 772: System.out.println(e);
773: e.printStackTrace();
1.4 rogo 774: }
775:
776: }
777:
778: /**
1.11 rogo 779: *
780: * @param beanDest2
781: * @param originalName
782: * @return
783: */
784: private static String addQuotes(DBBean bean, String originalName)
785: {
786: return bean.getQC() + originalName + bean.getQC();
787: }
788:
789: /**
1.4 rogo 790: * @param names
791: * @param dialog
792: * @param tbIndex
793: */
794: private static void prepareDialogforUse(Vector names, FM2SQL.ProgressDialog dialog, int tbIndex)
795: {
796: dialog.title.setText("Reading table data ...");
797: dialog.table.setText(names.get(tbIndex).toString());
798: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
799: dialog.setVisible(true);
800: }
801:
802: /**
803: * @param names
804: * @param query
805: * @param tbIndex
806: * @param layout
807: * @return
808: */
809: private static String addLayoutToQuery(Vector names, String query, int tbIndex, String layout)
810: {
811: layout = " layout " + bean.getQC() + layout + bean.getQC();
812: String name = names.get(tbIndex).toString();
813: StringBuffer queryLayout = new StringBuffer(query);
814: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
815: query = queryLayout.toString();
816: System.out.println("added layout " + query);
817: return query;
818: }
819:
820: /**
821: * Writes data to the destination table
822: *
823: * @param dialog
824: * progress dialog
825: * @param command
826: * @param j
827: * data index for progress bar
828: * @param pstm
829: * prepared statement
830: * @param rowCount
831: * number of datasets
832: * @return command
833: * @throws Exception
834: * @throws SQLException
835: */
836: private static StringBuffer writeDatainDestTable(FM2SQL.ProgressDialog dialog,
837: StringBuffer command, int j, PreparedStatement pstm, int rowCount, String delimiter)
838: throws Exception, SQLException
839: {
840: Vector row;
841: while ((row = bean.getNextRow()) != null)
842: {
843: j++;
844: Object obj = null;
845: for (int k = 0; k < row.size(); ++k)
846: {
847: obj = row.get(k);
848:
849: if (obj instanceof ArrayList)
850: obj = formatFileMakerArray((List) obj, delimiter);
851:
852: String str = (obj == null) ? "NULL" : obj.toString();
853: if (obj instanceof Double)
854: {
855: pstm.setDouble(k + 1, ((Double) obj).doubleValue());
856: } else if (!str.equals("NULL"))
857: pstm.setString(k + 1, str);
858: else
859: pstm.setNull(k + 1, Types.NULL);
860: }
861: pstm.execute();
862: if (isGUI)
863: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
864: command = null;
865: } // to while loop
866: return command;
867: }
868:
869: /**
870: * removes special characters from the input string as well as .fp5
871: *
872: * @param newName
873: * String to change
874: * @return
875: */
876: public static String convertText(String newName)
877: {
878: StringBuffer alterMe = new StringBuffer(newName.trim().toLowerCase());
879: int length = alterMe.length();
880: int j = 0;
881: int index = alterMe.indexOf(".fp5");
882: if (index >= 0)
883: {
884: alterMe.delete(index, index + 4);
885: length = length - 4;
886: }
887:
888: while (j < length)
889: {
890: if (alterMe.charAt(j) == ' ')
891: {
892: alterMe.setCharAt(j, '_');
893: // if(j<length-1) j=j+1;
894: } else if (alterMe.charAt(j) == '_')
895: {
896:
897: if (alterMe.charAt(j + 1) == '_')
898: alterMe.deleteCharAt(j);
899: length = length - 1;
900: // if(j<length-1) j=j+1;
901: } else if (alterMe.charAt(j) == 'ä')
902: {
903: alterMe.setCharAt(j, 'a');
904: alterMe.insert(j + 1, "e");
905: length = length + 1;
906: if (j < length - 1)
907: j = j + 1;
908: } else if (alterMe.charAt(j) == 'ö')
909: {
910: alterMe.setCharAt(j, 'o');
911: alterMe.insert(j + 1, "e");
912: length = length + 1;
913: if (j < length - 1)
914: j = j + 1;
915: } else if (alterMe.charAt(j) == 'ü')
916: {
917: alterMe.setCharAt(j, 'u');
918: alterMe.insert(j + 1, "e");
919: length = length + 1;
920: if (j < length - 1)
921: j = j + 1;
922: } else if (alterMe.charAt(j) == 'ß')
923: {
924: alterMe.setCharAt(j, 's');
925: alterMe.insert(j + 1, "s");
926: length = length + 1;
927: if (j < length - 1)
928: j = j + 1;
929: } else if (alterMe.charAt(j) == ':')
930: {
931: if (j < length - 1)
932: {
933: if (alterMe.charAt(j + 1) == ':')
934: {
935: alterMe.setCharAt(j, '_');
936: alterMe.delete(j + 1, j + 2);
937: length = length - 1;
938:
939: }
940:
941: if (j < length - 1)
942: j = j + 1;
943: }
944: } else if (alterMe.charAt(j) == '-')
945: {
946: alterMe.setCharAt(j, '_');
947:
948: } else if (alterMe.charAt(j) == '?')
949: {
950: // changed ? to _ because of update statement
951: alterMe.setCharAt(j, '_');
952: // length = length + 1;
953: // j=j+1;
954: System.out.println(alterMe);
955: } else if (alterMe.charAt(j) == '.')
956: {
957: if (j == length - 1)
958: {
959: alterMe.delete(j, j);
960: length--;
961: } else
962: alterMe.setCharAt(j, '_');
963: }
964:
965: ++j;
966: }
967: return alterMe.toString();
968: }
969:
970: /**
971: * Converts > and < in an entity (> or <)
972: *
973: * @param newName
974: * @return
975: */
976: public static String convertToEntities(String newName)
977: {
978: StringBuffer alterMe = new StringBuffer(newName.trim());
979: int length = alterMe.length();
980: int j = 0;
981:
982: while (j < length)
983: {
984:
985: if (alterMe.charAt(j) == '>')
986: {
987: alterMe.setCharAt(j, '&');
988: alterMe.insert(j + 1, "gt;");
989: length = length + 2;
990: if (j < length - 1)
991: j = j + 1;
992:
993: } else if (alterMe.charAt(j) == '<')
994: {
995: alterMe.setCharAt(j, '&');
996: alterMe.insert(j + 1, "lt;");
997: length = length + 2;
998: if (j < length - 1)
999: j = j + 1;
1000:
1001: }
1002: ++j;
1003: }
1004: return alterMe.toString();
1005: }
1006:
1007: /**
1008: * Masks the single quote character '-->\'
1009: *
1010: * @param newName
1011: * @return
1012: */
1013: public static String convertUml(String newName)
1014: {
1015: StringBuffer alterMe = new StringBuffer(newName.trim());
1016: int length = alterMe.length();
1017: int j = 0;
1018:
1019: while (j < length)
1020: {
1021:
1022: if (alterMe.charAt(j) == '\'')
1023: {
1024: alterMe.setCharAt(j, '\\');
1025: alterMe.insert(j + 1, "'");
1026: length = length + 1;
1027: if (j < length - 1)
1028: j = j + 1;
1029: }
1030: /*
1031: * else if (alterMe.charAt(j) == '"') { alterMe.setCharAt(j, '\\');
1032: * alterMe.insert(j + 1, "\""); length = length + 1; if(j <length-1)
1033: * j=j+1; } else if (alterMe.charAt(j) == '>') { alterMe.setCharAt(j,
1034: * '\\'); alterMe.insert(j + 1, ">"); length = length + 1; if(j <length-1)
1035: * j=j+1; } else if (alterMe.charAt(j) == ' <') { alterMe.setCharAt(j,
1036: * '\\'); alterMe.insert(j + 1, " <"); length = length + 1; if(j
1037: * <length-1) j=j+1; } else if (alterMe.charAt(j) == '?') {
1038: * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "?"); length = length +
1039: * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == '&') {
1040: * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "&"); length = length +
1041: * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == '=') {
1042: * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "="); length = length +
1043: * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == ',') {
1044: * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, ","); length = length +
1045: * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == '.') {
1046: * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "."); length = length +
1047: * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == '[') {
1048: * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "."); length = length +
1049: * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == ']') {
1050: * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "."); length = length +
1051: * 1; if(j <length-1) j=j+1; } else if (alterMe.charAt(j) == '%') {
1052: * alterMe.setCharAt(j, '\\'); alterMe.insert(j + 1, "%"); length = length +
1053: * 1; if(j <length-1) j=j+1; }
1054: */
1055: ++j;
1056: }
1057: return alterMe.toString();
1058: }
1059:
1060: /**
1061: * parses the input xml file for batch conversion called from readXMLFile *
1062: *
1063: * @param sb
1064: */
1065: public static void parseXMLConfig(StringBuffer sb)
1066: {
1067: // boolean finished = false;
1068: Vector databases = new Vector();
1069: try
1070: {
1071: databases = getXMLConfig(sb);
1.7 rogo 1072:
1.6 rogo 1073: // destination DataBase object
1.7 rogo 1074: DataBase dbDest = ((DataBase) databases.lastElement());
1.6 rogo 1075:
1.4 rogo 1076: DBBean database = ((DataBase) databases.lastElement()).bean;
1077: databases.remove(databases.size() - 1);
1.7 rogo 1078:
1.6 rogo 1079: for (Iterator iterator = dbDest.preCommands.iterator(); iterator.hasNext();)
1080: {
1081: SQLCommand sqlCommand = (SQLCommand) iterator.next();
1082: sqlCommand.executeCommand();
1083: }
1084:
1.4 rogo 1085: // databases.add(database);
1086: for (Iterator iter = databases.iterator(); iter.hasNext();)
1087: {
1088: DataBase db = (DataBase) iter.next();
1089: for (Iterator iterator = db.preCommands.iterator(); iterator.hasNext();)
1090: {
1091: SQLCommand sqlCommand = (SQLCommand) iterator.next();
1092: sqlCommand.executeCommand();
1093: }
1094: int mode = db.mode;
1.10 rogo 1095: if (mode == DataBase.CONVERT_TEMP_MODE)
1096: {
1097: convertBatch(db.bean, database, db.tables, db.layouts, db.selects, db.creates, db.ids,
1098: mode, db.delimiter);
1099: if (noError)
1100: {
1101: System.out.println("no Error occured ");
1102: // db.bean.setURL(database.url);
1103: // db.bean.setUserAndPasswd(database.user,database.passwd);
1104: //
1105: // Convert.user = db.bean.user;
1106: // Convert.passwd = db.bean.passwd;
1107: // userDest = database.user;
1108: // passwdDest = database.passwd;
1109: // synchronize(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates,
1110: // db.ids, mode, db.delimiter, new Vector(db.htIndex.values()));
1111: }
1112: }
1113: if (mode == DataBase.CONVERT_MODE || mode == DataBase.APPEND_MODE)
1.4 rogo 1114: convertBatch(db.bean, database, db.tables, db.layouts, db.selects, db.creates, db.ids,
1115: mode, db.delimiter);
1116: else if (mode == DataBase.UPDATE_MODE)
1117: {
1118:
1119: Convert.user = db.bean.user;
1120: Convert.passwd = db.bean.passwd;
1121: userDest = database.user;
1122: passwdDest = database.passwd;
1123:
1124: update(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates, db.ids,
1125: mode);
1126: } else if (mode == DataBase.SYNCHRONIZE_MODE)
1127: {
1128: Convert.user = db.bean.user;
1129: Convert.passwd = db.bean.passwd;
1130: userDest = database.user;
1131: passwdDest = database.passwd;
1132:
1133: synchronize(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates,
1.14 rogo 1134: db.ids, mode, db.delimiter, new Vector(db.htIndex.values()), db.destIDs);
1.4 rogo 1135: }
1136: for (Iterator iterator = db.postCommands.iterator(); iterator.hasNext();)
1137: {
1138: SQLCommand sqlCommand = (SQLCommand) iterator.next();
1139: sqlCommand.executeCommand();
1140: }
1141:
1.6 rogo 1142: }
1143: for (Iterator iterator = dbDest.postCommands.iterator(); iterator.hasNext();)
1144: {
1145: SQLCommand sqlCommand = (SQLCommand) iterator.next();
1146: sqlCommand.executeCommand();
1.4 rogo 1147: }
1148:
1149: } catch (Exception e)
1150: {
1151:
1152: e.printStackTrace();
1153:
1154: } finally
1155: {
1156: bean.closeAllConnections();
1157: beanDest.closeAllConnections();
1158: }
1.11 rogo 1159: }
1160:
1161: /**
1162: *
1163: * @param database
1164: * @param db
1165: * @throws Exception
1166: * @throws SQLException
1167: */
1168: private static void renameTable(DBBean database, String sourceTable, String destTable)
1169: throws SQLException, Exception
1170: {
1171: String command = "alter table " + sourceTable + " rename " + " to " + destTable;
1172: SQLCommand sqlCommand = new SQLCommand(database, command);
1173: sqlCommand.executeCommand();
1.4 rogo 1174: }
1175:
1176: public static Vector getXMLConfig(StringBuffer sb)
1177: {
1178:
1179: boolean finished = false;
1180: // parse string and build document tree
1181: Xparse parser = new Xparse();
1182: parser.changeEntities = true;
1183: Node root = parser.parse(sb.toString());
1184: // printContents(root);
1185: Vector databases = new Vector();
1186: Vector tables = new Vector();
1187: Vector layouts = new Vector();
1188: Vector selects = new Vector();
1189: Vector creates = new Vector();
1190: Vector ids = new Vector();
1.14 rogo 1191: Vector destIDs = new Vector();
1192:
1.4 rogo 1193: Vector preSQLCommands = new Vector();
1194: Vector postSQLCommands = new Vector();
1195:
1196: String delimiter = "|";
1197: int mode = -1;
1198: try
1199: {
1200: Node tempNode = root.find("convert/source", new int[]
1201: { 1, 1 });
1202: if (tempNode == null)
1203: throw new Error("parse error source tag missing");
1204: System.out.println(tempNode.name);
1205: int length = countNodes(tempNode);
1206: for (int i = 1; i <= length; i++)
1207: {
1208:
1209: DBBean database = new DBBean();
1210: tables = new Vector();
1211: layouts = new Vector();
1212: selects = new Vector();
1213: creates = new Vector();
1214: ids = new Vector();
1.14 rogo 1215: destIDs = new Vector();
1216:
1.4 rogo 1217: preSQLCommands = new Vector();
1218: postSQLCommands = new Vector();
1219:
1220: // parse dataBase
1221: Node node = root.find("convert/source/database/url", new int[]
1222: { 1, 1, i, 1 });
1223: Node node1 = root.find("convert/source/database/user", new int[]
1224: { 1, 1, i, 1, 1 });
1225: Node node2 = root.find("convert/source/database/password", new int[]
1226: { 1, 1, i, 1, 1 });
1227: Node node3 = root.find("convert/source/database", new int[]
1228: { 1, 1, i });
1229: Node nodeMode = root.find("convert/source/database/mode", new int[]
1230: { 1, 1, i, 1, 1 });
1231: Node delimiterNode = root.find("convert/source/database/delimiter", new int[]
1232: { 1, 1, i, 1, 1 });
1233:
1234: Node commandNodes = root.find("convert/source/database/sqlcommands", new int[]
1235: { 1, 1, i, 1, 1 });
1236: if (commandNodes != null)
1237: {
1238: parseCommandNode(commandNodes, database, preSQLCommands, postSQLCommands);
1239: }
1240: Node useNormanToUnicodeMapper = root.find(
1241: "convert/source/database/usenormantounicodemapper", new int[]
1242: { 1, 1, i, 1, 1 });
1243:
1244: if (delimiterNode != null)
1245: delimiter = delimiterNode.getCharacters();
1246: if (useNormanToUnicodeMapper != null)
1247: {
1248: database.setUseNormanToUnicodeMapper(Boolean.valueOf(
1249: useNormanToUnicodeMapper.getCharacters()).booleanValue());
1250: System.out.println("useMapper "
1251: + Boolean.valueOf(useNormanToUnicodeMapper.getCharacters().trim()).booleanValue());
1252: }
1253:
1254: if (node3 == null)
1255: throw new Error("parse error database tag missing");
1256: if (node == null)
1257: throw new Error("parse error url tag missing");
1258: if (node1 == null)
1259: throw new Error("parse error user tag missing");
1260: if (node2 == null)
1261: throw new Error("parse error password tag missing");
1262: String url = node.getCharacters();
1263: String user = node1.getCharacters();
1264: String password = node2.getCharacters();
1265: database.setURL(url.trim());
1266: database.setUserAndPasswd(user.trim(), password.trim());
1267: System.out.println(node.name + " " + node.getCharacters());
1268: System.out.println(node1.name + " " + node1.getCharacters());
1269: System.out.println(node2.name + " " + node2.getCharacters());
1270: String modeString = "";
1271: if (nodeMode == null)
1272: modeString = "convert";
1273: else
1274: modeString = nodeMode.getCharacters();
1275: if (modeString.equals("convert"))
1276: mode = DataBase.CONVERT_MODE;
1277: else if (modeString.equals("append"))
1278: mode = DataBase.APPEND_MODE;
1279: else if (modeString.equals("update"))
1280: mode = DataBase.UPDATE_MODE;
1281: else if (modeString.equals("delete"))
1282: mode = DataBase.DELETE_MODE;
1283:
1284: else if (modeString.equals("synchronize"))
1285: mode = DataBase.SYNCHRONIZE_MODE;
1.7 rogo 1286: else if (modeString.equals("convert_temp"))
1287: mode = DataBase.CONVERT_TEMP_MODE;
1288:
1.15 ! rogo 1289: System.out.println("mode ist "+mode);
1.4 rogo 1290: // if(node3!=null)
1291: // System.out.println(node3.name);
1292:
1293: int length2 = countNodes(node3);
1294:
1295: System.out.println("number of tables " + length2);
1296:
1297: for (int j = 1; j <= length2; ++j)
1298: {
1299: Node node4 = root.find("convert/source/database/table", new int[]
1300: { 1, 1, i, j });
1301: Node node5 = root.find("convert/source/database/table/select", new int[]
1302: { 1, 1, i, j, 1 });
1303: Node node6 = root.find("convert/source/database/table/create", new int[]
1304: { 1, 1, i, j, 1 });
1305:
1306: if (node4 != null)
1307: System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
1308: if (node5 != null)
1309: System.out.println(node5.name + " " + node5.getCharacters());
1310: if (node6 != null)
1311: System.out.println(node6.name + " " + node6.getCharacters());
1312: if (node4 == null)
1313: throw new Error("parse error table tag missing");
1314: // if(node5==null) throw new Error("parse error select tag
1315: // missing");
1316: // if(node6==null) throw new Error("parse error create tag
1317: // missing");
1318: String name = (String) node4.attributes.get("name");
1319: String layout = (String) node4.attributes.get("layout");
1320: String id = (String) node4.attributes.get("id");
1.14 rogo 1321: String destID = (String) node4.attributes.get("targetID");
1.4 rogo 1322: System.out.println("id was " + id);
1.14 rogo 1323: System.out.println("targetID was " + destID);
1.4 rogo 1324:
1325: if (name == null)
1326: throw new Error("parse error required table tag attribute name missing");
1327: if (layout == null)
1328: layout = "";
1329: if (id == null)
1330: id = "";
1.14 rogo 1331: if (destID == null)
1332: destID = id ;
1333:
1.4 rogo 1334: if (name.equals(""))
1335: throw new Error("parse error table tag attribute must not be empty");
1.10 rogo 1336: tables.add(name.intern());
1337: layouts.add(layout.intern());
1338: ids.add(id.intern());
1.14 rogo 1339: destIDs.add(destID.intern());
1.4 rogo 1340: String query = (node5 == null) ? "" : node5.getCharacters();
1341: if (query.equals(""))
1342: System.err.println("Warning empty select tag or select tag missing !!");
1343: query = (query.equals("")) ? "select * from " + database.getQC() + name
1344: + database.getQC() : query;
1345: selects.add(query);
1346: if (node6 != null)
1347: creates.add(node6.getCharacters().trim());
1348: else
1349: creates.add("");
1350:
1351: }
1352: DataBase dataBase = new DataBase(database, tables, layouts, selects, creates, ids, mode);
1353: dataBase.delimiter = delimiter;
1354: dataBase.preCommands = new Vector(preSQLCommands);
1355: dataBase.postCommands = new Vector(postSQLCommands);
1.14 rogo 1356: dataBase.destIDs = destIDs;
1.4 rogo 1357: databases.add(dataBase);
1358: }
1359: DBBean database = new DBBean();
1360:
1361: preSQLCommands.clear();
1362: postSQLCommands.clear();
1363:
1364: // parse dataBase
1365: Node node = root.find("convert/destination/database/url", new int[]
1366: { 1, 1, 1, 1 });
1367: Node node1 = root.find("convert/destination/database/user", new int[]
1368: { 1, 1, 1, 1, 1 });
1369: Node node2 = root.find("convert/destination/database/password", new int[]
1370: { 1, 1, 1, 1, 1 });
1371: Node commandNodes = root.find("convert/destination/database/sqlcommands", new int[]
1372: { 1, 1, 1, 1, 1 });
1373: if (commandNodes != null)
1374: {
1375: parseCommandNode(commandNodes, database, preSQLCommands, postSQLCommands);
1376: }
1377:
1378: String url = node.getCharacters();
1379: String user = node1.getCharacters();
1380: String password = node2.getCharacters();
1381: System.out.println(url);
1382: database.setURL(url.trim());
1383: database.setUserAndPasswd(user.trim(), password.trim());
1384: DataBase db = new DataBase(database, new Vector(), null, null, null, null, 0);
1385: databases.add(db);
1386: db.preCommands = new Vector(preSQLCommands);
1387: db.postCommands = new Vector(postSQLCommands);
1388:
1389: } catch (Exception e)
1390: {
1391: e.printStackTrace();
1392: }
1393: return databases;
1394: }
1395:
1396: /**
1397: *
1398: * @param commandNode
1399: * @param database
1400: * @param preSQLCommands
1401: * @param postSQLCommand
1402: */
1403: private static void parseCommandNode(Node commandNode, DBBean database,
1404: java.util.Vector preSQLCommands, java.util.Vector postSQLCommands)
1405: {
1406: // System.out.println(commandNode.name + " " + countNodes(commandNode));
1407: int numCommands = commandNode.contents.length();
1408: for (int j = 0; j < numCommands; ++j)
1409: {
1410: Node node = (Node) commandNode.contents.v.elementAt(j);
1411: if (node.type.equals("element"))
1412: {
1413: // System.out.println(node.name + " " + node.getCharacters() + database);
1414: String execute = node.attributes.get("execute").toString();
1415: if (execute.equals("before"))
1416: {
1417: preSQLCommands.add(new SQLCommand(database, node.getCharacters()));
1418: }
1419: if (execute.equals("after"))
1420: {
1421: postSQLCommands.add(new SQLCommand(database, node.getCharacters()));
1422: }
1423:
1424: }
1425:
1426: }
1427: }
1428:
1429: private static int countNodes(Node tempNode)
1430: {
1431: int length = 0;
1432: for (int i = 0; i < tempNode.contents.v.size(); ++i)
1433: {
1434: Node node = (Node) tempNode.contents.v.elementAt(i);
1435: if (node.type.equals("element"))
1436: {
1437: if (node.name.equals("database"))
1438: length++;
1439: if (node.name.equals("table"))
1440: length++;
1441: if (node.name.equals("sqlcommand"))
1442: length++;
1443:
1444: }
1445:
1446: // System.out.println(((Node)tempNode.contents.v.elementAt(i)).attributes+"
1447: // "+i);
1448: }
1449: return length;
1450: }
1451:
1452: private static void printContents(Node root)
1453: {
1454:
1455: Vector contents = (root.index == null) ? root.contents.v : root.index.v;
1456: for (int i = 0; i < contents.size(); ++i)
1457: {
1458: Node n = (Node) contents.elementAt(i);
1459: if (n.type.equals("element"))
1460: {
1461: System.out.println("tag " + n.name);
1462: System.out.println(n.getCharacters());
1463: // contents=n.contents.v i=0;
1464: }
1465: // System.out.println(n.type);
1466: }
1467: }
1468:
1469: /**
1470: * reads the specified xml file
1471: *
1472: * @param xmlFile
1473: */
1474: public static StringBuffer readXMLFile(String xmlFile)
1475: {
1476: InputStream stream = null;
1477: StringBuffer sb = new StringBuffer();
1478:
1479: try
1480: {
1481:
1482: if (xmlFile.indexOf("file://") >= 0 || xmlFile.indexOf("http://") >= 0)
1483: {
1484: URL url = new URL(xmlFile);
1485: stream = url.openStream();
1486: } else
1487: // read XML Metadata from a file
1488: stream = new FileInputStream(xmlFile);
1489: InputStreamReader isr = new InputStreamReader(stream, "UTF-8");
1490: BufferedReader buffr = new BufferedReader(isr);
1491: int c = 0;
1492: while ((c = buffr.read()) != -1)
1493: {
1494: char ch = (char) c;
1495: sb.append(ch);
1496: // System.out.print((char)c);
1497: }
1498: } catch (Exception e)
1499: {
1500: e.printStackTrace();
1501: } finally
1502: {
1503:
1504: try
1505: {
1506: stream.close();
1507: } catch (IOException e1)
1508: {
1509: // TODO Auto-generated catch block
1510: e1.printStackTrace();
1511: }
1512: }
1513: return sb;
1514: }
1515:
1516: /**
1517: * Helper class for Conversion etc. Contains data needed for the conversion
1518: *
1519: * @author rogo
1520: *
1521: */
1522:
1523: public static class ConversionProperties
1524: {
1525: String destTableName;
1526:
1527: String[] fieldNames;
1528:
1529: public ConversionProperties()
1530: {
1531: }
1532:
1533: public ConversionProperties(String destTableName, String[] fieldNames)
1534: {
1535: this.destTableName = destTableName;
1536: this.fieldNames = fieldNames;
1537:
1538: }
1539:
1540: }
1541:
1542: /**
1543: * Helper class for XML-File parsing Holds the parsed data
1544: *
1545: * @author rogo
1546: *
1547: */
1548: public static class DataBase
1549: {
1.14 rogo 1550:
1.4 rogo 1551: DBBean bean;
1552:
1553: Vector creates;
1554:
1555: Vector selects;
1556:
1557: Vector layouts;
1558:
1.7 rogo 1559: Vector tables = new Vector();
1.4 rogo 1560:
1561: Vector ids;
1.14 rogo 1562: Vector destIDs;
1.4 rogo 1563:
1564: String delimiter = "//";
1565:
1566: Vector preCommands;
1567: Vector postCommands;
1568:
1569: /**
1570: * maps table name to index fields
1571: */
1572: Hashtable htIndex = new Hashtable();
1573:
1574: boolean useNormanToUnicodeMapper = false;
1575:
1576: final static int CONVERT_MODE = 1;
1577:
1578: final static int APPEND_MODE = 2;
1579:
1580: final static int UPDATE_MODE = 3;
1581:
1582: final static int DELETE_MODE = 4;
1583:
1584: final static int SYNCHRONIZE_MODE = 5;
1585:
1.7 rogo 1586: final static int CONVERT_TEMP_MODE = 6;
1587:
1.4 rogo 1588: int mode = -1;
1589:
1590: public DataBase(DBBean bean, Vector tables, Vector layouts, Vector selects, Vector creates,
1591: Vector ids, int mode)
1592: {
1593: this.bean = bean;
1594: this.tables = tables;
1595: this.layouts = layouts;
1596: this.selects = selects;
1597: this.creates = creates;
1598: this.ids = ids;
1599: this.mode = mode;
1600: this.bean.setIDVector(ids);
1601: }
1602:
1603: /**
1604: * @param indexListVec
1605: */
1606: public void buildIndexTable(Vector indexListVec)
1607: {
1608: for (int i = 0; i < tables.size(); i++)
1609: {
1610: fillIndexList((String) tables.get(i), (String) indexListVec.get(i));
1611: }
1612: }
1613:
1614: /**
1615: * writes the data contained in this object to the buffered writer *
1616: *
1617: * @param buffr
1618: * @throws Exception
1619: */
1620: public void exportToXML(BufferedWriter buffr) throws Exception
1621: {
1622: // ids=bean.getIDVector();
1623: buffr.write(" <database>\n");
1624: buffr.write(" <url>" + bean.url + "</url>\n");
1625: buffr.write(" <user>" + bean.user + "</user>\n");
1626: buffr.write(" <password>" + bean.passwd + "</password>\n");
1627: buffr.write(" <delimiter>" + delimiter + "</delimiter>\n");
1628: String modeString = "";
1.13 rogo 1629: if (mode == CONVERT_TEMP_MODE)
1630: modeString = "convert_temp";
1631: else if (mode == CONVERT_MODE)
1.4 rogo 1632: modeString = "convert";
1633: else if (mode == APPEND_MODE)
1634: modeString = "append";
1635: else if (mode == UPDATE_MODE)
1636: modeString = "update";
1637: else if (mode == DELETE_MODE)
1638: modeString = "delete";
1639: else if (mode == SYNCHRONIZE_MODE)
1640: modeString = "synchronize";
1641:
1642: buffr.write(" <mode>" + modeString + "</mode>\n");
1643: buffr.write(" <usenormantounicodemapper>" + useNormanToUnicodeMapper
1644: + "</usenormantounicodemapper>\n");
1645: if (preCommands != null || postCommands != null)
1646: {
1647: int count = 0;
1648:
1649: buffr.write(" <sqlcommands> \n");
1650:
1651: if (preCommands != null)
1652: {
1653: while (count < preCommands.size())
1654: {
1.7 rogo 1655: SQLCommand sqlcommand = (SQLCommand) preCommands.get(count);
1656: buffr.write(" <sqlcommand execute=\"before\">" + sqlcommand.command
1657: + "</sqlcommand>\n");
1.4 rogo 1658: count++;
1659: }
1660: }
1661: if (postCommands != null)
1662: {
1663: count = 0;
1664: while (count < postCommands.size())
1665: {
1.7 rogo 1666: SQLCommand sqlcommand = (SQLCommand) postCommands.get(count);
1667:
1668: buffr.write(" <sqlcommand execute=\"after\">" + sqlcommand.command
1669: + "</sqlcommand>\n");
1.4 rogo 1670: count++;
1671: }
1672: }
1673: buffr.write(" </sqlcommands> \n");
1674:
1675: }
1676: int index = 0;
1677: while (index < tables.size())
1678: {
1679: String table = (String) tables.get(index);
1680: String layout = (String) layouts.get(index);
1681: String select = (String) selects.get(index);
1682: String create = (String) creates.get(index);
1683: String id = (String) ids.get(index);
1684: IndexList indexList = (IndexList) htIndex.get(table);
1685: if (indexList == null)
1686: indexList = new IndexList();
1687: buffr.write(" <table name = \"" + table + "\" layout = \"" + layout + "\" id = \""
1688: + id + "\" indexList =\"" + indexList + "\">\n");
1689: buffr.write(" <select>" + convertToEntities(select) + "</select>\n");
1690: if (!create.equals(""))
1691: buffr.write(" <create>" + create + " </create>\n");
1692: buffr.write(" </table>\n");
1693: index++;
1694: }
1695: buffr.write(" </database>\n");
1696: }
1697:
1698: public void fillIndexList(String table, String list)
1699: {
1700: IndexList indexList = new IndexList();
1701: StringTokenizer tokenizer = new StringTokenizer(list, ",");
1702: while (tokenizer.hasMoreTokens())
1703: {
1704: indexList.add(tokenizer.nextToken());
1705: }
1706: System.out.println(indexList);
1707:
1708: htIndex.put(table, indexList);
1709: }
1710:
1711: public String toString()
1712: {
1713: return bean.url + " " + tables;
1714: }
1715:
1716: }
1717:
1718: public static void writeConfig(String file, DataBase source, DataBase destination)
1719: throws Exception
1720: {
1721: if (!file.toLowerCase().endsWith(".xml"))
1722: file += ".xml";
1723: File f = new File(file);
1724:
1725: FileOutputStream fout = new FileOutputStream(f);
1726: OutputStreamWriter outsw = new OutputStreamWriter(fout, "UTF-8");
1727: BufferedWriter buffw = new BufferedWriter(outsw);
1728: buffw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
1729: buffw.newLine();
1730: buffw.write("<convert>\n");
1731: buffw.write(" <source>\n");
1732: source.exportToXML(buffw);
1733: buffw.write(" </source>\n");
1734: buffw.write("\n <destination>\n");
1735: destination.exportToXML(buffw);
1736: buffw.write(" </destination>\n");
1737: buffw.write("</convert>\n");
1738: buffw.close();
1739: }
1740:
1741: public static void delete(String source, String destination, Vector names, Vector layouts,
1742: Vector selects, Vector creates, Vector ids, int mode) throws Exception
1743: {
1744: FM2SQL.ProgressDialog dialog = null;
1745: if (isGUI)
1746: {
1747: dialog = initDialog();
1748: }
1749: // setting user and passwd
1750: bean.setUserAndPasswd(user, passwd);
1751: // setting user and passwd
1752: beanDest.setUserAndPasswd(userDest, passwdDest);
1753: StringBuffer command = null;
1754: String query = null;
1755: try
1756: {
1757: // bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
1758: // bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
1759: bean.setConnection(source);
1760: if (names == null)
1761: names = bean.getTableNames();
1762: // Collections.sort(names);
1763: int tbIndex = 1;
1764:
1765: // System.out.println("Start at
1766: // "+names.indexOf("archimedes_facsimiles"));
1767: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
1768: {
1769: Vector[] result = null;
1770: java.util.TreeSet myIds = new TreeSet();
1771: java.util.TreeSet myIdsDest = new TreeSet();
1772: int deltaID = 1;
1773: String idField = "";
1774: String destTableName = "";
1775:
1776: try
1777: {
1778: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
1779: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
1780: query = (selects != null) ? selects.get(tbIndex).toString() : query;
1781: // if vectors[1].get(i) != null)
1782: if (layout != "")
1783: {
1784: layout = " layout " + bean.getQC() + layout + bean.getQC();
1785: String name = names.get(tbIndex).toString();
1786: StringBuffer queryLayout = new StringBuffer(query);
1787: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
1788: query = queryLayout.toString();
1789: System.out.println("added layout " + query);
1790:
1791: }
1792: dialog.title.setText("Getting table data ...");
1793: dialog.table.setText(names.get(tbIndex).toString());
1794: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
1795: dialog.setVisible(true);
1796: bean.getConnection();
1797: bean.makeQuery(query, 50);
1798: idField = ids.get(tbIndex).toString();
1799:
1800: } catch (Exception e)
1801: {
1802: continue;
1803: }
1804: // determine destTableName from createStatement or from source
1805: // table name
1806: if (!creates.get(tbIndex).equals(""))
1807: {
1808: String create = creates.get(tbIndex).toString().toLowerCase();
1809: int fromIndex = create.indexOf("table") + 5;
1810: int toIndex = create.indexOf("(");
1811: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "")
1812: .trim();
1813: System.out.println("destTable " + destTableName);
1814:
1815: } else
1816: destTableName = convertText(names.get(tbIndex).toString());
1817:
1818: // for id kram
1819: Vector vec = null;
1820: Vector vecDest = null;
1821: // tempo
1822: beanDest.setConnection(destination);
1823: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
1824: String tempID = bean.getQC() + idField + bean.getQC();
1825: String tempIDdest = beanDest.getQC() + convertText(idField) + beanDest.getQC();
1826:
1827: int endIndex = -1;
1828: String tempQuery = query;
1829: String destQuery = query.replaceAll(names.get(tbIndex).toString(), destTableName);
1830: String tempQueryDest = destQuery;
1831: // remove extra query parts
1832: // destQuery.substring(0,destQuery.lastIndexOf(destTableName)+destTableName.length()+1);
1833: System.out.println("new Query " + tempQueryDest);
1834: if (!idField.equals(""))
1835: {
1836: long startTime = System.currentTimeMillis();
1837: int counter = -1;
1838: while (true)
1839: {
1840: ++counter;
1841: if (counter == 0 && dialog != null)
1842: dialog.title.setText("Check if data is available");
1843: else if (dialog != null)
1844: dialog.title.setText("Check if more data is available");
1845: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex),
1846: tempQuery, numHits);
1847: myIdsDest = beanDest.getIDVector(convertText(idField), destTableName, tempQueryDest,
1848: numHits);
1849: if (myIds.isEmpty())
1850: break;
1851: vec = new Vector(myIds);
1852: vecDest = new Vector(myIdsDest);
1853: rowCount = vec.size();
1854: // Deletion will work this way
1855: Vector deleted = new Vector(vec);
1856: Vector linesToDelete = new Vector(vecDest);
1857: // remove all lines that should not be deleted
1858: linesToDelete.removeAll(deleted);
1859: // System.out.println("ID LIST SIZE " +
1860: // Math.round((double) myIds.size() / (double)
1861: // numIntervalls) + " " + myIdsDest.size());
1862: // / @TODO complete delete task remove query show lines
1863: // to be deleted let user choose if he wants that
1864: System.out.println("number of lines to be deleted " + linesToDelete.size());
1865: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
1866: beanDest.setConnection(destination);
1867:
1868: Statement stm = beanDest.getConnection().createStatement();
1869:
1870: Vector tables = beanDest.getTableNames();
1871: // Collections.sort(tables);
1872: System.out.println(names.get(tbIndex) + " "
1873: + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
1874: tables = beanDest.getTableNames();
1875: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
1876: stm = beanDest.getConnection().createStatement();
1877:
1878: if (dialog != null)
1879: dialog.title.setText(" Deleting table data ...");
1880:
1881: int j = -1;
1882:
1883: Vector row = null;
1884: command = new StringBuffer();
1885:
1886: command.append("DELETE FROM");
1887: command.append(beanDest.getQC());
1888: command.append(destTableName);
1889: command.append(beanDest.getQC());
1890: int size = bean.getColumnNames().size();
1891: command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
1892: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
1893: System.out.println(command + " " + tbIndex);
1894: while (true)
1895: {
1896:
1897: ++j;
1898: if (j == linesToDelete.size())
1899: break;
1900: // print rows
1901: pstm.setString(1, linesToDelete.get(j).toString());
1902: System.out.println(pstm.toString());
1903: pstm.execute();
1904: if (isGUI)
1905: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
1906: command = null;
1907: }
1908: // prepare new query for next chunk
1.14 rogo 1909: if (query.toLowerCase().indexOf("where") > 0)
1.4 rogo 1910: tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
1911: else
1912: tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
1913:
1914: } // to outer while
1915: } // to idfield if
1916: } // table loop
1917:
1918: } catch (Exception e)
1919: {
1920: System.out.println("Error while connecting to database " + e);
1921: if (isGUI)
1922: {
1923: showExceptionDialog(dialog, command, e);
1924: resetGUI(dialog);
1925: } else
1926: {
1927: e.printStackTrace();
1928: }
1929: }
1930: } // to method
1931:
1932: /**
1933: * synchronize method based on delete method code
1934: * @param source
1935: * @param destination
1936: * @param names
1937: * @param layouts
1938: * @param selects
1939: * @param creates
1940: * @param ids
1941: * @param mode
1.14 rogo 1942: * @param destIDs TODO
1943: *
1.4 rogo 1944: * @throws Exception
1945: */
1946: // TODO implement append,update and delete in one method
1947: // TODO using id based algorithm
1948: public static void synchronize(String source, String destination, Vector names, Vector layouts,
1.14 rogo 1949: Vector selects, Vector creates, Vector ids, int mode, String delimiter, Vector indexList, java.util.Vector destIDs)
1.4 rogo 1950: throws Exception
1951: {
1952: System.out.println(" bin in synchronize!!!");
1953: FM2SQL.ProgressDialog dialog = null;
1954: if (isGUI)
1955: {
1956: dialog = initDialog();
1957: dialog.setTitle("Synchronize running ...");
1958:
1959: }
1960: // setting user and passwd
1961: bean.setUserAndPasswd(user, passwd);
1962: // setting user and passwd
1963: beanDest.setUserAndPasswd(userDest, passwdDest);
1964: StringBuffer command = null;
1965: String query = null;
1966: try
1967: {
1968: bean.setConnection(source);
1969: if (names == null)
1970: names = bean.getTableNames();
1971: int tbIndex = 1;
1972:
1973: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
1974: {
1975: Vector[] result = null;
1976: java.util.TreeSet myIds = new TreeSet();
1977: java.util.TreeSet myIdsDest = new TreeSet();
1978: int deltaID = 1;
1979: String idField = "";
1.14 rogo 1980: String destIDField = "";
1.4 rogo 1981: String destTableName = "";
1982:
1983: try
1984: {
1985: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
1986: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
1987: query = (selects != null) ? selects.get(tbIndex).toString() : query;
1988: // if vectors[1].get(i) != null)
1989: if (!layout.equals(""))
1990: {
1991: query = addLayoutToQuery(names, query, tbIndex, layout);
1992:
1993: }
1994: if (dialog != null)
1995: {
1996: prepareDialogforUse(names, dialog, tbIndex);
1997: }
1998: bean.getConnection();
1999: bean.makeQuery(query, 50);
2000: idField = ids.get(tbIndex).toString();
1.14 rogo 2001: destIDField = (destIDs!=null) ? destIDs.get(tbIndex).toString():convertText(idField);
1.4 rogo 2002: } catch (Exception e)
2003: {
2004: System.out.println("Warning exception occured \n " + e);
2005:
2006: continue;
2007: }
2008: // determine destTableName from createStatement or from source
2009: // table name
2010: if (!creates.get(tbIndex).equals(""))
2011: {
2012: String create = creates.get(tbIndex).toString().toLowerCase();
2013: int fromIndex = create.indexOf("table") + 5;
2014: int toIndex = create.indexOf("(");
2015: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "")
2016: .trim();
2017: System.out.println("destTable " + destTableName);
2018:
2019: } else
2020: destTableName = convertText(names.get(tbIndex).toString());
2021:
2022: // for id kram
2023: Vector vec = null;
2024: Vector vecDest = null;
2025: // tempo
2026: beanDest.setConnection(destination);
2027: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
2028: String tempID = bean.getQC() + idField + bean.getQC();
2029: String tempIDdest = beanDest.getQC() + convertText(idField) + beanDest.getQC();
2030:
2031: int endIndex = -1;
2032: String tempQuery = query;
2033: String destQuery = query.replaceAll(names.get(tbIndex).toString(), destTableName);
2034: destQuery = destQuery.replaceAll(bean.getQC(), beanDest.getQC());
2035: destQuery = removeLayoutPartFromQuery(destQuery, layouts.get(tbIndex).toString());
2036: // TODO remove layout part for destQuery
2037: String tempQueryDest = destQuery;
2038: // remove extra query parts
2039: // destQuery.substring(0,destQuery.lastIndexOf(destTableName)+destTableName.length()+1);
2040: System.out.println("new Query " + tempQueryDest);
1.14 rogo 2041: System.out.println("idfield " + idField + " " + ids.get(tbIndex).toString()+" targetID is "+destIDField);
1.4 rogo 2042: if (!idField.equals(""))
2043: {
2044: long startTime = System.currentTimeMillis();
2045: int counter = -1;
2046: TreeSet linesToDelete = null;
2047: PreparedStatement delPSt = null;
2048: while (true)
2049: {
2050: ++counter;
2051: if (counter == 0 && dialog != null)
2052: dialog.title.setText("Check if data is available");
2053: else if (dialog != null)
2054: dialog.title.setText("Check if more data is available");
2055:
2056: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex),
2057: tempQuery, 0);
1.14 rogo 2058: myIdsDest = beanDest.getIDVector(destIDField, destTableName, tempQueryDest, 0);
1.4 rogo 2059: // System.out.println("status of remove
2060: // "+myIds.remove("b015892"));
2061: System.out.println("ids found for " + idField + " " + !myIds.isEmpty());
2062: if (myIds.isEmpty())
2063: break;
2064: vec = new Vector(myIds);
2065: vecDest = new Vector(myIdsDest);
2066: rowCount = vec.size();
2067: // Deletion will work this way
2068: Vector deleted = new Vector(vec);
2069:
2070: TreeSet linesToAppend = new TreeSet(vec);
1.14 rogo 2071: // linesToAppend.addAll(vec);
1.4 rogo 2072: linesToDelete = new TreeSet(vecDest);
2073: // remove all lines that are already in dest database
2074: linesToAppend.removeAll(vecDest);
2075: // remove all lines that should not be deleted
2076: linesToDelete.removeAll(deleted);
2077: System.out.println("linesToAppend " + linesToAppend.size() + " " + destTableName);
2078: System.out.println("linesToDelete " + linesToDelete.size() + " " + destTableName);
2079: System.out.println("ID LIST SIZE "
2080: + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIds.size());
2081: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
2082: ConversionProperties prop = getFieldNamesAndDestTableName(creates.get(tbIndex)
2083: .toString(), query, names.get(tbIndex).toString());
2084: StringBuffer insCommand = createInsertCommand(prop.destTableName, prop.fieldNames);
2085: StringBuffer updCommand = createUpdateCommand(prop.destTableName, prop.fieldNames,
2086: tempIDdest);
2087: StringBuffer delCommand = createDeleteCommand(destTableName, tempIDdest);
2088: PreparedStatement insPst = beanDest.getConnection().prepareStatement(
2089: insCommand.toString());
2090: PreparedStatement updPst = beanDest.getConnection().prepareStatement(
2091: updCommand.toString());
2092: delPSt = beanDest.getConnection().prepareStatement(delCommand.toString());
2093: // delPSt.setString(1,"b015892");
2094: // delPSt.execute();
2095: // if (true)
2096: // return;
2097: if (vec.size() <= numIntervalls)
2098: {
2099: endIndex = 0;
2100: deltaID = vec.size();
2101: }
2102: for (int k = 0; k < vec.size() - deltaID; k = k + deltaID)
2103: {
2104: System.out.println(vec.get(k) + " " + vec.get(k + deltaID) + " " + vec.lastElement());
1.14 rogo 2105: if (query.toLowerCase().indexOf("where") > 0)
1.4 rogo 2106: tempQuery = query + " and " + tempID + ">='" + vec.get(k) + "' and " + tempID
2107: + "<='" + vec.get(k + deltaID) + "'";
2108: else
2109: tempQuery = query + " where " + tempID + ">='" + vec.get(k) + "' and " + tempID
2110: + "<='" + vec.get(k + deltaID) + "'";
2111: System.out.println(tempQuery);
2112: if (dialog != null)
2113: dialog.title.setText("Reading table data ...");
2114:
2115: // bean.makeQuery(tempQuery, deltaID);
2116: if (dialog != null)
2117: dialog.title.setText("Writing table data ...");
2118:
2119: performSynchronize(idField, vec, tempQuery, linesToDelete, linesToAppend, insPst,
2120: updPst, delPSt, deltaID, delimiter, dialog);
2121: // System.out.println("ID LIST SIZE " +
2122: // Math.round((double) myIds.size() / (double)
2123: // numIntervalls) + " " + myIdsDest.size());
2124: endIndex = k + deltaID;
2125: }
2126: System.out.println(endIndex);
2127: // all data written ? if not write last chunk of data
2128: if (endIndex == vec.size() - 1)
2129: System.out.println("fits");
2130: else
2131: {
2132: System.out.println(" last intervall from " + vec.get(endIndex) + " "
2133: + vec.lastElement());
2134:
1.14 rogo 2135: if (query.toLowerCase().indexOf("where") > 0)
1.4 rogo 2136: tempQuery = query + " and " + tempID + ">='" + vec.get(endIndex) + "' and "
2137: + tempID + "<='" + vec.lastElement() + "'";
2138: else
2139: tempQuery = query + " where " + tempID + ">='" + vec.get(endIndex) + "' and "
2140: + tempID + "<='" + vec.lastElement() + "'";
2141: System.out.println(tempQuery);
2142: if (dialog != null)
2143: dialog.title.setText("Reading table data ...");
2144: // bean.makeQuery(tempQuery, 0);
2145: if (dialog != null)
2146: dialog.title.setText("Writing table data ...");
2147: performSynchronize(idField, vec, tempQuery, linesToDelete, linesToAppend, insPst,
2148: updPst, delPSt, deltaID, delimiter, dialog);
2149: // System.out.println("ID LIST SIZE " +
2150: // Math.round((double) myIds.size() / (double)
2151: // numIntervalls) + " " + myIdsDest.size());
2152: }
2153: // prepare new query for next chunk
1.14 rogo 2154: if (query.toLowerCase().indexOf("where") > 0)
1.4 rogo 2155: tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
2156: else
2157: tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
2158:
2159: }
2160: String tableName = names.get(tbIndex).toString();
2161: if (!indexList.isEmpty())
2162: {
2163: IndexList idList = (IndexList) indexList.get(0);
2164: System.out.println("found list " + idList);
2165: Statement stm = beanDest.getConnection().createStatement();
2166: Vector destTables = beanDest.getTableNames();
2167: System.out.println("tempQueryDest" + tempQueryDest);
2168: beanDest.makeQuery(tempQueryDest, 0);
2169: for (Iterator iter = idList.iterator(); iter.hasNext();)
2170: {
2171: String indexField = (String) iter.next();
2172: indexField = convertText(indexField);
2173: String indexName = destTableName + "_" + indexField;
2174: if (destTables.contains(indexName))
2175: {
2176: stm.execute("DROP INDEX " + destTableName + "_" + indexField);
2177: // continue;
2178: }
2179: // stm.execute("DROP INDEX
2180: // "+destTableName+"_"+indexField);
2181:
2182: String type = beanDest.getColumnType(indexField).toLowerCase();
2183: // System.out.println(indexField+" "+type+"
2184: // "+(type.indexOf("text") >= 0 ||
2185: // type.indexOf("varchar") >= 0 || type.indexOf("char")
2186: // >= 0));
2187: if (type.indexOf("text") >= 0 || type.indexOf("varchar") >= 0
2188: || type.indexOf("char") >= 0)
2189: {
2190: if (beanDest.url.indexOf("mysql") >= 0)
2191: {
2192: // System.out.println("CREATE INDEX " +
2193: // indexName + " ON " + destTableName + " (" +
2194: // indexField + "(10))");
2195: // TODO problem if index exist !!!
2196: stm.execute("CREATE INDEX " + indexName + " ON " + destTableName + " ("
2197: + indexField + "(10))");
2198: } else
2199: {
2200: stm.execute("CREATE INDEX " + indexName + " ON " + destTableName + " (lower( "
2201: + indexField + "))");
2202:
2203: }
2204:
2205: } else
2206: {
2207: stm.execute("CREATE INDEX " + destTableName + "_" + indexField + " ON "
2208: + destTableName + "(" + indexField + ")");
2209:
2210: }
2211:
2212: // stm.execute("DROP INDEX
2213: // "+destTableName+"_"+indexField);
2214:
2215: }
2216: }
2217: // CREATE UNIQUE INDEX title_idx ON films (title);
2218: for (Iterator iter = linesToDelete.iterator(); iter.hasNext();)
2219: {
1.15 ! rogo 2220: String id = iter.next().toString();
1.4 rogo 2221: delPSt.setString(1, id);
2222: delPSt.execute();
2223:
2224: }
2225:
2226: long endTime = System.currentTimeMillis();
2227: System.out.println("Time for incremental synchronize elapsed " + (endTime - startTime));
2228: } // to idfield if
2229: } // table loop
2230:
2231: } catch (Exception e)
2232: {
2233: System.out.println("Error while connecting to database " + e);
2234: e.printStackTrace();
2235: if (isGUI)
2236: showExceptionDialog(dialog, command, e);
2237: }
2238: if (isGUI)
2239: {
2240: resetGUI(dialog);
2241: }
2242: }
2243:
2244: /**
2245: * @param destQuery
2246: * @param string
2247: * @return
2248: */
2249: private static String removeLayoutPartFromQuery(String destQuery, String layoutName)
2250: {
2251: String removeString = "layout " + beanDest.getQC() + layoutName + beanDest.getQC();
2252: destQuery = destQuery.replaceFirst(removeString, "");
2253: System.out.println("destQuery change to " + destQuery);
2254: return destQuery;
2255: }
2256:
2257: private static void performSynchronize(String idField, Vector vec, String tempQuery,
2258: TreeSet linesToDelete, TreeSet linesToAppend, PreparedStatement insPst,
2259: PreparedStatement updPst, PreparedStatement delPSt, int deltaID, String delimiter,
2260: FM2SQL.ProgressDialog dialog) throws SQLException, ParseException, Exception
2261: {
2262: if (dialog != null)
2263: {
2264: dialog.progress.setValue(0);
2265: dialog.title.setText("Retrieving new data");
2266: }
2267:
1.15 ! rogo 2268: Vector[] vectors = bean.getQueryData(tempQuery, 0);
1.4 rogo 2269: int count = 0, size = vectors[0].size();
2270: int idIndex = vectors[1].indexOf(idField);
2271: // System.out.println(idIndex + " " + vectors[1] + " " + idField);
2272: // todo arraylist code has to be added
2273: if (dialog != null)
2274: dialog.title.setText("Synchronize with new data");
2275:
2276: for (Iterator iter = vectors[0].iterator(); iter.hasNext();)
2277: {
2278: Vector line = (Vector) iter.next();
2279: Object lineIDIndex = line.get(idIndex);
2280: if (linesToAppend.contains(lineIDIndex))
1.14 rogo 2281: System.out.println("line " + linesToAppend.contains(lineIDIndex) + " " + lineIDIndex);
1.4 rogo 2282: if (linesToAppend.contains(lineIDIndex))
2283: {
2284: for (int l = 0; l < line.size(); ++l)
2285: {
2286: Object obj = line.get(l);
2287: if (obj instanceof ArrayList)
2288: obj = formatFileMakerArray((List) obj, delimiter);
2289: if (obj != null)
2290: insPst.setString(l + 1, obj.toString());
2291: else
2292: insPst.setNull(l + 1, Types.NULL);
2293: }
2294: insPst.execute();
2295:
2296: } else
2297: // update
2298: {
2299: for (int l = 0; l < line.size(); ++l)
2300: {
2301: Object obj = line.get(l);
2302: if (obj instanceof ArrayList)
2303: obj = formatFileMakerArray((List) obj, delimiter);
2304: if (obj != null)
2305: updPst.setString(l + 1, obj.toString());
2306: else
2307: updPst.setNull(l + 1, Types.NULL);
2308: }
2309: updPst.setString(line.size() + 1, line.get(idIndex).toString());
2310: // updPst.addBatch();
1.15 ! rogo 2311: // System.out.println("updating "+line.get(idIndex).toString()+" "+line);
! 2312: updPst.execute();
1.4 rogo 2313: }
2314: if (dialog != null)
2315: {
2316: int value = (int) Math.round(((double) count / (double) size) * 100.0);
2317: dialog.progress.setValue(value);
2318: count++;
2319: }
2320: }
2321: // updPst.executeBatch();
2322: } // to method
2323:
2324: /**
2325: * Converts input String in norman encoding to unicode
2326: *
2327: * @param inp
2328: * @return converted String
2329: */
2330: static public String normanToUnicode(String inp)
2331: {
2332: StringBuffer buf = new StringBuffer();
2333: for (int i = 0; i < inp.length(); i++)
2334: {
2335: char c = inp.charAt(i);
2336: // System.out.println("char "+c+" "+(int)c);
2337: switch (c)
2338: {
2339: case 1:
2340: buf.append("\u00d0");
2341: break; // Eth
2342: case 2:
2343: buf.append("\u00f0");
2344: break; // eth
2345: case 3:
2346: buf.append("\u0141");
2347: break; // Lslash
2348: case 4:
2349: buf.append("\u0142");
2350: break; // lslash
2351: case 5:
2352: buf.append("\u0160");
2353: break; // S caron
2354: case 6:
2355: buf.append("\u0161");
2356: break; // s caron
2357: case 7:
2358: buf.append("\u00dd");
2359: break; // Y acute
2360: case 8:
2361: buf.append("\u00fd");
2362: break; // y acute
2363: case 11:
2364: buf.append("\u00de");
2365: break; // Thorn
2366: case 12:
2367: buf.append("\u00fe");
2368: break; // thorn
2369: case 14:
2370: buf.append("\u017d");
2371: break; // Z caron
2372: case 15:
2373: buf.append("\u017e");
2374: break; // z caron
2375: case 17:
2376: buf.append("\u0073");
2377: break; // asciitilde
2378: case 18:
2379: buf.append("j\u0305");
2380: break; // j macron [does a single char exist?]
2381: case 19:
2382: buf.append("^");
2383: break; // circumflex
2384: case 20:
2385: buf.append("\u0303");
2386: break; // tilde
2387: case 21:
2388: buf.append("\u00bd");
2389: break; // onehalf
2390: case 22:
2391: buf.append("\u00bc");
2392: break; // onequarter
2393: case 23:
2394: buf.append("\u00b9");
2395: break; // onesuperior
2396: case 24:
2397: buf.append("\u00be");
2398: break; // threequarters
2399: case 25:
2400: buf.append("\u00b3");
2401: break; // threesuperior
2402: case 26:
2403: buf.append("\u00b2");
2404: break; // twosuperior
2405: case 27:
2406: buf.append("\u00a6");
2407: break; // brokenbar
2408: case 28:
2409: buf.append("-");
2410: break; // minus
2411: case 29:
2412: buf.append("\u00d7");
2413: break; // multiply
2414: case 39:
2415: buf.append("'");
2416: break; // quotesingle
2417: case 94:
2418: buf.append("\u0302");
2419: break; // circumflex
2420: case 96:
2421: buf.append("\u0300");
2422: break; // grave
2423: case 196:
2424: buf.append("\u00c4");
2425: break; // A dieresis
2426: case 197:
2427: buf.append("\u00c5");
2428: break; // A ring
2429: case 201:
2430: buf.append("\u00c9");
2431: break; // E acute
2432: case 209:
2433: buf.append("\u00d1");
2434: break; // N tilde
2435: case 214:
2436: buf.append("\u00d6");
2437: break; // O dieresis
2438: case 220:
2439: buf.append("\u00dc");
2440: break; // U dieresis
2441: case 225:
2442: buf.append("\u00e1");
2443: break; // a acute
2444: case 224:
2445: buf.append("\u00e0");
2446: break; // a grave
2447: case 226:
2448: buf.append("\u00e2");
2449: break; // a circumflex
2450: case 228:
2451: buf.append("\u00e4");
2452: break; // a dieresis
2453: case 227:
2454: buf.append("\u00e3");
2455: break; // a tilde
2456: case 229:
2457: buf.append("\u0101");
2458: break; // a macron
2459: case 231:
2460: buf.append("\u00e7");
2461: break; // c cedilla
2462: case 233:
2463: buf.append("\u00e9");
2464: break; // e acute
2465: case 232:
2466: buf.append("\u00e8");
2467: break; // e grave
2468: case 234:
2469: buf.append("\u00ea");
2470: break; // e circumflex
2471: case 235:
2472: buf.append("\u00eb");
2473: break; // e dieresis
2474: case 237:
2475: buf.append("\u00ed");
2476: break; // i acute
2477: case 236:
2478: buf.append("\u00ec");
2479: break; // i grave
2480: case 238:
2481: buf.append("\u00ee");
2482: break; // i circumflex
2483: case 239:
2484: buf.append("\u00ef");
2485: break; // i dieresis
2486: case 241:
2487: buf.append("\u00f1");
2488: break; // n tilde
2489: case 243:
2490: buf.append("\u00f3");
2491: break; // o acute
2492: case 242:
2493: buf.append("\u00f2");
2494: break; // o grave
2495: case 244:
2496: buf.append("\u00f4");
2497: break; // o circumflex
2498: case 246:
2499: buf.append("\u00f6");
2500: break; // o dieresis
2501: case 245:
2502: buf.append("\u00f5");
2503: break; // o tilde
2504: case 250:
2505: buf.append("\u00fa");
2506: break; // u acute
2507: case 249:
2508: buf.append("\u00f9");
2509: break; // u grave
2510: case 251:
2511: buf.append("\u00fb");
2512: break; // u circumflex
2513: case 252:
2514: buf.append("\u00fc");
2515: break; // u dieresis
2516: case 8224:
2517: buf.append("\u1e6d");
2518: break; // t underdot
2519: case 176:
2520: buf.append("\u00b0");
2521: break; // degree
2522: case 162:
2523: buf.append("\u1ebd");
2524: break; // e tilde
2525: case 163:
2526: buf.append("\u00a3");
2527: break; // sterling
2528: case 167:
2529: buf.append("\u00a7");
2530: break; // section
2531: case 182:
2532: buf.append("\u00b6");
2533: break; // paragraph
2534: case 223:
2535: buf.append("\u015b");
2536: break; // s acute
2537: case 174:
2538: buf.append("\u1e5b");
2539: break; // r underdot
2540: case 169:
2541: buf.append("\u1e45");
2542: break; // n overdot
2543: case 353:
2544: buf.append("\u1e45");
2545: break; // n overdot
2546: case 180:
2547: buf.append("\u0301");
2548: break; // acute
2549: case 168:
2550: buf.append("\u0308");
2551: break; // dieresis
2552: case 8800:
2553: buf.append("\u1e6d");
2554: break; // t underdot
2555: case 198:
2556: buf.append("\u00c6");
2557: break; // AE
2558: case 216:
2559: buf.append("\u014d");
2560: break; // o macron
2561: case 8734:
2562: buf.append("\u0129");
2563: break; // i tilde
2564: case 177:
2565: buf.append("\u00b1");
2566: break; // plusminus
2567: case 165:
2568: buf.append("\u012b");
2569: break; // i macron
2570: case 181:
2571: buf.append("\u1e43");
2572: break; // m underdot
2573: case 8706:
2574: buf.append("\u1e0d");
2575: break; // d underdot
2576: case 240:
2577: buf.append("\u1e0d");
2578: break; // d underdot
2579:
2580: case 8721:
2581: buf.append("\u1e63");
2582: break; // s underdot
2583: case 960:
2584: buf.append("\u017a");
2585: break; // z acute
2586: case 8747:
2587: buf.append("\u1e45");
2588: break; // n overdot
2589: case 937:
2590: buf.append("\u0169");
2591: break; // u tilde
2592: case 230:
2593: buf.append("\u00e6");
2594: break; // ae
2595: case 248:
2596: buf.append("\u00f8");
2597: break; // oslash
2598: case 191:
2599: buf.append("\u0304\u0306");
2600: break; // macron breve
2601: case 172:
2602: buf.append("\u1e37");
2603: break; //
2604: case 8730:
2605: buf.append("j\u0305");
2606: break; // j macron [does a single char exist?]
2607: case 402:
2608: buf.append("\u0103");
2609: break; // a breve
2610: case 8776:
2611: buf.append("\u016d");
2612: break; // u breve
2613: case 187:
2614: buf.append("\u1e42");
2615: break; // M underdot
2616: case 8230:
2617: buf.append("\u2026");
2618: break; // ellipsis
2619: case 192:
2620: buf.append("\u00c0");
2621: break; // A grave
2622: case 195:
2623: buf.append("\u00c3");
2624: break; // A tilde
2625: case 213:
2626: buf.append("\u00d5");
2627: break; // O tilde
2628: case 338:
2629: buf.append("m\u0306");
2630: break; // m breve
2631: case 339:
2632: buf.append("\u0153");
2633: break; // oe
2634: case 8211:
2635: buf.append("\u2013");
2636: break; // endash
2637: case 8212:
2638: buf.append("\u2014");
2639: break; // emdash
2640: case 8220:
2641: buf.append("\u201c");
2642: break; // quotedblleft
2643: case 8221:
2644: buf.append("\u201d");
2645: break; // quotedblright
2646: case 8216:
2647: buf.append("\u2018");
2648: break; // quoteleft
2649: case 8217:
2650: buf.append("\u2019");
2651: break; // quoteright
2652: case 247:
2653: buf.append("\u1e37");
2654: break; // l underring [actually underdot]
2655: case 9674:
2656: buf.append("\u1e41");
2657: break; // m overdot
2658: case 255:
2659: buf.append("n\u0306");
2660: break; // n breve
2661: case 376:
2662: buf.append("\u00d7");
2663: break; // multiply
2664: case 8364:
2665: buf.append("\u1e5b");
2666: break; // r underring [actually underdot]
2667: case 8249:
2668: buf.append("\u1e44");
2669: break; // N overdot
2670: case 8250:
2671: buf.append("\u1e62");
2672: break; // S underdot
2673: case 64257:
2674: buf.append("\u1e24");
2675: break; // H underdot
2676: case 64258:
2677: buf.append("\u1e0c");
2678: break; // D underdot
2679: case 8225:
2680: buf.append("\u2021");
2681: break; // daggerdbl
2682: case 8218:
2683: buf.append("\u1e36");
2684: break; // L underdot
2685: case 8222:
2686: buf.append("\u0113");
2687: break; // e macron
2688: case 194:
2689: buf.append("\u1e5f");
2690: break; // r underbar
2691: case 202:
2692: buf.append("r\u0324");
2693: break; // r underdieresis
2694: case 193:
2695: buf.append("\u012a");
2696: break; // I macron
2697: case 8486:
2698: case 203:
2699: buf.append("\u016b");
2700: break; // u macron
2701: case 200:
2702: buf.append("\u1e6c");
2703: break; // T underdot
2704: case 205:
2705: buf.append("\u1e64");
2706: break; // S acute
2707: case 206:
2708: buf.append("\u2020");
2709: break; // dagger
2710: case 207:
2711: buf.append("\u0115");
2712: break; // e breve
2713: case 204:
2714: buf.append("\u014f");
2715: break; // o breve
2716: case 211:
2717: buf.append("\u0100");
2718: break; // A macron
2719: case 212:
2720: buf.append("\u1e46");
2721: break; // N underdot
2722: case 210:
2723: buf.append("\u1e3b");
2724: break; // l underbar
2725: case 218:
2726: buf.append("\u016a");
2727: break; // U macron
2728: case 219:
2729: buf.append("\u0179");
2730: break; // Z acute
2731: case 217:
2732: buf.append("\u1e5a");
2733: break; // R underdot
2734: case 305:
2735: buf.append("\u0131");
2736: break; // dotlessi
2737: case 710:
2738: buf.append("\u1e47");
2739: break; // n underdot
2740: case 732:
2741: buf.append("\u1e49");
2742: break; // n underbar
2743: case 175:
2744: buf.append("\u0304");
2745: break; // macron
2746: case 728:
2747: buf.append("\u0306");
2748: break; // breve
2749: case 729:
2750: case 215:
2751: buf.append("\u1e25");
2752: break; // h underdot
2753: case 730:
2754: buf.append("\u012d");
2755: break; // i breve
2756: case 184:
2757: buf.append("\u0327");
2758: break; // cedilla
2759: case 733:
2760: buf.append("\u030b");
2761: break; // hungarumlaut
2762: case 731:
2763: buf.append("\u0328");
2764: break; // ogonek
2765: case 711:
2766: buf.append("\u030c");
2767: break; // caron
2768: case 199:
2769: buf.append("\u012b\u0303");
2770: break; // imacron tilde
2771: case 8226:
2772: buf.append("\u1e5d");
2773: break; // runderdot macron
2774: case 8482:
2775: buf.append("\u016b\0306");
2776: break; // umacron breve
2777: case 8804:
2778: buf.append("\u0101\u0301");
2779: break; // amacron acute
2780: case 8805:
2781: buf.append("\u016b\u0301");
2782: break; // umacron acute
2783: case 8719:
2784: buf.append("\u0113\u0301");
2785: break; // emacron acute
2786: case 170:
2787: buf.append("\u0113\u0300");
2788: break; // emacron breve
2789: case 186:
2790: buf.append("\u014d\u0300");
2791: break; // omacron breve
2792: case 161:
2793: buf.append("\u0101\u0306");
2794: break; // amacron breve
2795: case 8710:
2796: buf.append("\u0101\u0303");
2797: break; // amacron tilde
2798: case 171:
2799: buf.append("\u012b\u0301");
2800: break; // imacron acute
2801: case 8260:
2802: buf.append("\u1e00");
2803: break; // runderdotmacron acute
2804: case 183:
2805: buf.append("\u1e5b\u0301");
2806: break; // runderdot acute
2807: case 8240:
2808: buf.append("\u012b\u0306");
2809: break; // imacron breve
2810: case 63743:
2811: buf.append("\u016b\u0303");
2812: break; // umacron tilde
2813: default:
2814: buf.append(c);
2815: if ((int) c > 127)
2816: System.out.println("char " + c + " " + (int) c);
2817: break;
2818: }
2819: }
2820: return buf.toString();
2821: }
2822:
2823: static public String normanToUnicodeOld(String inp)
2824: {
2825: StringBuffer buf = new StringBuffer();
2826: for (int i = 0; i < inp.length(); i++)
2827: {
2828: char c = inp.charAt(i);
2829: switch (c)
2830: {
2831: case 1:
2832: buf.append("\u00d0");
2833: break; // Eth
2834: case 2:
2835: buf.append("\u00f0");
2836: break; // eth
2837: case 3:
2838: buf.append("\u0141");
2839: break; // Lslash
2840: case 4:
2841: buf.append("\u0142");
2842: break; // lslash
2843: case 5:
2844: buf.append("\u0160");
2845: break; // S caron
2846: case 6:
2847: buf.append("\u0161");
2848: break; // s caron
2849: case 7:
2850: buf.append("\u00dd");
2851: break; // Y acute
2852: case 8:
2853: buf.append("\u00fd");
2854: break; // y acute
2855: case 11:
2856: buf.append("\u00de");
2857: break; // Thorn
2858: case 12:
2859: buf.append("\u00fe");
2860: break; // thorn
2861: case 14:
2862: buf.append("\u017d");
2863: break; // Z caron
2864: case 15:
2865: buf.append("\u017e");
2866: break; // z caron
2867: case 17:
2868: buf.append("\u0073");
2869: break; // asciitilde
2870: case 18:
2871: buf.append("j\u0305");
2872: break; // j macron [does a single char exist?]
2873: case 19:
2874: buf.append("^");
2875: break; // circumflex
2876: case 20:
2877: buf.append("\u0303");
2878: break; // tilde
2879: case 21:
2880: buf.append("\u00bd");
2881: break; // onehalf
2882: case 22:
2883: buf.append("\u00bc");
2884: break; // onequarter
2885: case 23:
2886: buf.append("\u00b9");
2887: break; // onesuperior
2888: case 24:
2889: buf.append("\u00be");
2890: break; // threequarters
2891: case 25:
2892: buf.append("\u00b3");
2893: break; // threesuperior
2894: case 26:
2895: buf.append("\u00b2");
2896: break; // twosuperior
2897: case 27:
2898: buf.append("\u00a6");
2899: break; // brokenbar
2900: case 28:
2901: buf.append("-");
2902: break; // minus
2903: case 29:
2904: buf.append("\u00d7");
2905: break; // multiply
2906: case 39:
2907: buf.append("'");
2908: break; // quotesingle
2909: case 94:
2910: buf.append("\u0302");
2911: break; // circumflex
2912: case 96:
2913: buf.append("\u0300");
2914: break; // grave
2915: case 128:
2916: buf.append("\u00c4");
2917: break; // A dieresis
2918: case 129:
2919: buf.append("\u00c5");
2920: break; // A ring
2921: case 131:
2922: buf.append("\u00c9");
2923: break; // E acute
2924: case 132:
2925: buf.append("\u00d1");
2926: break; // N tilde
2927: case 133:
2928: buf.append("\u00d6");
2929: break; // O dieresis
2930: case 134:
2931: buf.append("\u00dc");
2932: break; // U dieresis
2933: case 135:
2934: buf.append("\u00e1");
2935: break; // a acute
2936: case 136:
2937: buf.append("\u00e0");
2938: break; // a grave
2939: case 137:
2940: buf.append("\u00e2");
2941: break; // a circumflex
2942: case 138:
2943: buf.append("\u00e4");
2944: break; // a dieresis
2945: case 139:
2946: buf.append("\u00e3");
2947: break; // a tilde
2948: case 140:
2949: buf.append("\u0101");
2950: break; // a macron
2951: case 141:
2952: buf.append("\u00e7");
2953: break; // c cedilla
2954: case 142:
2955: buf.append("\u00e9");
2956: break; // e acute
2957: case 143:
2958: buf.append("\u00e8");
2959: break; // e grave
2960: case 144:
2961: buf.append("\u00ea");
2962: break; // e circumflex
2963: case 145:
2964: buf.append("\u00eb");
2965: break; // e dieresis
2966: case 146:
2967: buf.append("\u00ed");
2968: break; // i acute
2969: case 147:
2970: buf.append("\u00ec");
2971: break; // i grave
2972: case 148:
2973: buf.append("\u00ee");
2974: break; // i circumflex
2975: case 149:
2976: buf.append("\u00ef");
2977: break; // i dieresis
2978: case 150:
2979: buf.append("\u00f1");
2980: break; // n tilde
2981: case 151:
2982: buf.append("\u00f3");
2983: break; // o acute
2984: case 152:
2985: buf.append("\u00f2");
2986: break; // o grave
2987: case 153:
2988: buf.append("\u00f4");
2989: break; // o circumflex
2990: case 154:
2991: buf.append("\u00f6");
2992: break; // o dieresis
2993: case 155:
2994: buf.append("\u00f5");
2995: break; // o tilde
2996: case 156:
2997: buf.append("\u00fa");
2998: break; // u acute
2999: case 157:
3000: buf.append("\u00f9");
3001: break; // u grave
3002: case 158:
3003: buf.append("\u00fb");
3004: break; // u circumflex
3005: case 159:
3006: buf.append("\u00fc");
3007: break; // u dieresis
3008: case 160:
3009: buf.append("\u1e6d");
3010: break; // t underdot
3011: case 161:
3012: buf.append("\u00b0");
3013: break; // degree
3014: case 162:
3015: buf.append("\u1ebd");
3016: break; // e tilde
3017: case 163:
3018: buf.append("\u00a3");
3019: break; // sterling
3020: case 164:
3021: buf.append("\u00a7");
3022: break; // section
3023: case 166:
3024: buf.append("\u00b6");
3025: break; // paragraph
3026: case 167:
3027: buf.append("\u015b");
3028: break; // s acute
3029: case 168:
3030: buf.append("\u1e5b");
3031: break; // r underdot
3032: case 169:
3033: buf.append("\u1e67");
3034: break; // s caron
3035: case 171:
3036: buf.append("\u0301");
3037: break; // acute
3038: case 172:
3039: buf.append("\u0308");
3040: break; // dieresis
3041: case 173:
3042: buf.append("\u1e6d");
3043: break; // t underdot
3044: case 174:
3045: buf.append("\u00c6");
3046: break; // AE
3047: case 175:
3048: buf.append("\u014d");
3049: break; // o macron
3050: case 176:
3051: buf.append("\u0129");
3052: break; // i tilde
3053: case 177:
3054: buf.append("\u00b1");
3055: break; // plusminus
3056: case 180:
3057: buf.append("\u012b");
3058: break; // i macron
3059: case 181:
3060: buf.append("\u1e43");
3061: break; // m underdot
3062: case 182:
3063: buf.append("\u1e0d");
3064: break; // d underdot
3065: case 183:
3066: buf.append("\u1e63");
3067: break; // s underdot
3068: case 185:
3069: buf.append("\u017a");
3070: break; // z acute
3071: case 186:
3072: buf.append("\u1e45");
3073: break; // n overdot
3074: case 189:
3075: buf.append("\u0169");
3076: break; // u tilde
3077: case 190:
3078: buf.append("\u00e6");
3079: break; // ae
3080: case 191:
3081: buf.append("\u00f8");
3082: break; // oslash
3083: case 192:
3084: buf.append("\u0304\u0306");
3085: break; // macron breve
3086: case 194:
3087: buf.append("\u1e37");
3088: break; //
3089: case 195:
3090: buf.append("j\u0305");
3091: break; // j macron [does a single char exist?]
3092: case 196:
3093: buf.append("\u0103");
3094: break; // a breve
3095: case 197:
3096: buf.append("\u016d");
3097: break; // u breve
3098: case 200:
3099: buf.append("\u1e42");
3100: break; // M underdot
3101: case 201:
3102: buf.append("\u2026");
3103: break; // ellipsis
3104: case 203:
3105: buf.append("\u00c0");
3106: break; // A grave
3107: case 204:
3108: buf.append("\u00c3");
3109: break; // A tilde
3110: case 205:
3111: buf.append("\u00d5");
3112: break; // O tilde
3113: case 206:
3114: buf.append("m\u0306");
3115: break; // m breve
3116: case 207:
3117: buf.append("\u0153");
3118: break; // oe
3119: case 208:
3120: buf.append("\u2013");
3121: break; // endash
3122: case 209:
3123: buf.append("\u2014");
3124: break; // emdash
3125: case 210:
3126: buf.append("\u201c");
3127: break; // quotedblleft
3128: case 211:
3129: buf.append("\u201d");
3130: break; // quotedblright
3131: case 212:
3132: buf.append("\u2018");
3133: break; // quoteleft
3134: case 213:
3135: buf.append("\u2019");
3136: break; // quoteright
3137: case 214:
3138: buf.append("\u1e37");
3139: break; // l underring [actually underdot]
3140: case 215:
3141: buf.append("\u1e41");
3142: break; // m overdot
3143: case 216:
3144: buf.append("n\u0306");
3145: break; // n breve
3146: case 217:
3147: buf.append("\u00d7");
3148: break; // multiply
3149: case 219:
3150: buf.append("\u1e5b");
3151: break; // r underring [actually underdot]
3152: case 220:
3153: buf.append("\u1e44");
3154: break; // N overdot
3155: case 221:
3156: buf.append("\u1e62");
3157: break; // S underdot
3158: case 222:
3159: buf.append("\u1e24");
3160: break; // H underdot
3161: case 223:
3162: buf.append("\u1e0c");
3163: break; // D underdot
3164: case 224:
3165: buf.append("\u2021");
3166: break; // daggerdbl
3167: case 226:
3168: buf.append("\u1e36");
3169: break; // L underdot
3170: case 227:
3171: buf.append("\u0113");
3172: break; // e macron
3173: case 229:
3174: buf.append("\u1e5f");
3175: break; // r underbar
3176: case 230:
3177: buf.append("r\u0324");
3178: break; // r underdieresis
3179: case 231:
3180: buf.append("\u012a");
3181: break; // I macron
3182: case 232:
3183: buf.append("\u016b");
3184: break; // u macron
3185: case 233:
3186: buf.append("\u01e6c");
3187: break; // T underdot
3188: case 234:
3189: buf.append("\u1e64");
3190: break; // S acute
3191: case 235:
3192: buf.append("\u2020");
3193: break; // dagger
3194: case 236:
3195: buf.append("\u0115");
3196: break; // e breve
3197: case 237:
3198: buf.append("\u014f");
3199: break; // o breve
3200: case 238:
3201: buf.append("\u0100");
3202: break; // A macron
3203: case 239:
3204: buf.append("\u1e46");
3205: break; // N underdot
3206: case 241:
3207: buf.append("\u1e3b");
3208: break; // l underbar
3209: case 242:
3210: buf.append("\u016a");
3211: break; // U macron
3212: case 243:
3213: buf.append("\u0179");
3214: break; // Z acute
3215: case 244:
3216: buf.append("\u1e5a");
3217: break; // R underdot
3218: case 245:
3219: buf.append("\u0131");
3220: break; // dotlessi
3221: case 246:
3222: buf.append("\u1e47");
3223: break; // n underdot
3224: case 247:
3225: buf.append("\u1e49");
3226: break; // n underbar
3227: case 248:
3228: buf.append("\u0304");
3229: break; // macron
3230: case 249:
3231: buf.append("\u0306");
3232: break; // breve
3233: case 250:
3234: buf.append("\u1e25");
3235: break; // h underdot
3236: case 251:
3237: buf.append("\u012d");
3238: break; // i breve
3239: case 252:
3240: buf.append("\u0327");
3241: break; // cedilla
3242: case 253:
3243: buf.append("\u030b");
3244: break; // hungarumlaut
3245: case 254:
3246: buf.append("\u0328");
3247: break; // ogonek
3248: case 255:
3249: buf.append("\u030c");
3250: break; // caron
3251: case 130:
3252: buf.append("\u012b\u0303");
3253: break; // imacron tilde
3254: case 165:
3255: buf.append("\u1e5d");
3256: break; // runderdot macron
3257: case 170:
3258: buf.append("\u016b\0306");
3259: break; // umacron breve
3260: case 178:
3261: buf.append("\u0101\u0301");
3262: break; // amacron acute
3263: case 179:
3264: buf.append("\u016b\u0301");
3265: break; // umacron acute
3266: case 184:
3267: buf.append("\u0113\u0301");
3268: break; // emacron acute
3269: case 187:
3270: buf.append("\u0113\u0300");
3271: break; // emacron breve
3272: case 188:
3273: buf.append("\u014d\u0300");
3274: break; // omacron breve
3275: case 193:
3276: buf.append("\u0101\u0306");
3277: break; // amacron breve
3278: case 198:
3279: buf.append("\u0101\u0303");
3280: break; // amacron tilde
3281: case 199:
3282: buf.append("\u012b\u0301");
3283: break; // imacron acute
3284: case 218:
3285: buf.append("\u1e00");
3286: break; // runderdotmacron acute
3287: case 225:
3288: buf.append("\u1e5b\u0301");
3289: break; // runderdot acute
3290: case 228:
3291: buf.append("\u012b\u0306");
3292: break; // imacron breve
3293: case 240:
3294: buf.append("\u016b\u0303");
3295: break; // umacron tilde
3296: default:
3297: buf.append(c);
3298: break;
3299: }
3300: }
3301: return buf.toString();
3302: }
3303:
3304: static public String normanToUnicodeNew(String inp)
3305: {
3306: StringBuffer buf = new StringBuffer();
3307: for (int i = 0; i < inp.length(); i++)
3308: {
3309: char c = inp.charAt(i);
3310: switch (c)
3311: {
3312: case 1:
3313: buf.append("\u00d0");
3314: break; // Eth
3315: case 2:
3316: buf.append("\u00f0");
3317: break; // eth
3318: case 3:
3319: buf.append("\u0141");
3320: break; // Lslash
3321: case 4:
3322: buf.append("\u0142");
3323: break; // lslash
3324: case 5:
3325: buf.append("\u0160");
3326: break; // S caron
3327: case 6:
3328: buf.append("\u0161");
3329: break; // s caron
3330: case 7:
3331: buf.append("\u00dd");
3332: break; // Y acute
3333: case 8:
3334: buf.append("\u00fd");
3335: break; // y acute
3336: case 11:
3337: buf.append("\u00de");
3338: break; // Thorn
3339: case 12:
3340: buf.append("\u00fe");
3341: break; // thorn
3342: case 14:
3343: buf.append("\u017d");
3344: break; // Z caron
3345: case 15:
3346: buf.append("\u017e");
3347: break; // z caron
3348: case 17:
3349: buf.append("\u0073");
3350: break; // asciitilde
3351: case 18:
3352: buf.append("j\u0305");
3353: break; // j macron [does a single char exist?]
3354: case 19:
3355: buf.append("^");
3356: break; // circumflex
3357: case 20:
3358: buf.append("\u0303");
3359: break; // tilde
3360: case 21:
3361: buf.append("\u00bd");
3362: break; // onehalf
3363: case 22:
3364: buf.append("\u00bc");
3365: break; // onequarter
3366: case 23:
3367: buf.append("\u00b9");
3368: break; // onesuperior
3369: case 24:
3370: buf.append("\u00be");
3371: break; // threequarters
3372: case 25:
3373: buf.append("\u00b3");
3374: break; // threesuperior
3375: case 26:
3376: buf.append("\u00b2");
3377: break; // twosuperior
3378: case 27:
3379: buf.append("\u00a6");
3380: break; // brokenbar
3381: case 28:
3382: buf.append("-");
3383: break; // minus
3384: case 29:
3385: buf.append("\u00d7");
3386: break; // multiply
3387: case 39:
3388: buf.append("'");
3389: break; // quotesingle
3390: case 94:
3391: buf.append("\u0302");
3392: break; // circumflex
3393: case 96:
3394: buf.append("\u0300");
3395: break; // grave
3396: case 196:
3397: buf.append("\u00c4");
3398: break; // A dieresis
3399: case 197:
3400: buf.append("\u00c5");
3401: break; // A ring
3402: case 201:
3403: buf.append("\u00c9");
3404: break; // E acute
3405: case 209:
3406: buf.append("\u00d1");
3407: break; // N tilde
3408: case 214:
3409: buf.append("\u00d6");
3410: break; // O dieresis
3411: case 220:
3412: buf.append("\u00dc");
3413: break; // U dieresis
3414: case 225:
3415: buf.append("\u00e1");
3416: break; // a acute
3417: case 224:
3418: buf.append("\u00e0");
3419: break; // a grave
3420: case 226:
3421: buf.append("\u00e2");
3422: break; // a circumflex
3423: case 228:
3424: buf.append("\u00e4");
3425: break; // a dieresis
3426: case 227:
3427: buf.append("\u00e3");
3428: break; // a tilde
3429: case 229:
3430: buf.append("\u0101");
3431: break; // a macron
3432: case 231:
3433: buf.append("\u00e7");
3434: break; // c cedilla
3435: case 233:
3436: buf.append("\u00e9");
3437: break; // e acute
3438: case 232:
3439: buf.append("\u00e8");
3440: break; // e grave
3441: case 234:
3442: buf.append("\u00ea");
3443: break; // e circumflex
3444: case 235:
3445: buf.append("\u00eb");
3446: break; // e dieresis
3447: case 237:
3448: buf.append("\u00ed");
3449: break; // i acute
3450: case 236:
3451: buf.append("\u00ec");
3452: break; // i grave
3453: case 238:
3454: buf.append("\u00ee");
3455: break; // i circumflex
3456: case 239:
3457: buf.append("\u00ef");
3458: break; // i dieresis
3459: case 241:
3460: buf.append("\u00f1");
3461: break; // n tilde
3462: case 243:
3463: buf.append("\u00f3");
3464: break; // o acute
3465: case 242:
3466: buf.append("\u00f2");
3467: break; // o grave
3468: case 244:
3469: buf.append("\u00f4");
3470: break; // o circumflex
3471: case 246:
3472: buf.append("\u00f6");
3473: break; // o dieresis
3474: case 245:
3475: buf.append("\u00f5");
3476: break; // o tilde
3477: case 250:
3478: buf.append("\u00fa");
3479: break; // u acute
3480: case 249:
3481: buf.append("\u00f9");
3482: break; // u grave
3483: case 251:
3484: buf.append("\u00fb");
3485: break; // u circumflex
3486: case 252:
3487: buf.append("\u00fc");
3488: break; // u dieresis
3489: case 8224:
3490: buf.append("\u1e6d");
3491: break; // t underdot
3492: case 176:
3493: buf.append("\u00b0");
3494: break; // degree
3495: case 162:
3496: buf.append("\u1ebd");
3497: break; // e tilde
3498: case 163:
3499: buf.append("\u00a3");
3500: break; // sterling
3501: case 167:
3502: buf.append("\u00a7");
3503: break; // section
3504: case 182:
3505: buf.append("\u00b6");
3506: break; // paragraph
3507: case 223:
3508: buf.append("\u015b");
3509: break; // s acute
3510: case 174:
3511: buf.append("\u1e5b");
3512: break; // r underdot
3513: case 169:
3514: buf.append("\u1e45");
3515: break; // n overdot
3516: case 180:
3517: buf.append("\u0301");
3518: break; // acute
3519: case 168:
3520: buf.append("\u0308");
3521: break; // dieresis
3522: case 8800:
3523: buf.append("\u1e6d");
3524: break; // t underdot
3525: case 198:
3526: buf.append("\u00c6");
3527: break; // AE
3528: case 216:
3529: buf.append("\u014d");
3530: break; // o macron
3531: case 8734:
3532: buf.append("\u0129");
3533: break; // i tilde
3534: case 177:
3535: buf.append("\u00b1");
3536: break; // plusminus
3537: case 165:
3538: buf.append("\u012b");
3539: break; // i macron
3540: case 181:
3541: buf.append("\u1e43");
3542: break; // m underdot
3543: case 8706:
3544: buf.append("\u1e0d");
3545: break; // d underdot
3546: case 8721:
3547: buf.append("\u1e63");
3548: break; // s underdot
3549: case 960:
3550: buf.append("\u017a");
3551: break; // z acute
3552: case 8747:
3553: buf.append("\u1e45");
3554: break; // n overdot
3555: case 937:
3556: buf.append("\u0169");
3557: break; // u tilde
3558: case 230:
3559: buf.append("\u00e6");
3560: break; // ae
3561: case 248:
3562: buf.append("\u00f8");
3563: break; // oslash
3564: case 191:
3565: buf.append("\u0304\u0306");
3566: break; // macron breve
3567: case 172:
3568: buf.append("\u1e37");
3569: break; //
3570: case 8730:
3571: buf.append("j\u0305");
3572: break; // j macron [does a single char exist?]
3573: case 402:
3574: buf.append("\u0103");
3575: break; // a breve
3576: case 8776:
3577: buf.append("\u016d");
3578: break; // u breve
3579: case 187:
3580: buf.append("\u1e42");
3581: break; // M underdot
3582: case 8230:
3583: buf.append("\u2026");
3584: break; // ellipsis
3585: case 192:
3586: buf.append("\u00c0");
3587: break; // A grave
3588: case 195:
3589: buf.append("\u00c3");
3590: break; // A tilde
3591: case 213:
3592: buf.append("\u00d5");
3593: break; // O tilde
3594: case 338:
3595: buf.append("m\u0306");
3596: break; // m breve
3597: case 339:
3598: buf.append("\u0153");
3599: break; // oe
3600: case 8211:
3601: buf.append("\u2013");
3602: break; // endash
3603: case 8212:
3604: buf.append("\u2014");
3605: break; // emdash
3606: case 8220:
3607: buf.append("\u201c");
3608: break; // quotedblleft
3609: case 8221:
3610: buf.append("\u201d");
3611: break; // quotedblright
3612: case 8216:
3613: buf.append("\u2018");
3614: break; // quoteleft
3615: case 8217:
3616: buf.append("\u2019");
3617: break; // quoteright
3618: case 247:
3619: buf.append("\u1e37");
3620: break; // l underring [actually underdot]
3621: case 9674:
3622: buf.append("\u1e41");
3623: break; // m overdot
3624: case 255:
3625: buf.append("n\u0306");
3626: break; // n breve
3627: case 376:
3628: buf.append("\u00d7");
3629: break; // multiply
3630: case 8364:
3631: buf.append("\u1e5b");
3632: break; // r underring [actually underdot]
3633: case 8249:
3634: buf.append("\u1e44");
3635: break; // N overdot
3636: case 8250:
3637: buf.append("\u1e62");
3638: break; // S underdot
3639: case 64257:
3640: buf.append("\u1e24");
3641: break; // H underdot
3642: case 64258:
3643: buf.append("\u1e0c");
3644: break; // D underdot
3645: case 8225:
3646: buf.append("\u2021");
3647: break; // daggerdbl
3648: case 8218:
3649: buf.append("\u1e36");
3650: break; // L underdot
3651: case 8222:
3652: buf.append("\u0113");
3653: break; // e macron
3654: case 194:
3655: buf.append("\u1e5f");
3656: break; // r underbar
3657: case 202:
3658: buf.append("r\u0324");
3659: break; // r underdieresis
3660: case 193:
3661: buf.append("\u012a");
3662: break; // I macron
3663: case 203:
3664: buf.append("\u016b");
3665: break; // u macron
3666: case 200:
3667: buf.append("\u1e6c");
3668: break; // T underdot
3669: case 205:
3670: buf.append("\u1e64");
3671: break; // S acute
3672: case 206:
3673: buf.append("\u2020");
3674: break; // dagger
3675: case 207:
3676: buf.append("\u0115");
3677: break; // e breve
3678: case 204:
3679: buf.append("\u014f");
3680: break; // o breve
3681: case 211:
3682: buf.append("\u0100");
3683: break; // A macron
3684: case 212:
3685: buf.append("\u1e46");
3686: break; // N underdot
3687: case 210:
3688: buf.append("\u1e3b");
3689: break; // l underbar
3690: case 218:
3691: buf.append("\u016a");
3692: break; // U macron
3693: case 219:
3694: buf.append("\u0179");
3695: break; // Z acute
3696: case 217:
3697: buf.append("\u1e5a");
3698: break; // R underdot
3699: case 305:
3700: buf.append("\u0131");
3701: break; // dotlessi
3702: case 710:
3703: buf.append("\u1e47");
3704: break; // n underdot
3705: case 732:
3706: buf.append("\u1e49");
3707: break; // n underbar
3708: case 175:
3709: buf.append("\u0304");
3710: break; // macron
3711: case 728:
3712: buf.append("\u0306");
3713: break; // breve
3714: case 729:
3715: buf.append("\u1e25");
3716: break; // h underdot
3717: case 730:
3718: buf.append("\u012d");
3719: break; // i breve
3720: case 184:
3721: buf.append("\u0327");
3722: break; // cedilla
3723: case 733:
3724: buf.append("\u030b");
3725: break; // hungarumlaut
3726: case 731:
3727: buf.append("\u0328");
3728: break; // ogonek
3729: case 711:
3730: buf.append("\u030c");
3731: break; // caron
3732: case 199:
3733: buf.append("\u012b\u0303");
3734: break; // imacron tilde
3735: case 8226:
3736: buf.append("\u1e5d");
3737: break; // runderdot macron
3738: case 8482:
3739: buf.append("\u016b\0306");
3740: break; // umacron breve
3741: case 8804:
3742: buf.append("\u0101\u0301");
3743: break; // amacron acute
3744: case 8805:
3745: buf.append("\u016b\u0301");
3746: break; // umacron acute
3747: case 8719:
3748: buf.append("\u0113\u0301");
3749: break; // emacron acute
3750: case 170:
3751: buf.append("\u0113\u0300");
3752: break; // emacron breve
3753: case 186:
3754: buf.append("\u014d\u0300");
3755: break; // omacron breve
3756: case 161:
3757: buf.append("\u0101\u0306");
3758: break; // amacron breve
3759: case 8710:
3760: buf.append("\u0101\u0303");
3761: break; // amacron tilde
3762: case 171:
3763: buf.append("\u012b\u0301");
3764: break; // imacron acute
3765: case 8260:
3766: buf.append("\u1e00");
3767: break; // runderdotmacron acute
3768: case 183:
3769: buf.append("\u1e5b\u0301");
3770: break; // runderdot acute
3771: case 8240:
3772: buf.append("\u012b\u0306");
3773: break; // imacron breve
3774: case 63743:
3775: buf.append("\u016b\u0303");
3776: break; // umacron tilde
3777: default:
3778: buf.append(c);
3779: break;
3780: }
3781: }
3782: return buf.toString();
3783: }
3784:
3785: public static ConversionProperties getFieldNamesAndDestTableName(String create, String query,
3786: String tableName)
3787: {
3788: String[] fieldNames = null;
3789: String destTableName = null;
3790: // determine destTableName from createStatement or from source table
3791: // name
3792: if (!create.equals(""))
3793: {
3794: int fromIndex = create.toLowerCase().indexOf("table") + 5;
3795: int toIndex = create.indexOf("(");
3796: int endIndex = create.indexOf(")", toIndex);
3797:
3798: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
3799: System.out.println("destTable " + destTableName);
3800: // retrieve field_names from select statement
3801: // TODO problem with different fieldNames in create statement will
3802: // overwrite them
1.7 rogo 3803: if (query.indexOf("*") < 0 && create.equals(""))// quick hack for hartmut
1.4 rogo 3804: {
3805: int selectEndIndex = query.indexOf("from");
3806: StringTokenizer tokenizer = new StringTokenizer(query.substring(6, selectEndIndex), ",");
3807: int numFields = tokenizer.countTokens();
3808: fieldNames = new String[numFields];
3809: int fieldIndex = 0;
3810: while (tokenizer.hasMoreTokens())
3811: {
3812: String fieldName = tokenizer.nextToken().trim();
3813: fieldNames[fieldIndex] = convertText(fieldName);
3814: System.out.println(fieldNames[fieldIndex]);
3815: fieldIndex++;
3816: }
3817:
3818: } else
3819: {
3820: // use create statement for field names
3821: StringTokenizer tokenizer = new StringTokenizer(create.substring(toIndex + 1, endIndex),
3822: ",");
3823: int numFields = tokenizer.countTokens();
3824: fieldNames = new String[numFields];
3825: int fieldIndex = 0;
3826: while (tokenizer.hasMoreTokens())
3827: {
3828: String fieldName = tokenizer.nextToken().trim();
3829: int index = fieldName.lastIndexOf(" ");
3830: fieldNames[fieldIndex] = fieldName.substring(0, index);
3831: System.out.println(fieldNames[fieldIndex]);
3832: fieldIndex++;
3833: }
3834: }
3835: } else
3836: {
3837: destTableName = convertText(tableName);
3838:
3839: // retrieve field_names from select statement
3840: if (query.indexOf("*") < 0)
3841: {
1.10 rogo 3842: int selectEndIndex = query.lastIndexOf("from");
1.4 rogo 3843: StringTokenizer tokenizer = new StringTokenizer(query.substring(6, selectEndIndex), ",");
3844: int numFields = tokenizer.countTokens();
3845: fieldNames = new String[numFields];
3846: int fieldIndex = 0;
3847: while (tokenizer.hasMoreTokens())
3848: {
3849: String fieldName = tokenizer.nextToken().trim();
1.10 rogo 3850: String text = convertText(fieldName);
3851: if (text.indexOf("\"") >= 0)
3852: fieldNames[fieldIndex] = convertText(fieldName);
3853: else
3854: fieldNames[fieldIndex] = beanDest.getQC() + convertText(fieldName) + beanDest.getQC();
1.4 rogo 3855: // System.out.println("field "+ fieldNames[fieldIndex]);
3856: fieldIndex++;
3857: }
3858:
3859: } else
3860: {
3861: Vector fieldNamesVec = bean.getColumnNames();
3862: fieldNames = new String[fieldNamesVec.size()];
3863: int fieldIndex = -1;
3864: for (Iterator iter = fieldNamesVec.iterator(); iter.hasNext();)
3865: {
3866: String element = (String) iter.next();
3867: fieldNames[++fieldIndex] = beanDest.getQC() + convertText(element) + beanDest.getQC();
3868: // System.out.println("field " + fieldNames[fieldIndex]);
3869: }
3870: }
3871: }
3872: return new ConversionProperties(destTableName, fieldNames);
3873: }
3874:
3875: /**
3876: * creates an insert into statement for the specified table and given field
3877: * names
3878: *
3879: * @param destTableName
3880: * @param fieldNames
3881: * @return
3882: */
3883: public static StringBuffer createInsertCommand(String destTableName, String[] fieldNames)
3884: {
3885: StringBuffer command = new StringBuffer();
3886: command.append("INSERT INTO ");
3887: command.append(beanDest.getQC());
3888: command.append(destTableName); // convertText((String)
3889: // names.get(tbIndex)));
3890: command.append(beanDest.getQC());
3891: command.append(" (");
3892: for (int i = 0; i < fieldNames.length; i++)
3893: {
3894: command.append(fieldNames[i]);
3895: if (i < fieldNames.length - 1)
3896: command.append(",");
3897: }
3898: command.append(") ");
3899:
3900: command.append(" values ( ");
3901: // add a question marks for every field
3902: for (int i = 0; i < fieldNames.length - 1; ++i)
3903: command.append("?,");
3904: command.append("?)");
3905: return command;
3906: }
3907:
3908: public static StringBuffer createUpdateCommand(String destTableName, String[] fieldNames,
3909: String id)
3910: {
3911: StringBuffer command = new StringBuffer();
3912:
3913: command.append("UPDATE ");
3914: command.append(beanDest.getQC());
3915: command.append(destTableName);
3916: command.append(beanDest.getQC());
3917: command.append(" SET ");
3918:
3919: int size = bean.getColumnNames().size();
3920: for (int i = 0; i < size - 1; ++i)
3921: command.append(fieldNames[i] + " = ? ,");
3922: command.append(fieldNames[size - 1] + " = ? ");
3923: command.append("WHERE " + id + " = ?");
3924: return command;
3925: }
3926:
3927: public static StringBuffer createDeleteCommand(String destTableName, String idField)
3928: {
3929: StringBuffer command = new StringBuffer();
3930:
3931: command.append("DELETE FROM");
3932: command.append(beanDest.getQC());
3933: command.append(destTableName);
3934: // command.append(convertText((String) names.get(tbIndex)));
3935: command.append(beanDest.getQC());
3936: command.append("WHERE " + idField + " = ?");
3937: return command;
3938: }
3939:
3940: public void makeTest(String table, String idField, String tempQuery) throws Exception
3941: {
3942: int counter = 0;
3943:
3944: // ****** test code *****
3945:
3946: bean.getConnection();
3947: ResultSet resultSet = null;
3948: String lastResult = "P227634.11";// "P227625.79554";//"P227625.77391";//"P116034.970998";
3949: String myQuery = "select " + bean.getQC() + idField + bean.getQC() + ",serial " + " from "
3950: + bean.getQC() + table + bean.getQC();
3951: System.out.println("Query is now " + myQuery);
3952: JDialog statusDialog = new JDialog();
3953: statusDialog.setTitle("Status Information");
3954: JLabel status = new JLabel("actual DataSet : ");
3955: JLabel status2 = new JLabel(Integer.toString(++counter));
3956: JLabel status3 = new JLabel(lastResult);
3957:
3958: JPanel statusPanel = new JPanel();
3959: JPanel statusPanel2 = new JPanel();
3960: statusPanel.add(status);
3961: statusPanel.add(status2);
3962: statusPanel2.add(status3);
3963: statusDialog.getContentPane().add(statusPanel, "North");
3964: statusDialog.getContentPane().add(statusPanel2, "Center");
3965: statusDialog.setLocation(400, 500);
3966: statusDialog.setSize(300, 150);
3967: statusDialog.setVisible(true);
3968: while (true)
3969: {
3970: if (!statusDialog.isVisible())
3971: statusDialog.setVisible(true);
3972: tempQuery = myQuery + " where " + bean.getQC() + idField + bean.getQC() + ">'" + lastResult
3973: + "'";
3974: resultSet = bean.makeQuery(tempQuery, 1);
3975: if (resultSet == null)
3976: {
3977: System.out.println("lastResult was " + lastResult + " counter was " + counter);
3978: break;
3979: } else
3980: {
3981: resultSet.next();
3982: lastResult = resultSet.getString(1);
3983: counter++;
3984: status2.setText(Integer.toString(counter));
3985: status3.setText(lastResult + " " + resultSet.getString(2));
3986: if (counter % 100 == 0)
3987: {
3988: System.out.println("actual Result was " + lastResult + " counter was " + counter);
3989: // break;
3990: }
3991: }
3992: resultSet = null;
3993: }
3994: System.exit(0);
1.1 rogo 3995:
1.4 rogo 3996: // ****** end Test ******
1.1 rogo 3997:
1.4 rogo 3998: }
1.8 rogo 3999:
4000: public final static String generateSuffix(final int step)
4001: {
4002: String fileString = null;
4003: if (step < 10)
4004: fileString = "00" + step;
4005: else if (step < 100)
4006: fileString = "0" + step;
4007: else
4008: fileString = step + "";
4009: return fileString;
4010: }
4011:
1.1 rogo 4012: }
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>