Article author: Zhang Yan. Mysql-udf-http is a simple set of MySQL user-defined functions (UDF, User-Defined Functions) offering four functions — http_get(), http_post(), http_put() and http_delete() — which let you perform REST-related operations inside a MySQL database over the HTTP protocol. Project URL: http://code.google.com/p/mysql-udf-http/ Chinese documentation: http://blog.zyan.cc/mysql-udf-http/ Usage environment: Linux operating system; supported MySQL versions: 5.1.x and 5.5.x. 5.0.x is untested. Software author: Zhang Yan
1. The REST architectural style: REST (Representational State Transfer) is a lightweight Web Service architectural style. Its implementation and operation are clearly simpler than SOAP and XML-RPC: it can be implemented entirely over the HTTP protocol, and it can also use caching to improve response speed, so it beats the SOAP protocol in performance, efficiency and ease of use. REST was first proposed by Dr. Roy Thomas Fielding in 2000 in his dissertation “Architectural Styles and the Design of Network-based Software Architectures“, click here to download the full Chinese translation PDF. In addition, there is a translated article that gives a simplified explanation of REST. Today, the common implementation of the REST architectural style is based on the HTTP protocol and its four basic methods (such as POST, GET, PUT and DELETE). Some people map the four HTTP methods onto the CRUD principle: for resources, the CRUD principle needs only four behaviors — Create, Read, Update and Delete — to complete all operations and handling.
In Mysql-udf-http, the four functions http_post(), http_get(), http_put() and http_delete() correspond respectively to the HTTP protocol’s POST, GET, PUT and DELETE methods. REST is an architectural style, not a protocol or a standard. The one-to-one correspondence between the HTTP protocol’s four methods “POST, GET, PUT, DELET” and the CRUD principle’s four behaviors “Create, Read, Update, Delete” is merely an architectural design habit, not a specification. Therefore the POST method can also be used to update a resource, and the PUT method can also be used to create one — it depends on how the specific application author defines things. For example, besides supporting the Memcached protocol, Tokyo Tyrant also supports REST-style access, where PUT means create and update, GET means read, and DELETE means delete (click here for Tokyo Tyrant installation and usage). Among the popular Web 2.0 application API interfaces at home and abroad, many support the REST architectural style. For example: Sina Weibo Open Platform, Renren API, Google OpenID, Flickr, Twitter, eBay, Facebook, Last.fm, del.icio.us, Yahoo Search, Amazon S3, Amazon EC2, Digg, Microsoft Bing, FriendFeed, PayPal, Foursquare, and more… When the number of records reaches hundreds of thousands or millions, people usually shard MySQL tables to reduce database pressure. But features such as displaying all data sorted by clicks, featured status or points cannot be implemented in sharded MySQL tables. The original purpose of writing Mysql-udf-http was to automatically synchronize the data of each MySQL shard into our TCSQL high-speed list database during project development, so it could be used for list queries and display, while content pages query the content of each MySQL shard directly by ID. Because the HTTP protocol is so general-purpose, much more can be done with Mysql-udf-http. With Mysql-udf-http, you can use triggers inside MySQL to synchronize MySQL data to REST-enabled applications. For example, if you have a standalone blog, you can create a MySQL trigger on the posts table so that when an article is published, its title and URL are automatically synchronized to Sina Weibo and Twitter. If you want to use Tokyo Tyrant as a cache, you can likewise use MySQL triggers to automatically synchronize data to Tokyo Tyrant on insert, delete and update. The detailed configuration method is covered in section 4 of this article.
2. Installing and using Mysql-udf-http: 1. Installing Mysql-udf-http on a Linux system Note: “/usr/local/webserver/mysql/“ is your MySQL installation path; if your MySQL installation path is different, modify it yourself.
ulimit -SHn 65535
wget http://curl.haxx.se/download/curl-7.21.1.tar.gz
tar zxvf curl-7.21.1.tar.gz cd curl-7.21.1/
./configure --prefix=/usr
make && make install
cd ../
echo "/usr/local/webserver/mysql/lib/mysql/" > /etc/ld.so.conf.d/mysql.conf /sbin/ldconfig
wget http://mysql-udf-http.googlecode.com/files/mysql-udf-http-1.0.tar.gz
tar zxvf mysql-udf-http-1.0.tar.gz
cd mysql-udf-http-1.0/
./configure --prefix=/usr/local/webserver/mysql --with-mysql=/usr/local/webserver/mysql/bin/mysql_config
make && make install cd ../
2. Logging into MySQL from the command line
/usr/local/webserver/mysql/bin/mysql -S /tmp/mysql.sock
3. Creating MySQL user-defined functions mysql>
create function http_get returns string soname 'mysql-udf-http.so';
create function http_post returns string soname 'mysql-udf-http.so';
create function http_put returns string soname 'mysql-udf-http.so';
create function http_delete returns string soname 'mysql-udf-http.so';
4. Usage I. Function descriptions: mysql>
SELECT http_get('<url>');
SELECT http_post('<url>', '<data>');
SELECT http_put('<url>', '<data>');
SELECT http_delete('<url>');
II. Example A: mysql>
/* Submit the keyword "xoyo" to Baidu Mobile Search via HTTP GET and POST */
SELECT http_get('http://m.baidu.com/s?word=xoyo&pn=0');
SELECT http_post('http://m.baidu.com/s','word=xoyo&pn=0');
/* Sina Weibo Open Platform: get the most recent status of the Sina user with ID 103500 */
SELECT http_get('http://api.t.sina.com.cn/statuses/user_timeline/103500.json?count=1&source=1561596835') AS data;
/* Sina Weibo Open Platform: publish a status */
SELECT http_post('http://your\_sina\_uid:your_password@api.t.sina.com.cn/statuses/update.xml?source=1561596835', 'status=Thins is sina weibo test information');
/* Tokyo Tyrant write, read and delete operations */
SELECT http_put('http://192.168.8.34:1978/key', 'This is value');
SELECT http_get('http://192.168.8.34:1978/key');
SELECT http_delete('http://192.168.8.34:1978/key');
III. Example Using MySQL triggers, mysql-udf-http and the third-party UDF function lib_mysqludf_json to automatically synchronize data to Tokyo Tyrant. (1). Download and install the modified lib_mysqludf_json: The following package is for 32-bit Linux operating systems:
wget http://mysql-udf-http.googlecode.com/files/lib_mysqludf_json-i386.tar.gz
tar zxvf lib_mysqludf_json-i386.tar.gz
cd lib_mysqludf_json-i386/
# If your MySQL installation path is not /usr/local/webserver/mysql/, modify the path below.
cp -f lib_mysqludf_json.so /usr/local/webserver/mysql/lib/mysql/plugin/lib_mysqludf_json.so
cd ../
The following package is for 64-bit Linux operating systems:
wget http://mysql-udf-http.googlecode.com/files/lib_mysqludf_json-x86_64.tar.gz
tar zxvf lib_mysqludf_json-x86_64.tar.gz cd lib_mysqludf_json-x86_64/
# If your MySQL installation path is not /usr/local/webserver/mysql/, modify the path below.
cp -f lib_mysqludf_json.so /usr/local/webserver/mysql/lib/mysql/plugin/lib_mysqludf_json.so
cd ../
# Log into MySQL from the command line:
/usr/local/webserver/mysql/bin/mysql -S /tmp/mysql.sock
mysql>
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';
For detailed usage of lib_mysqludf_json please visit: http://www.mysqludf.org/lib_mysqludf_json/ (2). Create a test table mysql>
SET NAMES UTF8;
USE test;
CREATE TABLE IF NOT EXISTS `mytable` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`addtime` int(10) NOT NULL,
`title` varchar(255) CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
(3). Create triggers for the test table: mysql>
/* Trigger for INSERT operations */
DELIMITER |
DROP TRIGGER IF EXISTS mytable_insert;
CREATE TRIGGER mytable_insert
AFTER INSERT ON mytable
FOR EACH ROW BEGIN
SET @tt_json = (SELECT json_object(id,addtime,title) FROM mytable WHERE id = NEW.id LIMIT 1);
SET @tt_resu = (SELECT http_put(CONCAT('http://192.168.8.34:1978/', NEW.id), @tt_json));
END |
DELIMITER ;
/* Trigger for UPDATE operations */
DELIMITER |
DROP TRIGGER IF EXISTS mytable_update;
CREATE TRIGGER mytable_update
AFTER UPDATE ON mytable
FOR EACH ROW BEGIN
SET @tt_json = (SELECT json_object(id,addtime,title) FROM mytable WHERE id = OLD.id LIMIT 1);
SET @tt_resu = (SELECT http_put(CONCAT('http://192.168.8.34:1978/', OLD.id), @tt_json));
END |
DELIMITER ;
/* Trigger for DELETE operations */
DELIMITER |
DROP TRIGGER IF EXISTS mytable_delete;
CREATE TRIGGER mytable_delete
AFTER DELETE ON mytable
FOR EACH ROW BEGIN
SET @tt_resu = (SELECT http_delete(CONCAT('http://192.168.8.34:1978/', OLD.id)));
END |
DELIMITER ;
(4). Query by joining the MySQL table with Tokyo Tyrant: mysql>
SELECT id,addtime,title,http_get(CONCAT('http://192.168.8.34:1978/',id)) AS tt FROM mytable ORDER BY id DESC LIMIT 0,5;
5. How to drop the mysql-udf-http UDF functions: mysql>
drop function http_get;
drop function http_post;
drop function http_put;
drop function http_delete;
Original link: http://blog.zyan.cc/mysql-udf-http/]function http_delete;
