Schéma de base de données¶

Introduction¶

Lorsqu'une application Ewt utilise un modèle de persistence basé sur une base de données, le schéma de cette dernière doit être décrit au moyen d'un fichier schema.xml.

Ce dernier doit décrire les tables et les colonnes de la base de données. Il est attendu que les clés primaires (primary key) et les clés distantes (foreign keys) y soient indiquées.

Clés primaires et clés distantes

Les clés primaires et les clés distantes ne peuvent être constituées que d'une seule colonne. Le moteur ne gère pas les clés composites.

Structure¶

Le fichier schema.xml permet de décrire la structure des données en base de données. La structure est très simple:

Un élément table permet de décrire une table de la base de données. Cet élément attend un attribut name qui indique le nom de la table.

Le moteur autorise de déclarer les tables d'infrastructure dans le fichier schema.xml. Ces dernières doivent alors indiquer un attribut infrastructure="true" pour que le moteur puisse les distinguer des autres.
Un élément column permet de décrire une colonne de table de la base de données. Cet élément peut avoir les attributs suivants:

name

Nom de la colonne

type

Type de la colonne. Les types supportés sont décrits dans le chapitre Types de champs

pk (ou PK, isPrimaryKey, primaryKey)

Cet attribut permet d'indiquer que la colonne fait partie de la clé primaire. La clé primaire peut être constituée d'une ou deux colonnes selon la nature du groupe auquel est rattachée la table. Un groupe single possédera une seule colonne dans sa clé primaire (selon les cas, il pourra s'agir de la colonne liée au mainfield ou au reffield). Un groupe multi pourra quant à lui posséder une clé primaire basée sur une (mainfield) ou deux colonnes (mainfield et reffield) selon la structure souhaitée pour sa table.

La colonne est considérée comme clé primaire si l'attribut vaut true, yes ou 1.

fk (ou FK, foreignReference, foreignRef, foreignKey)

Cet attribut indique que la colonne est une référence vers une colonne d'une autre table. La valeur de l'attribut peut être notée selon la forme TABLE.COLONNE ou plus simplement TABLE si cette dernière possède une clé primaire utilisant une colonne unique.

auto (ou autoIncrement, autoincrement)

Flag true/false indiquant que la colonne est auto-incrémentée au niveau de la base de données. Lorsqu'une colonne est marquée comme auto-incrémentée, le moteur ne cherchera pas à générer de valeur dans les requêtes de création de tuples.

À noter que certains pré-requis peuvent varier selon le SGBD. Par sur Postgresql, une colonne auto-incrémentée devra être du type serial ou bigserial. Sur SQLite, seule une colonne primary key peut être auto-increment.

Types de champs¶

L'attribut type permet de définir le type de colonne. Les types reconnus sont:

auto : uniquement utilisable sur les colonnes "primary key" et "foreign key"; dans ce cas, le type est déterminé en fonction du type par défaut de primary key (selon la propriété admin.tupleIdForm du fichier de configuration)
bit
boolean
tinyint
smallint
serial : spécifique à PostgreSQL
int ou integer
bigserial : spécifique à PostgreSQL
bigint : représente un long
float
real
double
numeric(#,#)
decimal(#,#) : représente un nombre décimal
char
varchar(#) ou varchar2(#): représente une chaîne de caractères
longvarchar
nchar
nvarchar
nlongvarchar
date : représente une date yyyy-MM-dd
time : représente une heure hh:mm:ss
timestamp : représente une date/heure yyyy-MM-dd hh-mm-ss.lll
clob : représente un texte long
nclob : représente un texte long
blob : représente un champ binaire (voir chapitre "PostgreSQL et les blobs" ci-dessous)
bin, binary ou bytea : représente un tableau de bytes (voir chapitre "PostgreSQL et les blobs" ci-dessous)

Les types suivants sont reconnus, mais non exploitables dans la version actuelle du moteur.

varbinary
longvarbinary
null
distinct
struct
array
ref
datalink
rowid
sqlxml
time_with_timezone
timestamp_with_timezone

Gestion des identifiants¶

Ewt gère automatiquement les identifiants de tuples (et donc de dossiers). Il applique globalement la logique suivante:

Les colonnes auto-incrémentées sont gérées par le SGBD directement. C'est donc ce dernier qui est responsable de générer l'identifiant. Notons que Ewt considère qu'une clé auto-incrémentée doit être de type numérique. Par conséquent le format d'id doit être int ou bigint. Le moteur émettra un avertissement si une colonne est déclarée comme auto-incrémentée alors qu'elle n'est pas d'un type approprié au stockage de valeurs int ou bigint.
Dans le cas de colonnes non auto-incrémentées, Ewt se charge de générer l'identifiant. On a deux cas de figure:
- Si la clé primaire est constituée d'une seule colonne, le moteur générera un identifiant unique sur la table.
- Les colonnes constituant une clé primaire et utilisant un format uuid ou snowflake sont générés de façon à être uniques sur la table.
- Les colonnes constituant une clé primaire non auto-incrémentée et utilisant un format numérique (int ou bigint) sont alimentées par Ewt au moyen d'une sous-requête. La sous-requête varie selon le nombre de colonnes qui constituent la clé primaire:
  - si la clé primaire est constituée d'une seule colonne, la sous-requête aura la forme suivante:
```
(SELECT COALESCE(MAX(sub.IdColumn),0)+1 FROM Table sub)
```
    - si la clé primaire est constituée de deux colonnes (mainfield et reffield), alors la sous-requête aura la forme suivante:
```
(SELECT COALESCE(MAX(sub.IdColumn),0)+1
 FROM Table sub 
 WHERE sub.RefField='REFFIELD')
```
    où REFFIELD est la valeur du reffield, c'est-à-dire l'identifiant de dossier.

Revenons sur cela au travers de quelques exemples. On considère que le format d'identifiant de tuple par défaut est uuid. Ici on va s'intéresser uniquement au cas d'une table associée à un groupe multi. Dans les exemples ci-dessous, la colonne IdCommentaire est le mainfield et la colonne IdTicket sert à faire la jointure avec la table principale, c'est donc le reffield.

Cas 1: clé primaire construite sur les deux colonnes (mainfield et reffield) non auto-incrémentées
```
<table name="TicketCommentaire">
    <column name="IdCommentaire" type="auto" pk="true"/>
    <column name="IdTicket" type="auto" pk="true" fk="Ticket.IdTicket"/>
    ...
</table>
```
Les tuples "IdCommentaire" générés pour cette table seront des uuid. Les uuid sont toujours uniques sur la table.
Cas 2: clé primaire construite sur les deux colonnes (mainfield et reffield), avec mainfield déclarée comme auto-incrémentée. Son type est bigint pour supporter l'auto-incrément et le champ correspondant (dans le descript.xml est déclaré avec l'attribut idform="bigint" pour indiquer au moteur qu'on souhaite utiliser un autre format que le format de base du config.xml)
```
<table name="TicketCommentaire">
    <column name="IdCommentaire" type="bigint" pk="true" auto="true"/>
    <column name="IdTicket" type="auto" pk="true" fk="Ticket.IdTicket"/>
    ...
</table>
```
Dans cette configuration, c'est le SGBD qui génère l'identifiant de mainfield. Les identifiants seront donc uniques sur la table. Un premier dossier recevra par exemple les valeurs 1 et 2, un second dossier recevra ensuite 3 et 4, etc.
Cas 3: clé primaire construite sur les deux colonnes (mainfield et reffield), avec mainfield déclarée avec un type bigint mais non auto-incrémentée.
```
<table name="TicketCommentaire">
    <column name="IdCommentaire" type="bigint" pk="true"/>
    <column name="IdTicket" type="auto" pk="true" fk="Ticket.IdTicket"/>
    ...
</table>
```
Le mainfield est généré par Ewt de façon à ce que chaque dossier commence la numérotation de ce champ à 1.
Cas 4: clé primaire constituée uniquement de mainfield, de type bigint mais non auto-incrémenté
```
<table name="TicketCommentaire">
    <column name="IdCommentaire" type="bigint" pk="true"/>
    <column name="IdTicket" type="auto" fk="Ticket.IdTicket"/>
    ...
</table>
```
Les valeurs de mainfield sont, comme dans le cas 2, uniques sur la table étant donné qu'ils doivent à eux seuls permettre l'identification des tuples. La seule différence par rapport aux cas 2 vient du fait qu'ici la valeur n'est pas calculée par le SGBD, mais par Ewt.
Cas 5: table sans clé primaire
```
<table name="TicketCommentaire">
    <column name="IdCommentaire" type="bigint"/>
    <column name="IdTicket" type="auto" fk="Ticket.IdTicket"/>
    ...
</table>
```
Dans ce cas, le moteur applique la même logique que dans le cas 3 : il considére que le tuple est identifié de façon unique au moyen de la paire mainfield/reffield et commence la numérotation du mainfield à 1 pour chaque dossier.

La création de tables sans clé primaire n'est toutefois pas recommandée car on perd le bénéfice des contrôles automatiques effectués par le SGBD concernant l'unicité de la clé primaire, ce qui peut représenter un danger en termes de qualité des données.

On peut donc voir que la façon de constituer la clé primaire joue un rôle sur la valeur des identifiants. Pour faire simple, le moteur s'arrange pour que les identifiants de lignes d'une table de groupe multi soient numérotés à partir de 1 pour chaque dossier

Exemple¶

<?xml version="1.0" encoding="UTF-8"?>
<schema>
    <!-- Exemple de table prévue pour un groupe "single". -->
    <table name="Client">
        <column name="IdClient" type="auto" pk="true"/>
        <column name="IdStatut" type="varchar(20)" fk="Statut.IdStatut"/>
        <column name="Code" type="varchar(10)"/>
        <column name="Nom" type="varchar(100)"/>
        <column name="Adresse" type="varchar(500)"/>
        <column name="Remarque" type="clob"/>
    </table>

    <!-- Exemple de table prévue pour un groupe "single".
         Ici la clé est déclarée en auto-incrémenté (ici de type "serial" 
         car le SGBD est Postgresql) -->
    <table name="Application">
        <column name="IdApplication" type="serial" pk="true" auto="true"/>
        <column name="IdClient" type="auto" fk="Client.IdClient"/>
        <column name="Nom" type="varchar(150)"/>
        <column name="NomCourt" type="varchar(50)"/>
    </table>

    <!-- Exemple de table prévue pour un groupe "multi".
         La clé primaire est constituée de la colonne IdClientAutorisation 
         qui est auto-incrémenté par le SGBD. -->
    <table name="ClientAutorisation">
        <column name="IdClientAutorisation" type="serial" pk="true" auto="true"/>
        <column name="IdClient" type="auto" fk="Client.IdClient"/>
        <column name="IdGroupe" type="auto" fk="ListeGroupe.IdGroupe"/>
    </table>

    <table name="ClientService">
        <column name="IdClientService" type="serial" pk="true" auto="true"/>
        <column name="IdClient" type="auto" pk="true" fk="Client.IdClient"/>
        <column name="Date" type="date"/>
        <column name="Description" type="varchar(500)"/>
        <column name="Remarque" type="clob"/>
        <column name="Heures" type="decimal(12,4)"/>
    </table>
</schema>

PostgreSQL et les blobs¶

Ewt supporte les deux méthodes de stockage de données binaires que propose PostgreSQL, à savoir:

bytea : Comme son nom l'indique, ce type représente un tableau de bytes. Les données binaires sont donc exprimées en bytes et placées sous la forme de tableau dans la DB, un peu comme les chaînes de caractères qui sont stockés sous la forme d'un tableau de caractères.
oid : Ce type est spécifique à PostgreSQL et permet le stockage de large objects. PostgreSQL utilise une structure spéciale pour ces objets (appelée pg_largeobject). Cela revient un peu comme de stocker les données binaires dans une GED et de conserver dans la DB un ID de l'entrée GED en question.

PostgreSQL recommande l'usage du type oid car il est plus performant que bytea. Attention toutefois, il y a un léger prix à payer en termes de maintenance avec l'utilisation du type oid. En effet, PostgreSQL ne supprime pas automatiquement les données large objects lorsque celles-ci ne sont plus référencées. Cela signifie que si on efface la valeur d'un champ oid, la référence est bien effacée, mais l'objet référencé ne l'est pas. PostgreSQL fournit cependant un outil pour effectuer un nettoyage des large objects qui ne sont pas référencés. Au niveau du serveur, on pourra effectuer ce nettoyage au moyen de la commande suivante:

vacuumlo -U username -W -v nom_database

où username désigne ici le nom d'utilisateur pour se connecter à la base de données nom_database.