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, DLLogging, 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

    • UPDATE_POLICY_OVERWRITE

      public static final int UPDATE_POLICY_OVERWRITE
      See Also:
    • UPDATE_POLICY_WRITENEW

      public static final int UPDATE_POLICY_WRITENEW
      See Also:
  • Method Details

    • getViewerCursorFinder

      public CursorFinder getViewerCursorFinder()
    • 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 -
    • 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)
    • 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
    • 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
    • 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
    • getLoggingAddOns

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