Retreival, Validation and Saving in single transaction

[ludydoo]

Hi,

If I have to do some validations on a model before saving it, what is the recommended approach?

The validation and update/create/delete operations have to be done in a single transaction.

Would this

Code: Select all

EntityConnection.BeginTransaction
aEmp := Context.GetEntity<TEmp>('empno = 1');
if aEmp.IsBlue = true then
aDept.Save;
EntityConnection.Commit

work?

[AlexP]

Hello,

Judging by your code, you need no transaction, since you are saving data if the condition is handled only.

[ludydoo]

Well not really because if not in the same transaction, aEmp.isBlue could have changed

aEmp := Context.GetEntity<TEmp>('empno = 1');
// aEmp(id=1).isBlue could have been changed here if not in a transaction.
if aEmp.IsBlue = true then
aDept.Save;

[AlexP]

Please describe in more details the behavior you want to implement in threads - and we will try to help you resolve the issue.

[ludydoo]

For example ;

There is an environment-wide parameter that describes the active term for the application (begin date and end date). Then, every user also has a term (begin date and end date). The user term must be included in the environment term. This is a business rule.

When I want to modify the user term, I have to check the application term, and check if the date resides inside of it.

Code: Select all

// when saving the user
if (aUser.termEnd > aEnv.termEnd) then aUser.termEnd := aEnv.termEnd;
if (aUser.termBegin < aEnv.termBegin) then aUser.termBegin := aEnv.termBegin;

// when saving the env
for(aUser in aEnv.aUsers) do begin
    if (aEnv.TermEnd < aUser.TermEnd) then
        aUser.TermEnd := aEnv.TermEnd;
    if (aEnv.TermBegin > aUser.TermBegin) then
        aUser.TermBegin := aEnv.TermBegin;
    aUser.Save();
end;

All those verifications and modifications have to happen in a single transaction, since it's a multi user application. Otherwise, between two checks, the environment and/or user parameters may have been changed by another operation on the database, by another user.

There are many business rules like this that must be enforced, and thus, that need to happen in a single transaction otherwise the data may be corrupt. I have accounting firms that have 100 employees using the software. This greatly augments the risk of data corruption when users work on the same data.

The advantage I saw in EntityDAC is that it abstracts the database tables and operations into a OO layer using models and LINQ. Comes in handy when there are more than a hundred of tables, stored procedures, etc. Also handy for navigating through joined properties (Client.Transactions.Entities.Prop.Prop....).

Though, the validation of those business rules cannot be done by EntityDAC, to my knowlege, since there is absolutely no concept of business rules or validations in EntityDac / SchemaModeler for Delphi. The only validation it enforces is that it sets the maximum field length.

I checked on other products such as RemObjects DataAbstract. They support business rule scripting. It is impossible to violate them since they are validated in a single transaction. There is no possibility of conflicting changes made to the database, due to simultaneous operations on the same data.

What I would like to see with EntityDAC is the ability to check business rules before updating, creating, deleting or selecting data, inside a single transaction. If the business rules are violated, the transaction must rollBack, otherwise, it commits.

The example I provided would look something like this in SQL

Code: Select all

BEGIN TRANSACTION

UPDATE FROM USERS SET TERM_BEGIN = _APP_TERM_BEGIN
WHERE TERM_BEGIN < _APP_TERM_BEGIN

UPDATE FROM USERS SET TERM_END = _APP_TERM_END
WHERE TERM_END > _APP_TERM_END

UPDATE FROM APP SET TERM_BEGIN = _APP_TERM_BEGIN
WHERE ID = _APP_ID

UPDATE FROM APP SET TERM_END = APP_TERM_END
WHERE ID = _APP_ID

COMMIT;

That would perhaps be represented in a LINQ query as such :

Code: Select all

function Users.OnUpdate(aDefaultUpdateScript : LinqQuery) : LinqQuery
var 
    aQuery : LinqQuery;
begin
    aQuery := Linq
    .Update
    .From(Users)
    .Where(Users.TermBegin < App.TermBegin)
    .Set(Users.TermBegin = App.TermBegin)
    .Then
    .Update
    .From(Users)
    .Where(Users.TermEnd > App.TermEnd)
    .Set(Users.TermEnd = App.TermEnd)
    .Then(aDefaultUpdateScript) // Contains default update script for Users table
    .Then
    .Insert(Log)
    .Set(Log.Message = "App Term Changed")
    .TriggerEvent("App Term Changed")
    .Commit()

end;

In sum it would be useful to have a full SQL scripting / Business rule support with EntityDAC

You can check how Transactions is handled in IBObjects which is quite useful

http://www.ibobjects.com/ibo_faq.html#transactions

[AlexP]

In order to implement this behavior, you can both manually run the transactions and use the TEntityContext.SubmitChanges method, without calling the Save method in each Entity. Upon calling this method, all the modified Entities will be stored in the database in a separate transaction.