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

• PamDataBlock
• PamTableDefinition
• PamTableItem

Field Summary

Fields

Modifier and Type	Field	Description
static final int	UPDATE_POLICY_OVERWRITE
static final int	UPDATE_POLICY_WRITENEW

Method Summary

Modifier and Type	Method	Description
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.
void	clearAllAddOns()	Clear all SQL Logging Addons
Integer	countTableItems()
Integer	countTableItems(PAMSelectClause pamViewParameters)	Get a count of the number of records in the given viewer range
String	createInClause(int[] idList)	Make an SQL clause in the from IN ( ...
void	deleteData(long dataStart, long dataEnd)
boolean	deleteData(PAMSelectClause pvp)
boolean	deleteData(PamDataUnit aDataUnit)
boolean	deleteIndexedItems(PamConnection connection, int[] deleteIndexes)	Delete one or more rows from the database based on their indexes.
boolean	doExtraChecks(DBProcess dbProcess, PamConnection connection)	Gives each module a chance to do additional checks and alterations to the database table at connection time.
Float	double2Float(Double val)	Convert a Double to a Float, dealing with null
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.
static final SQLLogging	findLogger(String tableName)	Searches the Pamguard system for a SQLLogger with a given table name.
PamTableDefinition	getBaseTableDefinition()	More and more data blocks are starting to use annotations, which require modifying the table definition, adding additional columns.
String	getEarlyLoadClause(SQLTypes sqlTypes, PamViewParameters pvp)	clause for loading data just prior to the normal viewer load time.
String	getIdListUpdatClause(SaveRequirements sr)
int	getLastLoadIndex()
Long	getLastLoadUID()
long	getLastTime()
ArrayList<SQLLoggingAddon>	getLoggingAddOns()
PamDataBlock	getPamDataBlock()
final PamTableDefinition	getTableDefinition()	Each SQLLogging class must provide a valid Pamguard database definition object
String	getTimesUpdateClause(SQLTypes sqlTypes, SaveRequirements sr)	Get a select clause for viewer updates.
String	getUIDMatchClause(PamDataUnit pamDataUnit, SQLTypes sqlTypes)	Get a string used to match binary data with the database records.
int	getUpdatePolicy()
CursorFinder	getViewerCursorFinder()
String	getViewerEverythingClause(SQLTypes sqlTypes, PamViewParameters pvp)	Get a load clause which selects everything, still ordered by UTC.
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.
String	getViewerLoadClause(SQLTypes sqlTypes, PamViewParameters pvp)	Get a standard select clause for loading viewer data, this is basically that UTC is between two times.
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.
String	getViewerUpdateClause(SaveRequirements sr)	Get a select clause for viewer updates.
boolean	isCanView()
boolean	isLoadViewData()
boolean	loadDataFrom(long startTimeMillis)	Load all data from a certain time.
boolean	loadEarlyData(PamConnection con, PamViewParameters pamViewParameters)	Load early database data.
boolean	loadEarlyData(PamViewParameters pamViewParameters)	Load early database data.
PamDataUnit	loadLastDataUnit()	Load the last data unit in the table.
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.
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.
boolean	loadViewData(PamConnection con, PamViewParameters pamViewParameters, ViewLoadObserver loadObserver)	Load viewer data for a single datablock.
boolean	loadViewData(PamViewParameters pamViewParameters, ViewLoadObserver loadObserver)
boolean	loadViewerData(long dataStart, long dataEnd, ViewLoadObserver loadObserver)
boolean	logData(PamConnection con, PamDataUnit dataUnit)	Called when a new PamDataUnit is added to a PamDataBlock to write those data to the database.
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.
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.
boolean	prepareForMixedMode(PamConnection con)
boolean	readLastData(PamConnection con)	Called when a new database is connected to read the last values back in from the database.
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.
boolean	readNextEmulation(SQLTypes sqlTypes)
boolean	reCheckTable()	Re-check the database tables associated with this Logger.
boolean	reLogData(PamConnection con, PamDataUnit dataUnit)	Called when an old PamDataUnit is updated.
boolean	reLogData(PamConnection con, PamDataUnit dataUnit, PamDataUnit superDetection)	Called when an old PamDataUnit is updated.
boolean	removeAddOn(SQLLoggingAddon sqlLoggingAddon)	Remove an SQLLogging Addon.
boolean	removeAddOn(SQLLoggingAddon sqlLoggingAddon, boolean removeFields)	Remove an SQLLogging Addon.
void	reset()	Reset anything needing resetting in the binary data source.
boolean	saveOfflineData(DBControlUnit dbControlUnit, PamConnection connection)	Save offline data in viewer mode.
void	setCanView(boolean canView)
void	setColumnData(int iCol, Object data)	Data values going in and out of the database are stored with their respective PamTableItems.
void	setLoadViewData(boolean loadViewData)
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.
void	setTableDefinition(PamTableDefinition pamTableDefinition)
void	setUpdatePolicy(int updatePolicy)
boolean	transferDataFromResult(SQLTypes sqlTypes, ResultSet resultSet)

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Details

UPDATE_POLICY_OVERWRITE

public static final int UPDATE_POLICY_OVERWRITE

See Also:

• Constant Field Values

UPDATE_POLICY_WRITENEW

public static final int UPDATE_POLICY_WRITENEW

See Also:

• Constant Field Values

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:

• PamTableDefinition
• PamTableItem

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:

• PamTableDefinition

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:

• PamDataUnit

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:

• PamDataUnit

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:

• PamDataUnit

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:

• PamDataUnit

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