Forums PostgreSQL.fr

Benjamin

Membre

Double compatibilité PostGreSQL / Oracle

Bonjour,

Je travaille pour un éditeur dont le logiciel est installé sur site chez ses clients.
Chaque installation repose sur :

- une application java, avec une couche d'ORM qui inclue des requêtes SQL, exécuté via une librairie JDBC

- une BDD Oracle propre à l'installation, accédée via JDBC par l'application java

Il n'y a pas de PL/SQL.

J'étudie la faisabilité de rendre la couche ORM de l'application java compatible avec PostGreSQL.
Un des scénario étudié serait d'avoir une couche ORM qui soit à la fois compatible PostGreSQL et Oracle.

Or la norme SQL, supportée à la fois par PostGreSQL et Oracle, ne semble pas suffisante pour couvrir toutes les fonctionnalités.

Par exemple pour faire des requêtes sur une arborescence d'objet :

- Avec Oracle :
   SELECT ...
   FROM ...
   START WITH ...
   CONNECT BY PRIOR ...
- Avec PostGre :
  WITH RECURSIVE tree (...) AS (
    SELECT ...
    FROM ...
  UNION ALL
    SELECT ...
    FROM ...
    WHERE ...
  )
  SELECT ... FROM tree;

Mais pas de syntaxe standard qui soit interprétable pour les 2 BDD semble-t-il.

Quelqu'un a-t-il une expérience / une expertise pour un projet qui consisterait à rendre une application à la fois compatible PostGreSQL et Oracle ?

rjuju

Administrateur

Re : Double compatibilité PostGreSQL / Oracle

Je ne comprends pas trop votre question.  Vous dites avoir un ORM, et c'est son rôle d'être compatible avec les deux moteurs, pas celui de l'application.  Je ne suis pas spécialiste d'ORM, mais j'imagine qu'il en existe beaucoup qui sont compatible oracle/pg.

edit: ah je viens de comprendre, il s'agit d'un ORM maison.  Oui, le SQL a beau être normalisé vous aurez de très nombreuses différences entre les moteurs.  J'imagine que vous générez un AST en fonction des appels.  La génération du sql spécifique ne devrait pas poser plus de soucis sur postgres qu'oracle, il faut juste implémenter une couche d'abstraction supplémentaire.  À mon avis, la plus grosse difficulé sera de gérer les comportements différents des moteurs (gestion des NULLs par exemple), que générer un ordre SQL un peu différent.

---

Julien.
https://rjuju.github.io/

dverite

Membre

Re : Double compatibilité PostGreSQL / Oracle

Dans l'exemple du CONNECT BY, c'est remplaçable par un WITH récursif équivalent puisque cette syntaxe est acceptée par Oracle depuis leur version 11g, cf http://www.orafaq.com/node/2993.

---

@DanielVerite
http://blog-postgresql.verite.pro/

Benjamin

Membre

Re : Double compatibilité PostGreSQL / Oracle

Merci : la solution avec le WITH récursif fonctionne effectivement, à quelques nuances près (en gras) :

Avec les données suivantes :
CREATE TABLE ARBORESCENCE(    
  ID VARCHAR(25),
    PERE_ID VARCHAR(25),
    REFERENCE VARCHAR(80),
    INTITULE VARCHAR(200));
INSERT INTO ARBORESCENCE (ID, PERE_ID, REFERENCE, INTITULE) VALUES ('1', null, '1', 'père');
INSERT INTO ARBORESCENCE (ID, PERE_ID, REFERENCE, INTITULE) VALUES ('11', '1', '11', 'fils 1');
INSERT INTO ARBORESCENCE (ID, PERE_ID, REFERENCE, INTITULE) VALUES ('12', '1', '12', 'fils 2');
INSERT INTO ARBORESCENCE (ID, PERE_ID, REFERENCE, INTITULE) VALUES ('111', '11', '111', 'petit-fils 1, fils de 11');
INSERT INTO ARBORESCENCE (ID, PERE_ID, REFERENCE, INTITULE) VALUES ('112', '11', '112', 'petit-fils 2, fils de 11');
INSERT INTO ARBORESCENCE (ID, PERE_ID, REFERENCE, INTITULE) VALUES ('121', '12', '121', 'petit-fils 1, fils de 12');

Sous PostGre :

WITH RECURSIVE ARBORENCE_RECURSIVE(ID, PERE_ID, REFERENCE, INTITULE, PROFONDEUR, CHEMIN) AS (
        SELECT A.ID, A.PERE_ID, A.REFERENCE, A.INTITULE, 1, CAST(A.ID AS TEXT)
        FROM ARBORESCENCE A
    WHERE PERE_ID IS NULL
    UNION ALL
        SELECT FILS.ID, FILS.PERE_ID, FILS.REFERENCE, FILS.INTITULE, PERE.PROFONDEUR + 1, PERE.CHEMIN || '/' || FILS.ID
        FROM ARBORESCENCE FILS, ARBORENCE_RECURSIVE PERE
        WHERE FILS.PERE_ID = PERE.ID
     )
SELECT * FROM ARBORENCE_RECURSIVE;

Sous Oracle :

WITH ARBORENCE_RECURSIVE(ID, PERE_ID, REFERENCE, INTITULE, PROFONDEUR, CHEMIN) AS (
        SELECT A.ID, A.PERE_ID, A.REFERENCE, A.INTITULE, 1, A.ID
        FROM ARBORESCENCE A
    WHERE PERE_ID IS NULL
    UNION ALL
        SELECT FILS.ID, FILS.PERE_ID, FILS.REFERENCE, FILS.INTITULE, PERE.PROFONDEUR + 1, PERE.CHEMIN || '/' || FILS.ID
        FROM ARBORESCENCE FILS, ARBORENCE_RECURSIVE PERE
        WHERE FILS.PERE_ID = PERE.ID
     )
SELECT * FROM ARBORENCE_RECURSIVE;

Dans les 2 cas, le résultat est :

"ID"                          "PERE_ID"               "REFERENCE"            "INTITULE"                  "PROFONDEUR"         "CHEMIN"                     
"1"                           ""                            "1"                           "père"                         "1"                           "1"                           
"11"                          "1"                          "11"                         "fils 1"                         "2"                           "1/11"                       
"12"                          "1"                          "12"                         "fils 2"                         "2"                           "1/12"                       
"111"                         "11"                        "111"                       "petit-fils 1, fils de 11"  "3"                           "1/11/111"                   
"112"                         "11"                        "112"                       "petit-fils 2, fils de 11"  "3"                           "1/11/112"                   
"121"                         "12"                        "121"                       "petit-fils 1, fils de 12"  "3"                           "1/12/121"

Benjamin

Membre

Re : Double compatibilité PostGreSQL / Oracle

@rjuju :
Oui il s'agit bien d'un ORM maison.