|
|
|
/* ============================================================================ */
|
|
/* 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;
|
|
|
|
|
|
-- ============================================================
|
|
-- Création des rôles et affectation des droits
|
|
-- ============================================================
|
|
|
|
CREATE USER seismesadm WITH PASSWORD 'ssmadm';
|
|
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;
|
|
|
|
CREATE USER seismesweb WITH PASSWORD 'ssmweb';
|
|
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');
|
|
*/
|