ZF-1269: Error extending Zend_Db_Table for PostGreSQL database views

Issue Type: Improvement Created: 2007-04-12T16:24:35.000+0000 Last Updated: 2009-08-07T07:13:46.000+0000 Status: Resolved Fix version(s): - 1.5.0 (17/Mar/08)

Reporter: Joshua L Ross (joshualross) Assignee: Darby Felton (darby) Tags: - Zend_Db

Related issues: Attachments:


I attempted to model a PostGreSQL database view by extending Zend_Db_Table and it is failing. I am able to instantiate the object but a call to Zend_Db_Table_Abstract::fetchAll() fails with the following error and stack trace:

Fatal error: Uncaught exception 'Zend_Db_Adapter_Exception' with message 'SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "FROM" at character 10' in /data/www/vhosts/mysite/v2/library/Zend/Db/Adapter/Pdo/Abstract.php:156 Stack trace:

0 /data/www/vhosts/mysite/v2/library/Zend/Db/Table/Abstract.php(761): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select))

1 /data/www/vhosts/mysite/v2/library/Zend/Db/Table/Abstract.php(658): Zend_Db_Table_Abstract->_fetch(NULL, NULL, NULL, NULL)

2 /data/www/vhosts/mysite/v2/application/fileprocessing/controllers/MyController.php(117): Zend_Db_Table_Abstract->fetchAll()

3 /data/www/vhosts/mysite/v2/library/Zend/Controller/Action.php(488): Fileprocessing_MyController->processAction()

4 /data/www/vhosts/mysite/v2/library/Zend/Controller/Dispatcher/Standard.php(214): Zend_Controller_Action->dispatch('processAction')

5 /da in /data/www/vhosts/mysite/v2/library/Zend/Db/Adapter/Pdo/Abstract.php on line 156

In the newsgroup Bill mentioned: {quote} I haven't tested the PostgreSQL adapter for Zend_Db with database views yet, so it's likely that some adjustment is needed in the query against the system catalog tables to support views in the describeTable() method. {quote}


Posted by Darby Felton (darby) on 2007-12-17T16:43:26.000+0000

I committed a unit test In SVN r7178 to test the use of Zend_Db_Table_Abstract::fetchAll() on a view. The table/view class Zend_Db_Table_TableViewBugsFixed (in trunk/tests/Zend/Db/Table/TableViewBugsFixed.php) represents a view created by the test setup. I noticed that I could only get this to work using a view name having all lowercase characters and no underscores. Also, defining the primary key in the class extending Zend_Db_Table_Abstract avoids problems with automatic primary key resolution via the results of describeTable().

I heartily recommend that more unit tests using views are developed for these database components.

Can you please provide more information about the conditions under which you experience this issue? I could not quickly reproduce the exact error message you received. Thanks. :)

Posted by Darby Felton (darby) on 2007-12-20T10:58:48.000+0000

Reducing priority to Should Have, pending additional issue reproduction information from reporter.

Posted by Joshua L Ross (joshualross) on 2007-12-20T13:01:15.000+0000

Unfortunately I no longer have access to this... I have moved on from that company. So my memory of the issue is going to be a bit muddle but in essence we had created a view in the database and were trying to use zend_db_table to create a model for it. So in the model _setup method we set the name property to the name of the view. It seemed to instantiate just fine but we could not perform any fetch operation. Hope that helps.

Posted by Darby Felton (darby) on 2008-01-29T10:25:50.000+0000

Resolving as "cannot reproduce" pending further information receipt.

Posted by Bruno Friedmann (brunofriedmann) on 2009-08-07T07:13:44.000+0000

I've just been hit by this bug with version from svn today 08-07-2009.

The test case would be this ( I express it first in human language )

Create a view called view_test_zf with two simple columns issue form a select as view_id, t.label as view_label from test

Now if you create a class extending Zend_Db_Table_Abstract like this

class Default_Model_DbView_Test extends Zend_Db_Table_Abstract { /** * The default table name */ protected $_name = 'view_test_zf'; protected $_id = 'view_id';

} This work well with real tables. and should be suffisant (db adapter used is pdo_pgsql with a traditionnal initialisation by Zend_Application )

It produce the following error

Exception information:

Message: A table must have a primary key, but none was found Stack trace:

0 /home/ioda_data/web-include/ZF.svn/library/Zend/Db/Table/Abstract.php(964): Zend_Db_Table_Abstract->_setupPrimaryKey()

1 /home/ioda_data/web-include/ZF.svn/library/Zend/Db/Table/Select.php(100): Zend_Db_Table_Abstract->info()

2 /home/ioda_data/web-include/ZF.svn/library/Zend/Db/Table/Select.php(78): Zend_Db_Table_Select->setTable(Object(Default_Model_DbView_Communes))

3 /home/ioda_data/web-include/ZF.svn/library/Zend/Db/Table/Abstract.php(1000): Zend_Db_Table_Select->__construct(Object(Default_Model_DbView_Communes))

4 /home/ioda_data/web-include/ZF.svn/library/Zend/Db/Table/Abstract.php(1286): Zend_Db_Table_Abstract->select()

5 /home/bruno/workspace/pdc-ioda/library/Sigeom/Pdc/Commune/Adapter/Default.php(10): Zend_Db_Table_Abstract->fetchAll()

6 /home/bruno/workspace/pdc-ioda/library/Sigeom/Pdc/Commune/Service.php(63): Sigeom_Pdc_Commune_Adapter_Default->listCommunes()

7 /home/bruno/workspace/pdc-ioda/application/controllers/IndexController.php(66): Sigeom_Pdc_Commune_Service->listCommunes()

8 /home/ioda_data/web-include/ZF.svn/library/Zend/Controller/Action.php(513): IndexController->indexAction()

9 /home/ioda_data/web-include/ZF.svn/library/Zend/Controller/Dispatcher/Standard.php(289): Zend_Controller_Action->dispatch('indexAction')

10 /home/ioda_data/web-include/ZF.svn/library/Zend/Controller/Front.php(946): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))

11 /home/ioda_data/web-include/ZF.svn/library/Zend/Application/Bootstrap/Bootstrap.php(77): Zend_Controller_Front->dispatch()

12 /home/bruno/workspace/pdc-ioda/application/Bootstrap.php(69): Zend_Application_Bootstrap_Bootstrap->run()

13 /home/bruno/workspace/pdc-ioda/application/Bootstrap.php(63): Bootstrap->runHttp()

14 /home/ioda_data/web-include/ZF.svn/library/Zend/Application.php(335): Bootstrap->run()

15 /home/bruno/workspace/pdc-ioda/contexts/commons.php(54): Zend_Application->run()

16 /home/bruno/workspace/pdc-ioda/contexts/http.php(8): require_once('/home/bruno/wor...')

17 /home/bruno/workspace/pdc-ioda/public/index.php(3): require_once('/home/bruno/wor...')

18 {main}

If you add this in the class

/** * @FIXME : Zend trouble with postgresql view */
protected function _setup() { $this->_name = 'view_test_zf'; $this->_primary = 'view_id'; parent::_setup(); }

public function __construct(){
    //  continue with automated setup

It simply work !

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.