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

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

GitHub - anse1/sqlsmith: A random SQL query generator

A random SQL query generator. Contribute to anse1/sqlsmith development by creating an account on GitHub.

Build on OSX
実行
コマンドラインオプション
実行情報
内部調査
参考リンク

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

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

1	/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

$ 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

--- 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)

./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
;

100

101

./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

;

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

オプション	説明	例
`--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オプションが指定されていれば，指定回数のクエリが投入されてからプログラムは終了する。

ロガー

クエリ生成及び実行，エラーのタイミングで呼ばれる。処理内容は，generated，executed，errorメソッドで実装する。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 */

#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);
}
}

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