1: /*
2: * Convert.java -- Converter class - Filemaker to SQL Converter
3: * Copyright (C) 2003 Robert Gordesch (rogo@mpiwg-berlin.mpg.de)
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:
15: import java.awt.Cursor;
16: import java.io.BufferedReader;
17: import java.io.BufferedWriter;
18: import java.io.File;
19: import java.io.FileInputStream;
20: import java.io.FileNotFoundException;
21: import java.io.FileOutputStream;
22: import java.io.InputStream;
23: import java.io.InputStreamReader;
24: import java.io.OutputStreamWriter;
25: import java.io.PrintStream;
26: import java.io.UnsupportedEncodingException;
27: import java.net.URL;
28: import java.sql.PreparedStatement;
29: import java.sql.SQLException;
30: import java.sql.Statement;
31: import java.sql.Types;
32: import java.text.ParseException;
33: import java.util.ArrayList;
34: import java.util.Hashtable;
35: import java.util.Iterator;
36: import java.util.List;
37: import java.util.StringTokenizer;
38: import java.util.TreeSet;
39: import java.util.Vector;
40:
41: import com.exploringxml.xml.Node;
42: import com.exploringxml.xml.Xparse;
43:
44: class Convert
45: {
46: /**Helper class for index creation
47: * @author rogo
48: *
49: */
50: public static class IndexList extends Vector
51: {
52: public String toString()
53: {
54: StringBuffer buff = new StringBuffer(1000);
55: int count = 0;
56: for (Iterator iter = this.iterator(); iter.hasNext();)
57: {
58: String element = (String) iter.next();
59: if (count < elementCount - 1)
60: {
61: buff.append(element).append(", ");
62: count++;
63: } else
64: buff.append(element);
65:
66: }
67:
68: return buff.toString();
69: }
70: }
71: static DBBean bean = new DBBean();
72: static DBBean beanDest = new DBBean();
73:
74: static String user = "", passwd = "e1nste1n";
75: static String userDest = "postgres", passwdDest = "rogo";
76: static boolean batchRun = false;
77: static Vector databases = new Vector();
78: final static int numHits = 5000;
79: final static int numIntervalls = 2;
80: public static void main(String args[])
81: {
82: /* try
83: {
84: //byte[] b = "ö".getBytes("UTF-8");
85: // System.out.println("QueryString " +b[0]+" "+b[1]+(new String(b).getBytes()[0])+" "+new String(b).getBytes()[1]);
86: //System.out.println(new String(b,"UTF-8"));
87: } catch (UnsupportedEncodingException e)
88: {
89: e.printStackTrace();
90: }*/
91: FileOutputStream file = null;
92: if (args.length != 1)
93: {
94: System.out.println("Usage: java Convert <xml config file>");
95: System.exit(-1);
96: }
97: try
98: {
99: file = new FileOutputStream("./log.txt");
100: } catch (FileNotFoundException e1)
101: {
102: e1.printStackTrace();
103: }
104: PrintStream stream = new PrintStream(file);
105: // System.setOut(stream);
106: //System.setErr(stream);
107:
108: readXMLFile(args[0]);
109: if (!(new File(args[0]).exists()))
110: System.exit(0);
111:
112: System.out.println("Finished!");
113: //convert("jdbc:fmpro:http://141.14.237.74:8050","jdbc:postgresql://erebos/test",null,null);
114: }
115: public static void convertBatch(DBBean source, DBBean destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode, String delimiter) throws Exception
116: {
117: bean = source;
118: beanDest = destination;
119: convert(null, null, names, layouts, selects, creates, ids, mode, delimiter);
120: if (true)
121: return;
122: StringBuffer command = null;
123: try
124: {
125: bean.setConnection(source.url);
126: if (names == null)
127: names = bean.getTableNames();
128: //Collections.sort(names);
129: int tbIndex = 1;
130:
131: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
132: {
133: Vector[] result = null;
134: try
135: {
136: String query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
137: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
138: query = (selects != null) ? selects.get(tbIndex).toString() : query;
139: //if vectors[1].get(i) != null)
140: if (!layout.equals(""))
141: {
142: System.out.println("before " + query + " table" + names.get(tbIndex));
143: layout = " layout " + bean.getQC() + layout + bean.getQC();
144: String name = names.get(tbIndex).toString();
145: StringBuffer queryLayout = new StringBuffer(query);
146: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
147: query = queryLayout.toString();
148: System.out.println("added layout " + query);
149:
150: }
151: System.out.println(" performing query " + query);
152: //result = bean.getQueryData(query, null, 0);
153: bean.getConnection();
154: bean.makeQuery(query, 0);
155: } catch (Exception e)
156: {
157: System.out.println(e.getMessage());
158: e.printStackTrace();
159: continue;
160: }
161: //beanDest.setConnection("jdbc:postgresql://erebos/test3");
162: beanDest.setConnection(destination.url);
163:
164: Statement stm = beanDest.getConnection().createStatement();
165:
166: Vector tables = beanDest.getTableNames();
167: // Collections.sort(tables);
168: System.out.println("converting table " + names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
169: tables = beanDest.getTableNames();
170: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
171: stm = beanDest.getConnection().createStatement();
172: // System.exit(0);
173: if (mode == Convert.DataBase.CONVERT_MODE)
174: {
175: if (tables.indexOf(names.get(tbIndex)) >= 0)
176: {
177: stm.executeUpdate("drop table " + beanDest.getQC() + names.get(tbIndex) + beanDest.getQC());
178: tables.remove((String) names.get(tbIndex));
179: System.out.println("dropped table " + names.get(tbIndex));
180: } else if (tables.indexOf(convertText(names.get(tbIndex).toString())) >= 0)
181: {
182: stm.executeUpdate("drop table " + beanDest.getQC() + convertText((String) names.get(tbIndex)) + beanDest.getQC());
183: tables.remove(convertText((String) names.get(tbIndex)));
184: System.out.println("dropped table " + names.get(tbIndex));
185: }
186:
187: if (tables.indexOf(names.get(tbIndex)) < 0 && tables.indexOf(convertText(names.get(tbIndex).toString())) < 0)
188: {
189: if (creates.get(tbIndex).equals("") || creates.get(tbIndex).toString().toLowerCase().indexOf("create") < 0)
190: {
191: System.out.println("Warning empty or invalid create statement - creating one for you\n");
192:
193: command = new StringBuffer(50);
194: command.append("CREATE TABLE ");
195: command.append(beanDest.getQC());
196: command.append(convertText((String) names.get(tbIndex)));
197: command.append(beanDest.getQC());
198: command.append("(");
199: String type = null;
200: Vector columnNames = bean.getColumnNames();
201: for (int i = 0; i < columnNames.size() - 1; ++i)
202: {
203: type = bean.metaData.getColumnTypeName(i + 1);
204: // System.out.println(i+" "+result[1].get(i)+" "+type);
205: type = (type.equals("NUMBER")) ? "INT4" : type;
206: type = (type.equals("CONTAINER")) ? "TEXT" : type;
207:
208: command.append(beanDest.getQC() + convertText((String) columnNames.get(i)) + beanDest.getQC() + " " + type + ", ");
209: }
210: type = bean.metaData.getColumnTypeName(columnNames.size());
211: type = (type.equals("NUMBER")) ? "INT4" : type;
212: type = (type.equals("CONTAINER")) ? "TEXT" : type;
213: command.append(beanDest.getQC() + convertText((String) columnNames.get(columnNames.size() - 1)) + beanDest.getQC() + " " + type);
214: command.append(" )");
215: } else
216: command = new StringBuffer().append(creates.get(tbIndex).toString());
217:
218: System.out.println(command);
219: // System.exit(0);
220: //command.append(DBBean.getQC());
221: stm.executeUpdate(command.toString());
222:
223: }
224: }
225: Vector row = null;
226: command = new StringBuffer();
227:
228: command.append("INSERT INTO ");
229: command.append(beanDest.getQC());
230: command.append(convertText((String) names.get(tbIndex)));
231: command.append(beanDest.getQC());
232: command.append(" values ( ");
233:
234: for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
235: command.append("?,");
236: command.append("?)");
237: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
238: System.out.println(command);
239: while ((row = bean.getNextRow()) != null)
240: {
241: //print rows
242: Object obj = null;
243: for (int k = 0; k < row.size(); ++k)
244: {
245: obj = row.get(k);
246: if (obj instanceof ArrayList)
247: obj = formatFileMakerArray((List) obj, "\n");
248: String str = (obj == null) ? "NULL" : obj.toString();
249: if (!str.equals("NULL"))
250: pstm.setString(k + 1, str);
251: else
252: pstm.setNull(k + 1, Types.NULL);
253: }
254: pstm.execute();
255:
256: } // to for loop
257:
258: }
259: } catch (Exception e)
260: {
261: System.out.println("Error while connecting to database " + e);
262: //dialog.setVisible(false);
263: //dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
264: //FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
265: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
266: java.io.PrintStream stream = new java.io.PrintStream(b);
267: stream.print(command + "\n\n");
268: e.printStackTrace(stream);
269: System.err.println(b);
270: //FM2SQL.showErrorDialog(b.toString(), "Error occured !");
271:
272: }
273: // dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
274: //FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
275:
276: // dialog.setVisible(false);
277: }
278: public static String formatFileMakerArray(List list, String delimiter)
279: {
280: StringBuffer formattedString = new StringBuffer();
281: for (int i = 0; i < list.size(); ++i)
282: {
283: formattedString.append(list.get(i).toString());
284: if (i < list.size() - 1)
285: formattedString.append(delimiter);
286: }
287: return formattedString.toString();
288: }
289: /**
290: * Method for SQL UPDATE
291: * @param source
292: * @param destination
293: * @param names
294: * @param layouts
295: * @param selects
296: * @param creates
297: * @param ids
298: * @param mode
299: * @throws Exception
300: */
301: public static void update(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
302: {
303: FM2SQL.ProgressDialog dialog = null;
304: if (FM2SQL.fmInstance != null)
305: {
306: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
307: dialog.setTitle("Conversion running ...");
308: dialog.title.setText("Getting table data ...");
309: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
310: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
311: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
312: dialog.thread = Thread.currentThread();
313: }
314: // setting user and passwd
315: bean.setUserAndPasswd(user, passwd);
316: // setting user and passwd
317: beanDest.setUserAndPasswd(userDest, passwdDest);
318: if (dialog != null)
319: dialog.setSize(400, 250);
320: StringBuffer command = null;
321: String query = null;
322: try
323: {
324: //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
325: //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
326: bean.setConnection(source);
327: if (names == null)
328: names = bean.getTableNames();
329: // Collections.sort(names);
330: int tbIndex = 1;
331:
332: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
333: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
334: {
335: Vector[] result = null;
336: String destTableName = "";
337: try
338: {
339: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
340: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
341: query = (selects != null) ? selects.get(tbIndex).toString() : query;
342: //if vectors[1].get(i) != null)
343: if (layout != "")
344: {
345: layout = " layout " + bean.getQC() + layout + bean.getQC();
346: String name = names.get(tbIndex).toString();
347: StringBuffer queryLayout = new StringBuffer(query);
348: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
349: query = queryLayout.toString();
350: System.out.println("added layout " + query);
351:
352: }
353: dialog.title.setText("Getting table data ...");
354: dialog.table.setText(names.get(tbIndex).toString());
355: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
356: dialog.show();
357: bean.getConnection();
358: bean.makeQuery(query, 0);
359: } catch (Exception e)
360: {
361: continue;
362: }
363: // determine destTableName from createStatement or from source table name
364: if (!creates.get(tbIndex).equals(""))
365: {
366: String create = creates.get(tbIndex).toString().toLowerCase();
367: int fromIndex = create.indexOf("table") + 5;
368: int toIndex = create.indexOf("(");
369: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
370: System.out.println("destTable " + destTableName);
371:
372: } else
373: destTableName = convertText(names.get(tbIndex).toString());
374:
375: //beanDest.setConnection("jdbc:postgresql://erebos/test3");
376: beanDest.setConnection(destination);
377:
378: Statement stm = beanDest.getConnection().createStatement();
379:
380: Vector tables = beanDest.getTableNames();
381: // Collections.sort(tables);
382: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
383: tables = beanDest.getTableNames();
384: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
385: stm = beanDest.getConnection().createStatement();
386: // System.exit(0);
387:
388: if (dialog != null)
389: dialog.title.setText("Updating table data ...");
390:
391: int j = -1;
392:
393: Vector row = null;
394: command = new StringBuffer();
395:
396: command.append("UPDATE ");
397: command.append(beanDest.getQC());
398: command.append(destTableName);
399: //command.append(convertText((String) names.get(tbIndex)));
400: command.append(beanDest.getQC());
401: command.append(" SET ");
402:
403: int size = bean.getColumnNames().size();
404: for (int i = 0; i < size - 1; ++i)
405: command.append(beanDest.getQC() + convertText((String) bean.getColumnNames().get(i)) + beanDest.getQC() + " = ? ,");
406: command.append(convertText((String) bean.getColumnNames().get(size - 1)) + " = ? ");
407: command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
408: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
409: System.out.println(command + " " + tbIndex);
410: int rowCount = bean.getRowCount(query);
411: int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
412: while ((row = bean.getNextRow()) != null)
413: {
414: j++;
415: //print rows
416: Object obj = null;
417: /* for(int k=0;k<row.size()-1;++k)
418: {
419: obj = row.get(k);
420: //System.out.println("row "+obj+" "+k);
421: if(obj!=null&&!(obj instanceof ArrayList))
422: command.append("'"+convertUml(obj.toString())+"',");
423: else if(obj!=null&& obj instanceof ArrayList)
424: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',");
425: else command.append("NULL,");
426: }
427: obj = row.get(row.size() - 1);
428: if (obj != null && !(obj instanceof ArrayList))
429: command.append("'"+convertUml(obj.toString())+"')");
430: else
431: if(obj!=null&& obj instanceof ArrayList)
432: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')"); //command.append(obj.toString()+")");
433: else command.append("NULL)");
434: */
435: //command.append("'"+row.get(row.size()-1)+"')");
436: //command.append(" )");
437: // for(int k=0;k<row.size();++k)
438:
439: // System.out.println();
440: // System.out.println(command+" "+j+" "+row.size()+" "+ ((Vector)result2[0].get(j)).size());
441: // System.out.println(command);
442: for (int k = 0; k < row.size(); ++k)
443: {
444: obj = row.get(k);
445: if (obj instanceof ArrayList)
446: obj = ((List) obj).get(0);
447: String str = (obj == null) ? "NULL" : obj.toString();
448: if (!str.equals("NULL"))
449: pstm.setString(k + 1, str);
450: else
451: pstm.setNull(k + 1, Types.NULL);
452: }
453: pstm.setString(row.size() + 1, row.get(idIndex).toString());
454: //System.out.println(pstm.toString());
455: // System.exit(0);
456: pstm.execute();
457: //stm.executeUpdate(command.toString());
458: if (dialog != null)
459: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
460: // System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
461: command = null;
462: } // to for loop
463:
464: }
465: } catch (Exception e)
466: {
467: System.out.println("Error while connecting to database " + e);
468: if (dialog != null)
469: {
470: dialog.setVisible(false);
471: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
472: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
473: }
474: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
475: java.io.PrintStream stream = new java.io.PrintStream(b);
476: stream.print(command + "\n\n");
477: e.printStackTrace(stream);
478: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
479:
480: }
481: if (dialog != null)
482: {
483: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
484: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
485:
486: dialog.setVisible(false);
487: }
488:
489: }
490: /**
491: * transfers the specified array of tables to the destination database
492: and creates the table if it does not exist if it exists and mode is not append the table is dropped
493:
494: * @param source
495: * @param destination
496: * @param names
497: * @param layouts
498: * @param selects
499: * @param creates
500: * @param ids
501: * @param mode
502: * @throws Exception
503: */
504:
505: public static void convert(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode, String delimiter) throws Exception
506: {
507:
508: FM2SQL.ProgressDialog dialog = null;
509:
510: if (FM2SQL.fmInstance != null)
511: {
512: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
513: dialog.setTitle("Conversion running ...");
514: dialog.title.setText("Getting table data ...");
515: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
516: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
517: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
518: dialog.thread = Thread.currentThread();
519: dialog.setSize(400, 250);
520: }
521: java.util.TreeSet myIds = new TreeSet();
522: int deltaID = 1;
523: String idField = "";
524: String destTableName = "";
525: String[] fieldNames = null;
526: if (source != null && destination != null)
527: {
528: // setting user and passwd
529: bean.setUserAndPasswd(user, passwd);
530: // setting user and passwd
531: beanDest.setUserAndPasswd(userDest, passwdDest);
532: }
533: StringBuffer command = null;
534: String query = null;
535: try
536: {
537: if (source != null)
538: bean.setConnection(source);
539: else
540: bean.setConnection(bean.url);
541:
542: if (names == null)
543: names = bean.getTableNames();
544: // Collections.sort(names);
545: int tbIndex = 1;
546:
547: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
548: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
549: {
550: Vector[] result = null;
551: try
552: {
553: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
554: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
555: query = (selects != null) ? selects.get(tbIndex).toString() : query;
556: //if vectors[1].get(i) != null)
557: if (layout != "")
558: {
559: layout = " layout " + bean.getQC() + layout + bean.getQC();
560: String name = names.get(tbIndex).toString();
561: StringBuffer queryLayout = new StringBuffer(query);
562: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
563: query = queryLayout.toString();
564: System.out.println("added layout " + query);
565:
566: }
567: // if ( layout!= "")
568: // query += " layout " + bean.getQC() + layout + bean.getQC();
569: if (dialog != null)
570: {
571: dialog.title.setText("Reading table data ...");
572: dialog.table.setText(names.get(tbIndex).toString());
573: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
574: dialog.show();
575: }
576: //result = bean.getQueryData(query, dialog, 0);
577: bean.getConnection();
578: bean.makeQuery(query, 50);
579: idField = ids.get(tbIndex).toString();
580:
581: } catch (Exception e)
582: {
583: System.out.println(e);
584: continue;
585: }
586: if (destination != null)
587: beanDest.setConnection(destination);
588: else
589: beanDest.setConnection(beanDest.url);
590: Statement stm = beanDest.getConnection().createStatement();
591:
592: Vector tables = beanDest.getTableNames();
593: // Collections.sort(tables);
594: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
595: tables = beanDest.getTableNames();
596: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
597: stm = beanDest.getConnection().createStatement();
598: // System.exit(0);
599:
600: // determine destTableName from createStatement or from source table name
601: ConversionProperties prop = getFieldNamesAndDestTableName(creates.get(tbIndex).toString(), query, names.get(tbIndex).toString());
602: destTableName = prop.destTableName;
603: fieldNames = prop.fieldNames;
604: if (mode == Convert.DataBase.CONVERT_MODE)
605: {
606:
607: if (tables.indexOf(destTableName) >= 0)
608: {
609: stm.executeUpdate("drop table " + beanDest.getQC() + destTableName + beanDest.getQC());
610: tables.remove(destTableName);
611: System.out.println("dropped table" + destTableName);
612:
613: }
614: /*
615: if(destTableName.equals(""))
616: if (tables.indexOf(names.get(tbIndex)) >= 0)
617: {
618: stm.executeUpdate("drop table " + beanDest.getQC() + names.get(tbIndex) + beanDest.getQC());
619: tables.remove((String) names.get(tbIndex));
620: System.out.println("dropped table" + names.get(tbIndex));
621: } else if (tables.indexOf(convertText(names.get(tbIndex).toString())) >= 0)
622: {
623: stm.executeUpdate("drop table " + beanDest.getQC() + convertText((String) names.get(tbIndex)) + beanDest.getQC());
624: tables.remove(convertText((String) names.get(tbIndex)));
625: System.out.println("dropped table" + names.get(tbIndex));
626: }
627: */
628: if ((tables.indexOf(destTableName) < 0)) //&& tables.indexOf(names.get(tbIndex)) < 0 && tables.indexOf(convertText(names.get(tbIndex).toString())) < 0 )
629: {
630:
631: if (creates.get(tbIndex).equals("") || creates.get(tbIndex).toString().toLowerCase().indexOf("create") < 0)
632: {
633: System.out.println("Warning empty or invalid create statement - creating one for you\n");
634:
635: command = new StringBuffer(50);
636: command.append("CREATE TABLE ");
637: command.append(beanDest.getQC());
638: command.append(convertText((String) names.get(tbIndex)));
639: command.append(beanDest.getQC());
640: command.append("(");
641: String type = null;
642: Vector columnNames = bean.getColumnNames();
643: for (int i = 0; i < columnNames.size() - 1; ++i)
644: {
645: type = bean.metaData.getColumnTypeName(i + 1);
646: // System.out.println(i+" "+result[1].get(i)+" "+type);
647: type = (type.equals("NUMBER")) ? "INT4" : type;
648: type = (type.equals("CONTAINER")) ? "TEXT" : type;
649:
650: command.append(beanDest.getQC() + convertText((String) columnNames.get(i)) + beanDest.getQC() + " " + type + ", ");
651: }
652: type = bean.metaData.getColumnTypeName(columnNames.size());
653: type = (type.equals("NUMBER")) ? "INT4" : type;
654: type = (type.equals("CONTAINER")) ? "TEXT" : type;
655: command.append(beanDest.getQC() + convertText((String) columnNames.get(columnNames.size() - 1)) + beanDest.getQC() + " " + type);
656: command.append(" )");
657:
658: // System.out.println(command);
659: // System.exit(0);
660: //command.append(DBBean.getQC());
661: } else
662: command = new StringBuffer().append(creates.get(tbIndex).toString().toLowerCase());
663: stm.executeUpdate(command.toString());
664:
665: }
666: }
667: if (dialog != null)
668: dialog.title.setText("Writing table data ...");
669:
670: // prepare the insert statement
671: int j = -1;
672: Vector row = null;
673: command = new StringBuffer();
674:
675: command.append("INSERT INTO ");
676: command.append(beanDest.getQC());
677: command.append(destTableName); //convertText((String) names.get(tbIndex)));
678: command.append(beanDest.getQC());
679: command.append(" (");
680: for (int i = 0; i < fieldNames.length; i++)
681: {
682: command.append(fieldNames[i]);
683: if (i < fieldNames.length - 1)
684: command.append(",");
685: }
686: command.append(") ");
687:
688: command.append(" values ( ");
689:
690: // add a question marks for every field
691: for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
692: command.append("?,");
693: command.append("?)");
694: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
695: System.out.println(command);
696: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
697: Vector vec = new Vector(myIds);
698: int endIndex = -1;
699: String tempQuery = query;
700: String tempID = bean.getQC() + idField + bean.getQC();
701: // if id_field not do incremental conversion else do it all at once
702: if (!idField.equals(""))
703: {
704: long startTime = System.currentTimeMillis();
705: int counter = -1;
706: while (true)
707: {
708: ++counter;
709: if (counter == 0 && dialog != null)
710: dialog.title.setText("Check if data is available");
711: else if (dialog != null)
712: dialog.title.setText("Check if more data is available");
713: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, numHits);
714: if (myIds.isEmpty())
715: break;
716: vec = new Vector(myIds);
717: rowCount = vec.size();
718: System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIds.size());
719: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
720: if (vec.size() <= numIntervalls)
721: {
722: endIndex = 0;
723: deltaID = vec.size();
724: }
725: for (int k = 0; k < vec.size() - deltaID; k = k + deltaID)
726: {
727: System.out.println(vec.get(k) + " " + vec.get(k + deltaID) + " " + vec.lastElement());
728: if (query.indexOf("where") > 0)
729: tempQuery = query + " and " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID) + "'";
730: else
731: tempQuery = query + " where " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID) + "'";
732: System.out.println(tempQuery);
733: if (dialog != null)
734: dialog.title.setText("Reading table data ...");
735:
736: bean.makeQuery(tempQuery, deltaID);
737: if (dialog != null)
738: dialog.title.setText("Writing table data ...");
739:
740: command = writeDatainDestTable(dialog, command, k, pstm, rowCount, delimiter);
741: endIndex = k + deltaID;
742: }
743: System.out.println(endIndex);
744: //all data written ? if not write last chunk of data
745: if (endIndex == vec.size() - 1)
746: System.out.println("fits");
747: else
748: {
749: System.out.println(" last intervall from " + vec.get(endIndex) + " " + vec.lastElement());
750:
751: if (query.indexOf("where") > 0)
752: tempQuery = query + " and " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement() + "'";
753: else
754: tempQuery = query + " where " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement() + "'";
755: System.out.println(tempQuery);
756: if (dialog != null)
757: dialog.title.setText("Reading table data ...");
758: bean.makeQuery(tempQuery, 0);
759: if (dialog != null)
760: dialog.title.setText("Writing table data ...");
761: command = writeDatainDestTable(dialog, command, endIndex, pstm, rowCount, delimiter);
762: }
763: // prepare new query for next chunk
764: if (query.indexOf("where") > 0)
765: tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
766: else
767: tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
768:
769: }
770: long endTime = System.currentTimeMillis();
771: System.out.println("Time for incremental convert elapsed " + (endTime - startTime));
772: } else
773: {
774: // read and write all in one big chunk
775: long startTime = System.currentTimeMillis();
776:
777: bean.makeQuery(query, 0);
778: command = writeDatainDestTable(dialog, command, j, pstm, rowCount, delimiter);
779: long endTime = System.currentTimeMillis();
780: System.out.println("Time for old convert elapsed " + (endTime - startTime));
781:
782: }
783: }
784: } catch (Exception e)
785: {
786: System.out.println("Error while connecting to database " + e);
787: if (dialog != null)
788: {
789: dialog.setVisible(false);
790: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
791: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
792: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
793: java.io.PrintStream stream = new java.io.PrintStream(b);
794: stream.print(command + "\n\n");
795: e.printStackTrace(stream);
796: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
797: } else
798: {
799: e.printStackTrace();
800:
801: }
802: }
803: if (dialog != null)
804: {
805: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
806: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
807: dialog.setVisible(false);
808: }
809: }
810: /**
811: * Writes data to the destination table
812: * @param dialog progress dialog
813: * @param command
814: * @param j data index for progress bar
815: * @param pstm prepared statement
816: * @param rowCount number of datasets
817: * @return command
818: * @throws Exception
819: * @throws SQLException
820: */
821: private static StringBuffer writeDatainDestTable(FM2SQL.ProgressDialog dialog, StringBuffer command, int j, PreparedStatement pstm, int rowCount, String delimiter) throws Exception, SQLException
822: {
823: Vector row;
824: while ((row = bean.getNextRow()) != null)
825: {
826: j++;
827: // row = (Vector) result[0].get(j);
828: /* command = new StringBuffer();
829:
830: command.append("INSERT INTO ");
831: command.append(beanDest.getQC());
832: command.append(convertText((String) names.get(tbIndex)));
833: command.append(beanDest.getQC());
834: command.append(" values ( ");
835: */
836: //print rows
837: Object obj = null;
838: /* for(int k=0;k<row.size()-1;++k)
839: {
840: obj = row.get(k);
841: //System.out.println("row "+obj+" "+k);
842: if(obj!=null&&!(obj instanceof ArrayList))
843: command.append("'"+convertUml(obj.toString())+"',");
844: else if(obj!=null&& obj instanceof ArrayList)
845: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',");
846: else command.append("NULL,");
847: }
848: obj = row.get(row.size() - 1);
849: if (obj != null && !(obj instanceof ArrayList))
850: command.append("'"+convertUml(obj.toString())+"')");
851: else
852: if(obj!=null&& obj instanceof ArrayList)
853: command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')"); //command.append(obj.toString()+")");
854: else command.append("NULL)");
855: */
856: //command.append("'"+row.get(row.size()-1)+"')");
857: //command.append(" )");
858: // for(int k=0;k<row.size();++k)
859:
860: // System.out.println();
861: // System.out.println(command+" "+j+" "+row.size()+" "+ ((Vector)result2[0].get(j)).size());
862: // System.out.println(command);
863: for (int k = 0; k < row.size(); ++k)
864: {
865: obj = row.get(k);
866:
867: if (obj instanceof ArrayList)
868: obj = formatFileMakerArray((List) obj, delimiter);
869:
870: String str = (obj == null) ? "NULL" : obj.toString();
871: if (obj instanceof Double)
872: {
873: pstm.setDouble(k + 1, ((Double) obj).doubleValue());
874: } else if (!str.equals("NULL"))
875: pstm.setString(k + 1, str);
876: else
877: pstm.setNull(k + 1, Types.NULL);
878: }
879: pstm.execute();
880: //stm.executeUpdate(command.toString());
881: if (dialog != null)
882: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
883: // System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
884: command = null;
885: } // to while loop
886: return command;
887: }
888:
889: /**
890: * removes special characters from the input string as well as .fp5
891: * @param newName String to change
892: * @return
893: */
894: public static String convertText(String newName)
895: {
896: StringBuffer alterMe = new StringBuffer(newName.trim().toLowerCase());
897: int length = alterMe.length();
898: int j = 0;
899: int index = alterMe.indexOf(".fp5");
900: if (index >= 0)
901: {
902: alterMe.delete(index, index + 4);
903: length = length - 4;
904: }
905:
906: while (j < length)
907: {
908: if (alterMe.charAt(j) == ' ')
909: {
910: alterMe.setCharAt(j, '_');
911: // if(j<length-1) j=j+1;
912: } else if (alterMe.charAt(j) == '_')
913: {
914:
915: if (alterMe.charAt(j + 1) == '_')
916: alterMe.deleteCharAt(j);
917: length = length - 1;
918: // if(j<length-1) j=j+1;
919: } else if (alterMe.charAt(j) == 'ä')
920: {
921: alterMe.setCharAt(j, 'a');
922: alterMe.insert(j + 1, "e");
923: length = length + 1;
924: if (j < length - 1)
925: j = j + 1;
926: } else if (alterMe.charAt(j) == 'ö')
927: {
928: alterMe.setCharAt(j, 'o');
929: alterMe.insert(j + 1, "e");
930: length = length + 1;
931: if (j < length - 1)
932: j = j + 1;
933: } else if (alterMe.charAt(j) == 'ü')
934: {
935: alterMe.setCharAt(j, 'u');
936: alterMe.insert(j + 1, "e");
937: length = length + 1;
938: if (j < length - 1)
939: j = j + 1;
940: } else if (alterMe.charAt(j) == 'ß')
941: {
942: alterMe.setCharAt(j, 's');
943: alterMe.insert(j + 1, "s");
944: length = length + 1;
945: if (j < length - 1)
946: j = j + 1;
947: } else if (alterMe.charAt(j) == ':')
948: {
949: if (j < length - 1)
950: {
951: if (alterMe.charAt(j + 1) == ':')
952: {
953: alterMe.setCharAt(j, '_');
954: alterMe.delete(j + 1, j + 2);
955: length = length - 1;
956:
957: }
958:
959: if (j < length - 1)
960: j = j + 1;
961: }
962: } else if (alterMe.charAt(j) == '-')
963: {
964: alterMe.setCharAt(j, '_');
965:
966: } else if (alterMe.charAt(j) == '?')
967: {
968: // changed ? to _ because of update statement
969: alterMe.setCharAt(j, '_');
970: // length = length + 1;
971: // j=j+1;
972: System.out.println(alterMe);
973: } else if (alterMe.charAt(j) == '.')
974: {
975: if (j == length - 1)
976: {
977: alterMe.delete(j, j);
978: length--;
979: } else
980: alterMe.setCharAt(j, '_');
981: }
982:
983: ++j;
984: }
985: return alterMe.toString();
986: }
987: /**
988: * Converts > and < in an entity (> or <)
989: * @param newName
990: * @return
991: */
992: public static String convertToEntities(String newName)
993: {
994: StringBuffer alterMe = new StringBuffer(newName.trim());
995: int length = alterMe.length();
996: int j = 0;
997:
998: while (j < length)
999: {
1000:
1001: if (alterMe.charAt(j) == '>')
1002: {
1003: alterMe.setCharAt(j, '&');
1004: alterMe.insert(j + 1, "gt;");
1005: length = length + 2;
1006: if (j < length - 1)
1007: j = j + 1;
1008:
1009: } else if (alterMe.charAt(j) == '<')
1010: {
1011: alterMe.setCharAt(j, '&');
1012: alterMe.insert(j + 1, "lt;");
1013: length = length + 2;
1014: if (j < length - 1)
1015: j = j + 1;
1016:
1017: }
1018: ++j;
1019: }
1020: return alterMe.toString();
1021: }
1022: /**
1023: * Masks the single quote character '-->\'
1024: * @param newName
1025: * @return
1026: */
1027: public static String convertUml(String newName)
1028: {
1029: StringBuffer alterMe = new StringBuffer(newName.trim());
1030: int length = alterMe.length();
1031: int j = 0;
1032:
1033: while (j < length)
1034: {
1035:
1036: if (alterMe.charAt(j) == '\'')
1037: {
1038: alterMe.setCharAt(j, '\\');
1039: alterMe.insert(j + 1, "'");
1040: length = length + 1;
1041: if (j < length - 1)
1042: j = j + 1;
1043: }
1044: /* else
1045: if (alterMe.charAt(j) == '"')
1046: {
1047: alterMe.setCharAt(j, '\\');
1048: alterMe.insert(j + 1, "\"");
1049: length = length + 1;
1050: if(j<length-1) j=j+1;
1051: }
1052: else
1053: if (alterMe.charAt(j) == '>')
1054: {
1055: alterMe.setCharAt(j, '\\');
1056: alterMe.insert(j + 1, ">");
1057: length = length + 1;
1058: if(j<length-1) j=j+1;
1059: }
1060: else
1061: if (alterMe.charAt(j) == '<')
1062: {
1063: alterMe.setCharAt(j, '\\');
1064: alterMe.insert(j + 1, "<");
1065: length = length + 1;
1066: if(j<length-1) j=j+1;
1067: }
1068: else
1069: if (alterMe.charAt(j) == '?')
1070: {
1071: alterMe.setCharAt(j, '\\');
1072: alterMe.insert(j + 1, "?");
1073: length = length + 1;
1074: if(j<length-1) j=j+1;
1075: }
1076: 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: ++j;
1133: }
1134: return alterMe.toString();
1135: }
1136: /**
1137: * parses the input xml file for batch conversion
1138: * called from readXMLFile
1139: * * @param sb
1140: */
1141: public static void parseXMLConfig(StringBuffer sb)
1142: {
1143: boolean finished = false;
1144: // parse string and build document tree
1145: Xparse parser = new Xparse();
1146: parser.changeEntities = true;
1147: Node root = parser.parse(sb.toString());
1148: // printContents(root);
1149: Vector databases = new Vector();
1150: Vector tables = new Vector();
1151: Vector layouts = new Vector();
1152: Vector selects = new Vector();
1153: Vector creates = new Vector();
1154: Vector ids = new Vector();
1155: Vector indexListVec = new Vector();
1156: String delimiter = "|";
1157: int mode = -1;
1158:
1159: try
1160: {
1161: Node tempNode = root.find("convert/source", new int[] { 1, 1 });
1162: if (tempNode == null)
1163: throw new Error("parse error source tag missing");
1164: System.out.println(tempNode.name);
1165: int length = countNodes(tempNode);
1166: for (int i = 1; i <= length; i++)
1167: {
1168:
1169: DBBean database = new DBBean();
1170: tables = new Vector();
1171: layouts = new Vector();
1172: selects = new Vector();
1173: creates = new Vector();
1174: ids = new Vector();
1175: indexListVec = new Vector();
1176: // parse dataBase
1177: Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
1178: Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
1179: Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
1180: Node node3 = root.find("convert/source/database", new int[] { 1, 1, i });
1181: Node nodeMode = root.find("convert/source/database/mode", new int[] { 1, 1, i, 1, 1 });
1182: Node delimiterNode = root.find("convert/source/database/delimiter", new int[] { 1, 1, i, 1, 1 });
1183: Node useNormanToUnicodeMapper = root.find("convert/source/database/usenormantounicodemapper", new int[] { 1, 1, i, 1, 1 });
1184:
1185: if (node3 == null)
1186: throw new Error("parse error database tag missing");
1187: if (node == null)
1188: throw new Error("parse error url tag missing");
1189: if (node1 == null)
1190: throw new Error("parse error user tag missing");
1191: if (node2 == null)
1192: throw new Error("parse error password tag missing");
1193: if (delimiterNode != null)
1194: delimiter = delimiterNode.getCharacters();
1195: if (useNormanToUnicodeMapper != null)
1196: {
1197: database.setUseNormanToUnicodeMapper(Boolean.valueOf(useNormanToUnicodeMapper.getCharacters()).booleanValue());
1198: System.out.println("useMapper " + Boolean.valueOf(useNormanToUnicodeMapper.getCharacters().trim()).booleanValue());
1199: }
1200:
1201: String url = node.getCharacters();
1202: String user = node1.getCharacters();
1203: String password = node2.getCharacters();
1204: database.setURL(url.trim());
1205: database.setUserAndPasswd(user.trim(), password.trim());
1206: System.out.println(node.name + " " + node.getCharacters());
1207: System.out.println(node1.name + " " + node1.getCharacters());
1208: System.out.println(node2.name + " " + node2.getCharacters());
1209: String modeString = "";
1210: if (nodeMode == null)
1211: modeString = "convert";
1212: else
1213: modeString = nodeMode.getCharacters();
1214: if (modeString.equals("convert"))
1215: mode = DataBase.CONVERT_MODE;
1216: else if (modeString.equals("append"))
1217: mode = DataBase.APPEND_MODE;
1218: else if (modeString.equals("update"))
1219: mode = DataBase.UPDATE_MODE;
1220: else if (modeString.equals("delete"))
1221: mode = DataBase.DELETE_MODE;
1222: else if (modeString.equals("synchronize"))
1223: mode = DataBase.SYNCHRONIZE_MODE;
1224:
1225: // if(node3!=null)
1226: // System.out.println(node3.name);
1227:
1228: int length2 = countNodes(node3);
1229:
1230: System.out.println("number of tables " + length2);
1231:
1232: for (int j = 1; j <= length2; ++j)
1233: {
1234: Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
1235: Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
1236: Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
1237: if (node4 != null)
1238: System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
1239: if (node5 != null)
1240: System.out.println(node5.name + " " + node5.getCharacters());
1241: if (node6 != null)
1242: System.out.println(node6.name + " " + node6.getCharacters());
1243: if (node4 == null)
1244: throw new Error("parse error table tag missing");
1245: // if(node5==null) throw new Error("parse error select tag missing");
1246: // if(node6==null) throw new Error("parse error create tag missing");
1247: String name = (String) node4.attributes.get("name");
1248: String layout = (String) node4.attributes.get("layout");
1249: String id = (String) node4.attributes.get("id");
1250: String indexList = (String) node4.attributes.get("indexList");
1251:
1252: System.out.println("id was " + id);
1253: if (name == null)
1254: throw new Error("parse error required table tag attribute name missing");
1255: if (layout == null)
1256: layout = "";
1257: if (id == null)
1258: id = "";
1259: if (indexList == null)
1260: indexList = "";
1261: if (name.equals(""))
1262: throw new Error("parse error table tag attribute must not be empty");
1263: tables.add(name);
1264: layouts.add(layout);
1265:
1266: ids.add(id);
1267: indexListVec.add(indexList);
1268: String query = (node5 == null) ? "" : node5.getCharacters();
1269: if (query.equals(""))
1270: System.err.println("Warning empty select tag or select tag missing !!");
1271: query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
1272: selects.add(query);
1273: if (node6 != null)
1274: creates.add(node6.getCharacters().trim());
1275: else
1276: creates.add("");
1277:
1278: }
1279: DataBase dataBase = new DataBase(database, tables, layouts, selects, creates, ids, mode);
1280: dataBase.delimiter = delimiter;
1281: dataBase.buildIndexTable(indexListVec);
1282: databases.add(dataBase);
1283: }
1284: DBBean database = new DBBean();
1285: // parse dataBase
1286: Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
1287: Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
1288: Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
1289: String url = node.getCharacters();
1290: String user = node1.getCharacters();
1291: String password = node2.getCharacters();
1292: System.out.println(url);
1293: database.setURL(url.trim());
1294: database.setUserAndPasswd(user.trim(), password.trim());
1295: //databases.add(database);
1296: for (Iterator iter = databases.iterator(); iter.hasNext();)
1297: {
1298: DataBase db = (DataBase) iter.next();
1299: if (mode == DataBase.CONVERT_MODE || mode == DataBase.APPEND_MODE)
1300: convertBatch(db.bean, database, db.tables, db.layouts, db.selects, db.creates, db.ids, mode, db.delimiter);
1301: else if (mode == DataBase.UPDATE_MODE)
1302: update(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates, db.ids, mode);
1303: else if (mode == DataBase.SYNCHRONIZE_MODE)
1304: {
1305: user = bean.user;
1306: passwd = bean.passwd;
1307: userDest = database.user;
1308: passwdDest = database.passwd;
1309:
1310: synchronize(db.bean.url, database.url, db.tables, db.layouts, db.selects, db.creates, db.ids, mode, db.delimiter, new Vector(db.htIndex.values()));
1311: }
1312: }
1313: // printContents(node3);
1314: // FM2SQL.fmInstance=new FM2SQL();
1315: } catch (Exception e)
1316: {
1317:
1318: e.printStackTrace();
1319:
1320: } finally
1321: {
1322: bean.closeAllConnections();
1323: beanDest.closeAllConnections();
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);
1342: }
1343:
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();
1361: String delimiter = "|";
1362: int mode = -1;
1363: try
1364: {
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++)
1371: {
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 });
1385: Node delimiterNode = root.find("convert/source/database/delimiter", new int[] { 1, 1, i, 1, 1 });
1386: Node useNormanToUnicodeMapper = root.find("convert/source/database/usenormantounicodemapper", new int[] { 1, 1, i, 1, 1 });
1387:
1388: if (delimiterNode != null)
1389: delimiter = delimiterNode.getCharacters();
1390: if (useNormanToUnicodeMapper != null)
1391: {
1392: database.setUseNormanToUnicodeMapper(Boolean.valueOf(useNormanToUnicodeMapper.getCharacters()).booleanValue());
1393: System.out.println("useMapper " + Boolean.valueOf(useNormanToUnicodeMapper.getCharacters().trim()).booleanValue());
1394: }
1395:
1396: if (node3 == null)
1397: throw new Error("parse error database tag missing");
1398: if (node == null)
1399: throw new Error("parse error url tag missing");
1400: if (node1 == null)
1401: throw new Error("parse error user tag missing");
1402: if (node2 == null)
1403: throw new Error("parse error password tag missing");
1404: String url = node.getCharacters();
1405: String user = node1.getCharacters();
1406: String password = node2.getCharacters();
1407: database.setURL(url.trim());
1408: database.setUserAndPasswd(user.trim(), password.trim());
1409: System.out.println(node.name + " " + node.getCharacters());
1410: System.out.println(node1.name + " " + node1.getCharacters());
1411: System.out.println(node2.name + " " + node2.getCharacters());
1412: String modeString = "";
1413: if (nodeMode == null)
1414: modeString = "convert";
1415: else
1416: modeString = nodeMode.getCharacters();
1417: if (modeString.equals("convert"))
1418: mode = DataBase.CONVERT_MODE;
1419: else if (modeString.equals("append"))
1420: mode = DataBase.APPEND_MODE;
1421: else if (modeString.equals("update"))
1422: mode = DataBase.UPDATE_MODE;
1423: else if (modeString.equals("delete"))
1424: mode = DataBase.DELETE_MODE;
1425:
1426: // if(node3!=null)
1427: // System.out.println(node3.name);
1428:
1429: int length2 = countNodes(node3);
1430:
1431: System.out.println("number of tables " + length2);
1432:
1433: for (int j = 1; j <= length2; ++j)
1434: {
1435: Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
1436: Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
1437: Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
1438: if (node4 != null)
1439: System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
1440: if (node5 != null)
1441: System.out.println(node5.name + " " + node5.getCharacters());
1442: if (node6 != null)
1443: System.out.println(node6.name + " " + node6.getCharacters());
1444: if (node4 == null)
1445: throw new Error("parse error table tag missing");
1446: // if(node5==null) throw new Error("parse error select tag missing");
1447: // if(node6==null) throw new Error("parse error create tag missing");
1448: String name = (String) node4.attributes.get("name");
1449: String layout = (String) node4.attributes.get("layout");
1450: String id = (String) node4.attributes.get("id");
1451: System.out.println("id was " + id);
1452:
1453: if (name == null)
1454: throw new Error("parse error required table tag attribute name missing");
1455: if (layout == null)
1456: layout = "";
1457: if (id == null)
1458: id = "";
1459: if (name.equals(""))
1460: throw new Error("parse error table tag attribute must not be empty");
1461: tables.add(name);
1462: layouts.add(layout);
1463: ids.add(id);
1464: String query = (node5 == null) ? "" : node5.getCharacters();
1465: if (query.equals(""))
1466: System.err.println("Warning empty select tag or select tag missing !!");
1467: query = (query.equals("")) ? "select * from " + database.getQC() + name + database.getQC() : query;
1468: selects.add(query);
1469: if (node6 != null)
1470: creates.add(node6.getCharacters().trim());
1471: else
1472: creates.add("");
1473:
1474: }
1475: DataBase dataBase = new DataBase(database, tables, layouts, selects, creates, ids, mode);
1476: dataBase.delimiter = delimiter;
1477: databases.add(dataBase);
1478: }
1479: DBBean database = new DBBean();
1480: // parse dataBase
1481: Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
1482: Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
1483: Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
1484: String url = node.getCharacters();
1485: String user = node1.getCharacters();
1486: String password = node2.getCharacters();
1487: System.out.println(url);
1488: database.setURL(url.trim());
1489: database.setUserAndPasswd(user.trim(), password.trim());
1490: databases.add(new DataBase(database, null, null, null, null, null, 0));
1491: //databases.add(database);
1492: /* for (Iterator iter = databases.iterator(); iter.hasNext();)
1493: {
1494: DataBase db = (DataBase) iter.next();
1495: convertBatch(db.bean,database,db.tables,db.layouts,db.selects,db.creates);
1496:
1497: }*/
1498: // printContents(node3);
1499: // FM2SQL.fmInstance=new FM2SQL();
1500: } catch (Exception e)
1501: {
1502: // TODO Auto-generated catch block
1503: e.printStackTrace();
1504: }
1505: return databases;
1506: }
1507:
1508: private static int countNodes(Node tempNode)
1509: {
1510: int length = 0;
1511: for (int i = 0; i < tempNode.contents.v.size(); ++i)
1512: {
1513: Node node = (Node) tempNode.contents.v.elementAt(i);
1514: if (node.type.equals("element"))
1515: {
1516: if (node.name.equals("database"))
1517: length++;
1518: if (node.name.equals("table"))
1519: length++;
1520: }
1521:
1522: // System.out.println(((Node)tempNode.contents.v.elementAt(i)).attributes+" "+i);
1523: }
1524: return length;
1525: }
1526: private static void printContents(Node root)
1527: {
1528:
1529: Vector contents = (root.index == null) ? root.contents.v : root.index.v;
1530: for (int i = 0; i < contents.size(); ++i)
1531: {
1532: Node n = (Node) contents.elementAt(i);
1533: if (n.type.equals("element"))
1534: {
1535: System.out.println("tag " + n.name);
1536: System.out.println(n.getCharacters());
1537: //contents=n.contents.v i=0;
1538: }
1539: // System.out.println(n.type);
1540: }
1541: }
1542: /**
1543: * reads the specified xml file
1544: * @param xmlFile
1545: */
1546: public static void readXMLFile(String xmlFile)
1547: {
1548: try
1549: {
1550: InputStream stream = null;
1551:
1552: if (xmlFile.indexOf("file://") >= 0 || xmlFile.indexOf("http://") >= 0)
1553: {
1554: URL url = new URL(xmlFile);
1555: stream = url.openStream();
1556: } else
1557: // read XML Metadata from a file
1558: stream = new FileInputStream(xmlFile);
1559: InputStreamReader isr = new InputStreamReader(stream, "UTF-8");
1560: BufferedReader buffr = new BufferedReader(isr);
1561: StringBuffer sb = new StringBuffer();
1562: int c = 0;
1563: while ((c = buffr.read()) != -1)
1564: {
1565: char ch = (char) c;
1566: sb.append(ch);
1567: // System.out.print((char)c);
1568: }
1569: parseXMLConfig(sb);
1570: } catch (Exception e)
1571: {
1572: e.printStackTrace();
1573: }
1574: }
1575: /**
1576: * Helper class for Conversion etc
1577: * Holds the some data
1578: * @author rogo
1579: *
1580: */
1581:
1582: public static class ConversionProperties
1583: {
1584: String destTableName;
1585: String[] fieldNames;
1586: public ConversionProperties()
1587: {
1588: }
1589: public ConversionProperties(String destTableName, String[] fieldNames)
1590: {
1591: this.destTableName = destTableName;
1592: this.fieldNames = fieldNames;
1593:
1594: }
1595:
1596: }
1597: /**
1598: * Helper class for XML-File parsing
1599: * Holds the parsed data
1600: * @author rogo
1601: *
1602: */
1603: public static class DataBase
1604: {
1605: DBBean bean;
1606: Vector creates;
1607: Vector selects;
1608: Vector layouts;
1609: Vector tables;
1610: Vector ids;
1611: String delimiter = "//";
1612: /**
1613: * maps table name to index fields
1614: */
1615: Hashtable htIndex = new Hashtable();
1616: boolean useNormanToUnicodeMapper = false;
1617:
1618: final static int CONVERT_MODE = 1;
1619: final static int APPEND_MODE = 2;
1620: final static int UPDATE_MODE = 3;
1621: final static int DELETE_MODE = 4;
1622: final static int SYNCHRONIZE_MODE = 5;
1623:
1624: int mode = -1;
1625:
1626: public DataBase(DBBean bean, Vector tables, Vector layouts, Vector selects, Vector creates, Vector ids, int mode)
1627: {
1628: this.bean = bean;
1629: this.tables = tables;
1630: this.layouts = layouts;
1631: this.selects = selects;
1632: this.creates = creates;
1633: this.ids = ids;
1634: this.mode = mode;
1635: this.bean.setIDVector(ids);
1636: }
1637: /**
1638: * @param indexListVec
1639: */
1640: public void buildIndexTable(Vector indexListVec)
1641: {
1642: for (int i = 0; i < tables.size(); i++)
1643: {
1644: fillIndexList((String) tables.get(i), (String) indexListVec.get(i));
1645: }
1646: }
1647: /**
1648: * writes the data contained in this object to the buffered writer
1649: * * @param buffr
1650: * @throws Exception
1651: */
1652: public void exportToXML(BufferedWriter buffr) throws Exception
1653: {
1654: // ids=bean.getIDVector();
1655: buffr.write(" <database>\n");
1656: buffr.write(" <url>" + bean.url + "</url>\n");
1657: buffr.write(" <user>" + bean.user + "</user>\n");
1658: buffr.write(" <password>" + bean.passwd + "</password>\n");
1659: buffr.write(" <delimiter>" + delimiter + "</delimiter>\n");
1660: String modeString = "";
1661: if (mode == CONVERT_MODE)
1662: modeString = "convert";
1663: else if (mode == APPEND_MODE)
1664: modeString = "append";
1665: else if (mode == UPDATE_MODE)
1666: modeString = "update";
1667: else if (mode == DELETE_MODE)
1668: modeString = "delete";
1669:
1670: buffr.write(" <mode>" + modeString + "</mode>\n");
1671: buffr.write(" <usenormantounicodemapper>" + useNormanToUnicodeMapper + "</usenormantounicodemapper>\n");
1672:
1673: int index = 0;
1674: while (index < tables.size())
1675: {
1676: String table = (String) tables.get(index);
1677: String layout = (String) layouts.get(index);
1678: String select = (String) selects.get(index);
1679: String create = (String) creates.get(index);
1680: String id = (String) ids.get(index);
1681: IndexList indexList = (IndexList) htIndex.get(table);
1682: if (indexList == null)
1683: indexList = new IndexList();
1684: buffr.write(" <table name = \"" + table + "\" layout = \"" + layout + "\" id = \"" + id + "\" indexList =\"" + indexList + "\">\n");
1685: buffr.write(" <select>" + convertToEntities(select) + "</select>\n");
1686: if (!create.equals(""))
1687: buffr.write(" <create>" + create + " </create>\n");
1688: buffr.write(" </table>\n");
1689: index++;
1690: }
1691: buffr.write(" </database>\n");
1692: }
1693:
1694: public void fillIndexList(String table, String list)
1695: {
1696: IndexList indexList = new IndexList();
1697: StringTokenizer tokenizer = new StringTokenizer(list, ",");
1698: while (tokenizer.hasMoreTokens())
1699: {
1700: indexList.add(tokenizer.nextToken());
1701: }
1702: System.out.println(indexList);
1703:
1704: htIndex.put(table, indexList);
1705: }
1706: public String toString()
1707: {
1708: return bean.url + " " + tables;
1709: }
1710:
1711: }
1712: public static String convertToUTF8(Object command)
1713: {
1714: String str = null;
1715: try
1716: {
1717: str = new String(command.toString().getBytes("UTF-8"));
1718: } catch (UnsupportedEncodingException e)
1719: {
1720: // TODO Auto-generated catch block
1721: e.printStackTrace();
1722: }
1723: return str;
1724: }
1725: public static void writeConfig(String file, DataBase source, DataBase destination) throws Exception
1726: {
1727: if (!file.toLowerCase().endsWith(".xml"))
1728: file += ".xml";
1729: File f = new File(file);
1730:
1731: FileOutputStream fout = new FileOutputStream(f);
1732: OutputStreamWriter outsw = new OutputStreamWriter(fout, "UTF-8");
1733: BufferedWriter buffw = new BufferedWriter(outsw);
1734: buffw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
1735: buffw.newLine();
1736: buffw.write("<convert>\n");
1737: buffw.write(" <source>\n");
1738: source.exportToXML(buffw);
1739: buffw.write(" </source>\n");
1740: buffw.write("\n <destination>\n");
1741: destination.exportToXML(buffw);
1742: buffw.write(" </destination>\n");
1743: buffw.write("</convert>\n");
1744: buffw.close();
1745: }
1746: public static void delete(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode) throws Exception
1747: {
1748: FM2SQL.ProgressDialog dialog = null;
1749: if (FM2SQL.fmInstance != null)
1750: {
1751: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
1752: dialog.setTitle("Conversion running ...");
1753: dialog.title.setText("Getting table data ...");
1754: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
1755: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
1756: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
1757: dialog.thread = Thread.currentThread();
1758: }
1759: // setting user and passwd
1760: bean.setUserAndPasswd(user, passwd);
1761: // setting user and passwd
1762: beanDest.setUserAndPasswd(userDest, passwdDest);
1763: if (dialog != null)
1764: dialog.setSize(400, 250);
1765: StringBuffer command = null;
1766: String query = null;
1767: try
1768: {
1769: //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
1770: //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
1771: bean.setConnection(source);
1772: if (names == null)
1773: names = bean.getTableNames();
1774: // Collections.sort(names);
1775: int tbIndex = 1;
1776:
1777: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
1778: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
1779: {
1780: Vector[] result = null;
1781: java.util.TreeSet myIds = new TreeSet();
1782: java.util.TreeSet myIdsDest = new TreeSet();
1783: int deltaID = 1;
1784: String idField = "";
1785: String destTableName = "";
1786:
1787: try
1788: {
1789: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
1790: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
1791: query = (selects != null) ? selects.get(tbIndex).toString() : query;
1792: //if vectors[1].get(i) != null)
1793: if (layout != "")
1794: {
1795: layout = " layout " + bean.getQC() + layout + bean.getQC();
1796: String name = names.get(tbIndex).toString();
1797: StringBuffer queryLayout = new StringBuffer(query);
1798: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
1799: query = queryLayout.toString();
1800: System.out.println("added layout " + query);
1801:
1802: }
1803: dialog.title.setText("Getting table data ...");
1804: dialog.table.setText(names.get(tbIndex).toString());
1805: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
1806: dialog.show();
1807: bean.getConnection();
1808: bean.makeQuery(query, 50);
1809: idField = ids.get(tbIndex).toString();
1810:
1811: } catch (Exception e)
1812: {
1813: continue;
1814: }
1815: // determine destTableName from createStatement or from source table name
1816: if (!creates.get(tbIndex).equals(""))
1817: {
1818: String create = creates.get(tbIndex).toString().toLowerCase();
1819: int fromIndex = create.indexOf("table") + 5;
1820: int toIndex = create.indexOf("(");
1821: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
1822: System.out.println("destTable " + destTableName);
1823:
1824: } else
1825: destTableName = convertText(names.get(tbIndex).toString());
1826:
1827: // for id kram
1828: Vector vec = null;
1829: Vector vecDest = null;
1830: // tempo
1831: beanDest.setConnection(destination);
1832: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
1833: String tempID = bean.getQC() + idField + bean.getQC();
1834: String tempIDdest = beanDest.getQC() + convertText(idField) + beanDest.getQC();
1835:
1836: int endIndex = -1;
1837: String tempQuery = query;
1838: String destQuery = query.replaceAll(names.get(tbIndex).toString(), destTableName);
1839: String tempQueryDest = destQuery;
1840: // remove extra query parts destQuery.substring(0,destQuery.lastIndexOf(destTableName)+destTableName.length()+1);
1841: System.out.println("new Query " + tempQueryDest);
1842: if (!idField.equals(""))
1843: {
1844: long startTime = System.currentTimeMillis();
1845: int counter = -1;
1846: while (true)
1847: {
1848: ++counter;
1849: if (counter == 0 && dialog != null)
1850: dialog.title.setText("Check if data is available");
1851: else if (dialog != null)
1852: dialog.title.setText("Check if more data is available");
1853: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, numHits);
1854: myIdsDest = beanDest.getIDVector(convertText(idField), destTableName, tempQueryDest, numHits);
1855: if (myIds.isEmpty())
1856: break;
1857: vec = new Vector(myIds);
1858: vecDest = new Vector(myIdsDest);
1859: rowCount = vec.size();
1860: // Deletion will work this way
1861: Vector deleted = new Vector(vec);
1862: Vector linesToDelete = new Vector(vecDest);
1863: // remove all lines that should not be deleted
1864: linesToDelete.removeAll(deleted);
1865: // System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIdsDest.size());
1866: /// @TODO complete delete task remove query show lines to be deleted let user choose if he wants that
1867: System.out.println("number of lines to be deleted " + linesToDelete.size());
1868: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
1869: beanDest.setConnection(destination);
1870:
1871: Statement stm = beanDest.getConnection().createStatement();
1872:
1873: Vector tables = beanDest.getTableNames();
1874: // Collections.sort(tables);
1875: System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames());
1876: tables = beanDest.getTableNames();
1877: // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
1878: stm = beanDest.getConnection().createStatement();
1879:
1880: if (dialog != null)
1881: dialog.title.setText(" Deleting table data ...");
1882:
1883: int j = -1;
1884:
1885: Vector row = null;
1886: command = new StringBuffer();
1887:
1888: command.append("DELETE FROM");
1889: command.append(beanDest.getQC());
1890: command.append(destTableName);
1891: //command.append(convertText((String) names.get(tbIndex)));
1892: command.append(beanDest.getQC());
1893: int size = bean.getColumnNames().size();
1894: command.append("WHERE " + convertText(ids.get(tbIndex).toString()) + " = ?");
1895: PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
1896: System.out.println(command + " " + tbIndex);
1897: //int rowCount = bean.getRowCount(query);
1898: // int idIndex = bean.getColumnNames().indexOf(ids.get(tbIndex));
1899: while (true)
1900: {
1901:
1902: ++j;
1903: if (j == linesToDelete.size())
1904: break;
1905: //print rows
1906: pstm.setString(1, linesToDelete.get(j).toString());
1907: System.out.println(pstm.toString());
1908: pstm.execute();
1909: if (dialog != null)
1910: dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
1911: command = null;
1912: }
1913: // prepare new query for next chunk
1914: if (query.indexOf("where") > 0)
1915: tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
1916: else
1917: tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
1918:
1919: } //to outer while
1920: } // to idfield if
1921: } // table loop
1922:
1923: } catch (Exception e)
1924: {
1925: System.out.println("Error while connecting to database " + e);
1926: if (dialog != null)
1927: {
1928: dialog.setVisible(false);
1929: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1930: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1931: }
1932: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
1933: java.io.PrintStream stream = new java.io.PrintStream(b);
1934: stream.print(command + "\n\n");
1935: e.printStackTrace(stream);
1936: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
1937:
1938: }
1939: if (dialog != null)
1940: {
1941: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1942: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
1943:
1944: dialog.setVisible(false);
1945: }
1946: } // to method
1947:
1948: /**
1949: * synchronize method based on delete method code
1950: *
1951: * @param source
1952: * @param destination
1953: * @param names
1954: * @param layouts
1955: * @param selects
1956: * @param creates
1957: * @param ids
1958: * @param mode
1959: * @throws Exception
1960: */
1961: // TODO implement append,update and delete in one method
1962: // TODO using id based algorithm
1963: public static void synchronize(String source, String destination, Vector names, Vector layouts, Vector selects, Vector creates, Vector ids, int mode, String delimiter, Vector indexList)
1964: throws Exception
1965: {
1966: System.out.println(" bin in synchronize!!!");
1967: FM2SQL.ProgressDialog dialog = null;
1968: if (FM2SQL.fmInstance != null)
1969: {
1970: dialog = new FM2SQL.ProgressDialog(FM2SQL.fmInstance, bean);
1971: dialog.setTitle("Synchronize running ...");
1972: dialog.title.setText("Getting table data ...");
1973: dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x + (FM2SQL.fmInstance.getWidth() - 400) / 2, FM2SQL.fmInstance.getLocationOnScreen().y + (FM2SQL.fmInstance.getHeight() - 250) / 2);
1974: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
1975: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
1976: dialog.thread = Thread.currentThread();
1977: }
1978: // setting user and passwd
1979: bean.setUserAndPasswd(user, passwd);
1980: // setting user and passwd
1981: beanDest.setUserAndPasswd(userDest, passwdDest);
1982: if (dialog != null)
1983: dialog.setSize(400, 250);
1984: StringBuffer command = null;
1985: String query = null;
1986: try
1987: {
1988: //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");
1989: //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
1990: bean.setConnection(source);
1991: if (names == null)
1992: names = bean.getTableNames();
1993: // Collections.sort(names);
1994: int tbIndex = 1;
1995:
1996: // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
1997: for (tbIndex = 0; tbIndex < names.size(); ++tbIndex)
1998: {
1999: Vector[] result = null;
2000: java.util.TreeSet myIds = new TreeSet();
2001: java.util.TreeSet myIdsDest = new TreeSet();
2002: int deltaID = 1;
2003: String idField = "";
2004: String destTableName = "";
2005:
2006: try
2007: {
2008: query = "select * from " + bean.getQC() + names.get(tbIndex).toString() + bean.getQC();
2009: String layout = (layouts.isEmpty()) ? "" : layouts.get(tbIndex).toString();
2010: query = (selects != null) ? selects.get(tbIndex).toString() : query;
2011: //if vectors[1].get(i) != null)
2012: if (!layout.equals(""))
2013: {
2014: layout = " layout " + bean.getQC() + layout + bean.getQC();
2015: String name = names.get(tbIndex).toString();
2016: StringBuffer queryLayout = new StringBuffer(query);
2017: queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
2018: query = queryLayout.toString();
2019: System.out.println("added layout " + query);
2020:
2021: }
2022: if (dialog != null)
2023: {
2024: dialog.title.setText("Getting table data ...");
2025: dialog.table.setText(names.get(tbIndex).toString());
2026: dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
2027: dialog.show();
2028: }
2029: bean.getConnection();
2030: bean.makeQuery(query, 50);
2031: idField = ids.get(tbIndex).toString();
2032:
2033: } catch (Exception e)
2034: {
2035: continue;
2036: }
2037: // determine destTableName from createStatement or from source table name
2038: if (!creates.get(tbIndex).equals(""))
2039: {
2040: String create = creates.get(tbIndex).toString().toLowerCase();
2041: int fromIndex = create.indexOf("table") + 5;
2042: int toIndex = create.indexOf("(");
2043: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
2044: System.out.println("destTable " + destTableName);
2045:
2046: } else
2047: destTableName = convertText(names.get(tbIndex).toString());
2048:
2049: // for id kram
2050: Vector vec = null;
2051: Vector vecDest = null;
2052: // tempo
2053: beanDest.setConnection(destination);
2054: int rowCount = (idField != "") ? myIds.size() : bean.getRowCount(query);
2055: String tempID = bean.getQC() + idField + bean.getQC();
2056: String tempIDdest = beanDest.getQC() + convertText(idField) + beanDest.getQC();
2057:
2058: int endIndex = -1;
2059: String tempQuery = query;
2060: String destQuery = query.replaceAll(names.get(tbIndex).toString(), destTableName);
2061: String tempQueryDest = destQuery;
2062: // remove extra query parts destQuery.substring(0,destQuery.lastIndexOf(destTableName)+destTableName.length()+1);
2063: System.out.println("new Query " + tempQueryDest);
2064: if (!idField.equals(""))
2065: {
2066: long startTime = System.currentTimeMillis();
2067: int counter = -1;
2068: TreeSet linesToDelete = null;
2069: PreparedStatement delPSt = null;
2070: while (true)
2071: {
2072: ++counter;
2073: if (counter == 0 && dialog != null)
2074: dialog.title.setText("Check if data is available");
2075: else if (dialog != null)
2076: dialog.title.setText("Check if more data is available");
2077: myIds = bean.getIDVector(ids.get(tbIndex).toString(), (String) names.get(tbIndex), tempQuery, 0);
2078: myIdsDest = beanDest.getIDVector(convertText(idField), destTableName, tempQueryDest, 0);
2079: //System.out.println("status of remove "+myIds.remove("b015892"));
2080: if (myIds.isEmpty())
2081: break;
2082: vec = new Vector(myIds);
2083: vecDest = new Vector(myIdsDest);
2084: rowCount = vec.size();
2085: // Deletion will work this way
2086: Vector deleted = new Vector(vec);
2087:
2088: TreeSet linesToAppend = new TreeSet(vec);
2089: linesToAppend.addAll(vec);
2090: linesToDelete = new TreeSet(vecDest);
2091: // remove all lines that are already in dest database
2092: linesToAppend.removeAll(vecDest);
2093: // remove all lines that should not be deleted
2094: linesToDelete.removeAll(deleted);
2095: System.out.println("linesToAppend " + linesToAppend + " " + destTableName);
2096: System.out.println("linesToDelete " + linesToDelete + " " + destTableName);
2097: System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIds.size());
2098: deltaID = (int) Math.round((double) myIds.size() / (double) numIntervalls);
2099: ConversionProperties prop = getFieldNamesAndDestTableName(creates.get(tbIndex).toString(), query, names.get(tbIndex).toString());
2100: StringBuffer insCommand = createInsertCommand(prop.destTableName, prop.fieldNames);
2101: StringBuffer updCommand = createUpdateCommand(prop.destTableName, prop.fieldNames, tempIDdest);
2102: StringBuffer delCommand = createDeleteCommand(destTableName, tempIDdest);
2103: PreparedStatement insPst = beanDest.getConnection().prepareStatement(insCommand.toString());
2104: PreparedStatement updPst = beanDest.getConnection().prepareStatement(updCommand.toString());
2105: delPSt = beanDest.getConnection().prepareStatement(delCommand.toString());
2106: // delPSt.setString(1,"b015892");
2107: // delPSt.execute();
2108: // if (true)
2109: // return;
2110: if (vec.size() <= numIntervalls)
2111: {
2112: endIndex = 0;
2113: deltaID = vec.size();
2114: }
2115: for (int k = 0; k < vec.size() - deltaID; k = k + deltaID)
2116: {
2117: System.out.println(vec.get(k) + " " + vec.get(k + deltaID) + " " + vec.lastElement());
2118: if (query.indexOf("where") > 0)
2119: tempQuery = query + " and " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID) + "'";
2120: else
2121: tempQuery = query + " where " + tempID + ">='" + vec.get(k) + "' and " + tempID + "<='" + vec.get(k + deltaID) + "'";
2122: System.out.println(tempQuery);
2123: if (dialog != null)
2124: dialog.title.setText("Reading table data ...");
2125:
2126: // bean.makeQuery(tempQuery, deltaID);
2127: if (dialog != null)
2128: dialog.title.setText("Writing table data ...");
2129:
2130: performSynchronize(idField, vec, tempQuery, linesToDelete, linesToAppend, insPst, updPst, delPSt, deltaID, delimiter);
2131: // System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIdsDest.size());
2132: endIndex = k + deltaID;
2133: }
2134: System.out.println(endIndex);
2135: //all data written ? if not write last chunk of data
2136: if (endIndex == vec.size() - 1)
2137: System.out.println("fits");
2138: else
2139: {
2140: System.out.println(" last intervall from " + vec.get(endIndex) + " " + vec.lastElement());
2141:
2142: if (query.indexOf("where") > 0)
2143: tempQuery = query + " and " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement() + "'";
2144: else
2145: tempQuery = query + " where " + tempID + ">='" + vec.get(endIndex) + "' and " + tempID + "<='" + vec.lastElement() + "'";
2146: System.out.println(tempQuery);
2147: if (dialog != null)
2148: dialog.title.setText("Reading table data ...");
2149: // bean.makeQuery(tempQuery, 0);
2150: if (dialog != null)
2151: dialog.title.setText("Writing table data ...");
2152: performSynchronize(idField, vec, tempQuery, linesToDelete, linesToAppend, insPst, updPst, delPSt, deltaID, delimiter);
2153: // System.out.println("ID LIST SIZE " + Math.round((double) myIds.size() / (double) numIntervalls) + " " + myIdsDest.size());
2154: }
2155: // prepare new query for next chunk
2156: if (query.indexOf("where") > 0)
2157: tempQuery = query + " and " + tempID + ">'" + vec.lastElement() + "'";
2158: else
2159: tempQuery = query + " where " + tempID + ">'" + vec.lastElement() + "'";
2160:
2161: }
2162: String tableName = names.get(tbIndex).toString();
2163: IndexList idList = (IndexList) indexList.get(0);
2164: System.out.println("found list " + idList);
2165: Statement stm = beanDest.getConnection().createStatement();
2166: Vector destTables = beanDest.getTableNames();
2167: beanDest.makeQuery(tempQueryDest,0);
2168: for (Iterator iter = idList.iterator(); iter.hasNext();)
2169: {
2170: String indexField = (String) iter.next();
2171: indexField = convertText(indexField);
2172: String indexName = destTableName + "_" + indexField;
2173: if (destTables.contains(indexName))
2174: {
2175: stm.execute("DROP INDEX "+destTableName+"_"+indexField);
2176: // continue;
2177: }
2178:
2179: String type = beanDest.getColumnType(indexField).toLowerCase();
2180: // System.out.println(indexField+" "+type+" "+(type.indexOf("text") >= 0 || type.indexOf("varchar") >= 0 || type.indexOf("char") >= 0));
2181: if (type.indexOf("text") >= 0 || type.indexOf("varchar") >= 0 || type.indexOf("char") >= 0)
2182: stm.execute("CREATE INDEX " + indexName + " ON " + destTableName + "(LOWER(" + indexField + "))");
2183: else
2184: stm.execute("CREATE INDEX " + destTableName + "_" + indexField + " ON " + destTableName + "(" + indexField + ")");
2185:
2186: // stm.execute("DROP INDEX "+destTableName+"_"+indexField);
2187:
2188: }
2189: // CREATE UNIQUE INDEX title_idx ON films (title);
2190: for (Iterator iter = linesToDelete.iterator(); iter.hasNext();)
2191: {
2192: String id = (String) iter.next();
2193: delPSt.setString(1, id);
2194: delPSt.execute();
2195:
2196: }
2197:
2198: long endTime = System.currentTimeMillis();
2199: System.out.println("Time for incremental synchronize elapsed " + (endTime - startTime));
2200: } // to idfield if
2201: } // table loop
2202:
2203: } catch (Exception e)
2204: {
2205: System.out.println("Error while connecting to database " + e);
2206: e.printStackTrace();
2207: if (dialog != null)
2208: {
2209: dialog.setVisible(false);
2210: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
2211: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
2212: }
2213: java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
2214: java.io.PrintStream stream = new java.io.PrintStream(b);
2215: stream.print(command + "\n\n");
2216: e.printStackTrace(stream);
2217: FM2SQL.showErrorDialog(b.toString(), "Error occured !");
2218:
2219: }
2220: if (dialog != null)
2221: {
2222: dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
2223: FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
2224:
2225: dialog.setVisible(false);
2226: }
2227: }
2228: private static void performSynchronize(
2229: String idField,
2230: Vector vec,
2231: String tempQuery,
2232: TreeSet linesToDelete,
2233: TreeSet linesToAppend,
2234: PreparedStatement insPst,
2235: PreparedStatement updPst,
2236: PreparedStatement delPSt,
2237: int deltaID,
2238: String delimiter)
2239: throws SQLException, ParseException
2240: {
2241: Vector[] vectors = bean.getQueryData(tempQuery, deltaID);
2242: int idIndex = vectors[1].indexOf(idField);
2243: // todo arraylist code has to be added
2244: for (Iterator iter = vectors[0].iterator(); iter.hasNext();)
2245: {
2246: Vector line = (Vector) iter.next();
2247: Object lineIDIndex = line.get(idIndex);
2248: if (linesToAppend.contains(lineIDIndex))
2249: System.out.println("line " + linesToAppend.contains(line.get(idIndex)) + " " + lineIDIndex);
2250: if (linesToAppend.contains(lineIDIndex))
2251: {
2252: for (int l = 0; l < line.size(); ++l)
2253: {
2254: Object obj = line.get(l);
2255: if (obj instanceof ArrayList)
2256: obj = formatFileMakerArray((List) obj, delimiter);
2257: if (obj != null)
2258: insPst.setString(l + 1, obj.toString());
2259: else
2260: insPst.setNull(l + 1, Types.NULL);
2261: }
2262: insPst.execute();
2263:
2264: } else // update
2265: {
2266: for (int l = 0; l < line.size(); ++l)
2267: {
2268: Object obj = line.get(l);
2269: if (obj instanceof ArrayList)
2270: obj = formatFileMakerArray((List) obj, delimiter);
2271: if (obj != null)
2272: updPst.setString(l + 1, obj.toString());
2273: else
2274: updPst.setNull(l + 1, Types.NULL);
2275: }
2276: updPst.setString(line.size() + 1, line.get(idIndex).toString());
2277: //updPst.addBatch();
2278: updPst.execute();
2279: }
2280: }
2281: //updPst.executeBatch();
2282: } // to method
2283:
2284: /**
2285: * Converts input String in norman encoding to unicode
2286: * @param inp
2287: * @return converted String
2288: */
2289: static public String normanToUnicode(String inp)
2290: {
2291: StringBuffer buf = new StringBuffer();
2292: for (int i = 0; i < inp.length(); i++)
2293: {
2294: char c = inp.charAt(i);
2295: // System.out.println("char "+c+" "+(int)c);
2296: switch (c)
2297: {
2298: case 1 :
2299: buf.append("\u00d0");
2300: break; // Eth
2301: case 2 :
2302: buf.append("\u00f0");
2303: break; // eth
2304: case 3 :
2305: buf.append("\u0141");
2306: break; // Lslash
2307: case 4 :
2308: buf.append("\u0142");
2309: break; // lslash
2310: case 5 :
2311: buf.append("\u0160");
2312: break; // S caron
2313: case 6 :
2314: buf.append("\u0161");
2315: break; // s caron
2316: case 7 :
2317: buf.append("\u00dd");
2318: break; // Y acute
2319: case 8 :
2320: buf.append("\u00fd");
2321: break; // y acute
2322: case 11 :
2323: buf.append("\u00de");
2324: break; // Thorn
2325: case 12 :
2326: buf.append("\u00fe");
2327: break; // thorn
2328: case 14 :
2329: buf.append("\u017d");
2330: break; // Z caron
2331: case 15 :
2332: buf.append("\u017e");
2333: break; // z caron
2334: case 17 :
2335: buf.append("\u0073");
2336: break; // asciitilde
2337: case 18 :
2338: buf.append("j\u0305");
2339: break; // j macron [does a single char exist?]
2340: case 19 :
2341: buf.append("^");
2342: break; // circumflex
2343: case 20 :
2344: buf.append("\u0303");
2345: break; // tilde
2346: case 21 :
2347: buf.append("\u00bd");
2348: break; // onehalf
2349: case 22 :
2350: buf.append("\u00bc");
2351: break; // onequarter
2352: case 23 :
2353: buf.append("\u00b9");
2354: break; // onesuperior
2355: case 24 :
2356: buf.append("\u00be");
2357: break; // threequarters
2358: case 25 :
2359: buf.append("\u00b3");
2360: break; // threesuperior
2361: case 26 :
2362: buf.append("\u00b2");
2363: break; // twosuperior
2364: case 27 :
2365: buf.append("\u00a6");
2366: break; // brokenbar
2367: case 28 :
2368: buf.append("-");
2369: break; // minus
2370: case 29 :
2371: buf.append("\u00d7");
2372: break; // multiply
2373: case 39 :
2374: buf.append("'");
2375: break; // quotesingle
2376: case 94 :
2377: buf.append("\u0302");
2378: break; // circumflex
2379: case 96 :
2380: buf.append("\u0300");
2381: break; // grave
2382: case 196 :
2383: buf.append("\u00c4");
2384: break; // A dieresis
2385: case 197 :
2386: buf.append("\u00c5");
2387: break; // A ring
2388: case 201 :
2389: buf.append("\u00c9");
2390: break; // E acute
2391: case 209 :
2392: buf.append("\u00d1");
2393: break; // N tilde
2394: case 214 :
2395: buf.append("\u00d6");
2396: break; // O dieresis
2397: case 220 :
2398: buf.append("\u00dc");
2399: break; // U dieresis
2400: case 225 :
2401: buf.append("\u00e1");
2402: break; // a acute
2403: case 224 :
2404: buf.append("\u00e0");
2405: break; // a grave
2406: case 226 :
2407: buf.append("\u00e2");
2408: break; // a circumflex
2409: case 228 :
2410: buf.append("\u00e4");
2411: break; // a dieresis
2412: case 227 :
2413: buf.append("\u00e3");
2414: break; // a tilde
2415: case 229 :
2416: buf.append("\u0101");
2417: break; // a macron
2418: case 231 :
2419: buf.append("\u00e7");
2420: break; // c cedilla
2421: case 233 :
2422: buf.append("\u00e9");
2423: break; // e acute
2424: case 232 :
2425: buf.append("\u00e8");
2426: break; // e grave
2427: case 234 :
2428: buf.append("\u00ea");
2429: break; // e circumflex
2430: case 235 :
2431: buf.append("\u00eb");
2432: break; // e dieresis
2433: case 237 :
2434: buf.append("\u00ed");
2435: break; // i acute
2436: case 236 :
2437: buf.append("\u00ec");
2438: break; // i grave
2439: case 238 :
2440: buf.append("\u00ee");
2441: break; // i circumflex
2442: case 239 :
2443: buf.append("\u00ef");
2444: break; // i dieresis
2445: case 241 :
2446: buf.append("\u00f1");
2447: break; // n tilde
2448: case 243 :
2449: buf.append("\u00f3");
2450: break; // o acute
2451: case 242 :
2452: buf.append("\u00f2");
2453: break; // o grave
2454: case 244 :
2455: buf.append("\u00f4");
2456: break; // o circumflex
2457: case 246 :
2458: buf.append("\u00f6");
2459: break; // o dieresis
2460: case 245 :
2461: buf.append("\u00f5");
2462: break; // o tilde
2463: case 250 :
2464: buf.append("\u00fa");
2465: break; // u acute
2466: case 249 :
2467: buf.append("\u00f9");
2468: break; // u grave
2469: case 251 :
2470: buf.append("\u00fb");
2471: break; // u circumflex
2472: case 252 :
2473: buf.append("\u00fc");
2474: break; // u dieresis
2475: case 8224 :
2476: buf.append("\u1e6d");
2477: break; // t underdot
2478: case 176 :
2479: buf.append("\u00b0");
2480: break; // degree
2481: case 162 :
2482: buf.append("\u1ebd");
2483: break; // e tilde
2484: case 163 :
2485: buf.append("\u00a3");
2486: break; // sterling
2487: case 167 :
2488: buf.append("\u00a7");
2489: break; // section
2490: case 182 :
2491: buf.append("\u00b6");
2492: break; // paragraph
2493: case 223 :
2494: buf.append("\u015b");
2495: break; // s acute
2496: case 174 :
2497: buf.append("\u1e5b");
2498: break; // r underdot
2499: case 169 :
2500: buf.append("\u1e45");
2501: break; // n overdot
2502: case 353 :
2503: buf.append("\u1e45");
2504: break; // n overdot
2505: case 180 :
2506: buf.append("\u0301");
2507: break; // acute
2508: case 168 :
2509: buf.append("\u0308");
2510: break; // dieresis
2511: case 8800 :
2512: buf.append("\u1e6d");
2513: break; // t underdot
2514: case 198 :
2515: buf.append("\u00c6");
2516: break; // AE
2517: case 216 :
2518: buf.append("\u014d");
2519: break; // o macron
2520: case 8734 :
2521: buf.append("\u0129");
2522: break; // i tilde
2523: case 177 :
2524: buf.append("\u00b1");
2525: break; // plusminus
2526: case 165 :
2527: buf.append("\u012b");
2528: break; // i macron
2529: case 181 :
2530: buf.append("\u1e43");
2531: break; // m underdot
2532: case 8706 :
2533: buf.append("\u1e0d");
2534: break; // d underdot
2535: case 240 :
2536: buf.append("\u1e0d");
2537: break; // d underdot
2538:
2539: case 8721 :
2540: buf.append("\u1e63");
2541: break; // s underdot
2542: case 960 :
2543: buf.append("\u017a");
2544: break; // z acute
2545: case 8747 :
2546: buf.append("\u1e45");
2547: break; // n overdot
2548: case 937 :
2549: buf.append("\u0169");
2550: break; // u tilde
2551: case 230 :
2552: buf.append("\u00e6");
2553: break; // ae
2554: case 248 :
2555: buf.append("\u00f8");
2556: break; // oslash
2557: case 191 :
2558: buf.append("\u0304\u0306");
2559: break; // macron breve
2560: case 172 :
2561: buf.append("\u1e37");
2562: break; //
2563: case 8730 :
2564: buf.append("j\u0305");
2565: break; // j macron [does a single char exist?]
2566: case 402 :
2567: buf.append("\u0103");
2568: break; // a breve
2569: case 8776 :
2570: buf.append("\u016d");
2571: break; // u breve
2572: case 187 :
2573: buf.append("\u1e42");
2574: break; // M underdot
2575: case 8230 :
2576: buf.append("\u2026");
2577: break; // ellipsis
2578: case 192 :
2579: buf.append("\u00c0");
2580: break; // A grave
2581: case 195 :
2582: buf.append("\u00c3");
2583: break; // A tilde
2584: case 213 :
2585: buf.append("\u00d5");
2586: break; // O tilde
2587: case 338 :
2588: buf.append("m\u0306");
2589: break; // m breve
2590: case 339 :
2591: buf.append("\u0153");
2592: break; // oe
2593: case 8211 :
2594: buf.append("\u2013");
2595: break; // endash
2596: case 8212 :
2597: buf.append("\u2014");
2598: break; // emdash
2599: case 8220 :
2600: buf.append("\u201c");
2601: break; // quotedblleft
2602: case 8221 :
2603: buf.append("\u201d");
2604: break; // quotedblright
2605: case 8216 :
2606: buf.append("\u2018");
2607: break; // quoteleft
2608: case 8217 :
2609: buf.append("\u2019");
2610: break; // quoteright
2611: case 247 :
2612: buf.append("\u1e37");
2613: break; // l underring [actually underdot]
2614: case 9674 :
2615: buf.append("\u1e41");
2616: break; // m overdot
2617: case 255 :
2618: buf.append("n\u0306");
2619: break; // n breve
2620: case 376 :
2621: buf.append("\u00d7");
2622: break; // multiply
2623: case 8364 :
2624: buf.append("\u1e5b");
2625: break; // r underring [actually underdot]
2626: case 8249 :
2627: buf.append("\u1e44");
2628: break; // N overdot
2629: case 8250 :
2630: buf.append("\u1e62");
2631: break; // S underdot
2632: case 64257 :
2633: buf.append("\u1e24");
2634: break; // H underdot
2635: case 64258 :
2636: buf.append("\u1e0c");
2637: break; // D underdot
2638: case 8225 :
2639: buf.append("\u2021");
2640: break; // daggerdbl
2641: case 8218 :
2642: buf.append("\u1e36");
2643: break; // L underdot
2644: case 8222 :
2645: buf.append("\u0113");
2646: break; // e macron
2647: case 194 :
2648: buf.append("\u1e5f");
2649: break; // r underbar
2650: case 202 :
2651: buf.append("r\u0324");
2652: break; // r underdieresis
2653: case 193 :
2654: buf.append("\u012a");
2655: break; // I macron
2656: case 8486 :
2657: case 203 :
2658: buf.append("\u016b");
2659: break; // u macron
2660: case 200 :
2661: buf.append("\u1e6c");
2662: break; // T underdot
2663: case 205 :
2664: buf.append("\u1e64");
2665: break; // S acute
2666: case 206 :
2667: buf.append("\u2020");
2668: break; // dagger
2669: case 207 :
2670: buf.append("\u0115");
2671: break; // e breve
2672: case 204 :
2673: buf.append("\u014f");
2674: break; // o breve
2675: case 211 :
2676: buf.append("\u0100");
2677: break; // A macron
2678: case 212 :
2679: buf.append("\u1e46");
2680: break; // N underdot
2681: case 210 :
2682: buf.append("\u1e3b");
2683: break; // l underbar
2684: case 218 :
2685: buf.append("\u016a");
2686: break; // U macron
2687: case 219 :
2688: buf.append("\u0179");
2689: break; // Z acute
2690: case 217 :
2691: buf.append("\u1e5a");
2692: break; // R underdot
2693: case 305 :
2694: buf.append("\u0131");
2695: break; // dotlessi
2696: case 710 :
2697: buf.append("\u1e47");
2698: break; // n underdot
2699: case 732 :
2700: buf.append("\u1e49");
2701: break; // n underbar
2702: case 175 :
2703: buf.append("\u0304");
2704: break; // macron
2705: case 728 :
2706: buf.append("\u0306");
2707: break; // breve
2708: case 729 :
2709: case 215 :
2710: buf.append("\u1e25");
2711: break; // h underdot
2712: case 730 :
2713: buf.append("\u012d");
2714: break; // i breve
2715: case 184 :
2716: buf.append("\u0327");
2717: break; // cedilla
2718: case 733 :
2719: buf.append("\u030b");
2720: break; // hungarumlaut
2721: case 731 :
2722: buf.append("\u0328");
2723: break; // ogonek
2724: case 711 :
2725: buf.append("\u030c");
2726: break; // caron
2727: case 199 :
2728: buf.append("\u012b\u0303");
2729: break; // imacron tilde
2730: case 8226 :
2731: buf.append("\u1e5d");
2732: break; // runderdot macron
2733: case 8482 :
2734: buf.append("\u016b\0306");
2735: break; // umacron breve
2736: case 8804 :
2737: buf.append("\u0101\u0301");
2738: break; // amacron acute
2739: case 8805 :
2740: buf.append("\u016b\u0301");
2741: break; // umacron acute
2742: case 8719 :
2743: buf.append("\u0113\u0301");
2744: break; // emacron acute
2745: case 170 :
2746: buf.append("\u0113\u0300");
2747: break; // emacron breve
2748: case 186 :
2749: buf.append("\u014d\u0300");
2750: break; // omacron breve
2751: case 161 :
2752: buf.append("\u0101\u0306");
2753: break; // amacron breve
2754: case 8710 :
2755: buf.append("\u0101\u0303");
2756: break; // amacron tilde
2757: case 171 :
2758: buf.append("\u012b\u0301");
2759: break; // imacron acute
2760: case 8260 :
2761: buf.append("\u1e00");
2762: break; // runderdotmacron acute
2763: case 183 :
2764: buf.append("\u1e5b\u0301");
2765: break; // runderdot acute
2766: case 8240 :
2767: buf.append("\u012b\u0306");
2768: break; // imacron breve
2769: case 63743 :
2770: buf.append("\u016b\u0303");
2771: break; // umacron tilde
2772: default :
2773: buf.append(c);
2774: if ((int) c > 127)
2775: System.out.println("char " + c + " " + (int) c);
2776: break;
2777: }
2778: }
2779: return buf.toString();
2780: }
2781:
2782: static public String normanToUnicodeOld(String inp)
2783: {
2784: StringBuffer buf = new StringBuffer();
2785: for (int i = 0; i < inp.length(); i++)
2786: {
2787: char c = inp.charAt(i);
2788: switch (c)
2789: {
2790: case 1 :
2791: buf.append("\u00d0");
2792: break; // Eth
2793: case 2 :
2794: buf.append("\u00f0");
2795: break; // eth
2796: case 3 :
2797: buf.append("\u0141");
2798: break; // Lslash
2799: case 4 :
2800: buf.append("\u0142");
2801: break; // lslash
2802: case 5 :
2803: buf.append("\u0160");
2804: break; // S caron
2805: case 6 :
2806: buf.append("\u0161");
2807: break; // s caron
2808: case 7 :
2809: buf.append("\u00dd");
2810: break; // Y acute
2811: case 8 :
2812: buf.append("\u00fd");
2813: break; // y acute
2814: case 11 :
2815: buf.append("\u00de");
2816: break; // Thorn
2817: case 12 :
2818: buf.append("\u00fe");
2819: break; // thorn
2820: case 14 :
2821: buf.append("\u017d");
2822: break; // Z caron
2823: case 15 :
2824: buf.append("\u017e");
2825: break; // z caron
2826: case 17 :
2827: buf.append("\u0073");
2828: break; // asciitilde
2829: case 18 :
2830: buf.append("j\u0305");
2831: break; // j macron [does a single char exist?]
2832: case 19 :
2833: buf.append("^");
2834: break; // circumflex
2835: case 20 :
2836: buf.append("\u0303");
2837: break; // tilde
2838: case 21 :
2839: buf.append("\u00bd");
2840: break; // onehalf
2841: case 22 :
2842: buf.append("\u00bc");
2843: break; // onequarter
2844: case 23 :
2845: buf.append("\u00b9");
2846: break; // onesuperior
2847: case 24 :
2848: buf.append("\u00be");
2849: break; // threequarters
2850: case 25 :
2851: buf.append("\u00b3");
2852: break; // threesuperior
2853: case 26 :
2854: buf.append("\u00b2");
2855: break; // twosuperior
2856: case 27 :
2857: buf.append("\u00a6");
2858: break; // brokenbar
2859: case 28 :
2860: buf.append("-");
2861: break; // minus
2862: case 29 :
2863: buf.append("\u00d7");
2864: break; // multiply
2865: case 39 :
2866: buf.append("'");
2867: break; // quotesingle
2868: case 94 :
2869: buf.append("\u0302");
2870: break; // circumflex
2871: case 96 :
2872: buf.append("\u0300");
2873: break; // grave
2874: case 128 :
2875: buf.append("\u00c4");
2876: break; // A dieresis
2877: case 129 :
2878: buf.append("\u00c5");
2879: break; // A ring
2880: case 131 :
2881: buf.append("\u00c9");
2882: break; // E acute
2883: case 132 :
2884: buf.append("\u00d1");
2885: break; // N tilde
2886: case 133 :
2887: buf.append("\u00d6");
2888: break; // O dieresis
2889: case 134 :
2890: buf.append("\u00dc");
2891: break; // U dieresis
2892: case 135 :
2893: buf.append("\u00e1");
2894: break; // a acute
2895: case 136 :
2896: buf.append("\u00e0");
2897: break; // a grave
2898: case 137 :
2899: buf.append("\u00e2");
2900: break; // a circumflex
2901: case 138 :
2902: buf.append("\u00e4");
2903: break; // a dieresis
2904: case 139 :
2905: buf.append("\u00e3");
2906: break; // a tilde
2907: case 140 :
2908: buf.append("\u0101");
2909: break; // a macron
2910: case 141 :
2911: buf.append("\u00e7");
2912: break; // c cedilla
2913: case 142 :
2914: buf.append("\u00e9");
2915: break; // e acute
2916: case 143 :
2917: buf.append("\u00e8");
2918: break; // e grave
2919: case 144 :
2920: buf.append("\u00ea");
2921: break; // e circumflex
2922: case 145 :
2923: buf.append("\u00eb");
2924: break; // e dieresis
2925: case 146 :
2926: buf.append("\u00ed");
2927: break; // i acute
2928: case 147 :
2929: buf.append("\u00ec");
2930: break; // i grave
2931: case 148 :
2932: buf.append("\u00ee");
2933: break; // i circumflex
2934: case 149 :
2935: buf.append("\u00ef");
2936: break; // i dieresis
2937: case 150 :
2938: buf.append("\u00f1");
2939: break; // n tilde
2940: case 151 :
2941: buf.append("\u00f3");
2942: break; // o acute
2943: case 152 :
2944: buf.append("\u00f2");
2945: break; // o grave
2946: case 153 :
2947: buf.append("\u00f4");
2948: break; // o circumflex
2949: case 154 :
2950: buf.append("\u00f6");
2951: break; // o dieresis
2952: case 155 :
2953: buf.append("\u00f5");
2954: break; // o tilde
2955: case 156 :
2956: buf.append("\u00fa");
2957: break; // u acute
2958: case 157 :
2959: buf.append("\u00f9");
2960: break; // u grave
2961: case 158 :
2962: buf.append("\u00fb");
2963: break; // u circumflex
2964: case 159 :
2965: buf.append("\u00fc");
2966: break; // u dieresis
2967: case 160 :
2968: buf.append("\u1e6d");
2969: break; // t underdot
2970: case 161 :
2971: buf.append("\u00b0");
2972: break; // degree
2973: case 162 :
2974: buf.append("\u1ebd");
2975: break; // e tilde
2976: case 163 :
2977: buf.append("\u00a3");
2978: break; // sterling
2979: case 164 :
2980: buf.append("\u00a7");
2981: break; // section
2982: case 166 :
2983: buf.append("\u00b6");
2984: break; // paragraph
2985: case 167 :
2986: buf.append("\u015b");
2987: break; // s acute
2988: case 168 :
2989: buf.append("\u1e5b");
2990: break; // r underdot
2991: case 169 :
2992: buf.append("\u1e67");
2993: break; // s caron
2994: case 171 :
2995: buf.append("\u0301");
2996: break; // acute
2997: case 172 :
2998: buf.append("\u0308");
2999: break; // dieresis
3000: case 173 :
3001: buf.append("\u1e6d");
3002: break; // t underdot
3003: case 174 :
3004: buf.append("\u00c6");
3005: break; // AE
3006: case 175 :
3007: buf.append("\u014d");
3008: break; // o macron
3009: case 176 :
3010: buf.append("\u0129");
3011: break; // i tilde
3012: case 177 :
3013: buf.append("\u00b1");
3014: break; // plusminus
3015: case 180 :
3016: buf.append("\u012b");
3017: break; // i macron
3018: case 181 :
3019: buf.append("\u1e43");
3020: break; // m underdot
3021: case 182 :
3022: buf.append("\u1e0d");
3023: break; // d underdot
3024: case 183 :
3025: buf.append("\u1e63");
3026: break; // s underdot
3027: case 185 :
3028: buf.append("\u017a");
3029: break; // z acute
3030: case 186 :
3031: buf.append("\u1e45");
3032: break; // n overdot
3033: case 189 :
3034: buf.append("\u0169");
3035: break; // u tilde
3036: case 190 :
3037: buf.append("\u00e6");
3038: break; // ae
3039: case 191 :
3040: buf.append("\u00f8");
3041: break; // oslash
3042: case 192 :
3043: buf.append("\u0304\u0306");
3044: break; // macron breve
3045: case 194 :
3046: buf.append("\u1e37");
3047: break; //
3048: case 195 :
3049: buf.append("j\u0305");
3050: break; // j macron [does a single char exist?]
3051: case 196 :
3052: buf.append("\u0103");
3053: break; // a breve
3054: case 197 :
3055: buf.append("\u016d");
3056: break; // u breve
3057: case 200 :
3058: buf.append("\u1e42");
3059: break; // M underdot
3060: case 201 :
3061: buf.append("\u2026");
3062: break; // ellipsis
3063: case 203 :
3064: buf.append("\u00c0");
3065: break; // A grave
3066: case 204 :
3067: buf.append("\u00c3");
3068: break; // A tilde
3069: case 205 :
3070: buf.append("\u00d5");
3071: break; // O tilde
3072: case 206 :
3073: buf.append("m\u0306");
3074: break; // m breve
3075: case 207 :
3076: buf.append("\u0153");
3077: break; // oe
3078: case 208 :
3079: buf.append("\u2013");
3080: break; // endash
3081: case 209 :
3082: buf.append("\u2014");
3083: break; // emdash
3084: case 210 :
3085: buf.append("\u201c");
3086: break; // quotedblleft
3087: case 211 :
3088: buf.append("\u201d");
3089: break; // quotedblright
3090: case 212 :
3091: buf.append("\u2018");
3092: break; // quoteleft
3093: case 213 :
3094: buf.append("\u2019");
3095: break; // quoteright
3096: case 214 :
3097: buf.append("\u1e37");
3098: break; // l underring [actually underdot]
3099: case 215 :
3100: buf.append("\u1e41");
3101: break; // m overdot
3102: case 216 :
3103: buf.append("n\u0306");
3104: break; // n breve
3105: case 217 :
3106: buf.append("\u00d7");
3107: break; // multiply
3108: case 219 :
3109: buf.append("\u1e5b");
3110: break; // r underring [actually underdot]
3111: case 220 :
3112: buf.append("\u1e44");
3113: break; // N overdot
3114: case 221 :
3115: buf.append("\u1e62");
3116: break; // S underdot
3117: case 222 :
3118: buf.append("\u1e24");
3119: break; // H underdot
3120: case 223 :
3121: buf.append("\u1e0c");
3122: break; // D underdot
3123: case 224 :
3124: buf.append("\u2021");
3125: break; // daggerdbl
3126: case 226 :
3127: buf.append("\u1e36");
3128: break; // L underdot
3129: case 227 :
3130: buf.append("\u0113");
3131: break; // e macron
3132: case 229 :
3133: buf.append("\u1e5f");
3134: break; // r underbar
3135: case 230 :
3136: buf.append("r\u0324");
3137: break; // r underdieresis
3138: case 231 :
3139: buf.append("\u012a");
3140: break; // I macron
3141: case 232 :
3142: buf.append("\u016b");
3143: break; // u macron
3144: case 233 :
3145: buf.append("\u01e6c");
3146: break; // T underdot
3147: case 234 :
3148: buf.append("\u1e64");
3149: break; // S acute
3150: case 235 :
3151: buf.append("\u2020");
3152: break; // dagger
3153: case 236 :
3154: buf.append("\u0115");
3155: break; // e breve
3156: case 237 :
3157: buf.append("\u014f");
3158: break; // o breve
3159: case 238 :
3160: buf.append("\u0100");
3161: break; // A macron
3162: case 239 :
3163: buf.append("\u1e46");
3164: break; // N underdot
3165: case 241 :
3166: buf.append("\u1e3b");
3167: break; // l underbar
3168: case 242 :
3169: buf.append("\u016a");
3170: break; // U macron
3171: case 243 :
3172: buf.append("\u0179");
3173: break; // Z acute
3174: case 244 :
3175: buf.append("\u1e5a");
3176: break; // R underdot
3177: case 245 :
3178: buf.append("\u0131");
3179: break; // dotlessi
3180: case 246 :
3181: buf.append("\u1e47");
3182: break; // n underdot
3183: case 247 :
3184: buf.append("\u1e49");
3185: break; // n underbar
3186: case 248 :
3187: buf.append("\u0304");
3188: break; // macron
3189: case 249 :
3190: buf.append("\u0306");
3191: break; // breve
3192: case 250 :
3193: buf.append("\u1e25");
3194: break; // h underdot
3195: case 251 :
3196: buf.append("\u012d");
3197: break; // i breve
3198: case 252 :
3199: buf.append("\u0327");
3200: break; // cedilla
3201: case 253 :
3202: buf.append("\u030b");
3203: break; // hungarumlaut
3204: case 254 :
3205: buf.append("\u0328");
3206: break; // ogonek
3207: case 255 :
3208: buf.append("\u030c");
3209: break; // caron
3210: case 130 :
3211: buf.append("\u012b\u0303");
3212: break; // imacron tilde
3213: case 165 :
3214: buf.append("\u1e5d");
3215: break; // runderdot macron
3216: case 170 :
3217: buf.append("\u016b\0306");
3218: break; // umacron breve
3219: case 178 :
3220: buf.append("\u0101\u0301");
3221: break; // amacron acute
3222: case 179 :
3223: buf.append("\u016b\u0301");
3224: break; // umacron acute
3225: case 184 :
3226: buf.append("\u0113\u0301");
3227: break; // emacron acute
3228: case 187 :
3229: buf.append("\u0113\u0300");
3230: break; // emacron breve
3231: case 188 :
3232: buf.append("\u014d\u0300");
3233: break; // omacron breve
3234: case 193 :
3235: buf.append("\u0101\u0306");
3236: break; // amacron breve
3237: case 198 :
3238: buf.append("\u0101\u0303");
3239: break; // amacron tilde
3240: case 199 :
3241: buf.append("\u012b\u0301");
3242: break; // imacron acute
3243: case 218 :
3244: buf.append("\u1e00");
3245: break; // runderdotmacron acute
3246: case 225 :
3247: buf.append("\u1e5b\u0301");
3248: break; // runderdot acute
3249: case 228 :
3250: buf.append("\u012b\u0306");
3251: break; // imacron breve
3252: case 240 :
3253: buf.append("\u016b\u0303");
3254: break; // umacron tilde
3255: default :
3256: buf.append(c);
3257: break;
3258: }
3259: }
3260: return buf.toString();
3261: }
3262: static public String normanToUnicodeNew(String inp)
3263: {
3264: StringBuffer buf = new StringBuffer();
3265: for (int i = 0; i < inp.length(); i++)
3266: {
3267: char c = inp.charAt(i);
3268: switch (c)
3269: {
3270: case 1 :
3271: buf.append("\u00d0");
3272: break; // Eth
3273: case 2 :
3274: buf.append("\u00f0");
3275: break; // eth
3276: case 3 :
3277: buf.append("\u0141");
3278: break; // Lslash
3279: case 4 :
3280: buf.append("\u0142");
3281: break; // lslash
3282: case 5 :
3283: buf.append("\u0160");
3284: break; // S caron
3285: case 6 :
3286: buf.append("\u0161");
3287: break; // s caron
3288: case 7 :
3289: buf.append("\u00dd");
3290: break; // Y acute
3291: case 8 :
3292: buf.append("\u00fd");
3293: break; // y acute
3294: case 11 :
3295: buf.append("\u00de");
3296: break; // Thorn
3297: case 12 :
3298: buf.append("\u00fe");
3299: break; // thorn
3300: case 14 :
3301: buf.append("\u017d");
3302: break; // Z caron
3303: case 15 :
3304: buf.append("\u017e");
3305: break; // z caron
3306: case 17 :
3307: buf.append("\u0073");
3308: break; // asciitilde
3309: case 18 :
3310: buf.append("j\u0305");
3311: break; // j macron [does a single char exist?]
3312: case 19 :
3313: buf.append("^");
3314: break; // circumflex
3315: case 20 :
3316: buf.append("\u0303");
3317: break; // tilde
3318: case 21 :
3319: buf.append("\u00bd");
3320: break; // onehalf
3321: case 22 :
3322: buf.append("\u00bc");
3323: break; // onequarter
3324: case 23 :
3325: buf.append("\u00b9");
3326: break; // onesuperior
3327: case 24 :
3328: buf.append("\u00be");
3329: break; // threequarters
3330: case 25 :
3331: buf.append("\u00b3");
3332: break; // threesuperior
3333: case 26 :
3334: buf.append("\u00b2");
3335: break; // twosuperior
3336: case 27 :
3337: buf.append("\u00a6");
3338: break; // brokenbar
3339: case 28 :
3340: buf.append("-");
3341: break; // minus
3342: case 29 :
3343: buf.append("\u00d7");
3344: break; // multiply
3345: case 39 :
3346: buf.append("'");
3347: break; // quotesingle
3348: case 94 :
3349: buf.append("\u0302");
3350: break; // circumflex
3351: case 96 :
3352: buf.append("\u0300");
3353: break; // grave
3354: case 196 :
3355: buf.append("\u00c4");
3356: break; // A dieresis
3357: case 197 :
3358: buf.append("\u00c5");
3359: break; // A ring
3360: case 201 :
3361: buf.append("\u00c9");
3362: break; // E acute
3363: case 209 :
3364: buf.append("\u00d1");
3365: break; // N tilde
3366: case 214 :
3367: buf.append("\u00d6");
3368: break; // O dieresis
3369: case 220 :
3370: buf.append("\u00dc");
3371: break; // U dieresis
3372: case 225 :
3373: buf.append("\u00e1");
3374: break; // a acute
3375: case 224 :
3376: buf.append("\u00e0");
3377: break; // a grave
3378: case 226 :
3379: buf.append("\u00e2");
3380: break; // a circumflex
3381: case 228 :
3382: buf.append("\u00e4");
3383: break; // a dieresis
3384: case 227 :
3385: buf.append("\u00e3");
3386: break; // a tilde
3387: case 229 :
3388: buf.append("\u0101");
3389: break; // a macron
3390: case 231 :
3391: buf.append("\u00e7");
3392: break; // c cedilla
3393: case 233 :
3394: buf.append("\u00e9");
3395: break; // e acute
3396: case 232 :
3397: buf.append("\u00e8");
3398: break; // e grave
3399: case 234 :
3400: buf.append("\u00ea");
3401: break; // e circumflex
3402: case 235 :
3403: buf.append("\u00eb");
3404: break; // e dieresis
3405: case 237 :
3406: buf.append("\u00ed");
3407: break; // i acute
3408: case 236 :
3409: buf.append("\u00ec");
3410: break; // i grave
3411: case 238 :
3412: buf.append("\u00ee");
3413: break; // i circumflex
3414: case 239 :
3415: buf.append("\u00ef");
3416: break; // i dieresis
3417: case 241 :
3418: buf.append("\u00f1");
3419: break; // n tilde
3420: case 243 :
3421: buf.append("\u00f3");
3422: break; // o acute
3423: case 242 :
3424: buf.append("\u00f2");
3425: break; // o grave
3426: case 244 :
3427: buf.append("\u00f4");
3428: break; // o circumflex
3429: case 246 :
3430: buf.append("\u00f6");
3431: break; // o dieresis
3432: case 245 :
3433: buf.append("\u00f5");
3434: break; // o tilde
3435: case 250 :
3436: buf.append("\u00fa");
3437: break; // u acute
3438: case 249 :
3439: buf.append("\u00f9");
3440: break; // u grave
3441: case 251 :
3442: buf.append("\u00fb");
3443: break; // u circumflex
3444: case 252 :
3445: buf.append("\u00fc");
3446: break; // u dieresis
3447: case 8224 :
3448: buf.append("\u1e6d");
3449: break; // t underdot
3450: case 176 :
3451: buf.append("\u00b0");
3452: break; // degree
3453: case 162 :
3454: buf.append("\u1ebd");
3455: break; // e tilde
3456: case 163 :
3457: buf.append("\u00a3");
3458: break; // sterling
3459: case 167 :
3460: buf.append("\u00a7");
3461: break; // section
3462: case 182 :
3463: buf.append("\u00b6");
3464: break; // paragraph
3465: case 223 :
3466: buf.append("\u015b");
3467: break; // s acute
3468: case 174 :
3469: buf.append("\u1e5b");
3470: break; // r underdot
3471: case 169 :
3472: buf.append("\u1e45");
3473: break; // n overdot
3474: case 180 :
3475: buf.append("\u0301");
3476: break; // acute
3477: case 168 :
3478: buf.append("\u0308");
3479: break; // dieresis
3480: case 8800 :
3481: buf.append("\u1e6d");
3482: break; // t underdot
3483: case 198 :
3484: buf.append("\u00c6");
3485: break; // AE
3486: case 216 :
3487: buf.append("\u014d");
3488: break; // o macron
3489: case 8734 :
3490: buf.append("\u0129");
3491: break; // i tilde
3492: case 177 :
3493: buf.append("\u00b1");
3494: break; // plusminus
3495: case 165 :
3496: buf.append("\u012b");
3497: break; // i macron
3498: case 181 :
3499: buf.append("\u1e43");
3500: break; // m underdot
3501: case 8706 :
3502: buf.append("\u1e0d");
3503: break; // d underdot
3504: case 8721 :
3505: buf.append("\u1e63");
3506: break; // s underdot
3507: case 960 :
3508: buf.append("\u017a");
3509: break; // z acute
3510: case 8747 :
3511: buf.append("\u1e45");
3512: break; // n overdot
3513: case 937 :
3514: buf.append("\u0169");
3515: break; // u tilde
3516: case 230 :
3517: buf.append("\u00e6");
3518: break; // ae
3519: case 248 :
3520: buf.append("\u00f8");
3521: break; // oslash
3522: case 191 :
3523: buf.append("\u0304\u0306");
3524: break; // macron breve
3525: case 172 :
3526: buf.append("\u1e37");
3527: break; //
3528: case 8730 :
3529: buf.append("j\u0305");
3530: break; // j macron [does a single char exist?]
3531: case 402 :
3532: buf.append("\u0103");
3533: break; // a breve
3534: case 8776 :
3535: buf.append("\u016d");
3536: break; // u breve
3537: case 187 :
3538: buf.append("\u1e42");
3539: break; // M underdot
3540: case 8230 :
3541: buf.append("\u2026");
3542: break; // ellipsis
3543: case 192 :
3544: buf.append("\u00c0");
3545: break; // A grave
3546: case 195 :
3547: buf.append("\u00c3");
3548: break; // A tilde
3549: case 213 :
3550: buf.append("\u00d5");
3551: break; // O tilde
3552: case 338 :
3553: buf.append("m\u0306");
3554: break; // m breve
3555: case 339 :
3556: buf.append("\u0153");
3557: break; // oe
3558: case 8211 :
3559: buf.append("\u2013");
3560: break; // endash
3561: case 8212 :
3562: buf.append("\u2014");
3563: break; // emdash
3564: case 8220 :
3565: buf.append("\u201c");
3566: break; // quotedblleft
3567: case 8221 :
3568: buf.append("\u201d");
3569: break; // quotedblright
3570: case 8216 :
3571: buf.append("\u2018");
3572: break; // quoteleft
3573: case 8217 :
3574: buf.append("\u2019");
3575: break; // quoteright
3576: case 247 :
3577: buf.append("\u1e37");
3578: break; // l underring [actually underdot]
3579: case 9674 :
3580: buf.append("\u1e41");
3581: break; // m overdot
3582: case 255 :
3583: buf.append("n\u0306");
3584: break; // n breve
3585: case 376 :
3586: buf.append("\u00d7");
3587: break; // multiply
3588: case 8364 :
3589: buf.append("\u1e5b");
3590: break; // r underring [actually underdot]
3591: case 8249 :
3592: buf.append("\u1e44");
3593: break; // N overdot
3594: case 8250 :
3595: buf.append("\u1e62");
3596: break; // S underdot
3597: case 64257 :
3598: buf.append("\u1e24");
3599: break; // H underdot
3600: case 64258 :
3601: buf.append("\u1e0c");
3602: break; // D underdot
3603: case 8225 :
3604: buf.append("\u2021");
3605: break; // daggerdbl
3606: case 8218 :
3607: buf.append("\u1e36");
3608: break; // L underdot
3609: case 8222 :
3610: buf.append("\u0113");
3611: break; // e macron
3612: case 194 :
3613: buf.append("\u1e5f");
3614: break; // r underbar
3615: case 202 :
3616: buf.append("r\u0324");
3617: break; // r underdieresis
3618: case 193 :
3619: buf.append("\u012a");
3620: break; // I macron
3621: case 203 :
3622: buf.append("\u016b");
3623: break; // u macron
3624: case 200 :
3625: buf.append("\u1e6c");
3626: break; // T underdot
3627: case 205 :
3628: buf.append("\u1e64");
3629: break; // S acute
3630: case 206 :
3631: buf.append("\u2020");
3632: break; // dagger
3633: case 207 :
3634: buf.append("\u0115");
3635: break; // e breve
3636: case 204 :
3637: buf.append("\u014f");
3638: break; // o breve
3639: case 211 :
3640: buf.append("\u0100");
3641: break; // A macron
3642: case 212 :
3643: buf.append("\u1e46");
3644: break; // N underdot
3645: case 210 :
3646: buf.append("\u1e3b");
3647: break; // l underbar
3648: case 218 :
3649: buf.append("\u016a");
3650: break; // U macron
3651: case 219 :
3652: buf.append("\u0179");
3653: break; // Z acute
3654: case 217 :
3655: buf.append("\u1e5a");
3656: break; // R underdot
3657: case 305 :
3658: buf.append("\u0131");
3659: break; // dotlessi
3660: case 710 :
3661: buf.append("\u1e47");
3662: break; // n underdot
3663: case 732 :
3664: buf.append("\u1e49");
3665: break; // n underbar
3666: case 175 :
3667: buf.append("\u0304");
3668: break; // macron
3669: case 728 :
3670: buf.append("\u0306");
3671: break; // breve
3672: case 729 :
3673: buf.append("\u1e25");
3674: break; // h underdot
3675: case 730 :
3676: buf.append("\u012d");
3677: break; // i breve
3678: case 184 :
3679: buf.append("\u0327");
3680: break; // cedilla
3681: case 733 :
3682: buf.append("\u030b");
3683: break; // hungarumlaut
3684: case 731 :
3685: buf.append("\u0328");
3686: break; // ogonek
3687: case 711 :
3688: buf.append("\u030c");
3689: break; // caron
3690: case 199 :
3691: buf.append("\u012b\u0303");
3692: break; // imacron tilde
3693: case 8226 :
3694: buf.append("\u1e5d");
3695: break; // runderdot macron
3696: case 8482 :
3697: buf.append("\u016b\0306");
3698: break; // umacron breve
3699: case 8804 :
3700: buf.append("\u0101\u0301");
3701: break; // amacron acute
3702: case 8805 :
3703: buf.append("\u016b\u0301");
3704: break; // umacron acute
3705: case 8719 :
3706: buf.append("\u0113\u0301");
3707: break; // emacron acute
3708: case 170 :
3709: buf.append("\u0113\u0300");
3710: break; // emacron breve
3711: case 186 :
3712: buf.append("\u014d\u0300");
3713: break; // omacron breve
3714: case 161 :
3715: buf.append("\u0101\u0306");
3716: break; // amacron breve
3717: case 8710 :
3718: buf.append("\u0101\u0303");
3719: break; // amacron tilde
3720: case 171 :
3721: buf.append("\u012b\u0301");
3722: break; // imacron acute
3723: case 8260 :
3724: buf.append("\u1e00");
3725: break; // runderdotmacron acute
3726: case 183 :
3727: buf.append("\u1e5b\u0301");
3728: break; // runderdot acute
3729: case 8240 :
3730: buf.append("\u012b\u0306");
3731: break; // imacron breve
3732: case 63743 :
3733: buf.append("\u016b\u0303");
3734: break; // umacron tilde
3735: default :
3736: buf.append(c);
3737: break;
3738: }
3739: }
3740: return buf.toString();
3741: }
3742: public static ConversionProperties getFieldNamesAndDestTableName(String create, String query, String tableName)
3743: {
3744: String[] fieldNames = null;
3745: String destTableName = null;
3746: // determine destTableName from createStatement or from source table name
3747: if (!create.equals(""))
3748: {
3749: int fromIndex = create.toLowerCase().indexOf("table") + 5;
3750: int toIndex = create.indexOf("(");
3751: int endIndex = create.indexOf(")", toIndex);
3752:
3753: destTableName = create.substring(fromIndex, toIndex).replaceAll(beanDest.getQC(), "").trim();
3754: System.out.println("destTable " + destTableName);
3755: // retrieve field_names from select statement
3756: // TODO problem with different fieldNames in create statement will overwrite them
3757: if (query.indexOf("*") < 0)
3758: {
3759: int selectEndIndex = query.indexOf("from");
3760: StringTokenizer tokenizer = new StringTokenizer(query.substring(6, selectEndIndex), ",");
3761: int numFields = tokenizer.countTokens();
3762: fieldNames = new String[numFields];
3763: int fieldIndex = 0;
3764: while (tokenizer.hasMoreTokens())
3765: {
3766: String fieldName = tokenizer.nextToken().trim();
3767: fieldNames[fieldIndex] = convertText(fieldName);
3768: System.out.println(fieldNames[fieldIndex]);
3769: fieldIndex++;
3770: }
3771:
3772: } else
3773: {
3774: // use create statement for field names
3775: StringTokenizer tokenizer = new StringTokenizer(create.substring(toIndex + 1, endIndex), ",");
3776: int numFields = tokenizer.countTokens();
3777: fieldNames = new String[numFields];
3778: int fieldIndex = 0;
3779: while (tokenizer.hasMoreTokens())
3780: {
3781: String fieldName = tokenizer.nextToken().trim();
3782: int index = fieldName.lastIndexOf(" ");
3783: fieldNames[fieldIndex] = fieldName.substring(0, index);
3784: System.out.println(fieldNames[fieldIndex]);
3785: fieldIndex++;
3786: }
3787: }
3788: } else
3789: {
3790: destTableName = convertText(tableName);
3791:
3792: // retrieve field_names from select statement
3793: if (query.indexOf("*") < 0)
3794: {
3795: int selectEndIndex = query.indexOf("from");
3796: StringTokenizer tokenizer = new StringTokenizer(query.substring(6, selectEndIndex), ",");
3797: int numFields = tokenizer.countTokens();
3798: fieldNames = new String[numFields];
3799: int fieldIndex = 0;
3800: while (tokenizer.hasMoreTokens())
3801: {
3802: String fieldName = tokenizer.nextToken().trim();
3803: fieldNames[fieldIndex] =beanDest.getQC() + convertText(fieldName)+beanDest.getQC();
3804: // System.out.println("field "+ fieldNames[fieldIndex]);
3805: fieldIndex++;
3806: }
3807:
3808: } else
3809: {
3810: Vector fieldNamesVec = bean.getColumnNames();
3811: fieldNames = new String[fieldNamesVec.size()];
3812: int fieldIndex = -1;
3813: for (Iterator iter = fieldNamesVec.iterator(); iter.hasNext();)
3814: {
3815: String element = (String) iter.next();
3816: fieldNames[++fieldIndex] = beanDest.getQC() + convertText(element) + beanDest.getQC();
3817: // System.out.println("field " + fieldNames[fieldIndex]);
3818: }
3819: }
3820: }
3821: return new ConversionProperties(destTableName, fieldNames);
3822: }
3823: /**
3824: * creates an insert into statement for the specified table and given
3825: * field names
3826: * @param destTableName
3827: * @param fieldNames
3828: * @return
3829: */
3830: public static StringBuffer createInsertCommand(String destTableName, String[] fieldNames)
3831: {
3832: StringBuffer command = new StringBuffer();
3833: command.append("INSERT INTO ");
3834: command.append(beanDest.getQC());
3835: command.append(destTableName); //convertText((String) names.get(tbIndex)));
3836: command.append(beanDest.getQC());
3837: command.append(" (");
3838: for (int i = 0; i < fieldNames.length; i++)
3839: {
3840: command.append(fieldNames[i]);
3841: if (i < fieldNames.length - 1)
3842: command.append(",");
3843: }
3844: command.append(") ");
3845:
3846: command.append(" values ( ");
3847: // add a question marks for every field
3848: for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
3849: command.append("?,");
3850: command.append("?)");
3851: return command;
3852: }
3853: public static StringBuffer createUpdateCommand(String destTableName, String[] fieldNames, String id)
3854: {
3855: StringBuffer command = new StringBuffer();
3856:
3857: command.append("UPDATE ");
3858: command.append(beanDest.getQC());
3859: command.append(destTableName);
3860: //command.append(convertText((String) names.get(tbIndex)));
3861: command.append(beanDest.getQC());
3862: command.append(" SET ");
3863:
3864: int size = bean.getColumnNames().size();
3865: for (int i = 0; i < size - 1; ++i)
3866: command.append(fieldNames[i] + " = ? ,");
3867: command.append(fieldNames[size - 1] + " = ? ");
3868: command.append("WHERE " + id + " = ?");
3869: return command;
3870: }
3871: public static StringBuffer createDeleteCommand(String destTableName, String idField)
3872: {
3873: StringBuffer command = new StringBuffer();
3874:
3875: command.append("DELETE FROM");
3876: command.append(beanDest.getQC());
3877: command.append(destTableName);
3878: //command.append(convertText((String) names.get(tbIndex)));
3879: command.append(beanDest.getQC());
3880: command.append("WHERE " + idField + " = ?");
3881: return command;
3882: }
3883: }
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