Database Testing Suite - Pagila

Comprehensive SQL test suite for the Pagila sample database (PostgreSQL). Includes schema validation, data integrity checks, CRUD operations, trigger behavior, view correctness, partitioning verification, and regression tests.

Database

Uses the Pagila database — a PostgreSQL port of the MySQL Sakila sample database representing a DVD rental store.

Test Categories

Manual Tests

• Schema validation
• Constraint verification (primary keys, foreign keys)
• Trigger definitions and behavior
• View existence, definitions, and executability
• Data integrity baselines and referential checks
• CRUD insert operations
• Materialized view existence and indexes
• Partitioning parent structure

AI-Generated Tests

• Schema column signatures and defaults
• Constraint domains, partitions, uniqueness
• Data reference content, quality, payments, relationships
• Function behavior (group_concat, inventory, film_stock, rewards, balance)
• Trigger counts, last_updated, fulltext
• View data correctness (customer_list, staff_list, actor_info, film_list, sales)
• Materialized view refresh, reconciliation, ordering, staleness
• Partitioning bounds, coverage, routing, integrity
• CRUD insert/update/delete success and failure
• Negative and edge case tests
• Business rule consistency tests
• Regression tests
• Reporting and cross-consistency tests

Running Tests

\c pagila
\i sql_tests/tests_version_3/manual_sql_tests/schema/01_schema_objects_tests.sql

Tech Stack

• Database: PostgreSQL
• Test Framework: Pure SQL with DO blocks and RAISE NOTICE assertions
• Sample Database: Pagila (DVD rental store)

Project Structure

Database-Testing-Suite-Pagila/
├── pagila_dump.sql                          # Complete Pagila database dump
├── sql_tests/
│   └── tests_version_3/
│       ├── ai_tests/                        # AI-generated test suites
│       │   ├── schema/                      # Column signatures, defaults
│       │   ├── constraints/                 # Domain, partition FK, unique indexes
│       │   ├── data_integrity/              # Reference content, quality, payments
│       │   ├── functions_ai/                # group_concat, inventory, rewards
│       │   ├── triggers_ai/                 # Trigger counts, last_updated
│       │   ├── views_ai/                    # View data correctness
│       │   ├── materialized_views_ai/       # Refresh, reconciliation, staleness
│       │   ├── partitioning_ai/             # Bounds, coverage, routing
│       │   ├── crud_dml_ai/                 # Insert/update/delete success & failure
│       │   ├── negative_ai/                 # Edge case and negative tests
│       │   ├── business_rules_ai/           # Business rule consistency
│       │   ├── regression_ai/               # Regression tests
│       │   ├── reporting_ai/                # Reporting cross-consistency
│       │   └── ai_1_results.txt             # AI test execution results
│       └── manual_sql_tests/                # Manually-written test suites
│           ├── schema/                      # Table/column existence
│           ├── constraints/                 # PK, FK, check constraints
│           ├── data_integrity/              # Referential integrity checks
│           ├── functions/                   # Function behavior
│           ├── triggers/                    # Trigger definitions
│           ├── views/                       # View existence and execution
│           ├── materialized_views/          # MV existence and indexes
│           ├── partitioning/                # Partition parent structure
│           ├── crud_dml_behavior/           # CRUD operations
│           ├── manual_1_results.txt         # Manual test results (batch 1)
│           └── manual_2_results.txt         # Manual test results (batch 2)
└── README.md