Obtenir la bonne ligne en utilisant l'héritage des tables dans PostgreSQL

Question

J'ai récemment essayé d'abandonner Doctrine en raison des performances et des problèmes d'abstraction et de passer à une logique basée sur une base de données. J'utilise principalement PostgreSQL.

Doctrine

Une chose que j'ai appréciée dans Doctrine est l'héritage, que j'ai utilisé pour des rôles multiples dans une application web. Il y a une table de base/classe Personne et chaque rôle (par exemple admin, développeur, utilisateur) étend cette classe. Tous les utilisateurs partagent une table de base, ce qui permet de conserver un login/identicateur unique (dans mon cas, c'est un email). Mais obtenir les informations de la personne à partir de la doctrine a abouti à la classe finale, avec toutes ses propriétés. Par exemple :

$user = $em->getRepository('Entities\Person')->findOneBy(array('email' => 'john.doe@example.com'));
if ( $user instanceof Entities\Developer) {
    ... 
}

Fonctionnalité intéressante, mais lorsque l'on a beaucoup de rôles, la requête SQL résultante était très inefficace, en sélectionnant à partir de la classe de base, en joignant à gauche tous les rôles et ensuite par le discriminateur défini, le mappeur a construit la classe finale à partir de la table de base et de la table finale.

PostgreSQL

J'ai découvert que postgres a implémenté l'héritage de table et cela fonctionne bien. Mais j'aimerais simuler le comportement de Doctrine, en récupérant un rôle dans la base de données (sans connaître son rôle et donc sa table finale).

Pour de meilleurs exemples, mes tableaux ressemblent à ceci :

--
-- base people table
--
CREATE TABLE people
(
  id serial NOT NULL,
  first_name character varying(25) NOT NULL,
  last_name character varying(25) NOT NULL,
  email character varying(50) NOT NULL,
  "password" character varying(150),
  CONSTRAINT people_pkey PRIMARY KEY (id)
);

--
-- role developer (does not have any role specific info)
-- 

CREATE TABLE developer
(
-- Inherited from table people:  id integer NOT NULL DEFAULT nextval('people_id_seq'::regclass),
-- Inherited from table people:  first_name character varying(25) NOT NULL,
-- Inherited from table people:  last_name character varying(25) NOT NULL,
-- Inherited from table people:  email character varying(50) NOT NULL,
-- Inherited from table people:  "password" character varying(150),
  CONSTRAINT developer_pkey PRIMARY KEY (id)
)
INHERITS (people);

--
-- role user
-- 

CREATE TABLE installer
(
-- Inherited from table people:  id integer NOT NULL DEFAULT nextval('people_id_seq'::regclass),
-- Inherited from table people:  first_name character varying(25) NOT NULL,
-- Inherited from table people:  last_name character varying(25) NOT NULL,
-- Inherited from table people:  email character varying(50) NOT NULL,
  client character varying(50),
-- Inherited from table people:  "password" character varying(150),
  CONSTRAINT installer_pkey PRIMARY KEY (id)
)
INHERITS (people);

Solution 1 -> 2 requêtes

Il est assez simple de trouver le rôle à partir de la table de base people et ensuite de sélectionner directement à partir de la table role :

-- returns name of table (and role) 'developer'
SELECT pg.relname 
FROM people p, pg_class pg
WHERE pg.oid=p.tableoid and p.email = 'john.doe@example.com';

-- getting roles full info
SELECT *
FROM developer
WHERE email = 'kracmar@dannax.sk';

Cette solution est très bien, mais je cherchais une solution plus agréable.

Solution 2 -> 1 requête utilisant une procédure

Ce serait bien d'obtenir des informations sur l'utilisateur en une seule requête. Je me suis plongé dans la documentation des fonctions pour trouver quelque chose, mais je n'ai pas réussi à le faire. Je pensais que l'utilisation de la requête de retour serait une solution, mais mon problème est que je dois spécifier le type de résultat à la fonction, mais il peut changer en fonction du rôle de l'utilisateur (table différente avec le nombre de colonnes et de types).

C'est l'un des résultats, la fonction renvoie un enregistrement mais ce n'est pas une requête, une seule colonne avec tous les champs séparés par des virgules.

    CREATE OR REPLACE FUNCTION get_person_by_email(person_email VARCHAR) 
            RETURNS record 
            LANGUAGE plpgsql 
            STABLE STRICT AS
    $BODY$
            DECLARE 
                    role varchar;
                    result record;

            BEGIN

                    SELECT pg.relname 
                    INTO role
                    FROM people p,
                            pg_class pg
                    WHERE pg.oid=p.tableoid
                            AND p.email = person_email;

                    IF NOT FOUND THEN
                            RAISE exception 'Person with email % does not exists.', person_email;
                    END IF;

                    CASE 
                            WHEN role = 'developer' THEN
                                    SELECT * 
                                    INTO result 
                                    FROM developer
                                    WHERE email = person_email;
                            WHEN ROLE = 'installer' THEN
                                    SELECT * 
                                    INTO result
                                    FROM installer
                                    WHERE email = person_email;

                    END CASE;

                    RETURN result;
            END;
    $BODY$;

La sélection à partir de cette fonction est impossible car il manque la définition des colonnes. Peut-être que je complique les choses et que je devrais utiliser la solution 1, mais de cette façon je n'apprendrai rien. Toute aide sera appréciée.

Answer 1

Je suppose que vous pourriez être intéressé par Fourchette Doctrine2 de zyxist :

Fork de Doctrine 2 Object Relational Mapper visant à créer un véritable support de l'héritage des tables pour PostgreSQL

Vous pouvez en savoir plus sur blog de l'auteur .