[文章作者:张宴 本文版本:v1.1 最后修改:2010.09.03 转载请注明原文链接:http://blog.zyan.cc/tcsql/]

  曾经有人提出,一般数据库缓存分为四种。第一种:单个对象的缓存(一个对象就是数据库一条记录),对于单个对象的临时缓存或永久缓存,用HashMap就可以了,Key-Value方式的Memcached、Memcachedb、Tokyo Tyrant都可以,或者直接对查询数据库的网页采用Squid做缓存,没什么太难的;第二种:列表缓存,就像论坛里帖子的列表;第三种:记录条数的缓存,比如一个论坛板块里有多少个帖子,这样才方便实现分页。第四种:复杂一点的group,sum,count查询,比如一个论坛里按点击数排名的最HOT的帖子列表。第一种比较好实现,后面三种比较困难,虽然可以通过各种方法来解决,但截至目前,似乎还没有使用即简单、并发处理能力又强、实时性又高的解决办法。



  TCSQL为列表页的实时缓存而生,是金山逍遥网技术支持部平台组以Tokyo Cabinet DBM为底层存储与索引,结合类似Memcached的Key-Value内存对象缓存,借鉴SQL语句的SELECT、INSERT、UPDATE、DELETE思想与功能开发的实时列表缓存数据库,能够较好地解决上述前三种类别,特别是第二种、第三种类别的高并发读写问题。

  TCSQL采用HTTP GET/POST协议+JSON数据交换格式在客户端、服务器端之间进行数据交互,支持HTTP协议的任何客户端或语言(例如JavaScript、PHP、JSP、Perl、Python等),都能够连接TCSQL服务器进行操作。这就意味着,一些查询量非常大的应用,甚至可以直接使用运行在用户浏览器端的JavaScript代码访问TCSQL数据库,当然,为了安全起见,你可以在中间用Nginx配以rewrite规则,对TCSQL做个反向代理,限制一下查询权限。

  利用开源的MySQL UDF自定义函数扩展lib_mysqludf_urlencode、lib_mysqludf_urlencode,以及我们平台组周洋同学编写的lib_mysqludf_http_post扩展,配以MySQL触发器,我们可以在MySQL的某张表发生插入、更新、删除操作时,自动将数据同步到TCSQL数据库,使得TCSQL可以当MySQL从库一样使用。

  TCSQL实时列表缓存数据库单机能够支撑1万以上的并发连接,QPS(每秒查询率)能够达到5000~15000次。

点击在新窗口中浏览此图片

  以下是TCSQL在10000并发连接情况下的查询速度(服务器为浪潮NF190服务器,两颗双核Xeon(TM) CPU 2.80GHz、4GB内存、1万转SCSI硬盘。):

  1、第一种类型A:主键查询并取出倒序第1条记录(“=”运算):12155次请求/秒

  查询内容:http://192.168.8.34:3888/?command=select&type=*&where=pkey:NUMEQ:随机数值&order_by=pkey&order_sort=NUMDESC&limit_skip=0&limit_max=1

  测试结果:
引用
Benchmarking: 10000 clients, running 60 sec.

Speed=729324 pages/min, 8031913 bytes/sec.
Requests: 60777 susceed, 0 failed.


  2、第一种类型B:其他索引键查询并取出倒序第1条记录(“=”运算):11897次请求/秒

  查询内容:http://192.168.8.34:3888/?command=select&type=*&where=uid:NUMEQ:随机数值&order_by=pkey&order_sort=NUMDESC&limit_skip=0&limit_max=1

  测试结果:
引用
Benchmarking: 10000 clients, running 60 sec.

Speed=713856 pages/min, 7865884 bytes/sec.
Requests: 59488 susceed, 0 failed.


  3、第二种类型:根据复合条件查询并取出倒序前10条记录:8778次请求/秒(相当于SELECT * FROM table WHERE dateline >= 随机时间戳 AND idtype = '变换的文本' ORDER BY pkey DESC LIMIT 0,10)

  查询内容:http://192.168.8.34:3888/?command=select&type=*&where=dateline:NUMGE:随机时间戳|idtype:STREQ:变换的文本&order_by=pkey&order_sort=NUMDESC&limit_skip=0&limit_max=10

  测试结果:
引用
Benchmarking: 10000 clients, running 60 sec.

Speed=526680 pages/min, 8971878 bytes/sec.
Requests: 43890 susceed, 0 failed.


  4、第三种类型:统计符合查询条件的记录数量:9160次请求/秒(相当于SELECT count(*) FROM table WHERE dateline >= 随机时间戳 AND idtype = '变换的文本')

  查询内容:http://192.168.8.34:3888/?command=select&type=count&where=dateline:NUMGE:随机时间戳|idtype:STREQ:变换的文本

  测试结果:
引用
Benchmarking: 10000 clients, running 5 sec.

Speed=549648 pages/min, 714542 bytes/sec.
Requests: 45804 susceed, 0 failed.





  一、TCSQL数据库编译安装
  1、编译安装TCSQL需要的扩展库
wget http://www.monkey.org/~provos/libevent-1.4.12-stable.tar.gz
tar zxvf libevent-1.4.12-stable.tar.gz
cd libevent-1.4.12-stable/
./configure --prefix=/usr
make && make install
cd ../

wget http://oss.metaparadigm.com/json-c/json-c-0.9.tar.gz
tar zxvf json-c-0.9.tar.gz
cd json-c-0.9/
./configure --prefix=/usr/local/json-c-0.9
make && make install
cd ../

wget http://www.1978th.net/tokyocabinet/tokyocabinet-1.4.33.tar.gz
tar zxvf tokyocabinet-1.4.33.tar.gz
cd tokyocabinet-1.4.33/
./configure --prefix=/usr/local/tokyocabinet-1.4.33
make && make install
cd ../

echo "/usr/local/json-c-0.9/lib/" > /etc/ld.so.conf.d/json-c-0.9.conf
echo "/usr/local/tokyocabinet-1.4.33/lib/" > /etc/ld.so.conf.d/tokyocabinet-1.4.33.conf

/sbin/ldconfig


  2、编译TCSQL数据库
  注:二进制程序及源码目前只对金山公司内部开放。
gcc -o tcsql tcsql.c -levent -ljson -I/usr/local/json-c-0.9/include/json/ -L/usr/local/json-c-0.9/lib/ -ltokyocabinet -lz -lbz2 -lrt -lpthread -lm -lc -I/usr/local/tokyocabinet-1.4.33/include/ -L/usr/local/tokyocabinet-1.4.33/lib/




  二、TCSQL数据库的启动

  点击在新窗口中浏览此图片

  1、以自定义方式,作为守护进程启动:
ulimit -SHn 65535
./tcsql -l 192.168.8.34 -p 3888 -x /data0/tcsql/data -t 3 -d


  2、以默认值,作为守护进程启动:
ulimit -SHn 65535
./tcsql -d


  注:请确保文件描述符数量够用(系统默认值为1024),以便TCSQL承担上万连接数的并发访问。



  三、TCSQL访问协议
  TCSQL采用HTTP GET/POST协议进行查询、插入、更新、删除、设置索引等操作。TCSQL为单表数据库,一个端口的数据库只有一张表。TCSQL表只有两种数据类型,字符型和数值型,其中主键字段的名称固定不可修改,为pkey,数值型索引。对应日期、时间等类型,请使用数值型的UNIX时间戳存放。

  TCSQL无需事先建表,插入第一条记录后,第一条记录拥有的字段即为该表的字段格式。各字段统一按照文本信息存放数据,无需指定数据类型。如果为字段建立数值型索引,Where条件的数值型运算符可以用到索引;如果为字段建立文本型索引,Where条件的文本型运算符可以用到索引。

  1、命令类别:
  查询:http://127.0.0.1:3888/?command=select
  插入:http://127.0.0.1:3888/?command=insert
  更新:http://127.0.0.1:3888/?command=update
  删除:http://127.0.0.1:3888/?command=delete
  设置索引:http://127.0.0.1:3888/?command=setindex



  2、SELECT查询命令:
  ①、完整参数的查询命令示例:
  A、以Linux下的curl命令访问:
curl "http://127.0.0.1:3888/?command=select&type=*&where=pkey:NUMGE:0|title:STRINC:keyword&order_by=pkey&order_sort=NUMDESC&limit_skip=0&limit_max=10"


  B、用PHP访问:
<?php
$result = json_decode(file_get_contents('http://127.0.0.1:3888/?command=select&type=*&where=pkey:NUMGE:0|title:STRINC:keyword&order_by=pkey&order_sort=NUMDESC&limit_skip=0&limit_max=10'), true));
print_r($result);
?>


  ②、相当于MySQL的SQL语句:
SELECT * FROM table WHERE pkey >= 0 AND title LIKE '%keyword%' ORDER BY pkey DESC LIMIT 0,10


  ③、必填参数说明:
引用
command=select
(命令类别为:select)


引用
&type=*
(查询类型为:* 或 count,相当于MySQL的select * 和 select count(*)语句)


  ④、可选参数说明:
引用
&where=pkey:NUMGE:0|title:STRINC:keyword
(where查询条件,格式为“字段名1:运算符:查询值|字段名2:运算符:查询值|...”,多个条件之间以“|”分割)

Where条件运算符:
数值型运算符:

NUMGT:表示比右边的数值要大。
NUMGE:表大于或等于右边的数值。
NUMLT:表示比右边的数值要小。
NUMLE:表示小于或等于右边的数值。
NUMBT:表示其大小处于右边文字段中被逗号分开的两个数值的中间。
NUMOREQ:表示同右边文字段中被逗号分开的两个数值中的其中一个是相同的。

文本型运算符:
STREQ:表示与右边的文字行列完全相同。
STRINC:表示含有右边文字行列的内容。
STRBW:表从右边的文字行列开始。
STREW:表示到右边的文字行列结束。
STRAND:表示包含右边的文字行列中右逗号分开部分的字段的全部。
STROR:表示包含右边文字段中逗号分开部分的其中一部分。
STROREQ:表示与右边文字段中逗号分开部分的其中某部分完全相同。


引用
&order_by=pkey&order_sort=NUMDESC
(排序字段与排序方式,order_by=pkey表示按照字段pkey排序,order_sort=NUMDESC表示排序方式为数值型倒序排序。)

可选的排序类型如下:
STRASC:表示按照文本型字段内的文本内容在字典中排列顺序的升序。
STRDESC:表示按照文本型字段内的文本内容在字典中排列顺序的降序。
STRASC:表示按照数值大小的升序。
STRDESC:表示按照数值大小的降序。


引用
&limit_skip=0&limit_max=10
(显示的偏移量和记录数,相当于SQL语句中的limit 0,10)


  ⑤、查询返回值(JSON输出):
  无数据时:
引用
[]


  有数据时:
引用
[{ "pkey": "3847", "appid": "3", "icon": "friend", "uid": "188", "username": "独孤拔剑", "dateline": "1253858842", "friend": "0", "hash_template": "cb26e9a5a87778b51eb9b36c38f2616b", "hash_data": "ca4a76f8444363e8215e19f8468be415", "title_template": "{actor} 和 {touser} 成为了好友", "title_data": "a:1:{s:6:\"touser\";s:44:\"<a href=\"space.php?uid=395\">江湖粉丝<\/a>\";}", "body_template": "", "body_data": "a:0:{}", "body_general": "", "image_1": "", "image_1_link": "", "image_2": "", "image_2_link": "", "image_3": "", "image_3_link": "", "image_4": "", "image_4_link": "", "target_ids": "", "id": "0", "idtype": "", "hot": "0" },{ "pkey": "3846", "appid": "3", "icon": "profile", "uid": "591", "username": "七~夜~魔~君", "dateline": "1253858783", "friend": "0", "hash_template": "3a7101a64ea7927f0b3f5179b7a457b3", "hash_data": "ec7d775d9211880bca2ba1d401e3bcb9", "title_template": "{actor} 开通了自己的个人主页", "title_data": "a:0:{}", "body_template": "", "body_data": "a:0:{}", "body_general": "", "image_1": "", "image_1_link": "", "image_2": "", "image_2_link": "", "image_3": "", "image_3_link": "", "image_4": "", "image_4_link": "", "target_ids": "", "id": "0", "idtype": "", "hot": "0" },{ "pkey": "3845", "appid": "3", "icon": "comment", "uid": "247", "username": "freespy", "dateline": "1253858601", "friend": "0", "hash_template": "2e512d5ffebbfb6b596df942871e413a", "hash_data": "eae2c0aebf53cebbe3e39a9da1072ddf", "title_template": "{actor} 评论了 {touser} 的图片", "title_data": "a:1:{s:6:\"touser\";s:44:\"<a href=\"space.php?uid=404\">小营居士<\/a>\";}", "body_template": "{pic_title}", "body_data": "a:1:{s:9:\"pic_title\";s:51:\"20090901_024374553bbea77f21199Vx54Z93Nzjd.jpg.thumb\";}", "body_general": "", "image_1": "http:\/\/pic.xoyo.com\/hu\/attachment\/200909\/24\/404_1253763392qQB4.jpg.thumb.jpg", "image_1_link": "space.php?uid=404&do=album&picid=364", "image_2": "", "image_2_link": "", "image_3": "", "image_3_link": "", "image_4": "", "image_4_link": "", "target_ids": "", "id": "0", "idtype": "", "hot": "0" },{ "pkey": "3844", "appid": "3", "icon": "album", "uid": "404", "username": "小营居士", "dateline": "1253763392", "friend": "0", "hash_template": "405cd24a70f8c85bf9f2affd3aadf790", "hash_data": "6c2921a232ef155f1397c93f68ee6e40", "title_template": "{actor} 上传了新图片", "title_data": "N;", "body_template": "{title}", "body_data": "a:1:{s:5:\"title\";s:51:\"20090901_024374553bbea77f21199Vx54Z93Nzjd.jpg.thumb\";}", "body_general": "", "image_1": "http:\/\/pic.xoyo.com\/hu\/attachment\/200909\/24\/404_1253763392qQB4.jpg.thumb.jpg", "image_1_link": "space.php?uid=404&do=album&picid=364", "image_2": "", "image_2_link": "", "image_3": "", "image_3_link": "", "image_4": "", "image_4_link": "", "target_ids": "", "id": "364", "idtype": "picid", "hot": "1" }]


  以上的JSON信息,通过PHP的print_r(json_decode($data, true))函数解码后得到的数组为:
引用
Array
(
    [0] => Array
        (
            [pkey] => 3847
            [appid] => 3
            [icon] => friend
            [uid] => 188
            [username] => 独孤拔剑
            [dateline] => 1253858842
            [friend] => 0
            [hash_template] => cb26e9a5a87778b51eb9b36c38f2616b
            [hash_data] => ca4a76f8444363e8215e19f8468be415
            [title_template] => {actor} 和 {touser} 成为了好友
            [title_data] => a:1:{s:6:"touser";s:44:"江湖粉丝";}
            [body_template] =>
            [body_data] => a:0:{}
            [body_general] =>
            [image_1] =>
            [image_1_link] =>
            [image_2] =>
            [image_2_link] =>
            [image_3] =>
            [image_3_link] =>
            [image_4] =>
            [image_4_link] =>
            [target_ids] =>
            [id] => 0
            [idtype] =>
            [hot] => 0
        )

    [1] => Array
        (
            [pkey] => 3846
            [appid] => 3
            [icon] => profile
            [uid] => 591
            [username] => 七~夜~魔~君
            [dateline] => 1253858783
            [friend] => 0
            [hash_template] => 3a7101a64ea7927f0b3f5179b7a457b3
            [hash_data] => ec7d775d9211880bca2ba1d401e3bcb9
            [title_template] => {actor} 开通了自己的个人主页
            [title_data] => a:0:{}
            [body_template] =>
            [body_data] => a:0:{}
            [body_general] =>
            [image_1] =>
            [image_1_link] =>
            [image_2] =>
            [image_2_link] =>
            [image_3] =>
            [image_3_link] =>
            [image_4] =>
            [image_4_link] =>
            [target_ids] =>
            [id] => 0
            [idtype] =>
            [hot] => 0
        )

    [2] => Array
        (
            [pkey] => 3845
            [appid] => 3
            [icon] => comment
            [uid] => 247
            [username] => freespy
            [dateline] => 1253858601
            [friend] => 0
            [hash_template] => 2e512d5ffebbfb6b596df942871e413a
            [hash_data] => eae2c0aebf53cebbe3e39a9da1072ddf
            [title_template] => {actor} 评论了 {touser} 的图片
            [title_data] => a:1:{s:6:"touser";s:44:"小营居士";}
            [body_template] => {pic_title}
            [body_data] => a:1:{s:9:"pic_title";s:51:"20090901_024374553bbea77f21199Vx54Z93Nzjd.jpg.thumb";}
            [body_general] =>
            [image_1] => http://pic.xoyo.com/hu/attachment/200909/24/404_1253763392qQB4.jpg.thumb.jpg
            [image_1_link] => space.php?uid=404&do=album&picid=364
            [image_2] =>
            [image_2_link] =>
            [image_3] =>
            [image_3_link] =>
            [image_4] =>
            [image_4_link] =>
            [target_ids] =>
            [id] => 0
            [idtype] =>
            [hot] => 0
        )

    [3] => Array
        (
            [pkey] => 3844
            [appid] => 3
            [icon] => album
            [uid] => 404
            [username] => 小营居士
            [dateline] => 1253763392
            [friend] => 0
            [hash_template] => 405cd24a70f8c85bf9f2affd3aadf790
            [hash_data] => 6c2921a232ef155f1397c93f68ee6e40
            [title_template] => {actor} 上传了新图片
            [title_data] => N;
            [body_template] => {title}
            [body_data] => a:1:{s:5:"title";s:51:"20090901_024374553bbea77f21199Vx54Z93Nzjd.jpg.thumb";}
            [body_general] =>
            [image_1] => http://pic.xoyo.com/hu/attachment/200909/24/404_1253763392qQB4.jpg.thumb.jpg
            [image_1_link] => space.php?uid=404&do=album&picid=364
            [image_2] =>
            [image_2_link] =>
            [image_3] =>
            [image_3_link] =>
            [image_4] =>
            [image_4_link] =>
            [target_ids] =>
            [id] => 364
            [idtype] => picid
            [hot] => 1
        )
)




  3、INSERT插入命令
  ①、完整参数的插入命令示例(以Linux下的curl命令访问):
curl -d "urlencode编码后的JSON数据" "http://127.0.0.1:3888/?command=insert"


  一个PHP的一维数组经过json_encode编码后,形成以下JSON数据:
引用
{"pkey":1,"appid":3,"icon":"profile","uid":2,"username":"wangduo","dateline":1251768576,"friend":0,"hash_template":"3a7101a64ea7927f0b3f5179b7a457b3","hash_data":"ec7d775d9211880bca2ba1d401e3bcb9","title_template":"{actor} 开通了自己的个人主页","title_data":"a:0:{}","body_template":"","body_data":"a:0:{}","body_general":"","image_1":"","image_1_link":"","image_2":"","image_2_link":"","image_3":"","image_3_link":"","image_4":"","image_4_link":"","target_ids":"","id":0,"idtype":"","hot":0}


  将以上的JSON数据urlencode后,通过HTTP POST发送到TCSQL(在PHP中不urlencode也行):
引用
%7B%22pkey%22%3A1%2C%22appid%22%3A3%2C%22icon%22%3A%22profile%22%2C%22uid%22%3A2%2C%22username%22%3A%22wangduo%22%2C%22dateline%22%3A1251768576%2C%22friend%22%3A0%2C%22hash_template%22%3A%223a7101a64ea7927f0b3f5179b7a457b3%22%2C%22hash_data%22%3A%22ec7d775d9211880bca2ba1d401e3bcb9%22%2C%22title_template%22%3A%22%7Bactor%7D%20%E5%BC%80%E9%80%9A%E4%BA%86%E8%87%AA%E5%B7%B1%E7%9A%84%E4%B8%AA%E4%BA%BA%E4%B8%BB%E9%A1%B5%22%2C%22title_data%22%3A%22a%3A0%3A%7B%7D%22%2C%22body_template%22%3A%22%22%2C%22body_data%22%3A%22a%3A0%3A%7B%7D%22%2C%22body_general%22%3A%22%22%2C%22image_1%22%3A%22%22%2C%22image_1_link%22%3A%22%22%2C%22image_2%22%3A%22%22%2C%22image_2_link%22%3A%22%22%2C%22image_3%22%3A%22%22%2C%22image_3_link%22%3A%22%22%2C%22image_4%22%3A%22%22%2C%22image_4_link%22%3A%22%22%2C%22target_ids%22%3A%22%22%2C%22id%22%3A0%2C%22idtype%22%3A%22%22%2C%22hot%22%3A0%7D


  注:INSERT插入命令如果遇到数据库中已经重复的主键,将会用新数据替换旧记录的数据,这一点跟MySQL的REPLACE INTO操作类似。



  4、UPDATE更新命令
  ①、完整参数的更新命令示例(以Linux下的curl命令访问):
curl -d "urlencode编码后的JSON数据" "http://127.0.0.1:3888/?command=update&type=*&where=pkey:NUMGT:195"


  一个PHP的一维数组经过json_encode编码后,形成以下JSON数据:
引用
{"pkey":1,"appid":3,"icon":"profile","uid":2,"username":"wangduo","dateline":1251768576,"friend":0,"hash_template":"3a7101a64ea7927f0b3f5179b7a457b3","hash_data":"ec7d775d9211880bca2ba1d401e3bcb9","title_template":"{actor} 开通了自己的个人主页","title_data":"a:0:{}","body_template":"","body_data":"a:0:{}","body_general":"","image_1":"","image_1_link":"","image_2":"","image_2_link":"","image_3":"","image_3_link":"","image_4":"","image_4_link":"","target_ids":"","id":0,"idtype":"","hot":0}


  将以上的JSON数据urlencode后,通过HTTP POST发送到TCSQL:
引用
%7B%22pkey%22%3A1%2C%22appid%22%3A3%2C%22icon%22%3A%22profile%22%2C%22uid%22%3A2%2C%22username%22%3A%22wangduo%22%2C%22dateline%22%3A1251768576%2C%22friend%22%3A0%2C%22hash_template%22%3A%223a7101a64ea7927f0b3f5179b7a457b3%22%2C%22hash_data%22%3A%22ec7d775d9211880bca2ba1d401e3bcb9%22%2C%22title_template%22%3A%22%7Bactor%7D%20%E5%BC%80%E9%80%9A%E4%BA%86%E8%87%AA%E5%B7%B1%E7%9A%84%E4%B8%AA%E4%BA%BA%E4%B8%BB%E9%A1%B5%22%2C%22title_data%22%3A%22a%3A0%3A%7B%7D%22%2C%22body_template%22%3A%22%22%2C%22body_data%22%3A%22a%3A0%3A%7B%7D%22%2C%22body_general%22%3A%22%22%2C%22image_1%22%3A%22%22%2C%22image_1_link%22%3A%22%22%2C%22image_2%22%3A%22%22%2C%22image_2_link%22%3A%22%22%2C%22image_3%22%3A%22%22%2C%22image_3_link%22%3A%22%22%2C%22image_4%22%3A%22%22%2C%22image_4_link%22%3A%22%22%2C%22target_ids%22%3A%22%22%2C%22id%22%3A0%2C%22idtype%22%3A%22%22%2C%22hot%22%3A0%7D


  注:UPDATE更新命令是SELECT查询命令和INSERT插入命令的结合,URL查询部分使用SELECT查询命令的语法,HTTP POST使用INSERT插入命令的语法。



  5、DELETE删除命令
  ①、完整参数的删除命令示例(以Linux下的curl命令访问):
curl "http://127.0.0.1:3888/?command=delete&type=*&where=pkey:NUMGT:195"




  6、SetIndex索引设置命令
  ①、创建文本型字段索引示例(以Linux下的curl命令访问):
curl "http://127.0.0.1:3888/?command=setindex&type=TDBITLEXICAL&field=需要建文本型索引的字段名"


  ②、创建数值型字段索引示例(以Linux下的curl命令访问):
curl "http://127.0.0.1:3888/?command=setindex&type=TDBITDECIMAL&field=需要建数值型索引的字段名"


  ③、优化索引示例(以Linux下的curl命令访问):
curl "http://127.0.0.1:3888/?command=setindex&type=TDBITOPT&field=需要优化索引的字段名"


引用
“type=”可填的参数为:
TDBITLEXICAL:创建文本型索引
TDBITDECIMAL:创建数值型索引
TDBITTOKEN:创建标记倒排索引
TDBITQGRAM:创建q-gram倒排索引
TDBITOPT:优化索引
TDBITVOID:删除索引




  四、让TCSQL从MySQL同步数据
  1、编译安装三个MySQL UDF函数扩展(json、urlencode、http_post)
wget http://mirrors.xoyo.com/xoyo/tcsql/mysql_to_tcsql_udf.tar.gz
tar zxvf mysql_to_tcsql_udf.tar.gz
cd mysql_to_tcsql_udf/lib_mysqludf_json/
make
make install
cd ../lib_mysqludf_urlencode/
echo "/usr/local/webserver/mysql/lib/mysql/" > /etc/ld.so.conf.d/mysql.Conf
/sbin/ldconfig
./configure --prefix=/usr/local/webserver/mysql/lib/mysql/plugin/ --with-mysq=/usr/local/webserver/mysql --with-mysql_config=/usr/local/webserver/mysql/bin/mysql_config --with-mysql-include=/usr/local/webserver/mysql/include
make && make install
cp -f /usr/local/webserver/mysql/lib/mysql/plugin/lib/* /usr/local/webserver/mysql/lib/mysql/plugin/
make
make install

cd ../lib_mysqludf_http_post/
make
make install


  2、通过命令行登入MySQL,执行以下语句创建MySQL自定义函数。
drop function lib_mysqludf_json_info;
drop function json_array;
drop function json_members;
drop function json_object;
drop function json_values;
create function lib_mysqludf_json_info returns string soname 'lib_mysqludf_json.so';
create function json_array returns string soname 'lib_mysqludf_json.so';
create function json_members returns string soname 'lib_mysqludf_json.so';
create function json_object returns string soname 'lib_mysqludf_json.so';
create function json_values returns string soname 'lib_mysqludf_json.so';

drop function http_post;
create function http_post returns string soname 'lib_http_post.so';

drop function urlencode;
create function urlencode returns string soname 'libmysqludf_urlencode.so';


  3、利用触发器自动同步MySQL数据到TCSQL数据库
  示例:通过MySQL命令行连接到MySQL服务器,执行以下SQL,对sns_feed表创建三个MySQL触发器:sns_feed_insert、sns_feed_update、sns_feed_delete,当MySQL的sns_feed表发生增、删、改操作时,自动将修改的记录内容通过HTTP POST到TCSQL数据库(192.168.8.34:3888)。
DELIMITER |
DROP TRIGGER IF EXISTS sns_feed_insert;
CREATE TRIGGER sns_feed_insert
AFTER INSERT ON sns_feed
FOR EACH ROW BEGIN
    SET @tcsql_result_json = (SELECT json_object(feedid as pkey,appid,icon,uid,username,dateline,friend,hash_template,hash_data,title_template,title_data,body_template,body_data,body_general,image_1,image_1_link,image_2,image_2_link,image_3,image_3_link,image_4,image_4_link,target_ids,id,idtype,hot) FROM sns_feed WHERE feedid = NEW.feedid limit 1);
    SET @tcsql_result_eval = (SELECT http_post('192.168.8.34', '3888', 'command=insert', urlencode(@tcsql_result_json)));
END |
DELIMITER ;

DELIMITER |
DROP TRIGGER IF EXISTS sns_feed_update;
CREATE TRIGGER sns_feed_update
AFTER UPDATE ON sns_feed
FOR EACH ROW BEGIN
    SET @tcsql_result_json = (SELECT json_object(feedid as pkey,appid,icon,uid,username,dateline,friend,hash_template,hash_data,title_template,title_data,body_template,body_data,body_general,image_1,image_1_link,image_2,image_2_link,image_3,image_3_link,image_4,image_4_link,target_ids,id,idtype,hot) FROM sns_feed WHERE feedid = OLD.feedid limit 1);
    SET @tcsql_result_eval = (SELECT http_post('192.168.8.34', '3888', 'command=insert', urlencode(@tcsql_result_json)));
END |
DELIMITER ;

DELIMITER |
DROP TRIGGER IF EXISTS sns_feed_delete;
CREATE TRIGGER sns_feed_delete
AFTER DELETE ON sns_feed
FOR EACH ROW BEGIN
    SET @tcsql_result_eval = (SELECT http_post('192.168.8.34', '3888', concat('command=delete&where=pkey:NUMEQ:', OLD.feedid), ''));
END |
DELIMITER ;




  五、TCSQL的PHP客户端Class
下载文件 (已下载 1934 次)


  以下是一段SELECT查询TCSQL,并逐行显示内容的PHP示例程序:
  您也可以参考以下网址,使用JavaScript对TCSQL数据库进行操作:
  http://www.json.org/JSONRequest.html
  http://www.devpro.it/JSON/files/JSONRequest-js.html



  六、TCSQL在实际项目中的应用
  金山游戏官网逍遥网内测中的SNS非实名制垂直类互动游戏社区“逍遥江湖”(相关新闻:http://tech.qq.com/a/20090924/000316.htm),使用TCSQL存放读写非常频繁的、记录用户每一步操作的Feed信息(好友动态、我的动态、热门推荐、全站动态)。

  2009-09-29 补充介绍:
引用
  关于Feed信息,数据写入MySQL主库,同步到N台MySQL从库。在其中的一台无其他查询的备用MySQL从库上,按照第四部分的内容建立了触发器,将数据实时同步到TCSQL。查询部分,直接修改UCH的代码,将原来查询MySQL的SQL语句,改成以下的TCSQL查询语句即可。MySQL单表能实现的大部分功能,TCSQL都能够实现,迁移起来很简单。

  【好友动态】
  TCSQL的查询语句:
  http://192.168.8.34:3888/?command=select&type=*&where=uid:NUMOREQ:0,57,48,60263,43,89163,42,74,91,93,94,105,106,109&order_by=dateline&order_sort=NUMDESC&limit_skip=0&limit_max=100

  等同于原MySQL的SQL查询语句:
  SELECT * FROM sns_feed USE INDEX(dateline) WHERE uid IN ('0',57,48,60263,43,89163,42,74,91,93,94,105,106,109) ORDER BY dateline DESC LIMIT 0,100


  【我的动态】
  TCSQL的查询语句:
  http://192.168.8.34:3888/?command=select&type=*&where=uid:NUMEQ:55&order_by=dateline&order_sort=NUMDESC&limit_skip=0&limit_max=100

  等同于原MySQL的SQL查询语句:
  SELECT * FROM sns_feed WHERE uid='55' ORDER BY dateline DESC LIMIT 0,100


  【热门推荐】
  TCSQL的查询语句:
  http://192.168.8.34:3888/?command=select&type=*&where=hot:NUMGE:3&order_by=dateline&order_sort=NUMDESC&limit_skip=0&limit_max=50

  等同于原MySQL的SQL查询语句:
  SELECT * FROM sns_feed WHERE hot >= '3' ORDER BY dateline DESC LIMIT 0,50


  【全站动态】
  TCSQL的查询语句:
  http://192.168.8.34:3888/?command=select&type=*&where=&order_by=dateline&order_sort=NUMDESC&limit_skip=0&limit_max=100

  等同于原MySQL的SQL查询语句:
  SELECT * FROM sns_feed ORDER BY dateline DESC LIMIT 0,100





技术大类 » 数据库技术 | 评论(107) | 引用(0) | 阅读(112591)
111
2009-11-11 17:51
不知什么时候可以发布?
gettouch
2009-11-12 15:08
直接ttserver+lua不就解决了么,用这么费尽么?
gavin2u Homepage
2009-12-11 09:26
请教,那个数据库原理图是用什么软体画的?

question
chen
2010-1-7 11:17
原来是防火墙不知道什么时候打开了。关了就好了
北京一哥 Email
2010-1-15 16:01
大哥,我是冲着本文中你的那张图片来的,能告诉我用什么画的吗?
我看是png格式,为什么一另存,就变成bmp格式了?
zshtom
2010-1-19 02:19
TC Table Database 的插入效率有做过测试吗 如果没有缓存 貌似插入的速度好像不是很快 特别是做了某些字段的索引以后
luckytiger Email
2010-1-30 20:41
按照 TCSQL 这种方式 , 如果在客户端 用 javascript ajax  通过 url 取得返回 json数据 ,那么这样直接 把 sql 语句 暴露在 浏览器端 ,那么这种方式是否是安全的呢?
lala
2010-4-8 10:24
不知道用起来安不安全unhappy
太阳里的雪 Homepage
2010-4-21 01:23
不知道用H2内存数据实现怎么样?
开源
2010-7-29 09:35
TT是GPL的,基于以上开发产品也应该遵守GPL。
开源开放,让大家一起分享技术的成果。
闭源,就不要拿出臭显了,自以为有多高的技术!
sqlercn Homepage
2010-9-13 11:38
STRASC:表示按照数值大小的升序。
STRDESC:表示按照数值大小的降序。
是不是应该为:
NUMASC:表示按照数值大小的升序。
NUMDESC:表示按照数值大小的降序。
开 源
2010-10-19 14:57
开源出来就好了
totem
2011-6-5 10:26
longhtml Email
2011-8-4 22:24
TCSQL 不开放出来的?  空欢喜一场呢
cnrainbing
2011-9-6 14:47
张老师有没有让TCSQL从ORACLE同步数据的工具啊?
cnrainbing
2011-9-11 19:22
张老师TCSQL 能提供下载吗?希望开源啊
louis vuitton uk Email Homepage
2011-11-23 09:31
This louis vuitton uk for sale belongs to the sounding just what are termed as Louis Vuitton vintage best sellers, many other products and services for the reason that range appearing companies.You will easily notice the unfold zippers of this coach outlet store online. That is the decoration. There are some inside pockets for you as well. They are easy to match your clothes and to carry.Let us inspire your inner beauty with fine christian louboutin sale. Purse the elegance in bridal wedding. Enjoy the fashion.
Louis Vuitton Neverfull Email Homepage
2012-4-21 19:41
www.lvbagsclassic.com are authorized authentic Louis Vuitton handbags outlet store. All the items at our site are 100% authentic. All our Louis Vuitton handbags will come with the authenticity card, serial Number, dust bag and care booklet. We promise you will be 100% satisfied when you get such cheap authentic Louis Vuitton handbags from us!
Louis Vuitton Neverfull
Hogan
2012-5-10 18:01
Ci sono diversi motivi principali per cui si può sicuramente trovare sul proprio volere le soluzioni contabili che coinvolgono tutta Stansted, anche se alcune persone potrebbero uomini e donne, probabilmente si dovrà disporre di soluzioni contabili rispetto con la gente. Stai con me per scoprire di più su molti dei motivi per prendere in considerazione l'impiego contabili in tutta Hogan Stansted. Costruire un businessIf vi capita di essere in funzione, è abbastanza possibile che si può fare uso di soluzioni contabili. Non date per scontato tutte le società hanno abbastanza soldi per utilizzare a tempo pieno il personale fiscali, così utilizzando una conditi insieme con l'agenzia affidabile che coinvolge tutta commercialisti Stansted potrebbe rendere meno difficili problemi.
hogan280 Email
2012-5-10 18:10
Non tanto è stato fatto perché le accuse della ragazza giovane non si pensava in primo luogo. Né come la segnalazione di Hogan questo caso potrebbe portare ad un simile tipo di stereotipo e mito attorno alla violenza sessuale.
分页: 2/6 第一页 上页 1 2 3 4 5 6 下页 最后页
发表评论
表情
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
打开HTML
打开UBB
打开表情
隐藏
记住我
昵称   密码   游客无需密码
网址   电邮   [注册]