【postgresql】ECPG（埋め込みSQL）を体験する

postgresqlで利用できるECPG(埋め込みSQL)の基本的な部分を体験するまでを調べました。

埋め込みSQLについて

埋め込みSQLとは

C言語からSQLコマンドを扱う有効手段。
Embedded SQL in Cということで標準規格はあるようですがDB種類ごとに実装の有無や準拠具合は統一的ではないようです。

埋め込みSQLのソース種類

以下2種類が有名どころです。

• Oracleの場合pro*C (*.pc)
• PostgreSQLの場合ECPG (*.pgc)

つまり、OracleのProCの代替と言えば真っ先にPostgreSQLのECPGとなります。

埋め込みSQLのコンパイルの流れ

1. SQLプリプロセッサを通してプリコンパイル
   • 通常のCプログラム（*.c)に変換される
2. Cコンパイラにでコンパイル
   • データベースと通信可能なバイナリへ変換

PostgreSQLでの埋め込みSQL体験準備

OS環境

利用環境は、Hyper-Vクイックスタートから開始できる「Windows10開発環境」を利用します。クイックスタートから「仮想マシンの作成」を行うだけでOKです。

PostgreSQLのインストール

上記で用意した仮想マシンに接続します。この先はずっと仮想マシンでの操作となります。

今回はchocolatey（パッケージ管理ソフト）を入れ、そこからPostgresqlを入れていきます。

管理者モードでPowershellを起動し、下記コマンドからchocolateyをインストールします。詳細はこちらを参考にしてください。(chocolateyインストール）

手順に従いpowershellポリシーを変更

 Set-ExecutionPolicy AllSigned

「Do you want to change the execution policy?」と問われるので「y」とします。

そして、chocolateyインストールのおまじないを実行

Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))

「choco」と打ってバージョン番号が出れば成功です

続いて、下記コマンドを実行

choco install postgresql pgadmin4 -y

結果、今回の環境では、以下のパッケージがインストールされました。

Installed:
 postgresql v12.1
 kb2919355 v1.0.20160915
 kb3033929 v1.0.5
 chocolatey-core.extension v1.3.5.1
 kb2999226 v1.0.20181019
 pgadmin4 v4.15
 kb2919442 v1.0.20160915
 postgresql12 v12.1
 vcredist140 v14.24.28127.4
 kb3035131 v1.0.3
 chocolatey-windowsupdate.extension v1.0.4 

パスワード未指定の場合はインストール時に作成される

chocoでのインストール時にパスワード指定していない場合、下記のような表示が残っているはずです。これが初期パスワードPowershellを閉じてしまう前にメモしておきましょう。

postgresql12 v12.1 [Approved]
 postgresql12 package files install completed. Performing other installation steps.
 WARNING: You did not specify a password for the postgres user so an insecure one has been generated for you. Please change it immediately.
 WARNING: Generated password: a80fe15a4251473ea62e411dbbb1d7b8

誤ってウィンドウを閉じてしまった場合は、「C:\ProgramData\chocolatey\logs」に、chocolateyのインストールログとして残ってるので検索してみてください。

なお、chocoのインストール時に特定のパスワードを指定する方法は下記参考にしてください。

Chocolatey: PostgreSQL12.1

動作・接続確認

最低限の動作確認をします。上記chocoのインストール直後の状態で既にpostgresqlのサービスは起動しています。

• pgadmin4を起動
• pgadmin4のパスワードを指定
• PostgreSQL 12を選択
• DBパスワードを指定（インストール時に自動生成されたランダム文字列）

うまくいけば、下記画像のように接続できるはずです。

ECPG確認 （potgresqlのプリコンパイルコマンド）

Powershellターミナルを新しく立ち上げ直して、「ecpg –help」と打ってみます。ここまででプリコンパイルまでは可能な環境が準備できました。

PS C:\WINDOWS\system32> ecpg --help
 ecpg is the PostgreSQL embedded SQL preprocessor for C programs.
 Usage:
   ecpg [OPTION]… FILE…
 Options:
   -c             automatically generate C code from embedded SQL code;
                  this affects EXEC SQL TYPE
   -C MODE        set compatibility mode; MODE can be one of
                  "INFORMIX", "INFORMIX_SE", "ORACLE"
   -D SYMBOL      define SYMBOL
   -h             parse a header file, this option includes option "-c"
   -i             parse system include files as well
   -I DIRECTORY   search DIRECTORY for include files
   -o OUTFILE     write result to OUTFILE
   -r OPTION      specify run-time behavior; OPTION can be:
                  "no_indicator", "prepare", "questionmarks"
   --regression   run in regression testing mode
   -t             turn on autocommit of transactions
   -V, --version  output version information, then exit
   -?, --help     show this help, then exit
 If no output file is specified, the name is formed by adding .c to the
 input file name, after stripping off .pgc if present.
 Report bugs to [email protected].

Cコンパイラについて

今回利用する「Windows10開発環境」の場合、VisualStudio2019がインストール済みとなっており、これを利用したいところですが初期設定などハマりそうなので、gccでテストしてみます。（VisualStudio2019で少し挑戦しましたがハマって抜け出せませんでした）

gccは下記でインストール可能です

choco install mingw -y

新しいコマンドプロンプトを立ち上げ、下記で確認しておきましょう。

gcc --help

ECPGを体験してみる

ECPGソースを用意（*.pgc）

ここからは、例えば下記のような作業場所で作業します。

mkdir C:\Users\User\Documents\ecpg
cd /d  C:\Users\User\Documents\ecpg 

ここに、「test1.pgc」というファイル名で下記を作成します。passwordなどの接続情報は各自の環境に合わせてください。

/* test.pgc */
int main()
{
    EXEC SQL BEGIN DECLARE SECTION;
        const char *target = "postgres@localhost:5432";
        const char *user = "postgres";
        const char *passwd = "a80fe15a4251473ea62e411dbbb1d7b8";/change password*/
    EXEC SQL END DECLARE SECTION;
    EXEC SQL CONNECT TO :target USER :user USING :passwd; EXEC SQL BEGIN DECLARE SECTION;
        char dbname[1024];
        char tmpstr[1024];
    EXEC SQL END DECLARE SECTION;

    EXEC SQL SELECT current_database() INTO :dbname;
    printf("current_database=%s \n", dbname);

    EXEC SQL SELECT cast(current_timestamp as varchar) INTO :tmpstr;
    printf("current_timestamp=%s \n", tmpstr);

    EXEC SQL DISCONNECT;
    return 0;
 }

プリコンパイル

上記ソースをSQLプリプロセッサを通してプリコンパイルすることで通常のCプログラム（*.c)に変換させます。下記コマンドで可能です

ecpg test1.pgc

test1.pgcと同じフォルダに「test1.c」が出来上がっていることを確認してください。

実行ファイル作成（コンパイル＆リンク）

まず、オブジェクトファイルのみ作成、次にライブラリをリンクさせるという2段階で実行ファイルを作成します。

gcc -I "c:\Program Files\PostgreSQL\12\include" -c test1.c
gcc -o test1 test1.o -L "c:\Program Files\PostgreSQL\12\lib" -lecpg

一つ目のコマンドで「test1.o」が作成されます

二つ目のコマンドで「test1.exe」が作成されます。

test1.exeの実行

test1.exeを実行してみましょう。「test1.exe」と打ち込むだけです。

C:\Users\User\Documents\ecpg>test1.exe
 current_database=postgres
 current_timestamp=2020-01-02 03:50:02.568824-08

うまくいきました。今回の目的としては十分です。

今回ソースコードで確認できたこと

ソースの中身を解説しませんでしたが、下記が確認できています

• DBへの接続
• 接続したDBへのSQL実行

ここまで確認できれば、最低限OKでしょう。

考察

Oracle　⇔　PostgreSQL

Oracleは有償ですが、PostgreSQLはOSSのため無償で利用できます。

• ProC　⇔　ECPG
• PLSQL　⇔　PLpgSQL

と、それぞれ同じレイヤを担当しているので、うまくコンバージョンを考えれば移行は夢ではないです。PLpgSQLでは、orafceのような互換性を向上する仕組みも存在しています。ECPGにはそのような互換性を補完するものがあるかわかりませんが、SQL文の方言を修正するだけで、多くのことが解決するのではないでしょうか。

　⇒ PostgreSQLでorafceを使えるようにする

C言語パワー

PostgreSQLを外部から接続し操作するケース。例えばバッチ処理などが考えられると思います。Ｃ言語コンパイラによる実行速度の速さを期待し、処理性能を追求することができるでしょう。

更に究極的に性能を追求するは、複雑な処理はPLpgSQLに任せることでＮＷオーバヘッドを更に減らした設計が可能でしょう。

そして、何より世に多く埋もれた「C言語のプロ」の方々の力を借りることができるという点もメリットかもしれませんね。

参考サイト

• PostgreSQL 11.3文書 第36章 ECPG – C言語による埋め込みSQL
• ｅｃｐｇのごく簡単な例
• C プログラムに SQL を埋め込む（プリプロセッサ ecpg を利用）