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