Develop a blog - Part 7: Store and retrieve blog posts

/
/
11 months ago
/

We need a persistence layer on our blog. Authors need to store a blog post in order for a reader to actually read it. Currently we have all functionality in place around creating and updating a blog post, but we don't have any persisitence in place yet. Which makes our blog utterly useless. Until now we pushed the decision for a persistence layer forward, because we wanted to focus on the business logic first. That way we would be in a better position to decide on what technology to use for our persistence layer.

When we started our journey we had the idea that we might be able to use a flat file system or a relational database for our blog. Now that we have most business logic in place we notice that there are some relational relationships emerging in our design. Unfortunately we also have some object oriented concepts in our design that don't really match with a relational database.

Let's investigate and see how best to implement a persistence layer using PostgreSQL as the persistence layer.

Previous articles

This article is part of a series. Read the other parts if you didn't read them yet:

The source code for the full series and the changes I made in this article are available on Github.

Database testing

Before we continue developing our application, we have to talk about database testing. In general you want to avoid external dependencies in your unit tests. In previous articles we prevented the use of dependencies by using stubs and mocks. And you can go a long way using these. Unfortunately, they aren't always enough. Especially when you want to test persistence and more complex queries. You can use a mock or a stub to verify if the database is called, but you can never test if the actual query will work.

In the past PHPUnit provided an extension called DbUnit, but this extension is now abandoned with the recommendation to roll your own application specific database testing functions. So that is what we will do by creating a UseDatabase trait. All tests that use the database can use this trait.

To test the database we will need to following before every test:

  1. A connection to a test database
  2. A known initial state for the database
  3. Asserts that allow us to verify the database.

We could create a database connection before every test using a setUp method. This would create a new connection for every single test, which can be slow. As the connection doesn't hold any state we can also reuse the database connection. PHPUnit supports this using the @beforeClass annotation.

<?php


namespace Tests\Utils;

use PDO;

trait UseDatabase
{
    private static PDO $pdo;

    protected function getPdo()
    {
        return self::$pdo;
    }

    /** @beforeClass  */
    public static function initialiseDatabase() : void{
        self::createDatabaseConnection();
    }

    private static function createDatabaseConnection() : void{
        self::$pdo = new PDO(
            sprintf("%s:dbname=%s;host=%s", $GLOBALS['DB_TYPE'], $GLOBALS['DB_NAME'], $GLOBALS['DB_HOST']),
            $GLOBALS['DB_USER'],
            $GLOBALS['DB_PASSWORD']
        );
        self::$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        self::$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    }
}

We want the database to be in a known state before every test. We can do that by creating all necessary tables at the start of the test class. We truncate the tables before every test and finally drop all tables at the end of our test suite again.

For now I added a database directory to our project which contains a .sql file per database table. We can provide our trait with a list of table names and then load the appropriate .sql file for that table to create it. In individual test classes, we can override the list of table names to make sure only the necessary tables are created.

protected static function getTables() : array
{
    return [
        'authors',
    ];
}

/** @beforeClass  */
public static function initialiseDatabase() : void
{
    self::createDatabaseConnection();
    self::createDatabaseTables();
}

private static function createDatabaseTables(): void
{
    foreach(self::getTables() as $table) {
        self::$pdo->exec(file_get_contents(
            dirname(__FILE__).'/../../database/'.$table.'.sql'
        ));
    }
}

/** @afterClass */
public static function dropDabaseTables(): void
{
    self::$pdo->exec(
        'DROP TABLE ' . implode(', ', self::getTables())
    );
}


/** @before */
protected function truncateDatabase() : void
{
    self::$pdo->exec(
        'TRUNCATE TABLE ' . implode(', ', self::getTables())
    );
}

Now that we have the database setup and teardown, we want some common asserts on the database. For now I added two methods: assertDatabaseCount to count the amount of records in a table and assertDatabaseHas to check if certain values are available in the database.

protected function assertDatabaseCount(string $table, int $count)
{
    $statement = self::$pdo->query('SELECT COUNT(*) FROM '.$table);
    $actualCount = $statement->fetch()['count'];

    $this->assertEquals($count, $actualCount);
}

protected function assertDatabaseContains(string $table, array $values)
{
    $statement = self::$pdo->query('SELECT * FROM '.$table);
    $results = $statement->fetchAll();

    $this->assertArrayContains($values, $results);
}

private function assertArrayContains(array $value, $results)
{
    $this->assertTrue(
        in_array($value, $results),
        sprintf(
            'Failed asserting %s is in array %s',
            print_r($value, true),
            print_r($results, true),
        )
    );
}

Finally we need to update our phpunit.xml to provide the needed $GLOBAL variables to connect to the database.

Database tests are much slower than normal unit tests and also have the prerequisite that the developer has the test database set up. To allow the developer to choose between which tests to run, I split up our tests in two testsuites: unit tests and database tests.

<phpunit bootstrap="vendor/autoload.php">
    <php>
        <var name="DB_TYPE" value="pgsql" />
        <var name="DB_HOST" value="localhost" />
        <var name="DB_USER" value="homestead" />
        <var name="DB_PASSWORD" value="secret" />
        <var name="DB_NAME" value="homestead" />
    </php>
    <testsuites>
        <testsuite name="Unit">
            <directory>./tests/Unit</directory>
        </testsuite>
        <testsuite name="Database">
            <directory>./tests/Database</directory>
        </testsuite>
    </testsuites>
</phpunit>

This configuration allows us to run all tests, only unit tests or only database tests.

Author Repository

Now that we have the infrastructure set up to test database queries, let's start with the simplest entity to store and retrieve: the Author. The Author is the simplest, because it doesn't have any relationships on itself. It just has a name and nothing else.

Let's create the database table by adding author.sql to the database directory in our project:

create table authors (
    name varchar(30) unique not null,
    primary key(name)
);

We will be using the repository pattern to connect to the database. We create a repository that acts as a place to store and retrieve our authors. The user of the repository doesn't have to know how the storing and retrieving works. This allows us to create specific repositories for specific storage solutions. For example one specific to a relational database and another one for a file system. For now we will create repositories to connect to a PostgreSQL database.

Our first AuthorRepositorytest:

<?php

namespace Tests\Database;

use PHPUnit\Framework\TestCase;
use Tests\Utils\UseDatabase;
use Webdevils\Blog\Author;
use Webdevils\Blog\Author\AuthorRepository;
use Webdevils\Blog\Author\DatabaseAuthorRepository;

class AuthorRepositoryTest extends TestCase
{
    use UseDatabase;

    private AuthorRepository $repository;

    protected function setUp() : void{
        $this->repository = new DatabaseAuthorRepository(
            $this->getPdo()
        );
    }

    /** @test */public function can_store_a_new_author()
    {
        $author = new Author('Mark');

        $this->repository->store($author);

        $this->assertDatabaseContains('authors', ['name' => 'Mark']);
        $this->assertDatabaseCount('authors', 1);
    }
}

In the test we create a new Author, we store it to the repository and we check if the repository successfully stored it in the database.

We introduce an AuthorRepository interface which describes how to interact with author repositories. We make a specific DatabaseRepository that implements the interface using a database back-end.

interface AuthorRepository
{
    public function store(Author $author) : void;
}

class DatabaseAuthorRepository implements AuthorRepository
{
    private PDO $pdo;

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

    public function store(Author $author) : void{
        $statement = $this->pdo->prepare(
            'INSERT INTO authors (name) VALUES (?)'
        );
        $statement->execute([$author->getName()]);
    }
}

We also want to retrieve or find the author based on its name from the repository. This will be our next test:

protected function createAuthor($name) : Author
{
    $author = new Author($name);
    $this->repository->store($author);

    return $author;
}

/** @test */
public function can_fetch_an_author()
{
    $author = $this->createAuthor('Mark');

    $fetchedAuthor = $this->repository->find('Mark');

    $this->assertEquals($author, $fetchedAuthor);
}

/** @test */
public function throws_exception_when_fetching_a_non_existent_author()
{
    $this->expectException(AuthorNotFound::class);

    $repository = new DatabaseAuthorRepository($this->getPdo());
    $repository->find('Mark');
}

There are two test cases:

  1. We try to fetch an author from the repository that doesn't exist
  2. We fetch an author that does exist.

To implement both tests we add the following code to our DatabaseAuthorRepository:

public function find(string $name): Author
{
    $statement = $this->pdo->prepare(
        'SELECT * FROM authors WHERE name = ?'
    );
    $statement->execute([$name]);

    $data = $statement->fetch();
    if($data === false) {
        throw new AuthorNotFound('Author '.$name.' not found');
    }
    return new Author($data['name']);
}

We find the author data using a select query. Check if we actually found something. If we found something we create a new Author object and return it. Otherwise we throw an AuthorNotFound exception.

Storing and fetching categories

Categories are a bit more complicated than authors. Every category has its own slug. From a database design we have two options here:

  1. Create two tables: slugs and categories
  2. Create a single table categories which has a field for a slug.

Both Category and BlogPost make use of a Slug. From that perspective it made sense to make a Slug class that contains the specific behaviour of a slug. From a database perspective there isn't much shared data however. A slug is just the url, but has no other data. It only needs to be unique for BlogPost or Category. A category and a blogpost could have the same slug and it wouldn't be a problem, as their entry URL will probably be different. For example: /categories/{slug} for a category and /blogposts/{slug} for blogposts.

From that perspective it makes more sense to store the slug in the categories table and have a DatabaseCategoryRepository that implements both a CategoryRepository interface and the SlugRepository interface.

From a database perspective we would have the following database table:

create table categories
(
    slug varchar(30) unique not null,
    name varchar(30) not null,
    primary key(slug)
);

We can create an initial test for storing a category:

class CategoryRepositoryTest extends TestCase
{
    use UseDatabase;

    private CategoryRepository $repository;

    protected function createGenerator(): SlugGenerator{
        $slugRepository = $this->createStub(SlugRepository::class);
        $slugRepository->method('exists')
            ->willReturn(false);

        return new SlugGenerator($slugRepository);
    }

    public function setUp(): void{
        $this->repository = new DatabaseCategoryRepository($this->getPdo());
    }

    /** @test */public function can_store_a_category()
    {
        $generator = $this->createGenerator();

        $category = new Category(
            generator: $generator,
            name: 'New category'
        );

        $this->repository->store($category);

        $this->assertDatabaseContains(
            'categories',
            [
                'slug' => $category->getSlug()->getUrl(),
                'name' => $category->getName()
            ]
        );
    }
}

We create a new Category and use the repository to store it. Then we check if the repository successfully saves the slug and name to the database.

class DatabaseCategoryRepository implements CategoryRepository
{
    private PDO $pdo;

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

    public function store(Category $category): void{
        $statement = $this->pdo->prepare(
            'INSERT INTO categories (slug, name) values (?, ?)'
        );
        $statement->execute([
            $category->getSlug()->getUrl(),
            $category->getName()
        ]);
    }
}

To create a category, we also need a SlugGenerator. The SlugGenerator uses a SlugRepository to check if the generated Slug exists. In the test we stub the SlugRepository, but we also need to start implementing a SlugRepository that checks with the database. We already have the DatabaseCategoryRepository that takes care of the storing and retrieving categories. It makes sense to add the slug check functionality on the same repository.

/** @test */
public function can_check_if_slug_exists()
{
    $slug = new Slug('new-category');

    $this->assertFalse($this->repository->exists($slug));

    $this->createCategory('New category');

    $this->assertTrue($this->repository->exists($slug));
}

The DatabaseCategoryRepository class will implement SlugRepository as well and implement the exists method:

public function exists(Slug $slug): bool
{
    $statement = $this->pdo->prepare(
        'SELECT EXISTS(SELECT * FROM categories WHERE slug = ?);'
    );
    $statement->execute([$slug->getUrl()]);

    return $statement->fetch()['exists'];
}

With the exists method in place, we can also refactor the createGenerator method on our test class:

protected function createGenerator(): SlugGenerator
{
    return new SlugGenerator(
        $this->repository
    );
}

On to our last test for CategoryRepository: fetch a category from the database:

/** @test */
public function can_fetch_a_category()
{
    $category = $this->createCategory('PHP');

    $this->assertEquals(
        $category,
        $this->repository->find(new Slug('php'))
    );
}

The test method is straightforward. Unfortunately, making the test green isn't. When calling the constructor of Category it will generate a slug based on the name. We don't want that when we are fetching from the database. We already have a generated slug that we want to use.

We can fix this by introducing a factory method. A factory method is a method that is responsible for constructing an object. In case of categories we have to create two:

  1. For normal creation of categories we can add a factory method called create
  2. For fetching objects from the database we can add a factory method called hydrate

When creating a new Category we want to use the create factory method. When fetching a Category from the database we use the hydrate method. To prevent anyone from using the constructor, we will define the constructor as private.

private function __construct(Slug $slug, string $name)
{
    $this->slug = $slug;
    $this->name = $name;
}

public static function create(SlugGenerator $generator, string $name) : Category
{
    if (self::isTooShort($name, self::MIN_NAME_LENGTH)) {
        throw new InvalidCategory('Category name must be minimum '.self::MIN_NAME_LENGTH.' characters');
    }
    if (self::isTooLong($name, self::MAX_NAME_LENGTH)) {
        throw new InvalidCategory('Category name must be maximum '.self::MAX_NAME_LENGTH.' characters');
    }

    return new Category(
        $generator->generate($name),
        $name
    );
}

public static function hydrate($slug, $name) : Category
{
    return new Category($slug, $name);
}

In the background I changed all tests to use the new factory methods. You can easily do this for yourself by using the tests to identify where you need to update your code.

Now that we have the two factory methods, making the test green is straightforward:

public function find(Slug $slug): Category
{
    $statement = $this->pdo->prepare(
        'SELECT * FROM categories WHERE slug = ?'
    );
    $statement->execute([$slug->getUrl()]);

    $categoryData = $statement->fetch();

    return Category::hydrate(
        new Slug($categoryData['slug']),
        $categoryData['name']
    );
}

When the author doesn't exist we want to throw an exception:

/** @test */
public function gives_an_exception_when_category_doesnt_exist()
{
    $this->expectException(CategoryNotFound::class);

    $this->repository->find(new Slug('php'));
}

And the implementation to make the test green:

public function find(Slug $slug): Category
{
    $statement = $this->pdo->prepare(
        'SELECT * FROM categories WHERE slug = ?'
    );
    $statement->execute([$slug->getUrl()]);

    $categoryData = $statement->fetch();

    if($categoryData === false) {
        throw new CategoryNotFound('Category with slug '.$slug->getUrl() . ' not found');
    }

    return Category::hydrate(
        new Slug($categoryData['slug']),
        $categoryData['name']
    );
}

Storing blogposts

Last but not least: blogposts. Storing and fetching blogposts is much more complicated than authors or categories. We have more fields, but also more complicated fields. Let's walk through all of the fields that need to be stored to the database:

  1. Slug: we take the same approach as with categories. We store them directly to the database table
  2. Authors: this is a many to many relationship which means we need to store this in a relationship table
  3. Category: will be a reference to the categories table
  4. title, introduction and content are normal fields in the database table
  5. Status: a complicated field. We can store the name of the status in the database, but we need to create the correct status object when creating the blogpost. Also, scheduled and published store their own state. Scheduled stores a publish date which we can store as a nullable field on the table. Published also stores a list of old slugs. These need to be stored in their own table. We need to take this table into account when we are checking for the existence of a slug.
  6. Parser: we give every parser a name and construct it using that name

Based on these decisions we can create the following tables:

  1. blogposts
  2. old_slugs
  3. blogposts_authors

The blogposts table looks like this:

create table blogposts
(
    slug varchar(70) unique not null,
    category varchar(30) not null,
    title varchar(70) not null,
    introduction text not null,
    content text not null,
    publish_date timestamp,
    status varchar(30) not null,
    parser varchar(30) not null,

    primary key(slug),
    foreign key(category) references categories(slug)
)

The old_slugs table:

create table old_slugs
(
    slug varchar(70) unique not null,
    blogpost_slug varchar(70) not null,

    primary key(slug),
    foreign key(blogpost_slug) references blogposts(slug)
);

And the blogposts_authors table:

create table blogposts_authors
(
    blogpost_slug varchar(70) not null,
    author_name varchar(30) not null,

    primary key(blogpost_slug, author_name),
    foreign key(blogpost_slug) references blogposts(slug),
    foreign key(author_name) references authors(name)
);

Let's write our first test to check if we can store a blogpost to the database:

class BlogPostRepositoryTest extends TestCase
{
    use UseDatabase;

    private BlogPostRepository $repository;

    private function createGenerator() : SlugGenerator{
        $repo = $this->createStub(SlugRepository::class);
        $repo->method('exists')
            ->willReturn(false);

        return new SlugGenerator($repo);
    }

    private function createAuthor(string $name) : Author{
        $authorRepo = new DatabaseAuthorRepository($this->getPdo());
        $author = new Author($name);

        $authorRepo->store($author);

        return $author;
    }

    private function createCategory(string $name) : Category{
        $categoryRepo = new DatabaseCategoryRepository($this->getPdo());
        $category = Category::create(
            $this->createGenerator(),
            'PHP'
        );

        $categoryRepo->store($category);

        return $category;
    }

    protected function setUp() : void{
        $this->repository = new DatabaseBlogPostRepository($this->getPdo());
    }


    /** @test */public function can_store_a_blogpost()
    {
        $blogPost = new BlogPost(
            $this->createGenerator(),
            new HTMLParser(),
            $this->createAuthor('Mark'),
            $this->createCategory('PHP'),
            'New Blogpost',
            'New blogpost with a new introduction',
            'New blogpost with a very long content',
        );

        $this->repository->store($blogPost);

        $this->assertDatabaseContains('blogposts', [
            'slug' => 'new-blogpost',
            'category' => 'php',
            'title' => 'New Blogpost',
            'introduction' => 'New blogpost with a new introduction',
            'content' => 'New blogpost with a very long content',
            'status' => Draft::NAME,
            'parser' => HTMLParser::NAME,
        ]);
        $this->assertDatabaseContains('blogposts_authors', [
            'blogpost_slug' => 'new-blogpost',
            'author_name' => 'Mark'
        ]);
    }
}

The test is quite complicated due to all the needed setup work. There is also some duplication with the other database tests. At a later stage we will refactor that. For now it is not a problem.

public function store(BlogPost $blogPost): void
{
    $this->pdo->beginTransaction();

    try {
        $this->storeBlogPost($blogPost);
        $this->storeBlogPostAuthors($blogPost->getAuthors(), $blogPost->getSlug());
    } catch (PDOException $e) {
        $this->pdo->rollBack();
        throw $e;
    }

    $this->pdo->commit();
}

private function storeBlogPost(BlogPost $blogPost): void
{
    $statement = $this->pdo->prepare(
        'INSERT INTO blogposts ' .
            '(slug, category, title, introduction, content, status, parser)' .
        'VALUES' .
            '(?, ?, ?, ?, ?, ?, ?)'
    );
    $statement->execute([
        $blogPost->getSlug()->getUrl(),
        $blogPost->getCategory()->getSlug()->getUrl(),
        $blogPost->getTitle(),
        $blogPost->getIntroduction(),
        $blogPost->getContent(),
        $blogPost->getStatus(),
        $blogPost->getParserName()
    ]);
}

private function storeBlogPostAuthors(array $authors, Slug $slug): void
{
    foreach ($authors as $author) {
        $statement = $this->pdo->prepare(
            'INSERT INTO blogposts_authors ' .
            '(blogpost_slug, author_name) ' .
            'VALUES ' .
            '(?, ?)'
        );
        $statement->execute([
            $slug->getUrl(),
            $author->getName(),
        ]);
    }
}

When storing a blogpost we need to store a record in the blogposts table and the blogposts_authors table. The database is only correct after we inserted both records. To make sure that, if the second query fails, the first query is rolled back, I added the two queries in a transaction. After the two inserts took place, we commit the transaction. If there is an error, we catch the PDOException, we rollback the transaction.

To make the code more readable, I split up the code for adding the blogposts record and the blogposts_authors record in their own method.

As with the Category, we want to implement the blog post SlugRepository. We can add a test for that. For now we will ignore the old slugs. We will come back to that later.

private function createGenerator() : SlugGenerator
{
    return new SlugGenerator($this->repository);
}

private function createBlogpost(string $title = 'New blogpost') : BlogPost
{
    $blogPost = new BlogPost(
        $this->createGenerator(),
        new HTMLParser(),
        $this->createAuthor('Mark'),
        $this->createCategory('PHP'),
        $title,
        'New introduction for a new blogpost',
        'Very long content for a new blogpost'
    );
    $this->repository->store($blogPost);

    return $blogPost;
}

/** @test */
public function can_check_if_slug_exists()
{
    $slug = new Slug('new-blogpost');

    $this->assertFalse($this->repository->exists($slug));

    $this->createBlogpost(
        title: 'New blogpost'
    );

    $this->assertTrue($this->repository->exists($slug));
}

The DatabaseBlogPostRepository can implement the SlugRepository and implement the exists method:

public function exists(Slug $slug): bool
{
    $statement = $this->pdo->prepare(
        'SELECT EXISTS(SELECT * FROM blogposts WHERE slug = ?)'
    );
    $statement->execute([$slug->getUrl()]);

    return $statement->fetch()['exists'];
}

Fetching blogposts

Fetching blogposts is next. The first test:

/** @test */
public function can_fetch_a_draft_blogpost()
{
    $blogPost = $this->createBlogpost();

    $this->assertEquals(
        $blogPost,
        $this->repository->find($blogPost->getSlug())
    );
}

The test is simple and elegant, but to make this test green, we have to do quite a lot. First, we need to segregate the constructor for creating a blogpost and hydrating a blogpost:

private function __construct(
    SlugGenerator $generator,
    Slug $slug,
    array $authors,
    Category $category,
    string $title,
    string $introduction,
    string $content,
    Status $status,
    Parser $parser,
) {
    $this->generator = $generator;
    $this->slug = $slug;
    $this->authors = $authors;
    $this->category = $category;
    $this->title = $title;
    $this->introduction = $introduction;
    $this->content = $content;
    $this->status = $status;
    $this->parser = $parser;
}

public static function create(
    SlugGenerator $generator,
    Parser $parser,
    Author $author,
    Category $category,
    string $title,
    string $introduction,
    string $content,
) : BlogPost {
    self::validate($title, $introduction, $content);

    return new BlogPost(
        $generator,
        $generator->generate($title),
        [$author],
        $category,
        $title,
        $introduction,
        $content,
        new Draft(),
        $parser
    );
}

public static function hydrate(
    SlugGenerator $generator,
    Slug $slug,
    array $authors,
    Category $category,
    string $title,
    string $introduction,
    string $content,
    Status $status,
    Parser $parser,
) : BlogPost {
    return new BlogPost(
        $generator,
        $slug,
        $authors,
        $category,
        $title,
        $introduction,
        $content,
        $status,
        $parser
    );
}

Next, we can create the find method on the CategoryRepository and DatabaseCategoryRepository:

public function find(Slug $slug): BlogPost
{
    $authors = $this->findAuthors($slug);
    $blogPostData = $this->findBlogPosts($slug);

    return $this->createBlogPost($blogPostData, $authors);
}

private function findAuthors(Slug $slug): array
{
    $statement = $this->pdo->prepare(
        'SELECT * FROM blogposts_authors WHERE blogpost_slug = ?'
    );
    $statement->execute([$slug->getUrl()]);

    return array_map(function ($author) {
        return new Author($author['author_name']);
    }, $statement->fetchAll());
}

private function findBlogPosts(Slug $slug): array|bool
{
    $statement = $this->pdo->prepare(
        'SELECT blogposts.*, categories.name as category_name ' .
        'FROM blogposts ' .
        'JOIN categories ON categories.slug = blogposts.category ' .
        'WHERE blogposts.slug = ?'
    );
    $statement->execute([$slug->getUrl()]);

    return $statement->fetch();
}

private function createBlogPost(array $blogPostData, array $authors): BlogPost
{
    $blogPost = BlogPost::hydrate(
        new SlugGenerator($this),
        new Slug($blogPostData['slug']),
        $authors,
        Category::hydrate(
            new Slug($blogPostData['category']),
            $blogPostData['category_name']
        ),
        $blogPostData['title'],
        $blogPostData['introduction'],
        $blogPostData['content'],
        $this->getStatus($blogPostData['status'], $blogPostData['publish_date']),
        $this->getParser($blogPostData['parser'])
    );
    return $blogPost;
}

private function getParser(string $parser) : Parser
{
    $parsers = [
        HTMLParser::NAME => new HTMLParser(),
        MarkdownParser::NAME => new MarkdownParser(),
    ];

    return $parsers[$parser];
}

private function getStatus(string $status, ?string $publishDate) : Status
{
    switch ($status) {
        case Draft::NAME:
            return new Draft();

        case Scheduled::NAME:
            return new Scheduled(new \DateTimeImmutable($publishDate));

        case Published::NAME:
            return new Published();
    }
}

Again, we will have to refactor this code in the future. All the factory methods that we created are probably better located at their own factory classes, but currently the repository is the only place where we need the functionality. As long as that is the case, we will keep the code here.

Happy case implemented. Next up is the unhappy case. What happens if the blogpost doesn't exist. The next test describes this case:

/** @test */
public function will_throw_an_exception_when_blogpost_doesnt_exist()
{
    $this->expectException(BlogPostNotFound::class);

    $this->repository->find(new Slug('new-blogpost'));
}

And the implementation:

public function find(Slug $slug): BlogPost
{
    $authors = $this->findAuthors($slug);
    $blogPostData = $this->findBlogPosts($slug);

    if ($blogPostData === false) {
        throw new BlogPostNotFound('BlogPost with slug ' . $slug->getUrl() . ' not found');
    }

    return $this->createBlogPost($blogPostData, $authors);
}

Update blogpost

We implemented storing of a new blogpost, but we have also functionality to update a blogpost. We want to store the blogpost after updating.

Let's first define how this would look like in the simple case:

private function assertDatabaseContainsBlogPost(BlogPost $blogPost): void
{
    $this->assertDatabaseContains('blogposts', [
        'slug' => $blogPost->getSlug()->getUrl(),
        'category' => $blogPost->getCategory()->getSlug()->getUrl(),
        'title' => $blogPost->getTitle(),
        'introduction' => $blogPost->getIntroduction(),
        'content' => $blogPost->getContent(),
        'publish_date' => $blogPost->getPublishDate(),
        'status' => $blogPost->getStatus(),
        'parser' => $blogPost->getParserName(),
    ]);
}

/** @test */
public function can_store_an_updated_blogpost()
{
    $blogPost = $this->createBlogpost();
    $blogPost->update(
        author: $this->createAuthor('Andy'),
        title: $blogPost->getTitle(),
        introduction: 'Updated introduction to the updated blogpost',
        content: 'Updated content to the updated blogpost'
    );

    $this->repository->store($blogPost);

    $this->assertDatabaseContainsBlogPost($blogPost);
    $this->assertDatabaseCount('blogposts', 1);
}

We use the same interface for updating as we use for storing a blogpost. It is up to the repository to determine what kind of SQL query is necessary.

In the repository we use the exists method to determine if we need to update or insert the blogpost:

public function store(BlogPost $blogPost): void
{
    $this->pdo->beginTransaction();

    if ($this->exists($blogPost->getSlug())) {
        $this->updateBlogPost($blogPost);
    } else {
        $this->storeBlogPost($blogPost);
        $this->storeBlogPostAuthors($blogPost->getAuthors(), $blogPost->getSlug());
    }


    $this->pdo->commit();
}

protected function updateBlogPost(BlogPost $blogPost) : void
{
    $statement = $this->pdo->prepare(
        'UPDATE blogposts SET '.
            'title = ?, ' .
            'introduction = ?, ' .
            'content = ? ' .
        'WHERE slug = ?;'
    );
    $statement->execute([
        $blogPost->getTitle(),
        $blogPost->getIntroduction(),
        $blogPost->getContent(),
        $blogPost->getSlug()->getUrl(),
    ]);
}

An attentive reader will note that there are many issues with our current update code. To name a few:

  1. When we change the title, the slug will change and it will just insert a new blogpost
  2. The new author isn't added to the database
  3. When we transition the state of the blogpost, it isn't reflected in the database
  4. Old slugs aren't stored

Let's work through them one by one. The first one, if we change the title, it will change the slug.

/** @test */
public function can_store_an_updated_blogpost_with_changed_title_and_slug()
{
    $blogPost = $this->createBlogpost();
    $blogPost->update(
        author: $this->createAuthor('Andy'),
        title: 'Updated title',
        introduction: $blogPost->getIntroduction(),
        content: $blogPost->getContent()
    );

    $this->repository->store($blogPost);

    $this->assertDatabaseContainsBlogPost($blogPost);
    $this->assertDatabaseCount('blogposts', 1);
}

The test fails on the last line: there are two blogposts in the database instead of one. There is a problem: we don't know the slug from before we updated the blogpost. In case of a published blogpost we store the old slugs, but we don't do that for drafts or scheduled blogposts.

We could build a mechanism where we remember what the last slug was, but this is tricky. What if we update the blogpost twice before we store it? We can also ask the user of the BlogPost class to remember the last slug and supply it to us when saving. This brings extra load on the user of our class and I don't like that idea. The simplicity of just updating the blogpost and then storing it in the repository is nice. The last option is to add a different field as an identifier to our blogpost that doesn't change when we update it. Our database has a good option for that: an auto increment field.

For now I think that is the best option. First we update our database tables:

create table blogposts
(
    id serial not null,
    slug varchar(70) unique not null,
    category varchar(30) not null,
    title varchar(70) not null,
    introduction text not null,
    content text not null,
    publish_date timestamp,
    status varchar(30) not null,
    parser varchar(30) not null,

    primary key(id),
    foreign key(category) references categories(slug)
)

create table blogposts_authors
(
    blogpost_id integer not null,
    author_name varchar(30) not null,

    primary key(blogpost_id, author_name),
    foreign key(blogpost_id) references blogposts(id),
    foreign key(author_name) references authors(name)
);

create table old_slugs
(
    slug varchar(70) unique not null,
    blogpost_id integer not null,

    primary key(slug),
    foreign key(blogpost_id) references blogposts(id)
);

We need to update our repository to work with the new ID field:

class DatabaseBlogPostRepository implements BlogPostRepository, SlugRepository
{
    private PDO $pdo;

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

    public function store(BlogPost $blogPost): void
    {
        $this->pdo->beginTransaction();

        if ($blogPost->getId() === null) {
            $id = $this->storeBlogPost($blogPost);
            $this->storeBlogPostAuthors($blogPost->getAuthors(), $id);

            $blogPost->setId($id);
        } else {
            $this->updateBlogPost($blogPost);
        }


        $this->pdo->commit();
    }

    protected function updateBlogPost(BlogPost $blogPost) : void
    {
        $statement = $this->pdo->prepare(
            'UPDATE blogposts SET '.
                'slug = ?, ' .
                'title = ?, ' .
                'introduction = ?, ' .
                'content = ? ' .
            'WHERE id = ?;'
        );
        $statement->execute([
            $blogPost->getSlug()->getUrl(),
            $blogPost->getTitle(),
            $blogPost->getIntroduction(),
            $blogPost->getContent(),
            $blogPost->getId(),
        ]);
    }

    protected function storeBlogPost(BlogPost $blogPost): string
    {
        $statement = $this->pdo->prepare(
            'INSERT INTO blogposts ' .
                '(slug, category, title, introduction, content, status, parser)' .
            'VALUES' .
                '(?, ?, ?, ?, ?, ?, ?)'
        );
        $statement->execute([
            $blogPost->getSlug()->getUrl(),
            $blogPost->getCategory()->getSlug()->getUrl(),
            $blogPost->getTitle(),
            $blogPost->getIntroduction(),
            $blogPost->getContent(),
            $blogPost->getStatus(),
            $blogPost->getParserName()
        ]);

        return $this->pdo->lastInsertId('blogposts_id_seq');
    }

    protected function storeBlogPostAuthors(array $authors, string $id): void
    {
        foreach ($authors as $author) {
            $statement = $this->pdo->prepare(
                'INSERT INTO blogposts_authors ' .
                '(blogpost_id, author_name) ' .
                'VALUES ' .
                '(?, ?)'
            );
            $statement->execute([
                $id,
                $author->getName(),
            ]);
        }
    }

    public function exists(Slug $slug): bool
    {
        $statement = $this->pdo->prepare(
            'SELECT EXISTS(SELECT * FROM blogposts WHERE slug = ?)'
        );
        $statement->execute([$slug->getUrl()]);

        return $statement->fetch()['exists'];
    }

    public function find(Slug $slug): BlogPost
    {
        $blogPostData = $this->findBlogPosts($slug);

        if ($blogPostData === false) {
            throw new BlogPostNotFound('BlogPost with slug ' . $slug->getUrl() . ' not found');
        }

        $authors = $this->findAuthors($blogPostData['id']);

        return $this->createBlogPost($blogPostData, $authors);
    }

    private function findAuthors(string $id): array
    {
        $statement = $this->pdo->prepare(
            'SELECT * FROM blogposts_authors WHERE blogpost_id = ?'
        );
        $statement->execute([$id]);

        return array_map(function ($author) {
            return new Author($author['author_name']);
        }, $statement->fetchAll());
    }

    private function findBlogPosts(Slug $slug): array|bool
    {
        $statement = $this->pdo->prepare(
            'SELECT blogposts.*, categories.name as category_name ' .
            'FROM blogposts ' .
            'JOIN categories ON categories.slug = blogposts.category ' .
            'WHERE blogposts.slug = ?'
        );
        $statement->execute([$slug->getUrl()]);

        return $statement->fetch();
    }

    private function createBlogPost(array $blogPostData, array $authors): BlogPost
    {
        $blogPost = BlogPost::hydrate(
            id: $blogPostData['id'],
            generator: new SlugGenerator($this),
            slug: new Slug($blogPostData['slug']),
            authors: $authors,
            category: Category::hydrate(
                new Slug($blogPostData['category']),
                $blogPostData['category_name']
            ),
            title: $blogPostData['title'],
            introduction: $blogPostData['introduction'],
            content: $blogPostData['content'],
            status: $this->getStatus($blogPostData['status'], $blogPostData['publish_date']),
            parser: $this->getParser($blogPostData['parser'])
        );
        return $blogPost;
    }

    private function getParser(string $parser) : Parser
    {
        $parsers = [
            HTMLParser::NAME => new HTMLParser(),
            MarkdownParser::NAME => new MarkdownParser(),
        ];

        return $parsers[$parser];
    }

    private function getStatus(string $status, ?string $publishDate) : Status
    {
        switch ($status) {
            case Draft::NAME:
                return new Draft();

            case Scheduled::NAME:
                return new Scheduled(new \DateTimeImmutable($publishDate));

            case Published::NAME:
                return new Published();
        }
    }
}

We have to add the id field to our BlogPost class and allow it to set the ID after a blogpost object is created.

public static function hydrate(
    string $id,
    SlugGenerator $generator,
    Slug $slug,
    array $authors,
    Category $category,
    string $title,
    string $introduction,
    string $content,
    Status $status,
    Parser $parser,
) : BlogPost {
    $blogPost = new BlogPost(
        $generator,
        $slug,
        $authors,
        $category,
        $title,
        $introduction,
        $content,
        $status,
        $parser
    );
    $blogPost->setId($id);

    return $blogPost;
}

public function setId(string $id)
{
    $this->id = $id;
}

public function getId() : ?string
{
    return $this->id;
}

With the ID field we can update the store and updateBlogPost methods in our repository to bring all our tests back to green.

public function store(BlogPost $blogPost): void
{
    $this->pdo->beginTransaction();

    if ($blogPost->getId() === null) {
        $id = $this->storeBlogPost($blogPost);
        $this->storeBlogPostAuthors($blogPost->getAuthors(), $id);

        $blogPost->setId($id);
    } else {
        $this->updateBlogPost($blogPost);
    }


    $this->pdo->commit();
}

protected function updateBlogPost(BlogPost $blogPost) : void
{
    $statement = $this->pdo->prepare(
        'UPDATE blogposts SET '.
            'slug = ?, ' .
            'title = ?, ' .
            'introduction = ?, ' .
            'content = ? ' .
        'WHERE id = ?;'
    );
    $statement->execute([
        $blogPost->getSlug()->getUrl(),
        $blogPost->getTitle(),
        $blogPost->getIntroduction(),
        $blogPost->getContent(),
        $blogPost->getId(),
    ]);
}

When you just create a blogpost the ID is null. When we store it, the id is set. We can use that fact to our advantage in the store method to determine if we need to insert or update the blogpost.

The next test: we want the new author to be stored on the blogpost:

/** @test */
public function can_store_and_updated_blogpost_with_extra_author()
{
    $blogPost = $this->createBlogpost();
    $blogPost->update(
        author: $this->createAuthor('Andy'),
        title: 'Updated title',
        introduction: $blogPost->getIntroduction(),
        content: $blogPost->getContent(),
    );

    $this->repository->store($blogPost);

    $this->assertDatabaseContains('blogposts_authors', [
        'blogpost_id' => $blogPost->getId(),
        'author_name' => 'Mark'
    ]);
    $this->assertDatabaseContains('blogposts_authors', [
        'blogpost_id' => $blogPost->getId(),
        'author_name' => 'Andy'
    ]);
    $this->assertDatabaseCount('blogposts_authors', 2);
}

The easiest way to do this, is by deleting all authors related to the blogpost and then add the new ones again. We already wrote the code to link authors to a blogpost.

public function store(BlogPost $blogPost): void
{
    $this->pdo->beginTransaction();

    try {
        if ($blogPost->getId() === null) {
            $id = $this->storeBlogPost($blogPost);
            $blogPost->setId($id);
        } else {
            $this->updateBlogPost($blogPost);
            $this->deleteAuthors($blogPost->getId());
        }

        $this->storeBlogPostAuthors($blogPost->getAuthors(), $blogPost->getId());
    } catch (\PDOException $e) {
        $this->pdo->rollBack();
        throw $e;
    }

    $this->pdo->commit();
}

private function deleteAuthors(string $id)
{
    $statement = $this->pdo->prepare(
        'DELETE FROM blogposts_authors WHERE blogpost_id = ?'
    );
    $statement->execute([$id]);
}

State transitions aren't stored yet. The following test points that out:

/** @test */
public function can_store_a_blogpost_that_is_published()
{
    $blogPost = $this->createBlogpost();
    $blogPost->publish();

    $this->repository->store($blogPost);

    $this->assertDatabaseContainsBlogPost($blogPost);
}

Which is just a matter of adding the extra fields to the query in the repository update method:

private function updateBlogPost(BlogPost $blogPost) : void
{
    $statement = $this->pdo->prepare(
        'UPDATE blogposts SET '.
            'slug = ?, ' .
            'title = ?, ' .
            'introduction = ?, ' .
            'content = ?, ' .
            'status = ?, ' .
            'publish_date = ?' .
        'WHERE id = ?;'
    );
    $statement->execute([
        $blogPost->getSlug()->getUrl(),
        $blogPost->getTitle(),
        $blogPost->getIntroduction(),
        $blogPost->getContent(),
        $blogPost->getStatus(),
        $blogPost->getPublishDate()
            ? $blogPost->getPublishDate()->format('Y-m-d H:i:s')
            : null,
        $blogPost->getId(),
    ]);
}

And finally, we have to store all previous slugs:

/** @test */
public function stores_old_slugs_when_updating()
{
    $blogPost = $this->createBlogpost();
    $blogPost->publish();

    $blogPost->update(
        author: new Author('Andy'),
        title: 'New title',
        introduction: $blogPost->getIntroduction(),
        content: $blogPost->getContent()
    );

    $this->repository->store($blogPost);

    foreach ($blogPost->getOldSlugs() as $slug) {
        $this->assertDatabaseContains('old_slugs', [
            'slug' => $slug->getUrl(),
            'blogpost_id' => $blogPost->getId(),
        ]);
    }
}

We can solve this in the same way we solved the linking of authors. We just remove all slugs and add them again.

public function store(BlogPost $blogPost): void
{
    $this->pdo->beginTransaction();

    try {
        if ($blogPost->getId() === null) {
            $id = $this->storeBlogPost($blogPost);
            $blogPost->setId($id);
        } else {
            $this->updateBlogPost($blogPost);
            $this->deleteAuthors($blogPost->getId());
            $this->deleteOldSlugs($blogPost->getId());
        }

        $this->storeBlogPostAuthors($blogPost->getAuthors(), $blogPost->getId());
        $this->storeOldSlugs($blogPost->getOldSlugs(), $blogPost->getId());
    } catch (\PDOException $e) {
        $this->pdo->rollBack();
        throw $e;
    }

    $this->pdo->commit();
}

private function deleteOldSlugs(string $id)
{
    $statement = $this->pdo->prepare(
        'DELETE FROM old_slugs WHERE blogpost_id = ?'
    );
    $statement->execute([$id]);
}

private function storeOldSlugs(array $slugs, string $id)
{
    foreach ($slugs as $slug) {
        $statement = $this->pdo->prepare(
            'INSERT INTO old_slugs ' .
                '(slug, blogpost_id) ' .
            'VALUES ' .
                '(?, ?);'
        );
        $statement->execute([$slug->getUrl(), $id]);
    }
}

We are almost done. We store all data about the blogpost. There is only one problem left: our find method doesn't bring back all data.

Fix bugs in blogpost find method

We left two bugs in our find method:

  1. When we find a published blogpost, the repository hydrates it with the wrong published date
  2. When we find a scheduled blogpost after the published date, the repository throws a ScheduleError
  3. Old slugs aren't recovered from the database

Let's start with the first two problems:

/** @test */
public function can_fetch_a_scheduled_blogpost()
{
    $blogPost = $this->createBlogpost();
    $blogPost->schedule(new \DateTimeImmutable('+1 day'));
    $this->repository->store($blogPost);

    $this->assertEquals(
        $blogPost,
        $this->repository->find($blogPost->getSlug())
    );
}

/** @test */
public function can_fetch_a_published_blogpost()
{
    $blogPost = $this->createBlogpost();
    $blogPost->publish();
    $this->repository->store($blogPost);

    $this->assertEquals(
        $blogPost,
        $this->repository->find($blogPost->getSlug())
    );
}

Both, Scheduled and Published, need a different constructor when they are normally created versus when they are hydrated. We already solved this problem for Category and BlogPost. We can do it for Scheduled and Published in the same way.

private function getStatus(string $status, ?string $publishDate) : Status
{
    switch ($status) {
        case Draft::NAME:
            return new Draft();

        case Scheduled::NAME:
            return Scheduled::hydrate(new \DateTimeImmutable($publishDate));

        case Published::NAME:
            return Published::hydrate(new \DateTimeImmutable($publishDate));
    }
}

Which leaves us to the last feature for this article: getting the old slugs for a published blogpost:

/** @test */
public function can_fetch_old_slugs_for_a_published_blogpost()
{
    $blogPost = $this->createBlogpost();
    $blogPost->publish();
    $blogPost->update(
        $this->createAuthor('Andy'),
        'New Title',
        $blogPost->getIntroduction(),
        $blogPost->getContent()
    );

    $this->repository->store($blogPost);

    $this->assertEquals(
        $blogPost->getOldSlugs(),
        $this->repository
            ->find($blogPost->getSlug())
            ->getOldSlugs()
    );
}

And some simple code to bring this test to green

private function createBlogPost(array $blogPostData, array $authors): BlogPost
{
    $status = $this->getStatus(
        $blogPostData['status'],
        $blogPostData['publish_date']
    );
    $this->addOldSlugs($blogPostData['id'], $status);

    $blogPost = BlogPost::hydrate(
        id: $blogPostData['id'],
        generator: new SlugGenerator($this),
        slug: new Slug($blogPostData['slug']),
        authors: $authors,
        category: Category::hydrate(
            new Slug($blogPostData['category']),
            $blogPostData['category_name']
        ),
        title: $blogPostData['title'],
        introduction: $blogPostData['introduction'],
        content: $blogPostData['content'],
        status: $status,
        parser: $this->getParser($blogPostData['parser'])
    );
    return $blogPost;
}

private function addOldSlugs(string $id, Status $status) : void
{
    $statement = $this->pdo->prepare(
        'SELECT * FROM old_slugs WHERE blogpost_id = ?'
    );
    $statement->execute([$id]);
    $slugs = $statement->fetchAll();

    foreach ($slugs as $slug) {
        $status->addOldSlug(
            new Slug($slug['slug'])
        );
    }
}

And that's it for today from functionality perspective.

Refactoring our tests

Before we end this article I want to refactor our test a bit. There are a few tests that can be collapsed into one:

  • can_store_an_updated_blogpost
  • can_store_an_updated_blogpost_with_changed_title_and_slug
  • can_store_an_updated_blogpost_with_extra_author

These tests all want to verify if we can store an updated blogpost. We can check for the new title, slug and extra author in the can_store_an_updated_blogpost test and remove the other two:

/** @test */
public function can_store_an_updated_blogpost()
{
    $blogPost = $this->createBlogpost();
    $blogPost->update(
        author: $this->createAuthor('Andy'),
        title: 'Updated title',
        introduction: 'Updated introduction to the updated blogpost',
        content: 'Updated content to the updated blogpost'
    );

    $this->repository->store($blogPost);

    $this->assertDatabaseContainsBlogPost($blogPost);
    $this->assertDatabaseCount('blogposts', 1);

    $this->assertDatabaseContains('blogposts_authors', [
        'blogpost_id' => $blogPost->getId(),
        'author_name' => 'Mark'
    ]);
    $this->assertDatabaseContains('blogposts_authors', [
        'blogpost_id' => $blogPost->getId(),
        'author_name' => 'Andy'
    ]);
    $this->assertDatabaseCount('blogposts_authors', 2);
}

We can do a similar thing for can_store_a_blogpost_that_is_published and stores_old_slugs_when_updating. These can be collapsed in a single test:

/** @test */
public function can_store_a_published_blogpost()
{
    $blogPost = $this->createBlogpost();
    $blogPost->publish();

    $blogPost->update(
        new Author('Andy'),
        title: 'New title',
        introduction: $blogPost->getIntroduction(),
        content: $blogPost->getContent()
    );

    $this->repository->store($blogPost);

    $this->assertDatabaseContainsBlogPost($blogPost);

    foreach ($blogPost->getOldSlugs() as $slug) {
        $this->assertDatabaseContains('old_slugs', [
            'slug' => $slug->getUrl(),
            'blogpost_id' => $blogPost->getId(),
        ]);
    }
}

Finally, we can collapse can_fetch_a_published_blogpost and can_fetch_old_slugs_for_a_published_blogpost into a single test:

/** @test */
public function can_fetch_a_published_blogpost()
{
    $blogPost = $this->createBlogpost();
    $blogPost->publish();
    $blogPost->update(
        $this->createAuthor('Andy'),
        'New Title',
        $blogPost->getIntroduction(),
        $blogPost->getContent()
    );

    $this->repository->store($blogPost);

    $actualBlogPost = $this->repository->find($blogPost->getSlug());
    $this->assertEquals(
        $blogPost,
        $actualBlogPost
    );
    $this->assertEquals(
        $blogPost->getOldSlugs(),
        $actualBlogPost
            ->getOldSlugs()
    );
}

And that's it for today. The article is already much longer than I would have liked, but building a working persistence layer on a domain model is complicated.

Next article and source code

Now that we can fetch single stored blogposts, in the next article we will continue with the possibiltiy to fetch lists of blogposts. In the original requirements we stated that we want to fetch blogposts based on their category, status and author. We will implement those options in the next article.

The source code of the Blog project and the changes made in this article are available on Github:

Author

Mark Kazemier's avatar
Mark Kazemier

Hi, my name is Mark. I'm the founder of webdevils.nl and love developing websites and other web applications. Through Webdevils.nl I want to spread my enthousiasm about the web and PHP. In my professional live I'm a security expert specialised in security monitoring.

View all posts