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

Issue Type: Bug Created: 2012-03-07T21:11:20.000+0000 Last Updated: 2012-07-17T16:32:27.000+0000 Status: Resolved Fix version(s): Reporter: Roger Hunwicks (rhunwicks) Assignee: Ralph Schindler (ralph) Tags: - Zend\Db

Related issues: Attachments:


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?


Posted by Roger Hunwicks (rhunwicks) on 2012-03-08T08:58:25.000+0000

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.

<pre class="highlight">
if (!isset($columnByTableInfos<a href="">$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.

Posted by Rodolfo Bibolotti (rockdolfolk) on 2012-07-01T03:57:21.000+0000

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.

Posted by Ralph Schindler (ralph) on 2012-07-11T14:52:09.000+0000

Fixed in beta5.

Have you found an issue?

See the Overview section for more details.


© 2006-2018 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.