-- Existing tables

CREATE TABLE aux_role

(

  role_id serial NOT NULL,

  rolecode character varying(30) NOT NULL,

  roledescription character varying(200),

  CONSTRAINT aux_role_pkey PRIMARY KEY (role_id )

);

CREATE TABLE reference

(

  reference_id serial NOT NULL,

  shortname character varying(250),

  fulltext text,

  referencetype character varying(250)

);

CREATE TABLE party

(

  party_id serial NOT NULL,

  salutation character varying(20),

  givenname character varying(50),

  middlename character varying(50),

  surname character varying(50),

  organizationname character varying(100)

);

CREATE TABLE plantname

(

  plantname_id serial NOT NULL,

  plantname character varying(255) NOT NULL,

  reference_id integer,

  dateentered timestamp with time zone DEFAULT now(),

  CONSTRAINT plantname_pkey PRIMARY KEY (plantname_id ),

  CONSTRAINT plantname_reference_id FOREIGN KEY (reference_id)

      REFERENCES reference (reference_id) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE

);

CREATE TABLE plantconcept

(

  plantconcept_id serial NOT NULL,

  plantname_id integer NOT NULL,

  reference_id integer NOT NULL,

  plantname character varying(200),

  plantcode character varying(23),

  plantdescription text,

  d_obscount integer,

  d_currentaccepted boolean,

  accessioncode character varying(255),

  CONSTRAINT plantconcept_pkey PRIMARY KEY (plantconcept_id ),

  CONSTRAINT plantconcept_plantname_id FOREIGN KEY (plantname_id)

      REFERENCES plantname (plantname_id) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE,

  CONSTRAINT plantconcept_reference_id FOREIGN KEY (reference_id)

      REFERENCES reference (reference_id) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE

);

CREATE TABLE namedplace

(

  namedplace_id serial NOT NULL,

  placesystem character varying(50),

  placename character varying(100) NOT NULL,

  placedescription text,

  placecode character varying(15),

  owner character varying(100),

  reference_id integer,

  d_obscount integer,

  accessioncode character varying(255),

  CONSTRAINT namedplace_pkey PRIMARY KEY (namedplace_id ),

  CONSTRAINT namedplace_reference_id FOREIGN KEY (reference_id)

      REFERENCES reference (reference_id) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE,

  CONSTRAINT namedplace_keys UNIQUE (placesystem , placename )

);

CREATE TABLE project

(

  project_id serial NOT NULL,

  projectname character varying(150) NOT NULL,

  projectdescription text,

  startdate timestamp with time zone,

  stopdate timestamp with time zone,

  d_obscount integer,

  d_lastlocationaddeddate timestamp with time zone,

  accessioncode character varying(255),

  CONSTRAINT project_pkey PRIMARY KEY (project_id ),

  CONSTRAINT project_keys UNIQUE (projectname , startdate , stopdate )

);

-- New tables

CREATE TABLE specimen -- A herbarium specimen. Contains Darwin Core specimen data.

(

  specimen_id serial NOT NULL,

  individualplant_id integer,

  reference_id integer NOT NULL,

  collectioncode_dwc character varying(255),

  catalognumber_dwc character varying(255),

  collectiondate timestamp with time zone,

  collector_id integer,

  museum_id integer,

  sourceaccessionnumber character varying(100),

  accessioncode character varying(255),

  taxonoccurrence_id integer,

  CONSTRAINT specimen_pkey PRIMARY KEY (specimen_id ),

  CONSTRAINT specimen_collector_id FOREIGN KEY (collector_id)

      REFERENCES party (party_id) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE,

  CONSTRAINT specimen_individualplant_id_fkey FOREIGN KEY (individualplant_id)

      REFERENCES individualplant (individualplant_id) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE,

  CONSTRAINT specimen_museum_id FOREIGN KEY (museum_id)

      REFERENCES party (party_id) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE,

  CONSTRAINT specimen_reference_id_fkey FOREIGN KEY (reference_id)

      REFERENCES reference (reference_id) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE,

  CONSTRAINT specimen_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)

      REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE,

  CONSTRAINT specimen_keys UNIQUE (reference_id , collectioncode_dwc , catalognumber_dwc )

);

CREATE TABLE sizeclass -- A range of size measurements used to aggregate organisms.

  sizeclass_id serial NOT NULL,

  mindiameter double precision,

  diameteraccuracy double precision,

  minheight double precision,

  heightaccuracy double precision,

  maxdiameter double precision,

  maxheight double precision,

  CONSTRAINT sizeclass_pkey PRIMARY KEY (sizeclass_id )