JDBCNotificationIndex.java

  1. /*
  2.  * JDBCNotificationIndex
  3.  */
  4. package gov.usgs.earthquake.distribution;

  6. import gov.usgs.earthquake.product.ProductId;
  7. import gov.usgs.earthquake.util.JDBCConnection;
  8. import gov.usgs.util.Config;
  9. import gov.usgs.util.StreamUtils;

  11. import java.io.File;
  12. import java.net.MalformedURLException;
  13. import java.net.URL;
  14. import java.sql.Connection;
  15. import java.sql.DriverManager;
  16. import java.sql.PreparedStatement;
  17. import java.sql.ResultSet;
  18. import java.sql.SQLException;
  19. import java.util.ArrayList;
  20. import java.util.Date;
  21. import java.util.Iterator;
  22. import java.util.List;
  23. import java.util.logging.Logger;

  25. /**
  26.  * Stores and retrieves Notifications.
  27.  *
  28.  * This is typically used by a NotificationReceiver to track its Notifications,
  29.  * but may also be used by NotificationListeners. Each object should maintain a
  30.  * separate NotificationIndex.
  31.  *
  32.  * This implementation uses a SQLite Database as the index.
  33.  *
  34.  * @see gov.usgs.earthquake.distribution.NotificationIndex
  35.  */
  36. public class JDBCNotificationIndex extends JDBCConnection implements
  37.         NotificationIndex {

  39.     private static final Logger LOGGER = Logger
  40.             .getLogger(JDBCNotificationIndex.class.getName());

  42.     /**
  43.      * Default (empty) DB Schema SQLite file for the index. If the configured
  44.      * index file does not exist in the file system at the time this instance's
  45.      * "startup()" method is called, then this file is copied out of the JAR
  46.      * file into the file system as configured.
  47.      *
  48.      * The schema contained in this file is very simple:
  49.      *
  50.      * CREATE TABLE notification_index ( id INTEGER PRIMARY KEY NOT NULL,
  51.      * product_source TEXT NOT NULL, product_type TEXT NOT NULL, product_code
  52.      * TEXT NOT NULL, product_update LONG NOT NULL, expiration_date LONG NOT
  53.      * NULL, tracker_url TEXT NOT NULL, product_url TEXT );
  54.      *
  55.      * CREATE TABLE tmp_lookup_table ( product_source TEXT, product_type TEXT,
  56.      * product_code TEXT);
  57.      *
  58.      * CREATE INDEX expired_index on notification_index (expiration_date);
  59.      * CREATE INDEX id_index on notification_index (product_source,
  60.      * product_type, product_code, product_update);
  61.      *
  62.      * CREATE TABLE notification_queue ( id INTEGER PRIMARY KEY NOT NULL,
  63.      * queue_name TEXT NOT NULL, product_source TEXT NOT NULL, product_type TEXT
  64.      * NOT NULL, product_code TEXT NOT NULL, product_update LONG NOT NULL );
  65.      *
  66.      * CREATE INDEX queue_index on notification_queue (queue_name,
  67.      * product_source, product_type, product_code, product_update);
  68.      *
  69.      */
  70.     private static final String JDBC_DB_SCHEMA = "etc/schema/notificationIndex.db";

  72.     // The following variables reference database information and are used for
  73.     // binding/fetching query parameters in the prepared statements
  74.     private static final String TABLE_NAME = "notification_index";
  75.     private static final String TMP_TABLE = "tmp_lookup_table";
  76.     private static final String ID_COLUMN = "id";
  77.     private static final String PRODUCT_SOURCE_COLUMN = "product_source";
  78.     private static final String PRODUCT_TYPE_COLUMN = "product_type";
  79.     private static final String PRODUCT_CODE_COLUMN = "product_code";
  80.     private static final String PRODUCT_UPDATE_COLUMN = "product_update";
  81.     private static final String EXPIRATION_DATE_COLUMN = "expiration_date";
  82.     private static final String TRACKER_URL_COLUMN = "tracker_url";
  83.     private static final String PRODUCT_URL_COLUMN = "product_url";

  85.     // SQLite driver information
  86.     /** SQLite driver class name. */
  87.     private static final String JDBC_DRIVER_CLASS = "org.sqlite.JDBC";
  88.     /** SQLite connect url without a filename. */
  89.     private static final String JDBC_CONNECT_URL = "jdbc:sqlite:";
  90.     /** Default SQLite database filename. */
  91.     private static final String JDBC_DEFAULT_FILE = "pd_index.db";

  93.     /**
  94.      * This is the property key used in the configuration file to specify a
  95.      * different SQLite database file. If this file doesn't exist it will be
  96.      * created at startup time
  97.      */
  98.     protected static final String JDBC_FILE_PROPERTY = "indexfile";

  100.     /** SQL stub for adding a notification to the index. */
  101.     private static final String DML_ADD_NOTIFICATION = String.format(
  102.             "INSERT INTO %s (%s, %s, %s, %s, %s, %s, %s) VALUES "
  103.                     + "(?, ?, ?, ?, ?, ?, ?)", TABLE_NAME,
  104.             PRODUCT_SOURCE_COLUMN, PRODUCT_TYPE_COLUMN, PRODUCT_CODE_COLUMN,
  105.             PRODUCT_UPDATE_COLUMN, EXPIRATION_DATE_COLUMN, TRACKER_URL_COLUMN,
  106.             PRODUCT_URL_COLUMN);

  108.     /** SQL stub for removing a notification from the index. */
  109.     private static final String DML_REMOVE_NOTIFICATION = String.format(
  110.             "DELETE FROM %s WHERE %s = ? AND %s = ? AND %s = ? "
  111.                     + "AND %s = ? AND %s = ? AND %s = ? AND %s = ?",
  112.             TABLE_NAME, PRODUCT_SOURCE_COLUMN, PRODUCT_TYPE_COLUMN,
  113.             PRODUCT_CODE_COLUMN, PRODUCT_UPDATE_COLUMN, EXPIRATION_DATE_COLUMN,
  114.             TRACKER_URL_COLUMN, PRODUCT_URL_COLUMN);

  116.     /** SQL stub for finding expired notifications. */
  117.     private static final String QUERY_FIND_EXPIRED_NOTIFICATIONS = String
  118.             .format("SELECT %s, %s, %s, %s, %s, %s, %s, %s FROM %s "
  119.                     + "WHERE %s <= ?", ID_COLUMN, PRODUCT_SOURCE_COLUMN,
  120.                     PRODUCT_TYPE_COLUMN, PRODUCT_CODE_COLUMN,
  121.                     PRODUCT_UPDATE_COLUMN, EXPIRATION_DATE_COLUMN,
  122.                     TRACKER_URL_COLUMN, PRODUCT_URL_COLUMN, TABLE_NAME,
  123.                     EXPIRATION_DATE_COLUMN);

  125.     /** SQL stub for finding notifications about a particular productId */
  126.     private static final String QUERY_FIND_NOTIFICATIONS_BY_ID = String.format(
  127.             "SELECT %s, %s, %s, %s, %s, %s, %s, %s FROM %s "
  128.                     + "WHERE %s = ? AND %s = ? AND %s = ? AND %s = ?",
  129.             ID_COLUMN, PRODUCT_SOURCE_COLUMN, PRODUCT_TYPE_COLUMN,
  130.             PRODUCT_CODE_COLUMN, PRODUCT_UPDATE_COLUMN, EXPIRATION_DATE_COLUMN,
  131.             TRACKER_URL_COLUMN, PRODUCT_URL_COLUMN, TABLE_NAME,
  132.             PRODUCT_SOURCE_COLUMN, PRODUCT_TYPE_COLUMN, PRODUCT_CODE_COLUMN,
  133.             PRODUCT_UPDATE_COLUMN);

  135.     /**
  136.      * SQL stub for finding notifications about products based on discrete data.
  137.      */
  138.     private static final String QUERY_FIND_NOTIFICATIONS_BY_DATA = String
  139.             .format("SELECT %s, %s, %s, %s, %s, %s, %s FROM %s WHERE "
  140.                     + "UPPER(%s) LIKE ? AND UPPER(%s) LIKE ? AND "
  141.                     + "UPPER(%s) LIKE ?", PRODUCT_SOURCE_COLUMN,
  142.                     PRODUCT_TYPE_COLUMN, PRODUCT_CODE_COLUMN,
  143.                     PRODUCT_UPDATE_COLUMN, EXPIRATION_DATE_COLUMN,
  144.                     TRACKER_URL_COLUMN, PRODUCT_URL_COLUMN, TABLE_NAME,
  145.                     PRODUCT_SOURCE_COLUMN, PRODUCT_TYPE_COLUMN,
  146.                     PRODUCT_CODE_COLUMN);

  148.     /** Name of the SQLite DB file to use. This is configurable. */
  149.     private String _jdbc_index_file = null;

  151.     /**
  152.      * Connection URL. Created from the JDBC_CONNET_URL and configured index
  153.      * file.
  154.      */
  155.     private String _jdbc_connect_url = null;

  157.     // These are the prepared statements we will use for all DB interactions. //

  159.     private PreparedStatement _dml_addNotification = null;
  160.     private PreparedStatement _dml_removeNotification = null;

  162.     private PreparedStatement _query_findExpiredNotifications = null;
  163.     private PreparedStatement _query_findNotificationsById = null;
  164.     private PreparedStatement _query_findNotificationsByData = null;

  166.     // Stubs used in the list version of find by data method. //
  167.     private static final String DML_CREATE_TMP_TABLE = String.format(
  168.             "CREATE TABLE IF NOT EXISTS %s (%s TEXT, %s TEXT, %s TEXT)",
  169.             TMP_TABLE, PRODUCT_SOURCE_COLUMN, PRODUCT_TYPE_COLUMN,
  170.             PRODUCT_CODE_COLUMN);
  171.     private static final String DML_ADD_TMP_SOURCE = String.format(
  172.             "INSERT INTO %s (%s) VALUES (?)", TMP_TABLE, PRODUCT_SOURCE_COLUMN);
  173.     private static final String DML_ADD_TMP_TYPE = String.format(
  174.             "INSERT INTO %s (%s) VALUES (?)", TMP_TABLE, PRODUCT_TYPE_COLUMN);
  175.     private static final String DML_ADD_TMP_CODE = String.format(
  176.             "INSERT INTO %s (%s) VALUES (?)", TMP_TABLE, PRODUCT_CODE_COLUMN);

  178.     private static final String QUERY_SEARCH_BY_SOURCE_TYPE_CODE = String
  179.             .format("SELECT %s, %s, %s, %s, %s, %s, %s FROM %s WHERE %s IN "
  180.                     + "(SELECT %s FROM %s) AND %s IN (SELECT %s FROM %s) AND "
  181.                     + "%s IN (SELECT %s FROM %s)", PRODUCT_SOURCE_COLUMN,
  182.                     PRODUCT_TYPE_COLUMN, PRODUCT_CODE_COLUMN,
  183.                     PRODUCT_UPDATE_COLUMN, EXPIRATION_DATE_COLUMN,
  184.                     TRACKER_URL_COLUMN, PRODUCT_URL_COLUMN, TABLE_NAME,
  185.                     PRODUCT_SOURCE_COLUMN, PRODUCT_SOURCE_COLUMN, TMP_TABLE,
  186.                     PRODUCT_TYPE_COLUMN, PRODUCT_TYPE_COLUMN, TMP_TABLE,
  187.                     PRODUCT_CODE_COLUMN, PRODUCT_CODE_COLUMN, TMP_TABLE);

  189.     private static final String QUERY_SEARCH_BY_SOURCE_TYPE = String.format(
  190.             "SELECT %s, %s, %s, %s, %s, %s, %s FROM %s WHERE %s IN "
  191.                     + "(SELECT %s FROM %s) AND %s IN (SELECT %s FROM %s)",
  192.             PRODUCT_SOURCE_COLUMN, PRODUCT_TYPE_COLUMN, PRODUCT_CODE_COLUMN,
  193.             PRODUCT_UPDATE_COLUMN, EXPIRATION_DATE_COLUMN, TRACKER_URL_COLUMN,
  194.             PRODUCT_URL_COLUMN, TABLE_NAME, PRODUCT_SOURCE_COLUMN,
  195.             PRODUCT_SOURCE_COLUMN, TMP_TABLE, PRODUCT_TYPE_COLUMN,
  196.             PRODUCT_TYPE_COLUMN, TMP_TABLE);

  198.     private static final String QUERY_SEARCH_BY_SOURCE_CODE = String.format(
  199.             "SELECT %s, %s, %s, %s, %s, %s, %s FROM %s WHERE %s IN "
  200.                     + "(SELECT %s FROM %s) AND %s IN (SELECT %s FROM %s)",
  201.             PRODUCT_SOURCE_COLUMN, PRODUCT_TYPE_COLUMN, PRODUCT_CODE_COLUMN,
  202.             PRODUCT_UPDATE_COLUMN, EXPIRATION_DATE_COLUMN, TRACKER_URL_COLUMN,
  203.             PRODUCT_URL_COLUMN, TABLE_NAME, PRODUCT_SOURCE_COLUMN,
  204.             PRODUCT_SOURCE_COLUMN, TMP_TABLE, PRODUCT_CODE_COLUMN,
  205.             PRODUCT_CODE_COLUMN, TMP_TABLE);

  207.     private static final String QUERY_SEARCH_BY_TYPE_CODE = String.format(
  208.             "SELECT %s, %s, %s, %s, %s, %s, %s FROM %s WHERE %s IN "
  209.                     + "(SELECT %s FROM %s) AND %s IN (SELECT %s FROM %s)",
  210.             PRODUCT_SOURCE_COLUMN, PRODUCT_TYPE_COLUMN, PRODUCT_CODE_COLUMN,
  211.             PRODUCT_UPDATE_COLUMN, EXPIRATION_DATE_COLUMN, TRACKER_URL_COLUMN,
  212.             PRODUCT_URL_COLUMN, TABLE_NAME, PRODUCT_TYPE_COLUMN,
  213.             PRODUCT_TYPE_COLUMN, TMP_TABLE, PRODUCT_CODE_COLUMN,
  214.             PRODUCT_CODE_COLUMN, TMP_TABLE);

  216.     private static final String QUERY_SEARCH_BY_SOURCE = String
  217.             .format("SELECT %s, %s, %s, %s, %s, %s, %s FROM %s WHERE %s IN "
  218.                     + "(SELECT %s FROM %s)", PRODUCT_SOURCE_COLUMN,
  219.                     PRODUCT_TYPE_COLUMN, PRODUCT_CODE_COLUMN,
  220.                     PRODUCT_UPDATE_COLUMN, EXPIRATION_DATE_COLUMN,
  221.                     TRACKER_URL_COLUMN, PRODUCT_URL_COLUMN, TABLE_NAME,
  222.                     PRODUCT_SOURCE_COLUMN, PRODUCT_SOURCE_COLUMN, TMP_TABLE);

  224.     private static final String QUERY_SEARCH_BY_TYPE = String.format(
  225.             "SELECT %s, %s, %s, %s, %s, %s, %s FROM %s WHERE %s IN "
  226.                     + "(SELECT %s FROM %s)", PRODUCT_SOURCE_COLUMN,
  227.             PRODUCT_TYPE_COLUMN, PRODUCT_CODE_COLUMN, PRODUCT_UPDATE_COLUMN,
  228.             EXPIRATION_DATE_COLUMN, TRACKER_URL_COLUMN, PRODUCT_URL_COLUMN,
  229.             TABLE_NAME, PRODUCT_TYPE_COLUMN, PRODUCT_TYPE_COLUMN, TMP_TABLE);

  231.     private static final String QUERY_SEARCH_BY_CODE = String.format(
  232.             "SELECT %s, %s, %s, %s, %s, %s, %s FROM %s WHERE %s IN "
  233.                     + "(SELECT %s FROM %s)", PRODUCT_SOURCE_COLUMN,
  234.             PRODUCT_TYPE_COLUMN, PRODUCT_CODE_COLUMN, PRODUCT_UPDATE_COLUMN,
  235.             EXPIRATION_DATE_COLUMN, TRACKER_URL_COLUMN, PRODUCT_URL_COLUMN,
  236.             TABLE_NAME, PRODUCT_CODE_COLUMN, PRODUCT_CODE_COLUMN, TMP_TABLE);
  237.     /*
  238.      * private static final String QUERY_ALL_NOTIFICATIONS = String.format(
  239.      * "SELECT %s, %s, %s, %s, %s, %s, %s FROM %s", PRODUCT_SOURCE_COLUMN,
  240.      * PRODUCT_TYPE_COLUMN, PRODUCT_CODE_COLUMN, PRODUCT_UPDATE_COLUMN,
  241.      * EXPIRATION_DATE_COLUMN, TRACKER_URL_COLUMN, PRODUCT_URL_COLUMN,
  242.      * TABLE_NAME);
  243.      */

  245.     private static final String QUERY_LATEST_NOTIFICATIONS = String.format(
  246.             "SELECT n.%s, n.%s, n.%s, n.%s, n.%s, n.%s, n.%s FROM %s n, "
  247.                     + "(select max(id) as id, product_source, product_type, "
  248.                     + "product_code, product_update from notification_index "
  249.                     + "group by product_source, product_type, product_code, "
  250.                     + "product_update) latest where n.id=latest.id "
  251.                     + " order by n.product_update asc",
  252.             PRODUCT_SOURCE_COLUMN, PRODUCT_TYPE_COLUMN, PRODUCT_CODE_COLUMN,
  253.             PRODUCT_UPDATE_COLUMN, EXPIRATION_DATE_COLUMN, TRACKER_URL_COLUMN,
  254.             PRODUCT_URL_COLUMN, TABLE_NAME);

  256.     // These are for searching the DB index by specific parameters. */
  257.     private PreparedStatement _dml_createTmpTable = null;
  258.     private PreparedStatement _dml_addTmpSource = null;
  259.     private PreparedStatement _dml_addTmpType = null;
  260.     private PreparedStatement _dml_addTmpCode = null;

  262.     private PreparedStatement _query_searchBySourceTypeCode = null;
  263.     private PreparedStatement _query_searchBySourceType = null;
  264.     private PreparedStatement _query_searchBySourceCode = null;
  265.     private PreparedStatement _query_searchByTypeCode = null;
  266.     private PreparedStatement _query_searchBySource = null;
  267.     private PreparedStatement _query_searchByType = null;
  268.     private PreparedStatement _query_searchByCode = null;
  269.     private PreparedStatement _query_getAllNotifications = null;

  271.     /**
  272.      * Default, no-arg constructor. This just ensures the JDBC SQLite driver is
  273.      * appropriately on the classpath for proper runtime execution. This
  274.      * probably will not get called directly in favor of the configurable
  275.      * constructor.
  276.      *
  277.      * @throws Exception
  278.      *             If the JDBC driver class is not found.
  279.      * @see #JDBC_DRIVER_CLASS
  280.      */
  281.     public JDBCNotificationIndex() throws Exception {
  282.         this((String) null);
  283.     }

  285.     /**
  286.      * Constructor call from filename, where filename is jdbc index file
  287.      * If null, then index file defaults
  288.      * @param filename String - What will be the index file
  289.      * @throws Exception if error occurs
  290.      */
  291.     public JDBCNotificationIndex(final String filename) throws Exception {
  292.         Class.forName(JDBC_DRIVER_CLASS);
  293.         _jdbc_index_file = filename;
  294.         if (_jdbc_index_file == null) {
  295.             _jdbc_index_file = JDBC_DEFAULT_FILE;
  296.         }
  297.         _jdbc_connect_url = JDBC_CONNECT_URL + _jdbc_index_file;
  298.     }

  300.     /**
  301.      * Constructor called from the config object conforming to the
  302.      * <code>Configurable</code> interface specification. This internally calls
  303.      * its no-arg constructor then configures itself.
  304.      *
  305.      * @param config
  306.      *            The config object from which this instance will be configured.
  307.      * @throws Exception
  308.      *             If the JDBC driver class is not found.
  309.      * @see gov.usgs.util.Configurable
  310.      * @see #JDBC_DRIVER_CLASS
  311.      */
  312.     public JDBCNotificationIndex(Config config) throws Exception {
  313.         this();
  314.         this.configure(config);
  315.     }

  317.     /**
  318.      * Reads the given <code>config</code> object and sets values appropriately.
  319.      *
  320.      * @param config
  321.      *            The config object from which this instance will be configured.
  322.      * @see gov.usgs.util.Configurable
  323.      */
  324.     public void configure(Config config) throws Exception {
  325.         _jdbc_index_file = config.getProperty(JDBC_FILE_PROPERTY);
  326.         if (_jdbc_index_file == null || "".equals(_jdbc_index_file)) {
  327.             _jdbc_index_file = JDBC_DEFAULT_FILE;
  328.         }
  329.         LOGGER.config("Notification index database is '" + _jdbc_index_file
  330.                 + "'");
  331.         _jdbc_connect_url = JDBC_CONNECT_URL + _jdbc_index_file;
  332.     }

  334.     @Override
  335.     protected Connection connect() throws Exception {
  336.         // Make sure file exists or copy it out of the JAR
  337.         File indexFile = new File(_jdbc_index_file);
  338.         if (!indexFile.exists()) {
  339.             // extract schema from jar
  340.             URL schemaURL = JDBCNotificationIndex.class.getClassLoader()
  341.                     .getResource(JDBC_DB_SCHEMA);
  342.             if (schemaURL == null) {
  343.                 schemaURL = new File(JDBC_DB_SCHEMA).toURI().toURL();
  344.             }
  345.             StreamUtils.transferStream(schemaURL, indexFile);
  346.         }

  348.         return DriverManager.getConnection(_jdbc_connect_url);
  349.     }

  351.     /**
  352.      * Connects to the JDBC DB index and prepares the DML/Query statements that
  353.      * will execute at runtime. If the JDBC DB index file does not exist then an
  354.      * empty schema will be copied out of the executing JAR file to be used.
  355.      *
  356.      * @see gov.usgs.util.Configurable
  357.      */
  358.     public void startup() throws Exception {
  359.         // call super startup to connect
  360.         super.startup();
  361.         Connection conn = getConnection();

  363.         // prepare statements
  364.         _dml_addNotification = conn.prepareStatement(DML_ADD_NOTIFICATION);
  365.         _dml_removeNotification = conn
  366.                 .prepareStatement(DML_REMOVE_NOTIFICATION);

  368.         _query_findExpiredNotifications = conn
  369.                 .prepareStatement(QUERY_FIND_EXPIRED_NOTIFICATIONS);
  370.         _query_findNotificationsById = conn
  371.                 .prepareStatement(QUERY_FIND_NOTIFICATIONS_BY_ID);
  372.         _query_findNotificationsByData = conn
  373.                 .prepareStatement(QUERY_FIND_NOTIFICATIONS_BY_DATA);

  375.         _dml_createTmpTable = conn.prepareStatement(DML_CREATE_TMP_TABLE);
  376.         _dml_addTmpSource = conn.prepareStatement(DML_ADD_TMP_SOURCE);
  377.         _dml_addTmpType = conn.prepareStatement(DML_ADD_TMP_TYPE);
  378.         _dml_addTmpCode = conn.prepareStatement(DML_ADD_TMP_CODE);

  380.         _query_searchBySourceTypeCode = conn
  381.                 .prepareStatement(QUERY_SEARCH_BY_SOURCE_TYPE_CODE);
  382.         _query_searchBySourceType = conn
  383.                 .prepareStatement(QUERY_SEARCH_BY_SOURCE_TYPE);
  384.         _query_searchBySourceCode = conn
  385.                 .prepareStatement(QUERY_SEARCH_BY_SOURCE_CODE);
  386.         _query_searchByTypeCode = conn
  387.                 .prepareStatement(QUERY_SEARCH_BY_TYPE_CODE);
  388.         _query_searchBySource = conn.prepareStatement(QUERY_SEARCH_BY_SOURCE);
  389.         _query_searchByType = conn.prepareStatement(QUERY_SEARCH_BY_TYPE);
  390.         _query_searchByCode = conn.prepareStatement(QUERY_SEARCH_BY_CODE);
  391.         _query_getAllNotifications = conn
  392.                 .prepareStatement(QUERY_LATEST_NOTIFICATIONS);

  394.     }

  396.     /**
  397.      * Closes the JDBC connection and all it's associated prepared statements.
  398.      *
  399.      * @see gov.usgs.util.Configurable
  400.      */
  401.     public synchronized void shutdown() throws Exception {
  402.         // Close the DML statements
  403.         try {
  404.             _dml_addNotification.close();
  405.         } catch (Exception e) {
  406.         } finally {
  407.             _dml_addNotification = null;
  408.         }
  409.         try {
  410.             _dml_removeNotification.close();
  411.         } catch (Exception e) {
  412.         } finally {
  413.             _dml_removeNotification = null;
  414.         }
  415.         try {
  416.             _dml_createTmpTable.close();
  417.         } catch (Exception e) {
  418.         } finally {
  419.             _dml_createTmpTable = null;
  420.         }
  421.         try {
  422.             _dml_addTmpSource.close();
  423.         } catch (Exception e) {
  424.         } finally {
  425.             _dml_addTmpSource = null;
  426.         }
  427.         try {
  428.             _dml_addTmpType.close();
  429.         } catch (Exception e) {
  430.         } finally {
  431.             _dml_addTmpType = null;
  432.         }
  433.         try {
  434.             _dml_addTmpCode.close();
  435.         } catch (Exception e) {
  436.         } finally {
  437.             _dml_addTmpCode = null;
  438.         }

  440.         // Close the query statements
  441.         try {
  442.             _query_findExpiredNotifications.close();
  443.         } catch (Exception e) {
  444.         } finally {
  445.             _query_findExpiredNotifications = null;
  446.         }
  447.         try {
  448.             _query_findNotificationsById.close();
  449.         } catch (Exception e) {
  450.         } finally {
  451.             _query_findNotificationsById = null;
  452.         }
  453.         try {
  454.             _query_findNotificationsByData.close();
  455.         } catch (Exception e) {
  456.         } finally {
  457.             _query_findNotificationsByData = null;
  458.         }
  459.         try {
  460.             _query_searchBySourceTypeCode.close();
  461.         } catch (Exception e) {
  462.         } finally {
  463.             _query_searchBySourceTypeCode = null;
  464.         }
  465.         try {
  466.             _query_searchBySourceType.close();
  467.         } catch (Exception e) {
  468.         } finally {
  469.             _query_searchBySourceType = null;
  470.         }
  471.         try {
  472.             _query_searchBySourceCode.close();
  473.         } catch (Exception e) {
  474.         } finally {
  475.             _query_searchBySourceCode = null;
  476.         }
  477.         try {
  478.             _query_searchByTypeCode.close();
  479.         } catch (Exception e) {
  480.         } finally {
  481.             _query_searchByTypeCode = null;
  482.         }
  483.         try {
  484.             _query_searchBySource.close();
  485.         } catch (Exception e) {
  486.         } finally {
  487.             _query_searchBySource = null;
  488.         }
  489.         try {
  490.             _query_searchByType.close();
  491.         } catch (Exception e) {
  492.         } finally {
  493.             _query_searchByType = null;
  494.         }
  495.         try {
  496.             _query_searchByCode.close();
  497.         } catch (Exception e) {
  498.         } finally {
  499.             _query_searchByCode = null;
  500.         }
  501.         try {
  502.             _query_getAllNotifications.close();
  503.         } catch (Exception e) {
  504.         } finally {
  505.             _query_getAllNotifications = null;
  506.         }

  508.         // call super shutdown to disconnect
  509.         super.shutdown();
  510.     }

  512.     /**
  513.      * Add a notification to the index.
  514.      *
  515.      * If an identical notification is already in the index, the implementation
  516.      * may choose whether or not to store the duplicate information.
  517.      *
  518.      * @param notification
  519.      *            the notification to add.
  520.      * @throws Exception
  521.      *             if an error occurs while storing the notification.
  522.      * @see gov.usgs.earthquake.distribution.NotificationIndex
  523.      */
  524.     public synchronized void addNotification(Notification notification)
  525.             throws Exception {
  526.         // verify connection
  527.         this.verifyConnection();

  529.         // Read the product id from the notification
  530.         ProductId productId = notification.getProductId();

  532.         // Parse the update date from the product id
  533.         java.sql.Date updateDate = new java.sql.Date(productId.getUpdateTime()
  534.                 .getTime());

  536.         // Parse the expiration date from the notification
  537.         java.sql.Date expirationDate = new java.sql.Date(notification
  538.                 .getExpirationDate().getTime());

  540.         // Read the URL value from the notification
  541.         String trackerUrl = notification.getTrackerURL() != null
  542.                 ? notification.getTrackerURL().toString()
  543.                 : "";

  545.         // Set the values we parsed above
  546.         _dml_addNotification.setString(1, productId.getSource());
  547.         _dml_addNotification.setString(2, productId.getType());
  548.         _dml_addNotification.setString(3, productId.getCode());
  549.         _dml_addNotification.setDate(4, updateDate);
  550.         _dml_addNotification.setDate(5, expirationDate);
  551.         _dml_addNotification.setString(6, trackerUrl);

  553.         // If this is a URL notification, set the product URL value as well
  554.         if (notification instanceof URLNotification) {
  555.             String productUrl = ((URLNotification) notification)
  556.                     .getProductURL().toString();
  557.             _dml_addNotification.setString(7, productUrl);
  558.         } else {
  559.             _dml_addNotification.setString(7, "");
  560.         }

  562.         // already verified above
  563.         Connection conn = getConnection();
  564.         try {
  565.             // Begin a transaction
  566.             conn.setAutoCommit(false);
  567.             // Execute the query
  568.             _dml_addNotification.executeUpdate();
  569.             // Commit the changes
  570.             conn.setAutoCommit(true);
  571.         } catch (SQLException sqx) {
  572.             // Undo any changes that may be in an unknown state. Ignore
  573.             // exceptions that occur in this call since we're already throwing
  574.             // an exception
  575.             try {
  576.                 conn.rollback();
  577.             } catch (SQLException ex) {
  578.             }

  580.             // Re-throw this exception
  581.             throw sqx;
  582.         } finally {
  583.             conn.setAutoCommit(true);
  584.         }
  585.     }

  587.     /**
  588.      * Remove a notification from the index.
  589.      *
  590.      * All matching notifications should be removed from the index.
  591.      *
  592.      * @param notification
  593.      *            the notification to remove.
  594.      * @throws Exception
  595.      *             if an error occurs while removing the notification.
  596.      * @see gov.usgs.earthquake.distribution.NotificationIndex
  597.      */
  598.     public synchronized void removeNotification(Notification notification)
  599.             throws Exception {
  600.         // verify connection
  601.         this.verifyConnection();

  603.         // Read the product id from the notification
  604.         ProductId productId = notification.getProductId();
  605.         // Parse the update date from the product id
  606.         java.sql.Date updateDate = new java.sql.Date(productId.getUpdateTime()
  607.                 .getTime());
  608.         // Parse the expiration date from the notification
  609.         java.sql.Date expirationDate = new java.sql.Date(notification
  610.                 .getExpirationDate().getTime());
  611.         // Read the URL value from the notification
  612.         String trackerUrl = notification.getTrackerURL() != null
  613.                 ? notification.getTrackerURL().toString()
  614.                 : "";

  616.         // Set the values we parsed above
  617.         _dml_removeNotification.setString(1, productId.getSource());
  618.         _dml_removeNotification.setString(2, productId.getType());
  619.         _dml_removeNotification.setString(3, productId.getCode());
  620.         _dml_removeNotification.setDate(4, updateDate);
  621.         _dml_removeNotification.setDate(5, expirationDate);
  622.         _dml_removeNotification.setString(6, trackerUrl);

  624.         // If this is a URL notification, set the product URL value as well
  625.         if (notification instanceof URLNotification) {
  626.             String productUrl = ((URLNotification) notification)
  627.                     .getProductURL().toString();
  628.             _dml_removeNotification.setString(7, productUrl);
  629.         } else {
  630.             // _dml_removeNotification.setNull(7, java.sql.Types.VARCHAR);
  631.             _dml_removeNotification.setString(7, "");
  632.         }

  634.         // already verified above
  635.         Connection conn = getConnection();
  636.         try {
  637.             // Begin a transaction
  638.             conn.setAutoCommit(false);
  639.             // Execute the query
  640.             _dml_removeNotification.executeUpdate();
  641.             // Commit the changes
  642.             conn.setAutoCommit(true);
  643.         } catch (SQLException sqx) {
  644.             // Undo any changes that may be in an unknown state. Ignore
  645.             // exceptions that occur in this call since we're already throwing
  646.             // an exception
  647.             try {
  648.                 conn.rollback();
  649.             } catch (SQLException ex) {
  650.             }
  651.             // Re-throw this exception
  652.             throw sqx;
  653.         } finally {
  654.             conn.setAutoCommit(true);
  655.         }
  656.     }

  658.     /**
  659.      * Remove notifications from the index.
  660.      *
  661.      * All matching notifications should be removed from the index.
  662.      *
  663.      * @param notifications
  664.      *            the notifications to remove.
  665.      * @throws Exception
  666.      *             if an error occurs while removing the notifications.
  667.      * @see gov.usgs.earthquake.distribution.NotificationIndex
  668.      */
  669.     public synchronized void removeNotifications(List<Notification> notifications)
  670.             throws Exception {
  671.         for (Notification notification : notifications) {
  672.             this.removeNotification(notification);
  673.         }
  674.     }

  676.     /**
  677.      * Search the index for notifications matching id.
  678.      *
  679.      * If more than one notification matches, all should be returned.
  680.      *
  681.      * @param id
  682.      *            the ProductId to find.
  683.      * @return a list of matching notifications.
  684.      * @throws Exception
  685.      *             if an error occurs while searching the index.
  686.      * @see gov.usgs.earthquake.distribution.NotificationIndex
  687.      */
  688.     public synchronized List<Notification> findNotifications(ProductId id)
  689.             throws Exception {
  690.         // verify connection
  691.         this.verifyConnection();

  693.         String source = id.getSource();
  694.         String type = id.getType();
  695.         String code = id.getCode();
  696.         java.sql.Date update = new java.sql.Date(id.getUpdateTime().getTime());

  698.         _query_findNotificationsById.setString(1, source);
  699.         _query_findNotificationsById.setString(2, type);
  700.         _query_findNotificationsById.setString(3, code);
  701.         _query_findNotificationsById.setDate(4, update);

  703.         return getNotifications(_query_findNotificationsById);
  704.     }

  706.     /**
  707.      * Search the index for notifications matching the sources, types, and
  708.      * codes.
  709.      *
  710.      * Only one notification for each unique ProductId
  711.      * (source+type+code+updateTime) should be returned. If sources, types,
  712.      * and/or codes are null, that parameter should be considered a wildcard. If
  713.      * sources, types, and codes are all null, a notification for each unique
  714.      * ProductId in the index should be returned.
  715.      *
  716.      * @param source
  717.      *            sources to include, or all if null.
  718.      * @param type
  719.      *            types to include, or all if null.
  720.      * @param code
  721.      *            codes to include, or all if null.
  722.      * @return a list of matching notifications.
  723.      * @throws Exception
  724.      *             if an error occurs while searching the index.
  725.      * @see gov.usgs.earthquake.distribution.NotificationIndex
  726.      */
  727.     public synchronized List<Notification> findNotifications(String source,
  728.             String type, String code) throws Exception {
  729.         // verify connection
  730.         this.verifyConnection();

  732.         source = (source == null) ? "%" : source.toUpperCase();
  733.         type = (type == null) ? "%" : type.toUpperCase();
  734.         code = (code == null) ? "%" : code.toUpperCase();

  736.         _query_findNotificationsByData.setString(1, source);
  737.         _query_findNotificationsByData.setString(2, type);
  738.         _query_findNotificationsByData.setString(3, code);

  740.         return getNotifications(_query_findNotificationsByData);
  741.     }

  743.     /**
  744.      * Search the index for notifications matching the sources, types, and
  745.      * codes.
  746.      *
  747.      * Only one notification for each unique ProductId
  748.      * (source+type+code+updateTime) should be returned. If sources, types,
  749.      * and/or codes are null, that parameter should be considered a wildcard. If
  750.      * sources, types, and codes are all null, a notification for each unique
  751.      * ProductId in the index should be returned.
  752.      *
  753.      * This implementation require synchronization to prevent SQLExceptions
  754.      * caused by concurrent access. SQLite locks the database whenever there is
  755.      * an open ResultSet resource. So even read queries can end up causing SQL
  756.      * concurrent access problems.
  757.      *
  758.      * @param sources
  759.      *            sources to include, or all if null.
  760.      * @param types
  761.      *            types to include, or all if null.
  762.      * @param codes
  763.      *            codes to include, or all if null.
  764.      * @return a list of matching notifications.
  765.      * @throws Exception
  766.      *             if an error occurs while searching the index.
  767.      */
  768.     public synchronized List<Notification> findNotifications(
  769.             List<String> sources, List<String> types, List<String> codes)
  770.             throws Exception {
  771.         // verify connection
  772.         this.verifyConnection();

  774.         List<Notification> n = null;

  776.         Connection conn = getConnection();
  777.         try {
  778.             // begin a transaction
  779.             conn.setAutoCommit(false);

  781.             // Create a temporary lookup table
  782.             _dml_createTmpTable.executeUpdate();

  784.             // Populate the temporary lookup table with our given lists
  785.             if (sources != null) {
  786.                 // Not null, insert values
  787.                 Iterator<String> iter = sources.iterator();
  788.                 while (iter.hasNext()) {
  789.                     _dml_addTmpSource.setString(1, iter.next());
  790.                     _dml_addTmpSource.addBatch();
  791.                 }
  792.                 _dml_addTmpSource.executeBatch();
  793.             }

  795.             if (types != null) {
  796.                 // Not null, insert values
  797.                 Iterator<String> iter = types.iterator();
  798.                 while (iter.hasNext()) {
  799.                     _dml_addTmpType.setString(1, iter.next());
  800.                     _dml_addTmpType.addBatch();
  801.                 }
  802.                 _dml_addTmpType.executeBatch();
  803.             }

  805.             if (codes != null) {
  806.                 // Not null, insert values
  807.                 Iterator<String> iter = codes.iterator();
  808.                 while (iter.hasNext()) {
  809.                     _dml_addTmpCode.setString(1, iter.next());
  810.                     _dml_addTmpCode.addBatch();
  811.                 }
  812.                 _dml_addTmpCode.executeBatch();
  813.             }

  815.             // is this a problem? reading with uncommitted writes?
  816.             PreparedStatement ps = getCorrectStatement(sources, types, codes);
  817.             n = getNotifications(ps);
  818.         } finally {
  819.             conn.rollback();
  820.             // todo: this looks funky, but it's re-enabling autoCommit, which is
  821.             // needed for selects to not block other transactions
  822.             conn.setAutoCommit(true);
  823.         }

  825.         return n;
  826.     }

  828.     /**
  829.      * Search the index for expired notifications.
  830.      *
  831.      * All expired notifications, even if duplicate, should be returned.
  832.      *
  833.      * @return a list of expired notifications.
  834.      * @throws Exception
  835.      *             if an error occurs while searching the index.
  836.      * @see gov.usgs.earthquake.distribution.NotificationIndex
  837.      */
  838.     public synchronized List<Notification> findExpiredNotifications()
  839.             throws Exception {
  840.         // verify connection
  841.         this.verifyConnection();

  843.         // Create a new calendar object set to current date/time
  844.         java.sql.Date curDate = new java.sql.Date((new Date()).getTime());

  846.         // Bind the expiration date parameter and run the query
  847.         _query_findExpiredNotifications.setDate(1, curDate);

  849.         return getNotifications(_query_findExpiredNotifications);
  850.     }

  852.     /**
  853.      * Executes a prepared statement and parses the result set into a list of
  854.      * notifications. The prepared statement can have any set of criteria and
  855.      * all required parameters should be bound before calling this method. The
  856.      * result set of the prepared statement must include at least: -
  857.      * PRODUCT_SOURCE_COLUMN<br>
  858.      * - PRODUCT_TYPE_COLUMN<br>
  859.      * - PRODUCT_CODE_COLUMN<br>
  860.      * - PRODUCT_UPDATE_COLUMN<br>
  861.      * - EXPIRATION_DATE_COLUMN<br>
  862.      * - TRACKER_URL_COLUMN<br>
  863.      * - PRODUCT_URL_COLUMN<br>
  864.      *
  865.      * @param ps
  866.      *            The prepared statement to execute.
  867.      * @return A list of notifications returned by executing the statement.
  868.      * @throws Exception
  869.      *             If a <code>SQLException</code> occurs.
  870.      */
  871.     protected synchronized List<Notification> getNotifications(PreparedStatement ps)
  872.             throws Exception {
  873.         List<Notification> n = new ArrayList<Notification>();
  874.         ResultSet rs = null;

  876.         try {
  877.             rs = ps.executeQuery();
  878.             while (rs.next()) {
  879.                 n.add(parseNotification(rs.getString(PRODUCT_SOURCE_COLUMN),
  880.                         rs.getString(PRODUCT_TYPE_COLUMN),
  881.                         rs.getString(PRODUCT_CODE_COLUMN),
  882.                         rs.getDate(PRODUCT_UPDATE_COLUMN),
  883.                         rs.getDate(EXPIRATION_DATE_COLUMN),
  884.                         rs.getString(TRACKER_URL_COLUMN),
  885.                         rs.getString(PRODUCT_URL_COLUMN)));
  886.             }
  887.         } finally {
  888.             try {
  889.                 rs.close();
  890.             } catch (Exception e) {
  891.                 //ignore
  892.             }
  893.         }

  895.         return n;
  896.     }

  898.     /**
  899.      * Creates and returns a <code>Notification</code> based on the provided
  900.      * data. If the <code>download</code> string references a valid URL, then a
  901.      * <code>URLNotification</code> is created, otherwise a
  902.      * <code>DefaultNotification</code> is created.
  903.      *
  904.      * @param source
  905.      *            The product source string.
  906.      * @param type
  907.      *            The product type string.
  908.      * @param code
  909.      *            The product code string.
  910.      * @param update
  911.      *            The latest update date/time for the product.
  912.      * @param expires
  913.      *            The date/time when this notification expires.
  914.      * @param tracker
  915.      *            A reference to a URL where information about this product is
  916.      *            posted.
  917.      * @param download
  918.      *            A reference to a URL where one can download this product, or
  919.      *            <code>null</code> if this is not a
  920.      *            <code>URLNotification</code>.
  921.      *
  922.      * @return The generated notification, or <code>null</code> if one could not
  923.      *         be created (but an exception did not occur).
  924.      *
  925.      * @throws Exception
  926.      *             If the <code>tracker</code> string cannot be successfully
  927.      *             parsed into a valid URL.
  928.      */
  929.     protected Notification parseNotification(String source, String type,
  930.             String code, java.sql.Date update, java.sql.Date expires,
  931.             String tracker, String download) throws Exception {
  932.         Notification n = null;
  933.         ProductId productId = new ProductId(source, type, code, update);
  934.         URL trackerURL = !"".equals(tracker) ? new URL(tracker) : null;
  935.         try {
  936.             n = new URLNotification(productId, expires, trackerURL, new URL(download));
  937.         } catch (MalformedURLException mux) {
  938.             n = new DefaultNotification(productId, expires, trackerURL);
  939.         }
  940.         return n;
  941.     }

  943.     /**
  944.      * @param sources List string of sources
  945.      * @param types List string of types
  946.      * @param codes List string of codes
  947.      * @return prepared query based on what is/is not null
  948.      * @throws Exception if error occurs
  949.      */
  950.     protected PreparedStatement getCorrectStatement(List<String> sources,
  951.             List<String> types, List<String> codes) throws Exception {
  952.         if (sources != null && types != null && codes != null) {
  953.             return _query_searchBySourceTypeCode;
  954.         } else if (sources != null && types != null && codes == null) {
  955.             return _query_searchBySourceType;
  956.         } else if (sources != null && types == null && codes != null) {
  957.             return _query_searchBySourceCode;
  958.         } else if (sources == null && types != null && codes != null) {
  959.             return _query_searchByTypeCode;
  960.         } else if (sources != null && types == null && codes == null) {
  961.             return _query_searchBySource;
  962.         } else if (sources == null && types != null && codes == null) {
  963.             return _query_searchByType;
  964.         } else if (sources == null && types == null && codes != null) {
  965.             return _query_searchByCode;
  966.         } else if (sources == null && types == null && codes == null) {
  967.             return _query_getAllNotifications;
  968.         }

  970.         return null;
  971.     }

  973. }
Created with JaCoCo 0.8.8.202204050719