最近は主に TypeScript を書いてます。楽しいです。
という話は置いておきまして、gorpを使ったバックエンドのコードレビューしていたときに、 SQLがあちこちに散らばってしまい可読性が悪くなってるのを見かけました。
gorpでは生SQLをかけるのでSQLが短ければ 直感的にわかりやすいというメリットはありますが、 結合や検索条件が複雑になるに連れコードは関数やファイルで分割されSQLはどんどん散り散りになっていきます。
私は十行足らずのSQLを表現するために数十行の Goのコードを書きたくありませんし、保守できる自信もありません。
前置きが長くなりましたが、表題の通り Go の標準テンプレート機能を使ってSQLを書いたほうが 直感的にわかりやすいだろうと思ったので、期待通りに動作するか検証してみます。
リポジトリ を作ったので手元で試したい人は Clone してください。 使い方は後述。 使うライブラリのバージョンとかは go.mod を参照のこと。
目次
準備が面倒で SQLBoiler の記事で使ったデータを流用してるので 設定が雑でも許してください。
👐 準備 preparation
$ git clone https://github.com/righ/gorp-tips.git $ cd gorp-tips $ docker-compose up
で環境を起動するとアプリ用のコンテナと MySQL のコンテナが起動するので docker exec -it gorp /bin/bash でアプリのコンテナに入ります。
すでにレコードも入ってるので特にもうすることはないです。一応 MySQL に接続してみます。
root@5a6a76a0bebd:~# mysql -uusr -ppw -hmysql db -e "select * from pilots;" +----+---------+ | id | name | +----+---------+ | 1 | Manfred | | 2 | William | | 3 | Albert | | 4 | Warner | | 5 | Andrew | +----+---------+
大丈夫そうですね。
実行するプログラムは src/ に入ってるので先に移動しておきましょう。
root@5a6a76a0bebd:~# cd src/
💪 実装
この記事では、上で準備したテーブルを雑にJOIN(LEFT JOIN)して検索して標準出力に表示するプログラムを作っていきます。
今回使ういくつかのプログラムはリポジトリパターンを採用しており、DBをいじる処理は repositories/ 配下に書き、コントローラー経由で呼び出しています。 それぞれのプログラムはコントローラーに異なるリポジトリを指定することで異なる処理を実行するという具合です。
-
📁 src
-
📁 controllers
- 🗒 jet_controller.go
-
📁 db
- 🗒 sql.go
- 🗒 sql2.go
-
📁 statik
- 🗒 dummy.go
- 🗒 statik.go
- 🗒 testutils.go
- 🗒 transaction.go
-
📁 factories
- 🗒 factories.go
- 🗒 jets_factory.go
- 🗒 pilots_factory.go
- 🗒 go.mod
- 🗒 go.sum
- 🗒 main_with_template.go
- 🗒 main_with_template_in_bin.go
- 🗒 main_without_template.go
-
📁 models
- 🗒 jet_model.go
- 🗒 request.go
- 🗒 result.go
-
📁 repositories
- 🗒 jet_repository0.go
- 🗒 jet_repository1.go
- 🗒 jet_repository2.go
- 🗒 jet_repository_test.go
-
📁 sql
- 🗒 query.sql
-
📁 controllers
package controllers
import (
"context"
_ "github.com/go-sql-driver/mysql"
"gorp-tips/models"
"gorp-tips/repositories"
)
func GetJets(ctx context.Context, repo repositories.JetRepository, req models.Request) ([]models.Result, error) {
return repo.GetJets(ctx, req)
}
package db
import (
"bytes"
"text/template"
)
func GetSQL(filename string, req interface{}) string {
var buf bytes.Buffer
t := template.Must(template.ParseFiles("sql/" + filename))
t.Execute(&buf, req)
return buf.String()
}
package db
import (
"bytes"
"io/ioutil"
"os"
"text/template"
_ "gorp-tips/db/statik"
"github.com/rakyll/statik/fs"
)
//go:generate statik -f -src=../sql -m
var files, _ = fs.New()
func GetSQL2(filename string, req interface{}) string {
var buf bytes.Buffer
f, _ := files.Open(string(os.PathSeparator) + filename)
b, _ := ioutil.ReadAll(f)
t := template.Must(template.New(filename).Parse(string(b)))
t.Execute(&buf, req)
return buf.String()
}
package statik
// Code generated by statik. DO NOT EDIT.
package statik
import (
"github.com/rakyll/statik/fs"
)
func init() {
data := "PK\x03\x04\x14\x00\x08\x00\x08\x00\x00\x00!(\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00 \x00 \x00query.sqlUT\x05\x00\x01\x80Cm8|\x91?O\xc30\x14\xc4w\x7f\x8aS\xe76b.\xea\x10ZW\xb4\x84\x04\xb9A\x88\xa9\xb2\xc8\xc3r\x95&\x88\xc2\x14\xf5\xbb#;\xf1\x9f\x02\xcdv\xbe\xbc\xfb\xc5\xef\xbc\xe3\x19_\x96\x0c8\xd0\xd7)i\xe4\x91\x90\xee\xcc!\x97G\x9a:_*g\xa7*\xb8om\xdd~\x0e\xfe\xd2h\xf3\xe5C\xd7mD\xb2G\xc7\xaae\xa3\xbe\xa5\xa2S\xe2\x14[\x8b\xe2\xd1\xd2\xd8\xb6\xd8\xe4C\x1a\x85S\x89\xae\xb0\xe8\xfff\x8d\xbd\xaeX\xc6\xd7%\xc2\xf4\xdeS},X>5B\xb9\xc8\x87\xa4\xcd\xfc\xc69e\x08/\xf7\\p\x94\xe2\x993\xa0\xeb\xa0\xdf\xd1\x10\x92T\x11n0;\x9f\x19\x90\xe6\xabP\xe0\x02s\xb3\xb1\x99\x9d\x81\x9a\xcaNt\xdd\x10{rEa2\x89\xd2q\x9d\xd9\xe6\x81c\xde_\xc9\x18\xd7P[\xfa\x07\x14\xde\xb7\xc7\x1ch\x1c\x92\x0d\x9b^R\xfe6a\xd6\xf2\xaf\x19\xb3\n\xb1\xe2\x02w\xaf\xbe\x80i\xaft\xc5n\xd9O\x00\x00\x00\xff\xffPK\x07\x08%R\xf1\xa6\xfb\x00\x00\x00w\x02\x00\x00PK\x01\x02\x14\x03\x14\x00\x08\x00\x08\x00\x00\x00!(%R\xf1\xa6\xfb\x00\x00\x00w\x02\x00\x00 \x00 \x00\x00\x00\x00\x00\x00\x00\x00\x00\xa4\x81\x00\x00\x00\x00query.sqlUT\x05\x00\x01\x80Cm8PK\x05\x06\x00\x00\x00\x00\x01\x00\x01\x00@\x00\x00\x00;\x01\x00\x00\x00\x00"
fs.Register(data)
}
package db
import (
"context"
"database/sql"
"testing"
"gorp-tips/models"
"github.com/go-gorp/gorp"
_ "github.com/go-sql-driver/mysql"
)
// TestingBlock テスト対象のブロック(関数)
type TestingBlock func(ctx context.Context, tx *NestableTx)
// Dependency 依存レコード(自身を含む)
type Dependency interface{}
func initDb(t *testing.T) *gorp.DbMap {
db, err := sql.Open("mysql", "usr:pw@tcp(testing_mysql:3306)/db")
if err != nil {
t.Fatalf("Failed to connect db. %s", err)
}
dbmap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{Engine: "InnoDB", Encoding: "utf8mb4"}}
models.MapStructsToTables(dbmap)
return dbmap
}
// RunTest テストを実行する
func RunTest(ctx context.Context, t *testing.T, block TestingBlock, deps ...Dependency) {
dbmap := initDb(t)
defer dbmap.Db.Close()
// トランザクション作成
tx, err := dbmap.Begin()
if err != nil {
t.Errorf("Failed to start transaction. %w", err)
return
}
ntx := &NestableTx{Transaction: tx}
defer ntx.Rollback()
// dependencies 投入
for _, m := range deps {
if err := ntx.Insert(m); err != nil {
t.Fatalf("Failed to load dependencies. %s, %+v", err, m)
}
}
// テスト実行
block(ctx, ntx)
}
package db
import (
"strconv"
"github.com/go-gorp/gorp"
)
type NestableTx struct {
*gorp.Transaction
savePoint int
next *NestableTx
resolved bool
}
func (tx *NestableTx) Begin() (*NestableTx, error) {
tx.next = &NestableTx{
Transaction: tx.Transaction,
savePoint: tx.savePoint + 1,
}
if err := tx.Savepoint("SP" + strconv.Itoa(tx.next.savePoint)); err != nil {
return nil, err
}
return tx.next, nil
}
func (tx *NestableTx) Rollback() error {
tx.resolved = true
if tx.savePoint > 0 {
return tx.RollbackToSavepoint("SP" + strconv.Itoa(tx.savePoint))
}
return tx.Transaction.Rollback()
}
func (tx *NestableTx) Commit() error {
if tx.next != nil && !tx.next.resolved {
if err := tx.next.Commit(); err != nil {
return err
}
}
tx.resolved = true
if tx.savePoint > 0 {
return tx.ReleaseSavepoint("SP" + strconv.Itoa(tx.savePoint))
}
return tx.Transaction.Commit()
}
package factories
type Fields map[string]interface{}
package factories
import (
"fmt"
"github.com/bluele/factory-go/factory"
"gorp-tips/db"
"gorp-tips/models"
)
var JetFactory = factory.NewFactory(
&models.Jet{},
).SeqInt("ID", func(n int) (interface{}, error) {
return n, nil
}).Attr("Age", func(args factory.Args) (interface{}, error) {
return uint8(20), nil
}).SeqInt("Name", func(n int) (interface{}, error) {
return fmt.Sprintf("Jet-%d", n), nil
}).Attr("Color", func(args factory.Args) (interface{}, error) {
return "White", nil
})
// MakeJet Jetのファクトリを作る
func MakeJet(fields Fields, deps []db.Dependency) (*models.Jet, []db.Dependency) {
m := JetFactory.MustCreateWithOption(fields).(*models.Jet)
if m.PilotID == 0 {
pilot, _deps := MakePilot(nil, nil)
m.PilotID = pilot.ID
deps = append(deps, _deps...)
}
deps = append(deps, m)
return m, deps
}
package factories
import (
"github.com/bluele/factory-go/factory"
"gorp-tips/db"
"gorp-tips/models"
)
var PilotFactory = factory.NewFactory(
&models.Pilot{},
).SeqInt("ID", func(n int) (interface{}, error) {
return n, nil
}).Attr("Name", func(args factory.Args) (interface{}, error) {
return "Tester", nil
})
// MakePilot Pilotのファクトリを作る
func MakePilot(fields Fields, deps []db.Dependency) (*models.Pilot, []db.Dependency) {
m := PilotFactory.MustCreateWithOption(fields).(*models.Pilot)
deps = append(deps, m)
return m, deps
}
var LanguageFactory = factory.NewFactory(
&models.Language{},
).SeqInt("ID", func(n int) (interface{}, error) {
return n, nil
}).Attr("Language", func(args factory.Args) (interface{}, error) {
return "English", nil
})
// MakeLanguage Languageのファクトリを作る
func MakeLanguage(fields Fields, deps []db.Dependency) (*models.Language, []db.Dependency) {
m := LanguageFactory.MustCreateWithOption(fields).(*models.Language)
deps = append(deps, m)
return m, deps
}
var PilotLanguageFactory = factory.NewFactory(
&models.PilotLanguage{},
)
// MakePilotLanguage PilotLanguageのファクトリを作る
func MakePilotLanguage(fields Fields, deps []db.Dependency) (*models.PilotLanguage, []db.Dependency) {
m := PilotLanguageFactory.MustCreateWithOption(fields).(*models.PilotLanguage)
if m.PilotID == 0 {
pilot, _deps := MakePilot(nil, nil)
m.PilotID = pilot.ID
deps = append(deps, _deps...)
}
if m.LanguageID == 0 {
lang, _deps := MakeLanguage(nil, nil)
m.LanguageID = lang.ID
deps = append(deps, _deps...)
}
deps = append(deps, m)
return m, deps
}
module gorp-tips
go 1.14
require (
github.com/bluele/factory-go v0.0.0-20200430111232-df9c4ffc2e3e
github.com/go-gorp/gorp v2.2.0+incompatible
github.com/go-sql-driver/mysql v1.5.0
github.com/google/go-cmp v0.5.1
github.com/labstack/gommon v0.3.0
github.com/poy/onpar v1.0.0 // indirect
github.com/rakyll/statik v0.1.7
)
cloud.google.com/go v0.26.0/go.mod h1:aQUYkXzVsufM+DwF1aE+0xfcU+56JwCaLick0ClmMTw=
github.com/BurntSushi/toml v0.3.1/go.mod h1:xHWCNGjB5oqiDr8zfno3MHue2Ht5sIBksp03qcyfWMU=
github.com/OneOfOne/xxhash v1.2.2/go.mod h1:HSdplMjZKSmBqAxg5vPj2TmRDmfkzw+cTzAElWljhcU=
github.com/a8m/expect v1.0.0/go.mod h1:4IwSCMumY49ScypDnjNbYEjgVeqy1/U2cEs3Lat96eA=
github.com/alecthomas/template v0.0.0-20160405071501-a0175ee3bccc/go.mod h1:LOuyumcjzFXgccqObfd/Ljyb9UuFJ6TxHnclSeseNhc=
github.com/alecthomas/units v0.0.0-20151022065526-2efee857e7cf/go.mod h1:ybxpYRFXyAe+OPACYpWeL0wqObRcbAqCMya13uyzqw0=
github.com/armon/consul-api v0.0.0-20180202201655-eb2c6b5be1b6/go.mod h1:grANhF5doyWs3UAsr3K4I6qtAmlQcZDesFNEHPZAzj8=
github.com/beorn7/perks v0.0.0-20180321164747-3a771d992973/go.mod h1:Dwedo/Wpr24TaqPxmxbtue+5NUziq4I4S80YR8gNf3Q=
github.com/beorn7/perks v1.0.0/go.mod h1:KWe93zE9D1o94FZ5RNwFwVgaQK1VOXiVxmqh+CedLV8=
github.com/bluele/factory-go v0.0.0-20200430111232-df9c4ffc2e3e h1:jEw5WGmc8WiBfPb+XxavfbxPAgtPdIycxSjMN8svHzw=
github.com/bluele/factory-go v0.0.0-20200430111232-df9c4ffc2e3e/go.mod h1:C+/xfXxCR66wsm6I3Mzbf72W/Lz2NPsGQhSWDVBa5YU=
github.com/cespare/xxhash v1.1.0/go.mod h1:XrSqR1VqqWfGrhpAt58auRo0WTKS1nRRg3ghfAqPWnc=
github.com/client9/misspell v0.3.4/go.mod h1:qj6jICC3Q7zFZvVWo7KLAzC3yx5G7kyvSDkc90ppPyw=
github.com/coreos/bbolt v1.3.2/go.mod h1:iRUV2dpdMOn7Bo10OQBFzIJO9kkE559Wcmn+qkEiiKk=
github.com/coreos/etcd v3.3.10+incompatible/go.mod h1:uF7uidLiAD3TWHmW31ZFd/JWoc32PjwdhPthX9715RE=
github.com/coreos/go-semver v0.2.0/go.mod h1:nnelYz7RCh+5ahJtPPxZlU+153eP4D4r3EedlOD2RNk=
github.com/coreos/go-systemd v0.0.0-20190321100706-95778dfbb74e/go.mod h1:F5haX7vjVVG0kc13fIWeqUViNPyEJxv/OmvnBo0Yme4=
github.com/coreos/pkg v0.0.0-20180928190104-399ea9e2e55f/go.mod h1:E3G3o1h8I7cfcXa63jLwjI0eiQQMgzzUDFVpN/nH/eA=
github.com/cpuguy83/go-md2man/v2 v2.0.0/go.mod h1:maD7wRr/U5Z6m/iR4s+kqSMx2CaBsrgA7czyZG/E6dU=
github.com/davecgh/go-spew v1.1.0 h1:ZDRjVQ15GmhC3fiQ8ni8+OwkZQO4DARzQgrnXU1Liz8=
github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c=
github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
github.com/dgrijalva/jwt-go v3.2.0+incompatible/go.mod h1:E3ru+11k8xSBh+hMPgOLZmtrrCbhqsmaPHjLKYnJCaQ=
github.com/dgryski/go-sip13 v0.0.0-20181026042036-e10d5fee7954/go.mod h1:vAd38F8PWV+bWy6jNmig1y/TA+kYO4g3RSRF0IAv0no=
github.com/fatih/color v1.9.0/go.mod h1:eQcE1qtQxscV5RaZvpXrrb8Drkc3/DdQ+uUYCNjL+zU=
github.com/fsnotify/fsnotify v1.4.7/go.mod h1:jwhsz4b93w/PPRr/qN1Yymfu8t87LnFCMoQvtojpjFo=
github.com/ghodss/yaml v1.0.0/go.mod h1:4dBDuWmgqj2HViK6kFavaiC9ZROes6MMH2rRYeMEF04=
github.com/go-gorp/gorp v2.2.0+incompatible h1:xAUh4QgEeqPPhK3vxZN+bzrim1z5Av6q837gtjUlshc=
github.com/go-gorp/gorp v2.2.0+incompatible/go.mod h1:7IfkAQnO7jfT/9IQ3R9wL1dFhukN6aQxzKTHnkxzA/E=
github.com/go-kit/kit v0.8.0/go.mod h1:xBxKIO96dXMWWy0MnWVtmwkA9/13aqxPnvrjFYMA2as=
github.com/go-logfmt/logfmt v0.3.0/go.mod h1:Qt1PoO58o5twSAckw1HlFXLmHsOX5/0LbT9GBnD5lWE=
github.com/go-logfmt/logfmt v0.4.0/go.mod h1:3RMwSq7FuexP4Kalkev3ejPJsZTpXXBr9+V4qmtdjCk=
github.com/go-sql-driver/mysql v1.5.0 h1:ozyZYNQW3x3HtqT1jira07DN2PArx2v7/mN66gGcHOs=
github.com/go-sql-driver/mysql v1.5.0/go.mod h1:DCzpHaOWr8IXmIStZouvnhqoel9Qv2LBy8hT2VhHyBg=
github.com/go-stack/stack v1.8.0/go.mod h1:v0f6uXyyMGvRgIKkXu+yp6POWl0qKG85gN/melR3HDY=
github.com/gogo/protobuf v1.1.1/go.mod h1:r8qH/GZQm5c6nD/R0oafs1akxWv10x8SbQlK7atdtwQ=
github.com/gogo/protobuf v1.2.1/go.mod h1:hp+jE20tsWTFYpLwKvXlhS1hjn+gTNwPg2I6zVXpSg4=
github.com/golang/glog v0.0.0-20160126235308-23def4e6c14b/go.mod h1:SBH7ygxi8pfUlaOkMMuAQtPIUF8ecWP5IEl/CR7VP2Q=
github.com/golang/groupcache v0.0.0-20190129154638-5b532d6fd5ef/go.mod h1:cIg4eruTrX1D+g88fzRXU5OdNfaM+9IcxsU14FzY7Hc=
github.com/golang/mock v1.1.1/go.mod h1:oTYuIxOrZwtPieC+H1uAHpcLFnEyAGVDL/k47Jfbm0A=
github.com/golang/protobuf v1.2.0/go.mod h1:6lQm79b+lXiMfvg/cZm0SGofjICqVBUtrP5yJMmIC1U=
github.com/golang/protobuf v1.3.1/go.mod h1:6lQm79b+lXiMfvg/cZm0SGofjICqVBUtrP5yJMmIC1U=
github.com/google/btree v1.0.0/go.mod h1:lNA+9X1NB3Zf8V7Ke586lFgjr2dZNuvo3lPJSGZ5JPQ=
github.com/google/go-cmp v0.2.0/go.mod h1:oXzfMopK8JAjlY9xF4vHSVASa0yLyX7SntLO5aqRK0M=
github.com/google/go-cmp v0.5.1 h1:JFrFEBb2xKufg6XkJsJr+WbKb4FQlURi5RUcBveYu9k=
github.com/google/go-cmp v0.5.1/go.mod h1:v8dTdLbMG2kIc/vJvl+f65V22dbkXbowE6jgT/gNBxE=
github.com/gorilla/websocket v1.4.0/go.mod h1:E7qHFY5m1UJ88s3WnNqhKjPHQ0heANvMoAMk2YaljkQ=
github.com/grpc-ecosystem/go-grpc-middleware v1.0.0/go.mod h1:FiyG127CGDf3tlThmgyCl78X/SZQqEOJBCDaAfeWzPs=
github.com/grpc-ecosystem/go-grpc-prometheus v1.2.0/go.mod h1:8NvIoxWQoOIhqOTXgfV/d3M/q6VIi02HzZEHgUlZvzk=
github.com/grpc-ecosystem/grpc-gateway v1.9.0/go.mod h1:vNeuVxBJEsws4ogUvrchl83t/GYV9WGTSLVdBhOQFDY=
github.com/hashicorp/hcl v1.0.0/go.mod h1:E5yfLk+7swimpb2L/Alb/PJmXilQ/rhwaUYs4T20WEQ=
github.com/inconshreveable/mousetrap v1.0.0/go.mod h1:PxqpIevigyE2G7u3NXJIT2ANytuPF1OarO4DADm73n8=
github.com/jonboulle/clockwork v0.1.0/go.mod h1:Ii8DK3G1RaLaWxj9trq07+26W01tbo22gdxWY5EU2bo=
github.com/julienschmidt/httprouter v1.2.0/go.mod h1:SYymIcj16QtmaHHD7aYtjjsJG7VTCxuUUipMqKk8s4w=
github.com/kisielk/errcheck v1.1.0/go.mod h1:EZBBE59ingxPouuu3KfxchcWSUPOHkagtvWXihfKN4Q=
github.com/kisielk/gotool v1.0.0/go.mod h1:XhKaO+MFFWcvkIS/tQcRk01m1F5IRFswLeQ+oQHNcck=
github.com/konsorten/go-windows-terminal-sequences v1.0.1/go.mod h1:T0+1ngSBFLxvqU3pZ+m/2kptfBszLMUkC4ZK/EgS/cQ=
github.com/kr/logfmt v0.0.0-20140226030751-b84e30acd515/go.mod h1:+0opPa2QZZtGFBFZlji/RkVcI2GknAs/DXo4wKdlNEc=
github.com/kr/pretty v0.1.0/go.mod h1:dAy3ld7l9f0ibDNOQOHHMYYIIbhfbHSm3C4ZsoJORNo=
github.com/kr/pty v1.1.1/go.mod h1:pFQYn66WHrOpPYNljwOMqo10TkYh1fy3cYio2l3bCsQ=
github.com/kr/text v0.1.0/go.mod h1:4Jbv+DJW3UT/LiOwJeYQe1efqtUx/iVham/4vfdArNI=
github.com/labstack/gommon v0.3.0 h1:JEeO0bvc78PKdyHxloTKiF8BD5iGrH8T6MSeGvSgob0=
github.com/labstack/gommon v0.3.0/go.mod h1:MULnywXg0yavhxWKc+lOruYdAhDwPK9wf0OL7NoOu+k=
github.com/magiconair/properties v1.8.0/go.mod h1:PppfXfuXeibc/6YijjN8zIbojt8czPbwD3XqdrwzmxQ=
github.com/mattn/go-colorable v0.1.2 h1:/bC9yWikZXAL9uJdulbSfyVNIR3n3trXl+v8+1sx8mU=
github.com/mattn/go-colorable v0.1.2/go.mod h1:U0ppj6V5qS13XJ6of8GYAs25YV2eR4EVcfRqFIhoBtE=
github.com/mattn/go-colorable v0.1.4 h1:snbPLB8fVfU9iwbbo30TPtbLRzwWu6aJS6Xh4eaaviA=
github.com/mattn/go-colorable v0.1.4/go.mod h1:U0ppj6V5qS13XJ6of8GYAs25YV2eR4EVcfRqFIhoBtE=
github.com/mattn/go-isatty v0.0.8/go.mod h1:Iq45c/XA43vh69/j3iqttzPXn0bhXyGjM0Hdxcsrc5s=
github.com/mattn/go-isatty v0.0.9 h1:d5US/mDsogSGW37IV293h//ZFaeajb69h+EHFsv2xGg=
github.com/mattn/go-isatty v0.0.9/go.mod h1:YNRxwqDuOph6SZLI9vUUz6OYw3QyUt7WiY2yME+cCiQ=
github.com/mattn/go-isatty v0.0.11 h1:FxPOTFNqGkuDUGi3H/qkUbQO4ZiBa2brKq5r0l8TGeM=
github.com/mattn/go-isatty v0.0.11/go.mod h1:PhnuNfih5lzO57/f3n+odYbM4JtupLOxQOAqxQCu2WE=
github.com/matttproud/golang_protobuf_extensions v1.0.1/go.mod h1:D8He9yQNgCq6Z5Ld7szi9bcBfOoFv/3dc6xSMkL2PC0=
github.com/mitchellh/go-homedir v1.1.0/go.mod h1:SfyaCUpYCn1Vlf4IUYiD9fPX4A5wJrkLzIz1N1q0pr0=
github.com/mitchellh/mapstructure v1.1.2/go.mod h1:FVVH3fgwuzCH5S8UJGiWEs2h04kUh9fWfEaFds41c1Y=
github.com/mwitkow/go-conntrack v0.0.0-20161129095857-cc309e4a2223/go.mod h1:qRWi+5nqEBWmkhHvq77mSJWrCKwh8bxhgT7d/eI7P4U=
github.com/nelsam/hel/v2 v2.3.2/go.mod h1:1ZTGfU2PFTOd5mx22i5O0Lc2GY933lQ2wb/ggy+rL3w=
github.com/oklog/ulid v1.3.1/go.mod h1:CirwcVhetQ6Lv90oh/F+FBtV6XMibvdAFo93nm5qn4U=
github.com/pelletier/go-toml v1.2.0/go.mod h1:5z9KED0ma1S8pY6P1sdut58dfprrGBbd/94hg7ilaic=
github.com/pkg/errors v0.8.0/go.mod h1:bwawxfHBFNV+L2hUp1rHADufV3IMtnDRdf1r5NINEl0=
github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM=
github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4=
github.com/poy/onpar v0.0.0-20200406201722-06f95a1c68e8/go.mod h1:nSbFQvMj97ZyhFRSJYtut+msi4sOY6zJDGCdSc+/rZU=
github.com/poy/onpar v1.0.0 h1:MfdQ9bnas+J1si8vUHAABXKxqOqDVaH4T3LRDYYv5Lo=
github.com/poy/onpar v1.0.0/go.mod h1:6X8FLNoxyr9kkmnlqpK6LSoiOtrO6MICtWwEuWkLjzg=
github.com/prometheus/client_golang v0.9.1/go.mod h1:7SWBe2y4D6OKWSNQJUaRYU/AaXPKyh/dDVn+NZz0KFw=
github.com/prometheus/client_golang v0.9.3/go.mod h1:/TN21ttK/J9q6uSwhBd54HahCDft0ttaMvbicHlPoso=
github.com/prometheus/client_model v0.0.0-20180712105110-5c3871d89910/go.mod h1:MbSGuTsp3dbXC40dX6PRTWyKYBIrTGTE9sqQNg2J8bo=
github.com/prometheus/client_model v0.0.0-20190129233127-fd36f4220a90/go.mod h1:xMI15A0UPsDsEKsMN9yxemIoYk6Tm2C1GtYGdfGttqA=
github.com/prometheus/common v0.0.0-20181113130724-41aa239b4cce/go.mod h1:daVV7qP5qjZbuso7PdcryaAu0sAZbrN9i7WWcTMWvro=
github.com/prometheus/common v0.4.0/go.mod h1:TNfzLD0ON7rHzMJeJkieUDPYmFC7Snx/y86RQel1bk4=
github.com/prometheus/procfs v0.0.0-20181005140218-185b4288413d/go.mod h1:c3At6R/oaqEKCNdg8wHV1ftS6bRYblBhIjjI8uT2IGk=
github.com/prometheus/procfs v0.0.0-20190507164030-5867b95ac084/go.mod h1:TjEm7ze935MbeOT/UhFTIMYKhuLP4wbCsTZCD3I8kEA=
github.com/prometheus/tsdb v0.7.1/go.mod h1:qhTCs0VvXwvX/y3TZrWD7rabWM+ijKTux40TwIPHuXU=
github.com/rakyll/statik v0.1.7 h1:OF3QCZUuyPxuGEP7B4ypUa7sB/iHtqOTDYZXGM8KOdQ=
github.com/rakyll/statik v0.1.7/go.mod h1:AlZONWzMtEnMs7W4e/1LURLiI49pIMmp6V9Unghqrcc=
github.com/rogpeppe/fastuuid v0.0.0-20150106093220-6724a57986af/go.mod h1:XWv6SoW27p1b0cqNHllgS5HIMJraePCO15w5zCzIWYg=
github.com/russross/blackfriday/v2 v2.0.1/go.mod h1:+Rmxgy9KzJVeS9/2gXHxylqXiyQDYRxCVz55jmeOWTM=
github.com/shurcooL/sanitized_anchor_name v1.0.0/go.mod h1:1NzhyTcUVG4SuEtjjoZeVRXNmyL/1OwPU0+IJeTBvfc=
github.com/sirupsen/logrus v1.2.0/go.mod h1:LxeOpSwHxABJmUn/MG1IvRgCAasNZTLOkJPxbbu5VWo=
github.com/soheilhy/cmux v0.1.4/go.mod h1:IM3LyeVVIOuxMH7sFAkER9+bJ4dT7Ms6E4xg4kGIyLM=
github.com/spaolacci/murmur3 v0.0.0-20180118202830-f09979ecbc72/go.mod h1:JwIasOWyU6f++ZhiEuf87xNszmSA2myDM2Kzu9HwQUA=
github.com/spf13/afero v1.1.2/go.mod h1:j4pytiNVoe2o6bmDsKpLACNPDBIoEAkihy7loJ1B0CQ=
github.com/spf13/cast v1.3.0/go.mod h1:Qx5cxh0v+4UWYiBimWS+eyWzqEqokIECu5etghLkUJE=
github.com/spf13/cobra v0.0.6/go.mod h1:/6GTrnGXV9HjY+aR4k0oJ5tcvakLuG6EuKReYlHNrgE=
github.com/spf13/jwalterweatherman v1.0.0/go.mod h1:cQK4TGJAtQXfYWX+Ddv3mKDzgVb68N+wFjFa4jdeBTo=
github.com/spf13/pflag v1.0.3/go.mod h1:DYY7MBk1bdzusC3SYhjObp+wFpr4gzcvqqNjLnInEg4=
github.com/spf13/viper v1.4.0/go.mod h1:PTJ7Z/lr49W6bUbkmS1V3by4uWynFiR9p7+dSq/yZzE=
github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
github.com/stretchr/objx v0.1.1/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
github.com/stretchr/testify v1.2.2/go.mod h1:a8OnRcib4nhh0OaRAV+Yts87kKdq0PP7pXfy6kDkUVs=
github.com/stretchr/testify v1.4.0 h1:2E4SXV/wtOkTonXsotYi4li6zVWxYlZuYNCXe9XRJyk=
github.com/stretchr/testify v1.4.0/go.mod h1:j7eGeouHqKxXV5pUuKE4zz7dFj8WfuZ+81PSLYec5m4=
github.com/tmc/grpc-websocket-proxy v0.0.0-20190109142713-0ad062ec5ee5/go.mod h1:ncp9v5uamzpCO7NfCPTXjqaC+bZgJeR0sMTm6dMHP7U=
github.com/ugorji/go v1.1.4/go.mod h1:uQMGLiO92mf5W77hV/PUCpI3pbzQx3CRekS0kk+RGrc=
github.com/valyala/bytebufferpool v1.0.0 h1:GqA5TC/0021Y/b9FG4Oi9Mr3q7XYx6KllzawFIhcdPw=
github.com/valyala/bytebufferpool v1.0.0/go.mod h1:6bBcMArwyJ5K/AmCkWv1jt77kVWyCJ6HpOuEn7z0Csc=
github.com/valyala/fasttemplate v1.0.1 h1:tY9CJiPnMXf1ERmG2EyK7gNUd+c6RKGD0IfU8WdUSz8=
github.com/valyala/fasttemplate v1.0.1/go.mod h1:UQGH1tvbgY+Nz5t2n7tXsz52dQxojPUpymEIMZ47gx8=
github.com/xiang90/probing v0.0.0-20190116061207-43a291ad63a2/go.mod h1:UETIi67q53MR2AWcXfiuqkDkRtnGDLqkBTpCHuJHxtU=
github.com/xordataexchange/crypt v0.0.3-0.20170626215501-b2862e3d0a77/go.mod h1:aYKd//L2LvnjZzWKhF00oedf4jCCReLcmhLdhm1A27Q=
github.com/yuin/goldmark v1.1.25/go.mod h1:3hX8gzYuyVAZsxl0MRgGTJEmQBFcNTphYh9decYSb74=
go.etcd.io/bbolt v1.3.2/go.mod h1:IbVyRI1SCnLcuJnV2u8VeU0CEYM7e686BmAb1XKL+uU=
go.uber.org/atomic v1.4.0/go.mod h1:gD2HeocX3+yG+ygLZcrzQJaqmWj9AIm7n08wl/qW/PE=
go.uber.org/multierr v1.1.0/go.mod h1:wR5kodmAFQ0UK8QlbwjlSNy0Z68gJhDJUG5sjR94q/0=
go.uber.org/zap v1.10.0/go.mod h1:vwi/ZaCAaUcBkycHslxD9B2zi4UTXhF60s6SWpuDF0Q=
golang.org/x/crypto v0.0.0-20180904163835-0709b304e793/go.mod h1:6SG95UA2DQfeDnfUPMdvaQW0Q7yPrPDi9nlGo2tz2b4=
golang.org/x/crypto v0.0.0-20190308221718-c2843e01d9a2/go.mod h1:djNgcEr1/C05ACkg1iLfiJU5Ep61QUkGW8qpdssI0+w=
golang.org/x/crypto v0.0.0-20191011191535-87dc89f01550/go.mod h1:yigFU9vqHzYiE8UmvKecakEJjdnWj3jj499lnFckfCI=
golang.org/x/lint v0.0.0-20181026193005-c67002cb31c3/go.mod h1:UVdnD1Gm6xHRNCYTkRU2/jEulfH38KcIWyp/GAMgvoE=
golang.org/x/lint v0.0.0-20190313153728-d0100b6bd8b3/go.mod h1:6SW0HCj/g11FgYtHlgUYUwCkIfeOF89ocIRzGO/8vkc=
golang.org/x/mod v0.2.0/go.mod h1:s0Qsj1ACt9ePp/hMypM3fl4fZqREWJwdYDEqhRiZZUA=
golang.org/x/net v0.0.0-20180826012351-8a410e7b638d/go.mod h1:mL1N/T3taQHkDXs73rZJwtUhF3w3ftmwwsq0BUmARs4=
golang.org/x/net v0.0.0-20181114220301-adae6a3d119a/go.mod h1:mL1N/T3taQHkDXs73rZJwtUhF3w3ftmwwsq0BUmARs4=
golang.org/x/net v0.0.0-20181220203305-927f97764cc3/go.mod h1:mL1N/T3taQHkDXs73rZJwtUhF3w3ftmwwsq0BUmARs4=
golang.org/x/net v0.0.0-20190311183353-d8887717615a/go.mod h1:t9HGtf8HONx5eT2rtn7q6eTqICYqUVnKs3thJo3Qplg=
golang.org/x/net v0.0.0-20190404232315-eb5bcb51f2a3/go.mod h1:t9HGtf8HONx5eT2rtn7q6eTqICYqUVnKs3thJo3Qplg=
golang.org/x/net v0.0.0-20190522155817-f3200d17e092/go.mod h1:HSz+uSET+XFnRR8LxR5pz3Of3rY3CfYBVs4xY44aLks=
golang.org/x/net v0.0.0-20190620200207-3b0461eec859/go.mod h1:z5CRVTTTmAJ677TzLLGU+0bjPO0LkuOLi4/5GtJWs/s=
golang.org/x/net v0.0.0-20200226121028-0de0cce0169b/go.mod h1:z5CRVTTTmAJ677TzLLGU+0bjPO0LkuOLi4/5GtJWs/s=
golang.org/x/oauth2 v0.0.0-20180821212333-d2e6202438be/go.mod h1:N/0e6XlmueqKjAGxoOufVs8QHGRruUQn6yWY3a++T0U=
golang.org/x/sync v0.0.0-20180314180146-1d60e4601c6f/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM=
golang.org/x/sync v0.0.0-20181108010431-42b317875d0f/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM=
golang.org/x/sync v0.0.0-20181221193216-37e7f081c4d4/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM=
golang.org/x/sync v0.0.0-20190423024810-112230192c58/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM=
golang.org/x/sync v0.0.0-20190911185100-cd5d95a43a6e/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM=
golang.org/x/sys v0.0.0-20180830151530-49385e6e1522/go.mod h1:STP8DvDyc/dI5b8T5hshtkjS+E42TnysNCUPdjciGhY=
golang.org/x/sys v0.0.0-20180905080454-ebe1bf3edb33/go.mod h1:STP8DvDyc/dI5b8T5hshtkjS+E42TnysNCUPdjciGhY=
golang.org/x/sys v0.0.0-20181107165924-66b7b1311ac8/go.mod h1:STP8DvDyc/dI5b8T5hshtkjS+E42TnysNCUPdjciGhY=
golang.org/x/sys v0.0.0-20181116152217-5ac8a444bdc5/go.mod h1:STP8DvDyc/dI5b8T5hshtkjS+E42TnysNCUPdjciGhY=
golang.org/x/sys v0.0.0-20190215142949-d0b11bdaac8a/go.mod h1:STP8DvDyc/dI5b8T5hshtkjS+E42TnysNCUPdjciGhY=
golang.org/x/sys v0.0.0-20190222072716-a9d3bda3a223/go.mod h1:STP8DvDyc/dI5b8T5hshtkjS+E42TnysNCUPdjciGhY=
golang.org/x/sys v0.0.0-20190412213103-97732733099d/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
golang.org/x/sys v0.0.0-20190813064441-fde4db37ae7a h1:aYOabOQFp6Vj6W1F80affTUvO9UxmJRx8K0gsfABByQ=
golang.org/x/sys v0.0.0-20190813064441-fde4db37ae7a/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
golang.org/x/sys v0.0.0-20191026070338-33540a1f6037 h1:YyJpGZS1sBuBCzLAR1VEpK193GlqGZbnPFnPV/5Rsb4=
golang.org/x/sys v0.0.0-20191026070338-33540a1f6037/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
golang.org/x/text v0.3.0/go.mod h1:NqM8EUOU14njkJ3fqMW+pc6Ldnwhi/IjpwHt7yyuwOQ=
golang.org/x/time v0.0.0-20190308202827-9d24e82272b4/go.mod h1:tRJNPiyCQ0inRvYxbN9jk5I+vvW/OXSQhTDSoE431IQ=
golang.org/x/tools v0.0.0-20180221164845-07fd8470d635/go.mod h1:n7NCudcB/nEzxVGmLbDWY5pfWTLqBcC2KZ6jyYvM4mQ=
golang.org/x/tools v0.0.0-20190114222345-bf090417da8b/go.mod h1:n7NCudcB/nEzxVGmLbDWY5pfWTLqBcC2KZ6jyYvM4mQ=
golang.org/x/tools v0.0.0-20190311212946-11955173bddd/go.mod h1:LCzVGOaR6xXOjkQ3onu1FJEFr0SW1gC7cKk1uF8kGRs=
golang.org/x/tools v0.0.0-20191119224855-298f0cb1881e/go.mod h1:b+2E5dAYhXwXZwtnZ6UAqBI28+e2cm9otk0dWdXHAEo=
golang.org/x/tools v0.0.0-20200313205530-4303120df7d8/go.mod h1:Sl4aGygMT6LrqrWclx+PTx3U+LnKx/seiNR+3G19Ar8=
golang.org/x/xerrors v0.0.0-20190717185122-a985d3407aa7/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
golang.org/x/xerrors v0.0.0-20191011141410-1b5146add898/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
golang.org/x/xerrors v0.0.0-20191204190536-9bdfabe68543 h1:E7g+9GITq07hpfrRu66IVDexMakfv52eLZ2CXBWiKr4=
golang.org/x/xerrors v0.0.0-20191204190536-9bdfabe68543/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
google.golang.org/appengine v1.1.0/go.mod h1:EbEs0AVv82hx2wNQdGPgUI5lhzA/G0D9YwlJXL52JkM=
google.golang.org/genproto v0.0.0-20180817151627-c66870c02cf8/go.mod h1:JiN7NxoALGmiZfu7CAH4rXhgtRTLTxftemlI0sWmxmc=
google.golang.org/grpc v1.19.0/go.mod h1:mqu4LbDTu4XGKhr4mRzUsmM4RtVoemTSY81AxZiDr8c=
google.golang.org/grpc v1.21.0/go.mod h1:oYelfM1adQP15Ek0mdvEgi9Df8B9CZIaU1084ijfRaM=
gopkg.in/alecthomas/kingpin.v2 v2.2.6/go.mod h1:FMv+mEhP44yOT+4EoQTLFTRgOQ1FBLkstjWtayDeSgw=
gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0=
gopkg.in/check.v1 v1.0.0-20180628173108-788fd7840127/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0=
gopkg.in/resty.v1 v1.12.0/go.mod h1:mDo4pnntr5jdWRML875a/NmxYqAlA73dVijT2AXvQQo=
gopkg.in/yaml.v2 v2.0.0-20170812160011-eb3733d160e7/go.mod h1:JAlM8MvJe8wmxCU4Bli9HhUf9+ttbYbLASfIpnQbh74=
gopkg.in/yaml.v2 v2.2.1/go.mod h1:hI93XBmqTisBFMUTm0b8Fm+jr3Dg1NNxqwp+5A1VGuI=
gopkg.in/yaml.v2 v2.2.2 h1:ZCJp+EgiOT7lHqUV2J862kp8Qj64Jo6az82+3Td9dZw=
gopkg.in/yaml.v2 v2.2.2/go.mod h1:hI93XBmqTisBFMUTm0b8Fm+jr3Dg1NNxqwp+5A1VGuI=
honnef.co/go/tools v0.0.0-20190102054323-c2f93a96b099/go.mod h1:rf3lG4BRIbNafJWhAfAdb/ePZxsR/4RtNHQocxwk9r4=
package main
import (
"context"
"database/sql"
"flag"
"fmt"
"gorp-tips/controllers"
"gorp-tips/models"
"gorp-tips/repositories"
"github.com/go-gorp/gorp"
_ "github.com/go-sql-driver/mysql"
"github.com/labstack/gommon/log"
)
func initDb() *gorp.DbMap {
db, _ := sql.Open("mysql", "usr:pw@tcp(mysql:3306)/db")
dbmap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{Engine: "InnoDB", Encoding: "utf8mb4"}}
models.MapStructsToTables(dbmap)
return dbmap
}
func main() {
dbmap := initDb()
defer dbmap.Db.Close()
var (
age = flag.Int("age", 0, "jet age")
pilotName = flag.String("pilot_name", "", "pilot name")
jetName = flag.String("jet_name", "", "jet name")
language = flag.String("language", "", "language")
)
flag.Parse()
req := models.Request{
Age: *age,
PilotName: *pilotName,
JetName: *jetName,
Language: *language,
}
repo := repositories.NewJetRepository1(dbmap)
results, err := controllers.GetJets(context.Background(), repo, req)
if err != nil {
log.Fatal(err)
return
}
for _, record := range results {
fmt.Printf("%+v\n", record)
}
}
package main
import (
"context"
"database/sql"
"flag"
"fmt"
"github.com/go-gorp/gorp"
_ "github.com/go-sql-driver/mysql"
"github.com/labstack/gommon/log"
"gorp-tips/controllers"
"gorp-tips/models"
"gorp-tips/repositories"
)
func initDb() *gorp.DbMap {
db, _ := sql.Open("mysql", "usr:pw@tcp(mysql:3306)/db")
dbmap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{Engine: "InnoDB", Encoding: "utf8mb4"}}
models.MapStructsToTables(dbmap)
return dbmap
}
func main() {
dbmap := initDb()
defer dbmap.Db.Close()
var (
age = flag.Int("age", 0, "jet age")
pilotName = flag.String("pilot_name", "", "pilot name")
jetName = flag.String("jet_name", "", "jet name")
language = flag.String("language", "", "language")
)
flag.Parse()
req := models.Request{
Age: *age,
PilotName: *pilotName,
JetName: *jetName,
Language: *language,
}
repo := repositories.NewJetRepository2(dbmap)
results, err := controllers.GetJets(context.Background(), repo, req)
if err != nil {
log.Fatal(err)
return
}
for _, record := range results {
fmt.Printf("%+v\n", record)
}
}
package main
import (
"context"
"database/sql"
"flag"
"fmt"
"gorp-tips/controllers"
"gorp-tips/models"
"gorp-tips/repositories"
"github.com/go-gorp/gorp"
_ "github.com/go-sql-driver/mysql"
"github.com/labstack/gommon/log"
)
func initDb() *gorp.DbMap {
db, _ := sql.Open("mysql", "usr:pw@tcp(mysql:3306)/db")
dbmap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{Engine: "InnoDB", Encoding: "utf8mb4"}}
models.MapStructsToTables(dbmap)
return dbmap
}
func main() {
dbmap := initDb()
defer dbmap.Db.Close()
var (
age = flag.Int("age", 0, "jet age")
pilotName = flag.String("pilot_name", "", "pilot name")
jetName = flag.String("jet_name", "", "jet name")
language = flag.String("language", "", "language")
)
flag.Parse()
req := models.Request{
Age: *age,
PilotName: *pilotName,
JetName: *jetName,
Language: *language,
}
repo := repositories.NewJetRepository0(dbmap)
results, err := controllers.GetJets(context.Background(), repo, req)
if err != nil {
log.Fatal(err)
return
}
for _, record := range results {
fmt.Printf("%+v\n", record)
}
}
package models
import (
"github.com/go-gorp/gorp"
)
type Jet struct {
ID int `db:"id"`
PilotID int `db:"pilot_id"`
Age uint8 `db:"age"`
Name string `db:"name"`
Color string `db:"color"`
}
type Pilot struct {
ID int `db:"id"`
Name string `db:"name"`
}
type Language struct {
ID int `db:"id"`
Language string `db:"language"`
}
type PilotLanguage struct {
PilotID int `db:"pilot_id"`
LanguageID int `db:"language_id"`
}
// MapStructsToTables 構造体と物理テーブルの紐付け
func MapStructsToTables(dbmap *gorp.DbMap) {
dbmap.AddTableWithName(Pilot{}, "pilots")
dbmap.AddTableWithName(Jet{}, "jets")
dbmap.AddTableWithName(Language{}, "languages")
dbmap.AddTableWithName(PilotLanguage{}, "pilot_languages")
}
package models
type Request struct {
Age int
PilotName string
JetName string
Language string
}
package models
type Result struct {
JetName string
JetAge uint8
JetColor string
PilotName string
Language *string
}
package repositories
import (
"context"
"strings"
"github.com/go-gorp/gorp"
"github.com/labstack/gommon/log"
"gorp-tips/models"
)
type JetRepository interface {
GetJets(ctx context.Context, req models.Request) ([]models.Result, error)
}
type jetRepository0 struct {
exec gorp.SqlExecutor
}
func NewJetRepository0(exec gorp.SqlExecutor) JetRepository {
return &jetRepository0{
exec: exec,
}
}
func (r *jetRepository0) GetJets(ctx context.Context, req models.Request) ([]models.Result, error) {
query := "SELECT jets.name AS jetName, jets.age AS jetAge, jets.color AS jetColor, pilots.name AS pilotName, languages.language "
query += "FROM jets "
query += "JOIN pilots ON pilots.id = jets.pilot_id "
query += "LEFT JOIN pilot_languages ON pilot_languages.pilot_id = jets.pilot_id "
query += "LEFT JOIN languages ON languages.id = pilot_languages.language_id "
conds, variables := makeCondition(req)
if conds != "" {
query += "WHERE " + conds
}
query += " ORDER BY jets.age, jets.id"
log.Debug(query)
var results []models.Result
if _, err := r.exec.Select(&results, query, variables); err != nil {
log.Error(err)
return nil, err
}
return results, nil
}
func makeCondition(req models.Request) (string, map[string]interface{}) {
conds := []string{}
context := map[string]interface{}{}
if req.Age > 0 {
conds = append(conds, "jets.age = :age")
context["age"] = req.Age
}
if req.PilotName != "" {
conds = append(conds, "pilots.name LIKE :pilot_name")
context["pilot_name"] = "%" + req.PilotName + "%"
}
if req.JetName != "" {
conds = append(conds, "jets.name LIKE :jet_name")
context["jet_name"] = "%" + req.JetName + "%"
}
if req.Language != "" {
conds = append(conds, "languages.language = :language")
context["language"] = req.Language
}
return strings.Join(conds, " AND "), context
}
package repositories
import (
"context"
"github.com/go-gorp/gorp"
"github.com/labstack/gommon/log"
"gorp-tips/db"
"gorp-tips/models"
)
type jetRepository1 struct {
exec gorp.SqlExecutor
}
func NewJetRepository1(exec gorp.SqlExecutor) JetRepository {
return &jetRepository1{
exec: exec,
}
}
func (r *jetRepository1) GetJets(ctx context.Context, req models.Request) ([]models.Result, error) {
query := db.GetSQL("query.sql", req)
log.Debug(query)
var results []models.Result
if _, err := r.exec.Select(&results, query, map[string]interface{}{
"age": req.Age,
"pilot_name": "%" + req.PilotName + "%",
"jet_name": "%" + req.JetName + "%",
"language": req.Language,
}); err != nil {
log.Error(err)
return nil, err
}
return results, nil
}
package repositories
import (
"context"
"github.com/go-gorp/gorp"
"github.com/labstack/gommon/log"
"gorp-tips/db"
"gorp-tips/models"
)
type jetRepository2 struct {
exec gorp.SqlExecutor
}
func NewJetRepository2(exec gorp.SqlExecutor) JetRepository {
return &jetRepository2{
exec: exec,
}
}
func (r *jetRepository2) GetJets(ctx context.Context, req models.Request) ([]models.Result, error) {
query := db.GetSQL2("query.sql", req)
log.Debug(query)
var results []models.Result
if _, err := r.exec.Select(&results, query, map[string]interface{}{
"age": req.Age,
"pilot_name": "%" + req.PilotName + "%",
"jet_name": "%" + req.JetName + "%",
"language": req.Language,
}); err != nil {
log.Error(err)
return nil, err
}
return results, nil
}
package repositories_test
import (
"context"
"testing"
"github.com/google/go-cmp/cmp"
"gorp-tips/db"
"gorp-tips/factories"
"gorp-tips/models"
"gorp-tips/repositories"
)
func TestRepository(t *testing.T) {
var deps []db.Dependency
ken, deps := factories.MakePilot(factories.Fields{"Name": "Ken"}, deps)
kyle, deps := factories.MakePilot(factories.Fields{"Name": "Kyle"}, deps)
kim, deps := factories.MakePilot(factories.Fields{"Name": "Kim"}, deps)
jp, deps := factories.MakeLanguage(factories.Fields{"Language": "Japanese"}, deps)
en, deps := factories.MakeLanguage(factories.Fields{"Language": "English"}, deps)
kr, deps := factories.MakeLanguage(factories.Fields{"Language": "Korean"}, deps)
_, deps = factories.MakePilotLanguage(factories.Fields{"PilotID": ken.ID, "LanguageID": jp.ID}, deps)
_, deps = factories.MakePilotLanguage(factories.Fields{"PilotID": kyle.ID, "LanguageID": jp.ID}, deps)
_, deps = factories.MakePilotLanguage(factories.Fields{"PilotID": kyle.ID, "LanguageID": en.ID}, deps)
_, deps = factories.MakePilotLanguage(factories.Fields{"PilotID": kim.ID, "LanguageID": jp.ID}, deps)
_, deps = factories.MakePilotLanguage(factories.Fields{"PilotID": kim.ID, "LanguageID": kr.ID}, deps)
falcon, deps := factories.MakeJet(factories.Fields{"Age": uint8(40), "Name": "Falcon", "PilotID": ken.ID}, deps)
hawk, deps := factories.MakeJet(factories.Fields{"Age": uint8(30), "Name": "Hawk", "PilotID": kyle.ID}, deps)
swallow, deps := factories.MakeJet(factories.Fields{"Age": uint8(20), "Name": "Swallow", "PilotID": kyle.ID}, deps)
dove, deps := factories.MakeJet(factories.Fields{"Age": uint8(10), "Name": "Dove", "Color": "gray"}, deps)
eagle, deps := factories.MakeJet(factories.Fields{"Age": uint8(10), "Name": "Eagle", "PilotID": kim.ID}, deps)
cases := []struct {
name string
req models.Request
expected []models.Result
}{
{
name: "age filter",
req: models.Request{Age: 10},
expected: []models.Result{
{JetName: dove.Name, JetAge: 10, JetColor: dove.Color, PilotName: "Tester", Language: nil},
{JetName: eagle.Name, JetAge: 10, JetColor: eagle.Color, PilotName: kim.Name, Language: &jp.Language},
{JetName: eagle.Name, JetAge: 10, JetColor: eagle.Color, PilotName: kim.Name, Language: &kr.Language},
},
},
{
name: "pilot name filter",
req: models.Request{PilotName: "en"},
expected: []models.Result{
{JetName: falcon.Name, JetAge: falcon.Age, JetColor: falcon.Color, PilotName: ken.Name, Language: &jp.Language},
},
},
{
name: "jet name filter",
req: models.Request{JetName: "awk"},
expected: []models.Result{
{JetName: hawk.Name, JetAge: hawk.Age, JetColor: hawk.Color, PilotName: kyle.Name, Language: &jp.Language},
{JetName: hawk.Name, JetAge: hawk.Age, JetColor: hawk.Color, PilotName: kyle.Name, Language: &en.Language},
},
},
{
name: "language filter",
req: models.Request{Language: "English"},
expected: []models.Result{
{JetName: swallow.Name, JetAge: swallow.Age, JetColor: swallow.Color, PilotName: kyle.Name, Language: &en.Language},
{JetName: hawk.Name, JetAge: hawk.Age, JetColor: hawk.Color, PilotName: kyle.Name, Language: &en.Language},
},
},
}
db.RunTest(context.Background(), t, func(ctx context.Context, ntx *db.NestableTx) {
repo := repositories.NewJetRepository(ntx)
for _, c := range cases {
t.Run("GetJets "+c.name, func(t *testing.T) {
got, err := repo.GetJets(ctx, c.req)
if err != nil {
t.Error(err)
return
}
if r := cmp.Diff(got, c.expected); r != "" {
t.Errorf("failed. expected: %v, got: %v", c.expected, got)
}
})
}
}, deps...)
}
SELECT
jets.name AS jetName,
jets.age AS jetAge,
jets.color AS jetColor,
pilots.name AS pilotName,
languages.language
FROM jets
JOIN pilots ON pilots.id = jets.pilot_id
LEFT JOIN pilot_languages ON pilot_languages.pilot_id = jets.pilot_id
LEFT JOIN languages ON languages.id = pilot_languages.language_id
WHERE TRUE
{{ if ne .Age 0 -}}
AND jets.age = :age
{{- end}}
{{if ne .PilotName "" -}}
AND pilots.name LIKE :pilot_name
{{- end}}
{{if ne .JetName "" -}}
AND jets.name LIKE :jet_name
{{- end}}
{{if ne .Language "" -}}
AND languages.language = :language
{{- end}}
ORDER BY jets.age, jets.id
;
まぁ難しい話はさておき、重要なポイントはDBアクセスはリポジトリ(repositories/)が担当して、 実行プログラムがそれを使い分けているだけなので以下ではそれらに注目してみていくことにしましょう。(そのために分けたというのもある)
😇 まずはふつうに normal
とりあえず、何も考えず SQLを組み立てて実装してみます。左右がプログラムで下が実行結果です。(以下すべてこの構成)
|
gorp-tips/src/main_without_template.go
package main
import (
"context"
"database/sql"
"flag"
"fmt"
"gorp-tips/controllers"
"gorp-tips/models"
"gorp-tips/repositories"
"github.com/go-gorp/gorp"
_ "github.com/go-sql-driver/mysql"
"github.com/labstack/gommon/log"
)
func initDb() *gorp.DbMap {
db, _ := sql.Open("mysql", "usr:pw@tcp(mysql:3306)/db")
dbmap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{Engine: "InnoDB", Encoding: "utf8mb4"}}
models.MapStructsToTables(dbmap)
return dbmap
}
func main() {
dbmap := initDb()
defer dbmap.Db.Close()
var (
age = flag.Int("age", 0, "jet age")
pilotName = flag.String("pilot_name", "", "pilot name")
jetName = flag.String("jet_name", "", "jet name")
language = flag.String("language", "", "language")
)
flag.Parse()
req := models.Request{
Age: *age,
PilotName: *pilotName,
JetName: *jetName,
Language: *language,
}
repo := repositories.NewJetRepository0(dbmap)
results, err := controllers.GetJets(context.Background(), repo, req)
if err != nil {
log.Fatal(err)
return
}
for _, record := range results {
fmt.Printf("%+v\n", record)
}
}
|
gorp-tips/src/repositories/jet_repository0.go
package repositories
import (
"context"
"strings"
"github.com/go-gorp/gorp"
"github.com/labstack/gommon/log"
"gorp-tips/models"
)
type JetRepository interface {
GetJets(ctx context.Context, req models.Request) ([]models.Result, error)
}
type jetRepository0 struct {
exec gorp.SqlExecutor
}
func NewJetRepository0(exec gorp.SqlExecutor) JetRepository {
return &jetRepository0{
exec: exec,
}
}
func (r *jetRepository0) GetJets(ctx context.Context, req models.Request) ([]models.Result, error) {
query := "SELECT jets.name AS jetName, jets.age AS jetAge, jets.color AS jetColor, pilots.name AS pilotName, languages.language "
query += "FROM jets "
query += "JOIN pilots ON pilots.id = jets.pilot_id "
query += "LEFT JOIN pilot_languages ON pilot_languages.pilot_id = jets.pilot_id "
query += "LEFT JOIN languages ON languages.id = pilot_languages.language_id "
conds, variables := makeCondition(req)
if conds != "" {
query += "WHERE " + conds
}
query += " ORDER BY jets.age, jets.id"
log.Debug(query)
var results []models.Result
if _, err := r.exec.Select(&results, query, variables); err != nil {
log.Error(err)
return nil, err
}
return results, nil
}
func makeCondition(req models.Request) (string, map[string]interface{}) {
conds := []string{}
context := map[string]interface{}{}
if req.Age > 0 {
conds = append(conds, "jets.age = :age")
context["age"] = req.Age
}
if req.PilotName != "" {
conds = append(conds, "pilots.name LIKE :pilot_name")
context["pilot_name"] = "%" + req.PilotName + "%"
}
if req.JetName != "" {
conds = append(conds, "jets.name LIKE :jet_name")
context["jet_name"] = "%" + req.JetName + "%"
}
if req.Language != "" {
conds = append(conds, "languages.language = :language")
context["language"] = req.Language
}
return strings.Join(conds, " AND "), context
}
|
root@3e65753c4761:~/src# go run main_without_template.go -language English {JetName:F-8 JetAge:1 JetColor:White PilotName:Albert Language:0xc0001c2260} {JetName:F-7 JetAge:10 JetColor:White PilotName:William Language:0xc0001c2280} {JetName:F-12 JetAge:10 JetColor:White PilotName:William Language:0xc0001c2290} {JetName:F-14 JetAge:10 JetColor:White PilotName:Warner Language:0xc0001c22a0} {JetName:F-17 JetAge:10 JetColor:White PilotName:William Language:0xc0001c22b0} {JetName:F-4 JetAge:20 JetColor:White PilotName:Warner Language:0xc0001c22c0} {JetName:F-18 JetAge:20 JetColor:White PilotName:Albert Language:0xc0001c22d0} {JetName:F-2 JetAge:25 JetColor:White PilotName:William Language:0xc0001c22e0} {JetName:F-3 JetAge:25 JetColor:White PilotName:Albert Language:0xc0001c22f0} {JetName:F-9 JetAge:30 JetColor:White PilotName:Warner Language:0xc0001c2300} {JetName:F-13 JetAge:30 JetColor:White PilotName:Albert Language:0xc0001c2310} {JetName:F-19 JetAge:30 JetColor:White PilotName:Warner Language:0xc0001c2320} root@3e65753c4761:~/src# go run main_without_template.go -language English -pilot_name r {JetName:F-8 JetAge:1 JetColor:White PilotName:Albert Language:0xc0000415d0} {JetName:F-14 JetAge:10 JetColor:White PilotName:Warner Language:0xc0000415f0} {JetName:F-4 JetAge:20 JetColor:White PilotName:Warner Language:0xc000041600} {JetName:F-18 JetAge:20 JetColor:White PilotName:Albert Language:0xc000041610} {JetName:F-3 JetAge:25 JetColor:White PilotName:Albert Language:0xc000041620} {JetName:F-9 JetAge:30 JetColor:White PilotName:Warner Language:0xc000041630} {JetName:F-13 JetAge:30 JetColor:White PilotName:Albert Language:0xc000041640} {JetName:F-19 JetAge:30 JetColor:White PilotName:Warner Language:0xc000041650}
備考
Language だけアドレスが表示されているのは language が LEFT OUTER で結合される(つまりnullableな)ので ポインタ型にする必要があったのです。構造体内のポインタ型を Printf でうまく表示する方法がみつからなかったのでこのようになってます。
大文字から始まる JetRepository は構造体ではなくインタフェースです。リポジトリ初期化関数の返却値やコントローラーの仮引数の型として使うことで抽象化しています。
標準入力で受け取った値を makeCondition で組み立て WHERE条件に渡すことでフィルタリングしています。 検索に必要なフィールドは固定でJOINしていますがパフォーマンスを考慮するなら JOIN の組み立ても分岐するべきでしょう。
今回の要件はシンプルなのでそこまで複雑ではないですがすでに少し辛さを感じます。検索条件を組み立てるSQLが分かれてしまっているのと、 文字列結合時に前後のスペースを意識しないといけないのが地味に精神に来ます。
📖 テンプレートに書いてみる template
ということで実行するSQLを テンプレートファイルに切り出してみます。
まず SQL を書いたGoテンプレートがこちら。
gorp-tips/src/sql/query.sql
SELECT
jets.name AS jetName,
jets.age AS jetAge,
jets.color AS jetColor,
pilots.name AS pilotName,
languages.language
FROM jets
JOIN pilots ON pilots.id = jets.pilot_id
LEFT JOIN pilot_languages ON pilot_languages.pilot_id = jets.pilot_id
LEFT JOIN languages ON languages.id = pilot_languages.language_id
WHERE TRUE
{{ if ne .Age 0 -}}
AND jets.age = :age
{{- end}}
{{if ne .PilotName "" -}}
AND pilots.name LIKE :pilot_name
{{- end}}
{{if ne .JetName "" -}}
AND jets.name LIKE :jet_name
{{- end}}
{{if ne .Language "" -}}
AND languages.language = :language
{{- end}}
ORDER BY jets.age, jets.id
;
パラメータの有無によってかき分けたいところは {{}} で囲んで条件分岐しています。
Go テンプレートの記法は Package template とかを見ましょう
続いてこのSQLを読み出して描画する関数 GetSQL を以下のように定義してみます。
gorp-tips/src/db/sql.go
package db
import (
"bytes"
"text/template"
)
func GetSQL(filename string, req interface{}) string {
var buf bytes.Buffer
t := template.Must(template.ParseFiles("sql/" + filename))
t.Execute(&buf, req)
return buf.String()
}
見てわかるようにテンプレートを読み出して描画したものをバッファに突っ込んでいるだけです。
この関数を用いて先程と同様のプログラムを書いてみますね。ほぼリポジトリにしか違いがないので右側にだけ注目してもらえればよいです。
|
gorp-tips/src/main_with_template.go
package main
import (
"context"
"database/sql"
"flag"
"fmt"
"gorp-tips/controllers"
"gorp-tips/models"
"gorp-tips/repositories"
"github.com/go-gorp/gorp"
_ "github.com/go-sql-driver/mysql"
"github.com/labstack/gommon/log"
)
func initDb() *gorp.DbMap {
db, _ := sql.Open("mysql", "usr:pw@tcp(mysql:3306)/db")
dbmap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{Engine: "InnoDB", Encoding: "utf8mb4"}}
models.MapStructsToTables(dbmap)
return dbmap
}
func main() {
dbmap := initDb()
defer dbmap.Db.Close()
var (
age = flag.Int("age", 0, "jet age")
pilotName = flag.String("pilot_name", "", "pilot name")
jetName = flag.String("jet_name", "", "jet name")
language = flag.String("language", "", "language")
)
flag.Parse()
req := models.Request{
Age: *age,
PilotName: *pilotName,
JetName: *jetName,
Language: *language,
}
repo := repositories.NewJetRepository1(dbmap)
results, err := controllers.GetJets(context.Background(), repo, req)
if err != nil {
log.Fatal(err)
return
}
for _, record := range results {
fmt.Printf("%+v\n", record)
}
}
|
gorp-tips/src/repositories/jet_repository1.go
package repositories
import (
"context"
"github.com/go-gorp/gorp"
"github.com/labstack/gommon/log"
"gorp-tips/db"
"gorp-tips/models"
)
type jetRepository1 struct {
exec gorp.SqlExecutor
}
func NewJetRepository1(exec gorp.SqlExecutor) JetRepository {
return &jetRepository1{
exec: exec,
}
}
func (r *jetRepository1) GetJets(ctx context.Context, req models.Request) ([]models.Result, error) {
query := db.GetSQL("query.sql", req)
log.Debug(query)
var results []models.Result
if _, err := r.exec.Select(&results, query, map[string]interface{}{
"age": req.Age,
"pilot_name": "%" + req.PilotName + "%",
"jet_name": "%" + req.JetName + "%",
"language": req.Language,
}); err != nil {
log.Error(err)
return nil, err
}
return results, nil
}
|
root@3e65753c4761:~/src# go run main_with_template.go -language English {JetName:F-8 JetAge:1 JetColor:White PilotName:Albert Language:0xc000099780} {JetName:F-7 JetAge:10 JetColor:White PilotName:William Language:0xc0000997a0} {JetName:F-12 JetAge:10 JetColor:White PilotName:William Language:0xc0000997b0} {JetName:F-14 JetAge:10 JetColor:White PilotName:Warner Language:0xc0000997c0} {JetName:F-17 JetAge:10 JetColor:White PilotName:William Language:0xc0000997d0} {JetName:F-4 JetAge:20 JetColor:White PilotName:Warner Language:0xc0000997e0} {JetName:F-18 JetAge:20 JetColor:White PilotName:Albert Language:0xc0000997f0} {JetName:F-2 JetAge:25 JetColor:White PilotName:William Language:0xc000099800} {JetName:F-3 JetAge:25 JetColor:White PilotName:Albert Language:0xc000099810} {JetName:F-9 JetAge:30 JetColor:White PilotName:Warner Language:0xc000099820} {JetName:F-13 JetAge:30 JetColor:White PilotName:Albert Language:0xc000099830} {JetName:F-19 JetAge:30 JetColor:White PilotName:Warner Language:0xc000099840} root@3e65753c4761:~/src# go run main_with_template.go -language English -pilot_name r {JetName:F-8 JetAge:1 JetColor:White PilotName:Albert Language:0xc000099780} {JetName:F-14 JetAge:10 JetColor:White PilotName:Warner Language:0xc0000997a0} {JetName:F-4 JetAge:20 JetColor:White PilotName:Warner Language:0xc0000997b0} {JetName:F-18 JetAge:20 JetColor:White PilotName:Albert Language:0xc0000997c0} {JetName:F-3 JetAge:25 JetColor:White PilotName:Albert Language:0xc0000997d0} {JetName:F-9 JetAge:30 JetColor:White PilotName:Warner Language:0xc0000997e0} {JetName:F-13 JetAge:30 JetColor:White PilotName:Albert Language:0xc0000997f0} {JetName:F-19 JetAge:30 JetColor:White PilotName:Warner Language:0xc000099800}
プログラムの最初の方は共通なので同じで、重要なのは後半です。 ファイル名(パス)と検索パラメータの構造体だけを渡してあげればSQLが取得できるのでとてもシンプルに見えませんか？
その後、取得した SQL中の 変数 を 展開するためのコンテキストをmapで渡してあげています。 渡した変数の使用可否を決めるのはテンプレートなのでこちらは何も考えずに検索条件を組み立ててあげればよいのです。
🐍 テンプレートファイルをバイナリに潜影蛇手するわね put templates into a bin
アプリを動かす環境がコンテナの場合 実行するSQLファイルもイメージに乗せてあげないといけません。
docker add などで追加するのもいいんですが、今回は statik というプログラムを使います。
これは静的ファイルをパッケージに埋め込むことで go のプログラムから ファイルへのアクセスを擬似的に表現します。
備考
具体的な用途は Dockerイメージ内でファイルを参照するとか、シングルバイナリにアセットを含めて配布するような場合です。
実際にファイルをバイナリ(プログラム)に埋め込むには go get github.com/rakyll/statik のように statik コマンドをインストールする必要があります。
(ビルドだけなら不要)
プログラム中に書かれている //go:generate statik -f -src=../files -m というコメントは ../files 内のファイルを
statik パッケージ(正確には statik.go)に閉じ込めるアノテーションのようなもので、
go generate することで実行されます。(ディレクトリに再帰的に適用する場合は go generate ./... のようにすること)
自動的に同期されるわけではないのでファイルが更新されたら statik の実行も忘れないようにしましょう。
初回実行時には statik が空で go のパッケージとして認識されないので、私の環境では dummy.go というファイルを置いています。
使うSQLファイルは先ほどと同じなので説明を省略して、SQLを読み出すための関数を新設します。 雑に GetSQL2 と命名しました。
gorp-tips/src/db/sql2.go
package db
import (
"bytes"
"io/ioutil"
"os"
"text/template"
_ "gorp-tips/db/statik"
"github.com/rakyll/statik/fs"
)
//go:generate statik -f -src=../sql -m
var files, _ = fs.New()
func GetSQL2(filename string, req interface{}) string {
var buf bytes.Buffer
f, _ := files.Open(string(os.PathSeparator) + filename)
b, _ := ioutil.ReadAll(f)
t := template.Must(template.New(filename).Parse(string(b)))
t.Execute(&buf, req)
return buf.String()
}
この関数を用いて先程と同様のプログラムを書いてみますよ。
|
gorp-tips/src/main_with_template_in_bin.go
package main
import (
"context"
"database/sql"
"flag"
"fmt"
"github.com/go-gorp/gorp"
_ "github.com/go-sql-driver/mysql"
"github.com/labstack/gommon/log"
"gorp-tips/controllers"
"gorp-tips/models"
"gorp-tips/repositories"
)
func initDb() *gorp.DbMap {
db, _ := sql.Open("mysql", "usr:pw@tcp(mysql:3306)/db")
dbmap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{Engine: "InnoDB", Encoding: "utf8mb4"}}
models.MapStructsToTables(dbmap)
return dbmap
}
func main() {
dbmap := initDb()
defer dbmap.Db.Close()
var (
age = flag.Int("age", 0, "jet age")
pilotName = flag.String("pilot_name", "", "pilot name")
jetName = flag.String("jet_name", "", "jet name")
language = flag.String("language", "", "language")
)
flag.Parse()
req := models.Request{
Age: *age,
PilotName: *pilotName,
JetName: *jetName,
Language: *language,
}
repo := repositories.NewJetRepository2(dbmap)
results, err := controllers.GetJets(context.Background(), repo, req)
if err != nil {
log.Fatal(err)
return
}
for _, record := range results {
fmt.Printf("%+v\n", record)
}
}
|
gorp-tips/src/repositories/jet_repository2.go
package repositories
import (
"context"
"github.com/go-gorp/gorp"
"github.com/labstack/gommon/log"
"gorp-tips/db"
"gorp-tips/models"
)
type jetRepository2 struct {
exec gorp.SqlExecutor
}
func NewJetRepository2(exec gorp.SqlExecutor) JetRepository {
return &jetRepository2{
exec: exec,
}
}
func (r *jetRepository2) GetJets(ctx context.Context, req models.Request) ([]models.Result, error) {
query := db.GetSQL2("query.sql", req)
log.Debug(query)
var results []models.Result
if _, err := r.exec.Select(&results, query, map[string]interface{}{
"age": req.Age,
"pilot_name": "%" + req.PilotName + "%",
"jet_name": "%" + req.JetName + "%",
"language": req.Language,
}); err != nil {
log.Error(err)
return nil, err
}
return results, nil
}
|
root@3e65753c4761:~/src# go run main_with_template_in_bin.go -language English {JetName:F-8 JetAge:1 JetColor:White PilotName:Albert Language:0xc000040200} {JetName:F-7 JetAge:10 JetColor:White PilotName:William Language:0xc000040220} {JetName:F-12 JetAge:10 JetColor:White PilotName:William Language:0xc000040230} {JetName:F-14 JetAge:10 JetColor:White PilotName:Warner Language:0xc000040240} {JetName:F-17 JetAge:10 JetColor:White PilotName:William Language:0xc000040250} {JetName:F-4 JetAge:20 JetColor:White PilotName:Warner Language:0xc000040260} {JetName:F-18 JetAge:20 JetColor:White PilotName:Albert Language:0xc000040270} {JetName:F-2 JetAge:25 JetColor:White PilotName:William Language:0xc000040280} {JetName:F-3 JetAge:25 JetColor:White PilotName:Albert Language:0xc000040290} {JetName:F-9 JetAge:30 JetColor:White PilotName:Warner Language:0xc0000402a0} {JetName:F-13 JetAge:30 JetColor:White PilotName:Albert Language:0xc0000402b0} {JetName:F-19 JetAge:30 JetColor:White PilotName:Warner Language:0xc0000402c0} root@3e65753c4761:~/src# go run main_with_template_in_bin.go -language English -pilot_name r {JetName:F-8 JetAge:1 JetColor:White PilotName:Albert Language:0xc00018e1d0} {JetName:F-14 JetAge:10 JetColor:White PilotName:Warner Language:0xc00018e1f0} {JetName:F-4 JetAge:20 JetColor:White PilotName:Warner Language:0xc00018e200} {JetName:F-18 JetAge:20 JetColor:White PilotName:Albert Language:0xc00018e210} {JetName:F-3 JetAge:25 JetColor:White PilotName:Albert Language:0xc00018e220} {JetName:F-9 JetAge:30 JetColor:White PilotName:Warner Language:0xc00018e230} {JetName:F-13 JetAge:30 JetColor:White PilotName:Albert Language:0xc00018e240} {JetName:F-19 JetAge:30 JetColor:White PilotName:Warner Language:0xc00018e250}
動いていそうなのでこれ以上言うことはないです。
👺 注意点 important point
テンプレートの変数とSQL自体の変数で２段階の展開をしているので「なぜこんな面倒な使い分けをしてるの？」と思うかもしれませんが、 可能な限り SQLの変数(以降プレースホルダ)を使うようにしたいのです。
GoのテンプレートはSQLを理解しないので、テンプレートによって変数を出力すると、 不正なSQL断片を受け入れて SQLインジェクション攻撃に対して脆弱になります。
ユーザからの入力値を描画する場合は プレースホルダを使いましょう。ぉじさんとの約束だょ。
OK |
NG |
|---|---|
-- 前略 AND jets.age = :age -- 後略 |
-- 前略 AND jets.age = {{.Age}} -- 後略 |
テンプレートにSQLを書くためのサードパーティライブラリもあるようですが、標準テンプレートだけで大した手間もなく実装できました。
もしSQLの組み立てに苦しんでいる方はぜひ試してみてください。
こんなん常識やろとか、もっといい方法があるぞという方はコメントお願いします！