package zeta.tool;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.zip.Deflater;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import zeta.util.DatabaseUtils;
import zeta.util.StreamUtils;
import zeta.util.ThrowableHandler;
public class ZetaBackup {
private static int maxCharsPerInsert = 25000;
private static String backupFolder = "backup";
private static final int INSERT = 0;
private static final int UPDATE = 1;
public static void main(String[] args) {
if (args.length == 3 && args[0].equals("r")) {
restore(args[1], args[2]);
return;
} else if (args.length == 2 && args[0].equals("e")) {
errorTable(Integer.parseInt(args[1]));
return;
} else if (args.length == 3 && args[0].equals("u")) {
backupFolder = ".";
try {
backupTable(args[1], UPDATE, args[2]);
} catch (Exception e) {
ThrowableHandler.handle(e);
}
return;
} else if (args.length == 1 && args[0].equals("b")) {
SimpleDateFormat sqlFormatter = new SimpleDateFormat("yyyy-MM-dd");
backup(sqlFormatter.format(new Date()));
return;
}
System.err.println("USAGE: java zeta.tool.ZetaBackup r <user> <password>\n"
+ " java zeta.tool.ZetaBackup e <server id>\n"
+ " java zeta.tool.ZetaBackup u <table> <append sql>\n"
+ " java zeta.tool.ZetaBackup b");
}
static void backup(String date) {
ZipOutputStream zip = null;
try {
File f = new File(backupFolder);
f.mkdir();
File[] list = f.listFiles();
if (list != null) {
for (int i = 0; i < list.length; ++i) {
if (list[i].getName().endsWith(".bak")) {
list[i].delete();
}
}
for (int i = 0; i < list.length; ++i) {
if (list[i].getName().endsWith(".sql")) {
list[i].renameTo(new File(list[i].getPath() + ".bak"));
}
}
}
backupTable("zeta.computation", INSERT, "");
reorgTable("zeta.computation", null);
backupTable("zeta.result", INSERT, "");
reorgTable("zeta.result", null);
backupTable("zeta.recomputation", INSERT, "");
reorgTable("zeta.recomputation", null);
backupTable("zeta.found", INSERT, "");
reorgTable("zeta.found", "zeta.found_type");
backupTable("zeta.workstation", INSERT, "");
reorgTable("zeta.workstation", null);
backupTable("zeta.user", INSERT, "");
reorgTable("zeta.user", null);
backupTable("zeta.server_range", INSERT, "");
reorgTable("zeta.server_range", null);
backupTable("zeta.server_synchronization", INSERT, "");
reorgTable("zeta.server_synchronization", null);
backupTable("zeta.parameter", INSERT, "");
reorgTable("zeta.parameter", null);
backupTable("zeta.error", INSERT, "");
reorgTable("zeta.error", null);
backupTable("zeta.task", INSERT, "");
backupTable("zeta.program", INSERT, "");
reorgTable("zeta.program", null);
reorgTable("zeta.approve", "zeta.approve_key");
zip = new ZipOutputStream(new FileOutputStream(backupFolder + '/' + date + ".zip"));
zip.setLevel(Deflater.BEST_COMPRESSION);
writeFile("zeta.computation.sql", zip);
writeFile("zeta.result.sql", zip);
writeFile("zeta.recomputation.sql", zip);
writeFile("zeta.found.sql", zip);
writeFile("zeta.workstation.sql", zip);
writeFile("zeta.user.sql", zip);
writeFile("zeta.server_range.sql", zip);
writeFile("zeta.parameter.sql", zip);
writeFile("zeta.error.sql", zip);
writeFile("zeta.task.sql", zip);
DatabaseUtils.db2CLP("CONNECT TO zeta;\nREORGCHK UPDATE STATISTICS > reorgchk.txt;\nREORGCHK UPDATE STATISTICS ON TABLE SYSTEM >> reorgchk.txt;\nCONNECT RESET;\n"); } catch (Exception e) {
ThrowableHandler.handle(e);
} finally {
StreamUtils.close(zip);
}
}
static void restore(String user, String password) {
Connection connection = null;
Statement stmt = null;
PreparedStatement pStmt = null;
BufferedReader reader = null;
try {
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
connection = DriverManager.getConnection("jdbc:db2:zeta", user, password);
stmt = connection.createStatement();
File file = new File(backupFolder);
File[] list = file.listFiles();
if (list != null) {
for (int i = 0; i < list.length; ++i) {
String filename = list[i].getName();
if (filename.startsWith("zeta.") && filename.endsWith(".sql")) {
reader = new BufferedReader(new FileReader(list[i]));
String tablename = filename.substring(0, filename.length()-4);
System.out.println("restore table " + tablename);
stmt.executeUpdate("DELETE FROM " + tablename);
if (!tablename.equals("zeta.program")) {
StringBuffer sql = new StringBuffer(100000);
while (true) {
String line = reader.readLine();
if (line == null) {
break;
}
if (line.length() > 0) {
sql.append(line);
if (line.charAt(line.length()-1) == ';') {
stmt.executeUpdate(sql.substring(0, sql.length()-1));
sql.delete(0, sql.length());
}
}
}
} else {
String sql = reader.readLine();
for (int idx = 1;; ++idx) {
String s = reader.readLine();
if (s == null) break;
s = s.substring(0, s.indexOf(",NULL"));
pStmt = connection.prepareStatement(sql + s + ",?)");
pStmt.setBytes(1, StreamUtils.getFile(backupFolder + "/zeta.program."+idx+".zip", false, false));
if (pStmt.executeUpdate() != 1) {
System.err.println("Error at idx=" + idx);
}
DatabaseUtils.close(pStmt);
pStmt = null;
}
}
StreamUtils.close(reader);
reader = null;
}
}
}
} catch (IOException ioe) {
ThrowableHandler.handle(ioe);
} catch (Exception e) {
ThrowableHandler.handle(e);
} finally {
DatabaseUtils.close(stmt);
DatabaseUtils.close(pStmt);
DatabaseUtils.close(connection);
StreamUtils.close(reader);
}
}
private static void errorTable(int serverId) {
Connection connection = null;
Statement stmt = null;
BufferedWriter writer = null;
try {
File file = new File("error.sql");
file.delete();
connection = GetData.getConnection();
stmt = connection.createStatement();
writer = new BufferedWriter(new FileWriter(file));
StringBuffer buffer = new StringBuffer(1000);
ResultSet rs = stmt.executeQuery("SELECT timestamp,sql_statement FROM zeta.error WHERE server_id=" + serverId + " ORDER BY timestamp");
while (rs.next()) {
buffer.delete(0, buffer.length());
buffer.append(rs.getString(2));
buffer.append(";\nDELETE FROM zeta.error WHERE timestamp=\'" + rs.getTimestamp(1) + "\';\n");
writer.write(buffer.toString());
}
rs.close();
} catch (Exception e) {
ThrowableHandler.handle(e);
} finally {
StreamUtils.close(writer);
DatabaseUtils.close(stmt);
DatabaseUtils.close(connection);
}
}
private static void backupTable(String table, int backupType, String appendSQL) throws Exception {
List columns = new ArrayList(15);
int countPrimaryKey = 0;
if (table.equals("zeta.computation")) {
countPrimaryKey = 2;
columns.add(new BackupTableColumn("task_id", Integer.class));
columns.add(new BackupTableColumn("work_unit_id", Long.class));
columns.add(new BackupTableColumn("range", Integer.class));
columns.add(new BackupTableColumn("server_id", Integer.class));
columns.add(new BackupTableColumn("workstation_id", Integer.class));
columns.add(new BackupTableColumn("user_id", Integer.class));
columns.add(new BackupTableColumn("version", String.class));
columns.add(new BackupTableColumn("start", Timestamp.class));
columns.add(new BackupTableColumn("parameters", String.class));
} else if (table.equals("zeta.result")) {
countPrimaryKey = 2;
columns.add(new BackupTableColumn("task_id", Integer.class));
columns.add(new BackupTableColumn("work_unit_id", Long.class));
columns.add(new BackupTableColumn("stop", Timestamp.class));
} else if (table.equals("zeta.recomputation")) {
countPrimaryKey = 2;
columns.add(new BackupTableColumn("task_id", Integer.class));
columns.add(new BackupTableColumn("work_unit_id", Long.class));
columns.add(new BackupTableColumn("range", Integer.class));
columns.add(new BackupTableColumn("server_id", Integer.class));
columns.add(new BackupTableColumn("workstation_id", Integer.class));
columns.add(new BackupTableColumn("user_id", Integer.class));
columns.add(new BackupTableColumn("version", String.class));
columns.add(new BackupTableColumn("count", Integer.class));
columns.add(new BackupTableColumn("reason", String.class));
columns.add(new BackupTableColumn("start", Timestamp.class));
columns.add(new BackupTableColumn("stop", Timestamp.class));
columns.add(new BackupTableColumn("parameters", String.class));
} else if (table.equals("zeta.found")) {
countPrimaryKey = 4;
columns.add(new BackupTableColumn("task_id", Integer.class));
columns.add(new BackupTableColumn("work_unit_id", Long.class));
columns.add(new BackupTableColumn("type", String.class));
columns.add(new BackupTableColumn("found", String.class));
columns.add(new BackupTableColumn("approved_YN", String.class));
columns.add(new BackupTableColumn("timestamp", Timestamp.class));
} else if (table.equals("zeta.workstation")) {
countPrimaryKey = 2;
columns.add(new BackupTableColumn("id", Integer.class));
columns.add(new BackupTableColumn("server_id", Integer.class));
columns.add(new BackupTableColumn("active_YN", String.class));
columns.add(new BackupTableColumn("key", String.class));
columns.add(new BackupTableColumn("hostname", String.class));
columns.add(new BackupTableColumn("hostaddress", String.class));
columns.add(new BackupTableColumn("last_update", Timestamp.class));
columns.add(new BackupTableColumn("os_name", String.class));
columns.add(new BackupTableColumn("os_version", String.class));
columns.add(new BackupTableColumn("os_arch", String.class));
columns.add(new BackupTableColumn("processors", Integer.class));
columns.add(new BackupTableColumn("processors_approved", Integer.class));
columns.add(new BackupTableColumn("number_of_redistributions", Integer.class));
columns.add(new BackupTableColumn("last_redistributed_work_unit", Long.class));
columns.add(new BackupTableColumn("last_redistributed_timestamp", Timestamp.class));
} else if (table.equals("zeta.user")) {
countPrimaryKey = 2;
columns.add(new BackupTableColumn("id", Integer.class));
columns.add(new BackupTableColumn("server_id", Integer.class));
columns.add(new BackupTableColumn("fail", Integer.class));
columns.add(new BackupTableColumn("trust", Integer.class));
columns.add(new BackupTableColumn("active_YN", String.class));
columns.add(new BackupTableColumn("recomputation_YN", String.class));
columns.add(new BackupTableColumn("name", String.class));
columns.add(new BackupTableColumn("email", String.class));
columns.add(new BackupTableColumn("email_valid_YN", String.class));
columns.add(new BackupTableColumn("team_name", String.class));
columns.add(new BackupTableColumn("join_in_team", Timestamp.class));
columns.add(new BackupTableColumn("number_of_redistributions", Integer.class));
columns.add(new BackupTableColumn("last_redistributed_work_unit", Long.class));
columns.add(new BackupTableColumn("last_redistributed_timestamp", Timestamp.class));
columns.add(new BackupTableColumn("properties", String.class));
} else if (table.equals("zeta.server_range")) {
countPrimaryKey = 3;
columns.add(new BackupTableColumn("server_id", Integer.class));
columns.add(new BackupTableColumn("task_id", Integer.class));
columns.add(new BackupTableColumn("work_unit_id", Long.class));
columns.add(new BackupTableColumn("range", Long.class));
columns.add(new BackupTableColumn("start", Timestamp.class));
} else if (table.equals("zeta.server_synchronization")) {
countPrimaryKey = 2;
columns.add(new BackupTableColumn("server_id", Integer.class));
columns.add(new BackupTableColumn("timestamp", Timestamp.class));
columns.add(new BackupTableColumn("sql_statement", String.class));
} else if (table.equals("zeta.parameter")) {
countPrimaryKey = 2;
columns.add(new BackupTableColumn("task_id", Integer.class));
columns.add(new BackupTableColumn("parameter", String.class));
columns.add(new BackupTableColumn("value", String.class));
} else if (table.equals("zeta.error")) {
countPrimaryKey = 2;
columns.add(new BackupTableColumn("server_id", Integer.class));
columns.add(new BackupTableColumn("timestamp", Timestamp.class));
columns.add(new BackupTableColumn("sql_statement", String.class));
} else if (table.equals("zeta.task")) {
countPrimaryKey = 1;
columns.add(new BackupTableColumn("id", Integer.class));
columns.add(new BackupTableColumn("name", String.class));
columns.add(new BackupTableColumn("encryption_class", String.class));
columns.add(new BackupTableColumn("encryption_signature", String.class));
columns.add(new BackupTableColumn("decryption_number", String.class));
columns.add(new BackupTableColumn("request_processor", String.class));
columns.add(new BackupTableColumn("result_processor", String.class));
} else if (table.equals("zeta.program")) {
countPrimaryKey = 5;
columns.add(new BackupTableColumn("task_id", Integer.class));
columns.add(new BackupTableColumn("name", String.class));
columns.add(new BackupTableColumn("os_name", String.class));
columns.add(new BackupTableColumn("os_version", String.class));
columns.add(new BackupTableColumn("os_arch", String.class));
columns.add(new BackupTableColumn("version", String.class));
columns.add(new BackupTableColumn("compressed_YN", String.class));
columns.add(new BackupTableColumn("last_update", Timestamp.class));
columns.add(new BackupTableColumn("program", Blob.class));
}
if (columns.size() > 0) {
BackupTableColumn[] c = new BackupTableColumn[columns.size()];
for (int i = 0; i < c.length; ++i) {
c[i] = (BackupTableColumn)columns.get(i);
}
backupTable(table, c, backupType, appendSQL, countPrimaryKey);
} else {
throw new IllegalArgumentException("table '" + table + "' is not defined");
}
}
private static void backupTable(String table, BackupTableColumn[] columns, int backupType, String appendSQL, int countPrimaryKey) throws Exception {
int originMaxCharsPerInsert = maxCharsPerInsert;
StringBuffer s = new StringBuffer(30000);
s.append("SELECT ");
final int l = columns.length;
for (int i = 0; i < l; ++i) {
if (columns[i].type != null) {
if (i > 0) {
s.append(',');
}
s.append(columns[i].column);
}
}
s.append(" FROM ");
s.append(table);
if (appendSQL != null && appendSQL.length() > 0) {
s.append(' ');
s.append(appendSQL);
}
if (backupType == UPDATE) {
s.append(" ORDER BY ");
for (int i = 0; i < countPrimaryKey; ++i) {
if (i > 0) {
s.append(',');
}
s.append(columns[i].column);
}
}
String select = s.toString();
s.delete(0, s.length());
if (backupType == INSERT) {
s.append("INSERT INTO ");
s.append(table);
s.append(" (");
} else if (backupType == UPDATE) {
if (countPrimaryKey < 1) {
throw new IllegalArgumentException("countPrimaryKey undefined");
}
maxCharsPerInsert = 1;
s.append("UPDATE ");
s.append(table);
s.append(" SET (");
} else {
throw new IllegalArgumentException();
}
boolean first = true;
for (int i = (backupType != UPDATE)? 0 : countPrimaryKey; i < l; ++i) {
if (!first) {
s.append(',');
}
first = false;
s.append(columns[i].column);
}
if (backupType == INSERT) {
s.append(") VALUES\n");
} else {
s.append(")=\n");
}
String insert = s.toString();
s.delete(0, s.length());
Connection connection = GetData.getConnection();
Statement stmt = null;
BufferedWriter writer = null;
try {
stmt = connection.createStatement();
writer = new BufferedWriter(new FileWriter(backupFolder + '/' + table + ((backupType > 0)? Integer.toString(backupType) : "") + ".sql"));
int charsPerInsert = maxCharsPerInsert;
System.out.println(select);
ResultSet rs = stmt.executeQuery(select);
int idx = 0;
while (rs.next()) {
++idx;
if (charsPerInsert >= maxCharsPerInsert) {
if (s.length() > 0) {
if (backupType == UPDATE) {
s.append(" WHERE ");
int posValue = insert.length();
while (posValue < s.length() && s.charAt(posValue) != '(') {
++posValue;
}
int startValue = posValue+1;
for (int i = 0; i < countPrimaryKey && posValue > 0 && posValue+1 < s.length(); ++i) {
s.append(columns[i].column);
s.append('=');
if (columns[i].type == Integer.class || columns[i].type == Long.class || columns[i].type == String.class || columns[i].type == Timestamp.class) {
int posValueEnd = ++posValue;
while (posValueEnd < s.length() && s.charAt(posValueEnd) != ',') {
++posValueEnd;
}
s.append(s.substring(posValue, posValueEnd));
posValue = posValueEnd;
} else {
throw new IllegalArgumentException("primary key type not supported");
}
if (i+1 < countPrimaryKey) {
s.append(" AND ");
}
}
s.delete(startValue, posValue+1);
}
s.append(";\n");
writer.write(s.toString());
}
s.delete(0, s.length());
charsPerInsert = 0;
}
final int size = s.length();
s.append((charsPerInsert == 0)? insert : ",\n");
s.append(" (");
for (int i = 0; i < l; ++i) {
if (i > 0) {
s.append(',');
}
if (columns[i].type == Integer.class) {
s.append(rs.getInt(i+1));
} else if (columns[i].type == String.class) {
String s2 = rs.getString(i+1);
if (s2 == null) {
s.append("NULL");
} else {
s.append('\'');
s.append(dublicateChar('\'', s2));
s.append('\'');
}
} else if (columns[i].type == Timestamp.class) {
Timestamp t = rs.getTimestamp(i+1);
if (t == null) {
s.append("NULL");
} else {
s.append('\'');
s.append(t);
s.append('\'');
}
} else if (columns[i].type == Long.class) {
s.append(rs.getLong(i+1));
} else if (columns[i].type == Blob.class) {
s.append("NULL");
StreamUtils.writeData(rs.getBinaryStream(i+1), new FileOutputStream(backupFolder + '/' + table + '.' + idx + ".zip"), true, true);
} else if (columns[i].type == null) {
s.append("NULL");
} else throw new SQLException("Not implemented!");
}
s.append(')');
charsPerInsert += s.length()-size;
}
if (s.length() > 0) {
if (backupType == UPDATE) {
s.append(" WHERE ");
int posValue = insert.length();
while (posValue < s.length() && s.charAt(posValue) != '(') {
++posValue;
}
int startValue = posValue+1;
for (int i = 0; i < countPrimaryKey && posValue > 0 && posValue+1 < s.length(); ++i) {
s.append(columns[i].column);
s.append('=');
if (columns[i].type == Integer.class || columns[i].type == Long.class || columns[i].type == String.class || columns[i].type == Timestamp.class) {
int posValueEnd = ++posValue;
while (posValueEnd < s.length() && s.charAt(posValueEnd) != ',') {
++posValueEnd;
}
s.append(s.substring(posValue, posValueEnd));
posValue = posValueEnd;
} else {
throw new IllegalArgumentException("primary key type not supported");
}
if (i+1 < countPrimaryKey) {
s.append(" AND ");
}
}
s.delete(startValue, posValue+1);
}
s.append(";\n");
writer.write(s.toString());
}
rs.close();
} finally {
StreamUtils.close(writer);
DatabaseUtils.close(stmt);
DatabaseUtils.close(connection);
}
maxCharsPerInsert = originMaxCharsPerInsert;
}
private static void reorgTable(String table, String primaryKeyName) throws Exception {
Connection con = GetData.getConnection();
try {
String origTable = table;
String schema = "";
int idx = table.indexOf('.');
if (idx > 0) {
schema = table.substring(0, idx);
table = table.substring(idx+1);
}
if (primaryKeyName == null) {
DatabaseMetaData dmd = con.getMetaData();
ResultSet rs = dmd.getPrimaryKeys(null, schema, table);
if (rs.next()) {
primaryKeyName = rs.getString(6);
}
rs.close();
}
StringBuffer sql = new StringBuffer(300);
sql.append("CONNECT TO zeta;\n"); sql.append("REORG TABLE ");
sql.append(origTable);
if (primaryKeyName != null) {
idx = primaryKeyName.indexOf('.');
if (idx == 0) {
primaryKeyName = "sysibm." + primaryKeyName;
}
sql.append(" INDEX ");
sql.append(primaryKeyName);
}
sql.append(";\n");
sql.append("-- REORG INDEXES ALL FOR TABLE ");
sql.append(origTable);
sql.append(";\n");
sql.append("RUNSTATS ON TABLE ");
sql.append(origTable);
sql.append(" AND DETAILED INDEXES ALL SHRLEVEL CHANGE;\n");
sql.append("CONNECT RESET;\n");
DatabaseUtils.db2CLP(sql.toString());
} finally {
DatabaseUtils.close(con);
}
}
private static void writeFile(String filename, ZipOutputStream zip) throws IOException {
filename = backupFolder + '/' + filename;
zip.putNextEntry(new ZipEntry(filename));
StreamUtils.writeData(new FileInputStream(filename), zip, true, false);
}
private static String dublicateChar(char c, String s) {
int i = s.indexOf(c);
if (i >= 0) {
int j = 0;
StringBuffer buffer = new StringBuffer(s.length()+100);
do {
buffer.append(s.substring(j, i+1));
buffer.append(c);
j = i+1;
i = s.indexOf(c, j);
} while (i >= 0);
if (j < s.length()) {
buffer.append(s.substring(j));
}
s = buffer.toString();
}
return s;
}
static class BackupTableColumn {
BackupTableColumn(String column, Class type) {
this.type = type;
this.column = column;
}
Class type;
String column;
}
}