/*
* :tabSize=2:indentSize=2:noTabs=true:
* :folding=explicit:collapseFolds=1:
*
* Copyright (C) 2009 Chaniel AB, Thomas Dilts 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., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA For more
* information, surf to www.lazy8.nu or email support@lazy8.nu
*/
package nu.lazy8.ledger.jdbc;
//{{{ imports
import java.io.DataInputStream;
import java.io.DataOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import java.util.zip.GZIPInputStream;
import java.util.zip.GZIPOutputStream;
import javax.crypto.Cipher;
import javax.crypto.CipherInputStream;
import javax.crypto.CipherOutputStream;
import javax.crypto.SecretKey;
import javax.crypto.SecretKeyFactory;
import javax.crypto.spec.PBEKeySpec;
import javax.crypto.spec.PBEParameterSpec;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import nu.lazy8.ledger.forms.DeleteCompany;
import nu.lazy8.ledger.messages.AccountTypeListChanged;
import nu.lazy8.ledger.messages.CompanyListChanged;
import nu.lazy8.ledger.messages.CustomerListChanged;
import nu.lazy8.util.gen.SetupInfo;
import nu.lazy8.util.gen.StringBinaryConverter;
import nu.lazy8.util.gen.SystemLog;
import nu.lazy8.util.gen.Translator;
import nu.lazy8.util.gen.WorkingDialog;
import org.gjt.sp.jedit.EditBus;
import org.gjt.sp.util.Log;
//}}}
/**
* Description of the Class
*
* @author Lazy Eight Data HB, Thomas Dilts
* @created den 5 mars 2002
*/
public class DatabaseBackup {
//{{{ members
public final static String[] sTables = {"APP.Account", "Company", "Amount", "Customer2",
"Activity2", "UniqNum", "AccountingPeriods", "AccountType"};
final static String filedescriptor = "Lazy8LedgerBackupVersionNumber";
final static String Companyfiledescriptor =
"Lazy8LedgerCompanyBackupVersionNumber";
final static double dVersionNumber = 2.0;
private int restoredCompId = 0;
// Salt
private final static byte[] salt = {
(byte) 0xc7, (byte) 0x73, (byte) 0x21, (byte) 0x8c,
(byte) 0x7e, (byte) 0xc8, (byte) 0xee, (byte) 0x99
};
// Iteration count
private final static int itCount = 20;
boolean isBadActivityTable = false;
Connection con;
//}}}
//{{{ +DatabaseBackup(Connection) : <init>
/**
* Constructor for the DatabaseBackup object
*
* @param conin Description of the Parameter
*/
public DatabaseBackup(Connection conin) {
con = conin;
}//}}}
//{{{ +BackupDatabase(String[], boolean) : void
/**
* Description of the Method
*
* @param sTables Description of the Parameter
* @param bBackupOneCompanyOnly Description of the Parameter
*/
public void BackupDatabase(String[] sTables, boolean bBackupOneCompanyOnly) {
/*
* get file name
* confirm good file
* open gziped data stream
* output in DataOutputStream format
* write version information string, a kind of file identifier
* write version double
* write the number of tables
* call savetable for each table to save each tables information
* close streams
*/
Integer iCompanyToBackup = new Integer(0);
if (bBackupOneCompanyOnly) {
CompanyDialog cd = new CompanyDialog(null,
Translator.getTranslation("Choose the company to backup"));
if (cd.bIsCancel) {
return;
}
iCompanyToBackup = (Integer) cd.cc.comboBox.getSelectedItemsKey();
}
//Get file name
JFileChooser fileDialog = new JFileChooser();
fileDialog.setDialogTitle(
Translator.getTranslation("Select a file name for the backup file"));
if (fileDialog.showSaveDialog(null) !=
JFileChooser.APPROVE_OPTION) {
return;
}
//check if the file exists
try {
InputStream in = new FileInputStream(fileDialog.getSelectedFile());
if (JOptionPane.CANCEL_OPTION == JOptionPane.showConfirmDialog(
null,
Translator.getTranslation("The file already exists. Continue?"),
"",
JOptionPane.OK_CANCEL_OPTION,
JOptionPane.QUESTION_MESSAGE)) {
return;
}
} catch (Exception ee) {
//all is well, the file does not exist
}
//now lets open the file for output
WorkingDialog workDialog = new WorkingDialog(null);
//open a gzipped file stream
try {
DataOutputStream doOut;
OutputStream out = new FileOutputStream(fileDialog.getSelectedFile());
GZIPOutputStream gzipout = new GZIPOutputStream(out);
OutputStream out2 = gzipout;
if (SetupInfo.getBoolProperty(SetupInfo.REQUIRE_BACKUP_PASSWORD)) {
out2 = createEncryptedOutputStream(gzipout);
}
doOut = new DataOutputStream(out2);
//save version
if (!bBackupOneCompanyOnly) {
doOut.writeUTF(filedescriptor);
} else {
doOut.writeUTF(Companyfiledescriptor);
}
doOut.writeDouble(dVersionNumber);
//save number of tables
doOut.writeInt(sTables.length);
//save each table
workDialog.setVisible(true);
workDialog.SetProgress(0);
for (int i = 0; i < sTables.length; i++) {
SaveTable(doOut, sTables[i], bBackupOneCompanyOnly, iCompanyToBackup);
workDialog.SetProgress((i * 100) / (sTables.length));
}
SystemLog.ErrorPrintln("before clossing work dialog");
workDialog.dispose();
SystemLog.ErrorPrintln("after clossing work dialog");
doOut.close();
SystemLog.ErrorPrintln("after closeing file");
} catch (Exception e2) {
e2.printStackTrace();
workDialog.dispose();
SystemLog.ErrorPrintln("FAILED trying to backup: " +
e2.getMessage());
JOptionPane.showMessageDialog(null,
Translator.getTranslation("Unable to create file. Error") +
" : " + e2.getMessage(),
Translator.getTranslation("Update not entered"),
JOptionPane.PLAIN_MESSAGE);
//try to delete the file
try {
fileDialog.getSelectedFile().delete();
return;
} catch (Exception eee) {
}
}
DataConnection.isDatabaseChanged = false;
}//}}}
//{{{ +RestoreDatabase(boolean, String, int, JFrame) : void
/**
* Description of the Method
*
* @param bRestoreOneCompanyOnly Description of the Parameter
* @param fileName Description of the Parameter
* @param iNewCompany Description of the Parameter
* @param view Description of the Parameter
*/
public void RestoreDatabase(boolean bRestoreOneCompanyOnly, String fileName, int iNewCompany, JFrame view) {
/*
* get file name
* confirm good file
* First read the entire file, checking that it is ok,
* then read it for real.....
* open gziped data stream
* input in DataInputStream format
* control version information string, a kind of file identifier
* control version double
* get the number of tables
* call restoretable for each table to save each tables information
* close streams
*/
//make sure they really want to do this
String sFile = null;
if (fileName == null) {
//Get file name
JFileChooser fileDialog = new JFileChooser();
fileDialog.setDialogTitle(
Translator.getTranslation("Select a file name for the backup file"));
if (fileDialog.showOpenDialog(null) !=
JFileChooser.APPROVE_OPTION) {
return;
}
sFile = fileDialog.getSelectedFile().getPath();
} else {
sFile = fileName;
}
Integer iCompanyToResoreTo = new Integer(0);
if (bRestoreOneCompanyOnly) {
if (fileName == null) {
int changeReply = JOptionPane.showConfirmDialog(
null,
Translator.getTranslation("Do you want to restore this company to an already existing company?")
+ "\n" + Translator.getTranslation("WARNING, the already existing company will get erased before the restore process."),
"",
JOptionPane.YES_NO_CANCEL_OPTION,
JOptionPane.QUESTION_MESSAGE);
if (JOptionPane.YES_OPTION == changeReply) {
CompanyDialog cd = new CompanyDialog(null,
Translator.getTranslation("Choose the company to restore to"));
if (cd.bIsCancel) {
return;
}
if (JOptionPane.CANCEL_OPTION == JOptionPane.showConfirmDialog(
null,
Translator.getTranslation("Are you sure you want to delete it?"),
"",
JOptionPane.OK_CANCEL_OPTION,
JOptionPane.QUESTION_MESSAGE)) {
return;
}
iCompanyToResoreTo = (Integer) cd.cc.comboBox.getSelectedItemsKey();
DeleteCompany.RemoveCompanyFromDatabase(iCompanyToResoreTo.intValue(), (javax.swing.JFrame) null, false);
} else if (JOptionPane.CANCEL_OPTION == changeReply) {
return;
}
} else {
iCompanyToResoreTo = new Integer(iNewCompany);
}
} else {
if (JOptionPane.CANCEL_OPTION == JOptionPane.showConfirmDialog(
null,
Translator.getTranslation("This will delete all data presently in the database. Continue?"),
"",
JOptionPane.OK_CANCEL_OPTION,
JOptionPane.QUESTION_MESSAGE)) {
return;
}
if (JOptionPane.CANCEL_OPTION == JOptionPane.showConfirmDialog(
null,
Translator.getTranslation("Are you sure you want to delete it?"),
"",
JOptionPane.OK_CANCEL_OPTION,
JOptionPane.QUESTION_MESSAGE)) {
return;
}
}
//check if the file exists
//this loop executes twice, first is just a test that the information
//seems valid. The second is the real read
WorkingDialog workDialog = new WorkingDialog(null);
workDialog.setVisible(true);
workDialog.SetProgress(0);
Cipher myCipher = null;
if (SetupInfo.getBoolProperty(SetupInfo.REQUIRE_BACKUP_PASSWORD)) {
myCipher = createCipher(Cipher.DECRYPT_MODE);
}
for (int iTestFile = 0; iTestFile < 2; iTestFile++) {
try {
InputStream in = new FileInputStream(sFile);
GZIPInputStream gzipin = new GZIPInputStream(in);
InputStream in2 = gzipin;
if (SetupInfo.getBoolProperty(SetupInfo.REQUIRE_BACKUP_PASSWORD)) {
in2 = new CipherInputStream(gzipin, myCipher);
}
DataInputStream doIn = new DataInputStream(in2);
//make sure file and descriptions are right
String sFileDesc = doIn.readUTF();
double dVersion = doIn.readDouble();
if (bRestoreOneCompanyOnly) {
if (Companyfiledescriptor.compareTo(sFileDesc) != 0
|| dVersion > dVersionNumber || dVersion < dVersionNumber
) {
SystemLog.ErrorPrintln("Not a valid file, descriptor =" +
sFileDesc + " should equal " + Companyfiledescriptor +
" and version number " + dVersion + " should equal " + dVersionNumber);
JOptionPane.showMessageDialog(null,
Translator.getTranslation("Invalid file name."),
Translator.getTranslation("Update not entered"),
JOptionPane.PLAIN_MESSAGE);
workDialog.dispose();
return;
}
} else
if (filedescriptor.compareTo(sFileDesc) != 0
|| dVersion > dVersionNumber || dVersion < dVersionNumber
) {
SystemLog.ErrorPrintln("Not a valid file, descriptor =" +
sFileDesc + " should equal " + filedescriptor +
" and version number " + dVersion + " should equal " + dVersionNumber);
JOptionPane.showMessageDialog(null,
Translator.getTranslation("Invalid file name."),
Translator.getTranslation("Update not entered"),
JOptionPane.PLAIN_MESSAGE);
workDialog.dispose();
return;
}
//get the number of tables
int iNumTables = doIn.readInt();
//call restoretable for each table to save each tables information
boolean bIsTestOnly = true;
if (iTestFile == 1) {
bIsTestOnly = false;
}
isBadActivityTable=false;
for (int i = 0; i < iNumTables; i++) {
RestoreTable(doIn, bIsTestOnly, bRestoreOneCompanyOnly,
iCompanyToResoreTo);
workDialog.SetProgress((iTestFile * iNumTables + i) * 100 / (2 * iNumTables));
}
if ( ! bRestoreOneCompanyOnly) restoredCompId=0;
if (iNumTables == (sTables.length - 1) && !bIsTestOnly) {
//this is an old import. We must add the AccountTypes Manually
DataConnection.AddDefaultAccountTypes(restoredCompId);
}
if (isBadActivityTable && !bIsTestOnly) {
//version 2.20, fix the PeriodId field in Activity2
lazy8ledger.Lazy8LedgerPlugin.AdjustAllPeriodIds(restoredCompId);
}
doIn.close();
EditBus.send(new CompanyListChanged(null, "no parameters"));
EditBus.send(new CustomerListChanged(null, "no parameters"));
EditBus.send(new AccountTypeListChanged(null, "no parameters"));
} catch (Exception ee) {
ee.printStackTrace();
workDialog.dispose();
//Cannot continue, the file does not exist
JOptionPane.showMessageDialog(null,
Translator.getTranslation("Invalid file name.") +
" : " + ee.getMessage(),
Translator.getTranslation("Update not entered"),
JOptionPane.PLAIN_MESSAGE);
SystemLog.ErrorPrintln("FAILED trying to backup: " +
ee.getMessage());
return;
}
}
workDialog.dispose();
}//}}}
//{{{ +RestoreTable(DataInputStream, boolean, boolean, Integer) : void
/**
* Description of the Method
*
* @param din Description of the Parameter
* @param bIsTestOnly Description of the Parameter
* @param bRestoreOneCompanyOnly Description of the Parameter
* @param iCompanyToResoreTo Description of the Parameter
* @exception java.io.IOException Description of the Exception
* @exception java.sql.SQLException Description of the Exception
*/
public void RestoreTable(DataInputStream din, boolean bIsTestOnly,
boolean bRestoreOneCompanyOnly, Integer iCompanyToResoreTo)
throws java.io.IOException, java.sql.SQLException {
int iCompIdFieldNumber = 0;
//get tablename
//get number of fields
//get Fieldname,fieldtype,fieldsize
//if all is well, remove all records in the existing table.
//get number of rows
//get each row
String sTableName = din.readUTF();
//fix for the new database mckoi94
if (sTableName.compareTo("Account") == 0) {
sTableName = "APP." + sTableName;
}
//fix for version 2.16
boolean isBadCustomerTable = false;
if (sTableName.compareTo("Customer") == 0) {
sTableName = "Customer2";
isBadCustomerTable = true;
}
//fix for version 2.20
boolean isLocalBadActivityTable = false;
if (sTableName.compareTo("Activity") == 0) {
sTableName = "Activity2";
isBadActivityTable = true;
isLocalBadActivityTable = true;
}
int iNumFields = din.readInt();
if (iNumFields == 0) {
return;
}
String[] FieldNames = new String[iNumFields];
int[] FieldTypes = new int[iNumFields];
int[] FieldSizes = new int[iNumFields];
for (int i = 0; i < iNumFields; i++) {
FieldNames[i] = din.readUTF();
if (FieldNames[i].compareTo("CompId") == 0) {
iCompIdFieldNumber = i;
}
FieldSizes[i] = din.readInt();
//not really needed
FieldTypes[i] = din.readInt();
}
if (bIsTestOnly) {
//now check if the table exists and that the fields are the same
//try to open the table to get the field names and types
ResultSet rs;
ResultSetMetaData rsmd;
PreparedStatement pps =
con.prepareStatement(DataConnection.filterSQL("SELECT * FROM " + sTableName));
rs = pps.executeQuery();
rsmd = rs.getMetaData();
for (int j = 0; j < iNumFields; j++) {
int i;
for (i = 1; i <= rsmd.getColumnCount(); i++) {
if (FieldNames[j].compareTo(rsmd.getColumnName(i)) == 0
// || FieldSizes[i-1] != rsmd.getColumnDisplaySize(i)
// || FieldTypes[i-1] != rsmd.getColumnType(i)
) {
break;
}
}
if (i > rsmd.getColumnCount()) {
throw new java.io.IOException("Field " + FieldNames[j] +
" is not in table " + sTableName);
}
}
} else {
//remove all records in the existing table.
String sDelete = new String("DELETE FROM " + sTableName);
SystemLog.ErrorPrintln("sDelete: " + sDelete);
if (bRestoreOneCompanyOnly && iCompanyToResoreTo.intValue() != 0) {
sDelete = sDelete + " WHERE CompId=" + iCompanyToResoreTo;
}
//also, find which field is the CompId
if (!bRestoreOneCompanyOnly || iCompanyToResoreTo.intValue() != 0) {
PreparedStatement pps =
con.prepareStatement(DataConnection.filterSQL(sDelete));
int iNumDeletedRows = pps.executeUpdate();
}
}
//get number of rows
int iNumRows = din.readInt();
//create the insert string
String sABunchOfQuestionMarks = "";
String sFields = "";
for (int i = 0; i < iNumFields; i++) {
sFields = sFields + sTableName + "." + FieldNames[i];
sABunchOfQuestionMarks = sABunchOfQuestionMarks + "?";
if (i != (iNumFields - 1)) {
sABunchOfQuestionMarks = sABunchOfQuestionMarks + ",";
sFields = sFields + ",";
}
}
//fix for version 2.16
if (isBadCustomerTable) {
//need to add the new field DefaultAcc
sFields = sFields + "," + sTableName + "." + "DefaultAcc";
sABunchOfQuestionMarks = sABunchOfQuestionMarks + "," + "?";
}
//fix for version 2.20
if (isLocalBadActivityTable) {
//need to add the new field DefaultAcc
sFields = sFields + "," + sTableName + "." + "PeriodId";
sABunchOfQuestionMarks = sABunchOfQuestionMarks + "," + "?";
}
String sInsert = "INSERT INTO " + sTableName + " (" + sFields + ") VALUES (" +
sABunchOfQuestionMarks + ")";
Log.log(Log.DEBUG, this, "sInsert: " + sInsert);
//get each row
for (int j = 0; j < iNumRows; j++) {
PreparedStatement pps =
con.prepareStatement(DataConnection.filterSQL(sInsert));
int i;
for (i = 1; i <= iNumFields; i++) {
switch (FieldTypes[i - 1]) {
case Types.VARCHAR:
pps.setString(i, din.readUTF());
break;
case Types.INTEGER:
int iValue = din.readInt();
if (bRestoreOneCompanyOnly && iCompanyToResoreTo.intValue() != 0
&& (i - 1) == iCompIdFieldNumber) {
iValue = iCompanyToResoreTo.intValue();
}
if ((i - 1) == iCompIdFieldNumber) {
restoredCompId = iValue;
}
pps.setInt(i, iValue);
break;
case Types.DOUBLE:
pps.setDouble(i, din.readDouble());
break;
case Types.DATE:
pps.setDate(i, new java.sql.Date(din.readLong()));
break;
default:
//this must be the blobs.....
pps.setBytes(i, StringBinaryConverter.StringToBinary(
din.readUTF()));
}
}
if (isBadCustomerTable || isLocalBadActivityTable) {
//need to add the new field DefaultAcc or PeriodId
pps.setInt(i, 0);
}
if (!bIsTestOnly) {
pps.executeUpdate();
}
}
}//}}}
//{{{ +SaveTable(DataOutputStream, String, boolean, Integer) : void
/**
* Description of the Method
*
* @param doOut Description of the Parameter
* @param sTableName Description of the Parameter
* @param bBackupOneCompanyOnly Description of the Parameter
* @param iCompanyToBackup Description of the Parameter
* @exception java.io.IOException Description of the Exception
* @exception java.sql.SQLException Description of the Exception
*/
public void SaveTable(DataOutputStream doOut, String sTableName,
boolean bBackupOneCompanyOnly, Integer iCompanyToBackup)
throws java.io.IOException, java.sql.SQLException {
//save tablename
//save number of fields
//save Fieldname,fieldtype,fieldsize
//save number of rows
//save each row
//we will catch ourselvs those errors that we can possibly recover
// from. other errors we let the calling funtion handle
//save tablename
doOut.writeUTF(sTableName);
//try to open the table to get the field names and types
ResultSet rs;
ResultSetMetaData rsmd;
try {
String sSelect = new String("SELECT * FROM " + sTableName);
if (bBackupOneCompanyOnly) {
sSelect = sSelect + " WHERE CompId=" + iCompanyToBackup;
}
PreparedStatement pps = con.prepareStatement(DataConnection.filterSQL(sSelect));
rs = pps.executeQuery();
rsmd = rs.getMetaData();
} catch (Exception e) {
SystemLog.ErrorPrintln("Could not open the table, Error:" + e.getMessage());
//write out that there are no colums and get out
doOut.writeInt(0);
return;
}
//save number of fields
doOut.writeInt(rsmd.getColumnCount());
//save Fieldname,fieldtype,fieldsize
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
doOut.writeUTF(rsmd.getColumnName(i));
doOut.writeInt(rsmd.getColumnDisplaySize(i));
//not really needed
doOut.writeInt(rsmd.getColumnType(i));
}
//save number of rows
//we need to count them first
int iNumOfRows = 0;
while (rs.next()) {
iNumOfRows++;
}
doOut.writeInt(iNumOfRows);
//save each row
rs.beforeFirst();
//position before the first record
int j = 0;
while (rs.next()) {
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
switch (rsmd.getColumnType(i)) {
case Types.VARCHAR:
if (rs.getString(i)==null)
doOut.writeUTF("");
else
doOut.writeUTF(rs.getString(i));
break;
case Types.INTEGER:
doOut.writeInt(rs.getInt(i));
break;
case Types.DOUBLE:
doOut.writeDouble(rs.getDouble(i));
break;
case Types.DATE:
if (rs.getDate(i)==null)
doOut.writeLong(0);
else
doOut.writeLong(rs.getDate(i).getTime());
break;
default:
//this must be the blobs.....
doOut.writeUTF(StringBinaryConverter.BinaryToString(
rs.getBytes(i)));
}
}
}
}//}}}
//{{{ -_createCipher(int)_ : Cipher
/**
* Description of the Method
*
* @param opmode Description of the Parameter
* @return Description of the Return Value
*/
private static Cipher createCipher(int opmode) {
PBEKeySpec pbeKeySpec;
PBEParameterSpec pbeParamSpec;
SecretKeyFactory keyFac;
// Create PBE parameter set
pbeParamSpec = new PBEParameterSpec(salt, itCount);
// Prompt user for encryption password.
// Collect user password as char array (using the
// "readPasswd" method from above), and convert
// it into a SecretKey object, using a PBE key
// factory.
DataConnection.PasswordDialog pDialog = new DataConnection.PasswordDialog(null, false, opmode == Cipher.ENCRYPT_MODE);
char[] dst = pDialog.jPass.getPassword();
pDialog.dispose();
pbeKeySpec = new PBEKeySpec(dst);
Cipher pbeCipher = null;
try {
keyFac = SecretKeyFactory.getInstance("PBEWithMD5AndDES");
SecretKey pbeKey = keyFac.generateSecret(pbeKeySpec);
// Create PBE Cipher
pbeCipher = Cipher.getInstance("PBEWithMD5AndDES");
// Initialize PBE Cipher with key and parameters
pbeCipher.init(opmode, pbeKey, pbeParamSpec);
} catch (Exception e) {
SystemLog.ErrorPrintln("Could not create cipher, Error:" + e.getMessage());
e.printStackTrace();
}
return pbeCipher;
}//}}}
//{{{ +_createEncryptedInputStream(InputStream)_ : InputStream
/**
* Description of the Method
*
* @param in Description of the Parameter
* @return Description of the Return Value
*/
public static InputStream createEncryptedInputStream(InputStream in) {
return new CipherInputStream(in, createCipher(Cipher.DECRYPT_MODE));
}//}}}
//{{{ +_createEncryptedOutputStream(OutputStream)_ : OutputStream
/**
* Description of the Method
*
* @param out Description of the Parameter
* @return Description of the Return Value
*/
public static OutputStream createEncryptedOutputStream(OutputStream out) {
return new CipherOutputStream(out, createCipher(Cipher.ENCRYPT_MODE));
}//}}}
}