sqldatabase.java

dump3 morpheus 0.2.9 src

/**
 * DuMP3 version morpheus_0.2.9 - a duplicate/similar file finder in Java<BR>
 * Copyright 2005 Alexander Gr&auml;sser<BR>
 * All Rights Reserved, http://dump3.sourceforge.net/<BR>
 * <BR>
 * This file is part of DuMP3.<BR>
 * <BR>
 * DuMP3 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.<BR>
 * <BR>
 * DuMP3 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.<BR>
 * <BR>
 * You should have received a copy of the GNU General Public License along with DuMP3; if not, write to the Free Software Foundation, Inc., 51 Franklin St,
 * Fifth Floor, Boston, MA 02110-1301 USA
 */
package net.za.grasser.duplicate.persist;

import java.io.IOException;
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.Types;
import java.util.HashMap;
import java.util.Map;
import net.za.grasser.duplicate.Configure;
import net.za.grasser.duplicate.file.FingerprintFile;
import net.za.grasser.duplicate.file.Status;
import net.za.grasser.duplicate.fingerprint.AbstractFingerprint;
import net.za.grasser.duplicate.util.HexArray;
import org.apache.log4j.Logger;
import sun.misc.BASE64Decoder;
import sun.misc.BASE64Encoder;

/**
 * This class ...
 * 
 * @author <a href="http://sourceforge.net/sendmessage.php?touser=733840">pyropunk at sourceforge dot net</a>
 * @version $Revision: 1.8 $
 * @modelguid {4CB6B643-581D-4686-8E2B-829C8B83867A}
 */
public class SQLDatabase implements Database {
  /**
   * <code>conn</code> SQLDatabase - Database connection
   */
  static Connection conn = null;
  /**
   * <code>log</code> SQLDatabase - Logger
   */
  static Logger log = Logger.getLogger(SQLDatabase.class);
  /**
   * <code>fingerprinttypes</code> SQLDatabase -
   */
  static HashMap<String, Integer> fingerprintTypes = null;
  /**
   * <code>infoTypes</code> SQLDatabase -
   */
  static HashMap<String, Integer> infoTypes = null;
  /**
   * <code>cls</code> SQLDatabase -
   */
  static String cls;
  /**
   * <code>url</code> SQLDatabase -
   */
  static String url;
  /**
   * <code>user</code> SQLDatabase -
   */
  static String user;
  /**
   * <code>pass</code> SQLDatabase -
   */
  static String pass;
  /**
   * <code>printColumn</code> SQLDatabase -
   */
  static String printColumn = null;
  /**
   * <code>stmtGetFingerprint</code> SQLDatabase -
   */
  static PreparedStatement stmtGetFingerprint = null;
  /**
   * <code>stmtGetStatus</code> SQLDatabase -
   */
  static PreparedStatement stmtGetStatus = null;
  /**
   * <code>stmtGetInfo</code> SQLDatabase -
   */
  static PreparedStatement stmtGetInfo = null;
  /**
   * <code>stmtInsertFilename</code> SQLDatabase -
   */
  static PreparedStatement stmtInsertFilename = null;
  /**
   * <code>stmtInsertInfo</code> SQLDatabase -
   */
  static PreparedStatement stmtInsertInfo = null;
  /**
   * <code>stmtInsertInfoType</code> SQLDatabase -
   */
  static PreparedStatement stmtInsertInfoType = null;
  /**
   * <code>stmtUpdateStatus</code> SQLDatabase -
   */
  static PreparedStatement stmtUpdateStatus = null;
  /**
   * <code>stmtGetFileId</code> SQLDatabase -
   */
  static PreparedStatement stmtGetFileId = null;
  /**
   * <code>stmtGetFileTypeId</code> SQLDatabase -
   */
  static PreparedStatement stmtGetFileTypeId = null;
  /**
   * <code>stmtInsertFingerprint</code> SQLDatabase -
   */
  static PreparedStatement stmtInsertFingerprint = null;

  /**
   * This class is a helper to get the file id and status from the db simultaneously.
   * 
   * @author <a href="http://sourceforge.net/sendmessage.php?touser=733840">pyropunk at sourceforge dot net</a>
   * @version $Revision: 1.8 $
   */
  class IDStatus {
    /**
     * <code>fileid</code> IDStatus -
     */
    int fileid = -1;
    /**
     * <code>status</code> IDStatus -
     */
    Status status = Status.FILE_UNKNOWN;
  }

  static {
    Configure.load();
    // Load a database driver
    cls = Configure.getProperty("driver", "com.mysql.jdbc.Driver", SQLDatabase.class.getName());
    url = Configure.getProperty("url", "jdbc:mysql://localhost/dump3", SQLDatabase.class.getName());
    user = Configure.getProperty("user", "root", SQLDatabase.class.getName());
    pass = Configure.getProperty("password", "pass", SQLDatabase.class.getName());
  }

  /**
   * SQLDatabase constructor.
   * 
   * @modelguid {E00434DD-5C6B-48A9-B50B-B3280775B335}
   */
  SQLDatabase() {
    super();
  }

  /**
   * @see net.za.grasser.duplicate.persist.Database#connect()
   */
  public boolean connect() {
    try {
      log.debug("connect");
      // try to connect
      Class.forName(cls);
      conn = DriverManager.getConnection(url, user, pass);
      log.debug("Opened database.");
      loadFingerprintTypes();
      checkStatus();
      loadInfoTypes();
      getPrintColumnName();
      return true;
    } catch (final ClassNotFoundException cnf) {
      log.error("Error: " + cnf.getMessage());
      conn = null;
    } catch (final SQLException sqle) {
      log.error("Error: " + sqle.getMessage());
      conn = null;
    }
    return false;
  }

  /**
   * @see net.za.grasser.duplicate.persist.Database#close()
   */
  public void close() {
    log.debug("close");
    try {
      if (stmtGetFingerprint != null) {
        stmtGetFingerprint.close();
      }
      if (stmtInsertFilename != null) {
        stmtInsertFilename.close();
      }
      if (stmtUpdateStatus != null) {
        stmtUpdateStatus.close();
      }
      if (stmtGetFileId != null) {
        stmtGetFileId.close();
      }
      if (stmtGetFileTypeId != null) {
        stmtGetFileTypeId.close();
      }
      if (stmtInsertFingerprint != null) {
        stmtInsertFingerprint.close();
      }
      if (stmtGetStatus != null) {
        stmtGetStatus.close();
      }
      conn.close();
    } catch (final SQLException se) {
      // ignore we are closing
    } catch (final Throwable t) {
      log.error("failed to close", t);
    }
  }

  /**
   * This method loads all info strings.
   */
  private void loadInfoTypes() {
    log.debug("load info types to memory");
    PreparedStatement statement = null;
    ResultSet rs = null;
    infoTypes = new HashMap<String, Integer>();
    try {
      statement = conn.prepareStatement("SELECT ft.fileinfotypeid, ft.fileinfotype FROM fileinfotype ft");
      rs = statement.executeQuery();
      while (rs.next()) {
        infoTypes.put(rs.getString(2), new Integer(rs.getInt(1)));
      }
      log.debug("load info types return");
    } catch (final SQLException se) {
      log.warn("Info types could not be loaded. Info tables need to be created.", se);
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
        if (statement != null) {
          statement.close();
        }
      } catch (final SQLException se) {
        // ignore we are closing
      }
    }
  }

  /**
   * This method checks that all the statuses are loaded
   */
  private void checkStatus() {
    log.debug("check status types");
    PreparedStatement statement = null;
    PreparedStatement insert = null;
    ResultSet rs = null;
    try {
      statement = conn.prepareStatement("SELECT st.statusid, st.status FROM status st WHERE st.statusid=? ORDER BY st.statusid");
      insert = conn.prepareStatement("INSERT INTO status (statusid, status) VALUES (?, ?)");
      for (final Status s : Status.values()) {
        statement.setInt(1, s.getValue());
        rs = statement.executeQuery();
        if (!rs.next()) {
          insert.setInt(1, s.getValue());
          insert.setString(2, s.getDescription());
          insert.executeUpdate();
        }
        rs.close();
      }
      log.debug("check status types return");
    } catch (final SQLException se) {
      log.warn("Statuses could not be checked.", se);
    } finally {
      try {
        if (statement != null) {
          statement.close();
        }
        if (insert != null) {
          insert.close();
        }
      } catch (final SQLException se) {
        // ignore we are closing
      }
    }
  }

  /**
   * This method loads a list of fingerprint types. Quicker than looking it up in the DB all the time.
   */
  private static void loadFingerprintTypes() {
    log.debug("load fingerprint types to memory");
    PreparedStatement statement = null;
    ResultSet rs = null;
    fingerprintTypes = new HashMap<String, Integer>();
    try {
      statement = conn.prepareStatement("SELECT ft.fingerprinttypeid, ft.fingerprinttype FROM fingerprinttype ft");
      rs = statement.executeQuery();
      while (rs.next()) {
        fingerprintTypes.put(rs.getString(2), new Integer(rs.getInt(1)));
      }
      log.debug("load fingerprint types return");
    } catch (final SQLException se) {
      log.warn("Fingerprint types could not be loaded.", se);
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
        if (statement != null) {
          statement.close();
        }
      } catch (final SQLException se) {
        // ignore we are closing
      }
    }
  }

  /**
   * This method determines the column name for the fingerprint table. Backward compatibility.
   */
  private static void getPrintColumnName() {
    log.debug("check fingerprint columns name (backward compatibility)");
    PreparedStatement statement = null;
    try {
      // query column names for fingerprint table
      statement = conn.prepareStatement("SELECT * FROM fingerprint WHERE 1 = 0");
      // query is never executed on the server - only prepared
      final ResultSetMetaData rsmd = statement.getMetaData();
      final int numcols = rsmd.getColumnCount();
      for (int i = 1; i <= numcols; i++) {
        if (rsmd.getColumnType(i) != Types.INTEGER) {
          printColumn = rsmd.getColumnName(i);
        }
      }
      // oops we coudn't get it - lets assume default
      if (printColumn == null) {
        printColumn = "fingerprint";
      }
      log.debug("check fingerprint columns return");
    } catch (final SQLException se) {
      log.warn("Print column name could not be determined.", se);
    } finally {
      try {
        if (statement != null) {
          statement.close();
        }
      } catch (final SQLException se) {
        // ignore we are closing
      }
    }
  }

  /**
   * gets the id for the filename
   * 
   * @param pKey filename
   * @return filename id
   */
  private IDStatus getFileNameId(final String pKey) {
    log.debug("get file id key=[" + pKey + "]");
    ResultSet rs = null;