PostgreSQL の SQL を静的に構文チェックしたくて、調べていたら pgsanity というぴったりなプログラムがあったので、使い方を簡単にメモ

SQL の構文チェック

SQL を実際に実行せずに、SQL の構文だけをチェックしたい場合

  1. explain を走らせる
  2. テーブルがなにもない空のデータベースに対して実行する
  3. トランザクションで SQL を囲み、最後にアボートする


出来ればデータベースを介さずに SQL の構文をチェックしたいなぁと探していると pgsanity というのがあった。

pgsanity のアーキテクチャ

PostgreSQL には SQL を C 言語のプログラム内にインラインで記述する埋め込み SQL(embedded SQL) の機能がある。
埋め込み SQL のメリットとしては、マニュアル(§33.1 The Concept)では以下の3つが挙げられている。

Embedded SQL has advantages over other methods for handling SQL commands from C code.

1. it takes care of the tedious passing of information to and from variables in your C program.
2. the SQL code in the program is checked at build time for syntactical correctness.
3.embedded SQL in C is specified in the SQL standard and supported by many other SQL database systems.

不正なインライン SQL はプリプロセッサーが弾いてくれる。

pgsanity では、このプリプロセッサー(ecpg)を使って SQL のシンタックスをチェックしている。

It does this by leveraging the ecpg command which is traditionally used for preparing C files with embedded sql for compilation. However, as part of that preparation, ecpg checks the embedded SQL statements for syntax errors using the exact same parser that is in PostgreSQL.

So the approach that PgSanity takes is to take a file that has a list of bare SQL in it, make that file look like a C file with embedded SQL, run it through ecpg and let ecpg report on the syntax errors of the SQL.


pgsanity では以下の順で処理している

  1. SQL をインライン SQL に変換(EXEC SQL ...)
  2. インライン SQL をプリプロセッサー(ecpg)に食わせる
  3. 正しい SQL の場合は C のコードが生成される。不正な場合は、エラーメッセージが返される。

プリプロセッサー ecpg を触ってみる

ecpg にインライン SQL を食わせて動作を確認する。

ecpg-c オプションで EXEC SQL 形式のインライン SQL を C のコードに変換できる。

-c : Automatically generate certain C code from SQL code. Currently, this works for EXEC SQL TYPE.

正しい SQL(select)

$ echo 'EXEC SQL select foo from bar;' > ok1.pgc
$ ecpg  -c ok1.pgc
$ cat ok1.c
/* Processed by ecpg (4.8.0) */
/* These include files are added by the preprocessor */
#include <ecpglib.h>
#include <ecpgerrno.h>
#include <sqlca.h>
/* End of automatic include section */

#line 1 "ok1.pgc"
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select foo from bar", ECPGt_EOIT, ECPGt_EORT);}
#line 1 "ok1.pgc"

正しい SQL(create table)

$ echo 'CREATE TABLE foo (
  name varchar(255)
);' > ok2.pgc
$ ecpg  -c ok2.pgc
$ echo $?
$ cat ok2.c
/* Processed by ecpg (4.8.0) */
/* These include files are added by the preprocessor */
#include <ecpglib.h>
#include <ecpgerrno.h>
#include <sqlca.h>
/* End of automatic include section */

#line 1 "ok2.pgc"
  name varchar(255)

NG:キーワード table を使っている

$ echo 'EXEC SQL select foo from table;' > ng1.pgc
$ ecpg  -c ng1.pgc
ng1.pgc:1: ERROR: syntax error at or near "table"
$ echo $?

NG:order by の位置がおかしい

$ echo 'EXEC SQL select a from b order by c where d = e;' > ng2.pgc
$ ecpg  -c ng2.pgc
ng2.pgc:1: ERROR: syntax error at or near "where"
$ echo $?


ecpg を確認したところで pgsanity をインストールする。
README.md の Installation に書かれているとおりにインストールすれば OK

  1. ecpg をインストール
  2. pipインストール
  3. $ pip install pgsanity を実行

pgsanity というプログラムがインストールされる。

pgsanity の使い方

README.md の Usage のセクションに使い方は書かれている。

SQL を引数でわたし、リターンコードを確認。

コマンドラインに SQL のファイルを渡す

$ pgsanity file_with_sql.sql
$ echo $?
$ pgsanity good1.sql good2.sql bad.sql
bad.sql: line 1: ERROR: syntax error at or near "bogus_token"
$ echo $?


$ echo "select mycol, from mytable;" | pgsanity
line 1: ERROR: syntax error at or near "from"
$ echo $?

SQL がセミコロンで終了していないと、エラーとして処理されるので、注意が必要。(Interpreting The Results のセクションにも注意書きがある)

$ echo "select mycol from mytable;" | pgsanity
$ echo "select mycol from mytable" | pgsanity
line 2: ERROR: syntax error at or near ""


Tagged with: , ,
Posted in database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • RT @__apf__: How to write a research paper: a guide for software engineers & practitioners. docs.google.com/presentation/d… /cc @inwyrd 1 week ago
  • RT @HayatoChiba: 昔、自然と対話しながら数学に打ち込んだら何かを悟れるのではと思いたち、専門書1つだけ持ってパワースポットで名高い奈良の山奥に1週間籠ったことがある。しかし泊まった民宿にドカベンが全巻揃っていたため、水島新司と対話しただけで1週間過ぎた。 それ… 3 weeks ago
  • RT @googlecloud: Ever wonder what underwater fiber optic internet cables look like? Look no further than this deep dive w/ @NatAndLo: https… 3 weeks ago
  • @ijin UTC+01:00 な時間帯で生活しています、、、 6 months ago
  • RT @mattcutts: Google's world-class Site Reliability Engineering team wrote a new book: amazon.com/Site-Reliabili… It's about managing produc… 9 months ago
%d bloggers like this: