0
 Followers
1
 Like

Action for Entity Update only if data diferent

Backend
New

Today the entity has the action: 

  • Create
  • CreateOrUpdate
  • Update
  • Get
  • GetForUpdate
  • Delete

I suggest create a new action called "UpdateOnlyIfChanged" and/or "CreateOrUpdateOnlyIfChanged" because if is need sync one database with another is more easy.

Today I use CreateOrUpdate for sync, but for all data is save in database unecessary if dont have modified.

Consequently the sync is more slow and I pay more in cloud because I have more writen in disk.

Created on 28 Dec 2018
Comments (5)

Imagine this scenario:

1. On Device A and Device B, the users both  sync with the server, and get a copy of Record 1.

2. On Device A, the user shows an edit screen for Record 1. The same thing happens on Device B. They both show the exact same fields.

3. On Device A, the user makes a change to field 1, saves, and syncs. The server now has their changes to field 1 in the database.

4. On Device B, the user makes no changes; they are not sure if the user on Device A made any changes, but based on the information they have, the information on their screen is 100% accurate. They save, and sync.

In your logic, the "change" that the user on Device B made would never go to the server. The user was told "your record has been saved" but their record was not actually saved. Your approach causes data integrity problems in multi user/multi tab scenarios. Users always assume, unless they have very good reasons, that the most recent save will be the exact data that goes into the database and becomes the record and its values. Your approach completely breaks that expectation.

The only way to do this the way you want, is during the sync, you compare the record in the DB to the record being sync'ed; that is actually slower because now you are adding a read to all "modified" records being sync'ed, an in-memory compare, and THEN an update/insert to some of them, rather than just writing all of them.

J.Ja

Changed the category to Backend


The idea is the same from the end. Instead of doing a direct update to the database first it makes a select and then an update if the data has been changed. The data is always updated, but is only written to disk if there is a change.

And when I said sync it was more server-friendly, example synchronizing a SAP BAPI with the database in outsystems, this has no competing user and the data would always be overwritten if the source base (BAPI) has been changed. But it suits any other situation as well.

In summary would include the command that makes Select + Update before modifying in the database.


Imagem relacionada

If this is what you are asking for, how is it different from the existing functionality (which is the default)?

J.Ja

I created an action and opened the generated code and obtained the following:

---------------------------------------------------------------------------------

/// <summary>

  /// Action: Updateteste

  /// </summary>


  public static void Updateteste(HeContext heContext, BitArray usedFields, RCtesteRecord inParamSource) {

   ENtesteEntityRecord ssENteste = inParamSource;

   using(Transaction trans = DatabaseAccess.ForCurrentDatabase.GetRequestTransaction()) {

    if (usedFields == null) {

     usedFields = new BitArray(3, true);

    }

    string updateSet = "UPDATE " + ENtesteEntity.LocalViewName(heContext.AppInfo.Tenant.Id, BuiltInFunction.GetCurrentLocale()) + " SET ";

    string parameters = "";

    parameters = (usedFields[1] ? (StringUtils.SuffixIfNotEmpty(parameters, ", ") + "`nome` = ?ssnome") : parameters);

    parameters = (usedFields[2] ? (StringUtils.SuffixIfNotEmpty(parameters, ", ") + "`endereco` = ?ssendereco") : parameters);

    string whereCondition = " WHERE `Id` = ?ssENtestessId";

    string sql = updateSet + parameters + whereCondition;

    Command sqlCmd = trans.CreateCommand(sql);

    int counter = 0;

    if (usedFields[1]) {

     sqlCmd.CreateParameter("?ssnome", DbType.String, ssENteste.ssnome);

     counter++;

    }

    if (usedFields[2]) {

     sqlCmd.CreateParameter("?ssendereco", DbType.String, ssENteste.ssendereco);

     counter++;

    }

    if (counter == 0) {

     string dummyUpdate = "`nome` = `nome`";

     string noUpdate = updateSet + dummyUpdate + whereCondition;

     sqlCmd = trans.CreateCommand(noUpdate);

    }

    sqlCmd.CreateParameter("?ssENtestessId", DbType.Int64, ssENteste.ssId);

    if (sqlCmd.ExecuteNonQueryWithoutTransformParametersSyntax("Entity Action Updateteste", true) <= 0)

     throw new DataBaseException(ENtesteEntity.LocalViewName(heContext.AppInfo.Tenant.Id, BuiltInFunction.GetCurrentLocale()) + " with key " + ssENteste.ssId + " was not updated");

   }

  }


---------------------------------------------------------------------------------

We can even use this Update Behavior screen with a "Only if different to data in database" option. The idea is to make the code generate something like:

---------------------------------------------------------------------------------

/// <summary>

  /// Action: Updateteste

  /// </summary>


  public static void Updateteste(HeContext heContext, BitArray usedFields, RCtesteRecord inParamSource) {

   ENtesteEntityRecord ssENteste = inParamSource;

   using(Transaction trans = DatabaseAccess.ForCurrentDatabase.GetRequestTransaction()) {

    if (usedFields == null) {

     usedFields = new BitArray(3, true);

    }

    string updateSet = "UPDATE " + ENtesteEntity.LocalViewName(heContext.AppInfo.Tenant.Id, BuiltInFunction.GetCurrentLocale()) + " SET ";

    string parameters = "";

    parameters = (usedFields[1] ? (StringUtils.SuffixIfNotEmpty(parameters, ", ") + "`nome` = ?ssnome") : parameters);

    parameters = (usedFields[2] ? (StringUtils.SuffixIfNotEmpty(parameters, ", ") + "`endereco` = ?ssendereco") : parameters);

    string whereCondition = " WHERE `Id` = ?ssENtestessId";

    string sql = updateSet + parameters + whereCondition;

    Command sqlCmd = trans.CreateCommand(sql);

    int counter = 0;

    if (usedFields[1]) {

     sqlCmd.CreateParameter("?ssnome", DbType.String, ssENteste.ssnome);

     counter++;

    }

    if (usedFields[2]) {

     sqlCmd.CreateParameter("?ssendereco", DbType.String, ssENteste.ssendereco);

     counter++;

    }

    if (counter == 0) {

     string dummyUpdate = "`nome` = `nome`";

     string noUpdate = updateSet + dummyUpdate + whereCondition;

     sqlCmd = trans.CreateCommand(noUpdate);

    }

    sqlCmd.CreateParameter("?ssENtestessId", DbType.Int64, ssENteste.ssId);


    // --------------------------- //

    change = true;

    // Modificação

    if(UpdateBehavior == "Only if different to data in database"){

    SELECT nome, endereco FROM " + ENtesteEntity.LocalViewName(heContext.AppInfo.Tenant.Id, BuiltInFunction.GetCurrentLocale()) + " WHERE Id` = ?ssENtestessId"

    if(nome != usedFields[1] || endereco != usedFields[2]){

    change = true;

}else{

change = false;

}

}

if(change){

  if (sqlCmd.ExecuteNonQueryWithoutTransformParametersSyntax("Entity Action Updateteste", true) <= 0)

        throw new DataBaseException(ENtesteEntity.LocalViewName(heContext.AppInfo.Tenant.Id, BuiltInFunction.GetCurrentLocale()) + " with key " + ssENteste.ssId + " was not updated");

    }

// --------------------------- //



   }

  }

views
170
Followers
0