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