/* Copyright 1999-2008 Acelet.org. All rights reserved. GPL v2 license */
/** @author Wei Jiang */
package com.acelet.s.chore;
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 AccessChoreDatabase {
public static final String DATABASE_SCHEMA_VERSION = "6.0";
protected static int REFRESH_CHORE = 1;
protected static int REFRESH_WORKING_CHORE = 2;
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 AccessChoreDatabase(Connection connection) throws Exception {
this.connection = connection;
this.connection.setAutoCommit(true);
if (initialized == false) {
getLastResultMaxSize();
initialized = true;
}
}
int changeChoreStatus(String choreName, int newStatus) throws Exception {
String updateChoreStatusSql = "UPDATE chores SET status=? " +
" WHERE name=?";
PreparedStatement updateChoreStatusStatement = null;
int nn = 0;
int rows = 0;
try {
updateChoreStatusStatement = connection.prepareStatement(updateChoreStatusSql);
updateChoreStatusStatement.setQueryTimeout(queryTimeout);
updateChoreStatusStatement.setInt(++nn, newStatus);
updateChoreStatusStatement.setString(++nn, choreName);
rows = updateChoreStatusStatement.executeUpdate();
if (rows == 1)
connection.commit();
else
connection.rollback();
return rows;
} finally {
if (updateChoreStatusStatement != null)
updateChoreStatusStatement.close();
setRefreshTime(REFRESH_CHORE);
}
}
public int deleteCandidateChore(long id) throws Exception {
String sql = "DELETE FROM candidate_chores WHERE id=" + id;
Statement statement = null;
int rows = 0;
try {
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
rows = statement.executeUpdate(sql);
if (rows == 1)
connection.commit();
else
connection.rollback();
return rows;
} finally {
if (statement != null)
statement.close();
}
}
public int deleteChore(long id) throws Exception {
String sql = "DELETE FROM chores WHERE id=" + id;
Statement statement = null;
int rows = 0;
try {
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
rows = statement.executeUpdate(sql);
if (rows == 1)
connection.commit();
else
connection.rollback();
return rows;
} finally {
if (statement != null)
statement.close();
setRefreshTime(REFRESH_CHORE);
}
}
public int deleteChore(String name) throws Exception {
String sql = "DELETE FROM chores WHERE name='" + name + "'";
Statement statement = null;
int rows = 0;
try {
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
rows = statement.executeUpdate(sql);
if (rows == 1)
connection.commit();
else
connection.rollback();
return rows;
} finally {
if (statement != null)
statement.close();
setRefreshTime(REFRESH_CHORE);
}
}
public int deleteDoerTalker(String name) throws Exception {
String sql = "DELETE FROM watchdogDoerTalker WHERE name='" + name + "'";
Statement statement = null;
int rows = 0;
try {
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
rows = statement.executeUpdate(sql);
if (rows == 1)
connection.commit();
else
connection.rollback();
return rows;
} finally {
if (statement != null)
statement.close();
}
}
public int deleteOldWorkingChores(long workingChoreRetireTime) throws Exception {
String sql = "DELETE FROM working_chores WHERE modifiedAt<?";
PreparedStatement deleteStatement = null;
int nn = 0;
int rows = 0;
try {
deleteStatement = connection.prepareStatement(sql);
deleteStatement.setQueryTimeout(queryTimeout);
deleteStatement.setLong(++nn, workingChoreRetireTime);
rows = deleteStatement.executeUpdate();
connection.commit();
return rows;
} finally {
if (deleteStatement != null)
deleteStatement.close();
}
}
public int deleteWorkingChore(long id) throws Exception {
String sql = "DELETE FROM working_chores WHERE id=" + id;
Statement statement = null;
int rows = 0;
try {
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
rows = statement.executeUpdate(sql);
if (rows == 1)
connection.commit();
else
connection.rollback();
return rows;
} finally {
if (statement != null)
statement.close();
}
}
private void getLastResultMaxSize() throws Exception {
Statement statement = null;
int nn = 1;
try {
String testSql = "SELECT lastResult FROM working_chores";
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 getNumberOfChores() throws Exception {
Statement statement = null;
int nn = 1;
try {
String sql = "SELECT count(*) FROM chores";
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
ResultSet resultSet = statement.executeQuery(sql);
resultSet.next();
return resultSet.getInt(1);
} finally {
if (statement != null)
statement.close();
}
}
private String getRefreshTime() throws Exception {
String sql = "SELECT refreshTime FROM watchdogRefresh";
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 chores = "0";
String workingChores = "0";
String preferences = "0";
String candidates = "0";
try {
chores = st.nextToken();
workingChores = st.nextToken();
preferences = st.nextToken();
candidates = st.nextToken();
} catch (Exception e) {
}
StringBuffer buffer = new StringBuffer();
buffer.append(chores);
buffer.append(" ");
buffer.append(workingChores);
buffer.append(" ");
buffer.append(preferences);
buffer.append(" ");
buffer.append(candidates);
buffer.append(" ");
return buffer.toString();
} else
throw new NameNotFoundException("watchdogRefresh");
} finally {
if (statement != null)
statement.close();
}
}
public String getRefreshTime(String doerTalkerName) throws Exception {
try {
return getRefreshTime();
} finally {
registerDoerTalker(doerTalkerName);
}
}
private Vector getSelectedCandidateChoreFromResultSet(ResultSet resultSet) throws Exception {
Vector allCandidateChores = new Vector();
while (resultSet.next()) {
long id = resultSet.getLong("id");
String name = resultSet.getString("name");
if (name == null)
name = "";
CandidateChore candidateChore = new CandidateChore(id, name);
allCandidateChores.add(candidateChore);
}
return allCandidateChores;
}
private Vector getSelectedChoreFromResultSet(ResultSet resultSet) throws Exception {
Vector allChores = new Vector();
long id = Chore.NOT_SPECIFIED;
String name = "";
String stamp = "";
String job = "";
int jobType = Chore.NOT_SPECIFIED;
String triggerObject = "";
int triggerType = Chore.NOT_SPECIFIED;
int delay = 0;
long fromPeriod = Chore.NOT_SPECIFIED;
long toPeriod = Long.MAX_VALUE;
long duration = Long.MAX_VALUE;
int killIfExceedsDuration = 0;
String hostname = "";
String club = "";
String description = "";
String comments = "";
int status = Chore.NOT_SPECIFIED;
long lastTriggerTime = 0;
long lastRunTime = Chore.NOT_SPECIFIED;
String creator = "";
String alarmEmail = "";
long modifiedAt = Chore.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");
triggerObject = resultSet.getString("triggerObject");
triggerType = resultSet.getInt("triggerType");
delay = resultSet.getInt("delay");
fromPeriod = resultSet.getLong("fromPeriod");
toPeriod = resultSet.getLong("toPeriod");
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");
lastTriggerTime = resultSet.getLong("lastTriggerTime");
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 = Chore.STATUS_ERROR;
if (id == Chore.NOT_SPECIFIED)
continue;
}
Chore chore = new Chore(id);
chore.name = name;
try {
chore.restore(
name,
stamp,
job,
jobType,
triggerObject,
triggerType,
delay,
fromPeriod,
toPeriod,
duration,
killIfExceedsDuration,
hostname,
club,
description,
comments,
status,
lastTriggerTime,
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");
}
allChores.add(chore);
}
return allChores;
}
private Vector getSelectedDoertalkerFromResultSet(ResultSet resultSet) throws Exception {
Vector allDoertalkerVector = new Vector();
while (resultSet.next()) {
String name = resultSet.getString("name");
long checkinTime = resultSet.getLong("checkinTime");
WatchdogDoerTalker doerTalker = new WatchdogDoerTalker(name, checkinTime);
allDoertalkerVector.add(doerTalker);
}
return allDoertalkerVector;
}
private Vector getSelectedWorkingChoreFromResultSet(ResultSet resultSet, int rows)
throws Exception {
Vector allWorkingChores = new Vector();
long id = Chore.NOT_SPECIFIED;
long id0 = Chore.NOT_SPECIFIED;
String name = "";
String stamp = "";
String job = "";
int jobType = Chore.NOT_SPECIFIED;
int oneShot = Chore.NOT_SPECIFIED;
String realHost = "";
String club = "";
int status = Chore.NOT_SPECIFIED;
String choreBrief = "";
long lastTriggerTime = 0;
long lastRunTime = Chore.NOT_SPECIFIED;
String lastResult = "";
long modifiedAt = Chore.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 = "";
stamp = resultSet.getString("stamp");
job = new ZipBlob().getText(resultSet, "job");
jobType = resultSet.getInt("jobType");
oneShot = resultSet.getInt("oneShot");
realHost = resultSet.getString("realHost");
if (realHost == null)
realHost = "";
club = resultSet.getString("club");
if (club == null)
club = "";
status = resultSet.getInt("status");
choreBrief = resultSet.getString("choreBrief");
lastTriggerTime = resultSet.getLong("lastTriggerTime");
lastRunTime = resultSet.getLong("lastRunTime");
lastResult = resultSet.getString("lastResult");
if (lastResult == null)
lastResult = "";
modifiedAt = resultSet.getLong("modifiedAt");
} catch (Exception ex) {
status = WorkingChore.STATUS_ERROR;
lastResult = ex.toString();
}
WorkingChore workingChore = new WorkingChore();
workingChore.restore(
id,
id0,
name,
stamp,
job,
jobType,
oneShot,
realHost,
club,
status,
choreBrief,
lastTriggerTime,
lastRunTime,
lastResult,
modifiedAt);
allWorkingChores.add(workingChore);
}
return allWorkingChores;
}
public int insertCandidateChore(String choreName) throws Exception {
Chore chore = selectChore(choreName);
if (chore == null)
throw new NameNotFoundException(choreName);
String insertSql = "INSERT INTO candidate_chores (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, choreName);
return insertStatement.executeUpdate();
} finally {
if (insertStatement != null)
insertStatement.close();
setRefreshTime(REFRESH_CANDIDATE);
}
}
public int insertChore(Chore chore) throws Exception {
String insertSql = "INSERT INTO chores (" +
"id," +
"name," +
"job," +
"jobType," +
"triggerObject," +
"triggerType," +
"delay," +
"fromPeriod," +
"toPeriod," +
"duration," +
"killIfExceedsDuration," +
"hostname," +
"club," +
"description," +
"comments," +
"status," +
"lastTriggerTime," +
"lastRunTime," +
"creator," +
"alarmEmail," +
"modifiedAt) VALUES " +
"(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement insertStatement = null;
int nn = 0;
int rows = 0;
try {
insertStatement = connection.prepareStatement(insertSql);;
insertStatement.setQueryTimeout(queryTimeout);
insertStatement.setLong(++nn, chore.getId());
insertStatement.setString(++nn, chore.name);
new ZipBlob().setText(insertStatement, ++nn, chore.getJob());
insertStatement.setInt(++nn, chore.getJobType());
insertStatement.setString(++nn, chore.getTriggerObject());
insertStatement.setInt(++nn, chore.getTriggerType());
insertStatement.setInt(++nn, chore.getDelay());
insertStatement.setLong(++nn, chore.getFromPeriod());
insertStatement.setLong(++nn, chore.getToPeriod());
insertStatement.setLong(++nn, chore.getDuration());
insertStatement.setInt(++nn, chore.getKillIfExceedsDuration());
insertStatement.setString(++nn, chore.getHostname());
insertStatement.setString(++nn, chore.getClub());
insertStatement.setString(++nn, chore.description);
insertStatement.setString(++nn, chore.comments);
insertStatement.setInt(++nn, chore.status);
insertStatement.setLong(++nn, chore.lastTriggerTime);
insertStatement.setLong(++nn, chore.lastRunTime);
insertStatement.setString(++nn, chore.getCreator());
insertStatement.setString(++nn, chore.getAlarmEmail());
insertStatement.setLong(++nn, chore.modifiedAt);
rows = insertStatement.executeUpdate();
if (rows == 1)
connection.commit();
else
connection.rollback();
return rows;
} finally {
if (insertStatement != null)
insertStatement.close();
setRefreshTime(REFRESH_CHORE);
}
}
private int insertDoerTalker(String name) throws Exception {
String insertSql = "INSERT INTO watchdogDoerTalker (name, checkinTime) VALUES(?,?)";
PreparedStatement insertStatement = null;
int nn = 0;
int rows = 0;
try {
insertStatement = connection.prepareStatement(insertSql);;
insertStatement.setQueryTimeout(interval);
insertStatement.setString(++nn, name);
insertStatement.setLong(++nn, System.currentTimeMillis());
try {
rows = insertStatement.executeUpdate();
} catch (SQLException sqlException) {
String sqlState = sqlException.getSQLState();
if (sqlState != null && sqlState.startsWith("23"))
rows = 0;
else
throw sqlException;
}
if (rows == 1)
connection.commit();
else
connection.rollback();
return rows;
} finally {
if (insertStatement != null)
insertStatement.close();
}
}
public int insertWatchdogPreference(String key, String value) throws Exception {
String insertSql = "INSERT INTO watchdogPreference (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 insertWorkingChore(WorkingChore workingChore) throws Exception {
if (workingChore.lastResult.length() > lastResultMaxSize)
workingChore.lastResult = workingChore.lastResult.substring(0, lastResultMaxSize);
String insertSql =
"INSERT INTO working_chores (" +
"id0," +
"name," +
"stamp," +
"job," +
"jobType," +
"oneShot," +
"realHost," +
"club," +
"status," +
"choreBrief," +
"lastTriggerTime," +
"lastRunTime," +
"lastResult," +
"modifiedAt" +
") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement insertStatement = null;
int nn = 0;
int rows = 0;
try {
insertStatement = connection.prepareStatement(insertSql);;
insertStatement.setQueryTimeout(queryTimeout);
insertStatement.setLong(++nn, workingChore.getId0());
insertStatement.setString(++nn, workingChore.name);
insertStatement.setString(++nn, workingChore.getStamp());
new ZipBlob().setText(insertStatement, ++nn, workingChore.getJob());
insertStatement.setInt(++nn, workingChore.getJobType());
insertStatement.setInt(++nn, workingChore.oneShot);
insertStatement.setString(++nn, workingChore.realHost);
insertStatement.setString(++nn, workingChore.club);
insertStatement.setInt(++nn, workingChore.status);
insertStatement.setString(++nn, workingChore.choreBrief);
insertStatement.setLong(++nn, workingChore.lastTriggerTime);
insertStatement.setLong(++nn, workingChore.lastRunTime);
insertStatement.setString(++nn, workingChore.lastResult);
insertStatement.setLong(++nn, workingChore.modifiedAt);
rows = insertStatement.executeUpdate();
if (rows == 1)
connection.commit();
else
connection.rollback();
setRefreshTime(REFRESH_WORKING_CHORE);
} 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 static void reset(Connection connection) throws Exception {
AccessChoreDatabase accessChoreDatabase = new AccessChoreDatabase(connection);
Properties properties = accessChoreDatabase.selectWatchdogPreference();
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 selectAllCandidateChores() throws Exception {
String selectAllSql = "SELECT " +
"id," +
"name " +
" FROM candidate_chores ORDER BY id";
PreparedStatement selectAllStatement = null;
try {
selectAllStatement = connection.prepareStatement(selectAllSql);
selectAllStatement.setQueryTimeout(queryTimeout);
ResultSet resultSet = selectAllStatement.executeQuery();
return getSelectedCandidateChoreFromResultSet(resultSet);
} finally {
if (selectAllStatement != null)
selectAllStatement.close();
}
}
public Vector selectAllChores() throws Exception {
String selectAllSql = "SELECT " +
"id," +
"name," +
"job," +
"jobType," +
"triggerObject," +
"triggerType," +
"delay," +
"fromPeriod," +
"toPeriod," +
"duration," +
"killIfExceedsDuration," +
"hostname," +
"club," +
"description," +
"comments," +
"status," +
"lastTriggerTime," +
"lastRunTime," +
"creator," +
"alarmEmail," +
"modifiedAt " +
" FROM chores ORDER BY name";
PreparedStatement selectAllStatement = null;
try {
selectAllStatement = connection.prepareStatement(selectAllSql);
selectAllStatement.setQueryTimeout(queryTimeout);
ResultSet resultSet = selectAllStatement.executeQuery();
return getSelectedChoreFromResultSet(resultSet);
} finally {
if (selectAllStatement != null)
selectAllStatement.close();
}
}
public Vector selectAllDoerTalkers() throws Exception {
String selectAllSql =
"SELECT name, checkinTime FROM watchdogDoerTalker 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 selectAllWorkingChores(long from, long to) throws Exception {
return selectAllWorkingChores(from, to, Integer.MAX_VALUE);
}
public Vector selectAllWorkingChores(long from, long to, int rows) throws Exception {
String selectAllSql = "SELECT " +
"id," +
"id0," +
"name," +
"stamp," +
"job," +
"jobType," +
"oneShot," +
"realHost," +
"club," +
"status," +
"choreBrief," +
"lastTriggerTime," +
"lastRunTime," +
"lastResult," +
"modifiedAt " +
" FROM working_chores 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 getSelectedWorkingChoreFromResultSet(resultSet, rows);
} finally {
if (selectAllStatement != null)
selectAllStatement.close();
}
}
public Chore selectChore(long id) throws Exception {
String selectIdSql = "SELECT " +
"id," +
"name," +
"job," +
"jobType," +
"triggerObject," +
"triggerType," +
"delay," +
"fromPeriod," +
"toPeriod," +
"duration," +
"killIfExceedsDuration," +
"hostname," +
"club," +
"description," +
"comments," +
"status," +
"lastTriggerTime," +
"lastRunTime," +
"creator," +
"alarmEmail," +
"modifiedAt " +
" FROM chores WHERE id=?";
PreparedStatement selectIdStatement = null;
try {
selectIdStatement = connection.prepareStatement(selectIdSql);
selectIdStatement.setQueryTimeout(queryTimeout);
selectIdStatement.setLong(1, id);
ResultSet resultSet = selectIdStatement.executeQuery();
Vector allChores = getSelectedChoreFromResultSet(resultSet);
Chore aChore = null;
if (allChores.size() > 0)
aChore = (Chore) allChores.elementAt(0);
return aChore;
} finally {
if (selectIdStatement != null)
selectIdStatement.close();
}
}
public Chore selectChore(String name) throws Exception {
String selectNameSql = "SELECT " +
"id," +
"name," +
"job," +
"jobType," +
"triggerObject," +
"triggerType," +
"delay," +
"fromPeriod," +
"toPeriod," +
"duration," +
"killIfExceedsDuration," +
"hostname," +
"club," +
"description," +
"comments," +
"status," +
"lastTriggerTime," +
"lastRunTime," +
"creator," +
"alarmEmail," +
"modifiedAt " +
" FROM chores WHERE name=?";
PreparedStatement selectNameStatement = null;
try {
selectNameStatement = connection.prepareStatement(selectNameSql);
selectNameStatement.setQueryTimeout(queryTimeout);
selectNameStatement.setString(1, name);
ResultSet resultSet = selectNameStatement.executeQuery();
Vector allChores = getSelectedChoreFromResultSet(resultSet);
Chore aChore = null;
if (allChores.size() > 0)
aChore = (Chore) allChores.elementAt(0);
return aChore;
} finally {
if (selectNameStatement != null)
selectNameStatement.close();
}
}
public Properties selectSettings() throws Exception {
Properties settings = new Properties();
String sql = "SELECT * from watchdogSettings";
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 String selectSchemaVersion() throws Exception {
String watchdogSchema = "?";
String selectSql = "SELECT theVersion FROM superWatchdogSchema";
Statement statement = null;
try {
statement = connection.createStatement();
statement.setQueryTimeout(queryTimeout);
ResultSet resultSet = statement.executeQuery(selectSql);
if (resultSet.next())
watchdogSchema = resultSet.getString("theVersion");
else
return "?";
if (resultSet.next())
return "??";
return watchdogSchema;
} finally {
if (statement != null)
statement.close();
}
}
public Properties selectWatchdogPreference() throws Exception {
Properties properties = new Properties();
String sql = "SELECT * from watchdogPreference";
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");
properties.setProperty(theKey, theValue);
}
return properties;
} finally {
if (statement != null)
statement.close();
}
}
public WorkingChore selectWorkingChore(long id) throws Exception {
String selectIdSql = "SELECT " +
"id," +
"id0," +
"name," +
"stamp," +
"job," +
"jobType," +
"oneShot," +
"realHost," +
"club," +
"status," +
"choreBrief," +
"lastTriggerTime," +
"lastRunTime," +
"lastResult," +
"modifiedAt" +
" FROM working_chores WHERE id=?";
PreparedStatement selectIdStatement = null;
try {
selectIdStatement = connection.prepareStatement(selectIdSql);
selectIdStatement.setQueryTimeout(queryTimeout);
selectIdStatement.setLong(1, id);
ResultSet resultSet = selectIdStatement.executeQuery();
Vector allChores = getSelectedWorkingChoreFromResultSet(resultSet, 1);
WorkingChore aChore = null;
if (allChores.size() > 0)
aChore = (WorkingChore) allChores.elementAt(0);
return aChore;
} finally {
if (selectIdStatement != null)
selectIdStatement.close();
}
}
private void setRefreshTime(int which) throws Exception {
String oldRefreshTime = getRefreshTime();
StringTokenizer st = new StringTokenizer(oldRefreshTime, " ");
String chores = "0";
String workingChores = "0";
String holidays = "0";
String preferences = "0";
String candidates = "0";
try {
chores = st.nextToken();
workingChores = st.nextToken();
preferences = st.nextToken();
candidates = st.nextToken();
} catch (Exception e) {
}
String now = System.currentTimeMillis() + "";
if (which == REFRESH_CHORE)
chores = now;
else if (which == REFRESH_WORKING_CHORE)
workingChores = now;
else if (which == REFRESH_PREFERENCE)
preferences = now;
else if (which == REFRESH_CANDIDATE)
candidates = now;
StringBuffer buffer = new StringBuffer();
buffer.append(chores);
buffer.append(" ");
buffer.append(workingChores);
buffer.append(" ");
buffer.append(preferences);
buffer.append(" ");
buffer.append(candidates);
buffer.append(" ");
String updateSql = "UPDATE watchdogRefresh 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();
selectChore("a");
return "ok";
}
int updateChore(Chore chore, long newModifiedAt) throws Exception {
String updateSql = "UPDATE chores SET " +
"name=?," +
"job=?," +
"jobType=?," +
"triggerObject=?," +
"triggerType=?," +
"delay=?," +
"fromPeriod=?," +
"toPeriod=?," +
"duration=?," +
"killIfExceedsDuration=?," +
"hostname=?," +
"club=?," +
"description=?," +
"comments=?," +
"status=?," +
"lastTriggerTime=?," +
"lastRunTime=?," +
"creator=?," +
"alarmEmail=?," +
"modifiedAt=? " +
" WHERE id=? AND modifiedAt=?";
PreparedStatement updateStatement = null;
int nn = 0;
int rows = 0;
try {
updateStatement = connection.prepareStatement(updateSql);
updateStatement.setQueryTimeout(queryTimeout);
updateStatement.setString(++nn, chore.name);
new ZipBlob().setText(updateStatement, ++nn, chore.getJob());
updateStatement.setInt(++nn, chore.getJobType());
updateStatement.setString(++nn, chore.getTriggerObject());
updateStatement.setInt(++nn, chore.getTriggerType());
updateStatement.setInt(++nn, chore.getDelay());
updateStatement.setLong(++nn, chore.getFromPeriod());
updateStatement.setLong(++nn, chore.getToPeriod());
updateStatement.setLong(++nn, chore.getDuration());
updateStatement.setInt(++nn, chore.getKillIfExceedsDuration());
updateStatement.setString(++nn, chore.getHostname());
updateStatement.setString(++nn, chore.getClub());
updateStatement.setString(++nn, chore.description);
updateStatement.setString(++nn, chore.comments);
updateStatement.setInt(++nn, chore.status);
updateStatement.setLong(++nn, chore.lastTriggerTime);
updateStatement.setLong(++nn, chore.lastRunTime);
updateStatement.setString(++nn, chore.getCreator());
updateStatement.setString(++nn, chore.getAlarmEmail());
updateStatement.setLong(++nn, newModifiedAt);
updateStatement.setLong(++nn, chore.getId());
updateStatement.setLong(++nn, chore.modifiedAt);
rows = updateStatement.executeUpdate();
if (rows == 1)
connection.commit();
else
connection.rollback();
return rows;
} finally {
if (updateStatement != null)
updateStatement.close();
setRefreshTime(REFRESH_CHORE);
}
}
public int updateChoreRuntimeInfo(Chore chore) throws Exception {
String updateSql = "UPDATE chores SET " +
"status=?," +
"lastTriggerTime=?," +
"lastRunTime=?" +
" WHERE id=?";
PreparedStatement updateStatement = null;
int nn = 0;
try {
updateStatement = connection.prepareStatement(updateSql);
updateStatement.setQueryTimeout(queryTimeout);
updateStatement.setInt(++nn, chore.status);
updateStatement.setLong(++nn, chore.lastTriggerTime);
updateStatement.setLong(++nn, chore.lastRunTime);
updateStatement.setLong(++nn, chore.getId());
return updateStatement.executeUpdate();
} finally {
connection.commit();
if (updateStatement != null)
updateStatement.close();
setRefreshTime(REFRESH_CHORE);
}
}
private int updateDoerTalker(String name) throws Exception {
String updateSql = "UPDATE watchdogDoerTalker SET checkinTime=? WHERE name=?";
PreparedStatement updateStatement = null;
int nn = 0;
int rows = 0;
try {
updateStatement = connection.prepareStatement(updateSql);
updateStatement.setQueryTimeout(interval);
updateStatement.setLong(++nn, System.currentTimeMillis());
updateStatement.setString(++nn, name);
rows = updateStatement.executeUpdate();
if (rows == 1)
connection.commit();
else
connection.rollback();
return rows;
} finally {
if (updateStatement != null)
updateStatement.close();
}
}
public int updateSettings(Properties settings) throws Exception {
String sql = "UPDATE watchdogSettings SET theValue=? WHERE theKey=?";
PreparedStatement updateChoreStatusStatement = null;
Enumeration enumeration = settings.keys();
int rows = 0;
try {
int size = settings.size();
updateChoreStatusStatement = connection.prepareStatement(sql);
updateChoreStatusStatement.setQueryTimeout(queryTimeout);
while (enumeration.hasMoreElements()) {
String theKey = (String) enumeration.nextElement();
String theValue = settings.getProperty(theKey);
updateChoreStatusStatement.clearParameters();
updateChoreStatusStatement.setString(1, theValue);
updateChoreStatusStatement.setString(2, theKey);
rows += updateChoreStatusStatement.executeUpdate();
}
if (rows >= 1)
connection.commit();
else
connection.rollback();
return rows;
} finally {
if (updateChoreStatusStatement != null)
updateChoreStatusStatement.close();
}
}
public int updateWatchdogPreference(Properties properties) throws Exception {
String sql = "UPDATE watchdogPreference SET theValue=? WHERE theKey=?";
PreparedStatement updateChoreStatusStatement = null;
Enumeration enumeration = properties.keys();
int rows = 0;
try {
updateChoreStatusStatement = connection.prepareStatement(sql);
updateChoreStatusStatement.setQueryTimeout(queryTimeout);
while (enumeration.hasMoreElements()) {
String theKey = (String) enumeration.nextElement();
String theValue = properties.getProperty(theKey);
updateChoreStatusStatement.clearParameters();
updateChoreStatusStatement.setString(1, theValue);
updateChoreStatusStatement.setString(2, theKey);
rows += updateChoreStatusStatement.executeUpdate();
}
return rows;
} finally {
if (updateChoreStatusStatement != null)
updateChoreStatusStatement.close();
setRefreshTime(REFRESH_PREFERENCE);
}
}
public int updateWorkingChore(WorkingChore workingChore) throws Exception {
if (workingChore.lastResult.length() > lastResultMaxSize)
workingChore.lastResult = workingChore.lastResult.substring(0, lastResultMaxSize);
String updateSql = "UPDATE working_chores SET " +
"status=?," +
"lastResult=?," +
"modifiedAt=? " +
" WHERE id0=? AND lastTriggerTime=?";
PreparedStatement updateStatement = null;
int nn = 0;
try {
updateStatement = connection.prepareStatement(updateSql);
updateStatement.setQueryTimeout(queryTimeout);
updateStatement.setInt(++nn, workingChore.status);
updateStatement.setString(++nn, workingChore.lastResult);
updateStatement.setLong(++nn, System.currentTimeMillis());
updateStatement.setLong(++nn, workingChore.getId0());
updateStatement.setLong(++nn, workingChore.lastTriggerTime);
return updateStatement.executeUpdate();
} finally {
if (updateStatement != null)
updateStatement.close();
setRefreshTime(REFRESH_WORKING_CHORE);
}
}
public int updateWorkingChoreForExceedingDuration(WorkingChore workingChore) throws Exception {
String updateSql = "UPDATE working_chores SET " +
"status=" + WorkingChore.STATUS_EXCEEDED_DURATION + "," +
"modifiedAt=? " +
" WHERE id0=? AND lastTriggerTime=? AND status=" + WorkingChore.STATUS_STARTED;;
PreparedStatement updateStatement = null;
int nn = 0;
try {
updateStatement = connection.prepareStatement(updateSql);
updateStatement.setQueryTimeout(queryTimeout);
updateStatement.setLong(++nn, System.currentTimeMillis());
updateStatement.setLong(++nn, workingChore.getId0());
updateStatement.setLong(++nn, workingChore.lastTriggerTime);
return updateStatement.executeUpdate();
} finally {
if (updateStatement != null)
updateStatement.close();
setRefreshTime(REFRESH_WORKING_CHORE);
}
}
}