The point is as you say
@Albert Kallal , I try to make a sync routine but I need to know when a record change.
The rownumber is the only field I have to know if something change.
Golly? You have a VERY good point, or shall I say idea!!!
One could use that column to detect if sql server has changed the data. So, I am "somewhat" backpedaling here!
However, we still would need a change detection column on the client (android) side. So, one could for example shove a actual date/time for updates to a record on the android/sqlite side.
However, lets assume we do/did/think that using the change detection column? Well, this would force a whole table compare. That's off my table design (pun intended).
right now, on my napkin design?
We have two distinct issues:
First up, change detection. A whole table (and row by row) compare is too slow. Not going down this road.
So, we adopt a standard of last update on each table (yes, that means a extra column for all tables). This column will be updated automatic on sql server side, and on android side it will be part of any update routine, or a setting in the sqlite table (not 100% sure which option - but a time/date update column is possible in sqlite - just not sure if it automatic).
So, now we ONLY need the last sync time. We ONLY take records updated AFTER this sync time.
Second issue:
PK generation. Call me a surrogate key crazy - but that's my design's. That means all my tables have a auto number PK (and I use "ID" for that PK).
So, how to handle LOCAL additions of data, and not have PK collisions?
I could/would consider a GUID PK - but I have too much existing designs and code written around the assuming that all tables have a autonumer PK.
So, there are several VERY interesting approaches to this problem MS Access + SharePoint uses a VERY cool trick. When off-line, then PK autonumbers start a -1, then -2, then -3 etc. In fact code to check WHEN off line simply has to check for negative PK's!!! (MS Access does this automatic at engine level). When you sync, then the negative keys are sent up to server, and the actual new/real PK's are returned during a sync process.
(and yes, this means I WILL use the relational PK/FK update abilities of sqlite on the android side. )
I experimented with sqlite and starting out with negative PK's (for adding records). Unfortunately, they STILL increment forward. If ANYONE is aware to tricking or setting sqlite to start at -1, then -2 etc. Please jump in - I'll give you a big group hug!!!!
So, you can start at say -100,000, and it increments forward (-99,000 etc.). This approach ALSO has the added benefit of telling that a PK exchange during sync has to occur! (so you kill two birds with one stone!!!).
However if you edit EXISTING data, then the next autonumber is re-set on sqlite/android side. So no go!!!!
Our sync code then can easy deal with:
Added records - exchange with sql server - pull back sql server assigned PK values.
Then you sync updated records (client side).
Then you sync server updated records.
Because my experimenting with sqlite showed "limited" control over the PK increments? Well, then right now the solution is to start all tables PK auto increment values on the sqlite side at 100,000. This will work, and during a sync, we get the PK values returned from sql server - and they are thus below 100,000. Now these days, tables with MORE then 100,000 rows is not all that rare. But hey, baby steps.
And editing data and PK's below 100,000 does not re-set the autonumber increment.
So "V1" of my sync design will assume:
autonumber PK sql server side.
sqlite (android side) - autonumber PK - all PK's start at 100,000 and auto increment forward.
all tables have a last updated column - this is a date + time column. This will allow us to sync ONLY changed records, and thus I want this to work if we have 5,000 rows, or 40,000 rows in a table.
So far, the above design means the least amount of changes to existing tables and software. If someone has a better sync design, I am all ears.
The negative PK autonumber trick is a really nice idea. but that pesky sqlite re-set during edits has kaboshed this design.
So we could consider and use the sql row version column to detect changes to data sql server side, but it would not be time based, and thus a full table scan and compare from both ends would be required. Full table scans are off the table for my design - not considering such a huge bandwidth requirement design unless all other approaches were to fail.
Have to run, but I never really considered the idea of using row version - a VERY neat-o idea on your part. For smaller tables, this is a good idea. But, I am as noted shying away from designs that require touching of all rows for a sync process. I going with last updated as a date/time value.
The other possible? And one that a few commercial android vendors offer? They adopted the .net sync framework, and built a android compatible sync client.
This I have looked at. And it would be a way to build a android to sql server sync system, and also a android to ms-access sync client - two birds with one stone. I don't know how the underlying architecture for the .net sync frame is setup. From what I can tell the .net framework uses an additional database to track the sync process.
but, for now? I going with a simple last sync time, and the assume that each record updated will have a time "later" then the sync time.
And yes, a exchange of the correct new PK value(s) that sql server assigns will be pulled down to the android side.
R
Albert