1: /*
2: * Convert.java -- Converter class - Filemaker to SQL Converter
3: * Copyright (C) 2004 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.util.*;
16: import java.sql.*;
17: import java.awt.Cursor;
18: import java.io.BufferedReader;
19: import java.io.BufferedWriter;
20: import java.io.File;
21: import java.io.FileInputStream;
22: import java.io.FileNotFoundException;
23: import java.io.FileOutputStream;
24: import java.io.InputStreamReader;
25: import java.io.OutputStreamWriter;
26: import java.io.PrintStream;
27: import java.io.UnsupportedEncodingException;
28:
29: import com.exploringxml.xml.Node;
30: import com.exploringxml.xml.Xparse;
31:
32: class Convert
33: {
34: static DBBean bean = new DBBean();
35: static DBBean beanDest = new DBBean();
36:
37: static String user = "", passwd = "e1nste1n";
38: static String userDest = "postgres", passwdDest = "rogo";
39: static boolean batchRun = false;
40: static Vector databases = new Vector();
41: final static int numHits = 5000;
42: final static int numIntervalls = 2;
43: public static void main(String args[])
44: {
45: /* try
46: {
47: //byte[] b = "ö".getBytes("UTF-8");
48: // System.out.println("QueryString " +b[0]+" "+b[1]+(new String(b).getBytes()[0])+" "+new String(b).getBytes()[1]);
49: //System.out.println(new String(b,"UTF-8"));
50: } catch (UnsupportedEncodingException e)
51: {
52: e.printStackTrace();
53: }*/
54: FileOutputStream file = null;
55: if (args.length != 1)
56: {
57: System.out.println("Usage: java Convert <xml config file>");
58: System.exit(-1);
59: }
60: if (!(new File(args[0]).exists()))
61: System.exit(0);
62: try
63: {
64: file = new FileOutputStream("./log.txt");
65: } catch (FileNotFoundException e1)
66: {
67: e1.printStackTrace();
68: }
69: PrintStream stream = new PrintStream(file);
70: System.setOut(stream);
71: System.setErr(stream);
72: readXMLFile(args[0]);
73: System.out.println("Finished!");
74: //convert("jdbc:fmpro:http://141.14.237.74:8050","jdbc:postgresql://erebos/test",null,null);
75: }
76: public static void convertBatch(DBBean source, DBBean destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids,int mode,String delimiter) throws Exception
77: {
78: bean = source;
79: beanDest = destination;
80: convert(null,null,names,layouts,selects,creates,ids,mode,delimiter);
81: if(true) return;
82: StringBuffer command = null;
83: try
84: {
85: bean.setConnection(source.url);
86: if (names == null)
87: names = bean.getTableNames();
88: //Collections.sort(names);
89: int tbIndex = 1;
90:
91: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
92: {
93: Vector[] result = null;
94: try
95: {
96: String query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
97: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
98: query = (selects != null) ? selects.get(tbIndex).toString() : query;
99: //if vectors[1].get(i) != null)
100: if (!layout.equals(""))
101: {
102: System.out.println("before " + query + " table" + names.get(tbIndex));
103: layout = " layout " + bean.getQC() + layout + bean.getQC();
104: String name = names.get(tbIndex).toString();
105: StringBuffer queryLayout = new StringBuffer(query);
106: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
107: query = queryLayout.toString();
108: System.out.println("added layout " + query);
109:
110: }
111: System.out.println(" performing query " + query);
112: //result = bean.getQueryData(query, null, 0);
113: bean.getConnection();
114: bean.makeQuery(query, 0);
115: } catch (Exception e)
116: {
117: System.out.println(e.getMessage());
118: e.printStackTrace();
119: continue;
120: }
121: //beanDest.setConnection("jdbc:postgresql://erebos/test3");
122: beanDest.setConnection(destination.url);
123:
124: Statement stm = beanDest.getConnection().createStatement();
125:
126: Vector tables = beanDest.getTableNames();
127: // Collections.sort(tables);
128: System.out.println("converting table " + names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
129: tables = beanDest.getTableNames();
130: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
131: stm = beanDest.getConnection().createStatement();
132: // System.exit(0);
133: if (mode == Convert.DataBase.CONVERT_MODE)
134: {
135: if (tables.indexOf(names.get(tbIndex)) >= 0)
136: {
137: stm.executeUpdate("drop table " + beanDest.getQC() + names.get(tbIndex) + beanDest.getQC());
138: tables.remove((String) names.get(tbIndex));
139: System.out.println("dropped table " + names.get(tbIndex));
140: } else if (tables.indexOf(convertText(names.get(tbIndex).toString())) >= 0)
141: {
142: stm.executeUpdate("drop table " + beanDest.getQC() + convertText((String) names.get(tbIndex)) + beanDest.getQC());
143: tables.remove(convertText((String) names.get(tbIndex)));
144: System.out.println("dropped table " + names.get(tbIndex));
145: }
146:
147: if (tables.indexOf(names.get(tbIndex)) < 0 && tables.indexOf(convertText(names.get(tbIndex).toString())) < 0)
148: {
149: if (creates.get(tbIndex).equals("") || creates.get(tbIndex).toString().toLowerCase().indexOf("create") < 0)
150: {
151: System.out.println("Warning empty or invalid create statement - creating one for you\n");
152:
153: command = new StringBuffer(50);
154: command.append("CREATE TABLE ");
155: command.append(beanDest.getQC());
156: command.append(convertText((String) names.get(tbIndex)));
157: command.append(beanDest.getQC());
158: command.append("(");
159: String type = null;
160: Vector columnNames = bean.getColumnNames();
161: for (int i = 0; i < columnNames.size() - 1; ++i)
162: {
163: type = bean.metaData.getColumnTypeName(i + 1);
164: // System.out.println(i+" "+result[1].get(i)+" "+type);
165: type = (type.equals("NUMBER")) ? "INT4" : type;
166: type = (type.equals("CONTAINER")) ? "TEXT" : type;
167:
168: command.append(beanDest.getQC() + convertText((String) columnNames.get(i)) + beanDest.getQC() + " " + type + ", ");
169: }
170: type = bean.metaData.getColumnTypeName(columnNames.size());
171: type = (type.equals("NUMBER")) ? "INT4" : type;
172: type = (type.equals("CONTAINER")) ? "TEXT" : type;
173: command.append(beanDest.getQC() + convertText((String) columnNames.get(columnNames.size() - 1)) + beanDest.getQC() + " " + type);
174: command.append(" )");
175: } else
176: command = new StringBuffer().append(creates.get(tbIndex).toString());
177:
178: System.out.println(command);
179: // System.exit(0);
180: //command.append(DBBean.getQC());
181: stm.executeUpdate(command.toString());
182:
183: }
184: }
185: Vector row = null;
186: command = new StringBuffer();
187:
188: command.append("INSERT INTO ");
189: command.append(beanDest.getQC());
190: command.append(convertText((String) names.get(tbIndex)));
191: command.append(beanDest.getQC());
192: command.append(" values ( ");
193:
194: for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
195: command.append("?,");
196: command.append("?)");
197: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
198: System.out.println(command);
199: while ((row = bean.getNextRow()) != null)
200: {
201: //print rows
202: Object obj = null;
203: for (int k = 0; k < row.size(); ++k)
204: {
205: obj = row.get(k);
206: if (obj instanceof ArrayList)
207: obj = formatFileMakerArray((List) obj,"\n");
208: String str = (obj == null) ? "NULL" : obj.toString();
209: if (!str.equals("NULL"))
210: pstm.setString(k + 1, str);
211: else
212: pstm.setNull(k + 1, Types.NULL);
213: }
214: pstm.execute();
215:
216: } // to for loop
217:
218: }
219: } catch (Exception e)
220: {
221: System.out.println("Error while connecting to database " + e);
222: //dialog.setVisible(false);
223: //dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
224: //FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
225: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
226: java.io.PrintStream stream = new java.io.PrintStream(b);
227: stream.print(command + "\n\n");
228: e.printStackTrace(stream);
229: System.err.println(b);
230: //FM2SQL.showErrorDialog(b.toString(), "Error occured !");
231:
232: }
233: // dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
234: //FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
235:
236: // dialog.setVisible(false);
237: }
238: public static String formatFileMakerArray(List list, String delimiter)
239: {
240: StringBuffer formattedString = new StringBuffer();
241: for(int i=0;i<list.size();++i)
242: {
243: formattedString.append(list.get(i).toString());
244: if(i<list.size()-1)
245: formattedString.append(delimiter);
246: }
247: return formattedString.toString();
248: }
249: /**
250: * Method for SQL UPDATE
251: * @param source
252: * @param destination
253: * @param names
254: * @param layouts
255: * @param selects
256: * @param creates
257: * @param ids
258: * @param mode
259: * @throws Exception
260: */
261: public static void update(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
262: {
263: FM2SQL.ProgressDialog dialog = null;
264: if (FM2SQL.fmInstance != null)
265: {
266: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance);
267: dialog.setTitle("Conversion running ...");
268: dialog.title.setText("Getting table data ...");
269: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
270: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
271: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
272: dialog.thread = Thread.currentThread();
273: }
274: // setting user and passwd
275: bean.setUserAndPasswd(user, passwd);
276: // setting user and passwd
277: beanDest.setUserAndPasswd(userDest, passwdDest);
278: if (dialog != null)
279: dialog.setSize(400, 250);
280: StringBuffer command = null;
281: String query = null;
282: try
283: {
284: //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
285: //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
286: bean.setConnection(source);
287: if (names == null)
288: names = bean.getTableNames();
289: // Collections.sort(names);
290: int tbIndex = 1;
291:
292: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
293: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
294: {
295: Vector[] result = null;
296: String destTableName = "";
297: try
298: {
299: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
300: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
301: query = (selects != null) ? selects.get(tbIndex).toString() : query;
302: //if vectors[1].get(i) != null)
303: if (layout != "")
304: {
305: layout = " layout " + bean.getQC() + layout + bean.getQC();
306: String name = names.get(tbIndex).toString();
307: StringBuffer queryLayout = new StringBuffer(query);
308: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
309: query = queryLayout.toString();
310: System.out.println("added layout " + query);
311:
312: }
313: dialog.title.setText("Getting table data ...");
314: dialog.table.setText(names.get(tbIndex).toString());
315: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
316: dialog.show();
317: bean.getConnection();
318: bean.makeQuery(query, 0);
319: } catch (Exception e)
320: {
321: continue;
322: }
323: // determine destTableName from createStatement or from source table name
324: if(!creates.get(tbIndex).equals(""))
325: {
326: String create =creates.get(tbIndex).toString().toLowerCase();
327: int fromIndex = create.indexOf("table")+5;
328: int toIndex = create.indexOf("(");
329: destTableName = create.substring(fromIndex,toIndex).replaceAll(beanDest.getQC(),"").trim();
330: System.out.println("destTable "+destTableName);
331:
332: } else
333: destTableName = convertText(names.get(tbIndex).toString());
334:
335: //beanDest.setConnection("jdbc:postgresql://erebos/test3");
336: beanDest.setConnection(destination);
337:
338: Statement stm = beanDest.getConnection().createStatement();
339:
340: Vector tables = beanDest.getTableNames();
341: // Collections.sort(tables);
342: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
343: tables = beanDest.getTableNames();
344: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
345: stm = beanDest.getConnection().createStatement();
346: // System.exit(0);
347:
348: if (dialog != null)
349: dialog.title.setText("Updating table data ...");
350:
351: int j = -1;
352:
353: Vector row = null;
354: command = new StringBuffer();
355:
356: command.append("UPDATE ");
357: command.append(beanDest.getQC());
358: command.append(destTableName);
359: //command.append(convertText((String) names.get(tbIndex)));
360: command.append(beanDest.getQC());
361: command.append(" SET ");
362:
363: int size = bean.getColumnNames().size();
364: for (int i = 0; i < size - 1; ++i)
365: command.append(beanDest.getQC() + convertText((String) bean.getColumnNames().get(i)) + beanDest.getQC() + " = ? ,");
366: command.append(convertText((String) bean.getColumnNames().get(size - 1)) + " = ? ");
367: command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
368: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
369: System.out.println(command + " " + tbIndex);
370: int rowCount = bean.getRowCount(query);
371: int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
372: while ((row = bean.getNextRow()) != null)
373: {
374: j++;
375: //print rows
376: Object obj = null;
377: /* for(int k=0;k<row.size()-1;++k)
378: {
379: obj = row.get(k);
380: //System.out.println("row "+obj+" "+k);
381: if(obj!=null&&!(obj instanceof ArrayList))
382: command.append("'"+convertUml(obj.toString())+"',");
383: else if(obj!=null&& obj instanceof ArrayList)
384: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',");
385: else command.append("NULL,");
386: }
387: obj = row.get(row.size() - 1);
388: if (obj != null && !(obj instanceof ArrayList))
389: command.append("'"+convertUml(obj.toString())+"')");
390: else
391: if(obj!=null&& obj instanceof ArrayList)
392: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')"); //command.append(obj.toString()+")");
393: else command.append("NULL)");
394: */
395: //command.append("'"+row.get(row.size()-1)+"')");
396: //command.append(" )");
397: // for(int k=0;k<row.size();++k)
398:
399: // System.out.println();
400: // System.out.println(command+" "+j+" "+row.size()+" "+ ((Vector)result2[0].get(j)).size());
401: // System.out.println(command);
402: for (int k = 0; k < row.size(); ++k)
403: {
404: obj = row.get(k);
405: if (obj instanceof ArrayList)
406: obj = ((List) obj).get(0);
407: String str = (obj == null) ? "NULL" : obj.toString();
408: if (!str.equals("NULL"))
409: pstm.setString(k + 1, str);
410: else
411: pstm.setNull(k + 1, Types.NULL);
412: }
413: pstm.setString(row.size() + 1, row.get(idIndex).toString());
414: //System.out.println(pstm.toString());
415: // System.exit(0);
416: pstm.execute();
417: //stm.executeUpdate(command.toString());
418: if (dialog != null)
419: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
420: // System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
421: command = null;
422: } // to for loop
423:
424: }
425: } catch (Exception e)
426: {
427: System.out.println("Error while connecting to database " + e);
428: if (dialog != null)
429: {
430: dialog.setVisible(false);
431: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
432: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
433: }
434: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
435: java.io.PrintStream stream = new java.io.PrintStream(b);
436: stream.print(command + "\n\n");
437: e.printStackTrace(stream);
438: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
439:
440: }
441: if (dialog != null)
442: {
443: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
444: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
445:
446: dialog.setVisible(false);
447: }
448:
449: }
450: /**
451: * transfers the specified array of tables to the destination database
452: and creates the table if it does not exist if it exists and mode is not append the table is dropped
453:
454: * @param source
455: * @param destination
456: * @param names
457: * @param layouts
458: * @param selects
459: * @param creates
460: * @param ids
461: * @param mode
462: * @throws Exception
463: */
464:
465:
466: public static void convert(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode,String delimiter) throws Exception
467: {
468:
469: FM2SQL.ProgressDialog dialog = null;
470:
471: if (FM2SQL.fmInstance != null)
472: {
473: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance);
474: dialog.setTitle("Conversion running ...");
475: dialog.title.setText("Getting table data ...");
476: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
477: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
478: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
479: dialog.thread = Thread.currentThread();
480: dialog.setSize(400, 250);
481: }
482: java.util.TreeSet myIds = new TreeSet();
483: int deltaID = 1;
484: String idField = "";
485: String destTableName = "";
486: if (source != null && destination != null)
487: {
488: // setting user and passwd
489: bean.setUserAndPasswd(user, passwd);
490: // setting user and passwd
491: beanDest.setUserAndPasswd(userDest, passwdDest);
492: }
493: StringBuffer command = null;
494: String query = null;
495: try
496: {
497: if(source!=null)
498: bean.setConnection(source);
499: else
500: bean.setConnection(bean.url);
501:
502: if (names == null)
503: names = bean.getTableNames();
504: // Collections.sort(names);
505: int tbIndex = 1;
506:
507: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
508: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
509: {
510: Vector[] result = null;
511: try
512: {
513: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
514: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
515: query = (selects != null) ? selects.get(tbIndex).toString() : query;
516: //if vectors[1].get(i) != null)
517: if (layout != "")
518: {
519: layout = " layout " + bean.getQC() + layout + bean.getQC();
520: String name = names.get(tbIndex).toString();
521: StringBuffer queryLayout = new StringBuffer(query);
522: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
523: query = queryLayout.toString();
524: System.out.println("added layout " + query);
525:
526: }
527: // if ( layout!= "")
528: // query += " layout " + bean.getQC() + layout + bean.getQC();
529: if (dialog != null)
530: {
531: dialog.title.setText("Reading table data ...");
532: dialog.table.setText(names.get(tbIndex).toString());
533: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
534: dialog.show();
535: }
536: //result = bean.getQueryData(query, dialog, 0);
537: bean.getConnection();
538: bean.makeQuery(query, 50);
539: idField = ids.get(tbIndex).toString();
540:
541: } catch (Exception e)
542: {
543: System.out.println(e);
544: continue;
545: }
546: if(destination!=null)
547: beanDest.setConnection(destination);
548: else
549: beanDest.setConnection(beanDest.url);
550: Statement stm = beanDest.getConnection().createStatement();
551:
552: Vector tables = beanDest.getTableNames();
553: // Collections.sort(tables);
554: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
555: tables = beanDest.getTableNames();
556: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
557: stm = beanDest.getConnection().createStatement();
558: // System.exit(0);
559:
560: // determine destTableName from createStatement or from source table name
561: if(!creates.get(tbIndex).equals(""))
562: {
563: String create =creates.get(tbIndex).toString().toLowerCase();
564: int fromIndex = create.indexOf("table")+5;
565: int toIndex = create.indexOf("(");
566: destTableName = create.substring(fromIndex,toIndex).replaceAll(beanDest.getQC(),"").trim();
567: System.out.println("destTable "+destTableName);
568:
569: } else
570: destTableName = convertText(names.get(tbIndex).toString());
571:
572: if (mode == Convert.DataBase.CONVERT_MODE)
573: {
574:
575: if (tables.indexOf(destTableName) >= 0)
576: {
577: stm.executeUpdate("drop table " + beanDest.getQC() + destTableName + beanDest.getQC());
578: tables.remove(destTableName);
579: System.out.println("dropped table" + destTableName);
580:
581: }
582: /*
583: if(destTableName.equals(""))
584: if (tables.indexOf(names.get(tbIndex)) >= 0)
585: {
586: stm.executeUpdate("drop table " + beanDest.getQC() + names.get(tbIndex) + beanDest.getQC());
587: tables.remove((String) names.get(tbIndex));
588: System.out.println("dropped table" + names.get(tbIndex));
589: } else if (tables.indexOf(convertText(names.get(tbIndex).toString())) >= 0)
590: {
591: stm.executeUpdate("drop table " + beanDest.getQC() + convertText((String) names.get(tbIndex)) + beanDest.getQC());
592: tables.remove(convertText((String) names.get(tbIndex)));
593: System.out.println("dropped table" + names.get(tbIndex));
594: }
595: */
596: if ((tables.indexOf(destTableName) < 0)) //&& tables.indexOf(names.get(tbIndex)) < 0 && tables.indexOf(convertText(names.get(tbIndex).toString())) < 0 )
597: {
598:
599: if (creates.get(tbIndex).equals("") || creates.get(tbIndex).toString().toLowerCase().indexOf("create") < 0)
600: {
601: System.out.println("Warning empty or invalid create statement - creating one for you\n");
602:
603: command = new StringBuffer(50);
604: command.append("CREATE TABLE ");
605: command.append(beanDest.getQC());
606: command.append(convertText((String) names.get(tbIndex)));
607: command.append(beanDest.getQC());
608: command.append("(");
609: String type = null;
610: Vector columnNames = bean.getColumnNames();
611: for (int i = 0; i < columnNames.size() - 1; ++i)
612: {
613: type = bean.metaData.getColumnTypeName(i + 1);
614: // System.out.println(i+" "+result[1].get(i)+" "+type);
615: type = (type.equals("NUMBER")) ? "INT4" : type;
616: type = (type.equals("CONTAINER")) ? "TEXT" : type;
617:
618: command.append(beanDest.getQC() + convertText((String) columnNames.get(i)) + beanDest.getQC() + " " + type + ", ");
619: }
620: type = bean.metaData.getColumnTypeName(columnNames.size());
621: type = (type.equals("NUMBER")) ? "INT4" : type;
622: type = (type.equals("CONTAINER")) ? "TEXT" : type;
623: command.append(beanDest.getQC() + convertText((String) columnNames.get(columnNames.size() - 1)) + beanDest.getQC() + " " + type);
624: command.append(" )");
625:
626: // System.out.println(command);
627: // System.exit(0);
628: //command.append(DBBean.getQC());
629: } else
630: command = new StringBuffer().append(creates.get(tbIndex).toString().toLowerCase());
631: stm.executeUpdate(command.toString());
632:
633: }
634: }
635: if(dialog!=null)
636: dialog.title.setText("Writing table data ...");
637:
638: // prepare the insert statement
639: int j = -1;
640: Vector row = null;
641: command = new StringBuffer();
642:
643: command.append("INSERT INTO ");
644: command.append(beanDest.getQC());
645: command.append(destTableName); //convertText((String) names.get(tbIndex)));
646: command.append(beanDest.getQC());
647:
648: command.append(" values ( ");
649:
650: // add a question marks for every field
651: for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
652: command.append("?,");
653: command.append("?)");
654: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
655: System.out.println(command);
656: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
657: Vector vec = new Vector(myIds);
658: int endIndex = -1;
659: String tempQuery = query;
660: String tempID = bean.getQC() + idField + bean.getQC();
661: // if id_field not do incremental conversion else do it all at once
662: if (!idField.equals(""))
663: {
664: long startTime = System.currentTimeMillis();
665: int counter = -1;
666: while (true)
667: {
668: ++counter;
669: if (counter == 0&&dialog!=null)
670: dialog.title.setText("Check if data is available");
671: else
672: if(dialog!=null)
673: dialog.title.setText("Check if more data is available");
674: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, numHits);
675: if (myIds.isEmpty())
676: break;
677: vec = new Vector(myIds);
678: rowCount = vec.size();
679: System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIds.size());
680: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
681: if (vec.size() <= numIntervalls)
682: {
683: endIndex = 0;
684: deltaID = vec.size();
685: }
686: for (int k = 0; k < vec.size() - deltaID; k = k + deltaID)
687: {
688: System.out.println(vec.get(k) + " " + vec.get(k + deltaID) + " " + vec.lastElement());
689: if (query.indexOf("where") > 0)
690: tempQuery = query + " and " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID)+"'";
691: else
692: tempQuery = query + " where " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID)+"'";
693: System.out.println(tempQuery);
694: if(dialog!=null)
695: dialog.title.setText("Reading table data ...");
696:
697: bean.makeQuery(tempQuery, deltaID);
698: if(dialog!=null)
699: dialog.title.setText("Writing table data ...");
700:
701: command = writeDatainDestTable(dialog, command, k, pstm, rowCount,delimiter);
702: endIndex = k + deltaID;
703: }
704: System.out.println(endIndex);
705: //all data written ? if not write last chunk of data
706: if (endIndex == vec.size() - 1)
707: System.out.println("fits");
708: else
709: {
710: System.out.println(" last intervall from " + vec.get(endIndex) + " " + vec.lastElement());
711:
712: if (query.indexOf("where") > 0)
713: tempQuery = query + " and " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement()+"'";
714: else
715: tempQuery = query + " where " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement()+"'";
716: System.out.println(tempQuery);
717: if(dialog!=null)
718: dialog.title.setText("Reading table data ...");
719: bean.makeQuery(tempQuery, 0);
720: if(dialog!=null)
721: dialog.title.setText("Writing table data ...");
722: command = writeDatainDestTable(dialog, command, endIndex, pstm, rowCount,delimiter);
723: }
724: // prepare new query for next chunk
725: if (query.indexOf("where") > 0)
726: tempQuery = query + " and " + tempID + ">'" + vec.lastElement()+"'";
727: else
728: tempQuery = query + " where " + tempID + ">'" + vec.lastElement()+"'";
729:
730: }
731: long endTime = System.currentTimeMillis();
732: System.out.println("Time for incremental convert elapsed " + (endTime - startTime));
733: } else
734: {
735: // read and write all in one big chunk
736: long startTime = System.currentTimeMillis();
737:
738: bean.makeQuery(query, 0);
739: command = writeDatainDestTable(dialog, command, j, pstm, rowCount,delimiter);
740: long endTime = System.currentTimeMillis();
741: System.out.println("Time for old convert elapsed " + (endTime - startTime));
742:
743: }
744: }
745: } catch (Exception e)
746: {
747: System.out.println("Error while connecting to database " + e);
748: if (dialog != null)
749: {
750: dialog.setVisible(false);
751: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
752: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
753: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
754: java.io.PrintStream stream = new java.io.PrintStream(b);
755: stream.print(command + "\n\n");
756: e.printStackTrace(stream);
757: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
758: } else
759: {
760: e.printStackTrace();
761:
762: }
763: }
764: if (dialog != null)
765: {
766: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
767: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
768: dialog.setVisible(false);
769: }
770: }
771: /**
772: * Writes data to the destination table
773: * @param dialog progress dialog
774: * @param command
775: * @param j data index for progress bar
776: * @param pstm prepared statement
777: * @param rowCount number of datasets
778: * @return command
779: * @throws Exception
780: * @throws SQLException
781: */
782: private static StringBuffer writeDatainDestTable(FM2SQL.ProgressDialog dialog, StringBuffer command, int j, PreparedStatement pstm, int rowCount,String delimiter) throws Exception, SQLException
783: {
784: Vector row;
785: while ((row = bean.getNextRow()) != null)
786: {
787: j++;
788: // row = (Vector) result[0].get(j);
789: /* command = new StringBuffer();
790:
791: command.append("INSERT INTO ");
792: command.append(beanDest.getQC());
793: command.append(convertText((String) names.get(tbIndex)));
794: command.append(beanDest.getQC());
795: command.append(" values ( ");
796: */
797: //print rows
798: Object obj = null;
799: /* for(int k=0;k<row.size()-1;++k)
800: {
801: obj = row.get(k);
802: //System.out.println("row "+obj+" "+k);
803: if(obj!=null&&!(obj instanceof ArrayList))
804: command.append("'"+convertUml(obj.toString())+"',");
805: else if(obj!=null&& obj instanceof ArrayList)
806: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',");
807: else command.append("NULL,");
808: }
809: obj = row.get(row.size() - 1);
810: if (obj != null && !(obj instanceof ArrayList))
811: command.append("'"+convertUml(obj.toString())+"')");
812: else
813: if(obj!=null&& obj instanceof ArrayList)
814: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')"); //command.append(obj.toString()+")");
815: else command.append("NULL)");
816: */
817: //command.append("'"+row.get(row.size()-1)+"')");
818: //command.append(" )");
819: // for(int k=0;k<row.size();++k)
820:
821: // System.out.println();
822: // System.out.println(command+" "+j+" "+row.size()+" "+ ((Vector)result2[0].get(j)).size());
823: // System.out.println(command);
824: for (int k = 0; k < row.size(); ++k)
825: {
826: obj = row.get(k);
827: if (obj instanceof ArrayList)
828: if (obj instanceof ArrayList)
829: obj = formatFileMakerArray((List) obj,delimiter);
830:
831: String str = (obj == null) ? "NULL" : obj.toString();
832: if (!str.equals("NULL"))
833: pstm.setString(k + 1, str);
834: else
835: pstm.setNull(k + 1, Types.NULL);
836: }
837: pstm.execute();
838: //stm.executeUpdate(command.toString());
839: if (dialog != null)
840: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
841: // System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
842: command = null;
843: } // to while loop
844: return command;
845: }
846:
847: /**
848: * removes special characters from the input string as well as .fp5
849: * @param newName String to change
850: * @return
851: */
852: public static String convertText(String newName)
853: {
854: StringBuffer alterMe = new StringBuffer(newName.trim().toLowerCase());
855: int length = alterMe.length();
856: int j = 0;
857: int index = alterMe.indexOf(".fp5");
858: if (index >= 0)
859: {
860: alterMe.delete(index, index + 4);
861: length = length - 4;
862: }
863:
864: while (j < length)
865: {
866: if (alterMe.charAt(j) == ' ')
867: {
868: alterMe.setCharAt(j, '_');
869: // if(j<length-1) j=j+1;
870: } else if (alterMe.charAt(j) == '_')
871: {
872:
873: if (alterMe.charAt(j + 1) == '_')
874: alterMe.deleteCharAt(j);
875: length = length - 1;
876: // if(j<length-1) j=j+1;
877: } else if (alterMe.charAt(j) == 'ä')
878: {
879: alterMe.setCharAt(j, 'a');
880: alterMe.insert(j + 1, "e");
881: length = length + 1;
882: if (j < length - 1)
883: j = j + 1;
884: } else if (alterMe.charAt(j) == 'ö')
885: {
886: alterMe.setCharAt(j, 'o');
887: alterMe.insert(j + 1, "e");
888: length = length + 1;
889: if (j < length - 1)
890: j = j + 1;
891: } else if (alterMe.charAt(j) == 'ü')
892: {
893: alterMe.setCharAt(j, 'u');
894: alterMe.insert(j + 1, "e");
895: length = length + 1;
896: if (j < length - 1)
897: j = j + 1;
898: } else if (alterMe.charAt(j) == 'ß')
899: {
900: alterMe.setCharAt(j, 's');
901: alterMe.insert(j + 1, "s");
902: length = length + 1;
903: if (j < length - 1)
904: j = j + 1;
905: } else if (alterMe.charAt(j) == ':')
906: {
907: if (j < length - 1)
908: {
909: if (alterMe.charAt(j + 1) == ':')
910: {
911: alterMe.setCharAt(j, '_');
912: alterMe.delete(j + 1, j + 2);
913: length = length - 1;
914:
915: }
916:
917: if (j < length - 1)
918: j = j + 1;
919: }
920: } else if (alterMe.charAt(j) == '-')
921: {
922: alterMe.setCharAt(j, '_');
923:
924: } else if (alterMe.charAt(j) == '?')
925: {
926: // changed ? to _ because of update statement
927: alterMe.setCharAt(j, '_');
928: // length = length + 1;
929: // j=j+1;
930: System.out.println(alterMe);
931: } else if (alterMe.charAt(j) == '.')
932: {
933: if (j == length - 1)
934: {
935: alterMe.delete(j, j);
936: length--;
937: } else
938: alterMe.setCharAt(j, '_');
939: }
940:
941: ++j;
942: }
943: return alterMe.toString();
944: }
945: /**
946: * Converts > and < in an entity (> or <)
947: * @param newName
948: * @return
949: */
950: public static String convertToEntities(String newName)
951: {
952: StringBuffer alterMe = new StringBuffer(newName.trim());
953: int length = alterMe.length();
954: int j = 0;
955:
956: while (j < length)
957: {
958:
959: if (alterMe.charAt(j) == '>')
960: {
961: alterMe.setCharAt(j, '&');
962: alterMe.insert(j + 1, "gt;");
963: length = length + 2;
964: if (j < length - 1)
965: j = j + 1;
966:
967: } else if (alterMe.charAt(j) == '<')
968: {
969: alterMe.setCharAt(j, '&');
970: alterMe.insert(j + 1, "lt;");
971: length = length + 2;
972: if (j < length - 1)
973: j = j + 1;
974:
975: }
976: ++j;
977: }
978: return alterMe.toString();
979: }
980: /**
981: * Masks the single quote character '-->\'
982: * @param newName
983: * @return
984: */
985: public static String convertUml(String newName)
986: {
987: StringBuffer alterMe = new StringBuffer(newName.trim());
988: int length = alterMe.length();
989: int j = 0;
990:
991: while (j < length)
992: {
993:
994: if (alterMe.charAt(j) == '\'')
995: {
996: alterMe.setCharAt(j, '\\');
997: alterMe.insert(j + 1, "'");
998: length = length + 1;
999: if (j < length - 1)
1000: j = j + 1;
1001: }
1002: /* else
1003: if (alterMe.charAt(j) == '"')
1004: {
1005: alterMe.setCharAt(j, '\\');
1006: alterMe.insert(j + 1, "\"");
1007: length = length + 1;
1008: if(j<length-1) j=j+1;
1009: }
1010: else
1011: if (alterMe.charAt(j) == '>')
1012: {
1013: alterMe.setCharAt(j, '\\');
1014: alterMe.insert(j + 1, ">");
1015: length = length + 1;
1016: if(j<length-1) j=j+1;
1017: }
1018: else
1019: if (alterMe.charAt(j) == '<')
1020: {
1021: alterMe.setCharAt(j, '\\');
1022: alterMe.insert(j + 1, "<");
1023: length = length + 1;
1024: if(j<length-1) j=j+1;
1025: }
1026: else
1027: if (alterMe.charAt(j) == '?')
1028: {
1029: alterMe.setCharAt(j, '\\');
1030: alterMe.insert(j + 1, "?");
1031: length = length + 1;
1032: if(j<length-1) j=j+1;
1033: }
1034: else
1035: if (alterMe.charAt(j) == '&')
1036: {
1037: alterMe.setCharAt(j, '\\');
1038: alterMe.insert(j + 1, "&");
1039: length = length + 1;
1040: if(j<length-1) j=j+1;
1041: }
1042: else
1043: if (alterMe.charAt(j) == '=')
1044: {
1045: alterMe.setCharAt(j, '\\');
1046: alterMe.insert(j + 1, "=");
1047: length = length + 1;
1048: if(j<length-1) j=j+1;
1049: }
1050: else
1051: if (alterMe.charAt(j) == ',')
1052: {
1053: alterMe.setCharAt(j, '\\');
1054: alterMe.insert(j + 1, ",");
1055: length = length + 1;
1056: if(j<length-1) j=j+1;
1057: }
1058: else
1059: if (alterMe.charAt(j) == '.')
1060: {
1061: alterMe.setCharAt(j, '\\');
1062: alterMe.insert(j + 1, ".");
1063: length = length + 1;
1064: if(j<length-1) j=j+1;
1065: }
1066: else
1067: if (alterMe.charAt(j) == '[')
1068: {
1069: alterMe.setCharAt(j, '\\');
1070: alterMe.insert(j + 1, ".");
1071: length = length + 1;
1072: if(j<length-1) j=j+1;
1073: }
1074: else
1075: if (alterMe.charAt(j) == ']')
1076: {
1077: alterMe.setCharAt(j, '\\');
1078: alterMe.insert(j + 1, ".");
1079: length = length + 1;
1080: if(j<length-1) j=j+1;
1081: }
1082: else
1083: if (alterMe.charAt(j) == '%')
1084: {
1085: alterMe.setCharAt(j, '\\');
1086: alterMe.insert(j + 1, "%");
1087: length = length + 1;
1088: if(j<length-1) j=j+1;
1089: }*/
1090: ++j;
1091: }
1092: return alterMe.toString();
1093: }
1094: /**
1095: * parses the input xml file for batch conversion
1096: * called from readXMLFile
1097: * * @param sb
1098: */
1099: public static void parseXMLConfig(StringBuffer sb)
1100: {
1101: boolean finished = false;
1102: // parse string and build document tree
1103: Xparse parser = new Xparse();
1104: parser.changeEntities = true;
1105: Node root = parser.parse(sb.toString());
1106: // printContents(root);
1107: Vector databases = new Vector();
1108: Vector tables = new Vector();
1109: Vector layouts = new Vector();
1110: Vector selects = new Vector();
1111: Vector creates = new Vector();
1112: Vector ids = new Vector();
1113: String delimiter = "|";
1114: int mode = -1;
1115:
1116: try
1117: {
1118: Node tempNode = root.find("convert/source", new int[] { 1, 1 });
1119: if (tempNode == null)
1120: throw new Error("parse error source tag missing");
1121: System.out.println(tempNode.name);
1122: int length = countNodes(tempNode);
1123: for (int i = 1; i <= length; i++)
1124: {
1125:
1126: DBBean database = new DBBean();
1127: tables = new Vector();
1128: layouts = new Vector();
1129: selects = new Vector();
1130: creates = new Vector();
1131: ids = new Vector();
1132: // parse dataBase
1133: Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
1134: Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
1135: Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
1136: Node node3 = root.find("convert/source/database", new int[] { 1, 1, i });
1137: Node nodeMode = root.find("convert/source/database/mode", new int[] { 1, 1, i, 1, 1 });
1138: Node delimiterNode =root.find("convert/source/database/delimiter", new int[] { 1, 1, i, 1, 1 });
1139: if (node3 == null)
1140: throw new Error("parse error database tag missing");
1141: if (node == null)
1142: throw new Error("parse error url tag missing");
1143: if (node1 == null)
1144: throw new Error("parse error user tag missing");
1145: if (node2 == null)
1146: throw new Error("parse error password tag missing");
1147: if(delimiterNode!=null) delimiter = delimiterNode.getCharacters();
1148: String url = node.getCharacters();
1149: String user = node1.getCharacters();
1150: String password = node2.getCharacters();
1151: database.setURL(url.trim());
1152: database.setUserAndPasswd(user.trim(), password.trim());
1153: System.out.println(node.name + " " + node.getCharacters());
1154: System.out.println(node1.name + " " + node1.getCharacters());
1155: System.out.println(node2.name + " " + node2.getCharacters());
1156: String modeString = "";
1157: if (nodeMode == null)
1158: modeString = "convert";
1159: else
1160: modeString = nodeMode.getCharacters();
1161: if (modeString.equals("convert"))
1162: mode = DataBase.CONVERT_MODE;
1163: else if (modeString.equals("append"))
1164: mode = DataBase.APPEND_MODE;
1165: else if (modeString.equals("update"))
1166: mode = DataBase.UPDATE_MODE;
1167: else if (modeString.equals("delete"))
1168: mode = DataBase.DELETE_MODE;
1169:
1170: // if(node3!=null)
1171: // System.out.println(node3.name);
1172:
1173: int length2 = countNodes(node3);
1174:
1175: System.out.println("number of tables " + length2);
1176:
1177: for (int j = 1; j <= length2; ++j)
1178: {
1179: Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
1180: Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
1181: Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
1182: if (node4 != null)
1183: System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
1184: if (node5 != null)
1185: System.out.println(node5.name + " " + node5.getCharacters());
1186: if (node6 != null)
1187: System.out.println(node6.name + " " + node6.getCharacters());
1188: if (node4 == null)
1189: throw new Error("parse error table tag missing");
1190: // if(node5==null) throw new Error("parse error select tag missing");
1191: // if(node6==null) throw new Error("parse error create tag missing");
1192: String name = (String) node4.attributes.get("name");
1193: String layout = (String) node4.attributes.get("layout");
1194: String id = (String) node4.attributes.get("id");
1195: System.out.println("id was " + id);
1196: if (name == null)
1197: throw new Error("parse error required table tag attribute name missing");
1198: if (layout == null)
1199: layout = "";
1200: if (id == null)
1201: id = "";
1202: if (name.equals(""))
1203: throw new Error("parse error table tag attribute must not be empty");
1204: tables.add(name);
1205: layouts.add(layout);
1206: ids.add(id);
1207: String query = (node5 == null) ? "" : node5.getCharacters();
1208: if (query.equals(""))
1209: System.err.println("Warning empty select tag or select tag missing !!");
1210: query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
1211: selects.add(query);
1212: if (node6 != null)
1213: creates.add(node6.getCharacters().trim());
1214: else
1215: creates.add("");
1216:
1217: }
1218: DataBase dataBase = new DataBase(database, tables, layouts, selects, creates, ids, mode);
1219: dataBase.delimiter = delimiter;
1220: databases.add(dataBase);
1221: }
1222: DBBean database = new DBBean();
1223: // parse dataBase
1224: Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
1225: Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
1226: Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
1227: String url = node.getCharacters();
1228: String user = node1.getCharacters();
1229: String password = node2.getCharacters();
1230: System.out.println(url);
1231: database.setURL(url.trim());
1232: database.setUserAndPasswd(user.trim(), password.trim());
1233: //databases.add(database);
1234: for (Iterator iter = databases.iterator(); iter.hasNext();)
1235: {
1236: DataBase db = (DataBase) iter.next();
1237: if (mode != DataBase.UPDATE_MODE)
1238: convertBatch(db.bean, database, db.tables, db.layouts, db.selects, db.creates, db.ids,mode,db.delimiter);
1239: else
1240: update(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates, db.ids, mode);
1241:
1242: }
1243: // printContents(node3);
1244: // FM2SQL.fmInstance=new FM2SQL();
1245: } catch (Exception e)
1246: {
1247:
1248: e.printStackTrace();
1249: }
1250: }
1251: public static Vector getXMLConfig(String xmlFile)
1252: {
1253: StringBuffer sb = null;
1254: try
1255: {
1256: // read XML Metadata from a file
1257: FileInputStream fi = new FileInputStream(xmlFile);
1258: InputStreamReader isr = new InputStreamReader(fi, "UTF-8");
1259: BufferedReader buffr = new BufferedReader(isr);
1260: sb = new StringBuffer();
1261: int c = 0;
1262: while ((c = buffr.read()) != -1)
1263: {
1264: char ch = (char) c;
1265: sb.append(ch);
1266: // System.out.print((char)c);
1267: }
1268:
1269: } catch (Exception e)
1270: {
1271: e.printStackTrace();
1272: }
1273:
1274: boolean finished = false;
1275: // parse string and build document tree
1276: Xparse parser = new Xparse();
1277: parser.changeEntities = true;
1278: Node root = parser.parse(sb.toString());
1279: // printContents(root);
1280: Vector databases = new Vector();
1281: Vector tables = new Vector();
1282: Vector layouts = new Vector();
1283: Vector selects = new Vector();
1284: Vector creates = new Vector();
1285: Vector ids = new Vector();
1286: String delimiter = "|";
1287: int mode = -1;
1288: try
1289: {
1290: Node tempNode = root.find("convert/source", new int[] { 1, 1 });
1291: if (tempNode == null)
1292: throw new Error("parse error source tag missing");
1293: System.out.println(tempNode.name);
1294: int length = countNodes(tempNode);
1295: for (int i = 1; i <= length; i++)
1296: {
1297:
1298: DBBean database = new DBBean();
1299: tables = new Vector();
1300: layouts = new Vector();
1301: selects = new Vector();
1302: creates = new Vector();
1303: ids = new Vector();
1304: // parse dataBase
1305: Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
1306: Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
1307: Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
1308: Node node3 = root.find("convert/source/database", new int[] { 1, 1, i });
1309: Node nodeMode = root.find("convert/source/database/mode", new int[] { 1, 1, i, 1, 1 });
1310: Node delimiterNode =root.find("convert/source/database/delimiter", new int[] { 1, 1, i, 1, 1 });
1311:
1312: if (delimiterNode != null)
1313: delimiter = delimiterNode.getCharacters();
1314: if (node3 == null)
1315: throw new Error("parse error database tag missing");
1316: if (node == null)
1317: throw new Error("parse error url tag missing");
1318: if (node1 == null)
1319: throw new Error("parse error user tag missing");
1320: if (node2 == null)
1321: throw new Error("parse error password tag missing");
1322: String url = node.getCharacters();
1323: String user = node1.getCharacters();
1324: String password = node2.getCharacters();
1325: database.setURL(url.trim());
1326: database.setUserAndPasswd(user.trim(), password.trim());
1327: System.out.println(node.name + " " + node.getCharacters());
1328: System.out.println(node1.name + " " + node1.getCharacters());
1329: System.out.println(node2.name + " " + node2.getCharacters());
1330: String modeString = "";
1331: if (nodeMode == null)
1332: modeString = "convert";
1333: else
1334: modeString = nodeMode.getCharacters();
1335: if (modeString.equals("convert"))
1336: mode = DataBase.CONVERT_MODE;
1337: else if (modeString.equals("append"))
1338: mode = DataBase.APPEND_MODE;
1339: else if (modeString.equals("update"))
1340: mode = DataBase.UPDATE_MODE;
1341: else if (modeString.equals("delete"))
1342: mode = DataBase.DELETE_MODE;
1343:
1344: // if(node3!=null)
1345: // System.out.println(node3.name);
1346:
1347: int length2 = countNodes(node3);
1348:
1349: System.out.println("number of tables " + length2);
1350:
1351: for (int j = 1; j <= length2; ++j)
1352: {
1353: Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
1354: Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
1355: Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
1356: if (node4 != null)
1357: System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
1358: if (node5 != null)
1359: System.out.println(node5.name + " " + node5.getCharacters());
1360: if (node6 != null)
1361: System.out.println(node6.name + " " + node6.getCharacters());
1362: if (node4 == null)
1363: throw new Error("parse error table tag missing");
1364: // if(node5==null) throw new Error("parse error select tag missing");
1365: // if(node6==null) throw new Error("parse error create tag missing");
1366: String name = (String) node4.attributes.get("name");
1367: String layout = (String) node4.attributes.get("layout");
1368: String id = (String) node4.attributes.get("id");
1369: System.out.println("id was " + id);
1370:
1371: if (name == null)
1372: throw new Error("parse error required table tag attribute name missing");
1373: if (layout == null)
1374: layout = "";
1375: if (id == null)
1376: id = "";
1377: if (name.equals(""))
1378: throw new Error("parse error table tag attribute must not be empty");
1379: tables.add(name);
1380: layouts.add(layout);
1381: ids.add(id);
1382: String query = (node5 == null) ? "" : node5.getCharacters();
1383: if (query.equals(""))
1384: System.err.println("Warning empty select tag or select tag missing !!");
1385: query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
1386: selects.add(query);
1387: if (node6 != null)
1388: creates.add(node6.getCharacters().trim());
1389: else
1390: creates.add("");
1391:
1392: }
1393: DataBase dataBase=new DataBase(database, tables, layouts, selects, creates, ids, mode);
1394: dataBase.delimiter=delimiter;
1395: databases.add(dataBase);
1396: }
1397: DBBean database = new DBBean();
1398: // parse dataBase
1399: Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
1400: Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
1401: Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
1402: String url = node.getCharacters();
1403: String user = node1.getCharacters();
1404: String password = node2.getCharacters();
1405: System.out.println(url);
1406: database.setURL(url.trim());
1407: database.setUserAndPasswd(user.trim(), password.trim());
1408: databases.add(new DataBase(database, null, null, null, null, null, 0));
1409: //databases.add(database);
1410: /* for (Iterator iter = databases.iterator(); iter.hasNext();)
1411: {
1412: DataBase db = (DataBase) iter.next();
1413: convertBatch(db.bean,database,db.tables,db.layouts,db.selects,db.creates);
1414:
1415: }*/
1416: // printContents(node3);
1417: // FM2SQL.fmInstance=new FM2SQL();
1418: } catch (Exception e)
1419: {
1420: // TODO Auto-generated catch block
1421: e.printStackTrace();
1422: }
1423: return databases;
1424: }
1425:
1426: private static int countNodes(Node tempNode)
1427: {
1428: int length = 0;
1429: for (int i = 0; i < tempNode.contents.v.size(); ++i)
1430: {
1431: Node node = (Node) tempNode.contents.v.elementAt(i);
1432: if (node.type.equals("element"))
1433: {
1434: if (node.name.equals("database"))
1435: length++;
1436: if (node.name.equals("table"))
1437: length++;
1438: }
1439:
1440: // System.out.println(((Node)tempNode.contents.v.elementAt(i)).attributes+" "+i);
1441: }
1442: return length;
1443: }
1444: private static void printContents(Node root)
1445: {
1446:
1447: Vector contents = (root.index == null) ? root.contents.v : root.index.v;
1448: for (int i = 0; i < contents.size(); ++i)
1449: {
1450: Node n = (Node) contents.elementAt(i);
1451: if (n.type.equals("element"))
1452: {
1453: System.out.println("tag " + n.name);
1454: System.out.println(n.getCharacters());
1455: //contents=n.contents.v i=0;
1456: }
1457: // System.out.println(n.type);
1458: }
1459: }
1460: /**
1461: * reads the specified xml file
1462: * @param xmlFile
1463: */
1464: public static void readXMLFile(String xmlFile)
1465: {
1466: try
1467: {
1468: // read XML Metadata from a file
1469: FileInputStream fi = new FileInputStream(xmlFile);
1470: InputStreamReader isr = new InputStreamReader(fi, "UTF-8");
1471: BufferedReader buffr = new BufferedReader(isr);
1472: StringBuffer sb = new StringBuffer();
1473: int c = 0;
1474: while ((c = buffr.read()) != -1)
1475: {
1476: char ch = (char) c;
1477: sb.append(ch);
1478: // System.out.print((char)c);
1479: }
1480: parseXMLConfig(sb);
1481: } catch (Exception e)
1482: {
1483: e.printStackTrace();
1484: }
1485: }
1486:
1487: /**
1488: * Helper class for XML-File parsing
1489: * Holds the parsed data
1490: * @author rogo
1491: *
1492: */
1493: public static class DataBase
1494: {
1495: DBBean bean;
1496: Vector creates;
1497: Vector selects;
1498: Vector layouts;
1499: Vector tables;
1500: Vector ids;
1501: String delimiter = "//";
1502: final static int CONVERT_MODE = 1;
1503: final static int APPEND_MODE = 2;
1504: final static int UPDATE_MODE = 3;
1505: final static int DELETE_MODE = 4;
1506:
1507: int mode = -1;
1508:
1509: public DataBase(DBBean bean, Vector tables, Vector layouts, Vector selects, Vector creates, Vector ids, int mode)
1510: {
1511: this.bean = bean;
1512: this.tables = tables;
1513: this.layouts = layouts;
1514: this.selects = selects;
1515: this.creates = creates;
1516: this.ids = ids;
1517: this.mode = mode;
1518: this.bean.setIDVector(ids);
1519: }
1520: /**
1521: * writes the data contained in this object to the buffered writer
1522: * * @param buffr
1523: * @throws Exception
1524: */
1525: public void exportToXML(BufferedWriter buffr) throws Exception
1526: {
1527: // ids=bean.getIDVector();
1528: buffr.write(" <database>\n");
1529: buffr.write(" <url>" + bean.url + "</url>\n");
1530: buffr.write(" <user>" + bean.user + "</user>\n");
1531: buffr.write(" <password>" + bean.passwd + "</password>\n");
1532: buffr.write(" <delimiter>"+delimiter+"</delimiter>\n");
1533: String modeString = "";
1534: if (mode == CONVERT_MODE)
1535: modeString = "convert";
1536: else if (mode == APPEND_MODE)
1537: modeString = "append";
1538: else if (mode == UPDATE_MODE)
1539: modeString = "update";
1540: else if (mode == DELETE_MODE)
1541: modeString = "delete";
1542:
1543: buffr.write(" <mode>" + modeString + "</mode>\n");
1544: int index = 0;
1545: while (index < tables.size())
1546: {
1547: String table = (String) tables.get(index);
1548: String layout = (String) layouts.get(index);
1549: String select = (String) selects.get(index);
1550: String create = (String) creates.get(index);
1551: String id = (String) ids.get(index);
1552:
1553: buffr.write(" <table name = \"" + table + "\" layout = \"" + layout + "\" id = \"" + id + "\" >\n");
1554: buffr.write(" <select>" + convertToEntities(select) + "</select>\n");
1555: if (!create.equals(""))
1556: buffr.write(" <create>" + create + " </create>\n");
1557: buffr.write(" </table>\n");
1558: index++;
1559: }
1560: buffr.write(" </database>\n");
1561: }
1562: public String toString()
1563: {
1564: return bean.url + " " + tables;
1565: }
1566:
1567: }
1568: public static String convertToUTF8(Object command)
1569: {
1570: String str = null;
1571: try
1572: {
1573: str = new String(command.toString().getBytes("UTF-8"));
1574: } catch (UnsupportedEncodingException e)
1575: {
1576: // TODO Auto-generated catch block
1577: e.printStackTrace();
1578: }
1579: return str;
1580: }
1581: public static void writeConfig(String file, DataBase source, DataBase destination) throws Exception
1582: {
1583: if (!file.toLowerCase().endsWith(".xml"))
1584: file += ".xml";
1585: File f = new File(file);
1586:
1587: FileOutputStream fout = new FileOutputStream(f);
1588: OutputStreamWriter outsw = new OutputStreamWriter(fout, "UTF-8");
1589: BufferedWriter buffw = new BufferedWriter(outsw);
1590: buffw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
1591: buffw.newLine();
1592: buffw.write("<convert>\n");
1593: buffw.write(" <source>\n");
1594: source.exportToXML(buffw);
1595: buffw.write(" </source>\n");
1596: buffw.write("\n <destination>\n");
1597: destination.exportToXML(buffw);
1598: buffw.write(" </destination>\n");
1599: buffw.write("</convert>\n");
1600: buffw.close();
1601: }
1602: public static void delete(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
1603: {
1604: FM2SQL.ProgressDialog dialog = null;
1605: if (FM2SQL.fmInstance != null)
1606: {
1607: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance);
1608: dialog.setTitle("Conversion running ...");
1609: dialog.title.setText("Getting table data ...");
1610: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
1611: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
1612: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
1613: dialog.thread = Thread.currentThread();
1614: }
1615: // setting user and passwd
1616: bean.setUserAndPasswd(user, passwd);
1617: // setting user and passwd
1618: beanDest.setUserAndPasswd(userDest, passwdDest);
1619: if (dialog != null)
1620: dialog.setSize(400, 250);
1621: StringBuffer command = null;
1622: String query = null;
1623: try
1624: {
1625: //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
1626: //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
1627: bean.setConnection(source);
1628: if (names == null)
1629: names = bean.getTableNames();
1630: // Collections.sort(names);
1631: int tbIndex = 1;
1632:
1633: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
1634: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
1635: {
1636: Vector[] result = null;
1637: java.util.TreeSet myIds = new TreeSet();
1638: java.util.TreeSet myIdsDest = new TreeSet();
1639: int deltaID = 1;
1640: String idField = "";
1641: String destTableName = "";
1642:
1643: try
1644: {
1645: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
1646: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
1647: query = (selects != null) ? selects.get(tbIndex).toString() : query;
1648: //if vectors[1].get(i) != null)
1649: if (layout != "")
1650: {
1651: layout = " layout " + bean.getQC() + layout + bean.getQC();
1652: String name = names.get(tbIndex).toString();
1653: StringBuffer queryLayout = new StringBuffer(query);
1654: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
1655: query = queryLayout.toString();
1656: System.out.println("added layout " + query);
1657:
1658: }
1659: dialog.title.setText("Getting table data ...");
1660: dialog.table.setText(names.get(tbIndex).toString());
1661: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
1662: dialog.show();
1663: bean.getConnection();
1664: bean.makeQuery(query, 50);
1665: idField = ids.get(tbIndex).toString();
1666:
1667: } catch (Exception e)
1668: {
1669: continue;
1670: }
1671: // determine destTableName from createStatement or from source table name
1672: if (!creates.get(tbIndex).equals(""))
1673: {
1674: String create = creates.get(tbIndex).toString().toLowerCase();
1675: int fromIndex = create.indexOf("table") + 5;
1676: int toIndex = create.indexOf("(");
1677: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
1678: System.out.println("destTable " + destTableName);
1679:
1680: } else
1681: destTableName = convertText(names.get(tbIndex).toString());
1682:
1683: // for id kram
1684: Vector vec = null;
1685: Vector vecDest = null;
1686: // tempo
1687: beanDest.setConnection(destination);
1688: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
1689: String tempID = bean.getQC() + idField + bean.getQC();
1690: String tempIDdest = beanDest.getQC() + convertText(idField) + beanDest.getQC();
1691:
1692: int endIndex = -1;
1693: String tempQuery = query;
1694: String destQuery = query.replaceAll(names.get(tbIndex).toString(), destTableName);
1695: String tempQueryDest = destQuery;
1696: // remove extra query parts destQuery.substring(0,destQuery.lastIndexOf(destTableName)+destTableName.length()+1);
1697: System.out.println("new Query " + tempQueryDest);
1698: if (!idField.equals(""))
1699: {
1700: long startTime = System.currentTimeMillis();
1701: int counter = -1;
1702: while (true)
1703: {
1704: ++counter;
1705: if (counter == 0 && dialog != null)
1706: dialog.title.setText("Check if data is available");
1707: else if (dialog != null)
1708: dialog.title.setText("Check if more data is available");
1709: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, numHits);
1710: myIdsDest = beanDest.getIDVector(convertText(idField), destTableName, tempQueryDest, numHits);
1711: if (myIds.isEmpty())
1712: break;
1713: vec = new Vector(myIds);
1714: vecDest = new Vector(myIdsDest);
1715: rowCount = vec.size();
1716: // Deletion will work this way
1717: Vector deleted = new Vector(vec);
1718: Vector linesToDelete = new Vector(vecDest);
1719: // remove all lines that should not be deleted
1720: linesToDelete.removeAll(deleted);
1721: // System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIdsDest.size());
1722: /// @TODO complete delete task remove query show lines to be deleted let user choose if he wants that
1723: System.out.println("number of lines to be deleted " + linesToDelete.size());
1724: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
1725: beanDest.setConnection(destination);
1726:
1727: Statement stm = beanDest.getConnection().createStatement();
1728:
1729: Vector tables = beanDest.getTableNames();
1730: // Collections.sort(tables);
1731: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
1732: tables = beanDest.getTableNames();
1733: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
1734: stm = beanDest.getConnection().createStatement();
1735:
1736: if (dialog != null)
1737: dialog.title.setText(" Deleting table data ...");
1738:
1739: int j = -1;
1740:
1741: Vector row = null;
1742: command = new StringBuffer();
1743:
1744: command.append("DELETE FROM");
1745: command.append(beanDest.getQC());
1746: command.append(destTableName);
1747: //command.append(convertText((String) names.get(tbIndex)));
1748: command.append(beanDest.getQC());
1749: int size = bean.getColumnNames().size();
1750: command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
1751: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
1752: System.out.println(command + " " + tbIndex);
1753: //int rowCount = bean.getRowCount(query);
1754: // int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
1755: while (true)
1756: {
1757:
1758: ++j;
1759: if (j == linesToDelete.size())
1760: break;
1761: //print rows
1762: pstm.setString(1, linesToDelete.get(j).toString());
1763: System.out.println(pstm.toString());
1764: pstm.execute();
1765: if (dialog != null)
1766: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
1767: command = null;
1768: }
1769: // prepare new query for next chunk
1770: if (query.indexOf("where") > 0)
1771: tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
1772: else
1773: tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
1774:
1775: } //to outer while
1776: } // to idfield if
1777: } // table loop
1778:
1779: } catch (Exception e)
1780: {
1781: System.out.println("Error while connecting to database " + e);
1782: if (dialog != null)
1783: {
1784: dialog.setVisible(false);
1785: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1786: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1787: }
1788: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
1789: java.io.PrintStream stream = new java.io.PrintStream(b);
1790: stream.print(command + "\n\n");
1791: e.printStackTrace(stream);
1792: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
1793:
1794: }
1795: if (dialog != null)
1796: {
1797: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1798: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1799:
1800: dialog.setVisible(false);
1801: }
1802: } // to method
1803:
1804: }
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