Example 3 - "Bookstore” application
Last updated
Last updated
We are going to use this schema as the sample schema for the examples that follow - This is the Gravity Bookstore sample database from DatabaseStar.
This schema contains the following tables:
book: a list of all books available in the store.
book_author: stores the authors for each book, which is a many-to-many relationship.
author: a list of all authors.
book_language: a list of possible languages of books.
publisher: a list of publishers for books.
customer: a list of the customers of the Gravity Bookstore.
customer_address: a list of addresses for customers, as a customer can have more than one address, and an address has more than one customer.
address_status: a list of statuses for an address, because addresses can be current or old.
address: a list of addresses in the system.
country: a list of countries that addresses are in.
cust_order: a list of orders placed by customers.
order_line: a list of books that are a part of each order.
shipping_method: the possible shipping methods for an order.
order_history: the history of an order, such as ordered, cancelled, delivered.
order_status: the possible statuses of an order.
Here is the ERD of this sample bookstore database:
This is the schema in the Neurelo Schema Language JSON Specification format
Get one author by id 19283
GET https://$ENV_API_URL/rest/author/19283
?select={"$scalars": true, "$related": true}Select first 10 publisher and books ordered by publisher name
GET https://$ENV_API_URL/rest/publisher
?select={"$scalars": true, "book": {"$scalars": true}}
&order_by=[{"publisher_name": "asc"}]
&take=10Select customer scalars and their orders. Filter by email containing "cdbaby.com"
GET https://$ENV_API_URL/rest/customer
?select={"$scalars": true, "cust_order": {"order_id": true, "order_line": {"$scalars": true}}}
&filter={"email": {"contains": "@cdbaby.com"}}Select book_id and the number of books sold where sales > 8
GET https://$ENV_API_URL/rest/order_line
?select={"book_id": true}
&aggregate={"_count": ["line_id"]}
&group_by=["book_id"]
&having={"book_id": {"_count": {"gt": 8}}}Get list of customer order with order_history and scalars. Filter by status is Order Received
GET https://$ENV_API_URL/rest/cust_order
?select={"order_history": {"order_status": {"$scalars": true}, "status_date": true}, "$scalars": true}
&filter={"order_history": {"every": {"order_status": {"status_value": {"equals": "Order Received"}}}}}
&take=3Get list of books not translated to "English” (.English. - includes “English”, “British English”, “United States English”), or “Spanish
GET https://$ENV_API_URL/rest/book
?select={"book_id": true, "title": true}
&filter={"book_language": {"NOT": [{"language_name": {"contains": "English"}}, {"language_name": "Spanish"}]}}Create a new book with author
POST https://$ENV_API_URL/rest/book
?select={"$scalars": true, "$related": true}
{
"book_id": 19283,
"title": "DUNE: A new hope",
"book_author": {
"create": {
"author": {
"create": {
"author_id": 19283,
"author_name": "Frank Lucas"
}
}
}
}
}Create many authors
POST https://$ENV_API_URL/rest/author
[
{
"author_id": 19284,
"author_name": "Francis Hube"
},
{
"author_id": 19285,
"author_name": "Brandon Stevens"
},
{
"author_id": 19286,
"author_name": "Red Roberts"
}
]Create a new author with many books
POST https://$ENV_API_URL/rest/author
?select={"$scalars": true, "$related": true}
{
"author_id": 19288,
"author_name": "Arthur Doyle",
"book_author": {
"create": [
{
"book": {
"create": {
"book_id": 19286,
"title": "Sherlock Holmes: Mistery one"
}
}
},
{
"book": {
"create": {
"book_id": 19287,
"title": "Sherlock Holmes: Mistery two"
}
}
}
]
}
}Create a customer with address and address status
POST https://$ENV_API_URL/rest/customer
?select={"$scalars": true, "customer_address": {"address": {"$scalars": true}}}
{
"customer_id": 19201,
"first_name": "Watson",
"email": "[email protected]",
"customer_address": {
"create": {
"status_id": 1,
"address": {
"create": {
"address_id": 19201,
"city": "San Francisco",
"street_name": "Big Street",
"street_number": "1"
}
}
}
}
}Update a single address
PATCH https://$ENV_API_URL/rest/address/19200
?select={"$scalars": true}
{
"city": "London",
"street_name": "Baker Street",
"street_number": "221B"
}Update multiple addresses based on filter
PATCH https://$ENV_API_URL/rest/address
?filter={"city": "London"}
{
"city": "London",
"street_name": "Baker Street",
"street_number": "221B"
}Update book title where title and language
PATCH https://$ENV_API_URL/rest/book
?filter={"AND": {"title": "Bed time stories", "book_language": {"language_name": "Russian"}}}
{
"isbn13": "the-new-isbn"
}Delete publishers that have forbidden books published
DELETE https://$ENV_API_URL/rest/publisher
?filter={"book": {"some": {"title": "Forbidden text as of now"}}}