UT Teck sake #2

おつまみ

Transcript

1. Lovely, Relational DataBase UNCOVERTRUTH Tech. sake #2 Shoota Kumano ( @shoota ) UNCOVERTRUTH Tech sake #2 1
2. TOC 今日はRDBMS の思い出とその背景 を肴に、 を飲もうと思います。 UNCOVERTRUTH Tech sake #2 2
3. About me. Shuta Kumano @shoota { 'age': 32, 'address': ' 青森県八戸市', 'jobs': [ 'Web システム開発/ 保守', 'iOS app. 開発', 'Android app. 開発', ] } UNCOVERTRUTH Tech sake #2 3
4. Q: SQL 好きですか？ UNCOVERTRUTH Tech sake #2 4
5. A: 僕はすきです。 - Oracle 9i/10g/11g - PostgreSQL - MySQL ロジック集合 パズルを解く感覚 一般解と最適解 1 Query Dream No Programming, but Programable UNCOVERTRUTH Tech sake #2 5
6. ある日 オンプレからIaaS に移行することになった。 へー。 ついに資産を持たなくなるんすね。 MW のバー ジョンも最新に上げるぞ。 わー。 下位互換のチェック大変だなぁ。 古いモジュー ルも現行の構成に焼き直す。 ...。 結構やばい量のタスクだぞ....。 あとな、Oracle からPostgreSQL にする ぞ。 UNCOVERTRUTH Tech sake #2 6
7. は？ UNCOVERTRUTH Tech sake #2 7
8. 「 今回の移行は膨れ上がったOracle 保守費用を 捨てることが裏テー マなんだってさ。」 UNCOVERTRUTH Tech sake #2 8
9. ORM は偉大 実は移行対象のアプリケー ションはORM をつかっ ていなかった。 XML にSQL テンプレー トを書き、 パラメー タをパ ー スしてQuery をビルドするスタイル。 当然、 方言・ 独自関数に依存したSQL テンプレー ト。 現在の多くのORM はDialect を指定することで（ そ こそこ） 吸収してくれる。 UNCOVERTRUTH Tech sake #2 9
10. SQL テンプレー ト > 3000 UNCOVERTRUTH Tech sake #2 10
11. テンプレー ト パラメー タ結合のname if/else, for の使用可能 RDBMS- 言語間の型マッピング指定（DATE） <!-- example --> select id, name, member_type from users where role = ${selected_role} $if( ${prefecture} ) and prefecture_code = ${prefecture} $endif UNCOVERTRUTH Tech sake #2 11
12. UNCOVERTRUTH Tech sake #2 12
13. 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
14. 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
15. 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
16. あっ UNCOVERTRUTH Tech sake #2 16
17. Oracle -> PostgreSQL 文字列連結演算子 Oracle はNULL 値が与えられると空文字と同等として 演算する。 select '[' || NULL || ']' from dual; >> '[]' PostgreSQL やMySQL のNULL 値演算はNULL を返す。 select '[' || NULL || ']' ; >> NULL UNCOVERTRUTH Tech sake #2 17
18. 演算子オー バライド、 独自演算子 PostgreSQL では独自の演算子を関数として定義する ことができるので、NULL を空文字として結合演算す る独自演算子を作成。 select '[' ||| NULL ||| ']'; >> '[]' UNCOVERTRUTH Tech sake #2 18
19. UNCOVERTRUTH Tech sake #2 19
20. Oracle -> PostgreSQL NVL(expr1, expr2) expr1 がNULL の場合に、expr2 を返す select NVL(NULL, '') from dual; >> '' デー タをObject にマッピングするときに、 Null -> null のマッピングを防ぐために使用していた UNCOVERTRUTH Tech sake #2 20
21. 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
22. Oracle -> PostgreSQL PostgreSQL にはNVL/NVL2 がない... UNCOVERTRUTH Tech sake #2 22
23. Oracle -> PostgreSQL COALESCE select coalesce(col1, col2, col3...) 引数のなかで最初のNULL じゃない値を返す NVL の代替にできる NVL2 の代替手段はCASE 文でした UNCOVERTRUTH Tech sake #2 23
24. UNCOVERTRUTH Tech sake #2 24
25. Oracle -> PostgreSQL 階層問い合わせ (start with/connect by) UNCOVERTRUTH Tech sake #2 25
26. 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
27. 階層問い合わせ (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
28. 階層問い合わせ (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
29. Oracle -> PostgreSQL Materialized View UNCOVERTRUTH Tech sake #2 29
30. Oracle -> PostgreSQL Materialized View UNCOVERTRUTH Tech sake #2 30
31. Oracle -> PostgreSQL Materialized View 実は割りと最近実装された（9.3～）。 移行時にはリリー スされてなかった。 UNCOVERTRUTH Tech sake #2 31
32. Oracle -> PostgreSQL Rule UNCOVERTRUTH Tech sake #2 32
33. Oracle -> PostgreSQL Rule Insert/Update/Delete を監視 指定したテー ブルへの操作が実行された場合に、 その操作を横取りできる 元の操作も実行するかどうか選べる。DML のオー バライドみたいな感じ Select もできるけどView と変わらない UNCOVERTRUTH Tech sake #2 33
34. UNCOVERTRUTH Tech. sake #2 Shoota Kumano ( @shoota ) UNCOVERTRUTH Tech sake #2 34