[SVN:3336] Field to track updated Database Row

[Leodagan]

I took the habit to add a new field to the DOL tables I'm working on to track the updates...

I just had the idea that this could be great if it was set on all tables so I won't have to update my schema each time I work on a different subject !

So I tried a simple update to make this change DB-wide !

Code: Select all

Index: DataObject.cs =================================================================== --- DataObject.cs (revision 3330) +++ DataObject.cs (working copy) @@ -115,6 +115,14 @@ [Browsable(false)] public virtual bool IsDeleted { get; set; } + /// <summary> + /// Last time this record was updated. + /// </summary> + [DataElement(AllowDbNull = false, Index = true)] + public DateTime Updated { + get { return DateTime.UtcNow; } + set { Dirty = true; } + } #region ICloneable Member

Adding a default Properties with datetime type to DataObject, having a DataElement Attribute so it's created in every table !

It always return Current Time so when recording any Object to database it should be updated, trying to "set" this field has no meaning, but I though setting the Dirty flag was pertinent...

There is some downtime when restarting DOL with this patch when it create the "indexed" datetime field...

Maybe it's too much index for some table, and should not be enabled by default ?

[Blue]

Index makes only sense if you query that at runtime else you hurt insert performance a bit for no reason. The usecase for a "modified" timestamp would be to audit changes. It would be best in this case to add the one who changed that DB row in case it was caused by a GM command.

We've often had items where we thought: who did change this and when? I mean ok, with GM logs (which we keep for years) it can always be found but having it right at hand would be nice ofcourse.

[Leodagan]

I had other use-case in mind I must admit

The typical use case for me is when launching my "development" Shard and changing some world content (working an hour on updating mobs, merchants, and other objects from the shard itself), when I'm finished working on my "dev shard" I need to extract a "SQL patch" from my updates to apply on my "prod shard" (or even to share with SVN updates, like the few amount of records I attached to Ceremony Script NPC)

With this date field I only need to select rows with a constraint : "Where Updated >= [BEGIN WORK TIME];" (this is why I indexed it, but it doesn't really need to be "fast")

Actually something to track the user who edited this row could be useful for Audit, but I really had development tools in mind

I don't know if there is any easy way to track "globally" HOW a record was updated, from what I think this would need a HUGE amount of Reflection to try to handle the "Stack Trace" leading to the SQL Query Method ?

there is some old code about an "AuditMgr" in the SVN trunk, I don't know if it's obsolete or unfinished, but I think for your purpose Blue this would be a better way to track other properties than just current time

[Leodagan]

Ok, if nobody is against, I would add something like this :

Code: Select all

Index: DataObject.cs =================================================================== --- DataObject.cs (revision 3330) +++ DataObject.cs (working copy) @@ -115,6 +115,14 @@ [Browsable(false)] public virtual bool IsDeleted { get; set; } + /// <summary> + /// Last time this record was updated. + /// </summary> + [DataElement(AllowDbNull = false, Index = false)] + public DateTime LastTimeUpdated { + get { return DateTime.UtcNow; } + set { Dirty = true; } + } #region ICloneable Member

this will create a non-indexed Datetime Column in Every Table, updating itself to UTCNow everytime the server write a row (no change on read...)

I can use a more exotic name to prevent any collision...

The can be useful to sort own records and match the one updated recently, giving the ability to review change to worldbuild or quest or any database object (maybe allowing for easier debug)

I don't see easy implementation to track more data about who changed the record, compared to this easy change

I can't see any easy way to have a server property switch for this, so if it could impact any user maybe it shouldn't get in core...

[Tolakram]

I'm fine with it.

Here where I work we add UpdatedBy and UpdatedOn to every table, but we are also required to have a full audit trail so in addition every table has a trigger that writes to a corresponding history table. Since DOL has no concept of UpdatedBy I think UpdatedOn (or whatever you want to call it) would be enough.

[Tolakram]

I did think of one concern, on the inventory tables I wonder what the restructure time is going to be when these load?

Since Inventory links to both ItemTemplate and ItemUnique I wonder if that will be an issue?

[Graveen]

Yes, no problem, go with it. I don't see any overhead to write DateTime. I don't also see a big impact on table size (and tbh it does not matter).

I would have suggested a Serverproperty to enable/disable it, but i also don't see any benefit to such a thing, so go for it

[Leodagan]

I did think of one concern, on the inventory tables I wonder what the restructure time is going to be when these load?

Since Inventory links to both ItemTemplate and ItemUnique I wonder if that will be an issue?

I should try some tests, but with a non indexed field, I don't think it will take much time...

And this will happen just once after update and restart.

[Blue]

Uthgard uses a "Modified" field on DataObject for quite some time now. No negative impact. It gets updated when the object is saved and was Dirty.

[Graveen]

I did think of one concern, on the inventory tables I wonder what the restructure time is going to be when these load?

Since Inventory links to both ItemTemplate and ItemUnique I wonder if that will be an issue?

I should try some tests, but with a non indexed field, I don't think it will take much time...

And this will happen just once after update and restart.

This is also per player (opposed as a global load similar to what is done with NPCs) so the overhead is negligeable, as we are speaking of a simple field (not pk, fk or index), - a few bytes read.

[Leodagan]

Added to Revision 3336