Skip to main content

This is documentation for Caché & Ensemble. See the InterSystems IRIS version of this content.Opens in a new tab

For information on migrating to InterSystems IRISOpens in a new tab, see Why Migrate to InterSystems IRIS?

テーブルの定義

この章では、Caché SQL でテーブルを作成する方法を説明します。以下のトピックについて説明します。

テーブル名とスキーマ名

テーブルを作成するには、テーブルを定義するか (CREATE TABLE を使用)、またはテーブルに投影される永続クラスを定義します。

  • DDL : Caché は、CREATE TABLE で指定されたテーブル名を使用して対応する永続クラス名を生成し、指定されたスキーマ名を使用して対応するパッケージ名を生成します。

  • クラス定義 : Caché は、永続クラス名を使用して対応するテーブル名を生成し、パッケージ名を使用して対応するスキーマ名を生成します。

これら 2 つの名前の間の対応は、以下の理由から同一にならない場合があります。

  • 永続クラスと SQL テーブルは異なる名前付け規則に従います。有効な文字と長さについて異なる要件が適用されます。スキーマ名とテーブル名では大文字と小文字は区別されません。パッケージ名とクラス名では区別されます。入力された有効な名前はシステムによって対応する有効な名前に自動的に変換され、生成された名前は確実に一意になります。

  • 永続クラス名と対応する SQL テーブル名は既定で一致します。SqlTableName クラス・キーワードを使用して、異なる SQL テーブル名を指定できます。

  • 既定のスキーマ名は、既定のパッケージ名に一致しない場合があります。未修飾の SQL テーブル名または永続クラス名を指定した場合、システムによって既定のスキーマ名またはパッケージ名が入力されます。既定の初期スキーマ名は SQLUser で、既定の初期パッケージ名は User になります。

システム全体のデフォルト・スキーマ

未修飾名を持つテーブルを作成した場合、Caché はそれにシステム全体のデフォルト SQL スキーマ名を割り当てます。スキーマ名の解析に関する以下の説明は、テーブル名、ビュー名、およびストアド・プロシージャ名にも当てはまります。

現在のデフォルト・スキーマ名を返すには、以下のように $SYSTEM.SQL.DefaultSchema()Opens in a new tab メソッドを呼び出します。

  WRITE $SYSTEM.SQL.DefaultSchema()

初期のシステム全体の既定の SQL スキーマ名は、SQLUser です。

Note:

SQL の既定のスキーマは SQLUser です。対応する永続クラス・パッケージ名は User です。

以下のいずれかを使用して、スキーマの既定値を変更できます。

  • $SYSTEM.SQL.SetDefaultSchema()Opens in a new tab メソッド。

  • 管理ポータルに移動します。[システム管理] から、[構成][SQL およびオブジェクトの設定][一般SQL設定] (システム, 構成, 一般SQL設定) を選択します。この画面で、[デフォルト SQL スキーマ名] の現在の設定を表示および編集できます。

デフォルト SQL スキーマ名を _CURRENT_USER に設定する場合、既定スキーマは SQL にログインするために使用される処理のユーザ名となります。その処理が SQL にログインしない場合、SQLUser が既定スキーマです。デフォルト SQL スキーマ名を _CURRENT_USER/Name に設定する場合、既定スキーマは SQL にログインするために使用される処理のユーザ名となります。その処理が SQL にログインしない場合、Name が既定スキーマです。

Caution:

デフォルト SQL スキーマ名を変更すると、システム上のすべてのネームスペースのすべてのクエリ・キャッシュが自動的に削除されます。デフォルト・スキーマ名を変更すると、未修飾のテーブル、ビュー、またはストアド・プロシージャ名を含むすべてのクエリの意味が変更されることになります。デフォルト SQL スキーマ名は、Caché のインストール時に設定されたものをその後に修正しないことを強くお勧めします。

スキーマの名前付けに関する考慮事項

スキーマ名は、識別子の規約に従い、英数字以外の文字の使用法に関して多くの考慮事項があります。“USER” またはその他の SQL 予約語をスキーマ名として指定しようとすると、SQLCODE -312 エラーとなります。INFORMATION_SCHEMA スキーマ名および対応する INFORMATION.SCHEMA パッケージ名は、すべてのネームスぺースで予約されています。このスキーマおよびパッケージ内で、テーブルまたはクラスを作成しないでください。

CREATE TABLE コマンドを使用してスキーマを作成する場合、Caché はスキーマ名を使用して対応するパッケージ名を生成します。スキーマの名前付け規約と対応するパッケージの名前付け規約は異なるため、ユーザは名前を変換する際の英数字以外の文字に関する考慮事項に注意する必要があります。これらの名前変換の考慮事項はテーブルと同じではありません。

  • 最初の文字 :

    • % (パーセント) : % をスキーマ名の最初の文字として指定すると、対応するパッケージがシステム・パッケージであり、そのすべてのクラスがシステム・クラスであることを示します。この使用には、適切な特権が必要です。そうでないと、<PROTECT> エラーを示す %msg を含む SQLCODE -400 エラーが出力されます。

    • _ (アンダースコア) : スキーマ名の最初の文字がアンダースコア文字である場合、この文字は対応するパッケージ名内で小文字の “u” に置き換えられます。例えば、スキーマ名 _MySchema は、パッケージ名 uMySchema を生成します。

  • 後続の文字 :

    • _ (アンダースコア) : スキーマ名の最初の文字以外の任意の文字がアンダースコア文字である場合、この文字は対応するパッケージ名内でピリオド (.) に置き換えられます。ピリオドはクラス区切り文字であるため、アンダースコアはスキーマをパッケージとサブパッケージに分割します。したがって、My_Schema はパッケージ Schema を含むパッケージ My を生成します (My.Schema)。

    • @、#、$ 文字 : スキーマ名にこれらの文字のいずれかが含まれる場合、これらの文字は対応するパッケージ名から削除されます。これらの文字を削除することで重複したパッケージ名が生成される場合、削除されたパッケージ名がさらに変更されます。削除されたスキーマ名の最後の文字が連続した整数 (0 で開始) で置き換えられ、一意のパッケージ名が生成されます。したがって、My@#$Schema はパッケージ MySchema を生成し、以降に作成される My#$Schema は、パッケージ MySchem0 を生成します。同じルールはクラス名に対応するテーブル名にも適用されます。

プラットフォーム固有のスキーマ名

Mac で ODBC ベースのクエリを作成し、Microsoft Query を使用して Microsoft Excel から実行する場合、使用可能なテーブルのリストからテーブルを選択すると、生成されたクエリにはテーブルのスキーマ (クラスのパッケージと同等のもの) は含まれません。例えば、(Samples ネームスペースの) Sample スキーマから Person テーブルのすべての行を返すよう選択すると、生成されるクエリは以下のようになります。

SELECT * FROM Person

Caché では未修飾のテーブル名は SQLUser スキーマにあるものとして解釈されるため、この文は、失敗するか、間違ったテーブルからデータを返します。これを修正するには、[SQL ビュー] タブで、必要なスキーマを明示的に参照するようにクエリを編集します。クエリは以下のようになります。

SELECT * FROM Sample.Person

テーブルの名前付けに関する考慮事項

テーブル名は、識別子の規約に従い、英数字以外の文字の使用法に関して多くの考慮事項があります。

CREATE TABLE コマンドを使用してテーブルを作成する場合、Caché はテーブル名を使用して対応するクラス名を生成します。テーブルの名前付け規約と対応するクラスの名前付け規約は異なるため、ユーザは名前を変換する際の英数字以外の文字に関する考慮事項に注意する必要があります。

  • 最初の文字 :

    • % (パーセント) : % をテーブル名の最初の文字として指定すると、対応するパッケージがシステム・クラスであることを示します。この使用には、適切な特権が必要です。そうでないと、<PROTECT> エラーを示す %msg を含む SQLCODE -400 エラーが出力されます。

    • _ (アンダースコア) : テーブル名の最初の文字がアンダースコア文字である場合、この文字は対応するパッケージ名から削除されます。例えば、テーブル名 _MyTable は、クラス名 MyTable を生成します。

    • 数値 : テーブル名の最初の文字を数値にすることはできません。テーブル名の最初の文字が句読点文字の場合、2 番目の文字に数字を指定することはできません。これにより、SQLCODE -400 エラーが発生し、生成される %msg の値は “エラー #5053 : クラス名 'schema.name' が無効です” になります (句読点文字なし)。例えば、指定したテーブル名が _7A の場合、生成される %msg は “エラー #5053: クラス名 'User.7A' が無効です” になります。

  • 後続の文字 :

    • 文字 : テーブル名には、最低でも 1 文字を含める必要があります。テーブル名の先頭の文字または最初の句読点に続く文字は、数字以外の文字にする必要があります。$ZNAME テストに合格した文字は、有効な文字です。$ZNAME 文字検証はロケールによって異なります (SQL 識別子 では、$ZNAME では有効な名前文字と判断されないアンダースコア文字 (_) を使用できるので、$ZNAME を使用して SQL テーブルとフィールド名を検証することはできません)。

    • _ (アンダースコア)、@、#、$ 文字 : テーブル名にこれらの文字のいずれかが含まれる場合、これらの文字は対応するクラス名から削除されます。これらの文字を削除することで重複したクラス名が生成される場合、削除されたクラス名がさらに変更されます。削除されたテーブル名の最後の文字が連続した整数 (0 で開始) で置き換えられ、パッケージ内で一意のクラス名が生成されます。したがって、My@#$_Table はパッケージ MyTable を生成し、以降に作成される My_#$Table は、パッケージ MyTabl0 を生成します。生成されたクラス名には句読点が含まれないため、句読点のみが異なるテーブル名の作成はお勧めできません。

  • テーブル名は、そのスキーマ内で重複しないようにする必要があります。既存テーブルと大文字/小文字区別のみが異なる名前でテーブルを作成しようとすると、SQLCODE -201 エラーが生成されます。

    $SYSTEM.SQL.TableExists()Opens in a new tab メソッドを使用して、テーブル名が既に存在するかどうかを確認できます。このメソッドは、テーブル名に対応するクラス名も返します。

“USER“ またはその他の SQL 予約語をスキーマ名として指定しようとすると、SQLCODE -312 エラーとなります。SQL 予約語をテーブル名またはスキーマ名として指定するには、名前を区切り文字付き識別子として指定します。区切り文字付き識別子を使用して英数字以外の文字を含むテーブルまたはスキーマ名を指定した場合、Caché は対応するクラスまたはパッケージ名を生成する際にこれらの英数字以外の文字を削除します。

以下のテーブル名の長さの制限が適用されます。

  • 一意性 : Caché はテーブル・クラス名の最初の 59 の英数字で一意性チェックを実行します。対応する SQL テーブル名は 59 文字を超える長さになる可能性がありますが、英数字以外の文字を取り除くと、この 59 文字の制限内で一意である必要があります。Caché はパッケージ名の最初の 189 文字で一意性チェックを実行します。

  • 推奨される最大長 : テーブル名は 128 文字を超えることはできません。テーブル名は 96 文字よりも大幅に長くすることができますが、最初の 96 の英数文字が異なるようにテーブル名を作成すると処理がはるかに容易になります。

  • 合計最大長 : テーブル名とそのスキーマ名 (同時に追加する場合) はスキーマ名とテーブル名を区切るドット文字を含め、220 文字を超えることはできません。

テーブル名の詳細は、“Caché SQL リファレンス” の “CREATE TABLE コマンド” を参照してください。クラスの詳細は、"Caché オブジェクトの使用法" ドキュメントの “Caché クラス” を参照してください。

RowID フィールド

RowID フィールドは、各レコードを連続した整数で一意に識別できる、システム生成のフィールドです。テーブルの定義時に、Caché SQL は、このフィールドを自動的に定義します。テーブルにデータが移入されるときに、Caché は、このフィールドに 1 から始まる連続した整数を割り当てます。RowID データの値は、必須、一意、NULL 以外、および変更不可として定義されます。

既定では、Caché は、このフィールドに “ID” という名前を付けます。ただし、このフィールド名は予約されていません。ユーザが “ID” というフィールドを定義している場合、Cache は RowId に “ID1” という名前を付けます。例えば、その後でユーザが ALTER TABLE を使用して “ID1” という名前のフィールドを定義すると、Caché は RowID の名前を “ID2” に変更します (それ以降も同様に動作します)。そのため、Caché は、どのような名前が RowID に割り当てられていても常に RowID (オブジェクト ID) 値を返す、%ID 疑似列名 (エイリアス) を提供しています。

既定では、Caché はこのフィールドを列番号 1 として定義します。

ALTER TABLE では、RowID フィールド定義の変更や削除はできません。

Row ID の値は、常にインクリメントされます。再使用されることはありません。そのため、削除操作や挿入操作でテーブル・データに変更が加えられていると、Row ID の値は昇順 (挿入順) の数値にはなりますが、数値としての連続性はなくなります。特定の ID 値が存在しているかどうかを判別するには、テーブルの %ExistsId() メソッドを呼び出します。

RowID カウンタは、TRUNCATE TABLE コマンドによって 1 にリセットされます。このカウンタは、DELETE コマンドがテーブル内のすべての行を削除した場合でも、DELETE コマンドによってはリセットされません。

既定では、RowId は非表示で (SELECT * では表示されない)、PRIVATE です。テーブルの作成時に、%PUBLICROWID キーワードを指定すると、RowId は非表示でなくなり、PUBLIC になります。このキーワードはテーブルの RowID を PUBLIC にすることを指定しているため、この RowID は外部キー参照として使用できるようになります。%PUBLICROWID キーワードを指定する場合、テーブルに対応するクラスは “Not SqlRowIdPrivate“ で定義されます。このオプション・キーワードは、テーブル要素の CREATE TABLE コンマ区切りリスト内の任意の場所で指定できます。ALTER TABLE では指定できません。

Note:

Samples ネームスペースで提供されるテーブルの例は、そのほとんどが %PUBLICROWID で定義されています。

テーブル内のフィールド名 (非表示および表示) をリストする方法は、“列の名前と番号” を参照してください。

既定では、RowID の値をユーザが変更することはできません。RowId 値の変更は、取り返しの付かない結果を招くことがあるため、極めて特別な状況でのみ、十分な注意を払って行う必要があります。Config.SQL.AllowRowIDUpdateOpens in a new tab プロパティにより、RowID の値のユーザによる変更を許可します。

詳細は、CREATE TABLE のリファレンス・ページの "RowID フィールドと %PUBLICROWID" を参照してください。

RowVersion および Serial カウンタ・フィールド

InterSystems SQL は、カウンタ値を自動的にインクリメントするための 2 つの専用データ型をサポートしています。

  • データ型 ROWVERSION のフィールドでは、ネームスペース全体のすべての RowVersion テーブルに対する挿入と更新をカウントします。ROWVERSION フィールドを含むテーブルの挿入と更新のみが、このカウンタをインクリメントします。ROWVERSION の値は一意で、変更できません。このネームスペース全体のカウンタがリセットされることはありません。

  • データ型 SERIAL のフィールド (%Library.CounterOpens in a new tab) では、テーブルへの挿入をカウントします。既定では、このフィールドは自動的にインクリメントされた整数を受け取ります。ただし、ユーザはこのフィールドに値を指定できます。

RowVersion フィールド

RowVersion フィールドは、行レベルのバージョン管理を提供する、オプションのユーザ定義フィールドです。これにより、ネームスペース全体で各行のデータが変更された順序を把握できます。Caché はネームスペース全体を範囲とするカウンタを維持しており、行データが変更 (挿入、更新、または %Save) されるたびに、そのフィールドに固有の正の増分整数を割り当てます。このカウンタはネームスペース全体を範囲とするため、ROWVERSION フィールドを含む 1 つのテーブルでの操作によって、同じネームスペース内の ROWVERSION フィールドを含む他のすべてのテーブルで使用される ROWVERSION カウンタの増分ポイントが設定されます。

RowVersion フィールドは、フィールド・データ型 ROWVERSION を指定して作成します。テーブルごとに指定できる ROWVERSION データ型フィールドは 1 つのみです。複数の ROWVERSION フィールドを含むテーブルを作成しようとすると、5320 コンパイル・エラーが発生します。

このフィールドには任意の名前を付けることができ、どの列位置にも表示できます。ROWVERSION (%Library.RowVersionOpens in a new tab) データ型は BIGINT (%Library.BigIntOpens in a new tab) にマッピングされます。

このフィールドは、自動的にインクリメントされるカウンタから 1 で始まる正の整数を受け取ります。このカウンタは、ROWVERSION 対応テーブル内のデータが挿入、更新、または %Save 操作によって変更されると必ずインクリメントされます。インクリメントされた値は、挿入または更新された行の ROWVERSION フィールドに記録されます。

ネームスペースには、RowVersion フィールドがあるテーブルと、そのフィールドがないテーブルを含めることができます。RowVersion フィールドがあるテーブルのデータが変更された場合にのみ、ネームスペース全体のカウンタは増分されます。

テーブルにデータが取り込まれるときに、Caché は挿入された各行に対して、このフィールドに連続的な整数を割り当てます。既にデータが取り込まれているテーブルに ROWVERSION フィールドを追加するために ALTER TABLE を使用する場合、このフィールドは既存のフィールドについては NULL として作成されます。その後テーブルで挿入や更新が実行されると、その行の RowVersion フィールドに連続的な整数が割り当てられます。このフィールドは読み取り専用です。RowVersion 値を変更しようとすると、SQLCODE -138 エラー : "読み取り専用フィールドを INSERT/UPDATE することはできません" が生成されます。したがって、RowVersion フィールドは固有であり変更不可であると定義されますが、必須であるまたは NULL でないとは定義されません。

RowVersion の値は必ず増分になります。再使用されることはありません。したがって、挿入と更新により割り当てられる固有の RowVersion 値は、一時的な順序です。削除操作では、このシーケンスから数字が削除されます。そのため、RowVersion 値は数字的に連続していない場合があります。

このカウンタはリセットされません。すべてのテーブル・データを削除しても、RowVersion カウンタはリセットされません。ROWVERSION フィールドを含むネームスペース内のすべてのテーブルを削除しても、このカウンタはリセットされません。

一意キーや主キーに RowVersion フィールドを含めることはできません。RowVersion フィールドは、IDKey インデックスの一部にすることはできません。

RowVersion フィールドは非表示ではありません (これは SELECT * により表示されます)。

これについては以下の、同じネームスペースにある 3 つのテーブルの例で示しています。

  1. それぞれ ROWVERSION フィールドがある Table1 と Table3 を作成し、ROWVERSION フィールドがない Table2 を作成します。

  2. Table1 に 10 行を挿入します。そうするとこれらの行の ROWVERSION 値により、カウンタは 10 だけ増分されます。カウンタはそれより前には使用されていなかったので、1 から始まり 10 になります。

  3. Table2 に 10 行を挿入します。Table2 には ROWVERSION フィールドがないので、カウンタは増分されません。

  4. Table1 の 1 行を更新します。そうするとこの行の ROWVERSION 値が反映されて、カウンタは増分されます (この場合は 11 になります)。

  5. Table3 に 10 行を挿入します。そうするとこれらの行の ROWVERSION 値により、カウンタは 10 だけ増分されます (12 から始まり 21 になります)。

  6. Table1 の 1 行を更新します。そうするとこの行の ROWVERSION 値が反映されて、カウンタは増分されます (この場合は 22 になります)。

  7. Table1 の 1 行を削除します。この場合に ROWVERSION カウンタは変更されません。

  8. Table3 の 1 行を更新します。そうするとこの行の ROWVERSION 値が反映されて、カウンタは増分されます (この場合は 23 になります)。

Serial フィールド

SERIAL データ型 (永続クラス・テーブル定義%Library.CounterOpens in a new tab) を使用して、1 つ以上のオプションの整数カウンタ・フィールドを指定して、テーブルへのレコードの挿入順序を記録することができます。既定では、テーブルに行が挿入されると常に、このフィールドは自動的にインクリメントされるテーブル・カウンタから正の整数を受け取ります。ただし、ユーザは挿入中にこのフィールドの整数値を指定して、テーブル・カウンタの既定をオーバーライドできます。

  • INSERT がカウンタ・フィールドの値を指定しない場合は、自動的に正の整数カウンタ値を受け取ります。カウントは 1 から始まります。連続する各値は、このフィールドの最大割り当てカウンタ値から 1 ずつ増加します。

  • INSERT がカウンタ・フィールドの整数値を指定する場合、フィールドはその値を受け取ります。これは、正または負の整数値で、現在のカウンタ値よりも小さくても大きくてもかまいません。また、このフィールドに既に割り当てられている整数にすることもできます。この値が、割り当てられたカウンタ値よりも大きい場合は、次に自動的に割り当てられるカウンタのインクリメント開始点をその値に設定します。

カウンタ・フィールドの値を UPDATE しようとすると、SQLCODE -105 エラーが返されます。

このカウンタは、TRUNCATE TABLE コマンドによって 1 にリセットされます。このカウンタは、DELETE コマンドがテーブル内のすべての行を削除した場合でも、DELETE コマンドによってはリセットされません。

永続クラスの作成によるテーブルの定義

Caché でテーブルを定義するには、スタジオを使用して永続クラスの定義を作成します。これらのクラスが Caché データベースで保存およびコンパイルされると、クラス定義に対応するリレーショナル・テーブルに自動的に投影されます (各クラスはテーブルを表し、各プロパティは列を表します)。1 つのクラス (テーブル) に定義可能なプロパティ (列) の最大数は 1000 です。

例えば、以下は、永続クラス MyApp.Person を定義します。

Class MyApp.Person Extends %Persistent 
{
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);
}

これをコンパイルすると、MyApp.Person 永続クラスおよび対応する SQL テーブル PersonMyApp スキーマ内に作成されます。これらの操作の実行方法の詳細は、"クラスの定義と使用" の “クラスの定義とコンパイル” を参照してください。

この例では、パッケージ名 MyApp が指定されています。永続クラスを定義する場合、パッケージ名が指定されていなければ、既定で User になります。これは、既定の SQL スキーマ名 SQLUser に対応します。例えば、Students という名前のテーブルを永続クラスとして定義すると、クラス User.Students と、対応する SQL スキーマ.テーブル名 SQLUser.Students が作成されます。

この例では、永続クラス名 Person が既定の SQL テーブル名です。SqlTableName クラス・キーワードを使用して、異なる SQL テーブル名を指定できます。

DDL CREATE TABLE 文を使用して、SQL スキーマ.テーブル名を指定しても、同じ MyApp.Person テーブルを定義できます。この SQL 文が正常に実行されると、対応する永続クラスがパッケージ名 MyApp とクラス名 Person で作成されます。

CREATE TABLE MyApp.Person (
    Name VARCHAR(50) NOT NULL,
    SSN VARCHAR(15) DEFAULT 'Unknown',
    DateOfBirth DATE,
    Sex VARCHAR(1)
)

CREATE TABLE は、対応するクラス定義で明示的な StorageStrategy を指定しません。代わりに、定義された既定のストレージ・ストラテジを取ります。

既定では、CREATE TABLE は対応するクラス定義で Final クラス・キーワードを指定します。これは、サブクラスを持てないことを示しています。

データベースのオブジェクト・ビューがどのようにリレーショナル・ビューに対応するかの概要は、"Caché オブジェクトの使用法" の “永続オブジェクトの概要” の章の “既定の SQL プロジェクションの概要” を参照してください。

上記のような永続クラス定義がコンパイルされると、対応するテーブルが作成されますが、このテーブル定義は、SQL DDL コマンド (または管理ポータルの Drop アクション) を使用して変更や削除することはできません。これらのコマンドを使用してこれらの操作を実行しようとすると、[DDL がクラス schema.name に対して有効になっていません]というメッセージが表示されます。これらの操作を許可するには、そのテーブル・クラス定義で [DdlAllowed] を指定する必要があります。

Class MyApp.Person Extends %Persistent [DdlAllowed]

クラス定義内で %Populate を指定すると、テスト・データが含まれたテーブルを自動的に生成できます。

Class MyApp.Person Extends (%Persistent,%Populate) [DdlAllowed]

これにより、そのクラスでは Populate() メソッドを使用できるようになります。このメソッドを実行すると、10 行のテスト・データを含むテーブルが生成されます。

一意の値

CREATE TABLE を使用すると、フィールドを UNIQUE として定義できます。これは、すべてのフィールドの値が一意の (重複がない) 値になることを意味します。

テーブルで永続クラスとして定義しても、対応するプロパティ・キーワードはサポートされません。代りに、プロパティとそのプロパティに対する一意のインデックスを定義する必要があります。以下の例では、各レコードに一意の Num 値を指定します。

  Class Sample.CaveDwellers Extends %Persistent [ DdlAllowed ]
  { 
  Property Num As %Integer;
  Property Troglodyte As %String(MAXLEN=50);
  Index UniqueNumIdx On Num [ Type=index,Unique ];
  }

一意の値のフィールドは、INSERT OR UPDATE 文を使用する際に必要になります。

クラス・プロパティ・キーワードのリファレンス資料は、"Caché クラス定義リファレンス" の “プロパティ・キーワード” の章を参照してください。

計算値

以下のクラス定義の例は、フィールド (Birthday) を含むテーブルを定義します。このテーブルは、DateOfBirth フィールド値を最初に設定したときに値を計算する SqlComputed と、DateOfBirth フィールド値を更新したときに値を再計算する SqlComputeOnChange を使用しています。Birthday フィールドの値には、このフィールドの値が計算/再計算されたときを記録する現在のタイムスタンプが含まれています。

Class Sample.MyStudents Extends %Persistent [DdlAllowed]
{
  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 ];
}

既存の DateOfBirth 値を指定する DateOfBirth に対して UPDATE を実行しても Birthday フィールドの値は再計算されません。対応する SQL コードは、"CREATE TABLE" のリファレンス・ページの "COMPUTECODE" のセクションを参照してください。

クラス・プロパティ・キーワードのリファレンス資料は、"Caché クラス定義リファレンス" の “プロパティ・キーワード” の章を参照してください。

クラス・メソッド

テーブル定義の一部としてクラス・メソッドを指定できます。その例を以下に示します。

Class MyApp.Person Extends %Persistent 
{
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);
ClassMethod Numbers() As %Integer [ SqlName = Numbers, SqlProc ]
  {
   QUIT 123
  }
}

以下に示すように、このメソッドは SELECT クエリ内で呼び出せます。

SELECT Name,SSN,MyApp.Numbers() FROM MyApp.Person

DDL を使用したテーブルの定義

Caché SQL で標準の DDL コマンドを使用してテーブルを定義できます。

Caché SQL で使用可能な DDL コマンド
ALTER コマンド CREATE コマンド DROP コマンド

ALTER TABLE

ALTER VIEW

CREATE TABLE

CREATE VIEW

CREATE INDEX

CREATE TRIGGER

DROP TABLE

DROP VIEW

DROP INDEX

DROP TRIGGER

これらの詳細は、"Caché SQL リファレンス" を参照してください。

DDL コマンドは、以下のようなさまざまな方法で実行できます。

埋め込み SQL での DDL の使用

ObjectScript のメソッドまたはルーチンで埋め込み SQL を使用して、DDL コマンドを呼び出すことができます。

例えば、以下のメソッドは TEST.EMPLOYEE テーブルを生成します。

ClassMethod CreateTable() As %Integer
{
 &sql(CREATE TABLE TEST.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)))
        
 Write "SQL: ",SQLCODE,!
 QUIT SQLCODE
}

このメソッドが呼び出されると、TEST.EMPLOYEE テーブルの生成を試みます (同様に、対応する TEST.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 または Caché Basic では、ダイナミック SQL %SQL.StatementOpens in a new tab オブジェクトを使用して、ダイナミック SQL を使用した DDL コマンドの作成と実行が可能です。

以下に示す例では、ダイナミック SQL を使用してテーブルを作成するクラス・メソッドを定義しています。

  Class Sample.NewT
  {
  ClassMethod DefTable(user As %String,pwd As %String) As %Status [Language=cache]
    {
    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 qStatus'=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","mycachepassword")

埋め込み SQL の例と同様に、現在ログインしているユーザがいない場合、このメソッドは失敗します。

コマンド行から DDL スクリプトを実行

Caché SQL DDL スクリプト・ファイルをインポートするには、ターミナル・セッションからインタラクティブに Cache()Opens in a new tab メソッドを使用するか、バックグラウンド・ジョブとして DDLImport("CACHE")Opens in a new tab メソッドを使用します。詳細は、このドキュメントの "SQL コードのインポート" の章を参照してください。

リレーショナル・データベースから Caché にテーブルを移行する際、テキスト・ファイル内に 1 つ以上の DDL スクリプトが存在していることがあります。Caché には、そのようなテーブルを Caché にロードするために役立つメソッドが用意されています。詳細 (特に Oracle()Opens in a new tabSybase()Opens in a new tabMSSQLServer()Opens in a new tab の各メソッドについて) は、"%SYSTEM.SQLOpens in a new tab" クラスを参照してください。

例えば、Caché コマンド行から Oracle DDL ファイルをロードするには、以下を実行します。

  1. “Caché キューブ” メニューの [ターミナル] コマンドを使用して、ターミナル・セッションを開始します。

  2. テーブル定義をロードしたいネームスペースに切り替えます。

     ZN "MYNAMESPACE"
  3. 目的の DDL インポート・メソッドを呼び出します。

     DO $SYSTEM.SQL.Oracle()

    ターミナルに表示された指示に従います。

既存のテーブルのクエリによるテーブルの定義

$SYSTEM.SQL.QueryToTable()Opens in a new tab メソッドを使用して、既存のテーブルに基づいて新しいテーブルを定義してデータを生成できます。ユーザはクエリと新しいテーブル名を指定します。既存のテーブル名または新しいテーブル名、あるいはその両方は、修飾または未修飾にできます。クエリに JOIN 構文を含めることができます。クエリで、新しいテーブルの列名になる列名エイリアスを指定できます。

  1. QueryToTable() は、既存のテーブルの DDL 定義をコピーして、指定した新しいテーブル名を割り当てます。データ型、maxlength、および minval/maxval を含む、クエリで指定されているフィールドの定義がコピーされます。既定値、必須の値、一意の値など、フィールド・データ制約はコピーされません。

    クエリで SELECT * または SELECT %ID を指定した場合、元のテーブルの RowID フィールドが、データ型 INTEGER の、必須ではない非一意のデータ・フィールドとしてコピーされます。QueryToTable() は、新しいテーブルの一意の RowID フィールドを生成します。コピーされた RowID が ID という名前である場合、生成される RowID は ID1 という名前になります。

    QueryToTable() は、この新しいテーブルに対して対応する永続クラスを作成します。この永続クラスは DdlAllowed として定義されます。新しいテーブルの所有者は現在のユーザです。

    新しいテーブルは、ソース・テーブルの該当する設定に関係なく、[%Cache ストレージ] = [はい]、および [ビットマップ・インデックスのサポート] = [はい] で定義されます。

    新しいテーブルに対して作成される唯一のインデックスは、IDKEY インデックスです。ビットマップ・エクステント・インデックスは生成されません。コピーされたフィールドのインデックス定義は、新しいテーブルにコピーされません。

    フィールド間の参照はコピーされません。

  2. 続いて、QueryToTable() が、クエリで選択されたフィールドから新しいテーブルにデータを生成します。また、テーブルのエクステント・サイズを 100,000 に設定します。さらに、IDKEY ブロック・カウントを試算します。テーブル・チューニングを実行し、実際のエクステント・サイズとブロック・カウントを設定し、各フィールドに対して選択性と平均フィールド・サイズの値を設定します。

QueryToTable() は、テーブルの定義の作成と、新しいテーブルへのデータの生成の両方を行います。テーブル定義の作成のみを行いたい場合は、クエリの WHERE 節で、データがない行を選択する条件を指定します。例えば、WHERE Age < 20 AND Age > 20 のようにします。

以下の例は、Sample.Person から Name フィールドと Age フィールドをコピーして、AVG(Age) フィールドを作成します。これらのフィールド定義を使用して、Sample.Youth という名前の新しいテーブルが作成されます。続いて、このメソッドは、Age < 21 であるレコードに対して Sample.Youth に Sample.Person データを生成します。AvgInit フィールドには、テーブル作成時に選択されたレコードの集約値が含まれます。

  DO $SYSTEM.SQL.QueryToTable("SELECT Name,Age,AVG(Age) AS AvgInit FROM Sample.Person WHERE Age < 21","Sample.Youth",1,.errors)

外部テーブル

Caché SQL には、“外部テーブル” を作成する機能もあります。外部テーブルとは、Caché ディクショナリで定義され、外部のリレーショナル・データベースに保存されるテーブルです。外部テーブルは、ネイティブの Caché テーブルであるかのように機能します。例えば、外部テーブルに対してクエリを発行し、INSERT、UPDATE、または DELETE 操作を実行できます。外部データベースには、Caché SQL ゲートウェイを使用してアクセスします。このゲートウェイは、ODBC または JDBC を使用した透過的な接続を提供します。詳細は、“Caché SQL ゲートウェイの使用法” を参照してください。

テーブルのリスト

INFORMATION.SCHEMA.TABLESOpens in a new tab 永続クラスは、現在のネームスペース内のすべてのテーブル (およびビュー) に関する情報を表示します。これは、スキーマ名、テーブル名、テーブルの所有者、新しいレコードを挿入できるかどうかなどのさまざまなプロパティを提供します。

以下の例は、現在のネームスペース内のすべてのテーブルとビューについて、テーブル・タイプ、スキーマ名、テーブル名、および所有者を返します。

SELECT Table_Type,Table_Schema,Table_Name,Owner FROM INFORMATION_SCHEMA.TABLES

管理ポータル SQL インタフェースの [カタログの詳細] タブを使用して、1 つのテーブルについて、INFORMATION.SCHEMA.TABLESOpens in a new tab とほぼ同じ情報を表示できます。

列の名前と番号のリスト

指定したテーブルのすべての列名 (フィールド名) をリストできる方法として、次の 3 つがあります。

  • GetColumns()Opens in a new tab メソッド。これは、非表示の列を含め、すべての列名および列番号をリストします。

  • 管理ポータルの SQL インタフェース (System, SQL) のスキーマ・コンテンツの [カタログの詳細] タブ。これは、すべての列名と列番号 (非表示の列を含む)、およびその他の情報 (データ型、および列が非表示かどうかを示すフラグなど) をリストします。

  • SELECT TOP 0 * FROM tablename。これは、非表示でないすべての列の名前を列番号順にリストします。非表示の列はどこでも列番号順に表示できるため、非表示でない列の名前を数えることで列番号を特定することはできないことに注意してください。アスタリスク構文の詳細は、"SELECT" コマンドを参照してください。

GetColumns() メソッド

テーブル内の列の名前を列番号順にリストするには、GetColumns()Opens in a new tab メソッドを次のように使用します。

  SET stat=##class(%SYSTEM.SQL).GetColumns("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 "GetColumns() cannot locate specified table" }

GetColumns() は、非表示の列を含め、定義されているすべての列をリストします。

このメソッドを次のように使用して、指定した列名の列番号を特定することもできます。

  SET stat=##class(%SYSTEM.SQL).GetColumns("Sample.Person",.byname)
  IF stat=1 {
         WRITE "Home_State is column number ",byname("Home_State"),!  }
  ELSE { WRITE "GetColumns() cannot locate specified table" }
FeedbackOpens in a new tab