Problem when trying to use DBALEventStore with PostgreSQL

[broadway]

Problem when trying to use DBALEventStore with PostgreSQL

[mbadolato]

BTW, slightly altering prepareLoadStatement() to

     private function prepareLoadStatement()
     {
         if (null === $this->loadStatement) {
-            $query = 'SELECT uuid, playhead, metadata, payload, recordedOn
+            $query = 'SELECT uuid, playhead, metadata, payload, recordedOn AS "recordedOn"
                 FROM ' . $this->tableName . '
                 WHERE uuid = :uuid
                 ORDER BY playhead ASC';

is a simple fix that would alleviate config issues. I'd prefer to know what the root cause and solution is, but if Quandidate would like a PR that tweaks the SQL to fix it, I can submit one.

[sstok]

PostgreSQL lowercases all columns and table names unless you use double quotes.
http://stackoverflow.com/questions/13409094/why-does-postgresql-default-everything-to-lower-case

This why its recommended to always use underscore for separating words in SQL.

[mbadolato]

Oh jeeeeez. See now, I spent a ton of time screwing around with DBAL and didn't even think about it being Postgres doing that. This is why I shouldn't work on things after 10 hour days. Thx for the link!

[mbadolato]

@sstok Easily fixed in the SELECT statement. Any idea how to force the quoting in the addColumn(...) commands when creating the table? Using double quotes or forcing escaped double quotes addColumn("\"recordedOn\""...) doesn't seem to work.

I'll try researching when I get home tonight; was just trying to see if a quick fix worked.

[mbadolato]

Well, I can finally get it running under PostgreSQL, but the quoting changes that have to be made, subsequently break it in MySQL. Le Sigh. So, looks like I may need to break out a PostgresDBALEventStore class that overrides the functions and quoting needed.

[mbadolato]

PR #53 submitted for review

[sstok]

You mean #53 ;)

[sstok]

From what I know is that Doctrine has its own quoting strategy, so using something like "id" will actually include the double-quotes in the name ""id"" (SQL based quote escaping).

But I'm not on this.

[mbadolato]

Heh whoops 53. Edited.

I couldn't get the quoting strategy to a place where it would work in both PgSQL and MySQL and I had no more time or brainpower to spend on messing around with it, so I just subclassed a PG version of the class with the proper quoting for that database. Not the most elegant of solutions and would prefer a better one, I just don't feel like spending any more time on the issue right now.

[mbadolato]

The other alternative is to not have the PGSQL version of the class and just simply change the SELECT to have as "recordedOn" as indicated a couple of days ago. I haven't double checked that solution across a few different platforms yet, but should be unobtrusive.

[sstok]

In know MySQL uses backtics for special keywords and threads"the same as'` when it comes to strings. I haven't worked with MySQL for a long time so I can be wrong though.

[stof]

IMO, the best alternative would be to change column names to use underscores rather than camelCase. Given that 1.0 is not released yet, such BC break could still be done.

Dealing with column names case sensitivity will be a huge pain in the ass: http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html#.VBqV4Ba79QJ

[mbadolato]

@stof I agree. I was just this second about to close my previous PR and push a new one that simply adds a AS "recordedOn" to the select statement, but if the qandidate peeps are ok with just changing the column name to recorded_on, I'll further change it

[mbadolato]

@asm89 @othillo @wjzijderveld Opinion on AS "recordedOn" vs recorded_on?

[wjzijderveld]

@mbadolato I would prefer recorded_on as well if that is the correct fix.

[mbadolato]

@wjzijderveld PR 55 submitted with the change