Hé, vous le scripteur !Cette relation peut-elle être sauvée ?
The Microsoft Scripting Guys
Télécharger le code de cet article: HeyScriptingGuy2007_09.exe (150KB)
Les relations sont importantes. Mais comment les scripteurs, eux entre tous, savent-ils que les relations sont importantes ? Hé bien, d'abord, nous regardons beaucoup la télévision, et la télé nous répète sans cesse que les relations sont importantes.
Et pas seulement dans les feuilletons ou les téléfilms hebdomadaires. Par exemple, il existe une publicité de société de crédit qui encourage les téléspectateurs à leur accorder leur clientèle. Est-ce parce qu'elle n'hésite pas à prêter de l'argent à des personnes qui ne devraient en aucun cas obtenir de prêt ? Non. Est-ce parce qu'elle prend un intérêt annuel de 900 % sur ce prêt ? Bien sûr que non. C'est parce que cette société de crédit noue une relation personnelle avec le client. Comme la publicité le clame, « Les relations sont importantes ».
Remarque : bien sûr, nous essuyons une larme, et nous n'en avons pas honte. Après tout, il faudrait être de marbre pour ne pas verser une larme à la pensée des liens solides et durables qui lient une personne à sa société de crédit.
Bien sûr, nous savons ce que vous pensez : « Bon sang, si les relations sont importantes pour les sociétés de crédit, les relations doivent vraiment être importantes pour les personnes qui écrivent des scripts qui interagissent avec des bases de données. » Et la vérité est que les relations devraient être importantes pour les personnes qui écrivent des scripts qui interagissent avec des bases de données. Malheureusement, ces scripteurs ne se rendent pas toujours compte que les relations sont importantes. Mais ne vous inquiétez pas, dans la chronique de ce mois, nous mettons les choses au clair.
Nous savons que de nombreux administrateurs système utilisent des bases de données, souvent pour suivre leur inventaire matériel. Le problème est qu'un grand nombre de ces bases de données sont configurées de façon moins qu'optimale. Par exemple, supposons que vous ayez besoin d'une base de données capable de suivre les lecteurs de disque connectés à tous vos ordinateurs. Si vous n'avez aucune expérience de la conception de base de données, il y a de fortes chances que vous créerez une base de données à une table, telle que celle illustrée à la figure 1.
Figure 1** Base de données à une table **
Comme vous pouvez le constater, il s'agit d'une conception très simple : un champ pour le nom de l'ordinateur, et deux champs d'option Oui/Non pour indiquer si un ordinateur dispose d'un lecteur C: et d'un lecteur D:. Et c'est à peu prêt tout. Clair, net et direct.
Alors, qu'est-ce qui ne va pas avec cette base de données ? Pour être honnête, rien ou presque ne va. Si vous pouvez être sûr qu'aucun de vos ordinateurs n'a plus de deux lecteurs de disque, cette conception fonctionnera (en quelque sorte). Mais, que se passe-t-il si un de vos ordinateurs dispose de trois lecteurs de disque ? Dans ce cas, vous devrez ajouter un autre champ pour le suivi du lecteur E. Bien, mais que se passe-t-il si un de vos ordinateurs est doté de 11 lecteurs de disque ? Et que se passe-t-il si vous voulez suivre les propriétés de chacun de ces lecteurs ? Par exemple, supposons que vous souhaitiez suivre la taille totale de chaque lecteur. Dans ce cas, vous aurez besoin d'un champ Taille_lecteur_C, d'un champ Taille_lecteur_D et d'un champ Taille_lecteur_E, etc. Enfin, vous avez compris. (Dieu vous garde de souhaiter suivre également l'espace disque disponible ou le type de connecteur de disque, ou si les quotas de disque ont été activés sur le lecteur, et ainsi de suite.)
Voici une règle de base à retenir : lorsqu'une entité peut avoir plus d'un quelque chose (par exemple, un ordinateur peut avoir plus d'un lecteur de disques), ce type de conception « fichier plat » (une seule table) n'est pas recommandé. Il est vrai que, dans le cas des lecteurs de disque, vous pouvez probablement vous en sortir ; après tout, vous aurez un nombre limité de lecteurs connectés à un ordinateur. Mais supposons que vous souhaitiez interroger votre base de données et extraire une liste de tous les lecteurs de disque supérieurs à 100 gigaoctets ou plus. Comment pensez-vous faire cela ? Premièrement, vous devrez chercher les lecteurs de 100 gigaoctets dans le champ Taille_lecteur_C, puis dans le champ Taille_lecteur_D, le champ Taille_lecteur_E... Une approche qui n'est ni efficace ni rentable. Mais quelle est l'alternative ? Si vous ne pouvez pas utiliser une base de données à fichier plat, que pouvez-vous utiliser ?
C'est simple : une base de données relationnelle. Les bases de données relationnelles sont notamment conçues pour gérer les relations de type un-à-plusieurs (par exemple, un ordinateur disposant de nombreux lecteurs de disque). Et avant que vous ne le demandiez, non, vous n'avez besoin d'acheter quoi que ce soit pour utiliser une base de données relationnelle. Si vous avez une base de données quelconque (Microsoft® Access™, SQL Server™, Oracle, etc.), vous possédez sans doute déjà une base de données relationnelle. Vous n'avez pas besoin d'acheter quoi que ce soit ; vous devez seulement savoir deux choses : 1) comment configurer une relation entre deux tables dans cette base de données ; et 2) comment écrire un script qui peut tirer profit de cette relation.
Remarque : d'après la télé, vous ne devez jamais essayer de tirer profit d'une relation. Mais notre cas est différent.
Comme le titre de cette rubrique n'est pas Hé, vous le concepteur de base de données !, nous ne nous éterniserons pas sur la conception de base de données. Au lieu de cela, nous vous montrerons une conception de base de données relationnelle très simple qui nous permettra d'aborder l'écriture de requêtes relationnelles. Dans cette base de données, nous créons deux tables. La première s'intitule Computers (Ordinateurs) et contient deux champs : ComputerName (Nom_ordinateur) et SerialNumber (Numéro_série). Vous enregistrez le nom de l'ordinateur dans le champ du nom d'ordinateur, et le numéro de série dans le champ du numéro de série. Tout simplement.
Mais alors, où donc affichons-nous les informations relatives à nos lecteurs de disque ? Dans notre seconde table, qui s'intitule DiskDrives (Lecteurs_disque). Cette table contient trois champs explicites : SerialNumber (Numéro_série), DriveLetter (Lettre_lecteur) et DriveSize (Taille_lecteur).
Le champ clé ici est celui de numéro de série. Si ce nom de champ vous semble familier, c'est qu'il l'est : en effet, ce même champ apparaît dans la table Computers (Ordinateurs). Il ne 'agit pas d'un hasard ou d'une coïncidence ; nous l'avons fait exprès. Afin d'établir une relation entre ces deux tables, nous avons besoin d'un champ commun aux deux tables ; nous pouvons ainsi définir quels lecteurs de disque appartiennent à quels ordinateurs.
Pourquoi avons-nous lié les deux tables à l'aide du numéro de série au lieu du nom de l'ordinateur, par exemple ? En fait, il existe une bonne raison pour cela : les noms des ordinateurs peuvent changer (et cela arrive souvent). Les numéros de série ne changent pas.
Mais assez parlé ! Examinons la figure 2, un script capable d'effectuer une recherche dans ces deux tables et d'extraire une liste des lecteurs de disque appartenant à chaque ordinateur. La figure 3 illustre le type de données renvoyées par ce script.
Figure 3 Résultats de la recherche
atl-ws-01
C:
atl-ws-01
D:
atl-ws-01
E:
atl-ws-02
C:
atl-ws-02
D:
Figure 2 Recherche dans les tables
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = c:\scripts\test.mdb"
objRecordSet.Open "SELECT Computers.*, DiskDrives.* FROM Computers " & _
"INNER JOIN DiskDrives ON Computers.SerialNumber = DiskDrives.SerialNumber " & _
"ORDER BY Computers.ComputerName, DiskDrives.Drive", objConnection, adOpenStatic, _
adLockOptimistic
objRecordSet.MoveFirst
Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item("ComputerName")
Wscript.Echo objRecordset.Fields.Item("Drive")
Wscript.Echo
objRecordset.MoveNext
Loop
objRecordSet.Close
objConnection.Close
Nous n'aborderons pas les principes de base de la connexion et de l'utilisation des bases de données dans la chronique de ce mois. Si vous êtes novice en matière de script, vous devriez jeter un œil sur le webcast des scripteurs intitulé « Écriture de script de base de données pour les administrateurs système » (go.microsoft.com/fwlink/?LinkId=22089). Disons simplement que nous nous connectons à une base de données Access (C:\Scripts\Test.mdb) et que nous utilisons des tables relationnelles nommées Computers (Ordinateurs) et DiskDrives (Lecteurs_disque). Cela doit apparaître clairement dans le script.
Une chose encore : vous devez apporter une petite modification pour que le script fonctionne avec Access 2007 : Il est important de modifier le fournisseur de Microsoft.Jet.OLEDB.4.0 en Microsoft.ACE.OLEDB.12.0, comme ceci :
objConnection.Open _
"Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = c:\scripts\test.accdb"
Voilà. Ce que nous allons faire, plutôt, c'est nous concentrer sur la requête SQL qui extrait les données de nos deux tables :
objRecordSet.Open "SELECT Computers.*, " & _
"DiskDrives.* FROM Computers " & _
"INNER JOIN DiskDrives ON " & _
"Computers.SerialNumber = " & _
"DiskDrives.SerialNumber " & _
"ORDER BY ComputerName, Drive", _
objConnection, adOpenStatic, adLockOptimistic
Compliqué ? Peut-être un peu. Voyons donc si nous pouvons décomposer quelque peu tout cela pour la rendre plus digeste.
La première partie de notre requête est en fait assez facile. Nous voulons sélectionner tous les champs dans les deux tables : Computers (Ordinateurs) et DiskDrives (Lecteurs_disque). D'où ce petit bout de SQL :
SELECT Computers.*, DiskDrives.*
Cela n'est pas trop compliqué ; l'astérisque, inutile de le dire, est simplement un caractère générique signifiant « tout ».
Si vous avez déjà écrit des requêtes SQL (ou si vous avez écrit des requêtes Windows® Management Instrumentation ou WMI, qui utilisent un sous-ensemble du langage de requête SQL), vous connaissez la routine : après avoir spécifié les éléments que vous voulez sélectionner, vous spécifiez l'emplacement à partir duquel vous voulez sélectionner ces éléments. La partie suivante de notre requête est donc :
FROM Computers INNER JOIN DiskDrives ON Computers.SerialNumber = DiskDrives.SerialNumber
Ne vous laissez pas décourager. Il est vrai que c'est plus compliqué qu'une instruction FROM typique, mais pour une bonne raison. Après tout, dans une requête SQL typique, nous sélectionnons seulement des données d'une table ; cette fois, nous sélectionnons des données de deux tables à la fois.
Observons de plus près la syntaxe et son fonctionnement. Comme vous le voyez, nous demandons au script de sélectionner des données de la table Computers (Ordinateurs) et de la table DiskDrives (Lecteurs_disque). Notez, cependant, que nous n'utilisons pas le mot « and » (et), mais INNER JOIN (JOINTURE INTERNE). Ce terme définit le type de relation, puis décrit comment joindre les informations des deux tables séparées en un jeu d'enregistrements unique. (Il existe d'autres types de jointures, mais nous les aborderons plus loin).
Comme nous l'avons sous-entendu, nous devons spécifier le type de relation qui existe entre nos tables, et spécifier exactement comment ces tables sont liées. C'est que ce bout de code fait pour nous :
ON Computers.SerialNumber = DiskDrives.SerialNumber
Ce que nous faisons ici, c'est définir comment nous allons joindre nos tables. Les enregistrements seront regroupés chaque fois que le champ de numéro de série de la table Computers (Ordinateurs) correspond au champ de numéro de série de la table DiskDrives (Lecteurs_disque). Que se passe-t-il si nous utilisons un champ différent (par exemple, ComputerName [Nom_ordinateur]) comme champ de jointure ? Notre code se présentera comme suit :
ON Computers.ComputerName = DiskDrives.ComputerName
Si vous vous posez la question, non, les deux champs n'ont pas besoin d'avoir le même nom, ils doivent simplement contenir les mêmes données. L'utilisation d'un même nom permet d'identifier plus facilement le champ relationnel. Il existe toutefois un petit point à retenir : comme nous avons deux champs avec le même nom, nous devons toujours utiliser la syntaxe : Nom_Table.Nom_Champ pour les références à l'un de ces champs. Cela signifie qu'il faut utiliser le code comme ceci : Wscript.Echo obj- Recordset.Fields.Item("Computers.SerialNumber").
C'est tout ce dont nous avons besoin. Le reste de la requête permet simplement de trier les données, d'abord par nom d'ordinateur, puis par lecteur :
ORDER BY ComputerName, Drive
Voilà, ce n'était pas si compliqué que ça, n'est-ce pas ? Il nous fait maintenant nous interrompre un instant pour parler des jointures. Pourquoi avons-nous utilisé une jointure interne dans notre requête ? C’est simple : Une jointure interne renvoie uniquement les enregistrements qui ont des valeurs correspondantes dans chaque table. En d'autres termes, supposons que nous ayons le numéro de série 121989. Si ce numéro de série apparaît dans les deux tables Computers (Ordinateurs) et DiskDrives (Lecteurs_disque), les enregistrements correspondants sont renvoyés. (Bien sûr, le numéro de série ne peut pas apparaître n'importe où ; il doit se trouver dans le champ de numéro de série.) Ca paraît logique ? Bon.
Supposons maintenant que nous ayons un ordinateur avec le numéro de série 121989, mais qu'il n'existe pas de lecteur de disque associé à ce numéro de série. Dans ce cas, aucune donnée ne sera renvoyée pour l'ordinateur avec le numéro de série 121989. Cela tient au fait qu'une jointure interne ne renvoie des informations que si un enregistrement a des valeurs correspondantes dans chacune des tables jointes.
En d'autres termes, une requête de jointure interne renvoie tous les ordinateurs qui ont des disques durs. Elle ne renvoie pas les ordinateurs qui n'ont pas de disque dur, ni les disques durs qui ne sont pas installés sur un ordinateur. C'est une bonne chose, car dans la plupart des cas, c'est ce que nous recherchons. Mais qu'en est-il si vous voulez une liste d'ordinateurs qui n'ont pas de disque dur ou des disques durs qui n'ont pas d'ordinateur ? Que se passe-t-il alors ?
C'est là qu'intervient la jointure externe. (Ah, c'est à cela que sert la jointure externe !) Dans un premier temps, disons qu'il existe deux types de jointures externes : La jointure gauche et la jointure droite. Dans notre base de données nous avons deux tables : Computers (Ordinateurs) (qui est considérée comme la table « gauche » parce qu'il s'agit de la table principale) et DiskDrives (Lecteurs_disque) (qui est considérée comme la table « droite » parce qu'il s'agit de la table secondaire). Supposons que notre jeu d'enregistrements renvoyé doive inclure tous les ordinateurs, y compris ceux qui n'ont pas lecteur de disque installé. Dans ce cas, nous utilisons la syntaxe LEFT OUTER JOIN (JOINTURE EXTERNE GAUCHE) et une requête qui ressemble à ceci :
objRecordSet.Open "SELECT Computers.*, " & _
"DiskDrives.* FROM Computers " & _
"LEFT OUTER JOIN DiskDrives ON " & _
"Computers.SerialNumber = " & _
"DiskDrives.SerialNumber " & _
"ORDER BY ComputerName, Drive", _
objConnection, adOpenStatic, adLockOptimistic
Comme vous l'avez sans doute compris, une jointure externe gauche renvoie tous les enregistrements de la table gauche, même s'il n'existe pas d'enregistrement correspondant dans la table droite. Nous obtenons donc tous les ordinateurs, mais seulement les enregistrements de la table droite (lecteurs de disque) qui sont associés à un ordinateur.
Nous pouvons également rechercher une liste de tous les lecteurs de disque, y compris ceux qui ne sont pas installés sur un ordinateur. La table DiskDrives (Lecteurs_disque) étant celle de droite dans la relation, nous utilisons la syntaxe RIGHT OUTER JOIN (JOINTURE EXTERNE DROITE), comme suit :
objRecordSet.Open "SELECT Computers.*, " & _
"DiskDrives.* FROM Computers " & _
"RIGHT OUTER JOIN DiskDrives ON " & _
"Computers.SerialNumber = " & _
"DiskDrives.SerialNumber " & _
"ORDER BY ComputerName, Drive", _
objConnection, adOpenStatic, adLockOptimistic
Ce qui nous fait penser que si DiskDrives (Lecteurs_disque) est la table droite dans la relation, alors la table Computers (Ordinateurs) devrait être appelée table mari dans la relation au lieu de table gauche. Comme le savent certains scripteurs par expérience, le mari n'est jamais l'entité droite dans une relation.
Avec la jointure externe droite, nous obtenons tous les enregistrements de la table droite (tous les lecteurs de disque), mais uniquement les ordinateurs (les enregistrements de la table gauche) qui sont associés à un lecteur de disques.
Cela ne vous semble peut-être pas très clair pour le moment, mais si vous faites des essais, vous verrez comment cela fonctionne. Si vous avez besoin d'une base de données pour faire des essais, un exemple est à votre disposition à la page microsoft.com/technet/scriptcenter/resources/tnmag/archive.mspx.
Il est important de préciser que chaque fois que vous utilisez une jointure externe, vous devez également, tout au moins, commencer votre script par la ligne On Error Resume Next. Pourquoi ? Supposons que nous créions une jointure externe gauche. Dans ce cas, il est possible que nous obtenions des ordinateurs qui n'ont pas de disque dur installé. Pas de problème (après tout, c'est ce que nous voulons obtenir), au moins jusqu'à ce que nous rencontrions cette ligne de code, qui renvoie en écho la lettre de lecteur :
Wscript.Echo objRecordset.Fields.Item("Drive")
Etant donné qu'il n'existe pas de champ Drive (Lecteur) pour cet ordinateur, le script s'arrêtera :
C:\Scripts\database.vbs(22, 9) Microsoft VBScript runtime error: Type mismatch
Si nous implémentons l'instruction On Error Resume Next, le script peut ignorer le fait qu'un ordinateur n'a pas lecteur de disque et poursuivre son travail. Vous pourriez utiliser un code comme celui-ci pour vérifier la valeur du champ Drive (Lecteur), puis prendre les mesures nécessaires :
If IsNull(objRecordset.Fields.Item("Drive")) _Then
Wscript.Echo "No disk drives installed."
Else
Wscript.Echo _
objRecordset.Fields.Item("Drive")
End If
Avec ce code, nous vérifions si nous obtenons une valeur nulle au lieu d'une véritable lettre de lecteur. Le cas échéant, nous renvoyons en écho le message « No disk drives installed » (Pas de lecteur de disque installé).. Si nous n'obtenons pas une valeur nulle, nous renvoyons tout simplement en écho la valeur du champ Drive (Lecteur). Le résultat net s'affiche comme illustré à la figure 4.
Figure 4 Affichage des résultats appropriés
atl-ws-01
C:
atl-ws-01
D:
atl-ws-02
C:
atl-ws-02
D:
alt-ws-03
No disk drives installed.
Sans entrer dans trop d'explications, examinons quelques autres requêtes. Par exemple, voici une requête de jointure interne qui renvoie une liste d'ordinateurs et leurs lecteurs de disque installés, à condition que la taille de ces lecteurs de disque soient supérieurs à 50 gigaoctets (nous supposons que la taille des lecteurs est enregistrée en gigaoctets) :
objRecordSet.Open "SELECT Computers.*, " & _
"DiskDrives.* FROM Computers " & _
"INNER JOIN DiskDrives ON " & _
"Computers.SerialNumber = " & _
"DiskDrives.SerialNumber " & _
"WHERE DriveSize > 50 " & _
"ORDER BY Computers.ComputerName, " & _
"DiskDrives.Drive", objConnection, _
adOpenStatic, adLockOptimistic
Comme vous pouvez le constater, nous avons simplement ajouté une clause WHERE standard à la requête d'origine :
WHERE DriveSize > 50
Que se passe-t-il si nous voulons uniquement des informations sur le lecteur E des ordinateurs ? Aucun problème, il suffit d'ajouter la clause WHERE appropriée :
WHERE Drive = 'E:'
Voici maintenant une requête légèrement plus compliquée, qui renvoie une collection d'ordinateurs sans lecteur de disque installé :
objRecordSet.Open _
"SELECT Computers.ComputerName, " & _
"DiskDrives.Drive " & _
"FROM Computers LEFT JOIN DiskDrives " & _
"ON Computers.SerialNumber = " & _
"DiskDrives.SerialNumber " & _
"GROUP BY Computers.ComputerName, " & _
"DiskDrives.Drive " & _
"HAVING (DiskDrives.Drive) Is Null " & _
"ORDER BY Computers.ComputerName", _
objConnection, adOpenStatic, adLockOptimistic
Comme indiqué, cette requête est un peu plus compliquée, et nous manquons d'espace pour expliquer son fonctionnement. Mais elle fonctionne, ce qui est le plus important.
Ou du moins assez important. Comme nous l'avons indiqué plusieurs fois, le plus important, ce sont les relations. Cela ne signifie pas que les relations ne peuvent pas mal tourner. Comme Woody Allen dit à la fin de « Annie Hall » « Une relation...c'est comme un requin ; si elle n'avance pas elle meurt. Et je pense qu'on a un requin mort entre les mains ». Ah, si seulement Woody avait connu les jointures internes et externes ! Avec ces techniques de requêtes, vos relations seront toujours réussies !
The Microsoft Scripting Guys travaillent pour (enfin, sont employés par) Microsoft. Lorsqu’ils ne sont pas en train de jouer au base-ball, d’entraîner une équipe ou de regarder un match (ou de se livrer à de multiples autres activités), ils dirigent le Script Center TechNet. Vous pouvez le vérifier vous-même sur www.scriptingguys.com.
© 2008 Microsoft Corporation et CMP Media, LLC. Tous droits réservés. Toute reproduction, totale ou partielle, est interdite sans autorisation préalable.