Un exemple complet d’implémentation d’Oracle Real Application Security avec Oracle APEX

Objectif

Objectif: établir une claire séparation des rôles entre l’équipe de développement et l’équipe chargée du modèle d’autorisations applicatives.

Plutôt que d’ajouter des filtres au niveau de l’application APEX ou bien au niveau de vues, l’approche est, ici, d’utiliser les fonctionnalités de Oracle RAS afin de mettre en place un contrôle d’accès via des rôles applicatifs.

Scénario

Une société fictive, BioAdvice, se prépare à lancer le Biogreen, un pesticide basé sur une nouvelle molécule.
Pour anticiper le volume à produire, l’équipe commerciale est chargée de sonder sa base installée afin d’enregistrer les intentions de commandes.
La force de vente de BioAdvice est organisée en cinq régions, avec un responsable régional à la tête et quelques commerciaux dans chacune des régions. Un directeur commercial supervise le niveau national et il est aidé par un assistant commercial. (voir Organisation en annexe).

Les règles d’accès et de visibilité sont les suivantes:

  • Le directeur commercial a accès sans restriction à toutes les informations.
  • L’assistant a accès à toutes les données, excepté la valeur du champs DISCOUNT
  • Le responsable régional a accès à toutes les données relative à la région dont il a la responsabilité.
  • Le commercial ne voit que les données, sans restriction, des clients qui sont dans son secteur.

Pour des raisons de commodité, un tableau excel a été envoyé à chaque commercial pour que celui-ci y enregistre les pré-commandes. L’idée est de consolider les tableaux qui seront remontés au fil de l’eau.

This image has an empty alt attribute; its file name is image-5-1024x576.png

Les tableaux sont remontés par mail ou déposés sur un file system puis assemblés via une macro excel.

Après quelques semaines de fonctionnement, cette approche s’avère inopérante car il est impossible d’obtenir une valeur consolidée en temps réel avec un bon niveau de fiabilité. On se retourne alors vers une solution basée sur un base de données unique avec un accès en ligne via une application web. La plateforme de développement retenue est Oracle APEX.

Une application est rapidement générée et l’échantillon ci-dessous est montré aux utilisateurs qui adhèrent à cette nouvelle interface.

La question qui reste à traiter concerne la visibilité des données. Comment respecter les règles qui ont été mentionnées plus haut ?
Lorsqu’on diffuse un tableau spécifique à chaque commercial, le problème est réglé par le fait même que le travail de découpage est déjà réalisé sous la forme de x tableaux spécifiques. Dans le cas d’une base centralisée, il faut ajouter une notion d’utilisateur et de droits d’accès. C’est ici qu’intervient Real Application Security (RAS).

Précisons que l’emploi de RAS n’est nullement un passage obligé ! On peut envisager une solution simple (mais présentant quelques défauts comme il sera discuté plus loin) consistant à créer une vue jouant le rôle de filtre.

Cette vue sera du type :

Rename ADO_PLAN to ADO_PLAN_T;
create or replace view ADO_PLAN as 
 select * from ADO_PLAN_T
 where region in (select region 
             from ado_emp 
             where job = 'responsable régional' and 
             login = lower(v('APP_USER'))
                 )
 or
 exists (select 1 
     from ado_emp 
     where login =lower(v('APP_USER')) and 
     job = 'directeur commercial'
     )
 or
 commercial = lower(v('APP_USER'))

On met à profit la connaissance de l’utilisateur qui est authentifié dans l’application APEX pour utiliser cette valeur (‘APP_USER’) dans le filtre de la vue. L’application reste inchangée. Il y a cependant une règle de sécurité qui devra être résolue en programmant directement une condition au niveau du champs DISCOUNT, dans la page liée au formulaire de détail ainsi que dans le rapport sur les commandes.

Génération d’une application APEX

Mise en oeuvre de RAS

Prise en compte de RAS dans l’interface APEX

Les utilisateurs doivent être enregistrés à la fois dans Oracle APEX, en tant qu’utilisateur et dans RAS en tant que principal. L’authentification sera toujours sous le contrôle d’APEX. A ce propos, un mécanisme de provisionnement est à mettre en place pour des architecture s’appuyant massivement sur RAS.

Masquage de colonnes pour certains rôles

RAS permet de masquer des colonnes. Dans notre exemple, les assistants commerciaux peuvent visualiser toutes les commandes, mais ne peuvent pas avoir connaissance du taux de discount accordé par le commercial pour son client. Observer que la colonne DISCOUNT est affichée avec des ‘xxx’.
On utilise la fonction:

COLUMN_AUTH_INDICATOR(col)
RETURN BOOLEAN;

Limitations, anomalies, bugs

Plugins
A ce stade, les tests que j’ai réalisés avec un plugin sont négatifs. C’est à dire que une requête sur la table DEMO.ADO_PLAN à l’intérieur d’un plugin ne ramène aucune ligne. Cela est bloquant dans l’usage d’un plugin de visualisation des données sur une maps Google que j’avais prévu d’utiliser.

Latence dans l’activation de RAS
Lors de l’activation de RAS, un délai de plusieurs heures peut s’avérer nécessaire pour que l’authentification fonctionne. Ci-dessous le message d’erreur obtenu. Quelques heures plus tard, ce message n’apparaissait plus.

Usage d’une subquery dans la définition du Data Policy
Je n’ai pas réussi a utiliser une sous-requête en voulant exprimer la règle concernant la région pour les responsables régionaux. Je pensais utiliser au départ une table telle qu’elle figure en annexe et invoquer le prédicat suivant:

'REGION IN (select REGION from DEMO.ADO_EMP where REGION = '||'REGION and -- login = lower(xs_sys_context(''xs$session'',''username'')))
dans la définition du REALM associé à l'ACL portant sur les responsables régionaux.
(à la place de 
       realms(2) := xs$realm_constraint_type(
                         realm => 'REGION = &' || 'PREGION'        
                    );
)

mais j’ai toujours abouti à des erreurs sur le prédicat. J’ai donc laissé tomber cette piste et j’ai créé autant d’ACLs qu’il y avait de régions. D’un point de vue « philosophique » c’est peut-être la meilleure approche dans la mesure où les informations sur l’organisation sont déportées dans le dictionnaire de RAS et non pas dans une table (ADO_EMP) d’un schéma applicatif.

Annexes

Scripts de création des Principals, ACLs et Data Security Policies

Tous les scripts de création d’un modèle d’autorisation, ci-dessous, sont accessibles dans leur dernière version sur github.

Principals

exec xs_principal.create_role(name => 'sr_role', enabled => true);
exec xs_principal.create_role(name => 'hq_role', enabled => true);
exec xs_principal.create_role(name => 'sa_role', enabled => true);
exec sys.xs_principal.create_role(name => 'sud_ventes', enabled => TRUE);
exec sys.xs_principal.create_role(name => 'nord_ventes', enabled => TRUE);
exec sys.xs_principal.create_role(name => 'est_ventes', enabled => TRUE);
exec sys.xs_principal.create_role(name => 'ouest_ventes', enabled => TRUE);
exec sys.xs_principal.create_role(name => 'centre_ventes', enabled => TRUE);
 grant db_bio to sr_role;
 grant db_bio to hq_role;
 grant db_bio to sa_role;
 grant db_bio to ouest_ventes;
 grant db_bio to sud_ventes;
 grant db_bio to nord_ventes;
 grant db_bio to est_ventes;
 grant db_bio to centre_ventes;
 -- Directeur ventes
 exec  xs_principal.create_user(name => 'alain', schema => 'DEMO');
 exec  sys.xs_principal.set_password('alain', 'alain');
 exec  xs_principal.grant_roles('alain', 'hq_role');
 -- Responsable régional
 exec  xs_principal.create_user(name => 'ygor', schema => 'DEMO');
 exec  sys.xs_principal.set_password('ygor', 'ygor');
 exec  xs_principal.grant_roles('ygor', 'ouest_ventes');
 -- Commercial
 exec  xs_principal.create_user(name => 'brice', schema => 'DEMO');
 exec  sys.xs_principal.set_password('brice', 'brice');
 exec  xs_principal.grant_roles('brice', 'sr_role');
 -- Assistant commercial
 exec  xs_principal.create_user(name => 'pierre', schema => 'DEMO');
 exec  sys.xs_principal.set_password('pierre', 'pierre');
 exec  xs_principal.grant_roles('pierre', 'sa_role');
 
 Give create session privilege
 BEGIN  
     SYS.XS_PRINCIPAL.GRANT_ROLES('ALAIN', 'XSCONNECT'); 
     SYS.XS_PRINCIPAL.GRANT_ROLES('YGOR', 'XSCONNECT'); 
     SYS.XS_PRINCIPAL.GRANT_ROLES('BRICE', 'XSCONNECT');
     SYS.XS_PRINCIPAL.GRANT_ROLES('PIERRE', 'XSCONNECT');
 END;
 /

ACLs

 Creating ACLs: EMP_ACL, IT_ACL, and HR_ACL
 (Ace stands for Access Control Entry)
 declare
       aces xs$ace_list := xs$ace_list();
     begin
       aces.extend(1);
 -- SR_ACL: This ACL grants SR_ROLE the privileges to view a sales rep's
 --          own record including DISCOUNT column.
 aces(1) := xs$ace_type(
             privilege_list => xs$name_list('select','view_discount'),
             principal_name => 'sr_role');
sys.xs_acl.create_acl(name => 'sr_acl', 
ace_list  => aces,
sec_class => 'bioprivs');
 -- HQ_ACL:  This ACL grants HQ_ROLE the privileges to view and update all
 -- order records including DISCOUNT column.
 aces(1):= xs$ace_type(
          privilege_list => xs$name_list('all'),
          principal_name => 'hq_role');
sys.xs_acl.create_acl(name => 'hq_acl',
ace_list  => aces,
sec_class => 'bioprivs');
 -- SA_ACL:  This ACL grants SA_ROLE the privileges to view and update all
 -- order records excluding DISCOUNT column.
      aces(1):= xs$ace_type(
          privilege_list => xs$name_list('select'),
          principal_name => 'sa_role');
 sys.xs_acl.create_acl(name => 'sa_acl',
ace_list  => aces,
sec_class => 'bioprivs');    
 end;
    /

Data Security Policy

 --Example 5-19 Creating the EMPLOYEES_DS Data Security Policy
 declare
    realms   xs$realm_constraint_list := xs$realm_constraint_list();
    cols     xs$column_constraint_list := xs$column_constraint_list();
 begin
    realms.extend(4);
 -- Realm #1: Only the order's own record.   
 --  SR_ROLE can view the realm including DISCOUNT column. 
  
    realms(1) := xs$realm_constraint_type(realm => 'COMMERCIAL = lower(xs_sys_context(''xs$session'',''username''))',
 acl_list => xs$name_list('sr_acl'));  

 -- Realm #2: The records in the same region as the Region Manager.  --                 realms(2) := xs$realm_constraint_type(realm => 'REGION = &' || 'PREGION' );
  
 -- Realm #3: All the records.  
 -- HQ_ROLE can view and update the realm including DISCOUNT column.  realms(3) := xs$realm_constraint_type(realm => '1 = 1',
   acl_list => xs$name_list('hq_acl'));

  --  Realm #4R: All the records and no acces to DISCOUNT.
  --  SA_ROLE can view and update the realm excluding DISCOUNT column.
   realms(4) := xs$realm_constraint_type(
        realm    => '1 = 1',
        acl_list => xs$name_list('sa_acl'));
-- Column constraint protects DISCOUNT column by requiring view_discount  -- privilege.  
 cols.extend(1);
 cols(1) := xs$column_constraint_type(column_list => xs$list('DISCOUNT'),
     privilege   => 'view_discount');
sys.xs_data_security.create_policy(name=> 'bio_ds', 
  realm_constraint_list  => realms,
  column_constraint_list => cols);  sys.xs_data_security.create_acl_parameter(policy => 'bio_ds',
  parameter => 'PREGION', 
  param_type => XS_ACL.TYPE_VARCHAR);  
end;
/

ACL pour une région

DECLARE
   ace_list XS$ACE_LIST;
 BEGIN
   ace_list := XS$ACE_LIST(
              XS$ACE_TYPE(privilege_list => XS$NAME_LIST('SELECT'),
                      granted => true,
                      principal_name => 'ouest_ventes'),
              XS$ACE_TYPE(privilege_list => XS$NAME_LIST('SELECT', 'view_discount'),
            granted => true,
           principal_name => 'ouest_ventes'));
 sys.xs_acl.create_acl(name => 'view_ouest_ventes',
                 ace_list => ace_list,
                 sec_class => 'bioprivs',
                 description => 'Authorize read access for the ouest region');
 sys.xs_acl.add_acl_parameter(acl => 'view_ouest_ventes',
                            policy => 'bio_ds',
                            parameter => 'PREGION',
                            value => 'ouest');
 END;
 /  
 -- ACLs for other regions have to be done !

Validation

-- Validating policy
 begin
   if (sys.xs_diag.validate_workspace()) then
     dbms_output.put_line('All configurations are correct.');
   else
     dbms_output.put_line('Some configurations are incorrect.');
   end if;
 end;
 /

Activation

-- Apply the data security policy to the EMPLOYEES table.
 begin
   xs_data_security.apply_object_policy(
     policy => 'bio_ds', 
     schema => 'DEMO',
     object =>'ADO_PLAN');
 end;
 /

Jeu de test

Organisation

Le tableau ci-dessous récapitule les Business Roles dans l’organisation de la société BioAdvice. Chacun sera traduit en un ou plusieurs rôles applicatifs. Les noms en gras sont ceux échantillonnés pour la démo

NOMPRENOMLOGINREGIONJOB
KlaessonFilmorefilmorecentreassistant commercial
GinnetyAdrienadriencentreresponsable régional
CostesAlainalaincorpdirecteur commercial
GrinishTaitetaitecorpdirecteur marketing
EarlyMerrickmerrickestcommercial
Mc PakeWaverleywaverleyestcommercial
GodingCharlescharlesestresponsable régional
KettelBrennenbrennennordcommercial
IannoAdamadamnordresponsable régional
PetcheyCorycorynordcommercial
CreanyIvarivarouestcommercial
CalkinYgorygorouestresponsable régional
TchaikovPatrickpatrickouestcommercial
SpondleyTalberttalbertouestcommercial
DuguetPierrepierreouestassistant commercial
LacombesBricebriceouestcommercial
FattoreEveretteverettsudcommercial
RandallCleoncleonsudresponsable régional
StedallGarrotgarrotsudcommercial

Feuille excel des commandes consolidées

Ce tableau est celui utilisé pour l’initialisation des données dans la table ADO_PLAN. Il est le résultat de la consolidation de tous les tableaux partiels remontés par les commerciaux.

Access to MyDeezer

This application gives ability to view and export playlists content in CSV/HTML format from your own Deezer account.
In order to export a playlist content, select it in the playlists list, choose the output format (CSV or HTML) then click on Export button.
Limitations: the maximum number of tracks beeing exported is 1000.
During navigation through tracks, it’s possible to set the number of tracks displayed on each page. Default is 10.

It’s possible to group tracks by artist : instead « 1.Primary report », Select the « 2.By artist » report in the upper select list.

Samples catalog

If choosing the HTML format, the tool will extract all the tracks in one html document with a link to a title preview (similar to the Deezer behaviour when the recipient has’nt got a deezer account). When the CSV format is chosen, the preview link is not retained in the exported attributes.

Issues

if the message « No data found » is displayed, that means probably that the token has expired. In this case, login again.
The Access token value can be seen in the Infos entry menu.

Consent and Confidentiality

The tool requires a very basic authorization set and has knowledge of « account name » only, no more. The consent page is displayed after first login page.

Updated on 28 oct 2019
GPM Factory

Leverage the Oracle BI Publisher templating power and produce high fidelity reports

Abstract

Oracle APEX is a tool which is well suited for rapid applications developpement (RAD). It’s possible to produce a tabular report based one a query in a very fast way. This is a standard feature. But when the goal is to build high precision reports « pixel perfect », like an invoice, for instance, we must switch to an other approach. The good news is that Oracle APEX gives ability to use XSLT-FO templates instead the standard ones. Perfect ! But how authoring these XSLT-FO templates, while keeping the APEX low code philosophy ? The following in this post explains a possible solution.

The approach is to use Oracle BI PUblisher Desktop product (BIP). It’s a plugin for Microsft Word which « transforms » MS Word in a template authoring tool without burden of with XSLT and XPATH syntax. ( One other alternative is to use Apache POI project then adding XPATH expressions in the XSL-FO template)
Important notice: It will be possible to use a functionnal subset of the BIP product but not all features. Therefore, the following informations must be considered as an experimental work only.

Materials & Documentation

Prerequisites

License Prerequisites

  • A commercial license for using BI Publisher (BIP)
  • A open source license for the SAXON xsl parser

We’ll not rely on BIP FOP engine and we’ll just use the BIP Desktop plugin. The license for BIP can be purchased on a Named User metric. That means that we can manage to buy at a low price but with a minimum of 10 licenses per processor.

SAXON is sold through several editions. The home edition, SAXON-HE is an open source product under Mozilla public license V2. It can be enough for testing and regular use as well.

Download & Documentation

Documentation

About XPATH and XSLT Syntax, BIP Desktop samples are a good source of inspiration:
« C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\samples\RTF templates »

Technical prerequisites

This post assumes a basic knowledge of RTF template design with BIP.
Once the RTF template is setup, we use the embeded function for exporting the template as an XSL-FO stylesheet.

Export option inside BIP Desktop plugin

From BIP plugin, an export menu option gives ability to export the template as a XSL-FO stylesheet. This exported stylesheet contains additional xsl function which are specific to BIP and can’t be used directly in Oracle APEX. In order to use a regular FO Processor (the one imbedded in APEX), we have to either remove or convert it, when possible.
For that purpose, we use a simple plsql procedure which will do a kind of cleanup in the template. It’is a fork of a Pavel Glebov’s project https://github.com/patrickmonaco/BIP_TO_FOP.
Some additional details have been added.
The XSL-FO beeing a regular XML document,it would have been ideally more judicious to use a XSLT parsing method, but this discussion is out of the scope of this post.

Implementation

Reminders about the FOP engine

An Apache FOP engine 2.3 is embedded in the ORDS java application. This engine relies on a XALAN (XSLTC) transformerFactory. This parser is compliant whit XSLT V1 but not XSLT v2. And this is a little bit ennoying for our purpose because some group functions generated by BIP should have to be rewritten for working in XSLT v1. Because we don’t want to developp a too complex convert tool, I prefered susbitute for XALAN (XSLTC) an other XSLT parser (SAXON) in order to minimize conversion effort.

This parser, SAXON, is XSLT v2 compliant . Keep in mind that license fees can be required in some cases.

So, once we setup the new transformerFactory (new way to transform XML input) in the CATALINA environmement file (if Apache Tomcat is used as a java container) , and after adding SAXON library in Tomcat lib directory, the way becomes much easier.

set CATALINA_OPTS=%CATALINA_OPTS% -Djavax.xml.transform.TransformerFactory=net.sf.saxon.TransformerFactoryImpl

Once the query has been created and the layout has been associated to, the report can be launched with the following URL:
f?p=&APP_ID.:0:&SESSION.:PRINT_REPORT=empdept

Install database objects

A DDL script (FOP_DDL) and a package, BOP_PKG, have to be installed in the worskpace owner schema.
cf https://github.com/patrickmonaco/BIP_TO_FOP

Adding a template repository

A simple APEX application is provided for managing templates. When a new template BIP template is uploaded, a new converted template is created.
At this stage, the lifecycle is:

  • Update/Create RTF template with MS Word & BIP Plugin
  • Export RTF template in XSL-FO (with BIP Word Plugin)
  • Upload the new version template in BOP repository
  • Download the converted template
  • Remove the previous layout
  • Re-create a new layout with the same name (or not) and upload the converted template
  • Update the report query to setup the new layout

The process is quite cumbersome because on each update, we have to remove then re-create a new layout, then upload the converted wslt-fo template. In order to minimize the tasks, the idea is to synchronise the repository with the apex layouts internal table.
Note: The following step is optionnal. Remove the related code inside the application if this step is to be ignored.

The name of this table is: WWV_FLOW_REPORT_LAYOUTS
The pl/sql procedure then updates the layout by uploading the template converted previously. The APEX table is: WWV_FLOW_REPORT_LAYOUTS .
Full access rights on this table must be granted to the workspace owner.

As SYS user, let’s grant DML on this table

grant ALL on APEX_<XXX>.WWV_FLOW_REPORT_LAYOUTS TO DEMO;

Now the lifecycle becomes:

  • Update RTF template with MS Word
  • Export RTF template in XSL-FO (with BIP Word Plugin)
  • Upload the new version template in BOP repository

Because each update in BOP Table will update the conterpart in WWV_FLOW_REPORT_LAYOUTS table, no more effort is needed.

Limitations:

  • Numerous BIP (xdo) functions are not supported
  • Wordarts, shapes are not supported
  • Static graphics (ie: an image included in the rtf template) encoded in base64 are not supported
  • Formatting in XPATH expression is supported for number datatype only

Discussion about XML dataset

Above, this is a XML sample generated by a Report Query.

select emp.*, 
dept.dname, 
dept.loc 
from emp, dept 
where emp.deptno = dept.deptno 

We can observe that the dataset is rendered as a single table (a single rowset), in other words, a flat tree. There is no way to build, with a regular report query, a complex tree document. The consequence is that we have to use Group Function inside the xst-fo template if break page is needed in the final report.

Therefore, because of the previous point, we’ll have to rely on XSLT group function in the plugin, for achieving some behaviours like page break. This point explains why we subsitute to XALAN (XSLTC), the SAXON parser which is more suitable.

Use cases

Tabular reports

There is no difficulty to design a template which produce a tabular report. It’s possible to leverage most of the features of BIP Desktop combined with the powerful of MS Word. The page break is managed by the Apache XSL-FO engine.

Tabular output with conditionnal formating

Invoices

It’s a very common type of document where there is :

  • a header: (invoice header, customer address, ..)
  • one or more set of data multi-lines related to the invoice (product items)
  • a footer: total and legal mentions, terms and conditions …

In the context of Oracle APEX, we must keep in mind that the XML output is flat. We just can leverage multiple rowsets in the same report. That leads to two different ways for building the XML dataset. The first one is to get a single rowset by joing all needed data upon a common key (order_id). The second one is to get a first rowset for the header information and other ones for multiple lines data.

One occurence of a result set

Goal: print a single document with a single occurence. ie: An invoice for a given order id.

In this case, the query can contains a single sql statement whichs is a join with all participants tables (order, ords items, products, customer).
Or we can combine two queries: one for the header ans on another for the order lines.

All occurences of a result set

In this case, the goal is to print a single document which contains all occurences returned by a query with an optional page break between occurences.

Query:

select emp.*, 
dept.dname, 
dept.loc 
from emp, dept 
where emp.deptno = dept.deptno 

Oracle APEX generates a XML document with the following schema:

XML schema generated by APEX Report Query
Output pdf with page break on Department
BIP RTF template



Fonts

Using specific fonts requires some additionnal setup. We need to adapt ORDS starting parameters.
A parameter in ORDS configuration file must be modified for pointing to the FOP configuration file:

<entry key="fop.configfile">
X:\distrib\fop-2.2\fop\conf\fopapex.xconf
</entry>

Once the server has been restarted, all the requierd fonts will be honored by Apache FOP. Otherwise, there is a fallback mechanism which maps the original fonts on a small subset (Times Roman).

Fonts embedded by the pdf document with fonts
Fonts embedded in the output pdf without fonts

Conclusion

The method presented previously shows up a good balance between complexity/High quality and simplicity/basic rendition in pdf document design. The BIP desktop plugin gives a very good flexibility provided you get a correct knowledge of MS Word.

APPENDIX

The following sample gives an overview of the XPATH query wich is generated by the BIP desktop plugin. Of course, it’s always possible to enrich the behavior by adding some additional XPATH expressions.

XPATH generated by BIP Desktop plugin
PDF output with the FOP engine / report with XSLT break
PDF output with the APEX FOP engine – Master detail
Simple Invoice RTF template in BIP desktop

Queries used for invoice demo

Multi-queries – two rowsets

select 
o.order_id,
o.customer_id,
o.order_total,
to_char(o.order_timestamp,'DD MONTH YYYY') order_timestamp,
c.cust_first_name,
c.cust_last_name,
c.cust_street_address1,
c.cust_street_address2,
c.cust_city,
c.cust_state,
c.cust_postal_code
from demo_orders o,
demo_customers c
where c.customer_id = o.customer_id
and o.order_id = 1

select
i.unit_price,
i.quantity,
round((i.quantity * i.unit_price),2) amount,
p.product_name,
p.product_description
from
demo_order_items i,
demo_product_info p
where p.product_id = i.product_id and
i.order_id=1
Sample multiple queries (rowsets)

Single Query – single rowset

select 
 o.order_id, 
 o.customer_id,
 o.order_total,
 o.order_timestamp,
 i.unit_price,
 i.quantity,
 p.product_name,
 p.product_description,
 c.cust_first_name,
 c.cust_last_name,
 c.cust_street_address1,
 c.cust_street_address2,
 c.cust_city,
 c.cust_state,
 c.cust_postal_code
 from demo_orders o,
 demo_order_items i,
 demo_customers c,
 demo_product_info p
 where o.order_id = i.order_id and
 p.product_id = i.product_id and
 c.customer_id = o.customer_id

Un chercheur Australien, Murray Cox, met en partage des données d’usage de AIRBNB sur le site insideairbnb.com. On y apprend qu’il y a plus de 60.000 logements proposés dans Paris (à comparer avec les 80.000 chambres d’hôtels) et que parmi les nombreux casse-têtes de la ville, il y a celui de l’usage abusif des logements sociaux dont certains sont proposés sur airbnb.
Je me suis livré à une expérience sur ces données, combinées avec celles de la ville de Paris (en open data) qui recense les immeubles de son parc de logements sociaux.
L’objectif est de rapprocher deux sources de données afin d’identifier l’usage anormal d’un logement social.

Méthode

1. Télécharger le set de données le plus récent pour la ville de Paris sur le site insideairbnb.com/paris (60.000 logements environ en juillet 2019)

2. Télécharger le set de données contenant la liste des logements sociaux de la ville de paris depuis le site open data (3100 immeubles, environ)

3. Charger les deux fichiers CSV dans une base relationnelle (Oracle dans mon cas avec le produit Oracle APEX)

4. Géolocaliser plus pécisément les adresse des logements sociaux à partir des APIs Google Maps. Les coordonnées fournies dans le fichier open Data présentent trop d’écart avec la localisation réelle sur Google Maps, déduite de l’adresse.

5. Effectuer un appariement (simple produit cartésien. cf requête en annexe) afin de détecter les localisations très proches (distance géodésique inférieure à 5m)

Extraire les couples répondant au critère ci-dessus et ne retenir que ceux présentant la caractéristique d’être proposés en location complète (ce qui est illégal pour une sous-location d’un appartement HLM, par exemple)

On arrive à un total de 334 logements, dont 39 sont très pratiquement confondues (moins de 2 m).
Il ne faut surtout pas conclure que ces propositions de logement sont illégales ! Il s’agit simplement d’une base de travail à partir de laquelle il reste à effectuer d’autres vérifications pour écarter toute suspicion.
Pour info: un id de logement airbnb, qui est unique, peut être examiné en utilisant l’url de la forme: https://www.airbnb.fr/rooms/XXXXX

Interface d’analyse des données

Les snaps d’écran suivants illustrent l’interface graphique que l’on peut produire rapidement avec Oracle APEX, et que l’on peut mettre à disposition d’analystes fonctionnels.

Annexes

Requetes SQL utilisées


insert into air_map (id_ag,id_ta,la_ag,lg_ag,la_ta,lg_ta,geo_dist)
select
    A.id,
    T.id, 
    to_char(A.LATITUDE),
    to_char(A.LONGITUDE),
    to_char(T.LATITUDE),
    to_char(T.LONGITUDE),
    trunc(
         sdo_geom.sdo_distance(
              sdo_geometry(2001, 4326, sdo_point_type( a.longitude,a.latitude, null), null, null),
              sdo_geometry(2001, 4326, sdo_point_type(t.longitude,t.latitude, null), null, null)
         )
    )
from AIR_PARIS A, AIR_PARIS_HLM T
where trunc(A.latitude,3) = trunc(T.latitude,3) and trunc(A.longitude,3) = trunc(T.longitude,3)

Géocodage

create or replace FUNCTION  "AIR_GPS"  
return VARCHAR2
is

-- P MONACO - GPM FACTORY
-- Aug 2019

l_clob CLOB;
t_temp varchar2(2000);
tnb INTEGER;
tlat VARCHAR2(30);
tlong VARCHAR2(30);
taddr VARCHAR2(2000);
tcode VARCHAR2(2000);
l_values apex_json.t_values;
l_count pls_integer;
tkey varchar2(100);
CURSOR poi_cur
IS
SELECT * from AIR_PARIS_HLM
where
glong is null;
l_poi poi_cur%ROWTYPE;
BEGIN
-- Retrieve API Key
select key into tkey
from API_KEY
where name = 'Google Maps API';
OPEN poi_cur;
LOOP
FETCH poi_cur INTO l_poi;
EXIT WHEN poi_cur%NOTFOUND; -- We go away

apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';

l_clob := APEX_WEB_SERVICE.make_rest_request(
p_url => 'https://maps.googleapis.com/maps/api/geocode/json',
p_parm_name => apex_util.string_to_table(
'address:key'),
p_parm_value => apex_util.string_to_table(
l_poi.adresse||', Paris France:'||tkey
),
p_http_method => 'GET'
);

apex_json.parse(p_values => l_values,
p_source => l_clob );
l_count := apex_json.get_count(
p_path => '.',
p_values => l_values
);

l_count :=1;
for idx in 1 .. l_count
loop
tlat := apex_json.get_varchar2(p_path=>'results[%d].geometry.location.lat',p0=> 1,p_values=>l_values);
tlong := apex_json.get_varchar2(p_path=>'results[%d].geometry.location.lng',p0=> 1,p_values=>l_values);

end loop;
if tlat is null then tlat := 0;end if;
update air_paris_hlm set glong=tlong, glat = tlat
where id = l_poi.id;
COMMIT;
END LOOP; -- End cursor
COMMIT;
CLOSE poi_cur;
htp.p('Terminé');
return('0');
EXCEPTION
when others then
htp.p('Undefined problem');
END;

Tables

CREATE TABLE  "AIR_PARIS" 
    (    "ID" NUMBER, 
     "NAME" VARCHAR2(255), 
     "HOST_ID" NUMBER, 
     "HOST_NAME" VARCHAR2(255), 
     "NEIGHBOURHOOD_GROUP" VARCHAR2(1), 
     "NEIGHBOURHOOD" VARCHAR2(30), 
     "LATITUDE" NUMBER, 
     "LONGITUDE" NUMBER, 
     "ROOM_TYPE" VARCHAR2(30), 
     "PRICE" NUMBER, 
     "MINIMUM_NIGHTS" NUMBER, 
     "NUMBER_OF_REVIEWS" NUMBER, 
     "LAST_REVIEW" DATE, 
     "REVIEWS_PER_MONTH" NUMBER, 
     "CALCULATED_HOST_LISTINGS_COUNT" NUMBER, 
     "AVAILABILITY_365" NUMBER, 
      CONSTRAINT "AIR_PARIS_PK" PRIMARY KEY ("ID")
   USING INDEX  ENABLE
    )

CREATE TABLE  "AIR_PARIS_HLM" 
    (    "ID" NUMBER, 
     "ADRESSE" VARCHAR2(255), 
     "CODE_POSTAL" NUMBER, 
     "VILLE" VARCHAR2(30), 
     "ANNÉE_DU_FINANCEMENT___AGRÉMENT" NUMBER, 
     "BAILLEUR_SOCIAL" VARCHAR2(30), 
     "NOMBRE_LOGEMENTS" NUMBER, 
     "PLA_I" NUMBER, 
     "PLUS" NUMBER, 
     "PLUS_CD" NUMBER, 
     "PLS" NUMBER, 
     "MODE_DE_RÉALISATION" VARCHAR2(100), 
     "COMMENTAIRES" VARCHAR2(1000), 
     "ARRONDISSEMENT" NUMBER, 
     "NATURE_DE_PROGRAMME" VARCHAR2(255), 
     "COORD_X_L93" NUMBER, 
     "COORD_Y_L93" NUMBER, 
     "GEO_SHAPE" VARCHAR2(300), 
     "GEO_POINT_2D" VARCHAR2(300), 
     "LATITUDE" NUMBER, 
     "LONGITUDE" NUMBER, 
     "GLAT" NUMBER, 
     "GLONG" NUMBER, 
      CONSTRAINT "AIR_PARIS_HLM_PK" PRIMARY KEY ("ID")
   USING INDEX  ENABLE
    )

CREATE TABLE  "AIR_MAP" 
    (    "ID" NUMBER(10,0), 
     "ID_AG" NUMBER, 
     "ID_TA" NUMBER, 
     "LA_AG" VARCHAR2(30), 
     "LG_AG" VARCHAR2(30), 
     "LA_TA" VARCHAR2(30), 
     "LG_TA" VARCHAR2(30), 
     "GEO_DIST" NUMBER(10,0), 
     "ROAD_DIST" NUMBER(10,0), 
     "ROAD_TIME" NUMBER(10,0), 
     "ROAD_TIME_TEXT" VARCHAR2(100), 
     "PREFERED" NUMBER(1,0)
    )

http://gpmfactory.com/wp-content/uploads/2019/04/pepg.html

Simon Arris Drag: https://simonsarris.com/making-html5-canvas-useful/

Drage & Drop: http://gpmfactory.com/wp-content/uploads/2019/04/drag2.html

Konvas: https://konvajs.org/docs/drag_and_drop/Drag_and_Drop.html

Drag & Drop: http://gpmfactory.com/wp-content/uploads/2019/04/konva.html

kdraw: http://gpmfactory.com/wp-content/uploads/2019/04/kdraw.html

Drag&Drop test1: http://jsfiddle.net/m1erickson/qm9Eb/

Drag3: http://gpmfactory.com/wp-content/uploads/2019/04/drag3.html

(https://stackoverflow.com/questions/24926028/drag-and-drop-multiple-objects-in-html5-canvas

Drag Codepen: https://codepen.io/alansarluv/pen/YXXbmr

Draggability: https://draggabilly.desandro.com/

W3School DragDrop: https://www.w3schools.com/htmL/tryit.asp?filename=tryhtml5_draganddrop

pepgs: http://gpmfactory.com/wp-content/uploads/2019/04/pepgs.html

Drag&Drop canvas : https://bl.ocks.org/lsbardel/b1133566e01d6d45fa7d11523dcb3e70

Drag me logo: https://codepen.io/tieppt/pen/GZPVjx

Simple D&G: https://codepen.io/sagarpatil/pen/LEZLav

park glich.me: https://park.glitch.me/

Documentation D&D: https://developer.mozilla.org/en-US/docs/Web/API/HTML_Drag_and_Drop_API

Canvas painter: https://projects.calebevans.me/painter/

jcanvas doc: https://projects.calebevans.me/jcanvas/docs/

Codpen drga: https://codepen.io/kanishkkunal/pen/YPPeoW/

Plugin for Oracle APEX

https://github.com/patrickmonaco/resource_diagram_plugin

An Oracle APEX plugin which displays data as a VIS timeline resource diagram cf http://visjs.org/docs/timeline/

« The VIS Timeline module is an interactive visualization chart to visualize data in time. The data items can take place on a single date, or have a start and end date (a range). You can freely move and zoom in the timeline by dragging and scrolling in the Timeline. Items can be created, edited, and deleted in the timeline. The time scale on the axis is adjusted automatically, and supports scales ranging from milliseconds to years.« 

Usage

Import this plugin In any page, add a new region based on « Resource Diagram plugin » Then, provide the following informations:

  • a regular query which must return:
    • resource name
    • start date (DATE or TIMESTAMP datatypes)
    • task name
    • End_date (optionnaly)
  • Group name column
  • Task name column
  • Start Date column

Edit Mode

If the edit mode is set, this gives opportunity to call a detail page. In this case, the plugin passes some contextual parameters and the target fields must be set:

  • Id field
  • Resource field
  • start date field
  • date format which is in use in the detail page (ie: MM-DD-YYYY HH24:MI:SS)

For the resource field, it’s possible to pass an internal column name instead the label of resource name.

After applying changes on the detail page and once the modal window is closed, the diagram is automatically refreshed in order to reflect the actual values from database.

Each time a user double clicks on a task or move it in an another lane or time slot, the plugin triggers the detail page (cf above). If a double click occurs in a blank area of the diagramm, a detail page in displayed with only the resource name filled.

Limitations

Cas d’usage

On récupère un tableau d’affectation de techniciens pour des interventions (installation, livraison, réparation) chez des clients. On désire obtenir une représentation graphique du planning des techniciens (considérés ici comme des ressources). Nous allons utiliser un chart de type Diagramme de Gantt proposé dans Oracle APEX et nous allons voir comment construire la requête .

Abstract: Oracle APEX – Gantt Chart with multiple taks per row

Échantillon du résultat qui sera obtenu


Diagramme Gantt de ressources

Structure de la table

la colonne NOM contient le nom du client.
TECHNICIEN contient le nom du technicien.

Échantillon du contenu de la table


Échantillon du contenu de la table

Query dans la section « Series »

L’objectif est de construire un tableau dont les lignes correspondent aux techniciens et les taches correspondent aux clients. On fabrique un id artificiel pour chaque technicien en calculant une valeur de hash et on combine (opérateur UNION) la liste des intervention avec celle des clients de façon à produire une arborescence de taches à un niveau. La fonction ORA_HASH appliquée au nom du technicien produit un ID utilisé comme Parent_id des interventions.

select * from 
(
select distinct technicien task_name,
ora_hash(technicien) task_id,
null parent_task,
null task_start_date,
null task_end_date,
0 status,
null gold,
(select min(debut) from atour) gantt_start_date,
(select max(debut + duree) from atour) gantt_end_date,
null viewport_start,
null view_port_end
from atour
where technicien is not null
UNION
select gold||'_' || nom task_name,
id task_id,
ora_hash(technicien) parent_task,
debut task_start_date,
(debut+duree) task_end_date,
0 status,
gold,
(select min(debut) from atour) gantt_start_date,
(select max(debut + duree) from atour) gantt_end_date,
(sysdate -10) viewport_start,
(sysdate + 10) view_port_end
from atour
where debut is not null
and technicien is not null
)
start with parent_task is null
connect by prior task_id = parent_task
order siblings by task_name

Code Javascript dans la section « Attributes » pour l’affichage des taches

Optionnellement, on peut aménager l’affichage pour produire des effets. Ici, on demande l’affichage d’une ligne rouge qui matérialise la journée en cours et on applique une couleur spécifique de tache pour les clients possédant un contrat de type « gold ». En raison de limitations des attributs passés dans le payload json, on concatène la valeur du champs GOLD avec celle du champs NOM (cf requete précédente) pour ensuite demander un filtrage u niveai Javascript. cf documentation Oracle Jet pour le composant Gantt.

function( options ){  
     options.dataFilter = function( data ) {     
var Mydata = data.rows;      
    Mydata.forEach(function(nrow, i) {    
           nrow["tasks"].forEach(function(nr, k) {
               if (String(nr.label).substring(0,1) == '1') {
                // data.rows[i].tasks[k].svgStyle     = {'fill': 'red'}; 
                 //  data.rows[i].tasks[k].svgStyle     = {'line': 'blue'}; 
                 data.rows[i].tasks[k].svgClassName = "u-color-20";
                //   data.rows[i].tasks[k].stack-label-style = {'fill': 'red'}; 
               }
            data.rows[i].tasks[k].label = String(nr.label).substring(2);
           });
    }); 
    return data;  
};  

var event = new Date();
// Define Reference Object line on the chart
var constantLine = [ { value: event.toISOString() } ];

// Set the referenceObjects option as part of the chart initialization
options.referenceObjects = constantLine;

 //options.gridlines.vertical ="visible";
return options;  

}

Le fonctionnement des sub Templates n’est pas autorisé par défaut dans le plugin BI Publisher pour Word. Il faut auparavant modifier une propriété dans le plugin:

Options / Preview / Properties

Disable external references ; Positionner à la valeur : false

(deux heures de vie perdues …)

L’import suit la syntaxe:

<?import:file:///D:\sample\mytemplate.rtf?>

 

Le thésaurus EUROVOC de la communauté européenne présente l’avantage d’être complet et disponible en de nombreuses langues. S’il est bien adapté à la recherche dans un corpus des débats parlementaires, il l’est moins, en revanche, dans d’autres domaines.

La version 4.7 a été mise en ligne le 15 décembre 2017.

Installation pour Oracle

Télécharger la version 4.7 pour le Français en format iso-2788 : eurovoc_fr.4.7

Déverrouiller le schéma CTXSYS

Alter user ctxsys account unlock;

Changer le password de CTXSYS

Alter user ctxsys identified by <PASSWORD> ;

Charger le thesaurus avec la commande en ligne ctxload. Ne pas oublier de positionner la variable NLS_LANG auparavant (essentiel dans le cas d’un thésaurus en Français, par exemple)

set NLS_LANG=French_France.UTF8 ctxload -user ctxsys/<PASSWORD> @hostname -thes -file C:\temp\eurovoc_fr.4.7.txt -name default

Pour supprimer un thesaurus existant, il faut utiliser l’une des commandes fournies dans le package CTX_THES.

begin
ctx_thes.drop_thesaurus('default');
end;

Annexes

La version ISO de ce thésaurus a été réalisée avec un traitement sql/Xml disponible dans le sgbd Oracle.

Les sources XML sont accessibles à l’adresse suivante: http://data.europa.eu/euodp/fr/data/dataset/eurovoc