
/* ============================================================================ */
/*   Nom de l'applicatoin	:	HYDROPHONES										*/
/*   Version				:	1.0												*/
/*   Nom du fichier			:	BDhydrophones.sql								*/
/*   Date de création		:	14/11/2013 										*/
/*   Copyright				:	Nicolas GABARRON								*/
/*   Description			:	Script de création de la base de données		*/
/* ============================================================================ */


-- CREATE DATABASE HYDROPHONES ;

-- ============================================================
--   Table : PAYS
-- ============================================================
CREATE TABLE pays
(
	py_id		INTEGER			NOT NULL			,
	py_name		VARCHAR(30)		NOT NULL	UNIQUE	,
	py_code2	VARCHAR(2)		NOT NULL	UNIQUE	,
	py_code3	VARCHAR(3)		NOT NULL	UNIQUE	,
	CONSTRAINT PK_PAYS PRIMARY KEY (py_id)
);
CREATE UNIQUE INDEX IDX_PAYS ON pays (py_id ASC);


-- ============================================================
--   Table : PORTS
-- ============================================================
CREATE TABLE ports
(
	prt_id		INTEGER			NOT NULL			,
	prt_name	VARCHAR(50)		NOT NULL	UNIQUE	,
	prt_state	INTEGER			NOT NULL			,
	CONSTRAINT PK_PORT PRIMARY KEY (prt_id)
);
CREATE UNIQUE INDEX IDX_PORT ON ports (prt_id ASC);


-- ============================================================
--   Table : NAVIRES
-- ============================================================
CREATE TABLE navires
(
	nav_id			INTEGER			NOT NULL			,
	nav_name		VARCHAR(50)		NOT NULL	UNIQUE	,
	nav_affret		VARCHAR(30)							,
	CONSTRAINT PK_NAVIRE PRIMARY KEY (nav_id)
);
CREATE UNIQUE INDEX IDX_NAVIRE ON navires (nav_id ASC);


-- ============================================================
--   Table : PERSONNES
-- ============================================================
CREATE TABLE personnes
(
	prs_id			INTEGER			NOT NULL,
	prs_fname		VARCHAR(30)		NOT NULL,
	prs_lname		VARCHAR(30)		NOT NULL,
	prs_mail		VARCHAR(50)				,
	prs_phone		VARCHAR(20)				,
	prs_lab			VARCHAR(100)	NOT NULL,
	prs_address		VARCHAR(255)			,
	CONSTRAINT PK_PERSONNE PRIMARY KEY (prs_id)
);
CREATE UNIQUE INDEX IDX_PERSONNE ON personnes (prs_id ASC);
CREATE UNIQUE INDEX IDX_PERSONNE2 ON personnes (prs_fname, prs_lname ASC);


-- ============================================================
--   Table : CAMPAGNES
-- ============================================================
CREATE TABLE campagnes
(
	ca_id		INTEGER			NOT NULL			,
	ca_name		VARCHAR(30)		NOT NULL	UNIQUE	,
	ca_begin	DATE			NOT NULL			,
	ca_end		DATE			NOT NULL			,
	ca_navire	INTEGER			NOT NULL			,
	ca_start	INTEGER			NOT NULL			,
	ca_stop		INTEGER			NOT NULL			,
	ca_chef		INTEGER			NOT NULL			,
	ca_resp		INTEGER								,
	CONSTRAINT PK_CAMPAGNE PRIMARY KEY (ca_id)		,
	CONSTRAINT CHK_DATE CHECK (ca_begin<=ca_end)
);
CREATE UNIQUE INDEX IDX_CAMPAGNE ON campagnes (ca_id ASC);


-- ============================================================
--   Table : ROLES
-- ============================================================
CREATE TABLE roles
(
	rol_id			INTEGER			NOT NULL			,
	rol_libelle		VARCHAR(50)		NOT NULL	UNIQUE	,
	CONSTRAINT PK_ROLE PRIMARY KEY (rol_id)
);
CREATE UNIQUE INDEX IDX_ROLE ON roles (rol_id);


-- ============================================================
--   Table : CA_PERSONNES
-- ============================================================
CREATE TABLE ca_personnes
(
	cp_campagne		INTEGER		NOT NULL,
	cp_personne		INTEGER		NOT NULL,
	cp_role			INTEGER		NOT NULL,
	CONSTRAINT PK_CA_PERSONNE PRIMARY KEY (cp_campagne, cp_personne, cp_role)
);
CREATE UNIQUE INDEX IDX_CA_PERSONNE ON ca_personnes (cp_campagne, cp_personne, cp_role);


-- ============================================================
--   Table : RESEAUX
-- ============================================================
CREATE TABLE reseaux
(
	rso_id			INTEGER			NOT NULL			,
	rso_name		VARCHAR(30)		NOT NULL	UNIQUE	,
	CONSTRAINT PK_RESEAU PRIMARY KEY (rso_id)
);
CREATE UNIQUE INDEX IDX_RESEAU ON reseaux (rso_id ASC);


-- ============================================================
--   Table : DEPLOIEMENTS
-- ============================================================
CREATE TABLE deploiements
(
	dpl_id			INTEGER			NOT NULL,
	dpl_hydro		VARCHAR(10)		NOT NULL,
	dpl_begin		DATE			NOT NULL,
	dpl_end			DATE			NOT NULL,
	dpl_cm			INTEGER			NOT NULL,
	dpl_cr			INTEGER			NOT NULL,
	dpl_reseau		INTEGER			NOT NULL,
	dpl_tdn			INTEGER			NOT NULL,
	dpl_mfd			INTEGER			NOT NULL,
	dpl_dely		INTEGER			NOT NULL,
	dpl_shd			INTEGER			NOT NULL,
	dpl_sfn			INTEGER			NOT NULL,
	dpl_nrs			INTEGER			NOT NULL,
	dpl_smp			INTEGER			NOT NULL,
	dpl_gain		INTEGER			NOT NULL,
	dpl_fil			INTEGER			NOT NULL,
	dpl_depth		INTEGER			NOT NULL,
	dpl_delay		INTEGER			NOT NULL,
	dpl_nberr		INTEGER			NOT NULL,
	dpl_ldisk		INTEGER			NOT NULL,
	dpl_fldisk		INTEGER			NOT NULL,
	dpl_sensitiv1	FLOAT			NOT NULL,
	dpl_sensitiv2	FLOAT			NOT NULL,
	CONSTRAINT PK_DEPLOIEMENT PRIMARY KEY (dpl_id),
	CONSTRAINT CHK_LDISK CHECK (dpl_ldisk<=dpl_tdn),
	CONSTRAINT CHK_DATE CHECK (dpl_begin<=dpl_end)
);
CREATE UNIQUE INDEX IDX_DEPLOIEMENT ON deploiements (dpl_id ASC);
CREATE UNIQUE INDEX IDX_DEPLOIEMENT2 ON deploiements (dpl_hydro, dpl_begin, dpl_end);

-- Ajout de la colonne géographique
SELECT AddGeometryColumn('', 'public', 'deploiements', 'dpl_geo', 4326, 'POINT', 2 );


-- ============================================================
--   Table : SEISMES
-- ============================================================

CREATE TABLE seismes
(
	ssm_id			INTEGER		NOT NULL,
	ssm_nbhyd		INTEGER		NOT NULL,
	ssm_date		TIMESTAMP	NOT NULL,
	ssm_dlat		FLOAT		NOT NULL,
	ssm_dlon		FLOAT		NOT NULL,
	ssm_ddate		FLOAT		NOT NULL,
	ssm_ampli		FLOAT		NOT NULL,
	ssm_dampli		FLOAT		NOT NULL,
	ssm_reseau		INTEGER		NOT NULL,
	CONSTRAINT PK_SEISME PRIMARY KEY (ssm_id)
);
CREATE UNIQUE INDEX IDX_SEISME ON seismes (ssm_id ASC);

-- Ajout de la colonne géographique
SELECT AddGeometryColumn('', 'public', 'seismes', 'ssm_geo', 4326, 'POINT', 2 );


-- ============================================================
--   Table : SEISME_DPL
-- ============================================================

CREATE TABLE seisme_dpl
(
	sh_seisme	INTEGER		NOT NULL,
	sh_dpl		INTEGER		NOT NULL,
	CONSTRAINT PK_SEISME_DPL PRIMARY KEY (sh_seisme, sh_dpl)
);
CREATE UNIQUE INDEX IDX_SEISME_DPL ON seisme_dpl (sh_seisme, sh_dpl);


-- ============================================================
--   Table : FICHIERS
-- ============================================================

CREATE TABLE fichiers
(
	fic_id		INTEGER			NOT NULL,
	fic_type	INTEGER			NOT NULL,
	fic_nom		VARCHAR(50)		NOT NULL,
	CONSTRAINT PK_FICHIERS PRIMARY KEY (fic_id)
);
CREATE UNIQUE INDEX IDX_FICHIERS_ID ON fichiers (fic_id);
CREATE UNIQUE INDEX IDX_FICHIERS_NOM ON fichiers (fic_nom);


-- ============================================================
-- les clés étrangéres
-- ============================================================

ALTER TABLE campagnes
	ADD CONSTRAINT FK_CAMPAGNE_NAVIRE FOREIGN KEY (ca_navire)
		REFERENCES navires (nav_id)
		ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE campagnes
	ADD CONSTRAINT FK_CAMPAGNE_PORT1 FOREIGN KEY (ca_start)
		REFERENCES ports (prt_id)
		ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE campagnes
	ADD CONSTRAINT FK_CAMPAGNE_PORT2 FOREIGN KEY (ca_stop)
		REFERENCES ports (prt_id)
		ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE ca_personnes
	ADD CONSTRAINT FK_CP_CAMPAGNE FOREIGN KEY (cp_campagne)
		REFERENCES campagnes (ca_id)
		ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE ca_personnes
	ADD CONSTRAINT FK_CP_PERSONNE FOREIGN KEY (cp_personne)
		REFERENCES personnes (prs_id)
		ON DELETE CASCADE ON UPDATE CASCADE;
		
ALTER TABLE ca_personnes
	ADD CONSTRAINT FK_CP_ROLE FOREIGN KEY (cp_role)
		REFERENCES roles (rol_id)
		ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE deploiements
	ADD CONSTRAINT FK_DEPL_RESEAU FOREIGN KEY (dpl_reseau)
		REFERENCES reseaux (rso_id)
		ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE deploiements
	ADD CONSTRAINT FK_DEPL_MOUIL FOREIGN KEY (dpl_cm)
		REFERENCES campagnes (ca_id)
		ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE deploiements
	ADD CONSTRAINT FK_DEPL_RECUP FOREIGN KEY (dpl_cr)
		REFERENCES campagnes (ca_id)
		ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE seismes
	ADD CONSTRAINT FK_SEISME_RESEAU FOREIGN KEY (ssm_reseau)
		REFERENCES reseaux (rso_id)
		ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE seisme_dpl
	ADD CONSTRAINT FK_SH_SEISME FOREIGN KEY (sh_seisme)
		REFERENCES seismes (ssm_id)
		ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE seisme_dpl
	ADD CONSTRAINT FK_SH_DPL FOREIGN KEY (sh_dpl)
		REFERENCES deploiements (dpl_id)
		ON DELETE CASCADE ON UPDATE CASCADE;
		


GRANT ALL PRIVILEGES ON pays TO seismesadm;
GRANT ALL PRIVILEGES ON ports TO seismesadm;
GRANT ALL PRIVILEGES ON navires TO seismesadm;
GRANT ALL PRIVILEGES ON personnes TO seismesadm;
GRANT ALL PRIVILEGES ON campagnes TO seismesadm;
GRANT ALL PRIVILEGES ON roles TO seismesadm;
GRANT ALL PRIVILEGES ON ca_personnes TO seismesadm;
GRANT ALL PRIVILEGES ON reseaux TO seismesadm;
GRANT ALL PRIVILEGES ON deploiements TO seismesadm;
GRANT ALL PRIVILEGES ON seismes TO seismesadm;
GRANT ALL PRIVILEGES ON seisme_dpl TO seismesadm;


GRANT SELECT ON pays TO seismesweb;
GRANT SELECT ON ports TO seismesweb;
GRANT SELECT ON navires TO seismesweb;
GRANT SELECT ON personnes TO seismesweb;
GRANT SELECT ON campagnes TO seismesweb;
GRANT SELECT ON roles TO seismesweb;
GRANT SELECT ON ca_personnes TO seismesweb;
GRANT SELECT ON reseaux TO seismesweb;
GRANT SELECT ON deploiements TO seismesweb;
GRANT SELECT ON seismes TO seismesweb;
GRANT SELECT ON seisme_dpl TO seismesweb;


-- ============================================================
-- Les données de référence
-- ============================================================

/*INSERT INTO pays (py_id, py_code2, py_code3, py_name) VALUES (250, 'FR', 'FRA', 'France');
INSERT INTO pays (py_id, py_code2, py_code3, py_name) VALUES (724, 'ES', 'ESP', 'Espagne');
INSERT INTO pays (py_id, py_code2, py_code3, py_name) VALUES (620, 'PT', 'PRT', 'Portugal');
INSERT INTO pays (py_id, py_code2, py_code3, py_name) VALUES (826, 'GB', 'GBR', 'Royaume-Uni');
INSERT INTO pays (py_id, py_code2, py_code3, py_name) VALUES (840, 'US', 'USA', 'Etats-Unis');

INSERT INTO ports (prt_id, prt_state, prt_name) VALUES (1, 250, 'Brest');
INSERT INTO ports (prt_id, prt_state, prt_name) VALUES (2, 250, 'Toulon');
INSERT INTO ports (prt_id, prt_state, prt_name) VALUES (3, 250, 'Concarneau');
INSERT INTO ports (prt_id, prt_state, prt_name) VALUES (4, 620, 'Ponta Delgada');
INSERT INTO ports (prt_id, prt_state, prt_name) VALUES (5, 826, 'Govan');
INSERT INTO ports (prt_id, prt_state, prt_name) VALUES (6, 620, 'Horta');
INSERT INTO ports (prt_id, prt_state, prt_name) VALUES (7, 840, 'Miami, FL');

INSERT INTO navires (nav_id, nav_name) VALUES (1, 'Pourquoi-pas?');
INSERT INTO navires (nav_id, nav_name) VALUES (2, 'Thalassa');
INSERT INTO navires (nav_id, nav_name) VALUES (3, 'L''Atalante');
INSERT INTO navires (nav_id, nav_name) VALUES (4, 'Le Suroît');
INSERT INTO navires (nav_id, nav_name) VALUES (5, 'RRS Discovery');
INSERT INTO navires (nav_id, nav_name) VALUES (6, 'R/V Knorr');
INSERT INTO navires (nav_id, nav_name) VALUES (7, 'M/V Arquipelago');
INSERT INTO navires (nav_id, nav_name) VALUES (8, 'NRP Almirante Gago Coutinho');
*/


-- ============================================================
-- Jeu de test
-- ============================================================
/*
INSERT INTO personnes (prs_id, prs_name, prs_surname, prs_mail, prs_phone, prs_lab, prs_address) VALUES (1, 'GOSLIN', 'Jean', 'mail JG', 'phone JG', 'lab JG', 'adr JG');
INSERT INTO campagnes (ca_id, ca_name, ca_begin, ca_end, ca_navire, ca_start, ca_stop, ca_chef, ca_resp) VALUES (1, 'MOMAR', CAST('2008-08-12' AS DATE), CAST('2008-08-19' AS DATE), 1, 1, 1, 1, 1);
INSERT INTO campagnes (ca_id, ca_name, ca_begin, ca_end, ca_navire, ca_start, ca_stop, ca_chef, ca_resp) VALUES (2, 'MOMAR2', CAST('2009-08-12' AS DATE), CAST('2009-08-19' AS DATE), 1, 1, 1, 1, 1);
INSERT INTO reseaux (rso_id, rso_name) VALUES (1, 'MARCHE1');
INSERT INTO reseaux (rso_id, rso_name) VALUES (2, 'SIRENA');
*/