package eu.dnetlib.functionality.alert.dao; import java.net.MalformedURLException; import java.net.URI; import java.net.URISyntaxException; import java.net.URL; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.SortedSet; import java.util.TreeSet; import javax.sql.DataSource; import org.apache.log4j.Logger; import org.springframework.jdbc.datasource.DataSourceUtils; import eu.dnetlib.domain.functionality.AlertSubscription; import eu.dnetlib.domain.functionality.AlertTemplate; /** * This bean implements alert DAO using an SQL database to persist templates and subscriptions through JDBC. * The SQL database must support foreign keys and should also support UTF-8. * @author thanos@di.uoa.gr * */ public class JDBCAlertDAO implements AlertDAO { private static final Logger logger = Logger.getLogger(JDBCAlertDAO.class); private static final String CREATE_TEMPLATE = "CREATE TABLE IF NOT EXISTS AlertTemplate (templateId VARCHAR (36) NOT NULL PRIMARY KEY, title TINYTEXT NOT NULL, message TEXT NOT NULL, link TEXT) DEFAULT CHARACTER SET " + "= 'UTF8', ENGINE = 'InnoDB';"; private static final String CREATE_SUBSCRIPTION = "CREATE TABLE IF NOT EXISTS AlertSubscription (templateId VARCHAR (36) NOT NULL, notificationService VARCHAR (255) NOT NULL, queryId VARCHAR (36) NOT NULL, resultId " + "VARCHAR (36) NOT NULL, alertMode VARCHAR (16) NOT NULL, subscriber VARCHAR (255) NOT NULL, enabled BOOLEAN NOT NULL DEFAULT FALSE, PRIMARY KEY (templateId, notificationService, queryId, resultId, alertMode, " + "subscriber), FOREIGN KEY (templateId) REFERENCES AlertTemplate (templateId) ON UPDATE CASCADE ON DELETE CASCADE) DEFAULT CHARACTER SET = 'UTF8', ENGINE = 'InnoDB';"; private static final String COUNT_TEMPLATES = "SELECT COUNT(*) FROM AlertTemplate;"; private static final String GET_TEMPLATES = "SELECT templateId, title, message, link FROM AlertTemplate ORDER BY templateId LIMIT ? OFFSET ?;"; private static final String GET_TEMPLATE = "SELECT title, message, link FROM AlertTemplate WHERE templateId = ?;"; private static final String SAVE_TEMPLATE = "INSERT INTO AlertTemplate (templateId, title, message, link) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE title = ?, message = ?, link = ?;"; private static final String DELETE_TEMPLATE = "DELETE FROM AlertTemplate WHERE templateId = ?;"; private static final String COUNT_SUBSCRIPTIONS = "SELECT COUNT(*) FROM AlertSubscription;"; private static final String GET_SUBSCRIPTIONS = "SELECT templateId, notificationService, queryId, resultId, alertMode, subscriber, enabled FROM AlertSubscription ORDER BY templateId, notificationService, queryId, " + "resultId, alertMode, subscriber LIMIT ? OFFSET ?;"; private static final String GET_SUBSCRIPTIONS_BY_ALERT_MODE_AND_SUBSCRIBER = "SELECT templateId, notificationService, queryId, resultId, alertMode, subscriber, enabled FROM AlertSubscription WHERE alertMode = ? AND " + "subscriber LIKE CONCAT(?, '%') ORDER BY templateId, notificationService, queryId, resultId, subscriber LIMIT ? OFFSET ?;"; private static final String GET_ENABLED_SUBSCRIPTIONS = "SELECT templateId, notificationService, queryId, resultId, alertMode, subscriber FROM AlertSubscription ORDER BY templateId, notificationService, queryId, " + "resultId, alertMode, subscriber LIMIT ? OFFSET ?;"; private static final String GET_ENABLED_SUBSCRIPTIONS_BY_NOTIFICATION_SERVICE_AND_QUERY_ID = "SELECT templateId, resultId, alertMode, subscriber FROM AlertSubscription WHERE notificationService = ? AND queryId = ? AND " + "enabled ORDER BY templateId, resultId, alertMode, subscriber LIMIT ? OFFSET ?;"; private static final String GET_SUBSCRIPTION = "SELECT enabled FROM AlertSubscription WHERE templateId = ? AND notificationService = ? AND queryId = ? AND resultId = ? AND alertMode = ? AND subscriber = ?;"; private static final String SAVE_SUBSCRIPTION = "INSERT INTO AlertSubscription(templateId, notificationService, queryId, resultId, alertMode, subscriber, enabled) VALUES (?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE " + "enabled = ?;"; private static final String DELETE_SUBSCRIPTION = "DELETE FROM AlertSubscription WHERE templateId = ? AND notificationService = ? AND queryId = ? AND resultId = ? AND alertMode = ? AND subscriber = ?;"; private DataSource dataSource; /** * Set the data source. * @param dataSource the data source of this DAO */ public void setDataSource(final DataSource dataSource) { this.dataSource = dataSource; } /** * Create necessary tables if they do not exist. * @throws AlertDAOException if any errors occur */ public void init() throws AlertDAOException { try { final Connection connection = DataSourceUtils.getConnection(dataSource); final Statement statement = connection.createStatement(); statement.executeUpdate(CREATE_TEMPLATE); statement.executeUpdate(CREATE_SUBSCRIPTION); statement.close(); DataSourceUtils.releaseConnection(connection, dataSource); logger.info("JDBC alert DAO initialization complete"); } catch (final SQLException e) { throw new AlertDAOException("error initializing JDBC alert DAO", e); } } @Override public int countTemplates() throws AlertDAOException { try { final Connection connection = DataSourceUtils.getConnection(dataSource); final PreparedStatement countTemplates = connection.prepareStatement(COUNT_TEMPLATES); final ResultSet resultSet = countTemplates.executeQuery(); final Integer templates = resultSet.next() ? resultSet.getInt(1) : null; countTemplates.close(); DataSourceUtils.releaseConnection(connection, dataSource); if (templates == null) throw new AlertDAOException("error counting templates"); else return templates; } catch (final SQLException e) { throw new AlertDAOException("error counting templates", e); } } @Override public SortedSet getTemplates(final int limit, final int offset) throws AlertDAOException { try { final Connection connection = DataSourceUtils.getConnection(dataSource); final PreparedStatement getTemplates = connection.prepareStatement(GET_TEMPLATES); getTemplates.setInt(1, limit); getTemplates.setInt(2, offset); final ResultSet resultSet = getTemplates.executeQuery(); final SortedSet templates = new TreeSet(); while (resultSet.next()) templates.add(new AlertTemplate(resultSet.getString(1), resultSet.getString(2), resultSet.getString(3), new URL(resultSet.getString(4)))); getTemplates.close(); DataSourceUtils.releaseConnection(connection, dataSource); return templates; } catch (final MalformedURLException e) { throw new AlertDAOException("error retrieving templates (limit: " + limit + ", offset: " + offset + ")"); } catch (final SQLException e) { throw new AlertDAOException("error retrieving templates (limit: " + limit + ", offset: " + offset + ")", e); } } @Override public AlertTemplate getTemplate(final String templateId) throws AlertDAOException { try { final Connection connection = DataSourceUtils.getConnection(dataSource); final PreparedStatement getTemplate = connection.prepareStatement(GET_TEMPLATE); getTemplate.setString(1, templateId); final ResultSet resultSet = getTemplate.executeQuery(); final AlertTemplate template = resultSet.next() ? new AlertTemplate(templateId, resultSet.getString(1), resultSet.getString(2), new URL(resultSet.getString(3))) : null; getTemplate.close(); DataSourceUtils.releaseConnection(connection, dataSource); return template; } catch (final MalformedURLException e) { throw new AlertDAOException("error retrieving template " + templateId, e); } catch (final SQLException e) { throw new AlertDAOException("error retrieving template " + templateId, e); } } @Override public void saveTemplate(final AlertTemplate template) throws AlertDAOException { try { final Connection connection = DataSourceUtils.getConnection(dataSource); final PreparedStatement saveTemplate = connection.prepareStatement(SAVE_TEMPLATE); saveTemplate.setString(1, template.getTemplateId()); saveTemplate.setString(2, template.getTitle()); saveTemplate.setString(3, template.getMessage()); saveTemplate.setString(4, template.getLink().toString()); saveTemplate.setString(5, template.getTitle()); saveTemplate.setString(6, template.getMessage()); saveTemplate.setString(7, template.getLink().toString()); saveTemplate.executeUpdate(); saveTemplate.close(); DataSourceUtils.releaseConnection(connection, dataSource); } catch (final SQLException e) { throw new AlertDAOException("error saving template " + template, e); } } @Override public void deleteTemplate(final String templateId) throws AlertDAOException { try { final Connection connection = DataSourceUtils.getConnection(dataSource); final PreparedStatement deleteTemplate = connection.prepareStatement(DELETE_TEMPLATE); deleteTemplate.setString(1, templateId); deleteTemplate.executeUpdate(); deleteTemplate.close(); DataSourceUtils.releaseConnection(connection, dataSource); } catch (final SQLException e) { throw new AlertDAOException("error deleting template " + templateId, e); } } @Override public int countSubscriptions() throws AlertDAOException { try { final Connection connection = DataSourceUtils.getConnection(dataSource); final PreparedStatement countSubscriptions = connection.prepareStatement(COUNT_SUBSCRIPTIONS); final ResultSet resultSet = countSubscriptions.executeQuery(); final Integer subscriptions = resultSet.next() ? resultSet.getInt(1) : null; countSubscriptions.close(); DataSourceUtils.releaseConnection(connection, dataSource); if (subscriptions == null) throw new AlertDAOException("error counting subscriptions"); else return subscriptions; } catch (final SQLException e) { throw new AlertDAOException("error counting subscriptions", e); } } @Override public SortedSet getSubscriptions(final int limit, final int offset) throws AlertDAOException { try { final Connection connection = DataSourceUtils.getConnection(dataSource); final PreparedStatement getSubscriptions = connection.prepareStatement(GET_SUBSCRIPTIONS); getSubscriptions.setInt(1, limit); getSubscriptions.setInt(2, offset); final ResultSet resultSet = getSubscriptions.executeQuery(); final SortedSet subscriptions = new TreeSet(); while (resultSet.next()) subscriptions.add(new AlertSubscription(resultSet.getString(1), new URL(resultSet.getString(2)), resultSet.getString(3), resultSet.getString(4), resultSet.getString(5), new URI(resultSet.getString(6)), resultSet.getBoolean(7))); getSubscriptions.close(); DataSourceUtils.releaseConnection(connection, dataSource); return subscriptions; } catch (final MalformedURLException e) { throw new AlertDAOException("error retrieving subscriptions (limit: " + limit + ", offset: " + offset + ")", e); } catch (final SQLException e) { throw new AlertDAOException("error retrieving subscriptions (limit: " + limit + ", offset: " + offset + ")", e); } catch (final URISyntaxException e) { throw new AlertDAOException("error retrieving subscriptions (limit: " + limit + ", offset: " + offset + ")", e); } } @Override public SortedSet getSubscriptions(final String alertMode, final String subscriber, final int limit, final int offset) throws AlertDAOException { try { final Connection connection = DataSourceUtils.getConnection(dataSource); final PreparedStatement getSubscriptions = connection.prepareStatement(GET_SUBSCRIPTIONS_BY_ALERT_MODE_AND_SUBSCRIBER); getSubscriptions.setString(1, alertMode); getSubscriptions.setString(2, subscriber); getSubscriptions.setInt(3, limit); getSubscriptions.setInt(4, offset); final ResultSet resultSet = getSubscriptions.executeQuery(); final SortedSet subscriptions = new TreeSet(); while (resultSet.next()) subscriptions.add(new AlertSubscription(resultSet.getString(1), new URL(resultSet.getString(2)), resultSet.getString(3), resultSet.getString(4), resultSet.getString(5), new URI(resultSet.getString(6)), resultSet.getBoolean(7))); getSubscriptions.close(); DataSourceUtils.releaseConnection(connection, dataSource); return subscriptions; } catch (final MalformedURLException e) { throw new AlertDAOException("error retrieving subscriptions with alert mode " + alertMode + " and subscriber like '" + subscriber + "' (limit: " + limit + ", offset: " + offset + ")", e); } catch (final SQLException e) { throw new AlertDAOException("error retrieving subscriptions with alert mode " + alertMode + " and subscriber like '" + subscriber + "' (limit: " + limit + ", offset: " + offset + ")", e); } catch (final URISyntaxException e) { throw new AlertDAOException("error retrieving subscriptions with alert mode " + alertMode + " and subscriber like '" + subscriber + "' (limit: " + limit + ", offset: " + offset + ")", e); } } @Override public SortedSet getEnabledSubscriptions(final int limit, final int offset) throws AlertDAOException { try { final Connection connection = DataSourceUtils.getConnection(dataSource); final PreparedStatement getEnabledSubscriptions = connection.prepareStatement(GET_ENABLED_SUBSCRIPTIONS); getEnabledSubscriptions.setInt(1, limit); getEnabledSubscriptions.setInt(2, offset); final ResultSet resultSet = getEnabledSubscriptions.executeQuery(); final SortedSet subscriptions = new TreeSet(); while (resultSet.next()) subscriptions.add(new AlertSubscription(resultSet.getString(1), new URL(resultSet.getString(2)), resultSet.getString(3), resultSet.getString(4), resultSet.getString(5), new URI(resultSet.getString(6)), true)); getEnabledSubscriptions.close(); DataSourceUtils.releaseConnection(connection, dataSource); return subscriptions; } catch (final MalformedURLException e) { throw new AlertDAOException("error retrieving enabled subscriptions", e); } catch (final SQLException e) { throw new AlertDAOException("error retrieving enabled subscriptions", e); } catch (final URISyntaxException e) { throw new AlertDAOException("error retrieving enabled subscriptions", e); } } @Override public SortedSet getEnabledSubscriptions(final URL notificationService, final String queryId, final int limit, final int offset) throws AlertDAOException { try { final Connection connection = DataSourceUtils.getConnection(dataSource); final PreparedStatement getEnabledSubscriptions = connection.prepareStatement(GET_ENABLED_SUBSCRIPTIONS_BY_NOTIFICATION_SERVICE_AND_QUERY_ID); getEnabledSubscriptions.setString(1, notificationService.toString()); getEnabledSubscriptions.setString(2, queryId); getEnabledSubscriptions.setInt(3, limit); getEnabledSubscriptions.setInt(4, offset); final ResultSet resultSet = getEnabledSubscriptions.executeQuery(); final SortedSet subscriptions = new TreeSet(); while (resultSet.next()) subscriptions.add(new AlertSubscription(resultSet.getString(1), notificationService, queryId, resultSet.getString(2), resultSet.getString(3), new URI(resultSet.getString(4)), true)); getEnabledSubscriptions.close(); DataSourceUtils.releaseConnection(connection, dataSource); return subscriptions; } catch (final SQLException e) { throw new AlertDAOException("error retrieving enabled subscriptions with notification service " + notificationService + " and query " + queryId, e); } catch (final URISyntaxException e) { throw new AlertDAOException("error retrieving enabled subscriptions with notification service " + notificationService + " and query " + queryId, e); } } @Override public AlertSubscription getSubscription(final String templateId, final URL notificationService, final String queryId, final String resultId, final String alertMode, final URI subscriber) throws AlertDAOException { try { final Connection connection = DataSourceUtils.getConnection(dataSource); final PreparedStatement getSubscription = connection.prepareStatement(GET_SUBSCRIPTION); getSubscription.setString(1, templateId); getSubscription.setString(2, notificationService.toString()); getSubscription.setString(3, queryId); getSubscription.setString(4, resultId); getSubscription.setString(5, alertMode); getSubscription.setString(6, subscriber.toString()); final ResultSet resultSet = getSubscription.executeQuery(); final AlertSubscription subscription = resultSet.next() ? new AlertSubscription(templateId, notificationService, queryId, resultId, alertMode, subscriber, resultSet.getBoolean(1)) : null; getSubscription.close(); DataSourceUtils.releaseConnection(connection, dataSource); return subscription; } catch (final SQLException e) { throw new AlertDAOException("error retrieving subscription (template: " + templateId + ", notification service: " + notificationService + ", query: " + queryId + ", result: " + resultId + ", alert mode: " + alertMode + ", subscriber: " + subscriber + ")", e); } } @Override public void saveSubscription(final AlertSubscription subscription) throws AlertDAOException { try { final Connection connection = DataSourceUtils.getConnection(dataSource); final PreparedStatement saveSubscription = connection.prepareStatement(SAVE_SUBSCRIPTION); saveSubscription.setString(1, subscription.getTemplateId()); saveSubscription.setString(2, subscription.getNotificationService().toString()); saveSubscription.setString(3, subscription.getQueryId()); saveSubscription.setString(4, subscription.getResultId()); saveSubscription.setString(5, subscription.getAlertMode()); saveSubscription.setString(6, subscription.getSubscriber().toString()); saveSubscription.setBoolean(7, subscription.isEnabled()); saveSubscription.setBoolean(8, subscription.isEnabled()); saveSubscription.executeUpdate(); saveSubscription.close(); DataSourceUtils.releaseConnection(connection, dataSource); } catch (final SQLException e) { throw new AlertDAOException("error saving subscription " + subscription, e); } } @Override public void deleteSubscription(final String templateId, final URL notificationService, final String queryId, final String resultId, final String alertMode, final URI subscriber) throws AlertDAOException { try { final Connection connection = DataSourceUtils.getConnection(dataSource); final PreparedStatement deleteSubscription = connection.prepareStatement(DELETE_SUBSCRIPTION); deleteSubscription.setString(1, templateId); deleteSubscription.setString(2, notificationService.toString()); deleteSubscription.setString(3, queryId); deleteSubscription.setString(4, resultId); deleteSubscription.setString(5, alertMode); deleteSubscription.setString(6, subscriber.toString()); deleteSubscription.executeUpdate(); deleteSubscription.close(); DataSourceUtils.releaseConnection(connection, dataSource); } catch (final SQLException e) { throw new AlertDAOException("error deleting subscription (template: " + templateId + ", notification service: " + notificationService + ", query: " + queryId + ", result: " + resultId + ", alert mode: " + alertMode + ", subscriber: " + subscriber + ")", e); } } }