/ - Diff - BIEN 3 - NCEAS Projects

« Previous | Next »

Revision 5499

Added by Aaron Marcuse-Kubitza over 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 int(1),
         addressstartdate timestamp NULL
         addressstartdate date
     );
-...
         creator_id int(11) NOT NULL,
         sourceaccessioncode text,
         taxonoccurrence_id int(11) NOT NULL,
         collectiondate timestamp NULL,
         collectiondate date,
         cover_fraction double precision,
         linecover_m double precision,
         basalarea_m2 double precision,
-...
         notespublic int(1),
         notesmgt int(1),
         revisions int(1),
         dateentered timestamp NULL ,
         dateentered date ,
         locationrationalenarrative text,
         accessioncode text
     );
-...
         footprintgeometry_dwc text,
         coordsaccuracy_deg double precision,
         identifier_id int(11),
         determinationdate timestamp NULL,
         determinationdate date,
         isoriginal int(1) DEFAULT false NOT NULL,
         iscurrent int(1) DEFAULT false NOT NULL,
         calculated int(1)
-...
         notespublic int(1),
         notesmgt int(1),
         revisions int(1),
         obsstartdate timestamp NULL,
         obsenddate timestamp NULL,
         dateentered timestamp NULL ,
         obsstartdate date,
         obsenddate date,
         dateentered date ,
         toptaxon1name text,
         toptaxon2name text,
         toptaxon3name text,
-...
         taxonconcept_id int(11) NOT NULL,
         creator_id int(11) NOT NULL,
         sourceaccessioncode text,
         creationdate timestamp NULL,
         creationdate date,
         accepted_concept_id int(11),
         matched_concept_id int(11),
         matched_concept_fit_fraction double precision,
-...
         notespublic int(1),
         notesmgt int(1),
         revisions int(1),
         determinationdate timestamp NULL,
         determinationdate date,
         accessioncode text
     );
-...
         commcode text,
         commframework text,
         commlevel text,
         classstartdate timestamp NULL,
         classstopdate timestamp NULL,
         classstartdate date,
         classstopdate date,
         expertsystem text,
         accessioncode text
     );
-...
         commstatus_id int(11) NOT NULL,
         commconcept_id int(11) NOT NULL,
         commconvergence text NOT NULL,
         correlationstart timestamp NOT NULL,
         correlationstop timestamp NULL
         correlationstart date NOT NULL,
         correlationstop date
     );
-...
         commname_id int(11) NOT NULL,
         commname text NOT NULL,
         reference_id int(11),
         dateentered timestamp NULL
         dateentered date
     );
-...
         commlevel text,
         commpartycomments text,
         party_id int(11) NOT NULL,
         startdate timestamp NOT NULL,
         stopdate timestamp NULL,
         startdate date NOT NULL,
         stopdate date,
         accessioncode text
     );
-...
         classsystem text,
         party_id int(11),
         commstatus_id int(11),
         usagestart timestamp NULL,
         usagestop timestamp NULL
         usagestart date,
         usagestop date
     );
-...
         graphicdescription text,
         graphictype text,
         graphicdata int(11),
         graphicdate timestamp NULL,
         graphicdate date,
         accessioncode text
     );
-...
         locationevent_id int(11) NOT NULL,
         party_id int(11) NOT NULL,
         role text NOT NULL,
         contributiondate timestamp NULL
         contributiondate date
     );
-...
         party_id int(11) NOT NULL,
         role text NOT NULL,
         synonymcomment text,
         classstartdate timestamp NULL  NOT NULL,
         classstopdate timestamp NULL,
         classstartdate date  NOT NULL,
         classstopdate date,
         accessioncode text
     );
-...
         role text NOT NULL,
         notetype text NOT NULL,
         notetext text NOT NULL,
         notedate timestamp NULL,
         notedate date,
         accessioncode text
     );
-...
         parentparty_id int(11) NOT NULL,
         childparty_id int(11) NOT NULL,
         role text,
         memberstart timestamp NULL  NOT NULL,
         memberstop timestamp NULL
         memberstart date  NOT NULL,
         memberstop date
     );
-...
         sourceaccessioncode text,
         projectname text,
         projectdescription text,
         startdate timestamp NULL,
         stopdate timestamp NULL,
         startdate date,
         stopdate date,
         d_obscount int(11),
         d_lastlocationaddeddate timestamp NULL,
         d_lastlocationaddeddate date,
         accessioncode text
     );
-...
         url text,
         doi text,
         additionalinfo text,
         pubdate timestamp NULL,
         accessdate timestamp NULL,
         conferencedate timestamp NULL,
         pubdate date,
         accessdate date,
         conferencedate date,
         accessioncode text
     );
-...
         previousvaluetext text NOT NULL,
         previousvaluetype text NOT NULL,
         previousrevision_id int(11),
         revisiondate timestamp NOT NULL
         revisiondate date NOT NULL
     );
-...
         taxonstatus_id int(11) NOT NULL,
         taxonconcept_id int(11) NOT NULL,
         plantconvergence text NOT NULL,
         correlationstart timestamp NOT NULL,
         correlationstop timestamp NULL
         correlationstart date NOT NULL,
         correlationstop date
     );
-...
         taxonconceptstatus text DEFAULT 'undetermined' NOT NULL,
         reference_id int(11),
         plantpartycomments text,
         startdate timestamp NULL,
         stopdate timestamp NULL,
         startdate date,
         stopdate date,
         accessioncode text
     );
-...
         acceptedsynonym text,
         party_id int(11),
         taxonstatus_id int(11),
         usagestart timestamp NULL,
         usagestop timestamp NULL
         usagestart date,
         usagestop date
     );

         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 over 12 years ago