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