Update base : description 32->255 + extra cols on v_relai_details

+ add equipement_modele Netonix 8 ports

2 files changed, 46 insertions, 8 deletions

diff --git a/schema/enum-tables-data-mysql.sql b/schema/enum-tables-data-mysql.sql
index 4425055..1612dbc 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=44 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Modèles d''équipements';
+) ENGINE=InnoDB AUTO_INCREMENT=45 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','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','','');
+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','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','','');
 /*!40000 ALTER TABLE `equipement_modeles` ENABLE KEYS */;
 UNLOCK TABLES;
 
@@ -273,4 +273,4 @@ UNLOCK TABLES;
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
--- Dump completed on 2016-03-27  0:49:48
+-- Dump completed on 2016-04-23 14:09:11
diff --git a/schema/schema-mysql.sql b/schema/schema-mysql.sql
index 8147536..aea9c0d 100644
--- a/schema/schema-mysql.sql
+++ b/schema/schema-mysql.sql
@@ -184,7 +184,7 @@ CREATE TABLE `equipements` (
   `equipement_modele_id` int(11) NOT NULL,
   `equipement_mode_id` char(8) COLLATE utf8_unicode_ci NOT NULL,
   `relais_id` int(11) DEFAULT NULL,
-  `description` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
   `date_achat` date DEFAULT NULL,
   `date_hs` date DEFAULT NULL,
   `notes` text COLLATE utf8_unicode_ci,
@@ -196,14 +196,50 @@ CREATE TABLE `equipements` (
   KEY `equipement_modele_id` (`equipement_modele_id`),
   KEY `service_id` (`service_id`),
   KEY `uplink_id` (`uplink_id`),
-  CONSTRAINT `equipements_ibfk_9` FOREIGN KEY (`ipmgmt_id`) REFERENCES `ipmgmt` (`ip4`) ON UPDATE CASCADE,
   CONSTRAINT `equipements_ibfk_1` FOREIGN KEY (`equipement_modele_id`) REFERENCES `equipement_modeles` (`id`) ON UPDATE CASCADE,
   CONSTRAINT `equipements_ibfk_4` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`),
   CONSTRAINT `equipements_ibfk_5` FOREIGN KEY (`relais_id`) REFERENCES `relais` (`id`) ON UPDATE CASCADE,
   CONSTRAINT `equipements_ibfk_6` FOREIGN KEY (`equipement_mode_id`) REFERENCES `equipement_modes` (`id`) ON UPDATE CASCADE,
-  CONSTRAINT `equipements_ibfk_8` FOREIGN KEY (`uplink_id`) REFERENCES `equipements` (`id`) ON UPDATE CASCADE
+  CONSTRAINT `equipements_ibfk_8` FOREIGN KEY (`uplink_id`) REFERENCES `equipements` (`id`) ON UPDATE CASCADE,
+  CONSTRAINT `equipements_ibfk_9` FOREIGN KEY (`ipmgmt_id`) REFERENCES `ipmgmt` (`ip4`) ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Matériel en circulation';
 /*!40101 SET character_set_client = @saved_cs_client */;
+/*!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 ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`gestion`@`localhost`*/ /*!50003 TRIGGER `equipements_insert` BEFORE INSERT ON `equipements` FOR EACH ROW BEGIN

+	-- INSERT NULL instead of '' for VARCHAR columns (prevents UNIQUE FK FAIL)

+	SET NEW.ipmgmt_id = IF(NEW.ipmgmt_id = '', NULL, NEW.ipmgmt_id);

+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 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 ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`gestion`@`localhost`*/ /*!50003 TRIGGER `equipements_update` BEFORE UPDATE ON `equipements` FOR EACH ROW BEGIN

+	-- INSERT NULL instead of '' for VARCHAR columns (prevents UNIQUE FK FAIL)

+	SET NEW.ipmgmt_id = IF(NEW.ipmgmt_id = '', NULL, NEW.ipmgmt_id);

+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 */ ;
 
 --
 -- Table structure for table `interesse_statuts`
@@ -490,10 +526,12 @@ SET character_set_client = utf8;
   `adt_title` tinyint NOT NULL,
   `adherent_id` tinyint NOT NULL,
   `service_id` tinyint NOT NULL,
+  `service` tinyint NOT NULL,
   `routeur_equipement_id` tinyint NOT NULL,
   `antenne1_equipement_id` tinyint NOT NULL,
   `antenne2_equipement_id` tinyint NOT NULL,
   `relais_id` tinyint NOT NULL,
+  `relais_degre` tinyint NOT NULL,
   `secteur_id` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
@@ -662,7 +700,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_relais_detail` AS select `re`.`title` AS `relais`,concat('CHD-',`a`.`id`) AS `adt`,concat_ws(' ',nullif(`a`.`raison`,''),`a`.`nom`,`a`.`prenom`) AS `adt_title`,`a`.`id` AS `adherent_id`,`s`.`id` AS `service_id`,`rt`.`id` AS `routeur_equipement_id`,`a1`.`id` AS `antenne1_equipement_id`,`a2`.`id` AS `antenne2_equipement_id`,`re`.`id` AS `relais_id`,`sec`.`id` AS `secteur_id` from ((((((`adherents` `a` left join `services` `s` on((`s`.`adherent_id` = `a`.`id`))) left join `equipements` `rt` on((`rt`.`service_id` = `s`.`id`))) left join `equipements` `a1` on((`a1`.`id` = `rt`.`uplink_id`))) left join `equipements` `a2` on((`a2`.`id` = `a1`.`uplink_id`))) left join `relais` `re` on((`re`.`id` = coalesce(`rt`.`relais_id`,`a1`.`relais_id`,`a2`.`relais_id`)))) left join `secteurs` `sec` on((`sec`.`id` = `re`.`secteur_id`))) where ((`a`.`adherent_statut_id` <> 1) and (`s`.`service_type_id` in (2,3,4)) and (`s`.`service_statut_id` in (1,2,4))) order by 1 */;
+/*!50001 VIEW `v_relais_detail` AS select `re`.`title` AS `relais`,concat('CHD-',`a`.`id`) AS `adt`,concat_ws(' ',nullif(`a`.`raison`,''),`a`.`nom`,`a`.`prenom`) AS `adt_title`,`a`.`id` AS `adherent_id`,`s`.`id` AS `service_id`,concat('SER-',`s`.`service_type_id`,'-',`s`.`adherent_id`,'-',`s`.`id`) AS `service`,`rt`.`id` AS `routeur_equipement_id`,`a1`.`id` AS `antenne1_equipement_id`,`a2`.`id` AS `antenne2_equipement_id`,`re`.`id` AS `relais_id`,(case when (`rt`.`relais_id` is not null) then 1 when (`a1`.`relais_id` is not null) then 2 when (`a2`.`relais_id` is not null) then 3 else NULL end) AS `relais_degre`,`sec`.`id` AS `secteur_id` from ((((((`adherents` `a` left join `services` `s` on((`s`.`adherent_id` = `a`.`id`))) left join `equipements` `rt` on((`rt`.`service_id` = `s`.`id`))) left join `equipements` `a1` on((`a1`.`id` = `rt`.`uplink_id`))) left join `equipements` `a2` on((`a2`.`id` = `a1`.`uplink_id`))) left join `relais` `re` on((`re`.`id` = coalesce(`rt`.`relais_id`,`a1`.`relais_id`,`a2`.`relais_id`)))) left join `secteurs` `sec` on((`sec`.`id` = `re`.`secteur_id`))) where ((`a`.`adherent_statut_id` <> 1) and (`s`.`service_type_id` in (2,3,4)) and (`s`.`service_statut_id` in (1,2,4))) order by 1 */;
 /*!50001 SET character_set_client      = @saved_cs_client */;
 /*!50001 SET character_set_results     = @saved_cs_results */;
 /*!50001 SET collation_connection      = @saved_col_connection */;
@@ -752,4 +790,4 @@ CREATE TABLE `villes` (
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
--- Dump completed on 2016-03-27  0:49:48
+-- Dump completed on 2016-04-23 14:09:11