Skip to content

Instantly share code, notes, and snippets.

@kpinc

kpinc/maketables

Created December 18, 2018 21:24
Show Gist options
  • Save kpinc/83fdec85ed2d630395cbb280b135b656 to your computer and use it in GitHub Desktop.
Save kpinc/83fdec85ed2d630395cbb280b135b656 to your computer and use it in GitHub Desktop.
Download ZIP
Babase schema
Raw
maketables
-- Copyright (C) 2011-2013 The Meme Factory, Inc. http://www.meme.com/
-- Copyright (C) 2004-2011, Karl O. Pinc <kop@meme.com>
--
-- This file is part of Babase.
--
-- Babase is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 3 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with Babase. If not, see <http://www.gnu.org/licenses/>.
--
-- Karl O. Pinc <kop@meme.com>
-- DO NOT EDIT THIS FILE. It was automatically generated. Edit
-- the *.m4 files instead. (Files _should_ be re-created by
-- typing 'make' at the command line.)
-- Make all the babase tables and give out privilges.
-- $Id: maketables.m4,v 1.75 2004/11/09 19:09:51 kop Exp $
--
-- Make all the tables and give out privilges.
--
--
-- Support Tables
--
-- Meaning of status codes hardcoded into integrity rules.
CREATE TABLE demog_references (
reference NUMERIC(6,4) PRIMARY KEY NOT NULL ,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON demog_references TO GROUP babase_readers;
GRANT SELECT ON demog_references TO GROUP babase_editors;
GRANT INSERT ON demog_references TO GROUP babase_editors;
GRANT UPDATE ON demog_references TO GROUP babase_editors;
GRANT DELETE ON demog_references TO GROUP babase_editors;
SELECT 'demog_references' AS done_with;
CREATE TABLE statuses (
status SMALLINT PRIMARY KEY NOT NULL ,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON statuses TO GROUP babase_readers;
GRANT SELECT ON statuses TO GROUP babase_editors;
GRANT INSERT ON statuses TO GROUP babase_editors;
GRANT UPDATE ON statuses TO GROUP babase_editors;
GRANT DELETE ON statuses TO GROUP babase_editors;
SELECT 'statuses' AS done_with;
CREATE TABLE bstatuses (
bstatus NUMERIC(2,1) PRIMARY KEY NOT NULL ,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON bstatuses TO GROUP babase_readers;
GRANT SELECT ON bstatuses TO GROUP babase_editors;
GRANT INSERT ON bstatuses TO GROUP babase_editors;
GRANT UPDATE ON bstatuses TO GROUP babase_editors;
GRANT DELETE ON bstatuses TO GROUP babase_editors;
SELECT 'bstatuses' AS done_with;
CREATE TABLE entrytypes (
entrytype CHAR(1)
PRIMARY KEY NOT NULL CONSTRAINT "entrytype: Cannot be empty or only whitespace characters"
CHECK(btrim(entrytype, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON entrytypes TO GROUP babase_readers;
GRANT SELECT ON entrytypes TO GROUP babase_editors;
GRANT INSERT ON entrytypes TO GROUP babase_editors;
GRANT UPDATE ON entrytypes TO GROUP babase_editors;
GRANT DELETE ON entrytypes TO GROUP babase_editors;
SELECT 'entrytypes' AS done_with;
CREATE TABLE mstatuses (
mstatus CHAR(1)
PRIMARY KEY NOT NULL CONSTRAINT "mstatus: Cannot be empty or only whitespace characters"
CHECK(btrim(mstatus, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON mstatuses TO GROUP babase_readers;
GRANT SELECT ON mstatuses TO GROUP babase_editors;
GRANT INSERT ON mstatuses TO GROUP babase_editors;
GRANT UPDATE ON mstatuses TO GROUP babase_editors;
GRANT DELETE ON mstatuses TO GROUP babase_editors;
SELECT 'mstatuses' AS done_with;
CREATE TABLE pcscolors (
color CHAR(1)
PRIMARY KEY NOT NULL CONSTRAINT "color: Cannot be empty or only whitespace characters"
CHECK(btrim(color, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON pcscolors TO GROUP babase_readers;
GRANT SELECT ON pcscolors TO GROUP babase_editors;
GRANT INSERT ON pcscolors TO GROUP babase_editors;
GRANT UPDATE ON pcscolors TO GROUP babase_editors;
GRANT DELETE ON pcscolors TO GROUP babase_editors;
SELECT 'pcscolors' AS done_with;
CREATE TABLE foodtypes (
ftype CHAR(1)
PRIMARY KEY NOT NULL CONSTRAINT "ftype: Cannot be empty or only whitespace characters"
CHECK(btrim(ftype, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON foodtypes TO GROUP babase_readers;
GRANT SELECT ON foodtypes TO GROUP babase_editors;
GRANT INSERT ON foodtypes TO GROUP babase_editors;
GRANT UPDATE ON foodtypes TO GROUP babase_editors;
GRANT DELETE ON foodtypes TO GROUP babase_editors;
SELECT 'foodtypes' AS done_with;
CREATE TABLE kidcontacts (
kidcontact CHAR(1)
PRIMARY KEY NOT NULL CONSTRAINT "kidcontact: Cannot be empty or only whitespace characters"
CHECK(btrim(kidcontact, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON kidcontacts TO GROUP babase_readers;
GRANT SELECT ON kidcontacts TO GROUP babase_editors;
GRANT INSERT ON kidcontacts TO GROUP babase_editors;
GRANT UPDATE ON kidcontacts TO GROUP babase_editors;
GRANT DELETE ON kidcontacts TO GROUP babase_editors;
SELECT 'kidcontacts' AS done_with;
CREATE TABLE suckles (
suckle CHAR(1)
PRIMARY KEY NOT NULL CONSTRAINT "suckle: Cannot be empty or only whitespace characters"
CHECK(btrim(suckle, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON suckles TO GROUP babase_readers;
GRANT SELECT ON suckles TO GROUP babase_editors;
GRANT INSERT ON suckles TO GROUP babase_editors;
GRANT UPDATE ON suckles TO GROUP babase_editors;
GRANT DELETE ON suckles TO GROUP babase_editors;
SELECT 'suckles' AS done_with;
CREATE TABLE palmtops (
palmtop SMALLINT PRIMARY KEY NOT NULL ,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON palmtops TO GROUP babase_readers;
GRANT SELECT ON palmtops TO GROUP babase_editors;
GRANT INSERT ON palmtops TO GROUP babase_editors;
GRANT UPDATE ON palmtops TO GROUP babase_editors;
GRANT DELETE ON palmtops TO GROUP babase_editors;
SELECT 'palmtops' AS done_with;
CREATE TABLE data_structures (
data_structure SMALLINT PRIMARY KEY NOT NULL ,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON data_structures TO GROUP babase_readers;
GRANT SELECT ON data_structures TO GROUP babase_editors;
GRANT INSERT ON data_structures TO GROUP babase_editors;
GRANT UPDATE ON data_structures TO GROUP babase_editors;
GRANT DELETE ON data_structures TO GROUP babase_editors;
SELECT 'data_structures' AS done_with;
CREATE TABLE gap_end_statuses (
gap_end_status SMALLINT PRIMARY KEY NOT NULL ,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON gap_end_statuses TO GROUP babase_readers;
GRANT SELECT ON gap_end_statuses TO GROUP babase_editors;
GRANT INSERT ON gap_end_statuses TO GROUP babase_editors;
GRANT UPDATE ON gap_end_statuses TO GROUP babase_editors;
GRANT DELETE ON gap_end_statuses TO GROUP babase_editors;
SELECT 'gap_end_statuses' AS done_with;
CREATE TABLE wstations (
wstation VARCHAR(3)
PRIMARY KEY NOT NULL CONSTRAINT "wstation: Cannot be empty or only whitespace characters"
CHECK(btrim(wstation, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON wstations TO GROUP babase_readers;
GRANT SELECT ON wstations TO GROUP babase_editors;
GRANT INSERT ON wstations TO GROUP babase_editors;
GRANT UPDATE ON wstations TO GROUP babase_editors;
GRANT DELETE ON wstations TO GROUP babase_editors;
SELECT 'wstations' AS done_with;
CREATE TABLE partunks (
unksname CHAR(1)
PRIMARY KEY NOT NULL CONSTRAINT "unksname: Cannot be empty or only whitespace characters"
CHECK(btrim(unksname, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON partunks TO GROUP babase_readers;
GRANT SELECT ON partunks TO GROUP babase_editors;
GRANT INSERT ON partunks TO GROUP babase_editors;
GRANT UPDATE ON partunks TO GROUP babase_editors;
GRANT DELETE ON partunks TO GROUP babase_editors;
SELECT 'partunks' AS done_with;
CREATE TABLE context_types (
context_type CHAR(1)
PRIMARY KEY NOT NULL CONSTRAINT "context_type: Cannot be empty or only whitespace characters"
CHECK(btrim(context_type, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON context_types TO GROUP babase_readers;
GRANT SELECT ON context_types TO GROUP babase_editors;
GRANT INSERT ON context_types TO GROUP babase_editors;
GRANT UPDATE ON context_types TO GROUP babase_editors;
GRANT DELETE ON context_types TO GROUP babase_editors;
SELECT 'context_types' AS done_with;
CREATE TABLE observer_roles (
role VARCHAR(75) PRIMARY KEY NOT NULL ,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON observer_roles TO GROUP babase_readers;
GRANT SELECT ON observer_roles TO GROUP babase_editors;
GRANT INSERT ON observer_roles TO GROUP babase_editors;
GRANT UPDATE ON observer_roles TO GROUP babase_editors;
GRANT DELETE ON observer_roles TO GROUP babase_editors;
SELECT 'observer_roles' AS done_with;
CREATE TABLE drugs (
drug CHAR(1) PRIMARY KEY NOT NULL CONSTRAINT "drug: Cannot be empty or only whitespace characters"
CHECK(btrim(drug, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON drugs TO GROUP babase_readers;
GRANT SELECT ON drugs TO GROUP babase_editors;
GRANT INSERT ON drugs TO GROUP babase_editors;
GRANT UPDATE ON drugs TO GROUP babase_editors;
GRANT DELETE ON drugs TO GROUP babase_editors;
SELECT 'drugs' AS done_with;
CREATE TABLE lymphstates (
lymphstate CHAR(1) PRIMARY KEY NOT NULL CONSTRAINT "lymphstate: Cannot be empty or only whitespace characters"
CHECK(btrim(lymphstate, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON lymphstates TO GROUP babase_readers;
GRANT SELECT ON lymphstates TO GROUP babase_editors;
GRANT INSERT ON lymphstates TO GROUP babase_editors;
GRANT UPDATE ON lymphstates TO GROUP babase_editors;
GRANT DELETE ON lymphstates TO GROUP babase_editors;
SELECT 'lymphstates' AS done_with;
CREATE TABLE parasites (
parasite CHAR(1) PRIMARY KEY NOT NULL CONSTRAINT "parasite: Cannot be empty or only whitespace characters"
CHECK(btrim(parasite, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON parasites TO GROUP babase_readers;
GRANT SELECT ON parasites TO GROUP babase_editors;
GRANT INSERT ON parasites TO GROUP babase_editors;
GRANT UPDATE ON parasites TO GROUP babase_editors;
GRANT DELETE ON parasites TO GROUP babase_editors;
SELECT 'parasites' AS done_with;
CREATE TABLE dart_sample_cats (
ds_cat SMALLINT PRIMARY KEY NOT NULL ,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON dart_sample_cats TO GROUP babase_readers;
GRANT SELECT ON dart_sample_cats TO GROUP babase_editors;
GRANT INSERT ON dart_sample_cats TO GROUP babase_editors;
GRANT UPDATE ON dart_sample_cats TO GROUP babase_editors;
GRANT DELETE ON dart_sample_cats TO GROUP babase_editors;
SELECT 'dart_sample_cats' AS done_with;
CREATE TABLE tconditions (
tcondition CHAR(1) PRIMARY KEY NOT NULL CONSTRAINT "tcondition: Cannot be empty or only whitespace characters"
CHECK(btrim(tcondition, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON tconditions TO GROUP babase_readers;
GRANT SELECT ON tconditions TO GROUP babase_editors;
GRANT INSERT ON tconditions TO GROUP babase_editors;
GRANT UPDATE ON tconditions TO GROUP babase_editors;
GRANT DELETE ON tconditions TO GROUP babase_editors;
SELECT 'tconditions' AS done_with;
CREATE TABLE tickstatuses (
tickstatus SMALLINT PRIMARY KEY NOT NULL ,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON tickstatuses TO GROUP babase_readers;
GRANT SELECT ON tickstatuses TO GROUP babase_editors;
GRANT INSERT ON tickstatuses TO GROUP babase_editors;
GRANT UPDATE ON tickstatuses TO GROUP babase_editors;
GRANT DELETE ON tickstatuses TO GROUP babase_editors;
SELECT 'tickstatuses' AS done_with;
CREATE TABLE toothsites (
toothsite SMALLINT PRIMARY KEY NOT NULL ,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON toothsites TO GROUP babase_readers;
GRANT SELECT ON toothsites TO GROUP babase_editors;
GRANT INSERT ON toothsites TO GROUP babase_editors;
GRANT UPDATE ON toothsites TO GROUP babase_editors;
GRANT DELETE ON toothsites TO GROUP babase_editors;
SELECT 'toothsites' AS done_with;
CREATE TABLE tstates (
tstate CHAR(1) PRIMARY KEY NOT NULL CONSTRAINT "tstate: Cannot be empty or only whitespace characters"
CHECK(btrim(tstate, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON tstates TO GROUP babase_readers;
GRANT SELECT ON tstates TO GROUP babase_editors;
GRANT INSERT ON tstates TO GROUP babase_editors;
GRANT UPDATE ON tstates TO GROUP babase_editors;
GRANT DELETE ON tstates TO GROUP babase_editors;
SELECT 'tstates' AS done_with;
-- swerb
CREATE TABLE swerb_loc_confidences (
confidence NUMERIC(2,1) PRIMARY KEY NOT NULL ,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON swerb_loc_confidences TO GROUP babase_readers;
GRANT SELECT ON swerb_loc_confidences TO GROUP babase_editors;
GRANT INSERT ON swerb_loc_confidences TO GROUP babase_editors;
GRANT UPDATE ON swerb_loc_confidences TO GROUP babase_editors;
GRANT DELETE ON swerb_loc_confidences TO GROUP babase_editors;
SELECT 'swerb_loc_confidences' AS done_with;
CREATE TABLE swerb_loc_statuses (
loc_status CHAR(1) PRIMARY KEY NOT NULL CONSTRAINT "loc_status: Cannot be empty or only whitespace characters"
CHECK(btrim(loc_status, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON swerb_loc_statuses TO GROUP babase_readers;
GRANT SELECT ON swerb_loc_statuses TO GROUP babase_editors;
GRANT INSERT ON swerb_loc_statuses TO GROUP babase_editors;
GRANT UPDATE ON swerb_loc_statuses TO GROUP babase_editors;
GRANT DELETE ON swerb_loc_statuses TO GROUP babase_editors;
SELECT 'swerb_loc_statuses' AS done_with;
CREATE TABLE swerb_time_sources (
source VARCHAR(5) PRIMARY KEY NOT NULL CONSTRAINT "source: Cannot be empty or only whitespace characters"
CHECK(btrim(source, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON swerb_time_sources TO GROUP babase_readers;
GRANT SELECT ON swerb_time_sources TO GROUP babase_editors;
GRANT INSERT ON swerb_time_sources TO GROUP babase_editors;
GRANT UPDATE ON swerb_time_sources TO GROUP babase_editors;
GRANT DELETE ON swerb_time_sources TO GROUP babase_editors;
SELECT 'swerb_time_sources' AS done_with;
CREATE TABLE place_types (
place CHAR(1) PRIMARY KEY NOT NULL CONSTRAINT "place: Cannot be empty or only whitespace characters"
CHECK(btrim(place, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON place_types TO GROUP babase_readers;
GRANT SELECT ON place_types TO GROUP babase_editors;
GRANT INSERT ON place_types TO GROUP babase_editors;
GRANT UPDATE ON place_types TO GROUP babase_editors;
GRANT DELETE ON place_types TO GROUP babase_editors;
SELECT 'place_types' AS done_with;
CREATE TABLE swerb_xysources (
xysource CHAR(1) PRIMARY KEY NOT NULL CONSTRAINT "XYSource: Cannot be empty or only whitespace characters"
CHECK(btrim(XYSource, E' \r\n\t\f\x0B') <> '')
CONSTRAINT "quad is not an allowed XYSource value"
CHECK(xysource <> 'quad'),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON swerb_xysources TO GROUP babase_readers;
GRANT SELECT ON swerb_xysources TO GROUP babase_editors;
GRANT INSERT ON swerb_xysources TO GROUP babase_editors;
GRANT UPDATE ON swerb_xysources TO GROUP babase_editors;
GRANT DELETE ON swerb_xysources TO GROUP babase_editors;
SELECT 'swerb_xysources' AS done_with;
CREATE TABLE dad_data_completeness (
completeness SMALLINT PRIMARY KEY NOT NULL ,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON dad_data_completeness TO GROUP babase_readers;
GRANT SELECT ON dad_data_completeness TO GROUP babase_editors;
GRANT INSERT ON dad_data_completeness TO GROUP babase_editors;
GRANT UPDATE ON dad_data_completeness TO GROUP babase_editors;
GRANT DELETE ON dad_data_completeness TO GROUP babase_editors;
SELECT 'dad_data_completeness' AS done_with;
CREATE TABLE dad_data_mismatches (
mismatch VARCHAR(12) PRIMARY KEY NOT NULL CONSTRAINT "mismatch: Cannot be empty or only whitespace characters"
CHECK(btrim(mismatch, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON dad_data_mismatches TO GROUP babase_readers;
GRANT SELECT ON dad_data_mismatches TO GROUP babase_editors;
GRANT INSERT ON dad_data_mismatches TO GROUP babase_editors;
GRANT UPDATE ON dad_data_mismatches TO GROUP babase_editors;
GRANT DELETE ON dad_data_mismatches TO GROUP babase_editors;
SELECT 'dad_data_mismatches' AS done_with;
CREATE TABLE hybridgene_software (
software SMALLINT PRIMARY KEY NOT NULL ,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON hybridgene_software TO GROUP babase_readers;
GRANT SELECT ON hybridgene_software TO GROUP babase_editors;
GRANT INSERT ON hybridgene_software TO GROUP babase_editors;
GRANT UPDATE ON hybridgene_software TO GROUP babase_editors;
GRANT DELETE ON hybridgene_software TO GROUP babase_editors;
SELECT 'hybridgene_software' AS done_with;
CREATE TABLE markers (
marker SMALLINT PRIMARY KEY NOT NULL ,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON markers TO GROUP babase_readers;
GRANT SELECT ON markers TO GROUP babase_editors;
GRANT INSERT ON markers TO GROUP babase_editors;
GRANT UPDATE ON markers TO GROUP babase_editors;
GRANT DELETE ON markers TO GROUP babase_editors;
SELECT 'markers' AS done_with;
CREATE TABLE deathnatures (
nature VARCHAR(30) PRIMARY KEY NOT NULL ,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON deathnatures TO GROUP babase_readers;
GRANT SELECT ON deathnatures TO GROUP babase_editors;
GRANT INSERT ON deathnatures TO GROUP babase_editors;
GRANT UPDATE ON deathnatures TO GROUP babase_editors;
GRANT DELETE ON deathnatures TO GROUP babase_editors;
SELECT 'deathnatures' AS done_with;
CREATE TABLE confidences (
confidence SMALLINT PRIMARY KEY NOT NULL,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> ''),
usage TEXT
CONSTRAINT "usage: Cannot be empty or only whitespace characters"
CHECK(btrim(usage, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON confidences TO GROUP babase_readers;
GRANT SELECT ON confidences TO GROUP babase_editors;
GRANT INSERT ON confidences TO GROUP babase_editors;
GRANT UPDATE ON confidences TO GROUP babase_editors;
GRANT DELETE ON confidences TO GROUP babase_editors;
SELECT 'confidences' AS done_with;
CREATE TABLE weatherhawk_softwares (
wsoftware VARCHAR(3) PRIMARY KEY NOT NULL
CONSTRAINT "wsoftware: Cannot be empty or only whitespace characters"
CHECK(btrim(wsoftware, E' \r\n\t\f\x0B') <> ''),
name TEXT NOT NULL
CONSTRAINT "name: Cannot be empty or only whitespace characters"
CHECK(btrim(name, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON weatherhawk_softwares TO GROUP babase_readers;
GRANT SELECT ON weatherhawk_softwares TO GROUP babase_editors;
GRANT INSERT ON weatherhawk_softwares TO GROUP babase_editors;
GRANT UPDATE ON weatherhawk_softwares TO GROUP babase_editors;
GRANT DELETE ON weatherhawk_softwares TO GROUP babase_editors;
SELECT 'weatherhawk_softwares' AS done_with;
--
-- The primary tables
--
-- Note that we don't have to grant execute privileges on the functions
-- we use in triggers as the triggers execute with our (the database
-- owner's) permissions.
-- dcauses
CREATE TABLE dcauses (
dcause SMALLINT PRIMARY KEY NOT NULL,
nature VARCHAR(30) NOT NULL
-- Using VARCHAR and not SMALLINT because leadership didn't
-- like the look of DCAUSES when "nature" was just an
-- integer from another support table. For similar reasons,
-- 30 character length was chosen because one of the
-- intended nature values really is that long, and
-- apparently no one wants to use an abbreviation and
-- require people to actually look at the DEATHNATURES
-- table for more details.
CONSTRAINT "Nature on DEATHNATURES" REFERENCES deathnatures,
agent TEXT NOT NULL
CONSTRAINT "agent: Cannot be empty or only whitespace characters"
CHECK(btrim(agent, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON dcauses TO GROUP babase_readers;
GRANT SELECT ON dcauses TO GROUP babase_editors;
GRANT INSERT ON dcauses TO GROUP babase_editors;
GRANT UPDATE ON dcauses TO GROUP babase_editors;
GRANT DELETE ON dcauses TO GROUP babase_editors;
SELECT 'dcauses' AS done_with;
-- observers
CREATE TABLE observers (
initials VARCHAR(4) PRIMARY KEY NOT NULL
CONSTRAINT "Initials: Cannot be empty or only whitespace characters"
CHECK(btrim(Initials, E' \r\n\t\f\x0B') <> ''),
oldgpsinitials TEXT NULL
CONSTRAINT "Oldgpsinitials: Cannot be empty or only whitespace characters"
CHECK(btrim(Oldgpsinitials, E' \r\n\t\f\x0B') <> ''),
name TEXT NOT NULL
CONSTRAINT "name: Cannot be empty or only whitespace characters"
CHECK(btrim(name, E' \r\n\t\f\x0B') <> ''),
timeframe TEXT NULL
CONSTRAINT "timeframe: Cannot be empty or only whitespace characters"
CHECK(btrim(timeframe, E' \r\n\t\f\x0B') <> ''),
notes TEXT NULL
CONSTRAINT "notes: Cannot be empty or only whitespace characters"
CHECK(btrim(notes, E' \r\n\t\f\x0B') <> ''),
role VARCHAR(75) NOT NULL
CONSTRAINT "Role on OBSERVER_ROLES" REFERENCES observer_roles,
swerb_observer_role VARCHAR(75) NOT NULL
CONSTRAINT "SWERB_Observer_Role on OBSERVER_ROLES" REFERENCES observer_roles,
swerb_driver_role VARCHAR(75) NOT NULL
CONSTRAINT "SWERB_Driver_Role on OBSERVER_ROLES" REFERENCES observer_roles
) WITHOUT OIDS;
GRANT SELECT ON observers TO GROUP babase_readers;
GRANT SELECT ON observers TO GROUP babase_editors;
GRANT INSERT ON observers TO GROUP babase_editors;
GRANT UPDATE ON observers TO GROUP babase_editors;
GRANT DELETE ON observers TO GROUP babase_editors;
SELECT 'observers' AS done_with;
-- acts
CREATE TABLE acts (
act CHAR(2) PRIMARY KEY NOT NULL
CONSTRAINT "act: Cannot be empty or only whitespace characters"
CHECK(btrim(act, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> ''),
class CHAR(1) NOT NULL
CONSTRAINT "Class on ACTS" REFERENCES acts,
retired BOOLEAN NOT NULL,
CONSTRAINT "Class one of A, G, M, C, E, P, or R"
CHECK(class = 'A'
OR class = 'G'
OR class = 'M'
OR class = 'C'
OR class = 'E'
OR class = 'P'
OR class = 'R')
) WITHOUT OIDS;
GRANT SELECT ON acts TO GROUP babase_readers;
GRANT SELECT ON acts TO GROUP babase_editors;
GRANT INSERT ON acts TO GROUP babase_editors;
GRANT UPDATE ON acts TO GROUP babase_editors;
GRANT DELETE ON acts TO GROUP babase_editors;
SELECT 'acts' AS done_with;
-- rnktypes
CREATE TABLE rnktypes (
rnktype VARCHAR(6) PRIMARY KEY NOT NULL
CONSTRAINT "rnktype: Cannot be empty or only whitespace characters"
CHECK(btrim(rnktype, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL,
query TEXT NOT NULL
) WITHOUT OIDS;
GRANT SELECT ON rnktypes TO GROUP babase_readers;
GRANT SELECT ON rnktypes TO GROUP babase_editors;
GRANT INSERT ON rnktypes TO GROUP babase_editors;
GRANT UPDATE ON rnktypes TO GROUP babase_editors;
GRANT DELETE ON rnktypes TO GROUP babase_editors;
SELECT 'rnktypes' AS done_with;
-- foodcodes
CREATE TABLE foodcodes (
fcode VARCHAR(3) PRIMARY KEY NOT NULL
CONSTRAINT "fcode: Cannot be empty or only whitespace characters"
CHECK(btrim(fcode, E' \r\n\t\f\x0B') <> ''),
ftype CHAR(1) NOT NULL
CONSTRAINT "Ftype on FOODTYPES" REFERENCES foodtypes,
descr TEXT NOT NULL
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON foodcodes TO GROUP babase_readers;
GRANT SELECT ON foodcodes TO GROUP babase_editors;
GRANT INSERT ON foodcodes TO GROUP babase_editors;
GRANT UPDATE ON foodcodes TO GROUP babase_editors;
GRANT DELETE ON foodcodes TO GROUP babase_editors;
SELECT 'foodcodes' AS done_with;
-- activities
CREATE TABLE activities(
activity CHAR(1) PRIMARY KEY NOT NULL
CONSTRAINT "activity: Cannot be empty or only whitespace characters"
CHECK(btrim(activity, E' \r\n\t\f\x0B') <> ''),
juvenile BOOLEAN NOT NULL,
female BOOLEAN NOT NULL,
descr TEXT NOT NULL
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON activities TO GROUP babase_readers;
GRANT SELECT ON activities TO GROUP babase_editors;
GRANT INSERT ON activities TO GROUP babase_editors;
GRANT UPDATE ON activities TO GROUP babase_editors;
GRANT DELETE ON activities TO GROUP babase_editors;
SELECT 'activities' AS done_with;
-- ncodes
CREATE TABLE ncodes(
ncode CHAR(1) PRIMARY KEY NOT NULL
CONSTRAINT "ncode: Cannot be empty or only whitespace characters"
CHECK(btrim(ncode, E' \r\n\t\f\x0B') <> ''),
juvenile BOOLEAN NOT NULL,
female BOOLEAN NOT NULL,
requires CHAR(1) NULL
CONSTRAINT "Requires on NCODES" REFERENCES ncodes,
nsex CHAR(1) NOT NULL
CONSTRAINT "Nsex one of A, M, O"
CHECK (nsex = 'A'
OR nsex = 'M'
OR nsex = 'O'),
nunique BOOLEAN NOT NULL,
descr TEXT NOT NULL
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> ''),
CONSTRAINT "Requires can't be NULL when Nsex is O"
CHECK (NOT(nsex = 'O' AND requires IS NULL))
) WITHOUT OIDS;
GRANT SELECT ON ncodes TO GROUP babase_readers;
GRANT SELECT ON ncodes TO GROUP babase_editors;
GRANT INSERT ON ncodes TO GROUP babase_editors;
GRANT UPDATE ON ncodes TO GROUP babase_editors;
GRANT DELETE ON ncodes TO GROUP babase_editors;
SELECT 'ncodes' AS done_with;
-- unksnames
CREATE TABLE unksnames (
unksname CHAR(3) PRIMARY KEY NOT NULL
CONSTRAINT "unksname: Cannot be empty or only whitespace characters"
CHECK(btrim(unksname, E' \r\n\t\f\x0B') <> ''),
lonemale BOOLEAN NOT NULL,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON unksnames TO GROUP babase_readers;
GRANT SELECT ON unksnames TO GROUP babase_editors;
GRANT INSERT ON unksnames TO GROUP babase_editors;
GRANT UPDATE ON unksnames TO GROUP babase_editors;
GRANT DELETE ON unksnames TO GROUP babase_editors;
SELECT 'unksnames' AS done_with;
-- postures
CREATE TABLE postures (
posture CHAR(1) PRIMARY KEY NOT NULL
CONSTRAINT "posture: Cannot be empty or only whitespace characters"
CHECK(btrim(posture, E' \r\n\t\f\x0B') <> ''),
juvonly BOOLEAN NOT NULL,
descr TEXT NOT NULL
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON postures TO GROUP babase_readers;
GRANT SELECT ON postures TO GROUP babase_editors;
GRANT INSERT ON postures TO GROUP babase_editors;
GRANT UPDATE ON postures TO GROUP babase_editors;
GRANT DELETE ON postures TO GROUP babase_editors;
SELECT 'postures' AS done_with;
-- setupids
CREATE TABLE setupids (
setupid SMALLINT PRIMARY KEY NOT NULL,
sid_string VARCHAR(32) NOT NULL
CONSTRAINT "sid_string: Cannot be empty or only whitespace characters"
CHECK(btrim(sid_string, E' \r\n\t\f\x0B') <> ''),
data_structure SMALLINT NOT NULL
CONSTRAINT "Data_Structure on DATA_STRUCTURES"
REFERENCES data_structures,
descr TEXT NOT NULL
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON setupids TO GROUP babase_readers;
GRANT SELECT ON setupids TO GROUP babase_editors;
GRANT INSERT ON setupids TO GROUP babase_editors;
GRANT UPDATE ON setupids TO GROUP babase_editors;
GRANT DELETE ON setupids TO GROUP babase_editors;
SELECT 'setupids' AS done_with;
-- programids
CREATE TABLE programids (
programid SMALLINT PRIMARY KEY NOT NULL,
pid_string VARCHAR(32) NOT NULL
CONSTRAINT "pid_string: Cannot be empty or only whitespace characters"
CHECK(btrim(pid_string, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON programids TO GROUP babase_readers;
GRANT SELECT ON programids TO GROUP babase_editors;
GRANT INSERT ON programids TO GROUP babase_editors;
GRANT UPDATE ON programids TO GROUP babase_editors;
GRANT DELETE ON programids TO GROUP babase_editors;
SELECT 'programids' AS done_with;
-- dad_software
CREATE TABLE dad_software (
software VARCHAR(10) PRIMARY KEY,
version TEXT NULL
CONSTRAINT "version: Cannot be empty or only whitespace characters"
CHECK(btrim(version, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON dad_software TO GROUP babase_readers;
GRANT SELECT ON dad_software TO GROUP babase_editors;
GRANT INSERT ON dad_software TO GROUP babase_editors;
GRANT UPDATE ON dad_software TO GROUP babase_editors;
GRANT DELETE ON dad_software TO GROUP babase_editors;
SELECT 'dad_software' AS done_with;
-- Bodyparts
CREATE TABLE bodyparts (
bpid VARCHAR(8) PRIMARY KEY
CONSTRAINT "bpid: Cannot be empty or only whitespace characters"
CHECK(btrim(bpid, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL
CONSTRAINT "Descr: Cannot be empty or only whitespace characters"
CHECK(btrim(Descr, E' \r\n\t\f\x0B') <> ''),
bodyside CHAR(1) NULL
CONSTRAINT "BODYPARTS.Bodyside must be L, R, C, or NULL"
CHECK (bodyside IS NULL
OR bodyside = 'L'
OR bodyside = 'R'
OR bodyside = 'C'),
innerouter CHAR(1) NULL
CONSTRAINT "BODYPARTS.Innerouter must be I, O, or NULL"
CHECK (innerouter IS NULL
OR innerouter = 'I'
OR innerouter = 'O'),
bodyregion VARCHAR(8) NOT NULL
CONSTRAINT "Bodyregion on BODYPARTS" REFERENCES bodyparts
) WITHOUT OIDS;
GRANT SELECT ON bodyparts TO GROUP babase_readers;
GRANT SELECT ON bodyparts TO GROUP babase_editors;
GRANT INSERT ON bodyparts TO GROUP babase_editors;
GRANT UPDATE ON bodyparts TO GROUP babase_editors;
GRANT DELETE ON bodyparts TO GROUP babase_editors;
SELECT 'bodyparts' AS done_with;
-- Toothcodes
CREATE TABLE toothcodes (
tooth VARCHAR(5) PRIMARY KEY
CONSTRAINT "tooth: Cannot be empty or only whitespace characters"
CHECK(btrim(tooth, E' \r\n\t\f\x0B') <> ''),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> ''),
canine BOOLEAN NOT NULL,
deciduous BOOLEAN NOT NULL,
toothsite SMALLINT NOT NULL
CONSTRAINT "TOOTHCODES.Toothsite on TOOTHSITES"
REFERENCES toothsites
) WITHOUT OIDS;
GRANT SELECT ON toothcodes TO GROUP babase_readers;
GRANT SELECT ON toothcodes TO GROUP babase_editors;
GRANT INSERT ON toothcodes TO GROUP babase_editors;
GRANT UPDATE ON toothcodes TO GROUP babase_editors;
GRANT DELETE ON toothcodes TO GROUP babase_editors;
SELECT 'toothcodes' AS done_with;
-- adcodes
CREATE TABLE adcodes (
adcode VARCHAR(2) PRIMARY KEY
CONSTRAINT "adcode: Cannot be empty or only whitespace characters"
CHECK(btrim(adcode, E' \r\n\t\f\x0B') <> ''),
adn CHAR(1) NOT NULL
CONSTRAINT "ADN must be one of: D, A, N"
CHECK (adn = 'D'
OR adn = 'A'
OR adn = 'N'),
time BOOLEAN NOT NULL,
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON adcodes TO GROUP babase_readers;
GRANT SELECT ON adcodes TO GROUP babase_editors;
GRANT INSERT ON adcodes TO GROUP babase_editors;
GRANT UPDATE ON adcodes TO GROUP babase_editors;
GRANT DELETE ON adcodes TO GROUP babase_editors;
SELECT 'adcodes' AS done_with;
-- aerials
CREATE TABLE aerials (
aerial SMALLINT PRIMARY KEY
CONSTRAINT "Aerial must be >= 1"
CHECK (aerial >= 1),
date DATE NOT NULL
) WITHOUT OIDS;
GRANT SELECT ON aerials TO GROUP babase_readers;
GRANT SELECT ON aerials TO GROUP babase_editors;
GRANT INSERT ON aerials TO GROUP babase_editors;
GRANT UPDATE ON aerials TO GROUP babase_editors;
GRANT DELETE ON aerials TO GROUP babase_editors;
SELECT 'aerials' AS done_with;
-- quad_data
CREATE TABLE quad_data (
quad VARCHAR(4) PRIMARY KEY
CONSTRAINT "quad: Cannot be empty or only whitespace characters"
CHECK(btrim(quad, E' \r\n\t\f\x0B') <> ''),
xyloc GEOMETRY(POINT, 32737) NULL
CONSTRAINT "X coord of XYLoc must have only 1 digit right of the decimal"
CHECK(ST_X(xyloc) * 10 = TRUNC(ST_X(xyloc) * 10))
CONSTRAINT "Y coord of XYLoc must have only 1 digit right of the decimal"
CHECK(ST_Y(xyloc) * 10 = TRUNC(ST_Y(xyloc) * 10))
CONSTRAINT "XYLoc within study area (see Babase Technical Specifications)"
CHECK(ST_Intersects(xyloc
, ST_PolygonFromText('POLYGON((42300.0 9497000.0, 651000.0 9497000.0, 651000.0 9894500.0, 42300.0 9894500.0, 42300.0 9497000.0))'
, 32737))),
aerial SMALLINT NULL
CONSTRAINT "Aerial on AERIALS" REFERENCES aerials
) WITHOUT OIDS;
GRANT SELECT ON quad_data TO GROUP babase_readers;
GRANT SELECT ON quad_data TO GROUP babase_editors;
GRANT INSERT ON quad_data TO GROUP babase_editors;
GRANT UPDATE ON quad_data TO GROUP babase_editors;
GRANT DELETE ON quad_data TO GROUP babase_editors;
SELECT 'quad_data' AS done_with;
-- gps_units
CREATE TABLE gps_units (
gps NUMERIC(2) PRIMARY KEY
CONSTRAINT "GPS cannot be negative"
CHECK(gps = ABS(gps)),
descr TEXT NOT NULL
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> ''),
make VARCHAR(100) NOT NULL
CONSTRAINT "make: Cannot be empty or only whitespace characters"
CHECK(btrim(make, E' \r\n\t\f\x0B') <> ''),
model VARCHAR(100) NOT NULL
CONSTRAINT "model: Cannot be empty or only whitespace characters"
CHECK(btrim(model, E' \r\n\t\f\x0B') <> ''),
errortype VARCHAR(8) NOT NULL
CONSTRAINT "Errortype must be either 'PDOP' or 'accuracy'"
CHECK(errortype = 'PDOP'
OR errortype = 'accuracy'),
label CHAR(1) NOT NULL
CONSTRAINT "label: Cannot be empty or only whitespace characters"
CHECK(btrim(label, E' \r\n\t\f\x0B') <> ''),
start DATE NOT NULL
CONSTRAINT "Start cannot be before 1993-09-01"
CHECK(start >= '1993-09-01'),
finish DATE NULL
CONSTRAINT "Finish cannot be before Start"
CHECK(finish >= start)
) WITHOUT OIDS;
GRANT SELECT ON gps_units TO GROUP babase_readers;
GRANT SELECT ON gps_units TO GROUP babase_editors;
GRANT INSERT ON gps_units TO GROUP babase_editors;
GRANT UPDATE ON gps_units TO GROUP babase_editors;
GRANT DELETE ON gps_units TO GROUP babase_editors;
SELECT 'gps_units' AS done_with;
-- groups
CREATE TABLE groups (
gid NUMERIC(6,4) PRIMARY KEY,
name VARCHAR(32)
CONSTRAINT "name: Cannot be empty or only whitespace characters"
CHECK(btrim(name, E' \r\n\t\f\x0B') <> ''),
from_group NUMERIC(6,4)
CONSTRAINT "From_group on GROUPS" REFERENCES groups,
to_group NUMERIC(6,4)
CONSTRAINT "To_group on GROUPS" REFERENCES groups,
permanent DATE NULL
CONSTRAINT "Permanent cannot be before Start"
CHECK(permanent IS NULL
OR start IS NULL
OR start <= permanent),
start DATE,
cease_to_exist DATE
CONSTRAINT "Cease_To_Exist must be after Start"
CHECK(cease_to_exist IS NULL
OR start IS NULL
OR start < cease_to_exist)
CONSTRAINT "Cease_To_Exist must be after or same day as Last_Reg_Census"
CHECK(cease_to_exist IS NULL
OR last_reg_census IS NULL
OR last_reg_census <= cease_to_exist),
last_reg_census DATE
CONSTRAINT "Last_Reg_Census must be after Start"
CHECK(last_reg_census IS NULL
OR start IS NULL
OR start < last_reg_census),
three_letter_code CHAR(3) NULL
CONSTRAINT "Three_letter_code must be uppercase"
CHECK(three_letter_code = UPPER(three_letter_code)),
one_letter_code CHAR(1) NULL
CONSTRAINT "One_letter_code must be uppercase"
CHECK(one_letter_code = UPPER(one_letter_code)),
study_grp DATE NULL
CONSTRAINT "Study_Grp cannot be before the group's Start"
CHECK(study_grp IS NULL
OR study_grp >= start)
CONSTRAINT "Cease_To_Exist cannot be before Study_Grp"
CHECK(cease_to_exist IS NULL
OR study_grp IS NULL
OR cease_to_exist >= study_grp)
CONSTRAINT "Last_Reg_Census cannot be before Study_Grp"
CHECK(last_reg_census IS NULL
OR study_grp IS NULL
OR study_grp <= last_reg_census)
CONSTRAINT "Last_Reg_Census must be NULL when Study_Grp is NULL"
CHECK(study_grp IS NOT NULL
OR last_reg_census IS NULL)
CONSTRAINT "The From_group may not be the same as the Gid"
CHECK(from_group <> gid),
CONSTRAINT "Non-Permanent groups must have a non-NULL From_group"
CHECK(permanent IS NOT NULL OR from_group IS NOT NULL)
) WITHOUT OIDS;
GRANT SELECT ON groups TO GROUP babase_readers;
GRANT SELECT ON groups TO GROUP babase_editors;
GRANT INSERT ON groups TO GROUP babase_editors;
GRANT UPDATE ON groups TO GROUP babase_editors;
GRANT DELETE ON groups TO GROUP babase_editors;
SELECT 'groups' AS done_with;
-- biograph
CREATE TABLE biograph (
bioid SERIAL PRIMARY KEY,
sname CHAR(3) UNIQUE,
CONSTRAINT "Sname must be uppercase"
CHECK(sname = UPPER(sname)),
CONSTRAINT "Sname must be 3 characters"
CHECK(sname IS NULL OR char_length(sname) = 3),
CONSTRAINT "Sname must not be 998"
CHECK(sname IS NULL OR sname <> '998'),
name VARCHAR(32),
CONSTRAINT "Name required when Sname not NULL (not abortion)"
CHECK(sname IS NULL OR name IS NOT NULL),
CONSTRAINT "name: Cannot be empty or only whitespace characters"
CHECK(btrim(name, E' \r\n\t\f\x0B') <> ''),
pid VARCHAR(6)
CONSTRAINT "pid: Cannot be empty or only whitespace characters"
CHECK(btrim(pid, E' \r\n\t\f\x0B') <> ''),
-- References PREGS is added below after pregs is created.
birth DATE NOT NULL,
CONSTRAINT "Birth <= Statdate" CHECK(birth <= statdate),
bstatus NUMERIC(2,1) NOT NULL
CONSTRAINT "Bstatus on BSTATUSES" REFERENCES bstatuses,
sex CHAR(1) NOT NULL
CONSTRAINT "Sex one of M, F, or U"
CHECK(sex = 'M' OR sex = 'F' OR sex = 'U'),
matgrp NUMERIC(6,4) NOT NULL
CONSTRAINT "Matgrp on GROUPS" REFERENCES groups,
matgrpconfidence
SMALLINT NOT NULL
CONSTRAINT "Matgrpconfidence on CONFIDENCES"
REFERENCES confidences,
statdate DATE NOT NULL,
CONSTRAINT "Statdate = Birth when Sname is NULL (abortion)"
CHECK((sname IS NULL AND statdate = birth)
OR sname IS NOT NULL),
status SMALLINT NOT NULL
CONSTRAINT "Status on STATUSES" REFERENCES statuses,
CONSTRAINT "Status <> 0 (alive) when Sname is NULL (abortion)"
CHECK((sname IS NULL AND status <> 0) OR sname IS NOT NULL),
CONSTRAINT "Dcause for live animal, or no dcause for dead animal"
CHECK((status = 0 AND dcause = 0)
OR (status <> 0 AND dcause <> 0)) ,
dcause SMALLINT NOT NULL
CONSTRAINT "Dcause on DCAUSES" REFERENCES dcauses,
alt_snames BOOLEAN NOT NULL,
entrydate DATE
CONSTRAINT "Entrydate and Sname must both be NULL or both non-NULL"
CHECK((entrydate IS NULL AND sname IS NULL)
OR (entrydate IS NOT NULL AND sname IS NOT NULL))
CONSTRAINT "Entrydate <= Statdate" CHECK(entrydate <= statdate)
CONSTRAINT "Entrydate >= Birth" CHECK(entrydate >= birth),
entrytype CHAR(1)
CONSTRAINT "Entrytype and Sname must both be NULL or both non-NULL"
CHECK((entrytype IS NULL AND sname IS NULL)
OR (entrytype IS NOT NULL AND sname IS NOT NULL))
CONSTRAINT "Entrytype on ENTRYTYPES" REFERENCES entrytypes,
dcausenatureconfidence SMALLINT NOT NULL
CONSTRAINT "DcauseNatureConfidence on CONFIDENCES" REFERENCES confidences,
CONSTRAINT "BIOGRAPH.DcauseNatureConfidence must be 0 when Dcause is 0"
CHECK(NOT(dcause = 0 AND dcausenatureconfidence <> 0)),
dcauseagentconfidence SMALLINT NOT NULL
CONSTRAINT "DcauseAgentConfidence on CONFIDENCES" REFERENCES confidences,
CONSTRAINT "BIOGRAPH.DcauseAgentConfidence must be 0 when Dcause is 0"
CHECK(NOT(dcause = 0 AND dcauseagentconfidence <> 0)),
CONSTRAINT "Birth = Entrydate when Entrytype = 'B' (Birth)"
CHECK((birth = entrydate AND entrytype = 'B')
OR entrytype <> 'B'),
CONSTRAINT "Birth <> Entrydate when Entrytype <> 'B' (Birth)"
CHECK((birth <> entrydate AND entrytype <> 'B')
OR entrytype = 'B')
) WITHOUT OIDS;
GRANT SELECT ON biograph TO GROUP babase_readers;
GRANT SELECT ON biograph TO GROUP babase_editors;
GRANT INSERT ON biograph TO GROUP babase_editors;
GRANT UPDATE ON biograph TO GROUP babase_editors;
GRANT DELETE ON biograph TO GROUP babase_editors;
SELECT 'biograph' AS done_with;
GRANT SELECT ON biograph_bioid_seq TO GROUP babase_readers;
GRANT SELECT ON biograph_bioid_seq TO GROUP babase_editors;
GRANT UPDATE ON biograph_bioid_seq TO GROUP babase_editors;
SELECT 'biograph_bioid_seq' AS done_with;
-- alternate_snames
create table alternate_snames (
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
sname_alternate CHAR(3) NOT NULL
CONSTRAINT "Sname_Alternate must be uppercase"
CHECK(sname_alternate = UPPER(sname_alternate))
CONSTRAINT "Sname_Alternate must be 3 characters"
CHECK(char_length(sname_alternate) = 3)
CONSTRAINT "Sname and Sname_Alternate must differ"
CHECK (sname <> sname_alternate)
CONSTRAINT "Sname_Alternate must not be 998"
CHECK(sname_alternate <> '998'),
name_alternate VARCHAR(32),
CONSTRAINT "name_alternate: Cannot be empty or only whitespace characters"
CHECK(btrim(name_alternate, E' \r\n\t\f\x0B') <> ''),
notes TEXT NOT NULL
CONSTRAINT "Notes: Cannot be empty or only whitespace characters"
CHECK(btrim(Notes, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON alternate_snames TO GROUP babase_readers;
GRANT SELECT ON alternate_snames TO GROUP babase_editors;
GRANT INSERT ON alternate_snames TO GROUP babase_editors;
GRANT UPDATE ON alternate_snames TO GROUP babase_editors;
GRANT DELETE ON alternate_snames TO GROUP babase_editors;
SELECT 'alternate_snames' AS done_with;
-- maturedates
CREATE TABLE maturedates (
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname) PRIMARY KEY,
matured DATE NOT NULL,
mstatus CHAR(1) NOT NULL
CONSTRAINT "Mstatus on MSTATUSES" REFERENCES mstatuses
) WITHOUT OIDS;
GRANT SELECT ON maturedates TO GROUP babase_readers;
GRANT SELECT ON maturedates TO GROUP babase_editors;
GRANT INSERT ON maturedates TO GROUP babase_editors;
GRANT UPDATE ON maturedates TO GROUP babase_editors;
GRANT DELETE ON maturedates TO GROUP babase_editors;
SELECT 'maturedates' AS done_with;
-- rankdates
CREATE TABLE rankdates (
sname CHAR(3) NOT NULL
CONSTRAINT "Sname on MATUREDATES" REFERENCES maturedates(sname) PRIMARY KEY,
ranked DATE NOT NULL
CONSTRAINT "RANKDATES.Ranked must fall on the first of the month"
CHECK (ranked = date_trunc('month', ranked)),
rstatus CHAR(1) NOT NULL
CONSTRAINT "Rstatus on MSTATUSES" REFERENCES mstatuses
) WITHOUT OIDS;
GRANT SELECT ON rankdates TO GROUP babase_readers;
GRANT SELECT ON rankdates TO GROUP babase_editors;
GRANT INSERT ON rankdates TO GROUP babase_editors;
GRANT UPDATE ON rankdates TO GROUP babase_editors;
GRANT DELETE ON rankdates TO GROUP babase_editors;
SELECT 'rankdates' AS done_with;
-- consortdates
CREATE TABLE consortdates (
sname CHAR(3) NOT NULL
CONSTRAINT "Sname on MATUREDATES" REFERENCES maturedates(sname) PRIMARY KEY,
consorted DATE NOT NULL
) WITHOUT OIDS;
GRANT SELECT ON consortdates TO GROUP babase_readers;
GRANT SELECT ON consortdates TO GROUP babase_editors;
GRANT INSERT ON consortdates TO GROUP babase_editors;
GRANT UPDATE ON consortdates TO GROUP babase_editors;
GRANT DELETE ON consortdates TO GROUP babase_editors;
SELECT 'consortdates' AS done_with;
-- dispersedates
CREATE TABLE dispersedates (
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname) PRIMARY KEY,
dispersed DATE NOT NULL,
dispconfidence SMALLINT NOT NULL
CONSTRAINT "Dispconfidence on CONFIDENCES" REFERENCES confidences
) WITHOUT OIDS;
GRANT SELECT ON dispersedates TO GROUP babase_readers;
GRANT SELECT ON dispersedates TO GROUP babase_editors;
GRANT INSERT ON dispersedates TO GROUP babase_editors;
GRANT UPDATE ON dispersedates TO GROUP babase_editors;
GRANT DELETE ON dispersedates TO GROUP babase_editors;
SELECT 'dispersedates' AS done_with;
-- pregs
CREATE TABLE pregs (
pid VARCHAR(6) PRIMARY KEY,
parity SMALLINT NULL
CONSTRAINT "Parity must be between 1 and 199, inclusive"
CHECK (parity >= 1 AND parity <= 199),
-- References CYCLES added below after cycles created.
conceive INT NOT NULL,
-- References CYCLES added below after cycles created.
resume INT
) WITHOUT OIDS;
GRANT SELECT ON pregs TO GROUP babase_readers;
GRANT SELECT ON pregs TO GROUP babase_editors;
GRANT INSERT ON pregs TO GROUP babase_editors;
GRANT UPDATE ON pregs TO GROUP babase_editors;
GRANT DELETE ON pregs TO GROUP babase_editors;
SELECT 'pregs' AS done_with;
-- Change biograph to know about pregs.
ALTER TABLE biograph
ADD CONSTRAINT "Pid on PREGS" FOREIGN KEY (pid) REFERENCES pregs;
-- cycgaps
CREATE TABLE cycgaps (
gapid SERIAL PRIMARY KEY,
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
code CHAR(1) NOT NULL
CONSTRAINT "Code one of S, E, or P"
CHECK (code = 'S'
OR code = 'E'
OR code = 'P'),
date DATE NOT NULL,
state CHAR(1) NULL
CONSTRAINT "State one of M, S, O, D, P, or L"
CHECK (state = 'M'
OR state = 'S'
OR state = 'O'
OR state = 'D'
OR state = 'P'
OR state = 'L'),
CONSTRAINT "State must be NULL when Code is E, and not NULL otherwise"
CHECK ((code = 'E' and state IS NULL)
OR state IS NOT NULL)
) WITHOUT OIDS;
GRANT SELECT ON cycgaps TO GROUP babase_readers;
GRANT SELECT ON cycgaps TO GROUP babase_editors;
GRANT INSERT ON cycgaps TO GROUP babase_editors;
GRANT UPDATE ON cycgaps TO GROUP babase_editors;
GRANT DELETE ON cycgaps TO GROUP babase_editors;
SELECT 'cycgaps' AS done_with;
GRANT SELECT ON cycgaps_gapid_seq TO GROUP babase_readers;
GRANT SELECT ON cycgaps_gapid_seq TO GROUP babase_editors;
GRANT UPDATE ON cycgaps_gapid_seq TO GROUP babase_editors;
SELECT 'cycgaps_gapid_seq' AS done_with;
-- cycgapdays
CREATE TABLE cycgapdays (
cgdid SERIAL PRIMARY KEY,
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
date DATE NOT NULL
) WITHOUT OIDS;
GRANT SELECT ON cycgapdays TO GROUP babase_readers;
GRANT SELECT ON cycgapdays TO GROUP babase_editors;
SELECT 'cycgapdays' AS done_with;
-- cycles
CREATE TABLE cycles (
cid SERIAL PRIMARY KEY,
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
seq INT NULL, -- triggers never allow NULL
series INT NULL -- triggers never allow NULL
) WITHOUT OIDS;
GRANT SELECT ON cycles TO GROUP babase_readers;
GRANT SELECT ON cycles TO GROUP babase_editors;
GRANT INSERT ON cycles TO GROUP babase_editors;
GRANT UPDATE ON cycles TO GROUP babase_editors;
GRANT DELETE ON cycles TO GROUP babase_editors;
SELECT 'cycles' AS done_with;
GRANT SELECT ON cycles_cid_seq TO GROUP babase_readers;
GRANT SELECT ON cycles_cid_seq TO GROUP babase_editors;
GRANT UPDATE ON cycles_cid_seq TO GROUP babase_editors;
SELECT 'cycles_cid_seq' AS done_with;
-- cycpoints
CREATE TABLE cycpoints (
cpid SERIAL PRIMARY KEY,
cid INT NOT NULL
CONSTRAINT "Cid on CYCLES" REFERENCES cycles,
date DATE NOT NULL,
edate DATE,
ldate DATE,
source CHAR(1) NOT NULL DEFAULT 'D'
CONSTRAINT "Source one of D, A, or E"
CHECK (source = 'D'
OR source = 'A'
OR source = 'E'),
code CHAR(1) NOT NULL
CONSTRAINT "Code one of M, T, or D"
CHECK (code = 'M'
OR code = 'T'
OR code = 'D'),
CONSTRAINT "Only Mdates may have Source = A"
CHECK (code = 'M' OR source <> 'A'),
CONSTRAINT "Edate <= date" CHECK (edate <= date),
CONSTRAINT "date <= Ldate" CHECK (date <= ldate)
) WITHOUT OIDS;
GRANT SELECT ON cycpoints TO GROUP babase_readers;
GRANT SELECT ON cycpoints TO GROUP babase_editors;
GRANT INSERT ON cycpoints TO GROUP babase_editors;
GRANT UPDATE ON cycpoints TO GROUP babase_editors;
GRANT DELETE ON cycpoints TO GROUP babase_editors;
SELECT 'cycpoints' AS done_with;
GRANT SELECT ON cycpoints_cpid_seq TO GROUP babase_readers;
GRANT SELECT ON cycpoints_cpid_seq TO GROUP babase_editors;
GRANT UPDATE ON cycpoints_cpid_seq TO GROUP babase_editors;
SELECT 'cycpoints_cpid_seq' AS done_with;
-- Change pregs to know about cycpoints.
ALTER TABLE pregs
ADD CONSTRAINT "Conceive on CYCPOINTS"
FOREIGN KEY (conceive) REFERENCES cycpoints;
ALTER TABLE pregs
ADD CONSTRAINT "Resume on CYCPOINTS"
FOREIGN KEY (resume) REFERENCES cycpoints;
-- sexskins
CREATE TABLE sexskins (
sxid SERIAL PRIMARY KEY,
date DATE NOT NULL,
size NUMERIC(3,1) NOT NULL
CONSTRAINT "Size must be between 0 and 20 inclusive"
CHECK(size >= 0 AND size <= 20)
CONSTRAINT "Size must be integral execpt for 0.5"
CHECK(size = TRUNC(size) OR size = 0.5),
cid INT NOT NULL CONSTRAINT "Cid on CYCLES" REFERENCES cycles
) WITHOUT OIDS;
GRANT SELECT ON sexskins TO GROUP babase_readers;
GRANT SELECT ON sexskins TO GROUP babase_editors;
GRANT INSERT ON sexskins TO GROUP babase_editors;
GRANT UPDATE ON sexskins TO GROUP babase_editors;
GRANT DELETE ON sexskins TO GROUP babase_editors;
SELECT 'sexskins' AS done_with;
GRANT SELECT ON sexskins_sxid_seq TO GROUP babase_readers;
GRANT SELECT ON sexskins_sxid_seq TO GROUP babase_editors;
GRANT UPDATE ON sexskins_sxid_seq TO GROUP babase_editors;
SELECT 'sexskins_sxid_seq' AS done_with;
-- pcskins
CREATE TABLE pcskins (
pcsid SERIAL PRIMARY KEY,
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
date DATE NOT NULL,
color CHAR(1) NOT NULL
CONSTRAINT "Color on PCSCOLORS" REFERENCES pcscolors
) WITHOUT OIDS;
GRANT SELECT ON pcskins TO GROUP babase_readers;
GRANT SELECT ON pcskins TO GROUP babase_editors;
GRANT INSERT ON pcskins TO GROUP babase_editors;
GRANT UPDATE ON pcskins TO GROUP babase_editors;
GRANT DELETE ON pcskins TO GROUP babase_editors;
SELECT 'pcskins' AS done_with;
GRANT SELECT ON pcskins_pcsid_seq TO GROUP babase_readers;
GRANT SELECT ON pcskins_pcsid_seq TO GROUP babase_editors;
GRANT UPDATE ON pcskins_pcsid_seq TO GROUP babase_editors;
SELECT 'pcskins_pcsid_seq' AS done_with;
-- repstats
CREATE TABLE repstats (
rid SERIAL PRIMARY KEY,
date DATE NOT NULL,
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
state CHAR(1) NOT NULL
CONSTRAINT "State one of P, C, or L"
CHECK(state = 'P' OR state = 'C' or state = 'L'),
dins INT NULL
CONSTRAINT "Dins >= 1"
CHECK(dins > 0 OR dins IS NULL),
dr INT NULL
CONSTRAINT "Dr >= 0"
CHECK(dr >= 0 OR dr IS NULL),
pid VARCHAR(6)
CONSTRAINT "Pid on PREGS" REFERENCES pregs
) WITHOUT OIDS;
GRANT SELECT ON repstats TO GROUP babase_readers;
GRANT SELECT ON repstats TO GROUP babase_editors;
SELECT 'repstats' AS done_with;
GRANT SELECT ON repstats_rid_seq TO GROUP babase_readers;
GRANT SELECT ON repstats_rid_seq TO GROUP babase_editors;
SELECT 'repstats_rid_seq' AS done_with;
-- cycstats
CREATE TABLE cycstats (
csid SERIAL PRIMARY KEY,
date DATE NOT NULL,
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
state CHAR(1) NOT NULL
CONSTRAINT "State one of M, S, O, or D"
CHECK(state = 'M' OR state = 'S' OR state = 'O' OR state = 'D'),
dins INT NULL
CONSTRAINT "Dins >= 1"
CHECK(dins > 0 OR dins IS NULL),
dr INT NULL
CONSTRAINT "Dr >= 0"
CHECK(dr >= 0 OR dr IS NULL),
cpids INT NULL
CONSTRAINT "Cpids on CYCPOINTS" REFERENCES cycpoints,
cpide INT NULL
CONSTRAINT "Cpide on CYCPOINTS" REFERENCES cycpoints
) WITHOUT OIDS;
GRANT SELECT ON cycstats TO GROUP babase_readers;
GRANT SELECT ON cycstats TO GROUP babase_editors;
SELECT 'cycstats' AS done_with;
GRANT SELECT ON cycstats_csid_seq TO GROUP babase_readers;
GRANT SELECT ON cycstats_csid_seq TO GROUP babase_editors;
SELECT 'cycstats_csid_seq' AS done_with;
-- mmintervals
CREATE TABLE mmintervals (
mmiid SERIAL PRIMARY KEY,
date DATE NOT NULL,
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
dini INT NULL
CONSTRAINT "Dini >= 1"
CHECK(dini > 0 OR dini IS NULL),
dr INT NULL
CONSTRAINT "Dr >= 0"
CHECK(dr >= 0 OR dr IS NULL),
cpids INT NULL
CONSTRAINT "Cpids on CYCPOINTS" REFERENCES cycpoints,
cpide INT NULL
CONSTRAINT "Cpide on CYCPOINTS" REFERENCES cycpoints
) WITHOUT OIDS;
GRANT SELECT ON mmintervals TO GROUP babase_readers;
GRANT SELECT ON mmintervals TO GROUP babase_editors;
SELECT 'mmintervals' AS done_with;
GRANT SELECT ON mmintervals_mmiid_seq TO GROUP babase_readers;
GRANT SELECT ON mmintervals_mmiid_seq TO GROUP babase_editors;
SELECT 'mmintervals_mmiid_seq' AS done_with;
-- mdintervals
CREATE TABLE mdintervals (
mdiid SERIAL PRIMARY KEY,
date DATE NOT NULL,
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
dini INT NULL
CONSTRAINT "Dini >= 1"
CHECK(dini > 0 OR dini IS NULL),
dr INT NULL
CONSTRAINT "Dr >= 0"
CHECK(dr >= 0 OR dr IS NULL),
cpids INT NULL
CONSTRAINT "Cpids on CYCPOINTS" REFERENCES cycpoints,
cpide INT NULL
CONSTRAINT "Cpide on CYCPOINTS" REFERENCES cycpoints
) WITHOUT OIDS;
GRANT SELECT ON mdintervals TO GROUP babase_readers;
GRANT SELECT ON mdintervals TO GROUP babase_editors;
SELECT 'mdintervals' AS done_with;
GRANT SELECT ON mdintervals_mdiid_seq TO GROUP babase_readers;
GRANT SELECT ON mdintervals_mdiid_seq TO GROUP babase_editors;
SELECT 'mdintervals_mdiid_seq' AS done_with;
-- members
CREATE TABLE members (
membid SERIAL PRIMARY KEY,
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
date DATE NOT NULL,
grp NUMERIC(6,4) NOT NULL CONSTRAINT "Grp on GROUPS" REFERENCES groups,
origin CHAR(1) NOT NULL
CONSTRAINT "Origin one of C, I, D, M, N, S, E, F, B, G, T, L, or R"
CHECK(origin = 'C'
OR origin = 'I'
OR origin = 'D'
OR origin = 'M'
OR origin = 'N'
OR origin = 'S'
OR origin = 'E'
OR origin = 'F'
OR origin = 'B'
OR origin = 'G'
OR origin = 'T'
OR origin = 'L'
OR origin = 'R'),
interp INT NULL
CONSTRAINT "Interp must be non-negative or NULL"
CHECK(interp >= 0 OR interp IS NULL),
supergroup NUMERIC(6,4) NOT NULL
CONSTRAINT "Supergroup on GROUPS" REFERENCES groups,
residency CHAR(1) NOT NULL
CONSTRAINT "Residency one of R, N or U"
CHECK(residency = 'R'
OR residency = 'N'
OR residency = 'U'),
lowfrequency BOOLEAN NOT NULL,
grpresidency NUMERIC(6,4) NOT NULL
CONSTRAINT "GrpResidency on GROUPS" REFERENCES groups,
daysinstate INT NOT NULL,
dayssincelastseen INT NOT NULL,
daystonextstate INT NOT NULL,
daystonextseen INT NOT NULL
) WITHOUT OIDS;
GRANT SELECT ON members TO GROUP babase_readers;
GRANT SELECT ON members TO GROUP babase_editors;
SELECT 'members' AS done_with;
CREATE TABLE census (
cenid SERIAL PRIMARY KEY,
date DATE NOT NULL
CONSTRAINT "Date may not be more than one year in the future"
CHECK(date <= LOCALTIMESTAMP + CAST('1 year' AS INTERVAL)),
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
grp NUMERIC(6,4) NOT NULL CONSTRAINT "Grp on GROUPS" REFERENCES groups,
status CHAR(1) NOT NULL
CONSTRAINT "Status one of C, D, A M, N, S, E, F, B, G, T, L, or R"
CHECK(status = 'C'
OR status = 'D'
OR status = 'A'
OR status = 'M'
OR status = 'N'
OR status = 'S'
OR status = 'E'
OR status = 'F'
OR status = 'B'
OR status = 'G'
OR status = 'T'
OR status = 'L'
OR status = 'R'),
cen BOOLEAN NOT NULL
CONSTRAINT "Cen must be TRUE when Status C or A, FALSE otherwise unless D"
CHECK (cen = TRUE AND (status = 'C'
OR status = 'D'
OR status = 'A')
OR (cen = FALSE AND NOT (status = 'C'
OR status = 'A')))
) WITHOUT OIDS;
GRANT SELECT ON census TO GROUP babase_readers;
GRANT SELECT ON census TO GROUP babase_editors;
GRANT INSERT ON census TO GROUP babase_editors;
GRANT UPDATE ON census TO GROUP babase_editors;
GRANT DELETE ON census TO GROUP babase_editors;
SELECT 'census' AS done_with;
GRANT SELECT ON census_cenid_seq TO GROUP babase_readers;
GRANT SELECT ON census_cenid_seq TO GROUP babase_editors;
GRANT UPDATE ON census_cenid_seq TO GROUP babase_editors;
SELECT 'census_cenid_seq' AS done_with;
CREATE TABLE demog (
cenid INT NOT NULL
CONSTRAINT "Cenid on CENSUS" PRIMARY KEY REFERENCES census,
reference NUMERIC(6,4) NOT NULL
CONSTRAINT "Reference on DEMOG_REFERENCES" REFERENCES demog_references,
comment TEXT NULL
CONSTRAINT "comment: Cannot be empty or only whitespace characters"
CHECK(btrim(comment, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON demog TO GROUP babase_readers;
GRANT SELECT ON demog TO GROUP babase_editors;
GRANT INSERT ON demog TO GROUP babase_editors;
GRANT UPDATE ON demog TO GROUP babase_editors;
GRANT DELETE ON demog TO GROUP babase_editors;
SELECT 'demog' AS done_with;
CREATE TABLE ranks (
rnkid SERIAL PRIMARY KEY,
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
rnkdate DATE NOT NULL
CONSTRAINT "Rnkdate between 1940 and 2040 (inclusive)"
CHECK (CAST('1940-01-01' AS DATE) <= rnkdate
AND rnkdate <= CAST('2040-12-31' AS DATE)),
CONSTRAINT "Rnkdate must be the first of the month"
CHECK (rnkdate = date_trunc('month', rnkdate)),
grp NUMERIC(6,4) NOT NULL
CONSTRAINT "Grp on GROUPS" REFERENCES groups,
rnktype VARCHAR(6) NOT NULL
CONSTRAINT "Rnktype on RNKTYPES" REFERENCES rnktypes,
rank INT NOT NULL
CONSTRAINT "Rank must be >= 1" CHECK (rank >= 1)
) WITHOUT OIDS;
GRANT SELECT ON ranks TO GROUP babase_readers;
GRANT SELECT ON ranks TO GROUP babase_editors;
GRANT INSERT ON ranks TO GROUP babase_editors;
GRANT UPDATE ON ranks TO GROUP babase_editors;
GRANT DELETE ON ranks TO GROUP babase_editors;
SELECT 'ranks' AS done_with;
GRANT SELECT ON ranks_rnkid_seq TO GROUP babase_readers;
GRANT SELECT ON ranks_rnkid_seq TO GROUP babase_editors;
GRANT UPDATE ON ranks_rnkid_seq TO GROUP babase_editors;
SELECT 'ranks_rnkid_seq' AS done_with;
CREATE TABLE samples (
sid SERIAL PRIMARY KEY,
date DATE NOT NULL,
-- If stime is ever required then make date and stime a single column.
stime TIME(0) NOT NULL
CONSTRAINT "Stime required after Jan 1, 07"
CHECK (NOT(stime IS NULL AND date >= '2007-01-01')),
observer VARCHAR(4) NOT NULL
CONSTRAINT "Observer on OBSERVERS" REFERENCES observers,
stype CHAR(1) NOT NULL
CONSTRAINT "Stype one of J, F"
CHECK(stype = 'J'
OR stype = 'F'),
grp NUMERIC(6,4) NOT NULL
CONSTRAINT "Grp on GROUPS" REFERENCES groups,
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
-- MINutes in Sample
mins INT NOT NULL
CONSTRAINT "Mins must be >= 0 and <= 10"
CHECK (mins >= 0 AND mins <= 10),
-- MINutes In Sight
minsis INT DEFAULT 0
NOT NULL
CONSTRAINT "Minsis must be >= 0 and <= Mins"
CHECK (minsis >= 0 AND minsis <= mins),
programid SMALLINT NULL
CONSTRAINT "Programid on PROGRAMIDS" REFERENCES programids,
setupid SMALLINT NULL
CONSTRAINT "Setupid on SETUPIDS" REFERENCES setupids,
palmtop SMALLINT NOT NULL
CONSTRAINT "Palmtop on PALMTOPS" REFERENCES palmtops
) WITHOUT OIDS;
GRANT SELECT ON samples TO GROUP babase_readers;
GRANT SELECT ON samples TO GROUP babase_editors;
GRANT INSERT ON samples TO GROUP babase_editors;
GRANT UPDATE ON samples TO GROUP babase_editors;
GRANT DELETE ON samples TO GROUP babase_editors;
SELECT 'samples' AS done_with;
GRANT SELECT ON samples_sid_seq TO GROUP babase_readers;
GRANT SELECT ON samples_sid_seq TO GROUP babase_editors;
GRANT UPDATE ON samples_sid_seq TO GROUP babase_editors;
SELECT 'samples_sid_seq' AS done_with;
CREATE TABLE interact_data(
iid SERIAL PRIMARY KEY,
sid INT
CONSTRAINT "Sid on SAMPLES" REFERENCES samples,
act CHAR(2) NOT NULL
CONSTRAINT "Act on ACTS" REFERENCES acts,
CONSTRAINT "Approach only when sid exists, during all-occurences sampling"
CHECK ((act = 'P' AND sid IS NOT NULL)
OR act <> 'P'),
CONSTRAINT "Request to groom only when sid exists, during all-occurences sampling"
CHECK ((act = 'R' AND sid IS NOT NULL)
OR act <> 'R'),
CONSTRAINT "Mount not allowed when sid exists, during all-occurences sampling"
CHECK ((act = 'M' AND sid IS NULL)
OR act <> 'M'),
CONSTRAINT "Consortship not allowed when sid exists, during all-occurences sampling"
CHECK ((act = 'C' AND sid IS NULL)
OR act <> 'C'),
CONSTRAINT "Ejaculation not allowed when sid exists, during all-occurences sampling"
CHECK ((act = 'E' AND sid IS NULL)
OR act <> 'E'),
date DATE NOT NULL,
start TIME(0)
CONSTRAINT "Start >= 05:00"
CHECK (start >= '05:00'::time)
CONSTRAINT "Start <= 20:00"
CHECK (start <= '20:00'::time)
CONSTRAINT "The seconds in the Start value must be 0 when Sid is NULL"
CHECK (sid IS NOT NULL OR EXTRACT('seconds' FROM start) = 0),
stop TIME(0)
CONSTRAINT "Stop >= 05:00"
CHECK (stop >= '05:00'::time)
CONSTRAINT "Stop <= 20:00"
CHECK (stop <= '20:00'::time)
CONSTRAINT "The seconds in the Stop value must be 0 when Sid is NULL"
CHECK (sid IS NOT NULL OR EXTRACT('seconds' FROM stop) = 0),
observer VARCHAR(4) NULL
CONSTRAINT "Observer on OBSERVERS" REFERENCES observers,
handwritten BOOLEAN NOT NULL
CONSTRAINT "Interactions during a focal cannot be handwritten"
CHECK ((sid IS NULL)
OR (NOT handwritten))
) WITHOUT OIDS;
GRANT SELECT ON interact_data TO GROUP babase_readers;
GRANT SELECT ON interact_data TO GROUP babase_editors;
GRANT INSERT ON interact_data TO GROUP babase_editors;
GRANT UPDATE ON interact_data TO GROUP babase_editors;
GRANT DELETE ON interact_data TO GROUP babase_editors;
SELECT 'interact_data' AS done_with;
GRANT SELECT ON interact_data_iid_seq TO GROUP babase_readers;
GRANT SELECT ON interact_data_iid_seq TO GROUP babase_editors;
GRANT UPDATE ON interact_data_iid_seq TO GROUP babase_editors;
SELECT 'interact_data_iid_seq' AS done_with;
CREATE TABLE parts (
partid SERIAL PRIMARY KEY,
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
role CHAR(1) NOT NULL
CONSTRAINT "Role on PARTS is one of R or E"
CHECK (role = 'R' OR role = 'E'),
iid INT NOT NULL
CONSTRAINT "Iid on INTERACT_DATA" REFERENCES interact_data
) WITHOUT OIDS;
GRANT SELECT ON parts TO GROUP babase_readers;
GRANT SELECT ON parts TO GROUP babase_editors;
GRANT INSERT ON parts TO GROUP babase_editors;
GRANT UPDATE ON parts TO GROUP babase_editors;
GRANT DELETE ON parts TO GROUP babase_editors;
SELECT 'parts' AS done_with;
GRANT SELECT ON parts_partid_seq TO GROUP babase_readers;
GRANT SELECT ON parts_partid_seq TO GROUP babase_editors;
GRANT UPDATE ON parts_partid_seq TO GROUP babase_editors;
SELECT 'parts_partid_seq' AS done_with;
CREATE TABLE point_data (
pntid SERIAL PRIMARY KEY,
sid INT NOT NULL
CONSTRAINT "Sid on SAMPLES" REFERENCES samples,
min SMALLINT NOT NULL -- Triggers don't allow NULL
CONSTRAINT "Min > 0"
CHECK (min IS NULL OR min > 0),
activity CHAR(1) NOT NULL,
posture CHAR(1) NOT NULL
CONSTRAINT "Posture on POSTURES" REFERENCES postures,
foodcode VARCHAR(3)
CONSTRAINT "Foodcode on FOODCODES" REFERENCES foodcodes,
CONSTRAINT "Foodcode present if and only if Activity = F"
CHECK (foodcode IS NULL
OR (foodcode IS NOT NULL
AND activity = 'F')),
ptime TIME(0) NOT NULL
CONSTRAINT "Ptime >= 05:00"
CHECK (ptime >= '05:00'::time)
CONSTRAINT "Ptime <= 19:00"
CHECK (ptime <= '19:00'::time)
) WITHOUT OIDS;
GRANT SELECT ON point_data TO GROUP babase_readers;
GRANT SELECT ON point_data TO GROUP babase_editors;
GRANT INSERT ON point_data TO GROUP babase_editors;
GRANT UPDATE ON point_data TO GROUP babase_editors;
GRANT DELETE ON point_data TO GROUP babase_editors;
SELECT 'point_data' AS done_with;
GRANT SELECT ON point_data_pntid_seq TO GROUP babase_readers;
GRANT SELECT ON point_data_pntid_seq TO GROUP babase_editors;
GRANT UPDATE ON point_data_pntid_seq TO GROUP babase_editors;
SELECT 'point_data_pntid_seq' AS done_with;
-- FPOINTS could inherit from POINT_DATA, but the pg syntax
-- is not standard and we can always make a view so I'm
-- going to hold off on this.
CREATE TABLE fpoints (
pntid INT PRIMARY KEY CONSTRAINT "Pntid on POINT_DATA" REFERENCES point_data,
kidcontact CHAR(1) NOT NULL
CONSTRAINT "Kidcontact one of A, V, D, O, N"
CHECK(kidcontact = 'A'
OR kidcontact = 'V'
OR kidcontact = 'D'
OR kidcontact = 'O'
OR kidcontact = 'N'),
kidsuckle CHAR(1) NOT NULL
CONSTRAINT "Kidsuckle one of S, N, U"
CHECK(kidsuckle = 'S'
OR kidsuckle = 'N'
OR kidsuckle = 'U')
) WITHOUT OIDS;
GRANT SELECT ON fpoints TO GROUP babase_readers;
GRANT SELECT ON fpoints TO GROUP babase_editors;
GRANT INSERT ON fpoints TO GROUP babase_editors;
GRANT UPDATE ON fpoints TO GROUP babase_editors;
GRANT DELETE ON fpoints TO GROUP babase_editors;
SELECT 'fpoints' AS done_with;
CREATE TABLE neighbors (
nghid SERIAL PRIMARY KEY,
pntid INT NOT NULL
CONSTRAINT "Pntid on POINT_DATA" REFERENCES point_data,
sname CHAR(3)
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
ncode CHAR(1) NOT NULL
CONSTRAINT "Ncode on NCODES" REFERENCES ncodes,
unksname CHAR(3)
CONSTRAINT "Unksname on UNKSNAMES" REFERENCES unksnames,
CONSTRAINT "Either an Sname or a Unksname but not both is required"
CHECK ((sname IS NULL AND unksname IS NOT NULL)
OR (sname IS NOT NULL AND unksname IS NULL))
) WITHOUT OIDS;
GRANT SELECT ON neighbors TO GROUP babase_readers;
GRANT SELECT ON neighbors TO GROUP babase_editors;
GRANT INSERT ON neighbors TO GROUP babase_editors;
GRANT UPDATE ON neighbors TO GROUP babase_editors;
GRANT DELETE ON neighbors TO GROUP babase_editors;
SELECT 'neighbors' AS done_with;
GRANT SELECT ON neighbors_nghid_seq TO GROUP babase_readers;
GRANT SELECT ON neighbors_nghid_seq TO GROUP babase_editors;
GRANT UPDATE ON neighbors_nghid_seq TO GROUP babase_editors;
SELECT 'neighbors_nghid_seq' AS done_with;
CREATE TABLE allmiscs (
almid SERIAL PRIMARY KEY,
sid INT NOT NULL CONSTRAINT "Sid on SAMPLES" REFERENCES samples,
atime TIME NOT NULL
CONSTRAINT "Atime >= 05:00"
CHECK (atime >= '05:00'::time)
CONSTRAINT "Atime <= 19:00;"
CHECK (atime <= '19:00'::time),
txt TEXT NOT NULL
CONSTRAINT "txt: Cannot be empty or only whitespace characters"
CHECK(btrim(txt, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON allmiscs TO GROUP babase_readers;
GRANT SELECT ON allmiscs TO GROUP babase_editors;
GRANT INSERT ON allmiscs TO GROUP babase_editors;
GRANT UPDATE ON allmiscs TO GROUP babase_editors;
GRANT DELETE ON allmiscs TO GROUP babase_editors;
SELECT 'allmiscs' AS done_with;
GRANT SELECT ON allmiscs_almid_seq TO GROUP babase_readers;
GRANT SELECT ON allmiscs_almid_seq TO GROUP babase_editors;
GRANT UPDATE ON allmiscs_almid_seq TO GROUP babase_editors;
SELECT 'allmiscs_almid_seq' AS done_with;
CREATE TABLE weatherhawk (
wid SERIAL PRIMARY KEY,
wstation VARCHAR(3) NOT NULL
CONSTRAINT "WStation on WSTATIONS" REFERENCES wstations,
timestamp TIMESTAMP NOT NULL
CONSTRAINT "TimeStamp must be on the hour"
CHECK (timestamp
= date_trunc('hour'
, timestamp)),
wsoftware VARCHAR(3) NOT NULL
CONSTRAINT "WSoftware on WEATHERHAWK_SOFTWARES" REFERENCES weatherhawk_softwares,
recordnum INT,
batvolt NUMERIC(4,2) NOT NULL
CONSTRAINT "BatVolt must be between 10.00 and 14.00, inclusive"
CHECK(BatVolt BETWEEN 10.00 AND 14.00),
batvolt_min NUMERIC(4,2)
CONSTRAINT "BatVolt_Min must be between 10.00 and 14.00, inclusive"
CHECK(BatVolt_Min BETWEEN 10.00 AND 14.00),
airtemp_avg NUMERIC(3,1) NOT NULL
CONSTRAINT "AirTemp_Avg must be between -10.0 and 50.0, inclusive"
CHECK(AirTemp_Avg BETWEEN -10.0 AND 50.0),
relativehumidity_avg NUMERIC(4,1) NOT NULL
CONSTRAINT "RelativeHumidity_Avg must be between 0.0 and 100.0, inclusive"
CHECK(RelativeHumidity_Avg BETWEEN 0.0 AND 100.0),
windspeed_avg_km_hr NUMERIC(2,0)
CONSTRAINT "WindSpeed_Avg_Km_Hr must be between 0 and 30, inclusive"
CHECK(WindSpeed_Avg_Km_Hr BETWEEN 0 AND 30),
windspeed_avg_m_s NUMERIC(3,1)
CONSTRAINT "WindSpeed_Avg_M_S must be between 0.0 and 15.0, inclusive"
CHECK(WindSpeed_Avg_M_S BETWEEN 0.0 AND 15.0),
solar NUMERIC(5,1)
CONSTRAINT "Solar must be between 0.0 and 2000.0, inclusive"
CHECK(Solar BETWEEN 0.0 AND 2000.0),
airtemp_min NUMERIC(3,1)
CONSTRAINT "AirTemp_Min must be between -10.0 and 50.0, inclusive"
CHECK(AirTemp_Min BETWEEN -10.0 AND 50.0),
airtemp_min_time TIMESTAMP
CONSTRAINT "AirTemp_Min_Time: Cannot contain seconds or fractions thereof"
CHECK (EXTRACT(SECONDS FROM AirTemp_Min_Time) = 0)
CONSTRAINT "AirTemp_Min_Time must be before or equal to TimeStamp"
CHECK (airtemp_min_time <= timestamp),
airtemp_max NUMERIC(3,1)
CONSTRAINT "AirTemp_Max must be between -10.0 and 50.0, inclusive"
CHECK(AirTemp_Max BETWEEN -10.0 AND 50.0),
airtemp_max_time TIMESTAMP
CONSTRAINT "AirTemp_Max_Time: Cannot contain seconds or fractions thereof"
CHECK (EXTRACT(SECONDS FROM AirTemp_Max_Time) = 0)
CONSTRAINT "AirTemp_Max_Time must be before or equal to TimeStamp"
CHECK (airtemp_max_time <= timestamp),
wind_dir numeric(4,1) NOT NULL
CONSTRAINT "Wind_Dir must be between 0.0 and 360.0, inclusive"
CHECK(Wind_Dir BETWEEN 0.0 AND 360.0),
windspeed_max_km_hr NUMERIC(2,0)
CONSTRAINT "WindSpeed_Max_Km_Hr must be between 0 and 30, inclusive"
CHECK(WindSpeed_Max_Km_Hr BETWEEN 0 AND 30),
windspeed_max_m_s NUMERIC(3,1)
CONSTRAINT "WindSpeed_Max_M_S must be between 0.0 and 15.0, inclusive"
CHECK(WindSpeed_Max_M_S BETWEEN 0.0 AND 15.0),
windspeed_max_time TIMESTAMP
CONSTRAINT "WindSpeed_Max_Time: Cannot contain seconds or fractions thereof"
CHECK (EXTRACT(SECONDS FROM WindSpeed_Max_Time) = 0)
CONSTRAINT "WindSpeed_Max_Time must be before or equal to TimeStamp"
CHECK (windspeed_max_time <= timestamp),
barometer NUMERIC(3,1) NOT NULL
CONSTRAINT "Barometer must be between 85.0 and 95.0, inclusive"
CHECK(Barometer BETWEEN 85.0 AND 95.0),
yearlyrain INT NOT NULL
CONSTRAINT "YearlyRain must be greater than or equal to 0"
CHECK (yearlyrain
>= 0),
timestamprain INT NOT NULL,
CONSTRAINT "TimeStampRain cannot be negative"
CHECK (timestamprain
>= 0),
CONSTRAINT "windspeed_max_km_hr must be NULL if windspeed_avg_km_hr is NULL"
CHECK((windspeed_avg_km_hr IS NULL AND windspeed_max_km_hr IS NULL)
OR windspeed_avg_km_hr IS NOT NULL),
CONSTRAINT "windspeed_max_m_s must be NULL if windspeed_avg_m_s is NULL"
CHECK((windspeed_avg_m_s IS NULL AND windspeed_max_m_s IS NULL)
OR windspeed_avg_m_s IS NOT NULL),
CONSTRAINT "Average Wind Speed must be recorded"
CHECK (COALESCE(windspeed_avg_km_hr, windspeed_avg_m_s)
IS NOT NULL),
CONSTRAINT "Average Wind Speed must be in only one of WindSpeed_Avg_Km_Hr and WindSpeed_Avg_M_S"
CHECK (windspeed_avg_km_hr IS NULL AND windspeed_avg_m_s IS NOT NULL
OR
windspeed_avg_km_hr IS NOT NULL AND windspeed_avg_m_s IS NULL),
CONSTRAINT "AirTemp_Min must be NULL if and only if AirTemp_Min_Time is NULL"
CHECK((AirTemp_Min IS NULL AND AirTemp_Min_Time IS NULL)
OR (AirTemp_Min IS NOT NULL AND AirTemp_Min_Time IS NOT NULL)),
CONSTRAINT "AirTemp_Max must be NULL if and only if AirTemp_Max_Time is NULL"
CHECK((AirTemp_Max IS NULL AND AirTemp_Max_Time IS NULL)
OR (AirTemp_Max IS NOT NULL AND AirTemp_Max_Time IS NOT NULL)),
CONSTRAINT "WindSpeed_Max_Time and (either WindSpeed_Max_Km_Hr or _M_S) must both be NULL or both non-NULL"
CHECK ((COALESCE(windspeed_max_km_hr, windspeed_max_m_s) IS NULL
AND windspeed_max_time IS NULL)
OR
(COALESCE(windspeed_max_km_hr, windspeed_max_m_s) IS NOT NULL
AND windspeed_max_time IS NOT NULL))
) WITHOUT OIDS;
GRANT SELECT ON weatherhawk TO GROUP babase_readers;
GRANT SELECT ON weatherhawk TO GROUP babase_editors;
GRANT INSERT ON weatherhawk TO GROUP babase_editors;
GRANT UPDATE ON weatherhawk TO GROUP babase_editors;
GRANT DELETE ON weatherhawk TO GROUP babase_editors;
SELECT 'weatherhawk' AS done_with;
GRANT SELECT ON weatherhawk_wid_seq TO GROUP babase_readers;
GRANT SELECT ON weatherhawk_wid_seq TO GROUP babase_editors;
GRANT UPDATE ON weatherhawk_wid_seq TO GROUP babase_editors;
SELECT 'weatherhawk_wid_seq' AS done_with;
CREATE TABLE wreadings (
wrid SERIAL PRIMARY KEY,
wstation VARCHAR(3) NOT NULL
CONSTRAINT "Wstation on WSTATIONS" REFERENCES wstations,
wrdaytime TIMESTAMP NOT NULL,
estdaytime BOOLEAN NOT NULL,
wrperson VARCHAR(4) NOT NULL
CONSTRAINT "WRperson on OBSERVERS" REFERENCES observers,
wrnotes TEXT NULL
CONSTRAINT "wrnotes: Cannot be empty or only whitespace characters"
CHECK(btrim(wrnotes, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON wreadings TO GROUP babase_readers;
GRANT SELECT ON wreadings TO GROUP babase_editors;
GRANT INSERT ON wreadings TO GROUP babase_editors;
GRANT UPDATE ON wreadings TO GROUP babase_editors;
GRANT DELETE ON wreadings TO GROUP babase_editors;
SELECT 'wreadings' AS done_with;
GRANT SELECT ON wreadings_wrid_seq TO GROUP babase_readers;
GRANT SELECT ON wreadings_wrid_seq TO GROUP babase_editors;
GRANT UPDATE ON wreadings_wrid_seq TO GROUP babase_editors;
SELECT 'wreadings_wrid_seq' AS done_with;
CREATE TABLE tempmins (
wrid INT PRIMARY KEY
CONSTRAINT "WRid on WREADINGS" REFERENCES wreadings,
tempmin NUMERIC(3,1) NOT NULL,
CONSTRAINT "Tempmin must be between -5 and 35 (inclusive)"
CHECK(tempmin >= -5 AND tempmin
<= 35),
CONSTRAINT "Tempmin must be a multiple of 0.5"
CHECK((tempmin * 2) % 1 = 0)
) WITHOUT OIDS;
GRANT SELECT ON tempmins TO GROUP babase_readers;
GRANT SELECT ON tempmins TO GROUP babase_editors;
GRANT INSERT ON tempmins TO GROUP babase_editors;
GRANT UPDATE ON tempmins TO GROUP babase_editors;
GRANT DELETE ON tempmins TO GROUP babase_editors;
SELECT 'tempmins' AS done_with;
CREATE TABLE tempmaxs (
wrid INT PRIMARY KEY
CONSTRAINT "WRid on WREADINGS" REFERENCES wreadings,
tempmax NUMERIC(3,1) NOT NULL
CONSTRAINT "Tempmax must be between 10 and 50 (inclusive)"
CHECK(tempmax >= 10
AND tempmax <= 50)
CONSTRAINT "Tempmax must be a multiple of 0.5"
CHECK((tempmax * 2) % 1 = 0)
) WITHOUT OIDS;
GRANT SELECT ON tempmaxs TO GROUP babase_readers;
GRANT SELECT ON tempmaxs TO GROUP babase_editors;
GRANT INSERT ON tempmaxs TO GROUP babase_editors;
GRANT UPDATE ON tempmaxs TO GROUP babase_editors;
GRANT DELETE ON tempmaxs TO GROUP babase_editors;
SELECT 'tempmaxs' AS done_with;
CREATE TABLE raingauges (
wrid INT PRIMARY KEY
CONSTRAINT "WRid on WREADINGS" REFERENCES wreadings,
rgspan INTERVAL(0) NOT NULL,
estrgspan BOOLEAN NULL,
rain NUMERIC(4,1) NOT NULL
CONSTRAINT "Rain must be non-negative"
CHECK(rain >= 0)
CONSTRAINT "Rain cannot be more than 200.0mm"
CHECK(rain <= 200.0)
) WITHOUT OIDS;
GRANT SELECT ON raingauges TO GROUP babase_readers;
GRANT SELECT ON raingauges TO GROUP babase_editors;
GRANT INSERT ON raingauges TO GROUP babase_editors;
GRANT UPDATE ON raingauges TO GROUP babase_editors;
GRANT DELETE ON raingauges TO GROUP babase_editors;
SELECT 'raingauges' AS done_with;
CREATE TABLE rgsetups (
rgsid SERIAL PRIMARY KEY,
wstation VARCHAR(3) NOT NULL
CONSTRAINT "Wstation on WSTATIONS" REFERENCES wstations,
rgsdaytime TIMESTAMP NOT NULL,
rgsestdaytime BOOLEAN NOT NULL,
rgsperson VARCHAR(4) NOT NULL
CONSTRAINT "RGSperson on OBSERVERS" REFERENCES observers
) WITHOUT OIDS;
GRANT SELECT ON rgsetups TO GROUP babase_readers;
GRANT SELECT ON rgsetups TO GROUP babase_editors;
GRANT INSERT ON rgsetups TO GROUP babase_editors;
GRANT UPDATE ON rgsetups TO GROUP babase_editors;
GRANT DELETE ON rgsetups TO GROUP babase_editors;
SELECT 'rgsetups' AS done_with;
GRANT SELECT ON rgsetups_rgsid_seq TO GROUP babase_readers;
GRANT SELECT ON rgsetups_rgsid_seq TO GROUP babase_editors;
GRANT UPDATE ON rgsetups_rgsid_seq TO GROUP babase_editors;
SELECT 'rgsetups_rgsid_seq' AS done_with;
-- mpis
CREATE TABLE mpis (
mpiid SERIAL PRIMARY KEY,
date DATE NOT NULL,
context_type CHAR(1) NOT NULL
CONSTRAINT "Context_type on CONTEXT_TYPES"
REFERENCES context_types(context_type)
CONSTRAINT "Context_type of N requires Context be NULL"
CHECK(context_type <> 'N'
OR (context_type = 'N' AND context IS NULL))
CONSTRAINT "Context_type of C requires Context be NULL"
CHECK(context_type <> 'C'
OR (context_type = 'C' AND context IS NULL)),
context TEXT NULL
CONSTRAINT "context: Cannot be empty or only whitespace characters"
CHECK(btrim(context, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON mpis TO GROUP babase_readers;
GRANT SELECT ON mpis TO GROUP babase_editors;
GRANT INSERT ON mpis TO GROUP babase_editors;
GRANT UPDATE ON mpis TO GROUP babase_editors;
GRANT DELETE ON mpis TO GROUP babase_editors;
SELECT 'mpis' AS done_with;
GRANT SELECT ON mpis_mpiid_seq TO GROUP babase_readers;
GRANT SELECT ON mpis_mpiid_seq TO GROUP babase_editors;
GRANT UPDATE ON mpis_mpiid_seq TO GROUP babase_editors;
SELECT 'mpis_mpiid_seq' AS done_with;
-- consorts
CREATE TABLE consorts (
mpiid INT PRIMARY KEY
CONSTRAINT "Mpiid on MPIS" REFERENCES mpis,
female CHAR(3) NULL
CONSTRAINT "Female on BIOGRAPH" REFERENCES biograph(sname),
had CHAR(3) NOT NULL
CONSTRAINT "Had on BIOGRAPH" REFERENCES biograph(sname),
got CHAR(3) NOT NULL
CONSTRAINT "Got on BIOGRAPH" REFERENCES biograph(sname)
) WITHOUT OIDS;
GRANT SELECT ON consorts TO GROUP babase_readers;
GRANT SELECT ON consorts TO GROUP babase_editors;
GRANT INSERT ON consorts TO GROUP babase_editors;
GRANT UPDATE ON consorts TO GROUP babase_editors;
GRANT DELETE ON consorts TO GROUP babase_editors;
SELECT 'consorts' AS done_with;
-- mpiacts
CREATE TABLE mpiacts (
mpiact VARCHAR(5) PRIMARY KEY,
descr TEXT NOT NULL UNIQUE,
kind CHAR(1) NOT NULL
CONSTRAINT "Kind must be one of: A, R, H, or O"
CHECK(kind = 'A'
OR kind = 'R'
OR kind = 'H'
OR kind = 'O'),
decided BOOLEAN NOT NULL
CONSTRAINT "Decided can only be TRUE when Kind is A"
CHECK(NOT(decided AND kind <> 'A')),
multi_first BOOLEAN NOT NULL
CONSTRAINT "Multi_first can only be TRUE when Kind is A"
CHECK(NOT(multi_first AND kind <> 'A'))
) WITHOUT OIDS;
GRANT SELECT ON mpiacts TO GROUP babase_readers;
GRANT SELECT ON mpiacts TO GROUP babase_editors;
GRANT INSERT ON mpiacts TO GROUP babase_editors;
GRANT UPDATE ON mpiacts TO GROUP babase_editors;
GRANT DELETE ON mpiacts TO GROUP babase_editors;
SELECT 'mpiacts' AS done_with;
-- mpi_data
CREATE TABLE mpi_data (
mpidid SERIAL PRIMARY KEY,
mpiid INT NOT NULL
CONSTRAINT "Mpiid on MPIS" REFERENCES mpis,
mpiact VARCHAR(5) NOT NULL
CONSTRAINT "Mpiact on MPIACTS" REFERENCES mpiacts,
seq INT NOT NULL,
helped BOOLEAN NULL,
active BOOLEAN NULL
) WITHOUT OIDS;
GRANT SELECT ON mpi_data TO GROUP babase_readers;
GRANT SELECT ON mpi_data TO GROUP babase_editors;
GRANT INSERT ON mpi_data TO GROUP babase_editors;
GRANT UPDATE ON mpi_data TO GROUP babase_editors;
GRANT DELETE ON mpi_data TO GROUP babase_editors;
SELECT 'mpi_data' AS done_with;
GRANT SELECT ON mpi_data_mpidid_seq TO GROUP babase_readers;
GRANT SELECT ON mpi_data_mpidid_seq TO GROUP babase_editors;
GRANT UPDATE ON mpi_data_mpidid_seq TO GROUP babase_editors;
SELECT 'mpi_data_mpidid_seq' AS done_with;
-- mpi_parts
CREATE TABLE mpi_parts (
mpipid SERIAL PRIMARY KEY,
mpidid INT NOT NULL
CONSTRAINT "MPIDid on MPI_DATA" REFERENCES mpi_data,
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
unksname CHAR(1) NULL
CONSTRAINT "Unksname on PARTUNKS" REFERENCES partunks,
role CHAR(1) NOT NULL
CONSTRAINT "Role on MPI_PARTS is one of R or E"
CHECK (role = 'R' OR role = 'E'),
CONSTRAINT "Either Sname or Unksname but not both must be NULL"
CHECK (((sname IS NULL) OR (unksname IS NULL))
AND ((sname IS NOT NULL) OR (unksname IS NOT NULL)))
) WITHOUT OIDS;
GRANT SELECT ON mpi_parts TO GROUP babase_readers;
GRANT SELECT ON mpi_parts TO GROUP babase_editors;
GRANT INSERT ON mpi_parts TO GROUP babase_editors;
GRANT UPDATE ON mpi_parts TO GROUP babase_editors;
GRANT DELETE ON mpi_parts TO GROUP babase_editors;
SELECT 'mpi_parts' AS done_with;
GRANT SELECT ON mpi_parts_mpipid_seq TO GROUP babase_readers;
GRANT SELECT ON mpi_parts_mpipid_seq TO GROUP babase_editors;
GRANT UPDATE ON mpi_parts_mpipid_seq TO GROUP babase_editors;
SELECT 'mpi_parts_mpipid_seq' AS done_with;
-- dad_data
CREATE TABLE dad_data (
dadid SERIAL PRIMARY KEY,
kid CHAR(3) NOT NULL
CONSTRAINT "Kid on BIOGRAPH" REFERENCES biograph(sname),
mom_sampled BOOLEAN NULL,
dad_sampled BOOLEAN NULL
CONSTRAINT "Dad_sampled must be NULL if and only if Dad_consensus is NULL"
CHECK((Dad_sampled IS NULL AND Dad_consensus IS NULL)
OR (Dad_sampled IS NOT NULL AND Dad_consensus IS NOT NULL)),
dad_excl CHAR(3) NULL
CONSTRAINT "Dad_excl on BIOGRAPH" REFERENCES biograph(sname),
loci_excl SMALLINT NULL
CONSTRAINT "Loci_excl must be NULL if and only if Dad_excl is NULL"
CHECK((Loci_excl IS NULL AND Dad_excl IS NULL)
OR (Loci_excl IS NOT NULL AND Dad_excl IS NOT NULL))
CONSTRAINT "Loci_excl must be NULL or between 0 and 40, inclusive"
CHECK (loci_excl IS NULL
OR (0 <= loci_excl
AND loci_excl <= 40)),
pdads_considered SMALLINT NOT NULL
CONSTRAINT "Pdads_considered must be between 0 and 50, inclusive"
CHECK (0 <= pdads_considered
AND pdads_considered <= 50),
pdads_typed SMALLINT NOT NULL
CONSTRAINT "Pdads_typed must be <= Pdads_considered"
CHECK (pdads_typed <= pdads_considered),
CONSTRAINT "Pdads_typed must be >= 0"
CHECK (pdads_typed >= 0),
dad_1perr CHAR(3) NULL
CONSTRAINT "Dad_1perr on BIOGRAPH" REFERENCES biograph(sname),
conf_1perr NUMERIC(3,2) NULL
CONSTRAINT "Conf_1perr must be NULL if and only if Dad_1perr is NULL"
CHECK((Conf_1perr IS NULL AND Dad_1perr IS NULL)
OR (Conf_1perr IS NOT NULL AND Dad_1perr IS NOT NULL))
CONSTRAINT "Conf_1perr must be between 0 and 1, inclusive"
CHECK (conf_1perr >= 0
AND conf_1perr <= 1),
dad_5perr CHAR(3) NULL
CONSTRAINT "Dad_5perr on BIOGRAPH" REFERENCES biograph(sname),
conf_5perr NUMERIC(3,2) NULL
CONSTRAINT "Conf_5perr must be NULL if and only if Dad_5perr is NULL"
CHECK((Conf_5perr IS NULL AND Dad_5perr IS NULL)
OR (Conf_5perr IS NOT NULL AND Dad_5perr IS NOT NULL))
CONSTRAINT "Conf_5perr must be between 0 and 1, inclusive"
CHECK (conf_5perr >= 0
AND conf_5perr <= 1),
dad_allmales CHAR(3) NULL
CONSTRAINT "Dad_allmales on BIOGRAPH" REFERENCES biograph(sname),
conf_allmales NUMERIC(3,2) NULL
CONSTRAINT "Conf_allmales must be NULL if and only if Dad_allmales is NULL"
CHECK((Conf_allmales IS NULL AND Dad_allmales IS NULL)
OR (Conf_allmales IS NOT NULL AND Dad_allmales IS NOT NULL))
CONSTRAINT "Conf_allmales must be between 0 and 1, inclusive"
CHECK (conf_allmales >= 0
AND conf_allmales <= 1),
dad_consensus CHAR(3) NULL
CONSTRAINT "Dad_consensus on BIOGRAPH" REFERENCES biograph(sname),
date DATE NOT NULL,
software VARCHAR(10) NOT NULL
CONSTRAINT "Software code must be on DAD_SOFTWARE"
REFERENCES dad_software,
comments TEXT NULL
CONSTRAINT "comments: Cannot be empty or only whitespace characters"
CHECK(btrim(comments, E' \r\n\t\f\x0B') <> ''),
consensus_mismatch VARCHAR(12)
CONSTRAINT "Consensus_Mismatch on DAD_DATA_MISMATCHES"
REFERENCES dad_data_mismatches
CONSTRAINT "Dad_consensus must be NULL if and only if Consensus_Mismatch is NULL"
CHECK((Dad_consensus IS NULL AND Consensus_Mismatch IS NULL)
OR (Dad_consensus IS NOT NULL AND Consensus_Mismatch IS NOT NULL)),
completeness SMALLINT NOT NULL
CONSTRAINT "Completeness on DAD_DATA_COMPLETENESS"
REFERENCES dad_data_completeness
);
GRANT SELECT ON dad_data TO GROUP babase_readers;
GRANT SELECT ON dad_data TO GROUP babase_editors;
GRANT INSERT ON dad_data TO GROUP babase_editors;
GRANT UPDATE ON dad_data TO GROUP babase_editors;
GRANT DELETE ON dad_data TO GROUP babase_editors;
SELECT 'dad_data' AS done_with;
GRANT SELECT ON dad_data_dadid_seq TO GROUP babase_readers;
GRANT SELECT ON dad_data_dadid_seq TO GROUP babase_editors;
GRANT UPDATE ON dad_data_dadid_seq TO GROUP babase_editors;
SELECT 'dad_data_dadid_seq' AS done_with;
-- dartings
CREATE TABLE dartings (
dartid SERIAL PRIMARY KEY,
sname CHAR(3) NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
date DATE NOT NULL,
darttime TIME(0) NULL
CONSTRAINT "Darttime: Cannot contain seconds or fractions thereof"
CHECK (EXTRACT(SECONDS FROM Darttime) = 0)
CONSTRAINT "Darttime >= 05:00"
CHECK (darttime::time >= '05:00'::time)
CONSTRAINT "Darttime <= 20:00"
CHECK (darttime::time <= '20:00'::time),
downtime TIME(0) NULL
CONSTRAINT "Downtime: Cannot contain seconds or fractions thereof"
CHECK (EXTRACT(SECONDS FROM Downtime) = 0)
CONSTRAINT "Downtime cannot be before Darttime"
CHECK (downtime >= darttime)
CONSTRAINT "Downtime cannot be later than 1 hour after Darttime"
CHECK (downtime <= darttime
+ '1 hour'::interval),
pickuptime TIME(0) NULL
CONSTRAINT "Pickuptime: Cannot contain seconds or fractions thereof"
CHECK (EXTRACT(SECONDS FROM Pickuptime) = 0)
CONSTRAINT "Pickuptime cannot be before Downtime"
CHECK (pickuptime >= downtime)
CONSTRAINT "Pickuptime cannot be later than 90 minutes after Downtime"
CHECK (Pickuptime < downtime + '90 minutes'::interval)
CONSTRAINT "Pickuptime cannot be before Darttime"
CHECK (pickuptime >= darttime)
CONSTRAINT "Pickuptime cannot be later than 90 minutes after Darttime"
CHECK (pickuptime < darttime
+ '90 minutes'::interval),
drug CHAR(1) NOT NULL
CONSTRAINT "Drug on DRUGS" REFERENCES drugs,
mass NUMERIC(3, 1) NULL
CONSTRAINT "Mass cannot be less than 1Kg"
CHECK (mass >= 1)
CONSTRAINT "Mass cannot be more than 40Kg"
CHECK (mass <= 40),
logisticnotes TEXT NULL CONSTRAINT "Logisticnotes: Cannot be empty or only whitespace characters"
CHECK(btrim(Logisticnotes, E' \r\n\t\f\x0B') <> ''),
dartcomments TEXT NULL CONSTRAINT "Dartcomments: Cannot be empty or only whitespace characters"
CHECK(btrim(Dartcomments, E' \r\n\t\f\x0B') <> ''),
crnotes TEXT NULL CONSTRAINT "CRnotes: Cannot be empty or only whitespace characters"
CHECK(btrim(CRnotes, E' \r\n\t\f\x0B') <> ''),
chnotes TEXT NULL CONSTRAINT "Chnotes: Cannot be empty or only whitespace characters"
CHECK(btrim(Chnotes, E' \r\n\t\f\x0B') <> ''),
ulnotes TEXT NULL CONSTRAINT "Ulnotes: Cannot be empty or only whitespace characters"
CHECK(btrim(Ulnotes, E' \r\n\t\f\x0B') <> ''),
hunotes TEXT NULL CONSTRAINT "Hunotes: Cannot be empty or only whitespace characters"
CHECK(btrim(Hunotes, E' \r\n\t\f\x0B') <> ''),
dphysnotes TEXT NULL CONSTRAINT "Dphysnotes: Cannot be empty or only whitespace characters"
CHECK(btrim(Dphysnotes, E' \r\n\t\f\x0B') <> ''),
pcvnotes TEXT NULL CONSTRAINT "PCVnotes: Cannot be empty or only whitespace characters"
CHECK(btrim(PCVnotes, E' \r\n\t\f\x0B') <> ''),
bodytempnotes TEXT NULL CONSTRAINT "Bodytempnotes: Cannot be empty or only whitespace characters"
CHECK(btrim(Bodytempnotes, E' \r\n\t\f\x0B') <> ''),
dsamplenotes TEXT NULL CONSTRAINT "Dsamplenotes: Cannot be empty or only whitespace characters"
CHECK(btrim(Dsamplenotes, E' \r\n\t\f\x0B') <> ''),
teethnotes TEXT NULL CONSTRAINT "Teethnotes: Cannot be empty or only whitespace characters"
CHECK(btrim(Teethnotes, E' \r\n\t\f\x0B') <> ''),
caninenotes TEXT NULL CONSTRAINT "Caninenotes: Cannot be empty or only whitespace characters"
CHECK(btrim(Caninenotes, E' \r\n\t\f\x0B') <> ''),
testesnotes TEXT NULL CONSTRAINT "Testesnotes: Cannot be empty or only whitespace characters"
CHECK(btrim(Testesnotes, E' \r\n\t\f\x0B') <> ''),
ticknotes TEXT NULL CONSTRAINT "Ticknotes: Cannot be empty or only whitespace characters"
CHECK(btrim(Ticknotes, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON dartings TO GROUP babase_readers;
GRANT SELECT ON dartings TO GROUP babase_editors;
GRANT INSERT ON dartings TO GROUP babase_editors;
GRANT UPDATE ON dartings TO GROUP babase_editors;
GRANT DELETE ON dartings TO GROUP babase_editors;
SELECT 'dartings' AS done_with;
GRANT SELECT ON dartings_dartid_seq TO GROUP babase_readers;
GRANT SELECT ON dartings_dartid_seq TO GROUP babase_editors;
GRANT UPDATE ON dartings_dartid_seq TO GROUP babase_editors;
SELECT 'dartings_dartid_seq' AS done_with;
-- anesths
CREATE TABLE anesths (
anesthid SERIAL PRIMARY KEY,
dartid INT NOT NULL
CONSTRAINT "Dartid on DARTINGS" REFERENCES dartings,
drug CHAR(1) NOT NULL
CONSTRAINT "Drug on DRUGS" REFERENCES drugs,
antime TIME(0) NULL
CONSTRAINT "antime: Cannot contain seconds or fractions thereof"
CHECK (EXTRACT(SECONDS FROM antime) = 0),
anamount NUMERIC(3, 2) NOT NULL
CONSTRAINT "Anamount must not be more than 1.0"
CHECK (anamount <= 1.0)
CONSTRAINT "Anamount cannot be negative"
CHECK (anamount >= 0)
) WITHOUT OIDS;
GRANT SELECT ON anesths TO GROUP babase_readers;
GRANT SELECT ON anesths TO GROUP babase_editors;
GRANT INSERT ON anesths TO GROUP babase_editors;
GRANT UPDATE ON anesths TO GROUP babase_editors;
GRANT DELETE ON anesths TO GROUP babase_editors;
SELECT 'anesths' AS done_with;
GRANT SELECT ON anesths_anesthid_seq TO GROUP babase_readers;
GRANT SELECT ON anesths_anesthid_seq TO GROUP babase_editors;
GRANT UPDATE ON anesths_anesthid_seq TO GROUP babase_editors;
SELECT 'anesths_anesthid_seq' AS done_with;
--dart_sample_types
CREATE TABLE dart_sample_types (
ds_type SMALLINT PRIMARY KEY NOT NULL,
ds_cat SMALLINT NOT NULL
CONSTRAINT "DS_Cat on DART_SAMPLE_CATS" REFERENCES dart_sample_cats,
descr TEXT NOT NULL
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> ''),
sex CHAR(1)
CONSTRAINT "sex: Cannot be empty or only whitespace characters"
CHECK(btrim(sex, E' \r\n\t\f\x0B') <> '')
CONSTRAINT "Sex either M or F if not NULL"
CHECK(sex IS NULL OR sex = 'M' OR sex = 'F'),
minimum SMALLINT NOT NULL,
maximum SMALLINT NOT NULL
) WITHOUT OIDS;
GRANT SELECT ON dart_sample_types TO GROUP babase_readers;
GRANT SELECT ON dart_sample_types TO GROUP babase_editors;
GRANT INSERT ON dart_sample_types TO GROUP babase_editors;
GRANT UPDATE ON dart_sample_types TO GROUP babase_editors;
GRANT DELETE ON dart_sample_types TO GROUP babase_editors;
SELECT 'dart_sample_types' AS done_with;
--dart_samples
CREATE TABLE dart_samples (
ds_id SERIAL PRIMARY KEY,
dartid INT NOT NULL
CONSTRAINT "Dartid on DARTINGS" REFERENCES dartings,
ds_type INT NOT NULL
CONSTRAINT "DS_Type on DART_SAMPLE_TYPES" REFERENCES dart_sample_types,
num SMALLINT NOT NULL
CONSTRAINT "Num must be > 0"
CHECK (num>0)
) WITHOUT OIDS;
GRANT SELECT ON dart_samples TO GROUP babase_readers;
GRANT SELECT ON dart_samples TO GROUP babase_editors;
GRANT INSERT ON dart_samples TO GROUP babase_editors;
GRANT UPDATE ON dart_samples TO GROUP babase_editors;
GRANT DELETE ON dart_samples TO GROUP babase_editors;
SELECT 'dart_samples' AS done_with;
GRANT SELECT ON dart_samples_ds_id_seq TO GROUP babase_readers;
GRANT SELECT ON dart_samples_ds_id_seq TO GROUP babase_editors;
GRANT UPDATE ON dart_samples_ds_id_seq TO GROUP babase_editors;
SELECT 'dart_samples_ds_id_seq' AS done_with;
-- crownrumps
CREATE TABLE crownrumps (
crid SERIAL PRIMARY KEY,
dartid INT NOT NULL
CONSTRAINT "Dartid on DARTINGS" REFERENCES dartings,
crlength NUMERIC(3, 1) NOT NULL
CONSTRAINT "CRlength may not be less than 10cm"
CHECK (crlength >= 10),
crseq smallint NULL
CONSTRAINT "CRseq cannot be less than 1"
CHECK (crseq >= 1),
crobserver VARCHAR(4) NULL
CONSTRAINT "CRobserver on Observers" REFERENCES observers
) WITHOUT OIDS;
GRANT SELECT ON crownrumps TO GROUP babase_readers;
GRANT SELECT ON crownrumps TO GROUP babase_editors;
GRANT INSERT ON crownrumps TO GROUP babase_editors;
GRANT UPDATE ON crownrumps TO GROUP babase_editors;
GRANT DELETE ON crownrumps TO GROUP babase_editors;
SELECT 'crownrumps' AS done_with;
GRANT SELECT ON crownrumps_crid_seq TO GROUP babase_readers;
GRANT SELECT ON crownrumps_crid_seq TO GROUP babase_editors;
GRANT UPDATE ON crownrumps_crid_seq TO GROUP babase_editors;
SELECT 'crownrumps_crid_seq' AS done_with;
-- chests
CREATE TABLE chests (
chid SERIAL PRIMARY KEY,
dartid INT NOT NULL
CONSTRAINT "Dartid on DARTINGS" REFERENCES dartings,
chcircum NUMERIC(3, 1) NOT NULL
CONSTRAINT "Chcircum may not be less than 25cm"
CHECK (chcircum >= 25),
chunadjusted NUMERIC(3, 1) NULL
CONSTRAINT "Chunadjusted may not be less than 25cm"
CHECK (chunadjusted >= 25),
chseq smallint NULL
CONSTRAINT "Chseq cannot be less than 1"
CHECK (chseq >= 1),
chobserver VARCHAR(4) NULL
CONSTRAINT "Chobserver on Observers" REFERENCES observers
) WITHOUT OIDS;
GRANT SELECT ON chests TO GROUP babase_readers;
GRANT SELECT ON chests TO GROUP babase_editors;
GRANT INSERT ON chests TO GROUP babase_editors;
GRANT UPDATE ON chests TO GROUP babase_editors;
GRANT DELETE ON chests TO GROUP babase_editors;
SELECT 'chests' AS done_with;
GRANT SELECT ON chests_chid_seq TO GROUP babase_readers;
GRANT SELECT ON chests_chid_seq TO GROUP babase_editors;
GRANT UPDATE ON chests_chid_seq TO GROUP babase_editors;
SELECT 'chests_chid_seq' AS done_with;
-- ulnas
CREATE TABLE ulnas (
ulid SERIAL PRIMARY KEY,
dartid INT NOT NULL
CONSTRAINT "Dartid on DARTINGS" REFERENCES dartings,
ullength NUMERIC(3, 1) NOT NULL
CONSTRAINT "Ullength may not be less than 10cm"
CHECK (ullength >= 10)
CONSTRAINT "Ullength may not be greater than 35cm"
CHECK (ullength <= 35),
ulunadjusted NUMERIC(3, 1) NULL
CONSTRAINT "Ulunadjusted may not be less than 10cm"
CHECK (ulunadjusted >= 10)
CONSTRAINT "Ulunadjusted may not be greater than 35cm"
CHECK (ulunadjusted <= 35),
ulseq SMALLINT NULL
CONSTRAINT "Ulseq cannot be less than 1"
CHECK (ulseq >= 1),
ulobserver VARCHAR(4) NULL
CONSTRAINT "Ulobserver on Observers" REFERENCES observers
) WITHOUT OIDS;
GRANT SELECT ON ulnas TO GROUP babase_readers;
GRANT SELECT ON ulnas TO GROUP babase_editors;
GRANT INSERT ON ulnas TO GROUP babase_editors;
GRANT UPDATE ON ulnas TO GROUP babase_editors;
GRANT DELETE ON ulnas TO GROUP babase_editors;
SELECT 'ulnas' AS done_with;
GRANT SELECT ON ulnas_ulid_seq TO GROUP babase_readers;
GRANT SELECT ON ulnas_ulid_seq TO GROUP babase_editors;
GRANT UPDATE ON ulnas_ulid_seq TO GROUP babase_editors;
SELECT 'ulnas_ulid_seq' AS done_with;
-- humeruses
CREATE TABLE humeruses (
huid SERIAL PRIMARY KEY,
dartid INT NOT NULL
CONSTRAINT "Dartid on DARTINGS" REFERENCES dartings,
hulength NUMERIC(3, 1) NOT NULL
CONSTRAINT "Hulength may not be less than 10cm"
CHECK (hulength >= 10)
CONSTRAINT "Hulength may not be greater than 35cm"
CHECK (hulength <= 35),
huunadjusted NUMERIC(3, 1) NULL
CONSTRAINT "Huunadjusted may not be less than 10cm"
CHECK (huunadjusted >= 10)
CONSTRAINT "Huunadjusted may not be greater than 35cm"
CHECK (huunadjusted <= 35),
huseq SMALLINT NULL
CONSTRAINT "Huseq cannot be less than 1"
CHECK (huseq >= 1),
huobserver VARCHAR(4) NULL
CONSTRAINT "Huobserver on Observers" REFERENCES observers
) WITHOUT OIDS;
GRANT SELECT ON humeruses TO GROUP babase_readers;
GRANT SELECT ON humeruses TO GROUP babase_editors;
GRANT INSERT ON humeruses TO GROUP babase_editors;
GRANT UPDATE ON humeruses TO GROUP babase_editors;
GRANT DELETE ON humeruses TO GROUP babase_editors;
SELECT 'humeruses' AS done_with;
GRANT SELECT ON humeruses_huid_seq TO GROUP babase_readers;
GRANT SELECT ON humeruses_huid_seq TO GROUP babase_editors;
GRANT UPDATE ON humeruses_huid_seq TO GROUP babase_editors;
SELECT 'humeruses_huid_seq' AS done_with;
-- dphys
CREATE TABLE dphys (
dphysid SERIAL PRIMARY KEY,
dartid INT NOT NULL
CONSTRAINT "Dartid on DARTINGS" REFERENCES dartings,
pulse SMALLINT NULL
CONSTRAINT "Pulse must be greater than 0"
CHECK (pulse > 0),
respiration SMALLINT NULL
CONSTRAINT "Respiration must be greater than 0"
CHECK (respiration > 0),
ringnode CHAR(1) NULL
CONSTRAINT "Ringnode on LYMPHSTATES" REFERENCES lymphstates,
lingnode CHAR(1) NULL
CONSTRAINT "Lingnode on LYMPHSTATES" REFERENCES lymphstates,
raxnode CHAR(1) NULL
CONSTRAINT "Raxnode on LYMPHSTATES" REFERENCES lymphstates,
laxnode CHAR(1) NULL
CONSTRAINT "Laxnode on LYMPHSTATES" REFERENCES lymphstates,
rsubmandnode CHAR(1) NULL
CONSTRAINT "Rsubmandnode on LYMPHSTATES" REFERENCES lymphstates,
lsubmandnode CHAR(1) NULL
CONSTRAINT "Lsubmandnode on LYMPHSTATES" REFERENCES lymphstates
) WITHOUT OIDS;
GRANT SELECT ON dphys TO GROUP babase_readers;
GRANT SELECT ON dphys TO GROUP babase_editors;
GRANT INSERT ON dphys TO GROUP babase_editors;
GRANT UPDATE ON dphys TO GROUP babase_editors;
GRANT DELETE ON dphys TO GROUP babase_editors;
SELECT 'dphys' AS done_with;
GRANT SELECT ON dphys_dphysid_seq TO GROUP babase_readers;
GRANT SELECT ON dphys_dphysid_seq TO GROUP babase_editors;
GRANT UPDATE ON dphys_dphysid_seq TO GROUP babase_editors;
SELECT 'dphys_dphysid_seq' AS done_with;
-- pcvs
CREATE TABLE pcvs (
pcvid SERIAL PRIMARY KEY,
dartid INT NOT NULL
CONSTRAINT "Dartid on DARTINGS" REFERENCES dartings,
pcv SMALLINT NOT NULL
CONSTRAINT "PCV >= 1"
CHECK (pcv >= 1)
CONSTRAINT "PCV <= 99"
CHECK (pcv <= 99),
pcvseq SMALLINT NULL
CONSTRAINT "PCVseq cannot be less than 1"
CHECK (pcvseq >= 1)
) WITHOUT OIDS;
GRANT SELECT ON pcvs TO GROUP babase_readers;
GRANT SELECT ON pcvs TO GROUP babase_editors;
GRANT INSERT ON pcvs TO GROUP babase_editors;
GRANT UPDATE ON pcvs TO GROUP babase_editors;
GRANT DELETE ON pcvs TO GROUP babase_editors;
SELECT 'pcvs' AS done_with;
GRANT SELECT ON pcvs_pcvid_seq TO GROUP babase_readers;
GRANT SELECT ON pcvs_pcvid_seq TO GROUP babase_editors;
GRANT UPDATE ON pcvs_pcvid_seq TO GROUP babase_editors;
SELECT 'pcvs_pcvid_seq' AS done_with;
-- bodytemps
CREATE TABLE bodytemps (
btid SERIAL PRIMARY KEY,
dartid INT NOT NULL
CONSTRAINT "Dartid on DARTINGS" REFERENCES dartings,
btemp NUMERIC(3,1) NOT NULL
CONSTRAINT "Btemp cannot be less than than 25"
CHECK (btemp >= 25)
CONSTRAINT "Btemp cannot be more than 45"
CHECK (btemp <= 45),
bttime TIME(0) NULL
CONSTRAINT "bttime: Cannot contain seconds or fractions thereof"
CHECK (EXTRACT(SECONDS FROM bttime) = 0)
) WITHOUT OIDS;
GRANT SELECT ON bodytemps TO GROUP babase_readers;
GRANT SELECT ON bodytemps TO GROUP babase_editors;
GRANT INSERT ON bodytemps TO GROUP babase_editors;
GRANT UPDATE ON bodytemps TO GROUP babase_editors;
GRANT DELETE ON bodytemps TO GROUP babase_editors;
SELECT 'bodytemps' AS done_with;
GRANT SELECT ON bodytemps_btid_seq TO GROUP babase_readers;
GRANT SELECT ON bodytemps_btid_seq TO GROUP babase_editors;
GRANT UPDATE ON bodytemps_btid_seq TO GROUP babase_editors;
SELECT 'bodytemps_btid_seq' AS done_with;
-- wbc_counts
CREATE TABLE wbc_counts(
wcid SERIAL PRIMARY KEY,
dartid INT NOT NULL
CONSTRAINT "Dartid on DARTINGS" REFERENCES dartings,
count_date DATE NOT NULL,
basophils SMALLINT NOT NULL
CONSTRAINT "Basophils cannot be negative"
CHECK (basophils >= 0),
eosinophils SMALLINT NOT NULL
CONSTRAINT "Eosinophils cannot be negative"
CHECK (eosinophils >= 0),
monocytes SMALLINT NOT NULL
CONSTRAINT "Monocytes cannot be negative"
CHECK (monocytes >= 0),
lymphocytes SMALLINT NOT NULL
CONSTRAINT "Lymphocytes cannot be negative"
CHECK (lymphocytes >= 0),
neutrophils SMALLINT NOT NULL
CONSTRAINT "Neutrophils cannot be negative"
CHECK (neutrophils >= 0),
counted_by VARCHAR(4) NOT NULL
CONSTRAINT "Counted_By on OBSERVERS"
REFERENCES observers,
slide_number SMALLINT NOT NULL
CONSTRAINT "Slide_number must be positive"
CHECK (slide_number > 0)
) WITHOUT OIDS;
GRANT SELECT ON wbc_counts TO GROUP babase_readers;
GRANT SELECT ON wbc_counts TO GROUP babase_editors;
GRANT INSERT ON wbc_counts TO GROUP babase_editors;
GRANT UPDATE ON wbc_counts TO GROUP babase_editors;
GRANT DELETE ON wbc_counts TO GROUP babase_editors;
SELECT 'wbc_counts' AS done_with;
GRANT SELECT ON wbc_counts_wcid_seq TO GROUP babase_readers;
GRANT SELECT ON wbc_counts_wcid_seq TO GROUP babase_editors;
GRANT UPDATE ON wbc_counts_wcid_seq TO GROUP babase_editors;
SELECT 'wbc_counts_wcid_seq' AS done_with;
-- teeth
CREATE TABLE teeth (
teethid SERIAL PRIMARY KEY,
dartid INT NOT NULL
CONSTRAINT "Dartid on DARTINGS" REFERENCES dartings,
tooth VARCHAR(5) NOT NULL
CONSTRAINT "Tooth on TOOTHCODES" REFERENCES toothcodes,
tstate CHAR(1) NULL -- dis-allow NULL in triggers
CONSTRAINT "Tstate on TSTATES" REFERENCES tstates,
tcondition CHAR(1) NULL
CONSTRAINT "Tcondition on TCONDITIONS" REFERENCES tconditions
CONSTRAINT "Tcondition must be NULL when Tstate is M"
CHECK (NOT(tstate = 'M'
AND tcondition IS NOT NULL))
CONSTRAINT "Tcondition must be non-NULL unless Tstate is M"
CHECK (NOT(tstate <> 'M'
AND tcondition IS NULL))
) WITHOUT OIDS;
GRANT SELECT ON teeth TO GROUP babase_readers;
GRANT SELECT ON teeth TO GROUP babase_editors;
GRANT INSERT ON teeth TO GROUP babase_editors;
GRANT UPDATE ON teeth TO GROUP babase_editors;
GRANT DELETE ON teeth TO GROUP babase_editors;
SELECT 'teeth' AS done_with;
GRANT SELECT ON teeth_teethid_seq TO GROUP babase_readers;
GRANT SELECT ON teeth_teethid_seq TO GROUP babase_editors;
GRANT UPDATE ON teeth_teethid_seq TO GROUP babase_editors;
SELECT 'teeth_teethid_seq' AS done_with;
-- testes_foo tables
-- TESTES_ARC
CREATE TABLE testes_ARC (
testesid SERIAL PRIMARY KEY,
dartid INT NOT NULL
CONSTRAINT "Dartid on DARTINGS" REFERENCES dartings,
testside CHAR(1) NOT NULL
CONSTRAINT "Testside must be L or R"
CHECK (testside = 'L' or testside = 'R'),
testlength NUMERIC(4,1) NULL
CONSTRAINT "Testlength cannot be less than 15mm"
CHECK (testlength >= 15)
CONSTRAINT "Testlength cannot be more than 140mm"
CHECK (testlength <= 140),
testwidth NUMERIC(3,1) NULL
CONSTRAINT "Testwidth cannot be less than 10mm"
CHECK (testwidth >= 10)
CONSTRAINT "Testwidth cannot be more than 95mm"
CHECK (testwidth <= 95),
testseq SMALLINT NULL
CONSTRAINT "Testseq cannot be less than 1"
CHECK (testseq >= 1)
CONSTRAINT "Testlength and Testwidth cannot both be NULL"
CHECK (Testlength IS NOT NULL OR Testwidth IS NOT NULL)
) WITHOUT OIDS;
GRANT SELECT ON testes_ARC TO GROUP babase_readers;
GRANT SELECT ON testes_ARC TO GROUP babase_editors;
GRANT INSERT ON testes_ARC TO GROUP babase_editors;
GRANT UPDATE ON testes_ARC TO GROUP babase_editors;
GRANT DELETE ON testes_ARC TO GROUP babase_editors;
SELECT 'testes_ARC' AS done_with;
GRANT SELECT ON testes_ARC_testesid_seq TO GROUP babase_readers;
GRANT SELECT ON testes_ARC_testesid_seq TO GROUP babase_editors;
GRANT UPDATE ON testes_ARC_testesid_seq TO GROUP babase_editors;
SELECT 'testes_ARC_testesid_seq' AS done_with;
-- TESTES_DIAM
CREATE TABLE testes_DIAM (
testesid SERIAL PRIMARY KEY,
dartid INT NOT NULL
CONSTRAINT "Dartid on DARTINGS" REFERENCES dartings,
testside CHAR(1) NOT NULL
CONSTRAINT "Testside must be L or R"
CHECK (testside = 'L' or testside = 'R'),
testlength NUMERIC(3,1) NULL
CONSTRAINT "Testlength cannot be less than 15mm"
CHECK (testlength >= 15)
CONSTRAINT "Testlength cannot be more than 75mm"
CHECK (testlength <= 75),
testwidth NUMERIC(3,1) NULL
CONSTRAINT "Testwidth cannot be less than 10mm"
CHECK (testwidth >= 10)
CONSTRAINT "Testwidth cannot be more than 51mm"
CHECK (testwidth <= 51),
testseq SMALLINT NULL
CONSTRAINT "Testseq cannot be less than 1"
CHECK (testseq >= 1)
CONSTRAINT "Testlength and Testwidth cannot both be NULL"
CHECK (Testlength IS NOT NULL OR Testwidth IS NOT NULL)
) WITHOUT OIDS;
GRANT SELECT ON testes_DIAM TO GROUP babase_readers;
GRANT SELECT ON testes_DIAM TO GROUP babase_editors;
GRANT INSERT ON testes_DIAM TO GROUP babase_editors;
GRANT UPDATE ON testes_DIAM TO GROUP babase_editors;
GRANT DELETE ON testes_DIAM TO GROUP babase_editors;
SELECT 'testes_DIAM' AS done_with;
GRANT SELECT ON testes_DIAM_testesid_seq TO GROUP babase_readers;
GRANT SELECT ON testes_DIAM_testesid_seq TO GROUP babase_editors;
GRANT UPDATE ON testes_DIAM_testesid_seq TO GROUP babase_editors;
SELECT 'testes_DIAM_testesid_seq' AS done_with;
-- ticks
CREATE TABLE ticks (
tickid SERIAL PRIMARY KEY,
dartid INT NOT NULL
CONSTRAINT "Dartid on DARTINGS" REFERENCES dartings,
bodypart VARCHAR(8) NOT NULL
CONSTRAINT "Bodypart on BODYPARTS" REFERENCES bodyparts,
tickkind VARCHAR(1) NOT NULL
CONSTRAINT "Tickkind on PARASITES" REFERENCES parasites,
tickcount SMALLINT NULL
CONSTRAINT "Tickcount cannot be less than 0"
CHECK (tickcount >= 0)
CONSTRAINT "Tickcount cannot be greater than 250"
CHECK (tickcount <= 250),
tickstatus SMALLINT NOT NULL
CONSTRAINT "Tickstatus on TICKSTATUSES" REFERENCES tickstatuses
CONSTRAINT "Tickstatus must be 0 if and only if Tickcount is 0"
CHECK(COALESCE((tickcount <> 0
AND tickstatus <> 0)
OR (tickcount = 0
AND tickstatus = 0)
, tickcount IS NULL
AND tickstatus <> 0))
CONSTRAINT "Tickstatus must be 1 when Tickcount is non-NULL and non-zero"
CHECK (tickcount IS NULL
OR tickcount = 0
OR tickstatus = 1),
tickbpnotes TEXT NULL
CONSTRAINT "tickbpnotes: Cannot be empty or only whitespace characters"
CHECK(btrim(tickbpnotes, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON ticks TO GROUP babase_readers;
GRANT SELECT ON ticks TO GROUP babase_editors;
GRANT INSERT ON ticks TO GROUP babase_editors;
GRANT UPDATE ON ticks TO GROUP babase_editors;
GRANT DELETE ON ticks TO GROUP babase_editors;
SELECT 'ticks' AS done_with;
GRANT SELECT ON ticks_tickid_seq TO GROUP babase_readers;
GRANT SELECT ON ticks_tickid_seq TO GROUP babase_editors;
GRANT UPDATE ON ticks_tickid_seq TO GROUP babase_editors;
SELECT 'ticks_tickid_seq' AS done_with;
-- swerb_departs_data
CREATE TABLE swerb_departs_data (
did SERIAL PRIMARY KEY,
date DATE NOT NULL
CONSTRAINT "Date must be on or after 1981-11-01"
CHECK (date >= '1981-11-01'),
time TIME(0) NULL
CONSTRAINT "Time >= 04:00 and Time <= 20:00"
CHECK(time IS NULL
OR (time >= '04:00' AND time <= '20:00'))
CONSTRAINT "time: Cannot contain seconds or fractions thereof"
CHECK (EXTRACT(SECONDS FROM time) = 0)
) WITHOUT OIDS;
GRANT SELECT ON swerb_departs_data TO GROUP babase_readers;
GRANT SELECT ON swerb_departs_data TO GROUP babase_editors;
GRANT INSERT ON swerb_departs_data TO GROUP babase_editors;
GRANT UPDATE ON swerb_departs_data TO GROUP babase_editors;
GRANT DELETE ON swerb_departs_data TO GROUP babase_editors;
SELECT 'swerb_departs_data' AS done_with;
GRANT SELECT ON swerb_departs_data_did_seq TO GROUP babase_readers;
GRANT SELECT ON swerb_departs_data_did_seq TO GROUP babase_editors;
GRANT UPDATE ON swerb_departs_data_did_seq TO GROUP babase_editors;
SELECT 'swerb_departs_data_did_seq' AS done_with;
-- swerb_departs_gps
CREATE TABLE swerb_departs_gps (
did INT PRIMARY KEY
CONSTRAINT "DID on SWERB_DEPARTS_DATA" REFERENCES swerb_departs_data,
xyloc GEOMETRY(POINT, 32737) NOT NULL
CONSTRAINT "X coord of XYLoc must have only 1 digit right of the decimal"
CHECK(ST_X(xyloc) * 10 = TRUNC(ST_X(xyloc) * 10))
CONSTRAINT "Y coord of XYLoc must have only 1 digit right of the decimal"
CHECK(ST_Y(xyloc) * 10 = TRUNC(ST_Y(xyloc) * 10))
CONSTRAINT "XYLoc within study area (see Babase Techinical Specifications)"
CHECK(ST_Intersects(xyloc
, ST_PolygonFromText('POLYGON((42300.0 9497000.0, 651000.0 9497000.0, 651000.0 9894500.0, 42300.0 9894500.0, 42300.0 9497000.0))'
, 32737))),
altitude NUMERIC(5,1) NULL
CONSTRAINT "Altitude must be between 0 and bb_altitude max, inclusive"
CHECK(altitude IS NULL
OR (0 <= altitude
AND altitude <= 10000)),
pdop NUMERIC(3,1) NULL
CONSTRAINT "PDOP must be between 0 and 50, inclusive"
CHECK(pdop IS NULL
OR (0 <= pdop
AND pdop <= 50)),
accuracy NUMERIC(3,1) NULL
CONSTRAINT "Accuracy must be between 0 and 15, inclusive"
CHECK(accuracy IS NULL
OR (0 <= accuracy
AND accuracy <= 15)),
gps NUMERIC(2) NULL
CONSTRAINT "GPS on GPS_UNITS" REFERENCES gps_units,
garmincode TEXT NULL
CONSTRAINT "Garmincode: Cannot be only whitespace characters"
CHECK(Garmincode IS NULL OR Garmincode = '' OR btrim(Garmincode, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON swerb_departs_gps TO GROUP babase_readers;
GRANT SELECT ON swerb_departs_gps TO GROUP babase_editors;
GRANT INSERT ON swerb_departs_gps TO GROUP babase_editors;
GRANT UPDATE ON swerb_departs_gps TO GROUP babase_editors;
GRANT DELETE ON swerb_departs_gps TO GROUP babase_editors;
SELECT 'swerb_departs_gps' AS done_with;
-- swerb_observers
CREATE TABLE swerb_observers (
swerboid SERIAL PRIMARY KEY,
did INT CONSTRAINT "Did on SWERB_DEPARTS_DATA"
REFERENCES swerb_departs_data,
observer VARCHAR(4) NOT NULL
CONSTRAINT "Observer on OBSERVERS" REFERENCES observers,
role VARCHAR(75) NOT NULL
CONSTRAINT "Role on OBSERVER_ROLES" REFERENCES observer_roles
) WITHOUT OIDS;
GRANT SELECT ON swerb_observers TO GROUP babase_readers;
GRANT SELECT ON swerb_observers TO GROUP babase_editors;
GRANT INSERT ON swerb_observers TO GROUP babase_editors;
GRANT UPDATE ON swerb_observers TO GROUP babase_editors;
GRANT DELETE ON swerb_observers TO GROUP babase_editors;
SELECT 'swerb_observers' AS done_with;
GRANT SELECT ON swerb_observers_swerboid_seq TO GROUP babase_readers;
GRANT SELECT ON swerb_observers_swerboid_seq TO GROUP babase_editors;
GRANT UPDATE ON swerb_observers_swerboid_seq TO GROUP babase_editors;
SELECT 'swerb_observers_swerboid_seq' AS done_with;
-- swerb_bes
CREATE TABLE swerb_bes (
beid SERIAL PRIMARY KEY,
did INT NOT NULL
CONSTRAINT "Did on SWERB_DEPARTS_DATA" REFERENCES swerb_departs_data,
focal_grp NUMERIC(6,4) NOT NULL
CONSTRAINT "Focal_grp on GROUPS" REFERENCES groups,
start TIME(0) NULL
CONSTRAINT "Start >= 05:00 and Start <= 20:00"
CHECK(start IS NULL
OR (start >= '05:00' AND start <= '20:00'))
CONSTRAINT "Start: Cannot contain seconds or fractions thereof"
CHECK (EXTRACT(SECONDS FROM Start) = 0),
btimeest BOOLEAN NULL
CONSTRAINT "Start must be NULL if Btimeest is NULL"
CHECK((Btimeest IS NULL AND Start IS NULL)
OR Btimeest IS NOT NULL),
bsource VARCHAR(5) NULL
CONSTRAINT "SWERB_BES.Bsource on SWERB_TIME_SOURCES"
REFERENCES swerb_time_sources
CONSTRAINT "Btimeest must be NULL if and only if Bsource is NULL"
CHECK((Btimeest IS NULL AND Bsource IS NULL)
OR (Btimeest IS NOT NULL AND Bsource IS NOT NULL))
CONSTRAINT "Btimeest = FALSE when Bsource = NR"
CHECK((bsource = 'NR' AND btimeest = FALSE)
OR bsource IS NULL
OR bsource <> 'NR'),
stop TIME(0) NULL
CONSTRAINT "Stop >= 05:00 and Stop <= 20:00"
CHECK(stop IS NULL
OR (stop >= '05:00' AND stop <= '20:00'))
CONSTRAINT "Stop >= Start"
CHECK(start IS NULL
OR stop IS NULL
OR start <= stop)
CONSTRAINT "Stop: Cannot contain seconds or fractions thereof"
CHECK (EXTRACT(SECONDS FROM Stop) = 0),
etimeest BOOLEAN NULL
CONSTRAINT "Stop must be NULL if Etimeest is NULL"
CHECK((Etimeest IS NULL AND Stop IS NULL)
OR Etimeest IS NOT NULL),
esource VARCHAR(5) NULL
CONSTRAINT "Esource on SWERB_TIME_SOURCES"
REFERENCES swerb_time_sources
CONSTRAINT "Etimeest must be NULL if and only if Esource is NULL"
CHECK((Etimeest IS NULL AND Esource IS NULL)
OR (Etimeest IS NOT NULL AND Esource IS NOT NULL))
CONSTRAINT "Etimeest = FALSE when Esource = NR"
CHECK((esource = 'NR' AND etimeest = FALSE)
OR esource IS NULL
OR esource <> 'NR'),
seq SMALLINT NULL
CONSTRAINT "SWERB_BES.Seq cannot be less than 1"
CHECK(seq >= 1),
is_effort BOOLEAN NOT NULL,
notes TEXT NULL
CONSTRAINT "Notes: Cannot be empty or only whitespace characters"
CHECK(btrim(Notes, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON swerb_bes TO GROUP babase_readers;
GRANT SELECT ON swerb_bes TO GROUP babase_editors;
GRANT INSERT ON swerb_bes TO GROUP babase_editors;
GRANT UPDATE ON swerb_bes TO GROUP babase_editors;
GRANT DELETE ON swerb_bes TO GROUP babase_editors;
SELECT 'swerb_bes' AS done_with;
GRANT SELECT ON swerb_bes_beid_seq TO GROUP babase_readers;
GRANT SELECT ON swerb_bes_beid_seq TO GROUP babase_editors;
GRANT UPDATE ON swerb_bes_beid_seq TO GROUP babase_editors;
SELECT 'swerb_bes_beid_seq' AS done_with;
-- swerb_data (gps_points)
CREATE TABLE swerb_data (
swid SERIAL PRIMARY KEY,
beid INT NOT NULL
CONSTRAINT "BEId on SWERB_BEIDS" REFERENCES swerb_bes,
seen_grp NUMERIC(6,4) NOT NULL
CONSTRAINT "Seen_grp on GROUPS" REFERENCES groups,
lone_animal CHAR(3)
CONSTRAINT "Lone_Animal on BIOGRAPH" REFERENCES biograph (sname)
CONSTRAINT "Lone_Animal must be NULL unless Event = O"
CHECK(event = 'O' OR lone_animal IS NULL)
CONSTRAINT "Lone_Animals must be in the 10.0 group"
CHECK(lone_animal IS NULL OR seen_grp = 10.0),
event CHAR(1) NOT NULL
CONSTRAINT "SWERB_DATA.Event must be one of: B, E, H, W, O"
CHECK(event = 'B'
OR event = 'E'
OR event = 'H'
OR event = 'W'
OR event = 'O'),
time TIME(0) NULL
CONSTRAINT "time: Cannot contain seconds or fractions thereof"
CHECK (EXTRACT(SECONDS FROM time) = 0)
CONSTRAINT "Time >= 05:00 and Time <= 20:00"
CHECK(time IS NULL
OR (time >= '05:00' AND time <= '20:00')),
quad VARCHAR(4) NULL
CONSTRAINT "Quad on QUAD_DATA" REFERENCES quad_data,
xyloc GEOMETRY(POINT, 32737) NULL
CONSTRAINT "X coord of XYLoc must have only 1 digit right of the decimal"
CHECK(ST_X(xyloc) * 10 = TRUNC(ST_X(xyloc) * 10))
CONSTRAINT "Y coord of XYLoc must have only 1 digit right of the decimal"
CHECK(ST_Y(xyloc) * 10 = TRUNC(ST_Y(xyloc) * 10))
CONSTRAINT "XYLoc within study area (see Babase Techinical Specifications)"
CHECK(ST_Intersects(xyloc
, ST_PolygonFromText('POLYGON((42300.0 9497000.0, 651000.0 9497000.0, 651000.0 9894500.0, 42300.0 9894500.0, 42300.0 9497000.0))'
, 32737))),
altitude NUMERIC(5,1) NULL
CONSTRAINT "Altitude must be NULL if XYLoc is NULL"
CHECK((XYLoc IS NULL AND Altitude IS NULL)
OR XYLoc IS NOT NULL)
CONSTRAINT "Altitude must be between (inclusive) 0 and 10000"
CHECK(altitude IS NULL
OR (0 <= altitude
AND altitude <= 10000)),
pdop NUMERIC(3,1) NULL
CONSTRAINT "Pdop must be NULL if XYLoc is NULL"
CHECK((XYLoc IS NULL AND Pdop IS NULL)
OR XYLoc IS NOT NULL)
CONSTRAINT "PDOP must be between 0 and 50, inclusive"
CHECK(pdop IS NULL
OR (0 <= pdop
AND pdop <= 50)),
accuracy NUMERIC(3,1) NULL
CONSTRAINT "Accuracy must be NULL if XYLoc is NULL"
CHECK((XYLoc IS NULL AND Accuracy IS NULL)
OR XYLoc IS NOT NULL)
CONSTRAINT "Accuracy must be between 0 and 15, inclusive"
CHECK(accuracy IS NULL
OR (0 <= accuracy
AND accuracy <= 15)),
subgroup BOOLEAN NOT NULL,
ogdistance NUMERIC(3,0) NULL
CONSTRAINT "Ogdistance must be >= 0"
CHECK(ogdistance IS NULL OR ogdistance >= 0)
CONSTRAINT "Ogdistance must have a 0 in the ones digit"
CHECK(ogdistance IS NULL OR MOD(ogdistance, 10) = 0)
CONSTRAINT "Ogdistance must be NULL unless Event = O"
CHECK(event = 'O'
OR ogdistance IS NULL),
gps_datetime TIMESTAMP NULL
CONSTRAINT "GPS_Datetime must be NULL if XYLoc is NULL"
CHECK((XYLoc IS NULL AND GPS_Datetime IS NULL)
OR XYLoc IS NOT NULL)
CONSTRAINT "GPS_Datetime cannot be more than 15 minutes after Time"
CHECK (NOT(event<>'W' AND
gps_datetime::TIME
> (time + '15 minutes'::interval)))
CONSTRAINT "GPS_Datetime cannot be more than 30 minutes after Time, when Event is W"
CHECK (NOT(event='W' AND
gps_datetime::TIME
> (time + '30 minutes'::interval)))
CONSTRAINT "Time cannot be more than 5 minutes after GPS_Datetime"
CHECK (NOT(time > (gps_datetime
+ '5 minutes'::interval)::TIME)),
garmincode VARCHAR(10) NULL
CONSTRAINT "Garmincode must be NULL if XYLoc is NULL"
CHECK((XYLoc IS NULL AND Garmincode IS NULL)
OR XYLoc IS NOT NULL)
CONSTRAINT "Garmincode: Cannot be only whitespace characters"
CHECK(Garmincode IS NULL OR Garmincode = '' OR btrim(Garmincode, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON swerb_data TO GROUP babase_readers;
GRANT SELECT ON swerb_data TO GROUP babase_editors;
GRANT INSERT ON swerb_data TO GROUP babase_editors;
GRANT UPDATE ON swerb_data TO GROUP babase_editors;
GRANT DELETE ON swerb_data TO GROUP babase_editors;
SELECT 'swerb_data' AS done_with;
GRANT SELECT ON swerb_data_swid_seq TO GROUP babase_readers;
GRANT SELECT ON swerb_data_swid_seq TO GROUP babase_editors;
GRANT UPDATE ON swerb_data_swid_seq TO GROUP babase_editors;
SELECT 'swerb_data_swid_seq' AS done_with;
-- swerb_gws
CREATE TABLE swerb_gws (
loc VARCHAR(10) PRIMARY KEY
CONSTRAINT "Loc cannot contain lower case"
CHECK(loc = upper(loc))
CONSTRAINT "loc: Cannot be empty or only whitespace characters"
CHECK(btrim(loc, E' \r\n\t\f\x0B') <> ''),
type CHAR(1) NOT NULL
CONSTRAINT "Type on PLACE_TYPES" REFERENCES place_types
CONSTRAINT "Type must be G when Loc is UNK"
CHECK ((type = 'G' AND loc = 'UNK')
OR loc <> 'UNK'),
altname VARCHAR(45) NULL
CONSTRAINT "altname: Cannot be empty or only whitespace characters"
CHECK(btrim(altname, E' \r\n\t\f\x0B') <> ''),
start DATE NOT NULL
CONSTRAINT "Start cannot be before 1981-11-01"
CHECK (start >= CAST('1981-11-01' AS DATE)),
finish DATE NULL,
notes TEXT NULL
CONSTRAINT "notes: Cannot be empty or only whitespace characters"
CHECK(btrim(notes, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON swerb_gws TO GROUP babase_readers;
GRANT SELECT ON swerb_gws TO GROUP babase_editors;
GRANT INSERT ON swerb_gws TO GROUP babase_editors;
GRANT UPDATE ON swerb_gws TO GROUP babase_editors;
GRANT DELETE ON swerb_gws TO GROUP babase_editors;
SELECT 'swerb_gws' AS done_with;
-- swerb_gw_loc_data
CREATE TABLE swerb_gw_loc_data (
sgwlid SERIAL PRIMARY KEY,
loc VARCHAR(10) NOT NULL
CONSTRAINT "Loc on SWERB_GWS" REFERENCES swerb_gws
CONSTRAINT "The unknown grove (bb_swerb_unknown_grove) may not be located anywhere"
CHECK(loc <> 'UNK'),
date DATE NOT NULL,
time TIME(0) NULL
CONSTRAINT "time: Cannot contain seconds or fractions thereof"
CHECK (EXTRACT(SECONDS FROM time) = 0)
CONSTRAINT "Time >= 05:00 and Time <= 20:00"
CHECK(time IS NULL
OR (time >= '05:00' AND time <= '20:00')),
quad VARCHAR(4) NULL
CONSTRAINT "Quad on QUAD_DATA" REFERENCES quad_data
CONSTRAINT "Quad must be NULL after 1994-09-30"
CHECK (quad IS NULL OR date <= '1994-09-30'),
xysource CHAR(1) NULL
CONSTRAINT "XYSource on SWERB_XYSOURCES" REFERENCES swerb_xysources
CONSTRAINT "XYSource must be NULL if and only if XYLoc is NULL"
CHECK((XYSource IS NULL AND XYLoc IS NULL)
OR (XYSource IS NOT NULL AND XYLoc IS NOT NULL)),
xyloc GEOMETRY(POINT, 32737)
CONSTRAINT "X coord of XYLoc must have only 1 digit right of the decimal"
CHECK(ST_X(xyloc) * 10 = TRUNC(ST_X(xyloc) * 10))
CONSTRAINT "Y coord of XYLoc must have only 1 digit right of the decimal"
CHECK(ST_Y(xyloc) * 10 = TRUNC(ST_Y(xyloc) * 10))
CONSTRAINT "XYLoc within study area (see Babase Techinical Specifications)"
CHECK(ST_Intersects(xyloc
, ST_PolygonFromText('POLYGON((42300.0 9497000.0, 651000.0 9497000.0, 651000.0 9894500.0, 42300.0 9894500.0, 42300.0 9497000.0))'
, 32737)))
CONSTRAINT "XYLoc must be NULL before 1993-09-01 when XYSource is NULL"
CHECK (xyloc IS NULL
OR date >= '1993-09-01'
OR xysource IS NOT NULL),
altitude NUMERIC(5,1) NULL
CONSTRAINT "Altitude must be NULL if XYLoc is NULL"
CHECK((XYLoc IS NULL AND Altitude IS NULL)
OR XYLoc IS NOT NULL)
CONSTRAINT "Altitude must be between (inclusive) 0 and 10000"
CHECK(altitude IS NULL
OR (0 <= altitude
AND altitude <= 10000)),
CONSTRAINT "Cannot have sub-meter altitude accuracy before 2004-01-01"
CHECK(altitude IS NULL
OR date >= '2004-01-01'
OR altitude = TRUNC(altitude)),
pdop NUMERIC(3,1) NULL
CONSTRAINT "Pdop must be NULL if XYLoc is NULL"
CHECK((XYLoc IS NULL AND Pdop IS NULL)
OR XYLoc IS NOT NULL)
CONSTRAINT "PDOP must be between 0 and 50, inclusive"
CHECK(pdop IS NULL
OR (0 <= pdop
AND pdop <= 50))
CONSTRAINT "PDOP values must be NULL before 1993-09-01 and after 2001-01-31"
CHECK(pdop IS NULL
OR ('1993-09-01' <= date
AND date <= '2001-01-31')),
accuracy NUMERIC(3,1) NULL
CONSTRAINT "Accuracy must be NULL if XYLoc is NULL"
CHECK((XYLoc IS NULL AND Accuracy IS NULL)
OR XYLoc IS NOT NULL)
CONSTRAINT "Accuracy must be between 0 and 15, inclusive"
CHECK(accuracy IS NULL
OR (0 <= accuracy
AND accuracy <= 15))
CONSTRAINT "Accuracy must be NULL before 2001-02-01"
CHECK(accuracy IS NULL
OR date >= '2001-02-01'),
gps NUMERIC(2) NULL
CONSTRAINT "GPS must be NULL if XYLoc is NULL"
CHECK((XYLoc IS NULL AND GPS IS NULL)
OR XYLoc IS NOT NULL)
CONSTRAINT "GPS on GPS_UNITS" REFERENCES gps_units
CONSTRAINT "GPS units were not used before 1993-09-01"
CHECK(gps IS NULL OR date >= '1993-09-01'),
notes TEXT NULL
CONSTRAINT "Notes: Cannot be empty or only whitespace characters"
CHECK(btrim(Notes, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON swerb_gw_loc_data TO GROUP babase_readers;
GRANT SELECT ON swerb_gw_loc_data TO GROUP babase_editors;
GRANT INSERT ON swerb_gw_loc_data TO GROUP babase_editors;
GRANT UPDATE ON swerb_gw_loc_data TO GROUP babase_editors;
GRANT DELETE ON swerb_gw_loc_data TO GROUP babase_editors;
SELECT 'swerb_gw_loc_data' AS done_with;
GRANT SELECT ON swerb_gw_loc_data_sgwlid_seq TO GROUP babase_readers;
GRANT SELECT ON swerb_gw_loc_data_sgwlid_seq TO GROUP babase_editors;
GRANT UPDATE ON swerb_gw_loc_data_sgwlid_seq TO GROUP babase_editors;
SELECT 'swerb_gw_loc_data_sgwlid_seq' AS done_with;
-- swerb_loc_data
CREATE TABLE swerb_loc_data (
swid INT PRIMARY KEY
CONSTRAINT "SWid on SWERB_DATA" REFERENCES swerb_data,
loc VARCHAR(10) NOT NULL
CONSTRAINT "Loc on SWERB_GWS" REFERENCES swerb_gws,
adcode VARCHAR(2) NOT NULL
CONSTRAINT "ADcode on ADCODES" REFERENCES adcodes,
loc_status CHAR(1) NOT NULL
CONSTRAINT "Loc_Status on SWERB_LOC_STATUSES" REFERENCES swerb_loc_statuses
CONSTRAINT "Loc_Status must be C when Loc is UNK"
CHECK(loc <> 'UNK'
OR (loc = 'UNK' AND loc_status = 'C')),
adtime TIME(0) NULL
CONSTRAINT "adtime: Cannot contain seconds or fractions thereof"
CHECK (EXTRACT(SECONDS FROM adtime) = 0)
CONSTRAINT "ADTime >= 05:00 and ADTime <= 20:00"
CHECK(adtime IS NULL
OR (adtime >= '05:00' AND adtime <= '20:00'))
) WITHOUT OIDS;
GRANT SELECT ON swerb_loc_data TO GROUP babase_readers;
GRANT SELECT ON swerb_loc_data TO GROUP babase_editors;
GRANT INSERT ON swerb_loc_data TO GROUP babase_editors;
GRANT UPDATE ON swerb_loc_data TO GROUP babase_editors;
GRANT DELETE ON swerb_loc_data TO GROUP babase_editors;
SELECT 'swerb_loc_data' AS done_with;
-- swerb_loc_data_confidences
CREATE TABLE swerb_loc_data_confidences (
swid INT PRIMARY KEY
CONSTRAINT "SWId on SWERB_LOC_DATA" REFERENCES swerb_loc_data
-- Yes, this references SWERB_LOC_DATA, not SWERB_DATA
,
confidence NUMERIC(2,1) NOT NULL
CONSTRAINT "Confidence on SWERB_LOC_CONFIDENCES"
REFERENCES swerb_loc_confidences,
nearest_loc VARCHAR(10) NOT NULL
CONSTRAINT "Nearest_Loc on SWERB_GWS" REFERENCES swerb_gws
) WITHOUT OIDS;
GRANT SELECT ON swerb_loc_data_confidences TO GROUP babase_readers;
GRANT SELECT ON swerb_loc_data_confidences TO GROUP babase_editors;
GRANT INSERT ON swerb_loc_data_confidences TO GROUP babase_editors;
GRANT UPDATE ON swerb_loc_data_confidences TO GROUP babase_editors;
GRANT DELETE ON swerb_loc_data_confidences TO GROUP babase_editors;
SELECT 'swerb_loc_data_confidences' AS done_with;
-- swerb_loc_gps
CREATE TABLE swerb_loc_gps (
swid INT PRIMARY KEY
CONSTRAINT "SWid on SWERB_DATA" REFERENCES swerb_data,
xyloc GEOMETRY(POINT, 32737) NOT NULL
CONSTRAINT "X coord of XYLoc must have only 1 digit right of the decimal"
CHECK(ST_X(xyloc) * 10 = TRUNC(ST_X(xyloc) * 10))
CONSTRAINT "Y coord of XYLoc must have only 1 digit right of the decimal"
CHECK(ST_Y(xyloc) * 10 = TRUNC(ST_Y(xyloc) * 10))
CONSTRAINT "XYLoc within study area (see Babase Techinical Specifications)"
CHECK(ST_Intersects(xyloc
, ST_PolygonFromText('POLYGON((42300.0 9497000.0, 651000.0 9497000.0, 651000.0 9894500.0, 42300.0 9894500.0, 42300.0 9497000.0))'
, 32737))),
altitude NUMERIC(5,1) NULL
CONSTRAINT "Altitude must be between (inclusive) 0 and 10000"
CHECK(altitude IS NULL
OR (0 <= altitude
AND altitude <= 10000)),
CONSTRAINT "Cannot have sub-meter altitude accuracy before 2004-01-01"
CHECK(altitude IS NULL
OR gps_datetime::DATE >= '2004-01-01'
OR altitude = TRUNC(altitude)),
pdop NUMERIC(3,1) NULL
CONSTRAINT "PDOP must be between 0 and 50, inclusive"
CHECK(pdop IS NULL
OR (0 <= pdop
AND pdop <= 50))
CONSTRAINT "PDOP values must be NULL before 1993-09-01 and after 2001-01-31"
CHECK(pdop IS NULL
OR ('1993-09-01' <= gps_datetime::DATE
AND gps_datetime::date <= '2001-01-31')),
accuracy NUMERIC(3,1) NULL
CONSTRAINT "Accuracy must be between 0 and 15, inclusive"
CHECK(accuracy IS NULL
OR (0 <= accuracy
AND accuracy <= 15))
CONSTRAINT "Accuracy must be NULL before 2001-02-01"
CHECK(accuracy IS NULL
OR gps_datetime::date >= '2001-02-01'),
gps_datetime TIMESTAMP NOT NULL
CONSTRAINT "SWERB_LOC_GPS rows may not exist before 2004-01-01"
CHECK(gps_datetime::date >= '2004-01-01'),
garmincode VARCHAR(10) NOT NULL
CONSTRAINT "Garmincode: Cannot be only whitespace characters"
CHECK(Garmincode IS NULL OR Garmincode = '' OR btrim(Garmincode, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON swerb_loc_gps TO GROUP babase_readers;
GRANT SELECT ON swerb_loc_gps TO GROUP babase_editors;
GRANT INSERT ON swerb_loc_gps TO GROUP babase_editors;
GRANT UPDATE ON swerb_loc_gps TO GROUP babase_editors;
GRANT DELETE ON swerb_loc_gps TO GROUP babase_editors;
SELECT 'swerb_loc_gps' AS done_with;
-- swerb_trees
CREATE TABLE trees (
tid SERIAL PRIMARY KEY,
loc VARCHAR(10) NOT NULL
CONSTRAINT "Loc on SWERB_GWS" REFERENCES swerb_gws
CONSTRAINT "Trees may not be placed in the unknown grove (bb_swerb_unknown_grove)"
CHECK(loc <> 'UNK'),
tree INT NOT NULL
) WITHOUT OIDS;
GRANT SELECT ON trees TO GROUP babase_readers;
GRANT SELECT ON trees TO GROUP babase_editors;
GRANT INSERT ON trees TO GROUP babase_editors;
GRANT UPDATE ON trees TO GROUP babase_editors;
GRANT DELETE ON trees TO GROUP babase_editors;
SELECT 'trees' AS done_with;
GRANT SELECT ON trees_tid_seq TO GROUP babase_readers;
GRANT SELECT ON trees_tid_seq TO GROUP babase_editors;
GRANT UPDATE ON trees_tid_seq TO GROUP babase_editors;
SELECT 'trees_tid_seq' AS done_with;
-- behave_gaps
CREATE TABLE behave_gaps (
bgid SERIAL PRIMARY KEY,
grp NUMERIC(6,4) NOT NULL
CONSTRAINT "Grp on GROUPS" REFERENCES groups,
gap_start DATE NOT NULL,
gap_end DATE,
gap_end_status SMALLINT
CONSTRAINT "Gap_End_Status on GAP_END_STATUSES"
REFERENCES gap_end_statuses
CONSTRAINT
"Gap_End and Gap_End_Status must both be NULL or both non-NULL"
CHECK((gap_end IS NULL AND gap_end_status IS NULL)
OR (gap_end IS NOT NULL AND gap_end_status IS NOT NULL)),
notes TEXT NOT NULL
CONSTRAINT "Notes: Cannot be empty or only whitespace characters"
CHECK(btrim(Notes, E' \r\n\t\f\x0B') <> ''),
CONSTRAINT "Gap_End must be after Gap_Start, or NULL"
CHECK(gap_end IS NULL OR gap_end > gap_start)
) WITHOUT OIDS;
GRANT SELECT ON behave_gaps TO GROUP babase_readers;
GRANT SELECT ON behave_gaps TO GROUP babase_editors;
GRANT INSERT ON behave_gaps TO GROUP babase_editors;
GRANT UPDATE ON behave_gaps TO GROUP babase_editors;
GRANT DELETE ON behave_gaps TO GROUP babase_editors;
SELECT 'behave_gaps' AS done_with;
GRANT SELECT ON behave_gaps_bgid_seq TO GROUP babase_readers;
GRANT SELECT ON behave_gaps_bgid_seq TO GROUP babase_editors;
GRANT UPDATE ON behave_gaps_bgid_seq TO GROUP babase_editors;
SELECT 'behave_gaps_bgid_seq' AS done_with;
-- hybridgene_analyses
CREATE TABLE hybridgene_analyses (
hgaid SERIAL PRIMARY KEY NOT NULL,
date DATE NOT NULL,
analyzed_by VARCHAR(4) NOT NULL
CONSTRAINT "Analyzed_By on OBSERVERS"
REFERENCES observers,
software SMALLINT NOT NULL
CONSTRAINT "Software on HYBRIDGENE_SOFTWARE"
REFERENCES hybridgene_software,
marker SMALLINT NOT NULL
CONSTRAINT "Marker on MARKERS" REFERENCES markers,
comments TEXT
CONSTRAINT "comments: Cannot be empty or only whitespace characters"
CHECK(btrim(comments, E' \r\n\t\f\x0B') <> '')
) WITHOUT OIDS;
GRANT SELECT ON hybridgene_analyses TO GROUP babase_readers;
GRANT SELECT ON hybridgene_analyses TO GROUP babase_editors;
GRANT INSERT ON hybridgene_analyses TO GROUP babase_editors;
GRANT UPDATE ON hybridgene_analyses TO GROUP babase_editors;
GRANT DELETE ON hybridgene_analyses TO GROUP babase_editors;
SELECT 'hybridgene_analyses' AS done_with;
GRANT SELECT ON hybridgene_analyses_hgaid_seq TO GROUP babase_readers;
GRANT SELECT ON hybridgene_analyses_hgaid_seq TO GROUP babase_editors;
GRANT UPDATE ON hybridgene_analyses_hgaid_seq TO GROUP babase_editors;
SELECT 'hybridgene_analyses_hgaid_seq' AS done_with;
-- hybridgene_scores
CREATE TABLE hybridgene_scores (
hgsid SERIAL PRIMARY KEY NOT NULL,
hgaid INT NOT NULL
CONSTRAINT "HGAId on HYBRIDGENE_ANALYSES"
REFERENCES hybridgene_analyses,
sname CHAR(3) NOT NULL
CONSTRAINT "Sname on BIOGRAPH" REFERENCES biograph(sname),
score NUMERIC(8,7) NOT NULL
CONSTRAINT "Score between Lower_Conf and Upper_Conf"
CHECK(score BETWEEN lower_conf AND upper_conf),
lower_conf NUMERIC(8,7) NOT NULL
CONSTRAINT "Lower_Conf must be between 0 and 1"
CHECK(lower_conf BETWEEN 0 AND 1),
upper_conf NUMERIC(8,7) NOT NULL
CONSTRAINT "Upper_Conf must be between 0 and 1"
CHECK(upper_conf BETWEEN 0 AND 1)
) WITHOUT OIDS;
GRANT SELECT ON hybridgene_scores TO GROUP babase_readers;
GRANT SELECT ON hybridgene_scores TO GROUP babase_editors;
GRANT INSERT ON hybridgene_scores TO GROUP babase_editors;
GRANT UPDATE ON hybridgene_scores TO GROUP babase_editors;
GRANT DELETE ON hybridgene_scores TO GROUP babase_editors;
SELECT 'hybridgene_scores' AS done_with;
GRANT SELECT ON hybridgene_scores_hgsid_seq TO GROUP babase_readers;
GRANT SELECT ON hybridgene_scores_hgsid_seq TO GROUP babase_editors;
GRANT UPDATE ON hybridgene_scores_hgsid_seq TO GROUP babase_editors;
SELECT 'hybridgene_scores_hgsid_seq' AS done_with;
-- We keep the warning system in a separate file.
--
-- Support Tables
--
-- The key, descr support tables.
-- support_table(foo, fookey, SMALLINT, )
-- support_table(bar, barkey, CHAR(1)
-- , empty_`'string_check(`barkey'))
CREATE TABLE IQTYPES (
IQType VARCHAR(8)
PRIMARY KEY NOT NULL CONSTRAINT "IQType: Cannot be empty or only whitespace characters"
CHECK(btrim(IQType, E' \r\n\t\f\x0B') <> '')
CONSTRAINT "IQType: Cannot contain any whitespace characters"
CHECK(IQType IS NULL OR
IQType !~ '( |\r|\n|\t|\f|\x0B)'),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON IQTYPES TO GROUP babase_readers;
GRANT SELECT ON IQTYPES TO GROUP babase_editors;
GRANT INSERT ON IQTYPES TO GROUP babase_editors;
GRANT UPDATE ON IQTYPES TO GROUP babase_editors;
GRANT DELETE ON IQTYPES TO GROUP babase_editors;
SELECT 'IQTYPES' AS done_with;
CREATE TABLE WARNING_REMARKS (
WRID VARCHAR(15)
PRIMARY KEY NOT NULL CONSTRAINT "WRID: Cannot be empty or only whitespace characters"
CHECK(btrim(WRID, E' \r\n\t\f\x0B') <> '')
CONSTRAINT "WRID: Cannot contain any whitespace characters"
CHECK(WRID IS NULL OR
WRID !~ '( |\r|\n|\t|\f|\x0B)'),
descr TEXT NOT NULL UNIQUE
CONSTRAINT "descr: Cannot be empty or only whitespace characters"
CHECK(btrim(descr, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON WARNING_REMARKS TO GROUP babase_readers;
GRANT SELECT ON WARNING_REMARKS TO GROUP babase_editors;
GRANT INSERT ON WARNING_REMARKS TO GROUP babase_editors;
GRANT UPDATE ON WARNING_REMARKS TO GROUP babase_editors;
GRANT DELETE ON WARNING_REMARKS TO GROUP babase_editors;
SELECT 'WARNING_REMARKS' AS done_with;
--
-- The primary tables
--
-- integrity_queries
CREATE TABLE integrity_queries (
iqname VARCHAR(15) PRIMARY KEY
CONSTRAINT "IQName: Cannot be empty or only whitespace characters"
CHECK(btrim(IQName, E' \r\n\t\f\x0B') <> '')
CONSTRAINT "IQName: Cannot contain any whitespace characters"
CHECK(IQName IS NULL OR
IQName !~ '( |\r|\n|\t|\f|\x0B)')
, error BOOLEAN NOT NULL
, type VARCHAR(8) NOT NULL
CONSTRAINT "Type on IQTypes" REFERENCES iqtypes
, first_run TIMESTAMP(0) WITHOUT TIME ZONE
, last_run TIMESTAMP(0) WITHOUT TIME ZONE
CONSTRAINT "Last_Run must be >= First_Run"
CHECK(last_run IS NULL
OR first_run IS NULL
OR first_run <= last_run)
, query TEXT NOT NULL
CONSTRAINT "Query: Cannot be empty or only whitespace characters"
CHECK(btrim(Query, E' \r\n\t\f\x0B') <> '')
, comment TEXT
CONSTRAINT "Comment: Cannot be empty or only whitespace characters"
CHECK(btrim(Comment, E' \r\n\t\f\x0B') <> '')
);
GRANT SELECT ON INTEGRITY_QUERIES TO GROUP babase_readers;
GRANT SELECT ON INTEGRITY_QUERIES TO GROUP babase_editors;
GRANT INSERT ON INTEGRITY_QUERIES TO GROUP babase_editors;
GRANT UPDATE ON INTEGRITY_QUERIES TO GROUP babase_editors;
GRANT DELETE ON INTEGRITY_QUERIES TO GROUP babase_editors;
SELECT 'INTEGRITY_QUERIES' AS done_with;
-- integrity_warnings
CREATE TABLE integrity_warnings (
iwid SERIAL PRIMARY KEY
, iqname VARCHAR(15) NOT NULL
CONSTRAINT "Iqname on INTEGRITY_QUERIES" REFERENCES integrity_queries
, first_seen TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL
, last_seen TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL
CONSTRAINT "Last_Seen must be >= First_Seen"
CHECK(first_seen <= last_seen)
, resolved TIMESTAMP(0) WITHOUT TIME ZONE
CONSTRAINT "Resolved must be >= First_Seen"
CHECK(resolved IS NULL OR first_seen <= resolved)
, deferred_to TIMESTAMP(0) WITHOUT TIME ZONE
CONSTRAINT "Either Resolved or Deferred_To or both must be NULL"
CHECK(NOT(resolved IS NOT NULL AND deferred_to IS NOT NULL))
CONSTRAINT "Deferred_To must be >= First_Seen"
CHECK(deferred_to IS NULL OR first_seen <= deferred_to)
, category VARCHAR(15)
CONSTRAINT "Category on WARNING_REMARKS"
REFERENCES warning_remarks
, warning_id TEXT NOT NULL
CONSTRAINT "Warning_ID: Cannot be empty or only whitespace characters"
CHECK(btrim(Warning_ID, E' \r\n\t\f\x0B') <> '')
, warning_message TEXT NOT NULL
CONSTRAINT "Warning_Message: Cannot be empty or only whitespace characters"
CHECK(btrim(Warning_Message, E' \r\n\t\f\x0B') <> '')
, notes TEXT
CONSTRAINT "Notes: Cannot be empty or only whitespace characters"
CHECK(btrim(Notes, E' \r\n\t\f\x0B') <> '')
);
-- Let editors update everything but warning_id and warning_message.
GRANT SELECT ON INTEGRITY_WARNINGS TO GROUP babase_readers;
GRANT SELECT ON INTEGRITY_WARNINGS TO GROUP babase_editors;
GRANT INSERT ON INTEGRITY_WARNINGS TO GROUP babase_editors;
GRANT UPDATE ON INTEGRITY_WARNINGS TO GROUP babase_editors;
GRANT DELETE ON INTEGRITY_WARNINGS TO GROUP babase_editors;
SELECT 'INTEGRITY_WARNINGS' AS done_with;
GRANT SELECT ON INTEGRITY_WARNINGS_IWID_seq TO GROUP babase_readers;
GRANT SELECT ON INTEGRITY_WARNINGS_IWID_seq TO GROUP babase_editors;
GRANT UPDATE ON INTEGRITY_WARNINGS_IWID_seq TO GROUP babase_editors;
SELECT 'INTEGRITY_WARNINGS_IWID_seq' AS done_with;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment