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