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/dump-it.sh | 2 +- schema/enum-tables-data-mysql.sql | 12 +++--- schema/schema-mysql.sql | 79 ++++++++++++++++++++++++++++++++++++--- 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 -- cgit v1.1