1: /*
2: * Convert.java -- Converter class - Filemaker to SQL Converter
3: * Copyright (C) 2003 Robert Gordesch (rogo@mpiwg-berlin.mpg.de)
4: * This program is free software; you can redistribute it and/or modify it
5: * under the terms of the GNU General Public License as published by the Free
6: * Software Foundation; either version 2 of the License, or (at your option)
7: * any later version. Please read license.txt for the full details. A copy of
8: * the GPL may be found at http://www.gnu.org/copyleft/lgpl.html You should
9: * have received a copy of the GNU General Public License along with this
10: * program; if not, write to the Free Software Foundation, Inc., 59 Temple
11: * Place, Suite 330, Boston, MA 02111-1307 USA Created on 15.09.2003 by
12: * rogo
13: */
14:
15: import java.awt.Cursor;
16: import java.io.BufferedReader;
17: import java.io.BufferedWriter;
18: import java.io.File;
19: import java.io.FileInputStream;
20: import java.io.FileNotFoundException;
21: import java.io.FileOutputStream;
22: import java.io.InputStream;
23: import java.io.InputStreamReader;
24: import java.io.OutputStreamWriter;
25: import java.io.PrintStream;
26: import java.io.UnsupportedEncodingException;
27: import java.net.URL;
28: import java.sql.PreparedStatement;
29: import java.sql.SQLException;
30: import java.sql.Statement;
31: import java.sql.Types;
32: import java.util.ArrayList;
33: import java.util.Iterator;
34: import java.util.List;
35: import java.util.StringTokenizer;
36: import java.util.TreeSet;
37: import java.util.Vector;
38:
39: import com.exploringxml.xml.Node;
40: import com.exploringxml.xml.Xparse;
41:
42: class Convert
43: {
44: static DBBean bean = new DBBean();
45: static DBBean beanDest = new DBBean();
46:
47: static String user = "", passwd = "e1nste1n";
48: static String userDest = "postgres", passwdDest = "rogo";
49: static boolean batchRun = false;
50: static Vector databases = new Vector();
51: final static int numHits = 5000;
52: final static int numIntervalls = 2;
53: public static void main(String args[])
54: {
55: /* try
56: {
57: //byte[] b = "ö".getBytes("UTF-8");
58: // System.out.println("QueryString " +b[0]+" "+b[1]+(new String(b).getBytes()[0])+" "+new String(b).getBytes()[1]);
59: //System.out.println(new String(b,"UTF-8"));
60: } catch (UnsupportedEncodingException e)
61: {
62: e.printStackTrace();
63: }*/
64: FileOutputStream file = null;
65: if (args.length != 1)
66: {
67: System.out.println("Usage: java Convert <xml config file>");
68: System.exit(-1);
69: }
70: try
71: {
72: file = new FileOutputStream("./log.txt");
73: } catch (FileNotFoundException e1)
74: {
75: e1.printStackTrace();
76: }
77: PrintStream stream = new PrintStream(file);
78: // System.setOut(stream);
79: //System.setErr(stream);
80:
81: readXMLFile(args[0]);
82: if (!(new File(args[0]).exists()))
83: System.exit(0);
84:
85: System.out.println("Finished!");
86: //convert("jdbc:fmpro:http://141.14.237.74:8050","jdbc:postgresql://erebos/test",null,null);
87: }
88: public static void convertBatch(DBBean source, DBBean destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode, String delimiter) throws Exception
89: {
90: bean = source;
91: beanDest = destination;
92: convert(null, null, names, layouts, selects, creates, ids, mode, delimiter);
93: if (true)
94: return;
95: StringBuffer command = null;
96: try
97: {
98: bean.setConnection(source.url);
99: if (names == null)
100: names = bean.getTableNames();
101: //Collections.sort(names);
102: int tbIndex = 1;
103:
104: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
105: {
106: Vector[] result = null;
107: try
108: {
109: String query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
110: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
111: query = (selects != null) ? selects.get(tbIndex).toString() : query;
112: //if vectors[1].get(i) != null)
113: if (!layout.equals(""))
114: {
115: System.out.println("before " + query + " table" + names.get(tbIndex));
116: layout = " layout " + bean.getQC() + layout + bean.getQC();
117: String name = names.get(tbIndex).toString();
118: StringBuffer queryLayout = new StringBuffer(query);
119: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
120: query = queryLayout.toString();
121: System.out.println("added layout " + query);
122:
123: }
124: System.out.println(" performing query " + query);
125: //result = bean.getQueryData(query, null, 0);
126: bean.getConnection();
127: bean.makeQuery(query, 0);
128: } catch (Exception e)
129: {
130: System.out.println(e.getMessage());
131: e.printStackTrace();
132: continue;
133: }
134: //beanDest.setConnection("jdbc:postgresql://erebos/test3");
135: beanDest.setConnection(destination.url);
136:
137: Statement stm = beanDest.getConnection().createStatement();
138:
139: Vector tables = beanDest.getTableNames();
140: // Collections.sort(tables);
141: System.out.println("converting table " + names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
142: tables = beanDest.getTableNames();
143: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
144: stm = beanDest.getConnection().createStatement();
145: // System.exit(0);
146: if (mode == Convert.DataBase.CONVERT_MODE)
147: {
148: if (tables.indexOf(names.get(tbIndex)) >= 0)
149: {
150: stm.executeUpdate("drop table " + beanDest.getQC() + names.get(tbIndex) + beanDest.getQC());
151: tables.remove((String) names.get(tbIndex));
152: System.out.println("dropped table " + names.get(tbIndex));
153: } else if (tables.indexOf(convertText(names.get(tbIndex).toString())) >= 0)
154: {
155: stm.executeUpdate("drop table " + beanDest.getQC() + convertText((String) names.get(tbIndex)) + beanDest.getQC());
156: tables.remove(convertText((String) names.get(tbIndex)));
157: System.out.println("dropped table " + names.get(tbIndex));
158: }
159:
160: if (tables.indexOf(names.get(tbIndex)) < 0 && tables.indexOf(convertText(names.get(tbIndex).toString())) < 0)
161: {
162: if (creates.get(tbIndex).equals("") || creates.get(tbIndex).toString().toLowerCase().indexOf("create") < 0)
163: {
164: System.out.println("Warning empty or invalid create statement - creating one for you\n");
165:
166: command = new StringBuffer(50);
167: command.append("CREATE TABLE ");
168: command.append(beanDest.getQC());
169: command.append(convertText((String) names.get(tbIndex)));
170: command.append(beanDest.getQC());
171: command.append("(");
172: String type = null;
173: Vector columnNames = bean.getColumnNames();
174: for (int i = 0; i < columnNames.size() - 1; ++i)
175: {
176: type = bean.metaData.getColumnTypeName(i + 1);
177: // System.out.println(i+" "+result[1].get(i)+" "+type);
178: type = (type.equals("NUMBER")) ? "INT4" : type;
179: type = (type.equals("CONTAINER")) ? "TEXT" : type;
180:
181: command.append(beanDest.getQC() + convertText((String) columnNames.get(i)) + beanDest.getQC() + " " + type + ", ");
182: }
183: type = bean.metaData.getColumnTypeName(columnNames.size());
184: type = (type.equals("NUMBER")) ? "INT4" : type;
185: type = (type.equals("CONTAINER")) ? "TEXT" : type;
186: command.append(beanDest.getQC() + convertText((String) columnNames.get(columnNames.size() - 1)) + beanDest.getQC() + " " + type);
187: command.append(" )");
188: } else
189: command = new StringBuffer().append(creates.get(tbIndex).toString());
190:
191: System.out.println(command);
192: // System.exit(0);
193: //command.append(DBBean.getQC());
194: stm.executeUpdate(command.toString());
195:
196: }
197: }
198: Vector row = null;
199: command = new StringBuffer();
200:
201: command.append("INSERT INTO ");
202: command.append(beanDest.getQC());
203: command.append(convertText((String) names.get(tbIndex)));
204: command.append(beanDest.getQC());
205: command.append(" values ( ");
206:
207: for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
208: command.append("?,");
209: command.append("?)");
210: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
211: System.out.println(command);
212: while ((row = bean.getNextRow()) != null)
213: {
214: //print rows
215: Object obj = null;
216: for (int k = 0; k < row.size(); ++k)
217: {
218: obj = row.get(k);
219: if (obj instanceof ArrayList)
220: obj = formatFileMakerArray((List) obj, "\n");
221: String str = (obj == null) ? "NULL" : obj.toString();
222: if (!str.equals("NULL"))
223: pstm.setString(k + 1, str);
224: else
225: pstm.setNull(k + 1, Types.NULL);
226: }
227: pstm.execute();
228:
229: } // to for loop
230:
231: }
232: } catch (Exception e)
233: {
234: System.out.println("Error while connecting to database " + e);
235: //dialog.setVisible(false);
236: //dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
237: //FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
238: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
239: java.io.PrintStream stream = new java.io.PrintStream(b);
240: stream.print(command + "\n\n");
241: e.printStackTrace(stream);
242: System.err.println(b);
243: //FM2SQL.showErrorDialog(b.toString(), "Error occured !");
244:
245: }
246: // dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
247: //FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
248:
249: // dialog.setVisible(false);
250: }
251: public static String formatFileMakerArray(List list, String delimiter)
252: {
253: StringBuffer formattedString = new StringBuffer();
254: for (int i = 0; i < list.size(); ++i)
255: {
256: formattedString.append(list.get(i).toString());
257: if (i < list.size() - 1)
258: formattedString.append(delimiter);
259: }
260: return formattedString.toString();
261: }
262: /**
263: * Method for SQL UPDATE
264: * @param source
265: * @param destination
266: * @param names
267: * @param layouts
268: * @param selects
269: * @param creates
270: * @param ids
271: * @param mode
272: * @throws Exception
273: */
274: public static void update(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
275: {
276: FM2SQL.ProgressDialog dialog = null;
277: if (FM2SQL.fmInstance != null)
278: {
279: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
280: dialog.setTitle("Conversion running ...");
281: dialog.title.setText("Getting table data ...");
282: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
283: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
284: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
285: dialog.thread = Thread.currentThread();
286: }
287: // setting user and passwd
288: bean.setUserAndPasswd(user, passwd);
289: // setting user and passwd
290: beanDest.setUserAndPasswd(userDest, passwdDest);
291: if (dialog != null)
292: dialog.setSize(400, 250);
293: StringBuffer command = null;
294: String query = null;
295: try
296: {
297: //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
298: //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
299: bean.setConnection(source);
300: if (names == null)
301: names = bean.getTableNames();
302: // Collections.sort(names);
303: int tbIndex = 1;
304:
305: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
306: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
307: {
308: Vector[] result = null;
309: String destTableName = "";
310: try
311: {
312: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
313: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
314: query = (selects != null) ? selects.get(tbIndex).toString() : query;
315: //if vectors[1].get(i) != null)
316: if (layout != "")
317: {
318: layout = " layout " + bean.getQC() + layout + bean.getQC();
319: String name = names.get(tbIndex).toString();
320: StringBuffer queryLayout = new StringBuffer(query);
321: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
322: query = queryLayout.toString();
323: System.out.println("added layout " + query);
324:
325: }
326: dialog.title.setText("Getting table data ...");
327: dialog.table.setText(names.get(tbIndex).toString());
328: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
329: dialog.show();
330: bean.getConnection();
331: bean.makeQuery(query, 0);
332: } catch (Exception e)
333: {
334: continue;
335: }
336: // determine destTableName from createStatement or from source table name
337: if (!creates.get(tbIndex).equals(""))
338: {
339: String create = creates.get(tbIndex).toString().toLowerCase();
340: int fromIndex = create.indexOf("table") + 5;
341: int toIndex = create.indexOf("(");
342: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
343: System.out.println("destTable " + destTableName);
344:
345: } else
346: destTableName = convertText(names.get(tbIndex).toString());
347:
348: //beanDest.setConnection("jdbc:postgresql://erebos/test3");
349: beanDest.setConnection(destination);
350:
351: Statement stm = beanDest.getConnection().createStatement();
352:
353: Vector tables = beanDest.getTableNames();
354: // Collections.sort(tables);
355: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
356: tables = beanDest.getTableNames();
357: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
358: stm = beanDest.getConnection().createStatement();
359: // System.exit(0);
360:
361: if (dialog != null)
362: dialog.title.setText("Updating table data ...");
363:
364: int j = -1;
365:
366: Vector row = null;
367: command = new StringBuffer();
368:
369: command.append("UPDATE ");
370: command.append(beanDest.getQC());
371: command.append(destTableName);
372: //command.append(convertText((String) names.get(tbIndex)));
373: command.append(beanDest.getQC());
374: command.append(" SET ");
375:
376: int size = bean.getColumnNames().size();
377: for (int i = 0; i < size - 1; ++i)
378: command.append(beanDest.getQC() + convertText((String) bean.getColumnNames().get(i)) + beanDest.getQC() + " = ? ,");
379: command.append(convertText((String) bean.getColumnNames().get(size - 1)) + " = ? ");
380: command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
381: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
382: System.out.println(command + " " + tbIndex);
383: int rowCount = bean.getRowCount(query);
384: int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
385: while ((row = bean.getNextRow()) != null)
386: {
387: j++;
388: //print rows
389: Object obj = null;
390: /* for(int k=0;k<row.size()-1;++k)
391: {
392: obj = row.get(k);
393: //System.out.println("row "+obj+" "+k);
394: if(obj!=null&&!(obj instanceof ArrayList))
395: command.append("'"+convertUml(obj.toString())+"',");
396: else if(obj!=null&& obj instanceof ArrayList)
397: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',");
398: else command.append("NULL,");
399: }
400: obj = row.get(row.size() - 1);
401: if (obj != null && !(obj instanceof ArrayList))
402: command.append("'"+convertUml(obj.toString())+"')");
403: else
404: if(obj!=null&& obj instanceof ArrayList)
405: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')"); //command.append(obj.toString()+")");
406: else command.append("NULL)");
407: */
408: //command.append("'"+row.get(row.size()-1)+"')");
409: //command.append(" )");
410: // for(int k=0;k<row.size();++k)
411:
412: // System.out.println();
413: // System.out.println(command+" "+j+" "+row.size()+" "+ ((Vector)result2[0].get(j)).size());
414: // System.out.println(command);
415: for (int k = 0; k < row.size(); ++k)
416: {
417: obj = row.get(k);
418: if (obj instanceof ArrayList)
419: obj = ((List) obj).get(0);
420: String str = (obj == null) ? "NULL" : obj.toString();
421: if (!str.equals("NULL"))
422: pstm.setString(k + 1, str);
423: else
424: pstm.setNull(k + 1, Types.NULL);
425: }
426: pstm.setString(row.size() + 1, row.get(idIndex).toString());
427: //System.out.println(pstm.toString());
428: // System.exit(0);
429: pstm.execute();
430: //stm.executeUpdate(command.toString());
431: if (dialog != null)
432: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
433: // System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
434: command = null;
435: } // to for loop
436:
437: }
438: } catch (Exception e)
439: {
440: System.out.println("Error while connecting to database " + e);
441: if (dialog != null)
442: {
443: dialog.setVisible(false);
444: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
445: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
446: }
447: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
448: java.io.PrintStream stream = new java.io.PrintStream(b);
449: stream.print(command + "\n\n");
450: e.printStackTrace(stream);
451: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
452:
453: }
454: if (dialog != null)
455: {
456: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
457: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
458:
459: dialog.setVisible(false);
460: }
461:
462: }
463: /**
464: * transfers the specified array of tables to the destination database
465: and creates the table if it does not exist if it exists and mode is not append the table is dropped
466:
467: * @param source
468: * @param destination
469: * @param names
470: * @param layouts
471: * @param selects
472: * @param creates
473: * @param ids
474: * @param mode
475: * @throws Exception
476: */
477:
478: public static void convert(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode, String delimiter) throws Exception
479: {
480:
481: FM2SQL.ProgressDialog dialog = null;
482:
483: if (FM2SQL.fmInstance != null)
484: {
485: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
486: dialog.setTitle("Conversion running ...");
487: dialog.title.setText("Getting table data ...");
488: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
489: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
490: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
491: dialog.thread = Thread.currentThread();
492: dialog.setSize(400, 250);
493: }
494: java.util.TreeSet myIds = new TreeSet();
495: int deltaID = 1;
496: String idField = "";
497: String destTableName = "";
498: String[] fieldNames = null;
499: if (source != null && destination != null)
500: {
501: // setting user and passwd
502: bean.setUserAndPasswd(user, passwd);
503: // setting user and passwd
504: beanDest.setUserAndPasswd(userDest, passwdDest);
505: }
506: StringBuffer command = null;
507: String query = null;
508: try
509: {
510: if (source != null)
511: bean.setConnection(source);
512: else
513: bean.setConnection(bean.url);
514:
515: if (names == null)
516: names = bean.getTableNames();
517: // Collections.sort(names);
518: int tbIndex = 1;
519:
520: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
521: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
522: {
523: Vector[] result = null;
524: try
525: {
526: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
527: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
528: query = (selects != null) ? selects.get(tbIndex).toString() : query;
529: //if vectors[1].get(i) != null)
530: if (layout != "")
531: {
532: layout = " layout " + bean.getQC() + layout + bean.getQC();
533: String name = names.get(tbIndex).toString();
534: StringBuffer queryLayout = new StringBuffer(query);
535: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
536: query = queryLayout.toString();
537: System.out.println("added layout " + query);
538:
539: }
540: // if ( layout!= "")
541: // query += " layout " + bean.getQC() + layout + bean.getQC();
542: if (dialog != null)
543: {
544: dialog.title.setText("Reading table data ...");
545: dialog.table.setText(names.get(tbIndex).toString());
546: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
547: dialog.show();
548: }
549: //result = bean.getQueryData(query, dialog, 0);
550: bean.getConnection();
551: bean.makeQuery(query, 50);
552: idField = ids.get(tbIndex).toString();
553:
554: } catch (Exception e)
555: {
556: System.out.println(e);
557: continue;
558: }
559: if (destination != null)
560: beanDest.setConnection(destination);
561: else
562: beanDest.setConnection(beanDest.url);
563: Statement stm = beanDest.getConnection().createStatement();
564:
565: Vector tables = beanDest.getTableNames();
566: // Collections.sort(tables);
567: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
568: tables = beanDest.getTableNames();
569: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
570: stm = beanDest.getConnection().createStatement();
571: // System.exit(0);
572:
573: // determine destTableName from createStatement or from source table name
574: ConversionProperties prop= getFieldNamesAndDestTableName(creates.get(tbIndex).toString(),query,names.get(tbIndex).toString());
575: destTableName = prop.destTableName;
576: fieldNames = prop.fieldNames;
577: if (mode == Convert.DataBase.CONVERT_MODE)
578: {
579:
580: if (tables.indexOf(destTableName) >= 0)
581: {
582: stm.executeUpdate("drop table " + beanDest.getQC() + destTableName + beanDest.getQC());
583: tables.remove(destTableName);
584: System.out.println("dropped table" + destTableName);
585:
586: }
587: /*
588: if(destTableName.equals(""))
589: if (tables.indexOf(names.get(tbIndex)) >= 0)
590: {
591: stm.executeUpdate("drop table " + beanDest.getQC() + names.get(tbIndex) + beanDest.getQC());
592: tables.remove((String) names.get(tbIndex));
593: System.out.println("dropped table" + names.get(tbIndex));
594: } else if (tables.indexOf(convertText(names.get(tbIndex).toString())) >= 0)
595: {
596: stm.executeUpdate("drop table " + beanDest.getQC() + convertText((String) names.get(tbIndex)) + beanDest.getQC());
597: tables.remove(convertText((String) names.get(tbIndex)));
598: System.out.println("dropped table" + names.get(tbIndex));
599: }
600: */
601: if ((tables.indexOf(destTableName) < 0)) //&& tables.indexOf(names.get(tbIndex)) < 0 && tables.indexOf(convertText(names.get(tbIndex).toString())) < 0 )
602: {
603:
604: if (creates.get(tbIndex).equals("") || creates.get(tbIndex).toString().toLowerCase().indexOf("create") < 0)
605: {
606: System.out.println("Warning empty or invalid create statement - creating one for you\n");
607:
608: command = new StringBuffer(50);
609: command.append("CREATE TABLE ");
610: command.append(beanDest.getQC());
611: command.append(convertText((String) names.get(tbIndex)));
612: command.append(beanDest.getQC());
613: command.append("(");
614: String type = null;
615: Vector columnNames = bean.getColumnNames();
616: for (int i = 0; i < columnNames.size() - 1; ++i)
617: {
618: type = bean.metaData.getColumnTypeName(i + 1);
619: // System.out.println(i+" "+result[1].get(i)+" "+type);
620: type = (type.equals("NUMBER")) ? "INT4" : type;
621: type = (type.equals("CONTAINER")) ? "TEXT" : type;
622:
623: command.append(beanDest.getQC() + convertText((String) columnNames.get(i)) + beanDest.getQC() + " " + type + ", ");
624: }
625: type = bean.metaData.getColumnTypeName(columnNames.size());
626: type = (type.equals("NUMBER")) ? "INT4" : type;
627: type = (type.equals("CONTAINER")) ? "TEXT" : type;
628: command.append(beanDest.getQC() + convertText((String) columnNames.get(columnNames.size() - 1)) + beanDest.getQC() + " " + type);
629: command.append(" )");
630:
631: // System.out.println(command);
632: // System.exit(0);
633: //command.append(DBBean.getQC());
634: } else
635: command = new StringBuffer().append(creates.get(tbIndex).toString().toLowerCase());
636: stm.executeUpdate(command.toString());
637:
638: }
639: }
640: if (dialog != null)
641: dialog.title.setText("Writing table data ...");
642:
643: // prepare the insert statement
644: int j = -1;
645: Vector row = null;
646: command = new StringBuffer();
647:
648: command.append("INSERT INTO ");
649: command.append(beanDest.getQC());
650: command.append(destTableName); //convertText((String) names.get(tbIndex)));
651: command.append(beanDest.getQC());
652: command.append(" (");
653: for (int i = 0; i < fieldNames.length; i++)
654: {
655: command.append(fieldNames[i]);
656: if (i < fieldNames.length - 1)
657: command.append(",");
658: }
659: command.append(") ");
660:
661: command.append(" values ( ");
662:
663: // add a question marks for every field
664: for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
665: command.append("?,");
666: command.append("?)");
667: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
668: System.out.println(command);
669: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
670: Vector vec = new Vector(myIds);
671: int endIndex = -1;
672: String tempQuery = query;
673: String tempID = bean.getQC() + idField + bean.getQC();
674: // if id_field not do incremental conversion else do it all at once
675: if (!idField.equals(""))
676: {
677: long startTime = System.currentTimeMillis();
678: int counter = -1;
679: while (true)
680: {
681: ++counter;
682: if (counter == 0 && dialog != null)
683: dialog.title.setText("Check if data is available");
684: else if (dialog != null)
685: dialog.title.setText("Check if more data is available");
686: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, numHits);
687: if (myIds.isEmpty())
688: break;
689: vec = new Vector(myIds);
690: rowCount = vec.size();
691: System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIds.size());
692: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
693: if (vec.size() <= numIntervalls)
694: {
695: endIndex = 0;
696: deltaID = vec.size();
697: }
698: for (int k = 0; k < vec.size() - deltaID; k = k + deltaID)
699: {
700: System.out.println(vec.get(k) + " " + vec.get(k + deltaID) + " " + vec.lastElement());
701: if (query.indexOf("where") > 0)
702: tempQuery = query + " and " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID) + "'";
703: else
704: tempQuery = query + " where " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID) + "'";
705: System.out.println(tempQuery);
706: if (dialog != null)
707: dialog.title.setText("Reading table data ...");
708:
709: bean.makeQuery(tempQuery, deltaID);
710: if (dialog != null)
711: dialog.title.setText("Writing table data ...");
712:
713: command = writeDatainDestTable(dialog, command, k, pstm, rowCount, delimiter);
714: endIndex = k + deltaID;
715: }
716: System.out.println(endIndex);
717: //all data written ? if not write last chunk of data
718: if (endIndex == vec.size() - 1)
719: System.out.println("fits");
720: else
721: {
722: System.out.println(" last intervall from " + vec.get(endIndex) + " " + vec.lastElement());
723:
724: if (query.indexOf("where") > 0)
725: tempQuery = query + " and " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement() + "'";
726: else
727: tempQuery = query + " where " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement() + "'";
728: System.out.println(tempQuery);
729: if (dialog != null)
730: dialog.title.setText("Reading table data ...");
731: bean.makeQuery(tempQuery, 0);
732: if (dialog != null)
733: dialog.title.setText("Writing table data ...");
734: command = writeDatainDestTable(dialog, command, endIndex, pstm, rowCount, delimiter);
735: }
736: // prepare new query for next chunk
737: if (query.indexOf("where") > 0)
738: tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
739: else
740: tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
741:
742: }
743: long endTime = System.currentTimeMillis();
744: System.out.println("Time for incremental convert elapsed " + (endTime - startTime));
745: } else
746: {
747: // read and write all in one big chunk
748: long startTime = System.currentTimeMillis();
749:
750: bean.makeQuery(query, 0);
751: command = writeDatainDestTable(dialog, command, j, pstm, rowCount, delimiter);
752: long endTime = System.currentTimeMillis();
753: System.out.println("Time for old convert elapsed " + (endTime - startTime));
754:
755: }
756: }
757: } catch (Exception e)
758: {
759: System.out.println("Error while connecting to database " + e);
760: if (dialog != null)
761: {
762: dialog.setVisible(false);
763: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
764: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
765: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
766: java.io.PrintStream stream = new java.io.PrintStream(b);
767: stream.print(command + "\n\n");
768: e.printStackTrace(stream);
769: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
770: } else
771: {
772: e.printStackTrace();
773:
774: }
775: }
776: if (dialog != null)
777: {
778: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
779: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
780: dialog.setVisible(false);
781: }
782: }
783: /**
784: * Writes data to the destination table
785: * @param dialog progress dialog
786: * @param command
787: * @param j data index for progress bar
788: * @param pstm prepared statement
789: * @param rowCount number of datasets
790: * @return command
791: * @throws Exception
792: * @throws SQLException
793: */
794: private static StringBuffer writeDatainDestTable(FM2SQL.ProgressDialog dialog, StringBuffer command, int j, PreparedStatement pstm, int rowCount, String delimiter) throws Exception, SQLException
795: {
796: Vector row;
797: while ((row = bean.getNextRow()) != null)
798: {
799: j++;
800: // row = (Vector) result[0].get(j);
801: /* command = new StringBuffer();
802:
803: command.append("INSERT INTO ");
804: command.append(beanDest.getQC());
805: command.append(convertText((String) names.get(tbIndex)));
806: command.append(beanDest.getQC());
807: command.append(" values ( ");
808: */
809: //print rows
810: Object obj = null;
811: /* for(int k=0;k<row.size()-1;++k)
812: {
813: obj = row.get(k);
814: //System.out.println("row "+obj+" "+k);
815: if(obj!=null&&!(obj instanceof ArrayList))
816: command.append("'"+convertUml(obj.toString())+"',");
817: else if(obj!=null&& obj instanceof ArrayList)
818: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',");
819: else command.append("NULL,");
820: }
821: obj = row.get(row.size() - 1);
822: if (obj != null && !(obj instanceof ArrayList))
823: command.append("'"+convertUml(obj.toString())+"')");
824: else
825: if(obj!=null&& obj instanceof ArrayList)
826: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')"); //command.append(obj.toString()+")");
827: else command.append("NULL)");
828: */
829: //command.append("'"+row.get(row.size()-1)+"')");
830: //command.append(" )");
831: // for(int k=0;k<row.size();++k)
832:
833: // System.out.println();
834: // System.out.println(command+" "+j+" "+row.size()+" "+ ((Vector)result2[0].get(j)).size());
835: // System.out.println(command);
836: for (int k = 0; k < row.size(); ++k)
837: {
838: obj = row.get(k);
839:
840: if (obj instanceof ArrayList)
841: obj = formatFileMakerArray((List) obj, delimiter);
842:
843: String str = (obj == null) ? "NULL" : obj.toString();
844: if (obj instanceof Double)
845: {
846: pstm.setDouble(k + 1, ((Double) obj).doubleValue());
847: } else if (!str.equals("NULL"))
848: pstm.setString(k + 1, str);
849: else
850: pstm.setNull(k + 1, Types.NULL);
851: }
852: pstm.execute();
853: //stm.executeUpdate(command.toString());
854: if (dialog != null)
855: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
856: // System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
857: command = null;
858: } // to while loop
859: return command;
860: }
861:
862: /**
863: * removes special characters from the input string as well as .fp5
864: * @param newName String to change
865: * @return
866: */
867: public static String convertText(String newName)
868: {
869: StringBuffer alterMe = new StringBuffer(newName.trim().toLowerCase());
870: int length = alterMe.length();
871: int j = 0;
872: int index = alterMe.indexOf(".fp5");
873: if (index >= 0)
874: {
875: alterMe.delete(index, index + 4);
876: length = length - 4;
877: }
878:
879: while (j < length)
880: {
881: if (alterMe.charAt(j) == ' ')
882: {
883: alterMe.setCharAt(j, '_');
884: // if(j<length-1) j=j+1;
885: } else if (alterMe.charAt(j) == '_')
886: {
887:
888: if (alterMe.charAt(j + 1) == '_')
889: alterMe.deleteCharAt(j);
890: length = length - 1;
891: // if(j<length-1) j=j+1;
892: } else if (alterMe.charAt(j) == 'ä')
893: {
894: alterMe.setCharAt(j, 'a');
895: alterMe.insert(j + 1, "e");
896: length = length + 1;
897: if (j < length - 1)
898: j = j + 1;
899: } else if (alterMe.charAt(j) == 'ö')
900: {
901: alterMe.setCharAt(j, 'o');
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, 'u');
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, 's');
916: alterMe.insert(j + 1, "s");
917: length = length + 1;
918: if (j < length - 1)
919: j = j + 1;
920: } else if (alterMe.charAt(j) == ':')
921: {
922: if (j < length - 1)
923: {
924: if (alterMe.charAt(j + 1) == ':')
925: {
926: alterMe.setCharAt(j, '_');
927: alterMe.delete(j + 1, j + 2);
928: length = length - 1;
929:
930: }
931:
932: if (j < length - 1)
933: j = j + 1;
934: }
935: } else if (alterMe.charAt(j) == '-')
936: {
937: alterMe.setCharAt(j, '_');
938:
939: } else if (alterMe.charAt(j) == '?')
940: {
941: // changed ? to _ because of update statement
942: alterMe.setCharAt(j, '_');
943: // length = length + 1;
944: // j=j+1;
945: System.out.println(alterMe);
946: } else if (alterMe.charAt(j) == '.')
947: {
948: if (j == length - 1)
949: {
950: alterMe.delete(j, j);
951: length--;
952: } else
953: alterMe.setCharAt(j, '_');
954: }
955:
956: ++j;
957: }
958: return alterMe.toString();
959: }
960: /**
961: * Converts > and < in an entity (> or <)
962: * @param newName
963: * @return
964: */
965: public static String convertToEntities(String newName)
966: {
967: StringBuffer alterMe = new StringBuffer(newName.trim());
968: int length = alterMe.length();
969: int j = 0;
970:
971: while (j < length)
972: {
973:
974: if (alterMe.charAt(j) == '>')
975: {
976: alterMe.setCharAt(j, '&');
977: alterMe.insert(j + 1, "gt;");
978: length = length + 2;
979: if (j < length - 1)
980: j = j + 1;
981:
982: } else if (alterMe.charAt(j) == '<')
983: {
984: alterMe.setCharAt(j, '&');
985: alterMe.insert(j + 1, "lt;");
986: length = length + 2;
987: if (j < length - 1)
988: j = j + 1;
989:
990: }
991: ++j;
992: }
993: return alterMe.toString();
994: }
995: /**
996: * Masks the single quote character '-->\'
997: * @param newName
998: * @return
999: */
1000: public static String convertUml(String newName)
1001: {
1002: StringBuffer alterMe = new StringBuffer(newName.trim());
1003: int length = alterMe.length();
1004: int j = 0;
1005:
1006: while (j < length)
1007: {
1008:
1009: if (alterMe.charAt(j) == '\'')
1010: {
1011: alterMe.setCharAt(j, '\\');
1012: alterMe.insert(j + 1, "'");
1013: length = length + 1;
1014: if (j < length - 1)
1015: j = j + 1;
1016: }
1017: /* else
1018: if (alterMe.charAt(j) == '"')
1019: {
1020: alterMe.setCharAt(j, '\\');
1021: alterMe.insert(j + 1, "\"");
1022: length = length + 1;
1023: if(j<length-1) j=j+1;
1024: }
1025: else
1026: if (alterMe.charAt(j) == '>')
1027: {
1028: alterMe.setCharAt(j, '\\');
1029: alterMe.insert(j + 1, ">");
1030: length = length + 1;
1031: if(j<length-1) j=j+1;
1032: }
1033: else
1034: if (alterMe.charAt(j) == '<')
1035: {
1036: alterMe.setCharAt(j, '\\');
1037: alterMe.insert(j + 1, "<");
1038: length = length + 1;
1039: if(j<length-1) j=j+1;
1040: }
1041: else
1042: if (alterMe.charAt(j) == '?')
1043: {
1044: alterMe.setCharAt(j, '\\');
1045: alterMe.insert(j + 1, "?");
1046: length = length + 1;
1047: if(j<length-1) j=j+1;
1048: }
1049: else
1050: if (alterMe.charAt(j) == '&')
1051: {
1052: alterMe.setCharAt(j, '\\');
1053: alterMe.insert(j + 1, "&");
1054: length = length + 1;
1055: if(j<length-1) j=j+1;
1056: }
1057: else
1058: if (alterMe.charAt(j) == '=')
1059: {
1060: alterMe.setCharAt(j, '\\');
1061: alterMe.insert(j + 1, "=");
1062: length = length + 1;
1063: if(j<length-1) j=j+1;
1064: }
1065: else
1066: if (alterMe.charAt(j) == ',')
1067: {
1068: alterMe.setCharAt(j, '\\');
1069: alterMe.insert(j + 1, ",");
1070: length = length + 1;
1071: if(j<length-1) j=j+1;
1072: }
1073: else
1074: if (alterMe.charAt(j) == '.')
1075: {
1076: alterMe.setCharAt(j, '\\');
1077: alterMe.insert(j + 1, ".");
1078: length = length + 1;
1079: if(j<length-1) j=j+1;
1080: }
1081: else
1082: if (alterMe.charAt(j) == '[')
1083: {
1084: alterMe.setCharAt(j, '\\');
1085: alterMe.insert(j + 1, ".");
1086: length = length + 1;
1087: if(j<length-1) j=j+1;
1088: }
1089: else
1090: if (alterMe.charAt(j) == ']')
1091: {
1092: alterMe.setCharAt(j, '\\');
1093: alterMe.insert(j + 1, ".");
1094: length = length + 1;
1095: if(j<length-1) j=j+1;
1096: }
1097: else
1098: if (alterMe.charAt(j) == '%')
1099: {
1100: alterMe.setCharAt(j, '\\');
1101: alterMe.insert(j + 1, "%");
1102: length = length + 1;
1103: if(j<length-1) j=j+1;
1104: }*/
1105: ++j;
1106: }
1107: return alterMe.toString();
1108: }
1109: /**
1110: * parses the input xml file for batch conversion
1111: * called from readXMLFile
1112: * * @param sb
1113: */
1114: public static void parseXMLConfig(StringBuffer sb)
1115: {
1116: boolean finished = false;
1117: // parse string and build document tree
1118: Xparse parser = new Xparse();
1119: parser.changeEntities = true;
1120: Node root = parser.parse(sb.toString());
1121: // printContents(root);
1122: Vector databases = new Vector();
1123: Vector tables = new Vector();
1124: Vector layouts = new Vector();
1125: Vector selects = new Vector();
1126: Vector creates = new Vector();
1127: Vector ids = new Vector();
1128: String delimiter = "|";
1129: int mode = -1;
1130:
1131: try
1132: {
1133: Node tempNode = root.find("convert/source", new int[] { 1, 1 });
1134: if (tempNode == null)
1135: throw new Error("parse error source tag missing");
1136: System.out.println(tempNode.name);
1137: int length = countNodes(tempNode);
1138: for (int i = 1; i <= length; i++)
1139: {
1140:
1141: DBBean database = new DBBean();
1142: tables = new Vector();
1143: layouts = new Vector();
1144: selects = new Vector();
1145: creates = new Vector();
1146: ids = new Vector();
1147: // parse dataBase
1148: Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
1149: Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
1150: Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
1151: Node node3 = root.find("convert/source/database", new int[] { 1, 1, i });
1152: Node nodeMode = root.find("convert/source/database/mode", new int[] { 1, 1, i, 1, 1 });
1153: Node delimiterNode = root.find("convert/source/database/delimiter", new int[] { 1, 1, i, 1, 1 });
1154: Node useNormanToUnicodeMapper = root.find("convert/source/database/usenormantounicodemapper", new int[] { 1, 1, i, 1, 1 });
1155:
1156: if (node3 == null)
1157: throw new Error("parse error database tag missing");
1158: if (node == null)
1159: throw new Error("parse error url tag missing");
1160: if (node1 == null)
1161: throw new Error("parse error user tag missing");
1162: if (node2 == null)
1163: throw new Error("parse error password tag missing");
1164: if (delimiterNode != null)
1165: delimiter = delimiterNode.getCharacters();
1166: if(useNormanToUnicodeMapper!=null)
1167: {
1168: database.setUseNormanToUnicodeMapper(Boolean.valueOf(useNormanToUnicodeMapper.getCharacters()).booleanValue());
1169: System.out.println("useMapper "+Boolean.valueOf(useNormanToUnicodeMapper.getCharacters().trim()).booleanValue());
1170: }
1171:
1172: String url = node.getCharacters();
1173: String user = node1.getCharacters();
1174: String password = node2.getCharacters();
1175: database.setURL(url.trim());
1176: database.setUserAndPasswd(user.trim(), password.trim());
1177: System.out.println(node.name + " " + node.getCharacters());
1178: System.out.println(node1.name + " " + node1.getCharacters());
1179: System.out.println(node2.name + " " + node2.getCharacters());
1180: String modeString = "";
1181: if (nodeMode == null)
1182: modeString = "convert";
1183: else
1184: modeString = nodeMode.getCharacters();
1185: if (modeString.equals("convert"))
1186: mode = DataBase.CONVERT_MODE;
1187: else if (modeString.equals("append"))
1188: mode = DataBase.APPEND_MODE;
1189: else if (modeString.equals("update"))
1190: mode = DataBase.UPDATE_MODE;
1191: else if (modeString.equals("delete"))
1192: mode = DataBase.DELETE_MODE;
1193: else if (modeString.equals("synchronize"))
1194: mode = DataBase.SYNCHRONIZE_MODE;
1195:
1196: // if(node3!=null)
1197: // System.out.println(node3.name);
1198:
1199: int length2 = countNodes(node3);
1200:
1201: System.out.println("number of tables " + length2);
1202:
1203: for (int j = 1; j <= length2; ++j)
1204: {
1205: Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
1206: Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
1207: Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
1208: if (node4 != null)
1209: System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
1210: if (node5 != null)
1211: System.out.println(node5.name + " " + node5.getCharacters());
1212: if (node6 != null)
1213: System.out.println(node6.name + " " + node6.getCharacters());
1214: if (node4 == null)
1215: throw new Error("parse error table tag missing");
1216: // if(node5==null) throw new Error("parse error select tag missing");
1217: // if(node6==null) throw new Error("parse error create tag missing");
1218: String name = (String) node4.attributes.get("name");
1219: String layout = (String) node4.attributes.get("layout");
1220: String id = (String) node4.attributes.get("id");
1221: System.out.println("id was " + id);
1222: if (name == null)
1223: throw new Error("parse error required table tag attribute name missing");
1224: if (layout == null)
1225: layout = "";
1226: if (id == null)
1227: id = "";
1228: if (name.equals(""))
1229: throw new Error("parse error table tag attribute must not be empty");
1230: tables.add(name);
1231: layouts.add(layout);
1232: ids.add(id);
1233: String query = (node5 == null) ? "" : node5.getCharacters();
1234: if (query.equals(""))
1235: System.err.println("Warning empty select tag or select tag missing !!");
1236: query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
1237: selects.add(query);
1238: if (node6 != null)
1239: creates.add(node6.getCharacters().trim());
1240: else
1241: creates.add("");
1242:
1243: }
1244: DataBase dataBase = new DataBase(database, tables, layouts, selects, creates, ids, mode);
1245: dataBase.delimiter = delimiter;
1246: databases.add(dataBase);
1247: }
1248: DBBean database = new DBBean();
1249: // parse dataBase
1250: Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
1251: Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
1252: Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
1253: String url = node.getCharacters();
1254: String user = node1.getCharacters();
1255: String password = node2.getCharacters();
1256: System.out.println(url);
1257: database.setURL(url.trim());
1258: database.setUserAndPasswd(user.trim(), password.trim());
1259: //databases.add(database);
1260: for (Iterator iter = databases.iterator(); iter.hasNext();)
1261: {
1262: DataBase db = (DataBase) iter.next();
1263: if (mode == DataBase.CONVERT_MODE || mode == DataBase.APPEND_MODE)
1264: convertBatch(db.bean, database, db.tables, db.layouts, db.selects, db.creates, db.ids, mode, db.delimiter);
1265: else
1266: if (mode == DataBase.UPDATE_MODE)
1267: update(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates, db.ids, mode);
1268: else if(mode==DataBase.SYNCHRONIZE_MODE)
1269: {
1270: user =bean.user;
1271: passwd =bean.passwd;
1272: userDest =database.user;
1273: passwdDest =database.passwd;
1274:
1275: synchronize(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates, db.ids, mode);
1276: }
1277: }
1278: // printContents(node3);
1279: // FM2SQL.fmInstance=new FM2SQL();
1280: } catch (Exception e)
1281: {
1282:
1283: e.printStackTrace();
1284: }
1285: }
1286: public static Vector getXMLConfig(String xmlFile)
1287: {
1288: StringBuffer sb = null;
1289: try
1290: {
1291: // read XML Metadata from a file
1292: FileInputStream fi = new FileInputStream(xmlFile);
1293: InputStreamReader isr = new InputStreamReader(fi, "UTF-8");
1294: BufferedReader buffr = new BufferedReader(isr);
1295: sb = new StringBuffer();
1296: int c = 0;
1297: while ((c = buffr.read()) != -1)
1298: {
1299: char ch = (char) c;
1300: sb.append(ch);
1301: // System.out.print((char)c);
1302: }
1303:
1304: } catch (Exception e)
1305: {
1306: e.printStackTrace();
1307: }
1308:
1309: boolean finished = false;
1310: // parse string and build document tree
1311: Xparse parser = new Xparse();
1312: parser.changeEntities = true;
1313: Node root = parser.parse(sb.toString());
1314: // printContents(root);
1315: Vector databases = new Vector();
1316: Vector tables = new Vector();
1317: Vector layouts = new Vector();
1318: Vector selects = new Vector();
1319: Vector creates = new Vector();
1320: Vector ids = new Vector();
1321: String delimiter = "|";
1322: int mode = -1;
1323: try
1324: {
1325: Node tempNode = root.find("convert/source", new int[] { 1, 1 });
1326: if (tempNode == null)
1327: throw new Error("parse error source tag missing");
1328: System.out.println(tempNode.name);
1329: int length = countNodes(tempNode);
1330: for (int i = 1; i <= length; i++)
1331: {
1332:
1333: DBBean database = new DBBean();
1334: tables = new Vector();
1335: layouts = new Vector();
1336: selects = new Vector();
1337: creates = new Vector();
1338: ids = new Vector();
1339: // parse dataBase
1340: Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
1341: Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
1342: Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
1343: Node node3 = root.find("convert/source/database", new int[] { 1, 1, i });
1344: Node nodeMode = root.find("convert/source/database/mode", new int[] { 1, 1, i, 1, 1 });
1345: Node delimiterNode = root.find("convert/source/database/delimiter", new int[] { 1, 1, i, 1, 1 });
1346: Node useNormanToUnicodeMapper = root.find("convert/source/database/usenormantounicodemapper", new int[] { 1, 1, i, 1, 1 });
1347:
1348: if (delimiterNode != null)
1349: delimiter = delimiterNode.getCharacters();
1350: if(useNormanToUnicodeMapper!=null)
1351: {
1352: database.setUseNormanToUnicodeMapper(Boolean.valueOf(useNormanToUnicodeMapper.getCharacters()).booleanValue());
1353: System.out.println("useMapper "+Boolean.valueOf(useNormanToUnicodeMapper.getCharacters().trim()).booleanValue());
1354: }
1355:
1356: if (node3 == null)
1357: throw new Error("parse error database tag missing");
1358: if (node == null)
1359: throw new Error("parse error url tag missing");
1360: if (node1 == null)
1361: throw new Error("parse error user tag missing");
1362: if (node2 == null)
1363: throw new Error("parse error password tag missing");
1364: String url = node.getCharacters();
1365: String user = node1.getCharacters();
1366: String password = node2.getCharacters();
1367: database.setURL(url.trim());
1368: database.setUserAndPasswd(user.trim(), password.trim());
1369: System.out.println(node.name + " " + node.getCharacters());
1370: System.out.println(node1.name + " " + node1.getCharacters());
1371: System.out.println(node2.name + " " + node2.getCharacters());
1372: String modeString = "";
1373: if (nodeMode == null)
1374: modeString = "convert";
1375: else
1376: modeString = nodeMode.getCharacters();
1377: if (modeString.equals("convert"))
1378: mode = DataBase.CONVERT_MODE;
1379: else if (modeString.equals("append"))
1380: mode = DataBase.APPEND_MODE;
1381: else if (modeString.equals("update"))
1382: mode = DataBase.UPDATE_MODE;
1383: else if (modeString.equals("delete"))
1384: mode = DataBase.DELETE_MODE;
1385:
1386: // if(node3!=null)
1387: // System.out.println(node3.name);
1388:
1389: int length2 = countNodes(node3);
1390:
1391: System.out.println("number of tables " + length2);
1392:
1393: for (int j = 1; j <= length2; ++j)
1394: {
1395: Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
1396: Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
1397: Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
1398: if (node4 != null)
1399: System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
1400: if (node5 != null)
1401: System.out.println(node5.name + " " + node5.getCharacters());
1402: if (node6 != null)
1403: System.out.println(node6.name + " " + node6.getCharacters());
1404: if (node4 == null)
1405: throw new Error("parse error table tag missing");
1406: // if(node5==null) throw new Error("parse error select tag missing");
1407: // if(node6==null) throw new Error("parse error create tag missing");
1408: String name = (String) node4.attributes.get("name");
1409: String layout = (String) node4.attributes.get("layout");
1410: String id = (String) node4.attributes.get("id");
1411: System.out.println("id was " + id);
1412:
1413: if (name == null)
1414: throw new Error("parse error required table tag attribute name missing");
1415: if (layout == null)
1416: layout = "";
1417: if (id == null)
1418: id = "";
1419: if (name.equals(""))
1420: throw new Error("parse error table tag attribute must not be empty");
1421: tables.add(name);
1422: layouts.add(layout);
1423: ids.add(id);
1424: String query = (node5 == null) ? "" : node5.getCharacters();
1425: if (query.equals(""))
1426: System.err.println("Warning empty select tag or select tag missing !!");
1427: query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
1428: selects.add(query);
1429: if (node6 != null)
1430: creates.add(node6.getCharacters().trim());
1431: else
1432: creates.add("");
1433:
1434: }
1435: DataBase dataBase = new DataBase(database, tables, layouts, selects, creates, ids, mode);
1436: dataBase.delimiter = delimiter;
1437: databases.add(dataBase);
1438: }
1439: DBBean database = new DBBean();
1440: // parse dataBase
1441: Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
1442: Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
1443: Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
1444: String url = node.getCharacters();
1445: String user = node1.getCharacters();
1446: String password = node2.getCharacters();
1447: System.out.println(url);
1448: database.setURL(url.trim());
1449: database.setUserAndPasswd(user.trim(), password.trim());
1450: databases.add(new DataBase(database, null, null, null, null, null, 0));
1451: //databases.add(database);
1452: /* for (Iterator iter = databases.iterator(); iter.hasNext();)
1453: {
1454: DataBase db = (DataBase) iter.next();
1455: convertBatch(db.bean,database,db.tables,db.layouts,db.selects,db.creates);
1456:
1457: }*/
1458: // printContents(node3);
1459: // FM2SQL.fmInstance=new FM2SQL();
1460: } catch (Exception e)
1461: {
1462: // TODO Auto-generated catch block
1463: e.printStackTrace();
1464: }
1465: return databases;
1466: }
1467:
1468: private static int countNodes(Node tempNode)
1469: {
1470: int length = 0;
1471: for (int i = 0; i < tempNode.contents.v.size(); ++i)
1472: {
1473: Node node = (Node) tempNode.contents.v.elementAt(i);
1474: if (node.type.equals("element"))
1475: {
1476: if (node.name.equals("database"))
1477: length++;
1478: if (node.name.equals("table"))
1479: length++;
1480: }
1481:
1482: // System.out.println(((Node)tempNode.contents.v.elementAt(i)).attributes+" "+i);
1483: }
1484: return length;
1485: }
1486: private static void printContents(Node root)
1487: {
1488:
1489: Vector contents = (root.index == null) ? root.contents.v : root.index.v;
1490: for (int i = 0; i < contents.size(); ++i)
1491: {
1492: Node n = (Node) contents.elementAt(i);
1493: if (n.type.equals("element"))
1494: {
1495: System.out.println("tag " + n.name);
1496: System.out.println(n.getCharacters());
1497: //contents=n.contents.v i=0;
1498: }
1499: // System.out.println(n.type);
1500: }
1501: }
1502: /**
1503: * reads the specified xml file
1504: * @param xmlFile
1505: */
1506: public static void readXMLFile(String xmlFile)
1507: {
1508: try
1509: {
1510: InputStream stream = null;
1511:
1512: if(xmlFile.indexOf("file://")>=0||xmlFile.indexOf("http://")>=0)
1513: {
1514: URL url=new URL(xmlFile);
1515: stream = url.openStream();
1516: }
1517: else
1518: // read XML Metadata from a file
1519: stream = new FileInputStream(xmlFile);
1520: InputStreamReader isr = new InputStreamReader(stream, "UTF-8");
1521: BufferedReader buffr = new BufferedReader(isr);
1522: StringBuffer sb = new StringBuffer();
1523: int c = 0;
1524: while ((c = buffr.read()) != -1)
1525: {
1526: char ch = (char) c;
1527: sb.append(ch);
1528: // System.out.print((char)c);
1529: }
1530: parseXMLConfig(sb);
1531: } catch (Exception e)
1532: {
1533: e.printStackTrace();
1534: }
1535: }
1536: /**
1537: * Helper class for Conversion etc
1538: * Holds the some data
1539: * @author rogo
1540: *
1541: */
1542:
1543: public static class ConversionProperties
1544: {
1545: String destTableName;
1546: String[] fieldNames ;
1547: public ConversionProperties()
1548: {
1549: }
1550: public ConversionProperties(String destTableName, String[] fieldNames)
1551: {
1552: this.destTableName = destTableName;
1553: this.fieldNames = fieldNames;
1554:
1555: }
1556:
1557: }
1558: /**
1559: * Helper class for XML-File parsing
1560: * Holds the parsed data
1561: * @author rogo
1562: *
1563: */
1564: public static class DataBase
1565: {
1566: DBBean bean;
1567: Vector creates;
1568: Vector selects;
1569: Vector layouts;
1570: Vector tables;
1571: Vector ids;
1572: String delimiter = "//";
1573: boolean useNormanToUnicodeMapper = false;
1574:
1575: final static int CONVERT_MODE = 1;
1576: final static int APPEND_MODE = 2;
1577: final static int UPDATE_MODE = 3;
1578: final static int DELETE_MODE = 4;
1579: final static int SYNCHRONIZE_MODE = 5;
1580:
1581: int mode = -1;
1582:
1583: public DataBase(DBBean bean, Vector tables, Vector layouts, Vector selects, Vector creates, Vector ids, int mode)
1584: {
1585: this.bean = bean;
1586: this.tables = tables;
1587: this.layouts = layouts;
1588: this.selects = selects;
1589: this.creates = creates;
1590: this.ids = ids;
1591: this.mode = mode;
1592: this.bean.setIDVector(ids);
1593: }
1594: /**
1595: * writes the data contained in this object to the buffered writer
1596: * * @param buffr
1597: * @throws Exception
1598: */
1599: public void exportToXML(BufferedWriter buffr) throws Exception
1600: {
1601: // ids=bean.getIDVector();
1602: buffr.write(" <database>\n");
1603: buffr.write(" <url>" + bean.url + "</url>\n");
1604: buffr.write(" <user>" + bean.user + "</user>\n");
1605: buffr.write(" <password>" + bean.passwd + "</password>\n");
1606: buffr.write(" <delimiter>" + delimiter + "</delimiter>\n");
1607: String modeString = "";
1608: if (mode == CONVERT_MODE)
1609: modeString = "convert";
1610: else if (mode == APPEND_MODE)
1611: modeString = "append";
1612: else if (mode == UPDATE_MODE)
1613: modeString = "update";
1614: else if (mode == DELETE_MODE)
1615: modeString = "delete";
1616:
1617: buffr.write(" <mode>" + modeString + "</mode>\n");
1618: buffr.write(" <usenormantounicodemapper>" + useNormanToUnicodeMapper + "</usenormantounicodemapper>\n");
1619:
1620: int index = 0;
1621: while (index < tables.size())
1622: {
1623: String table = (String) tables.get(index);
1624: String layout = (String) layouts.get(index);
1625: String select = (String) selects.get(index);
1626: String create = (String) creates.get(index);
1627: String id = (String) ids.get(index);
1628:
1629: buffr.write(" <table name = \"" + table + "\" layout = \"" + layout + "\" id = \"" + id + "\" >\n");
1630: buffr.write(" <select>" + convertToEntities(select) + "</select>\n");
1631: if (!create.equals(""))
1632: buffr.write(" <create>" + create + " </create>\n");
1633: buffr.write(" </table>\n");
1634: index++;
1635: }
1636: buffr.write(" </database>\n");
1637: }
1638: public String toString()
1639: {
1640: return bean.url + " " + tables;
1641: }
1642:
1643: }
1644: public static String convertToUTF8(Object command)
1645: {
1646: String str = null;
1647: try
1648: {
1649: str = new String(command.toString().getBytes("UTF-8"));
1650: } catch (UnsupportedEncodingException e)
1651: {
1652: // TODO Auto-generated catch block
1653: e.printStackTrace();
1654: }
1655: return str;
1656: }
1657: public static void writeConfig(String file, DataBase source, DataBase destination) throws Exception
1658: {
1659: if (!file.toLowerCase().endsWith(".xml"))
1660: file += ".xml";
1661: File f = new File(file);
1662:
1663: FileOutputStream fout = new FileOutputStream(f);
1664: OutputStreamWriter outsw = new OutputStreamWriter(fout, "UTF-8");
1665: BufferedWriter buffw = new BufferedWriter(outsw);
1666: buffw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
1667: buffw.newLine();
1668: buffw.write("<convert>\n");
1669: buffw.write(" <source>\n");
1670: source.exportToXML(buffw);
1671: buffw.write(" </source>\n");
1672: buffw.write("\n <destination>\n");
1673: destination.exportToXML(buffw);
1674: buffw.write(" </destination>\n");
1675: buffw.write("</convert>\n");
1676: buffw.close();
1677: }
1678: public static void delete(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
1679: {
1680: FM2SQL.ProgressDialog dialog = null;
1681: if (FM2SQL.fmInstance != null)
1682: {
1683: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
1684: dialog.setTitle("Conversion running ...");
1685: dialog.title.setText("Getting table data ...");
1686: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
1687: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
1688: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
1689: dialog.thread = Thread.currentThread();
1690: }
1691: // setting user and passwd
1692: bean.setUserAndPasswd(user, passwd);
1693: // setting user and passwd
1694: beanDest.setUserAndPasswd(userDest, passwdDest);
1695: if (dialog != null)
1696: dialog.setSize(400, 250);
1697: StringBuffer command = null;
1698: String query = null;
1699: try
1700: {
1701: //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
1702: //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
1703: bean.setConnection(source);
1704: if (names == null)
1705: names = bean.getTableNames();
1706: // Collections.sort(names);
1707: int tbIndex = 1;
1708:
1709: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
1710: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
1711: {
1712: Vector[] result = null;
1713: java.util.TreeSet myIds = new TreeSet();
1714: java.util.TreeSet myIdsDest = new TreeSet();
1715: int deltaID = 1;
1716: String idField = "";
1717: String destTableName = "";
1718:
1719: try
1720: {
1721: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
1722: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
1723: query = (selects != null) ? selects.get(tbIndex).toString() : query;
1724: //if vectors[1].get(i) != null)
1725: if (layout != "")
1726: {
1727: layout = " layout " + bean.getQC() + layout + bean.getQC();
1728: String name = names.get(tbIndex).toString();
1729: StringBuffer queryLayout = new StringBuffer(query);
1730: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
1731: query = queryLayout.toString();
1732: System.out.println("added layout " + query);
1733:
1734: }
1735: dialog.title.setText("Getting table data ...");
1736: dialog.table.setText(names.get(tbIndex).toString());
1737: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
1738: dialog.show();
1739: bean.getConnection();
1740: bean.makeQuery(query, 50);
1741: idField = ids.get(tbIndex).toString();
1742:
1743: } catch (Exception e)
1744: {
1745: continue;
1746: }
1747: // determine destTableName from createStatement or from source table name
1748: if (!creates.get(tbIndex).equals(""))
1749: {
1750: String create = creates.get(tbIndex).toString().toLowerCase();
1751: int fromIndex = create.indexOf("table") + 5;
1752: int toIndex = create.indexOf("(");
1753: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
1754: System.out.println("destTable " + destTableName);
1755:
1756: } else
1757: destTableName = convertText(names.get(tbIndex).toString());
1758:
1759: // for id kram
1760: Vector vec = null;
1761: Vector vecDest = null;
1762: // tempo
1763: beanDest.setConnection(destination);
1764: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
1765: String tempID = bean.getQC() + idField + bean.getQC();
1766: String tempIDdest = beanDest.getQC() + convertText(idField) + beanDest.getQC();
1767:
1768: int endIndex = -1;
1769: String tempQuery = query;
1770: String destQuery = query.replaceAll(names.get(tbIndex).toString(), destTableName);
1771: String tempQueryDest = destQuery;
1772: // remove extra query parts destQuery.substring(0,destQuery.lastIndexOf(destTableName)+destTableName.length()+1);
1773: System.out.println("new Query " + tempQueryDest);
1774: if (!idField.equals(""))
1775: {
1776: long startTime = System.currentTimeMillis();
1777: int counter = -1;
1778: while (true)
1779: {
1780: ++counter;
1781: if (counter == 0 && dialog != null)
1782: dialog.title.setText("Check if data is available");
1783: else if (dialog != null)
1784: dialog.title.setText("Check if more data is available");
1785: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, numHits);
1786: myIdsDest = beanDest.getIDVector(convertText(idField), destTableName, tempQueryDest, numHits);
1787: if (myIds.isEmpty())
1788: break;
1789: vec = new Vector(myIds);
1790: vecDest = new Vector(myIdsDest);
1791: rowCount = vec.size();
1792: // Deletion will work this way
1793: Vector deleted = new Vector(vec);
1794: Vector linesToDelete = new Vector(vecDest);
1795: // remove all lines that should not be deleted
1796: linesToDelete.removeAll(deleted);
1797: // System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIdsDest.size());
1798: /// @TODO complete delete task remove query show lines to be deleted let user choose if he wants that
1799: System.out.println("number of lines to be deleted " + linesToDelete.size());
1800: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
1801: beanDest.setConnection(destination);
1802:
1803: Statement stm = beanDest.getConnection().createStatement();
1804:
1805: Vector tables = beanDest.getTableNames();
1806: // Collections.sort(tables);
1807: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
1808: tables = beanDest.getTableNames();
1809: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
1810: stm = beanDest.getConnection().createStatement();
1811:
1812: if (dialog != null)
1813: dialog.title.setText(" Deleting table data ...");
1814:
1815: int j = -1;
1816:
1817: Vector row = null;
1818: command = new StringBuffer();
1819:
1820: command.append("DELETE FROM");
1821: command.append(beanDest.getQC());
1822: command.append(destTableName);
1823: //command.append(convertText((String) names.get(tbIndex)));
1824: command.append(beanDest.getQC());
1825: int size = bean.getColumnNames().size();
1826: command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
1827: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
1828: System.out.println(command + " " + tbIndex);
1829: //int rowCount = bean.getRowCount(query);
1830: // int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
1831: while (true)
1832: {
1833:
1834: ++j;
1835: if (j == linesToDelete.size())
1836: break;
1837: //print rows
1838: pstm.setString(1, linesToDelete.get(j).toString());
1839: System.out.println(pstm.toString());
1840: pstm.execute();
1841: if (dialog != null)
1842: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
1843: command = null;
1844: }
1845: // prepare new query for next chunk
1846: if (query.indexOf("where") > 0)
1847: tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
1848: else
1849: tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
1850:
1851: } //to outer while
1852: } // to idfield if
1853: } // table loop
1854:
1855: } catch (Exception e)
1856: {
1857: System.out.println("Error while connecting to database " + e);
1858: if (dialog != null)
1859: {
1860: dialog.setVisible(false);
1861: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1862: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1863: }
1864: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
1865: java.io.PrintStream stream = new java.io.PrintStream(b);
1866: stream.print(command + "\n\n");
1867: e.printStackTrace(stream);
1868: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
1869:
1870: }
1871: if (dialog != null)
1872: {
1873: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1874: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1875:
1876: dialog.setVisible(false);
1877: }
1878: } // to method
1879:
1880:
1881: /**
1882: * synchronize method based on delete method code
1883: *
1884: * @param source
1885: * @param destination
1886: * @param names
1887: * @param layouts
1888: * @param selects
1889: * @param creates
1890: * @param ids
1891: * @param mode
1892: * @throws Exception
1893: */
1894: // TODO implement append,update and delete in one method
1895: // TODO using id based algorithm
1896: public static void synchronize(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
1897: {
1898: System.out.println(" bin in synchronize!!!");
1899: FM2SQL.ProgressDialog dialog = null;
1900: if (FM2SQL.fmInstance != null)
1901: {
1902: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
1903: dialog.setTitle("Synchronize running ...");
1904: dialog.title.setText("Getting table data ...");
1905: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
1906: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
1907: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
1908: dialog.thread = Thread.currentThread();
1909: }
1910: // setting user and passwd
1911: bean.setUserAndPasswd(user, passwd);
1912: // setting user and passwd
1913: beanDest.setUserAndPasswd(userDest, passwdDest);
1914: if (dialog != null)
1915: dialog.setSize(400, 250);
1916: StringBuffer command = null;
1917: String query = null;
1918: try
1919: {
1920: //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
1921: //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
1922: bean.setConnection(source);
1923: if (names == null)
1924: names = bean.getTableNames();
1925: // Collections.sort(names);
1926: int tbIndex = 1;
1927:
1928: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
1929: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
1930: {
1931: Vector[] result = null;
1932: java.util.TreeSet myIds = new TreeSet();
1933: java.util.TreeSet myIdsDest = new TreeSet();
1934: int deltaID = 1;
1935: String idField = "";
1936: String destTableName = "";
1937:
1938: try
1939: {
1940: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
1941: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
1942: query = (selects != null) ? selects.get(tbIndex).toString() : query;
1943: //if vectors[1].get(i) != null)
1944: if (!layout.equals(""))
1945: {
1946: layout = " layout " + bean.getQC() + layout + bean.getQC();
1947: String name = names.get(tbIndex).toString();
1948: StringBuffer queryLayout = new StringBuffer(query);
1949: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
1950: query = queryLayout.toString();
1951: System.out.println("added layout " + query);
1952:
1953: }
1954: if(dialog!=null) {
1955: dialog.title.setText("Getting table data ...");
1956: dialog.table.setText(names.get(tbIndex).toString());
1957: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
1958: dialog.show();
1959: }
1960: bean.getConnection();
1961: bean.makeQuery(query, 50);
1962: idField = ids.get(tbIndex).toString();
1963:
1964: } catch (Exception e)
1965: {
1966: continue;
1967: }
1968: // determine destTableName from createStatement or from source table name
1969: if (!creates.get(tbIndex).equals(""))
1970: {
1971: String create = creates.get(tbIndex).toString().toLowerCase();
1972: int fromIndex = create.indexOf("table") + 5;
1973: int toIndex = create.indexOf("(");
1974: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
1975: System.out.println("destTable " + destTableName);
1976:
1977: } else
1978: destTableName = convertText(names.get(tbIndex).toString());
1979:
1980: // for id kram
1981: Vector vec = null;
1982: Vector vecDest = null;
1983: // tempo
1984: beanDest.setConnection(destination);
1985: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
1986: String tempID = bean.getQC() + idField + bean.getQC();
1987: String tempIDdest = beanDest.getQC() + convertText(idField) + beanDest.getQC();
1988:
1989: int endIndex = -1;
1990: String tempQuery = query;
1991: String destQuery = query.replaceAll(names.get(tbIndex).toString(), destTableName);
1992: String tempQueryDest = destQuery;
1993: // remove extra query parts destQuery.substring(0,destQuery.lastIndexOf(destTableName)+destTableName.length()+1);
1994: System.out.println("new Query " + tempQueryDest);
1995: if (!idField.equals(""))
1996: {
1997: long startTime = System.currentTimeMillis();
1998: int counter = -1;
1999: while (true)
2000: {
2001: ++counter;
2002: if (counter == 0 && dialog != null)
2003: dialog.title.setText("Check if data is available");
2004: else if (dialog != null)
2005: dialog.title.setText("Check if more data is available");
2006: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, numHits);
2007: myIdsDest = beanDest.getIDVector(convertText(idField), destTableName, tempQueryDest, numHits);
2008: if (myIds.isEmpty())
2009: break;
2010: vec = new Vector(myIds);
2011: vecDest = new Vector(myIdsDest);
2012: rowCount = vec.size();
2013: // Deletion will work this way
2014: Vector deleted = new Vector(vec);
2015: Vector linesToDelete = new Vector(vecDest);
2016:
2017: TreeSet linesToAppend = new TreeSet(vec);
2018: // remove all lines that are already in dest database
2019: linesToAppend.removeAll(vecDest);
2020: // remove all lines that should not be deleted
2021: linesToDelete.removeAll(deleted);
2022: System.out.println("linesToAppend "+linesToAppend+" "+vecDest+" "+destTableName);
2023: System.out.println("linesToDelete "+linesToDelete+" "+vecDest+" "+destTableName);
2024: // prepare new query for next chunk
2025: if (query.indexOf("where") > 0)
2026: tempQuery = query + " and " + tempID + ">='" + vec.firstElement() + "' and "+tempID + "<='" + vec.lastElement() + "' order by "+tempID;
2027: else
2028: tempQuery = query + " where " + tempID + ">='" + vec.firstElement() + "' and "+tempID + "<='" + vec.lastElement() + "'";// order by "+tempID;
2029: System.out.println("tempQuery is now "+tempQuery+" order by "+tempID);
2030: // bean.makeQuery(tempQuery,vec.size());
2031: Vector[] vectors=bean.getQueryData(tempQuery,vec.size());
2032: int idIndex = vectors[1].indexOf(idField);
2033: for (Iterator iter = vectors[0].iterator(); iter.hasNext();)
2034: {
2035: Vector line = (Vector) iter.next();
2036: System.out.println("line "+linesToAppend.contains(line.get(idIndex)));
2037: }
2038: System.out.println( );
2039: // System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIdsDest.size());
2040: /// @TODO complete delete task remove query show lines to be deleted let user choose if he wants that
2041: System.out.println("number of lines to be deleted " + linesToDelete.size());
2042: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
2043: System.out.println("deltaID "+deltaID);
2044: System.exit(0);
2045:
2046: beanDest.setConnection(destination);
2047:
2048: Statement stm = beanDest.getConnection().createStatement();
2049:
2050: Vector tables = beanDest.getTableNames();
2051: // Collections.sort(tables);
2052: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
2053: tables = beanDest.getTableNames();
2054: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
2055: stm = beanDest.getConnection().createStatement();
2056:
2057: if (dialog != null)
2058: dialog.title.setText(" Deleting table data ...");
2059:
2060: int j = -1;
2061:
2062: Vector row = null;
2063: command = new StringBuffer();
2064:
2065: command.append("DELETE FROM");
2066: command.append(beanDest.getQC());
2067: command.append(destTableName);
2068: //command.append(convertText((String) names.get(tbIndex)));
2069: command.append(beanDest.getQC());
2070: int size = bean.getColumnNames().size();
2071: command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
2072: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
2073: System.out.println(command + " " + tbIndex);
2074: //int rowCount = bean.getRowCount(query);
2075: // int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
2076: while (true)
2077: {
2078:
2079: ++j;
2080: if (j == linesToDelete.size())
2081: break;
2082: //print rows
2083: pstm.setString(1, linesToDelete.get(j).toString());
2084: System.out.println(pstm.toString());
2085: pstm.execute();
2086: if (dialog != null)
2087: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
2088: command = null;
2089: }
2090: // prepare new query for next chunk
2091: if (query.indexOf("where") > 0)
2092: tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
2093: else
2094: tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
2095:
2096: } //to outer while
2097: } // to idfield if
2098: } // table loop
2099:
2100: } catch (Exception e)
2101: {
2102: System.out.println("Error while connecting to database " + e);
2103: if (dialog != null)
2104: {
2105: dialog.setVisible(false);
2106: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
2107: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
2108: }
2109: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
2110: java.io.PrintStream stream = new java.io.PrintStream(b);
2111: stream.print(command + "\n\n");
2112: e.printStackTrace(stream);
2113: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
2114:
2115: }
2116: if (dialog != null)
2117: {
2118: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
2119: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
2120:
2121: dialog.setVisible(false);
2122: }
2123: } // to method
2124:
2125:
2126:
2127: /**
2128: * Converts input String in norman encoding to unicode
2129: * @param inp
2130: * @return converted String
2131: */
2132: static public String normanToUnicode(String inp) {
2133: StringBuffer buf = new StringBuffer();
2134: for (int i = 0; i < inp.length(); i++) {
2135: char c = inp.charAt(i);
2136: // System.out.println("char "+c+" "+(int)c);
2137: switch (c) {
2138: case 1: buf.append("\u00d0"); break; // Eth
2139: case 2: buf.append("\u00f0"); break; // eth
2140: case 3: buf.append("\u0141"); break; // Lslash
2141: case 4: buf.append("\u0142"); break; // lslash
2142: case 5: buf.append("\u0160"); break; // S caron
2143: case 6: buf.append("\u0161"); break; // s caron
2144: case 7: buf.append("\u00dd"); break; // Y acute
2145: case 8: buf.append("\u00fd"); break; // y acute
2146: case 11: buf.append("\u00de"); break; // Thorn
2147: case 12: buf.append("\u00fe"); break; // thorn
2148: case 14: buf.append("\u017d"); break; // Z caron
2149: case 15: buf.append("\u017e"); break; // z caron
2150: case 17: buf.append("\u0073"); break; // asciitilde
2151: case 18: buf.append("j\u0305"); break; // j macron [does a single char exist?]
2152: case 19: buf.append("^"); break; // circumflex
2153: case 20: buf.append("\u0303"); break; // tilde
2154: case 21: buf.append("\u00bd"); break; // onehalf
2155: case 22: buf.append("\u00bc"); break; // onequarter
2156: case 23: buf.append("\u00b9"); break; // onesuperior
2157: case 24: buf.append("\u00be"); break; // threequarters
2158: case 25: buf.append("\u00b3"); break; // threesuperior
2159: case 26: buf.append("\u00b2"); break; // twosuperior
2160: case 27: buf.append("\u00a6"); break; // brokenbar
2161: case 28: buf.append("-"); break; // minus
2162: case 29: buf.append("\u00d7"); break; // multiply
2163: case 39: buf.append("'"); break; // quotesingle
2164: case 94: buf.append("\u0302"); break; // circumflex
2165: case 96: buf.append("\u0300"); break; // grave
2166: case 196: buf.append("\u00c4"); break; // A dieresis
2167: case 197: buf.append("\u00c5"); break; // A ring
2168: case 201: buf.append("\u00c9"); break; // E acute
2169: case 209: buf.append("\u00d1"); break; // N tilde
2170: case 214: buf.append("\u00d6"); break; // O dieresis
2171: case 220: buf.append("\u00dc"); break; // U dieresis
2172: case 225: buf.append("\u00e1"); break; // a acute
2173: case 224: buf.append("\u00e0"); break; // a grave
2174: case 226: buf.append("\u00e2"); break; // a circumflex
2175: case 228: buf.append("\u00e4"); break; // a dieresis
2176: case 227: buf.append("\u00e3"); break; // a tilde
2177: case 229: buf.append("\u0101"); break; // a macron
2178: case 231: buf.append("\u00e7"); break; // c cedilla
2179: case 233: buf.append("\u00e9"); break; // e acute
2180: case 232: buf.append("\u00e8"); break; // e grave
2181: case 234: buf.append("\u00ea"); break; // e circumflex
2182: case 235: buf.append("\u00eb"); break; // e dieresis
2183: case 237: buf.append("\u00ed"); break; // i acute
2184: case 236: buf.append("\u00ec"); break; // i grave
2185: case 238: buf.append("\u00ee"); break; // i circumflex
2186: case 239: buf.append("\u00ef"); break; // i dieresis
2187: case 241: buf.append("\u00f1"); break; // n tilde
2188: case 243: buf.append("\u00f3"); break; // o acute
2189: case 242: buf.append("\u00f2"); break; // o grave
2190: case 244: buf.append("\u00f4"); break; // o circumflex
2191: case 246: buf.append("\u00f6"); break; // o dieresis
2192: case 245: buf.append("\u00f5"); break; // o tilde
2193: case 250: buf.append("\u00fa"); break; // u acute
2194: case 249: buf.append("\u00f9"); break; // u grave
2195: case 251: buf.append("\u00fb"); break; // u circumflex
2196: case 252: buf.append("\u00fc"); break; // u dieresis
2197: case 8224: buf.append("\u1e6d"); break; // t underdot
2198: case 176: buf.append("\u00b0"); break; // degree
2199: case 162: buf.append("\u1ebd"); break; // e tilde
2200: case 163: buf.append("\u00a3"); break; // sterling
2201: case 167: buf.append("\u00a7"); break; // section
2202: case 182: buf.append("\u00b6"); break; // paragraph
2203: case 223: buf.append("\u015b"); break; // s acute
2204: case 174: buf.append("\u1e5b"); break; // r underdot
2205: case 169: buf.append("\u1e45"); break; // n overdot
2206: case 353: buf.append("\u1e45"); break; // n overdot
2207: case 180: buf.append("\u0301"); break; // acute
2208: case 168: buf.append("\u0308"); break; // dieresis
2209: case 8800: buf.append("\u1e6d"); break; // t underdot
2210: case 198: buf.append("\u00c6"); break; // AE
2211: case 216: buf.append("\u014d"); break; // o macron
2212: case 8734: buf.append("\u0129"); break; // i tilde
2213: case 177: buf.append("\u00b1"); break; // plusminus
2214: case 165: buf.append("\u012b"); break; // i macron
2215: case 181: buf.append("\u1e43"); break; // m underdot
2216: case 8706: buf.append("\u1e0d"); break; // d underdot
2217: case 240: buf.append("\u1e0d"); break; // d underdot
2218:
2219: case 8721: buf.append("\u1e63"); break; // s underdot
2220: case 960: buf.append("\u017a"); break; // z acute
2221: case 8747: buf.append("\u1e45"); break; // n overdot
2222: case 937: buf.append("\u0169"); break; // u tilde
2223: case 230: buf.append("\u00e6"); break; // ae
2224: case 248: buf.append("\u00f8"); break; // oslash
2225: case 191: buf.append("\u0304\u0306"); break; // macron breve
2226: case 172: buf.append("\u1e37"); break; //
2227: case 8730: buf.append("j\u0305"); break; // j macron [does a single char exist?]
2228: case 402: buf.append("\u0103"); break; // a breve
2229: case 8776: buf.append("\u016d"); break; // u breve
2230: case 187: buf.append("\u1e42"); break; // M underdot
2231: case 8230: buf.append("\u2026"); break; // ellipsis
2232: case 192: buf.append("\u00c0"); break; // A grave
2233: case 195: buf.append("\u00c3"); break; // A tilde
2234: case 213: buf.append("\u00d5"); break; // O tilde
2235: case 338: buf.append("m\u0306"); break; // m breve
2236: case 339: buf.append("\u0153"); break; // oe
2237: case 8211: buf.append("\u2013"); break; // endash
2238: case 8212: buf.append("\u2014"); break; // emdash
2239: case 8220: buf.append("\u201c"); break; // quotedblleft
2240: case 8221: buf.append("\u201d"); break; // quotedblright
2241: case 8216: buf.append("\u2018"); break; // quoteleft
2242: case 8217: buf.append("\u2019"); break; // quoteright
2243: case 247: buf.append("\u1e37"); break; // l underring [actually underdot]
2244: case 9674: buf.append("\u1e41"); break; // m overdot
2245: case 255: buf.append("n\u0306"); break; // n breve
2246: case 376: buf.append("\u00d7"); break; // multiply
2247: case 8364: buf.append("\u1e5b"); break; // r underring [actually underdot]
2248: case 8249: buf.append("\u1e44"); break; // N overdot
2249: case 8250: buf.append("\u1e62"); break; // S underdot
2250: case 64257: buf.append("\u1e24"); break; // H underdot
2251: case 64258: buf.append("\u1e0c"); break; // D underdot
2252: case 8225: buf.append("\u2021"); break; // daggerdbl
2253: case 8218: buf.append("\u1e36"); break; // L underdot
2254: case 8222: buf.append("\u0113"); break; // e macron
2255: case 194: buf.append("\u1e5f"); break; // r underbar
2256: case 202: buf.append("r\u0324"); break; // r underdieresis
2257: case 193: buf.append("\u012a"); break; // I macron
2258: case 8486:
2259: case 203: buf.append("\u016b"); break; // u macron
2260: case 200: buf.append("\u1e6c"); break; // T underdot
2261: case 205: buf.append("\u1e64"); break; // S acute
2262: case 206: buf.append("\u2020"); break; // dagger
2263: case 207: buf.append("\u0115"); break; // e breve
2264: case 204: buf.append("\u014f"); break; // o breve
2265: case 211: buf.append("\u0100"); break; // A macron
2266: case 212: buf.append("\u1e46"); break; // N underdot
2267: case 210: buf.append("\u1e3b"); break; // l underbar
2268: case 218: buf.append("\u016a"); break; // U macron
2269: case 219: buf.append("\u0179"); break; // Z acute
2270: case 217: buf.append("\u1e5a"); break; // R underdot
2271: case 305: buf.append("\u0131"); break; // dotlessi
2272: case 710: buf.append("\u1e47"); break; // n underdot
2273: case 732: buf.append("\u1e49"); break; // n underbar
2274: case 175: buf.append("\u0304"); break; // macron
2275: case 728: buf.append("\u0306"); break; // breve
2276: case 729:case 215: buf.append("\u1e25"); break; // h underdot
2277: case 730: buf.append("\u012d"); break; // i breve
2278: case 184: buf.append("\u0327"); break; // cedilla
2279: case 733: buf.append("\u030b"); break; // hungarumlaut
2280: case 731: buf.append("\u0328"); break; // ogonek
2281: case 711: buf.append("\u030c"); break; // caron
2282: case 199: buf.append("\u012b\u0303"); break; // imacron tilde
2283: case 8226: buf.append("\u1e5d"); break; // runderdot macron
2284: case 8482: buf.append("\u016b\0306"); break; // umacron breve
2285: case 8804: buf.append("\u0101\u0301"); break; // amacron acute
2286: case 8805: buf.append("\u016b\u0301"); break; // umacron acute
2287: case 8719: buf.append("\u0113\u0301"); break; // emacron acute
2288: case 170: buf.append("\u0113\u0300"); break; // emacron breve
2289: case 186: buf.append("\u014d\u0300"); break; // omacron breve
2290: case 161: buf.append("\u0101\u0306"); break; // amacron breve
2291: case 8710: buf.append("\u0101\u0303"); break; // amacron tilde
2292: case 171: buf.append("\u012b\u0301"); break; // imacron acute
2293: case 8260: buf.append("\u1e00"); break; // runderdotmacron acute
2294: case 183: buf.append("\u1e5b\u0301"); break; // runderdot acute
2295: case 8240: buf.append("\u012b\u0306"); break; // imacron breve
2296: case 63743: buf.append("\u016b\u0303"); break; // umacron tilde
2297: default: buf.append(c);
2298: if((int)c>127)
2299: System.out.println("char "+c+" "+(int)c);
2300: break;
2301: }
2302: }
2303: return buf.toString();
2304: }
2305:
2306: static public String normanToUnicodeOld(String inp) {
2307: StringBuffer buf = new StringBuffer();
2308: for (int i = 0; i < inp.length(); i++) {
2309: char c = inp.charAt(i);
2310: switch (c) {
2311: case 1: buf.append("\u00d0"); break; // Eth
2312: case 2: buf.append("\u00f0"); break; // eth
2313: case 3: buf.append("\u0141"); break; // Lslash
2314: case 4: buf.append("\u0142"); break; // lslash
2315: case 5: buf.append("\u0160"); break; // S caron
2316: case 6: buf.append("\u0161"); break; // s caron
2317: case 7: buf.append("\u00dd"); break; // Y acute
2318: case 8: buf.append("\u00fd"); break; // y acute
2319: case 11: buf.append("\u00de"); break; // Thorn
2320: case 12: buf.append("\u00fe"); break; // thorn
2321: case 14: buf.append("\u017d"); break; // Z caron
2322: case 15: buf.append("\u017e"); break; // z caron
2323: case 17: buf.append("\u0073"); break; // asciitilde
2324: case 18: buf.append("j\u0305"); break; // j macron [does a single char exist?]
2325: case 19: buf.append("^"); break; // circumflex
2326: case 20: buf.append("\u0303"); break; // tilde
2327: case 21: buf.append("\u00bd"); break; // onehalf
2328: case 22: buf.append("\u00bc"); break; // onequarter
2329: case 23: buf.append("\u00b9"); break; // onesuperior
2330: case 24: buf.append("\u00be"); break; // threequarters
2331: case 25: buf.append("\u00b3"); break; // threesuperior
2332: case 26: buf.append("\u00b2"); break; // twosuperior
2333: case 27: buf.append("\u00a6"); break; // brokenbar
2334: case 28: buf.append("-"); break; // minus
2335: case 29: buf.append("\u00d7"); break; // multiply
2336: case 39: buf.append("'"); break; // quotesingle
2337: case 94: buf.append("\u0302"); break; // circumflex
2338: case 96: buf.append("\u0300"); break; // grave
2339: case 128: buf.append("\u00c4"); break; // A dieresis
2340: case 129: buf.append("\u00c5"); break; // A ring
2341: case 131: buf.append("\u00c9"); break; // E acute
2342: case 132: buf.append("\u00d1"); break; // N tilde
2343: case 133: buf.append("\u00d6"); break; // O dieresis
2344: case 134: buf.append("\u00dc"); break; // U dieresis
2345: case 135: buf.append("\u00e1"); break; // a acute
2346: case 136: buf.append("\u00e0"); break; // a grave
2347: case 137: buf.append("\u00e2"); break; // a circumflex
2348: case 138: buf.append("\u00e4"); break; // a dieresis
2349: case 139: buf.append("\u00e3"); break; // a tilde
2350: case 140: buf.append("\u0101"); break; // a macron
2351: case 141: buf.append("\u00e7"); break; // c cedilla
2352: case 142: buf.append("\u00e9"); break; // e acute
2353: case 143: buf.append("\u00e8"); break; // e grave
2354: case 144: buf.append("\u00ea"); break; // e circumflex
2355: case 145: buf.append("\u00eb"); break; // e dieresis
2356: case 146: buf.append("\u00ed"); break; // i acute
2357: case 147: buf.append("\u00ec"); break; // i grave
2358: case 148: buf.append("\u00ee"); break; // i circumflex
2359: case 149: buf.append("\u00ef"); break; // i dieresis
2360: case 150: buf.append("\u00f1"); break; // n tilde
2361: case 151: buf.append("\u00f3"); break; // o acute
2362: case 152: buf.append("\u00f2"); break; // o grave
2363: case 153: buf.append("\u00f4"); break; // o circumflex
2364: case 154: buf.append("\u00f6"); break; // o dieresis
2365: case 155: buf.append("\u00f5"); break; // o tilde
2366: case 156: buf.append("\u00fa"); break; // u acute
2367: case 157: buf.append("\u00f9"); break; // u grave
2368: case 158: buf.append("\u00fb"); break; // u circumflex
2369: case 159: buf.append("\u00fc"); break; // u dieresis
2370: case 160: buf.append("\u1e6d"); break; // t underdot
2371: case 161: buf.append("\u00b0"); break; // degree
2372: case 162: buf.append("\u1ebd"); break; // e tilde
2373: case 163: buf.append("\u00a3"); break; // sterling
2374: case 164: buf.append("\u00a7"); break; // section
2375: case 166: buf.append("\u00b6"); break; // paragraph
2376: case 167: buf.append("\u015b"); break; // s acute
2377: case 168: buf.append("\u1e5b"); break; // r underdot
2378: case 169: buf.append("\u1e67"); break; // s caron
2379: case 171: buf.append("\u0301"); break; // acute
2380: case 172: buf.append("\u0308"); break; // dieresis
2381: case 173: buf.append("\u1e6d"); break; // t underdot
2382: case 174: buf.append("\u00c6"); break; // AE
2383: case 175: buf.append("\u014d"); break; // o macron
2384: case 176: buf.append("\u0129"); break; // i tilde
2385: case 177: buf.append("\u00b1"); break; // plusminus
2386: case 180: buf.append("\u012b"); break; // i macron
2387: case 181: buf.append("\u1e43"); break; // m underdot
2388: case 182: buf.append("\u1e0d"); break; // d underdot
2389: case 183: buf.append("\u1e63"); break; // s underdot
2390: case 185: buf.append("\u017a"); break; // z acute
2391: case 186: buf.append("\u1e45"); break; // n overdot
2392: case 189: buf.append("\u0169"); break; // u tilde
2393: case 190: buf.append("\u00e6"); break; // ae
2394: case 191: buf.append("\u00f8"); break; // oslash
2395: case 192: buf.append("\u0304\u0306"); break; // macron breve
2396: case 194: buf.append("\u1e37"); break; //
2397: case 195: buf.append("j\u0305"); break; // j macron [does a single char exist?]
2398: case 196: buf.append("\u0103"); break; // a breve
2399: case 197: buf.append("\u016d"); break; // u breve
2400: case 200: buf.append("\u1e42"); break; // M underdot
2401: case 201: buf.append("\u2026"); break; // ellipsis
2402: case 203: buf.append("\u00c0"); break; // A grave
2403: case 204: buf.append("\u00c3"); break; // A tilde
2404: case 205: buf.append("\u00d5"); break; // O tilde
2405: case 206: buf.append("m\u0306"); break; // m breve
2406: case 207: buf.append("\u0153"); break; // oe
2407: case 208: buf.append("\u2013"); break; // endash
2408: case 209: buf.append("\u2014"); break; // emdash
2409: case 210: buf.append("\u201c"); break; // quotedblleft
2410: case 211: buf.append("\u201d"); break; // quotedblright
2411: case 212: buf.append("\u2018"); break; // quoteleft
2412: case 213: buf.append("\u2019"); break; // quoteright
2413: case 214: buf.append("\u1e37"); break; // l underring [actually underdot]
2414: case 215: buf.append("\u1e41"); break; // m overdot
2415: case 216: buf.append("n\u0306"); break; // n breve
2416: case 217: buf.append("\u00d7"); break; // multiply
2417: case 219: buf.append("\u1e5b"); break; // r underring [actually underdot]
2418: case 220: buf.append("\u1e44"); break; // N overdot
2419: case 221: buf.append("\u1e62"); break; // S underdot
2420: case 222: buf.append("\u1e24"); break; // H underdot
2421: case 223: buf.append("\u1e0c"); break; // D underdot
2422: case 224: buf.append("\u2021"); break; // daggerdbl
2423: case 226: buf.append("\u1e36"); break; // L underdot
2424: case 227: buf.append("\u0113"); break; // e macron
2425: case 229: buf.append("\u1e5f"); break; // r underbar
2426: case 230: buf.append("r\u0324"); break; // r underdieresis
2427: case 231: buf.append("\u012a"); break; // I macron
2428: case 232: buf.append("\u016b"); break; // u macron
2429: case 233: buf.append("\u01e6c"); break; // T underdot
2430: case 234: buf.append("\u1e64"); break; // S acute
2431: case 235: buf.append("\u2020"); break; // dagger
2432: case 236: buf.append("\u0115"); break; // e breve
2433: case 237: buf.append("\u014f"); break; // o breve
2434: case 238: buf.append("\u0100"); break; // A macron
2435: case 239: buf.append("\u1e46"); break; // N underdot
2436: case 241: buf.append("\u1e3b"); break; // l underbar
2437: case 242: buf.append("\u016a"); break; // U macron
2438: case 243: buf.append("\u0179"); break; // Z acute
2439: case 244: buf.append("\u1e5a"); break; // R underdot
2440: case 245: buf.append("\u0131"); break; // dotlessi
2441: case 246: buf.append("\u1e47"); break; // n underdot
2442: case 247: buf.append("\u1e49"); break; // n underbar
2443: case 248: buf.append("\u0304"); break; // macron
2444: case 249: buf.append("\u0306"); break; // breve
2445: case 250: buf.append("\u1e25"); break; // h underdot
2446: case 251: buf.append("\u012d"); break; // i breve
2447: case 252: buf.append("\u0327"); break; // cedilla
2448: case 253: buf.append("\u030b"); break; // hungarumlaut
2449: case 254: buf.append("\u0328"); break; // ogonek
2450: case 255: buf.append("\u030c"); break; // caron
2451: case 130: buf.append("\u012b\u0303"); break; // imacron tilde
2452: case 165: buf.append("\u1e5d"); break; // runderdot macron
2453: case 170: buf.append("\u016b\0306"); break; // umacron breve
2454: case 178: buf.append("\u0101\u0301"); break; // amacron acute
2455: case 179: buf.append("\u016b\u0301"); break; // umacron acute
2456: case 184: buf.append("\u0113\u0301"); break; // emacron acute
2457: case 187: buf.append("\u0113\u0300"); break; // emacron breve
2458: case 188: buf.append("\u014d\u0300"); break; // omacron breve
2459: case 193: buf.append("\u0101\u0306"); break; // amacron breve
2460: case 198: buf.append("\u0101\u0303"); break; // amacron tilde
2461: case 199: buf.append("\u012b\u0301"); break; // imacron acute
2462: case 218: buf.append("\u1e00"); break; // runderdotmacron acute
2463: case 225: buf.append("\u1e5b\u0301"); break; // runderdot acute
2464: case 228: buf.append("\u012b\u0306"); break; // imacron breve
2465: case 240: buf.append("\u016b\u0303"); break; // umacron tilde
2466: default: buf.append(c); break;
2467: }
2468: }
2469: return buf.toString();
2470: }
2471: static public String normanToUnicodeNew(String inp) {
2472: StringBuffer buf = new StringBuffer();
2473: for (int i = 0; i < inp.length(); i++) {
2474: char c = inp.charAt(i);
2475: switch (c) {
2476: case 1: buf.append("\u00d0"); break; // Eth
2477: case 2: buf.append("\u00f0"); break; // eth
2478: case 3: buf.append("\u0141"); break; // Lslash
2479: case 4: buf.append("\u0142"); break; // lslash
2480: case 5: buf.append("\u0160"); break; // S caron
2481: case 6: buf.append("\u0161"); break; // s caron
2482: case 7: buf.append("\u00dd"); break; // Y acute
2483: case 8: buf.append("\u00fd"); break; // y acute
2484: case 11: buf.append("\u00de"); break; // Thorn
2485: case 12: buf.append("\u00fe"); break; // thorn
2486: case 14: buf.append("\u017d"); break; // Z caron
2487: case 15: buf.append("\u017e"); break; // z caron
2488: case 17: buf.append("\u0073"); break; // asciitilde
2489: case 18: buf.append("j\u0305"); break; // j macron [does a single char exist?]
2490: case 19: buf.append("^"); break; // circumflex
2491: case 20: buf.append("\u0303"); break; // tilde
2492: case 21: buf.append("\u00bd"); break; // onehalf
2493: case 22: buf.append("\u00bc"); break; // onequarter
2494: case 23: buf.append("\u00b9"); break; // onesuperior
2495: case 24: buf.append("\u00be"); break; // threequarters
2496: case 25: buf.append("\u00b3"); break; // threesuperior
2497: case 26: buf.append("\u00b2"); break; // twosuperior
2498: case 27: buf.append("\u00a6"); break; // brokenbar
2499: case 28: buf.append("-"); break; // minus
2500: case 29: buf.append("\u00d7"); break; // multiply
2501: case 39: buf.append("'"); break; // quotesingle
2502: case 94: buf.append("\u0302"); break; // circumflex
2503: case 96: buf.append("\u0300"); break; // grave
2504: case 196: buf.append("\u00c4"); break; // A dieresis
2505: case 197: buf.append("\u00c5"); break; // A ring
2506: case 201: buf.append("\u00c9"); break; // E acute
2507: case 209: buf.append("\u00d1"); break; // N tilde
2508: case 214: buf.append("\u00d6"); break; // O dieresis
2509: case 220: buf.append("\u00dc"); break; // U dieresis
2510: case 225: buf.append("\u00e1"); break; // a acute
2511: case 224: buf.append("\u00e0"); break; // a grave
2512: case 226: buf.append("\u00e2"); break; // a circumflex
2513: case 228: buf.append("\u00e4"); break; // a dieresis
2514: case 227: buf.append("\u00e3"); break; // a tilde
2515: case 229: buf.append("\u0101"); break; // a macron
2516: case 231: buf.append("\u00e7"); break; // c cedilla
2517: case 233: buf.append("\u00e9"); break; // e acute
2518: case 232: buf.append("\u00e8"); break; // e grave
2519: case 234: buf.append("\u00ea"); break; // e circumflex
2520: case 235: buf.append("\u00eb"); break; // e dieresis
2521: case 237: buf.append("\u00ed"); break; // i acute
2522: case 236: buf.append("\u00ec"); break; // i grave
2523: case 238: buf.append("\u00ee"); break; // i circumflex
2524: case 239: buf.append("\u00ef"); break; // i dieresis
2525: case 241: buf.append("\u00f1"); break; // n tilde
2526: case 243: buf.append("\u00f3"); break; // o acute
2527: case 242: buf.append("\u00f2"); break; // o grave
2528: case 244: buf.append("\u00f4"); break; // o circumflex
2529: case 246: buf.append("\u00f6"); break; // o dieresis
2530: case 245: buf.append("\u00f5"); break; // o tilde
2531: case 250: buf.append("\u00fa"); break; // u acute
2532: case 249: buf.append("\u00f9"); break; // u grave
2533: case 251: buf.append("\u00fb"); break; // u circumflex
2534: case 252: buf.append("\u00fc"); break; // u dieresis
2535: case 8224: buf.append("\u1e6d"); break; // t underdot
2536: case 176: buf.append("\u00b0"); break; // degree
2537: case 162: buf.append("\u1ebd"); break; // e tilde
2538: case 163: buf.append("\u00a3"); break; // sterling
2539: case 167: buf.append("\u00a7"); break; // section
2540: case 182: buf.append("\u00b6"); break; // paragraph
2541: case 223: buf.append("\u015b"); break; // s acute
2542: case 174: buf.append("\u1e5b"); break; // r underdot
2543: case 169: buf.append("\u1e45"); break; // n overdot
2544: case 180: buf.append("\u0301"); break; // acute
2545: case 168: buf.append("\u0308"); break; // dieresis
2546: case 8800: buf.append("\u1e6d"); break; // t underdot
2547: case 198: buf.append("\u00c6"); break; // AE
2548: case 216: buf.append("\u014d"); break; // o macron
2549: case 8734: buf.append("\u0129"); break; // i tilde
2550: case 177: buf.append("\u00b1"); break; // plusminus
2551: case 165: buf.append("\u012b"); break; // i macron
2552: case 181: buf.append("\u1e43"); break; // m underdot
2553: case 8706: buf.append("\u1e0d"); break; // d underdot
2554: case 8721: buf.append("\u1e63"); break; // s underdot
2555: case 960: buf.append("\u017a"); break; // z acute
2556: case 8747: buf.append("\u1e45"); break; // n overdot
2557: case 937: buf.append("\u0169"); break; // u tilde
2558: case 230: buf.append("\u00e6"); break; // ae
2559: case 248: buf.append("\u00f8"); break; // oslash
2560: case 191: buf.append("\u0304\u0306"); break; // macron breve
2561: case 172: buf.append("\u1e37"); break; //
2562: case 8730: buf.append("j\u0305"); break; // j macron [does a single char exist?]
2563: case 402: buf.append("\u0103"); break; // a breve
2564: case 8776: buf.append("\u016d"); break; // u breve
2565: case 187: buf.append("\u1e42"); break; // M underdot
2566: case 8230: buf.append("\u2026"); break; // ellipsis
2567: case 192: buf.append("\u00c0"); break; // A grave
2568: case 195: buf.append("\u00c3"); break; // A tilde
2569: case 213: buf.append("\u00d5"); break; // O tilde
2570: case 338: buf.append("m\u0306"); break; // m breve
2571: case 339: buf.append("\u0153"); break; // oe
2572: case 8211: buf.append("\u2013"); break; // endash
2573: case 8212: buf.append("\u2014"); break; // emdash
2574: case 8220: buf.append("\u201c"); break; // quotedblleft
2575: case 8221: buf.append("\u201d"); break; // quotedblright
2576: case 8216: buf.append("\u2018"); break; // quoteleft
2577: case 8217: buf.append("\u2019"); break; // quoteright
2578: case 247: buf.append("\u1e37"); break; // l underring [actually underdot]
2579: case 9674: buf.append("\u1e41"); break; // m overdot
2580: case 255: buf.append("n\u0306"); break; // n breve
2581: case 376: buf.append("\u00d7"); break; // multiply
2582: case 8364: buf.append("\u1e5b"); break; // r underring [actually underdot]
2583: case 8249: buf.append("\u1e44"); break; // N overdot
2584: case 8250: buf.append("\u1e62"); break; // S underdot
2585: case 64257: buf.append("\u1e24"); break; // H underdot
2586: case 64258: buf.append("\u1e0c"); break; // D underdot
2587: case 8225: buf.append("\u2021"); break; // daggerdbl
2588: case 8218: buf.append("\u1e36"); break; // L underdot
2589: case 8222: buf.append("\u0113"); break; // e macron
2590: case 194: buf.append("\u1e5f"); break; // r underbar
2591: case 202: buf.append("r\u0324"); break; // r underdieresis
2592: case 193: buf.append("\u012a"); break; // I macron
2593: case 203: buf.append("\u016b"); break; // u macron
2594: case 200: buf.append("\u1e6c"); break; // T underdot
2595: case 205: buf.append("\u1e64"); break; // S acute
2596: case 206: buf.append("\u2020"); break; // dagger
2597: case 207: buf.append("\u0115"); break; // e breve
2598: case 204: buf.append("\u014f"); break; // o breve
2599: case 211: buf.append("\u0100"); break; // A macron
2600: case 212: buf.append("\u1e46"); break; // N underdot
2601: case 210: buf.append("\u1e3b"); break; // l underbar
2602: case 218: buf.append("\u016a"); break; // U macron
2603: case 219: buf.append("\u0179"); break; // Z acute
2604: case 217: buf.append("\u1e5a"); break; // R underdot
2605: case 305: buf.append("\u0131"); break; // dotlessi
2606: case 710: buf.append("\u1e47"); break; // n underdot
2607: case 732: buf.append("\u1e49"); break; // n underbar
2608: case 175: buf.append("\u0304"); break; // macron
2609: case 728: buf.append("\u0306"); break; // breve
2610: case 729: buf.append("\u1e25"); break; // h underdot
2611: case 730: buf.append("\u012d"); break; // i breve
2612: case 184: buf.append("\u0327"); break; // cedilla
2613: case 733: buf.append("\u030b"); break; // hungarumlaut
2614: case 731: buf.append("\u0328"); break; // ogonek
2615: case 711: buf.append("\u030c"); break; // caron
2616: case 199: buf.append("\u012b\u0303"); break; // imacron tilde
2617: case 8226: buf.append("\u1e5d"); break; // runderdot macron
2618: case 8482: buf.append("\u016b\0306"); break; // umacron breve
2619: case 8804: buf.append("\u0101\u0301"); break; // amacron acute
2620: case 8805: buf.append("\u016b\u0301"); break; // umacron acute
2621: case 8719: buf.append("\u0113\u0301"); break; // emacron acute
2622: case 170: buf.append("\u0113\u0300"); break; // emacron breve
2623: case 186: buf.append("\u014d\u0300"); break; // omacron breve
2624: case 161: buf.append("\u0101\u0306"); break; // amacron breve
2625: case 8710: buf.append("\u0101\u0303"); break; // amacron tilde
2626: case 171: buf.append("\u012b\u0301"); break; // imacron acute
2627: case 8260: buf.append("\u1e00"); break; // runderdotmacron acute
2628: case 183: buf.append("\u1e5b\u0301"); break; // runderdot acute
2629: case 8240: buf.append("\u012b\u0306"); break; // imacron breve
2630: case 63743: buf.append("\u016b\u0303"); break; // umacron tilde
2631: default: buf.append(c); break;
2632: }
2633: }
2634: return buf.toString();
2635: }
2636: public static ConversionProperties getFieldNamesAndDestTableName(String create,String query,String tableName )
2637: {
2638: String[] fieldNames = null;
2639: String destTableName = null;
2640: // determine destTableName from createStatement or from source table name
2641: if (!create.equals(""))
2642: {
2643: int fromIndex = create.toLowerCase().indexOf("table") + 5;
2644: int toIndex = create.indexOf("(");
2645: int endIndex = create.indexOf(")", toIndex);
2646:
2647: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
2648: System.out.println("destTable " + destTableName);
2649: // retrieve field_names from select statement
2650: // TODO problem with different fieldNames in create statement will overwrite them
2651: if (query.indexOf("*") < 0)
2652: {
2653: int selectEndIndex = query.indexOf("from");
2654: StringTokenizer tokenizer = new StringTokenizer(query.substring(6, selectEndIndex), ",");
2655: int numFields = tokenizer.countTokens();
2656: fieldNames = new String[numFields];
2657: int fieldIndex = 0;
2658: while (tokenizer.hasMoreTokens())
2659: {
2660: String fieldName = tokenizer.nextToken().trim();
2661: fieldNames[fieldIndex] = convertText(fieldName);
2662: System.out.println(fieldNames[fieldIndex]);
2663: fieldIndex++;
2664: }
2665:
2666: } else
2667: {
2668: // use create statement for field names
2669: StringTokenizer tokenizer = new StringTokenizer(create.substring(toIndex + 1, endIndex), ",");
2670: int numFields = tokenizer.countTokens();
2671: fieldNames = new String[numFields];
2672: int fieldIndex = 0;
2673: while (tokenizer.hasMoreTokens())
2674: {
2675: String fieldName = tokenizer.nextToken().trim();
2676: int index = fieldName.lastIndexOf(" ");
2677: fieldNames[fieldIndex] = fieldName.substring(0, index);
2678: System.out.println(fieldNames[fieldIndex]);
2679: fieldIndex++;
2680: }
2681: }
2682: } else
2683: {
2684: destTableName = convertText(tableName);
2685:
2686: // retrieve field_names from select statement
2687: if (query.indexOf("*") < 0)
2688: {
2689: int selectEndIndex = query.indexOf("from");
2690: StringTokenizer tokenizer = new StringTokenizer(query.substring(6, selectEndIndex), ",");
2691: int numFields = tokenizer.countTokens();
2692: fieldNames = new String[numFields];
2693: int fieldIndex = 0;
2694: while (tokenizer.hasMoreTokens())
2695: {
2696: String fieldName = tokenizer.nextToken().trim();
2697: fieldNames[fieldIndex] = convertText(fieldName);
2698: // System.out.println("field "+ fieldNames[fieldIndex]);
2699: fieldIndex++;
2700: }
2701:
2702: } else
2703: {
2704: Vector fieldNamesVec = bean.getColumnNames();
2705: fieldNames = new String[fieldNamesVec.size()];
2706: int fieldIndex = -1;
2707: for (Iterator iter = fieldNamesVec.iterator(); iter.hasNext();)
2708: {
2709: String element = (String) iter.next();
2710: fieldNames[++fieldIndex] = bean.getQC() + convertText(element) + bean.getQC();
2711: // System.out.println("field " + fieldNames[fieldIndex]);
2712: }
2713: }
2714: }
2715: return new ConversionProperties(destTableName,fieldNames);
2716: }
2717: }
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