Issues

ZF2-203: Zend\Db\Metadata\Metadata doesn't work for Postgresql databases

Description

If you do {{new Zend\Db\Metadata\Metadata($adapter)}} and pass a PDO Adapter connected to a Postgresql database, you get an error:

{{PHP Fatal error: Uncaught exception 'Exception' with message 'cannot create source from adapter' in vendor/ZendFramework/library/Zend/Db/Metadata/Metadata.php:74}}

This is because {{createSourceFromAdapter}} uses {{$adapter->getPlatform()->getName()}}, which for Postgresql is SQL92 and this is not an option in the switch statement. Postgresql has an information schema that provides the correct information (as far as I can tell), so you could just update the switch to use {{new Source\InformationSchemaMetadata($adapter)}} for Postgresql as well as SQLServer.

Unfortunately, this doesn't work because the queries against the information schema that are run by {{Zend\Db\Metadata\Source\InformationSchemaMetadata}} all use {{$platform->quoteIdentifier}} around the table names, column names and the schema name. This fails for Postgresql because the actual names are all lowercase, whereas they are specified in uppercase in the queries. Obviously the quoted uppercase objects don't match any objects in the database and the query fails.

There are a number of possible solutions: * Remove all the quoting from the queries. Postgresql will happily find the correct lowercase object from the unquoted uppercase name. Assuming that SQLServer (the only other database using {{Zend\Db\Metadata\Source\InformationSchemaMetadata}}) will also work like this, this seems like the best solution. This is what I have done in my fork of zf2 and I can push a branch back to GitHub and create a pull request if you want. However, I can't test against SQLServer, which is why I haven't assumed this is the preferred solution. * Leave the quoting but make all the schema, table and column names lowercase. I suspect that this would break SQLServer, although I haven't tried. * Refactor {{Zend\Db\Metadata\Source\InformationSchemaMetadata}} to allow (slightly) different queries for SQLServer and Postgresql.

If you like option 1 and I push my branch to GitHub, can someone test it against SQLServer?

Comments

After further investigation, this won't be that simple! After you remove the identifier quoting, the queries work but the metadata isn't populated because the class expects the result set to contain the uppercase column name, e.g.


if (!isset($columnByTableInfos$row['TABLE_NAME']])) {
                $columnByTableInfos[$row['TABLE_NAME']] = array();
            }

Therefore, we either need a separate Source\Pgsql or we need to allow the case folding to be configured.

In any case, it would be useful if {{Metadata\Metadata}} allowed a source to be injected (as implied by the [RFC so that we could provide a customized one if necessary (I've got my own schema metadata tables with more details than information_schema, e.g. prompt, is_displayed, display_type, etc.

The problem continues in the SELECT because Zend/Db uses quotes to define it:

LINE 1: SELECT "zf2tutorial.album".* FROM "zf2tutorial.album" ^ I tested the line in postgres and the error is for the quotes.

Fixed in beta5.