package org.apache.ojb.broker.metadata;

 /* Copyright 2002-2005 The Apache Software Foundation

  *

  * Licensed under the Apache License, Version 2.0 (the "License");

  * you may not use this file except in compliance with the License.

  * You may obtain a copy of the License at

  *

  *     http://www.apache.org/licenses/LICENSE-2.0

  *

  * Unless required by applicable law or agreed to in writing, software

  * distributed under the License is distributed on an "AS IS" BASIS,

  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

  * See the License for the specific language governing permissions and

  * limitations under the License.

  */

 import java.sql.Connection;

 import java.sql.DatabaseMetaData;

 import java.sql.SQLException;

 import java.util.HashMap;

 import javax.sql.DataSource;

 import org.apache.commons.beanutils.PropertyUtils;

 /**

  * This class provides some utility functions to OJB for working with JDBC metadata.

  * 

  * @author <a href="mailto:tomdz@apache.org">Thomas Dudziak</a>

  */

 public class JdbcMetadataUtils

 {

     /** The name of the property returned by the {@link #splitConnectionUrl(String)} method

         that contains the protocol */ 

     public static final String PROPERTY_PROTOCOL    = "protocol";

     /** The name of the property returned by the {@link #splitConnectionUrl(String)} method

         that contains the sub protocol */ 

     public static final String PROPERTY_SUBPROTOCOL = "subprotocol";

     /** The name of the property returned by the {@link #splitConnectionUrl(String)} method

         that contains the database alias (the actual database url) */ 

     public static final String PROPERTY_DBALIAS     = "dbAlias";

     /** Identifier for the DB2 platform */

     public static final String PLATFORM_DB2         = "Db2";

     /** Identifier for the Firebird platform */

     public static final String PLATFORM_FIREBIRD    = "Firebird";

     /** Identifier for the Hsqldb platform */

     public static final String PLATFORM_HSQLDB      = "Hsqldb";

     /** Identifier for the Informix platform */

     public static final String PLATFORM_INFORMIX    = "Informix";

     /** Identifier for the MaxDB platform */

     public static final String PLATFORM_MAXDB       = "MaxDB";

     /** Identifier for the McKoi platform */

     public static final String PLATFORM_MCKOI       = "McKoi";

     /** Identifier for the MsAccess platform */

     public static final String PLATFORM_MSACCESS    = "MsAccess";

     /** Identifier for the Microsoft SQL Server platform */

     public static final String PLATFORM_MSSQLSERVER = "MsSQLServer";

     /** Identifier for the MySQL platform */

     public static final String PLATFORM_MYSQL       = "MySQL";

     /** Identifier for the generic Oracle platform */

     public static final String PLATFORM_ORACLE      = "Oracle";

     /** Identifier for the Oracle9i platform */

     public static final String PLATFORM_ORACLE9I    = "Oracle9i";

     /** Identifier for the PostgresSQL platform */

     public static final String PLATFORM_POSTGRESQL  = "PostgreSQL";

     /** Identifier for the generic Sybase platform */

     public static final String PLATFORM_SYBASE      = "Sybase";

     /** Identifier for the Sybase ASA platform */

     public static final String PLATFORM_SYBASEASA   = "SybaseASA";

     /** Identifier for the Sybase ASE platform */

     public static final String PLATFORM_SYBASEASE   = "SybaseASE";

     /** Identifier for the Oracle9i for WebLogic platform */

     public static final String PLATFORM_WLORACLE9I  = "WLOracle9i";

     /** The standard DB2 jdbc driver */

     public static final String DRIVER_DB2                     = "COM.ibm.db2.jdbc.app.DB2Driver";

     /** The i-net DB2 jdbc driver */

     public static final String DRIVER_DB2_INET                = "com.inet.drda.DRDADriver";

     /** The standard Firebird jdbc driver */

     public static final String DRIVER_FIREBIRD                = "org.firebirdsql.jdbc.FBDriver";

     /** The standard Hsqldb jdbc driver */

     public static final String DRIVER_HSQLDB                  = "org.hsqldb.jdbcDriver";

     /** The i-net pooled jdbc driver for SQLServer and Sybase */

     public static final String DRIVER_INET_POOLED             = "com.inet.pool.PoolDriver";

     /** The standard Informix jdbc driver */

     public static final String DRIVER_INFORMIX                = "com.informix.jdbc.IfxDriver";

     /** The jTDS jdbc driver for SQLServer and Sybase */

     public static final String DRIVER_JTDS                    = "net.sourceforge.jtds.jdbc.Driver";

     /** The standard MaxDB jdbc driver */

     public static final String DRIVER_MAXDB                   = "com.sap.dbtech.jdbc.DriverSapDB";

     /** The standard McKoi jdbc driver */

     public static final String DRIVER_MCKOI                   = "com.mckoi.JDBCDriver";

     /** The standard SQLServer jdbc driver */

     public static final String DRIVER_MSSQLSERVER             = "com.microsoft.jdbc.sqlserver.SQLServerDriver";

     /** The i-net SQLServer jdbc driver */

     public static final String DRIVER_MSSQLSERVER_INET        = "com.inet.tds.TdsDriver";

     /** The JNetDirect SQLServer jdbc driver */

     public static final String DRIVER_MSSQLSERVER_JSQLCONNECT = "com.jnetdirect.jsql.JSQLDriver";

     /** The standard MySQL jdbc driver */

     public static final String DRIVER_MYSQL                   = "com.mysql.jdbc.Driver";

     /** The old MySQL jdbc driver */

     public static final String DRIVER_MYSQL_OLD               = "org.gjt.mm.mysql.Driver";

     /** The standard Oracle jdbc driver */

     public static final String DRIVER_ORACLE                  = "oracle.jdbc.driver.OracleDriver";

     /** The i-net Oracle jdbc driver */

     public static final String DRIVER_ORACLE_INET             = "com.inet.ora.OraDriver";

     /** The standard PostgreSQL jdbc driver */

     public static final String DRIVER_POSTGRESQL              = "org.postgresql.Driver";

     /** The standard Sapdb jdbc driver */

     public static final String DRIVER_SAPDB                   = DRIVER_MAXDB;

     /** The standard Sybase jdbc driver */

     public static final String DRIVER_SYBASE                  = "com.sybase.jdbc2.jdbc.SybDriver";

     /** The old Sybase jdbc driver */

     public static final String DRIVER_SYBASE_OLD              = "com.sybase.jdbc.SybDriver";

     /** The i-net Sybase jdbc driver */

     public static final String DRIVER_SYBASE_INET             = "com.inet.syb.SybDriver";

     /** The subprotocol used by the standard DB2 driver */

     public static final String SUBPROTOCOL_DB2                       = "db2";

     /** The subprotocol used by the i-net DB2 driver */

     public static final String SUBPROTOCOL_DB2_INET                  = "inetdb2";

     /** The subprotocol used by the standard Firebird driver */

     public static final String SUBPROTOCOL_FIREBIRD                  = "firebirdsql";

     /** The subprotocol used by the standard Hsqldb driver */

     public static final String SUBPROTOCOL_HSQLDB                    = "hsqldb";

     /** The subprotocol used by the standard Informix driver */

     public static final String SUBPROTOCOL_INFORMIX                  = "informix-sqli";

     /** The subprotocol used by the standard MaxDB driver */

     public static final String SUBPROTOCOL_MAXDB                     = "sapdb";

     /** The subprotocol used by the standard McKoi driver */

     public static final String SUBPROTOCOL_MCKOI                     = "mckoi";

     /** The subprotocol used by the standard SQLServer driver */

     public static final String SUBPROTOCOL_MSSQLSERVER               = "microsoft:sqlserver";

     /** A subprotocol used by the i-net SQLServer driver */

     public static final String SUBPROTOCOL_MSSQLSERVER_INET          = "inetdae";

     /** A subprotocol used by the i-net SQLServer driver */

     public static final String SUBPROTOCOL_MSSQLSERVER6_INET         = "inetdae6";

     /** A subprotocol used by the i-net SQLServer driver */

     public static final String SUBPROTOCOL_MSSQLSERVER7_INET         = "inetdae7";

     /** A subprotocol used by the i-net SQLServer driver */

     public static final String SUBPROTOCOL_MSSQLSERVER7A_INET        = "inetdae7a";

     /** A subprotocol used by the pooled i-net SQLServer driver */

     public static final String SUBPROTOCOL_MSSQLSERVER_INET_POOLED   = "inetpool:inetdae";

     /** A subprotocol used by the pooled i-net SQLServer driver */

     public static final String SUBPROTOCOL_MSSQLSERVER6_INET_POOLED  = "inetpool:inetdae6";

     /** A subprotocol used by the pooled i-net SQLServer driver */

     public static final String SUBPROTOCOL_MSSQLSERVER7_INET_POOLED  = "inetpool:inetdae7";

     /** A subprotocol used by the pooled i-net SQLServer driver */

     public static final String SUBPROTOCOL_MSSQLSERVER7A_INET_POOLED = "inetpool:inetdae7a";

     /** The subprotocol used by the JNetDirect SQLServer driver */

     public static final String SUBPROTOCOL_MSSQLSERVER_JSQLCONNECT   = "JSQLConnect";

     /** The subprotocol used by the jTDS SQLServer driver */

     public static final String SUBPROTOCOL_MSSQLSERVER_JTDS          = "jtds:sqlserver";

     /** The subprotocol used by the standard MySQL driver */

     public static final String SUBPROTOCOL_MYSQL                     = "mysql";

     /** The subprotocol used by the standard Oracle driver */

     public static final String SUBPROTOCOL_ORACLE                    = "oracle";

     /** The subprotocol used by the i-net Oracle driver */

     public static final String SUBPROTOCOL_ORACLE_INET               = "inetora";

     /** The subprotocol used by the standard PostgreSQL driver */

     public static final String SUBPROTOCOL_POSTGRESQL                = "postgresql";

     /** The subprotocol used by the standard Sapdb driver */

     public static final String SUBPROTOCOL_SAPDB                     = SUBPROTOCOL_MAXDB;

     /** The subprotocol used by the standard Sybase driver */

     public static final String SUBPROTOCOL_SYBASE                    = "sybase:Tds";

     /** The subprotocol used by the i-net Sybase driver */

     public static final String SUBPROTOCOL_SYBASE_INET               = "inetsyb";

     /** The subprotocol used by the pooled i-net Sybase driver */

     public static final String SUBPROTOCOL_SYBASE_INET_POOLED        = "inetpool:inetsyb";

     /** The subprotocol used by the jTDS Sybase driver */

     public static final String SUBPROTOCOL_SYBASE_JTDS               = "jtds:sybase";

     /** Maps the sub-protocl part of a jdbc connection url to a OJB platform name */

     private HashMap jdbcSubProtocolToPlatform = new HashMap();

     /** Maps the jdbc driver name to a OJB platform name */

     private HashMap jdbcDriverToPlatform      = new HashMap();

     /**

      * Creates a new <code>JdbcMetadataUtils</code> object.

      */

     public JdbcMetadataUtils()

     {

         // Note that currently Sapdb and MaxDB have equal subprotocols and

         // drivers so we have no means to distinguish them

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_DB2,                       PLATFORM_DB2);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_DB2_INET,                  PLATFORM_DB2);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_FIREBIRD,                  PLATFORM_FIREBIRD);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_HSQLDB,                    PLATFORM_HSQLDB);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_INFORMIX,                  PLATFORM_INFORMIX);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MAXDB,                     PLATFORM_MAXDB);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER,               PLATFORM_MSSQLSERVER);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER_INET,          PLATFORM_MSSQLSERVER);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER6_INET,         PLATFORM_MSSQLSERVER);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER7_INET,         PLATFORM_MSSQLSERVER);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER7A_INET,        PLATFORM_MSSQLSERVER);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER_INET_POOLED,   PLATFORM_MSSQLSERVER);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER6_INET_POOLED,  PLATFORM_MSSQLSERVER);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER7_INET_POOLED,  PLATFORM_MSSQLSERVER);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER7A_INET_POOLED, PLATFORM_MSSQLSERVER);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER_JTDS,          PLATFORM_MSSQLSERVER);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MYSQL,                     PLATFORM_MYSQL);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_ORACLE,                    PLATFORM_ORACLE);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_ORACLE_INET,               PLATFORM_ORACLE);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_POSTGRESQL,                PLATFORM_POSTGRESQL);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_SYBASE,                    PLATFORM_SYBASE);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_SYBASE_INET,               PLATFORM_SYBASE);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_SYBASE_INET_POOLED,        PLATFORM_SYBASE);

         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_SYBASE_JTDS,               PLATFORM_SYBASE);

         jdbcDriverToPlatform.put(DRIVER_DB2,                     PLATFORM_DB2);

         jdbcDriverToPlatform.put(DRIVER_DB2_INET,                PLATFORM_DB2);

         jdbcDriverToPlatform.put(DRIVER_FIREBIRD,                PLATFORM_FIREBIRD);

         jdbcDriverToPlatform.put(DRIVER_HSQLDB,                  PLATFORM_HSQLDB);

         jdbcDriverToPlatform.put(DRIVER_INFORMIX,                PLATFORM_INFORMIX);

         jdbcDriverToPlatform.put(DRIVER_MAXDB,                   PLATFORM_MAXDB);

         jdbcDriverToPlatform.put(DRIVER_MCKOI,                   PLATFORM_MCKOI);

         jdbcDriverToPlatform.put(DRIVER_MSSQLSERVER,             PLATFORM_MSSQLSERVER);

         jdbcDriverToPlatform.put(DRIVER_MSSQLSERVER_INET,        PLATFORM_MSSQLSERVER);

         jdbcDriverToPlatform.put(DRIVER_MSSQLSERVER_JSQLCONNECT, PLATFORM_MSSQLSERVER);

         jdbcDriverToPlatform.put(DRIVER_MYSQL,                   PLATFORM_MYSQL);

         jdbcDriverToPlatform.put(DRIVER_MYSQL_OLD,               PLATFORM_MYSQL);

         jdbcDriverToPlatform.put(DRIVER_ORACLE,                  PLATFORM_ORACLE);

         jdbcDriverToPlatform.put(DRIVER_ORACLE_INET,             PLATFORM_ORACLE);

         jdbcDriverToPlatform.put(DRIVER_POSTGRESQL,              PLATFORM_POSTGRESQL);

         jdbcDriverToPlatform.put(DRIVER_SYBASE,                  PLATFORM_SYBASE);

         jdbcDriverToPlatform.put(DRIVER_SYBASE_OLD,              PLATFORM_SYBASE);

         jdbcDriverToPlatform.put(DRIVER_SYBASE_INET,             PLATFORM_SYBASE);

     }

     /**

      * Fills parameters of the given {@link JdbcConnectionDescriptor} with metadata

      * extracted from the given datasource.

      * 

      * @param jcd        The jdbc connection descriptor to fill

      * @param dataSource The data source

      * @param username   The username required to establish a connection via the data source

      *                   Can be empty if the data source does not require it or if one

      *                   is specified in the jdbc connection descriptor

      * @param password   The username required to establish a connection via the data source

      *                   Can be empty if the data source or username does not require it or if one

      *                   is specified in the jdbc connection descriptor

      */

     public void fillJCDFromDataSource(JdbcConnectionDescriptor jcd, DataSource dataSource, String username, String password) throws MetadataException

     {

         String           realUsername = (jcd.getUserName() != null ? jcd.getUserName() : username);

         String           realPassword = (jcd.getPassWord() != null ? jcd.getPassWord() : password);

         Connection       connection   = null;

         DatabaseMetaData metadata     = null;

         try

         {

             // we have to open a connection to be able to retrieve metadata

             if (realUsername != null)

             {

                 connection = dataSource.getConnection(realUsername, realPassword);

             }

             else

             {

                 connection = dataSource.getConnection();

             }

             metadata = connection.getMetaData();

         }

         catch (Throwable t)

         {

             if (connection != null)

             {

                 try

                 {

                     connection.close();

                 }

                 catch (SQLException ex)

                 {}

             }

             throw new MetadataException("Could not get the metadata from the given datasource", t);

         }

         try

         {

             HashMap urlComponents = parseConnectionUrl(metadata.getURL());

             if (urlComponents.containsKey(PROPERTY_DBALIAS))

             {

                 jcd.setProtocol((String)urlComponents.get(PROPERTY_PROTOCOL));

                 jcd.setSubProtocol((String)urlComponents.get(PROPERTY_SUBPROTOCOL));

                 jcd.setDbAlias((String)urlComponents.get(PROPERTY_DBALIAS));

                 if (jdbcSubProtocolToPlatform.containsKey(jcd.getSubProtocol()))

                 {

                     // TODO: We might be able to use this: metadata.getDatabaseProductName();

                     jcd.setDbms((String)jdbcSubProtocolToPlatform.get(jcd.getSubProtocol()));

                 }

             }

         }

         catch (Throwable t)

         {

             try

             {

                 connection.close();

             }

             catch (SQLException ex)

             {}

             throw new MetadataException("Could not get the metadata from the given datasource", t);

         }

         try

         {

             // this will only work with JDK >= 1.4 and only with some jdbc drivers

             Integer majorVersion = (Integer)PropertyUtils.getProperty(metadata, "JDBCMajorVersion");

             Integer minorVersion = (Integer)PropertyUtils.getProperty(metadata, "JDBCMinorVersion");

             jcd.setJdbcLevel(Double.parseDouble(majorVersion.toString()+"."+minorVersion.toString()));

         }

         catch (Throwable t)

         {

             // otherwise we're assuming JDBC 2.0 compliance

             jcd.setJdbcLevel(2.0);

         }

         try

         {

             connection.close();

         }

         catch (SQLException ex)

         {}

     }

     /**

      * Splits the given jdbc connection url into its components and puts them into

      * a hash map using the <code>PROPERTY_</code> constants.

      * 

      * @param jdbcConnectionUrl The connection url

      * @return The properties

      */

     public HashMap parseConnectionUrl(String jdbcConnectionUrl)

     {

         HashMap result = new HashMap();

         if (jdbcConnectionUrl == null)

         {

             return result;

         }

         int pos = jdbcConnectionUrl.indexOf(':');

         int lastPos;

         result.put(PROPERTY_PROTOCOL, jdbcConnectionUrl.substring(0, pos));

         lastPos = pos;

         pos     = jdbcConnectionUrl.indexOf(':', lastPos + 1);

         String subProtocol = jdbcConnectionUrl.substring(lastPos + 1, pos);

         // there are a few jdbc drivers that have a subprotocol containing one or more ':'

         if ("inetpool".equals(subProtocol))

         {

             // Possible forms are:

             //   inetpool:<subprotocol>

             //   inetpool:jdbc:<subprotocol>   (where we'll remove the 'jdbc' part)

             int tmpPos = jdbcConnectionUrl.indexOf(':', pos + 1);

             if ("inetpool:jdbc".equals(jdbcConnectionUrl.substring(lastPos + 1, tmpPos)))

             {

                 pos    = tmpPos;

                 tmpPos = jdbcConnectionUrl.indexOf(':', pos + 1);

             }

             subProtocol += ":" + jdbcConnectionUrl.substring(pos + 1, tmpPos);

         }

         else if ("jtds".equals(subProtocol) ||

                  "microsoft".equals(subProtocol) ||

                  "sybase".equals(subProtocol))

         {

             pos         = jdbcConnectionUrl.indexOf(':', pos + 1);

             subProtocol = ":" + jdbcConnectionUrl.substring(lastPos + 1, pos);

         }

         result.put(PROPERTY_SUBPROTOCOL, subProtocol);

         result.put(PROPERTY_DBALIAS, jdbcConnectionUrl.substring(pos + 1));

         return result;

     }

     /**

      * Derives the OJB platform to use for a database that is connected via a url using the specified

      * subprotocol, and where the specified jdbc driver is used.

      * 

      * @param jdbcSubProtocol The JDBC subprotocol used to connect to the database

      * @param jdbcDriver      The JDBC driver used to connect to the database

      * @return The platform identifier or <code>null</code> if no platform could be found

      */

     public String findPlatformFor(String jdbcSubProtocol, String jdbcDriver)

     {

         String platform = (String)jdbcSubProtocolToPlatform.get(jdbcSubProtocol);

         if (platform == null)

         {

             platform = (String)jdbcDriverToPlatform.get(jdbcDriver);

         }

         return platform;

     }

 }