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.
SESSIONS Maps an external session identifier with an internal order identifier.Keeps track of the date/time that the 'basket' was last accessed.
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.
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.
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.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40