ngx_rds_json: help ngx_drizzle and other DBD modules to emit JSON data

[agentzh]

Hi, all~

I'm happy to announce the first release of our ngx_rds_json module
that can convert Resty DBD Streams (RDS) to JSON.

As some of you might have noticed, the mysql/drizzle DBD driver module
ngx_drizzle generates a specific binary stream in a format known as
RDS that is defined by ourselves. We introduced RDS just because we
don't want to bind to a specific textual format and makes internal
data exchange or conversion unnecessarily hard.

As web app developers, we're certainly more interested in more popular
textual formats like JSON, YAML, CSV, or even HTML. This module does
the job of formating RDS to JSON in a truly streaming fashion.

The project is hosted on GitHub as our other nginx modules:

     http://github.com/agentzh/rds-json-nginx-module/

Release tarballs can be downloaded from here

    http://github.com/agentzh/rds-json-nginx-module/downloads

Just as the ngx_drizzle module, this module is now considered highly
experimental, but it's maturing very rapidly because it's part of our
Taobao.com's company $project. If you have found any bugs, or any
wishlist, please send us mails or create tickets on GitHub.

Here's some typical use cases drawn from ngx_rds_json's test suite (
http://github.com/agentzh/rds-json-nginx-module/blob/master/test/t/sanity.t
):

Use Case 1

    mysql db init

        create table cats (id integer, name text);
        insert into cats (id) values (2);
        insert into cats (id, name) values (3, 'bob');

    nginx.conf

        upstream backend {
            drizzle_server 127.0.0.1:3306 dbname=test
                 password=some_pass user=monty
                 protocol=mysql;
        }
        server {
            ...
            location /mysql {
                drizzle_pass backend;
                drizzle_query 'select * from cats';
                rds_json on;
            }
        }

    request

        GET /mysql

    response

        [{"id":2,"name":null},{"id":3,"name":"bob"}]

Use Case 2

    mysql db init

        (ditto)

    nginx.conf

        upstream backend {
            # ditto
        }
        server {
            ...
            location /mysql {
                if ($arg_name ~ '[^A-Za-z0-9]') {
                    return 400;
                }
                drizzle_pass backend;
                drizzle_query "update cats set name='$arg_name' where
name='$arg_name'";
                rds_json on;
            }
        }

    request

        GET /mysql?name=bob

    response

        {"errcode":0,"errstr":Rows matched: 1  Changed: 0  Warnings: 0"}

Use Case 3

    mysql db init

        create table foo (id serial, flag bit);

    nginx.conf

        upstream backend {
            # ditto
        }
        server {
            ...
            location /mysql {
                if ($arg_bit !~ '^[01]$') {
                    return 400;
                }
                drizzle_pass backend;
                drizzle_query "insert into foo (flag) values ($arg_bit);";
                rds_json on;
            }
        }

    request

        GET /mysql?bit=1

    response

        {"errcode":0,"insert_id":1,"affected_rows":1}

You'll see fancier (working) use cases in the test suite, by combining
ngx_echo module's echo_location or echo_location_async directive.
Parallel SQL queries can be very useful for certain applications.

As a side note, chaoslawful++ is working on ngx_rds_tt2 module which
will allow us to use Perl TT2's template language to specify custom
output formater for RDSs. Here's a quick example that will work very
soon:

    location /myxml {
        drizzle_query 'select * from products';
        drizzle_pass my_mysql;

        echo_before_body '<?xml version="1.0"?>';
        echo_before_body '<pie>';

        rds_tt2_line_template
           '<slice title="[% title | xml %]" color="[% color | xml %]>'
              '[% count %]'
           '</slice>';

        echo_after_body '</pie>';
    }

And you will get streaming output as well, just buffered on the data
line level. (in contrast, ngx_json_rds does not buffer data for large
fields like BLOBs).

And there will be ngx_srcache that can allow you to buffer database
output by ngx_memc + memcached as well ;)

Stay tuned!
-agentzh