Transformer une base Oracle en OpenData avec Oracle REST Data Services

Dans un article précédent (Faire tourner Oracle Database Server dans un container Docker), nous avons créé un container Docker appelé oracle-db dans lequel tourne une base de données Human Resources.

Dans cet article, nous allons exposer partiellement la base de données sur le web avec OpenAPI. En résumé, nous allons permettre à des personnes autorisées d'accéder par exemple à http://oursite/api/employees pour obtenir la liste de tous les employés. Ce schéma OpenAPI généré peut d'ailleurs être linté ; voyez Validez votre schéma OpenAPI face aux standards REST Belgif.

Exactement comme nous l'avons fait avec PostgREST (voir mon article N'interrogez plus votre base PostgreSQL, préférez PostgREST) mais, cette fois, avec une base de données Oracle.

Et la magie viendra de Oracle REST Data Services, aussi appelé ords.

Voici où nous allons : la même table Oracle, désormais accessible en simple JSON via HTTP.

Dans l'article précédent (Faire tourner Oracle Database Server dans un container Docker), nous avons créé un container Docker appelé oracle-db dans lequel tourne une base de données Human Resources.

Lisez cet article et suivez les étapes pour disposer, avant de continuer ici, d'un container Docker fonctionnel comme décrit ci-dessous.

Quelques prérequis​

Télécharger une image Docker pour Oracle REST Data Services​

Contrairement à l'image Docker Oracle Database Server, aucun identifiant n'est nécessaire pour télécharger l'image officielle Oracle ORDS.

Lancez simplement docker pull container-registry.oracle.com/database/ords-developer:latest pour la récupérer. C'est une image de 2,2 Go.

$ docker pull container-registry.oracle.com/database/ords-developer:latest▋

Créer un container Oracle ORDS​

Nous allons procéder étape par étape.

Créer un volume pour la configuration ORDS​

ORDS a besoin de deux dossiers : un pour ce qu'il appelle ses secrets et un pour ses fichiers de configuration. Utilisons un volume Docker auto-géré pour les fichiers de configuration ; lancez donc docker volume create ords_config dans votre console.

Configurer la chaîne de connexion​

Pour pouvoir démarrer ORDS, la doc officielle indique qu'il faut créer un fichier appelé conn_string.txt avec notre chaîne de connexion. Dans notre cas, voici le fichier à créer :

$ mkdir -p ords_secrets
$ chmod 777 ords_secrets
$ echo 'CONN_STRING=SYS/admin@oracle-db:1521/ORCLPDB1' > ords_secrets/conn_string.txt▋

L'image Docker officielle ORDS est livrée avec APEX (l'abréviation d'Oracle Application Express).

D'après la documentation, Oracle Application Express (APEX) est une plateforme de développement low-code qui vous permet de construire des applications d'entreprise évolutives et sécurisées, dotées de fonctionnalités de premier plan, déployables n'importe où. Voir https://apex.oracle.com/ pour plus d'informations.

Pour nos besoins, nous voulons juste ORDS ; APEX ne nous sert à rien. Pour cela, nous allons initialiser la variable d'environnement IGNORE_APEX à TRUE.

Créer le container​

Pour créer notre container ORDS (sans APEX), lancez :

$ docker run -d --rm \
    --name oracle-ords \
    --network oracle \
    -p 8181:8181 \
    -e IGNORE_APEX=TRUE \
    -v ./ords_secrets/:/opt/oracle/variables \
    -v ords_config:/etc/ords/config/ \
    container-registry.oracle.com/database/ords-developer:latest
▋

Cette commande va ajouter le layer ORDS dans votre base de données (sur base de la chaîne de connexion, soit ORCLPDB1 chez nous).

$ docker run -d --rm \
    --name oracle-ords \
    --network oracle \
    -p 8181:8181 \
    -v ords_config:/etc/ords/config/ \
    container-registry.oracle.com/database/ords-developer:latest
▋

Nous devons créer notre utilisateur dans la base de données​

Avant de pouvoir utiliser ORDS et accéder à des objets via une requête HTTP, nous devons créer un nouvel utilisateur dans notre base et lui donner quelques droits.

Lancez docker exec -it oracle-db sqlplus sys/admin@ ORCLPDB1 as sysdba pour démarrer une console SQL*Plus et vous connecter à la base PDB.

Nous devons créer un utilisateur pour ORDS ; disons l'utilisateur hr. Lancez cette instruction : CREATE USER hr IDENTIFIED BY admin;

Puis accordez les permissions : GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO hr;

Une fois créé, nous devons quitter la console (démarrée avec l'utilisateur sys) et nous reconnecter avec notre utilisateur.

Tapez donc exit pour quitter la console sqlplus et lancez docker exec -it oracle-db sqlplus hr/admin@ ORCLPDB1.

De retour dans la console sqlplus (connecté en tant qu'utilisateur hr), lancez : EXECUTE ORDS.ENABLE_SCHEMA;.

(lisez ORDS 101: Enabling Oracle Schemas for HTTPS/REST si vous voulez plus de détails)

Démarrer l'interface web d'ORDS​

À ce stade, nous avons installé ORDS, configuré notre base pour l'utiliser et créé un utilisateur appelé hr. Nous pouvons nous rendre sur http://localhost: 8181/ords et nous connecter au dashboard ORDS :

http://localhost:8181/ords/_/landing

Utilisez hr et admin, notre utilisateur personnalisé, sur la page de login :

http://localhost:8181/ords/_/landing

Dès à présent, nous pouvons demander à ORDS la liste des objets déjà accessibles ; rendez-vous simplement sur http://localhost: 8181/ords/hr/open-api-catalog/ pour obtenir... une liste vide.

C'est normal puisque nous devons indiquer quel objet (une table, une vue, une procédure stockée) est accessible ou non. Mais oui, en accédant à http://localhost: 8181/ords/hr/open-api-catalog/ et en obtenant une réponse JSON, nous pouvons confirmer qu'ORDS tourne correctement.

La page http://localhost: 8181/ords/hr/open-api-catalog/ s'appelle le Schema Metadata (documentation).

Activer REST sur les objets de la base​

Nous devons donc activer chaque objet un par un. Le plus simple est d'utiliser Oracle SQL Developer. Si vous ne l'avez pas, téléchargez-le gratuitement.

Créez une nouvelle connexion avec ces paramètres :

Cliquez sur le bouton Save puis sur Connect.

Juste après le login, en cliquant sur la liste des tables ou des vues, il n'y a rien.

C'est normal : nous venons de créer notre utilisateur hr et le schéma hr associé, et il n'y a aucun objet dans ce schéma.

Ajouter une vue employee dans notre schéma hr​

Connectons-nous à notre base et affichons la liste des employés : lancez docker exec -it oracle-db sqlplus sys/admin@ ORCLPDB1 as sysdba, puis SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL FROM SYSTEM.EMPLOYEES;.

Maintenant que nous sommes sûrs d'avoir accès à la table, créons une vue dans notre schéma hr pour afficher le contenu de la table :

• lancez GRANT SELECT ON SYSTEM.EMPLOYEES TO hr; et
• créez la vue en lançant CREATE VIEW HR.EMPLOYEES AS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID FROM SYSTEM.EMPLOYEES;.

Si nous voulons vérifier notre vue :

• Tapez exit dans la console sqlplus puisque vous êtes connecté en tant que sys,
• Dans la console Linux, lancez docker exec -it oracle-db sqlplus hr/admin@ ORCLPDB1 pour vous connecter en tant que hr, puis
• lancez SELECT * FROM HR.EMPLOYEES;.

Comme vous le voyez, vous obtenez la liste des employés.

À ce stade, nous avons donc ajouté une vue appelée employees dans notre schéma hr et elle fonctionne.

Retournez dans l'interface Oracle SQL Developer, faites un clic droit sur le nœud Views et choisissez Refresh.

Vous verrez la vue ajoutée :

Clic droit sur la vue EMPLOYEES puis cliquez sur Enable REST Service....

Veillez à cocher la case Enable object, donnez un nom à votre objet et, pour ce petit tutoriel, décochez la case Authorization required.

Cliquez sur le bouton Next et prêtez attention à l'onglet SQL :

Vous obtiendrez le DDL à exécuter dans une console si vous souhaitez activer REST sur la table par le code, plutôt qu'avec l'interface graphique.

La dernière étape est de passer sur le site ORDS (http://localhost: 8181/ords/hr/_sdw/?nav=rest-workshop). Rafraîchissez la page et vous verrez maintenant que vous avez un objet dans la zone AUTOREST :

http://localhost:8181/ords/hr/_sdw/?nav=rest-workshop

Cliquez sur cette zone et vous verrez l'objet (qui est une vue) sur lequel nous avons activé REST précédemment :

http://localhost:8181/ords/hr/_sdw/?nav=rest-workshop

Regardez l'icône Open in a new tab sur l'image ci-dessus. Cliquez sur ce bouton et vous obtiendrez vos enregistrements en réponse JSON :

http://localhost:8181/ords/hr/employees/

L'URL http://localhost: 8181/ords/hr/employees/ s'appelle l'Object Data (documentation)

Comme ce n'est rien de plus qu'une URL, vous pouvez l'utiliser avec n'importe quel outil, par exemple curl. La commande à lancer est curl http://localhost:8181/ords/hr/employees/ | jq — la même sortie que celle montrée en début d'article.

Et qu'en est-il du catalogue OpenAPI​

Retournez dans votre navigateur et accédez à nouveau à http://localhost: 8181/ords/hr/open-api-catalog/.

Cette fois, vous verrez que vous avez un élément appelé EMPLOYEES.

Si vous voulez obtenir la description de cette ressource, la page http://localhost: 8181/ords/hr/open-api-catalog/employees/ peut être visitée pour obtenir par exemple la liste des champs exposés.

Cette page s'appelle l'Object Metadata (documentation)

Créons une nouvelle vue pour le plaisir :

• Lancez docker exec -it oracle-db sqlplus sys/admin@ ORCLPDB1 as sysdba puis
• Dans la console SQL, lancez GRANT SELECT ON SYSTEM.DEPARTMENTS TO HR; suivi de
• CREATE VIEW HR.DEPARTMENTS AS SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID FROM SYSTEM.DEPARTMENTS; et
• Passez dans l'interface Oracle SQL Developer, rafraîchissez la liste des vues, faites un clic droit sur la nouvelle vue departments

Maintenant, en rafraîchissant à nouveau http://localhost: 8181/ords/hr/open-api-catalog/, vous verrez que vous avez désormais un deuxième élément appelé DEPARTMENTS.

http://localhost:8181/ords/hr/open-api-catalog/

Travailler avec REST​

Rendez-vous sur http://localhost: 8181/ords/hr/_sdw/?nav=rest-workshop ou, depuis le menu hamburger, cliquez sur l'élément REST.

http://localhost:8181/ords/hr/_sdw/?nav=rest-workshop

Utiliser les options de pagination, de filtrage et de tri dans la querystring (facultatif — passez si l'endpoint par défaut suffit)​

Lors de l'accès à un endpoint (comme http://localhost: 8181/ords/hr/employees/), nous pouvons manipuler l'URL pour ajouter des paramètres de pagination, de filtrage ou de tri.

On parle de JSON QBE, pour JSON query-by-example.

Voyez la documentation officielle : https://docs.oracle.com/en/database/oracle/simple-oracle-document-access/adsdi/overview-soda-filter-specifications-qbes.html

Pagination​

Doc

Sauf configuration spécifique, ORDS utilise la pagination, c'est-à-dire qu'il limite la liste des enregistrements à 25 rows.

Donc, si vous avez plus de 25 employés, l'endpoint http://localhost: 8181/ords/hr/employees/ n'en retournera que les 25 premiers.

En accédant à une page « complète » comme http://localhost: 8181/ords/hr/employees/, nous ne recevrons en fait qu'un nombre précis d'enregistrements (selon la configuration d'ORDS). À la fin de la réponse JSON, il y aura des liens de navigation :

http://localhost:8181/ords/hr/employees/

Comme vous le voyez sur l'image, la réponse JSON fournit une liste d'enregistrements (dans un tableau nommé items) puis quelques propriétés comme hasMore (true/false), limit c'est-à-dire le nombre de rows à chaque appel, offset qui est la « page » (à 0 les 25 premières rows sont affichées, à 1 les rows 26 à 50, ...), count qui est le nombre de rows dans la réponse.

Donc, tant que hasMore vaut true, il reste des enregistrements à récupérer avec une requête suivante.

Il y a deux paramètres pour contrôler la pagination des résultats : offset et limit.

Nous pouvons donc lancer plusieurs requêtes comme http://localhost: 8181/ords/hr/employees/?offset=25, http://localhost: 8181/ords/hr/employees/?offset=50 et ainsi de suite.

Nous pouvons définir le nombre d'enregistrements par page avec le paramètre limit de la querystring : http://localhost: 8181/ords/hr/employees/?limit=100.

Filtrage​

La syntaxe générale est /?q={"column":{"$eq":"value"}}.

Supérieur à​

Par exemple, qui gagne plus de 20 000 € de salaire ? http://localhost: 8181/ords/hr/employees/?q={"salary":{"$gt":20000}} appliquera un filtre sur salary > 20000.

http://localhost:8181/ords/hr/employees/?q={"salary":{"$gt":20000}}

Égal​

Pour trouver tous les employés appelés Steven : http://localhost: 8181/ords/hr/employees/?q={"first_name":{"$eq":"Steven"}}

http://localhost:8181/ords/hr/employees/?q={"first_name":{"$eq":"Steven"}}

Instring / contains / like​

Les employés ayant le motif alex dans leur prénom : http://localhost: 8181/ords/hr/employees/?q={"first_name":{"$instr":"alex"}} (correspondra par exemple à Alexander ou Alexis).

http://localhost:8181/ords/hr/employees/?q={"first_name":{"$instr":"alex"}}

Qui travaille dans l'ICT ? Ici, c'est l'opérateur like qu'il faut utiliser : http://localhost: 8181/ords/hr/employees/?q={"job_id":{"$like":"IT_%"}} (IT_% signifie commence par)

http://localhost:8181/ords/hr/employees/?q={"job_id":{"$like":"IT_%"}}

Filtrage complexe​

Nous pouvons aussi utiliser AND comme dans cet exemple : http://localhost: 8181/ords/hr/employees/?q={"first_name":{"$instr":"alex"},"salary":{"$gt":5000}}, c'est-à-dire récupérer toutes les personnes ayant alex dans leur prénom et gagnant plus de 5 000 €

http://localhost:8181/ords/hr/employees/?q={"first_name":{"$instr":"alex"},"salary":{"$gt":5000}}

Utiliser le filtrage complexe

L'URL suivante http://localhost: 8181/ords/hr/employees/?q={"job_id":{"$like":"%CLERK"},"salary":{"$gt":2899},"hire_date":{"$gt":{"$date":"2016-12-31T12:59:59Z"}}} retournera tous les employés qui :

• travaillent comme clerk (job_id se terminant par le mot CLERK),
• ont un salaire supérieur à 2 899 € et
• ont été engagés à partir du 1er janvier 2017.

http://localhost:8181/ords/hr/employees/?q={"job_id":{"$like":"%CLERK"},"salary":{"$gt":2899},"hire_date":{"$gt":{"$date":"2016-12-31T12:59:59Z"}}}

Tri​

La syntaxe générale est /?q={"$orderby":{"fieldname1":"asc","fieldname2":"desc"}}.

Pour trier sur le prénom en desc puis sur le salaire (le plus élevé en premier) : http://localhost: 8181/ords/hr/employees/?q={"$orderby":{"first_name":"desc","salary":"desc"}}.

http://localhost:8181/ords/hr/employees/?q={"$orderby":{"first_name":"desc","salary":"desc"}}

Swagger​

ORDS peut générer un fichier openapi.json à utiliser avec Swagger. Pour en savoir plus : Manage & Monitor Oracle Database with REST APIs

Pour aller plus loin​

• ORDS best practices topics (www.oracle.com)
• Installation, Configuration, and Development Guide (docs.oracle.com)
• Blog de Jeff Smith - Articles sur ORDS
  • Adding HTTP(S) Access Logs for ORDS Standalone