--

-- PostgreSQL database dump

--

--

-- Name: establishmentmeans_dwc; Type: TYPE; Schema: public; Owner: -

--

--

-- Name: TYPE establishmentmeans_dwc; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: growthform; Type: TYPE; Schema: public; Owner: -

--

--

-- Name: occurrencestatus_dwc; Type: TYPE; Schema: public; Owner: -

--

--

-- Name: placerank; Type: TYPE; Schema: public; Owner: -

--

--

-- Name: TYPE placerank; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: role; Type: TYPE; Schema: public; Owner: -

--

--

-- Name: taxonclass; Type: TYPE; Schema: public; Owner: -

--

--

-- Name: TYPE taxonclass; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: taxonrank; Type: TYPE; Schema: public; Owner: -

--

--

-- Name: TYPE taxonrank; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: make_analytical_db(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: namedplace_update_ancestors(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: plantname_update_ancestors(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: plantobservation_aggregateoccurrence_count_1(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: concat(text); Type: AGGREGATE; Schema: public; Owner: -

--

--

-- Name: address; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE address (

    address_id int(11) NOT NULL,

    party_id int(11) NOT NULL,

    organization_id int(11),

    orgposition text,

    email text,

    deliverypoint text,

    city text,

    administrativearea text,

    postalcode text,

    country text,

    currentflag int(1),

    addressstartdate timestamp NULL

);

--

-- Name: address_address_id_seq; Type: SEQUENCE; Schema: public; Owner: -

--

--

-- Name: address_address_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

--

--

-- Name: aggregateoccurrence; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE aggregateoccurrence (

    aggregateoccurrence_id int(11) NOT NULL,

    datasource_id int(11) NOT NULL,

    sourceaccessioncode text,

    taxonoccurrence_id int(11) NOT NULL,

    collectiondate timestamp NULL,

    cover double precision,

    linecover double precision,

    basalarea double precision,

    biomass double precision,

    inferencearea double precision,

    count int(11),

    stratum_id int(11),

    coverindex_id int(11),

    occurrencestatus_dwc text DEFAULT 'present' NOT NULL,

    method_id int(11),

    notes text,

    accessioncode text

);

--

-- Name: TABLE aggregateoccurrence; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN aggregateoccurrence.linecover; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN aggregateoccurrence.occurrencestatus_dwc; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: aggregateoccurrence_aggregateoccurrence_id_seq; Type: SEQUENCE; Schema: public; Owner: -

--

--

-- Name: aggregateoccurrence_aggregateoccurrence_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

--

--

-- Name: location; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE location (

    location_id int(11) NOT NULL,

    datasource_id int(11) NOT NULL,

    sourceaccessioncode text,

    parent_id int(11),

    authorlocationcode text,

    centerlatitude double precision,

    centerlongitude double precision,

    locationaccuracy double precision,

    confidentialitystatus int(11) DEFAULT 0 NOT NULL,

    confidentialityreason text,

    publiclatitude double precision,

    publiclongitude double precision,

    sublocationxposition double precision,

    sublocationyposition double precision,

    authore text,

    authorn text,

    authorzone text,

    authordatum text,

    authorlocation text,

    locationnarrative text,

    azimuth double precision,

    shape text,

    area double precision,

    standsize text,

    placementmethod text,

    permanence int(1),

    layoutnarrative text,

    elevation double precision,

    elevationaccuracy double precision,

    elevationrange double precision,

    slopeaspect double precision,

    minslopeaspect double precision,

    maxslopeaspect double precision,

    slopegradient double precision,

    minslopegradient double precision,

    maxslopegradient double precision,

    topoposition text,

    landform text,

    surficialdeposits text,

    rocktype text,

    stateprovince text,

    country text,

    submitter_surname text,

    submitter_givenname text,

    submitter_email text,

    notespublic int(1),

    notesmgt int(1),

    revisions int(1),

    dateentered timestamp NULL ,

    locationrationalenarrative text,

    accessioncode text

);

--

-- Name: TABLE location; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: locationdetermination; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE locationdetermination (

    locationdetermination_id int(11) NOT NULL,

    location_id int(11) NOT NULL,

    latitude double precision,

    longitude double precision,

    verbatimlatitude text,

    verbatimlongitude text,

    verbatimcoordinates text,

    footprintgeometry_dwc text,

    coordsaccuracy double precision,

    namedplace_id int(11),

    identifier_id int(11),

    determinationdate timestamp NULL,

    isoriginal int(1) DEFAULT false NOT NULL,

    iscurrent int(1) DEFAULT false NOT NULL,

    calculated int(1)

);

--

-- Name: TABLE locationdetermination; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN locationdetermination.footprintgeometry_dwc; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN locationdetermination.coordsaccuracy; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: locationevent; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE locationevent (

    locationevent_id int(11) NOT NULL,

    datasource_id int(11) NOT NULL,

    sourceaccessioncode text,

    parent_id int(11),

    location_id int(11),

    project_id int(11),

    authoreventcode text,

    previous_id int(11),

    dateaccuracy text,

    method_id int(11),

    temperature double precision,

    precipitation double precision,

    autotaxoncover int(1),

    originaldata text,

    effortlevel text,

    floristicquality text,

    bryophytequality text,

    lichenquality text,

    locationeventnarrative text,

    landscapenarrative text,

    homogeneity text,

    phenologicaspect text,

    representativeness text,

    standmaturity text,

    successionalstatus text,

    basalarea double precision,

    hydrologicregime text,

    soilmoistureregime text,

    soildrainage text,

    watersalinity text,

    waterdepth double precision,

    shoredistance double precision,

    soildepth double precision,

    organicdepth double precision,

    soiltaxon_id int(11),

    soiltaxonsrc text,

    percentbedrock double precision,

    percentrockgravel double precision,

    percentwood double precision,

    percentlitter double precision,

    percentbaresoil double precision,

    percentwater double precision,

    percentother double precision,

    nameother text,

    treeht double precision,

    shrubht double precision,

    fieldht double precision,

    nonvascularht double precision,

    submergedht double precision,

    treecover double precision,

    shrubcover double precision,

    fieldcover double precision,

    nonvascularcover double precision,

    floatingcover double precision,

    submergedcover double precision,

    dominantstratum text,

    growthform1type text,

    growthform2type text,

    growthform3type text,

    growthform1cover double precision,

    growthform2cover double precision,

    growthform3cover double precision,

    totalcover double precision,

    notespublic int(1),

    notesmgt int(1),

    revisions int(1),

    obsstartdate timestamp NULL,

    obsenddate timestamp NULL,

    dateentered timestamp NULL ,

    toptaxon1name text,

    toptaxon2name text,

    toptaxon3name text,

    toptaxon4name text,

    toptaxon5name text,

    numberoftaxa int(11),

    accessioncode text

);

--

-- Name: TABLE locationevent; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: method; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE method (

    method_id int(11) NOT NULL,

    reference_id int(11),

    name text NOT NULL,

    description text,

    diameterheight double precision,

    mindiameter double precision,

    maxdiameter double precision,

    minheight double precision,

    maxheight double precision,

    observationtype text,

    observationmeasure text,

    covermethod_id int(11),

    samplingfactor double precision DEFAULT 1 NOT NULL,

    coverbasis text,

    stemsamplemethod text,

    shape text,

    length double precision,

    width double precision,

    radius double precision,

    area double precision,

    samplearea double precision,

    subplotspacing double precision,

    subplotmethod_id int(11),

    pointsperline int(11),

    accessioncode text

);

--

-- Name: TABLE method; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.reference_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.name; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.description; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.diameterheight; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.mindiameter; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.maxdiameter; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.minheight; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.maxheight; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.observationtype; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.observationmeasure; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.samplingfactor; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.coverbasis; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.stemsamplemethod; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.shape; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.length; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.width; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.radius; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.area; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.samplearea; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.subplotspacing; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.subplotmethod_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN method.pointsperline; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: namedplace; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE namedplace (

    namedplace_id int(11) NOT NULL,

    parent_id int(11),

    rank text NOT NULL,

    placename text NOT NULL,

    placecode text,

    placedescription text,

    accessioncode text

);

--

-- Name: namedplace_ancestor; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE namedplace_ancestor (

    namedplace_id int(11) NOT NULL,

    ancestor_id int(11) NOT NULL

);

--

-- Name: party; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE party (

    party_id int(11) NOT NULL,

    salutation text,

    givenname text,

    middlename text,

    surname text,

    organizationname text,

    currentname_id int(11),

    contactinstructions text,

    email text,

    partytype text,

    partypublic int(1) DEFAULT true,

    d_obscount int(11),

    accessioncode text

);

--

-- Name: plantconcept; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE plantconcept (

    plantconcept_id int(11) NOT NULL,

    plantname_id int(11) NOT NULL,

    scientificname text,

    scientificnameauthor text,

    scientificnamewithauthor text,

    domain text,

    kingdom text,

    phylum text,

    class text,

    `order` text,

    family text,

    genus text,

    species text,

    subspecies text,

    variety text,

    forma text,

    cultivar text,

    plantcode text,

    accessioncode text

);

--

-- Name: plantname; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE plantname (

    plantname_id int(11) NOT NULL,

    parent_id int(11),

    scope_id int(11),

    rank text NOT NULL,

    plantname text NOT NULL,

    description text,

    accessioncode text

);

--

-- Name: plantname_ancestor; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE plantname_ancestor (

    plantname_id int(11) NOT NULL,

    ancestor_id int(11) NOT NULL

);

--

-- Name: plantobservation; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE plantobservation (

    plantobservation_id int(11) NOT NULL,

    datasource_id int(11) NOT NULL,

    sourceaccessioncode text,

    aggregateoccurrence_id int(11) NOT NULL,

    overallheight double precision,

    overallheightaccuracy double precision,

    collectionnumber text,

    stemcount int(11),

    plant_id int(11),

    accessioncode text

);

--

-- Name: TABLE plantobservation; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN plantobservation.collectionnumber; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: specimenreplicate; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE specimenreplicate (

    specimenreplicate_id int(11) NOT NULL,

    datasource_id int(11) NOT NULL,

    sourceaccessioncode text,

    plantobservation_id int(11),

    institution_id int(11),

    collectioncode_dwc text,

    catalognumber_dwc text,

    description text,

    specimen_id int(11),

    accessioncode text

);

--

-- Name: TABLE specimenreplicate; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN specimenreplicate.institution_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN specimenreplicate.collectioncode_dwc; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: taxondetermination; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE taxondetermination (

    taxondetermination_id int(11) NOT NULL,

    taxonoccurrence_id int(11) NOT NULL,

    plantconcept_id int(11) NOT NULL,

    party_id int(11),

    role text DEFAULT 'unknown' NOT NULL,

    determinationtype text,

    reference_id int(11),

    isoriginal int(1) DEFAULT false NOT NULL,

    iscurrent int(1) DEFAULT false NOT NULL,

    taxonfit text,

    taxonconfidence text,

    grouptype text,

    notes text,

    notespublic int(1),

    notesmgt int(1),

    revisions int(1),

    determinationdate timestamp NULL,

    accessioncode text

);

--

-- Name: TABLE taxondetermination; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: taxonoccurrence; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE taxonoccurrence (

    taxonoccurrence_id int(11) NOT NULL,

    datasource_id int(11) NOT NULL,

    sourceaccessioncode text,

    locationevent_id int(11),

    authortaxoncode text,

    verbatimcollectorname text,

    growthform text,

    iscultivated int(1),

    cultivatedbasis text,

    isnative int(1),

    accessioncode text

);

--

-- Name: TABLE taxonoccurrence; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonoccurrence.iscultivated; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonoccurrence.cultivatedbasis; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonoccurrence.isnative; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: analytical_db_view; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW analytical_db_view AS

    SELECT datasource.organizationname AS `dataSourceName`, family.plantname AS family, genus.plantname AS genus, species.plantname AS species, binomial.plantname AS taxon, authority.plantname AS `taxonAuthor`, variety.plantname AS `taxonMorphospecies`, country.placename AS country, stateprovince.placename AS `stateProvince`, county.placename AS `countyParish`, taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS `collectionNumber`, array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], CAST(' ' AS text)) AS `identifiedBy`, aggregateoccurrence.collectiondate AS `observationDate`, location.authorlocationcode AS `plotCode`, location.area AS `plotAreaHa`, method.name AS `plotMethod`, locationdetermination.latitude, locationdetermination.longitude, location.elevation AS elevation_m, taxonoccurrence.iscultivated AS `isCultivated`, taxonoccurrence.cultivatedbasis AS `isCultivatedReason`, aggregateoccurrence.cover AS `pctCover` FROM (((((((((((((((((((((((((((((((((((((((((location JOIN party datasource ON (((datasource.party_id = location.datasource_id) AND (datasource.organizationname IS NOT NULL)))) JOIN locationdetermination USING (location_id)) LEFT JOIN namedplace_ancestor continent_ancestor ON ((continent_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace continent ON (((continent.namedplace_id = continent_ancestor.ancestor_id) AND (continent.rank = CAST('continent' AS placerank))))) LEFT JOIN namedplace_ancestor country_ancestor ON ((country_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace country ON (((country.namedplace_id = country_ancestor.ancestor_id) AND (country.rank = CAST('country' AS placerank))))) LEFT JOIN namedplace_ancestor stateprovince_ancestor ON ((stateprovince_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace stateprovince ON (((stateprovince.namedplace_id = stateprovince_ancestor.ancestor_id) AND (stateprovince.rank = CAST('stateProvince' AS placerank))))) LEFT JOIN namedplace_ancestor county_ancestor ON ((county_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace county ON (((county.namedplace_id = county_ancestor.ancestor_id) AND (county.rank = CAST('county' AS placerank))))) JOIN locationevent USING (location_id)) JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN plantconcept USING (plantconcept_id)) LEFT JOIN plantname_ancestor binomial_ancestor ON ((binomial_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname binomial ON (((binomial.plantname_id = binomial_ancestor.ancestor_id) AND (binomial.rank = CAST('binomial' AS taxonrank))))) LEFT JOIN plantname_ancestor kingdom_ancestor ON ((kingdom_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname kingdom ON (((kingdom.plantname_id = kingdom_ancestor.ancestor_id) AND (kingdom.rank = CAST('kingdom' AS taxonrank))))) LEFT JOIN plantname_ancestor division_ancestor ON ((division_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname division ON (((division.plantname_id = division_ancestor.ancestor_id) AND (division.rank = CAST('division' AS taxonrank))))) LEFT JOIN plantname_ancestor class_ancestor ON ((class_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname class ON (((class.plantname_id = class_ancestor.ancestor_id) AND (class.rank = CAST('class' AS taxonrank))))) LEFT JOIN plantname_ancestor order_ancestor ON ((order_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname `order` ON (((`order`.plantname_id = order_ancestor.ancestor_id) AND (`order`.rank = CAST('order' AS taxonrank))))) LEFT JOIN plantname_ancestor family_ancestor ON ((family_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname family ON (((family.plantname_id = family_ancestor.ancestor_id) AND (family.rank = CAST('family' AS taxonrank))))) LEFT JOIN plantname_ancestor genus_ancestor ON ((genus_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname genus ON (((genus.plantname_id = genus_ancestor.ancestor_id) AND (genus.rank = CAST('genus' AS taxonrank))))) LEFT JOIN plantname_ancestor species_ancestor ON ((species_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname species ON (((species.plantname_id = species_ancestor.ancestor_id) AND (species.rank = CAST('species' AS taxonrank))))) LEFT JOIN plantname_ancestor subspecies_ancestor ON ((subspecies_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname subspecies ON (((subspecies.plantname_id = subspecies_ancestor.ancestor_id) AND (subspecies.rank = CAST('subspecies' AS taxonrank))))) LEFT JOIN plantname_ancestor authority_ancestor ON ((authority_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname authority ON (((authority.plantname_id = authority_ancestor.ancestor_id) AND (authority.rank = CAST('authority' AS taxonrank))))) LEFT JOIN plantname_ancestor variety_ancestor ON ((variety_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname variety ON (((variety.plantname_id = variety_ancestor.ancestor_id) AND (variety.rank = CAST('variety' AS taxonrank))))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));

--

-- Name: VIEW analytical_db_view; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: classcontributor; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE classcontributor (

    classcontributor_id int(11) NOT NULL,

    commclass_id int(11) NOT NULL,

    party_id int(11) NOT NULL,

    role text

);

--

-- Name: classcontributor_classcontributor_id_seq; Type: SEQUENCE; Schema: public; Owner: -

--

--

-- Name: classcontributor_classcontributor_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

--

--

-- Name: commclass; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE commclass (

    commclass_id int(11) NOT NULL,

    locationevent_id int(11) NOT NULL,

    inspection int(1),

    tableanalysis int(1),

    multivariateanalysis int(1),

    classpublication_id int(11),

    classnotes text,

    commname text,

    commcode text,

    commframework text,

    commlevel text,

    classstartdate timestamp NULL,

    classstopdate timestamp NULL,

    expertsystem text,

    accessioncode text

);

--

-- Name: commclass_commclass_id_seq; Type: SEQUENCE; Schema: public; Owner: -

--

--

-- Name: commclass_commclass_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

--

--

-- Name: commconcept; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE commconcept (

    commconcept_id int(11) NOT NULL,

    commname_id int(11) NOT NULL,

    reference_id int(11),

    commdescription text,

    d_obscount int(11),

    commname text,

    d_currentaccepted int(1),

    accessioncode text

);

--

-- Name: commconcept_commconcept_id_seq; Type: SEQUENCE; Schema: public; Owner: -

--

--

-- Name: commconcept_commconcept_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

--

--

-- Name: commcorrelation; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE commcorrelation (

    commcorrelation_id int(11) NOT NULL,

    commstatus_id int(11) NOT NULL,

    commconcept_id int(11) NOT NULL,

    commconvergence text NOT NULL,

    correlationstart timestamp NOT NULL,

    correlationstop timestamp NULL

);

--

-- Name: commcorrelation_commcorrelation_id_seq; Type: SEQUENCE; Schema: public; Owner: -

--

--

-- Name: commcorrelation_commcorrelation_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

--

--

-- Name: commdetermination; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE commdetermination (

    commdetermination_id int(11) NOT NULL,

    commclass_id int(11) NOT NULL,

    commconcept_id int(11) NOT NULL,

    commcode text,

    commname text,

    classfit text,

    classconfidence text,

    commauthority_id int(11),

    notes text,

    type int(1),

    nomenclaturaltype int(1)

);

--

-- Name: commdetermination_commdetermination_id_seq; Type: SEQUENCE; Schema: public; Owner: -

--

--

-- Name: commdetermination_commdetermination_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

--