Showing posts with label PHP. Show all posts
Showing posts with label PHP. Show all posts

Monday 1 May 2023

PHP / Symfony: working through "Symfony: The Fast Track", part 5: Twig stuff, and irritation

G'day:

I'm back on the case working through "Symfony: The Fast Track". This will be part five of this series, after the first four:

The page I'm starting on is Building the User Interface, which seems more interesting than the previous section. I also see mention of my old friend Twig in the first subsection of this, so it will be good to look at that some more and see how it's moved along since I last did PHP UI stuff about 7-8 years ago (god is it that long?).


Using Twig for the Templates

The first thing I need to do is to restore a twig file I deleted during the last exercise, thinking it was redundant. So: back comes templates/base.html.twig. I'll be referring back to this, so will dump it out here, even though it's boilerplate, and not my code:

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>{% block title %}Welcome!{% endblock %}</title>
    <link
    	rel="icon"
        href="
            data:image/svg+xml,
            <svg
            	xmlns=%22http://www.w3.org/2000/svg%22
                viewBox=%220 0 128 128%22
            >
                <text y=%221.2em%22 font-size=%2296%22>⚫️</text>
            </svg>
        "
    >
    {# Run `composer require symfony/webpack-encore-bundle` to start using Symfony UX #}
    {% block stylesheets %}
        {{ encore_entry_link_tags('app') }}
    {% endblock %}

    {% block javascripts %}
        {{ encore_entry_script_tags('app') }}
    {% endblock %}
</head>
<body>
{% block body %}{% endblock %}
</body>
</html>

And one for templates/conference/index.html.twig:

{% extends 'base.html.twig' %}

{% block title %}Conference Guestbook{% endblock %}

{% block body %}
    <h2>Give your feedback!</h2>

    {% for conference in conferences %}
        <h4>{{ conference }}</h4>
    {% endfor %}
{% endblock %}

Note that it inherits from the base one, and overrides its title and body blocks. I'll not discuss all the syntax vagaries as they're well documented: Twig Documentation. It's easy enough to read and infer what's going on, anyhow.

The next section is entitled "Using Twig in a Controller" and I panicked as I thought they meant they were gonna put Twig code into a controller method (remember they've been a bit woolly about what goes in a controller already, so it would not have surprised me), but fortunately no. It's just describing how to use the twig file:

public function index(Environment $twig, ConferenceRepository $conferenceRepository): Response
{
    // return of inline HTML (!!) removed

    return new Response($twig->render(
        "conference/index.html.twig",
        ["conferences" => $conferenceRepository->findAll()]
    ));
}

The conferences there are then used in the loop in the twig.


Creating the Page for a Conference

OK so here's a cool thing Symfony does. I am creating the page for the route /conference/{id}, and if my controller method takes a Conference object, Symfony is clever enough to go "I see what yer doing there" and goes and gets the conference for that ID. EG, I've added this routing to get to my controller method:

config/routes/conference.yaml

# …

show:
    path: /{id}
    methods: [ GET ]
    controller: adamcameron\symfonythefasttrack\Controller\ConferenceController::show

And the controller method the exercise has me write is this (nothing noteworthy really):

public function show(Environment $twig, Conference $conference, CommentRepository $commentRepository): Response
{
    return new Response($twig->render(
        "conference/show.html.twig",
        [
            "conference" => $conference,
            "comments" => $commentRepository->findBy(
                ["conference" => $conference],
                ["createdAt" => "DESC"]
            )
        ]
    ));
}

The routing takes an ID; the controller receives the equivalent Conference object.

Note that whilst it's neat that Symfony does this, that controller they've had me write is borderline not great MVC in my books. I don't really think controllers should be aware of things like "repositories". Repositories are a storage consideration, and should be hidden away with the model. What should be happening here I think is that the controller receives its Conference, but the Conference should implement whatever is necessary to get its Comment collection from wherever it put it (and via whatever mechanism it did it with). This is not the controller's job. This is a good example of how using generic ORM crap leads to subpar code. I realise this is a tutorial about using Twig, but I think on the whole this book is encouraging some pretty poor design decisions because that better shows-off Symfony's bells and whistles.

Ahem. Moving along…

For reference here's the twig template for the view:

{% extends 'base.html.twig' %}

{% block title %}Conference Guestbook - {{ conference }}{% endblock %}

{% block body %}
    <h2>{{ conference }} Conference</h2>

    {% if comments|length > 0 %}
        {% for comment in comments %}
            {% if comment.photofilename %}
                <img src="{{ asset('uploads/photos/' ~ comment.photofilename) }}"/>
            {% endif %}

            <h4>{{ comment.author }}</h4>
            <small>
                {{ comment.createdAt|format_datetime('medium', 'short') }}
            </small>

            <p>{{ comment.text }}</p>
        {% endfor %}
    {% else %}
        <div>No comments have been posted yet for this conference.</div>
    {% endif %}
{% endblock %}

And the test shows the initial part of the template implementation presents the detail from the correct conference correctly:

/** @testdox /conference/{id} end point returns a 200 OK for a valid conference */
public function testShow()
{
    $kernel = new Kernel("test", true);
    $kernel->boot();

    $container = $kernel->getContainer();

    $connection = $container->get("database_connection");
    $testConference = $connection->executeQuery("
        SELECT id, city, year
        FROM conference LIMIT 1
    ")->fetchAssociative();
    if (!$testConference) {
        $this->markTestSkipped("No conferences in database, test aborted");
    }

    $client = static::createClient();
    $client->request('GET', '/conference/' . $testConference['id']);

    $this->assertResponseIsSuccessful();

    $this->assertSelectorTextContains(
        'title',
        sprintf(
            "Conference Guestbook - %s %s",
            $testConference['city'],
            $testConference['year']
        )
    );
}

I presumed it would 404 if the {id} requested wasn't in the DB:

/** @testdocs /conference/{id} end point returns a 404 if the conference is not found */
public function testShowNotFound()
{
    $client = static::createClient();
    $client->request('GET', '/conference/999999');

    $this->assertResponseStatusCodeSame(Response::HTTP_NOT_FOUND);
}

And this passes. NB: the 404 page is a general Symfony one, but that's fine. It's handling it correctly.

It's important to note that I have not written any code relating to Conferences or the storage thereof in this chapter of the exercise. I'm simply using the stuff Symfony generated for me during the previous exercise.

Oh one additional thing I did need to do was to install the twig/intl-extra dependency, because I'm using that format_datetime Twig filter. How did I know I needed to do this? Symfony told me (well, OK, I have also just scrolled ahead in the lesson, and it also told me there):

That's quite cool. I note it's claiming it's a syntax error though, which it is not. If it was a syntax error, the code wouldn't compile, let alone run to the point to which it works out I have a Twig filter missing, and which one it is and where to get it from. It's a MissingTwigExtensionError or something. But a runtime error of some sort, not a compile-time one. Still: semantics aside, it's helpful. It does seem a bit odd to me though that something in either Twig or Symfony clearly knows what all the extensions are and what external libs they're in. This seems a bit of an inappropriate overreach to me, and a bit of a break of the Open-Closed Principle. Still: productivity over dogma, eh?

Oh! So far I have no comments in the DB, so all that Twig bit about rendering them doesn't do anything. All I'm getting is this:

Time to test the comments bit, before I move on.

[… more time passes than I would like to admit…]

I went round the houses with this a bit. I had to install a bunch of Node.js stuff in my Node container because one of the Twig function's internal workings checked for [something] and if it didn't exist: threw a PHP exception. I didn't preserve the error, but this code (in show.html.twig) was the culprit:

<img src="{{ asset('uploads/photos/' ~ comment.photofilename) }}"/>

The asset function was complaining that public/build/entrypoints.json was missing. I googled about and found out I needed to have symfony/webpack-encore-bundle installed, the instructions for which (that previous link) seem to not be as complete as they could be for codebases not using Symfony Flex. Still: the residual issues I had were all fairly googleable, and easily fixed. Sorry to be a bit vague there, but I did this lot on Fri/Sat - it's now Sun - and I can't be arsed digging through my browser history to recreate the - fairly unrewarding - sequence of hurdles I bumbled over to get my PHP app to stop erroring because I didn't have some webpack shit configured properly.

BTW: this is another thing I don't think a server-side MVC framework should be investing its time in. There's plenty of client-side frameworks for handling client-side stuff. Server-side frameworks should just busy themselves with their own job. Seems to me like another case of "if your only tool is a hammer, every problem starts looking like a nail".

Anyway, my test stopped erroring, and after some small effort I got it to go green. I actually ended up with two tests here:

/** @testdox /conference/{id} also displays any comment associated with the conference */
public function testShowComments()
{
    $testConference = $this->getConferenceToTestWith();

    $client = static::createClient();
    $client->request("GET", "/conference/" . $testConference->getId());

    $this->assertResponseIsSuccessful();
    $this->assertCommentersAreCorrect($client, $testConference);
}

private function getConferenceToTestWith(): Conference
{
    $container = Container::getContainer();

    $repo = $container->get("testing.ConferenceRepository");
    $qb = $repo->createQueryBuilder("cf");

    $qb->select("cf")
        ->setMaxResults(1)
        ->leftJoin("cf.comments", "cm")
        ->groupBy("cf.id")
        ->having("count(cm.id) > 1");
    $testConference = $qb->getQuery()->getResult();

    if (!$testConference) {
        $this->markTestSkipped("No suitable conferences in database, test aborted");
    }

    return $testConference[0];
}

private function getExpectedCommenters(Conference $testConference): array
{
    $expectedCommenters = $testConference->getComments()->toArray();
    usort($expectedCommenters, function ($e1, $e2) {
        return $e2->getCreatedAt() <=> $e1->getCreatedAt();
    });
    $expectedCommenters = array_map(function ($comment) {
        return $comment->getAuthor();
    }, $expectedCommenters);
    return $expectedCommenters;
}

private function assertCommentersAreCorrect(KernelBrowser $client, Conference $testConference): void
{
    $commenters = $client
        ->getCrawler()
        ->filter("body > h4")
        ->each(function ($node) {
            return $node->text();
        });

    $expectedCommenters = $this->getExpectedCommenters($testConference);
    $this->assertEquals($expectedCommenters, $commenters);
}

This one goes right through from the controller to the DB and back: comparing the data from the DB (expected) matching the data returned in the mark-up (actual).

I don't wanna be hitting the DB in my tests as a rule, especially if it's only a mechanism to get data to then see what the logic does with it. If I'm testing the logic, I want the expediency of using canned data that best exercises the logic. I tried to write a unit test of this, but it proved to be more difficult than I had patience for. The problem being that all the "logic" is in the controller method:

public function show(
    Environment $twig,
    Conference $conference,
    CommentRepository $commentRepository
): Response {
    return new Response($twig->render(
        "conference/show.html.twig",
        [
            "conference" => $conference,
            "comments" => $commentRepository->findBy(
                ["conference" => $conference],
                ["createdAt" => "DESC"]
            )
        ]
    ));
}

So to unit test that, I need to be able to mock the collaborators. Mocking my ones - the Conference and the CommentsRepository - was easy. Dealing with the Twig Environment was beyond my patience. And I wasn't even trying to do anything fancy with it: all I wanted was to spy on it so I could test the values exiting my test unit. But Symfony is very very convoluted in how it configures things, and I could not work out a minimum Environment that would actually initialise without erroring. Please note that I was very aware of "don't mock what you don't own" during all of this, and beyond a point I had decided that this was the wrong approach as well as being a fool's errand.

In the end I wrote a more functional test. I stuck with the Environment that Symfony had configured in its DI container, and the checked the results of its rendering. This is not so bad: it's all just done in code and no hits to external services (the DB), but it resulted in more code than I'm really happy with. There's no need to pore over this: I'm not gonna discuss it further. I only included to show the amount of effort involved.

/** @testdox /conference/{id} displays correct conference and comments */
public function testShow()
{
    $conferenceTitle = "MOCKED_CONFERENCE";
    list($twigEnvironment, $conference, $commentRepository) = $this->getTestDependencies($conferenceTitle);
    $controller = new ConferenceController();

    $result = $controller->show($twigEnvironment, $conference, $commentRepository);

    $this->assertResponseIsCorrect($result, $conferenceTitle, $commentRepository);
}

private function getTestDependencies(string $conferenceTitle): array
{
    $twigEnvironment = self::getContainer()->get(TwigEnvironment::class);

    $conference = $this->getMockedConference($conferenceTitle);

    $commentRepository = $this->getMockedCommentRepository();
    return array($twigEnvironment, $conference, $commentRepository);
}

private function getMockedConference(string $conferenceTitle): Conference
{
    $conference = $this->getMockBuilder(Conference::class)
        ->disableOriginalConstructor()
        ->getMock();
    $conference
        ->expects($this->any())
        ->method("__toString")
        ->willReturn($conferenceTitle);
    return $conference;
}

private function getMockedCommentRepository(): CommentRepository
{
    $commentCount = 3;
    $commentsToTestFor = range(1, $commentCount);

    $testComments = array_map(function ($i) {
        return (new Comment())
            ->setAuthor("COMMENTER" . $i)
            ->setText("COMMENT" . $i)
            ->setCreatedAt(new \DateTimeImmutable("2020-01-0{$i}"));
    }, $commentsToTestFor);

    $commentRepository = $this->getMockBuilder(CommentRepository::class)
        ->disableOriginalConstructor()
        ->getMock();
    $commentRepository
        ->method("findBy")
        ->willReturn($testComments);
    return $commentRepository;
}

private function assertResponseIsCorrect(Response $result, string $conferenceTitle, mixed $commentRepository): void
{
    $this->assertEquals(Response::HTTP_OK, $result->getStatusCode());

    $xpathDocument = $this->getContextAsXpathDocument($result);
    $this->assertTitleIsCorrect($xpathDocument);
    $this->assertSubheadingIsCorrect($xpathDocument, $conferenceTitle);
    $this->assertCommentsAreCorrect($xpathDocument, $commentRepository);
}

private function getContextAsXpathDocument(Response $result): DOMXPath
{
    $content = $result->getContent();
    $document = new DOMDocument();
    $document->loadHTML($content, LIBXML_NOWARNING | LIBXML_NOERROR);
    $xpathDocument = new DOMXPath($document);
    return $xpathDocument;
}

private function assertTitleIsCorrect(DOMXPath $xpathDocument): void
{
    $title = $xpathDocument->query('/html/head/title[text()]');
    $this->assertCount(1, $title);
    $this->assertEquals("Conference Guestbook - MOCKED_CONFERENCE", $title->item(0)->textContent);
}

private function assertSubheadingIsCorrect(DOMXPath $xpathDocument, string $conferenceTitle): void
{
    $h2 = $xpathDocument->query('/html/body/h2[text()]');
    $this->assertCount(1, $h2);
    $this->assertEquals("$conferenceTitle Conference", $h2->item(0)->textContent);
}

private function assertCommentsAreCorrect(DOMXPath $xpathDocument, CommentRepository $commentRepository): void
{
    $testComments = $commentRepository->findBy([], []);
    $commentCount = count($testComments);

    $commentAuthors = $xpathDocument->query('/html/body/h4[text()]');
    $this->assertCount($commentCount, $commentAuthors);
    $commentTexts = $xpathDocument->query('/html/body/p[text()]');
    $this->assertCount($commentCount, $commentTexts);
    $commentDates = $xpathDocument->query('/html/body/small[text()]');
    $this->assertCount($commentCount, $commentDates);

    array_walk($testComments, function ($comment, $i) use ($commentAuthors, $commentTexts, $commentDates) {
        $this->assertEquals($comment->getAuthor(), $commentAuthors->item($i)->textContent);
        $this->assertEquals($comment->getText(), $commentTexts->item($i)->textContent);

        $commentCreatedAtFormatted = $comment->getCreatedAt()->format("M j, Y, h:i A");
        $this->assertEquals($commentCreatedAtFormatted, trim($commentDates->item($i)->textContent));
    });
}

There's a moral to all this. "Don't mock what you don't own" issues aside, testing code that is tightly-coupled to the framework - like a controller method pretty much is - is gonna have overhead. As such: keep as little code in yer controllers as possible: have yer business logic within yer model, because you "own" this, and there will be less over-engineers framework code getting in the way.


Linking Pages Together & Pagination & some refactoring

Next they've got some more Twig advice about using its methods to build predictable URLs insted of hard-coding them, eg:

<a href="/conference/{{ conference.id }}">View</a>
<a href="{{ path('conference', { id: conference.id }) }}">View</a>

Good advice. I'm not gonna dwell on it further than that, other than sticking this code into my Twig template (after I test for it, obvs). I'll spare you the code, but it'll be in source control if you decide you do want to look at it. I got slightly tripped-up there as I had changed my route's name to be conference_show, and that reference to conference is the route's name. Easily fixed though.

Next it shows how to paginate record sets in the UI, which is pleasingly straight forward. A method in the repository:


public const PAGINATOR_PER_PAGE = 2;

// …

public function getPaginator(Conference $conference, int $offset): Paginator
{
    $query = $this->createQueryBuilder('c')
        ->andWhere('c.conference = :conference')
        ->setParameter('conference', $conference)
        ->orderBy('c.createdAt', 'DESC')
        ->setMaxResults(self::PAGINATOR_PER_PAGE)
        ->setFirstResult($offset)
        ->getQuery();

    return new Paginator($query);
}

Pass a paginator instead of the articles from the controller:

public function show(
    Request $request,
    Environment $twig,
    Conference $conference,
    CommentRepository $commentRepository
): Response {
    $offset = max(0, $request->query->getInt('offset', 0));
    $paginator = $commentRepository->getPaginator($conference, $offset);

    return new Response($twig->render(
        "conference/show.html.twig",
        [
            "conference" => $conference,
            "comments" => $commentRepository->findBy(["conference" => $conference], ["createdAt" => 'DESC']),
            "comments" => $paginator,
            'previous' => $offset - CommentRepository::PAGINATOR_PER_PAGE,
            'next' => min(count($paginator), $offset + CommentRepository::PAGINATOR_PER_PAGE)
        ]
    ));
}

And then add the pagination stuff to the view:

<div>There are {{ comments|length }} comments.</div>
{% for comment in comments %}
    {% if comment.photofilename %}
        <img src="{{ asset('uploads/photos/' ~ comment.photofilename) }}"/>
    {% endif %}

    <h4>{{ comment.author }}</h4>
    <small>
        {{ comment.createdAt|format_datetime('medium', 'short') }}
    </small>

    <p>{{ comment.text }}</p>
{% endfor %}

{% if previous >= 0 %}
    <a href="{{ path('conference_show', { id: conference.id, offset: previous }) }}">Previous</a>
{% endif %}
{% if next < comments|length %}
    <a href="{{ path('conference_show', { id: conference.id, offset: next }) }}">Next</a>
{% endif %}

The only change I made to this was that the suggested name for the pagination method was getCommentPaginator, but given it was in the CommentRepository, I thought repeating "comment" there was tautological, so changed it to getPaginator.

Oh, the page now looks like this:

Cool.

One thing to note here is that in refactoring my tests of the ConferenceController show method to accommodate the pagination, I pushed the coupling of the test class over the top of what PHPMD was happy with (the threshold is the default 13 classes), so I refactored that into three classes: Test, Assertions and Dependencies.

tests
`-- php
    `-- Functional
        `-- Controller
            `-- ConferenceController
                |-- Index
                |   `-- Test.php
                `-- Show
                    |-- Assertions.php
                    |-- Dependencies.php
                    `-- Test.php

I think this is all caused by testing code in a controller; having to mess around with Requests and Environments and having to test for rendered mark-up instead of just values-in and value-out adds "unnecessary" overhead. I am realy irked (in a laughing way) by what I just read in the next section which is the refactoring bit…

You might have noticed that both methods in ConferenceController take a Twig environment as an argument. Instead of injecting it into each method, let's leverage the render() helper method provided by the parent class

Symfony the Fast Track › Building the User Interface › Refactoring the Controller

Oh FFS. Ha. OK. Fine. Tell me that now.

So I can simplify things a bit:

public function show(
    Request $request,
    Environment $twig,
    Conference $conference,
    CommentRepository $commentRepository
): Response {
    $offset = max(0, $request->query->getInt('offset', 0));
    $paginator = $commentRepository->getPaginator($conference, $offset);

    return new Response($twig->render('conference/index.html.twig', [
    return $this->render(
        "conference/show.html.twig",
        [
            "conference" => $conference,
            "comments" => $paginator,
            'previous' => $offset - CommentRepository::PAGINATOR_PER_PAGE,
            'next' => min(count($paginator), $offset + CommentRepository::PAGINATOR_PER_PAGE)
        ]
    );
    ]));
}

That's good, but I really don't know why they didn't lead with that. There was nothing that relied on the Twig Environment in those methods, so why pass it in? Just to give an extra paragraph at the end?

Oh I also had to tweak a couple of the tests that broke because of this: for that render method to be available, I needed to initialise the controller class with the DI container. This was easy though:

$controller = new ConferenceController();
$container = self::getContainer();
$controller->setContainer($container);

End of section / article

Man. I only got through one page of the docs with this article, and it also took me four bites to write, even though it didn't really cover much ground. I think the issue is that a) I lack self-discipline; b) I'm not finding this stuff particularly compelling. I also got deeply frustrated with the amount of dicking around I had to do to try to convince Symfony to let me at its dependencies when I was trying to test stuff, and also the waste of time that was getting Encore working, because - as I said earlier - client-side libs are nothing to do with a server-side MVC framework, and should not get in the way. The more I use Symfony, the more I miss Silex.

I'm not looking forward to the re-drafting & read-throughs ofthis before I press "send", as I suspect it's a bit of a half-hearted mess. But I've come this far, so I'm buggered if I'm not publishing it. Well done if you read this far.

Righto.

--
Adam

Friday 7 April 2023

PHP / Symfony: working through "Symfony: The Fast Track", part 4: not really about Symfony, this one

G'day:

Once again I'm gonna continue working through "Symfony: The Fast Track". This will be part four of this series, after the first three:

The page I'm starting on is Setting up an Admin Backend, which sounds very situation-specific to me (so: not very portable knowledge to acquire), but I guess I'll learn "Symfony's way of ~". And there might be some useful stuff in there. Let's see.


Setting up an Admin Backend

Installing more dependencies

First they describe some Symfony concepts: Symfony Components: low-level stuff like routing, HTTP etc; and Symfony Bundles: higher-level stuff like wrappers for third-party libs. Fair enough.

Hrm. More Symfony opinions now. There's a "feature" of Symfony "aliases". Their wording:

Aliases are shortcuts for popular Composer packages. Want an ORM for your application? Require orm. Want to develop an API? Require api. These aliases are automatically resolved to one or more regular Composer packages. They are opinionated choices made by the Symfony core team.
Symfony: The Fast Track › Setting up an Admin Backend › Installing more Dependencies

Righto then.

This is a bit pathetic:

Another neat feature is that you can always omit the symfony vendor. Require cache instead of symfony/cache.
ibid

Mate. Yer bragging about omitting seven keystrokes. Well: other than the fact I've got to type in symfony composer rather than just composer. How was this even a good use of anyone's dev time, let alone brag about it being not only a feature, but a "neat" one? Sigh.

They're getting me to do this: symfony composer req "admin:^4", which installs easycorp/easyadmin-bundle, and adds a reference to it in config/bundles.php,and adds config/packages/uid.yaml. The latter looks intriguing, I guess I'll find about about it later.


Configuring EasyAdmin

Next up I'm configuring this EasyAdmin thing:

root:/var/www# symfony console make:admin:dashboard

 Which class name do you prefer for your Dashboard controller? [DashboardController]:
 >

 In which directory of your project do you want to generate "DashboardController"? [src/Controller/Admin/]:
 >

[OK] Your dashboard class has been successfully generated.
Next steps: * Configure your Dashboard at "src/Controller/Admin/DashboardController.php" * Run "make:admin:crud" to generate CRUD controllers and link them from the Dashboard. root:/var/www#

This has added a controller with some boilerplate (I'll spare you, but I'll link through to it in source control once it's there). There's a slight bug in the generation, in that it has not picked up my app's PSR-4 namespace, so it's written out this:

namespace App\Controller\Admin;
Which I will change to be:
namespace adamcameron\symfonythefasttrack\Controller\Admin;

I kinda think this should have looked at the maker.yaml config file that Symfony itself suggested I create to set the root_namespace (see It looks like your app may be using a namespace other than "App" in the second article of this series). Ah well: never mind: it's an easy fix.

It's also put an annotation in to handle the routing which I am not gonna run with, I'll stick it in the routing config where it belongs.

And because I'm changing something here, I'm gonna make a test for it. I should really have done this before running the wizard I guess. I didn't think about it. I also didn't know what the wizard was gonna do at the time, however I could have read ahead. Anyways, this has configured a /admin/ end point, so I will make sure it returns a 200:

namespace adamcameron\symfonythefasttrack\tests\Acceptance\Controller;

use Symfony\Bundle\FrameworkBundle\Test\WebTestCase;
use Symfony\Component\HttpFoundation\Response;

/** @testdox Tests the endpoints in the DashboardController */
class DashboardControllerTest extends WebTestCase
{
    /** @testdox The index endpoint returns a 200 response */
    public function testIndex()
    {
        $client = static::createClient();
        $client->request('GET', '/admin/');

        $this->assertEquals(Response::HTTP_OK, $client->getResponse()->getStatusCode());
    }
}

(This changes in the next step to be a 302, but I adjusted the test accordingly behind the scenes).

I've added a new test suite too, for acceptance tests (hitting the "front" of pages, and verifying they do whatever they are supposed to. For now, 200ing will be fine).

This test still passes after I reconfigure the routing. And I also get a page rendering in the browser:

Next I'm building a CRUD UI for the entities I've created. This is done via symfony console make:admin:crud.I'll spare you the detail unless there's something interesting.

[…]

Nope, nothing interesting except it asked me what namespace to use this time, so I was able to give it the correct one. It defaulted to Symfony's default App one though.

The process has created two skeleton controller classes. They don't have any routing in them, which is "interesting". The next step is wiring them into that initial dashbaord page I created, so let's see - just in the browser - what happens when I've done that.

public function configureMenuItems(): iterable
{
    yield MenuItem::linkToDashboard('Dashboard', 'fa fa-home');
    yield MenuItem::linktoRoute('Back to the website', 'fas fa-home', 'homepage');
    yield MenuItem::linkToCrud('Conferences', 'fas fa-map-marker-alt', Conference::class);
    yield MenuItem::linkToCrud('Comments', 'fas fa-comments', Comment::class);
}

A controller should only be busying itself with marrying-up which model should provide the data for the response, and which view to use to render the data. Here it is defining the data too. I mean this is a third-party package it's demonstrating here, so this approach is not strictly Symfony's doing; but Symfony is choosing to use this package, so I don't think it's great that official Symfony guidance should be encouraging poor practice like this.

It's also got me to reconfigure the index route handler method to redirect to the CRUD UI for conferences:

public function index(): Response
{
    $routeBuilder = $this->container->get(AdminUrlGenerator::class);
    $url = $routeBuilder->setController(ConferenceCrudController::class)->generateUrl();

    return $this->redirect($url);
}

When I reloaded the index page, I got a 500 error: the conference table was missing. Ah. I have rebuilt the containers since the last article, and I blew away the volume the DB data was in. I need to rerun the migration to get those tables back.


"Doctrine: know your limits!"(*)

Oh for goodness sake.

I just re-ran the migration to recreate the Conference and Comment tables, and I noticed... Symfony blew away the rest of the tables in the database (OK, granted, there was only one other table). WTH, Symfony? Then I looked at the migration file:

public function up(Schema $schema): void
{
    // this up() migration is auto-generated, please modify it to your needs
   // [snip for brevity]
    $this->addSql('DROP TRIGGER IF EXISTS notify_trigger ON messenger_messages;');
    $this->addSql('CREATE TRIGGER notify_trigger AFTER INSERT OR UPDATE [etc]');
    $this->addSql('ALTER TABLE comment ADD CONSTRAINT FK_9474526C604B8382 FOREIGN KEY [etc]');
    $this->addSql('DROP TABLE test');
}

You what, son?. Who the hell told you to do that? (/me hastily checks the make:migration process to confirm I didn't just go "yeah yeah yeah, delete the other tables. Cos like of course I want you to do that". No, I had not).

I googled about the place, and found this: Doctrine › Migrations › Generating Migrations › Ignoring Custom Tables:

If you have custom tables which are not managed by Doctrine you will need to tell Doctrine to ignore these tables. Otherwise, everytime you run the diff command, Doctrine will try to drop those tables. You can configure Doctrine with a schema filter.

What? Just: no. Doctrine, I have said "map these entities". Your job is to - let's see if you follow my thinking here - map these entities. Nothing else. Leave the rest of the frickin DB alone. It's not your business. Jesus.

At least they go on to say:

If you use the DoctrineBundle with Symfony you can set the schema_filter option in your configuration.

And over on the Symfony side of things: Symfony › Bundles › DoctrineMigrationsBundle › Manual Tables:

If you follow a specific scheme you can configure doctrine/dbal to ignore those tables. Let's say all custom tables will be prefixed by t_. In this case you just have have to add the following configuration option to your doctrine configuration:

doctrine:
    dbal:
        schema_filter: ~^(?!t_)~

Note that if you have multiple connections configured then the schema_filter configuration will need to be placed per-connection.

OK, two things.

  • What if you're not a lunatic from the 1990s and don't put hungarian notation on the beginning of things, so there isn't a prefix to match all the rest of the tables in your database?
  • Why the hell are you tightly coupling this to connection config? If I don't want Doctrine to delete my shit, then that's not gonna be - by default - connection specific. It's gonna be a blanket "don't delete my shit, you weirdo!?" across the board. I can see how maybe it could be overridden on a connection-by-connection basis (maybe?), but this should be a top-level Doctrine config thing (and "don't do it" being the default behaviour).

Ugh. But OK, I've added this to my connection:

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                wrapper_class: Doctrine\DBAL\Connections\PrimaryReadReplicaConnection
                dbname: '%env(resolve:POSTGRES_PRIMARY_DB)%'
                host: '%env(resolve:POSTGRES_PRIMARY_HOST)%'
                port: '%env(resolve:POSTGRES_PRIMARY_PORT)%'
                user: '%env(resolve:POSTGRES_PRIMARY_USER)%'
                password: '%env(resolve:POSTGRES_PRIMARY_PASSWORD)%'
                driver: pdo_pgsql
                server_version: 15
                charset: utf8
                schema_filter: ~^(?!test)~
                replicas:
                    replica1:
                        dbname: '%env(resolve:POSTGRES_REPLICA_DB)%'
                        host: '%env(resolve:POSTGRES_REPLICA_HOST)%'
                        port: '%env(resolve:POSTGRES_REPLICA_PORT)%'
                        user: '%env(resolve:POSTGRES_REPLICA_USER)%'
                        password: '%env(resolve:POSTGRES_REPLICA_PASSWORD)%'
                        charset: utf8

I guess I'm lucky I only have the one table to exclude, or that "pattern" could get quite weighty, quite quickly.

As an aside: I found out that when running symfony console make:migration, it'll try to run all the files in the migrations directory. As each has its own time-stamp-unique file name, I probably only want to keep the most recent one in source control? Or at least only one in that directory at any given time, anyhow. There might be a way of telling it to only run one migration. I should look into that.

Anyway: I have all my tables back in the DB now, so the page renders an empty Conference CRUD page:

And it all works fine once I allow the /admin/ route to process POST requests instead of just GETs (this is on me: I didn't realise I'd be needing to allow POSTs when I set up the routing).

I had less luck initially adding in comments, as the steps are slightly out of order in the book. I got onto this fix via googling "symfony the fast track comment UI broken", and landed on the GitHub issue to get it fixed: [Book] Step 9 Issue: Cannot create new Comment. So: in case you are following along here: after the step of testing/trying out the "Add conference" functionality, don't continue to add a comment; skip ahead and do the "Customizing EasyAdmin" bit first. It's just the next step.

This step adds code to configure how the form fields should work:

class CommentCrudController extends AbstractCrudController
{
    public static function getEntityFqcn(): string
    {
        return Comment::class;
    }

    public function configureCrud(Crud $crud): Crud
    {
        return $crud
            ->setEntityLabelInSingular('Conference Comment')
            ->setEntityLabelInPlural('Conference Comments')
            ->setSearchFields(['author', 'text', 'email'])
            ->setDefaultSort(['createdAt' => 'DESC'])
        ;
    }

    public function configureFilters(Filters $filters): Filters
    {
            return $filters
                    ->add(EntityFilter::new('conference'))
            ;
    }

    public function configureFields(string $pageName): iterable
    {
        yield AssociationField::new('conference');
        yield TextField::new('author');
        yield EmailField::new('email');
        yield TextareaField::new('text')
                ->hideOnIndex()
            ;
        yield TextField::new('photoFilename')
                ->onlyOnIndex()
            ;

        $createdAt = DateTimeField::new('createdAt')->setFormTypeOptions([
                'html5' => true,
                'years' => range(date('Y'), ((int)date('Y')) + 5),
                'widget' => 'single_text',
            ]);
        if (Crud::PAGE_EDIT === $pageName) {
            yield $createdAt->setFormTypeOption('disabled', true);
        } else {
            yield $createdAt;
        }
    }
}

(Again: directly in the controller. Bleah).


That's it for that page. It didn't really have much to do with Symfony though, did it? It was all about this third-party admin app that I have no interest in whatsoever. This is no slight on EasyAdmin, it looks slick. But this is a Symfony book, not an EasyAdmin book.

I'm a bit annoyed at my experiences working through that page, so I'm leaving off for now.

There not much code that I would be willing to put my name next to in this effort, but I'll link to it anyhow: 1.8.

The next part is here: PHP / Symfony: working through "Symfony: The Fast Track", part 5: Twig stuff, and irritation.

Righto.

--
Adam

(*) [cough]

Sunday 2 April 2023

PHP / Symfony: working through "Symfony: The Fast Track", part 3: doing some ORM / DB config

G'day:

Today I'm gonna continue working through "Symfony: The Fast Track". This will be part three of this series, after the first two:

I also had a brief interlude yesterday whilst I messed around with the DB connnection driver the app was using.: Symfony / Doctrine / DBAL: convincing/configuring it to use a PrimaryReadReplicaConnection connection. This was not part of the Symfony book, just something I wanted to do.


Setting up a Database

Nothing much to see here. It's about setting up a PostgreSQL Docker container. Done already.

It also has some stuff that demonstrates another irk for me when it comes to frameworks that are getting a bit self important, and overreaching their job (which is to be a bloody web framework. Just that). Clock this:

Using the psql command-line utility might prove useful from time to time. But you need to remember the credentials and the database name. […]

[…] Thanks to these conventions, accessing the database via symfony run is much easier:

symfony run psql
Symfony: The Fast Track › Setting up a Database › Accessing the Local Database

Lads. You've "done a thing" that saves the person passing a -U and -d param to psql. But coupling it to the framework, and requiring the PostgreSQL client being installed in yer PHP environment. Just… why?

In contrast, here's me logging into the client in the PostgreSQL container:

psql -U user1primary -d db1primary

(Because I'm on the "server" I don't need a password, as auth is handed off to whatever mechanism I used to start the shell on the server, which is handy). So it was a good use of Symfony's time implementing the work to save those coupla dozen keystrokes. Ugh.

It also goes in to how to do some stuff with the production environment they introduced a few chapters back, but I'm not using that so I ignored it.


Describing the Data Structure

Bumpf

This starts by discussing config/packages/doctrine.yaml and how it works, and about DATABASE_URL. You can read about my opinion of DATABASE_URL in yesterday's article (Symfony / Doctrine / DBAL: convincing/configuring it to use a PrimaryReadReplicaConnection connection). Ah to be fair in a simple situation it would work well, but it does seem like a weird way to manage the configuration of the various params the DB needs to connect. Even if they are ultimately used via a URL. Bad coupling of disparate concepts, IMO.


Creating entities / repositories & property relationships

Next: running a wizard to create some boilerplate.The input values I am providing for this lot are:

city, string, 255, no;
year, string, 4, no;
isInternational, boolean, no.
root :/var/www# symfony console make:entity Conference

 created: src/Entity/Conference.php
 created: src/Repository/ConferenceRepository.php

 Entity generated! Now let's add some fields!
 You can always add more fields later manually or by re-running this command.

 New property name (press <return> to stop adding fields):
 > city

 Field type (enter ? to see all types) [string]:
 >

 Field length [255]:
 >

 Can this field be null in the database (nullable) (yes/no) [no]:
 >

 updated: src/Entity/Conference.php

 Add another property? Enter the property name (or press <return> to stop adding fields):
 > year

 Field type (enter ? to see all types) [string]:
 >

 Field length [255]:
 > 4

 Can this field be null in the database (nullable) (yes/no) [no]:
 >

 updated: src/Entity/Conference.php

 Add another property? Enter the property name (or press <return> to stop adding fields):
 > isInternational

 Field type (enter ? to see all types) [boolean]:
 >

 Can this field be null in the database (nullable) (yes/no) [no]:
 >

 updated: src/Entity/Conference.php

 Add another property? Enter the property name (or press <return> to stop adding fields):
 >


Success!
Next: When you're ready, create a migration with php bin/console make:migration root :/var/www#

As this has stated, it's created two files: src/Entity/Conference.php and src/Repository/ConferenceRepository.php. Let's have a look.

The Conference has a lot of repetition, so I'll elide a bunch of it, and only show the city property's bumpf:

#[ORM\Entity(repositoryClass: ConferenceRepository::class)]
class Conference
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\Column(length: 255)]
    private ?string $city = null;

    // ...

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

    public function getCity(): ?string
    {
        return $this->city;
    }

    public function setCity(string $city): self
    {
        $this->city = $city;

        return $this;
    }

    // ...

}

Firstly: ewwww… annotations (well: PHP attributes in this case, but it amounts to the same thing). I hate having storage-specific shite in my code. I'd much rather a discrete mapping file (YAML or something), and keep the storage considerations as the second-class citizen that it should be. However I suppose this is a necessary evil with ORM shite these days (can you tell I'm not completely sold on ORM as a concept? ;-)). There is currently a way of doing the mapping with YAML - Doctrine › ORM › YAML Mapping - but at the top of that page they say it's deprecated in favour of "one of the other mappings", which seems to mean XML or with actual PHP. In 2023 someone is advocating moving from YAML to XML. Cute. Anyway: for now I'll stick with the attributes. It's not the worst thing about this code.

It's funny that the docs page I'm following says "Note that the class itself is a plain PHP class with no signs of Doctrine". I mean… except all the Doctrine-specific attributes, you mean. Which are 24% of the statements in the class.

The worst thing is the getter and setter methods all properties have created by default. I'm not that happy with encouraging anti-patterns like this. See Tell-Don't-Ask and I also think it's a gateway drug for Law of Demeter violations. It's an enabler for bad OOP. I guess if their wizard thing just created the class and the properties (and the [muttermutter] ORM annotations), then it wouldn't seem like there was much of a point in having the wizard: it's not saving much effort.

I can see that I'm gonna need to bundle these "entities" away somewhere away from my actual application model, and just call on them to handle the storage side of things.

Oh yeah the ConferenceRepository class:

/**
 * @extends ServiceEntityRepository<Conference>
 *
 * @method Conference|null find($id, $lockMode = null, $lockVersion = null)
 * @method Conference|null findOneBy(array $criteria, array $orderBy = null)
 * @method Conference[]    findAll()
 * @method Conference[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class ConferenceRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Conference::class);
    }

    public function save(Conference $entity, bool $flush = false): void
    {
        $this->getEntityManager()->persist($entity);

        if ($flush) {
            $this->getEntityManager()->flush();
        }
    }

    public function remove(Conference $entity, bool $flush = false): void
    {
        $this->getEntityManager()->remove($entity);

        if ($flush) {
            $this->getEntityManager()->flush();
        }
    }

//    /**
//     * @return Conference[] Returns an array of Conference objects
//     */
//    public function findByExampleField($value): array
//    {
//        return $this->createQueryBuilder('c')
//            ->andWhere('c.exampleField = :val')
//            ->setParameter('val', $value)
//            ->orderBy('c.id', 'ASC')
//            ->setMaxResults(10)
//            ->getQuery()
//            ->getResult()
//        ;
//    }

//    public function findOneBySomeField($value): ?Conference
//    {
//        return $this->createQueryBuilder('c')
//            ->andWhere('c.exampleField = :val')
//            ->setParameter('val', $value)
//            ->getQuery()
//            ->getOneOrNullResult()
//        ;
//    }
}

I love how they are pretending PHP is Java and it has generics with stuff like ServiceEntityRepository<Conference>. Why do that? But then again I'm asking the same question about that entire comment block. What's the point? You have the code already defining all that lot.

It's not as bad (or is it?) as all the generated commented-out code at the bottom. WTaF?

Other than that: yeah cool… it's separated the definition of the entities from the storage thereof. I'll have to see how the code to save an object works. Currently all I can see tying these two together is the #[ORM\Entity(repositoryClass: ConferenceRepository::class)] attribute on the Conference class.

Next it gets me to create a Comment entity. I'll spare you the bulk of the detail, but this is the config:

author, string, 255, no;
text, text, no;
email, string, 255, no;
createdAt, datetime_immutable, no.

Actually there was one interesting thing here:

 Add another property? Enter the property name (or press <return> to stop adding fields):
 > createdAt

 Field type (enter ? to see all types) [datetime_immutable]:
 >
 

It seems to have clocked from the name createdAt that it should be a date time. That's quite cool.

Next I need to establish the relationship between the two entities, which is done by running symfony console make:entity Conference again:

root:/var/www# symfony console make:entity Conference

 Your entity already exists! So let's add some new fields!

 New property name (press <return> to stop adding fields):
 > comments

 Field type (enter ? to see all types) [string]:
 > OneToMany

 What class should this entity be related to?:
 > Comment

 A new property will also be added to the Comment class
 so that you can access and set the related Conference object from it.

 New field name inside Comment [conference]:
 >

 Is the Comment.conference property allowed to be null (nullable)? (yes/no) [yes]:
 > no

 Do you want to activate orphanRemoval on your relationship?
 A Comment is "orphaned" when it is removed from its related Conference.
 e.g. $conference->removeComment($comment)

 NOTE: If a Comment may *change* from one Conference to another, answer "no".

 Do you want to automatically delete orphaned 
 adamcameron\symfonythefasttrack\Entity\Comment objects (orphanRemoval)? (yes/no) [no]:
 > yes

 updated: src/Entity/Conference.php
 updated: src/Entity/Comment.php

 Add another property? Enter the property name (or press <return> to stop adding fields):
 >


Success!
Next: When you're ready, create a migration with php bin/console make:migration root:/var/www#

OK that's quite cool. It also does some auto-complete for me too:

That endeavour has added this to the Conference class:

#[ORM\OneToMany(mappedBy: 'conference', targetEntity: Comment::class, orphanRemoval: true)]
private Collection $comments;

And this stuff too:

/**
 * @return Collection<int, Comment>
 */
public function getComments(): Collection
{
    return $this->comments;
}

public function addComment(Comment $comment): self
{
    if (!$this->comments->contains($comment)) {
        $this->comments->add($comment);
        $comment->setConference($this);
    }

    return $this;
}

public function removeComment(Comment $comment): self
{
    if ($this->comments->removeElement($comment)) {
        // set the owning side to null (unless already changed)
        if ($comment->getConference() === $this) {
            $comment->setConference(null);
        }
    }

    return $this;
}

And to the Comment class:

#[ORM\ManyToOne(inversedBy: 'comments')]
#[ORM\JoinColumn(nullable: false)]
private ?Conference $conference = null;

Migrating the Database

OK I had wondered what this term I'd seen mentioned "migrations" was all about. It's how to apply the entity schema to the DB schema, by the sounds of it. Migrating the entity schema? Not sure that's the term I'd use: to me during a "migration" one moves from one place to another; but one ends up in the new place. It's applying the mapping, innit? Ah well: naming stuff is hard.

It'll be interesting to see if this works given I have not set up the DB exactly the way they wanted me to. Plus - peril - there is already data in it. Nothing ventured, nothing gained though right? Here goes:

root:/var/www# symfony console make:migration


Success!
Next: Review the new migration "migrations/Version20230402161944.php" Then: Run the migration with php bin/console doctrine:migrations:migrate See https://symfony.com/doc/current/bundles/DoctrineMigrationsBundle/index.html root:/var/www#

Oh right, I'm getting ahead of myself. It's just prepped the script, not run anything yet.

This is what it generated (as migrations/Version20230402161944.php):

/**
 * Auto-generated Migration: Please modify to your needs!
 */
final class Version20230402161944 extends AbstractMigration
{
    public function getDescription(): string
    {
        return '';
    }

    public function up(Schema $schema): void
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->addSql('CREATE SEQUENCE comment_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
        $this->addSql('CREATE SEQUENCE conference_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
        $this->addSql('
        	CREATE TABLE comment (
            	id INT NOT NULL,
                conference_id INT NOT NULL,
                author VARCHAR(255) NOT NULL,
                text TEXT NOT NULL,
                email VARCHAR(255) NOT NULL,
                created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
                photo_filename VARCHAR(255) DEFAULT NULL,
                PRIMARY KEY(id)
            )'
        );
        $this->addSql('CREATE INDEX IDX_9474526C604B8382 ON comment (conference_id)');
        $this->addSql('COMMENT ON COLUMN comment.created_at IS \'(DC2Type:datetime_immutable)\'');
        $this->addSql('
        	CREATE TABLE conference (
            	id INT NOT NULL,
                city VARCHAR(255) NOT NULL,
                year VARCHAR(4) NOT NULL,
                is_international BOOLEAN NOT NULL,
                PRIMARY KEY(id)
            )
        ');
        $this->addSql('
        	CREATE TABLE messenger_messages (
                id BIGSERIAL NOT NULL,
                body TEXT NOT NULL,
                headers TEXT NOT NULL,
                queue_name VARCHAR(190) NOT NULL,
                created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
                available_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
                delivered_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL,
                PRIMARY KEY(id)
            )
        ');
        $this->addSql('CREATE INDEX IDX_75EA56E0FB7336F0 ON messenger_messages (queue_name)');
        $this->addSql('CREATE INDEX IDX_75EA56E0E3BD61CE ON messenger_messages (available_at)');
        $this->addSql('CREATE INDEX IDX_75EA56E016BA31DB ON messenger_messages (delivered_at)');
        $this->addSql('CREATE OR REPLACE FUNCTION notify_messenger_messages() RETURNS TRIGGER AS $$
            BEGIN
                PERFORM pg_notify(\'messenger_messages\', NEW.queue_name::text);
                RETURN NEW;
            END;
        $$ LANGUAGE plpgsql;');
        $this->addSql('DROP TRIGGER IF EXISTS notify_trigger ON messenger_messages;');
        $this->addSql('
        	CREATE TRIGGER notify_trigger
            AFTER INSERT OR UPDATE
            ON messenger_messages
            FOR EACH ROW
            	EXECUTE PROCEDURE notify_messenger_messages();
        ');
        $this->addSql('
        	ALTER TABLE comment
            ADD CONSTRAINT FK_9474526C604B8382
            	FOREIGN KEY (conference_id)
                REFERENCES conference (id)
                NOT DEFERRABLE
                INITIALLY IMMEDIATE
        ');
        $this->addSql('DROP TABLE test');
    }

    public function down(Schema $schema): void
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->addSql('CREATE SCHEMA public');
        $this->addSql('DROP SEQUENCE comment_id_seq CASCADE');
        $this->addSql('DROP SEQUENCE conference_id_seq CASCADE');
        $this->addSql('CREATE TABLE test (id INT NOT NULL, value VARCHAR(50) NOT NULL)');
        $this->addSql('ALTER TABLE comment DROP CONSTRAINT FK_9474526C604B8382');
        $this->addSql('DROP TABLE comment');
        $this->addSql('DROP TABLE conference');
        $this->addSql('DROP TABLE messenger_messages');
    }
}

Seems legit.

It's "interesting" that it's creating a few triggers in there. That pg_notify thing looks interesting. I wonder what messenger_messages is?

OK now I'm sending all that to the DB:

root:/var/www# symfony console doctrine:migrations:migrate

 WARNING!
 You are about to execute a migration in database "db1primary"
 that could result in schema changes and data loss.
 Are you sure you wish to continue? (yes/no) [yes]:
 >

[notice] Migrating up to DoctrineMigrations\Version20230402161944
[notice] finished in 462.3ms, used 20M memory, 1 migrations executed, 15 sql queries


[OK] Successfully migrated to version : DoctrineMigrations\Version20230402161944
root:/var/www#

Blimey. So far: so good. Let's see what the DB has to say:

root:/# psql -U user1primary -d db1primary

db1primary=# \dt
                      List of relations
 Schema |            Name             | Type  |    Owner
--------+-----------------------------+-------+--------------
 public | comment                     | table | user1primary
 public | conference                  | table | user1primary
 public | doctrine_migration_versions | table | user1primary
 public | messenger_messages          | table | user1primary
(4 rows)
db1primary=# \d comment
                                      Table "public.comment"
     Column     |              Type              | Collation | Nullable |         Default
----------------+--------------------------------+-----------+----------+-------------------------
 id             | integer                        |           | not null |
 conference_id  | integer                        |           | not null |
 author         | character varying(255)         |           | not null |
 text           | text                           |           | not null |
 email          | character varying(255)         |           | not null |
 created_at     | timestamp(0) without time zone |           | not null |
 photo_filename | character varying(255)         |           |          | NULL::character varying
Indexes:
    "comment_pkey" PRIMARY KEY, btree (id)
    "idx_9474526c604b8382" btree (conference_id)
Foreign-key constraints:
    "fk_9474526c604b8382" FOREIGN KEY (conference_id) REFERENCES conference(id)
db1primary=# \d conference
                         Table "public.conference"
      Column      |          Type          | Collation | Nullable | Default
------------------+------------------------+-----------+----------+---------
 id               | integer                |           | not null |
 city             | character varying(255) |           | not null |
 year             | character varying(4)   |           | not null |
 is_international | boolean                |           | not null |
Indexes:
    "conference_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "comment" CONSTRAINT "fk_9474526c604b8382" FOREIGN KEY (conference_id) REFERENCES conference(id)

It all seems fine! Good work.


The last bit is about doing stuff on the production DB which doesn't apply to what I'm doing, so I'm ignoring that.

And I think I will leave that here. With one thing or another that took me a while and it's Sunday afternoon (OK: evening now) and I wanna do something else.

For all my whining about annotations and ORM and nomenclature, I'm finding this stuff pretty polished. There's no boats being pushed out regarding complexity here, but it's nailing the simple stuff.

There's none of me own code in here, but I've committed and tagged it as 1.7 anyhow.

Oh, and Part 4 is done: PHP / Symfony: working through "Symfony: The Fast Track", part 4: not really about Symfony, this one. Thought it's largely a waste of time. Maybe skip it.

Righto.

--
Adam

Saturday 1 April 2023

Symfony / Doctrine / DBAL: convincing/configuring it to use a PrimaryReadReplicaConnection connection

G'day:

A while back I documented how to create/configure a PrimaryReadReplicaConnection connection in PHP. PrimaryReadReplicaConnection is the replacement for MasterSlaveConnection, which has been retired due to socially-insensitive nomenclature. This is all in "PHP: PrimaryReadReplicaConnection - configuration / usage example".

Today's exercise is to get one to work in my Symfony project (adamcameron/SymfonyFastTrack).

Symfony's default DB connectivity is done via the DATABASE_URL environment variable which must be set (docs: Configuring Doctrine ORM). This is fine for simple situations, even though I personally think it's a daft way of handling connnection parameters: it's a bit "type couply" to using a URL to connect to a DB, which is not the only way of doing it. But falls flat pretty quickly once not in a simple situation. The problem is that the DATABASE_URL allows only for a single connection. It's gonna be pretty common to be using primary / replicas I think. I guess perhaps the Symfony thinking(?) is that this is better handled on a DB load balancer than in the app. However: I don't have one of those, and i have also seen enterprise-scale PHP-driven operations that also simply use a PrimaryReadReplicaConnection for this. I have a PrimaryReadReplicaConnection driver. I need to make it work. This is not well / clearly / at all documented.

Firstly: there is no escaping it. One needs to have DATABASE_URL set. And it needs to be valid. This is even if you remove references to it in config: the connection won't configure (and the application won't work) unless it exists. The Symfony docs say to use override_url to prevent this behaviour:

When specifying a url parameter, any information extracted from that URL will override explicitly set parameters unless override_url is set to true. An example database URL would be mysql://snoopy:redbaron@localhost/baseball, and any explicitly set driver, user, password and dbname parameter would be overridden by this URL. See the Doctrine DBAL documentation for more information.
Doctrine DBAL Configuration

However that link to the Doctrine docs is obsolete, and that setting has been deprecated since 2.4 (we're on 2.9 now):

UPGRADE FROM 2.3 to 2.4
=======================

Configuration
--------

 * Setting the `host`, `port`, `user`, `password`, `path`, `dbname`, `unix_socket`
   or `memory` configuration options while the `url` one is set has been deprecated.
 * The `override_url` configuration option has been deprecated.
DoctrineBundle/UPGRADE-2.4.md

OK. Great. Thanks for that. I must provide a DATABASE_URL. But any explicitly-set overrides I set in the actual connection config are ignored. And the functionality that used to permit me to actively say "FFS will you just do what yer told" (ie: override_url) has been deprecated. And the URL approach doesn't actually support the options I want to use, so it's completely useless to me. This seems pretty mickey mouse to me, from where I'm standing. But all right then; I'll play yer silly game.

After some trial end error (because there's no documentation that I can find), I came up with this (in config/packages/doctrine.yaml):

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                wrapper_class: Doctrine\DBAL\Connections\PrimaryReadReplicaConnection
                dbname: '%env(resolve:POSTGRES_PRIMARY_DB)%'
                host: '%env(resolve:POSTGRES_PRIMARY_HOST)%'
                port: '%env(resolve:POSTGRES_PRIMARY_PORT)%'
                user: '%env(resolve:POSTGRES_PRIMARY_USER)%'
                password: '%env(resolve:POSTGRES_PRIMARY_PASSWORD)%'
                driver: pdo_pgsql
                server_version: 15
                charset: utf8
                replicas:
                    replica1:
                        dbname: '%env(resolve:POSTGRES_REPLICA_DB)%'
                        host: '%env(resolve:POSTGRES_REPLICA_HOST)%'
                        port: '%env(resolve:POSTGRES_REPLICA_PORT)%'
                        user: '%env(resolve:POSTGRES_REPLICA_USER)%'
                        password: '%env(resolve:POSTGRES_REPLICA_PASSWORD)%'
                        charset: utf8


I have used the primary values in DATABASE_URL (in docker/php/envVars.public):

# …

DATABASE_URL="postgresql://${POSTGRES_PRIMARY_USER}:${POSTGRES_PRIMARY_PASSWORD}@${POSTGRES_PRIMARY_HOST}:${POSTGRES_PRIMARY_PORT}/${POSTGRES_PRIMARY_DB}?serverVersion=15&charset=utf8"

To test that this actually works, and it doesn't just send everything to the primary, I have set up two completely separate DBs, with different credentials (and host, port, and even database name). These are separate PostgreSQL containers. I'll push the config to Github, but I won't repeat it here as it's really just a duplication of what I already had in this codebase. But here's links to the relevant bits of various files:

It's important to note that in this experimentation the two DBs are completely separate, and there's no replication going on. I'm just testing the connection config is correctly switching between primary and replica, and it's easier to do when the databases have different data in them.

# primary.test
"id"    "value"
101    "Test row 1"
102    "Test row 2"
104    "PRIMARY"
105    "TEST_VALUE"
106    "TEST_VALUE"

(You can see I've already been running some tests there)

# replica.test
101    "Test row 1"
102    "Test row 2"
103    "REPLICA"
/** @testdox Writing to primary definitely does not impact the replica */
public function testWritingToPrimaryDoesNotImpactReplica()
{
    $sqlForCount = "SELECT COUNT(1) AS count FROM test";

    $primaryConnection = $this->getPrimaryConnection();
    $replicaConnection = $this->getReplicaConnection();

    $initialPrimaryCount = $primaryConnection->executeQuery($sqlForCount)->fetchOne();
    $initialReplicaCount = $replicaConnection->executeQuery($sqlForCount)->fetchOne();
    $this->assertNotEquals(
        $initialPrimaryCount,
        $initialReplicaCount,
        "Test aborted as the test requires the DBs to NOT be in sync (and they are)"
    );

    $defaultConnection = $this->getDefaultConnection();
    $this->assertFalse($defaultConnection->isConnectedToPrimary(), "Connection did not start on replica");

    $initialDefaultCount = $defaultConnection->executeQuery($sqlForCount)->fetchOne();
    $this->assertEquals($initialDefaultCount, $initialReplicaCount, "Row count from default should match replica");

    $defaultConnection->executeStatement("INSERT INTO test (value) VALUES (?)", ["TEST_VALUE"]);
    $this->assertTrue(
        $defaultConnection->isConnectedToPrimary(),
        "Connection did not switch to primary after INSERT"
    );

    $countFromDefault = $defaultConnection->executeQuery($sqlForCount)->fetchOne();
    $countFromPrimary = $primaryConnection->executeQuery($sqlForCount)->fetchOne();
    $countFromReplica = $replicaConnection->executeQuery($sqlForCount)->fetchOne();

    $this->assertEquals($countFromDefault, $countFromPrimary, "Row count from default should match primary");
    $this->assertEquals($initialReplicaCount, $countFromReplica, "Row count from replica should not have changed");
}

Here:

  • I'm creating three connections, one each that directly queries the primary and replica DBs respectively, and one that is the app's default connection (which is the PrimaryReadReplicaConnection one).
  • I get row counts from each, making sure they start off differently (otherwise it'll be harder to see the difference, further down).
  • I then check the row count from the default connection matches the replica (it should start using the replica connection.
  • Then I insert a row using the default connection. This has a dual effect:
  • The default connection should now be pointing to the primary database.
  • So a row count from that connection should match the primary now.
  • And the replica count should be unchanged.

And the helper functions:

private function getPrimaryConnection(): Connection
{
    return DriverManager::getConnection([
        "dbname" => getenv("POSTGRES_PRIMARY_DB"),
        "user" => getenv("POSTGRES_PRIMARY_USER"),
        "password" => getenv("POSTGRES_PRIMARY_PASSWORD"),
        "host" => getenv("POSTGRES_PRIMARY_HOST"),
        "port" => getenv("POSTGRES_PRIMARY_PORT"),
        "driver" => "pdo_pgsql"
    ]);
}

private function getReplicaConnection(): Connection
{
    return DriverManager::getConnection([
        "dbname" => getenv("POSTGRES_REPLICA_DB"),
        "user" => getenv("POSTGRES_REPLICA_USER"),
        "password" => getenv("POSTGRES_REPLICA_PASSWORD"),
        "host" => getenv("POSTGRES_REPLICA_HOST"),
        "port" => getenv("POSTGRES_REPLICA_PORT"),
        "driver" => "pdo_pgsql"
    ]);
}

public function getDefaultConnection(): PrimaryReadReplicaConnection
{
    $kernel = new Kernel("test", true);
    $kernel->boot();

    $container = $kernel->getContainer();

    return $container->get("doctrine.dbal.default_connection");
}

And that all worked, so that's good.

I'm gonna tag all this in GitHub as 1.6, but I'm gonna roll-back the two different DBs and just point the replica to the same DB as the primary from now on. I want the PrimaryReadReplicaConnection connection in place in this project, but I don't wanna horse around keeping throw-away DBs actually in sync. That version is tagged as 1.6.1.

And now tomorrow I can get on with what I actually wanted to be writing about today :-|

Righto.

--
Adam