generalDatabase
Class SQLLogging

java.lang.Object
  generalDatabase.SQLLogging

Direct Known Subclasses:

AcquisitionLogging, AISLogger, AngleLogging, BeakedExperimentLogging, BeakedLogging, ClickTrainLogger, DbSpecial, DepthSQLLogging, DetectorSQLLogging, GpsLogger, ListeningEffortLogging, MapCommentSQLLogging, NoiseLogging, OfflineClickLogging, OfflineEventLogging, PamDetectionLogging, RecorderLogger, ShoreStationLogging, TerrellaLogger, ThingHeardLogging, UserInputLogger, VetoLogging, VRLogging, WhistleClassifierLogging, WhistleEventLogger, WorkshopSQLLogging

public abstract class SQLLogging
extends java.lang.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

private boolean	canView
private PamViewParameters	currentViewParameters
private java.sql.Statement	dbStatement
private int	lastLoadIndex Database index of last item loaded offlien or mixed mode
private long	lastTime time of last item loaded offline or mixed mode
private boolean	loadViewData
private CursorFinder	loggingCursorFinder
private boolean	mixedDataWaiting
private java.sql.ResultSet	mixedModeResult
private java.sql.PreparedStatement	mixedModeStatement
private PamDataBlock	pamDataBlock
private PamTableDefinition	pamTableDefinition Reference to the table definition object.
private java.sql.ResultSet	resultSet
private java.sql.Connection	selectConnection
private java.sql.PreparedStatement	selectStatement
private java.sql.Connection	statementCon
static int	UPDATE_POLICY_OVERWRITE
static int	UPDATE_POLICY_WRITENEW
private int	updatePolicy
private CursorFinder	viewerCursorFinder

Constructor Summary

protected

SQLLogging(PamDataBlock pamDataBlock) SQLLogging constructor.

Method Summary

protected boolean	createDataUnit(long timeMilliseconds, int databaseIndex) Turn the data, which have been transferred back into the PamTableItems back into a useable data unit and put it into the datablock.
java.lang.String	createInClause(int[] idList) Make an SQL clause in the from IN ( ...
protected java.sql.ResultSet	createViewResultSet(java.sql.Connection con, PamViewParameters pamViewParameters)
boolean	doExtraChecks(DBProcess dbProcess, java.sql.Connection connection) Gives each module a chance to do additional checks and alterations to the database table at connection time.
protected void	fillTableData(PamDataUnit pamDataUnit) Automatically fills table data columns that can be done automatically (counters, primary keys and columns cross referenced to data in other tables).
protected void	finalize()
static SQLLogging	findLogger(java.lang.String tableName) Searches the Pamguard system for a SQLLogger with a given table name.
java.lang.String	getIdListUpdatClause(SaveRequirements sr)
long	getLastTime()
PamDataBlock	getPamDataBlock()
private java.sql.ResultSet	getResultSet(java.sql.Connection con)
PamTableDefinition	getTableDefinition() Each SQLLogging class must provide a valid Pamguard database definition object
java.lang.String	getTimesUpdateClause(SaveRequirements sr) Get a select clause for viewer updates.
int	getUpdatePolicy()
CursorFinder	getViewerCursorFinder()
java.lang.String	getViewerLoadClause(PamViewParameters pvp)
java.lang.String	getViewerUpdateClause(SaveRequirements sr) Get a select clause for viewer updates.
boolean	isCanView()
boolean	isLoadViewData()
boolean	loadViewData(java.sql.Connection con, PamViewParameters pamViewParameters) Load viewer data for a single datablock.
boolean	loadViewerData(long dataStart, long dataEnd)
boolean	logData(java.sql.Connection con, PamDataUnit dataUnit) 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(java.sql.Connection con)
boolean	readLastData(java.sql.Connection con) Called when a new database is connected to read the last values back in from the database.
boolean	readMixedModeData(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()
boolean	reCheckTable() Recheck the databse tables associated with this Logger.
boolean	reLogData(java.sql.Connection con, PamDataUnit dataUnit) Called when an old PamDataUnit is updated.
boolean	saveOfflineData(DBControlUnit dbControlUnit, java.sql.Connection connection) Save offline data in viewer mode.
void	setCanView(boolean canView)
void	setColumnData(int iCol, java.lang.Object data) Data values going in and out of the database are stored with their respective PamTableItems.
void	setLoadViewData(boolean loadViewData)
abstract void	setTableData(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)
private boolean	standardOfflineSave(DBControlUnit dbControlUnit, java.sql.Connection connection)
boolean	transferDataFromResult(java.sql.ResultSet resultSet)

Methods inherited from class java.lang.Object

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

Field Detail

selectStatement

private java.sql.PreparedStatement selectStatement

selectConnection

private java.sql.Connection selectConnection

pamTableDefinition

private PamTableDefinition pamTableDefinition

Reference to the table definition object. This MUST be set from within the concrete logging class.

currentViewParameters

private PamViewParameters currentViewParameters

pamDataBlock

private PamDataBlock pamDataBlock

lastTime

private long lastTime

time of last item loaded offline or mixed mode

lastLoadIndex

private int lastLoadIndex

Database index of last item loaded offlien or mixed mode

dbStatement

private java.sql.Statement dbStatement

canView

private boolean canView

loadViewData

private boolean loadViewData

loggingCursorFinder

private CursorFinder loggingCursorFinder

viewerCursorFinder

private CursorFinder viewerCursorFinder

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

updatePolicy

private int updatePolicy

statementCon

private java.sql.Connection statementCon

resultSet

private java.sql.ResultSet resultSet

mixedModeStatement

private java.sql.PreparedStatement mixedModeStatement

mixedModeResult

private java.sql.ResultSet mixedModeResult

mixedDataWaiting

private boolean mixedDataWaiting

Constructor Detail

SQLLogging

protected SQLLogging(PamDataBlock pamDataBlock)

SQLLogging constructor.

Method Detail

getViewerCursorFinder

public CursorFinder getViewerCursorFinder()

finalize

protected void finalize()
                 throws java.lang.Throwable

Overrides:

finalize in class java.lang.Object

Throws:

java.lang.Throwable

findLogger

public static final SQLLogging findLogger(java.lang.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,
                          java.lang.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(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 final void fillTableData(PamDataUnit pamDataUnit)

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 -

logData

public boolean logData(java.sql.Connection 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 retreived OK

See Also:

PamDataUnit

reLogData

public boolean reLogData(java.sql.Connection 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 nit.

Returns:

the number of rows written to the database.

See Also:

PamDataUnit

getResultSet

private java.sql.ResultSet getResultSet(java.sql.Connection con)

readLastData

public boolean readLastData(java.sql.Connection 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

loadViewerData

public boolean loadViewerData(long dataStart,
                              long dataEnd)

loadViewData

public boolean loadViewData(java.sql.Connection con,
                            PamViewParameters pamViewParameters)

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:

loadViewerData - Swing Worker object

con - database connection

pamViewParameters - viewer parameters.

Returns:

getViewerLoadClause

public java.lang.String getViewerLoadClause(PamViewParameters pvp)

createDataUnit

protected boolean createDataUnit(long timeMilliseconds,
                                 int databaseIndex)

Turn the data, which have been transferred back into the PamTableItems back into a useable data unit and put it into the datablock.

Returns:

true if a data unit was sucessfully created

createViewResultSet

protected java.sql.ResultSet createViewResultSet(java.sql.Connection con,
                                                 PamViewParameters pamViewParameters)

transferDataFromResult

public final boolean transferDataFromResult(java.sql.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()

prepareForMixedMode

public boolean prepareForMixedMode(java.sql.Connection con)

readMixedModeData

public boolean readMixedModeData(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()

Recheck the databse 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 sucessful.

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

doExtraChecks

public boolean doExtraChecks(DBProcess dbProcess,
                             java.sql.Connection 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.

saveOfflineData

public boolean saveOfflineData(DBControlUnit dbControlUnit,
                               java.sql.Connection 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 tow here, but functions must override for 3.

Parameters:

dbControlUnit -

connection -

Returns:

standardOfflineSave

private boolean standardOfflineSave(DBControlUnit dbControlUnit,
                                    java.sql.Connection connection)

getViewerUpdateClause

public java.lang.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 java.lang.String getTimesUpdateClause(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 java.lang.String getIdListUpdatClause(SaveRequirements sr)

createInClause

public java.lang.String createInClause(int[] idList)

Make an SQL clause in the from IN ( ... )

Parameters:

idList -

Returns:

string clause.