データ定義言語（DDL）

Table of Contents

Quick Links

バージョン2.5では、DDLに対して重要な追加と拡張が施されました。

Quick Links

クラシックサーバーでのプロシージャ定義変更の可視性
CREATE/ALTER/DROP USER
ビューを変更する構文
CREATE VIEWの拡張
計算項目に対するALTERの仕組み
SQLパーミッションの拡張
データベースのデフォルトのコレーション属性
ALTER CHARACTER SET コマンド
CREATE DATABASEの進化

このリリースではFirebird 3を目指したアーキテクチャ変更が強調されていますが、多数の改善や拡張も実装されています。その多くはトラッカーでの機能要求に応えたものです。

一般的警告

バージョン2.5以上では、カラムのデータ型の変更が可能になっています。そのカラムがストアドプロシージャやトリガで参照されていたとしても、例外がスローされることはありません。ただし、コンパイル済みのPSQLはBLOB内でバイナリ表現（“BLR”）として静的に格納されているため、オリジナルのBLRはバックアップやリストアを行なっても残っています。BLRは静的であるため、データ型を変更しても更新されません。

このことは、テーブルやそのビューから影響を受けるカラムを参照しているBLRが、それらのカラムを参照するTYPE OF構文を使って任意の変数を宣言している場合、警告なしに破壊される可能性が高いことを意味します。最善の場合、BLRは“注意が必要”のフラグを付けられますが、テストでは、どんな条件でもフラグ付けが行なわれるわけではないことが分かっています。

つまり、フィールドがコンパイル済みPSQLに何らかの依存関係を持っていたとしても、あなたがそのフィールドの型を変更するのをエンジンはもう止めてくれなくなった、ということです。変更された状況を管理するため、影響を受けるプロシージャやトリガを特定したり、変更に対応してそれらを再コンパイルしたりといった対応は、あなた自身の問題となります。

クラシックサーバーでのプロシージャ定義変更の可視性

Dmitry Yemanov

トラッカー・リファレンス CORE-2052

一件の変更により、クラシックサーバーで、変更されたストアドプロシージャに関する他のクライアントの接続からの可視性の問題への対処が行われました。変更中のトランザクションがコミットを完了するとすぐに、その変更がサーバー全体で確認できるようになりました。

CREATE/ALTER/DROP USER

Alex Peshkov

トラッカー・リファレンス CORE-696

バージョン2.5で、Firebirdはついに、通常のデータベースへのログイン時にSQL文を発行することでサーバーのユーザーアカウントを管理できる構文を手に入れました。

CREATE USERとALTER USER文はいずれもパラメータGRANT ADMIN ROLEとREVOKE ADMIN ROLEをを持ち、SYSDBA権限を持つユーザーがセキュリティ・データベースのロールRDB$ADMINを通常のユーザーに付与することができます。この使い方の解説とロールRDB$ADMINの全容については、管理機能の章の新しいシステムロールRDB$ADMINの項を参照して下さい。

構文パターン

SYSDBAまたはSYSDBA権限を持つユーザーは、現在のデータベースとセキュリティ・データベースのいずれにも新しいユーザーを追加できます：

CREATE USER <ユーザー名> {PASSWORD 'パスワード'}
  [FIRSTNAME 'ファーストネーム']
  [MIDDLENAME 'ミドルネーム']
  [LASTNAME 'ラストネーム']
  [GRANT ADMIN ROLE];

Note

新しいユーザーを作成する際はPASSWORD句が必要です。これは新しいユーザーの初期パスワードとなります。ユーザーが後からこれを変更するにはALTER USERを使います。

SYSDBAまたはSYSDBA権限を持つユーザーは、現在のデータベースとセキュリティ・データベースのいずれでも、既存のユーザーのパスワードや固有名詞属性を変更できます。権限がないユーザーは、同じ文を使うことで自分自身の属性のみ変更できます：

ALTER USER <ユーザー名>
  [PASSWORD 'パスワード']
  [FIRSTNAME 'ファーストネーム']
  [MIDDLENAME 'ミドルネーム']
  [LASTNAME 'ラストネーム']
  [{GRANT | REVOKE} ADMIN ROLE];

Note

少なくとも一つのオプションパラメータを与える必要があります。

ALTER USERでは<ユーザー名>の変更はできません。別の<ユーザー名>に変更したい場合は、もとのユーザーを削除し新たなユーザーを作成しなくてはなりません。

SYSDBAまたはSYSDBA権限を持つユーザーは、現在のデータベースとセキュリティ・データベースのいずれでも、ユーザーを削除することができます：

DROP USER <ユーザー名>;

制限事項

現在のデータベースとセキュリティー・データベースのいずれでも、CREATE USERやDROP USER文とGRANT | REVOKE ADMIN ROLEを使えるのはSYSDBAまたはロールRDB$ADMINを手にしているユーザーだけです。

通常のユーザーは自身のパスワードや固有名詞の要素をALTERすることができます。別のユーザーの修正を試みても失敗します。

例

SYSDBAまたは同等の権限を持つユーザーは、現在のデータベースでもセキュリティ・データベースでも、以下のことができます：

CREATE USER alex PASSWORD 'test';

ALTER USER alex FIRSTNAME 'Alex' LASTNAME 'Peshkov';

ALTER USER alex PASSWORD 'IdQfA';

ビューを変更する構文

Adriano dos Santos Fernandes

従来は、ビュー定義を変更するためには、ビュー定義をオフラインのどこかに保存してからビューを削除し、その上で再作成するする必要がありました。これは、特に依存関係の問題がある場合には非常に煩わしいものでした。そこで、バージョン2.5では、ALTER VIEWとCREATE OR ALTER VIEWの構文が導入されました。

トラッカー・リファレンス CORE-770 および CORE-1640

ALTER VIEW

ALTER VIEWはビュー定義の変更を可能にします。ビューの古いバージョンと全ての依存関係を再作成（削除して作成）する必要はありません。

CREATE OR ALTER VIEW

CREATE OR ALTER VIEWを使うと、ビューが存在する場合はビュー定義が変更されます（ALTER VIEWと同様）。存在しない場合はビューが作成されます。

構文パターン

{create [ or alter ] | alter } view <ビュー名>
  [ ( <フィールドリスト> ) ]
as <select文>{

例

create table users (
    id integer,
    name varchar(20),
    passwd varchar(20)
);

create view v_users as
    select name from users;

alter view v_users (id, name) as
    select id, name from users;

CREATE VIEWの拡張

CREATE VIEWに以下のような拡張が追加されました。

FROM句にストアドプロシージャを指定

Adriano dos Santos Fernandes

トラッカー・リファレンス CORE-886.

ビュー定義のFROM句に選択型ストアドプロシージャを指定できるようになりました。

例

    create view a_view as
    select * from a_procedure(current_date);

カラムリストを含まないUNION句を用いたビューの作成

Dmitry Yemanov

トラッカー・リファレンス CORE-1402

セットがUNION句で定義されている場合、CREATE VIEWからカラムリストを省略できるようになりました。

例

 recreate view V1 as
   select d.rdb$relation_id from rdb$database d
   union all
   select d.rdb$relation_id from rdb$database d

 recreate view V2 as
   select d.rdb$relation_id as q from rdb$database d
   union all
   select d.rdb$relation_id as w from rdb$database d

推定されたカラム名

Adriano dos Santos Fernandes

トラッカー・リファレンス CORE-2424

CREATE VIEWが、GROUP BY句を含むビューまたは派生テーブルのカラム名を推定できるようになりました。

例

 create view V as 
   select d.rdb$relation_id from rdb$database d 
     group by d.rdb$relation_id

 create view V as 
    select a from (select 1 a from rdb$database);

計算項目に対するALTERの仕組み

Adriano dos Santos Fernandes

トラッカー・リファレンス CORE-1454

COMPUTED BY <式>と定義されたカラムをALTER TABLE...ALTER COLUMN構文を使って変更できるようになりました。この機能はカラム定義の<式>要素を異なる式に変更するためだけに使われます。計算項目を非計算項目に、またその逆の変換を行うことはできません。

構文パターン

alter table <テーブル名>
  alter <計算項目名>
  [type <データ型>]
  COMPUTED BY (<式>);

例

create table test (
  n integer,
  dn computed by (n * 2)
);
commit;
alter table test
  alter dn computed by (n + n);

SQLパーミッションの拡張

Alex Peshkov

SQLパーミッション（権限）のエリアに以下の拡張が実装されました。

GRANTED BY句

GRANT文とREVOKE文にオプションでGRANTED BY（またはAS）句を含めることで、CURRENT_USER（デフォルト）以外のユーザーを権限付与者にできるようになりました。

構文パターン

grant <権限> to <オブジェクト>
  [ { granted by | as } [ user ] <ユーザー名> ]
--
revoke <権限> from <オブジェクト>
  [ { granted by | as } [ user ] <ユーザー名> ]g

Tip

{ granted by | as }

GRANTED BYとASは同じ働きをします。SQL標準で推奨される形式はGRANTED BYです。われわれは他のサーバー（例えばInformix）との互換性のためにASをサポートしています。

例

SYSDBAとしてログイン：

create role r1; -- SYSDBAがこのロールを所持しています
  /* 次に、SYSDBAはuser1に対し、他のユーザーに同じロールを
     付与する権限を付けてこのロールを付与します */
grant r1 to user1 with admin option;
  /* SYSDBAがGRANTED BY句を使ってuser1の
     ADMIN OPTIONを行使します */
grant r1 to public granted by user1;

この効果はisqlでも得られます：

  SQL>show grant;
  /* このデータベースに対するを付与します */
  GRANT R1 TO PUBLIC GRANTED BY USER1
  GRANT R1 TO USER1 WITH ADMIN OPTION
  SQL>

ALTER ROLE

トラッカー・リファレンス CORE-1660

新しいALTER ROLE文の機能は、WindowsのAdministratorsに対する“信頼された認証”を通じたSYSDBAパーミッション割り当ての制御に特化しています。現在これに他の用途はありません。

Note

ALTER ROLEの使い方の解説とRDB$ADMINの全容については、管理機能の章の新しいシステムロールRDB$ADMINの項を参照して下さい。

REVOKE ALL

トラッカー・リファレンス CORE-2113

ユーザーがセキュリティ・データベースまたは他の認証ソース（OSのACLなど）から削除される時、データベース内で関連するSQL権限のクリーンアップは手動で行う必要があります。この拡張により、特定のユーザーやロールの全ての権限を一度に取り消すことができるようになりました。

構文パターン

REVOKE ALL ON ALL FROM { <ユーザーリスト> | <ロールリスト> }

例

SYSDBAとしてログイン：

# gsec -del guest
# isql employee
fbs bin # ./isql employee
Database:  employee
SQL> REVOKE ALL ON ALL FROM USER guest;
SQL>

データベースのデフォルトのコレーション属性

Adriano dos Santos Fernandes

トラッカー・リファレンス CORE-1737 および CORE-1803

ODS 11.2以上のデータベースは、デフォルトのキャラクタ・セットに関連付けた、デフォルトのCOLLATION属性を持つことができ、異なるコレーション用のCOLLATE句が指定されていない限り、全てのテキスト・カラム、ドメイン、変数の定義を同じコレーションオーダーで作成できます。

COLLATION句はオプションです。これが省略された場合は、キャラクタ・セット用のデフォルトのコレーションオーダーが使われます。

Tip

ALTER CHARACTER SET用の構文が導入されたことで、データベースで使われるキャラクタ・セット用のデフォルトのコレーションオーダーも変更可能になっていることに注意して下さい。

構文パターン

create database <ファイル名>
    [ page_size <ページサイズ> ]
    [ length = <長さ> ]
    [ user <ユーザー名> ]
    [ password <ユーザーパスワード> ]
    [ set names <charset名> ]
    [ default character set <charset名>
       [ [ collation <コレーション名> ] ]
    [ difference file <ファイル名> ]

Note

CREATE DATABASE用のパラメータDIFFERENCE FILEは新しいものではありません。これは、バージョン2.0で、nBackupユーティリティに関連して静かに導入されており、ドキュメント化も三年間でひっそりと行われていました。詳細は、この章の終わりにあるCREATE DATABASEの進化をご覧ください。

例

create database 'test.fdb'
    default character set win1252
    collation win_ptbr;

ALTER CHARACTER SET コマンド

Adriano dos Santos Fernandes

トラッカー・リファレンス CORE-1803

このバージョンでは新しい構文が導入され、データベースにキャラクタ・セット用のデフォルトのコレーションを設定できるようになりました。

デフォルトのコレーションが使われるのは、（カラムまたはドメイン定義中のCHARACTER SET句を通じて明示的に、またはデータベースのデフォルトのキャラクタ・セット属性を通じて暗に）指定されたキャラクタ・セットを持つテーブルのカラムが作成され、そして、いかなるCOLLATION句も指定されていない場合となります。

Note

文字列定数も、接続キャラクタ・セットのデフォルトのコレーションを使います。

既存のカラムのキャラクタ・セットとコレーションはALTER CHARACTER SETの変更によっては影響を受けません。

構文パターン

ALTER CHARACTER SET <charset名>
    SET DEFAULT COLLATION <コレーション名>

例

create database 'people.fdb'
  default character set win1252;

alter character set win1252
  set default collation win_ptbr;

create table person (
  id integer,
  name varchar(50)  /* データベースのデフォルトの
                       キャラクタ・セットとwin1252の
                       デフォルトのコレーションを使います */
);

insert into person
  values (1, 'adriano');
insert into person
  values (2, 'ADRIANO');

/* win_ptbrは大文字小文字を区別しないので、
   両方のレコードの取得します */
select * from person where name like 'A%';

Tip

また別の改善により、システムテーブルRDB$CHARACTER_SETS内のRDB$DEFAULT_COLLATE_NAMEの現在の値がバックアップ／リストアのサイクルの中で維持されるようになりました。

CREATE DATABASEの進化

nBackupの状態の登録と変更を行うために導入されたデータベースのヘッダ属性に対するDDLのサポートは、Firebird 2.0以来、進化を続けています。nBackupのユーザーには、完全なnBackupの間にメインのデータベースとは別のファイルに“増分”データの格納を開始、終了するALTER DATABASE文はお馴染みとなっています。

nBackup用の増分ファイルの命名

ALTER DATABASEはまたもう一つの引数を持ち、これによって増分データを格納するファイルに使われる名前を設定することができます。Paul Vinkenoog氏によるnBackupの素晴らしいマニュアルを引用すれば：

デフォルトでは、増分ファイルはデータベース自身と同じディレクトリ内に置かれます。このファイルには、データベースファイルと同じ名前に.deltaを加えた名前が付けられます。通常これを変更する理由はありませんが、必要なら変更できます。—ただし、それをnbackupから行うことはできません。

任意のクライアントからデータベースに接続したら、あなた自身でSQL文を入力し、コマンドを打って下さい：

alter database
  add difference file 'パスとファイル名'

カスタムの増分ファイル指定は、データベース内で保持されます；これはシステムテーブルRDB$FILESに格納されます。

デフォルトの挙動に戻すには、次のSQL文を発行して下さい：

alter database
  drop difference file

さらに詳しく知りたい方は、バージョン2.0のリリースノートまたはnBackupのマニュアルをお読み下さい。

CREATE DATABASEの引数DIFFERENCE FILE

C. Valderrama

Firebird 2.0で、増分ファイルのカスタム名を規定する構文は、CREATE DATABASEの追加のオプション引数として登場しました。データベースのデフォルトCOLLATION属性の項の上記の構文パターンの中でその配置を確認することができます。ALTER DATABASEの場合と同様に、引数用のキーワードはDIFFERENCE FILEであり、引数は有効なファイル指定となります。ALTER DATABASE BEGIN BACKUPが呼び出される場合、または、同等のnBackupのシェルコマンドが呼び出される場合はいつでも、作成される増分ファイルのカスタム名を指定できます。

使い方の例

  ]..\bin> isql -user sysdba -pass masterkey

 Use CONNECT or CREATE DATABASE to specify a database
 SQL> create database 'ticks' difference file 'jaguar';
 SQL> shell dir jaguar;
 Volume in drive F is Firebird
 Volume Serial Number is BCD9-4211

 Directory of ..\bin

 File Not Found

これは正しく、ただファイル名が定義されただけです。次にこれを使います：

 SQL> alter database begin backup;
 SQL> shell dir jaguar;
 Volume in drive F is Firebird
 Volume Serial Number is BCD9-4211

 Directory of ..\bin

 10-11-2009  00:59             8.192 jaguar
                1 File(s)          8.192 bytes
                0 Dir(s)  16.617.979.904 bytes free

 SQL> alter database end backup;
 SQL> shell dir jaguar;
 Volume in drive F is Firebird
 Volume Serial Number is BCD9-4211

 Directory of ..\bin

 SQL> drop database;
 SQL> ˆZ

引数がファイル名なので、シングルクォートで囲います。ダブルクォートは無効となります：SQL文は失敗し、煩雑なエラーメッセージが返されます。

 ]..\bin> isql -user sysdba -pass masterke

 Use CONNECT or CREATE DATABASE to specify a database
 SQL> create database 'ticks' DIFFERENCE FILE 'jaguar';
 SQL> alter database add difference file 'leopard';

 Statement failed, SQLCODE = -607
 unsuccessful metadata update
 -Difference file is already defined

このメッセージは正しいです。呼び出しALTER DATABASE END BACKUPによって増分が削除されたとしても、差分ファイルの名前はずっと格納されています。なお、存在して良いのは一つだけです。

 SQL> alter database drop difference file;
 SQL> alter database begin backup;

これは何も破壊したことになりません。エンジンがこの事態を救い、デフォルトの仕組みを使って増分を作成するためです。

 SQL> alter database add difference file 'leopard';
 SQL> alter database begin backup;
 SQL> alter database drop difference file;

 Statement failed, SQLCODE = -607
 unsuccessful metadata update
 -Cannot change difference file name while database is in backup mode

これは正しい検証です。

 SQL> alter database end backup;
 SQL> drop database;
 SQL> ˆZ