Account

Blacknest BDS Schema Overview

Date	2013-12-03
Version	14

Note that this is a "work in progress" document and is being continually updated.

Introduction

This document provides an overview of the Blacknest Data System (BDS) Database Schema.

Database Schema Overview

The BDS Database holds information on all of the Seismic data and associated metadata. The database will be implemented as a MySQL database. Generally the database is not directly accessed. It is normally only accessed by the BDS Data Engine in response to API requests.

General

Dates and times: These are stored to the nearest micro-second. We are using a MySQL String to store the date/time formated using ISO 8601 format: eg "2002-04-03T11:20:53.103000". An entry of 0000-01-01T00:00:00.000000 is used for unknown. The start of time is "1900-01-01T00:00:00.000000" The end of time is "8000-01-01T00:00:00.000000".

Config

Name	Type	Attributes	Description
id	Integer	unique, not_null, auto_increment	Unique ID for this entry
name	String	Primary	The parameter name
value	String		The parameters value

User and Security Management

Groups

The list of user groups.

Name	Type	Attributes	Description
id	String	primary, unique, auto_increment	Unique ID for this entry
group	String	primary	The group name
description	String		The groups description
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

Users

The list of users.

Name	Type	Attributes	Description
id	Integer	primary, unique, auto_increment	Unique ID for this entry. (Used as the userId)
user	String	primary	The users login ID
password	String		The users login password (Encrypted)
name	String		The users full name
groups	String		The list of groups the user belongs to. (comma separated list of names)
email	String		The users email address
telephone	String		The users telephone number
address	String		The users postal number
enabled	Boolean		If this user is enabled
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on updateCURRENT_TIMESTAMP	DateTime this record was last updated

AccessGroups

Used to keep the list of data access permissions.

Name	Type	Attributes	Description
id	Integer	primary, unique, auto_increment	Unique ID for this entry. (Used as the userId)
group	String		A group that can access this data
startTime	String		The Start time
endTime	String		The End time
network	String		The Network name
station	String		The Station/Array name
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

Overall Information MetaData

Networks

The list of networks.

Name	Type	Attributes	Description
id	Integer	unique, not_null, auto_increment	Unique ID for this entry
network	String	Primary	The Network name
description	String		The Network description
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

NetworkStations

Used to keep the list of all stations managed by a network.

Name	Type	Attributes	Description
id	Integer	unique, not_null, auto_increment	Unique ID for this entry
network	String	Primary	The Network name
station	String		The Station/Array name
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

Stations

Used to keep the list of all station names.

Name	Type	Attributes	Description
id	Integer	unique, not_null, auto_increment	Unique ID for this entry
station	String		The Station/Array name
alias	String		A station alias used for output in data formats
type	String		The station type (Station, Array)
description	String		A description
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

ArrayChannels

Used to keep a list of stations within an array.

Name	Type	Attributes	Description
id	Integer	unique, not_null, auto_increment	Unique ID for this entry
array	String		The Array name
station	String		The station name
channel	String		The channel name
order	Integer		The station/channel order within the array
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

StationLocations

Used to keep the list of all station locations

Name	Type	Attributes	Description
id	Integer	unique, not_null, auto_increment	Unique ID for this entry
startTime	String	Primary	The Start time
endTime	String	Primary	The End time
network	String	Primary	The Network name
station	String	Primary	The Station/Array name
datum	String		The Datum for the Latitude/Longitude normally WGS84
latitude	Double		The Latitude in degrees using the datum.
longitude	Double		The Longitude in degrees using the datum.
elevation	Double		The ground level elevation in metres from the WGS84 ellipsoid (Sea level)
arrayOffsetEast	Double		For a station in an array, this is the offset from the array's centre in km (These should be moved to ArrayChannels)
arrayOffsetNorth	Double		For a station in an array, this is the offset from the array's centre in km (These should be moved to ArrayChannels)
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

Channel MetaData

Channels

Used to keep the list of all channels.

Name	Type	Attributes	Description
id	Integer	unique, not_null, auto_increment	Unique ID for this entry
startTime	String	Primary	The Start time for this channel, when it came into existence
endTime	String	Primary	The End time for this channel, when it ceased existence
network	String	Primary	The Network name
station	String	Primary	The Station/Array name
channel	String		The Channels primary identifier (Usually the channelType and channelAux combined with a "_")
channelType	String		The Channels Type. Three characters such as (BHZ)
channelAux	String		The Channels Auxiliary identifier. Could be the location ID (Usually two digits, e.g. "01")
dataType	String		The type of data. (seismic,seismicUnknown,data,unknown,empty)
description	String		A Description
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

Sources

Name	Type	Attributes	Description
id	Integer	unique, not_null, auto_increment	Unique ID for this entry
source	String	Primary	The sensor data source name
sourceMeta	String		The MetaData source associated with this sensor data
alias	String		A short alias for the source name.
description	String		A Description
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

SourcePrioritys

Name	Type	Attributes	Description
id	Integer	unique, not_null, auto_increment	Unique ID for this entry
startTime	String	Primary	The Start time
endTime	String	Primary	The End time
source	String	Primary	The Source name
priority	Int		The priority. Higher valude is higher priority
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

Calibrations

Used to keep the list of all calibration entries for a channel.

Name	Type	Attributes	Description
id	Integer	unique, not_null, auto_increment	Unique ID for this entry
startTime	String	Primary	The Start time for this channel
endTime	String	Primary	The End time for this channel
network	String	Primary	The Network name
station	String	Primary	The Station/Array name
channel	String	Primary	The Channels primary identifier
source	String	Primary	The Source of the data this metadata is for
name	String	Primary	The Calibration name to handle multiple Calibrations. Currently supports "Main" and "Measured"
samplingFrequency	Double		The Sampling frequency used
calibrationFrequency	Double		The frequency that the CalibrationFactor value is valid for (Hz)
calibrationFactor	Double		The scaling value to apply to the data to normalise to nanometres. This is a measured value at the calibration frequency. For seisometers it is in nanometres/count. The calibrationUnits specify the actual units.
calibrationUnits	String		Unit for calibration Factor, e.g. nanometers. (Maybe we should remove this and always have the calibrationFactor in nanometers/count ?)
depth	Double		The depth below ground level (m)
verticalAngle	Double		The Sensor's placement vertical angle in degrees degrees with zero = vertically up (degrees)
horizontalAngle	Double		The Sensor's placement horizontal angle in degrees clockwise from north (degrees)
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

ChannelInstruments

Used to keep the list of all digitiser/sensor combinations for a channel by date/time.

Name	Type	Attributes	Description
id	Integer	unique, not_null, auto_increment	Unique ID for this entry
startTime	String	Primary	The Start time
endTime	String	Primary	The End time
channelId	Integer	Primary	The Channel id
source	String	Primary	The Source of the data this metadata is for
digitiserId	Integer		The Digitiser in use
sensorId	Integer		The Sensor in use
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

Digitisers

Used to keep the list of all digitisers. Note that digitiser entries can be shared across multiple channels.

Name	Type	Attributes	Description
id	Integer	unique, not_null, auto_increment	Unique ID for this entry
startTime	String	Primary	The Start time, when it came into existence
endTime	String	Primary	The End time, when it ceased existence
name	String		The digitiser's model name. Only used when there is a unique physical digitiser
type	String		The digitiser type. Only used when there is a unique physical digitiser
serialNumber	String		The digitisers's serial number. Only used when there is a unique physical digitiser
numberChannels	Integer		The number of supported channels, for cross checking
baseSamplingFrequency	Double		The main sampling frequency (Hz). This could be 0. The SamplingFrequency in Calibrations will be used for data.
initialSamplingFrequency	Double		The initial pre-decimation sampling frequency (Hz)
gain	Double		The overall gain of the digitiser at the manufacturers calibration frequency (counts/volt?). (For information only)
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

Sensors

Used to keep the list of all sensors. Note that sensor entries can be shared across multiple channels.

Name	Type	Attributes	Description
id	Integer	unique, not_null, auto_increment	Unique ID for this entry
startTime	String	Primary	The Start time, when it came into existence
endTime	String	Primary	The End time, when it ceased existence
name	String		The sensor name.
type	String		The type of sensor. (Seismometer, Hydrophone etc)
serialNumber	String		The sensor's serial number. Only used when there is a unique physical sensor
numberChannels	Integer		The number of supported channels, for cross checking
gain	Double		The overall gain of the sensor at the manufacturers calibration frequency. (For information only)
gainUnits	String		Units in which gain is quoted, e.g. volts per (m/s), volts per Pa
oldId	Integer		Original databases instrument id.
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

Responses

Used to keep the list of all frequency responses for a given channels digitiser and sensors.

Name	Type	Attributes	Description
id	Integer	unique, not_null, auto_increment	Unique ID for this entry
startTime	String	Primary	The Start time
endTime	String	Primary	The End time
network	String	Primary	The Network name
station	String	Primary	The Station/Array name
channel	String	Primary	The Channels primary identifier
source	String	Primary	The Source of the data this metadata is for
stage	Integer	Primary	The stage number. 0 is for Overall response
stageType	String		The stage type: A - Analog (rad/sec), B - Analog (Hz), C - Composite, D - Digital.
name	String		The responses name (Sensor,Digitiser,Anti-Aliasing filter, post filter etc)
type	String		The type of response (PoleZero,AmplitudePhase or FIR Coefficients)
data	Blob		PoleZero, AmplitudePhase or FIR Coefficient data. This is stored in an encoded HEX/ASCII format. We could store as a more reabable ASCII)
gain	Double		Overall gain at gain frequency. (For information)
gainFrequency	Double		Frequency that gain is valid for. (For information)
filterType	String		Not used ??
decimation	Double		Decimation performed post filter. (For information)
symmetry	String		Symmetry for FIR coefficients (A = asymmetric, B = symmetric[odd], C = symmetric[even])
description	String		Misc description.
measured	Boolean		Set if response was a measured response.
sampleRate	Double		The stage's sample rate if set. generally for Digitiser FIR filters. set to 0.00 if not used.
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

Seismic Sensor Data

DataFIles

Used to keep the list of data files managed by the BDS.

Name	Type	Attributes	Description
id	Integer	unique, auto_increment,Primary	Unique integer ID
startTime	String	Primary	The Start time
endTime	String	Primary	The End time
location	String		The location of the data. This could be a local archive or even a remote archive
format	String		The data's format
url	String		File location. This can embody a protocol, host, path and filename.
comment	String		A general comment string.
importUserId	Integer		The user ID of the importing user
importTime	String		The Time the data was imported
state	String		Status info on the import (importing, ok etc)
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

DataChannels

Used to keep the list of all of the data channels and which file they are in.

Name	Type	Attributes	Description
id	Integer	unique, auto_increment,Primary	Unique integer ID
startTime	String	Primary	The Start time
endTime	String	Primary	The End time
network	String	Primary	The Network organisation the original data is from
station	String	Primary	The Station/Array name
channel	String	Primary	The Data channel name
source	String	Primary	Some information on the source of this data (Master, Tape, Processed ...). This would allow multiple sources of data from the same Array and TimePeriod. It could also support processed data.
numBlocks	Integer		The total number of blocks per channel if known, 0 otherwise
numSamples	Integer64		The total number of samples per channel if known, 0 otherwise
sampleRate	Double		The Sample rate
sampleFormat	Integer		The format of each sample (Int16, Int32, Float32 etc)
dataFileId	Integer		The Data Files ID
dataFileChannel	Integer		The channel number within the data file
importFormat	String		The original data format
importFilename	String		The original data file name.
importStartTime	String		The original import files start time
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

Misc Data

Notes

Used to keep the list of data outages and other notes.

Name	Type	Attributes	Description

id	Integer	unique, auto_increment,Primary	Unique integer ID
startTime	String		Optional Start Time
endTime	String		Optional End Time
network	String		Optional Network
station	String		Optional Station
channel	String		Optional Channel
source	String		Optional Source
type	String		The entry type: (Log, Outage, Note, Warning etc)
user	String		The user who made the entry
timeAdded	String		The time the entry was made
errorNumber	Integer		Error number if error.
title	String		A Title for the note
description	String		A Comment for the note
docFormat	String		Document format if any.
docUrl	String		Document Url if any.
dataFileId	Integer		The data file id associated with this note
importFilename	String		The import filename
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

Changes

Used to keep the list of individual changes made to the database.

Name	Type	Attributes	Description
id	Integer	unique, auto_increment,Primary	Unique integer ID
changeGroupId	Integer		The Change Group ID
type	String		The entry type: (Log, Outage, Note, Warning etc)
time	String		The time the change was made
table	String		The Table that was altered. (Should be have a list of tables ?)
rowId	Integer		The tables entry that was altered (We would need a unique ID in each record for this)(Should this be in effect a list ?)
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

ChangeGroups

Used to keep the list of change groups made to the database.

Name	Type	Attributes	Description
id	Integer	unique, auto_increment,Primary	Unique integer ID
type	String		The Change type
time	String		The time the change was made
user	String		The User that made the change
title	String		A Title for the change
description	String		A Comment for the change
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

Logs

Used to keep the list of log entries

Name	Type	Attributes	Description
id	Integer	unique, auto_increment,Primary	Unique integer ID
time	String		The time of the log entry
type	String		The log type (error,warning,notice,debug etc)
priority	Integer		The priority (0 to 5)
subSystem	String		The SubSystem
title	String		A Title
description	String		A Comment
lastUpdate	Timestamp	default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	DateTime this record was last updated

In the future we will also need database information on the following:

Data Request queues