import java.sql.*;
import java.io.*;
import java.util.zip.*;
import java.util.Enumeration;
import java.util.LinkedList;

/**
 * Allows direct JDB access to an OpenOffice.org {@code odb} file.  Extracts the zipped
 * {@code odb} file into a temporary directory.  The database can then be modified using
 * HSQLDB (the driver class {@code org.hsqldb.jdbcDriver}, bundled with OpenOffice in the
 * {@code hsqldb.jar} file, must be on the classpath).  Note that, because all
 * changes are made to separate, temporary files, no modifications will be reflected in
 * the original {@code odb} file until {@link #close} is invoked, causing the original
 * data to be overwritten.
 */
public class OpenOfficeDatabaseConnector {
  
  private final File _odbFile;
  private final File _lockFile;
  private final long _lockFileModified;
  private final File _tempDir;
  private final Connection _connection;
  
  /** Create a connector for the given file name */
  public OpenOfficeDatabaseConnector(String odbFileName) 
    throws FileNotFoundException, IOException, SQLException {
    
    this(new File(odbFileName));
  }
  
  /**
   * Create a connector for the given file.  Lock the database file (potentially preventing
   * simultaneous access, although OpenOffice may ignore the lock).
   * 
   * @throws RuntimeException  If {@code org.hsqldb.jdbcDriver} is not on the classpath
   * @throws IOException  If the given file is nonexistent, not correctly formatted, or currently locked,
   *                      or if an error occurs when creating the temporary files
   * @throws SQLException  If a database connection cannot be established
   */
  public OpenOfficeDatabaseConnector(File odbFile) 
    throws IOException, SQLException {
    
    if (!odbFile.getName().endsWith(".odb")) { 
      throw new IOException("Database filename " + odbFile + " does not have the '.odb' extension");
    }
    if (!odbFile.exists()) {
      throw new IOException("Database file " + odbFile + " does not exist");
    }
    String shortName = odbFile.getName().substring(0, odbFile.getName().lastIndexOf(".odb"));
    _lockFile = new File(odbFile.getParent(), shortName + ".lck");
    if (_lockFile.exists()) {
      throw new IOException("Database file " + odbFile + " is currently in use");
    }
    
    _odbFile = odbFile;
    _tempDir = makeTempDir(shortName);
    expandOdb(_odbFile, _tempDir, shortName);
    try { Class.forName("org.hsqldb.jdbcDriver"); }
    catch (ClassNotFoundException e) { throw new RuntimeException(e); }
    
    String url = "jdbc:hsqldb:file:" + _tempDir + "/" + shortName + ";ifexists=true";
    _connection = DriverManager.getConnection(url, "sa", "");

    File tempLockFile = new File(_tempDir, shortName + ".lck");
    if (tempLockFile.exists()) { copyFile(tempLockFile, _lockFile); }
    _lockFileModified = _lockFile.lastModified();
  }
  
  /** @return  A JDB connection object, allowing interaction with the database */
  public Connection connection() { return _connection; }
  
  /**
   * Close the database, re-zipping the data and overwriting the original {@code odb} file.
   * Note that no changes will appear in the original file unless this or {@link #closeAndCompact}
   * is invoked.
   * 
   * @throws IOException  If the {@code odb} file cannot be overwritten, or if an error occurs
   *                      while cleaning up the temporary files
   */
  public void close() throws IOException {
    close(false);
  }
  
  /**
   * Close and compact the database, re-zipping the data and overwriting the original {@code odb} file.
   * Note that no changes will appear in the original file unless this or {@link #close}
   * is invoked.  Compaction will make the database file smaller, but take longer than just closing.
   * 
   * @throws IOException  If the {@code odb} file cannot be overwritten, or if an error occurs
   *                      while cleaning up the temporary files
   */
  public void closeAndCompact() throws IOException {
    close(true);
  }
  
  private void close(boolean compact) throws IOException {  
    try { 
      _connection.createStatement().executeQuery(compact ? "shutdown compact" : "shutdown");
      _connection.close();
    }
    catch (SQLException e) { throw new RuntimeException(e); }
    zipOdb(_tempDir, _odbFile);
    deleteDir(_tempDir);
    if (_lockFile.exists() && _lockFile.lastModified() == _lockFileModified) { 
      _lockFile.delete();
    }
  }
  

  private static File makeTempDir(String odbFileName) throws IOException {
    File f = File.createTempFile("hsqldb." + odbFileName + ".", null);
    boolean deleted = f.delete();
    if (!deleted) { throw new IOException("Unable to delete temp file " + f); }
    boolean created = f.mkdir();
    if (!created) { throw new IOException("Unable to create temp directory " + f); }
    return f;
  }
  
  private static void deleteDir(File d) throws IOException {
    if (d.exists()) {
      if (d.isDirectory()) {
        for (File f : d.listFiles()) { deleteDir(f); }
        boolean success = d.delete();
        if (!success) { throw new IOException("Unable to delete directory " + d); }
      }
      else { 
        boolean success = d.delete();
        if (!success) { throw new IOException("Unable to delete file " + d); }
      }
    }
  }
  
  private static void expandOdb(File odbFile, File dir, String shortName) throws IOException {
    ZipFile z = new ZipFile(odbFile);
    try {
      LinkedList<ZipEntry> entries = new LinkedList<ZipEntry>();
      entries.add(z.getEntry("database/properties"));
      entries.add(z.getEntry("database/script"));
      entries.add(z.getEntry("database/log"));
      entries.add(z.getEntry("database/data"));
      entries.add(z.getEntry("database/backup"));
      for (ZipEntry e : entries) {
        if (e != null) {
          String fileName = shortName + "." + e.getName().substring(e.getName().lastIndexOf("/") + 1);
          OutputStream out = new FileOutputStream(dir + "/" + fileName);
          InputStream in = z.getInputStream(e);
          try { copyStreams(in, out); }
          finally { in.close(); out.close(); }
        }
      }
    }
    finally { z.close(); }
  }
  
  private static void zipOdb(File dir, File odbFile) throws IOException {
    // First, zip the original contents, plus the new database files, into a new, temporary location
    File tempZip = File.createTempFile(odbFile.getName(), "zip");
    ZipFile z = new ZipFile(odbFile);
    try {
      ZipOutputStream out = new ZipOutputStream(new FileOutputStream(tempZip));
      try {
        Enumeration<? extends ZipEntry> entries = z.entries();
        while (entries.hasMoreElements()) {
          ZipEntry e = entries.nextElement();
          if (!e.getName().startsWith("database/") || e.isDirectory()) {
            InputStream in = z.getInputStream(e);
            try {
              out.putNextEntry(e);
              copyStreams(in, out);
            }
            finally { in.close(); }
          }
        }
        
        for (File f : dir.listFiles()) {
          // remove the filename prefix from database files
          ZipEntry e = new ZipEntry("database/" + f.getName().substring(f.getName().lastIndexOf(".") + 1));
          e.setTime(f.lastModified());
          InputStream in = new FileInputStream(f);
          try { 
            out.putNextEntry(e);
            copyStreams(in, out);
          }
          finally { in.close(); }
        }
      }
      finally { out.close(); }
    }
    finally { z.close(); }
    
    // Second, copy the new zip file over the old one
    OutputStream out = new FileOutputStream(odbFile);
    try {
      InputStream in = new FileInputStream(tempZip);
      try {
        copyStreams(in, out);
      }
      finally { in.close(); }
    }
    finally { out.close(); }
    
    boolean deleted = tempZip.delete();
    if (!deleted) { throw new IOException("Unable to delete " + tempZip); }
  }
  
  private static void copyFile(File from, File to) throws FileNotFoundException, IOException {
    InputStream in = new FileInputStream(from);
    try {
      OutputStream out = new FileOutputStream(to);
      try { copyStreams(in, out); }
      finally { out.close(); }
    }
    finally { in.close(); }
  }
  
  private static void copyStreams(InputStream in, OutputStream out) throws IOException {
    byte[] buffer = new byte[1024];
    int read = in.read(buffer);
    while (read >= 0) { 
      out.write(buffer, 0, read);
      read = in.read(buffer);
    }
  }
    
}