Go向けORM sqllaの紹介と JOINやUNIONを含んだクエリの扱い方

by mackee

Slide 1

Slide 1 text

Go޲͚ORM sqllaͷ঺հͱ JOIN΍UNIONΛؚΜͩΫΤϦ ͷѻ͍ํ macopy a.k.a @mackee_w 2023-08-26 ভೆ.pm #1

Slide 2

Slide 2 text

ࢲͱ͸ • macopy X: @mackee_w • Perl͕޷͖ Goͱ͸஥ྑ͠ • ීஈ͸3DϓϦϯλΛԆʑͱ͍͍ͬͯ͡ ·͢ • ࣸਅ͸ϥεϕΨεͰϏʔϧͰ͢

Slide 3

Slide 3 text

sqllaͱ͸ • Go޲͚ORM • SQLͷ૊Έཱͯʹඞཁͳϝ ιουΛDBεΩʔϚఆ͔ٛΒ ίʔυੜ੒͢Δͷ͕ಛ௃ • ࢲ(macopy)͕࡞੒ɾϝϯςφ ϯεΛ͍ͯ͠·͢ • gitHub.com/mackee/go-sqlla

Slide 4

Slide 4 text

sqllaͷചΓ • ςʔϒϧͷΧϥϜʹରԠͨ͠ϝιου͕ίʔυੜ੒͞ΕΔͷͰɺิ׬ ʹग़ͯ͘Δ • `column IN (…)`, όϧΫΠϯαʔτ, `INSERT ~ ON DUPLICATEKEY UPDATE` ʹରԠ • ϑϨʔϜϫʔΫ΍ύοέʔδߏ੒ʹڧ͘ґଘ͠ͳ͍ͷͰɺҰ෦෼͚ͩ sqllaΛೖΕΔ͜ͱ͕Մೳ

Slide 5

Slide 5 text

ଞͷORMͱൺ΂Δͱ ʮsqlla͕΍Βͳ͍͜ͱʯͰ঺հ͢Δํ͕ ࡍཱͭ

Slide 6

Slide 6 text

ͦ΋ͦ΋ORMͬͯ ͳʹ

Slide 7

Slide 7 text

Object-Relational Mapper/Mapping • ͭ·ΓRDBMSͷΫΤϦ݁ՌΛϓϩάϥϛϯάݴޠݻ༗ͷΦϒδΣΫ τʹϚοϐϯά͢Δߦҝ, ΋͘͠͸ͦΕΛ΍Δπʔϧ/ϥΠϒϥϦ

Slide 8

Slide 8 text

͜͜ͰΈͳ͞Μɺ಄ͷதͰ ORMͱݴΘΕΔϥΠϒϥϦΛࢥ͍ු ͔΂ग़͍ͯͩ͘͞

Slide 9

Slide 9 text

Django ORM, SQLAlchemy, Peewee, Pony ORM, Tortoise ORM, Hibernate, EclipseLink, JOOQ, MyBatis, ActiveJDBC, ActiveRecord, Sequel, DataMapper, Entity Framework, Dapper, LINQ to SQL, Massive, Eloquent ORM, Doctrine ORM, Propel, Sequelize, TypeORM, Waterline, Objection.js, GORM, xorm, Storm, Pop, Exposed, Ktorm, OrmLite ChatGPTʹͰ͖Δ͚ͩྻڍͯ͠ͱ͓ئ͍ͨ͠(ͳ͍΋ͷ͕͋Δ͔΋)

Slide 10

Slide 10 text

Perlͩͱ • Class::DBI • DBIx::Class • Teng • Aniki • Otogiri

Slide 11

Slide 11 text

࠶ܝ: Object-Relational Mapper/Mapping • ͭ·ΓRDBMSͷΫΤϦ݁ՌΛϓϩάϥϛϯάݴޠݻ༗ͷΦϒδΣΫ τʹϚοϐϯά͢Δߦҝ, ΋͘͠͸ͦΕΛ΍Δπʔϧ/ϥΠϒϥϦ

Slide 12

Slide 12 text

͕ͩɺੈͷதͰݴΘΕ͍ͯΔ ORM͸ ͦΕҎ֎ͷ࢓ࣄΛ΍͍ͬͯΔ

Slide 13

Slide 13 text

ΫΤϦϏϧμʔ • ϓϩάϥϛϯάݴޠͷγϯλοΫεͰDMLΛ૊ΈཱͯΔ Teng(Perl)

Slide 14

Slide 14 text

εΩʔϚ؅ཧ • RDBMSͷDDLΛݴޠͷγϯλοΫεͰఆٛͨ͠Γɺ΋͘͠͸ͦͷٯ ม׵Λߦ͏ gorm(Go)

Slide 15

Slide 15 text

εΩʔϚϚΠάϨʔγϣϯ • ͢Ͱʹ͋ΔDBͷDDLΛɺ໨తͷঢ়ଶͷDDLʹͳΔΑ͏ʹมԽͤ͞Δ ActiveRecord(Ruby)

Slide 16

Slide 16 text

ଞʹ΋͜Μͳػೳ͕ • ૠೖ࣌ɾߋ৽࣌ϑοΫ • DBͷܕͱϓϩάϥϛϯάݴޠͷܕͷม׵ • in fl ate/de fl ateͳͲͱݺ͹ΕͨΓ͢Δ • ίωΫγϣϯϓʔϦϯά

Slide 17

Slide 17 text

݁ہͷͱ͜ΖɺRDBMSΛ͏·͘ѻ͏ ͨΊͷϥΠϒϥϦͩͬͨΓ ͦΜͳϥΠϒϥϦͷதͷҰͭͷػೳͱ ͯ͠ORM͕͋Δ

Slide 18

Slide 18 text

sqlla͸ԿΛ΍ͬͯԿΛ΍Βͳ͍͔

Slide 19

Slide 19 text

sqllaͷػೳ • ΫΤϦϏϧμʔ • ߋ৽࣌ɾૠೖ࣌ϑοΫ • ORM • Ҏ্ʂ

Slide 20

Slide 20 text

sqlla͕΍Βͳ͍͜ͱ • εΩʔϚϚΠάϨʔγϣϯ • DBίωΫγϣϯ؅ཧ • ߴ౓ͳܕม׵

Slide 21

Slide 21 text

DBίωΫγϣϯΛ࣋ͨͳ͍ͱ͸ ΫΤϦ࣮ߦ࣌ʹຖճʹsqlla.DB interfaceΛຬͨ͢ΦϒδΣΫτΛ౉͢

Slide 22

Slide 22 text

sqlla͚ͩͩͱ͖͍ͭͷͰɺݱ࣮తʹ͸͜͏ͯ͠ ·͢ • εΩʔϚ؅ཧ github.com/mackee/go-genddl • sqllaޓ׵ͷstruct͔ΒDDLΛग़ྗ͢Δ • εΩʔϚϚΠάϨʔγϣϯ github.com/k0kubun/sqldef • DDL͔ΒALTERจΛੜ੒͢Δ • ίωΫγϣϯ؅ཧ database/sql • ΧϥϜͷܕͱGoͷܕͷ૬ޓม׵͸֤driverʹ೚͍ͤͯΔ • ήετͷshogo82148͞Μ(go-sql-driver/mysqlͷίϯτϦϏϡʔλʔ)ײँͯ͠·͢ʂ

Slide 23

Slide 23 text

࠷ۙͷ೰Έ ʮJOINΛsqllaͰѻ͍͍ͨʯ

Slide 24

Slide 24 text

ORMʹ͓͚ΔJOIN໰୊ • ୯ମςʔϒϧΛҾ࣌͘͸RDBMSͷ݁Ռͷܗ͕มΘΔ͜ͱ͸͋Μ·Γ ͳ͍ • ΧϥϜ਺͕ݮΔ͙Β͍ • JOINΛ͢ΔͱΧϥϜ਺͕૿ݮ͢Δ ΧϥϜͷܕ΋ೖΓࠞͬͯ͡͠·͏ • ੩తʹఆٛͨ͠ΦϒδΣΫτʹϚοϐϯά͕Ͱ͖ͳ͍ ͜ͱ͕͋Δ

Slide 25

Slide 25 text

ݱঢ়ͷsqllaͷJOINͷѻ͍ํ

Slide 26

Slide 26 text

ݱঢ়ͷ໰୊఺ • ୯Ұͷstructʹ͔͠ಉ࣌ʹϚοϐϯά͕Ͱ͖ͳ͍ • ྫͩͱaccountςʔϒϧͷΈ • ෳ਺ʹඥ͚ͮΔʹ͸ ToSqlͰSQLΛు͖ͭͭखಈͰScanΛճ͍ͯ͠ Δ

Slide 27

Slide 27 text

ΞΠσΟΞ: ϏϡʔΛ࢖͏ͷ͸Ͳ͏͔ • ϏϡʔΛ࢖ͬͯJOIN͞ΕͨΫΤϦͱ୯ମͷςʔϒϧʹݟ͔͚ͤΔ • ϏϡʔͳΒUNION΋ѻ͑Δ

Slide 28

Slide 28 text

No content

Slide 29

Slide 29 text

͜ΕͰ͍͍Μ͡Όͳ͍ʁʁ

Slide 30

Slide 30 text

͍ͭͰʹgenddlଆͰ΋VIEWΛ࡞ΕΔΑ͏ʹ͢Δ

Slide 31

Slide 31 text

΋͏গ᩵͠୔͍ͨ͠ • ݩʑͷςʔϒϧͷΧϥϜΛؙ͝ͱ͖͍࣋ͬͯͨ • ݸผͷςʔϒϧΛUPDATEͨ͘͠ͳͬͨΓ͢Δέʔε • structʹϏδωεϩδοΫͷϝιουΛషΓ෇͚͍ͯΔέʔε • TonamelͩͱActiveRecordύλʔϯతʹͦ͏͍͏;͏ʹ͍ͯ͠Δ

Slide 32

Slide 32 text

Ͱ͖ΔΑ͏ʹͯ͠Έͨ

Slide 33

Slide 33 text

͏·͍͖ͦ͘͏(࣮ݧத)

Slide 34

Slide 34 text

՝୊ • Where۟༻ͷϝιουͰ໊લ͕ඃͬͨΒͲ͏͢Δͷ͔ • Account.ID͸AccountID(v, operator…)ʹͳΔ͕ɺ͜ͷ๏ଇͰ͍͘ͱඃͬͯ͠·͏͜ͱ͕͋Δͷ Ͱ͸ɾɾɾʁ • Ҋ֎ͳ͍͔΋ʁ • εΩʔϚϚΠάϨʔγϣϯ࣌ʹϏϡʔͷΧϥϜ͕৯͍ҧͬͯഁ໓͠ͳ͍͔Ͳ͏͔ • ςετͰΘ͔Δ͔Βେৎ෉ʁɹͨͩ `u.*` Έ͍ͨͳࢦఆΛ͢ΔͱɺDML࣮ߦத͏ʹരൃ͢Δ͔΋ • MySQLͰsqldefΛ࢖͏ࡍʹϏϡʔΛؚΜͩDDLΛ࢖͏ͱৗʹࠩ෼͕ൃੜ͢Δ • ຊମʹڍಈΛվળ͢ΔPull RequestΛૹΕͳ͍͔ௐࠪத… ࠙਌ձͰ୭͔ʹฉ͔͘΋

Slide 35

Slide 35 text

ͦͷଞ sqlla ʹؔ͢Δల๬ • Goͷdatabase/sqlʹNull[T]͕ೖͬͨΒଈରԠ͢Δͭ΋ΓͰ͢ • ΧελϜςϯϓϨʔτ • sqllaͷੜ੒࣌ͷςϯϓϨʔτΛϨσΟϝΠυͷ΋ͷ͡Όͳͯ͘Ϣʔ βʔ͕࡞ͬͨ΋ͷΛద༻Ͱ͖ΔΑ͏ʹ͢Δ • υΩϡϝϯτ • sqlla handbook ͳͲ͸Ͳ͏͔ͱݴΘΕ͍ͯΔ

Slide 36

Slide 36 text

Ҏ্ʂ