Issues

ZF-1269: Error extending Zend_Db_Table for PostGreSQL database views

Description

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}

Comments

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. :)

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

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.

Resolving as "cannot reproduce" pending further information receipt.

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 t.id 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
    $this->_setup();
}

It simply work !