/* Copyright 1999-2008 Acelet.org. All rights reserved. GPL v2 license */
/** @author Wei Jiang */
package com.acelet.s.task;
import java.io.*;
import java.sql.*;
import java.util.*;
import com.acelet.lib.Common;
import com.acelet.lib.ZipBlob;
import com.acelet.lib.DatabaseConnectionBrokenException;
import com.acelet.lib.NameNotFoundException;
public class AccessTaskDatabase {
public static final String DATABASE_SCHEMA_VERSION = "8.0";
protected static int REFRESH_TASK = 1;
protected static int REFRESH_WORKING_TASK = 2;
protected static int REFRESH_HOLIDAY = 3;
protected static int REFRESH_PREFERENCE = 4;
protected static int REFRESH_CANDIDATE = 5;
protected static boolean initialized = false;
protected static int queryTimeout = 15;
protected static int lastResultMaxSize = 500;
protected static int interval = 2;
protected Connection connection;
public AccessTaskDatabase(Connection connection) throws Exception {
this.connection = connection;
this.connection.setAutoCommit(true);
if (initialized == false) {
getLastResultMaxSize();
initialized = true;
}
}
int changeTaskStatus(String taskName, int newStatus) throws Exception {
String updateTaskStatusSql = "UPDATE tasks SET status=? WHERE name=?";
PreparedStatement updateTaskStatusStatement = null;
int nn = 0;
try {
updateTaskStatusStatement = connection.prepareStatement(updateTaskStatusSql);
updateTaskStatusStatement.setQueryTimeout(queryTimeout);
updateTaskStatusStatement.setInt(++nn, newStatus);
updateTaskStatusStatement.setString(++nn, taskName);
return updateTaskStatusStatement.executeUpdate();
} finally {
if (updateTaskStatusStatement != null)
updateTaskStatusStatement.close();
setRefreshTime(REFRESH_TASK);
}
}
public int deleteCandidateTask(long id) throws Exception {
String sql = "DELETE FROM candidate_tasks WHERE id=" + id;
Statement statement = null;
try {
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
return statement.executeUpdate(sql);
} finally {
if (statement != null)
statement.close();
}
}
public int deleteDoerTalker(String name) throws Exception {
String sql = "DELETE FROM doerTalkers WHERE name='" + name + "'";
Statement statement = null;
try {
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
return statement.executeUpdate(sql);
} finally {
if (statement != null)
statement.close();
}
}
public int deleteEntitlement(String role) throws Exception {
String sql = "DELETE FROM entitlements WHERE role='" + role + "'";
Statement statement = null;
try {
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
return statement.executeUpdate(sql);
} finally {
if (statement != null)
statement.close();
}
}
public int deleteHoliday(long id) throws Exception {
String sql = "DELETE FROM holiday WHERE id=?";
PreparedStatement deleteStatement = null;
int nn = 0;
try {
deleteStatement = connection.prepareStatement(sql);
deleteStatement.setQueryTimeout(queryTimeout);
deleteStatement.setLong(++nn, id);
return deleteStatement.executeUpdate();
} finally {
if (deleteStatement != null)
deleteStatement.close();
setRefreshTime(REFRESH_HOLIDAY);
}
}
public int deleteHolidayRule(long id) throws Exception {
String sql = "DELETE FROM holiday_rule WHERE id=?";
PreparedStatement deleteStatement = null;
int nn = 0;
try {
deleteStatement = connection.prepareStatement(sql);
deleteStatement.setQueryTimeout(queryTimeout);
deleteStatement.setLong(++nn, id);
return deleteStatement.executeUpdate();
} finally {
if (deleteStatement != null)
deleteStatement.close();
}
}
public int deleteHolidaySet(String setName) throws Exception {
PreparedStatement deleteSetStatement = null;
int nn = 0;
int setRows = 0;
try {
deleteSetStatement = connection.prepareStatement("DELETE FROM holiday_rule WHERE setName=?");
deleteSetStatement.setQueryTimeout(queryTimeout);
deleteSetStatement.setString(++nn, setName);
setRows = deleteSetStatement.executeUpdate();
} finally {
if (deleteSetStatement != null)
deleteSetStatement.close();
}
PreparedStatement deleteHolidayStatement = null;
nn = 0;
int holidayRows = 0;
try {
deleteHolidayStatement = connection.prepareStatement("DELETE FROM holiday WHERE setName=?");
deleteHolidayStatement.setQueryTimeout(queryTimeout);
deleteHolidayStatement.setString(++nn, setName);
holidayRows = deleteHolidayStatement.executeUpdate();
return setRows + holidayRows;
} finally {
if (deleteHolidayStatement != null)
deleteHolidayStatement.close();
if (holidayRows > 0)
setRefreshTime(REFRESH_HOLIDAY);
}
}
public int deleteRole(String name) throws Exception {
String sql = "DELETE FROM roles WHERE name='" + name + "'";
Statement statement = null;
try {
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
return statement.executeUpdate(sql);
} finally {
if (statement != null)
statement.close();
}
}
public int deleteTask(long id) throws Exception {
String sql = "DELETE FROM tasks WHERE id=" + id;
Statement statement = null;
try {
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
return statement.executeUpdate(sql);
} finally {
if (statement != null)
statement.close();
setRefreshTime(REFRESH_TASK);
}
}
public int deleteTask(String name) throws Exception {
String sql = "DELETE FROM tasks WHERE name='" + name + "'";
Statement statement = null;
try {
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
return statement.executeUpdate(sql);
} finally {
if (statement != null)
statement.close();
setRefreshTime(REFRESH_TASK);
}
}
public int deleteOldWorkingTasks(long workingTaskRetireTime) throws Exception {
String sql = "DELETE FROM working_tasks WHERE modifiedAt<?";
PreparedStatement deleteStatement = null;
int nn = 0;
try {
deleteStatement = connection.prepareStatement(sql);
deleteStatement.setQueryTimeout(queryTimeout);
deleteStatement.setLong(++nn, workingTaskRetireTime);
return deleteStatement.executeUpdate();
} finally {
if (deleteStatement != null)
deleteStatement.close();
}
}
public int deleteWorkingTask(long id) throws Exception {
String sql = "DELETE FROM working_tasks WHERE id=" + id;
Statement statement = null;
try {
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
return statement.executeUpdate(sql);
} finally {
if (statement != null)
statement.close();
}
}
public int getEntitlement(String name) throws Exception {
Statement statement = null;
try {
String selectSql = "SELECT count(*) FROM roles";
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
ResultSet resultSet = statement.executeQuery(selectSql);
resultSet.next();
int count = resultSet.getInt(1);
if (count == 0)
return 0xFFFF;
statement.close();
selectSql = "SELECT role FROM roles WHERE name='" + name + "'";
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
resultSet = statement.executeQuery(selectSql);
if (resultSet.next() == false)
return 0;
String role = resultSet.getString("role");
if (role == null)
return 0;
statement.close();
selectSql = "SELECT entitlement FROM entitlements WHERE role='" + role + "'";
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
resultSet = statement.executeQuery(selectSql);
if (resultSet.next() == false)
return 0;
return resultSet.getInt("entitlement");
} finally {
if (statement != null)
statement.close();
}
}
public void getLastResultMaxSize() throws Exception {
Statement statement = null;
int nn = 1;
try {
String testSql = "SELECT lastResult FROM working_tasks";
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
ResultSet resultSet = statement.executeQuery(testSql);
ResultSetMetaData rsmd = resultSet.getMetaData();
lastResultMaxSize = rsmd.getColumnDisplaySize(nn++) - 1;
if (lastResultMaxSize <= 0)
lastResultMaxSize = 2147483647;
} finally {
if (statement != null)
statement.close();
}
}
public int getNumberOfTasks() throws Exception {
Statement statement = null;
int nn = 1;
try {
String sql = "SELECT count(*) FROM tasks";
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
ResultSet resultSet = statement.executeQuery(sql);
resultSet.next();
return resultSet.getInt(1);
} finally {
if (statement != null)
statement.close();
}
}
String getRefreshTime() throws Exception {
String sql = "SELECT refreshTime FROM schedulerRefresh";
Statement statement = null;
try {
statement = connection.createStatement();
statement.setQueryTimeout(interval);
ResultSet resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
String aRefreshTime = resultSet.getString("refreshTime");
StringTokenizer st = new StringTokenizer(aRefreshTime);
String tasks = "0";
String workingTasks = "0";
String holidays = "0";
String preferences = "0";
String candidates = "0";
try {
tasks = st.nextToken();
workingTasks = st.nextToken();
holidays = st.nextToken();
preferences = st.nextToken();
candidates = st.nextToken();
} catch (Exception e) {
}
StringBuffer buffer = new StringBuffer();
buffer.append(tasks);
buffer.append(" ");
buffer.append(workingTasks);
buffer.append(" ");
buffer.append(holidays);
buffer.append(" ");
buffer.append(preferences);
buffer.append(" ");
buffer.append(candidates);
buffer.append(" ");
return buffer.toString();
} else
throw new NameNotFoundException("schedulerRefresh");
} finally {
if (statement != null)
statement.close();
}
}
public String getRefreshTime(String doerTalkerName) throws Exception {
try {
return getRefreshTime();
} finally {
registerDoerTalker(doerTalkerName);
}
}
private Vector getSelectedCandidateTaskFromResultSet(ResultSet resultSet) throws Exception {
Vector allCandidateTasks = new Vector();
while (resultSet.next()) {
long id = resultSet.getLong("id");
String name = resultSet.getString("name");
if (name == null)
name = "";
CandidateTask candidateTask = new CandidateTask(id, name);
allCandidateTasks.add(candidateTask);
}
return allCandidateTasks;
}
private Vector getSelectedDoertalkerFromResultSet(ResultSet resultSet) throws Exception {
Vector allDoertalkerVector = new Vector();
while (resultSet.next()) {
String name = resultSet.getString("name");
long checkinTime = resultSet.getLong("checkinTime");
DoerTalker doerTalker = new DoerTalker(name, checkinTime);
allDoertalkerVector.add(doerTalker);
}
return allDoertalkerVector;
}
private Vector getSelectedTaskFromResultSet(ResultSet resultSet) throws Exception {
Vector allTasks = new Vector();
long id = Task.NOT_SPECIFIED;
String name = "";
String job = "";
int jobType = Task.NOT_SPECIFIED;
int repeating = Task.NOT_SPECIFIED;
long startTime = Task.NOT_SPECIFIED;
int minutelyPeriod = Task.NOT_SPECIFIED;
int hourlyPeriod = Task.NOT_SPECIFIED;
int dailyPeriod = Task.NOT_SPECIFIED;
int weeklyPeriod = Task.NOT_SPECIFIED;
int weeklyWeekDay = Task.NOT_SPECIFIED;
int monthlyPeriod = Task.NOT_SPECIFIED;
int monthlyTheDay = Task.NOT_SPECIFIED;
int monthlyWhichWeek = Task.NOT_SPECIFIED;
int monthlyWhichWeekWeekDay = Task.NOT_SPECIFIED;
String specifiedTimes = "";
long wouldBe = Task.NOT_SPECIFIED;
int holidayPolicy = Task.NOT_SPECIFIED;
String holidaySet = Task.DEFAULT_HOLIDAY_SET_NAME;
long expiration = Long.MAX_VALUE;
long duration = Task.NOT_SPECIFIED;
int killIfExceedsDuration = 0;
String hostname = "";
String club = "";
String description = "";
String comments = "";
int status = Task.NOT_SPECIFIED;
long nextRunTime = Task.NOT_SPECIFIED;
long lastRunTime = Task.NOT_SPECIFIED;
String creator = "";
String alarmEmail = "";
long modifiedAt = Task.NOT_SPECIFIED;
while (resultSet.next()) {
try {
id = resultSet.getLong("id");
name = resultSet.getString("name");
if (name == null)
name = "";
job = new ZipBlob().getText(resultSet, "job");
jobType = resultSet.getInt("jobType");
repeating = resultSet.getInt("repeating");
startTime = resultSet.getLong("startTime");
minutelyPeriod = resultSet.getInt("minutelyPeriod");
hourlyPeriod = resultSet.getInt("hourlyPeriod");
dailyPeriod = resultSet.getInt("dailyPeriod");
weeklyPeriod = resultSet.getInt("weeklyPeriod");
weeklyWeekDay = resultSet.getInt("weeklyWeekDay");
monthlyPeriod = resultSet.getInt("monthlyPeriod");
monthlyTheDay = resultSet.getInt("monthlyTheDay");
monthlyWhichWeek = resultSet.getInt("monthlyWhichWeek");
monthlyWhichWeekWeekDay = resultSet.getInt("monthlyWhichWeekWeekDay");
specifiedTimes = resultSet.getString("specifiedTimes");
wouldBe = resultSet.getLong("wouldBe");
holidayPolicy = resultSet.getInt("holidayPolicy");
holidaySet = resultSet.getString("holidaySet");
holidaySet = holidaySet==null? Task.DEFAULT_HOLIDAY_SET_NAME: holidaySet;
expiration = resultSet.getLong("expiration");
duration = resultSet.getLong("duration");
killIfExceedsDuration = resultSet.getInt("killIfExceedsDuration");
hostname = resultSet.getString("hostname");
hostname = hostname==null? "": hostname.trim();
club = resultSet.getString("club");
club = club==null? "": club.trim();
description = resultSet.getString("description");
if (description == null)
description = "";
comments = resultSet.getString("comments");
if (comments == null)
comments = "";
status = resultSet.getInt("status");
nextRunTime = resultSet.getLong("nextRunTime");
lastRunTime = resultSet.getLong("lastRunTime");
creator = resultSet.getString("creator");
if (creator == null)
creator = "";
alarmEmail = resultSet.getString("alarmEmail");
if (alarmEmail == null)
alarmEmail = "";
modifiedAt = resultSet.getLong("modifiedAt");
} catch (Exception ex) {
status = Task.STATUS_ERROR;
if (id == Task.NOT_SPECIFIED)
continue;
}
Task task = new Task(id);
task.name = name;
try {
task.restore(
name,
job,
jobType,
repeating,
startTime,
minutelyPeriod,
hourlyPeriod,
dailyPeriod,
weeklyPeriod,
weeklyWeekDay,
monthlyPeriod,
monthlyTheDay,
monthlyWhichWeek,
monthlyWhichWeekWeekDay,
specifiedTimes,
wouldBe,
holidayPolicy,
holidaySet,
expiration,
duration,
killIfExceedsDuration,
hostname,
club,
description,
comments,
status,
nextRunTime,
lastRunTime,
creator,
alarmEmail,
modifiedAt);
} catch (Exception e) {
System.out.println("\n" + new java.util.Date().toString());
System.out.println(name + ":");
e.printStackTrace();
System.out.println("\n");
}
allTasks.add(task);
}
return allTasks;
}
private Vector getSelectedWorkingTaskFromResultSet(ResultSet resultSet, int rows)
throws Exception {
Vector allWorkingTasks = new Vector();
long id = Task.NOT_SPECIFIED;
long id0 = Task.NOT_SPECIFIED;
String name = "";
String job = "";
int jobType = Task.NOT_SPECIFIED;
int status = Task.NOT_SPECIFIED;
int oneShot = Task.NOT_SPECIFIED;
String realHost = "";
String club = "";
String taskBrief = "";
long nextRunTime = Task.NOT_SPECIFIED;
long lastRunTime = Task.NOT_SPECIFIED;
String lastResult = "";
long modifiedAt = Task.NOT_SPECIFIED;
for (int i = 0; i < rows; i++) {
if (resultSet.next() == false)
break;
try {
id = resultSet.getLong("id");
id0 = resultSet.getLong("id0");
name = resultSet.getString("name");
if (name == null)
name = "";
job = new ZipBlob().getText(resultSet, "job");
jobType = resultSet.getInt("jobType");
status = resultSet.getInt("status");
oneShot = resultSet.getInt("oneShot");
realHost = resultSet.getString("realHost");
if (realHost == null)
realHost = "";
club = resultSet.getString("club");
if (club == null)
club = "";
taskBrief = resultSet.getString("taskBrief");
nextRunTime = resultSet.getLong("nextRunTime");
lastRunTime = resultSet.getLong("lastRunTime");
lastResult = resultSet.getString("lastResult");
if (lastResult == null)
lastResult = "";
modifiedAt = resultSet.getLong("modifiedAt");
} catch (Exception ex) {
status = WorkingTask.STATUS_ERROR;
lastResult = ex.toString();
}
WorkingTask workingTask = new WorkingTask();
workingTask.restore(
id,
id0,
name,
job,
jobType,
oneShot,
realHost,
club,
status,
taskBrief,
nextRunTime,
lastRunTime,
lastResult,
modifiedAt);
allWorkingTasks.add(workingTask);
}
return allWorkingTasks;
}
public int insertCandidateTask(String taskName) throws Exception {
Task task = selectTask(taskName);
if (task == null)
throw new NameNotFoundException(taskName);
String insertSql = "INSERT INTO candidate_tasks (id, name) VALUES(?,?)";
PreparedStatement insertStatement = null;
int nn = 0;
long id = Common.getUniqueIdNumber();
try {
insertStatement = connection.prepareStatement(insertSql);;
insertStatement.setQueryTimeout(queryTimeout);
insertStatement.setLong(++nn, id);
insertStatement.setString(++nn, taskName);
return insertStatement.executeUpdate();
} finally {
if (insertStatement != null)
insertStatement.close();
setRefreshTime(REFRESH_CANDIDATE);
}
}
private int insertDoerTalker(String name) throws Exception {
String insertSql = "INSERT INTO doerTalkers (name, checkinTime) VALUES(?,?)";
PreparedStatement insertStatement = null;
int nn = 0;
try {
insertStatement = connection.prepareStatement(insertSql);;
insertStatement.setQueryTimeout(interval);
insertStatement.setString(++nn, name);
insertStatement.setLong(++nn, System.currentTimeMillis());
try {
return insertStatement.executeUpdate();
} catch (SQLException sqlException) {
String sqlState = sqlException.getSQLState();
if (sqlState != null && sqlState.startsWith("23"))
return 0;
else
throw sqlException;
}
} finally {
if (insertStatement != null)
insertStatement.close();
}
}
public int insertEntitlement(String role, int entitlement) throws Exception {
String insertSql = "INSERT INTO entitlements (role, entitlement) VALUES(?,?)";
PreparedStatement insertStatement = null;
int nn = 0;
long id = Common.getUniqueIdNumber();
try {
insertStatement = connection.prepareStatement(insertSql);;
insertStatement.setQueryTimeout(queryTimeout);
insertStatement.setString(++nn, role);
insertStatement.setInt(++nn, entitlement);
return insertStatement.executeUpdate();
} finally {
if (insertStatement != null)
insertStatement.close();
}
}
public int insertHoliday(Holiday holiday) throws Exception {
String insertSql = "INSERT INTO holiday (id, setName, name, theDay, comments) " +
" VALUES(?,?,?,?,?)";
PreparedStatement insertStatement = null;
int nn = 0;
int rows = 0;
try {
insertStatement = connection.prepareStatement(insertSql);;
insertStatement.setQueryTimeout(queryTimeout);
insertStatement.setLong(++nn, holiday.id);
insertStatement.setString(++nn, holiday.setName);
insertStatement.setString(++nn, holiday.name);
insertStatement.setString(++nn, holiday.theDay);
insertStatement.setString(++nn, holiday.comments);
try {
rows = insertStatement.executeUpdate();
} catch (SQLException sqlException) {
String sqlState = sqlException.getSQLState();
if (sqlState != null && sqlState.startsWith("23"))
rows = 0;
else
throw sqlException;
}
if (rows > 0)
setRefreshTime(REFRESH_HOLIDAY);
return rows;
} finally {
if (insertStatement != null)
insertStatement.close();
}
}
public int insertHolidayRule(HolidayRule holidayRule) throws Exception {
if (holidayRule.holidaying == null || holidayRule.holidaying.length() == 0)
holidayRule.holidaying = " ";
String insertSql = "INSERT INTO holiday_rule (" +
"id," +
"setName," +
"name," +
"holidaying," +
"fixMonth," +
"fixDay," +
"monthlyMonth," +
"monthlyWhichWeek," +
"monthlyWhichWeekWeekDay," +
"comments)" +
" VALUES(?,?,?,?,?,?,?,?,?,?)";
PreparedStatement insertStatement = null;
int nn = 0;
try {
insertStatement = connection.prepareStatement(insertSql);;
insertStatement.setQueryTimeout(queryTimeout);
insertStatement.setLong(++nn, holidayRule.id);
insertStatement.setString(++nn, holidayRule.setName);
insertStatement.setString(++nn, holidayRule.name);
insertStatement.setString(++nn, holidayRule.holidaying);
insertStatement.setInt(++nn, holidayRule.fixMonth);
insertStatement.setInt(++nn, holidayRule.fixDay);
insertStatement.setInt(++nn, holidayRule.monthlyMonth);
insertStatement.setInt(++nn, holidayRule.monthlyWhichWeek);
insertStatement.setInt(++nn, holidayRule.monthlyWhichWeekWeekDay);
insertStatement.setString(++nn, holidayRule.comments);
try {
return insertStatement.executeUpdate();
} catch (SQLException sqlException) {
String sqlState = sqlException.getSQLState();
if (sqlState != null && sqlState.startsWith("23"))
return 0;
else
throw sqlException;
}
} finally {
if (insertStatement != null)
insertStatement.close();
}
}
public int insertHolidayRules(Vector holidayRuleVector) throws Exception {
int rows = 0;
for (int i = 0; i < holidayRuleVector.size(); i++) {
try {
HolidayRule holidayRule = (HolidayRule) holidayRuleVector.elementAt(i);
rows += insertHolidayRule(holidayRule);
} catch (SQLException sqlException) {
String sqlState = sqlException.getSQLState();
if (sqlState != null && sqlState.startsWith("23"))
continue;
else
throw sqlException;
}
}
return rows;
}
public int insertHolidays(Vector holidayVector) throws Exception {
int rows = 0;
for (int i = 0; i < holidayVector.size(); i++) {
try {
rows += insertHoliday((Holiday) holidayVector.elementAt(i));
} catch (SQLException sqlException) {
String sqlState = sqlException.getSQLState();
if (sqlState != null && sqlState.startsWith("23"))
continue;
else
throw sqlException;
}
}
if (rows > 0)
setRefreshTime(REFRESH_HOLIDAY);
return rows;
}
public int insertRole(String name, String role) throws Exception {
String insertSql = "INSERT INTO roles (name, role) VALUES(?,?)";
PreparedStatement insertStatement = null;
int nn = 0;
long id = Common.getUniqueIdNumber();
try {
insertStatement = connection.prepareStatement(insertSql);;
insertStatement.setQueryTimeout(queryTimeout);
insertStatement.setString(++nn, name);
insertStatement.setString(++nn, role);
return insertStatement.executeUpdate();
} finally {
if (insertStatement != null)
insertStatement.close();
}
}
public int insertSchedulerPreference(String key, String value) throws Exception {
String insertSql = "INSERT INTO schedulerPreference (theKey, theValue) VALUES(?,?)";
PreparedStatement insertStatement = null;
int nn = 0;
try {
insertStatement = connection.prepareStatement(insertSql);;
insertStatement.setQueryTimeout(queryTimeout);
insertStatement.setString(++nn, key);
insertStatement.setString(++nn, value);
return insertStatement.executeUpdate();
} finally {
if (insertStatement != null)
insertStatement.close();
}
}
public int insertTask(Task task) throws Exception {
String insertSql = "INSERT INTO tasks (" +
"id," +
"name," +
"job," +
"jobType," +
"repeating," +
"startTime," +
"minutelyPeriod," +
"hourlyPeriod," +
"dailyPeriod," +
"weeklyPeriod," +
"weeklyWeekDay," +
"monthlyPeriod," +
"monthlyTheDay," +
"monthlyWhichWeek," +
"monthlyWhichWeekWeekDay," +
"specifiedTimes," +
"wouldBe," +
"holidayPolicy," +
"holidaySet," +
"expiration," +
"duration," +
"killIfExceedsDuration," +
"hostname," +
"club," +
"description," +
"comments," +
"status," +
"nextRunTime," +
"lastRunTime," +
"creator," +
"alarmEmail," +
"modifiedAt) VALUES " +
"(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement insertStatement = null;
int nn = 0;
try {
insertStatement = connection.prepareStatement(insertSql);;
insertStatement.setQueryTimeout(queryTimeout);
insertStatement.setLong(++nn, task.getId());
insertStatement.setString(++nn, task.name);
new ZipBlob().setText(insertStatement, ++nn, task.getJob());
insertStatement.setInt(++nn, task.getJobType());
insertStatement.setInt(++nn, task.getRepeating());
insertStatement.setLong(++nn, task.getStartTime());
insertStatement.setInt(++nn, task.getMinutelyPeriod());
insertStatement.setInt(++nn, task.getHourlyPeriod());
insertStatement.setInt(++nn, task.getDailyPeriod());
insertStatement.setInt(++nn, task.getWeeklyPeriod());
insertStatement.setInt(++nn, task.getWeeklyWeekDay());
insertStatement.setInt(++nn, task.getMonthlyPeriod());
insertStatement.setInt(++nn, task.getMonthlyTheDay());
insertStatement.setInt(++nn, task.getMonthlyWhichWeek());
insertStatement.setInt(++nn, task.getMonthlyWhichWeekWeekDay());
insertStatement.setString(++nn, task.getSpecifiedTimes());
insertStatement.setLong(++nn, task.getWouldBe());
insertStatement.setInt(++nn, task.getHolidayPolicy());
insertStatement.setString(++nn, task.getHolidaySet());
insertStatement.setLong(++nn, task.getExpiration());
insertStatement.setLong(++nn, task.getDuration());
insertStatement.setInt(++nn, task.getKillIfExceedsDuration());
insertStatement.setString(++nn, task.getHostname());
insertStatement.setString(++nn, task.getClub());
insertStatement.setString(++nn, task.description);
insertStatement.setString(++nn, task.comments);
insertStatement.setInt(++nn, task.status);
insertStatement.setLong(++nn, task.getNextRunTime());
insertStatement.setLong(++nn, task.lastRunTime);
insertStatement.setString(++nn, task.getCreator());
insertStatement.setString(++nn, task.getAlarmEmail());
insertStatement.setLong(++nn, task.modifiedAt);
return insertStatement.executeUpdate();
} finally {
if (insertStatement != null)
insertStatement.close();
setRefreshTime(REFRESH_TASK);
}
}
public int insertWorkingTask(WorkingTask workingTask) throws Exception {
if (workingTask.lastResult.length() > lastResultMaxSize)
workingTask.lastResult =
workingTask.lastResult.substring(0, lastResultMaxSize);
String insertSql =
"INSERT INTO working_tasks (" +
"id0," +
"name," +
"job," +
"jobType," +
"oneShot," +
"realHost," +
"club," +
"status," +
"taskBrief," +
"nextRunTime," +
"lastRunTime," +
"lastResult," +
"modifiedAt" +
") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement insertStatement = null;
int nn = 0;
int rows = 0;
try {
insertStatement = connection.prepareStatement(insertSql);;
insertStatement.setQueryTimeout(queryTimeout);
insertStatement.setLong(++nn, workingTask.getId0());
insertStatement.setString(++nn, workingTask.name);
new ZipBlob().setText(insertStatement, ++nn, workingTask.getJob());
insertStatement.setInt(++nn, workingTask.getJobType());
insertStatement.setInt(++nn, workingTask.oneShot);
insertStatement.setString(++nn, workingTask.realHost);
insertStatement.setString(++nn, workingTask.club);
insertStatement.setInt(++nn, workingTask.status);
insertStatement.setString(++nn, workingTask.taskBrief);
insertStatement.setLong(++nn, workingTask.nextRunTime);
insertStatement.setLong(++nn, workingTask.lastRunTime);
insertStatement.setString(++nn, workingTask.lastResult);
insertStatement.setLong(++nn, workingTask.modifiedAt);
rows = insertStatement.executeUpdate();
if (rows == 1)
setRefreshTime(REFRESH_WORKING_TASK);
} catch (SQLException sqlException) {
String sqlState = sqlException.getSQLState();
if (sqlState != null && sqlState.startsWith("23")) {
rows = 0;
} else
throw sqlException;
} finally {
if (insertStatement != null)
insertStatement.close();
}
return rows;
}
public int registerDoerTalker(String name) throws Exception {
int rows = updateDoerTalker(name);
if (rows == 0) {
return insertDoerTalker(name);
}
return rows;
}
public int renameHolidaySet(String oldName, String newName) throws Exception {
PreparedStatement updateRuleStatment = null;
int nn = 0;
int ruleRows = 0;
try {
updateRuleStatment =
connection.prepareStatement("UPDATE holiday_rule SET setName=? WHERE setName=?");
updateRuleStatment.setQueryTimeout(queryTimeout);
updateRuleStatment.setString(++nn, newName);
updateRuleStatment.setString(++nn, oldName);
ruleRows = updateRuleStatment.executeUpdate();
} finally {
if (updateRuleStatment != null)
updateRuleStatment.close();
}
PreparedStatement updateHolidayStatement = null;
nn = 0;
int holidayRows = 0;
try {
updateHolidayStatement =
connection.prepareStatement("UPDATE holiday SET setName=? WHERE setName=?");
updateHolidayStatement.setQueryTimeout(queryTimeout);
updateHolidayStatement.setString(++nn, newName);
updateHolidayStatement.setString(++nn, oldName);
holidayRows = updateHolidayStatement.executeUpdate();
return ruleRows + holidayRows;
} finally {
if (updateHolidayStatement != null)
updateHolidayStatement.close();
}
}
public static void reset(Connection connection) throws Exception {
AccessTaskDatabase accessTaskDatabase = new AccessTaskDatabase(connection);
Properties properties = accessTaskDatabase.selectSchedulerPreference();
String queryTimeoutString = properties.getProperty("queryTimeout");
if (queryTimeoutString != null)
queryTimeout = Integer.parseInt(queryTimeoutString);
String intervalString = properties.getProperty("interval");
if (intervalString != null)
interval = Integer.parseInt(intervalString);
}
public Vector selectAllCandidateTasks() throws Exception {
String selectAllSql = "SELECT " +
"id," +
"name " +
" FROM candidate_tasks ORDER BY id";
PreparedStatement selectAllStatement = null;
try {
selectAllStatement = connection.prepareStatement(selectAllSql);
selectAllStatement.setQueryTimeout(queryTimeout);
ResultSet resultSet = selectAllStatement.executeQuery();
return getSelectedCandidateTaskFromResultSet(resultSet);
} finally {
if (selectAllStatement != null)
selectAllStatement.close();
}
}
public Vector selectAllDoerTalkers() throws Exception {
String selectAllSql = "SELECT name, checkinTime FROM doerTalkers ORDER BY checkinTime DESC";
PreparedStatement selectAllStatement = null;
try {
selectAllStatement = connection.prepareStatement(selectAllSql);
selectAllStatement.setQueryTimeout(queryTimeout);
ResultSet resultSet = selectAllStatement.executeQuery();
return getSelectedDoertalkerFromResultSet(resultSet);
} finally {
if (selectAllStatement != null)
selectAllStatement.close();
}
}
public Vector selectAllEntitlements() throws Exception {
String selectAllSql = "SELECT * FROM entitlements ORDER BY role";
PreparedStatement selectAllStatement = null;
try {
selectAllStatement = connection.prepareStatement(selectAllSql);
selectAllStatement.setQueryTimeout(queryTimeout);
ResultSet resultSet = selectAllStatement.executeQuery();
Vector allEntitlementsVector = new Vector();
while (resultSet.next()) {
String role = resultSet.getString("role");
int entitlement = resultSet.getInt("entitlement");
Properties properties = new Properties();
properties.setProperty("role", role);
properties.setProperty("entitlement", entitlement + "");
allEntitlementsVector.add(properties);
}
return allEntitlementsVector;
} finally {
if (selectAllStatement != null)
selectAllStatement.close();
}
}
public Vector selectAllHolidays() throws Exception {
String selectAllSql = "SELECT " +
"id," +
"setName," +
"name," +
"theDay," +
"comments " +
" FROM holiday ORDER BY setName, theDay";
PreparedStatement selectAllStatement = null;
try {
selectAllStatement = connection.prepareStatement(selectAllSql);
selectAllStatement.setQueryTimeout(queryTimeout);
Vector vector = new Vector();
ResultSet resultSet = selectAllStatement.executeQuery();
while (resultSet.next()) {
String setName = resultSet.getString("setName");
if (setName == null)
setName = "";
String theDay = resultSet.getString("theDay");
if (theDay == null)
theDay = "";
int theYear = Integer.parseInt(theDay.substring(0, 4));
long id = resultSet.getLong("id");
String name = resultSet.getString("name");
if (name == null)
name = "";
String comments = resultSet.getString("comments");
if (comments == null)
comments = "";
Holiday holiday = new Holiday(id, setName, name, theDay, comments);
vector.add(holiday);
}
return vector;
} finally {
if (selectAllStatement != null)
selectAllStatement.close();
}
}
public Vector selectAllHolidayRules() throws Exception {
String selectAllSql = "SELECT " +
"id," +
"setName," +
"name," +
"holidaying," +
"fixMonth," +
"fixDay," +
"monthlyMonth," +
"monthlyWhichWeek," +
"monthlyWhichWeekWeekDay," +
"comments " +
" FROM holiday_rule ORDER BY id";
PreparedStatement selectAllStatement = null;
try {
selectAllStatement = connection.prepareStatement(selectAllSql);
selectAllStatement.setQueryTimeout(queryTimeout);
Vector vector = new Vector();
ResultSet resultSet = selectAllStatement.executeQuery();
while (resultSet.next()) {
long id = resultSet.getLong("id");
String setName = resultSet.getString("setName");
if (setName == null)
setName = "";
String name = resultSet.getString("name");
if (name == null)
name = "";
String holidaying = resultSet.getString("holidaying");
if (holidaying == null)
holidaying = "";
int fixMonth = resultSet.getInt("fixMonth");
int fixDay = resultSet.getInt("fixDay");
int monthlyMonth = resultSet.getInt("monthlyMonth");
int monthlyWhichWeek = resultSet.getInt("monthlyWhichWeek");
int monthlyWhichWeekWeekDay =
resultSet.getInt("monthlyWhichWeekWeekDay");
String comments = resultSet.getString("comments");
if (comments == null)
comments = "";
HolidayRule holidayRule = new HolidayRule(id, setName, name, holidaying, fixMonth,
fixDay, monthlyMonth, monthlyWhichWeek, monthlyWhichWeekWeekDay,
comments);
vector.add(holidayRule);
}
return vector;
} finally {
if (selectAllStatement != null)
selectAllStatement.close();
}
}
public Vector selectAllRoles() throws Exception {
String selectAllSql = "SELECT * FROM roles ORDER BY name";
PreparedStatement selectAllStatement = null;
try {
selectAllStatement = connection.prepareStatement(selectAllSql);
selectAllStatement.setQueryTimeout(queryTimeout);
ResultSet resultSet = selectAllStatement.executeQuery();
Vector allRolesVector = new Vector();
while (resultSet.next()) {
String name = resultSet.getString("name");
String role = resultSet.getString("role");
Properties properties = new Properties();
properties.setProperty("name", name);
properties.setProperty("role", role);
allRolesVector.add(properties);
}
return allRolesVector;
} finally {
if (selectAllStatement != null)
selectAllStatement.close();
}
}
public Vector selectAllTasks() throws Exception {
String selectAllSql = "SELECT " +
"id," +
"name," +
"job," +
"jobType," +
"repeating," +
"startTime," +
"minutelyPeriod," +
"hourlyPeriod," +
"dailyPeriod," +
"weeklyPeriod," +
"weeklyWeekDay," +
"monthlyPeriod," +
"monthlyTheDay," +
"monthlyWhichWeek," +
"monthlyWhichWeekWeekDay," +
"specifiedTimes," +
"wouldBe," +
"holidayPolicy," +
"holidaySet," +
"expiration," +
"duration," +
"killIfExceedsDuration," +
"hostname," +
"club," +
"description," +
"comments," +
"status," +
"nextRunTime," +
"lastRunTime," +
"creator," +
"alarmEmail," +
"modifiedAt " +
" FROM tasks ORDER BY name";
PreparedStatement selectAllStatement = null;
try {
selectAllStatement = connection.prepareStatement(selectAllSql);
selectAllStatement.setQueryTimeout(queryTimeout);
ResultSet resultSet = selectAllStatement.executeQuery();
return getSelectedTaskFromResultSet(resultSet);
} finally {
if (selectAllStatement != null)
selectAllStatement.close();
}
}
public Vector selectAllWorkingTasks(long from, long to) throws Exception {
return selectAllWorkingTasks(from, to, Integer.MAX_VALUE);
}
public Vector selectAllWorkingTasks(long from, long to, int rows) throws Exception {
String selectAllSql = "SELECT " +
"id," +
"id0," +
"name," +
"job," +
"jobType," +
"oneShot," +
"realHost," +
"club," +
"status," +
"taskBrief," +
"nextRunTime," +
"lastRunTime," +
"lastResult," +
"modifiedAt " +
" FROM working_tasks WHERE modifiedAt > ? AND modifiedAt < ? " +
" ORDER BY modifiedAt DESC";
PreparedStatement selectAllStatement = null;
try {
selectAllStatement = connection.prepareStatement(selectAllSql);
selectAllStatement.setQueryTimeout(queryTimeout);
int nn = 0;
selectAllStatement.setLong(++nn, from);
selectAllStatement.setLong(++nn, to);
ResultSet resultSet = selectAllStatement.executeQuery();
return getSelectedWorkingTaskFromResultSet(resultSet, rows);
} finally {
if (selectAllStatement != null)
selectAllStatement.close();
}
}
public Holiday selectHoliday(long id) throws Exception {
Holiday holiday = null;
String selectSql = "SELECT " +
"id," +
"setName," +
"name," +
"theDay," +
"comments " +
" FROM holiday WHERE id=?";
PreparedStatement selectStatement = null;
try {
selectStatement = connection.prepareStatement(selectSql);
selectStatement.setQueryTimeout(queryTimeout);
int nn = 0;
selectStatement.setLong(++nn, id);
ResultSet resultSet = selectStatement.executeQuery();
if (resultSet.next()) {
String setName = resultSet.getString("setName");
if (setName == null)
setName = "";
String name = resultSet.getString("name");
if (name == null)
name = "";
String theDay = resultSet.getString("theDay");
String comments = resultSet.getString("comments");
if (comments == null)
comments = "";
holiday = new Holiday(id, setName, name, theDay, comments);
}
return holiday;
} finally {
if (selectStatement != null)
selectStatement.close();
}
}
public HolidayRule selectHolidayRule(long id) throws Exception {
HolidayRule holidayRule = null;
String selectSql = "SELECT " +
"id," +
"setName," +
"name," +
"holidaying," +
"fixMonth," +
"fixDay," +
"monthlyMonth," +
"monthlyWhichWeek," +
"monthlyWhichWeekWeekDay," +
"comments " +
" FROM holiday_rule WHERE id=?";
PreparedStatement selectStatement = null;
try {
selectStatement = connection.prepareStatement(selectSql);
selectStatement.setQueryTimeout(queryTimeout);
int nn = 0;
selectStatement.setLong(++nn, id);
ResultSet resultSet = selectStatement.executeQuery();
if (resultSet.next()) {
String setName = resultSet.getString("setName");
if (setName == null)
setName = "";
String name = resultSet.getString("name");
if (name == null)
name = "";
String holidaying = resultSet.getString("holidaying");
if (holidaying == null)
holidaying = "";
int fixMonth = resultSet.getInt("fixMonth");
int fixDay = resultSet.getInt("fixDay");
int monthlyMonth = resultSet.getInt("monthlyMonth");
int monthlyWhichWeek = resultSet.getInt("monthlyWhichWeek");
int monthlyWhichWeekWeekDay =
resultSet.getInt("monthlyWhichWeekWeekDay");
String comments = resultSet.getString("comments");
if (comments == null)
comments = "";
holidayRule = new HolidayRule(id, setName, name, holidaying, fixMonth, fixDay,
monthlyMonth, monthlyWhichWeek, monthlyWhichWeekWeekDay, comments);
}
return holidayRule;
} finally {
if (selectStatement != null)
selectStatement.close();
}
}
public Properties selectSchedulerPreference() throws Exception {
Properties properties = new Properties();
String sql = "SELECT * from schedulerPreference";
Statement statement = null;
try {
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String theKey = resultSet.getString("theKey");
String theValue = resultSet.getString("theValue");
if (theValue != null)
properties.setProperty(theKey, theValue);
}
return properties;
} finally {
if (statement != null)
statement.close();
}
}
public Properties selectSettings() throws Exception {
Properties settings = new Properties();
String sql = "SELECT * from schedulerSettings";
Statement statement = null;
try {
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String theKey = resultSet.getString("theKey");
String theValue = resultSet.getString("theValue");
settings.setProperty(theKey, theValue);
}
return settings;
} finally {
if (statement != null)
statement.close();
}
}
public Task selectTask(long id) throws Exception {
String selectIdSql = "SELECT " +
"id," +
"name," +
"job," +
"jobType," +
"repeating," +
"startTime," +
"minutelyPeriod," +
"hourlyPeriod," +
"dailyPeriod," +
"weeklyPeriod," +
"weeklyWeekDay," +
"monthlyPeriod," +
"monthlyTheDay," +
"monthlyWhichWeek," +
"monthlyWhichWeekWeekDay," +
"specifiedTimes," +
"wouldBe," +
"holidayPolicy," +
"holidaySet," +
"expiration," +
"duration," +
"killIfExceedsDuration," +
"hostname," +
"club," +
"description," +
"comments," +
"status," +
"nextRunTime," +
"lastRunTime," +
"creator," +
"alarmEmail," +
"modifiedAt " +
" FROM tasks WHERE id=?";
PreparedStatement selectIdStatement = null;
try {
selectIdStatement = connection.prepareStatement(selectIdSql);
selectIdStatement.setQueryTimeout(queryTimeout);
selectIdStatement.setLong(1, id);
ResultSet resultSet = selectIdStatement.executeQuery();
Vector allTasks = getSelectedTaskFromResultSet(resultSet);
Task aTask = null;
if (allTasks.size() > 0)
aTask = (Task) allTasks.elementAt(0);
return aTask;
} finally {
if (selectIdStatement != null)
selectIdStatement.close();
}
}
public Task selectTask(String name) throws Exception {
String selectNameSql = "SELECT " +
"id," +
"name," +
"job," +
"jobType," +
"repeating," +
"startTime," +
"minutelyPeriod," +
"hourlyPeriod," +
"dailyPeriod," +
"weeklyPeriod," +
"weeklyWeekDay," +
"monthlyPeriod," +
"monthlyTheDay," +
"monthlyWhichWeek," +
"monthlyWhichWeekWeekDay," +
"specifiedTimes," +
"wouldBe," +
"holidayPolicy," +
"holidaySet," +
"expiration," +
"duration," +
"killIfExceedsDuration," +
"hostname," +
"club," +
"description," +
"comments," +
"status," +
"nextRunTime," +
"lastRunTime," +
"creator," +
"alarmEmail," +
"modifiedAt " +
" FROM tasks WHERE name=?";
PreparedStatement selectNameStatement = null;
try {
selectNameStatement = connection.prepareStatement(selectNameSql);
selectNameStatement.setQueryTimeout(queryTimeout);
selectNameStatement.setString(1, name);
ResultSet resultSet = selectNameStatement.executeQuery();
Vector allTasks = getSelectedTaskFromResultSet(resultSet);
Task aTask = null;
if (allTasks.size() > 0)
aTask = (Task) allTasks.elementAt(0);
return aTask;
} finally {
if (selectNameStatement != null)
selectNameStatement.close();
}
}
public String selectSchemaVersion() throws Exception {
String schedulerSchema = "?";
String selectSql = "SELECT theVersion FROM superSchedulerSchema";
Statement statement = null;
try {
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
ResultSet resultSet = statement.executeQuery(selectSql);
if (resultSet.next())
schedulerSchema = resultSet.getString("theVersion");
else
return "?";
if (resultSet.next())
return "??";
return schedulerSchema;
} finally {
if (statement != null)
statement.close();
}
}
public WorkingTask selectWorkingTask(long id) throws Exception {
String selectIdSql = "SELECT " +
"id," +
"id0," +
"name," +
"job," +
"jobType," +
"oneShot," +
"realHost," +
"club," +
"status," +
"taskBrief," +
"nextRunTime," +
"lastRunTime," +
"lastResult," +
"modifiedAt" +
" FROM working_tasks WHERE id=?";
PreparedStatement selectIdStatement = null;
try {
selectIdStatement = connection.prepareStatement(selectIdSql);
selectIdStatement.setQueryTimeout(queryTimeout);
selectIdStatement.setLong(1, id);
ResultSet resultSet = selectIdStatement.executeQuery();
Vector allTasks = getSelectedWorkingTaskFromResultSet(resultSet, 1);
WorkingTask aTask = null;
if (allTasks.size() > 0)
aTask = (WorkingTask) allTasks.elementAt(0);
return aTask;
} finally {
if (selectIdStatement != null)
selectIdStatement.close();
}
}
void setRefreshTime(int which) throws Exception {
String oldRefreshTime = getRefreshTime();
StringTokenizer st = new StringTokenizer(oldRefreshTime, " ");
String tasks = "0";
String workingTasks = "0";
String holidays = "0";
String preferences = "0";
String candidates = "0";
try {
tasks = st.nextToken();
workingTasks = st.nextToken();
holidays = st.nextToken();
preferences = st.nextToken();
candidates = st.nextToken();
} catch (Exception e) {
}
String now = System.currentTimeMillis() + "";
if (which == REFRESH_TASK)
tasks = now;
else if (which == REFRESH_WORKING_TASK)
workingTasks = now;
else if (which == REFRESH_HOLIDAY)
holidays = now;
else if (which == REFRESH_PREFERENCE)
preferences = now;
else if (which == REFRESH_CANDIDATE)
candidates = now;
StringBuffer buffer = new StringBuffer();
buffer.append(tasks);
buffer.append(" ");
buffer.append(workingTasks);
buffer.append(" ");
buffer.append(holidays);
buffer.append(" ");
buffer.append(preferences);
buffer.append(" ");
buffer.append(candidates);
buffer.append(" ");
String updateSql = "UPDATE schedulerRefresh SET refreshTime='" + buffer.toString() + "'";
Statement statement = null;
try {
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
statement.executeUpdate(updateSql);
} finally {
if (statement != null)
statement.close();
}
}
public String testConnection() throws Exception {
if (connection == null || connection.isClosed())
throw new DatabaseConnectionBrokenException();
selectTask("a");
return "ok";
}
private int updateDoerTalker(String name) throws Exception {
String updateSql = "UPDATE doerTalkers SET checkinTime=? WHERE name=?";
PreparedStatement updateStatement = null;
int nn = 0;
try {
updateStatement = connection.prepareStatement(updateSql);
updateStatement.setQueryTimeout(interval);
updateStatement.setLong(++nn, System.currentTimeMillis());
updateStatement.setString(++nn, name);
return updateStatement.executeUpdate();
} finally {
if (updateStatement != null)
updateStatement.close();
}
}
public int updateEntitlement(String role, int entitlement) throws Exception {
String updateSql = "UPDATE entitlements SET entitlement=? WHERE role=?";
PreparedStatement updateStatement = null;
int nn = 0;
try {
updateStatement = connection.prepareStatement(updateSql);
updateStatement.setQueryTimeout(queryTimeout);
updateStatement.setInt(++nn, entitlement);
updateStatement.setString(++nn, role);
return updateStatement.executeUpdate();
} finally {
if (updateStatement != null)
updateStatement.close();
}
}
public int updateHoliday(Holiday holiday) throws Exception {
String updateSql = "UPDATE holiday SET setName=?, name=?, theDay=?, comments=? WHERE id=?";
PreparedStatement updateStatement = null;
int nn = 0;
try {
updateStatement = connection.prepareStatement(updateSql);
updateStatement.setQueryTimeout(queryTimeout);
updateStatement.setString(++nn, holiday.setName);
updateStatement.setString(++nn, holiday.name);
updateStatement.setString(++nn, holiday.theDay);
updateStatement.setString(++nn, holiday.comments);
updateStatement.setLong(++nn, holiday.id);
return updateStatement.executeUpdate();
} finally {
if (updateStatement != null)
updateStatement.close();
setRefreshTime(REFRESH_HOLIDAY);
}
}
public int updateHolidayRule(HolidayRule holidayRule) throws Exception {
String updateSql = "UPDATE holiday_rule SET setName=?, name=?, holidaying=?, fixMonth=?, " +
" fixDay=?, monthlyMonth=?, monthlyWhichWeek=?, " +
" monthlyWhichWeekWeekDay=?, comments=? WHERE id=?";
PreparedStatement updateStatement = null;
int nn = 0;
try {
updateStatement = connection.prepareStatement(updateSql);
updateStatement.setQueryTimeout(queryTimeout);
updateStatement.setString(++nn, holidayRule.setName);
updateStatement.setString(++nn, holidayRule.name);
updateStatement.setString(++nn, holidayRule.holidaying);
updateStatement.setInt(++nn, holidayRule.fixMonth);
updateStatement.setInt(++nn, holidayRule.fixDay);
updateStatement.setInt(++nn, holidayRule.monthlyMonth);
updateStatement.setInt(++nn, holidayRule.monthlyWhichWeek);
updateStatement.setInt(++nn, holidayRule.monthlyWhichWeekWeekDay);
updateStatement.setString(++nn, holidayRule.comments);
updateStatement.setLong(++nn, holidayRule.id);
return updateStatement.executeUpdate();
} finally {
if (updateStatement != null)
updateStatement.close();
}
}
public int updateRole(String name, String role) throws Exception {
String updateSql = "UPDATE roles SET role=? WHERE name=?";
PreparedStatement updateStatement = null;
int nn = 0;
try {
updateStatement = connection.prepareStatement(updateSql);
updateStatement.setQueryTimeout(queryTimeout);
updateStatement.setString(++nn, role);
updateStatement.setString(++nn, name);
return updateStatement.executeUpdate();
} finally {
if (updateStatement != null)
updateStatement.close();
}
}
public int updateSchedulerPreference(Properties properties) throws Exception {
String sql = "UPDATE schedulerPreference SET theValue=? WHERE theKey=?";
PreparedStatement updateTaskStatusStatement = null;
Enumeration enumeration = properties.keys();
int rows = 0;
try {
updateTaskStatusStatement = connection.prepareStatement(sql);
updateTaskStatusStatement.setQueryTimeout(queryTimeout);
while (enumeration.hasMoreElements()) {
String theKey = (String) enumeration.nextElement();
String theValue = properties.getProperty(theKey);
updateTaskStatusStatement.clearParameters();
updateTaskStatusStatement.setString(1, theValue);
updateTaskStatusStatement.setString(2, theKey);
rows += updateTaskStatusStatement.executeUpdate();
}
return rows;
} finally {
if (updateTaskStatusStatement != null)
updateTaskStatusStatement.close();
setRefreshTime(REFRESH_PREFERENCE);
}
}
public int updateSettings(Properties settings) throws Exception {
String sql = "UPDATE schedulerSettings SET theValue=? WHERE theKey=?";
PreparedStatement updateTaskStatusStatement = null;
Enumeration enumeration = settings.keys();
int rows = 0;
try {
updateTaskStatusStatement = connection.prepareStatement(sql);
updateTaskStatusStatement.setQueryTimeout(queryTimeout);
while (enumeration.hasMoreElements()) {
String theKey = (String) enumeration.nextElement();
String theValue = settings.getProperty(theKey);
updateTaskStatusStatement.clearParameters();
updateTaskStatusStatement.setString(1, theValue);
updateTaskStatusStatement.setString(2, theKey);
rows += updateTaskStatusStatement.executeUpdate();
}
return rows;
} finally {
if (updateTaskStatusStatement != null)
updateTaskStatusStatement.close();
}
}
public int updateTask(Task task, long newModifiedAt) throws Exception {
String updateSql = "UPDATE tasks SET " +
"name=?," +
"job=?," +
"jobType=?," +
"repeating=?," +
"startTime=?," +
"minutelyPeriod=?," +
"hourlyPeriod=?," +
"dailyPeriod=?," +
"weeklyPeriod=?," +
"weeklyWeekDay=?," +
"monthlyPeriod=?," +
"monthlyTheDay=?," +
"monthlyWhichWeek=?," +
"monthlyWhichWeekWeekDay=?," +
"specifiedTimes=?," +
"wouldBe=?," +
"holidayPolicy=?," +
"holidaySet=?," +
"expiration=?," +
"duration=?," +
"killIfExceedsDuration=?," +
"hostname=?," +
"club=?," +
"description=?," +
"comments=?," +
"status=?," +
"nextRunTime=?," +
"lastRunTime=?," +
"creator=?," +
"alarmEmail=?," +
"modifiedAt=? " +
" WHERE id=? AND modifiedAt=?";
PreparedStatement updateStatement = null;
int nn = 0;
try {
updateStatement = connection.prepareStatement(updateSql);
updateStatement.setQueryTimeout(queryTimeout);
updateStatement.setString(++nn, task.name);
new ZipBlob().setText(updateStatement, ++nn, task.getJob());
updateStatement.setInt(++nn, task.getJobType());
updateStatement.setInt(++nn, task.getRepeating());
updateStatement.setLong(++nn, task.getStartTime());
updateStatement.setInt(++nn, task.getMinutelyPeriod());
updateStatement.setInt(++nn, task.getHourlyPeriod());
updateStatement.setInt(++nn, task.getDailyPeriod());
updateStatement.setInt(++nn, task.getWeeklyPeriod());
updateStatement.setInt(++nn, task.getWeeklyWeekDay());
updateStatement.setInt(++nn, task.getMonthlyPeriod());
updateStatement.setInt(++nn, task.getMonthlyTheDay());
updateStatement.setInt(++nn, task.getMonthlyWhichWeek());
updateStatement.setInt(++nn, task.getMonthlyWhichWeekWeekDay());
updateStatement.setString(++nn, task.getSpecifiedTimes());
updateStatement.setLong(++nn, task.getWouldBe());
updateStatement.setInt(++nn, task.getHolidayPolicy());
updateStatement.setString(++nn, task.getHolidaySet());
updateStatement.setLong(++nn, task.getExpiration());
updateStatement.setLong(++nn, task.getDuration());
updateStatement.setInt(++nn, task.getKillIfExceedsDuration());
updateStatement.setString(++nn, task.getHostname());
updateStatement.setString(++nn, task.getClub());
updateStatement.setString(++nn, task.description);
updateStatement.setString(++nn, task.comments);
updateStatement.setInt(++nn, task.status);
updateStatement.setLong(++nn, task.getNextRunTime());
updateStatement.setLong(++nn, task.lastRunTime);
updateStatement.setString(++nn, task.getCreator());
updateStatement.setString(++nn, task.getAlarmEmail());
updateStatement.setLong(++nn, newModifiedAt);
updateStatement.setLong(++nn, task.getId());
updateStatement.setLong(++nn, task.modifiedAt);
return updateStatement.executeUpdate();
} finally {
if (updateStatement != null)
updateStatement.close();
setRefreshTime(REFRESH_TASK);
}
}
public int updateTaskRuntimeInfo(Task task) throws Exception {
String updateSql = "UPDATE tasks SET " +
"wouldBe=?," +
"status=?," +
"nextRunTime=?," +
"lastRunTime=?" +
" WHERE id=?";
PreparedStatement updateStatement = null;
int nn = 0;
try {
updateStatement = connection.prepareStatement(updateSql);
updateStatement.setQueryTimeout(queryTimeout);
updateStatement.setLong(++nn, task.getWouldBe());
updateStatement.setInt(++nn, task.status);
updateStatement.setLong(++nn, task.getNextRunTime());
updateStatement.setLong(++nn, task.lastRunTime);
updateStatement.setLong(++nn, task.getId());
return updateStatement.executeUpdate();
} finally {
if (updateStatement != null)
updateStatement.close();
setRefreshTime(REFRESH_TASK);
}
}
public int updateWorkingTask(WorkingTask workingTask) throws Exception {
if (workingTask.lastResult.length() > lastResultMaxSize)
workingTask.lastResult =
workingTask.lastResult.substring(0, lastResultMaxSize);
String updateSql = "UPDATE working_tasks SET " +
"status=?," +
"lastResult=?," +
"modifiedAt=? " +
" WHERE id0=? AND nextRunTime=?";
PreparedStatement updateStatement = null;
int nn = 0;
try {
updateStatement = connection.prepareStatement(updateSql);
updateStatement.setQueryTimeout(queryTimeout);
updateStatement.setInt(++nn, workingTask.status);
updateStatement.setString(++nn, workingTask.lastResult);
updateStatement.setLong(++nn, System.currentTimeMillis());
updateStatement.setLong(++nn, workingTask.getId0());
updateStatement.setLong(++nn, workingTask.nextRunTime);
return updateStatement.executeUpdate();
} finally {
if (updateStatement != null)
updateStatement.close();
setRefreshTime(REFRESH_WORKING_TASK);
}
}
public int updateWorkingTaskForExceedingDuration(WorkingTask workingTask) throws Exception {
String updateSql = "UPDATE working_tasks SET " +
"status=" + WorkingTask.STATUS_EXCEEDED_DURATION + "," +
"modifiedAt=? " +
" WHERE id0=? AND nextRunTime=? AND status=" + WorkingTask.STATUS_STARTED;;
PreparedStatement updateStatement = null;
int nn = 0;
try {
updateStatement = connection.prepareStatement(updateSql);
updateStatement.setQueryTimeout(queryTimeout);
updateStatement.setLong(++nn, System.currentTimeMillis());
updateStatement.setLong(++nn, workingTask.getId0());
updateStatement.setLong(++nn, workingTask.nextRunTime);
return updateStatement.executeUpdate();
} finally {
if (updateStatement != null)
updateStatement.close();
setRefreshTime(REFRESH_WORKING_TASK);
}
}
}