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.