$ curl http:\/\/localhost:8765\/0.1\/version\r\n{\r\n \"version\" : \"2011.04.15.2285\"\r\n}<\/pre>\nBut that wasn’t nearly enough… I also wanted to be able to issue arbitrary queries. Thanks to the supporting code we have in the Drizzle server for EXECUTE() (also used by the replication slave), this was also pretty easy. I created a way to execute the content of a HTTP POST request as if you had done so with EXECUTE() – all nicely wrapped in a transaction.<\/p>\n
I created a simple table using the drizzle client, connecting over a normal TCP socket speaking the MySQL protocol and inserted a row in it:<\/p>\n
$ ..\/client\/drizzle --port 9306 test\r\nWelcome to the Drizzle client.. Commands end with ; or \\g.\r\nYour Drizzle connection id is 4\r\nConnection protocol: mysql\r\nServer version: 2011.04.15.2285 Source distribution (json-interface)\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the buffer.\r\n\r\ndrizzle> show create table t1\\G\r\n*************************** 1. row ***************************\r\n Table: t1\r\nCreate Table: CREATE TABLE `t1` (\r\n `a` INT NOT NULL AUTO_INCREMENT,\r\n `b` VARCHAR(100) COLLATE utf8_general_ci DEFAULT NULL,\r\n PRIMARY KEY (`a`)\r\n) ENGINE=InnoDB COLLATE = utf8_general_ci\r\n1 row in set (0.001209 sec)\r\n\r\ndrizzle> insert into t1 (b) values (\"from mysql protocol\");\r\nQuery OK, 1 row affected (0.00207 sec)<\/pre>\nNow to select rows from it via HTTP and get a JSON object back with the result set:<\/p>\n
$ curl http:\/\/localhost:8765\/0.1\/sql --data 'select * from t1;'\r\n{\r\n \"query\" : \"select * from t1;\",\r\n \"result_set\" : [\r\n [ \"1\", \"from mysql protocol\" ],\r\n [ \"\", \"\" ]\r\n ],\r\n \"sqlstate\" : \"00000\"\r\n}\r\n<\/pre>\nI can also insert more rows using the HTTP interface and then select them from the MySQL protocol interface:<\/p>\n
$ curl http:\/\/localhost:8765\/0.1\/sql --data 'insert into t1 values (NULL, \\\"from HTTP!\\\");'\r\n{\r\n \"query\" : \"insert into t1 values (NULL, \\\\\\\"from HTTP!\\\\\\\");\",\r\n \"sqlstate\" : \"00000\"\r\n}\r\n\r\ndrizzle> select * from t1;\r\n+---+---------------------+\r\n| a | b |\r\n+---+---------------------+\r\n| 1 | from mysql protocol | \r\n| 2 | from HTTP! | \r\n+---+---------------------+\r\n2 rows in set (0.000907 sec)<\/pre>\nSo what does this get us? With the addition of proper authentication, you could start doing some really quite neat and nifty things. I imagine we could add interfaces to avoid SQL and directly do key lookups, table scans and index range scans, giving really quite sweet performance. We could start building web tools to manage and manipulate the database speaking the native language of the web.<\/p>\n
But… there’s more!<\/p>\n
Since we have a web server and a way to execute queries via HTTP along with getting the result set as JSON, why can’t we have a simple Web UI for monitoring the database server and running queries built into the database server<\/strong>?<\/p>\nYes we can.<\/p>\n
![\"\"](\"https:\/\/i0.wp.com\/www.flamingspork.com\/blog\/wp-content\/uploads\/2011\/04\/Screenshot-11-300x230.png?resize=300%2C230\" "\\"Screenshot-11\\"")
<\/a><\/p>\nIf you wanted a WHERE condition or anything else, easy. Change the query, hit execute:<\/p>\n
![\"\"](\"https:\/\/i0.wp.com\/www.flamingspork.com\/blog\/wp-content\/uploads\/2011\/04\/Screenshot-12.png?resize=460%2C318\" "\\"Screenshot-12\\"")
<\/a>No TCP connection or parsing the MySQL protocol or anything. Just HTTP requests straight to the database server from the browser with a bit of client side javascript producing the HTML for the table.<\/p>\nProof of concept code is up on launchpad in\u00c2\u00a0lp:~stewart\/drizzle\/json-interface<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"So… I had another one of those “hrrm… this shouldn’t be hard to hack a proof-of-concept” moments. Web apps are increasingly speaking JSON all around the place. Why can’t we speak JSON to\/from the database? Why? Seriously, why not? One … Continue reading