So, to give you some context: I have a client, written in delphi, that is directly connected to the database (no server in-between) at the moment. that means there are around 300 clients connected to my db on a daily basis.. that is obviously bad.
the plan is to build an api-server using typescript. but because this is a pretty complex system and it will take years to transition, we cant do the transition at a fixed point in time but all this has to happen over time.
that creates a lot of problems that need to be solved. one of them is data integrity and transaction safety.
so my idea was to generate a hash from every entity when it is read from the database. when someone wants to write to the db, the hash musst be passed back with the update request, and only if the passed hash matches the hash matches the current state of the entity in the database, the update request will be further executed.
now it may happen, that the client wants to update data trough the api that it initially got via the direct connection. so the client needs to be able to generate that same hash key.
first possible solution
one solution i thought about was to serialize the entity data to json and generate the hash from the json string. downsides:
- all json serializers (in the different languages and environments) must use the same syntaxes
- indentation
- quotation-mark/aphostroph
- float with comma or dot
- date serialization
- etc
- the field names may be/are different in the database and the api (database is all in German, api will be using English)
second possible solution
the second solution i thought of was to just serialize all fields to a csv-like format
<field-1-content>;<field-2-content>
downsides:
- serialization of all non string values must be the same in all environments but would be doable i guess
- the field-order must be the same everywhere. that seems really hard, especially since we use database abstraction layers in both environments. sorting alphabetically by field names is not possible since the api uses English names and the database has German field names. also some field names are changed in the sql statements the client executes (yeah, the client uses direct sql and an abstraction layer, i am sorry to tell you that ._.).
third possible solution
third idea was to just insert a "random_id" field into every entity in the database that gets changed whenever the entity is updated. that would mean a lot of work in the database since that one is pretty messy too. so i wanted to go around that if possible.
what would you do? any other ideas i didn't have?
thanks in advance
Most databases got this covered. We are using MSSql and do this using 'timestamp' fields. This field is automatically updated by MSSql whenever data changes. Each client receives the value from this field (the value can be casted to a Int64) and returns the value when it wants to update data in the database.
The update statements looks something like this: