テーブル一覧、テーブル定義情報取得用SQL
関連ツールを用いて作成したDBテーブル定義から、自動で
- テーブル定義情報のDTO(フィールド、setter/getter)
- テーブル情報1レコードに対するSELECT/INSERT/UPDATE/DELETEの処理を行う一連のサービスクラス(ベース:SpringJDBC)
を作れないかなと思っている。
最終的にはWebアプリケーションで「テーブル一覧」→「テーブル詳細」→「各種クラス作成(DTO/SQL/Entity等など)」を行えるものを用意したいと思っているのだが、それは先の話として…。
とりあえず手始めに、その辺の機能を満たすものを作るために必要なSQL文から調べてみる。
- 対象テーブル一覧の取得
SELECT
sut.relid,
sut.schemaname,
sut.relname,
des.objsubid,
des.description
FROM
pg_stat_user_tables sut,
pg_description des
WHERE
sut.relid = des.objoid
AND des.objsubid = 0
WHERE句の最後の行、「des.objsubid = 0」は、テーブルのコメント文(ここではテーブル名をコメントとして登録した)を取得するための条件。テーブル名のコメント(0)→テーブル項目名のコメント(1…)と続いていたので、ここでは0(テーブル名のコメント?)を指定したが、果たしてこれは正しいのだろうか…。
- 対象テーブルの詳細情報取得
SELECT
att.attnum AS NUM,
att.attname AS COL_NAME,
typ.typname AS COL_TYPE,
att.attlen AS COL_LENGTH,
CASE att.attnotnull
WHEN 't' THEN 'NOT NULL'
WHEN 'f' THEN 'NULL'
END AS NOTNULL,
com.description AS COL_COMMENTFROM
pg_attribute att,
pg_stat_user_tables sut,
pg_type typ,
pg_description com
WHERE
att.attrelid = sut.relid
AND att.attrelid = com.objoid
AND att.attnum = com.objsubid
AND att.atttypid = typ.oid
AND att.attnum > 0
AND sut.relname = '(テーブル名)'ORDER BY
att.attnum
項目名(英字)と項目名(日本語名:コメントから編集)はフィールド、setter/getterメソッド(及びそのコメント部分)を作成する上で必須。
SELECT時の値の取得方法やINSERT/UPDATE時のフォーマット指定の判定材料として、データの型も必要。
桁数は…DTOとかDBアクセスクラスを作成する上では無くても平気かな。桁数はエラーチェック方面の話になってくるし。
SQL作成にあたっては以下の情報を参考にさせて頂きました。m(_ _)m
- [pgsql-jp: 24896] Re: テーブル情報の取得方法
- システムカタログを参照する
- 2007/03/16 PostgreSQL でカラム一覧を取得 (HiNa) - fetus Diary - fetus
- テーブルのカラム名とデータ型を、SQLを使って動的に取得する。 - 永字八法