Teil 2: Datenbank-Schicht in PHP schrittweise erarbeiten

[1unitedpower]

Datenbank-Schicht in PHP

Für die Datenbank-Schicht brauchen wir zunächst drei Funktionen: Eine Funktion zum Speichern eines neues Logbuch-Eintrags; eine Funktion, um die Anzahl der Personen auszulesen, die in der vergangen Minute die Seite aufgerufen haben; und eine Funktion, die die Gesamtanzahl der Personen ausliest, die jemals die Seite aufgerufen haben. Diese drei Funktionen dienen dazu, zwischen der Datenbank-Schicht (auch Persistenz-Schicht genannt) und der Geschäftslogik zu vermitteln. Die Funktionen sind also verwandt miteinander, wir implementieren sie deshalb als drei Methoden einer gemeinsamen Klasse, die wir AccessLogRepository nennen. Hier ist das grobe Schema (auch Signatur genannt) unserer Klasse, die Methoden-Rümpfe werden wir im Anschluss einzeln implementieren.

<?php
declare(strict_types=1);

namespace SelfHtml\Counter;

use \PDO;

final class AccessLogRepository
{
    private $pdo;

    public function __construct(PDO $pdo)
    {
    }

    /**
     * Erzeugt einen neuen Datenbank-Eintrag für die angebene IP-Adresse
     * und den aktuellen Zeitstempel.
     *
     * @param string $ip IP-Adresse des zu speichernden Log-Eintrags
     *
     * @return void
     */
    public function log(string $ip) : void
    {
    }

    /**
     * Liest die Anzahl der verschiedenen IP-Adressen aus, die in den vergangenen
     * X Sekunden einen Zugriff durchgeführt haben.
     *
     * @param int $maxAge Das maximale Alter in Sekunden, das ein Eintrag haben darf,
     * damit die dazugehörige IP-Adresse in die Zählung aufgenommen wird.
     *
     * @return int
     */
    public function countIpsYoungerThan(int $maxAge) : int
    {
    }

    /**
     * Liest die Anzahl allers Sessions aus der Datenbank aus.
     * Eine Session wird von Log-Einträgen der selben IP-Adresse gebildet,
     * die zeitlich nahe beianander stehen.
     *
     * @param int $sessionLifeTime Die maximale Dauer in Sekunden, die zwischen zwei
     * Log-Einträgen der selben IP-Adresse vergangen sein darf, damit sie zur selben
     * Session gezählt werden.
     *
     * @return int
     */
    public function countAllSessions(int $sessionLifeTime) : int
    {
    }
}

Wir beginnen mit der einfachsten Funktion, dem Konstruktor, und arbeiten uns dann schrittweise von oben nach unten zu den schwierigeren Funktionen vor.

Der Konstruktor bekommt als Parameter eine Datenbank-Verbindung in Form einer PDO-Instanz übergeben. Diese wollen wir in den späteren Funktionen nutzen, wir merken sie uns deshalb in einer Instanz-Variablen.

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

Als nächstes kümmern wir uns, um die log-Funktion. Sie bekommt als Parameter eine IP-Adresse, die gespeichert werden soll. Sonst soll die Funktion nichts machen. Der Rückgabe-Typ void signalisiert uns, dass die Funktion auch nichts zurückgeben soll.

    /**
     * Erzeugt einen neuen Datenbank-Eintrag für die angebene IP-Adresse
     * und den aktuellen Zeitstempel.
     *
     * @param string $ip IP-Adresse des zu speichernden Log-Eintrags
     *
     * @return void
     */
    public function log(string $ip) : void
    {
        static $query = 'INSERT INTO `access_log` (`ip`) VALUES (:ip);';
        $this->pdo->prepare($query)->execute([':ip' => $ip]);
    }

Die Insert-Anfrage in $query beschreibt einen Einfüge-Vorgang in SQL. Die Spalten id und access_time werden von SQL automatisch befüllt, weil wir das in unserem Schema so vorgesehen haben. Die Spalte ip müssen wir selber befüllen. Dafür haben wir einen Platzhalter :ip in der Abfrage notiert. Die eigentliche Ausführung der Datenbank-Anfrage findet in der nächsten Zeile in zwei Phasen statt: Die Methode prepare teilt der Datenbank mit, dass hier eine Anfrage mit potenziellen Platzhaltern folgt. Der Aufruf von execute setzt den Wert der tatsächlichen IP-Adresse $ip für den Platzhalter ein und löst den Schreibvorgang in der Datenbank aus.

Als nächsten kümmern wir uns um die countIpsYoungerThan-Funtkion. Sie bekommt ein Zeitinterval in Sekunden übergeben, und soll die Anzahl der verschiedenen IP-Adressen auslesen, die in den vergangenen Sekunden, eine Anfrage ausgeführt haben.

    /**
     * Liest die Anzahl der verschiedenen IP-Adressen aus, die in den vergangenen
     * X Sekunden einen Zugriff durchgeführt haben.
     *
     * @param int $maxAge Das maximale Alter in Sekunden, das ein Eintrag haben darf,
     * damit die dazugehörige IP-Adresse in die Zählung aufgenommen wird.
     *
     * @return int
     */
    public function countIpsYoungerThan(int $maxAge) : int
    {
        static $query = <<<SQL
            SELECT COUNT(DISTINCT `ip`)
            FROM `access_log`
            WHERE TIMESTAMPDIFF(
                    SECOND,
                    `access_time`,
                    NOW()
                ) <= :maxAge;
            SQL;
        return (int) $this->pdo->prepare($query)
            ->execute(['mageAge' => (string) $magAge])
            ->fetchColumn();
    }

Die Methode folgt dem selben Schema, wie die vorherige: Erst notieren wir die nötige SQL-Anfrage, dann rufen wir prepare auf, um die Datenbank auf den Platzhalter :maxAge aufmerksam zu machen, anschließend führen wir die Anfrage mit execute aus. Diesmal kommt noch ein Schritt fetchColumn hinzu, weil wir einen Lesezugriff auf die Datenbank ausgeführt haben und wir das SQL-Ergebnis als Rückgabe-Wert unserer PHP-Funktion weitereichen möchten.

SELECT COUNT(DISINCT ip) sorgt dafür, dass wir nur verschiedene IP-Adresse zählen. Der Krux hier ist die WHERE-Klausel. Dort berechnen wir das Alter eines Log-Eintrags in Sekunden und vergleichen es mit dem maximalen Alter, dass der Log-Eintrag haben darf, um gezählt zu werden.

Die letzte Funktion countAllSessions ist etwas komplizierter. Wir wollen hier nicht bloß die Anzahl der verschiedenen IP-Adressen zählen. Stattdessen wollen wir alle Log-Einträge, die innerhalb eines vorgebenen Zeitraumes von der selben IP-Adresse verursacht worden, als zusammengehörige Session nur einmal zählen. Die SQL-Abfrage dafür erarbeiten wir uns schrittweise. Stellen wir uns zunächst vor, wir hätten eine Tabelle mit nur einer Spalte namens delta, die die Zeitabstände in Sekunden von Zugriffen der selben IP-Adresse enthält, oder den Sonderwert NULL falls es keinen vorherigen Log-Eintrag der selben IP-Adresse gibt. Dann könnten wir die folgende SQL-Anfrage stellen:

SELECT COUNT(*) FROM ???
WHERE `delta` IS NULL OR `delta` > :session_life_time;

Die Frage ist, was gehört an die Stelle, die mit ??? gekennzeichnet ist. Denn die Tabelle, die wir uns vorgestellt haben ist ja nur fiktiv. An dieser Stelle muss nicht unbedingt ein Tabellenname stehen, wir können dort auch eine Unterabfrage platzieren. Diese Unterabfrage soll in einem Zwischenschritt unsere fiktive Tabelle simulieren. Wir haben bereits gesehen, wie wir Zeitabstände in SQL berechnen. Die folgende Skizze bringt uns einen Schritt näher zum Ziel:

SELECT
    TIMESTAMPDIFF(
        SECOND,
        `access_time`,
        ???
    ) as `delta`
FROM `access_log`;

Wir müssen nun abermals die durch ??? gekennzeichnete Lücke schließen. Zuvor haben wir dort den aktuellen Zeitstempel mit NOW() eingesetzt. Diesmal jedoch, wollen wir dort den Zeitstempel des Log-Eintrags haben, der zur selben IP-Adresse gehört, und der zeitlich unmittelbar davor stattgefunden hat. Diesen Eintrage bekommen wir mit dem folgenden Ausdruck:

LEAD(`access_time`) OVER (
    PARTITION BY `ip`
    ORDER BY `access_time`
)

Insgesamt ergibt sich somit die Anfrage:

SELECT COUNT(*) FROM (
    SELECT
        TIMESTAMPDIFF(
            SECOND,
            `access_time`,
            LEAD(`access_time`) OVER (
                PARTITION BY `ip`
                ORDER BY `access_time`
            )
        ) as `delta`
    FROM `access_log`
) `t`
WHERE `delta` IS NULL OR `delta` > :session_life_time;

Und in die resultierende PHP-Funktion sieht wie folt aus:

    /**
     * Liest die Anzahl allers Sessions aus der Datenbank aus.
     * Eine Session wird von Log-Einträgen der selben IP-Adresse gebildet,
     * die zeitlich nahe beianander stehen.
     *
     * @param int $sessionLifeTime Die maximale Dauer in Sekunden, die zwischen zwei
     * Log-Einträgen der selben IP-Adresse vergangen sein darf, damit sie zur selben
     * Session gezählt werden.
     *
     * @return int
     */
    public function countAllSessions(int $sessionLifeTime) : int
    {
        static $query = <<<SQL
            SELECT COUNT(*) FROM (
                SELECT
                    TIMESTAMPDIFF(
                        SECOND,
                        `access_time`,
                        LEAD(`access_time`) OVER (
                            PARTITION BY `ip`
                            ORDER BY `access_time`
                        )
                    ) as `delta`
                FROM `access_log`
            ) `t`
            WHERE `delta` IS NULL OR `delta` > :session_life_time;
            SQL;
        return (int) $this->pdo->prepare($query)
            ->execute([':session_life_time' => (string) $sessionLifeTime]))
            ->fetchColumn();
    }