/********************************************************* begin of preamble
**
** Copyright (C) 2003-2010 Software- und Organisations-Service GmbH.
** All rights reserved.
**
** This file may be used under the terms of either the
**
** GNU General Public License version 2.0 (GPL)
**
** as published by the Free Software Foundation
** http://www.gnu.org/licenses/gpl-2.0.txt and appearing in the file
** LICENSE.GPL included in the packaging of this file.
**
** or the
**
** Agreement for Purchase and Licensing
**
** as offered by Software- und Organisations-Service GmbH
** in the respective terms of supply that ship with this file.
**
** THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS
** IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
** THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
** PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS
** BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
** CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
** SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
** INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
** CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
** ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
** POSSIBILITY OF SUCH DAMAGE.
********************************************************** end of preamble*/
package sos.scheduler.managed;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Properties;
import java.util.Random;
import java.util.Vector;
import sos.connection.SOSConnection;
import sos.spooler.Order;
import sos.spooler.Variable_set;
import sos.util.SOSArguments;
import sos.util.SOSLogger;
import sos.util.SOSSchedulerLogger;
/**
* This class executes database statements for managed orders. It
* can be extended to create own database jobs (e.g do further processing
* with the results of the statement).<br/>
* In that case the executeStatements function has to be overwritten.
*
* @see JobSchedulerManagedDatabaseJob#executeStatements(SOSConnection, String)
* @author andreas.pueschel@sos-berlin.com
* @since 1.0 2005-03-05
*/
public class JobSchedulerManagedDatabaseJob extends JobSchedulerManagedJob {
// was this job generated by SchedulerManagedUserJob?
private boolean userJob = false;
private Random rand = new Random();
// IP adress of the Job Scheduler host
private String ip = null;
// Revoke statements for later deletion of the user (mysql user jobs only)
private String revokeUser = "";
private String revokeUserQuoted = "";
private boolean autoCommit = false;
public boolean spooler_init() {
if (!super.spooler_init()) return false;
return true;
}
/**
* processing
*/
public boolean spooler_process() {
SOSConnection localConnection = null;
Order order = null;
String command = "";
orderPayload = null;
Variable_set realOrderParams = null;
boolean rc = true;
boolean resultsetAsWarning = false;
boolean resultsetAsParameters = false;
boolean resultsetNameValue = false;
boolean execReturnsResultSet = false;
autoCommit = false;
try {
this.setLogger(new SOSSchedulerLogger(spooler_log));
super.prepareParams();
if(orderPayload!=null && orderPayload.var("scheduler_order_is_user_job")!=null && orderPayload.var("scheduler_order_is_user_job").equals("1")){
userJob = true;
}
if(orderPayload!=null && orderPayload.var("resultset_as_warning")!=null && (orderPayload.var("resultset_as_warning").equals("1") || orderPayload.var("resultset_as_warning").equalsIgnoreCase("true"))){
resultsetAsWarning = true;
}
if(orderPayload!=null && orderPayload.var("exec_returns_resultset")!=null && (orderPayload.var("exec_returns_resultset").equals("1") || orderPayload.var("exec_returns_resultset").equalsIgnoreCase("true"))){
execReturnsResultSet = true;
}
if(orderPayload!=null && orderPayload.var("resultset_as_parameters")!=null && (orderPayload.var("resultset_as_parameters").equals("1") || orderPayload.var("resultset_as_parameters").equalsIgnoreCase("true") || orderPayload.var("resultset_as_parameters").equalsIgnoreCase("name_value"))){
resultsetAsParameters = true;
if (orderPayload.var("resultset_as_parameters").equalsIgnoreCase("name_value")){
resultsetNameValue = true;
}
}
if(orderPayload!=null && orderPayload.var("auto_commit")!=null && (orderPayload.var("auto_commit").equals("1") || orderPayload.var("auto_commit").equalsIgnoreCase("true"))){
autoCommit = true;
}
try{
if(userJob){
checkOldTempUsers();
localConnection=this.getUserConnection(orderPayload.var("scheduler_order_user_name"), orderPayload.var("scheduler_order_schema"));
}else{
localConnection = JobSchedulerManagedObject.getOrderConnection(this.getConnection(), this);
localConnection.connect();
}
} catch (Exception e) {
throw new Exception("error occurred establishing database connection: " + e.getMessage());
}
localConnection.setExecReturnsResultSet(execReturnsResultSet);
try {
if (orderJob) command = JobSchedulerManagedObject.getOrderCommand(this.getConnection(), this);
if (command == null || command.length() == 0) {
command = JobSchedulerManagedObject.getJobCommand(this.getConnection(), this);
}
if (command == null || command.length() == 0) throw new Exception("command is empty");
} catch(Exception e){
throw (new Exception("no database command found: " + e));
}
// replace job-specific placeholders
command = command.replaceAll("\\$\\{scheduler_order_job_name\\}" , this.getJobName());
command = command.replaceAll("\\$\\{scheduler_order_job_id\\}" , Integer.toString(this.getJobId()));
command = command.replaceAll("\\$\\{scheduler_id\\}" , spooler.id());
// replace parameters
if (orderPayload != null)
command = JobSchedulerManagedObject.replaceVariablesInCommand(command, orderPayload, getLogger());
// replace order-specific placeholders
if(orderJob) {
order = spooler_task.order();
realOrderParams = order.params();
command = command.replaceAll("\\$\\{scheduler_order_id\\}" , order.id());
command = command.replaceAll("\\$\\{scheduler_order_managed_id\\}" , "0");
this.getLogger().info("executing database statement(s) for managed order [" + order.id() + "]: " + command);
} else {
this.getLogger().info("executing database statement(s): " + command);
}
executeStatements(localConnection, command);
this.getLogger().info("database statement(s) executed.");
if ((resultsetAsWarning || resultsetAsParameters) && localConnection.getResultSet() != null){
String warning = "";
HashMap result = null;
while( !(result = localConnection.get()).isEmpty()) {
String orderParamKey = "";
int columnCount =0;
warning = "execution terminated with warning:";
Iterator resultIterator = result.keySet().iterator();
boolean resultParametersSet = false;
while(resultIterator.hasNext()) {
columnCount++;
String key = (String) resultIterator.next();
if (key == null || key.length() == 0) continue;
String value = result.get(key).toString();
warning += " " + key + "=" + value;
if (resultsetAsParameters && order!=null && !resultParametersSet){
if (resultsetNameValue){ // name/value pairs from two columns
if (columnCount==1){
orderParamKey = value;
}
if (columnCount==2){
if (realOrderParams.value(orderParamKey)==null || realOrderParams.value(orderParamKey).length()==0){
realOrderParams.set_var(orderParamKey, value);
}
}
}else if (realOrderParams.value(key)==null || realOrderParams.value(key).length()==0){
// column name = name, value=value
realOrderParams.set_var(key, value);
resultParametersSet = true;
}
}
}
}
if (warning!=null && warning.length()>0 && resultsetAsWarning){
rc = false;
this.getLogger().warn(warning);
}
}
if (getLogger().hasWarnings() || getLogger().hasErrors()) spooler_task.end();
return rc && orderJob;
}
catch (Exception e) {
spooler_log.warn("error occurred processing managed order [" + ((order != null) ? "Job Chain: " + order.job_chain().name() + ", ID:"+ order.id() : "(none)") + "] : " + e);
if (userJob) writeError(e, order);
spooler_task.end();
return false;
}
finally {
//try { if (localConnection != null) localConnection.rollback(); } catch (Exception ex) {} // ignore this errror
try { if (localConnection != null && !userJob) localConnection.disconnect(); } catch (Exception ex) {} // ignore this errror
if(userJob) {
closeUserConnection(localConnection);
updateRunTime(order, getLogger(), getConnection());
}
try{getConnection().commit();} catch (Exception e){}
}
}
/**
* Cleanup
*/
public void spooler_exit() {
super.spooler_exit();
}
static public void updateRunTime(Order order, SOSLogger logger, SOSConnection conn) {
try{
String id = order.id();
String nextStart = conn.getSingleValue("SELECT \"NEXT_START\" FROM "+
JobSchedulerManagedObject.getTableManagedUserJobs()+ " WHERE \"ID\"=" +
id);
if(nextStart==null || nextStart.length()==0){
try{
logger.debug3("No next start for order "+id+". Deleting order.");
} catch(Exception e){}
conn.execute("DELETE FROM "+JobSchedulerManagedObject.getTableManagedUserJobs()+ " WHERE "+
" \"ID\"="+id);
conn.commit();
} else{
String nextTime = conn.getSingleValue("SELECT "+nextStart);
logger.debug3("next Start for this order: "+nextTime);
String jobRunTime = "CONCAT('<run_time let_run = \"yes\"><date date=\"',DATE('"+nextTime+"'),'\"><period single_start=\"', TIME('"+nextTime+"'), '\"/></date></run_time>')";
//String jobRunTime = "<run_time let_run = \"yes\"><period><single_start = \""+ nextTime+ "\"/></period></run_time>";
conn.execute("UPDATE "+JobSchedulerManagedObject.getTableManagedUserJobs()+
" SET \"RUN_TIME\"="+jobRunTime+", \"NEXT_TIME\"='"+nextTime+"', UPDATED=1 WHERE "+
" \"ID\"="+id);
}
} catch (Exception e){
try{
logger.warn("Error occured setting next runtime: "+e);
conn.rollback();
} catch (Exception ex){}
}
}
private void writeError(Exception e, Order order){
try{
String currentErrorText = e.getMessage();
Throwable thr = e.getCause();
int errCode=0;
while(thr!=null){
if(thr instanceof SQLException){
SQLException sqlEx = (SQLException) thr;
currentErrorText = sqlEx.getLocalizedMessage();
errCode = sqlEx.getErrorCode();
break;
}
thr = thr.getCause();
}
if (currentErrorText != null && currentErrorText.length() > 250) {
currentErrorText = currentErrorText.substring(currentErrorText.length()-250);
}
getConnection().execute("UPDATE "+JobSchedulerManagedObject.getTableManagedUserJobs()+
" SET \"ERROR\"=1, \"ERROR_TEXT\"='"+currentErrorText.replaceAll("'", "''")+"'," +
" \"ERROR_CODE\"='"+errCode+"' WHERE "+
" \"ID\"='"+order.id()+"'");
getConnection().commit();
}catch (Exception ex){
try{
getLogger().warn("Error occured writing error: "+ex);
} catch (Exception exe){}
}
}
/**
* get Database connection for a given user (mysql user jobs only)
*
* @throws Exception
*/
protected SOSConnection getUserConnection(String user, String schema) throws Exception{
if (ip==null){
try{
ip = getConnection().getSingleValue("SELECT CONVERT(SUBSTRING_INDEX(CURRENT_USER(),_utf8'@',-1) USING latin1)");
} catch (Exception e){
spooler_log.debug1("Could not optain ip Address for this host. Generated" +
" database users will be for all hosts.");
ip="%";
}
}
String userLeft = user.split("@")[0];
String userRight = user.split("@")[1];
String query = "SHOW GRANTS FOR '"+userLeft+"'@'"+userRight+"'";
ArrayList grants = this.getConnection().getArray(query);
this.getConnection().commit();
String newUserName = createRandomString();
String password = createRandomString();
revokeUser = "'"+newUserName+"'@'"+ip+"'";
revokeUserQuoted = "\\'"+newUserName+"\\'@\\'"+ip+"\\'";
String[] newGrants = new String[grants.size()];
int grantCounter = 0;
Iterator it = grants.iterator();
while(it.hasNext()){
HashMap map = (HashMap) it.next();
String grant = map.values().iterator().next().toString();
String newGrant = grant.replaceAll("TO '"+userLeft+"'@", "TO '"+newUserName+"'@");
newGrant = newGrant.replaceAll("@'"+userRight+"'", "@'"+ip+"'");
newGrant = newGrant.replaceAll("BY PASSWORD '.*'", "BY '"+password+"'");
try{
getLogger().debug6("Original GRANT statement: "+grant);
getLogger().debug6("New GRANT statement: "+newGrant);
} catch (Exception e){}
newGrants[grantCounter]=newGrant;
grantCounter++;
}
try{
getConnection().execute("INSERT INTO "+JobSchedulerManagedObject.getTableManagedTempUsers()+
"(\"NAME\", \"STATUS\", \"MODIFIED\") VALUES ("+
"'"+revokeUserQuoted+"', 'BEFORE_CREATION', %now)");
getConnection().commit();
} catch (Exception e) {}
try{
getLogger().debug3("executing new GRANT statements... ");
} catch (Exception e){}
for (int i=0; i<newGrants.length;i++){
this.getConnection().execute(newGrants[i]);
}
try{
getConnection().execute("UPDATE "+JobSchedulerManagedObject.getTableManagedTempUsers()+
" SET \"STATUS\"='CREATED', \"MODIFIED\"= %now WHERE "+
"\"NAME\"='"+revokeUserQuoted+"'");
} catch (Exception e) {}
getConnection().commit();
// als neuer user connecten
SOSConnection userConnection;
Properties spoolerProp = this.getJobSettings().getSection("spooler");
String dbProperty = spoolerProp.getProperty("db").replaceAll("jdbc:", "-url=jdbc:");
dbProperty = dbProperty.substring(dbProperty.indexOf('-'));
SOSArguments arguments = new SOSArguments(dbProperty);
try {
spooler_log.debug6("..creating user connection object");
userConnection = SOSConnection.createInstance(
spoolerProp.getProperty("db_class"),
arguments.as_string("-class=", ""),
arguments.as_string("-url=", ""),
newUserName,
password,
getLogger() );
}
catch (Exception e) {
throw new Exception("error occurred establishing database connection: " + e.getMessage());
}
userConnection.connect();
if (schema!=null && schema.length()>0) userConnection.execute("use "+schema);
userConnection.commit();
return userConnection;
}
private String createRandomString(){
String random = Long.toString(Math.abs(rand.nextLong()), 36);
if (random.length()>16) return random.substring(0,16);
if (random.length()<8) return createRandomString();
else return random;
}
protected void closeUserConnection(SOSConnection conn){
try{
if (conn!=null) conn.disconnect();
try{
getLogger().debug3("executing revoke statements to delete temporary user...");
} catch (Exception e){}
try{
getConnection().execute("UPDATE "+JobSchedulerManagedObject.getTableManagedTempUsers()+
" SET \"STATUS\"='BEFORE_DELETION', \"MODIFIED\"= %now WHERE "+
"\"NAME\"='"+revokeUserQuoted+"'");
getConnection().commit();
} catch (Exception e) {}
deleteUser(revokeUser);
getConnection().execute("DELETE FROM "+JobSchedulerManagedObject.getTableManagedTempUsers()+
" WHERE \"NAME\"='"+revokeUserQuoted+"'");
} catch (Exception e){
try{
getLogger().warn("Error occurred removing user: "+e);
} catch (Exception ex) {}
}
}
private void deleteUser(String userName) throws Exception{
String query = "SHOW GRANTS FOR " + userName;
ArrayList grants = getConnection().getArray(query);
getConnection().commit();
String revokes[] = new String[grants.size()];
int counter = grants.size() - 1;
for(Iterator it = grants.iterator(); it.hasNext();)
{
HashMap map = (HashMap)it.next();
String grant = map.values().iterator().next().toString();
String revoke = grant.replaceAll(" WITH GRANT OPTION", " ");
revoke = revoke.replaceAll("GRANT ", "REVOKE ");
revoke = revoke.replaceAll(" TO ", " FROM ");
revokes[counter] = revoke;
counter--;
}
for(int i = 0; i < revokes.length; i++)
if(revokes[i] != null && revokes[i].length() > 0)
getConnection().execute(revokes[i]);
this.getConnection().execute("REVOKE ALL PRIVILEGES ON *.* FROM "+userName);
this.getConnection().execute("REVOKE GRANT OPTION ON *.* FROM "+userName);
this.getConnection().execute("DROP USER "+userName);
}
private void checkOldTempUsers(){
try{
ArrayList users = getConnection().getArray("SELECT \"NAME\", \"STATUS\" FROM "+
JobSchedulerManagedObject.getTableManagedTempUsers()+
" WHERE DATEDIFF(%now,\"MODIFIED\")>1");
getConnection().commit();
Iterator iter = users.iterator();
while(iter.hasNext()){
HashMap map = (HashMap) iter.next();
String userName = map.get("name").toString();
String status = map.get("status").toString();
try{
getLogger().debug3("User "+userName+" has not been properly deleted and" +
" was left with status "+status+". Trying to delete him now...");
deleteUser(userName);
}catch (Exception e){
try{
getLogger().warn("Error occured deleting old temporary user "+
userName+" :"+e);
} catch (Exception ex){}
}
}
}catch (Exception e){
try{
getLogger().warn("Error occured deleting old temporary users: "+e);
} catch (Exception ex){}
}
}
/**
* <p>This function can be overwritten to write own database jobs.
* These could be used to do custom processing of the results.</p>
* <p>The given SOSConnection object is already connected and need not be
* disconnected afterwards. The standard implementation is basically:</p>
* <p><code>conn.executeStatements(command);<br/></p>
*
* @param conn connected SOSConnection Object
* @param command database command
* @throws Exception
* @see SOSConnection
*/
protected void executeStatements(SOSConnection conn, String command) throws Exception{
Exception exception = null;
try {
conn.setAutoCommit(autoCommit);
conn.executeStatements(command);
} catch (Exception e) { exception = e;}
finally{
conn.setAutoCommit(false);
}
try{
Vector output = conn.getOutput();
if (output.size()>0){
getLogger().info("Output from Database Server:");
Iterator it = output.iterator();
while (it.hasNext()){
String line = (String) it.next();
getLogger().info(" "+line);
}
}
else{
getLogger().debug9("No Output from Database Server.");
}
} catch (Exception e){}
if (exception != null) throw new Exception(exception);
}
}