条件に該当するデータが存在しない時だけ登録するSQL

条件に該当するデータが存在しない時だけ登録するSQL

insert into [テーブル名] (フィールド名)
select 登録する値
where NOT EXISTS (select 1 from [テーブル名] where 条件)

例1:テーブル「tbl1」のフィールド「f1」に「テストデータ」がなければ、「テストデータ」を登録する

insert into tbl1 (f1)
select 'テストデータ'
where NOT EXISTS (select 1 from tbl1 where f1='テストデータ')

例2:テーブル「tbl1」のフィールド「f1」に「テストデータ」がなければ、フィールド「f2」に「ダミーデータ」を登録する

insert into tbl1 (f2)
select 'ダミーデータ'
where NOT EXISTS (select 1 from tbl1 where f1='テストデータ')

FirebirdでRows句を利用して指定した範囲の行を取得する

Firebirdでは、指定した件数のみ取得したいときはROWS句を使用します。

SELECT <columns> FROM ... ROWS 取得件数

たとえば次のように記述します。

SELECT * FROM t1 ROWS 3;

上記の場合、先頭の行から3行目までを取得します。

指定した範囲の行を取得するときは ROWS ~ TO ~ を使用します。

SELECT * FROM t1 ROWS 開始位置 TO 終了位置

たとえば次のように記述します。

SELECT * FROM t1 ROWS 3 TO 5

上記の場合、3行目から5行目までの3行を取得します。

ROWS句はUPDATE文やDELETE文にも使用できます。

UPDATE t1 SET f1=100 ROWS 3 TO 5

DELETE FROM t1 ROWS 3 TO 5

LibreOffice 4.2のBaseでFirebird Embeddedが使用可能になりました。

LibreOffice 4.2のBaseでFirebird Embeddedが使用可能になりました。

初期設定では使用できませんので、次のように設定を変更します。

メニューの「ツール」→「オプション」を選択します。

001

「LibreOffice」→「詳細」→「実験的な機能を有効にする」をチェックします。

002

設定を変更した後、Baseデータベースを新規に作成すると、

003

内蔵データベースのプルダウンメニューで「Firebird Embedded」を選択できるようになります。

004

追記

LO Embedded Firebird/HSQLDB performance comparison | AHunt.org」によると、HSQLDBよりもFirebirdのほうがかなり高速のようです。
HSQLDBが遅いのはJava/C++間の変換によるところが大きいと考えられるとのこと。

graph_firebirdhsqldb
グラフは「LO Embedded Firebird/HSQLDB performance comparison | AHunt.org」より引用。

追記

SQLiteを使う案もあったが、以下の理由で採用されなかった。
(「Bug 38811 – default to SQLite not HSQLDB in Base」)

  • SQLiteは機能が貧弱。標準SQLの多くの機能が不足している。
  • SQLiteはデータ型が弱い。日時型がない。

FirebirdのINSERT文で追加されたIDを取得するには

Firebird 2.0以降では、INSERT文にRETURNING句を加えることで、追加されたIDを取得できます。

INSERT INTO t1 (id, ...) VALUES (next value for gen_t1, ...) RETURNING id;

DelphiのTIBSQLでIDを取得するには次のようなコードになります。

IBSQL1.SQL.Text := 'INSERT INTO t1 (id, ...) VALUES (next value for gen_t1, ...) RETURNING id';
IBSQL1.ExecQuery;
ID := IBSQL1.FieldByName('id').AsInteger;
IBSQL1.Close;

DelphiでFireDacを使用する場合は次のようなコードになります。

FDQuery1.SQL.Text := 'INSERT INTO t1 (id, ...) VALUES (next value for gen_t1, ...) RETURNING id1';
FDQuery1.Open;
ID := FDQuery1.Fields[0].Value;

Windows 8の64bit版にFirebirdをインストールする

Windows 8 64bit版にFirebird 2.5.2 64bit版をインストールして、開発環境を整える手順をまとめました。

firebirdDOWNLOADSのページからインストーラをダウンロードします。
64bit版のWindows8を使っているので、64bit用のFirebirdをダウンロードしました。

ダウンロードしたインストーラを実行します。

Firebird-2.5.2.26539_0_x64.exe

インストール時の設定は次のようにしました。

インストールの設定

  • Run the Firebird server as

    開発時のみ起動するようにしたかったので「Run as an Application」にしました。

  • Start Firebird automatically every time you boot up

    こちらも開発時のみ起動するようにしたかったのでチェックを外しました。

  • Install Control Panel Applet

    不要なのでチェックを外しました。

  • Copy Firebird client library to directory

    チェックするとシステムディレクトリにクライアントライブラリをコピーします。

    すでに異なるバージョンのFirebirdがインストールされている場合はライブラリを上書きすることになるので、問題が発生するかもしれません。
    Firebirdがインストールされている場合はこのチェックを外して、システムディレクトリではなくアプリケーションのフォルダーにライブラリをコピーします。

    Firebirdはインストールされておらず複数バージョンをインストールする予定もないので、チェックしました。

  • Generate client library as GDS32.DLL for legacy app. support

    Delphiで作られている多くのアプリケーションでは、GDS32.DLLという名前でクライアントライブラリを使用します。
    チェックするとGDS32.DLLという名前でクライアントライブラリを作成します。

    もちろんチェックします。

インストールが完了したら、「Firebird Guardian」のリンクを修正します。
アイコンを右クリックしてファイルの場所を開きます。

Firebird Guardianファイルの場所を開く

「Firebird Guardian」のプロパティを開き、リンク先の末尾にある空白を削除します。
末尾に空白があると「Firebird Guardian」を起動できないようです。

Firebird Guardianのプロパティ

Firebird Guardianとisqlを起動します。

SQL> connect "C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\employee.
fdb" user sysdba password masterkey;
Statement failed, SQLSTATE = 28000
cannot attach to password database

接続できませんでした。

Firebird Guardianを終了して、管理者として実行します。

SQL> connect "C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\employee.fdb" user sysdba password masterkey;
Server version:
WI-V2.5.2.26539 Firebird 2.5
WI-V2.5.2.26539 Firebird 2.5/XNet (WIN8PRO)/P12
WI-V2.5.2.26539 Firebird 2.5/XNet (WIN8PRO)/P12
Database:  "C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\employee.fdb", User: sysdbab", User: sysdba

今度は接続できました。

Firebird用の管理ツール「FlameRobin」をダウンロードして実行します。
やはり、Firebird Guardianを管理者権限で起動しないと接続できないようです。

IBConsole 日本語版+αをダウンロードします。
FlameRobinと同様、Firebird用の管理ツールです。日本語を安心して使用できます。

IBConsole 日本語版+αの64bit版を使う場合は、Firebirdをインストールしたフォルダーのbinフォルダーにある「fbclient.dll」をIBConsole 日本語版+αのフォルダーにコピーして、ファイル名を「ibclient64.dll」に変更します。

IBConsole 日本語版+αの32bit版を使う場合は、FirebirdをインストールしたフォルダーのWOW64フォルダーにある「fbclient.dll」をIBConsole 日本語版+αのフォルダーにコピーして、ファイル名を「gds32.dll」に変更します。

IBConsole 日本語版+αの64bit版・32bit版のどちらでも、64bit用Firebirdに接続できました。

以上で、Firebirdを使う準備ができました。

Firebirdデータベースをgbakでバックアップ・リストアする

Firebirdデータベースをgbakでバックアップ・リストアする方法。
よく使うものだけを抜粋。

(1)バックアップ

基本的な使い方

gbak -B -USER ユーザー名 -PASSWORD パスワード バックアップするデータベースの名前 バックアップ先ファイル

例:バックアップする

gbak -B -USER sysdba -PASSWORD masterkey data.fdb data.fbk

-V(-VERIFY)スイッチは、詳細な情報をスクリーンに表示する。
-yスイッチを使用するとログファイルに出力する。

例:詳細な情報を表示してバックアップする

gbak.exe -B -VERIFY -USER sysdba -PASSWORD masterkey data.fdb data.fbk

例:詳細な情報をログファイルに出力してバックアップする

gbak.exe -B -VERIFY -Y backup.log -USER sysdba -PASSWORD masterkey data.fdb data.fbk

(2)リストア

バックアップしたファイルから新しいデータベースを作成する

gbak -C バックアップしたファイル リストアするデータベースの名前

gbak -C data.fbk data.fdb

既存のファイルを置き換える、または新しく作成する

gbak -R バックアップしたファイル リストアするデータベースの名前

gbak -R data.fbk data.fdb

-V(-VERIFY)スイッチは、詳細な情報をスクリーンに表示する。
-yスイッチを使用するとログファイルに出力する。

gbak -R -V data.fbk data.fdb

Firebirdのテーブルやフィールドにコメントをつける

FirebirdのCOMMENT文を使用すると、テーブルやフィールドにコメントをつけることができます。

COMMENT文の構文はこちら

使用例

create table USERS (
  ID integer primary key,
  NAME varchar(100),
  BIRTHDAY timestamp
);
comment on table USERS is 'USERS TABLE';
comment on column USERS.ID is 'USER ID';
comment on column USERS.NAME is 'USER NAME';
comment on column USERS.BIRTHDAY is 'USER BIRTHDAY';

テーブルのコメントはRDB$RELATIONSテーブルのRDB$RELATION_NAMEに、
フィールドのコメントはRDB$RELATION_FIELDSテーブルのRDB$RELATION_NAMEに
登録されます。

DEKOさんのIBConsoleを使うと、登録したコメントが表示されます。(編集も可能)

FirebirdでCheck制約に正規表現を使用する

Firebird 2.5ではCheck制約に「SIMILAR TO」で正規表現を使用することができます。

正規表現に使用できるメタ文字

[ ] ( ) | ^ - + * % _ ? {

create table tbl1 (
  /* hat、cat、bat のような三文字の文字列にマッチする */
  col1 varchar(10) check(col1 SIMILAR TO '_at'),
  /* "[hc]at" は hat と cat にマッチする */
  col2 varchar(10) check(col2 SIMILAR TO '[hc]at'),
  /* "[^b]at" は bat 以外の ".at" でマッチする全ての文字列にマッチする */
  col3 varchar(10) check(col3 SIMILAR TO '[^b]at'),
)

正規表現に使用できる文字クラス

[:ALPHA:]
[:DIGIT:]
[:ALNUM:]
[:UPPER:]
[:LOWER:]
[:SPACE:]
[:WHITESPACE:]

create table tbl2 (
  /* アルファベットで構成されている文字にマッチする */
  col1 varchar(20) check(col1 SIMILAR TO '[[:ALPHA:]]+'),
  /* 数字で構成されている文字にマッチする */
  col2 varchar(20) check(col2 SIMILAR TO '[[:DIGIT:]]+'),
  /* 4~8以外の数字で構成されている文字にマッチする */
  col3 varchar(20) check(col3 SIMILAR TO '[[:DIGIT:]^4-8]+'),
)

Firebirdのunion演算子の結果に対してorder byで並び替える

Firebird 2.5で、union演算子の結果に対してorder byで並び替えるために、次のようなSQLを書いたらエラーになった。

select C1, C2, C3 from T1
union all 
select C1, C2, C3 from T2
order by C1

解決策は、次のように書く。

select C1,C2,C3
from (
  select C1, C2, C3 from T1
  union all 
  select C1, C2, C3 from T2
)
order by C1

FirebirdでCURRENT_TIMESTAMPを使ってTIMESTAMP型の列のデフォルト値に現在の日時に指定する

CURRENT_TIMESTAMPは現在の日時をTIMESTAMP型で返します。

select CURRENT_TIMESTAMP from rdb$database

テーブルを定義する時に列のデフォルト値としてCURRENT_TIMESTAMPを指定できます。

列名 TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL

テーブルの作成例

CREATE TABLE tbl1 (
  id INT PRIMARY KEY,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
)