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=10

• Select 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=3

• Get 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": "[e-mail]",
      "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"}}}