<commit_before>package fi.csc.microarray.manager;

import it.sauronsoftware.cron4j.Scheduler;

import java.io.File;
import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.Arrays;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;
import java.util.Timer;
import java.util.TimerTask;

import javax.jms.JMSException;

import org.apache.log4j.Logger;
import org.eclipse.jetty.security.ConstraintMapping;
import org.eclipse.jetty.security.ConstraintSecurityHandler;
import org.eclipse.jetty.security.HashLoginService;
import org.eclipse.jetty.server.Connector;
import org.eclipse.jetty.server.Handler;
import org.eclipse.jetty.server.handler.DefaultHandler;
import org.eclipse.jetty.server.handler.HandlerCollection;
import org.eclipse.jetty.server.nio.SelectChannelConnector;
import org.eclipse.jetty.util.security.Constraint;
import org.eclipse.jetty.util.security.Password;
import org.eclipse.jetty.util.thread.QueuedThreadPool;
import org.eclipse.jetty.webapp.WebAppContext;
import org.h2.tools.Server;
import org.joda.time.DateTime;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import fi.csc.microarray.config.Configuration;
import fi.csc.microarray.config.DirectoryLayout;
import fi.csc.microarray.constants.ApplicationConstants;
import fi.csc.microarray.exception.MicroarrayException;
import fi.csc.microarray.messaging.JMSMessagingEndpoint;
import fi.csc.microarray.messaging.MessagingEndpoint;
import fi.csc.microarray.messaging.MessagingListener;
import fi.csc.microarray.messaging.MessagingTopic;
import fi.csc.microarray.messaging.MessagingTopic.AccessMode;
import fi.csc.microarray.messaging.MonitoredNodeBase;
import fi.csc.microarray.messaging.Topics;
import fi.csc.microarray.messaging.message.ChipsterMessage;
import fi.csc.microarray.messaging.message.FeedbackMessage;
import fi.csc.microarray.messaging.message.JobLogMessage;
import fi.csc.microarray.service.KeepAliveShutdownHandler;
import fi.csc.microarray.service.ShutdownCallback;
import fi.csc.microarray.util.Emails;
import fi.csc.microarray.util.SystemMonitorUtil;
import fi.csc.microarray.util.UrlTransferUtil;

/**
 * Monitoring database and tool for Chipster server system.
 * 
 * Using JdbcTemplate this way for inserts is really slow.
 * 
 * @author <NAME>
 */
public class Manager extends MonitoredNodeBase implements MessagingListener, ShutdownCallback {
	

	private class BackupTimerTask extends TimerTask {

		private BackupRotation backupRotation;
		
		public BackupTimerTask(File backupDir) {
			this.backupRotation = new BackupRotation(backupDir);
		}
				
		@Override
		public void run() {
			logger.info("Creating database backup");
			String fileName = backupRotation.getBackupFile(DateTime.now()).getPath();
			String sql = "SCRIPT TO '" + fileName + "' COMPRESSION ZIP";
			jdbcTemplate.execute(sql);
			
			int affectedRows = jdbcTemplate.update(REMOVE_OLD_TEST_JOBS);
			logger.info("cleaned up " + affectedRows + " old test jobs from database");
			
			int deletedFiles = backupRotation.rotate();
			logger.info("cleaned up " + deletedFiles + " old database backup files");
		}		
	}
	
	
	/**
	 * Loggers.
	 */
	private final Logger logger;

    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcInsert insertJobTemplate;
    private SimpleJdbcInsert insertAccountTemplate;
    private String feedbackEmail;

	private static final String CREATE_JOBS_TABLE = 
		"CREATE TABLE IF NOT EXISTS jobs (" +
		"id VARCHAR(100) PRIMARY KEY, " + 
		"operation VARCHAR(200), " +
		"status VARCHAR(200), " + 
		"starttime DATETIME DEFAULT NULL, " + 
		"endtime DATETIME DEFAULT NULL, " +
		"wallclockTime INT DEFAULT NULL, " +
		"errorMessage TEXT DEFAULT NULL, " +
		"outputText TEXT DEFAULT NULL, " + 
		"username VARCHAR(200), " +
		"compHost VARCHAR(500)" +
		");";
	
	private static final String CREATE_JOBS_INDEXES = 
			"create index if not exists INDEX_ADMIN_WEB_STARTTIMEDESC_ID on JOBS(STARTTIME DESC, ID);" + 
			"create index if not exists INDEX_ADMIN_WEB_USERNAME on JOBS(USERNAME);" + 
			"create index if not exists INDEX_ADMIN_WEB_OPERATION on JOBS(OPERATION);" + 
			"create index if not exists INDEX_ADMIN_WEB_STATUS on JOBS(STATUS);";
	
	/**
	 * Used in admin-web statistics to ignore test accounts 
	 */
	private static final String CREATE_ACCOUNTS_TABLE = 
			"CREATE TABLE IF NOT EXISTS accounts (" +
			"username VARCHAR(200) PRIMARY KEY, " +
			"ignoreInStatistics BOOLEAN DEFAULT FALSE" + 			
			");";
	
	private static final String REMOVE_OLD_TEST_JOBS = 
			"DELETE FROM JOBS " +
			"WHERE starttime < DATEADD('MONTH', -1, CURRENT_DATE()) " + // at least a month old<commit_msg>Create index for end time instead of start time<commit_after>package fi.csc.microarray.manager;

import it.sauronsoftware.cron4j.Scheduler;

import java.io.File;
import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.Arrays;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;
import java.util.Timer;
import java.util.TimerTask;

import javax.jms.JMSException;

import org.apache.log4j.Logger;
import org.eclipse.jetty.security.ConstraintMapping;
import org.eclipse.jetty.security.ConstraintSecurityHandler;
import org.eclipse.jetty.security.HashLoginService;
import org.eclipse.jetty.server.Connector;
import org.eclipse.jetty.server.Handler;
import org.eclipse.jetty.server.handler.DefaultHandler;
import org.eclipse.jetty.server.handler.HandlerCollection;
import org.eclipse.jetty.server.nio.SelectChannelConnector;
import org.eclipse.jetty.util.security.Constraint;
import org.eclipse.jetty.util.security.Password;
import org.eclipse.jetty.util.thread.QueuedThreadPool;
import org.eclipse.jetty.webapp.WebAppContext;
import org.h2.tools.Server;
import org.joda.time.DateTime;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import fi.csc.microarray.config.Configuration;
import fi.csc.microarray.config.DirectoryLayout;
import fi.csc.microarray.constants.ApplicationConstants;
import fi.csc.microarray.exception.MicroarrayException;
import fi.csc.microarray.messaging.JMSMessagingEndpoint;
import fi.csc.microarray.messaging.MessagingEndpoint;
import fi.csc.microarray.messaging.MessagingListener;
import fi.csc.microarray.messaging.MessagingTopic;
import fi.csc.microarray.messaging.MessagingTopic.AccessMode;
import fi.csc.microarray.messaging.MonitoredNodeBase;
import fi.csc.microarray.messaging.Topics;
import fi.csc.microarray.messaging.message.ChipsterMessage;
import fi.csc.microarray.messaging.message.FeedbackMessage;
import fi.csc.microarray.messaging.message.JobLogMessage;
import fi.csc.microarray.service.KeepAliveShutdownHandler;
import fi.csc.microarray.service.ShutdownCallback;
import fi.csc.microarray.util.Emails;
import fi.csc.microarray.util.SystemMonitorUtil;
import fi.csc.microarray.util.UrlTransferUtil;

/**
 * Monitoring database and tool for Chipster server system.
 * 
 * Using JdbcTemplate this way for inserts is really slow.
 * 
 * @author <NAME>
 */
public class Manager extends MonitoredNodeBase implements MessagingListener, ShutdownCallback {
	

	private class BackupTimerTask extends TimerTask {

		private BackupRotation backupRotation;
		
		public BackupTimerTask(File backupDir) {
			this.backupRotation = new BackupRotation(backupDir);
		}
				
		@Override
		public void run() {
			logger.info("Creating database backup");
			String fileName = backupRotation.getBackupFile(DateTime.now()).getPath();
			String sql = "SCRIPT TO '" + fileName + "' COMPRESSION ZIP";
			jdbcTemplate.execute(sql);
			
			int affectedRows = jdbcTemplate.update(REMOVE_OLD_TEST_JOBS);
			logger.info("cleaned up " + affectedRows + " old test jobs from database");
			
			int deletedFiles = backupRotation.rotate();
			logger.info("cleaned up " + deletedFiles + " old database backup files");
		}		
	}
	
	
	/**
	 * Loggers.
	 */
	private final Logger logger;

    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcInsert insertJobTemplate;
    private SimpleJdbcInsert insertAccountTemplate;
    private String feedbackEmail;

	private static final String CREATE_JOBS_TABLE = 
		"CREATE TABLE IF NOT EXISTS jobs (" +
		"id VARCHAR(100) PRIMARY KEY, " + 
		"operation VARCHAR(200), " +
		"status VARCHAR(200), " + 
		"starttime DATETIME DEFAULT NULL, " + 
		"endtime DATETIME DEFAULT NULL, " +
		"wallclockTime INT DEFAULT NULL, " +
		"errorMessage TEXT DEFAULT NULL, " +
		"outputText TEXT DEFAULT NULL, " + 
		"username VARCHAR(200), " +
		"compHost VARCHAR(500)" +
		");";
	
	private static final String CREATE_JOBS_INDEXES = 
			"create index if not exists INDEX_ADMIN_WEB_ENDTIMEDESC_ID on JOBS(ENDTIME DESC, ID);" + 
			"create index if not exists INDEX_ADMIN_WEB_USERNAME on JOBS(USERNAME);" + 
			"create index if not exists INDEX_ADMIN_WEB_OPERATION on JOBS(OPERATION);" + 
			"create index if not exists INDEX_ADMIN_WEB_STATUS on JOBS(STATUS);";
	
	/**
	 * Used in admin-web statistics to ignore test accounts 
	 */
	private static final String CREATE_ACCOUNTS_TABLE = 
			"CREATE TABLE IF NOT EXISTS accounts (" +
			"username VARCHAR(200) PRIMARY KEY, " +
			"ignoreInStatistics BOOLEAN DEFAULT FALSE" + 			
			");";
	
	private static final String REMOVE_OLD_TEST_JOBS = 
			"DELETE FROM JOBS " +
			"WHERE starttime < DATEADD('MONTH', -1, CURRENT_DATE()) " + // at least a month old
{'fraction_non_alphanumeric': 0.09842192691029901, 'fraction_numerical': 0.004568106312292359, 'mean_word_length': 5.001869158878504, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4038516', 'n_tokens_mistral': 3299, 'n_tokens_neox': 2946, 'n_words': 655}