Discussion :

[bronon]

Bonsoir à tous
Ce long week end est l'occasion pour ceux sont obligés de rester à Paris de régler les choses mises de côté.
Donc je cherche à faire une requête qui va vérifier que toutes les colonnes de la table ayant un comment "obligatoire" sont bien remplies !
Évidemment le nombre de col concernées n'est pas fixé (trop simple).
Col1 ==> obligatoire ==> à vérifier pas vide !
Col2 ==> pas obligatoire
Col3 ==> obligatoire ==> à vérifier pas vide !
Col4 ==> pas obligatoire
Et ainsi de suite

Je pense que je suis obligé de jongler avec information.table et mes tables ?

Si qqu'un peut m'indiquer une piste
d'avance merci

[MàJ]
Ce matin j'ai tenté un truc comme ceci (Email fait partie des rubriques obligatoires)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT xxx.* FROM (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS AS yyy WHERE TABLE_NAME = 'table1' AND COLUMN_COMMENT = 'obligatoire')) xxx  WHERE xxx.Email = 'nom@site.com'

Je ne suis pas convaincu d'être dans la bonne direction mais de toutes les manières je récupère dans la console SQL l'erreur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

#1248 - Every derived table must have its own alias

Que je ne sais interpréter !

[Artemus24]

Salut bronon.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
 
--------------
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 `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`     integer unsigned NOT NULL auto_increment primary key,
  `col1`   integer              NULL DEFAULT    0 COMMENT 'obligatoire',
  `col2`   varchar(255)         NULL DEFAULT NULL COMMENT 'facultatif',
  `col3`   varchar(255)         NULL DEFAULT NULL COMMENT 'obligatoire',
  `col4`   varchar(255)         NULL DEFAULT NULL COMMENT 'facultatif'
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`col1`,`col2`,`col3`,`col4`) values
  (222,     ' ',     '',      default),
  (0,       '',      default, 'oui'),
  (222,     default, 'oui',   ' '),
  (default, 'oui',   ' ',     '')
--------------
 
--------------
select * from test
--------------
 
+----+------+------+------+------+
| id | col1 | col2 | col3 | col4 |
+----+------+------+------+------+
|  1 |  222 |      |      | NULL |
|  2 |    0 |      | NULL | oui  |
|  3 |  222 | NULL | oui  |      |
|  4 |    0 | oui  |      |      |
+----+------+------+------+------+
--------------
DROP TABLE IF EXISTS `sample`
--------------
 
--------------
CREATE TABLE `sample`
( `clef`    integer unsigned NOT NULL,
  `num`     integer unsigned NOT NULL,
  `libelle` varchar(255)         NULL DEFAULT NULL COMMENT 'obligatoire',
  primary key (`clef`,`num`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `sample` (`clef`,`num`,`libelle`) values
  (1, 1, 'un'),(1, 2, 'one'),(2, 1, 'deux'),(2, 2, ''),(3, 1, 'trois'),(3, 2, default)
--------------
 
--------------
select * from sample
--------------
 
+------+-----+---------+
| clef | num | libelle |
+------+-----+---------+
|    1 |   1 | un      |
|    1 |   2 | one     |
|    2 |   1 | deux    |
|    2 |   2 |         |
|    3 |   1 | trois   |
|    3 |   2 | NULL    |
+------+-----+---------+
--------------
select column_name, data_type, column_comment
from information_schema.columns
where table_schema = 'base'
  and table_name   = 'test'
--------------
 
+-------------+-----------+----------------+
| column_name | data_type | column_comment |
+-------------+-----------+----------------+
| id          | int       |                |
| col1        | int       | obligatoire    |
| col2        | varchar   | facultatif     |
| col3        | varchar   | obligatoire    |
| col4        | varchar   | facultatif     |
+-------------+-----------+----------------+
--------------
select id, col1
from test
where (col1 is null or col1 = '')
  and exists (select 1 from information_schema.columns where table_schema = 'base' and table_name = 'test' and column_comment like 'obl%')
--------------
 
+----+------+
| id | col1 |
+----+------+
|  2 |    0 |
|  4 |    0 |
+----+------+
--------------
DROP PROCEDURE IF EXISTS `verify`
--------------
 
--------------
CREATE PROCEDURE `verify`
(  in_database varchar(255),
   in_table    varchar(255)
)
DETERMINISTIC
NO SQL
BEGIN
  DECLARE _keys  VARCHAR(255);
  DECLARE _line  VARCHAR(255);
  DECLARE _type  VARCHAR(255);
  DECLARE _col   VARCHAR(255);
  DECLARE _cond  VARCHAR(255);
 
  DECLARE _fin   INTEGER  DEFAULT 1;
 
  DECLARE _tab1  CURSOR FOR SELECT group_concat(column_name) from information_schema.columns
                 where table_schema = in_database and table_name = in_table and column_key = 'pri';
 
  DECLARE _tab2  CURSOR FOR SELECT data_type, column_name from information_schema.columns
                 where table_schema = in_database and table_name = in_table and column_comment like 'obl%';
 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET _fin = 0;
 
  OPEN  _tab1;
  FETCH _tab1 into _keys;
  CLOSE _tab1;
 
  SET _fin = 1;
 
  OPEN  _tab2;
  FETCH _tab2 INTO _type, _col;
 
  WHILE (_fin)
  DO
    CASE _type WHEN 'varchar' THEN SET _cond = " = ''";
                              ELSE SET _cond = " = 0";
    END CASE;
 
    SET _line = concat('select ', _keys, ',', _col, ' from ', in_table, ' where ', _col, ' is null or ', _col, _cond, ' order by ', _keys);
 
    SELECT  in_database, in_table, _line;
 
    SET @req = _line;
 
    PREPARE            stmt FROM @req;
    EXECUTE            stmt;
    DEALLOCATE PREPARE stmt;
 
  FETCH _tab2 INTO _type, _col;
  END WHILE;
 
  CLOSE _tab2;
END
--------------
 
--------------
call `verify` ('base', 'test')
--------------
 
+-------------+----------+---------------------------------------------------------------------+
| in_database | in_table | _line                                                               |
+-------------+----------+---------------------------------------------------------------------+
| base        | test     | select id,col1 from test where col1 is null or col1 = 0 order by id |
+-------------+----------+---------------------------------------------------------------------+
+----+------+
| id | col1 |
+----+------+
|  2 |    0 |
|  4 |    0 |
+----+------+
+-------------+----------+----------------------------------------------------------------------+
| in_database | in_table | _line                                                                |
+-------------+----------+----------------------------------------------------------------------+
| base        | test     | select id,col3 from test where col3 is null or col3 = '' order by id |
+-------------+----------+----------------------------------------------------------------------+
+----+------+
| id | col3 |
+----+------+
|  1 |      |
|  2 | NULL |
|  4 |      |
+----+------+
--------------
call `verify` ('base', 'sample')
--------------
 
+-------------+----------+---------------------------------------------------------------------------------------------+
| in_database | in_table | _line                                                                                       |
+-------------+----------+---------------------------------------------------------------------------------------------+
| base        | sample   | select clef,num,libelle from sample where libelle is null or libelle = '' order by clef,num |
+-------------+----------+---------------------------------------------------------------------------------------------+
+------+-----+---------+
| clef | num | libelle |
+------+-----+---------+
|    2 |   2 |         |
|    3 |   2 | NULL    |
+------+-----+---------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

@+

[escartefigue]

Bonsoir,

Pour ce genre de problème, il y a les moyens curatifs, requêtes, procédures, ou autre, l'exemple proposé par Artémus24 en fait partie, mais aussi et préférablement les moyens préventifs, qui évitent que des valeurs indésirables puissent être insérées dans les colonnes concernées.

Donc pour stopper l'hémorragie, analysez et corrigez l'existant, puis ajoutez des contraintes check dans vos tables (à ajouter dans le CREATE ou a faire par ALTER sur les tables concernées)

http://dev.mysql.com/doc/refman/5.7/...ate-table.html

[Artemus24]

Salut Escartefigue.

ajoutez des contraintes check dans vos tables

Les "check" ne fonctionne pas sous MySql, ce qui est bien dommage !
@+

[escartefigue]

Damn'it ! comme on dit dans les bd américaines et merci pour ce rappel

Exact et facheux, avec MySql il faut créer un Trigger en lieu et place de la contraint check, ca revient au même, c'est juste un peu plus lourd

Un jour peut être, MySql rattrapera ses trop nombreux retards sur les fonctions SQL aujoud'hui non supportées

[bronon]

Merci pour vos réponses,
Je dois admettre que votre manière de cripter me déconcerte un peu, je n'emploie jamais cette méthode.
Donc si j'ai bien compris mais rien n'est moins sûr, je devrais faire une subquery dans un premier select ?
Ma question est écrire en littéral cette requête ?
D'avance merci
Bon Week End

[Artemus24]

Salut Bronon.

Ce que je t'ai proposé en tant que procédure stockée, c'est juste un habillage de la requête qui va vérifier si la colonne "obligatoire", contient autre chose que NULL ou vide si de type chaîne de caractères ou zéro si de type numérique.

Donc si j'ai bien compris mais rien n'est moins sûr, je devrais faire une subquery dans un premier select ?

Non, pas du tout. Vous devez d'abord créer la procédure stockée. Si vous avez des problèmes de compréhension, je reste à votre disposition.
Ensuite, vous lancez ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

call `verify` ('base', 'test')

Cela permet de lancer la procédure stockéee et faire la vérification de la table "test" dans la base de données "base".
J'ai préféré faire une vérification par table et non par base de données, ce qui pourrait prendre beaucoup de temps.

Je rappelle que ce dont je vous ai communiqué n'est pas un script (un source), mais une exécution.
De ce fait, il y a des choses qui n'apparaissent pas à l'exécution, comme les points-virgules, le "use", le "delimiter" ...

Je vous conseille de commencer par comprendre ce que j'ai fait et de faire des tests de votre coté.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

#1248 - Every derived table must have its own alias

Cela signifie que vous avez oublié de déclarer l'alias de la table dérivée (en rouge) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT xxx.* FROM (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table1' AND COLUMN_COMMENT = 'obligatoire') as xxx WHERE xxx.Email = 'nom@site.com';

Je vous donne ci-après le script :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
SET AUTOCOMMIT = 0;
START TRANSACTION;
 
-- ======================
-- Base de Données `base`
-- ======================
 
DROP DATABASE IF EXISTS `base`;
 
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`;
 
use `base`;
 
-- ============
-- Table `test`
-- ============
 
DROP TABLE IF EXISTS `test`;
 
CREATE TABLE `test`
( `id`     integer unsigned NOT NULL auto_increment primary key,
  `col1`   integer              NULL DEFAULT    0 COMMENT 'obligatoire',
  `col2`   varchar(255)         NULL DEFAULT NULL COMMENT 'facultatif',
  `col3`   varchar(255)         NULL DEFAULT NULL COMMENT 'obligatoire',
  `col4`   varchar(255)         NULL DEFAULT NULL COMMENT 'facultatif'
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- =====================
-- Insertion dans `test`
-- =====================
 
insert into `test` (`col1`,`col2`,`col3`,`col4`) values
  (222,     ' ',     '',      default),
  (0,       '',      default, 'oui'),
  (222,     default, 'oui',   ' '),
  (default, 'oui',   ' ',     '');
 
-- ================
-- Vidage de `test`
-- ================
 
select * from test;
 
-- ==============
-- Table `sample`
-- ==============
 
DROP TABLE IF EXISTS `sample`;
 
CREATE TABLE `sample`
( `clef`    integer unsigned NOT NULL,
  `num`     integer unsigned NOT NULL,
  `libelle` varchar(255)         NULL DEFAULT NULL COMMENT 'obligatoire',
  primary key (`clef`,`num`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- =======================
-- Insertion dans `sample`
-- =======================
 
insert into `sample` (`clef`,`num`,`libelle`) values
  (1, 1, 'un'),(1, 2, 'one'),(2, 1, 'deux'),(2, 2, ''),(3, 1, 'trois'),(3, 2, default);
 
-- ==================
-- Vidage de `sample`
-- ==================
 
select * from sample;
 
-- ===========
-- Requête N°1
-- ===========
 
select column_name, data_type, column_comment
from information_schema.columns
where table_schema = 'base'
  and table_name   = 'test';
 
-- ===========
-- Requête N°2
-- ===========
 
select id, col1
from test
where (col1 is null or col1 = '')
  and exists (select 1 from information_schema.columns where table_schema = 'base' and table_name = 'test' and column_comment like 'obl%');
 
-- ==================
-- Procedure `verify`
-- ==================
 
DROP PROCEDURE IF EXISTS `verify`;
 
DELIMITER $$
CREATE PROCEDURE `verify`
(  in_database varchar(255),
   in_table    varchar(255)
)
DETERMINISTIC
NO SQL
BEGIN
  DECLARE _keys  VARCHAR(255);
  DECLARE _line  VARCHAR(255);
  DECLARE _type  VARCHAR(255);
  DECLARE _col   VARCHAR(255);
  DECLARE _cond  VARCHAR(255);
 
  DECLARE _fin   INTEGER  DEFAULT 1;
 
  DECLARE _tab1  CURSOR FOR SELECT group_concat(column_name) from information_schema.columns
                 where table_schema = in_database and table_name = in_table and column_key = 'pri';
 
  DECLARE _tab2  CURSOR FOR SELECT data_type, column_name from information_schema.columns
                 where table_schema = in_database and table_name = in_table and column_comment like 'obl%';
 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET _fin = 0;
 
  OPEN  _tab1;
  FETCH _tab1 into _keys;
  CLOSE _tab1;
 
  SET _fin = 1;
 
  OPEN  _tab2;
  FETCH _tab2 INTO _type, _col;
 
  WHILE (_fin)
  DO
    CASE _type WHEN 'varchar' THEN SET _cond = " = ''";
                              ELSE SET _cond = " = 0";
    END CASE;
 
    SET _line = concat('select ', _keys, ',', _col, ' from ', in_table, ' where ', _col, ' is null or ', _col, _cond, ' order by ', _keys);
 
    SELECT  in_database, in_table, _line;
 
    SET @req = _line;
 
    PREPARE            stmt FROM @req;
    EXECUTE            stmt;
    DEALLOCATE PREPARE stmt;
 
  FETCH _tab2 INTO _type, _col;
  END WHILE;
 
  CLOSE _tab2;
END$$
DELIMITER ;
 
-- =====================
-- Exécuter la procédure
-- =====================
 
call `verify` ('base', 'test');
call `verify` ('base', 'sample');
 
-- ===
-- Fin
-- ===
 
COMMIT;
SET AUTOCOMMIT = 1;
exit

@+

[bronon]

Bonjour à tous je reviens sur ce pb que je n'arrive pas à formaliser. Je pensais pouvoir faire l'impasse mais le satané pb me rattrape !
Donc, je sélection dans INFORMATION_SCHEMA.COLUMNS les colonne ayant un commentaire avec %obligat%. De ce résultat je dois vérifier que toutes les colonnes sélectionnées sont bien remplies pour le row xxx avec une valeur (donc non vide).

Comment faire en scriptant littéralement seule chose que je sais faire avec PHP/MySql.

Désolé d'être aussi lourd

[al1_24]

Il aurait été tellement plus simple lors de la conception de la base de poser une contrainte NOT NULL sur ces colonnes...

[Artemus24]

Salut Bronon.

Je t'ai donné la solution par l'intermédiaire d'une procédure stockée.
Qu'est-ce qui ne va pas dans ce script ?
As-tu un problème de compréhension ou un problème de faisabilité ?

Donne un peu plus d'explications sur ta problématique, avec un exemple ou je ne sais quoi d'autre.

@+

[bronon]

Merci pour ta réponse,
Mon pb est un vrai pb de compréhension soyons clairs !
Merci pour ta patience
Bonne journée