How to make sure record Ids are globally unique across multi DB?

edwinsn · 2015-03-04 18:11:43

Hi friends,

Imagine we have 10 POS system running standalone, and at the end of the day all the sales data will be synced to the master server at headquarter for building analysis reports.

That means we have 10 databases of the very same table structure, and ALL the data from those 10 databases will be synced to a SINGLE master database at the end of the day.

Obviously we cannot use the default primary key (ID) generation in mORMot, because we should avoid duplicated Ids in the master database. So what will be the correct way of avoiding duplicated ID?

One option I can think of is manually assigning record Ids, and each record ID has a unique DB server ID. But I suspect there are simpler solutions? Thanks.

Thanks.

ab · 2015-03-04 18:37:23

This is http://synopse.info/fossil/tktview?name=3453f314d9 feature request AFAIR.
Similar forum thread - with discussion - is http://synopse.info/forum/viewtopic.php … 863#p11863
But not yet implemented.

Since IDs are TID = Int64 now, you can reserve some bits per sub domain.
See http://synopse.info/forum/viewtopic.php?id=2160
Putting the timestamp in IDs may not be a good idea, but using ranges, with per POS low-level bits set, should do the trick.
You can add a TSQLRecord with a locally computed ID by using TSQLRest.Add(..., ForceID=true)

You may also redirect the table to another database - see http://synopse.info/files/html/Synopse% … ml#TITL_93 - and use a single table per POS system.
Each one with its own class type.

So there is no built-in mechanism for this now, if you want to share the same DB, with on demand synchronization on the master DB.
But I guess having the server ID within the record IDs could be the best solution.

edwinsn · 2015-03-05 04:31:57

ab, Thanks for your answer!

edwinsn · 2015-03-06 12:27:40

Hi Arnaud,

For situation like a POS system I described above, a mORMot server usually serves less than 10 PC clients - Do you think it's sufficent to use SQLite (instead of real DB system like MS SQL Server) as the underlying databse in situation like that?

I remember you mentioned other people is using mORMot/SQLite to serve a lot of data and a lot of client PCs, but I couldn't find that info... Thanks.

ab · 2015-03-06 15:17:51

SQLite in the mORMot server is just perfect for this kind of storage.

You may be surprised by the performance and dmsllydisk use.

MORMot will take care of all client server process for you.

edwinsn · 2015-03-06 15:58:49

Great! Thanks!

mORMot Open Source

#1 2015-03-04 18:11:43

How to make sure record Ids are globally unique across multi DB?

#2 2015-03-04 18:37:23

Re: How to make sure record Ids are globally unique across multi DB?

#3 2015-03-05 04:31:57

Re: How to make sure record Ids are globally unique across multi DB?

#4 2015-03-06 12:27:40

Re: How to make sure record Ids are globally unique across multi DB?

#5 2015-03-06 15:17:51

Re: How to make sure record Ids are globally unique across multi DB?

#6 2015-03-06 15:58:49

Re: How to make sure record Ids are globally unique across multi DB?

Board footer