From 5709f164402a76dfd0e6ef098a45c73f976b48cd Mon Sep 17 00:00:00 2001 From: Ludovic Pouzenc Date: Thu, 21 Jun 2018 22:02:28 +0200 Subject: schema update : more views for api + more enums --- schema/enum-tables-data-mysql.sql | 14 +-- schema/schema-mysql.sql | 181 +++++++++++++++++++++++++++++++++++++- 2 files changed, 185 insertions(+), 10 deletions(-) diff --git a/schema/enum-tables-data-mysql.sql b/schema/enum-tables-data-mysql.sql index 32a531d..667a9a0 100644 --- a/schema/enum-tables-data-mysql.sql +++ b/schema/enum-tables-data-mysql.sql @@ -1,8 +1,8 @@ --- MySQL dump 10.13 Distrib 5.5.47, for debian-linux-gnu (x86_64) +-- MySQL dump 10.16 Distrib 10.1.26-MariaDB, for debian-linux-gnu (x86_64) -- -- Host: localhost Database: gestion -- ------------------------------------------------------ --- Server version 5.5.47-0+deb8u1 +-- Server version 10.1.26-MariaDB-0+deb9u1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -52,7 +52,7 @@ CREATE TABLE `adherent_statuts` ( `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `title` (`title`) -) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -61,7 +61,7 @@ CREATE TABLE `adherent_statuts` ( LOCK TABLES `adherent_statuts` WRITE; /*!40000 ALTER TABLE `adherent_statuts` DISABLE KEYS */; -INSERT INTO `adherent_statuts` VALUES (2,'☐ Fiche ☐ Prélev'),(4,'☐ Fiche ☑ Prélev'),(3,'☑ Fiche ☐ Prélev'),(5,'☑ Fiche ☑ Prélev'),(7,'À relancer'),(8,'Bénévole sans service'),(6,'Incohérent'),(1,'Résilié'); +INSERT INTO `adherent_statuts` VALUES (2,'☐ Fiche ☐ Prélev'),(4,'☐ Fiche ☑ Prélev'),(3,'☑ Fiche ☐ Prélev'),(5,'☑ Fiche ☑ Prélev'),(7,'À relancer'),(8,'Bénévole sans service'),(6,'Incohérent'),(1,'Résilié'),(9,'Restauré'); /*!40000 ALTER TABLE `adherent_statuts` ENABLE KEYS */; UNLOCK TABLES; @@ -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=49 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Modèles d''équipements'; +) ENGINE=InnoDB AUTO_INCREMENT=55 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 (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','',''); +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','TLWR841','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','',''),(49,'Synology','RT1900ac','',''),(50,'Ubiquiti','LiteBeam AC 120°','',''),(51,'Ubiquiti','Nanobeam 5AC 19','',''),(52,'Ubiquiti','PowerBeam 5AC 25','',''),(53,'Ubiquiti','PowerBeam 5AC Gen2','',''),(54,'Ubiquiti','EdgePoint 16 ports','',''); /*!40000 ALTER TABLE `equipement_modeles` ENABLE KEYS */; UNLOCK TABLES; @@ -275,4 +275,4 @@ UNLOCK TABLES; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2016-08-29 20:20:38 +-- Dump completed on 2018-06-21 22:00:27 diff --git a/schema/schema-mysql.sql b/schema/schema-mysql.sql index 636ebd5..9a12d26 100644 --- a/schema/schema-mysql.sql +++ b/schema/schema-mysql.sql @@ -1,8 +1,8 @@ --- MySQL dump 10.13 Distrib 5.5.47, for debian-linux-gnu (x86_64) +-- MySQL dump 10.16 Distrib 10.1.26-MariaDB, for debian-linux-gnu (x86_64) -- -- Host: localhost Database: gestion -- ------------------------------------------------------ --- Server version 5.5.47-0+deb8u1 +-- Server version 10.1.26-MariaDB-0+deb9u1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -441,6 +441,21 @@ CREATE TABLE `services` ( /*!40101 SET character_set_client = @saved_cs_client */; -- +-- Table structure for table `tmp_mac_seen` +-- + +DROP TABLE IF EXISTS `tmp_mac_seen`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `tmp_mac_seen` ( + `ip` varchar(25) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, + `mac` varchar(17) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, + UNIQUE KEY `ip` (`ip`), + KEY `mac` (`mac`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- -- Temporary table structure for view `v_adt_sms` -- @@ -501,6 +516,75 @@ SET character_set_client = utf8; SET character_set_client = @saved_cs_client; -- +-- Temporary table structure for view `v_compta_svc_no_compte` +-- + +DROP TABLE IF EXISTS `v_compta_svc_no_compte`; +/*!50001 DROP VIEW IF EXISTS `v_compta_svc_no_compte`*/; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v_compta_svc_no_compte` ( + `service` tinyint NOT NULL, + `adt` tinyint NOT NULL, + `adt_title` tinyint NOT NULL, + `relais_id` tinyint NOT NULL, + `relais` tinyint NOT NULL, + `secteur_id` tinyint NOT NULL, + `secteur` tinyint NOT NULL, + `no_compte` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; + +-- +-- Temporary table structure for view `v_ippubliques_allocations` +-- + +DROP TABLE IF EXISTS `v_ippubliques_allocations`; +/*!50001 DROP VIEW IF EXISTS `v_ippubliques_allocations`*/; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v_ippubliques_allocations` ( + `secteur` tinyint NOT NULL, + `allouees` tinyint NOT NULL, + `utilisee` tinyint NOT NULL, + `neuves` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; + +-- +-- Temporary table structure for view `v_rapprochement_compta` +-- + +DROP TABLE IF EXISTS `v_rapprochement_compta`; +/*!50001 DROP VIEW IF EXISTS `v_rapprochement_compta`*/; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v_rapprochement_compta` ( + `svc_id` tinyint NOT NULL, + `svc_annee_premiere_saisie` tinyint NOT NULL, + `actif2015` tinyint NOT NULL, + `actif2016` tinyint NOT NULL, + `actif2017` tinyint NOT NULL, + `svc_derniere_modif` tinyint NOT NULL, + `adt` tinyint NOT NULL, + `adt_type` tinyint NOT NULL, + `raison` tinyint NOT NULL, + `nom` tinyint NOT NULL, + `prenom` tinyint NOT NULL, + `service_type` tinyint NOT NULL, + `prix_base_ht` tinyint NOT NULL, + `prix_ht` tinyint NOT NULL, + `periodicite` tinyint NOT NULL, + `date_debut` tinyint NOT NULL, + `date_fin` tinyint NOT NULL, + `statut` tinyint NOT NULL, + `proprio` tinyint NOT NULL, + `description` tinyint NOT NULL, + `notes` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; + +-- -- Temporary table structure for view `v_relais` -- @@ -598,6 +682,21 @@ SET character_set_client = utf8; SET character_set_client = @saved_cs_client; -- +-- Temporary table structure for view `v_tmp_diff_seen_mac` +-- + +DROP TABLE IF EXISTS `v_tmp_diff_seen_mac`; +/*!50001 DROP VIEW IF EXISTS `v_tmp_diff_seen_mac`*/; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v_tmp_diff_seen_mac` ( + `mac` tinyint NOT NULL, + `seen_ip` tinyint NOT NULL, + `gestion_mgmtip` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; + +-- -- Table structure for table `villes` -- @@ -738,6 +837,63 @@ DELIMITER ; /*!50001 SET collation_connection = @saved_col_connection */; -- +-- Final view structure for view `v_compta_svc_no_compte` +-- + +/*!50001 DROP TABLE IF EXISTS `v_compta_svc_no_compte`*/; +/*!50001 DROP VIEW IF EXISTS `v_compta_svc_no_compte`*/; +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET character_set_client = utf8mb4 */; +/*!50001 SET character_set_results = utf8mb4 */; +/*!50001 SET collation_connection = utf8mb4_general_ci */; +/*!50001 CREATE ALGORITHM=UNDEFINED */ +/*!50013 DEFINER=`gestion`@`localhost` SQL SECURITY INVOKER */ +/*!50001 VIEW `v_compta_svc_no_compte` AS select `v`.`service` AS `service`,`v`.`adt` AS `adt`,`v`.`adt_title` AS `adt_title`,`v`.`relais_id` AS `relais_id`,`r`.`title` AS `relais`,`v`.`secteur_id` AS `secteur_id`,`s`.`title` AS `secteur`,(41020039455 + `v`.`secteur_id`) AS `no_compte` from ((`v_relais_detail` `v` left join `relais` `r` on((`r`.`id` = `v`.`relais_id`))) left join `secteurs` `s` on((`s`.`id` = `v`.`secteur_id`))) order by `v`.`adherent_id` */; +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; + +-- +-- Final view structure for view `v_ippubliques_allocations` +-- + +/*!50001 DROP TABLE IF EXISTS `v_ippubliques_allocations`*/; +/*!50001 DROP VIEW IF EXISTS `v_ippubliques_allocations`*/; +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET character_set_client = utf8mb4 */; +/*!50001 SET character_set_results = utf8mb4 */; +/*!50001 SET collation_connection = utf8mb4_general_ci */; +/*!50001 CREATE ALGORITHM=UNDEFINED */ +/*!50013 DEFINER=`gestion`@`localhost` SQL SECURITY INVOKER */ +/*!50001 VIEW `v_ippubliques_allocations` AS select `sec`.`title` AS `secteur`,count(`i`.`ip4`) AS `allouees`,sum(if(isnull(`ser`.`id`),0,1)) AS `utilisee`,sum(if(isnull(`ser`.`id`),1,0)) AS `neuves` from ((`ippubliques` `i` left join `secteurs` `sec` on((`i`.`secteur_id` = `sec`.`id`))) left join `services` `ser` on((`i`.`ip4` = `ser`.`ippublique_id`))) where (`i`.`secteur_id` is not null) group by `sec`.`title` */; +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; + +-- +-- Final view structure for view `v_rapprochement_compta` +-- + +/*!50001 DROP TABLE IF EXISTS `v_rapprochement_compta`*/; +/*!50001 DROP VIEW IF EXISTS `v_rapprochement_compta`*/; +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET character_set_client = utf8mb4 */; +/*!50001 SET character_set_results = utf8mb4 */; +/*!50001 SET collation_connection = utf8mb4_general_ci */; +/*!50001 CREATE ALGORITHM=UNDEFINED */ +/*!50013 DEFINER=`gestion`@`localhost` SQL SECURITY DEFINER */ +/*!50001 VIEW `v_rapprochement_compta` AS select `s`.`id` AS `svc_id`,(case year(`s`.`created`) when 0 then 'import-TTN' else year(`s`.`created`) end) AS `svc_annee_premiere_saisie`,if(((('2015-01-01' < `s`.`date_fin`) or isnull(`s`.`date_fin`)) and ('2015-12-31' > `s`.`date_debut`)),'oui','non') AS `actif2015`,if(((('2016-01-01' < `s`.`date_fin`) or isnull(`s`.`date_fin`)) and ('2016-12-31' > `s`.`date_debut`)),'oui','non') AS `actif2016`,if(((('2017-01-01' < `s`.`date_fin`) or isnull(`s`.`date_fin`)) and ('2017-12-31' > `s`.`date_debut`)),'oui','non') AS `actif2017`,if((unix_timestamp(`s`.`modified`) < unix_timestamp('2015-01-01 00:00:00')),'jamais',`s`.`modified`) AS `svc_derniere_modif`,concat('CHD',`a`.`id`) AS `adt`,`at`.`title` AS `adt_type`,`a`.`raison` AS `raison`,`a`.`nom` AS `nom`,`a`.`prenom` AS `prenom`,`st`.`title` AS `service_type`,`st`.`prix_base_ht` AS `prix_base_ht`,`s`.`prix_ht` AS `prix_ht`,`p`.`title` AS `periodicite`,`s`.`date_debut` AS `date_debut`,`s`.`date_fin` AS `date_fin`,`sst`.`title` AS `statut`,`a`.`proprio` AS `proprio`,`s`.`description` AS `description`,`a`.`notes` AS `notes` from (((((`services` `s` left join `adherents` `a` on((`a`.`id` = `s`.`adherent_id`))) left join `adherent_types` `at` on((`at`.`id` = `a`.`adherent_type_id`))) left join `service_types` `st` on((`st`.`id` = `s`.`service_type_id`))) left join `service_statuts` `sst` on((`sst`.`id` = `s`.`service_statut_id`))) left join `periodicites` `p` on((`p`.`id` = `st`.`periodicite_id`))) where (`s`.`service_statut_id` <> 1) order by `a`.`id`,`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 */; + +-- -- Final view structure for view `v_relais` -- @@ -850,6 +1006,25 @@ DELIMITER ; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; + +-- +-- Final view structure for view `v_tmp_diff_seen_mac` +-- + +/*!50001 DROP TABLE IF EXISTS `v_tmp_diff_seen_mac`*/; +/*!50001 DROP VIEW IF EXISTS `v_tmp_diff_seen_mac`*/; +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET character_set_client = utf8mb4 */; +/*!50001 SET character_set_results = utf8mb4 */; +/*!50001 SET collation_connection = utf8mb4_unicode_ci */; +/*!50001 CREATE ALGORITHM=UNDEFINED */ +/*!50013 DEFINER=`gestion`@`localhost` SQL SECURITY INVOKER */ +/*!50001 VIEW `v_tmp_diff_seen_mac` AS select `s`.`mac` AS `mac`,`s`.`ip` AS `seen_ip`,`e`.`ipmgmt_id` AS `gestion_mgmtip` from (`tmp_mac_seen` `s` left join `equipements` `e` on((`e`.`mac` = `s`.`mac`))) where ((not((`s`.`ip` like '%::%'))) and (isnull(`e`.`ipmgmt_id`) or (`s`.`ip` <> `e`.`ipmgmt_id`))) order by 1,2 */; +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; @@ -860,4 +1035,4 @@ DELIMITER ; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2016-08-29 20:20:38 +-- Dump completed on 2018-06-21 22:00:27 -- cgit v1.1