Annotation of FM2SQL/Convert.java, revision 1.46
1.1 rogo 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:
1.34 rogo 18: class Convert
1.1 rogo 19: {
1.34 rogo 20: static DBBean bean = new DBBean();
21: static DBBean beanDest = new DBBean();
1.1 rogo 22:
1.34 rogo 23: static String user = "", passwd = "e1nste1n";
24: static String userDest = "postgres", passwdDest = "rogo";
25: static boolean batchRun = false;
1.1 rogo 26: static Vector databases = new Vector();
1.30 rogo 27: final static int numHits = 5000;
28: final static int numIntervalls = 2;
1.1 rogo 29: public static void main(String args[])
30: {
1.34 rogo 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: }*/
1.1 rogo 40: FileOutputStream file = null;
1.34 rogo 41: if (args.length != 1)
1.1 rogo 42: {
1.34 rogo 43: System.out.println("Usage: java Convert <xml config file>");
44: System.exit(-1);
1.1 rogo 45: }
1.34 rogo 46: if (!(new File(args[0]).exists()))
47: System.exit(0);
1.1 rogo 48: try
49: {
50: file = new FileOutputStream("./log.txt");
51: } catch (FileNotFoundException e1)
52: {
53: e1.printStackTrace();
1.34 rogo 54: }
55: PrintStream stream = new PrintStream(file);
56: System.setOut(stream);
57: System.setErr(stream);
58: readXMLFile(args[0]);
59: System.out.println("Finished!");
1.1 rogo 60: //convert("jdbc:fmpro:http://141.14.237.74:8050","jdbc:postgresql://erebos/test",null,null);
61: }
1.42 rogo 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
1.1 rogo 63: {
1.34 rogo 64: bean = source;
65: beanDest = destination;
1.42 rogo 66: convert(null,null,names,layouts,selects,creates,ids,mode,delimiter);
1.34 rogo 67: if(true) return;
68: StringBuffer command = null;
1.1 rogo 69: try
70: {
71: bean.setConnection(source.url);
1.34 rogo 72: if (names == null)
73: names = bean.getTableNames();
1.1 rogo 74: //Collections.sort(names);
1.34 rogo 75: int tbIndex = 1;
76:
77: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
1.1 rogo 78: {
79: Vector[] result = null;
1.34 rogo 80: try
1.1 rogo 81: {
1.34 rogo 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);
1.1 rogo 98: //result = bean.getQueryData(query, null, 0);
1.34 rogo 99: bean.getConnection();
100: bean.makeQuery(query, 0);
101: } catch (Exception e)
1.1 rogo 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();
1.34 rogo 113: // Collections.sort(tables);
114: System.out.println("converting table " + names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
1.1 rogo 115: tables = beanDest.getTableNames();
116: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
117: stm = beanDest.getConnection().createStatement();
118: // System.exit(0);
1.34 rogo 119: if (mode == Convert.DataBase.CONVERT_MODE)
1.1 rogo 120: {
1.34 rogo 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)
1.1 rogo 127: {
1.34 rogo 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)
1.1 rogo 134: {
1.34 rogo 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());
1.1 rogo 168:
1.34 rogo 169: }
1.1 rogo 170: }
1.34 rogo 171: Vector row = null;
172: command = new StringBuffer();
1.1 rogo 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 ( ");
1.3 rogo 179:
1.34 rogo 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)
1.41 rogo 193: obj = formatFileMakerArray((List) obj,"\n");
1.34 rogo 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:
1.1 rogo 218: }
1.34 rogo 219: // dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
220: //FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1.1 rogo 221:
1.34 rogo 222: // dialog.setVisible(false);
1.1 rogo 223: }
1.41 rogo 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: }
1.38 rogo 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: */
1.34 rogo 247: public static void update(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
1.12 rogo 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;
1.45 rogo 277:
1.12 rogo 278: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
279: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
280: {
281: Vector[] result = null;
1.45 rogo 282: String destTableName = "";
1.12 rogo 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: }
1.45 rogo 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:
1.12 rogo 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);
1.34 rogo 333:
1.12 rogo 334: if (dialog != null)
1.36 rogo 335: dialog.title.setText("Updating table data ...");
1.12 rogo 336:
337: int j = -1;
1.34 rogo 338:
1.12 rogo 339: Vector row = null;
340: command = new StringBuffer();
341:
342: command.append("UPDATE ");
343: command.append(beanDest.getQC());
1.45 rogo 344: command.append(destTableName);
345: //command.append(convertText((String) names.get(tbIndex)));
1.12 rogo 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)
1.34 rogo 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()) + " = ?");
1.12 rogo 354: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
1.34 rogo 355: System.out.println(command + " " + tbIndex);
356: int rowCount = bean.getRowCount(query);
357: int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
1.12 rogo 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: }
1.34 rogo 399: pstm.setString(row.size() + 1, row.get(idIndex).toString());
400: //System.out.println(pstm.toString());
401: // System.exit(0);
1.12 rogo 402: pstm.execute();
403: //stm.executeUpdate(command.toString());
1.34 rogo 404: if (dialog != null)
405: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
1.12 rogo 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);
1.18 rogo 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: }
1.12 rogo 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: }
1.18 rogo 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: }
1.12 rogo 434:
435: }
1.34 rogo 436: /**
1.38 rogo 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:
1.42 rogo 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
1.1 rogo 453: {
1.34 rogo 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 = "";
1.38 rogo 471: String destTableName = "";
1.34 rogo 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;
1.1 rogo 481: try
482: {
1.34 rogo 483: if(source!=null)
1.1 rogo 484: bean.setConnection(source);
1.34 rogo 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)
1.1 rogo 495: {
496: Vector[] result = null;
1.34 rogo 497: try
1.1 rogo 498: {
1.34 rogo 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);
1.21 rogo 511:
1.34 rogo 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;
1.1 rogo 531: }
1.34 rogo 532: if(destination!=null)
1.1 rogo 533: beanDest.setConnection(destination);
1.34 rogo 534: else
535: beanDest.setConnection(beanDest.url);
1.1 rogo 536: Statement stm = beanDest.getConnection().createStatement();
537:
538: Vector tables = beanDest.getTableNames();
1.34 rogo 539: // Collections.sort(tables);
1.1 rogo 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);
1.45 rogo 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:
1.34 rogo 558: if (mode == Convert.DataBase.CONVERT_MODE)
1.1 rogo 559: {
1.38 rogo 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(""))
1.34 rogo 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)
1.1 rogo 576: {
1.34 rogo 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: }
1.38 rogo 581: */
582: if ((tables.indexOf(destTableName) < 0)) //&& tables.indexOf(names.get(tbIndex)) < 0 && tables.indexOf(convertText(names.get(tbIndex).toString())) < 0 )
1.1 rogo 583: {
1.38 rogo 584:
1.34 rogo 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
1.38 rogo 616: command = new StringBuffer().append(creates.get(tbIndex).toString().toLowerCase());
1.34 rogo 617: stm.executeUpdate(command.toString());
1.1 rogo 618:
1.34 rogo 619: }
1.1 rogo 620: }
1.34 rogo 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());
1.38 rogo 631: command.append(destTableName); //convertText((String) names.get(tbIndex)));
1.34 rogo 632: command.append(beanDest.getQC());
1.38 rogo 633:
1.34 rogo 634: command.append(" values ( ");
635:
1.38 rogo 636: // add a question marks for every field
1.34 rogo 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();
1.38 rogo 647: // if id_field not do incremental conversion else do it all at once
1.34 rogo 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)
1.39 rogo 676: tempQuery = query + " and " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID)+"'";
1.34 rogo 677: else
1.39 rogo 678: tempQuery = query + " where " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID)+"'";
1.34 rogo 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:
1.43 rogo 687: command = writeDatainDestTable(dialog, command, k, pstm, rowCount,delimiter);
1.34 rogo 688: endIndex = k + deltaID;
689: }
690: System.out.println(endIndex);
1.38 rogo 691: //all data written ? if not write last chunk of data
1.34 rogo 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());
1.1 rogo 697:
1.34 rogo 698: if (query.indexOf("where") > 0)
1.39 rogo 699: tempQuery = query + " and " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement()+"'";
1.34 rogo 700: else
1.39 rogo 701: tempQuery = query + " where " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement()+"'";
1.34 rogo 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 ...");
1.43 rogo 708: command = writeDatainDestTable(dialog, command, endIndex, pstm, rowCount,delimiter);
1.34 rogo 709: }
1.38 rogo 710: // prepare new query for next chunk
1.34 rogo 711: if (query.indexOf("where") > 0)
1.39 rogo 712: tempQuery = query + " and " + tempID + ">'" + vec.lastElement()+"'";
1.34 rogo 713: else
1.39 rogo 714: tempQuery = query + " where " + tempID + ">'" + vec.lastElement()+"'";
1.34 rogo 715:
716: }
717: long endTime = System.currentTimeMillis();
718: System.out.println("Time for incremental convert elapsed " + (endTime - startTime));
719: } else
1.29 rogo 720: {
1.38 rogo 721: // read and write all in one big chunk
1.34 rogo 722: long startTime = System.currentTimeMillis();
1.38 rogo 723:
1.34 rogo 724: bean.makeQuery(query, 0);
1.43 rogo 725: command = writeDatainDestTable(dialog, command, j, pstm, rowCount,delimiter);
1.34 rogo 726: long endTime = System.currentTimeMillis();
727: System.out.println("Time for old convert elapsed " + (endTime - startTime));
1.29 rogo 728:
729: }
1.34 rogo 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();
1.33 rogo 747:
1.3 rogo 748: }
1.1 rogo 749: }
1.34 rogo 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: }
1.27 rogo 756: }
1.38 rogo 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: */
1.43 rogo 768: private static StringBuffer writeDatainDestTable(FM2SQL.ProgressDialog dialog, StringBuffer command, int j, PreparedStatement pstm, int rowCount,String delimiter) throws Exception, SQLException
1.27 rogo 769: {
770: Vector row;
1.34 rogo 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)
1.41 rogo 814: if (obj instanceof ArrayList)
1.43 rogo 815: obj = formatFileMakerArray((List) obj,delimiter);
1.41 rogo 816:
1.34 rogo 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
1.27 rogo 830: return command;
1.1 rogo 831: }
832:
1.38 rogo 833: /**
834: * removes special characters from the input string as well as .fp5
835: * @param newName String to change
836: * @return
837: */
1.34 rogo 838: public static String convertText(String newName)
1.1 rogo 839: {
840: StringBuffer alterMe = new StringBuffer(newName.trim().toLowerCase());
841: int length = alterMe.length();
842: int j = 0;
1.34 rogo 843: int index = alterMe.indexOf(".fp5");
844: if (index >= 0)
845: {
846: alterMe.delete(index, index + 4);
847: length = length - 4;
848: }
849:
1.1 rogo 850: while (j < length)
1.34 rogo 851: {
1.1 rogo 852: if (alterMe.charAt(j) == ' ')
853: {
854: alterMe.setCharAt(j, '_');
1.34 rogo 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) == ':')
1.1 rogo 892: {
1.34 rogo 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: }
1.1 rogo 902:
1.34 rogo 903: if (j < length - 1)
904: j = j + 1;
905: }
906: } else if (alterMe.charAt(j) == '-')
907: {
908: alterMe.setCharAt(j, '_');
1.16 rogo 909:
1.34 rogo 910: } else if (alterMe.charAt(j) == '?')
1.3 rogo 911: {
1.34 rogo 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, '_');
1.3 rogo 925: }
1.34 rogo 926:
927: ++j;
1.1 rogo 928: }
929: return alterMe.toString();
930: }
1.38 rogo 931: /**
932: * Converts > and < in an entity (> or <)
933: * @param newName
934: * @return
935: */
1.4 rogo 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: }
1.38 rogo 966: /**
967: * Masks the single quote character '-->\'
968: * @param newName
969: * @return
970: */
1.1 rogo 971: public static String convertUml(String newName)
1.34 rogo 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: }
1.38 rogo 1080: /**
1081: * parses the input xml file for batch conversion
1082: * called from readXMLFile
1083: * * @param sb
1084: */
1.34 rogo 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();
1.42 rogo 1099: String delimiter = "|";
1.34 rogo 1100: int mode = -1;
1101:
1102: try
1.1 rogo 1103: {
1.34 rogo 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++)
1.1 rogo 1110: {
1.34 rogo 1111:
1.1 rogo 1112: DBBean database = new DBBean();
1.34 rogo 1113: tables = new Vector();
1114: layouts = new Vector();
1115: selects = new Vector();
1116: creates = new Vector();
1117: ids = new Vector();
1.1 rogo 1118: // parse dataBase
1.34 rogo 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 });
1.42 rogo 1124: Node delimiterNode =root.find("convert/source/database/delimiter", new int[] { 1, 1, i, 1, 1 });
1.34 rogo 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");
1.42 rogo 1133: if(delimiterNode!=null) delimiter = delimiterNode.getCharacters();
1.1 rogo 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());
1.34 rogo 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: // if(node3!=null)
1154: // System.out.println(node3.name);
1155:
1156: int length2 = countNodes(node3);
1157:
1158: System.out.println("number of tables " + length2);
1159:
1160: for (int j = 1; j <= length2; ++j)
1161: {
1162: Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
1163: Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
1164: Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
1165: if (node4 != null)
1166: System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
1167: if (node5 != null)
1168: System.out.println(node5.name + " " + node5.getCharacters());
1169: if (node6 != null)
1170: System.out.println(node6.name + " " + node6.getCharacters());
1171: if (node4 == null)
1172: throw new Error("parse error table tag missing");
1173: // if(node5==null) throw new Error("parse error select tag missing");
1174: // if(node6==null) throw new Error("parse error create tag missing");
1175: String name = (String) node4.attributes.get("name");
1176: String layout = (String) node4.attributes.get("layout");
1177: String id = (String) node4.attributes.get("id");
1178: System.out.println("id was " + id);
1179: if (name == null)
1180: throw new Error("parse error required table tag attribute name missing");
1181: if (layout == null)
1182: layout = "";
1183: if (id == null)
1184: id = "";
1185: if (name.equals(""))
1186: throw new Error("parse error table tag attribute must not be empty");
1187: tables.add(name);
1188: layouts.add(layout);
1189: ids.add(id);
1190: String query = (node5 == null) ? "" : node5.getCharacters();
1191: if (query.equals(""))
1192: System.err.println("Warning empty select tag or select tag missing !!");
1193: query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
1194: selects.add(query);
1195: if (node6 != null)
1196: creates.add(node6.getCharacters().trim());
1197: else
1198: creates.add("");
1199:
1200: }
1.42 rogo 1201: DataBase dataBase = new DataBase(database, tables, layouts, selects, creates, ids, mode);
1202: dataBase.delimiter = delimiter;
1203: databases.add(dataBase);
1.34 rogo 1204: }
1205: DBBean database = new DBBean();
1206: // parse dataBase
1207: Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
1208: Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
1209: Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
1210: String url = node.getCharacters();
1211: String user = node1.getCharacters();
1212: String password = node2.getCharacters();
1213: System.out.println(url);
1214: database.setURL(url.trim());
1215: database.setUserAndPasswd(user.trim(), password.trim());
1216: //databases.add(database);
1217: for (Iterator iter = databases.iterator(); iter.hasNext();)
1.1 rogo 1218: {
1219: DataBase db = (DataBase) iter.next();
1.34 rogo 1220: if (mode != DataBase.UPDATE_MODE)
1.42 rogo 1221: convertBatch(db.bean, database, db.tables, db.layouts, db.selects, db.creates, db.ids,mode,db.delimiter);
1.17 rogo 1222: else
1.34 rogo 1223: update(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates, db.ids, mode);
1.17 rogo 1224:
1.1 rogo 1225: }
1.34 rogo 1226: // printContents(node3);
1227: // FM2SQL.fmInstance=new FM2SQL();
1228: } catch (Exception e)
1229: {
1.35 rogo 1230:
1.34 rogo 1231: e.printStackTrace();
1232: }
1233: }
1234: public static Vector getXMLConfig(String xmlFile)
1235: {
1236: StringBuffer sb = null;
1237: try
1238: {
1239: // read XML Metadata from a file
1240: FileInputStream fi = new FileInputStream(xmlFile);
1241: InputStreamReader isr = new InputStreamReader(fi, "UTF-8");
1242: BufferedReader buffr = new BufferedReader(isr);
1243: sb = new StringBuffer();
1244: int c = 0;
1245: while ((c = buffr.read()) != -1)
1246: {
1247: char ch = (char) c;
1248: sb.append(ch);
1249: // System.out.print((char)c);
1.1 rogo 1250: }
1251:
1.34 rogo 1252: } catch (Exception e)
1253: {
1254: e.printStackTrace();
1255: }
1256:
1257: boolean finished = false;
1258: // parse string and build document tree
1259: Xparse parser = new Xparse();
1260: parser.changeEntities = true;
1261: Node root = parser.parse(sb.toString());
1262: // printContents(root);
1263: Vector databases = new Vector();
1264: Vector tables = new Vector();
1265: Vector layouts = new Vector();
1266: Vector selects = new Vector();
1267: Vector creates = new Vector();
1268: Vector ids = new Vector();
1.42 rogo 1269: String delimiter = "|";
1.34 rogo 1270: int mode = -1;
1271: try
1.1 rogo 1272: {
1.34 rogo 1273: Node tempNode = root.find("convert/source", new int[] { 1, 1 });
1274: if (tempNode == null)
1275: throw new Error("parse error source tag missing");
1276: System.out.println(tempNode.name);
1277: int length = countNodes(tempNode);
1278: for (int i = 1; i <= length; i++)
1.1 rogo 1279: {
1.34 rogo 1280:
1281: DBBean database = new DBBean();
1282: tables = new Vector();
1283: layouts = new Vector();
1284: selects = new Vector();
1285: creates = new Vector();
1286: ids = new Vector();
1287: // parse dataBase
1288: Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
1289: Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
1290: Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
1291: Node node3 = root.find("convert/source/database", new int[] { 1, 1, i });
1292: Node nodeMode = root.find("convert/source/database/mode", new int[] { 1, 1, i, 1, 1 });
1.42 rogo 1293: Node delimiterNode =root.find("convert/source/database/delimiter", new int[] { 1, 1, i, 1, 1 });
1294:
1.44 rogo 1295: if (delimiterNode != null)
1296: delimiter = delimiterNode.getCharacters();
1.34 rogo 1297: if (node3 == null)
1298: throw new Error("parse error database tag missing");
1299: if (node == null)
1300: throw new Error("parse error url tag missing");
1301: if (node1 == null)
1302: throw new Error("parse error user tag missing");
1303: if (node2 == null)
1304: throw new Error("parse error password tag missing");
1305: String url = node.getCharacters();
1306: String user = node1.getCharacters();
1307: String password = node2.getCharacters();
1308: database.setURL(url.trim());
1309: database.setUserAndPasswd(user.trim(), password.trim());
1310: System.out.println(node.name + " " + node.getCharacters());
1311: System.out.println(node1.name + " " + node1.getCharacters());
1312: System.out.println(node2.name + " " + node2.getCharacters());
1313: String modeString = "";
1314: if (nodeMode == null)
1315: modeString = "convert";
1316: else
1317: modeString = nodeMode.getCharacters();
1318: if (modeString.equals("convert"))
1319: mode = DataBase.CONVERT_MODE;
1320: else if (modeString.equals("append"))
1321: mode = DataBase.APPEND_MODE;
1322: else if (modeString.equals("update"))
1323: mode = DataBase.UPDATE_MODE;
1324: // if(node3!=null)
1325: // System.out.println(node3.name);
1326:
1327: int length2 = countNodes(node3);
1328:
1329: System.out.println("number of tables " + length2);
1330:
1331: for (int j = 1; j <= length2; ++j)
1332: {
1333: Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
1334: Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
1335: Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
1336: if (node4 != null)
1337: System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
1338: if (node5 != null)
1339: System.out.println(node5.name + " " + node5.getCharacters());
1340: if (node6 != null)
1341: System.out.println(node6.name + " " + node6.getCharacters());
1342: if (node4 == null)
1343: throw new Error("parse error table tag missing");
1344: // if(node5==null) throw new Error("parse error select tag missing");
1345: // if(node6==null) throw new Error("parse error create tag missing");
1346: String name = (String) node4.attributes.get("name");
1347: String layout = (String) node4.attributes.get("layout");
1348: String id = (String) node4.attributes.get("id");
1349: System.out.println("id was " + id);
1350:
1351: if (name == null)
1352: throw new Error("parse error required table tag attribute name missing");
1353: if (layout == null)
1354: layout = "";
1355: if (id == null)
1356: id = "";
1357: if (name.equals(""))
1358: throw new Error("parse error table tag attribute must not be empty");
1359: tables.add(name);
1360: layouts.add(layout);
1361: ids.add(id);
1362: String query = (node5 == null) ? "" : node5.getCharacters();
1363: if (query.equals(""))
1364: System.err.println("Warning empty select tag or select tag missing !!");
1365: query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
1366: selects.add(query);
1367: if (node6 != null)
1368: creates.add(node6.getCharacters().trim());
1369: else
1370: creates.add("");
1371:
1372: }
1.43 rogo 1373: DataBase dataBase=new DataBase(database, tables, layouts, selects, creates, ids, mode);
1374: dataBase.delimiter=delimiter;
1375: databases.add(dataBase);
1.1 rogo 1376: }
1.34 rogo 1377: DBBean database = new DBBean();
1378: // parse dataBase
1379: Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
1380: Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
1381: Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
1382: String url = node.getCharacters();
1383: String user = node1.getCharacters();
1384: String password = node2.getCharacters();
1385: System.out.println(url);
1386: database.setURL(url.trim());
1387: database.setUserAndPasswd(user.trim(), password.trim());
1388: databases.add(new DataBase(database, null, null, null, null, null, 0));
1389: //databases.add(database);
1390: /* for (Iterator iter = databases.iterator(); iter.hasNext();)
1391: {
1392: DataBase db = (DataBase) iter.next();
1393: convertBatch(db.bean,database,db.tables,db.layouts,db.selects,db.creates);
1394:
1395: }*/
1396: // printContents(node3);
1397: // FM2SQL.fmInstance=new FM2SQL();
1398: } catch (Exception e)
1399: {
1400: // TODO Auto-generated catch block
1401: e.printStackTrace();
1.1 rogo 1402: }
1.34 rogo 1403: return databases;
1404: }
1405:
1406: private static int countNodes(Node tempNode)
1407: {
1408: int length = 0;
1409: for (int i = 0; i < tempNode.contents.v.size(); ++i)
1.1 rogo 1410: {
1.34 rogo 1411: Node node = (Node) tempNode.contents.v.elementAt(i);
1412: if (node.type.equals("element"))
1.1 rogo 1413: {
1.34 rogo 1414: if (node.name.equals("database"))
1415: length++;
1416: if (node.name.equals("table"))
1417: length++;
1.1 rogo 1418: }
1.34 rogo 1419:
1420: // System.out.println(((Node)tempNode.contents.v.elementAt(i)).attributes+" "+i);
1.1 rogo 1421: }
1.34 rogo 1422: return length;
1423: }
1424: private static void printContents(Node root)
1425: {
1426:
1427: Vector contents = (root.index == null) ? root.contents.v : root.index.v;
1428: for (int i = 0; i < contents.size(); ++i)
1.1 rogo 1429: {
1.34 rogo 1430: Node n = (Node) contents.elementAt(i);
1431: if (n.type.equals("element"))
1432: {
1433: System.out.println("tag " + n.name);
1434: System.out.println(n.getCharacters());
1435: //contents=n.contents.v i=0;
1436: }
1437: // System.out.println(n.type);
1.1 rogo 1438: }
1.34 rogo 1439: }
1.38 rogo 1440: /**
1441: * reads the specified xml file
1442: * @param xmlFile
1443: */
1.34 rogo 1444: public static void readXMLFile(String xmlFile)
1.1 rogo 1445: {
1.34 rogo 1446: try
1447: {
1448: // read XML Metadata from a file
1449: FileInputStream fi = new FileInputStream(xmlFile);
1450: InputStreamReader isr = new InputStreamReader(fi, "UTF-8");
1451: BufferedReader buffr = new BufferedReader(isr);
1452: StringBuffer sb = new StringBuffer();
1453: int c = 0;
1454: while ((c = buffr.read()) != -1)
1455: {
1456: char ch = (char) c;
1457: sb.append(ch);
1458: // System.out.print((char)c);
1459: }
1460: parseXMLConfig(sb);
1461: } catch (Exception e)
1462: {
1463: e.printStackTrace();
1464: }
1.1 rogo 1465: }
1.38 rogo 1466:
1467: /**
1468: * Helper class for XML-File parsing
1469: * Holds the parsed data
1470: * @author rogo
1471: *
1472: */
1.34 rogo 1473: public static class DataBase
1474: {
1475: DBBean bean;
1476: Vector creates;
1477: Vector selects;
1478: Vector layouts;
1479: Vector tables;
1480: Vector ids;
1.43 rogo 1481: String delimiter = "//";
1.34 rogo 1482: final static int CONVERT_MODE = 1;
1483: final static int APPEND_MODE = 2;
1484: final static int UPDATE_MODE = 3;
1.40 rogo 1485: final static int DELETE_MODE = 4;
1486:
1.34 rogo 1487: int mode = -1;
1488:
1489: public DataBase(DBBean bean, Vector tables, Vector layouts, Vector selects, Vector creates, Vector ids, int mode)
1490: {
1491: this.bean = bean;
1492: this.tables = tables;
1493: this.layouts = layouts;
1494: this.selects = selects;
1495: this.creates = creates;
1496: this.ids = ids;
1497: this.mode = mode;
1498: this.bean.setIDVector(ids);
1499: }
1.38 rogo 1500: /**
1.42 rogo 1501: * writes the data contained in this object to the buffered writer
1.38 rogo 1502: * * @param buffr
1503: * @throws Exception
1504: */
1.34 rogo 1505: public void exportToXML(BufferedWriter buffr) throws Exception
1506: {
1507: // ids=bean.getIDVector();
1508: buffr.write(" <database>\n");
1509: buffr.write(" <url>" + bean.url + "</url>\n");
1510: buffr.write(" <user>" + bean.user + "</user>\n");
1511: buffr.write(" <password>" + bean.passwd + "</password>\n");
1.42 rogo 1512: buffr.write(" <delimiter>"+delimiter+"</delimiter>\n");
1.34 rogo 1513: String modeString = "";
1514: if (mode == CONVERT_MODE)
1515: modeString = "convert";
1516: else if (mode == APPEND_MODE)
1.5 rogo 1517: modeString = "append";
1.34 rogo 1518: else if (mode == UPDATE_MODE)
1519: modeString = "update";
1520:
1521: buffr.write(" <mode>" + modeString + "</mode>\n");
1522: int index = 0;
1523: while (index < tables.size())
1524: {
1525: String table = (String) tables.get(index);
1526: String layout = (String) layouts.get(index);
1527: String select = (String) selects.get(index);
1528: String create = (String) creates.get(index);
1529: String id = (String) ids.get(index);
1530:
1531: buffr.write(" <table name = \"" + table + "\" layout = \"" + layout + "\" id = \"" + id + "\" >\n");
1532: buffr.write(" <select>" + convertToEntities(select) + "</select>\n");
1533: if (!create.equals(""))
1534: buffr.write(" <create>" + create + " </create>\n");
1535: buffr.write(" </table>\n");
1536: index++;
1537: }
1538: buffr.write(" </database>\n");
1539: }
1540: public String toString()
1541: {
1542: return bean.url + " " + tables;
1543: }
1.1 rogo 1544:
1.34 rogo 1545: }
1546: public static String convertToUTF8(Object command)
1.1 rogo 1547: {
1.34 rogo 1548: String str = null;
1549: try
1550: {
1551: str = new String(command.toString().getBytes("UTF-8"));
1552: } catch (UnsupportedEncodingException e)
1553: {
1554: // TODO Auto-generated catch block
1555: e.printStackTrace();
1556: }
1557: return str;
1.1 rogo 1558: }
1559: public static void writeConfig(String file, DataBase source, DataBase destination) throws Exception
1560: {
1.34 rogo 1561: if (!file.toLowerCase().endsWith(".xml"))
1562: file += ".xml";
1.1 rogo 1563: File f = new File(file);
1.34 rogo 1564:
1565: FileOutputStream fout = new FileOutputStream(f);
1566: OutputStreamWriter outsw = new OutputStreamWriter(fout, "UTF-8");
1.1 rogo 1567: BufferedWriter buffw = new BufferedWriter(outsw);
1568: buffw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
1569: buffw.newLine();
1570: buffw.write("<convert>\n");
1571: buffw.write(" <source>\n");
1572: source.exportToXML(buffw);
1573: buffw.write(" </source>\n");
1574: buffw.write("\n <destination>\n");
1575: destination.exportToXML(buffw);
1576: buffw.write(" </destination>\n");
1577: buffw.write("</convert>\n");
1578: buffw.close();
1.34 rogo 1579: }
1.46 ! rogo 1580: public static void delete(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
! 1581: {
! 1582: FM2SQL.ProgressDialog dialog = null;
! 1583: if (FM2SQL.fmInstance != null)
! 1584: {
! 1585: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance);
! 1586: dialog.setTitle("Conversion running ...");
! 1587: dialog.title.setText("Getting table data ...");
! 1588: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
! 1589: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
! 1590: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
! 1591: dialog.thread = Thread.currentThread();
! 1592: }
! 1593: // setting user and passwd
! 1594: bean.setUserAndPasswd(user, passwd);
! 1595: // setting user and passwd
! 1596: beanDest.setUserAndPasswd(userDest, passwdDest);
! 1597: if (dialog != null)
! 1598: dialog.setSize(400, 250);
! 1599: StringBuffer command = null;
! 1600: String query = null;
! 1601: try
! 1602: {
! 1603: //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
! 1604: //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
! 1605: bean.setConnection(source);
! 1606: if (names == null)
! 1607: names = bean.getTableNames();
! 1608: // Collections.sort(names);
! 1609: int tbIndex = 1;
! 1610:
! 1611: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
! 1612: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
! 1613: {
! 1614: Vector[] result = null;
! 1615: java.util.TreeSet myIds = new TreeSet();
! 1616: java.util.TreeSet myIdsDest = new TreeSet();
! 1617: int deltaID = 1;
! 1618: String idField = "";
! 1619: String destTableName = "";
! 1620:
! 1621: try
! 1622: {
! 1623: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
! 1624: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
! 1625: query = (selects != null) ? selects.get(tbIndex).toString() : query;
! 1626: //if vectors[1].get(i) != null)
! 1627: if (layout != "")
! 1628: {
! 1629: layout = " layout " + bean.getQC() + layout + bean.getQC();
! 1630: String name = names.get(tbIndex).toString();
! 1631: StringBuffer queryLayout = new StringBuffer(query);
! 1632: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
! 1633: query = queryLayout.toString();
! 1634: System.out.println("added layout " + query);
! 1635:
! 1636: }
! 1637: dialog.title.setText("Getting table data ...");
! 1638: dialog.table.setText(names.get(tbIndex).toString());
! 1639: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
! 1640: dialog.show();
! 1641: bean.getConnection();
! 1642: bean.makeQuery(query, 50);
! 1643: idField = ids.get(tbIndex).toString();
! 1644:
! 1645: } catch (Exception e)
! 1646: {
! 1647: continue;
! 1648: }
! 1649: // determine destTableName from createStatement or from source table name
! 1650: if (!creates.get(tbIndex).equals(""))
! 1651: {
! 1652: String create = creates.get(tbIndex).toString().toLowerCase();
! 1653: int fromIndex = create.indexOf("table") + 5;
! 1654: int toIndex = create.indexOf("(");
! 1655: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
! 1656: System.out.println("destTable " + destTableName);
! 1657:
! 1658: } else
! 1659: destTableName = convertText(names.get(tbIndex).toString());
! 1660:
! 1661: // for id kram
! 1662: Vector vec = null;
! 1663: Vector vecDest = null;
! 1664: // tempo
! 1665: beanDest.setConnection(destination);
! 1666:
! 1667: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
! 1668: String tempID = bean.getQC() + idField + bean.getQC();
! 1669: String tempIDdest = beanDest.getQC() + convertText(idField) + beanDest.getQC();
! 1670:
! 1671: int endIndex = -1;
! 1672: String tempQuery = query;
! 1673: String destQuery =query.replaceAll(names.get(tbIndex).toString(),destTableName);
! 1674: String tempQueryDest = destQuery;
! 1675: System.out.println("new Query "+tempQueryDest);
! 1676: if (!idField.equals(""))
! 1677: {
! 1678: long startTime = System.currentTimeMillis();
! 1679: int counter = -1;
! 1680: while (true)
! 1681: {
! 1682: ++counter;
! 1683: if (counter == 0 && dialog != null)
! 1684: dialog.title.setText("Check if data is available");
! 1685: else if (dialog != null)
! 1686: dialog.title.setText("Check if more data is available");
! 1687: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, numHits);
! 1688: myIdsDest = beanDest.getIDVector(convertText(idField), destTableName, tempQueryDest, numHits);
! 1689: if (myIds.isEmpty())
! 1690: break;
! 1691: vec = new Vector(myIds);
! 1692: vecDest = new Vector(myIdsDest);
! 1693: rowCount = vec.size();
! 1694: // Deletion will work this way
! 1695: Vector deleted=new Vector(vec);
! 1696: deleted.remove(0);
! 1697: Vector linesToDelete=new Vector(vecDest);
! 1698: // remove all lines that should not be deleted
! 1699: linesToDelete.removeAll(deleted);
! 1700: // System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIdsDest.size());
! 1701: /// @TODO complete delete task remove query show lines to be deleted let user choose if he wants that
! 1702: System.out.println("number of lines to be deleted "+linesToDelete.size());
! 1703: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
! 1704: if (vec.size() <= numIntervalls)
! 1705: {
! 1706: endIndex = 0;
! 1707: deltaID = vec.size();
! 1708: }
! 1709: for (int k = 0; k < vec.size() - deltaID; k = k + deltaID)
! 1710: {
! 1711: System.out.println(vec.get(k) + " " + vec.get(k + deltaID) + " " + vec.lastElement());
! 1712: if (query.indexOf("where") > 0)
! 1713: tempQuery = query + " and " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID) + "'";
! 1714: else
! 1715: tempQuery = query + " where " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID) + "'";
! 1716: if (destQuery.indexOf("where") > 0)
! 1717: tempQueryDest = destQuery + " and " + tempIDdest + ">='" + vec.get(k) + "' and " + tempIDdest + "<='" + vec.get(k + deltaID) + "'";
! 1718: else
! 1719: tempQueryDest = destQuery + " where " + tempIDdest + ">='" + vecDest.get(k) + "' and " + tempIDdest + "<='" + vecDest.get(k + deltaID) + "'";
! 1720:
! 1721: System.out.println(tempQuery);
! 1722: System.out.println(tempQueryDest);
! 1723:
! 1724: if (dialog != null)
! 1725: dialog.title.setText("Reading table data ...");
! 1726:
! 1727: bean.makeQuery(tempQuery, deltaID);
! 1728: beanDest.makeQuery(tempQueryDest, deltaID);
! 1729:
! 1730: if (dialog != null)
! 1731: dialog.title.setText("Writing table data ...");
! 1732:
! 1733: // command = writeDatainDestTable(dialog, command, k, pstm, rowCount,delimiter);
! 1734: endIndex = k + deltaID;
! 1735: }
! 1736: System.out.println(endIndex);
! 1737: //all data written ? if not write last chunk of data
! 1738: if (endIndex == vec.size() - 1)
! 1739: System.out.println("fits");
! 1740: else
! 1741: {
! 1742: System.out.println(" last intervall from " + vec.get(endIndex) + " " + vec.lastElement());
! 1743:
! 1744: if (query.indexOf("where") > 0)
! 1745: tempQuery = query + " and " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement() + "'";
! 1746: else
! 1747: tempQuery = query + " where " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement() + "'";
! 1748: if (destQuery.indexOf("where") > 0)
! 1749: tempQueryDest = destQuery + " and " + tempIDdest + ">='" + vec.get(endIndex) + "' and " + tempIDdest + "<='" + vec.lastElement() + "'";
! 1750: else
! 1751: tempQueryDest = destQuery + " where " + tempIDdest + ">='" + vec.get(endIndex) + "' and " + tempIDdest + "<='" + vec.lastElement() + "'";
! 1752:
! 1753: System.out.println(tempQuery);
! 1754: if (dialog != null)
! 1755: dialog.title.setText("Reading table data ...");
! 1756: bean.makeQuery(tempQuery, 0);
! 1757: beanDest.makeQuery(tempQueryDest, 0);
! 1758:
! 1759: if (dialog != null)
! 1760: dialog.title.setText("Writing table data ...");
! 1761: // command = writeDatainDestTable(dialog, command, endIndex, pstm, rowCount,delimiter);
! 1762: }
! 1763: // prepare new query for next chunk
! 1764: if (query.indexOf("where") > 0)
! 1765: tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
! 1766: else
! 1767: tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
! 1768:
! 1769: }
! 1770: long endTime = System.currentTimeMillis();
! 1771: System.out.println("Time for incremental convert elapsed " + (endTime - startTime));
! 1772: } /*else
! 1773: {
! 1774: // read and write all in one big chunk
! 1775: long startTime = System.currentTimeMillis();
! 1776:
! 1777: bean.makeQuery(query, 0);
! 1778: command = writeDatainDestTable(dialog, command, j, pstm, rowCount,delimiter);
! 1779: long endTime = System.currentTimeMillis();
! 1780: System.out.println("Time for old convert elapsed " + (endTime - startTime));
! 1781:
! 1782: }*/
! 1783:
! 1784: if (true)
! 1785: System.exit(0);
! 1786: //beanDest.setConnection("jdbc:postgresql://erebos/test3");
! 1787: beanDest.setConnection(destination);
! 1788:
! 1789: Statement stm = beanDest.getConnection().createStatement();
! 1790:
! 1791: Vector tables = beanDest.getTableNames();
! 1792: // Collections.sort(tables);
! 1793: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
! 1794: tables = beanDest.getTableNames();
! 1795: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
! 1796: stm = beanDest.getConnection().createStatement();
! 1797: // System.exit(0);
! 1798:
! 1799: if (dialog != null)
! 1800: dialog.title.setText("Updating table data ...");
! 1801:
! 1802: int j = -1;
! 1803:
! 1804: Vector row = null;
! 1805: command = new StringBuffer();
! 1806:
! 1807: command.append("UPDATE ");
! 1808: command.append(beanDest.getQC());
! 1809: command.append(destTableName);
! 1810: //command.append(convertText((String) names.get(tbIndex)));
! 1811: command.append(beanDest.getQC());
! 1812: command.append(" SET ");
! 1813:
! 1814: int size = bean.getColumnNames().size();
! 1815: for (int i = 0; i < size - 1; ++i)
! 1816: command.append(beanDest.getQC() + convertText((String) bean.getColumnNames().get(i)) + beanDest.getQC() + " = ? ,");
! 1817: command.append(convertText((String) bean.getColumnNames().get(size - 1)) + " = ? ");
! 1818: command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
! 1819: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
! 1820: System.out.println(command + " " + tbIndex);
! 1821: //int rowCount = bean.getRowCount(query);
! 1822: int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
! 1823: while ((row = bean.getNextRow()) != null)
! 1824: {
! 1825: j++;
! 1826: //print rows
! 1827: Object obj = null;
! 1828: /* for(int k=0;k<row.size()-1;++k)
! 1829: {
! 1830: obj = row.get(k);
! 1831: //System.out.println("row "+obj+" "+k);
! 1832: if(obj!=null&&!(obj instanceof ArrayList))
! 1833: command.append("'"+convertUml(obj.toString())+"',");
! 1834: else if(obj!=null&& obj instanceof ArrayList)
! 1835: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',");
! 1836: else command.append("NULL,");
! 1837: }
! 1838: obj = row.get(row.size() - 1);
! 1839: if (obj != null && !(obj instanceof ArrayList))
! 1840: command.append("'"+convertUml(obj.toString())+"')");
! 1841: else
! 1842: if(obj!=null&& obj instanceof ArrayList)
! 1843: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')"); //command.append(obj.toString()+")");
! 1844: else command.append("NULL)");
! 1845: */
! 1846: //command.append("'"+row.get(row.size()-1)+"')");
! 1847: //command.append(" )");
! 1848: // for(int k=0;k<row.size();++k)
! 1849:
! 1850: // System.out.println();
! 1851: // System.out.println(command+" "+j+" "+row.size()+" "+ ((Vector)result2[0].get(j)).size());
! 1852: // System.out.println(command);
! 1853: for (int k = 0; k < row.size(); ++k)
! 1854: {
! 1855: obj = row.get(k);
! 1856: if (obj instanceof ArrayList)
! 1857: obj = ((List) obj).get(0);
! 1858: String str = (obj == null) ? "NULL" : obj.toString();
! 1859: if (!str.equals("NULL"))
! 1860: pstm.setString(k + 1, str);
! 1861: else
! 1862: pstm.setNull(k + 1, Types.NULL);
! 1863: }
! 1864: pstm.setString(row.size() + 1, row.get(idIndex).toString());
! 1865: //System.out.println(pstm.toString());
! 1866: // System.exit(0);
! 1867: pstm.execute();
! 1868: //stm.executeUpdate(command.toString());
! 1869: if (dialog != null)
! 1870: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
! 1871: // System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
! 1872: command = null;
! 1873: } // to for loop
! 1874:
! 1875: }
! 1876: } catch (Exception e)
! 1877: {
! 1878: System.out.println("Error while connecting to database " + e);
! 1879: if (dialog != null)
! 1880: {
! 1881: dialog.setVisible(false);
! 1882: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
! 1883: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
! 1884: }
! 1885: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
! 1886: java.io.PrintStream stream = new java.io.PrintStream(b);
! 1887: stream.print(command + "\n\n");
! 1888: e.printStackTrace(stream);
! 1889: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
! 1890:
! 1891: }
! 1892: if (dialog != null)
! 1893: {
! 1894: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
! 1895: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
! 1896:
! 1897: dialog.setVisible(false);
! 1898: }
! 1899: }// to method
! 1900:
! 1901: }
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