Projet

Général

Profil

Télécharger (23,6 ko)

Publication de fichiers » MARBEN3.2.sql

Nicolas GABARRON, 11/10/2012 10:05

 

-- ================================================================ --
-- ================================================================ --
-- Nom de la base : MARBEN 3.2 --
-- Date de création : 28/09/2012 --
-- Copyright : Nicolas GABARRON --
-- SGBD : PostgreSQL / PostGIS --
-- Description : Script de création de la base de données --
-- ================================================================ --
-- ================================================================ --



-- CREATE DATABASE MARBEN

-- ============================================================ --
-- ============================================================ --
-- --
-- DONNEES SPATIALES --
-- --
-- ============================================================ --
-- ============================================================ --


-- Table : SECTOR
-- (Liste des secteur Rebent (et uniquement Rebent))

CREATE TABLE sector
(
sct_id SERIAL NOT NULL,
sct_order INTEGER NOT NULL,
sct_name VARCHAR(50) NOT NULL,
sct_comment VARCHAR(500) ,
CONSTRAINT pk_sector PRIMARY KEY (sct_id)
);
CREATE UNIQUE INDEX idx_sector_id ON sector (sct_id ASC);
CREATE UNIQUE INDEX idx_sector_name ON sector (sct_name ASC);


-- Table : SITE
-- (Liste des sites étudiés)

CREATE TABLE site
(
st_id SERIAL NOT NULL ,
st_name VARCHAR(50) NOT NULL ,
st_actif BOOLEAN NOT NULL DEFAULT TRUE,
st_sector INTEGER ,
st_comment VARCHAR(500) ,
CONSTRAINT pk_site PRIMARY KEY (st_id)
);
CREATE UNIQUE INDEX idx_site_id ON site (st_id ASC);
CREATE UNIQUE INDEX idx_site_name ON site (st_name ASC);


-- Table : POINT
-- (Liste des points étudiés)

CREATE TABLE point
(
pt_id SERIAL NOT NULL ,
pt_name VARCHAR(50) NOT NULL ,
pt_actif BOOLEAN NOT NULL DEFAULT TRUE,
pt_x VARCHAR(15) ,
pt_y VARCHAR(15) ,
pt_site INTEGER ,
pt_comment VARCHAR(500) ,
CONSTRAINT pk_point PRIMARY KEY (pt_id)
);
CREATE UNIQUE INDEX idx_point_id ON point (pt_id ASC);
CREATE UNIQUE INDEX idx_point_name ON point (pt_name ASC);
-- CREATE UNIQUE INDEX idx_point_geo ON point (pt_x, pt_y);
-- obligé de virer cette index unique car plusieurs point ont les même coordonnées (Ex Meaben maerl 1, 2, 3

SELECT AddGeometryColumn('', 'public', 'point', 'pt_geo', 4326, 'POINT', 2 );



-- ============================================================ --
-- ============================================================ --
-- --
-- SOURCES DE DONNEES (STRATEGIE SPATIALE) --
-- --
-- ============================================================ --
-- ============================================================ --


-- Table : THEME
-- (Liste des thématiques d'études (bancs de maërl, herbiers, ...)

CREATE TABLE theme
(
thm_code VARCHAR(2) NOT NULL,
thm_name VARCHAR(50) NOT NULL,
thm_comment VARCHAR(500) ,
CONSTRAINT pk_theme PRIMARY KEY (thm_code)
);
CREATE UNIQUE INDEX idx_theme_code ON theme (thm_code ASC);
CREATE UNIQUE INDEX idx_theme_name ON theme (thm_name ASC);


-- Table : PERSON
-- (Liste des personnes concernées par les programmes de recherche, que ce soit
-- en tant que responsable ou en tant que participant aux campagnes d'échantillonnage)

CREATE TABLE person
(
prs_id SERIAL NOT NULL,
prs_name VARCHAR(20) NOT NULL,
prs_surname VARCHAR(20) NOT NULL,
prs_organism VARCHAR(50) NOT NULL,
prs_status VARCHAR(30) NOT NULL,
prs_missions VARCHAR(150) ,
prs_address VARCHAR(100) ,
prs_cp VARCHAR(5) ,
prs_town VARCHAR(30) ,
prs_phone VARCHAR(14) ,
prs_mail VARCHAR(50) ,
prs_comment VARCHAR(500) ,
CONSTRAINT pk_person PRIMARY KEY (prs_id)
);
CREATE UNIQUE INDEX idx_person_id ON person (prs_id ASC);


-- Table : SOURCE
-- (Liste des programmes de recherche dont sont issues les données)

CREATE TABLE data_source
(
src_id SERIAL NOT NULL,
src_name VARCHAR(75) NOT NULL,
src_initials VARCHAR(10) ,
src_desc VARCHAR(500) ,
src_date DATE ,
src_resp INTEGER ,
src_comment VARCHAR(500) ,
CONSTRAINT pk_data_source PRIMARY KEY (src_id)
);
CREATE UNIQUE INDEX idx_source_id ON data_source (src_id ASC);
CREATE UNIQUE INDEX idx_source_name ON data_source (src_name ASC);
CREATE UNIQUE INDEX idx_source_initials ON data_source (src_initials ASC);



-- ============================================================ --
-- ============================================================ --
-- --
-- TAXONOMIE ET REGROUPEMENTS D'ESPECES --
-- --
-- ============================================================ --
-- ============================================================ --


-- Table : FCTNAL_GR

CREATE TABLE fctnal_gr
(
fgr_id SERIAL NOT NULL,
fgr_name VARCHAR(100) NOT NULL,
fgr_desc VARCHAR(255) ,
fgr_comment VARCHAR(500) ,
CONSTRAINT pk_fctnal_gr PRIMARY KEY (fgr_id)
);
CREATE UNIQUE INDEX idx_fgr_id ON fctnal_gr (fgr_id ASC);
CREATE UNIQUE INDEX idx_fgr_name ON fctnal_gr (fgr_name ASC);


-- Table : AUTHORITY

CREATE TABLE authority
(
ath_id SERIAL NOT NULL,
ath_name VARCHAR(85) NOT NULL,
ath_comment VARCHAR(500) ,
CONSTRAINT pk_authority PRIMARY KEY (ath_id)
);
CREATE UNIQUE INDEX idx_authority_id ON authority (ath_id ASC);
CREATE UNIQUE INDEX idx_authority_name ON authority (ath_name ASC);


-- Table : TAXONOMY

CREATE TABLE taxonomy
(
sp_aphia INTEGER NOT NULL ,
sp_bg INTEGER NOT NULL UNIQUE ,
sp_bd INTEGER NOT NULL UNIQUE ,
sp_niveau INTEGER NOT NULL ,
sp_rank VARCHAR(25) NOT NULL ,
sp_lib VARCHAR(50) NOT NULL ,
sp_comment VARCHAR(500) ,
CONSTRAINT pk_taxonomy PRIMARY KEY (sp_aphia),
CONSTRAINT chk_bornes CHECK (sp_bg<sp_bd)
);
CREATE UNIQUE INDEX idx_taxonomy_id ON taxonomy (sp_aphia ASC);
CREATE UNIQUE INDEX idx_taxonomy_lib ON taxonomy (sp_rank, sp_lib);



-- Table : PHOTO_SP

CREATE TABLE photo_sp
(
psp_id SERIAL NOT NULL,
psp_name VARCHAR(50) NOT NULL,
psp_date TIMESTAMP NOT NULL,
psp_legend VARCHAR(255) NOT NULL,
psp_espece INTEGER NOT NULL,
psp_comment VARCHAR(500) ,
CONSTRAINT pk_photo_sp PRIMARY KEY (psp_id)
);
CREATE UNIQUE INDEX idx_photosp_id ON photo_sp (psp_id ASC);
CREATE UNIQUE INDEX idx_photosp_name ON photo_sp (psp_name ASC);



-- ============================================================ --
-- ============================================================ --
-- --
-- STATIONS --
-- --
-- ============================================================ --
-- ============================================================ --


-- Table : STATION
-- (Dates de sortie sur chaque point de chaque programme)
-- sta_point devrait etre NOT NULL, mais les données sont tellement merdiques
-- que toutes les stations ne sont pas géographiquement renseignées !!!!
CREATE TABLE station
(
sta_id SERIAL NOT NULL,
sta_date TIMESTAMP NOT NULL,
sta_site INTEGER ,
sta_point INTEGER ,
sta_source INTEGER NOT NULL,
sta_theme VARCHAR(2) NOT NULL,
sta_comment VARCHAR(500) ,
CONSTRAINT pk_station PRIMARY KEY (sta_id)
);
CREATE UNIQUE INDEX idx_station_id ON station (sta_id ASC);
CREATE UNIQUE INDEX idx_station ON station (sta_date, sta_site, sta_point, sta_source, sta_theme);


-- ============================================================ --
-- ============================================================ --
-- --
-- MATERIEL & METHODES --
-- --
-- ============================================================ --
-- ============================================================ --


-- Table : UNIT_TYP
-- (Liste des types d'unité (volume, métrique, masse, ...))

CREATE TABLE unit_typ
(
utyp_id SERIAL NOT NULL,
utyp_name VARCHAR(35) NOT NULL,
utyp_comment VARCHAR(500) ,
CONSTRAINT pk_unit_typ PRIMARY KEY (utyp_id)
);
CREATE UNIQUE INDEX idx_utyp_id ON unit_typ (utyp_id ASC);
CREATE UNIQUE INDEX idx_utyp_name ON unit_typ (utyp_name ASC);


-- Table : UNIT
-- (Liste des unités de mesure)

CREATE TABLE unit
(
unit_id SERIAL NOT NULL,
unit_name VARCHAR(100) NOT NULL,
unit_symb VARCHAR(20) NOT NULL,
unit_typ INTEGER NOT NULL,
unit_comment VARCHAR(500) ,
CONSTRAINT pk_unit PRIMARY KEY (unit_id)
);
CREATE UNIQUE INDEX idx_unit_id ON unit (unit_id ASC);
CREATE UNIQUE INDEX idx_unit_name ON unit (unit_name ASC);
CREATE UNIQUE INDEX idx_unit_symb ON unit (unit_symb ASC);


-- Table : SAMPLERS
-- (Liste des engins d'échantillonnage utilisables)

CREATE TABLE samplers
(
splr_id SERIAL NOT NULL,
splr_name VARCHAR(100) NOT NULL,
splr_desc VARCHAR(150) ,
splr_size NUMERIC(6,4) ,
splr_unit INTEGER ,
splr_comment VARCHAR(500) ,
CONSTRAINT pk_sampler PRIMARY KEY (splr_id)
);
CREATE UNIQUE INDEX idx_sampler_id ON samplers (splr_id ASC);
CREATE UNIQUE INDEX idx_sampler_name ON samplers (splr_name ASC);


-- Table : PHOTO_SAMPLERS
-- (Liste des photos des engins d'échantillonnage utilisables)

CREATE TABLE photo_samplers
(
psplr_id SERIAL NOT NULL,
psplr_name VARCHAR(50) NOT NULL,
psplr_date TIMESTAMP ,
psplr_legend VARCHAR(255) ,
psplr_sampler INTEGER NOT NULL,
psplr_comment VARCHAR(500) ,
CONSTRAINT pk_photo_sampler PRIMARY KEY (psplr_id)
);
CREATE UNIQUE INDEX idx_photo_samplers_id ON photo_samplers (psplr_id ASC);
CREATE UNIQUE INDEX idx_photo_samplers_name ON photo_samplers (psplr_name ASC);


-- Table : SAMPL_METH
-- (Méthodes d'échantionnage emplyées pour réaliser les prélèvements (engin utilisé,
-- nombre de coups de l'engin dans un prélèvement, ...))

CREATE TABLE sampl_meth
(
smeth_id SERIAL NOT NULL,
smeth_name VARCHAR(75) NOT NULL,
smeth_sampler INTEGER NOT NULL,
smeth_nbtime NUMERIC NOT NULL,
smeth_desc VARCHAR(255) ,
smeth_ref VARCHAR(255) ,
smeth_comment VARCHAR(500) ,
CONSTRAINT pk_sampl_meth PRIMARY KEY (smeth_id)
);
CREATE UNIQUE INDEX idx_smeth_id ON sampl_meth (smeth_id ASC);


-- Table : SAMPLE

CREATE TABLE sample
(
spl_id SERIAL NOT NULL,
spl_station INTEGER NOT NULL,
spl_smeth INTEGER NOT NULL,
spl_libelle VARCHAR(50) NOT NULL,
spl_sslib VARCHAR(50) ,
spl_comment VARCHAR(500) ,
CONSTRAINT pk_sample PRIMARY KEY (spl_id)
);
CREATE UNIQUE INDEX idx_sample_id ON sample (spl_id ASC);


-- Table : PHOTO_SAMPL
CREATE TABLE photo_sampl
(
pspl_id SERIAL NOT NULL,
pspl_name VARCHAR(50) NOT NULL,
pspl_date TIMESTAMP ,
pspl_legend VARCHAR(255) ,
pspl_sampl INTEGER NOT NULL,
pspl_comment VARCHAR(500) ,
CONSTRAINT pk_photo_sampl PRIMARY KEY (pspl_id)
);
CREATE UNIQUE INDEX idx_photo_sampl_id ON photo_sampl (pspl_id ASC);
CREATE UNIQUE INDEX idx_photo_sampl_name ON photo_sampl (pspl_name ASC);



-- ============================================================ --
-- ============================================================ --
-- --
-- RESULTATS --
-- --
-- ============================================================ --
-- ============================================================ --

-- Table : METH_ANA
-- (Liste des méthodes d'analyse des échantillons)

CREATE TABLE meth_ana
(
mna_id SERIAL NOT NULL,
mna_name VARCHAR(70) NOT NULL,
mna_desc VARCHAR(200) ,
mna_condition VARCHAR(255) ,
mna_conserv VARCHAR(255) ,
mna_prepar VARCHAR(255) ,
mna_ref VARCHAR(255) ,
mna_comment VARCHAR(500) ,
CONSTRAINT pk_meth_ana PRIMARY KEY (mna_id)
);
CREATE UNIQUE INDEX idx_meth_ana_id ON meth_ana (mna_id ASC);
CREATE UNIQUE INDEX idx_meth_ana_name ON meth_ana (mna_name ASC);


-- Table : PARAMETRES
-- (Liste des paramètres mesurables)

CREATE TABLE parametres
(
prm_id SERIAL NOT NULL,
prm_name VARCHAR(70) NOT NULL,
prm_comment VARCHAR(500) ,
CONSTRAINT pk_parametres PRIMARY KEY (prm_id)
);
CREATE UNIQUE INDEX idx_parametres_id ON parametres (prm_id ASC);
CREATE UNIQUE INDEX idx_parametres_name ON parametres (prm_name ASC);


-- Table : RESULTAT
CREATE TABLE resultat
(
res_id SERIAL NOT NULL ,
res_sample INTEGER ,
res_meth_ana INTEGER ,
res_param INTEGER ,
res_unit INTEGER ,
res_taxo VARCHAR(100) ,
res_fctnal_gr INTEGER ,
broken BOOLEAN DEFAULT FALSE ,
res_val NUMERIC ,
res_num INTEGER ,
res_comment VARCHAR(500) ,
CONSTRAINT pk_resultat PRIMARY KEY (res_id)
);
CREATE UNIQUE INDEX idx_resultat ON resultat (res_id ASC);


-- Table : demandeurs

CREATE TABLE demandeurs
(
nom VARCHAR(30) NOT NULL,
prenom VARCHAR(30) NOT NULL,
qualite VARCHAR(100) NOT NULL,
tel VARCHAR(15) NOT NULL,
email VARCHAR(50) NOT NULL,
organisme VARCHAR(100) NOT NULL,
service VARCHAR(100) NOT NULL,
responsable VARCHAR(50) NOT NULL,
adresse VARCHAR(100) NOT NULL,
cp VARCHAR(10) NOT NULL,
ville VARCHAR(30) NOT NULL,
CONSTRAINT pk_demandeur PRIMARY KEY (email)
);
-- CREATE UNIQUE INDEX IDX_DEMANDEUR ON DEMANDEURS (EMAIL ASC);


-- Table : demandes

CREATE TABLE demandes
(
email VARCHAR(50) NOT NULL,
date_dem TIMESTAMP NOT NULL,
ddebut DATE NOT NULL,
dfin DATE NOT NULL,
etat INTEGER NOT NULL,
CONSTRAINT pk_demande PRIMARY KEY (email, date_dem)
);

-- ============================================================
-- FIN DE DECLARATION DES TABLES
-- ============================================================


-- ============================================================ --
-- ============================================================ --
-- --
-- LES CLES ETRANGERES --
-- --
-- ============================================================ --
-- ============================================================ --


-- ***** Les clés entre les tables du groupe 'Données spatiales' *****

ALTER TABLE site
ADD CONSTRAINT fk_site_sector FOREIGN KEY (st_sector)
REFERENCES sector (sct_id)
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE point
ADD CONSTRAINT fk_point_site FOREIGN KEY (pt_site)
REFERENCES site (st_id)
ON DELETE CASCADE ON UPDATE CASCADE;

-- ***** Les clés entre les tables du groupe 'Source des données' *****

ALTER TABLE data_source
ADD CONSTRAINT fk_datasource_person FOREIGN KEY (src_resp)
REFERENCES person (prs_id)
ON DELETE CASCADE ON UPDATE CASCADE;
-- ***** Les clés entre les tables du groupe 'Taxonomie et regroupement d'espèces' *****

-- ALTER TABLE taxonomy
-- ADD CONSTRAINT fk_taxonomy_authority FOREIGN KEY (sp_authority)
-- REFERENCES authority (ath_id)
-- ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE photo_sp
ADD CONSTRAINT fk_photosp_espece FOREIGN KEY (psp_espece)
REFERENCES taxonomy (sp_aphia)
ON DELETE CASCADE ON UPDATE CASCADE;
-- ***** Les clés entre les tables du groupe 'Stations' *****

ALTER TABLE station
ADD CONSTRAINT fk_station_site FOREIGN KEY (sta_site)
REFERENCES site (st_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE station
ADD CONSTRAINT fk_station_point FOREIGN KEY (sta_point)
REFERENCES point (pt_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE station
ADD CONSTRAINT fk_station_source FOREIGN KEY (sta_source)
REFERENCES data_source (src_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE station
ADD CONSTRAINT fk_station_theme FOREIGN KEY (sta_theme)
REFERENCES theme (thm_code)
ON DELETE CASCADE ON UPDATE CASCADE;

-- ***** Les clés entre les tables du groupe 'Matériel & Méthodes' *****

ALTER TABLE unit
ADD CONSTRAINT fk_unit_utyp FOREIGN KEY (unit_typ)
REFERENCES unit_typ (utyp_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE samplers
ADD CONSTRAINT fk_sampler_unit FOREIGN KEY (splr_unit)
REFERENCES unit (unit_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE photo_samplers
ADD CONSTRAINT fk_sampler_photo FOREIGN KEY (psplr_sampler)
REFERENCES samplers (splr_id)
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE sampl_meth
ADD CONSTRAINT fk_samplmeth_sampler FOREIGN KEY (smeth_sampler)
REFERENCES samplers (splr_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE sample
ADD CONSTRAINT fk_sample_station FOREIGN KEY (spl_station)
REFERENCES station (sta_id)
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE sample
ADD CONSTRAINT fk_sample_samplmeth FOREIGN KEY (spl_smeth)
REFERENCES sampl_meth (smeth_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE photo_sampl
ADD CONSTRAINT fk_sample_photo FOREIGN KEY (pspl_sampl)
REFERENCES sample (spl_id)
ON DELETE CASCADE ON UPDATE CASCADE;
-- ***** Les clés entre les tables du groupe 'Résultats' *****

ALTER TABLE resultat
ADD CONSTRAINT fk_res_sample FOREIGN KEY (res_sample)
REFERENCES sample (spl_id)
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE resultat
ADD CONSTRAINT fk_res_methana FOREIGN KEY (res_meth_ana)
REFERENCES meth_ana (mna_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE resultat
ADD CONSTRAINT fk_res_param FOREIGN KEY (res_param)
REFERENCES parametres (prm_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE resultat
ADD CONSTRAINT fk_res_unit FOREIGN KEY (res_meth_ana)
REFERENCES meth_ana (mna_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE resultat
ADD CONSTRAINT fk_res_fctnalgr FOREIGN KEY (res_fctnal_gr)
REFERENCES fctnal_gr (fgr_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE demandes
ADD CONSTRAINT fk_email FOREIGN KEY (email)
REFERENCES demandeurs (email)
ON DELETE RESTRICT ON UPDATE RESTRICT;

-- ============================================================
-- ============================================================
--
-- CREATION DES ROLES ET AFFECTATION DES DROITS
--
-- ============================================================
-- ============================================================

CREATE ROLE marbenadm WITH LOGIN PASSWORD 'mbnadm';
CREATE ROLE marbenweb WITH LOGIN PASSWORD 'mbnweb';

-- marbenadm est le propriétaire des tables
ALTER TABLE geometry_columns OWNER TO marbenadm;
ALTER TABLE spatial_ref_sys OWNER TO marbenadm;

ALTER TABLE sector OWNER TO marbenadm;
ALTER TABLE site OWNER TO marbenadm;
ALTER TABLE point OWNER TO marbenadm;

ALTER TABLE theme OWNER TO marbenadm;
ALTER TABLE person OWNER TO marbenadm;
ALTER TABLE data_source OWNER TO marbenadm;

ALTER TABLE fctnal_gr OWNER TO marbenadm;
ALTER TABLE taxonomy OWNER TO marbenadm;
ALTER TABLE authority OWNER TO marbenadm;
ALTER TABLE photo_sp OWNER TO marbenadm;

ALTER TABLE station OWNER TO marbenadm;
ALTER TABLE unit_typ OWNER TO marbenadm;
ALTER TABLE unit OWNER TO marbenadm;

ALTER TABLE samplers OWNER TO marbenadm;
ALTER TABLE photo_samplers OWNER TO marbenadm;
ALTER TABLE sampl_meth OWNER TO marbenadm;
ALTER TABLE sample OWNER TO marbenadm;
ALTER TABLE photo_sampl OWNER TO marbenadm;

ALTER TABLE meth_ana OWNER TO marbenadm;
ALTER TABLE parametres OWNER TO marbenadm;
ALTER TABLE resultat OWNER TO marbenadm;

ALTER TABLE demandes OWNER TO marbenadm;
ALTER TABLE demandeurs OWNER TO marbenadm;

-- marbenadm a tous les privilèges sur les tables

GRANT ALL PRIVILEGES ON geometry_columns TO marbenadm;
GRANT ALL PRIVILEGES ON spatial_ref_sys TO marbenadm;

GRANT ALL PRIVILEGES ON sector TO marbenadm;
GRANT ALL PRIVILEGES ON site TO marbenadm;
GRANT ALL PRIVILEGES ON point TO marbenadm;

GRANT ALL PRIVILEGES ON theme TO marbenadm;
GRANT ALL PRIVILEGES ON person TO marbenadm;
GRANT ALL PRIVILEGES ON data_source TO marbenadm;

GRANT ALL PRIVILEGES ON fctnal_gr TO marbenadm;
GRANT ALL PRIVILEGES ON taxonomy TO marbenadm;
GRANT ALL PRIVILEGES ON authority TO marbenadm;
GRANT ALL PRIVILEGES ON photo_sp TO marbenadm;

GRANT ALL PRIVILEGES ON station TO marbenadm;
GRANT ALL PRIVILEGES ON unit_typ TO marbenadm;
GRANT ALL PRIVILEGES ON unit TO marbenadm;

GRANT ALL PRIVILEGES ON samplers TO marbenadm;
GRANT ALL PRIVILEGES ON photo_samplers TO marbenadm;
GRANT ALL PRIVILEGES ON sampl_meth TO marbenadm;
GRANT ALL PRIVILEGES ON sample TO marbenadm;
GRANT ALL PRIVILEGES ON photo_sampl TO marbenadm;

GRANT ALL PRIVILEGES ON meth_ana TO marbenadm;
GRANT ALL PRIVILEGES ON parametres TO marbenadm;
GRANT ALL PRIVILEGES ON resultat TO marbenadm;

GRANT ALL PRIVILEGES ON demandeurs TO marbenadm;
GRANT ALL PRIVILEGES ON demandes TO marbenadm;

-- marbenweb n'a que les privilèges de select sur les tables

GRANT SELECT ON geometry_columns TO marbenweb;
GRANT SELECT ON spatial_ref_sys TO marbenweb;

GRANT SELECT ON sector TO marbenweb;
GRANT SELECT ON site TO marbenweb;
GRANT SELECT ON point TO marbenweb;

GRANT SELECT ON theme TO marbenweb;
GRANT SELECT ON person TO marbenweb;
GRANT SELECT ON data_source TO marbenweb;

GRANT SELECT ON fctnal_gr TO marbenweb;
GRANT SELECT ON taxonomy TO marbenweb;
GRANT SELECT ON authority TO marbenweb;
GRANT SELECT ON photo_sp TO marbenweb;

GRANT SELECT ON station TO marbenweb;
GRANT SELECT ON unit_typ TO marbenweb;
GRANT SELECT ON unit TO marbenweb;

GRANT SELECT ON samplers TO marbenweb;
GRANT SELECT ON photo_samplers TO marbenweb;
GRANT SELECT ON sampl_meth TO marbenweb;
GRANT SELECT ON sample TO marbenweb;
GRANT SELECT ON photo_sampl TO marbenweb;

GRANT SELECT ON meth_ana TO marbenweb;
GRANT SELECT ON parametres TO marbenweb;
GRANT SELECT ON resultat TO marbenweb;

GRANT SELECT ON demandeurs TO marbenweb;
GRANT SELECT ON demandes TO marbenweb;
GRANT INSERT ON demandeurs TO marbenweb;
GRANT INSERT ON demandes TO marbenweb;
GRANT UPDATE ON demandeurs TO marbenweb;
GRANT UPDATE ON demandes TO marbenweb;


-- ajout d'enregistrements 'inconnu' pour les tables paramètres, unit
-- INSERT INTO unit (unit_id, unit_name, unit_symb, unit_typ) VALUES (9999, 'Unité non renseignée', 'Non renseigné', 13);
-- INSERT INTO parametres (prm_id, prm_name) VALUES (9999, 'Paramètre non renseigné');
-- INSERT INTO theme (thm_code, thm_name) VALUES ('ZZ', 'Thème non renseigné');


/*
REVOKE ALL PRIVILEGES ON site FROM marbenadm;
REVOKE ALL PRIVILEGES ON point FROM marbenadm;
REVOKE ALL PRIVILEGES ON theme FROM marbenadm;
REVOKE ALL PRIVILEGES ON data_source FROM marbenadm;
REVOKE ALL PRIVILEGES ON person FROM marbenadm;
REVOKE ALL PRIVILEGES ON station FROM marbenadm;
REVOKE ALL PRIVILEGES ON samplers FROM marbenadm;
REVOKE ALL PRIVILEGES ON sampl_meth FROM marbenadm;
REVOKE ALL PRIVILEGES ON echantillon FROM marbenadm;
REVOKE ALL PRIVILEGES ON meth_ana FROM marbenadm;
REVOKE ALL PRIVILEGES ON parametres FROM marbenadm;
REVOKE ALL PRIVILEGES ON unit_typ FROM marbenadm;
REVOKE ALL PRIVILEGES ON unit FROM marbenadm;
REVOKE ALL PRIVILEGES ON resultat FROM marbenadm;
DROP USER marbenadm;

REVOKE SELECT ON site FROM marbenweb;
REVOKE SELECT ON point FROM marbenweb;
REVOKE SELECT ON theme FROM marbenweb;
REVOKE SELECT ON data_source FROM marbenweb;
REVOKE SELECT ON person FROM marbenweb;
REVOKE SELECT ON station FROM marbenweb;
REVOKE SELECT ON samplers FROM marbenweb;
REVOKE SELECT ON sampl_meth FROM marbenweb;
REVOKE SELECT ON echantillon FROM marbenweb;
REVOKE SELECT ON meth_ana FROM marbenweb;
REVOKE SELECT ON parametres FROM marbenweb;
REVOKE SELECT ON unit_typ FROM marbenweb;
REVOKE SELECT ON unit FROM marbenweb;
REVOKE SELECT ON resultat FROM marbenweb;
DROP USER marbenweb;
*/
(2-2/4)