/*
* This software and supporting documentation were developed by
*
* Siemens Corporate Technology
* Competence Center Knowledge Management and Business Transformation
* D-81730 Munich, Germany
*
* Authors (representing a really great team ;-) )
* Stefan B. Augustin, Thorbj�rn Hansen, Manfred Langen
*
* This software is Open Source under GNU General Public License (GPL).
* Read the text of this license in LICENSE.TXT
* or look at www.opensource.org/licenses/
*
* Once more we emphasize, that:
* THIS SOFTWARE IS MADE AVAILABLE, AS IS, WITHOUT ANY WARRANTY
* REGARDING THE SOFTWARE, ITS PERFORMANCE OR
* FITNESS FOR ANY PARTICULAR USE, FREEDOM FROM ANY COMPUTER DISEASES OR
* ITS CONFORMITY TO ANY SPECIFICATION. THE ENTIRE RISK AS TO QUALITY AND
* PERFORMANCE OF THE SOFTWARE IS WITH THE USER.
*
*/
// TsqlBatch
// ************ package ****************************************************
package mod.tsql;
/** Generic class for db dumps.
* It writes the result to file in tsql dump format.
* It runs in an endless loop sleeping the determined interval
* in o:/KFM/config/all/TsqlBatch.properties.
*
* <usage>
* TsqlBatch tBatch = new TsqlBatch(); // make an instance
* tBatch.addDump("members", "where nick like 'tab.%'", "nick"); // add tables to dump,
* //define where conditions if necessary and order by clauses if necessary
* tBatch.execute(); // execute it
* </usage>
*
*/
// ************ imports ****************************************************
import KFM.Converter;
import java.util.*;
import java.sql.Timestamp;
import java.io.*;
import java.util.zip.*;
import KFM.DirNavigator.*;
import KFM.Smtp;
public class TsqlBatch implements FileWorker{
private Vector mDumps; // Holds a string array of tablenames, where conditions and order by clauses
private ZipOutputStream mZipOutputStream;
private String mTempDirPrefix; // prefix for temp directory the dumps will be stored temporarily
private String mTempDir; // the path relative to mTempDirPrefix to temp directory
private String mZipDir; // directory the zipped file will be stored
private String mMailHost; // mail host
private String mAdminEmail; // administrator that received the mail
private String mFrom; // email address from the sender
private String mSmtpMessage = null; // message that should be sent
private int mLoopInterval = -1; // defines the number of loops that will be processed.
// With a value of -1 the number of loops will be endless (the default).
private Properties mProps = null; // holds the properties e.g. database connection
public TsqlBatch(String tPropsPath)
throws FileNotFoundException,
IOException{
mDumps = new Vector();
mProps = new Properties();
mProps.load(new FileInputStream(tPropsPath));
}
/**
* Adds a dump condition to this instance.
*
* @param aTable Table that should be dumped
* @param aWhere where condition to restrict the query (null if not needed)
* @param aOrderBy order by clause to order the query (null if not needed)
*/
public void addDump(
String aTable,
String aWhere,
String aOrderBy)
{
if (aWhere == null)
aWhere = " ";
if (aOrderBy == null)
aOrderBy = " ";
String [] tStr = new String[3];
tStr[0] = aTable;
tStr[1] = aWhere;
tStr[2] = aOrderBy;
mDumps.addElement(tStr);
}
private Vector getDumps()
{
return mDumps;
}
/**
* Constructs a set of tsql commands to dump each table added by 'addDump'
* @param aTimestamp timestamp that should be added to the filename
* @return string of tsql queries separated by '\n'
*/
private String getDumpQueries(
String aTimestamp)
{
StringBuffer tBuf = new StringBuffer();
Vector tDumps = getDumps();
String tTempDir = mTempDirPrefix + "/" + mTempDir;
File tFile = new File(tTempDir);
if (!tFile.exists())
tFile.mkdirs();
for (int i = 0; i < tDumps.size(); i++)
{
String[] tTableAndWhere = (String[]) tDumps.elementAt(i);
String tTable = tTableAndWhere[0];
String tWhere = tTableAndWhere[1];
String tOrderBy = tTableAndWhere[2];
tBuf.append( "!log '" + tTempDir + "/dumpTable" + tTable.toUpperCase() + aTimestamp + ".tsql'\n");
tBuf.append("!echo !data table " + tTable + "\n");
tBuf.append("select * from " + tTable);
if (!tWhere.equals(""))
tBuf.append(" " + tWhere+ " ");
if (!tOrderBy.equals(" "))
tBuf.append(" order by " + tOrderBy);
tBuf.append("\n");
tBuf.append("!log console\n");
}
return tBuf.toString();
}
/**
* Method that stores all dumpfiles in a zip file and copies it to 'K' drive.
* @param aTimestamp
* @throws IOException
*/
public void makeZip(
String aTimestamp)
throws IOException
{
try{
String tZipFileName = mZipDir + "/DumpTables" + aTimestamp + ".zip";
File tFile = new File(tZipFileName);
FileOutputStream tFileOutputStream = new FileOutputStream(tFile.getPath());
mZipOutputStream = new ZipOutputStream(tFileOutputStream);
DirNavigator tNavigator = new DirNavigator(mTempDirPrefix + "/" + mTempDir, this);
tNavigator.unsetRecursively();
tNavigator.traverse();
mZipOutputStream.flush();
}
finally{
if (mZipOutputStream != null)
mZipOutputStream.close();
}
}
/**
* Steps through the temporary directory and stores all entries in zip file.
* The zip file is marked with the current date do distinguish the different zip files.
* @param aFile
*/
public void workFile (File aFile)
{
try{
String tPrefix = mTempDirPrefix + "/";
String tPath = aFile.getPath().substring(tPrefix.length());
ZipEntry tZipEntry = new ZipEntry(tPath);
tZipEntry.setMethod(ZipEntry.DEFLATED);
mZipOutputStream.putNextEntry(tZipEntry);
String tContent = KFM.File.FileUtils.readWholeTextFile(aFile.getPath());
mZipOutputStream.write(tContent.getBytes(), 0, tContent.length());
aFile.delete();
}
catch (IOException e)
{
e.printStackTrace();
System.exit(-1);
}
}
/**
* Executes the program.
* First initializes the db connection given in 'o:/KFM/config/all/TsqlBatch.properties'.
* Second it starts an endless loop executing the dumps and then sleeping the period
* given in property 'dumpInterval' of 'o:/KFM/config/all/TsqlBatch.properties'.
*/
public void execute ()
{
try{
long tInterval = Long.parseLong(mProps.getProperty("dumpInterval"));
String tConnection = mProps.getProperty("JdbcConnection");
String tUser = mProps.getProperty("JdbcUser");
String tPassword = mProps.getProperty("JdbcPassword");
mTempDir = mProps.getProperty("tempDir");
mTempDirPrefix = mProps.getProperty("tempDirPrefix");
mZipDir = mProps.getProperty("zipDir");
mAdminEmail = mProps.getProperty("AdminEmail");
mMailHost = mProps.getProperty("MailHost");
mFrom = mProps.getProperty("From");
TsqlInterpreter tTsql = new TsqlInterpreter();
int tLoopCounter = 0;
while (true)
{
// initialize db connection right before using to prevent
// long lasted connections
// initialize db connection
tTsql.initialize(new String[] {
"-S", tConnection,
"-U", tUser,
"-P", tPassword
});
// get the current timestamp to mark each dump file with the current date
Timestamp tTimeStamp = new Timestamp(System.currentTimeMillis());
String ttimestr = tTimeStamp.toString();
// extract the current date from timestamp
String tDate = ttimestr.substring(0, ttimestr.indexOf(" "));
// extract the current time (only hours and minutes)
String tTime = ttimestr.substring(ttimestr.indexOf(" ") + 1, ttimestr.indexOf(" ") + 6);
tTime = Converter.replaceString(":", "_", tTime);
// build a new string out of date and time because the method
// 'toString' of timestamp contains spaces so that we can't use it for filename creation.
ttimestr = tDate + "_" + tTime;
// now execute commands
String tQueryString = "!set format data\n"
+ getDumpQueries(ttimestr)
+ "!echo End of dump\n";
tTsql.execute(tQueryString);
tTsql.closeConnections();
this.makeZip(ttimestr);
// inform user by email if requested
if (mSmtpMessage != null)
{
Smtp tMail = new Smtp(mMailHost, mAdminEmail, mFrom, mSmtpMessage, true);
}
// if the requested number of loops are processed, terminate
tLoopCounter ++;
if (mLoopInterval != -1 && tLoopCounter >= mLoopInterval)
break;
else // wait for the next interval
Thread.sleep(tInterval);
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
/**
* Here you can define the number of loops that will be executed.
* If you don't define a value (or don't call this method) be process runs in an endless loop.
* @param aInterval
*/
public void setLoopInterval(
int aInterval)
{
mLoopInterval = aInterval;
}
/**
* Here you can define the message that will be sent to you to inform you the update was
* made correctly. It will be sent after each successfull update so if you haven't get the message
* one day you have to make sure the process is still running.
*/
public void setSmtpMessage(String aMessage)
{
mSmtpMessage = "Subject: " + aMessage + "\n\n" + aMessage + "\n";
}
/**
* This is only an example to run this class.
* In the example the tables members, desktops, gadgetdefs and gadgetroles are dumped.
* @param args
*/
public static void main (String [] args)
{
try{
if (args == null || args.length < 1)
System.out.println("Usage: java mod.tsql.TsqlBatch <path to property TsqlBatch.properties>");
TsqlBatch tBatch = new TsqlBatch(args[0]);
tBatch.addDump("members", "where nick like 'tab.%'", "nick");
tBatch.addDump("desktops", "where members_id in (select id from members where nick like 'tab.%')", "members_id");
tBatch.addDump("gadgetdefs", null, "id");
tBatch.addDump("gadgetroles", null, "gadgetdefs_id");
tBatch.addDump("category", null, null);
tBatch.addDump("link", null, null);
tBatch.addDump("catlink", null, null);
tBatch.addDump("link_attribute", null, null);
tBatch.addDump("link_content", null, null);
tBatch.addDump("link_keyword", null, null);
tBatch.addDump("category_attribute", null, null);
tBatch.addDump("category_en_keyword", null, null);
tBatch.addDump("category_keyword", null, null);
tBatch.setSmtpMessage("Database dump for tabs, gadgetdefs and gadgetroles actualized");
tBatch.setLoopInterval(1);
tBatch.execute();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}