2012/05/31

MySQL INSERT 與 UPDATE 的一些特殊情況用法

最近剛好在設計 social network 的 database schema,遇到很多 special query 的需求,在此一一記錄下來。

如果不存在才 INSERT,存在就 skip
舉例來說,如果想記錄某 user 是否去過某國家
-----------------
 user  | country
-------+---------
 tony  | US
 tony  | HK
 kelly | US
Query:
INSERT INTO `trip` (`user`, `country`) 
SELECT 'tony', 'US' FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `trip` WHERE `user` = "tony" AND `country` = "US" LIMIT 1
);
* 想 INSERT 的 table 可以跟檢查存在性的 table 不同個。

參考資料
mysql - Insert Where Not Exists-Without Primary Key - Stack Overflow



如果不存在才 UPDATE,否則 skip
舉例來說,你想讓 user 更新 email 欄位(unique),但 email 又不能跟其他人一樣:
------------------------------
 user      | email
-----------+------------------
 olivia    | olivia@email.com
 andy_wang | andy@email.com
 andy_lin  | (NULL)
Query:
UPDATE `profile` SET `email` = "andy@email.com" WHERE `user` = "andy_lin" AND NOT EXISTS
(SELECT 1 FROM `profile` WHERE `email` = "andy@email.com");
但你會發現 mysql execute query 會有下面錯誤:
You can't specify target table 'profile' for update in FROM clause
應該改成:
UPDATE `profile` SET `email` = "andy@email.com" 
WHERE `user` = "andy_lin" AND NOT EXISTS
  (SELECT * FROM (SELECT 1 FROM `profile` WHERE `email` = "andy@email.com") temp);

參考資料
MySQL can’t specify target table for update in FROM clause | VerySimple


2012/05/29

Python 串列(List)、字典(Dict) 的排序

List
直接對list本身排序
>>> l = [3, 2, 5, 4, 1]
>>> l.sort()
>>> l
[1, 2, 3, 4, 5]
>>> l.sort(reverse=True)
>>> l
[5, 4, 3, 2, 1]

不動到原始順序
>>> l = [3, 5, 4]
>>> sorted(l)
[3, 4, 5]
>>> l
[3, 5, 4]
根據element的運算結果
>>> l = ["tom", "Michael", "jenny"]
>>> l.sort(key=str.lower)
>>> l
['jenny', 'Michael', 'tom']


Dictionary
Dict本身是無序的,所以只能在輸出時做排序
>>> d = {"b": 1, "a": 3, "c": 2}
>>> d
{'a': 3, 'c': 2, 'b': 1}
>>> sorted(d)
['a', 'b', 'c']
>>> for key in sorted(d):
...     "%s: %d" % (key, d[key])
... 
'a: 3'
'b: 1'
'c: 2'


Dict in List
List.sort() 內的 key 是排序的依據,可以是 element 的屬性或是 function 運算後的結果
By function
>>> l = [{"id": 3, "name": "john"}, {"id": 2, "name": "brandon"}, {"id": 1, "name": "susan"}]
>>> l
[{'id': 3, 'name': 'john'}, {'id': 2, 'name': 'brandon'}, {'id': 1, 'name': 'susan'}]
>>> def my_func(d):
...     return d['id']
... 
>>> l.sort(key=my_func) 
>>> l
[{'id': 1, 'name': 'susan'}, {'id': 2, 'name': 'brandon'}, {'id': 3, 'name': 'john'}]

簡寫,use in-line function
>>> l.sort(key=lambda d:d['id'])   
>>> l
[{'id': 1, 'name': 'susan'}, {'id': 2, 'name': 'brandon'}, {'id': 3, 'name': 'john'}]
>>> l.sort(key=lambda d:d['name'])
>>> l
[{'id': 2, 'name': 'brandon'}, {'id': 3, 'name': 'john'}, {'id': 1, 'name': 'susan'}]


參考資料
HowTo/Sorting - PythonInfo Wiki

2012/05/25

在 OS X 安裝 GraphicsMagick 和 Pgmagick

有時候在 Mac OS X 想自己抓 source code 回來裝都會很頭大,所以有人就會建議用套件管理程式,像 MacPortsHomeBrewFink,但說真的也不是什麼套件都在這地方找得到。


這次想在 OS X 安裝 GraphicsMagick 和 Pgmagick,GraphicsMagick 是一套圖形處理的 library,比 ImageMagick 更快更好。其實我也沒有要處理太複雜的事情,就 圖形的裁切、縮放、套用一些濾鏡而已,但速度是我考量的。而 Pgmagick 則是 for python 的 wrapper。


直接抓 GraphicsMagick 原始碼下來編譯
遇到問題:
Undefined symbols for architecture x86_64:
  "___builtin_object_size", referenced from:
      _BlurImageScanlines.omp_fn.1 in magick_libGraphicsMagick_la-effect.o
      _XShearImage.omp_fn.0 in magick_libGraphicsMagick_la-shear.o
      _YShearImage.omp_fn.1 in magick_libGraphicsMagick_la-shear.o
ld: symbol(s) not found for architecture x86_64
collect2: ld returned 1 exit status
make[1]: *** [magick/libGraphicsMagick.la] Error 1
make: *** [all] Error 2

遇到這種問題都很沒轍,上次遇到時好像是 dependency 的library configure 時沒有加 --enable-shared,但這次就不知道怎麼找了。

加了一些有的沒的環境變數 LDFLAGS="-arch x86_64" CXXFLAGS="-arch x86_64" CFLAGS="-m64" ... 沒用。

2012/05/21

Form validation in python web development

表單驗證

User 要傳資料到 server 不外乎就是透過 HTML 表單搭配 HTTP method GET/POST,為了避免 user 輸入奇怪的資料造成 server 異常,通常我們都會去限定各欄位值域、資料形態、格式...這些東西,如果自己用一堆 if 去寫一定會寫到瘋掉,好在網路上已經有現成的 library 可以用。

表單驗證有可以分別在 client 端或跟 server 端去做:
  • Client 端驗證
    好處是可以減少 server loading,少了傳輸的 latency,反應速度也比較快,但這只能防君子不能防小人,client 端驗證是用 javascript,有心人只要把 script 修改一下就可以避過。
    Library 可以參考:10 Useful jQuery Form Validation Techniques and Tutorials
  • Server 端驗證
    無論 client 端有沒有做,server 端做檢查是必然的事情啦!這也是我今天想介紹的,在 python 上我只用過一套:WTForms,一用就上癮,不只可以做到驗證,表單也可以自動產生。
    如果你 web framework 是用 flask,已經有現成的 extension: Flask-WTF 可以用,我自己是用 tornado,必須要另外疊一個 wrapper,幸好已經有人幫忙寫好了: flying with tornado on appengine

2012/05/13

讓 SimpleDB 支援 paging (offset)

在 implement page 的時候通常會用到 SQL 語法的"offset"
取第一頁:
SELECT * FORM `article` OFFSET LIMIT 10
取第二頁:
SELECT * FORM `article` OFFSET LIMIT 10 OFFSET 10
取第三頁:
SELECT * FORM `article` OFFSET LIMIT 10 OFFSET 20

但 AWS SimpleDB 並不支援 offset 這個與法,必須要用 next_token 方式來取
以下用python boto library做個示例:
sdb_conn = SDBConnection(...)

# Skip first 20 items
query = "SELECT COUNT(*) FORM `article` OFFSET LIMIT 20"
rs = sdb_conn.select(sdb_domain, query=query)

# Get target page
query = "SELECT * FORM `article` OFFSET LIMIT 10"
rs2 = sdb_conn.select(sdb_domain, query=query, next_token=rs.next_token)
for item in rs2:
    ...

"SELECT COUNT(*)" 是最有效率的略過方法,如果用"SELECT * "則會有 return 2500 rows 的限制!


 參考資料

2012/05/10

Redis 初試

Redis
是一種 key-value 的 storage,可以想成進階版的 memcached,它提供了更多種 data structure 與 operator。它不再只是一個儲存體,更能做一些邏輯運算,之前很多要在 client 端做的事情,都可以在 redis server 處理掉。
和其他 NoSQL 相比,資料儲存在 memory,存取速度超快,但也因此儲存空間會受限在 memory size,雖然支援分散式 (Master-Slave),但每個 node 都是一個映射,也就是儲存一模一樣的資料,所以什麼該放在 redis 也是需要仔細想過。它不是完全用來取代 database,而是針對經常存取、較複雜運算的部份再派它上場。

這三篇推薦看一下

這三份文件也可以看

2012/05/08

社交網路資料庫設計:怎麼找共同朋友、朋友的朋友

在 facebook 內當你點朋友時,你可以知道彼此共同朋友有哪些,另外在網頁右欄有個"你可能認識"的專區,這個功能是怎麼做的?

先來分析這兩個語意:
1. 共同的朋友:"自己的朋友"跟"朋友的朋友"重疊的部分
2. 你可能認識的人:把朋友的朋友全部列出來,依據重疊次數來排序,越高的表示你越可能認識的人,如果是你還沒加到名單的,facebook 就會主動推薦給你


在 stackoverflow 找到兩篇參考文章:

2012/05/04

在 multi-thread 下使用 OpenSSL

OpenSSL 支援 multi-thread 嗎?從官網的FAQ中得知:
Yes (with limitations: an SSL connection may not concurrently be used by multiple threads). On Windows and many Unix systems, OpenSSL automatically uses the multi-threaded versions of the standard libraries. If your platform is not one of these, consult the INSTALL file.

要支援 multi-thread 必須提供兩個 callback function:CRYPTO_set_locking_callback(), CRYPTO_set_id_callback(),在需要 lock 物件時,OpenSSL 就會去 call。
pthread_mutex_t *ssl_mutex = NULL;

static void ssl_locking_cb (int mode, int type, const char* file, int line)
{
  if (mode & CRYPTO_LOCK)
    pthread_mutex_lock(&ssl_mutex[type]);
  else
    pthread_mutex_unlock(&ssl_mutex[type]);
}

static unsigned long ssl_id_cb (void)
{
  return (unsigned long)pthread_self();
}

int ssl_init (void)
{
  int   i;

  /* The number of lock we need is getting from CRYPTO_num_locks() */
  if ((ssl_mutex = malloc(sizeof(pthread_mutex_t) * CRYPTO_num_locks()))
   == NULL) {
    printf("malloc() failed.\n");
    return -1;
  }

  /* Init. mutex. */
  for (i = 0; i < CRYPTO_num_locks(); i++) {
    pthread_mutex_init(&ssl_mutex[i], NULL);
  }

  /* Set up locking function */
  CRYPTO_set_locking_callback(ssl_locking_cb);
  CRYPTO_set_id_callback(ssl_id_cb);

  /* Init. library ... */
  SSL_library_init();
  ERR_load_crypto_strings();
  SSL_load_error_strings();
  OpenSSL_add_all_algorithms();
}


提升效能?

當初是在寫 server 時想讓 socket accept 後,由不同 thread 來做 SSL handshake 以提升系統的速度,但根據我的測試結果,由於不同 thread 都使用同一個 SSL_CTX 來 handshake,所以彼此間會互相 lock,效能變得跟 single thread一樣。有想過 initial 多組 SSL_CTX,但好像會遇到其他問題,就沒有再深究了。