Author: mhillyer
Date: 2006-03-23 06:15:17 +0100 (Thu, 23 Mar 2006)
New Revision: 1648
Log:
Sample Database: Document procedures, functions, triggers. Correct some schema bugs.
Modified:
trunk/sample-data/sakila/changelog.xml
trunk/sample-data/sakila/sakila-data.sql
trunk/sample-data/sakila/sakila-schema.sql
trunk/sample-data/sakila/sakila.xml
Modified: trunk/sample-data/sakila/changelog.xml
===================================================================
--- trunk/sample-data/sakila/changelog.xml 2006-03-23 04:50:02 UTC (rev 1647)
+++ trunk/sample-data/sakila/changelog.xml 2006-03-23 05:15:17 UTC (rev 1648)
@@ -6,6 +6,37 @@
]>
<section id="sakila-news">
+ <section id="sakila-news-0.7">
+
+ <title>Version 0.7</title>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Fixed bug in sales_by_store view that caused the same manager
+ to be listed for every store.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Fixed bug in inventory_held_by_customer function that caused
+ function to return multiple rows.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Moved rental_date trigger to sakila-data.sql file to prevent
+ it from interfering with data loading.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
<section id="sakila-news-0.6">
<title>Version 0.6</title>
Modified: trunk/sample-data/sakila/sakila-data.sql
===================================================================
--- trunk/sample-data/sakila/sakila-data.sql 2006-03-23 04:50:02 UTC (rev 1647)
+++ trunk/sample-data/sakila/sakila-data.sql 2006-03-23 05:15:17 UTC (rev 1648)
@@ -129,6 +129,13 @@
COMMIT;
--
+-- Trigger to enforce rental_date on INSERT
+--
+
+CREATE TRIGGER rental_date BEFORE INSERT ON rental
+ FOR EACH ROW SET NEW.rental_date = NOW();
+
+--
-- Dumping data for table staff
--
Modified: trunk/sample-data/sakila/sakila-schema.sql
===================================================================
--- trunk/sample-data/sakila/sakila-schema.sql 2006-03-23 04:50:02 UTC (rev 1647)
+++ trunk/sample-data/sakila/sakila-schema.sql 2006-03-23 05:15:17 UTC (rev 1648)
@@ -1,601 +1,595 @@
--- Sakila Sample Database Schema
--- Version 0.6
--- Copyright 2006 MySQL AB
-
-SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
-SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
-SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
-
-DROP SCHEMA IF EXISTS sakila;
-CREATE SCHEMA sakila;
-USE sakila;
-
---
--- Table structure for table `actor`
---
-
-CREATE TABLE actor (
- actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
- first_name VARCHAR(45) NOT NULL,
- last_name VARCHAR(45) NOT NULL,
- last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (actor_id),
- KEY idx_actor_last_name (last_name)
-)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Table structure for table `address`
---
-
-CREATE TABLE address (
- address_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
- address VARCHAR(50) NOT NULL,
- address2 VARCHAR(50) DEFAULT NULL,
- district VARCHAR(20) NOT NULL,
- city_id SMALLINT UNSIGNED NOT NULL,
- postal_code VARCHAR(10) DEFAULT NULL,
- phone VARCHAR(20) NOT NULL,
- last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (address_id),
- KEY idx_fk_city_id (city_id),
- CONSTRAINT `fk_address_city` FOREIGN KEY (city_id) REFERENCES city (city_id) ON DELETE RESTRICT ON UPDATE CASCADE
-)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Table structure for table `category`
---
-
-CREATE TABLE category (
- category_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
- name VARCHAR(25) NOT NULL,
- last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (category_id)
-)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Table structure for table `city`
---
-
-CREATE TABLE city (
- city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
- city VARCHAR(50) NOT NULL,
- country_id SMALLINT UNSIGNED NOT NULL,
- last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (city_id),
- KEY idx_fk_country_id (country_id),
- CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country (country_id) ON DELETE RESTRICT ON UPDATE CASCADE
-)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Table structure for table `country`
---
-
-CREATE TABLE country (
- country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
- country VARCHAR(50) NOT NULL,
- last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (country_id)
-)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Table structure for table `customer`
---
-
-CREATE TABLE customer (
- customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
- store_id TINYINT UNSIGNED NOT NULL,
- first_name VARCHAR(45) NOT NULL,
- last_name VARCHAR(45) NOT NULL,
- email VARCHAR(50) DEFAULT NULL,
- address_id SMALLINT UNSIGNED NOT NULL,
- active BOOLEAN NOT NULL DEFAULT TRUE,
- create_date DATETIME NOT NULL,
- last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (customer_id),
- KEY idx_fk_store_id (store_id),
- KEY idx_fk_address_id (address_id),
- KEY idx_last_name (last_name),
- CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE,
- CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE
-)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Table structure for table `film`
---
-
-CREATE TABLE film (
- film_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
- title VARCHAR(255) NOT NULL,
- description TEXT DEFAULT NULL,
- release_year YEAR DEFAULT NULL,
- language_id TINYINT UNSIGNED NOT NULL,
- original_language_id TINYINT UNSIGNED DEFAULT NULL,
- rental_duration TINYINT UNSIGNED NOT NULL DEFAULT 3,
- rental_rate DECIMAL(4,2) NOT NULL DEFAULT 4.99,
- length SMALLINT UNSIGNED DEFAULT NULL,
- replacement_cost DECIMAL(5,2) NOT NULL DEFAULT 19.99,
- rating ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G',
- special_features SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
- last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (film_id),
- KEY idx_title (title),
- KEY idx_fk_language_id (language_id),
- KEY idx_fk_original_language_id (original_language_id),
- CONSTRAINT fk_film_language FOREIGN KEY (language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE,
- CONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE
-)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Table structure for table `film_actor`
---
-
-CREATE TABLE film_actor (
- actor_id SMALLINT UNSIGNED NOT NULL,
- film_id SMALLINT UNSIGNED NOT NULL,
- last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (actor_id,film_id),
- KEY idx_fk_film_id (`film_id`),
- CONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE RESTRICT ON UPDATE CASCADE,
- CONSTRAINT fk_film_actor_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE
-)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Table structure for table `film_category`
---
-
-CREATE TABLE film_category (
- film_id SMALLINT UNSIGNED NOT NULL,
- category_id TINYINT UNSIGNED NOT NULL,
- last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (film_id, category_id),
- CONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE,
- CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category (category_id) ON DELETE RESTRICT ON UPDATE CASCADE
-)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Table structure for table `film_text`
---
-
-CREATE TABLE film_text (
- film_id SMALLINT NOT NULL,
- title VARCHAR(255) NOT NULL,
- description TEXT,
- PRIMARY KEY (film_id),
- FULLTEXT KEY idx_title_description (title,description)
-)ENGINE=MyISAM DEFAULT CHARSET=utf8;
-
---
--- Triggers for loading film_text from film
---
-
-DELIMITER ;;
-CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
- INSERT INTO film_text (film_id, title, description)
- VALUES (new.film_id, new.title, new.description);
- END;;
-
-
-CREATE TRIGGER `upd_film` AFTER UPDATE ON `film` FOR EACH ROW BEGIN
- IF (old.title != new.title) or (old.description != new.description)
- THEN
- UPDATE film_text
- SET title=new.title,
- description=new.description,
- film_id=new.film_id
- WHERE film_id=old.film_id;
- END IF;
- END;;
-
-
-CREATE TRIGGER `del_film` AFTER DELETE ON `film` FOR EACH ROW BEGIN
- DELETE FROM film_text WHERE film_id = old.film_id;
- END;;
-
-DELIMITER ;
-
---
--- Table structure for table `inventory`
---
-
-CREATE TABLE inventory (
- inventory_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
- film_id SMALLINT UNSIGNED NOT NULL,
- store_id TINYINT UNSIGNED NOT NULL,
- last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (inventory_id),
- KEY idx_fk_film_id (film_id),
- KEY idx_store_id_film_id (store_id,film_id),
- CONSTRAINT fk_inventory_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE,
- CONSTRAINT fk_inventory_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE
-)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Table structure for table `language`
---
-
-CREATE TABLE language (
- language_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
- name CHAR(20) NOT NULL,
- last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (language_id)
-)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Table structure for table `payment`
---
-
-CREATE TABLE payment (
- payment_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
- customer_id SMALLINT UNSIGNED NOT NULL,
- staff_id TINYINT UNSIGNED NOT NULL,
- rental_id INT DEFAULT NULL,
- amount DECIMAL(5,2) NOT NULL,
- payment_date DATETIME NOT NULL,
- last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (payment_id),
- KEY idx_fk_staff_id (staff_id),
- KEY idx_fk_customer_id (customer_id),
- CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental (rental_id) ON DELETE SET NULL ON UPDATE CASCADE,
- CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE,
- CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE
-)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-
---
--- Table structure for table `rental`
---
-
-CREATE TABLE rental (
- rental_id INT NOT NULL AUTO_INCREMENT,
- rental_date DATETIME NOT NULL,
- inventory_id MEDIUMINT UNSIGNED NOT NULL,
- customer_id SMALLINT UNSIGNED NOT NULL,
- return_date DATETIME DEFAULT NULL,
- staff_id TINYINT UNSIGNED NOT NULL,
- last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (rental_id),
- UNIQUE KEY (rental_date,inventory_id,customer_id),
- KEY idx_fk_inventory_id (inventory_id),
- KEY idx_fk_customer_id (customer_id),
- KEY idx_fk_staff_id (staff_id),
- CONSTRAINT fk_rental_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE,
- CONSTRAINT fk_rental_inventory FOREIGN KEY (inventory_id) REFERENCES inventory (inventory_id) ON DELETE RESTRICT ON UPDATE CASCADE,
- CONSTRAINT fk_rental_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE
-)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Trigger to enforce rental_date on INSERT
---
-
-CREATE TRIGGER rental_date BEFORE INSERT ON rental
- FOR EACH ROW SET NEW.rental_date = NOW();
-
---
--- Table structure for table `staff`
---
-
-CREATE TABLE staff (
- staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
- first_name VARCHAR(45) NOT NULL,
- last_name VARCHAR(45) NOT NULL,
- address_id SMALLINT UNSIGNED NOT NULL,
- picture BLOB DEFAULT NULL,
- email VARCHAR(50) DEFAULT NULL,
- store_id TINYINT UNSIGNED NOT NULL,
- active BOOLEAN NOT NULL DEFAULT TRUE,
- username VARCHAR(16) NOT NULL,
- password VARCHAR(40) BINARY DEFAULT NULL,
- last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (staff_id),
- KEY idx_fk_store_id (store_id),
- KEY idx_fk_address_id (address_id),
- CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE,
- CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE
-)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Table structure for table `store`
---
-
-CREATE TABLE store (
- store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
- manager_staff_id TINYINT UNSIGNED NOT NULL,
- address_id SMALLINT UNSIGNED NOT NULL,
- last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (store_id),
- UNIQUE KEY idx_unique_manager (manager_staff_id),
- KEY idx_fk_address_id (address_id),
- CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE,
- CONSTRAINT fk_store_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE
-)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- View structure for view `customer_list`
---
-
-CREATE VIEW customer_list
-AS
-SELECT cu.customer_id AS ID, CONCAT(cu.first_name, _utf8' ', cu.last_name) AS name, a.address AS address, a.postal_code AS `zip code`,
- a.phone AS phone, city.city AS city, country.country AS country, IF(cu.active, _utf8'active',_utf8'') AS notes, cu.store_id AS SID
-FROM customer AS cu JOIN address AS a ON cu.address_id = a.address_id JOIN city ON a.city_id = city.city_id
- JOIN country ON city.country_id = country.country_id;
-
---
--- View structure for view `film_list`
---
-
-CREATE VIEW film_list
-AS
-SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
- film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(actor.first_name, _utf8' ', actor.last_name) SEPARATOR ', ') AS actors
-FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
- JOIN film_actor ON film.film_id = film_actor.film_id
- JOIN actor ON film_actor.actor_id = actor.actor_id
-GROUP BY film.film_id;
-
---
--- View structure for view `nicer_but_slower_film_list`
---
-
-CREATE VIEW nicer_but_slower_film_list
-AS
-SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
- film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(CONCAT(UCASE(SUBSTR(actor.first_name,1,1)),
- LCASE(SUBSTR(actor.first_name,2,LENGTH(actor.first_name))),_utf8' ',CONCAT(UCASE(SUBSTR(actor.last_name,1,1)),
- LCASE(SUBSTR(actor.last_name,2,LENGTH(actor.last_name)))))) SEPARATOR ', ') AS actors
-FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
- JOIN film_actor ON film.film_id = film_actor.film_id
- JOIN actor ON film_actor.actor_id = actor.actor_id
-GROUP BY film.film_id;
-
---
--- View structure for view `staff_list`
---
-
-CREATE VIEW staff_list
-AS
-SELECT s.staff_id AS ID, CONCAT(s.first_name, _utf8' ', s.last_name) AS name, a.address AS address, a.postal_code AS `zip code`, a.phone AS phone,
- city.city AS city, country.country AS country, s.store_id AS SID
-FROM staff AS s JOIN address AS a ON s.address_id = a.address_id JOIN city ON a.city_id = city.city_id
- JOIN country ON city.country_id = country.country_id;
-
---
--- View structure for view `sales_by_store`
---
-
-CREATE VIEW sales_by_store
-AS
-SELECT
-CONCAT(c.city, _utf8',', cy.country) AS store
-, CONCAT(m.first_name, _utf8' ', m.last_name) AS manager
-, SUM(p.amount) AS total_sales
-FROM payment AS p
-INNER JOIN rental AS r ON p.rental_id = r.rental_id
-INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
-INNER JOIN store AS s ON i.store_id = s.store_id
-INNER JOIN address AS a ON s.address_id = a.address_id
-INNER JOIN city AS c ON a.city_id = c.city_id
-INNER JOIN country AS cy ON c.country_id = cy.country_id
-INNER JOIN staff AS m ON s.manager_staff_id
-GROUP BY s.store_id
-ORDER BY cy.country, c.city;
-
---
--- View structure for view `sales_by_film_category`
---
--- Note that total sales will add up to >100% because
--- some titles belong to more than 1 category
---
-
-CREATE VIEW sales_by_film_category
-AS
-SELECT
-c.name AS category
-, SUM(p.amount) AS total_sales
-FROM payment AS p
-INNER JOIN rental AS r ON p.rental_id = r.rental_id
-INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
-INNER JOIN film AS f ON i.film_id = f.film_id
-INNER JOIN film_category AS fc ON f.film_id = fc.film_id
-INNER JOIN category AS c ON fc.category_id = c.category_id
-GROUP BY c.name
-ORDER BY total_sales DESC;
-
---
--- Procedure structure for procedure `rewards_report`
---
-
-DELIMITER //
-
-CREATE PROCEDURE rewards_report (
- IN min_monthly_purchases TINYINT UNSIGNED
- , IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED
- , OUT count_rewardees INT
-)
-LANGUAGE SQL
-NOT DETERMINISTIC
-READS SQL DATA
-SQL SECURITY DEFINER
-COMMENT 'Provides a customizable report on best customers'
-proc: BEGIN
-
- DECLARE last_month_start DATE;
- DECLARE last_month_end DATE;
-
- /* Some sanity checks... */
- IF min_monthly_purchases = 0 THEN
- SELECT 'Minimum monthly purchases parameter must be > 0';
- LEAVE proc;
- END IF;
- IF min_dollar_amount_purchased = 0.00 THEN
- SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
- LEAVE proc;
- END IF;
-
- /* Determine start and end time periods */
- SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
- SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
- SET last_month_end = LAST_DAY(last_month_start);
-
- /*
- Create a temporary storage area for
- Customer IDs.
- */
- CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
-
- /*
- Find all customers meeting the
- monthly purchase requirements
- */
- INSERT INTO tmpCustomer (customer_id)
- SELECT p.customer_id
- FROM payment AS p
- WHERE p.payment_date BETWEEN last_month_start AND last_month_end
- GROUP BY customer_id
- HAVING SUM(p.amount) > min_dollar_amount_purchased
- AND COUNT(customer_id) > min_monthly_purchases;
-
- /* Populate OUT parameter with count of found customers */
- SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
-
- /*
- Output ALL customer information of matching rewardees.
- Customize output as needed.
- */
- SELECT c.*
- FROM tmpCustomer AS t
- INNER JOIN customer AS c ON t.customer_id = c.customer_id;
-
- /* Clean up */
- DROP TABLE tmpCustomer;
-END //
-
-DELIMITER ;
-
-DELIMITER $$
-
-CREATE FUNCTION get_customer_balance(p_customer_id INT, p_effective_date DATETIME) RETURNS DECIMAL(5,2)
- DETERMINISTIC
-BEGIN
-
- #OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE
- #THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:
- # 1) RENTAL FEES FOR ALL PREVIOUS RENTALS
- # 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE
- # 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST
- # 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED
-
- DECLARE v_rentfees DECIMAL(5,2); #FEES PAID TO RENT THE VIDEOS INITIALLY
- DECLARE v_overfees INTEGER; #LATE FEES FOR PRIOR RENTALS
- DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY
-
- SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
- FROM film, inventory, rental
- WHERE film.film_id = inventory.film_id
- AND inventory.inventory_id = rental.inventory_id
- AND rental.rental_date <= p_effective_date
- AND rental.customer_id = p_customer_id;
-
- SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
- ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees
- FROM rental, inventory, film
- WHERE film.film_id = inventory.film_id
- AND inventory.inventory_id = rental.inventory_id
- AND rental.rental_date <= p_effective_date
- AND rental.customer_id = p_customer_id;
-
-
- SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
- FROM payment
-
- WHERE payment.payment_date <= p_effective_date
- AND payment.customer_id = p_customer_id;
-
- RETURN v_rentfees + v_overfees - v_payments;
-END $$
-
-DELIMITER ;
-
-DELIMITER $$
-
-CREATE PROCEDURE film_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
-BEGIN
- SELECT inventory_id
- FROM inventory
- WHERE film_id = p_film_id
- AND store_id = p_store_id
- AND inventory_in_stock(inventory_id);
-
- SELECT FOUND_ROWS() INTO p_film_count;
-END $$
-
-DELIMITER ;
-
-DELIMITER $$
-
-CREATE PROCEDURE film_not_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
-BEGIN
- SELECT inventory_id
- FROM inventory
- WHERE film_id = p_film_id
- AND store_id = p_store_id
- AND NOT inventory_in_stock(inventory_id);
-
- SELECT FOUND_ROWS() INTO p_film_count;
-END $$
-
-DELIMITER ;
-
-DELIMITER $$
-
-CREATE FUNCTION inventory_held_by_customer(p_inventory_id INT) RETURNS INT
-BEGIN
- DECLARE v_customer_id INT;
- DECLARE CONTINUE HANDLER FOR 1329 RETURN NULL;
-
- SELECT customer_id INTO v_customer_id
- FROM rental
- WHERE return_date IS NULL;
-
- RETURN v_customer_id;
-END $$
-
-DELIMITER ;
-
-DELIMITER $$
-
-CREATE FUNCTION inventory_in_stock(p_inventory_id INT) RETURNS BOOLEAN
-BEGIN
- DECLARE v_rentals INT;
- DECLARE v_out INT;
-
- #AN ITEM IS IN-STOCK IF EITHER THERE ARE NO ROWS IN THE rental TABLE
- #FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED
-
- SELECT COUNT(*) INTO v_rentals
- FROM rental
- WHERE inventory_id = p_inventory_id;
-
- IF v_rentals = 0 THEN
- RETURN TRUE;
- END IF;
-
- SELECT COUNT(rental_id) INTO v_out
- FROM inventory LEFT JOIN rental USING(inventory_id)
- WHERE inventory.inventory_id = p_inventory_id
- AND rental.return_date IS NULL;
-
- IF v_out > 0 THEN
- RETURN FALSE;
- ELSE
- RETURN TRUE;
- END IF;
-END $$
-
-DELIMITER ;
-
-SET SQL_MODE=@OLD_SQL_MODE;
-SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
-SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-
-
+-- Sakila Sample Database Schema
+-- Version 0.6
+-- Copyright 2006 MySQL AB
+
+SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
+SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
+SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
+
+DROP SCHEMA IF EXISTS sakila;
+CREATE SCHEMA sakila;
+USE sakila;
+
+--
+-- Table structure for table `actor`
+--
+
+CREATE TABLE actor (
+ actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
+ first_name VARCHAR(45) NOT NULL,
+ last_name VARCHAR(45) NOT NULL,
+ last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (actor_id),
+ KEY idx_actor_last_name (last_name)
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `address`
+--
+
+CREATE TABLE address (
+ address_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
+ address VARCHAR(50) NOT NULL,
+ address2 VARCHAR(50) DEFAULT NULL,
+ district VARCHAR(20) NOT NULL,
+ city_id SMALLINT UNSIGNED NOT NULL,
+ postal_code VARCHAR(10) DEFAULT NULL,
+ phone VARCHAR(20) NOT NULL,
+ last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (address_id),
+ KEY idx_fk_city_id (city_id),
+ CONSTRAINT `fk_address_city` FOREIGN KEY (city_id) REFERENCES city (city_id) ON DELETE RESTRICT ON UPDATE CASCADE
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `category`
+--
+
+CREATE TABLE category (
+ category_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
+ name VARCHAR(25) NOT NULL,
+ last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (category_id)
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `city`
+--
+
+CREATE TABLE city (
+ city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
+ city VARCHAR(50) NOT NULL,
+ country_id SMALLINT UNSIGNED NOT NULL,
+ last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (city_id),
+ KEY idx_fk_country_id (country_id),
+ CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country (country_id) ON DELETE RESTRICT ON UPDATE CASCADE
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `country`
+--
+
+CREATE TABLE country (
+ country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
+ country VARCHAR(50) NOT NULL,
+ last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (country_id)
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `customer`
+--
+
+CREATE TABLE customer (
+ customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
+ store_id TINYINT UNSIGNED NOT NULL,
+ first_name VARCHAR(45) NOT NULL,
+ last_name VARCHAR(45) NOT NULL,
+ email VARCHAR(50) DEFAULT NULL,
+ address_id SMALLINT UNSIGNED NOT NULL,
+ active BOOLEAN NOT NULL DEFAULT TRUE,
+ create_date DATETIME NOT NULL,
+ last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (customer_id),
+ KEY idx_fk_store_id (store_id),
+ KEY idx_fk_address_id (address_id),
+ KEY idx_last_name (last_name),
+ CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE,
+ CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `film`
+--
+
+CREATE TABLE film (
+ film_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
+ title VARCHAR(255) NOT NULL,
+ description TEXT DEFAULT NULL,
+ release_year YEAR DEFAULT NULL,
+ language_id TINYINT UNSIGNED NOT NULL,
+ original_language_id TINYINT UNSIGNED DEFAULT NULL,
+ rental_duration TINYINT UNSIGNED NOT NULL DEFAULT 3,
+ rental_rate DECIMAL(4,2) NOT NULL DEFAULT 4.99,
+ length SMALLINT UNSIGNED DEFAULT NULL,
+ replacement_cost DECIMAL(5,2) NOT NULL DEFAULT 19.99,
+ rating ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G',
+ special_features SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
+ last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (film_id),
+ KEY idx_title (title),
+ KEY idx_fk_language_id (language_id),
+ KEY idx_fk_original_language_id (original_language_id),
+ CONSTRAINT fk_film_language FOREIGN KEY (language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE,
+ CONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `film_actor`
+--
+
+CREATE TABLE film_actor (
+ actor_id SMALLINT UNSIGNED NOT NULL,
+ film_id SMALLINT UNSIGNED NOT NULL,
+ last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (actor_id,film_id),
+ KEY idx_fk_film_id (`film_id`),
+ CONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE RESTRICT ON UPDATE CASCADE,
+ CONSTRAINT fk_film_actor_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `film_category`
+--
+
+CREATE TABLE film_category (
+ film_id SMALLINT UNSIGNED NOT NULL,
+ category_id TINYINT UNSIGNED NOT NULL,
+ last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (film_id, category_id),
+ CONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE,
+ CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category (category_id) ON DELETE RESTRICT ON UPDATE CASCADE
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `film_text`
+--
+
+CREATE TABLE film_text (
+ film_id SMALLINT NOT NULL,
+ title VARCHAR(255) NOT NULL,
+ description TEXT,
+ PRIMARY KEY (film_id),
+ FULLTEXT KEY idx_title_description (title,description)
+)ENGINE=MyISAM DEFAULT CHARSET=utf8;
+
+--
+-- Triggers for loading film_text from film
+--
+
+DELIMITER ;;
+CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
+ INSERT INTO film_text (film_id, title, description)
+ VALUES (new.film_id, new.title, new.description);
+ END;;
+
+
+CREATE TRIGGER `upd_film` AFTER UPDATE ON `film` FOR EACH ROW BEGIN
+ IF (old.title != new.title) or (old.description != new.description)
+ THEN
+ UPDATE film_text
+ SET title=new.title,
+ description=new.description,
+ film_id=new.film_id
+ WHERE film_id=old.film_id;
+ END IF;
+ END;;
+
+
+CREATE TRIGGER `del_film` AFTER DELETE ON `film` FOR EACH ROW BEGIN
+ DELETE FROM film_text WHERE film_id = old.film_id;
+ END;;
+
+DELIMITER ;
+
+--
+-- Table structure for table `inventory`
+--
+
+CREATE TABLE inventory (
+ inventory_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
+ film_id SMALLINT UNSIGNED NOT NULL,
+ store_id TINYINT UNSIGNED NOT NULL,
+ last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (inventory_id),
+ KEY idx_fk_film_id (film_id),
+ KEY idx_store_id_film_id (store_id,film_id),
+ CONSTRAINT fk_inventory_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE,
+ CONSTRAINT fk_inventory_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `language`
+--
+
+CREATE TABLE language (
+ language_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
+ name CHAR(20) NOT NULL,
+ last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (language_id)
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `payment`
+--
+
+CREATE TABLE payment (
+ payment_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
+ customer_id SMALLINT UNSIGNED NOT NULL,
+ staff_id TINYINT UNSIGNED NOT NULL,
+ rental_id INT DEFAULT NULL,
+ amount DECIMAL(5,2) NOT NULL,
+ payment_date DATETIME NOT NULL,
+ last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (payment_id),
+ KEY idx_fk_staff_id (staff_id),
+ KEY idx_fk_customer_id (customer_id),
+ CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental (rental_id) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE,
+ CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+
+--
+-- Table structure for table `rental`
+--
+
+CREATE TABLE rental (
+ rental_id INT NOT NULL AUTO_INCREMENT,
+ rental_date DATETIME NOT NULL,
+ inventory_id MEDIUMINT UNSIGNED NOT NULL,
+ customer_id SMALLINT UNSIGNED NOT NULL,
+ return_date DATETIME DEFAULT NULL,
+ staff_id TINYINT UNSIGNED NOT NULL,
+ last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (rental_id),
+ UNIQUE KEY (rental_date,inventory_id,customer_id),
+ KEY idx_fk_inventory_id (inventory_id),
+ KEY idx_fk_customer_id (customer_id),
+ KEY idx_fk_staff_id (staff_id),
+ CONSTRAINT fk_rental_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE,
+ CONSTRAINT fk_rental_inventory FOREIGN KEY (inventory_id) REFERENCES inventory (inventory_id) ON DELETE RESTRICT ON UPDATE CASCADE,
+ CONSTRAINT fk_rental_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `staff`
+--
+
+CREATE TABLE staff (
+ staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
+ first_name VARCHAR(45) NOT NULL,
+ last_name VARCHAR(45) NOT NULL,
+ address_id SMALLINT UNSIGNED NOT NULL,
+ picture BLOB DEFAULT NULL,
+ email VARCHAR(50) DEFAULT NULL,
+ store_id TINYINT UNSIGNED NOT NULL,
+ active BOOLEAN NOT NULL DEFAULT TRUE,
+ username VARCHAR(16) NOT NULL,
+ password VARCHAR(40) BINARY DEFAULT NULL,
+ last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (staff_id),
+ KEY idx_fk_store_id (store_id),
+ KEY idx_fk_address_id (address_id),
+ CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE,
+ CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `store`
+--
+
+CREATE TABLE store (
+ store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
+ manager_staff_id TINYINT UNSIGNED NOT NULL,
+ address_id SMALLINT UNSIGNED NOT NULL,
+ last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (store_id),
+ UNIQUE KEY idx_unique_manager (manager_staff_id),
+ KEY idx_fk_address_id (address_id),
+ CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE,
+ CONSTRAINT fk_store_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- View structure for view `customer_list`
+--
+
+CREATE VIEW customer_list
+AS
+SELECT cu.customer_id AS ID, CONCAT(cu.first_name, _utf8' ', cu.last_name) AS name, a.address AS address, a.postal_code AS `zip code`,
+ a.phone AS phone, city.city AS city, country.country AS country, IF(cu.active, _utf8'active',_utf8'') AS notes, cu.store_id AS SID
+FROM customer AS cu JOIN address AS a ON cu.address_id = a.address_id JOIN city ON a.city_id = city.city_id
+ JOIN country ON city.country_id = country.country_id;
+
+--
+-- View structure for view `film_list`
+--
+
+CREATE VIEW film_list
+AS
+SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
+ film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(actor.first_name, _utf8' ', actor.last_name) SEPARATOR ', ') AS actors
+FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
+ JOIN film_actor ON film.film_id = film_actor.film_id
+ JOIN actor ON film_actor.actor_id = actor.actor_id
+GROUP BY film.film_id;
+
+--
+-- View structure for view `nicer_but_slower_film_list`
+--
+
+CREATE VIEW nicer_but_slower_film_list
+AS
+SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
+ film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(CONCAT(UCASE(SUBSTR(actor.first_name,1,1)),
+ LCASE(SUBSTR(actor.first_name,2,LENGTH(actor.first_name))),_utf8' ',CONCAT(UCASE(SUBSTR(actor.last_name,1,1)),
+ LCASE(SUBSTR(actor.last_name,2,LENGTH(actor.last_name)))))) SEPARATOR ', ') AS actors
+FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
+ JOIN film_actor ON film.film_id = film_actor.film_id
+ JOIN actor ON film_actor.actor_id = actor.actor_id
+GROUP BY film.film_id;
+
+--
+-- View structure for view `staff_list`
+--
+
+CREATE VIEW staff_list
+AS
+SELECT s.staff_id AS ID, CONCAT(s.first_name, _utf8' ', s.last_name) AS name, a.address AS address, a.postal_code AS `zip code`, a.phone AS phone,
+ city.city AS city, country.country AS country, s.store_id AS SID
+FROM staff AS s JOIN address AS a ON s.address_id = a.address_id JOIN city ON a.city_id = city.city_id
+ JOIN country ON city.country_id = country.country_id;
+
+--
+-- View structure for view `sales_by_store`
+--
+
+CREATE VIEW sales_by_store
+AS
+SELECT
+CONCAT(c.city, _utf8',', cy.country) AS store
+, CONCAT(m.first_name, _utf8' ', m.last_name) AS manager
+, SUM(p.amount) AS total_sales
+FROM payment AS p
+INNER JOIN rental AS r ON p.rental_id = r.rental_id
+INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
+INNER JOIN store AS s ON i.store_id = s.store_id
+INNER JOIN address AS a ON s.address_id = a.address_id
+INNER JOIN city AS c ON a.city_id = c.city_id
+INNER JOIN country AS cy ON c.country_id = cy.country_id
+INNER JOIN staff AS m ON s.manager_staff_id = m.mager_staff_id
+GROUP BY s.store_id
+ORDER BY cy.country, c.city;
+
+--
+-- View structure for view `sales_by_film_category`
+--
+-- Note that total sales will add up to >100% because
+-- some titles belong to more than 1 category
+--
+
+CREATE VIEW sales_by_film_category
+AS
+SELECT
+c.name AS category
+, SUM(p.amount) AS total_sales
+FROM payment AS p
+INNER JOIN rental AS r ON p.rental_id = r.rental_id
+INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
+INNER JOIN film AS f ON i.film_id = f.film_id
+INNER JOIN film_category AS fc ON f.film_id = fc.film_id
+INNER JOIN category AS c ON fc.category_id = c.category_id
+GROUP BY c.name
+ORDER BY total_sales DESC;
+
+--
+-- Procedure structure for procedure `rewards_report`
+--
+
+DELIMITER //
+
+CREATE PROCEDURE rewards_report (
+ IN min_monthly_purchases TINYINT UNSIGNED
+ , IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED
+ , OUT count_rewardees INT
+)
+LANGUAGE SQL
+NOT DETERMINISTIC
+READS SQL DATA
+SQL SECURITY DEFINER
+COMMENT 'Provides a customizable report on best customers'
+proc: BEGIN
+
+ DECLARE last_month_start DATE;
+ DECLARE last_month_end DATE;
+
+ /* Some sanity checks... */
+ IF min_monthly_purchases = 0 THEN
+ SELECT 'Minimum monthly purchases parameter must be > 0';
+ LEAVE proc;
+ END IF;
+ IF min_dollar_amount_purchased = 0.00 THEN
+ SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
+ LEAVE proc;
+ END IF;
+
+ /* Determine start and end time periods */
+ SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
+ SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
+ SET last_month_end = LAST_DAY(last_month_start);
+
+ /*
+ Create a temporary storage area for
+ Customer IDs.
+ */
+ CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
+
+ /*
+ Find all customers meeting the
+ monthly purchase requirements
+ */
+ INSERT INTO tmpCustomer (customer_id)
+ SELECT p.customer_id
+ FROM payment AS p
+ WHERE p.payment_date BETWEEN last_month_start AND last_month_end
+ GROUP BY customer_id
+ HAVING SUM(p.amount) > min_dollar_amount_purchased
+ AND COUNT(customer_id) > min_monthly_purchases;
+
+ /* Populate OUT parameter with count of found customers */
+ SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
+
+ /*
+ Output ALL customer information of matching rewardees.
+ Customize output as needed.
+ */
+ SELECT c.*
+ FROM tmpCustomer AS t
+ INNER JOIN customer AS c ON t.customer_id = c.customer_id;
+
+ /* Clean up */
+ DROP TABLE tmpCustomer;
+END //
+
+DELIMITER ;
+
+DELIMITER $$
+
+CREATE FUNCTION get_customer_balance(p_customer_id INT, p_effective_date DATETIME) RETURNS DECIMAL(5,2)
+ DETERMINISTIC
+BEGIN
+
+ #OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE
+ #THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:
+ # 1) RENTAL FEES FOR ALL PREVIOUS RENTALS
+ # 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE
+ # 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST
+ # 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED
+
+ DECLARE v_rentfees DECIMAL(5,2); #FEES PAID TO RENT THE VIDEOS INITIALLY
+ DECLARE v_overfees INTEGER; #LATE FEES FOR PRIOR RENTALS
+ DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY
+
+ SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
+ FROM film, inventory, rental
+ WHERE film.film_id = inventory.film_id
+ AND inventory.inventory_id = rental.inventory_id
+ AND rental.rental_date <= p_effective_date
+ AND rental.customer_id = p_customer_id;
+
+ SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
+ ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees
+ FROM rental, inventory, film
+ WHERE film.film_id = inventory.film_id
+ AND inventory.inventory_id = rental.inventory_id
+ AND rental.rental_date <= p_effective_date
+ AND rental.customer_id = p_customer_id;
+
+
+ SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
+ FROM payment
+
+ WHERE payment.payment_date <= p_effective_date
+ AND payment.customer_id = p_customer_id;
+
+ RETURN v_rentfees + v_overfees - v_payments;
+END $$
+
+DELIMITER ;
+
+DELIMITER $$
+
+CREATE PROCEDURE film_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
+BEGIN
+ SELECT inventory_id
+ FROM inventory
+ WHERE film_id = p_film_id
+ AND store_id = p_store_id
+ AND inventory_in_stock(inventory_id);
+
+ SELECT FOUND_ROWS() INTO p_film_count;
+END $$
+
+DELIMITER ;
+
+DELIMITER $$
+
+CREATE PROCEDURE film_not_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
+BEGIN
+ SELECT inventory_id
+ FROM inventory
+ WHERE film_id = p_film_id
+ AND store_id = p_store_id
+ AND NOT inventory_in_stock(inventory_id);
+
+ SELECT FOUND_ROWS() INTO p_film_count;
+END $$
+
+DELIMITER ;
+
+DELIMITER $$
+
+CREATE FUNCTION inventory_held_by_customer(p_inventory_id INT) RETURNS INT
+BEGIN
+ DECLARE v_customer_id INT;
+ DECLARE CONTINUE HANDLER FOR 1329 RETURN NULL;
+
+ SELECT customer_id INTO v_customer_id
+ FROM rental
+ WHERE return_date IS NULL
+ AND inventory_id = p_inventory_id;
+
+ RETURN v_customer_id;
+END $$
+
+DELIMITER ;
+
+DELIMITER $$
+
+CREATE FUNCTION inventory_in_stock(p_inventory_id INT) RETURNS BOOLEAN
+BEGIN
+ DECLARE v_rentals INT;
+ DECLARE v_out INT;
+
+ #AN ITEM IS IN-STOCK IF EITHER THERE ARE NO ROWS IN THE rental TABLE
+ #FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED
+
+ SELECT COUNT(*) INTO v_rentals
+ FROM rental
+ WHERE inventory_id = p_inventory_id;
+
+ IF v_rentals = 0 THEN
+ RETURN TRUE;
+ END IF;
+
+ SELECT COUNT(rental_id) INTO v_out
+ FROM inventory LEFT JOIN rental USING(inventory_id)
+ WHERE inventory.inventory_id = p_inventory_id
+ AND rental.return_date IS NULL;
+
+ IF v_out > 0 THEN
+ RETURN FALSE;
+ ELSE
+ RETURN TRUE;
+ END IF;
+END $$
+
+DELIMITER ;
+
+SET SQL_MODE=@OLD_SQL_MODE;
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
+SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
+
+
Modified: trunk/sample-data/sakila/sakila.xml
===================================================================
--- trunk/sample-data/sakila/sakila.xml 2006-03-23 04:50:02 UTC (rev 1647)
+++ trunk/sample-data/sakila/sakila.xml 2006-03-23 05:15:17 UTC (rev 1648)
@@ -1306,24 +1306,626 @@
<title>Views</title>
+ <para>
+ This section describes the views that are included with the
+ &sampdb; sample database, in alphabetical order.
+ </para>
+
+ <section id="sakila-structure-views-customer_list">
+
+ <title>The customer_list View</title>
+
+ <para>
+ The <literal>customer_list</literal> view provides a list of
+ customers, with first name and last name concatenated together
+ and address information combined into a single view.
+ </para>
+
+ <para>
+ The customer_list view incorporates data from the customer,
+ address, city and country tables.
+ </para>
+
+ </section>
+
+ <section id="sakila-structure-views-film_list">
+
+ <title>The film_list View</title>
+
+ <para>
+ The <literal>film_list</literal> view contains a formatted
+ view of the film table, with a comma-separated list of the
+ film's actors.
+ </para>
+
+ <para>
+ The film_list view incorporates data from the film, category,
+ film_category, actor and film_actor tables.
+ </para>
+
+ </section>
+
+ <section id="sakila-structure-views-nicer_film_list">
+
+ <title>The nicer_but_slower_film_list View</title>
+
+ <para>
+ The <literal>nicer_but_slower_film_list</literal> view
+ contains a formatted view of the film table, with a
+ comma-separated list of the film's actors.
+ </para>
+
+ <para>
+ The nicer_but_slower_film_list view differs from the film_list
+ view in the list of actors. The lettercase of the actor names
+ is adjusted so that the first letter of each name is
+ capitalized rather than have the name in all-caps.
+ </para>
+
+ <para>
+ As indicated in its name, the nicer_but_slower_film_list
+ performs additional processing and therefore takes longer to
+ return data than the film_list view.
+ </para>
+
+ <para>
+ The nicer_but_slower_film_list view incorporates data from the
+ film, category, film_category, actor and film_actor tables.
+ </para>
+
+ </section>
+
+ <section id="sakila-structure-views-sales_by_film_category">
+
+ <title>The sales_by_film_category View</title>
+
+ <para>
+ The <literal>sales_by_film_category</literal> view provides a
+ list of total sales, broken down by individual film category.
+ </para>
+
+ <para>
+ Because a film can be listed in multiple categories, it is not
+ advisable to calculate aggregate sales by totalling the rows
+ of this view.
+ </para>
+
+ <para>
+ The sales_by_film_category view incorporates data from the
+ category, payment, rental, inventory, film, film_category and
+ category tables.
+ </para>
+
+ </section>
+
+ <section id="sakila-structure-views-sales_by_store">
+
+ <title>The sales_by_store View</title>
+
+ <para>
+ The <literal>sales_by_store</literal> view provides a list of
+ total sales, broken down by store.
+ </para>
+
+ <para>
+ The view returns the store location, manager name, and total
+ sales.
+ </para>
+
+ <para>
+ The sales_by_store view incorporates data from the city,
+ country, payment, rental, inventory, store, address and staff
+ tables.
+ </para>
+
+ </section>
+
+ <section id="sakila-structure-views-staff-list">
+
+ <title>The staff_list View</title>
+
+ <para>
+ The <literal>staff_list</literal> view provides a list of all
+ staff members, including address and store information.
+ </para>
+
+ <para>
+ The staff_list view incorporates data from the staff and
+ address tables.
+ </para>
+
+ </section>
+
</section>
<section id="sakila-structure-procedures">
<title>Stored Procedures</title>
+ <para>
+ This is a list of stored procedures included with the &sampdb;
+ sample database, in alphabetical order.
+ </para>
+
+ <para>
+ All parameters listed are IN parameters unless listed otherwise.
+ </para>
+
+ <section id="sakila-structure-procedures-film_in_stock">
+
+ <title>The film_in_stock Stored Procedure</title>
+
+ <bridgehead>Description</bridgehead>
+
+ <para>
+ The <literal>film_in_stock</literal> stored procedure is used
+ to determine if there are any copies of a given film in stock
+ at a given store.
+ </para>
+
+ <bridgehead>Parameters</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>p_film_id</literal> - The id of the film to be
+ checked, from the <literal>film_id</literal> column of the
+ <literal>film</literal> table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>p_store_id</literal> - The id of the store to
+ check for, from the <literal>store_id</literal> column of
+ the <literal>store</literal> table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>p_film_count</literal> - An OUT parameter that
+ returns a count of the copies of the film in stock.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <bridgehead>Return Values</bridgehead>
+
+ <para>
+ This function returns a table of inventory id numbers for the
+ copies of the film in stock.
+ </para>
+
+ <bridgehead>Sample Usage</bridgehead>
+
+<programlisting>
+mysql> CALL film_in_stock(1,1,@count);
++--------------+
+| inventory_id |
++--------------+
+| 1 |
+| 2 |
+| 3 |
+| 4 |
++--------------+
+4 rows in set (0.06 sec)
+
+Query OK, 0 rows affected (0.06 sec)
+
+mysql> SELECT @count;
++--------+
+| @count |
++--------+
+| 4 |
++--------+
+1 row in set (0.00 sec)
+
+mysql>
+</programlisting>
+
+ </section>
+
+ <section id="sakila-structure-procedures-film_not_in_stock">
+
+ <title>The film_not_in_stock Stored Procedure</title>
+
+ <bridgehead>Description</bridgehead>
+
+ <para>
+ The <literal>film_not_in_stock</literal> stored procedure is
+ used to determine if there are any copies of a given film not
+ in stock (rented out) at a given store.
+ </para>
+
+ <bridgehead>Parameters</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>p_film_id</literal> - The id of the film to be
+ checked, from the <literal>film_id</literal> column of the
+ <literal>film</literal> table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>p_store_id</literal> - The id of the store to
+ check for, from the <literal>store_id</literal> column of
+ the <literal>store</literal> table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>p_film_count</literal> - An OUT parameter that
+ returns a count of the copies of the film not in stock.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <bridgehead>Return Values</bridgehead>
+
+ <para>
+ This function returns a table of inventory id numbers for the
+ copies of the film not in stock.
+ </para>
+
+ <bridgehead>Sample Usage</bridgehead>
+
+<programlisting>
+mysql> CALL film_not_in_stock(2,2,@count);
++--------------+
+| inventory_id |
++--------------+
+| 9 |
++--------------+
+1 row in set (0.02 sec)
+
+Query OK, 0 rows affected (0.02 sec)
+
+mysql> SELECT @count;
++--------+
+| @count |
++--------+
+| 1 |
++--------+
+1 row in set (0.00 sec)
+</programlisting>
+
+ </section>
+
+ <section id="sakila-structure-procedures-rewards_report">
+
+ <title>The rewards_report Stored Procedure</title>
+
+ <bridgehead>Description</bridgehead>
+
+ <para>
+ The <literal>rewards_report</literal> stored procedure
+ generates a customizable list of the top customers for the
+ previous month.
+ </para>
+
+ <bridgehead>Parameters</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>min_monthly_purchases</literal> - The minimum
+ number of purchases/rentals a customer needed to make in
+ the last month to qualify.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>min_dollar_amount_purchased</literal> - The
+ minimum dollar amount a customer needed to spend in the
+ last month to qualify.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>count_rewardees</literal> - An OUT parameter that
+ returns a count of the customers who met the
+ qualifications specified.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <bridgehead>Return Values</bridgehead>
+
+ <para>
+ This function returns a table of customers who met the
+ qualifications specified. The table returned is of the same
+ structure as the
+ <link
+ linkend="sakila-structure-tables-customer">customer</link>
+ table.
+ </para>
+
+ <bridgehead>Sample Usage</bridgehead>
+
+<programlisting>
+mysql> CALL rewards_report(7,20.00,@count);
+
+...
+| 598 | 1 | WADE | DELVALLE | WADE.DELVALLE@stripped | 604 | 1 | 2006-02-24 10:48:30 | 2006-02-15 04:57:20 |
+| 599 | 2 | AUSTIN | CINTRON | AUSTIN.CINTRON@stripped | 605 | 1 | 2006-02-24 10:48:30 | 2006-02-15 04:57:20 |
+...
+
+42 rows in set (0.11 sec)
+
+Query OK, 0 rows affected (0.67 sec)
+
+mysql> SELECT @count;
++--------+
+| @count |
++--------+
+| 42 |
++--------+
+1 row in set (0.00 sec)
+</programlisting>
+
+ </section>
+
</section>
<section id="sakila-structure-functions">
<title>Stored Functions</title>
+ <para>
+ This section lists the stored functions included with the
+ &sampdb; sample database.
+ </para>
+
+ <section id="sakila-structure-functions-get_customer_balance">
+
+ <title>The get_customer_balance Function</title>
+
+ <para>
+ The <literal>get_customer_balance</literal> function returns
+ the current amount owing on a specified customer's account.
+ </para>
+
+ <bridgehead>Parameters</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>p_customer_id</literal> - The id of the customer
+ to check, from the <literal>customer_id</literal> column
+ of the <literal>customer</literal> table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>p_effective_date</literal> - The cutoff date for
+ items that will be applied to the balance. Any
+ rentals/payments/etc after this date are not counted.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <bridgehead>Return Values</bridgehead>
+
+ <para>
+ This functions returns the amount owing on the customer's
+ account.
+ </para>
+
+ <bridgehead>Sample Usage</bridgehead>
+
+<programlisting>
+mysql> SELECT get_customer_balance(298,NOW());
++---------------------------------+
+| get_customer_balance(298,NOW()) |
++---------------------------------+
+| 22.00 |
++---------------------------------+
+1 row in set (0.00 sec)
+</programlisting>
+
+ </section>
+
+ <section id="sakila-structure-functions-inventory_held_by_customer">
+
+ <title>The inventory_held_by_customer Function</title>
+
+ <para>
+ The <literal>inventory_held_by_customer</literal> function
+ returns the customer_id of the customer who has rented out the
+ specified inventory item.
+ </para>
+
+ <bridgehead>Parameters</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>p_inventory_id</literal> - The id of the
+ inventory item to be checked.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <bridgehead>Return Values</bridgehead>
+
+ <para>
+ This functions returns the customer_id of the customer who is
+ currently renting the item, or NULL if the item is in stock.
+ </para>
+
+ <bridgehead>Sample Usage</bridgehead>
+
+<programlisting>
+mysql> SELECT inventory_held_by_customer(8);
++-------------------------------+
+| inventory_held_by_customer(8) |
++-------------------------------+
+| NULL |
++-------------------------------+
+1 row in set (0.14 sec)
+
+mysql> SELECT inventory_held_by_customer(9);
++-------------------------------+
+| inventory_held_by_customer(9) |
++-------------------------------+
+| 366 |
++-------------------------------+
+1 row in set (0.00 sec)
+
+mysql>
+</programlisting>
+
+ </section>
+
+ <section id="sakila-structure-functions-inventory_in_stock">
+
+ <title>The inventory_in_stock Function</title>
+
+ <para>
+ The <literal>inventory_function</literal> function returns a
+ boolean value indicating whether the inventory item specified
+ is in stock.
+ </para>
+
+ <bridgehead>Parameters</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>p_inventory_id</literal> - The id of the
+ inventory item to be checked.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <bridgehead>Return Values</bridgehead>
+
+ <para>
+ This function returns TRUE or FALSE depending on whether the
+ item specified is in stock.
+ </para>
+
+ <bridgehead>Sample Usage</bridgehead>
+
+<programlisting>
+mysql> SELECT inventory_in_stock(9);
++-----------------------+
+| inventory_in_stock(9) |
++-----------------------+
+| 0 |
++-----------------------+
+1 row in set (0.03 sec)
+
+mysql> SELECT inventory_in_stock(8);
++-----------------------+
+| inventory_in_stock(8) |
++-----------------------+
+| 1 |
++-----------------------+
+1 row in set (0.00 sec)
+</programlisting>
+
+ </section>
+
</section>
<section id="sakila-structure-triggers">
<title>Triggers</title>
+ <para>
+ This section lists the triggers in the &sampdb; sample database.
+ </para>
+
+ <section id="sakila-structure-triggers-customer_create_date">
+
+ <title>The customer_create_date Trigger</title>
+
+ <para>
+ The <literal>customer_create_date</literal> trigger sets the
+ create_date column of the customer table to the current time
+ and date as rows are inserted.
+ </para>
+
+ </section>
+
+ <section id="sakila-structure-triggers-customer_create_date">
+
+ <title>The payment_date Trigger</title>
+
+ <para>
+ The <literal>payment_date</literal> trigger sets the
+ payment_date column of the payment table to the current time
+ and date as rows are inserted.
+ </para>
+
+ </section>
+
+ <section id="sakila-structure-triggers-rental_date">
+
+ <title>The rental_date Trigger</title>
+
+ <para>
+ The <literal>rental_date</literal> trigger sets the
+ rental_date column of the rental table to the current time and
+ date as rows are inserted.
+ </para>
+
+ </section>
+
+ <section id="sakila-structure-triggers-ins_film">
+
+ <title>The ins_film Trigger</title>
+
+ <para>
+ The <literal>ins_film</literal> trigger duplicates all INSERT
+ operations on the film table to the film_text table.
+ </para>
+
+ </section>
+
+ <section id="sakila-structure-triggers-upd_film">
+
+ <title>The upd_film Trigger</title>
+
+ <para>
+ The <literal>upd_film</literal> trigger duplicates all UPDATE
+ operations on the film table to the film_text table.
+ </para>
+
+ </section>
+
+ <section id="sakila-structure-triggers-del_film">
+
+ <title>The del_film Trigger</title>
+
+ <para>
+ The <literal>del_film</literal> trigger duplicates all DELETE
+ operations on the film table to the film_text table.
+ </para>
+
+ </section>
+
</section>
</section>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1648 - trunk/sample-data/sakila | mhillyer | 23 Mar |
