Package generalDatabase

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

  • Constructor Details

    • SQLLogging

      protected SQLLogging(PamDataBlock pamDataBlock)
      SQLLogging constructor.

  • Method Details

    • getViewerCursorFinder

      public CursorFinder getViewerCursorFinder()

    • finalize

      protected void finalize() throws Throwable
      Overrides:
      finalize in class Object
      Throws:
      Throwable

    • findLogger

      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

      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

      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

      public void setTableDefinition(PamTableDefinition pamTableDefinition)
      Parameters:
      pamTableDefinition - PamTableDefinition to set

    • setTableData

      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

      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

      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

      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

      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

      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

      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

      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

      public PamDataUnit loadLastDataUnit()
      Load the last data unit in the table.
      Returns:
      last data unit in the table.

    • loadViewerData

      public boolean loadViewerData(long dataStart, long dataEnd, ViewLoadObserver loadObserver)

    • countTableItems

      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

      public boolean loadViewData(PamViewParameters pamViewParameters, ViewLoadObserver loadObserver)

    • loadViewData

      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

      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

      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

      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

      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

      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

      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

      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

      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

      public void deleteData(long dataStart, long dataEnd)

    • deleteData

      public boolean deleteData(PAMSelectClause pvp)

    • createDataUnit

      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

      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

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

    • createEarlyResultSet

      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

      public boolean transferDataFromResult(SQLTypes sqlTypes, ResultSet resultSet)

    • getPamDataBlock

      public PamDataBlock getPamDataBlock()

    • prepareEmulation

      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

      public boolean readNextEmulation(SQLTypes sqlTypes)

    • prepareForMixedMode

      public boolean prepareForMixedMode(PamConnection con)

    • readMixedModeData

      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

      public int getUpdatePolicy()

    • setUpdatePolicy

      public void setUpdatePolicy(int updatePolicy)

    • isCanView

      public boolean isCanView()

    • setCanView

      public void setCanView(boolean canView)

    • isLoadViewData

      public boolean isLoadViewData()

    • setLoadViewData

      public void setLoadViewData(boolean loadViewData)

    • reCheckTable

      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

      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

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

    • getLastLoadIndex

      public int getLastLoadIndex()

    • getLastLoadUID

      public Long getLastLoadUID()

    • saveOfflineData

      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

      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

      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

      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

      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

      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

      public String getIdListUpdatClause(SaveRequirements sr)

    • createInClause

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

    • addAddOn

      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

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

    • removeAddOn

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

    • clearAllAddOns

      public void clearAllAddOns()
      Clear all SQL Logging Addons

    • double2Float

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

    • reset

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

    • deleteData

      public boolean deleteData(PamDataUnit aDataUnit)

    • getUIDMatchClause

      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

      public Integer countTableItems()

    • getBaseTableDefinition

      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

      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

      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

      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

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

    • deleteSubtableItem

      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

      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

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