package com.spun.util.database;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.sql.SQLException;
import java.sql.Statement;
import com.spun.util.DatabaseConfiguration;
import com.spun.util.DatabaseUtils;
import com.spun.util.logger.SimpleLogger;
public class DatabaseLifeCycleUtils
{
/***********************************************************************/
public static void backupDatabase(Statement stmt, String databaseName, DatabaseConfiguration config,
String fileName) throws Exception
{
switch (config.type)
{
case DatabaseUtils.SQLSERVER2005 :
case DatabaseUtils.SQLSERVER2000 :
case DatabaseUtils.SQLSERVER :
backupSQLServer(stmt, databaseName, fileName);
break;
case DatabaseUtils.POSTGRESQL :
backupPostgreSQL(databaseName, config, fileName);
break;
case DatabaseUtils.MY_SQL :
backupMySQL(databaseName, fileName);
break;
default :
throw new Error("Unhandled database type: " + DatabaseUtils.getDatabaseType(config.type));
}
}
/***********************************************************************/
private static void backupMySQL(String databaseName, String fileName) throws Exception
{
File file = new File(fileName);
if (!file.getParentFile().exists())
{
file.getParentFile().createNewFile();
}
String commandLine = "mysqldump -r " + fileName + " " + databaseName;
Process process = Runtime.getRuntime().exec(commandLine);
process.waitFor();
if (process.exitValue() != 0) { throw new Error(extractError(commandLine, process.getErrorStream())); }
}
/***********************************************************************/
private static void backupPostgreSQL(String databaseName, DatabaseConfiguration config, String fileName)
throws Exception
{
String commandLine = null;
try
{
File file = new File(fileName);
if (!file.getParentFile().exists())
{
file.getParentFile().createNewFile();
}
if (System.getProperty("os.name").indexOf("Windows") >= 0)
{
commandLine = "pg_dump --clean --username=" + config.getUserName() + " --file=\"" + file.getCanonicalPath()
+ "\" " + databaseName;
}
else
{
commandLine = "pg_dump --clean --file=" + file.getCanonicalPath() + " " + databaseName;
}
Process process = Runtime.getRuntime().exec(commandLine);
// check for a password prompt
if (getPasswordPrompt(process))
{
// send the password
sendPassword(process, config.getPassword());
}
process.waitFor();
if (process.exitValue() != 0) { throw new Error(extractError(commandLine, process.getErrorStream())); }
}
catch (IOException e)
{
SimpleLogger.variable("CommandLine", commandLine);
throw e;
}
}
private static boolean getPasswordPrompt(Process process) throws Exception
{
InputStream error = process.getErrorStream();
InputStream in = process.getInputStream();
int TIMEOUT = 3;
long timeOut = System.currentTimeMillis() + (TIMEOUT * 1000);
StringBuffer prompt = new StringBuffer();
while (System.currentTimeMillis() < timeOut)
{
if (in.available() == 0 && error.available() == 0)
{
Thread.sleep(500);
}
else
{
if (in.available() != 0)
{
prompt.append((char) in.read());
}
if (error.available() != 0)
{
prompt.append((char) error.read());
}
timeOut = System.currentTimeMillis() + (TIMEOUT * 1000);
}
}
SimpleLogger.variable("prompt", prompt.toString());
return prompt.toString().startsWith("Password");
}
private static void sendPassword(Process process, String password) throws Exception
{
BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(process.getOutputStream()));
writer.write(password);
writer.newLine();
writer.flush();
writer.close();
}
/***********************************************************************/
private static void backupSQLServer(Statement stmt, String databaseName, String fileName) throws SQLException
{
String sql = "BACKUP DATABASE " + databaseName + " TO DISK = '" + fileName + "'";
SimpleLogger.query("BACKUP", sql);
stmt.execute(sql);
}
/***********************************************************************/
public static void restoreDatabase(Statement stmt, String databaseName, DatabaseConfiguration config,
String fileName) throws Exception
{
switch (config.type)
{
case DatabaseUtils.SQLSERVER2005 :
case DatabaseUtils.SQLSERVER2000 :
case DatabaseUtils.SQLSERVER :
restoreSQLServer(stmt, databaseName, fileName);
break;
case DatabaseUtils.POSTGRESQL :
restorePostgreSQL(databaseName, config, fileName);
break;
case DatabaseUtils.MY_SQL :
restoreMySQL(stmt, databaseName, fileName);
break;
default :
throw new Error("Unhandled database type: " + DatabaseUtils.getDatabaseType(config.type));
}
}
/***********************************************************************/
private static void restoreMySQL(Statement stmt, String databaseName, String fileName) throws SQLException
{
String restoreCommand = "LOAD DATA INFILE '" + fileName + "' REPLACE ...";
SimpleLogger.query(restoreCommand);
stmt.execute(restoreCommand);
}
/***********************************************************************/
private static void restorePostgreSQL(String databaseName, DatabaseConfiguration config, String fileName)
throws Error, Exception
{
String commandLine;
if (System.getProperty("os.name").indexOf("Windows") >= 0)
{
commandLine = "psql -f " + fileName + " -U " + config.userName + " " + databaseName;
}
else
{
commandLine = "psql -f " + fileName + " " + databaseName;
}
SimpleLogger.event("RUNNING : " + commandLine);
Process process = Runtime.getRuntime().exec(commandLine);
if (getPasswordPrompt(process))
{
sendPassword(process, config.getPassword());
}
Thread.sleep(2000);
String string = null;
BufferedReader reader = new BufferedReader(new InputStreamReader(process.getInputStream()));
if (reader.ready())
{
while ((string = reader.readLine()) != null)
{
SimpleLogger.variable(string);
}
}
reader.close();
reader = new BufferedReader(new InputStreamReader(process.getErrorStream()));
if (reader.ready())
{
while ((string = reader.readLine()) != null)
{
SimpleLogger.variable(string);
}
}
process.waitFor();
reader.close();
if (process.exitValue() != 0) { throw new Error(extractError(commandLine, process.getErrorStream())); }
}
/***********************************************************************/
private static void restoreSQLServer(Statement stmt, String databaseName, String fileName) throws SQLException
{
stmt.execute("USE master");
String restoreCommand = "RESTORE DATABASE " + databaseName + " FROM DISK = '" + fileName + "'";
SimpleLogger.query(restoreCommand);
stmt.execute(restoreCommand);
stmt.execute("USE " + databaseName);
}
/***********************************************************************/
private static String extractError(String commandLine, InputStream error) throws Exception
{
/*
Process whoami = Runtime.getRuntime().exec("whoami");
whoami.waitFor();
String userName = extractText(whoami.getInputStream());*/
String errorText = extractText(error);
return "Error Executing '" + commandLine + /*"' AS USER '" + userName + */"'- " + errorText;
}
/***********************************************************************/
public static String extractText(InputStream inStream) throws IOException
{
StringBuffer errorBuffer = new StringBuffer();
BufferedReader in = new BufferedReader(new InputStreamReader(inStream));
while (in.ready())
{
errorBuffer.append(in.readLine());
}
return errorBuffer.toString();
}
/***********************************************************************/
public static void deleteTable(String tableName, int databaseType, Statement stmt) throws SQLException
{
switch (databaseType)
{
case DatabaseUtils.SQLSERVER2005 :
case DatabaseUtils.SQLSERVER2000 :
case DatabaseUtils.SQLSERVER :
deleteSQLServerTable(tableName, stmt);
break;
case DatabaseUtils.POSTGRESQL :
deletePostgreSQLTable(tableName, stmt);
break;
case DatabaseUtils.MY_SQL :
deleteMySqlTable(tableName, stmt);
break;
default :
throw new Error("Unhandled database type: " + DatabaseUtils.getDatabaseType(databaseType));
}
}
/***********************************************************************/
private static void deleteMySqlTable(String tableName, Statement stmt) throws SQLException
{
stmt.executeUpdate("TRUNCATE " + tableName);
}
/***********************************************************************/
private static void deletePostgreSQLTable(String tableName, Statement stmt) throws SQLException
{
stmt.executeUpdate("DELETE FROM " + tableName);
stmt.executeQuery("select setval('" + tableName + "_pkey_seq',1)");
}
/***********************************************************************/
public static void resetTableIndex(String tableName, int databaseType, Statement stmt) throws SQLException
{
switch (databaseType)
{
case DatabaseUtils.SQLSERVER2005 :
case DatabaseUtils.SQLSERVER2000 :
case DatabaseUtils.SQLSERVER :
break;
case DatabaseUtils.POSTGRESQL :
resetPostgreIndex(tableName, stmt);
break;
case DatabaseUtils.MY_SQL :
break;
default :
throw new Error("Unhandled database type: " + DatabaseUtils.getDatabaseType(databaseType));
}
}
/***********************************************************************/
private static void resetPostgreIndex(String tableName, Statement stmt) throws SQLException
{
String sql = "select setval('" + tableName + "_pkey_seq',(select max(pkey) + 1 from " + tableName + "))";
SimpleLogger.query("reset index", sql);
stmt.executeQuery(sql);
}
/***********************************************************************/
private static void deleteSQLServerTable(String tableName, Statement stmt) throws SQLException
{
stmt.executeUpdate("DELETE FROM " + tableName);
stmt.executeUpdate("DBCC CHECKIDENT('" + tableName + "', RESEED, 1)");
}
/***********************************************************************/
/***********************************************************************/
}