renvoyer des données hiérarchisées

[charles31]

Bonsoir,

je suis nouveau dans le monde PostgreSQL, ayant plutôt l'habitude travailler sur MSSQL. Mais je sens que Postrgre pourrait bien devenir mon SGBD préféré. Je trouve d'ailleurs l'interface pgAdmin très agréable.

Avant de m'engager plus avant dans la construction d'une base de production sous Postgre, j'ai effectué quelques tests qui me paraissent concluants. Toutefois, je bloque sur un point fondamental par rapport à mes habitudes de travail et je n'ai pas encore trouvé d'infos pertinentes à ce sujet. Voici ma problématique : pour diverses raisons qui sont plus ou moins immuables, je souhaite pouvoir écrire des procédures stockées retournant des données hiérarchisées. Par exemple, un enregistrement issu d'une table T1 suivi de n1 enregistrements issus d'une table T2, puis l'enregistrement suivant de la table T1 suivi de n2 enregistrement de la table T2, etc. Tout cela, bien évidemment, sans limitation du nombre de niveaux (comprendre un nombre illimité de tables).

A ce jour, sous MSSQL, je renvoie tout simplement plusieurs jeux d'enregistrements (parfois un jeu est composé d'une ligne unique). Utilisant un Reader spécifique .NET, je parcours l'ensemble de ces jeux de résultats et alloue des objets dynamiquement en fonction des types et noms de colonnes rendus par le Reader.

D'après ce que j'ai pu comprendre, les fonctions PLPGSQL peuvent renvoyer des "setof records", mais je l'ai plutôt analysé comme un alias de table temporaire dont la lecture ne peut d'ailleurs se faire qu'en fournissant les noms et types des colonnes rendues.

Y a t-il un moyen de rendre ce résultat de façon dynamique ? est-il possible de renvoyer un ensemble de données hiérarchisées qu'un langage objet (PHP ?) pourrait recueillir en allouant dynamiquement des objets correspondants aux enregistrements retournés ?

Merci de m'aider à faire le pas vers Postgre...

[CinePhil]

J'ai l'impression que ton besoin correspond en fait par exemple à une liste d'articles classés par catégorie :
catégorie1
-- article 1
-- article 2
categorie2
-- article 3
catégorie4
catégorie5
-- article 4
...

Si c'est ça, pas besoin de procédure compliquée.

Bref, donne plus de détails sur ton besoin.

[charles31]

Voici un exemple détaillé :
Soient les tables groupes et individu qui suivent :

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
CREATE TABLE groupes
(
  id bigserial NOT NULL,
  libelle character varying(50),
  CONSTRAINT id PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
 
INSERT INTO  groupes (id,libelle) VALUES(1,'groupe1');
INSERT INTO  groupes (id,libelle) VALUES(2,'groupe2');
 
CREATE TABLE individu
(
  id bigint NOT NULL DEFAULT NEXTVAL('"maTable_id_seq"'::regclass),
  libelle character varying(50),
  "idGrp" bigint,
  CONSTRAINT "maTable_pkey" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
 
INSERT INTO  individu (id,libelle,"idGrp") VALUES(1,'a',1);
INSERT INTO  individu (id,libelle,"idGrp") VALUES(2,'b',1);
INSERT INTO  individu (id,libelle,"idGrp") VALUES(3,'c',2);
INSERT INTO  individu (id,libelle,"idGrp") VALUES(4,'d',2);

Je souhaite écrire une procédure me renvoyant les données de la façon suivante :

1,'groupe1'
1,'a',1
2,'b',1
2,'groupe2'
3,'c',2
4,'d',2

Par exemple, sous MSSQL, j'écris qqch comme :

Code :

1
2
SELECT id,libelle FROM groupes WHERE id=@id
SELECT id,libelle,"idGrp" FROM individu WHERE "idGrp" = @id

je place ce bloc de requête dans un boucle avec un curseur du type :
@myCursor CURSOR FOR SELECT id FROM groupes

J'ai une classe qui récupère ensuite ces jeux d'enregistrement successifs via un Reader et valorise des objets hiérarchisés en conséquence.

Merci pour votre aide

[estofilo]

Envoyé par charles31

D'après ce que j'ai pu comprendre, les fonctions PLPGSQL peuvent renvoyer des "setof records", mais je l'ai plutôt analysé comme un alias de table temporaire dont la lecture ne peut d'ailleurs se faire qu'en fournissant les noms et types des colonnes rendues.

Y a t-il un moyen de rendre ce résultat de façon dynamique ? est-il possible de renvoyer un ensemble de données hiérarchisées qu'un langage objet (PHP ?) pourrait recueillir en allouant dynamiquement des objets correspondants aux enregistrements retournés ?

Non ce n'est pas possible en pratique avec postgresql. Ca fait partie de la fonctionnalité de procédure stockée qui stagne dans la TODO list.

[CinePhil]

Donc ça correspond à ce que je pensais.
Et comme ce n'est pas le boulot du SGBD de présenter les données mais celui du logiciel qui interroge la BDD, une simple requête avec jointure entre les deux tables te donneras ce que tu veux.

Code :

1
2
3
4
5
SELECT g.id AS id_groupe, g.libelle AS libelle_groupe,
  i.id AS id_individu, i.libelle AS libelle_individu
FROM groupes g
INNER JOIN individu i ON i.idGrp = g.id
ORDER BY g.id, i.id

Au passage :
1) bigint pour des id de groupe et d'individu... tu comptes enregistrer la population de la galaxie ?

2) En principe on ne nomme pas les tables au pluriel.

3) Éviter de mélanger les minuscules et majuscules avec Postgresql sous peine de s'emmerder avec les guillemets.

[charles31]

1) bigint, pour moi, c'est une habitude pour toutes les tables. Cela dit, groupes et individu n'étaient que des exemples,et oui j'ai des tables suffisamment importantes pour le justifier.

2) ok pour le pluriel, je ne savais pas et suivrai le conseil

3) de même pour les majuscules, je comprends mieux maintenant l'apparition des guillemets involontaires... Cela veut dire qu'il faut écrire le nom de toutes les tables et champs en minuscule ?

Pour la remarque sur les requêtes en général, je ne suis pas du tout d'accord !
Evidemment, dans ce cas extrêmement basique, une jointure de base fait l'affaire et en associant un schéma, un parser pourra isoler les blocs par niveau dans le jeu produit par la requête.
Mais l'organisation des tables et les relations existantes ne permettent malheureusement pas toujours de produire une requête unique comportant l'ensemble des données désirées. D'où l'intérêt notamment d'utiliser la procédure stockée. En terme de maintenance aussi tout simplement, sans considération de performance, il peut être très judicieux de décomposer la requête en blocs de requêtes plus simples pour optimiser les opérations de modifications et fortement diminuer la batterie de tests à effectuer pour les valider.
De plus, lorsque l'on effectue des boucles côté logiciel pour récupérer les résultats requis, il y a 2 problèmes importants :
1/ les perfos sont fortement dégradées à cause de la relation client-serveur qui ralentit considérablement les accès multiples.
2/ le débugage est beaucoup plus compliqué et coûteux (cela est vrai pour la maintenance également). Alors qu'il est tellement facile d'exécuter la requête SQL d'une procédure stockée une fois pour toute dans le SGBD...

Bref, dommage que postgre n'intègre pas cette fonctionnalité qui est tellement pratique.

[charles31]

Envoyé par estofilo

Non ce n'est pas possible en pratique avec postgresql. Ca fait partie de la fonctionnalité de procédure stockée qui stagne dans la TODO list.

Ok, tant pis. Merci pour cette précision, je vais devoir repenser mon choix ou mes méthodes...

[CinePhil]

Envoyé par charles31

1) bigint, pour moi, c'est une habitude pour toutes les tables. Cela dit, groupes et individu n'étaient que des exemples,et oui j'ai des tables suffisamment importantes pour le justifier.

Envoyé par Postgresql

bigint 8 octets grands entiers de -9223372036854775808 à 9 223 372 036 854 775 807

9 milliards de milliards !C'est bien ce que je disais, tu comptes la population de la galaxie !

3) de même pour les majuscules, je comprends mieux maintenant l'apparition des guillemets involontaires... Cela veut dire qu'il faut écrire le nom de toutes les tables et champs en minuscule ?

Ce n'est pas obligatoire mais conseillé pour ne pas s'emmerder avec les guillemets. Postgresql gère mal les noms avec des majuscules.

Mais l'organisation des tables et les relations existantes ne permettent malheureusement pas toujours de produire une requête unique comportant l'ensemble des données désirées.

Mais comme tu ne donnes pas le détail de la composition réelle de ta BDD, on ne peut pas t'aider efficacement.

D'où l'intérêt notamment d'utiliser la procédure stockée. En terme de maintenance aussi tout simplement, sans considération de performance, il peut être très judicieux de décomposer la requête en blocs de requêtes plus simples pour optimiser les opérations de modifications et fortement diminuer la batterie de tests à effectuer pour les valider.

Je n'ai pas assez d'expérience pour juger de cela.

De plus, lorsque l'on effectue des boucles côté logiciel pour récupérer les résultats requis, il y a 2 problèmes importants :
1/ les perfos sont fortement dégradées à cause de la relation client-serveur qui ralentit considérablement les accès multiples.

Ma suggestion était de ramener tout le résultat d'une seule requête et de parcourir le tableau de résultats dans le logiciel ; que du classique qui est pratiqué par des centaines de milliers de sites internet ou autres applications chaque seconde.
Bien sûr, si le résultat de la requête ramène 9 milliards de milliards de lignes, ça peut effectivement poser problème...

2/ le débugage est beaucoup plus compliqué et coûteux (cela est vrai pour la maintenance également).

Je ne vois pas pourquoi ! 1 seule requête et un bout de programme qui traite le résultat au lieu de d'une procédure plus complexe qu'une requête et un autre bout de programme pour traiter le résultat...

Alors qu'il est tellement facile d'exécuter la requête SQL d'une procédure stockée une fois pour toute dans le SGBD...

Là sur le principe, je suis d'accord... comme il est tout aussi facile d'écrire une requête dans un programme et qui sera exécutée chaque fois que le programme sera lancé.

Bref, tu ne nous dis pas tout sur ton besoin réel qui puisse justifier ce choix technologique d'apparence complexe par rapport au besoin que tu as jusqu'ici exprimé.
Et là je ne peux pas t'aider davantage sans en savoir plus.

[estofilo]

Envoyé par charles31

3) de même pour les majuscules, je comprends mieux maintenant l'apparition des guillemets involontaires... Cela veut dire qu'il faut écrire le nom de toutes les tables et champs en minuscule ?

Non le fonctionnement est standard. C'est-à-dire que:
Si on fait CREATE TABLE Abcd(...) on pourra s'y référer en abcd ou ABCD ou n'importe quelle variation de casse.
Mais si on fait CREATE TABLE "Abcd"(...) il faudra s'y référer en "Abcd" rigoureusement, guillemets inclus.

Il se trouve que le formulaire de création de table de PGAdmin met des guillemets autour des noms de table sans demander son avis à l'utilisateur, c'est ce qui apparemment pose problème. Mais c'est un choix de PGAdmin, pas de postgresql.

[charles31]

- Attention, integer = 2^32 soit un max de un peu plus de 2 millards si on commence à 0. Et là, sur la durée, pour une table de grande taille, c'est l'overflow assuré !

- pour la nomenclature, j'en déduis que l'utilisation de minuscules est préconisé. On utilise les "_" alors ? ex : table_groupe, table_utilisateur, etc ?
de même pour les champs ? ex : id_grp ?

- Pour le debug et la maintenance, je parle en effet de requêtes en chaîne avec plusieurs curseurs imbriqués, désolé de ne pas avoir été plus précis dans l'expression de mon besoin


J'envisage une solution passant par un cast de chaque record en varchar en utilisant des séparateurs entre les champs. Je pourrai alors renvoyer un jeu unique du type (id, varchar) correspondant à chaque enregistrement désiré, utilisable avec le return standard setof records.
Mon parser fera l'affaire ensuite moyennant quelques modifs.

En gros, voici mon idée :
- procédure "cast"('requête SQL') qui renvoie un setof de varchar. Chaque varchar étant par exemple de la forme : 'id1;libelle1;id_grp1' (séparateur ';')
Je pense d'ailleurs à un premier varchar comprenant le schéma avant d'enchaîner sur les enregistrements sélectionnés.

- procédure stockée principale qui renvoie un setof de (id, varchar)
comprenant les boucles des requêtes imbriquées avec, pour chaque requête, un appel de la procédure "cast" et un insert du setof obtenu dans une table temporaire de type (id, varchar). Cette table temporaire étant renvoyée par la procédure.

Ce pattern permet : une décomposition en différentes requêtes pour une maintenance et un débug plus facile au sein de chaque donnée hiérarchisée. Une logique de boucle au sein de la procédure stockée via des curseurs optimisés (donc + efficace).
L'inconvénient majeur, à mon goût, est le passage des requêtes en paramètre de la procédure "cast" (donc de type varchar). Ce qui impose une construction des requêtes par concaténation de chaînes (pour permettre la prise en compte des variables : notamment les curseurs impliqués) : j'aime bien éviter normalement, c'est lourd...

Avez-vous des suggestions, des remarques ?

ps : si mon explication est incompréhensible, n'hésitez pas à m'en faire part, je reprendrai...

[charles31]

Envoyé par estofilo

Non le fonctionnement est standard. C'est-à-dire que:
Si on fait CREATE TABLE Abcd(...) on pourra s'y référer en abcd ou ABCD ou n'importe quelle variation de casse.
Mais si on fait CREATE TABLE "Abcd"(...) il faudra s'y référer en "Abcd" rigoureusement, guillemets inclus.

Il se trouve que le formulaire de création de table de PGAdmin met des guillemets autour des noms de table sans demander son avis à l'utilisateur, c'est ce qui apparemment pose problème. Mais c'est un choix de PGAdmin, pas de postgresql.

ok, merci pour cette précision. J'imagine qu'il vaut mieux éviter alors.

[SQLpro]

Pourquoi ne pas tout simplement utiliser des vues ?
En principe toutes les applications ne devraient attaquer que des vues, pas des tables. Cela s'appelle le modèle externe...

A +

[charles31]

Il est vrai que je n'utilise pas assez les vues...
Mais il me semble que dans ce cas précis, le problème est le même avec une vue dans le sens où celle-ci est une table (virtuelle) et ne peut donc pas répondre à mon besoin.
En fait, je ne voies pas comment une vue pourrait renvoyer un jeu d'enregistrements de type différent... Mais je me trompe peut-être...

[SQLpro]

Une vue peut remettre à plat sous forme d'une seule table les données.

Exemple T1(A, C, D), T(2(A, B, D)
Vue V(A, B, C, D)

Certes il y aura de la redondance, mais ce sera moins problématique que de faire ce que vous faites avec des curseurs qui sont pas optimisés !

A +

[charles31]

Oui en effet, si la mise à plat est utilisée, on obtient alors un type unique de record et le tour est joué. C'était déjà le cas avec la procédure stockée mais la vue est sans doute plus adaptée pour obtenir ce résultat. Je vais donc m'orienter vers ce choix pour ces cas-là.

Mon parser étant capable de reconstruire les objets hiérarchisés depuis un jeu mis à plat, le problème est résolu... quand je peut mettre à plat.

Il me reste à traiter les cas des hiérarchies sur de nombreuses générations (trop lourds pour de la mise à plat, car trop de redondance) et le cas des hiérarchies avec des types d'enfants multiples qui, à mon avis, sont ingérables de cette manière....

Mais là aussi, la vue peut m'aider à résoudre le problème en décomposant les blocs au sein des boucles en vues distinctes qui seront ensuite castées de la façon décrite plus haut pour que l'ensemble renvoie un jeu de type unique.

Je vais maintenant faire qq tests et vous ferai part de mes résultats.
Suis toujours ouvert aux autres suggestions...

A bientôt