Database and Model Unit Tests

Relevant source files

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

• test/test_database.py
• test/test_db.py
• test/test_db_extended.py
• test/test_track.py
• app/database.py
• app/track.py

The database and model unit tests ensure the reliability of data persistence and the integrity of the core data structures within the Spotify API Data Collector. These tests focus on validating the interaction between the application and its SQLite storage, as well as the correct instantiation and serialization of the primary data model, the Track class.

The testing suite uses pytest and temporary files to simulate a real database environment without affecting production data. This ensures that every database operation—from table initialization and indexing to batch inserts and complex queries—functions as expected before deployment.

Sources: test/test_db.py:10-23, test/test_database.py:18-31

Data Model Validation: The Track Class

The Track class is the central data structure of the project. Unit tests verify that it can be correctly instantiated from raw Spotify API data and accurately converted to other formats for storage or export.

Track Lifecycle and Transformation

The following diagram illustrates how the Track model serves as a bridge between the external API data and the internal database.

The Track model includes logic to handle missing fields from the API, providing default values (e.g., 0 for popularity or empty strings for genres) to prevent runtime errors during processing.

Sources: test/test_track.py:33-91, app/track.py

Data Field Specifications

Based on the implementation in app/track.py and validated in test_track.py, the following fields comprise the core data model:

Field	Type	Description
track_id	str	Unique Spotify identifier (Primary Key)
track_name	str	Name of the track
track_popularity	int	Popularity score (0-100)
album_name	str	Name of the album containing the track
duration_ms	int	Duration in milliseconds
year	int	Release year (indexed for search)
artist_name	str	Name of the artist (indexed for search)
artist_genres	str	Comma-separated list of genres

Sources: app/database.py:15-30, test/test_track.py:12-31

Database Management Tests

The Database class in app/db.py acts as a wrapper for SQLite connections. The test suite ensures robust connection handling, transaction management (including rollbacks), and path configuration.

Connection and Transaction Flow

Tests verify that the database correctly manages connections and rolls back changes if an error occurs during an operation.

Database Initialization and Indexing

The initialization process creates the tracks table and essential indexes to optimize query performance for the API.

• init_db: Creates the tracks table with a UNIQUE constraint on track_id.
• Indexing: Creates idx_tracks_year, idx_tracks_artist_name, and idx_tracks_popularity.

Sources: app/database.py:11-45, test/test_database.py:53-65

Persistence and Retrieval Operations

The app/database.py module contains the logic for high-level database interactions, which are heavily tested for correctness and edge cases like duplicate handling.

Key Database Functions

Function	Purpose	Tested Behavior
insert_track	Single record insertion	Uses INSERT OR IGNORE to prevent duplicates.
insert_tracks_batch	Bulk insertion	Handles multiple records in a single transaction.
get_tracks_by_year	Filtered retrieval	Returns tracks for a specific year sorted by popularity.
get_tracks_by_artist	Search by artist	Case-insensitive search using LOWER().
track_exists	Existence check	Efficiently checks if a track_id is present.

Sources: app/database.py:48-124, test/test_database.py:79-195

Duplicate Prevention Logic

A critical feature tested is the idempotence of data collection. Since the track_id is marked as UNIQUE, the system ensures that multiple collection runs do not create redundant entries.

# From app/database.py:48-55
def insert_track(db: Database, track: Track) -> None:
    insert_query = """
    INSERT OR IGNORE INTO tracks (
        track_id, track_name, ...
    ) VALUES (?, ?, ...)
    """
    # ... execution logic

Sources: app/database.py:48-55, test/test_database.py:91-98

Configuration and Path Testing

Extended tests in test_db_extended.py and test_db.py ensure that the database can be initialized in various environments, including handling custom URI schemes and ensuring directory creation.

• Directory Creation: The Database class automatically creates the parent directories for the database file if they do not exist.
• URL Parsing: Validates that sqlite:/// URLs are correctly parsed to local file paths, while falling back to defaults for unsupported protocols.

Sources: test/test_db.py:126-150, test/test_db_extended.py:13-25

Conclusion

The Database and Model Unit Tests provide a comprehensive safety net for the application's data layer. By validating the Track model's transformation logic and the Database manager's transaction integrity, the project ensures that musical metadata is collected, stored, and retrieved without corruption or duplication. This modular testing approach allows the project to scale from simple collection to complex API services with a guaranteed reliable data foundation.