Auto FormationDatabaseOracle Application Express

APEX SQL Workshop Initiation

Atelier APEX SQL

Prise en main de l'atelier APEX SQL pour gérer la structure de vos bases de données.

Cet article est un guide d'initiation pour la prise en main de l'atelier SQL d'Application Express. Le principal objectif est de créer les structures d'une base de données relationnelle à partir d'une feuille de calcul au format CSV.  Cette feuille de calcul regroupe dans un tableau les informations concernant le classement, en nombre d'exemplaires, des jeux vidéo vendus dans le monde (les informations ont été extraites depuis le site www.vgchartz.com).  Ce tableau va permettre à titre pédagogique, de construire les tables d'une base de données de démonstration et ensuite de développer un projet basé sur ce schéma de tables SQL afin de mettre en évidence les différentes fonctionnalités, la rapidité et l'efficacité du constructeur d'application d'APEX (App Builder) :

Introduction

Cette série d'articles vient en complément des tutoriels officiels que propose Oracle avec APEX, l'objectif étant de mettre en évidence différentes solutions alternatives et évolutives permettant une coopération entre les logiciels afin de réduire les coûts de développement et d'éviter le gaspillage de temps de manière considérable.

Que vous soyez étudiant, développeur, professionnel IT, décideur ou tout simplement curieux, à la recherche d'idées innovantes, ces articles vont vous permettre d'investir rapidement, à titre d'auto formation, la pratique d'utilisation du langage SQL, jusqu'à un niveau avancé, et le développement rapide d'applications que vous pourrez déployer en interne et/ou sur internet.

L'objectif ici est de vous permettre d'apprendre à utiliser le langage SQL par la pratique, notamment si vous êtes débutant, puis à consolider vos connaissances sur la conception des structures d'une base de données avec l'atelier SQL d'APEX, c'est à dire la création du schéma relationnel.

Pré requis

Pour suivre cet article, vous devez avoir un environnement APEX via un espace de travail (Workspace) opérationnel. Pour installer et créer votre espace de travail, si ce n'est déjà fait, reportez vous à APEX - Enregistrement & Workspace.

Téléchargez le fichier zip video_game_10000 sur votre ordinateur ou votre poste de travail, notamment si votre environnement APEX est sur apex.oracle.com. Vous pouvez également télécharger le fichier zip video_game_57496, plus volumineux, si vous avez un environnement APEX sur Oracle Cloud "Always Free" (attention ce fichier zip contient un fichier csv de 27 Mo qui ne pourra pas être chargé sur apex.oracle.com car l'espace de stockage du Workspace est limité).

Sites :  apex.oracle.com, tutorials, cloud free, apex+autonomous

Création de la table principale à partir du chargement d'un fichier texte

La table principale de votre base de données sera créée à partir du fichier games_10000.csv qui contient le classement des ventes de jeux vidéo dans le monde sous forme de tableau. Le fichier contient 10000 références dont le nombre d'exemplaires vendu pour chaque jeu depuis sa création. La première ligne du fichier est la description de chaque champ dans le tableau. Les champs sont séparés dans le fichier par le caractère "|" :

POSITION|IMAGE_GAME|GAME|CONSOLE|PUBLISHER|TOTAL_SHIPPED|TOTAL_SALES|RELEASE_DATE|LAST_UPDATE

Création d'une table à partir d'un fichier CSV

Le tutoriel qui est décrit ici est effectué dans un Workspace sur apex.oracle.com mais vous pouvez exécuter cet atelier dans n'importe quel autre environnement où votre Workspace a été installé.

1. Rendez vous sur apex.oracle.com et connectez vous à votre espace de travail

2. Cliquez sur « SQL Workshop », le menu principal de l'atelier SQL s'affiche. Cliquez sur « Utilitaires »

3. Cliquez sur « Atelier de données » puis sur « Charger les données ».

4. Après avoir extrait le fichier games_10000.csv du fichier games_10000.zip, glissez ou déposez le dans la fenêtre ou sélectionnez le fichier sur votre poste de travail en cliquant sur « Sélectionnez un fichier ».

Le format du fichier est automatiquement reconnu. Le caractère "|" de séparation des champs est détecté, la première ligne est utilisée pour identifier l'entête des colonnes de la table qui va être crée. Vous pouvez également choisir le type de clé primaire : gardez ce qui est sélectionné par défaut : « Colonne d'identité ».

5. Entrez "GAMES" comme nom de table, sélectionnez l'encodage de fichier « Europe de l'Ouest - Windows 1252 »  et cliquez sur « Configurer ».

Vous pouvez contrôler ici les types des colonnes et sélectionner celles que vous désirez charger dans la base de données. Entrez « N/A » dans le champ « Traiter comme NULL » et cliquez sur « Enregistrer les modifications ».

6. Cliquez sur « Charger les données », la table GAMES est créée et le chargement des données démarre.

7. Cliquez sur « Affichez la table », le navigateur d'objet s'ouvre.

La colonne ID a été créée automatiquement. C'est la clé primaire, elle est associée à une séquence pour l'incrémentation automatique. La colonne GAME est le nom du jeu vidéo et IMAGE_GAME contient du code HTML qui fait référence à l'image du jeu sur le site Vgchartz. CONSOLE est le type ou le nom de la console sur laquelle le jeu est diffusé. Cette colonne contient également du code HTML. PUBLISHER est le nom de l'éditeur du jeu vidéo. Les champs TOTAL_SHIPPED et TOTAL_SALES indiquent respectivement  le nombre d'exemplaires diffusés et le nombre d'exemplaire vendus depuis la date de sortie : RELEASE_DATE. La dernière colonne LAST_UPDATE est la date de dernière mise à jours.

Mis à part la colonne POSITION qui est de type NUMBER, les autres colonnes sont des VARCHAR2 (type alpha numérique). Il est donc nécessaire d'ajuster les types de certaines colonnes afin de pouvoir bénéficier de la puissance de traitement du langage SQL ultérieurement. Les colonnes RELEASE_DATE et LAST_UPDATE devraient être de type DATE tandis que les colonnes TOTAL_SHIPPED et TOTAL_SALES devraient être de type numérique (NUMBER).

Je vous propose donc comme exercice de style, de faire les modifications des types directement en SQL depuis l'atelier SQL Workshop.

Modifications des types de colonnes avec SQL Workshop

Les opérations qui suivent seront faites en mode ligne de commande SQL. Ouvrez la console en cliquant « Commandes SQL » depuis le menu principal de SQL Workshop :

La page affiche une aire dans laquelle vous pouvez entrer des requêtes SQL. Par exemple, entrez la requête suivante puis cliquez sur « Exécuter », le résultat de la requête s'affiche dans le cadre en dessous :

select count(*) from games

Vous allez donc entrer de cette manière toutes les requêtes SQL qui suivent pour convertir les types des colonnes de la table GAMES.

Création de la table de chargement intermédiaire LOAD_GAMES

La table GAMES va être copiée dans la table LOAD_GAMES avec une technique que l'on nomme CTAS (Create Table As Select). Les données et les types vont être convertis à la volée par cette technique :

create table LOAD_GAMES as 
select POSITION, IMAGE_GAME, GAME, CONSOLE, PUBLISHER, 
       TO_NUMBER(
          REGEXP_REPLACE( 
             REGEXP_REPLACE(TOTAL_SHIPPED, 'N/A', '0'), 
          '([0-9])m', '\1'), 
       '9999D99', ' NLS_NUMERIC_CHARACTERS = ''.,'' ') as TOTAL_SHIPPED,
       TO_NUMBER(
          REGEXP_REPLACE( 
             REGEXP_REPLACE(TOTAL_SALES, 'N/A', '0'), 
          '([0-9])m', '\1'), 
       '9999D99', ' NLS_NUMERIC_CHARACTERS = ''.,'' ') as TOTAL_SALES,
       TO_DATE(
          REGEXP_REPLACE(
             DECODE(RELEASE_DATE, '', '01 Jan 00', 'N/A', '01 Jan 00', RELEASE_DATE),
          '([0-9]{2})([A-Za-z]+)', '\1'), 
       'DD MON RR', 'NLS_DATE_LANGUAGE = American') as RELEASE_DATE,
       TO_DATE(
          REGEXP_REPLACE(
             DECODE(LAST_UPDATE, '', '01 Jan 00', 'N/A', '01 Jan 00', LAST_UPDATE),
          '([0-9]{2})([A-Za-z]+)', '\1'), 
       'DD MON RR', 'NLS_DATE_LANGUAGE = American') as LAST_UPDATE 
from GAMES

La requête fait appel à des fonctions de conversion de types : TO_NUMBER et TO_DATE, de tests DECODE et d'expressions régulière REGEXP_REPLACE :

- TO_NUMBER converti les colonnes TOTAL_SHIPPED et TOTAL_SALES selon le masque de conversion '9999D99' en précisant le format du séparateur décimal en fonction de la langue (soit un "." en Anglais, soit une "," en Français). Modifiez la syntaxe selon le format numérique de votre session APEX :
- Si votre environnement APEX est en Français : NLS_NUMERIC_CHARACTERS = ''.,''
- Si votre environnement APEX est en Anglais : NLS_NUMERIC_CHARACTERS = '',.''

- TO_DATE converti les colonnes RELEASE_DATE et LAST_UPDATE selon le masque de conversion 'DD MON RR' en précisant que les champs sont au format Americain, il est donc nécessaire de l'indiquer à la fonction  :

to_date(....., 'NLS_DATE_LANGUAGE = American')

- REGEXP_REPLACE modifie les champs TOTAL_SHIPPED et TOTAL_SALES en éliminant le "m" qui exprime les valeurs en millions, transforme éventuellement les champs vides ou les expressions "N/A" (Not Applicable) à la date 01/01/2000 et élimine les suffixes qui suivent les jours du mois comme par exemple, "1st -> 1", pour les colonnes RELEASE_DATE et LAST_UPDATE.

Entrez et exécutez la requête ci-dessus dans l'aire de la page Commande SQL :

La table résultante devrait être au bon format, ouvrez le navigateur d'objets et sélectionnez la table LOAD_GAMES pour le vérifier :

La table originale GAMES va être recréée vide avec le même format et les données vont être copiée ensuite depuis la table LOAD_GAMES.

L'opération consiste à supprimez la table GAMES pour la recréer vide ensuite plutôt que d'effectuer un CTAS car le code DDL (Data Definition Language) de création va recréer la colonne ID avec la fonction auto incrément :

Drop table games purge

Astuce: Pour récupérer la syntaxe de création d'une table, cliquez sur l'onglet SQL du navigateur d'objet après avoir sélectionné la table concernée :

Copiez et collez le code SQL dans un fichier texte. Modifiez le type des colonnes et copier ce texte SQL dans l'aire de saisie de la page Commandes SQL. Voici le code SQL résultant :

Création sur Oracle Cloud

CREATE TABLE "GAMES" 
 ( "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE, 
 "POSITION" NUMBER, 
 "IMAGE_GAME" VARCHAR2(4000) COLLATE "USING_NLS_COMP", 
 "GAME" VARCHAR2(255) COLLATE "USING_NLS_COMP", 
 "CONSOLE" VARCHAR2(255) COLLATE "USING_NLS_COMP", 
 "PUBLISHER" VARCHAR2(255) COLLATE "USING_NLS_COMP", 
 "TOTAL_SHIPPED" VARCHAR2(50) COLLATE "USING_NLS_COMP", 
 "TOTAL_SALES" VARCHAR2(50) COLLATE "USING_NLS_COMP", 
 "RELEASE_DATE" VARCHAR2(50) COLLATE "USING_NLS_COMP", 
 "LAST_UPDATE" VARCHAR2(50) COLLATE "USING_NLS_COMP", 
 PRIMARY KEY ("ID")
 USING INDEX ENABLE
 ) DEFAULT COLLATION "USING_NLS_COMP"
/
alter table "GAMES" modify
("RELEASE_DATE" DATE)
/
alter table "GAMES" modify
("LAST_UPDATE" DATE)
/
alter table "GAMES" modify
("TOTAL_SHIPPED" NUMBER)
/
alter table "GAMES" modify
("TOTAL_SALES" NUMBER)
/

Création sur apex.oracle.com

CREATE TABLE "GAMES" 
 ( "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE, 
 "POSITION" NUMBER, 
 "IMAGE_GAME" VARCHAR2(4000), 
 "GAME" VARCHAR2(255), 
 "CONSOLE" VARCHAR2(255), 
 "PUBLISHER" VARCHAR2(255), 
 "TOTAL_SHIPPED" NUMBER, 
 "TOTAL_SALES" NUMBER, 
 "RELEASE_DATE" DATE, 
 "LAST_UPDATE" DATE, 
 PRIMARY KEY ("ID")
 USING INDEX ENABLE
 )
/

Exécutez le code ci-dessus en fonction de votre environnement.

Il faut maintenant recopier les données dans l'autre sens. La requête suivante permet d'effectuer cette opération :

insert into GAMES (
 POSITION, 
 IMAGE_GAME, 
 GAME, 
 CONSOLE,
 PUBLISHER, 
 TOTAL_SHIPPED, 
 TOTAL_SALES,
 RELEASE_DATE,
 LAST_UPDATE)
select POSITION, 
 IMAGE_GAME, 
 GAME, 
 CONSOLE,
 PUBLISHER, 
 TOTAL_SHIPPED, 
 TOTAL_SALES,
 RELEASE_DATE,
 LAST_UPDATE
from LOAD_GAMES

Exécuter à nouveau ce code depuis la page Commandes SQL :

La table GAMES est maintenant au bon format. L'étape suivante consiste à normaliser le schéma de base de données.

Normalisation du schéma de base de données

Ouvrez à nouveau le navigateur d'objets (Objects Browser) depuis la page Atelier SQL (SQL Workshop) et sélectionnez la table GAMES.

En analysant le contenu de la table GAMES, les informations des colonnes CONSOLE et PUBLISHER sont répétées un grand nombre de fois. Cela induit une redondance d'information qui peut être pénalisante lorsque le volume de données augmente. Pour les puristes, en termes de modélisation, les attributs CONSOLE et PUBLISHER  ne dépendent pas fonctionnellement d'un jeu vidéo en particulier, la troisième forme normale (3NF) n'est pas respectée. Un type de console de jeu peut exister sans que le jeu existe et un éditeur de jeu vidéo peut exister sans avoir créé ou distribué aucun jeu. Il est donc nécessaire de déplacer ces informations dans des tables spécifiques. Le navigateur d'objets de l'atelier SQL permet facilement de normaliser cela.

Création de tables de consultation (lookup table)

Les informations de la colonne PUBLISHER vont être déplacées dans une table de consultation et cette colonne va être transformée en clé étrangère (foreign key). Pour créer cette table, sélectionnez la table GAMES avec le navigateur d'objets et cliquer sur « Créer une table de consultation »

Sélectionnez la colonne PUBLISHER avec le radio bouton

Cliquez sur « Suivant »

Entrez le nom de table PUBLISHERS et celui de la séquence (générateur de clé numérique unique) PUBLISHERS_SEQ. Cliquez sur « Suivant »

La table "lookup" PUBLISHERS va être créée avec deux colonnes : la clé primaire PUBLISHER_ID qui sera alimentée par la séquence PUBLISHERS_SEQ, et PUBLISHER qui contiendra le nom du jeu vidéo. Vous pouvez récupérer ou afficher le code SQL qui va être exécuté en cliquant sur « SQL» (en bas à gauche). Cliquez sur « Créer une table de consultation ».

Le navigateur d'objet affiche automatiquement les caractéristiques de la table PUBLISHERS. Cliquez sur l'onglet « Données », vous voyez le contenu de la table qui est la liste des éditeurs de jeux vidéo.

Création d'une table de consultation à plusieurs colonnes d'information

La colonne CONSOLE de la table GAMES contient le code HTML qui pointe vers l'image logo de chaque console de jeu sur le site www.vgchartz.com, par exemple :

https://www.vgchartz.com/images/consoles/Wii_b.png alt="Wii"

Il s'agit ici de la console Wii, le fichier image du logo sur le site vgchartz est Wii_b.png et le nom de la console est désigné via le TAG alt (alt="Wii"). L'idée ici est de créer une table de consultation qui contiendra le nom de la console et son logo sous forme d'image. Pour réaliser cela nous allons utiliser le code SQL qui va être généré par la fonction de création de table "lookup" du navigateur d'objet et modifier ce code pour créer les deux colonnes de la table de consultation des consoles de jeu.

Pour créer cette table, sélectionnez la table GAMES avec le navigateur d'objets et cliquer sur « Créer une table de consultation ». Sélectionnez la colonne CONSOLE avec le radio bouton.

Cliquez sur « Suivant »

Entrez le nom de la table : CONSOLE_GAMES et de la séquence : CONSOLE_GAMES_SEQ. Cliquez sur « Suivant ».

Cliquez sur « SQL » pour afficher le code qui devrait être exécuté. Copier ce code et coller le dans un fichier texte. Cliquez sur « Annuler ».

Attention : ne pas cliquer sur « Créer une table de consultation ».

Mettez en forme ce code SQL et modifiez le pour intégrer la création des deux colonnes de la table "lookup". Voici le code résultant :

create table "CONSOLE_GAMES"(
 "CONSOLE_ID" number not null primary key, 
 "CONSOLE" varchar2(4000) not null,
 "CONSOLE_IMG_HTML" varchar2(4000)
);

create sequence "CONSOLE_GAMES_SEQ";

create or replace trigger "T_CONSOLE_GAMES" 
before insert or update on "CONSOLE_GAMES" 
for each row 
begin 
 if inserting and :new."CONSOLE_ID" is null 
 then 
 for c1 in (select "CONSOLE_GAMES_SEQ".nextval nv from dual) 
 loop 
 :new."CONSOLE_ID" := c1.nv; 
 end loop; 
 end if; 
end;
/

insert into "CONSOLE_GAMES" ( "CONSOLE", "CONSOLE_IMG_HTML" ) 
select distinct REGEXP_REPLACE(REGEXP_REPLACE(CONSOLE,'https://www.vgchartz.com/images/consoles/(.*).png', '\1'), '^.*alt="(.*)"', '\1') CONSOLE, 
"CONSOLE" CONSOLE_IMG_HTML 
from "GAMES"
where "CONSOLE" is not null;

alter table "GAMES" add "CONSOLE2" number;

update "GAMES" x set "CONSOLE2" = (select "CONSOLE_ID" from "CONSOLE_GAMES" where "CONSOLE_IMG_HTML" = x."CONSOLE");

alter table "GAMES" drop column "CONSOLE";

alter table "GAMES" rename column "CONSOLE2" to "CONSOLE_ID";

alter table "GAMES" add foreign key ("CONSOLE_ID") references "CONSOLE_GAMES"("CONSOLE_ID");

La table CONSOLE_GAMES, la séquence CONSOLE_GAMES_SEQ et le déclencheur ("trigger") T_CONSOLE_GAMES qui va incrémenter automatiquement la clé primaire ID sont créés en premier lieu. La table est ensuite alimentée par la requête "insert select" en utilisant une double expression régulière (fonction REGEXP_REPLACE) pour renseigner la colonne CONSOLE avec uniquement le nom de la console :

l'expression 'https://www.vgchartz.com/images/consoles/(.*).png' est remplacée par uniquement ce qui est entre parenthèses et si la chaîne de caractère contient alt="(.*)", seulement ce qui est entre parenthèses sera conservé.

Les instructions qui suivent remplacent la colonne CONSOLE de la table GAMES par la clé étrangère (foreign key) CONSOLE_ID.

Copiez le texte SQL qui a été modifié et exécutez le, instruction par instruction dans la fenêtre de commandes SQL :

Ouvrez à nouveau le navigateur d'objet et affichez les caractéristiques de la table CONSOLE_GAMES. Cliquez sur l'onglet « Données », vous voyez le contenu de la table qui est la liste des console de jeux vidéo avec les deux colonnes d'information : CONSOLE et CONSOLE_IMG_HTML.

Au final le schéma de votre base de données est constitué de trois tables.

Nous allons maintenant pouvoir créer une application. Rendez vous sur la page APEX App Builder Initiation