Category Archives: Bases de données

Analyser des adresses URL avec parse_url dans PostgreSQL

Posted on by
1

Note: La version 1.1 est sortie.

Note: voir la page du projet

Si vous stockez des adresses URL dans votre base de données, il est possible que vous souhaitiez récupérer des données de celles-ci comme le nom de domaine, le path qui correspond à /dossier/fichier.html par exemple, les paramètres envoyés, etc… Pour ça, il fallait auparavant utiliser par exemple une fonction Pl/Sh pour demander à un script PHP tel ou tel champ de l’URL, analysée avec la fonction parse_url de PHP.

Maintenant, il en est tout autrement ! ;-) Basée sur la fonction parse_url de PHP, j’ai codé une simple petite fonction parse_url utilisable à partir de PostgreSQL 8.4. Dans ce module “parse_url”, il y a:

  • Une fonction parse_url (text) qui retourne un record. Elle prend pour argument une adresse URL sous une forme texte et retourne un record nommé “url_record” défini par ("scheme" text, "user" text, "pass" text, "host" text, "port" integer, "path" text, "query" text, "fragment" text)
  • Une fonction parse_url (text, text) qui retourne une valeur texte correspondant au champ nommé dans le second argument. Le champ peut être:
    • scheme: Le schéma de l’adresse URL. (http, https, ftp…)
    • user: Le nom d’utilisateur si fourni
    • pass: Le mot de passe si fourni
    • host: Le nom de domaine
    • port: Le port de connexion si spécifié
    • path: L’adresse du fichier par rapport au nom de domaine
    • query: Les paramètres URL envoyés
    • fragment: Le contenu situé après “#”

Continue reading →

PHP PDO: Récupérer les notes du serveur de bases de données

Posted on by
Reply

Certaines bases de données comme Oracle et PostgreSQL – MySQL le fait partiellement – renvoient des notes (notices en anglais) concernant une requête ALTER, UPDATE ou DELETE. Il est même possible de renvoyer des notes sous PostgreSQL et Oracle en faisant respectivement RAISE NOTICE et DMBS_OUTPUT.PUT_LINE. Ces informations peuvent parfois être retournée depuis les drivers de base de la base de données mais pas avec PDO.

Note: Ces patchs sont créés et testés pour PHP 5.3 uniquement.

Accèder à la page du projet PDO – Notices

Continue reading →

PostgreSQL: Très grands entiers – Very big integers

Posted on by
1

Le type de données bigint ne peut avoir des valeurs que entre -9223372036854775808 et 9223372036854775807 (voir la documentation de PostgreSQL sur les nombres). Seulement, parfois, peut-être très rarement mais parfois, les nombres que vous souhaitez ajouter dans la base de données sont plus grands. Pour ça, il faut utiliser le type numeric.

Si votre plus grand nombre a 40 caractères, vous allez créer une table avec un champ de type numeric(40, 0) :

CREATE TABLE high_numbers (
    mon_grand_nombre NUMERIC(40, 0)
);

D’après la documentaton, The type numeric can store numbers with up to 1000 digits of precision and perform calculations exactly. C’est-à-dire, pour les non-anglophones que le type numeric peut avoir des nombres de plus de 1000 chiffres de précision et faire des calculs avec parfaitement !

Attention, However, the numeric type is very slow compared to the floating-point types – le type numeric est très lent par rapport au type float, à utiliser avec modération donc !

PostgreSQL: Changer le type d’une colonne

Posted on by
4

J’ai récement eu besoin de transformer un champ texte – varchar – en entier – integer. Pour cela, j’ai d’abord transformer toutes les valeurs de cette colonne en entiers sous forme de texte, c’est-à-dire des entiers transtypés vers varchar(50) – value::varchar(50) – puis, j’ai voulu changer le type de colonne :

ALTER TABLE schema.matable ALTER COLUMN colonne TYPE INTEGER;

Mais, erreur…

ERREUR:  la colonne « colonne » ne peut pas être convertie vers le type pg_catalog.int4

Continue reading →

Pl/PgSQL: Parcourir un tableau

Posted on by
2

Pour PostgreSQL >= 9.1

Depuis PostgreSQL 9.1, nous pouvons utiliser l’opérateur FOREACH, comme ceci:

FOREACH v_row IN ARRAY v_monarray
LOOP
        -- La variable "v_row" contient le row de votre tableau.
END LOOP;

Pour PostgreSQL > 8.3 et <= 9.0

Pour parcourir un tableau de données, ça pourrait être très simple en utilisant FOR ... IN ... mais cette synthaxe utilise uniquement des données de type RECORD pour fonctionner. C’est pourquoi, depuis PostgreSQL 8.4, il y a une fonction unnest qui permet de transformer un tableau (array) en un RECORD. Avant, nous allons créer cette fonction.

Dans votre bloc Pl/PgSQL, faites comme ceci:

FOR v_array_data IN SELECT unnest(v_monarray) LOOP
 
        -- Vos instructions...
 
END LOOP;

Données:

  • v_monarray est un tableau de données (exemple: integer[] )
  • v_array_data est une variable contenant la valeur de la donnée du tableau du tour (exemple: integer)
    Note: Doit être du même type que la donnée du tableau

Pour PostgreSQL <= 8.3

Pour ces versions, nous allons créer la fonction unnset (voir article sur le wiki de PostgreSQL) comme ceci:

CREATE OR REPLACE FUNCTION unnest(anyarray)
  RETURNS SETOF anyelement AS
$BODY$
SELECT $1[i] FROM
    generate_series(array_lower($1,1),
                    array_upper($1,1)) i;
$BODY$
  LANGUAGE 'sql' IMMUTABLE

Ainsi, vous pouvez appliquer l’exemple pour PostgreSQL 8.4.

Sinon, vous pouvez aussi utiliser WHILE comme ceci:

WHILE v_loop_i <= v_array_count LOOP
 
        -- Vos instructions...
        v_array_data := v_monarray[v_loop_i];
 
        v_loop_i := v_loop_i + 1;
    END LOOP;

Avec:

  • v_array_count la taille du tableau
  • v_loop_i une variable de type integer initialisée à 1

Pl/PgSQL: Noms de variables dynamiques

Posted on by
Reply

Si dans un trigger vous voulez récupérer un champ qui change à chaque fois (nom dynamique), ou plus généralement si vous voulez récupérer la valeur d’un champ xxxxxx d’un RECORD, il faut utiliser EXECUTE. Soient:

  • NEW, la nouvelle ligne (automatiquement créé avec les triggers sur INSERT et UPDATE)
    Note: C’est une variable de type RECORD. Ainsi, vous pouvez la remplacer par n’importe quel autre RECORD.
  • v_critere, le nom du champ à récupérer
  • v_value, la variable dans laquelle on veut mettre la valeur
  • monschema.matable, le nom de la table qui correspond au RECORD.
EXECUTE 'SELECT (' || quote_literal(NEW) || '::monschema.matable).' || v_critere INTO v_value;

Ainsi, si v_critere = id_somethink, alors la valeur du champ “id_somethink” du RECORD sera placée dans la variable v_value. “Tout simplement” :-)

Merci beaucoup à Dimitri et Guillaume de la liste PostgreSQL pour m’avoir aider à trouver cette solution qui n’est pas implantée malgré le patch proposé.

Installer Redmine avec PostgreSQL

Posted on by
2

Redmine est un gestionnaire de projet, plus particulièrement avancé dans la gestion des tâches. Il gêre les dépots SVN, GIT ou CVS et peut être interfacé avec Mylyn sous Eclipse ! Il est très complet et néanmoins impressionnant par sa claretée.

Pour installer Redmine, il vous faut une installation Ruby On Rails. Nous allons utiliser PostgreSQL comme base de données.
Continue reading →

MySQL: Solutions de Haute Disponibilité

Posted on by
Reply

Lors de cette petite conférence, Olivier Dasini a présenter 3 grandes méthodes pour mettre en place une infrastructure de haute disponibilité avec MySQL.

La disponibilité peut en fait ce traduire par: uptime/(uptime+downtime) soit le rapport de l’uptime sur le temps pendant lequel le serveur aurait du être disponible.

  • 90 % est équivalent à 36,5 jours/an
  • 99,9999 % est équivalent à 31 secondes/an Continue reading →

Triggers et clés étrangères

Posted on by
Reply

Sous PostgreSQL, il y a plusieurs langages de procédures comme plpgsql pour éxécuter des commandes (surtout SQL) au sein de la base ou pgplsh pour créer des fonctions écrites en shell.

Avec ces langages, on peut écrire des fonctions qui seront appellées via des SELECT ou PERFORM dans le cas d’une éxécution depuis une autre fonction. Cependant, elles ont un autre avantages : elle peuvent être appellées par des triggers. Ce sont, en français, des déclencheurs, c’est-à-dire que c’est fonctions sont appelée avant ou après chaque ajout, modification ou suppression de ligne ou bien avant ou après chaque instruction (qui peuvent contenir plusieurs opérations de modification de ligne).

Les triggers peuvent modifier les données dans le cas d’une modification ou d’une insertion mais peuvent aussi empècher l’éxécution de l’opération dans tous les cas (à condition que le trigger soit paramétré pour être éxécuté avant l’opération sur la ligne). Les triggers après-insertion sont plus utiles qu’ils peuvent en avoir l’air car il permettent de répliquer des informations par exemple, ou de complèter des tables de backups par exemple. De plus, ces triggers AFTER ont connaissance des informations de séquences (clés primaires auto-complètées par exemple). Continue reading →

PostgreSQL : Une fonction en shell

Posted on by
1

Le langage de fonction principal de PostgreSQL est plpgsql, langage qui permet beaucoup d’interaction au sein de la base de données. Cependant, lorsque l’on veut automatiser un grand nombre de procédures au niveau de la base de données, il faut parfois avoir accès aux commandes shell, chose qui n’est pas possible dans plpgsql.

Pour avoir accès au shell, il nous faut changer le langage de fonctions. Nous allons donc choisir un langage créé pour creer des fonctions entièrement en shell !

PL/sh Procedural Language Handler

Comme nous l’apprend le titre, PL/sh est un langage de procédure créé pour PostgreSQL par Peter Eisentraut en 2001. La dernière version est actuellement la version 1.3 datant de fin décembre 2007, adaptée pour les versions 8.2 et 8.3 de PostgreSQL. Continue reading →