Slide 1

Slide 1 text

改善 Programmer  生活的   SQL  技能 #1   Rack  Lin  阿土伯  @RubyTue24  

Slide 2

Slide 2 text

about:me   •  ViViPOS  Co.,  Ltd    技術總監   –  利用 JavaScript  寫 〞收銀機〞(傳統產業,全新感 受)   •  PHP  /  JavaScript  LiveScript  /  Java  (目前移情於 Scala  )     •  [email protected]   •  hKps://twiKer.com/racklin   •  hKp://www.plurk.com/racklin   •  hKps://www.facebook.com/racklin1002  

Slide 3

Slide 3 text

一開始題目是   • Programmer  如何與            DBA  真心相愛  

Slide 4

Slide 4 text

但是   • Programmer  如何與            DBA  真心相愛   • 苦命的 PG  通常兼 DBA  

Slide 5

Slide 5 text

今天也不談 DBA   •  這裡有堂課好便宜     – OCP-­‐DBA     今天也不是 SQL 教學  

Slide 6

Slide 6 text

何謂   • 改善 Programmer 生活   – 更快、更好、更簡潔   – 多一點時間陪家人、朋友   – 別限制在框架中  

Slide 7

Slide 7 text

關於 Rails   By  ihower  

Slide 8

Slide 8 text

Rails  對其它語言影響   By  ihower  

Slide 9

Slide 9 text

Rails  超讚,影響深遠   在 ruby  場子,政治正確  

Slide 10

Slide 10 text

但是關於 ORM  ….   •  hKp://file.ithome.com.tw/20120720/401_1615-­‐1710%20JRuby_Wang-­‐ Shen%20Lu.pdf  

Slide 11

Slide 11 text

你了解 ORM  做了什麼嗎?   •  Rails   •  Java   – Hibernate   – Grails   •  PHP   – Lavara   – Phalcon   – CakePHP  

Slide 12

Slide 12 text

CakePHP  2.3.6  #1  

Slide 13

Slide 13 text

CakePHP  2.3.6  #2  

Slide 14

Slide 14 text

你的 DB  只做 CRUD ?   •  CREATE  TABLE   INSERT  INTO   SELECT   DELETE  FROM      

Slide 15

Slide 15 text

老舊的 Database  Server  ?   •  mSQL  ?  (1993)   •  MySQL  <  3.x  ?    

Slide 16

Slide 16 text

ORM  迷思   •  ORM  開發者企圖簡化了使用者直接操作 SQL  ,  但不會產出 “較美觀”SQL  .   – hasAndBelongsToMany  (CAKEPHP  SourceCode)   •  SELECT  a  from  A  where  a  IN(  …………..  )  產出超長 SQL  。   •  上線一陣子後就死的不明不白。   •  ORM  開發者企圖讓您在各個資料庫平台中 移植你的程式,所以不會產出 “最佳 化”SQL.      

Slide 17

Slide 17 text

關於 MySQL   •  曾經我也是用 MySQL  ,直到我膝蓋中了一 箭。     – SELECT  *  FROM  users  WHERE  username=‘rack’   AND  paswd  =  0;     – hKp://sqlfiddle.com/#!2/pf31/5/0  

Slide 18

Slide 18 text

‘STRING’  is  equal  to  0  

Slide 19

Slide 19 text

Type  handling   •  了解 Framework  如何處理變數   – DB  Column  Type  or  Input  Data  Type  ?     – bindParam  with  custom  query   •  SELECT  a.user  as  aa  ,  a.passwd  as  bb  WHERE  aa  =  ?  AND     bb  =  ?   •  Resqul  Services   •  XML   •  JSON  

Slide 20

Slide 20 text

PostgreSQL  Type  Handling   看到這 Hint  ,  感動到眼淚都流下來了!  

Slide 21

Slide 21 text

今天的 DEMO  是基於   PostgreSQL  9.2    

Slide 22

Slide 22 text

Database  被搞壞了  

Slide 23

Slide 23 text

Schema   •  Linux  CHROOT  ,  FreeBSD  jail  .     •  Per  User  Schema   –  永遠不要信任:   •  使用者   •  新手開發者   •  Per  App  Schema   –  正式環境中的 Staging   •  Schema  And  search_path  .  

Slide 24

Slide 24 text

Schema  in  VIVIPOS   •  SELECT  COUNT(schema_name)  as  K  FROM   informaton_schema.schemata;   –   372  schema   – 為難搞的客戶和新手鎖在建立獨立的 Schema    

Slide 25

Slide 25 text

Database  變慢   Big  Data  !?  

Slide 26

Slide 26 text

Big  定義是什麼?  

Slide 27

Slide 27 text

Big  Data   •  你的  Big  Data   –  百萬筆級   •  別人的 Small  Data     –  VIVIPOS  單一客戶      -­‐  (ec2  large  +  500G  EBS)   •  1000        transactons/day   •  1500        clients     •  4  *365                    years   =  2,190,000,000        (21億筆)   •  我的 Big  Data,  金融業的 Small  Data   –  一天幾百萬上下  

Slide 28

Slide 28 text

PostgreSQL  特殊功能   •  Partton  Table   •  Partal  Index   •  DBLink   – Connect  Remote  DB  Server  as  View   •  Data  Wrappers   – MySQL  /  Oracle  /  Redis  /  Mongo hKp://wiki.postgresql.org/wiki/Foreign_data_wrappers  

Slide 29

Slide 29 text

Database  View   •  Fine-­‐grained  Views   – Users  Table   •  Username,password,email,cellphones…..   – Views   •  user_auths    (username,  password)   •  User_profiles  (不含 password)     •  Mapping  Model  to  VIEW   •  降低 Mass  Assignment  帶來的風險.    

Slide 30

Slide 30 text

Updatable  View   •  View  是唯讀的   – Model  Mapping  to  View 也是唯讀的.     •  MySQL  5.5  後支援   •  PostgreSQL   – USE  RULE   CREATE  RULE  user_login_update  AS    ON  UPDATE  TO  user_login      DO  INSTEAD            UPDATE  users  SET  password  =  new.password              WHERE  users.account  =  new.account;    

Slide 31

Slide 31 text

Database  View   •  Simplify  Model  associatons   –  User   •  belongsTo  Compony   –  Compony   •  hasMany  User   •  belongsTo  Region   –  Region   •  hasMany  Machine   •  Get  User  Machines  Lists  ?   –  N+1  Query  ?     –  Prepare  in  Controller  ?  

Slide 32

Slide 32 text

Database  View   •  建立 user_machines  VIEW         •  Mapping  UserMachine  to  ‘user_machines’   VIEW   CREATE  View  user_machines  AS  (   SELECT  u.id  as  user_id,  m.*  FROM  users  u   INNER  JOIN  user_componies  ….   INNER  JOIN  compony_regions  ….   LEFT  JOIN  region_machines  ……   )    

Slide 33

Slide 33 text

Complex  SQL  and  Joins   未完! 待續……..  共四頁!  

Slide 34

Slide 34 text

Complex  SQL  and  Joins   •  需要多個 Sub-­‐Query     •  需要 Temporary  Table  儲存運算過程中的資 料,並重覆引用   •  SQL  複雜到爸媽都認不得      

Slide 35

Slide 35 text

Common  Table  Expression   •  在單⼀一  SELECT、INSERT、UPDATE、DELETE   或  CREATE  VIEW  陳述式之執⾏行範圍內定義 的暫存結果集。CTE  與衍⽣生資料表類似的地 ⽅方在於,它不會儲存為物件,⽽而且其⽣生命 週期僅限於查詢期間.   – 建⽴立遞迴查詢   – 您不需要在中繼資料中儲存定義   – 在相同陳述式中多次參考結果資料表  

Slide 36

Slide 36 text

CTE  Example   •  Top  Terminal  Product  Sales   – Products_in_top_terminals  

Slide 37

Slide 37 text

CTE  Example   •  Top  Terminal  Product  Sales   – Products_in_top_terminals  

Slide 38

Slide 38 text

天不從人願   •  貨幣匯率表   Create  Table  exgRate  (        exgRateId  serial  primary  key,  -­‐-­‐  主鍵        factTime  tmestamp,                       -­‐-­‐  匯入時間        currency  char(3),                            -­‐-­‐  貨幣別        purpose  varchar(2),                       -­‐-­‐  匯率種類        currType  varchar(10),                   -­‐-­‐  貨幣種類        rate  numeric(18,6)                         -­‐-­‐  匯率   );  

Slide 39

Slide 39 text

工程師角度期望的報表  

Slide 40

Slide 40 text

客戶期望報表1  

Slide 41

Slide 41 text

客戶期望報表2   一個月後…………    

Slide 42

Slide 42 text

客戶期望報表N   三個月後…………    

Slide 43

Slide 43 text

Data  Mining  ?   •  RD  找 PM  抱怨   •  PM  找客戶抱怨   •  客戶向天抱怨   – 為什麼 Data  Mining  變成 Data  Money  $$$  

Slide 44

Slide 44 text

PIVOT  /  Crosstab   •  一般稱為「樞紐分析表」,PostgreSQL  為 「交叉表」   •  SELECT  *  FROM  crosstab('...')  AS  ct(row_name   text,  category_1  text,  category_2  text);  

Slide 45

Slide 45 text

crosstab  demo   •  客戶報表其實很簡單!   – 客戶期望表 N  -­‐  Crosstab_2    

Slide 46

Slide 46 text

Stored  Procedure   OK!    那我們跳過不談 Stored  Procedure  .  

Slide 47

Slide 47 text

Trigger   •  觸發程序是⼀一種在資料庫伺服器發⽣生事件 時,會⾃自動執⾏行的特殊預存程序   – Logging     •  Logging  in  controller  !?   – Backup  data   – Calling  Stored  Procedure  from  Triggers  

Slide 48

Slide 48 text

PLV8   PostgreSQL  procedual  language  powered  by  V8   JavaScript  Engine  

Slide 49

Slide 49 text

1003  tables  in  cookpad?  

Slide 50

Slide 50 text

PostgreSQL  on  the  Mac   •  Postgres.app  

Slide 51

Slide 51 text

postgres.heroku.com  

Slide 52

Slide 52 text

Thanks