Author: mhillyer
Date: 2006-02-01 00:51:53 +0100 (Wed, 01 Feb 2006)
New Revision: 1159
Log:
Update Sakila schema, update changelog.
Modified:
trunk/sample-data/sakila/changelog.xml
trunk/sample-data/sakila/sakila-schema.sql
Modified: trunk/sample-data/sakila/changelog.xml
===================================================================
--- trunk/sample-data/sakila/changelog.xml 2006-01-31 23:38:29 UTC (rev 1158)
+++ trunk/sample-data/sakila/changelog.xml 2006-01-31 23:51:53 UTC (rev 1159)
@@ -1,223 +1,276 @@
-<?xml version="1.0" encoding="utf-8"?>
-<!DOCTYPE section PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
-"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
-[
- <!ENTITY sakila "Sakila">
- ]>
-<section id="sakila-news">
-
- <title>Sakila Change History</title>
-
- <section id="sakila-news-0.2">
-
- <title>Version 0.2</title>
-
- <itemizedlist>
-
- <listitem>
- <para>
- All tables have a <literal>last_update</literal> TIMESTAMP
- with traditional behavior (DEFAULT CURRENT_TIMESTAMP ON UPDATE
- CURRENT_TIMESTAMP)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>actor_id</literal> is now a SMALLINT
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>address_id</literal> is now a SMALLINT
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>category_id</literal> is now a TINYINT
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>city_id</literal> is now a SMALLINT
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>country_id</literal> is now a SMALLINT
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>customer_id</literal> is now a SMALLINT
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>first_name</literal>, <literal>last_name</literal>
- for customer table are now CHAR instead of VARCHAR
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>customer</literal> table now has email CHAR(50)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>create_date</literal> on customer table is now
- DATETIME (to accommodate last_update TIMESTAMP)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>customer</literal> table has a new ON INSERT trigger
- that enforces <literal>create_date</literal> column being set
- to NOW()
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>film_id</literal> is now SMALLINT
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>film.description</literal> now has DEFAULT NULL
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>film.release_year</literal> added with type YEAR
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>film.language_id</literal> and
- <literal>film.original_language_id</literal> added along with
- language table. For foreign films that may have been subbed.
- <literal>original_language_id</literal> can be NULL,
- <literal>language_id</literal> is NOT NULL
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>film.length</literal> is now SMALLINT
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>film.category_id</literal> column removed
- </para>
- </listitem>
-
- <listitem>
- <para>
- New table: <literal>film_category</literal> - allows for
- multiple categories per film
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>film_text.category_id</literal> column removed
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>inventory_id</literal> is now MEDIUMINT
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>payment_id</literal> is now SMALLINT
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>payment.payment_date</literal> is now DATETIME
- </para>
- </listitem>
-
- <listitem>
- <para>
- Trigger added to <literal>payment</literal> table to enforce
- that payment_date is set to NOW() upon INSERT
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>rental.rent_date</literal> is now rental.rental_date
- and is now DATETIME
- </para>
- </listitem>
-
- <listitem>
- <para>
- Trigger added to <literal>rental</literal> table to enforce
- that rental_date is set to NOW() upon INSERT
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>staff_id</literal> is now TINYINT
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>staff.email</literal> added (VARCHAR(50))
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>staff.username</literal> added (VARCHAR(16))
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>store_id</literal> is now TINYINT
- </para>
- </listitem>
-
- <listitem>
- <para>
- VIEW <literal>film_list</literal> updated to handle new
- film_category table
- </para>
- </listitem>
-
- <listitem>
- <para>
- VIEW <literal>nicer_but_slower_film_list</literal> updated to
- handle new film_category table
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
-</section>
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE section PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
+"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
+[
+ <!ENTITY sakila "Sakila">
+ ]>
+<section id="sakila-news">
+
+ <title>Sakila Change History</title>
+
+ <section id="sakila-news-0.3">
+
+ <title>Version 0.3</title>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Changed address.district to VARCHAR(20)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Changed customer.first_name to VARCHAR(45)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Changed customer.last_name to VARCHAR(45)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Changed customer.email to VARCHAR(50)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ payment.rental_id added - NULLable INT column
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Foreign key added for payment.rental_id to rental.rental_id
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ rental.rental_id added, INT Auto_Increment, made into
+ surrogate primary key, old primary key changed to UNIQUE key.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ </section>
+
+
+ <section id="sakila-news-0.2">
+
+ <title>Version 0.2</title>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ All tables have a <literal>last_update</literal> TIMESTAMP
+ with traditional behavior (DEFAULT CURRENT_TIMESTAMP ON UPDATE
+ CURRENT_TIMESTAMP)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>actor_id</literal> is now a SMALLINT
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>address_id</literal> is now a SMALLINT
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>category_id</literal> is now a TINYINT
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>city_id</literal> is now a SMALLINT
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>country_id</literal> is now a SMALLINT
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>customer_id</literal> is now a SMALLINT
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>first_name</literal>, <literal>last_name</literal>
+ for customer table are now CHAR instead of VARCHAR
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>customer</literal> table now has email CHAR(50)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>create_date</literal> on customer table is now
+ DATETIME (to accommodate last_update TIMESTAMP)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>customer</literal> table has a new ON INSERT trigger
+ that enforces <literal>create_date</literal> column being set
+ to NOW()
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>film_id</literal> is now SMALLINT
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>film.description</literal> now has DEFAULT NULL
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>film.release_year</literal> added with type YEAR
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>film.language_id</literal> and
+ <literal>film.original_language_id</literal> added along with
+ language table. For foreign films that may have been subbed.
+ <literal>original_language_id</literal> can be NULL,
+ <literal>language_id</literal> is NOT NULL
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>film.length</literal> is now SMALLINT
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>film.category_id</literal> column removed
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ New table: <literal>film_category</literal> - allows for
+ multiple categories per film
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>film_text.category_id</literal> column removed
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>inventory_id</literal> is now MEDIUMINT
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>payment_id</literal> is now SMALLINT
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>payment.payment_date</literal> is now DATETIME
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Trigger added to <literal>payment</literal> table to enforce
+ that payment_date is set to NOW() upon INSERT
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>rental.rent_date</literal> is now rental.rental_date
+ and is now DATETIME
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Trigger added to <literal>rental</literal> table to enforce
+ that rental_date is set to NOW() upon INSERT
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>staff_id</literal> is now TINYINT
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>staff.email</literal> added (VARCHAR(50))
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>staff.username</literal> added (VARCHAR(16))
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>store_id</literal> is now TINYINT
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ VIEW <literal>film_list</literal> updated to handle new
+ film_category table
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ VIEW <literal>nicer_but_slower_film_list</literal> updated to
+ handle new film_category table
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+</section>
Modified: trunk/sample-data/sakila/sakila-schema.sql
===================================================================
--- trunk/sample-data/sakila/sakila-schema.sql 2006-01-31 23:38:29 UTC (rev 1158)
+++ trunk/sample-data/sakila/sakila-schema.sql 2006-01-31 23:51:53 UTC (rev 1159)
@@ -1,5 +1,5 @@
-- Sakila Sample Database
--- Version 0.2
+-- Version 0.3
-- Copyright 2006 MySQL AB
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
@@ -32,7 +32,7 @@
address_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
address VARCHAR(50) NOT NULL,
address2 VARCHAR(50) DEFAULT NULL,
- district VARCHAR(50) NOT NULL,
+ district VARCHAR(20) NOT NULL,
city_id SMALLINT UNSIGNED NOT NULL,
postal_code VARCHAR(10) DEFAULT NULL,
phone VARCHAR(20) NOT NULL,
@@ -85,9 +85,9 @@
CREATE TABLE customer (
customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
store_id TINYINT UNSIGNED NOT NULL,
- first_name CHAR(45) NOT NULL,
- last_name CHAR(45) NOT NULL,
- email CHAR(50) DEFAULT 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,
@@ -236,12 +236,14 @@
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;
@@ -258,13 +260,15 @@
--
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_date,inventory_id,customer_id),
+ 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),
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1159 - trunk/sample-data/sakila | mhillyer | 1 Feb |
