• [アーティクル]

Hey, Scripting Guy!このリレーションシップを救うことはできますか

Microsoft Scripting Guys

この記事で使用しているコードのダウンロード: HeyScriptingGuy2007_09.exe (150KB)

リレーションシップ (関係) はとても重要です。でも、どうして他のだれでもなく Scripting Guys が、関係の重要性を知っているのでしょうか。まず、1 つには、Scripting Guys はテレビをたくさん見るからです。テレビではいつも関係が重要だと言っています。

関係が重要なのは、連続ドラマやテレビ映画の中だけのことではありません。たとえば、ある給料日ローン会社のコマーシャルでは、視聴者にその会社のビジネスを支援してほしいと働きかけています。もともとローンを組むつもりがない人にお金を貸したいからでしょうか。いいえ、違います。では、ローンで 900% の年利を徴収するつもりだからでしょうか。もちろんそうではありません。この給料日ローン会社は、皆さんと個人的な関係を形成したいのです。この会社のコマーシャルが示すように、"関係は重要" なのです。

注 : 確かに、Scripting Guys は泣いています。でもそのことを恥ずかしいとは思いません。結局のところ、皆さんが冷たく無神経にならざるを得ないのは、ある人と給料日ローン会社を結び付ける深く揺るぎない関係について、ちょっと考えただけで涙してしまわないようにするためなのです。

もちろん、皆さんが言いたいことはわかっています。「おいおい、給料日ローン会社にとって関係が重要だと言うなら、データベースを操作するスクリプトを記述する人にとって関係は本当に重要であるに違いない。」と言いたいのですよね。実際、データベースを操作するスクリプトを記述する人々にとってリレーションシップ (関係) は重要です。しかし、残念ながら、このようなスクリプト作成者たちは関係が重要であるということを理解しているとは限りません。でも、心配はいりません。今月のコラムで Scripting Guys が皆さんに正しい情報をお伝えします。

多くのシステム管理者は、よくハードウェア インベントリの追跡方法としてデータベースを使用します。問題は、このようなデータベースの多くが、決して最適とは言えない方法でセットアップされているということです。たとえば、すべてのコンピュータに搭載されているディスク ドライブを追跡するデータベースが必要だとします。データベース設計の経験がない場合は、図 1 のような 1 つのテーブルで構成されたデータベースを作成する可能性が高いでしょう。

図 1 1 つのテーブルで構成されたデータベース

図 1** 1 つのテーブルで構成されたデータベース **

ご覧のとおり、このデータベースの設計はとても単純です。コンピュータ名用のフィールドが 1 つあり、コンピュータに C ドライブと D ドライブがあるかどうかを Yes/No で示すフィールドが 2 つあります。それだけです。簡潔で、すっきりしていて、要領を得ています。

では、このデータベースのどこに問題があるのでしょうか。実を言うと、ほとんどすべてに問題があります。どのコンピュータにもディスク ドライブが 2 個以上搭載されていないことが確実な場合には、この設計は (ある程度) 機能します。しかし、ディスク ドライブが 3 個搭載されたコンピュータがある場合はどうなるでしょうか。その場合は、ドライブ E の追跡をするためのフィールドをもう 1 つ追加する必要があります。これは、それほど大きな問題ではありません。しかし、ディスク ドライブが 11 個あるコンピュータの場合はどうなるでしょうか。そして、各ドライブのプロパティを追跡する必要がある場合はどうなるでしょうか。たとえば、各ドライブの合計サイズを把握する必要があるとします。その場合は、Drive_C_Size というフィールドが必要になります。それから、Drive_D_Size というフィールドも必要です。また、Drive_E_Size というフィールドも必要です。ここで説明しようとしていることが、だんだん、わかってきていただけたと思います (使用可能なディスク領域、ディスクのコネクタの種類、ドライブでディスク クォータが有効になってるかどうかなどについても追跡するなんて、とんでもありません)。

この経験に基づいて得られた法則を覚えておいてください。あるエンティティが、あるものを 1 つ以上持つことができる場合 (たとえば、コンピュータは 1 個以上のディスク ドライブを搭載できます)、この "フラット ファイル" (1 つのテーブルで構成されたデータベース) の設計は、適切ではありません。確かに、コンピュータに接続されるディスク ドライブの数には制限があるので、ディスク ドライブについては、フラット ファイルを使用して管理することは可能だと思うかもしれません。しかし、データベースを照会して、100 GB 以上の容量のすべてのディスク ドライブの一覧を取得する必要がある場合はどうでしょうか。このような操作を行うには、どうすればよいでしょうか。まず、Drive_C_Size フィールドで 100 GB のドライブを探し、それから、Drive_D_Size フィールド、Drive_E_Size フィールドでも同様に探す必要があります。全体的に見れば、とても効率的または有効的なアプローチとは言えません。では、他にどんな方法があるのでしょうか。フラット ファイルのデータベースを使用できない場合は、何を使用できるのでしょうか。

答えは簡単です。リレーショナル データベースを使用します。リレーショナル データベースは、特に一対多のリレーションシップ (たとえば、多数のディスク ドライブを搭載できる 1 台のコンピュータ) を扱うために設計されました。それから、聞かれる前に言っておきますが、リレーショナル データベースを使用するために、出かけていって何か新しいものを購入する必要はありません。どんな種類のデータベースでも (Microsoft® Access™、SQL Server™、Oracle など)、データベースには既にリレーショナル データベースが用意されている可能性が高いからです。何も購入する必要はありませんが、次の 2 つの方法だけを知っておく必要があります。1) データベースで、2 つのテーブル間のリレーションシップをセットアップする方法と、2) そのリレーションシップを利用できるスクリプトを記述する方法です。

注 : テレビによると、リレーションシップ (関係) は絶対に利用しないほうがよいのですが、データベースに関しては違います。

このコラムは Hey,Database Design Guy! ではないので、データベース設計の説明に多くの時間を費やすつもりはありません。代わりに、リレーショナル クエリの記述について説明できるように、とても単純なリレーショナル データベース設計を紹介するにとどめます。このデータベースには 2 つのテーブルがあります。1 つ目は Computers という名前で、ComputerName と SerialNumber という 2 つのフィールドで構成されています。ComputerName フィールドにはコンピュータ名が格納され、SerialNumber フィールドにはお察しのとおりのものが格納されます。そうです、シリアル番号です。

では、いったいどこにディスク ドライブの情報が格納されるのでしょうか。それは 2 つ目の DiskDrives という名前のテーブルに格納されます。このテーブルには、SerialNumber、DriveLetter、および DriveSize という名前の一目瞭然な 3 つのフィールドがあります。

ここで重要なフィールドは SerialNumber です。このフィールド名に聞き覚えがあるなら、それは当然です。Computers テーブルにもまったく同じ名前のフィールドがあります。これは事故や偶然によるものではなく、わざとそうしました。これら 2 つのテーブル間にリレーションシップを確立するためには、どちらのテーブルにも存在するフィールドが 1 つ必要です。このようなフィールドにより、どのディスク ドライブがどのコンピュータに属しているかを区別できます。

なぜ、2 つのテーブルをリンクさせるのに、たとえばコンピュータ名ではなくシリアル番号を使用したのでしょうか。これについては正当な理由があります。コンピュータ名は変更できます (また、変更することが多いです) が、一般的に、シリアル番号は変更されないからです。

データベース設計についての説明は十分でしょう。図 2 を見てみましょう。これは、Computers と Disk Drive の 2 つのテーブルを検索して、各コンピュータに搭載されているディスク ドライブの一覧を取得できるスクリプトです。図 3 は、このスクリプトで返されるデータです。

Figure 3 検索結果

atl-ws-01
C:

atl-ws-01
D:

atl-ws-01
E:

atl-ws-02
C:

atl-ws-02
D:

Figure 2 テーブルの検索

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

今月のコラムでは、データベースへの接続や操作の基本事項について説明するつもりはありません。データベースをスクリプトで操作することに不慣れな場合は、Scripting Guys の Web キャスト「システム管理者のためのスクリプトによるデータベースの操作」(go.microsoft.com/fwlink/?LinkId=22089) を参照してください。Scripting Guys が説明するのは、今 Access データベース (C:\Scripts\Test.mdb) に接続しているということと、Computers および DiskDrives という名前のリレーショナル テーブルを操作しているということだけです。これは、スクリプトを見れば非常に明快なことです。

それから、もう 1 つ説明できるのは、このスクリプトを Access 2007 で動作させるために 1 つちょっとした変更が必要だということです。次のようにプロバイダを Microsoft.Jet.OLEDB.4.0 から Microsoft.ACE.OLEDB.12.0 に変更する必要があります。これは重要です。

objConnection.Open _
 "Provider = Microsoft.ACE.OLEDB.12.0; " & _
 "Data Source = c:\scripts\test.accdb"

これで完了です。次に、この 2 つのテーブルからデータを取得する SQL クエリについて説明しましょう。

objRecordSet.Open "SELECT Computers.*, " & _
 "DiskDrives.* FROM Computers " & _
 "INNER JOIN DiskDrives ON " & _
 "Computers.SerialNumber = " & _
 "DiskDrives.SerialNumber " & _
 "ORDER BY ComputerName, Drive", _
 objConnection, adOpenStatic, adLockOptimistic

このクエリは複雑でしょうか。もしかすると、少し複雑かもしれません。では、このクエリを少し分解して、わかりやすくしてみましょう。

クエリの最初の部分は実際非常に簡単です。Computers テーブルと DiskDrives テーブルの両方にあるすべてのフィールドを選択する必要があるので、次のような SQL クエリがあります。

SELECT Computers.*, DiskDrives.*

この部分は、それほど難しくないでしょう。もちろん、アスタリスクは、"すべて" を意味する単なるワイルドカード文字です。

以前に SQL クエリを記述したことがある (または SQL クエリ言語のサブセットを使用する Windows® Management Instrumentation (WMI) クエリを記述したことがある) 場合、手順はおわかりでしょう。選択する必要があるアイテムを指定したら、これらのアイテムを選択してくる場所を指定します。したがって、クエリ パズルの次のピースは下記のようになります。

FROM Computers INNER JOIN DiskDrives ON Computers.SerialNumber = DiskDrives.SerialNumber

このスクリプトを見てうろたえないでください。確かに、これは典型的な FROM ステートメントよりも複雑ですが、このステートメントを使用するには正当な理由があります。典型的な SQL クエリでは 1 つのテーブルからデータを選択しますが、今回は一度に 2 つのテーブルからデータを選択する必要があるため、このような複雑な FROM ステートメントを使用しています。

この構文とそれがどのように機能するのかについて詳しく見ていきましょう。ご覧のとおり、スクリプトでは Computers テーブルと DiskDrives テーブルからデータを選択しています。ただし、and は使用せず、代わりに INNER JOIN という表現を使用しています。この表現は、リレーションシップの種類を定義しています。つまり、どのようにして 2 つの異なるテーブルからの情報を 1 つのレコードセットに結合するかを示しています (結合には他にも種類がありますが、それらについては少し後で説明します)。

先ほど説明したとおり、テーブル間のリレーションシップの種類は自由に指定できます。また、それらのテーブルがどう関係付けるかについても厳密に指定できます。次のコード部分では、以上のような処理を行います。

ON Computers.SerialNumber = DiskDrives.SerialNumber

ここでは、テーブルの結合方法を定義しています。Computers テーブルの SerialNumber フィールドと DiskDrives テーブルの SerialNumber フィールドが一致するレコードをグループにまとめます。もし異なるフィールド (たとえば、ComputerName) を結合フィールドとして使用していたとしたら、どうなるでしょうか。その場合は、コードは次のようになります。

ON Computers.ComputerName = DiskDrives.ComputerName

疑問に思っている読者のために一言付け加えておきますが、2 つのフィールドが同じ名前である必要はありません。必要なのは、同じデータが含まれているということだけです。ただし、同じ名前を使用すると、リレーショナル フィールドを特定しやすくなります。ここで、1 つだけ少し気を付けておくことがあります。同じ名前のフィールドが 2 つあるので、これらのフィールドのうちの 1 つを参照する場合には、常に「テーブル名.フィールド名」という構文を使用する必要があります。つまり、Wscript.Echo obj- Recordset.Fields.Item("Computers.SerialNumber") のようなコードを使用します。

これで必要な作業はすべて完了です。クエリの残りの部分では、データを、コンピュータ名で並べ替えてから、ドライブ名で並べ替えているだけです。

ORDER BY ComputerName, Drive

それほど複雑ではなかったでしょう。ちょっと中断して、今度は結合について説明しましょう。なぜ、このクエリでは内部結合を使用したのでしょうか。答えは簡単です。内部結合は、各テーブルで一致する値のあるレコードだけを返します。つまり、シリアル番号 121989 があると仮定すると、このシリアル番号が Computers と DiskDrives テーブルの両方にある場合に、対応するレコードが返されます (もちろん、シリアル番号はどこにでも格納できるわけではなく、SerialNumber フィールドに格納されています)。わかりますよね。よかった。

シリアル番号が 121989 のコンピュータがあるとします。ただし、そのシリアル番号のディスク ドライブはありません。このような場合は、シリアル番号 121989 のコンピュータのデータは返されません。これは、内部結合が、各結合テーブル内のレコードに一致する値がある場合だけ情報を返すからです。

つまり、内部結合クエリはハード ディスクを搭載しているすべてのコンピュータを返しますが、ハード ディスクが搭載されていないコンピュータとコンピュータに搭載されていないハード ディスクは返しません。多くの場合、このような情報を必要とするので、これは便利です。しかし、ハード ディスクを搭載していないコンピュータの一覧やコンピュータに搭載されていないハード ディスクの一覧が必要な場合はどうでしょうか。そのときはどうすればよいのでしょうか。

このような場合には、外部結合の出番となります (そうです、外部結合は、そのためのものなのです)。今回のコラムでは、外部結合には左外部結合と右外部結合の 2 種類だけしかないということにさせてください。このデータベースには 2 つのテーブルがあります。Computers テーブル (マスタ テーブルなので "左" テーブルと見なされます) と DiskDrives テーブル (マスタ テーブルではないので "右" テーブルと見なされます) です。返されるレコードセットに、ディスク ドライブが搭載されていないコンピュータも含め、すべてのコンピュータが含まれている必要があるとします。このような場合は、LEFT OUTER JOIN 構文を使用し、クエリは次のようになります。

objRecordSet.Open "SELECT Computers.*, " & _
 "DiskDrives.* FROM Computers " & _
 "LEFT OUTER JOIN DiskDrives ON " & _
 "Computers.SerialNumber = " & _
 "DiskDrives.SerialNumber " & _
 "ORDER BY ComputerName, Drive", _
 objConnection, adOpenStatic, adLockOptimistic

おそらくおわかりだと思いますが、左外部結合では右テーブルに対応するレコードがなくても、左テーブルのすべてのレコードが返されます。つまり、すべてのコンピュータが返されますが、右テーブル (DiskDrives) から返されるのはコンピュータと関連付けられているレコードだけです。

また、コンピュータに搭載されていないハード ディスクも含めた、すべてのハード ディスクの一覧が必要になる場合があります。この関係において DiskDrives テーブルは右テーブルなので、次のように RIGHT OUTER JOIN 構文を使用します。

objRecordSet.Open "SELECT Computers.*, " & _
 "DiskDrives.* FROM Computers " & _
 "RIGHT OUTER JOIN DiskDrives ON " & _
 "Computers.SerialNumber = " & _
 "DiskDrives.SerialNumber " & _
 "ORDER BY ComputerName, Drive", _
 objConnection, adOpenStatic, adLockOptimistic

さて、考えてみたのですが、この関係において DiskDrives が右テーブルならば、Computers は左テーブルではなく、夫テーブルと呼ばれてもよいのではないでしょうか。しかし、Scripting Guys の何人かが辛い経験から知っているとおり、夫婦という関係において、夫は決して権利のあるエンティティ (存在) ではないのです。

右外部結合を使用すると、右テーブルのすべてのレコード (すべてのディスク ドライブ) が返されますが、コンピュータ (左テーブルのレコード) については、ディスク ドライブと関連付けられているレコードだけが返されます。

今はこの原理があまりわからないかもしれませんが、データベースを少し操作してみれば、どのように動作するかを理解できると思います。操作するデータベースが必要な場合は、microsoft.com/technet/scriptcenter/resources/tnmag/archive.mspx でサンプルを提供しているのでご利用ください。

外部結合を使用する場合は、少なくともスクリプトの最初の行に On Error Resume Next を記述する必要があります。これは重要です。その理由は、ここで左外部結合を実行すると仮定すると、ハード ディスクが搭載されていないコンピュータを取得する可能性があるからです。ハード ディスクが搭載されていないコンピュータも返す必要があったので、少なくとも、ドライブ文字を表示する次のコード行に遭遇するまでの間なら、これで問題ありません。

Wscript.Echo objRecordset.Fields.Item("Drive")

ハード ディスクが搭載されていないコンピュータには Drive フィールドがないので、スクリプトは次のエラーで動作を停止してしまいます。

C:\Scripts\database.vbs(22, 9) Microsoft VBScript runtime error: Type mismatch

ただし、On Error Resume Next を実装すると、スクリプトはコンピュータにディスク ドライブがないという事実を無視することができ、そのまま処理を続けることができます。また、次のようなコードを使用すると、Drive フィールドの値を確認してから適切なアクションを実行することができます。

If IsNull(objRecordset.Fields.Item("Drive")) _Then
    Wscript.Echo "No disk drives installed."
Else
    Wscript.Echo _
      objRecordset.Fields.Item("Drive")
End If

このコードを使用すると、実際のドライブ文字ではなく、Null 値が返されるかどうかを確認できます。Null 値が返された場合には、"No disk drives installed." というメッセージを表示します。Null 値が返されない場合は、単純に Drive フィールドの値を表示します。図 4 に示すような最終結果が得られます。

Figure 4 適切な結果の表示

atl-ws-01
C:

atl-ws-01
D:

atl-ws-02
C:

atl-ws-02
D:

alt-ws-03
No disk drives installed.

詳しくは説明しませんが、他にもいくつかのクエリを見てみましょう。たとえば、次にコンピュータの一覧とそのコンピュータに搭載されたディスク ドライブの一覧を返す内部結合のクエリを示します。このクエリでは、ディスク ドライブが 50 GB より大きいという条件を指定しています (ドライブ サイズは GB 単位で格納されていると仮定しています)。

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

おわかりのように、ここでは一般的な WHERE 句を元のクエリに追加しただけです。

WHERE DriveSize > 50

では、コンピュータのドライブ E に関する情報だけが必要な場合はどうすればよいでしょうか。この場合も、次のように適切な WHERE 句を追加するだけでよいのです。

WHERE Drive = 'E:'

次は多少複雑なクエリです。このクエリでは、ディスク ドライブが搭載されていないコンピュータのコレクションを返します。

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

既に言及したように、このクエリは少し複雑ですが、スペースが不足してきたので動作については説明しません。しかし、このクエリは正常に実行されます。それが最も重要なことなのです。

そして、おそらくその次に最も重要なのは、何度も指摘したように、本当に重要なのはリレーションシップ (関係) だということです。しかし、常に良好な関係を保てるとは限りません。「アニー・ホール」の最後でウディ・アレンがこう言っています。「関係というのはサメと同じで常に前進してないと死ぬ。僕たちの関係はサメの死骸だ。」と。ウディが内部結合と外部結合について知っていれば、このような結末にはならなかったと思います。このコラムで紹介したクエリ技術を使えば、常に適切な関係 (リレーションシップ) を築くことができます。

Microsoft Scripting Guys は、マイクロソフトの仕事をしています、というよりもマイクロソフトにより雇われています。野球をプレイしたり監督したり観戦したり (または他のさまざまな活動を) しているのでない限り、彼らは TechNet スクリプト センターを運営しています。詳細については、www.scriptingguys.com を参照してください。

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; 許可なしに一部または全体を複製することは禁止されています.