--==============================================================
--
-- Mimer SQL Example database DDL script
--
--==============================================================

SET ECHO ON;
SET MESSAGE OFF;

WHENEVER WARNING CONTINUE;


--==============================================================
-- Create a log file and record the date/time.
--==============================================================

LOG INPUT, OUTPUT ON 'mimer_store.log';
SET :start_time = LOCALTIMESTAMP(0);


--==============================================================
-- Ensure that the default transaction modes are set.
--==============================================================

ROLLBACK;
SET SESSION READ WRITE;
SET SESSION ISOLATION LEVEL REPEATABLE READ;
-- *****SET SESSION DIAGNOSTICS SIZE 50;
SET TRANSACTION START IMPLICIT;


--==============================================================
-- Script should be read by the System Administrator (SYSADM).
--==============================================================

SET :user = SESSION_USER;


--==============================================================
-- Remove any existing objects related to the example database.
--==============================================================

WHENEVER ERROR CONTINUE;
DROP IDENT mimer_store CASCADE;
WHENEVER ERROR EXIT, ROLLBACK;


--==============================================================
--#Creating databanks, idents, and tables
-- Create owning ident for example database objects.
--
-- Ident has the rights to create databanks and idents.
--==============================================================

CREATE IDENT mimer_store AS USER USING 'GoodiesRUs';

GRANT DATABANK TO mimer_store;
GRANT IDENT TO mimer_store WITH GRANT OPTION;

DISCONNECT;


--==============================================================
-- Connect as the example database owner.
--==============================================================

CONNECT;
MIMER_STORE
GoodiesRUs


--==============================================================
-- Create databanks to hold the tables.
--==============================================================

CREATE DATABANK mimer_store OF 1200 PAGES IN 'mimer_store.dbf'
   WITH LOG OPTION;
CREATE DATABANK mimer_orders OF 200 PAGES IN 'mimer_orders.dbf'
   WITH LOG OPTION;
CREATE DATABANK mimer_blobs OF 1000 PAGES IN 'mimer_blobs.dbf'
   WITH TRANS OPTION;


--**************************************************************
-- MIMER_STORE schema.
--
-- A schema defines a local environment within which private
-- database objects can be created. When an ident (other than a
-- GROUP) is created then a schema of the same name is created
-- by default.
--
-- The full name for any object is <schema_name>.<object_name>
-- e.g. MIMER_STORE.ADDRESS_LINE
--**************************************************************


--==============================================================
-- Create domains in the default schema for the owning ident.
--==============================================================

CREATE DOMAIN address_line AS CHARACTER VARYING(48);
CREATE DOMAIN country_code AS CHARACTER(2);
CREATE DOMAIN currency_code AS CHAR(3);
CREATE DOMAIN euros AS NUMERIC(7, 2)
                       CONSTRAINT euros_value_is_null CHECK (VALUE IS NOT NULL)
                       CONSTRAINT euros_value_illegal CHECK (VALUE > 0.0);
CREATE DOMAIN format_id AS SMALLINT;
CREATE DOMAIN internal_id AS INTEGER;
CREATE DOMAIN name AS VARCHAR(48) COLLATE english
                      DEFAULT NULL
                      CONSTRAINT name_value_is_null CHECK (VALUE IS NOT NULL);
CREATE DOMAIN soundex AS CHAR(6) DEFAULT '000000'
                         CONSTRAINT soundex_value_is_null
                            CHECK (VALUE IS NOT NULL)
                         CONSTRAINT soundex_value_illegal
                            CHECK (CHAR_LENGTH(TRIM(VALUE)) = 6
                                   AND VALUE BETWEEN '000000' AND '999999');


--==============================================================
-- Create sequences.
-- A sequence can provide a series of integer values.
--==============================================================

CREATE UNIQUE SEQUENCE customer_id_seq INITIAL_VALUE = 100001 INCREMENT = 1;
CREATE UNIQUE SEQUENCE item_id_seq INITIAL_VALUE = 60001;
CREATE UNIQUE SEQUENCE product_id_seq INITIAL_VALUE = 30001;
CREATE UNIQUE SEQUENCE producer_id_seq INITIAL_VALUE = 10001;
CREATE UNIQUE SEQUENCE object_id_seq;
CREATE UNIQUE SEQUENCE order_id_seq INITIAL_VALUE = 700001;
CREATE SEQUENCE order_no_seq INITIAL_VALUE = 10000 MAX_VALUE = 99999;


--==============================================================
-- Create tables.
--==============================================================

CREATE TABLE mimer_store.currencies (
      code mimer_store.currency_code PRIMARY KEY,
      currency CHARACTER(32) NOT NULL,
      exchange_rate DECIMAL(12, 4));

COMMENT ON TABLE currencies IS 'Holds currency details';
COMMENT ON COLUMN currencies.code IS 'ISO currency code';
COMMENT ON COLUMN currencies.currency IS 'Currency name';
COMMENT ON COLUMN currencies.exchange_rate IS 'Conversion rate against euro';


CREATE TABLE countries (
      code country_code CONSTRAINT cnt_primary_key PRIMARY KEY,
      country name CONSTRAINT cnt_country_exists UNIQUE
                   CONSTRAINT cnt_country_is_null NOT NULL,
      currency_code currency_code CONSTRAINT cnt_currency_code_is_null NOT NULL,
      FOREIGN KEY (currency_code) REFERENCES currencies(code))
   IN mimer_store;


CREATE TABLE categories (
      category_id SMALLINT,
      category CHAR VARYING(20) COLLATE english
                                CONSTRAINT ctg_category_is_null NOT NULL,
      CONSTRAINT ctg_primary_key PRIMARY KEY (category_id),
      CONSTRAINT ctg_category_exists UNIQUE (category))
   IN mimer_store;


CREATE TABLE formats (
      format_id format_id,
      format VARCHAR(20) COLLATE english
                         CONSTRAINT fmt_format_is_null NOT NULL,
      category_id SMALLINT CONSTRAINT fmt_category_id_is_null NOT NULL,
      display_order SMALLINT CONSTRAINT fmt_display_order_is_null NOT NULL,
      CONSTRAINT fmt_primary_key PRIMARY KEY (format_id),
      CONSTRAINT fmt_format_category_id_exists UNIQUE (format, category_id),
      CONSTRAINT fmt_category_id_display_order_exists
         UNIQUE (category_id, display_order),
      CONSTRAINT fmt_categories FOREIGN KEY (category_id) REFERENCES categories)
   IN mimer_store;


CREATE TABLE producers (
      producer_id internal_id DEFAULT NEXT_VALUE OF producer_id_seq,
      producer name CONSTRAINT prd_producer_is_null NOT NULL,
      CONSTRAINT prd_primary_key PRIMARY KEY (producer_id),
      CONSTRAINT prd_producer_exists UNIQUE (producer))
   IN mimer_store;

CREATE INDEX prd_producer on producers(producer COLLATE english);

CREATE TABLE products (
      product name,
      product_id internal_id DEFAULT NEXT_VALUE OF product_id_seq,
      product_search soundex,
      CONSTRAINT pdt_primary_key PRIMARY KEY (product),
      CONSTRAINT pdt_product_id_exists UNIQUE (product_id))
   IN mimer_store;

CREATE INDEX pdt_product on products(product COLLATE english);
CREATE INDEX pdt_product_search ON products(product_search);


CREATE TABLE images (
      image_id internal_id DEFAULT NEXT_VALUE OF object_id_seq,
      image BLOB(20K) CONSTRAINT img_image_is_null NOT NULL,
      CONSTRAINT img_primary_key PRIMARY KEY (image_id))
   IN mimer_blobs;


CREATE TABLE items (
      item_id internal_id DEFAULT NEXT_VALUE OF item_id_seq,
      product_id internal_id CONSTRAINT itm_product_id_is_null NOT NULL,
      format_id format_id CONSTRAINT itm_format_id_is_null NOT NULL,
      release_date DATE,
      status CHAR DEFAULT 'A' CONSTRAINT itm_status_is_null NOT NULL
                              CONSTRAINT itm_status_illegal
                                 -- Available, Deleted
                                 CHECK (status IN ('A', 'X')),
      price euros CONSTRAINT itm_price_illegal
                     CHECK (price >= 4.99 AND price <= 366.00),
      stock SMALLINT CONSTRAINT itm_stock_is_null NOT NULL
                     CONSTRAINT itm_stock_illegal CHECK (stock >= 0),
      reorder_level SMALLINT CONSTRAINT itm_reorder_level_is_null NOT NULL,
      ean_code BIGINT CONSTRAINT itm_ean_code_is_null NOT NULL,
      producer_id internal_id DEFAULT NULL,
      image_id internal_id DEFAULT NULL,
      CONSTRAINT itm_primary_key PRIMARY KEY(item_id),
      CONSTRAINT itm_ean_code_exists UNIQUE (ean_code),
      CONSTRAINT itm_products
         FOREIGN KEY (product_id) REFERENCES products(product_id)
         ON DELETE CASCADE ON UPDATE NO ACTION,
      CONSTRAINT itm_formats FOREIGN KEY (format_id) REFERENCES formats
         ON DELETE CASCADE ON UPDATE NO ACTION,
      CONSTRAINT itm_producers FOREIGN KEY (producer_id) REFERENCES producers
         ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT itm_images FOREIGN KEY (image_id) REFERENCES images
         ON DELETE SET DEFAULT ON UPDATE NO ACTION)
   IN mimer_store;

CREATE INDEX itm_release_date ON items(release_date);


CREATE TABLE customers (
      customer_id internal_id DEFAULT NEXT_VALUE OF customer_id_seq,
      title VARCHAR(6),
      surname name CONSTRAINT cst_surname_is_null NOT NULL,
      forename VARCHAR(24) COLLATE english
                           CONSTRAINT cst_forename_is_null NOT NULL,
      date_of_birth DATE,
      address_1 address_line CONSTRAINT cst_address_1_is_null NOT NULL,
      address_2 address_line,
      town VARCHAR(32) CONSTRAINT cst_town_is_null NOT NULL,
      postcode VARCHAR(12) COLLATE english
                           CONSTRAINT cst_postcode_is_null NOT NULL,
      country_code country_code CONSTRAINT cst_country_code_is_null NOT NULL,
      email VARCHAR(128) COLLATE english,
      password VARCHAR(18),
      registered DATE DEFAULT CURRENT_DATE,
      last_order TIMESTAMP(0) DEFAULT NULL,
      CONSTRAINT cst_primary_key PRIMARY KEY (customer_id),
      CONSTRAINT cst_email_exists UNIQUE (email),
      CONSTRAINT cst_countries FOREIGN KEY (country_code) REFERENCES countries
         ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT cst_email_password_cross_check
         CHECK (   ((email IS NULL OR email = '')
                    AND (password IS NULL OR password = ''))
                OR ((email IS NOT NULL AND email <> '')
                    AND (password IS NOT NULL AND password <> ''))))
   IN mimer_orders;

CREATE INDEX cst_date_of_birth ON customers(date_of_birth);
CREATE INDEX cst_postcode ON customers(postcode);


CREATE TABLE orders (
      order_id internal_id DEFAULT NEXT_VALUE OF order_id_seq,
      order_no VARCHAR(16),
      customer_id internal_id,
      datetime TIMESTAMP(0) DEFAULT LOCALTIMESTAMP
                            CONSTRAINT ord_datetime_is_null NOT NULL,
      CONSTRAINT ord_primary_key PRIMARY KEY (order_id),
      CONSTRAINT ord_order_no_exists UNIQUE (order_no),
      CONSTRAINT ord_customers  FOREIGN KEY (customer_id) REFERENCES customers
         ON DELETE NO ACTION ON UPDATE NO ACTION)
   IN mimer_orders;


CREATE TABLE order_items (
      order_id internal_id,
      seq_no INTEGER,
      item_id internal_id CONSTRAINT ori_item_id_is_null NOT NULL,
      quantity SMALLINT CONSTRAINT ori_quantity_is_null NOT NULL,
      "VALUE" euros,
      CONSTRAINT ori_primary_key PRIMARY KEY (order_id, seq_no),
      CONSTRAINT ori_orders FOREIGN KEY (order_id) REFERENCES orders
         ON DELETE CASCADE ON UPDATE NO ACTION,
      CONSTRAINT ori_items FOREIGN KEY (item_id) REFERENCES items
         ON DELETE NO ACTION ON UPDATE NO ACTION)
   IN mimer_orders;


CREATE TABLE stop_words (
      word VARCHAR(10) COLLATE english
                       CONSTRAINT stp_word_illegal CHECK (word <> ''),
      CONSTRAINT stp_primary_key PRIMARY KEY (word))
   IN mimer_store;

CREATE INDEX stp_word on stop_words(word collate english);
--==============================================================
--#Creating MIMER_STORE schema PSM routines
-- Note the use of '@'.
--==============================================================

@
CREATE MODULE routines
   DECLARE PROCEDURE age_of_adult(IN p_country_code CHAR(2),
                                  OUT p_age INTERVAL YEAR)
   -- Given a Country code, returns the age at which a person is considered to
   -- be an adult
   DETERMINISTIC
   CONTAINS SQL
   BEGIN
      CASE p_country_code
         WHEN 'US' THEN SET p_age = INTERVAL '21' YEAR;  -- America
         WHEN 'TR' THEN SET p_age = INTERVAL '15' YEAR;  -- Turkey
         ELSE SET p_age = INTERVAL '18' YEAR;
      END CASE;
   END;  -- of routine age_of_adult


   DECLARE FUNCTION capitalize(p_name VARCHAR(200)) RETURNS VARCHAR(200)
   -- Changes the initial letter of each word to uppercase
   -- and the remaining letters to lowercase
   DETERMINISTIC
   BEGIN
      DECLARE v_current_char, v_previous_char CHAR DEFAULT ' ';
      DECLARE v_offset, v_word_offset INTEGER DEFAULT 1;
      DECLARE v_text VARCHAR(200);

      SET v_text = LOWER(p_name);

skip_lead_chars:
      WHILE v_offset <= CHAR_LENGTH(v_text) DO
         SET v_current_char = SUBSTRING(v_text FROM v_offset FOR 1);
         
         IF v_current_char BETWEEN 'a' AND 'z' THEN
            LEAVE skip_lead_chars;
         END IF;
         
         SET v_offset = v_offset + 1;
      END WHILE;
      
      WHILE v_offset <= CHAR_LENGTH(v_text) DO
         SET v_current_char = SUBSTRING(v_text FROM v_offset FOR 1);
         IF v_previous_char = ' '
         OR v_previous_char = '(' THEN
            SET v_text = PASTE(v_text, v_offset, 1, UPPER(v_current_char));

            SET v_word_offset = v_offset;
         END IF;

         IF v_current_char = '.'
         AND v_offset - v_word_offset = 1 THEN
            SET v_text = PASTE(v_text, v_offset-1, 1, UPPER(v_previous_char));

            SET v_word_offset = v_offset+1;
         END IF;

         SET v_previous_char = v_current_char;

         SET v_offset = v_offset + 1;
      END WHILE;

      RETURN v_text;
   END;  -- of routine capitalize


   DECLARE FUNCTION cast_to_date(p_date VARCHAR(10)) RETURNS DATE
   -- Take a date string (e.g. DD/MM/[YY]YY) and return a date data type
   NOT DETERMINISTIC
   BEGIN
      DECLARE v_date VARCHAR(10);
      DECLARE v_day, v_month CHAR(2);
      DECLARE v_year CHAR(4);
      DECLARE v_offset INTEGER;

      DECLARE date_not_valid CONDITION;

      IF p_date = '' THEN
         RETURN NULL;
      END IF;

      SET v_date = TRIM(p_date);

      -- Treat '*' as a shorthand form for today
      IF v_date LIKE '*%' THEN
         IF v_date = '*' THEN
            RETURN CURRENT_DATE;
         ELSE
            -- Add or subtract a number of days from today
            RETURN CURRENT_DATE
                   + CAST(SUBSTRING(v_date FROM 2) AS INTERVAL DAY(2));
         END IF;
      END IF;

      SET v_date = REPLACE(v_date, '/', '-');
      SET v_date = REPLACE(v_date, '.', '-');

      -- Extract day
      SET v_offset = POSITION('-' IN v_date) - 1;
      IF v_offset <= 0 OR v_offset >= 3 THEN
         SIGNAL date_not_valid;
      END IF;
      SET v_day = PASTE('00', 3-v_offset, v_offset,
                        SUBSTRING(v_date FROM 1 FOR v_offset));
      SET v_date = SUBSTRING(v_date FROM v_offset+2);

      -- Extract month
      SET v_offset = POSITION('-' IN v_date) - 1;
      IF v_offset <= 0 OR v_offset >= 3 THEN
         SIGNAL date_not_valid;
      END IF;
      SET v_month = PASTE('00', 3-v_offset, v_offset,
                          SUBSTRING(v_date FROM 1 FOR v_offset));
      SET v_date = SUBSTRING(v_date FROM v_offset+2);

      -- Extract year
      SET v_offset = CHAR_LENGTH(v_date);
      IF v_offset <= 0 OR v_offset = 3 OR v_offset >= 5 THEN
         SIGNAL date_not_valid;
      END IF;
      SET v_year = PASTE(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS CHAR(4)),
                         5-v_offset, v_offset, v_date);

      RETURN CAST(v_year || '-' || v_month || '-' || v_day AS DATE);
   END;  -- of routine cast_to_date


   DECLARE FUNCTION ean_check_digit(p_ean BIGINT) RETURNS INTEGER
   -- Return the check digit for an EAN (European Article Number)
   DETERMINISTIC
   BEGIN
      DECLARE v_check_digit, v_check_sum INTEGER DEFAULT 0;
      DECLARE v_ean BIGINT;
      DECLARE v_index INTEGER DEFAULT 1;

      SET v_ean = p_ean;

      WHILE v_index <= 12 DO
         SET v_check_sum = v_check_sum + (MOD(v_ean, 10) * 3)
                           + MOD(v_ean / 10, 10);
         SET v_ean = v_ean / 100;
         SET v_index = v_index + 2;
      END WHILE;

      SET v_check_digit = 10 - MOD(v_check_sum, 10);
      IF v_check_digit = 10 THEN
         SET v_check_digit = 0;
      END IF;

      RETURN v_check_digit;
   END;  -- of routine ean_check_digit


   DECLARE FUNCTION extract_datetime(p_datetime VARCHAR(40))
      RETURNS VARCHAR(29)
   -- Returns the datetime/interval details from a cast input value
   DETERMINISTIC
   BEGIN
      DECLARE v_datetime VARCHAR(40) DEFAULT ' ';

      IF p_datetime IS NOT NULL THEN
         SET v_datetime = SUBSTRING(p_datetime
                                    FROM POSITION('''' IN p_datetime)+1);
         SET v_datetime = SUBSTRING(v_datetime
                                    FROM 1 FOR POSITION('''' IN v_datetime)-1);

         IF p_datetime LIKE 'DATE''%' THEN
            SET v_datetime = SUBSTRING(v_datetime FROM 9 FOR 2) || '/' ||
                             SUBSTRING(v_datetime FROM 6 FOR 2) || '/' ||
                             SUBSTRING(v_datetime FROM 1 FOR 4);
         END IF;
      END IF;

      RETURN v_datetime;
   END;  -- of routine extract_datetime


   DECLARE FUNCTION extract_date(p_date DATE) RETURNS CHAR(10)
   -- Returns the input date as a character string (DD/MM/YYYY)
   DETERMINISTIC
   BEGIN
      RETURN CAST(extract_datetime(CAST(p_date AS CHAR(16))) AS CHAR(10));
   END;  -- of routine extract_date


   DECLARE FUNCTION index_text(p_text VARCHAR(200)) RETURNS VARCHAR(200)
   -- Removes all punctuation characters
   -- and strips out any words held in the table STOP_WORDS
   READS SQL DATA
   BEGIN
      DECLARE v_char CHAR;
      DECLARE v_index_text, v_text VARCHAR(201) DEFAULT '';
      DECLARE v_offset INTEGER;
      DECLARE v_punctuation CHAR(31) DEFAULT '!"#$%&()*+,-./:;<=>?@[\]^_`{|}~';

      SET v_text = TRIM(p_text);

      -- Change all punctuation characters to a space (excluding single quote)
      SET v_offset = CHARACTER_LENGTH(v_text);
      WHILE v_offset > 0 DO
         SET v_char = SUBSTRING(v_text FROM v_offset FOR 1);
         IF POSITION(v_char IN v_punctuation) <> 0 THEN
            SET v_text = PASTE(v_text, v_offset, 1, ' ');
         END IF;

         SET v_offset = v_offset - 1;
      END WHILE;

      -- Replace all occurences of double space with a single space,
      -- has to be done twice
      SET v_text = REPLACE(v_text, '  ', ' ');
      SET v_text = REPLACE(v_text, '  ', ' ');
      SET v_text = TRIM(v_text) || ' ';

   scan:
      LOOP
         -- Strip out all words in the stop list
         SET v_offset = POSITION(' ' IN v_text);

         IF v_offset = 0 THEN LEAVE scan; END IF;

         IF NOT EXISTS(SELECT 1
                          FROM stop_words
                          WHERE word = SUBSTRING(v_text FROM 1
                                                 FOR v_offset-1)) THEN
            SET v_index_text = v_index_text
                               || SUBSTRING(v_text FROM 1 FOR v_offset);
         END IF;

         SET v_text = SUBSTRING(v_text FROM v_offset+1);
      END LOOP scan;

      RETURN TRIM(v_index_text);
   END;  -- of routine index_text


   DECLARE FUNCTION product_search_code(p_name VARCHAR(48)) RETURNS CHAR(6)
   -- Forms the Soundex code for the input,
   -- after all common words have been stripped out
   READS SQL DATA
   BEGIN
      DECLARE v_code VARCHAR(48);

      SET v_code = SOUNDEX(index_text(p_name));
      IF v_code = '000000' THEN
         SET v_code = SOUNDEX(p_name);
      END IF;

      RETURN v_code;
   END;  -- of routine product_search_code


   DECLARE FUNCTION recipient(p_title VARCHAR(6),
                              p_forename VARCHAR(24),
                              p_surname VARCHAR(48)) RETURNS VARCHAR(48)
   -- Creates a capitalized string of the form [<title> ][<initial> ]<surname>
   DETERMINISTIC
   BEGIN
      DECLARE v_recipient VARCHAR(60) DEFAULT '';

      IF p_title IS NOT NULL
      AND p_title <> '' THEN
         SET v_recipient = capitalize(TRIM(p_title)) || ' ';
      END IF;

      IF p_forename <> '' THEN
         SET v_recipient = v_recipient
                           || UPPER(SUBSTRING(TRIM(p_forename) FROM 1 FOR 1))
                           || ' ';
      END IF;

      SET v_recipient = v_recipient || capitalize(TRIM(p_surname));

      RETURN SUBSTRING(v_recipient FROM 1 FOR 48);
   END;  -- of routine recipient


   DECLARE FUNCTION salutation(p_title VARCHAR(6), p_forename VARCHAR(24),
                               p_surname VARCHAR(48), p_dob DATE,
                               p_country_code CHAR(2)) RETURNS VARCHAR(48)
   -- Determines the form of address depending on the persons age
   BEGIN
      DECLARE v_age INTERVAL YEAR;
      DECLARE v_salutation VARCHAR(60);

      IF p_forename <> '' THEN
         CALL age_of_adult(p_country_code, v_age);

         IF p_dob > CURRENT_DATE - v_age THEN
            SET v_salutation = TRIM(p_forename);
         END IF;
      END IF;

      IF v_salutation IS NULL THEN
         IF p_title <> '' THEN
            SET v_salutation = p_title;
         ELSE
            SET v_salutation = 'Mr/Ms';
         END IF;

         SET v_salutation = TRIM(v_salutation) || ' ' || TRIM(p_surname);
      END IF;

      RETURN SUBSTRING(capitalize(v_salutation) FROM 1 FOR 48);
   END;  -- of routine salutation


   DECLARE PROCEDURE stop_words(p_text VARCHAR(200))
   -- Inserts entries in the STOP_WORDS table
   DETERMINISTIC
   MODIFIES SQL DATA
   BEGIN
      DECLARE v_offset INTEGER;
      DECLARE v_text VARCHAR(201);

      SET v_text = REPLACE(TRIM(p_text), '  ', ' ');
      SET v_text = REPLACE(v_text, '  ', ' ')  || ' ';

   scan:
      LOOP
         -- Extract words from list
         SET v_offset = POSITION(' ' IN v_text);

         IF v_offset = 0 THEN LEAVE scan; END IF;

         INSERT INTO stop_words(word)
            VALUES (UPPER(SUBSTRING(v_text FROM 1 FOR v_offset-1)));

         SET v_text = SUBSTRING(v_text FROM v_offset+1);
      END LOOP;
   END;  -- of routine stop_words


   DECLARE FUNCTION validate_ean_code(p_barcode BIGINT) RETURNS BIGINT
   -- Returns the EAN with the correct check digit
   DETERMINISTIC
   BEGIN
      -- The Universal Product Code (UPC) is a subset
      -- of the more general European Article Numbering
      DECLARE v_ean BIGINT;
      DECLARE v_type_e INTEGER;

      IF p_barcode > 0 THEN
         SET v_ean = p_barcode / 10;  -- Strip off check digit

         IF v_ean <= 999999 THEN
            -- The UPC Type E is only seven digits (most significant is zero)
            -- plus the check digit. Transform Type E to Type A
            SET v_type_e = v_ean / 10;
            CASE MOD(v_ean, 10)
               WHEN 0 THEN SET v_ean = (v_type_e / 1000) * 100000000
                                       + MOD(v_type_e, 1000);
               WHEN 1 THEN SET v_ean = (((v_type_e / 1000) * 10) + 1) * 10000000
                                       + MOD(v_type_e, 1000);
               WHEN 2 THEN SET v_ean = (((v_type_e / 1000) * 10) + 2) * 10000000
                                       + MOD(v_type_e, 1000);
               WHEN 3 THEN SET v_ean = (v_type_e / 100) * 10000000
                                       + MOD(v_type_e, 100);
               WHEN 4 THEN SET v_ean = (v_type_e / 10) * 1000000
                                       + MOD(v_type_e, 10);
               ELSE SET v_ean = v_type_e * 100000 + MOD(v_ean, 10);
            END CASE;
         END IF;

         RETURN (v_ean * 10) + ean_check_digit(v_ean);
      END IF;

      RETURN 1;  -- Input invalid
   END;  -- of routine validate_ean_code

END MODULE  -- routines
@


--==============================================================
-- Add a check constraint on the ITEMS table that uses a PSM
-- function.
--==============================================================

-- *****ALTER TABLE items ADD
-- *****   -- Ensure that the EAN is valid
-- *****   -- and prevent UPC Type E codes being stored on the database
-- *****   CONSTRAINT itm_ean_code_illegal
-- *****      CHECK (mimer_store.validate_ean_code(ean_code) = ean_code); -- ***** remove MIMER_STORE.


--==============================================================
-- Create views
-- Create a view based on a single base table.
--==============================================================

CREATE VIEW customer_details
   AS SELECT surname, forename, address_1, address_2, town, postcode,
             title, date_of_birth, country_code, customer_id
   FROM customers;


--==============================================================
-- Create a view based on a view; includes 'with check option'.
--==============================================================

CREATE VIEW swedish_customers
   AS SELECT *
   FROM customer_details
   WHERE country_code = 'SE'
   WITH CHECK OPTION;


--==============================================================
-- Create a join view that uses PSM functions.
--==============================================================

CREATE VIEW customer_addresses (
      surname,
      forename,
      recipient,
      address_1,
      address_2,
      town,
      postcode,
      country,
      salutation,
      customer_id)
   AS SELECT
      surname,
      forename,
      UPPER(recipient(title, forename, surname)),
      address_1,
      COALESCE(address_2, ' '),
      UPPER(town),
      UPPER(postcode),
      UPPER(country),
      salutation(title, forename, surname, date_of_birth, country_code),
      customer_id
   FROM customer_details
   JOIN countries ON code = country_code;


--==============================================================
-- Create a view based on a number of tables, including an outer
-- join.
--==============================================================

CREATE VIEW product_details
   AS SELECT product, COALESCE(producer, ' ') AS producer, format, price, stock,
             reorder_level, release_date, ean_code, status, product_search,
             item_id, category_id, product_id, display_order, image_id
   FROM products
   NATURAL JOIN items
   NATURAL JOIN formats
   NATURAL LEFT OUTER JOIN producers;


--==============================================================
-- Create a PSM procedure outside of a module.
--==============================================================

@
CREATE PROCEDURE coming_soon(IN p_category VARCHAR(20))
-- Result set procedure that returns items that will be released
-- in the next month
VALUES (VARCHAR(48), VARCHAR(48), VARCHAR(20), CHAR(10), NUMERIC(7, 2), INTEGER)
AS (product, producer, format, release_date, price, item_id)
READS SQL DATA
BEGIN
   DECLARE row_values ROW (product VARCHAR(48), producer VARCHAR(48),
                           format VARCHAR(20), release_date CHAR(10),
                           price NUMERIC(7, 2), item_id INTEGER);
   DECLARE v_sqlstate CHAR(5);

   DECLARE c_1 CURSOR FOR SELECT product, producer, format,
                                 extract_date(release_date), price, item_id
                             FROM product_details
                             NATURAL JOIN categories
                             WHERE category = p_category
                             AND release_date BETWEEN CURRENT_DATE
                                                      + INTERVAL '1' DAY
                                              AND CURRENT_DATE
                                                  + INTERVAL '1' MONTH;

   DECLARE category_not_allocated CONDITION;

   IF NOT EXISTS(SELECT 1
                    FROM categories
                    WHERE category = p_category) THEN
      SIGNAL category_not_allocated;
   END IF;

   OPEN c_1;

read_loop:
   LOOP
      FETCH c_1
         INTO row_values;

      -- Check for 'NOT FOUND'
      GET DIAGNOSTICS EXCEPTION 1 v_sqlstate = RETURNED_SQLSTATE;
      IF v_sqlstate = '02000' THEN LEAVE read_loop; END IF;

      RETURN row_values;  -- Return data to caller one row at a time
   END LOOP read_loop;

   CLOSE c_1;
END  -- of routine coming_soon
@


--==============================================================
-- Create statement triggers for schema MIMER_STORE.
--==============================================================

@
CREATE TRIGGER products_after_insert
   AFTER INSERT ON products
REFERENCING NEW TABLE AS pdt
FOR EACH STATEMENT
BEGIN ATOMIC
   -- Force the update trigger to fire
   declare a cursor for select product_id from pdt;
   declare a int;
   declare exit handler for not found begin end;
   open a;
   
   loop
       fetch a into a;
       UPDATE products
          SET product_search = DEFAULT
        WHERE product_id = a;
   end loop;
   close a;
END  -- of trigger products_after_insert
@


@
CREATE TRIGGER products_after_update
   AFTER UPDATE ON products
REFERENCING NEW TABLE AS pdt
begin atomic
-- Note that this trigger is called recursively.
-- An update statement causes the trigger to fire even when
-- no rows are updated, hence the 'IF EXISTS' statement
   declare a cursor for select product_id from pdt p
                         WHERE exists (select 1 from products
                             where (product_search <> product_search_code(p.product)
                            OR product <> capitalize(TRIM(p.product)))
                          and product_id = p.product_id);
   declare a int;
   declare exit handler for not found begin end;
   open a;
   
   loop
       fetch a into a;
       UPDATE products
          SET product_search = product_search_code(product)
        WHERE product_id = a;
   end loop;
   close a;
end
@

--==============================================================
-- Create idents and grant privileges
-- on tables, views and routines in the MIMER_STORE schema.
--==============================================================

CREATE IDENT mimer_adm AS USER USING 'adm';
GRANT IDENT TO mimer_adm;
GRANT TABLE ON mimer_store TO mimer_adm;

CREATE IDENT mimer_admin_group AS GROUP;
GRANT MEMBER ON mimer_admin_group TO mimer_adm WITH GRANT OPTION;

GRANT SELECT, INSERT ON categories TO mimer_admin_group;
GRANT SELECT, INSERT, UPDATE, DELETE ON countries TO mimer_admin_group;
GRANT SELECT, UPDATE(exchange_rate) ON currencies TO mimer_admin_group;
GRANT SELECT, INSERT ON customers TO mimer_admin_group;
GRANT SELECT, UPDATE(display_order), INSERT ON formats TO mimer_admin_group;
GRANT SELECT ON images TO mimer_admin_group;
GRANT SELECT ON items TO mimer_admin_group;
GRANT SELECT, INSERT, UPDATE, DELETE ON order_items TO mimer_admin_group;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO mimer_admin_group;
GRANT SELECT, INSERT, UPDATE ON producers TO mimer_admin_group;
GRANT SELECT ON products TO mimer_admin_group;
GRANT SELECT ON stop_words TO mimer_admin_group;

GRANT SELECT ON customer_addresses TO mimer_admin_group;
GRANT SELECT, INSERT, UPDATE, DELETE ON customer_details TO mimer_admin_group;
GRANT SELECT ON product_details TO mimer_admin_group;
GRANT SELECT, INSERT, UPDATE, DELETE ON swedish_customers TO mimer_admin_group;

GRANT USAGE ON DOMAIN name TO mimer_admin_group;
GRANT USAGE ON SEQUENCE customer_id_seq TO mimer_admin_group;
GRANT USAGE ON SEQUENCE order_id_seq TO mimer_admin_group;
GRANT USAGE ON SEQUENCE producer_id_seq TO mimer_admin_group;

GRANT EXECUTE ON PROCEDURE age_of_adult TO mimer_admin_group;
GRANT EXECUTE ON FUNCTION capitalize TO mimer_admin_group;
GRANT EXECUTE ON FUNCTION cast_to_date TO mimer_admin_group;
GRANT EXECUTE ON FUNCTION extract_datetime TO mimer_admin_group;
GRANT EXECUTE ON FUNCTION extract_date TO mimer_admin_group;
GRANT EXECUTE ON FUNCTION index_text TO mimer_admin_group;
GRANT EXECUTE ON FUNCTION recipient TO mimer_admin_group;
GRANT EXECUTE ON FUNCTION salutation TO mimer_admin_group;
GRANT EXECUTE ON FUNCTION validate_ean_code TO mimer_admin_group;

CREATE IDENT mimer_usr AS USER USING 'usr';

CREATE IDENT mimer_store_group AS GROUP;
GRANT MEMBER ON mimer_store_group TO mimer_usr;
GRANT MEMBER ON mimer_store_group TO mimer_admin_group WITH GRANT OPTION;

GRANT EXECUTE ON PROCEDURE coming_soon TO mimer_store_group;


--**************************************************************
--#Creating the MIMER_STORE_MUSIC schema
-- Owned by the current ident.
--
-- The order that objects are created within a 'create schema'
-- statement is irrelevant (e.g. an object does not need to be
-- created before it can be referenced).
--
-- The full name for any object is <schema_name>.<object_name>
-- e.g. MIMER_STORE_MUSIC.DURATION
--**************************************************************

@
CREATE SCHEMA mimer_store_music
   --==============================================================
   -- Create domains.
   --==============================================================

   CREATE DOMAIN duration AS INTERVAL MINUTE(2) TO SECOND(0)
   CREATE DOMAIN track_no AS SMALLINT


   --==============================================================
   -- Create sequences.
   --==============================================================

   CREATE UNIQUE SEQUENCE artist_id_seq INITIAL_VALUE = 50001


   --==============================================================
   -- Create tables.
   --==============================================================

   CREATE TABLE artists (
         artist mimer_store.name CONSTRAINT art_artist_illegal
                                    CHECK (artist <> ''),
         artist_id mimer_store.internal_id DEFAULT NEXT_VALUE OF artist_id_seq
                                           CONSTRAINT art_artist_id_is_null
                                              NOT NULL,
         artist_search mimer_store.soundex,
         CONSTRAINT art_primary_key  PRIMARY KEY (artist),
         CONSTRAINT art_artist_id_exists UNIQUE (artist_id))
      IN mimer_store

   CREATE INDEX art_artist_search ON artists(artist_search)
   CREATE INDEX art_artist on artists(artist COLLATE english)


   CREATE TABLE titles (
         item_id mimer_store.internal_id,
         artist_id mimer_store.internal_id CONSTRAINT mtl_artist_id_is_null
                                              NOT NULL,
         CONSTRAINT mtl_primary_key PRIMARY KEY (item_id),
         CONSTRAINT mtl_items FOREIGN KEY (item_id) REFERENCES mimer_store.items
            ON DELETE CASCADE ON UPDATE NO ACTION,
         CONSTRAINT mtl_artists
            FOREIGN KEY (artist_id) REFERENCES artists(artist_id)
            ON DELETE NO ACTION ON UPDATE NO ACTION)
      IN mimer_store


   CREATE TABLE tracks (
         item_id mimer_store.internal_id,
         track_no track_no,
         track mimer_store.name CONSTRAINT trc_name_is_null NOT NULL,
         length duration DEFAULT NULL
                         CONSTRAINT trc_length_illegal
                         CHECK (length BETWEEN INTERVAL '00:01' MINUTE TO SECOND
                                AND INTERVAL '60:00' MINUTE TO SECOND),
         sample_id mimer_store.internal_id DEFAULT NULL,
         CONSTRAINT trc_primary_key PRIMARY KEY (item_id, track_no),
         CONSTRAINT trc_items FOREIGN KEY (item_id) REFERENCES mimer_store.items
            ON DELETE CASCADE ON UPDATE NO ACTION,
         CONSTRAINT trc_samples FOREIGN KEY (sample_id) REFERENCES samples
            ON DELETE SET DEFAULT ON UPDATE NO ACTION)
      IN mimer_store


   CREATE TABLE samples (
         sample_id mimer_store.internal_id DEFAULT NEXT_VALUE OF
                                              mimer_store.object_id_seq,
         sample BLOB(1M) CONSTRAINT smp_sample_is_null NOT NULL,
         CONSTRAINT smp_primary_key PRIMARY KEY (sample_id))
      IN mimer_blobs


   --==============================================================
   -- Create a view based on tables from different schemas.
   -- The result set is dependent on the ident using the view.
   --==============================================================

   CREATE VIEW details
      AS SELECT
         artist, ptd.*,
         item_id,  -- Note that ITEM_ID has to be explicitly included
         artist_search, artist_id
      FROM mimer_store.product_details AS ptd
      NATURAL JOIN mimer_store_music.titles  -- *****
      NATURAL JOIN mimer_store_music.artists  -- *****
      WHERE session_user <> 'MIMER_WEB'
      OR (session_user = 'MIMER_WEB' AND format NOT IN ('Vinyl'))


   --==============================================================
   -- Create a view based on the previous view and include
   -- additional select restrictions.
   --==============================================================

   CREATE VIEW search
      AS SELECT
         product AS title, artist, producer AS label, format,
         mimer_store.extract_date(release_date) AS release_date,
         price, image_id,
         product_search title_search, artist_search,
         item_id, artist_id, product_id, display_order
      FROM mimer_store_music.details  -- *****
      WHERE status = 'A'
      OR (status = 'X' AND stock >= 1)


   --==============================================================
   -- Grant privileges on tables and views in the MIMER_STORE_MUSIC
   -- schema.
   --==============================================================

   GRANT SELECT, UPDATE ON artists TO mimer_admin_group
   GRANT SELECT ON details TO mimer_admin_group
   GRANT SELECT ON samples TO mimer_admin_group
   GRANT SELECT ON search TO mimer_admin_group
   GRANT SELECT, UPDATE ON titles TO mimer_admin_group
   GRANT SELECT, UPDATE ON tracks TO mimer_admin_group

-- end of schema mimer_store_music
@


--==============================================================
-- Create MIMER_STORE_MUSIC PSM routines in a module.
--
-- Note that the PSM routine names have been capitalized rather
-- than the parts separated by an underscore; the name is stored
-- in the Data Dictionary as uppercase.
--==============================================================

@
CREATE MODULE Mimer_Store_Music.Routines
   DECLARE PROCEDURE AddTitle(IN p_MusicTitle VARCHAR(48),
                              IN p_RecordedBy VARCHAR(48),
                              IN p_Label VARCHAR(48),
                              IN p_Format VARCHAR(20),
                              IN p_DateReleased VARCHAR(10),
                              IN p_Price NUMERIC(7, 2),
                              IN p_StockLevel SMALLINT,
                              IN p_ReorderPoint SMALLINT,
                              IN p_EAN BIGINT)
   -- Add the details for a music entity
   MODIFIES SQL DATA
   BEGIN
      DECLARE Art ROW AS (Mimer_Store_Music.Artists(Artist_ID));
      DECLARE Fmt ROW AS (Mimer_Store.Formats(Format_ID));
      DECLARE Itm ROW AS (Mimer_Store.Items(Item_ID));
      DECLARE Pdt ROW AS (Mimer_Store.Products(Product_ID));
      DECLARE Prd ROW AS (Mimer_Store.Producers(Producer_ID));

      DECLARE Music_format_not_valid CONDITION;
      DECLARE Record_label_not_allocated CONDITION;

      BEGIN
         DECLARE EXIT HANDLER FOR NOT FOUND
            SIGNAL Music_format_not_valid;

         -- Check that format is valid for the Music category
         SELECT Format_ID
            INTO Fmt
            FROM Mimer_Store.Formats
            NATURAL JOIN Mimer_Store.Categories
            WHERE Format = TRIM(p_Format)
            AND Category = 'Music';
      END;

      BEGIN
         DECLARE EXIT HANDLER FOR NOT FOUND
            SIGNAL Record_label_not_allocated;

         IF p_Label <> '' THEN
            -- Check that the label is known
            SELECT Producer_ID
               INTO Prd
               FROM Mimer_Store.Producers
               WHERE Producer = TRIM(p_Label);
         END IF;
      END;

      BEGIN
         DECLARE CONTINUE HANDLER FOR NOT FOUND
         BEGIN
            INSERT INTO Mimer_Store.Products(Product)
               VALUES (p_MusicTitle);

            SET Pdt.Product_ID = CURRENT_VALUE OF
                                    Mimer_Store.Product_ID_Seq;
         END;  -- of not found handler
               
         SELECT Product_ID
            INTO Pdt
            FROM Mimer_Store.Products
            WHERE Product = TRIM(p_MusicTitle);
      END;

      BEGIN
         DECLARE EXIT HANDLER FOR SQLEXCEPTION
         BEGIN
            SELECT Artist_ID
               INTO Art
               FROM Mimer_Store_Music.Artists
               WHERE Artist = TRIM(p_RecordedBy);
         END;  -- of not found handler

         INSERT INTO Mimer_Store_Music.Artists(Artist)
            VALUES (p_RecordedBy);
            
         SET Art.Artist_ID = CURRENT_VALUE OF
                                Mimer_Store_Music.Artist_ID_Seq;
      END;

      INSERT INTO Mimer_Store.Items(Item_ID, Product_ID, Format_ID,
                                    Release_Date,
                                    Price, Stock, Reorder_Level,
                                    EAN_Code, Producer_ID)
         VALUES (DEFAULT, Pdt.Product_ID, Fmt.Format_ID,
                 Mimer_Store.Cast_To_Date(p_DateReleased),
                 p_Price, p_StockLevel, p_ReorderPoint,
                 p_EAN, Prd.Producer_ID);

      INSERT INTO Mimer_Store_Music.Titles(Item_ID, Artist_ID)
         VALUES (CURRENT_VALUE OF Mimer_Store.Item_ID_Seq, Art.Artist_ID);
   END;  -- of routine AddTitle


   DECLARE PROCEDURE AddTrack(IN p_EAN BIGINT, IN p_TrackNumber SMALLINT,
                              IN p_Name VARCHAR(48), IN p_Length VARCHAR(5))
   -- Add the track details
   MODIFIES SQL DATA
   BEGIN
      DECLARE Itm ROW AS (Mimer_Store.Items(Item_ID));

      DECLARE v_Duration INTERVAL MINUTE TO SECOND(0);

      DECLARE EAN_not_valid CONDITION;
      DECLARE Length_not_valid CONDITION;

      DECLARE EXIT HANDLER FOR SQLSTATE VALUE '22006',-- Invalid interval format
                               SQLSTATE VALUE '22015' -- Interval field overflow
      BEGIN
         SIGNAL Length_not_valid;
      END;  -- of sqlstate handler

      BEGIN
         DECLARE EXIT HANDLER FOR NOT FOUND
         BEGIN
            SIGNAL EAN_not_valid;
         END;  -- of not found handler

         -- Check that EAN is valid for the Music category
         SELECT Item_ID
            INTO Itm
            FROM Mimer_Store.Items
            NATURAL JOIN Mimer_Store.Formats
            NATURAL JOIN Mimer_Store.Categories
            WHERE EAN_code = p_EAN
            AND Category = 'Music';
      END;

      BEGIN
         DECLARE Constraint_violation CONDITION FOR SQLSTATE '23000';

         DECLARE EXIT HANDLER FOR Constraint_violation
         BEGIN
            DECLARE v_Name VARCHAR(128);

            GET DIAGNOSTICS EXCEPTION 1 v_Name = CONSTRAINT_NAME;

            IF v_Name NOT LIKE '___\_PRIMARY\_KEY' ESCAPE '\' THEN
               -- Constraint violation other than duplicate row
               RESIGNAL;
            END IF;
         END; -- of constraint violation handler

         IF p_Length <> '' THEN
            SET v_Duration = CAST(p_Length AS INTERVAL MINUTE TO SECOND);
         END IF;

         INSERT INTO Mimer_Store_Music.Tracks(Item_ID, Track_No, Track, Length)
            VALUES (Itm.Item_ID, p_TrackNumber, TRIM(p_Name), v_Duration);
      END;
   END;  -- of routine AddTrack


   DECLARE FUNCTION ArtistName(p_Name VARCHAR(48)) RETURNS VARCHAR(48)
   -- Returns the name with any leading definite or indefinite articles removed
   DETERMINISTIC
   BEGIN
      DECLARE v_Offset INTEGER;
      DECLARE v_Name VARCHAR(48);

      SET v_Name = TRIM(p_Name);

      -- Strip off any use of a leading definite article ('the')
      -- or the indefinite articles ('a' & 'an')
      SET v_Offset = POSITION(' ' IN v_Name);
      IF POSITION(SUBSTRING(v_Name FROM 1 FOR v_Offset) IN 'A AN THE ') > 0
      AND SUBSTRING(v_Name FROM v_Offset+1) <> '' THEN
         SET v_Name = SUBSTRING(v_Name FROM v_Offset+1);
      END IF;

      RETURN v_Name;
   END;  -- of routine ArtistName


   DECLARE FUNCTION ArtistSearchCode(p_Name VARCHAR(48)) RETURNS CHAR(6)
   -- Form the Soundex value for a name
   DETERMINISTIC
   BEGIN
      RETURN SOUNDEX(Mimer_Store_Music.ArtistName(p_Name));
   END;  -- of routine ArtistSearchCode


   DECLARE PROCEDURE Search(IN p_MusicTitle VARCHAR(48),
                            IN p_RecordedBy VARCHAR(48),
                            IN p_MaxRows INTEGER)
   -- Search the Music database for matches. MAXROWS limits the maximum number
   -- of rows returned (a value of zero suppresses this feature).
   -- The search involves a number of levels of match and grades the row with a
   -- star rating to indicate the level of match
   VALUES (VARCHAR(48), VARCHAR(48), VARCHAR(20), NUMERIC(7, 2), INTEGER,
           INTEGER, CHAR(4))
   AS (Title, Artist, Format, Price, Item_ID,
       Artist_ID, Match_Level)
   READS SQL DATA
   SearchProcedure:
   BEGIN
      DECLARE v_ArtistCode, v_TitleCode VARCHAR(7);
      DECLARE v_MatchLevel CHAR(4);
      DECLARE Data ROW AS (Mimer_Store_Music.Search(Title, Artist, Format,
                                                    Price, Item_ID, Artist_ID,
                                                    Title_Search, Artist_Search,
                                                    Product_ID, Display_Order));
      DECLARE v_RowCount INTEGER DEFAULT 0;

      DECLARE c_1 CURSOR FOR SELECT Title, Artist, Format, Price,
                                    Item_ID, Artist_ID,
                                    Title_Search, Artist_Search,
                                    Product_ID, Display_Order
                                FROM Mimer_Store_Music.Search
                                WHERE Title LIKE TRIM(LEADING FROM p_MusicTitle)
                                      || '%'
                                AND Artist LIKE TRIM(LEADING FROM p_RecordedBy)
                                    || '%'
                                ORDER BY Title, Artist, Product_ID,
                                         Display_Order;

      DECLARE c_2 CURSOR FOR SELECT Title, Artist, Format, Price,
                                    Item_ID, Artist_ID,
                                    Title_Search, Artist_Search,
                                    Product_ID, Display_Order
                                FROM Mimer_Store_Music.Search
                                WHERE Title_Search LIKE v_TitleCode
                                AND Artist_Search LIKE v_ArtistCode
                                ORDER BY Title, Artist, Product_ID,
                                         Display_Order;

   ExactMatch:
      BEGIN
         DECLARE EXIT HANDLER FOR NOT FOUND
            CLOSE c_1;

         OPEN c_1;

         LOOP
            FETCH c_1
               INTO Data;

            RETURN (Data.Title, Data.Artist, Data.Format, Data.Price,
                    Data.Item_ID, Data.Artist_ID, '****');

            SET v_RowCount = v_RowCount + 1;

            IF p_MaxRows > 0
            AND v_RowCount >= p_MaxRows THEN
               LEAVE SearchProcedure;
            END IF;
         END LOOP;
      END ExactMatch;

      IF v_RowCount <> 0 THEN LEAVE SearchProcedure; END IF;

   CloseMatch:
      BEGIN
         DECLARE EXIT HANDLER FOR NOT FOUND
            CLOSE c_2;

         SET v_TitleCode = Mimer_Store.Product_Search_Code(p_MusicTitle);
         SET v_ArtistCode = Mimer_Store_Music.ArtistSearchCode(p_RecordedBy);

         OPEN c_2;

         LOOP
            FETCH c_2
               INTO Data;

            RETURN (Data.Title, Data.Artist, Data.Format, Data.Price,
                    Data.Item_ID, Data.Artist_ID, '***');

            SET v_RowCount = v_RowCount + 1;

            IF p_MaxRows > 0
            AND v_RowCount >= p_MaxRows THEN
               LEAVE CloseMatch;  -- Note that cursor is automatically closed
            END IF;
         END LOOP;
      END CloseMatch;

   FuzzyMatch:
      BEGIN
         DECLARE v_Artist, v_Title VARCHAR(48);
         DECLARE v_ArtistSearch, v_TitleSearch VARCHAR(7);

         DECLARE EXIT HANDLER FOR NOT FOUND
            LEAVE FuzzyMatch;

         SET v_TitleSearch = v_TitleCode;
         SET v_ArtistSearch = v_ArtistCode;

         SET v_TitleCode = TRIM(TRAILING '0' FROM
                              Mimer_Store.Product_Search_Code(p_MusicTitle))
                           || '%';
         SET v_ArtistCode = TRIM(TRAILING '0' FROM
                               Mimer_Store_Music.ArtistSearchCode(p_RecordedBy))
                            || '%';

         IF v_TitleCode = v_TitleSearch
         AND v_ArtistCode = v_ArtistSearch THEN
            LEAVE FuzzyMatch;
         END IF;

         OPEN c_2;

         SET v_Title = Mimer_Store.Index_Text(p_MusicTitle) || '%';
         SET v_Artist = Mimer_Store_Music.ArtistName(p_RecordedBy) || '%';

         LOOP
            FETCH c_2
               INTO Data;

            IF Data.Title_Search <> v_TitleSearch
            OR Data.Artist_Search <> v_ArtistSearch THEN
               IF Mimer_Store.Index_Text(Data.Title) LIKE v_Title
               OR Mimer_Store_Music.ArtistName(Data.Artist) LIKE v_Artist THEN
                  SET v_MatchLevel = '**';
               ELSE
                  SET v_MatchLevel = '*';
               END IF;

               RETURN (Data.Title, Data.Artist, Data.Format, Data.Price,
                       Data.Item_ID, Data.Artist_ID, v_MatchLevel);

               SET v_RowCount = v_RowCount + 1;

               IF p_MaxRows > 0
               AND v_RowCount >= p_MaxRows THEN
                  LEAVE FuzzyMatch;
               END IF;
            END IF;
         END LOOP;
      END FuzzyMatch;
   END SearchProcedure;  -- of routine Search


   DECLARE PROCEDURE TitleDetails(IN p_ItemID INTEGER)
   -- Result set procedure that returns music details
   VALUES (VARCHAR(48), VARCHAR(48), VARCHAR(48), VARCHAR(20), CHAR(10),
           NUMERIC(7, 2), VARCHAR(7), INTEGER, INTEGER)
   AS (Title, Artist, Label, Format, Release_Date,
       Price, Play_Time, Image_ID, Item_ID)
   READS SQL DATA
   OuterLevel:
   BEGIN
      DECLARE Data ROW AS (Mimer_Store_Music.Search(Title, Artist, Label,
                                                    Format, Release_Date, Price,
                                                    Image_ID));
      DECLARE v_PlayTime VARCHAR(7);

      DECLARE Item_identifier_not_valid CONDITION;

      DECLARE EXIT HANDLER FOR NOT FOUND
         SIGNAL Item_identifier_not_valid;

      SELECT Product, Artist, Producer, Format,
             Mimer_Store.Extract_Date(Release_Date), Price, Image_ID
         INTO Data
         FROM Mimer_Store_Music.Details
         NATURAL JOIN Mimer_Store.Categories
         WHERE Item_ID = p_ItemID
         AND Category = 'Music';

   InnerLevel:
      BEGIN
         -- Sum the track times.
         -- Note that SUM(ALL LENGTH) can't be used as SUM discards NULL values
         DECLARE Data ROW AS (Mimer_Store_Music.Tracks(Length));
         DECLARE v_PlayTime INTERVAL MINUTE(4) TO SECOND(0)
                            DEFAULT INTERVAL '0:00' MINUTE TO SECOND;

         DECLARE c_1 CURSOR FOR SELECT Length
                                   FROM Mimer_Store_Music.Tracks
                                   WHERE Item_ID = p_ItemID;

         DECLARE EXIT HANDLER FOR NOT FOUND
         BEGIN
            CLOSE c_1;

            -- Note the use of the compound statement label
            -- to qualify the variable names
            IF InnerLevel.v_PlayTime > INTERVAL '0:00' MINUTE TO SECOND THEN
               SET OuterLevel.v_PlayTime =
                  Mimer_Store.Extract_Datetime(CAST(InnerLevel.v_PlayTime
                                                    AS VARCHAR(40)));
            ELSE
               SET OuterLevel.v_PlayTime = ' ';
            END IF;
         END;  -- of not found handler

         OPEN c_1;

         LOOP
            FETCH c_1
               INTO Data;

            SET v_PlayTime = v_PlayTime + Data.Length;
         END LOOP;
      END InnerLevel;

      RETURN (Data.Title, Data.Artist, Data.Label, Data.Format,
              Data.Release_Date, Data.Price, v_PlayTime, Data.Image_ID,
              p_ItemID);
   END;  -- of routine TitleDetails


   DECLARE PROCEDURE TrackDetails(IN p_ItemID INTEGER)
   -- Result set procedure that returns track details
   VALUES (SMALLINT, VARCHAR(48), VARCHAR(6), INTEGER)
   AS (Track_No, Title, Length, Sample_ID)
   READS SQL DATA
   BEGIN
      DECLARE Data ROW (Track_No SMALLINT, Title VARCHAR(48), Length VARCHAR(6),
                        Sample_ID INTEGER);

      DECLARE c_1 CURSOR FOR SELECT Track_No, Track,
                                    Mimer_Store.Extract_Datetime(
                                       CAST(Length AS VARCHAR(40))),
                                    Sample_ID
                                FROM Mimer_Store_Music.Tracks
                                WHERE Item_ID = p_ItemID
                                ORDER BY Track_No;

      DECLARE EXIT HANDLER FOR NOT FOUND
         CLOSE c_1;

      OPEN c_1;

      IF NOT EXISTS(SELECT 1
                       FROM Mimer_Store.Items
                       WHERE Item_ID = p_ItemID) THEN
         SIGNAL SQLSTATE 'UE001';
      END IF;

      LOOP
         FETCH c_1
            INTO Data;

         RETURN Data;
      END LOOP;
   END;  -- of routine TrackDetails

END MODULE  -- Mimer_Store_Music.Routines
@


--==============================================================
-- Create statement triggers for schema MIMER_STORE_MUSIC.
--==============================================================

@
CREATE TRIGGER mimer_store_music.artists_after_insert
   AFTER INSERT ON mimer_store_music.artists
REFERENCING NEW TABLE AS art
begin atomic
  declare a cursor for select artist_id from art;
  declare a int;
  declare exit handler for not found begin end;
  open a;
  loop
      fetch a into a;
      UPDATE mimer_store_music.artists
         SET artist_search = DEFAULT
       WHERE artist_id = a;
  end loop;
  close a;
end
@


@
CREATE TRIGGER mimer_store_music.artists_after_update
   AFTER UPDATE ON mimer_store_music.artists
REFERENCING NEW TABLE AS art
BEGIN ATOMIC
   DECLARE art ROW AS (mimer_store_music.artists(artist, artist_id,
                                                 artist_search));
   DECLARE src ROW AS (mimer_store_music.artists(artist_search));
   DECLARE c_1 CURSOR FOR SELECT artist, artist_id, artist_search
                             FROM art;

   DECLARE EXIT HANDLER FOR NOT FOUND
      CLOSE c_1;

   OPEN c_1;

art:
   LOOP
      FETCH c_1
         INTO art;

      SET src.artist_search =
          mimer_store_music.ArtistSearchCode(TRIM(art.artist));
      IF art.artist_search <> src.artist_search
      OR art.artist <> TRIM(art.artist) THEN
         UPDATE mimer_store_music.artists
            SET artist_search = src.artist_search,
                artist = TRIM(artist)
            WHERE artist_id = art.artist_id;
      END IF;
   END LOOP art;
END  -- of trigger mimer_store_music.artists_after_update
@


--==============================================================
-- Grant privileges on routines in the MIMER_STORE_MUSIC schema.
--==============================================================

GRANT EXECUTE ON PROCEDURE mimer_store_music.Search TO mimer_store_group;
GRANT EXECUTE ON PROCEDURE mimer_store_music.TitleDetails TO mimer_store_group;
GRANT EXECUTE ON PROCEDURE mimer_store_music.TrackDetails TO mimer_store_group;
GRANT EXECUTE ON PROCEDURE mimer_store_music.AddTitle TO mimer_store_group;
GRANT EXECUTE ON PROCEDURE mimer_store_music.AddTrack TO mimer_store_group;


--==============================================================
-- Create synonyms for the MIMER_STORE_MUSIC schema tables
-- and views.
--==============================================================

CREATE SYNONYM artists FOR mimer_store_music.artists;
CREATE SYNONYM music_details FOR mimer_store_music.details;
CREATE SYNONYM music_search FOR mimer_store_music.search;
CREATE SYNONYM music_titles FOR mimer_store_music.titles;
CREATE SYNONYM samples FOR mimer_store_music.samples;
CREATE SYNONYM tracks FOR mimer_store_music.tracks;


--**************************************************************
--#Creating the MIMER_STORE_BOOK schema
-- Owned by the current ident.
--
-- The full name for any object is <schema_name>.<object_name>
-- e.g. MIMER_STORE_BOOK.TITLES
--**************************************************************

@
CREATE SCHEMA mimer_store_book
   --==============================================================
   -- Create tables.
   --==============================================================

   CREATE TABLE titles (
         item_id mimer_store.internal_id,
         authors_list VARCHAR(128) CONSTRAINT btl_authors_list_is_null NOT NULL,
         isbn CHAR(10),
         CONSTRAINT btl_primary_key PRIMARY KEY (item_id),
         CONSTRAINT btl_isbn_exists UNIQUE (isbn),
         CONSTRAINT btl_items FOREIGN KEY (item_id) REFERENCES mimer_store.items
            ON DELETE CASCADE ON UPDATE NO ACTION)
      IN mimer_store


   CREATE TABLE authors (
         keyword_id mimer_store.internal_id,
         item_id mimer_store.internal_id,
         CONSTRAINT ath_primary_key PRIMARY KEY (keyword_id, item_id),
         CONSTRAINT ath_keywords
             FOREIGN KEY (keyword_id) REFERENCES keywords(keyword_id)
             ON DELETE NO ACTION ON UPDATE NO ACTION,
         CONSTRAINT ath_items FOREIGN KEY (item_id) REFERENCES mimer_store.items
            ON DELETE CASCADE ON UPDATE NO ACTION)
      IN mimer_store


   CREATE TABLE keywords (
         keyword mimer_store.name CONSTRAINT kyw_keyword_illegal
                                     CHECK (keyword <> ''),
         keyword_id mimer_store.internal_id DEFAULT NEXT_VALUE OF
                                               keyword_id_seq,
         CONSTRAINT kyw_primary_key PRIMARY KEY (keyword),
         CONSTRAINT kyw_keyword_id_exists UNIQUE (keyword_id))
      IN mimer_store


   --==============================================================
   -- Create sequences.
   --==============================================================

   CREATE UNIQUE SEQUENCE keyword_id_seq INITIAL_VALUE = 200001


   --==============================================================
   -- Grant privileges on tables in the MIMER_STORE_BOOK schema.
   --==============================================================

   GRANT SELECT ON authors TO mimer_admin_group
   GRANT SELECT ON keywords TO mimer_admin_group
   GRANT SELECT ON titles TO mimer_admin_group

-- end of schema mimer_store_book
@


--==============================================================
-- Create MIMER_STORE_BOOK PSM routines.
--==============================================================

@
CREATE PROCEDURE mimer_store_book.validate_isbn(INOUT p_isbn CHAR(18))
-- Validate an ISBN; an exception is raised if invalid
DETERMINISTIC
BEGIN
   DECLARE v_check_digit CHAR;
   DECLARE v_check_sum INTEGER DEFAULT 0;
   DECLARE v_index INTEGER DEFAULT 1;

   DECLARE isbn_not_valid CONDITION;
   DECLARE invalid_character_value CONDITION FOR SQLSTATE '22018';

   DECLARE EXIT HANDLER FOR invalid_character_value
      SIGNAL isbn_not_valid;

   IF UPPER(p_isbn) LIKE 'ISBN%' then
      SET p_isbn = TRIM(SUBSTRING(p_isbn FROM 5));
   END IF;

   -- An ISBN is a 10-character value of the form
   -- <country or group of countries identifier><publisher identifier>
   -- <title identifier><check digit>
   SET p_isbn = REPLACE(REPLACE(p_isbn, '-', ''), ' ', '');

   REPEAT
      SET v_check_sum = v_check_sum + ((11 - v_index)
                      * CAST(SUBSTRING(p_isbn FROM v_index FOR 1) AS INTEGER));
      SET v_index = v_index + 1;
   UNTIL v_index = 10
   END REPEAT;

   SET v_check_sum = 11 - MOD(v_check_sum, 11);
   IF v_check_sum <= 9 THEN
      SET v_check_digit = CAST(v_check_sum AS CHAR);
   ELSE
      IF v_check_sum = 10 THEN
         SET v_check_digit = 'X';
      ELSE
         SET v_check_digit = '0';
      END IF;
   END IF;

   IF v_check_digit <> UPPER(SUBSTRING(p_isbn FROM 10)) THEN
      SIGNAL isbn_not_valid;
   END IF;
END  -- of routine mimer_store_book.validate_isbn
@


@
CREATE FUNCTION mimer_store_book.format_isbn(p_isbn CHAR(13)) RETURNS CHAR(13)
-- Format an ISBN
DETERMINISTIC
BEGIN
   DECLARE v_hyphens ROW (one INTEGER, two INTEGER) DEFAULT (0, 0);
   DECLARE v_isbn CHAR(13);
   DECLARE v_publisher INTEGER;

   -- The <country identifer> is 1 to 5 digits
   -- the <publisher identifier> is 1 to 7 digits

   SET v_isbn = p_isbn;
   CALL mimer_store_book.validate_isbn(v_isbn);

   CASE
      WHEN v_isbn LIKE '0%' THEN
         -- English language group prefix ranges
         -- (US, UK, Canada, Australia, New Zealand, etc)
         SET v_publisher = CAST(SUBSTRING(v_isbn FROM 2 FOR 2) AS INTEGER);
         CASE
            WHEN v_publisher >= 0 THEN SET v_hyphens = (1, 3);
            WHEN v_publisher > 20 THEN SET v_hyphens = (1, 4);
            WHEN v_publisher > 70 THEN SET v_hyphens = (1, 5);
            WHEN v_publisher > 85 THEN SET v_hyphens = (1, 6);
            WHEN v_publisher > 90 THEN SET v_hyphens = (1, 7);
            WHEN v_publisher > 95 THEN SET v_hyphens = (1, 8);
         END CASE;

      WHEN v_isbn LIKE '1%' THEN
         -- Additional English language
         SET v_publisher = CAST(SUBSTRING(v_isbn FROM 2 FOR 4) AS INTEGER);
         CASE
            WHEN v_publisher BETWEEN 0000 AND 0999 THEN SET v_hyphens = (1, 3);
            WHEN v_publisher BETWEEN 1000 AND 3999 THEN SET v_hyphens = (1, 4);
            WHEN v_publisher BETWEEN 4000 AND 5499 THEN SET v_hyphens = (1, 5);
            WHEN v_publisher BETWEEN 5500 AND 8697 THEN SET v_hyphens = (1, 6);
            WHEN v_publisher BETWEEN 8698 AND 9989 THEN SET v_hyphens = (1, 7);
            WHEN v_publisher BETWEEN 9990 AND 9999 THEN SET v_hyphens = (1, 8);
         END CASE;

      WHEN v_isbn LIKE '2%' THEN
         -- French language group
         SET v_publisher = CAST(SUBSTRING(v_isbn FROM 2 FOR 7) AS INTEGER);
         CASE
            WHEN v_publisher <= 1999999
               THEN SET v_hyphens = (1, 3);
            WHEN v_publisher BETWEEN 2000000 AND 3999999
               THEN SET v_hyphens = (1, 4);
            WHEN v_publisher BETWEEN 7000000 AND 7599999
            OR v_publisher BETWEEN 7654000 AND 7654999
            OR v_publisher BETWEEN 7777000 AND 7777999
               THEN SET v_hyphens = (1, 5);
            WHEN v_publisher BETWEEN 8400000 AND 8699999
               THEN SET v_hyphens = (1, 6);
            WHEN v_publisher BETWEEN 9000000 AND 9197689
               THEN SET v_hyphens = (1, 7);
            WHEN v_publisher BETWEEN 9500000 AND 9599768
               THEN SET v_hyphens = (1, 8);
            ELSE BEGIN END;
         END CASE;

      WHEN v_isbn LIKE '91%' THEN
         -- Swedish
         SET v_publisher = CAST(SUBSTRING(v_isbn FROM 2 FOR 2) AS INTEGER);
         CASE
            WHEN v_publisher BETWEEN 00 AND 19 THEN SET v_hyphens = (2, 3);
            WHEN v_publisher BETWEEN 20 AND 49 THEN SET v_hyphens = (2, 4);
            WHEN v_publisher BETWEEN 50 AND 64 THEN SET v_hyphens = (2, 5);
            WHEN v_publisher BETWEEN 70 AND 79 THEN SET v_hyphens = (2, 6);
            WHEN v_publisher BETWEEN 85 AND 94 THEN SET v_hyphens = (2, 7);
            WHEN v_publisher BETWEEN 97 AND 99 THEN SET v_hyphens = (2, 8);
            ELSE BEGIN END;
         END CASE;

      ELSE BEGIN END;
   END CASE;

   IF v_hyphens <> (0, 0) THEN
      SET v_isbn = SUBSTRING(v_isbn FROM 1 FOR v_hyphens.one)
                   || '-' ||
                   SUBSTRING(v_isbn FROM v_hyphens.one+1 FOR
                             v_hyphens.two-v_hyphens.one)
                   || '-' ||
                   SUBSTRING(v_isbn FROM v_hyphens.two+1 FOR 9-v_hyphens.two)
                   || '-' ||
                   SUBSTRING(v_isbn FROM 10);
   ELSE
      SET v_isbn = p_isbn;
   END IF;

   RETURN v_isbn;
END  -- of routine mimer_store_book.format_isbn
@


@
CREATE FUNCTION mimer_store_book.authors(p_authors_list VARCHAR(128))
   RETURNS VARCHAR(48)
-- Returns the name of the first author, with a mark of omission if there
-- is more than one
DETERMINISTIC
BEGIN
   DECLARE v_offset INTEGER;
   DECLARE v_omission CHAR(12) DEFAULT ' ... [et al]';

   -- Return only the name of the first author
   -- If there is more than one author, follow the name with a mark of omission
   SET v_offset = POSITION(';' IN p_authors_list);
   IF v_offset = 0 THEN
      SET v_offset = POSITION(' and ' IN p_authors_list);
      IF v_offset = 0 THEN
         SET v_offset = POSITION(' & ' IN p_authors_list);
         IF v_offset = 0 THEN
            RETURN SUBSTRING(p_authors_list FROM 1 FOR 48);
         END IF;
      END IF;
   END IF;

   IF v_offset > 48 - CHAR_LENGTH(v_omission) THEN
      SET v_offset = 49 - CHAR_LENGTH(v_omission);
   END IF;

   RETURN SUBSTRING(p_authors_list FROM 1 FOR v_offset-1) || v_omission;
END  -- of routine mimer_store_book.authors
@


@
CREATE FUNCTION mimer_store_book.authors_name(p_name VARCHAR(48))
   RETURNS VARCHAR(48)
-- Formats a name into <surname>[,<initial>]
DETERMINISTIC
BEGIN
   DECLARE v_length, v_offset INTEGER;
   DECLARE v_fnm, v_name VARCHAR(48);

   SET v_length = POSITION(',' IN p_name);
   IF v_length = 0 THEN
      SET v_name = UPPER(TRIM(SUBSTRING(p_name FROM 1)));
   ELSE
      -- Append first initial to surname
      SET v_name = UPPER(TRIM(SUBSTRING(p_name FROM 1 FOR v_length)));
      SET v_fnm = UPPER(TRIM(SUBSTRING(p_name FROM v_length+1)));
      SET v_name = v_name || SUBSTRING(v_fnm FROM 1 FOR 1);
   END IF;

   RETURN v_name;
END  -- of routine mimer_store_book.authors_name
@


@
CREATE FUNCTION mimer_store_book.keyword_id(p_keyword VARCHAR(48)) 
   RETURNS INTEGER
-- Inserts a word in the KEYWORDS table
-- and returns the identifier with which the keyword is associated
MODIFIES SQL DATA
BEGIN
   DECLARE v_keyword_id INTEGER;

   DECLARE CONTINUE HANDLER FOR NOT FOUND
   BEGIN
      INSERT INTO mimer_store_book.keywords(keyword)
         VALUES (UPPER(TRIM(p_keyword)));

      SET v_keyword_id = CURRENT_VALUE OF mimer_store_book.keyword_id_seq;
   END;  -- of not found handler

   SELECT keyword_id
      INTO v_keyword_id
      FROM mimer_store_book.keywords
      WHERE keyword = TRIM(p_keyword);

   RETURN v_keyword_id;
END  -- of routine mimer_store_book.keyword_id
@


@
CREATE PROCEDURE mimer_store_book.catalogue_authors(IN p_item_id INTEGER,
                                                 IN p_authors_list VARCHAR(128))
-- Stores author names as keywords and forms a link between a book
-- and the keywords
MODIFIES SQL DATA
BEGIN
   DECLARE v_author VARCHAR(50);
   DECLARE v_authors VARCHAR(130);
   DECLARE v_offset, v_length INTEGER;

   SET v_authors = REPLACE(' ' || p_authors_list || ' ', ' and ', ';');
   SET v_authors = REPLACE(v_authors, ' & ', ';');
   SET v_authors = TRIM(v_authors);

extract_authors:
   LOOP
      IF v_authors = '' THEN LEAVE extract_authors; END IF;

      SET v_offset = POSITION(';' IN v_authors);

      IF v_offset <> 1 THEN
         IF v_offset = 0
         OR v_offset > 49 THEN
            SET v_length = 48;
         ELSE
            SET v_length = v_offset - 1;
         END IF;

         SET v_author = mimer_store_book.authors_name(
                           SUBSTRING(v_authors FROM 1 FOR v_length));

         BEGIN
            DECLARE v_keyword_id INTEGER;

            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
            BEGIN
               -- Ignore all SQL errors
            END;  -- of sqlexception handler

            SET v_keyword_id = mimer_store_book.keyword_id(v_author);

            INSERT INTO mimer_store_book.authors(keyword_id, item_id)
               VALUES (v_keyword_id, p_item_id);
         END;
      END IF;

      IF v_offset = 0 THEN LEAVE extract_authors; END IF;

      SET v_authors = TRIM(SUBSTRING(v_authors FROM v_offset+1));
   END LOOP extract_authors;
END  -- of routine mimer_store_book.catalogue_authors
@


--==============================================================
-- Create statement triggers for schema MIMER_STORE_BOOK.
--==============================================================

@
CREATE TRIGGER mimer_store_book.titles_after_insert
   AFTER INSERT ON mimer_store_book.titles
REFERENCING NEW TABLE AS btl
BEGIN ATOMIC
   DECLARE btl ROW AS (mimer_store_book.titles(item_id, authors_list));
   DECLARE c_1 CURSOR FOR SELECT item_id, authors_list
                             FROM btl;

   DECLARE EXIT HANDLER FOR NOT FOUND
      CLOSE c_1;

   OPEN c_1;

btl:
   LOOP
      FETCH c_1
         INTO btl;

      CALL mimer_store_book.catalogue_authors(btl.item_id, btl.authors_list);
   END LOOP btl;
END  -- of trigger mimer_store_book.titles_after_insert
@


@
CREATE TRIGGER mimer_store_book.titles_after_update
   AFTER UPDATE ON mimer_store_book.titles
REFERENCING OLD TABLE AS btl_old
            NEW TABLE AS btl_new
BEGIN ATOMIC
   DECLARE btl ROW AS (mimer_store_book.titles(item_id, authors_list));
   DECLARE c_1 CURSOR FOR SELECT item_id, btl_new.authors_list
                             FROM btl_old JOIN btl_new USING (item_id)
                             WHERE btl_old.authors_list <> btl_new.authors_list;
   DECLARE ath ROW AS (mimer_store_book.authors(keyword_id));
   DECLARE c_2 CURSOR FOR SELECT keyword_id
                             FROM mimer_store_book.authors
                             WHERE item_id = btl.item_id;

   DECLARE EXIT HANDLER FOR NOT FOUND
      CLOSE c_1;

   OPEN c_1;

btl:
   LOOP
      FETCH c_1
         INTO btl;

      BEGIN
         DECLARE EXIT HANDLER FOR NOT FOUND
            CLOSE c_2;

         OPEN c_2;

ath:     LOOP
            FETCH c_2
               INTO ath;

            DELETE
               FROM mimer_store_book.authors
               WHERE CURRENT OF c_2;

            BEGIN
               DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
               BEGIN
               END;  -- of sqlexception handler

               DELETE
                  FROM mimer_store_book.keywords
                  WHERE keyword_id = ath.keyword_id;
            END;

         END LOOP ath;
      END;

      CALL mimer_store_book.catalogue_authors(btl.item_id, btl.authors_list);
   END LOOP btl;
END  -- of trigger mimer_store_book.titles_after_update
@


CREATE VIEW mimer_store_book.details
   AS SELECT authors_list, product AS title, producer AS publisher, format,
             price, stock, reorder_level,
             extract_date(release_date) AS release_date,
             'ISBN ' || mimer_store_book.format_isbn(isbn) AS isbn,
             ean_code, status, product_search AS title_search, item_id,
             category_id, product_id, display_order, image_id
   FROM product_details
   NATURAL JOIN mimer_store_book.titles;


--==============================================================
-- Create instead of trigger on insert against view.
--==============================================================

@
CREATE TRIGGER mimer_store_book.details_instead_of_insert
   INSTEAD OF INSERT ON mimer_store_book.details
REFERENCING NEW TABLE AS bdt
BEGIN ATOMIC
   DECLARE ean BIGINT;
   DECLARE book ROW AS (mimer_store_book.details(isbn, authors_list, title,
                                                 publisher, format,
                                                 release_date, price, stock,
                                                 reorder_level));
   DECLARE data ROW AS (mimer_store.formats(format_id),
                        mimer_store_book.details(item_id, product_id),
                        mimer_store.producers(producer_id));

   DECLARE c_1 CURSOR FOR SELECT isbn, authors_list, title,
                                 publisher, format,
                                 release_date, price, stock,
                                 reorder_level
                             FROM bdt;

   DECLARE EXIT HANDLER FOR NOT FOUND
      CLOSE c_1;

   OPEN c_1;

bdt:
   LOOP
      FETCH c_1
         INTO book;

      -- Validate ISBN
      CALL mimer_store_book.validate_isbn(book.isbn);

      BEGIN
         DECLARE book_format_not_valid CONDITION;

         DECLARE EXIT HANDLER FOR NOT FOUND
            SIGNAL book_format_not_valid;

         -- Check that FORMAT is valid for the Book category
         SELECT format_id
            INTO data.format_id
            FROM mimer_store.formats
            NATURAL JOIN mimer_store.categories
            WHERE format = TRIM(book.format)
            AND category = 'Books';
      END;

      BEGIN
         DECLARE publisher_not_allocated CONDITION;

         DECLARE EXIT HANDLER FOR NOT FOUND
            SIGNAL publisher_not_allocated;

         IF book.publisher <> '' THEN
            -- Check that PUBLISHER is valid
            SELECT producer_id
               INTO data.producer_id
               FROM mimer_store.producers
               WHERE producer = TRIM(book.publisher);
         END IF;
      END;

      BEGIN
         DECLARE CONTINUE HANDLER FOR NOT FOUND
         BEGIN
            SET data.product_id = CURRENT_VALUE OF mimer_store.product_id_seq;

            INSERT INTO mimer_store.products(product, product_id)
               VALUES (book.title, data.product_id);
         END;  -- of not found handler

         SELECT product_id
            INTO data.product_id
            FROM mimer_store.products
            WHERE product = TRIM(book.title);
      END;

      -- The "Bookland" EAN prefix of 978 is followed by the first nine digits
      -- of the ISBN. The check digit of the ISBN is replaced by an EAN check
      -- digit
      SET ean = 978000000000 + CAST(SUBSTRING(book.isbn FROM 1 FOR 9)
                                    AS INTEGER);

      INSERT INTO mimer_store.items(item_id, product_id, format_id,
                                    release_date, price,
                                    stock, reorder_level,
                                    ean_code,
                                    producer_id)
         VALUES (CURRENT_VALUE OF mimer_store.item_id_seq,
                 data.product_id, data.format_id,
                 mimer_store.cast_to_date(book.release_date),
                 book.price, book.stock, book.reorder_level,
                 (ean * 10) + mimer_store.ean_check_digit(ean),
                 data.producer_id);

      SET data.item_id = CURRENT_VALUE OF mimer_store.item_id_seq;
                
      INSERT INTO mimer_store_book.titles(item_id, authors_list, isbn)
         VALUES (data.item_id, book.authors_list, book.isbn);
   END LOOP bdt;
END -- of trigger mimer_store_book.details_instead_of_insert
@


@
CREATE PROCEDURE mimer_store_book.add_title(IN p_book_title VARCHAR(48),
                                            IN p_authors VARCHAR(128),
                                            IN p_published_by VARCHAR(48),
                                            IN p_format VARCHAR(20),
                                            IN p_isbn CHAR(18),
                                            IN p_date_released CHAR(10),
                                            IN p_price NUMERIC(7, 2),
                                            IN p_stock SMALLINT,
                                            IN p_reorder_level SMALLINT)
-- Add the details for a book entity; inserts against the join view which fires
-- the instead of trigger
MODIFIES SQL DATA
BEGIN
   -- Insert into join view
   INSERT INTO mimer_store_book.details(title, authors_list, publisher, format,
                                        isbn, release_date, price, stock,
                                        reorder_level)
      VALUES (p_book_title, p_authors, p_published_by, p_format,
              p_isbn, p_date_released, p_price, p_stock,
              p_reorder_level);
END  -- of routine mimer_store_book.add_title
@


@
CREATE PROCEDURE mimer_store_book.search(IN p_book_title VARCHAR(48),
                                         IN p_author VARCHAR(48))
-- Search the Book database for matches
VALUES (VARCHAR(48), VARCHAR(128), VARCHAR(20), NUMERIC(7, 2), INTEGER)
AS (title, authors_list, format, price, item_id)
READS SQL DATA
BEGIN
   DECLARE data ROW AS (mimer_store_book.details(title, authors_list, format,
                                                 price, item_id, product_id,
                                                 display_order));
   DECLARE c_1 CURSOR FOR SELECT DISTINCT 
                                 title, authors_list, format,
                                 price, item_id, product_id,
                                 display_order
                             FROM mimer_store_book.details
                             NATURAL JOIN mimer_store_book.authors
                             NATURAL JOIN mimer_store_book.keywords
                             WHERE title_search LIKE TRIM(TRAILING '0' FROM
                                mimer_store.product_search_code(p_book_title))
                                || '%'
                             AND keyword LIKE REPLACE(
                                mimer_store_book.authors_name(p_author),
                                ',', '%,')
                                || '%'
                             ORDER BY title, authors_list, product_id,
                                      display_order;

   DECLARE EXIT HANDLER FOR NOT FOUND
      CLOSE c_1;

   OPEN c_1;

   LOOP
      FETCH c_1
         INTO data;

      RETURN (data.title, data.authors_list, data.format, data.price,
              data.item_id);
   END LOOP;
END  -- of routine mimer_store_book.search
@


@
CREATE PROCEDURE mimer_store_book.title_details(IN p_item_id INTEGER)
-- Result set procedure that returns book details
VALUES (VARCHAR(48), VARCHAR(128), VARCHAR(48), VARCHAR(18), VARCHAR(20),
        CHAR(10), NUMERIC(7, 2), INTEGER, INTEGER)
AS (title, authors_list, publisher, isbn, format,
    release_date, price, image_id, item_id)
READS SQL DATA
BEGIN
   DECLARE data ROW AS (mimer_store_book.details(title, authors_list, publisher,
                                                 isbn, format, release_date,
                                                 price, image_id, item_id));

   DECLARE item_identifier_not_valid CONDITION;

   DECLARE EXIT HANDLER FOR NOT FOUND
      SIGNAL item_identifier_not_valid;

   SELECT title, authors_list, publisher, isbn, format,
          release_date, price, image_id, item_id
      INTO data
      FROM mimer_store_book.details
      NATURAL JOIN mimer_store.categories
      WHERE item_id = p_item_id
      AND category = 'Books';

   RETURN data;
END  -- of routine mimer_store_book.title_details
@

--==============================================================
-- Grant privileges on views and routines
-- in the MIMER_STORE_BOOK schema.
--==============================================================

GRANT SELECT ON mimer_store_book.details TO mimer_admin_group;

GRANT EXECUTE ON PROCEDURE mimer_store_book.search TO mimer_store_group;
GRANT EXECUTE ON PROCEDURE mimer_store_book.title_details TO mimer_store_group;
GRANT EXECUTE ON PROCEDURE mimer_store_book.add_title TO mimer_store_group;


--==============================================================
-- Create synonyms for the MIMER_STORE_BOOK schema tables
-- and views.
--==============================================================

CREATE SYNONYM authors FOR mimer_store_book.authors;
CREATE SYNONYM book_details FOR mimer_store_book.details;
CREATE SYNONYM book_titles FOR mimer_store_book.titles;
CREATE SYNONYM keywords FOR mimer_store_book.keywords;


--**************************************************************
-- MIMER_STORE schema continued.
--
-- Add two procedures to the base schema and grant privilege.
--**************************************************************

@
CREATE PROCEDURE order_item(IN p_order_id INTEGER,
                            IN p_item_id INTEGER,
                            IN p_quantity_ordered SMALLINT)
-- Procedure to associate an order for a quantity of a particular item
-- against an order identifier
MODIFIES SQL DATA
BEGIN
   DECLARE itm ROW AS (mimer_store.items(price));

   DECLARE item_identifier_not_allocated CONDITION;
   DECLARE quantity_ordered_not_valid CONDITION;

   DECLARE EXIT HANDLER FOR NOT FOUND
      SIGNAL item_identifier_not_allocated;

   IF p_quantity_ordered <= 0 THEN
      SIGNAL quantity_ordered_not_valid;
   END IF;

   SELECT price
      INTO itm.price
      FROM mimer_store.items
      WHERE item_id = p_item_id;

   INSERT INTO mimer_store.order_items(order_id, seq_no, item_id,
                                       quantity, "VALUE")
      SELECT p_order_id, COALESCE(MAX(seq_no)+1, 1), p_item_id,
             p_quantity_ordered, itm.price
         FROM mimer_store.order_items
         WHERE order_id = p_order_id;

   BEGIN
      DECLARE constraint_violation CONDITION FOR SQLSTATE '23000';
      DECLARE insufficient_stock CONDITION;

      DECLARE EXIT HANDLER FOR constraint_violation
         SIGNAL insufficient_stock;

      UPDATE mimer_store.items
         SET stock = stock - p_quantity_ordered
         WHERE item_id = p_item_id;
   END;
END  -- of routine order_item
@


@
CREATE PROCEDURE barcode(IN p_ean BIGINT)
-- Result set procedure that returns book or music details for the given EAN
VALUES (VARCHAR(48), VARCHAR(48), VARCHAR(20), NUMERIC(7, 2), INTEGER)
AS (title, creator, format, price, item_id)
READS SQL DATA
BEGIN
   DECLARE data ROW AS (product_details(product, format, price, item_id),
                        mimer_store_music.details(artist));

   DECLARE ean_not_allocated CONDITION;

   DECLARE EXIT HANDLER FOR NOT FOUND
      SIGNAL ean_not_allocated;

   SELECT product, format, price, item_id
      INTO data.product, data.format, data.price, data.item_id
      FROM mimer_store.product_details
      WHERE ean_code = p_ean;

   BEGIN
      DECLARE CONTINUE HANDLER FOR NOT FOUND
      BEGIN
         SELECT artist
            INTO data.artist
            FROM mimer_store_music.titles
            NATURAL JOIN mimer_store_music.artists
            WHERE item_id = data.item_id;
      END;

      SELECT mimer_store_book.authors(authors_list)
         INTO data.artist
         FROM mimer_store_book.titles
         WHERE item_id = data.item_id;

      RETURN (data.product, data.artist, data.format, data.price, data.item_id);
   END;
END  -- of routine barcode
@

GRANT EXECUTE ON PROCEDURE barcode TO mimer_store_group;


--**************************************************************
--#Creating the MIMER_STORE_WEB schema
-- Owned by the current ident.
--
-- The full name for any object is <schema_name>.<object_name>
-- e.g. MIMER_STORE_WEB.SESSION_NO_SEQ
--**************************************************************

@
CREATE SCHEMA mimer_store_web AUTHORIZATION mimer_store
   --==============================================================
   -- Create sequences.
   --==============================================================

   CREATE SEQUENCE session_no_seq INITIAL_VALUE = 1000001


   --==============================================================
   -- Create tables.
   --==============================================================

   CREATE TABLE mimer_store_web.sessions (
         session_no VARCHAR(16) DEFAULT ''
                                CONSTRAINT ssn_session_no_illegal
                                   CHECK (session_no <> ''),
         order_id mimer_store.internal_id DEFAULT CURRENT_VALUE OF
                                             mimer_store.order_id_seq,
         last_accessed TIMESTAMP(9) DEFAULT LOCALTIMESTAMP(9),
         CONSTRAINT ssn_primary_key PRIMARY KEY (session_no),
         CONSTRAINT ssn_orders
            FOREIGN KEY (order_id) REFERENCES mimer_store.orders
            ON DELETE CASCADE ON UPDATE NO ACTION)
      IN mimer_orders

-- END OF SCHEMA mimer_store_web
@


--==============================================================
-- Create MIMER_STORE_WEB PSM routines.
--==============================================================

@
CREATE FUNCTION mimer_store_web.session_expiration_period()
   RETURNS INTERVAL HOUR TO MINUTE
-- Defines the period that a session can be unused
DETERMINISTIC
RETURN INTERVAL '10' MINUTE(3)  -- Intentionally very short
@


@
CREATE PROCEDURE mimer_store_web.delete_basket(p_session_no VARCHAR(16))
-- Deletes expired baskets
MODIFIES SQL DATA
BEGIN
   IF p_session_no = '*' THEN
      -- '*' indicates that all expired sessions should be deleted
      DELETE
         FROM mimer_store.orders
         WHERE order_id IN (SELECT order_id
                               FROM mimer_store_web.sessions
                               WHERE last_accessed < LOCALTIMESTAMP -
                                  mimer_store_web.session_expiration_period());
   ELSE
      -- Delete the specified session
      DELETE
         FROM mimer_store.orders
         WHERE order_id = (SELECT order_id
                              FROM mimer_store_web.sessions
                              WHERE session_no = p_session_no);
   END IF;
END  -- of routine mimer_store_web.delete_basket
@


@
CREATE FUNCTION mimer_store_web.order_id(p_session_no VARCHAR(16))
   RETURNS INTEGER
-- Returns the order identifier for a given session number,
-- provided that the session hasn't expired
READS SQL DATA
BEGIN
   DECLARE ssn ROW AS (mimer_store_web.sessions(order_id));

   DECLARE session_invalid CONDITION;

   DECLARE EXIT HANDLER FOR NOT FOUND
      SIGNAL session_invalid;

   SELECT order_id
      INTO ssn
      FROM mimer_store_web.sessions
      WHERE session_no = p_session_no
      AND last_accessed > LOCALTIMESTAMP
          - mimer_store_web.session_expiration_period();

   RETURN ssn.order_id;
END  -- of routine mimer_store_web.order_id
@


@
CREATE FUNCTION mimer_store_web.validate_basket(p_session_no VARCHAR(16))
   RETURNS INTEGER
-- Returns the order identifier for a given session number,
-- deleting the basket if the session has expired
MODIFIES SQL DATA
BEGIN
   DECLARE session_invalid CONDITION;

   DECLARE EXIT HANDLER FOR session_invalid
   BEGIN
      CALL mimer_store_web.delete_basket(p_session_no);

      RESIGNAL;
   END;  -- of session_invalid handler

   RETURN mimer_store_web.order_id(p_session_no);
END  -- of routine mimer_store_web.validate_basket
@


@
CREATE FUNCTION mimer_store_web.add_to_basket(p_session_no VARCHAR(16),
                                              p_item_id INTEGER,
                                              p_quantity_ordered SMALLINT)
   RETURNS VARCHAR(16)
-- Add an item to an order. The session number is unconditionally returned
MODIFIES SQL DATA
BEGIN
   DECLARE ssn ROW AS (mimer_store_web.sessions(order_id, session_no));
   DECLARE v_ws VARCHAR(10);

   IF p_session_no = '' THEN
      -- Session undefined so create
      INSERT INTO mimer_store.orders(order_id)
         VALUES (DEFAULT);

      -- Format a session number
      SET v_ws = CAST(DAYOFYEAR(CURRENT_DATE) AS VARCHAR(3));
      SET ssn.session_no = PASTE('000', 4-CHAR_LENGTH(v_ws),
                                 CHAR_LENGTH(v_ws), v_ws);
      SET v_ws = CAST(NEXT_VALUE OF mimer_store_web.session_no_seq
                      AS VARCHAR(10));
      SET ssn.session_no = ssn.session_no || '-' ||
                           PASTE(REPEAT('0', 10), 11-CHAR_LENGTH(v_ws),
                                 CHAR_LENGTH(v_ws), v_ws);
      SET ssn.session_no = SUBSTRING(ssn.session_no FROM 1 FOR 8) || '-' ||
                           SUBSTRING(ssn.session_no FROM 9 FOR 3) || '-' ||
                           SUBSTRING(ssn.session_no FROM 12);

      INSERT INTO mimer_store_web.sessions(session_no)
         VALUES (ssn.session_no);
   ELSE
      SET ssn.session_no = p_session_no;
   END IF;

   SET ssn.order_id = mimer_store_web.validate_basket(ssn.session_no);

   CALL mimer_store.order_item(ssn.order_id, p_item_id, p_quantity_ordered);

   RETURN ssn.session_no;
END  -- of routine mimer_store_web.add_to_basket
@


@
CREATE PROCEDURE mimer_store_web.view_basket(p_session_no VARCHAR(16))
-- Result set procedure to display the contents of a basket
VALUES (VARCHAR(48), VARCHAR(48), VARCHAR(20), SMALLINT, NUMERIC(10, 2))
AS (title, creator, format, quantity, price)
READS SQL DATA
BEGIN
   DECLARE itm ROW AS (mimer_store.items(ean_code),
                       mimer_store.order_items(quantity, "VALUE"));
   DECLARE ssn ROW AS (mimer_store_web.sessions(order_id));
   DECLARE v_total_price NUMERIC(10, 2) DEFAULT 0.0;

   DECLARE c_1 CURSOR FOR SELECT ean_code, SUM(quantity), "VALUE"
                             FROM mimer_store.items
                             NATURAL JOIN mimer_store.order_items
                             WHERE order_id = ssn.order_id
                             GROUP BY ean_code, "VALUE";

   DECLARE EXIT HANDLER FOR NOT FOUND
   BEGIN
      CLOSE c_1;

      -- Return total information
      RETURN ('Total price (euros)', NULL, NULL, NULL, v_total_price);
   END;  -- of not found handler

   SET ssn.order_id = mimer_store_web.order_id(p_session_no);

   OPEN c_1;

   LOOP
      FETCH c_1
         INTO itm;

      BEGIN
         DECLARE data ROW AS (mimer_store.music_details(product, artist, format,
                                                        price, item_id));

         DECLARE c_2 CURSOR FOR CALL mimer_store.barcode(itm.ean_code);

         DECLARE EXIT HANDLER FOR NOT FOUND
            CLOSE c_2;

         OPEN c_2;

         LOOP
            FETCH c_2
               INTO data.product, data.artist, data.format, data.price,
                    data.item_id; -- *****

            SET v_total_price = v_total_price + (itm.quantity * itm."VALUE");

            RETURN (data.product, data.artist, data.format, itm.quantity,
                    itm.quantity * itm."VALUE");
         END LOOP;
      END;
   END LOOP;
END  -- of routine mimer_store_web.view_basket
@


@
CREATE FUNCTION mimer_store_web.validate_customer(IN p_email_addr VARCHAR(128),
                                                  IN p_password VARCHAR(18))
   RETURNS INTEGER
-- Validate a customer by email address and password
READS SQL DATA
BEGIN
   DECLARE cst ROW AS (mimer_store.customers(customer_id));

   DECLARE customer_not_valid CONDITION;

   DECLARE EXIT HANDLER FOR NOT FOUND
      SIGNAL customer_not_valid;

   SELECT customer_id
      INTO cst
      FROM mimer_store.customers
      WHERE email = p_email_addr
      AND password = p_password;

   RETURN cst.customer_id;
END  -- of routine mimer_store_web.validate_customer
@


@
CREATE PROCEDURE mimer_store_web.place_order(IN p_session_no VARCHAR(16),
                                             IN p_customer_id INTEGER,
                                             OUT p_order_no VARCHAR(16),
                                             OUT p_euro_price NUMERIC(10, 2),
                                             OUT p_local_currency VARCHAR(32),
                                             OUT p_local_price NUMERIC(15, 2))
-- Complete an order and tidy up
MODIFIES SQL DATA
BEGIN
   DECLARE data ROW AS (mimer_store_web.sessions(order_id),
                        mimer_store.orders(order_no));
   DECLARE v_day_no VARCHAR(5);
   DECLARE v_euros NUMERIC(10, 2);

   DECLARE processing_error_encountered CONDITION;

   DECLARE EXIT HANDLER FOR SQLEXCEPTION
      SIGNAL processing_error_encountered;

   SET data.order_id = mimer_store_web.validate_basket(p_session_no);

   -- Format the order number
   SET v_day_no = CAST(MOD(EXTRACT(YEAR FROM CURRENT_DATE), 100) * 1000
                  + DAYOFYEAR(CURRENT_DATE) AS VARCHAR(5));
   SET data.order_no = PASTE('00000', 6-CHAR_LENGTH(v_day_no),
                             CHAR_LENGTH(v_day_no), v_day_no)
                       || '-' ||
                       CAST(NEXT_VALUE OF mimer_store.order_no_seq
                            AS VARCHAR(10));

   UPDATE mimer_store.orders
      SET order_no = data.order_no,
          customer_id = p_customer_id,
          datetime = DEFAULT
      WHERE order_id = data.order_id;

   DELETE
      FROM mimer_store_web.sessions
      WHERE session_no = p_session_no;

   SELECT SUM(quantity * "VALUE")
      INTO v_euros
      FROM mimer_store.items
      NATURAL JOIN mimer_store.order_items
      WHERE order_id = data.order_id;

   SELECT currency, v_euros * exchange_rate
      INTO p_local_currency, p_local_price
      FROM mimer_store.customers
      JOIN mimer_store.countries AS cnt ON cnt.code = country_code
      JOIN mimer_store.currencies AS crn ON crn.code = currency_code
      WHERE customer_id = p_customer_id
      AND currency_code <> 'EUR'
      AND exchange_rate IS NOT NULL;

   UPDATE mimer_store.customers
      SET last_order = LOCALTIMESTAMP
      WHERE customer_id = p_customer_id;

   SET p_order_no = data.order_no;
   SET p_euro_price = v_euros;
END  -- of routine mimer_store_web.place_order
@


--==============================================================
-- Create statement triggers for schema MIMER_STORE_WEB.
--==============================================================

@
CREATE TRIGGER mimer_store_web.sessions_after_update
   AFTER UPDATE ON mimer_store_web.sessions
REFERENCING OLD TABLE AS ssn_old
            NEW TABLE AS ssn_new
BEGIN ATOMIC
   DECLARE ssn ROW AS (mimer_store_web.sessions(session_no, order_id));

   DECLARE c_1 CURSOR FOR SELECT session_no, order_id
                             FROM ssn_old;

   DECLARE ssn_illegal_operation CONDITION;

   DECLARE EXIT HANDLER FOR NOT FOUND
      CLOSE c_1;

   OPEN c_1;

ssn:
   LOOP
      FETCH c_1
         INTO ssn;

      BEGIN
         DECLARE CONTINUE HANDLER FOR NOT FOUND
         BEGIN
         END;  -- of not found handler

         IF NOT EXISTS(SELECT 1
                          FROM ssn_new
                          WHERE session_no = ssn.session_no
                          AND order_id = ssn.order_id) THEN
            SIGNAL ssn_illegal_operation;
         END IF;
      END;
   END LOOP ssn;
END  -- of trigger mimer_store_web.sessions_after_update
@


--==============================================================
-- Create synonyms for the MIMER_STORE_WEB schema table.
--==============================================================

CREATE SYNONYM sessions FOR mimer_store_web.sessions;


--==============================================================
-- Grant privileges on tables and routines
-- in the MIMER_STORE_WEB schema.
--==============================================================

GRANT SELECT ON mimer_store_web.sessions TO mimer_admin_group;

GRANT EXECUTE ON FUNCTION mimer_store_web.add_to_basket TO mimer_store_group;
GRANT EXECUTE ON PROCEDURE mimer_store_web.place_order TO mimer_store_group;
GRANT EXECUTE ON FUNCTION mimer_store_web.validate_customer
   TO mimer_store_group;
GRANT EXECUTE ON PROCEDURE mimer_store_web.view_basket TO mimer_store_group;


--==============================================================
-- Create an ident for use in web-based applications.
--
-- The ident is only allowed to execute PSM routines.
--==============================================================

CREATE IDENT mimer_web AS USER USING 'web' WITHOUT SCHEMA;

GRANT EXECUTE ON PROCEDURE coming_soon TO mimer_web;
GRANT EXECUTE ON PROCEDURE mimer_store_music.Search TO mimer_web;
GRANT EXECUTE ON PROCEDURE mimer_store_music.TitleDetails TO mimer_web;
GRANT EXECUTE ON PROCEDURE mimer_store_music.TrackDetails TO mimer_web;
GRANT EXECUTE ON PROCEDURE mimer_store_book.search TO mimer_web;
GRANT EXECUTE ON PROCEDURE mimer_store_book.title_details TO mimer_web;
GRANT EXECUTE ON FUNCTION mimer_store_web.add_to_basket TO mimer_web;
GRANT EXECUTE ON PROCEDURE mimer_store_web.place_order TO mimer_web;
GRANT EXECUTE ON FUNCTION mimer_store_web.validate_customer TO mimer_web;
GRANT EXECUTE ON PROCEDURE mimer_store_web.view_basket TO mimer_web;


READ 'exdat.sql';


--==============================================================
--#Update optimizer statistics after database load.
--==============================================================

UPDATE STATISTICS FOR IDENT mimer_store;


--==============================================================
-- Create a set of statements to use from within Mimer Embedded.
--==============================================================

CREATE STATEMENT product_details_stmt SELECT *
                                         FROM product_details ORDER BY product;
CREATE STATEMENT barcode_stmt CALL barcode(:ean);
CREATE STATEMENT coming_soon_stmt CALL coming_soon(:category);
CREATE STATEMENT validate_ean_code_stmt SET :ean = validate_ean_code(:ean);

CREATE STATEMENT music_details_stmt SELECT *
                                       FROM music_details;
CREATE STATEMENT music_search_stmt
   CALL mimer_store_music.Search(:title, :recorded_by, :max_rows);
CREATE STATEMENT music_title_details_stmt
   CALL mimer_store_music.TitleDetails(:item_id);
CREATE STATEMENT music_track_details_stmt
   CALL mimer_store_music.TrackDetails(:item_id);

CREATE STATEMENT book_details_stmt SELECT *
                                      FROM book_details;
CREATE STATEMENT book_search_stmt
   CALL mimer_store_book.search(:title, :author);
CREATE STATEMENT book_title_details_stmt
   CALL mimer_store_book.title_details(:item_id);

GRANT EXECUTE ON STATEMENT book_details_stmt TO mimer_store_group;
GRANT EXECUTE ON STATEMENT book_search_stmt TO mimer_store_group;
GRANT EXECUTE ON STATEMENT book_title_details_stmt TO mimer_store_group;
GRANT EXECUTE ON STATEMENT product_details_stmt TO mimer_store_group;
GRANT EXECUTE ON STATEMENT barcode_stmt TO mimer_store_group;
GRANT EXECUTE ON STATEMENT coming_soon_stmt TO mimer_store_group;
GRANT EXECUTE ON STATEMENT validate_ean_code_stmt TO mimer_store_group;
GRANT EXECUTE ON STATEMENT music_details_stmt TO mimer_store_group;
GRANT EXECUTE ON STATEMENT music_search_stmt TO mimer_store_group;
GRANT EXECUTE ON STATEMENT music_title_details_stmt TO mimer_store_group;
GRANT EXECUTE ON STATEMENT music_track_details_stmt TO mimer_store_group;


--==============================================================
-- Disconnect from the current ident.
--==============================================================

DISCONNECT;


--==============================================================
-- Create a full set of synonyms for MIMER_ADM.
--==============================================================

CONNECT;
MIMER_ADM
adm

CREATE SYNONYM artists FOR mimer_store_music.artists;
CREATE SYNONYM authors FOR mimer_store_book.authors;
CREATE SYNONYM book_details FOR mimer_store_book.details;
CREATE SYNONYM book_titles FOR mimer_store_book.titles;
CREATE SYNONYM categories FOR mimer_store.categories;
CREATE SYNONYM countries FOR mimer_store.countries;
CREATE SYNONYM currencies FOR mimer_store.currencies;
CREATE SYNONYM customer_addresses FOR mimer_store.customer_addresses;
CREATE SYNONYM customer_details FOR mimer_store.customer_details;
CREATE SYNONYM customers FOR mimer_store.customers;
CREATE SYNONYM formats FOR mimer_store.formats;
CREATE SYNONYM images FOR mimer_store.images;
CREATE SYNONYM items FOR mimer_store.items;
CREATE SYNONYM keywords FOR mimer_store_book.keywords;
CREATE SYNONYM music_details FOR mimer_store_music.details;
CREATE SYNONYM music_search FOR mimer_store_music.search;
CREATE SYNONYM music_titles FOR mimer_store_music.titles;
CREATE SYNONYM order_items FOR mimer_store.order_items;
CREATE SYNONYM orders FOR mimer_store.orders;
CREATE SYNONYM producers FOR mimer_store.producers;
CREATE SYNONYM product_details FOR mimer_store.product_details;
CREATE SYNONYM products FOR mimer_store.products;
CREATE SYNONYM samples FOR mimer_store_music.samples;
CREATE SYNONYM sessions FOR mimer_store_web.sessions;
CREATE SYNONYM stop_words FOR mimer_store.stop_words;
CREATE SYNONYM swedish_customers FOR mimer_store.swedish_customers;
CREATE SYNONYM tracks FOR mimer_store_music.tracks;


--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- To protect the database you should now change the passwords !
-- for the following idents:                                   !
-- MIMER_STORE, MIMER_ADM and MIMER_USR                        !
--                                                             !
-- See ALTER IDENT in the Reference Manual                     !
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


SET :end_time = LOCALTIMESTAMP(0);

CLOSE LOG;

SET MESSAGE ON;

-- EXIT;