Feedback on current O/R Mapping Unittests

[thr]

More nitpicking :
Would you be able to handle anything but a single integer primarykey properly ?

Ok, back to this question again - these composite keys are starting to get realy hard to work with. I'm toying around with a pessimistic/optimistic offline locking now and to keep track of a locked object/row that has ~4-5 keys is getting realy realy realy realy messy. I know it's pushing the limit to not allow more then one primary key or a sequenced key - but as far as I can see there is no 100% way to lock a composite key object/row when you need offline locking. And I've looked in PoEAA and all fowlers example use a single longint key, yes I know they are examples.

I've also looked at both EZPDO, Livepipe and some more libs and while they don't offer offline concurrency which is the main reason I would drop the composite key - they all require one unique ID. Yes I'm aware of propel and that it supports composite keys - which I do also(for now), but I see no way to lock objects for offline concurrency when they have multiple keys.

I'm more then open to suggestions on how to solve it tho

[thr]

I made a cute little drawing to show how the different object interconnect with eachother, any suggestions?

1. URL: http://mirkk.nu/orm/diagrams/Session.jpg

2. Uploaded here:

[Ren]

Optimistic locking, I thought, just requiring extra conditions on the WHERE clauses of the DELETE & UPDATE making sure a row hasn't changed from its expected value, and if the number of affected rows isn't 1 then you know somethings changed and time to abort the transaction.

As for pessimistic, I guess you must be using tables? to centrally track locked rows?

[kyberfabrikken]

I don't see the problem either, but I may be missing something obvious ?

[thr]

I don't see the problem either, but I may be missing something obvious ?

For optimistic locking no - I don't require one unique id, but take pessimistic - I might got a locking table that looks like this:

[ Owner ] [ Locktype ] [ Table] [ Key ]
 sessid1   ex. write    Person     1

Now If i wanted to lock a table with more then one key field where would I put the key? Yes I know it's possible to use my hashed key for the object (which I use in my identity map) - but that requires many queries (ie first retriving all locks for the table you're querying, then retrieve all rows you want, then compare locks <> rows and see if any locks match any of the hashed primary key values for your rows)

Hmm, ok - It doesn't sound so bad when I write it out that way tbh... maybee I was a bit tired last night ;p ah well I'll have another go at it today.

[thr]

Optimistic locking, I thought, just requiring extra conditions on the WHERE clauses of the DELETE & UPDATE making sure a row hasn't changed from its expected value, and if the number of affected rows isn't 1 then you know somethings changed and time to abort the transaction.

Yes you are correct, it requires a sequence version field. (You could move the sequenced value field to a separate table if you wanted, to not pollute the real table but that's a bit to much hassle tbh).

As for pessimistic, I guess you must be using tables? to centrally track locked rows?

Yes, as I described in my post above Optimistic is "np" with Composite Keys, the problem comes when you want to use Pessimistic locking and have to save the whole Composite Key in one table.

No another note, I talked to Illusina on the forums over MSN last night and we came to the conclusion that pre/post treatment (or "lifecyckle hooks") queries/objects is realy nice to have, atm. I've only come up with 6 of em which are the folowing:

• PreDelete
• PreUpdate
• PreInsert
• PostDelete
• PostUpdate
• PostInsert

Would any of you consider more of hooks needed?

[seacon]

No another note, I talked to Illusina on the forums over MSN last night and we came to the conclusion that pre/post treatment (or "lifecyckle hooks") queries/objects is realy nice to have, atm. I've only come up with 6 of em which are the folowing:

• PreDelete
• PreUpdate
• PreInsert
• PostDelete
• PostUpdate
• PostInsert

Would any of you consider more of hooks needed?

Have you taken a look at RoR implementation?

[seacon]

I made a cute little drawing to show how the different object interconnect with eachother, any suggestions?

1. URL: http://mirkk.nu/orm/diagrams/Session.jpg

It's surprisingly similar to what I have been working on so far the past several months.. though my main target is PHP4.

BTW, how is the Session component in the drawing related to PHP Session? To make it clearer, I have it named 'Workspace' and the PHP session can hold multiple instances of workspace just for adding the possiblity to interact with different data sources (different DB, server etc.). Also, each workspace has only 1 active transaction, so actually in my implementatin, 1 workspace = 1 UOW.

[Ren]

Yes you are correct, it requires a sequence version field. (You could move the sequenced value field to a separate table if you wanted, to not pollute the real table but that's a bit to much hassle tbh).

A timestamp, aye, thou not strictly necessary, if keep the original row retrieved.

Now If i wanted to lock a table with more then one key field where would I put the key? Yes I know it's possible to use my hashed key for the object (which I use in my identity map) - but that requires many queries (ie first retriving all locks for the table you're querying, then retrieve all rows you want, then compare locks <> rows and see if any locks match any of the hashed primary key values for your rows)

Hmm, perhaps time to use DBMS specific extensions? MySQL has

SELECT * FROM Persons LOCK IN SHARE MODE;
&
SELECT * FROM Persons FOR UPDATE;

?

[Ren]

• PreDelete
• PreUpdate
• PreInsert
• PostDelete
• PostUpdate
• PostInsert

Dont think there is much more in providing client side triggers.

[thr]

Hmm, perhaps time to use DBMS specific extensions?

I realy don't wanna do DBMS specific stuff - using PDO to get database indepence.

[Ren]

Ok,

How about Key column being a varbinary column, and using pack() on the composite primary key.

Edit:
Would have to incorporate the length if any column is a varchar.. actually the identity map could use it too, only just thought of it because been writing Zip reading/writing classes last few days.

Edit #2:
Actually its probably a pain to use, as its a varargs function, and doesnt take an array of stuff to pack, so scratch that.

[kyberfabrikken]

Yes you are correct, it requires a sequence version field. (You could move the sequenced value field to a separate table if you wanted, to not pollute the real table but that's a bit to much hassle tbh).

You don't need to have a version-field. You can use all columns as criteria instead. That way, if the row has changed, it won't get updated.

• PreDelete
• PreUpdate
• PreInsert
• PostDelete
• PostUpdate
• PostInsert

Would any of you consider more of hooks needed?

onbeforesave and onaftersave are nice too.

[thr]

You don't need to have a version-field. You can use all columns as criteria instead. That way, if the row has changed, it won't get updated.

Ok, good point - funny it's not even mentioned in PoEAA. Not that I doubt your knowledge about this but is there any know drawbacks by using all columns vs. versioning field?

onbeforesave and onaftersave are nice too.

Hm, what's the difference between that and onInsert() / onAfterInsert() ?

[33degrees]

Hm, what's the difference between that and onInsert() / onAfterInsert() ?

onSave() would be called on both updates and inserts, allowing a single spot to put code that is needed on both; especially useful for validation, IMO.

[thr]

onSave() would be called on both updates and inserts, allowing a single spot to put code that is needed on both; especially useful for validation, IMO.

ah, subtle but important difference ;p

[kyberfabrikken]

Ok, good point - funny it's not even mentioned in PoEAA. Not that I doubt your knowledge about this but is there any know drawbacks by using all columns vs. versioning field?

There's the obvious - You'll need to keep a lot of data in memory, that you wouldn't otherwise. For most scenarios I don't think this is much of a problem. If you have an indentitymap in the first place, you're probably not going to juggle around with thousands of records anyway.
I don't think there's any other pitfalls, but I'm not sure.

Would any of you consider more of hooks needed?

Perhaps the ability to register a one-time handler. Not sure how useful this actually is, but it could be used internally by the uow to update dependencies etc. Again - like with the onsave events, this is just conveniencemethods - it could be handled elsewhere even if you don't support it directly.

[jayboots]

I think onSave complicates the code. Does that imply that if onSave is defined that the insert/updates won't be called? I'd say it is better to follow transact SQL -- if required, factor validation into other methods and call that from the standard places.

[Ren]

There's the obvious - You'll need to keep a lot of data in memory, that you wouldn't otherwise. For most scenarios I don't think this is much of a problem. If you have an indentitymap in the first place, you're probably not going to juggle around with thousands of records anyway.
I don't think there's any other pitfalls, but I'm not sure.

• Blob types maybe a problem, uncertain if can compare.
• string types and case insensitivity, may have to ensure the condition tests as binary, using CAST()

[33degrees]

I think onSave complicates the code. Does that imply that if onSave is defined that the insert/updates won't be called? I'd say it is better to follow transact SQL -- if required, factor validation into other methods and call that from the standard places.

No, both onSave and onInsert would be called for inserts, and both onSave and onUpdate called for updates. The only issue what order they're called in; I put onSave first, because it's more generic, and the other two are specialisations. In any case, as long as the exact mechanism is properly documented, it's not really an issue.

[33degrees]

Speaking of hooks, do you plan on allowing them to modify the action being executed, or are they simply called before and after? I'm currently using hooks in my orm for validation, and if they return false the action is interupted, but I'm considering also adding the ability to modify the query to be able to plugin different behaviors i.e. changing a DELETE FROM news WHERE id = 1 to a UPDATE news SET deleted = 1 WHERE id = 1. Are you considering doing something similar?

[jayboots]

No, both onSave and onInsert would be called for inserts, and both onSave and onUpdate called for updates. The only issue what order they're called in; I put onSave first, because it's more generic, and the other two are specialisations. In any case, as long as the exact mechanism is properly documented, it's not really an issue.

Say I only want to implement insert functionality (not update) -- I can either implement validation directly in the beforeInsert or I can implement both beforeInsert and onSave. Further, insert and update can have different validation rules (for example, updates may be restricted on certain fields). In my view, this extra semantic (onSave) leads to confusion especially because it is at a different level of generalization. I can imagine a higher-order object that implements onSave but forgoes the insert/update pairs but I don't see the point to have them all available at the same level.

[kyberfabrikken]

In my view, this extra semantic (onSave) leads to confusion especially because it is at a different level of generalization. I can imagine a higher-order object that implements onSave but forgoes the insert/update pairs but I don't see the point to have them all available at the same level.

I think I agree on that.

[thr]

There's the obvious - You'll need to keep a lot of data in memory, that you wouldn't otherwise. For most scenarios I don't think this is much of a problem. If you have an indentitymap in the first place, you're probably not going to juggle around with thousands of records anyway.
I don't think there's any other pitfalls, but I'm not sure.

• Blob types maybe a problem, uncertain if can compare.
• string types and case insensitivity, may have to ensure the condition tests as binary, using CAST()

Ok - going to have to check this out more before I decide, but I think it'll be better to go with the standard way to use a versioning/timestamp field.

I think onSave complicates the code. Does that imply that if onSave is defined that the insert/updates won't be called? I'd say it is better to follow transact SQL -- if required, factor validation into other methods and call that from the standard places.

Would you say that for all (ie: onDelete, onUpdate, onInsert) or just for onSave? And by complicating the code are you pointing at the codebase of the ORM or the codebase where you use the orm?

No, both onSave and onInsert would be called for inserts, and both onSave and onUpdate called for updates. The only issue what order they're called in; I put onSave first, because it's more generic, and the other two are specialisations. In any case, as long as the exact mechanism is properly documented, it's not really an issue.

Ok - good points there, yes documentation is A and O. But i'm not sure about onSave it seems to generic realy.

Speaking of hooks, do you plan on allowing them to modify the action being executed, or are they simply called before and after? I'm currently using hooks in my orm for validation, and if they return false the action is interupted, but I'm considering also adding the ability to modify the query to be able to plugin different behaviors i.e. changing a DELETE FROM news WHERE id = 1 to a UPDATE news SET deleted = 1 WHERE id = 1. Are you considering doing something similar?

Oh, yes - atm. I was thinking about returning bool(true) == continue query and bool(false) == bail out, but the more I think about it the more it seems odd. If data is invalid should it realy come to the millisecond before the db write to bail out and not earlier?

Say I only want to implement insert functionality (not update) -- I can either implement validation directly in the beforeInsert or I can implement both beforeInsert and onSave. Further, insert and update can have different validation rules (for example, updates may be restricted on certain fields). In my view, this extra semantic (onSave) leads to confusion especially because it is at a different level of generalization. I can imagine a higher-order object that implements onSave but forgoes the insert/update pairs but I don't see the point to have them all available at the same level.

Ok some realy good points there again, and I agree that the onSave (as I said above) feels way to generic. And about validation? Shouldn't data validation be put somewhere else except in the ORM layer? Seems odd to have it there.

the onDelete/onUpdate/onInsert-methods are (imho) there more to simmulate cascades, etc. for RDBMS that doesn't support it nativley and do some "strange" cascades that aren't supported commonly.

[jayboots]

Would you say that for all (ie: onDelete, onUpdate, onInsert) or just for onSave? And by complicating the code are you pointing at the codebase of the ORM or the codebase where you use the orm?
...
Ok some realy good points there again, and I agree that the onSave (as I said above) feels way to generic. And about validation? Shouldn't data validation be put somewhere else except in the ORM layer? Seems odd to have it there.

It is perfectly reasonable to build a more generalized layer on top of the ORM layer, so I tend to agree: validation issues should be outside of the ORM implementation. Whether the application factorizes validation or not, it is still up to the app to issue/call validation from within the normal insert/update/delete hooks.