sqldatabase.java

dump3 morpheus 0.2.9 src

    try {
      if (stmtGetFileId == null) {
        stmtGetFileId = conn.prepareStatement("SELECT fn.filenameid, fn.statusid FROM filename fn WHERE fn.filename=?");
      }
      stmtGetFileId.setString(1, pKey.replace('\'', '*').replace('\"', ':'));
      rs = stmtGetFileId.executeQuery();
      if (rs.next()) {
        final IDStatus s = new IDStatus();
        s.fileid = rs.getInt(1);
        s.status = Status.find(rs.getInt(2));
        log.debug("get file id return [" + s.fileid + "," + s.status.toString() + "]");
        return s;
      }
      log.debug("get file id return [null]");
      return null;
    } catch (final SQLException se) {
      log.warn("Filename not found for file key=" + pKey, se);
      return null;
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
      } catch (final SQLException se) {
        // ignore we are closing
      }
    }
  }

  /**
   * This method gets all the info from the db
   * 
   * @param piFileId
   * @return Map<String, String>
   */
  private Map<String, String> getInfo(final int piFileId) {
    log.debug("get info key=[" + piFileId + "]");
    ResultSet rs = null;
    try {
      if (stmtGetInfo == null) {
        stmtGetInfo = conn.prepareStatement("SELECT ft.fileinfotype, fi.fileinfo FROM fileinfotype ft, fileinfo fi"
            + " WHERE fi.filenameid=? AND fi.fileinfotypeid = ft.fileinfotypeid");
      }
      stmtGetInfo.setInt(1, piFileId);
      rs = stmtGetInfo.executeQuery();
      final Map<String, String> info = new HashMap<String, String>();
      while (rs.next()) {
        info.put(rs.getString(1), rs.getString(2));
      }
      if (info.isEmpty()) {
        log.debug("get info return [empty]");
      } else {
        log.debug("get info return [info]");
      }
      return info;
    } catch (final SQLException se) {
      log.error("Info not found.", se);
      return null;
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
      } catch (final SQLException se) {
        // ignore we are closing
      }
    }
  }

  /**
   * This method will read the fingerprints from the database
   * 
   * @param piFileId
   * @param pBf
   */
  private void getFingerprints(final int piFileId, final FingerprintFile pBf) {
    log.debug("get fingerprints key=[" + piFileId + "]");
    ResultSet rs = null;
    try {
      if (stmtGetFingerprint == null) {
        stmtGetFingerprint = conn.prepareStatement("SELECT ft.fingerprinttype, fp." + printColumn + " FROM fingerprint fp, fingerprinttype ft"
            + " WHERE fp.filenameid=? AND fp.fingerprinttypeid=ft.fingerprinttypeid");
      }
      stmtGetFingerprint.setInt(1, piFileId);
      rs = stmtGetFingerprint.executeQuery();
      while (rs.next()) {
        final String key = rs.getString(1);
        final byte[] fp = new BASE64Decoder().decodeBuffer(rs.getString(2));
        final AbstractFingerprint af = pBf.getFingerprints().get(key);
        if (af != null) {
          af.setFingerprint(fp);
        }
      }
      log.debug("get fingerprints return");
    } catch (final SQLException se) {
      log.error("Fingerprint not found.", se);
    } catch (final IOException ioe) {
      log.error("Fingerprint could not be created.", ioe);
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
      } catch (final SQLException se) {
        // ignore we are closing
      }
    }
  }

  /**
   * This method inserts the file into the database.
   * 
   * @param pKey
   * @param pStatus
   * @return id of the inserted file
   */
  private int insertFileName(final String pKey, final int pStatus) {
    log.debug("insert file id key=[" + pKey + "," + pStatus + "]");
    ResultSet rs = null;
    try {
      if (stmtInsertFilename == null) {
        stmtInsertFilename = conn.prepareStatement("INSERT INTO filename (filename, statusid) values (?,?)");
      }
      stmtInsertFilename.setString(1, pKey.replace('\'', '*').replace('\"', ':'));
      stmtInsertFilename.setInt(2, pStatus);
      stmtInsertFilename.executeUpdate();
      rs = stmtInsertFilename.getGeneratedKeys();
      int id = -1;
      if (rs.next()) {
        id = rs.getInt(1);
        log.debug("insert file return [" + id + "]");
        return id;
      }
      log.debug("insert file return [" + id + "]");
      return id;
    } catch (final SQLException se) {
      final IDStatus id = getFileNameId(pKey);
      if (id == null) {
        log.warn("Filename not inserted for file key=" + pKey, se);
        return -1;
      }
      log.debug("insert file return [" + id.fileid + "]");
      return id.fileid;
    } finally {
      try {
        if (!conn.getAutoCommit()) {
          conn.commit();
        }
        if (rs != null) {
          rs.close();
        }
      } catch (final SQLException se) {
        log.warn("Could not commit", se);
      }
    }
  }

  /**
   * This method inserts new info types into the info type table.
   * 
   * @param pType
   * @return Integer
   */
  private Integer insertInfoType(final String pType) {
    log.debug("insert info type key=[" + pType + "]");
    ResultSet rs = null;
    try {
      if (stmtInsertInfoType == null) {
        stmtInsertInfoType = conn.prepareStatement("INSERT INTO fileinfotype (fileinfotype) values (?)");
      }
      stmtInsertInfoType.setString(1, pType);
      stmtInsertInfoType.executeUpdate();
      rs = stmtInsertInfoType.getGeneratedKeys();
      Integer i = null;
      if (rs.next()) {
        i = new Integer(rs.getInt(1));
        infoTypes.put(pType, i);
      }
      log.debug("insert info type return [" + i + "]");
      return i;
    } catch (final SQLException se) {
      log.warn("Info type not inserted for key=" + pType, se);
      return null;
    } finally {
      try {
        if (!conn.getAutoCommit()) {
          conn.commit();
        }
        if (rs != null) {
          rs.close();
        }
      } catch (final SQLException se) {
        log.warn("Could not commit", se);
      }
    }
  }

  /**
   * @param pFile
   * @param pInfo
   */
  private void insertInfo(final int pFile, final Map<String, String> pInfo) {
    log.debug("insert info key=[" + pFile + "]");
    if (pInfo == null) {
      return;
    }
    try {
      if (stmtInsertInfo == null) {
        stmtInsertInfo = conn.prepareStatement("INSERT INTO fileinfo (fileinfotypeid, filenameid, fileinfo) values (?,?,?)");
      }
      for (final String str : pInfo.keySet()) {
        Integer id = infoTypes.get(str);
        if (id == null) {
          // insert info type first
          id = insertInfoType(str);
          if (id == null) {
            // tables probably don't exist
            return;
          }
        }
        stmtInsertInfo.setInt(1, id.intValue());
        stmtInsertInfo.setInt(2, pFile);
        stmtInsertInfo.setString(3, pInfo.get(str));
        stmtInsertInfo.executeUpdate();
      }
      log.debug("insert info return [inserted]");
    } catch (final SQLException se) {
      log.warn("Info not inserted for file key=[" + pFile + "]");
    } finally {
      try {
        if (!conn.getAutoCommit()) {
          conn.commit();
        }
      } catch (final SQLException se) {
        log.warn("Could not commit", se);
      }
    }
  }

  /**
   * gets the fingerprinttype id from the hash (loaded at startup), inserts it if not present
   * 
   * @param pFileType
   * @return fingerprinttype id
   */
  private Integer getFileTypeId(final String pFileType) {
    Integer ft = fingerprintTypes.get(pFileType);
    if (ft == null) {
      log.debug("get file type key=[" + pFileType + "]");
      ResultSet rs = null;
      try {
        if (stmtGetFileTypeId == null) {
          stmtGetFileTypeId = conn.prepareStatement("INSERT INTO fingerprinttype (fingerprinttype) VALUES (?)");
        }
        stmtGetFileTypeId.setString(1, pFileType);
        stmtGetFileTypeId.executeUpdate();
        rs = stmtGetFileTypeId.getGeneratedKeys();
        if (rs.next()) {
          ft = new Integer(rs.getInt(1));
          fingerprintTypes.put(pFileType, ft);
        }
        log.debug("get file type return [" + ft + "]");
        return ft;
      } catch (final SQLException se) {
        log.warn("Fingerprint type could not be inserted key=" + pFileType, se);
        return null;
      } finally {
        try {
          if (!conn.getAutoCommit()) {
            conn.commit();
          }
          if (rs != null) {
            rs.close();
          }
        } catch (final SQLException se) {
          log.warn("Could not commit", se);
        }
      }
    }
    return ft;
  }

  /**
   * @param fn
   * @param key
   * @param pBf
   */
  private void insertFingerprint(final int fn, final String key, final AbstractFingerprint pBf) {
    if (log.isDebugEnabled()) {
      final StringBuffer sb = new StringBuffer();
      sb.setLength(0);
      sb.append("insert print file=[").append(fn).append("] key=[");
      sb.append(key).append("] print=[").append(HexArray.makeString(pBf.getFingerprint())).append("]");
      log.debug(sb);
    }
    final byte[] fingerprint = pBf.getFingerprint();
    try {
      final Integer ft = getFileTypeId(key);
      if (fingerprint == null) {
        return;
      }
      if (stmtInsertFingerprint == null) {
        stmtInsertFingerprint = conn.prepareStatement("INSERT INTO fingerprint (filenameid,fingerprinttypeid," + printColumn + ") VALUES (?,?,?)");
      }
      stmtInsertFingerprint.setInt(1, fn);
      stmtInsertFingerprint.setInt(2, ft.intValue());
      stmtInsertFingerprint.setString(3, (new BASE64Encoder()).encodeBuffer(fingerprint));
      stmtInsertFingerprint.executeUpdate();
      log.debug("insert print return [inserted]");
    } catch (final SQLException se) {
      // ignore - fingerprint already exists.
      log.warn("Fingerprint already exists");
    } finally {
      try {
        if (!conn.getAutoCommit()) {
          conn.commit();
        }
      } catch (final SQLException se) {
        log.warn("Could not commit", se);
      }
    }
  }

  /**
   * @see net.za.grasser.duplicate.persist.Database#put(net.za.grasser.duplicate.file.FingerprintFile)
   */
  public synchronized void put(final FingerprintFile pBf) {
    final String key = pBf.getKey();
    log.debug("put key=[" + key + "]");
    final int fn = insertFileName(key, pBf.getStatus().getValue());
    if (fn == -1) {
      log.debug("put return [not inserted]");
      return;
    }
    insertInfo(fn, pBf.getInfo());
    for (final Map.Entry<String, AbstractFingerprint> af : pBf.getFingerprints().entrySet()) {
      insertFingerprint(fn, af.getKey(), af.getValue());
    }
    log.debug("put return [inserted]");
  }

  /**
   * @see net.za.grasser.duplicate.persist.Database#get(net.za.grasser.duplicate.file.FingerprintFile)
   */
  public synchronized boolean get(final FingerprintFile pBf) {
    final String key = pBf.getKey();
    log.debug("get key=[" + key + "]");
    final IDStatus s = getFileNameId(key);
    if (s != null) {
      pBf.setStatus(s.status);
      pBf.getInfo().putAll(getInfo(s.fileid));
      getFingerprints(s.fileid, pBf);
      log.debug("get return [found]");
      return true;
    }
    log.debug("get return [not found]");
    return false;
  }
}