Re: remote database backup,

/*
 * DatabaseExporter.java
 * Feb 2004
 * NASA/GCMD
 * [email protected]
 * Created on February, 2004
 */

// DISCLAIMER
//
// IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY
// FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES
// ARISING OUT OF THE USE OF THIS SOFTWARE, ITS DOCUMENTATION, OR ANY
// DERIVATIVES THEREOF, EVEN IF THE AUTHORS HAVE BEEN ADVISED OF THE
// POSSIBILITY OF SUCH DAMAGE.
//
// THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
// INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE, AND NON-INFRINGEMENT.  THIS SOFTWARE
// IS PROVIDED ON AN "AS IS" BASIS, AND THE AUTHORS AND DISTRIBUTORS HAVE
// NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR
// MODIFICATIONS.

package gov.nasa.gsfc.md.util.db;

import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.Date;

import gov.nasa.gsfc.md.util.jar.*;

/** Exports the database to a bunch of files, each one representing
 * a table in the database.
 * ONLY supports VARCHAR, VARCHAR2, NUMBER, LONG, DATE, FLOAT
 * at the moment.
 *
 * TODO: Support more types
 */

public class DatabaseExporter {
  Connection conn;
  boolean silentMode;
  public static String driver = "oracle.jdbc.driver.OracleDriver";

  public DatabaseExporter(String databaseUrl, Properties prop,
                          boolean silentMode) throws ClassNotFoundException,
      SQLException {
    this.silentMode = silentMode;
    Class.forName(driver);
    conn = DriverManager.getConnection(databaseUrl, prop);
    conn.setAutoCommit(false);
  }

  public DatabaseExporter(Connection conn, boolean silentMode) throws
      SQLException {
    this.silentMode = silentMode;
    this.conn = conn;
    conn.setAutoCommit(false);
  }

  public void exportDatabase(String catalog, String schema, String tableName,
                             String[] tableTypes, JarCompressor compressor) throws
      IOException, SQLException {
    DatabaseMetaData metadata = conn.getMetaData();
    ResultSet set = metadata.getTables(catalog, schema, tableName, tableTypes);
    while (set.next()) {
      String name = set.getString(3);

      // Retrieve number of rows.
      String sqlCount = "SELECT COUNT(*) FROM " + name;
      Statement stmtCount = conn.createStatement();
      ResultSet resultsCount = stmtCount.executeQuery(sqlCount);
      resultsCount.next();
      int noRows = resultsCount.getInt(1);
      resultsCount.close();
      stmtCount.close();

      String sql = "SELECT * FROM " + name;
      Statement stmt = conn.createStatement();
      ResultSet rows = stmt.executeQuery(sql);
      ResultSetMetaData meta = rows.getMetaData();

      System.out.print("Exporting " + name);
      for (int i = 0; i < 30 - name.length(); i++) {
        System.out.print(" ");
      }
      File tmpDirectory = File.createTempFile("temp", ".txt").getParentFile();
      File targetFile = new File(tmpDirectory, name);

      ObjectOutputStream oos = new ObjectOutputStream(new FileOutputStream(
          targetFile));

      // Write Header data (column names)
      String[] headerData = new String[meta.getColumnCount()];
      for (int i = 0; i < meta.getColumnCount(); i++) {
        headerData[i] = meta.getColumnName(i + 1);
      }
      oos.writeObject(headerData);

      // Write Header data (types)
      headerData = new String[meta.getColumnCount()];
      for (int i = 0; i < meta.getColumnCount(); i++) {
        headerData[i] = meta.getColumnTypeName(i + 1);
      }
      oos.writeObject(headerData);

      // Write number of records
      oos.writeInt(noRows);

      // Write Row data
      int rowNo = 0;
      int oldPercent = 0;
      while (rows.next()) {
        Object columnData[] = new Object[meta.getColumnCount()];
        for (int i = 0; i < meta.getColumnCount(); i++) {
          columnData[i] = rows.getObject(i + 1);
        }
        oos.writeObject(columnData);

        int newPercent = Math.round(++rowNo / (float) noRows * 100 / 3);
        if (newPercent != oldPercent) {
          for (int i = 0; i < newPercent - oldPercent; i++) {
            System.out.print("*");
          }
          oldPercent = newPercent;
        }
      }
      System.out.println("");
      oos.close();
      rows.close();
      stmt.close();

      compressor.addFile(targetFile, false, false,
                         java.util.zip.Deflater.DEFAULT_COMPRESSION);

      targetFile.delete();
    }
    set.close();
  }

  public static void printUsage() {
    System.out.println("Usage:");
    System.out.println("java gov.nasa.gsfc.md.util.DatabaseImporter -database_url <url> -user_id=<string> -password=<password> -target=<path> -table_listing <path>");
  }

  private static File createHeaderFile() throws IOException {
    File tmpDirectory = File.createTempFile("temp", ".txt").getParentFile();
    File headerFile = new File(tmpDirectory, "export_header.txt");
    PrintWriter writer = new PrintWriter(new FileWriter(headerFile));
    writer.println(new Date().toString());
    writer.flush();
    writer.close();
    return headerFile;
  }

  public static void main(String argv[]) throws Exception {
    String catalog = "md8db";
    String schema = "MDDBA";
    String tableName = "%";
    String targetFile = "./export.jar";
    String tableListing = "./table_listing.txt";
    String databaseUrl = null;
    String userId = null;
    String password = null;

    if (argv.length == 0) {
      printUsage();
      System.exit(1);
    }

    int i = 0;
    for (i = 0; i < argv.length; i++) {
      if ("-DRIVER".equalsIgnoreCase(argv[i])) {
        if (i + 1 < argv.length) {
          driver = argv[++i];
        }
      }
      else if ("-DATABASE_URL".equalsIgnoreCase(argv[i])) {
        if (i + 1 < argv.length) {
          databaseUrl = argv[++i];
        }
      }
      else if ("-USER_ID".equalsIgnoreCase(argv[i])) {
        if (i + 1 < argv.length) {
          userId = argv[++i];
        }
      }
      else if ("-TARGET".equalsIgnoreCase(argv[i])) {
        if (i + 1 < argv.length) {
          targetFile = argv[++i];
        }
      }
      else if ("-TABLE_LISTING".equalsIgnoreCase(argv[i])) {
        if (i + 1 < argv.length) {
          tableListing = argv[++i];
        }
      }
      else if ("-PASSWORD".equalsIgnoreCase(argv[i])) {
        if (i + 1 < argv.length) {
          password = argv[++i];
        }
      }
      else if ("-CATALOG".equalsIgnoreCase(argv[i])) {
        if (i + 1 < argv.length) {
          catalog = argv[++i].toUpperCase();
        }
      }
      else if ("-SCHEMA".equalsIgnoreCase(argv[i])) {
        if (i + 1 < argv.length) {
          schema = argv[++i].toUpperCase();
        }
      }
      else if ("-TABLE_NAME".equalsIgnoreCase(argv[i])) {
        if (i + 1 < argv.length) {
          tableName = argv[++i].toUpperCase();
        }
      }
      else if (argv[i].startsWith("-")) {
        System.out.println("Invalid argument " + argv[i]);
        System.exit(1);
      }
      else {
        break;
      }
    }

    Properties dbProps = new Properties();
    dbProps.put("user", userId);
    dbProps.put("password", password);
    DatabaseExporter dbExporter = new DatabaseExporter(databaseUrl, dbProps, true);
    String tableTypes[] = {
        "TABLE"};
    JarCompressor compressor = new JarCompressor(new File(targetFile));
    compressor.open();

    File headerFile = createHeaderFile();
    File tableListingFile = new File(tableListing);

    compressor.addFile(headerFile, false, false,
                       java.util.zip.Deflater.DEFAULT_COMPRESSION);

    compressor.addFile(tableListingFile, false, false,
                       java.util.zip.Deflater.DEFAULT_COMPRESSION);

    headerFile.delete();

    dbExporter.exportDatabase(catalog, schema, tableName, tableTypes,
                              compressor);
    compressor.close();
  }

}

package gov.nasa.gsfc.md.util.db;

/*
 * DatabaseImporter.java
 * Feb 2004
 * NASA/GCMD
 * [email protected]
 * Created on February, 2004
 */

// DISCLAIMER
//
// IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY
// FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES
// ARISING OUT OF THE USE OF THIS SOFTWARE, ITS DOCUMENTATION, OR ANY
// DERIVATIVES THEREOF, EVEN IF THE AUTHORS HAVE BEEN ADVISED OF THE
// POSSIBILITY OF SUCH DAMAGE.
//
// THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
// INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE, AND NON-INFRINGEMENT.  THIS SOFTWARE
// IS PROVIDED ON AN "AS IS" BASIS, AND THE AUTHORS AND DISTRIBUTORS HAVE
// NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR
// MODIFICATIONS.

import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.jar.*;
import java.util.zip.*;

import javax.swing.*;

import gov.nasa.gsfc.md.util.gui.ProgressMonitor;
import gov.nasa.gsfc.md.setup.*;

/** Imports a file created from DatabaseExporter into the specified database.
 *
 * ONLY supports VARCHAR, VARCHAR2, NUMBER, LONG, DATE, FLOAT
 * at the moment.
 *
 * TODO: Support more types
 *
 */

public class DatabaseImporter {

    Connection conn;
    File exportFile;
    String[] tableNames;

  public static String driver = "oracle.jdbc.driver.OracleDriver";

  public DatabaseImporter(String databaseUrl, Properties prop, File exportFile) throws
      ClassNotFoundException,
      SQLException {
    this.exportFile = exportFile;
    Class.forName(driver);
    conn = DriverManager.getConnection(databaseUrl, prop);
    conn.setAutoCommit(false);
  }

  public DatabaseImporter(String databaseUrl, Properties prop, File exportFile, String tableName) throws
      ClassNotFoundException,
      SQLException {
    this.exportFile = exportFile;
    Class.forName(driver);
    conn = DriverManager.getConnection(databaseUrl, prop);
    conn.setAutoCommit(false);
    tableNames = new String[] {tableName};
  }

  public DatabaseImporter(Connection conn, File exportFile) throws
      ClassNotFoundException,
      SQLException {
    this.exportFile = exportFile;
    this.conn = conn;
    conn.setAutoCommit(false);
  }

  public void importDatabase() throws IOException, SQLException,
      ClassNotFoundException, IOException {
    importDatabase(null);
  }

  public void importDatabase(ProgressMonitor progressMonitor) throws
      IOException,
      ClassNotFoundException, SQLException {
    ObjectInputStream ois = null;
    ProgressMonitorInputStream pmis = null;
    InputStream stream = null;

    ZipFile jf = new JarFile(exportFile);
    if (tableNames == null)
	tableNames = getTableListing(jf);

    try {
      for (int i = 0; i < tableNames.length; i++) {
        if (progressMonitor != null) {
          if (progressMonitor.isCanceled()) {
            return;
          }
          progressMonitor.setNote("Importing " + tableNames[i] + " table (" + i +
                                  "/" + tableNames.length + ")");
        }
        else {
          System.out.print("Importing " + tableNames[i]);
          for (int j = 0; j < 30 - tableNames[i].length(); j++) {
            System.out.print(" ");
          }
        }

        // Setup SQL INSERT query.

        jf = new JarFile(exportFile);
        stream = getTableStream(jf, tableNames[i]);

        if (progressMonitor != null) {
          pmis = new ProgressMonitorInputStream(progressMonitor.getDialog(),
                                                "Importing " + tableNames[i],
                                                stream);
          ois = new ObjectInputStream(pmis);
        }
        else {
          ois = new ObjectInputStream(stream);
        }
        String[] columnName = (String[]) ois.readObject();
        String[] dataType = (String[]) ois.readObject();
        int noRows = ois.readInt();
        String sql = "INSERT INTO " + tableNames[i] + " (";
        int j = 0;
        for (j = 0; j < columnName.length - 1; j++) {
          if (Setup.get("DATABASE_TYPE").toLowerCase().startsWith("postgresql"))
            sql += columnName[j] + ",";
          else
            sql += tableNames[i] + "." + columnName[j] + ",";
        }

        if (Setup.get("DATABASE_TYPE").toLowerCase().startsWith("postgresql"))
          sql += columnName[j];
        else
          sql += tableNames[i] + "." + columnName[j];

        sql += ") VALUES (";
        for (j = 0; j < columnName.length - 1; j++) {
          sql += "?,";
        }
        sql += "?)";

        PreparedStatement statement = conn.prepareStatement(
            sql);

        int rowNo = 0;
        int oldPercent = 0;

        long startTime = System.currentTimeMillis();
        while (true) {
          if (progressMonitor != null && progressMonitor.isCanceled()) {
            return;
          }
          try {
            statement.clearParameters();
            Object[] columnData = (Object[]) ois.readObject();

            for (j = 0; j < columnData.length; j++) {
              Object data = columnData[j];

              if (dataType[j].startsWith("VARCHAR")) {
                statement.setString(j + 1, (String) data);
              }
              else if (dataType[j].equals("NUMBER")) {
                if (data != null) {
                  statement.setInt(j + 1,
                                   ( (java.math.BigDecimal) data).intValue());
                }
                else {
                  statement.setNull(j + 1, Types.NUMERIC);
                }
              }
              else if (dataType[j].equals("LONG")) {
                if (data != null) {
                  statement.setCharacterStream(j + 1,
                                               new StringReader( (String) data),
                                               ( (String) data).length());
                }
                else {
                  statement.setNull(j + 1, Types.LONGVARCHAR);
                }
              }
              else if (dataType[j].equals("DATE")) {
                if (data != null) {
                  long time = ( (java.util.Date) data).getTime();
                  Calendar c = Calendar.getInstance();
                  c.setTime( (java.util.Date) data);
                  statement.setTimestamp(j + 1, new java.sql.Timestamp(time), c);
                }
                else {
                  statement.setNull(j + 1, Types.TIMESTAMP);
                }
              }
              else if (dataType[j].equals("FLOAT")) {
                if (data != null) {
                  statement.setFloat(j + 1, ( (Float) data).floatValue());
                }
                else {
                  statement.setNull(j + 1, Types.FLOAT);
                }
              }
              else {
                throw new RuntimeException("Type not supported, type=" +
                                           dataType[j]);
              }
            }

            try {
              statement.executeUpdate();

              if (progressMonitor == null) {
                int newPercent = Math.round(++rowNo / (float) noRows * 100 / 3);
                if (newPercent != oldPercent) {
                  for (int k = 0; k < newPercent - oldPercent; k++) {
                    System.out.print("*");
                  }
                  oldPercent = newPercent;
                }
                if ( (++rowNo % 100) == 0) {
                  conn.commit();
                }
                continue;
              }

              if (progressMonitor != null) {
                if ( (++rowNo % 100) == 0) {
                  conn.commit();

                }
                long elapsedTime = System.currentTimeMillis() - startTime;
                float rowTime = elapsedTime / (float) rowNo;
                float timeRemaing = Math.round( ( (rowTime * noRows) -
                                                 elapsedTime) / 1000);
                pmis.getProgressMonitor().setNote(
                    "Estimated time remaining " +
                    timeRemaing + " secs.");
              }
            }
            catch (SQLException e) {
              System.out.println("psql=" + sql);
              throw e;
            }
          }
          catch (java.io.EOFException e) {
            break;
          }
        }
        System.out.println("");
        conn.commit();
        statement.close();
        if (progressMonitor != null) {
          progressMonitor.incrementProgress();
        }
      }
    }

    catch (InterruptedIOException e) {
      progressMonitor.cancel();
      return;
    }
    finally {
      if (conn != null) {
        conn.close();
      }
      if (pmis != null) {
        pmis.close();
      }
      if (stream != null) {
        stream.close();
      }
      if (jf != null) {
        jf.close();
      }
      if (ois != null) {
        ois.close();
      }
    }

  }

  private InputStream getTableStream(ZipFile jf, String tableName) throws
      IOException {
    return jf.getInputStream(jf.getEntry(tableName));
  }

  private String[] getTableListing(ZipFile jf) throws IOException {
    BufferedReader reader = new BufferedReader(new InputStreamReader(jf.
        getInputStream(jf.getEntry("import.txt"))));
    String line = null;
    Vector list = new Vector();
    while ( (line = reader.readLine()) != null) {
      list.addElement(line.toUpperCase());
    }
    String tableNames[] = new String[list.size()];
    list.copyInto(tableNames);
    jf.close();
    return tableNames;
  }

  public static void main(String argv[]) throws Exception {
    String databaseUrl = null;
    String userId = null;
    String password = null;
    String exportFile = null;
    String table = null;

    if (argv.length == 0) {
      printUsage();
      System.exit(1);
    }

    int i = 0;
    for (i = 0; i < argv.length; i++) {
      if ("-DRIVER".equalsIgnoreCase(argv[i])) {
        if (i + 1 < argv.length) {
          driver = argv[++i];
        }
      }
      else if ("-DATABASE_URL".equalsIgnoreCase(argv[i])) {
        if (i + 1 < argv.length) {
          databaseUrl = argv[++i];
        }
      }
      else if ("-USER_ID".equalsIgnoreCase(argv[i])) {
        if (i + 1 < argv.length) {
          userId = argv[++i];
        }
      }
      else if ("-PASSWORD".equalsIgnoreCase(argv[i])) {
        if (i + 1 < argv.length) {
          password = argv[++i];
        }
      }
      else if ("-EXPORT_FILE".equalsIgnoreCase(argv[i])) {
        if (i + 1 < argv.length) {
          exportFile = argv[++i];
        }
      }
      else if ("-TABLE".equalsIgnoreCase(argv[i])) {
        if (i + 1 < argv.length) {
          table = argv[++i];
        } else {
	    System.err.println("Must include a table name.");
	    return;
	}
      }
      else if (argv[i].startsWith("-")) {
        System.out.println("Invalid argument " + argv[i]);
        System.exit(1);
      }
      else {
        break;
      }
    }

    Properties dbProps = new Properties();
    dbProps.put("user", userId);
    dbProps.put("password", password);
    DatabaseImporter dbImporter;
    if (table == null) {
	dbImporter = new DatabaseImporter(databaseUrl, dbProps,
					  new File(exportFile));
    } else {
	dbImporter = new DatabaseImporter(databaseUrl, dbProps,
					  new File(exportFile), table);
    }
    dbImporter.importDatabase();
  }

  public static void printUsage() {
    System.out.println("Usage:");
    System.out.println("java gov.nasa.gsfc.md.util.DatabaseImporter -database_url <url> -user_id=<string> -password=<password -export_file <path>>");
  }
}