diff options
Diffstat (limited to 'schema/schema-mysql.sql')
| -rw-r--r-- | schema/schema-mysql.sql | 10 |
1 files changed, 5 insertions, 5 deletions
diff --git a/schema/schema-mysql.sql b/schema/schema-mysql.sql index 21fc549..e107490 100644 --- a/schema/schema-mysql.sql +++ b/schema/schema-mysql.sql @@ -178,11 +178,11 @@ DROP TABLE IF EXISTS `equipements`; CREATE TABLE `equipements` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uplink_id` int(11) DEFAULT NULL, + `service_id` int(11) DEFAULT NULL, `mac` char(17) COLLATE utf8_unicode_ci NOT NULL, - `ipmgmt_id` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL, `hostname` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `equipement_modele_id` int(11) NOT NULL, - `service_id` int(11) DEFAULT NULL, + `ipmgmt_id` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL, `relais_id` int(11) DEFAULT NULL, `equipement_mode_id` char(8) COLLATE utf8_unicode_ci NOT NULL, `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, @@ -644,7 +644,7 @@ CREATE TABLE `villes` ( /*!50001 SET collation_connection = utf8mb4_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`gestion`@`localhost` SQL SECURITY DEFINER */ -/*!50001 VIEW `v_api_gen_conf` AS select 'do_conf' AS `do_conf`,'actif' AS `etat`,concat_ws('-','SER',`s`.`service_type_id`,`a`.`id`,`s`.`id`) AS `svc_id`,`routeur`.`ipmgmt_id` AS `ip4_nexthop`,`ipp`.`ip4` AS `ip4_public`,`CALC_WANIP6LL`(`ipp`.`ip6`) AS `ip6_nexthop`,`ipp`.`ip6` AS `ip6_prefix` from ((((`services` `s` left join `service_types` `st` on((`st`.`id` = `s`.`service_type_id`))) left join `ippubliques` `ipp` on((`ipp`.`ip4` = `s`.`ippublique_id`))) left join `adherents` `a` on((`s`.`adherent_id` = `a`.`id`))) left join `equipements` `routeur` on(((`routeur`.`service_id` = `s`.`id`) and (`routeur`.`equipement_mode_id` in ('rt-soho','rt-sta'))))) where ((`s`.`service_type_id` in (2,3,4)) and (`s`.`service_statut_id` in (2,4)) and (`a`.`adherent_statut_id` <> 1)) */; +/*!50001 VIEW `v_api_gen_conf` AS select 'do_conf' AS `do_conf`,'actif' AS `etat`,concat('SER-',`s`.`id`) AS `svc_id`,`routeur`.`ipmgmt_id` AS `ip4_nexthop`,`ipp`.`ip4` AS `ip4_public`,`CALC_WANIP6LL`(`ipp`.`ip6`) AS `ip6_nexthop`,`ipp`.`ip6` AS `ip6_prefix` from ((((`services` `s` left join `service_types` `st` on((`st`.`id` = `s`.`service_type_id`))) left join `ippubliques` `ipp` on((`ipp`.`ip4` = `s`.`ippublique_id`))) left join `adherents` `a` on((`s`.`adherent_id` = `a`.`id`))) left join `equipements` `routeur` on(((`routeur`.`service_id` = `s`.`id`) and (`routeur`.`equipement_mode_id` in ('rt-soho','rt-sta'))))) where ((`s`.`service_type_id` in (2,3,4)) and (`s`.`service_statut_id` in (2,4)) and (`a`.`adherent_statut_id` <> 1)) */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; @@ -701,7 +701,7 @@ CREATE TABLE `villes` ( /*!50001 SET collation_connection = utf8mb4_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`gestion`@`localhost` SQL SECURITY DEFINER */ -/*!50001 VIEW `v_relais_detail` AS select `re`.`title` AS `relais`,concat('CHD-',`a`.`id`) AS `adt`,concat_ws(' ',nullif(`a`.`raison`,''),`a`.`nom`,`a`.`prenom`) AS `adt_title`,`a`.`id` AS `adherent_id`,`s`.`id` AS `service_id`,concat('SER-',`s`.`service_type_id`,'-',`s`.`adherent_id`,'-',`s`.`id`) AS `service`,`rt`.`id` AS `routeur_equipement_id`,`a1`.`id` AS `antenne1_equipement_id`,`a2`.`id` AS `antenne2_equipement_id`,`re`.`id` AS `relais_id`,(case when (`rt`.`relais_id` is not null) then 1 when (`a1`.`relais_id` is not null) then 2 when (`a2`.`relais_id` is not null) then 3 else NULL end) AS `relais_degre`,`sec`.`id` AS `secteur_id` from ((((((`adherents` `a` left join `services` `s` on((`s`.`adherent_id` = `a`.`id`))) left join `equipements` `rt` on((`rt`.`service_id` = `s`.`id`))) left join `equipements` `a1` on((`a1`.`id` = `rt`.`uplink_id`))) left join `equipements` `a2` on((`a2`.`id` = `a1`.`uplink_id`))) left join `relais` `re` on((`re`.`id` = coalesce(`rt`.`relais_id`,`a1`.`relais_id`,`a2`.`relais_id`)))) left join `secteurs` `sec` on((`sec`.`id` = `re`.`secteur_id`))) where ((`a`.`adherent_statut_id` <> 1) and (`s`.`service_type_id` in (2,3,4)) and (`s`.`service_statut_id` in (1,2,4))) order by 1 */; +/*!50001 VIEW `v_relais_detail` AS select `re`.`title` AS `relais`,concat('CHD-',`a`.`id`) AS `adt`,concat_ws(' ',nullif(`a`.`raison`,''),`a`.`nom`,`a`.`prenom`) AS `adt_title`,`a`.`id` AS `adherent_id`,`s`.`id` AS `service_id`,concat('SER-',`s`.`id`) AS `service`,`rt`.`id` AS `routeur_equipement_id`,`a1`.`id` AS `antenne1_equipement_id`,`a2`.`id` AS `antenne2_equipement_id`,`re`.`id` AS `relais_id`,(case when (`rt`.`relais_id` is not null) then 1 when (`a1`.`relais_id` is not null) then 2 when (`a2`.`relais_id` is not null) then 3 else NULL end) AS `relais_degre`,`sec`.`id` AS `secteur_id` from ((((((`adherents` `a` left join `services` `s` on((`s`.`adherent_id` = `a`.`id`))) left join `equipements` `rt` on((`rt`.`service_id` = `s`.`id`))) left join `equipements` `a1` on((`a1`.`id` = `rt`.`uplink_id`))) left join `equipements` `a2` on((`a2`.`id` = `a1`.`uplink_id`))) left join `relais` `re` on((`re`.`id` = coalesce(`rt`.`relais_id`,`a1`.`relais_id`,`a2`.`relais_id`)))) left join `secteurs` `sec` on((`sec`.`id` = `re`.`secteur_id`))) where ((`a`.`adherent_statut_id` <> 1) and (`s`.`service_type_id` in (2,3,4)) and (`s`.`service_statut_id` in (1,2,4))) order by 1 */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; @@ -791,4 +791,4 @@ CREATE TABLE `villes` ( /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2016-05-22 12:10:25 +-- Dump completed on 2016-06-12 16:55:19 |