diff options
Diffstat (limited to 'schema/schema-mysql.sql')
| -rw-r--r-- | schema/schema-mysql.sql | 79 |
1 files changed, 74 insertions, 5 deletions
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 |