テーブルの定義
ここでは、InterSystems SQL でテーブルを作成する方法を説明します。
テーブル名とスキーマ名
テーブルは、テーブルの定義 (CREATE TABLE を使用) またはテーブルに投影される永続クラスの定義によって作成できます。
-
DDL : InterSystems IRIS® データ・プラットフォームでは、CREATE TABLE に指定したテーブル名を使用して、対応する永続クラス名が生成され、指定したスキーマ名を使用して、対応するパッケージ名が生成されます。
-
クラス定義 : InterSystems IRIS® データ・プラットフォームでは、永続クラス名を使用して対応するテーブル名が生成され、パッケージ名を使用して、対応するスキーマ名が生成されます。
これら 2 つの名前の対応は、以下の理由により、同一ではない場合があります。
-
永続クラスおよび SQL テーブルは、異なる名前付け規約に従います。適用される有効な文字と長さの要件が異なります。スキーマ名とテーブル名では大文字と小文字が区別されませんが、パッケージ名とクラス名では大文字と小文字が区別されます。システムによって、指定された有効な名前が対応する有効な名前に自動的に変換され、生成される名前は必ず一意になります。
-
既定で、永続クラス名と対応する SQL テーブル名は一致します。SqlTableName クラス・キーワードを使用して、異なる SQL テーブル名を指定できます。
-
既定のスキーマ名は、既定のパッケージ名と一致しない場合があります。未修飾の SQL テーブル名または永続クラス名を指定すると、システムによって既定のスキーマ名またはパッケージ名が指定されます。初期の既定のスキーマ名は SQLUser で、初期の既定のパッケージ名は User です。
スキーマ名
テーブル名、ビュー名、ストアド・プロシージャ名は修飾する (schema.name) ことも、修飾しない (name) ことも可能です。
-
スキーマ名を指定する場合 (修飾名)、指定されたテーブル、ビュー、またはストアド・プロシージャがそのスキーマに割り当てられます。スキーマが存在しない場合、InterSystems SQL はスキーマを作成し、テーブル、ビュー、またはストアド・プロシージャをそのスキーマに割り当てます。
-
スキーマ名を指定しない場合 (未修飾名)、InterSystems SQL は既定のスキーマ名またはスキーマ検索パスを使用してスキーマを以下のように割り当てます。
このセクションでは、以下のトピックについて説明します。
スキーマの名前付けに関する考慮事項
スキーマ名は、識別子の規約に従い、英数字以外の文字の使用法に関して多くの考慮事項があります。スキーマ名を区切り識別子として指定することはできません。“USER” またはその他の SQL 予約語をスキーマ名として指定しようとすると、SQLCODE -312 エラーとなります。INFORMATION_SCHEMA スキーマ名および対応する INFORMATION.SCHEMA パッケージ名は、すべてのネームスペースで予約されています。このスキーマおよびパッケージ内で、テーブルまたはクラスを作成しないでください。
CREATE TABLE などの create 操作を実行する場合に、まだ存在していないスキーマを指定すると、InterSystems IRIS によって新しいスキーマが作成されます。InterSystems IRIS はスキーマ名を使用して、対応するパッケージ名を生成します。スキーマの名前付け規約と対応するパッケージの名前付け規約は異なるため、ユーザは名前を変換する際の英数字以外の文字に関する考慮事項に注意する必要があります。これらの名前変換の考慮事項はテーブルと同じではありません。
-
最初の文字 :
-
% (パーセント) : % をスキーマ名の最初の文字として指定すると、対応するパッケージがシステム・パッケージであり、そのすべてのクラスがシステム・クラスであることを示します。この使用には、適切な特権が必要です。そうでないと、<PROTECT> エラーを示す %msg を含む SQLCODE -400 エラーが出力されます。
-
_ (アンダースコア) : スキーマ名の最初の文字がアンダースコア文字である場合、この文字は対応するパッケージ名内で小文字の “u” に置き換えられます。例えば、スキーマ名 _MySchema は、パッケージ名 uMySchema を生成します。
-
-
後続の文字 :
-
_ (アンダースコア) : スキーマ名の最初の文字以外の任意の文字がアンダースコア文字である場合、この文字は対応するパッケージ名内でピリオド (.) に置き換えられます。ピリオドはクラス区切り文字であるため、アンダースコアはスキーマをパッケージとサブパッケージに分割します。したがって、My_Schema はパッケージ Schema を含むパッケージ My を生成します (My.Schema)。
-
@、#、$ 文字 : スキーマ名にこれらの文字のいずれかが含まれる場合、これらの文字は対応するパッケージ名から削除されます。これらの文字を削除することで重複したパッケージ名が生成される場合、削除されたパッケージ名がさらに変更されます。削除されたスキーマ名の最後の文字が連続した整数 (0 で開始) で置き換えられ、一意のパッケージ名が生成されます。したがって、My@#$Schema はパッケージ MySchema を生成し、以降に作成される My#$Schema は、パッケージ MySchem0 を生成します。同じルールはクラス名に対応するテーブル名にも適用されます。
-
予約スキーマ名
INFORMATION_SCHEMA スキーマ名および対応する INFORMATION.SCHEMA パッケージ名は、すべてのネームスペースで予約されています。このスキーマおよびパッケージ内で、テーブルまたはクラスを作成しないでください。
IRIS_Shard スキーマ名は、すべてのネームスペースで予約されています。このスキーマ内で、テーブル、ビュー、またはプロシージャを作成しないでください。IRIS_Shard スキーマに格納される項目は、カタログ・クエリでも、INFORMATION_SCHEMA クエリでも表示されません。
既定のスキーマ名
-
テーブル、ビュー、トリガ、またはストアド・プロシージャの作成や削除などの DDL 操作を実行する場合、未修飾名として既定のスキーマ名が指定されます。スキーマ検索パスの値は無視されます。
-
既存のテーブル、ビュー、またはストアド・プロシージャにアクセスして、SELECT、CALL、INSERT、UPDATE、DELETE などの DML 操作を実行する場合、未修飾名としてスキーマ検索パス (指定されている場合) からのスキーマ名が指定されます。スキーマ検索パスが指定されていない場合、またはスキーマ検索パスを使用して名前の付いた項目が見つからない場合、既定のスキーマ名が指定されます。
初期設定では、すべてのネームスペース (システム全体) に同じ既定のスキーマ名を使用します。すべてのネームスペースに同じ既定のスキーマ名を設定するか、現在のネームスペースに既定のスキーマ名を設定できます。
未修飾名を使用してテーブルまたはその他の項目を作成すると、InterSystems IRIS によってその項目に既定のスキーマ名と対応する永続クラス・パッケージ名が割り当てられます。名前の付いたスキーマまたは既定のスキーマが存在しない場合、InterSystems IRIS によってスキーマ (およびパッケージ) が作成され、作成した項目がそのスキーマに割り当てられます。スキーマの最後の項目を削除すると、InterSystems IRIS によって、そのスキーマ (およびパッケージ) が削除されます。スキーマ名の解析に関する以下の説明は、テーブル名、ビュー名、およびストアド・プロシージャ名にも当てはまります。
初期のシステム全体の既定の SQL スキーマ名は、SQLUser です。対応する永続クラス・パッケージ名は User です。このため、未修飾テーブル名 Employee か修飾付きテーブル名 SQLUser.Employee のいずれかによって、クラス User.Employee が生成されます。
USER は予約語であるため、スキーマ名を User (または他の SQL 予約語) にして修飾名を指定しようとすると、SQLCODE -1 エラーが返されます。
現在の既定のスキーマ名を返すには、$SYSTEM.SQL.Schema.Default()Opens in a new tab メソッドを呼び出します。
または、以下のプリプロセッサ・マクロを使用します。
#include %occConstant
WRITE $$$DefSchema
以下のいずれかを使用して、既定のスキーマ名を変更できます。
-
管理ポータルに移動します。[システム管理] から、[構成]、[SQL およびオブジェクトの設定]、[SQL] の順に選択します。この画面で、[既定のスキーマ] の現在のシステム全体の設定を表示および編集できます。このオプションでは、システム全体の既定のスキーマ名が設定されます。このシステム全体の設定は、現在のネームスペースの SetDefault() メソッド値によってオーバーライドできます。
-
$SYSTEM.SQL.Schema.SetDefault()Opens in a new tab メソッド。既定では、このメソッドはシステム全体の既定のスキーマ名を設定します。ただし、ブーリアンの 3 番目の引数 = 1 を設定することで、現在のネームスペースのみの既定のスキーマを設定できます。ネームスペースごとに異なる既定のスキーマ名が設定されている場合、$SYSTEM.SQL.CurrentSettings()Opens in a new tab メソッドは現在のネームスペースの既定のスキーマ名を返します。
デフォルト SQL スキーマ名を変更すると、システム上のすべてのネームスペースのすべてのクエリ・キャッシュが自動的に削除されます。デフォルト・スキーマ名を変更すると、未修飾のテーブル、ビュー、またはストアド・プロシージャ名を含むすべてのクエリの意味が変更されることになります。デフォルト SQL スキーマ名は InterSystems IRIS のインストール時に設定し、その後は変更しないことを強くお勧めします。
スキーマ名は、対応するクラス・パッケージ名の生成に使用されます。これらの名前は名前付け規約が異なるため、同一ではない場合があります。
システム全体の既定のスキーマとして SQL 予約語を設定することで、SQL 予約語と同じ名前でスキーマを作成できますが、これは推奨されません。クラスの命名の一意性に関する規約に従って、User という名前の既定のスキーマにより、対応するクラス・パッケージ名 Use0 が生成されます。
_CURRENT_USER キーワード
-
システム全体の既定のスキーマ名として使用 : _CURRENT_USER を既定のスキーマ名として指定すると、現在ログインしているプロセスのユーザ名が既定のスキーマ名として割り当てられます。_CURRENT_USER の値は、ObjectScript 特殊変数値 $USERNAME の最初の部分です。$USERNAME が名前とシステム・アドレスで構成される場合 (Deborah@TestSys)、_CURRENT_USER には名前の部分のみが含まれます。つまり、_CURRENT_USER は、同じ既定のスキーマ名を複数のユーザに割り当てることができます。プロセスがログインしていない場合、_CURRENT_USER では SQLUser が既定のスキーマ名として指定されます。
_CURRENT_USER/name (name は任意の文字列) を既定のスキーマ名として指定すると、現在ログインしているプロセスのユーザ名が既定のスキーマ名として割り当てられます。プロセスがログインしていない場合、name が既定のスキーマ名として使用されます。例えば、_CURRENT_USER/HMO は、プロセスがログインしていないときは既定のスキーマ名として HMO を使用します。
$SYSTEM.SQL.Schema.SetDefault()Opens in a new tab で、"_CURRENT_USER" を引用符付き文字列として指定します。
-
DDL コマンドのスキーマ名として使用 : _CURRENT_USER を DDL 文で明示的なスキーマ名として指定すると、これは、現在のシステム全体の既定のスキーマに置き換えられます。例えば、システム全体の既定のスキーマが SQLUser の場合、コマンド DROP TABLE _CURRENT_USER.OldTable では、SQLUser.OldTable が削除されることになります。これは、システム全体の既定のスキーマを使用する必要があることを明示的に示すために名前を修飾する便利な方法です。これは、未修飾名の指定と機能的に同じです。このキーワードを DML 文で使用することはできません。
スキーマ検索パス
DML 操作のために既存のテーブル (またはビューかストアド・プロシージャ) にアクセスする場合、未修飾名としてスキーマ検索パスからのスキーマ名が指定されます。スキーマは、指定された順序で検索され、最初の一致が返されます。検索パスに指定されているスキーマで一致が見つからない場合、または検索パスが存在しない場合、既定のスキーマ名が使用されます (#import マクロ指示文では別の検索方法が使用され、既定のスキーマ名に "フォールスルー" することはありません)。
-
埋め込み SQL では、#sqlcompile path マクロ指示文または #import マクロ指示文を使用して、InterSystems IRIS で未修飾名の解決に使用するスキーマ検索パスを指定できます。#sqlcompile path は、最初に検出された一致で未修飾名を解決します。#import は、検索パス内にリストされているすべてのスキーマに対して一致がちょうど 1 つある場合に未修飾名を解決します。
-
以下の例では、2 つのスキーマ名を記述して検索パスを指定しています。
#sqlcompile path=Customers,Employees
詳細は "マクロ・プリプロセッサ指示文" を参照してください。
-
ダイナミック SQL では、%SchemaPath プロパティを使用して、InterSystems IRIS で未修飾テーブル名の解決に使用するスキーマ検索パスを指定できます。%SchemaPath プロパティを直接指定することも、このプロパティを %SQL.StatementOpens in a new tab の %New()Opens in a new tab メソッドの 2 つ目のパラメータとして指定することもできます。以下の例では、2 つのスキーマ名を記述して検索パスを指定しています。
SET tStatement = ##class(%SQL.Statement).%New(0,"Customers,Employees")
詳細は、“ダイナミック SQL の使用” を参照してください。
-
SQL シェルでは、SQL シェル構成パラメータ PATH を使用して、InterSystems IRIS で未修飾テーブル名の解決に使用するスキーマ検索パスを指定できます。
スキーマ検索パスで指定されているスキーマのどれとも未修飾名が一致しなかったり既定のスキーマ名とも未修飾名が一致しない場合、SQLCODE -30 エラー (例えば、"SQLCODE: -30 メッセージ: スキーマ内でテーブル 'PEOPLE' が見つかりません: CUSTOMERS,EMPLOYEES,SQLUSER") が発行されます。
プラットフォーム固有のスキーマ名を含める
Mac で ODBC ベースのクエリを作成し、Microsoft Query を使用して Microsoft Excel から実行する場合、使用可能なテーブルのリストからテーブルを選択すると、生成されたクエリにはテーブルのスキーマ (クラスのパッケージと同等のもの) は含まれません。例えば、Sample スキーマから Person テーブルのすべての行を返すよう選択すると、生成されるクエリは以下のようになります。
SELECT * FROM Person
InterSystems IRIS では未修飾のテーブル名は SQLUser スキーマにあるものとして解釈されるため、この文は、失敗するか、間違ったテーブルからデータを返します。これを修正するには、[SQL ビュー] タブで、必要なスキーマを明示的に参照するようにクエリを編集します。クエリは以下のようになります。
SELECT * FROM Sample.Person
スキーマのリスト
INFORMATION.SCHEMA.SCHEMATAOpens in a new tab 永続クラスは、現在のネームスペース内のすべてのスキーマをリストします。
以下の例では、現在のネームスペース内のすべての非システム・スキーマの名前が返されます。
SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE NOT SCHEMA_NAME %STARTSWITH '%'
管理ポータルの左側にある SQL インタフェースでは、スキーマ (またはフィルタ・パターンに一致する複数のスキーマ) のコンテンツを表示できます。詳細は、"スキーマ・コンテンツのフィルタ処理" を参照してください。
テーブル名
各テーブルには、スキーマ内で一意の名前が付けられています。テーブルには SQL テーブル名および対応する永続クラス名の両方があります。これらの名前は、許可されている文字、大文字と小文字の区別、および最大長が異なります。SQL CREATE TABLE コマンドを使用して定義されている場合は、識別子の規約に従った SQL テーブル名を指定します。対応する永続クラス名がシステムで生成されます。永続クラス定義として定義されている場合は、英数字のみを含む名前を指定する必要があります。この名前が、大文字小文字を区別する永続クラス名および (既定では) 対応する大文字と小文字を区別しない SQL テーブル名の両方に使用されます。オプションの SqlTableName クラス・キーワードを使用して、別の SQL テーブル名を指定できます。
CREATE TABLE コマンドを使用してテーブルを作成する場合、InterSystems IRIS はテーブル名を使用して対応する永続クラス名を生成します。テーブルの名前付け規約と対応するクラスの名前付け規約は異なるため、ユーザは名前を変換する際の英数字以外の文字に関する考慮事項に注意する必要があります。
-
最初の文字 :
-
% (パーセント) : テーブル名の最初の文字として % が予約されているため、避ける必要があります ("識別子" を参照)。指定した場合、% 文字は対応する永続クラス名から削除されます。
-
_ (アンダースコア) : テーブル名の最初の文字がアンダースコア文字である場合、この文字は対応する永続クラス名から削除されます。例えば、テーブル名 _MyTable は、クラス名 MyTable を生成します。
-
数値 : テーブル名の最初の文字を数値にすることはできません。テーブル名の最初の文字が句読点文字の場合、2 番目の文字に数字を指定することはできません。これにより、SQLCODE -400 エラーが発生し、生成される %msg の値は “エラー #5053 : クラス名 'schema.name' が無効です” になります (句読点文字なし)。例えば、指定したテーブル名が _7A の場合、生成される %msg は “エラー #5053: クラス名 'User.7A' が無効です” になります。
-
-
後続の文字 :
-
文字 : テーブル名には、最低でも 1 文字を含める必要があります。テーブル名の先頭の文字または最初の句読点に続く文字は、数字以外の文字にする必要があります。$ZNAME テストに合格した文字は、有効な文字です。$ZNAME 文字検証はロケールによって異なります (識別子には句読点文字を含めることができるため、$ZNAME を使用して SQL 識別子を検証することはできません)。
-
_ (アンダースコア)、@、#、$ 文字 : テーブル名にこれらの文字のいずれかが含まれる場合、これらの文字は対応するクラス名から削除され、一意の永続クラス名が生成されます。生成されたクラス名には句読点が含まれないため、句読点のみが異なるテーブル名の作成はお勧めできません。
-
-
テーブル名は、そのスキーマ内で重複しないようにする必要があります。既存テーブルと大文字/小文字区別のみが異なる名前でテーブルを作成しようとすると、SQLCODE -201 エラーが生成されます。
ただし、同じスキーマ内のビューおよびテーブルには、同じ名前を指定できません。これを実行しようとすると、SQLCODE -201 エラーが返されます。
$SYSTEM.SQL.Schema.TableExists()Opens in a new tab メソッドを使用して、テーブル名が既に存在するかどうかを確認できます。$SYSTEM.SQL.Schema.ViewExists()Opens in a new tab メソッドを使用して、ビュー名が既に存在するかどうかを確認できます。これらのメソッドは、テーブル名またはビュー名に対応するクラス名も返します。管理ポータルの SQL インタフェース [カタログの詳細] の [テーブル情報] オプションでは、選択した SQL テーブル名に対応するクラス名が表示されます。
“USER“ またはその他の SQL 予約語をスキーマ名として指定しようとすると、SQLCODE -312 エラーとなります。SQL 予約語をテーブル名またはスキーマ名として指定するには、名前を区切り文字付き識別子として指定します。区切り文字付き識別子を使用して英数字以外の文字を含むテーブルまたはスキーマ名を指定した場合、InterSystems IRIS は対応するクラスまたはパッケージ名を生成する際にこれらの英数字以外の文字を削除します。
以下のテーブル名の長さの制限が適用されます。
-
一意性 : InterSystems IRIS は永続クラス名の最初の 189 文字で一意性チェックを実行します。対応する SQL テーブル名は 189 文字を超える長さになる可能性がありますが、英数字以外の文字を取り除くと、この 189 文字の制限内で一意である必要があります。InterSystems IRIS はパッケージ名の最初の 189 文字で一意性チェックを実行します。
-
推奨される最大長 : 一般に、テーブル名は 128 文字を超えることはできません。テーブル名は 96 文字よりも大幅に長くすることができますが、最初の 96 の英数文字が異なるようにテーブル名を作成すると処理がはるかに容易になります。
-
合計最大長 : パッケージ名とその永続クラス名 (同時に追加する場合) は、220 文字を超えることはできません。これには、既定のスキーマ (パッケージ) 名 (スキーマ名が指定されていない場合) およびパッケージ名とクラス名を区切るドット文字が含まれます。テーブル名が対応する永続クラス名に変換されるときに 220 文字を超える文字が削除される場合は、スキーマ名とテーブル名の合計を 220 文字より長くすることができます。
テーブル名の詳細は、"CREATE TABLE" コマンドを参照してください。さらなる詳細は、"クラスの定義" を参照してください。
RowID フィールド
SQL では、すべてのレコードは RowID という一意の整数値によって識別されます。InterSystems SQL では、RowID フィールドを指定する必要はありません。テーブルを作成して希望のデータ・フィールドを指定するとき、RowID フィールドが自動的に作成されます。この RowID は内部的に使用されますが、クラス・プロパティにマップはされません。既定では、永続クラスが SQL テーブルに投影される場合にのみ、表示されます。この投影されたテーブルでは、追加の RowID フィールドが表示されます。既定では、このフィールドは "ID" と名付けられ、列 1 に割り当てられます。
既定では、テーブルにデータが入力されるときに、InterSystems IRIS は、このフィールドに 1 から始まる連続した正の整数を割り当てます。RowID データ型は BIGINT (%Library.BigIntOpens in a new tab) です。RowID 用に生成される値には、以下の制約があります。各値は一意です。NULL 値は許可されていません。照合は EXACT です。既定では、値を変更することはできません。
既定では、InterSystems IRIS は、このフィールドに “ID” という名前を付けます。ただし、このフィールド名は予約されていません。RowID フィールド名は、テーブルがコンパイルされるたびに再設定されます。ユーザが “ID” という名前のフィールドを定義している場合、テーブルがコンパイルされると、InterSystems IRIS は RowID に “ID1” という名前を付けます。例えば、その後でユーザが ALTER TABLE を使用して “ID1” という名前のフィールドを定義すると、テーブルのコンパイルで RowID の名前が “ID2” に変更されます (それ以降も同様に動作します)。永続クラス定義では、SqlRowIdName クラス・キーワードを使用して、このクラスが投影されるテーブルの RowID フィールド名を直接指定できます。これらの理由により、名前で RowID フィールドを参照することは避ける必要があります。
InterSystems SQL では、どのようなフィールド名が RowID に割り当てられていても常に RowID 値を返す、%ID 疑似列名 (エイリアス) を提供しています。(InterSystems TSQL では、同じ働きをする $IDENTITY 疑似列名を提供しています。)
ALTER TABLE では、RowID フィールド定義の変更や削除はできません。
レコードをテーブルに挿入すると、InterSystems IRIS は各レコードに整数の ID 値を割り当てます。RowID の値は必ず増分になります。再使用されることはありません。そのため、レコードが挿入および削除された場合、RowID の値は昇順の数値にはなりますが、数値としての連続性はなくなります。
-
既定では、CREATE TABLE を使用して定義されたテーブルは、複数のプロセスによるテーブルへの迅速な同時入力ができる $SEQUENCE を使用して ID の割り当てを実行します。$SEQUENCE を使用してテーブルにデータを入力する際、一連の RowID 値がプロセスに割り当てられ、その後、プロセスがそれらを連続的に割り当てます。同時プロセスがそれぞれ独自の割り当てられたシーケンスを使用して RowID を割り当てるため、複数のプロセスによって挿入されたレコードが挿入順であると見なすことはできません。
SetOption()Opens in a new tab メソッドの DDLUseSequence オプションを設定すると、InterSystems IRIS が $INCREMENT を使用して ID 割り当てを実行するように構成できます。現在の設定を確認するには、$SYSTEM.SQL.CurrentSettings()Opens in a new tab メソッドを呼び出します。
-
既定では、永続クラスを作成して定義したテーブルが、$INCREMENT を使用して ID 割り当てを実行します。永続クラス定義では、IdFunction ストレージ・キーワードを sequence または increment に設定できます (例えば、<IdFunction>sequence</IdFunction>)。
永続クラス定義では、IdLocation ストレージ・キーワード・グローバル (例えば、永続クラス Sample.Person の場合は <IdLocation>^Sample.PersonD</IdLocation>) には、割り当てられた最も大きい RowID カウンタ値が含まれます。(これはレコードに割り当てられた最も大きい整数であり、プロセスに割り当てられた最も大きい整数ではありません。)この RowID カウンタ値は、既存のレコードとは対応しなくなる可能性があることに注意してください。特定の RowID 値を持つレコードが存在しているかどうかを判別するには、テーブルの %ExistsId() メソッドを呼び出します。
RowID カウンタは、TRUNCATE TABLE コマンドによってリセットされます。このカウンタは、DELETE コマンドがテーブル内のすべての行を削除した場合でも、DELETE コマンドによってはリセットされません。テーブルにデータが挿入されていない場合、または TRUNCATE TABLE を使用してすべてのテーブル・データが削除されている場合、IdLocation ストレージ・キーワード・グローバル値は定義されません。
既定では、RowID の値をユーザが変更することはできません。RowID 値を変更しようとすると、SQLCODE -107 エラーが生成されます。この既定値をオーバーライドして RowID 値を変更できるようにすると、取り返しの付かない結果を招くことがあるため、極めて特別な状況でのみ、十分な注意を払って行う必要があります。Config.SQL.AllowRowIDUpdateOpens in a new tab プロパティにより、RowID の値のユーザによる変更を許可します。
フィールドに基づく RowID
テーブルを投影する永続クラスを定義することで、RowID にフィールドの値またはフィールドの組み合わせの値を設定するよう定義できます。それには、IdKey インデックス・キーワードでインデックスを指定します。例えば、インデックス定義 IdxId On PatientName [IdKey]; を指定して、テーブルに PatientName フィールドの値と同じ値の RowID を設定したり、インデックス定義 IdxId On (PatientName,SSN) [IdKey]; を指定して、PatientName フィールドと SSN フィールドを組み合わせた値の RowID を設定できます。
-
フィールドに基づく RowID は、システムで割り当てられる連続する正の整数を取る RowID よりも非効率的です。
-
INSERT 時 : RowID を構成するフィールドまたはフィールドの組み合わせに指定される値は、一意である必要があります。一意でない値を指定すると、SQLCODE -119 "UNIQUE あるいは PRIMARY KEY 制約が INSERT の一意性チェックに失敗しました" が生成されます。
-
UPDATE 時 : 既定では、RowID を構成する各フィールドの値は変更できません。これらのいずれかのフィールドの値を変更しようとすると、SQLCODE -107 "RowID またはフィールドに基づく RowID を UPDATE できません" が生成されます。
RowID が複数のフィールドに基づく場合、RowID 値は各コンポーネント・フィールドの値を || 演算子で結合した値になります (Ross,Betsy||123-45-6789 など)。InterSystems IRIS は複数のフィールドに基づく RowID の最大長の特定を試み、最大長を特定できない場合、RowID の長さは既定で 512 になります。
詳細は、"主キー" を参照してください。
主キー
InterSystems IRIS では、テーブル内の行を一意に識別する RowID と主キーの 2 つの方法が用意されています。
オプションの主キーは、アプリケーションがテーブル内の行を一意に識別するために使用できる (例えば、結合で)、意味のある値です。主キーは、ユーザ指定のデータ・フィールドまたは複数のデータ・フィールドの組み合わせにできます。主キーの値は一意でなければなりませんが、整数値である必要はありません。RowID は、テーブル内の行を識別するために内部的に使用される整数値です。通常、主キーはアプリケーションによって生成された値であり、RowID は InterSystems IRIS によって生成された一意の整数値です。
RowID フィールドを使用してデータの行にアクセスするために、マスタ・マップが自動的に作成されます。主キー・フィールドを定義すると、主キー・インデックスが自動的に作成されて維持されます。
行を識別するために 2 つの異なるフィールドとインデックスを持つという二重性は、必ずしも良いことではありません。以下の 2 つの方法のいずれかで、単一の行識別子とインデックスに解決することができます。
-
アプリケーションで生成された主キー値を IDKEY として使用します。PrimaryKey と IdKey の両方のキーワードを含むクラス定義で主キー・インデックスを識別することで、これを行うことができます (このために PKEY_IS_IDKEY フラグを設定した場合は、DDL からもこの操作を行うことができます)。これにより、主キー・インデックスがテーブルのマスタ・マップになります。この結果、主キーが行のメインの内部アドレスとして使用されます。主キーが複数のフィールドで構成される場合、または主キーの値が整数でない場合、これは非効率です。
-
アプリケーションで生成された主キー値は使用しないでください。代わりにシステムで生成された RowID の整数をアプリケーションが使用する主キーとしてアプリケーション内で使用してください (例えば、結合で)。この操作の利点は、整数の RowID が、ビットマップ・インデックスの使用を含め、より効率的な処理に適していることです。
アプリケーションの性質に応じて、単一の行識別子とインデックスに解決したり、アプリケーションで生成された主キーとシステムで生成された RowID に別個のインデックスを設定することができます。
RowVersion、AutoIncrement、および Serial カウンタ・フィールド
InterSystems SQL は、カウンタ値を自動的にインクリメントするための 3 つの専用データ型をサポートしています。3 つのデータ型はすべて、%Library.BigIntOpens in a new tab データ型クラスを拡張するサブクラスです。
-
%Library.RowVersionOpens in a new tab : ネームスペース全体のすべての RowVersion テーブルに対する挿入と更新をカウントします。ROWVERSION フィールドを含むテーブルの挿入と更新のみが、このカウンタをインクリメントします。ROWVERSION の値は一意で、変更できません。このネームスペース全体のカウンタがリセットされることはありません。詳細は、"ROWVERSION フィールド" を参照してください。
-
%Library.CounterOpens in a new tab (SERIAL カウンタ・フィールドとも呼ばれます) : テーブルへの挿入をカウントします。既定では、このフィールドは自動的にインクリメントされた整数を受け取ります。ただし、ユーザはこのフィールドにゼロ以外の整数値を指定できます。ユーザは重複値を指定できます。ユーザが指定した値が、システムで指定された最も大きい値よりも大きい場合、自動インクリメント・カウンタが設定され、ユーザが指定した値からインクリメントされます。詳細は、"Serial カウンタ・フィールド" を参照してください。
-
%Library.AutoIncrementOpens in a new tab : テーブルへの挿入をカウントします。既定では、このフィールドは自動的にインクリメントされた整数を受け取ります。ただし、ユーザはこのフィールドにゼロ以外の整数値を指定できます。 ユーザは重複値を指定できます。ユーザ値を指定しても、自動インクリメント・カウンタには影響を与えません。詳細は、"AutoIncrement フィールド" を参照してください。
これら 3 つのフィールドすべて、および IDENTITY フィールドは、以下の例に示すように、AUTO_INCREMENT = YES を返します。
SELECT COLUMN_NAME,AUTO_INCREMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTable'
RowVersion フィールド
RowVersion フィールドは、行レベルのバージョン管理を提供する、オプションのユーザ定義フィールドです。これにより、ネームスペース全体で各行のデータが変更された順序を把握できます。InterSystems IRIS はネームスペース全体を範囲とするカウンタを維持しており、行データが変更 (挿入、更新、または %Save) されるたびに、そのフィールドに一意の正の増分整数を割り当てます。このカウンタはネームスペース全体を範囲とするため、ROWVERSION フィールドを含む 1 つのテーブルでの操作によって、同じネームスペース内の ROWVERSION フィールドを含む他のすべてのテーブルで使用される ROWVERSION カウンタの増分ポイントが設定されます。
RowVersion フィールドは、フィールド・データ型 ROWVERSION を指定して作成します (%Library.RowVersionOpens in a new tab)。テーブルごとに指定できる ROWVERSION データ型フィールドは 1 つのみです。複数の ROWVERSION フィールドを含むテーブルを作成しようとすると、5320 コンパイル・エラーが発生します。
このフィールドには任意の名前を付けることができ、どの列位置にも表示できます。ROWVERSION (%Library.RowVersionOpens in a new tab) データ型は BIGINT (%Library.BigIntOpens in a new tab) にマッピングされます。
このフィールドは、自動インクリメント・カウンタから 1 で始まる正の整数を受け取ります。このカウンタは、ROWVERSION 対応テーブル内のデータが挿入、更新、または %Save 操作によって変更されると必ずインクリメントされます。インクリメントされた値は、挿入または更新された行の ROWVERSION フィールドに記録されます。
ネームスペースには、RowVersion フィールドがあるテーブルと、そのフィールドがないテーブルを含めることができます。RowVersion フィールドがあるテーブルのデータが変更された場合にのみ、ネームスペース全体のカウンタは増分されます。
テーブルにデータが入力されるときに、InterSystems IRIS は挿入された各行に対して、このフィールドに連続した整数を割り当てます。既にデータが取り込まれているテーブルに ROWVERSION フィールドを追加するために ALTER TABLE を使用する場合、このフィールドは既存のフィールドについては NULL として作成されます。その後テーブルで挿入や更新が実行されると、その行の RowVersion フィールドに連続的な整数が割り当てられます。このフィールドは読み取り専用です。RowVersion 値を変更しようとすると、SQLCODE -138 エラー : "読み取り専用フィールドを INSERT/UPDATE することはできません" が生成されます。したがって、RowVersion フィールドは固有であり変更不可であると定義されますが、必須であるまたは NULL でないとは定義されません。
RowVersion の値は必ず増分になります。再使用されることはありません。したがって、挿入と更新により割り当てられる固有の RowVersion 値は、一時的な順序です。削除操作では、このシーケンスから数字が削除されます。そのため、RowVersion 値は数字的に連続していない場合があります。
このカウンタはリセットされません。すべてのテーブル・データを削除しても、RowVersion カウンタはリセットされません。ROWVERSION フィールドを含むネームスペース内のすべてのテーブルを削除しても、このカウンタはリセットされません。
一意キーや主キーに RowVersion フィールドを含めることはできません。RowVersion フィールドは、IDKey インデックスの一部にすることはできません。
シャード・テーブルに RowVersion フィールドを含めることはできません。
RowVersion フィールドは非表示ではありません (これは SELECT * により表示されます)。
これについては以下の、同じネームスペースにある 3 つのテーブルの例で示しています。
-
それぞれ ROWVERSION フィールドがある Table1 と Table3 を作成し、ROWVERSION フィールドがない Table2 を作成します。
-
Table1 に 10 行を挿入します。そうするとこれらの行の ROWVERSION 値により、カウンタは 10 だけ増分されます。カウンタはそれより前には使用されていなかったので、1 から始まり 10 になります。
-
Table2 に 10 行を挿入します。Table2 には ROWVERSION フィールドがないので、カウンタは増分されません。
-
Table1 の 1 行を更新します。そうするとこの行の ROWVERSION 値が反映されて、カウンタは増分されます (この場合は 11 になります)。
-
Table3 に 10 行を挿入します。そうするとこれらの行の ROWVERSION 値により、カウンタは 10 だけ増分されます (12 から始まり 21 になります)。
-
Table1 の 1 行を更新します。そうするとこの行の ROWVERSION 値が反映されて、カウンタは増分されます (この場合は 22 になります)。
-
Table1 の 1 行を削除します。この場合に ROWVERSION カウンタは変更されません。
-
Table3 の 1 行を更新します。そうするとこの行の ROWVERSION 値が反映されて、カウンタは増分されます (この場合は 23 になります)。
Serial カウンタ・フィールド
SERIAL データ型 (永続クラス・テーブル定義の %Library.CounterOpens in a new tab) を使用して、1 つ以上のオプションの整数カウンタ・フィールドを指定して、テーブルへのレコードの挿入順序を記録することができます。各 Serial カウンタ・フィールドは、独自のカウンタを保持しています。
テーブルに行が挿入されると、Serial カウンタ・フィールドは自動インクリメント・カウンタから正の整数を受け取り、このフィールドに値なし (NULL) または値 0 が指定されます。ただし、ユーザは挿入中にこのフィールドにゼロ以外の整数値を指定して、テーブル・カウンタの既定をオーバーライドできます。
-
INSERT がカウンタ・フィールドにゼロ以外の整数値を指定しない場合、カウンタ・フィールドは自動的に正の整数カウンタ値を受け取ります。カウントは 1 から始まります。連続する各値は、このフィールドの最大割り当てカウンタ値から 1 ずつ増加します。
-
INSERT がカウンタ・フィールドにゼロ以外の整数値を指定する場合、フィールドはその値を受け取ります。これは、正または負の整数値で、現在のカウンタ値よりも小さくても大きくてもかまいません。また、このフィールドに既に割り当てられている整数にすることもできます。この値が、割り当てられたカウンタ値よりも大きい場合は、自動インクリメント・カウンタのインクリメント開始点をその値に設定します。
カウンタ・フィールドの値を UPDATE しようとすると、SQLCODE -105 エラーが返されます。
このカウンタは、TRUNCATE TABLE コマンドによって 1 にリセットされます。このカウンタは、DELETE コマンドがテーブル内のすべての行を削除した場合でも、DELETE コマンドによってはリセットされません。
シャード・テーブルに Serial カウンタ・フィールドを含めることはできません。
AutoIncrement フィールド
%Library.AutoIncrementOpens in a new tab データ型 (または BIGINT AUTO_INCREMENT) を使用して整数カウンタ・フィールドを指定し、テーブルへのレコードの挿入順序を記録することができます。テーブルごとに指定できる %AutoIncrement データ型フィールドは 1 つのみです。テーブルに行が挿入されると、このフィールドは自動インクリメント・カウンタから正の整数を受け取り、このフィールドに値なし (NULL) または値 0 が指定されます。ただし、ユーザは挿入中にこのフィールドにゼロ以外の整数値を指定して、テーブル・カウンタの既定をオーバーライドできます。
-
INSERT がカウンタ・フィールドにゼロ以外の整数値を指定しない場合、カウンタ・フィールドは自動的に正の整数カウンタ値を受け取ります。カウントは 1 から始まります。連続する各値は、このフィールドの最大割り当てカウンタ値から 1 ずつ増加します。
-
INSERT がカウンタ・フィールドにゼロ以外の整数値を指定する場合、フィールドはその値を受け取ります。これは、正または負の整数値で、現在のカウンタ値よりも小さくても大きくてもかまいません。また、このフィールドに既に割り当てられている整数にすることもできます。ユーザが割り当てた値は、自動インクリメント・カウンタに影響を与えません。
カウンタ・フィールドの値を UPDATE しようとすると、SQLCODE -105 エラーが返されます。
このカウンタは、TRUNCATE TABLE コマンドによって 1 にリセットされます。このカウンタは、DELETE コマンドがテーブル内のすべての行を削除した場合でも、DELETE コマンドによってはリセットされません。
シャード・テーブルには AutoIncrement フィールドを含めることができます。
DDL を使用したテーブルの定義
InterSystems SQL で標準の DDL コマンドを使用してテーブルを定義できます。
ALTER コマンド | CREATE コマンド | DROP コマンド |
---|---|---|
|
DDL コマンドは、以下のようなさまざまな方法で実行できます。
-
ODBC 呼び出しを使用
-
JDBC 呼び出しを使用
埋め込み SQL での DDL の使用
ObjectScript のメソッドまたはルーチンで埋め込み SQL を使用して、DDL コマンドを呼び出すことができます。
例えば、以下のメソッドは Sample.Employee テーブルを生成します。
ClassMethod CreateTable() As %String
{
&sql(CREATE TABLE Sample.Employee (
EMPNUM INT NOT NULL,
NAMELAST CHAR (30) NOT NULL,
NAMEFIRST CHAR (30) NOT NULL,
STARTDATE TIMESTAMP,
SALARY MONEY,
ACCRUEDVACATION INT,
ACCRUEDSICKLEAVE INT,
CONSTRAINT EMPLOYEEPK PRIMARY KEY (EMPNUM)))
IF SQLCODE=0 {WRITE "Table created" RETURN "Success"}
ELSEIF SQLCODE=-201 {WRITE "Table already exists" RETURN SQLCODE}
ELSE {WRITE "Serious SQL Error, returning SQLCODE" RETURN SQLCODE_" "_%msg}
}
このメソッドが呼び出されると、Sample.Employee テーブルの生成を試みます (同様に、対応する Sample.Employee クラスの生成も試みます)。これが成功すると、SQLCODE 変数は 0 に設定されます。失敗した場合は、SQLCODE に失敗の原因を示す SQL エラー・コードが含まれます。
このような DDL コマンドでエラーが発生する主な原因は、以下のとおりです。
-
SQLCODE -99 (権限違反) : このエラーは、ユーザがその DDL コマンドを実行する特権を持っていないことを示します。通常、これはアプリケーションが現在のユーザが誰であるかを確立していないことに起因します。これは、$SYSTEM.Security.Login()Opens in a new tab メソッドを使用して、プログラムで実行できます。
DO $SYSTEM.Security.Login(username,password)
-
SQLCODE -201 (テーブルまたはビュー名がユニークではありません) : 新規のテーブルを生成する際に、既に存在するテーブル名を使用した場合に、このエラーが表示されます。
クラス・メソッドを使用した DDL の実行
ObjectScript では、ダイナミック SQL %SQL.StatementOpens in a new tab オブジェクトを使用して、ダイナミック SQL を使用した DDL コマンドの作成と実行が可能です。
以下に示す例では、ダイナミック SQL を使用してテーブルを作成するクラス・メソッドを定義しています。
Class Sample.NewT
{
ClassMethod DefTable(user As %String,pwd As %String) As %Status [Language=objectscript]
{
DO ##class(%SYSTEM.Security).Login(user,pwd)
SET myddl=2
SET myddl(1)="CREATE TABLE Sample.MyTest "
SET myddl(2)="(NAME VARCHAR(30) NOT NULL,SSN VARCHAR(15) NOT NULL)"
SET tStatement=##class(%SQL.Statement).%New()
SET tStatus=tStatement.%Prepare(.myddl)
IF tStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset=tStatement.%Execute()
IF rset.%SQLCODE=0 {WRITE "Created a table"}
ELSEIF rset.%SQLCODE=-201 {WRITE "table already exists"}
ELSE {WRITE "Unexpected error SQLCODE=",rset.%SQLCODE}
}
}
このメソッドは、以下のように呼び出します。
DO ##class(Sample.NewT).DefTable("myname","mypassword")
埋め込み SQL の例と同様に、現在ログインしているユーザがいない場合、このメソッドは失敗します。
DDL スクリプトのインポートおよび実行によるテーブルの定義
InterSystems SQL DDL スクリプト・ファイルをインポートするには、ターミナル・セッションからインタラクティブに $SYSTEM.SQL.Schema.Run()Opens in a new tab メソッドを使用するか、バックグラウンド・ジョブとして $SYSTEM.SQL.Schema.ImportDDL("IRIS")Opens in a new tab メソッドを使用します。このメソッドでは複数の SQL コマンドをインポートおよび実行でき、ユーザは txt スクリプト・ファイルを使用してテーブルおよびビューを定義し、これらにデータを入力することができます。詳細は、"SQL コードのインポート" を参照してください。
他のベンダのリレーショナル・データベースから InterSystems IRIS にテーブルを移行する際、テキスト・ファイル内に 1 つ以上の DDL スクリプトが存在していることがあります。InterSystems IRIS には、そのようなテーブルを InterSystems IRIS にロードする際に役立ついくつかの %SYSTEM.SQL.SchemaOpens in a new tab メソッドが用意されています。汎用の ImportDDL()Opens in a new tab メソッドまたは特定ベンダ用の %SYSTEM.SQL.SchemaOpens in a new tab ロード・メソッドを使用できます。ベンダ固有の SQL は、InterSystems SQL に変換されて実行されます。エラーおよびサポートされない機能は、ログ・ファイルに記録されます。詳細は、"コード移行 : 非 InterSystems SQL のインポート" を参照してください。
例えば、ObjectScript コマンド行から Oracle DDL ファイルをロードするには、以下の手順を実行します。
-
InterSystems IRIS ランチャー・メニューの [ターミナル] コマンドを使用して、ターミナル・セッションを開始します。
-
テーブル定義をロードしたいネームスペースに切り替えます。
SET $namespace = "MYNAMESPACE"
-
目的の DDL インポート・メソッドを呼び出します。
DO $SYSTEM.SQL.Schema.LoadOracle()
ターミナルに表示された指示に従います。
永続クラスの作成によるテーブルの定義
SQL テーブルを定義する標準的な方法は、SQL プロンプト上あるいは JDBC 接続経由または ODBC 接続経由で CREATE TABLE DDL コマンドを実行することですが、VS Code や Studio などの IDE を利用して永続クラス・テーブルの定義を作成することもできます。クラスは、%Persistent として定義するか、%Persistent として定義されているスーパークラスから継承する必要があります。これらのクラスが InterSystems IRIS データベースで保存およびコンパイルされたとき、クラス定義に対応するリレーショナル・テーブルが自動的に投影されます (各クラスはテーブルを表し、各プロパティは列を表します)。1 つのクラス (テーブル) に定義可能なプロパティ (列) の最大数は 1000 です。
例えば、以下では、永続クラス MyApp.Person が定義されます。
Class MyApp.Person Extends %Persistent
{
Parameter USEEXTENTSET = 1;
Property Name As %String(MAXLEN=50) [Required];
Property SSN As %String(MAXLEN=15) [InitialExpression = "Unknown"];
Property DateOfBirth As %Date;
Property Sex As %String(MAXLEN=1);
Index BitmapExtent [ Extent, Type = bitmap ];
}
このクラスの特徴を以下に示します。
-
コンパイルした定義によって、MyApp.Person 永続クラスおよび対応する SQL テーブル Person が MyApp スキーマに作成されます。これらの操作の実行方法の詳細は、"クラスの定義" を参照してください。
-
このクラス定義には、パッケージ名 MyApp があります。永続クラスを定義するとき、未指定のパッケージ名は既定で "User" になります。この名前は、既定の SQL スキーマ名である SQLUser に相当します。例えば、テーブル Students を永続クラスとして定義すると、User.Students クラスとそれに対応する SQL schema.table 名 SQLUser.Students が作成されます。
-
永続クラス名 Person が SQL テーブル名として使用されます。別の SQL テーブル名を指定するには SqlTableName クラス・キーワードを使用します。
-
USEEXTENTSET クラス・パラメータが定義され、1 に設定されます。このパラメータによって、テーブル・ストレージをまとめた、より効率的なグローバルのセットが作成されます。SQL テーブルに投影されるすべての永続クラスに USEEXTENTSET パラメータを指定することをお勧めします。
-
ビットマップ・エクステント・インデックスは、エクステント・セット内のすべての ID のインデックスを作成します。この設定により、カウントやその他の操作がより効率的になります。SQL テーブルに投影されるすべての永続クラスにビットマップ・エクステント・インデックスを作成することをお勧めします。
同じ MyApp.Person テーブルは、DDL 文 CREATE TABLE を使用して、SQL schema.table 名を指定することで定義できます。この SQL 文が正常に実行されると、パッケージ名が MyApp でクラス名が Person の対応する永続クラスが生成されます。
CREATE TABLE MyApp.Person (
Name VARCHAR(50) NOT NULL,
SSN VARCHAR(15) DEFAULT 'Unknown',
DateOfBirth DATE,
Sex VARCHAR(1)
)
DDL コマンドを使用してテーブルを定義するときは、USEEXTENTSET の指定も、ビットマップ・エクステント・インデックスの作成も不要です。InterSystems SQL によって、これらの設定が自動的に適用され、投影された永続クラスに反映されます。
既定では、CREATE TABLE は対応するクラス定義で Final クラス・キーワードを指定します。これは、サブクラスを持てないことを示しています。
データベースのオブジェクト・ビューとリレーショナル・ビューの対応関係は、"既定の SQL プロジェクションの概要" を参照してください。
上記のような永続クラス定義をコンパイルすると、対応するテーブルが作成されますが、このテーブル定義は、SQL DDL コマンド (または管理ポータルの Drop アクション) を使用しても変更や削除ができません。これらのコマンドを使用してこのような操作を実行しようとすると、メッセージ [DDL がクラス schema.name に対して有効になっていません] が表示されます。これらの操作を許可するには、そのテーブル・クラス定義で [DdlAllowed] を指定する必要があります。
Class MyApp.Person Extends %Persistent [DdlAllowed]
プロパティ・パラメータの定義
永続クラスにテーブルを定義すると、そのテーブルの列に、定義したプロパティが投影されます。すべてのプロパティ定義では、そのプロパティの基となっているクラスを指定するデータ型クラスを指定する必要があります。指定したデータ型により、プロパティで使用できる値がそのデータ型に制限されます。テーブルに投影される永続クラスを定義する際、%Library パッケージのクラスを使用して、このデータ型を指定する必要があります。このクラスは、%Library.Datatype として、または %Datatype として指定できます。
多くのデータ型クラスには、使用できるプロパティ値を詳細に定義するためのパラメータが用意されています。これらのパラメータは、個々のデータ型に固有です。より一般的なデータ定義パラメータの一部を以下に示します。
データがフィールドに挿入、またはフィールドで更新されると、InterSystems SQL は自動的にデータを検証して、データ型および参照整合性制約を適用します。他の方法でテーブルにデータを入力する場合は、テーブル・データを検証する必要があります。
プロパティ値の制限
数値データ型では、MAXVAL および MINVAL パラメータを指定して、使用できる値の範囲を制限できます。定義上、数値データ型にはサポートされる最大値 (正または負) があります。MAXVAL および MINVAL を使用して、使用できる範囲をさらに制限できます。
文字列データ型では、MAXLEN および MINLEN パラメータを指定して、使用できる長さ (文字数) を制限できます。定義上、文字列データ型にはサポートされる最大長があります。MAXLEN および MINLEN を使用して、使用できる範囲をさらに制限できます。MAXLEN パラメータと MINLEN パラメータによって適用される長さ制限は、データベースにデータが格納されている場合にのみ適用されます。クエリでは、データベースにあるすべてのデータが有効であると想定しているからです。具体的には、INSERT を使用してデータベースにデータを追加するとき、または UPDATE を使用してデータベースのデータを編集するとき (または、ObjectScript で %Save()Opens in a new tab を実行するとき) に、この制限が適用されます。既定では、MAXLEN を超える長さのプロパティ値があると、INSERT の場合は SQLCODE -104、UPDATE の場合は SQLCODE -105 の各検証エラーが発生します。TRUNCATE=1 を指定すると、MAXLEN を超える文字列の値を使用できますが、指定した文字列は MAXLEN の長さに切り詰められます。既定の最大文字列長は 4096 です。この制限値は、CPF の ODBCVarcharMaxlen フィールドで構成できます。
許可されるプロパティ値
以下の 2 つの方法で、実際のプロパティ値を制限できます。
-
使用できる値のリスト (VALUELIST および DISPLAYLIST で列挙された値)。
-
使用できる値の一致パターン (PATTERN)。
列挙されるプロパティ値
テーブルを永続クラスとして定義することで、指定した特定の値のみを設定できるプロパティ (列) を定義できます。このためには、VALUELIST パラメータを指定します。VALUELIST (論理ストレージ値のリストを指定) は通常、DISPLAYLIST (対応する表示値のリストを指定) と共に使用されます。どちらのリストも、リスト区切り文字で始まります。複数のデータ型が VALUELIST および DISPLAYLIST を指定できます。次の例では、列挙された値を持つ 2 つのプロパティを定義します。
Class Sample.Students Extends %Persistent
{
Parameter USEEXTENTSET = 1;
Property Name As %String(MAXLEN=50) [Required];
Property DateOfBirth As %Date;
Property ChoiceStr As %String(VALUELIST=",0,1,2",DISPLAYLIST=",NO,YES,MAYBE");
Property ChoiceODBCStr As %EnumString(VALUELIST=",0,1,2",DISPLAYLIST=",NO,YES,MAYBE");
Index BitmapExtent [ Extent, Type = bitmap ];
}
VALUELIST が指定されている場合、INSERT または UPDATE は VALUELIST にリストされているいずれかの値のみを指定でき、それ以外の場合は値なし (NULL) が指定されます。VALUELIST の有効な値では、大文字と小文字が区別されます。必須のプロパティでは、VALUELIST の値に一致しない値を指定すると、INSERT の場合は SQLCODE -104、UPDATE の場合は SQLCODE -105 の各検証エラーが返されます。必須ではないプロパティでは、VALUELIST の値に一致しない値は NULL 値に変換されます。
ODBC モードで表示した場合、%String および %EnumString データ型は動作が異なります。上の例では、論理モードで表示した場合、ChoiceStr と ChoiceODBCStr は共に VALUELIST 値を表示します。表示モードで表示した場合、ChoiceStr と ChoiceODBCStr は共に DISPLAYLISTT 値を表示します。ODBC モードで表示した場合、ChoiceStr は VALUELIST 値を表示し、ChoiceODBCStr は DISPLAYLIST 値を表示します。
プロパティ値のパターン・マッチング
複数のデータ型が PATTERN パラメータを指定できます。PATTERN は、使用できる値を、指定された ObjectScript パターンと一致する値に制限します。このパターンは、先頭の疑問符を省略した、引用符付きの文字列で指定します。次の例では、パターンを持つプロパティを定義します。
Class Sample.Students Extends %Persistent
{
Parameter USEEXTENTSET = 1;
Property Name As %String(MAXLEN=50) [Required];
Property DateOfBirth As %Date;
Property Telephone As %String(PATTERN = "3N1""-""3N1""-""4N");
Index BitmapExtent [ Extent, Type = bitmap ];
}
パターンは引用符付きの文字列として指定されるため、パターンで指定されるリテラルは二重引用符で囲む必要があります。パターン・マッチングは、MAXLEN および TRUNCATE の前に適用されます。このため、MAXLEN を超え、切り捨てられる可能性のある文字列のパターンを指定する場合、パターンを “.E” で終了できます (任意のタイプの、無制限の数の末尾の文字)。
PATTERN と一致しない値を指定すると、INSERT の場合は SQLCODE -104、UPDATE の場合は SQLCODE -105 の各検証エラーが発生します。
一意のプロパティ値
CREATE TABLE を使用すると、列を UNIQUE として定義できます。これは、すべてのフィールドの値が一意の (重複がない) 値になることを意味します。
テーブルを永続クラスとして定義しても、対応する一意性プロパティ・キーワードはサポートされません。代りに、プロパティとそのプロパティに対する一意のインデックスを定義する必要があります。以下の例では、各レコードに一意の Num 値を指定します。
Class Sample.CaveDwellers Extends %Persistent [ DdlAllowed ]
{
Parameter USEEXTENTSET = 1;
Property Num As %Integer;
Property Troglodyte As %String(MAXLEN=50);
Index UniqueNumIdx On Num [ Type=index,Unique ];
Index BitmapExtent [ Extent, Type = bitmap ];
}
インデックス名は、プロパティの名前付け規約に従います。オプションの Type キーワードは、インデックス・タイプを指定します。Unique キーワードは、プロパティ (列) を一意として定義します。
一意の値の列は、INSERT OR UPDATE 文を使用する際に必要になります。
計算プロパティ値
以下のクラス定義の例は、列 (Birthday) を持つテーブルを定義します。このテーブルでは、DateOfBirth の値が初めて設定されたときに、SqlComputed を使用して列の値が計算され、DateOfBirth の値が更新されたときに、SqlComputeOnChange を使用して列の値が再計算されます。Birthday には、この値の計算日時または再計算日時を記録した最新のタイムスタンプが記述されます。
Class Sample.MyStudents Extends %Persistent [DdlAllowed]
{
Parameter USEEXTENTSET = 1;
Property Name As %String(MAXLEN=50) [Required];
Property DateOfBirth As %Date;
Property Birthday As %String
[ SqlComputeCode = {SET {Birthday}=$PIECE($ZDATE({DateOfBirth},9),",")_
" changed: "_$ZTIMESTAMP},
SqlComputed, SqlComputeOnChange = DateOfBirth ];
Index BitmapExtent [ Extent, Type = bitmap ];
}
既存の DateOfBirth 値を指定する DateOfBirth に対して UPDATE を実行しても Birthday の値は再計算されません。
SqlComputeCode プロパティのキーワードには、値の計算に使用される ObjectScript コードが記述されています。PropertyComputation メソッドに計算コードを指定することもできます。この場合、計算するプロパティの名前は Property です。このメソッドでは、ObjectScript 以外の言語 (Python など) で計算コードを指定できます。
このクラスでは、AgeComputation メソッドによって DOB (生年月日) プロパティに基づいて Age プロパティが計算されます。cols 入力引数は、%Library.PropertyHelperOpens in a new tab オブジェクトです。このオブジェクトの getfield メソッドを使用して、他のプロパティを参照できます。
Class Sample.MyStudents Extends %Persistent [ DdlAllowed ]
{
Parameter USEEXTENTSET = 1;
Property Name As %String(MAXLEN = 50) [ Required ];
Property DOB As %Date;
Property Age As %Integer [ Calculated, SqlComputed, SqlComputeOnChange = DOB ];
Index BitmapExtent [ Extent, Type = bitmap ];
ClassMethod AgeComputation(cols As %Library.PropertyHelper) As %Integer
{
set today = $zdate($horolog,8)
set bdate = $zdate(cols.getfield("DOB"), 8)
return $select(bdate = "":"", 1:(today - bdate) \ 10000)
}
}
Class Sample.MyStudents Extends %Persistent [ DdlAllowed ]
{
Parameter USEEXTENTSET = 1;
Property Name As %String(MAXLEN = 50) [ Required ];
Property DOB As %Date;
Property Age As %Integer [ Calculated, SqlComputed, SqlComputeOnChange = DOB ];
Index BitmapExtent [ Extent, Type = bitmap ];
ClassMethod AgeComputation(cols As %Library.PropertyHelper) As %Integer [ Language = python ]
{
import datetime as d
iris_date_offset = d.date(1840,12,31).toordinal()
bdate = d.date.fromordinal(cols.getfield("DOB") + iris_date_offset).strftime("%Y%m%d")
today = d.date.today().strftime("%Y%m%d")
return str((int(today) - int(bdate)) // 10000) if bdate else ""
}
}
SqlComputed および SqlComputeCode に加えて ComputeLocalOnly も指定すると、計算コードは、データがクエリの発行元と同じサーバに格納される場合にのみ実行されます。このオプションは主にシャード環境で、ローカルに格納される計算済みデータのみを返すために使用されます。
計算コードの指定の詳細は、"計算列" を参照してください。
クラス・プロパティのキーワードの詳細は、"プロパティの構文とキーワード" を参照してください。
埋め込みオブジェクト (%SerialObject)
プロパティを定義する埋め込みシリアル・オブジェクト・クラスを参照することで、永続テーブルの構造を簡素化できます。例えば、通り、市、都道府県、および郵便番号で構成された住所情報を MyData.Person テーブルに含める場合があります。これらのプロパティを MyData.Person に指定するのではなく、これらのプロパティを定義するシリアル・オブジェクト (%SerialObject) クラスを定義してから、この埋め込みオブジェクトを参照する 1 つの Home プロパティを MyData.Person で指定できます。これを以下のクラス定義で示しています。
Class MyData.Person Extends (%Persistent) [ DdlAllowed ]
{ Parameter USEEXTENTSET = 1;
Property Name As %String(MAXLEN=50);
Property Home As MyData.Address;
Property Age As %Integer;
Index BitmapExtent [ Extent, Type = bitmap ];
}
Class MyData.Address Extends (%SerialObject)
{ Property Street As %String;
Property City As %String;
Property State As %String;
Property PostalCode As %String;
}
シリアル・オブジェクト・プロパティ内のデータに直接アクセスすることはできません。シリアル・オブジェクト・プロパティを参照する永続クラス/テーブルを介してアクセスする必要があります。
-
永続テーブルから個々のシリアル・オブジェクト・プロパティを参照するには、アンダースコアを使用します。例えば、SELECT Name, Home_State FROM MyData.Person は州 (State) のシリアル・オブジェクト・プロパティ値を文字列として返します。シリアル・オブジェクト・プロパティ値は、クエリで指定された順序で返されます。
-
永続テーブルからすべてのシリアル・オブジェクト・プロパティを参照するには、参照フィールドを指定します。例えば、SELECT Home FROM MyData.Person はすべての MyData.Address プロパティの値を %List 構造として返します。シリアル・オブジェクト・プロパティ値は、シリアル・オブジェクトで指定されている Home_Street、Home_City、Home_State、Home_PostalCode の順序で返されます。管理ポータルの SQL インタフェース [カタログの詳細] では、この参照フィールドは [コンテナ] フィールドと呼ばれます。これは [隠し] フィールドであるため、SELECT * 構文では返されません。
-
永続クラスの SELECT * は、入れ子になっているシリアル・オブジェクトを含め、すべてのシリアル・オブジェクト・プロパティを個別に返します。例えば、SELECT * FROM MyData.Person は Age、Name、Home_City、Home_PostalCode、Home_State、および Home_Street の値を (この順序で) 返します。Home の %List 構造の値は返しません。シリアル・オブジェクト・プロパティ値は照合順で返されます。SELECT * はまず、永続クラスのすべてのフィールドを照合順でリストし (通常、アルファベット順)、その後に、入れ子になっているシリアル・オブジェクト・プロパティを照合順でリストします。
埋め込みシリアル・オブジェクトは、参照する永続テーブルと同じパッケージ内に存在する必要はありません。%Library.SerialObject の SqlCategory (および SqlCategory を明示的に定義していない %SerialObject のすべてのサブクラス) は STRING です。
埋め込みオブジェクトを定義することで、永続テーブルの定義を次のように簡素化できます。
-
永続テーブルに、同じ埋め込みオブジェクト内の異なる複数のレコードを参照する複数のプロパティを含めることができます。例えば、MyData.Person テーブルに、両方とも MyData.Address シリアル・オブジェクト・クラスを参照する、Home プロパティと Office プロパティを含めることができます。
-
複数の永続テーブルが同じ埋め込みオブジェクトのインスタンスを参照できます。例えば、MyData.Person テーブルの Home プロパティと MyData.Employee テーブルの WorkPlace プロパティが、両方とも MyData.Address シリアル・オブジェクト・クラスを参照できます。
-
埋め込みオブジェクトは、別の埋め込みオブジェクトを参照できます。例えば、MyData.Address 埋め込みオブジェクトに、CountryCode プロパティ、AreaCode プロパティ、および PhoneNum プロパティが含まれる MyData.Telephone 埋め込みオブジェクトを参照する Phone プロパティを含めることができます。永続クラスから複数のアンダースコアを使用して、入れ子になったシリアル・オブジェクト・プロパティを参照します (例えば、Home_Phone_AreaCode)。
シリアル・オブジェクト・クラスをコンパイルすると、ストレージ定義にデータ仕様が生成されます。コンパイラはシリアル・オブジェクト・クラス名に "State" という語を付加することで、この仕様にデータ名を割り当てます。したがって、MyData.Address には <Data name="AddressState"> が割り当てられます。この名前 (この例では AddressState) が既にプロパティ名として使用されている場合、コンパイラは整数を付加して一意のデータ名を作成します (<Data name="AddressState1">)。
"シリアル・オブジェクトの概要" を参照してください。
シリアル・オブジェクト・プロパティのインデックス作成の詳細は、"埋め込みオブジェクト (%SerialObject) のプロパティのインデックス作成" を参照してください。
クラス・メソッド
テーブル定義の一部としてクラス・メソッドを指定できます。その例を以下に示します。
Class MyApp.Person Extends %Persistent
{
Parameter USEEXTENTSET = 1;
Property Name As %String(MAXLEN=50) [Required];
Property SSN As %String(MAXLEN=15) [InitialExpression = "Unknown"];
Property DateOfBirth As %Date;
Property Sex As %String(MAXLEN=1);
Index BitmapExtent [ Extent, Type = bitmap ];
ClassMethod Numbers() As %Integer [ SqlName = Numbers, SqlProc ]
{
QUIT 123
}
}
クラス・メソッドを SQL プロシージャに投影するには SqlProc キーワードが必要です。SELECT クエリでは、メソッドに定義した SqlName 値を使用してこのメソッドを呼び出すことができます。以下に例を示します。
SELECT Name,SSN,MyApp.Numbers() FROM MyApp.Person
永続クラスの作成によるシャード・テーブルの定義
シャード・テーブルとして投影する永続クラスを定義する前に、シャーディング環境を設定する必要があります。その後、シャード永続クラスを定義するには、クラス・キーワード Sharded=1 を、オプションのシャード関連クラス属性と共に指定します。
シャード・テーブルは、データのない新しい永続クラスに対してのみ定義します。既存のクラスにシャーディングを適用すると、データにアクセスできなくなる可能性があります。
このクラスは、サンプルのシャード永続クラスと、オプションでシャード関連のクラス属性セットを定義します。
Class Sample.MyShardT Extends %Persistent [ Sharded = 1 ]
{
Parameter DEFAULTCONCURRENCY As BOOLEAN = 0;
Parameter USEEXTENTSET = 1;
Index BitmapExtent [ Extent, Type = bitmap ];
}
次のクラス属性が適用されます。
-
Sharded = 1 キーワードは、このクラスが投影されるテーブルをシャード・テーブルとして定義します。この設定により、シャーディング・インフラストラクチャで、データ分散を含め、シャード・テーブル・ストレージを管理します。したがって、InterSystems IRIS で生成および維持される既定のストレージ定義をカスタマイズすることはできません。このクラス定義内のストレージ定義に対して行われた変更はすべて無視されます。
-
DEFAULTCONCONCURRENCY クラス・パラメータは 0 (ロックなし) に設定されます。シャード・テーブルには分散する性質があることから、0 のパラメータ値が必要です。この値は、%Open や %OpenId などのオブジェクト・メソッドの既定値として使用されます。したがって、すべてのメソッド呼び出しに並行処理引数を渡す必要はありません。
-
USEEXTENTSET クラス・パラメータは 1 に設定されます。これにより、テーブル・ストレージは、より効率的なグローバルのセットに編成されます。DDL コマンドを使用してシャード・テーブルを定義する場合、InterSystems SQL はこの設定を自動的に適用します。
-
ビットマップ・エクステント・インデックスは、エクステント・セット内のすべての ID のインデックスを作成します。この設定により、カウントやその他の操作がより効率的になります。DDL コマンドを使用してシャード・テーブルを定義する場合、InterSystems SQL はこの設定を自動的に適用します。
その後、シャード・キー・インデックスを定義できます。シャード・テーブルを作成すると、抽象シャード・キー・インデックスが自動的に生成されます。シャード・キー・インデックスは、行が存在するシャードを指定します。シャード・キー・インデックスの定義とシャード・テーブルの作成の詳細は、"シャード・テーブルの作成とデータのロード" を参照してください。
シャード・クラスの制限事項
-
シャード・クラスではサポートされないクラス・パラメータ : CONNECTION、DEFAULTGLOBAL、DSINTERVAL、DSTIME、IDENTIFIEDBY、OBJJOURNAL。
-
シャード・クラスではサポートされないクラス・キーワード : Language、ViewQuery。
-
シャード・クラスではサポートされないスーパー・クラス : %Library.IndexBuilder、%DocDB.Document。
-
シャード・クラスではサポートされないプロパティ・データ型 : %Library.Text。
-
リレーションシップ・プロパティは、シャード・クラスではサポートされていません。
-
プロジェクションは、シャード・クラスではサポートされていません。
-
"objectscript" 以外の言語のメソッドは、シャード・クラスではサポートされていません。
-
タイプが %SQLQuery でないクラス・クエリは、シャード・クラスではサポートされていません。
これらの機能を使用してシャード・クラスをコンパイルしようとすると、コンパイル時エラーが発生します。
シャード・テーブルの定義
シャード・テーブルを作成するには、3 つの要件があります。
-
ライセンス・キーでシャーディングがサポートされている必要があります。管理ポータルの [システム管理]→[ライセンス]→[ライセンスキー] で、現在のライセンスを表示するか、新しいライセンスをアクティブにします。
-
InterSystems IRIS インスタンスでシャーディングを有効にする必要があります。シャーディングを有効にするには、%Admin_Secure 権限が必要です。管理ポータルの [システム管理]→[構成]→[システム構成]→[シャーディング構成] で [シャーディングの有効化] ボタンを選択します。これにより、シャード・クラスタで現在の InterSystems IRIS インスタンスを使用できるようになります。[すべてのロールに対してこのインスタンスを有効にする] または [シャード・マスタ・ロールに対してのみこのインスタンスを有効にする] を選択します。[OK] を押します。InterSystems IRIS インスタンスを再開します。
-
InterSystems IRIS インスタンス上にシャード・クラスタを導入する必要があります。このシャード・クラスタには、シャード・マスタ・ネームスペースが含まれます。現在のネームスペースがシャーディング用に構成されていない場合、シャード・テーブルを定義しようとすると、"エラー #9319: 現在のネームスペース %1 にはシャードが構成されていません" で失敗します。詳細は、"API または管理ポータルを使用したクラスタの導入" を参照してください。
次に、シャード・クラスタの一部として定義されているシャード・マスタ・ネームスペース内で、シャード・テーブルを定義できます。CREATE TABLE を使用し、シャード・キーを指定することでシャード・テーブルを定義できます。または、シャード・テーブルに投影される永続クラスを作成できます。
シャード・テーブルの定義の詳細は、"ターゲット・シャード・テーブルの作成" を参照してください。
既存のテーブルのクエリによるテーブルの定義
既存のテーブル (テーブルまたはビュー、複数可) に基づいて新しいテーブルを定義して生成できます。これには、クエリと新しいテーブル名を指定します。既存のテーブル名と新しいテーブル名のいずれかまたは両方を修飾付きまたは未修飾にできます。クエリに JOIN 構文を含めることができます。クエリに、新しいテーブルの列名になる列名エイリアスを指定できます。
この操作は、前述したとおり、CREATE TABLE AS SELECT コマンド、または $SYSTEM.SQL.Schema.QueryToTable()Opens in a new tab メソッドを使用して実行できます。
-
QueryToTable() は、既存のテーブルの DDL 定義をコピーして、指定された新しいテーブル名にこれを割り当てます。これは、data type (データ型)、maxlength (最大長)、minval/maxval (最小/最大有効値) など、クエリに指定されたフィールドの定義をコピーします。既定値、必須の値、一意の値などフィールドのデータ制約はコピーしません。フィールドから別のテーブルへの参照はコピーしません。
クエリで SELECT * または SELECT %ID を指定すると、元のテーブルの RowID フィールドが、必須ではなく、一意ではない、データ型 integer のデータ・フィールドとしてコピーされます。QueryToTable() は、新しいテーブルの一意の RowID フィールドを生成します。コピーされた RowID を ID と名付けた場合、生成される RowID の名前は ID1 となります。
QueryToTable() は、この新しいテーブルの対応する永続クラスを作成します。永続クラスは DdlAllowed として定義されます。新しいテーブルの所有者は現在のユーザです。
ソース・テーブルでの設定に関係なく、新しいテーブルでは、Default Storage = YES (既定のストレージ・クラスを使用する) および Supports Bitmap Indices = YES (ビットマップ・インデックスをサポートする) と定義されます。
新しいテーブルに作成される唯一のインデックスが IDKEY インデックスです。ビットマップ・エクステント・インデックスは生成されません。コピーされたフィールドのインデックス定義は、新しいテーブルにコピーされません。
-
次に、QueryToTable() は、クエリによって選択されたフィールドのデータを新しいテーブルに生成します。テーブルのエクステント・サイズを 100,000 に設定します。また、IDKEY ブロック・カウントを見積もります。テーブルのチューニングを実行し、実際のエクステント・サイズとブロック・カウント、および各フィールドの選択性と平均フィールド・サイズの値を設定します。
QueryToTable() は、テーブル定義の作成と新しいテーブルへのデータの生成の両方を実行します。テーブル定義の作成だけを行いたい場合は、クエリの WHERE 節にデータ行なしで選択する条件を指定します。例えば、WHERE Age < 20 AND Age > 20 のように指定します。
以下の例では、Name フィールドと Age フィールドが Sample.Person からコピーされ、AVG(Age) フィールドが作成されます。これらのフィールド定義は、Sample.Youth という名前の新しいテーブルの作成に使用されます。このメソッドは次に、Age < 21 のレコードに関する Sample.Person のデータを Sample.Youth に生成します。AvgInit フィールドには、テーブルの作成時に選択されたレコードの集約値が含まれます。
DO $SYSTEM.SQL.Schema.QueryToTable("SELECT Name,Age,AVG(Age) AS AvgInit FROM Sample.Person WHERE Age < 21","Sample.Youth",1,.errors)
外部テーブル
外部テーブルは、InterSystems IRIS 2023.3 で試験的機能として用意されています。つまり、実稼働環境ではサポートされません。ただし、この機能は十分にテストされており、お客様に大きな価値をもたらすことができると考えています。
インターシステムズでは、この新機能を実際の環境で使用したお客様からのフィードバックをお待ちしています。ご自分の体験を他のユーザと共有する場合、または質問がある場合は、Developer Community にアクセスするか、インターシステムズのサポート窓口 (WRC) までお問い合わせください。
InterSystems SQL では、外部データ・ソースのデータを InterSystems IRIS のインスタンスに投影する外部テーブルを定義できます。このテーブルを使用すると、そのインスタンスに格納されているデータに対するクエリと同様に、このような外部データに対するクエリを実行できます。
外部テーブルの概要
さまざまな理由により、InterSystems IRIS に直接データをロードすることが不可能であるか、合理的ではない場合があります。例として、データ・ファイルがきわめて大きく、InterSystems IRIS テーブルにロードするストレージ・コストに見合うほど頻繁にはクエリが実行されない状況が挙げられます。外部テーブルは、別のシステムで管理されているデータを投影し、InterSystems IRIS のインスタンスで管理および格納しているデータと同様に、投影したデータにクエリおよびアクセスできるようにする機能です。
外部テーブルの作成
外部テーブルを作成する前に、InterSystems IRIS が外部データ・ソースとどのように対話するかを決定するために外部サーバを定義する必要があります。外部サーバを定義すると、外部ソースのデータを表す外部テーブルを 1 つ以上定義できます。そのためには、外部データ・ソースのフィールドを InterSystems IRIS の列にマッピングするうえで必要な、列の名前とタイプなどの詳細情報を指定します。
手順 1:外部サーバを定義する
外部テーブルを定義するには、外部サーバを定義し、使用する外部データ・ラッパを指定しておく必要があります。そのためには、CREATE FOREIGN SERVER コマンドを使用します。
CREATE FOREIGN SERVER コマンドでは、外部データ・ラッパを指定する必要があります。InterSystems IRIS で特定のタイプのデータ・ソースをどのように操作するかを、この外部データ・ラッパで指定します。CREATE FOREIGN SERVER コマンドでは、外部データ・ラッパと、その外部データ・ラッパが必要とするメタデータを指定する必要があります。現在のところ、InterSystems SQL では 2 つの外部データ・ラッパとして CSV と JDBC をサポートしています。CSV 外部データ・ラッパでは、ローカル・ファイル・システムのフォルダへのパスを指定する必要があります。JDBC 外部データ・ラッパでは、外部データベースに接続するための JDBC 接続を指定する必要があります。
外部サーバに定義できる外部テーブルの数に制限はありません。
以下の例は、CSV 外部データ・ラッパを使用する外部サーバの作成方法を示しています。
CREATE FOREIGN SERVER Sample.TestFile FOREIGN DATA WRAPPER CSV HOST '\path\to\file'
以下の例は、JDBC 外部データ・ラッパを使用する外部サーバの作成方法を示しています。
CREATE FOREIGN SERVER Sample.PostgresDB FOREIGN DATA WRAPPER JDBC CONNECTION 'postgresConnection'
手順 2:外部テーブルを定義する
外部サーバを定義すると、CREATE FOREIGN TABLE コマンドを使用して外部テーブルを定義できます。このテーブルの列名は外部ソースのデータと同じ列名にすることができるほか、InterSystems IRIS では別の名前で外部ソースのデータ列を参照することもできます。外部テーブルを作成する構文は、LOAD DATA コマンドに似ています。
CREATE FOREIGN TABLE Sample.AccountTeam (
TeamID BIGINT,
Name VARCHAR(50),
CountryCode VARCHAR(10)
) SERVER Sample.PostgresDB TABLE 'Sample.Teams'
データ定義言語 (DDL) 文を使用して外部テーブルを作成すると、ClassType を "view" として、対応するクラスが作成されます。このクラスは手動で編集しないでください。また、外部テーブルは CREATE FOREIGN TABLE コマンドで定義する必要があります。クラス定義を作成することで外部テーブルを作成することはできません。
外部テーブルのクエリ
外部テーブルのクエリは、ネイティブ・テーブルのクエリとまったく同じです。
SELECT Name, CountryCode FROM Sample.AccountTeam ORDER BY Name
クエリではさらに高度な構文を活用することもできます。
SELECT t.Name, COUNT(m.*)
FROM Sample.AccountManager m JOIN Sample.AccountTeam t
ON m.TeamID = t.TeamID
WHERE t.CountryCode = 'UK' AND m.Salary > 100000
GROUP BY t.Name
InterSystems SQL では、WHERE 節の簡潔な述語が可能な範囲で送信またはプッシュ・ダウンされます。これにより、ネットワーク上で送受信されるデータの量を制限し、リモート・データベースでの最適化を最大限に活用します。ただし、2 つの外部テーブル間での GROUP BY や JOIN のような複雑な節は、外部データの取得を完了した後で InterSystems IRIS によって処理されます。
クエリを発行するユーザには %Gateway_Object:USE 特権が必要です。
外部テーブルに対するクエリの発行では、その基盤となる Java ベース・エンジンが使用されます。このエンジンでは、サーバ上に Java 仮想マシン (JVM) をインストールした環境が必要です。セットアップ済みの JVM があり、その JVM に PATH 環境変数でアクセスできれば、初めてクエリを発行したときに、InterSystems IRIS で自動的にその JVM を使用して外部言語サーバが起動します。特定の JVM が使用されるように外部言語サーバをカスタマイズする場合、またはリモート・サーバを使用する場合は、"外部サーバ接続の管理" を参照してください。
外部テーブルの削除
外部テーブルを削除するには DROP FOREIGN TABLE コマンドを使用します。
DROP FOREIGN TABLE Example.MyForeignTable
また、CASCADE オプションを指定して DROP FOREIGN SERVER コマンドを使用すると、外部サーバとそこに定義されているすべての外部テーブルを削除できます。
DROP FOREIGN SERVER Example.PostgresDB CASCADE
フェデレーション・テーブル
フェデレーション・テーブルは、InterSystems IRIS 2023.3 で試験的機能として用意されています。つまり、実稼働環境ではサポートされません。ただし、この機能は十分にテストされており、お客様に大きな価値をもたらすことができると考えています。
インターシステムズでは、この新機能を実際の環境で使用したお客様からのフィードバックをお待ちしています。ご自分の体験を他のユーザと共有する場合、または質問がある場合は、Developer Community にアクセスするか、インターシステムズのサポート窓口 (WRC) までお問い合わせください。
フェデレーション・テーブルは、同じまたは同様のスキーマをホストしている InterSystems IRIS® データ・プラットフォームの複数の異なるインスタンスに存在するデータへのクエリ・アクセスを提供します。シャード・テーブルとは異なり、フェデレーション・テーブルは読み取り専用であり、シャード・キーに基づいて複数のソースにわたってデータを管理したり、分散することはありません。フェデレーション・テーブルのソース・テーブルは、そのデータを物理的に格納し、個別のインスタンスまたはネームスペースに存在し、ホスト・インスタンスによって排他的に管理されます。このため、フェデレーション・テーブルは読み取り専用です。フェデレーション・テーブルは、マルチテナント環境など、同じアプリケーションやスキーマのさまざまな導入にわたって分析やクエリが必要なユース・ケースにソリューションを提供します。
フェデレーション・テーブル作成の要件
複数のソース・テーブルを 1 つのフェデレーション・テーブルとして接続するには、まず、該当するソース・データを含むインスタンスすべてにわたるシャード・クラスタを構成する必要があります。シャード・クラスタの構成の詳細は、"シャード・クラスタの導入" を参照してください。シャーディングが初めての場合は、"シャーディングによるデータ量に応じた水平方向の拡張" で、用語や概念を理解しておくと役立ちます。
1 つ以上のソース・テーブルを含むネームスペース (ソース・ネームスペースと呼ばれる) ごとに、クラスタ・ネームスペースを作成する必要があります。この要件は、各ネームスペースが個別のインスタンスに存在する場合と、複数のソース・ネームスペースが同一のインスタンスに存在する場合の両方に適用されます。後者の場合、同一のインスタンス上に複数のクラスタ・ネームスペースを構成して、各ソース・ネームスペースが対応するクラスタ・ネームスペースを持つようにする必要があります。
以下の図は、フェデレーション・テーブル、ソース・テーブル、クラスタ・ネームスペース、およびソース・ネームスペースのリレーションシップを示しています。
フェデレーション・テーブルの作成
シャード・クラスタを構成すると、フェデレーション・テーブルの作成は、フェデレーション・テーブルの定義と、フェデレーション・テーブルへのソース・テーブルの接続の 2 つの手順で構成されます。
フェデレーション・テーブルの定義
シャード・クラスタのマスタ・ネームスペースから、$SYSTEM.Sharding.CreateFederatedTable() を使用してフェデレーション・テーブルを作成できます。このメソッドの使用法の詳細は、クラス・リファレンスを参照してください。
CreateFederatedTable() によって、フェデレーション・テーブルを表し、ソース・テーブルの列をフェデレーション・テーブルに投影する方法を定義したフェデレーション・テーブル定義が、クラスタ・ネームスペースに登録されます。API 呼び出しの一部として、ソース・テーブルの列がフェデレーション・テーブルから除外されたり、定数値を持つダミー列が追加される場合があります (ソース・ネームスペースを識別するためなど)。またこのメソッドは、指定されたソース・ネームスペースからの最初のソース・テーブルをアタッチします。このソース・テーブルは、同じインスタンス上に格納されます。ソース・ネームスペースが、そのテーブルの投影先のフェデレーション・テーブルの存在によって影響を受けることはありません。
フェデレーション・テーブル定義に関して、以下の点に留意してください。
-
ソース・テーブル内のプライベート・プロパティは、CreateFederatedTable() の引数として明示的にリストされない限り、フェデレーション・テーブルに投影されません。
-
ソース・フィールドのデータ型は、フェデレーション・テーブル内の対応するフィールドのデータ型と同じである必要はありません。暗黙的なデータ型変換は、UNION 節で使用されるものと同じで、VARCHAR、DOUBLE、NUMERIC、BIGINT、INTEGER、SMALLINT、TINYINT のように、優先順位の最も高いデータ型を返します。
-
SqlRowIdPrivate クラス・キーワードはソース・テーブルによって指定され、ID はソース・テーブルからの名前で投影されます。このキーワードが定義されている場合、RowID はフェデレーション・テーブルに投影されません。DDL を介して作成されたテーブルでは、既定で SqlPrivateRowId が指定されることに注意してください。
新規作成されたフェデレーション・テーブルは、シャード・クラスタ内の任意のクラスタ・ネームスペースからすぐにアクセス可能になります。
この時点で、新規作成されたフェデレーション・テーブルは、CreateFederatedTable() の呼び出しで参照されたソース・ネームスペース内のソース・テーブルからのデータのみを投影します。ソース・テーブルのデータを他のソース・ネームスペースに追加する方法については、以下の "フェデレーション・テーブルへのソース・テーブルの接続" を参照してください。
例
以下の例では、ターミナルから CreateFederatedTable() を呼び出して、IRISCLUSTER ネームスペース (クラスタ・ネームスペースの既定の名前) にフェデレーション・テーブル Hospital.Employees を作成します。このフェデレーション・テーブルでは、USER ネームスペースの Employees.Doctors を最初のソース・テーブルとして使用します。このフェデレーション・テーブルの列は、ソース・テーブルの列と同一です。
do $SYSTEM.Sharding.CreateFederatedTable(,"Hospital.Employees", "USER", "Employees.Doctors")
以下の例では、IRISCLUSTER クラスタ・ネームスペースにフェデレーション・テーブル Hospital.DiagnosisLog を作成します。このフェデレーション・テーブルでは、HOSPITAL ネームスペースの Hospital.Patient を最初のソース・テーブルとして使用し、そのソース・テーブルから Diagnosis 列と DateAdmitted 列を投影します。ここでは、DateAdmitted 列が AdmissionDate としてフェデレーション・テーブルに投影されます。この例の結果として得られるフェデレーション・テーブルには、2 つの列のみが含まれます。
do $SYSTEM.Sharding.CreateFederatedTable("IRISCLUSTER","Hospital.DiagnosisLog", "HOSPITAL", "Hospital.Patient", $lb($lb("Diagnosis"), $lb("DateAdmitted","AdmissionDate")))
フェデレーション・テーブルへのソース・テーブルの接続
フェデレーション・テーブルを作成したら、これに他のソース・ネームスペースのソース・テーブルを接続します。そのためには、各ソース・ネームスペースで $SYSTEM.Sharding.ConnectFederatedTable() メソッドを使用します。このメソッドの詳細は、クラス・リファレンスを参照してください。
ソース・テーブルをフェデレーション・テーブルに接続すると、TUNE TABLE で収集されていたテーブル統計がそのフェデレーション・テーブルに報告されます。クエリ・パフォーマンスを最適化するために、ソース・テーブルをフェデレーション・テーブルに接続する前に、そのソース・テーブルで TUNE TABLE を実行して、テーブル統計を最大限正確なものにする必要があります。ソース・テーブルを再チューニングする必要があり、更新された統計をフェデレーション・テーブルに伝播する場合は、ソース・テーブルをチューニングしてから、ConnectFederatedTable() の Force 引数を 1 に指定して、これをフェデレーション・テーブルに再接続します。
ソース・テーブルが変更されたり、削除された場合、フェデレーション・テーブル定義は自動的に更新されません。これは、フェデレーション・テーブルがソース・ネームスペースを読み取り専用として扱い、そのネームスペース内のイベントはフェデレーション・テーブルをトリガできないためです。ソース・テーブルが変更された場合、変更されたソース・ネームスペースに対応するクラスタ・ネームスペースで ConnectFederatedTable() を呼び出し、ConnectFederatedTable() の Force 引数を 1 に指定して、変更を指定する必要があります。例えば、ソース・テーブル内の列名を変更した場合、フェデレーション・テーブルに投影される列名を再指定する必要があります。フェデレーション・テーブルに投影する必要のないソース・テーブルへの列の追加など、互換性のある変更の場合、このステップは不要です。
複数の異なるソース・ネームスペース間でソース・テーブルの構造が同じであれば、ConnectFederatedTable() の呼び出しは簡単にスクリプト化できます。%SYSTEM.ShardWorkMgr.Broadcast() インスタンス・メソッドを使用して、各クラスタ・ネームスペースで 1 回呼び出しを実行するだけです。詳細は、"例" を参照してください。
例
以下の例では、ConnectFederatedTable() を使用して、USER ネームスペース内の Employees.Nurses ソース・テーブルを IRISCLUSTER ネームスペース内の Hospital.Employees フェデレーション・テーブルに接続します。
do $SYSTEM.Sharding.CreateFederatedTable("IRISCLUSTER","Hospital.Employees","USER","Employees.Nurses")
以下の例では、%SYSTEM.ShardWorkMgr.Broadcast() インスタンス・メソッドを使用して、HOSPITAL ネームスペース内の Employees.Nurses というソース・テーブルを、新たに作成した、同じく Employees.Nurses というフェデレーション・テーブルに接続します。この手法では、インスタンス間に同じ引数がブロードキャストされるため、各インスタンスにまったく同じ名前のソース・テーブルおよびソース・ネームスペースが必要です。ConnectFederatedTable() のクラスタ・ネームスペースの引数は、空白のままにしておく必要があります。
zn "IRISCLUSTER"
set status=$SYSTEM.Sharding.CreateFederatedTable(,"Employees.Nurses","HOSPITAL","Employees.Nurses")
set shardManager=$SYSTEM.ShardWorkMgr.%New()
set status=shardManager.Broadcast("DS","##class(%SYSTEM.Sharding).ConnectFederatedTable",,"Employees.Nurses","HOSPITAL","Employees.Nurses")
フェデレーション・テーブルに対するクエリ
フェデレーション・テーブルに対するクエリは、標準テーブルに対するクエリと透過的に同様です。フェデレーション・テーブルに対するクエリは、ダイナミック SQL、埋め込み SQL、または JDBC や ODBC といったデータベース・ドライバを使用して実行できます。
フェデレーション・テーブルの削除または切断
シャード・クラスタからフェデレーション・テーブルを削除するには、$SYSTEM.Sharding.DropFederatedTable() を使用します。このメソッドは、任意のインスタンスのクラスタ・ネームスペースから呼び出され、すべてのクラスタ・ネームスペースからフェデレーション・テーブル定義を削除します。フェデレーション・テーブルはもう存在しないので、これ以降のフェデレーション・テーブルへのクエリの試行は失敗します。フェデレーション・テーブルの削除によって、ソース・テーブルやそのデータが影響を受けることはありません。
フェデレーション・テーブルから 1 つのソース・テーブルを削除するには、切断するソース・テーブルを含むソース・ネームスペースに対応するクラスタ・ネームスペースから $SYSTEM.Sharding.DisconnectFederatedTable() を呼び出します。このメソッドは、フェデレーション・テーブルからソース・テーブルを切断します。フェデレーション・テーブルからソース・テーブルを切断すると、そのフェデレーション・テーブルに対するクエリでは、そのソース・テーブルからのデータを返さなくなります。このソース・テーブルは、後で ConnectFederatedTable() を使用して、フェデレーション・テーブルに再接続することができます。
テーブルのリスト
INFORMATION.SCHEMA.TABLESOpens in a new tab 永続クラスは、現在のネームスペース内のすべてのテーブル (およびビュー) に関する情報を表示します。これには、スキーマ名とテーブル名、テーブルの所有者、新しいレコードを挿入できるかどうかなど、さまざまなプロパティが含まれます。TABLETYPE プロパティは、ベース・テーブルなのかビューなのかを示します。
以下の例では、現在のネームスペース内のすべてのテーブルとビューに関するテーブル・タイプ、スキーマ名、テーブル名、および所有者が返されます。
SELECT Table_Type,Table_Schema,Table_Name,Owner FROM INFORMATION_SCHEMA.TABLES
INFORMATION.SCHEMA.CONSTRAINTTABLEUSAGEOpens in a new tab 永続クラスは、現在のネームスペースの各テーブルに定義された主キー (明示的または暗黙的)、外部キー、または一意制約ごとに 1 つの行を表示します。INFORMATION.SCHEMA.KEYCOLUMNUSAGEOpens in a new tab は、現在のネームスペースの各テーブルのこれらのいずれかの制約の一部として定義されたフィールドごとに、1 つの行を表示します。
管理ポータルの SQL インタフェースの [カタログの詳細] タブを使用して、単一テーブルに対してほぼ同じ情報を表示できます。
列の名前と番号のリスト
指定したテーブルのすべての列名 (フィールド名) をリストできる方法として、次の 4 つがあります。
-
GetAllColumns()Opens in a new tab メソッド。これは、非表示の列を含め、すべての列名および列番号をリストします。GetVisibleColumns()Opens in a new tab メソッドは、非表示でないすべての列をリストします。ID (RowID) フィールドは非表示であることも非表示でないこともあります。x__classname 列は常に非表示です。これは、永続クラスが Final クラス・キーワードを指定して定義されていない限り、自動的に定義されます。
-
管理ポータルの SQL インタフェース ([システム・エクスプローラ]→[SQL]) のスキーマ・コンテンツの [カタログの詳細] タブ。これは、すべての列名と列番号 (非表示の列を含む)、およびその他の情報 (データ型、および列が非表示かどうかを示すフラグなど) をリストします。
-
SELECT TOP 0 * FROM tablename。これは、非表示でないすべての列の名前を列番号順にリストします。非表示の列はどこでも列番号順に表示できるため、非表示でない列の名前を数えることで列番号を特定することはできないことに注意してください。アスタリスク構文の詳細は、"SELECT" コマンドを参照してください。
-
INFORMATION.SCHEMA.COLUMNSOpens in a new tab 永続クラスは、現在のネームスペースの各テーブルまたはビューの、非表示でない各列の行をリストします。INFORMATION.SCHEMA.COLUMNSOpens in a new tab は、テーブルおよびビューの列の特性をリストするための、多くのプロパティを提供します。非表示のフィールドはカウントされないため、ORDINALPOSITION は列番号と同じではないことに注意してください。GetAllColumns() メソッドは、非表示のフィールドと非表示でないフィールドの両方をカウントします。
以下の例は、INFORMATION.SCHEMA.COLUMNSOpens in a new tab を使用していくつかの列のプロパティをリストします。
SELECT TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT,IS_NULLABLE,UNIQUE_COLUMN,PRIMARY_KEY FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='Sample'
列の取得メソッド
テーブル内の列の名前を列番号順にリストするには、GetAllColumns()Opens in a new tab または GetVisibleColumns()Opens in a new tab メソッドを次のように使用します。
SET stat=##class(%SYSTEM.SQL.Schema).GetAllColumns("Sample.Person",.byname,.bynum)
IF stat=1 {
SET i=1
WHILE $DATA(bynum(i)) { WRITE "name is ",bynum(i)," col num is ",i,!
SET i=i+1 }
}
ELSE { WRITE "GetAllColumns() cannot locate specified table" }
GetAllColumns() は、非表示の列を含め、定義されているすべての列をリストします。テーブルが埋め込み %SerialObject クラスを参照する場合、GetAllColumns() はまず永続クラスのすべての列をリストし (%SerialObject を参照するプロパティを含め)、次にすべての %SerialObject プロパティをリストします。これを、以下の GetAllColumns() の結果に示します。
name is ID col num is 1 name is Age col num is 2 name is Home col num is 3 name is Name col num is 4 name is x__classname col num is 5 name is Home_City col num is 6 name is Home_Phone col num is 7 name is Home_Phone_AreaCode col num is 8 name is Home_Phone_Country col num is 9 name is Home_Phone_TNum col num is 10 name is Home_PostalCode col num is 11 name is Home_State col num is 12 name is Home_Street col num is 13
このメソッドを次のように使用して、指定した列名の列番号を特定することもできます。
SET stat=##class(%SYSTEM.SQL.Schema).GetAllColumns("Sample.Person",.byname)
IF stat=1 {
WRITE "Home_State is column number ",byname("Home_State"),! }
ELSE { WRITE "GetAllColumns() cannot locate specified table" }
制約のリスト
INFORMATION.SCHEMA.TABLECONSTRAINTSOpens in a new tab 永続クラスは、テーブル名、制約タイプ、および制約名をリストします。制約タイプには、UNIQUE、PRIMARY KEY、および FOREIGN KEY があります。テーブル定義で制約の名前を指定しなかった場合、制約名は、テーブル名、制約タイプ、およびテーブルの列番号から生成されます。例えば、MYTABLE_UNIQUE3 のようになります。これを、以下の例に示します。
SELECT Table_Schema,Table_Name,Constraint_Type,Constraint_Name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INFORMATION.SCHEMA.CONSTRAINTCOLUMNUSAGEOpens in a new tab 永続クラスは、テーブル名、列名、および制約名をリストします。制約に複数の列が含まれる場合は、列ごとに個々の項目がリストされます。テーブル定義で制約の名前を指定しなかった場合、制約名は、テーブル名、制約タイプ、およびテーブルの列番号から生成されます。例えば、MYTABLE_UNIQUE3 のようになります。これを、以下の例に示します。
SELECT Table_Schema,Table_Name,Column_Name,Constraint_Name FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
INFORMATION.SCHEMA.REFERENTIALCONSTRAINTSOpens in a new tab 永続クラスは、参照するテーブルを含む外部キー制約 (CONSTRAINT_SCHEMA、CONSTRAINT_TABLE_NAME)、参照されるテーブル (UNIQUE_CONSTRAINT_SCHEMA、UNIQUE_CONSTRAINT_TABLE)、外部キー名 (CONSTRAINT_NAME)、および UPDATE と DELETE の参照アクション (UPDATE_RULE、DELETE_RULE) とその値 NO ACTION、SET DEFAULT、SET NULL、または CASCADE をリストします。これを、以下の例に示します。
SELECT Constraint_Table_Name,Unique_Constraint_Table,Constraint_Name,Update_Rule,Delete_Rule FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS