{ Fernando Horta } → News

Overview

The primary goal of the GP2 system is to reliably store comprehensive simulated data and facilitate complex querying for generating standings, race summaries, and historical overviews. The database structure is highly normalized, reflecting the relationships between various entities like drivers, teams, engines, and seasons. The application layer is built around two core classes: the GP2Repo for all data access (CRUD operations and complex reports) and the GP2Render for handling presentation logic and view generation.

Technical Stack

The foundation of the GP2 system relies on a modern, robust open-source stack:

• Database: MySQL 8.0. All tables utilize the InnoDB storage engine, which supports the necessary transactional integrity and Foreign Key constraints.
• Backend Language: PHP 8.2
• Database Interface: PHP Data Objects (PDO), which is used exclusively for secure and structured database interaction.

Architecture and Design

The system adheres to a layered architectural pattern, clearly separating data persistence from presentation logic, maximizing maintainability and testability.

1. Database Architecture

The database schema enforces high data integrity through normalization and explicit constraints.

• Core Entities: Tables like gp2_driver, gp2_team, gp2_engine, gp2_status, and gp2_track serve as lookup tables for basic entities, each identified by an auto-incrementing integer primary key (id).
• Relational Tables: Complex relationships are managed through join tables or tables containing multiple Foreign Keys (FKs):
  • gp2_season_lineup: Maps drivers, teams, and engines to a specific season, using a composite primary key (season, driver).
  • gp2_result: Stores individual race results, linking to season, driver, and status.
  • gp2_points: Uses a composite primary key (type, position) to define different scoring rules.
• Constraint Rationale: Foreign Keys, such as those linking results to seasons and drivers, are set with ON DELETE CASCADE and ON UPDATE CASCADE. This ensures that when a core entity (like a season or a driver) is deleted or updated, all dependent records (like race results or lineup entries) are automatically handled by the database, maintaining consistency.

2. Application Architecture (GP2Repo and GP2Render)

The application logic is partitioned into two main components:

• GP2Repo (Repository Pattern): This class handles all data interaction. A key architectural decision here is the use of two separate database connections. This separation suggests a possible security or permission rationale, where administrative (write) operations are handled by a connection with elevated privileges, while standard read operations use a potentially less privileged connection.
• GP2Render (Presentation Layer): This class is instantiated with a GP2Repo instance and is responsible solely for formatting the data fetched by the repository into HTML output. It manages flow control, such as retrieving URL parameters (getParams) and rendering complex views like championship tables (renderFullDriversStandings).

Features

The system supports a full spectrum of data management and reporting capabilities:

1. Standard CRUD Operations: Complete Create, Read, Update, and Delete functions are implemented for all base tables, including gp2_driver, gp2_team, gp2_engine, gp2_status, and gp2_track.
2. Complex Data Entry: Dedicated methods exist for managing composite key tables and tables with FK constraints, such as createPoints (type, position, points), createSeason (validating pointsType), and createSeasonLineup.
3. Comprehensive Standings Calculations: The repository executes complex, multi-join SQL queries to generate real-time championship standings, calculating points based on results and the season's specific points table:
   • Team Standings: getTeamsChampionshipStandings.
   • Driver Standings: getDriversChampionshipStandings.
   • These standings can be filtered to reflect results after a specific round.
4. Race Detail and Navigation: Functions retrieve detailed race results, including position, laps, gap, driver name, and points received. Helper functions provide navigation links to move between previous and next rounds (renderPreviousNext).
5. SEO Optimization: The rendering layer incorporates URL slug generation to produce SEO-friendly links based on entity names (e.g., season ID, category, year, and track details).

Challenges and Solutions

Security and Best Practices

The system employs several crucial security measures fundamental to modern web development:

1. Prepared Statements: All database interactions, whether read (SELECT), write (INSERT, UPDATE, DELETE), or complex reporting, utilize PDO prepared statements. This practice ensures that external input is correctly bound and escaped, completely preventing SQL injection vulnerabilities.
2. Input Sanitization (XSS Prevention): The GP2Render class includes a sanitize helper function. This function uses htmlspecialchars($value, ENT_QUOTES, 'UTF-8') to neutralize input, ensuring that scripts cannot be injected into the HTML output (Cross-Site Scripting protection).
3. Principle of Least Privilege: The architectural separation of read ($this->pdo) and write ($this->pdoAdmin) connections suggests that the read-only connection might operate with restricted database permissions, limiting the potential damage if a read-side query were exploited.
4. Data Typing: PHP methods consistently use type hinting for input arguments (e.g., int $id, string $name) and explicitly define PDO parameter types (\PDO::PARAM_INT, \PDO::PARAM_STR, \PDO::PARAM_NULL) during binding.

Results and Learnings

The adopted architecture successfully achieves several key outcomes:

• Robust Data Model: The normalized MySQL schema, leveraging Foreign Key constraints, guarantees data consistency across results, lineups, and seasonal attributes.
• Separation of Concerns: By using the Repository pattern (GP2Repo), the data access logic is isolated from the view logic (GP2Render). This means database technology could potentially be swapped without affecting the rendering code, and vice versa.
• Effective Reporting: The system is capable of generating complex, aggregated reports necessary for a sports database, such as calculating total points based on a season's scoring rule set (gp2_points table) and linking results back to the driver's current team/engine configuration.

The approach of validating foreign keys in the application layer before the database operation is a valuable learning, providing a better user experience with clearer, non-database-specific error messages, such as an InvalidArgumentException if a required ID does not exist.

Code Snippets

1. SQL Schema Snippet: Defining a Relational Table

The gp2_season_lineup table is critical as it maps drivers, teams, and engines to a specific season, using a composite primary key and multiple foreign keys:

CREATE TABLE `gp2_season_lineup` (
`season` int NOT NULL,
`driver` int NOT NULL,
`team` int NOT NULL,
`engine` int NOT NULL,
`number` int NOT NULL,
`image` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Primary Key definition
ALTER TABLE `gp2_season_lineup`
ADD PRIMARY KEY (`season`,`driver`),
-- Foreign Key definition for driver
ADD CONSTRAINT `lineup_driver` FOREIGN KEY (`driver`) REFERENCES `gp2_driver` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `lineup_engine` FOREIGN KEY (`engine`) REFERENCES `gp2_engine` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `lineup_season` FOREIGN KEY (`season`) REFERENCES `gp2_season` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `lineup_team` FOREIGN KEY (`team`) REFERENCES `gp2_team` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;;

2. PHP Snippet: Secure CRUD Operation with Prepared Statements

All CRUD operations follow the pattern of preparing the SQL statement and binding values securely, as shown in the updateDriver method:

public function updateDriver(int $id, string $name): bool {
    $stmt = $this->pdoAdmin->prepare("UPDATE gp2_driver SET name = :name WHERE id = :id");
    $stmt->bindValue(':name', $name, \PDO::PARAM_STR);
    $stmt->bindValue(':id', $id, \PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->rowCount() > 0;
}

3. PHP Snippet: Complex Data Retrieval (Championship Standings)

Reporting functions rely on intricate JOIN operations to pull together disparate data points, such as calculating standings by linking results to points tables and the driver's lineup configuration:

public function getDriversChampionshipStandings(int $season, int $round = 100): array {
        $stmt = $this->pdo->prepare("SELECT gp2_driver.name, CONCAT(gp2_team.name,' - ',gp2_engine.name) 'team',
 SUM(CASE WHEN gp2_points.points IS NOT NULL THEN gp2_points.points ELSE 0 END) total FROM `gp2_result` 
            JOIN gp2_season_rounds ON (gp2_season_rounds.season = gp2_result.season AND gp2_season_rounds.round = gp2_result.round)
            JOIN gp2_season ON gp2_season.id = gp2_result.season
            JOIN gp2_driver ON gp2_result.driver = gp2_driver.id
            JOIN gp2_season_lineup ON gp2_season_lineup.season = gp2_season.id AND gp2_season_lineup.driver = gp2_result.driver
            JOIN gp2_team ON gp2_season_lineup.team = gp2_team.id
            JOIN gp2_engine ON gp2_engine.id = gp2_season_lineup.engine
            JOIN gp2_points ON gp2_points.type = gp2_season.points AND gp2_points.position = gp2_result.position
            WHERE gp2_season.id = :season
            AND gp2_season_rounds.round <= :round
            GROUP BY gp2_season_rounds.season, gp2_driver.name
            ORDER BY total DESC, gp2_result.position ASC;");
        $stmt->bindValue(':season', $season, \PDO::PARAM_INT);
        $stmt->bindValue(':round', $round, \PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

go to gp2 pages