SQLクエリジェネレータsqlsmithを使ってみる

sqlsmithというSQLを自動生成してくれるソフトウェアがある。現在は,PostgreSQL9.1 or laterとSQLite3がサポートされているらしい。

https://github.com/anse1/sqlsmith

Build on OSX

早速ビルドして試してみる。環境はOSX。

brew install libpqxx automake libtool autoconf autoconf-archive
git clone git@github.com:anse1/sqlsmith.git
cd sqlsmith
autoreconf -i
./configure
make

とREADME.mdのとおりにすると以下のようなエラーになってしまう。。どうもヘッダーファイルが見つからないらしい。

/postgres.hh:14:10: fatal error: 'postgresql/libpq-fe.h' file not found

brew install postgresqlでインストールした先の情報は,以下のようになっており,/usr/local/Cellar/postgresql/9.6.2/includeにあるらしい。

$ pg_config
BINDIR = /usr/local/Cellar/postgresql/9.6.2/bin
DOCDIR = /usr/local/Cellar/postgresql/9.6.2/share/doc/postgresql
HTMLDIR = /usr/local/Cellar/postgresql/9.6.2/share/doc/postgresql
INCLUDEDIR = /usr/local/Cellar/postgresql/9.6.2/include
PKGINCLUDEDIR = /usr/local/Cellar/postgresql/9.6.2/include
INCLUDEDIR-SERVER = /usr/local/Cellar/postgresql/9.6.2/include/server
LIBDIR = /usr/local/lib
PKGLIBDIR = /usr/local/lib/postgresql
LOCALEDIR = /usr/local/Cellar/postgresql/9.6.2/share/locale
MANDIR = /usr/local/Cellar/postgresql/9.6.2/share/man
SHAREDIR = /usr/local/share/postgresql
SYSCONFDIR = /usr/local/etc/postgresql
PGXS = /usr/local/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--disable-debug' '--prefix=/usr/local/Cellar/postgresql/9.6.2' '--datadir=/usr/local/share/postgresql' '--libdir=/usr/local/lib' '--sysconfdir=/usr/local/etc' '--docdir=/usr/local/Cellar/postgresql/9.6.2/share/doc/postgresql' '--enable-thread-safety' '--with-bonjour' '--with-gssapi' '--with-ldap' '--with-openssl' '--with-pam' '--with-libxml' '--with-libxslt' '--with-perl' '--with-tcl' '--with-tclconfig=/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.12.sdk/usr/lib' '--with-uuid=e2fs' 'CC=clang' 'LDFLAGS=-L/usr/local/opt/openssl/lib -L/usr/local/opt/readline/lib' 'CPPFLAGS=-I/usr/local/opt/openssl/include -I/usr/local/opt/readline/include'
CC = clang
CPPFLAGS = -DFRONTEND -I/usr/local/opt/openssl/include -I/usr/local/opt/readline/include -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.12.sdk/usr/include/libxml2
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2
CFLAGS_SL = 
LDFLAGS = -L../../src/common -L/usr/local/opt/openssl/lib -L/usr/local/opt/readline/lib -Wl,-dead_strip_dylibs
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = -lpgcommon -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lm  
VERSION = PostgreSQL 9.6.2

postgres.hhの以下を変更することで対応した。

--- sqlsmith_orig/postgres.hh 2017-04-06 23:54:19.000000000 +0900
+++ sqlsmith/postgres.hh    2017-04-06 23:52:48.000000000 +0900
@@ -11,7 +11,7 @@
 #include <pqxx/pqxx>
 
 extern "C" {
-#include <postgresql/libpq-fe.h>
+#include <libpq-fe.h>
 }
 
 #define OID long

実行

--max-queriesを指定すると最大試行回数を指定できる。なおトランザクションは全てロールバックされる。

./sqlsmith --verbose --target="host=/tmp port=5432 dbname=regression" --max-queries=1  
SQLsmith v1.0-157-g2194f0e-dirty
Loading types...done.
Loading tables...done.
Loading columns and constraints...done.
Loading operators...done.
Loading routines...done.
Loading routine parameters...done.
Loading aggregates...done.
Loading aggregate parameters...done.
Generating indexes...done.
.
queries: 1
AST stats (avg): height = 13 nodes = 222
error rate: 0
impedance report: 
  window_function: 0/1 (bad/ok)
  table_sample: 0/1 (bad/ok)

--dry-runするとSQLが標準出力に出力される。

./sqlsmith --verbose --target="host=/tmp port=5432 dbname=regression" --max-queries=1  --dry-run
SQLsmith v1.0-157-g2194f0e-dirty
Loading types...done.
Loading tables...done.
Loading columns and constraints...done.
Loading operators...done.
Loading routines...done.
Loading routine parameters...done.
Loading aggregates...done.
Loading aggregate parameters...done.
Generating indexes...done.
select  
  subq_0.c0 as c0, 
  (select classoid from pg_catalog.pg_seclabel limit 1 offset 5)
     as c1, 
  (select pg_catalog.min(valuntil) from pg_catalog.pg_shadow)
     as c2, 
  (select lanvalidator from pg_catalog.pg_language limit 1 offset 1)
     as c3
from 
  pg_catalog.pg_attribute as sample_0 tablesample system (2.9) 
    inner join (select  
          sample_1.connamespace as c0, 
          (select autoanalyze_count from pg_catalog.pg_stat_user_tables limit 1 offset 5)
             as c1, 
          (select rolcreatedb from pg_catalog.pg_roles limit 1 offset 2)
             as c2, 
          sample_1.contoencoding as c3, 
          sample_1.contoencoding as c4, 
          sample_1.connamespace as c5, 
          (select prepared from pg_catalog.pg_prepared_xacts limit 1 offset 2)
             as c6, 
          case when EXISTS (
              select  
                  (select pg_catalog.avg(procost) from pg_catalog.pg_proc)
                     as c0, 
                  ref_0.domain_name as c1, 
                  ref_0.domain_schema as c2, 
                  ref_0.domain_catalog as c3
                from 
                  information_schema.column_domain_usage as ref_0
                where (false) 
                  or (ref_0.domain_name is not NULL)) then sample_1.conname else sample_1.conname end
             as c7, 
          50 as c8
        from 
          pg_catalog.pg_conversion as sample_1 tablesample system (6) 
        where (sample_1.contoencoding is NULL) 
          or (sample_1.conproc is not NULL)
        limit 68) as subq_0
    on (case when (select table_schema from information_schema.role_column_grants limit 1 offset 1)
               is NULL then case when cast(null as bpchar) ~>~ cast(null as bpchar) then cast(null as date) else cast(null as date) end
             else case when cast(null as bpchar) ~>~ cast(null as bpchar) then cast(null as date) else cast(null as date) end
             end
           >= subq_0.c6)
where cast(null as tinterval) #>= case when subq_0.c0 is not NULL then case when EXISTS (
        select  
            sample_2.collowner as c0
          from 
            pg_catalog.pg_collation as sample_2 tablesample bernoulli (9.4) 
              inner join information_schema.sql_parts as ref_1
              on (cast(null as uuid) = cast(null as uuid))
          where (false) 
            and (cast(null as box) << cast(null as box))
          limit 73) then case when (false) 
          or (cast(null as bpchar) < cast(null as bpchar)) then pg_catalog.reltimein(
          cast(pg_catalog.time_out(
            cast(cast(null as "time") as "time")) as cstring)) else pg_catalog.reltimein(
          cast(pg_catalog.time_out(
            cast(cast(null as "time") as "time")) as cstring)) end
         else case when (false) 
          or (cast(null as bpchar) < cast(null as bpchar)) then pg_catalog.reltimein(
          cast(pg_catalog.time_out(
            cast(cast(null as "time") as "time")) as cstring)) else pg_catalog.reltimein(
          cast(pg_catalog.time_out(
            cast(cast(null as "time") as "time")) as cstring)) end
         end
       else case when EXISTS (
        select  
            sample_2.collowner as c0
          from 
            pg_catalog.pg_collation as sample_2 tablesample bernoulli (9.4) 
              inner join information_schema.sql_parts as ref_1
              on (cast(null as uuid) = cast(null as uuid))
          where (false) 
            and (cast(null as box) << cast(null as box))
          limit 73) then case when (false) 
          or (cast(null as bpchar) < cast(null as bpchar)) then pg_catalog.reltimein(
          cast(pg_catalog.time_out(
            cast(cast(null as "time") as "time")) as cstring)) else pg_catalog.reltimein(
          cast(pg_catalog.time_out(
            cast(cast(null as "time") as "time")) as cstring)) end
         else case when (false) 
          or (cast(null as bpchar) < cast(null as bpchar)) then pg_catalog.reltimein(
          cast(pg_catalog.time_out(
            cast(cast(null as "time") as "time")) as cstring)) else pg_catalog.reltimein(
          cast(pg_catalog.time_out(
            cast(cast(null as "time") as "time")) as cstring)) end
         end
       end
    ;

コマンドラインオプション

オプション 説明
--target=constr 接続先 --target="host=/tmp port=5432 dbname=regression"
--sqlite=URI SQLiteへの接続先 --sqlite="foo.db"
--log-to=constr ログ出力先のデータベース。スキーマ定義は,log.sqlにある。 --log-to="host=/tmp port=5432 dbname=regression_log"
--seed シード --seed=$(date +%s)
--dump-all-graphs 生成された抽象構文木ASTを出力する --dump-all-graphs
--dry-run クエリを実行せず出力する --dry-run
--exclude-catalog リレーションにカタログテーブルを使わない --exclude-catalog
--max-queries この値に指定されたクエリを実行すると処理を終了する --max-queries=10

実行情報

--verboseオプションをつけると,クエリ実行の情報が標準エラー出力に表示される。

文字 意味
. 成功
t タイムアウト
s 構文エラー
c コネクションエラー
e その他のエラー

内部調査

全体の処理の流れは以下のようになっている。カスタマイズしたい時の参考になるかもしれない。

メイン処理

メイン部分の処理の流れは以下のとおり。コマンド実行すると,sqliteかpostgresqlかに応じてスキーマ及びドライバを初期化し,無限ループでひたすらクエリを生成して実行する。--max-queriesオプションが指定されていれば,指定回数のクエリが投入されてからプログラムは終了する。

ロガー

クエリ生成及び実行,エラーのタイミングで呼ばれる。処理内容は,generatedexecutederrorメソッドで実装する。loggerクラスの仮想関数をオーバーライドすれば独自のロガーを作成できる。

impedance_feedback

クエリ実行の統計を記録する

pqxx_logger

クエリ実行の統計情報をpostgresqlデータベースに記録する。

cerr_logger

標準エラー出力に情報を出力する。--verboseオプションが指定された場合に有効。

ast_logger

--dump-all-graphsオプションが指定された場合に有効。ASTをxml形式でファイルに出力する。sqlsmith-<実行回数>.xmlというファイル名で作業ディレクトリに出力される。xmlは,graphml形式に対応している。

クエリ生成

ASTノードはクラスで宣言されており,各ノードクラスのoutメソッドを呼ぶことでクエリが組み立てられる。ASTノードのベースクラスはprodクラスである。

またASTは,Visitorパターンで実装されている。ASTツリーをwalkしたい場合はVisitorを実装して,ルートノードのacceptにVisitorオブジェクトを渡して呼び出してやれば良い。

各ノードのクエリ生成をカスタマイズするには,ASTノードクラスのoutメソッドをオーバーライドすれば可能である。以下例である。

#ifndef myast_h
#define myast_h

#include <iostream>
#include "grammar.hh"
using namespace std;

struct my_select : query_spec {
    my_select(prod *p, struct scope *s, bool lateral = 0)
        : query_spec(p, s, lateral)
    { }
    virtual ~my_select() {  }
    virtual void out(std::ostream &out) {
        out << "select 1;" << endl;
        cerr << "LOG: select 1;" << endl;
    }
};

#endif /* myast_h */

ASTノード生成の関数も少し触る必要がある。

shared_ptr<prod> statement_factory(struct scope *s)
{
  try {
    s->new_stmt();
    if (1) // ここにオリジナルを仕込む。
        return make_shared<my_select>((struct prod *)0, s);
    else if (d42() == 1)
      return make_shared<insert_stmt>((struct prod *)0, s);
    else if (d42() == 1)
      return make_shared<delete_returning>((struct prod *)0, s);
    else if (d42() == 1) {
      return make_shared<upsert_stmt>((struct prod *)0, s);
    } else if (d42() == 1)
      return make_shared<update_returning>((struct prod *)0, s);
    else if (d6() > 4)
      return make_shared<select_for_update>((struct prod *)0, s);
    else if (d6() > 5)
      return make_shared<common_table_expression>((struct prod *)0, s);
    return make_shared<query_spec>((struct prod *)0, s);
  } catch (runtime_error &e) {
    return statement_factory(s);
  }
}

参考リンク

  • https://korte.credativ.com/~ase/sqlsmith-talk.pdf
  • https://github.com/anse1/sqlsmith

byebyehaikikyou

日記やIT系関連のネタ、WordPressに関することなど様々な事柄を書き付けた雑記です。ITエンジニア経験があるのでプログラミングに関することなどが多いです。

シェアする

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

コメントする

Translate »