Coding With the DBSlayer
The DBSlayer does not ship with any language bindings, since to clients it is more of a generic API than a highly complex middleware layer. Also, we're pretty sure our users could create nicer bindings than what we might ship at first. Notable contributions for your favorite language are welcome and will be included in future DBSlayer releases with your name. So, how do applications interact with the DBSlayer?
How Do I Execute Queries?
Sending queries to a remote DBSlayer instance requires the few basic steps:
- Create an associative array of one or more arguments against the /db? endpoint
- Encode that array to JSON
- Create a URL out of the host, port of the slayer instance followed by /db? and a URL encoded representation of the query
- Use your language's built-in HTTP client functions to send/receive and decode the JSON to parse.
To illustrate all of these together, here is the basic code to execute these steps in PHP using the CURL functions:
<?php function submitQuery($sql) { $request = array( "SQL" => $sql, "STATS" => ($show_stats ? true : false), "HOST" => ($get_host_info ? true : false) ); $query = json_encode($request); $url = $this->base_url . '?' . rawurlencode($query); $this->debug("using url '$url'"); if (!curl_setopt($this->curl_handle, CURLOPT_URL, $url)) { throw new Exception("cannot set cURL CURLOPT_URL to '$url':". curl_error() . "(" . curl_errno() . ")"); } $request_results = curl_exec($this->curl_handle); if (!$request_results) { throw new Exception("curl_exec() failed for url '$url':". curl_error() . "(" . curl_errno() . ")"); } return array( "results" => $request_results, "connection-stats" => $this->getConnectionStats($this->curl_handle) ); } ?>
Just for kicks, here is the code in Ruby
require 'open-uri' def query_url(sql) query_hash = { "SQL" => sql } url_args = URI.encode(query_hash.to_json) "http://#{@slayer_server}:#{@slayer_port}/db?#{url_args}" end def exec_query(sql) url = query_url(sql) open(url) do |f| yield JSON.parse(f.read) end end
As long as your language has a basic HTTP library as well as JSON encoding/decoding functions, you can communicate with the DBSlayer.
How Do I Execute Multiple Queries Simultaneously?
Since the DBSlayer is built on top of a web server, you can do parallel SQL queries with whatever mechanism your programming language might provide to do parallel web requests. In some cases, this might entail multithreading, but you should also be aware that CURL provides functionality for single-threaded applications to do multiple simultaneous requests via select(). For example, this is some code in PHP to do that
<?php function submitMulti(array $query_array) { $curl_request = array(); $curl_results = array(); $curl_multi_handle = curl_multi_init(); // foreach query: // 1) create a new cURL handle based on $this->curl_handle using curl_copy_handle() // 2) set the cURL url based on $this->base_url and the query string // 3) if #2 succeeded, add the handle to the multi handle, else create an error message // to be passed back to caller foreach($query_array as $i => $query) { // new curl handle based on our "template" handle $curl_handle = curl_copy_handle($this->curl_handle); $url = $this->base_url . '?' . rawurlencode($query); $this->debug("using url '$url' for query #$i"); // set the url, or set an error if (!curl_setopt($curl_handle, CURLOPT_URL, $url)) { $this->error("cannot set cURL CURLOPT_URL to '$url' for query #$i: " . curl_error($curl_handle) . "(" . curl_errno($curl_handle) . ")" ); array_push($curl_request, array( "error" => ("cannot set cURL CURLOPT_URL to '$url' for query #$i:" . curl_error($curl_handle) . "(" . curl_errno($curl_handle) . ")") )); } else { // add the "simple" handle to the "multi" handle curl_multi_add_handle($curl_multi_handle, $curl_handle); // keep track of all our "simple" handles - we'll loop through them later array_push($curl_request, array("curl_handle" => $curl_handle)); } // if setting CURLOPT_URL succeeded } // foreach query // start performing the multi request do { $curl_multi_exec_results = curl_multi_exec($curl_multi_handle, $curl_multi_exec_is_active); } while ($curl_multi_exec_results == CURLM_CALL_MULTI_PERFORM); while ($curl_multi_exec_is_active and $curl_multi_exec_results == CURLM_OK) { // wait for network if (curl_multi_select($curl_multi_handle) != -1) { // pull in any new data, or at least handle timeouts do { $curl_multi_exec_results = curl_multi_exec($curl_multi_handle, $curl_multi_exec_is_active); } while ($curl_multi_exec_results == CURLM_CALL_MULTI_PERFORM); } } if ($curl_multi_exec_results != CURLM_OK) { $this->error("curl_multi_exec() failed"); } // loop through "simple" handles foreach($curl_request as $i => $curl_request_data) { if (isset($curl_request_data["error"])) { // we failed before even sending the request array_push($curl_results, array("error" => $curl_request_data["error"])); } else { $error = curl_error($curl_request_data["curl_handle"]); if (($curl_request_data["curl_handle"]) && ($error == "")) { // request succeeded array_push($curl_results, array( "results" => curl_multi_getcontent($curl_request_data["curl_handle"]), "connection-stats" => $this->getConnectionStats($curl_request_data["curl_handle"]) ) ); } else { // we sent the request, but it failed array_push($curl_results, array("error" => $error) ); } } } return $curl_results; } ?>
What If I Need To Link Two Queries Together?
Unlike MySQL, DBSlayer is a stateless protocol. There is no guarantee that any two queries will execute in the same session environment or even the same server. Fortunately, the bulk of MySQL is also stateless as long as you avoid the SET command and a few other parameters that affect session state (and can really break things for other users). In most cases you can simply execute two SQL queries independently. However, if you need to guarantee that two SQL queries are executed in the same session, there are two options at your disposal:
- Separate the SQL queries with a semicolon.
- Wrap the SQL actions within a stored procedure and call that.
The first is better for ad hoc usage, but in the long-term you might find it better to go with the latter for the sake of data abstraction in some cases.
How Does The DBSlayer Return Multiple ResultSets??
As the following example demonstrates, the DBSlayer returns multiple result sets as multiple entries under the "RESULT" key in the JSON response. This means that RESULT will contain an associative array for a single result or an array of associative arrays for multiple results. Your code should handle either scenario.
{"SQL": "select Name, Population from testdb.City order by Population desc limit 5; select Name, Population from testdb.City order by Population asc limit 5;"}
{"RESULT": [{"TYPES" : ["MYSQL_TYPE_STRING" , "MYSQL_TYPE_LONG"] ,
"HEADER" : ["Name" , "Population"] ,
"ROWS" : [["Mumbai (Bombay)" , 10500000] ,
["Seoul" , 9981619] ,
["São Paulo" , 9968485] ,
["Shanghai" , 9696300] ,
["Jakarta" , 9604900]
]} ,
{"TYPES" : ["MYSQL_TYPE_STRING" , "MYSQL_TYPE_LONG"],
"HEADER" : ["Name" , "Population"] ,
"ROWS" : [["Adamstown" , 42],
["West Island" , 167],
["Fakaofo" , 300],
["Città del Vaticano" , 455],
["Bantam" , 503]]
}]
}
How Do I Do Transactions?
Transactions must obviously be executed in a single session and should not be spread across multiple calls to the DBSlayer. To do this, use semicolon-delimited SQL statements in a single query or a stored procedure as outlined above. For better abstraction, encapsulating transaction-protected statements in a stored procedure is preferable.
