Extraction PK et FK depuis les RDB$ [Résolu]

[qi130]

Bonjour,

Je cherche à obtenir en 1 seule requète le nb de PK et le nb de FK définies sur les tables "utilisateur" d'une DB

J'ai donc sorti mon guide SQL pour programmer ça:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT a.rdb$relation_name AS Tabl, a.rdb$field_id AS Nb_col, count(b.RDB$FIELD_NAME) AS Nb_PK
FROM  rdb$index_segments b
INNER JOIN rdb$relation_constraints r ON b.rdb$index_name=r.rdb$index_name
                                     AND r.rdb$constraint_type='PRIMARY KEY'
INNER JOIN rdb$relations a ON a.rdb$relation_name=r.rdb$relation_name
WHERE a.RDB$SYSTEM_FLAG=0
union
SELECT c.rdb$relation_name AS Tabl, c.rdb$field_id AS Nb_col,count(z.RDB$FIELD_NAME) AS Nb_FK
FROM rdb$index_segments z
INNER JOIN rdb$relation_constraints k ON z.rdb$index_name=k.rdb$index_name
                                     AND k.rdb$constraint_type='FOREIGN KEY'
INNER JOIN rdb$relations c ON c.rdb$relation_name=k.rdb$relation_name
WHERE c.RDB$SYSTEM_FLAG=0
GROUP BY a.rdb$relation_name ,a.rdb$field_id

et devinez...

Envoyé par IBexpert

Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

Et même punition avec le group by embarquant c.rdb$relation_name et c.rdb$field_id en +
Idem en partant de rdb$relations et le 2nd inner sur rdb$index_segments

Par contre

Code :

1
2
3
4
5
6
7
SELECT a.rdb$relation_name AS Tabl, a.rdb$field_id AS Nb_col, count(b.RDB$FIELD_NAME) AS Nb_PK
FROM rdb$relations a
INNER JOIN rdb$relation_constraints r ON a.rdb$relation_name=r.rdb$relation_name
                                     AND r.rdb$constraint_type='PRIMARY KEY'
INNER JOIN rdb$index_segments b ON b.rdb$index_name=r.rdb$index_name
WHERE a.RDB$SYSTEM_FLAG=0
GROUP BY a.rdb$relation_name ,a.rdb$field_id

marche correctement, mais je n'ai que les PK...
D'où l'idée du UNION.

Qui peut m'aider ?

[SergioMaster]

Salut

pour les FK voir mon post et ses suites ici

[Cl@udius]

Salut

Il te manque un group by sur le 1° select avant l'union. Non ?

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
SELECT
  A.RDB$RELATION_NAME AS TABL,
  A.RDB$FIELD_ID AS NB_COL,
  COUNT(B.RDB$FIELD_NAME) AS NB_PK
FROM
  RDB$INDEX_SEGMENTS B
  JOIN RDB$RELATION_CONSTRAINTS R
    ON B.RDB$INDEX_NAME=R.RDB$INDEX_NAME AND R.RDB$CONSTRAINT_TYPE='PRIMARY KEY'
  JOIN RDB$RELATIONS A
    ON A.RDB$RELATION_NAME=R.RDB$RELATION_NAME
WHERE
  A.RDB$SYSTEM_FLAG=0
GROUP BY
  1, 2
 
UNION
SELECT
  C.RDB$RELATION_NAME AS TABL,
  C.RDB$FIELD_ID AS NB_COL,
  COUNT(Z.RDB$FIELD_NAME) AS NB_FK
FROM
  RDB$INDEX_SEGMENTS Z
INNER JOIN
  RDB$RELATION_CONSTRAINTS K
    ON Z.RDB$INDEX_NAME=K.RDB$INDEX_NAME AND K.RDB$CONSTRAINT_TYPE='FOREIGN KEY'
  JOIN RDB$RELATIONS C
    ON C.RDB$RELATION_NAME=K.RDB$RELATION_NAME
WHERE
  C.RDB$SYSTEM_FLAG=0
GROUP BY
  1, 2

Sinon en ce moment je suis sur les CTEs, j'ai ça aussi:

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
WITH
  PKS AS (
    SELECT
      A.RDB$RELATION_NAME AS TABL,
      A.RDB$FIELD_ID AS NB_COL,
      COUNT(B.RDB$FIELD_NAME) AS NB_PK,
      LIST(TRIM(B.RDB$FIELD_NAME)) PK_LIST
    FROM
      RDB$INDEX_SEGMENTS B
      JOIN RDB$RELATION_CONSTRAINTS R
        ON B.RDB$INDEX_NAME=R.RDB$INDEX_NAME AND R.RDB$CONSTRAINT_TYPE='PRIMARY KEY'
      JOIN RDB$RELATIONS A
        ON A.RDB$RELATION_NAME=R.RDB$RELATION_NAME
    WHERE
      A.RDB$SYSTEM_FLAG=0
    GROUP BY
      1, 2
  ),
  FKS AS (
    SELECT
      C.RDB$RELATION_NAME AS TABL,
      COUNT(Z.RDB$FIELD_NAME) AS NB_FK,
      LIST(TRIM(Z.RDB$FIELD_NAME)) FK_LIST
    FROM
      RDB$INDEX_SEGMENTS Z
    JOIN RDB$RELATION_CONSTRAINTS K
        ON Z.RDB$INDEX_NAME=K.RDB$INDEX_NAME AND K.RDB$CONSTRAINT_TYPE='FOREIGN KEY'
    JOIN RDB$RELATIONS C
        ON C.RDB$RELATION_NAME=K.RDB$RELATION_NAME
    WHERE
      C.RDB$SYSTEM_FLAG=0
    GROUP BY
      1
  )
SELECT
  PKS.TABL, PKS.NB_COL, PKS.NB_PK, PKS.PK_LIST, FKS.NB_FK, FKS.FK_LIST
FROM
  PKS LEFT JOIN FKS ON PKS.TABL = FKS.TABL

@+ Claudius

[qi130]

Haaaa !

avec le group by 1,2 la requète passe, mais les FK ne sont pas affichées (pas de colonne Nb_FK dans le grid....

[Cl@udius]

Comme tu fais un union les champs NB_PK et NB_FK ne font qu'un et porte le nom de NB_PK celui de la 1° requête.

[qi130]

Alors, retour à la case départ

[Cl@udius]

As-tu essayé ma 2° requête avec les CTEs (si tu es sous 2.1) ?

[Cl@udius]

Sinon et comme ça ? C'est fait à l'arrache, surement moyen de faire mieux.

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
SELECT
  A.RDB$RELATION_NAME AS TABL,
  A.RDB$FIELD_ID AS NB_COL,
  COUNT(B.RDB$FIELD_NAME) AS NB_PK,
  FKS.NB_FK
FROM
  RDB$INDEX_SEGMENTS B
  JOIN RDB$RELATION_CONSTRAINTS R
    ON B.RDB$INDEX_NAME=R.RDB$INDEX_NAME AND R.RDB$CONSTRAINT_TYPE='PRIMARY KEY'
  JOIN RDB$RELATIONS A
    ON A.RDB$RELATION_NAME=R.RDB$RELATION_NAME
  LEFT JOIN (
    SELECT
      A.RDB$RELATION_NAME AS TABL,
      COUNT(B.RDB$FIELD_NAME) AS NB_FK
    FROM
      RDB$INDEX_SEGMENTS B
      JOIN RDB$RELATION_CONSTRAINTS R
        ON B.RDB$INDEX_NAME=R.RDB$INDEX_NAME AND R.RDB$CONSTRAINT_TYPE='FOREIGN KEY'
      JOIN RDB$RELATIONS A
        ON A.RDB$RELATION_NAME=R.RDB$RELATION_NAME
    WHERE
      A.RDB$SYSTEM_FLAG=0
    GROUP BY
      1) FKS
   ON A.RDB$RELATION_NAME = FKS.TABL
 
WHERE
  A.RDB$SYSTEM_FLAG=0
GROUP BY
  1, 2, 4;

@+

[qi130]

Nickel !



à tous.

[SergioMaster]

@Cl@udius
désolé mais je ne crois pas que les valeurs retournées soit exacte , je me retouve avec une table ayant 4 pk et 5 fk alors que celle-ci , je dirais "se trouve en bout de chaine"
1 pk et 4fk
de même j'ai une table temporaire sans index du tout qui se retrouve avec 10 pk

cependant cela me donne une bonne piste de travail , bien que celui-ci soit en cours d'abandon sauf a mes temps perdus

[Cl@udius]

Salut Sergio

Envoyé par SergioMaster

désolé mais je ne crois pas que les valeurs retournées soit exacte

Ben ça c'est possible

Envoyé par Cl@udius

C'est fait à l'arrache, surement moyen de faire mieux.

Je vais y jeter un coup d'oeil...

@+ Claudius

[qi130]

Pour apporter mon grain de sel:

J'ai eu comme Serge des chiffres bizarres, mais en fouillant dans les tables RDB$, j'y ai vu la même chose (genre 4 FK au lieu d'1), et IBexpert rapporte ces 4 FK dans l'onglet DDL (3 fois la même)

Alors j'incrimine autre chose que le script de Cl@udius:
- mon outil case ? (faudra que je regénère le modèle)
- le moteur ? (base créée sous IB6 dont on connait les lacunes)

A la limite, si les chiffres diffèrent entre cette requète et un clone embarquant un distinct, c'est un bon outil de contrôle

Ca permettra au moins d'éliminer des index qui pénalisent les perfs


Autre point: la requète n'est pas digérée sous Delphi avec un compo FBLDsql (oui je sais, c'est exotique, mais FBLib est utilisable sous D6PE )

Quelqu'un peut essayer avec un compo + "sérieux" ?
erreur: token unknow SELECT (le 2nd)

[Cl@udius]

Salut

Et avec cette requête, est-ce que les résultats sont plus cohérents ?

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
SELECT
  R.RDB$RELATION_NAME TBL_NAME,
  R.RDB$FIELD_ID AS TBL_FLD_COUNT,
  I_S.RDB$INDEX_NAME PK_NAME,
  COUNT(I_S.RDB$FIELD_NAME) PK_FIELD_COUNT,
  FKs.FK_COUNT
FROM
  RDB$RELATIONS R
  JOIN RDB$RELATION_CONSTRAINTS R_C
    ON R.RDB$RELATION_NAME = R_C.RDB$RELATION_NAME
  JOIN RDB$INDEX_SEGMENTS I_S
    ON R_C.RDB$INDEX_NAME = I_S.RDB$INDEX_NAME AND
      R_C.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
  LEFT JOIN (
    SELECT
      R.RDB$RELATION_NAME TBL_NAME,
      COUNT(DISTINCT R_C.RDB$CONSTRAINT_NAME) FK_COUNT
    FROM
      RDB$RELATIONS R
      JOIN RDB$RELATION_CONSTRAINTS R_C
        ON R.RDB$RELATION_NAME = R_C.RDB$RELATION_NAME
      JOIN RDB$INDEX_SEGMENTS I_S
        ON R_C.RDB$INDEX_NAME = I_S.RDB$INDEX_NAME AND
          R_C.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'
    WHERE
      R.RDB$SYSTEM_FLAG = 0
    GROUP BY
      1
  ) FKs
    ON R.RDB$RELATION_NAME = FKs.TBL_NAME
WHERE
  R.RDB$SYSTEM_FLAG = 0
GROUP BY
  1, 2, 3, 5;

Envoyé par qi130

Quelqu'un peut essayer avec un compo + "sérieux" ?
erreur: token unknow SELECT (le 2nd)

Je vais tester avec les UIB.

@+ Claudius

[Cl@udius]

Envoyé par Cl@udius

Je vais tester avec les UIB.

Test done: OK avec la requête ci-dessus.

[SergioMaster]

Cela me parait plus cohérent
j'ai utilisé Flamerobin pour tester une base un peu , beaucoup, complexe
à priori c'est nickel.

Dés que j'ai un peu de temps je teste avec FIBplus

[qi130]

Bon en fait, la requète marche aussi avec fblib (je devais pas être ok hier soir )

encore à vous 2