Atomic commits

[christiangarsia]

Can I create atomic commits involving different tables using Maphper? If an insert fails, I want everything to roll back. I want to use SQLite3.

[TRPB]

I'm not sure there would be a particularly clean way to implement this because however you do it, it's action at a distance (By design, it's changing the database).

Assuming transactions work the same way they do in MySQL, you could do this:

$blogSource = new \Maphper\DataSource\Database($pdo, 'blog', 'id');

$pdo->query('START TRANSACTION');

// Do something with your various mappers

$pdo->query('COMMIT'); //or ROLLBACK

While this could be done in the Database class, this would assume that all databases support transactions and break the principle of least surprise:

$blogSource->beginTransaction();
//...
$blogSource->commit();

Would affect all \Maphper\DataSource\Database instances because it's configuring the underlying database connection.

I'm happy to take suggestions, but because this is done at a database level, not a table level, it doesn't really fit neatly into what Maphper is doing.

[TRPB]

Thinking about this a little more, Maphper could infer transactions when performing multiple inserts at the same time.

$user = new \stdclass;

$user->name = 'Foo';

//Address is a relationship
$user->address = new \stdclass;

$user->address->address1 = '123 fake street';

//Perform a nested save to `user` and `address` tables
$users[] = $user;

It would be limited to this single use case: Inserting multiple records at the same time using the defined relationships.

Would that work for what you're doing?

Having said that, I wouldn't recommend getting to the stage where an insert fails. Maphper performs insert or update and assumes that the data is saved. In edit mode, it automatically updates the table to fit the data supplied.

I'd recommend making sure the data is valid before sending it to the database. See also: https://r.je/4-reasons-to-avoid-foreign-key-constraints-database-logic

[christiangarsia]

Thanks. That is exactly what I am looking for, although I would need to go one level deeper (eg. address also has an object and a third table would be required), would that still be possible? I understand what you mean about validating the data before, but I’m thinking about a scenario where the database connection would be lost (eg. power loss) half way and I would want to keep my tables consistent.

[TRPB]

Yes, it would work for any depth.

I'll try to get a patch together over the next few days. The hard part is keeping track of whether a transaction has already been started which looks like it's poorly supported on both MySQL and SQLite.

[christiangarsia]

Yes, it would work for any depth.

I'll try to get a patch together over the next few days. The hard part is keeping track of whether a transaction has already been started which looks like it's poorly supported on both MySQL and SQLite.

Any update on this request? Thanks.

[pwFoo]

Would be a nice optional feature I think.