Annotation Database Entity Documentation
The database is large, complex and relational with a lot of entities describing many facets of ROV operations, measurements and observations.
Notes About Specific Entities
These notes about specific entities complement the generated documentation below.
rov.dive and rov.transect
Dives represent the span of time during which an ROV, submersible or drop camera is in the water, but may also represent the time during which the instruments are operating. In cases when the instrument data are contained entirely within the time span logged by the operator, the times are recorded unchanged. When the instrument data extend beyond the recorded times of the dive, they may be extended. Occasionally, the operator will record an erroneous time, or omit a time altogether. The instrument times can help to reconstruct this history. Dives also relate to important configuration information, objectives, crew and operational notes.
Transects are defined by their start and end times, contained entirely within dives and do not cross the boundaries between dives. They do not have any related entities and function mainly as accounting items. However, they are extremely important in the analysis of observations and measurements collected during the dive. For example, if one is calculating the density of a particular population of organisms, one must know the precise geographic extent of the region. Transects accomplish this and store objectives and operational notes.
In instances where the times of transects are not recorded, or lost, they can sometimes be reconstructed from status events in the observation record (e.g., a VideoMiner database may have transect start/end events). Otherwise, they are not recorded at all. Ideally, transect records are present and their start and end times are recorded without modification, so long as they are reasonable (i.e., the end time occurs after the start time).
Generated Documentation
Database entity documentation is generated from the production database by the script, db_generate_docs.py. The script outputs a file whose contents can be copied into this page after the <!-- BEGIN GENERATED CONTENT --> tag.
The documentation is arranged into conceptual groupings with the following structure.
- Schema
- Table
- Materialized View
- View
System and Django tables aren't included. The ID column of each table is also excluded.
Database Comments
Documentation resides in the database itself, in the form of comments on the relations and columns. Documentations can be created by executing SQL statements in the form,
COMMENT ON SHCEMA [schema] IS 'This is a schema comment.' COMMENT ON TABLE [schema].[table name] IS 'This is a table comment.' COMMENT ON COLUMN [schema].[table name].[column name] IS 'This is a column comment.'
Comments are managed using DDL, as described on the Database Upgrades page.
Generated Entity Documentation
Note: this page is auto-generated from the documentation comments in the actual database. Do not edit it directly.
Schema: inaturalist
Stores data from the iNaturalist taxonomy catalogue.
Table: observation
Stores observations from the iNaturalist species guide. TODO: This table isn't linked to the taxon table by a foreign key relation because the taxon ID is not unique in this table.
Name | Type | Nullable | Documentation |
---|---|---|---|
captive_cultivated | boolean | YES | True, if the observation is of a captive or farmed organism. |
common_name | character varying | YES | The common name of the organism. |
coordinates_obscured | boolean | YES | Indicates whether the coordinate is obscured for the public. |
created_at | timestamp with time zone | YES | The time of creation of the record. |
description | text | YES | A description of the observation. |
geoprivacy | character varying | YES | Set to private if the record is not public. TODO: Other values? |
iconic_taxon_name | character varying | YES | Grouping by arbitrary taxonomic level. |
image_url | text | YES | A URL for an image of the observation. |
latitude | real | YES | The latitude of the observation. |
license | character varying | YES | The license applied to the record. |
longitude | real | YES | The longitude of the observation. |
num_identification_agreements | smallint | YES | The number of votes for the identifaction. |
num_identification_disagreements | smallint | YES | Number of votes against the identification. |
oauth_application_id | character varying | YES | The ID of an OAUTH application used to modify the record. |
observed_on | date | YES | The time of observation. Created by combining the observed_on and time_observed_at and time_zone fields. |
place_guess | character varying | YES | The informal name of the site of the observation. |
positional_accuracy | integer | YES | The positional accuracy of the observation position (m). |
positioning_device | character varying | YES | The device used to collect the position. |
positioning_method | character varying | YES | The positioning method. |
public_positional_accuracy | integer | YES | The positional accuracy of the position showed to the public (m). |
quality_grade | character varying | YES | The quality of the report. 'research' indicates that the record is authoritative. |
scientific_name | character varying | YES | The scientific name given to the observation. |
sound_url | text | YES | A URL for a sound file for the record. |
species_guess | character varying | YES | A guess at the species name. |
tag_list | jsonb | YES | A list of tags associated with the observation. Represented as a JSON list. |
taxon_class_name | character varying | YES | The name of the taxon's class. |
taxon_family_name | character varying | YES | The family of the taxon. |
taxon_form_name | character varying | YES | The form of the taxon. |
taxon_genus_name | character varying | YES | The genus of the taxon. |
taxon_genushybrid_name | character varying | YES | The hybrid genus of the taxon. |
taxon_geoprivacy | character varying | YES | The privacy setting of the taxon. 'open' or 'obscured'. |
taxon_hybrid_name | character varying | YES | The hybrid name of the taxon. |
taxon_id | integer | NO | The iNaturalist taxon ID. |
taxon_kingdom_name | character varying | YES | The taxon' kingdom. |
taxon_order_name | character varying | YES | The order of the taxon. |
taxon_phylum_name | character varying | YES | The taxon phylum. |
taxon_species_name | character varying | YES | The species of the taxon. |
taxon_subclass_name | character varying | YES | The taxon's subclass. |
taxon_subfamily_name | character varying | YES | The subfamily of the taxon. |
taxon_suborder_name | character varying | YES | The suborder of a taxon. |
taxon_subphylum_name | character varying | YES | The taxon subphylum. |
taxon_subspecies_name | character varying | YES | The subspecies of the taxon. |
taxon_subtribe_name | character varying | YES | The subtribe of the taxon. |
taxon_superclass_name | character varying | YES | The taxon superclass. |
taxon_superfamily_name | character varying | YES | The taxon's superfamily. |
taxon_superorder_name | character varying | YES | The superorder of the taxon. |
taxon_supertribe_name | character varying | YES | The supertribe of the taxon. |
taxon_tribe_name | character varying | YES | The tribe of the taxon. |
taxon_variety_name | character varying | YES | The variety name of the taxon. |
updated_at | timestamp with time zone | YES | The time of update of the record. |
url | text | YES | A URL pointing to the online record. |
user_id | integer | YES | The ID of the iNaturalist user. |
user_login | character varying | YES | The login name of the iNaturalist user. |
Schema: intertidal
Stores data for the intertidal database. Contains information about UAS flights, surveys and other derivative data.
Table: quadrat
A record of observations within an intertidal quadrat.
Name | Type | Nullable | Documentation |
---|---|---|---|
centroid_height | real | YES | The elevation of the corner position in m (ellipsoidal). |
centroid_lat | double precision | YES | The corner latitude. |
centroid_lon | double precision | YES | The corner longitude. |
corner_height1 | real | YES | The elevation of the corner position in m (ellipsoidal). |
corner_height2 | real | YES | The elevation of the corner position in m (ellipsoidal). |
corner_height3 | real | YES | The elevation of the corner position in m (ellipsoidal). |
corner_height4 | real | YES | The elevation of the corner position in m (ellipsoidal). |
corner_lat1 | double precision | YES | The corner latitude. |
corner_lat2 | double precision | YES | The corner latitude. |
corner_lat3 | double precision | YES | The corner latitude. |
corner_lat4 | double precision | YES | The corner latitude. |
corner_lon1 | double precision | YES | The corner longitude. |
corner_lon2 | double precision | YES | The corner longitude. |
corner_lon3 | double precision | YES | The corner longitude. |
corner_lon4 | double precision | YES | The corner longitude. |
created_on | timestamp without time zone | NO | created_on = models.DateTimeField(default = datetime.utcnow, null = False) |
flag_number | character varying | NO | The number of the flag used to mark the quadrat. May be non-numeric if necessary. |
geom | geometry | YES | A geometry representing either the corners of the quadrat (multipoint) or the centroid. |
name | character varying | NO | A unique name for the quadrat. |
note | text | YES | An optional note about the quadrat. |
survey_id | integer | NO | A reference to the intertidal suvey. |
time | timestamp without time zone | NO | The time of the quadrat (UTC). |
transect | integer | NO | The transect number. This is an integer corresponding to whatever rubric was chosen for the survey. |
zone | integer | NO | The zone number. This is an integer corresponding to whatever rubric was chosen for the survey. |
Table: quadrat_observation
Records observations within quadrats. The observation type enumeration is defined in the database export panel.
Name | Type | Nullable | Documentation |
---|---|---|---|
cell | integer | YES | The quadrat cell, typically from 0 - 100. |
count | integer | YES | The number of organisms observed. |
coverage | real | YES | The percent coverage by the organism, 0-100. TODO: Not part of the specification. |
length | real | YES | The measured length of an organism. |
quadrat_id | integer | NO | A link to the quadrat. |
surveytaxon_id | integer | NO | A link to the survey taxon. |
type | integer | YES | The type of observation; one of quadrat_obs_type. |
Table: quadrat_substrate
List substrates observed in intertidal quadrats.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The name of the substrate. |
Table: quadrat_substrate_coverage
Associates quadrat substrate coverages with quadrats.
Name | Type | Nullable | Documentation |
---|---|---|---|
coverage | real | NO | The coverage amount between 0% and 100%. |
quadrat_id | integer | NO | A reference to the quadrat. |
substrate_id | integer | NO | A reference to the substrate. |
Table: survey
Stores metadata about intertidal surveys.
Name | Type | Nullable | Documentation |
---|---|---|---|
created_on | timestamp without time zone | NO | The time of creation of this record. |
end_date | timestamp without time zone | YES | The end date of the survey. |
name | character varying | NO | The name of the survey. |
note | text | YES | Notes about the survey. |
objective | text | YES | The survey objective. |
site_id | integer | YES | Relates the survey to a site entity. |
start_date | timestamp without time zone | NO | The start date of the survey. |
Table: survey_crew
Associates intertidal survey crew members with roles.
Name | Type | Nullable | Documentation |
---|---|---|---|
note | text | YES | An optional note about the survey role. |
person_id | integer | NO | A reference to the person with this role. |
role_id | integer | NO | A reference to the role. |
survey_id | integer | NO | A reference to the survey. |
Table: survey_role
A list of roles available to intertidal survey crew members.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The name of the role. |
note | text | YES | A note about the role. |
Table: survey_taxon
Links taxon records to intertidal surveys.
Name | Type | Nullable | Documentation |
---|---|---|---|
survey_id | integer | NO | A link to the survey. |
taxon_id | integer | NO | A link to the taxon record. |
Schema: ndst
Contains tables that store transient data loaded from the Dive Logging App so it can be loaded into the ROV database.
Table: cruise
None
Name | Type | Nullable | Documentation |
---|---|---|---|
active | boolean | NO | [None] |
created_on | timestamp without time zone | NO | [None] |
leg | text | YES | [None] |
name | text | YES | [None] |
note | text | YES | [None] |
objective | text | YES | [None] |
row_id | text | YES | [None] |
status | character varying | YES | [None] |
summary | text | YES | [None] |
updated_on | timestamp without time zone | NO | [None] |
Table: diveconfig
None
Name | Type | Nullable | Documentation |
---|---|---|---|
active | boolean | NO | [None] |
name | text | YES | [None] |
note | text | YES | [None] |
row_id | text | YES | [None] |
ship_config | text | YES | [None] |
ship_instrument_configs | text | YES | [None] |
sub_config | text | YES | [None] |
sub_instrument_configs | text | YES | [None] |
Table: dives
None
Name | Type | Nullable | Documentation |
---|---|---|---|
active | boolean | NO | [None] |
cruise_name | text | YES | [None] |
dive_config | text | YES | [None] |
end_time | text | YES | [None] |
leg | text | YES | [None] |
name | text | YES | [None] |
note | text | YES | [None] |
objective | text | YES | [None] |
pilot | text | YES | [None] |
row_id | text | YES | [None] |
site_name | text | YES | [None] |
start_time | text | YES | [None] |
summary | text | YES | [None] |
Table: equipconfig
None
Name | Type | Nullable | Documentation |
---|---|---|---|
active | boolean | NO | [None] |
configuration | text | YES | [None] |
name | text | YES | [None] |
note | text | YES | [None] |
row_id | text | YES | [None] |
short_code | text | YES | [None] |
type | text | YES | [None] |
Table: equipment
None
Name | Type | Nullable | Documentation |
---|---|---|---|
active | boolean | NO | [None] |
brand | text | YES | [None] |
instrument_id | integer | YES | [None] |
model | text | YES | [None] |
note | text | YES | [None] |
platform_id | integer | YES | [None] |
row_id | text | YES | [None] |
serial_number | text | YES | [None] |
short_code | text | YES | [None] |
short_code_mapped | character varying | YES | An optional mapping from the origin short code to an internal short code. Not unique because multiple source items can map to a single internal item. |
type | text | YES | [None] |
Table: people
None
Name | Type | Nullable | Documentation |
---|---|---|---|
active | boolean | NO | [None] |
text | YES | [None] | |
first_name | text | YES | [None] |
initials | text | YES | [None] |
last_name | text | YES | [None] |
person_id | integer | YES | [None] |
row_id | text | YES | [None] |
Table: transects
None
Name | Type | Nullable | Documentation |
---|---|---|---|
active | boolean | NO | [None] |
cruise_name | text | YES | [None] |
dive_name | text | YES | [None] |
end_time | text | YES | [None] |
leg | text | YES | [None] |
name | text | YES | [None] |
note | text | YES | [None] |
objective | text | YES | [None] |
row_id | text | YES | [None] |
start_time | text | YES | [None] |
summary | text | YES | [None] |
Schema: obis
Stores data from the OBIS taxonomy and observation database.
Table: taxon
OBIS taxonomy records imported directly from the OBIS observation data, but filtered for uniqueness on the Aphia ID.
Name | Type | Nullable | Documentation |
---|---|---|---|
aphiaid | integer | NO | [None] |
class | character varying | YES | [None] |
domain | character varying | YES | [None] |
family | character varying | YES | [None] |
forma | character varying | YES | [None] |
genus | character varying | YES | [None] |
gigaclass | character varying | YES | [None] |
infraclass | character varying | YES | [None] |
infrakingdom | character varying | YES | [None] |
infraorder | character varying | YES | [None] |
infraphylum | character varying | YES | [None] |
kingdom | character varying | YES | [None] |
megaclass | character varying | YES | [None] |
natio | character varying | YES | [None] |
order | character varying | YES | [None] |
organismname | character varying | YES | [None] |
originalscientificname | character varying | YES | [None] |
parvorder | character varying | YES | [None] |
parvphylum | character varying | YES | [None] |
phylum | character varying | YES | [None] |
phylum_division | character varying | YES | [None] |
scientificname | character varying | NO | [None] |
section | character varying | YES | [None] |
series | character varying | YES | [None] |
species | character varying | YES | [None] |
subclass | character varying | YES | [None] |
subfamily | character varying | YES | [None] |
subforma | character varying | YES | [None] |
subgenus | character varying | YES | [None] |
subkingdom | character varying | YES | [None] |
suborder | character varying | YES | [None] |
subphylum | character varying | YES | [None] |
subphylum_subdivision | character varying | YES | [None] |
subsection | character varying | YES | [None] |
subspecies | character varying | YES | [None] |
subterclass | character varying | YES | [None] |
subtribe | character varying | YES | [None] |
subvariety | character varying | YES | [None] |
superclass | character varying | YES | [None] |
superdomain | character varying | YES | [None] |
superfamily | character varying | YES | [None] |
superorder | character varying | YES | [None] |
supertribe | character varying | YES | [None] |
taxonid | character varying | YES | [None] |
taxonomicstatus | character varying | YES | [None] |
taxonrank | character varying | YES | [None] |
taxonremarks | text | YES | [None] |
tribe | character varying | YES | [None] |
type | character varying | YES | [None] |
variety | character varying | YES | [None] |
vernacularname | character varying | YES | [None] |
Schema: pa
Stores data about protected areas, including RCAs, parks, etc.
Table: mpa
None
Name | Type | Nullable | Documentation |
---|---|---|---|
aichi_t11 | character varying | YES | [None] |
biome | character varying | YES | [None] |
comments | character varying | YES | [None] |
delisdate | integer | YES | [None] |
geom | geometry | YES | [None] |
geometry | USER-DEFINED | YES | [None] |
gov_type | character varying | YES | [None] |
iucn_cat | character varying | YES | [None] |
jur_id | character varying | YES | [None] |
legisl_e | character varying | YES | [None] |
legisl_f | character varying | YES | [None] |
loc_e | character varying | YES | [None] |
loc_f | character varying | YES | [None] |
mgmt_e | character varying | YES | [None] |
mgmt_f | character varying | YES | [None] |
name_e | character varying | YES | [None] |
name_f | character varying | YES | [None] |
name_ind | character varying | YES | [None] |
o_area | double precision | YES | [None] |
objectid | bigint | NO | [None] |
oecm | character varying | YES | [None] |
ogc_fid | integer | NO | [None] |
owner_e | character varying | YES | [None] |
owner_f | character varying | YES | [None] |
parent_id | integer | YES | [None] |
protdate | integer | YES | [None] |
shape_area | double precision | YES | [None] |
shape_length | double precision | YES | [None] |
status_e | character varying | YES | [None] |
status_f | character varying | YES | [None] |
subs_right | character varying | YES | [None] |
type_e | character varying | YES | [None] |
type_f | character varying | YES | [None] |
url | character varying | YES | [None] |
zone_id | integer | YES | [None] |
zonedesc_e | character varying | YES | [None] |
zonedesc_f | character varying | YES | [None] |
Table: mpa_data_object
A data object related to an MPA. This is the abstract, or top-level object, which may contain one or more physical objects (files) or documents.
Name | Type | Nullable | Documentation |
---|---|---|---|
created_on | timestamp without time zone | NO | The time of creation of the record. |
description | text | YES | A description of the object. |
doi | character varying | YES | The DOI of the object. |
isbn | character varying | YES | The ISBN of the object. |
mpa_id | integer | NO | A reference to the MPA record. |
name | character varying | NO | The name of the data object. |
updated_on | timestamp without time zone | NO | The last update time of the object. |
Table: mpa_data_object_file
Gives the ability for more than one data object to own a file and vice versa.
Name | Type | Nullable | Documentation |
---|---|---|---|
data_object_id | integer | NO | A reference to the data object. |
file_id | integer | NO | A reference to the file. |
Table: protected_area
A unified table for protected areas.
Name | Type | Nullable | Documentation |
---|---|---|---|
area | real | YES | The nominal area in sq. km of the protected area. |
geom | geometry | NO | The boundary geometry of the protected area. |
name_e | character varying | NO | The English name of the protected area. |
name_f | character varying | NO | The French name of the protected area. |
note | text | YES | An optional note about the protected area. |
original_id | integer | YES | The original ID of the protected area, from the source database. |
type | character varying | NO | The type of protected area, including "RCA", "MPA", etc. |
year_created | integer | YES | The year of creation of the protected area. |
Table: protected_area_data_object
A data object related to an RCA. This is the abstract, or top-level object, which may contain one or more physical objects (files) or documents.
Name | Type | Nullable | Documentation |
---|---|---|---|
created_on | timestamp without time zone | NO | The time of creation of the record. |
description | text | YES | A description of the object. |
doi | character varying | YES | The DOI of the object. |
isbn | character varying | YES | The ISBN of the object. |
name | character varying | NO | The name of the data object. |
protected_area_id | integer | YES | A link to the protected area. |
updated_on | timestamp without time zone | NO | The last update time of the object. |
Table: protected_area_data_object_file
Gives the ability for more than one data object to own a file and vice versa.
Name | Type | Nullable | Documentation |
---|---|---|---|
data_object_id | integer | NO | A reference to the data object. |
file_id | integer | NO | A reference to the file. |
Table: rca
Stores basic information about RCAs along with a boundary geometry.
Name | Type | Nullable | Documentation |
---|---|---|---|
area | real | YES | The area of the RCA (from source). |
description | text | YES | A description of the RCA. |
formerid | smallint | YES | The former ID of the RCA (from source). |
geom | geometry | YES | The RCA geography. |
hectares | real | YES | The number of hectares in the RCA (from source). |
len | real | YES | The length (?) of the RCA (from source). |
name | character varying | YES | The name of the RCA. |
rca_id | double precision | YES | The RCA ID as defined by the provider. |
sq_km | real | YES | The area of the RCA (from source). |
yr_created | smallint | YES | The year the RCA was created (from source). |
Schema: rov
The main schema of the ROV database, stores information about all relevant entities including cruise metadata, personnel, dives, transects, equipment configuration, telemetry, water properties and observations.
Table: abundance
Stores a list of abundance labels from the ACFOR scale.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | A descriptive label for the abundance level. |
note | text | YES | An optional note about the abundance level. |
rank | integer | YES | A rank used to objectively interpret the abundance label as an ordinal rank. It doesn't matter what the values are so long as they increase monotonically and do not overlap. |
short_code | character varying | NO | Contains a short code that can be used to look up an abundance (e.g., during import) without relying on the primary key. |
source | character varying | NO | A source label to distinguish abundance labels with the same names. TODO: Provisional, pending determination of how abundance codes are handled. |
Table: annotation_protocol
A table to record annotation protocols for annotation projects. Stores information such as the author of the protocol, the observation interval, the medium used and the types of observations to be made.
Name | Type | Nullable | Documentation |
---|---|---|---|
algae_species | character varying | YES | Whether all algae species are identified: "all", "subset" or "none". |
annotation_software_id | integer | NO | Software used to annotate the video or images. Looked up in the annotation software table. |
biogenic_habitat | boolean | YES | True if any habitat categories include fauna (e.g., sponge reefs). |
created_on | timestamp without time zone | NO | The date of creation of this record. |
creator_id | integer | YES | Records the identity of the person who created this protocol. |
fish_species | character varying | YES | Whether all fish species are identified: "all", "subset" or "none". |
fov_interval | real | YES | [None] |
fov_interval_unit | character varying | NO | A unit for the field of view interval, such as 's' for seconds or 'm' for metres. |
habitat_interval | real | YES | [None] |
habitat_interval_unit | character varying | NO | A unit for the habitat interval, such as 's' for seconds or 'm' for metres. |
habitat_only | boolean | YES | Set to true if only habitat variables were recorded. |
image_interval | real | YES | The interval between images or frame grabs. Typically 3 to 10 seconds. |
image_interval_unit | character varying | NO | A unit for the image interval, such as 's' for seconds or 'm' for metres. |
image_overlap | boolean | YES | Is there overlap between the images (true) or do they represent independent non-overlapping space (false). |
invertebrate_species | character varying | YES | Whether all invertebrate species are identified: "all", "subset" or "none". |
is_template | boolean | NO | If this is meant to be a template that is copied for use, mark this column `true`. |
medium_type_id | integer | NO | The type of media used for annotation. Looked up in media type table. |
name | character varying | NO | The name of the annotation protocol. Should be unique. |
note | text | YES | If only a subset of invert, fish or algae species were identified, note here which groups were the primary focus (target species). For example, benthic fish or corals and sponges. Also for other notes of interest. |
observation_interval | real | YES | [None] |
observation_interval_unit | character varying | NO | A unit for the observation interval, such as 's' for seconds or 'm' for metres. |
protocol_document | character varying | YES | Link, title, DOI, etc. of a document describing the protocol in full. |
species_guide | character varying | YES | A URL to the iNaturalist species guide that was used for annotation. |
updated_on | timestamp without time zone | NO | The date of update of this record. |
Table: annotation_protocol_document
Contains documents related to an annotation protocol. These can be a URL or actual file data. If the file data are present in the database, the URL can still be used to provide the origin of the file, etc.
Name | Type | Nullable | Documentation |
---|---|---|---|
annotation_protocol_id | integer | NO | A reference to the annotation protocol. |
created_on | timestamp without time zone | NO | The time of creation of the record. |
file_name | character varying | YES | The original name of the document file. |
file_type | character varying | YES | The mime type of the file. Required if file data are given. |
note | text | YES | An optional note about the document. |
title | character varying | NO | The title of the document. |
updated_on | timestamp without time zone | NO | The time of update of the record. |
url | character varying | YES | An optional URL for the document. If file data are not given, this field is required. |
Table: annotation_software
The software used for annotation.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The name of the annotation software. |
note | text | YES | An optional note about the annotation software. |
short_code | character | NO | Provides a short code for looking up the entity. |
Table: biocover
A lookup table listing the available biocover types for the habitat_event table. TODO: This table may be altered to provide a hierarchical list of types with increasing specificity. TODO: Should perhaps refer to the taxon table.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The name of the biocover. |
note | text | YES | An optional description of the biocover. |
short_code | character varying | NO | Contains a short code that can be used to look up a biocover (e.g., during import) without relying on the primary key. |
Table: comment_event
Provides a way to record comments rather than using a sparse note field on the event.
Name | Type | Nullable | Documentation |
---|---|---|---|
event_id | integer | NO | A reference to the parent event. |
note | text | YES | A text comment or note. |
Table: complexity
A lookup table listing the available habitat complexity types for the habitat_event table. TODO: This table may be altered to provide a hierarchical list of types with increasing specificity.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The name of the complexity type. |
note | text | YES | An optional description of the complexity type. |
short_code | character varying | NO | Contains a short code that can be used to look up a complexity (e.g., during import) without relying on the primary key. |
Table: coverage
A lookup table listing the percentage of coverage for the habitat_event table. The coverages are given as ranges so the text of the level is given in the name field and the values in the min and max fields contain the bounding values.
Name | Type | Nullable | Documentation |
---|---|---|---|
maximum | real | NO | The minimum value in the range. |
minimum | real | NO | The minimum value in the range. |
name | character varying | NO | A characterization of percent coverage. Presented as a range of percentages. |
note | text | YES | An optional note about the coverage percentage. |
short_code | character varying | NO | Contains a short code that can be used to look up a coverage (e.g., during import) without relying on the primary key. |
Table: cruise
Cruise legs occur within a cruise and are assigned specific crews, scientific programs, etc. A single leg can cover an entire cruise, legs can be spaced end-to-end, or can theoretically overlap. Legs can share members or equipment, and crew members can have specific roles related to a leg. Cruise legs can be created without a scientific program or a cruise because imported data sets may only list the name of the cruise and not indicate whether it was part of a longer cruise.
Name | Type | Nullable | Documentation |
---|---|---|---|
approved | integer | NO | If zero, the record is not approved and should not be shown or used. Currently a non-zero value represents approval, but may be expanded to various levels of approval in the future. |
created_on | timestamp without time zone | NO | The date of creation of this record. |
end_time | timestamp without time zone | YES | The end time of the leg. |
leg | integer | NO | Cruise legs are numbered from 1. |
name | character varying | NO | A name for this leg of the cruise. |
note | text | YES | Notes about the cruise. |
objective | text | YES | A statement of the operational or scientific objectives of the cruise. |
planned_track | USER-DEFINED | YES | A multilinestring containing the planned track of the leg. TODO: Not known whether this is necessary. |
ship_id | integer | NO | [None] |
start_time | timestamp without time zone | NO | The start time of the leg. |
summary | text | YES | A summary of the cruise, with information about whether the objectives were met and any other pertinent information. |
updated_on | timestamp without time zone | NO | The date of update of this record. |
Table: cruise_crew
Associates crew members with a cruise leg and their roles. A crew member can have multiple roles. Note: these roles are distinct from members of programs, such as Chief Scientist. It may be necessary to revisit this structure or the division of roles.
Name | Type | Nullable | Documentation |
---|---|---|---|
cruise_id | integer | NO | Reference to the cruise leg to which the member is assigned. |
cruise_role_id | integer | NO | Reference to the cruise role. |
note | text | YES | An optional note about the crew member. |
person_id | integer | NO | Reference to the person on the crew. |
Table: cruise_document
None
Name | Type | Nullable | Documentation |
---|---|---|---|
created_on | timestamp without time zone | NO | [None] |
cruise_id | integer | NO | [None] |
file_name | character varying | YES | [None] |
file_type | character varying | YES | [None] |
note | text | YES | [None] |
title | character varying | NO | [None] |
updated_on | timestamp without time zone | NO | [None] |
url | character varying | YES | [None] |
Table: cruise_fn_contact
A table for associating First Nations contacts with a cruise.
Name | Type | Nullable | Documentation |
---|---|---|---|
contact_name | character varying | NO | The full name of the contact. |
cruise_id | integer | NO | A reference to the cruise. |
character varying | YES | The email address of the contact. | |
nation | text | YES | The name of the nation or group represented by the contact. |
note | text | YES | A note about the contact. |
phone | character varying | YES | The phone number of the contact. |
Table: cruise_library
This table creates an association between a cruise leg and documents in the library that may be relevant to its research objectives.
Name | Type | Nullable | Documentation |
---|---|---|---|
cruiseleg_id | integer | NO | A reference to the cruise leg. |
library_id | integer | NO | A reference to the document in the library. |
note | text | YES | An optional note about the document with respect to the cruise leg. May be used to reference points of interest in a paper, etc. |
Table: cruise_program
A table to link programs and cruises. A cruise can be under the auspices of more than one program or none.
Name | Type | Nullable | Documentation |
---|---|---|---|
cruise_id | integer | NO | The ID of a cruise. |
program_id | integer | NO | The ID of a program. |
Table: cruise_role
A lookup table of roles available to members of cruise leg crews.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The name of the role. |
note | text | YES | An optional note about the role and its responsibilities. |
short_code | character varying | NO | [None] |
Table: disturbance
Provides a nominal level of disturbance for habitat events.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The textual representation of the disturbance level. |
note | text | YES | A note about the disturbance level. |
short_code | character varying | NO | Contains a short code that can be used to look up a disturbance (e.g., during import) without relying on the primary key. |
Table: dive
A dive is what an ROV does. The dive has a start and end time (not necessarily submerge/resurface), a crew and possibly a name. Transects occur during dives.
Name | Type | Nullable | Documentation |
---|---|---|---|
attributes | jsonb | YES | A JSON column used for recording structured attributes that do not fit with the regular table structure. |
created_on | timestamp without time zone | NO | The date of creation of this record. |
cruise_id | integer | NO | A reference to the cruise leg during which the dive was performed. |
end_time | timestamp without time zone | NO | The end of the dive. |
name | character varying | NO | A name for the dive. |
note | text | YES | An optional note about the dive. |
objective | text | YES | A statement of the practical or research objectives for this dive. |
ship_config_id | integer | NO | A reference to the platform config for the ship. |
site_id | integer | YES | Optional reference to the geographic site of the dive. |
start_time | timestamp without time zone | NO | The start of the dive. Not necessarily the time the vehicle is placed in the water. |
sub_config_id | integer | NO | A reference to the platform config for the submersible or ROV. |
summary | text | YES | A summary of the dive; whether objectives were met, problems encountered, etc. |
updated_on | timestamp without time zone | NO | The date of update of this record. |
Table: dive_crew
Assigns roles to dive crew members. Crew members are selected from the person.
Name | Type | Nullable | Documentation |
---|---|---|---|
dive_id | integer | NO | The dive to which the crew member is assigned. |
dive_role_id | integer | NO | A reference to the dive role. |
note | text | YES | An optional note about the crew member. |
person_id | integer | NO | A reference to the person. |
Table: dive_role
A list of roles available to crew members on a dive via the dive_crew table.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The name of the role. |
note | text | YES | An optional description of the role. |
short_code | character varying | NO | [None] |
Table: equipment_type
This is a lookup table to provide the names of types of equipment for the model_equipment_type table, e.g., "Digital Still Camera", "Thermometer," "ROV," etc.
Name | Type | Nullable | Documentation |
---|---|---|---|
category | USER-DEFINED | YES | An enumeration column identifying the equipment as platform, instrument or some other type. |
name | character varying | NO | A name for the equipment type. |
note | text | YES | An optional note about the equipment type. |
short_code | character varying | NO | A short code for referencing the equipment type in import documents. |
Table: event
The event table is a parent or abstract table that provides properties common to habitat, observation, status and measurement events. Each of those event records must have a reference to one event record. Conceptually, the event row and its child entity row are considered to be one object.
Name | Type | Nullable | Documentation |
---|---|---|---|
attributes | jsonb | YES | [None] |
created_on | timestamp without time zone | NO | The time of creation of this record. |
dive_id | integer | NO | A reference to a dive. |
end_time | timestamp without time zone | YES | The end time of the event. If the event is instantaneous, this field is null. |
frames | jsonb | YES | Preserves the frames from the Biigle annotation. |
medium_filename | character varying | YES | The name of the media file from which this event is derived. |
medium_id | integer | YES | A reference to the medium on which this annotation was created. |
original_id | integer | NO | The original ID of the event if read from a source that has IDs. |
original_labels | jsonb | YES | Optionally stores information about annotation labels used to generate this event. For Biigle, stores the label IDs. |
shape | jsonb | YES | The shape used for annotations (e.g., Biigle). The coordinates for this shape should have been converted to metres. GeoJSON doesn't support circles, so we use a format that encapsulates the shape type and the raw list of coordinates, something like: { "type": "circle", "shape": [1.0, 1.0, 1.0] } which is a circle with radius 1 at position 1,1. A whole-screen annotation will be a rectangle the size of the screen. |
shape_area | double precision | YES | The area of the shape in square metres, calculated from the screenand shape dimensions and the laser point distance. |
start_time | timestamp without time zone | NO | The time at the start of the event. If the event is instantaneous, this is the time at which it occurred. |
tags | jsonb | YES | Stores a list of tags relevant to this event. |
transect_id | integer | YES | An optional reference to a transect. TODO: Clarify the use of transects and whether this column is nullable. |
Table: event_logger
This table tracks the people who contributed to annotation, which may be composed of a number of separate labels.
Name | Type | Nullable | Documentation |
---|---|---|---|
event_id | integer | NO | The event that was created from the annotation(s). |
person_id | integer | NO | A person who contributed to the annotation. |
Table: habitat_event
The habitat event table records information about the biocover, thickness, substrate and complexity of the habitat. Some survey protocols record the taxon with biocover observations. A reference to the taxon table is provided. This table is a realization of the event table.
Name | Type | Nullable | Documentation |
---|---|---|---|
annotation_protocol_id | integer | NO | A reference to the annotation protocol used in the creation of this observation. |
biocover_coverage_id | integer | YES | A coverage value for the biocover. |
biocover_id | integer | YES | A reference to the biocover lookup. |
complexity_id | integer | YES | A reference to the habitat complexity lookup. |
disturbance_id | integer | YES | Indicates the level of disturbance of the substrate and/or biota. |
event_id | integer | NO | A reference to the parent event. |
relief_id | integer | YES | A reference to the relief level. |
substrate_coverage_id | integer | YES | A coverage value for the substrate. |
substrate_id | integer | YES | A reference to the dominant substrate lookup. |
taxon_id | integer | YES | A reference to the taxon used in the creation of this observation. |
thickness_id | integer | YES | A reference to the biocover thickness lookup. |
Table: image_quality
Nominal image quality levels, originally used by VideoMiner but applicable to new records.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | A name for the quality level. |
note | text | YES | An optional note. |
rank | integer | YES | An ordinal rank (zero is high) for the quality level. |
short_code | character varying | NO | Contains a short code that can be used to look up an image quality (e.g., during import) without relying on the primary key. |
Table: import_queue_annotator
Stores the import packages created by annotators.
Name | Type | Nullable | Documentation |
---|---|---|---|
created_on | timestamp without time zone | NO | The date of creation of the record. |
cruise_name | character varying | NO | The name of the cruise to which this record is linked. A lookup is not used because the cruise record may not have been created yet. |
data | jsonb | NO | Stores the JSON representation of the import job. |
mseauser_id | integer | NO | A reference to the MSEA user that created the record. |
name | character varying | NO | A unique name for the import queue record. |
note | text | YES | An optional note about the import package. |
status | character varying | YES | A short description of the processing status of the job. |
updated_on | timestamp without time zone | NO | The date of update of the record. |
Table: import_queue_annotator_label_map_prefill
A table to store the last configured tags and values for labels. Provides pre-filling in label mapping application.
Name | Type | Nullable | Documentation |
---|---|---|---|
created_on | timestamp without time zone | YES | [None] |
label_tree_name | character varying | YES | The label tree name. If given identifies the label uniquely with the tree name. |
name | character varying | NO | The text of the label. |
tag_data | jsonb | NO | The the tag data. |
tags | jsonb | NO | The list of tags. |
updated_on | timestamp without time zone | YES | [None] |
Table: import_queue_pi
Stores the import packages created by principal investigators.
Name | Type | Nullable | Documentation |
---|---|---|---|
created_on | timestamp without time zone | NO | The date of creation of the record. |
cruise_id | integer | NO | A reference to the cruise. |
mseauser_id | integer | NO | A reference to the MSEA user that created the record. |
name | character varying | NO | A name of the import job |
note | text | YES | An optional note about the import package. |
status | character varying | YES | A short description of the processing status of the job. |
updated_on | timestamp without time zone | NO | The date of update of the record. |
Table: instrument
This table represents instruments, which are concrete instances of the types represented in the model table. instruments tend to be things that generate data, be it a thermometer or a camera. For the purposes of this database, an instrument is anything that is not a platform.
Name | Type | Nullable | Documentation |
---|---|---|---|
attributes | jsonb | YES | A freeform list of attributes for this instrument. |
created_on | timestamp without time zone | NO | The date of creation of this record. |
model_id | integer | NO | A reference to the instrument model. |
note | text | YES | An optional note about this instrument. |
organisation_id | integer | NO | A reference to the organisation that owns and operates the instrument. |
retired | date | YES | If the instrument is retired, this records the retirement date. If null, the instrument is assumed to be active. |
serial_number | character varying | NO | The serial number of the instrument. If a serial number is not available, some unique identifier can be substituted. No two instruments of the same model may have the same serial number. |
short_code | character varying | NO | Contains a short code that can be used to look up an instrument (e.g., during import) without relying on the primary key. |
updated_on | timestamp without time zone | NO | The date of update of this record. |
Table: instrument_config
This table records the configuration of an instrument, including settings and its spatial relationship to a parent entity -- another configured vehicle or instrument upon which this instrument is mounted.
Name | Type | Nullable | Documentation |
---|---|---|---|
configuration | jsonb | YES | Configuration information about the instrument config. |
created_on | timestamp without time zone | NO | The date of creation of this record. |
instrument_id | integer | NO | Reference to the instrument targeted by the configuration. |
name | character varying | NO | The name of the instrument config. Need not be unique: used to identify the config within the platform config. |
note | text | YES | An optional note about this configuration. |
platform_config_id | integer | YES | [None] |
updated_on | timestamp without time zone | NO | The date of update of this record. |
Table: measurement
This table preserves time-stamped measurements generated by instruments. Each measurement has an associated quantity and unit. TODO: At this point multi-part measurement would be stored separately as the ability to store vectors isn't universal across DBMSes.
Name | Type | Nullable | Documentation |
---|---|---|---|
instrument_config_id | integer | NO | The configured instrument used to generate this item. |
is_modelled | boolean | NO | A flag to indicate whether the value is derived from measurements by some modelling process. |
measurement_type_id | integer | NO | A reference to the type of this measurement. |
quantity | real | NO | The scalar quantity or magnitude of the measurement. |
signal_quality | real | YES | A quality of the measurement as reported by the instrument. TODO: Requires clarification. |
timestamp | timestamp without time zone | NO | The time that the measurement was recorded. |
Table: measurement_event
A table for storing human-created measurements.
Name | Type | Nullable | Documentation |
---|---|---|---|
event_id | integer | NO | A reference to the parent event. |
measurement_type_id | integer | NO | A reference to the measurement type. |
quantity | real | NO | The measurement value in the measurement type unit. |
Table: measurement_type
This is a lookup table of types of measurement types for the measurement or measurement_event tables. This is fairly open-ended and can represent something like salinity with a specific unit, be it the SI unit or a discipline-specific unit. It is provided to allow users to easily select units for a measurement category when importing data.
Name | Type | Nullable | Documentation |
---|---|---|---|
maximum | real | YES | An optional upper bound on the value of the measurement. Null implies no limit. |
minimum | real | YES | An optional lower bound on the value of the measurement. Null implies no limit. |
name | character varying | NO | The type of measurement. E.g., "Density" or "Salinity." |
note | text | YES | An optional note about this measurement type. |
short_code | character varying | NO | Contains a short code that can be used to look up a measurement type (e.g., during import) without relying on the primary key. |
unit | character varying | NO | The unit. SI units are preferred but not required. The unit selection should probably depend on field-specific idiomatic or cultural preferences. |
Table: medium
This table represents videos, photographs or other media. It may store acoustic data in the future. The table is designed with a hierarchical structure to allow the preservation of links between original data and derived (i.e., cropped or otherwise modified) data. For example, if a ten minute segment of a one hour video is extracted, the new segment can be stored with a reference to its parent.
Name | Type | Nullable | Documentation |
---|---|---|---|
biigle_video_id | integer | YES | Provisional: the ID of the video as represented in Biigle.de. |
created_on | timestamp without time zone | NO | The date of creation of this record. |
file_id | integer | NO | A required reference to the file. |
geostamp_source_id | integer | YES | The source of the geostamp information on photo or video data. Refers to an instrument, e.g., a GPS. |
height | integer | YES | The height of the video frame in pixels. |
instrument_config_id | integer | NO | An instrument configuration for the instrument that generated this media. |
length | real | YES | If a video or acoustic file (etc.), the length in seconds. |
medium_format_id | integer | YES | A reference to the format of the data. |
note | text | YES | An optional note about this data. |
parent_id | integer | YES | If the video or photo is derived or cropped from another, this refers to the source record (optional). |
source_time | real | YES | If this is an extract from a video or a frame grab, indicates the zero-based start time with respect to the source video. |
start_time | timestamp without time zone | NO | The start date/time of this video, or the instantaneous time if it's a photograph. |
thumbnail | bytea | YES | An optional thumbnail to represent the stored medium. |
width | integer | YES | The width of the video frame in pixels. |
Table: medium_format
A lookup table for data formats for the medium table. This will include things like video, photo and acoustic. TODO: To be determined whether medium formats should be discriminated more granularly than this. TODO: Should maybe be categorised into video/photo/acoustic/etc. along with things like the format (JPG, MP4, AAC) and possibly more specific encoding parameters.
Name | Type | Nullable | Documentation |
---|---|---|---|
extensions | jsonb | NO | A list of file extensions that correspond to this medium type. For example, the JPEG image type may have extensions "jpg" or "jpeg" in any case. This list will help applications guess the correct format of a file if it isn't known. Note that more than one media type can have the same extension, so this feature doesn't provide a guaranteed one-to-one mapping. Use it only as a guide. |
medium_type_id | integer | NO | A reference to the medium type (e.g., video or photo). |
name | character varying | NO | The name of the format. |
note | text | YES | An optional note about the format. |
short_code | character varying | NO | Contains a short code that can be used to look up a medium format (e.g., during import) without relying on the primary key. |
Table: medium_type
A simple lookup to provide media types to the annotation_protocol table. These are not specific media formats (as stored in medium_format), but provided a higher-level distinction.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The name of the media type. |
note | text | YES | An optional note about the media type. |
short_code | character | NO | Provides a short code for looking up the entity. |
Table: model
This table records the brand and model of equipment in the inventory. This data is abstract, which is to say, there is only one record for "GoPro Hero4" but there will be one record for each concrete instance of the model in the instrument table. The model table stores both instruments and platforms.
Name | Type | Nullable | Documentation |
---|---|---|---|
attributes | jsonb | YES | A free-form JSON field for attributes of this model. |
brand_name | character varying | NO | The brand name. |
equipment_type_id | integer | NO | A reference to the equipment type. |
model_name | character varying | NO | The model name. |
note | text | YES | An optional note about the model. |
Table: model_documentation
This table creates an association between a model and documentation in the library.
Name | Type | Nullable | Documentation |
---|---|---|---|
library_id | integer | NO | A reference to the document in the library. |
model_id | integer | NO | A reference to the model. |
note | text | YES | An optional note about the document with respect to the cruise leg. May be used to reference points of interest in a paper, etc. |
Table: model_equipment_type
This table associates an equipment model with the abstract type of equipment it represents. For example, a GoPro Hero4 is both a digital video camera and a digital still camera. These values are taken from the equipment_type table.
Name | Type | Nullable | Documentation |
---|---|---|---|
equipment_type_id | integer | NO | The equipment type ID. |
model_id | integer | NO | A reference to the model. |
Table: observation_category
Observation categories are used by annotation protocols.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The name of the observation category. |
note | text | YES | An optional note about the observation category. |
short_code | character varying | NO | A short code for referencing the observation category in import documents. |
Table: observation_confidence
Provides a nominal observation confidence level for observation events.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The textual representation of the confidence level. |
note | text | YES | A note about the confidence level. |
rank | integer | NO | This field is a way of ranking confidence levels so that an ordering can be established. |
short_code | character varying | NO | Contains a short code that can be used to look up a observation confidence (e.g., during import) without relying on the primary key. |
Table: observation_event
An observation event records the occurrence of a phenomenon as a result of observation by a human user, usually by analyzing a video recording or photograph. Observations can relate to a species taxonomy or an element in a label tree. This table will be "sparse," that is, certain a value will be given for only one or two columns in each row, and the interpretation of the values will be informed by the information in the annotation protocol table. This table is a realization of the event table.
Name | Type | Nullable | Documentation |
---|---|---|---|
abundance_id | integer | YES | Link to an abundance level. |
annotation_protocol_id | integer | NO | A reference to the annotation protocol used in the creation of this observation. |
category | character varying | YES | A free-form category label used for distinguishing types of labels within an annotation project. Useful for flagging records for review. |
count | integer | YES | The number of individuals observed. TODO: Interpretation depends on the observation interval as recorded in theannotation protocol table. |
coverage_id | integer | YES | Provides an indication of the amount of a scene covered by an organism. Implies habitat forming. |
event_id | integer | NO | A reference to the parent event. |
observation_confidence_id | integer | YES | References the observation confidence lookup to indicate the user's confidence in the observation. |
taxon_id | integer | YES | A reference to the taxon used in the creation of this observation. |
Table: orientation
This table preserves time-stamped orientation measurements from instruments. These are interpreted according to a specified type. Ship and ROV orientation will be recorded here, though ships don't ordinarily have an orientation.
Name | Type | Nullable | Documentation |
---|---|---|---|
instrument_config_id | integer | NO | The configured instrument used to generate this item. |
is_modelled | boolean | NO | A flag to indicate whether the value is derived from measurements by some modelling process. |
orientation | jsonb | NO | The orientation vector. |
orientation_type_id | integer | NO | A reference to the orientation type of this orientation. |
signal_quality | real | YES | The signal quality of the position as reported by the instrument. TODO: Requires clarification. |
timestamp | timestamp without time zone | NO | The time the position was recorded. |
Table: orientation_type
These entities describe the interpretation of the orientation vector in the instrument_config and orientation tables. This can be a 3- or 4-element vector representing yaw, pitch roll; Tait-Bryan angles; Euler angles or a Quaternion.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The name of the orientation type (e.g., "Quaternion"). |
note | text | YES | An optional note about the orientation type. |
short_code | character varying | NO | Contains a short code that can be used to look up an orientation type (e.g., during import) without relying on the primary key. |
unit | character varying | NO | The units used to interpret the elements of the vector. |
Table: platform
This table maintains the inventory of vehicles, that is, ships and ROVs.
Name | Type | Nullable | Documentation |
---|---|---|---|
attributes | jsonb | YES | A freeform list of attributes for this platform. |
created_on | timestamp without time zone | NO | The date of creation of this record. |
model_id | integer | NO | A reference to the model of the platform. |
name | character varying | NO | The name of the platform. If this is a ship, it might be "CCGS Vector" or if it's an ROV, it might be given an arbitrary name by the maintainer. |
note | text | YES | An optional note about this platform. |
organisation_id | integer | NO | A reference to the organisation that owns and operates the platform. |
retired | date | YES | If the platform is retired, this records the date. If null, the platform is assumed to be active. |
serial_number | character varying | YES | The serial number of the platform. If this is an ROV it will be the manufacturer's serial number. If it's a vessel, this might be the IMO number. In any case, it must be unique. |
short_code | character varying | NO | Contains a short code that can be used to look up a platform (e.g., during import) without relying on the primary key. |
updated_on | timestamp without time zone | NO | The date of update of this record. |
Table: platform_config
This table stores configuration information about vehicles (ships, ROVs,etc.) used for surveys. All references to vehicles are made through this table rather than directly to the platform table, because the disposition of equipment on the platform is essential to understanding how data has been generated, as well as for simple record-keeping purposes.
Name | Type | Nullable | Documentation |
---|---|---|---|
configuration | jsonb | YES | The configuration data as a JSON object. |
created_on | timestamp without time zone | NO | The date of creation of this record. |
note | text | YES | An optional note about the configuration record. |
platform_id | integer | NO | A reference to the platform. |
updated_on | timestamp without time zone | NO | The date of update of this record. |
Table: position
This table preserves time-stamped position measurements from instruments. These can be linear (e.g., UTM) or angular (e.g., Geographic) positions in a specified unit. Ship and ROV positions will be recorded here. TODO: Should this table have a spatial object (point) as well, or be split into two tables, one for absolute georeferenced positions and one for relative positions and orientations?
Name | Type | Nullable | Documentation |
---|---|---|---|
geom | geometry | YES | The point geometry. |
instrument_config_id | integer | NO | The configured instrument used to generate this item. |
is_modelled | boolean | NO | A flag to indicate whether the value is derived from measurements by some modelling process. |
position_type_id | integer | NO | A reference to the position type of this position. |
signal_quality | real | YES | The signal quality of the position as reported by the instrument. TODO: Requires clarification. |
timestamp | timestamp without time zone | NO | The time the position was recorded. |
Table: position_type
Describes the interpretation of a coordinate vector in the position table. These can be geographic or Cartesian positions or orientations and have defined linear or angular units.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The name of the position type. E.g., "Geographic" or "Yaw, Pitch, Roll." |
note | text | YES | An optional note about this position type. |
short_code | character varying | NO | Contains a short code that can be used to look up a position type (e.g., during import) without relying on the primary key. |
unit | character varying | NO | The unit. Linear or angular. E.g., "m" or "radians." |
Table: program
This is a listing of scientific programs that can be associated with cruise legs, and gives information about the governorship and funding of specific research activities. Label trees and taxonomies may be associated with programs, since some trees are relevant to specific research objectives and not others. TODO: Currently a lable tree/taxonomy can only be linked to one program. Need to study further.
Name | Type | Nullable | Documentation |
---|---|---|---|
created_on | timestamp without time zone | NO | The date of creation of this item. |
end_date | date | YES | The optional end date of the program. |
name | character varying | NO | The name of the program. |
note | text | YES | An optional note about the program. |
objective | character varying | YES | The objective or mandate of the program. |
start_date | date | NO | The starting date of the program. |
summary | text | YES | A summary of the program; whether objectives were met, problems encountered, etc. |
updated_on | timestamp without time zone | NO | The date of update of this record. |
Table: program_library
A table to associate programs with library documents.
Name | Type | Nullable | Documentation |
---|---|---|---|
library_id | integer | NO | A reference to the library item. |
program_id | integer | NO | A reference to the program item. |
Table: program_member
Assigns user roles to a program.
Name | Type | Nullable | Documentation |
---|---|---|---|
person_id | integer | NO | A reference to the person. |
program_id | integer | NO | The reference to the program. |
role_id | integer | NO | A reference to the program role. |
Table: program_role
Represents the roles a person might perform in respect to a program. A person can be assigned multiple roles within a single program, and multiple people can work on a program with the same role.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The name of the role. E.g., "Chief Scientist." |
note | text | YES | An optional note about the role. |
Table: protocol
This table stores "(survey) protocol" entries from the source data. TODO: Used by events though it's not yet clear if it should stay this way.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | A name for the survey protocol. |
note | text | YES | An optional note about the protocol. |
short_code | character varying | NO | Contains a short code that can be used to look up a survey protocol (e.g., during import) without relying on the primary key. |
Table: relief
Provides a nominal level of terrain relief for habitat events.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The textual representation of the relief level. |
note | text | YES | A note about the relief level. |
short_code | character varying | NO | Contains a short code that can be used to look up a relief (e.g., during import) without relying on the primary key. |
Table: status_event
This table records status events with names given by the status_type_detail table. Status events represent a state that changes at some time and persists until another state preempts it. For example, "on bottom" would persist until an "off bottom" event is recorded. A status_event is a specialization of an event.
Name | Type | Nullable | Documentation |
---|---|---|---|
event_id | integer | NO | The associated event. |
status_type_detail_id | integer | NO | The detailed status type. |
Table: status_type
This is a lookup table for available status types, such as "on bottom" or "off bottom." These are specialized in the status_type_detail table where a note distinguishes different flavours of a given type. For example, there can be multiple types of "Off Transect" events with a different explanation for each.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The name of the status event. |
note | text | YES | An optional description of the status type. |
short_code | character varying | NO | A short code for referencing the status type in import documents. |
Table: status_type_detail
This table associates a status type with a status event. The point of this indirection is to allow the addition of detail relative to the status type. For example, there are multiple reasons why an ROV might be "off transect", including that the vehicle has experienced a failure, or the pilot is investigating some interesting object with no research value. The status type value is the discriminator for the event, but the detail adds context the doesn't interfere with it.
Name | Type | Nullable | Documentation |
---|---|---|---|
detail | character varying | NO | The status type detail. This field distinguishes different uses for status types. For example, there can be more than one reason to record an "Off Transect" event. |
note | text | YES | Optional extended description of detail. |
short_code | character varying | NO | Contains a short code that can be used to look up a status type detail (e.g., during import) without relying on the primary key. |
status_type_id | integer | NO | A reference to the status type. |
Table: substrate
A lookup table listing the available substrate types for the habitat_event table. TODO: This table may be altered to provide a hierarchical list of types with increasing specificity.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The name of the substrate. |
note | text | YES | An optional description of the substrate. |
short_code | character varying | NO | Contains a short code that can be used to look up a substrate (e.g., during import) without relying on the primary key. |
Table: survey_mode
This table stores "survey mode" entries from the source data. TODO: Used by events though it's not yet clear if it should stay this way.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The name of the survey mode. |
note | text | YES | An optional note about the survey mode. |
short_code | character varying | NO | Contains a short code that can be used to look up a survey mode (e.g., during import) without relying on the primary key. |
Table: thickness
A lookup table listing the available substrate thicknesses for the habitat_event table. TODO: This table may be altered to provide a hierarchical list of types with increasing specificity.
Name | Type | Nullable | Documentation |
---|---|---|---|
maximum | real | YES | The maximum value in the range. |
minimum | real | YES | The minimum value in the range. |
name | character varying | NO | A characterization of biocover thickness. |
note | text | YES | An optional note about the biocover thickness. |
short_code | character varying | NO | Contains a short code that can be used to look up a thickness (e.g., during import) without relying on the primary key. |
Table: transect
A transect is a section of a dive during which interesting data are collected. Every transect is associated with a dive.
Name | Type | Nullable | Documentation |
---|---|---|---|
attributes | jsonb | YES | A JSON column used for recording structured attributes that do not fit with the regular table structure. |
dive_id | integer | NO | A reference to the dive during which this transect occurred. |
end_time | timestamp without time zone | YES | The end time of the transect. |
name | character varying | NO | The name of the transect. |
note | text | YES | An optional note about the transect. |
objective | text | YES | A statement of the practical or research objectives for this true. |
start_time | timestamp without time zone | NO | The start time of the transect. |
summary | text | YES | A summary of the transect; whether objectives were met, problems encountered, etc. |
Table: weather_observation
Surface weather observations can be recorded by any crew member aboard a ship during a cruise leg.
Name | Type | Nullable | Documentation |
---|---|---|---|
cruise_crew_id | integer | YES | A reference to the cruise leg crew member who is making the report. |
cruise_id | integer | NO | A link to the cruise during which this record was recorded. |
note | text | YES | An optional note about the observation |
pressure | real | YES | The air pressure. |
swell | character varying | YES | Description of swell. |
temperature | real | YES | The air temperature. |
time | timestamp without time zone | NO | An optional note about the weather. |
wind_direction | real | YES | The wind direction. |
wind_speed | real | YES | The wind speed. |
Materialized View: cruise_track
Constructs a geometry for each cruise which describes the path of the ship.
Name | Type | Nullable | Documentation |
---|---|---|---|
colour | text | False | The a colour code generated from the cruise's ID used for cartography. |
cruise_id | integer | False | The reference to the cruise. |
cruise_name | text | False | The cruise name and leg. |
geom | geometry | False | The cruise track geometry. |
instrument_config_id | integer | False | The reference to the instrument configuration. |
Materialized View: depth
A view on the measurements table containing only depths.
Name | Type | Nullable | Documentation |
---|---|---|---|
dive_id | integer | False | The ID of the dive during which the depth was measured. |
quantity | real | False | [None] |
timestamp | timestamp(6) without time zone | False | The timestamp of the record. |
Materialized View: dive_track
Constructs a geometry for each dive which describes the path of the submersible.
Name | Type | Nullable | Documentation |
---|---|---|---|
colour | text | False | The a colour code generated from the cruise's ID. Used for cartography. |
cruise_id | integer | False | The reference to the cruise. |
cruise_name | text | False | The cruise name. |
dive_id | integer | False | The reference to the dive. |
dive_name | character varying(64) | False | The dive name. |
geom | geometry | False | The dive track geometry. |
transect_id | integer | False | The reference to the transect. Zero if there is no corresponding transect. |
transect_name | character varying(64) | False | The transect name. |
Materialized View: evt_depth
Creates a relation between an event and the depth nearest the start and end times of the event.
Name | Type | Nullable | Documentation |
---|---|---|---|
end_diff | interval | False | [None] |
end_measurement_id | integer | False | The link to the depth nearest the event's end time, or null if one is not supplied. |
event_id | integer | False | The link to the event's's event ID. |
start_diff | interval | False | [None] |
start_measurement_id | integer | False | The link to the depth nearest the event's start time. |
Materialized View: evt_pos
Creates a relation between an event and the positions nearest the start and end times of the event.
Name | Type | Nullable | Documentation |
---|---|---|---|
end_diff | interval | False | [None] |
end_position_id | integer | False | The link to the position nearest the event's end time, or null if one is not supplied. |
event_id | integer | False | The link to the event's's event ID. |
start_diff | interval | False | [None] |
start_position_id | integer | False | The link to the position nearest the event's start time. |
Materialized View: measurement_position
Creates a relation between a measurement and the temporally-nearest position.
Name | Type | Nullable | Documentation |
---|---|---|---|
measurement_id | integer | False | The link to the measurement. |
position_id | integer | False | The link to the position. |
Stores data that are shared between other schemas, such as personnel information, taxonomy, etc.
Table: db_version
Stores the current database version so that upgrade scripts can perform migrations appropriately.
Name | Type | Nullable | Documentation |
---|---|---|---|
revision | integer | NO | The revision number. |
updated_on | timestamp without time zone | NO | The time the upgrade was performed. |
version_major | integer | NO | The major version. |
version_minor | integer | NO | The minor version number. |
Table: file
Stores a record of a file object and its location on disk, along with some metadata. This entity is used by all other entities in all schemas that refer to a file object. This should make it easier to audit file stores and e.g., find duplicates using the hash.
Name | Type | Nullable | Documentation |
---|---|---|---|
blob_url | character varying | YES | A URL referencing the file in online blob storage. This field is subject to change depending on where the files reside and will change if they're moved. |
created_on | timestamp without time zone | NO | The creation time of the file record, not necessarily the file itself (this should be stored in metadata). |
description | text | YES | An optional description for the file. |
file_type_id | integer | YES | An optional reference to the file type. |
hash | character varying | NO | And MD5 hash of the file data. Used to compare files and search for identical versions. |
metadata | jsonb | YES | A JSON dictionary containing metadata relating to the file. |
name | character varying | NO | The name for the file. |
path | character varying | NO | A path of the file, relative to the root directory where files are stored. |
updated_on | timestamp without time zone | NO | The update time of the file record, not necessarily the file itself (this should be stored in metadata). |
Table: file_type
A list of file types.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | The name of the file type. |
short_code | character varying | NO | A short code for referencing the file type in import documents. |
Table: hart_taxon
Species from wherever the Hart codes are from? This table contains records loaded from the VideoMiner species table. This appears to be related to whatever the source of Hart codes is, but at this point VM is the only source. If the name needs to be changed in the future, so be it. Records in this table have a one-to-one relationship with records in the taxon table, and it serves as a metadata table for records stored there.
Name | Type | Nullable | Documentation |
---|---|---|---|
authority | text | YES | The identification authority. |
common_name | character varying | YES | The common name. |
from_date | date | YES | ? |
grouping_id | integer | YES | The grouping. * B - Bird * F - Fish * I - Invertebrate * M - Mammal * O - Object? * R - Reptile |
latin_name | character varying | YES | The Latin name of the item, which is distinct in this table from the scientific name. Appears to be informal, and not necessarily Latin. |
nodc_code_v7 | real | YES | The NODC code, v7. |
nodc_code_v8 | integer | YES | The NODC code, V8. Identical to the ITIS ID. |
rank_id | integer | YES | A three-letter code indicating the taxonomic level. |
scientific_name | character varying | YES | The scientific (e.g., Linnean) name. |
sp_species_code | character varying | YES | Another species code whose purpose/origin is not known. |
species_code | character varying | NO | The species, or Hart, code. |
species_prov_code | character varying | YES | ? |
taxa_reference | text | YES | The reference for the identification. |
to_date | date | YES | ? |
Table: library
This table stores a partial record of documents, books and papers that can be referenced by other entities. The ISBN and DOI fields are set to unique, but these may vary (in representation, at least) for a single document so care should be taken not to add duplicates. However, updated papers with the same title and author, but different date, are not disallowed.
Name | Type | Nullable | Documentation |
---|---|---|---|
abstract | text | YES | The abstract of the entry. |
authors | jsonb | YES | The (list of) author(s) of the document. This is a JSON list of objects containing whatever information is necessary. Authors should be listed with at least first_name and last_name, and any other relevant information, such as email or institution. |
created_on | timestamp without time zone | YES | The creation time of the file record, not necessarily the file itself (this should be stored in metadata). |
doi | character varying | YES | The DOI of the document. |
file_id | integer | NO | An optional reference to a file. |
institution | character varying | YES | The name of the institution or publisher responsible for the document. |
isbn | character varying | YES | The ISBN of the book, if it is a book. |
issn | character varying | YES | The ISSN of the entry. |
keywords | jsonb | YES | A list of keywords relating to the entry. |
mendeley_id | character varying | YES | The ID of the record in Mendeley. |
publication | character varying | YES | The name of the publication in which the document appeared. |
title | text | NO | The title of the book, paper, or other document. |
type | character varying | YES | Describes the type of entry: book, article, etc. |
updated_on | timestamp without time zone | YES | The update time of the file record, not necessarily the file itself (this should be stored in metadata). |
year | character varying | YES | The publishing date of the document. |
Table: mseauser
Represents an MSEA user and is linked to a single Django auth User. Stores extra application-related properties such as the Biigle API key.
Name | Type | Nullable | Documentation |
---|---|---|---|
biigle_api_key | character varying | YES | The Biigle API key. |
biigle_username | character varying | YES | The Biigle username. |
pg_role | character varying | YES | The name of the PostgreSQL role that the user will use to log in directly to the database. |
user_id | integer | NO | A reference to the Django user. |
Table: mseauser_restriction
Links an MSEA user to a restriction. The user will have access to records related to this group.
Name | Type | Nullable | Documentation |
---|---|---|---|
created_on | timestamp without time zone | NO | The creation time of the record. |
mseauser_id | integer | NO | The ID of the MSEA user. |
restriction_id | integer | NO | The ID of the restriction. |
updated_on | timestamp without time zone | NO | The last update time of the record. |
Table: news_item
A table for news items related to each sub-site.
Name | Type | Nullable | Documentation |
---|---|---|---|
content | text | NO | The content of the item. |
created_on | timestamp without time zone | NO | The date of creation of the item. |
sites | character varying | YES | An optional comma-delimited list of sites the item refers to. The current list is 'rca', 'rov', 'intertidal'. Leave empty to apply to all sites. TODO: Currently not standardized or enforced. |
title | character varying | NO | A title for the item. |
Table: organisation
Convenient storage for organisations involved in MSEA activities.
Name | Type | Nullable | Documentation |
---|---|---|---|
country | character varying | NO | The code for the country where the organisation is based (e.g., "CA" for Canada). |
name | character varying | NO | The full name of the organisation. |
note | text | YES | An optional note about the organisation. |
short_code | character varying | NO | A short code for looking up the entity; usually the organisation's acronym. |
Table: person
Stores information about people involved in all aspects of the application. This is not intended to be a comprehensive duplicate of the individual's HR dossier, just a means of representing a person and providing a means of associating it with a real-world individual, e.g., by their email.
Name | Type | Nullable | Documentation |
---|---|---|---|
biigle_user_id | integer | YES | The Biigle database ID of a user. The UUID should be used instead. |
biigle_uuid | character | YES | The Biigle UUID is used to identify the user in Biigle apps. |
bio | text | YES | A brief biography of the person. |
character varying | NO | Email address. | |
first_name | character varying | NO | First name. |
last_name | character varying | NO | Last name. |
photo | bytea | YES | A photo of the person. |
Table: restriction
Provides a list of restrictions that can be applied to individual observation rows, to restrict access to allowed roles.
Name | Type | Nullable | Documentation |
---|---|---|---|
created_on | timestamp without time zone | NO | The creation time of the restriction. |
name | character varying | NO | The name of the restriction. |
note | text | YES | An optional note about the restriction. |
short_code | character varying | NO | A short code used to reference the restriction. |
updated_on | timestamp without time zone | NO | The last update time of the restriction. |
Table: site
Contains the names and geographic boundaries of study sites. This will be useful not only for mapping but for querying data by spatial extent.
Name | Type | Nullable | Documentation |
---|---|---|---|
name | character varying | NO | A name for the site. |
note | text | YES | An optional note about the site. |
spatial_library_id | integer | YES | A reference to the spatial library entry. |
Table: spatial_library
Maintains a library of spatial data.
Name | Type | Nullable | Documentation |
---|---|---|---|
created_on | timestamp without time zone | NO | The time when this record was created. |
file_id | integer | YES | A reference to a related file. |
geom | geometry | YES | A vector representation of the object(s), projected into WGS84 (lat/lon) and stored as a geography type. |
metadata | jsonb | YES | A JSON object containing metadata related to the spatial object. |
name | character varying | NO | A name for the entry. |
note | text | YES | A note about this library item. |
rast | bytea | YES | A raster. |
thumbnail | bytea | YES | A small thumbnail in binary (JPG) form. |
updated_on | timestamp without time zone | NO | The date of update of this record. |
Table: spatial_library_file
Allows a spatial library entity to own more than one file.
Name | Type | Nullable | Documentation |
---|---|---|---|
file_id | integer | NO | A link to the file. |
spatial_library_id | integer | NO | A link to the spatial library. |
Table: survey_suggestion
A table of suggestions supplied by users for upgrades to the database.
Name | Type | Nullable | Documentation |
---|---|---|---|
created_on | timestamp without time zone | NO | [None] |
down_votes | integer | NO | A count of the down votes on the suggestion. |
character varying | YES | The email of the suggesting user. | |
screen_name | character varying | YES | A screen name for the suggesting user. |
suggestion | text | NO | The text of the suggestion. |
up_votes | integer | NO | A count of the up votes on the suggestion. |
Table: taxon
The taxon table stores information about the identification of observed specimens. A taxon record stores the name of the taxonomic unit (e.g., the species or genus name), or an open nomenclature (ON) identifier using *sp.* or *cf.*; an optional operational taxonomic unit (OTU), and a reference to any known taxa in the Hart, ITIS, iNaturalist or WoRMS databases. The original label stores the name as imported. The label, OTU and Hart code are updated at audit time from the original label and the scientific and common names are updated if available. The auditing process is necessary to produce reasonable data. Some of this work can be performed automatically. Multiple records in this table can refer to the same species or subspecies. It *may or may not be known whether the species referred to by a record are the same or different.* Some amount of auditing will probably be necessary on this table. The contents of this table are driven by observations; it is not populated from existing catalogues but from observations, and the authoritative entity IDs are populated as a post-processing step from existing catalogs. If a match in one of the authoritative catalogue is not found, the relation fields are left empty and the taxon should be audited, to correct spelling or assign a correct identifier.
Name | Type | Nullable | Documentation |
---|---|---|---|
aphia_id | integer | YES | A reference to the WoRMs database. The actual Aphia ID used by WoRMs is used as the primary key in the WoRMs taxon table. |
common_name | character varying | YES | The common name of the taxon. |
hart_code | character varying | YES | Used when there's not ref in the hart code table. |
hart_id | integer | YES | A reference to the Hart code table. This is an identification with species used by VideoMiner and other tools that use that list. |
inaturalist_id | integer | YES | A reference to the iNaturalist taxon. The iNaturalist taxon ID is used as the primary key in the iNaturalist taxon table. |
label | character varying | YES | The label given to the species at audit time. May be modified from the original label. May differ from the scientific or common names. |
original_label | character varying | NO | The original label as entered by the annotator or observer. On review or audit, the label, hart_code and otu fields will be populated and references to outside databases updated. This field will not be edited. |
otu | character varying | YES | The operational taxonomic unit is a unique identifier for this instances of an observation whose identification isn't certain. |
review_note | text | YES | Optional note about the review. |
reviewed_by_id | integer | YES | Relates to the person who reviewed the record. |
reviewed_on | timestamp without time zone | YES | Gives the time that the record was reviewed. |
scientific_name | character varying | YES | The scientific (binomial, trinomial) name of the taxon. Updated at audit time, based on the label or other fields. |
Table: taxon_restriction
Links an MSEA user to a restriction. The user will have access to records related to this group.
Name | Type | Nullable | Documentation |
---|---|---|---|
created_on | timestamp without time zone | NO | The creation time of the record. |
restriction_id | integer | NO | The ID of the restriction. |
taxon_id | integer | NO | The ID of the taxon. |
updated_on | timestamp without time zone | NO | The last update time of the record. |
Table: taxonomic_grouping
Provides a lookup for the taxonomic grouping (Invertebrate, Fish, etc.) Used by VideoMiner.
Name | Type | Nullable | Documentation |
---|---|---|---|
code | character | YES | A one-letter code for the grouping. |
name | character varying | NO | The name of the taxonomic grouping. |
note | text | YES | A note about the taxonomic grouping. |
Table: taxonomic_rank
Provides a lookup for the taxonomic rank (Kingdom, Class, Species, etc.) Used by VideoMiner.
Name | Type | Nullable | Documentation |
---|---|---|---|
code | character | YES | A three-letter code for the rank. |
name | character varying | NO | The name of the taxonomic rank. |
note | text | YES | A note about the taxonomic rank. |
Table: uploaded_file
A table to store uploaded files that can be associated with other entities in separate requests. This table is intended to store temporary entities: when an uploaded file has been handled, these records should be deleted.
Name | Type | Nullable | Documentation |
---|---|---|---|
created_on | timestamp without time zone | NO | The time of creation of the record. |
expires_on | timestamp without time zone | NO | The expiry time of the file. It may be deleted by a maintenance script after this time. |
name | character varying | NO | The original name of the file. |
path | character varying | NO | The path to the temporary location of the file. |
type | character varying | NO | The guessed MIME type of the file. |
Schema: wiki
Stores data related to the Wiki using the standard MediaWiki structure.
Table: actor
None
Name | Type | Nullable | Documentation |
---|---|---|---|
actor_id | bigint | NO | [None] |
actor_name | text | NO | [None] |
actor_user | integer | YES | [None] |
Table: archive
None
Name | Type | Nullable | Documentation |
---|---|---|---|
ar_actor | bigint | NO | [None] |
ar_comment_id | bigint | NO | [None] |
ar_deleted | smallint | NO | [None] |
ar_id | integer | NO | [None] |
ar_len | integer | YES | [None] |
ar_minor_edit | smallint | NO | [None] |
ar_namespace | integer | NO | [None] |
ar_page_id | integer | YES | [None] |
ar_parent_id | integer | YES | [None] |
ar_rev_id | integer | NO | [None] |
ar_sha1 | text | NO | [None] |
ar_timestamp | timestamp with time zone | NO | [None] |
ar_title | text | NO | [None] |
Table: bot_passwords
None
Name | Type | Nullable | Documentation |
---|---|---|---|
bp_app_id | text | NO | [None] |
bp_grants | text | NO | [None] |
bp_password | text | NO | [None] |
bp_restrictions | text | NO | [None] |
bp_token | text | NO | [None] |
bp_user | integer | NO | [None] |
Table: category
None
Name | Type | Nullable | Documentation |
---|---|---|---|
cat_files | integer | NO | [None] |
cat_id | integer | NO | [None] |
cat_pages | integer | NO | [None] |
cat_subcats | integer | NO | [None] |
cat_title | text | NO | [None] |
Table: categorylinks
None
Name | Type | Nullable | Documentation |
---|---|---|---|
cl_collation | text | NO | [None] |
cl_from | integer | NO | [None] |
cl_sortkey | text | NO | [None] |
cl_sortkey_prefix | text | NO | [None] |
cl_timestamp | timestamp with time zone | NO | [None] |
cl_to | text | NO | [None] |
cl_type | text | NO | [None] |
Table: change_tag
None
Name | Type | Nullable | Documentation |
---|---|---|---|
ct_id | integer | NO | [None] |
ct_log_id | integer | YES | [None] |
ct_params | text | YES | [None] |
ct_rc_id | integer | YES | [None] |
ct_rev_id | integer | YES | [None] |
ct_tag_id | integer | NO | [None] |
Table: change_tag_def
None
Name | Type | Nullable | Documentation |
---|---|---|---|
ctd_count | bigint | NO | [None] |
ctd_id | integer | NO | [None] |
ctd_name | text | NO | [None] |
ctd_user_defined | smallint | NO | [None] |
Table: comment
None
Name | Type | Nullable | Documentation |
---|---|---|---|
comment_data | text | YES | [None] |
comment_hash | integer | NO | [None] |
comment_id | bigint | NO | [None] |
comment_text | text | NO | [None] |
Table: content
None
Name | Type | Nullable | Documentation |
---|---|---|---|
content_address | text | NO | [None] |
content_id | bigint | NO | [None] |
content_model | smallint | NO | [None] |
content_sha1 | text | NO | [None] |
content_size | integer | NO | [None] |
Table: content_models
None
Name | Type | Nullable | Documentation |
---|---|---|---|
model_id | integer | NO | [None] |
model_name | text | NO | [None] |
Table: externallinks
None
Name | Type | Nullable | Documentation |
---|---|---|---|
el_from | integer | NO | [None] |
el_id | integer | NO | [None] |
el_index | text | NO | [None] |
el_index_60 | text | NO | [None] |
el_to | text | NO | [None] |
el_to_domain_index | text | NO | [None] |
el_to_path | text | YES | [None] |
Table: filearchive
None
Name | Type | Nullable | Documentation |
---|---|---|---|
fa_actor | bigint | NO | [None] |
fa_archive_name | text | YES | [None] |
fa_bits | integer | YES | [None] |
fa_deleted | smallint | NO | [None] |
fa_deleted_reason_id | bigint | NO | [None] |
fa_deleted_timestamp | timestamp with time zone | YES | [None] |
fa_deleted_user | integer | YES | [None] |
fa_description_id | bigint | NO | [None] |
fa_height | integer | YES | [None] |
fa_id | integer | NO | [None] |
fa_major_mime | text | YES | [None] |
fa_media_type | text | YES | [None] |
fa_metadata | text | YES | [None] |
fa_minor_mime | text | YES | [None] |
fa_name | text | NO | [None] |
fa_sha1 | text | NO | [None] |
fa_size | integer | YES | [None] |
fa_storage_group | text | YES | [None] |
fa_storage_key | text | YES | [None] |
fa_timestamp | timestamp with time zone | YES | [None] |
fa_width | integer | YES | [None] |
Table: image
None
Name | Type | Nullable | Documentation |
---|---|---|---|
img_actor | bigint | NO | [None] |
img_bits | integer | NO | [None] |
img_description_id | bigint | NO | [None] |
img_height | integer | NO | [None] |
img_major_mime | text | NO | [None] |
img_media_type | text | YES | [None] |
img_metadata | text | NO | [None] |
img_minor_mime | text | NO | [None] |
img_name | text | NO | [None] |
img_sha1 | text | NO | [None] |
img_size | integer | NO | [None] |
img_timestamp | timestamp with time zone | NO | [None] |
img_width | integer | NO | [None] |
Table: imagelinks
None
Name | Type | Nullable | Documentation |
---|---|---|---|
il_from | integer | NO | [None] |
il_from_namespace | integer | NO | [None] |
il_to | text | NO | [None] |
Table: interwiki
None
Name | Type | Nullable | Documentation |
---|---|---|---|
iw_api | text | NO | [None] |
iw_local | smallint | NO | [None] |
iw_prefix | character varying | NO | [None] |
iw_trans | smallint | NO | [None] |
iw_url | text | NO | [None] |
iw_wikiid | character varying | NO | [None] |
Table: ip_changes
None
Name | Type | Nullable | Documentation |
---|---|---|---|
ipc_hex | text | NO | [None] |
ipc_rev_id | integer | NO | [None] |
ipc_rev_timestamp | timestamp with time zone | NO | [None] |
Table: ipblocks
None
Name | Type | Nullable | Documentation |
---|---|---|---|
ipb_address | text | NO | [None] |
ipb_allow_usertalk | smallint | NO | [None] |
ipb_anon_only | smallint | NO | [None] |
ipb_auto | smallint | NO | [None] |
ipb_block_email | smallint | NO | [None] |
ipb_by_actor | bigint | NO | [None] |
ipb_create_account | smallint | NO | [None] |
ipb_deleted | smallint | NO | [None] |
ipb_enable_autoblock | smallint | NO | [None] |
ipb_expiry | timestamp with time zone | NO | [None] |
ipb_id | integer | NO | [None] |
ipb_parent_block_id | integer | YES | [None] |
ipb_range_end | text | NO | [None] |
ipb_range_start | text | NO | [None] |
ipb_reason_id | bigint | NO | [None] |
ipb_sitewide | smallint | NO | [None] |
ipb_timestamp | timestamp with time zone | NO | [None] |
ipb_user | integer | NO | [None] |
Table: ipblocks_restrictions
None
Name | Type | Nullable | Documentation |
---|---|---|---|
ir_ipb_id | integer | NO | [None] |
ir_type | smallint | NO | [None] |
ir_value | integer | NO | [None] |
Table: iwlinks
None
Name | Type | Nullable | Documentation |
---|---|---|---|
iwl_from | integer | NO | [None] |
iwl_prefix | text | NO | [None] |
iwl_title | text | NO | [None] |
Table: job
None
Name | Type | Nullable | Documentation |
---|---|---|---|
job_attempts | integer | NO | [None] |
job_cmd | text | NO | [None] |
job_id | integer | NO | [None] |
job_namespace | integer | NO | [None] |
job_params | text | NO | [None] |
job_random | integer | NO | [None] |
job_sha1 | text | NO | [None] |
job_timestamp | timestamp with time zone | YES | [None] |
job_title | text | NO | [None] |
job_token | text | NO | [None] |
job_token_timestamp | timestamp with time zone | YES | [None] |
Table: l10n_cache
None
Name | Type | Nullable | Documentation |
---|---|---|---|
lc_key | character varying | NO | [None] |
lc_lang | text | NO | [None] |
lc_value | text | NO | [None] |
Table: langlinks
None
Name | Type | Nullable | Documentation |
---|---|---|---|
ll_from | integer | NO | [None] |
ll_lang | text | NO | [None] |
ll_title | text | NO | [None] |
Table: linktarget
None
Name | Type | Nullable | Documentation |
---|---|---|---|
lt_id | bigint | NO | [None] |
lt_namespace | integer | NO | [None] |
lt_title | text | NO | [None] |
Table: log_search
None
Name | Type | Nullable | Documentation |
---|---|---|---|
ls_field | text | NO | [None] |
ls_log_id | integer | NO | [None] |
ls_value | character varying | NO | [None] |
Table: logging
None
Name | Type | Nullable | Documentation |
---|---|---|---|
log_action | text | NO | [None] |
log_actor | bigint | NO | [None] |
log_comment_id | bigint | NO | [None] |
log_deleted | smallint | NO | [None] |
log_id | integer | NO | [None] |
log_namespace | integer | NO | [None] |
log_page | integer | YES | [None] |
log_params | text | NO | [None] |
log_timestamp | timestamp with time zone | NO | [None] |
log_title | text | NO | [None] |
log_type | text | NO | [None] |
Table: module_deps
None
Name | Type | Nullable | Documentation |
---|---|---|---|
md_deps | text | NO | [None] |
md_module | text | NO | [None] |
md_skin | text | NO | [None] |
Table: objectcache
None
Name | Type | Nullable | Documentation |
---|---|---|---|
exptime | timestamp with time zone | NO | [None] |
flags | integer | YES | [None] |
keyname | text | NO | [None] |
modtoken | character varying | NO | [None] |
value | text | YES | [None] |
Table: oldimage
None
Name | Type | Nullable | Documentation |
---|---|---|---|
oi_actor | bigint | NO | [None] |
oi_archive_name | text | NO | [None] |
oi_bits | integer | NO | [None] |
oi_deleted | smallint | NO | [None] |
oi_description_id | bigint | NO | [None] |
oi_height | integer | NO | [None] |
oi_major_mime | text | NO | [None] |
oi_media_type | text | YES | [None] |
oi_metadata | text | NO | [None] |
oi_minor_mime | text | NO | [None] |
oi_name | text | NO | [None] |
oi_sha1 | text | NO | [None] |
oi_size | integer | NO | [None] |
oi_timestamp | timestamp with time zone | NO | [None] |
oi_width | integer | NO | [None] |
Table: page
None
Name | Type | Nullable | Documentation |
---|---|---|---|
page_content_model | text | YES | [None] |
page_id | integer | NO | [None] |
page_is_new | smallint | NO | [None] |
page_is_redirect | smallint | NO | [None] |
page_lang | text | YES | [None] |
page_latest | integer | NO | [None] |
page_len | integer | NO | [None] |
page_links_updated | timestamp with time zone | YES | [None] |
page_namespace | integer | NO | [None] |
page_random | double precision | NO | [None] |
page_title | text | NO | [None] |
page_touched | timestamp with time zone | NO | [None] |
titlevector | tsvector | YES | [None] |
Table: page_props
None
Name | Type | Nullable | Documentation |
---|---|---|---|
pp_page | integer | NO | [None] |
pp_propname | text | NO | [None] |
pp_sortkey | double precision | YES | [None] |
pp_value | text | NO | [None] |
Table: page_restrictions
None
Name | Type | Nullable | Documentation |
---|---|---|---|
pr_cascade | smallint | NO | [None] |
pr_expiry | timestamp with time zone | YES | [None] |
pr_id | integer | NO | [None] |
pr_level | text | NO | [None] |
pr_page | integer | NO | [None] |
pr_type | text | NO | [None] |
Table: pagelinks
None
Name | Type | Nullable | Documentation |
---|---|---|---|
pl_from | integer | NO | [None] |
pl_from_namespace | integer | NO | [None] |
pl_namespace | integer | NO | [None] |
pl_title | text | NO | [None] |
Table: protected_titles
None
Name | Type | Nullable | Documentation |
---|---|---|---|
pt_create_perm | text | NO | [None] |
pt_expiry | timestamp with time zone | NO | [None] |
pt_namespace | integer | NO | [None] |
pt_reason_id | bigint | NO | [None] |
pt_timestamp | timestamp with time zone | NO | [None] |
pt_title | text | NO | [None] |
pt_user | integer | NO | [None] |
Table: querycache
None
Name | Type | Nullable | Documentation |
---|---|---|---|
qc_namespace | integer | NO | [None] |
qc_title | text | NO | [None] |
qc_type | text | NO | [None] |
qc_value | integer | NO | [None] |
Table: querycache_info
None
Name | Type | Nullable | Documentation |
---|---|---|---|
qci_timestamp | timestamp with time zone | NO | [None] |
qci_type | text | NO | [None] |
Table: querycachetwo
None
Name | Type | Nullable | Documentation |
---|---|---|---|
qcc_namespace | integer | NO | [None] |
qcc_namespacetwo | integer | NO | [None] |
qcc_title | text | NO | [None] |
qcc_titletwo | text | NO | [None] |
qcc_type | text | NO | [None] |
qcc_value | integer | NO | [None] |
Table: recentchanges
None
Name | Type | Nullable | Documentation |
---|---|---|---|
rc_actor | bigint | NO | [None] |
rc_bot | smallint | NO | [None] |
rc_comment_id | bigint | NO | [None] |
rc_cur_id | integer | NO | [None] |
rc_deleted | smallint | NO | [None] |
rc_id | integer | NO | [None] |
rc_ip | text | NO | [None] |
rc_last_oldid | integer | NO | [None] |
rc_log_action | text | YES | [None] |
rc_log_type | text | YES | [None] |
rc_logid | integer | NO | [None] |
rc_minor | smallint | NO | [None] |
rc_namespace | integer | NO | [None] |
rc_new | smallint | NO | [None] |
rc_new_len | integer | YES | [None] |
rc_old_len | integer | YES | [None] |
rc_params | text | YES | [None] |
rc_patrolled | smallint | NO | [None] |
rc_source | text | NO | [None] |
rc_this_oldid | integer | NO | [None] |
rc_timestamp | timestamp with time zone | NO | [None] |
rc_title | text | NO | [None] |
rc_type | smallint | NO | [None] |
Table: redirect
None
Name | Type | Nullable | Documentation |
---|---|---|---|
rd_fragment | text | YES | [None] |
rd_from | integer | NO | [None] |
rd_interwiki | character varying | YES | [None] |
rd_namespace | integer | NO | [None] |
rd_title | text | NO | [None] |
Table: revision
None
Name | Type | Nullable | Documentation |
---|---|---|---|
rev_actor | bigint | NO | [None] |
rev_comment_id | bigint | NO | [None] |
rev_deleted | smallint | NO | [None] |
rev_id | integer | NO | [None] |
rev_len | integer | YES | [None] |
rev_minor_edit | smallint | NO | [None] |
rev_page | integer | NO | [None] |
rev_parent_id | integer | YES | [None] |
rev_sha1 | text | NO | [None] |
rev_timestamp | timestamp with time zone | NO | [None] |
Table: revision_comment_temp
None
Name | Type | Nullable | Documentation |
---|---|---|---|
revcomment_comment_id | bigint | NO | [None] |
revcomment_rev | integer | NO | [None] |
Table: searchindex
None
Name | Type | Nullable | Documentation |
---|---|---|---|
si_page | integer | NO | [None] |
si_text | text | NO | [None] |
si_title | character varying | NO | [None] |
Table: site_identifiers
None
Name | Type | Nullable | Documentation |
---|---|---|---|
si_key | text | NO | [None] |
si_site | integer | NO | [None] |
si_type | text | NO | [None] |
Table: site_stats
None
Name | Type | Nullable | Documentation |
---|---|---|---|
ss_active_users | bigint | YES | [None] |
ss_good_articles | bigint | YES | [None] |
ss_images | bigint | YES | [None] |
ss_row_id | integer | NO | [None] |
ss_total_edits | bigint | YES | [None] |
ss_total_pages | bigint | YES | [None] |
ss_users | bigint | YES | [None] |
Table: sites
None
Name | Type | Nullable | Documentation |
---|---|---|---|
site_config | text | NO | [None] |
site_data | text | NO | [None] |
site_domain | character varying | NO | [None] |
site_forward | smallint | NO | [None] |
site_global_key | text | NO | [None] |
site_group | text | NO | [None] |
site_id | integer | NO | [None] |
site_language | text | NO | [None] |
site_protocol | text | NO | [None] |
site_source | text | NO | [None] |
site_type | text | NO | [None] |
Table: slot_roles
None
Name | Type | Nullable | Documentation |
---|---|---|---|
role_id | integer | NO | [None] |
role_name | text | NO | [None] |
Table: slots
None
Name | Type | Nullable | Documentation |
---|---|---|---|
slot_content_id | bigint | NO | [None] |
slot_origin | bigint | NO | [None] |
slot_revision_id | bigint | NO | [None] |
slot_role_id | smallint | NO | [None] |
Table: templatelinks
None
Name | Type | Nullable | Documentation |
---|---|---|---|
tl_from | integer | NO | [None] |
tl_from_namespace | integer | NO | [None] |
tl_target_id | bigint | NO | [None] |
Table: text
None
Name | Type | Nullable | Documentation |
---|---|---|---|
old_flags | text | NO | [None] |
old_id | integer | NO | [None] |
old_text | text | NO | [None] |
textvector | tsvector | YES | [None] |
Table: updatelog
None
Name | Type | Nullable | Documentation |
---|---|---|---|
ul_key | character varying | NO | [None] |
ul_value | text | YES | [None] |
Table: uploadstash
None
Name | Type | Nullable | Documentation |
---|---|---|---|
us_chunk_inx | integer | YES | [None] |
us_id | integer | NO | [None] |
us_image_bits | smallint | YES | [None] |
us_image_height | integer | YES | [None] |
us_image_width | integer | YES | [None] |
us_key | character varying | NO | [None] |
us_media_type | USER-DEFINED | YES | [None] |
us_mime | character varying | YES | [None] |
us_orig_path | character varying | NO | [None] |
us_path | character varying | NO | [None] |
us_props | text | YES | [None] |
us_sha1 | character varying | NO | [None] |
us_size | integer | NO | [None] |
us_source_type | character varying | YES | [None] |
us_status | character varying | NO | [None] |
us_timestamp | timestamp with time zone | NO | [None] |
us_user | integer | NO | [None] |
Table: user
None
Name | Type | Nullable | Documentation |
---|---|---|---|
user_editcount | integer | YES | [None] |
user_email | text | NO | [None] |
user_email_authenticated | timestamp with time zone | YES | [None] |
user_email_token | text | YES | [None] |
user_email_token_expires | timestamp with time zone | YES | [None] |
user_id | integer | NO | [None] |
user_name | text | NO | [None] |
user_newpass_time | timestamp with time zone | YES | [None] |
user_newpassword | text | NO | [None] |
user_password | text | NO | [None] |
user_password_expires | timestamp with time zone | YES | [None] |
user_real_name | text | NO | [None] |
user_registration | timestamp with time zone | YES | [None] |
user_token | text | NO | [None] |
user_touched | timestamp with time zone | NO | [None] |
Table: user_autocreate_serial
None
Name | Type | Nullable | Documentation |
---|---|---|---|
uas_shard | integer | NO | [None] |
uas_value | integer | NO | [None] |
Table: user_former_groups
None
Name | Type | Nullable | Documentation |
---|---|---|---|
ufg_group | text | NO | [None] |
ufg_user | integer | NO | [None] |
Table: user_groups
None
Name | Type | Nullable | Documentation |
---|---|---|---|
ug_expiry | timestamp with time zone | YES | [None] |
ug_group | text | NO | [None] |
ug_user | integer | NO | [None] |
Table: user_newtalk
None
Name | Type | Nullable | Documentation |
---|---|---|---|
user_id | integer | NO | [None] |
user_ip | text | NO | [None] |
user_last_timestamp | timestamp with time zone | YES | [None] |
Table: user_properties
None
Name | Type | Nullable | Documentation |
---|---|---|---|
up_property | text | NO | [None] |
up_user | integer | NO | [None] |
up_value | text | YES | [None] |
Table: watchlist
None
Name | Type | Nullable | Documentation |
---|---|---|---|
wl_id | integer | NO | [None] |
wl_namespace | integer | NO | [None] |
wl_notificationtimestamp | timestamp with time zone | YES | [None] |
wl_title | text | NO | [None] |
wl_user | integer | NO | [None] |
Table: watchlist_expiry
None
Name | Type | Nullable | Documentation |
---|---|---|---|
we_expiry | timestamp with time zone | NO | [None] |
we_item | integer | NO | [None] |