Cours 4D v11 Addendum R3
Cours 4D v11 SQL pour développer les connaissances surs les bases du langage
…
Moteur SQL
Schémas Le moteur SQL intégré de 4D v11 SQL implémente le concept de schémas. Cette implémentation se traduit par des modifications d’interface et la prise en charge de nouvelles commandes SQL.
La création, la modification et la suppression des schémas s’effectuent via des commandes SQL. Une nouvelle option de l’Inspecteur permet également d’affecter les tables aux schémas.
Présentation 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.
Lorsqu’une base de données est créée ou convertie avec 4D v11 SQL r3 ou une version ultérieure, un schéma par défaut est créé afin de regrouper toutes les tables de la base. Ce schéma est nommé "DEFAULT_SCHEMA". Il ne peut pas être supprimé ni renommé.
Dans les versions précédentes de 4D, les droits d’accès via le SQL étaient définis globalement pour la base. Désormais, ils seront définis par schémas. Chaque schéma pourra se voir attribuer un type d’accès parmi les suivants :
n Lecture seulement (données)
n Lecture/Ecriture (données)
n Complet (données et structure)
Note : - Lors de la conversion d’une ancienne base en version 11.3 ou suivante, les droits d’accès globaux (tels que définis dans la page SQL des préférences de l’application) sont transférés au schéma par défaut. - Comme dans les versions précédentes, le contrôle des accès 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, etc., dispose toujours d’un accès complet.
Seuls le Super_Utilisateur et l’Administrateur de la base peuvent créer, modifier ou supprimer un schéma.
Si le système de gestion des accès de 4D n’est pas activé (c’est-à-dire, si aucun mot de passe n’est assigné au Super_Utilisateur), tous les utilisateurs peuvent créer et modifier des schémas sans restriction.
Création d’un schéma Les schémas peuvent être créés uniquement par programmation, à l’aide de la commande SQL suivante :
CREATE SCHEMA Nom_Schema
Lorsque vous créez un nouveau schéma, par défaut les droits associés sont les suivants :
n Lecture seulement (Données) : Tout le monde
n Lecture/Ecriture (Données) : Tout le monde
n Complet (Données & Structure) : Personne
/ Exemple de création d’un schéma nommé "Droits_Compta" : CREATE SCHEMA Droits_Compta
Affectation des tables aux schémas Chaque table appartient à un seul schéma. Vous pouvez affecter un schéma à une table soit via l’Inspecteur, soit par programmation.
n Affectation en structure
Vous pouvez affecter un schéma à une table dans la zone SQL de l’Ins¬pecteur de table (fenêtre de structure), via un pop up menu listant les schémas définis dans la base :
n Affectation par programmation
Il est également possible d’affecter une table à un schéma au moment de sa création à l’aide du langage SQL.
/ Création d’une table et affectation au schéma MonSchema1 : CREATE TABLE MonSchema1.MaTable
Si le schéma MONSCHEMA1 n’existe pas, une erreur est retournée et la table est assignée au schéma par défaut.
/ Création d’une table et affectation au schéma par défaut : CREATE TABLE MaTable
n Modification de l’affectation
Pour modifier l’affectation courante d’un schéma, vous pouvez utiliser la commande SQL ALTER TABLE :
ALTER TABLE Nom_Table SET SCHEMA Nom_Schema
/ Transfert de la table "MaTable" au schéma "MonSchema2" : ALTER TABLE MaTable SET SCHEMA MonSchema2
Note : Les tables système (_USER_TABLES, _USER_COLUMNS, _USER_INDEXES, _USER_CONSTRAINTS, _USER_IND_COLUMNS et _USER_CONS_COLUMN) sont affectées à un schéma particulier nommé SYSTEM_SCHEMA. Ce schéma ne peut être ni modifié ni supprimé par un utilisateur. Il n’apparaît pas dans la liste des schémas affichée dans l’Inspecteur de table. Il est accessible en mode Lecture seulement à tout utilisateur.
Renommer un schéma Vous pouvez renommer un schéma à l’aide de la commande SQL ALTER SCHEMA :
ALTER SCHEMA ancien_nom RENAME TO nouveau_nom
/ Renommage du schéma "MyFirstSchema" en "MyLastSchema" : ALTER SCHEMA MyFirstSchema RENAME TO MyLastSchema
Modifier les droits d’accès Vous pouvez modifier les droits d’accès associés à un schéma à l’aide de la commande SQL GRANT :
GRANT [READ | READ_WRITE | ALL] ON Nom_Schema TO 4D_User_Group
4D_User_Group représente le nom du groupe d’utilisateurs 4D auquel vous souhaitez affecter les droits d’accès au schéma.
Note : 4D permet de définir des noms de groupes comportant des espaces ou des caractères accentués, qui ne sont pas acceptés par le standard SQL. Dans ce cas, vous devez encadrer le nom avec les caractères [ et ]. Par exemple : GRANT READ ON [le schéma] TO [les admins!]
Les mots-clés READ, READ-WRITE et ALL correspondent aux types d’accès définis dans la page SQL des Préférences :
n READ instaure le mode d’accès Lecture seulement (données)
n READ_WRITE instaure le mode d’accès Lecture/Ecriture (données)
n ALL instaure le mode d’accès complet (données et structure).
/ Vous souhaitez autoriser l’accès en lecture écriture des données du schéma MonSchema1 au groupe "Power_Users" :
GRANT READ_WRITE ON MonSchema1 TO POWER_USERS
Vous pouvez supprimer les droits d’accès spécifiques associés à un schéma à l’aide de la commande SQL REVOKE :
REVOKE [READ | READ_WRITE | ALL] ON Nom_Schema
En fait, lorsque vous exécutez cette commande, vous affectez le pseudo-groupe d’utilisateurs Personne au droit d’accès défini.
/ Vous souhaitez supprimer tout droit en lecture écriture au schéma MonSchema1 :
REVOKE READ_WRITE ON MonSchema1
Il est possible de supprimer tout schéma, à l’exception du schéma par défaut. Lorsque vous supprimez un schéma, toutes les tables qui lui étaient affectées sont transférées au schéma par défaut. Les tables transférées héritent des droits d’accès du schéma par défaut.
La suppression d’un schéma est effectuée à l’aide de la commande SQL DROP SCHEMA :
DROP SCHEMA Schema_name
/ Vous souhaitez supprimer le schéma MyFirstSchema (auquel sont affec¬tées les tables Table1 et Table2) :
DROP SCHEMA MyFirstSchema
Après cette opération, les deux tables Table1 et Table2 sont réaffectées au schéma par défaut.
Si vous tentez de supprimer un schéma inexistant ou ayant déjà été supprimé, une erreur est générée.
...
Importer et exporter des schémas
4D permet d’exporter et d’importer les schémas définis dans une base par l’intermédiaire de tables système. Cette fonction est utile notamment en cas de mise à jour de la structure : il suffit d’importer la définition des schémas dans la nouvelle structure afin qu’elle soit immédiatement opérationnelle.
Pour cela, il suffit d’utiliser la nouvelle table système nommée _USER_SCHEMAS (cf. paragraphe précédent). Les données relatives aux schémas peuvent être stockées dans un BLOB crypté à l’aide d’une méthode du type suivant :
Debut SQL
SELECT * from [_USER_SCHEMAS] INTO Array1,Array2,Array3...;
Fin SQL
VARIABLE VERS BLOB(Array1;SchemaBlob;*)
VARIABLE VERS BLOB(Array2;SchemaBlob;*)
VARIABLE VERS BLOB(Array3;SchemaBlob;*)
...
Le BLOB peut ensuite être enregistré dans un fichier texte ou un champ.
L’import des schémas dans une base s’effectuera selon le principe illustré ci-dessous :
`Initialisation des variables tableau
BLOB VERS VARIABLE(SchemaBlob;Array1;Offset) BLOB VERS VARIABLE(SchemaBlob;Array2;Offset) BLOB VERS VARIABLE(SchemaBlob;Array3;Offset)
...
`Remplacement de la table système _USER_SCHEMAS courante par
`celle qui a été stockée dans le BLOB
$Execute:="INSERT into [_USER_SCHEMAS] ID, SchemaName, ...VALUES
("...)
Debut SQL
EXECUTE IMMEDIATE $Execute;
Fin SQL
Note : Bien entendu, l’utilisateur qui exécute la méthode d’import doit disposer des droits d’accès adéquats (ce doit être le Super_Utilisteur ou l’Administrateur de la base).
INSERT La commande INSERT bénéficie de deux nouveautés :
n Possibilité d’insérer un contenu de fichier
n Possibilité d’effectuer des insertions multi-lignes
Insertion du contenu de fichiers (INFILE) La commande INSERT permet désormais d’utiliser le contenu d’un fichier externe pour définir les valeurs d’un nouvel enregistrement. Pour cela, la commande admet le nouveau mot-clé facultatif INFILE :
INSERT INTO {nom_sql | chaîne_sql}
[(ref_colonne, ..., ref_colonne)]
{VALUES([INFILE]{expression_arithmétique |NULL}, ..., [INFILE]{expression_arithmétique |NULL}) |sous_requête}
Le mot-clé INFILE doit être utilisé uniquement avec des expressions de type VARCHAR. Lorsque le mot-clé INFILE est passé, la valeur expression_arithmétique est évaluée en tant que chemin d’accès de fichier ; si le fichier est trouvé, le contenu du fichier est inséré dans la colonne correspondante. Seuls des champs de type texte ou BLOB peuvent recevoir des valeurs issues d’un INFILE. Le contenu du fichier est transféré sous forme de données brutes, sans interprétation.
Le fichier recherché doit se trouver sur l’ordinateur hébergeant le moteur SQL, même si la requête provient d’un client distant. De même, le chemin d’accès doit être exprimé en respectant la syntaxe du système d’exploitation du moteur SQL. Il peut être absolu ou relatif.
Insertions multi-lignes Le moteur SQL intégré de 4D admet désormais les insertions multi
lignes de valeurs, ce qui permet d’alléger et d’optimiser le code. L’exécution du code utilisant la syntaxe d’insertion multi-lignes est particulièrement optimisé lors de l’insertion de grandes quantités de données.
La syntaxe des insertions multi-lignes est la suivante :
INSERT INTO {nom_sql | chaîne_sql}
[(ref_colonne, ..., ref_colonne)]
VALUES(expression_arithmétique, ..., expression_arithmétique), ..., (expression_arithmétique, ..., expression_arithmétique);
Cette syntaxe permet d’éviter la répétition des lignes, nécessaire dans
les versions précédentes de 4D. Par exemple :
Debut SQL
INSERT INTO MaTable (Chp1,Chp2,ChpBol,ChpDate,ChpHeure,
ChpInfo) VALUES (1,1,1,'11/01/01','11:01:01',’First row’) ; INSERT INTO MaTable (Chp1,Chp2,ChpBol,ChpDate,ChpHeure,
ChpInfo) VALUES (2,2,0,'12/01/02','12:02:02',’2nd row’),
INSERT INTO MaTable (Chp1,Chp2,ChpBol,ChpDate,ChpHeure, ChpInfo) VALUES (7,7,1,'17/01/07','17:07:07',’7th row’); Fin SQL
Désormais, vous pouvez écrire :
Debut SQL
INSERT INTO MaTable
(Chp1,Chp2,ChpBol,ChpDate,ChpHeure, ChpInfo) VALUES
(1,1,1,'11/01/01','11:01:01',’Premiere ligne’), (2,2,0,'12/01/02','12:02:02',’Deuxième ligne’), (3,3,1,'13/01/03','13:03:03',’Troisième ligne’),
(7,7,1,'17/01/07','17:07:07',’Septième ligne’); Fin SQL
Vous pouvez également utiliser des variables, par exemple :
vid1:=1
vidx1:=1
vbol1:=1
vdate1:= !11/01/01!
vheure1:=?11:01:01?
vtexte1:=”Première ligne”
`Insertion multi-lignes
Debut SQL
INSERT INTO MaTable
(Chp1,Chp2,ChpBol,ChpDate,ChpHeure, ChpInfo)
VALUES
(:vid1, :vidx1, :vbol1, :vdate1, :vheure1, :vtexte1),
(2,2,0,'12/01/02','12:02:02',’Deuxième ligne’),
(7,7,1,'17/01/07','17:07:07',’Septième ligne’); Fin SQL
avec cette syntaxe :
TABLEAU TEXTE(vTabId;0)
TABLEAU TEXTE(vTabIdx;0)
TABLEAU TEXTE(vTabText;0)
TABLEAU BOOLEEN(vTabbol;0)
TABLEAU DATE(vTabdate;0)
TABLEAU ENTIER LONG(vTabL;0)
...
Debut SQL
INSERT INTO MaTable
(Chp1,Chp2,ChpBol,ChpDate,ChpHeure, ChpInfo)
VALUES
( :vTabId, :vTabIdx, :vTabbol, :vTabdate, :vTabL, :vTabText);
Fin SQL
Note : Vous ne pouvez pas combiner des variables simples et des tableaux dans la même instruction INSERT.
SELECT La commande SELECT admet désormais des références à des variables
4D dans les clauses LIMIT et OFFSET. Vous pouvez maintenant écrire : SELECT... OFFSET :var1 LIMIT :var2 ...
... ... ...
SQL FIXER OPTION SQL FIXER OPTION (option; valeur)
Paramètres Type Description
option Entier long Numéro d’option à définir
valeur Entier long Nouvelle valeur de l’option
Note : SQL FIXER OPTION est le nouveau nom de la commande ODBC FIXER OPTION (cf. paragraphe “Renommage des commandes ODBC”, page 13).
La commande SQL FIXER OPTION permet désormais de définir l’encodage du texte utilisé pour les requêtes envoyées aux sources externes (via le SQL pass-through).
Note : La commande SQL LIRE OPTION permet de connaître l’encodage courant.
Pour cela, une nouvelle constante a été ajoutée dans le thème "SQL" : SQL Jeu de caractères (valeur 100). Lorsque vous passez cette constante dans le paramètre option, vous devez passer dans le paramètre valeur l’identifiant MIBEnum du jeu de caractères à utiliser. Les numéros MIBEnum sont référencés à l’adresse suivante : ….
Par exemple, si vous souhaitez utiliser le type d’encodage UTF-7, vous devez exécuter l’instruction suivante :
SQL FIXER OPTION(SQL Jeu de caractères;103)
Par défaut, 4D utilise en interne l’encodage UTF-8 (valeur 106).
Lorsque vous modifiez l’encodage à l’aide de la commande SQL FIXER OPTION, la modification est effective pour le process courant et la connexion courante.
Si la commande a été exécutée correctement, la variable système OK prend la valeur 1. Sinon, par exemple si vous exécutez la commande SQL FIXER OPTION alors qu’il n’y a pas de connexion externe valide, OK prend la valeur 0.
Renommage des commandes ODBC L’implémentation des connexions externes directes à des bases 4D Server a entraîné une réorganisation des commandes existantes. En effet, la portée des commandes du thème "Sources de données externes" a été étendue. Un nouveau préfixage et l’abandon de commandes ont été nécessaires.
n Toutes les commandes du thème "Sources de données externes", aupa¬ravant préfixées "ODBC", ont été renommées. Elles sont désormais pré¬fixées "SQL". Par exemple, ODBC LOGIN est désormais intitulée SQL LOGIN.
Cette modification globale a été rendue nécessaire car ces commandes de communication permettent désormais de manipuler des requêtes SQL "directes", n’utilisant pas nécessairement le protocole ODBC.
n Pour plus de clarté, toutes les commandes désormais préfixées "SQL" ont été déplacées dans le thème "SQL". Le thème "Sources de données externes" a été supprimé.
n Les constantes associées à ce thème ont également été préfixées "SQL" et le thème de constantes "Source de données externe" a été renommé "SQL".
n La commande SQL LOGIN va désormais permettre d’aiguiller les requê¬tes SQL vers la source de donnée requise : base interne, source ODBC ou base 4D Server. La commande SQL LOGOUT, quant à elle, permet de refermer la connexion ouverte précédemment.
Les commandes UTILISER BASE INTERNE et UTILISER BASE EXTERNE ne sont donc plus nécessaires. Leur utilisation est déconseillée, elles ne seront pas maintenues dans les prochaines versions de 4D.
SQL LOGIN SQL LOGIN{(source; utilisateur; motDePasse{;*})}
Paramètres Type Description
source Texte Æ Nom de la base externe ou
Adresse IP de la base externe ou
Nom de source de données ODBC ou titi pour afficher le dialogue de sélection
utilisateur Texte Æ Nom d'utilisateur
motDePasse Texte Æ Mot de passe de l’utilisateur
* * Æ Appliquer à Debut SQL/Fin SQL
Si omis : non (base interne) Si passé : oui
Note : SQL LOGIN est le nouveau nom de la commande ODBC LOGIN.
La commande SQL LOGIN permet d’ouvrir une connexion avec une source de données SQL, définie dans le paramètre source. Elle désigne la cible du code SQL exécuté ultérieurement dans l’application :
via la commande SQL EXECUTER
via le code placé à l’intérieur des balises Debut SQL / Fin SQL (si le paramètre * est passé, cf. ci-dessous).
La source de données SQL peut être soit :
une base 4D Server externe (nouveauté 4D v11 SQL r3), une source ODBC externe,
le moteur SQL interne.
Vous pouvez passer dans source l’une des valeurs suivantes :
une adresse IP
Syntaxe : IP:{:}
Dans ce cas, la commande ouvre une connexion directe avec la base 4D Server exécutée sur l’ordinateur ayant l’adresse IP définie. Sur l’ordina¬teur "cible", le serveur SQL doit être lancé. Si vous passez un numéro de port TCP, il doit avoir été spécifié comme port de publication du ser¬veur SQL dans la base "cible". Si vous ne passez pas de numéro de port TCP, le port par défaut sera utilisé (19812). Le numéro de port TCP du serveur SQL peut être modifié dans la page SQL/Configuration des Pré¬férences de l’application.
Reportez-vous aux exemples 1 et 2.
n un nom de publication de base 4D
Syntaxe : 4D:
Dans ce cas, la commande ouvre une connexion directe avec la base 4D Server dont le nom de publication sur le réseau correspond au nom spécifié. Le nom de publication réseau d’une base est défini dans la page Client-Serveur/Configuration des Préférences de l’application. Reportez-vous à l’exemple 4.
Note : Le numéro de port TCP du serveur SQL 4D cible (qui publie la base 4D) et le numéro de port TCP du serveur SQL de l’application 4D ouvrant la connexion doivent être identiques.
n un nom de source de données ODBC valide
Syntaxe : ODBC: ou
Dans ce cas, le paramètre source contient le nom de la source de don¬nées telle qu'elle a été définie dans le gestionnaire du pilote ODBC. Ce principe correspond au fonctionnement précédent de la commande ODBC LOGIN.
La syntaxe sans le préfixe "ODBC:" a été conservée afin d’assurer la compatibilité avec les versions précédentes de 4D, toutefois pour des raisons de lisibilité du code il est conseillé d’utiliser le préfixe "ODBC:". Reportez-vous à l’exemple 4.
n une chaîne vide
Syntaxe : ""
Dans ce cas la commande provoque l’affichage de la boîte de dialogue de connexion, permettant de désigner manuellement la source de don¬nées à laquelle se connecter :
Cette boîte de dialogue comporte plusieurs pages. La page TCP/IP se compose des éléments suivants :
n Nom cible : ce menu est construit à l’aide de deux listes :
- la liste des bases ouvertes récemment en connexion directe. Le mécanisme de mise à jour de cette liste est identique à celui de l’application 4D, à la différence près que le dossier contenant les fichiers .4DLink est nommé "Favorites SQL v11" au lieu de "Favorites v11".
- la liste des applications 4D Server dont le serveur SQL est lancé et dont le port TCP pour les connexions SQL est égal à celui de l’appli¬cation source. Cette liste est mise à jour dynamiquement à chaque nouvel appel de la commande SQL LOGIN sans le paramètre source. Le caractère "^" placé devant un nom de base indique que la con¬nexion est effectuée en mode sécurisé via SSL.
n Adresse réseau : cette zone affiche l’adresse IP et éventuellement le port TCP de la base sélectionnée dans le menu Nom cible.
Vous pouvez également saisir dans cette zone une adresse IP puis cli¬quer sur le bouton Connexion afin de vous connecter à la base 4D Server correspondante. Vous pouvez également spécifier le port TCP, en saisissant deux points (:) puis le numéro du port à la suite de l’adresse. Par exemple : 192.168.93.105:19855
n Utilisateur et Mot de passe : ces zones permettent de saisir les iden¬tifiants de la connexion.
Les pages DSN utilisateur et DSN système affichent respectivement la liste des sources de données ODBC utilisateur et système définies dans le gestionnaire ODBC de la machine. Ces pages permettent de sélectionner une source de données et de saisir des identifiants afin d’ouvrir une connexion avec une source ODBC externe.
Si la connexion est établie, la variable système OK prend la valeur 1. Sinon, elle prend la valeur 0 et une erreur est générée. Cette erreur peut être interceptée via une méthode de gestion d’erreurs installée par la commande APPELER SUR ERREUR.
n la constante SQL_INTERNAL Syntaxe : SQL_INTERNAL Dans ce cas, la commande redirige les requêtes SQL suivantes vers le moteur SQL interne de la base.
Le paramètre facultatif * a été ajouté pour des raisons de compatibilité. Dans les versions précédentes de 4D, la commande ODBC LOGIN n’affectait pas le code SQL inclus dans les balises Debut SQL/Fin SQL (pour cela, il était nécessaire d’utiliser les commandes UTILISER BASE INTERNE et UTILISER BASE EXTERNE). Pour ne pas modifier le fonctionnement des bases existantes, un appel de SQL LOGIN sans le paramètre * ne changera pas la cible du code SQL exécuté au sein des balises Debut SQL/Fin SQL.
Si vous souhaitez que le code placé dans les balises Debut SQL/Fin SQL soit appliqué à la source définie par la commande SQL LOGIN, il est nécessaire de passer le paramètre *.
Note : Dans le cas d’une connexion directe, si vous passez des chaînes vides dans les paramètres utilisateur et motDePasse, la connexion ne sera acceptée que si les mots de passe 4D ne sont pas activés dans la base cible. Sinon, la connexion est refusée.
Pour refermer la connexion courante et libérer la mémoire, il suffit d’exécuter la commande SQL LOGOUT. Toutes les requêtes SQL sont alors dirigées vers le moteur SQL interne de la base.
Si vous appelez une nouvelle fois SQL LOGIN sans avoir refermé explicitement la connexion courante, elle est automatiquement refermée.
/ Exemple 1 : Ouverture d’une connexion directe avec l’application 4D Server v11 SQL exécutée sur le poste ayant l’adresse IP 192.168.45.34 et répondant sur le port TCP par défaut. Les requêtes SQL exécutées via la commande SQL EXECUTE seront redirigées vers cette connexion, les requêtes incluses dans les balises Debut SQL/Fin SQL ne seront pas redi¬rigées.
SQL LOGIN("IP:192.168.45.34";"John";"azerty")
/ Exemple 2 : Ouverture d’une connexion directe avec l’application 4D Server v11 SQL exécutée sur le poste ayant l’adresse IP 192.168.45.34 et répondant sur le port TCP 20150. Les requêtes SQL exécutées via la commande SQL EXECUTE et les requêtes incluses dans les balises Debut SQL/Fin SQL seront redirigées vers cette connexion.
SQL LOGIN("IP:192.168.45.34:20150";"John";"azerty";*)