Cleanup function for ToSIC_EAV_DataTimeline table

[trouble2]

I'm submitting a

[x] feature request

...about

[x] other / unknown

Current Behavior / Expected Behavior

On some DNN installations which use 2sxc for a longer period of time, the ToSIC_EAV_DataTimeline table takes up a lot of space.
In most cases this is not a problem, but for optimisation and/or sql space limitations it would be a good idea to have some kind of cleanup functionality. Perhaps some scheduler and a setting in 2sxc where you can say delete everything older then ... ( 1 year, 6 months, 3 weeks, whatever),,,

Instructions to Reproduce the Problem

Your environment

General DNN/2sxc installations

[jeremy-farrance]

This was recently discussed in the Discord for DNN Community / 2sxc. I am very glad to -not- be alone regarding this (sometimes serious) problem!

2sxc and SQL Table Size Issues
https://discord.com/channels/706654343176126496/1315729795354595410

[jeremy-farrance]

Additionally, we often know when we are creating this situation. So a really great alternate solution would be to provide a new parameter to the existing App.Data.Update() method so that we can specify to NOT create a history item for the call. Of course it would default to true, but when we know we don't need or want the history, we just:

    if (wasTotal != total)
    {
      Dictionary<string, object> fields = new Dictionary<string, object>();
      fields.Add("Total", total);
      fields.Add("ScoresProcessed", DateTime.Now);
      App.Data.Update(scoreCardId, fields, saveHistory: false); // <<< history not needed <<< 
      Accu.Log($"Updated Total from {wasTotal} to {total} on ScoreCard for {scoreCard.EventQualification.EntityTitle}");
    }

    /// <inheritdoc cref="ToSic.Eav.Apps.IAppData.Update"/>
    void Update(int entityId, Dictionary<string, object> values, string userName = null, bool saveHistory = true);
                                                                                                     ^^^^^^^^

[jeremy-farrance]

And a third option would be a new setting named SaveHistory for all Content-Types. Always true by default to avoid compatibility issues, but allow Supers to be able to set it to false and have that particular CT never create history items. Yay!!

[iJungleboy]

I think the idea is great, BUT it would have to be a tweak API OR a settings record.

Reason is that adding parameters works in Razor code (which has been just about the only use case in the past), but breaks in compiled code.

So if Razor code uses a method, and the method is expanded in an update, things continue to work, because the line of code is recompiled the next time it's used.

On the other hand, if any DLL is using this code, then it internally references that exact method signature, and would break if parameters are added. I see this happening a lot in other frameworks :( where they are forced to add more and more signatures, to prevent breaking.

Since we've been using tweak APIs for just about everything, I think that's the way to go for now.

If anybody would want to contribute this, feel free to give it a go ;). I would not have the resources to do this any time soon.
If you have questions, I'll be glad to guide you.

[jeremy-farrance]

I realize you can shoot down my guesses at possible solution suggestions easily, but we are presenting a problem and asking for any solution.

The Dale Carnegie Method for Code (Jeremy's adapted version):

1. Problem
2. Possible Solutions and Options
3. If the Problem is worth solving, choose the Best Solution
4. Implemention
5. If a better/best solution is discovered later, Enhance!

Another possible solution (2) might be to add a new method:

    /// <inheritdoc cref="ToSic.Eav.Apps.IAppData.Update"/>
    void UpdateNoHistoy(int entityId, Dictionary<string, object> values, string userName = null);
                ^^^^^^

So then simply:

App.Data.UpdateNoHistory(scoreCardId, fields); // <<< history not needed <<< 

This option seems really nice to me because nothing will break and thanks to modern 2sxc IntelliSense, this will be easy to discover!

[jeremy-farrance]

Also, please note that I appear to have hijacked this Issue. @trouble2's hope was to have some sort of cleanup methods added to 2sxc.

Perhaps some scheduler and a setting in 2sxc where you can say delete everything older then ... ( 1 year, 6 months, 3 weeks, whatever)...

[iJungleboy]

Let's summarize:

Scheduled Clean-Up

@trouble2 wished for some kind of scheduled clean-up.

We do not have this as a priority so nobody I know would volunteer to implement this.
Anybody wishing for such a feature is welcome to contribute this - but I will close this task now, as we won't be doing this any time soon.

API to update data without History changes

@jeremy-farrance wished for a new API to create / update data without adding history.

I guess this too is an interesting idea but would require quite a bit of effort to do - and ATM would not be on our priorities.
So contributions would be welcome, but we won't plan this any time soon, so I'll close this.