This is a little strange, and it's likely an issue with how I'm using Doctrine DBAL. But, other users of this library may run into this issue, and it would be helpful if there's an answer here.
Everything works just fine if I use pdo_sqlite. The problem comes in when using the pdo_pgsql driver. There's apparently some column name case-sensitivity issues happening and I cannot figure out the way around it. Granted, I'm new to working directly with DBAL, but still... nothing I've tried helps.
I have a test class with the following set up:
$parameters = [
'driver' => 'pdo_pgsql',
'user' => 'test',
'password' => 'test',
'database' => 'test',
'host' => 'localhost',
'port' => 5433,
];
$connection = DriverManager::getConnection($parameters);
$schemaManager = $connection->getSchemaManager();
$schema = $schemaManager->createSchema();
$eventStore = new DBALEventStore($connection, new SimpleInterfaceSerializer(), new SimpleInterfaceSerializer(), 'events');
if ($table = $eventStore->configureSchema($schema)) {
$schemaManager->createTable($table);
}
Yes, 5433 is the correct port on this machine (not the default 5432) and all of the connection info is correct. I can connect to the database.
Here's the problem:
When createTable($table) is called, the column names are getting normalized and thus lowercase. This is an issue with the recordedOn field. The column name is getting created as 'recordedon'. This means that the deserializeEvent
is throwing an error on
DateTime::fromString($row['recordedOn'])
because it thinks the recordedOn doesn't exist (which it doesn't the database has it as 'recordedon').
The inserts into the table work just fine. The data is getting retrieved just fine. It just crashes in the deserializeEvent method.
If I manually change the column name in the database to recordedOn, then the insert commands in the event store no longer work.
Broadway\EventStore\DBALEventStoreException:
Caused by
Doctrine\DBAL\DBALException: An exception occurred while executing 'INSERT INTO events (uuid, playhead, metadata, payload, recordedOn, type) VALUES (?, ?, ?, ?, ?, ?)' with params ["07ebf76f-7ddf-4e57-969d-e271c560ebdd", 0, "{\"class\":\"Broadway\\\\Domain\\\\Metadata\",\"payload\":[]}", "{\"class\":\"Cybernox\\\\Theme\\\\Events\\\\ThemeCreatedEvent\",\"payload\":{\"themeId\":\"07ebf76f-7ddf-4e57-969d-e271c560ebdd\"}}", "2014-09-16T07:07:02.626363+00:00", "Cybernox.Theme.Events.ThemeCreatedEvent"]:
SQLSTATE[42703]: Undefined column: 7 ERROR: column "recordedon" of relation "events" does not exist
LINE 1: ...T INTO events (uuid, playhead, metadata, payload, recordedOn...
I thought it might be simply a case of needing to configure the connection to use the PDO::CASE_NATURAL values, so I added a driverOptions to the connection. Still nothing (trying CASE_LOWER and CASE_UPPER didn't work either. In fact, UPPER caused a bigger crash where tables and column names were empty)
$parameters = [
'driver' => 'pdo_pgsql',
'user' => 'test',
'password' => 'test',
'database' => 'test',
'host' => 'localhost',
'port' => 5433,
'driverOptions' => [\PDO::ATTR_CASE => \PDO::CASE_NATURAL],
];
The only way I could get a success (and it's totally wrong, I was just trying to see if I could get SOMETHING to work), was to add
$row['recordedOn'] = $row['recordedon'];
to DBALEventStore::deserializeEvent()
just to keep the mismatch from happening.
Anyone know of the proper voodoo to use with the dbal connection to keep the column names as-is instead of getting lowercased?