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

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  

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

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

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

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

關於 Rails   By  ihower  

Rails  對其它語言影響   By  ihower  

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

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

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

CakePHP  2.3.6  #1  

CakePHP  2.3.6  #2  

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

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

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

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

‘STRING’  is  equal  to  0  

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  

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

今天的 DEMO  是基於   PostgreSQL  9.2    

Database  被搞壞了  

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

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

Database  變慢   Big  Data  !?  

Big  定義是什麼？  

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   –  一天幾百萬上下  

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  

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  帶來的風險.    

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;    

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  ?  

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  ……   )    

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

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

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

CTE  Example   •  Top  Terminal  Product  Sales   – Products_in_top_terminals  

CTE  Example   •  Top  Terminal  Product  Sales   – Products_in_top_terminals  

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

工程師角度期望的報表  

客戶期望報表1  

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

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

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

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

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

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

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

PLV8   PostgreSQL  procedual  language  powered  by  V8   JavaScript  Engine  

1003  tables  in  cookpad?  

PostgreSQL  on  the  Mac   •  Postgres.app  

postgres.heroku.com  

Thanks