Slide 1
Slide 1 text
github.com/
pinzolo/sqlt
Slide 2
Slide 2 text
$ whoami
• NODA Masato
• @pinzolo
• Go ϓϥΠϕʔτͰ͍࢝Ίͯ2͙
Β͍
• SQLࣗͰॻ͖͍ͨ
Slide 3
Slide 3 text
sqltͱ
text/template Λϕʔεʹͯ͠࡞ͨ͠ 2way SQL ϥΠϒϥ
Ϧ
Slide 4
Slide 4 text
2way SQL ͬͯʁ
• ΫΤϦϏϧμͷҰछ
• ςϯϓϨʔτʹରͯ͠ύϥϝʔλΛͯ͠SQLΛੜ͢Δ
• ςϯϓϨʔτࣗମ͕࣮ߦͰ͖ΔSQLͰ͋Δ
Slide 5
Slide 5 text
2way SQL ͬͯʁ
• ΫΤϦϏϧμͷҰछ
• ςϯϓϨʔτʹରͯ͠ύϥϝʔλΛͯ͠SQLΛੜ͢Δ
• ςϯϓϨʔτࣗମ͕࣮ߦͰ͖ΔSQLͰ͋Δ <- ࠷େͷಛ
Slide 6
Slide 6 text
͜Μͳͭ
ex: Doma
SELECT *
FROM item
WHERE status = 'AVAILABLE'
/*%if name != null*/
AND name = /*name*/'John Doe'
/*%end*/
Slide 7
Slide 7 text
2way SQL ࣗମͷϝϦ
οτɾσϝϦοτ ORM
૪͜͜ͰׂѪ
Slide 8
Slide 8 text
No content
Slide 9
Slide 9 text
ͳͥ text/template
ϕʔεͰ࡞Ζ͏ͱࢥͬͨ
ͷ͔ʁ
Slide 10
Slide 10 text
ָ͔͔ͨͬͨ͠Β
Slide 11
Slide 11 text
text/template Λ͏ͱ
if range ͱ੍͍ͬͨޚߏจ͕ఏڙ͞Ε͓ͯΓɺeq ne and or
ͳͲͷ΄΅ if ʹඞਢͳॲཧ࠷࣮ͷඞཁ͕ແ͍
Slide 12
Slide 12 text
text/template Λ͏ͱ
template.FuncMap ܦ༝ͰςϯϓϨʔτͰ͑ΔؔΛొ
Ͱ͖ΔͷͰɺSQLʹؔ͢ΔΈࠐΈؔϓϩδΣΫτຖͷศརؔ
ͷొ͕༰қ
Slide 13
Slide 13 text
text/template Λ͏ͱ
ύϑΥʔϚϯεΛຊՈʹͿΜ͛ΒΕΔɻ
ʢͨͩ͠ɺવࣗͰScannerΛॻ͍ͨํ͕ΑΓߴੑೳͳͷʹ
ͳΔʣ
Slide 14
Slide 14 text
ϝϦοτଟ͍
Slide 15
Slide 15 text
(ςϯϓϨʔτݴޠΛͬͯςϯϓϨʔτݴޠΛ࡞ΔΜ͔ͩΒ
େ͕ྲྀ༻Ͱ͖ͯͨΓલͳͷ͚ͩͲ)
Slide 16
Slide 16 text
No content
Slide 17
Slide 17 text
Sample code
SQL
SELECT *
FROM users
WHERE id IN /*% in "ids" %*/(1, 2)
AND name = /*% p "name" %*/'John Doe'
/*%- if get "onlyMale" %*/
AND sex = 'MALE'
/*%- end %*/
ORDER BY /*% out "order" %*/id
Slide 18
Slide 18 text
Sample code
Go
st := sqlt.New(sqlt.Postgres)
query, args, err := st.Exec(s, map[string]interface{}{
"ids": []int{1, 2, 3},
"order": "name DESC",
"onlyMale": false,
"name": "Alex",
})
Slide 19
Slide 19 text
Sample code
Generated SQL
SELECT *
FROM users
WHERE id IN ($1, $2, $3)
AND name = $4
ORDER BY name DESC
Slide 20
Slide 20 text
Sample code
Generated SQL (ExecNamed)
SELECT *
FROM users
WHERE id IN (:ids__1, :ids__2, :ids__3)
AND name = :name
ORDER BY name DESC
Slide 21
Slide 21 text
Sample code
Generated SQL (Annotation Option)
SELECT *
FROM users
WHERE id IN ($1, $2, $3)/*# ids */
AND name = $4/*# name */
ORDER BY name DESC
Slide 22
Slide 22 text
ͳͥ text/template
Λ༻͍ͯ͜Μͳ͜ͱ͕ग़
དྷΔͷ͔ʁ
Slide 23
Slide 23 text
sqlt Λ࣮ݱ͍ͯ͠Δೋͭͷൃ
1.Template.Delims ʹΑΔ SQL ϑϨϯυϦʔͳσϦϛλ
2.ؔͰͳ͘ϝιουΛ FuncMap ʹొͯ͠෭࡞༻Λར༻͢
Δ
Slide 24
Slide 24 text
Template.Delims
ຊདྷ {{ ͱ }} Ͱ͋Δ text/template ͷσϦϛλΛมߋ͢Δ
ػೳɻ
͜ΕΛ /*% ͱ %*/ ͱ͍͏SQLʹͱͬͯίϝϯτͰ͋Δจࣈʹ
มߋ͍ͯ͠ΔͷͰɺςϯϓϨʔτͷ֤छ໋ྩΛແಟԽ͍ͯ͠Δɻ
Slide 25
Slide 25 text
ϝιουͷ෭࡞༻ར༻
FuncMap ʹొ͢ΔؔΛ७ਮͳؔͰͳ͘ɺಛఆͷΠϯελ
ϯεʹॴଐ͢Δϝιουʹ͢Δ͜ͱʹΑͬͯεϨουϩʔΧϧͳ෭
࡞༻Λར༻Ͱ͖Δɻ
͓ʹςϯϓϨʔτʹ͞Εͨύϥϝʔλ͔ΒɺSQL࣮ߦʹඞཁͳ
ύϥϝʔλΛ࡞͢ΔͨΊʹར༻͍ͯ͠Δɻ
Slide 26
Slide 26 text
ྫ1ʣύϥϝʔλͷׂ
SELECT * FROM users
WHERE name LIKE /*% infix "name" %*/'John Doe'
/*% if get "available" %*/
AND status = /*% in "stats" %*/(NULL)
/*% end %*/
/*% if get "email" %*/
AND email = /*% p "email" %*/'[email protected]'
/*% end %*/
Slide 27
Slide 27 text
ྫ1ʣύϥϝʔλͷׂ
1.SQL࣮ߦ࣌ʹඞཁͳύϥϝʔλʢnameʣ
2.SQLߏங࣌ʹඞཁͳύϥϝʔλ(available)
3.྆ํʹඞཁͳύϥϝʔλʢemailʣ
4.ಛఆͷ݅࣌ʹඞཁͳύϥϝʔλʢstatsʣ
͜ͷ͏ͪɺ࣮ߦ࣌ʹ 2 ݅ʹΑͬͯ 4 Λഉআ͍ͨ͠
Slide 28
Slide 28 text
ྫ2ʣIN ۟ʹ͓͚ΔεϥΠεͷల։
΄ͱΜͲͷ database/sql ͷυϥΠό WHERE id IN $1 ʹ
ରͯ͠εϥΠεΛͯ͠ల։͠ͳ͍ɻ
Slide 29
Slide 29 text
ྫ3ʣΤεέʔϓॲཧͨ͠ύϥϝʔλ͕
ඞཁ
SELECT id
, name
, name = /*% out "name" %*/'John' AS matched
FROM users
WHERE name LIKE /*% infix "name" %*/'John'
ORDER BY matched DESC, name
Slide 30
Slide 30 text
ྫ3ʣΤεέʔϓॲཧͨ͠ύϥϝʔλ͕
ඞཁ
ಉ͡ name ύϥϝʔλΛར༻͍ͯ͠Δ͕ɺ1ͭΊͦͷ··Ͱ͍
͍ͷʹର͠ɺ2ͭΊ LIKE ͷରʹ͢ΔͨΊ % _ ΛΤεέ
ʔϓ͕ͨ͠ඞཁɻ
Slide 31
Slide 31 text
No content
Slide 32
Slide 32 text
sqltͰղܾͰ͖Δ͜ͱ
Slide 33
Slide 33 text
1.SQLΛผϑΝΠϧͰཧͰ͖Δ
2.2way-SQLͳͷͰςετ͍͢͠
3.Pure SQL ͳͷͰπʔϧͷԸܙΛड͚͍͢
4.ύϥϝʔλʹ໊લΛ͚ΒΕΔ
5.in, suffix, prefix ͷΑ͏ͳ SQL ઐ༻ؔʹΑΓແବͳ
ॲཧΛආ͚ΒΕΔ
6.DBͷҧ͍ΛSQLͷத͚ͩʹด͡ࠐΊ͍͢
Slide 34
Slide 34 text
sqlt͕ղܾΕͳ͔
ͬͨ͜ͱ
Slide 35
Slide 35 text
SQLΠϯδΣΫγϣϯ
Slide 36
Slide 36 text
೦ͳ͕Β
type Form struct {
Name string
}
st := sqlt.New(sqlt.Postgres)
query, args, err := st.Exec(s, map[string]interface{}{
"form": Form{Name: "' OR 1 = 1;"},
})
Slide 37
Slide 37 text
͜Μͳίʔυ͕
SELECT *
FROM users
/*%- $f := get "form" %*/
WHERE name = '/*% $f.Name %*/'
Slide 38
Slide 38 text
͔͚ͯ͠·͏
SELECT *
FROM users
WHERE name = '' OR 1 = 1;
Slide 39
Slide 39 text
range Ͱ
type V struct {
Value string
}
st := sqlt.New(sqlt.Postgres)
query, args, err := st.Exec(s, map[string]interface{}{
"values": []V{
V{"' OR 1 = 1;"},
V{"foo"},
V{"bar"},
},
})
Slide 40
Slide 40 text
ॻ͚ͯ͠·͏
SELECT * FROM users
WHERE (
/*%- range $i, $v := get "values" %*/
/*%- if ne $i 0 %*/ OR /*% end %*/
name = '/*% $v.Value %*/'
/*%- end %*/
)
Slide 41
Slide 41 text
ͪͳΈʹ
SELECT * FROM users
WHERE (
/*%- range $i, $v := get "values" %*/
/*%- if ne $i 0 %*/ OR /*% end %*/
name = /*% p (name "values" $i "Value") %*/''
/*%- end %*/
)
҆શʹॻ͘ํ๏ఏڙ͍ͯ͠Δɻ
Slide 42
Slide 42 text
σϑΥϧτͷຒΊࠐΈॲཧʹରͯ͋͠·ΓʹແྗͳͷͰɺҰ࣌ม
Λܦ༝͢Δ͚ͩͰ͍͘ΒͰSQLΠϯδΣΫγϣϯΛࠐΊͯ͠
·͏ɻ
σϑΥϧτͷຒΊࠐΈॲཧʹ hook Λॻ͚ͨΓ͢Δ͜ͱ͕ग़དྷΔ
ͳΒରࡦͰ͖Δͷ͕ͩɺͦΜͳ͜ͱ͕ग़དྷΔͳΒ͓ͦΒ͘
html/template ͳΜͯଘࡏ͍ͯ͠ͳ͍ɻ
Slide 43
Slide 43 text
͑Δͷʁ
• ϨϏϡʔ͕͔ͬ͠ΓճΔ
• Θ͔͍ͬͯΔਓ͚ͩ
• ݸਓϓϩμΫτ
ʹ͑ͳ͍͜ͱͳ͍͔͠Εͳ͘ͳ͍ɻੵۃతʹ͓͢͢Ί
͠ͳ͍ɻ
ʢ͕ࣗ͏ʹ͜ΕͰ·͍͍͔͋ͱࢥͬͯΔʣ
Slide 44
Slide 44 text
Կ͕͍͚ͳ͔ͬͨͷ͔ʁ
• 7ʙ8ׂͷػೳΛຬͨ͢ͱ͍͏ϝϦοτΛ༏ઌ͗ͨ͢͠
• Ұ൪େࣄͳͱ͜ΖΛ֎͞ͳ͍ͱ͍͏ߟ͑Λ༏ઌ͖͢
• ָʹղܾͰ͖Δ͜ͱ୭͔͕ղܾ͍ͯ͠Δ
• ͍͟ͱ͍͏ͱ͖ʹϨϕϧ෦ΛίϯτϩʔϧͰ͖Δ͔ͬͯͷ
Ϛδେࣄ
Slide 45
Slide 45 text
ڭ܇
Slide 46
Slide 46 text
अಓʹ҆қʹඈͼ͔ͭͳ
͍