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