The MIMER_STORE_WEB Schema

This schema provides some of the SQL functionality required to create a Web-based application to order items.

The ident-name in the AUTHORIZATION clause is currently restricted to be the name of the current ident.

The basic idea behind a Web application would be two tabs, one for Music and the other for Books. The relevant SEARCH routine provides a list of matches (one of the details returned is the ITEM_ID). The user would then have the ability to drill down to display further information (use MIMER_STORE_MUSIC.TitleDetails and MIMER_STORE_MUSIC.TrackDetails for music items and MIMER_STORE_BOOK.TITLE_DETAILS for book items.

If an item is selected for purchase (the quantity should be prompted for) and MIMER_STORE_WEB.ADD_TO_BASKET used to order the item (a blank SESSION_ID defines a new session). Once a session has been created the basket can be viewed using MIMER_STORE_WEB.VIEW_BASKET.

When an order has been completed the user needs to be identified by their e-mail and password (MIMER_STORE_WEB.VALIDATE_CUSTOMER) and then a call should be made to MIMER_STORE_WEB.PLACE_ORDER.

Tables

Table name	Description
`SESSIONS`	Maps an external session identifier with an internal order identifier. Keeps track of the date/time that the 'basket' was last accessed.

PSM Routines


PSM routine name	Description
`SESSION_EXPIRATION_PERIOD`	Function that returns an interval data type that defines the period in which a basket should be accessed.
`DELETE_BASKET`	Procedure to delete a specified basket session; alternatively a session of '`*`' will delete all 'baskets' that have expired. Note that the procedure deletes entries in the `ORDERS` table and relies on foreign key definitions in referencing tables to tidy up.
`ORDER_ID`	Function to return the order identifier associated with a specified session identifier. Raises an exception if the 'basket' hasn't been used within the period specified by `SESSION_EXPIRATION_PERIOD`.
`VALIDATE_BASKET`	Function that uses the `ORDER_ID` function to validate that the basket is still active. Uses an exception handler to catch any `SESSION_INVALID` exception raised by `ORDER_ID`. The exception handler will call `DELETE_BASKET` to remove a basket that has expired.
`ADD_TO_BASKET`	Function to place an order for a quantity of a specific item. If the session identifier is provided, then the order is placed against the relevant order identifier; if the session identifier is blank, then a new basket is created. The function returns the current session identifier.
`VIEW_BASKET`	Result set procedure that lists the items ordered. Uses a `GROUP BY` clause. Calls the `BARCODE` procedure, which is itself a result set procedure.
`VALIDATE_CUSTOMER`	Function to identify a customer by their e-mail address and password.
`PLACE_ORDER`	Procedure to order the contents of the basket. Procedure takes two in parameter: session identifier and customer identifier. Returns an order number, total price in euros, the local currency for the customer and the price in that local currency.

Triggers

There is a statement trigger that will fire after an update to the SESSIONS table. The trigger is designed to prevent any changes to the values of the SESSION_NO and ORDER_ID columns. This demonstrates that a trigger can be used to abort an SQL update operation.

Idents

A synonym is created by MIMER_STORE for the SESSIONS tables in the MIMER_STORE_WEB schema.

A new user ident, MIMER_WEB, is created to allow Web-applications execute privilege on certain of the PSM routines.