Implémentations du moteur SQL de 4D

Fondamentalement, le moteur SQL de 4D est conforme à la norme SQL92. Cela signifie que, pour une description détaillée des commandes, fonctions, opérateurs et syntaxes à utiliser, vous pouvez vous référer à n’importe quelle documentation sur le SQL92. De multiples ressources sur ce thème sont disponibles, par exemple, sur Internet.

Cependant, le moteur SQL de 4D ne prend pas en charge 100 % des fonctions du SQL92 et propose en outre des fonctions supplémentaires, non présentes dans cette norme.

Ce paragraphe décrit les principales implémentations et limitations du moteur SQL de 4D.

Le moteur SQL de 4D étant intégré au coeur de la base de données de 4D, les limitations relatives au nombre maximum de tables, de colonnes (champs) et d’enregistrements par base ainsi que les règles de nommage des tables et des colonnes sont identiques à celles du moteur standard de 4D. Elles sont listées ci-dessous :

• Nombre maximum de tables par base : deux milliards en théorie, mais limitation à 32767 pour des raisons de compatibilité avec 4D.
• Nombre maximum de colonnes (champs) par table : deux milliards en théorie, mais limitation à 32767 pour des raisons de compatibilité avec 4D.
• Nombre maximum de lignes (enregistrements) par table : un milliard.
• Nombre maximum de clés d’index : 128 milliards pour les types alpha, texte et float ; 256 milliards pour les autres types (scalaires).
• Une clé primaire ne peut pas être une valeur NULL et doit être unique. Il n’est pas obligatoire d’indexer les colonnes (champs) clés primaires.
• Nombre maximum de caractères pour les noms de tables et de champs : 31 caractères (limitation de 4D).
• Il n’est pas possible de créer plusieurs tables avec le même nom. Les mécanismes de contrôle standard de 4D sont appliqués.

Le tableau suivant indique les types de données pris en charge dans le SQL de 4D ainsi que leur type correspondant dans 4D :

La conversion entre les types de données numériques est automatique. Les chaînes qui représentent un nombre ne sont pas converties en valeur numérique. L’opérateur de transtypage CAST permet de convertir des valeurs d’un type en un autre.
Les types de données SQL suivants ne sont pas implémentés :

• NCHAR
• NCHAR VARYING

La valeur NULL est implémentée dans le langage SQL de 4D ainsi que dans le moteur de base de données de 4D. En revanche, cette valeur n’existe pas dans le langage de 4D. Il est toutefois possible de lire et d’écrire la valeur NULL dans un champ 4D via les commandes Is field value Null et SET FIELD VALUE NULL.

Pour des raisons de compatibilité dans 4D, les valeurs NULL stockées dans les tables des bases de données 4D sont automatiquement converties en valeurs par défaut lors des manipulations effectuées via le langage de 4D. Par exemple, dans le cas de l’instruction suivante :

... si le champ MonchpAlpha contient la valeur NULL, la variable mavarAlpha contiendra “” (chaîne vide).

Les valeurs par défaut dépendent du type de données :

• Pour les types Alpha et Texte : “”
• Pour les types Réel, Entier et Entier long : 0
• Pour le type Date : “00/00/00”
• Pour le type Heure : “00:00:00”
• Pour le type Booléen : Faux
• Pour le type Image : Image vide
• Pour le type BLOB : BLOB vide

En revanche, ce mécanisme ne s’applique pas en principe aux traitements effectués au niveau du moteur de la base de données 4D, tels que les recherches. En effet, la recherche d’une valeur “vide” (par exemple mavaleur=0) ne trouvera pas les enregistrements stockant la valeur NULL, et inversement. Lorsque les deux types de valeurs (valeurs par défaut et NULL) cohabitent dans les enregistrements pour un même champ, certains traitements peuvent être faussés ou nécessiter du code supplémentaire.
Pour éviter ces désagréments, une option permet d’uniformiser tous les traitements dans le langage de 4D : Traduire les NULL en valeurs vides. Cette option, accessible dans l’Inspecteur des champs de l’éditeur de structure, permet d’étendre le principe d’usage des valeurs par défaut à tous les traitements. Les champs contenant la valeur NULL seront systématiquement considérés comme contenant la valeur par défaut. Cette option est cochée par défaut.

La propriété Traduire les NULL en valeurs vides est prise en compte à un niveau très bas du moteur de la base de données. Elle agit notamment sur la routine Is field value Null.

La propriété de champ Refuser l’écriture de la valeur NULL permet d’interdire le stockage de la valeur NULL :

Lorsque cet attribut est coché pour un champ, il ne sera pas possible de stocker la valeur NULL dans ce champ. Cette propriété de bas niveau correspond précisément à l’attribut NOT NULL du SQL.
De manière générale, si vous souhaitez pouvoir utiliser des valeurs NULL dans votre base de données 4D, il est conseillé d’utiliser exclusivement le langage SQL de 4D.

Note: Dans 4D, les champs peuvent également avoir l’attribut “Obligatoire”. Les deux notions sont proches mais leur portée est différente : l’attribut “Obligatoire” est un contrôle de saisie, tandis que l’attribut “Refuser l’écriture de la valeur NULL” agit au niveau du moteur de la base de données.

Si un champ disposant de cet attribut reçoit la valeur NULL, une erreur est générée.

Le serveur SQL de 4D prend en charge les constantes date et heure conformément à l’API ODBC. La syntaxe des séquences de constantes date et heure ODBC est la suivante :

Note : fff indique des millisecondes.

Par exemple, vous pouvez utiliser les constantes suivantes :

L'analyseur SQL de date rejette toute expression date spécifiant "0" comme numéro de jour ou de mois. Les expressions telles que {d'0000-00-00'} ou CAST('0000-00-00' AS TIMESTAMP) génèrent une erreur. Pour effectuer en SQL des recherches sur des dates vides (à ne pas confondre avec des dates Null), vous devez utiliser une expression 4D intermédiaire. Par exemple :

La propriété "Disponible via SQL", disponible pour les méthodes projet, permet de contrôler l'exécution des méthodes projet de 4D via le SQL.

Lorsqu’elle est cochée, cette option autorise l’exécution de la méthode projet par le moteur SQL de 4D. Elle est désélectionnée par défaut, ce qui signifie que, sauf autorisation explicite, les méthodes projet de 4D sont protégées et peuvent pas être appelées par le moteur SQL de 4D.
Cette propriété s’applique à toutes les requêtes SQL internes et externes — exécutées via le driver ODBC, le code SQL inséré dans les balises Begin SQL/End SQL ou la commande QUERY BY SQL.

Notes :

• Même si une méthode dispose de l’attribut “Disponible via SQL”, les accès définis au niveau des Propriétés de la base et des propriétés de la méthode sont pris en compte pour l’exécution de la méthode.
• La fonction ODBC SQLProcedure retourne uniquement les méthodes projet disposant de l’attribut “Disponible via SQL”.

• Transactions Auto-commit : cette option permet d’activer le mécanisme d’auto-commit dans le moteur SQL. Le mode auto-commit a pour but de préserver l’intégrité référentielle des données. Lorsque cette option est cochée, toute requête SELECT, INSERT, UPDATE et DELETE (SIUD) effectuée en-dehors d’une transaction est automatiquement incluse dans une transaction ad hoc. Ce principe garantit que les requêtes seront entièrement exécutées ou, en cas d’erreur, intégralement annulées.
  Les requêtes incluses dans une transaction (gestion personnalisée de l’intégrité référentielle) ne sont pas affectées par cette option.
  Lorsque cette option n’est pas cochée, aucune transaction automatique n’est générée (à l’exception des requêtes SELECT... FOR UPDATE, reportez-vous à la description de la commande SELECT). Par défaut, cette option n’est pas cochée.
  Vous pouvez également gérer cette option par programmation à l’aide de la commande SET DATABASE PARAMETER.

Note : Seules les bases locales interrogées par le moteur SQL de 4D sont affectées par ce paramètre. Dans le cas de connexions externes à d'autres bases SQL, le mécanisme d’auto-commit est pris en charge par les moteurs SQL distants.

• Tenir compte de la casse des caractères pour les comparaisons de chaînes : cette option permet de modifier le mode de prise en compte de la casse des caractères dans les requêtes SQL. Elle est cochée par défaut, ce qui signifie que le moteur SQL établit une différence entre les majuscules et les minuscules ainsi qu'entre les caractères accentués lors des comparaisons de chaînes (tris et recherches). Par exemple "ABC"="ABC" mais "ABC" # "Abc" et "abc" # "âbc".
  Dans certains cas, par exemple pour aligner le fonctionnement du moteur SQL sur celui du moteur 4D, vous pourrez souhaiter que les comparaisons de chaînes ne tiennent pas compte de la casse ("ABC"="Abc"="âbc"). Pour cela, il suffit de désélectionner l’option.
  Vous pouvez également gérer cette option par programmation à l’aide de la commande SET DATABASE PARAMETER.

4D implémente le concept de schémas. Un schéma est un objet virtuel contenant des tables de la base. Dans le SQL, le concept de schémas a pour but de permettre l’attribution de droits d’accès spécifiques à des ensembles d’objets de la base de données. Les schémas découpent la base en entités indépendantes dont l’assemblage représente la base entière. Autrement dit, une table appartient toujours à un et un seul schéma.

• Pour créer un schéma, vous devez utiliser la commande CREATE SCHEMA. Vous utilisez ensuite les commandes GRANT et REVOKE pour configurer les types d'accès des schémas.
• Pour associer une table à un schéma, vous pouvez appeler les commandes CREATE TABLE ou ALTER TABLE. Vous pouvez également utiliser le pop up menu "Schémas" de la Palette Inspecteur de l'éditeur de Structure de 4D, qui liste tous les schémas définis dans la base.
• La commande DROP SCHEMA permet de supprimer un schéma.

Note : Le contrôle des accès via les schémas s’applique uniquement aux connexions depuis l’extérieur. Le code SQL exécuté à l’intérieur de 4D via les balises Debut SQL/Fin SQL, SQL EXECUTER, CHERCHER PAR SQL... dispose toujours d’un accès complet.

L’architecture multi-bases est implémentée au niveau du serveur SQL de 4D. Depuis 4D, il est possible :

• de se connecter à une base existante à l’aide de la commande SQL LOGIN.
• de passer d’une base à l’autre en utilisant les commandes 4D SQL LOGIN et SQL LOGOUT.
• d'ouvrir et d'utiliser une autre base 4D que la base courante à l’aide de la commande USE DATABASE.

Dans le langage SQL, la clé primaire permet d’identifier dans une table la ou les colonnes (champs) chargée(s) de désigner de façon unique les enregistrements (lignes). La définition d’une clé primaire est notamment indispensable à la fonction de réplication des enregistrements d’une table de 4D (cf. section Réplication via le SQL) et à la journalisation des tables 4D à compter de la v14.

4D vous permet de gérer la clé primaire d’une table de plusieurs manières :

• via le langage SQL
• depuis l’éditeur de structure de 4D.

Notes :

• Vous pouvez également définir des clés primaires à l'aide du Gestionnaire de clés primaires de 4D en mode Développement.
• Pour une description de l'utilisation des clés primaires, veuillez vous reporter au paragraphe Règles d'usage des champs clés primaires.

Vous pouvez définir une clé primaire (PRIMARY KEY) lors de la création d’une table (via la commande CREATE TABLE) ou de l’ajout ou de la modification d’une colonne (via la commande ALTER TABLE). La clé primaire est définie à l'aide de la clause PRIMARY KEY suivie du nom de la colonne ou d'une liste de colonnes. Pour plus d'informations, reportez-vous à la section primary_key_definition.

4D vous permet de créer et de supprimer directement une clé primaire via le menu contextuel de l’éditeur de structure.

Pour plus d'informations sur ce point, reportez-vous au paragraphe Clé primaire dans le manuel Mode Développement de 4D.

Le moteur SQL intégré de 4D prend en charge les vues SQL (SQL views) standard. Une vue est une table virtuelle dont les données peuvent provenir de plusieurs tables de la base de données. Une fois qu’une vue est définie, vous pouvez l’utiliser dans une instruction SELECT comme une table réelle. 

Les données présentes dans une vue sont définies grâce à une requête de définition (definition query) basée sur la commande SELECT. Les tables réelles utilisées dans la requête de définition sont appelées "tables sources". Une vue SQL contient des colonnes et des lignes comme une table standard, mais elle n’existe pas en réalité, elle n’est qu’une représentation issue d’un traitement et conservé en mémoire durant la session. Seule la définition de la vue est stockée temporairement. 

Deux commandes SQL permettent de gérer les vues dans 4D : CREATE VIEW et DROP VIEW.