Last active
August 29, 2015 14:10
-
-
Save dlebauer/59d9e44ec29838259fa4 to your computer and use it in GitHub Desktop.
BETYdb Schema with proposed constraints
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| Navicat PGSQL Data Transfer | |
| Source Server : pecandev | |
| Source Server Version : 90305 | |
| Source Host : pecandev.igb.illinois.edu:5432 | |
| Source Database : bety_constraints | |
| Source Schema : public | |
| Target Server Type : PGSQL | |
| Target Server Version : 90305 | |
| File Encoding : 65001 | |
| Date: 2014-12-11 15:08:42 | |
| */ | |
| -- ---------------------------- | |
| -- Sequence structure for citations_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."citations_id_seq"; | |
| CREATE SEQUENCE "public"."citations_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 758 | |
| CACHE 1; | |
| SELECT setval('"public"."citations_id_seq"', 758, true); | |
| -- ---------------------------- | |
| -- Sequence structure for counties_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."counties_id_seq"; | |
| CREATE SEQUENCE "public"."counties_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 3399 | |
| CACHE 1; | |
| SELECT setval('"public"."counties_id_seq"', 3399, true); | |
| -- ---------------------------- | |
| -- Sequence structure for covariates_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."covariates_id_seq"; | |
| CREATE SEQUENCE "public"."covariates_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 15780 | |
| CACHE 1; | |
| SELECT setval('"public"."covariates_id_seq"', 15780, true); | |
| -- ---------------------------- | |
| -- Sequence structure for cultivars_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."cultivars_id_seq"; | |
| CREATE SEQUENCE "public"."cultivars_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 462 | |
| CACHE 1; | |
| SELECT setval('"public"."cultivars_id_seq"', 462, true); | |
| -- ---------------------------- | |
| -- Sequence structure for dbfiles_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."dbfiles_id_seq"; | |
| CREATE SEQUENCE "public"."dbfiles_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 3959 | |
| CACHE 1; | |
| SELECT setval('"public"."dbfiles_id_seq"', 3959, true); | |
| -- ---------------------------- | |
| -- Sequence structure for ensembles_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."ensembles_id_seq"; | |
| CREATE SEQUENCE "public"."ensembles_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 26 | |
| CACHE 1; | |
| SELECT setval('"public"."ensembles_id_seq"', 26, true); | |
| -- ---------------------------- | |
| -- Sequence structure for entities_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."entities_id_seq"; | |
| CREATE SEQUENCE "public"."entities_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 69 | |
| CACHE 1; | |
| SELECT setval('"public"."entities_id_seq"', 69, true); | |
| -- ---------------------------- | |
| -- Sequence structure for formats_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."formats_id_seq"; | |
| CREATE SEQUENCE "public"."formats_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 38 | |
| CACHE 1; | |
| SELECT setval('"public"."formats_id_seq"', 38, true); | |
| -- ---------------------------- | |
| -- Sequence structure for formats_variables_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."formats_variables_id_seq"; | |
| CREATE SEQUENCE "public"."formats_variables_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 56 | |
| CACHE 1; | |
| SELECT setval('"public"."formats_variables_id_seq"', 56, true); | |
| -- ---------------------------- | |
| -- Sequence structure for inputs_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."inputs_id_seq"; | |
| CREATE SEQUENCE "public"."inputs_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 286 | |
| CACHE 1; | |
| SELECT setval('"public"."inputs_id_seq"', 286, true); | |
| -- ---------------------------- | |
| -- Sequence structure for likelihoods_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."likelihoods_id_seq"; | |
| CREATE SEQUENCE "public"."likelihoods_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 1 | |
| CACHE 1; | |
| -- ---------------------------- | |
| -- Sequence structure for location_yields_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."location_yields_id_seq"; | |
| CREATE SEQUENCE "public"."location_yields_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 29478 | |
| CACHE 1; | |
| SELECT setval('"public"."location_yields_id_seq"', 29478, true); | |
| -- ---------------------------- | |
| -- Sequence structure for machines_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."machines_id_seq"; | |
| CREATE SEQUENCE "public"."machines_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 11 | |
| CACHE 1; | |
| SELECT setval('"public"."machines_id_seq"', 11, true); | |
| -- ---------------------------- | |
| -- Sequence structure for managements_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."managements_id_seq"; | |
| CREATE SEQUENCE "public"."managements_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 3355 | |
| CACHE 1; | |
| SELECT setval('"public"."managements_id_seq"', 3355, true); | |
| -- ---------------------------- | |
| -- Sequence structure for methods_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."methods_id_seq"; | |
| CREATE SEQUENCE "public"."methods_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 39 | |
| CACHE 1; | |
| SELECT setval('"public"."methods_id_seq"', 39, true); | |
| -- ---------------------------- | |
| -- Sequence structure for mimetypes_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."mimetypes_id_seq"; | |
| CREATE SEQUENCE "public"."mimetypes_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 1086 | |
| CACHE 1; | |
| SELECT setval('"public"."mimetypes_id_seq"', 1086, true); | |
| -- ---------------------------- | |
| -- Sequence structure for models_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."models_id_seq"; | |
| CREATE SEQUENCE "public"."models_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 10 | |
| CACHE 1; | |
| SELECT setval('"public"."models_id_seq"', 10, true); | |
| -- ---------------------------- | |
| -- Sequence structure for pfts_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."pfts_id_seq"; | |
| CREATE SEQUENCE "public"."pfts_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 104 | |
| CACHE 1; | |
| SELECT setval('"public"."pfts_id_seq"', 104, true); | |
| -- ---------------------------- | |
| -- Sequence structure for posteriors_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."posteriors_id_seq"; | |
| CREATE SEQUENCE "public"."posteriors_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 341 | |
| CACHE 1; | |
| SELECT setval('"public"."posteriors_id_seq"', 341, true); | |
| -- ---------------------------- | |
| -- Sequence structure for priors_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."priors_id_seq"; | |
| CREATE SEQUENCE "public"."priors_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 275 | |
| CACHE 1; | |
| SELECT setval('"public"."priors_id_seq"', 275, true); | |
| -- ---------------------------- | |
| -- Sequence structure for runs_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."runs_id_seq"; | |
| CREATE SEQUENCE "public"."runs_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 414 | |
| CACHE 1; | |
| SELECT setval('"public"."runs_id_seq"', 414, true); | |
| -- ---------------------------- | |
| -- Sequence structure for sessions_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."sessions_id_seq"; | |
| CREATE SEQUENCE "public"."sessions_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 6265 | |
| CACHE 1; | |
| SELECT setval('"public"."sessions_id_seq"', 6265, true); | |
| -- ---------------------------- | |
| -- Sequence structure for sites_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."sites_id_seq"; | |
| CREATE SEQUENCE "public"."sites_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 1159 | |
| CACHE 1; | |
| SELECT setval('"public"."sites_id_seq"', 1159, true); | |
| -- ---------------------------- | |
| -- Sequence structure for species_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."species_id_seq"; | |
| CREATE SEQUENCE "public"."species_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 51189 | |
| CACHE 1; | |
| SELECT setval('"public"."species_id_seq"', 51189, true); | |
| -- ---------------------------- | |
| -- Sequence structure for traits_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."traits_id_seq"; | |
| CREATE SEQUENCE "public"."traits_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 51201 | |
| CACHE 1; | |
| SELECT setval('"public"."traits_id_seq"', 51201, true); | |
| -- ---------------------------- | |
| -- Sequence structure for treatments_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."treatments_id_seq"; | |
| CREATE SEQUENCE "public"."treatments_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 2204 | |
| CACHE 1; | |
| SELECT setval('"public"."treatments_id_seq"', 2204, true); | |
| -- ---------------------------- | |
| -- Sequence structure for users_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."users_id_seq"; | |
| CREATE SEQUENCE "public"."users_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 161 | |
| CACHE 1; | |
| SELECT setval('"public"."users_id_seq"', 161, true); | |
| -- ---------------------------- | |
| -- Sequence structure for variables_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."variables_id_seq"; | |
| CREATE SEQUENCE "public"."variables_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 553 | |
| CACHE 1; | |
| SELECT setval('"public"."variables_id_seq"', 553, true); | |
| -- ---------------------------- | |
| -- Sequence structure for workflows_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."workflows_id_seq"; | |
| CREATE SEQUENCE "public"."workflows_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 1 | |
| CACHE 1; | |
| -- ---------------------------- | |
| -- Sequence structure for yields_id_seq | |
| -- ---------------------------- | |
| DROP SEQUENCE "public"."yields_id_seq"; | |
| CREATE SEQUENCE "public"."yields_id_seq" | |
| INCREMENT 1 | |
| MINVALUE 1 | |
| MAXVALUE 9223372036854775807 | |
| START 12983 | |
| CACHE 1; | |
| SELECT setval('"public"."yields_id_seq"', 12983, true); | |
| -- ---------------------------- | |
| -- Table structure for citations | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."citations"; | |
| CREATE TABLE "public"."citations" ( | |
| "id" int4 DEFAULT nextval('citations_id_seq'::regclass) NOT NULL, | |
| "author" varchar(255) COLLATE "default" NOT NULL, | |
| "year" int4 NOT NULL, | |
| "title" varchar(255) COLLATE "default" NOT NULL, | |
| "journal" varchar(255) COLLATE "default", | |
| "vol" int4, | |
| "pg" varchar(255) COLLATE "default", | |
| "url" varchar(512) COLLATE "default", | |
| "pdf" varchar(255) COLLATE "default", | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "doi" varchar(255) COLLATE "default", | |
| "user_id" int4 | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| COMMENT ON COLUMN "public"."citations"."author" IS 'last name of first author'; | |
| COMMENT ON COLUMN "public"."citations"."year" IS 'year of publication'; | |
| COMMENT ON COLUMN "public"."citations"."title" IS 'article title'; | |
| COMMENT ON COLUMN "public"."citations"."journal" IS 'Journal name'; | |
| COMMENT ON COLUMN "public"."citations"."pg" IS 'page range of article'; | |
| COMMENT ON COLUMN "public"."citations"."url" IS 'link to article url'; | |
| COMMENT ON COLUMN "public"."citations"."pdf" IS 'link to pdf version of article'; | |
| COMMENT ON COLUMN "public"."citations"."doi" IS 'Digital Object Identifier'; | |
| -- ---------------------------- | |
| -- Table structure for citations_sites | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."citations_sites"; | |
| CREATE TABLE "public"."citations_sites" ( | |
| "citation_id" int4 NOT NULL, | |
| "site_id" int4 NOT NULL, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6) | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for citations_treatments | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."citations_treatments"; | |
| CREATE TABLE "public"."citations_treatments" ( | |
| "citation_id" int8 NOT NULL, | |
| "treatment_id" int8 NOT NULL, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6) | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for covariates | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."covariates"; | |
| CREATE TABLE "public"."covariates" ( | |
| "id" int4 DEFAULT nextval('covariates_id_seq'::regclass) NOT NULL, | |
| "trait_id" int4 NOT NULL, | |
| "variable_id" int4 NOT NULL, | |
| "level" numeric(16,4), | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "n" int4, | |
| "statname" varchar(255) COLLATE "default", | |
| "stat" numeric(16,4) | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| COMMENT ON COLUMN "public"."covariates"."level" IS 'Value of covariate, units are determined in variables table by the variable_id foreign key.'; | |
| -- ---------------------------- | |
| -- Table structure for cultivars | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."cultivars"; | |
| CREATE TABLE "public"."cultivars" ( | |
| "id" int4 DEFAULT nextval('cultivars_id_seq'::regclass) NOT NULL, | |
| "specie_id" int4 NOT NULL, | |
| "name" varchar(255) COLLATE "default" NOT NULL, | |
| "ecotype" varchar(255) COLLATE "default", | |
| "notes" text COLLATE "default", | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "previous_id" int4 | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| COMMENT ON COLUMN "public"."cultivars"."name" IS 'Cultivar name given by breeder or reported in citation.'; | |
| COMMENT ON COLUMN "public"."cultivars"."ecotype" IS 'Does not apply for all species, used in the case of switchgrass to differentiate lowland and upland genotypes.'; | |
| -- ---------------------------- | |
| -- Table structure for dbfiles | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."dbfiles"; | |
| CREATE TABLE "public"."dbfiles" ( | |
| "id" int4 DEFAULT nextval('dbfiles_id_seq'::regclass) NOT NULL, | |
| "file_name" varchar(255) COLLATE "default" NOT NULL, | |
| "file_path" varchar(255) COLLATE "default" NOT NULL, | |
| "md5" varchar(255) COLLATE "default", | |
| "created_user_id" int4, | |
| "updated_user_id" int4, | |
| "machine_id" int4 NOT NULL, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "container_type" varchar(255) COLLATE "default" NOT NULL, | |
| "container_id" int4 NOT NULL | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| COMMENT ON COLUMN "public"."dbfiles"."container_type" IS 'this and container_id are part of a polymorphic relationship, specifies table and primary key of that table'; | |
| COMMENT ON COLUMN "public"."dbfiles"."container_id" IS 'foreign key for either inputs or posteriors (table specified by container_type)'; | |
| -- ---------------------------- | |
| -- Table structure for ensembles | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."ensembles"; | |
| CREATE TABLE "public"."ensembles" ( | |
| "id" int4 DEFAULT nextval('ensembles_id_seq'::regclass) NOT NULL, | |
| "notes" text COLLATE "default", | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "runtype" varchar(255) COLLATE "default", | |
| "workflow_id" int4 NOT NULL | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for entities | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."entities"; | |
| CREATE TABLE "public"."entities" ( | |
| "id" int4 DEFAULT nextval('entities_id_seq'::regclass) NOT NULL, | |
| "parent_id" int4, | |
| "name" varchar(255) COLLATE "default", | |
| "notes" text COLLATE "default", | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6) | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for formats | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."formats"; | |
| CREATE TABLE "public"."formats" ( | |
| "id" int4 DEFAULT nextval('formats_id_seq'::regclass) NOT NULL, | |
| "dataformat" text COLLATE "default", | |
| "notes" text COLLATE "default", | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "name" varchar(255) COLLATE "default", | |
| "header" varchar(255) COLLATE "default", | |
| "skip" varchar(255) COLLATE "default", | |
| "mimetype_id" int4 | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for formats_variables | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."formats_variables"; | |
| CREATE TABLE "public"."formats_variables" ( | |
| "id" int4 DEFAULT nextval('formats_variables_id_seq'::regclass) NOT NULL, | |
| "format_id" int4, | |
| "variable_id" int4, | |
| "name" varchar(255) COLLATE "default", | |
| "unit" varchar(255) COLLATE "default", | |
| "storage_type" varchar(255) COLLATE "default", | |
| "column_number" int4, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6) | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for inputs | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."inputs"; | |
| CREATE TABLE "public"."inputs" ( | |
| "id" int4 DEFAULT nextval('inputs_id_seq'::regclass) NOT NULL, | |
| "site_id" int4, | |
| "notes" text COLLATE "default", | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "start_date" timestamp(6), | |
| "end_date" timestamp(6), | |
| "name" varchar(255) COLLATE "default" NOT NULL, | |
| "parent_id" int4, | |
| "user_id" int4, | |
| "access_level" int4 NOT NULL, | |
| "raw" bool, | |
| "format_id" int4 NOT NULL | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for inputs_runs | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."inputs_runs"; | |
| CREATE TABLE "public"."inputs_runs" ( | |
| "input_id" int4 NOT NULL, | |
| "run_id" int4 NOT NULL, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6) | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for inputs_variables | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."inputs_variables"; | |
| CREATE TABLE "public"."inputs_variables" ( | |
| "input_id" int4 NOT NULL, | |
| "variable_id" int4 NOT NULL, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6) | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for likelihoods | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."likelihoods"; | |
| CREATE TABLE "public"."likelihoods" ( | |
| "id" int4 DEFAULT nextval('likelihoods_id_seq'::regclass) NOT NULL, | |
| "run_id" int4 NOT NULL, | |
| "variable_id" int4 NOT NULL, | |
| "input_id" int4 NOT NULL, | |
| "loglikelihood" numeric(10), | |
| "n_eff" numeric(10), | |
| "weight" numeric(10), | |
| "residual" numeric(10), | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6) | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for machines | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."machines"; | |
| CREATE TABLE "public"."machines" ( | |
| "id" int4 DEFAULT nextval('machines_id_seq'::regclass) NOT NULL, | |
| "hostname" varchar(255) COLLATE "default" NOT NULL, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6) | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for managements | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."managements"; | |
| CREATE TABLE "public"."managements" ( | |
| "id" int4 DEFAULT nextval('managements_id_seq'::regclass) NOT NULL, | |
| "citation_id" int4, | |
| "date" date NOT NULL, | |
| "dateloc" numeric(4,2), | |
| "mgmttype" varchar(255) COLLATE "default" NOT NULL, | |
| "level" numeric(16,4), | |
| "units" varchar(255) COLLATE "default", | |
| "notes" text COLLATE "default", | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "user_id" int4 | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| COMMENT ON COLUMN "public"."managements"."date" IS 'Date on which management was conducted.'; | |
| COMMENT ON COLUMN "public"."managements"."dateloc" IS 'Level of confidence in value given as date. See documentation for details.'; | |
| COMMENT ON COLUMN "public"."managements"."mgmttype" IS 'Type of management'; | |
| COMMENT ON COLUMN "public"."managements"."level" IS 'Amount applied, not always required.'; | |
| COMMENT ON COLUMN "public"."managements"."units" IS 'units, standardized for each management type.'; | |
| -- ---------------------------- | |
| -- Table structure for managements_treatments | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."managements_treatments"; | |
| CREATE TABLE "public"."managements_treatments" ( | |
| "treatment_id" int4 NOT NULL, | |
| "management_id" int4 NOT NULL, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6) | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for methods | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."methods"; | |
| CREATE TABLE "public"."methods" ( | |
| "id" int4 DEFAULT nextval('methods_id_seq'::regclass) NOT NULL, | |
| "name" varchar(255) COLLATE "default" NOT NULL, | |
| "description" text COLLATE "default" NOT NULL, | |
| "citation_id" int4 NOT NULL, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6) | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for mimetypes | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."mimetypes"; | |
| CREATE TABLE "public"."mimetypes" ( | |
| "id" int4 DEFAULT nextval('mimetypes_id_seq'::regclass) NOT NULL, | |
| "type_string" varchar(255) COLLATE "default" NOT NULL | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for models | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."models"; | |
| CREATE TABLE "public"."models" ( | |
| "id" int4 DEFAULT nextval('models_id_seq'::regclass) NOT NULL, | |
| "model_name" varchar(255) COLLATE "default" NOT NULL, | |
| "model_path" varchar(255) COLLATE "default" NOT NULL, | |
| "revision" varchar(255) COLLATE "default" NOT NULL, | |
| "parent_id" int4, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "model_type" varchar(255) COLLATE "default" NOT NULL | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for pfts | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."pfts"; | |
| CREATE TABLE "public"."pfts" ( | |
| "id" int4 DEFAULT nextval('pfts_id_seq'::regclass) NOT NULL, | |
| "definition" text COLLATE "default" NOT NULL, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "name" varchar(255) COLLATE "default" NOT NULL | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| COMMENT ON COLUMN "public"."pfts"."definition" IS 'Defines the creator and context under which the pft will be used.'; | |
| COMMENT ON COLUMN "public"."pfts"."name" IS 'unique identifier used by PEcAn.'; | |
| -- ---------------------------- | |
| -- Table structure for pfts_priors | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."pfts_priors"; | |
| CREATE TABLE "public"."pfts_priors" ( | |
| "pft_id" int4 NOT NULL, | |
| "prior_id" int4 NOT NULL, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6) | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for pfts_species | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."pfts_species"; | |
| CREATE TABLE "public"."pfts_species" ( | |
| "pft_id" int4 NOT NULL, | |
| "specie_id" int4 NOT NULL, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6) | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for posteriors | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."posteriors"; | |
| CREATE TABLE "public"."posteriors" ( | |
| "id" int4 DEFAULT nextval('posteriors_id_seq'::regclass) NOT NULL, | |
| "pft_id" int4 NOT NULL, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "format_id" int4 NOT NULL | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for posteriors_runs | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."posteriors_runs"; | |
| CREATE TABLE "public"."posteriors_runs" ( | |
| "posterior_id" int4 NOT NULL, | |
| "run_id" int4 NOT NULL, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6) | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for priors | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."priors"; | |
| CREATE TABLE "public"."priors" ( | |
| "id" int4 DEFAULT nextval('priors_id_seq'::regclass) NOT NULL, | |
| "citation_id" int4 NOT NULL, | |
| "variable_id" int4 NOT NULL, | |
| "phylogeny" varchar(255) COLLATE "default", | |
| "distn" varchar(255) COLLATE "default" NOT NULL, | |
| "parama" numeric(16,4) NOT NULL, | |
| "paramb" numeric(16,4), | |
| "paramc" numeric(16,4), | |
| "n" int4, | |
| "notes" text COLLATE "default", | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6) | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| COMMENT ON COLUMN "public"."priors"."phylogeny" IS 'Used to note the group of plants for which the prior was specified, often the group of plants represented by the data used to specify the prior.'; | |
| COMMENT ON COLUMN "public"."priors"."distn" IS 'Name of the probability distribution, using R naming convention (e.g. ''beta'',''f'', ''gamma'', ''lnorm'', ''norm'', ''pois'', ''t'', ''unif'', ''weibull''.'; | |
| COMMENT ON COLUMN "public"."priors"."parama" IS 'First parameter for distribution, as specified by R.'; | |
| COMMENT ON COLUMN "public"."priors"."paramb" IS 'Second parameter for distribution, as specified by R.'; | |
| COMMENT ON COLUMN "public"."priors"."paramc" IS 'A third parameter, if required.'; | |
| COMMENT ON COLUMN "public"."priors"."n" IS 'number of observations used to specify prior.'; | |
| -- ---------------------------- | |
| -- Table structure for runs | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."runs"; | |
| CREATE TABLE "public"."runs" ( | |
| "id" int4 DEFAULT nextval('runs_id_seq'::regclass) NOT NULL, | |
| "model_id" int4 NOT NULL, | |
| "site_id" int4 NOT NULL, | |
| "start_time" timestamp(6) NOT NULL, | |
| "finish_time" timestamp(6) NOT NULL, | |
| "outdir" varchar(255) COLLATE "default" NOT NULL, | |
| "outprefix" varchar(255) COLLATE "default" NOT NULL, | |
| "setting" varchar(255) COLLATE "default" NOT NULL, | |
| "parameter_list" text COLLATE "default" NOT NULL, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "started_at" timestamp(6) NOT NULL, | |
| "finished_at" timestamp(6), | |
| "ensemble_id" int4 NOT NULL | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| COMMENT ON COLUMN "public"."runs"."start_time" IS 'beginning of time period being simulated'; | |
| COMMENT ON COLUMN "public"."runs"."finish_time" IS 'end of time period being simulated'; | |
| COMMENT ON COLUMN "public"."runs"."started_at" IS 'system time when run ends'; | |
| COMMENT ON COLUMN "public"."runs"."finished_at" IS 'system time when run ends; can be null when record is created'; | |
| -- ---------------------------- | |
| -- Table structure for schema_migrations | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."schema_migrations"; | |
| CREATE TABLE "public"."schema_migrations" ( | |
| "version" varchar(255) COLLATE "default" NOT NULL | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for sites | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."sites"; | |
| CREATE TABLE "public"."sites" ( | |
| "id" int4 DEFAULT nextval('sites_id_seq'::regclass) NOT NULL, | |
| "city" varchar(255) COLLATE "default", | |
| "state" varchar(255) COLLATE "default", | |
| "country" varchar(255) COLLATE "default", | |
| "lat" numeric(9,6) NOT NULL, | |
| "lon" numeric(9,6) NOT NULL, | |
| "mat" int4, | |
| "map" int4, | |
| "masl" int4, | |
| "soil" varchar(255) COLLATE "default", | |
| "som" numeric(4,2), | |
| "notes" text COLLATE "default", | |
| "soilnotes" text COLLATE "default", | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "sitename" varchar(255) COLLATE "default" NOT NULL, | |
| "greenhouse" bool NOT NULL, | |
| "user_id" int4, | |
| "local_time" int4, | |
| "sand_pct" numeric(9,5), | |
| "clay_pct" numeric(9,5), | |
| "espg" varchar(255) COLLATE "default" | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| COMMENT ON COLUMN "public"."sites"."city" IS 'Nearest city to site.'; | |
| COMMENT ON COLUMN "public"."sites"."state" IS 'If in the United States, state in which study is conducted.'; | |
| COMMENT ON COLUMN "public"."sites"."lat" IS 'Latitude, in decimal degrees'; | |
| COMMENT ON COLUMN "public"."sites"."lon" IS 'Longitude, in decimal degrees.'; | |
| COMMENT ON COLUMN "public"."sites"."mat" IS 'Mean Annual Temperature (C)'; | |
| COMMENT ON COLUMN "public"."sites"."map" IS 'Mean Annual Precipitation (mm)'; | |
| COMMENT ON COLUMN "public"."sites"."masl" IS 'Elevation (m above sea level)'; | |
| COMMENT ON COLUMN "public"."sites"."soil" IS 'Soil type, as described in documentation.'; | |
| COMMENT ON COLUMN "public"."sites"."som" IS 'Depreciated'; | |
| COMMENT ON COLUMN "public"."sites"."greenhouse" IS 'Boolean: indicates if study was conducted in a field (0) or greenhouse, pot, or growth chamber (1)'; | |
| -- ---------------------------- | |
| -- Table structure for species | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."species"; | |
| CREATE TABLE "public"."species" ( | |
| "id" int4 DEFAULT nextval('species_id_seq'::regclass) NOT NULL, | |
| "spcd" int4, | |
| "genus" varchar(255) COLLATE "default" NOT NULL, | |
| "species" varchar(255) COLLATE "default" NOT NULL, | |
| "scientificname" varchar(255) COLLATE "default" NOT NULL, | |
| "commonname" varchar(255) COLLATE "default", | |
| "notes" varchar(255) COLLATE "default", | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "AcceptedSymbol" varchar(255) COLLATE "default", | |
| "SynonymSymbol" varchar(255) COLLATE "default", | |
| "Symbol" varchar(255) COLLATE "default", | |
| "PLANTS_Floristic_Area" text COLLATE "default", | |
| "State" text COLLATE "default", | |
| "Category" varchar(255) COLLATE "default", | |
| "Family" varchar(255) COLLATE "default", | |
| "FamilySymbol" varchar(255) COLLATE "default", | |
| "FamilyCommonName" varchar(255) COLLATE "default", | |
| "xOrder" varchar(255) COLLATE "default", | |
| "SubClass" varchar(255) COLLATE "default", | |
| "Class" varchar(255) COLLATE "default", | |
| "SubDivision" varchar(255) COLLATE "default", | |
| "Division" varchar(255) COLLATE "default", | |
| "SuperDivision" varchar(255) COLLATE "default", | |
| "SubKingdom" varchar(255) COLLATE "default", | |
| "Kingdom" varchar(255) COLLATE "default", | |
| "ITIS_TSN" int4, | |
| "Duration" varchar(255) COLLATE "default", | |
| "GrowthHabit" varchar(255) COLLATE "default", | |
| "NativeStatus" varchar(255) COLLATE "default", | |
| "NationalWetlandIndicatorStatus" varchar(255) COLLATE "default", | |
| "RegionalWetlandIndicatorStatus" varchar(255) COLLATE "default", | |
| "ActiveGrowthPeriod" varchar(255) COLLATE "default", | |
| "AfterHarvestRegrowthRate" varchar(255) COLLATE "default", | |
| "Bloat" varchar(255) COLLATE "default", | |
| "C2N_Ratio" varchar(255) COLLATE "default", | |
| "CoppicePotential" varchar(255) COLLATE "default", | |
| "FallConspicuous" varchar(255) COLLATE "default", | |
| "FireResistance" varchar(255) COLLATE "default", | |
| "FoliageTexture" varchar(255) COLLATE "default", | |
| "GrowthForm" varchar(255) COLLATE "default", | |
| "GrowthRate" varchar(255) COLLATE "default", | |
| "MaxHeight20Yrs" int4, | |
| "MatureHeight" int4, | |
| "KnownAllelopath" varchar(255) COLLATE "default", | |
| "LeafRetention" varchar(255) COLLATE "default", | |
| "Lifespan" varchar(255) COLLATE "default", | |
| "LowGrowingGrass" varchar(255) COLLATE "default", | |
| "NitrogenFixation" varchar(255) COLLATE "default", | |
| "ResproutAbility" varchar(255) COLLATE "default", | |
| "AdaptedCoarseSoils" varchar(255) COLLATE "default", | |
| "AdaptedMediumSoils" varchar(255) COLLATE "default", | |
| "AdaptedFineSoils" varchar(255) COLLATE "default", | |
| "AnaerobicTolerance" varchar(255) COLLATE "default", | |
| "CaCO3Tolerance" varchar(255) COLLATE "default", | |
| "ColdStratification" varchar(255) COLLATE "default", | |
| "DroughtTolerance" varchar(255) COLLATE "default", | |
| "FertilityRequirement" varchar(255) COLLATE "default", | |
| "FireTolerance" varchar(255) COLLATE "default", | |
| "MinFrostFreeDays" int4, | |
| "HedgeTolerance" varchar(255) COLLATE "default", | |
| "MoistureUse" varchar(255) COLLATE "default", | |
| "pH_Minimum" numeric(5,2), | |
| "pH_Maximum" numeric(5,2), | |
| "Min_PlantingDensity" int4, | |
| "Max_PlantingDensity" int4, | |
| "Precipitation_Minimum" int4, | |
| "Precipitation_Maximum" int4, | |
| "RootDepthMinimum" int4, | |
| "SalinityTolerance" varchar(255) COLLATE "default", | |
| "ShadeTolerance" varchar(255) COLLATE "default", | |
| "TemperatureMinimum" int4, | |
| "BloomPeriod" varchar(255) COLLATE "default", | |
| "CommercialAvailability" varchar(255) COLLATE "default", | |
| "FruitSeedPeriodBegin" varchar(255) COLLATE "default", | |
| "FruitSeedPeriodEnd" varchar(255) COLLATE "default", | |
| "Propogated_by_BareRoot" varchar(255) COLLATE "default", | |
| "Propogated_by_Bulbs" varchar(255) COLLATE "default", | |
| "Propogated_by_Container" varchar(255) COLLATE "default", | |
| "Propogated_by_Corms" varchar(255) COLLATE "default", | |
| "Propogated_by_Cuttings" varchar(255) COLLATE "default", | |
| "Propogated_by_Seed" varchar(255) COLLATE "default", | |
| "Propogated_by_Sod" varchar(255) COLLATE "default", | |
| "Propogated_by_Sprigs" varchar(255) COLLATE "default", | |
| "Propogated_by_Tubers" varchar(255) COLLATE "default", | |
| "Seeds_per_Pound" int4, | |
| "SeedSpreadRate" varchar(255) COLLATE "default", | |
| "SeedlingVigor" varchar(255) COLLATE "default" | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for traits | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."traits"; | |
| CREATE TABLE "public"."traits" ( | |
| "id" int4 DEFAULT nextval('traits_id_seq'::regclass) NOT NULL, | |
| "site_id" int4, | |
| "specie_id" int4 NOT NULL, | |
| "citation_id" int4 NOT NULL, | |
| "cultivar_id" int4, | |
| "treatment_id" int4 NOT NULL, | |
| "date" timestamp(6), | |
| "dateloc" numeric(4,2), | |
| "time" time(6), | |
| "timeloc" numeric(4,2), | |
| "mean" numeric(16,4) NOT NULL, | |
| "n" int4, | |
| "statname" varchar(255) COLLATE "default", | |
| "stat" numeric(16,4), | |
| "notes" text COLLATE "default", | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "variable_id" int4 NOT NULL, | |
| "user_id" int4, | |
| "checked" int4 NOT NULL, | |
| "access_level" int4 NOT NULL, | |
| "entity_id" int4, | |
| "method_id" int4, | |
| "date_year" int4, | |
| "date_month" int4, | |
| "date_day" int4, | |
| "time_hour" int4, | |
| "time_minute" int4 | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| COMMENT ON COLUMN "public"."traits"."site_id" IS 'Site at which measurement was taken.'; | |
| COMMENT ON COLUMN "public"."traits"."specie_id" IS 'Species on which measurement was taken.'; | |
| COMMENT ON COLUMN "public"."traits"."citation_id" IS 'Citation in which data was originally reported.'; | |
| COMMENT ON COLUMN "public"."traits"."cultivar_id" IS 'Cultivar information, if any.'; | |
| COMMENT ON COLUMN "public"."traits"."treatment_id" IS 'Experimental treatment identification. Required, can indicate observational study.'; | |
| COMMENT ON COLUMN "public"."traits"."date" IS 'Date on which measurement was made.'; | |
| COMMENT ON COLUMN "public"."traits"."dateloc" IS 'Level of confidence in date. See documentation.'; | |
| COMMENT ON COLUMN "public"."traits"."time" IS 'Time at which measurement was taken. Sometimes necessary, e.g. for photosynthesis measurements.'; | |
| COMMENT ON COLUMN "public"."traits"."timeloc" IS 'Level of confidence in time.'; | |
| COMMENT ON COLUMN "public"."traits"."mean" IS 'Mean value of trait.'; | |
| COMMENT ON COLUMN "public"."traits"."n" IS 'Number of experimental replicates used to estimate mean and statistical summary.'; | |
| COMMENT ON COLUMN "public"."traits"."statname" IS 'Name of reported statistic.'; | |
| COMMENT ON COLUMN "public"."traits"."stat" IS 'Value of reported statistic.'; | |
| COMMENT ON COLUMN "public"."traits"."variable_id" IS 'Links to information in variables table that describes trait being measured. '; | |
| COMMENT ON COLUMN "public"."traits"."user_id" IS 'ID of user who entered data.'; | |
| COMMENT ON COLUMN "public"."traits"."checked" IS 'accepts values [-1, 0, 1]. 0 is default, and means that data have not been checked. 1 indicates that the data have been checked and are correct, -1 indicates that data have been checked and found to be incorrect or suspicious, e.g. outside of the acceptab'; | |
| COMMENT ON COLUMN "public"."traits"."access_level" IS 'Level of access required to view data.'; | |
| -- ---------------------------- | |
| -- Table structure for treatments | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."treatments"; | |
| CREATE TABLE "public"."treatments" ( | |
| "id" int4 DEFAULT nextval('treatments_id_seq'::regclass) NOT NULL, | |
| "name" varchar(255) COLLATE "default" NOT NULL, | |
| "definition" varchar(255) COLLATE "default", | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "control" bool NOT NULL, | |
| "user_id" int4 | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| COMMENT ON COLUMN "public"."treatments"."name" IS 'Name of treatment, should be easy to associate with treatment name in original study.'; | |
| COMMENT ON COLUMN "public"."treatments"."definition" IS 'Description of treatment, e.g. levels of fertilizer applied, etc. This information may be redundant with ''levels'' information recorded in Managements table.'; | |
| COMMENT ON COLUMN "public"."treatments"."control" IS 'Boolean, indicates if treatment is a control or observational (1) or experimental treatment (0).'; | |
| -- ---------------------------- | |
| -- Table structure for users | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."users"; | |
| CREATE TABLE "public"."users" ( | |
| "id" int4 DEFAULT nextval('users_id_seq'::regclass) NOT NULL, | |
| "login" varchar(40) COLLATE "default" NOT NULL, | |
| "name" varchar(100) COLLATE "default" NOT NULL, | |
| "email" varchar(100) COLLATE "default" NOT NULL, | |
| "city" varchar(255) COLLATE "default", | |
| "country" varchar(255) COLLATE "default", | |
| "area" varchar(255) COLLATE "default", | |
| "crypted_password" varchar(40) COLLATE "default" NOT NULL, | |
| "salt" varchar(40) COLLATE "default" NOT NULL, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "remember_token" varchar(40) COLLATE "default", | |
| "remember_token_expires_at" timestamp(6), | |
| "access_level" int4 NOT NULL, | |
| "page_access_level" int4 NOT NULL, | |
| "apikey" varchar(255) COLLATE "default" NOT NULL, | |
| "state_prov" varchar(255) COLLATE "default", | |
| "postal_code" varchar(255) COLLATE "default" | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| COMMENT ON COLUMN "public"."users"."login" IS 'login id'; | |
| COMMENT ON COLUMN "public"."users"."name" IS 'User name'; | |
| COMMENT ON COLUMN "public"."users"."email" IS 'email address'; | |
| COMMENT ON COLUMN "public"."users"."access_level" IS 'data to which user has access'; | |
| COMMENT ON COLUMN "public"."users"."page_access_level" IS 'Determines the extent of data, if any, that user can edit.'; | |
| -- ---------------------------- | |
| -- Table structure for variables | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."variables"; | |
| CREATE TABLE "public"."variables" ( | |
| "id" int4 DEFAULT nextval('variables_id_seq'::regclass) NOT NULL, | |
| "description" varchar(255) COLLATE "default", | |
| "units" varchar(255) COLLATE "default" NOT NULL, | |
| "notes" text COLLATE "default", | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "name" varchar(255) COLLATE "default" NOT NULL, | |
| "max" varchar(255) COLLATE "default", | |
| "min" varchar(255) COLLATE "default" | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| COMMENT ON COLUMN "public"."variables"."description" IS 'Description or definition of variable.'; | |
| COMMENT ON COLUMN "public"."variables"."units" IS 'units in which data must be entered.'; | |
| COMMENT ON COLUMN "public"."variables"."name" IS 'variable name, this is the name used by PEcAn and in other modeling contexts.'; | |
| -- ---------------------------- | |
| -- Table structure for workflows | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."workflows"; | |
| CREATE TABLE "public"."workflows" ( | |
| "id" int4 DEFAULT nextval('workflows_id_seq'::regclass) NOT NULL, | |
| "folder" varchar(255) COLLATE "default" NOT NULL, | |
| "started_at" timestamp(6) NOT NULL, | |
| "finished_at" timestamp(6) NOT NULL, | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "site_id" int4 NOT NULL, | |
| "model_id" int4 NOT NULL, | |
| "hostname" varchar(255) COLLATE "default" NOT NULL, | |
| "params" text COLLATE "default" NOT NULL, | |
| "advanced_edit" bool DEFAULT false NOT NULL, | |
| "start_date" timestamp(6) NOT NULL, | |
| "end_date" timestamp(6) NOT NULL | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| -- ---------------------------- | |
| -- Table structure for yields | |
| -- ---------------------------- | |
| DROP TABLE IF EXISTS "public"."yields"; | |
| CREATE TABLE "public"."yields" ( | |
| "id" int4 DEFAULT nextval('yields_id_seq'::regclass) NOT NULL, | |
| "citation_id" int4 NOT NULL, | |
| "site_id" int4, | |
| "specie_id" int4 NOT NULL, | |
| "treatment_id" int4 NOT NULL, | |
| "cultivar_id" int4, | |
| "date" date, | |
| "dateloc" numeric(4,2), | |
| "statname" varchar(255) COLLATE "default", | |
| "stat" numeric(16,4), | |
| "mean" numeric(16,4) NOT NULL, | |
| "n" int4, | |
| "notes" text COLLATE "default", | |
| "created_at" timestamp(6), | |
| "updated_at" timestamp(6), | |
| "user_id" int4, | |
| "checked" int4 NOT NULL, | |
| "access_level" int4 NOT NULL, | |
| "method_id" int4, | |
| "entity_id" int4, | |
| "date_year" int4, | |
| "date_month" int4, | |
| "date_day" int4 | |
| ) | |
| WITH (OIDS=FALSE) | |
| ; | |
| COMMENT ON COLUMN "public"."yields"."citation_id" IS 'Citation in which data originally reported.'; | |
| COMMENT ON COLUMN "public"."yields"."site_id" IS 'Site at which crop was harvested.'; | |
| COMMENT ON COLUMN "public"."yields"."specie_id" IS 'Species for which yield was measured.'; | |
| COMMENT ON COLUMN "public"."yields"."treatment_id" IS 'Experimental treatment identification. Required, can indicate observational study.'; | |
| COMMENT ON COLUMN "public"."yields"."cultivar_id" IS 'Cultivar information, if any.'; | |
| COMMENT ON COLUMN "public"."yields"."date" IS 'Date on which crop was harvested.'; | |
| COMMENT ON COLUMN "public"."yields"."dateloc" IS 'Level of confidence in harvest date. See documentation.'; | |
| COMMENT ON COLUMN "public"."yields"."statname" IS 'Name of reported statistic.'; | |
| COMMENT ON COLUMN "public"."yields"."stat" IS 'Value of reported statistic.'; | |
| COMMENT ON COLUMN "public"."yields"."mean" IS 'Mean yield reported. '; | |
| COMMENT ON COLUMN "public"."yields"."n" IS 'Number of replicates used to estimate mean and statistical summary.'; | |
| COMMENT ON COLUMN "public"."yields"."user_id" IS 'ID of user who entered data.'; | |
| COMMENT ON COLUMN "public"."yields"."checked" IS 'accepts values [-1, 0, 1]. 0 is default, and means that data have not been checked. 1 indicates that the data have been checked and are correct, -1 indicates that data have been checked and found to be incorrect or suspicious, e.g. outside of the acceptab'; | |
| COMMENT ON COLUMN "public"."yields"."access_level" IS 'Level of access required to view data.'; | |
| -- ---------------------------- | |
| -- Alter Sequences Owned By | |
| -- ---------------------------- | |
| -- ---------------------------- | |
| -- Indexes structure for table citations | |
| -- ---------------------------- | |
| CREATE INDEX "index_citations_on_user_id" ON "public"."citations" USING btree (user_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table citations | |
| -- ---------------------------- | |
| ALTER TABLE "public"."citations" ADD UNIQUE ("author", "year", "title"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table citations | |
| -- ---------------------------- | |
| ALTER TABLE "public"."citations" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table citations_sites | |
| -- ---------------------------- | |
| CREATE UNIQUE INDEX "index_citations_sites_on_citation_id_and_site_id" ON "public"."citations_sites" USING btree (citation_id, site_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table citations_sites | |
| -- ---------------------------- | |
| ALTER TABLE "public"."citations_sites" ADD UNIQUE ("citation_id", "site_id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table citations_treatments | |
| -- ---------------------------- | |
| CREATE UNIQUE INDEX "index_citations_treatments_on_citation_id_and_treatment_id" ON "public"."citations_treatments" USING btree (citation_id, treatment_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table citations_treatments | |
| -- ---------------------------- | |
| ALTER TABLE "public"."citations_treatments" ADD UNIQUE ("citation_id", "treatment_id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table covariates | |
| -- ---------------------------- | |
| CREATE INDEX "index_covariates_on_trait_id_and_variable_id" ON "public"."covariates" USING btree (trait_id, variable_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table covariates | |
| -- ---------------------------- | |
| ALTER TABLE "public"."covariates" ADD UNIQUE ("trait_id", "variable_id"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table covariates | |
| -- ---------------------------- | |
| ALTER TABLE "public"."covariates" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table cultivars | |
| -- ---------------------------- | |
| CREATE INDEX "index_cultivars_on_specie_id" ON "public"."cultivars" USING btree (specie_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table cultivars | |
| -- ---------------------------- | |
| ALTER TABLE "public"."cultivars" ADD UNIQUE ("specie_id", "name"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table cultivars | |
| -- ---------------------------- | |
| ALTER TABLE "public"."cultivars" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table dbfiles | |
| -- ---------------------------- | |
| CREATE INDEX "index_dbfiles_on_container_id_and_container_type" ON "public"."dbfiles" USING btree (container_type); | |
| CREATE INDEX "index_dbfiles_on_created_user_id" ON "public"."dbfiles" USING btree (created_user_id); | |
| CREATE INDEX "index_dbfiles_on_machine_id" ON "public"."dbfiles" USING btree (machine_id); | |
| CREATE INDEX "index_dbfiles_on_updated_user_id" ON "public"."dbfiles" USING btree (updated_user_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table dbfiles | |
| -- ---------------------------- | |
| ALTER TABLE "public"."dbfiles" ADD UNIQUE ("file_name", "file_path", "machine_id"); | |
| ALTER TABLE "public"."dbfiles" ADD UNIQUE ("container_type", "container_id"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table dbfiles | |
| -- ---------------------------- | |
| ALTER TABLE "public"."dbfiles" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table ensembles | |
| -- ---------------------------- | |
| ALTER TABLE "public"."ensembles" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table entities | |
| -- ---------------------------- | |
| CREATE INDEX "index_entities_on_parent_id" ON "public"."entities" USING btree (parent_id); | |
| -- ---------------------------- | |
| -- Primary Key structure for table entities | |
| -- ---------------------------- | |
| ALTER TABLE "public"."entities" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Uniques structure for table formats | |
| -- ---------------------------- | |
| ALTER TABLE "public"."formats" ADD UNIQUE ("dataformat"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table formats | |
| -- ---------------------------- | |
| ALTER TABLE "public"."formats" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table formats_variables | |
| -- ---------------------------- | |
| CREATE INDEX "index_formats_variables_on_format_id_and_variable_id" ON "public"."formats_variables" USING btree (format_id, variable_id); | |
| -- ---------------------------- | |
| -- Primary Key structure for table formats_variables | |
| -- ---------------------------- | |
| ALTER TABLE "public"."formats_variables" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table inputs | |
| -- ---------------------------- | |
| CREATE INDEX "index_inputs_on_format_id" ON "public"."inputs" USING btree (format_id); | |
| CREATE INDEX "index_inputs_on_parent_id" ON "public"."inputs" USING btree (parent_id); | |
| CREATE INDEX "index_inputs_on_site_id" ON "public"."inputs" USING btree (site_id); | |
| CREATE INDEX "index_inputs_on_user_id" ON "public"."inputs" USING btree (user_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table inputs | |
| -- ---------------------------- | |
| ALTER TABLE "public"."inputs" ADD UNIQUE ("site_id", "start_date", "end_date", "format_id"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table inputs | |
| -- ---------------------------- | |
| ALTER TABLE "public"."inputs" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table inputs_runs | |
| -- ---------------------------- | |
| CREATE UNIQUE INDEX "index_inputs_runs_on_input_id_and_run_id" ON "public"."inputs_runs" USING btree (input_id, run_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table inputs_runs | |
| -- ---------------------------- | |
| ALTER TABLE "public"."inputs_runs" ADD UNIQUE ("input_id", "run_id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table inputs_variables | |
| -- ---------------------------- | |
| CREATE UNIQUE INDEX "index_inputs_variables_on_input_id_and_variable_id" ON "public"."inputs_variables" USING btree (input_id, variable_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table inputs_variables | |
| -- ---------------------------- | |
| ALTER TABLE "public"."inputs_variables" ADD UNIQUE ("input_id", "variable_id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table likelihoods | |
| -- ---------------------------- | |
| CREATE INDEX "index_likelihoods_on_input_id" ON "public"."likelihoods" USING btree (input_id); | |
| CREATE INDEX "index_likelihoods_on_run_id" ON "public"."likelihoods" USING btree (run_id); | |
| CREATE INDEX "index_likelihoods_on_variable_id" ON "public"."likelihoods" USING btree (variable_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table likelihoods | |
| -- ---------------------------- | |
| ALTER TABLE "public"."likelihoods" ADD UNIQUE ("run_id", "variable_id", "input_id"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table likelihoods | |
| -- ---------------------------- | |
| ALTER TABLE "public"."likelihoods" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table machines | |
| -- ---------------------------- | |
| CREATE INDEX "index_machines_on_hostname" ON "public"."machines" USING btree (hostname); | |
| -- ---------------------------- | |
| -- Uniques structure for table machines | |
| -- ---------------------------- | |
| ALTER TABLE "public"."machines" ADD UNIQUE ("hostname"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table machines | |
| -- ---------------------------- | |
| ALTER TABLE "public"."machines" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table managements | |
| -- ---------------------------- | |
| CREATE INDEX "index_managements_on_citation_id" ON "public"."managements" USING btree (citation_id); | |
| CREATE INDEX "index_managements_on_user_id" ON "public"."managements" USING btree (user_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table managements | |
| -- ---------------------------- | |
| ALTER TABLE "public"."managements" ADD UNIQUE ("date", "mgmttype"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table managements | |
| -- ---------------------------- | |
| ALTER TABLE "public"."managements" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table managements_treatments | |
| -- ---------------------------- | |
| CREATE UNIQUE INDEX "index_managements_treatments_on_management_id_and_treatment_id" ON "public"."managements_treatments" USING btree (management_id, treatment_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table managements_treatments | |
| -- ---------------------------- | |
| ALTER TABLE "public"."managements_treatments" ADD UNIQUE ("treatment_id", "management_id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table methods | |
| -- ---------------------------- | |
| CREATE INDEX "index_methods_on_citation_id" ON "public"."methods" USING btree (citation_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table methods | |
| -- ---------------------------- | |
| ALTER TABLE "public"."methods" ADD UNIQUE ("name", "citation_id"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table methods | |
| -- ---------------------------- | |
| ALTER TABLE "public"."methods" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Uniques structure for table mimetypes | |
| -- ---------------------------- | |
| ALTER TABLE "public"."mimetypes" ADD UNIQUE ("type_string"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table mimetypes | |
| -- ---------------------------- | |
| ALTER TABLE "public"."mimetypes" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table models | |
| -- ---------------------------- | |
| CREATE INDEX "index_models_on_parent_id" ON "public"."models" USING btree (parent_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table models | |
| -- ---------------------------- | |
| ALTER TABLE "public"."models" ADD UNIQUE ("model_path"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table models | |
| -- ---------------------------- | |
| ALTER TABLE "public"."models" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Uniques structure for table pfts | |
| -- ---------------------------- | |
| ALTER TABLE "public"."pfts" ADD UNIQUE ("name"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table pfts | |
| -- ---------------------------- | |
| ALTER TABLE "public"."pfts" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table pfts_priors | |
| -- ---------------------------- | |
| CREATE UNIQUE INDEX "index_pfts_priors_on_pft_id_and_prior_id" ON "public"."pfts_priors" USING btree (pft_id, prior_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table pfts_priors | |
| -- ---------------------------- | |
| ALTER TABLE "public"."pfts_priors" ADD UNIQUE ("pft_id", "prior_id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table pfts_species | |
| -- ---------------------------- | |
| CREATE UNIQUE INDEX "index_pfts_species_on_pft_id_and_specie_id" ON "public"."pfts_species" USING btree (pft_id, specie_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table pfts_species | |
| -- ---------------------------- | |
| ALTER TABLE "public"."pfts_species" ADD UNIQUE ("pft_id", "specie_id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table posteriors | |
| -- ---------------------------- | |
| CREATE INDEX "index_posteriors_on_pft_id" ON "public"."posteriors" USING btree (pft_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table posteriors | |
| -- ---------------------------- | |
| ALTER TABLE "public"."posteriors" ADD UNIQUE ("pft_id", "format_id"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table posteriors | |
| -- ---------------------------- | |
| ALTER TABLE "public"."posteriors" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table posteriors_runs | |
| -- ---------------------------- | |
| CREATE UNIQUE INDEX "index_posteriors_runs_on_posterior_id_and_run_id" ON "public"."posteriors_runs" USING btree (posterior_id, run_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table posteriors_runs | |
| -- ---------------------------- | |
| ALTER TABLE "public"."posteriors_runs" ADD UNIQUE ("posterior_id", "run_id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table priors | |
| -- ---------------------------- | |
| CREATE INDEX "index_priors_on_citation_id" ON "public"."priors" USING btree (citation_id); | |
| CREATE INDEX "index_priors_on_variable_id" ON "public"."priors" USING btree (variable_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table priors | |
| -- ---------------------------- | |
| ALTER TABLE "public"."priors" ADD UNIQUE ("variable_id", "phylogeny", "notes"); | |
| ALTER TABLE "public"."priors" ADD UNIQUE ("citation_id", "variable_id", "phylogeny"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table priors | |
| -- ---------------------------- | |
| ALTER TABLE "public"."priors" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table runs | |
| -- ---------------------------- | |
| CREATE INDEX "index_runs_on_ensemble_id" ON "public"."runs" USING btree (ensemble_id); | |
| CREATE INDEX "index_runs_on_model_id" ON "public"."runs" USING btree (model_id); | |
| CREATE INDEX "index_runs_on_site_id" ON "public"."runs" USING btree (site_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table runs | |
| -- ---------------------------- | |
| ALTER TABLE "public"."runs" ADD UNIQUE ("model_id", "site_id", "start_time", "finish_time", "parameter_list", "ensemble_id"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table runs | |
| -- ---------------------------- | |
| ALTER TABLE "public"."runs" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table schema_migrations | |
| -- ---------------------------- | |
| CREATE UNIQUE INDEX "unique_schema_migrations" ON "public"."schema_migrations" USING btree (version); | |
| -- ---------------------------- | |
| -- Primary Key structure for table schema_migrations | |
| -- ---------------------------- | |
| ALTER TABLE "public"."schema_migrations" ADD PRIMARY KEY ("version"); | |
| -- ---------------------------- | |
| -- Indexes structure for table sites | |
| -- ---------------------------- | |
| CREATE INDEX "index_sites_on_user_id" ON "public"."sites" USING btree (user_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table sites | |
| -- ---------------------------- | |
| ALTER TABLE "public"."sites" ADD UNIQUE ("lat", "lon", "sitename"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table sites | |
| -- ---------------------------- | |
| ALTER TABLE "public"."sites" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Uniques structure for table species | |
| -- ---------------------------- | |
| ALTER TABLE "public"."species" ADD UNIQUE ("scientificname"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table species | |
| -- ---------------------------- | |
| ALTER TABLE "public"."species" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table traits | |
| -- ---------------------------- | |
| CREATE INDEX "index_traits_on_citation_id" ON "public"."traits" USING btree (citation_id); | |
| CREATE INDEX "index_traits_on_cultivar_id" ON "public"."traits" USING btree (cultivar_id); | |
| CREATE INDEX "index_traits_on_entity_id" ON "public"."traits" USING btree (entity_id); | |
| CREATE INDEX "index_traits_on_method_id" ON "public"."traits" USING btree (method_id); | |
| CREATE INDEX "index_traits_on_site_id" ON "public"."traits" USING btree (site_id); | |
| CREATE INDEX "index_traits_on_specie_id" ON "public"."traits" USING btree (specie_id); | |
| CREATE INDEX "index_traits_on_treatment_id" ON "public"."traits" USING btree (treatment_id); | |
| CREATE INDEX "index_traits_on_user_id" ON "public"."traits" USING btree (user_id); | |
| CREATE INDEX "index_traits_on_variable_id" ON "public"."traits" USING btree (variable_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table traits | |
| -- ---------------------------- | |
| ALTER TABLE "public"."traits" ADD UNIQUE ("site_id", "specie_id", "citation_id", "cultivar_id", "treatment_id", "date", "time", "variable_id", "entity_id", "method_id", "date_year", "date_month", "date_day", "time_hour", "time_minute"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table traits | |
| -- ---------------------------- | |
| ALTER TABLE "public"."traits" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table treatments | |
| -- ---------------------------- | |
| CREATE INDEX "index_treatments_on_user_id" ON "public"."treatments" USING btree (user_id); | |
| -- ---------------------------- | |
| -- Primary Key structure for table treatments | |
| -- ---------------------------- | |
| ALTER TABLE "public"."treatments" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table users | |
| -- ---------------------------- | |
| CREATE UNIQUE INDEX "index_users_on_login" ON "public"."users" USING btree (login); | |
| -- ---------------------------- | |
| -- Uniques structure for table users | |
| -- ---------------------------- | |
| ALTER TABLE "public"."users" ADD UNIQUE ("login"); | |
| ALTER TABLE "public"."users" ADD UNIQUE ("email"); | |
| ALTER TABLE "public"."users" ADD UNIQUE ("crypted_password"); | |
| ALTER TABLE "public"."users" ADD UNIQUE ("salt"); | |
| ALTER TABLE "public"."users" ADD UNIQUE ("apikey"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table users | |
| -- ---------------------------- | |
| ALTER TABLE "public"."users" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Uniques structure for table variables | |
| -- ---------------------------- | |
| ALTER TABLE "public"."variables" ADD UNIQUE ("name"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table variables | |
| -- ---------------------------- | |
| ALTER TABLE "public"."variables" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Uniques structure for table workflows | |
| -- ---------------------------- | |
| ALTER TABLE "public"."workflows" ADD UNIQUE ("site_id", "model_id", "params", "advanced_edit", "start_date", "end_date"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table workflows | |
| -- ---------------------------- | |
| ALTER TABLE "public"."workflows" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Indexes structure for table yields | |
| -- ---------------------------- | |
| CREATE INDEX "index_yields_on_citation_id" ON "public"."yields" USING btree (citation_id); | |
| CREATE INDEX "index_yields_on_cultivar_id" ON "public"."yields" USING btree (cultivar_id); | |
| CREATE INDEX "index_yields_on_method_id" ON "public"."yields" USING btree (method_id); | |
| CREATE INDEX "index_yields_on_site_id" ON "public"."yields" USING btree (site_id); | |
| CREATE INDEX "index_yields_on_specie_id" ON "public"."yields" USING btree (specie_id); | |
| CREATE INDEX "index_yields_on_treatment_id" ON "public"."yields" USING btree (treatment_id); | |
| CREATE INDEX "index_yields_on_user_id" ON "public"."yields" USING btree (user_id); | |
| -- ---------------------------- | |
| -- Uniques structure for table yields | |
| -- ---------------------------- | |
| ALTER TABLE "public"."yields" ADD UNIQUE ("citation_id", "site_id", "specie_id", "treatment_id", "cultivar_id", "method_id", "entity_id", "date_year", "date_month", "date_day"); | |
| -- ---------------------------- | |
| -- Primary Key structure for table yields | |
| -- ---------------------------- | |
| ALTER TABLE "public"."yields" ADD PRIMARY KEY ("id"); | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."citations" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."citations" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."citations_sites" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."citations_sites" ADD FOREIGN KEY ("site_id") REFERENCES "public"."sites" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."citations_sites" ADD FOREIGN KEY ("citation_id") REFERENCES "public"."citations" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."citations_treatments" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."citations_treatments" ADD FOREIGN KEY ("citation_id") REFERENCES "public"."citations" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."citations_treatments" ADD FOREIGN KEY ("treatment_id") REFERENCES "public"."treatments" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."covariates" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."covariates" ADD FOREIGN KEY ("trait_id") REFERENCES "public"."traits" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."covariates" ADD FOREIGN KEY ("variable_id") REFERENCES "public"."variables" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."cultivars" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."cultivars" ADD FOREIGN KEY ("specie_id") REFERENCES "public"."species" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."dbfiles" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."dbfiles" ADD FOREIGN KEY ("machine_id") REFERENCES "public"."machines" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."dbfiles" ADD FOREIGN KEY ("updated_user_id") REFERENCES "public"."users" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."dbfiles" ADD FOREIGN KEY ("created_user_id") REFERENCES "public"."users" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."ensembles" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."ensembles" ADD FOREIGN KEY ("workflow_id") REFERENCES "public"."workflows" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."entities" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."entities" ADD FOREIGN KEY ("parent_id") REFERENCES "public"."entities" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."formats" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."formats" ADD FOREIGN KEY ("mimetype_id") REFERENCES "public"."mimetypes" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."formats_variables" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."formats_variables" ADD FOREIGN KEY ("variable_id") REFERENCES "public"."variables" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."formats_variables" ADD FOREIGN KEY ("format_id") REFERENCES "public"."formats" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."inputs" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."inputs" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."inputs" ADD FOREIGN KEY ("site_id") REFERENCES "public"."sites" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."inputs" ADD FOREIGN KEY ("parent_id") REFERENCES "public"."inputs" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."inputs" ADD FOREIGN KEY ("format_id") REFERENCES "public"."formats" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."inputs_runs" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."inputs_runs" ADD FOREIGN KEY ("input_id") REFERENCES "public"."inputs" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."inputs_runs" ADD FOREIGN KEY ("run_id") REFERENCES "public"."runs" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."inputs_variables" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."inputs_variables" ADD FOREIGN KEY ("variable_id") REFERENCES "public"."variables" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."inputs_variables" ADD FOREIGN KEY ("input_id") REFERENCES "public"."inputs" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."likelihoods" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."likelihoods" ADD FOREIGN KEY ("input_id") REFERENCES "public"."inputs" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."likelihoods" ADD FOREIGN KEY ("run_id") REFERENCES "public"."runs" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."likelihoods" ADD FOREIGN KEY ("variable_id") REFERENCES "public"."variables" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."managements" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."managements" ADD FOREIGN KEY ("citation_id") REFERENCES "public"."citations" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."managements_treatments" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."managements_treatments" ADD FOREIGN KEY ("treatment_id") REFERENCES "public"."treatments" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."managements_treatments" ADD FOREIGN KEY ("management_id") REFERENCES "public"."managements" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."models" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."models" ADD FOREIGN KEY ("parent_id") REFERENCES "public"."models" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."pfts_priors" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."pfts_priors" ADD FOREIGN KEY ("prior_id") REFERENCES "public"."priors" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."pfts_priors" ADD FOREIGN KEY ("pft_id") REFERENCES "public"."pfts" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."pfts_species" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."pfts_species" ADD FOREIGN KEY ("pft_id") REFERENCES "public"."pfts" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."pfts_species" ADD FOREIGN KEY ("specie_id") REFERENCES "public"."species" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."posteriors" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."posteriors" ADD FOREIGN KEY ("format_id") REFERENCES "public"."formats" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."posteriors" ADD FOREIGN KEY ("pft_id") REFERENCES "public"."pfts" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."posteriors_runs" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."posteriors_runs" ADD FOREIGN KEY ("run_id") REFERENCES "public"."runs" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."posteriors_runs" ADD FOREIGN KEY ("posterior_id") REFERENCES "public"."posteriors" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."priors" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."priors" ADD FOREIGN KEY ("variable_id") REFERENCES "public"."variables" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."priors" ADD FOREIGN KEY ("citation_id") REFERENCES "public"."citations" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."runs" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."runs" ADD FOREIGN KEY ("site_id") REFERENCES "public"."sites" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."runs" ADD FOREIGN KEY ("model_id") REFERENCES "public"."models" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."runs" ADD FOREIGN KEY ("ensemble_id") REFERENCES "public"."ensembles" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."sites" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."sites" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."traits" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."traits" ADD FOREIGN KEY ("treatment_id") REFERENCES "public"."treatments" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."traits" ADD FOREIGN KEY ("site_id") REFERENCES "public"."sites" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."traits" ADD FOREIGN KEY ("citation_id") REFERENCES "public"."citations" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."traits" ADD FOREIGN KEY ("cultivar_id") REFERENCES "public"."cultivars" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."traits" ADD FOREIGN KEY ("specie_id") REFERENCES "public"."species" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."traits" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."traits" ADD FOREIGN KEY ("entity_id") REFERENCES "public"."entities" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."traits" ADD FOREIGN KEY ("method_id") REFERENCES "public"."methods" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."traits" ADD FOREIGN KEY ("variable_id") REFERENCES "public"."variables" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."treatments" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."treatments" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."workflows" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."workflows" ADD FOREIGN KEY ("model_id") REFERENCES "public"."models" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."workflows" ADD FOREIGN KEY ("site_id") REFERENCES "public"."sites" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- ---------------------------- | |
| -- Foreign Key structure for table "public"."yields" | |
| -- ---------------------------- | |
| ALTER TABLE "public"."yields" ADD FOREIGN KEY ("specie_id") REFERENCES "public"."species" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."yields" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."yields" ADD FOREIGN KEY ("entity_id") REFERENCES "public"."entities" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."yields" ADD FOREIGN KEY ("method_id") REFERENCES "public"."methods" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."yields" ADD FOREIGN KEY ("cultivar_id") REFERENCES "public"."cultivars" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."yields" ADD FOREIGN KEY ("site_id") REFERENCES "public"."sites" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."yields" ADD FOREIGN KEY ("citation_id") REFERENCES "public"."citations" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| ALTER TABLE "public"."yields" ADD FOREIGN KEY ("treatment_id") REFERENCES "public"."treatments" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment