/*
* Adito
*
* Copyright (C) 2003-2006 3SP LTD. All Rights Reserved
*
* This program 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.
* This program 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.
*
* You should have received a copy of the GNU General Public
* License along with this program; if not, write to the Free Software
* Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
*/
package com.adito.jdbc;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
* Dumps the contents of a database to an output stream as SQL.
*/
public class DBDumper {
final static Log log = LogFactory.getLog(DBDumper.class);
/**
* Dump table creation and data. It is up to the caller to close the stream and connections when
* finished with.
*
* @param writer write SQL to this writer.
* @param conx connection to get data from
* @param quoteChar character to use to quote strings
* @throws Exception on any error
*/
public void dumpToSQL(PrintWriter writer, JDBCConnectionImpl conx, char quoteChar) throws Exception {
dumpTable(writer, conx, quoteChar, null);
dumpData(writer, conx, quoteChar, null);
}
/**
* Dump table creation SQL. It is up to the caller to close the stream and connections when
* finished with.
*
* @param writer write SQL to this writer.
* @param conx connection to get data from
* @param quoteChar character to use to quote strings
* @param tables array of table names or <code>null</code> to dump all in
* database
* @throws Exception on any error
*/
public void dumpTable(PrintWriter writer, JDBCConnectionImpl conx, char quoteChar, String[] tables) throws Exception {
Connection jdbcConnection = conx.getConnection();
DatabaseMetaData dbMetaData = jdbcConnection.getMetaData();
if (tables == null) {
ResultSet rs = dbMetaData.getTables(null, null, null, null);
try {
while (rs.next()) {
String tableName = rs.getString("TABLE_NAME");
String tableType = rs.getString("TABLE_TYPE");
if (tableType.equalsIgnoreCase("TABLE")) {
dumpTable(writer, conx, quoteChar, new String[] { tableName });
}
}
} finally {
rs.close();
}
} else {
for (int i = 0; i < tables.length; i++) {
String tableName = tables[i];
log.info("Dumping table creation for " + tableName);
writer.println("CREATE TABLE " + tableName + " (");
boolean first = true;
// Columns
ResultSet rs2 = dbMetaData.getColumns(null, null, tableName, "%");
try {
while (rs2.next()) {
if (first) {
first = false;
} else {
writer.println(",");
}
String columnName = rs2.getString("COLUMN_NAME");
String columnType = rs2.getString("TYPE_NAME");
int columnSize = rs2.getInt("COLUMN_SIZE");
String nullable = rs2.getString("IS_NULLABLE");
String nullString = "NULL";
if ("NO".equalsIgnoreCase(nullable)) {
nullString = "NOT NULL";
}
writer.print(" " + columnName + " " + columnType);
if (columnSize != 0) {
if (columnType.equalsIgnoreCase("varchar") && columnSize > 255) {
columnSize = 255;
}
writer.print(" (" + columnSize + ")");
}
writer.print(" " + nullString);
}
} finally {
rs2.close();
}
// Keys
try {
rs2 = dbMetaData.getPrimaryKeys(null, null, tableName);
String primaryKeyName = null;
StringBuffer primaryKeyColumns = new StringBuffer();
while (rs2.next()) {
String thisKeyName = rs2.getString("PK_NAME");
if ((thisKeyName != null && primaryKeyName == null) || (thisKeyName == null && primaryKeyName != null)
|| (thisKeyName != null && !thisKeyName.equals(primaryKeyName))
|| (primaryKeyName != null && !primaryKeyName.equals(thisKeyName))) {
if (primaryKeyColumns.length() > 0) {
writer.print(",\n PRIMARY KEY ");
if (primaryKeyName != null) {
writer.print(primaryKeyName);
}
writer.print("(" + primaryKeyColumns.toString() + ")");
}
primaryKeyColumns = new StringBuffer();
primaryKeyName = thisKeyName;
}
if (primaryKeyColumns.length() > 0) {
primaryKeyColumns.append(", ");
}
primaryKeyColumns.append(rs2.getString("COLUMN_NAME"));
}
if (primaryKeyColumns.length() > 0) {
writer.print(",\n PRIMARY KEY ");
if (primaryKeyName != null) {
writer.print(primaryKeyName);
}
writer.print(" (" + primaryKeyColumns.toString() + ")");
}
} finally {
rs2.close();
}
writer.println("\n);");
writer.println();
}
}
}
/**
* Dump table creation SQL. It is up to the caller to close the stream and connections when
* finished with.
*
* @param writer write SQL to this writer.
* @param conx connection to get data from
* @param quoteChar character to use to quote strings
* @param tables array of table names or <code>null</code> to dump all in
* database
* @throws Exception on any error
*/
public void dumpData(PrintWriter writer, JDBCConnectionImpl conx, char quoteChar, String[] tables) throws Exception {
Connection jdbcConnection = conx.getConnection();
DatabaseMetaData dbMetaData = jdbcConnection.getMetaData();
if (tables == null) {
ResultSet rs = dbMetaData.getTables(null, null, null, null);
try {
while (rs.next()) {
String tableName = rs.getString("TABLE_NAME");
String tableType = rs.getString("TABLE_TYPE");
if (tableType.equalsIgnoreCase("TABLE")) {
dumpData(writer, conx, quoteChar, new String[] { tableName });
}
}
} finally {
rs.close();
}
} else {
for (int i = 0; i < tables.length; i++) {
String tableName = tables[i];
log.info("Dumping data for table " + tableName);
// Data
PreparedStatement stmt = jdbcConnection.prepareStatement("SELECT * FROM " + tableName);
try {
ResultSet rs2 = stmt.executeQuery();
try {
while (rs2.next()) {
dumpRow(writer, rs2);
}
} finally {
rs2.close();
}
} finally {
stmt.close();
}
writer.println();
}
}
}
/**
* Dump a single result set row as an INSERT statement.
*
* @param writer
* @param resultSet
* @throws SQLException
*/
public void dumpRow(PrintWriter writer, ResultSet resultSet) throws SQLException {
String tableName = resultSet.getMetaData().getTableName(1);
int columnCount = resultSet.getMetaData().getColumnCount();
writer.print("INSERT INTO " + tableName + " VALUES (");
for (int j = 0; j < columnCount; j++) {
if (j > 0) {
writer.print(", ");
}
Object value = resultSet.getObject(j + 1);
if (value == null) {
writer.print("NULL");
} else {
String outputValue = value.toString();
if (value instanceof Number) {
writer.print(outputValue);
} else {
/*
* TODO
*
* This escaping will current only work
* for HSQLDB. This needs to be moved up
* into the engine.
*/
outputValue = outputValue.replaceAll("'", "''");
writer.print("'" + outputValue + "'");
}
}
}
writer.println(");");
}
}