Administration de base de données - blog.claude … · Réaliser un script qui permet de générer un fichier de requête SQL pour remplir la nouvelle base de données. ... [analyser

Fabien Poisson Claude Juston

Administration de base de données

Projet de rétro ingénierie d’une base de données

Master 1 STIC-ISRI

Juin 2016

Ce dossier, rédigé en première année de MASTER STIC mention ISRI, a

pour objectif principal d’expliquer notre démarche pour mener à bien la

réalisation du projet sur la rétro-ingénierie d’une base de données,

proposé dans l’unité d’enseignement administration de base de données.

[Tapez ici] Page 1 sur 16 Fabien POISSON

Claude JUSTON

Table des matières

Introduction : ................................................................................................................................................. 2

I-) Analyse du dump CSV.................................................................................................................................... 3

A- 1ière Approche ........................................................................................................................................ 3

B- L’étude approfondie .............................................................................................................................. 4

II-) Analyse du schéma relationnel .................................................................................................................... 5

A- Le choix des tables ................................................................................................................................. 5

B- Le schéma relationnel............................................................................................................................ 6

III-) Réalisation du Script .................................................................................................................................. 12

A- La réflexion sur l’écriture du script ...................................................................................................... 12

B- L’explication du code ........................................................................................................................... 13

C- Le résultat ............................................................................................................................................ 15


Claude JUSTON

Introduction :

Dans le cadre du Master STIC ISRI, nous avons une unité d’enseignement « Administration de base de données » afin de nous apprendre à gérer, exploiter et configurer des bases de données de différents types (MySQL, PostgreSQL etc…). Afin de mettre en avant nos connaissances apprises durant les cours, nous devons réaliser un projet de rétro-ingénierie qui consiste à passer d’une base données existante à une nouvelle base de données. Pour cela nous avons :

Un dump de la base de données (fichier plat au format .csv)

Une nouvelle base de données OpenConcerto sous PostgreSQL où l’on doit insérer les don-

nées

Et nous devons :

Réaliser un script qui permet de générer un fichier de requête SQL pour remplir la nouvelle

base de données.

Ce dossier a pour but d’expliquer les différentes démarches que nous avons réalisées afin de permettre la création du fichier de requête SQL. Celui-ci sera de composé de trois parties :

Analyse des données du dump de la base de données

Analyse du schéma relationnel de la base de données cible OpenConcerto

Réalisation du script permettant de générer le fichier des requêtes SQL


Claude JUSTON

I-) Analyse du dump CSV

A- 1ière Approche

La première approche pour le projet est d’analyser le dump de la base de données, il s’agit d’un fichier plat de type .csv (Comma-separated values). C’est-à-dire que les valeurs des colonnes sont séparées par un point-virgule (« ; »), nous utiliserons cette particularité pour séparer les différentes valeurs des colonnes dans le script. Quand l’on ouvre ce fichier à l’aide d’un tableur comme Microsoft Excel on peut y trouver 16 colonnes et 400 lignes. Avant de commencer l’analyse du dump, il fallait s’intéresser à la base de données cible. Nous savions que celle-ci est gérée par l’utilitaire Open Concerto, un ERP de gestion. La base de données d’OpenConcerto dispose d’un vaste schéma relationnel ce qui lui offre la possibilité de gérer un ensemble d’activités pour les entreprises :

Gestion commerciale

Comptabilité

Paye

Relation Client

Etc…

Afin de simplifier la recherche des tables cibles dans la base de données Open Concerto, nous avons cherché à savoir quelle activité correspondait le dump CSV de la base de données (« Etait-ce la gestion des payes, la comptabilité d’amortissement, etc… ? »). Avec les noms de colonnes (N° compte, Libellé Compte, Code Pointage et Code lettrage) nous avons déduis qu’il s’agissait d’un dump relatif au domaine de la compatibilité dite simple, c’est-à-dire des écritures comptables de bases comme le paiement d’une facture à un prestataire. Nous savons alors qu’il est requis d’avoir des connaissances en compatibilité afin de pouvoir placer les données dans les bons champs des tables fournies par OpenConcerto.


Claude JUSTON

B- L’étude approfondie

Nous avons étudié les différentes données des colonnes du dump, nous avons remarqué les points suivants à la première étude :

Les lignes sont regroupées par l’intermédiaire d’un ID (Colonne N° Document)

Certains caractères (comme les accents) n’ont pas le bon encodage :

Des valeurs n’ont pas le même formatage ou ne sont pas renseignées (données vides)

Dans le même temps, il a été important de se renseigner sur la base de données cible (OpenConcerto) pour pouvoir continuer l’étude approfondie du dump csv. Nous avons alors compris qu’un numéro de document regroupe plusieurs mouvements comptable (ici numéro ligne) et qu’une référence facture peut être référencée dans plusieurs numéros de document. Cette logique est élémentaire pour la réalisation du dump .sql. Concernant les données des mouvements comptables en eux-mêmes, nous avons dit qu’il s’agissait de comptabilité simple, cependant quelques lignes du dump CSV possèdent des valeurs particulières comme les lignes 22 et 23 où il s’agit de frais kilométriques. Ce genre « anomalies » correspondant à des valeurs peu communes doivent être mises en évidence et ciblées afin d’obtenir le meilleur compris lorsque l’on réalisera l’importation de ces données. Suite à cela, nous nous sommes alors accordés pour imposer certains paramètres lors de l’écriture de l’algorithme de transformation. En l’occurrence, certaines données/valeurs ne pourraient pas être complétement importées ou alors elles seront légèrement modifiées. Il est évident, que pour des raisons de temps (et d’argent dans le milieu professionnel) certains choix stratégiques sont à penser et fixer dès le début d’un projet (ici d’un projet de rétro-ingénierie). Ces choix seront détaillés tout au long du dossier, et nous argumenteront notre logique concernant les choix pour l’importation vers OpenConcerto. Enfin, pour approfondir l’analyse du dump et réaliser le script, nous sommes passés à l’analyse en parallèle du dump du schéma relationnel de la base de données Open Concerto.


Claude JUSTON

II-) Analyse du schéma relationnel

La deuxième partie d’analyse est celle du schéma relationnel de la base de données Open Concerto en parallèle du dump afin de pouvoir dégager les tendances et point fort lors de la conception de notre script pour générer les requêtes SQL. Cette partie est la plus importante car elle permet de déterminer quelles sont les relations entre les tables et quels champs doivent être renseignés pour y insérer les données du dump CSV.

A- Le choix des tables

Une base de données telle que peut l’être celle d’OpenConcerto (OC) est très complexe à étudier à cause de sa complexité relationnelle. En Effet, étudier un schéma relationnel de 111 tables ne peut pas se faire sans un minimum de méthodologie. Nous nous sommes alors concentrés sur une comparaison entre le dump CSV et la base de données d’OC. Après cette étude, nous avons retenus les tables essentielles pour réaliser cet import :

COMPTE_PCE

FOURNISSEUR

CLIENT

SALARIE

PIECE

MOUVEMENT

ECRITURE

JOURNAL

Cette sélection est motivée par le fait, encore une fois, que le dump .csv ne représente que des écritures comptables dites « simples ». Il n’est donc pas nécessaire d’utiliser plus de tables, car cette sélection de table supportera l’intégralité des données à importer.


Claude JUSTON

B- Le schéma relationnel

Nous sommes dans une base de données relationnelle avec des contraintes d’unicité garantissant l’intégrité des données et il est essentiel de respecter cela. Nous avons alors représenté ces liaisons par l’intermédiaire du logiciel SQL Power Architect. Chaque contrainte d’unicité a été étudiée pour comprendre ensuite comment intégrer les données correctement dans ce nouveau schéma relationnel. Voici l’étude des données (réalisée sous le logiciel SQL Manager Lite for PostgreSQL) concernant les écritures comptables : Ici les différentes données déjà présentes dans les tables utilisées

Figure 1 : Table ECRITURE

Figure 2: Table MOUVEMENT

Figure 3 : Table PIECE


Claude JUSTON

Figure 4 : Table NATURE_COMPTE

Figure 5 : Table JOURNAL


Claude JUSTON

Figure 6: COMPTE_PCE


Claude JUSTON

Et le schéma relationnel :


Claude JUSTON


Claude JUSTON

Nous ne rajoutons pas les données déjà présentes pour cette partie du schéma relationnel, mais le principe est le même. Dans l’optique de former au mieux nos requêtes d’insert, il est aussi essentiel d’étudier en plus les données par défaut des différents champs ainsi que leur type. Par exemple pour la table écriture :

Enfin, n’oublions pas de vérifier le type d’encodage de la base de données :

Il s’agit ici d’UTF-8 (très important pour la gestion des caractères accentués)


Claude JUSTON

III-) Réalisation du Script

L’objectif final est d’obtenir un fichier regroupant les requêtes SQL permettant l’importation du dump .CSV . Il y a une multitude de possibilité pour réaliser ce genre de fichier, cependant certaines solutions sont plus efficaces que d’autres. Nous avons opté pour l’utilisation du langage de programmation Python qui est d’une grande souplesse et d’une rapidité d’exécution qui font de lui un bon candidat pour ce genre de projet. De plus, Python prend en charge, par l’intermédiaire d’une librairie, le format de fichier .csv, ce qui nous permet d’écrire du code très simplement pour exploiter le fichier dump au format CSV.

A- La réflexion sur l’écriture du script

Il est intéressant de remarquer dans ce dump : Toutes les cellules (ligne à ligne) ne disposent pas forcement de données, par exemple certaines

cellules des colonnes fournisseur ou même client sont vides

Un numéro document regroupe plusieurs lignes

Les comptes tiers ne sont pas incrémentés, exemple : nous avons 401000 à la ligne 3, avec une pré-

cision à 3 zéro, alors qu’il serait intéressant d’avoir un 401001 pour identifier précisément le

compte de ce fournisseur

Les références factures ne sont pas toutes les mêmes

Des données de frais kilométrique aux lignes 22 et 23

Débit et crédit exprimés avec des nombres à virgules

Une référence facture peut être commune à plusieurs numéro de document

Des ‘ (quotes) sont présentes dans certaines données, il faut donc les doubler pour éviter des er-

reurs d’analyse syntaxique (parsing) par le compilateur

L’ordre dans lequel nous allons fournir les requêtes à PostgreSql est aussi déterminant pour respecter les contraintes relationnelles entre les tables. Concernant les requêtes d’insert en elles-mêmes et notre démarche de composition, nous avons écrit chacune dans leur écriture minimale, c’est-à-dire en prenant en compte que certains champs seront automatiquement complétés grâce à leur valeur par défaut. De plus, certaines liaisons doivent être respectées, mais avec des valeurs indéfinies, nous utilisons alors les données déjà présentent dans la BDD pour respecter ces contraintes de clé, c’est pour cela que de nombreux champs sont à 1 (ceux-ci ont été vérifié à chaque fois). L’utilisation de la puissance du SQL est très sollicité par l’intermédiaire des SELECT dans INSERT pour ainsi récupérer les données relationnelles insérées plus haut dans le fichier .sql . Les dates, sont quant à elles, soient mises à l’instant T (avec la fonction SQL NOW()), soient définies en utilisant les données du fichier .CSV . Enfin, pour chaque type de requête insert, nous avons testé une première fois la requête pour vérifier si le SGBDR l’acceptée sans problème.


Claude JUSTON

B- L’explication du code

A ce stade du dossier il est important d’avoir le code en parallèle, étant commenté, il sert aussi de

documentation explicative en complément de ce dossier.

Notre code commence par un import de la bibliothèque CSV. Nous fixons aussi un certain nombre de variable pour plus de flexibilité comme pour le nom de la bdd par exemple, ou alors le fichier d’entrée (dump csv) et celui de sortie (dump sql), etc… Le principe dans notre algorithme de création, est de remplir des « listes » (appellation Python) de données temporaires grâce à une lecture d’ensemble du fichier .csv puis par la suite, exploiter ces listes de données pour former correctement les requêtes sql. Cependant, ce processus (import vers la nouvelle base) se fait avec quelques pertes (de valeurs) dans le sens où il est trop couteux en temps (et dans le milieu professionnel : couteux en argent) d’avoir un résultat fidèle à 100% au dump CSV original. Nous pensons notamment :

Aux écritures liées au frais kilométrique que nous intégrerons comme des écritures « nor-

males »

Aux colonnes « Ref.paiement » et « code pointage » dont nous faisons complétement abstrac-

tion

Ces compromis sont nécessaires, mais n’entachent en rien un import d’une grande qualité tout en gardant l’intégrité de toutes les autres colonnes. Enfin, l’utilisation de certains champs pour les requêtes INSERT n’est peut-être pas en adéquation avec le fonctionnement voulu par OpenConcerto. Cependant, ces choix nous paraissent logiques dans une certaine mesure, et dans le cas où nous n’avons pas la possibilité de demander aux développeurs du logiciel les liaisons champs bdd <-> OC, nous avons dû fixer arbitrairement ces choix (à la différence du milieu professionnel où un échange aurait pu confirmer ou infirmer certains choix). L’important étant de respecter le type de données des différents champs. Logique du code :

Nous commençons alors par former les requêtes pour les comptes PCE, pour cela nous faisons

un distinguo (regroupement) des différents « comptes pce » qu’il peut y avoir dans le fichier.

Cette logique est à plusieurs reprises utilisée pour éviter d’avoir des doublons dans nos don-

nées de sortie.

Les fournisseurs sont ensuite traités, nous incrémentons un numéro à partir de 40100 (401

fournisseur, étude du plan comptable général) pour les rendre unique, et nous lions les tables

« compte_pce » et « fournisseur » grâce à ce numéro

La logique est la même pour les clients

Et les salariés (même si le numéro PCE ne sera pas valorisé par la suite, nous avons pensé qu’il

serait intéressant de le faire malgré tout)


Claude JUSTON

Comme nous avons dit, une pièce (ou facture) peut faire référence à plusieurs numéro de mou-

vement, quand il n’y a pas de numéro de facture, nous en créons un en taguant l’information

(IMPORT.id).

Cependant, cette logique de double appel est un peu compliqué à gérer pour la date de créa-

tion de la pièce, nous prenons alors la 1ière date.

Gestion des mouvements en faisant référence à la pièce insérée plus haut

Enfin les écritures, le plus gros du système. Rappel : Une pièce fait référence à plusieurs mou-

vements, et un mouvement fait référence à plusieurs écritures.

Nous commençons à rechercher la référence du « compte_pce » de l’écriture (est-ce un four-

nisseur, client, etc..), multiplication par 100 de la valeur numérique pour rentrer dans le type de

données en base (bigint), correspondance « ID_journal » (note : transformation de AC en HA) et

récupération de l’ID mouvement associé.


Claude JUSTON

C- Le résultat

En finalité, nous obtenons un fichier .sql d’environ 5700 lignes. Pour plus de lisibilité de ce dernier, nous avons organisé et commenté les différentes phases de requêtes SQL : Pour tester cette ensemble de requête, nous avons fait appel aux sessions (« BEGIN ; », ainsi en cas de problème, il a été facile pour nous de ne pas toucher directement la base de données par l’intermédiaire des « ROLLBACK ; ». Nous pouvons alors vérifier les données enregistrées en base par l’intermédiaire de cette requête :

SELECT P."NOM" AS P_NOM, P."CREATION_DATE" AS P_DATE, M."SOURCE" AS M_SOURCE,M."NUMERO" AS M_NUMERO, J."NOM" AS J_NOM,J."CODE" AS J_CODE, CP."NUMERO" CP_NUM,CP."INFOS" CP_INFO, E."DEBIT"/100 as DEBIT,E."CREDIT"/100 as CREDIT FROM "BDD"."OpenConcerto48"."ECRITURE" E, "BDD"."OpenConcerto48"."PIECE" P, "BDD"."OpenConcerto48"."MOUVEMENT" M, "BDD"."OpenConcerto48"."JOURNAL" J, "BDD"."OpenConcerto48"."COMPTE_PCE" CP WHERE M."ID"=E."ID_MOUVEMENT" AND P."ID"=M."ID_PIECE" AND J."ID"=E."ID_JOURNAL" AND CP."ID"=E."ID_COMPTE_PCE" --exclusion de la ligne par default AND M."NUMERO"<>0


Claude JUSTON

Nous obtenons bien 399 lignes en base, le même nombre de tuples que contenu dans le dump .CSV.

Documents

Administration de base de données - blog.claude … · Réaliser un script qui permet de générer un fichier de requête SQL pour remplir la nouvelle base de données. ... [analyser