データベース再入門：データベースの基本構造を理解しよう

データベースとは何か～ISAMからRDBMSまで

データベース再入門：

データベースの基本構造を理解しよう

酒井法雄 SAKAI, Norio norio@int21.co.jp　　

RDBMSを使う人が増えている．Visual BasicでもRDBMSを使う仕組みが強化されてきた．しかし，RDBMSは難しい．一朝一夕には使えるようにならないのだ．
なぜRDBMSを理解できないかといえば，実はそれ以前に「データベースとはなんぞや」という部分が欠落したままRDBMSにチャレンジしているケースが多いからのようだ．
「なんぞや」を理解する一番よい方法，それは自分で作ってみることだ．
そこで，ここではデータベースをより理解するために，DAOなどの既存のデータアクセス手法を使わずに，独自にデータベースを作ってみることにする．カンタンなものからはじめて，最終的にはSQL Serverモドキを作り，どのような構造になっているかを理解しよう．

データベースを理解するために

　RDBMS（Relational Data Base Management System）は，現在のパーソナルコンピュータでの重要なファクターである．一般的なRDBMSとしては，Oracle7あるいはOracle8や，Microsoft SQL Serverなどが挙げられる．
　さて，今回はこのRDBMSの特集であるが，この項ではRDBMSを活用する実用的な話はしない．というのも，RDBMS以前の部分がわかっていないと話にならないからだ．それなりに長いことデータベース関係の仕事をしていた方でも，意外にデータベースの基本的なことがわかっていないケースが多いようだ．だからと言って，「こうすべし」ばかりの押しつけでは実にならない．
　そこで，データベースとは何かということを身をもって知っていただくため，既存のデータアクセスエンジンを一切使わず，データベース，あるいはデータベースエンジンを作ることにしたい．最終的には，無謀にもSQLモドキのDBMSサーバーとミドルウェア，クライアントプログラムまで作ってしまおう．その過程で，RDBMS自体についても順次述べていくことにしたい．
　データを効率的に扱う方法を考えて作っていけば，現実のRDBMSの設計や運用，アクセス方法などで，パフォーマンスを向上させる方法や，やってはいけないことなども見えてくるはずだ．
　ここでは，まずデータベース，そしてデータベースの発展系であるDBMS，そしてその一形態のRDBMSとは何かについて概論を述べ，実際にデータベースエンジンを作ってみることにする．

データベースとは何か

　まずは，データベースとは何かを考えてみよう．
　トップダウンに言えば，データベースとは，データを貯蔵し，それをリクエストに合わせてデータを出力，追加，修正するプログラムだ．しかし，本来はその目的を考えるべきである．
　私たちは，コンピュータを使ってデータ処理をしている．ワープロに文章を書いてファイルに保存するのも，データ処理である．また，インターネットにアクセスして，画像や音声などのファイルをダウンロードし，それらを系統的に整理するといったこともデータ処理のひとつだ．
　このように，コンピュータを使って保存し，コンピュータを使って認識できるようなデータを扱うことが，データベースの基本である．文書ファイルにしても，ピクチャーファイルにしても，目的のものをいち速く探し出したり，一部を修正したり，はたまた削除したり，新しいものを作ったりといったことをするのは一緒である．これを，私たちはより効率的に行ないたいと考えてきた．
　この，効率的にデータを扱うための仕組みがデータベースである．端的に言えば，データベースとは，データを大量に貯蓄し，目的のものをすばやく出し，すばやく内容を変えるためのシステムなのである．
　こう考えてくると，Windowsのファイルシステム自体がデータベースと呼べなくもない．しかし，一般にデータベースは，より正規化されたデータが秩序正しく収められているものを言う．

DBMSとは何か

　データをよりたやすく扱おうとすると，そのための仕組みが必要になる．これをDBMS（Data Base Management System）と呼ぶ．
　単にデータベースと呼んだときには，パーソナル向けのものも含まれるが，DBMSと言うときには，いわゆるクライアント／サーバー（以下C/S）型のように，複数のユーザー間でデータを共有できるものを言う．さらに，一般的には次のような条件を備えていることが必要だ．

トランザクション
　データを処理するときには，複数の操作が完結して初めて完了するものがある．よく言われる例として，振込処理がある．振込は，次のような処理が必要になる．

1.振込元口座の値を取り出し
2.そこから振込金額と手数料をマイナスし
3.元口座に書き戻し
4.振込先口座の値を取り出し
5.振込金額をプラスし
6.元口座に書き戻す

可用性
　DBMSは複数ユーザーが大量のデータを扱うことを前提としている．したがって，データが破壊されたりしたときの対処が問題になる．これには，ハードウェア的障害と，ソフトウェア的障害がある．
　ハードウェア的障害に備えるには，RAIDなどを使って複数ドライブをミラーリングすることで対処する．
　ハードウェア的には問題ないが，データの内容に不整合が生じるといった，ソフトウェア的障害のときには，さまざまな原因が考えられる．DBMSでは，基本的にはバックアップを取って対処する．それもたとえば，週に一度はデータベース全体のバックアップをし，1日に一度は処理の変更履歴のみをバックアップするといったやり方である．あるいは，変更履歴は別ドライブにして対処するといったことも考えられる．このようにしておけば，後からバックアップを元にしてトランザクション単位でデータを復旧することができる．
　実際に運用するときには，このあたりの計画も含めて，綿密なシステムを考える必要がある．

データモデル
　データは特定のデータ構造を使って表わされるモデルに当てはめられる．このモデルはデータベースの仕組みによって異なり，リレーショナル型であれば表の行と列，オブジェクト指向型であればオブジェクトやクラス，インスタンスなどになる．
　このようなモデルは，特定のアプリケーションに依存しない汎用なものとして定義される．
　DBMS設計で最も重要なのはこの部分である．

RDBMSとは何か

　RDBMSのRは，Relationalの略であり，これはDBMSの一形態である．リレーショナル型の他にも，ネットワーク型や階層型と呼ばれるDBMSがあるが，今はデータベースと言えばリレーショナル型が主流である．また，昨今はオブジェクト指向型のDBMSも注目されてきている．
　リレーショナルモデルは，1970年に米国IBMのDr. E.F.Coddが提案したものだ．これはカンタンに言えば集合論をデータベースの世界に持ち込んだもので，当初は学術的色彩も強く，また，ハードウェアやソフトウェアも未熟だったため，実用化されはじめたのは，1980年ごろからである．
　リレーショナル型の特徴は，データを複数の表形式として扱うことにある．表とは，横にデータ項目を示すCol（カラム・列），縦にレコードを示すRow（ロウ・行）の2次元配列として表わすものだ．複雑なデータも，複数の表にわけて正規化すれば，それぞれは単純化できる．これらの別々の表を組み合わせ，その関連（Relational）を定義し，効率よくアクセスし，メインテンナンス効率も上げようというのだ．

図1：リレーショナルモデルの図

(57KB)

　ここでは，Visual Basicに附属のBIBLIO.MDBの内容を例に説明しよう．MDBはDBMSとして備えなくてはならない条件を満たしていないので，DBMSとはいえないのだが，リレーショナル型のモデルを使っている．このデータベースは，書籍に関するものである．書籍には，タイトル，ISBN，出版社，出版社についての情報，筆者名，筆者についての情報といったデータがある．これをひとつの表形式で表わすことも，もちろん可能だ．
　といっても，複数の書籍を発売している出版社はふつうだし，複数の書籍を執筆しているライターもいるだろう．となると，書籍名をベースにして考えると，同じ出版社や筆者についての情報が，あちこちに出てくることになる．仮に翔泳社の書籍が10冊あったとすると，もし出版社が引っ越ししてデータが変更されたとすると，10か所を修正しなくてはならない．これでは効率が悪すぎる．
　そこで，次の4つの表にわけて考えてみよう．

Titles	書籍自体の情報が入っている
Publishers	出版社の情報が入っている
Authors	筆者の情報が入っている
Title Authors	書籍のISBN番号と筆者のIDが入っている

図2：書名は重複することがあるが
ISBNコードはユニークである

(31KB)

　ここで重要なのは，キーの設定である．
　もし偶然にも同じ名前の本の本があったらどうだろう？　区別がつかなくなってしまうではないか．そこで，表には必ず唯一無二であることを示す，すなわちユニークな情報が必要になる．Titlesであれば，書籍の番号であるISBNがユニークである．そこで，ISBNをキーとすれば，レコードを特定することができるわけだ（図2）．

　書籍を特定すれば，PubIDも決まる．これは，出版社の表PublishersのキーであるPubIDと同じであるから，ここから出版社についての情報を得ることができる．
　また，ISBNが決まれば，そこからTitle Author表を見て，Au_IDを得ることができる．これは，筆者の表AuthorsのキーであるAu_IDと同じだから，筆者についての情報も得られるというわけだ．
　このように，現実世界の複雑なデータを，複数の表にうまく分解することが，RDBMS構築の鍵である．この設計こそが一番重要なことなのだ．このように，現実世界のものを表にしてうまくアクセスできるように設計することを，データモデリングと呼ぶ．

ミドルウェアとは何か

図3：ODBCの基本構造

図4：MS SQL Serverへの接続

図5：Oracleへの接続

　たとえば，DAOは元々はAccessのデータベースエンジンである．DBMSのエンジンは後述するようにISAMと呼ばれるインデックスファイルにより高速なアクセス方法を取るもので，それにSQLの構文解析エンジンを搭載している．ところが，ISAMは，インデックスファイルがないと基本的にアクセスできない．つまり，インデックス情報をサーバー側から取得しないと高速に動作しないのである．
　DAOでは，図6のように複雑な形になっているが，あくまでも中身はISAMである．元々ISAMとは次元の違うところにあるRDBMSを利用するのに，ローカルにISAMが必要なのだから，パフォーマンスが悪くなってしまうのである．

図6：DAO使用時の問題

　もっとも，これを改善すべくMicrosoftはDAOにもさまざまな拡張をしてきている．しかし，他のミドルウェア，たとえばRDOやOracle Object for OLEを使えば，より柔軟性の高いシステムを構築できるのだから，DAOやAccessを使ってC/Sの開発をする意味はないと言ってよい．むしろ，そのために起きる弊害の方が大きいのである．

SQLとは何か

　SQLは，元々はIBM社のデータベース言語だったが，RDBMSと共に発展してきた言語である．1986年以来，SQLはANSIで，そしてISOで規格されるようになったが，実際には各ベンダーの独自色が強く，本当に共通して使える部分は意外に少ない．たとえば，SQL-92準拠といったところで，実はSQL-92には「基本」「中間」「フル」の三つのレベルがあり，フル実装されているものはまだない（と思う）．
　一般にはRDBMSへの問い合わせ言語といわれるSQLだが，そのせいか「SELECT」「INSERT INTO」「UPDATE」などは有名だが，その他のSQLを知らないという人も少なくない．
　実はSQLには，次の三つのものがある．

・データ定義言語（DDL）：データベースやデータ構造の定義や管理をする
・データ操作言語（DML）：データの問い合わせや操作をするために使われる
・データ制御言語（DCL）：データベースへのアクセス許可などを管理する

　SQL ServerやOracleなどでも，GUIベースのツールが標準でついてくるようになったので，何でもこれでできてしまいそうだが，実はそうではない．実際にはデータベースの作成や定義などもテキストファイルベースのSQL文として保存しておけば，再度作り直すことができるなどのメリットもある．
　各社ともにSQLを拡張している．たとえば，SQL ServerならばTransact SQL，OracleならばPL/IベースのPL/SQLといった具合だ．これらの拡張SQLでは，ある程度のプログラミングができるようになっており，サーバー側で動作するプログラムを作ることができる．ある程度複雑なトランザクションであれば，こうした関数をストアードプロシージャとしてサーバーに作成しておき，クライアントからキックして実行させることもできる．
　また，サーバー側であるイベントをきっかけにして，こうした関数が動作するトリガも記述できる．
　最近では，よりオブジェクト指向を強めた拡張がなされる傾向にある．
　何はともあれ，RDBMSにSQLは必須である．逆にSQLさえわかってしまえば，RDOなどの高機能な仕組みを使いすぎ，かえってパフォーマンスを悪化させるといったこともなくなるハズだ．
　RDBMSについては，まだまだ書きたいことはあるのだが，最近ではその手の書籍も豊富に出てきていることでもあり，これくらいにしておこう．
　ここからは，むしろもっと基本について述べることにする．たとえば，データをいかに速く検索したり，更新したり，整合性をとっていくか，そしてSQL文の役割やC/S型の基本的な考え方を，実際にデータベースエンジンを作りながら考えていこう．

データベースでやりたいこと

図7：Titles.txt

(40KB)

　データベースとはデータを活用するためのシステムである．つまり，まずはデータが必要なのである．いや，これから作るのでもよい．データさえあれば，あとはそれを活用するシステムがあればよい．
　たとえば，さきほどの例で使ったBIBLIO.MDBは，各テーブルをAccessを使ってテキスト形式に保存することができる．このとき，カラムの間をカンマで区切るCSV形式にすることが多い．CSVにしておけば，あとからさまざまなデータベースで利用することができるので便利だ．そこで，まずはTitlesテーブルをTitles.txtというファイルに保存してみよう．これで，データはできあがった．これをベースにして，プログラムを作っていくことにしよう．
　このファイルは実に1.12Mbytesもある巨大なものである．それもそのはず，8637レコードものデータが入っている．これはなかなかのデータ数である．
　このファイルを，メモ帳で開いてみよう（図7）．開くだけでも結構時間がかかるが，Accessが起動するよりは速い．メモ帳には，検索機能がある．この機能を使えば，この膨大なデータの中から目的の書籍を探し出すことは，そんなに難しくはないだろう．
　ただし，それはタイトルがわかっているといった単純なケースだけだ．「1995年までに発売されたVisual Basicに関する本」となっただけで，かなり探すのが面倒になるだろう．また，「検索された本の情報を，発売された年代順に，出版社ごとに見ていきたい」となると，これまたたいへんである．さらに，「dBASEIIIの本はすべて削除する」とか，「出版社が変わってしまったので変更したい」となると，検索以外に，修正や削除の作業が加わり，いっそう手間がかかることになる．特に修正は，どこまでがどのデータ項目なのかカンマ区切りではわかりにくいということもある．
　こうした面倒なことをカンタンにできるようにしようというのがデータベースなのである．これからデータベースを作るわけだが，あまりにも便利なものを最初から目指すと挫折してしまうし，なぜそうすべきなのかもわかりにくいので，順番にカンタンなことからやっていこう．
　そこで，検索，修正，削除，追加，レコードの移動といったことができるものを作っていくことにしよう．

シーケンシャルファイルでの
アクセス

　まずは，テキストファイルをメモ帳で開いたときに目にした，データ項目のわかりにくさを解消しよう．Visual Basicのフォーム上に配置したテキストボックスに，各項目が出てくるようにする．
　このとき出てくるのは1行分だけである．この一行をレコードと呼ぶ．また，表示される各項目をフィールドと呼ぶ．
　テキストファイルをそのままこのような形で扱うためには，シーケンシャルファイルでのアクセスをする．ファイルは，オープン，読み書き，クローズの順で使うことになる．Visual Basicではファイルをオープンする場合，次のような構文で記述する（リスト１）．

リスト1：

Openファイル名For Input As  #ファイル番号　読み込みモードでオープン
Openファイル名For Output As #ファイル番号　書き込みモードでオープン
Openファイル名For Append As #ファイル番号　追加書き込みモードでオープン

　実際に読み込むには，Input #やLine Input #文を使う．使い終わったファイルはクローズする．
　実データは，Line Input #文で読み込み，その内容を一行ずつリストボックスに追加する．

　' ファイルをリストボックスに読み込む
　Open AppPath & SEQFILE For Input As #fd
　Do Until EOF(fd)
　    Line Input #fd, sTmp
　    lstData.AddItem sTmp
　Loop
　Close fd

図8：シーケンシャルファイルでのアクセス

(85KB)

　また，データの先頭行にはフィールド名が入っているから，それを取り出してラベルに表示する．テキストボックスおよびラベルはひとつ目（Index = 0）のみ配置したコントロール配列であるから，この内容を元にしてコントロールを配置できる．
　リストボックスにデータを入れておけば，リストボックスから目的のデータを一行読み込むのもカンタンだし，修正したり追加するのも，リストボックスに対してできる（図8）．
　リストボックスから目的のレコードをクリックすると，リストボックスの内容は各テキストボックスにコピーされる．リストボックスではCSV形式になっているので，分解して各フィールドの内容を配列に格納する．このとき，単純にカンマを基準にして取り出してしまうと，"でくくられた中にあるデータのカンマも区切りとしてしまう．これではいけないので，"があるところでは，次の"までの間のカンマは調べないようにしなくてはならない．
　また，逆にテキストボックスの内容をまとめてひとつの文字列にするコードも必要だ．しかし，厳密には"の対応の問題がここでもあるので，これはとりあえずのコードである．
　レコードの移動は，リストボックスのListIndexを変更するだけだ．検索機能もつけてみた．
　追加，修正，削除もごくカンタンにリストボックスのメソッドを使うだけだ．　プログラム終了時には，逆にリストボックスの内容をすべて書き戻せばよい（リスト2）．実行画面およびリストを示す（図9）．リスト3

リスト2：

Private Sub Form_Unload(Cancel As Integer)
    Dim fd As Integer
    Dim i As Integer

    If MsgBox("Write Data?", vbQuestion Or vbYesNo, "Quit") = vbYes Then
        ' リストボックスからファイルに書き込む
        fd = FreeFile
        Open AppPath & SEQFILE For Output As #fd
        For i = 0 To lstData.ListCount - 1
            Print #fd, lstData.List(i)
        Next i
        Close fd
    End If
End Sub

図9：シーケンシャルファイルアクセスプログラムの実行例

(9KB)

　このプログラムは，読み込んだファイルをすべてメモリ（リストボックス）上に配置し，プログラムはオンメモリの情報を読み書きする．したがって，次のような特徴がある．

【長所】
　実行中の動作は高速である．特に検索時には非常に高速だ．リストボックスを使わないで配列にすれば，もっと高速になるだろうが，ここではカンタンにするためリストボックスを使った．

【短所】
　データ件数が多いと起動・終了時のファイルアクセスに非常に時間がかかることになる．というのも，シーケンシャルファイルは，追加以外は，必ずファイル全体単位で読み込み，書き込みをするからだ．すでに8000件以上あるこのテキストファイルを読み込むのには，数十秒がかかる．もちろん，検索時にファイルをオープンするという手もあるが，毎回検索には必ずファイルアクセスが絡むので速度は低下する．
　件数が多くなってくると，メモリが圧迫されるという問題もある．もっとも，昨今のディスクやメモリ事情からいえば，こんなことはどうということはないだろう．
　万が一途中で異常終了すると，すべてがメモリ上にあるので，変更は水の泡である．しかし，ファイルアクセスに時間がかかるため，ときどき保存するといったわけにもいかない．

ランダムアクセスファイルでの
アクセス

　シーケンシャルファイルでの問題は，まさにメモ帳でファイルを読み込んだときと同じで，データ全体を読み書きしなくてはならない点にある．それでは，特定のところだけ選んで読み書きできれば，また別の展開がありそうではないか．
　これを可能にするのが，シーケンシャルファイルと並んで，Visual Basicにあらかじめ用意されているファイルアクセス方法が，ランダムアクセスファイルである．
　ランダムアクセスファイルは，レコード単位での読み書きをすることができる．先ほどの例でいうならば，リストボックスの1行ずつを自由にファイルから読み書きできるのだ．そのため，あらかじめ1レコードのサイズを決定しておく必要がある．
　また，ひとつの文字列としてだけでなく，複数のフィールドからなる，つまり複数のデータ，複数のデータ型からなるユーザー定義型の変数として，まとめて読み書きすることができるのだ．
　たとえば，元データであるTitlesのデータ構造は次のようになっていた．

Title	Text　　　255
Year Published	Integer
ISBN	Text　　　20
PubID	Long
Description	Text　　　50
Notes	Text　　　50
Subject	Text　　　50
Comments	Memo

　これを，Visual Basicのユーザー定義型で書き換えると，たとえば次のように書くことができる．

　Type tagTitles
　    Title As String * 255
　    YearPublished As Integer
　    ISBN As String * 20
　    PubID As Long
　    Description As String * 50
　    Notes As String * 50
　    Subject As String * 50
　    Comments As String * 255
　End Type

　Public recTitle As tagTitles

　Open AppPath & DATFILE For Random As #fd Len = Len(recTitle)

　Get #fd, pos, recTitle
　Put #fd, pos, recTitle

　Private Sub cmdAdd_Click()
　    ' 最後のレコード: LOF(fd) \ Len(recTitle)
　    If SetData() Then
　        curPos = LOF(fd) \ Len(recTitle) + 1
　        PutData curPos
　    End If
　End Sub

リスト4：指定位置から検索

Private Function SearchList(st As Long) As Integer
    Dim i As Long

    SearchList = -1
    For i = st To LOF(fd) \ Len(recTitle)
        If GetData(i) Then
            If InStr(recTitle.Title, txtSearch.Text) <> 0 Then
                SearchList = i
                Exit For
            End If
        End If
    Next i
End Function

　このように，ランダムアクセスファイルにも長所と短所がある．

【長所】
　いつでも自由にファイルの任意レコードにアクセスすることができる．したがって，起動／終了時もスピーディだし，内容も随時更新される．
　ユーザー定義型を使って一括アクセスできるので，スマートにコーディングできる．
【短所】
　検索時にもファイルにアクセスしなくてはならないので，遅くなってしまう．

図10：ランダムアクセスファイルでのアクセス

(46KB)

リスト5：

図11：ランダムアクセスファイルプログラムの実行例

(7KB)

ISAMとインデックス

　シーケンシャルファイルとランダムアクセスファイルには，一長一短があることがわかった．では，これらのよいところだけをうまく取り込んだらどうだろうか．
　このようにして考えられたのが，ISAM（Indexed Sequencial Access Method）である．
　ISAMは，基本的にはランダムアクセスファイルである．しかし，検索時にはシーケンシャルファイルを元にしたインデックス，すなわち索引を使ってオンメモリで素早く実行しようというものだ．
　索引には，本のタイトルや名前など，よく検索に使われるであろうものだけを用意しておく．そして，次のようにタイトルの後に対応するランダムアクセスファイルの実レコードを入れておく（リスト6）．

リスト6：

"Accu-Data User's Guide 4.1",4322
"Acronyms and Abbreviations for Computer Technology and Telecommunications",4674
"Acs Directory of Graduate Research, 1993/Cd-Rom Version",5016
"Act Computer Study Program/IBM",4577
"Act! for Windows",7509
"Active Perception (Computer Vision",4556
"ActiveXプログラミング入門",8350
"ActiveXを知る",8349
"Activity Experiences and Programming Within Long-Term Care",5736
"Ada : A Developmental Approach",1329

　このようにしておけば，タイトルから実レコードを知ることができるわけだ．
　たとえば，タイトルをリストボックスに入れておき，ItemDataプロパティに実レコードを入れておけば，すぐに実データにアクセスできる（図12）．

図12：ISAMを使ったアクセス

(96KB)

　こうして作ったのが，ISAMプログラムである．ここではForm_Loadでフィールドのタイトルおよびインデックスのありなし，さらにプライマリのインデックスを指定するようにした．もちろん，あらかじめインデックスは作っておく必要がある（リスト7）．

リスト7：インデックスの指定

MAXINDEX = 7
vTitles = Array("Title", "Year Published", "ISBN", _
    "PubID", "Description", "Notes", "Subject", "Comments")
vIndex = Array(True, False, True, _
    False, False, False, False, False)
'プライマリキーの指定
PRIKEY = 2 ' ISBN

　実際に動かしてみると，このプログラムではデータの選び方が悪かったことに気づいた．Titlesは非常に長いデータもあるため，タイトルとレコードだけのインデックスファイルにしても，まだ560Kbytes以上もあったのだ．起動／終了時には，やはりかなりの時間がかかってしまった．しかし，インデックスをもっと短いものにすれば，これは改善される．プライマリインデックスのISBNだけにしてみれば，ずっと動作は速くなる（図13，リスト8参照）．
　また，テキスト指定の検索時には，インデックスを指定したものであれば，コンボボックスから選べるようにもなっている．このあたりは，かなり汎用性を意識してコーディングした．ここまでのプログラムでも，ランダムアクセスファイル用のユーザー定義型の指定以外は，ほとんどコードを修正しなくてもさまざまなデータ形式に対応できるようにしてある．
　このように，ISAMはうまくいいとこどりをしたシステムだ．しかし，複数のファイルを組み合わせて使うため，整合性が損なわれたときの修復などに問題もある．

図13：ISAMプログラムの実行例インデックスのリストボックスからもレコード指定可能

(9KB)

リスト8

サーチとソート

　ISAMで検索を高速にすることができたが，それはオンメモリゆえに高速だというだけである．アルゴリズム的に工夫をこらしたわけではない．検索もベタで順番に調べていく，いわゆるバカサーチである．
　バカサーチだと，実際のデータベースでは件数が増えるにしたがって，後ろの方にあるデータの検索が遅くなってしまう．ヘタをすると10秒以上もかかってしまうこともある．え？　Accessはもっとかかるって？　それはAccessだからだ．いや，ちゃんとインデックスを作っていなかったりした場合だろう．
　そこで，もっと高速にアクセスする方法を考えてみよう．
　たとえば，いま1000件のデータがあったとしよう．目的のデータは656番目にあるとする．すると，バカサーチでは，少なくとも656回ループを回って比較をしなくてはならないことになる．これはいくらコンピュータが速いからといっても，情けない話ではないか．
　人間だったら見当をつけて，「このへんだな」と近いところを狙うハズだ．これをコンピュータにもやらせない手はないだろう．
　ここで紹介するのは，バイナリサーチという手法だ．

図14：バイナリーサーチ

(38KB)

まず，最小の1と最大の1000の真ん中500を調べる．
656は500より大きいから，次は500と1000の中間の750を調べる．
656は750より小さいから，次は500と750の中間の625を調べる．
656は625より大きいから，次は625と750の中間の688を調べる．
656は688より小さいから，次は625と688の中間の656を調べると...

　ビンゴ!!　である．わずか5回のループで検索が終了してしまったわけだ．もっと位置が悪いものだとしても，あと5回もループすれば見つかるということになる（図14）．

　ちなみに，ISAMプログラムでは，検索でバイナリーサーチを選べるようになっているので，動かしてみればこの違いを体感することができるハズだ（図15）．ただし，検索条件としては先頭からの部分一致になる．
　バイナリサーチ部分のコードをリスト9に示す．

図15：ISAMプログラムの実行例インデックスを使っての検索も可能

(8KB)

リスト9：指定位置からバイナリ検索

Private Function BSearchList(st As Long) As Integer
    Dim nextpos As Long
    Dim lastpos As Long
    Dim minpos As Long
    Dim maxpos As Long
    Dim s As String

    BSearchList = -1
    minpos = 0
    maxpos = frmMain.cboIdx(cboSelIdx.ListIndex).ListCount
    Do
        nextpos = (maxpos + minpos) \ 2
        If lastpos = nextpos Then Exit Do
        s = frmMain.cboIdx(cboSelIdx.ListIndex).List(nextpos)
        If InStr(s, txtSearch.Text) <> 0 Then
            BSearchList = nextpos
            Exit Do
        Else
            If s > txtSearch.Text Then
                maxpos = nextpos
            Else
                minpos = nextpos
            End If
            lastpos = nextpos
        End If
    Loop
End Function

　このように，バイナリーサーチの威力は絶大だ．しかし，これはインデックスが順番にソート（並べ換え）されているという前提でないと使えない．バイナリサーチは基本的には大小比較できないといけないからだ．
　途中でデータが追加されたときなどは，ソートしなおすか，後から追加されたものはバカサーチするなどの対処が必要だ．しかし，いずれはソートしなおさなくてはならないわけで，ここであまり時間がかかりすぎるのも問題である．
　このプログラムでは，リストボックスのSortedプロパティをTrueにしてある．この状態でデータをAddItemメソッドで追加しているので，自動的にインデックスはソートされる．
　高速にソートする方法として，クイックソートなどもあるが，ここでは述べない．また，もっと高級なハッシュなどの方法を使ってインデックスを管理方法もある．いずれにしても，ここで述べたいのは，こういった優れたアルゴリズムは，データベースを考える上で実は非常に重要なのであるということだ．
　何はともあれ，インデックスをうまく使えば，より高速にアクセスできることがわかっていただけただろう．

ランダムアクセスファイルへの
コンバートとインデックス作成

図16：シーケンシャルファイルをランダムアクセスファイルと
インデックスにコンバートするプログラムの実行例

(12KB)

　何気なく，ランダムアクセスファイルやISAMのインデックスを使ってきたが，実はこれだって作らなければできない．元々はTitles.txtというテキストファイルしかなかったのだ．
　そこで，一度リストボックスに読み込んだデータをランダムアクセスファイルに書き出したり，それぞれのフィールドごとのコンボボックスの内容をインデックスに書き出すプログラムを作った（図16，リスト10）．

リスト10

SQLモドキ言語エンジンの作成

　さて，ISAMもでき，だんだんデータベースらしくなってきた．そこで，次のステップとしてSQL文を解釈して対応する処理をするエンジンを作ってみることにしよう．
　といっても，SQL文は実に多彩な組み合わせがあり，そうそうカンタンに構文解析をできるものではない．少なくとも，Visual Basicでそんなコードを書きたくはない．だがやってみたい．
　そこで「SELECT hoge, hage FROM foo WHERE hoge > 100」のような単純なSQL文を実行するものにしてみた．WHERE節の演算子としては，「=」の他に大小比較などや，「Like」もサポートした．
　基本的には，SQL文をデリミター文字列で分解して配列に代入していき，その内容を元にしてISAMプログラムでのアクセスを実行するというものだ．内容については，あまり細かく書いてもしかたないのでこれくらいにしておく．
　ここで重要なことは，あくまでも内部はISAMを使ったアクセスであるということだ．つまり，SQLモドキエンジンは，単なる言語解析パーサーに過ぎないのである．
　実際に動かしてみると，入力した文に合わせてそれなりにちゃんと動いてデータを返してくれるところがカワイイ．思わずこのまま拡張してSQL文をもっとサポートし，RDBMSサーバーにしてしまおうなどという野望が頭をよぎってしまう．
　それはともかく，このプログラムでのトピックは，WHERE節の解析である．WHERE節に指定されたカラムにインデックスが指定されていればインデックスを使って高速に検索する．とはいっても，ここではバイナリーサーチすら使っていないのでたかが知れているのだが…
　問題は，インデックスがないカラムが指定されたときだ．このときには，ベタでランダムアクセスファイルを順に読んでいくしかない．これはきわめて遅い．また，このコードでは汎用性と作りやすさの観点から，少々余計なコードが走るという効率の悪い方式になっているので，なおさら遅くなっている．
　通常のRDBMSではここまでひどくはないにしろ，インデックスがないから遅くなってまうのは，こういった理由からなのである．

ベンチマーク
　実際に，このSQLモドキエンジンを積んだデータベースでのベンチマークを紹介しよう．といっても単純なものである（表1）．

表1：ベンチマークの仕様
Titles　8637 Records　　アプリケーション起動　　起動約29sec　　終了約8sec　　

WHERE節の内容インタープリタでのクエリー時間(コンパイル時) (件数)

ISBN プライマリキー

ISBN = "Unknown9J" 2.1sec　　　　　（1件）

ISBN like "Unknown*" 2.4(1.5)sec　　　　　（14件）

ISBN = "XXXXXXXX" 2.1(1.3)sec　　　　　（0件）

ISBN like "*" 79.4sec　　　　　（全件）

Title インデックスあり

Title like "*入門*" 3.0(2.1) sec　　　　　（20件）

Title like "XXXXXXX" 2.8sec　　　　　（0件）

Title like "*" 79.7 sec　　　　　（全件）

'Year Published' インデックスなし

'Year Published' = '1997' 59.7(48.9)sec　　　　　（22件）

'Year Published' like "XXXXXXX" 59.9sec　　　　　（0件）

'Year Published' like "*" 77.2sec　　　　　（全件）

全件空読みに約60sec　　全件データ読み込みと表示にかかる時間約77sec

結果は

全件表示にかかる時間 = 77sec - (全件空読みに約60sec) = 17sec
インデックス全件空読みに約2.1sec
というレートとなった．やはり，リストボックスを使わないでオプティマイズすれば，もっともっと高速化することができそうだ．
　なお，この結果はPentium 150MHzメモリ128MBのマシンで，Visual Basic 5.0 SP2のインタープリタ（一部コンパイル後のもの）で実行したものである．

図17：SQL言語エンジン

(75KB)

図18：SQLモドキ言語エンジン搭載プログラムの実行例

(17KB)

リスト11

SQLモドキサーバーの作成と
クライアントからのアクセス

　SQL文の解析はもっとやってみたかったのだが，時間もないことでこれくらいにして，次のステップとしてサーバーデータベースにしてみよう．といっても，もちろん本格的なRDBMSではなく，実験的なものである．なにせ，元のSQLのエンジンがボロすぎる．
　さて，クライアント側からサーバーデータベースにあるデータを得るためには，どのようなことを考えなくてはならないだろうか．次に必要なことを順に述べる．

データを扱う方法…SQL
　まず第一に，データの扱い方である．たとえば，ISAMのデータベースそのままにアクセスしようとしたら，クライアントにインデックスをまるごと持ってきて，対応するレコード番号をサーバーに送るというようなやり方になるだろう．
　しかし，インデックスのデータをまるごとクライアントに持ってくるのは効率的ではないし，効率よくフィールドごとにデータを取得したいとなると，データ構造もクライアント側で把握する必要がある．こうなってくると，クライアント側でのプログラミングも面倒なことこの上ない．
　そこで，SQLである．文字列（文）としてサーバーに要求すれば，サーバー側で適切なクエリーを行ない，得たレコードセットをサーバー側に保持する．それを一件ずつ取得していけばよい．これがシンプルで効率的な方法だ．
　本当ならば，ここで得られるデータはカラムごとにわかれていた方が望ましいが，ここでは単純化のため，タブで区切られた一連の文字列として返すことにする．

ミドルウェア…OLE
　サーバーとクライアントの通信は，基本的に文字ベースで行なうことが決まった．では，実際にどのようにして通信すればよいのだろうか？
　TCP/IPでアプリケーション層に独自プロトコルを作るというのが，今なら最もスマートな感じがするが，ここではそんな面倒なことはやっていられない．
　そこでOLEを使うことにする．つまりサーバーはActiveXサーバーとして実現し，クライアント側からはリモートオートメーションやDCOMを使ってアクセスするわけだ．これならば，ExecSQLやFetchといったメソッドをクラスに実現するだけなので，話はカンタンだ．
　これはもちろん，DCOMを使った本格的なオブジェクト指向ミドルウェアとも言えなくはない．しかし，あくまでもこれはサンプルであり実験なので，とりあえず同じマシンで動かして遊ぼうという程度である．やろうと思えばDCOMも使えるというだけの話だ．

排他ロック
　複数のユーザーがこのデータベースにアクセスするという前提ならば，排他ロックどころかインスタンシング（Instancing）の方法も考えなくてはならない．ランダムアクセスファイルのロックはもちろん，実行中にインデックスのシーケンシャルファイルを書き出されたりしたら，整合性が取れなくなってしまうからだ．
　したがって，あくまでもインスタンシングはMultiUseにしなくてはならない．しかし，元になるSQLモドキプログラムでは，結果セットを得るのは同じリストボックスであるから，事実上これも不整合が起きることが容易に予想できる．
　というわけで，ここでは面倒なことを考えずに，実験だからいいやということにしておこう．
　このような，いささかいいかげんな方針で作ったのが，SQLmSvrである．クラスモジュールQueryを追加し，リスト12のようなコードを記述した．

リスト12：クラスモジュールQuery

Private fetchptr As Long

Public Function ExecSQL(sql As String) As Long
    frmMain.txtSQL.Text = sql
    frmMain.cmdSQL.value = True
    fetchptr = -1
    ExecSQL = frmMain.lstResult.ListCount
End Function

Public Function fetch(s As String) As Long
    ' 最後
    If frmMain.lstResult.ListCount < fetchptr + 1 Then
        s = ""
        frmMain.lblStatus.Caption = "最後のレコードです"
        fetch = fetchptr + 1
    ' 空
    ElseIf frmMain.lstResult.ListCount = 0 Then
        s = Null
        fetch = -1
        frmMain.lblStatus.Caption = "空です"
    Else
        fetchptr = fetchptr + 1
        s = frmMain.lstResult.List(fetchptr)
        fetch = fetchptr
        frmMain.lblStatus.Caption = "1件処理しました"
    End If
End Function

Public Function GetStatus() As String
    GetStatus = frmMain.lblStatus.Caption
End Function

' ExecSQLは，SQL文を実行する
' Fetchは，SQL文で実行された結果セットを1レコードずつ取得する
' GetStatusは，エラーやステータス状況を得る

　プロジェクトをActiveX Serverにすれば，もう立派な，SELECTしかできないSQLモドキServerの完成である．
　次にクライアントである．といっても難しいことはない．とりあえず実験なのだからたいそうなものを作る必要はない．SQL文が記述でき，それぞれのメソッドを呼び出してfetchした結果を得たり，ステータスが得られればOKだ（リスト13）．必然的に，サーバープログラムの下半分と同じデザインになってしまった（図19）．

リスト13：SQLモドキクライアント

Dim oSQL As New Query

Private Sub cmdFetch_Click()
    Dim s As String
    Dim r As Long

    r = oSQL.fetch(s)
    lstResult.AddItem s
    cmdGetStatus.Value = True
End Sub

Private Sub cmdGetStatus_Click()
    lblStatus.Caption = oSQL.GetStatus
End Sub

Private Sub cmdSQL_Click()
    Dim r As Long

    lstResult.Clear
    r = oSQL.ExecSQL(txtSQL.Text)
    cmdGetStatus.Value = True
    AppActivate Me.Caption
End Sub

図19：SQLモドキServerへアクセスするクライアントプログラムの実行画面

(7KB)

　いささかおちゃらけたものになった感があるが，現実のRDBMSサーバーでも，やっていることの基本は変わりない．データベースの基本はISAMなのである．いかに高速に動作するかとか，いかに排他制御をうまくやるとか，高可用性とかいったメカニズムがもっと高級になっているだけなのだ．
　クライアント側からSQL文が送られ，その構文を解析し，実行し，結果セットを保持し，リクエストに合わせてクライアントに返すというメカニズムは，まさにSQLベースのRDBMSサーバーにほかならないのである（図20）．

図20：SQLモドキServerプログラムの実行画面

(16KB)

リスト14

図21：SQL RDBMSサーバーとクライアント

(73KB)

“RDBMSのために...その1”

　いやぁ，今回の原稿は，実にたいへんだった．
　どうもデータベースの基本的な考え方を理解せずに，DAOだRDOだとおっしゃている方が多いようなので，データベースの基礎の基礎の話からスタートしようと考えた．そこで，久々にランダムアクセスファイルを使ったり，カンタンなISAMを作ったり，果ては少々カッコワルイものだったが，SQLの構文解析をしたわけだが，これが意外に時間がかかった．
　実際，もうISAMなどは作らなくてもDAOを使えば済む話だし，C/S型のRDBMSも安価になってきている．こんなものを作ってまで解説すべき内容なのだろうかと，正直いって思った．
　しかし，実際に作ってくると，自分自身，今まで気づかなかったデータアクセスの問題や，C/SにおけるSQLの必要性などについてあらためて考えさせられることになった．ちょっとだけかもしれないが，これは意味のあることだったように思う．
　そういったことを，この一連のプログラムをよく見直して，皆さんにも体験していただきたい．
　作ったプログラムには，さまざまなテクニックが盛り込まれており，他の目的にも役立つトピックもありそうだったのだが，いかんせんページも時間もなく，説明が不十分になってしまった．もちろん，バグもたくさんあると思う．
　これをベースにして，もっと高速化するためにはどんなことをすればよいのかと改造するのもよいだろう．あるいは現代のRDBMSは不用意に複雑になっているのではないかと考えてみるのも一興だろう．
　最後に，次の問題を考えてみていただきたい．回答は本稿とサンプルプログラムの中にある．

・ISAMとは何か
・インデックスが必要なのはなぜか
・高速にインデックスにアクセスするにはどんなことをすればよいか
・プライマリーキーはなぜユニークでないといけないのか
・なぜミドルウェアが必要なのか
・C/SではなぜSQLを使うのか
・SQL文の実行から取得までのシーケンスを述べよ
・高機能なミドルウェアは本当にやりたいことを高速にできるのか

本記事中に掲載したサンプル・プログラムは、ここからダウンロードできます。
なお動作させるためには.txt，.idx，.datファイルを各々のサブディレクトリにコピーする必要があります．

VB Magazine ライブラリ | Visual Basicコースホームページ
int21 ホームページ | PCDN ホームページ

WHERE節の内容	インタープリタでのクエリー時間(コンパイル時)	(件数)
ISBN プライマリキー
ISBN = "Unknown9J"	2.1sec	（1件）
ISBN like "Unknown*"	2.4(1.5)sec	（14件）
ISBN = "XXXXXXXX"	2.1(1.3)sec	（0件）
ISBN like "*"	79.4sec	（全件）
Title インデックスあり
Title like "入門"	3.0(2.1) sec	（20件）
Title like "XXXXXXX"	2.8sec	（0件）
Title like "*"	79.7 sec	（全件）
'Year Published' インデックスなし
'Year Published' = '1997'	59.7(48.9)sec	（22件）
'Year Published' like "XXXXXXX"	59.9sec	（0件）
'Year Published' like "*"	77.2sec	（全件）