This document provides a detailed guide to using the REST query interface of Polypheny. The interface provides an easy way for external applications to interact with Polypheny using HTTP methods.
Introduction
REST (Representational State Transfer) is an architectural style for distributed systems, particularly web services. A RESTful interface allows for interaction with resources (like database tables or objects) through standard HTTP methods, such as GET (retrieve), POST (create), PUT/PATCH (update), and DELETE (delete). REST interfaces are useful because:
- Platform Independence: Clients can be written in any language and run on any platform that supports HTTP.
- Statelessness: Each request from a client contains all the information needed to process the request, making scaling easier.
- Cacheability: Responses can be cached, enhancing performance.
- Layered System: Allows for more modular architecture, with components interacting in a layered manner.
In the context of Polypheny, the REST interface offers a seamless way to interact with the database using the familiar HTTP methods. It abstracts away the complexities of direct database communication, providing a more straightforward way to execute queries and manage data.
API Base URL
The base URL for all API endpoints is:
{protocol}://{host}:{port}/restapi/v1/res/{namespace}.{entity}
Where:
{protocol}
: The protocol used, typicallyhttp
.{host}
: The host address, typically127.0.0.1
for local installations.{port}
: The port on which the REST Interface is listing, default is8089
.{namespace}
: The name of the namespace{entity}
: The name of the entity (e.g., table)
Authentication
All requests to the REST API must be authenticated using basic authentication. Please use the username pa
and an empty password.
request.basicAuth("pa", "");
Response Format
The server responds with a JSON object. A typical response object contains the following:
{
"result": [
{
"column_name": "value",
...
}
],
"size": 1
}
Error Handling
If the server returns a non-success status code, an exception is raised with an appropriate error message.
Basic CRUD Operations
CREATE (Insert Data)
To insert data into a table, use the POST
method.
Payload:
{
"data": [
{
"namespace.entity.column": "value",
...
}
]
}
Examples:
Java
import kong.unirest.Unirest;
import com.google.gson.JsonObject;
import com.google.gson.JsonPrimitive;
public void insertData() {
JsonObject row = new JsonObject();
row.add("public.users.name", new JsonPrimitive("John Doe"));
row.add("public.users.age", new JsonPrimitive(25));
String url = "http://127.0.0.1:8089/restapi/v1/res/public.users";
Unirest.post(url)
.basicAuth("pa", "")
.header("Content-ExpressionType", "application/json")
.body(row)
.asString();
}
Bash
#!/bin/bash
# ------------------------------
# Configuration
# ------------------------------
HOST="127.0.0.1"
PORT="8089"
USERNAME="pa"
PASSWORD=""
ENDPOINT="/restapi/v1/res/public.user"
# Data to be sent to the server
USER_DATA=(
"public.user.name=John Doe"
"public.user.age=25"
)
# Convert data array to JSON format
json_data() {
local data
for item in "${USER_DATA[@]}"; do
key="${item%=*}"
value="${item#*=}"
data+="\"$key\": \"$value\","
done
echo "{ \"data\": [ { ${data%,} } ] }"
}
# ------------------------------
# Main Script Execution
# ------------------------------
URL="http://$HOST:$PORT$ENDPOINT"
# Send POST request using curl
curl -X POST "$URL" \
-u "$USERNAME:$PASSWORD" \
-H "Content-Type: application/json" \
-d "$(json_data)"
READ (Retrieve Data)
To retrieve data from a table or view, use the GET
method.
Query Parameters:
<namespace>.<entity>.<alias_or_column>=<operation><literal_value>
Examples:
Retrieving rows from the table public.users
where name
is John Doe
:
Java
import kong.unirest.Unirest;
public void fetchData() {
String filterCondition = "public.users.name==John Doe";
String url = "http://127.0.0.1:8089/restapi/v1/res/public.users";
String response = Unirest.get(url)
.basicAuth("pa", "")
.queryString(filterCondition)
.asString()
.getBody();
}
Bash
#!/bin/bash
# ------------------------------
# Configuration
# ------------------------------
HOST="127.0.0.1"
PORT="8089"
USERNAME="pa"
PASSWORD=""
ENDPOINT="/restapi/v1/res/public.users"
# Filter condition components
COLUMN="public.users.name"
OPERATOR="="
VALUE="John Doe"
# Assemble the filter condition
FILTER_CONDITION="${COLUMN}=${OPERATOR}${VALUE}"
# ------------------------------
# Main Script Execution
# ------------------------------
URL="http://$HOST:$PORT$ENDPOINT"
# Send GET request using curl
curl -X GET "$URL" \
-u "$USERNAME:$PASSWORD" \
-G \
--data-urlencode "$FILTER_CONDITION"
UPDATE (Modify Data)
To update data in a table, use the PATCH
method.
Payload:
{
"data": [
{
"namespace.entity.column": "new_value",
...
}
]
}
Query Parameters:
<namespace>.<entity>.<alias_or_column>=<operation><literal_value>
Examples:
Updating the value of age
to 26
in the table public.users
where name
is John Doe
:
Java
import kong.unirest.Unirest;
import com.google.gson.JsonObject;
public void updateData() {
// Data to update
JsonObject dataToUpdate = new JsonObject();
dataToUpdate.addProperty("public.users.age", 26);
String filterCondition = "public.users.name==John Doe";
String url = "http://127.0.0.1:8089/restapi/v1/res/public.users";
Unirest.patch(url)
.basicAuth("pa", "")
.header("Content-Type", "application/json")
.queryString(filterCondition)
.body(dataToUpdate.toString())
.asString();
}
Bash
DELETE (Remove Data)
To delete data from a table, use the DELETE
method.
Query Parameters:
<namespace>.<entity>.<alias_or_column>=<operation><literal_value>
Examples:
Deleting rows from the table public.users
where name
is John Doe
:
Java
import kong.unirest.Unirest;
public void deleteData() {
String filterCondition = "public.users.name==John Doe";
String url = "http://127.0.0.1:8089/restapi/v1/res/public.users";
Unirest.delete(url)
.basicAuth("pa", "")
.queryString(filterCondition)
.asString();
}
Bash
#!/bin/bash
# ------------------------------
# Configuration
# ------------------------------
HOST="127.0.0.1"
PORT="8089"
USERNAME="pa"
PASSWORD=""
ENDPOINT="/restapi/v1/res/public.users"
# Filter condition components
FILTER_COLUMN="public.users.name"
FILTER_VALUE="John Doe"
FILTER_OPERATOR="="
FILTER_CONDITION="${FILTER_COLUMN}=${FILTER_OPERATOR}${FILTER_VALUE}"
# ------------------------------
# Main Script Execution
# ------------------------------
URL="http://$HOST:$PORT$ENDPOINT"
# Send DELETE request using curl
curl -X DELETE "$URL" \
-u "$USERNAME:$PASSWORD" \
--data-urlencode "$FILTER_CONDITION"
Filter
Logical Operators:
AND
: Combines two or more conditions and returns true if all conditions are true.OR
: Combines two or more conditions and returns true if at least one condition is true.
Comparison Operators:
EQ
: Equal to.NEQ
: Not equal to.GT
: Greater than.LT
: Less than.GTE
: Greater than or equal to.LTE
: Less than or equal to.
Special Operators:
IN
: Checks if a value is within a set of values.LIKE
: Checks if a value matches a pattern (with wildcards).
Syntax:
- Filters should be provided as a JSON object.
- Each filter condition should be an object with the following properties:
column
: The name of the column to filter on.operator
: The operator to use (from the list above).value
: The value to compare against.
- Multiple conditions can be combined using the logical operators.
Usage:
- To filter rows where the column “name” is equal to “John”:
{ "column": "name", "operator": "EQ", "value": "John" }
- To filter rows where the column “age” is greater than 25 and the column “city” is “New York”:
{ "AND": [ { "column": "age", "operator": "GT", "value": 25 }, { "column": "city", "operator": "EQ", "value": "New York" } ] }
Examples:
Filtering rows from the table public.users
where the column age
is greater than 25 and the column city
is “New York”:
Java
import kong.unirest.Unirest;
import com.google.gson.JsonObject;
import com.google.gson.JsonArray;
public void filterData() {
// Constructing the filter conditions
JsonObject ageCondition = new JsonObject();
ageCondition.addProperty("column", "public.users.age");
ageCondition.addProperty("operator", "GT");
ageCondition.addProperty("value", 25);
JsonObject cityCondition = new JsonObject();
cityCondition.addProperty("column", "public.users.city");
cityCondition.addProperty("operator", "EQ");
cityCondition.addProperty("value", "New York");
JsonArray combinedConditions = new JsonArray();
combinedConditions.add(ageCondition);
combinedConditions.add(cityCondition);
JsonObject filter = new JsonObject();
filter.add("AND", combinedConditions);
// Making the request
String url = "http://127.0.0.1:8089/restapi/v1/res/public.users";
String response = Unirest.get(url)
.basicAuth("pa", "")
.queryString("filter", filter.toString())
.asString()
.getBody();
System.out.println(response);
}
Sort
The REST API provides a sorting option that allows users to sort the results of their queries based on specified columns. The sorting option is implemented using the sort
parameter in the request.
Syntax:
?sort=<columnName>:<sortOrder>
<columnName>
: The name of the column by which you want to sort the results.<sortOrder>
: The order in which you want to sort the results. It can be eitherasc
for ascending order ordesc
for descending order.
Multiple columns can be specified for sorting by separating them with a comma.
Usage:
?sort=name:asc,age:desc
This will sort the results first by the name
column in ascending order and then by the age
column in descending order.
Examples:
Java
import kong.unirest.Unirest;
public void fetchSortedData() {
// Define the sorting conditions
String sortConditions = "public.users.name:asc,public.users.age:desc";
// Making the request
String url = "http://127.0.0.1:8089/restapi/v1/res/public.users";
String response = Unirest.get(url)
.basicAuth("pa", "")
.queryString("sort", sortConditions)
.asString()
.getBody();
System.out.println(response);
}
Aggregate
The aggregate
option allows you to perform aggregation operations on the data. The structure of the aggregate
option is as follows:
- type: Specifies the type of aggregation operation to be performed. Supported values include:
COUNT
SUM
AVG
MIN
MAX
-
column: Specifies the column on which the aggregation operation should be performed.
- as: (Optional) Specifies an alias for the resulting aggregated column.
Usage:
{
"aggregate": [
{
"type": "SUM",
"column": "price",
"as": "total_price"
}
]
}
In the above example, the SUM
aggregation operation will be performed on the price
column, and the result will be aliased as total_price
.
Example (Java):
import kong.unirest.Unirest;
public void fetchAggregatedData() {
// Define the aggregate conditions
String aggregateConditions = "type:SUM,column:public.products.price,as:total_price";
// Making the request
String url = "http://127.0.0.1:8089/restapi/v1/res/public.products";
String response = Unirest.get(url)
.basicAuth("pa", "")
.queryString("aggregate", aggregateConditions)
.asString()
.getBody();
System.out.println(response);
}
Project
The project
option allows you to select specific columns from the data. The structure of the project
option is as follows:
-
column: Specifies the name of the column to be selected.
-
as: (Optional) Specifies an alias for the selected column.
Usage:
{
"project": [
{
"column": "product_name",
"as": "name"
},
{
"column": "product_id"
}
]
}
In the above example, the product_name
column will be selected and aliased as name
, and the product_id
column will be selected without any alias.
Examples:
Java
import kong.unirest.Unirest;
public void fetchProjectedData() {
// Define the project conditions
String projectConditions = "column:public.products.product_name,as:name;column:public.products.product_id";
// Making the request
String url = "http://127.0.0.1:8089/restapi/v1/res/public.products";
String response = Unirest.get(url)
.basicAuth("pa", "")
.queryString("project", projectConditions)
.asString()
.getBody();
System.out.println(response);
}
Join
The REST interface allows you to perform join operations between tables. Joining tables combines rows from two or more tables based on a related column between them. This enables you to query data from multiple tables as one set.
Syntax:
To perform a join operation, you need to specify the tables you want to join and the condition for the join. The general syntax for joining is:
{protocol}://{host}:{port}/restapi/v1/res/{namespace}.{entity}?join={namespace2}.{entity2}&on={namespace}.{entity}.{column}={namespace2}.{entity2}.{column2}
Where:
{namespace2}
and{entity2}
represent the second table you want to join with.{column}
and{column2}
are the columns on which the join condition is based.
Types of Joins:
The REST interface supports various types of joins, including:
- Inner Join: Returns records that have matching values in both tables.
- Left (Outer) Join: Returns all records from the left table, and the matched records from the right table.
- Right (Outer) Join: Returns all records from the right table, and the matched records from the left table.
- Full (Outer) Join: Returns all records when there is a match in either the left or the right table.
The type of join can be specified using the type
parameter in the URL.
Examples:
Joining the public.users
table with the public.orders
table on the id
column:
Java
import kong.unirest.Unirest;
public void joinTables() {
String url = "http://127.0.0.1:8089/restapi/v1/res/public.users";
String response = Unirest.get(url)
.basicAuth("pa", "")
.queryString("join", "public.orders")
.queryString("on", "public.users.id=public.orders.userId")
.asString()
.getBody();
}
In this example, we are performing an inner join by default. To specify a different type of join, you can add the type
parameter to the query string.
Group
The REST interface allows you to group data based on one or more columns. Grouping data is particularly useful when you want to aggregate data in specific categories.
Syntax:
To perform a grouping operation, you need to specify the columns by which you want to group the data. The general syntax for grouping is:
{protocol}://{host}:{port}/restapi/v1/res/{namespace}.{entity}?groupby={namespace}.{entity}.{column1},{namespace}.{entity}.{column2},...
Where:
{column1}
,{column2}
, … are the columns by which you want to group the data.
Using Grouping with Aggregation
Grouping is often used in conjunction with aggregation to summarize data. For instance, you can group data by a specific column and then calculate the sum, average, or count of another column within each group.
Examples:
Grouping the public.sales
table by the product
column and calculating the total quantity sold for each product:
Java
import kong.unirest.Unirest;
public void groupData() {
String url = "http://127.0.0.1:8089/restapi/v1/res/public.sales";
String response = Unirest.get(url)
.basicAuth("pa", "")
.queryString("groupby", "public.sales.product")
.queryString("aggregate", "type:SUM,column:public.sales.quantity,as:total_quantity")
.asString()
.getBody();
System.out.println(response);
}
In this example, the data from the sales
table is grouped by the product
column. For each product, the total quantity sold is calculated and returned as total_quantity
.
Limiting and Offsetting Results
The REST interface provides functionality to limit the number of results returned by a query and to offset the starting point of the results. This is particularly useful for implementing pagination in applications.
Limiting
To limit the number of results returned by a query, use the limit
parameter.
Syntax:
{protocol}://{host}:{port}/restapi/v1/res/{namespace}.{entity}?limit={number}
Where:
{number}
is the maximum number of results you want to retrieve.
Offsetting
To skip a specific number of results before starting to return the results, use the offset
parameter.
Syntax:
{protocol}://{host}:{port}/restapi/v1/res/{namespace}.{entity}?offset={number}
Where:
{number}
is the number of results to skip before returning the remaining results.
Using Limiting and Offsetting Together
You can use both limit
and offset
together to implement pagination.
Syntax:
{protocol}://{host}:{port}/restapi/v1/res/{namespace}.{entity}?limit={limitNumber}&offset={offsetNumber}
Where:
{limitNumber}
is the maximum number of results you want to retrieve.{offsetNumber}
is the number of results to skip.
Example (Java):
Retrieving the next 10 products from the public.products
table after skipping the first 50:
import kong.unirest.Unirest;
public void fetchPaginatedData() {
String url = "http://127.0.0.1:8089/restapi/v1/res/public.products";
String response = Unirest.get(url)
.basicAuth("pa", "")
.queryString("limit", 10)
.queryString("offset", 50)
.asString()
.getBody();
System.out.println(response);
}
In this example, the first 50 products are skipped, and the next 10 products are retrieved from the products
table.