miningsqlsource.java

一个数据挖掘软件ALPHAMINERR的整个过程的JAVA版源代码

/*
 *    This program is free software; you can redistribute it and/or modify
 *    it under the terms of the GNU General Public License as published by
 *    the Free Software Foundation; either version 2 of the License, or
 *    (at your option) any later version.
 *
 *    This program is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *    GNU General Public License for more details.
 *
 *    You should have received a copy of the GNU General Public License
 *    along with this program; if not, write to the Free Software
 *    Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
 */

 /**
  * Title: XELOPES Data Mining Library
  * Description: The XELOPES library is an open platform-independent and data-source-independent library for Embedded Data Mining.
  * Copyright: Copyright (c) 2002 Prudential Systems Software GmbH
  * Company: ZSoft (www.zsoft.ru), Prudsys (www.prudsys.com)
  * @author Valentine Stepanenko ([e-mail])
  * @version 1.0
  */

package com.prudsys.pdm.Input.Relational;

import java.util.*;
import java.sql.*;

import org.omg.cwm.objectmodel.core.*;
import org.omg.cwm.resource.relational.*;

import com.prudsys.pdm.Core.*;

/**
 * Specification of JDBC address of database.
 */
public class MiningSqlSource
{
    // -----------------------------------------------------------------------
    //  Variables declarations
    // -----------------------------------------------------------------------
    /** Database JDBC URL. */
    protected String url;

    /** Database JDBC driver. */
    protected String driver;

    /** Database user name. */
    protected String user;

    /** Database user password. */
    protected String password;

    /** JDBC connection object. */
    protected Connection connection = null;

    /** Login not part of JDBC URL. */
    protected boolean useLogin = false;

    /** Tables of database. */
    private TableInfo[] tables;

    // -----------------------------------------------------------------------
    //  Constructors
    // -----------------------------------------------------------------------
    /**
     * Empty constructor.
     */
    public MiningSqlSource()
    {
    }

    /**
     * Creates MiningSQLSource from JDBC address.
     *
     * @param url database URL
     * @param user database user
     * @param password database password
     * @param driver database driver URL
     * @exception IllegalArgumentException wrong JDBC address
     */
    public MiningSqlSource( String url, String user, String password, String driver ) throws IllegalArgumentException
    {
        setUrl( url );
        setUser( user );
        setPassword( password );
        setDriver( driver );
        useLogin = true;
    }

    /**
     * Creates MiningSQLSource from JDBC URL and driver.
     *
     * @param url database URL
     * @param driver database driver URL
     * @exception IllegalArgumentException wrong URL
     */
    public MiningSqlSource( String url, String driver ) throws IllegalArgumentException
    {
        setUrl( url );
        setDriver( driver );
        useLogin = false;
    }

    // -----------------------------------------------------------------------
    //  Getter and setter methods
    // -----------------------------------------------------------------------
    /**
     * Returns URL of database.
     *
     * @return URL of database
     */
    public String getUrl()
    {
        return url;
    }

    /**
     * Sets URL of database.
     *
     * @param url new URL of database
     * @exception IllegalArgumentException URL string is null
     */
    public void setUrl(String url) throws IllegalArgumentException
    {
        if( url == null )
        {
            throw new IllegalArgumentException( "URL can't be null" );
        }
        this.url = url;
    }

    /**
     * Returns URL of JDBC driver.
     *
     * @return URL of JDBC driver
     */
    public String getDriver()
    {
        return driver;
    }

    /**
     * Sets URL of JDBC driver.
     *
     * @param driver new URL of JDBC driver
     * @exception IllegalArgumentException driver string is null
     */
    public void setDriver(String driver) throws IllegalArgumentException
    {
        if( driver == null )
        {
            throw new IllegalArgumentException( "Driver can't be null" );
        }
        this.driver = driver;
    }

    /**
     * Get username of database.
     *
     * @return user name
     */
    public String getUser()
    {
        return user;
    }

    /**
     * Sets user name of database.
     *
     * @param user new user name
     * @exception IllegalArgumentException user name is null
     */
    public void setUser(String user) throws IllegalArgumentException
    {
        if( user == null )
        {
            throw new IllegalArgumentException( "User can't be null" );
        }
        this.user = user;
        this.useLogin = true;
    }

    /**
     * Returns password of database.
     *
     * @return password
     */
    public String getPassword()
    {
        return password;
    }

    /**
     * Sets password of database.
     *
     * @param password new password
     * @exception IllegalArgumentException password is null
     */
    public void setPassword(String password) throws IllegalArgumentException
    {
        if( password == null )
        {
            throw new IllegalArgumentException( "Password can't be null" );
        }
        this.password = password;
        this.useLogin = true;
    }

    /**
     * Returns description of all tables. Better to use method
     * getPhysicalModel.
     *
     * @return description of all tables
     * @throws MiningException
     */
    public TableInfo[] getTables() throws MiningException {

      if(tables == null)
      {
        try
        {
          Connection conn = getConnection();
          DatabaseMetaData meta = conn.getMetaData();
          String[] types = new String[1];
          types[0] = "TABLE";
//        types[1] = "VIEW";
//        types[2] = "SYSTEM TABLE";
          ResultSet rs = meta.getTables(null,null,"%",types);
          Vector vt = new Vector();
          while(rs.next()) {
            String catalog = rs.getString(1);
            String schema = rs.getString(2);
            String name = rs.getString(3);
            String remarks = rs.getString(5);
            ResultSet rs2 = meta.getColumns(null,null,name,null);
            Vector vc = new Vector();
            while(rs2.next()) {
              ColumnInfo c = ColumnInfo.getColumnInfo(rs2);
              if(c == null) continue;
              vc.add(c);
              //System.out.println("Col: "+c.getName());
            }
            ColumnInfo[] ci = new ColumnInfo[vc.size()];
            for(int i=0;i<ci.length;i++)
              ci[i] = (ColumnInfo)vc.get(i);
            vt.add(new TableInfo(catalog,schema,name,TableInfo.TABLE,remarks,ci));
          }
          TableInfo[] ti = new TableInfo[vt.size()];
          for(int i=0;i<ti.length;i++)
            ti[i] = (TableInfo)vt.get(i);
          return ti;
        }
        catch (SQLException ex)
        {
          throw new MiningException("Failed to query database table structure:\n"+ex.getMessage());
        }
      }
      else return tables;
    }

    /**
     * Returns physical model of database.
     *
     * @return CWM Relational Catalog
     * @throws MiningException could not get physical model
     */
    public org.omg.cwm.objectmodel.core.Package getPhysicalModel()
      throws MiningException {

      // Get CWM package factories:
      com.prudsys.pdm.Cwm.CWMCompletePackage cwmFactory = com.prudsys.pdm.Cwm.CWMCompletePackage.getCWMCompletePackage();
      CorePackage corePkg = cwmFactory.getCore();
      RelationalPackage relPkg = cwmFactory.getRelational();

      try
      {
        // Get database connection:
        Connection conn = getConnection();
        DatabaseMetaData meta = conn.getMetaData();

        // Get SQL simple type info:
        Vector dtv = new Vector();
        ResultSet rs = meta.getTypeInfo();
        while ( rs.next() ) {
          SQLSimpleType type = relPkg.getSQLSimpleType().createSQLSimpleType();
          type.setName( rs.getString(1) );
          type.setTypeNumber( new Long( rs.getInt(2) ) );

          dtv.addElement(type);
        };
        rs.close();

        // Get catalog:
        String catName = conn.getCatalog();
        Catalog cat = relPkg.getCatalog().createCatalog();
        cat.setName(catName);

        // Get schemata of catalog:
        Vector sv = new Vector();
        rs = meta.getSchemas();
        while ( rs.next() ) {
          String schName = rs.getString(1);
          Schema sch = relPkg.getSchema().createSchema();
          sch.setName(schName);

          sv.addElement(sch);
        }
        rs.close();

        // Get schmata meta data:
        for (int i = 0; i < sv.size(); i++) {
          // Assign schema to catalog:
          Schema sch = (Schema) sv.elementAt(i);
          cat.addOwnedElement(sch);
          sch.setNamespace(cat);

          // Get tables of schema:
          Vector tiv = new Vector();
          String[] types = new String[1];
          types[0] = "TABLE";
          rs = meta.getTables(null, sch.getName(), null, types);
          while (rs.next()) {
            String tabName = rs.getString(3);

            Table tab = relPkg.getTable().createTable();
            tab.setName(tabName);

            // Export column meta data owned by the table:
            Vector cv = new Vector();
            ResultSet rs2 = meta.getColumns(null, null, tabName, null);
            while (rs2.next()) {
              Column col = relPkg.getColumn().createColumn();
              col.setName(rs2.getString(4));
              col.setLength(new Long(rs2.getInt(7)));
              int ctn = rs2.getInt(5);

              // Find type:
              for (int j = 0; j < dtv.size(); j++) {
                SQLSimpleType type = (SQLSimpleType) dtv.elementAt(j);
                if ( type.getTypeNumber().longValue() == ctn ) {
                  col.setType(type);
                  break;
                }
              }

              tab.addFeature(col);
              col.setOwner(tab);

              cv.addElement(col);
            }
            rs2.close();

            tiv.addElement(tab);
          };

          // here we can add keys and indexes to tiv ...

          // Assign tables data to schema:
          for (int j = 0; j < tiv.size(); j++) {
            ModelElement tabInd = (ModelElement) tiv.elementAt(j);
            sch.addOwnedElement(tabInd);
            tabInd.setNamespace(sch);
          }

        };
        rs.close();

        return cat;
      }
      catch (SQLException ex)
      {
          throw new MiningException("Failed to query database table structure:\n"+ex.getMessage());
      }
   }

   // -----------------------------------------------------------------------
   //  JDBC connection and execution methods
   // -----------------------------------------------------------------------
   /**
    * Establishes connection to database.
    *
    * @return connection handle to database
    * @exception MiningException could not establish connecetion
    */
   public Connection getConnection() throws MiningException
   {
       try
       {
           if( connection == null || connection.isClosed() )
           {
               java.lang.Class.forName( driver );

               if(!useLogin)
                 connection =  DriverManager.getConnection( url );
               else
                 connection =  DriverManager.getConnection( url, user, password );
           }
       }
       catch( ClassNotFoundException cnfe )
       {
           throw new MiningException( "Can't find database driver." );
       }
       catch( SQLException sqlexc )
       {
           throw new MiningException( "Can't open database connection:\n"+sqlexc.getMessage() );
       }
       return connection;
   }

   /**
    * Executes SQL statement.
    *
    * @param request SQL statement to execute
    * @return result set of execution of SQL statement
    * @exception MiningException could not execute query
    */
   public ResultSet executeQuery( String request ) throws MiningException
   {
       Statement statement = null;
       ResultSet resultSet = null;
       try
       {
           // Open connection:
           Connection conn = getConnection();

           // Determine cursor scrolling type:
           DatabaseMetaData dbmt = conn.getMetaData();
           int type = ResultSet.TYPE_SCROLL_INSENSITIVE;
           if ( !dbmt.supportsResultSetType(type) )
           {
             type = ResultSet.TYPE_FORWARD_ONLY;
           }

           // Execute statement:
           statement = conn.createStatement( type, ResultSet.CONCUR_READ_ONLY );
           try {
             resultSet = statement.executeQuery( request );
           }
           catch( SQLException sqle) {
             statement = conn.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY );
             resultSet = statement.executeQuery( request );
           }
       }
       catch ( SQLException sqle )
       {
           throw new MiningException( "Can't execute SQL statement:\n"+sqle.getMessage() );
       }
       return resultSet;
   }

}