database http interface

Only support Get
http://locahost:3100/postgis/sql/db/select....encoded sql/


Get, Put, Delete
http://locahost:3100/postgis/crud/db/table/key


document store, key/value pair
http://locahost:3100/mysql/doc/db/table/key

====================================

The HTTP plugin implements multiple HTTP interfaces, for example:
• plain SQL access including meta data
• a CRUD (Create-Read-Update-Delete) interface to relational tables
• an interface for storing JSON documents in relational tables

Some of the interfaces follow Representational State Transfer (REST) ideas, some don’t.

----------------------------------------------------

There are three APIs: plain SQL over HTTP, CRUD and DOCUMENT. All three return JSON.

shell> curl ... --url "http://127.0.0.1:8080/sql/db/SELECT+1"
[
{
"meta":[
  {"type":8,"catalog":"def",
  "database":"","table":"",
  "org_table":"","column":"1","org_column":"",
  "charset":63,"length":1,
  "flags":129,"decimals":0}
],
"data":[ 
        ["1"]
],
"status":[{"server_status":2,"warning_count":0}]
}
]

The CRUD endpoint a single row from a MySQL table identified by its primary key value. The row is mapped to JSON in the most simple way. No meta data is included: a very lightweight reply.

shell> curl ...  --url "http://127.0.0.1:8080/crud/db/simple/1"
{"id":"1","col_a":"Ahoy"}

The SQL endpoint supports HTTP GET requests only. CRUD and DOCUMENT endpoints accept GET, PUT and DELETE requests. 

The DOCUMENT endpoint takes any valid JSON and stores it. The access pattern is key-document:

shell> # curl -i -X PUT --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/doc/db/another_table"
HTTP/1.1 201 Created
...
{"info": "Table created"}
shell> # curl -i -X PUT -d '{"words": ["Hello", "world"]}' --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/doc/db/another_table/key"
HTTP/1.1 200 OK
...
{"info": "Document added"}
shell> # curl -X DELETE -i --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/doc/db/another_table/"
HTTP/1.1 200 OK
...
{"info": "Table dropped"}

------------------------------------

Select all of the names in the table:

$ curl --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/SELECT+name_first,+name_last+FROM+names"
[
{
"meta":[
 {"type":253,"catalog":"def","database":"myhttp","table":"names","org_table":"names","column":"name_first","org_column":"name_first","charset":33,"length":120,"flags":0,"decimals":0},
 {"type":253,"catalog":"def","database":"myhttp","table":"names","org_table":"names","column":"name_last","org_column":"name_last","charset":33,"length":120,"flags":0,"decimals":0}
],
"data":[ 
 ["Clark","Kent"],
 ["Bruce","Wayne"],
 ["Hal","Jordan"],
 ["Barry","Allen"],
 ["Diana","Prince"],
 ["Arthur","Curry"],
 ["Oliver","Queen"],
 ["Ray","Palmer"],
 ["Carter","Hall"]
],
"status":[{"server_status":34,"warning_count":0}]
}
]

Selecting a single name:

$ curl --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/SELECT+name_first,+name_last+FROM+names+where+name_first+=+'Clark'"
[
{
"meta":[
 {"type":253,"catalog":"def","database":"myhttp","table":"names","org_table":"names","column":"name_first","org_column":"name_first","charset":33,"length":120,"flags":0,"decimals":0},
 {"type":253,"catalog":"def","database":"myhttp","table":"names","org_table":"names","column":"name_last","org_column":"name_last","charset":33,"length":120,"flags":0,"decimals":0}
],
"data":[ 
 ["Clark","Kent"]
],
"status":[{"server_status":34,"warning_count":0}]
}
]

Deleting a row:

$ curl --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/delete+from+names+where+name_first+=+'Hal'"
{"server_status":34,"warning_count":0,"affected_rows":1,"last_insert_id":0}

Inserting a row:

$ curl --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/INSERT+INTO+names+(name_first,+name_last)+VALUES+('Hal','Jordan');"
{"server_status":2,"warning_count":0,"affected_rows":1,"last_insert_id":1018}