dimanche 24 mai 2009

Décrire les objets dans une Base de données Postgresql/PostGIS








Cette article montre comment décrire et commenter une base de données et les objets contenus dans cette base, et consulter la description des tables et des variables via un navigateur web.



1. Décrire et consulter les tables de la base de données Postgresql


Ces derniers temps ,un de mes travaux a été de transférer un grande quantité de données sous POSTGRESQL/POSTGIS. La base de données crée est divisée en schéma. Chaque schéma correspond à un thème (foncier, environnement…). Pour consulter le contenu de la base de données, il est possible d’utiliser la commande \d quand nous sommes connectés à la base.


Exemple 1 :


ma_base=# \d


List of relations

Schema | Name | Type | Owner

-------- +---------------------+----------+----------

public | accident | table | postgres

public | accident_gid_seq | sequence | postgres

envi | pnr | table | postgres

envi | pnr_ggid_seq | sequence | postgres


Essayez la commande \d+

Et \d+ nom_de_la_table


La colonne « description » s’ajoute.

Avec la commande « comment on », il est possible de décrire chaque objet (schema, table, variables…).


Exemple 2 :

ma_base=# comment on schema envi is 'données environnemmentales';

ma_base=# comment on table envi.pnr is 'parc naturel regional ';

ma_base=# comment on column envi.pnr.nom is 'nom du PNR ';


Refaite \d+ et \d+ nom_de_la_table pour visualiser la description des tables et des variables.

A savoir, la description des objets est contenue dans la table pg_catalog.pg_description



2. Afficher les descriptions dans une table


L’idée est d’afficher le contenu d’un \d+ dans une table. En d’autres termes, je cherche à mettre dans une nouvelle table « descrip_db » la liste des tables contenues dans ma base de données ainsi que la description de chaque table. De plus, je souhaite une table « descrip_matable » avec les variables, le type de données (caractère, numérique…) et la description des variables contenues dans « ma_table » Après quelques recherches, voici le code à écrire :


Exemple 3 : voir la description des tables d’un schema

CREATE TABLE descrip_db as

SELECT distinct

info.table_schema,info.table_name, pde.description

from

(select c.*, pc.*, pc.oid as oid

from pg_catalog.pg_class as pc, information_schema.columns as c

where pc.relname=c.table_name and c.table_schema != 'information_schema' and c.table_schema != 'pg_catalog') as info

LEFT OUTER JOIN

(SELECT pc.*, PD.*

FROM pg_catalog.pg_description PD right outer join pg_catalog.pg_class PC

on PD.objoid = PC.oid AND PD.objsubid = 0 ) as pde

on info.oid = pde.objoid

where

info.table_schema = 'nom_schema';


Dans le dernier where, choisissez le nom du schema (ou enlevez la condition where pour visualiser la description des tables de tous les schemas.


Exemple 3 : voir la description des variables d’une table

CREATE TABLE descrip_matable as

SELECT info.column_name,info.data_type, pde.description from

(select c.table_name, pc.relname, c.data_type, c.column_name, c.ordinal_position, pc.oid from pg_catalog.pg_class as pc, information_schema.columns as c where pc.relname=c.table_name and c.table_schema ='mon_schema' AND pc.relname = 'ma_table') as info

LEFT OUTER JOIN (select pd.objsubid, pd.objoid, pd.description from pg_catalog.pg_class as pc, pg_catalog.pg_description as pd where (pc.oid=pd.objoid) and pc.relname = 'ma_table') as pde on info.ordinal_position=pde.objsubid

ORDER BY info.ordinal_position ;


Remplacez mon_schema et ma_table par le nom du schema et de la table dont vous souhaitez voir la description des variables (= colonnes)



3. Afficher le contenu du select via un navigateur web


L’idée est de créer des pages web qui affichent les descriptions des schemas, des tables et des variables. Pour cela, on peut créer des pages PHP. Il vous faut d’abord configurer un serveur Apache avec PHP. Ce serveur peut par exemple être mis en place facilement via EasyPhp (à téléchager gratuitement). Si vous n’êtes pas à l’aise avec PHP, je vous incite à regarder du côté du Site du Zero.


Je vais vous lister les étapes pour créer les pages web qui m’ont servi à mettre en plalce un petit outil de recherche pour mes collègues (et que j’utilise moi-même tous les jours !)

On va créer plusieurs pages PHP avec des menus déroulants pour améliorer la navigation. Mettre l’ensemble des pages dans un même dossier, nommé « LABEL »


1. Code Création de labelform.php :

Formulaire permettant deux choses :

1.renseigner le schema via un menu déroulant pour visualiser le contenu d’un schema. Le formulaire vous enverra vers labelform2.php

2. un lien envoyant sur psqlschema.php permettant de voir la description des schemas et des tables


(pour mieux visualiser les pages PHP, copier le code dans Notepad++ (éditeur de texte gratuit) et choisissez le langage PHP.


Début Code :


<html><head>

</head>

<body>

Etape 1 : choisir un schema

<form method="post" action="labelform2.php">

<?php

$connection = pg_connect("dbname=ma_base user=postgres password=ppppp port=5432 host=localhost");

if (!$connection) {

echo"Connection a échouée";

}

echo "<br />";

$result_sch = pg_query($connection, "

SELECT distinct c.table_schema

from information_schema.columns as c

where c.table_schema != 'information_schema' and c.table_schema != 'pg_catalog'

order by c.table_schema; ");

echo "<select name=\"schema\">";

while ($donnees = pg_fetch_array($result_sch) ) //si marche

{

echo " <OPTION Value=\"".$donnees[0]."\">".$donnees[0]."</option> ";

}

echo "</select>";

?>

<input type="submit" value="Valider" />

</form>

<span style=" color: rgb(67, 48, 51);"><br><big style="color: rgb(183, 79, 89);"><span style="font-weight: bold;">Pour

voir la description des tables et leurs schemas :</span></big></span> <a style="font-family: Calibri;" href="psqlschema.php">lien</a><br>

<br>

<br>

<span style=" color: rgb(67, 48, 51);"><br></span><a href="psqlschema.php"></a>

</body>

</html>

Fin code

Copie d’écran :




Dans $connection, paramétrez votre connexion à votre base de données.


Remarquez les fonctions php qui permettent une interaction avec votre base de données postgresql : pg_connect et pg_query.



2.Code Création de labelform2.php :

Cette page ressemble a labelform.php et Envoie vers psql2.php lors du choix de la table



Début Code :

<html><head>

</head>

<body>

Etape 2 : choisir une table

<form method="post" action="psql2.php">

<?php

$connection = pg_connect("dbname=ma_base user=postgres password=ppppp port=5432 host=localhost");

if (!$connection) {

echo"Connection a échouée";

}

echo "<br />";

$schema = $_POST['schema'];

$result = pg_query($connection, "

SELECT distinct info.table_schema,info.table_name

from

(select c.*, pc.*, pc.oid as oid

from pg_catalog.pg_class as pc, information_schema.columns as c

where pc.relname=c.table_name and c.table_schema != 'information_schema' and c.table_schema != 'pg_catalog') as info

LEFT OUTER JOIN

(SELECT pc.*, PD.*

FROM pg_catalog.pg_description PD right outer join pg_catalog.pg_class PC

on PD.objoid = PC.oid AND PD.objsubid = 0 ) as pde

on info.oid = pde.objoid

where info.table_schema ='".$schema."'

ORDER BY info.table_schema,info.table_name; ");

?>

<select name="schema">

<option value="<?php echo $schema;?>"><?php echo $schema;?></option>

<?php

echo "<select name=\"table\">";

while ($donnees1 = pg_fetch_array($result) ) //si marche pas voir pg_copy

{

echo " <OPTION Value=\"".$donnees1[1]."\">".$donnees1[0]." . ".$donnees1[1]."</option> ";

}

echo "</select>";echo "\n ";

?>

<input type="submit" value="Valider" />

</form>

<a style="font-family: Calibri;" href="labelform.php"> retour étape 1</a>

<br>

<br>

<br>

<span style=" color: rgb(67, 48, 51);"><br><big style="color: rgb(183, 79, 89);"><span style="font-weight: bold;">Pour

voir la description des tables et leurs schemas :</span></big></span> <a style="font-family: Calibri;" href="psqlschema.php">lien</a><br>

<br>

<br>

<br>

<br>

<span style=" color: rgb(67, 48, 51);"><br></span><a href="psqlschema.php"></a>

</body></html>


Fin code


Copie d’écran :



3. Code Création de psql2.php :

Affiche la description des variables de la table choisie dans labelform2.php

J’y ai rajouté une colonne qui mon un exemple de la variable (ex : pour la variable ‘code_insee’, la colonne Exemple mettrait 84007.


Début Code :

<html><head>

</head><body>

<a href="labelform.php">retour</a></big>

<br><br>

<?php echo "\n \n";

$table = $_POST['table'];

$schema = $_POST['schema']; echo "Vous souhaitez visualiser les champs de la table : \n";

echo "<span class=\"normal\"><font color=\"red\" ><b>".$table."</b></font></span>"; echo "<br />";

echo "Qui se trouve dans le schema : \n";

echo "<span class=\"normal\"><font color=\"red\" ><b>".$schema."</b></font></span>"; echo "\n \n";

echo "<br />";

$connection = pg_connect("dbname=ma_base user=postgres password=ppppp port=5432 host=localhost");

if (!$connection) { echo"Connection a échouée"; } echo "<br />";

$result = pg_query($connection,

"SELECT info.column_name,info.data_type, pde.description from

(select c.table_name, pc.relname, c.data_type, c.column_name, c.ordinal_position, pc.oid from pg_catalog.pg_class as pc, information_schema.columns as c where pc.relname=c.table_name and c.table_schema ='".$schema."' AND pc.relname = '".$table."') as info

LEFT OUTER JOIN (select pd.objsubid, pd.objoid, pd.description from pg_catalog.pg_class as pc, pg_catalog.pg_description as pd where (pc.oid=pd.objoid) and pc.relname = '".$table."') as pde on info.ordinal_position=pde.objsubid

ORDER BY info.ordinal_position; ");

$select = pg_query($connection, "SELECT * from ".$schema.".".$table." limit 1; ");

$descrip_q = pg_query($connection, "

SELECT distinct info.table_schema,info.table_name, pde.description

from

(select c.*, pc.*, pc.oid as oid

from pg_catalog.pg_class as pc, information_schema.columns as c

where pc.relname=c.table_name and c.table_schema != 'information_schema' and c.table_schema != 'pg_catalog') as info

LEFT OUTER JOIN

(SELECT pc.*, PD.*

FROM pg_catalog.pg_description PD right outer join pg_catalog.pg_class PC

on PD.objoid = PC.oid AND PD.objsubid = 0 ) as pde

on info.oid = pde.objoid

where

info.table_schema = '".$schema."' and info.table_name = '".$table."' ; ");

$description = pg_fetch_result($descrip_q, 0, 2);

///eventuellement rajouter une L pour label //$result = pg_query($connection, "SELECT * FROM ".$table." ;");

if (!$result) { echo "Une erreur s'est produite.: peut-être que la couche n'existe pas?\n"; }

echo "Description : \n";

echo "<span class=\"normal\"><font color=\"green\" ><b>".$description."</b></font></span>"; echo "\n \n"; echo "<br /> <br /><br />";

?>

<table style="text-align: left; background-color: rgb(242, 220, 105); width: 1300px; height: 33px;" border="1" cellpadding="2" cellspacing="2">

<tbody>

<tr>

<td style="width: 350px; font-weight: bold;"><big>Variable

</big></td>

<td style="width: 150px; font-weight: bold;"><big>Type

</big></td>

<td style="width: 500px; font-weight: bold;"><big>Commentaires

</big></td>

<td style="width: 300px; font-weight: bold;"><big>Exemple

</big></td>

</tr>

</tbody>

</table>

<?php while ($donnees = pg_fetch_array($result)){ ?>

<table style="text-align: left; width: 1300px; height: 33px;" border="1" cellpadding="2" cellspacing="2" >

<tbody>

<tr>

<td style="width: 350px;"><?php echo $donnees[0];echo "<br />" ;?>

</td>

<td style="width: 150px;"><?php echo $donnees[1]; echo "<br />";?>

</td>

<td style="width: 500px;"><?php echo $donnees[2]; echo "<br />";?>

</td>

<td style="width: 300px;"><?php

$num= pg_field_num($select, $donnees[0]);

$exemple = pg_fetch_result($select, 0, $num);

if ($donnees[0] != "the_geom" ){

echo $exemple; echo "<br />";}

if ($donnees[0] = "the_geom" ){

echo "";}

?>

</td>

</tr>

</tbody>

</table>

<?php }

pg_close(); ?> <big style="font-family: Calibri;"><a href="labelform.php">retour</a></big><br>

<br>

</body></html>


Fin code



Copie d’écran :



4. Code Création de psqlschema.php :

Pour visualiser la description des schemas et la description des tables. Un menu déroulant permet de faire les tables qui se situe dans un schema selectionné.



Début code

<html><head></head><body>

<?php $connection = pg_connect("dbname=ma_base user=postgres password=ppppp port=5432 host=localhost");

if (!$connection) {

echo"Connection a échouée";

}

echo "<br />";

//$result = pg_query($connection, "SELECT * FROM ".$schema.".".$table." ;");

$result = pg_query($connection, "

SELECT distinct info.table_schema,info.table_name, pde.description

from

(select c.*, pc.*, pc.oid as oid

from pg_catalog.pg_class as pc, information_schema.columns as c

where pc.relname=c.table_name and c.table_schema != 'information_schema' and c.table_schema != 'pg_catalog') as info

LEFT OUTER JOIN

(SELECT pc.*, PD.*

FROM pg_catalog.pg_description PD right outer join pg_catalog.pg_class PC

on PD.objoid = PC.oid AND PD.objsubid = 0 ) as pde

on info.oid = pde.objoid

ORDER BY info.table_schema,info.table_name; ");

$result_sch = pg_query($connection, "

select pp.nspname, pde.description

from pg_catalog.pg_namespace as pp LEFT OUTER JOIN pg_catalog.pg_description as pde

on pp.oid = pde.objoid

where pp.nspname not in ('pg_toast','pg_temp_1','pg_catalog','information_schema')

order by pp.nspname; ");

if (!$result) {

echo "Une erreur s'est produite: peut-être que la couche n'existe pas?\n";

}

if ($result_sch) {

echo "<br /> <B> Les tables sont réparties en plusieurs schemas : </B> <br /><br />" ;

}

?>

<table style="text-align: left; background-color: rgb(253, 180, 120); width: 950px; height: 32px;" border="1" cellpadding="2" cellspacing="2">

<tbody>

<tr>

<td style="width: 150px; font-weight: bold;">Schemas </td>

<td style="width: 800px; font-weight: bold;">Description </td>

</tr>

</tbody>

</table>

<?php while ($shema = pg_fetch_array($result_sch) ) //si marche pas voir pg_copy

{

?>

<table style="text-align: left; width: 950px; height: 32px;" border="1" cellpadding="2" cellspacing="2">

<tbody>

<tr>

<td style="width: 150px;"><?php echo $shema[0]; ?> </td>

<td style="width: 800px;"><?php echo $shema[1]; ?> </td>

</tr>

</tbody>

</table>

<?php

}

echo "<br /> <br /> <br /> ";

///eventuellement rajouter une L pour label

//$result = pg_query($connection, "SELECT * FROM ".$table." ;");

if (!$result) {

echo "Une erreur s'est produite: peut-être que la couche n'existe pas?\n";

}

if ($result) {

echo " <B>Liste des schemas et tables disponibles dans la base de données 'ecodev': </B> <br /><br />";

}

?>

<form method="post" action="psqlschema2.php">

<?php

$result_sch1 = pg_query($connection, "

select pp.nspname, pde.description

from pg_catalog.pg_namespace as pp LEFT OUTER JOIN pg_catalog.pg_description as pde

on pp.oid = pde.objoid

where pp.nspname not in ('pg_toast','pg_temp_1','pg_catalog','information_schema')

order by pp.nspname; ");

echo "Pour selectionner seulement un schema : ";

echo "<select name=\"schema\">";

while ($shem = pg_fetch_array($result_sch1) )

{

echo " <OPTION Value=\"".$shem[0]."\">".$shem[0]."</option> ";

}

echo "</select>";

?>

<input type="submit" value="Valider" />

</form>

<table style="text-align: left; background-color: rgb(253, 210, 97); width: 1100px; height: 32px;" border="1" cellpadding="2" cellspacing="2">

<tbody>

<tr>

<td style="width: 100px; font-weight: bold;">Schemas </td>

<td style="width: 250px; font-weight: bold;">Tables </td>

<td style="width: 750px; font-weight: bold;">Description </td>

</tr>

</tbody>

</table>

<?php while ($donnees = pg_fetch_array($result) )

{

?>

<table style="text-align: left; width: 1100px; height: 32px;" border="1" cellpadding="2" cellspacing="2">

<tbody>

<tr>

<td style="width: 100px;"><?php echo $donnees[0];echo "<br />" ?> </td>

<td style="width: 250px;"><?php echo $donnees[1]; echo "<br />"?> </td>

<td style="width: 750px;"><?php echo $donnees[2]; echo "<br />"?> </td>

</tr>

</tbody>

</table>

<?php }

pg_close();

?>

<br><br>

<a style="font-family: Calibri;" href="labelform.php"><big style="font-weight: bold;"><big>retour</big></big></a><span style="font-family: Calibri;">

</span><big style="font-weight: bold;"><big><br>

</big></big><br>

</body></html>



Fin code

Copie d’écran :




5. Code Création de psqlschema2.php :

Pour visualiser la description des schemas et la description des tables. Un menu déroulant permet de voir la description des tables qui se situent dans un schema selectionné.


Début Code:

<html><head></head><body>

<?php $connection = pg_connect("dbname=ma_base user=postgres password=pascal port=5432 host=localhost");

if (!$connection) {

echo"Connection a échouée";

}

echo "<br />";

//$result = pg_query($connection, "SELECT * FROM ".$schema.".".$table." ;");

$schema = $_POST['schema'];

$result = pg_query($connection, "

SELECT distinct info.table_schema,info.table_name, pde.description

from

(select c.*, pc.*, pc.oid as oid

from pg_catalog.pg_class as pc, information_schema.columns as c

where pc.relname=c.table_name and c.table_schema != 'information_schema' and c.table_schema != 'pg_catalog') as info

LEFT OUTER JOIN

(SELECT pc.*, PD.*

FROM pg_catalog.pg_description PD right outer join pg_catalog.pg_class PC

on PD.objoid = PC.oid AND PD.objsubid = 0 ) as pde

on info.oid = pde.objoid

where info.table_schema ='".$schema."'

ORDER BY info.table_schema,info.table_name; ");

$result_sch = pg_query($connection, "

select pp.nspname, pde.description

from pg_catalog.pg_namespace as pp LEFT OUTER JOIN pg_catalog.pg_description as pde

on pp.oid = pde.objoid

where pp.nspname not in ('pg_toast','pg_temp_1','pg_catalog','information_schema')

order by pp.nspname; ");

if (!$result) {

echo "Une erreur s'est produite: peut-être que la couche n'existe pas?\n";

}

if ($result_sch) {

echo "<br /> <B> Les tables sont réparties en plusieurs schemas : </B> <br /><br />" ;

}

?>

<table style="text-align: left; background-color: rgb(253, 210, 97); width: 1100px; height: 32px;" border="1" cellpadding="2" cellspacing="2">

<tbody>

<tr>

<td style="width: 100px; font-weight: bold;">Schemas </td>

<td style="width: 250px; font-weight: bold;">Tables </td>

<td style="width: 750px; font-weight: bold;">Description </td>

</tr>

</tbody>

</table>

<?php while ($donnees = pg_fetch_array($result) )

{

?>

<table style="text-align: left; width: 1100px; height: 32px;" border="1" cellpadding="2" cellspacing="2">

<tbody>

<tr>

<td style="width: 100px;"><?php echo $donnees[0];echo "<br />" ?> </td>

<td style="width: 250px;"><?php echo $donnees[1]; echo "<br />"?> </td>

<td style="width: 750px;"><?php echo $donnees[2]; echo "<br />"?> </td>

</tr>

</tbody>

</table>

<?php }

pg_close();

?>

<br><br>

<a style="font-family: Calibri;" href="labelform.php"><big style="font-weight: bold;"><big>retour</big></big></a><span style="font-family: Calibri;">

</span><big style="font-weight: bold;"><big><br>

</big></big><br>

</body></html>


Fin Code


Copie d’écran :



Pour visualiser les pages, allez sur votre navigateur web (Mozilla FireFox) et indiquez l’adresse de la première page.

Exemple : http://localhost/label/labelform.php


Désormais, à vous de jouer en recopiant le code et en le modifiant à votre guise. En plaçant ces pages sur un serveur, faites profiter vos collègues qui ont souvent une idée très imprécise des données dont dispose votre organisme.