Annotation of FM2SQL/Convert.java, revision 1.40
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.34 rogo 62: public static void convertBatch(DBBean source, DBBean destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids,int mode) throws Exception
1.1 rogo 63: {
1.34 rogo 64: bean = source;
65: beanDest = destination;
66: convert(null,null,names,layouts,selects,creates,ids,mode);
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)
193: obj = ((List) obj).get(0);
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: }
224:
1.38 rogo 225: /**
226: * Method for SQL UPDATE
227: * @param source
228: * @param destination
229: * @param names
230: * @param layouts
231: * @param selects
232: * @param creates
233: * @param ids
234: * @param mode
235: * @throws Exception
236: */
1.34 rogo 237: 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 238: {
239: FM2SQL.ProgressDialog dialog = null;
240: if (FM2SQL.fmInstance != null)
241: {
242: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance);
243: dialog.setTitle("Conversion running ...");
244: dialog.title.setText("Getting table data ...");
245: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
246: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
247: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
248: dialog.thread = Thread.currentThread();
249: }
250: // setting user and passwd
251: bean.setUserAndPasswd(user, passwd);
252: // setting user and passwd
253: beanDest.setUserAndPasswd(userDest, passwdDest);
254: if (dialog != null)
255: dialog.setSize(400, 250);
256: StringBuffer command = null;
257: String query = null;
258: try
259: {
260: //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
261: //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
262: bean.setConnection(source);
263: if (names == null)
264: names = bean.getTableNames();
265: // Collections.sort(names);
266: int tbIndex = 1;
267:
268: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
269: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
270: {
271: Vector[] result = null;
272: try
273: {
274: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
275: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
276: query = (selects != null) ? selects.get(tbIndex).toString() : query;
277: //if vectors[1].get(i) != null)
278: if (layout != "")
279: {
280: layout = " layout " + bean.getQC() + layout + bean.getQC();
281: String name = names.get(tbIndex).toString();
282: StringBuffer queryLayout = new StringBuffer(query);
283: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
284: query = queryLayout.toString();
285: System.out.println("added layout " + query);
286:
287: }
288: dialog.title.setText("Getting table data ...");
289: dialog.table.setText(names.get(tbIndex).toString());
290: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
291: dialog.show();
292: bean.getConnection();
293: bean.makeQuery(query, 0);
294: } catch (Exception e)
295: {
296: continue;
297: }
298: //beanDest.setConnection("jdbc:postgresql://erebos/test3");
299: beanDest.setConnection(destination);
300:
301: Statement stm = beanDest.getConnection().createStatement();
302:
303: Vector tables = beanDest.getTableNames();
304: // Collections.sort(tables);
305: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
306: tables = beanDest.getTableNames();
307: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
308: stm = beanDest.getConnection().createStatement();
309: // System.exit(0);
1.34 rogo 310:
1.12 rogo 311: if (dialog != null)
1.36 rogo 312: dialog.title.setText("Updating table data ...");
1.12 rogo 313:
314: int j = -1;
1.34 rogo 315:
1.12 rogo 316: Vector row = null;
317: command = new StringBuffer();
318:
319: command.append("UPDATE ");
320: command.append(beanDest.getQC());
321: command.append(convertText((String) names.get(tbIndex)));
322: command.append(beanDest.getQC());
323: command.append(" SET ");
324:
325: int size = bean.getColumnNames().size();
326: for (int i = 0; i < size - 1; ++i)
1.34 rogo 327: command.append(beanDest.getQC() + convertText((String) bean.getColumnNames().get(i)) + beanDest.getQC() + " = ? ,");
328: command.append(convertText((String) bean.getColumnNames().get(size - 1)) + " = ? ");
329: command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
1.12 rogo 330: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
1.34 rogo 331: System.out.println(command + " " + tbIndex);
332: int rowCount = bean.getRowCount(query);
333: int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
1.12 rogo 334: while ((row = bean.getNextRow()) != null)
335: {
336: j++;
337: //print rows
338: Object obj = null;
339: /* for(int k=0;k<row.size()-1;++k)
340: {
341: obj = row.get(k);
342: //System.out.println("row "+obj+" "+k);
343: if(obj!=null&&!(obj instanceof ArrayList))
344: command.append("'"+convertUml(obj.toString())+"',");
345: else if(obj!=null&& obj instanceof ArrayList)
346: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',");
347: else command.append("NULL,");
348: }
349: obj = row.get(row.size() - 1);
350: if (obj != null && !(obj instanceof ArrayList))
351: command.append("'"+convertUml(obj.toString())+"')");
352: else
353: if(obj!=null&& obj instanceof ArrayList)
354: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')"); //command.append(obj.toString()+")");
355: else command.append("NULL)");
356: */
357: //command.append("'"+row.get(row.size()-1)+"')");
358: //command.append(" )");
359: // for(int k=0;k<row.size();++k)
360:
361: // System.out.println();
362: // System.out.println(command+" "+j+" "+row.size()+" "+ ((Vector)result2[0].get(j)).size());
363: // System.out.println(command);
364: for (int k = 0; k < row.size(); ++k)
365: {
366: obj = row.get(k);
367: if (obj instanceof ArrayList)
368: obj = ((List) obj).get(0);
369: String str = (obj == null) ? "NULL" : obj.toString();
370: if (!str.equals("NULL"))
371: pstm.setString(k + 1, str);
372: else
373: pstm.setNull(k + 1, Types.NULL);
374: }
1.34 rogo 375: pstm.setString(row.size() + 1, row.get(idIndex).toString());
376: //System.out.println(pstm.toString());
377: // System.exit(0);
1.12 rogo 378: pstm.execute();
379: //stm.executeUpdate(command.toString());
1.34 rogo 380: if (dialog != null)
381: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
1.12 rogo 382: // System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
383: command = null;
384: } // to for loop
385:
386: }
387: } catch (Exception e)
388: {
389: System.out.println("Error while connecting to database " + e);
1.18 rogo 390: if (dialog != null)
391: {
392: dialog.setVisible(false);
393: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
394: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
395: }
1.12 rogo 396: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
397: java.io.PrintStream stream = new java.io.PrintStream(b);
398: stream.print(command + "\n\n");
399: e.printStackTrace(stream);
400: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
401:
402: }
1.18 rogo 403: if (dialog != null)
404: {
405: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
406: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
407:
408: dialog.setVisible(false);
409: }
1.12 rogo 410:
411: }
1.34 rogo 412: /**
1.38 rogo 413: * transfers the specified array of tables to the destination database
414: and creates the table if it does not exist if it exists and mode is not append the table is dropped
415:
416: * @param source
417: * @param destination
418: * @param names
419: * @param layouts
420: * @param selects
421: * @param creates
422: * @param ids
423: * @param mode
424: * @throws Exception
425: */
426:
427:
1.34 rogo 428: public static void convert(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
1.1 rogo 429: {
1.34 rogo 430:
431: FM2SQL.ProgressDialog dialog = null;
432:
433: if (FM2SQL.fmInstance != null)
434: {
435: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance);
436: dialog.setTitle("Conversion running ...");
437: dialog.title.setText("Getting table data ...");
438: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
439: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
440: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
441: dialog.thread = Thread.currentThread();
442: dialog.setSize(400, 250);
443: }
444: java.util.TreeSet myIds = new TreeSet();
445: int deltaID = 1;
446: String idField = "";
1.38 rogo 447: String destTableName = "";
1.34 rogo 448: if (source != null && destination != null)
449: {
450: // setting user and passwd
451: bean.setUserAndPasswd(user, passwd);
452: // setting user and passwd
453: beanDest.setUserAndPasswd(userDest, passwdDest);
454: }
455: StringBuffer command = null;
456: String query = null;
1.1 rogo 457: try
458: {
1.34 rogo 459: if(source!=null)
1.1 rogo 460: bean.setConnection(source);
1.34 rogo 461: else
462: bean.setConnection(bean.url);
463:
464: if (names == null)
465: names = bean.getTableNames();
466: // Collections.sort(names);
467: int tbIndex = 1;
468:
469: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
470: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
1.1 rogo 471: {
472: Vector[] result = null;
1.34 rogo 473: try
1.1 rogo 474: {
1.34 rogo 475: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
476: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
477: query = (selects != null) ? selects.get(tbIndex).toString() : query;
478: //if vectors[1].get(i) != null)
479: if (layout != "")
480: {
481: layout = " layout " + bean.getQC() + layout + bean.getQC();
482: String name = names.get(tbIndex).toString();
483: StringBuffer queryLayout = new StringBuffer(query);
484: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
485: query = queryLayout.toString();
486: System.out.println("added layout " + query);
1.21 rogo 487:
1.34 rogo 488: }
489: // if ( layout!= "")
490: // query += " layout " + bean.getQC() + layout + bean.getQC();
491: if (dialog != null)
492: {
493: dialog.title.setText("Reading table data ...");
494: dialog.table.setText(names.get(tbIndex).toString());
495: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
496: dialog.show();
497: }
498: //result = bean.getQueryData(query, dialog, 0);
499: bean.getConnection();
500: bean.makeQuery(query, 50);
501: idField = ids.get(tbIndex).toString();
502:
503: } catch (Exception e)
504: {
505: System.out.println(e);
506: continue;
1.1 rogo 507: }
1.34 rogo 508: if(destination!=null)
1.1 rogo 509: beanDest.setConnection(destination);
1.34 rogo 510: else
511: beanDest.setConnection(beanDest.url);
1.1 rogo 512: Statement stm = beanDest.getConnection().createStatement();
513:
514: Vector tables = beanDest.getTableNames();
1.34 rogo 515: // Collections.sort(tables);
1.1 rogo 516: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
517: tables = beanDest.getTableNames();
518: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
519: stm = beanDest.getConnection().createStatement();
520: // System.exit(0);
1.34 rogo 521: if (mode == Convert.DataBase.CONVERT_MODE)
1.1 rogo 522: {
1.38 rogo 523: if(!creates.get(tbIndex).equals(""))
524: {
525: String create =creates.get(tbIndex).toString().toLowerCase();
526: int fromIndex = create.indexOf("table")+5;
527: int toIndex = create.indexOf("(");
528: destTableName = create.substring(fromIndex,toIndex).replaceAll(beanDest.getQC(),"").trim();
529: System.out.println("destTable "+destTableName);
530:
531: } else
532: destTableName = convertText(names.get(tbIndex).toString());
533:
534: if (tables.indexOf(destTableName) >= 0)
535: {
536: stm.executeUpdate("drop table " + beanDest.getQC() + destTableName + beanDest.getQC());
537: tables.remove(destTableName);
538: System.out.println("dropped table" + destTableName);
539:
540: }
541: /*
542: if(destTableName.equals(""))
1.34 rogo 543: if (tables.indexOf(names.get(tbIndex)) >= 0)
544: {
545: stm.executeUpdate("drop table " + beanDest.getQC() + names.get(tbIndex) + beanDest.getQC());
546: tables.remove((String) names.get(tbIndex));
547: System.out.println("dropped table" + names.get(tbIndex));
548: } else if (tables.indexOf(convertText(names.get(tbIndex).toString())) >= 0)
1.1 rogo 549: {
1.34 rogo 550: stm.executeUpdate("drop table " + beanDest.getQC() + convertText((String) names.get(tbIndex)) + beanDest.getQC());
551: tables.remove(convertText((String) names.get(tbIndex)));
552: System.out.println("dropped table" + names.get(tbIndex));
553: }
1.38 rogo 554: */
555: if ((tables.indexOf(destTableName) < 0)) //&& tables.indexOf(names.get(tbIndex)) < 0 && tables.indexOf(convertText(names.get(tbIndex).toString())) < 0 )
1.1 rogo 556: {
1.38 rogo 557:
1.34 rogo 558: if (creates.get(tbIndex).equals("") || creates.get(tbIndex).toString().toLowerCase().indexOf("create") < 0)
559: {
560: System.out.println("Warning empty or invalid create statement - creating one for you\n");
561:
562: command = new StringBuffer(50);
563: command.append("CREATE TABLE ");
564: command.append(beanDest.getQC());
565: command.append(convertText((String) names.get(tbIndex)));
566: command.append(beanDest.getQC());
567: command.append("(");
568: String type = null;
569: Vector columnNames = bean.getColumnNames();
570: for (int i = 0; i < columnNames.size() - 1; ++i)
571: {
572: type = bean.metaData.getColumnTypeName(i + 1);
573: // System.out.println(i+" "+result[1].get(i)+" "+type);
574: type = (type.equals("NUMBER")) ? "INT4" : type;
575: type = (type.equals("CONTAINER")) ? "TEXT" : type;
576:
577: command.append(beanDest.getQC() + convertText((String) columnNames.get(i)) + beanDest.getQC() + " " + type + ", ");
578: }
579: type = bean.metaData.getColumnTypeName(columnNames.size());
580: type = (type.equals("NUMBER")) ? "INT4" : type;
581: type = (type.equals("CONTAINER")) ? "TEXT" : type;
582: command.append(beanDest.getQC() + convertText((String) columnNames.get(columnNames.size() - 1)) + beanDest.getQC() + " " + type);
583: command.append(" )");
584:
585: // System.out.println(command);
586: // System.exit(0);
587: //command.append(DBBean.getQC());
588: } else
1.38 rogo 589: command = new StringBuffer().append(creates.get(tbIndex).toString().toLowerCase());
1.34 rogo 590: stm.executeUpdate(command.toString());
1.1 rogo 591:
1.34 rogo 592: }
1.1 rogo 593: }
1.34 rogo 594: if(dialog!=null)
595: dialog.title.setText("Writing table data ...");
596:
597: // prepare the insert statement
598: int j = -1;
599: Vector row = null;
600: command = new StringBuffer();
601:
602: command.append("INSERT INTO ");
603: command.append(beanDest.getQC());
1.38 rogo 604: command.append(destTableName); //convertText((String) names.get(tbIndex)));
1.34 rogo 605: command.append(beanDest.getQC());
1.38 rogo 606:
1.34 rogo 607: command.append(" values ( ");
608:
1.38 rogo 609: // add a question marks for every field
1.34 rogo 610: for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
611: command.append("?,");
612: command.append("?)");
613: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
614: System.out.println(command);
615: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
616: Vector vec = new Vector(myIds);
617: int endIndex = -1;
618: String tempQuery = query;
619: String tempID = bean.getQC() + idField + bean.getQC();
1.38 rogo 620: // if id_field not do incremental conversion else do it all at once
1.34 rogo 621: if (!idField.equals(""))
622: {
623: long startTime = System.currentTimeMillis();
624: int counter = -1;
625: while (true)
626: {
627: ++counter;
628: if (counter == 0&&dialog!=null)
629: dialog.title.setText("Check if data is available");
630: else
631: if(dialog!=null)
632: dialog.title.setText("Check if more data is available");
633: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, numHits);
634: if (myIds.isEmpty())
635: break;
636: vec = new Vector(myIds);
637: rowCount = vec.size();
638: System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIds.size());
639: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
640: if (vec.size() <= numIntervalls)
641: {
642: endIndex = 0;
643: deltaID = vec.size();
644: }
645: for (int k = 0; k < vec.size() - deltaID; k = k + deltaID)
646: {
647: System.out.println(vec.get(k) + " " + vec.get(k + deltaID) + " " + vec.lastElement());
648: if (query.indexOf("where") > 0)
1.39 rogo 649: tempQuery = query + " and " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID)+"'";
1.34 rogo 650: else
1.39 rogo 651: tempQuery = query + " where " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID)+"'";
1.34 rogo 652: System.out.println(tempQuery);
653: if(dialog!=null)
654: dialog.title.setText("Reading table data ...");
655:
656: bean.makeQuery(tempQuery, deltaID);
657: if(dialog!=null)
658: dialog.title.setText("Writing table data ...");
659:
660: command = writeDatainDestTable(dialog, command, k, pstm, rowCount);
661: endIndex = k + deltaID;
662: }
663: System.out.println(endIndex);
1.38 rogo 664: //all data written ? if not write last chunk of data
1.34 rogo 665: if (endIndex == vec.size() - 1)
666: System.out.println("fits");
667: else
668: {
669: System.out.println(" last intervall from " + vec.get(endIndex) + " " + vec.lastElement());
1.1 rogo 670:
1.34 rogo 671: if (query.indexOf("where") > 0)
1.39 rogo 672: tempQuery = query + " and " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement()+"'";
1.34 rogo 673: else
1.39 rogo 674: tempQuery = query + " where " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement()+"'";
1.34 rogo 675: System.out.println(tempQuery);
676: if(dialog!=null)
677: dialog.title.setText("Reading table data ...");
678: bean.makeQuery(tempQuery, 0);
679: if(dialog!=null)
680: dialog.title.setText("Writing table data ...");
681: command = writeDatainDestTable(dialog, command, endIndex, pstm, rowCount);
682: }
1.38 rogo 683: // prepare new query for next chunk
1.34 rogo 684: if (query.indexOf("where") > 0)
1.39 rogo 685: tempQuery = query + " and " + tempID + ">'" + vec.lastElement()+"'";
1.34 rogo 686: else
1.39 rogo 687: tempQuery = query + " where " + tempID + ">'" + vec.lastElement()+"'";
1.34 rogo 688:
689: }
690: long endTime = System.currentTimeMillis();
691: System.out.println("Time for incremental convert elapsed " + (endTime - startTime));
692: } else
1.29 rogo 693: {
1.38 rogo 694: // read and write all in one big chunk
1.34 rogo 695: long startTime = System.currentTimeMillis();
1.38 rogo 696:
1.34 rogo 697: bean.makeQuery(query, 0);
698: command = writeDatainDestTable(dialog, command, j, pstm, rowCount);
699: long endTime = System.currentTimeMillis();
700: System.out.println("Time for old convert elapsed " + (endTime - startTime));
1.29 rogo 701:
702: }
1.34 rogo 703: }
704: } catch (Exception e)
705: {
706: System.out.println("Error while connecting to database " + e);
707: if (dialog != null)
708: {
709: dialog.setVisible(false);
710: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
711: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
712: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
713: java.io.PrintStream stream = new java.io.PrintStream(b);
714: stream.print(command + "\n\n");
715: e.printStackTrace(stream);
716: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
717: } else
718: {
719: e.printStackTrace();
1.33 rogo 720:
1.3 rogo 721: }
1.1 rogo 722: }
1.34 rogo 723: if (dialog != null)
724: {
725: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
726: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
727: dialog.setVisible(false);
728: }
1.27 rogo 729: }
1.38 rogo 730: /**
731: * Writes data to the destination table
732: * @param dialog progress dialog
733: * @param command
734: * @param j data index for progress bar
735: * @param pstm prepared statement
736: * @param rowCount number of datasets
737: * @return command
738: * @throws Exception
739: * @throws SQLException
740: */
1.27 rogo 741: private static StringBuffer writeDatainDestTable(FM2SQL.ProgressDialog dialog, StringBuffer command, int j, PreparedStatement pstm, int rowCount) throws Exception, SQLException
742: {
743: Vector row;
1.34 rogo 744: while ((row = bean.getNextRow()) != null)
745: {
746: j++;
747: // row = (Vector) result[0].get(j);
748: /* command = new StringBuffer();
749:
750: command.append("INSERT INTO ");
751: command.append(beanDest.getQC());
752: command.append(convertText((String) names.get(tbIndex)));
753: command.append(beanDest.getQC());
754: command.append(" values ( ");
755: */
756: //print rows
757: Object obj = null;
758: /* for(int k=0;k<row.size()-1;++k)
759: {
760: obj = row.get(k);
761: //System.out.println("row "+obj+" "+k);
762: if(obj!=null&&!(obj instanceof ArrayList))
763: command.append("'"+convertUml(obj.toString())+"',");
764: else if(obj!=null&& obj instanceof ArrayList)
765: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',");
766: else command.append("NULL,");
767: }
768: obj = row.get(row.size() - 1);
769: if (obj != null && !(obj instanceof ArrayList))
770: command.append("'"+convertUml(obj.toString())+"')");
771: else
772: if(obj!=null&& obj instanceof ArrayList)
773: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')"); //command.append(obj.toString()+")");
774: else command.append("NULL)");
775: */
776: //command.append("'"+row.get(row.size()-1)+"')");
777: //command.append(" )");
778: // for(int k=0;k<row.size();++k)
779:
780: // System.out.println();
781: // System.out.println(command+" "+j+" "+row.size()+" "+ ((Vector)result2[0].get(j)).size());
782: // System.out.println(command);
783: for (int k = 0; k < row.size(); ++k)
784: {
785: obj = row.get(k);
786: if (obj instanceof ArrayList)
787: obj = ((List) obj).get(0);
788: String str = (obj == null) ? "NULL" : obj.toString();
789: if (!str.equals("NULL"))
790: pstm.setString(k + 1, str);
791: else
792: pstm.setNull(k + 1, Types.NULL);
793: }
794: pstm.execute();
795: //stm.executeUpdate(command.toString());
796: if (dialog != null)
797: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
798: // System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
799: command = null;
800: } // to while loop
1.27 rogo 801: return command;
1.1 rogo 802: }
803:
1.38 rogo 804: /**
805: * removes special characters from the input string as well as .fp5
806: * @param newName String to change
807: * @return
808: */
1.34 rogo 809: public static String convertText(String newName)
1.1 rogo 810: {
811: StringBuffer alterMe = new StringBuffer(newName.trim().toLowerCase());
812: int length = alterMe.length();
813: int j = 0;
1.34 rogo 814: int index = alterMe.indexOf(".fp5");
815: if (index >= 0)
816: {
817: alterMe.delete(index, index + 4);
818: length = length - 4;
819: }
820:
1.1 rogo 821: while (j < length)
1.34 rogo 822: {
1.1 rogo 823: if (alterMe.charAt(j) == ' ')
824: {
825: alterMe.setCharAt(j, '_');
1.34 rogo 826: // if(j<length-1) j=j+1;
827: } else if (alterMe.charAt(j) == '_')
828: {
829:
830: if (alterMe.charAt(j + 1) == '_')
831: alterMe.deleteCharAt(j);
832: length = length - 1;
833: // if(j<length-1) j=j+1;
834: } else if (alterMe.charAt(j) == 'ä')
835: {
836: alterMe.setCharAt(j, 'a');
837: alterMe.insert(j + 1, "e");
838: length = length + 1;
839: if (j < length - 1)
840: j = j + 1;
841: } else if (alterMe.charAt(j) == 'ö')
842: {
843: alterMe.setCharAt(j, 'o');
844: alterMe.insert(j + 1, "e");
845: length = length + 1;
846: if (j < length - 1)
847: j = j + 1;
848: } else if (alterMe.charAt(j) == 'ü')
849: {
850: alterMe.setCharAt(j, 'u');
851: alterMe.insert(j + 1, "e");
852: length = length + 1;
853: if (j < length - 1)
854: j = j + 1;
855: } else if (alterMe.charAt(j) == 'ß')
856: {
857: alterMe.setCharAt(j, 's');
858: alterMe.insert(j + 1, "s");
859: length = length + 1;
860: if (j < length - 1)
861: j = j + 1;
862: } else if (alterMe.charAt(j) == ':')
1.1 rogo 863: {
1.34 rogo 864: if (j < length - 1)
865: {
866: if (alterMe.charAt(j + 1) == ':')
867: {
868: alterMe.setCharAt(j, '_');
869: alterMe.delete(j + 1, j + 2);
870: length = length - 1;
871:
872: }
1.1 rogo 873:
1.34 rogo 874: if (j < length - 1)
875: j = j + 1;
876: }
877: } else if (alterMe.charAt(j) == '-')
878: {
879: alterMe.setCharAt(j, '_');
1.16 rogo 880:
1.34 rogo 881: } else if (alterMe.charAt(j) == '?')
1.3 rogo 882: {
1.34 rogo 883: // changed ? to _ because of update statement
884: alterMe.setCharAt(j, '_');
885: // length = length + 1;
886: // j=j+1;
887: System.out.println(alterMe);
888: } else if (alterMe.charAt(j) == '.')
889: {
890: if (j == length - 1)
891: {
892: alterMe.delete(j, j);
893: length--;
894: } else
895: alterMe.setCharAt(j, '_');
1.3 rogo 896: }
1.34 rogo 897:
898: ++j;
1.1 rogo 899: }
900: return alterMe.toString();
901: }
1.38 rogo 902: /**
903: * Converts > and < in an entity (> or <)
904: * @param newName
905: * @return
906: */
1.4 rogo 907: public static String convertToEntities(String newName)
908: {
909: StringBuffer alterMe = new StringBuffer(newName.trim());
910: int length = alterMe.length();
911: int j = 0;
912:
913: while (j < length)
914: {
915:
916: if (alterMe.charAt(j) == '>')
917: {
918: alterMe.setCharAt(j, '&');
919: alterMe.insert(j + 1, "gt;");
920: length = length + 2;
921: if (j < length - 1)
922: j = j + 1;
923:
924: } else if (alterMe.charAt(j) == '<')
925: {
926: alterMe.setCharAt(j, '&');
927: alterMe.insert(j + 1, "lt;");
928: length = length + 2;
929: if (j < length - 1)
930: j = j + 1;
931:
932: }
933: ++j;
934: }
935: return alterMe.toString();
936: }
1.38 rogo 937: /**
938: * Masks the single quote character '-->\'
939: * @param newName
940: * @return
941: */
1.1 rogo 942: public static String convertUml(String newName)
1.34 rogo 943: {
944: StringBuffer alterMe = new StringBuffer(newName.trim());
945: int length = alterMe.length();
946: int j = 0;
947:
948: while (j < length)
949: {
950:
951: if (alterMe.charAt(j) == '\'')
952: {
953: alterMe.setCharAt(j, '\\');
954: alterMe.insert(j + 1, "'");
955: length = length + 1;
956: if (j < length - 1)
957: j = j + 1;
958: }
959: /* else
960: if (alterMe.charAt(j) == '"')
961: {
962: alterMe.setCharAt(j, '\\');
963: alterMe.insert(j + 1, "\"");
964: length = length + 1;
965: if(j<length-1) j=j+1;
966: }
967: else
968: if (alterMe.charAt(j) == '>')
969: {
970: alterMe.setCharAt(j, '\\');
971: alterMe.insert(j + 1, ">");
972: length = length + 1;
973: if(j<length-1) j=j+1;
974: }
975: else
976: if (alterMe.charAt(j) == '<')
977: {
978: alterMe.setCharAt(j, '\\');
979: alterMe.insert(j + 1, "<");
980: length = length + 1;
981: if(j<length-1) j=j+1;
982: }
983: else
984: if (alterMe.charAt(j) == '?')
985: {
986: alterMe.setCharAt(j, '\\');
987: alterMe.insert(j + 1, "?");
988: length = length + 1;
989: if(j<length-1) j=j+1;
990: }
991: else
992: if (alterMe.charAt(j) == '&')
993: {
994: alterMe.setCharAt(j, '\\');
995: alterMe.insert(j + 1, "&");
996: length = length + 1;
997: if(j<length-1) j=j+1;
998: }
999: else
1000: if (alterMe.charAt(j) == '=')
1001: {
1002: alterMe.setCharAt(j, '\\');
1003: alterMe.insert(j + 1, "=");
1004: length = length + 1;
1005: if(j<length-1) j=j+1;
1006: }
1007: else
1008: if (alterMe.charAt(j) == ',')
1009: {
1010: alterMe.setCharAt(j, '\\');
1011: alterMe.insert(j + 1, ",");
1012: length = length + 1;
1013: if(j<length-1) j=j+1;
1014: }
1015: else
1016: if (alterMe.charAt(j) == '.')
1017: {
1018: alterMe.setCharAt(j, '\\');
1019: alterMe.insert(j + 1, ".");
1020: length = length + 1;
1021: if(j<length-1) j=j+1;
1022: }
1023: else
1024: if (alterMe.charAt(j) == '[')
1025: {
1026: alterMe.setCharAt(j, '\\');
1027: alterMe.insert(j + 1, ".");
1028: length = length + 1;
1029: if(j<length-1) j=j+1;
1030: }
1031: else
1032: if (alterMe.charAt(j) == ']')
1033: {
1034: alterMe.setCharAt(j, '\\');
1035: alterMe.insert(j + 1, ".");
1036: length = length + 1;
1037: if(j<length-1) j=j+1;
1038: }
1039: else
1040: if (alterMe.charAt(j) == '%')
1041: {
1042: alterMe.setCharAt(j, '\\');
1043: alterMe.insert(j + 1, "%");
1044: length = length + 1;
1045: if(j<length-1) j=j+1;
1046: }*/
1047: ++j;
1048: }
1049: return alterMe.toString();
1050: }
1.38 rogo 1051: /**
1052: * parses the input xml file for batch conversion
1053: * called from readXMLFile
1054: * * @param sb
1055: */
1.34 rogo 1056: public static void parseXMLConfig(StringBuffer sb)
1057: {
1058: boolean finished = false;
1059: // parse string and build document tree
1060: Xparse parser = new Xparse();
1061: parser.changeEntities = true;
1062: Node root = parser.parse(sb.toString());
1063: // printContents(root);
1064: Vector databases = new Vector();
1065: Vector tables = new Vector();
1066: Vector layouts = new Vector();
1067: Vector selects = new Vector();
1068: Vector creates = new Vector();
1069: Vector ids = new Vector();
1070: int mode = -1;
1071:
1072: try
1.1 rogo 1073: {
1.34 rogo 1074: Node tempNode = root.find("convert/source", new int[] { 1, 1 });
1075: if (tempNode == null)
1076: throw new Error("parse error source tag missing");
1077: System.out.println(tempNode.name);
1078: int length = countNodes(tempNode);
1079: for (int i = 1; i <= length; i++)
1.1 rogo 1080: {
1.34 rogo 1081:
1.1 rogo 1082: DBBean database = new DBBean();
1.34 rogo 1083: tables = new Vector();
1084: layouts = new Vector();
1085: selects = new Vector();
1086: creates = new Vector();
1087: ids = new Vector();
1.1 rogo 1088: // parse dataBase
1.34 rogo 1089: Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
1090: Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
1091: Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
1092: Node node3 = root.find("convert/source/database", new int[] { 1, 1, i });
1093: Node nodeMode = root.find("convert/source/database/mode", new int[] { 1, 1, i, 1, 1 });
1094:
1095: if (node3 == null)
1096: throw new Error("parse error database tag missing");
1097: if (node == null)
1098: throw new Error("parse error url tag missing");
1099: if (node1 == null)
1100: throw new Error("parse error user tag missing");
1101: if (node2 == null)
1102: throw new Error("parse error password tag missing");
1.1 rogo 1103: String url = node.getCharacters();
1104: String user = node1.getCharacters();
1105: String password = node2.getCharacters();
1106: database.setURL(url.trim());
1107: database.setUserAndPasswd(user.trim(), password.trim());
1.34 rogo 1108: System.out.println(node.name + " " + node.getCharacters());
1109: System.out.println(node1.name + " " + node1.getCharacters());
1110: System.out.println(node2.name + " " + node2.getCharacters());
1111: String modeString = "";
1112: if (nodeMode == null)
1113: modeString = "convert";
1114: else
1115: modeString = nodeMode.getCharacters();
1116: if (modeString.equals("convert"))
1117: mode = DataBase.CONVERT_MODE;
1118: else if (modeString.equals("append"))
1119: mode = DataBase.APPEND_MODE;
1120: else if (modeString.equals("update"))
1121: mode = DataBase.UPDATE_MODE;
1122: // if(node3!=null)
1123: // System.out.println(node3.name);
1124:
1125: int length2 = countNodes(node3);
1126:
1127: System.out.println("number of tables " + length2);
1128:
1129: for (int j = 1; j <= length2; ++j)
1130: {
1131: Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
1132: Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
1133: Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
1134: if (node4 != null)
1135: System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
1136: if (node5 != null)
1137: System.out.println(node5.name + " " + node5.getCharacters());
1138: if (node6 != null)
1139: System.out.println(node6.name + " " + node6.getCharacters());
1140: if (node4 == null)
1141: throw new Error("parse error table tag missing");
1142: // if(node5==null) throw new Error("parse error select tag missing");
1143: // if(node6==null) throw new Error("parse error create tag missing");
1144: String name = (String) node4.attributes.get("name");
1145: String layout = (String) node4.attributes.get("layout");
1146: String id = (String) node4.attributes.get("id");
1147: System.out.println("id was " + id);
1148: if (name == null)
1149: throw new Error("parse error required table tag attribute name missing");
1150: if (layout == null)
1151: layout = "";
1152: if (id == null)
1153: id = "";
1154: if (name.equals(""))
1155: throw new Error("parse error table tag attribute must not be empty");
1156: tables.add(name);
1157: layouts.add(layout);
1158: ids.add(id);
1159: String query = (node5 == null) ? "" : node5.getCharacters();
1160: if (query.equals(""))
1161: System.err.println("Warning empty select tag or select tag missing !!");
1162: query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
1163: selects.add(query);
1164: if (node6 != null)
1165: creates.add(node6.getCharacters().trim());
1166: else
1167: creates.add("");
1168:
1169: }
1170: databases.add(new DataBase(database, tables, layouts, selects, creates, ids, mode));
1171: }
1172: DBBean database = new DBBean();
1173: // parse dataBase
1174: Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
1175: Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
1176: Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
1177: String url = node.getCharacters();
1178: String user = node1.getCharacters();
1179: String password = node2.getCharacters();
1180: System.out.println(url);
1181: database.setURL(url.trim());
1182: database.setUserAndPasswd(user.trim(), password.trim());
1183: //databases.add(database);
1184: for (Iterator iter = databases.iterator(); iter.hasNext();)
1.1 rogo 1185: {
1186: DataBase db = (DataBase) iter.next();
1.34 rogo 1187: if (mode != DataBase.UPDATE_MODE)
1188: convertBatch(db.bean, database, db.tables, db.layouts, db.selects, db.creates, db.ids,mode);
1.17 rogo 1189: else
1.34 rogo 1190: update(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates, db.ids, mode);
1.17 rogo 1191:
1.1 rogo 1192: }
1.34 rogo 1193: // printContents(node3);
1194: // FM2SQL.fmInstance=new FM2SQL();
1195: } catch (Exception e)
1196: {
1.35 rogo 1197:
1.34 rogo 1198: e.printStackTrace();
1199: }
1200: }
1201: public static Vector getXMLConfig(String xmlFile)
1202: {
1203: StringBuffer sb = null;
1204: try
1205: {
1206: // read XML Metadata from a file
1207: FileInputStream fi = new FileInputStream(xmlFile);
1208: InputStreamReader isr = new InputStreamReader(fi, "UTF-8");
1209: BufferedReader buffr = new BufferedReader(isr);
1210: sb = new StringBuffer();
1211: int c = 0;
1212: while ((c = buffr.read()) != -1)
1213: {
1214: char ch = (char) c;
1215: sb.append(ch);
1216: // System.out.print((char)c);
1.1 rogo 1217: }
1218:
1.34 rogo 1219: } catch (Exception e)
1220: {
1221: e.printStackTrace();
1222: }
1223:
1224: boolean finished = false;
1225: // parse string and build document tree
1226: Xparse parser = new Xparse();
1227: parser.changeEntities = true;
1228: Node root = parser.parse(sb.toString());
1229: // printContents(root);
1230: Vector databases = new Vector();
1231: Vector tables = new Vector();
1232: Vector layouts = new Vector();
1233: Vector selects = new Vector();
1234: Vector creates = new Vector();
1235: Vector ids = new Vector();
1.1 rogo 1236:
1.34 rogo 1237: int mode = -1;
1238: try
1.1 rogo 1239: {
1.34 rogo 1240: Node tempNode = root.find("convert/source", new int[] { 1, 1 });
1241: if (tempNode == null)
1242: throw new Error("parse error source tag missing");
1243: System.out.println(tempNode.name);
1244: int length = countNodes(tempNode);
1245: for (int i = 1; i <= length; i++)
1.1 rogo 1246: {
1.34 rogo 1247:
1248: DBBean database = new DBBean();
1249: tables = new Vector();
1250: layouts = new Vector();
1251: selects = new Vector();
1252: creates = new Vector();
1253: ids = new Vector();
1254: // parse dataBase
1255: Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
1256: Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
1257: Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
1258: Node node3 = root.find("convert/source/database", new int[] { 1, 1, i });
1259: Node nodeMode = root.find("convert/source/database/mode", new int[] { 1, 1, i, 1, 1 });
1260:
1261: if (node3 == null)
1262: throw new Error("parse error database tag missing");
1263: if (node == null)
1264: throw new Error("parse error url tag missing");
1265: if (node1 == null)
1266: throw new Error("parse error user tag missing");
1267: if (node2 == null)
1268: throw new Error("parse error password tag missing");
1269: String url = node.getCharacters();
1270: String user = node1.getCharacters();
1271: String password = node2.getCharacters();
1272: database.setURL(url.trim());
1273: database.setUserAndPasswd(user.trim(), password.trim());
1274: System.out.println(node.name + " " + node.getCharacters());
1275: System.out.println(node1.name + " " + node1.getCharacters());
1276: System.out.println(node2.name + " " + node2.getCharacters());
1277: String modeString = "";
1278: if (nodeMode == null)
1279: modeString = "convert";
1280: else
1281: modeString = nodeMode.getCharacters();
1282: if (modeString.equals("convert"))
1283: mode = DataBase.CONVERT_MODE;
1284: else if (modeString.equals("append"))
1285: mode = DataBase.APPEND_MODE;
1286: else if (modeString.equals("update"))
1287: mode = DataBase.UPDATE_MODE;
1288:
1289: // if(node3!=null)
1290: // System.out.println(node3.name);
1291:
1292: int length2 = countNodes(node3);
1293:
1294: System.out.println("number of tables " + length2);
1295:
1296: for (int j = 1; j <= length2; ++j)
1297: {
1298: Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
1299: Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
1300: Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
1301: if (node4 != null)
1302: System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
1303: if (node5 != null)
1304: System.out.println(node5.name + " " + node5.getCharacters());
1305: if (node6 != null)
1306: System.out.println(node6.name + " " + node6.getCharacters());
1307: if (node4 == null)
1308: throw new Error("parse error table tag missing");
1309: // if(node5==null) throw new Error("parse error select tag missing");
1310: // if(node6==null) throw new Error("parse error create tag missing");
1311: String name = (String) node4.attributes.get("name");
1312: String layout = (String) node4.attributes.get("layout");
1313: String id = (String) node4.attributes.get("id");
1314: System.out.println("id was " + id);
1315:
1316: if (name == null)
1317: throw new Error("parse error required table tag attribute name missing");
1318: if (layout == null)
1319: layout = "";
1320: if (id == null)
1321: id = "";
1322: if (name.equals(""))
1323: throw new Error("parse error table tag attribute must not be empty");
1324: tables.add(name);
1325: layouts.add(layout);
1326: ids.add(id);
1327: String query = (node5 == null) ? "" : node5.getCharacters();
1328: if (query.equals(""))
1329: System.err.println("Warning empty select tag or select tag missing !!");
1330: query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
1331: selects.add(query);
1332: if (node6 != null)
1333: creates.add(node6.getCharacters().trim());
1334: else
1335: creates.add("");
1336:
1337: }
1338: databases.add(new DataBase(database, tables, layouts, selects, creates, ids, mode));
1.1 rogo 1339: }
1.34 rogo 1340: DBBean database = new DBBean();
1341: // parse dataBase
1342: Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
1343: Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
1344: Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
1345: String url = node.getCharacters();
1346: String user = node1.getCharacters();
1347: String password = node2.getCharacters();
1348: System.out.println(url);
1349: database.setURL(url.trim());
1350: database.setUserAndPasswd(user.trim(), password.trim());
1351: databases.add(new DataBase(database, null, null, null, null, null, 0));
1352: //databases.add(database);
1353: /* for (Iterator iter = databases.iterator(); iter.hasNext();)
1354: {
1355: DataBase db = (DataBase) iter.next();
1356: convertBatch(db.bean,database,db.tables,db.layouts,db.selects,db.creates);
1357:
1358: }*/
1359: // printContents(node3);
1360: // FM2SQL.fmInstance=new FM2SQL();
1361: } catch (Exception e)
1362: {
1363: // TODO Auto-generated catch block
1364: e.printStackTrace();
1.1 rogo 1365: }
1.34 rogo 1366: return databases;
1367: }
1368:
1369: private static int countNodes(Node tempNode)
1370: {
1371: int length = 0;
1372: for (int i = 0; i < tempNode.contents.v.size(); ++i)
1.1 rogo 1373: {
1.34 rogo 1374: Node node = (Node) tempNode.contents.v.elementAt(i);
1375: if (node.type.equals("element"))
1.1 rogo 1376: {
1.34 rogo 1377: if (node.name.equals("database"))
1378: length++;
1379: if (node.name.equals("table"))
1380: length++;
1.1 rogo 1381: }
1.34 rogo 1382:
1383: // System.out.println(((Node)tempNode.contents.v.elementAt(i)).attributes+" "+i);
1.1 rogo 1384: }
1.34 rogo 1385: return length;
1386: }
1387: private static void printContents(Node root)
1388: {
1389:
1390: Vector contents = (root.index == null) ? root.contents.v : root.index.v;
1391: for (int i = 0; i < contents.size(); ++i)
1.1 rogo 1392: {
1.34 rogo 1393: Node n = (Node) contents.elementAt(i);
1394: if (n.type.equals("element"))
1395: {
1396: System.out.println("tag " + n.name);
1397: System.out.println(n.getCharacters());
1398: //contents=n.contents.v i=0;
1399: }
1400: // System.out.println(n.type);
1401:
1.1 rogo 1402: }
1.34 rogo 1403: }
1.38 rogo 1404: /**
1405: * reads the specified xml file
1406: * @param xmlFile
1407: */
1.34 rogo 1408: public static void readXMLFile(String xmlFile)
1.1 rogo 1409: {
1.34 rogo 1410: try
1411: {
1412: // read XML Metadata from a file
1413: FileInputStream fi = new FileInputStream(xmlFile);
1414: InputStreamReader isr = new InputStreamReader(fi, "UTF-8");
1415: BufferedReader buffr = new BufferedReader(isr);
1416: StringBuffer sb = new StringBuffer();
1417: int c = 0;
1418: while ((c = buffr.read()) != -1)
1419: {
1420: char ch = (char) c;
1421: sb.append(ch);
1422: // System.out.print((char)c);
1423: }
1424: parseXMLConfig(sb);
1425: } catch (Exception e)
1426: {
1427: e.printStackTrace();
1428: }
1.1 rogo 1429: }
1.38 rogo 1430:
1431: /**
1432: * Helper class for XML-File parsing
1433: * Holds the parsed data
1434: * @author rogo
1435: *
1436: */
1.34 rogo 1437: public static class DataBase
1438: {
1439: DBBean bean;
1440: Vector creates;
1441: Vector selects;
1442: Vector layouts;
1443: Vector tables;
1444: Vector ids;
1445: final static int CONVERT_MODE = 1;
1446: final static int APPEND_MODE = 2;
1447: final static int UPDATE_MODE = 3;
1.40 ! rogo 1448: final static int DELETE_MODE = 4;
! 1449:
1.34 rogo 1450: int mode = -1;
1451:
1452: public DataBase(DBBean bean, Vector tables, Vector layouts, Vector selects, Vector creates, Vector ids, int mode)
1453: {
1454: this.bean = bean;
1455: this.tables = tables;
1456: this.layouts = layouts;
1457: this.selects = selects;
1458: this.creates = creates;
1459: this.ids = ids;
1460: this.mode = mode;
1461: this.bean.setIDVector(ids);
1462: }
1.38 rogo 1463: /**
1464: * writes the data contained in this object th the buffered writer
1465: * * @param buffr
1466: * @throws Exception
1467: */
1.34 rogo 1468: public void exportToXML(BufferedWriter buffr) throws Exception
1469: {
1470: // ids=bean.getIDVector();
1471: buffr.write(" <database>\n");
1472: buffr.write(" <url>" + bean.url + "</url>\n");
1473: buffr.write(" <user>" + bean.user + "</user>\n");
1474: buffr.write(" <password>" + bean.passwd + "</password>\n");
1475: String modeString = "";
1476: if (mode == CONVERT_MODE)
1477: modeString = "convert";
1478: else if (mode == APPEND_MODE)
1.5 rogo 1479: modeString = "append";
1.34 rogo 1480: else if (mode == UPDATE_MODE)
1481: modeString = "update";
1482:
1483: buffr.write(" <mode>" + modeString + "</mode>\n");
1484: int index = 0;
1485: while (index < tables.size())
1486: {
1487: String table = (String) tables.get(index);
1488: String layout = (String) layouts.get(index);
1489: String select = (String) selects.get(index);
1490: String create = (String) creates.get(index);
1491: String id = (String) ids.get(index);
1492:
1493: buffr.write(" <table name = \"" + table + "\" layout = \"" + layout + "\" id = \"" + id + "\" >\n");
1494: buffr.write(" <select>" + convertToEntities(select) + "</select>\n");
1495: if (!create.equals(""))
1496: buffr.write(" <create>" + create + " </create>\n");
1497: buffr.write(" </table>\n");
1498: index++;
1499: }
1500: buffr.write(" </database>\n");
1501: }
1502: public String toString()
1503: {
1504: return bean.url + " " + tables;
1505: }
1.1 rogo 1506:
1.34 rogo 1507: }
1508: public static String convertToUTF8(Object command)
1.1 rogo 1509: {
1.34 rogo 1510: String str = null;
1511: try
1512: {
1513: str = new String(command.toString().getBytes("UTF-8"));
1514: } catch (UnsupportedEncodingException e)
1515: {
1516: // TODO Auto-generated catch block
1517: e.printStackTrace();
1518: }
1519: return str;
1.1 rogo 1520: }
1521: public static void writeConfig(String file, DataBase source, DataBase destination) throws Exception
1522: {
1.34 rogo 1523: if (!file.toLowerCase().endsWith(".xml"))
1524: file += ".xml";
1.1 rogo 1525: File f = new File(file);
1.34 rogo 1526:
1527: FileOutputStream fout = new FileOutputStream(f);
1528: OutputStreamWriter outsw = new OutputStreamWriter(fout, "UTF-8");
1.1 rogo 1529: BufferedWriter buffw = new BufferedWriter(outsw);
1530: buffw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
1531: buffw.newLine();
1532: buffw.write("<convert>\n");
1533: buffw.write(" <source>\n");
1534: source.exportToXML(buffw);
1535: buffw.write(" </source>\n");
1536: buffw.write("\n <destination>\n");
1537: destination.exportToXML(buffw);
1538: buffw.write(" </destination>\n");
1539: buffw.write("</convert>\n");
1540: buffw.close();
1.34 rogo 1541: }
1542: }
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