databaseloader.java

矩阵的QR分解算法

  public String getUser(){
   
      return m_DataBaseConnection.getUsername();
  }
  
  /**
   * the tip text for this property
   * 
   * @return the tip text
   */
  public String userTipText(){
  
      return "The user name for the database";
  }
  
  /**
   * Sets user password for the database
   * 
   * @param password the password
   */
  public void setPassword(String password){
   
      m_Password = password;
      m_DataBaseConnection.setPassword(password);
  }

  /**
   * Returns the database password
   *
   * @return the database password
   */
  public String getPassword() {
    return m_DataBaseConnection.getPassword();
  }
  
  /**
   * the tip text for this property
   * 
   * @return the tip text
   */
  public String passwordTipText(){
  
      return "The database password";
  }
  
  
  /** 
   * Sets the database url, user and pw
   * 
   * @param url the database url
   * @param userName the user name
   * @param password the password
   */  
  public void setSource(String url, String userName, String password){
  
      try{
        m_DataBaseConnection = new DatabaseConnection();
        setUrl(url);
        setUser(userName);
        setPassword(password);
      } catch(Exception ex) {
            printException(ex);
      }    
  }
  
  /** 
   * Sets the database url
   * 
   * @param url the database url
   */  
  public void setSource(String url){
  
      try{
        m_DataBaseConnection = new DatabaseConnection();
        setUrl(url);
        m_User = m_DataBaseConnection.getUsername();
        m_Password = m_DataBaseConnection.getPassword();
      } catch(Exception ex) {
            printException(ex);
       }    
  }
  
  /** 
   * Sets the database url using the DatabaseUtils file
   * 
   * @throws Exception if something goes wrong
   */  
  public void setSource() throws Exception{
  
        m_DataBaseConnection = new DatabaseConnection();
        m_URL = m_DataBaseConnection.getDatabaseURL();
        m_User = m_DataBaseConnection.getUsername();
        m_Password = m_DataBaseConnection.getPassword();
  }
  
  /**
   * Opens a connection to the database
   */
  public void connectToDatabase() {
   
      try{
        if(!m_DataBaseConnection.isConnected()){
            m_DataBaseConnection.connectToDatabase();
        }
      } catch(Exception ex) {
	printException(ex);
       }    
  }
  
  
  /** 
   * Returns the table name or all after the FROM clause of the user specified query
   * to retrieve instances.
   * 
   * @param onlyTableName true if only the table name should be returned, false otherwise
   * @return the end of the query
   */  
  private String endOfQuery(boolean onlyTableName){
      String table;
      int beginIndex, endIndex;
      
      beginIndex = m_query.indexOf("FROM ")+5;
      while(m_query.charAt(beginIndex) == ' ')
          beginIndex++;
      endIndex = m_query.indexOf(" ",beginIndex);
      if(endIndex != -1 && onlyTableName)
          table = m_query.substring(beginIndex,endIndex);
      else
          table = m_query.substring(beginIndex);
      if(m_DataBaseConnection.getUpperCase())
          table = table.toUpperCase();
      return table;
  }
  
  /** 
   * Checks for a unique key using the JDBC driver's method:
   * getPrimaryKey(), getBestRowIdentifier().
   * Depending on their implementation a key can be detected.
   * The key is needed to order the instances uniquely for an inremental loading.
   * If an existing key cannot be detected, use -P option.
   * 
   * @throws Exception if database error occurs
   * @return true, if a key could have been detected, false otherwise
   */  
  private boolean checkForKey() throws Exception {
  
      String query = m_query;
      
      query = query.replaceAll(" +"," ");
      //query has to use all columns
      if(!query.startsWith("SELECT *"))
          return false;
      m_orderBy.removeAllElements();
      if(!m_DataBaseConnection.isConnected())
            m_DataBaseConnection.connectToDatabase();
      DatabaseMetaData dmd = m_DataBaseConnection.getMetaData();
      String table = endOfQuery(true);
      //System.out.println(table);
      //check for primary keys
      ResultSet rs = dmd.getPrimaryKeys(null,null,table);
      while(rs.next()){
          m_orderBy.addElement(rs.getString(4));
      }
      rs.close();
      if(m_orderBy.size() != 0)
          return true;
      //check for unique keys
      rs = dmd.getBestRowIdentifier(null,null,table,DatabaseMetaData.bestRowSession,false);
      ResultSetMetaData rmd = rs.getMetaData();
      int help = 0;
      while(rs.next()){
          m_orderBy.addElement(rs.getString(2));
          help++;
      }
      rs.close();
      if(help == rmd.getColumnCount()){
          m_orderBy.removeAllElements();
      }
      if(m_orderBy.size() != 0)
          return true;
      
      return false;
  }
  
  /** 
   * Converts string attribute into nominal ones for an instance read during
   * incremental loading
   * 
   * @param rs The result set
   * @param i the index of the nominal value
   * @throws Exception exception if it cannot be converted
   */  
  private void stringToNominal(ResultSet rs, int i) throws Exception{
  
      while(rs.next()){
        String str = rs.getString(1);
        if(!rs.wasNull()){
            Double index = (Double)m_nominalIndexes[i - 1].get(str);
            if (index == null) {
                index = new Double(m_nominalStrings[i - 1].size());
                m_nominalIndexes[i - 1].put(str, index);
                m_nominalStrings[i - 1].addElement(str);
            }
        }
      }
  }
  
  /** 
   * Used in incremental loading. Modifies the SQL statement,
   * so that only one instance per time is tretieved and the instances are ordered
   * uniquely.
   * 
   * @param query the query to modify for incremental loading
   * @param offset sets which tuple out of the uniquely ordered ones should be returned
   * @param choice the kind of query that is suitable for the used DBMS
   * @return the modified query that returns only one result tuple.
   */  
  private String limitQuery(String query, int offset, int choice){
  
      String limitedQuery;
      StringBuffer order = new StringBuffer();
      String orderByString = "";
      
      if(m_orderBy.size() != 0){
        order.append(" ORDER BY ");
        for(int i = 0; i < m_orderBy.size()-1; i++){
            if(m_DataBaseConnection.getUpperCase())
                order.append(((String)m_orderBy.elementAt(i)).toUpperCase());
            else
                order.append((String)m_orderBy.elementAt(i));
            order.append(", ");
        }
        if(m_DataBaseConnection.getUpperCase())
            order.append(((String)m_orderBy.elementAt(m_orderBy.size()-1)).toUpperCase());
        else
            order.append((String)m_orderBy.elementAt(m_orderBy.size()-1));
        orderByString = order.toString();
      }
      if(choice == 0){
          limitedQuery = query.replaceFirst("SELECT","SELECT LIMIT "+offset+" 1");
          limitedQuery = limitedQuery.concat(orderByString);
          return limitedQuery;
      }
      if(choice == 1){
          limitedQuery = query.concat(orderByString+" LIMIT 1 OFFSET "+offset);
          return limitedQuery;
      }
      limitedQuery = query.concat(orderByString+" LIMIT "+offset+", 1");
      //System.out.println(limitedQuery);
      return limitedQuery;
  }
  
  /** 
   * Counts the number of rows that are loaded from the database
   * 
   * @throws Exception if the number of rows cannot be calculated
   * @return the entire number of rows
   */  
  private int getRowCount() throws Exception{
  
    String query = "SELECT COUNT(*) FROM "+endOfQuery(false);
    if(m_DataBaseConnection.execute(query) == false) {
        throw new Exception("Cannot count results tuples.");
    }
    ResultSet rs = m_DataBaseConnection.getResultSet();
    rs.next();
    int i = rs.getInt(1);
    rs.close();
    return i;
  }


  /**
   * Determines and returns (if possible) the structure (internally the 
   * header) of the data set as an empty set of instances.
   *
   * @return the structure of the data set as an empty set of Instances
   * @throws IOException if an error occurs
   */
  public Instances getStructure() throws IOException {

    if (m_DataBaseConnection == null) {
      throw new IOException("No source database has been specified");
    }
    connectToDatabase();
  pseudo:
      try{
    if(m_pseudoIncremental && m_structure == null){
        if (getRetrieval() == BATCH) {
            throw new IOException("Cannot mix getting instances in both incremental and batch modes");
        }
        setRetrieval(NONE);  
        m_datasetPseudoInc = getDataSet();
        m_structure = new Instances(m_datasetPseudoInc,0);
        setRetrieval(NONE);
        return m_structure;
    }
    if (m_structure == null) {
      if(m_checkForTable) {
        if(!m_DataBaseConnection.tableExists(endOfQuery(true)))
          throw new IOException(
              "Table does not exist according to metadata from JDBC driver. "
              + "If you are convinced the table exists, set 'checkForTable' "
              + "to 'False' in your DatabaseUtils.props file and try again.");
      }
        //finds out which SQL statement to use for the DBMS to limit the number of resulting rows to one
        int choice = 0;
        boolean rightChoice = false;
        while (!rightChoice){
            try{
                if (m_DataBaseConnection.execute(limitQuery(m_query,0,choice)) == false) {
                    throw new IOException("Query didn't produce results");
                }
                m_choice = choice;
                rightChoice = true;
            }
            catch (SQLException ex) {
                choice++;
                if(choice == 3){
                    System.out.println("Incremental loading not supported for that DBMS. Pseudoincremental mode is used if you use incremental loading.\nAll rows are loaded into memory once and retrieved incrementally from memory instead of from the database.");
                    m_pseudoIncremental = true;
                    break pseudo;
                }
            }
        }
        String end = endOfQuery(false);
        ResultSet rs = m_DataBaseConnection.getResultSet();
        ResultSetMetaData md = rs.getMetaData();
        rs.close();
        int numAttributes = md.getColumnCount();
        int [] attributeTypes = new int [numAttributes];
        m_nominalIndexes = new Hashtable [numAttributes];
        m_nominalStrings = new FastVector [numAttributes];
        for (int i = 1; i <= numAttributes; i++) {
            switch (m_DataBaseConnection.translateDBColumnType(md.getColumnTypeName(i))) {
                case DatabaseConnection.STRING :
                    //System.err.println("String --> nominal");
                    ResultSet rs1;
                    String columnName = md.getColumnName(i);
                    if(m_DataBaseConnection.getUpperCase())
                        columnName = columnName.toUpperCase();
                    m_nominalIndexes[i - 1] = new Hashtable();
                    m_nominalStrings[i - 1] = new FastVector();
                    String query = "SELECT COUNT(DISTINCT( "+columnName+" )) FROM " + end;
                    if (m_DataBaseConnection.execute(query) == true){
                        rs1 = m_DataBaseConnection.getResultSet();
                        rs1.next();
                        int count = rs1.getInt(1);
                        rs1.close();
                        if(count > m_nominalToStringLimit || m_DataBaseConnection.execute("SELECT DISTINCT ( "+columnName+" ) FROM "+ end) == false){
                            attributeTypes[i - 1] = Attribute.STRING;
                            break;
                        }
                        rs1 = m_DataBaseConnection.getResultSet();
                    }
                    else{
                        //System.err.println("Count for nominal values cannot be calculated. Attribute "+columnName+" treated as String.");
                        attributeTypes[i - 1] = Attribute.STRING;
                        break;
                    }
                    attributeTypes[i - 1] = Attribute.NOMINAL;
                    stringToNominal(rs1,i);
                    rs1.close();
                    break;
                case DatabaseConnection.TEXT:
                    //System.err.println("boolean --> string");
                    columnName = md.getColumnName(i);
                    if(m_DataBaseConnection.getUpperCase())
                      columnName = columnName.toUpperCase();
                    m_nominalIndexes[i - 1] = new Hashtable();
                    m_nominalStrings[i - 1] = new FastVector();
                    query = "SELECT COUNT(DISTINCT( "+columnName+" )) FROM " + end;
                    if (m_DataBaseConnection.execute(query) == true){
                      rs1 = m_DataBaseConnection.getResultSet();
                      stringToNominal(rs1,i);
                      rs1.close();
                    }
                    attributeTypes[i - 1] = Attribute.STRING;
                    break;
                case DatabaseConnection.BOOL:
                    //System.err.println("boolean --> nominal");
                    attributeTypes[i - 1] = Attribute.NOMINAL;
                    m_nominalIndexes[i - 1] = new Hashtable();
                    m_nominalIndexes[i - 1].put("false", new Double(0));
                    m_nominalIndexes[i - 1].put("true", new Double(1));
                    m_nominalStrings[i - 1] = new FastVector();
                    m_nominalStrings[i - 1].addElement("false");
                    m_nominalStrings[i - 1].addElement("true");
                    break;
                case DatabaseConnection.DOUBLE:
                    //System.err.println("BigDecimal --> numeric");
                    attributeTypes[i - 1] = Attribute.NUMERIC;
                    break;
                case DatabaseConnection.BYTE: