List:Commits« Previous MessageNext Message »
From:mhillyer Date:January 18 2006 11:49pm
Subject:svn commit - mysqldoc@docsrva: r912 - trunk/sample-data/sakila
View as plain text  
Author: mhillyer
Date: 2006-01-19 00:49:44 +0100 (Thu, 19 Jan 2006)
New Revision: 912

Log:
Add sakila sample DB file to docsrva SVN tree.


Added:
   trunk/sample-data/sakila/sakila-schema.sql

Added: trunk/sample-data/sakila/sakila-schema.sql
===================================================================
--- trunk/sample-data/sakila/sakila-schema.sql	2006-01-18 23:47:45 UTC (rev 911)
+++ trunk/sample-data/sakila/sakila-schema.sql	2006-01-18 23:49:44 UTC (rev 912)
@@ -0,0 +1,307 @@
+-- Sakila Sample Database
+-- Version 0.1
+-- Copyright 2006 MySQL AB
+
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 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 INT UNSIGNED NOT NULL AUTO_INCREMENT,
+  first_name VARCHAR(45) NOT NULL,
+  last_name VARCHAR(45) NOT NULL,
+  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 INT UNSIGNED NOT NULL AUTO_INCREMENT,
+  address VARCHAR(50) NOT NULL,
+  address2 VARCHAR(50) DEFAULT NULL,
+  district VARCHAR(50) NOT NULL,
+  city_id INT UNSIGNED NOT NULL,
+  postal_code VARCHAR(10) NOT NULL,
+  phone VARCHAR(20) NOT NULL,
+  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 INT UNSIGNED NOT NULL AUTO_INCREMENT,
+  name VARCHAR(25) NOT NULL,
+  PRIMARY KEY  (category_id)
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `city`
+--
+
+CREATE TABLE city (
+  city_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
+  city VARCHAR(50) NOT NULL,
+  country_id INT UNSIGNED NOT NULL,
+  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 INT UNSIGNED NOT NULL AUTO_INCREMENT,
+  country VARCHAR(50) NOT NULL,
+  PRIMARY KEY  (country_id)
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `customer`
+--
+
+CREATE TABLE customer (
+  customer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
+  store_id INT UNSIGNED NOT NULL,
+  first_name VARCHAR(45) NOT NULL,
+  last_name VARCHAR(45) NOT NULL,
+  address_id INT UNSIGNED NOT NULL,
+  active BOOLEAN NOT NULL DEFAULT TRUE,
+  create_date TIMESTAMP NOT NULL DEFAULT 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 INT UNSIGNED NOT NULL AUTO_INCREMENT,
+  title VARCHAR(255) NOT NULL,
+  description TEXT,
+  category_id INT UNSIGNED DEFAULT NULL,
+  rental_duration TINYINT UNSIGNED NOT NULL DEFAULT 3,
+  rental_rate DECIMAL(4,2) NOT NULL DEFAULT 4.99,
+  length INT 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,
+  PRIMARY KEY  (film_id),
+  KEY idx_fk_category_id (category_id),
+  KEY idx_title (title),
+  CONSTRAINT fk_film_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_actor`
+--
+
+CREATE TABLE film_actor (
+  actor_id INT UNSIGNED NOT NULL,
+  film_id INT UNSIGNED NOT NULL,
+  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_text`
+--
+
+CREATE TABLE film_text (
+  film_id INT NOT NULL,
+  title VARCHAR(255) NOT NULL,
+  description TEXT,
+  category_id INT UNSIGNED NOT NULL,
+  PRIMARY KEY  (film_id),
+  FULLTEXT KEY idx_title_description (title,description)
+)ENGINE=MyISAM DEFAULT CHARSET=utf8;
+
+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) or (old.category_id != new.category_id)
+    THEN
+        UPDATE film_text
+            SET title=new.title,
+                description=new.description,
+                category_id=new.category_id,
+                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 INT UNSIGNED NOT NULL AUTO_INCREMENT,
+  film_id INT UNSIGNED NOT NULL,
+  store_id INT UNSIGNED NOT NULL,
+  PRIMARY KEY  (inventory_id),
+  KEY idx_fk_store_id (store_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 `payment`
+--
+
+CREATE TABLE payment (
+  payment_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
+  customer_id INT UNSIGNED NOT NULL,
+  staff_id INT UNSIGNED NOT NULL,
+  amount DECIMAL(5,2) NOT NULL,
+  payment_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  PRIMARY KEY  (payment_id),
+  KEY idx_fk_staff_id (staff_id),
+  KEY idx_fk_customer_id (customer_id),
+  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 (
+  rent_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  inventory_id INT UNSIGNED NOT NULL,
+  customer_id INT UNSIGNED NOT NULL,
+  return_date DATETIME DEFAULT NULL,
+  staff_id INT UNSIGNED NOT NULL,
+  PRIMARY KEY  (rent_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 INT UNSIGNED NOT NULL AUTO_INCREMENT,
+  first_name VARCHAR(45) NOT NULL,
+  last_name VARCHAR(45) NOT NULL,
+  address_id INT UNSIGNED NOT NULL,
+  picture BLOB DEFAULT NULL,
+  store_id INT UNSIGNED NOT NULL,
+  active BOOL NOT NULL DEFAULT TRUE,
+  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 INT UNSIGNED NOT NULL AUTO_INCREMENT,
+  manager_staff_id INT UNSIGNED NOT NULL,
+  address_id INT UNSIGNED NOT NULL,
+  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 JOIN film ON category.category_id = film.category_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 JOIN film ON category.category_id = film.category_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;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+
+

Thread
svn commit - mysqldoc@docsrva: r912 - trunk/sample-data/sakilamhillyer19 Jan