Example 3 - "Bookstore” application
Schema
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
JSON Schema
{
"objects": {
"address": {
"properties": {
"address_id": {
"type": "integer",
"identifier": true
},
"city": {
"type": "string",
"sourceType": [
"VarChar",
100
],
"nullable": true
},
"country": {
"nullable": true,
"$ref": "#/objects/country",
"relation": {
"attrKey": [
"country_id"
],
"foreignAttrKey": [
"country_id"
],
"sourceName": "fk_addr_ctry",
"onUpdate": "NoAction",
"onDelete": "NoAction"
}
},
"country_id": {
"type": "integer",
"nullable": true
},
"cust_order": {
"type": "array",
"items": {
"$ref": "#/objects/cust_order"
}
},
"customer_address": {
"type": "array",
"items": {
"$ref": "#/objects/customer_address"
}
},
"street_name": {
"type": "string",
"sourceType": [
"VarChar",
200
],
"nullable": true
},
"street_number": {
"type": "string",
"sourceType": [
"VarChar",
10
],
"nullable": true
}
}
},
"address_status": {
"properties": {
"address_status": {
"type": "string",
"sourceType": [
"VarChar",
30
],
"nullable": true
},
"status_id": {
"type": "integer",
"identifier": true
}
}
},
"author": {
"properties": {
"author_id": {
"type": "integer",
"identifier": true
},
"author_name": {
"type": "string",
"sourceType": [
"VarChar",
400
],
"nullable": true
},
"book_author": {
"type": "array",
"items": {
"$ref": "#/objects/book_author"
}
}
}
},
"book": {
"properties": {
"book_author": {
"type": "array",
"items": {
"$ref": "#/objects/book_author"
}
},
"book_id": {
"type": "integer",
"identifier": true
},
"book_language": {
"nullable": true,
"$ref": "#/objects/book_language",
"relation": {
"attrKey": [
"language_id"
],
"foreignAttrKey": [
"language_id"
],
"sourceName": "fk_book_lang",
"onUpdate": "NoAction",
"onDelete": "NoAction"
}
},
"isbn13": {
"type": "string",
"sourceType": [
"VarChar",
13
],
"nullable": true
},
"language_id": {
"type": "integer",
"nullable": true
},
"num_pages": {
"type": "integer",
"nullable": true
},
"order_line": {
"type": "array",
"items": {
"$ref": "#/objects/order_line"
}
},
"publication_date": {
"type": "string",
"format": "date",
"sourceType": "Date",
"nullable": true
},
"publisher": {
"nullable": true,
"$ref": "#/objects/publisher",
"relation": {
"attrKey": [
"publisher_id"
],
"foreignAttrKey": [
"publisher_id"
],
"sourceName": "fk_book_pub",
"onUpdate": "NoAction",
"onDelete": "NoAction"
}
},
"publisher_id": {
"type": "integer",
"nullable": true
},
"title": {
"type": "string",
"sourceType": [
"VarChar",
400
],
"nullable": true
}
}
},
"book_author": {
"properties": {
"author": {
"$ref": "#/objects/author",
"relation": {
"attrKey": [
"author_id"
],
"foreignAttrKey": [
"author_id"
],
"sourceName": "fk_ba_author",
"onUpdate": "NoAction",
"onDelete": "NoAction"
}
},
"author_id": {
"type": "integer"
},
"book": {
"$ref": "#/objects/book",
"relation": {
"attrKey": [
"book_id"
],
"foreignAttrKey": [
"book_id"
],
"sourceName": "fk_ba_book",
"onUpdate": "NoAction",
"onDelete": "NoAction"
}
},
"book_id": {
"type": "integer"
}
},
"constraints": [
{
"type": "identifier",
"property": [
"book_id",
"author_id"
],
"sourceName": "pk_bookauthor"
}
]
},
"book_language": {
"properties": {
"book": {
"type": "array",
"items": {
"$ref": "#/objects/book"
}
},
"language_code": {
"type": "string",
"sourceType": [
"VarChar",
8
],
"nullable": true
},
"language_id": {
"type": "integer",
"identifier": true
},
"language_name": {
"type": "string",
"sourceType": [
"VarChar",
50
],
"nullable": true
}
}
},
"country": {
"properties": {
"address": {
"type": "array",
"items": {
"$ref": "#/objects/address"
}
},
"country_id": {
"type": "integer",
"identifier": true
},
"country_name": {
"type": "string",
"sourceType": [
"VarChar",
200
],
"nullable": true
}
}
},
"cust_order": {
"properties": {
"address": {
"nullable": true,
"$ref": "#/objects/address",
"relation": {
"attrKey": [
"dest_address_id"
],
"foreignAttrKey": [
"address_id"
],
"sourceName": "fk_order_addr",
"onUpdate": "NoAction",
"onDelete": "NoAction"
}
},
"customer": {
"nullable": true,
"$ref": "#/objects/customer",
"relation": {
"attrKey": [
"customer_id"
],
"foreignAttrKey": [
"customer_id"
],
"sourceName": "fk_order_cust",
"onUpdate": "NoAction",
"onDelete": "NoAction"
}
},
"customer_id": {
"type": "integer",
"nullable": true
},
"dest_address_id": {
"type": "integer",
"nullable": true
},
"order_date": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
],
"nullable": true
},
"order_history": {
"type": "array",
"items": {
"$ref": "#/objects/order_history"
}
},
"order_id": {
"type": "integer",
"default": {
"function": "autoincrement"
},
"identifier": true
},
"order_line": {
"type": "array",
"items": {
"$ref": "#/objects/order_line"
}
},
"shipping_method": {
"nullable": true,
"$ref": "#/objects/shipping_method",
"relation": {
"attrKey": [
"shipping_method_id"
],
"foreignAttrKey": [
"method_id"
],
"sourceName": "fk_order_ship",
"onUpdate": "NoAction",
"onDelete": "NoAction"
}
},
"shipping_method_id": {
"type": "integer",
"nullable": true
}
}
},
"customer": {
"properties": {
"cust_order": {
"type": "array",
"items": {
"$ref": "#/objects/cust_order"
}
},
"customer_address": {
"type": "array",
"items": {
"$ref": "#/objects/customer_address"
}
},
"customer_id": {
"type": "integer",
"identifier": true
},
"email": {
"type": "string",
"sourceType": [
"VarChar",
350
],
"nullable": true
},
"first_name": {
"type": "string",
"sourceType": [
"VarChar",
200
],
"nullable": true
},
"last_name": {
"type": "string",
"sourceType": [
"VarChar",
200
],
"nullable": true
}
}
},
"customer_address": {
"properties": {
"address": {
"$ref": "#/objects/address",
"relation": {
"attrKey": [
"address_id"
],
"foreignAttrKey": [
"address_id"
],
"sourceName": "fk_ca_addr",
"onUpdate": "NoAction",
"onDelete": "NoAction"
}
},
"address_id": {
"type": "integer"
},
"customer": {
"$ref": "#/objects/customer",
"relation": {
"attrKey": [
"customer_id"
],
"foreignAttrKey": [
"customer_id"
],
"sourceName": "fk_ca_cust",
"onUpdate": "NoAction",
"onDelete": "NoAction"
}
},
"customer_id": {
"type": "integer"
},
"status_id": {
"type": "integer",
"nullable": true
}
},
"constraints": [
{
"type": "identifier",
"property": [
"customer_id",
"address_id"
],
"sourceName": "pk_custaddr"
}
]
},
"order_history": {
"properties": {
"cust_order": {
"nullable": true,
"$ref": "#/objects/cust_order",
"relation": {
"attrKey": [
"order_id"
],
"foreignAttrKey": [
"order_id"
],
"sourceName": "fk_oh_order",
"onUpdate": "NoAction",
"onDelete": "NoAction"
}
},
"history_id": {
"type": "integer",
"default": {
"function": "autoincrement"
},
"identifier": true
},
"order_id": {
"type": "integer",
"nullable": true
},
"order_status": {
"nullable": true,
"$ref": "#/objects/order_status",
"relation": {
"attrKey": [
"status_id"
],
"foreignAttrKey": [
"status_id"
],
"sourceName": "fk_oh_status",
"onUpdate": "NoAction",
"onDelete": "NoAction"
}
},
"status_date": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
],
"nullable": true
},
"status_id": {
"type": "integer",
"nullable": true
}
}
},
"order_line": {
"properties": {
"book": {
"nullable": true,
"$ref": "#/objects/book",
"relation": {
"attrKey": [
"book_id"
],
"foreignAttrKey": [
"book_id"
],
"sourceName": "fk_ol_book",
"onUpdate": "NoAction",
"onDelete": "NoAction"
}
},
"book_id": {
"type": "integer",
"nullable": true
},
"cust_order": {
"nullable": true,
"$ref": "#/objects/cust_order",
"relation": {
"attrKey": [
"order_id"
],
"foreignAttrKey": [
"order_id"
],
"sourceName": "fk_ol_order",
"onUpdate": "NoAction",
"onDelete": "NoAction"
}
},
"line_id": {
"type": "integer",
"default": {
"function": "autoincrement"
},
"identifier": true
},
"order_id": {
"type": "integer",
"nullable": true
},
"price": {
"type": "string",
"format": "decimal",
"sourceType": [
"Decimal",
5,
2
],
"nullable": true
}
}
},
"order_status": {
"properties": {
"order_history": {
"type": "array",
"items": {
"$ref": "#/objects/order_history"
}
},
"status_id": {
"type": "integer",
"identifier": true
},
"status_value": {
"type": "string",
"sourceType": [
"VarChar",
20
],
"nullable": true
}
}
},
"publisher": {
"properties": {
"book": {
"type": "array",
"items": {
"$ref": "#/objects/book"
}
},
"publisher_id": {
"type": "integer",
"identifier": true
},
"publisher_name": {
"type": "string",
"sourceType": [
"VarChar",
400
],
"nullable": true
}
}
},
"shipping_method": {
"properties": {
"cost": {
"type": "string",
"format": "decimal",
"sourceType": [
"Decimal",
6,
2
],
"nullable": true
},
"cust_order": {
"type": "array",
"items": {
"$ref": "#/objects/cust_order"
}
},
"method_id": {
"type": "integer",
"identifier": true
},
"method_name": {
"type": "string",
"sourceType": [
"VarChar",
100
],
"nullable": true
}
}
}
}
}GET endpoint examples
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"}]}}
POST endpoint examples
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": "watson@solvesmysteries.com", "customer_address": { "create": { "status_id": 1, "address": { "create": { "address_id": 19201, "city": "San Francisco", "street_name": "Big Street", "street_number": "1" } } } } }
PATCH endpoint examples
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 endpoint examples
Delete publishers that have forbidden books published
DELETE https://$ENV_API_URL/rest/publisher ?filter={"book": {"some": {"title": "Forbidden text as of now"}}}
Last updated
