Primary keys (Sequence) not setting after save
Closed this issue · 10 comments
My table 'users' have following structure (postgree):
CREATE TABLE users
(
id serial NOT NULL,
login character varying(255),
CONSTRAINT users_pkey PRIMARY KEY (id)
) WITH (
OIDS=FALSE
);
I am write this code
$row = new RowGateway('id', 'users', $this->getAdapter());
$row->login = 'new';
$row->save();
And I have an exeption: While processing primary key data, a known key id was not found in the data array
I try use Pdo_Pgsql and Pgsql driver
Please fix it
Originally posted by @Sohorev at zendframework/zend-db#191
to solve the problem I wrote a custom class RowGateway with method save.
It is characterized by obtaining the value primaryKeyValue:
$primaryKeyValue = $this->sql->getAdapter()->getDriver()->getLastGeneratedValue($this->tableGateway->getSequenceName());
namespace QP\Db\RowGateway;
class RowGateway extends \Zend\Db\RowGateway\RowGateway
{
/**
*
* @var \QP\Db\TableGateway\Table
*/
protected $tableGateway;
public function __construct($primaryKeyColumn, $tableGateway)
{
$this->tableGateway = $tableGateway;
parent::__construct($primaryKeyColumn, $tableGateway->table, $tableGateway->adapter);
}
public function save()
{
$this->initialize();
if ($this->rowExistsInDatabase()) {
// UPDATE
$data = $this->data;
$where = [];
$isPkModified = false;
// primary key is always an array even if its a single column
foreach ($this->primaryKeyColumn as $pkColumn) {
$where[$pkColumn] = $this->primaryKeyData[$pkColumn];
if ($data[$pkColumn] == $this->primaryKeyData[$pkColumn]) {
unset($data[$pkColumn]);
} else {
$isPkModified = true;
}
}
$statement = $this->sql->prepareStatementForSqlObject($this->sql->update()->set($data)->where($where));
$result = $statement->execute();
$rowsAffected = $result->getAffectedRows();
unset($statement, $result); // cleanup
// If one or more primary keys are modified, we update the where clause
if ($isPkModified) {
foreach ($this->primaryKeyColumn as $pkColumn) {
if ($data[$pkColumn] != $this->primaryKeyData[$pkColumn]) {
$where[$pkColumn] = $data[$pkColumn];
}
}
}
} else {
// INSERT
$insert = $this->sql->insert();
$insert->values($this->data);
$statement = $this->sql->prepareStatementForSqlObject($insert);
$result = $statement->execute();
$primaryKeyValue = $this->sql->getAdapter()->getDriver()->getLastGeneratedValue($this->tableGateway->getSequenceName());
if ($primaryKeyValue && count($this->primaryKeyColumn) == 1) {
$this->primaryKeyData = [$this->primaryKeyColumn[0] => $primaryKeyValue];
} else {
// make primary key data available so that $where can be complete
$this->processPrimaryKeyData();
}
$rowsAffected = $result->getAffectedRows();
unset($statement, $result); // cleanup
$where = [];
// primary key is always an array even if its a single column
foreach ($this->primaryKeyColumn as $pkColumn) {
$where[$pkColumn] = $this->primaryKeyData[$pkColumn];
}
}
// refresh data
$statement = $this->sql->prepareStatementForSqlObject($this->sql->select()->where($where));
$result = $statement->execute();
$rowData = $result->current();
unset($statement, $result); // cleanup
// make sure data and original data are in sync after save
$this->populate($rowData, true);
// return rows affected
return $rowsAffected;
}
}
Originally posted by @Sohorev at zendframework/zend-db#191 (comment)
You can use the TableGateway feature "SequenceFeature".
Originally posted by @froschdesign at zendframework/zend-db#191 (comment)
More related to zendframework/zend-db#186 inspired by zendframework/zend-db#172, and in part discussion from zendframework/zend-db#177 because of serial in his create statement. It is currently a deficiency in the SequenceFeature which makes it not ready to be documented and publicized that I am trying to solve in free time.
Originally posted by @alextech at zendframework/zend-db#191 (comment)
Also appears SequenceFeature cannot be applied to RowGateway, only TableGateway it seems? I will have to add support for that too.
Originally posted by @alextech at zendframework/zend-db#191 (comment)
@alextech
Can you check this: Add the Sequence feature and the RowGateway feature to TableGateway? The FeatureSet is needed.
Originally posted by @froschdesign at zendframework/zend-db#191 (comment)
Trying to do that, not understanding how to apply TableGateway to insertion problem. RowGatewayFeature from what see in docs and the test I made (following https://docs.zendframework.com/zend-db/row-gateway/)
$tbl = new TableGateway('usersdev', $this->dbcon, [new RowGatewayFeature('id')]);
$rs = $tbl->select(['id' => '2']);
/** @var RowGateway $row */
$row = $rs->current();
$row->name = 'Masha';
$row->save();
do not have this problem because once select query returns data array, follow up update() call already filters out keys specified in first parameter (primary key column name).
I do not see how to apply table gateway nor additional features to RowGateway for new data. Another undocumented feature? Can't be because
- RowGateway has no setters to its internal featureSet array which is not used for anything besides adding its own instance as a feature I guess for purpose of allowing magic calls
- RowGateway creates its own
Selectobject completely independently https://github.com/zendframework/zend-db/blob/master/src/RowGateway/AbstractRowGateway.php#L160, no call to any feature, not wanting table gateway.
Originally posted by @alextech at zendframework/zend-db#191 (comment)
I do not see how to apply table gateway nor additional features to RowGateway for new data.
The idea was to add a FeatureSet with the sequence and the RowGateway feature to the TableGateway,
RowGateway creates its own Select object ... not wanting table gateway.
This is why I asked for a check.
Thanks for you test!
Originally posted by @froschdesign at zendframework/zend-db#191 (comment)
This package is considered feature-complete, and is now in security-only maintenance mode, following a decision by the Technical Steering Committee.
If you have a security issue, please follow our security reporting guidelines.
If you wish to take on the role of maintainer, please nominate yourselfIf you are looking for an actively maintained package alternative, we recommend: