import java.util.*;
import java.sql.*;
import java.awt.Cursor;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.PrintStream;
import java.io.UnsupportedEncodingException;

import com.exploringxml.xml.Node;
import com.exploringxml.xml.Xparse;

class Convert 
{
	static DBBean bean = new DBBean();
	static DBBean beanDest = new DBBean();

  static String user ="",passwd="e1nste1n";
  static String userDest="postgres",passwdDest="rogo";
  static boolean batchRun=false;
  static Vector databases = new Vector();
  
  public static void main(String args[])
  {
/*    try
    {
      //byte[] b = "ö".getBytes("UTF-8");
    //  System.out.println("QueryString " +b[0]+" "+b[1]+(new String(b).getBytes()[0])+" "+new String(b).getBytes()[1]);
    //System.out.println(new String(b,"UTF-8"));
    } catch (UnsupportedEncodingException e)
    {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }*/
    FileOutputStream file = null;
    if(args.length!=1)
    {
     System.out.println("Usage: java Convert <xml config file>");
     System.exit(-1);
    }
    if(!(new File(args[0]).exists())) System.exit(0);
    try
    {
      file = new FileOutputStream("./log.txt");
    } catch (FileNotFoundException e1)
    {
      // TODO Auto-generated catch block
      e1.printStackTrace();
    } 
   PrintStream stream= new PrintStream(file);
   System.setOut(stream);
   System.setErr(stream);
   readXMLFile(args[0]);
   System.out.println("Finished!");
    //convert("jdbc:fmpro:http://141.14.237.74:8050","jdbc:postgresql://erebos/test",null,null);
  }
  public static void  convertBatch(DBBean source,DBBean destination,Vector names,Vector layouts,Vector selects,Vector creates,int mode) throws Exception
  {
   /*FM2SQL.ProgressDialog dialog = new  FM2SQL.ProgressDialog(FM2SQL.fmInstance);
   dialog.setTitle("Conversion running ...");
   dialog.title.setText("Getting table data ...");
   dialog.setLocation(500,500); 
   dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
  */
  // FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
   //dialog.thread=Thread.currentThread();
   bean=source;
   beanDest = destination;
 /*  // setting user and passwd 
   bean.setUserAndPasswd(user,passwd);
  // setting user and passwd 
  beanDest.setUserAndPasswd(userDest,passwdDest);
  */
  //dialog.setSize(400,250);
   StringBuffer command= null;
    try
    {
      //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");    
    //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
      bean.setConnection(source.url);
      if(names==null)
       names=bean.getTableNames();
      //Collections.sort(names);
        int tbIndex = 1;
     
     // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
      for(tbIndex=0;tbIndex<names.size();++tbIndex)
      {
        Vector[] result = null;
       try {
     String query = "select * from " + bean.getQC() +names.get(tbIndex).toString() + bean.getQC();
     String layout=(layouts.isEmpty()) ? "":layouts.get(tbIndex).toString(); 
     query = (selects!=null) ? selects.get(tbIndex).toString():query;
        //if  vectors[1].get(i) != null)
        if (!layout.equals(""))
        {
          System.out.println("before "+query+" table"+names.get(tbIndex));
          layout = " layout " + bean.getQC() + layout + bean.getQC();
          String name = names.get(tbIndex).toString();
          StringBuffer queryLayout = new StringBuffer(query);
          queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
          query = queryLayout.toString();
          System.out.println("added layout " + query);
        
        }
        //  if ( layout!= "")
        //   query += " layout " + bean.getQC() + layout + bean.getQC();
     /*   dialog.title.setText("Getting table data ...");
        dialog.table.setText(names.get(tbIndex).toString());
        dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
        dialog.show();
      */
          System.out.println(" performing query "+query);
          //result = bean.getQueryData(query, null, 0);
        bean.getConnection();
        bean.makeQuery(query,0);
      } catch (Exception e)
        {
          System.out.println(e.getMessage());
          e.printStackTrace();
          continue;
        }
        //beanDest.setConnection("jdbc:postgresql://erebos/test3");
        beanDest.setConnection(destination.url);

        Statement stm = beanDest.getConnection().createStatement();

        Vector tables = beanDest.getTableNames();
     //   Collections.sort(tables);
        System.out.println("converting table "+names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames()); 
        tables = beanDest.getTableNames();
        // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
        stm = beanDest.getConnection().createStatement();
        // System.exit(0);
      if(mode==Convert.DataBase.CONVERT_MODE) 
      {
        if(tables.indexOf(names.get(tbIndex))>=0) 
        {
         stm.executeUpdate("drop table "+beanDest.getQC()+names.get(tbIndex)+beanDest.getQC());
        tables.remove((String)names.get(tbIndex));
        System.out.println("dropped table "+ names.get(tbIndex));
        }
        else
        if(tables.indexOf(convertText(names.get(tbIndex).toString()))>=0) 
        {
         stm.executeUpdate("drop table "+beanDest.getQC()+convertText((String)names.get(tbIndex))+beanDest.getQC());
        tables.remove(convertText((String)names.get(tbIndex)));
        System.out.println("dropped table "+ names.get(tbIndex));
        }
 
        if(tables.indexOf(names.get(tbIndex))<0&&tables.indexOf(convertText(names.get(tbIndex).toString()))<0) 
        {
          if(creates.get(tbIndex).equals("")||creates.get(tbIndex).toString().toLowerCase().indexOf("create")<0)
          {
            System.out.println("Warning empty or invalid create statement - creating one for you\n");
        
          command = new StringBuffer(50);
          command.append("CREATE TABLE ");
          command.append(beanDest.getQC());
          command.append(convertText((String)names.get(tbIndex)));
          command.append(beanDest.getQC());
          command.append("(");
           String type = null;
           Vector columnNames=bean.getColumnNames();
          for(int i=0;i<columnNames.size()-1;++i)
          {
           type = bean.metaData.getColumnTypeName(i+1);
         //   System.out.println(i+" "+result[1].get(i)+" "+type);
          type = (type.equals("NUMBER")) ?"INT4":type;
          type = (type.equals("CONTAINER")) ?"TEXT":type;

          command.append(beanDest.getQC() + convertText((String) columnNames.get(i)) + beanDest.getQC() + " " + type + ", ");
        }
          type = bean.metaData.getColumnTypeName(columnNames.size());
          type = (type.equals("NUMBER")) ? "INT4" : type;
          type = (type.equals("CONTAINER")) ? "TEXT" : type;
          command.append(beanDest.getQC() + convertText((String) columnNames.get(columnNames.size() - 1)) + beanDest.getQC() + " " + type);
          command.append(" )");
        }
         else
         command=new StringBuffer().append(creates.get(tbIndex).toString());
         
           System.out.println(command);
          //  System.exit(0);
          //command.append(DBBean.getQC());   
          stm.executeUpdate(command.toString());
        
      }
    }
    // System.out.println(names);
    /*    
    if (idVal == "")
          stm.executeUpdate("INSERT  INTO " + DBBean.getQC() + box.getSelectedItem() + DBBean.getQC() + " (" + DBBean.getQC() + columnName + DBBean.getQC() + ")  VALUES ('" + value + "') ");
        else
          stm.executeUpdate("UPDATE   " + DBBean.getQC() + box.getSelectedItem() + DBBean.getQC() + " SET  " + DBBean.getQC() + columnName + DBBean.getQC() + "='" + value + "' WHERE ID='" + idVal + "' ");
      */

    // Vector[] result2=beanDest.getQueryData(names.get(tbIndex).toString());
    // print results to screen
  /*  for (int i = 0; i < result[1].size(); ++i)
    {
      //print Header
      // System.out.println(" "+result[1].get(i)+" "+result2[1].get(i)+" "+i);
    }*/
    // System.out.println();
    //dialog.title.setText("Writing table data ...");
    Vector row = null;
    //for (int j = 0; j < result[0].size(); ++j)
    command = new StringBuffer();

    command.append("INSERT  INTO ");
    command.append(beanDest.getQC());
    command.append(convertText((String) names.get(tbIndex)));
    command.append(beanDest.getQC());
    command.append(" values ( ");

    for (int i = 0; i < bean.getColumnNames().size() - 1; ++i)
      command.append("?,");
    command.append("?)");
    PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
    System.out.println(command);
    while((row=bean.getNextRow())!=null)
    {
/*      //Vector row = (Vector) result[0].get(j);
      command = new StringBuffer();

        command.append("INSERT  INTO ");
        command.append(beanDest.getQC());
        command.append(convertText((String) names.get(tbIndex)));
        command.append(beanDest.getQC());
        command.append(" values ( ");
   */
             //print rows
     Object obj = null;
     /*
     for(int k=0;k<row.size()-1;++k)
     {
        obj = row.get(k);
        //System.out.println("row "+obj+" "+k);
       if(obj!=null&&!(obj instanceof ArrayList))
       command.append("'"+convertUml(new String(obj.toString().getBytes("UTF-8"),"UTF-8"))+"',"); 
       else if(obj!=null&&   obj instanceof ArrayList)
       command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',"); 
       else command.append("NULL,");
     }
     obj = row.get(row.size() - 1);
     if (obj != null && !(obj instanceof ArrayList))
     command.append("'"+convertUml(new String(obj.toString().getBytes("UTF-8"),"UTF-8"))+"')"); 
      else
      if(obj!=null&&   obj instanceof ArrayList)
       command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')");         //command.append(obj.toString()+")");
       else command.append("NULL)");
       //command.append("'"+row.get(row.size()-1)+"')"); 
     //command.append(" )");
     //  for(int k=0;k<row.size();++k)
        
    // System.out.println();
       //   System.out.println(command+" "+j+" "+row.size()+" "+  ((Vector)result2[0].get(j)).size());
           // System.out.println(command);
            stm.executeUpdate(command.toString());
  // dialog.progress.setValue((int)(((double)(j+1)/(double)result[0].size())*100.0));
  // System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
   command = null;
*/
    for (int k = 0; k < row.size(); ++k)
    {
      obj = row.get(k);
      if (obj instanceof ArrayList)
        obj = ((List) obj).get(0);
      String str = (obj == null) ? "NULL" : obj.toString();
      if (!str.equals("NULL"))
        pstm.setString(k + 1, str);
      else
        pstm.setNull(k + 1, Types.NULL);
    }
    pstm.execute();

}// to for loop    
   
    }
 } catch(Exception e) 
   {
     System.out.println("Error while connecting to database "+ e);
     //dialog.setVisible(false);
     //dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
     //FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
     java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
     java.io.PrintStream stream = new java.io.PrintStream(b);
     stream.print(command+"\n\n");
     e.printStackTrace(stream);
     System.err.println(b);
     //FM2SQL.showErrorDialog(b.toString(), "Error occured !");
   
   }
 //  dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
   //FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));

 //  dialog.setVisible(false); 
  }



  public static void  convert(String source,String destination,Vector names,Vector layouts,Vector selects,Vector creates,int mode) throws Exception
  {
   FM2SQL.ProgressDialog dialog = new  FM2SQL.ProgressDialog(FM2SQL.fmInstance);
   dialog.setTitle("Conversion running ...");
   dialog.title.setText("Getting table data ...");
   dialog.setLocation(FM2SQL.fmInstance.getLocationOnScreen().x+(FM2SQL.fmInstance.getWidth()-400)/2,FM2SQL.fmInstance.getLocationOnScreen().y+(FM2SQL.fmInstance.getHeight()-250)/2); 
	 dialog.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
	 FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
   dialog.thread=Thread.currentThread();
   // setting user and passwd 
   bean.setUserAndPasswd(user,passwd);
  // setting user and passwd 
  beanDest.setUserAndPasswd(userDest,passwdDest);
  dialog.setSize(400,250);
   StringBuffer command= null;
   String query = null;
    try
    {
      //bean.setConnection("jdbc:fmpro:http://141.14.237.74:8050");    
	  //bean.setConnection("jdbc:postgresql://erebos/test","postgres","rogo");
      bean.setConnection(source);
      if(names==null)
       names=bean.getTableNames();
     // Collections.sort(names);
        int tbIndex = 1;
     
     // System.out.println("Start at "+names.indexOf("archimedes_facsimiles"));
      for(tbIndex=0;tbIndex<names.size();++tbIndex)
      {
        Vector[] result = null;
       try {
	    query = "select * from " + bean.getQC() +names.get(tbIndex).toString() + bean.getQC();
	   String layout=(layouts.isEmpty()) ? "":layouts.get(tbIndex).toString(); 
     query = (selects!=null) ? selects.get(tbIndex).toString():query;
        //if  vectors[1].get(i) != null)
        if (layout != "")
        {
          layout = " layout " + bean.getQC() + layout + bean.getQC();
          String name = names.get(tbIndex).toString();
          StringBuffer queryLayout = new StringBuffer(query);
          queryLayout.insert(queryLayout.indexOf(name) + name.length() + 1, " " + layout);
          query = queryLayout.toString();
          System.out.println("added layout  "+ query);
        
        }
        //  if ( layout!= "")
        //	 query += " layout " + bean.getQC() + layout + bean.getQC();
        dialog.title.setText("Getting table data ...");
        dialog.table.setText(names.get(tbIndex).toString());
        dialog.status.setText("Table " + (tbIndex + 1) + " of " + names.size());
        dialog.show();
        //result = bean.getQueryData(query, dialog, 0);
        bean.getConnection();
        bean.makeQuery(query,0);
      } catch (Exception e)
        {
          continue;
        }
        //beanDest.setConnection("jdbc:postgresql://erebos/test3");
        beanDest.setConnection(destination);

        Statement stm = beanDest.getConnection().createStatement();

        Vector tables = beanDest.getTableNames();
       // Collections.sort(tables);
        System.out.println(names.get(tbIndex) + " " + tables.indexOf(convertText((String) names.get(tbIndex)))); // "//beanDest.getTypeNames()); 
        tables = beanDest.getTableNames();
        // System.out.println(beanDest.getTableNames(beanDest.getCatalogs().get(2).toString()));
        stm = beanDest.getConnection().createStatement();
        // System.exit(0);
      if(mode==Convert.DataBase.CONVERT_MODE) 
      {
        if(tables.indexOf(names.get(tbIndex))>=0) 
        {
         stm.executeUpdate("drop table "+beanDest.getQC()+names.get(tbIndex)+beanDest.getQC());
        tables.remove((String)names.get(tbIndex));
        System.out.println("dropped table"+ names.get(tbIndex));
        }
        else
        if(tables.indexOf(convertText(names.get(tbIndex).toString()))>=0) 
        {
         stm.executeUpdate("drop table "+beanDest.getQC()+convertText((String)names.get(tbIndex))+beanDest.getQC());
        tables.remove(convertText((String)names.get(tbIndex)));
        System.out.println("dropped table"+ names.get(tbIndex));
        }
 
				if(tables.indexOf(names.get(tbIndex))<0&&tables.indexOf(convertText(names.get(tbIndex).toString()))<0) 
        {
          if(creates.get(tbIndex).equals("")||creates.get(tbIndex).toString().toLowerCase().indexOf("create")<0)
          {
            System.out.println("Warning empty or invalid create statement - creating one for you\n");
        
          command = new StringBuffer(50);
          command.append("CREATE TABLE ");
          command.append(beanDest.getQC());
          command.append(convertText((String)names.get(tbIndex)));
          command.append(beanDest.getQC());
          command.append("(");
           String type = null;
          Vector columnNames = bean.getColumnNames();
          for(int i=0;i<columnNames.size()-1;++i)
          {
           type = bean.metaData.getColumnTypeName(i+1);
         //   System.out.println(i+" "+result[1].get(i)+" "+type);
          type = (type.equals("NUMBER")) ?"INT4":type;
          type = (type.equals("CONTAINER")) ?"TEXT":type;

          command.append(beanDest.getQC() + convertText((String) columnNames.get(i)) + beanDest.getQC() + " " + type + ", ");
        }
          type = bean.metaData.getColumnTypeName(columnNames.size());
          type = (type.equals("NUMBER")) ? "INT4" : type;
          type = (type.equals("CONTAINER")) ? "TEXT" : type;
          command.append(beanDest.getQC() + convertText((String) columnNames.get(columnNames.size() - 1)) + beanDest.getQC() + " " + type);
          command.append(" )");

          // System.out.println(command);
          //  System.exit(0);
          //command.append(DBBean.getQC());   
          } else
          command=new StringBuffer().append(creates.get(tbIndex).toString());
          stm.executeUpdate(command.toString());
        
      }
    }
    // System.out.println(names);
    /*    
    if (idVal == "")
          stm.executeUpdate("INSERT  INTO " + DBBean.getQC() + box.getSelectedItem() + DBBean.getQC() + " (" + DBBean.getQC() + columnName + DBBean.getQC() + ")  VALUES ('" + value + "') ");
        else
          stm.executeUpdate("UPDATE   " + DBBean.getQC() + box.getSelectedItem() + DBBean.getQC() + " SET  " + DBBean.getQC() + columnName + DBBean.getQC() + "='" + value + "' WHERE ID='" + idVal + "' ");
      */

    // Vector[] result2=beanDest.getQueryData(names.get(tbIndex).toString());
    // print results to screen
   /* for (int i = 0; i < result[1].size(); ++i)
    {
      //print Header
      // System.out.println(" "+result[1].get(i)+" "+result2[1].get(i)+" "+i);
    }*/
    // System.out.println();
    dialog.title.setText("Writing table data ...");
    
    //for (int j = 0; j < result[0].size(); ++j)
    int j=-1;
    Vector row = null;
    command = new StringBuffer();

    command.append("INSERT  INTO ");
    command.append(beanDest.getQC());
    command.append(convertText((String) names.get(tbIndex)));
    command.append(beanDest.getQC());
    command.append(" values ( ");
    
    for(int i=0;i<bean.getColumnNames().size()-1;++i) command.append("?,");
    command.append("?)");
    PreparedStatement pstm = beanDest.getConnection().prepareStatement(command.toString());
    System.out.println(command);
    int rowCount= bean.getRowCount(query);
    while((row=bean.getNextRow())!=null)
    {
      j++;
      // row = (Vector) result[0].get(j);
   /*   command = new StringBuffer();

        command.append("INSERT  INTO ");
        command.append(beanDest.getQC());
        command.append(convertText((String) names.get(tbIndex)));
        command.append(beanDest.getQC());
        command.append(" values ( ");
     */
           //print rows
     Object obj = null;
    /* for(int k=0;k<row.size()-1;++k)
     {
        obj = row.get(k);
        //System.out.println("row "+obj+" "+k);
       if(obj!=null&&!(obj instanceof ArrayList))
       command.append("'"+convertUml(obj.toString())+"',"); 
       else if(obj!=null&&   obj instanceof ArrayList)
       command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"',"); 
       else command.append("NULL,");
     }
     obj = row.get(row.size() - 1);
     if (obj != null && !(obj instanceof ArrayList))
     command.append("'"+convertUml(obj.toString())+"')"); 
      else
      if(obj!=null&&   obj instanceof ArrayList)
       command.append("'"+convertUml(((ArrayList)obj).get(0).toString())+"')");         //command.append(obj.toString()+")");
       else command.append("NULL)");
      */
      //command.append("'"+row.get(row.size()-1)+"')"); 
      //command.append(" )");
      //  for(int k=0;k<row.size();++k)

      // System.out.println();
      //   System.out.println(command+" "+j+" "+row.size()+" "+  ((Vector)result2[0].get(j)).size());
      // System.out.println(command);
      for(int k=0;k<row.size();++k) 
      {
        obj = row.get(k);
        if(obj instanceof ArrayList) obj=((List)obj).get(0);
        String str =(obj==null) ? "NULL":obj.toString();
        if(!str.equals("NULL"))
        pstm.setString(k+1,str); 
        else pstm.setNull(k+1,Types.NULL);
      }
      pstm.execute();
      //stm.executeUpdate(command.toString());
      dialog.progress.setValue((int) (((double) (j + 1) / (double) rowCount) * 100.0));
	// System.out.println( (int)(((double)(j+1)/(double)result[0].size())*100.0)+" "+result[0].size()+" "+j);
   command = null;
}// to for loop    
   
    }
 } catch(Exception e) 
   {
     System.out.println("Error while connecting to database "+ e);
     dialog.setVisible(false);
	   dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
		 FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
		 java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
		 java.io.PrintStream stream = new java.io.PrintStream(b);
		 stream.print(command+"\n\n");
		 e.printStackTrace(stream);
		 FM2SQL.showErrorDialog(b.toString(), "Error occured !");
	 
   }
	 dialog.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
	 FM2SQL.fmInstance.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));

	 dialog.setVisible(false); 
  }

 public static String convertText(String newName)
  {
    StringBuffer alterMe = new StringBuffer(newName.trim().toLowerCase());
    int length = alterMe.length();
    int j = 0;
    int index=alterMe.indexOf(".fp5");
    if ( index >= 0 )
     {
       alterMe.delete(index, index + 4);
       length = length - 4;
     }
 
    while (j < length)
    { 
      if (alterMe.charAt(j) == ' ')
      {
        alterMe.setCharAt(j, '_');
    //    if(j<length-1) j=j+1;
      }
      else
      if (alterMe.charAt(j) == '_')
      {
		
		if(alterMe.charAt(j+1)=='_')
		alterMe.deleteCharAt(j);
        length = length-1;
      //  if(j<length-1) j=j+1;
      }
     else
	 if (alterMe.charAt(j) == 'ä')
		  {
			alterMe.setCharAt(j, 'a');
			alterMe.insert(j+1,"e");
			length=length+1;
			if(j<length-1) j=j+1;
		  }
		else
		 if (alterMe.charAt(j) == 'ö')
			  {
				alterMe.setCharAt(j, 'o');
				alterMe.insert(j+1,"e");
				length=length+1;
				if(j<length-1) j=j+1;
			  }
		else
		 if (alterMe.charAt(j) == 'ü')
			  {
				alterMe.setCharAt(j, 'u');
				alterMe.insert(j+1,"e");
				length=length+1;
				if(j<length-1) j=j+1;
			  }
		else
    if (alterMe.charAt(j) == 'ß')
    {
      alterMe.setCharAt(j, 's');
      alterMe.insert(j + 1, "s");
      length = length + 1;
      if (j < length - 1)
        j = j + 1;
    } else if (alterMe.charAt(j) == ':')
    {
      if(j<length-1)
      {
      if (alterMe.charAt(j + 1) == ':')
      {
        alterMe.setCharAt(j,'_');
        alterMe.delete(j+1,j+2);
      length = length - 1;
   
      }
 
      if (j < length - 1)
        j = j + 1;
      }
    } else if (alterMe.charAt(j) == '-')
    {
      alterMe.setCharAt(j, '_');

    }
    else if (alterMe.charAt(j) == '.')
    {
      if(j==length-1)
      {
        alterMe.delete(j, j); 
        length--;
      }
			else
			alterMe.setCharAt(j,'_');
    }

    ++j;
  }
    return alterMe.toString();
  }
  public static String convertToEntities(String newName)
  {
    StringBuffer alterMe = new StringBuffer(newName.trim());
    int length = alterMe.length();
    int j = 0;

    while (j < length)
    {

      if (alterMe.charAt(j) == '>')
      {
        alterMe.setCharAt(j, '&');
        alterMe.insert(j + 1, "gt;");
        length = length + 2;
        if (j < length - 1)
          j = j + 1;

      } else if (alterMe.charAt(j) == '<')
      {
        alterMe.setCharAt(j, '&');
        alterMe.insert(j + 1, "lt;");
        length = length + 2;
        if (j < length - 1)
          j = j + 1;

      }
      ++j;
    }
    return alterMe.toString();
  }
  public static String convertUml(String newName)
   {
	 StringBuffer alterMe = new StringBuffer(newName.trim());
	 int length = alterMe.length();
	 int j = 0;
	
	
	 while (j < length)
	 { 
	
	   if (alterMe.charAt(j) == '\'')
	   {
		 alterMe.setCharAt(j, '\\');
		 alterMe.insert(j + 1, "'");
		 length = length + 1;
		 if(j<length-1) j=j+1;
	   }
	/*   else
	   if (alterMe.charAt(j) == '"')
	   {
		 alterMe.setCharAt(j, '\\');
		 alterMe.insert(j + 1, "\"");
		 length = length + 1;
		 if(j<length-1) j=j+1;
	   }
	  else
	  if (alterMe.charAt(j) == '>')
	   {
		 alterMe.setCharAt(j, '\\');
		 alterMe.insert(j + 1, ">");
		 length = length + 1;
		 if(j<length-1) j=j+1;
	   }
	  else
	  if (alterMe.charAt(j) == '<')
	   {
		 alterMe.setCharAt(j, '\\');
		 alterMe.insert(j + 1, "<");
		 length = length + 1;
		 if(j<length-1) j=j+1;
	   }
	 else
	 if (alterMe.charAt(j) == '?')
	   {
		 alterMe.setCharAt(j, '\\');
		 alterMe.insert(j + 1, "?");
		 length = length + 1;
		 if(j<length-1) j=j+1;
	   }
	 else
	 if (alterMe.charAt(j) == '&')
	   {
		 alterMe.setCharAt(j, '\\');
		 alterMe.insert(j + 1, "&");
		 length = length + 1;
		 if(j<length-1) j=j+1;
	   }
	 else
	 if (alterMe.charAt(j) == '=')
	   {
		 alterMe.setCharAt(j, '\\');
		 alterMe.insert(j + 1, "=");
		 length = length + 1;
		 if(j<length-1) j=j+1;
	   }
	 else
	 if (alterMe.charAt(j) == ',')
	   {
		 alterMe.setCharAt(j, '\\');
		 alterMe.insert(j + 1, ",");
		 length = length + 1;
		 if(j<length-1) j=j+1;
	   }
	 else
	 if (alterMe.charAt(j) == '.')
	   {
		 alterMe.setCharAt(j, '\\');
		 alterMe.insert(j + 1, ".");
		 length = length + 1;
		 if(j<length-1) j=j+1;
	   }
	 else
	 if (alterMe.charAt(j) == '[')
	   {
		 alterMe.setCharAt(j, '\\');
		 alterMe.insert(j + 1, ".");
		 length = length + 1;
		 if(j<length-1) j=j+1;
	   }
	else
	 if (alterMe.charAt(j) == ']')
	   {
		 alterMe.setCharAt(j, '\\');
		 alterMe.insert(j + 1, ".");
		 length = length + 1;
		 if(j<length-1) j=j+1;
	   }
	else
	 if (alterMe.charAt(j) == '%')
	   {
		 alterMe.setCharAt(j, '\\');
		 alterMe.insert(j + 1, "%");
		 length = length + 1;
		 if(j<length-1) j=j+1;
	   }*/
		++j;
	 }
	 return alterMe.toString();
   }
   
   public static void parseXMLConfig(StringBuffer sb)
    {
      boolean finished = false;
      // parse string and build document tree
      Xparse parser =new Xparse();
      parser.changeEntities = true;
      Node  root = parser.parse(sb.toString());
       // printContents(root);
      Vector databases= new Vector();
      Vector tables = new Vector();
      Vector layouts = new Vector();
      Vector selects = new Vector();
      Vector creates = new Vector();
			int mode = -1;
   
      try
      {
        Node tempNode = root.find("convert/source", new int[] { 1, 1 });
        if(tempNode==null) throw new Error("parse error source tag missing");
        System.out.println(tempNode.name);
        int length =countNodes(tempNode);
        for (int i = 1; i <= length; i++)
        {
          
          DBBean database = new DBBean();
          tables = new Vector();
          layouts = new Vector();
          selects = new Vector();
          creates = new Vector();

         // parse dataBase
          Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
          Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
          Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
          Node node3 = root.find("convert/source/database", new int[] { 1, 1, i});
    			Node nodeMode = root.find("convert/source/database/mode/", new int[] {1, 1, i, 1,1});

          if(node3== null) throw new Error("parse error database tag missing");
          if(node==null) throw new Error("parse error url tag missing");
          if(node1==null) throw new Error("parse error user tag missing");
          if(node2==null) throw new Error("parse error password tag missing");
          String url=node.getCharacters();
          String user=node1.getCharacters();
          String password=node2.getCharacters();
          database.setURL(url.trim());
          database.setUserAndPasswd(user.trim(),password.trim());
          System.out.println(node.name + " " + node.getCharacters());
          System.out.println(node1.name + " " + node1.getCharacters());
          System.out.println(node2.name + " " + node2.getCharacters());
          String modeString = "";
          if (nodeMode == null)
            modeString = "convert";
          else
            modeString = nodeMode.getCharacters();
          if (modeString.equals("convert"))
            mode = DataBase.CONVERT_MODE;
          else
            if (modeString.equals("append"))
              mode = DataBase.APPEND_MODE;
            else
              if (modeString.equals("update"))
                mode = DataBase.UPDATE_MODE;
       //   if(node3!=null)
         // System.out.println(node3.name);
          
          int length2= countNodes(node3);
    
         System.out.println("number of tables " +length2);
    
          for(int j=1;j<=length2;++j)
          {
            Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
            Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
            Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
            if(node4!=null)
            System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
            if(node5!=null)
            System.out.println(node5.name + " " + node5.getCharacters());
            if(node6!=null)
            System.out.println(node6.name + " " + node6.getCharacters());
            if(node4==null) throw new Error("parse error table tag missing");
            // if(node5==null) throw new Error("parse error select tag missing");
            // if(node6==null) throw new Error("parse error create tag missing");
            String name = (String)node4.attributes.get("name");
            String layout = (String)node4.attributes.get("layout");
            if(name==null) throw new Error("parse error required table tag attribute name missing");
            if(layout==null) layout ="";
            if(name.equals("")) throw new Error("parse error table tag attribute must not be empty");
            tables.add(name);
            layouts.add(layout);
            String query = (node5==null) ? "":node5.getCharacters(); 
            if(query.equals("")) System.err.println("Warning empty select tag or  select tag missing !!");
            query = (query.equals("")) ? "select * from "+database.getQC()+name+database.getQC():query;
            selects.add(query);
            if(node6!=null) creates.add(node6.getCharacters().trim());
             else
              creates.add("");
            
          }
          databases.add(new DataBase(database, tables, layouts, selects,creates,mode));
        }
        DBBean database = new DBBean();
        // parse dataBase
        Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
        Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
        Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
        String url = node.getCharacters();
        String user = node1.getCharacters();
        String password = node2.getCharacters();
        System.out.println(url);
        database.setURL(url.trim());
        database.setUserAndPasswd(user.trim(), password.trim());
        //databases.add(database);
       for (Iterator iter = databases.iterator(); iter.hasNext();)
      {
        DataBase db = (DataBase) iter.next();
        convertBatch(db.bean,database,db.tables,db.layouts,db.selects,db.creates,mode);
        
      }
        // printContents(node3);
        //   FM2SQL.fmInstance=new FM2SQL();
      } catch (Exception e)
      {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
      /*
      Node tempNode=root.find(rootNode,new int[] {1});
      if(tempNode==null) return rtTag;
      int count=1;
      /* 
      for(int i=0;i<tempNode.contents.v.size();++i)
      {
        // System.out.println(((Node)tempNode.contents.v.elementAt(i)).attributes+" "+i);
      }*/
   /*
      if(tempNode.contents.v.size()==0) return rtTag;
      Node notNull=null;
      Node rtNode=root.find(rootNode+"/rt",new int[] {1,1});
      rtTag=(rtNode==null) ? "finished":rtNode.getCharacters();
      System.out.println("rt tag is "+ rtTag);
      int length=0;//(tempNode.contents.length()-1)/2;
      for(int i=0;i<tempNode.contents.v.size();++i)    {
        Node node=(Node)tempNode.contents.v.elementAt(i);
        if(node.type.equals("element"))
        if(node.name.equals("doc"))length++;
        // System.out.println(((Node)tempNode.contents.v.elementAt(i)).attributes+" "+i);
      }
     if(length==0) rtTag="finished";        
      while(count<=length)
      {
         //System.out.println(count+" "+length);
        tempNode=root.find(rootNode+"/doc",new int[] {1,count});
        if(tempNode==null)
        {
          rtTag="finished";
          break;
        }    
        Bundle.Document doc = new Bundle.Document();
        Hashtable attributes=tempNode.attributes;
        int docID=0;
        int ver=0;
         if(attributes != null)
        {
          docID=Integer.parseInt((String)attributes.get("id"));
          ver=Integer.parseInt((String)attributes.get("ver"));
          doc.addData("docID",(String)attributes.get("id"));
          doc.addData("ver",(String)attributes.get("ver"));
        }
        bundle.docs.put(new Integer(docID),doc);
        bundle.documents.addElement(doc);
        // System.out.println("id " +docID+" ver "+ver);
        Node fileNode=root.find(rootNode+"/doc/file",new int[] {1,count,1});
        attributes=(fileNode==null)? null:fileNode.attributes;
        int fileID=0;
        int fileVer=0;
        if(attributes != null)
        {
          fileID=Integer.parseInt((String)attributes.get("id"));
          fileVer=Integer.parseInt((String)attributes.get("ver"));
          doc.addData("fileID",(String)attributes.get("id"));
          doc.addData("fileVer",(String)attributes.get("ver"));
          // System.out.println("fileid " +fileID+" ver "+fileVer);
        }
     // add all Document tags to actual Document
        for(int i=0;i<tempNode.contents.v.size();++i)
        {   
          Node node=(Node)tempNode.contents.v.elementAt(i);
          if(node.type.equals("element"))
           {
             //System.out.println(node.name+" "+node.getCharacters()+" "+node.contents.v.size());
             if(node.contents.v.size()>1) 
             {
               for(int j=0;j<node.contents.v.size();++j)
               {  
                 Node node2=(Node)node.contents.v.elementAt(j);
                 if(node2.type.equals("element"))
                  {
                    doc.addData(node2.name,node2.getCharacters());
                    //System.out.println(node2.name+" "+node2.getCharacters()+" "+node2.contents.v.size());
                  }
               }
             } else doc.addData(node.name,node.getCharacters());
          }
        }
        bundle.docsLocator.put(doc.locator,doc); 
   
      bundle.documents.addElement(doc);
        count++;
      }      */
  
      // System.out.println(bundle.docs.size());
   // return rtTag;
    }
    public static Vector getXMLConfig(String xmlFile)
     {
       StringBuffer sb=null;
       try 
             {
                // read XML Metadata from a file
                FileInputStream fi= new FileInputStream(xmlFile);
                InputStreamReader isr= new InputStreamReader(fi,"UTF-8");
                BufferedReader buffr = new BufferedReader(isr);
                sb=new StringBuffer();
                int c=0;
                while ((c = buffr.read()) != -1) 
                {
                  char ch=(char)c;
                  sb.append(ch);
                  // System.out.print((char)c);
                }
               
            } catch(Exception e) { e.printStackTrace();} 
  
       boolean finished = false;
       // parse string and build document tree
       Xparse parser =new Xparse();
       parser.changeEntities = true;
       Node  root = parser.parse(sb.toString());
   // printContents(root);
       Vector databases= new Vector();
       Vector tables = new Vector();
       Vector layouts = new Vector();
       Vector selects = new Vector();
       Vector creates = new Vector();
       int mode = -1;
       try
       {
         Node tempNode = root.find("convert/source", new int[] { 1, 1 });
         if(tempNode==null) throw new Error("parse error source tag missing");
         System.out.println(tempNode.name);
         int length =countNodes(tempNode);
         for (int i = 1; i <= length; i++)
         {
          
           DBBean database = new DBBean();
           tables = new Vector();
           layouts = new Vector();
           selects = new Vector();
           creates = new Vector();

          // parse dataBase
           Node node = root.find("convert/source/database/url", new int[] { 1, 1, i, 1 });
           Node node1 = root.find("convert/source/database/user", new int[] { 1, 1, i, 1, 1 });
           Node node2 = root.find("convert/source/database/password", new int[] { 1, 1, i, 1, 1 });
           Node node3 = root.find("convert/source/database", new int[] { 1, 1, i});
           Node nodeMode = root.find("convert/source/database/mode", new int[] { 1, 1, i, 1, 1 });
           
           if(node3== null) throw new Error("parse error database tag missing");
           if(node==null) throw new Error("parse error url tag missing");
           if(node1==null) throw new Error("parse error user tag missing");
           if(node2==null) throw new Error("parse error password tag missing");
           String url=node.getCharacters();
           String user=node1.getCharacters();
           String password=node2.getCharacters();
           database.setURL(url.trim());
           database.setUserAndPasswd(user.trim(),password.trim());
           System.out.println(node.name + " " + node.getCharacters());
           System.out.println(node1.name + " " + node1.getCharacters());
           System.out.println(node2.name + " " + node2.getCharacters());
           String modeString = "";  
          if (nodeMode == null)
            modeString = "convert";
          else
            modeString = nodeMode.getCharacters();
          if (modeString.equals("convert"))
            mode = DataBase.CONVERT_MODE;
          else
            if (modeString.equals("append"))
              mode = DataBase.APPEND_MODE;
            else
              if (modeString.equals("update"))
                mode = DataBase.UPDATE_MODE;
			 
        
        //   if(node3!=null)
          // System.out.println(node3.name);
          
           int length2= countNodes(node3);
    
          System.out.println("number of tables " +length2);
    
           for(int j=1;j<=length2;++j)
           {
             Node node4 = root.find("convert/source/database/table", new int[] { 1, 1, i, j });
             Node node5 = root.find("convert/source/database/table/select", new int[] { 1, 1, i, j, 1 });
             Node node6 = root.find("convert/source/database/table/create", new int[] { 1, 1, i, j, 1 });
             if(node4!=null)
             System.out.println(node4.name + " " + node4.attributes.get("layout").equals(""));
             if(node5!=null)
             System.out.println(node5.name + " " + node5.getCharacters());
             if(node6!=null)
             System.out.println(node6.name + " " + node6.getCharacters());
             if(node4==null) throw new Error("parse error table tag missing");
             // if(node5==null) throw new Error("parse error select tag missing");
             // if(node6==null) throw new Error("parse error create tag missing");
             String name = (String)node4.attributes.get("name");
             String layout = (String)node4.attributes.get("layout");
             if(name==null) throw new Error("parse error required table tag attribute name missing");
             if(layout==null) layout ="";
             if(name.equals("")) throw new Error("parse error table tag attribute must not be empty");
             tables.add(name);
             layouts.add(layout);
             String query = (node5==null) ? "":node5.getCharacters(); 
             if(query.equals("")) System.err.println("Warning empty select tag or  select tag missing !!");
             query = (query.equals("")) ? "select * from "+database.getQC()+name+database.getQC():query;
             selects.add(query);
             if(node6!=null) creates.add(node6.getCharacters().trim());
              else
               creates.add("");
            
           }
           databases.add(new DataBase(database, tables, layouts, selects,creates,mode));
         }
         DBBean database = new DBBean();
         // parse dataBase
         Node node = root.find("convert/destination/database/url", new int[] { 1, 1, 1, 1 });
         Node node1 = root.find("convert/destination/database/user", new int[] { 1, 1, 1, 1, 1 });
         Node node2 = root.find("convert/destination/database/password", new int[] { 1, 1, 1, 1, 1 });
         String url = node.getCharacters();
         String user = node1.getCharacters();
         String password = node2.getCharacters();
         System.out.println(url);
         database.setURL(url.trim());
         database.setUserAndPasswd(user.trim(), password.trim());
         databases.add(new DataBase(database,null,null,null,null,0));
         //databases.add(database);
    /*    for (Iterator iter = databases.iterator(); iter.hasNext();)
       {
         DataBase db = (DataBase) iter.next();
         convertBatch(db.bean,database,db.tables,db.layouts,db.selects,db.creates);
        
       }*/
         // printContents(node3);
         //   FM2SQL.fmInstance=new FM2SQL();
       } catch (Exception e)
       {
         // TODO Auto-generated catch block
         e.printStackTrace();
       }
       return databases;
      }


    private static int countNodes(Node tempNode)
    {
      int length = 0;
      for(int i=0;i<tempNode.contents.v.size();++i)    {
      Node node=(Node)tempNode.contents.v.elementAt(i);
      if(node.type.equals("element"))
      {
        if(node.name.equals("database"))length++;
        if(node.name.equals("table"))length++;
      }
      
            // System.out.println(((Node)tempNode.contents.v.elementAt(i)).attributes+" "+i);
       }
     return length;
    }
    private static void printContents(Node root)
    {
        
      
      Vector contents=(root.index==null)? root.contents.v:root.index.v;
      for (int i=0;i<contents.size();++i)
      {
        Node n=(Node)contents.elementAt(i);
        if(n.type.equals("element"))
        {
           System.out.println("tag "+n.name);
           System.out.println(n.getCharacters());
          //contents=n.contents.v i=0;
        }
        // System.out.println(n.type);
      
        
      
      }
    }
    public static void readXMLFile(String xmlFile)
    {
    try 
        {
           // read XML Metadata from a file
           FileInputStream fi= new FileInputStream(xmlFile);
           InputStreamReader isr= new InputStreamReader(fi,"UTF-8");
           BufferedReader buffr = new BufferedReader(isr);
           StringBuffer sb=new StringBuffer();
           int c=0;
           while ((c = buffr.read()) != -1) 
           {
             char ch=(char)c;
             sb.append(ch);
             // System.out.print((char)c);
           }
           parseXMLConfig(sb);
       } catch(Exception e) { e.printStackTrace();} 
    }
public static class DataBase 
{
  DBBean bean;
  Vector creates;
  Vector selects;
  Vector layouts;
  Vector tables;
  final static int CONVERT_MODE = 1;
	final static int APPEND_MODE  = 2;
	final static int UPDATE_MODE  = 3;
  int mode = -1;
  
  public DataBase(DBBean bean,Vector tables,Vector layouts,Vector selects,Vector creates,int mode)
  {
    this.bean = bean;
    this.tables = tables;
    this.layouts = layouts;
    this.selects = selects;
    this.creates = creates;
    this.mode = mode;
  }
  public void exportToXML(BufferedWriter buffr) throws Exception
   {
     buffr.write("    <database>\n");
     buffr.write("      <url>"+bean.url+"</url>\n");
     buffr.write("      <user>"+bean.user+"</user>\n");
     buffr.write("      <password>"+bean.passwd+"</password>\n");
     String modeString ="";
    if (mode == CONVERT_MODE)
      modeString = "convert";
    else
      if (mode == APPEND_MODE)
        modeString = "append";
      else
        if (mode == UPDATE_MODE)
          modeString = "update";
  
     buffr.write("      <mode>"+ modeString+"</mode>\n");
     int index = 0;
     while(index<tables.size())
     {
       String table=(String)tables.get(index);
       String layout=(String)layouts.get(index);
       String select=(String)selects.get(index);
       String create=(String)creates.get(index);
   
       buffr.write("      <table name = \""+table+"\" layout = \""+layout+"\">\n");
       buffr.write("         <select>"+ convertToEntities(select)+"</select>\n");
       if(!create.equals(""))
       buffr.write("         <create>"+create+"         </create>\n"); 
       buffr.write("      </table>\n");
       index++;
     }
     buffr.write("    </database>\n"); 
   }
public String toString() {return bean.url+" "+tables;}

}
public static String convertToUTF8(Object command)
{
  String str = null;
  try
  {
    str = new String(command.toString().getBytes("UTF-8"));
  } catch (UnsupportedEncodingException e)
  {
    // TODO Auto-generated catch block
    e.printStackTrace();
  }
  return str;
}
  public static void writeConfig(String file, DataBase source, DataBase destination) throws Exception
  {
    if(!file.toLowerCase().endsWith(".xml")) file+=".xml"; 
    File f = new File(file);
  
    FileOutputStream fout= new FileOutputStream(f);
    OutputStreamWriter outsw = new OutputStreamWriter(fout,"UTF-8");
    BufferedWriter buffw = new BufferedWriter(outsw);
    buffw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
    buffw.newLine();
    buffw.write("<convert>\n");
    buffw.write("  <source>\n");
    source.exportToXML(buffw);
    buffw.write("  </source>\n");
    buffw.write("\n  <destination>\n");
    destination.exportToXML(buffw);
    buffw.write("  </destination>\n");
    buffw.write("</convert>\n");
    buffw.close();
  } 
}