· 8 years ago · Mar 28, 2018, 05:10 PM
1-- phpMyAdmin SQL Dump
2-- version 4.7.7
3-- https://www.phpmyadmin.net/
4--
5-- Hôte : db
6-- Généré le : mer. 28 mars 2018 à 16:21
7-- Version du serveur : 10.2.12-MariaDB-10.2.12+maria~jessie
8-- Version de PHP : 7.1.9
9
10SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
11SET AUTOCOMMIT = 0;
12START TRANSACTION;
13SET time_zone = "+00:00";
14
15--
16-- Base de données : `parents`
17--
18CREATE DATABASE IF NOT EXISTS `parents` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
19USE `parents`;
20
21-- --------------------------------------------------------
22
23--
24-- Structure de la table `relation`
25--
26
27DROP TABLE IF EXISTS `relation`;
28CREATE TABLE `relation` (
29 `external_user_metadata_id` int(11) NOT NULL,
30 `parent_id` int(11) NOT NULL
31) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
32
33--
34-- Déchargement des données de la table `relation`
35--
36
37INSERT INTO `relation` (`external_user_metadata_id`, `parent_id`) VALUES
38(2, 4),
39(3, 4),
40(4, 5),
41(5, 6),
42(6, 5),
43(7, 6),
44(8, 7),
45(9, 8),
46(10, 8),
47(11, 11),
48(12, 11),
49(13, 6),
50(13, 9),
51(14, 10),
52(15, 10),
53(16, 13),
54(17, 11),
55(18, 12);
56
57-- --------------------------------------------------------
58
59--
60-- Structure de la table `user`
61--
62
63DROP TABLE IF EXISTS `user`;
64CREATE TABLE `user` (
65 `id` int(11) NOT NULL,
66 `uuid` char(36) COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:guid)',
67 `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
68 `roles` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:json)'
69) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
70
71--
72-- Déchargement des données de la table `user`
73--
74
75INSERT INTO `user` (`id`, `uuid`, `email`, `roles`) VALUES
76(4, '47fd52ab-def7-7542-198d-aed8742cd96f', 'dev@acme.fr', '["ROLE_HR_PLUS"]'),
77(5, 'eb21b1f9-feef-350a-9e67-64b2fd822588', '83e84d5c6467-fpeltier@olivier.fr', '["ROLE_HR_PLUS"]'),
78(6, '673127ae-fe0c-3a19-8c2b-b0cb4988f15f', '833f0aef4f7d-jalexandre@live.com', '["ROLE_HR_PLUS"]'),
79(7, 'f4ceeda1-a2b8-3776-9d26-729b406146af', '0f4005c8fd08-kallard@vallee.org', '["ROLE_HR_PLUS"]'),
80(8, '1dbaf1fc-9e72-3734-966f-b7249293d6d9', '7b9c36e35399-perrin.daniel@noos.fr', '["ROLE_HR_PLUS"]'),
81(9, '36fa31bb-51da-3217-a437-58cd576e70c1', 'e9fa5fa7892c-carpentier.lucy@blin.fr', '["ROLE_HR"]'),
82(10,'6cc2835e-1858-3c93-9e9a-d13a68bcff4e', 'f3147799c3a6-pguibert@marchal.fr', '["ROLE_HR"]'),
83(11, '998597fd-6051-34c4-ac91-a31281a75899', '2d6516b63154-isabelle.merle@nguyen.com', '["ROLE_HR"]'),
84(12, '5eafa5ac-4c51-3f00-b1a7-0ada4fde1df1', '3cea9b55ae8b-flebrun@rey.fr', '["ROLE_HR"]'),
85(13, 'df08fc36-3133-3358-99b0-65444dd7a90f', '8b73237c6f68-honore21@valette.net', '["ROLE_HR"]'),
86(14, 'e39ddc13-a044-332a-a043-05348c1b3018', 'fd34e3f17437-maurice.bernard@wanadoo.fr', '["ROLE_COLLABORATOR"]'),
87(15, '8253fa83-9403-3964-b945-6feaa98f8282', '6988e45ed12f-antoine.joly@live.com', '["ROLE_COLLABORATOR"]'),
88(16, '6158a00a-6144-3399-b77c-35f71bc24d6f', 'a4e4e748af71-edith.bernier@monnier.fr', '["ROLE_COLLABORATOR"]'),
89(17, '4b062152-5b12-34dd-9cc4-da3594bbcebf', '184d9bddbd52-thierry.millet@marion.fr', '["ROLE_COLLABORATOR"]'),
90(18, '0befce6b-b3f1-3694-8ca0-57bd275d0a03', 'e1197f785e55-gmaillard@renault.com', '["ROLE_COLLABORATOR"]'),
91(19, 'e2117df5-eccb-3663-a8e4-a7cb62aa2b15', '87dae8f77f08-leclerc.sebastien@bouygtel.fr', '["ROLE_COLLABORATOR"]'),
92(20, 'bdd6804d-1677-34dd-89ad-3b847c92f938', '91da73408de9-jguillou@wanadoo.fr', '["ROLE_COLLABORATOR"]'),
93(21, 'bab48fa8-3db8-34e2-98f4-e6dfcf9eb72e', '3105a4e0c35f-opoirier@ifrance.com', '["ROLE_COLLABORATOR"]');
94
95-- --------------------------------------------------------
96
97--
98-- Structure de la table `user_metadata_external`
99--
100
101DROP TABLE IF EXISTS `user_metadata_external`;
102CREATE TABLE `user_metadata_external` (
103 `id` int(11) NOT NULL,
104 `user` int(11) DEFAULT NULL
105) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
106
107--
108-- Déchargement des données de la table `user_metadata_external`
109--
110
111INSERT INTO `user_metadata_external` (`id`, `user`) VALUES
112(1, 4),
113(2, 5),
114(3, 6),
115(4, 7),
116(5, 8),
117(6, 9),
118(7, 10),
119(8, 11),
120(9, 12),
121(10, 13),
122(11, 14),
123(12, 15),
124(13, 16),
125(14, 17),
126(15, 18),
127(16, 19),
128(17, 20),
129(18, 21);
130
131--
132-- Index pour les tables déchargées
133--
134
135--
136-- Index pour la table `relation`
137--
138ALTER TABLE `relation`
139 ADD PRIMARY KEY (`external_user_metadata_id`,`parent_id`),
140 ADD KEY `IDX_628947496ECDFD99` (`external_user_metadata_id`),
141 ADD KEY `IDX_62894749727ACA70` (`parent_id`);
142
143--
144-- Index pour la table `user`
145--
146ALTER TABLE `user`
147 ADD PRIMARY KEY (`id`),
148 ADD UNIQUE KEY `uuid_idx` (`uuid`),
149 ADD UNIQUE KEY `email_idx` (`email`);
150
151--
152-- Index pour la table `user_metadata_external`
153--
154ALTER TABLE `user_metadata_external`
155 ADD PRIMARY KEY (`id`),
156 ADD UNIQUE KEY `uuid_idx` (`user`);
157
158--
159-- Contraintes pour les tables déchargées
160--
161
162--
163-- Contraintes pour la table `relation`
164--
165ALTER TABLE `relation`
166 ADD CONSTRAINT `FK_628947496ECDFD99` FOREIGN KEY (`external_user_metadata_id`) REFERENCES `user_metadata_external` (`id`),
167 ADD CONSTRAINT `FK_62894749727ACA70` FOREIGN KEY (`parent_id`) REFERENCES `user` (`id`);
168
169COMMIT;
170
171+----------------------------------------------+-----------+
172| id | email | parents |
173+----+-----------------------------------------+-----------+
174| 14 | fd34e3f17437-maurice.bernard@wanadoo.fr | 4 |
175+----------------------------------------------+-----------+
176
177SELECT u0.id, u0.email, @parents := IF(r0.parent_id IS NOT NULL, @parents, @parents + 1) AS parents
178FROM user u0
179LEFT JOIN user_metadata_external m0 ON m0.user = u0.id
180LEFT JOIN relation r0 ON r0.external_user_metadata_id = m0.id
181CROSS JOIN (SELECT @parents := 0) var
182WHERE u0.id = 14