"nested transactions" in pomm?

[tlode]

As I understand the ModelLayer concept, this is the place to put multiple queries into one transaction.

Supposed I have a method in a ModelLayer class which uses a transaction and want to wrap that method call into another transaction. In ModelLayer I have to check if there is currently an active transaction and decide whether to use startTransaction() or setSavepoint(). This goes like this (every time)

$inTransaction = $this->isInTransaction();
try {
  if ($inTransaction)
    $this->setSavepoint('sp_1');
  else
    $this->startTransaction();

Same goes for commits and rollbacks which is pretty annoying. Is there already an elegant way to avoid this?

I usually solved this by implementing a simple transaction stack, who keeps track of ongoing transactions and save points. begin, commit or rollback methods take care of what method to use. I've copied a trivial example in here.

[chanmix51]

Each Public method in a ModelLayer is a transaction. If several methods want to share some code, they call a protected (or private) method that assumes it is in a transaction block. It can throw an exception in case of a failure which is caught by the public method managing the transaction. If you want a partial rollback, use the savepoints.

[tlode]

This would only be possible if all methods were inside the ModelLayer sub class. But what if I have distributed methods in different objects?

I've already found a solution using a transaction trait which overwrites the implementation, when I need to. But to be more clear about the problem and because I am interested how this would be solved with pomm, I give an example of what I need to do:

Supposed, I have different types of hierarchical data objects, let's say categories and menu items. Both have nothing to do with each other. Instead of using parent-child relations, I often use nested sets for hierarchical data, which does not often change. Therefor I have three tables: nested_nodes, categories and menu_items. categories and menu_items references nested_nodes by foreign key.

In pomm this could lead to three Model classes. A generic NestedNodesModel and specific CategoriesModel and MenuItemsModel. If I need to insert a new category here is what I would like to do in simplified code:

$categoriesModel->createAsChildOf($rootCategory);
->    begin transaction
->    $newNode = $nestedNodesModel->createNodeAsChildOf( $rootCategory->node_id );
       ->    savepoint x;
       ->    $nestedNodesModel->spreadForChildRightInsert();  // make space for a new node
       ->    $nestedNodesModel->createAndSave(['lft' => ..., 'rgt' =>  ...]);
       ->    release savepoint x;
->   $newCategory = $categoriesModel->createAndSave(['node_id' => $newNode->id, ...]);
->   commit transaction

As you can see the code is distributed over different models using transactions. Nested nodes could also be created without any other objects depending on it. So actually they need to know if they should use begin transaction or savepoint.

If I would now add a test which also wraps the whole thing into a begin ... rollback transaction, even categoriesModel would have to use savepoint instead of begin transaction.

But, there is one more problem: pomm does not "allow" to use transactions in Model only in ModelLayer. So let's move it there, but since both entities uses transactions, both methods need to be moved into their ModelLayer class. And from within it is not possible to get another ModelLayer by the api - there is only getModel().

I hope, I could make the above problem more clear. In my experience it is not an uncommon case to use "nested transactions".

If you are interested, I've added a rough version of TransactionTrait here. This let me even insert transaction methods into Model classes.

[chanmix51]

The fact that transaction methods are only available in ModelLayer classes is done on purpose. Model methods should embed one SQL query hence they are atomic by definition.

Model layer is here to group model calls in transactions and to turn technical exceptions into busines oriented exceptions. Several model classes can be used in the same transaction within the same ModelLayer method, this is why a deferring constraint method is available here.

Organizing the code between the model and model layer classes is as important as between controllers and model. In the Model classes, queries should be protected with public methods calling them with a Where instance as parameter. Each model method use one SQL query. So they can be grouped into transaction within a ModelLayer method.

[tlode]

Thank you for making this clear. Wouldn't it be good then, to also allow access to other ModelLayer classes within a ModelLayer instance?

In my example above it would help when CategoriesModelLayer could have access to methods of NestedNodesModelLayer for creating a new node. Otherwise it would unnecessarily need to know how a nested node is to be created.

[chanmix51]

Ok, I understand why you get confused. The name of the model layer class is not correlated with model classes it uses nor related tables.

Since database schemas may be seen as functionnal grouping of elements, maybe a good model layer class name would be the name of the schema like PublicModelLayer which would group transactions about this schema's relations. This ModelLayer instance would manipulate all model classes in the PublicSchema namespace…

[tlode]

You are right, that's what I thought :-)

Wouldn't you agree, that e.g. a UsersModelLayer could group all user related queries (groups, roles, ...) in a well defined api and at the same time it is also only one component in a bigger data model, which is organized through various other ModelLayer instances. These instances would also have the need to use UsersModelLayer from within. That's actual the main part I am confused about (and of course how "nested transactions" would fit in this).

But I think I'll find my way around this. I really don't want to waste your time any longer :-) Thanks

[chanmix51]

Can I close this issue now ?

[tlode]

Yes, thanks.