Demande d'aide sur une procédure de rechercher / remplacer

Version imprimable

Bonjour

La procédure ci-dessous a pour but de faire une recherche dans l'ensemble des bases, en omettant le préfixe, afin de modifier des valeurs spécifiques dans des tables. Cette procédure a été écrite par un de mes développeurs.

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
DELIMITER |
 
DROP PROCEDURE IF EXISTS joomlaUsersGroups |
 
CREATE PROCEDURE joomlaUsersGroups()
 
    BEGIN
        DECLARE table_name VARCHAR(255);
        DECLARE end_of_tables INT DEFAULT 0;
 
        DECLARE cur CURSOR FOR
            SELECT t.table_name
            FROM information_schema.tables t
            WHERE t.table_schema = DATABASE()
                  AND t.table_type = 'BASE TABLE'
                  AND t.table_name LIKE '%user\_usergroup\_map';
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_tables = 1;
 
        OPEN cur;
 
        tables_loop: LOOP
            FETCH cur
            INTO table_name;
 
            IF end_of_tables = 1
            THEN
                LEAVE tables_loop;
            END IF;
 
            SET @s = CONCAT('UPDATE ', table_name, ' SET group_id = XXX WHERE user_id = XXX');
            PREPARE stmt FROM @s;
            EXECUTE stmt;
 
        END LOOP;
 
        CLOSE cur;
    END |
 
DELIMITER ;

Cette procédure marche bien, car je n'avais toujours qu'une seule table qui finissait par "user_usergroup_map" :
- XXX_user_usergroup_map
- YYY_user_usergroup_map

Toutefois, j'ai une autre base sur laquelle faire mon rechercher / remplacer global. Sauf que cette fois, elles finissent par _users. Et ce suffixe existe pour plein de tables :
- jos_users
- jos_jev_users
- jos_fb_users
- ...

Or moi, je ne veux cibler que la table jos_users.
Dans mon cas, le prefixe "jos_" change dans cette base. J'ai 30000 tables en tout
J'aurais donc par exemple en plus :
- academie20_users
- academie20_jev_users
- academie20_fb_users
- ...

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
DELIMITER |
 
DROP PROCEDURE IF EXISTS joomlaUsersGroups |
 
CREATE PROCEDURE joomlaUsersGroups()
 
    BEGIN
        DECLARE table_name VARCHAR(255);
        DECLARE end_of_tables INT DEFAULT 0;
 
        DECLARE cur CURSOR FOR
            SELECT t.table_name
            FROM information_schema.tables t
            WHERE t.table_schema = DATABASE()
                  AND t.table_type = 'BASE TABLE'
                  AND t.table_name LIKE '%users';
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_tables = 1;
 
        OPEN cur;
 
        tables_loop: LOOP
            FETCH cur
            INTO table_name;
 
            IF end_of_tables = 1
            THEN
                LEAVE tables_loop;
            END IF;
 
            SET @s = CONCAT('UPDATE IGNORE ', table_name, ' SET usertype = XXX WHERE id = XXX');
            PREPARE stmt FROM @s;
            EXECUTE stmt;
 
        END LOOP;
 
        CLOSE cur;
    END |
 
DELIMITER ;

Mais ça ne fonctionne pas, car dans certaines tables il n'existe pas de champ ID.
J'ai bien tenté de rajouter IGNORE après UPDATE mais ça ne marche toujours pas :

Citation:

#1054 - Unknown column 'id' in 'where clause'

Je n'y connais rien personnellement là-dedans. Si quelqu'un avait une solution, car mon développeur n'est plus dispo pour un moment.

J'espère avoir été assez clair !

Merci !

Citation:

Envoyé par skouf01

Mais ca ne fonctionne pas car dans certaines tables il n'existe pas de champ ID.

Si vous voulez simplement ignorer les tables qui n'ont pas de colonne id, vous pouvez modifier la requête de déclaration du curseur en ajoutant une jointure sur la table information_schema.columns pour filtrer sur les tables ayant une colonne 'id'
Code:

1 2 3 4 5 6 7 8 9 10 SELECT t.table_name FROM information_schema.TABLES t INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON C.table_schema = t.TABLE_SCHEMA AND c.table_name = t.table_name WHERE t.table_schema = DATABASE() AND t.table_type = 'BASE TABLE' AND t.table_name LIKE '%user\_usergroup\_map' AND c.Column_name = 'id'

Merci beaucoup

La solution fonctionne très bien. J'ai juste dû modifier la casse du "C" pour que ca fonctionne

voici le code final

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
DELIMITER |
 
DROP PROCEDURE IF EXISTS joomlaUsersGroups |
 
CREATE PROCEDURE joomlaUsersGroups()
 
    BEGIN
        DECLARE table_name VARCHAR(255);
        DECLARE end_of_tables INT DEFAULT 0;
 
        DECLARE cur CURSOR FOR
            SELECT t.table_name
            FROM information_schema.tables t
			INNER JOIN INFORMATION_SCHEMA.COLUMNS C
				ON C.table_schema = t.TABLE_SCHEMA
				AND C.table_name = t.table_name
            WHERE t.table_schema = DATABASE()
                  AND t.table_type = 'BASE TABLE'
                  AND t.table_name LIKE '%users'
				  AND C.column_name = 'email';
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_tables = 1;
 
        OPEN cur;
 
        tables_loop: LOOP
            FETCH cur
            INTO table_name;
 
            IF end_of_tables = 1
            THEN
                LEAVE tables_loop;
            END IF;
 
            SET @s = CONCAT('UPDATE ', table_name, ' SET usertype = "Registered" WHERE email = "xxx@xxxxxxxxx.com"');
            PREPARE stmt FROM @s;
            EXECUTE stmt;
 
        END LOOP;
 
        CLOSE cur;
    END |
 
DELIMITER ;

Bon week end !