merge 3.0.2 to trunk

[cdmlib.git] / cdmlib-persistence / src / test / resources / dbscripts / 001_cdm.ddl

diff --git a/cdmlib-persistence/src/test/resources/dbscripts/001_cdm.ddl b/cdmlib-persistence/src/test/resources/dbscripts/001_cdm.ddl
index c49c2bfe3691d1b7fcf4fbc9001307be82325983..6477958e4ac13df5b5109f8ebb37190b0437746e 100644 (file)
--- a/cdmlib-persistence/src/test/resources/dbscripts/001_cdm.ddl
+++ b/cdmlib-persistence/src/test/resources/dbscripts/001_cdm.ddl
@@ -1,5 +1,4 @@
-
-    create table Address (
+       create table Address (
         id integer not null,
         created timestamp,
         uuid varchar(36),
@@ -399,6 +398,158 @@
         primary key (CDM_VIEW_id, superviews_id),
         unique (superviews_id)
     );
+    
+    
+    create table Classification (
+        id integer not null,
+        created timestamp,
+        uuid varchar(36),
+        updated timestamp,
+        lsid_authority varchar(255),
+        lsid_lsid varchar(255),
+        lsid_namespace varchar(255),
+        lsid_object varchar(255),
+        lsid_revision varchar(255),
+        protectedtitlecache bit not null,
+        titleCache varchar(255),
+        microreference varchar(255),
+        createdby_id integer,
+        updatedby_id integer,
+        name_id integer,
+        reference_id integer,
+        primary key (id),
+        unique (uuid)
+    );
+
+    create table Classification_AUD (
+        id integer not null,
+        REV integer not null,
+        revtype tinyint,
+        created timestamp,
+        uuid varchar(36),
+        updated timestamp,
+        lsid_authority varchar(255),
+        lsid_lsid varchar(255),
+        lsid_namespace varchar(255),
+        lsid_object varchar(255),
+        lsid_revision varchar(255),
+        protectedtitlecache bit,
+        titleCache varchar(255),
+        microreference varchar(255),
+        createdby_id integer,
+        updatedby_id integer,
+        name_id integer,
+        reference_id integer,
+        primary key (id, REV)
+    );
+
+    create table Classification_Annotation (
+        Classification_id integer not null,
+        annotations_id integer not null,
+        primary key (Classification_id, annotations_id),
+        unique (annotations_id)
+    );
+
+    create table Classification_Annotation_AUD (
+        REV integer not null,
+        Classification_id integer not null,
+        annotations_id integer not null,
+        revtype tinyint,
+        primary key (REV, Classification_id, annotations_id)
+    );
+
+    create table Classification_Credit (
+        Classification_id integer not null,
+        credits_id integer not null,
+        sortIndex integer not null,
+        primary key (Classification_id, sortIndex),
+        unique (credits_id)
+    );
+
+    create table Classification_Credit_AUD (
+        REV integer not null,
+        Classification_id integer not null,
+        credits_id integer not null,
+        sortIndex integer not null,
+        revtype tinyint,
+        primary key (REV, Classification_id, credits_id, sortIndex)
+    );
+
+    create table Classification_Extension (
+        Classification_id integer not null,
+        extensions_id integer not null,
+        primary key (Classification_id, extensions_id),
+        unique (extensions_id)
+    );
+
+    create table Classification_Extension_AUD (
+        REV integer not null,
+        Classification_id integer not null,
+        extensions_id integer not null,
+        revtype tinyint,
+        primary key (REV, Classification_id, extensions_id)
+    );
+
+    create table Classification_Marker (
+        Classification_id integer not null,
+        markers_id integer not null,
+        primary key (Classification_id, markers_id),
+        unique (markers_id)
+    );
+
+    create table Classification_Marker_AUD (
+        REV integer not null,
+        Classification_id integer not null,
+        markers_id integer not null,
+        revtype tinyint,
+        primary key (REV, Classification_id, markers_id)
+    );
+
+    create table Classification_OriginalSourceBase (
+        Classification_id integer not null,
+        sources_id integer not null,
+        primary key (Classification_id, sources_id),
+        unique (sources_id)
+    );
+
+    create table Classification_OriginalSourceBase_AUD (
+        REV integer not null,
+        Classification_id integer not null,
+        sources_id integer not null,
+        revtype tinyint,
+        primary key (REV, Classification_id, sources_id)
+    );
+
+    create table Classification_Rights (
+        Classification_id integer not null,
+        rights_id integer not null,
+        primary key (Classification_id, rights_id),
+        unique (rights_id)
+    );
+
+    create table Classification_Rights_AUD (
+        REV integer not null,
+        Classification_id integer not null,
+        rights_id integer not null,
+        revtype tinyint,
+        primary key (REV, Classification_id, rights_id)
+    );
+
+    create table Classification_TaxonNode (
+        Classification_id integer not null,
+        rootnodes_id integer not null,
+        primary key (Classification_id, rootnodes_id),
+        unique (rootnodes_id)
+    );
+
+    create table Classification_TaxonNode_AUD (
+        REV integer not null,
+        Classification_id integer not null,
+        rootnodes_id integer not null,
+        revtype tinyint,
+        primary key (REV, Classification_id, rootnodes_id)
+    );
+
 
     create table Collection (
         id integer not null,
@@ -1002,8 +1153,8 @@
         imagegallery bit not null,
         createdby_id integer,
         updatedby_id integer,
-        taxon_fk integer,
-        taxonName_fk integer,
+        taxon_id integer,
+        taxonname_id integer,
         primary key (id),
         unique (uuid)
     );
@@ -1026,8 +1177,8 @@
         imagegallery bit,
         createdby_id integer,
         updatedby_id integer,
-        taxon_fk integer,
-        taxonName_fk integer,
+        taxon_id integer,
+        taxonname_id integer,
         primary key (id, REV)
     );
 
@@ -1498,9 +1649,9 @@
         updated timestamp,
         createdby_id integer,
         updatedby_id integer,
+        featureTree_id integer not null,
         feature_id integer,
-        parent_fk integer,
-        taxon_id integer,
+        parent_id integer,
         primary key (id),
         unique (uuid)
     );
@@ -1514,9 +1665,9 @@
         updated timestamp,
         createdby_id integer,
         updatedby_id integer,
+               featureTree_id integer not null,
         feature_id integer,
-        parent_fk integer,
-        taxon_id integer,
+        parent_id integer,
         primary key (id, REV)
     );
 
@@ -1548,21 +1699,6 @@
         primary key (REV, FeatureNode_id, onlyapplicableif_id)
     );
 
-    create table FeatureNode_Representation (
-        FeatureNode_id integer not null,
-        questions_id integer not null,
-        primary key (FeatureNode_id, questions_id),
-        unique (questions_id)
-    );
-
-    create table FeatureNode_Representation_AUD (
-        REV integer not null,
-        FeatureNode_id integer not null,
-        questions_id integer not null,
-        revtype tinyint,
-        primary key (REV, FeatureNode_id, questions_id)
-    );
-
     create table FeatureTree (
         DTYPE varchar(31) not null,
         id integer not null,
@@ -1864,6 +2000,12 @@
         primary key (id),
         unique (uuid, authority)
     );
+       
+       create table hibernate_sequences (
+               sequence_name varchar(255),
+               next_val integer,
+               primary key (sequence_name)
+       );
 
     create table HomotypicalGroup (
         id integer not null,
@@ -2040,6 +2182,46 @@
         person_id integer,
         primary key (id, REV)
     );
+           
+    create table KeyStatement (
+        id integer not null,
+        created timestamp,
+        uuid varchar(36),
+        updated timestamp,
+        createdby_id integer,
+        updatedby_id integer,
+        primary key (id),
+        unique (uuid)
+    );
+    
+    create table KeyStatement_AUD (
+        id integer not null,
+        REV integer not null,
+        revtype tinyint,
+        created timestamp,
+        uuid varchar(36),
+        updated timestamp,
+        createdby_id integer,
+        updatedby_id integer,
+        primary key (id, REV)
+      );
+      
+     create table KeyStatement_LanguageString (
+        keyStatement_id integer not null,
+        label_id integer not null,
+        label_mapkey_id integer not null,
+        primary key (keyStatement_id, label_mapkey_id),
+        unique (label_id)
+    );
+
+    create table KeyStatement_LanguageString_AUD (
+        REV integer not null,
+        keyStatement_id integer not null,
+        label_id integer not null,
+        label_mapkey_id integer not null,
+        revtype tinyint,
+        primary key (REV, keyStatement_id, label_id, label_mapkey_id)
+    );
 
     create table LSIDAuthority (
         id integer not null,
@@ -2227,17 +2409,17 @@
     );
 
     create table MediaKey_Taxon (
-        mediaKey_fk integer not null,
-        taxon_fk integer not null,
-        primary key (mediaKey_fk, taxon_fk)
+        mediaKey_id integer not null,
+        taxon_id integer not null,
+        primary key (mediaKey_id, taxon_id)
     );
 
     create table MediaKey_Taxon_AUD (
         REV integer not null,
-        mediaKey_fk integer not null,
-        taxon_fk integer not null,
+        mediaKey_id integer not null,
+        taxon_id integer not null,
         revtype tinyint,
-        primary key (REV, mediaKey_fk, taxon_fk)
+        primary key (REV, mediaKey_id, taxon_id)
     );
 
     create table MediaRepresentation (
@@ -2538,17 +2720,17 @@
     );
 
     create table MultiAccessKey_Taxon (
-        multiAccessKey_fk integer not null,
-        taxon_fk integer not null,
-        primary key (multiAccessKey_fk, taxon_fk)
+        multiAccessKey_id integer not null,
+        taxon_id integer not null,
+        primary key (multiAccessKey_id, taxon_id)
     );
 
     create table MultiAccessKey_Taxon_AUD (
         REV integer not null,
-        multiAccessKey_fk integer not null,
-        taxon_fk integer not null,
+        multiAccessKey_id integer not null,
+        taxon_id integer not null,
         revtype tinyint,
-        primary key (REV, multiAccessKey_fk, taxon_fk)
+        primary key (REV, multiAccessKey_id, taxon_id)
     );
 
     create table NameRelationship (
@@ -2768,47 +2950,233 @@
         primary key (PermissionGroup_id, grantedauthorities_id)
     );
 
+   create table PolytomousKey (
+               id integer not null,
+        created timestamp,
+        uuid varchar(36),
+        updated timestamp,
+        lsid_authority varchar(255),
+        lsid_lsid varchar(255),
+        lsid_namespace varchar(255),
+        lsid_object varchar(255),
+        lsid_revision varchar(255),
+        protectedtitlecache bit not null,
+        titleCache varchar(255),
+        uri varchar(255),
+        createdby_id integer,
+        updatedby_id integer,
+        root_id integer,
+        primary key (id),
+        unique (uuid)
+    );
+
+   create table PolytomousKey_AUD (
+               id integer not null,
+        REV integer not null,
+        revtype tinyint,
+        created timestamp,
+        uuid varchar(36),
+        updated timestamp,
+        lsid_authority varchar(255),
+        lsid_lsid varchar(255),
+        lsid_namespace varchar(255),
+        lsid_object varchar(255),
+        lsid_revision varchar(255),
+        protectedtitlecache bit not null,
+        titleCache varchar(255),
+        uri varchar(255),
+        createdby_id integer,
+        updatedby_id integer,
+        root_id integer,
+        primary key (id, REV)
+    );
+
+    create table PolytomousKey_Annotation (
+        PolytomousKey_id integer not null,
+        annotations_id integer not null,
+        primary key (PolytomousKey_id, annotations_id),
+        unique (annotations_id)
+    );
+
+    create table PolytomousKey_Annotation_AUD (
+        REV integer not null,
+        PolytomousKey_id integer not null,
+        annotations_id integer not null,
+        revtype tinyint,
+        primary key (REV, PolytomousKey_id, annotations_id)
+    );
+
+    create table PolytomousKey_Credit (
+        PolytomousKey_id integer not null,
+        credits_id integer not null,
+        sortIndex integer not null,
+        primary key (PolytomousKey_id, sortIndex),
+        unique (credits_id)
+    );
+
+    create table PolytomousKey_Credit_AUD (
+        REV integer not null,
+        PolytomousKey_id integer not null,
+        credits_id integer not null,
+        sortIndex integer not null,
+        revtype tinyint,
+        primary key (REV, PolytomousKey_id, credits_id, sortIndex)
+    );
+
+    create table PolytomousKey_Extension (
+        PolytomousKey_id integer not null,
+        extensions_id integer not null,
+        primary key (PolytomousKey_id, extensions_id),
+        unique (extensions_id)
+    );
+
+    create table PolytomousKey_Extension_AUD (
+        REV integer not null,
+        PolytomousKey_id integer not null,
+        extensions_id integer not null,
+        revtype tinyint,
+        primary key (REV, PolytomousKey_id, extensions_id)
+    );
+
+    create table PolytomousKey_Marker (
+        PolytomousKey_id integer not null,
+        markers_id integer not null,
+        primary key (PolytomousKey_id, markers_id),
+        unique (markers_id)
+    );
+
+    create table PolytomousKey_Marker_AUD (
+        REV integer not null,
+        PolytomousKey_id integer not null,
+        markers_id integer not null,
+        revtype tinyint,
+        primary key (REV, PolytomousKey_id, markers_id)
+    );
+
     create table PolytomousKey_NamedArea (
-        FeatureTree_id integer not null,
+        PolytomousKey_id integer not null,
         geographicalscope_id integer not null,
-        primary key (FeatureTree_id, geographicalscope_id)
+        primary key (PolytomousKey_id, geographicalscope_id)
     );
 
     create table PolytomousKey_NamedArea_AUD (
         REV integer not null,
-        FeatureTree_id integer not null,
+        PolytomousKey_id integer not null,
         geographicalscope_id integer not null,
         revtype tinyint,
-        primary key (REV, FeatureTree_id, geographicalscope_id)
+        primary key (REV, PolytomousKey_id, geographicalscope_id)
+    );
+    
+    create table PolytomousKey_OriginalSourceBase (
+        PolytomousKey_id integer not null,
+        sources_id integer not null,
+        primary key (PolytomousKey_id, sources_id),
+        unique (sources_id)
+    );
+
+    create table PolytomousKey_OriginalSourceBase_AUD (
+        REV integer not null,
+        PolytomousKey_id integer not null,
+        sources_id integer not null,
+        revtype tinyint,
+        primary key (REV, PolytomousKey_id, sources_id)
+    );
+
+
+    create table PolytomousKey_Rights (
+        PolytomousKey_id integer not null,
+        rights_id integer not null,
+        primary key (PolytomousKey_id, rights_id),
+        unique (rights_id)
+    );
+
+    create table PolytomousKey_Rights_AUD (
+        REV integer not null,
+        PolytomousKey_id integer not null,
+        rights_id integer not null,
+        revtype tinyint,
+        primary key (REV, PolytomousKey_id, rights_id)
     );
 
     create table PolytomousKey_Scope (
-        FeatureTree_id integer not null,
+        PolytomousKey_id integer not null,
         scoperestrictions_id integer not null,
-        primary key (FeatureTree_id, scoperestrictions_id)
+        primary key (PolytomousKey_id, scoperestrictions_id)
     );
 
     create table PolytomousKey_Scope_AUD (
         REV integer not null,
-        FeatureTree_id integer not null,
+        PolytomousKey_id integer not null,
         scoperestrictions_id integer not null,
         revtype tinyint,
-        primary key (REV, FeatureTree_id, scoperestrictions_id)
+        primary key (REV, PolytomousKey_id, scoperestrictions_id)
     );
 
     create table PolytomousKey_Taxon (
-        polytomousKey_fk integer not null,
-        taxon_fk integer not null,
-        primary key (polytomousKey_fk, taxon_fk)
+        polytomousKey_id integer not null,
+        taxon_id integer not null,
+        primary key (polytomousKey_id, taxon_id)
     );
 
     create table PolytomousKey_Taxon_AUD (
         REV integer not null,
-        polytomousKey_fk integer not null,
-        taxon_fk integer not null,
+        polytomousKey_id integer not null,
+        taxon_id integer not null,
+        revtype tinyint,
+        primary key (REV, polytomousKey_id, taxon_id)
+    );
+    
+        
+    create table PolytomousKeyNode (
+        id integer not null,
+        created timestamp,
+        uuid varchar(36),
+        updated timestamp,
+        nodenumber integer,
+        sortindex integer,
+        createdby_id integer,
+        updatedby_id integer,
+        key_id integer,
+        parent_id integer,
+        question_id integer,
+        statement_id integer,
+        feature_id integer,
+        taxon_id integer,
+        subkey_id integer,
+        othernode_id integer,
+        primary key (id),
+        unique (uuid)
+    );
+
+    create table PolytomousKeyNode_AUD (
+        id integer not null,
+        REV integer not null,
         revtype tinyint,
-        primary key (REV, polytomousKey_fk, taxon_fk)
+        created timestamp,
+        uuid varchar(36),
+        updated timestamp,
+        nodenumber integer,
+        sortindex integer,
+        createdby_id integer,
+        updatedby_id integer,
+        key_id integer,
+        parent_id integer,
+        question_id integer,
+        statement_id integer,
+        feature_id integer,
+        taxon_id integer,
+        subkey_id integer,
+        othernode_id integer,
+        primary key (id, REV)
     );
+    
+    create table PolytomousKeyNode_PolytomousKeyNode_AUD(
+       id integer not null,
+        REV integer not null,
+        revtype tinyint,
+        parent_id integer, 
+        sortIndex integer
+    )
 
     create table Reference (
         DTYPE varchar(31) not null,
@@ -3363,15 +3731,18 @@
         createdby_id integer,
         updatedby_id integer,
         lifestage_id integer,
-        sex_id integer,
+        sex_id integer,    
+        exsiccatum varchar(255),
+       primarycollector_id integer,
         collection_id integer,
-        derivationevent_id integer,
+        derivedfrom_id integer,
         storedunder_id integer,
         preservation_id integer,
         gatheringevent_id integer,
         primary key (id),
         unique (uuid)
     );
+    
 
     create table SpecimenOrObservationBase_AUD (
         DTYPE varchar(31) not null,
@@ -3396,12 +3767,14 @@
         fieldnotes varchar(255),
         fieldnumber varchar(255),
         barcode varchar(255),
+        exsiccatum varchar(255),
         gatheringevent_id integer,
         accessionnumber varchar(255),
         catalognumber varchar(255),
         collectorsnumber varchar(255),
+        primarycollector_id integer,
         collection_id integer,
-        derivationevent_id integer,
+        derivedfrom_id integer,
         storedunder_id integer,
         preservation_id integer,
         primary key (id, REV)
@@ -3470,19 +3843,19 @@
 
     create table SpecimenOrObservationBase_LanguageString (
         SpecimenOrObservationBase_id integer not null,
-        description_id integer not null,
-        description_mapkey_id integer not null,
-        primary key (SpecimenOrObservationBase_id, description_mapkey_id),
-        unique (description_id)
+        definition_id integer not null,
+        definition_mapkey_id integer not null,
+        primary key (SpecimenOrObservationBase_id, definition_mapkey_id),
+        unique (definition_id)
     );
 
     create table SpecimenOrObservationBase_LanguageString_AUD (
         REV integer not null,
         SpecimenOrObservationBase_id integer not null,
-        description_id integer not null,
-        description_mapkey_id integer not null,
+        definition_id integer not null,
+        definition_mapkey_id integer not null,
         revtype tinyint,
-        primary key (REV, SpecimenOrObservationBase_id, description_id, description_mapkey_id)
+        primary key (REV, SpecimenOrObservationBase_id, definition_id, definition_mapkey_id)
     );
 
     create table SpecimenOrObservationBase_Marker (
@@ -3751,7 +4124,7 @@
         taxonomicchildrencount integer,
         createdby_id integer,
         updatedby_id integer,
-        taxonName_fk integer,
+        name_id integer,
         sec_id integer,
         taxonomicparentcache_id integer,
         primary key (id),
@@ -3778,7 +4151,7 @@
         usenamecache bit,
         createdby_id integer,
         updatedby_id integer,
-        taxonName_fk integer,
+        name_id integer,
         sec_id integer,
         taxonstatusunknown bit,
         unplaced bit,
@@ -4138,7 +4511,7 @@
         referenceforparentchildrelation_id integer,
         synonymtobeused_id integer,
         taxon_id integer,
-        taxonomictree_id integer,
+        classification_id integer,
         primary key (id),
         unique (uuid)
     );
@@ -4158,7 +4531,7 @@
         referenceforparentchildrelation_id integer,
         synonymtobeused_id integer,
         taxon_id integer,
-        taxonomictree_id integer,
+        classification_id integer,
         primary key (id, REV)
     );
 
@@ -4259,156 +4632,6 @@
         primary key (REV, TaxonRelationship_id, markers_id)
     );
 
-    create table TaxonomicTree (
-        id integer not null,
-        created timestamp,
-        uuid varchar(36),
-        updated timestamp,
-        lsid_authority varchar(255),
-        lsid_lsid varchar(255),
-        lsid_namespace varchar(255),
-        lsid_object varchar(255),
-        lsid_revision varchar(255),
-        protectedtitlecache bit not null,
-        titleCache varchar(255),
-        microreference varchar(255),
-        createdby_id integer,
-        updatedby_id integer,
-        name_id integer,
-        reference_id integer,
-        primary key (id),
-        unique (uuid)
-    );
-
-    create table TaxonomicTree_AUD (
-        id integer not null,
-        REV integer not null,
-        revtype tinyint,
-        created timestamp,
-        uuid varchar(36),
-        updated timestamp,
-        lsid_authority varchar(255),
-        lsid_lsid varchar(255),
-        lsid_namespace varchar(255),
-        lsid_object varchar(255),
-        lsid_revision varchar(255),
-        protectedtitlecache bit,
-        titleCache varchar(255),
-        microreference varchar(255),
-        createdby_id integer,
-        updatedby_id integer,
-        name_id integer,
-        reference_id integer,
-        primary key (id, REV)
-    );
-
-    create table TaxonomicTree_Annotation (
-        TaxonomicTree_id integer not null,
-        annotations_id integer not null,
-        primary key (TaxonomicTree_id, annotations_id),
-        unique (annotations_id)
-    );
-
-    create table TaxonomicTree_Annotation_AUD (
-        REV integer not null,
-        TaxonomicTree_id integer not null,
-        annotations_id integer not null,
-        revtype tinyint,
-        primary key (REV, TaxonomicTree_id, annotations_id)
-    );
-
-    create table TaxonomicTree_Credit (
-        TaxonomicTree_id integer not null,
-        credits_id integer not null,
-        sortIndex integer not null,
-        primary key (TaxonomicTree_id, sortIndex),
-        unique (credits_id)
-    );
-
-    create table TaxonomicTree_Credit_AUD (
-        REV integer not null,
-        TaxonomicTree_id integer not null,
-        credits_id integer not null,
-        sortIndex integer not null,
-        revtype tinyint,
-        primary key (REV, TaxonomicTree_id, credits_id, sortIndex)
-    );
-
-    create table TaxonomicTree_Extension (
-        TaxonomicTree_id integer not null,
-        extensions_id integer not null,
-        primary key (TaxonomicTree_id, extensions_id),
-        unique (extensions_id)
-    );
-
-    create table TaxonomicTree_Extension_AUD (
-        REV integer not null,
-        TaxonomicTree_id integer not null,
-        extensions_id integer not null,
-        revtype tinyint,
-        primary key (REV, TaxonomicTree_id, extensions_id)
-    );
-
-    create table TaxonomicTree_Marker (
-        TaxonomicTree_id integer not null,
-        markers_id integer not null,
-        primary key (TaxonomicTree_id, markers_id),
-        unique (markers_id)
-    );
-
-    create table TaxonomicTree_Marker_AUD (
-        REV integer not null,
-        TaxonomicTree_id integer not null,
-        markers_id integer not null,
-        revtype tinyint,
-        primary key (REV, TaxonomicTree_id, markers_id)
-    );
-
-    create table TaxonomicTree_OriginalSourceBase (
-        TaxonomicTree_id integer not null,
-        sources_id integer not null,
-        primary key (TaxonomicTree_id, sources_id),
-        unique (sources_id)
-    );
-
-    create table TaxonomicTree_OriginalSourceBase_AUD (
-        REV integer not null,
-        TaxonomicTree_id integer not null,
-        sources_id integer not null,
-        revtype tinyint,
-        primary key (REV, TaxonomicTree_id, sources_id)
-    );
-
-    create table TaxonomicTree_Rights (
-        TaxonomicTree_id integer not null,
-        rights_id integer not null,
-        primary key (TaxonomicTree_id, rights_id),
-        unique (rights_id)
-    );
-
-    create table TaxonomicTree_Rights_AUD (
-        REV integer not null,
-        TaxonomicTree_id integer not null,
-        rights_id integer not null,
-        revtype tinyint,
-        primary key (REV, TaxonomicTree_id, rights_id)
-    );
-
-    create table TaxonomicTree_TaxonNode (
-        TaxonomicTree_id integer not null,
-        rootnodes_id integer not null,
-        primary key (TaxonomicTree_id, rootnodes_id),
-        unique (rootnodes_id)
-    );
-
-    create table TaxonomicTree_TaxonNode_AUD (
-        REV integer not null,
-        TaxonomicTree_id integer not null,
-        rootnodes_id integer not null,
-        revtype tinyint,
-        primary key (REV, TaxonomicTree_id, rootnodes_id)
-    );
-
     create table TermVocabulary (
         DTYPE varchar(31) not null,
         id integer not null,
@@ -5620,12 +5843,12 @@
 
     alter table DescriptionBase 
         add constraint FKFF4D58CDDE9A3DE3 
-        foreign key (taxon_fk) 
+        foreign key (taxon_id) 
         references TaxonBase;
 
     alter table DescriptionBase 
         add constraint FKFF4D58CDDA93512F 
-        foreign key (taxonName_fk) 
+        foreign key (taxonname_id) 
         references TaxonNameBase;
 
     alter table DescriptionBase 
@@ -6115,19 +6338,19 @@
 
     alter table FeatureNode 
         add constraint FK4CEED9F8E0AD2C03 
-        foreign key (parent_fk) 
+        foreign key (parent_id) 
         references FeatureNode;
 
+    alter table FeatureNode 
+        add constraint FK4CEED9F8DE9A3E39 
+        foreign key (featureTree_id) 
+        references FeatureTree;
+
     alter table FeatureNode 
         add constraint FK4CEED9F84220AFEB 
         foreign key (feature_id) 
         references DefinedTermBase;
 
-    alter table FeatureNode 
-        add constraint FK4CEED9F8DE9A3E39 
-        foreign key (taxon_id) 
-        references TaxonBase;
-
     alter table FeatureNode 
         add constraint FK4CEED9F8BC5DA539 
         foreign key (updatedby_id) 
@@ -6168,21 +6391,6 @@
         foreign key (REV) 
         references AuditEvent;
 
-    alter table FeatureNode_Representation 
-        add constraint FK98668A14ED54F5E0 
-        foreign key (questions_id) 
-        references Representation;
-
-    alter table FeatureNode_Representation 
-        add constraint FK98668A1452FCC4B 
-        foreign key (FeatureNode_id) 
-        references FeatureNode;
-
-    alter table FeatureNode_Representation_AUD 
-        add constraint FK8F578DE534869AAE 
-        foreign key (REV) 
-        references AuditEvent;
-
     alter table FeatureTree 
         add constraint FK4CF19F944FF2DB2C 
         foreign key (createdby_id) 
@@ -6720,12 +6928,12 @@
 
     alter table MediaKey_Taxon 
         add constraint FKC00C3966815C793 
-        foreign key (mediaKey_fk) 
+        foreign key (mediaKey_id) 
         references Media;
 
     alter table MediaKey_Taxon 
         add constraint FKC00C3966DE9A3DE3 
-        foreign key (taxon_fk) 
+        foreign key (taxon_id) 
         references TaxonBase;
 
     alter table MediaKey_Taxon_AUD 
@@ -6990,12 +7198,12 @@
 
     alter table MultiAccessKey_Taxon 
         add constraint FKCC7A356DB64A7AD3 
-        foreign key (multiAccessKey_fk) 
+        foreign key (multiAccessKey_id) 
         references WorkingSet;
 
     alter table MultiAccessKey_Taxon 
         add constraint FKCC7A356DDE9A3DE3 
-        foreign key (taxon_fk) 
+        foreign key (taxon_id) 
         references TaxonBase;
 
     alter table MultiAccessKey_Taxon_AUD 
@@ -7200,8 +7408,8 @@
 
     alter table PolytomousKey_NamedArea 
         add constraint FK1C727CFFED57882F 
-        foreign key (FeatureTree_id) 
-        references FeatureTree;
+        foreign key (PolytomousKey_id) 
+        references PolytomousKey;
 
     alter table PolytomousKey_NamedArea_AUD 
         add constraint FK750A135034869AAE 
@@ -7210,8 +7418,8 @@
 
     alter table PolytomousKey_Scope 
         add constraint FK8D97986DED57882F 
-        foreign key (FeatureTree_id) 
-        references FeatureTree;
+        foreign key (PolytomousKey_id) 
+        references PolytomousKey;
 
     alter table PolytomousKey_Scope 
         add constraint FK8D97986D546985E4 
@@ -7225,12 +7433,12 @@
 
     alter table PolytomousKey_Taxon 
         add constraint FK8DA4E8E389D9775 
-        foreign key (polytomousKey_fk) 
-        references FeatureTree;
+        foreign key (polytomousKey_id) 
+        references PolytomousKey;
 
     alter table PolytomousKey_Taxon 
         add constraint FK8DA4E8E3DE9A3DE3 
-        foreign key (taxon_fk) 
+        foreign key (taxon_id) 
         references TaxonBase;
 
     alter table PolytomousKey_Taxon_AUD 
@@ -7669,6 +7877,12 @@
 
     create index specimenOrObservationBaseTitleCacheIndex on SpecimenOrObservationBase (titleCache);
 
+    
+    alter table SpecimenOrObservationBase 
+        add constraint FK11CB3232F75F225E 
+        foreign key (primarycollector_id) 
+        references AgentBase;
+    
     alter table SpecimenOrObservationBase 
         add constraint FK21CA32727CC340C5 
         foreign key (storedunder_id) 
@@ -7701,7 +7915,7 @@
 
     alter table SpecimenOrObservationBase 
         add constraint FK21CA32724AAB411A 
-        foreign key (derivationevent_id) 
+        foreign key (derivedfrom_id) 
         references DerivationEvent;
 
     alter table SpecimenOrObservationBase 
@@ -7781,12 +7995,12 @@
 
     alter table SpecimenOrObservationBase_LanguageString 
         add constraint FKCFAA931628459272 
-        foreign key (description_mapkey_id) 
+        foreign key (definition_mapkey_id) 
         references DefinedTermBase;
 
     alter table SpecimenOrObservationBase_LanguageString 
         add constraint FKCFAA93162BEBA58D 
-        foreign key (description_id) 
+        foreign key (definition_id) 
         references LanguageString;
 
     alter table SpecimenOrObservationBase_LanguageString 
@@ -8048,7 +8262,7 @@
 
     alter table TaxonBase 
         add constraint FK9249B49BDA93512F 
-        foreign key (taxonName_fk) 
+        foreign key (name_id) 
         references TaxonNameBase;
 
     alter table TaxonBase 
@@ -8355,8 +8569,8 @@
 
     alter table TaxonNode 
         add constraint FK924F5BCC759FE399 
-        foreign key (taxonomictree_id) 
-        references TaxonomicTree;
+        foreign key (classification_id) 
+        references Classification;
 
     alter table TaxonNode 
         add constraint FK924F5BCCDE9A3E39 
@@ -8478,132 +8692,132 @@
         foreign key (REV) 
         references AuditEvent;
 
-    alter table TaxonomicTree 
+    alter table Classification 
         add constraint FKE332DBE04FF2DB2C 
         foreign key (createdby_id) 
         references UserAccount;
 
-    alter table TaxonomicTree 
+    alter table Classification 
         add constraint FKE332DBE0765B124B 
         foreign key (reference_id) 
         references Reference;
 
-    alter table TaxonomicTree 
+    alter table Classification 
         add constraint FKE332DBE077E2F09E 
         foreign key (name_id) 
         references LanguageString;
 
-    alter table TaxonomicTree 
+    alter table Classification 
         add constraint FKE332DBE0BC5DA539 
         foreign key (updatedby_id) 
         references UserAccount;
 
-    alter table TaxonomicTree_AUD 
+    alter table Classification_AUD 
         add constraint FK14CE19B134869AAE 
         foreign key (REV) 
         references AuditEvent;
 
-    alter table TaxonomicTree_Annotation 
+    alter table Classification_Annotation 
         add constraint FK9877150E759FE399 
-        foreign key (TaxonomicTree_id) 
-        references TaxonomicTree;
+        foreign key (Classification_id) 
+        references Classification;
 
-    alter table TaxonomicTree_Annotation 
+    alter table Classification_Annotation 
         add constraint FK9877150E1E403E0B 
         foreign key (annotations_id) 
         references Annotation;
 
-    alter table TaxonomicTree_Annotation_AUD 
+    alter table Classification_Annotation_AUD 
         add constraint FKADD60BDF34869AAE 
         foreign key (REV) 
         references AuditEvent;
 
-    alter table TaxonomicTree_Credit 
+    alter table Classification_Credit 
         add constraint FK21329C58759FE399 
-        foreign key (TaxonomicTree_id) 
-        references TaxonomicTree;
+        foreign key (Classification_id) 
+        references Classification;
 
-    alter table TaxonomicTree_Credit 
+    alter table Classification_Credit 
         add constraint FK21329C5832D1B9F 
         foreign key (credits_id) 
         references Credit;
 
-    alter table TaxonomicTree_Credit_AUD 
+    alter table Classification_Credit_AUD 
         add constraint FKD388DE2934869AAE 
         foreign key (REV) 
         references AuditEvent;
 
-    alter table TaxonomicTree_Extension 
+    alter table Classification_Extension 
         add constraint FKF3E9BA80759FE399 
-        foreign key (TaxonomicTree_id) 
-        references TaxonomicTree;
+        foreign key (Classification_id) 
+        references Classification;
 
-    alter table TaxonomicTree_Extension 
+    alter table Classification_Extension 
         add constraint FKF3E9BA80927DE9DF 
         foreign key (extensions_id) 
         references Extension;
 
-    alter table TaxonomicTree_Extension_AUD 
+    alter table Classification_Extension_AUD 
         add constraint FK1BB4A85134869AAE 
         foreign key (REV) 
         references AuditEvent;
 
-    alter table TaxonomicTree_Marker 
+    alter table Classification_Marker 
         add constraint FK31598599777265A1 
         foreign key (markers_id) 
         references Marker;
 
-    alter table TaxonomicTree_Marker 
+    alter table Classification_Marker 
         add constraint FK31598599759FE399 
-        foreign key (TaxonomicTree_id) 
-        references TaxonomicTree;
+        foreign key (Classification_id) 
+        references Classification;
 
-    alter table TaxonomicTree_Marker_AUD 
+    alter table Classification_Marker_AUD 
         add constraint FK37A73EEA34869AAE 
         foreign key (REV) 
         references AuditEvent;
 
-    alter table TaxonomicTree_OriginalSourceBase 
+    alter table Classification_OriginalSourceBase 
         add constraint FKDE264D1C759FE399 
-        foreign key (TaxonomicTree_id) 
-        references TaxonomicTree;
+        foreign key (Classification_id) 
+        references Classification;
 
-    alter table TaxonomicTree_OriginalSourceBase 
+    alter table Classification_OriginalSourceBase 
         add constraint FKDE264D1C3A6735D9 
         foreign key (sources_id) 
         references OriginalSourceBase;
 
-    alter table TaxonomicTree_OriginalSourceBase_AUD 
+    alter table Classification_OriginalSourceBase_AUD 
         add constraint FK99EE8CED34869AAE 
         foreign key (REV) 
         references AuditEvent;
 
-    alter table TaxonomicTree_Rights 
+    alter table Classification_Rights 
         add constraint FK3A4D7336759FE399 
-        foreign key (TaxonomicTree_id) 
-        references TaxonomicTree;
+        foreign key (Classification_id) 
+        references Classification;
 
-    alter table TaxonomicTree_Rights 
+    alter table Classification_Rights 
         add constraint FK3A4D7336C13F7B21 
         foreign key (rights_id) 
         references Rights;
 
-    alter table TaxonomicTree_Rights_AUD 
+    alter table Classification_Rights_AUD 
         add constraint FKA381160734869AAE 
         foreign key (REV) 
         references AuditEvent;
 
-    alter table TaxonomicTree_TaxonNode 
+    alter table Classification_TaxonNode 
         add constraint FK3349DA2D759FE399 
-        foreign key (TaxonomicTree_id) 
-        references TaxonomicTree;
+        foreign key (Classification_id) 
+        references Classification;
 
-    alter table TaxonomicTree_TaxonNode 
+    alter table Classification_TaxonNode 
         add constraint FK3349DA2D18929176 
         foreign key (rootnodes_id) 
         references TaxonNode;
 
-    alter table TaxonomicTree_TaxonNode_AUD 
+    alter table Classification_TaxonNode_AUD 
         add constraint FK6973297E34869AAE 
         foreign key (REV) 
         references AuditEvent;