jooq/sakila

The Sakila Database

The Sakila example database

The Sakila database is a nicely normalised database modelling a DVD rental store (for those of you old enough to remember what that is). Its design includes a few nice features:

• Many to many relationships
• Multiple paths between entities (e.g. film-inventory-rental-payment vs film-inventory-store-customer-payment) to practice joins
• Consistent naming of columns
  • Primary keys are called [tablename]_[id]
  • Foreign keys are called like their referenced primary key, if possible. This allows for using JOIN .. USING syntax where supported
  • Relationship tables do not have any surrogate keys but use composite primary keys
  • Every table has a last_update audit column
  • A generated data set of a reasonable size is available

ERD

With this database, we can try out some nice SQL queries, e.g. by using PostgreSQL syntax:

Actor with most films (ignoring ties)

SELECT first_name, last_name, count(*) films
FROM actor AS a
JOIN film_actor AS fa USING (actor_id)
GROUP BY actor_id, first_name, last_name
ORDER BY films DESC
LIMIT 1;

Yields:

first_name    last_name    films
--------------------------------
GINA          DEGENERES       42

Cumulative revenue of all stores

SELECT payment_date, amount, sum(amount) OVER (ORDER BY payment_date)
FROM (
  SELECT CAST(payment_date AS DATE) AS payment_date, SUM(amount) AS amount
  FROM payment
  GROUP BY CAST(payment_date AS DATE)
) p
ORDER BY payment_date;

Yields:

payment_date       amount         sum
-------------------------------------
2005-05-24          29.92       29.92
2005-05-25         573.63      603.55
2005-05-26         754.26     1357.81
2005-05-27         685.33     2043.14
2005-05-28         804.04     2847.18
2005-05-29         648.46     3495.64
2005-05-30         628.42     4124.06
2005-05-31         700.37     4824.43
2005-06-14          57.84     4882.27
2005-06-15        1376.52     6258.79
2005-06-16        1349.76     7608.55
2005-06-17        1332.75     8941.30
...

History

The Sakila example database was originally developed by Mike Hillyer of the MySQL AB documentation team. it was ported to other databases by DB Software Laboratory

License: BSD Copyright DB Software Laboratory http://www.etl-tools.com

sakila's Issues

Fix a few issues in the mysql-sakila-insert-data script

There are a few problems when trying to import the scripts in MySQL workbench:

• Add USE sakila in the script
• ALTER TABLE store CHANGE COLUMN manager_staff_id manager_staff_id TINYINT UNSIGNED NULL produces "Error Code: 3780. Referencing column 'manager_staff_id' and referenced column 'staff_id' in foreign key constraint 'fk_store_staff' are incompatible."

Add tests to the repository

Hi there,

I've just ported this over to Informix, and I'll happily submit a pull request shortly.

However, because I've had to guess at some of the behaviour in MySQL (which I've never used) I wondered if you had any tests, such as "SELECT function(x, y) should return 10 rows of data with these columns and these values". Even allowing for formatting differences it would still prove the basic functionality exists and is correct?

SQLite schema should use `INTEGER` not `INT` IDs

This allows SQLite to set ID columns as rowid which basically allows not setting IDs when inserting or performing similar operations.

Add a CockroachDB port of the PostgreSQL schema

It would be convenient if a port of the PostgreSQL schema existed also for CockroachDB. The syntax is almost the same, though some PG features aren't supported in CRDB.

mysql-sakila-schema should create views that work in strict mode

There are views like this:

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;

This view can't be fetched from, as there's an error in strict mode:

Error Code: 1055. Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sakila.category.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Use COPY command in PostgreSQL INSERT script

The CockroachDB port uses COPY: #2, just like the YugabyteDB port: https://raw.githubusercontent.com/jOOQ/sakila/main/yugabytedb-sakila-db/yugabytedb-sakila-insert-data.sql

We should do that as well for PostgreSQL

manager_staff_id in SQL Server Data Insert wrong datatype

ALTER TABLE store ADD CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id);

fails because the script earlier is altering the column to TINYINT, should just be INT when referencing that column.

Add a H2 port

Add a H2 port.

Triggers or stored procedures are not required.

Docker support

Hi @lukaseder,

First, Many thanks for this repository :)

I was reviewing this repository because I have interest in the sakila support for Postgres and I reviewed how the jooq demo is using the scripts about sakila:
https://github.com/jOOQ/demo/blob/main/jOOQ-demo-oss/jOOQ-demo-utils/src/main/java/org/jooq/demo/AbstractDemo.java

Using the scripts from this repo:
https://github.com/jOOQ/sakila/tree/main/postgres-sakila-db

I tried to create a Docker image with the data, but I was not able to do it because in the execution it raise some error:

FROM postgres:alpine
ADD postgres-sakila-db/postgres-sakila-schema.sql postgres-sakila-db/postgres-sakila-insert-data.sql /docker-entrypoint-initdb.d/

I tried with the scripts from the demo too without success:

FROM postgres:alpine
ADD \
  postgres/V1__postgres-sakila-schema-init.sql \
  postgres/V2__postgres-sakila-schema-tables.sql \
  postgres/V3__postgres-sakila-schema-functions.sql \
  postgres/V4__postgres-sakila-schema-constraints.sql \
  postgres/V5__postgres-sakila-insert-data.sql \
  /docker-entrypoint-initdb.d/

Does exist a way to populate to emulate the following repo:
https://github.com/fspacek/docker-postgres-sakila

The main motivation to this issue is run a docker image with the date prepopulated.

Many thanks in advance

Juan Antonio