DiRT Rally 2.0 Project

November 15, 2025 08:36pm

This technical article details the structure and implementation of the data layer for the "Dirt Rally" project, focusing on the DirtRepo class, which manages all associated results, events, and classifications drawing on the database schema provided.

Dirt Rally Data Repository (DirtRepo Class)

Since 1.9.0 | Developed by Fernando Horta

? Overview

The DirtRepo class serves as the repository layer for the Dirt Rally database, handling all data read and write operations related to race results, events, tags, and the relationships between them. Implemented in PHP, this repository strictly separates read-only operations (for fixed data like cars, classes, and tracks) from full CRUD entities (events, tags, and results).

The underlying database, named [db], defines a schema specifically for managing dirt racing data, with tables prefixed dirt_.

⚙️ Technical Stack

Area Technologies
Backend Logic PHP 8, OOP Design
Database Access PDO (PHP Data Objects)
Database Platform MySQL
Architecture Two-Connection Model (Read/Admin Separation)

? Architecture & Database Implementation

The core architectural decision is the utilization of a two-connection model to enforce the principle of least privilege.

  1. Class Initialization: The DirtRepo constructor initializes two separate PDO connection instances: $this->pdoRead and $this->pdoAdmin. These connections are obtained from a static Database class, which handles the connection logic.
  2. Modular Structure: The repository organizes its methods into distinct sections: Read-Only Tables (cars, classes, tracks), Events CRUD, Tags CRUD, Results CRUD, and Result Tag Relations.

Database Schema ([db] tables):

The system relies on several normalized tables to store racing data:

Table Name Purpose & Key Fields Relationships (Foreign Keys)
dirt_classes Stores definitions of car classes (e.g., class varchar(64)). None (Parent table).
dirt_cars Stores car names and their corresponding classes. class references dirt_classes.id.
dirt_events Stores event names. None (Parent table).
dirt_tracks Stores track details, including country (2 chars), track name, and distance. None (Parent table).
dirt_tags Stores descriptive tags (e.g., 'crashed', 'time penalty', 'rolled over'). None (Parent table).
dirt_results Stores individual race times. car references dirt_cars.id; track references dirt_tracks.id; event references dirt_events.id.
dirt_result_tag Many-to-Many join table between results and tags. Composite primary key on (resultado, tag).

?️ Key Methods and Functionality

The DirtRepo class provides extensive methods for data retrieval and modification:

I. Read-Only Operations (Using $this->pdoRead)

  • getCars(): Retrieves all cars, performing a JOIN with dirt_classes to include the respective class name, ordered by car name.
  • getClasses(): Fetches all available car classes, ordered alphabetically.
  • getTracks(): Retrieves all tracks, performing a JOIN with a countries table to include the full country name, ordered first by country, then by track name.

II. CRUD Operations (Events, Tags, Results)

The repository provides full CRUD functionality for dirt_events, dirt_tags, and dirt_results. Creation and update methods utilize the write-privileged connection ($this->pdoAdmin) and Prepared Statements for security.

  • Event Management: Methods include getEvents(), getEvent(int $id), createEvent(string $name), updateEvent(int $id, string $name), and deleteEvent(int $id).
  • Tag Management: Methods include getTags(), getTag(int $id), createTag(string $tag), updateTag(int $id, string $tag), and deleteTag(int $id).
  • Results Retrieval (getResults): This is a complex read operation that retrieves race results. The query performs four JOINs to gather comprehensive metadata, including car name, track name, event name (left join), and country. It optionally filters results by $eventId or $resultId and orders them by date descending.
  • Results Modification: createResult accepts car, track, optional event, time, and optional video URL. updateResult allows changing the car, event, and video link for an existing result. deleteResult removes a result by ID.

III. Tag Relationship Management

These methods manage the many-to-many relationship between results and tags stored in dirt_result_tag:

  • getResultTags(int $resultId): Retrieves all tags associated with a specific result, joining with dirt_tags to get the tag name.
  • addResulTags(int $resultId, int $tagId): Links a tag to a result using an INSERT IGNORE statement to prevent duplicate relationships.
  • removeResultTag and clearResultTags: Provide granular and bulk deletion of tag associations.

? Security & Best Practices

The design of DirtRepo adheres to several security best practices common in the CMS project architecture:

  1. Least Privilege: By utilizing $this->pdoRead and $this->pdoAdmin connections, the application ensures that public-facing queries only use credentials with read permissions, significantly limiting the potential damage from a read-only breach.
  2. Prepared Statements: All methods involving user input or data modification (createEvent, getEvent, updateTag, etc.) use prepared statements with placeholders (? or :name), mitigating SQL injection risks.
  3. Robust Error Handling: Every major database operation is wrapped in a try...catch (PDOException $e) block. Upon failure, the PDO exception is caught, and a new descriptive Exception is thrown, detailing the error and the affected entity (e.g., "Error fetching event #$id").

The functionality of the DirtRepo class is akin to a pit crew chief managing the entire race operation. The read connection is like the chief reviewing performance data (tracks, cars, existing classes), while the admin connection is the chief making critical decisions—creating new events, modifying results, or assigning repair tags—all through secure, organized procedures.

See the project