/schemas/vegbien.sql - Diff - BIEN 3 - NCEAS Projects

« Previous | Next »

Revision 5499

Added by Aaron Marcuse-Kubitza about 12 years ago

schemas/vegbien.sql: Changed `timestamp with time zone` fields to `date` because time information is not stored in these fields, and it's confusing to have an arbitrary timezone (the server's timezone) and an arbitrary time (midnight) set for input data that only has a precision to the nearest day

         postalcode text,
         country text,
         currentflag boolean,
         addressstartdate timestamp with time zone
         addressstartdate date
     );
-...
         creator_id integer NOT NULL,
         sourceaccessioncode text,
         taxonoccurrence_id integer NOT NULL,
         collectiondate timestamp with time zone,
         collectiondate date,
         cover_fraction double precision,
         linecover_m double precision,
         basalarea_m2 double precision,
-...
         notespublic boolean,
         notesmgt boolean,
         revisions boolean,
         dateentered timestamp with time zone DEFAULT now(),
         dateentered date DEFAULT now(),
         locationrationalenarrative text,
         accessioncode text,
         CONSTRAINT location_required_key CHECK (((sourceaccessioncode IS NOT NULL) OR (authorlocationcode IS NOT NULL)))
-...
         footprintgeometry_dwc text,
         coordsaccuracy_deg double precision,
         identifier_id integer,
         determinationdate timestamp with time zone,
         determinationdate date,
         isoriginal boolean DEFAULT false NOT NULL,
         iscurrent boolean DEFAULT false NOT NULL,
         calculated boolean
-...
         notespublic boolean,
         notesmgt boolean,
         revisions boolean,
         obsstartdate timestamp with time zone,
         obsenddate timestamp with time zone,
         dateentered timestamp with time zone DEFAULT now(),
         obsstartdate date,
         obsenddate date,
         dateentered date DEFAULT now(),
         toptaxon1name text,
         toptaxon2name text,
         toptaxon3name text,
-...
         taxonconcept_id integer NOT NULL,
         creator_id integer NOT NULL,
         sourceaccessioncode text,
         creationdate timestamp with time zone,
         creationdate date,
         accepted_concept_id integer,
         matched_concept_id integer,
         matched_concept_fit_fraction double precision,
-...
         notespublic boolean,
         notesmgt boolean,
         revisions boolean,
         determinationdate timestamp with time zone,
         determinationdate date,
         accessioncode text
     );
-...
         commcode text,
         commframework text,
         commlevel text,
         classstartdate timestamp with time zone,
         classstopdate timestamp with time zone,
         classstartdate date,
         classstopdate date,
         expertsystem text,
         accessioncode text
     );
-...
         commstatus_id integer NOT NULL,
         commconcept_id integer NOT NULL,
         commconvergence text NOT NULL,
         correlationstart timestamp with time zone NOT NULL,
         correlationstop timestamp with time zone
         correlationstart date NOT NULL,
         correlationstop date
     );
-...
         commname_id integer NOT NULL,
         commname text NOT NULL,
         reference_id integer,
         dateentered timestamp with time zone DEFAULT now()
         dateentered date DEFAULT now()
     );
-...
         commlevel text,
         commpartycomments text,
         party_id integer NOT NULL,
         startdate timestamp with time zone NOT NULL,
         stopdate timestamp with time zone,
         startdate date NOT NULL,
         stopdate date,
         accessioncode text
     );
-...
         classsystem text,
         party_id integer,
         commstatus_id integer,
         usagestart timestamp with time zone,
         usagestop timestamp with time zone
         usagestart date,
         usagestop date
     );
-...
         graphicdescription text,
         graphictype text,
         graphicdata oid,
         graphicdate timestamp with time zone,
         graphicdate date,
         accessioncode text
     );
-...
         locationevent_id integer NOT NULL,
         party_id integer NOT NULL,
         role role NOT NULL,
         contributiondate timestamp with time zone
         contributiondate date
     );
-...
         party_id integer NOT NULL,
         role role NOT NULL,
         synonymcomment text,
         classstartdate timestamp with time zone DEFAULT now() NOT NULL,
         classstopdate timestamp with time zone,
         classstartdate date DEFAULT now() NOT NULL,
         classstopdate date,
         accessioncode text
     );
-...
         role role NOT NULL,
         notetype text NOT NULL,
         notetext text NOT NULL,
         notedate timestamp with time zone,
         notedate date,
         accessioncode text
     );
-...
         parentparty_id integer NOT NULL,
         childparty_id integer NOT NULL,
         role role,
         memberstart timestamp with time zone DEFAULT now() NOT NULL,
         memberstop timestamp with time zone
         memberstart date DEFAULT now() NOT NULL,
         memberstop date
     );
-...
         sourceaccessioncode text,
         projectname text,
         projectdescription text,
         startdate timestamp with time zone,
         stopdate timestamp with time zone,
         startdate date,
         stopdate date,
         d_obscount integer,
         d_lastlocationaddeddate timestamp with time zone,
         d_lastlocationaddeddate date,
         accessioncode text,
         CONSTRAINT project_required_key CHECK (((sourceaccessioncode IS NOT NULL) OR (projectname IS NOT NULL)))
     );
-...
         url text,
         doi text,
         additionalinfo text,
         pubdate timestamp with time zone,
         accessdate timestamp with time zone,
         conferencedate timestamp with time zone,
         pubdate date,
         accessdate date,
         conferencedate date,
         accessioncode text
     );
-...
         previousvaluetext text NOT NULL,
         previousvaluetype text NOT NULL,
         previousrevision_id integer,
         revisiondate timestamp with time zone NOT NULL
         revisiondate date NOT NULL
     );
-...
         taxonstatus_id integer NOT NULL,
         taxonconcept_id integer NOT NULL,
         plantconvergence text NOT NULL,
         correlationstart timestamp with time zone NOT NULL,
         correlationstop timestamp with time zone
         correlationstart date NOT NULL,
         correlationstop date
     );
-...
         taxonconceptstatus text DEFAULT 'undetermined'::text NOT NULL,
         reference_id integer,
         plantpartycomments text,
         startdate timestamp with time zone,
         stopdate timestamp with time zone,
         startdate date,
         stopdate date,
         accessioncode text
     );
-...
         acceptedsynonym text,
         party_id integer,
         taxonstatus_id integer,
         usagestart timestamp with time zone,
         usagestop timestamp with time zone
         usagestart date,
         usagestop date
     );
-...
     -- Name: locationevent_unique_within_location; Type: INDEX; Schema: public; Owner: -; Tablespace:
     --
     CREATE UNIQUE INDEX locationevent_unique_within_location ON locationevent USING btree (location_id, (COALESCE(authoreventcode, '\\N'::text)), (COALESCE(obsstartdate, 'infinity'::timestamp with time zone))) WHERE ((parent_id IS NULL) AND (sourceaccessioncode IS NULL));
     CREATE UNIQUE INDEX locationevent_unique_within_location ON locationevent USING btree (location_id, (COALESCE(authoreventcode, '\\N'::text)), (COALESCE(obsstartdate, 'infinity'::date))) WHERE ((parent_id IS NULL) AND (sourceaccessioncode IS NULL));
     --
-...
     -- Name: locationevent_unique_within_project; Type: INDEX; Schema: public; Owner: -; Tablespace:
     --
     CREATE UNIQUE INDEX locationevent_unique_within_project ON locationevent USING btree ((COALESCE(project_id, 2147483647)), (COALESCE(authoreventcode, '\\N'::text)), (COALESCE(obsstartdate, 'infinity'::timestamp with time zone))) WHERE ((((parent_id IS NULL) AND (project_id IS NOT NULL)) AND (authoreventcode IS NOT NULL)) AND (obsstartdate IS NOT NULL));
     CREATE UNIQUE INDEX locationevent_unique_within_project ON locationevent USING btree ((COALESCE(project_id, 2147483647)), (COALESCE(authoreventcode, '\\N'::text)), (COALESCE(obsstartdate, 'infinity'::date))) WHERE ((((parent_id IS NULL) AND (project_id IS NOT NULL)) AND (authoreventcode IS NOT NULL)) AND (obsstartdate IS NOT NULL));
     --
-...
     -- Name: project_unique_name_date; Type: INDEX; Schema: public; Owner: -; Tablespace:
     --
     CREATE UNIQUE INDEX project_unique_name_date ON project USING btree (creator_id, (COALESCE(projectname, '\\N'::text)), (COALESCE(startdate, 'infinity'::timestamp with time zone))) WHERE (sourceaccessioncode IS NULL);
     CREATE UNIQUE INDEX project_unique_name_date ON project USING btree (creator_id, (COALESCE(projectname, '\\N'::text)), (COALESCE(startdate, 'infinity'::date))) WHERE (sourceaccessioncode IS NULL);
     --
-...
     -- Name: taxonconcept_unique; Type: INDEX; Schema: public; Owner: -; Tablespace:
     --
     CREATE UNIQUE INDEX taxonconcept_unique ON taxonconcept USING btree ((COALESCE(parent_id, 2147483647)), (COALESCE(taxonname, '\\N'::text)), (COALESCE(rank, 'unknown'::taxonrank)), creator_id, (COALESCE(sourceaccessioncode, '\\N'::text)), (COALESCE(creationdate, 'infinity'::timestamp with time zone)), (COALESCE(identifyingtaxonomicname, '\\N'::text)), (COALESCE(taxonomicname, '\\N'::text)), (COALESCE(author, '\\N'::text)), (COALESCE(taxonomicnamewithauthor, '\\N'::text)));
     CREATE UNIQUE INDEX taxonconcept_unique ON taxonconcept USING btree ((COALESCE(parent_id, 2147483647)), (COALESCE(taxonname, '\\N'::text)), (COALESCE(rank, 'unknown'::taxonrank)), creator_id, (COALESCE(sourceaccessioncode, '\\N'::text)), (COALESCE(creationdate, 'infinity'::date)), (COALESCE(identifyingtaxonomicname, '\\N'::text)), (COALESCE(taxonomicname, '\\N'::text)), (COALESCE(author, '\\N'::text)), (COALESCE(taxonomicnamewithauthor, '\\N'::text)));
     --

Also available in: Unified diff

Project

General

Profile

Revision 5499

Added by Aaron Marcuse-Kubitza about 12 years ago