Class SQLLogging

java.lang.Object
generalDatabase.SQLLogging
Direct Known Subclasses:
AcousticSQLLogging, AcquisitionLogging, AISLogger, AlarmLogging, AngleLogging, ArrayAccelLogging, ArraySensorLogging, BufferedSQLLogging, BuoyStatusLogging, CalibrationLogging, ClickTrainDetSubLogging, ClickTrainLogger, CPODSubDetLogging, DbHtLogging, DbSpecial, DepthSQLLogging, DetectionGroupSubLogging, DetectorSQLLogging, DifarSqlLogging, DLGroupSubLogging, DLResultLogging, EffortLogging, FormsLogging, GpsLogger, HydrophoneSQLLogging, IMULogging, LinePlotLogging, ListeningEffortLogging, MapCommentSQLLogging, MarkGroupSubSQLLogging, MeygenLogging, NoiseLogging, OfflineClickLogging, OneBandLogging, PamDetectionLogging, PrintScreenLogging, QAOpsLogging, QASubTableLogging, RecorderLogger, RoccaDetectionLogger, RoccaStatsLogger, RockBlockIncomingLogger, RockBlockOutgoingLogger, SimSoundsLogging, StreamerLogging, SuperDetLogging, TargetMotionSQLLogging, TethysLogger, ThingHeardLogging, TideSQLLogging, TrackedGroupSqlLogging, TriggerBackgroundLogging, UserInputLogger, VetoLogging, VRSQLLogging, WhistleClassifierLogging, WhistleEventLogger

public abstract class SQLLogging extends Object
SQLLogging performs basic SQL actions to write and read data from a database.

Each SQLLogging object is Associated with a PamDataBlock.

When a database is first connected, the last values entered into the database will be read back into Pamguard.

When a PamDataUnit is added to a PamDataBlock, Pamguard will automatically call the fillTableData function. This will automatically fill in any database columns that are cross referenced to data from other tables. It will then call the abstract function setTableData where the programmer is responsible for filling data for other columns.

Author:
Doug Gillespie
See Also:
  • Field Details Link icon

  • Constructor Details Link icon

    • SQLLogging Link icon

      protected SQLLogging(PamDataBlock pamDataBlock)
      SQLLogging constructor.
  • Method Details Link icon

    • getViewerCursorFinder Link icon

      public CursorFinder getViewerCursorFinder()
    • finalize Link icon

      protected void finalize() throws Throwable
      Overrides:
      finalize in class Object
      Throws:
      Throwable
    • findLogger Link icon

      public static final SQLLogging findLogger(String tableName)
      Searches the Pamguard system for a SQLLogger with a given table name. Table name blanks are replaced with the _ character during the search.
      Parameters:
      tableName - table name to search for.
      Returns:
      reference to a SQLLogging
    • setColumnData Link icon

      public void setColumnData(int iCol, Object data)
      Data values going in and out of the database are stored with their respective PamTableItems. This function is used to set data for a particular column before it is written to the database.

      It is more efficient to maintain references to each PamTableItem and to set the values directly in each PamTableItem in the setTableData function.

      Parameters:
      iCol - Database item index
      data - Data object (can be null, otherwise must be correct type for the column)
      See Also:
    • getTableDefinition Link icon

      public final PamTableDefinition getTableDefinition()
      Each SQLLogging class must provide a valid Pamguard database definition object
      Returns:
      a Pamguard database table definition object
      See Also:
    • setTableDefinition Link icon

      public void setTableDefinition(PamTableDefinition pamTableDefinition)
      Parameters:
      pamTableDefinition - PamTableDefinition to set
    • setTableData Link icon

      public abstract void setTableData(SQLTypes sqlTypes, PamDataUnit pamDataUnit)
      Callback function when new data are created that allows the user to set the data for each column. Columns that have data which can be filled automatically (counters, primary keys and columns cross referenced to data in other tables) are filled automatically in fillTableData()
      Parameters:
      pamDataUnit -
    • fillTableData Link icon

      protected void fillTableData(SQLTypes sqlTypes, PamDataUnit pamDataUnit, PamDataUnit superDetection)
      Automatically fills table data columns that can be done automatically (counters, primary keys and columns cross referenced to data in other tables). The abstract function setTableData is then called to fill in the other columns with detector specific data.
      Parameters:
      pamDataUnit -
      superDetection -
    • logData Link icon

      public boolean logData(PamConnection con, PamDataUnit dataUnit)
      Called when a new PamDataUnit is added to a PamDataBlock to write those data to the database. Functionality moved down to PamCursor so that exact writing method can become database specific if necessary.
      Parameters:
      con - Database Connection
      dataUnit - Pamguard Data unit.
      Returns:
      true if written and new index of dataUnit retrieved OK
      See Also:
    • logData Link icon

      public boolean logData(PamConnection con, PamDataUnit dataUnit, PamDataUnit superDetection)
      Called when a new PamDataUnit is added to a PamDataBlock to write those data to the database. Functionality moved down to PamCursor so that exact writing method can become database specific if necessary.
      Parameters:
      con - Database Connection
      dataUnit - Pamguard Data unit.
      superDetection - reference to a super detection so additional cross referencing can be filled
      Returns:
      true if written and new index of dataUnit retrieved OK
      See Also:
    • reLogData Link icon

      public boolean reLogData(PamConnection con, PamDataUnit dataUnit)
      Called when an old PamDataUnit is updated. The record is either updated or a new record is written, but cross referenced to the old unit for bookkeeping purposes based on the updatePolicy flag.
      Parameters:
      con - Database Connection
      dataUnit - Pamguard Data unit.
      Returns:
      the number of rows written to the database.
      See Also:
    • reLogData Link icon

      public boolean reLogData(PamConnection con, PamDataUnit dataUnit, PamDataUnit superDetection)
      Called when an old PamDataUnit is updated. The record is either updated or a new record is written, but cross referenced to the old unit for bookkeeping purposes based on the updatePolicy flag.
      Parameters:
      con - Database Connection
      dataUnit - Pamguard Data unit.
      superDetection - reference to a super detection so additional cross referencing can be filled
      Returns:
      the number of rows written to the database.
      See Also:
    • findClosestDataPoint Link icon

      public PamDataUnit findClosestDataPoint(PamConnection con, long timeMillis)
      Find the data point which is closest in time to that given, or null returning whatever type of data unit this deals with.
      Parameters:
      timeMillis -
      Returns:
    • readLastData Link icon

      public boolean readLastData(PamConnection con)
      Called when a new database is connected to read the last values back in from the database.
      Parameters:
      con - Database connection handle.
      Returns:
      true is successful or false if no data available or an error occurred
    • loadLastDataUnit Link icon

      public PamDataUnit loadLastDataUnit()
      Load the last data unit in the table.
      Returns:
      last data unit in the table.
    • loadViewerData Link icon

      public boolean loadViewerData(long dataStart, long dataEnd, ViewLoadObserver loadObserver)
    • countTableItems Link icon

      public Integer countTableItems(PAMSelectClause pamViewParameters)
      Get a count of the number of records in the given viewer range
      Parameters:
      pamViewParameters -
      Returns:
      null if an error, otherwise a count of records.
    • loadViewData Link icon

      public boolean loadViewData(PamViewParameters pamViewParameters, ViewLoadObserver loadObserver)
    • loadViewData Link icon

      public boolean loadViewData(PamConnection con, PamViewParameters pamViewParameters, ViewLoadObserver loadObserver)
      Load viewer data for a single datablock.

      this executes in a Swing Worker thread, so needs to send notification objects to that thread, and not direct to the Controller so that they can be published back in the AWT thread.

      Parameters:
      con - database connection
      pamViewParameters - viewer parameters.
      loadObserver -
      loadViewerData - Swing Worker object
      Returns:
    • loadDataFrom Link icon

      public boolean loadDataFrom(long startTimeMillis)
      Load all data from a certain time.
      Parameters:
      startTimeMillis - start time in milliseconds
      Returns:
      true if load successful (does not mean any data were loaded)
    • loadEarlyData Link icon

      public boolean loadEarlyData(PamViewParameters pamViewParameters)
      Load early database data.
      Parameters:
      pamViewParameters - query on what to load.
      Returns:
      true if load successful (does not mean any data were loaded)
    • loadEarlyData Link icon

      public boolean loadEarlyData(PamConnection con, PamViewParameters pamViewParameters)
      Load early database data.
      Parameters:
      con - Database connection
      pamViewParameters - query on what to load.
      Returns:
      true if load successful (does not mean any data were loaded)
    • getViewerLoadClause Link icon

      public String getViewerLoadClause(SQLTypes sqlTypes, PamViewParameters pvp)
      Get a standard select clause for loading viewer data, this is basically that UTC is between two times.
      Parameters:
      sqlTypes - SQL types - database specific functions
      pvp - load parameters.
      Returns:
      Clause, in the form WHERE UTC BETWEEN A and B ORDER BY UTC
    • getViewerOverlapClause Link icon

      public String getViewerOverlapClause(SQLTypes sqlTypes, PamViewParameters pvp, String endTimeName)
      Get a clause for loading viewer data where an overlap is required between an extended event and the data load period. an extended "event" and
      Parameters:
      sqlTypes - SQL types - database specific functions
      pvp - load parameters.
      endTimeName - Name of the field representing the end of the event.
      Returns:
      Clause in the format WHERE NOT (UTCinvalid input: '&gtloadEnd' OR EventEndinvalid input: '&ltLoadStart') ORDER BY UTC
    • getViewerLessThanClause Link icon

      public String getViewerLessThanClause(SQLTypes sqlTypes, PamViewParameters pvp, boolean onlyNulls, String colNameToTest)
      Get a clause for loading viewer data where UTC time is less than the passed end time. This method can also limit the results to only rows with a null in a specified column.
      Parameters:
      sqlTypes - SQL types - database specific functions
      pvp - load parameters
      onlyNulls - whether to only include rows with nulls in a specific column (true) or all rows (false)
      colNameToTest - which column to check for nulls - ignored if onlyNulls is false
      Returns:
    • getViewerEverythingClause Link icon

      public String getViewerEverythingClause(SQLTypes sqlTypes, PamViewParameters pvp)
      Get a load clause which selects everything, still ordered by UTC.
      Parameters:
      sqlTypes - SQL types - database specific functions (not used)
      pvp - load parameters. (not used)
      Returns:
      "ORDER BY UTC";
    • getEarlyLoadClause Link icon

      public String getEarlyLoadClause(SQLTypes sqlTypes, PamViewParameters pvp)
      clause for loading data just prior to the normal viewer load time.
      Parameters:
      pvp - load view parameters.
      Returns:
      a SQL clause string.
    • deleteData Link icon

      public void deleteData(long dataStart, long dataEnd)
    • deleteData Link icon

      public boolean deleteData(PAMSelectClause pvp)
    • createDataUnit Link icon

      protected PamDataUnit createDataUnit(SQLTypes sqlTypes, long timeMilliseconds, int databaseIndex)
      Turn the data, which have been transferred back into the PamTableItems back into a usable data. Don't add to datablock. This will happen in calling function after additional add-ons and annotations have been added to the data unit. .
      Returns:
      true if a data unit was successfully created
    • createViewResultSet Link icon

      protected ResultSet createViewResultSet(PamConnection con, PamViewParameters pamViewParameters)
      Get a results set for the viewer. In principle, the view parameters can contain information about the analysis time as well as the actual data view time, but this is never used
      Parameters:
      con - connection
      pamViewParameters - view parameters.
      Returns:
      a result set
    • createViewResultSet Link icon

      protected ResultSet createViewResultSet(PamConnection con, String selectClause)
      Create a bespoke result set.
      Parameters:
      con -
      selectClause -
      Returns:
    • createEarlyResultSet Link icon

      protected ResultSet createEarlyResultSet(PamConnection con, PamViewParameters pamViewParameters)
      Get a result set for data preceding the main view time. Uses similar SQL, but is all just before the main time and reverse ordered.
      Parameters:
      con - connection
      pamViewParameters - view parameters.
      Returns:
      a result set
    • transferDataFromResult Link icon

      public boolean transferDataFromResult(SQLTypes sqlTypes, ResultSet resultSet)
    • getPamDataBlock Link icon

      public PamDataBlock getPamDataBlock()
    • prepareEmulation Link icon

      public boolean prepareEmulation(long[] times)
      Written to prepare the AIS module for general data emulation - will try to put it in this higher level class, but will move to AISLogger if there are any problems with it.
      Parameters:
      times - time limits for the emulation.
      Returns:
      true if statement prepared OK/
    • readNextEmulation Link icon

      public boolean readNextEmulation(SQLTypes sqlTypes)
    • prepareForMixedMode Link icon

      public boolean prepareForMixedMode(PamConnection con)
    • readMixedModeData Link icon

      public boolean readMixedModeData(SQLTypes sqlTypes, long timeTo)
      always creates the data unit on the next pass through each loop so that they are only created AFTER the tiem cut off ahs passed.
      Parameters:
      timeTo -
      Returns:
      true if data were read and used
    • getUpdatePolicy Link icon

      public int getUpdatePolicy()
    • setUpdatePolicy Link icon

      public void setUpdatePolicy(int updatePolicy)
    • isCanView Link icon

      public boolean isCanView()
    • setCanView Link icon

      public void setCanView(boolean canView)
    • isLoadViewData Link icon

      public boolean isLoadViewData()
    • setLoadViewData Link icon

      public void setLoadViewData(boolean loadViewData)
    • reCheckTable Link icon

      public boolean reCheckTable()
      Re-check the database tables associated with this Logger. This only needs to be called if the content of tables has changed during PAMGUARD operations.
      Returns:
      true if checks successful.

      Note that if no database is present, false will be returned

    • doExtraChecks Link icon

      public boolean doExtraChecks(DBProcess dbProcess, PamConnection connection)
      Gives each module a chance to do additional checks and alterations to the database table at connection time. e.g. the GPS module may want to check that the table is not an old Logger table and if necessary alter the columns into the PAMGUARD format.
      Parameters:
      connection -
      Returns:
      true if tests and alterations OK.
    • getLastTime Link icon

      public long getLastTime()
      Returns:
      the last time of data read in during mixed mode or NMEA emulations.
    • getLastLoadIndex Link icon

      public int getLastLoadIndex()
    • getLastLoadUID Link icon

      public Long getLastLoadUID()
    • saveOfflineData Link icon

      public boolean saveOfflineData(DBControlUnit dbControlUnit, PamConnection connection)
      Save offline data in viewer mode.

      This is a pretty basic function which assumes pretty much a 1:1 correspondence between data loaded into the viewer and data to be saved.

      Three types of saving to do

      1. Pre existing 1:1 correspondence between data in memory and database
      2. Viewer will have added or removed data (e.g. map comments may easily have added data)
      3. Weird stuff like in the click detector where the database is only holding information about a subset of clicks held in the binary data files

      Should be able to deal with first two here, but functions must override for 3.

      Parameters:
      dbControlUnit -
      connection -
      Returns:
    • deleteIndexedItems Link icon

      public boolean deleteIndexedItems(PamConnection connection, int[] deleteIndexes)
      Delete one or more rows from the database based on their indexes.
      Parameters:
      connection -
      deleteIndexes - list of Id's
      Returns:
      true if no exception
    • insertCursorRow Link icon

      protected int insertCursorRow(PamConnection connection, PamCursor pamCursor, PamDataUnit aUnit) throws SQLException
      Used in standard offline save to add data to a scrollable cursor and adds the cursor row id to the data unit.
      Parameters:
      connection -
      pamCursor -
      aUnit -
      Returns:
      The
      Throws:
      SQLException
    • updateCursorRow Link icon

      protected void updateCursorRow(PamConnection connection, PamCursor pamCursor, PamDataUnit aUnit) throws SQLException
      Used in standard offline save to update data in a scrollable cursor.
      Parameters:
      connection -
      pamCursor -
      aUnit -
      Throws:
      SQLException
    • getViewerUpdateClause Link icon

      public String getViewerUpdateClause(SaveRequirements sr)
      Get a select clause for viewer updates. this may be overridden for some data types depending on what' most optimal for date retrieval.

      A couple of examples you may want to use are in getTimesUpdateClause and getIdListUpdatClause

      getIdListUpdatClause is currently the default.

      Parameters:
      sr - requirements extracted from loaded data
      Returns:
      clause string (including any sort)
    • getTimesUpdateClause Link icon

      public String getTimesUpdateClause(SQLTypes sqlTypes, SaveRequirements sr)
      Get a select clause for viewer updates. this may be overridden for some data types depending on what' most optimal for date retrieval.

      for example, the default selection is based on time - which won't work if the event times may have changed - ok for things which will be fixed in time.

      Parameters:
      sr - requirements extracted from loaded data
      Returns:
      clause string (including any sort)
    • getIdListUpdatClause Link icon

      public String getIdListUpdatClause(SaveRequirements sr)
    • createInClause Link icon

      public String createInClause(int[] idList)
      Make an SQL clause in the from IN ( ... )
      Parameters:
      idList -
      Returns:
      string clause.
    • addAddOn Link icon

      public void addAddOn(SQLLoggingAddon sqlLoggingAddon)
      Add an SQL Logging addon - something which adds some standard columns to what it probably a non standard table, e.g. adding target motion results to a click event detection.

      Note that database checks are carried out immediately this gets added.

      Parameters:
      sqlLoggingAddon -
    • removeAddOn Link icon

      public boolean removeAddOn(SQLLoggingAddon sqlLoggingAddon)
      Remove an SQLLogging Addon.
      Parameters:
      sqlLoggingAddon -
      Returns:
      true if the addon existed.
    • removeAddOn Link icon

      public boolean removeAddOn(SQLLoggingAddon sqlLoggingAddon, boolean removeFields)
      Remove an SQLLogging Addon.
      Parameters:
      sqlLoggingAddon -
      Returns:
      true if the addon existed.
    • clearAllAddOns Link icon

      public void clearAllAddOns()
      Clear all SQL Logging Addons
    • double2Float Link icon

      public Float double2Float(Double val)
      Convert a Double to a Float, dealing with null
      Parameters:
      val - Double object
      Returns:
      Float object.
    • reset Link icon

      public void reset()
      Reset anything needing resetting in the binary data source. This get's called just before PamStart().
    • deleteData Link icon

      public boolean deleteData(PamDataUnit aDataUnit)
    • getUIDMatchClause Link icon

      public String getUIDMatchClause(PamDataUnit pamDataUnit, SQLTypes sqlTypes)
      Get a string used to match binary data with the database records.
      Parameters:
      pamDataUnit - Data unit to match
      sqlTypes - type for specific formatting.
      Returns:
      WHERE type clause (without the WHERE)
    • countTableItems Link icon

      public Integer countTableItems()
    • getBaseTableDefinition Link icon

      public PamTableDefinition getBaseTableDefinition()
      More and more data blocks are starting to use annotations, which require modifying the table definition, adding additional columns. This get's a bit awkward, when annotations are being added and removed. To help, we're going to try storing a baseTableDefinition the very first time that the main table definition is set, so that it can be got and modified by the annotation handler shortly after the main table is created.
      Returns:
      the baseTableDefinition
    • logSubtableData Link icon

      protected boolean logSubtableData(PamConnection con, SuperDetection superDetection)
      If there are subdetections associated with the passed data unit, this method goes through and creates the subtable. Note that this should be called using the expression subtable.logSubtableData, because it uses the getTableDefinition() method to get the subtable table (so we need to actually be in the subtable when calling this, or else the incorrect table will be returned)
      Parameters:
      con - PamConnection to use
      superDetection - the parent data unit that may or may not have subdetections to log in the table
      Returns:
    • loadSubtableData Link icon

      public ArrayList<PamSubtableData> loadSubtableData(PamConnection con, SQLLogging parentLogging, String idList, ViewLoadObserver loadObserver)
      Loads the subtable data related to sub/superdetection linking, and stores it in a PamSubtableData ArrayList for reattachment later. Note that this DOES NOT load any additional columns of data added into overridden setTableData methods.
      Parameters:
      con - database connection
      parentLogging - super detection logging instance.
      idList - list of ID's in the parent data that have been loaded. Note Id, NOT UID
      Returns:
      list of all PamSubtableData items
    • loadSubtableData Link icon

      public ArrayList<PamSubtableData> loadSubtableData(PamConnection con, SQLLogging parentLogging, PamViewParameters pvp, ViewLoadObserver loadObserver)
      Called within a subLogging from the superLogging Loads the subtable data related to sub/superdetection linking, and stores it in a PamSubtableData ArrayList for reattachment later. Note that this DOES NOT load any additional columns of data added into overridden setTableData methods.
      Parameters:
      con - database connection
      parentLogging - super detection logging instance.
      pvp - view parameters (time based)
      loadObserver -
      Returns:
      list of all PamSubtableData items
    • updateSubtable Link icon

      protected boolean updateSubtable(PamConnection connection, SuperDetection parentUnit)
      Updates a subtable by adding new subdetections and removing deleted subdetections
      Parameters:
      connection -
      parentUnit -
      Returns:
    • deleteSubtableItem Link icon

      protected boolean deleteSubtableItem(PamConnection con, long parentUID, long subDbIndex)
      Deletes a single row from the subtable, based on the database index passed to this method.
      Parameters:
      con - PamConnection to use
      parentUID - UID of the super detection
      subDbIndex - UID of the sub detection. Use instead of UID, since more reliable.
      dbIndex - the database index we are deleting
      Returns:
      true if no SQL error
    • deleteSubtableItems Link icon

      protected boolean deleteSubtableItems(PamConnection con, long parentID)
      Deletes all sub table items associated with this super detection.
      Parameters:
      con - PamConnection to use
      dbIndex - the database index we are deleting
      parentUID - UID of the super detection
      Returns:
    • getLoggingAddOns Link icon

      public ArrayList<SQLLoggingAddon> getLoggingAddOns()
      Returns:
      the loggingAddOns