Annotation of FM2SQL/Convert.java, revision 1.56
1.51 rogo 1: /*
2: * Convert.java -- Converter class - Filemaker to SQL Converter
1.52 rogo 3: * Copyright (C) 2003 Robert Gordesch (rogo@mpiwg-berlin.mpg.de)
1.51 rogo 4: * This program is free software; you can redistribute it and/or modify it
5: * under the terms of the GNU General Public License as published by the Free
6: * Software Foundation; either version 2 of the License, or (at your option)
7: * any later version. Please read license.txt for the full details. A copy of
8: * the GPL may be found at http://www.gnu.org/copyleft/lgpl.html You should
9: * have received a copy of the GNU General Public License along with this
10: * program; if not, write to the Free Software Foundation, Inc., 59 Temple
11: * Place, Suite 330, Boston, MA 02111-1307 USA Created on 15.09.2003 by
12: * rogo
13: */
14:
1.1 rogo 15: import java.util.*;
16: import java.sql.*;
17: import java.awt.Cursor;
18: import java.io.BufferedReader;
19: import java.io.BufferedWriter;
20: import java.io.File;
21: import java.io.FileInputStream;
22: import java.io.FileNotFoundException;
23: import java.io.FileOutputStream;
24: import java.io.InputStreamReader;
25: import java.io.OutputStreamWriter;
26: import java.io.PrintStream;
27: import java.io.UnsupportedEncodingException;
28:
29: import com.exploringxml.xml.Node;
30: import com.exploringxml.xml.Xparse;
31:
1.34 rogo 32: class Convert
1.1 rogo 33: {
1.34 rogo 34: static DBBean bean = new DBBean();
35: static DBBean beanDest = new DBBean();
1.1 rogo 36:
1.34 rogo 37: static String user = "", passwd = "e1nste1n";
38: static String userDest = "postgres", passwdDest = "rogo";
39: static boolean batchRun = false;
1.1 rogo 40: static Vector databases = new Vector();
1.30 rogo 41: final static int numHits = 5000;
42: final static int numIntervalls = 2;
1.1 rogo 43: public static void main(String args[])
44: {
1.34 rogo 45: /* try
46: {
47: //byte[] b = "ö".getBytes("UTF-8");
48: // System.out.println("QueryString " +b[0]+" "+b[1]+(new String(b).getBytes()[0])+" "+new String(b).getBytes()[1]);
49: //System.out.println(new String(b,"UTF-8"));
50: } catch (UnsupportedEncodingException e)
51: {
52: e.printStackTrace();
53: }*/
1.1 rogo 54: FileOutputStream file = null;
1.34 rogo 55: if (args.length != 1)
1.1 rogo 56: {
1.34 rogo 57: System.out.println("Usage: java Convert <xml config file>");
58: System.exit(-1);
1.1 rogo 59: }
1.34 rogo 60: if (!(new File(args[0]).exists()))
61: System.exit(0);
1.1 rogo 62: try
63: {
64: file = new FileOutputStream("./log.txt");
65: } catch (FileNotFoundException e1)
66: {
67: e1.printStackTrace();
1.34 rogo 68: }
69: PrintStream stream = new PrintStream(file);
70: System.setOut(stream);
71: System.setErr(stream);
72: readXMLFile(args[0]);
73: System.out.println("Finished!");
1.1 rogo 74: //convert("jdbc:fmpro:http://141.14.237.74:8050","jdbc:postgresql://erebos/test",null,null);
75: }
1.55 rogo 76: public static void convertBatch(DBBean source, DBBean destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode, String delimiter) throws Exception
1.1 rogo 77: {
1.34 rogo 78: bean = source;
79: beanDest = destination;
1.55 rogo 80: convert(null, null, names, layouts, selects, creates, ids, mode, delimiter);
81: if (true)
82: return;
1.34 rogo 83: StringBuffer command = null;
1.1 rogo 84: try
85: {
86: bean.setConnection(source.url);
1.34 rogo 87: if (names == null)
88: names = bean.getTableNames();
1.1 rogo 89: //Collections.sort(names);
1.34 rogo 90: int tbIndex = 1;
91:
92: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
1.1 rogo 93: {
94: Vector[] result = null;
1.34 rogo 95: try
1.1 rogo 96: {
1.34 rogo 97: String query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
98: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
99: query = (selects != null) ? selects.get(tbIndex).toString() : query;
100: //if vectors[1].get(i) != null)
101: if (!layout.equals(""))
102: {
103: System.out.println("before " + query + " table" + names.get(tbIndex));
104: layout = " layout " + bean.getQC() + layout + bean.getQC();
105: String name = names.get(tbIndex).toString();
106: StringBuffer queryLayout = new StringBuffer(query);
107: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
108: query = queryLayout.toString();
109: System.out.println("added layout " + query);
110:
111: }
112: System.out.println(" performing query " + query);
1.1 rogo 113: //result = bean.getQueryData(query, null, 0);
1.34 rogo 114: bean.getConnection();
115: bean.makeQuery(query, 0);
116: } catch (Exception e)
1.1 rogo 117: {
118: System.out.println(e.getMessage());
119: e.printStackTrace();
120: continue;
121: }
122: //beanDest.setConnection("jdbc:postgresql://erebos/test3");
123: beanDest.setConnection(destination.url);
124:
125: Statement stm = beanDest.getConnection().createStatement();
126:
127: Vector tables = beanDest.getTableNames();
1.34 rogo 128: // Collections.sort(tables);
129: System.out.println("converting table " + names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
1.1 rogo 130: tables = beanDest.getTableNames();
131: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
132: stm = beanDest.getConnection().createStatement();
133: // System.exit(0);
1.34 rogo 134: if (mode == Convert.DataBase.CONVERT_MODE)
1.1 rogo 135: {
1.34 rogo 136: if (tables.indexOf(names.get(tbIndex)) >= 0)
137: {
138: stm.executeUpdate("drop table " + beanDest.getQC() + names.get(tbIndex) + beanDest.getQC());
139: tables.remove((String) names.get(tbIndex));
140: System.out.println("dropped table " + names.get(tbIndex));
141: } else if (tables.indexOf(convertText(names.get(tbIndex).toString())) >= 0)
1.1 rogo 142: {
1.34 rogo 143: stm.executeUpdate("drop table " + beanDest.getQC() + convertText((String) names.get(tbIndex)) + beanDest.getQC());
144: tables.remove(convertText((String) names.get(tbIndex)));
145: System.out.println("dropped table " + names.get(tbIndex));
146: }
147:
148: if (tables.indexOf(names.get(tbIndex)) < 0 && tables.indexOf(convertText(names.get(tbIndex).toString())) < 0)
1.1 rogo 149: {
1.34 rogo 150: if (creates.get(tbIndex).equals("") || creates.get(tbIndex).toString().toLowerCase().indexOf("create") < 0)
151: {
152: System.out.println("Warning empty or invalid create statement - creating one for you\n");
153:
154: command = new StringBuffer(50);
155: command.append("CREATE TABLE ");
156: command.append(beanDest.getQC());
157: command.append(convertText((String) names.get(tbIndex)));
158: command.append(beanDest.getQC());
159: command.append("(");
160: String type = null;
161: Vector columnNames = bean.getColumnNames();
162: for (int i = 0; i < columnNames.size() - 1; ++i)
163: {
164: type = bean.metaData.getColumnTypeName(i + 1);
165: // System.out.println(i+" "+result[1].get(i)+" "+type);
166: type = (type.equals("NUMBER")) ? "INT4" : type;
167: type = (type.equals("CONTAINER")) ? "TEXT" : type;
168:
169: command.append(beanDest.getQC() + convertText((String) columnNames.get(i)) + beanDest.getQC() + " " + type + ", ");
170: }
171: type = bean.metaData.getColumnTypeName(columnNames.size());
172: type = (type.equals("NUMBER")) ? "INT4" : type;
173: type = (type.equals("CONTAINER")) ? "TEXT" : type;
174: command.append(beanDest.getQC() + convertText((String) columnNames.get(columnNames.size() - 1)) + beanDest.getQC() + " " + type);
175: command.append(" )");
176: } else
177: command = new StringBuffer().append(creates.get(tbIndex).toString());
178:
179: System.out.println(command);
180: // System.exit(0);
181: //command.append(DBBean.getQC());
182: stm.executeUpdate(command.toString());
1.1 rogo 183:
1.34 rogo 184: }
1.1 rogo 185: }
1.34 rogo 186: Vector row = null;
187: command = new StringBuffer();
1.1 rogo 188:
189: command.append("INSERT INTO ");
190: command.append(beanDest.getQC());
191: command.append(convertText((String) names.get(tbIndex)));
192: command.append(beanDest.getQC());
193: command.append(" values ( ");
1.3 rogo 194:
1.34 rogo 195: for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
196: command.append("?,");
197: command.append("?)");
198: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
199: System.out.println(command);
200: while ((row = bean.getNextRow()) != null)
201: {
202: //print rows
203: Object obj = null;
204: for (int k = 0; k < row.size(); ++k)
205: {
206: obj = row.get(k);
207: if (obj instanceof ArrayList)
1.55 rogo 208: obj = formatFileMakerArray((List) obj, "\n");
1.34 rogo 209: String str = (obj == null) ? "NULL" : obj.toString();
210: if (!str.equals("NULL"))
211: pstm.setString(k + 1, str);
212: else
213: pstm.setNull(k + 1, Types.NULL);
214: }
215: pstm.execute();
216:
217: } // to for loop
218:
219: }
220: } catch (Exception e)
221: {
222: System.out.println("Error while connecting to database " + e);
223: //dialog.setVisible(false);
224: //dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
225: //FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
226: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
227: java.io.PrintStream stream = new java.io.PrintStream(b);
228: stream.print(command + "\n\n");
229: e.printStackTrace(stream);
230: System.err.println(b);
231: //FM2SQL.showErrorDialog(b.toString(), "Error occured !");
232:
1.1 rogo 233: }
1.34 rogo 234: // dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
235: //FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1.1 rogo 236:
1.34 rogo 237: // dialog.setVisible(false);
1.1 rogo 238: }
1.55 rogo 239: public static String formatFileMakerArray(List list, String delimiter)
240: {
241: StringBuffer formattedString = new StringBuffer();
242: for (int i = 0; i < list.size(); ++i)
243: {
244: formattedString.append(list.get(i).toString());
245: if (i < list.size() - 1)
246: formattedString.append(delimiter);
247: }
248: return formattedString.toString();
249: }
1.38 rogo 250: /**
251: * Method for SQL UPDATE
252: * @param source
253: * @param destination
254: * @param names
255: * @param layouts
256: * @param selects
257: * @param creates
258: * @param ids
259: * @param mode
260: * @throws Exception
261: */
1.34 rogo 262: 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 263: {
264: FM2SQL.ProgressDialog dialog = null;
265: if (FM2SQL.fmInstance != null)
266: {
1.55 rogo 267: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
1.12 rogo 268: dialog.setTitle("Conversion running ...");
269: dialog.title.setText("Getting table data ...");
270: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
271: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
272: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
273: dialog.thread = Thread.currentThread();
274: }
275: // setting user and passwd
276: bean.setUserAndPasswd(user, passwd);
277: // setting user and passwd
278: beanDest.setUserAndPasswd(userDest, passwdDest);
279: if (dialog != null)
280: dialog.setSize(400, 250);
281: StringBuffer command = null;
282: String query = null;
283: try
284: {
285: //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
286: //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
287: bean.setConnection(source);
288: if (names == null)
289: names = bean.getTableNames();
290: // Collections.sort(names);
291: int tbIndex = 1;
1.55 rogo 292:
1.12 rogo 293: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
294: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
295: {
296: Vector[] result = null;
1.45 rogo 297: String destTableName = "";
1.12 rogo 298: try
299: {
300: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
301: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
302: query = (selects != null) ? selects.get(tbIndex).toString() : query;
303: //if vectors[1].get(i) != null)
304: if (layout != "")
305: {
306: layout = " layout " + bean.getQC() + layout + bean.getQC();
307: String name = names.get(tbIndex).toString();
308: StringBuffer queryLayout = new StringBuffer(query);
309: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
310: query = queryLayout.toString();
311: System.out.println("added layout " + query);
312:
313: }
314: dialog.title.setText("Getting table data ...");
315: dialog.table.setText(names.get(tbIndex).toString());
316: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
317: dialog.show();
318: bean.getConnection();
319: bean.makeQuery(query, 0);
320: } catch (Exception e)
321: {
322: continue;
323: }
1.45 rogo 324: // determine destTableName from createStatement or from source table name
1.55 rogo 325: if (!creates.get(tbIndex).equals(""))
326: {
327: String create = creates.get(tbIndex).toString().toLowerCase();
328: int fromIndex = create.indexOf("table") + 5;
329: int toIndex = create.indexOf("(");
330: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
331: System.out.println("destTable " + destTableName);
332:
333: } else
334: destTableName = convertText(names.get(tbIndex).toString());
1.45 rogo 335:
1.12 rogo 336: //beanDest.setConnection("jdbc:postgresql://erebos/test3");
337: beanDest.setConnection(destination);
338:
339: Statement stm = beanDest.getConnection().createStatement();
340:
341: Vector tables = beanDest.getTableNames();
342: // Collections.sort(tables);
343: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
344: tables = beanDest.getTableNames();
345: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
346: stm = beanDest.getConnection().createStatement();
347: // System.exit(0);
1.34 rogo 348:
1.12 rogo 349: if (dialog != null)
1.36 rogo 350: dialog.title.setText("Updating table data ...");
1.12 rogo 351:
352: int j = -1;
1.34 rogo 353:
1.12 rogo 354: Vector row = null;
355: command = new StringBuffer();
356:
357: command.append("UPDATE ");
358: command.append(beanDest.getQC());
1.45 rogo 359: command.append(destTableName);
360: //command.append(convertText((String) names.get(tbIndex)));
1.12 rogo 361: command.append(beanDest.getQC());
362: command.append(" SET ");
363:
364: int size = bean.getColumnNames().size();
365: for (int i = 0; i < size - 1; ++i)
1.34 rogo 366: command.append(beanDest.getQC() + convertText((String) bean.getColumnNames().get(i)) + beanDest.getQC() + " = ? ,");
367: command.append(convertText((String) bean.getColumnNames().get(size - 1)) + " = ? ");
368: command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
1.12 rogo 369: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
1.34 rogo 370: System.out.println(command + " " + tbIndex);
371: int rowCount = bean.getRowCount(query);
372: int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
1.12 rogo 373: while ((row = bean.getNextRow()) != null)
374: {
375: j++;
376: //print rows
377: Object obj = null;
378: /* for(int k=0;k<row.size()-1;++k)
379: {
380: obj = row.get(k);
381: //System.out.println("row "+obj+" "+k);
382: if(obj!=null&&!(obj instanceof ArrayList))
383: command.append("'"+convertUml(obj.toString())+"',");
384: else if(obj!=null&& obj instanceof ArrayList)
385: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',");
386: else command.append("NULL,");
387: }
388: obj = row.get(row.size() - 1);
389: if (obj != null && !(obj instanceof ArrayList))
390: command.append("'"+convertUml(obj.toString())+"')");
391: else
392: if(obj!=null&& obj instanceof ArrayList)
393: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')"); //command.append(obj.toString()+")");
394: else command.append("NULL)");
395: */
396: //command.append("'"+row.get(row.size()-1)+"')");
397: //command.append(" )");
398: // for(int k=0;k<row.size();++k)
399:
400: // System.out.println();
401: // System.out.println(command+" "+j+" "+row.size()+" "+ ((Vector)result2[0].get(j)).size());
402: // System.out.println(command);
403: for (int k = 0; k < row.size(); ++k)
404: {
405: obj = row.get(k);
406: if (obj instanceof ArrayList)
407: obj = ((List) obj).get(0);
408: String str = (obj == null) ? "NULL" : obj.toString();
409: if (!str.equals("NULL"))
410: pstm.setString(k + 1, str);
411: else
412: pstm.setNull(k + 1, Types.NULL);
413: }
1.34 rogo 414: pstm.setString(row.size() + 1, row.get(idIndex).toString());
415: //System.out.println(pstm.toString());
416: // System.exit(0);
1.12 rogo 417: pstm.execute();
418: //stm.executeUpdate(command.toString());
1.34 rogo 419: if (dialog != null)
420: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
1.12 rogo 421: // System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
422: command = null;
423: } // to for loop
424:
425: }
426: } catch (Exception e)
427: {
428: System.out.println("Error while connecting to database " + e);
1.18 rogo 429: if (dialog != null)
430: {
431: dialog.setVisible(false);
432: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
433: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
434: }
1.12 rogo 435: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
436: java.io.PrintStream stream = new java.io.PrintStream(b);
437: stream.print(command + "\n\n");
438: e.printStackTrace(stream);
439: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
440:
441: }
1.18 rogo 442: if (dialog != null)
443: {
444: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
445: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
446:
447: dialog.setVisible(false);
448: }
1.12 rogo 449:
450: }
1.34 rogo 451: /**
1.38 rogo 452: * transfers the specified array of tables to the destination database
453: and creates the table if it does not exist if it exists and mode is not append the table is dropped
1.55 rogo 454:
1.38 rogo 455: * @param source
456: * @param destination
457: * @param names
458: * @param layouts
459: * @param selects
460: * @param creates
461: * @param ids
462: * @param mode
463: * @throws Exception
1.55 rogo 464: */
465:
466: public static void convert(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode, String delimiter) throws Exception
1.1 rogo 467: {
1.34 rogo 468:
469: FM2SQL.ProgressDialog dialog = null;
470:
471: if (FM2SQL.fmInstance != null)
472: {
1.55 rogo 473: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
1.34 rogo 474: dialog.setTitle("Conversion running ...");
475: dialog.title.setText("Getting table data ...");
476: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
477: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
478: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
479: dialog.thread = Thread.currentThread();
480: dialog.setSize(400, 250);
481: }
482: java.util.TreeSet myIds = new TreeSet();
483: int deltaID = 1;
484: String idField = "";
1.38 rogo 485: String destTableName = "";
1.55 rogo 486: String[] fieldNames = null;
1.34 rogo 487: if (source != null && destination != null)
488: {
489: // setting user and passwd
490: bean.setUserAndPasswd(user, passwd);
491: // setting user and passwd
492: beanDest.setUserAndPasswd(userDest, passwdDest);
493: }
494: StringBuffer command = null;
495: String query = null;
1.1 rogo 496: try
497: {
1.55 rogo 498: if (source != null)
499: bean.setConnection(source);
1.34 rogo 500: else
1.55 rogo 501: bean.setConnection(bean.url);
502:
1.34 rogo 503: if (names == null)
504: names = bean.getTableNames();
505: // Collections.sort(names);
506: int tbIndex = 1;
507:
508: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
509: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
1.1 rogo 510: {
511: Vector[] result = null;
1.34 rogo 512: try
1.1 rogo 513: {
1.34 rogo 514: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
515: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
516: query = (selects != null) ? selects.get(tbIndex).toString() : query;
517: //if vectors[1].get(i) != null)
518: if (layout != "")
519: {
520: layout = " layout " + bean.getQC() + layout + bean.getQC();
521: String name = names.get(tbIndex).toString();
522: StringBuffer queryLayout = new StringBuffer(query);
523: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
524: query = queryLayout.toString();
525: System.out.println("added layout " + query);
1.21 rogo 526:
1.34 rogo 527: }
528: // if ( layout!= "")
529: // query += " layout " + bean.getQC() + layout + bean.getQC();
530: if (dialog != null)
531: {
532: dialog.title.setText("Reading table data ...");
533: dialog.table.setText(names.get(tbIndex).toString());
534: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
535: dialog.show();
536: }
537: //result = bean.getQueryData(query, dialog, 0);
538: bean.getConnection();
539: bean.makeQuery(query, 50);
540: idField = ids.get(tbIndex).toString();
541:
542: } catch (Exception e)
543: {
544: System.out.println(e);
545: continue;
1.1 rogo 546: }
1.55 rogo 547: if (destination != null)
548: beanDest.setConnection(destination);
1.34 rogo 549: else
1.55 rogo 550: beanDest.setConnection(beanDest.url);
1.1 rogo 551: Statement stm = beanDest.getConnection().createStatement();
552:
553: Vector tables = beanDest.getTableNames();
1.34 rogo 554: // Collections.sort(tables);
1.1 rogo 555: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
556: tables = beanDest.getTableNames();
557: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
558: stm = beanDest.getConnection().createStatement();
559: // System.exit(0);
1.55 rogo 560:
561: // determine destTableName from createStatement or from source table name
562: if (!creates.get(tbIndex).equals(""))
1.45 rogo 563: {
1.55 rogo 564: String create = creates.get(tbIndex).toString().toLowerCase();
565: int fromIndex = create.indexOf("table") + 5;
566: int toIndex = create.indexOf("(");
567: int endIndex = create.indexOf(")", toIndex);
568:
569: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
570: System.out.println("destTable " + destTableName);
571: // retrieve field_names from select statement
572: if (query.indexOf("*") < 0)
573: {
574: int selectEndIndex = query.indexOf("from");
575: StringTokenizer tokenizer = new StringTokenizer(query.substring(6, selectEndIndex), ",");
576: int numFields = tokenizer.countTokens();
577: fieldNames = new String[numFields];
578: int fieldIndex = 0;
579: while (tokenizer.hasMoreTokens())
580: {
581: String fieldName = tokenizer.nextToken().trim();
582: fieldNames[fieldIndex] = convertText(fieldName);
583: System.out.println(fieldNames[fieldIndex]);
584: fieldIndex++;
585: }
586:
587: } else
588: {
589: // use create statement for field names
590: StringTokenizer tokenizer = new StringTokenizer(create.substring(toIndex + 1, endIndex), ",");
591: int numFields = tokenizer.countTokens();
592: fieldNames = new String[numFields];
593: int fieldIndex = 0;
594: while (tokenizer.hasMoreTokens())
595: {
596: String fieldName = tokenizer.nextToken().trim();
597: int index = fieldName.lastIndexOf(" ");
598: fieldNames[fieldIndex] = fieldName.substring(0, index);
599: System.out.println(fieldNames[fieldIndex]);
600: fieldIndex++;
601: }
602: }
1.45 rogo 603: } else
1.55 rogo 604: {
1.45 rogo 605: destTableName = convertText(names.get(tbIndex).toString());
606:
1.55 rogo 607: // retrieve field_names from select statement
608: if (query.indexOf("*") < 0)
609: {
610: int selectEndIndex = query.indexOf("from");
611: StringTokenizer tokenizer = new StringTokenizer(query.substring(6, selectEndIndex), ",");
612: int numFields = tokenizer.countTokens();
613: fieldNames = new String[numFields];
614: int fieldIndex = 0;
615: while (tokenizer.hasMoreTokens())
616: {
617: String fieldName = tokenizer.nextToken().trim();
618: fieldNames[fieldIndex] = convertText(fieldName);
619: // System.out.println("field "+ fieldNames[fieldIndex]);
620: fieldIndex++;
621: }
622:
623: } else
624: {
625: Vector fieldNamesVec = bean.getColumnNames();
626: fieldNames = new String[fieldNamesVec.size()];
627: int fieldIndex = -1;
628: for (Iterator iter = fieldNamesVec.iterator(); iter.hasNext();)
629: {
630: String element = (String) iter.next();
631: fieldNames[++fieldIndex] = bean.getQC() + convertText(element) + bean.getQC();
632: // System.out.println("field " + fieldNames[fieldIndex]);
633: }
634: }
635: }
1.34 rogo 636: if (mode == Convert.DataBase.CONVERT_MODE)
1.1 rogo 637: {
1.38 rogo 638:
639: if (tables.indexOf(destTableName) >= 0)
640: {
641: stm.executeUpdate("drop table " + beanDest.getQC() + destTableName + beanDest.getQC());
642: tables.remove(destTableName);
643: System.out.println("dropped table" + destTableName);
644:
645: }
646: /*
647: if(destTableName.equals(""))
1.34 rogo 648: if (tables.indexOf(names.get(tbIndex)) >= 0)
649: {
650: stm.executeUpdate("drop table " + beanDest.getQC() + names.get(tbIndex) + beanDest.getQC());
651: tables.remove((String) names.get(tbIndex));
652: System.out.println("dropped table" + names.get(tbIndex));
653: } else if (tables.indexOf(convertText(names.get(tbIndex).toString())) >= 0)
1.1 rogo 654: {
1.34 rogo 655: stm.executeUpdate("drop table " + beanDest.getQC() + convertText((String) names.get(tbIndex)) + beanDest.getQC());
656: tables.remove(convertText((String) names.get(tbIndex)));
657: System.out.println("dropped table" + names.get(tbIndex));
658: }
1.55 rogo 659: */
1.38 rogo 660: if ((tables.indexOf(destTableName) < 0)) //&& tables.indexOf(names.get(tbIndex)) < 0 && tables.indexOf(convertText(names.get(tbIndex).toString())) < 0 )
1.1 rogo 661: {
1.55 rogo 662:
1.34 rogo 663: if (creates.get(tbIndex).equals("") || creates.get(tbIndex).toString().toLowerCase().indexOf("create") < 0)
664: {
665: System.out.println("Warning empty or invalid create statement - creating one for you\n");
666:
667: command = new StringBuffer(50);
668: command.append("CREATE TABLE ");
669: command.append(beanDest.getQC());
670: command.append(convertText((String) names.get(tbIndex)));
671: command.append(beanDest.getQC());
672: command.append("(");
673: String type = null;
674: Vector columnNames = bean.getColumnNames();
675: for (int i = 0; i < columnNames.size() - 1; ++i)
676: {
677: type = bean.metaData.getColumnTypeName(i + 1);
678: // System.out.println(i+" "+result[1].get(i)+" "+type);
679: type = (type.equals("NUMBER")) ? "INT4" : type;
680: type = (type.equals("CONTAINER")) ? "TEXT" : type;
681:
682: command.append(beanDest.getQC() + convertText((String) columnNames.get(i)) + beanDest.getQC() + " " + type + ", ");
683: }
684: type = bean.metaData.getColumnTypeName(columnNames.size());
685: type = (type.equals("NUMBER")) ? "INT4" : type;
686: type = (type.equals("CONTAINER")) ? "TEXT" : type;
687: command.append(beanDest.getQC() + convertText((String) columnNames.get(columnNames.size() - 1)) + beanDest.getQC() + " " + type);
688: command.append(" )");
689:
690: // System.out.println(command);
691: // System.exit(0);
692: //command.append(DBBean.getQC());
693: } else
1.38 rogo 694: command = new StringBuffer().append(creates.get(tbIndex).toString().toLowerCase());
1.34 rogo 695: stm.executeUpdate(command.toString());
1.1 rogo 696:
1.34 rogo 697: }
1.1 rogo 698: }
1.55 rogo 699: if (dialog != null)
700: dialog.title.setText("Writing table data ...");
1.34 rogo 701:
702: // prepare the insert statement
703: int j = -1;
704: Vector row = null;
705: command = new StringBuffer();
706:
707: command.append("INSERT INTO ");
708: command.append(beanDest.getQC());
1.38 rogo 709: command.append(destTableName); //convertText((String) names.get(tbIndex)));
1.34 rogo 710: command.append(beanDest.getQC());
1.55 rogo 711: command.append(" (");
712: for (int i = 0; i < fieldNames.length; i++)
713: {
714: command.append(fieldNames[i]);
715: if (i < fieldNames.length - 1)
716: command.append(",");
717: }
718: command.append(") ");
719:
1.34 rogo 720: command.append(" values ( ");
721:
1.38 rogo 722: // add a question marks for every field
1.34 rogo 723: for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
724: command.append("?,");
725: command.append("?)");
726: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
727: System.out.println(command);
728: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
729: Vector vec = new Vector(myIds);
730: int endIndex = -1;
731: String tempQuery = query;
732: String tempID = bean.getQC() + idField + bean.getQC();
1.38 rogo 733: // if id_field not do incremental conversion else do it all at once
1.34 rogo 734: if (!idField.equals(""))
735: {
736: long startTime = System.currentTimeMillis();
737: int counter = -1;
738: while (true)
739: {
740: ++counter;
1.55 rogo 741: if (counter == 0 && dialog != null)
1.34 rogo 742: dialog.title.setText("Check if data is available");
1.55 rogo 743: else if (dialog != null)
1.34 rogo 744: dialog.title.setText("Check if more data is available");
745: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, numHits);
746: if (myIds.isEmpty())
747: break;
748: vec = new Vector(myIds);
749: rowCount = vec.size();
750: System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIds.size());
751: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
752: if (vec.size() <= numIntervalls)
753: {
754: endIndex = 0;
755: deltaID = vec.size();
756: }
757: for (int k = 0; k < vec.size() - deltaID; k = k + deltaID)
758: {
759: System.out.println(vec.get(k) + " " + vec.get(k + deltaID) + " " + vec.lastElement());
760: if (query.indexOf("where") > 0)
1.55 rogo 761: tempQuery = query + " and " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID) + "'";
1.34 rogo 762: else
1.55 rogo 763: tempQuery = query + " where " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID) + "'";
1.34 rogo 764: System.out.println(tempQuery);
1.55 rogo 765: if (dialog != null)
766: dialog.title.setText("Reading table data ...");
1.34 rogo 767:
768: bean.makeQuery(tempQuery, deltaID);
1.55 rogo 769: if (dialog != null)
770: dialog.title.setText("Writing table data ...");
1.34 rogo 771:
1.55 rogo 772: command = writeDatainDestTable(dialog, command, k, pstm, rowCount, delimiter);
1.34 rogo 773: endIndex = k + deltaID;
774: }
775: System.out.println(endIndex);
1.38 rogo 776: //all data written ? if not write last chunk of data
1.34 rogo 777: if (endIndex == vec.size() - 1)
778: System.out.println("fits");
779: else
780: {
781: System.out.println(" last intervall from " + vec.get(endIndex) + " " + vec.lastElement());
1.1 rogo 782:
1.34 rogo 783: if (query.indexOf("where") > 0)
1.55 rogo 784: tempQuery = query + " and " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement() + "'";
1.34 rogo 785: else
1.55 rogo 786: tempQuery = query + " where " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement() + "'";
1.34 rogo 787: System.out.println(tempQuery);
1.55 rogo 788: if (dialog != null)
789: dialog.title.setText("Reading table data ...");
1.34 rogo 790: bean.makeQuery(tempQuery, 0);
1.55 rogo 791: if (dialog != null)
792: dialog.title.setText("Writing table data ...");
793: command = writeDatainDestTable(dialog, command, endIndex, pstm, rowCount, delimiter);
1.34 rogo 794: }
1.38 rogo 795: // prepare new query for next chunk
1.34 rogo 796: if (query.indexOf("where") > 0)
1.55 rogo 797: tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
1.34 rogo 798: else
1.55 rogo 799: tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
1.34 rogo 800:
801: }
802: long endTime = System.currentTimeMillis();
803: System.out.println("Time for incremental convert elapsed " + (endTime - startTime));
804: } else
1.29 rogo 805: {
1.38 rogo 806: // read and write all in one big chunk
1.34 rogo 807: long startTime = System.currentTimeMillis();
1.55 rogo 808:
1.34 rogo 809: bean.makeQuery(query, 0);
1.55 rogo 810: command = writeDatainDestTable(dialog, command, j, pstm, rowCount, delimiter);
1.34 rogo 811: long endTime = System.currentTimeMillis();
812: System.out.println("Time for old convert elapsed " + (endTime - startTime));
1.29 rogo 813:
814: }
1.34 rogo 815: }
816: } catch (Exception e)
817: {
818: System.out.println("Error while connecting to database " + e);
819: if (dialog != null)
820: {
821: dialog.setVisible(false);
822: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
823: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
824: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
825: java.io.PrintStream stream = new java.io.PrintStream(b);
826: stream.print(command + "\n\n");
827: e.printStackTrace(stream);
828: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
829: } else
830: {
831: e.printStackTrace();
1.33 rogo 832:
1.3 rogo 833: }
1.1 rogo 834: }
1.34 rogo 835: if (dialog != null)
836: {
837: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
838: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
839: dialog.setVisible(false);
840: }
1.27 rogo 841: }
1.38 rogo 842: /**
843: * Writes data to the destination table
844: * @param dialog progress dialog
845: * @param command
846: * @param j data index for progress bar
847: * @param pstm prepared statement
848: * @param rowCount number of datasets
849: * @return command
850: * @throws Exception
851: * @throws SQLException
852: */
1.55 rogo 853: private static StringBuffer writeDatainDestTable(FM2SQL.ProgressDialog dialog, StringBuffer command, int j, PreparedStatement pstm, int rowCount, String delimiter) throws Exception, SQLException
1.27 rogo 854: {
855: Vector row;
1.34 rogo 856: while ((row = bean.getNextRow()) != null)
857: {
858: j++;
859: // row = (Vector) result[0].get(j);
860: /* command = new StringBuffer();
861:
862: command.append("INSERT INTO ");
863: command.append(beanDest.getQC());
864: command.append(convertText((String) names.get(tbIndex)));
865: command.append(beanDest.getQC());
866: command.append(" values ( ");
867: */
868: //print rows
869: Object obj = null;
870: /* for(int k=0;k<row.size()-1;++k)
871: {
872: obj = row.get(k);
873: //System.out.println("row "+obj+" "+k);
874: if(obj!=null&&!(obj instanceof ArrayList))
875: command.append("'"+convertUml(obj.toString())+"',");
876: else if(obj!=null&& obj instanceof ArrayList)
877: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',");
878: else command.append("NULL,");
879: }
880: obj = row.get(row.size() - 1);
881: if (obj != null && !(obj instanceof ArrayList))
882: command.append("'"+convertUml(obj.toString())+"')");
883: else
884: if(obj!=null&& obj instanceof ArrayList)
885: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')"); //command.append(obj.toString()+")");
886: else command.append("NULL)");
887: */
888: //command.append("'"+row.get(row.size()-1)+"')");
889: //command.append(" )");
890: // for(int k=0;k<row.size();++k)
891:
892: // System.out.println();
893: // System.out.println(command+" "+j+" "+row.size()+" "+ ((Vector)result2[0].get(j)).size());
894: // System.out.println(command);
895: for (int k = 0; k < row.size(); ++k)
896: {
897: obj = row.get(k);
1.55 rogo 898:
899: if (obj instanceof ArrayList)
900: obj = formatFileMakerArray((List) obj, delimiter);
901:
1.34 rogo 902: String str = (obj == null) ? "NULL" : obj.toString();
1.55 rogo 903: if (obj instanceof Double)
904: {
905: pstm.setDouble(k + 1, ((Double) obj).doubleValue());
906: } else if (!str.equals("NULL"))
1.34 rogo 907: pstm.setString(k + 1, str);
908: else
909: pstm.setNull(k + 1, Types.NULL);
910: }
911: pstm.execute();
912: //stm.executeUpdate(command.toString());
913: if (dialog != null)
914: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
915: // System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
916: command = null;
917: } // to while loop
1.27 rogo 918: return command;
1.1 rogo 919: }
920:
1.38 rogo 921: /**
922: * removes special characters from the input string as well as .fp5
923: * @param newName String to change
924: * @return
925: */
1.34 rogo 926: public static String convertText(String newName)
1.1 rogo 927: {
928: StringBuffer alterMe = new StringBuffer(newName.trim().toLowerCase());
929: int length = alterMe.length();
930: int j = 0;
1.34 rogo 931: int index = alterMe.indexOf(".fp5");
932: if (index >= 0)
933: {
934: alterMe.delete(index, index + 4);
935: length = length - 4;
936: }
937:
1.1 rogo 938: while (j < length)
1.34 rogo 939: {
1.1 rogo 940: if (alterMe.charAt(j) == ' ')
941: {
942: alterMe.setCharAt(j, '_');
1.34 rogo 943: // if(j<length-1) j=j+1;
944: } else if (alterMe.charAt(j) == '_')
945: {
946:
947: if (alterMe.charAt(j + 1) == '_')
948: alterMe.deleteCharAt(j);
949: length = length - 1;
950: // if(j<length-1) j=j+1;
951: } else if (alterMe.charAt(j) == 'ä')
952: {
953: alterMe.setCharAt(j, 'a');
954: alterMe.insert(j + 1, "e");
955: length = length + 1;
956: if (j < length - 1)
957: j = j + 1;
958: } else if (alterMe.charAt(j) == 'ö')
959: {
960: alterMe.setCharAt(j, 'o');
961: alterMe.insert(j + 1, "e");
962: length = length + 1;
963: if (j < length - 1)
964: j = j + 1;
965: } else if (alterMe.charAt(j) == 'ü')
966: {
967: alterMe.setCharAt(j, 'u');
968: alterMe.insert(j + 1, "e");
969: length = length + 1;
970: if (j < length - 1)
971: j = j + 1;
972: } else if (alterMe.charAt(j) == 'ß')
973: {
974: alterMe.setCharAt(j, 's');
975: alterMe.insert(j + 1, "s");
976: length = length + 1;
977: if (j < length - 1)
978: j = j + 1;
979: } else if (alterMe.charAt(j) == ':')
1.1 rogo 980: {
1.34 rogo 981: if (j < length - 1)
982: {
983: if (alterMe.charAt(j + 1) == ':')
984: {
985: alterMe.setCharAt(j, '_');
986: alterMe.delete(j + 1, j + 2);
987: length = length - 1;
988:
989: }
1.1 rogo 990:
1.34 rogo 991: if (j < length - 1)
992: j = j + 1;
993: }
994: } else if (alterMe.charAt(j) == '-')
995: {
996: alterMe.setCharAt(j, '_');
1.16 rogo 997:
1.34 rogo 998: } else if (alterMe.charAt(j) == '?')
1.3 rogo 999: {
1.34 rogo 1000: // changed ? to _ because of update statement
1001: alterMe.setCharAt(j, '_');
1002: // length = length + 1;
1003: // j=j+1;
1004: System.out.println(alterMe);
1005: } else if (alterMe.charAt(j) == '.')
1006: {
1007: if (j == length - 1)
1008: {
1009: alterMe.delete(j, j);
1010: length--;
1011: } else
1012: alterMe.setCharAt(j, '_');
1.3 rogo 1013: }
1.34 rogo 1014:
1015: ++j;
1.1 rogo 1016: }
1017: return alterMe.toString();
1018: }
1.38 rogo 1019: /**
1020: * Converts > and < in an entity (> or <)
1021: * @param newName
1022: * @return
1023: */
1.4 rogo 1024: public static String convertToEntities(String newName)
1025: {
1026: StringBuffer alterMe = new StringBuffer(newName.trim());
1027: int length = alterMe.length();
1028: int j = 0;
1029:
1030: while (j < length)
1031: {
1032:
1033: if (alterMe.charAt(j) == '>')
1034: {
1035: alterMe.setCharAt(j, '&');
1036: alterMe.insert(j + 1, "gt;");
1037: length = length + 2;
1038: if (j < length - 1)
1039: j = j + 1;
1040:
1041: } else if (alterMe.charAt(j) == '<')
1042: {
1043: alterMe.setCharAt(j, '&');
1044: alterMe.insert(j + 1, "lt;");
1045: length = length + 2;
1046: if (j < length - 1)
1047: j = j + 1;
1048:
1049: }
1050: ++j;
1051: }
1052: return alterMe.toString();
1053: }
1.38 rogo 1054: /**
1055: * Masks the single quote character '-->\'
1056: * @param newName
1057: * @return
1058: */
1.1 rogo 1059: public static String convertUml(String newName)
1.34 rogo 1060: {
1061: StringBuffer alterMe = new StringBuffer(newName.trim());
1062: int length = alterMe.length();
1063: int j = 0;
1064:
1065: while (j < length)
1066: {
1067:
1068: if (alterMe.charAt(j) == '\'')
1069: {
1070: alterMe.setCharAt(j, '\\');
1071: alterMe.insert(j + 1, "'");
1072: length = length + 1;
1073: if (j < length - 1)
1074: j = j + 1;
1075: }
1076: /* else
1077: if (alterMe.charAt(j) == '"')
1078: {
1079: alterMe.setCharAt(j, '\\');
1080: alterMe.insert(j + 1, "\"");
1081: length = length + 1;
1082: if(j<length-1) j=j+1;
1083: }
1084: else
1085: if (alterMe.charAt(j) == '>')
1086: {
1087: alterMe.setCharAt(j, '\\');
1088: alterMe.insert(j + 1, ">");
1089: length = length + 1;
1090: if(j<length-1) j=j+1;
1091: }
1092: else
1093: if (alterMe.charAt(j) == '<')
1094: {
1095: alterMe.setCharAt(j, '\\');
1096: alterMe.insert(j + 1, "<");
1097: length = length + 1;
1098: if(j<length-1) j=j+1;
1099: }
1100: else
1101: if (alterMe.charAt(j) == '?')
1102: {
1103: alterMe.setCharAt(j, '\\');
1104: alterMe.insert(j + 1, "?");
1105: length = length + 1;
1106: if(j<length-1) j=j+1;
1107: }
1108: else
1109: if (alterMe.charAt(j) == '&')
1110: {
1111: alterMe.setCharAt(j, '\\');
1112: alterMe.insert(j + 1, "&");
1113: length = length + 1;
1114: if(j<length-1) j=j+1;
1115: }
1116: else
1117: if (alterMe.charAt(j) == '=')
1118: {
1119: alterMe.setCharAt(j, '\\');
1120: alterMe.insert(j + 1, "=");
1121: length = length + 1;
1122: if(j<length-1) j=j+1;
1123: }
1124: else
1125: if (alterMe.charAt(j) == ',')
1126: {
1127: alterMe.setCharAt(j, '\\');
1128: alterMe.insert(j + 1, ",");
1129: length = length + 1;
1130: if(j<length-1) j=j+1;
1131: }
1132: else
1133: if (alterMe.charAt(j) == '.')
1134: {
1135: alterMe.setCharAt(j, '\\');
1136: alterMe.insert(j + 1, ".");
1137: length = length + 1;
1138: if(j<length-1) j=j+1;
1139: }
1140: else
1141: if (alterMe.charAt(j) == '[')
1142: {
1143: alterMe.setCharAt(j, '\\');
1144: alterMe.insert(j + 1, ".");
1145: length = length + 1;
1146: if(j<length-1) j=j+1;
1147: }
1148: else
1149: if (alterMe.charAt(j) == ']')
1150: {
1151: alterMe.setCharAt(j, '\\');
1152: alterMe.insert(j + 1, ".");
1153: length = length + 1;
1154: if(j<length-1) j=j+1;
1155: }
1156: else
1157: if (alterMe.charAt(j) == '%')
1158: {
1159: alterMe.setCharAt(j, '\\');
1160: alterMe.insert(j + 1, "%");
1161: length = length + 1;
1162: if(j<length-1) j=j+1;
1163: }*/
1164: ++j;
1165: }
1166: return alterMe.toString();
1167: }
1.55 rogo 1168: /**
1169: * parses the input xml file for batch conversion
1170: * called from readXMLFile
1171: * * @param sb
1172: */
1.34 rogo 1173: public static void parseXMLConfig(StringBuffer sb)
1174: {
1175: boolean finished = false;
1176: // parse string and build document tree
1177: Xparse parser = new Xparse();
1178: parser.changeEntities = true;
1179: Node root = parser.parse(sb.toString());
1180: // printContents(root);
1181: Vector databases = new Vector();
1182: Vector tables = new Vector();
1183: Vector layouts = new Vector();
1184: Vector selects = new Vector();
1185: Vector creates = new Vector();
1186: Vector ids = new Vector();
1.42 rogo 1187: String delimiter = "|";
1.34 rogo 1188: int mode = -1;
1189:
1190: try
1.1 rogo 1191: {
1.34 rogo 1192: Node tempNode = root.find("convert/source", new int[] { 1, 1 });
1193: if (tempNode == null)
1194: throw new Error("parse error source tag missing");
1195: System.out.println(tempNode.name);
1196: int length = countNodes(tempNode);
1197: for (int i = 1; i <= length; i++)
1.1 rogo 1198: {
1.34 rogo 1199:
1.1 rogo 1200: DBBean database = new DBBean();
1.34 rogo 1201: tables = new Vector();
1202: layouts = new Vector();
1203: selects = new Vector();
1204: creates = new Vector();
1205: ids = new Vector();
1.1 rogo 1206: // parse dataBase
1.34 rogo 1207: Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
1208: Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
1209: Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
1210: Node node3 = root.find("convert/source/database", new int[] { 1, 1, i });
1211: Node nodeMode = root.find("convert/source/database/mode", new int[] { 1, 1, i, 1, 1 });
1.55 rogo 1212: Node delimiterNode = root.find("convert/source/database/delimiter", new int[] { 1, 1, i, 1, 1 });
1.34 rogo 1213: if (node3 == null)
1214: throw new Error("parse error database tag missing");
1215: if (node == null)
1216: throw new Error("parse error url tag missing");
1217: if (node1 == null)
1218: throw new Error("parse error user tag missing");
1219: if (node2 == null)
1220: throw new Error("parse error password tag missing");
1.55 rogo 1221: if (delimiterNode != null)
1222: delimiter = delimiterNode.getCharacters();
1.1 rogo 1223: String url = node.getCharacters();
1224: String user = node1.getCharacters();
1225: String password = node2.getCharacters();
1226: database.setURL(url.trim());
1227: database.setUserAndPasswd(user.trim(), password.trim());
1.34 rogo 1228: System.out.println(node.name + " " + node.getCharacters());
1229: System.out.println(node1.name + " " + node1.getCharacters());
1230: System.out.println(node2.name + " " + node2.getCharacters());
1231: String modeString = "";
1232: if (nodeMode == null)
1233: modeString = "convert";
1234: else
1235: modeString = nodeMode.getCharacters();
1236: if (modeString.equals("convert"))
1237: mode = DataBase.CONVERT_MODE;
1238: else if (modeString.equals("append"))
1239: mode = DataBase.APPEND_MODE;
1240: else if (modeString.equals("update"))
1241: mode = DataBase.UPDATE_MODE;
1.48 rogo 1242: else if (modeString.equals("delete"))
1243: mode = DataBase.DELETE_MODE;
1244:
1.34 rogo 1245: // if(node3!=null)
1246: // System.out.println(node3.name);
1247:
1248: int length2 = countNodes(node3);
1249:
1250: System.out.println("number of tables " + length2);
1251:
1252: for (int j = 1; j <= length2; ++j)
1253: {
1254: Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
1255: Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
1256: Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
1257: if (node4 != null)
1258: System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
1259: if (node5 != null)
1260: System.out.println(node5.name + " " + node5.getCharacters());
1261: if (node6 != null)
1262: System.out.println(node6.name + " " + node6.getCharacters());
1263: if (node4 == null)
1264: throw new Error("parse error table tag missing");
1265: // if(node5==null) throw new Error("parse error select tag missing");
1266: // if(node6==null) throw new Error("parse error create tag missing");
1267: String name = (String) node4.attributes.get("name");
1268: String layout = (String) node4.attributes.get("layout");
1269: String id = (String) node4.attributes.get("id");
1270: System.out.println("id was " + id);
1271: if (name == null)
1272: throw new Error("parse error required table tag attribute name missing");
1273: if (layout == null)
1274: layout = "";
1275: if (id == null)
1276: id = "";
1277: if (name.equals(""))
1278: throw new Error("parse error table tag attribute must not be empty");
1279: tables.add(name);
1280: layouts.add(layout);
1281: ids.add(id);
1282: String query = (node5 == null) ? "" : node5.getCharacters();
1283: if (query.equals(""))
1284: System.err.println("Warning empty select tag or select tag missing !!");
1285: query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
1286: selects.add(query);
1287: if (node6 != null)
1288: creates.add(node6.getCharacters().trim());
1289: else
1290: creates.add("");
1291:
1292: }
1.42 rogo 1293: DataBase dataBase = new DataBase(database, tables, layouts, selects, creates, ids, mode);
1294: dataBase.delimiter = delimiter;
1295: databases.add(dataBase);
1.34 rogo 1296: }
1297: DBBean database = new DBBean();
1298: // parse dataBase
1299: Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
1300: Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
1301: Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
1302: String url = node.getCharacters();
1303: String user = node1.getCharacters();
1304: String password = node2.getCharacters();
1305: System.out.println(url);
1306: database.setURL(url.trim());
1307: database.setUserAndPasswd(user.trim(), password.trim());
1308: //databases.add(database);
1309: for (Iterator iter = databases.iterator(); iter.hasNext();)
1.1 rogo 1310: {
1311: DataBase db = (DataBase) iter.next();
1.34 rogo 1312: if (mode != DataBase.UPDATE_MODE)
1.55 rogo 1313: convertBatch(db.bean, database, db.tables, db.layouts, db.selects, db.creates, db.ids, mode, db.delimiter);
1.17 rogo 1314: else
1.34 rogo 1315: update(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates, db.ids, mode);
1.17 rogo 1316:
1.1 rogo 1317: }
1.34 rogo 1318: // printContents(node3);
1319: // FM2SQL.fmInstance=new FM2SQL();
1320: } catch (Exception e)
1321: {
1.55 rogo 1322:
1.34 rogo 1323: e.printStackTrace();
1324: }
1325: }
1326: public static Vector getXMLConfig(String xmlFile)
1327: {
1328: StringBuffer sb = null;
1329: try
1330: {
1331: // read XML Metadata from a file
1332: FileInputStream fi = new FileInputStream(xmlFile);
1333: InputStreamReader isr = new InputStreamReader(fi, "UTF-8");
1334: BufferedReader buffr = new BufferedReader(isr);
1335: sb = new StringBuffer();
1336: int c = 0;
1337: while ((c = buffr.read()) != -1)
1338: {
1339: char ch = (char) c;
1340: sb.append(ch);
1341: // System.out.print((char)c);
1.1 rogo 1342: }
1343:
1.34 rogo 1344: } catch (Exception e)
1345: {
1346: e.printStackTrace();
1347: }
1348:
1349: boolean finished = false;
1350: // parse string and build document tree
1351: Xparse parser = new Xparse();
1352: parser.changeEntities = true;
1353: Node root = parser.parse(sb.toString());
1354: // printContents(root);
1355: Vector databases = new Vector();
1356: Vector tables = new Vector();
1357: Vector layouts = new Vector();
1358: Vector selects = new Vector();
1359: Vector creates = new Vector();
1360: Vector ids = new Vector();
1.55 rogo 1361: String delimiter = "|";
1.34 rogo 1362: int mode = -1;
1363: try
1.1 rogo 1364: {
1.34 rogo 1365: Node tempNode = root.find("convert/source", new int[] { 1, 1 });
1366: if (tempNode == null)
1367: throw new Error("parse error source tag missing");
1368: System.out.println(tempNode.name);
1369: int length = countNodes(tempNode);
1370: for (int i = 1; i <= length; i++)
1.1 rogo 1371: {
1.34 rogo 1372:
1373: DBBean database = new DBBean();
1374: tables = new Vector();
1375: layouts = new Vector();
1376: selects = new Vector();
1377: creates = new Vector();
1378: ids = new Vector();
1379: // parse dataBase
1380: Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
1381: Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
1382: Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
1383: Node node3 = root.find("convert/source/database", new int[] { 1, 1, i });
1384: Node nodeMode = root.find("convert/source/database/mode", new int[] { 1, 1, i, 1, 1 });
1.55 rogo 1385: Node delimiterNode = root.find("convert/source/database/delimiter", new int[] { 1, 1, i, 1, 1 });
1386:
1.44 rogo 1387: if (delimiterNode != null)
1388: delimiter = delimiterNode.getCharacters();
1.34 rogo 1389: if (node3 == null)
1390: throw new Error("parse error database tag missing");
1391: if (node == null)
1392: throw new Error("parse error url tag missing");
1393: if (node1 == null)
1394: throw new Error("parse error user tag missing");
1395: if (node2 == null)
1396: throw new Error("parse error password tag missing");
1397: String url = node.getCharacters();
1398: String user = node1.getCharacters();
1399: String password = node2.getCharacters();
1400: database.setURL(url.trim());
1401: database.setUserAndPasswd(user.trim(), password.trim());
1402: System.out.println(node.name + " " + node.getCharacters());
1403: System.out.println(node1.name + " " + node1.getCharacters());
1404: System.out.println(node2.name + " " + node2.getCharacters());
1405: String modeString = "";
1406: if (nodeMode == null)
1407: modeString = "convert";
1408: else
1409: modeString = nodeMode.getCharacters();
1410: if (modeString.equals("convert"))
1411: mode = DataBase.CONVERT_MODE;
1412: else if (modeString.equals("append"))
1413: mode = DataBase.APPEND_MODE;
1414: else if (modeString.equals("update"))
1415: mode = DataBase.UPDATE_MODE;
1.48 rogo 1416: else if (modeString.equals("delete"))
1417: mode = DataBase.DELETE_MODE;
1418:
1.34 rogo 1419: // if(node3!=null)
1420: // System.out.println(node3.name);
1421:
1422: int length2 = countNodes(node3);
1423:
1424: System.out.println("number of tables " + length2);
1425:
1426: for (int j = 1; j <= length2; ++j)
1427: {
1428: Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
1429: Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
1430: Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
1431: if (node4 != null)
1432: System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
1433: if (node5 != null)
1434: System.out.println(node5.name + " " + node5.getCharacters());
1435: if (node6 != null)
1436: System.out.println(node6.name + " " + node6.getCharacters());
1437: if (node4 == null)
1438: throw new Error("parse error table tag missing");
1439: // if(node5==null) throw new Error("parse error select tag missing");
1440: // if(node6==null) throw new Error("parse error create tag missing");
1441: String name = (String) node4.attributes.get("name");
1442: String layout = (String) node4.attributes.get("layout");
1443: String id = (String) node4.attributes.get("id");
1444: System.out.println("id was " + id);
1445:
1446: if (name == null)
1447: throw new Error("parse error required table tag attribute name missing");
1448: if (layout == null)
1449: layout = "";
1450: if (id == null)
1451: id = "";
1452: if (name.equals(""))
1453: throw new Error("parse error table tag attribute must not be empty");
1454: tables.add(name);
1455: layouts.add(layout);
1456: ids.add(id);
1457: String query = (node5 == null) ? "" : node5.getCharacters();
1458: if (query.equals(""))
1459: System.err.println("Warning empty select tag or select tag missing !!");
1460: query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
1461: selects.add(query);
1462: if (node6 != null)
1463: creates.add(node6.getCharacters().trim());
1464: else
1465: creates.add("");
1466:
1467: }
1.55 rogo 1468: DataBase dataBase = new DataBase(database, tables, layouts, selects, creates, ids, mode);
1469: dataBase.delimiter = delimiter;
1.43 rogo 1470: databases.add(dataBase);
1.1 rogo 1471: }
1.34 rogo 1472: DBBean database = new DBBean();
1473: // parse dataBase
1474: Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
1475: Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
1476: Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
1477: String url = node.getCharacters();
1478: String user = node1.getCharacters();
1479: String password = node2.getCharacters();
1480: System.out.println(url);
1481: database.setURL(url.trim());
1482: database.setUserAndPasswd(user.trim(), password.trim());
1483: databases.add(new DataBase(database, null, null, null, null, null, 0));
1484: //databases.add(database);
1485: /* for (Iterator iter = databases.iterator(); iter.hasNext();)
1486: {
1487: DataBase db = (DataBase) iter.next();
1488: convertBatch(db.bean,database,db.tables,db.layouts,db.selects,db.creates);
1489:
1490: }*/
1491: // printContents(node3);
1492: // FM2SQL.fmInstance=new FM2SQL();
1493: } catch (Exception e)
1494: {
1495: // TODO Auto-generated catch block
1496: e.printStackTrace();
1.1 rogo 1497: }
1.34 rogo 1498: return databases;
1499: }
1500:
1501: private static int countNodes(Node tempNode)
1502: {
1503: int length = 0;
1504: for (int i = 0; i < tempNode.contents.v.size(); ++i)
1.1 rogo 1505: {
1.34 rogo 1506: Node node = (Node) tempNode.contents.v.elementAt(i);
1507: if (node.type.equals("element"))
1.1 rogo 1508: {
1.34 rogo 1509: if (node.name.equals("database"))
1510: length++;
1511: if (node.name.equals("table"))
1512: length++;
1.1 rogo 1513: }
1.34 rogo 1514:
1515: // System.out.println(((Node)tempNode.contents.v.elementAt(i)).attributes+" "+i);
1.1 rogo 1516: }
1.34 rogo 1517: return length;
1518: }
1519: private static void printContents(Node root)
1520: {
1521:
1522: Vector contents = (root.index == null) ? root.contents.v : root.index.v;
1523: for (int i = 0; i < contents.size(); ++i)
1.1 rogo 1524: {
1.34 rogo 1525: Node n = (Node) contents.elementAt(i);
1526: if (n.type.equals("element"))
1527: {
1528: System.out.println("tag " + n.name);
1529: System.out.println(n.getCharacters());
1530: //contents=n.contents.v i=0;
1531: }
1532: // System.out.println(n.type);
1.1 rogo 1533: }
1.34 rogo 1534: }
1.38 rogo 1535: /**
1536: * reads the specified xml file
1537: * @param xmlFile
1538: */
1.34 rogo 1539: public static void readXMLFile(String xmlFile)
1.1 rogo 1540: {
1.34 rogo 1541: try
1542: {
1543: // read XML Metadata from a file
1544: FileInputStream fi = new FileInputStream(xmlFile);
1545: InputStreamReader isr = new InputStreamReader(fi, "UTF-8");
1546: BufferedReader buffr = new BufferedReader(isr);
1547: StringBuffer sb = new StringBuffer();
1548: int c = 0;
1549: while ((c = buffr.read()) != -1)
1550: {
1551: char ch = (char) c;
1552: sb.append(ch);
1553: // System.out.print((char)c);
1554: }
1555: parseXMLConfig(sb);
1556: } catch (Exception e)
1557: {
1558: e.printStackTrace();
1559: }
1.1 rogo 1560: }
1.55 rogo 1561:
1.38 rogo 1562: /**
1563: * Helper class for XML-File parsing
1564: * Holds the parsed data
1565: * @author rogo
1566: *
1567: */
1.34 rogo 1568: public static class DataBase
1569: {
1570: DBBean bean;
1571: Vector creates;
1572: Vector selects;
1573: Vector layouts;
1574: Vector tables;
1575: Vector ids;
1.43 rogo 1576: String delimiter = "//";
1.34 rogo 1577: final static int CONVERT_MODE = 1;
1578: final static int APPEND_MODE = 2;
1579: final static int UPDATE_MODE = 3;
1.40 rogo 1580: final static int DELETE_MODE = 4;
1581:
1.34 rogo 1582: int mode = -1;
1583:
1584: public DataBase(DBBean bean, Vector tables, Vector layouts, Vector selects, Vector creates, Vector ids, int mode)
1585: {
1586: this.bean = bean;
1587: this.tables = tables;
1588: this.layouts = layouts;
1589: this.selects = selects;
1590: this.creates = creates;
1591: this.ids = ids;
1592: this.mode = mode;
1593: this.bean.setIDVector(ids);
1594: }
1.38 rogo 1595: /**
1.42 rogo 1596: * writes the data contained in this object to the buffered writer
1.38 rogo 1597: * * @param buffr
1598: * @throws Exception
1599: */
1.34 rogo 1600: public void exportToXML(BufferedWriter buffr) throws Exception
1601: {
1602: // ids=bean.getIDVector();
1603: buffr.write(" <database>\n");
1604: buffr.write(" <url>" + bean.url + "</url>\n");
1605: buffr.write(" <user>" + bean.user + "</user>\n");
1606: buffr.write(" <password>" + bean.passwd + "</password>\n");
1.55 rogo 1607: buffr.write(" <delimiter>" + delimiter + "</delimiter>\n");
1.34 rogo 1608: String modeString = "";
1609: if (mode == CONVERT_MODE)
1610: modeString = "convert";
1611: else if (mode == APPEND_MODE)
1.5 rogo 1612: modeString = "append";
1.34 rogo 1613: else if (mode == UPDATE_MODE)
1614: modeString = "update";
1.48 rogo 1615: else if (mode == DELETE_MODE)
1.55 rogo 1616: modeString = "delete";
1.34 rogo 1617:
1618: buffr.write(" <mode>" + modeString + "</mode>\n");
1619: int index = 0;
1620: while (index < tables.size())
1621: {
1622: String table = (String) tables.get(index);
1623: String layout = (String) layouts.get(index);
1624: String select = (String) selects.get(index);
1625: String create = (String) creates.get(index);
1626: String id = (String) ids.get(index);
1627:
1628: buffr.write(" <table name = \"" + table + "\" layout = \"" + layout + "\" id = \"" + id + "\" >\n");
1629: buffr.write(" <select>" + convertToEntities(select) + "</select>\n");
1630: if (!create.equals(""))
1631: buffr.write(" <create>" + create + " </create>\n");
1632: buffr.write(" </table>\n");
1633: index++;
1634: }
1635: buffr.write(" </database>\n");
1636: }
1637: public String toString()
1638: {
1639: return bean.url + " " + tables;
1640: }
1.1 rogo 1641:
1.34 rogo 1642: }
1643: public static String convertToUTF8(Object command)
1.1 rogo 1644: {
1.34 rogo 1645: String str = null;
1646: try
1647: {
1648: str = new String(command.toString().getBytes("UTF-8"));
1649: } catch (UnsupportedEncodingException e)
1650: {
1651: // TODO Auto-generated catch block
1652: e.printStackTrace();
1653: }
1654: return str;
1.1 rogo 1655: }
1656: public static void writeConfig(String file, DataBase source, DataBase destination) throws Exception
1657: {
1.34 rogo 1658: if (!file.toLowerCase().endsWith(".xml"))
1659: file += ".xml";
1.1 rogo 1660: File f = new File(file);
1.34 rogo 1661:
1662: FileOutputStream fout = new FileOutputStream(f);
1663: OutputStreamWriter outsw = new OutputStreamWriter(fout, "UTF-8");
1.1 rogo 1664: BufferedWriter buffw = new BufferedWriter(outsw);
1665: buffw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
1666: buffw.newLine();
1667: buffw.write("<convert>\n");
1668: buffw.write(" <source>\n");
1669: source.exportToXML(buffw);
1670: buffw.write(" </source>\n");
1671: buffw.write("\n <destination>\n");
1672: destination.exportToXML(buffw);
1673: buffw.write(" </destination>\n");
1674: buffw.write("</convert>\n");
1675: buffw.close();
1.34 rogo 1676: }
1.46 rogo 1677: public static void delete(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
1678: {
1679: FM2SQL.ProgressDialog dialog = null;
1680: if (FM2SQL.fmInstance != null)
1681: {
1.55 rogo 1682: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
1.46 rogo 1683: dialog.setTitle("Conversion running ...");
1684: dialog.title.setText("Getting table data ...");
1685: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
1686: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
1687: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
1688: dialog.thread = Thread.currentThread();
1689: }
1690: // setting user and passwd
1691: bean.setUserAndPasswd(user, passwd);
1692: // setting user and passwd
1693: beanDest.setUserAndPasswd(userDest, passwdDest);
1694: if (dialog != null)
1695: dialog.setSize(400, 250);
1696: StringBuffer command = null;
1697: String query = null;
1698: try
1699: {
1700: //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
1701: //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
1702: bean.setConnection(source);
1703: if (names == null)
1704: names = bean.getTableNames();
1705: // Collections.sort(names);
1706: int tbIndex = 1;
1707:
1708: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
1709: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
1710: {
1711: Vector[] result = null;
1712: java.util.TreeSet myIds = new TreeSet();
1713: java.util.TreeSet myIdsDest = new TreeSet();
1714: int deltaID = 1;
1715: String idField = "";
1716: String destTableName = "";
1717:
1718: try
1719: {
1720: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
1721: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
1722: query = (selects != null) ? selects.get(tbIndex).toString() : query;
1723: //if vectors[1].get(i) != null)
1724: if (layout != "")
1725: {
1726: layout = " layout " + bean.getQC() + layout + bean.getQC();
1727: String name = names.get(tbIndex).toString();
1728: StringBuffer queryLayout = new StringBuffer(query);
1729: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
1730: query = queryLayout.toString();
1731: System.out.println("added layout " + query);
1732:
1733: }
1734: dialog.title.setText("Getting table data ...");
1735: dialog.table.setText(names.get(tbIndex).toString());
1736: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
1737: dialog.show();
1738: bean.getConnection();
1739: bean.makeQuery(query, 50);
1740: idField = ids.get(tbIndex).toString();
1741:
1742: } catch (Exception e)
1743: {
1744: continue;
1745: }
1746: // determine destTableName from createStatement or from source table name
1747: if (!creates.get(tbIndex).equals(""))
1748: {
1749: String create = creates.get(tbIndex).toString().toLowerCase();
1750: int fromIndex = create.indexOf("table") + 5;
1751: int toIndex = create.indexOf("(");
1752: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
1753: System.out.println("destTable " + destTableName);
1754:
1755: } else
1756: destTableName = convertText(names.get(tbIndex).toString());
1757:
1758: // for id kram
1759: Vector vec = null;
1760: Vector vecDest = null;
1.47 rogo 1761: // tempo
1762: beanDest.setConnection(destination);
1.46 rogo 1763: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
1764: String tempID = bean.getQC() + idField + bean.getQC();
1765: String tempIDdest = beanDest.getQC() + convertText(idField) + beanDest.getQC();
1.47 rogo 1766:
1.46 rogo 1767: int endIndex = -1;
1768: String tempQuery = query;
1.47 rogo 1769: String destQuery = query.replaceAll(names.get(tbIndex).toString(), destTableName);
1.49 rogo 1770: String tempQueryDest = destQuery;
1771: // remove extra query parts destQuery.substring(0,destQuery.lastIndexOf(destTableName)+destTableName.length()+1);
1.47 rogo 1772: System.out.println("new Query " + tempQueryDest);
1.46 rogo 1773: if (!idField.equals(""))
1774: {
1775: long startTime = System.currentTimeMillis();
1776: int counter = -1;
1777: while (true)
1778: {
1779: ++counter;
1780: if (counter == 0 && dialog != null)
1781: dialog.title.setText("Check if data is available");
1782: else if (dialog != null)
1783: dialog.title.setText("Check if more data is available");
1784: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, numHits);
1785: myIdsDest = beanDest.getIDVector(convertText(idField), destTableName, tempQueryDest, numHits);
1786: if (myIds.isEmpty())
1787: break;
1788: vec = new Vector(myIds);
1789: vecDest = new Vector(myIdsDest);
1790: rowCount = vec.size();
1.47 rogo 1791: // Deletion will work this way
1792: Vector deleted = new Vector(vec);
1793: Vector linesToDelete = new Vector(vecDest);
1794: // remove all lines that should not be deleted
1795: linesToDelete.removeAll(deleted);
1796: // System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIdsDest.size());
1.46 rogo 1797: /// @TODO complete delete task remove query show lines to be deleted let user choose if he wants that
1.47 rogo 1798: System.out.println("number of lines to be deleted " + linesToDelete.size());
1.46 rogo 1799: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
1.47 rogo 1800: beanDest.setConnection(destination);
1.46 rogo 1801:
1.47 rogo 1802: Statement stm = beanDest.getConnection().createStatement();
1.46 rogo 1803:
1.47 rogo 1804: Vector tables = beanDest.getTableNames();
1805: // Collections.sort(tables);
1806: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
1807: tables = beanDest.getTableNames();
1808: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
1809: stm = beanDest.getConnection().createStatement();
1810:
1811: if (dialog != null)
1812: dialog.title.setText(" Deleting table data ...");
1813:
1814: int j = -1;
1815:
1816: Vector row = null;
1817: command = new StringBuffer();
1818:
1819: command.append("DELETE FROM");
1820: command.append(beanDest.getQC());
1821: command.append(destTableName);
1822: //command.append(convertText((String) names.get(tbIndex)));
1823: command.append(beanDest.getQC());
1824: int size = bean.getColumnNames().size();
1825: command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
1826: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
1827: System.out.println(command + " " + tbIndex);
1828: //int rowCount = bean.getRowCount(query);
1829: // int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
1830: while (true)
1.46 rogo 1831: {
1832:
1.48 rogo 1833: ++j;
1.47 rogo 1834: if (j == linesToDelete.size())
1835: break;
1836: //print rows
1837: pstm.setString(1, linesToDelete.get(j).toString());
1838: System.out.println(pstm.toString());
1.55 rogo 1839: pstm.execute();
1.46 rogo 1840: if (dialog != null)
1.47 rogo 1841: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
1842: command = null;
1.46 rogo 1843: }
1844: // prepare new query for next chunk
1845: if (query.indexOf("where") > 0)
1846: tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
1847: else
1848: tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
1849:
1.47 rogo 1850: } //to outer while
1851: } // to idfield if
1852: } // table loop
1853:
1854: } catch (Exception e)
1.55 rogo 1855: {
1856: System.out.println("Error while connecting to database " + e);
1.47 rogo 1857: if (dialog != null)
1858: {
1.55 rogo 1859: dialog.setVisible(false);
1.47 rogo 1860: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1861: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1.55 rogo 1862: }
1863: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
1864: java.io.PrintStream stream = new java.io.PrintStream(b);
1865: stream.print(command + "\n\n");
1866: e.printStackTrace(stream);
1867: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
1868:
1869: }
1870: if (dialog != null)
1871: {
1872: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1873: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1.46 rogo 1874:
1.55 rogo 1875: dialog.setVisible(false);
1876: }
1877: } // to method
1.56 ! rogo 1878:
! 1879: /**
! 1880: * Converts input String in norman encoding to unicode
! 1881: * @param inp
! 1882: * @return converted String
! 1883: */
! 1884: static public String normanToUnicode(String inp) {
! 1885: StringBuffer buf = new StringBuffer();
! 1886: for (int i = 0; i < inp.length(); i++) {
! 1887: char c = inp.charAt(i);
! 1888: switch (c) {
! 1889: case 1: buf.append("\u00d0"); break; // Eth
! 1890: case 2: buf.append("\u00f0"); break; // eth
! 1891: case 3: buf.append("\u0141"); break; // Lslash
! 1892: case 4: buf.append("\u0142"); break; // lslash
! 1893: case 5: buf.append("\u0160"); break; // S caron
! 1894: case 6: buf.append("\u0161"); break; // s caron
! 1895: case 7: buf.append("\u00dd"); break; // Y acute
! 1896: case 8: buf.append("\u00fd"); break; // y acute
! 1897: case 11: buf.append("\u00de"); break; // Thorn
! 1898: case 12: buf.append("\u00fe"); break; // thorn
! 1899: case 14: buf.append("\u017d"); break; // Z caron
! 1900: case 15: buf.append("\u017e"); break; // z caron
! 1901: case 17: buf.append("\u0073"); break; // asciitilde
! 1902: case 18: buf.append("j\u0305"); break; // j macron [does a single char exist?]
! 1903: case 19: buf.append("^"); break; // circumflex
! 1904: case 20: buf.append("\u0303"); break; // tilde
! 1905: case 21: buf.append("\u00bd"); break; // onehalf
! 1906: case 22: buf.append("\u00bc"); break; // onequarter
! 1907: case 23: buf.append("\u00b9"); break; // onesuperior
! 1908: case 24: buf.append("\u00be"); break; // threequarters
! 1909: case 25: buf.append("\u00b3"); break; // threesuperior
! 1910: case 26: buf.append("\u00b2"); break; // twosuperior
! 1911: case 27: buf.append("\u00a6"); break; // brokenbar
! 1912: case 28: buf.append("-"); break; // minus
! 1913: case 29: buf.append("\u00d7"); break; // multiply
! 1914: case 39: buf.append("'"); break; // quotesingle
! 1915: case 94: buf.append("\u0302"); break; // circumflex
! 1916: case 96: buf.append("\u0300"); break; // grave
! 1917: case 128: buf.append("\u00c4"); break; // A dieresis
! 1918: case 129: buf.append("\u00c5"); break; // A ring
! 1919: case 131: buf.append("\u00c9"); break; // E acute
! 1920: case 132: buf.append("\u00d1"); break; // N tilde
! 1921: case 133: buf.append("\u00d6"); break; // O dieresis
! 1922: case 134: buf.append("\u00dc"); break; // U dieresis
! 1923: case 135: buf.append("\u00e1"); break; // a acute
! 1924: case 136: buf.append("\u00e0"); break; // a grave
! 1925: case 137: buf.append("\u00e2"); break; // a circumflex
! 1926: case 138: buf.append("\u00e4"); break; // a dieresis
! 1927: case 139: buf.append("\u00e3"); break; // a tilde
! 1928: case 140: buf.append("\u0101"); break; // a macron
! 1929: case 141: buf.append("\u00e7"); break; // c cedilla
! 1930: case 142: buf.append("\u00e9"); break; // e acute
! 1931: case 143: buf.append("\u00e8"); break; // e grave
! 1932: case 144: buf.append("\u00ea"); break; // e circumflex
! 1933: case 145: buf.append("\u00eb"); break; // e dieresis
! 1934: case 146: buf.append("\u00ed"); break; // i acute
! 1935: case 147: buf.append("\u00ec"); break; // i grave
! 1936: case 148: buf.append("\u00ee"); break; // i circumflex
! 1937: case 149: buf.append("\u00ef"); break; // i dieresis
! 1938: case 150: buf.append("\u00f1"); break; // n tilde
! 1939: case 151: buf.append("\u00f3"); break; // o acute
! 1940: case 152: buf.append("\u00f2"); break; // o grave
! 1941: case 153: buf.append("\u00f4"); break; // o circumflex
! 1942: case 154: buf.append("\u00f6"); break; // o dieresis
! 1943: case 155: buf.append("\u00f5"); break; // o tilde
! 1944: case 156: buf.append("\u00fa"); break; // u acute
! 1945: case 157: buf.append("\u00f9"); break; // u grave
! 1946: case 158: buf.append("\u00fb"); break; // u circumflex
! 1947: case 159: buf.append("\u00fc"); break; // u dieresis
! 1948: case 160: buf.append("\u1e6d"); break; // t underdot
! 1949: case 161: buf.append("\u00b0"); break; // degree
! 1950: case 162: buf.append("\u1ebd"); break; // e tilde
! 1951: case 163: buf.append("\u00a3"); break; // sterling
! 1952: case 164: buf.append("\u00a7"); break; // section
! 1953: case 166: buf.append("\u00b6"); break; // paragraph
! 1954: case 167: buf.append("\u015b"); break; // s acute
! 1955: case 168: buf.append("\u1e5b"); break; // r underdot
! 1956: case 169: buf.append("\u1e67"); break; // s caron
! 1957: case 171: buf.append("\u0301"); break; // acute
! 1958: case 172: buf.append("\u0308"); break; // dieresis
! 1959: case 173: buf.append("\u1e6d"); break; // t underdot
! 1960: case 174: buf.append("\u00c6"); break; // AE
! 1961: case 175: buf.append("\u014d"); break; // o macron
! 1962: case 176: buf.append("\u0129"); break; // i tilde
! 1963: case 177: buf.append("\u00b1"); break; // plusminus
! 1964: case 180: buf.append("\u012b"); break; // i macron
! 1965: case 181: buf.append("\u1e43"); break; // m underdot
! 1966: case 182: buf.append("\u1e0d"); break; // d underdot
! 1967: case 183: buf.append("\u1e63"); break; // s underdot
! 1968: case 185: buf.append("\u017a"); break; // z acute
! 1969: case 186: buf.append("\u1e45"); break; // n overdot
! 1970: case 189: buf.append("\u0169"); break; // u tilde
! 1971: case 190: buf.append("\u00e6"); break; // ae
! 1972: case 191: buf.append("\u00f8"); break; // oslash
! 1973: case 192: buf.append("\u0304\u0306"); break; // macron breve
! 1974: case 194: buf.append("\u1e37"); break; //
! 1975: case 195: buf.append("j\u0305"); break; // j macron [does a single char exist?]
! 1976: case 196: buf.append("\u0103"); break; // a breve
! 1977: case 197: buf.append("\u016d"); break; // u breve
! 1978: case 200: buf.append("\u1e42"); break; // M underdot
! 1979: case 201: buf.append("\u2026"); break; // ellipsis
! 1980: case 203: buf.append("\u00c0"); break; // A grave
! 1981: case 204: buf.append("\u00c3"); break; // A tilde
! 1982: case 205: buf.append("\u00d5"); break; // O tilde
! 1983: case 206: buf.append("m\u0306"); break; // m breve
! 1984: case 207: buf.append("\u0153"); break; // oe
! 1985: case 208: buf.append("\u2013"); break; // endash
! 1986: case 209: buf.append("\u2014"); break; // emdash
! 1987: case 210: buf.append("\u201c"); break; // quotedblleft
! 1988: case 211: buf.append("\u201d"); break; // quotedblright
! 1989: case 212: buf.append("\u2018"); break; // quoteleft
! 1990: case 213: buf.append("\u2019"); break; // quoteright
! 1991: case 214: buf.append("\u1e37"); break; // l underring [actually underdot]
! 1992: case 215: buf.append("\u1e41"); break; // m overdot
! 1993: case 216: buf.append("n\u0306"); break; // n breve
! 1994: case 217: buf.append("\u00d7"); break; // multiply
! 1995: case 219: buf.append("\u1e5b"); break; // r underring [actually underdot]
! 1996: case 220: buf.append("\u1e44"); break; // N overdot
! 1997: case 221: buf.append("\u1e62"); break; // S underdot
! 1998: case 222: buf.append("\u1e24"); break; // H underdot
! 1999: case 223: buf.append("\u1e0c"); break; // D underdot
! 2000: case 224: buf.append("\u2021"); break; // daggerdbl
! 2001: case 226: buf.append("\u1e36"); break; // L underdot
! 2002: case 227: buf.append("\u0113"); break; // e macron
! 2003: case 229: buf.append("\u1e5f"); break; // r underbar
! 2004: case 230: buf.append("r\u0324"); break; // r underdieresis
! 2005: case 231: buf.append("\u012a"); break; // I macron
! 2006: case 232: buf.append("\u016b"); break; // u macron
! 2007: case 233: buf.append("\u01e6c"); break; // T underdot
! 2008: case 234: buf.append("\u1e64"); break; // S acute
! 2009: case 235: buf.append("\u2020"); break; // dagger
! 2010: case 236: buf.append("\u0115"); break; // e breve
! 2011: case 237: buf.append("\u014f"); break; // o breve
! 2012: case 238: buf.append("\u0100"); break; // A macron
! 2013: case 239: buf.append("\u1e46"); break; // N underdot
! 2014: case 241: buf.append("\u1e3b"); break; // l underbar
! 2015: case 242: buf.append("\u016a"); break; // U macron
! 2016: case 243: buf.append("\u0179"); break; // Z acute
! 2017: case 244: buf.append("\u1e5a"); break; // R underdot
! 2018: case 245: buf.append("\u0131"); break; // dotlessi
! 2019: case 246: buf.append("\u1e47"); break; // n underdot
! 2020: case 247: buf.append("\u1e49"); break; // n underbar
! 2021: case 248: buf.append("\u0304"); break; // macron
! 2022: case 249: buf.append("\u0306"); break; // breve
! 2023: case 250: buf.append("\u1e25"); break; // h underdot
! 2024: case 251: buf.append("\u012d"); break; // i breve
! 2025: case 252: buf.append("\u0327"); break; // cedilla
! 2026: case 253: buf.append("\u030b"); break; // hungarumlaut
! 2027: case 254: buf.append("\u0328"); break; // ogonek
! 2028: case 255: buf.append("\u030c"); break; // caron
! 2029: case 130: buf.append("\u012b\u0303"); break; // imacron tilde
! 2030: case 165: buf.append("\u1e5d"); break; // runderdot macron
! 2031: case 170: buf.append("\u016b\0306"); break; // umacron breve
! 2032: case 178: buf.append("\u0101\u0301"); break; // amacron acute
! 2033: case 179: buf.append("\u016b\u0301"); break; // umacron acute
! 2034: case 184: buf.append("\u0113\u0301"); break; // emacron acute
! 2035: case 187: buf.append("\u0113\u0300"); break; // emacron breve
! 2036: case 188: buf.append("\u014d\u0300"); break; // omacron breve
! 2037: case 193: buf.append("\u0101\u0306"); break; // amacron breve
! 2038: case 198: buf.append("\u0101\u0303"); break; // amacron tilde
! 2039: case 199: buf.append("\u012b\u0301"); break; // imacron acute
! 2040: case 218: buf.append("\u1e00"); break; // runderdotmacron acute
! 2041: case 225: buf.append("\u1e5b\u0301"); break; // runderdot acute
! 2042: case 228: buf.append("\u012b\u0306"); break; // imacron breve
! 2043: case 240: buf.append("\u016b\u0303"); break; // umacron tilde
! 2044: default: buf.append(c); break;
! 2045: }
! 2046: }
! 2047: return buf.toString();
! 2048: }
1.46 rogo 2049:
1.55 rogo 2050: }
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