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

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

在 OS X 安裝 GraphicsMagick 和 Pgmagick

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


這次想在 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" ... 沒用。

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

讓 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 的限制！


 參考資料

• Chris Moyer: Paging SDB results in boto
• AWS Developer Forums: There is LIMIT, but is there LIMIT 40, ...

Redis 初試

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

這三篇推薦看一下

• 为什么使用Redis及其产品定位_Web开发_酷勤网
• Redis内存使用优化与存储_Web开发_酷勤网
• Redis复制与可扩展集群搭建_数据库技术_酷勤网 

這三份文件也可以看

• Redis介绍
• 新浪微博开放平台Redis实践
• Facebook游戏开发商wooga的架构:基于MySQL和Redis

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

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

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


在 stackoverflow 找到兩篇參考文章：

• database design - how facebook calculates mutual friends? - Stack Overflow
• database - Wondering how Facebook does the "Mutual friends" feature - Stack Overflow 

在 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，但好像會遇到其他問題，就沒有再深究了。