webprogrammierung/php/model/DatabaseCommentManager.php

<?php

require_once "CommentManagerDAO.php";
require_once "Comment.php";

/**
 * Verwaltet die Speicherung und das Laden von Kommentaren
 * über eine SQLite-Datenbank.
 *
 * @author Caroline Schulte
 */
class DatabaseCommentManager implements CommentManagerDAO
{
    private static $instance = null;

    /**
     * Erstellt die Kommentartabelle, falls diese noch nicht existiert.
     */
    public function __construct()
    {
        try {
            $db = $this->getConnection();

            $db->exec("
                CREATE TABLE IF NOT EXISTS comments (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    article_id INTEGER NOT NULL,
                    parent_comment_id INTEGER NULL,
                    author TEXT NOT NULL,
                    content TEXT NOT NULL,
                    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                );
            ");

            $columns = $db->query("PRAGMA table_info(comments);")->fetchAll(PDO::FETCH_ASSOC);
            $hasParentColumn = false;

            foreach ($columns as $column) {
                if ($column["name"] === "parent_comment_id") {
                    $hasParentColumn = true;
                    break;
                }
            }

            if (!$hasParentColumn) {
                $db->exec("ALTER TABLE comments ADD COLUMN parent_comment_id INTEGER NULL;");
            }

        } catch (PDOException $e) {
            throw new RuntimeException("internal_error");
        }
    }

    /**
     * Baut die Verbindung zur SQLite-Datenbank auf.
     *
     * @return PDO Datenbankverbindung
     */
    private function getConnection()
    {
        try {
            $dsn = 'sqlite:' . __DIR__ . '/../../db/comments.db';

            $db = new PDO($dsn, null, null);
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            return $db;

        } catch (PDOException $e) {
            throw new RuntimeException("internal_error");
        }
    }

    /**
     * Gibt die Singleton-Instanz zurück.
     *
     * @return DatabaseCommentManager
     */
    public static function getInstance()
    {
        if (self::$instance === null) {
            self::$instance = new DatabaseCommentManager();
        }

        return self::$instance;
    }

    /**
     * Speichert einen neuen Kommentar oder eine Antwort.
     *
     * @param int $articleId ID des Beitrags
     * @param string $author Autor des Kommentars
     * @param string $content Inhalt des Kommentars
     * @param int|null $parentCommentId ID des Eltern-Kommentars oder null
     *
     * @return int ID des neu gespeicherten Kommentars
     */
    public function addComment(
        $articleId,
        $author,
        $content,
        $parentCommentId = null
    ) {
        try {
            $db = $this->getConnection();

            if ($parentCommentId === "" || $parentCommentId === 0 || $parentCommentId === "0") {
                $parentCommentId = null;
            }

            $sql = "
                INSERT INTO comments (
                    article_id,
                    parent_comment_id,
                    author,
                    content
                )
                VALUES (
                    :articleId,
                    :parentCommentId,
                    :author,
                    :content
                )
            ";

            $command = $db->prepare($sql);

            $command->execute([
                ":articleId" => $articleId,
                ":parentCommentId" => $parentCommentId,
                ":author" => $author,
                ":content" => $content
            ]);

            return intval($db->lastInsertId());

        } catch (PDOException $e) {
            throw new RuntimeException("internal_error");
        }
    }

    /**
     * Lädt alle Kommentare eines Beitrags.
     *
     * @param int $articleId ID des Beitrags
     *
     * @return Comment[]
     */
    public function getCommentsByArticle($articleId)
    {
        try {
            $db = $this->getConnection();

            $sql = "
                SELECT
                    id,
                    article_id,
                    CASE
                        WHEN parent_comment_id IS NULL THEN NULL
                        WHEN parent_comment_id = '' THEN NULL
                        WHEN parent_comment_id = 0 THEN NULL
                        ELSE parent_comment_id
                    END AS parent_comment_id,
                    author,
                    content,
                    created
                FROM comments
                WHERE article_id = :articleId
                ORDER BY created ASC
            ";

            $command = $db->prepare($sql);
            $command->execute([":articleId" => $articleId]);

            return $this->mapRowsToComments($command);

        } catch (PDOException $e) {
            throw new RuntimeException("internal_error");
        }
    }

    /**
     * Lädt alle Kommentare eines Autors.
     *
     * @param string $author E-Mail-Adresse des Autors
     *
     * @return Comment[]
     */
    public function getCommentsByAuthor($author)
    {
        try {
            $db = $this->getConnection();

            $sql = "
                SELECT
                    id,
                    article_id,
                    CASE
                        WHEN parent_comment_id IS NULL THEN NULL
                        WHEN parent_comment_id = '' THEN NULL
                        WHEN parent_comment_id = 0 THEN NULL
                        ELSE parent_comment_id
                    END AS parent_comment_id,
                    author,
                    content,
                    created
                FROM comments
                WHERE author = :author
                ORDER BY created DESC
            ";

            $command = $db->prepare($sql);
            $command->execute([":author" => $author]);

            return $this->mapRowsToComments($command);

        } catch (PDOException $e) {
            throw new RuntimeException("internal_error");
        }
    }

    /**
     * Wandelt Datenbankzeilen in Comment-Objekte um.
     *
     * @param PDOStatement $command Ausgeführtes Statement
     *
     * @return Comment[]
     */
    private function mapRowsToComments($command)
    {
        $comments = [];

        while ($row = $command->fetch(PDO::FETCH_ASSOC)) {
            $parentCommentId = null;

            if (
                isset($row["parent_comment_id"])
                && $row["parent_comment_id"] !== null
                && $row["parent_comment_id"] !== ""
                && intval($row["parent_comment_id"]) !== 0
            ) {
                $parentCommentId = intval($row["parent_comment_id"]);
            }

            $comments[] = new Comment(
                intval($row["id"]),
                intval($row["article_id"]),
                $parentCommentId,
                $row["author"],
                $row["content"],
                $row["created"]
            );
        }

        return $comments;
    }
}