Let the database manage defaults on UPDATE calls

In our system, we have a single database server with many middle tier clients.
Each middle tier client will have a different time (milliseconds, but important nonetheless)

So, we'd like to generate timestamps on the database server

Our platform is SQL Server which allows this construct with the DEFAULT keyword

UPDATE SomeTable SET ..., UpdatedOn = DEFAULT, ...

Note that most database calls are via Lightspeed unless performance is critical (we have up to 100k writes per second) where we use stored procedures. In the stored procedures the RDBMS generates timestamps (as above), so even if we made the middle tier use some whizzy timestamp generator, we'd still have differences in the system as a whole

This SQL construct means

• the client doesn't need knowledge of the actual default
• UpdatedOn can be maintained without a trigger

Is this possible in Lightspeed please?

Thanks Shawn aka gbn

Repro script:

CREATE TABLE data.test (
    ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Filler varchar(100) NOT NULL,
    CreatedOn datetime NOT NULL DEFAULT GETDATE(),
    UpdatedOn datetime NOT NULL DEFAULT GETDATE()
)
GO
INSERT data.test (Filler) VALUES ('foo'),('bar'),('cat');
GO
SELECT * FROM data.test
GO
UPDATE data.test 
SET 
    Filler = 'dog',
    UpdatedOn = DEFAULT -- this behaviour
WHERE ID = 3;
GO
SELECT * FROM data.test
GO

This isn't currently possible in LightSpeed because LightSpeed expects to update the in-memory copy of the entity as well as the database record, and the current architecture doesn't support reading values out of the database. I'm investigating an approach where we would update the in-memory copy with our best estimate of the time while letting the database do its own thing; this would mean that after saving the in-memory entity could be out of sync with the database record depending on the clock difference between the middle tier and the RDBMS. This probably wouldn't be an issue unless you intended to use the in-memory entity after the save. Is that something you'd want to be able to do?

(Please note we are not committing to offering this feature -- just looking at feasibility.)

Thank you

The database will be read/extracted separately for MIS and BI purposes which is where the importance of coherent timestamps becomes important.

So this would be sufficient, if Lightspeed could issue the UPDATE with the DEFAULT keyword

An UPDATE without DEFAULT won't affect the column in SQL Server. This is different to MySQL's auto-update on a TIMESTAMP column using DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP which would be ideal...

Hi Shawn,

This will be in the next nightly build. To use it, implement a custom timestamp strategy (as explained in the LightSpeed user guide), and have your strategy class implement the new IAutoTimestampExpressionStrategy interface. Your GetTimeExpression method should return the desired SQL expression for the column, or null to use the client-side timestamp returned from IAutoTimestampStrategy.GetTime().

public object GetTimeExpression(Type type, TimestampUsage usage, LightSpeedContext context) {
  if (usage == TimestampUsage.DeletedOn)
    return null;
  return "DEFAULT";  // or "GETDATE()" or "GETUTCDATE()" etc.
}

As previously mentioned LightSpeed will NOT read back the timestamp generated by the database. After saving changes, therefore, the entity will contain the timestamp returned from IAutoTimestampStrategy.GetTime(). If your application needs to know the timestamp generated by the database, you will need to reload the entity.

If your application needs to be portable across different database engines, you can use the LightSpeedContext.DataProvider value to determine which database is being targeted.

Please let us know if you run into any problems or have any feedback!

Thank you: this is very good service

It'll be next week now before we can test I'm sorry.