Sakila Sample Database

Relevant source files

The following files were used as context for generating this wiki page:

• README.md
• tests/sakila/Readme.md
• tests/sakila/mysql/sakila-schema.sql
• tests/sakila/postgres/sakila-schema.sql
• tests/sakila/sqlite/sqlite-sakila-schema.sql
• tests/discovery/mysql/schema.rs

The Sakila Sample Database serves as a standardized testing ground and example for the sea-schema library. It provides a complex, real-world schema featuring multiple tables, views, triggers, and stored procedures across different database engines including MySQL, PostgreSQL, and SQLite. Sources: README.md:44-46, tests/sakila/Readme.md

Within the project, Sakila is used to validate the library's primary capabilities: schema definition (def), discovery from live databases, and writing/exporting schemas into SQL statements or SeaQuery structures. It facilitates cross-database performance comparison and ensures that sea-schema can accurately map complex relationships and data types. Sources: README.md:21-28, tests/sakila/sqlite/sqlite-sakila-schema.sql:5-10

Schema Architecture and Entities

The database models a movie rental business, involving actors, films, inventory, customers, and staff. The architecture relies heavily on foreign key constraints to maintain referential integrity across the inventory and rental workflows.

Entity Relationship Overview

The following diagram illustrates the core relationships between films, actors, and categories as defined in the schema.

Core Tables Summary

Table	Description	Primary Key
actor	Details of actors in films.	actor_id
film	Core movie metadata (rating, features, etc.).	film_id
customer	Information on customers renting films.	customer_id
inventory	Specific copies of films held at stores.	inventory_id
rental	Records of film rental transactions.	rental_id
staff	Employees managing stores and rentals.	staff_id
payment	Financial transactions for rentals.	payment_id

Sources: tests/sakila/mysql/sakila-schema.sql:63-350, tests/discovery/mysql/schema.rs:16-1180

Implementation Details by Engine

MySQL Implementation

The MySQL version utilizes the InnoDB engine and supports specific features like FULLTEXT indexes on the film_text table and specialized data types like SET for special features and ENUM for ratings. Sources: tests/sakila/mysql/sakila-schema.sql:157-178, tests/discovery/mysql/schema.rs:655-685

PostgreSQL Implementation

The PostgreSQL version uses custom types (enums) like mpaa_rating and domains like year. It also implements partitioning for the film table based on ranges of film_id. Sources: tests/sakila/postgres/sakila-schema.sql:75-103, tests/sakila/postgres/sakila-schema.sql:149-165

SQLite Implementation

The SQLite port focuses on compatibility, utilizing AUTOINCREMENT for primary keys and triggers to simulate ON UPDATE CURRENT_TIMESTAMP behavior, which is not native to SQLite. Sources: tests/sakila/sqlite/sqlite-sakila-schema.sql:22-45

Business Logic: Procedures and Functions

The schema includes stored logic to handle complex calculations, such as rewards reporting and inventory checks.

Customer Balance Flow

The get_customer_balance function calculates late fees and initial rental costs while subtracting payments.

Inventory Management

Key functions manage the availability of stock:

• inventory_in_stock: Determines if a specific inventory item is currently available by checking if all rental records for that ID have a return_date. Sources: tests/sakila/mysql/sakila-schema.sql:572-598, tests/sakila/postgres/sakila-schema.sql:480-509
• film_in_stock: A procedure that returns a count of available copies for a specific film at a specific store. Sources: tests/sakila/mysql/sakila-schema.sql:531-547, tests/sakila/postgres/sakila-schema.sql:385-397

Data Discovery and Mapping

sea-schema uses the Sakila database to demonstrate how raw SQL definitions are mapped to internal Rust structures. For example, the film_actor table in MySQL is discovered as a TableDef with composite primary keys and foreign key constraints.

// Representative mapping of film_actor table
TableDef {
    info: TableInfo { name: "film_actor", .. },
    columns: [
        ColumnInfo { name: "actor_id", key: Primary, .. },
        ColumnInfo { name: "film_id", key: Primary, .. },
    ],
    foreign_keys: [
        ForeignKeyInfo {
            name: "fk_film_actor_actor",
            referenced_table: "actor",
            ..
        },
    ],
}

Sources: README.md:58-154, tests/discovery/mysql/schema.rs:770-850

Setup and Maintenance

The Sakila dataset requires specific environmental configurations for testing:

• Import: The dataset must be imported first before running discovery tests. Sources: tests/sakila/Readme.md:3
• PHP Dependencies: Generating JSON schema representations requires php-mysql and php-pgsql extensions. Sources: tests/sakila/Readme.md:8-12
• Dumping: The project suggests using mysqldump with flags like --no-data and --skip-opt to reproduce standardized SQL outputs for comparison. Sources: tests/sakila/Readme.md:20-25

In summary, the Sakila Sample Database is a critical asset within the sea-schema project, providing the structural complexity necessary to verify cross-engine schema management and discovery features.