52 lines
1.6 KiB
Python
52 lines
1.6 KiB
Python
#!/usr/bin/env python3
|
|
from pycen import con_bdcen as con
|
|
|
|
drop_line = "DROP VIEW IF EXISTS travaux.v_line_saisie_travaux;"
|
|
v_line_saisie_travaux = """
|
|
CREATE OR REPLACE VIEW travaux.v_line_saisie_travaux
|
|
AS SELECT a.gid AS serial,
|
|
a.geom,
|
|
a.id_site,
|
|
a.annee_trav,
|
|
b.legende AS id_gestion,
|
|
c.lib_presta,
|
|
a.etat_trav,
|
|
a.nom_presta,
|
|
a.date_debut,
|
|
a.duree_trav,
|
|
a.nb_etp,
|
|
a.nb_balle,
|
|
e.val_mat,
|
|
a.legende,
|
|
a.rmq,
|
|
d.val_mat AS prestation,
|
|
a.long_m,
|
|
a.crea_date,
|
|
a.date_update
|
|
FROM travaux.cr_line_saisie_travaux a
|
|
LEFT JOIN ref_travaux.cr_type_gestion b ON a.id_gestion = b.id_gestion::integer
|
|
LEFT JOIN ref_travaux.cr_type_prestataire c ON a.id_presta = c.id_presta::integer
|
|
LEFT JOIN ref_travaux.cr_type_prestation d ON a.prestation = d.id
|
|
LEFT JOIN ref_travaux.cr_valorisation_matiere e ON a.prestation = e.id
|
|
ORDER BY a.date_debut DESC;
|
|
"""
|
|
grant = """
|
|
ALTER TABLE travaux.v_line_saisie_travaux OWNER TO cen_admin;
|
|
GRANT ALL ON TABLE travaux.v_line_saisie_travaux TO cen_admin;
|
|
GRANT ALL ON TABLE travaux.v_line_saisie_travaux TO grp_admin;
|
|
GRANT SELECT ON TABLE travaux.v_line_saisie_travaux TO grp_consult;
|
|
GRANT SELECT, UPDATE, TRUNCATE, INSERT, DELETE ON TABLE travaux.v_line_saisie_travaux TO grp_travaux;
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(drop_line)
|
|
cnx.execute(v_line_saisie_travaux)
|
|
cnx.execute(grant)
|
|
|
|
drop_point = "DROP VIEW IF EXISTS travaux.v_point_saisie_travaux;"
|
|
v_point_saisie_travaux = """
|
|
"""
|
|
|
|
|
|
drop_poly = "DROP VIEW IF EXISTS travaux.v_poly_saisie_travaux;"
|
|
v_poly_saisie_travaux = """
|
|
""" |