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.
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.
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.
Note that the CREATE SCHEMA statement includes grant object privilege statements as well as object definitions.
The names in the PSM routines for this schema have been capitalized rather than the parts separated by an underscore.
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 stored 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.
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.
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.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40