LoginSignup
5
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@bubu_sukeinSumally Inc.

Golangで動的SQLを作成するライブラリ（sqlpatchwork）を作ってGitに公開してみた

Last updated at Posted at 2020-08-19

bubusuke/sqlpatchworkについてご紹介します!

  • Golang勉強中です！
  • 初めてのOSS。しかもAuthor。
  • みんなに使ってもらいたいので説明頑張りました！長文です。ご容赦ください。

できるようになること

  • 動的SQLの仕組みを簡単に導入できます。
  • 複数レコードを一括処理するバルククエリ（MultiRow-INSERTなどのMultiRowクエリ）のSQLを動的に簡単に作れます。
  • MultiRowクエリの書き方や特徴について知りたい方は こちら も合わせてご覧ください。

Update!!

SQLファイルなしでも使えるようになりました！(2020.08.21)

2020.08.21.go
package main

import "github.com/bubusuke/sqlpatchwork"

func simpleSample() {
	qps := map[string]string{
		"prefix":    "INSERT INTO hoge_table (col1, col2) VALUES",
		"loopVal":   "(:col1_@@,:col2_@@)",
		"loopDelim": ",",
	}

	spw := sqlpatchwork.NewSimplePWSkipPrs("SkipFileParse", qps)

	// 後の使い方は使用方法デモの章と一緒
}
func onOffSample() {
	qps := sqlpatchwork.OnOffQPs(
		sqlpatchwork.OnOffQP("SELECT s.item_code , s.sales_date , COUNT(*) AS count FROM sales_tran s"),
		sqlpatchwork.OnOffQP("INNER JOIN item_master i ON i.item_code = s.item_code", "itemTypeNotNil", "colorCodeNotNil"),
		sqlpatchwork.OnOffQP("WHERE 1=1"),
		sqlpatchwork.OnOffQP("AND i.item_type = :item_type", "itemTypeNotNil"),
		sqlpatchwork.OnOffQP("AND i.color_code = :color_code", "colorCodeNotNil"),
		sqlpatchwork.OnOffQP("GROUP BY s.item_code , s.sales_date ORDER BY s.item_code , s.sales_date"))

	spw := sqlpatchwork.NewOnOffPWSkipPrs("SkipFileParse", qps)
	// 後の使い方は使用方法デモの章と一緒

}

使用方法デモ

本当は最初に書きたかったですが、めっちゃ長いので最後に記載します。

作成に至った経緯

JavaのMyBatisやUrobroSQLのようなものを探したのですが見つかりません。
具体的には、以下の3つの機能をもつものを探してました。

  • SQLファイルを読み込む（goにSQLを埋め込みたくない・素のSQLを書きたい読みたい）
  • パラメータに応じて動的にSQLを構築する（似たようなSQLを何度も書きたくない）
  • BulkInsertやMultiRow-Insertを実行できる（一度に沢山データを処理したい）

あれ、。。。ない？
→　仮に作るならどういうものが嬉しいだろう。
→　ちょっといい感じのコンセプト浮かぶ。
→　できそうな気がする。。。
→　よっしゃ！作ってみるか！！

という流れ。

ライブラリ説明

このライブラリがやること

以下の3つをするだけです。

step 1. SQLファイルに記載されたSQLを複数のパーツ（query-piece）に分ける。
step 2. 利用するquery-piece を選ぶ。
step 3. 選んだquery-pieceをつなぎ合わせる。

ピースをツギハギするという意味で、SQL Patchworkと名付けました。
ツギハギするためのstep. 2に当たる分岐ロジックは各自、Golangで実装して使うため、
SQLファイル上には、If/Choose/for などの制御構文がないことが特徴です。

(2020.08.21追記ここから)
SQLファイルなしでも使えるようになりました。
SQLファイルなしの場合、以下のように使います。

step 1. SQLを複数のパーツ（query-piece）に分けたものを引数で渡す。
step 2. 利用するquery-piece を選ぶ（SQLファイル利用と一緒）。
step 3. 選んだquery-pieceをつなぎ合わせる（SQLファイル利用と一緒）。
(2020.08.21追記ここまで)

Summary.JPG

Point 1
  • @startキーワードでQuery-pieceブロックを開始します。
  • @startキーワードに続く単語がQuery-pieceのIDとなります。
    • 二単語目以降は無視されます。例 /*@start ThisIsID ThisIsIgnored*/
    • SimplePatchworkモード（後述）の場合、IDを一意にする必要があります。
    • OnOffPatchworkモード（後述）の場合、複数のQuery-pieceに同じIDを付与できます。
    • OnOffPatchworkモード（後述）の場合、一つのQuery-pieceに対して、/区切りで複数のIDを付与できます。
  • @endキーワードでQuery-Pieceブロックを終了します。
  • Query-Pieceブロックの入れ子構造には対応していません。
Point 2
  • SQL中のCommentoutや、Commentblockは全て無視されます（最終アウトプットで出力されません）。
Point 3
  • Query-Pieceの選択方法が異なる、SimplePatchworkモードとOnOffPatchworkモードという2つ機能を提供します。例に示す図の処理はどちらのモードでも実現できます（詳しくは後述）。
Point 4
  • 本機能の最終アウトプットはSQLのQueryテキストです（string型）。DBアクセス等の機能はありません。
  • 最終アウトプットは、改行なし、コメントなし、Trimなどの整形処理を施したQueryとなります。
  • 使用したSQLファイル、選択したQueryPieceのIDをコメントとして記載したQueryをアウトプットすることも可能です。

コンセプト

低い学習コスト

例えば、MyBatis。便利なのですが、利用するにはいくつものMyBatis固有の文法を覚える必要があり、学習コストは決して低くはないです。誰でも利用できるよう、SQLに記載する固有の文法は限りなく減らし、学習コストを下げようと考えました（この方がParse処理でバグがないですしね）。

他のDBアクセスライブラリとの共存共生（当ライブラリをアドオン的に利用）

DBアクセス周りやORM周りでは既に素晴らしいライブラリがいくつも存在します。そのようなライブラリとの共存共生を目指しました。つまり、このライブラリはSQL Query Builderであり、DBアクセス・ORMに関する一切の機能は持ちません。

Easy to test

SQLファイル上に独自の制御構文があると、goの魅力的なテストツールの恩恵が受けられません。このため、ツギハギロジックはGolangで利用者に実装してもらうようにしました。このため、「独自制御構文での分岐ロジックを全てカバーできているか」という問題に悩まさせることはありません。

SimplePatchworkモードとOnOffPatchworkモード

Query-Pieceの選択方法が異なる、SimplePatchworkモードとOnOffPatchworkモードという2つ機能を提供します。

SimplePatchworkモード

QueryPieceを指示した順 につなぎ合わせるSimpleかつ強力なモードです。
SimplePatchwork.JPG

Point 1
  • Query-pieceのIDは一意に設定する必要があります。
Point 2, 3
  • 同じQuery-pieceを複数回指定すること可能です。
  • @@キーワードは、そのQueryPieceを指定した数だけインクリメントした数値に置換されます（0からインクリメントしていきます）。バインド変数につけておくことで、Multirow-insertなどのクエリ構築時にバインド変数を各行で変更できます。この @@キーワード置換機能はSimplePatchworkモードのみ提供しており、OnOffPatchworkモードは提供していません。

2. OnOffPatchworkモード

SimplePatchworkはシンプルに組み合わせる機能のため、汎用性が高い反面、選択ロジックが長くなったり、SQLファイルに本当に継ぎはぎのパーツを記載されて可読性が下がることが懸念されます。

この懸念事項の解決策として提供する機能がOnOffPatchworkモードです。
このモードでは、Query-pieceをSQLファイルで記載されている順 につなぎ合わせます。
そしてつなぎ合わせる際に、Query-pieceを指示したものだけをつなげます。
OnOffPatchwork.JPG

Point 1
  • 必ず選択するQueryPieceは@startと@endの指定を省略できます（省略した部分には、__defaultというIDが付与されます）。
Point 2
  • 複数のQuery-pieceに同じIDを付与できます
  • 一つのQuery-pieceに対して、/区切りで複数のIDを付与できます。
Point 3
  • 使用する（Onにする）IDを選択すれば該当するQuery-pieceをつなげます(SQLファイルに記載された順につなげます)。

SQLファイルに導入した独自文法

以下の三つのみです。

  • /*@start someID*/ : someIDというIDをもつQuery-Pieceの開始を宣言します。
  • /*@end*/ : Query-Pieceの終了を宣言します。
  • @@ : 同じQuery-Pieceの利用回数の数値で置換されます。0,1とインクリメントされていきます。詳しくはSimplePatchworkのPoint2をご確認ください。（SimplePatchworkモードのみの機能です）。

(2020.08.21追記ここから)
SQLファイルなしで本機能を使う場合は、@@キーワードのみを覚えればよいですね。
(2020.08.21追記ここまで)

ライセンス

MITです。どうぞご利用ください！

想定問答

Q1. SimplePatchworkとOnOffPatchworkをどう使い分けたらよい？
A1. OnOffPatchworkの方が、Golangに実装するロジックも、SQLの構文もスッキリし、可読性が高くなると思います。可能な限りOnOffPatchworkの利用を推奨します。（当初、OnOffPatchworkのみ実装予定でしたが、これではMultirow-insertを実現できないため、SimplePatchworkを作りました。）

Q2. IN句の動的に展開したい場合はSimplePatchworkを利用すべきでしょうか。
A2. 既存のDBアクセスライブラリによっては、IN句のSlice型バインド変数を展開する機能が存在します。それを利用すればOnOffPatchworkの利用でも動的に展開することは可能です。

Q3. IN句の複数列指定の場合（ (col1, col2) IN ((?,?),...,(?,?)) の場合）はどうでしょうか。
A3. おそらく既存のDBアクセスライブラリは対応できていないため、SimplePatchworkでツギハギしてください。

(2020.08.21追記ここから)
Q4. バルクインサートでちょっと使いたいだけなのにSQLファイル必要なの？
A4. SQLファイルなしで利用できるようアップデートしました。
(2020.08.21追記ここまで)

課題・TODO（もしよければご協力ください）

  • 内部はかなり泥臭い処理になってます。特に[]byte型とstring型を行ったり来たりしてます。この変換ってパフォーマンス大丈夫かなと気になってますが力及ばず。
  • 英語に自信なし！
  • テストのカバレッジは90%。100%までちょくちょく足していきます。

最後に

やっぱり自分で手を動かしてつくるとわかんないところ多くて苦労しました。いい勉強になった！
Golang初学者が作成したため、上記課題に限らず改善箇所の宝庫のはずｗ
変なところあればコメントやPR頂けると嬉しいです。

使用方法デモ

お待たせしました。こんな感じで使っていただければ。

SimplePatchwork

package main

import (
	"fmt"
	"log"

	"github.com/bubusuke/sqlpatchwork"
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
)

type hoge struct {
	Col1 int    `db:"col1"`
	Col2 string `db:"col2"`
}

func main() {
	hoges := []hoge{
		{Col1: 100, Col2: "foo"},
		{Col1: 200, Col2: "bar"},
		{Col1: 300, Col2: "hoge"},
	}
	spw, err := sqlpatchwork.NewSimplePatchwork("./sqls/simplePatchwork.sql")
	if err != nil {
		fmt.Println(err)
	}
	bindMap := make(map[string]interface{})

	if err := spw.AddQueryPiecesToBuild("prefix"); err != nil {
		fmt.Println(err)
	}
	for i, h := range hoges {
		if i != 0 {
			if err := spw.AddQueryPiecesToBuild("loopDelim"); err != nil {
				fmt.Println(err)
			}
		}
		if err := spw.AddQueryPiecesToBuild("loopVal"); err != nil {
			fmt.Println(err)
		}
		bindMap[sqlpatchwork.LoopNoAttach("col1_@@", i)] = h.Col1
		bindMap[sqlpatchwork.LoopNoAttach("col2_@@", i)] = h.Col2
	}

	fmt.Println("==========================")
	fmt.Println("TargetIDs are")
	fmt.Println(spw.TargetIDs())
	fmt.Println("==========================")
	fmt.Println("BuildingQuery is")
	fmt.Println(spw.BuildQuery())
	fmt.Println("==========================")
	fmt.Println("BuildingQueryWithTrace is")
	fmt.Println(spw.BuildQueryWithTraceDesc())
	fmt.Println("==========================")
	fmt.Println("BindMap is")
	fmt.Println(bindMap)
	fmt.Println("==========================")

	insertDemoByUsingSqlx(spw.BuildQuery(), bindMap)
}

//insertDemoByUsingSqlx is using jomoiron/sqlx to execute SQL.
func insertDemoByUsingSqlx(query string, bindMap map[string]interface{}) {
	db, err := sqlx.Connect("postgres", "user=postgres password=pass dbname=postgres sslmode=disable")
	if err != nil {
		log.Fatalln(err)
	}

	db.MustExec("DROP TABLE IF EXISTS hoge_table")
	db.MustExec("CREATE TABLE hoge_table (col1 int, col2 varchar(25))")

	tx := db.MustBegin()
	_, err = tx.NamedExec(query, bindMap)
	if err != nil {
		log.Fatalln(err)
	}
	tx.Commit()

	hoges := []hoge{}
	err = db.Select(&hoges, "SELECT col1, col2 FROM hoge_table ORDER BY col1")
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Println("==========================")
	fmt.Println("The result of query execution is")
	fmt.Println(hoges)
	fmt.Println("==========================")

}

// The file content of ./sqls/simplePatchwork.sql
// --------------------------------------------
// /*@start prefix */
// INSERT INTO hoge_table (col1, col2) VALUES 
// /*@end*/
// /*@start loopVal */
// (:col1_@@,:col2_@@)
// /*@end*/
// /*@start loopDelim */
// ,
// /*@end*/

// STDOUT
// --------------------------------------------
// ==========================
// TargetIDs are
// [prefix loopVal loopDelim loopVal loopDelim loopVal]
// ==========================
// BuildingQuery is
// INSERT INTO hoge_table (col1, col2) VALUES (:col1_0,:col2_0) , (:col1_1,:col2_1) , (:col1_2,:col2_2)
// ==========================
// BuildingQueryWithTrace is
// INSERT /* ./sqls/simplePatchwork.sql [prefix loopVal loopDelim loopVal loopDelim loopVal] */ INTO hoge_table (col1, col2) VALUES (:col1_0,:col2_0) , (:col1_1,:col2_1) , (:col1_2,:col2_2)
// ==========================
// BindMap is
// map[col1_0:100 col1_1:200 col1_2:300 col2_0:foo col2_1:bar col2_2:hoge]
// ==========================
// ==========================
// The result of query execution is
// [{100 foo} {200 bar} {300 hoge}]
// ==========================

OnOffPatchwork

package main

import (
	"fmt"
	"log"

	"github.com/bubusuke/sqlpatchwork"
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
)

type req struct {
	ItemType  string `db:"item_type"`
	ColorCode string `db:"color_code"`
}

type Res struct {
	ItemCode  int    `db:"item_code"`
	SalesDate string `db:"sales_date"`
	Count     int    `db:"count"`
}

func buildQuery(r *req, isTraceDesc bool) string {
	spw, err := sqlpatchwork.NewOnOffPatchwork("./sqls/onoffPatchwork.sql")
	if err != nil {
		fmt.Println(err)
	}
	if r.ItemType != "" {
		spw.AddQueryPiecesToBuild("itemTypeNotNil")
	}
	if r.ColorCode != "" {
		spw.AddQueryPiecesToBuild("colorCodeNotNil")
	}
	fmt.Println("==========================")
	fmt.Println("request is")
	fmt.Println(r)
	fmt.Println("==========================")
	fmt.Println("TargetIDs are")
	fmt.Println(spw.TargetIDs())
	fmt.Println("==========================")
	fmt.Println("BuildingQuery is")
	fmt.Println(spw.BuildQuery())
	fmt.Println("==========================")
	fmt.Println("BuildingQueryWithTrace is")
	fmt.Println(spw.BuildQueryWithTraceDesc())
	fmt.Println("==========================")

	if isTraceDesc {
		return spw.BuildQueryWithTraceDesc()
	}
	return spw.BuildQuery()
}

func main() {

	resetDB()

	req1 := &req{ItemType: "T-shirts"}
	query1 := buildQuery(req1, false)
	selectDemoByUsingSqlx(query1, req1)

	req2 := &req{ColorCode: "W"}
	query2 := buildQuery(req2, false)
	selectDemoByUsingSqlx(query2, req2)

	req3 := &req{}
	query3 := buildQuery(req3, false)
	selectDemoByUsingSqlx(query3, req3)
}

//resetDB reset DB Data.
func resetDB() {
	db, err := sqlx.Connect("postgres", "user=postgres password=pass dbname=postgres sslmode=disable")
	if err != nil {
		log.Fatalln(err)
	}

	db.MustExec("DROP TABLE IF EXISTS sales_tran")
	db.MustExec("DROP TABLE IF EXISTS item_master")
	db.MustExec("CREATE TABLE item_master (item_code varchar(4), item_type varchar(25), color_code varchar(2))")
	db.MustExec("INSERT INTO item_master VALUES " +
		"('1000', 'T-shirts', 'B')" +
		",('2000', 'T-shirts', 'W')" +
		",('3000', 'pants', 'W')")

	db.MustExec("CREATE TABLE sales_tran (item_code varchar(4), sales_date date)")
	db.MustExec("INSERT INTO sales_tran VALUES " +
		"('1000','2020-08-20')" +
		",('1000','2020-08-20')" +
		",('1000','2020-08-19')" +
		",('2000','2020-08-20')" +
		",('2000','2020-08-19')" +
		",('2000','2020-08-19')" +
		",('3000','2020-08-19')" +
		",('3000','2020-08-18')" +
		",('3000','2020-08-18')")
}

//selectDemoByUsingSqlx is using jomoiron/sqlx to execute SQL.
func selectDemoByUsingSqlx(query string, rq *req) {
	db, err := sqlx.Connect("postgres", "user=postgres password=pass dbname=postgres sslmode=disable")
	if err != nil {
		log.Fatalln(err)
	}

	rows, err := db.NamedQuery(query, rq)
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Println("==========================")
	fmt.Println("The result of query execution is")
	res := Res{}
	for rows.Next() {
		err := rows.StructScan(&res)
		if err != nil {
			log.Fatalln(err)
		}
		fmt.Println(res)
	}
	fmt.Println("==========================")

}

// The file content of ./sqls/onoffPatchwork.sql
// --------------------------------------------
// SELECT
//  s.item_code
// , s.sales_date
// , COUNT(*) AS count
// FROM
//  sales_tran s
// /*@start itemTypeNotNil/colorCodeNotNil */
// INNER JOIN
//  item_master i
// ON
//  i.item_code = s.item_code
// /*@end*/
// WHERE 1=1
// /*@start itemTypeNotNil*/
// AND i.item_type = :item_type
// /*@end*/
// /*@start colorCodeNotNil*/
// AND i.color_code = :color_code
// /*@end*/
// GROUP BY
//  s.item_code
// , s.sales_date
// ORDER BY
//  s.item_code
// , s.sales_date

// STDOUT
// --------------------------------------------
// ==========================
// request is
// &{T-shirts }
// ==========================
// TargetIDs are
// [__default itemTypeNotNil]
// ==========================
// BuildingQuery is
// SELECT s.item_code , s.sales_date , COUNT(*) AS count FROM sales_tran s INNER JOIN item_master i ON i.item_code = s.item_code WHERE 1=1 AND i.item_type = :item_type GROUP BY s.item_code , s.sales_date ORDER BY s.item_code , s.sales_date
// ==========================
// BuildingQueryWithTrace is
// SELECT /* ./sqls/onoffPatchwork.sql [__default itemTypeNotNil] */ s.item_code , s.sales_date , COUNT(*) AS count FROM sales_tran s INNER JOIN item_master i ON i.item_code = s.item_code WHERE 1=1 AND i.item_type = :item_type GROUP BY s.item_code , s.sales_date ORDER BY s.item_code , s.sales_date
// ==========================
// ==========================
// The result of query execution is
// {1000 2020-08-19T00:00:00Z 1}
// {1000 2020-08-20T00:00:00Z 2}
// {2000 2020-08-19T00:00:00Z 2}
// {2000 2020-08-20T00:00:00Z 1}
// ==========================
// ==========================
// request is
// &{ W}
// ==========================
// TargetIDs are
// [__default colorCodeNotNil]
// ==========================
// BuildingQuery is
// SELECT s.item_code , s.sales_date , COUNT(*) AS count FROM sales_tran s INNER JOIN item_master i ON i.item_code = s.item_code WHERE 1=1 AND i.color_code = :color_code GROUP BY s.item_code , s.sales_date ORDER BY s.item_code , s.sales_date
// ==========================
// BuildingQueryWithTrace is
// SELECT /* ./sqls/onoffPatchwork.sql [__default colorCodeNotNil] */ s.item_code , s.sales_date , COUNT(*) AS count FROM sales_tran s INNER JOIN item_master i ON i.item_code = s.item_code WHERE 1=1 AND i.color_code = :color_code GROUP BY s.item_code , s.sales_date ORDER BY s.item_code , s.sales_date
// ==========================
// ==========================
// The result of query execution is
// {2000 2020-08-19T00:00:00Z 2}
// {2000 2020-08-20T00:00:00Z 1}
// {3000 2020-08-18T00:00:00Z 2}
// {3000 2020-08-19T00:00:00Z 1}
// ==========================
// ==========================
// request is
// &{ }
// ==========================
// TargetIDs are
// [__default]
// ==========================
// BuildingQuery is
// SELECT s.item_code , s.sales_date , COUNT(*) AS count FROM sales_tran s WHERE 1=1 GROUP BY s.item_code , s.sales_date ORDER BY s.item_code , s.sales_date
// ==========================
// BuildingQueryWithTrace is
// SELECT /* ./sqls/onoffPatchwork.sql [__default] */ s.item_code , s.sales_date , COUNT(*) AS count FROM sales_tran s WHERE 1=1 GROUP BY s.item_code , s.sales_date ORDER BY s.item_code , s.sales_date
// ==========================
// ==========================
// The result of query execution is
// {1000 2020-08-19T00:00:00Z 1}
// {1000 2020-08-20T00:00:00Z 2}
// {2000 2020-08-19T00:00:00Z 2}
// {2000 2020-08-20T00:00:00Z 1}
// {3000 2020-08-18T00:00:00Z 2}
// {3000 2020-08-19T00:00:00Z 1}
// ==========================
5
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?