From 0b2eb60538411c1b5e554caf01c2fa51d07c30c8 Mon Sep 17 00:00:00 2001 From: Ludovic Pouzenc Date: Sat, 12 Mar 2016 00:31:17 +0100 Subject: Add SQL schema. --- schema/dump-it.sh | 21 ++ schema/schema-mysql.sql | 741 ++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 762 insertions(+) create mode 100755 schema/dump-it.sh create mode 100644 schema/schema-mysql.sql diff --git a/schema/dump-it.sh b/schema/dump-it.sh new file mode 100755 index 0000000..6e9ea5c --- /dev/null +++ b/schema/dump-it.sh @@ -0,0 +1,21 @@ +#!/bin/sh +# Copyright 2016 Ludovic Pouzenc +# Copyright 2016 Nicolas Goaziou +# +# This file is part of CHD Gestion. +# +# CHD Gestion is free software: you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation, either version 3 of the License, or +# (at your option) any later version. +# +# CHD Gestion is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with CHD Gestion. If not, see . +# +mysqldump --defaults-file=/etc/mysql/debian.cnf --no-data gestion \ + | sed -e 's/AUTO_INCREMENT=[0-9]\+ //g' > schema-mysql.sql diff --git a/schema/schema-mysql.sql b/schema/schema-mysql.sql new file mode 100644 index 0000000..06ec3e4 --- /dev/null +++ b/schema/schema-mysql.sql @@ -0,0 +1,741 @@ +-- MySQL dump 10.13 Distrib 5.5.47, for debian-linux-gnu (x86_64) +-- +-- Host: localhost Database: gestion +-- ------------------------------------------------------ +-- Server version 5.5.47-0+deb8u1 + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `adherent_role_types` +-- + +DROP TABLE IF EXISTS `adherent_role_types`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `adherent_role_types` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL, + `list_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `adherent_roles` +-- + +DROP TABLE IF EXISTS `adherent_roles`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `adherent_roles` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `adherent_id` int(11) NOT NULL, + `adherent_role_type_id` int(11) NOT NULL, + `ville_id` int(11) DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `id_adherent` (`adherent_id`), + KEY `id_adherent_role_type` (`adherent_role_type_id`), + KEY `id_ville` (`ville_id`), + CONSTRAINT `adherent_roles_ibfk_1` FOREIGN KEY (`adherent_id`) REFERENCES `adherents` (`id`) ON UPDATE CASCADE, + CONSTRAINT `adherent_roles_ibfk_2` FOREIGN KEY (`adherent_role_type_id`) REFERENCES `adherent_role_types` (`id`) ON UPDATE CASCADE, + CONSTRAINT `adherent_roles_ibfk_3` FOREIGN KEY (`ville_id`) REFERENCES `villes` (`id`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `adherent_statuts` +-- + +DROP TABLE IF EXISTS `adherent_statuts`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `adherent_statuts` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `title` (`title`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `adherent_types` +-- + +DROP TABLE IF EXISTS `adherent_types`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `adherent_types` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `adherents` +-- + +DROP TABLE IF EXISTS `adherents`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `adherents` ( + `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id = numéro ADT', + `nom` varchar(32) COLLATE utf8_unicode_ci NOT NULL, + `prenom` varchar(32) COLLATE utf8_unicode_ci NOT NULL, + `raison` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, + `tel_mobile1` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL, + `adherent_type_id` int(11) NOT NULL, + `ville_id` int(11) NOT NULL, + `adherent_statut_id` int(11) NOT NULL, + `civilite_id` int(11) NOT NULL, + `nom2` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, + `prenom2` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, + `mail1` varchar(48) COLLATE utf8_unicode_ci DEFAULT NULL, + `mail2` varchar(48) COLLATE utf8_unicode_ci DEFAULT NULL, + `adresse1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, + `adresse2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, + `tel_fixe1` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL, + `tel_fixe2` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL, + `tel_mobile2` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL, + `num_adt_ttn` char(6) COLLATE utf8_unicode_ci DEFAULT NULL, + `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date de la première saisie de l''adhérent', + `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date de la dernière modification de l''adhérent', + `proprio` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, + `notes` text COLLATE utf8_unicode_ci, + PRIMARY KEY (`id`), + KEY `ville` (`ville_id`), + KEY `adherent_type_id` (`adherent_type_id`), + KEY `civilite_id` (`civilite_id`), + KEY `adherent_statut` (`adherent_statut_id`), + CONSTRAINT `adherents_ibfk_1` FOREIGN KEY (`ville_id`) REFERENCES `villes` (`id`) ON UPDATE CASCADE, + CONSTRAINT `adherents_ibfk_2` FOREIGN KEY (`adherent_type_id`) REFERENCES `adherent_types` (`id`) ON UPDATE CASCADE, + CONSTRAINT `adherents_ibfk_3` FOREIGN KEY (`civilite_id`) REFERENCES `civilites` (`id`) ON UPDATE CASCADE, + CONSTRAINT `adherents_ibfk_4` FOREIGN KEY (`adherent_statut_id`) REFERENCES `adherent_statuts` (`id`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Adhérents'; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `civilites` +-- + +DROP TABLE IF EXISTS `civilites`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `civilites` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `equipement_modeles` +-- + +DROP TABLE IF EXISTS `equipement_modeles`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `equipement_modeles` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `constructeur` varchar(32) COLLATE utf8_unicode_ci NOT NULL, + `modele` varchar(32) COLLATE utf8_unicode_ci NOT NULL, + `profile` varchar(16) COLLATE utf8_unicode_ci NOT NULL, + `revision` varchar(8) COLLATE utf8_unicode_ci NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Modèles d''équipements'; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `equipement_modes` +-- + +DROP TABLE IF EXISTS `equipement_modes`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `equipement_modes` ( + `id` char(8) COLLATE utf8_unicode_ci NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `equipements` +-- + +DROP TABLE IF EXISTS `equipements`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `equipements` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `mac` char(17) COLLATE utf8_unicode_ci NOT NULL, + `uplink_id` int(11) DEFAULT NULL, + `service_id` int(11) DEFAULT NULL, + `ipmgmt_id` int(11) DEFAULT NULL, + `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, + `date_achat` date DEFAULT NULL, + `date_hs` date DEFAULT NULL, + `notes` text COLLATE utf8_unicode_ci, + PRIMARY KEY (`id`), + UNIQUE KEY `mac` (`mac`), + UNIQUE KEY `ipmgmt_id` (`ipmgmt_id`), + KEY `relais_id` (`relais_id`), + KEY `equipement_mode_id` (`equipement_mode_id`), + KEY `equipement_modele_id` (`equipement_modele_id`), + KEY `service_id` (`service_id`), + KEY `uplink_id` (`uplink_id`), + 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_7` FOREIGN KEY (`ipmgmt_id`) REFERENCES `ipmgmt` (`id`) ON UPDATE CASCADE, + CONSTRAINT `equipements_ibfk_8` FOREIGN KEY (`uplink_id`) REFERENCES `equipements` (`id`) 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 */; + +-- +-- Table structure for table `interesses` +-- + +DROP TABLE IF EXISTS `interesses`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `interesses` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `date_demande` date NOT NULL, + `civilite_id` int(11) NOT NULL, + `nom` varchar(32) COLLATE utf8_unicode_ci NOT NULL, + `prenom` varchar(32) COLLATE utf8_unicode_ci NOT NULL, + `raison` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, + `ville_id` int(11) NOT NULL, + `telephone` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL, + `mail` varchar(48) COLLATE utf8_unicode_ci DEFAULT NULL, + `adresse1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, + `adresse2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, + `notes` text COLLATE utf8_unicode_ci, + PRIMARY KEY (`id`), + UNIQUE KEY `id` (`id`), + KEY `civilite_id` (`civilite_id`), + KEY `ville_id` (`ville_id`), + CONSTRAINT `interesses_ibfk_1` FOREIGN KEY (`civilite_id`) REFERENCES `civilites` (`id`) ON UPDATE CASCADE, + CONSTRAINT `interesses_ibfk_2` FOREIGN KEY (`ville_id`) REFERENCES `villes` (`id`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `ipmgmt` +-- + +DROP TABLE IF EXISTS `ipmgmt`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `ipmgmt` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `ip4` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL, + `ip6` varchar(39) COLLATE utf8_unicode_ci DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `ip` (`ip4`), + UNIQUE KEY `ip6` (`ip6`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `ippubliques` +-- + +DROP TABLE IF EXISTS `ippubliques`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `ippubliques` ( + `ip4` varchar(15) COLLATE utf8_unicode_ci NOT NULL, + `ip6` varchar(39) COLLATE utf8_unicode_ci DEFAULT NULL, + `secteur_id` int(11) DEFAULT NULL, + PRIMARY KEY (`ip4`), + UNIQUE KEY `ip6` (`ip6`), + KEY `secteur_id` (`secteur_id`), + CONSTRAINT `ippubliques_ibfk_1` FOREIGN KEY (`secteur_id`) REFERENCES `secteurs` (`id`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `periodicites` +-- + +DROP TABLE IF EXISTS `periodicites`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `periodicites` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `relais` +-- + +DROP TABLE IF EXISTS `relais`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `relais` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL, + `ville_id` int(11) NOT NULL, + `secteur_id` int(11) NOT NULL, + `gps_long` float(10,7) NOT NULL, + `gps_lat` float(10,7) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `title` (`title`), + KEY `ville_id` (`ville_id`), + KEY `id_secteur` (`secteur_id`), + CONSTRAINT `relais_ibfk_2` FOREIGN KEY (`secteur_id`) REFERENCES `secteurs` (`id`) ON UPDATE CASCADE, + CONSTRAINT `relais_ibfk_3` FOREIGN KEY (`ville_id`) REFERENCES `villes` (`id`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `secteurs` +-- + +DROP TABLE IF EXISTS `secteurs`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `secteurs` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `title` (`title`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Différents secteurs de déploiement'; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `service_statuts` +-- + +DROP TABLE IF EXISTS `service_statuts`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `service_statuts` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `service_types` +-- + +DROP TABLE IF EXISTS `service_types`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `service_types` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL, + `prix_base_ht` decimal(7,2) NOT NULL, + `periodicite_id` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `periodicite_id` (`periodicite_id`), + CONSTRAINT `service_types_ibfk_1` FOREIGN KEY (`periodicite_id`) REFERENCES `periodicites` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Services disponibles'; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `services` +-- + +DROP TABLE IF EXISTS `services`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `services` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `adherent_id` int(11) NOT NULL, + `service_type_id` int(11) NOT NULL, + `service_statut_id` int(11) NOT NULL, + `ippublique_id` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL, + `prix_ht` decimal(7,2) DEFAULT NULL, + `date_debut` datetime DEFAULT NULL, + `date_fin` datetime DEFAULT NULL, + `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, + `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + `lat` float(10,7) DEFAULT NULL, + `lng` float(10,7) DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `id_adherent` (`adherent_id`), + KEY `id_type` (`service_type_id`), + KEY `service_statut_id` (`service_statut_id`), + KEY `ip4publique_id` (`ippublique_id`), + CONSTRAINT `services_ibfk_1` FOREIGN KEY (`adherent_id`) REFERENCES `adherents` (`id`), + CONSTRAINT `services_ibfk_2` FOREIGN KEY (`service_type_id`) REFERENCES `service_types` (`id`) ON UPDATE CASCADE, + CONSTRAINT `services_ibfk_4` FOREIGN KEY (`service_statut_id`) REFERENCES `service_statuts` (`id`) ON UPDATE CASCADE, + CONSTRAINT `services_ibfk_5` FOREIGN KEY (`ippublique_id`) REFERENCES `ippubliques` (`ip4`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Services souscrits par les adhérents'; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Temporary table structure for view `v_adt_sms` +-- + +DROP TABLE IF EXISTS `v_adt_sms`; +/*!50001 DROP VIEW IF EXISTS `v_adt_sms`*/; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v_adt_sms` ( + `id` tinyint NOT NULL, + `tel_mobile1` tinyint NOT NULL, + `tel_mobile2` tinyint NOT NULL, + `tel_contact` tinyint NOT NULL, + `mail_contact` tinyint NOT NULL, + `relais` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; + +-- +-- Temporary table structure for view `v_api_gen_conf` +-- + +DROP TABLE IF EXISTS `v_api_gen_conf`; +/*!50001 DROP VIEW 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 +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; + +-- +-- Temporary table structure for view `v_api_gen_firm` +-- + +DROP TABLE IF EXISTS `v_api_gen_firm`; +/*!50001 DROP VIEW IF EXISTS `v_api_gen_firm`*/; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v_api_gen_firm` ( + `ippublique_id` tinyint NOT NULL, + `nom` tinyint NOT NULL, + `prenom` tinyint NOT NULL, + `raison` tinyint NOT NULL, + `constructeur` tinyint NOT NULL, + `modele` tinyint NOT NULL, + `profile` tinyint NOT NULL, + `revision` tinyint NOT NULL, + `ip6prefix` tinyint NOT NULL, + `ip4wan` tinyint NOT NULL, + `ip6wanll` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; + +-- +-- Temporary table structure for view `v_relais` +-- + +DROP TABLE IF EXISTS `v_relais`; +/*!50001 DROP VIEW IF EXISTS `v_relais`*/; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v_relais` ( + `relais_id` tinyint NOT NULL, + `relais` tinyint NOT NULL, + `secteur` tinyint NOT NULL, + `nb_connectes` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; + +-- +-- Temporary table structure for view `v_relais_detail` +-- + +DROP TABLE IF EXISTS `v_relais_detail`; +/*!50001 DROP VIEW IF EXISTS `v_relais_detail`*/; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v_relais_detail` ( + `relais` tinyint NOT NULL, + `adt` tinyint NOT NULL, + `adt_title` tinyint NOT NULL, + `adherent_id` tinyint NOT NULL, + `service_id` 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, + `secteur_id` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; + +-- +-- Temporary table structure for view `v_secteurs` +-- + +DROP TABLE IF EXISTS `v_secteurs`; +/*!50001 DROP VIEW IF EXISTS `v_secteurs`*/; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v_secteurs` ( + `id` tinyint NOT NULL, + `secteur` tinyint NOT NULL, + `nb_connectes` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; + +-- +-- Temporary table structure for view `v_sympa_adherents` +-- + +DROP TABLE IF EXISTS `v_sympa_adherents`; +/*!50001 DROP VIEW IF EXISTS `v_sympa_adherents`*/; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v_sympa_adherents` ( + `mail` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; + +-- +-- Temporary table structure for view `v_sympa_referents` +-- + +DROP TABLE IF EXISTS `v_sympa_referents`; +/*!50001 DROP VIEW IF EXISTS `v_sympa_referents`*/; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v_sympa_referents` ( + `mail` tinyint NOT NULL, + `village_list_name` tinyint NOT NULL, + `sms_list_name` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; + +-- +-- Temporary table structure for view `v_sympa_roles` +-- + +DROP TABLE IF EXISTS `v_sympa_roles`; +/*!50001 DROP VIEW IF EXISTS `v_sympa_roles`*/; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v_sympa_roles` ( + `mail` tinyint NOT NULL, + `list_name` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; + +-- +-- Table structure for table `villes` +-- + +DROP TABLE IF EXISTS `villes`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `villes` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL, + `cp` char(5) COLLATE utf8_unicode_ci NOT NULL, + `tel_contact` varchar(10) COLLATE utf8_unicode_ci NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `title` (`title`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Final view structure for view `v_adt_sms` +-- + +/*!50001 DROP TABLE IF EXISTS `v_adt_sms`*/; +/*!50001 DROP VIEW IF EXISTS `v_adt_sms`*/; +/*!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_adt_sms` AS select concat('CHD-',`a`.`id`) AS `id`,`a`.`tel_mobile1` AS `tel_mobile1`,`a`.`tel_mobile2` AS `tel_mobile2`,`v`.`tel_contact` AS `tel_contact`,concat(lcase(replace(`v`.`title`,' ','-')),'@chd.sx') AS `mail_contact`,`re`.`title` AS `relais` from (((((((`adherents` `a` left join `villes` `v` on((`v`.`id` = `a`.`ville_id`))) 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` = 4)) */; +/*!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_api_gen_conf` +-- + +/*!50001 DROP TABLE IF EXISTS `v_api_gen_conf`*/; +/*!50001 DROP VIEW IF EXISTS `v_api_gen_conf`*/; +/*!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_api_gen_conf` AS select 'do_conf' AS `do_conf`,'actif' AS `etat`,concat_ws('-','SER',`s`.`service_type_id`,`a`.`id`,`s`.`id`) AS `svc_id`,`ipr`.`ip4` AS `ip4_nexthop`,`ipp`.`ip4` AS `ip4_public`,coalesce(`ipr`.`ip6`,`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'))))) left join `ipmgmt` `ipr` on((`ipr`.`id` = `routeur`.`ipmgmt_id`))) where ((`s`.`service_type_id` in (2,3,4)) and (`s`.`service_statut_id` in (2,4)) and (`a`.`adherent_statut_id` <> 1)) */; +/*!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_api_gen_firm` +-- + +/*!50001 DROP TABLE IF EXISTS `v_api_gen_firm`*/; +/*!50001 DROP VIEW IF EXISTS `v_api_gen_firm`*/; +/*!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_api_gen_firm` AS select `s`.`ippublique_id` AS `ippublique_id`,`a`.`nom` AS `nom`,`a`.`prenom` AS `prenom`,`a`.`raison` AS `raison`,`em`.`constructeur` AS `constructeur`,`em`.`modele` AS `modele`,`em`.`profile` AS `profile`,`em`.`revision` AS `revision`,`ip`.`ip6` AS `ip6prefix`,`ip2`.`ip4` AS `ip4wan`,`ip2`.`ip6` AS `ip6wanll` from (((((`services` `s` left join `adherents` `a` on((`a`.`id` = `s`.`adherent_id`))) left join `ippubliques` `ip` on((`ip`.`ip4` = `s`.`ippublique_id`))) left join `equipements` `e` on((`e`.`service_id` = `s`.`id`))) left join `equipement_modeles` `em` on((`em`.`id` = `e`.`equipement_modele_id`))) left join `ipmgmt` `ip2` on((`ip2`.`id` = `e`.`ipmgmt_id`))) where ((`s`.`service_statut_id` = 4) and (`s`.`ippublique_id` is not null)) */; +/*!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` +-- + +/*!50001 DROP TABLE IF EXISTS `v_relais`*/; +/*!50001 DROP VIEW IF EXISTS `v_relais`*/; +/*!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_relais` AS select `v`.`relais_id` AS `relais_id`,`v`.`relais` AS `relais`,`s`.`title` AS `secteur`,count(0) AS `nb_connectes` from (`v_relais_detail` `v` left join `secteurs` `s` on((`v`.`secteur_id` = `s`.`id`))) group by `v`.`relais_id`,`v`.`relais`,`s`.`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_relais_detail` +-- + +/*!50001 DROP TABLE IF EXISTS `v_relais_detail`*/; +/*!50001 DROP VIEW IF EXISTS `v_relais_detail`*/; +/*!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_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 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_secteurs` +-- + +/*!50001 DROP TABLE IF EXISTS `v_secteurs`*/; +/*!50001 DROP VIEW IF EXISTS `v_secteurs`*/; +/*!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_secteurs` AS select `s`.`id` AS `id`,`s`.`title` AS `secteur`,count(0) AS `nb_connectes` from (`v_relais_detail` `v` left join `secteurs` `s` on((`v`.`secteur_id` = `s`.`id`))) group by `s`.`id`,`s`.`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_sympa_adherents` +-- + +/*!50001 DROP TABLE IF EXISTS `v_sympa_adherents`*/; +/*!50001 DROP VIEW IF EXISTS `v_sympa_adherents`*/; +/*!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_sympa_adherents` AS select distinct `a`.`mail1` AS `mail` from `adherents` `a` where ((length(`a`.`mail1`) > 5) and (`a`.`adherent_statut_id` <> 1)) */; +/*!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_sympa_referents` +-- + +/*!50001 DROP TABLE IF EXISTS `v_sympa_referents`*/; +/*!50001 DROP VIEW IF EXISTS `v_sympa_referents`*/; +/*!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_sympa_referents` AS select `a`.`mail1` AS `mail`,lcase(replace(`v`.`title`,' ','-')) AS `village_list_name`,`v`.`tel_contact` AS `sms_list_name` from ((`adherent_roles` `ar` left join `adherents` `a` on((`ar`.`adherent_id` = `a`.`id`))) left join `villes` `v` on((`ar`.`ville_id` = `v`.`id`))) where ((`ar`.`adherent_role_type_id` = 7) and (length(`a`.`mail1`) > 5)) */; +/*!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_sympa_roles` +-- + +/*!50001 DROP TABLE IF EXISTS `v_sympa_roles`*/; +/*!50001 DROP VIEW IF EXISTS `v_sympa_roles`*/; +/*!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_sympa_roles` AS select distinct `a`.`mail1` AS `mail`,`art`.`list_name` AS `list_name` from ((`adherent_roles` `ar` left join `adherents` `a` on((`ar`.`adherent_id` = `a`.`id`))) left join `adherent_role_types` `art` on((`ar`.`adherent_role_type_id` = `art`.`id`))) where (length(`a`.`mail1`) > 5) */; +/*!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 */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2016-03-12 0:30:48 -- cgit v1.1