torutkのブログ

ソフトウェア・エンジニアのブログ

データベースを利用するアプリケーション(データベース作成)

はじめに

続きです。

torutk.hatenablog.jp

今回は、データベースにテーブルを定義し、アプリケーションから利用できるようにします。 利用するデータベースは先の日記に記載のとおり、SQL Serverです。

データベースの作成ツール

SQL Server Express Edition(またはDeveloper Edition)がインストールされた環境で開発を進めます。 データベースの新規作成、テーブルの新規作成は、SSMS(SQL Server Management Studio)から行えます。 作成後のカラムのデータ型変更などもできるので、設計が固まるまではSSMSで作業します。

ER図を試行錯誤で作成しながらテーブルの設計をしましたが、今回はER図は頭の整理のためのスケッチとしての役割に留まっています。

SQL Serverのコマンド環境は、SQL Serverをインストールしたマシンであれば、コマンドプロンプトからsqlcmdコマンドを実行すると利用できます。対話モードとSQLファイルを実行するモードがあります。

データベース作成

データベース領域の作成

テーブルを定義するために、まずデータベースを1個作成します。ここにテーブル群を定義していきます。

スペクトラムデータのテーブル

まずはデータファイルの内容をデータベースのテーブルに定義します。項目としては次を入れます。

  • 名前(元のファイル名)
  • 周波数の開始値
  • 周波数の終了値
  • 電力値の点列

かなり絞った項目としています。データの取得日時、取得に使用した計測器・配線などの計測系、取得した信号の素性、などなど盛り込みたい情報があります。計測条件、計測対象、計測記録はそれぞれ別テーブル(別エンティティ)として定義する方針とします。

テーブルのスキーマ(現時点の結果)

列名 データ型 NOT NULL制約 備考
id bigint あり 主キー(自動インクリメント)
name nvarchar(50) なし スペクトラム
startFrequency real あり 周波数の開始値
stopFrequency real あり 周波数の終了値
spectrum varbinary(max) あり スペクトラム点列

先に挙げた項目の他に、idを追加しています。RDBMSでは、レコードを一意に識別する主キーが必要です。名前を主キーにするとしたら、名前は任意に付与できるので一意を保つにはアプリケーション側(ユーザー側)で頑張る必要があります。これは運用がやっかいなので避けたいところです。また、他の項目でレコードを一意に表現できるものはありません。そこで、サロゲートキーを設けることとします。

NOT NULL制約は、データを成立させるのに不可欠な項目に付与します。

idの設計

idは、整数でインサートごとに自動でインクリメントして採番します。データ型は最初int(32bit符号付整数)としていましたが、intだとレコード数が21億( 2^{31}-1)を超えられないので、多数のデータを保持する可能性のあるテーブルではbigintにしておきます。 自動インクリメントにするには、SSMS上では列のプロパティから、IDENTITYの指定でIDであるを[いいえ]から[はい]に変更します。

nameの設計

スペクトラムデータの名称を保持します。SQL Serverの文字列は、ユニコード文字と非ユニコード文字とそれぞれのデータ型が用意されています。nchar、nvarcharがユニコード文字のデータ型で、char、varcharが非ユニコード文字のデータ型です。

日本語Windows環境でデフォルトで構築した場合、非ユニコードはデフォルト(既定)ではコードページ932(Windows_31J)で扱われます。アプリケーション側の文字コードユニコードなので、データベース側で非ユニコードのデータ型を使うと文字コード変換が生じていろいろ面倒になりそうです。そこで、ASCII文字に限定できない場合はユニコード文字を扱うデータ型とします。

nvarcharでサイズ50を指定した場合は、最大50文字(UCS-2の範囲)となります。補助文字を使用する照合順序を指定した場合は、UTF-16となるので、サロゲートペア文字が使われた場合は50文字より少なくなります。

なお、SQL Serverでは、文字列を格納するデータ型にtext、ntextもありますが、これらは非推奨となっています。

周波数の開始値、終了値

小数点の数値は、float、real、decimal のデータ型が使えます。(numeric型もありますが、SQL Serverではdecimalと同一です。)

floatとrealは浮動小数点数のデータ型で、decimalは固定長の有効桁数(10進数)と小数点以下の桁数を指定したデータ型です。 floatは有効桁数(仮数部のbit数)を1~53で指定しますが、内部では24もしくは53のどちらかで扱います。 realは、float(24)と同義です。 周波数の値はそれほど有効桁数を必要としないので、今回はストレージ上のサイズが小さくてすむrealを使用します。

spectrumの設計

スペクトラムデータの点列を保持します。バイナリデータですが点数がまちまちのため可変長のバイナリとします。サイズを数値指定する場合は1~8000の範囲なので最大8000バイトとなります。しかし、格納したい点列は1万点を超えることがあるので、この場合はサイズにmaxを指定します。maxを指定すると、最大で  2^{31} - 1 バイトまで格納可能です。

なお、binary、image は非推奨となっています。

計測記録テーブルへの参照を含めるか

後で作成する計測記録のテーブルには、スペクトラムデータを参照する外部キーを設ける予定です。 その逆方向としてスペクトラムのテーブルに計測記録への外部キーを設けるかどうか悩みました。 双方向の関連を持つか、片方向の関連のみとするか、現時点では明確な根拠を持てないでいます。必要性を感じるまでは、スペクトラムのテーブルには計測記録への外部キーは持たせないでおきます。