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