Ipmgmt : ip4 as key, ip6 deleted.

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