source: ether_statistics/persistence/sql/vmipsl_createSimulation.sql @ 569

-- ----------------------------
-- Table structure for "simulation"
-- ----------------------------
DROP TABLE "simulation";
CREATE TABLE "public"."simulation" (
"id" int4 DEFAULT NULL NOT NULL,
"location" varchar(1000) DEFAULT NULL,
"xmlnsheader" varchar(1000) DEFAULT NULL,
CONSTRAINT "simulation_pkey" PRIMARY KEY ("id")
)
WITH (OIDS=FALSE);

ALTER TABLE "public"."simulation" OWNER TO "postgres";;

-- ----------------------------
-- Table structure for "simulation_attribute"
-- ----------------------------
DROP TABLE "simulation_attribute";
CREATE TABLE "public"."simulation_attribute" (
"id" int4 DEFAULT NULL NOT NULL,
"name" varchar(100) DEFAULT NULL::character varying,
"value" varchar(1000) DEFAULT NULL::character varying,
CONSTRAINT "simulation_attribute_copy_pkey" PRIMARY KEY ("id")
)
WITH (OIDS=FALSE);

ALTER TABLE "public"."simulation_attribute" OWNER TO "postgres";;

-- ----------------------------
-- Table structure for "simulation_dimension"
-- ----------------------------
DROP TABLE "simulation_dimension";
CREATE TABLE "public"."simulation_dimension" (
"id" int4 DEFAULT NULL NOT NULL,
"name" varchar(100) DEFAULT NULL::character varying NOT NULL,
"length" varchar(40) DEFAULT NULL::character varying NOT NULL,
"isunlimited" bool DEFAULT NULL,
CONSTRAINT "simulation_dimension_copy_pkey" PRIMARY KEY ("id")
)
WITH (OIDS=FALSE);

ALTER TABLE "public"."simulation_dimension" OWNER TO "postgres";;

-- ----------------------------
-- Table structure for "simulation_variable"
-- ----------------------------
DROP TABLE "simulation_variable";
CREATE TABLE "public"."simulation_variable" (
"id" int4 DEFAULT NULL NOT NULL,
"name" varchar(40) DEFAULT NULL::character varying NOT NULL,
"shape" varchar(100) DEFAULT NULL::character varying,
"type" varchar(40) DEFAULT NULL::character varying,
"units" varchar(40) DEFAULT NULL::character varying,
"long_name" varchar(100) DEFAULT NULL::character varying,
CONSTRAINT "simulation_variable_copy_pkey" PRIMARY KEY ("id")
)
WITH (OIDS=FALSE);

ALTER TABLE "public"."simulation_variable" OWNER TO "postgres";;

-- ----------------------------
-- Table structure for "rel_simulation_attribute"
-- ----------------------------
DROP TABLE "rel_simulation_attribute";
CREATE TABLE "public"."rel_simulation_attribute" (
"simulation_id" int4 DEFAULT NULL NOT NULL,
"attribute_id" int4 DEFAULT NULL NOT NULL,
CONSTRAINT "rel_simulation_attribute_pkey" PRIMARY KEY ("simulation_id", "attribute_id"),
CONSTRAINT "rel_simulation_attribute_simulation_id_fkey" FOREIGN KEY ("simulation_id") REFERENCES "public"."simulation" ("id") ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT "rel_simulation_attribute_attribute_id_fkey" FOREIGN KEY ("attribute_id") REFERENCES "public"."simulation_attribute" ("id") ON DELETE CASCADE ON UPDATE NO ACTION
)
WITH (OIDS=FALSE);

ALTER TABLE "public"."rel_simulation_attribute" OWNER TO "postgres";

CREATE INDEX "ind_rel_simulation_attribute_attributekey" ON "public"."rel_simulation_attribute" USING btree ("attribute_id");
CREATE INDEX "ind_rel_simulation_attribute_simulationkey" ON "public"."rel_simulation_attribute" USING btree ("simulation_id");;


-- ----------------------------
-- Table structure for "rel_simulation_dimension"
-- ----------------------------
DROP TABLE "rel_simulation_dimension";
CREATE TABLE "public"."rel_simulation_dimension" (
"simulation_id" int4 DEFAULT NULL,
"dimension_id" int4 DEFAULT NULL,
CONSTRAINT "rel_simulation_dimension_simulation_id_fkey" FOREIGN KEY ("simulation_id") REFERENCES "public"."simulation" ("id") ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT "rel_simulation_dimension_dimension_id_fkey" FOREIGN KEY ("dimension_id") REFERENCES "public"."simulation_dimension" ("id") ON DELETE CASCADE ON UPDATE NO ACTION
)
WITH (OIDS=FALSE);

ALTER TABLE "public"."rel_simulation_dimension" OWNER TO "postgres";

CREATE INDEX "ind_rel_simulation_dimension_dimensionkey" ON "public"."rel_simulation_dimension" USING btree ("dimension_id");
CREATE INDEX "ind_rel_simulation_dimension_simulationkey" ON "public"."rel_simulation_dimension" USING btree ("simulation_id");;


-- ----------------------------
-- Table structure for "rel_simulation_variable"
-- ----------------------------
DROP TABLE "rel_simulation_variable";
CREATE TABLE "public"."rel_simulation_variable" (
"simulation_id" int4 DEFAULT NULL NOT NULL,
"variable_id" int4 DEFAULT NULL NOT NULL,
CONSTRAINT "rel_simulation_variable_pkey" PRIMARY KEY ("simulation_id", "variable_id"),
CONSTRAINT "rel_simulation_variable_simulation_id_fkey" FOREIGN KEY ("simulation_id") REFERENCES "public"."simulation" ("id") ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT "rel_simulation_variable_variable_id_fkey" FOREIGN KEY ("variable_id") REFERENCES "public"."simulation_variable" ("id") ON DELETE CASCADE ON UPDATE NO ACTION
)
WITH (OIDS=FALSE);

ALTER TABLE "public"."rel_simulation_variable" OWNER TO "postgres";

CREATE INDEX "ind_rel_simulation_variable_simulationkey" ON "public"."rel_simulation_variable" USING btree ("simulation_id");
CREATE INDEX "ind_rel_simulation_variable_variablekey" ON "public"."rel_simulation_variable" USING btree ("variable_id");;



/* ****************************************************************** */
/* **************** TABLES DE RELATION DE SIMULATION **************** */
/* ****************************************************************** */
/* Création des tables via navicat, puis index et clefs étrangÚres en mode commande, car gros bug via navicat */

/* REL_SIMULATION_DIMENSION */
CREATE INDEX "ind_rel_simulation_dimension_simulationkey" ON "public"."rel_simulation_dimension" ("simulation_id"  );
CREATE INDEX "ind_rel_simulation_dimension_dimensionkey" ON "public"."rel_simulation_dimension" ("dimension_id"  );
ALTER TABLE rel_simulation_dimension ADD FOREIGN KEY(simulation_id) REFERENCES simulation ON DELETE CASCADE;
ALTER TABLE rel_simulation_dimension ADD FOREIGN KEY(dimension_id) REFERENCES simulation_dimension ON DELETE CASCADE;

/* REL_SIMULATION_ATTRIBUTE */
CREATE INDEX "ind_rel_simulation_attribute_simulationkey" ON "public"."rel_simulation_attribute" ("simulation_id"  );
CREATE INDEX "ind_rel_simulation_attribute_attributekey" ON "public"."rel_simulation_attribute" ("attribute_id"  );
ALTER TABLE rel_simulation_attribute ADD FOREIGN KEY(simulation_id) REFERENCES simulation ON DELETE CASCADE;
ALTER TABLE rel_simulation_attribute ADD FOREIGN KEY(attribute_id) REFERENCES simulation_attribute ON DELETE CASCADE;

/* REL_SIMULATION_VARIABLE */
CREATE INDEX "ind_rel_simulation_variable_simulationkey" ON "public"."rel_simulation_variable" ("simulation_id"  );
CREATE INDEX "ind_rel_simulation_variable_variablekey" ON "public"."rel_simulation_variable" ("variable_id"  );
ALTER TABLE rel_simulation_variable ADD FOREIGN KEY(simulation_id) REFERENCES simulation ON DELETE CASCADE;
ALTER TABLE rel_simulation_variable ADD FOREIGN KEY(variable_id) REFERENCES simulation_variable ON DELETE CASCADE;