Nombre d'entrées du 15 au 15 du mois.

Version imprimable

Voir 40 message(s) de cette discussion en une page

:ptdr:

Bon, bon, bon... J'en suis là...
J'ai renommé ma table horaire (Daphné ne m'en voudra pas, j'imagine)... ;)
Quand ça tournera pour une personne, j'ajouterai un champ login et je réadapterai tout pour que ça tourne pour tout le monde qui voudra saisir ses horaires...
J'ai renommé le champ 'date' 'jour' parce que j'ai souvent la flemme d'échapper les mots réservés SQL.

Et j'ai posé des indexes de ouf... Je vous montre tout ça.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
 
 
--
-- Base de données :  `entrepot`
--
 
-- --------------------------------------------------------
 
--
-- Structure de la table `horaire`
--
 
DROP TABLE IF EXISTS `horaire`;
CREATE TABLE IF NOT EXISTS `horaire` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `jour` date NOT NULL,
  `debut` time NOT NULL,
  `fin` time NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jour_index` (`jour`),
  KEY `debut_index` (`debut`),
  KEY `fin_index` (`fin`),
  KEY `complet_index` (`jour`,`debut`,`fin`),
  KEY `jour_debut_index` (`jour`,`debut`),
  KEY `jour_fin_index` (`jour`,`fin`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;
 
--
-- Déchargement des données de la table `horaire`
--
 
INSERT INTO `horaire` (`id`, `jour`, `debut`, `fin`) VALUES
(35, '2018-01-01', '07:00:00', '11:30:00'),
(36, '2018-01-15', '07:00:00', '11:00:00'),
(37, '2018-03-15', '18:00:00', '24:00:00'),
(38, '2018-03-16', '00:00:00', '04:00:00'),
(39, '2018-05-05', '14:00:00', '16:00:00'),
(1, '2018-05-14', '09:00:00', '21:00:00'),
(2, '2018-05-15', '08:00:00', '10:00:00'),
(3, '2018-05-16', '09:00:00', '18:00:00'),
(4, '2018-05-17', '09:00:00', '18:00:00'),
(5, '2018-05-18', '09:00:00', '18:00:00'),
(6, '2018-05-19', '09:00:00', '18:00:00'),
(7, '2018-05-20', '09:00:00', '18:00:00'),
(8, '2018-05-21', '09:00:00', '18:00:00'),
(9, '2018-05-22', '09:00:00', '18:00:00'),
(10, '2018-05-23', '09:00:00', '18:00:00'),
(11, '2018-05-24', '09:00:00', '18:00:00'),
(12, '2018-05-25', '09:00:00', '18:00:00'),
(13, '2018-05-26', '09:00:00', '18:00:00'),
(14, '2018-05-27', '09:00:00', '18:00:00'),
(15, '2018-05-28', '09:00:00', '18:00:00'),
(16, '2018-05-29', '09:00:00', '18:00:00'),
(17, '2018-05-30', '09:00:00', '18:00:00'),
(18, '2018-05-31', '09:00:00', '18:00:00'),
(19, '2018-06-01', '09:00:00', '18:00:00'),
(20, '2018-06-02', '09:00:00', '18:00:00'),
(21, '2018-06-03', '09:00:00', '18:00:00'),
(22, '2018-06-04', '09:00:00', '18:00:00'),
(23, '2018-06-05', '09:00:00', '18:00:00'),
(24, '2018-06-06', '09:00:00', '18:00:00'),
(25, '2018-06-07', '09:00:00', '18:00:00'),
(26, '2018-06-08', '09:00:00', '18:00:00'),
(27, '2018-06-09', '09:00:00', '18:00:00'),
(28, '2018-06-10', '09:00:00', '18:00:00'),
(29, '2018-06-11', '09:00:00', '18:00:00'),
(30, '2018-06-12', '09:00:00', '18:00:00'),
(31, '2018-06-13', '09:00:00', '18:00:00'),
(32, '2018-06-14', '09:00:00', '18:00:00'),
(33, '2018-06-15', '09:00:00', '18:00:00'),
(34, '2018-06-16', '09:00:00', '18:00:00');
COMMIT;

Ensuite je bricole ces triggers de non chevauchement. (non, non, Jreaux, je ne fouette pas et je ne chevauche pas davantage, LOL)...

Ca bloque dès le début... if exists coince...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
CREATE TRIGGER no_overlapping_insert
BEFORE INSERT ON horaire 
IF EXISTS
(
	SELECT *
	FROM horaire
	WHERE STR_TO_DATE(CONCAT(jour, ' ', debut), '%Y-%m-%d %H:%i:%s') <= NEW.STR_TO_DATE(CONCAT(jour, ' ', fin), '%Y-%m-%d %H:%i:%s')
	AND STR_TO_DATE(CONCAT(jour, ' ', fin), '%Y-%m-%d %H:%i:%s') >= NEW.STR_TO_DATE(CONCAT(jour, ' ', debut), '%Y-%m-%d %H:%i:%s')
)
THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'Insertion impossible car chevauchement de date !';
END IF;
 
CREATE TRIGGER no_overlapping_update
BEFORE UPDATE ON horaire 
IF EXISTS
(
	SELECT *
	FROM horaire
	WHERE STR_TO_DATE(CONCAT(jour, ' ', debut), '%Y-%m-%d %H:%i:%s') <= NEW.STR_TO_DATE(CONCAT(jour, ' ', fin), '%Y-%m-%d %H:%i:%s')
	AND STR_TO_DATE(CONCAT(jour, ' ', fin), '%Y-%m-%d %H:%i:%s') >= NEW.STR_TO_DATE(CONCAT(jour, ' ', debut), '%Y-%m-%d %H:%i:%s')
)
THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'Modification impossible car chevauchement de date !';
END IF;

Sur le fond, la requête de chevauchement fonctionne :
Si je recherche le 1er janvier de 08 à 12H, cela me retourne bien qu'en base, j'ai déjà le 1er janvier de 07H à 11H30...

Code:

1
2
3
4
SELECT *
FROM horaire
WHERE STR_TO_DATE(CONCAT(jour, ' ', debut), '%Y-%m-%d %H:%i:%s') <= STR_TO_DATE('2018-01-01 12:00:00', '%Y-%m-%d %H:%i:%s')
AND STR_TO_DATE(CONCAT(jour, ' ', fin), '%Y-%m-%d %H:%i:%s') >= STR_TO_DATE('2018-01-01 08:00:00', '%Y-%m-%d %H:%i:%s')

edit encore !!!
La requête précédente ne permettait pas les saisies suivantes pour le même jour :
08H=>12H30
12H30=>18H

Ce qui était un peu sévère, si une personne croit qu'elle va prendre son après midi, puis change d'avis et fait une journée continue de 10H...
Donc j'ai conscience que ça comptera 2 fois la minute de 12H30 dans la vue finale et que ça faussera légèrement le calcul, mais je préfère ça... Marge d'erreur d'une minute, c'est pas la mort, plutôt que de bloquer totalement une saisie qui est légitime.
Alors bref, je veux une date strictement inférieure ou supérieure pour considérer qu'il y a vraiment chevauchement...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER no_insert_overlapping
BEFORE INSERT ON horaire 
IF EXISTS
(
	SELECT *
	FROM horaire
	WHERE STR_TO_DATE(CONCAT(jour, ' ', debut), '%Y-%m-%d %H:%i:%s') < NEW.STR_TO_DATE(CONCAT(jour, ' ', fin), '%Y-%m-%d %H:%i:%s')
	AND STR_TO_DATE(CONCAT(jour, ' ', fin), '%Y-%m-%d %H:%i:%s') > NEW.STR_TO_DATE(CONCAT(jour, ' ', debut), '%Y-%m-%d %H:%i:%s')
)
THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'Insertion impossible car chevauchement de date !';
END IF;

Salut à tous.

Citation:

Envoyé par jreaux62

On doit bien créer 2 lignes dans la bdd, pour le même jour, non ?

Je me suis mal exprimé, et vous avez raison !
Entre l'heure de début et l'heure de fin, il ne doit pas y avoir de trou.

En fait, je voulais dire de ne pas mettre dans une ligne une date de début et une date de fin à cheval sur deux jours.

Citation:

Envoyé par jreaux62

On doit aussi créer 2 lignes : 20h-24h (jour1) et 0h-4h (jour2)

Oui, vous avez encore raison. C'est ce que j'ai fait dans mon exemple (le dernier).

Citation:

Envoyé par jreaux62

3- D'autre part la période fevrier-mars aura + ou - 1 jour (année bissextile ou non)

Cela ne pose aucun problème car dans ce cas là, le mois de février aura 29 jours.

Citation:

Envoyé par jreaux62

Me trompe-je ?

Vos remarques sont pertinentes et cela mérite un +1.

Citation:

Envoyé par Dendrite

"Vous devez créer AU MOINS une ligne par jour différent."

Je dirais plutôt une ligne par intervalle de temps, ce qui donne :
Code:

1 2 3 4 5 6 7 8 +--------------+--------------+--------------+ | date du jour | heure début | heure fin | +--------------+--------------+--------------+ | 2018-05-31 | 09:00:00 | 12:00:00 | | 2018-05-31 | 13:30:00 | 17:00:00 | | 2018-05-31 | 22:30:00 | 24:00:00 | | 2018-06-01 | 00:00:00 | 04:00:00 | +--------------+--------------+--------------+
Citation:

Envoyé par Dendrite

Serait-il possible de poser une clé d'unicité pour que les périodes ne puissent pas se recouper ? J'imagine que non ?

Je suppose que vous pensez à un index pour déterminer des périodes qui se chevauchent. A moins de me tromper, je pense que c'est non.

Par contre, vous pouvez faire une requête pour tester l'insertion. Par exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 -------------- insert into w_daphne (`date`,`debut`,`fin`) select t1.date, t1.debut, t1.fin from ( select '2018-03-15' as date, '19:00:00' as debut, '21:00:00' as fin from dual ) as t1 where not exists ( select 1 from w_daphne as t2 where t2.date = t1.date and t2.fin >= t1.debut and t2.debut <= t1.fin ) -------------- -------------- select * from `w_daphne` where `date` between '2018-03-01' and '2018-03-31' -------------- +----+------------+----------+----------+ | id | date | debut | fin | +----+------------+----------+----------+ | 37 | 2018-03-15 | 18:00:00 | 24:00:00 | | 38 | 2018-03-16 | 00:00:00 | 04:00:00 | +----+------------+----------+----------+ Appuyez sur une touche pour continuer...
Dans cet exemple, j'utilise la période "2018-03-15 19:00:00 21:00:00" recouvre une période déjà existante "2018-03-15 18:00:00 24:00:00"

Autre exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 -------------- insert into w_daphne (`date`,`debut`,`fin`) select t1.date, t1.debut, t1.fin from ( select '2018-03-12' as date, '19:00:00' as debut, '21:00:00' as fin from dual ) as t1 where not exists ( select 1 from w_daphne as t2 where t2.date = t1.date and t2.fin >= t1.debut and t2.debut <= t1.fin ) -------------- -------------- select * from `w_daphne` where `date` between '2018-03-01' and '2018-03-31' -------------- +----+------------+----------+----------+ | id | date | debut | fin | +----+------------+----------+----------+ | 37 | 2018-03-15 | 18:00:00 | 24:00:00 | | 38 | 2018-03-16 | 00:00:00 | 04:00:00 | | 39 | 2018-03-12 | 19:00:00 | 21:00:00 | +----+------------+----------+----------+ Appuyez sur une touche pour continuer...
Ici, on peut faire l'insertion car pas de résultat en retour de la requête.

@+

Salut à tous.

Ou bien par un déclencheur :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `horaire`
--------------
 
--------------
CREATE TABLE `horaire`
(  `id`     integer unsigned not null auto_increment primary key,
   `date`   date             not null,
   `debut`  time             not null,
   `fin`    time             not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TRIGGER IF EXISTS `recouvrement`
--------------
 
--------------
CREATE TRIGGER `recouvrement`
BEFORE INSERT ON `horaire`
FOR EACH ROW
BEGIN
  DECLARE _msg  varchar(255);
 
  if exists (select 1 from horaire where date = new.date and fin >= new.debut and debut <= new.fin) then
        set _msg = concat('Recouvrement des horaires ("', new.date, '","', new.debut, '","', new.fin, '"), Insertion interdite !');
     SIGNAL SQLSTATE VALUE '07777' SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 7777;
  end if;
END
--------------
 
--------------
INSERT INTO `horaire` (`date`,`debut`, `fin`) VALUES
('2018-05-14', '09:00:00', '21:00:00'),
('2018-05-15', '08:00:00', '10:00:00'),
('2018-05-16', '09:00:00', '18:00:00'),
('2018-05-17', '09:00:00', '18:00:00'),
('2018-05-18', '09:00:00', '18:00:00'),
('2018-05-19', '09:00:00', '18:00:00'),
('2018-05-20', '09:00:00', '18:00:00'),
('2018-05-21', '09:00:00', '18:00:00'),
('2018-05-22', '09:00:00', '18:00:00'),
('2018-05-23', '09:00:00', '18:00:00'),
('2018-05-24', '09:00:00', '18:00:00'),
('2018-05-25', '09:00:00', '18:00:00'),
('2018-05-26', '09:00:00', '18:00:00'),
('2018-05-27', '09:00:00', '18:00:00'),
('2018-05-28', '09:00:00', '18:00:00'),
('2018-05-29', '09:00:00', '18:00:00'),
('2018-05-30', '09:00:00', '18:00:00'),
('2018-05-31', '09:00:00', '18:00:00'),
('2018-06-01', '09:00:00', '18:00:00'),
('2018-06-02', '09:00:00', '18:00:00'),
('2018-06-03', '09:00:00', '18:00:00'),
('2018-06-04', '09:00:00', '18:00:00'),
('2018-06-05', '09:00:00', '18:00:00'),
('2018-06-06', '09:00:00', '18:00:00'),
('2018-06-07', '09:00:00', '18:00:00'),
('2018-06-08', '09:00:00', '18:00:00'),
('2018-06-09', '09:00:00', '18:00:00'),
('2018-06-10', '09:00:00', '18:00:00'),
('2018-06-11', '09:00:00', '18:00:00'),
('2018-06-12', '09:00:00', '18:00:00'),
('2018-06-13', '09:00:00', '18:00:00'),
('2018-06-14', '09:00:00', '18:00:00'),
('2018-06-15', '09:00:00', '18:00:00'),
('2018-06-16', '09:00:00', '18:00:00'),
('2018-01-01', '07:00:00', '11:30:00'),
('2018-01-15', '07:00:00', '11:00:00')
--------------
 
--------------
INSERT INTO `horaire` (`date`,`debut`, `fin`) VALUES
('2018-03-15', '18:00:00', '24:00:00')
--------------
 
--------------
INSERT INTO `horaire` (`date`,`debut`, `fin`) VALUES
('2018-03-16', '00:00:00', '04:00:00')
--------------
 
--------------
INSERT INTO `horaire` (`date`,`debut`, `fin`) VALUES
('2018-03-15', '19:00:00', '22:00:00')
--------------
 
ERROR 7777 (07777) at line 99: Recouvrement des horaires ("2018-03-15","19:00:00","22:00:00"), Insertion interdite !
--------------
select * from `horaire`
--------------
 
+----+------------+----------+----------+
| id | date       | debut    | fin      |
+----+------------+----------+----------+
|  1 | 2018-05-14 | 09:00:00 | 21:00:00 |
|  2 | 2018-05-15 | 08:00:00 | 10:00:00 |
|  3 | 2018-05-16 | 09:00:00 | 18:00:00 |
|  4 | 2018-05-17 | 09:00:00 | 18:00:00 |
|  5 | 2018-05-18 | 09:00:00 | 18:00:00 |
|  6 | 2018-05-19 | 09:00:00 | 18:00:00 |
|  7 | 2018-05-20 | 09:00:00 | 18:00:00 |
|  8 | 2018-05-21 | 09:00:00 | 18:00:00 |
|  9 | 2018-05-22 | 09:00:00 | 18:00:00 |
| 10 | 2018-05-23 | 09:00:00 | 18:00:00 |
| 11 | 2018-05-24 | 09:00:00 | 18:00:00 |
| 12 | 2018-05-25 | 09:00:00 | 18:00:00 |
| 13 | 2018-05-26 | 09:00:00 | 18:00:00 |
| 14 | 2018-05-27 | 09:00:00 | 18:00:00 |
| 15 | 2018-05-28 | 09:00:00 | 18:00:00 |
| 16 | 2018-05-29 | 09:00:00 | 18:00:00 |
| 17 | 2018-05-30 | 09:00:00 | 18:00:00 |
| 18 | 2018-05-31 | 09:00:00 | 18:00:00 |
| 19 | 2018-06-01 | 09:00:00 | 18:00:00 |
| 20 | 2018-06-02 | 09:00:00 | 18:00:00 |
| 21 | 2018-06-03 | 09:00:00 | 18:00:00 |
| 22 | 2018-06-04 | 09:00:00 | 18:00:00 |
| 23 | 2018-06-05 | 09:00:00 | 18:00:00 |
| 24 | 2018-06-06 | 09:00:00 | 18:00:00 |
| 25 | 2018-06-07 | 09:00:00 | 18:00:00 |
| 26 | 2018-06-08 | 09:00:00 | 18:00:00 |
| 27 | 2018-06-09 | 09:00:00 | 18:00:00 |
| 28 | 2018-06-10 | 09:00:00 | 18:00:00 |
| 29 | 2018-06-11 | 09:00:00 | 18:00:00 |
| 30 | 2018-06-12 | 09:00:00 | 18:00:00 |
| 31 | 2018-06-13 | 09:00:00 | 18:00:00 |
| 32 | 2018-06-14 | 09:00:00 | 18:00:00 |
| 33 | 2018-06-15 | 09:00:00 | 18:00:00 |
| 34 | 2018-06-16 | 09:00:00 | 18:00:00 |
| 35 | 2018-01-01 | 07:00:00 | 11:30:00 |
| 36 | 2018-01-15 | 07:00:00 | 11:00:00 |
| 37 | 2018-03-15 | 18:00:00 | 24:00:00 |
| 38 | 2018-03-16 | 00:00:00 | 04:00:00 |
+----+------------+----------+----------+
--------------
select    date_format(`date` - interval 15 day,'%Y-%m') as periode,
          cast(sum(timediff(`fin`,`debut`)) as time)   as total
    from  horaire
group by  periode
order by  periode desc
--------------
 
+---------+-----------+
| periode | total     |
+---------+-----------+
| 2018-06 | 09:00:00  |
| 2018-05 | 279:00:00 |
| 2018-04 | 14:00:00  |
| 2018-03 | 04:00:00  |
| 2018-02 | 06:00:00  |
| 2017-12 | 08:30:00  |
+---------+-----------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

J'ai inséré qu'une seule ligne en recouvrement.
A vous de me dire si cela vous convient ! ;)

@+

Je te lis bien sûr, Artemus...
Mais je souhaiterais d'abord me débloquer avec cette histoire de syntaxe de trigger qui coince... J'en suis là et j'applique à la lettre ce que j'ai trouvé sur la doc mysql. Et je pose ce code dans l'écran SQL de mon phpmyadmin. Et ça me jette sur "near END IF"... comprends pas...
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 delimiter | CREATE TRIGGER no_insert_overlapping BEFORE INSERT ON horaire FOR EACH ROW BEGIN IF EXISTS( SELECT * FROM horaire WHERE STR_TO_DATE(CONCAT(jour, ' ', debut), '%Y-%m-%d %H:%i:%s') < NEW.STR_TO_DATE(CONCAT(jour, ' ', fin), '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE(CONCAT(jour, ' ', fin), '%Y-%m-%d %H:%i:%s') > NEW.STR_TO_DATE(CONCAT(jour, ' ', debut), '%Y-%m-%d %H:%i:%s') ) END IF; END; | delimiter ;

02/06/2018, 13h45
Artemus24

Salut Dendrite.

Citation:

Envoyé par Dendrite

Je te lis bien sûr, Artemus...

Et bien non, car vous avez raté un de mes messages ou je vous donne le déclencheur qui teste les recouvrements.
Je viens de constater que le sujet a basculé dans le forum php alors que la question concernait MySql. :weird:

@+
02/06/2018, 13h46
Invité

Dendrite, tu devrais ouvrir une nouvelle discussion.
(avec un lien vers celle-ci)

Quelqu'un a des nouvelles de dllaurent ? :aie:
02/06/2018, 13h48
Artemus24

Mais non, son problème est résolu (je parle bien sûr du déclencheur).

Oui, oui, j'arrête bientôt Jreaux, mais je veux en finir avec le déclencheur.
Ca ne me paraît pas du tout hors sujet avec la problématique de départ de dllaurent.
Artemus, je me trompe ou bien c'est ceci and fin > new.debut and debut < new.fin tandis que tu avais mis autre chose and fin > new.fin and debut < new.debut...
Par ailleurs, je ne peux pas activer ce trigger en le mettant dans ma console SQL... et je ne comprends pas pourquoi... :pleure:
Code:

1 2 3 4 5 6 7 8 9 10 11 CREATE TRIGGER `recouvrement` BEFORE INSERT ON `horaire` FOR EACH ROW BEGIN DECLARE _msg varchar(255); if exists (select 1 from horaire where jour = new.jour and fin > new.debut and debut < new.fin) then set _msg = concat('Recouvrement des horaires ("', new.jour, '","', new.debut, '","', new.fin, '"), Insertion interdite !'); SIGNAL SQLSTATE VALUE '07777' SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 7777; end if; END

Salut Dendrite.

Citation:

Envoyé par Dendrite

Artemus, je me trompe ou bien c'est ceci (fin>debut et debut<fin)

Vous parlez des conditions strictes.

Cela depend de ce que vous faites aux bornes. Je suis partie de l'hypothèse que le chevauchement est autorisé sur la borne.
Autrement dit, on peut écrire :
Code:

1 2 3 4 5 6 +--------------+--------------+--------------+ | date du jour | heure début | heure fin | +--------------+--------------+--------------+ | 2018-05-31 | 09:00:00 | 12:00:00 | | 2018-05-31 | 12:00:00 | 17:00:00 | +--------------+--------------+--------------+
Par contre, si vous n'autorisez pas le chevauchement, voilà ce que vous devez écrire :
Code:

1 2 3 4 5 6 +--------------+--------------+--------------+ | date du jour | heure début | heure fin | +--------------+--------------+--------------+ | 2018-05-31 | 09:00:00 | 11:59:00 | | 2018-05-31 | 12:00:00 | 17:00:00 | +--------------+--------------+--------------+
Citation:

Envoyé par Dendrite

Par ailleurs, je ne peux pas activer ce trigger en le mettant dans ma console SQL... et je ne comprends pas pourquoi...

Il faut bien attribuer ce déclencheur à une base !
Vous avez oubliez d'utiliser le "use base".
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 -- ========================== -- Déclencheur dans `horaire` -- ========================== use `base`; DROP TRIGGER IF EXISTS `recouvrement`; DELIMITER $$ CREATE TRIGGER `recouvrement` BEFORE INSERT ON `horaire` FOR EACH ROW BEGIN DECLARE _msg varchar(255); if exists (select 1 from horaire where date = new.date and fin >= new.debut and debut <= new.fin) then set _msg = concat('Recouvrement des horaires ("', new.date, '","', new.debut, '","', new.fin, '"), Insertion interdite !'); SIGNAL SQLSTATE VALUE '07777' SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 7777; end if; END$$ DELIMITER ;
@+

Alleluia, merci encore Artemus !

Ceci dit, je maintiens 2 remarques par rapport à votre trigger :

Code:

and fin >= new.fin and debut <= new.debut

Qui chez moi devient

Code:

and fin > new.debut and debut < new.fin

1) fin >= new.fin ne signifie rien, c'est bien fin >= new.debut qui définit le chevauchement.
2) fin > new.debut est moins strict que fin >= new.debut puisqu'un déclencheur strict doit faire remonter plus de choses...

Je ne sais pas si je suis claire, mais je suis sûre de mon coup. Merci encore pour tout Artemus.

Voici donc le trigger qui fonctionne comme je veux :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 use `entrepot`; DROP TRIGGER IF EXISTS `recouvrement_insertion`; DELIMITER $$ CREATE TRIGGER `recouvrement_insertion` BEFORE INSERT ON `horaire` FOR EACH ROW BEGIN DECLARE _msg varchar(255); if exists (select 1 from horaire where jour = new.jour and fin > new.debut and debut < new.fin) then set _msg = concat('Recouvrement des horaires ("', new.jour, '","', new.debut, '","', new.fin, '"), Insertion interdite !'); SIGNAL SQLSTATE VALUE '07777' SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 7777; end if; END$$ DELIMITER ;

Salut Dendrite.

Oups ! :oops:
Cela m'arrive assez souvent de faire de bêtises. Désolé.
Je corrige !

Edit : c'est fait, j'ai corrigé mes deux messages. Désolé de cette erreur d'inattention. :oops:
Je pense que le sujet est maintenant résolu. :D

Citation:

Envoyé par Dendrite

faut-il poser des indexes sur les champs debut et fin sur lesquels on va opérer tous les calculs ? J'aurais tendance à répondre oui, moi...

OUI !

Pour la table horaire (index mis en rouge) :

Code:

1
2
3
4
5
6
7
8
9
10
11
DROP TABLE IF EXISTS `horaire`;

CREATE TABLE `horaire`
(  `id`     integer unsigned not null auto_increment primary key,
   `date`   date             not null,
   `debut`  time             not null,
   `fin`    time             not null,
   index `idx` (`date`,`debut`,`fin`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;

Et voici un explain sur l'insertion :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
--------------
explain
insert into `horaire` (`date`,`debut`,`fin`)
  select  t1.date,
          t1.debut,
          t1.fin
    from  (  select  '2018-03-15' as date,
                     '19:00:00'   as debut,
                     '21:00:00'   as fin
               from  dual
          ) as t1

where not exists (  select  1
                      from  `horaire` as t2
                     where  t2.date   = t1.date
                       and  t2.fin   >= t1.debut
                       and  t2.debut <= t1.fin
                 )
--------------

+----+--------------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type        | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                               |
+----+--------------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|  1 | INSERT             | NULL  | NULL       | NULL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE noticed after reading const tables |
|  3 | DEPENDENT SUBQUERY | t2    | NULL       | range | idx           | idx  | 6       | NULL |    1 |    33.33 | Using where; Using index                            |
|  2 | DERIVED            | NULL  | NULL       | NULL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used                                      |
+----+--------------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
--------------
select  *
  from  `horaire`
 where  `date` between '2018-03-01' and '2018-03-31'
--------------

+----+------------+----------+----------+
| id | date       | debut    | fin      |
+----+------------+----------+----------+
| 37 | 2018-03-15 | 18:00:00 | 24:00:00 |
| 38 | 2018-03-16 | 00:00:00 | 04:00:00 |
+----+------------+----------+----------+

Appuyez sur une touche pour continuer...

On voit bien dans l'explain que l'index 'idx' a été sélectionné pour la sous-requête t2.
Donc oui, l'index a bien été pris en compte.

@+

Merci.

Pour conclure, il faut 4 triggers.
Car il y a aussi l'incohérence qui consiste à mettre la fin avant le début sur un même tuple...
Avec ça, on est couverts côté base.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
DROP TRIGGER IF EXISTS `no_inconsistent_insert`;
DELIMITER $$
CREATE TRIGGER `no_inconsistent_insert` BEFORE INSERT ON `horaire` FOR EACH ROW BEGIN
	if (new.debut >= new.fin) 
	then SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insertion impossible car debut et fin non conformes !';
  end if;
END
$$
DELIMITER ;
DROP TRIGGER IF EXISTS `no_inconsistent_update`;
DELIMITER $$
CREATE TRIGGER `no_inconsistent_update` BEFORE UPDATE ON `horaire` FOR EACH ROW BEGIN
	if (new.debut >= new.fin) 
	then SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Modification impossible car debut et fin non conformes !';
  end if;
END
$$
DELIMITER ;
DROP TRIGGER IF EXISTS `no_overlapping_insert`;
DELIMITER $$
CREATE TRIGGER `no_overlapping_insert` BEFORE INSERT ON `horaire` FOR EACH ROW BEGIN
	if exists (select 1 from horaire where jour = new.jour and fin > new.debut and debut < new.fin) 
	then SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insertion impossible car chevauchement de date !';
  end if;
END
$$
DELIMITER ;
DROP TRIGGER IF EXISTS `no_overlapping_update`;
DELIMITER $$
CREATE TRIGGER `no_overlapping_update` BEFORE UPDATE ON `horaire` FOR EACH ROW BEGIN
	if exists (select 1 from horaire where jour = new.jour and fin > new.debut and debut < new.fin) 
	then SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Modification impossible car chevauchement de date !';
  end if;
END
$$
DELIMITER ;

Salut Dendrite.

Il n'est pas nécessaire de créer quatre déclencheurs, vous pouvez les combiner :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- ===========================
-- Déclencheurs dans `horaire`
-- ===========================
 
DELIMITER $$
 
DROP TRIGGER IF EXISTS `insert_erreur`$$
 
CREATE TRIGGER `insert_erreur`
BEFORE INSERT ON `horaire`
FOR EACH ROW
BEGIN
  DECLARE _msg  varchar(255);
 
  if (new.debut >= new.fin) then
        set _msg = concat('Horaire début ("', new.debut, '"), plus grand que horaire fin "(', new.fin, '"), Insertion interdite !');
     SIGNAL SQLSTATE VALUE '08888' SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 8888;
  end if;
 
  if exists (select 1 from horaire where date = new.date and fin >= new.debut and debut <= new.fin) then
        set _msg = concat('Recouvrement des horaires ("', new.date, '","', new.debut, '","', new.fin, '"), Insertion interdite !');
     SIGNAL SQLSTATE VALUE '07777' SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 7777;
  end if;
END$$
 
DROP TRIGGER IF EXISTS `update_erreur`$$
 
DELIMITER $$
CREATE TRIGGER `update_erreur`
BEFORE UPDATE ON `horaire`
FOR EACH ROW
BEGIN
  DECLARE _msg  varchar(255);
 
  if (new.debut >= new.fin) then
        set _msg = concat('Horaire début ("', new.debut, '"), plus grand que horaire fin "(', new.fin, '"), Insertion interdite !');
     SIGNAL SQLSTATE VALUE '08888' SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 8888;
  end if;
 
  if exists (select 1 from horaire where date = new.date and fin >= new.debut and debut <= new.fin) then
        set _msg = concat('Recouvrement des horaires ("', new.date, '","', new.debut, '","', new.fin, '"), Insertion interdite !');
     SIGNAL SQLSTATE VALUE '07777' SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 7777;
  end if;
 
END$$
 
DELIMITER ;

Attention à l'ordre de vos déclencheurs, car si vous mettez celle du recouvrement avant celle de Debut >= Fin, elle sera exécuté en premier !

@+

02/06/2018, 20h26
Dendrite

Merci Artemus, ça marche impeccable.

Dernier rectificatif sur le trigger d'update : Il faut ajouter ceci à la clause de non chevauchement, sinon, on ne peut pas éditer l'id courant en toute liberté... where id != new.id

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
DROP TRIGGER IF EXISTS `update_erreur`;
CREATE TRIGGER `update_erreur` BEFORE UPDATE ON `horaire` FOR EACH ROW BEGIN
  DECLARE _msg  varchar(255);
 
  if (new.debut >= new.fin) then
         set _msg = concat('Modification interdite ! Horaire debut plus grand que horaire fin !');
     SIGNAL SQLSTATE VALUE '08888' SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 8888;
  end if;
  if exists (select 1 from horaire where id != new.id and jour = new.jour and fin > new.debut and debut < new.fin) then
        set _msg = concat('Modification interdite ! Les horaires recoupent des horaires déjà saisis !');
     SIGNAL SQLSTATE VALUE '07777' SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 7777;
  end if;
END

04/06/2018, 12h48
Dendrite
Un dernier problème plus grave et qui revient droit au fond de ce post :
Code:

1 2 3 4 5 6 7 8 select distinct jour, cast(sum(timediff(fin,debut)) as time) as cumul, login from horaire where id in (3,4) group by jour, login order by jour desc, cumul desc, login
sum(timediff(fin,debut)) peut donner... "66500" oui, 06H et... 65 minutes...

dendy 2018-06-04 10:00:00 11:50:00
dendy 2018-06-04 13:00:00 18:15:00

Du coup bien sûr,
CAST( "66500") as time provoque null...

Une solution ? Je cherche de mon côté...

EDIT : j'ai trouvé :
Code:

1 2 3 4 5 6 7 8 select distinct jour, sec_to_time(sum(time_to_sec(timediff(fin,debut)))) as cumul, login from horaire where id in (3,4) group by jour, login order by jour desc, cumul desc, login
affiche bien 07h05
17/10/2018, 16h55
dllaurent

Bonjour Tout le monde,

Je suis désolé mais j’ai dû abandonner le projet pendant un certain temps. Je pense m’y remettre d’ici peu et si cela vous intéresse, je posterais la manière que j’ai utilisée.

Effectivement vos solutions ont porté ces fruits, vous m’avez beaucoup aidé et je suis donc arrivé à ce que je voulais.

Je souhaiterais tous vous remercier pour la participation et le temps accorder à ce forum et en particulier à mon problème.

Voir 40 message(s) de cette discussion en une page