2015年1月29日 星期四

[SQL] select n rows from each group @ MySQL 5.6

假設有一張 table 名為 log 長這樣:

{
id INTEGER,
level VARCHAR(16),
user VARCHAR(32)
}

mysql> SELECT * FROM log
1, "SA", "admin1"
2, "SA", "admin2"
3, "SA", "admin3"
4, "SA", "admin4"
5, "RD", "programmer1"
6, "RD", "programmer2"
7, "RD", "programmer3"
8, "RD", "programmer4"
9, "RD", "programmer1"
10, "FAE", "programmerA"
11, "FAE", "programmerB"
12, "FAE", "programmerC"


有沒有一招可以撈出,讓每個 Level 只顯示 3 筆資料?假想成果:

mysql> SELECT ... FROM log GROUP BY level
1, "SA", "admin1"
2, "SA", "admin2"
3, "SA", "admin3"
5, "RD", "programmer1"
6, "RD", "programmer2"
7, "RD", "programmer3"
10, "FAE", "programmerA"
11, "FAE", "programmerB"
12, "FAE", "programmerC"


土法煉鋼法,用 UNION ALL 來處理:

mysql> SELECT * FROM (SELECT * FROM log WHERE level = 'SA' LIMIT 3) AS t UNION ALL (SELECT * FROM log WHERE level = 'RD' LIMIT 3) UNION ALL (SELECT * FROM log WHERE level = 'FAE' LIMIT 3);

所幸,問了一下強者我同學,得到個關鍵字:GROUP_CONCAT , http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_group-concat

mysql> SELECT level, GROUP_CONCAT(user) FROM log GROUP BY level;
"SA", "admin1,admin2,admin3"
"RD", "programmer1, programmer2, programmer3"
"FAE", "programmerA, programmerB, programmerC"


如果想限制撈出的資料個數,要設定 group_concat_max_len:

mysql> SET group_concat_max_len = 2;
mysql> SELECT level, GROUP_CONCAT(user) FROM log GROUP BY level;
"SA", "admin1,admin2"
"RD", "programmer1, programmer2"
"FAE", "programmerA, programmerB"


雖然上述結果還不太適合再做 JOIN 來處理,但,已經算佛心了... XD

其他 Google 用的關鍵字:"select top n rows from each group",會看到一些 RANK() OVER(PARTITION BY level) ,但對 MySQL 應該不適用 XD 強者我同學說,若在 PostgreSQL 可以用:

postgrel> select level, array_aggr(user)[0:1] from table group by level;

看來該多給 PostgrelSQL 機會 XD (當初案子用到 GIS 相關 plugin 才有用它...)

此外,跟強者我同學閒聊時,發現去年的一些經驗還滿適合使用的,有些查詢很久的指令,可以考慮定期產生並儲存在另一張 tabel,降低一般 client 觸發複雜的 SQL Query,像是 JOIN, GROUP 等,這也是在大型服務中也常用到的方式。算是此次閒聊最大的心得,因為去年也有應用這個架構來處理服務,驗證自已的(偷懶)做法無誤 XDDD

2015年1月24日 星期六

[Linux] 快速安裝 Gitlab 及簡易設定 @ Ubuntu 14.04

感覺近年來幾套出色的管理套件都是 Ruby 派的,例如 Redmine 等,然而有許多安裝瑣事要處理 Orz 所幸 Gitlab 官方有打包一個 debian package 讓初學者可以快速上手:

$ wget https://downloads-packages.s3.amazonaws.com/ubuntu-14.04/gitlab_7.6.2-omnibus.5.3.0.ci.1-1_amd64.deb
$ sudo apt-get install openssh-server
$ sudo apt-get install postfix # Select 'Internet Site'
$ sudo dpkg -i gitlab_7.6.2-omnibus.5.3.0.ci.1-1_amd64.deb
$ sudo gitlab-ctl reconfigure


如此一來,預設 http://localhost/ 就可以登入查看了。然而,預設環境比較適合體驗啦,我又多做了幾個動作,包含也在系統裝上 Ruby2.0 方便未來的相關使用。

更新 Gitlab 環境:
$ sudo vim /etc/gitlab/gitlab.rb

# 對外 Web 資訊
external_url = 'http://YourPublicDomainName'
# 對外 SSH 資訊
gitlab_rails['gitlab_ssh_host'] = 'YourPublicDomainName'
# 時區
gitlab_rails['time_zone'] = 'Asia/Taipei'
# 開啟 https 服務, 以及強制使用 https
nginx['redirect_http_to_https'] = true
nginx['redirect_http_to_https_port'] = 80
nginx['ssl_certificate'] = "/etc/gitlab/ssl/YourPublicDomainName.crt"
nginx['ssl_certificate_key'] = "/etc/gitlab/ssl/YourPublicDomainName.key"

$ sudo gitlab-ctl reconfigure
$ sudo gitlab-ctl restart


更新 Postfix 環境:

$ sudo vim /etc/postfix/main.cf
myhostname = YourPublicDomainName
$ sudo postfix reload


安裝 Ruby 2.0 (非必要):

$ sudo apt-get update
$ sudo apt-get install ruby2.0 ruby2.0-dev
$ ruby -v
ruby 1.9.3p484 (2013-11-22 revision 43786) [x86_64-linux]
$ gem -v
1.8.23
$ sudo ln -sf /usr/bin/ruby2.0 /usr/bin/ruby
$ sudo ln -sf /usr/bin/gem2.0 /usr/bin/gem
$ ruby -v
ruby 2.0.0p384 (2014-01-12) [x86_64-linux-gnu]
$ gem -v
2.0.14


以上則是稍微完整一點的調整。

然而,有時為了確保服務長久性,會有一些變動方式,例如 gitlab 資料儲存在於 /var/opt/gitlab ,可以給予一個獨立空間維護,因此,在安裝 gitlab 軟體前,先自行把 /var/opt/gitlab 擺在額外的 partition (此例是 AWS EC2 管理,給予一個 64GB 空間: http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-using-volumes.html ):

$ lsblk
NAME    MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
xvda    202:0    0  20G  0 disk
└─xvda1 202:1    0  20G  0 part /
xvdb    202:16   0   4G  0 disk /mnt
xvdc    202:32   0  64G  0 disk
$ sudo mkfs -t ext4  /dev/xvdc
$ sudo mkdir /64GB
$ sudo mount /dev/xvdc /64GB
$ sudo mkdir -p /64GB/gitlab /var/opt
$ sudo ln -s /64GB/gitlab /var/opt/gitlab


接著再跑安裝 gitlab 流程...如果已經安裝了,那就是配置好 /64GB 後:

$ sudo gitlab-ctl stop
ok: down: logrotate: 1s, normally up
ok: down: nginx: 0s, normally up
ok: down: postgresql: 0s, normally up
ok: down: redis: 1s, normally up
ok: down: sidekiq: 0s, normally up
ok: down: unicorn: 0s, normally up
$ sudo cp -a /var/opt/gitlab /64GB/gitlab
$ sudo mv /var/opt/gitlab /var/opt/gitlab-bak
$ sudo ln -s /64GB/gitlab /var/opt/gitlab
$ sudo gitlab-ctl start

2015年1月21日 星期三

[C++] JSON Library - jsoncpp @ Ubuntu 14.04, Mac OS X 10.10.1

JSON Format 現今已經是一個非常常見的溝通管道,最近常在 Ubuntu server 上練習 C++,偶爾沒網路時也會想用 Mac OS X 進行,因此,著重在如何輕鬆管理 C++ JSON Library 的用法,找了一下,大概就是 jsoncpp 啦

在 Ubuntu 14.04:

$ sudo apt-get install libjsoncpp-dev
$ g++ -I/usr/include/jsoncpp/ main.cpp -ljsoncpp


在 Mac OS X 10.10.1 搭配 MacPorts 管理:

$ sudo port install jsoncpp
$ g++ -std=c++11 -I/opt/local/include/ -L/opt/local/lib main.cpp -ljsoncpp


由於都可以透過系統常見的管理工具安裝,即可省去下載 source code 編譯等管理成本。簡易範例:

$ vim example.cpp
#include <iostream>       // std::cout
#include <string>         // std::string
#include <json/json.h>

int main() {
        std::string raw = "{\"key\":\"value\",\"我\":\"是誰\",\"array\":[\"a\",\"b\",123]}";
        Json::Reader reader;
        Json::Value value;
        std::cout << "Input: " << raw << std::endl;
        if (reader.parse(raw, value)) {
                std::cout << "parsing: " << value ;//<< std::endl;
                std::cout << "get: " << value["我"] ;//<< std::endl;
                std::string data = value["key"].asString();//toStyledString();
                std::cout << "string: [" << data << "]" << std::endl;

                // with -std=c++11
                std::cout << "---" << std::endl;
                for (auto it : value) {
                        std::cout << "elm: " << it ;//<< std::endl;
                        if (it.isArray()) {
                                for (auto it2 : it)
                                        std::cout << "array data: " << it2 ;//<< std::endl;
                        }
                }        
        }
        return 0;
}
$ g++ -I/opt/local/include/ -L/opt/local/lib main.cpp -ljsoncpp
$ ./a.out
Input: {"key":"value","我":"是誰","array":["a","b",123]}
parsing:
{
"array" : [ "a", "b", 123 ],
"key" : "value",
"我" : "是誰"
}
get: "是誰"
string: [value]
---
elm: [ "a", "b", 123 ]
array data: "a"
array data: "b"
array data: 123
elm: "value"
elm: "是誰"

2015年1月8日 星期四

[MySQL] 使用 FOREIGN KEY 筆記 @ MySQL 5.6

話說,2014年算是我最常用 SQL DB 的一年,在這之前我都是用 NOSQL 架構 Orz (扣除 SQLite 啦)。最近設計一些新服務的資料儲存,正在想如何使用 MySQL Relations 的特色,才發現之前都沒在用 FOREIGN KEY 啦 :P

簡短介紹 FOREIGN KEY 的功用:

當設計很多階層性的 table 時,如 user (上層), group (中層), data (下層),其中 data 每一筆都有 user, group 資訊,而 group 裡每一筆都跟 user 有關。此時在 DELETE 事件發生時,如果有建立 Relations 時,可在上層資料刪除時,順便幫你把相關資料刪掉。此外,在新增下層資料時,也會幫你驗證資料的正確性,不會讓你隨意新增假資料。

對於要建立 FOREIGN KEY 的要件:
  • 在上層(Parent)預計使用的參考欄位要有 index 屬性,如 Primary Key, Unique Key 或 Index 都行
  • 在下層(Child)預計使用的欄位也要有 index 屬性
  • 在上層跟下層對應的欄位型態要一樣,例如一樣為非負整數等
此外,則是可以設定 Action,例如上層刪除、更新資料時,下層是否也要一同更新,其選項:
  • Restrict: 拒絕 parent event,也將造成 parent 動作失敗
  • Cascade: 跟著 parent event 更新或是刪除
  • Set null: 收到 parent event 時,將參考欄位設定成 null
  • No action: 標準 SQL 語法且為預設選項,在 MySQL 環境上與 Restrict 等價
下次是操作時碰到的問題:

Q: Cannot add foreign key constraint
A: 仔細確認一下指定的欄位,其型態是不是一致的,或是對應的 Action 若為 Set Null 時,要確認資料欄位是否允許 Null

Q: Cannot add or update a child row: a foreign key constraint fails
A: 追蹤一下已存在的資料,是不是有不合理的地方,例如下層存在一筆資料,其對應上層的資料無法匹配。解法就是手動修正,或是乾脆清光資料來做也行。

整體上,建議有參考關係時,可以面對 Parent delete event 時,可以設定成 Cascade 處理,如此一來刪除資料就輕鬆許多,不必做多個處理。

2015年1月2日 星期五

MongoDB 開發筆記 - 替 Mongoose (Node.js MongoDB Driver) 送 patch 的經驗

大概 2014 年 11 月附近,下了班仍會抽空把玩 MongoDB ,當時就決定搭配 node.js 來把玩 :P 當時在挑選 Node.js MongoDB Driver 時,看到官方文件提到了 Mongoose ,就順勢把玩了幾下。

對於 nodejs - mongoose 跟 nodejs - mongodb 的差異點在於 mongoose 做了滿多整合方式,例如 QueryStreams 的效果,在處理大量資料時,非常便利!

除了適應 node.js 的 async 架構外,又發現 mongoose 跟原生 mongodb 設計上已經有些 out of date,在 Stack Overflow 和 mongoose issue 有不少抱怨,就試著 trace mongoose 架構,再送看看 patch,很幸運地被接受了 :P 而且只多寫了 2~3 行 code,果真好的 framework 都已經設計的很好,可能花了 15 分鐘了解架構,而 testing 花了 30 分鐘,結果 coding 才 3 分鐘 XDD

只是送 patch 的過程並沒有很順利,包含未先讀 mongoose / CONTRIBUTING.md 的描述就直接上 code,被挑了 coding style 問題,另外,還沒有跑 unit test XDDD 就碰到 CI 回報錯誤 Orz 接著才發現補上的那段 code 並不嚴謹。把 code 嚴謹化後,CI 也直接回報可以 merge :) 就這樣非常快地 2 天內就被 merge 進 master 了。

這次 patch 經驗:

  • 開發上碰到需求不被滿足,接著搜尋 Stack Overflow 及該專案的 issue tracking,發現問題一直沒被解決時,就可以著手看看要不要抽時間來貢獻
  • 接著發現解法後,一定要先看專案的 CONTRIBUTING 規定
  • 不錯的專案一定都有 unit test,記得跑一下
  • 提供簡單的 testing 程式碼跟預期結果,可以加速 commiter 閱讀及提升認同感

剩下的就只要花時間等待了 Orz 例如這次的 commiter 是 MongoDB New York RD,作息剛好日夜顛倒,因此每一次完整的互動大概都花了 2 天。