Here is a list of all the queries that are available to you to manipulate your business repository tables.
The API queries that apply to business repositories allow adding, selecting, updating and deleting operations.
Introduction
There are several types of query bodies. The most sophisticated is the one used to manipulate the content of the business repository.
The elements in the body of this query correspond to parts of SQL queries.
We have a trade repository that lists animals (dogs and cats). It has a Name column (Text), a Race column (Text) and a Tattoo column (Boolean).
The selectors (1️⃣ selectors)
“selectors” corresponds to the part that follows the WHERE in a SQL query.
Example : UPDATE table SET nom_colonne_1 WHERE selectors
The structure of the selector is a list of JSONs, each of which must contain in key, the ID of a column and in value, the value to be set.
If several key/value pairs are in the same JSON, the returned result will be the intersection of the conditions, i.e. the conditions will be assembled with a logical “ET“.
Conversely, several JSONs in the selector will allow you to retrieve the union of your results, i.e. a logical “OR”.
Simple selector
To retrieve the set of race de cats, I use the following selector:"selectors":[{"f0_race":"Chat"}]
That’s like saying “I’m getting all the rows that have “Cat” in the race column.”
Selector “AND”
To recover all the cats that are tattooed, I use the following selector:"selectors":[{"f0_race":"Chat", "f2_tatouage":"true"}]
It’s like saying “g id=”gid_0”>”I’m getting all the rows that have “Cat” in the race column AND “true” in the tattoo column.”
“OR” selector
To retrieve all the dogs and animals called “Slipper”, I use the following selector:"selectors":[{"f0_race":"Chien"},{"f1_nom":"Pantoufle"}]
That’s like saying “I get all the rows that have “cat” in the “breed” column and all the rows that have “slipper” in the “name” column.”
The difference with the previous selector is that two JSONs with one condition each are used instead of one JSON with two conditions: this results in a OR instead of a ET.
The new values (2️⃣ newValues)
“newValues” corresponds to the part that follows SET (in the case of an UPDATE) or VALUES (in the case of an INSERT).
Example : UPDATE table SET newValues WHERE condition
"selectors": [
{
"f0_race": "Chat",
"f1_nom": "Plume"
}
],
"newValues": {
"f1_nom": "Chaussette"
}It’s like saying “I change the name of every cat whose name is “Feather” to “Sock.””
The column(s) I wish to select (3️⃣ colomnsToRetrieve)
“columnsToRetrieve” is the part before SELECT.
Example : SELECT columnsToRetrieve FROM nom_du_tableau
"selectors": [
{
"f1_race": "Chat"
}
],
"columnsToRetrieve": {
"f1_nom"
}This is like saying “Selects all the names of the lines where “Cat” is present in the “Race” column.
The different methods
[POST] /table
Creates a new business repository.
The ids entered for the creation of a business repository as well as for the columns will not be taken into account. They are generated automatically and may not be filled in the body of the query.
In the case below, we have therefore removed them.
{
"name": "Liste d'entreprises",
"fields": [
{
"name": "Nom de l'entreprise",
"type": "TEXT"
},
{
"name": "Nombre d'employés",
"type": "NUMBER"
}
]
}Result:
[GET] /table/tables
Retrieves all business repositories and returns their structures/columns their structures/columns (name, id, type).
No need to send arguments.
Result:
[{
"name": "Liste d'entreprises",
"id": 30,
"fields": [
{
"name": "Nom de l'entreprise",
"id": "f0_nomdelentreprise",
"type": "TEXT"
},
{
"name": "Nombre d'employés",
"id": "f1_nombredemployes",
"type": "NUMBER"
}
]
},
{
"name" : "Autre référentiel métier",
"id": 31,
...
},
{
"name" : "Encore un autre référentiel métier",
"id": 32,
...
}][GET] /table/{tableId}
Retrieves the business repository having for ID the one transmitted in the path.
Result for /table/30 :
{"name":"Liste d'entreprises","id":30,"fields":[{ "name": "Nom de l'entreprise", "id": "f0_nomdelentreprise", "type": "TEXT" }, { "name": "Nombre d'employés", "id": "f1_nombredemployes", "type": "NUMBER" }]}
[DELETE] /table/{tableId}
Sdeletes the business repository having for ID the one transmitted in path..
[PUT] /table/{tableId}/rows
Adds new lines to the business repository having as ID the one transmitted in the path.
If a column is absent when adding a row, the element will have the value “null”.
[
{
"f0_nomdelentreprise": "Entreprise A",
"f1_nombredemployes": "10"
},
{
"f0_nomdelentreprise": "Entreprise B",
"f1_nombredemployes": "20"
},
{
"f0_nomdelentreprise": "Entreprise C",
"f1_nombredemployes": "30"
},
{
"f0_nomdelentreprise": "Entreprise D",
"f1_nombredemployes": "40"
}
]Result:
[GET] /table/{tableId}/rows
Retrieves the content (all lines) of the business repository having for ID the one transmitted in the path.
Result for /table/30/rows :
[{ "f0_nomdelentreprise": "Entreprise A", "f1_nombredemployes": "10" }, { "f0_nomdelentreprise": "Entreprise B", "f1_nombredemployes": "20" }, { "f0_nomdelentreprise": "Entreprise C", "f1_nombredemployes": "30" }, { "f0_nomdelentreprise": "Entreprise D", "f1_nombredemployes": "40" }]
[POST] /table/{tableId}/rows
Updates the business repository having as ID the one transmitted in the path.
Only the lines that correspond to those in the selectors will be impacted.
{
"selectors": [
{
"f0_nomdelentreprise": "Entreprise A",
"f1_nombredemployes": "10"
},
{
"f0_nomdelentreprise": "Entreprise B",
"f1_nombredemployes": "500"
}
],
"newValues": {
"f0_nomdelentreprise": "Entreprise A ou B modifiée",
"f1_nombredemployes": "60"
}
}In our case, only “Company A” is supposed to be modified since it does have 10 employees while “Company B” does not have 500 employees.
Result:
[POST] /table/{tableId}/rows (UpdateAll)
Updates the business repository having as ID the one transmitted in the path.
All lines will be impacted.
The “selectors” and “columnsToRetrieve” parts of the body of the query are not exploited. You don’t have to fill them in, you can even delete them.
{
"newValues": {
"f0_nomdelentreprise": "Entreprise Z"
}
}
It is requested that all rows in column f0_company name now have the value “Company Z”.
Result:
[PUT] /table/{tableId}/rows/delete
Deletes the desired lines in the business repository having as ID the one transmitted in the path.
The “newValues” and “columnsToRetrieve” parts of the body of the query are not exploited. You don’t have to fill them in, you can even delete them.
In the case below, we have therefore removed them.
{
"selectors": [
{
"f0_company name": "Company D"
}
]
}
We indicate that we want to delete the rows that have “Company D” in the “Company Name” column.
[POST] /table/{tableId}/rows/select
Selects the elements corresponding to the selector in the column indicated in “columnsToRetrieve”.
The “newValues” part of the body of the query is not exploited. It is therefore not necessary to fill it in, it can even be deleted.
In the case below, we have therefore removed it.
{
"selectors": [
{
"f1_nombredemployes": "20"
},
{
"f1_nombredemployes": "30"
}
],
"columnsToRetrieve": [
"f0_nomdelentreprise"
]
}
Result:
[DELETE] /table/{tableId}/rows
Deletes all the lines in the business repository having as ID the one transmitted in the path.
