Hey, Scripting Guy!È possibile salvare questa relazione?
The Microsoft Scripting Guys
Scarica il codice per questo articolo: HeyScriptingGuy2007_09.exe (150KB)
Le relazioni contano. Ma come fanno gli Scripting Guy, più di tutti, a saperlo? Bene, da un lato, guardiamo molta TV e la TV ripete sempre che le relazioni contano.
E non sto parlando solo delle soap opera o dei telefilm settimanali. Ad esempio, ricordo una pubblicità di una compagnia che concedeva prestiti a breve termine offrendo aiuto agli ascoltatori per sostenere la loro attività. Mi sono chiesto se lo facevano perché disposti a prestare soldi a chi probabilmente non avrebbe potuto mai ottenere un prestito. Ma la risposta è no. Forse lo facevano perché guadagnavano un interesse del 900 percento annuo sul prestito? No di certo. Semplicemente questa società era interessata a stabilire una relazione con le persone. D'altronde il suo motto era: "Le relazioni contano".
Nota: certo, lo sappiamo e non c'è niente di cui vergognarsi. Dopotutto, solo le persone fredde e insensibili non si sentirebbero angosciate al pensiero dei vincoli profondi e duraturi che li legano alla società di prestito in questione.
Naturalmente, sappiamo a cosa stai pensando: "Perbacco, se le relazioni contano per le società di prestito, dovrebbero contare ancor di più per le persone che scrivono script che interagiscono con i database". E il punto è che le relazioni devono contare per le persone che scrivono script che interagiscono con i database. Sfortunatamente, però, questi autori di script non sempre realizzano l'importanza delle relazioni. Ma va bene, nell'articolo di questo mese metteremo tutti in riga.
Sappiamo che molti amministratori di sistema utilizzano spesso i database solo per controllare il proprio inventario hardware. Il problema è che molti di questi database sono configurati non proprio in modo ottimale. Ad esempio, supponiamo che sia necessario un database per tenere traccia delle unità disco collegate ai propri computer. Se la progettazione del database non prevede un background, è probabile che verrà creato un database a una tabella, come illustrato nella Figura 1.
Figura 1** Database a una tabella **
Come è possibile vedere, è tutto molto semplice: abbiamo un campo in cui inserire il nome del computer e un paio di campi con la scelte Sì/No da utilizzare per indicare se un computer ha un'unità C e un'unità D. E questo è quanto: conciso, chiaro e mirato.
Dunque, cosa c'è che non va in questi database? Bene, in realtà, quasi tutto. Se con certezza si può affermare che i propri computer non abbiano più di due unità disco, tutto funzionerà (o quasi). Al contrario, cosa accade se un computer contiene tre unità disco? In questo caso, sarà necessario aggiungere un altro campo per tenere traccia dell'unità E. Perfetto, ma cosa succede se un computer utilizza undici unità disco? E se si desidera tenere traccia delle proprietà di tutte le unità? Ad esempio, supponiamo che si desideri tenere traccia delle dimensioni totali di ciascuna unità. In questo caso, sarà necessario un campo Dimensioni_Unità_C, un campo Dimensioni_Unità_D, un campo Dimensioni_Unità_E e così via. E, Dio ce ne guardi, si potrebbe anche voler controllare lo spazio disponibile sui diversi dischi, il tipo di connettore disco, se le quote disco sono state attivate sulle singole unità e via dicendo.
Ecco una regola empirica da tenere a mente: ogni volta che un'entità ha più di un "qualcosa" (ad esempio un computer può avere più di un'unità disco), questo progetto "file flat" (a una tabella) non è appropriato. È vero, nel caso delle unità disco è possibile pensare di cavarsela. Dopotutto, si utilizza semplicemente un numero finito di unità collegate a un computer. Ma supponiamo si desideri sottoporre a query il proprio database e che venga restituito un elenco di tutte le unità disco per un totale di almeno 100 gigabyte. Come si dovrebbe procedere? Bene, è necessario dapprima cercare le unità di 100 GB nel campo Dimensioni_Unità_C, poi nel campo Dimensioni_Unità_D, quindi nel campo Dimensioni_Unità_E e così all'infinito... Un approccio non molto pratico o utile, direi. Ma qual è l'alternativa? Cosa resta se non è possibile utilizzare un database "file flat"?
È facile: un database relazionale. I database relazionali sono ideati, tra le altre cose, per gestire le relazioni uno-a-molti (ad esempio, un computer che può avere diverse unità disco). E, prevedendo la domanda, posso anticipare che non è necessario acquistare niente di nuovo per utilizzare un database relazionale. Se si utilizza un database di qualsiasi tipo, (Microsoft® Access™, SQL Server™, Oracle e così via), ciò che sorprende è che si dispone già di un database relazionale. Non è necessario acquistare niente, basta solo sapere due cose: 1) come configurare una relazione tra due tabelle nel database e 2) come scrivere uno script che possa sfruttare questa relazione.
Nota: secondo quanto propinato in TV, non si dovrebbe mai tentare di sfruttare una relazione. Ma stiamo parlando di altro.
Poiché questa rubrica non si chiama Hey, Database Design Guy!, non ci occuperemo qui della progettazione dei database. Al contrario, illustreremo un database relazionale molto semplice che ci consentirà di spiegare come scrivere query relazionali. Nel database in questione abbiamo due tabelle. La prima viene denominata Computers e presenta due campi: ComputerName e SerialNumber. Nel campo ComputerName è possibile inserire il nome del computer, mentre nel campo SerialNumber verrà inserito...indovina? Esatto! Il numero di serie.
Poi, dove inseriremo le informazioni sulla nostra unità disco? Nella seconda tabella, denominata DiskDrives. Questa tabella contiene tre campi molto chiari: SerialNumber, DriveLetter e DriveSize.
Il campo principale qui è SerialNumber. Se questo nome suona familiare, bene, è normale: abbiamo lo stesso campo nella tabella Computers. E non si tratta di un caso o di una coincidenza. Per stabilire una relazione tra queste due tabelle è necessario che uno stesso campo venga visualizzato in entrambe le tabelle. Solo così è possibile identificare a quale computer appartengono le singole unità disco.
Perché abbiamo collegato le due tabelle utilizzando il numero di serie invece, ad esempio, del nome computer? C'è un motivo valido: i nomi computer possono cambiare (come capita spesso), mentre i numeri di serie in genere non vengono modificati.
Ma basta parlare. Diamo un'occhiata alla Figura 2 in cui viene illustrato uno script in grado di eseguire ricerche in queste due tabelle e di recuperare un elenco delle unità disco che appartengono a ciascun computer. Nella Figura 3 viene illustrato il tipo di dati restituiti dallo script.
Figure 3 Risultati della ricerca
atl-ws-01
C:
atl-ws-01
D:
atl-ws-01
E:
atl-ws-02
C:
atl-ws-02
D:
Figure 2 Ricerca nelle tabelle
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
Nell'articolo di questo mese non ci occuperemo dei principi di base della connessione e dell'utilizzo dei database. Se non si ha esperienza con la creazione degli script di database, è possibile consultare il Webcast degli Scripting Guy "Creazione degli script di database per gli amministratori di sistema" (go.microsoft.com/fwlink/?LinkId=22089). Tutto ciò che diremo è che siamo collegati a un database Access (C:\Scripts\Test.mdb) e che stiamo utilizzando le tabelle relazionali denominate Computers e DiskDrives. E questo dovrebbe risultare abbastanza evidente dallo script.
Bene, un'altra cosa che diremo è che sarà necessario apportare una piccola modifica per fare tutto ciò con Access 2007: è importante che il provider venga modificato da Microsoft.Jet.OLEDB.4.0 a Microsoft.ACE.OLEDB.12.0 nel seguente modo:
objConnection.Open _
"Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = c:\scripts\test.accdb"
Tutto qui. Quello su cui ci soffermeremo, invece, è la query SQL in grado di recuperare i dati dalle nostre due tabelle:
objRecordSet.Open "SELECT Computers.*, " & _
"DiskDrives.* FROM Computers " & _
"INNER JOIN DiskDrives ON " & _
"Computers.SerialNumber = " & _
"DiskDrives.SerialNumber " & _
"ORDER BY ComputerName, Drive", _
objConnection, adOpenStatic, adLockOptimistic
Sembra difficile? Forse un poco. Dunque vediamo se riusciamo a rendere il tutto un po' più semplice.
La prima parte della query è in realtà abbastanza facile. Vogliamo selezionare tutti i campi in entrambe le tabelle: Computers e DiskDrives. Da qui, la parte di SQL:
SELECT Computers.*, DiskDrives.*
Non è poi così difficile. L'asterisco, inutile a dirsi, è semplicemente un carattere jolly che significa "tutto".
Se in passato sono state scritte query SQL (o query Windows® Management Instrumentation, o WMI, che utilizzano un sottoinsieme del linguaggio di query SQL), si conosce già la routine: una volta specificati gli elementi da selezionare, è necessario specificare la posizione dalla quale si desidera farlo. Ecco il successivo tassello del nostro puzzle:
FROM Computers INNER JOIN DiskDrives ON Computers.SerialNumber = DiskDrives.SerialNumber
Non farti scoraggiare. È vero, è più complicato rispetto a una tipica istruzione FROM, ma per un buon motivo. Dopotutto, in una query SQL tipica vengono semplicemente selezionati i dati da una tabella, mentre questa volta stiamo selezionando i dati da due tabelle contemporaneamente.
Esaminiamo adesso più da vicino la sintassi e il relativo funzionamento. Come è possibile vedere, stiamo chiedendo allo script di selezionare i dati dalla tabella Computers e dalla tabella DiskDrives. Si noti, tuttavia, che non utilizziamo il termine "and", ma l'espressione INNER JOIN che definisce il tipo di relazione e, a sua volta, descrive il modo in cui le informazioni provenienti da due diverse tabelle verranno unite in un unico recordset. Sì, esistono altri tipi di join, ma discuteremo di questi ultimi più avanti.
Come abbiamo lasciato intuire, siamo noi a dover specificare il tipo di relazione esistente tra le due tabelle oltre a specificare esattamente come queste due tabelle sono correlate. Ecco quanto fa per noi questa parte di codice:
ON Computers.SerialNumber = DiskDrives.SerialNumber
Si tratta di definire il modo in cui andiamo a unire le nostre tabelle. I record verranno raggruppati ogni volta che quanto inserito nel campo SerialNumber della tabella Computers corrisponde a quanto inserito nel campo SerialNumber della tabella DiskDrives. Cosa accadrebbe se avessimo utilizzato un campo diverso (ad esempio ComputerName) come campo per il join? Beh, il nostro codice sarebbe stato simile al seguente:
ON Computers.ComputerName = DiskDrives.ComputerName
Nel caso lo si desideri sapere, no, i due campi non devono avere lo stesso nome, devono contenere solo gli stessi dati. L'impiego di uno stesso nome semplifica l'identificazione del campo relazionale. C'è solo un piccolo problema da considerare: se utilizziamo lo stesso nome per i due campi, è necessario utilizzare sempre la sintassi Table.Name.Field.Name quando ci riferiamo a uno dei due campi, il che vuol dire utilizzare un codice simile al seguente: Wscript.Echo obj- Recordset.Fields.Item("Computers.SerialNumber").
Questo è tutto ciò di cui abbiamo bisogno. Il resto della query ordina i dati, prima in base al nome computer e poi all'unità:
ORDER BY ComputerName, Drive
Visto, non è stato poi così difficile, vero? Dobbiamo adesso fare una piccola pausa e parlare dei join. Perché abbiamo utilizzato un inner join nella nostra query? La risposta è semplice. Un inner join restituisce solo record con valori corrispondenti in ciascuna tabella. In altre parole, supponiamo di avere il numero di serie 121989. Se questo numero di serie viene visualizzato in entrambe le tabelle, Computers e DiskDrives, verranno restituiti i record corrispondenti. Naturalmente, il numero di serie non può essere visualizzato ovunque, ma solo nel campo SerialNumber. Tutto chiaro? Bene.
Adesso, supponiamo di avere un computer con il numero di serie 121989 e che nessuna unità disco abbia lo stesso numero di serie. In questo caso, non verranno restituiti dati per il computer con il numero di serie 121989 perché un inner join restituisce informazioni solo se un record presenta valori corrispondenti in ciascuna delle tabelle unite.
In altre parole, una query di inner join restituisce tutti i computer che hanno dischi rigidi e non i computer che ne sono sprovvisti o i dischi rigidi non installati su un computer. Perfetto, è proprio quello che vogliamo. E se volessimo un elenco di computer che non hanno dischi rigidi o dischi rigidi non installati su computer? Cosa accadrebbe?
Ecco a cosa serve l'outer join. Oh, ecco a cosa serve l'outer join! Per ora, ci limitiamo a dire che esistono solo due tipi di outer join: left e right. Nel nostro database abbiamo due tabelle: Computers (considerata la tabella di sinistra in quanto tabella principale) e DiskDrives (considerata la tabella di destra perché non è la tabella principale). Supponiamo di volere che il recordset restituito includa tutti i computer, anche quelli sprovvisti di unità disco installate. In questo caso, è necessario utilizzare la sintassi LEFT OUTER JOIN e una query simile alla seguente:
objRecordSet.Open "SELECT Computers.*, " & _
"DiskDrives.* FROM Computers " & _
"LEFT OUTER JOIN DiskDrives ON " & _
"Computers.SerialNumber = " & _
"DiskDrives.SerialNumber " & _
"ORDER BY ComputerName, Drive", _
objConnection, adOpenStatic, adLockOptimistic
Come forse è possibile immaginare, un left outer join restituisce tutti i record nella tabella di sinistra, anche se non esistono record corrispondenti nella tabella di destra. A questo punto, vengono restituiti tutti i computer, ma solo i record della tabella di destra (unità disco) associati a un computer.
In alternativa, potremmo desiderare un elenco di tutte le unità disco, incluse quelle non installate in un computer. Poiché DiskDrives è la tabella di destra nella relazione, è necessario utilizzare la sintassi RIGHT OUTER JOIN nel seguente modo:
objRecordSet.Open "SELECT Computers.*, " & _
"DiskDrives.* FROM Computers " & _
"RIGHT OUTER JOIN DiskDrives ON " & _
"Computers.SerialNumber = " & _
"DiskDrives.SerialNumber " & _
"ORDER BY ComputerName, Drive", _
objConnection, adOpenStatic, adLockOptimistic
Adesso che ci pensiamo, se DiskDrives è la tabella di destra nella relazione, Computers deve essere di diritto la tabella "compagna" della relazione e non la tabella di sinistra. Come appreso dagli Scripting Guy dopo una dolorosa esperienza, la tabella "compagna" non è mai l'entità di destra della relazione.
Con il right outer join otteniamo tutti i record nella tabella di destra (tutte le unità disco), ma solo i computer (record nella tabella di sinistra) associati a un'unità disco.
Quanto appena detto potrebbe non essere chiarissimo al momento, ma pian piano lo diventerà. Per ottenere un database con il quale esercitarsi, visitare il sito microsoft.com/technet/scriptcenter/resources/tnmag/archive.mspx dove ne è disponibile un campione.
È importante ricordare che ogni volta che si utilizza un outer join, è necessario, almeno, eseguire l'istruzione On Error Resume Next nella prima riga dello script. Perché? Supponiamo di eseguire un left outer join. In questo caso è possibile che vengano restituiti alcuni computer che non dispongono di dischi rigidi installati. Bene (dopo tutto, è proprio quello che vogliamo), almeno finché non ci imbattiamo in questa riga di codice che indica la lettera dell'unità:
Wscript.Echo objRecordset.Fields.Item("Drive")
Poiché per questo computer non esiste un campo Drive, lo script verrà arrestato in modo anomalo:
C:\Scripts\database.vbs(22, 9) Microsoft VBScript runtime error: Type mismatch
Se implementiamo On Error Resume Next, tuttavia, è possibile che lo script ignori che il computer non presenta unità disco e continui per la sua strada. In caso contrario, è possibile utilizzare un codice simile per verificare il valore del campo Drive e comportarsi di conseguenza:
If IsNull(objRecordset.Fields.Item("Drive")) _Then
Wscript.Echo "No disk drives installed."
Else
Wscript.Echo _
objRecordset.Fields.Item("Drive")
End If
Con questo codice è possibile controllare che venga restituito un valore Null invece di una lettera di unità. In questo caso, riceveremo il messaggio indicante che nessuna unità disco è installata. Se non riceviamo un valore Null, avremo semplicemente il valore del campo Drive. Il risultato netto è l'output, come illustrato nella Figura 4.
Figure 4 Visualizzazione dei risultati appropriati
atl-ws-01
C:
atl-ws-01
D:
atl-ws-02
C:
atl-ws-02
D:
alt-ws-03
No disk drives installed.
Senza eccessivi approfondimenti, diamo un'occhiata a qualche altra query. Ad esempio, ecco una query di inner join che restituisce un elenco di computer con le relative unità disco installate, ammesso che queste unità disco siano superiori a 50 gigabyte (stiamo immaginando che le dimensioni delle unità siano misurate in gigabyte):
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
Come risulta chiaro, è bastato aggiungere una clausola standard WHERE alla query originale:
WHERE DriveSize > 50
E se avessimo voluto solo ricevere informazioni sull'unità E dei computer? Nessun problema, avremmo anche qui dovuto solo aggiungere la clausola WHERE appropriata:
WHERE Drive = 'E:'
Di seguito, invece, una query leggermente più complessa che restituisce un insieme di computer senza unità disco installate:
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
Come detto in precedenza, questa query è un po' più complicata e poiché lo spazio a nostra disposizione è quasi terminato, non ne spiegheremo il funzionamento. Ma funziona e questa è la cosa che più conta.
Beh, forse la seconda cosa che più conta, perché come abbiamo evidenziato più volte, ciò che conta veramente sono le relazioni. Il che non vuol dire che le relazioni non possano finire. Come disse Woody Allen al termine del film "Io e Annie": "Una relazione, io penso, è come uno squalo. Cioè deve costantemente muoversi oppure muore. Ed io penso che quello che abbiamo nelle nostre mani sia uno squalo morto". Ah, se solo Woody avesse saputo qualcosa in più sugli inner join e gli outer join. Con queste tecniche di query, sono garantite ottime relazioni!
The Microsoft Scripting Guys lavorano o, per meglio dire, sono stipendiati da Microsoft. Quando non si dedicano al baseball (o ad altre attività) come giocatori, allenatori o semplici spettatori, gestiscono il TechNet Script Center. Consultare la pagina www.scriptingguys.com.
© 2008 Microsoft Corporation e CMP Media, LLC. Tutti i diritti riservati. È vietata la riproduzione completa o parziale senza autorizzazione.