Metacat

/**

 *  '$RCSfile$'

 *    Purpose: A Class that manages database access of scheduled task

 *             information.

 *  Copyright: 2009 Regents of the University of California and the

 *             National Center for Ecological Analysis and Synthesis

 *    Authors: Michael Daigle

 *

 *   '$Author: daigle $'

 *     '$Date: 2009-03-23 13:56:56 -0800 (Mon, 23 Mar 2009) $'

 * '$Revision: 4854 $'

 *

 * This program is free software; you can redistribute it and/or modify

 * it under the terms of the GNU General Public License as published by

 * the Free Software Foundation; either version 2 of the License, or

 * (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

 */

package edu.ucsb.nceas.metacat.scheduler;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Timestamp;

import java.util.Calendar;

import java.util.Vector;

import org.apache.log4j.Logger;

import org.quartz.Job;

import edu.ucsb.nceas.metacat.shared.AccessException;

import edu.ucsb.nceas.metacat.shared.BaseAccess;

public class ScheduledJobAccess extends BaseAccess {

	private Logger logMetacat = Logger.getLogger(ScheduledJobAccess.class);

		super("ScheduledJobAccess");

	}

	 * Get a job based on it's id

	 *

	 * @param jobId

	 *            the id of the job in the database

	 * @return the scheduled job data object that represents the desired job

	 */

		ScheduledJobDAO jobDAO = null;

		try {

			String sql = "SELECT * FROM scheduled_job WHERE id = ? AND status != 'deleted'";

			pstmt = conn.prepareStatement(sql);

			pstmt.setLong(1, jobId);

			sqlReport += " [" + jobId + "]";

			ResultSet resultSet = pstmt.getResultSet();

			if (resultSet.next()) {

				jobDAO = populateDAO(resultSet);

			}

		} catch (SQLException sqle) {

			throw new AccessException("ScheduledJobAccess.getJob - SQL error when getting scheduled job: "

					+ jobId  + " : "  + sqle.getMessage());

		} finally {

			try {

				if (pstmt != null) {

					pstmt.close();

				}

			} catch (SQLException sqle) {

			}

		}

		// DAOs and add that list to the job DAO

		Vector<ScheduledJobParamDAO> jobParamList =

			jobParamAccess.getJobParamsForJobId(jobDAO.getId());

		for(ScheduledJobParamDAO jobParamDAO : jobParamList) {

			jobDAO.addJobParam(jobParamDAO);

		}

		return jobDAO;

	}

	 * Get a job by it's name

	 * @param jobName the name of the job to get

	 * @return the scheduled job data object that represents the desired job

	 */

		ScheduledJobDAO jobDAO = null;

			pstmt.setString(1, jobName);

			sqlReport += " [" + jobName + "]";

			ResultSet resultSet = pstmt.getResultSet();

				jobDAO = populateDAO(resultSet);

				throw new AccessException("ScheduledJobAccess.getJobByName - could not find scheduled job with name: "

						+ jobName);

		} catch (SQLException sqle) {

			throw new AccessException("ScheduledJobAccess.getJobByName - SQL error when getting scheduled job by name: "

					+ jobName  + " : "  + sqle.getMessage());

			try {

				if (pstmt != null) {

					pstmt.close();

				}

			} catch (SQLException sqle) {

				logMetacat.error("ScheduledJobAccess.getJobByName - An error occurred "

						+ "closing prepared statement: " + sqle.getMessage());

				DBConnectionPool.returnDBConnection(conn, serialNumber);

			}

		// DAOs and add that list to the job DAO

		Vector<ScheduledJobParamDAO> jobParamList =

			jobParamAccess.getJobParamsForJobId(jobDAO.getId());

		for(ScheduledJobParamDAO jobParamDAO : jobParamList) {

			jobDAO.addJobParam(jobParamDAO);

		}

	}

	 * Get all jobs that have a given parameter with a given value

	 *

	 * @param groupName

	 *            the group to which the job belongs. This keeps us from

	 *            returning unrelated jobs that just happen to have a similar

	 *            parameter

	 * @param paramName

	 *            the name of the parameter we are looking for

	 * @param paramValue

	 *            the value of the parameter we are looking for

	 * @return a HashMap of job data objects with all jobs in a given group that

	 *         have parameters that match our parameter.

	 */

		HashMap<Long, ScheduledJobDAO> jobsWithParamMap = new HashMap<Long, ScheduledJobDAO>();

			ScheduledJobDAO jobDAO = allJobsMap.get(jobDAOId);

			if (paramValue != null && paramName != null) {

				ScheduledJobParamDAO jobParamDAO = jobDAO.getJobParam(paramName);

				if(jobParamDAO != null && jobParamDAO.getValue().equals(paramValue)) {

					jobsWithParamMap.put(jobDAOId, jobDAO);

				}

			}

		}

		return jobsWithParamMap;

	}

	 * Get all jobs for a given group.  A group is typically a category that coincides with

	 * the job type (has it's own job implementation).

	 * @param groupName the name of the group we want to search

	 * @return a HashMap of job data objects for the desired group.

	 */

		ScheduledJobDAO jobDAO = null;

		HashMap<Long, ScheduledJobDAO> allJobDAOs = new HashMap<Long, ScheduledJobDAO>();

		try {

			String sql = "SELECT * FROM scheduled_job WHERE status != 'deleted'";

			if (groupName != null) {

				sql += " AND group_name = ?" ;

			}

			pstmt = conn.prepareStatement(sql);

				pstmt.setString(1, groupName);

			logMetacat.info(sqlReport);

			pstmt.execute();

			ResultSet resultSet = pstmt.getResultSet();

			while (resultSet.next()) {

				jobDAO = populateDAO(resultSet);

				allJobDAOs.put(jobDAO.getId(), jobDAO);

			}

			// takes a little stress off the database by avoiding a join.

			Vector<ScheduledJobParamDAO> jobParamList =

				jobParamAccess.getAllJobParams();

				if( allJobDAOs.containsKey(jobId)) {

					allJobDAOs.get(jobId).addJobParam(jobParamDAO);

				}

			}

			return allJobDAOs;

		} catch (SQLException sqle) {

			throw new AccessException("ScheduledJobAccess.getJobByName - SQL error when getting all jobs : "

					+ sqle.getMessage());

		} finally {

			try {

				if (pstmt != null) {

					pstmt.close();

				}

			} catch (SQLException sqle) {

			} finally {

				DBConnectionPool.returnDBConnection(conn, serialNumber);

			}

		}

	}

	 * Create a job in the database.

	 *

	 * @param name

	 *            the name of the job. This should be unique

	 * @param triggerName

	 *            the name of the trigger that is registered in the scheduler

	 *            service

	 * @param groupName

	 *            the group of the job

	 * @param jobClass

	 *            the class that implements the job functionality. The name of

	 *            this class will be extracted and put into the database.

	 * @param startTime

	 *            the time when the job should first run

	 * @param intervalValue

	 *            the amount of time between job runs.

	 * @param intervalUnit

	 *            the unit of time that the intervalValue represents. Valid

	 *            values are s,m,h, d and w

	 * @param jobParams

	 *            a map of parameters that are associated with this job.

	 */

			throws AccessException {

		jobDAO.setGroupName(groupName);

		jobDAO.setStartTime(new Timestamp(startTime.getTimeInMillis()));

		jobDAO.setIntervalUnit(intervalUnit);

	}

	/**

	 * Create a job in the database

	 * @param jobDAO the job data object that holds necessary information

	 * @param jobParams a map of parameters that are associated with this job.

	 */

		PreparedStatement pstmt = null;

			String sql =

				+ "VALUES(now(), now(), ?, ?, ?, ?, ?, ?, ?, ?, ?)";

			pstmt.setString(1, jobDAO.getStatus());

			pstmt.setString(2, jobDAO.getName());

			pstmt.setString(4, jobDAO.getGroupName());

			pstmt.setString(5, jobDAO.getClassName());

			pstmt.setTimestamp(6, jobDAO.getStartTime());

			pstmt.setInt(8, jobDAO.getIntervalValue());

			pstmt.setString(9, jobDAO.getIntervalUnit());

				+ " [" + jobDAO.getStatus() + ","

				+ jobDAO.getName() + ","

				+ jobDAO.getTriggerName() + ","

				+ jobDAO.getGroupName() + ","

				+ jobDAO.getClassName() + ",(Timestamp)"

				+ jobDAO.getStartTime().toString() + ",";

			if(jobDAO.getEndTime() == null) {

				sqlReport += "null,";

			} else {

				sqlReport += jobDAO.getEndTime().toString() + ",";

			}

			sqlReport += jobDAO.getIntervalValue() + ","

				+ jobDAO.getIntervalUnit() + "]";

			logMetacat.info(sqlReport);

		} catch (SQLException sqle) {

			throw new AccessException("ScheduledJobAccess.createJob - SQL error when creating scheduled job "

					+ jobDAO.getName()  + " : "  + sqle.getMessage());

			try {

				if (pstmt != null) {

					pstmt.close();

				}

			} catch (SQLException sqle) {

				logMetacat.error("ScheduledJobAccess.createJob - An error occurred "

						+ "closing prepared statement: " + sqle.getMessage());

			} finally {

				DBConnectionPool.returnDBConnection(conn, serialNumber);

			}

			try {

				updatedJobDAO = getJobByName(jobDAO.getName());

			} catch (AccessException ae) {

				if (updatedJobDAO != null) {

					updateJobStatus(updatedJobDAO);

				} else {

					logMetacat.warn("ScheduledJobAccess.createJob - Tried to delete non-existant scheduled job: "

							+ jobDAO.getName());

				}

			}

		}

	}

	 * Update the status of a job in the database

	 *

	 * @param jobDAO

	 *            the job data object that we want to change. The status should

	 *            already have been updated in this object

	 */

		if (jobDAO == null) {

			throw new AccessException("ScheduledJobAccess.updateJobStatus - job DAO cannot be null.");

		}

			String sql = "UPDATE scheduled_job SET status = ? WHERE id = ?";

			pstmt = conn.prepareStatement(sql);

			pstmt.setLong(2, jobDAO.getId());

				+  " [" + jobDAO.getStatus() + "," + jobDAO.getId() + "]";

			logMetacat.info(sqlReport);

			pstmt.execute();

		} catch (SQLException sqle) {

			throw new AccessException("ScheduledJobAccess.deleteJob - SQL error when "

					+ "deleting scheduled job " + jobDAO.getName()  + " : "  + sqle.getMessage());

			try {

				if (pstmt != null) {

					pstmt.close();

				}

			} catch (SQLException sqle) {

				logMetacat.error("ScheduledJobAccess.updateJobStatus - An error occurred "

						+ "closing prepared statement: " + sqle.getMessage());

			} finally {

				DBConnectionPool.returnDBConnection(conn, serialNumber);

			}

		}

	}

	 * Populate a job data object with the current row in a resultset

	 *

	 * @param resultSet

	 *            the result set which is already pointing to the desired row.

	 * @return a scheduled job data object

	 */

		ScheduledJobDAO jobDAO = new ScheduledJobDAO();

		jobDAO.setId(resultSet.getLong("id"));

		jobDAO.setCreateTime(resultSet.getTimestamp("date_created"));

		jobDAO.setModTime(resultSet.getTimestamp("date_updated"));

		jobDAO.setStatus(resultSet.getString("status"));

		jobDAO.setName(resultSet.getString("name"));

		jobDAO.setTriggerName(resultSet.getString("trigger_name"));

		jobDAO.setGroupName(resultSet.getString("group_name"));

		jobDAO.setClassName(resultSet.getString("class_name"));

		jobDAO.setStartTime(resultSet.getTimestamp("start_time"));

		jobDAO.setIntervalUnit(resultSet.getString("interval_unit"));

		return jobDAO;

	}