Ei, Equipe de Scripts!É possível salvar esta relação?
A Equipe de Scripts da Microsoft
Faça download do código deste artigo: HeyScriptingGuy2007_09.exe (150KB)
As relações são importantes. Mas como justamente a Equipe de Scripts saberia disso? Para começar, nós assistimos muita TV, e na TV sempre se diz que as relações são importantes.
E não é só nas novelas e nos filmes que passam sempre na TV. Por exemplo, o comercial de uma empresa de empréstimos pessoais incentiva a fidelidade dos clientes. Será porque eles estão dispostos a emprestar dinheiro a pessoas que provavelmente não deveriam mesmo conseguir um empréstimo? Não. Será porque vão cobrar juros que chegam a 900% ao ano sobre o empréstimo? Claro que não. É porque essa empresa de empréstimos cria uma relação pessoal com você. Como diz o comercial, "as relações são importantes".
Observação: é claro que estamos chorando — e não temos vergonha. Afinal, é preciso que o indivíduo seja totalmente frio e insensível para não derramar uma lágrima quando pensa nos laços profundos e duradouros que tem com a sua empresa de empréstimos pessoais.
Claro que sabemos o que você está pensando: "Nossa, se as relações são importantes para as empresas de empréstimos pessoais, então devem ser mesmo muito importantes para quem escreve scripts que interagem com bancos de dados". E o fato é que as relações deveriam ser mesmo importantes para as pessoas que escrevem esses scripts. Só que, infelizmente, esses criadores de scripts nem sempre percebem a importância das relações. Mas tudo bem, porque na coluna deste mês vamos acertar isso.
Sabemos que muitos administradores de sistemas usam bancos de dados, em geral como uma maneira de acompanhar o inventário de hardware. O problema é que muitos desses bancos de dados são configurados de forma aquém do ideal. Suponha, por exemplo, que você precise de um banco de dados para acompanhar as unidades de disco conectadas a todos os seus computadores. Se não tiver algum conhecimento sobre a criação de bancos de dados, é bem provável que crie um banco de dados com uma só tabela como o da Figura 1.
Figura 1** Um banco de dados com uma só tabela **
Como você pode ver, trata-se de um design muito simples: há um campo para o nome do computador e alguns campos Sim/Não usados para indicar se o computador tem as unidades C: e D:. E é só isso. Claro, limpo e direto ao ponto.
Então, o que há de errado com esse banco de dados? Para falar a verdade, quase tudo. Se você tem certeza de que nenhum computador tem mais de duas unidades de disco, o design (quase) funcionará. Mas o que acontecerá se você tiver um computador com três unidades de disco? Nesse caso, você precisará adicionar outro campo para acompanhar a unidade E. Tudo bem, mas e se um computador tiver onze unidades de disco? E se você quiser acompanhar as propriedades de cada unidade? Suponha, por exemplo, que você queira acompanhar o tamanho total de cada unidade. Nesse caso, você precisará de um campo Drive_C_Size. E de um campo Drive_D_Size. E de um campo Drive_E_Size... Você já entendeu (e tomara que você não queira também controlar o espaço disponível em disco, o tipo de conector do disco, se as cotas de disco foram habilitadas na unidade e assim por diante).
É preciso ter em mente a seguinte regra: sempre que uma entidade puder ter mais de uma unidade de alguma coisa (como um computador, que pode ter mais de uma unidade de disco), esse design de "arquivo simples" (uma só tabela) não será recomendável. É claro que, no caso das unidades de disco, você considera possível que funcione; afinal, você terá apenas uma quantidade finita de unidades conectadas a um computador. Mas e se você quiser consultar seu banco de dados e obter uma lista de todas as unidades de disco de 100 gigabytes ou mais? Como seria possível fazer algo assim? Primeiro, você precisaria procurar as unidades de 100 gigabytes no campo Drive_C_Size; depois, seria preciso procurar a mesma coisa no campo Drive_D_Size, no campo Drive_E_Size... No fim das contas, não é uma abordagem muito eficaz. Mas qual é a alternativa? Se você não pode usar um banco de dados de arquivo simples, o que pode usar?
É simples: um banco de dados relacional. Os bancos de dados relacionais são criados, entre outras coisas, para lidar com relações um-para-muitos (por exemplo, um computador que pode ter várias unidades de disco). E, antes que você pergunte, não é preciso comprar nada novo para poder usar um banco de dados relacional. Se você já tem um banco de dados de qualquer tipo (Microsoft® Access™, SQL Server™, Oracle etc.), é provável que já tenha um banco de dados relacional. Não é preciso comprar nada; basta que você saiba duas coisas: 1) como configurar uma relação entre duas tabelas no banco de dados; e 2) como escrever um script capaz de tirar proveito dessa relação.
Observação: de acordo com a TV, você nunca deve tentar tirar proveito de uma relação. Mas aqui é diferente.
Como esta coluna não se chama Ei, Equipe de Design de Bancos de Dados!, não vamos gastar muito tempo discutindo o design de bancos de dados. Em vez disso, vamos apenas mostrar um design muito simples de banco de dados relacional, que nos permitirá abordar a escrita de consultas relacionais. Nesse banco de dados, tempos duas tabelas. A primeira é denominada Computers e consiste em dois campos: ComputerName e SerialNumber. O campo ComputerName é onde armazenamos o nome do computador; o campo SerialNumber é onde armazenamos — ah, você já sabe? Isso mesmo, o número de série.
Então, onde colocamos as informações sobre a unidade de disco? Na segunda tabela, denominada DiskDrives. Essa tabela contém três campos auto-explicativos: SerialNumber, DriveLetter e DriveSize.
O campo principal aqui é SerialNumber. Se esse nome de campo parece familiar, é mesmo: temos exatamente o mesmo campo na tabela Computers. E não se trata de mera coincidência; é proposital. Para estabelecer uma relação entre as tabelas, precisamos de um campo que apareça nas duas; é assim que podemos saber quais unidades de disco pertencem a quais computadores.
Por que vinculamos as duas tabelas usando o número de série e não, digamos, o nome do computador? Na verdade, há um bom motivo para isso: os nomes de computadores podem mudar (e mudam com freqüência). Os números de série, em geral, não mudam.
Mas chega de conversa. Vamos examinar a Figura 2, um script capaz de pesquisar nas duas tabelas e recuperar uma lista das unidades de disco pertencentes a cada computador. A Figura 3 mostra o tipo de dados que o script retorna.
Figure 3 Resultados da pesquisa
atl-ws-01
C:
atl-ws-01
D:
atl-ws-01
E:
atl-ws-02
C:
atl-ws-02
D:
Figure 2 Pesquisando nas tabelas
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
Não vamos abordar os conceitos básicos da conexão e do trabalho com bancos de dados na coluna deste mês. Se você é iniciante em script de bancos de dados, vale a pena consultar o webcast "Script de bancos de dados para administradores de sistemas" (go.microsoft.com/fwlink/?LinkId=22089) da Equipe de Scripts. Diremos apenas que estamos conectados a um banco de dados Access (C:\Scripts\Test.mdb) e trabalhando com tabelas relacionais denominadas Computers e DiskDrives. Isso deve ficar bem claro a partir do script.
Diremos também que você precisa fazer uma pequena alteração para que isso funcione no Access 2007: é importante que Provider seja alterado de Microsoft.Jet.OLEDB.4.0 para Microsoft.ACE.OLEDB.12.0, da seguinte forma:
objConnection.Open _
"Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = c:\scripts\test.accdb"
É isso. Agora, nos concentraremos na consulta SQL para recuperar dados das duas tabelas:
objRecordSet.Open "SELECT Computers.*, " & _
"DiskDrives.* FROM Computers " & _
"INNER JOIN DiskDrives ON " & _
"Computers.SerialNumber = " & _
"DiskDrives.SerialNumber " & _
"ORDER BY ComputerName, Drive", _
objConnection, adOpenStatic, adLockOptimistic
Complicado? Talvez seja um pouco. Então, vejamos se conseguimos dividir isso em partes e tornar mais fácil de entender.
A primeira parte da nossa consulta, na verdade, é bem simples. Queremos selecionar todos os campos das duas tabelas: Computers e DiskDrives. Por isso, usamos um pouco de SQL:
SELECT Computers.*, DiskDrives.*
Não é tão difícil; nem é preciso dizer que o asterisco funciona como um caractere curinga, que significa "tudo".
Se você já escreveu consultas SQL (ou se já escreveu consultas WMI, ou Instrumentação de Gerenciamento do Windows®, que usam um subconjunto da linguagem de consulta SQL), então conhece a rotina: depois de especificar os itens que deseja selecionar, você especifica onde deseja selecionar esses itens. Temos, assim, a próxima peça do quebra-cabeça da nossa consulta:
FROM Computers INNER JOIN DiskDrives ON Computers.SerialNumber = DiskDrives.SerialNumber
Não deixe que isso o desanime. É mesmo mais complicado do que uma instrução FROM típica, mas há um bom motivo para isso. Afinal, em uma consulta SQL típica, selecionamos dados apenas em uma tabela; agora, vamos selecionar dados em duas tabelas de uma só vez.
Vamos examinar a sintaxe em mais detalhes para ver como funciona. Como você pode ver, pedimos ao script para selecionar dados nas tabelas Computers e DiskDrives. Observe, no entanto, que não usamos a palavra "and"; em vez disso, usamos o termo INNER JOIN. Esse termo define o tipo de relação e, conseqüentemente, descreve como será feita a junção das informações de duas tabelas separadas em um só conjunto de registros (claro que há outros tipos de junções; trataremos deles mais adiante).
Como já deixamos implícito, cabe a nós especificar o tipo de relação existente entre as nossas tabelas. Também cabe a nós especificar exatamente como as tabelas se relacionam. É o que faz o trecho de código a seguir:
ON Computers.SerialNumber = DiskDrives.SerialNumber
O que estamos fazendo é definir como será a junção das tabelas. Os registros serão agrupados sempre que o campo SerialNumber na tabela Computers corresponder ao campo SerialNumber na tabela DiskDrives. E se tivéssemos usado um outro campo (digamos, ComputerName) como nosso campo de junção? O código ficaria assim:
ON Computers.ComputerName = DiskDrives.ComputerName
Na verdade, os dois campos não precisam ter o mesmo nome; basta que contenham os mesmos dados. Usar o mesmo nome torna mais fácil identificar o campo relacional. Há apenas um pequeno detalhe do qual é preciso lembrar-se: como temos dois campos com o mesmo nome, precisamos usar sempre a sintaxe TableName.FieldName quando nos referimos a um desses campos. Isso significa usar código como este: Wscript.Echo obj- Recordset.Fields.Item("Computers.SerialNumber").
Isso é tudo de que precisamos. O resto da consulta simplesmente classifica os dados, primeiro por nome do computador e depois por unidade:
ORDER BY ComputerName, Drive
Viu, não foi tão ruim assim, foi? Precisamos fazer uma pequena pausa para falar sobre junções. Por que usamos uma junção interna na nossa consulta? É fácil. Uma junção interna retorna somente registros com valores correspondentes em cada tabela. Em outras palavras, suponha que temos o número de série 121989. Se esse número de série aparecer nas tabelas Computers e DiskDrives, os registros correspondentes serão retornados (logicamente, não basta que o número de série apareça em qualquer lugar; ele precisa estar no campo SerialNumber). Isso faz sentido? Muito bem.
Agora, suponha que temos um computador com o número de série 121989; no entanto, não há unidades de disco com esse número de série. Nesse caso, não serão retornados dados sobre o computador com o número de série 121989. Isso ocorre porque uma junção interna só retorna informações se um registro tiver valores correspondentes em cada uma das tabelas associadas.
Em outras palavras, uma consulta de junção interna retorna todos os computadores com discos rígidos. Ela não retornará os computadores que não têm discos rígidos, ou então os discos rígidos que não estão instalados em um computador. Isso é bom, pois geralmente é o que queremos. Mas e se você quiser uma lista dos computadores que não têm discos rígidos, ou então dos discos rígidos sem computadores? O que fazer?
É para isso que serve a junção externa (ah, então é para isso que serve a junção externa!). No momento, diremos que existem apenas dois tipos de junções externas: Left e Right. Em nosso banco de dados, tempos duas tabelas: Computers (considerada a tabela "esquerda", por ser a tabela mestra) e DiskDrives (considerada a tabela "direita", bem... por não ser a tabela mestra). Suponha que o conjunto de registros retornado precisa incluir todos os computadores, até mesmo aqueles que não têm discos rígidos instalados. Nesse caso, usamos a sintaxe LEFT OUTER JOIN e uma consulta como esta:
objRecordSet.Open "SELECT Computers.*, " & _
"DiskDrives.* FROM Computers " & _
"LEFT OUTER JOIN DiskDrives ON " & _
"Computers.SerialNumber = " & _
"DiskDrives.SerialNumber " & _
"ORDER BY ComputerName, Drive", _
objConnection, adOpenStatic, adLockOptimistic
Como você provavelmente já percebeu, uma junção externa esquerda retorna todos os registros na tabela esquerda, mesmo que não haja registros correspondentes na tabela direita. Então, obtemos todos os computadores, mas somente registros da tabela direita (unidades de disco) associados a um computador.
Existe também a possibilidade de querermos uma lista de todas as unidades de disco, inclusive aquelas que não estão instaladas em um computador. Como DiskDrives é a tabela direita na relação, usamos a sintaxe RIGHT OUTER JOIN desta forma:
objRecordSet.Open "SELECT Computers.*, " & _
"DiskDrives.* FROM Computers " & _
"RIGHT OUTER JOIN DiskDrives ON " & _
"Computers.SerialNumber = " & _
"DiskDrives.SerialNumber " & _
"ORDER BY ComputerName, Drive", _
objConnection, adOpenStatic, adLockOptimistic
Pensando bem no assunto, se DiskDrives é a tabela direita na relação, então Computers deveria ser conhecida como a tabela "marido" na relação, em vez de tabela esquerda. Afinal, como alguns membros da Equipe de Scripts já aprenderam com a dura experiência, o marido nunca é a entidade direita em uma relação.
Com a junção externa direita, obtemos todos os registros na tabela direita (todas as unidades de disco), mas somente computadores (registros na tabela esquerda) associados a uma unidade de disco.
Talvez isso não faça muito sentido no momento mas, se você treinar um pouco, entenderá como funciona. Se precisar de um banco de dados para fazer experiências, postamos um exemplo em microsoft.com/technet/scriptcenter/resources/tnmag/archive.mspx.
É importante mencionar que, sempre que você usar uma junção externa, deverá também, pelo menos, usar On Error Resume Next na próxima linha do seu script. Por quê? Bem, suponha que seja usada uma junção externa esquerda; nesse caso, é possível que sejam obtidos alguns computadores sem discos rígidos instalados. Tudo bem (afinal, é isso mesmo que queremos obter), pelo menos até encontrarmos esta linha de código, que retorna a letra da unidade:
Wscript.Echo objRecordset.Fields.Item("Drive")
Como não existe nenhum campo Drive neste computador específico, o script será interrompido por uma falha:
C:\Scripts\database.vbs(22, 9) Microsoft VBScript runtime error: Type mismatch
No entanto, se implementarmos On Error Resume Next, o script poderá ignorar o fato de que um computador não tem discos rígidos e seguir seu caminho. Como alternativa, você pode também usar um código como este para verificar o valor do campo Drive e adotar a ação adequada:
If IsNull(objRecordset.Fields.Item("Drive")) _Then
Wscript.Echo "No disk drives installed."
Else
Wscript.Echo _
objRecordset.Fields.Item("Drive")
End If
Com esse código, verificamos se um valor Null é obtido, em vez de uma letra de unidade. Se isso acontecer, será recuperada a mensagem "No disk drives installed" (Nenhuma unidade de disco instalada). Se não obtivermos um valor Null, simplesmente será recuperado o valor do campo Drive. O resultado é uma saída como a mostrada na Figura 4.
Figure 4 Exibição dos resultados apropriados
atl-ws-01
C:
atl-ws-01
D:
atl-ws-02
C:
atl-ws-02
D:
alt-ws-03
No disk drives installed.
Sem entrar em explicações muito detalhadas, vejamos mais algumas consultas. Eis aqui, por exemplo, uma consulta de junção interna que retorna uma lista de computadores e suas unidades de disco instaladas, desde que tenham mais de 50 gigabytes (supondo que os tamanhos das unidades sejam armazenados em gigabytes):
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
Como você pode ver claramente, a única coisa que fizemos foi adicionar uma cláusula WHERE padrão à consulta original:
WHERE DriveSize > 50
E se quiséssemos apenas informações sobre a unidade E dos computadores? Sem problemas. Basta adicionar a cláusula Where apropriada:
WHERE Drive = 'E:'
Eis aqui uma consulta um pouco mais complicada, que retorna uma coleção de computadores que não têm unidades de disco instaladas:
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
Como avisamos, essa é um pouco mais complicada e, como não resta muito espaço, não vamos explicar como funciona. Mas funciona, e isso é o mais importante.
Bem, talvez a segunda coisa mais importante. Afinal, como já dissemos várias vezes, o que mais importa são as relações. Mas isso não significa que as relações não possam dar errado. Como diz Woody Allen no final de "Noivo neurótico, noiva nervosa", "Uma relação... é como um tubarão; precisa seguir em frente constantemente, ou então morre. E acho que temos aqui um tubarão morto". Ah, se o Woody conhecesse as junções internas e externas... Com essas técnicas de consulta, suas relações serão sempre bem-sucedidas!
A Equipe de Scripts da Microsoft trabalha para a — bem, é empregada da — Microsoft. Quando não está jogando/treinando/assistindo beisebol (e diversas outras atividades), ela administra o Script Center da TechNet. Confira no site www.scriptingguys.com.
© 2008 Microsoft Corporation e CMP Media, LLC. Todos os direitos reservados. A reprodução parcial ou completa sem autorização é proibida..