Mise en conformite avec le SANDRE

[cedric]

Va aussi affecter le programme JAVA et le programme R attention !

[cedric]

!#sql
-- ticket 81
-- Mise en conformité avec le sandre et modification des noms et types de certains champs
-- a lancer sur une base "test" et adapter le programme JAVA en fonction
-- owners Cédric et François


/* fonction ref.updatesql
Cette fonction permet de lancer des requêtes de mise à jour dans tous les schemas
*/
CREATE OR REPLACE FUNCTION ref.updatesql(myschemas varchar[],scriptsql text)RETURNS int AS $$
	DECLARE
	totalcount int;	
	nbschema int;
	i integer;
	BEGIN	  
		select INTO nbschema array_length(myschemas,1);
		i=1;
		While (i <=nbschema) LOOP
		EXECUTE 'SET search_path TO '||myschemas[i]||', public';
		EXECUTE scriptsql;	
		i=i+1;
		END LOOP;	
	RETURN nbschema;
	END;
	$$
LANGUAGE 'plpgsql' ;
COMMENT ON FUNCTION ref.compile (myschema varchar) IS 'fonction pour lancer un script de mise à jour sur chaque schema';
-- ci dessous un essai qui fonctionne
comment on table iav.t_station_sta is 'table dont le sta_code doit être modifié';
select ref.updatesql('{"iav","logrami"}','comment on table t_station_sta is ''table dont le sta_code doit être modifié''');
select ref.updatesql('{"azti","bgm","charente","fd80","iav","inra","logrami","migado","migradour","mrm","saumonrhin","smatah"}','comment on table t_station_sta is ''table dont le sta_code doit être modifié''');
/* travail sur les array pour voir
create table essai(es varchar[]);
insert into essai values ('{"toto","titi"}');
select * from essai;
select es[1] from essai;
select array_length(es,1) from essai
drop table essai
*/

alter table iav.t_station_sta   rename column sta_code to sta_identifiant; -- test sur IAV
-- lancement sur tous les autres (à faire avec François)
select ref.updatesql('{"azti","bgm","charente","fd80","inra","logrami","migado","migradour","mrm","saumonrhin","smatah"}',
'alter table t_station_sta rename column sta_code to sta_identifiant');

[cedric]

-- ticket 81 version 0.5
-- Mise en conformité avec le sandre et modification des noms et types de certains champs
-- a lancer sur une base "test" et adapter le programme JAVA en fonction
-- owners Cédric et François
-- reste à faire


/* fonction ref.updatesql
Cette fonction permet de lancer des requêtes de mise à jour dans tous les schemas
*/
CREATE OR REPLACE FUNCTION ref.updatesql(myschemas varchar[],scriptsql text)RETURNS int AS $$
        DECLARE
        totalcount int; 
        nbschema int;
        i integer;
        BEGIN     
                select INTO nbschema array_length(myschemas,1);
                i=1;
                While (i <=nbschema) LOOP
                EXECUTE 'SET search_path TO '||myschemas[i]||', public';
                EXECUTE scriptsql;      
                i=i+1;
                END LOOP;       
        RETURN nbschema;
        END;
        $$
LANGUAGE 'plpgsql' ;
COMMENT ON FUNCTION ref.updatesql (myschemas varchar[],scriptsql text) IS 'fonction pour lancer un script de mise à jour sur chaque schema';
-- ci dessous un essai qui fonctionne
comment on table iav.t_station_sta is 'table dont le sta_code doit être modifié';
select ref.updatesql('{"iav","logrami"}','comment on table t_station_sta is ''table dont le sta_code doit être modifié''');
select ref.updatesql('{"azti","bgm","charente","fd80","iav","inra","logrami","migado","migradour","mrm","saumonrhin","smatah"}','comment on table t_station_sta is ''table dont le sta_code doit être modifié''');
/* travail sur les array pour voir
create table essai(es varchar[]);
insert into essai values ('{"toto","titi"}');
select * from essai;
select es[1] from essai;
select array_length(es,1) from essai
drop table essai
*/

alter table iav.t_station_sta   rename column sta_code to sta_identifiant; -- test sur IAV
-- lancement sur tous les autres (à faire avec François)
select ref.updatesql('{"azti","bgm","charente","fd80","inra","logrami","migado","migradour","mrm","saumonrhin","smatah"}',
'alter table t_station_sta rename column sta_code to sta_identifiant');


create table ref.ts_nomenclature_nom(
nom_id serial primary key,
nom_nomtable character varying(60),
nom_nomenclaturesandreid integer);

set search_path to iav;
/* ***************
STATION
* ****************/
--TODO  rajouter un sta_identifiant (serial) et faire le lien dessus au lieu de sta_code et rajouter une contrainte unique sur sta_code
/* ***************
OUVRAGE
* ****************/
/* CODE DE OUVRAGE*/

alter table t_ouvrage_ouv alter column ouv_code type character varying(30); -- comme le ROE à vérifier






/* NATURES OUVRAGE*/
insert into ref.ts_nomenclature_nom(nom_nomtable,nom_nomenclaturesandreid) values (ref.tr_natureouvrage_nov,284);

select * from ref.tr_natureouvrage_nov;
/*

        OBST_ECOUL      Obstacles à l'écoulement        Un obstacle à l’écoulement est un ouvrage lié à l’eau qui est à l’origine d’une modification de l’écoulement des eaux de surface (dans les talwegs, lits mineurs et majeurs de cours d'eau et zones de submersion marine). Seuls les obstacles artificiels (provenant de l’activité humaine) sont pris en compte.       Validé  04/06/2003      04/06/2003
1.1     BAR     Barrage         Validé  04/06/2003      04/06/2003
1.2     SEUIL   Seuil en rivière                Validé  04/06/2003      04/06/2003
1.3     DIGUE   Digue           Validé  04/06/2003      04/06/2003
1.4     PONT    Pont            Validé  04/06/2003      04/06/2003
1.5     EPIS    Epis en rivière Sur une partie de la largeur du lit mineur ou lit majeur        Validé  04/06/2003      04/06/2003
"0";"Nature de l'ouvrage inconnue"
"1";"Barrage"
"2";"Seuil déversant"
"3";"Filet barrage"
"4";"Usine"
"5";"pas d’ouvrage"
*/
alter table t_ouvrage_ouv DROP CONSTRAINT c_fk_ouv_nov_code;
update ref.tr_natureouvrage_nov set nov_code='1.1' where nov_code='1';
update t_ouvrage_ouv set ouv_nov_code='1.1' where ouv_nov_code='1';
update ref.tr_natureouvrage_nov set (nov_code,nov_nom)=('1.2','Seuil en rivière') where nov_code='2';
update t_ouvrage_ouv set ouv_nov_code='1.2' where ouv_nov_code='2';
update t_ouvrage_ouv set ouv_nov_code='6.1' where ouv_nov_code='4';
update ref.tr_natureouvrage_nov set nov_code='6.1' where nov_code='4';

-- A voir avec le sandre
update t_ouvrage_ouv set ouv_nov_code='X.2' where ouv_nov_code='3';
update ref.tr_natureouvrage_nov set nov_code='X.2' where nov_code='3';
insert into ref.tr_natureouvrage_nov values ('X.1','barrière électrique')

 alter table t_ouvrage_ouv ADD CONSTRAINT c_fk_ouv_nov_code FOREIGN KEY (ouv_nov_code)
      REFERENCES ref.tr_natureouvrage_nov (nov_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;

alter table t_ouvrage_ouv drop column ouv_denivelee_max;

/* ECHAPPEMENT*/

-- TODO remplacer ouvrage par station pour les taux d'échappement
-- méthode d'estimation du taux d'échappement
/*
Code    Mnémonique      Libellé
0       Mode inconnu    Mode d'estimation inconnu
1       Calcul  Par calcul sur des données élémentaires
2       Avis d'experts  Sur avis d'experts
3       Non estimable   Non estimable
4       Mesuré  Mesuré
*/
-- TODO rajouter une table référentielle et mettre le lien dans txe_methode_estimation 
-- rajouter une contrainte de clé étrangère et faire sauter c_ck_txe_methode_estimation
-- vérifier compatibilité JAVA

-- idem pour niveaux d'échappement
/*
Code    Mnémonique      Libellé Définition
0       Inconnu Niveau d'échappement inconnu    
1       Nul     Niveau d'échappement nul        Taux d'échappement de l'ordre de 0%
2       Faible  Niveau d'échappement faible     Taux d'échappement < 33 %
3       Moyen   Niveau d'échappement moyen      Taux d'échappemententre 33 % et 66 %
4       Fort    Niveau d'échappement fort       Taux d'échappement > 66 %
*/

/*
DF
*/

/*
Code    Mnemonique      Libellé         Définition
1       PASSERALENTI    Passe à ralentisseurs   La passe à ralentisseurs est un canal rectiligne à pente relativement forte (entre 1/10 et 1/5 suivant le type de passe et l'espèce considérée), de section rectangulaire, dans lequel sont installés sur le fond uniquement (passes à ralentisseurs de fond suractifs, passes à ralentisseurs à chevrons épais) ou à la fois sur le fond et les parois latérales (passes à ralentisseurs plans) des déflecteurs destinés à réduire les vitesses moyennes de l'écoulement. Ces déflecteurs, de formes plus ou moins complexes, donnent naissance à des courants hélicoïdaux qui assurent une forte dissipation d'énergie au sein de l'écoulement.
2       PASSEBASSIN     Passes à bassins successifs     Dispositif très commun et de conception relativement ancienne, consistant à diviser la hauteur à franchir en plusieurs petites chutes formant une série de bassins. Il existe plusieurs types de communications entre bassins, le passage de l'eau pouvant s'effectuer soit par déversement de surface, soit par écoulement à travers un ou plusieurs orifices ménagés dans la cloison, soit encore par une ou plusieurs fentes ou échancrures. On rencontre également des passes de type mixte.
3       ECLUSEPOISS     Ecluse à poisson        ascenseur à poissons permet de remonter les poissons, piégés dans une cuve, et de les déverser en amont de l'obstacle. A noter que les écluses à bateau permettent aussi le franchissement piscicole.
4       EXUTOIREDEVAL   Exutoire de dévalaison  
5       PASANG  Passe à anguille        Rampe équipée d'un matériau facilitant la progression des jeunes anguilles à la montaison. Les matériaux employés peuvent être d'origine naturelle (cailloux, branchages, bruyère, paille) ou artificielle (brosses , plots en béton...). Ce sont essentiellement des substrats de type brosse qui sont utilisés aujourd'hui en France. L'espacement entre chaque faisceau de soies dépend de la taille des individus à faire passer.
5a      TAPBROSSE       Tapis brosse    
5b      SUBRUGU Substrat rugueux        
5c      PASSPIEGE       Passe piège     
7       PREBAR  Pré-barrage     Dispositifs formés de plusieurs petits seuils, le plus souvent en béton ou enrochements jointoyés, créant à l'aval de l'obstacle des grands bassins qui fractionnent la chute à franchir. Ces prébarrages sont généralement implantés sur une partie de la largeur de l'obstacle, à proximité de l'une des deux rives pour en faciliter l'entretien.
8       RAMPE   Rampe   
8a      RPEPARTLARG     Rampe sur partie de la largeur  
8b      RPETOTLARG      Rampe sur totalité de la largeur        
9       RIVIERE Rivière de contournement        Dispositif consistant à relier biefs amont et aval par un chenal dans lequel l'énergie est dissipée et les vitesses réduites par la rugosité du fond et celle des parois ainsi que par une succession d'obstacles (blocs, épis, seuils) plus ou moins régulièrement répartis, reproduisant en quelque sorte l'écoulement dans un cours d'eau naturel.
10      AUTRES  Autre type de dispositif        
*/
select * from ref.tr_typedf_tdf
-- TODO mettre à jour avec les infos du SANDRE, faire sauter les contraintes, reprendre table du sandre tel quel (ajouter une mnémonique), 
--.modifier les codes dans t_dispositiffranchissement_dif
--.


/*
DC
*/



select * from ref.tr_typedc_tdc
/*
Code    Mnemonique              Libellé         Définition
0       Dispositif inconnu      Type de dispositif inconnu      
1       Piégeage                Piégeage        
2       Résistivité             Compteur à résistivité  
3       Analyse visuelle d'image        Analyse visuelle d'image (reconnaissance directe, par bande vidéo, assistée par ordinateur, ...)        
4       Accoustique             Comptage accoustique    
5       Optoélectronique        Comptage optoélectronique
*/
insert into ref.tr_typedc_tdc values (6,'comptage radio');
insert into ref.tr_typedc_tdc values (7,'comptage sonar');

/*
OPERATION
*/


/*
LOT
*/

--TODO renseigner les tax_tax_code
/*
- Méthode d'obtention : Moyen d'obtention de la mesure de l’effectif du lot de poissons. La liste des valeurs autorisées est définie par le Sandre dans la nomenclature n°141.

Code    Mnémonique      Libellé Définition      Statut  Création        Modification
0       Mode inconnu    Mode d'estimation inconnu               Validé  04/09/1998      26/03/2010
1       Calcul  Par calcul sur des données élémentaires         Validé  04/09/1998      26/03/2010
2       Avis d'experts  Sur avis d'experts              Validé  04/09/1998      26/03/2010
3       Non estimable   Non estimable           Validé  04/09/1998      26/03/2010
4       Mesuré  Mesuré          Validé  02/04/2010      02/04/2010
*/
--TODO Modifier la contrainte, faire appel à une table référentielle, en pratique c'est la même que pour les taux d'échappement.


-- DEVENIR
-- TODO changer les libéllés et les dernières lignes
/*
Code    Mnémonique      Libellé Définition
0       Inconnu Devenir inconnu 
1       Relaché Relâché au droit de la station  Relâché au droit de la station
2       Trépassé        Trépassé        Trépassé
3       Transféré       Transféré dans le milieu naturel en dehors de la station        Transféré dans le milieu naturel en dehors de la station
4       Elevage Mis en élevage  Mis en élevage
5       Prelévé Prélevé pour étude      Prélevé pour étude
6       Relâché pour recapture  Relâché avant la station et susceptible d’être recapturé        
*/
select * from ref.tr_devenirlot_dev;
/*
"1";"Relâché au droit de la station";1
"0";"Devenir inconnu";2
"2";"Trépassé";3
"3";"Transporté dans le milieu naturel ";4
"5";"Relâché avant l'entrée de la station, susceptible d'être recapturé dans la même station";5
"4";"Mis en élevage";6
*/


Dans les pathologies, ajouter une colonne avec l'intensité de la pathologie
/* 
PATHOLOGIE
*/
/*
Code    Mnémonique      Libellé Définition
00      00      Ni poux, ni traces de poux      Le poisson, généralement un salmonidé migrateur venu de la mer, n'héberge aucun pou de mer et ne présente aucune lésion visible consécutive à une colonisation par le pou de mer (qui est en fait un crustacé parasite des salmonidés migrateurs)
AA      AA      Altération de l'aspect  Le corps du poisson examiné présente des altérations morphologiques caractérisées, pouvant éventuellement être détaillées ou non.
AC      AC      Altération de la couleur        La pigmentation présente des altérations entrainant une coloration anormale de tout ou partie du corps du poisson.
AD      AD      Difforme        Le corps du poisson présente des déformations anormales se traduisant par des acures ou des bosselures,extériorisation possible d'une atteinte interne, virale par exemple (ex : nécrose pancréatique infectieuse de la truite arc-en-ciel)
AG      AG      Grosseur, excroissance  Le corps du poisson présente une ou des déformations marquées constituant des excroissances ou des tumeurs
AH      AH      Aspect hérissé (écailles)       Les écailles du poisson ont tendance à se relever perpendiculairement au corps, à la suite généralement d'une infection au niveau des téguments
AM      AM      Maigreur        Le corps du poisson présente une minceur marquée par rapport à la normalité
AO      AO      Absence d'organe        L'altération morphologique observée sur le poisson se traduit par l'absence d'un organe (nageoire, opercule, oeil, machoire)
BG      BG      Bulle de gaz    Présence de bulle de gaz pouvant être observées sous la peau, au bord des nageoires, au niveau des yeux, des branchies ou de la bouche.
CA      CA      Coloration anormale     L'altération de la pigmentation entraîne la différenciation de zones diversement colorées, avec en particulier des zones sombres.
CB      CB      Branchiures (Argulus...)        Présence visible, à la surface du corps ou des branchies du poisson, de crustacés branchiures à un stade donné de leur cycle de développement.
CC      CC      Copépodes (Ergasilus, Lerna,...)        Présence visible, à la surface du corps ou des branchies du poisson, de crustacés parasites, à un stade donné de leur cycle de développement.
CO      CO      Coloration opaque (oeil)        L'altération de la coloration se traduit par une opacification de l'un ou des deux yeux.
CR      CR      Crustacés       Présence visible, ?? la surface du corps ou des branchies du poisson, de crustacés parasites, à un stade donné de leur cycle de développement.
CS      CS      Coloration sombre       L'altération de la coloration du corps du poisson se traduit par un assombrissement de tout ou partie de celui-ci (noircissement).
CT      CT      Coloration terne (pâle) L'altération de la coloration du corps du poisson se traduit par une absence de reflets lui conférant un aspect terne, pâle, voire une décoloration.
HA      HA      Acanthocéphales Présence visible, à la surface du corps ou des branchies du poisson, d'acanthocéphales à un stade donné de leur cycle de développement.
HC      HC      Cestodes (Ligula, Bothriocephalus, ...) Présence visible, à la surface du corps ou des branchies du poisson, de cestodes à un stade donné de leur cycle de développement.
HE      HE      Hémorragie      Ecoulement de sang pouvant être observé à la surface du corps ou au niveau des branchies.
HH      HH      Hirudinés (Piscicola)   Présence visible sur le poisson de sangsue(s)
HN      HN      Nématodes (Philometra, Philimena...)    Présence visible, à la surface du corps ou des branchies du poisson, de nématodes à un stade donné de leur cycle de développement.
HS      HS      Stade pre-mortem        Le poisson présente un état pathologique tel qu'il n'est plus capable de se mouvoir normalement dans son milieu et qu'il est voué à une mort certaine à brève échéance.
HT      HT      Trématodes (Bucephalus, ...)    Présence visible, à la surface du corps ou des branchies du poisson, de trématodes parasites à un stade donné de leur cycle de développement.
IS      IS      Individu sain   Après examen du poisson, aucun signe externe, caractéristique d'une pathologie quelconque, n'est décelable à l'oeil nu
LD      LD      Lésions diverses        Les téguments présentent une altération quelconque de leur intégrité.
NE      NE      Nécrose Lésion(s) observée(s) à la surface du corps avec mortification des tissus.
NN      NN      Pathologie non renseigné        L'aspect pathologique du poisson n'a fait l'objet d'aucun examen et aucune information n'est fournie à ce sujet
PA      PA      Parasitisme     Présence visible, à la surface du corps ou des branchies du poisson, d'organismes parasites vivant à ses dépens.
PB      PB      Points blancs   Présence de points blancs consécutive à la prolif??ration de certains protozoaires parasites comme Ichtyopthtirius (ne pas confondre avec les boutons de noces, formations kératinisées apparaissant lors de la période de reproduction)
PC      PC      Champignons (mousse, ...)       Présence d'un développement à la surface du corps, d'un mycélium formant une sorte de plaque rappelant l'aspect de la mousse et appartenant à une espèce de champignon colonisant les tissus du poisson.
PL      PL      Plaie - blessure        Présence d'une ou plusieurs lésions à la surface du tégument généralement due à un prédateur (poisson, oiseau,.)
PN      PN      Points noirs    Présence de tâches noires bien individualisées sur la surface du tégument du poisson
SM      SM      Sécrétion de mucus importante   Présence anormale de mucus sur le corps ou au niveau de la chambre branchiale.
UH      UH      Ulcère hémorragique     Ecoulement de sang observé au niveau d'une zone d'altération des tissus.
VL      VL      Vésicule contenant un liquide   Présence d'un oedème constituant une excroissance.
ZO      ZO      Etat pathologie multiforme      Le poisson présente plus de deux caractéristiques pathologiques différentes
01      01      Traces de poux  Le poisson ne porte aucun pou mais présente des lésions cutanées consécutives à une colonisation par le pou de mer. La présence du poisson en eau douce a été suffisante pour obliger les poux à quitter leur hôte.
11      11      < 10 poux ; sans flagelles      Le poisson présente moins de 10 poux de mer, mais ces derniers, en raison d'un présence prolongée en eau douce, ont déjà perdu leur flagelle
21      21      < 10 poux ; avec flagelles      Le poisson présente moins de 10 poux de mer, mais ces derniers, compte-tenu de l'arrivée récente de leur hôte en eau douce, n'ont pas encore perdu leur flagelle
31      31      > 10 poux, sans flagelles       Le poisson présente plus de 10 poux de mer, mais ces derniers, en raison d'un présence prolongée en eau douce, ont déjà perdu leur flagelle
42      42      > 10 poux, avec flagelles       Le poisson présente plus de 10 poux de mer, mais ces derniers, compte-tenu de l'arrivée récente de leur hôte en eau douce, n'ont pas encore perdu leur flagelle
*/
select * from ref.tr_pathologie_pat order by pat_code

/*
rajouter =>

UH 

en trop
=> 51
=> ER
=> EX
=> UL 
=> US

MODIFIER
UL ulcère non hémorragique
/*
LOCALISATION 
*/

select * from ref.tr_localisationanatomique_loc order by loc_code;

-- en trop (rajouter au sandre)
/*
ABD
ADP
ANA
CAB
CAH
... tous 
*/