Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


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.


Table name
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
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 ORDERS table 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 SESSION_EXPIRATION_PERIOD.
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.
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 BY clause. Calls the BARCODE procedure, 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 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.

Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
Mimer SQL Documentation TOC PREV NEXT INDEX