DROP TABLE IF EXISTS "aggregate_observation";

CREATE TABLE "aggregate_observation" (

  "traits" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*KEY "fk_aggregate_observation_taxon_name1_idx" ("taxon_concept")*/CHECK (true),

DROP TABLE IF EXISTS "base_class";

CREATE TABLE "base_class" (

  PRIMARY KEY ("id"),

  /*KEY "fk_base_class_referenced_class1_idx" ("referenced_class")*/CHECK (true),

  /*CONSTRAINT "fk_base_class_referenced_class1" FOREIGN KEY ("referenced_class") REFERENCES "referenced_class" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),

DROP TABLE IF EXISTS "collection";

CREATE TABLE "collection" (

  PRIMARY KEY ("id"),

  /*CONSTRAINT "collection_unique" */UNIQUE ("institution","name"),

  /*KEY "fk_collection_organization1_idx" ("institution")*/CHECK (true),

DROP TABLE IF EXISTS "community";

CREATE TABLE "community" (

  "info" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*CONSTRAINT "fk_community_record1" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)

DROP TABLE IF EXISTS "coordinates";

CREATE TABLE "coordinates" (

  PRIMARY KEY ("id")

);

DROP TABLE IF EXISTS "derived_class";

CREATE TABLE "derived_class" (

  PRIMARY KEY ("id"),

  /*CONSTRAINT "fk_derived_class_base_class1" FOREIGN KEY ("id") REFERENCES "base_class" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)

);

DROP TABLE IF EXISTS "event";

CREATE TABLE "event" (

  PRIMARY KEY ("id"),

  /*KEY "fk_event_place1_idx" ("place")*/CHECK (true),

  /*KEY "fk_event1_idx" ("parent")*/CHECK (true),

DROP TABLE IF EXISTS "event_participant";

CREATE TABLE "event_participant" (

  "sort_order" integer DEFAULT NULL,

  PRIMARY KEY ("event","party"),

  /*KEY "fk_event_has_party_party1_idx" ("party")*/CHECK (true),

DROP TABLE IF EXISTS "geological_context";

CREATE TABLE "geological_context" (

  "info" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*CONSTRAINT "fk_geological_context_record1" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)

DROP TABLE IF EXISTS "geovalidation";

CREATE TABLE "geovalidation" (

  "geovalid" integer NOT NULL,

  "lat_long_domain_valid" integer NOT NULL,

  "lat_long_in_ranks" set('hstore') DEFAULT NULL,

DROP TABLE IF EXISTS "individual";

CREATE TABLE "individual" (

  PRIMARY KEY ("id"),

  /*CONSTRAINT "fk_individual_record1" FOREIGN KEY ("id") REFERENCES "taxon_occurrence" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)

);

DROP TABLE IF EXISTS "individual_observation";

CREATE TABLE "individual_observation" (

  "traits" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*KEY "fk_individual_observation_individual1_idx" ("individual")*/CHECK (true),

DROP TABLE IF EXISTS "method";

CREATE TABLE "method" (

  "info" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*KEY "fk_method_method1_idx" ("parent")*/CHECK (true),

DROP TABLE IF EXISTS "organization";

CREATE TABLE "organization" (

  "info" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*CONSTRAINT "fk_organization_party1" FOREIGN KEY ("id") REFERENCES "party" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)

DROP TABLE IF EXISTS "parsed_taxon_assertion";

CREATE TABLE "parsed_taxon_assertion" (

  "match_score" float DEFAULT NULL,

  "match_info" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

DROP TABLE IF EXISTS "party";

CREATE TABLE "party" (

  "info" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*CONSTRAINT "fk_collection_source10" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)

DROP TABLE IF EXISTS "place";

CREATE TABLE "place" (

  PRIMARY KEY ("id"),

  /*KEY "fk_place_coordinates1_idx" ("coordinates")*/CHECK (true),

  /*KEY "fk_place1_idx" ("parent")*/CHECK (true),

DROP TABLE IF EXISTS "place_observation";

CREATE TABLE "place_observation" (

  "elevation_m" double precision DEFAULT NULL,

  "slope_incline_deg" double precision DEFAULT NULL,

  "slope_direction_deg_N" double precision DEFAULT NULL,

  "observations" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*KEY "fk_place_observation_place1_idx" ("place")*/CHECK (true),

DROP TABLE IF EXISTS "place_path";

CREATE TABLE "place_path" (

  "ranks" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id")

);

DROP TABLE IF EXISTS "plot";

CREATE TABLE "plot" (

  "area_m2" double precision DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*CONSTRAINT "fk_subplot_place1" FOREIGN KEY ("id") REFERENCES "place" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)

);

DROP TABLE IF EXISTS "project";

CREATE TABLE "project" (

  "info" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*CONSTRAINT "fk_project_event1" FOREIGN KEY ("id") REFERENCES "event" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)

DROP TABLE IF EXISTS "record";

CREATE TABLE "record" (

  "info" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*CONSTRAINT "record_unique" */UNIQUE ("source","source_id_scope","source_record_id"),

DROP TABLE IF EXISTS "referenced_class";

CREATE TABLE "referenced_class" (

  PRIMARY KEY ("id"),

  /*CONSTRAINT "fk_example_record10" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)

);

DROP TABLE IF EXISTS "relationship";

CREATE TABLE "relationship" (

  "info" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*KEY "fk_relationship_record1_idx" ("record")*/CHECK (true),

DROP TABLE IF EXISTS "soil_observation";

CREATE TABLE "soil_observation" (

  "observations" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*CONSTRAINT "fk_soil_observation_place_observation1" FOREIGN KEY ("id") REFERENCES "place_observation" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)

DROP TABLE IF EXISTS "source";

CREATE TABLE "source" (

  "info" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*CONSTRAINT "source_unique" */UNIQUE ("parent","name"),

DROP TABLE IF EXISTS "specimen";

CREATE TABLE "specimen" (

  PRIMARY KEY ("id"),

  /*CONSTRAINT "specimen_unique_in_individual" */UNIQUE ("individual","code_in_individual"),

  /*CONSTRAINT "specimen_unique_by_collection_event" */UNIQUE ("collection_event"),

DROP TABLE IF EXISTS "specimen_observation";

CREATE TABLE "specimen_observation" (

  "traits" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*KEY "fk_specimen_observation_specimen1_idx" ("specimen")*/CHECK (true),

DROP TABLE IF EXISTS "stem";

CREATE TABLE "stem" (

  PRIMARY KEY ("id"),

  /*KEY "fk_stem_individual1_idx" ("individual")*/CHECK (true),

  /*CONSTRAINT "fk_stem_individual1" FOREIGN KEY ("individual") REFERENCES "individual" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),

DROP TABLE IF EXISTS "stem_observation";

CREATE TABLE "stem_observation" (

  "traits" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*CONSTRAINT "stem_observation_unique" */UNIQUE ("individual_observation","stem"),

DROP TABLE IF EXISTS "stratum";

CREATE TABLE "stratum" (

  "info" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*CONSTRAINT "fk_place_path_record10" FOREIGN KEY ("id") REFERENCES "method" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)

DROP TABLE IF EXISTS "subplot";

CREATE TABLE "subplot" (

  "x_m" double precision DEFAULT NULL,

  "y_m" double precision DEFAULT NULL,

  PRIMARY KEY ("id"),

DROP TABLE IF EXISTS "taxon_assertion";

CREATE TABLE "taxon_assertion" (

  "annotations" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*KEY "fk_taxon_assertion_taxon_string1_idx" ("string")*/CHECK (true),

DROP TABLE IF EXISTS "taxon_concept";

CREATE TABLE "taxon_concept" (

  PRIMARY KEY ("id"),

  /*CONSTRAINT "taxon_concept_unique_name" */UNIQUE ("according_to"),

  /*KEY "fk_taxon_taxon1_idx" ("parent")*/CHECK (true),

DROP TABLE IF EXISTS "taxon_determination";

CREATE TABLE "taxon_determination" (

  "fit_info" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*CONSTRAINT "taxon_determination_unique" */UNIQUE ("taxon_assertion","identified_by"),

DROP TABLE IF EXISTS "taxon_name";

CREATE TABLE "taxon_name" (

  PRIMARY KEY ("id"),

  /*KEY "fk_taxon_concept_taxon_string10_idx" ("unique_name")*/CHECK (true),

  /*CONSTRAINT "fk_taxon_concept_taxon_string10" FOREIGN KEY ("unique_name") REFERENCES "taxon_string" ("string") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),

DROP TABLE IF EXISTS "taxon_observation";

CREATE TABLE "taxon_observation" (

  "cultivated" integer DEFAULT NULL,

  "traits" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

DROP TABLE IF EXISTS "taxon_occurrence";

CREATE TABLE "taxon_occurrence" (

  PRIMARY KEY ("id"),

  /*KEY "fk_taxon_occurrence_taxon_determination1_idx" ("original_determination")*/CHECK (true),

  /*KEY "fk_taxon_occurrence_taxon_determination2_idx" ("current_determination")*/CHECK (true),

DROP TABLE IF EXISTS "taxon_path";

CREATE TABLE "taxon_path" (

  "ranks" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*CONSTRAINT "fk_taxon_path_taxon_name1" FOREIGN KEY ("id") REFERENCES "taxon_name" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)

DROP TABLE IF EXISTS "taxon_presence";

CREATE TABLE "taxon_presence" (

  "traits" set('hstore') DEFAULT NULL,

  PRIMARY KEY ("id"),

  /*KEY "fk_taxon_presence_taxon_name1_idx" ("taxon_concept")*/CHECK (true),

DROP TABLE IF EXISTS "taxon_string";

CREATE TABLE "taxon_string" (

  PRIMARY KEY ("string"),

  /*KEY "fk_taxon_string_parsed_taxon_assertion1_idx" ("parsed_taxon_assertion")*/CHECK (true),

  /*CONSTRAINT "fk_taxon_string_parsed_taxon_assertion1" FOREIGN KEY ("parsed_taxon_assertion") REFERENCES "parsed_taxon_assertion" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)

DROP TABLE IF EXISTS "validatable_place";

CREATE TABLE "validatable_place" (

  PRIMARY KEY ("id"),

  /*CONSTRAINT "validatable_place_unique" */UNIQUE ("path","coordinates"),

  /*KEY "fk_geovalidation_place_path1_idx" ("path")*/CHECK (true),

-e "s/\/\*.* (SET NAMES )([0-9A-Za-z_]+) \*\/;/\1'\2';/" \

-e 's/ (CHARACTER SET|COLLATE) [0-9A-Za-z_]+//g' \

-e "s/ COMMENT '[^']*'//g" \

-e 's/ (tinytext|mediumtext|longtext)/ text/g' \

-e 's/ (int|mediumint|smallint|tinyint)([ (])/ integer\2/g' \

-e 's/ double/ double precision/g' \