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
{
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
{
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
{
?>
<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.
