UT Teck sake #2 - Speaker Deck

Slide 1

Slide 1 text

Lovely, Relational DataBase UNCOVERTRUTH Tech. sake #2 Shoota Kumano ( @shoota ) UNCOVERTRUTH Tech sake #2 1

Slide 2

Slide 2 text

TOC 今日はRDBMS の思い出とその背景を肴に、を飲もうと思います。 UNCOVERTRUTH Tech sake #2 2

Slide 3

Slide 3 text

About me. Shuta Kumano @shoota { 'age': 32, 'address': ' 青森県八戸市', 'jobs': [ 'Web システム開発/ 保守', 'iOS app. 開発', 'Android app. 開発', ] } UNCOVERTRUTH Tech sake #2 3

Slide 4

Slide 4 text

Q: SQL 好きですか？ UNCOVERTRUTH Tech sake #2 4

Slide 5

Slide 5 text

A: 僕はすきです。 - Oracle 9i/10g/11g - PostgreSQL - MySQL ロジック集合パズルを解く感覚一般解と最適解 1 Query Dream No Programming, but Programable UNCOVERTRUTH Tech sake #2 5

Slide 6

Slide 6 text

ある日オンプレからIaaS に移行することになった。へー。ついに資産を持たなくなるんすね。 MW のバージョンも最新に上げるぞ。わー。下位互換のチェック大変だなぁ。古いモジュールも現行の構成に焼き直す。 ...。結構やばい量のタスクだぞ....。あとな、Oracle からPostgreSQL にするぞ。 UNCOVERTRUTH Tech sake #2 6

Slide 7

Slide 7 text

は？ UNCOVERTRUTH Tech sake #2 7

Slide 8

Slide 8 text

「今回の移行は膨れ上がったOracle 保守費用を捨てることが裏テーマなんだってさ。」 UNCOVERTRUTH Tech sake #2 8

Slide 9

Slide 9 text

ORM は偉大実は移行対象のアプリケーションはORM をつかっていなかった。 XML にSQL テンプレートを書き、パラメータをパースしてQuery をビルドするスタイル。当然、方言・独自関数に依存したSQL テンプレート。現在の多くのORM はDialect を指定することで（そこそこ）吸収してくれる。 UNCOVERTRUTH Tech sake #2 9

Slide 10

Slide 10 text

SQL テンプレート > 3000 UNCOVERTRUTH Tech sake #2 10

Slide 11

Slide 11 text

テンプレートパラメータ結合のname if/else, for の使用可能 RDBMS- 言語間の型マッピング指定（DATE） select id, name, member_type from users where role = ${selected_role} $if( ${prefecture} ) and prefecture_code = ${prefecture} $endif UNCOVERTRUTH Tech sake #2 11

Slide 12

Slide 12 text

UNCOVERTRUTH Tech sake #2 12

Slide 13

Slide 13 text

Oracle -> PostgreSQL dual 表/sysdate Oracle のSELECT 文はfrom 句が必須なので、仮の表としてdual 表を指定する。 PostgreSQL にはsysdate がない。(MySQL はある) select sysdate from dual; select now(); select current_timestamp(); UNCOVERTRUTH Tech sake #2 13

Slide 14

Slide 14 text

Oracle -> PostgreSQL 文字列連結演算子 char_col fmt_char 'Hoge' '[Hoge]' 'Foo' '[Foo]' 'Bar' '[Bar]' select char_col, '['|| char_col ||']' as fmt_char from table UNCOVERTRUTH Tech sake #2 14

Slide 15

Slide 15 text

Oracle -> PostgreSQL 文字列連結演算子 select char_col, '['|| char_col ||']' as fmt_char from foo_table; char_col fmt_char 'Hoge' '[Hoge]' 'Foo' '[Foo]' 'Bar' '[Bar]' NULL NULL UNCOVERTRUTH Tech sake #2 15

Slide 16

Slide 16 text

あっ UNCOVERTRUTH Tech sake #2 16

Slide 17

Slide 17 text

Oracle -> PostgreSQL 文字列連結演算子 Oracle はNULL 値が与えられると空文字と同等として演算する。 select '[' || NULL || ']' from dual; >> '[]' PostgreSQL やMySQL のNULL 値演算はNULL を返す。 select '[' || NULL || ']' ; >> NULL UNCOVERTRUTH Tech sake #2 17

Slide 18

Slide 18 text

演算子オーバライド、独自演算子 PostgreSQL では独自の演算子を関数として定義することができるので、NULL を空文字として結合演算する独自演算子を作成。 select '[' ||| NULL ||| ']'; >> '[]' UNCOVERTRUTH Tech sake #2 18

Slide 19

Slide 19 text

UNCOVERTRUTH Tech sake #2 19

Slide 20

Slide 20 text

Oracle -> PostgreSQL NVL(expr1, expr2) expr1 がNULL の場合に、expr2 を返す select NVL(NULL, '') from dual; >> '' データをObject にマッピングするときに、 Null -> null のマッピングを防ぐために使用していた UNCOVERTRUTH Tech sake #2 20

Slide 21

Slide 21 text

Oracle -> PostgreSQL NVL2(expr1, expr2, expr3) expr1 がNULL でない場合にexpr2 を、 NULL の場合に expr3 を返す select NVL2('a', 1, 2), NVL2(NULL, 1, 2) from dual; >> 1,2 Nullable な外部キーをexpr1 に指定してグルーピングする場合などに使用 UNCOVERTRUTH Tech sake #2 21

Slide 22

Slide 22 text

Oracle -> PostgreSQL PostgreSQL にはNVL/NVL2 がない... UNCOVERTRUTH Tech sake #2 22

Slide 23

Slide 23 text

Oracle -> PostgreSQL COALESCE select coalesce(col1, col2, col3...) 引数のなかで最初のNULL じゃない値を返す NVL の代替にできる NVL2 の代替手段はCASE 文でした UNCOVERTRUTH Tech sake #2 23

Slide 24

Slide 24 text

UNCOVERTRUTH Tech sake #2 24

Slide 25

Slide 25 text

Oracle -> PostgreSQL 階層問い合わせ (start with/connect by) UNCOVERTRUTH Tech sake #2 25

Slide 26

Slide 26 text

Oracle -> PostgreSQL 階層問い合わせ id parent_id category_name 1 null メンズ 2 1 トップス 3 2 T シャツ 4 2 トレーナー 5 null セール 6 1 パンツ UNCOVERTRUTH Tech sake #2 26

Slide 27

Slide 27 text

階層問い合わせ (start with/connect by) select prior category_name as parent, category_name as name from t start with parent_id is null connect by prior id=parent_id where parent_id is not null; parent name メンズトップストップス T シャツトップストレーナーセールパンツ UNCOVERTRUTH Tech sake #2 27

Slide 28

Slide 28 text

階層問い合わせ (with 句による再帰) WITH r(id, parent_id, category_name) AS ( select id, parent_id, category_name from t where parent_id IS NULL UNION ALL select s.id, s.parent_id, s.category_name as parent from t as s, r where r.id = s.parent_id ) search depth first BY id set ordcol SELECT r.parent, r.category_name as name FROM r WHERE r.parent_id i not null ORDER BY ordcol; UNCOVERTRUTH Tech sake #2 28

Slide 29

Slide 29 text

Oracle -> PostgreSQL Materialized View UNCOVERTRUTH Tech sake #2 29

Slide 30

Slide 30 text

Oracle -> PostgreSQL Materialized View UNCOVERTRUTH Tech sake #2 30

Slide 31

Slide 31 text

Oracle -> PostgreSQL Materialized View 実は割りと最近実装された（9.3～）。移行時にはリリースされてなかった。 UNCOVERTRUTH Tech sake #2 31

Slide 32

Slide 32 text

Oracle -> PostgreSQL Rule UNCOVERTRUTH Tech sake #2 32

Slide 33

Slide 33 text

Oracle -> PostgreSQL Rule Insert/Update/Delete を監視指定したテーブルへの操作が実行された場合に、その操作を横取りできる元の操作も実行するかどうか選べる。DML のオーバライドみたいな感じ Select もできるけどView と変わらない UNCOVERTRUTH Tech sake #2 33

Slide 34

Slide 34 text

UNCOVERTRUTH Tech. sake #2 Shoota Kumano ( @shoota ) UNCOVERTRUTH Tech sake #2 34