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