-- ================================================================ --
-- ================================================================ --
--   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;
*/