Discussion :

[saberanaibi]

j'ai utilisé Postgresql comme administration de base de données . J'ai cré des tables dans ma base de donné
Je veux générer un diagramme de classes à partir de la base ou bien à partir su script sql .
Est-ce qu'il y a des idées svp c'est urgent ?

[Sve@r]

Bonjour

Avec cette requête select schemaname, relname from pg_stat_user_tables order by schemaname, relname tu peux récupérer la liste des schémas et tables de ta base

Avec celle là select column_name, data_type, character_maximum_length, numeric_precision, numeric_precision_radix, numeric_scale from information_schema.columns where (table_schema, table_name)=("schemaX", "tableY") order by ordinal_position tu récupères les colonnes d'une table Y placée dans un schema X

Et avec cette dernière select pg_constraint.conrelid, pg_constraint.confrelid, pg_constraint.conkey, pg_constraint.confkey, t2.schemaname, t2.relname from pg_constraint inner join pg_stat_user_tables as t1 on (t1.relid=pg_constraint.conrelid) inner join pg_stat_user_tables as t2 on (t2.relid=pg_constraint.confrelid) where (pg_constraint.contype, t1.schemaname, t1.relname)=("f", "schemaX", "tableY") tu récupères les contraintes d'intégrité qui lient la table Y du schéma X aux autres tables.

Avec tout ça tu peux créer ton diagramme de classes. Perso j'utilise graphviz. Mais je passe par un langage externe (Python) pour pouvoir interfacer Postgres (psycopg) et manipuler les informations récupérées par ces requêtes.

[SQLpro]

Et avec cette dernière select pg_constraint.conrelid, pg_constraint.confrelid, pg_constraint.conkey, pg_constraint.confkey, t2.schemaname, t2.relname from pg_constraint inner join pg_stat_user_tables as t1 on (t1.relid=pg_constraint.conrelid) inner join pg_stat_user_tables as t2 on (t2.relid=pg_constraint.confrelid) where (pg_constraint.contype, t1.schemaname, t1.relname)=("f", "schemaX", "tableY") tu récupères les contraintes d'intégrité qui lient la table Y du schéma X aux autres tables.

Plutôt que cette dernière utilisez les vues INFORMATION_SCHEMA qui sont la norme et non pg_constraint qui est spécifique.

1) la liste des contraintes FK :
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

2) la liste des colonnes utilisées par les contraintes
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

3) la liste des contraintes PK ou UNIQUE en référence
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')


Mais pour tout ceci il serait plus intéressant d'utiliser un outil qui fait cela tout seul comme Power AMC (rétro ingénierie).

A +

[Sve@r]

Plutôt que cette dernière utilisez les vues INFORMATION_SCHEMA qui sont la norme et non pg_constraint qui est spécifique.

Oui, tu l'avais déjà dit dans cette discussion.

Ben je suis désolé mais étant donné qu'un nom de contrainte (y compris dans un schéma particulier) peut ne pas être unique dans une bdd, il est absolument impossible, pour deux contraintes d'intégrité portant le même nom, de discerner laquelle est laquelle en utilisant les vues standards.

J'ai créé une bdd pour l'occasion

Code sql :

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
drop database if exists "essai";
 
create database "essai"
	template = "template0"
	encoding = 'utf8';
 
\connect "essai"
 
create table "join_toto"(
	"id1_join_toto" integer not null,
	"id2_join_toto" integer not null,
	primary key("id1_join_toto", "id2_join_toto")
);
create table "base_toto"(
	"id_base_toto" integer not null,
	"ref1_base_toto" integer not null,
	"ref2_base_toto" integer not null,
	constraint "fk_xxx" foreign key ("ref1_base_toto", "ref2_base_toto")
		references "join_toto"("id2_join_toto", "id1_join_toto") match full
		on update cascade on delete cascade,
	primary key("id_base_toto")
);
 
create table "join_titi"(
	"id1_join_titi" integer not null,
	"id2_join_titi" integer not null,
	primary key("id1_join_titi", "id2_join_titi")
);
create table "base_titi"(
	"id_base_titi" integer not null,
	"ref1_base_titi" integer not null,
	"ref2_base_titi" integer not null,
	constraint "fk_xxx" foreign key ("ref1_base_titi", "ref2_base_titi")
		references "join_titi"("id2_join_titi", "id1_join_titi") match full
		on update cascade on delete cascade,
	primary key("id_base_titi")
);

J'ai soigneusement épluché les 67 tables qui sont dans "information_schema" et nulle part je n'ai trouvé d'enregistrement qui associe de façon certaine une chaine contenant "base_toto" avec "cat_toto" (ni directement, ni indirectement comme par exemple "base_toto" avec "cat_toto_pkey").
On peut associer "base_toto" avec "fk_xxx", puis "base_toto"+"fk_xxx" avec "ref1_base_toto" et "ref2_base_toto" mais nulle part on trouve d'association entre "base_toto" et "cat_toto". Et le nom de la contrainte "public.fk_xxx" n'étant pas unique, il s'associe alors avec "public.cat_toto" mais aussi avec "public.cat_titi".

Pour info, la liste de toutes les tables essayées
information_schema.administrable_role_authorizations
information_schema.applicable_roles
information_schema.attributes
information_schema.character_sets
information_schema.check_constraint_routine_usage
information_schema.check_constraints
information_schema.collation_character_set_applicability
information_schema.collations
information_schema.column_domain_usage
information_schema.column_options
information_schema.column_privileges
information_schema.columns
information_schema.column_udt_usage
information_schema.constraint_column_usage
information_schema.constraint_table_usage
information_schema.data_type_privileges
information_schema.domain_constraints
information_schema.domains
information_schema.domain_udt_usage
information_schema.element_types
information_schema.enabled_roles
information_schema.foreign_data_wrapper_options
information_schema.foreign_data_wrappers
information_schema.foreign_server_options
information_schema.foreign_servers
information_schema.foreign_table_options
information_schema.foreign_tables
information_schema.information_schema_catalog_name
information_schema.key_column_usage
information_schema.parameters
information_schema._pg_foreign_data_wrappers
information_schema._pg_foreign_servers
information_schema._pg_foreign_table_columns
information_schema._pg_foreign_tables
information_schema._pg_user_mappings
information_schema.referential_constraints
information_schema.role_column_grants
information_schema.role_routine_grants
information_schema.role_table_grants
information_schema.role_udt_grants
information_schema.role_usage_grants
information_schema.routine_privileges
information_schema.routines
information_schema.schemata
information_schema.sequences
information_schema.sql_features
information_schema.sql_implementation_info
information_schema.sql_languages
information_schema.sql_packages
information_schema.sql_parts
information_schema.sql_sizing
information_schema.sql_sizing_profiles
information_schema.table_constraints
information_schema.table_privileges
information_schema.tables
information_schema.transforms
information_schema.triggered_update_columns
information_schema.triggers
information_schema.udt_privileges
information_schema.usage_privileges
information_schema.user_defined_types
information_schema.user_mapping_options
information_schema.user_mappings
information_schema.view_column_usage
information_schema.view_routine_usage
information_schema.views
information_schema.view_table_usage

[SQLpro]

Et bien tu n'as pas bien cherché, car tout objet possède un identifiant SQL unique unique dans une base, mais un identifiant SQL est TOUJOURS constitué de deux parties :
1) le préfixe de schéma
2) le nom de l'objet.

Démonstration :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE SCHEMA S1;
 
CREATE TABLE S1.T (C INT NOT NULL);
 
CREATE SCHEMA S2;
 
CREATE TABLE S2.T (C INT NOT NULL);
 
ALTER TABLE S1.T ADD CONSTRAINT K PRIMARY KEY (C);
 
ALTER TABLE S2.T ADD CONSTRAINT K PRIMARY KEY (C);

Code :

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

1
2
3
SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME 
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY'

Code :

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

1
2
3
4
CONSTRAINT_SCHEMA  CONSTRAINT_NAME 
-----------------  ---------------
s1                 k
s2                 k

Et maintenant essayons de créer une autre contrainte de nom K pour l'une des deux tables :

Code :

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

ALTER TABLE S1.T ADD CONSTRAINT K CHECK (C > 0);

ERROR: ERREUR: la contrainte « k » de la relation « t » existe déjà
SQL state: 42710

CQFD

C'est juste que vous ne savez pas ce qu'est un schéma et que les messages de PostgreSQL sont complétement con.

Apprenez le langage SQL. Mon site web comme mon bouquin peuvent vous y aider :


A +

[Sve@r]

Et bien tu n'as pas bien cherché

Mouais. Sauf que c'est pas moi qui ai cherché, mais un programme. J'ai d'abord tout descendu le contenu de chacune des 67 tables déjà citées dans des fichiers textes (un fichier par table) puis mon programme (écrit en Python) a cherché les fichiers qui contenaient à la fois "base_toto" et à la fois "cat_toto" dans (au-moins) une ligne (juste demandé for lig in fp: if "base_toto" in lig and if "join_toto" in lig: return n "n" étant le nom du fichier traité).
Le seul fichier qu'il a trouvé ça a été un fichier texte que j'avais créé pour tester...

, car tout objet possède un identifiant SQL unique unique dans une base, mais un identifiant SQL est TOUJOURS constitué de deux parties :
1) le préfixe de schéma
2) le nom de l'objet.

Oui. C'est pour ça que dans mon post précédent j'ai bien précisé "public." chaque fois que je parlais de mes objets. Pour bien montrer que j'avais pris en compte ce fait et que, dans cet exemple, j'avais tout créé dans le schéma "public". Mais tout aurait pu se faire aussi dans le schéma "zorglub" que cela n'aurait rien changé.

Démonstration :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE SCHEMA S1;
 
CREATE TABLE S1.T (C INT NOT NULL);
 
CREATE SCHEMA S2;
 
CREATE TABLE S2.T (C INT NOT NULL);
 
ALTER TABLE S1.T ADD CONSTRAINT K PRIMARY KEY (C);
 
ALTER TABLE S2.T ADD CONSTRAINT K PRIMARY KEY (C);

Code :

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

1
2
3
SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME 
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY'

Code :

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

1
2
3
4
CONSTRAINT_SCHEMA  CONSTRAINT_NAME 
-----------------  ---------------
s1                 k
s2                 k

Pfff ça démontre rien. Tu bosses dans deux schémas différents. Comme je l'ai dit maintes et maintes fois, ce souci apparait quand deux contraintes de même nom sont présentes dans le même schéma...

Et maintenant essayons de créer une autre contrainte de nom K pour l'une des deux tables :

... mais dans deux tables différentes !!! T'as essayé mon propre exemple au moins (au lieu de me montrer le tien qui n'a rien à voir) ??? Tu arrives ensuite à récupérer la bonne table de la bonne contrainte ???

C'est juste que vous ne savez pas ce qu'est un schéma

Oui, on n'a qu'à dire ça

Apprenez le langage SQL.

Parce que j'ai l'air de ne pas le connaitre ??? J'ai pourtant sorti quelques requêtes qui, selon moi, montraient que je m'en sortais à peu près en SQL.
Surtout que ce n'est pas un souci de SQL en ce sens que ce n'est pas un souci de syntaxe ou de requête complexe => n'importe quel débutant en SQL s'en sortirait si on lui disait "faut taper dans telle table en relation avec telle autre et en relation avec telle autre" (deux jointures et c'est réglé) ; c'est vraiment un souci de "tu dis que c'est possible mais sans montrer comment".
Alors en 2015 j'étais impressionné par tes références et je me suis dit "si un type de cette carrure le dit ça doit être vrai j'ai qu'à chercher" mais j'avais pas trouvé à l'époque. Et tu le redis encore aujourd'hui là encore sans dire ni comment ni par où donc là je me suis mis à chercher sérieusement et cette fois je le dis haut et fort: avec ma base "essai" telle que je l'ai écrite, il est impossible, en utilisant uniquement les vues standards, de déterminer que "join_toto" est en relation avec "base_toto" et avec elle seule.

[SQLpro]

T'as essayé mon propre exemple au moins (au lieu de me montrer le tien qui n'a rien à voir) ??? Tu arrives ensuite à récupérer la bonne table de la bonne contrainte ???

En effet c'est une erreur dans PostgreSQL qui fait que le nom de la contrainte n'est pas unique sauf à travers différents objets.

Ce n'est pas grave, car dans ce cas il faut faire une jointure sur nom de table d'objet, schema et nom de contrainte.

Mais je te confirme que c'est un bug….

A +

[Sve@r]

En effet c'est une erreur dans PostgreSQL qui fait que le nom de la contrainte n'est pas unique sauf à travers différents objets.

Je veux bien le croire (tu dois connaitre la norme mieux que moi). Mais c'est alors un bug de conception et non un bug de "oups j'ai mal écrit mon code"...

Ce n'est pas grave, car dans ce cas il faut faire une jointure sur nom de table d'objet, schema et nom de contrainte.

Hé si j'avais pu... Le nom de la table n'apparait pas dans les tables système (enfin pas là où apparait le nom de la contrainte) !!!

Mais je te confirme que c'est un bug….

Ben quoi que ce soit ça confirme bien que je ne pouvais pas faire autrement que passer par pg_constraint. Note bien que ça ne me fait pas plaisir car j'ai bien conscience que je fais appel à des outils internes Postgres et donc à chaque nouvelle version de Postgres je devrai vérifier si mon programme fonctionne toujours (et heureusement que je ne gère pas la compatibilité descendante !!!) mais au-moins je suis heureux de voir que je ne pouvais pas faire autrement.

Ceci dit, si c'est vraiment un bug ça veut dire qu'un jour ou l'autre il sera corrigé. Ca signifie alors qu'à ce moment là on pourra utiliser les tables de référence... mais ça signifie aussi qu'on ne pourra plus créer de contraintes de même nom dans deux tables. Ce jour là, mes bdd (et probablement un gros paquet d'autres dans le monde) auront un gros souci... ...

[SQLpro]

Je veux bien le croire (tu dois connaitre la norme mieux que moi). Mais c'est alors un bug de conception et non un bug de "oups j'ai mal écrit mon code"...

Extrait de la norme SQL :
La dernière ligne précise bien que la clef primaire de la table d'INFORMATION_SCHEMA.TABLE_CONSTRAINTS possède la clef primaire suivante :

• CONSTRAINT_CATALOG : la base
• CONSTRAINT_SCHEMA : le schéma SQL
• CONSTRAINT_NAME : le nom de la contrainte

A +

PS : j'ai posté dans la liste des bugs PG :

***

Author : SQLpro
Title : Primary key error in INFORMATION_SCHEMA views
on 2018-05-23 22:12


Hi everyone,

In the SQL standard, the INFORMATION_SCHEMA.TABLE_CONSTRAINTS has a primary key compound of:
- CONSTRAINT_CATALOG: the database name
- CONSTRAINT_SCHEMA: the constraint's SQL schema
- CONSTRAINT_NAME; the name of the constraint

This part of the standard ISO/IEC 9075-11:2003
Information technology -- Database languages -- SQL -- Part 11: Information and Definition Schemas (SQL/Schemata) is show bellow:



In PostgreSQL VERSION() --> 10.3 version 64 bits on Windows (and probably in oldest versions) this PRIMARY KEY does not exists.
The Following script demonstrate the bug:

CREATE SCHEMA s;
CREATE TABLE s.foo (C INT);
ALTER TABLE s.foo ADD CONSTRAINT k CHECK(C>0);
CREATE TABLE s.foo2 (C INT);
ALTER TABLE s.foo2 ADD CONSTRAINT k CHECK(C>=1);

This last SQL command should fail because the database name, the schema name and the constraint name is the same for those two constraints, but did'nt !

This is confirm by the query:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 's';

Such a bug introduce major confusion in scripting or using the metadata.

Thanks for correcting this bug in order to make exploitable the information views of schemas that are stable in time because going through the systems views PG _... is subject to caution and has no guarantee of stability in time!

Thanks

***

[SQLpro]

Pour info, Tom Lane n'a pas envie de corriger cela...

http://www.postgresql-archive.org/Pr...td6022177.html

A +

[Sve@r]

Pour info, Tom Lane n'a pas envie de corriger cela...

because it would break applications that work today.

Mouais, c'est un peu ce que je pensais quand j'ai dit "Ce jour là, mes bdd (et probablement un gros paquet d'autres dans le monde) auront un gros souci..."

but Postgres does not require check constraints to be named uniquely across the whole schema, only per-table

Là on pourrait effectivement se poser la question primordiale: pourquoi ont-ils décidé de ne pas respecter cette norme SQL (parce que ça ça doit dater de la v0.1) mais bon...
Pour info (je viens de demander à un dev Oracle de chez-nous), Oracle respecte cette norme. Moi qui était si fier de voir que Postgres tenait la comparaison face à ce géant...

Merci d'avoir essayé. J'ai apprécié ta façon polie mais concise et claire de présenter le truc (même si j'ai senti une certaine tension dans la suite que tu as donnée).
De mon côté je fais un effort pour passer au maximum par "information_schema" chaque fois que je le peux (je viens ce matin de modifier une requête qui récupérait les clefs primaires dans pg_sais_plus_quoi en la remplaçant par "table_constraints" puis "key_column_usage")...