カスタムSQL文に必要な情報

デフォルトで、 Connection Manager は DB2 、 Oracle 、 MS SQL Server データソース用のデフォルトSQL文を提供します。

カスタムデータソースカテゴリを選択した場合、自身のカスタムSQL文を入力する必要があります。

「テーブルと列」タブで。
制約、一意性、参照整合性のすべてあるいはいずれかのデータをテストしたい場合、「一意性」タブと「外部キー」タブで。

「 General 」セクションで「一意性情報を取得」チェックボックスおよび/または「参照整合性情報を取得」チェックボックスを無効化させている場合、「 Connection Manager 」は「一意性」および/または「外部キー」SQLタブをそれぞれグレーアウトします。

デフォルトのSQL文をカスタム文で上書きすることも可能です。SQL文を入力して確認する方法については、 see chapter "SQLステートメントの検証" を参照してください。

以下の章では、カスタムSQL文に必須のヘッダーをすべて紹介しています。

テーブルと列

「テーブルと列」タブのSQL文には、以下の情報を含める必要があります。

ヘッダー	説明
Schema	スキーマ名
Table	テーブル名
Column	列名
Type	列のデータタイプ
Length	列の長さ
Scale	列のスケール
Nullable	列がnullableかどうかの情報。値は「 Y 」または「 N 」でなければなりません。

例

以下は、 PostgreSQL 10 でありうる、カスタムの「テーブルと列」SQL文の例です。実際のSQLステートメントは、実行したいクエリの種類によって異なります。

SELECT   c.table_schema AS "SCHEMA", 
             c.table_name   AS "TABLE", 
             c.column_name  AS "COLUMN", 
             c.data_type    AS "TYPE", 
             CASE 
                      WHEN c.character_maximum_length IS NULL THEN c.numeric_precision 
                      ELSE c.character_maximum_length 
                             END as "LENGTH", 
             c.numeric_scale AS "SCALE", 
             CASE c.is_nullable 
                      WHEN 'YES'THEN 'Y'
                      ELSE 'N'
             END AS "NULLABLE"
    FROM     information_schema.columns c 
    JOIN     information_schema.tables t 
    ON       c.table_schema = t.table_schema 
    AND      c.table_name = t.table_name 
    WHERE    t.table_type = 'BASE TABLE'
    AND      c.table_schema = 'public'
ORDER BY c.table_schema, 
         c.table_name, 
         c.ordinal_position offset 0 rows;
                                                    

一意性

「一意性」タブのSQL文には、以下の情報が含まれていなければなりません。

ヘッダー	説明
Schema	スキーマ名
Table	テーブル名
Constraint	制約名
Column	列名
Rule	制約のタイプに関する情報。許容される値: 一意性制約用の「U 」プライマリキー制約用の「P 」

例

以下は、 PostgreSQL 10 用のカスタム一意性SQL文の例です。実際のSQLステートメントは、実行したいクエリの種類によって異なります。

SELECT   c.table_schema    AS "SCHEMA", 
         c.table_name      AS "TABLE", 
         c.constraint_name AS "CONSTRAINT", 
         s.column_name     AS "COLUMN", 
         CASE c.constraint_type 
                  WHEN 'UNIQUE' THEN 'U' 
                  ELSE 'P' 
         END as "RULE"
FROM     information_schema.table_constraints c 
JOIN     information_schema.key_column_usage s 
ON       c.constraint_name = s.constraint_name 
WHERE   ( 
                  constraint_type = 'UNIQUE'
         OR       constraint_type = 'PRIMARY KEY') 
ORDER BY c.table_schema, 
         c.table_name, 
         c.constraint_name, 
         s.ordinal_position offset 0 rows;
                                                    

外部キー

「外部キー」タブのSQL文には、以下の情報が含まれていなければなりません。

ヘッダー	説明
Schema	スキーマ名
Table	テーブル名
Constraint	外部キー名
Column	列名
ParentSchema	外部キーが指すスキーマ名
ParentTable	外部キーが指すテーブル名
ParentColumn	外部キーが指す列名

例

以下は、 PostgreSQL 10 用のカスタム外部キー SQL文の例です。実際のSQLステートメントは、実行したいクエリの種類によって異なります。

SELECT  t.table_schema as "SCHEMA",
        t.table_name as "TABLE",
        c.column_name as "COLUMN",
        r.constraint_name as "CONSTRAINT",
        k2.table_schema as "PARENTSCHEMA",
        k2.table_name as "PARENTTABLE",
        k2.column_name as "PARENTCOLUMN"       
FROM information_schema.tables t
    LEFT JOIN pg_catalog.pg_description td
        ON (td.objoid = (quote_ident(t.table_schema)||'.'||quote_ident(t.table_name))::regclass AND td.objsubid = 0)
    NATURAL LEFT JOIN information_schema.columns c
        LEFT JOIN(
            information_schema.key_column_usage k
                NATURAL JOIN information_schema.table_constraints n
                NATURAL LEFT JOIN information_schema.referential_constraints r)
        ON c.table_catalog=k.table_catalog
        AND c.table_schema=k.table_schema
        AND c.table_name=k.table_name
        AND c.column_name=k.column_name
    LEFT JOIN information_schema.key_column_usage k2
        ON k.position_in_unique_constraint=k2.ordinal_position
        AND r.unique_constraint_catalog=k2.constraint_catalog
        AND r.unique_constraint_schema=k2.constraint_schema
        AND r.unique_constraint_name=k2.constraint_name
    LEFT JOIN pg_catalog.pg_description cd
        ON (cd.objoid = (quote_ident(t.table_schema)||'.'||quote_ident(t.table_name))::regclass AND cd.objsubid = c.ordinal_position)
WHERE t.TABLE_TYPE='BASE TABLE' AND t.table_schema NOT IN('information_schema','pg_catalog') AND n.constraint_type = 'FOREIGN KEY'
ORDER BY t.table_schema, t.table_name, r.constraint_name, k.ordinal_position;
                                                    