126 lines
4.1 KiB
Python
126 lines
4.1 KiB
Python
from pycen import con_fon
|
|
|
|
foreign_server = """
|
|
CREATE SERVER fdw_azalee
|
|
FOREIGN DATA WRAPPER postgres_fdw
|
|
OPTIONS (dbname 'azalee', host '91.134.194.221', port '5432')
|
|
"""
|
|
user_mapping = """
|
|
CREATE USER MAPPING
|
|
FOR cen_admin
|
|
SERVER fdw_azalee
|
|
OPTIONS (user 'cen_admin', password '#CEN38@venir')
|
|
"""
|
|
foreign_table = """
|
|
-- DROP FOREIGN TABLE inventaires.inventaire_zh;
|
|
CREATE FOREIGN TABLE inventaires.inventaire_zh (
|
|
site_code varchar(10) NULL,
|
|
nom varchar NULL,
|
|
auteur_site varchar NULL,
|
|
auteur_geom varchar NULL,
|
|
auteur_last_maj varchar NULL,
|
|
date_site date NULL,
|
|
date_geom date NULL,
|
|
date_last_maj date NULL,
|
|
type_milieu varchar NULL,
|
|
type_site varchar NULL,
|
|
typo_sdage varchar NULL,
|
|
rmq_site text NULL,
|
|
rmq_fct_majeur text NULL,
|
|
rmq_interet_patri text NULL,
|
|
rmq_bilan_menace text NULL,
|
|
rmq_orient_act text NULL,
|
|
rmq_usage_process text NULL,
|
|
code_cb varchar NULL,
|
|
lib_cb text NULL,
|
|
activ_hum varchar NULL,
|
|
impact varchar NULL,
|
|
"position" varchar NULL,
|
|
rmq_activ_hum text NULL,
|
|
connexion varchar NULL,
|
|
subm_orig varchar NULL,
|
|
subm_freq varchar NULL,
|
|
subm_etend varchar NULL,
|
|
fct_bio varchar NULL,
|
|
fct_hydro varchar NULL,
|
|
int_patri varchar NULL,
|
|
"val_socioEco" varchar NULL,
|
|
crit_delim varchar NULL,
|
|
crit_def_esp varchar NULL,
|
|
entree_eau_reg varchar NULL,
|
|
entree_eau_perm varchar NULL,
|
|
entree_eau_topo varchar NULL,
|
|
sortie_eau_reg varchar NULL,
|
|
sortie_eau_perm varchar NULL,
|
|
sortie_eau_topo varchar NULL,
|
|
geom public.geometry(geometry, 2154) NULL
|
|
)
|
|
SERVER fdw_azalee
|
|
OPTIONS (schema_name 'zones_humides', table_name 'v_zoneshumides');
|
|
|
|
-- Permissions
|
|
ALTER TABLE inventaires.inventaire_zh OWNER TO cen_admin;
|
|
GRANT ALL ON TABLE inventaires.inventaire_zh TO cen_admin;
|
|
"""
|
|
|
|
view_v_zoneshumides = """
|
|
-- inventaires.v_zoneshumides source
|
|
CREATE OR REPLACE VIEW inventaires.v_zoneshumides
|
|
AS SELECT inventaire_zh.site_code,
|
|
inventaire_zh.nom,
|
|
inventaire_zh.auteur_site,
|
|
inventaire_zh.auteur_geom,
|
|
inventaire_zh.auteur_last_maj,
|
|
inventaire_zh.date_site,
|
|
inventaire_zh.date_geom,
|
|
inventaire_zh.date_last_maj,
|
|
inventaire_zh.type_milieu,
|
|
inventaire_zh.type_site,
|
|
inventaire_zh.typo_sdage,
|
|
inventaire_zh.rmq_site,
|
|
inventaire_zh.rmq_fct_majeur,
|
|
inventaire_zh.rmq_interet_patri,
|
|
inventaire_zh.rmq_bilan_menace,
|
|
inventaire_zh.rmq_orient_act,
|
|
inventaire_zh.rmq_usage_process,
|
|
inventaire_zh.code_cb,
|
|
inventaire_zh.lib_cb,
|
|
inventaire_zh.activ_hum,
|
|
inventaire_zh.impact,
|
|
inventaire_zh."position",
|
|
inventaire_zh.rmq_activ_hum,
|
|
CASE
|
|
WHEN inventaire_zh.rmq_activ_hum ~~ '%remblai%'::text THEN 't'::text
|
|
ELSE 'f'::text
|
|
END AS remblais,
|
|
inventaire_zh.connexion,
|
|
inventaire_zh.subm_orig,
|
|
inventaire_zh.subm_freq,
|
|
inventaire_zh.subm_etend,
|
|
inventaire_zh.fct_bio,
|
|
inventaire_zh.fct_hydro,
|
|
inventaire_zh.int_patri,
|
|
inventaire_zh."val_socioEco",
|
|
inventaire_zh.crit_delim,
|
|
inventaire_zh.crit_def_esp,
|
|
inventaire_zh.entree_eau_reg,
|
|
inventaire_zh.entree_eau_perm,
|
|
inventaire_zh.entree_eau_topo,
|
|
inventaire_zh.sortie_eau_reg,
|
|
inventaire_zh.sortie_eau_perm,
|
|
inventaire_zh.sortie_eau_topo,
|
|
inventaire_zh.geom
|
|
FROM inventaires.inventaire_zh;
|
|
|
|
-- Permissions
|
|
ALTER TABLE inventaires.v_zoneshumides OWNER TO cen_admin;
|
|
GRANT ALL ON TABLE inventaires.v_zoneshumides TO cen_admin;
|
|
GRANT SELECT ON TABLE inventaires.v_zoneshumides TO grp_sig;
|
|
GRANT SELECT ON TABLE inventaires.v_zoneshumides TO cen_user;
|
|
"""
|
|
|
|
with con_fon.begin() as cnx:
|
|
cnx.execute(foreign_server)
|
|
cnx.execute(user_mapping)
|
|
cnx.execute(foreign_table)
|
|
cnx.execute(view_v_zoneshumides) |