The Example Environment

This appendix describes the contents of the Mimer SQL example environment.

The complete environment is owned by a USER ident named MIMER_STORE.

The MIMER_STORE ident is granted the privilege to create databanks and idents.

The example environment is based on a store that sells music and books, available in various formats. The store also has a web site, through which users can place orders. Stored procedures (SQL/PSM) are used to take care of various aspects of the business logic.

The example environment forms as the basis for examples given in the Mimer SQL documentation set.

Linux:On Linux, the dbinstall utility, used to create the system databanks, provides an option to create the example environment.

Win:On Windows, the Mimer Administrator is used to create the system databanks. The wizard used for this provides an option to create the example environment.

The example environment can also be created manually by using the EXLOAD program (see below).

For more information about the example environment, see the feature description ‘The Example Database’ on the Mimer SQL developer site,
https://developer.mimer.com/article/the-example-database/.

The EXLOAD program

By using the EXLOAD program the example environment can be created or removed. (The example environment can also be removed by dropping the MIMER_STORE ident from any SQL command tool).

At installation of the example environment, default values are provided for passwords and for databank file names. These default values can be changed by using the ALTER command after the installation is completed or by explicitly giving passwords and filenames as command line arguments to the EXLOAD command, see the syntax below.

Syntax

The EXLOAD syntax (expressed in Unix-style) is as follows:

exload [-s | -m] [-f | -r] [-p pass] [database]

exload [--single | --multi] [--force | --remove] [--password=pass] [database]

exload [-v|--version | [-?|--help]

When creating the example database it is possible to change the default values for passwords and filenames. This is done by adding parameters after the 'database' parameter (that must be given in this case). These optional parameters must be added in the following order:

[MIMER_STORE-password

MIMER_ADM-password

MIMER_USR-password

MIMER_WEB-password

MIMER_STORE-databank-filename

MIMER_ORDERS-databank-filename

MIMER_BLOBS-databank-filename]

Command-line Arguments

Unix-style	VMS-style	Function
-s --single	/SINGLE	Single-user mode database access
-m --multi	/MULTI	Multi-user mode database access
-r --remove	/REMOVE	Remove the example database
-f --force	/FORCE	Install the example database even if it already exists. The old environment is dropped.
-p SYSADMpwd --password=SYSADMpwd	/PASSWORD=SYSADMpwd	Password for SYSADM
database	database	Database name
MIMER_STOREpwd	MIMER_STOREpwd	Password for MIMER_STORE ident
MIMER_ADMpwd	MIMER_ADMpwd	Password for MIMER_ADM ident
MIMER_USRpwd	MIMER_USRpwd	Password for MIMER_USR ident
MIMER_WEBpwd	MIMER_WEBpwd	Password for MIMER_WEB ident
MIMER_STOREdbfilename	MIMER_STOREdbfilename	Filename for MIMER_STORE databank
MIMER_ORDERSdbfilename	MIMER_ORDERSdbfilename	Filename for MIMER_ORDERS databank
MIMER_BLOBSdbfilename	MIMER_BLOBSdbfilename	Filename for MIMER_BLOBS databank
-? --help	/HELP	Show help text.
-v --version	/VERSION	Display version information.

All parameters to EXLOAD are optional. If no command line arguments are given, the following will happen:

•A SYSADM password is needed and will be prompted for.

•Access mode and database name will be taken from MIMER_MODE and MIMER_DATABASE, respectively.

•If the example environment is already installed a question is prompted whether to continue or not

•Default values are used for all example database passwords and filenames. The values used are displayed when the EXLOAD program is executed.

Exit Codes

The EXLOAD program returns an error status to the operating system when an error is encountered. This may be useful when running EXLOAD from scripts.

The following error codes are used:

Linux/Windows	OpenVMS	Usage
0 (success)	1 (success)	This code is used when EXLOAD has executed successfully.
>1 (error)	2 (error)	This error code is used when EXLOAD failed to execute successfully.

The MIMER_STORE Schema

In this base schema most of the objects supported by Mimer SQL are introduced: collations, databanks, domains, idents, sequences, synonyms, tables and views. They are used to demonstrate some of the functionality that is available – constraints; primary, unique and foreign keys; secondary indices; NOT NULL; default values; and column and table check clauses. The schema also includes examples of PSM routines and triggers.

When an ident, other than a GROUP, is created, a schema of the same name is created by default. Objects in the MIMER_STORE schema have to be created before other objects can reference them.

Databanks

A databank is the physical file where a collection of tables is stored. There are three databanks defined in the example environment; the databank where the tables containing BLOBs will be stored is defined with the TRANSACTION option. The databanks have names that start with the prefix MIMER_.

Domains

There are a number of domains defined. In essence, a domain is a column data type specification that can be used in a number of table column definitions.

A number of different data types are introduced. The domains introduce the idea of consistency in the table definitions and demonstrate how to use check clauses, including the equivalent of NOT NULL.

Sequences

A sequence is an object that can provide a unique integer value. The sequences start with initial values other than the default to give the various sequences different number ranges; this is sometimes helpful when looking at data. The sequence names have a _SEQ suffix to help to clarify when they are being used.

Tables

Data in a relational database is logically organized in tables, which consist of horizontal rows and vertical columns. Columns are identified by a column-name.

A relational database is built up of several inter-dependent tables that can be joined together. Tables are joined by using related values that appear in one or more columns in each of the tables.

All the foundation tables are created under the MIMER_STORE schema; these tables provide a reasonably complete set for an introduction to SQL.

The tables CURRENCIES and COUNTRIES are used in many of the basic examples in this manual and contain entries for almost all countries and currencies, providing a moderate amount of data for any exercise.

Table name	Description
CURRENCIES	Holds currency details. Introduces the use of domains (qualified with the schema name) and the decimal data type. Demonstrates that column constraints can include the primary key definition. Introduces the concept of NOT NULL in column definitions. Uses the CHARACTER data type to show the difference compared to CHARACTER VARYING. The table is created with the schema name explicitly included, after this example the default option is used. Note the table is not defined in a specific databank, the system determines which databank to use; all other tables are created in a named databank. Examples of creating comments on the table and columns follow this table creation; these are the only examples of comments.
COUNTRIES	Holds country details. Introduces the concept of naming constraints; as when naming objects, a consistent approach will give benefits. Includes a unique constraint. Introduces a foreign key definition linking the currency code to an entry in the CURRENCIES table. The column in the referenced table (CURRENCIES) is explicitly named; in future this is only done if the columns are not defined as the primary key.
CATEGORIES	Product categories, i.e. Music and Books. A category of ‘Video’ is included for course work. Shows the use of a collation in a column definition. A collation is a set of rules that determines how data is sorted and compared. Character data is sorted using collations that define the correct character sequence, with the capability to handle case-sensitivity and accents. In this table create statement the primary, unique and foreign keys are defined as table constraints. This is the style that has been adopted in the rest of the schema, the previous examples were to show the syntax possibilities; again a consistent approach will make it easier for others.
FORMATS	Product formats, e.g. Paperback, Audio CD. A domain could/should have been used for the CATEGORY_ID columns in the CATEGORIES and FORMATS table. There are two unique constraints, both defined on two columns. A DISPLAY_ORDER column has been included; the idea is that this can be used to display the different formats for a particular product in an order other than alphabetic.
PRODUCERS	Name of the record label or book publisher i.e. the organization that made the product. Shows the use of a column default value that refers to a sequence.
PRODUCTS	Name of the product, i.e. name of the album or title of the book. It may be that the same product name is the title of a book and an album. Until this point these referenced tables had been defined with the primary key as the identifier and the associated ‘name’ as a unique key; this demonstrates the reverse. This table holds a Soundex value of the name. As we shall see later the name is processed before the Soundex value is taken. A secondary index is defined on this Soundex value.
IMAGES	An item may have an image associated with it; for example the image could be an album or book jacket cover. Note that this table is in the MIMER_BLOBS databank and therefore changes are not logged.
ITEMS	This forms a link between a product name and the different formats in which it is available. For example an album may appear in a number of different formats: Audio CD, Cassette, DVD Audio and Vinyl. The table contains a number of attributes for the item, such as price and stock level. There is also a reorder level but that is an extension for the classroom (i.e. a trigger can be used to place an order). This table references a number of other tables and introduces the ability to specify what action to take when a referenced row is deleted. Both the PRODUCER_ID and IMAGE_ID permit a null value, which allows outer joins to be demonstrated. A date data type is introduced in RELEASE_DATE. A secondary index is created on the date column allowing examples of how date ranges can be used. The European Article Numbering (EAN) code is a useful example; it is the barcode that appears on everything these days. It is a unique key in its own right but the table has ITEM_ID as the primary key, making the EAN_CODE a candidate key. Obviously the EAN_CODE could have been used in referencing tables but in general it is a mistake to use external identifiers to join tables; this conflicts with the use of the International codes in the CURRENCIES and COUNTRIES tables but the data in these tables can be considered to be static. A property of an EAN code is that it is a 13-digit number that incorporates a check digit. A PSM function to validate the check digit is included later in this schema; an ALTER TABLE statement then uses the PSM routine in a check clause to validate the EAN code.
CUSTOMERS	Personal details for store customers, whether they are on a mailing list or are Web users. Shows the use of current_date as a default value against the REGISTERED column. The idea is that when customers order through the Web-site they will be prompted for their e-mail address and password; the table check clause makes sure that the e-mail and password are either both defined or neither is defined. Secondary indices are defined on the date of birth and post code columns, these are both useful when trying to identify a customer (e.g. when there are too many John Smiths to search through by name).
ORDERS	A record of when a customer placed an order.
ORDER_ITEMS	What items went into an order and the price at the time that the order was placed. This is the first time that a multi-column primary key is used. VALUE is an SQL reserved word; the definition shows how to force the use of a reserved word.
STOP_WORDS	Contains the 100 most common words in the English language.

Note that the CUSTOMERS, ORDERS and ORDER_ITEMS tables are grouped together in the MIMER_ORDERS databank.

It could be argued that the ITEMS table is not fully normalized – the PRODUCER_ID is repeated for each different format. But if you think about it you will realize that there is no benefit in introducing another table, only a penalty.

PSM Routines

The MIMER_STORE schema also introduces some fairly simple PSM routines (defined in a module named ROUTINES). These routines provide a basic introduction to functions and procedures; they demonstrate the general syntax of a number of PSM statements, including deterministic and access options.

Routines defined in a module cannot be modified without dropping the module, in which case all the routines in the module are dropped.

Note:The use of the ‘@’ character which is used in BSQL to delimit SQL statements whose syntax involves use of the normal end-of-statement character ‘;’ before the actual end of the statement. The ‘@’ character may be used to delimit any statement; this is useful when dealing with large statements as the error reporting facility in BSQL shows more information in such cases.

You can debug PSM routines using Mimer SQL’s Java-based graphic debugger. The debugger has support for watching variables, step-wise execution and setting breakpoints. You can debug procedures, functions and triggers.

Routine name	Description
AGE_OF_ADULT	Procedure that returns the age that a person is considered to be an adult. A slightly contrived procedure to introduce the in and out parameter types; also includes an interval data type and a case statement.
CAPITALIZE	Function that processes a string making the first character of a word uppercase and the remainder of the word lowercase.
CAST_TO_DATE	Function that takes a character string (dd/mm/yy) and converts it to a date data type. Note the use of the like comparison. Introduce the concept of signaling, but this can be skipped over at this point.
EAN_CHECK_DIGIT	Function that returns the check digit for an EAN.
EXTRACT_DATETIME	Function to return just the variable component from a datetime or interval value. The value has to be cast to a character string in the call. Uses ‘is not null’.
EXTRACT_DATE	Function to show how EXTRACT_DATETIME is used.
INDEX_TEXT	Function to process a character string, removing all punctuation characters. Also removes any of the words that are held in the table STOP_WORDS by using the exists predicate.
PRODUCT_SEARCH_CODE	Function to return the Soundex value of a character string after it has been processed by the INDEX_TEXT function.
RECIPIENT	Function to form the recipient details, e.g. for a letter address – Mr J Smith. Note that the use of ‘is not null’ is redundant.
SALUTATION	Function to create a formal or informal salutation based on the age of the person. Performs simple date arithmetic using the AGE_OF_ADULT function.
STOP_WORDS	Database load procedure to extract the words from a character string and insert them into the STOP_WORDS table.
VALIDATE_EAN_CODE	Function to return the EAN code with the correct check digit. Example of large number arithmetic.

The ITEMS table is altered to include a check clause using the VALIDATE_EAN_CODE function to ensure that only valid EAN codes can be entered.

After the definition of the views (see next section for details) there is another PSM routine, it shows how to create a standalone procedure.

Procedures

Procedure name	Description
COMING_SOON	Result set procedure that returns the products for a particular category that will be released in the next month. Makes use of the PRODUCT_DETAILS view to demonstrate that a select against a view is treated the same as a table. Shows how to define a result set procedure and one form of the row data type. Introduces the concepts of cursors. The use of get diagnostics avoids any real need to cover condition handlers at this stage.

Procedure name

Description

COMING_SOON

Result set procedure that returns the products for a particular category that will be released in the next month.

Makes use of the PRODUCT_DETAILS view to demonstrate that a select against a view is treated the same as a table.

Shows how to define a result set procedure and one form of the row data type. Introduces the concepts of cursors.

The use of get diagnostics avoids any real need to cover condition handlers at this stage.

Views

There are a number of views in the MIMER_STORE schema to show various aspects of the functionality that can be supported.

View name	Description
CUSTOMER_DETAILS	Based on the single table CUSTOMERS. Shows the simplest type of view – updatable if the user has the privilege. Allows a new customer to be added to the database.
SWEDISH_CUSTOMERS	Based on the previous view (i.e. views can be defined on views) but with a selection condition so that only Swedish customers are displayed. Uses a ‘with check option’ so that only Swedish customers can be operated on through this view.
CUSTOMER_ADDRESSES	Again based on the CUSTOMER_DETAILS view but also joined to the COUNTRIES table. Shows how PSM functions can be used from standard SQL statements, including applying pre-defined functions on the result. Demonstrates how to name the columns in a view definition. Introduces the coalesce expression. The join is slightly more complicated than it needs to be because the column names are not consistent.
PRODUCT_DETAILS	Forms a view across a number of tables, including an outer join on the PRODUCERS table. Note that the coalesce expression in the select list is named; all calculated columns in a view have to be named.

Triggers

There are examples of two very simple statement triggers in the MIMER_STORE schema, both defined against the PRODUCTS table. They are designed to ensure that the value of the PRODUCT_SEARCH column is based on the product name. The insert trigger unconditionally performs an update of the PRODUCT_SEARCH column (setting it to the default value), which will force the update trigger to fire.

The update trigger checks whether the value in the PRODUCT_SEARCH column is equal to the value returned by the PRODUCT_SEARCH_CODE function when applied to the product name. If the value of PRODUCT_SEARCH is inconsistent with the product name then an update is performed; this will cause a recursive call of the update trigger. Note that an update statement causes the trigger to fire even when no rows are updated, hence the use of 'if exists'.

The PRODUCT_SEARCH_CODE function returns the Soundex value of the product name after it has been processed by the INDEX_TEXT function, which basically involves stripping out any of the words that appear in the STOP_WORDS table (i.e. the 100 most common words in the English language). As an example, the album title ‘Same As It Ever Was’ is reduced to ‘Same Ever’ by the INDEX_TEXT function and the Soundex value returned by the PRODUCT_SEARCH_CODE function would then be ‘467900’. By using this function against a user input string, it is possible to allow a limited level of fuzzy matching.

Idents

There are a number of idents, both users and groups. All ident names have a MIMER_ prefix and group idents have a _GROUP suffix to aid with their identification.

The user ident MIMER_ADM can be thought of as an administrator, which is the reason why the user has been given the system privilege to create new idents and tables. Access and object privileges are not granted directly to the user but inherited through membership of MIMER_ADMIN_GROUP. This group of idents has a high level of privileges to manipulate the database tables and views but they do not have total uncontrolled access, only the MIMER_STORE ident has that. It should be possible to perform a lot of the course work from the MIMER_ADM ident.

The user MIMER_USR is granted membership of the MIMER_STORE_GROUP. As can be seen it is possible to grant a group membership of another group; in this case, the members of the MIMER_ADMIN_GROUP (now and in the future) are automatically granted whatever privileges are allocated to MIMER_STORE_GROUP. So when the execute privilege on the PSM procedure COMING_SOON is allocated to MIMER_STORE_GROUP it is automatically also granted to the members of MIMER_ADMIN_GROUP, which includes the user MIMER_ADM.

Note:At the end of the default installation of the example database, the permission to create further idents and databanks from example environment is revoked. This is since the default system is given public passwords, and therefore is open to any user. The revoke commands used are the following:

REVOKE DATABANK FROM MIMER_STORE;

REVOKE IDENT FROM MIMER_STORE CASCADE;

To deliberately open up the system, use the GRANT IDENT and GRANT DATABANK statements.

See the exdef.sql file for details.

The MIMER_STORE_MUSIC Schema

Schemas have been used to group objects related to a specific area; note that all the schemas are owned by the ident MIMER_STORE.

It is possible to ignore the schemas other than MIMER_STORE; they can be viewed as a black box for the purposes of an introduction to SQL.

A CREATE SCHEMA statement is used to demonstrate that, within the statement, an object doesn't have to be created before it can be referenced.

The DURATION domain introduces the INTERVAL data type.

Tables

Table name	Description
ARTISTS	Holds artist names (e.g. Bruce Springsteen) with a Soundex value based on the name. Shows that objects in schema other than the current have to be qualified with the owning schema name.
TITLES	Links an artist name with an item.
TRACKS	Holds track details, e.g. title, length.
SAMPLES	Holds samples from the tracks.

Views

View name	Description
DETAILS	This view includes the PRODUCT_DETAILS view and demonstrates a number of things: Tables can be included from more than one schema. The use of a correlation name in the PRODUCT_DETAILS table reference. Restriction conditions can be applied so that different users would see different results, in this case the ident MIMER_WEB would not see vinyl albums. One of the quirks in SQL – ITEM_ID has to be explicitly included in the select-list.
SEARCH	This view is based on DETAILS but includes additional selection restrictions. Note that the AS clause is noise and may be omitted but it does add clarity. This view demonstrates both forms.

View name

Description

DETAILS

This view includes the PRODUCT_DETAILS view and demonstrates a number of things:

Tables can be included from more than one schema.

The use of a correlation name in the PRODUCT_DETAILS table reference.

Restriction conditions can be applied so that different users would see different results, in this case the ident MIMER_WEB would not see vinyl albums.

One of the quirks in SQL – ITEM_ID has to be explicitly included in the select-list.

This view is based on DETAILS but includes additional selection restrictions.

Note that the AS clause is noise and may be omitted but it does add clarity. This view demonstrates both forms.

Note that the CREATE SCHEMA statement includes grant object privilege statements as well as object definitions.

PSM Routines

The names in the PSM routines for this schema have been capitalized rather than the parts separated by an underscore.

PSM routine name	Description
AddTitle	Procedure to insert the base details for an album into the database, updating a number of tables. There are a number of checks on the input, for instance that the format is valid for the Music category and that the label exists in the PRODUCERS table – these introduce exception handlers. Uses another form of the row data type. Demonstrates the use of CURRENT VALUE of a sequence.
AddTrack	Procedure to insert the track details for an EAN code. Demonstrates the use of SQLSTATE values in an exception handler. Introduces the RESIGNAL statement and shows another option with GET DIAGNOSTICS.
ArtistName	Function to remove any leading definite or indefinite articles from a name.
ArtistSearchCode	Function to return the Soundex value of a character string after it has been processed by the ArtistName function.
Search	Result set procedure that searches the Music 'database' for matches based on the supplied title and artist details; a third parameter specifies the maximum number of rows to be returned (a value of zero suppresses this feature). Makes use of the SEARCH view. Includes the use of the DISPLAY_ORDER column in the FORMATS table to present the data in an order other than alphabetic. Each row is given a star rating that indicates the level of match (**** = exact). The artist 'Bruce Springsteen' provides a number of matches. This procedure demonstrates the level of functionality that can be placed in the database.
TitleDetails	Result set procedure that returns music details for a given item identifier. Given the result from a search this would allow the user to 'drill down' into the displayed information. Illustrates how the compound statement label can be used to qualify a variable name. Shows that interval arithmetic can be performed.
TrackDetails	Result set procedure that returns any track details for a given item identifier. Usage is the same as for TitleDetails. Demonstrates the use of a user defined SQLSTATE.

Triggers

There are two statement triggers in the MIMER_STORE_MUSIC schema, both defined against the ARTISTS table. They are designed to ensure that the value of the ARTIST_SEARCH column is based on the artist name. The same technique as used in the MIMER_STORE schema to force an unconditional update is applied in the insert trigger.

The update trigger is written to show that it can contain the same level of functionality as any PSM routine; in this case it uses a cursor to process the updates.

Idents

Execute privilege on the PSM routines Search, TitleDetails and TrackDetails is granted to MIMER_STORE_GROUP.

Synonyms are created by MIMER_STORE for all tables and views in the MIMER_STORE_MUSIC schema. The synonyms are created with a MUSIC_ prefix for the DETAILS, SEARCH and TITLES tables/views.

The MIMER_STORE_BOOK Schema

This schema contains a table named TITLES, as does the MIMER_STORE_MUSIC schema.

Tables

Table name	Description
TITLES	Links an item with a list of authors and an ISBN. A book may have more than one author, the names are held as a list in the column AUTHORS_LIST in the form: surname, forenames; surname, forenames; …
AUTHORS	Links an item to an entry in the KEYWORDS table.
KEYWORDS	Holds each author in the form surname,[first initial]. An extension would be to categorize books and create a new table to form a link between an item and various categories, with the category being held in the KEYWORDS table.

Table name

Description

TITLES

Links an item with a list of authors and an ISBN.

A book may have more than one author, the names are held as a list in the column AUTHORS_LIST in the form: surname, forenames; surname, forenames; …

AUTHORS

Links an item to an entry in the KEYWORDS table.

KEYWORDS

Holds each author in the form surname,[first initial].

An extension would be to categorize books and create a new table to form a link between an item and various categories, with the category being held in the KEYWORDS table.

Object privileges are granted on the MIMER_STORE_BOOK schema tables to MIMER_ADMIN_GROUP.

PSM Routines

PSM routine name	Description
VALIDATE_ISBN	Procedure to validate an ISBN. Uses an INOUT parameter. Shows how to CAST to an INTEGER and trap any error to validate that a string is numeric.
FORMAT_ISBN	Function to format an ISBN (e.g. insert hyphens to separate the country, group, publisher, title identifiers). Demonstrates the use of CASE statements, including where there is not an ELSE. Uses a row data type to simplify coding.
AUTHORS	Function to return the first author from a list of authors; if there is more than one author then a mark of omission is included.
AUTHORS_NAME	Function to format an author's name into surname[,initial].
KEYWORD_ID	Function to insert a word into the KEYWORDS table and return the identifier with which the keyword is associated.
CATALOGUE_AUTHORS	Given the list of authors associated with a book, extracts each author, calls the AUTHORS_NAME function and then the KEYWORD_ID function. Finally, creates a link between each name and the book in the AUTHORS table.
ADD_TITLE	Procedure to insert the base details for a book into the database, updating a number of tables. Inserts against the DETAILS view.
SEARCH	Result set procedure that searches the Book 'database' for matches based on the supplied title and author (surname, forename). The author 'Christie, Agatha' will provide a number of matches. Demonstrates a different method of searching.
TITLE_DETAILS	Result set procedure that returns book details for a given item identifier. Given the result from a search this would allow the user to 'drill down' into the displayed information.

Views

View name	Description
DETAILS	This view includes the PRODUCT_DETAILS view and is the equivalent of the DETAILS view in the MIMER_STORE_MUSIC schema.

Triggers

There are two statement triggers defined against the TITLES table. They are designed to maintain the entries in the AUTHORS and KEYWORDS tables. The update trigger is written to show how to use the OLD and NEW table aliases.

There is also an INSTEAD OF trigger defined on the DETAILS view. This is used by the ADD_TITLE procedure to update the underlying tables on which the view is based. Note that the INSTEAD OF trigger has to be defined before an insert statement against the view can be included, otherwise the join is not considered to be updateable.

Idents

Access privileges on the view DETAILS are granted to MIMER_ADMIN_GROUP.

Execute privilege on the PSM routines SEARCH and TITLE_DETAILS is granted to MIMER_STORE_GROUP.

Synonyms are created by MIMER_STORE for all tables in the MIMER_STORE_BOOK schema. The synonyms are created with a BOOK_ prefix for the DETAILS view and TITLES table.

The MIMER_STORE Schema Revisited

A quick return to the base schema to include two PSM routines that are outside the scope of an introduction to SQL.

PSM Routines

PSM routine name	Description
ORDER_ITEM	Procedure to associate an order for a quantity of a particular item against an order identifier.
BARCODE	Result set procedure that returns the book or music details for a given EAN. The intention would be to use this with a POS barcode reader.

PSM routine name

Description

ORDER_ITEM

Procedure to associate an order for a quantity of a particular item against an order identifier.

BARCODE

Result set procedure that returns the book or music details for a given EAN.

The intention would be to use this with a POS barcode reader.

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.

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.

Synonyms

A complete set of synonyms is created for the ident MIMER_ADM. This is to simply access to the tables in the classroom; the synonym can be used in place of the table-names that would have to be qualified with the schema-name.