root/trunk/src/java/org/jcoderz/phoenix/dbview/DbView.java

/*
 * $Id$
 *
 * Copyright 2006, The jCoderZ.org Project. All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are
 * met:
 *
 *    * Redistributions of source code must retain the above copyright
 *      notice, this list of conditions and the following disclaimer.
 *    * Redistributions in binary form must reproduce the above
 *      copyright notice, this list of conditions and the following
 *      disclaimer in the documentation and/or other materials
 *      provided with the distribution.
 *    * Neither the name of the jCoderZ.org Project nor the names of
 *      its contributors may be used to endorse or promote products
 *      derived from this software without specific prior written
 *      permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS "AS IS" AND
 * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 * PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE REGENTS AND CONTRIBUTORS
 * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
 * BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
 * WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
 * OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
 * ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */
package org.jcoderz.phoenix.dbview;


import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.io.Reader;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.nio.charset.Charset;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.TimeZone;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.jcoderz.commons.util.Constants;
import org.jcoderz.commons.util.DbUtil;
import org.jcoderz.commons.util.IoUtil;
import org.jcoderz.commons.util.LoggingUtils;
import org.jcoderz.commons.util.XmlUtil;


/**
 * Converts the db content into xml.
 *
 * @author Andreas Mandel
 */
public class DbView
{
   /** Default database jndi name. */
   public static final String DATASOURCE = "java:comp/env/jdbc/svs/db";

   /** Line separator to be used in output files. */
   public static final String LINE_SEPARATOR = Constants.LINE_SEPARATOR;

   private static final int MILLIS_PER_SECOND
           = org.jcoderz.commons.types.Date.MILLIS_PER_SECOND;
   private static final String SELECT_ALL_TABLES = "select * from tab";
   private static final String CLASSNAME = DbView.class.getName();
   private static final Logger logger = Logger.getLogger(CLASSNAME);

   private final Map mTypeMapper = new HashMap();
   private final DateFormat mDateFormater
         = new SimpleDateFormat("yyyy.MM.dd HH:mm:ss.SSS",
                 Constants.SYSTEM_LOCALE);

   {
      mDateFormater.setTimeZone(TimeZone.getTimeZone("UTC"));
   }

   private String mDbDriver = Constants.ORACLE_DRIVER_CLASS_NAME;
   private int mNumberOfColumns;
   private String mSqlStatement;
   private String mDbUrl;
   private String mDbUser;
   private String mDbPasswd;
   private File mOutputDir;
   private File mOutputFile;
   private Level mLogLevel;


   public static void main (String[] args)
         throws SecurityException
   {
      final DbView main = new DbView();
      try
      {
         main.parseArguments(args);
         if (main.mSqlStatement == null)
         {
            main.dumpAllTables();
         }
         else
         {
            main.performQuery();
         }
      }
      catch (IllegalArgumentException ex)
      {
         System.err.println(ex.getMessage());
         System.err.println("Usage:");
         System.err.println(DbView.class.getName());
         System.err.println(" -dbUrl [-dbUser scott -dbPasswd tiger] "
               + "-sql \"select * from tab\" "
               + "-outFile foo.xml");
         System.err.println("DbView -dbUrl [-dbUser scott -dbPasswd tiger] "
               + "-outDir sql/");
         System.err.println();
         System.err.println("To add type converters use:");
         System.err.println("-type ROW_NAME JAVA_CLASS FROM_DB TO_DISPLAY");
         System.err.println("ex.: -type STATUS com.encous.foo.Status fromInt "
               + "toDisplayString");
         System.err.println();
         System.err.println("To use a db driver other than oracle add:");
         System.err.println("-dbDriver your.db.driver");
      }
      catch (Exception ex)
      {
         ex.printStackTrace();
      }
   }

   public void dumpAllTables ()
         throws IOException, ClassNotFoundException, SQLException
   {
      dumpAllTables(mOutputDir);
   }

   public void performQuery ()
         throws IOException, ClassNotFoundException, SQLException
   {
      performConvertion (mOutputFile, mSqlStatement);
   }

   public void dumpAllTables (File dir)
         throws IOException, ClassNotFoundException, SQLException
   {
      Connection dbConnection = null;
      PreparedStatement statement = null;
      try
      {
         dbConnection = getConnectionFromDbUrl();

         performConvertion(new File(dir, "tables.xml").getCanonicalPath(),
               dbConnection, SELECT_ALL_TABLES);

         statement = dbConnection.prepareStatement(SELECT_ALL_TABLES);
         final ResultSet rs = statement.executeQuery();
         while (rs.next())
         {
            final String tableName = rs.getString(1);
            performConvertion(
                  new File(dir,
                      escapeTableName(tableName) + ".xml").getCanonicalPath(),
                      dbConnection, "select * from \"" + tableName + '"');
         }
      }
      finally
      {
          DbUtil.close(statement);
          DbUtil.close(dbConnection);
      }

   }

   private void parseArguments (String[] args)
         throws IOException, NoSuchMethodException, ClassNotFoundException
   {
      try
      {
         for (int i = 0; i < args.length; )
         {
            if ("-sql".equals(args[i]))
            {
               mSqlStatement = args[++i];
            }
            else if ("-dbUrl".equals(args[i]))
            {
               mDbUrl = args[++i];
            }
            else if ("-dbUser".equals(args[i]))
            {
               mDbUser = args[++i];
            }
            else if ("-dbPasswd".equals(args[i]))
            {
               mDbPasswd = args[++i];
            }
            else if (args[i].equals("-type"))
            {
               final String rowname = args[++i];
               final String classname = args[++i];
               final String fromDbType = args[++i];
               final String toDisplay = args[++i];
               addTypeMapping(rowname, classname, fromDbType, toDisplay);
            }
            else if ("-outDir".equals(args[i]))
            {
               mOutputDir = new File(args[++i]);
            }
            else if ("-outFile".equals(args[i]))
            {
               mOutputFile = new File(args[++i]);
            }
            else if ("-dbDriver".equals(args[i]))
            {
               mDbDriver = args[++i];
            }
            else if ("-loglevel".equals(args[i]))
            {
               mLogLevel = Level.parse(args[++i]);
               Logger.getLogger("").setLevel(mLogLevel);
               LoggingUtils.setGlobalHandlerLogLevel(mLogLevel);
            }
            else
            {
               throw new IllegalArgumentException(
                     "Invalid argument '" + args[i]  + "'");
            }
            ++i;
         }

         checkParameters();
      }
      catch (IndexOutOfBoundsException e)
      {
         final IllegalArgumentException ex = new IllegalArgumentException(
            "Missing value for " + args[args.length - 1]);
         ex.initCause(e);
         throw ex;
      }
   }

   private void checkParameters ()
        throws IllegalArgumentException
   {
       if (mOutputDir == null && mOutputFile == null)
       {
           throw new IllegalArgumentException(
               "Need either output dir or output file.");
       }
       if (mSqlStatement != null && mOutputFile != null)
       {
           throw new IllegalArgumentException(
              "Need output file for sql statement.");
       }
       if (mSqlStatement == null && mOutputDir == null)
       {
           throw new IllegalArgumentException(
                   "Need output dir for global dump.");
       }
       if (!mOutputDir.isDirectory())
       {
          throw new RuntimeException("out dir must be a directory.");
       }
       if (mOutputFile.isDirectory())
       {
          throw new RuntimeException(
                "out file must not be a directory.");
       }
   }

   public void performConvertion (File file, String query)
         throws IOException, ClassNotFoundException, SQLException
   {
      PrintWriter out = null;
      Connection dbConnection = null;
      PreparedStatement statement = null;
      try
      {
         final FileOutputStream stream = new FileOutputStream(file);
         final OutputStreamWriter writer = new OutputStreamWriter(stream,
               Charset.forName("us-ascii"));
         out = new PrintWriter(writer);
         dbConnection = getConnectionFromDbUrl();
         statement = dbConnection.prepareStatement(query);
         final ResultSet rs = statement.executeQuery();
         xmlOpen(null, dbConnection.getMetaData().getURL(), query, out);
         metaData2Xml(rs.getMetaData(), out);
         resultSet2Xml(rs, out);
         xmlClose(out);

      }
      finally
      {
         IoUtil.close(out);
         DbUtil.close(statement);
         DbUtil.close(dbConnection);
      }
   }

   public void addTypeMapping (String typeName, String typeClass,
         String fromDb, String toString)
         throws SecurityException, NoSuchMethodException, ClassNotFoundException
   {
      mTypeMapper.put(typeName,
            new TypeMapper(typeName, typeClass, fromDb, toString));
   }

   private void performConvertion (String fileName,
         final Connection dbConnection, String query)
         throws IOException, SQLException
   {
      logger.fine("about to dump '" + query + "' into '" + fileName +"'.");
      PrintWriter out = null;
      PreparedStatement statement = null;
      try
      {
         final FileOutputStream stream = new FileOutputStream(fileName);
         final OutputStreamWriter writer = new OutputStreamWriter(stream,
               Charset.forName("us-ascii"));
         out = new PrintWriter(writer);
         statement = dbConnection.prepareStatement(query);
         final ResultSet rs = statement.executeQuery();
         xmlOpen(null, dbConnection.getMetaData().getURL(), query, out);
         metaData2Xml(rs.getMetaData(), out);
         resultSet2Xml(rs, out);
         xmlClose(out);

      }
      finally
      {
          IoUtil.close(out);
          DbUtil.close(statement);
      }
   }

   private Connection getConnectionFromDataSource ()
       throws NamingException, SQLException
   {
      final Context ctx = new InitialContext();
      final DataSource ds = (DataSource) ctx.lookup(DATASOURCE);
      return ds.getConnection();
   }

   private Connection getConnectionFromDbUrl ()
         throws ClassNotFoundException, SQLException
   {
      Class.forName(mDbDriver);
      final Connection con;

      if (mDbUser == null)
      {
         con = DriverManager.getConnection(mDbUrl);
      }
      else
      {
         con = DriverManager.getConnection(mDbUrl, mDbUser, mDbPasswd);
      }

      return con;
   }

   private void xmlOpen (String dataSource, String dataBaseUri,
         String statement, PrintWriter out)
         throws IOException, SQLException
   {
      out.print("<result statement='");
      out.print(XmlUtil.attributeEscape(statement));
      out.println("'");
      if (dataSource != null)
      {
         out.print("        data-source='");
         out.print(XmlUtil.attributeEscape(dataSource));
         out.println("'");
      }
      if (dataBaseUri != null)
      {
         out.print("        db-uri='");
         out.print(XmlUtil.attributeEscape(dataBaseUri));
         out.println("'");
      }
      out.print("        creation-date='");
      out.print(display(new Date()));
      out.println("'>");
   }

   private void xmlClose (PrintWriter out)
   {
      out.println("</result>");
   }

   private void metaData2Xml (ResultSetMetaData md, PrintWriter out)
         throws IOException, SQLException
   {
      mNumberOfColumns = md.getColumnCount();
      out.print("   <meta-data number-of-columns='");
      out.print(mNumberOfColumns);
      out.println("'>");
      for (int column = 1; column <= mNumberOfColumns; column++)
      {
         out.println("      <column");
         metaDataAsString("name", md.getColumnName(column), out);
         metaDataAsString("type-name", md.getColumnTypeName(column), out);
         metaDataAsString("display-name", md.getColumnLabel(column), out);
         metaDataAsString("class-name", md.getColumnClassName(column), out);
         try
         {
            metaDataAsString("precision", md.getPrecision(column), out);
         }
         catch (NumberFormatException ex)
         {
            // this happens for lobs in oracle seems to denote infinity.
         }
         metaDataAsString("scale", md.getScale(column), out);
         metaDataAsString("catalog-name", md.getCatalogName(column), out);
         metaDataAsString("schema-name", md.getSchemaName(column), out);
         metaDataAsString("table-name", md.getTableName(column), out);
         metaDataAsStringNullable("is-nullable", md.isNullable(column), out);
         metaDataAsString("type", md.getColumnType(column), out);
         metaDataAsString("display-size", md.getColumnDisplaySize(column), out);
         out.println("      />");
      }
      out.println("   </meta-data>");
   }


   private void metaDataAsStringNullable (
         String attributeName, int nullable, PrintWriter out)
   {
      switch (nullable)
      {
         case ResultSetMetaData.columnNoNulls:
            metaDataAsString(attributeName, "no-nulls", out);
            break;
         case ResultSetMetaData.columnNullable:
            metaDataAsString(attributeName, "nullable", out);
            break;
         case ResultSetMetaData.columnNullableUnknown:
            metaDataAsString(attributeName, "unknown", out);
            break;
         default:
            metaDataAsString(attributeName, "illeagal", out);
         break;
      }
   }

   /**
    * @param string
    * @param i
    * @param out
    */
   private void metaDataAsString (
         String attributeName, int i, PrintWriter out)
   {
      metaDataAsString(attributeName, String.valueOf(i), out);
   }


   /**
    * @param string
    * @param string2
    * @param out
    */
   private void metaDataAsString (String attributeName,
         String attributeValue, PrintWriter out)
   {
      out.print("              ");
      out.print(attributeName);
      out.print("='");
      out.print(XmlUtil.attributeEscape(attributeValue));
      out.println("'");
   }



   private void resultSet2Xml (ResultSet rs, PrintWriter out)
         throws IOException, SQLException
   {
      out.println("   <result-set>");
      while (rs.next())
      {
         out.print("      <row row-number='");
         out.print(rs.getRow());
         out.println("'>");
         result2Xml(rs, out);
         out.println("      </row>");
      }
      out.println("   </result-set>");
   }

   private void result2Xml (ResultSet rs, PrintWriter out)
         throws SQLException, IOException
   {
      for (int column = 1; column <= mNumberOfColumns; column++)
      {
         final Object o = typedGetter(rs, column);
         out.print("         <column");
         if (o == null)
         {
            out.println(" isNull='true'>");
         }
         else
         {
            out.println(">");
         }
         final String name = rs.getMetaData().getColumnName(column);
         final String typeName = rs.getMetaData().getColumnTypeName(column);
         object2Xml(o, name, typeName, out);
         out.println("         </column>");
      }

   }

   private Object typedGetter (ResultSet rs, int column)
         throws SQLException, IOException
   {
      final Object result;
      final int type = rs.getMetaData().getColumnType(column);
      switch (type)
      {
         case Types.TIMESTAMP:
            result = convertTimestamp(rs.getTimestamp(column));
            break;
         case Types.DATE:
         case Types.TIME:
            // FIXME: Take care for milisecond & timezone!?
            result = rs.getDate(column);
            break;
         case Types.CLOB:
            result = readNclob(rs, column);
            break;
         case Types.BLOB:
            result = readBlob(rs, column);
            break;
         default:
            result = rs.getObject(column);
      }
      return result;
   }

   private Object readBlob (ResultSet rs, int column)
         throws SQLException
   {
      final Blob blob = rs.getBlob(column);
      final byte [] data;
      if (blob != null)
      {
         final long length = blob.length();
         if (length > Integer.MAX_VALUE)
         {
            data = "Length of Blob exceeds maximum of MAX_INT".getBytes();
         }
         else
         {
            data = blob.getBytes(1, (int) length);
         }
      }
      else
      {
         data = null;
      }
      return data;
   }

   private String readNclob (ResultSet rs, int column)
         throws SQLException, IOException
   {
      final String result;
      final Reader reader = rs.getCharacterStream(column);
      if (reader != null)
      {
          try
          {
             result = IoUtil.readFully(reader);
          }
          finally
          {
             IoUtil.close(reader);
          }
      }
      else
      {
          result = null;
      }
      return result;
   }


   private void object2Xml (Object object, String name, String typeName,
         PrintWriter out)
         throws SQLException
   {
      if (object != null)
      {
         out.print("            <raw>");
         out.print(XmlUtil.escape(String.valueOf(object)));
         out.println("</raw>");
         final String display = objectFormater(object, name, typeName);
         if (display != null)
         {
            out.print("            <display>");
            out.print(XmlUtil.escape(display));
            out.println("</display>");
         }
      }
      else
      {
         out.println("            <raw/>");
      }
   }

   private String objectFormater (Object object, String name, String typeName)
         throws SQLException
   {
      String result = null;

      if (mTypeMapper.containsKey(name))
      {
         try
         {
            final TypeMapper mapper = (TypeMapper) mTypeMapper.get(name);
            result = mapper.toDisplay(object);
         }
         catch (IllegalArgumentException e)
         {
            result = "Failed to convert type '" + e.toString() + "'.";
            logger.log(Level.WARNING, result, e);
         }
         catch (IllegalAccessException e)
         {
            result = "Failed to convert type '" + e.toString() + "'.";
            logger.log(Level.WARNING, result, e);

         }
         catch (InvocationTargetException e)
         {
            result = "Failed to convert type '" + e.toString() + "'.";
            logger.log(Level.WARNING, result, e);
         }
         catch (RuntimeException e)
         {
            result = "Failed to convert type '" + e.toString() + "'.";
            logger.log(Level.WARNING, result, e);
         }
      }
      else if (object instanceof Date)
      {
         result = display((Date) object);
      }
      else if (object instanceof String)
      {
         result = display((String) object);
      }
      else
      {
         result = null;
      }
      return result;
   }


   private String display (Date d)
   {
      final String result;
      if (d == null)
      {
         result = null;
      }
      else
      {
         result = mDateFormater.format(d);
      }
      return result;
   }

   private String display (String str)
   {
      final String result;
      if (str == null)
      {
         result = null;
      }
      else if (str.indexOf('<') != -1)
      {
         final String detect = str.trim();
         if (detect.indexOf('<') == 0
               && detect.lastIndexOf('>') == (detect.length() - 1))
         {
            result = XmlUtil.formatXml(str);
         }
         else
         {
            result = null;
         }
      }
      else
      {
         result = null;
      }

      return result;
   }

   private static Date convertTimestamp (Timestamp ts)
   {
      final Date d;
      if (ts != null)
      {
         // ts.getTime does not return millis....
         d = new Date(((ts.getTime() / MILLIS_PER_SECOND) * MILLIS_PER_SECOND)
               + (ts.getNanos()
                   / org.jcoderz.commons.types.Date.NANOS_PER_MILLI));
      }
      else
      {
         d = null;
      }
      return d;
   }

   private static class TypeMapper
   {
      private final Class mTypeClass;
      private final Method mFromDb;
      private final Method mToString;
      private final String mTypeName;
      private final Class mInputType;

      public TypeMapper (String typeName, String typeClass, String fromDb,
            String toString)
            throws SecurityException, NoSuchMethodException,
               ClassNotFoundException
      {
         this(typeName, Class.forName(typeClass), fromDb, toString);
      }

      public TypeMapper (String typeName, Class typeClass, String fromDb,
            String toString)
            throws SecurityException, NoSuchMethodException
      {
         mTypeClass = typeClass;
         mToString = typeClass.getMethod(toString, null);
//         mFromDb = typeClass.getMethod(fromDb, new Class[] {Object.class});
         final Method [] methods = typeClass.getMethods();
         Method method = null;
         for (int i = 0; i < methods.length; i++)
         {
            if (methods[i].getReturnType() == mTypeClass
               &&  methods[i].getName().startsWith(fromDb)
               &&  ((methods[i].getModifiers() & Modifier.STATIC) != 0))
            {
               method = methods[i];
               break;
            }
         }

         mFromDb = method;
         mInputType = method.getParameterTypes()[0];
         mTypeName = typeName;
      }

      public String toDisplay (Object in)
            throws IllegalArgumentException, IllegalAccessException,
               InvocationTargetException
      {
         Object type = null;
         try
         {
            type = mFromDb.invoke(null, new Object[] {in});
         }
         catch (Exception ex)
         {
            if (in instanceof Number)
            {
               if (mInputType == Integer.TYPE)
               {
                  type = mFromDb.invoke(null,
                        new Object[]
                        {
                           new Integer(((Number) in).intValue())
                        });
               }
               else if (mInputType == Long.TYPE)
               {
                  type = mFromDb.invoke(null,
                        new Object[]
                        {
                           new Long(((Number) in).longValue())
                        });
               }
            }
            else
            {
               final IllegalArgumentException axe
                     = new IllegalArgumentException(
                        "Could not map type for object '" + String.valueOf(in)
                        + "' of class " + in.getClass().getName()
                        + " into expected converter "
                        + "type " + mInputType.getName() + ".");
               axe.initCause(ex);
               throw axe;
            }
         }
         return (String) mToString.invoke(type, null);
      }
   }

   public static String escapeTableName (String in)
   {
       return in.replaceAll("[/\\\\$]", "#");
   }
}