From 509b591aa18d6e67acb6df4270985ddfbb17b09a Mon Sep 17 00:00:00 2001 From: Ludovic Pouzenc Date: Sun, 27 Mar 2016 00:50:17 +0100 Subject: Ipmgmt : ip4 as key, ip6 deleted. --- schema/enum-tables-data-mysql.sql | 2 +- schema/schema-mysql.sql | 22 +++++++++------------- 2 files changed, 10 insertions(+), 14 deletions(-) diff --git a/schema/enum-tables-data-mysql.sql b/schema/enum-tables-data-mysql.sql index e83e881..4425055 100644 --- a/schema/enum-tables-data-mysql.sql +++ b/schema/enum-tables-data-mysql.sql @@ -273,4 +273,4 @@ UNLOCK TABLES; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2016-03-27 0:04:25 +-- Dump completed on 2016-03-27 0:49:48 diff --git a/schema/schema-mysql.sql b/schema/schema-mysql.sql index 908280a..8147536 100644 --- a/schema/schema-mysql.sql +++ b/schema/schema-mysql.sql @@ -180,7 +180,7 @@ CREATE TABLE `equipements` ( `mac` char(17) COLLATE utf8_unicode_ci NOT NULL, `uplink_id` int(11) DEFAULT NULL, `service_id` int(11) DEFAULT NULL, - `ipmgmt_id` int(11) DEFAULT NULL, + `ipmgmt_id` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL, `equipement_modele_id` int(11) NOT NULL, `equipement_mode_id` char(8) COLLATE utf8_unicode_ci NOT NULL, `relais_id` int(11) DEFAULT NULL, @@ -196,11 +196,11 @@ CREATE TABLE `equipements` ( KEY `equipement_modele_id` (`equipement_modele_id`), KEY `service_id` (`service_id`), KEY `uplink_id` (`uplink_id`), + CONSTRAINT `equipements_ibfk_9` FOREIGN KEY (`ipmgmt_id`) REFERENCES `ipmgmt` (`ip4`) ON UPDATE CASCADE, CONSTRAINT `equipements_ibfk_1` FOREIGN KEY (`equipement_modele_id`) REFERENCES `equipement_modeles` (`id`) ON UPDATE CASCADE, CONSTRAINT `equipements_ibfk_4` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`), CONSTRAINT `equipements_ibfk_5` FOREIGN KEY (`relais_id`) REFERENCES `relais` (`id`) ON UPDATE CASCADE, CONSTRAINT `equipements_ibfk_6` FOREIGN KEY (`equipement_mode_id`) REFERENCES `equipement_modes` (`id`) ON UPDATE CASCADE, - CONSTRAINT `equipements_ibfk_7` FOREIGN KEY (`ipmgmt_id`) REFERENCES `ipmgmt` (`id`) ON UPDATE CASCADE, CONSTRAINT `equipements_ibfk_8` FOREIGN KEY (`uplink_id`) REFERENCES `equipements` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Matériel en circulation'; /*!40101 SET character_set_client = @saved_cs_client */; @@ -260,12 +260,9 @@ DROP TABLE IF EXISTS `ipmgmt`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `ipmgmt` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `ip4` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL, - `ip6` varchar(39) COLLATE utf8_unicode_ci DEFAULT NULL, - PRIMARY KEY (`id`), - UNIQUE KEY `ip` (`ip4`), - UNIQUE KEY `ip6` (`ip6`) + `ip4` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', + PRIMARY KEY (`ip4`), + UNIQUE KEY `ip` (`ip4`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; @@ -459,8 +456,7 @@ SET character_set_client = utf8; `profile` tinyint NOT NULL, `revision` tinyint NOT NULL, `ip6prefix` tinyint NOT NULL, - `ip4wan` tinyint NOT NULL, - `ip6wanll` tinyint NOT NULL + `ip4wan` tinyint NOT NULL ) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; @@ -609,7 +605,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`,`ipr`.`ip4` AS `ip4_nexthop`,`ipp`.`ip4` AS `ip4_public`,coalesce(`ipr`.`ip6`,`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'))))) left join `ipmgmt` `ipr` on((`ipr`.`id` = `routeur`.`ipmgmt_id`))) 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_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 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; @@ -628,7 +624,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_firm` AS select `s`.`ippublique_id` AS `ippublique_id`,`a`.`nom` AS `nom`,`a`.`prenom` AS `prenom`,`a`.`raison` AS `raison`,`em`.`constructeur` AS `constructeur`,`em`.`modele` AS `modele`,`em`.`profile` AS `profile`,`em`.`revision` AS `revision`,`ip`.`ip6` AS `ip6prefix`,`ip2`.`ip4` AS `ip4wan`,`ip2`.`ip6` AS `ip6wanll` from (((((`services` `s` left join `adherents` `a` on((`a`.`id` = `s`.`adherent_id`))) left join `ippubliques` `ip` on((`ip`.`ip4` = `s`.`ippublique_id`))) left join `equipements` `e` on((`e`.`service_id` = `s`.`id`))) left join `equipement_modeles` `em` on((`em`.`id` = `e`.`equipement_modele_id`))) left join `ipmgmt` `ip2` on((`ip2`.`id` = `e`.`ipmgmt_id`))) where ((`s`.`service_statut_id` = 4) and (`s`.`ippublique_id` is not null)) */; +/*!50001 VIEW `v_api_gen_firm` AS select `s`.`ippublique_id` AS `ippublique_id`,`a`.`nom` AS `nom`,`a`.`prenom` AS `prenom`,`a`.`raison` AS `raison`,`em`.`constructeur` AS `constructeur`,`em`.`modele` AS `modele`,`em`.`profile` AS `profile`,`em`.`revision` AS `revision`,`ip`.`ip6` AS `ip6prefix`,`e`.`ipmgmt_id` AS `ip4wan` from ((((`services` `s` left join `adherents` `a` on((`a`.`id` = `s`.`adherent_id`))) left join `ippubliques` `ip` on((`ip`.`ip4` = `s`.`ippublique_id`))) left join `equipements` `e` on((`e`.`service_id` = `s`.`id`))) left join `equipement_modeles` `em` on((`em`.`id` = `e`.`equipement_modele_id`))) where ((`s`.`service_statut_id` = 4) and (`s`.`ippublique_id` is not null)) */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; @@ -756,4 +752,4 @@ CREATE TABLE `villes` ( /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2016-03-27 0:04:25 +-- Dump completed on 2016-03-27 0:49:48 -- cgit v1.1