Discussion :

[mballasse]

Bonjour
Ma base de données Firebird est composée de quelques 300 tables, (version Firebird 2.5 )

Les quelques 300 tables sont toutes constituées d'une "clé primaire logique", c'est à dire un champ de type Integer rempli à l'aide d'un générateur ou d'une procédure stockée appelée par un applicatif métier (développé en Delphi) qui remplit cette valeur.
Je cherche un moyen de parcourir toutes les tables de ma base à la recherche d'éventuels doublons et d'afficher le nom de la table dans laquelle se trouve le doublon et la valeur de la "clé logique".
En théorie (mais comme chacun le sait la théorie c'est bien joli), cela n'est pas possible, mais je voudrais tout de même avoir un moyen de vérifier.

Inutile de me faire un cours théorique sur l'intérêt d'une clé primaire physique, je sais parfaitement quels sont les avantages mais aussi les inconvénients, je cherche juste une solution efficace pour retrouver les éventuels doublons (s'il y en a)

Merci pour votre aide.

[SergioMaster]

Bonjour,

pour récupérer les tables contenant des colonnes indexées RDB$INDICES cela me semble un bon point de départ,

Code :

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

1
2
SELECT I.* FROM RDB$INDICES I 
WHERE I.RDB$UNIQUE_FLAG=1 AND I.RDB$SYSTEM_FLAG=0 and RDB$INDEX_ID=1

on peut déjà obtenir les clés unique (I.RDB$INDEX_NAME) j'ai juste un doute sur le fait que RDB$INDEX_ID=1 indique toujours la clé primaire, dans ton cas, RDB$SEGMENT_COUNT=1 pourrait être un critère supplémentaire

Quelque chose comme

Code :

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

1
2
3
SELECT I.RDB$INDEX_NAME,I.RDB$RELATION_NAME,LIST(TRIM(S.RDB$FIELD_NAME)) FROM RDB$INDICES I LEFT JOIN RDB$INDEX_SEGMENTS S ON I.RDB$INDEX_NAME=S.RDB$INDEX_NAME
WHERE I.RDB$UNIQUE_FLAG=0 AND I.RDB$SYSTEM_FLAG=0 AND i.RDB$FOREIGN_KEY IS NULL
GROUP BY 1,2

devrait fournir les clés à tester

une jointure (Full Join) entre ces deux devrait fournir ce qu'il faut pour tester les doublons

Code :

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

1
2
3
4
5
6
7
8
9
10
 
WITH P AS (SELECT I.RDB$INDEX_NAME ,I.RDB$RELATION_NAME T,LIST(TRIM(S.RDB$FIELD_NAME)) K FROM RDB$INDICES I 
  LEFT JOIN RDB$INDEX_SEGMENTS S ON I.RDB$INDEX_NAME=S.RDB$INDEX_NAME
  WHERE I.RDB$UNIQUE_FLAG=1 AND I.RDB$SYSTEM_FLAG=0 AND i.RDB$FOREIGN_KEY IS NULL GROUP BY 1,2),
     S AS (SELECT I.RDB$INDEX_NAME SK,I.RDB$RELATION_NAME ST,LIST(TRIM(S.RDB$FIELD_NAME)) SC FROM RDB$INDICES I 
     LEFT JOIN RDB$INDEX_SEGMENTS S ON I.RDB$INDEX_NAME=S.RDB$INDEX_NAME
WHERE I.RDB$UNIQUE_FLAG=0 AND I.RDB$SYSTEM_FLAG=0 AND i.RDB$FOREIGN_KEY IS NULL
GROUP BY 1,2)
 
SELECT P.*,S.* FROM P FULL JOIN S ON P.T=S.ST WHERE P.RDB$INDEX_NAME IS NOT NULL AND S.SK IS NOT NULL

si l'on utilise ensuite un EXECUTE STATEMENT à construire à partir de ces résultats en théorie c'est tout bon.

on met tout ça en forme dans un EXECUTE BLOCK car je ne pense pas qu'il faille le mettre dans un procédure et le tour est joué

Voilà en tout cas la théorie à améliorer

[mballasse]

Merci pour ces informations.
Mais les champs sont bien indexés mais comme des indexes simples sans aucune contrainte d'unicité, donc la première requête ne me donne rien.

[SergioMaster]

Bonjour,

j'avais cru comprendre par "clé primaire logique" que la colonne incrémentée était déclarée comme primaire mais surtout unique et déclarée comme index !

Code :

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

1
2
SELECT I.* FROM RDB$INDICES I 
WHERE I.RDB$SYSTEM_FLAG=0 and RDB$SEGMENT_COUNT=1

devrait indiquer les tables avec index ne contenant qu'un seule colonne

il est donc possible pour chaque ligne de cette requête de vérifier s'il y a des colonnes en double ou non
soit par sql soit en tentant de créer un index unique

[Artemus24]

Salut mballasse.

Je cherche un moyen de parcourir toutes les tables de ma base à la recherche d'éventuels doublons

Qu'est-ce que vous nommez doublon ?
--> Une ligne présente plusieurs fois dans une même table.
--> une ligne présente plusieurs fois dans plusieurs tables.

Pour supprimer un doublon dans une table, le mieux est de créer un index unique sur la colonne en question.
Savez-vous comment identifier la colonne qui doit être testée ?

Hormis la suppression des doublons, devez-vous faire autre chose de particulier sur ces tables ?

@+

[escartefigue]

Pour supprimer un doublon dans une table, le mieux est de créer un index unique sur la colonne en question.

Non, ça c'est la méthode préventive pour éviter la création de doublons, mais si des doublons existent déjà, la création d'un index unique pour les colonnes sera rejetée à cause justement de la présence de ces doublons
La méthode curative dépend de la volumétrie des tables à traiter, quelle est elle ?

[SergioMaster]

Bonjour,

je vous renvoie tous deux au début de la discussion

Je cherche un moyen de parcourir toutes les tables de ma base à la recherche d'éventuels doublons et d'afficher le nom de la table dans laquelle se trouve le doublon et la valeur de la "clé logique".
En théorie (mais comme chacun le sait la théorie c'est bien joli), cela n'est pas possible, mais je voudrais tout de même avoir un moyen de vérifier.

dans ma réponse j'ai indiqué qu'il était possible de retrouver les informations d'index, les tables associées etc... le seul hic j'avais extrapolé en partant sur le fait que cette fameuse clé logique était déjà une contrainte de clé primaire ou, à minima un index unique.

le seul problème qui se pose est en fait d'avoir pour chacune des tables à tester au moins un index unique.
Cela dit rien n'empêche de le faire pour chaque table la création d'une colonne numérique, l'incrémenter, en faire une clé unique puis de faire les vérifications de doublons.

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
-- OBJECTIF créer une colonne fiable unique index pour travailler ensuite sur le problème des doublons
-- écrit à la volée, donc non testé en conditions réelles
-- les SUSPEND ne sont là que pour  suivre le déroulement, TABLETRAITEE pourrait très bien être une VARIABLE du bloc
SET TERM !;
-- création de la colonne 
EXECUTE BLOCK RETURNS (TABLETRAITEE VARCHAR(31))  
AS
BEGIN
FOR SELECT TRIM(RDB$RELATION_NAME) FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG=0 AND RDB$RELATION_TYPE=0 INTO :TABLETRAITEE 
  DO BEGIN
    STMT='ALTER TABLE '||:TABLETRAITEE||' ADD VERCOL BIGINT DEFAULT 0 NOT NULL ';
    EXECUTE STATEMENT STMT;
    SUSPEND;
 END
END!
COMMIT !
-- Mise à jour de la colonne + Création de l'index
EXECUTE BLOCK 
RETURNS ( TABLETRAITEE VARCHAR(31))
AS
DECLARE STMT VARCHAR(1024);
DECLARE STMTU VARCHAR(1024);
DECLARE N BIGINT;
DECLARE DBKEY CHAR(8); -- à vérifier, l'utilisation et la taille 
BEGIN
FOR SELECT TRIM(RDB$RELATION_NAME) FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG=0 AND RDB$RELATION_TYPE=0 INTO :TABLETRAITEE 
  DO BEGIN
    STMT='SELECT RDB$DB_KEY FROM '||:TABLETRAITEE;
    STMTU='UPDATE '||:TABLETRAITEE||' SET COLVER=? WHERE RDB$DB_KEY=?';
    N=0;
    FOR EXECUTE STATEMENT STMT INTO :DBKEY
      DO BEGIN
        EXCUTE STATEMENT (STMTU) (:N,:DBKEY); -- statement paramétré
        N=N+1;
      END       
    STMT='CREATE UNIQUE INDEX '||:TABLETRAITEE||'_CVIDX ON ':TABLETRAITEE; -- un seul truc peut clocher la longueur du nom de l'index !
    EXECUTE STATEMENT STMT;     
    SUSPEND;
 END
END!

--- Suite recherche des doublons

Donc, en théorie (pour reprendre l'expression), c'est possible ! cela prendra du temps ,certainement plus que de vérifier les 300 tables une à une à la main s'il n'y a qu'une seule BDD à traiter mais s'il y en a des dizaines ....

[mballasse]

Pour être plus précis sur la structure de ma base de données, je possède quelques 300 tables structurées ainsi :

CREATE TABLE TEST
(
ID INTEGER NOT NULL,,
OUT INTEGER,
REPLIC INTEGER NOT NULL,
CHAMP1 ...
CHAMP2 ...
CHAMP n...
)

CREATE INDEX TESTINDEX 1 ON TEST(ID);

ID : champ "clé logique technique" integer , not null , "normalement" unique pour la base de données, rempli à l'aide d'un générateur directement en SQL ou en passant par une procédure stockée appelée par le programme
OUT : flag integer me disant si l'enregsitrement est supprimé ou pas (aucune suppression physique dans la base)
REPLIC : champ contenant un numéro de version dans la base de données unique et incrémenté à chaque modification me permettant de faire fonctionner un système de réplication de données entre base distantes
champ1 à champN : les champs de ma table

Il n'y a pas d'index unique, pas d'index de clé primaire.

Concernant le nombre d'enregistrements, ça peut varier de quelques dizaines à plus de 4 millions pour la plus grosse table.

[mballasse]

Salut mballasse.


Qu'est-ce que vous nommez doublon ?
--> Une ligne présente plusieurs fois dans une même table.
--> une ligne présente plusieurs fois dans plusieurs tables.

Pour supprimer un doublon dans une table, le mieux est de créer un index unique sur la colonne en question.
Savez-vous comment identifier la colonne qui doit être testée ?

Hormis la suppression des doublons, devez-vous faire autre chose de particulier sur ces tables ?

@+

Un doublon est une valeur qui peut être en double dans la même table (ça c'est facile à retrouver) mais elle pourrait éventuellement être présente dans une autre table

La colonne à tester porte toujours le même nom quelque soit la table : ID

[SergioMaster]

Re,

ID : champ "clé logique technique" integer , not null , "normalement" unique pour la base de données,

Un doublon est une valeur qui peut être en double dans la même table (ça c'est facile à retrouver) mais elle pourrait éventuellement être présente dans une autre table

c'est donc ID qu'il faut tester pour toutes les tables ! je ne l'avais pas compris ainsi du coup il faut construire en une union entre toutes les tables
(ID,NOMTABLE) puis chercher à l'intérieur de ce résultat s'il existe ID en double

- utiliser une GTT pour récupérer les données

Code :

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

CREATE GLOBAL TEMPORARY TABLE TESTERDOUBLON (ID INTEGER,NOMTABLE VARCHAR(31));

-puis

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
SET TERM !; 
EXECUTE BLOCK 
RETURNS( ID INTEGER, 
              NOMTABLE VARCHAR(31))
AS
DECLARE VARIABLE N INTEGER;
DECLARE VARIABLE T VARCHAR(31);
DECLARE STMT VARCHAR(1024);
BEGIN
N=0;
FOR SELECT TRIM(RDB$RELATION_NAME) FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG=0 AND RDB$RELATION_TYPE=0 INTO :T 
 DO BEGIN
   STMT='SELECT ID FROM '||:T;
   FOR EXECUTE STATEMENT STMT INTO :N DO
     INSERT INTO  TESTERDOUBLON(ID,NOMTABLE) VALUES (:N,:T); 
 END
-- Doublons 
FOR  
WITH D AS (SELECT  COUNT(lD),ID FROM  TESTER DOUBLON GROUP BY ID HAVING  COUNT(ID) > 1
 SELECT TD.ID,TD.NOMTABLE FROM TESTERDOUBLON TD JOIN D ON TD.ID=D.ID INTO :ID,:NOMTABLE 
DO SUSPEND;
 
END!

Bon, encore une fois, écrit sans trop de contrôles et donc à améliorer

[Artemus24]

Salut à tous.

le seul problème qui se pose est en fait d'avoir pour chacune des tables à tester au moins un index unique.

Oui, sinon comme identifier d'une manière unique les lignes en doublons.

Cela dit rien n'empêche de le faire pour chaque table la création d'une colonne numérique, l'incrémenter, en faire une clé unique puis de faire les vérifications de doublons.

Je pense que la règle imposée par mballasse est de ne pas modifier la base de données. A lui de confirmer que c'est bien le cas.

Pour être plus précis sur la structure de ma base de données, je possède quelques 300 tables structurées ainsi :

Voulez-vous dire que vos 300 tables ont toutes la même structure et les mêmes noms de colonnes ?

Il n'y a pas d'index unique, pas d'index de clé primaire.

Oups !!! Bonjour la performance.

Un doublon est une valeur qui peut être en double dans la même table (ça c'est facile à retrouver) mais elle pourrait éventuellement être présente dans une autre table

C'est bien ce qui me semblait. Par doublon, vous avez les deux notions, à savoir dans une table et inter-table, si je peux m'exprimer ainsi.

La solution est en fait de fusionner toutes vos tables en une seule, afin d'identifier d'une manière simple vos doublons.

La colonne à tester porte toujours le même nom quelque soit la table : ID

Il vaut mieux, sinon cela va compliquer la requête.

J'ai repris l'excellente analyse de SergioMaster, et j'ai créé un jeu d'essai, histoire de voire ce que cela donne à l'exécution.
pour la colonne 'id', j'ai mis une chaîne de caractères.

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
CREATE DATABASE '..\..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET WIN1252;
 
-- ============================
-- Création de la table 'test1'
-- ============================
 
create table test1
( id  char(20)  not null
);
 
-- ======================
-- Insertion dans 'test1'
-- ======================
 
insert into test1 (id) values ('un');
insert into test1 (id) values ('cinq');
insert into test1 (id) values ('huit');
insert into test1 (id) values ('dix');
insert into test1 (id) values ('quinze');
insert into test1 (id) values ('vingt-trois');
insert into test1 (id) values ('dix');
insert into test1 (id) values ('trente-deux');
 
-- =================
-- Vidage de 'test1'
-- =================
 
select * from test1;
 
ID
====================
un
cinq
huit
dix
quinze
vingt-trois
dix
trente-deux
 
 
-- ============================
-- Création de la table 'test2'
-- ============================
 
create table test2
( id  char(20)  not null
);
 
-- ======================
-- Insertion dans 'test2'
-- ======================
 
insert into test2 (id) values ('deux');
insert into test2 (id) values ('trois');
insert into test2 (id) values ('cinq');
insert into test2 (id) values ('neuf');
insert into test2 (id) values ('onze');
insert into test2 (id) values ('quinze');
insert into test2 (id) values ('dix-huit');
insert into test2 (id) values ('neuf');
 
-- =================
-- Vidage de 'test2'
-- =================
 
select * from test2;
 
ID
====================
deux
trois
cinq
neuf
onze
quinze
dix-huit
neuf
 
 
-- ============================
-- Création de la table 'test3'
-- ============================
 
create table test3
( id  char(20)  not null
);
 
-- ======================
-- Insertion dans 'test3'
-- ======================
 
insert into test3 (id) values ('quatre');
insert into test3 (id) values ('huit');
insert into test3 (id) values ('douze');
insert into test3 (id) values ('trente-trois');
insert into test3 (id) values ('douze');
insert into test3 (id) values ('quarente-deux');
insert into test3 (id) values ('cinquante-trois');
insert into test3 (id) values ('cent-vingt-huit');
 
-- =================
-- Vidage de 'test3'
-- =================
 
select * from test3;
 
ID
====================
quatre
huit
douze
trente-trois
douze
quarente-deux
cinquante-trois
cent-vingt-huit
 
 
commit;
 
-- ===========
-- Requête N°1
-- ===========
 
select  trim(rdb$relation_name) as name
  from  rdb$relations
 where  rdb$system_flag = 0;
 
NAME
===============================
TEST1
TEST2
TEST3
 
 
-- ===========
-- Requête N°3
-- ===========
 
CREATE GLOBAL TEMPORARY TABLE doublon (lieu char(20), id char(20));
 
-- ===========
-- Requête N°2
-- ===========
 
SET TERM #;
 
execute block
returns (lieu  char(20),
         id    char(20))
as
  declare variable name varchar(30);
  declare variable stmt varchar(4000) default '';
begin
  for select  trim(rdb$relation_name)
        from  rdb$relations
       where  rdb$system_flag = 0
  into :name
  do
  begin
    stmt = 'select id from ' || :name;
 
    for execute statement stmt into :id
    do  insert into doublon (lieu, id) values (:name, :id);
  end
 
  for
    with cte AS (  select  id, count(id) as nbre
                     from  doublon
                 group by  id
                   having  count(id) > 1  )
    select  t1.lieu, t1.id
      from  doublon as t1
      join  cte     as t2
        on  t2.id = t1.id
  order by  id
  into :lieu, :id
  do suspend;
 
end#
 
LIEU                 ID
==================== ====================
TEST1                cinq
TEST2                cinq
TEST1                dix
TEST1                dix
TEST3                douze
TEST3                douze
TEST1                huit
TEST3                huit
TEST2                neuf
TEST2                neuf
TEST1                quinze
TEST2                quinze
 
 
SET TERM ;#
 
exit;
 
Appuyez sur une touche pour continuer...

Avant de reprendre l'exemple de SergioMaster, j'ai fait une tentative, sans créer une table temporaire, mais en passant par un "union all".
Or je ne sais pas pourquoi, tous les doublons dans une table donnée, ont été supprimés.

@+

[mballasse]

Merci pour vos réponses à tous
Pour compléter la remarque, il y a bien un index sur la colonne de chaque table mais il n'y a pas la contrainte unique
Les tables n'ont évidemment toutes la même structure, à un détail près, c'est qu'elles ont toutes les 3 champs techniques précisés ci dessus (ID, OUT, REPLIC)
je ne vais bien évidemment pas modifier ma base de données, il me faut "idéalement " une routine Live !!!!

[SergioMaster]

Bonjour,

je ne vais bien évidemment pas modifier ma base de données, il me faut "idéalement " une routine Live !

je ne pense pas que créer une GTT soit un entorse énorme puisque ce n'est qu'un création de table destructible par la suite. je ne sais pas si cette GTT peut se créer dans l'EXECUTE BLOCK (enfin on peut certainement la créer mais sera-t-elle utilisable ?)

un script SQL plus complet devrait être ce que tu nommes "routine live"

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
 
CREATE GLOBAL TEMPORARY TABLE TESTERDOUBLON (ID INTEGER,NOMTABLE VARCHAR(31));
COMMIT;
SET TERM !; 
EXECUTE BLOCK 
RETURNS( ID INTEGER, 
              NOMTABLE VARCHAR(31))
AS
DECLARE VARIABLE N INTEGER;
DECLARE VARIABLE T VARCHAR(31);
DECLARE STMT VARCHAR(1024);
BEGIN
N=0;
FOR SELECT TRIM(RDB$RELATION_NAME) FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG=0 AND RDB$RELATION_TYPE=0 INTO :T 
 DO BEGIN
   STMT='SELECT ID FROM '||:T;
   FOR EXECUTE STATEMENT STMT INTO :N DO
     INSERT INTO  TESTERDOUBLON(ID,NOMTABLE) VALUES (:N,:T); 
 END
-- Doublons 
FOR  
WITH D AS (SELECT  COUNT(lD),ID FROM  TESTER DOUBLON GROUP BY ID HAVING  COUNT(ID) > 1
 SELECT TD.ID,TD.NOMTABLE FROM TESTERDOUBLON TD JOIN D ON TD.ID=D.ID INTO :ID,:NOMTABLE 
DO SUSPEND;
END!
SET TERM ; !
DROP TABLE TESTERDOUBLON;

[mballasse]

Merci a vous tous, je teste cela au plus vite et je vous fais un retour