Couche d'accès aux données

Cette section explique comment utiliser la couche d’accès aux données et les outils qui permettent d’effectuer des requêtes dans la base de données.

---

Présentation général

SAINet utilise un serveur de base de données relationnelle. En production c’est le plus souvent MariaDB qui est utilisée, et en développement c’est h2.

SAINet permet de faire cohabiter plusieurs sociétés (ou domaines) dans la même base de données.

Il est aussi possible de partager des données en base entre les différentes sociétés:

Chaque enregistrement peut ainsi être partagé entre les différentes sociétés, être commun à certaines ou visible par toutes (ALL).
Pour cela, en base, une colonne existe dans les différentes tables: DOMAINPK. Elle peut contenir une société, une liste de sociétés séparées par des virgules ou la valeur spéciale ALL.

Le répertoire server/database/schema contient les requêtes SQL de création de la base de données (un fichier par table).

Exemple de table: sysusr.sql dans server/database/schema/sysusr.sql

CREATE TABLE `sysusr` (
  `DOMAINPK` varchar(255) NOT NULL,
  `USERPK` varchar(200) NOT NULL,
  `ACCOUNTDOMAIN` varchar(100) DEFAULT NULL,
  `CATEGORYLIST` varchar(100) DEFAULT NULL,
  `GROUPLIST` varchar(1000) DEFAULT NULL,
  `FULLNAME` varchar(200) DEFAULT NULL,
  `ADDRESS` varchar(200) DEFAULT NULL,
  `PASSWORD` varchar(2000) DEFAULT NULL,
  `PWDDATE` datetime DEFAULT NULL,
  `LOGINERRORCOUNT` bigint(10) DEFAULT NULL,
...

Remarques:

• Le nom de la table en SQL doit toujours être en minuscule.
• Les noms de colonnes doivent toujours être en majuscules.
• Le schéma de la base de données est global (contrairement au paramétrage, le schéma est le même dans tous les déploiements SAINet de la même version).
• Les définitions SQL doivent:
  • se limiter à la structure de la table.
  • éviter les fonctionnalités propres à un SGBD en particulier.
• Les contraintes logiques (foreign keys) ne sont pas déclarées. La cohérence des données est assurée en code métier. (pour des raisons de performance et éviter des DELETE CASCADE).
• Il n’y a aucune vue, trigger ou autre procédure stockée.
• Les définitions des champs se limitent aux données de base (varchar, datetime, decimal, int, longtext, linestring).

A chaque table correspond une portion de XML définie dans un fichier TablesAndDatafields appelée Table Descriptor. Ces définitions contiennent des informations complémentaires sur la table et ses différents champs. Il permet de définir par exemple:

• des contraintes métiers sur un champ.
• la tâche à utiliser pour visualiser/éditer une table.
• des informations esthétiques (icône, libellé, …).

Le détail de ces informations peut être trouvé ici.

Exemple pour la table sysusr dans le fichier modules/global/config/TablesAndDataFields_SYS.xml

  <Table ID="SYSUSR" InternLabel="Comptes utilisateurs" Icon="Employe" ...>
    <DataFields>
      <DataField ID="SYSUSR.USER_PK" InternLabel="ID de l'utilisateur" InternDescription="L'id de l'utilisateur est unique, même à trvers tous les sites." Source="DataBaseDirect" Format="String" MaxLength="200" IsKey="true" ... />
      <DataField ID="SYSUSR.FULLNAME" InternLabel="Nom de l'utilisateur" Source="DataBaseDirect" Format="String" MaxLength="200" .../>
      <DataField ID="SYSUSR.AUTHNICKNAME" InternLabel="Nom alternatif" Source="DataBaseDirect" Format="String" MaxLength="200" ... />
      <DataField ID="SYSUSR.ACCOUNTDOMAIN" InternLabel="Société, site" InternDescription="Nom du 'domaine informatique' de l'utilisateur" Source="DataBaseDirect" Format="List" TableName="SYSDOM" GridView="SYS01_N1" ... />
      <DataField ID="SYSUSR.CATEGORYLIST" InternLabel="Catégorie(s)" InternDescription="Admin, User" Source="DataBaseDirect" Format="List" EnumName="USER_CATEGORIES" MaxLength="100" ... />
      ...

Une classe Java (Entity) est déclarée pour chaque table en base (sauf quelques exceptions). Elles sont définies dans le package saierp.core.business.entities et elles héritent (presque) toutes de BusinessEntity.

Exemple pour l’entity Sysusr:

package saierp.core.business.entities;
...
@Entity
@Table(name = "sysusr")
public class Sysusr extends AbstractSimpleTable<SysusrPK> {
    private static final Logger log = Logger.getLogger(Sysusr.class.getName());
    private static final long serialVersionUID = 1L;
    @EmbeddedId
    private SysusrPK sysusrPK;
    @Column(name = "ACCOUNTDOMAIN")
    private String accountdomain;
    @Column(name = "CATEGORYLIST")
    private String categorylist;
    @Column(name = "GROUPLIST")
    private String grouplist;
    @Column(name = "FULLNAME")
    private String fullname;
...

Exemple pour SysusrPK

package saierp.core.business.entities;
...
@Embeddable
public class SysusrPK implements Serializable, Cloneable, Shareable {
    @Basic(optional = false)
    @Column(name = "DOMAINPK", nullable = false)
    private String domainpk;
    @Basic(optional = false)
    @Column(name = "USERPK", nullable = false)
    private String userpk;

    public SysusrPK() {
    }

    public SysusrPK(String domainpk, String userpk) {
        this.domainpk = Constants.ALL_DOMAIN;
        this.userpk = userpk;
    }
...

La mise à jour du schéma de la base de données s’effectue automatiquement lors d’une mise à jour du serveur.

---

Framework DatabasePortal

Le framework utilisé pour accéder aux données est la classe DatabasePortal.

Cette classe permet de faire des requêtes en base de données soit via JPQL (Java Persistence Query Language) soit via du SQL natif.

De manière générale, le JPQL doit être utilisé, car c’est un langage de requête orienté objet indépendant de la plateforme. JPQL sert à exécuter des requêtes sur des enregistrements persistés en base de données mais en travaillant sur les entités Java correspondant aux tables plutôt que sur les tables elles-mêmes.

Le DatabasePortal permet de faire des requêtes pour récupérer directement des entités Java.

Exemple d’utilisation

List<Sysusr> accounts = databasePortal.getResultList(userSession,
                                       "SELECT s FROM Sysusr s", true);

Voici les principales méthodes qui permettent de travailler sur les entités Java:

List getResultList(UserSessionBean userSession, String ejbqlQuery, boolean addConstraints, Object... parameters)
<T> T getSingleResult(UserSessionBean userSession, String ejbqlQuery, boolean addConstraints, Object... parameters)
<T> T getSingleResultIfExists(UserSessionBean userSession, String ejbqlQuery, boolean addConstraints, Object... parameters)
int executeUpdate(UserSessionBean userSession, String ejbqlQuery, boolean addConstraints, Object... parameters)

Pour utiliser ces différentes méthodes, il est nécessaire de passer en paramètre le user en session ainsi qu’un booléen qui permet de définir si la requête doit être contrainte uniquement au domaine du user.

L’API sur les entités peut aussi être utilisée pour récupérer des objets simples (String, Bigdecimal, Date,…) ou une liste d’objets simples.

Exemple

// récupération du nom complet des users
List<String> userNames = databasePortal.getResultList(userSession, "SELECT s.fullname FROM Sysusr s", true);

// récupération du nom complet + langue locale
List<Sysusr> results = databasePortal.getResultList(userSession, "SELECT s.fullname, s.locale FROM Sysusr s", true);
for(Object[] result: results) {
    String fullName = (String) result[0];
    String locale = (String) result[1];
    ...
}

// récupération de l'entité
List<Sysusr> users = databasePortal.getResultList(userSession, "SELECT s FROM Sysusr s", true);
for(Sysusr user: users) {
    ...
}

En cas de problème de performance une des premières choses à faire et d’utiliser une projection pour éviter de récupérer toute l’entité. Cela peut déjà accélérer suffisamment le traitement pour éviter de devoir se pencher sur des requêtes en SQL natif.

Une autre astuce consiste à utiliser databasePortal.flushAndClear() pour éviter de garder les entités en mémoire, surtout lorsque le traitement n’en a plus besoin. Du coup, attention aux entités déjà récupérées qui doivent être modifiées après le clear car elles sont détachées, il faudra donc utiliser databasePortal.merge(entity).

Dans les différentes méthodes de requêtage, le paramètre addConstraints permet de demander l’ajout des contraintes automatiquement. Il existe deux contraintes:

• la contrainte sur domainpk, valable pour toutes les entités.
• la contrainte sur exerpk, valable pour les entités qui implémentent ExerciseHandler et qui ont donc le champ exerpk dans leur pk.

Lors du passage à ‘true’ du paramètre addConstraints une contrainte est automatiquement ajoutée sur la société. Elle permet de ne remonter que les entités appartenant à la société courante de l’utilisateur (ainsi que les entités partagées à tous les domaines).

        Long countWithConstraints = databasePortal.getSingleResult(userSession,
            "SELECT COUNT(immeuble) FROM Reglocdossier immeuble", true);
        

        Long count = databasePortal.getSingleResult(userSession,
            "SELECT COUNT(immeuble) FROM Reglocdossier immeuble", false);
        
        System.out.println("countWithConstraints:" + countWithConstraints);
        System.out.println("count:" + count);

        // countWithConstraints:248
        // count:5094

La valeur utilisée est généralement envoyée par le client sous la forme d’une session-var nommée CGE_EXERCICE (l’exercice peut être sélectionné en CGE99). Pour certaines tâches (STO30), elle est inférée depuis la date d’écriture (cf attribut KeepInSessionVar).

Il est recommandé de toujours utiliser des requêtes avec des paramêtres. Pour cela il suffit d’utiliser le ? suivie d’un chiffre qui correspont au numéro du paramètre passé à l’execution de la requête.

Exemple:

List<Sysusr> accountsFR = databasePortal.getResultList(userSession,
                                       "SELECT s FROM Sysusr s WHERE s.locale = ?1", true, "FR");

List<Sysusr> activeAccountsFR = databasePortal.getResultList(userSession,
                                       "SELECT s FROM Sysusr s WHERE s.locale = ?1 AND active = ?2", true, "FR", 1);

La partie générique de SAINet repose sur une structure d’objet Dossier / Page.

Dans une tâche, il existe des méthodes utilitaires permettant de récupérer les Dossiers / Pages et la valeur d’un champ dans une page.

Exemple:

public class Xxxdossier extends AbstractDossierTable<Regimmfielddata> {
...
}

public class TacheEx1 extends BusinessTask {
...
        // recuperation du dossier
        Dossier xxxDossier = getDossier(dossierEntity); // a partir de l entity
        Dossier<Xxxdossier> xxxDossier = getDossier(Xxxdossier.class, xxId); // a partir de l id de l objet

        // recuperation d'une ou plusieurs page du dossier
        List<DossierPage> xxxPages = xxxDossier.getPages("PAGE_ID");
        DossierPage currentPage = xxxDossier.getActivePageAt("PAGE_ID", now);

        // recuperation d'un champ dans la page
        String fieldValue = currentPage.getValue("FIELD_NAME");
...
}

Il est aussi possible de faire des requêtes en utilisant du SQL.

Voici les principales méthodes qui permettent de travailler avec du SQL natif:

List executeNativeQuery(String queryStr, Object... parameters)
public int executeNativeUpdate(String queryStr, Object... parameters

Exemple d’utilisation

String query = "SELECT NOCPTEPK,NOFINAREPF,NOFINAREPD FROM cgecfi "
             + "WHERE NOFINAREPF IS NOT NULL AND NOFINAREPF <> '' AND DOMAINPK = '"+soc+"'";
List<Object[]> results = databasePortal.executeNativeQuery(query);
for(Object[] result: results) {
    String fullName = (String) result[0];
    String locale = (String) result[1];
    ...
}

---

Outil de développement SQL

L’AdminClient (dans sainet/tools) a un onglet qui permet d’effectuer des requêtes sur la base de données d’un serveur SAINet déjà démarré.

Il est possible d’effectuer des requêtes SQL. Pour cela il faut faire précéder la requête par le mot clef JDBC ou NATIVE.

Il est aussi possible d’effectuer des requêtes JPQL dans l’admin client.

La commande SQL SHOW COLUMNS permet de voir la structure d’une table (fonctionne pour MariaDB et H2):

JDBC SHOW COLUMNS FROM SYSUSR