No SQL query components
Example 1: load my saved game
You are developing this cool game that runs on mobile devices. Although your game is addictive, your players will stop playing from time to time so you decided to persist the games as Apstrata documents. When your players get back to the game, you need to load its data back to the device, i.e. get the corresponding document using its document key (in our example, we assume our document has the following key F0BBBBEC7EE9EF59EAC220599BADFD08).
The simplest way for you to create a "Saved Query" is to log in to the Apstrata workbench, click on "Manage App > Saved Queries > New". This opens a query editor within which a predefined template is already loaded.
<query> <executeACL>creator</executeACL> <!-- This query only authorizes the retrieval of a document created by the caller of the query --> <store>DefaultStore</store> <!-- Put the name of your store here. "DefaultStore" is the default value --> <condition><![CDATA[ apsdb.documentKey<string> = {docKey}]]></condition> <!-- {docKey} is a mandatory parameter to pass to the query --> <returnedFields> <!-- The list of document fields we want to return --> <field>player</field> <field>level</field> <field>lives</field> <field>score</field> </returnedFields> </query>
Try it!
From the "Saved Queries" editor, click "Save" to save your query then "Run". The workbench opens a form where you have to enter the "docKey" field with a value that matches one of your game document keys. Once done, click "Go" to execute the saved query.
Invoke the saved query
curl "https://varick.apstrata.com/apsdb/rest/O763A7F690/Query?apsws.time=1419244026775 &apsws.authSig=00826da6btdd6514a14986s366d681cf &apsws.responseType=json&apsws.authMode=simple &apsdb.queryName=loadGame&docKey=F0BBBBEC7EE9EF59EAC220599BADFD08"
// We retrieve the value of the docKey parameter expected by the saved query, from the request var docKey = request.parameters["docKey"]; var params = { "apsdb.queryName": "loadGame", // we pass the name of the saved query "docKey": docKey } return apsdb.callApi("Query", params, null);
try { String authKey = "O763A7F690"; // Replace with your Application key String baseURL = "https://varick.apstrata.com/apsdb/rest"; // Sign your call with a signature based on your application key and secret to authenticate against Apstrata // Simple signature is used in this example String secret = "S4A0120A6E64952FE75285BD0XD243S2"; // Replace with your application secret Connection ownerConnection = new OwnerConnection(baseUrl, authKey, secret); Client client = new Client(baseURL, authKey, ownerConnection); Client.AuthMode mode = Client.AuthMode.SIMPLE; // Prepare the parameters to send to the SaveDocument API List<NameValuePair> parameters = new ArrayList<NameValuePair>(); // Prepare the parameters to send according to the requested Apstrata API List<NameValuePair> parameters = new ArrayList<NameValuePair>(); parameters.add(new BasicNameValuePair("apsdb.queryName","loadGame")); parameters.add(new BasicNameValuePair("docKey","F0BBBBEC7EE9EF59EAC220599BADFD08")); // Invoke the API using the Client instance and signature mode // note that since we're not passing files, we send "null" in place of the "files" parameter response = client.callAPIJson(Query, parameters, null, mode); } catch (Exception e) { response = "Error: " + e.getMessage(); e.printStackTrace(); }
Example 2: get top 10 highest scores, in descending order, grouped by level
In your game, you would like to display the 10 best scores of all times, as well as the corresponding level player name. Using Apstrata, you can simply create a query that returns no more than 10 results and such as "score > 0", also specifying that the results should be sorted in descending order.
Once again, let us use the Apstrata workbench to create a saved query:
<query> <executeACL>creator</executeACL><!-- This query only authorizes the retrieval of a document created by the caller of the query --> <store>DefaultStore</store> <condition><![CDATA[ score<numeric> > 0]]></condition><!-- we search for documents that have a strictly positive score --> <returnedFields> <!-- list of fields to return --> <field>player</field> <field>level</field> <field>lives</field> <field>score</field> </returnedFields> <aggregate> <expression>Max($score)</expression> <!-- we query by Max score --> <global>true</global> <!-- the aggregation is applied to all the documents in the store that match the condition --> <groupBy> <!-- group results by level --> <field> <name>level</name> <type>numeric</type> </field> </groupBy> </aggregate> <resultsPerPage>10</resultsPerPage> <!-- only return 10 results per page. since we do not mention a page number, we will get the top 10 scores --> </query>
Invoke the saved query
curl "https://varick.apstrata.com/apsdb/rest/O763A7F690/Query?apsws.time=1419263545113 &apsws.authSig=00826da6btdd6514a14986s366d681cf &apsws.responseType=json &apsws.authMode=simple &apsdb.queryName=topScores"
// We retrieve the value of the docKey parameter expected by the saved query, from the request var docKey = request.parameters["docKey"]; var params = { "apsdb.queryName": "topScores" // we pass the name of the saved query } return apsdb.callApi("Query", params, null);
try { String authKey = "O763A7F690"; // Replace with your Application key String baseURL = "https://varick.apstrata.com/apsdb/rest"; // Sign your call with a signature based on your application key and secret to authenticate against Apstrata // Simple signature is used in this example String secret = "S4A0120A6E64952FE75285BD0XD243S2"; // Replace with your application secret Connection ownerConnection = new OwnerConnection(baseUrl, authKey, secret); Client client = new Client(baseURL, authKey, ownerConnection); Client.AuthMode mode = Client.AuthMode.SIMPLE; // Prepare the parameters to send to the SaveDocument API List<NameValuePair> parameters = new ArrayList<NameValuePair>(); parameters.add(new BasicNameValuePair("apsdb.queryName","topScores")); // Invoke the API using the Client instance and signature mode // note that since we're not passing files, we send "null" in place of the "files" parameter response = client.callAPIJson(Query, parameters, null, mode); } catch (Exception e) { response = "Error: " + e.getMessage(); e.printStackTrace(); }