Annotation Database Entity Documentation

From MediaWiki
Jump to navigation Jump to search

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.

Columns in "observation"
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.

Columns in "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.

Columns in "quadrat_observation"
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.

Columns in "quadrat_substrate"
Name Type Nullable Documentation
name character varying NO The name of the substrate.

Table: quadrat_substrate_coverage

Associates quadrat substrate coverages with quadrats.

Columns in "quadrat_substrate_coverage"
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.

Columns in "survey"
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.

Columns in "survey_crew"
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.

Columns in "survey_role"
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.

Columns in "survey_taxon"
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

Columns in "cruise"
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

Columns in "diveconfig"
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

Columns in "dives"
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

Columns in "equipconfig"
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

Columns in "equipment"
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

Columns in "people"
Name Type Nullable Documentation
active boolean NO [None]
email 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

Columns in "transects"
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.

Columns in "taxon"
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

Columns in "mpa"
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.

Columns in "mpa_data_object"
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.

Columns in "mpa_data_object_file"
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.

Columns in "protected_area"
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.

Columns in "protected_area_data_object"
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.

Columns in "protected_area_data_object_file"
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.

Columns in "rca"
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.

Columns in "abundance"
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.

Columns in "annotation_protocol"
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.

Columns in "annotation_protocol_document"
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.

Columns in "annotation_software"
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.

Columns in "biocover"
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.

Columns in "comment_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.

Columns in "complexity"
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.

Columns in "coverage"
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.

Columns in "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.

Columns in "cruise_crew"
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

Columns in "cruise_document"
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.

Columns in "cruise_fn_contact"
Name Type Nullable Documentation
contact_name character varying NO The full name of the contact.
cruise_id integer NO A reference to the cruise.
email 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.

Columns in "cruise_library"
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.

Columns in "cruise_program"
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.

Columns in "cruise_role"
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.

Columns in "disturbance"
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.

Columns in "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.
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.

Columns in "dive_crew"
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.

Columns in "dive_role"
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.

Columns in "equipment_type"
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.

Columns in "event"
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.

Columns in "event_logger"
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.

Columns in "habitat_event"
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.

Columns in "image_quality"
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.

Columns in "import_queue_annotator"
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.

Columns in "import_queue_annotator_label_map_prefill"
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.

Columns in "import_queue_pi"
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.

Columns in "instrument"
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.

Columns in "instrument_config"
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.

Columns in "measurement"
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.

Columns in "measurement_event"
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.

Columns in "measurement_type"
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.

Columns in "medium"
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.

Columns in "medium_format"
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.

Columns in "medium_type"
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.

Columns in "model"
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.

Columns in "model_documentation"
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.

Columns in "model_equipment_type"
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.

Columns in "observation_category"
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.

Columns in "observation_confidence"
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.

Columns in "observation_event"
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.

Columns in "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.

Columns in "orientation_type"
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.

Columns in "platform"
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.

Columns in "platform_config"
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?

Columns in "position"
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.

Columns in "position_type"
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.

Columns in "program"
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.

Columns in "program_library"
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.

Columns in "program_member"
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.

Columns in "program_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.

Columns in "protocol"
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.

Columns in "relief"
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.

Columns in "status_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.

Columns in "status_type"
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.

Columns in "status_type_detail"
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.

Columns in "substrate"
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.

Columns in "survey_mode"
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.

Columns in "thickness"
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.

Columns in "transect"
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.

Columns in "weather_observation"
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.

Columns in "cruise_track"
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.

Columns in "depth"
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.

Columns in "dive_track"
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.

Columns in "evt_depth"
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.

Columns in "evt_pos"
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.

Columns in "measurement_position"
Name Type Nullable Documentation
measurement_id integer False The link to the measurement.
position_id integer False The link to the position.

Schema: shared

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.

Columns in "db_version"
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.

Columns in "file"
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.

Columns in "file_type"
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.

Columns in "hart_taxon"
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.

Columns in "library"
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.

Columns in "mseauser"
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.

Columns in "mseauser_restriction"
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.

Columns in "news_item"
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.

Columns in "organisation"
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.

Columns in "person"
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.
email 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.

Columns in "restriction"
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.

Columns in "site"
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.

Columns in "spatial_library"
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.

Columns in "spatial_library_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.

Columns in "survey_suggestion"
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.
email 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.

Columns in "taxon"
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.

Columns in "taxon_restriction"
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.

Columns in "taxonomic_grouping"
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.

Columns in "taxonomic_rank"
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.

Columns in "uploaded_file"
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

Columns in "actor"
Name Type Nullable Documentation
actor_id bigint NO [None]
actor_name text NO [None]
actor_user integer YES [None]

Table: archive

None

Columns in "archive"
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

Columns in "bot_passwords"
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

Columns in "category"
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

Columns in "categorylinks"
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

Columns in "change_tag"
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

Columns in "change_tag_def"
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

Columns in "comment"
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

Columns in "content"
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

Columns in "content_models"
Name Type Nullable Documentation
model_id integer NO [None]
model_name text NO [None]

Table: externallinks

None

Columns in "externallinks"
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

Columns in "filearchive"
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

Columns in "image"
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

Columns in "imagelinks"
Name Type Nullable Documentation
il_from integer NO [None]
il_from_namespace integer NO [None]
il_to text NO [None]

Table: interwiki

None

Columns in "interwiki"
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

Columns in "ip_changes"
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

Columns in "ipblocks"
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

Columns in "ipblocks_restrictions"
Name Type Nullable Documentation
ir_ipb_id integer NO [None]
ir_type smallint NO [None]
ir_value integer NO [None]

Table: iwlinks

None

Columns in "iwlinks"
Name Type Nullable Documentation
iwl_from integer NO [None]
iwl_prefix text NO [None]
iwl_title text NO [None]

Table: job

None

Columns in "job"
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

Columns in "l10n_cache"
Name Type Nullable Documentation
lc_key character varying NO [None]
lc_lang text NO [None]
lc_value text NO [None]

Table: langlinks

None

Columns in "langlinks"
Name Type Nullable Documentation
ll_from integer NO [None]
ll_lang text NO [None]
ll_title text NO [None]

Table: linktarget

None

Columns in "linktarget"
Name Type Nullable Documentation
lt_id bigint NO [None]
lt_namespace integer NO [None]
lt_title text NO [None]

Table: log_search

None

Columns in "log_search"
Name Type Nullable Documentation
ls_field text NO [None]
ls_log_id integer NO [None]
ls_value character varying NO [None]

Table: logging

None

Columns in "logging"
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

Columns in "module_deps"
Name Type Nullable Documentation
md_deps text NO [None]
md_module text NO [None]
md_skin text NO [None]

Table: objectcache

None

Columns in "objectcache"
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

Columns in "oldimage"
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

Columns in "page"
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

Columns in "page_props"
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

Columns in "page_restrictions"
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

Columns in "pagelinks"
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

Columns in "protected_titles"
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

Columns in "querycache"
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

Columns in "querycache_info"
Name Type Nullable Documentation
qci_timestamp timestamp with time zone NO [None]
qci_type text NO [None]

Table: querycachetwo

None

Columns in "querycachetwo"
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

Columns in "recentchanges"
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

Columns in "redirect"
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

Columns in "revision"
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

Columns in "revision_comment_temp"
Name Type Nullable Documentation
revcomment_comment_id bigint NO [None]
revcomment_rev integer NO [None]

Table: searchindex

None

Columns in "searchindex"
Name Type Nullable Documentation
si_page integer NO [None]
si_text text NO [None]
si_title character varying NO [None]

Table: site_identifiers

None

Columns in "site_identifiers"
Name Type Nullable Documentation
si_key text NO [None]
si_site integer NO [None]
si_type text NO [None]

Table: site_stats

None

Columns in "site_stats"
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

Columns in "sites"
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

Columns in "slot_roles"
Name Type Nullable Documentation
role_id integer NO [None]
role_name text NO [None]

Table: slots

None

Columns in "slots"
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

Columns in "templatelinks"
Name Type Nullable Documentation
tl_from integer NO [None]
tl_from_namespace integer NO [None]
tl_target_id bigint NO [None]

Table: text

None

Columns in "text"
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

Columns in "updatelog"
Name Type Nullable Documentation
ul_key character varying NO [None]
ul_value text YES [None]

Table: uploadstash

None

Columns in "uploadstash"
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

Columns in "user"
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

Columns in "user_autocreate_serial"
Name Type Nullable Documentation
uas_shard integer NO [None]
uas_value integer NO [None]

Table: user_former_groups

None

Columns in "user_former_groups"
Name Type Nullable Documentation
ufg_group text NO [None]
ufg_user integer NO [None]

Table: user_groups

None

Columns in "user_groups"
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

Columns in "user_newtalk"
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

Columns in "user_properties"
Name Type Nullable Documentation
up_property text NO [None]
up_user integer NO [None]
up_value text YES [None]

Table: watchlist

None

Columns in "watchlist"
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

Columns in "watchlist_expiry"
Name Type Nullable Documentation
we_expiry timestamp with time zone NO [None]
we_item integer NO [None]