From 5cdd55ebf2fec7ab72864ce852de89055cf4b573 Mon Sep 17 00:00:00 2001 From: Ludovic Pouzenc Date: Sun, 28 Aug 2016 10:57:42 +0200 Subject: mySQL : dump routines and commit recent minor changes --- schema/schema-mysql.sql | 79 +++++++++++++++++++++++++++++++++++++++++++++---- 1 file changed, 74 insertions(+), 5 deletions(-) (limited to 'schema/schema-mysql.sql') 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 -- cgit v1.1