データベース Archive

弁慶フレームワークにDB2を対応させるため、環境構築をしています。

インストールしたのは、DB2 Express-C 9.5 for Windowsで、使用制限があるものの、無償で利用することができ、商用利用も可能とのことです。

インストール方法については、 [DB2 Express-C 9.5 for Windows クイックインストール] を参考にしました。

.NETから利用するために、Providerが欲しいところですが、標準インストールで入るようです。

テーブル一覧、フィールド一覧の取得方法は、また後ほど書きます。

SQLServer2005以降で、ROW_NUMBER を使ってみる

SELECT 
    * 
FROM 
    (
    SELECT 
        ROW_NUMBER() OVER(ORDER BY OrderID, ProductID) AS ROWNUM, 
        * 
    FROM 
        "Order Details Extended" 
    ) A 
WHERE 
    ROWNUM BETWEEN 11 AND 20 
ORDER BY 
    OrderID, 
    ProductID
;

SQLは簡潔明瞭ですが、サブクエリで全件返しているので、効率が良くないかもしれません。

TOP を組み合わせてみる

SELECT TOP 10 
    * 
FROM 
    "Order Details Extended" A 
WHERE 
    NOT EXISTS (
    SELECT 
        * 
    FROM 
        (
        SELECT TOP 10
            * 
        FROM 
            "Order Details Extended" 
        ) B 
    WHERE 
        A.OrderID = B.OrderID 
        AND A.ProductID = B.ProductID
    ) 
ORDER BY 
    OrderID, 
    ProductID
;

NOT EXISTSを使っているので、少し分かりにくいかと思いますが、要約すると、
先頭10件を除いた集合の中から、さらに先頭の10件を返す
ということになります。

SQLServer2000のときは、現在のレコードより前に来るべき行の COUNT(*)+1 を取ることで、ROWNUMの代わりとすることができますが、ソートキーが複数のときは、少し面倒くさいので例にしてみます。
テスト用データベースは、Northwindを使用しています。
SQLそのものは基本的な構文だけで書けますので、Oracle、PostgreSQL、MySQL、DB2などでも同様に実装できると思います。（文字列変換等は変えないとダメだけど）

１．ソートキー毎に合算していく

SELECT 
    (
    SELECT 
        COUNT(*) 
    FROM 
        "Order Details Extended" A 
    WHERE 
        A.OrderID < B.OrderID 
    ) + 
    (
    SELECT 
        COUNT(*) 
    FROM 
        "Order Details Extended" A 
    WHERE 
        A.OrderID = B.OrderID 
        AND A.ProductID < B.ProductID 
    ) + 1 AS ROWNUM, 
    B.* 
FROM 
    "Order Details Extended" B 
ORDER BY 
    B.OrderID, 
    B.ProductID
;

フィールド数が多くなるほどサブクエリが増えるので、面倒くさくなります。
これを応用すると、あるグループごとに連番を振ることも可能ですね。

SELECT 
    (
    SELECT 
        COUNT(*) + 1 
    FROM 
        "Order Details Extended" A 
    WHERE 
        A.OrderID = B.OrderID 
        AND A.ProductID < B.ProductID 
    ) AS GROUP_NUM, 
    B.* 
FROM 
    "Order Details Extended" B 
ORDER BY 
    B.OrderID, 
    B.ProductID
;

２．文字列として結合し、１つのフィールド扱いにする

SELECT 
    (
    SELECT 
        COUNT(*) + 1 
    FROM 
        "Order Details Extended" A 
    WHERE 
        RIGHT('00000' + CONVERT(VARCHAR, A.OrderID), 5) + RIGHT('000' + CONVERT(VARCHAR, A.ProductID), 3) < RIGHT('00000' + CONVERT(VARCHAR, B.OrderID), 5) + RIGHT('000' + CONVERT(VARCHAR, B.ProductID), 3) 
    ) AS ROWNUM, 
    B.* 
FROM 
    "Order Details Extended" B 
ORDER BY 
    B.OrderID, 
    B.ProductID
;

Int型なので、一旦文字列にしています。もちろん、元々文字列ならそのまま足せるので、ラクチンです。
Int型でも、A.OrderID * 1000 + A.ProductID のように、桁を揃えてしまう手もありますが、IntとCharが混在していることもありますので、上記のような例にしてあります。

SQLServer2005以降のときは、ROW_NUMBER 関数が用意されているので、これを利用します。
テスト用データベースは、Northwindを使用しています。

SELECT 
    ROW_NUMBER() OVER(ORDER BY OrderID, ProductID) AS ROWNUM, 
    * 
FROM 
    "Order Details Extended" 
ORDER BY 
    OrderID, 
    ProductID
;

SQLServer2005に、サンプルデータベースを入れてみました。

昔なつかしのNorthwind、pubsはSQLServer2000用とのことでしたが、全く問題なくインストールできました。
2005のExpressEdition、SQLServer2008では未確認です。誰かフォローください。
(ExpressEditionに入れることは可能なようです)

Northwind and pubs Sample Databases for SQL Server 2000

1. ダウンロードページから、SQL2000SampleDb.msi をダウンロードします。
2. SQL2000SampleDb.msi をダブルクリックしてサンプル データベース スクリプトを展開（C:\Program Files\SQL Server 2000 Sample Databases）します。
3. コマンドプロンプトを起動します。
4. osql -E -i "C:\SQL Server 2000 Sample Databases\instnwnd.sql" を実行します。(Northwind)
5. osql -E -i "C:\SQL Server 2000 Sample Databases\instpubs.sql" を実行します。(Pubs)

SQLServer2005のAdventureWorksデータベースはこちら
Microsoft SQL Server Product Samples: Database - Release: SQL Server 2005 SP2a

SQLServer2008のAdventureWorksデータベースはこちら
Microsoft SQL Server Product Samples: Database - Release: SQL Server 2008 February CTP

PostgreSQLで、フィールド一覧を取得する方法です。
TABLEのみが対象になります。

フィールドサイズの取得は、面倒なのですがフィールドタイプで固定な数値を割り振りました。

「@TABLE_NAME」は、バインド変数なので、対象としたいテーブル名を入れてください。2箇所あるので注意してください。

SELECT 
    A.FIELD_NAME, 
    A.FIELD_TYPE, 
    CASE WHEN A.FIELD_SIZE IS NULL THEN 0 ELSE A.FIELD_SIZE END AS FIELD_SIZE, 
    CASE WHEN B.IS_PK IS NULL THEN 0 ELSE B.IS_PK END AS IS_PK, 
    A.IS_NULLABLE 
FROM 
    (
    SELECT 
        PG_CLASS.RELNAME AS TABLE_NAME, 
        PG_ATTRIBUTE.ATTNUM AS FIELD_NUM, 
        PG_ATTRIBUTE.ATTNAME AS FIELD_NAME, 
        PG_TYPE.TYPNAME AS FIELD_TYPE, 
        CASE WHEN PG_TYPE.TYPNAME = 'bpchar' THEN PG_ATTRIBUTE.ATTTYPMOD-4 
            WHEN PG_TYPE.TYPNAME = 'varchar' THEN PG_ATTRIBUTE.ATTTYPMOD-4 
            WHEN PG_TYPE.TYPNAME = 'numeric' THEN ( PG_ATTRIBUTE.ATTTYPMOD-4 ) / 65536 
            WHEN PG_TYPE.TYPNAME = 'decimal' THEN ( PG_ATTRIBUTE.ATTTYPMOD-4 ) / 65536 
            WHEN PG_TYPE.TYPNAME = 'date' THEN 10 
            WHEN PG_TYPE.TYPNAME = 'time' THEN 8 
            WHEN PG_TYPE.TYPNAME = 'timestamp' THEN 19 
            WHEN PG_TYPE.TYPNAME = 'bool' THEN 1 END AS FIELD_SIZE, 
        CASE WHEN PG_ATTRIBUTE.ATTNOTNULL = TRUE THEN 0 
            WHEN PG_ATTRIBUTE.ATTNOTNULL = FALSE THEN 1 END AS IS_NULLABLE 
    FROM 
        PG_CLASS INNER JOIN PG_ATTRIBUTE ON PG_ATTRIBUTE.ATTRELID = PG_CLASS.OID 
        INNER JOIN PG_TYPE ON PG_TYPE.OID = PG_ATTRIBUTE.ATTTYPID 
    WHERE 
        PG_CLASS.RELNAME = @TABLE_NAME AND 
        PG_ATTRIBUTE.ATTNUM >= 0 AND 
        PG_CLASS.RELKIND = 'r' 
    ) AS A LEFT JOIN 
    (
    SELECT 
        PG_CLASS.RELNAME AS TABLE_NAME, 
        PG_ATTRIBUTE.ATTNUM AS FIELD_NUM, 
        PG_ATTRIBUTE.ATTNAME AS FIELD_NAME, 
        1 AS IS_PK 
    FROM 
        PG_CLASS INNER JOIN PG_ATTRIBUTE ON PG_ATTRIBUTE.ATTRELID = PG_CLASS.OID 
        INNER JOIN PG_CONSTRAINT ON PG_CLASS.OID = PG_CONSTRAINT.CONRELID 
    WHERE 
        PG_CLASS.RELNAME = @TABLE_NAME AND 
        PG_CONSTRAINT.CONTYPE = 'p' AND 
        PG_ATTRIBUTE.ATTNUM = ANY(PG_CONSTRAINT.CONKEY)
    ) AS B ON A.TABLE_NAME = B.TABLE_NAME AND A.FIELD_NUM = B.FIELD_NUM 
ORDER BY 
    A.TABLE_NAME, 
    A.FIELD_NUM

PostgreSQLで、テーブル一覧を取得する方法です。
対象となるのは、TABLEだけです。
VIEW名などを別の管理表を見れば取れるのですが、フィールド情報を取得できるのがTABLEだけのようです。

PG_TABLESをただ取ってきただけだと、管理表なども取れてしまうので、WHERE句で強引に除外しています。

SELECT 
    SCHEMANAME, 
    TABLENAME 
FROM 
    PG_TABLES 
WHERE 
    NOT TABLENAME LIKE 'pg%' 
    AND NOT SCHEMANAME = 'information_schema' 
ORDER BY 
    SCHEMANAME, 
    TABLENAME

SQLServer Compact Editionでフィールド一覧を取得する方法です。
PKかどうかを判定するために、インデックス表のPKレコードが結合できたときにPKとみなしています。

「@TABLE_NAME」は、バインド変数なので、対象としたいテーブル名を入れてください。

SELECT 
    A.COLUMN_NAME AS FIELD_NAME, 
    A.DATA_TYPE AS FIELD_TYPE, 
    A.AUTOINC_INCREMENT, 
    A.CHARACTER_MAXIMUM_LENGTH, 
    CASE WHEN B.PRIMARY_KEY IS NULL THEN 0 ELSE 1 END AS IS_PK, 
    A.IS_NULLABLE 
FROM 
    INFORMATION_SCHEMA.COLUMNS AS A 
    LEFT OUTER JOIN INFORMATION_SCHEMA.INDEXES AS B ON A.COLUMN_NAME = B.COLUMN_NAME AND B.PRIMARY_KEY = 1 
WHERE 
    A.TABLE_NAME = @TABLE_NAME 
ORDER BY 
    A.ORDINAL_POSITION

以前にも、SQLServerのテーブル一覧を取得するエントリを書いたのですが、Compact Editionではやり方が異なるので、紹介しておきます。

SELECT 
    * 
FROM 
    INFORMATION_SCHEMA.TABLES 
WHERE 
    TABLE_TYPE = 'TABLE' 
ORDER BY 
    TABLE_TYPE, 
    TABLE_NAME

Oracleで、フィールド一覧を取得する方法です。
TABLE、VIEWが対象になります。
ストアドはソースが取得できるのですが、パラメータ情報の取得方法は無さそうです。
面倒くさいけど、地道に解析するしか無さそうです。

「:TABLE_NAME」は、バインド変数なので、対象としたいテーブル名を入れてください。

DATA_PRECISION、DATA_SCALE の２つを組み合わせると、NUMBER型の桁数を求めることができます。
NUMBER型の場合は、常にFIELD_SIZE = 22で返ってくるので、注意してください。

SELECT 
    A.COLUMN_NAME AS FIELD_NAME, 
    A.DATA_TYPE AS FIELD_TYPE, 
    A.DATA_LENGTH AS FIELD_SIZE, 
    DECODE(C.POSITION, NULL, 0, 1) AS IS_PK, 
    A.NULLABLE,
    A.DATA_PRECISION, 
    A.DATA_SCALE 
FROM 
    USER_TAB_COLUMNS A 
    LEFT OUTER JOIN USER_CONSTRAINTS B ON A.TABLE_NAME = B.TABLE_NAME AND B.CONSTRAINT_TYPE = 'P' 
    LEFT OUTER JOIN USER_CONS_COLUMNS C ON B.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND A.COLUMN_NAME = C.COLUMN_NAME 
WHERE 
    A.TABLE_NAME = :TABLE_NAME 
ORDER BY 
    A.TABLE_NAME, 
    A.COLUMN_ID

Oracleで、テーブル一覧を取得する方法です。
対象となるのは、TABLE、VIEW、SEQUENCEです。

SELECT 
    B.OWNER, 
    A.TABLE_NAME, 
    A.TABLE_TYPE 
FROM 
    USER_CATALOG A 
    INNER JOIN ALL_CATALOG B ON A.TABLE_NAME = B.TABLE_NAME

正規化できない人って多いなあと思う、今日この頃。

正規化なんて、初級シスアドにも出てくる程度の常識だと思うのですが。
教科書に載っているようなことを覚えずに、力ずくでコーディングしちゃダメだ。
アルゴリズムを考えるのも確かに大事だけど、こういう定石を覚える方が先だと思う。

いくら頭がよくても、大概の人はこういう定石を考えた人には遠く及ばないので、時間の無駄といえる。
もし同レベルの賢さで、運良く同じ結論にたどり着いたとしても、やっぱり考えた時間は無駄だ。

職場であるソースを眺めていると、集合演算の無いGROUP BY句が出てきました。
しばし考えて、重複行を削除したいのだなと理解したのですが、実際どっちを使う方がいいのでしょうかねえ。

以下、簡単な例です。

SELECT DISTINCT 

	顧客CODE 

FROM 

	注文 

SELECT 

	顧客CODE 

FROM 

	注文 

GROUP BY 

	顧客CODE 

ググってみると、ほとんどのデータベースでGROUP BYの方がパフォーマンスが良いようです。
（とは言っても、劇的に差が出るほどでもない）

本来の考え方からすると、重複行を削除するのはDISTINCTであり、GROUP BYは集合演算をするためのものです。
少しでも早いパフォーマンスが要求されたり、対象レコードが半端じゃない量だったり、繰り返しSQLを実行する必要があるときは仕方無いとしても、速度にほとんど差が無いのであれば、DISTINCTを使う方が本来の意味通りになるため、可読性が向上し、使いやすいんじゃないかなと思うんですけどね。

IDENTITY（自動インクリメント）なフィールドを使っていると、当然出てくるのがIDENTITY値の取得です。
局面としては、子テーブルにレコードを登録する際に親のID番号が必要になるとかそんな感じでしょうか。

くれぐれも、
SELECT MAX(ID) FROM HOGE
なんてしてはダメなんですよね。今登録したIDが最大値なんて保証は無いし、何より遅いし。

一番手っ取り早い方法が、@@IDENTITYを使う方法なんですが、使い方によっては問題もあるようですね。
@@IDENTITY クライシスを管理する

要約すると、@@IDENTITYを使った場合は、トリガーによってIDENTITY値が増えたときの値まで取れてしまうとのこと。
で、その解決方法としてSCOPE_IDENTITY() なんかがあるんですが、有効範囲とか微妙に分かりにくいので、分かりやすく解説されているページを探してみました。
[Landscape - エンジニアのメモ]IDENT_CURRENT / @@IDENTITY / SCOPE_IDENTITY の違い

SCOPE_IDENTITY()の場合、確かにトリガーの影響は受けないものの、有効範囲が非常に小さく（例えば同一ストアド内）使いにくいんですね。
具体的には、以下のような命令を纏めて実行するのは問題ないけど、バラバラに実行するとIDENTITY値はNULLとなってしまいます。

begin transaction;

insert into IDENTITY_TEST (name) values ('hoge');

select SCOPE_IDENTITY();

commit transaction;

※ INDENTITYフィールドとして、IDという列があると思ってください。

問題になりそうなところはトリガーの掛け方だと思うので、そこを注意すれば大した問題でもない気がしますが．．．

以前、SQL文を実行してSQLServer上のテーブル一覧を取得する方法を紹介しましたが、今回はスキーマコレクションから取得する方法です。

参考
SqlConnection.GetSchema メソッド
GetSchema メソッドの使用

参考の例だとSqlConnectionを使っていますが、.NET Framework version 2.0なら DbConnectionを使うこともできます。
実際に他のデータベースで試したことはないですが、テーブル情報取得のSQL文はデータベースによって大きく異なるため、結構使えるかもしれません。

テーブル情報の取得と同じように、SYS***関連のテーブルを結合することで取得できます。
得たい情報によって結合するSYS***が変わりますが、大体こんな感じになるんじゃないかなと思います。
少々長いですが、フィールドの方向やIDENTITYかどうかも取得できるようになっています。
必要に応じて追加・削除してくださいまし。

SELECT 
  CAST(A.NAME AS VARCHAR(40)) AS FIELD_NAME, 
  CAST(C.NAME AS VARCHAR(20)) AS FIELD_TYPE, 
  CASE WHEN A.STATUS & 8 = 8 THEN 1 ELSE 0 END AS DIRECTION_INPUT,
  CASE WHEN A.STATUS & 64 = 64 THEN 1 ELSE 0 END AS DIRECTION_OUTPUT,
  CASE WHEN A.STATUS & 128 = 128 THEN 1 ELSE 0 END AS IS_IDENTITY,
  A.LENGTH AS FIELD_SIZE, 
  CASE WHEN E.COLID IS NULL THEN '0' ELSE '1' END AS PK_FLAG, 
  A.NUMBER AS PROCEDURE_NO 
FROM 
  SYSCOLUMNS AS A 
  INNER JOIN SYSOBJECTS AS B ON B.ID = A.ID 
  INNER JOIN SYSTYPES AS C ON C.XUSERTYPE = A.XTYPE 
  LEFT JOIN SYSINDEXES AS D ON D.ID = A.ID 
      AND D.STATUS & 2048 <> 0 
  LEFT JOIN SYSINDEXKEYS AS E ON E.ID = D.ID 
      AND E.INDID = D.INDID 
      AND E.COLID = A.COLID 
WHERE 
  B.NAME = '対象テーブル名' 
ORDER BY 
  A.NUMBER, 
  A.COLID

このSQLでは、テーブル以外にもストアドの情報まで取れるようになっています。
PROCEDURE_NOが1以上のときは、ストアドで使用される項目となります。

データベース内にどんなテーブルが存在するかは、以下のSQLを実行することで得られる。

SELECT NAME, TYPE 

FROM sysobjects 

ORDER BY TYPE, NAME

所有者もほしいときは、sysusersを結合させて、UID同士を結合させる。

SELECT B.NAME AS OWNER, A.NAME, A.TYPE 

FROM sysobjects A JOIN sysusers B ON A.UID = B.UID 

ORDER BY A.TYPE, A.NAME

システムテーブルが邪魔な場合は、

SELECT B.NAME AS OWNER, A.NAME, A.TYPE 

FROM sysobjects A JOIN sysusers B ON A.UID = B.UID 

WHERE A.TYPE IN('U', 'IF', 'TF', 'V', 'P') AND A.STATUS >= 0 

ORDER BY A.TYPE, A.NAME

使う場合は、自己責任でお願いします。

System.Data.Common 名前空間
ADO.NET でのプロバイダに依存しないコードの作成

これすごいです。
7/4の記事でポリモーフィズムがどうたらとか書いていたんですが、もうどうでもいいです（泣）
VisualStudio.NET 2003までは、各プロバイダ用にサブクラス化していた（さらに言うと、依存DLLの関係でプロジェクトも分けていた）んですが、もうそんな必要は無いようです。

で、早速使ってみました。
たとえば、SqlParameterなんかの場合はDbParameterなんて感じになります。
基本的にDb****という命名になるようですね。
Newする際には、ファクトリクラスを使ってCreateするだけのお手軽さでした。
デバッグ実行でブレークしたついでに、オブジェクトの状態を見てみると、ちゃんとプロバイダごとのオブジェクトが生成されてます！
速度も特に遅いと感じたこともなかったので、今後はこれがスタンダードになるのかな。

O/Rマッピングを調査中です。
というか、激しく悩んでいます。

O/Rマッピングを簡単に説明すると、オブジェクトとリレーショナルデータベースを自動的にマッピングする仕組みです（簡単すぎ：汗）。
Hibernateなんかが有名なんですが、SQL文、データベース名とオブジェクト名のマッピング、テーブルのカラムとクラスのフィールドのマッピング等をXMLに定義しておくことで、自動的に変換してくれる仕組みです。

これを導入することで、オブジェクト指向とリレーショナルデータベースの思想の違い（インピーダンスミスマッチ）を解決できるし、ビジネスロジックにSQLが紛れ込むこともなくなります。
また、SQLがXMLに書かれるため、メンテナンスがし易いといったメリットもあるのです。

当然のことながら、C#でも実装できますし、オープンソースでもいくつかあります。
ところが、.NETではDataSetというのがあって、これを使うことでデータグリッド等の表示が簡単に行えるので、わざわざエンティティクラスを定義する必要があるのか微妙なところです。
というか、DataSetをエンティティクラスの代わりに使うこともできちゃいますし。（現実世界の構造とは狂うかもしれないが）
あと、DataTableを変更したときに、変更内容に応じて自動的にInsert、Update、Deleteを行ってくれる機能まであったりする。
しかも、Abstractクラスでメソッド宣言するときに、パラメータなんかとインタフェースで定義しておいて、実装はサブクラスで行うようにすれば、ADO.NETだけでなく、ODP.NETやpgsqlでも、ポリモーフィズムが実現できる。

また、SQLをXMLに書くというのも、私は大歓迎ですが、O/Rマッピングを知らないエンジニアがすんなり受け入れてくれるとも思えないんですよねえ。
とりあえず、
・SQLをXMLに書かないが、データベース層に該当するクラス内に閉じ込める
・DataSetを返す
・機能（SELECT、INSERT、UPDATE、DELETE）と実装（SQLの実行、トランザクション処理、コネクション管理）を分ける
といったあたりを念頭にコーディングしてみることとする。