ZF-4380: Zend_Db_Table upon view with insert rule (PostgreSQL) fails to obtain sequence id after the insert

Description

This is a bit specific issue.

We build CMS over the views in PostgreSQL, where the views are only tables used from the CMS PHP code (for many reasons.)

We have this view:


CREATE OR REPLACE VIEW test_view AS 
 SELECT .....

CREATE OR REPLACE RULE "_INSERT" AS
ON INSERT TO test_view DO INSTEAD (
    SELECT store_item(...) AS null_variable;
    SELECT store_item_version(....) AS null_variable; /* advances sequence_version */
    INSERT INTO moduletable_test (version_id, ...) VALUES (currval('sequence_version'::regclass)::integer, ...);
);

(note that PERFORM stmt does not work when used in rules)

With that, we have this class:


class CMS_Module_Test extends Zend_Db_Table_Abstract {
    protected $_name = 'test_view';
    protected $_primary = 'version_id';
    protected $_rowClass = 'CMS_Item';
    protected $_sequence = 'sequence_version';

    (...)
}

(note that I have simplified the current abstraction into one class)

When we try to execute createRow and save() methods, the save fails on refreshing the row from database with this exception: bq. Cannot refresh row as parent is missing

This is because of the order how the ZF saves new item: * Create empty row instance * fill-up and call save() * Obtain primary key value from sequence * Save the row with sending the sequence value * Refresh the row from database

Since the database is designed to be a quite independent, we generate primary keys from sequences in database, not in the php code and therefore, it throws exception.

h3. Workaround

There is simple workaround present in current version of CMS:


class CMS_Module_Test extends Zend_Db_Table_Abstract {
    (...)
    protected $_sequence = false; // manual override
    (...)
}

class CMS_Item extends Zend_Db_Table_Row_Abstract {
    protected $_tableClass = 'CMS_Module';

    protected function _postInsert() {
        parent::_postInsert();

        // We get from the database the version_id
        $this->version_id = $this->getTable()
                                 ->getAdapter()
                                 ->fetchOne("SELECT CURRVAL('sequence_version')");
    }
}

However, that can prevent the CMS_Item class being used for same purpose on tables (eg. project-specific ones), since it gets the version_id after the insert. In case that there would be more system-like logic in the postInsert function, extending and overriding would not come to result, since the system-specific logic have to be rewritten and that destroys whole OOP concept. This could be fixed by adding one class in between (and you can imagine the results of this.)

Please note that when we use: bq. $sequence = true; ZF generates the sequence name from the table name. That is wrong since we use _one sequence for many views

h3. Proposed solution:


class CMS_Module extends Zend_Db_Table_Abstract {
    protected $_sequence = 'sequence_version';
    protected $_sequenceLoading = Zend_Db_Table_Abstract::SEQ_AFTER_INSERT; /* _BEFORE_ present and default --> backward compatible */
}

Or defaultly loading the sequence after the insert (which I believe was already discussed)

h3.-Note that problem that cause this issue has already been reported in ZF-3454- (wrong, that task is similar, but none of proposed solutions would fix ZF-3454)

Comments

Bulk change of all issues last updated before 1st January 2010 as "Won't Fix".

Feel free to re-open and provide a patch if you want to fix this issue.