Postgresql unit test fails "unknown field"

silverstripe-4
module
Tags: #<Tag:0x00007f10c73d4148> #<Tag:0x00007f10c721ba90>

#1

SilverStripe: 4.*

Hi All, I have been working on a module and trying to get it running in Travis. The builds are all running OK, except for Postgresql.

It looks like the custom TestOnly DataObject I am using is not being setup correctly. Does anyone have any idea why though?

The most recent test results are: Travis CI - Test and Deploy Your Code with Confidence

The error I am getting is:

SilverStripe\ORM\Connect\DatabaseException: Couldn't run query:
SELECT DISTINCT "SlightlyBetterBulkLoader_Player"."ClassName", "SlightlyBetterBulkLoader_Player"."LastEdited", "SlightlyBetterBulkLoader_Player"."Created", "SlightlyBetterBulkLoader_Player"."FirstName", "SlightlyBetterBulkLoader_Player"."Biography", "SlightlyBetterBulkLoader_Player"."Birthday", "SlightlyBetterBulkLoader_Player"."ExternalIdentifier", "SlightlyBetterBulkLoader_Player"."IsRegistered", "SlightlyBetterBulkLoader_Player"."Status", "SlightlyBetterBulkLoader_Player"."ID", 
			CASE WHEN "SlightlyBetterBulkLoader_Player"."ClassName" IS NOT NULL THEN "SlightlyBetterBulkLoader_Player"."ClassName"
			ELSE  E'ilateral\\SilverStripe\\SlightlyBetterBulkLoader\\Tests\\Data\\Player' END AS "RecordClassName"
FROM "SlightlyBetterBulkLoader_Player"
WHERE (FirstName = $1)
LIMIT 1
ERROR:  column "firstname" does not exist
LINE 5:  WHERE (FirstName = $1)

Which appears to be caused by the following:

        $obj = DataObject::get_one(
            Player::class,
            [ 'FirstName' => 'John' ]
        );

My Player object looks like:

<?php

namespace ilateral\SilverStripe\SlightlyBetterBulkLoader\Tests\Data;

use SilverStripe\Dev\TestOnly;
use SilverStripe\ORM\DataObject;

class Player extends DataObject implements TestOnly
{
    private static $table_name = "SlightlyBetterBulkLoader_Player";

    private static $db = [
        'FirstName' => 'Varchar(255)',
        'Biography' => 'HTMLText',
        'Birthday' => 'Date',
        'ExternalIdentifier' => 'Varchar(255)',
        'IsRegistered' => 'Boolean',
        'Status' => 'Varchar'
    ];

    private static $field_labels = [
        'FirstName' => 'Player Name'
    ];

    private static $required_fields = [
        'FirstName',
        'Birthday'
    ];
}

#2

The problem is probably the quoting: PostgreSQL is case-insensitive without quotes and case-sensitive with quotes. A quick test to see if this is the case woul be to try:

$obj = DataObject::get_one(
    Player::class,
    [ '"FirstName"' => 'John' ]
);

#3

This fixes the problem, cheers. I think it is worth mentioning (if anyone finds this) that some people on slack also recommended using Player::get()->filter() instead (which apparently escapes column names correctly).


#4

In this case I think this should be considered a bug, or at least a missing documentation. I opened an issue referring to this forum thread.