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 .
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
{
"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 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=10GET 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": "watson@solvesmysteries.com",
"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"}}}Select customer scalars and their orders. Filter by email containing ""
