ぽにょろん

思いついたこととメモ

Firebirdでテーブル状態に合わせてINSERTとUPDATEを実行する

実は過去に、こんな記事を書いていました。
ponyoth.hateblo.jp

上記のFirebird版になります。
データ更新の際に、すでに該当行があればUPDATE、なければINSERTする単純な例です。

ただし、Firebirdには「UPDATE OR INSERT」という構文があります。
そのため、「MERGE」と「UPDATE OR INSERT」を並記します。

目次

検証環境

テスト用テーブルの作成

create table MergeTest(
  Id int unique not null,
  Memo varchar(200)
);

insert into MergeTest values(1, 'test1');
insert into MergeTest values(2, 'test2');
insert into MergeTest values(3, 'test3');
insert into MergeTest values(4, 'test4');

f:id:kowill:20160613170435p:plain

「MERGE」の場合

merge into MergeTest as t
using (select 4 Id, 'change' Memo from MergeTest) as s
  on (t.Id = s.Id)
when matched then
  update set t.Memo = s.Memo
when not matched then
  insert (Id, Memo) values(s.Id, s.Memo);

merge into MergeTest as t
using (select 5 Id, 'test5' Memo from MergeTest) as s
  on (t.Id = s.Id)
when matched then
  update set t.Memo = s.Memo
when not matched then
  insert (Id, Memo) values(s.Id, s.Memo);

リファレンスはこちら。
MERGE

「UPDATE OR INSERT」の場合

update or insert into MERGETEST (ID, MEMO) values (4, 'change') matching (ID);
update or insert into MERGETEST (ID, MEMO) values (5, 'test5') matching (ID);

リファレンスはこちら。
UPDATE OR INSERT

結果

f:id:kowill:20160613170440p:plain

「MERGE」は他のRDBMSと同じように書けます。
今回の目的の場合は、「UPDATE OR INSERT」の方が分かりやすそうですね。

AvalonEditをTabControlでつかう

自作ツールのTabControlのContentにAvalonEditを導入しようとしたところ、
思いの外苦戦したので記録しておきます。

目次

環境

  • C# (.NET Framwork 3.5)
  • WPF
  • VS2015
  • AvalonEdit version="5.0.3"

問題点

  • TabControlでTabを切り替えた時に、AvalonEdithへの入力値が引き継がれてしまう
    XAMLでTabItemを明示的に記述した場合は再現しない。
    ContentTemplateのDataTemplateを使って動的にタブを制御すると再現する。

あぁ、そういうことかって感はありますよね。

  • イメージ
    f:id:kowill:20160614164154p:plain

解決

わりと無理やりですが、TextプロパティをBindして、書き換えるようにしました。
こんな感じのコントロールを作ってしまいます。

gist.github.com

f:id:kowill:20160614165548p:plain

何か見落としている気がしないでもないですけど、意図した事ができるようになったので良し。
ちゃんとした解決法知っている人がいたら教えてください。

AvalonEditを使って、SQL文にSyntaxHighlightつけたい

自作ツールにFirebirdSQL文を入力した際に、SyntaxHighlightできるようにしたい!
ということで、AvalonEditを使ってみました。

目次

環境

AvalonEditとは

AvalonEdit by icsharpcode

WPFベースのテキストエディタコンポーネントです。
SharpDevelop というOSSIDEテキストエディタとして使われているものです。
そのため、SyntaxHighlight のみならずコード補完等も出来るようです。

AvalonEditの詳しい解説は下記のDocumentを参照。
* AvalonEdit - Table of Content

実装

ライブラリの取得

公式ページにあるように nuget から Install 可能です。

Install-Package AvalonEdit

エディタの配置

名前空間設定して、TextEditorを配置すればよし。

<Window x:Class="AvalonEditSample.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:avalon="http://icsharpcode.net/sharpdevelop/avalonedit"
        xmlns:local="clr-namespace:AvalonEditSample"
        mc:Ignorable="d"
        Title="MainWindow" Height="350" Width="525">
    <avalon:TextEditor />
</Window>

SyntaxHighlight の設定

AvalonEdit では、SyntaxHighlightingプロパティで指定できる言語がいくつかあります。
C#, PowerShell, HTML, CSS, VB, C++, Java ,その他諸々が対応しています。
以下のコードを確認すると、対応状況が分かります。

AvalonEdit/Resources.cs at master · icsharpcode/AvalonEdit · GitHub

AvalonEdit/ICSharpCode.AvalonEdit/Highlighting/Resources at master · icsharpcode/AvalonEdit · GitHub

C#の設定をする場合はこんな感じでOKです。

<avalon:TextEditor SyntaxHighlighting="C#"/>  

SyntaxHighlight のカスタマイズ

下記に従って、任意の設定が可能です。
AvalonEdit - Table of Content

適当にFirebirdのキーワードを突っ込んだ xshdファイルを作成します。

<SyntaxDefinition name="SQL" extensions=".sql" xmlns="http://icsharpcode.net/sharpdevelop/syntaxdefinition/2008">
  ...
  <Color name="Keyword" foreground="#FF0000FF" fontWeight="bold" exampleText="SELECT" />
    <Keywords color="Keyword">
      ...
      <Word>INDEX</Word>
      <Word>INNER</Word>
      <Word>INPUT_TYPE</Word>
      <Word>INSENSITIVE</Word>
      <Word>INSERT</Word>
      <Word>INSERTING</Word>
      <Word>INT</Word>
      <Word>INTEGER</Word>
      <Word>INTO</Word>
      <Word>IS</Word>
      <Word>ISOLATION</Word>
      <Word>JOIN</Word>
      <Word>KEY</Word>
      <Word>LAST</Word>
      ...

長いのでGistに上げました。
Firebird 用xshd · GitHub

SyntaxHighlight のカスタマイズ2

コードビハインドに、読み込み処理を書いておきます。

public MainWindow()
{
    InitializeComponent();
    using (var reader = new XmlTextReader("sql.xshd"))
    {
        Editor.SyntaxHighlighting = HighlightingLoader.Load(reader, HighlightingManager.Instance);
    }
}

結果

f:id:kowill:20160613130128p:plain

上記の通り、概ね思った通りになります。

ADO.NET provider 5.0.0.0 が出てた

FB3対応の「ADO.NET provider 5.0.0.0」がリリースされてました。

ADO.NET provider 5.0.0.0 for Firebird is ready | Jiří {x2} Činčura

Release Notes - .NET Data provider - Version 5.0.0.0 - Text format - Firebird RDBMS Issue Tracker

これで自前ビルドせず、NuGetから持ってくればよくなりました。

使用方法はこれまでと変わらず、以下の通りで可能です。

ponyoth.hateblo.jp

同一プログラムからFirebird2.5と3.0のDBにアクセスしたい

同一プログラムからFB3とFB2.5にアクセスするシナリオを考え、サンプルを作ってみました。

目次

環境

  • C# (.NET Framwork 4.5.2)
  • VS2015
  • Firebird .NET Data Provider (5a17a58)
  • Embedded

課題

  • DBにアクセスする前に、ファイルのODSバージョンを知りたい
  • FB2.5とFB3.0のDLLの共存

ODSバージョンの取得

Firebirdのデータベースファイルは一つのバイナリです。
ググった結果、このバイナリを読んで、ヘッダーページにアクセスすれば良さそうということが分かりました。

データベースファイルの内部構造を探ってみた(2/3) − @IT

ただ、情報が古そうなのでソースコードを探してみることに。

github.com

atmarkitの記事と相違はありそうですが、大体よさそう。

ということで、2パターン試してみた。

structを使うパターン

定義を写経して、

[StructLayout(LayoutKind.Sequential)]
public struct HeaderPage
{
    public Pag hdr_header;
    // Page size of database
    [MarshalAs(UnmanagedType.U2)]
    public ushort hdr_page_size;
    // Version of on-disk structure
    [MarshalAs(UnmanagedType.U2)]
    public ushort hdr_ods_version;
    // Page number of PAGES relation
    [MarshalAs(UnmanagedType.U4)]
    public uint hdr_PAGES;
    // Page number of next hdr page
    [MarshalAs(UnmanagedType.U4)]
    public uint hdr_next_page;
....

Marshalのメソッドをつくり、

public static T RawDataToObject<T>(byte[] rawData) where T : struct
{
    var pinnedData = GCHandle.Alloc(rawData, GCHandleType.Pinned);
    try
    {
        return (T)Marshal.PtrToStructure(pinnedData.AddrOfPinnedObject(), typeof(T));
    }
    finally
    {
        pinnedData.Free();
    }
}

ファイル読んで、完成。

private const ushort FirebirdFlag = 0x8000;
....

using (FileStream stream = new FileStream(path, FileMode.Open))
{
    var buf = new byte[1024];
    stream.Read(buf, 0, buf.Length);
    var header = RawDataToObject<HeaderPage>(buf);
    var odsVersion = header.hdr_ods_version & ~FirebirdFlag;
    Console.WriteLine($"ODSVer: {odsVersion}");
    Console.WriteLine($"ODSMinorVer: {header.hdr_ods_minor}");
}

必要なところだけ変換

private const ushort FirebirdFlag = 0x8000;
....
using (var fs = new FileStream(path, FileMode.Open, FileAccess.Read))
{
    int fileSize = (int)fs.Length;
    byte[] buf = new byte[1024];
    fs.Read(buf, 0, 1024);
    var obsHex = buf.Skip(0x12).Take(2).Select(x => x.ToString("X2")).Reverse().Aggregate("", (x, y) => x + y);
    var minor = buf.Skip(0x40).Take(2).Select(x => x.ToString("X2")).Reverse().Aggregate("", (x, y) => x + y);
    Console.WriteLine($"ODSVer: {Convert.ToInt32(obsHex, 16) & ~FirebirdFlag}");
    Console.WriteLine($"ODSMinorVer: {Convert.ToInt32(minor, 16)}");
}

比較

どちらでも行けますが、個人的にはstructでMarshallする方がよいのではと思ってます。*1
結局、定義とにらめっこしたり、リトルエンディアンの処理する必要がありますし。

注意点として、OdsVersionはそのままの値ではだめで、0x8000とのANDをとってあげる必要があります。

firebird/pag.cpp at B3_0_Release · FirebirdSQL/firebird · GitHub

※バイナリ眺めてるときにちゃんとやるなら上記みたいにやらないとだめだけど、
ODSも2桁だし、ODS_MINORも1桁だし普通にリトルエンディアンとか放置でも行けるじゃんって思いました。

FB3とFB2.5の共存

まず必要なDLLをプロジェクトに突っ込んでおきます。
f:id:kowill:20160425224959p:plain

あとは、FBConnectionのClientLibraryに使いたい方のDLLを指定してあげるだけ。

var builder = new FbConnectionStringBuilder();
builder.DataSource = "localhost";
builder.Database = @"D:\DB\TESTFB3.FDB";
builder.ServerType = FbServerType.Embedded;
builder.ClientLibrary = @"dll\fb3\fbclient";
....
var builder = new FbConnectionStringBuilder();
builder.DataSource = "localhost";
builder.Database = @"D:\DB\TESTFB2.FDB";
builder.ServerType = FbServerType.Embedded;
builder.ClientLibrary = @"dll\fb25\fbembed";
....

こうすれば特に問題なく共存が可能です。
Process.GetCurrentProcess().Modules で見てみるとこんな感じになります。
f:id:kowill:20160425225600p:plain

解決

これで課題は解決できたので、あとは必要に応じて処理を組み込めば、同一プログラムからの呼び出しは問題なしです。
与えられたDBファイルから判断して、処理を変えることも可能です。

実際に自分のプロジェクトでやろうとしていたんですが、諸事情でサスペンドしてますw
本当に問題がないのかは不明ですが、今回はこんな感じで終了です。

一応、サンプルを。

github.com

*1:使い方大丈夫なのだろうか

C#+FirebirdでExeの終了が遅かった件がFB3では再現しなくなっていた

以前記事にした以下の件、Firebird3.0で置き換え試したところ、
再現しなくなっていました。

ponyoth.hateblo.jp

ponyoth.hateblo.jp

Firebird2.5特有の問題なのか、Firebird3.0での修正に伴い解消されたのか。
謎ですが、一安心。

Firebird3.0でEmbedded試してみた

昨日(4月19日)、Firebird3.0(以下FB3)が正式にReleaseされました。

www.firebirdsql.org

せっかくなので、FB3でのEmbedded接続を試してみました。
C#です。

目次

今回試した環境

FB2.5からの大きな変更点

これまでWindows版ではEmbedded用のパッケージが別に用意されていましたが、
FB3からは一緒になったようです。

キムラデービーブログさんに詳しくあるので、そちらを参照してください。

blog.kimuradb.com

Firebirdのバイナリを取得

www.firebirdsql.org
上記から、目的のプラットフォームに合わせて取得。

必要なDLLをコピー

今回必要なものを以下の2つ。
* fbclient.dll
* plugins/engine12.dll

最終的に、以下のようなフォルダ階層になればOK。
f:id:kowill:20160421010106p:plain

ソースコード

gist.github.com

プロジェクトのプラットフォームターゲットの変更に注意。
x86バイナリなら、x86に。x64ならそのように。

実行結果

f:id:kowill:20160421010052p:plain

rank や boolean型を使えていることが確認できます。
Hello Firebird 3.0!完了!

注意事項

  • .NET Data Provider
    2016/4/20時点でNugetから取得した、FirebirdSql.Data.FirebirdClientは
    FB3から対応したBoolean型の型変換できずエラーになります。
    Window関数やその他DBの機能に関してはほとんど動きそうですが、
    Boolean型を試したい場合は、素直にCloneして自前で構築しましょう。*1

  • Firebird2.5で作成したDBについて
    ODS(On-Disk Structure)が変更になっているため、上記の手順で参照するとエラーになります。
    マイグレーションする必要があるようです。
    ツールとかで両バージョン参照したいとかは一工夫(?)いりそうですね。。

  • サポートライブラリ
    上記手順では、サポートライブラリ*2入れてませんが、
    入れておかないと、firebird.logに読み込み失敗ログが出ます。
    ちゃんとした環境で使うときは入れて下さい。

*1:masterをcloneしてくればOK

*2:icu*.dll, ib_util.dll