mySQL : dump routines and commit recent minor changes

3 files changed, 82 insertions, 11 deletions

diff --git a/schema/dump-it.sh b/schema/dump-it.sh
index eb9ab84..d3cfba4 100755
--- a/schema/dump-it.sh
+++ b/schema/dump-it.sh
@@ -32,6 +32,6 @@ service_types
 mysqldump --defaults-file=/etc/mysql/debian.cnf gestion \
 	$enum_tables > enum-tables-data-mysql.sql
 
-mysqldump --defaults-file=/etc/mysql/debian.cnf --no-data gestion \
+mysqldump --defaults-file=/etc/mysql/debian.cnf --no-data --routines gestion \
 	| sed -e 's/AUTO_INCREMENT=[0-9]\+ //g' > schema-mysql.sql
 
diff --git a/schema/enum-tables-data-mysql.sql b/schema/enum-tables-data-mysql.sql
index 84a1468..3ba1971 100644
--- a/schema/enum-tables-data-mysql.sql
+++ b/schema/enum-tables-data-mysql.sql
@@ -127,7 +127,7 @@ CREATE TABLE `equipement_modeles` (
   `profile` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
   `revision` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Modèles d''équipements';
+) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Modèles d''équipements';
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -136,7 +136,7 @@ CREATE TABLE `equipement_modeles` (
 
 LOCK TABLES `equipement_modeles` WRITE;
 /*!40000 ALTER TABLE `equipement_modeles` DISABLE KEYS */;
-INSERT INTO `equipement_modeles` VALUES (1,'Mikrotik','RouterBoard 750UP','',''),(2,'TP-Link','TL-WR740ND v??','TLWR740','4'),(3,'TP-Link','TL-WR740ND v1-3','',''),(4,'TP-Link','TL-WR740ND v4','TLWR740','4'),(5,'TP-Link','TL-WR740ND v5.0','TLWR740','5'),(6,'TP-Link','TL-WR841ND v9','TLWR841','9'),(7,'TP-Link','TL-WR841ND v10','TLWR841','10'),(8,'TP-Link','TL-WR841ND v11','','11'),(10,'Ubiquiti','AirFiber 5X + AF-5G30-S45','',''),(11,'Ubiquiti','AirFiber 5X + Mars 25dBi','',''),(12,'Ubiquiti','NanoBeam M5 16','',''),(13,'Ubiquiti','NanoBeam M5 19','',''),(14,'Ubiquiti','NanoBridge M5 G22','',''),(15,'Ubiquiti','NanoBridge M5 G25','',''),(16,'Ubiquiti','Nanostation M5','',''),(17,'Ubiquiti','Nanostation M5 Loco','',''),(18,'Ubiquiti','PowerBeam M5 400','',''),(19,'Ubiquiti','Rocket M5','',''),(20,'Ubiquiti','ToughSwitch TS5','',''),(21,'Ubiquiti','ToughSwitch TS8','',''),(40,'Netonix','WS-6-MINI','',''),(41,'Mikrotik','RB2011UiAS-2HnD-IN',' ',' '),(42,'Ubiquiti','EdgeMax EdgeSwitch 24-Port 250W','',''),(43,'VM','Linux KVM','',''),(44,'Netonix','8 ports','',''),(45,'Ubiquiti','Nanostation M2 Loco','',''),(46,'TP-Link','TL-WR740ND v5.1','TLWR740','');
+INSERT INTO `equipement_modeles` VALUES (2,'TP-Link','TL-WR740ND v??','TLWR740','4'),(3,'TP-Link','TL-WR740ND v1-3','',''),(4,'TP-Link','TL-WR740ND v4','TLWR740','4'),(5,'TP-Link','TL-WR740ND v5.0','TLWR740','5'),(6,'TP-Link','TL-WR740ND v5.1','TLWR740','51'),(7,'TP-Link','TL-WR740ND v6','TLWR740','6'),(8,'TP-Link','TL-WR841ND v9','TLWR841','9'),(9,'TP-Link','TL-WR841ND v10','TLWR841','10'),(10,'TP-Link','TL-WR841ND v11','','11'),(11,'TP-Link','TL-WR1043ND v1.10','','110'),(12,'Ubiquiti','NanoBeam M5 16','',''),(13,'Ubiquiti','NanoBeam M5 19','',''),(14,'Ubiquiti','NanoBridge M5 G22','',''),(15,'Ubiquiti','NanoBridge M5 G25','',''),(16,'Ubiquiti','Nanostation M5','',''),(17,'Ubiquiti','Nanostation M5 Loco','',''),(18,'Ubiquiti','PowerBeam M5 400','',''),(19,'Ubiquiti','Rocket M5','',''),(20,'Ubiquiti','AirFiber 5X + Mars 25dBi','',''),(21,'Ubiquiti','AirFiber 5X + AF-5G30-S45','',''),(22,'Ubiquiti','Nanostation M2 Loco','',''),(30,'Ubiquiti','ToughSwitch TS5','',''),(31,'Ubiquiti','ToughSwitch TS8','',''),(32,'Ubiquiti','EdgeMax EdgeSwitch 24-Port 250W','',''),(40,'Netonix','WS-6-MINI','',''),(41,'Netonix','8 ports','',''),(45,'Mikrotik','RouterBoard 750UP','',''),(46,'Mikrotik','RB2011UiAS-2HnD-IN',' ',' '),(47,'Routeur adhérent custom','-','',''),(48,'VM','Linux KVM','','');
 /*!40000 ALTER TABLE `equipement_modeles` ENABLE KEYS */;
 UNLOCK TABLES;
 
@@ -222,7 +222,9 @@ DROP TABLE IF EXISTS `service_statuts`;
 CREATE TABLE `service_statuts` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
-  PRIMARY KEY (`id`)
+  `ident` varchar(7) COLLATE utf8_unicode_ci DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `ident` (`ident`)
 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
@@ -232,7 +234,7 @@ CREATE TABLE `service_statuts` (
 
 LOCK TABLES `service_statuts` WRITE;
 /*!40000 ALTER TABLE `service_statuts` DISABLE KEYS */;
-INSERT INTO `service_statuts` VALUES (-1,'Inconnu'),(1,'Prévu'),(2,'Actif TTN'),(3,'Migré'),(4,'Actif CHD'),(5,'Suspendu'),(6,'Résilié');
+INSERT INTO `service_statuts` VALUES (-1,'Inconnu','unknown'),(1,'Prévu','planned'),(2,'Actif TTN','ttn_a'),(3,'Migré','ttn_m'),(4,'Actif CHD','active'),(5,'Suspendu','suspend'),(6,'Résilié','cancel');
 /*!40000 ALTER TABLE `service_statuts` ENABLE KEYS */;
 UNLOCK TABLES;
 
@@ -273,4 +275,4 @@ UNLOCK TABLES;
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
--- Dump completed on 2016-06-12 16:55:19
+-- Dump completed on 2016-08-28 10:56:15
diff --git a/schema/schema-mysql.sql b/schema/schema-mysql.sql
index e107490..831fbcc 100644
--- a/schema/schema-mysql.sql
+++ b/schema/schema-mysql.sql
@@ -383,7 +383,9 @@ DROP TABLE IF EXISTS `service_statuts`;
 CREATE TABLE `service_statuts` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
-  PRIMARY KEY (`id`)
+  `ident` varchar(7) COLLATE utf8_unicode_ci DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `ident` (`ident`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
@@ -465,13 +467,14 @@ DROP TABLE IF EXISTS `v_api_gen_conf`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v_api_gen_conf` (
-  `do_conf` tinyint NOT NULL,
   `etat` tinyint NOT NULL,
   `svc_id` tinyint NOT NULL,
   `ip4_nexthop` tinyint NOT NULL,
   `ip4_public` tinyint NOT NULL,
   `ip6_nexthop` tinyint NOT NULL,
-  `ip6_prefix` tinyint NOT NULL
+  `ip6_prefix` tinyint NOT NULL,
+  `mac_nexthop` tinyint NOT NULL,
+  `modified_ts` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 
@@ -612,6 +615,72 @@ CREATE TABLE `villes` (
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
+-- Dumping routines for database 'gestion'
+--
+/*!50003 DROP FUNCTION IF EXISTS `CALC_WANIP6LL` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = '' */ ;
+DELIMITER ;;
+CREATE DEFINER=`gestion`@`localhost` FUNCTION `CALC_WANIP6LL`(`ip6_prefix` VARCHAR(39) CHARSET utf8) RETURNS varchar(39) CHARSET utf8 COLLATE utf8_unicode_ci
+    NO SQL
+    DETERMINISTIC
+BEGIN

+    DECLARE len INT(11);

+    SET len = LENGTH(ip6_prefix);

+    

+	RETURN IF(ip6_prefix REGEXP ':....:....::$',

+              CONCAT('fe80::',RIGHT(LEFT(ip6_prefix,len-2),9)),

+              NULL

+             );

+END ;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 DROP FUNCTION IF EXISTS `FREE_IP` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = '' */ ;
+DELIMITER ;;
+CREATE DEFINER=`gestion`@`localhost` FUNCTION `FREE_IP`(`arg_service_id` INT(11)) RETURNS varchar(15) CHARSET utf8 COLLATE utf8_unicode_ci
+    NO SQL
+BEGIN

+

+DECLARE res VARCHAR(15);

+

+SELECT ip.ip4 INTO res

+FROM ippubliques ip

+LEFT JOIN services s ON (s.ippublique_id = ip.ip4)

+WHERE s.id IS NULL

+AND ip.secteur_id = (

+  SELECT secteur_id FROM v_relais_detail

+  WHERE service_id=arg_service_id

+)

+AND ip.secteur_id IS NOT NULL

+ORDER BY INET_ATON(ip.ip4)

+LIMIT 1;

+

+RETURN res;

+END ;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+
+--
 -- Final view structure for view `v_adt_sms`
 --
 
@@ -644,7 +713,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('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 VIEW `v_api_gen_conf` AS select `ss`.`ident` 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`,NULL AS `mac_nexthop`,unix_timestamp(`s`.`modified`) AS `modified_ts` from (((((`services` `s` left join `service_types` `st` on((`st`.`id` = `s`.`service_type_id`))) left join `service_statuts` `ss` on((`ss`.`id` = `s`.`service_statut_id`))) left join `ippubliques` `ipp` on((`ipp`.`ip4` = `s`.`ippublique_id`))) left join `adherents` `a` on((`a`.`id` = `s`.`adherent_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 (4,5,6)) and (`ipp`.`ip4` is not null)) order by `ss`.`ident` desc,`s`.`id` */;
 /*!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 +860,4 @@ CREATE TABLE `villes` (
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
--- Dump completed on 2016-06-12 16:55:19
+-- Dump completed on 2016-08-28 10:56:15