Changes between Version 3 and Version 4 of Ticket #28


Ignore:
Timestamp:
Mar 21, 2010 9:38:53 PM (15 years ago)
Author:
cedric
Comment:

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #28

    • Property Status changed from new to accepted

  • Ticket #28 – Description

    v3 v4  
    11script sql permettant la création du schéma et le grant de toutes les autorisations 
    22= Notes pour mémoire pour Cédric = 
    3 Remplacer le nom logrami  par n'importe quel autre organisme 
     3Ci dessous 
    44Sauvegarde et création d'un schéma vide avec la commande -- schema pour ne sauvegarder qu'un schéma et la commande -s pour ne sauvegarder que la structure des tables 
     5et --inserts pour pouvoir restaurer la base directement en commande sql sans utiliser pgrestore. 
    56{{{ 
    67CD C:\base 
    7 C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5433 --schema=iav -s BD_CONTMIG_NAT > sauv_schema.sql 
    8 CREATE SCHEMA logrami GRANT ALL ON SCHEMA logrami; 
    9 C:\"Program Files"\PostgreSQL\8.4\bin\psql -d CCM_EDA -p 5433 -U postgres -f sauv_schema.sql 
     8C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5433 --schema=iav --schema-only BD_CONTMIG_NAT > sauv_schema.sql 
     9C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5433 --schema=iav --schema-only BD_CONTMIG_NAT > sauv_schema.sql 
     10C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5433 --table=iav.ts_taxonvideo_txv --inserts BD_CONTMIG_NAT > ts_taxonvideo.sql 
    1011}}} 
     12Le fichier ici en contrôle de source: [[BR]] 
     13source:branches/STACOMIR0.3/requètes/base_nationale/Create_new_schema.sql 
     14permet de restaurer une base [[BR]] 
     15suivre les procédures i.e.$ 
     16{{ 
     17Pour créer un schéma (ex migado) il faut faire rechercher remplacer logrami => migado 
     18}} 
     19Il faut aussi faire recherche remplacer LOGRAMI MIGADO 
    1120 
    12 {{{ 
    13 DELETE FROM  tj_caracteristiquelot_car 
    14 ; 
    15 DELETE FROM tj_prelevementlot_prl; 
    16  
    17 DELETE FROM  tj_conditionenvironnementale_env 
    18 ; 
    19 --DELETE FROM  tr_valeurparametrequalitatif_val 
    20 --; 
    21 --DELETE FROM  tr_parametrequalitatif_qal 
    22 --; 
    23 --DELETE FROM  tr_parametrequantitatif_qan 
    24 --; 
    25 --DELETE FROM  tg_parametre_par 
    26 --; 
    27 DELETE FROM  tj_actionmarquage_act 
    28 ; 
    29 DELETE FROM  t_marque_mqe 
    30 ; 
    31 DELETE FROM  t_operationmarquage_omq 
    32 ; 
    33 --DELETE FROM  tr_naturemarque_nmq 
    34 --; 
    35 DELETE FROM  tj_pathologieconstatee_pco 
    36 ; 
    37 --DELETE FROM  tr_pathologie_pat 
    38 --; 
    39 --DELETE FROM  tr_localisationanatomique_loc 
    40 --; 
    41 DELETE FROM  t_lot_lot 
    42 ; 
    43 --DELETE FROM  tr_devenirlot_dev 
    44 --; 
    45 DELETE FROM  tj_coefficientconversion_coe 
    46 ; 
    47 --DELETE FROM  tr_typequantitelot_qte 
    48 --; 
    49 DELETE FROM  tj_tauxechappement_txe 
    50 ; 
    51 --DELETE FROM  tr_niveauechappement_ech 
    52 --; 
    53 --DELETE FROM  tr_stadedeveloppement_std 
    54 --; 
    55 DELETE FROM  tj_dfestdestinea_dtx 
    56 ; 
    57 --DELETE FROM  tr_taxon_tax 
    58 --; 
    59 --DELETE FROM  tr_niveautaxonomique_ntx 
    60 ; 
    61 DELETE FROM  t_operation_ope 
    62 ; 
    63 DELETE FROM  t_periodefonctdispositif_per 
    64 ; 
    65 --DELETE FROM  tr_typearretdisp_tar 
    66 --; 
    67 DELETE FROM ts_taillevideo_tav; 
    68  
    69 DELETE FROM  t_dispositifcomptage_dic 
    70 ; 
    71 --DELETE FROM  tr_typedc_tdc 
    72 --; 
    73 DELETE FROM  tj_dfesttype_dft 
    74 ; 
    75 --DELETE FROM  tr_typedf_tdf 
    76 --; 
    77 DELETE FROM  t_dispositiffranchissement_dif 
    78 ; 
    79  
    80 DELETE FROM  tg_dispositif_dis 
    81 ; 
    82 DELETE FROM  t_ouvrage_ouv 
    83 ; 
    84 --DELETE FROM  tr_natureouvrage_nov 
    85 --; 
    86 DELETE FROM  tj_stationmesure_stm  
    87 ; 
    88 DELETE FROM  t_station_sta  
    89 ; 
    90 DELETE FROM  ts_description_donnee_dsc; 
    91 DELETE FROM  tj_prelevementlot_prl; 
    92  
    93 DELETE FROM t_bilanmigrationjournalier_bjo; 
    94 DELETE FROM t_bilanmigrationmensuel_bme; 
    95  
    96 ALTER SEQUENCE t_lot_lot_lot_identifiant_seq RESTART WITH 1; 
    97 ALTER SEQUENCE t_operation_ope_ope_identifiant_seq RESTART WITH 1; 
    98 ALTER SEQUENCE t_ouvrage_ouv_ouv_identifiant_seq RESTART WITH 1; 
    99 ALTER SEQUENCE tg_dispositif_dis_dis_identifiant_seq RESTART WITH 1; 
    100 ALTER SEQUENCE tj_stationmesure_stm_stm_identifiant_seq RESTART WITH 1; 
    101 ALTER SEQUENCE tr_valeurparametrequalitatif_val_val_identifiant_seq RESTART WITH 1; 
    102 ALTER SEQUENCE t_bilanmigrationjournalier_bjo_bjo_identifiant_seq RESTART WITH 1; 
    103 ALTER SEQUENCE t_bilanmigrationmensuel_bme_bme_identifiant_seq RESTART WITH 1; 
    104  
    105  
    106 CREATE ROLE logrami LOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; 
    107 GRANT ALL ON SCHEMA logrami TO logrami; 
    108 GRANT SELECT,INSERT,UPDATE ON logrami.t_station_sta TO logrami; 
    109 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_bilanmigrationjournalier_bjo TO logrami; 
    110 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_bilanmigrationmensuel_bme TO logrami; 
    111 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_caracteristiquelot_car TO logrami; 
    112 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_conditionenvironnementale_env TO logrami; 
    113 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_stationmesure_stm TO logrami; 
    114 GRANT SELECT ON ref.tr_valeurparametrequalitatif_val TO logrami; 
    115 GRANT SELECT ON ref.tr_parametrequalitatif_qal TO logrami; 
    116 GRANT SELECT ON ref.tr_parametrequantitatif_qan TO logrami; 
    117 GRANT SELECT ON ref.tg_parametre_par TO logrami; 
    118 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_dfesttype_dft TO logrami; 
    119 GRANT SELECT ON ref.tr_typedf_tdf TO logrami; 
    120 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_dfestdestinea_dtx TO logrami; 
    121 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_tauxechappement_txe TO logrami; 
    122 GRANT SELECT ON ref.tr_niveauechappement_ech TO logrami; 
    123 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_coefficientconversion_coe TO logrami; 
    124 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_pathologieconstatee_pco TO logrami; 
    125 GRANT SELECT ON ref.tr_pathologie_pat TO logrami; 
    126 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_actionmarquage_act TO logrami; 
    127 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_marque_mqe TO logrami; 
    128 GRANT SELECT ON ref.tr_localisationanatomique_loc TO logrami; 
    129 GRANT SELECT ON ref.tr_naturemarque_nmq TO logrami; 
    130 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_operationmarquage_omq TO logrami; 
    131 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_lot_lot TO logrami; 
    132 GRANT SELECT ON ref.tr_devenirlot_dev TO logrami; 
    133 GRANT SELECT ON ref.tr_typequantitelot_qte TO logrami; 
    134 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.ts_taxonvideo_txv TO logrami; 
    135 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.ts_taillevideo_tav TO logrami; 
    136 GRANT SELECT ON ref.tr_stadedeveloppement_std TO logrami; 
    137 GRANT SELECT ON ref.tr_taxon_tax TO logrami; 
    138 GRANT SELECT ON ref.tr_niveautaxonomique_ntx TO logrami; 
    139 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_operation_ope TO logrami; 
    140 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_periodefonctdispositif_per TO logrami; 
    141 GRANT SELECT ON ref.tr_typearretdisp_tar TO logrami; 
    142 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_dispositifcomptage_dic TO logrami; 
    143 GRANT SELECT ON ref.tr_typedc_tdc TO logrami; 
    144 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_dispositiffranchissement_dif TO logrami; 
    145 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tg_dispositif_dis TO logrami; 
    146 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_ouvrage_ouv TO logrami; 
    147 GRANT SELECT ON ref.tr_natureouvrage_nov TO logrami; 
    148 GRANT SELECT ON ref.tr_prelevement_pre TO logrami; 
    149 GRANT SELECT,INSERT,UPDATE,DELETE ON ref.ts_sequence_seq TO logrami; 
    150 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_stationmesure_stm TO logrami; 
    151 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_prelevementlot_prl TO logrami; 
    152 GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_marque_mqe TO logrami; 
    153  
    154 GRANT SELECT, UPDATE ON logrami.tg_dispositif_dis_dis_identifiant_seq TO logrami; 
    155 GRANT SELECT, UPDATE ON logrami.t_lot_lot_lot_identifiant_seq TO logrami; 
    156 GRANT SELECT, UPDATE ON logrami.t_operation_ope_ope_identifiant_seq TO logrami; 
    157 GRANT SELECT, UPDATE ON logrami.t_ouvrage_ouv_ouv_identifiant_seq TO logrami; 
    158 GRANT SELECT, UPDATE ON logrami.tj_stationmesure_stm_stm_identifiant_seq TO logrami; 
    159 GRANT SELECT, UPDATE ON logrami.t_bilanmigrationmensuel_bme_bme_identifiant_seq TO logrami; 
    160 GRANT SELECT, UPDATE ON logrami.t_bilanmigrationjournalier_bjo_bjo_identifiant_seq TO logrami; 
    161  
    162 GRANT SELECT ON ref.ts_sequence_seq TO logrami; 
    163 GRANT SELECT ON TABLE ref.ts_sequence_seq TO invite; 
    164  
    165 -- vues 
    166 GRANT SELECT, UPDATE, INSERT ON TABLE logrami.v_taxon_tax TO logrami; 
    167 GRANT SELECT, UPDATE, INSERT ON TABLE logrami.vue_lot_ope_car TO logrami; 
    168 GRANT SELECT, UPDATE, INSERT ON TABLE logrami.vue_lot_ope_car_qan TO logrami; 
    169 GRANT SELECT, UPDATE, INSERT ON TABLE logrami.vue_ope_lot_ech_parqual TO logrami; 
    170 GRANT SELECT, UPDATE, INSERT ON TABLE logrami.vue_ope_lot_ech_parquan TO logrami; 
    171  
    172  
    173 -- GRANT select to invite for logrami 
    174 GRANT SELECT ON logrami.t_station_sta TO invite; 
    175 GRANT SELECT ON logrami.t_bilanmigrationjournalier_bjo TO invite; 
    176 GRANT SELECT ON logrami.t_bilanmigrationmensuel_bme TO invite; 
    177 GRANT SELECT ON logrami.tj_caracteristiquelot_car TO invite; 
    178 GRANT SELECT ON logrami.tj_conditionenvironnementale_env TO invite; 
    179 GRANT SELECT ON logrami.tj_stationmesure_stm TO invite; 
    180 GRANT SELECT ON logrami.tj_dfesttype_dft TO invite; 
    181 GRANT SELECT ON logrami.tj_dfestdestinea_dtx TO invite; 
    182 GRANT SELECT ON logrami.tj_tauxechappement_txe TO invite; 
    183 GRANT SELECT ON logrami.tj_coefficientconversion_coe TO invite; 
    184 GRANT SELECT ON logrami.tj_pathologieconstatee_pco TO invite; 
    185 GRANT SELECT ON logrami.tj_actionmarquage_act TO invite; 
    186 GRANT SELECT ON logrami.t_marque_mqe TO invite; 
    187 GRANT SELECT ON logrami.t_operationmarquage_omq TO invite; 
    188 GRANT SELECT ON logrami.t_lot_lot TO invite; 
    189 GRANT SELECT ON logrami.ts_taxonvideo_txv TO invite; 
    190 GRANT SELECT ON logrami.ts_taillevideo_tav TO invite; 
    191 GRANT SELECT ON logrami.t_operation_ope TO invite; 
    192 GRANT SELECT ON logrami.t_periodefonctdispositif_per TO invite; 
    193 GRANT SELECT ON logrami.t_dispositifcomptage_dic TO invite; 
    194 GRANT SELECT ON logrami.t_dispositiffranchissement_dif TO invite; 
    195 GRANT SELECT ON logrami.tg_dispositif_dis TO invite; 
    196 GRANT SELECT ON logrami.t_ouvrage_ouv TO invite; 
    197 GRANT SELECT ON logrami.t_station_sta TO invite; 
    198 GRANT SELECT ON logrami.tj_stationmesure_stm TO invite; 
    199 GRANT SELECT ON logrami.tj_prelevementlot_prl TO invite; 
    200 GRANT SELECT ON logrami.t_marque_mqe TO invite; 
    201 --vues 
    202 GRANT SELECT ON logrami.v_taxon_tax TO invite; 
    203 GRANT SELECT ON logrami.vue_lot_ope_car TO invite; 
    204 GRANT SELECT ON logrami.vue_lot_ope_car_qan TO invite; 
    205 GRANT SELECT ON logrami.vue_ope_lot_ech_parqual TO invite; 
    206 GRANT SELECT ON logrami.vue_ope_lot_ech_parquan TO invite; 
    207  
    208  
    209 GRANT SELECT, UPDATE ON logrami.tg_dispositif_dis_dis_identifiant_seq TO invite; 
    210 GRANT SELECT, UPDATE ON logrami.t_lot_lot_lot_identifiant_seq TO invite; 
    211 GRANT SELECT, UPDATE ON logrami.t_operation_ope_ope_identifiant_seq TO invite; 
    212 GRANT SELECT, UPDATE ON logrami.t_ouvrage_ouv_ouv_identifiant_seq TO invite; 
    213 GRANT SELECT, UPDATE ON logrami.tj_stationmesure_stm_stm_identifiant_seq TO invite; 
    214 GRANT SELECT, UPDATE ON logrami.t_bilanmigrationmensuel_bme_bme_identifiant_seq TO invite; 
    215 GRANT SELECT, UPDATE ON logrami.t_bilanmigrationjournalier_bjo_bjo_identifiant_seq TO invite;