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
AUTHORIZATIONclause 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
SEARCHroutine 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.TrackDetailsfor music items and
MIMER_STORE_BOOK.TITLE_DETAILSfor book items.
If an item is selected for purchase (the quantity should be prompted for) and
MIMER_STORE_WEB.ADD_TO_BASKETused to order the item (a blank
SESSION_IDdefines a new session). Once a session has been created the basket can be viewed using
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
Maps an external session identifier with an internal order identifier.Keeps track of the date/time that the 'basket' was last accessed.
Function that returns an interval data type that defines the period in which a basket should be accessed.
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
ORDERStable and relies on foreign key definitions in referencing tables to tidy up.
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
Function that uses the
ORDER_IDfunction to validate that the basket is still active.Uses an exception handler to catch any
SESSION_INVALIDexception raised by
ORDER_ID. The exception handler will call
DELETE_BASKETto remove a basket that has expired.
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.
Result set procedure that lists the items ordered.Uses a
GROUP BYclause. Calls the
BARCODEprocedure, which is itself a result set procedure.
Function to identify a customer by their e-mail address and password.
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.
There is a statement trigger that will fire after an update to the
SESSIONStable. The trigger is designed to prevent any changes to the values of the
ORDER_IDcolumns. This demonstrates that a trigger can be used to abort an SQL update operation.
A synonym is created by
SESSIONStables in the
A new user ident,
MIMER_WEB, is created to allow Web-applications execute privilege on certain of the PSM routines.
Mimer Information Technology AB
Phone: +46 18 780 92 00