HTML5 App Database Syncing

Question

I currently am working on a project that involves storing data in an HTML5 SQL-Lite Database. Currently, I have a schema as follows (4 Tables):

TransData:
-----------------------------------------------------------
|   TID   | UserName | TransColor | ... |  Date  |  Note  |
-----------------------------------------------------------
|    6    | Brendan  |      Red   | ... |        |        |
-----------------------------------------------------------
|    7    | Brendan  |      Red   | ... |        |     1  |
-----------------------------------------------------------

FullData:
-----------------------------------------------------------
|   TID   | UserName | TransColor | ... |  Date  |  Note  |
-----------------------------------------------------------
|    1    | Brendan  |      Red   | ... |        |  Start |
-----------------------------------------------------------
|    ...  | Brendan  |      Red   | ... |        |        |
-----------------------------------------------------------
|   40    | Brendan  |      Red   | ... |        |  End   |
-----------------------------------------------------------

SalamanderData:
----------------------------------------------------
|   SID   | SalamanderName | Length | ... |  TID   |
----------------------------------------------------
|    1    | Northern-Slimy |   16   | ... |    6   |
----------------------------------------------------
|    2    |   Two-Lined    |   26   | ... |    6   |
----------------------------------------------------  
|    3    |   Two-Lined    |   12   | ... |    7   |
----------------------------------------------------  

SalamanderData:
----------------------------------------------------
|   SID   | SalamanderName | Length | ... |  TID   |
----------------------------------------------------
|    1    | Northern-Slimy |   16   | ... |    6   |       
----------------------------------------------------
|    2    |   Two-Lined    |   26   | ... |    6   |   
---------------------------------------------------- 
|    3    |   Two-Lined    |   12   | ... |    7   |
----------------------------------------------------

Note: The "Note" column in TransData is used to point to the beginning data point of a collection in the FullData field.

The database between my App and the server SHOULD NOT BE IN SYNC. I am merely trying to dump all of these tables into the database on the server (and by dump I mean, update the references to other tables, and then insert into the server database).

I was going to use MAX(TID-Server) + TID-App = new TID-Server, and cascade the updates down the tables.

How would you go about doing this?

Can anyone help? I can't figure out how to write an insertion query that would first cascade updates to the inserted items. — Brendan Lesniak, Sep 06 '12 at 02:19
You show SalamanderData twice. If I understand your question correctly, you have 4 tables "client side" with a table schema similar to what's "server side". The tables are not and will not be in sync, but occasionally you need to send the contents of all 4 "client side" tables to the server where they'll be updated or inserted as appropriate. The big challenge is the primary keys, which will of course not be in synch (or worse may be duplicated). Is this close? — Dan Pichelman, Sep 06 '12 at 18:13
That's exactly it. And yes, I accidentally copied my SalamanderData table twice; that is supposed to be a BugData table, which shows counts for various bugs such as centipedes and millipedes. I need to update the primary keys on insertion so that they are synced to the table and each other. — Brendan Lesniak, Sep 06 '12 at 18:25

score 6 · Accepted Answer · edited May 23 '17 at 12:16

From the comment by Dan Pichelman, the problem is that the client inserts records into the local DB and, to do so, has to determine primary keys for them. But, given all the different clients doing the same, the new PKs will clash when they hit the server.

This is a common problem is systems which are physically disconnected (at least sometimes) or where there cannot be a single point of failure such as in a shared sequence generator.

Some common solutions are:

GUID

Here the PK is a 128-bit (or larger) random number. The chance that any two PKs are the same is exceedingly small. But to reduce the change of collision even more, the GUID algorithm includes seeding with unique machine identifiers (the network MAC) and time. Two GUIDs produced on the same machine will never collide, and neither will GUIDs produced on machines with different MACs. Most machines and languages have native functions to generate GUIDs, but JavaScript doesn't. See:

A partition naming scheme

In this scheme, the PK is again a large number (bitfield really), and you partition it into a hierarchical manner. A good example is the international telephone system (at least before portable numbers). Here the telephone number is divided into:

Country code: eg USA - 1
Area code: eg Sunnyvale - 615
Subscriber number, controlled by the exchange.

In your case, you might partition the number by:

User login (eg a number unique to each user)
Session id (eg a number unique to each logon per user, to distinguish between different sessions of the same user but on different browsers/computers)
Serial number

Combining all three you will have a guaranteed unique PK.

PK 'license' Server

The first two suggestions have the merit that they work completely disconnected. If you have a connected client, you could have a web service that supplies PKs when the client requests them.

For efficiency, it might return a batch of, say, 100 numbers. This might even be returned when the user logs on.

The client can use them all and request for more. There might be cases where the client forgets state and leaves a 'hole' in the global PK sequence. This would almost certainly not be a concern.

Some considerations

Sometimes you might like sequential PKs for table ordering purposes. In which case, do you need to order by client or by time of creation? If either is important, you might rate the partition naming scheme higher. Put the client or time as the first partition as appropriate. Alternatively, add more columns to your table.

If you don't want the fixed structure of the partition naming scheme, the GUID will work well.

If you want central co-ordination, use the PK license server.

score 0 · Answer 2 · answered Sep 06 '12 at 18:35

I don't know an aesthetically pleasing way to do this, but I "solved" it by writing stored procedures:

The first table is easy - update or insert as needed. If doing an insert, obtain the primary key of the newly inserted record then process the dependent tables accordingly (which usually means inserting data with the new primary key). Repeat as needed as you walk through the relationships. For 4 tables you should be OK, but I'd hate to do it for 40.

In my case it was messy and involved temporary lookup tables with oldPK and newPK in them.

It was also a fairly lengthy & tedious bit of code whose only redeeming feature is that it works.

score 0 · Answer 3 · answered Sep 12 '12 at 18:23

This goes a little against what you've asked for, so just comment if this is totally out of line and I'll remove it. But you did not specify any particular reason for why the server/client cannot be in sync regarding the Primary Key (which in my opinion is really the issue here).

I'm also making the assumption (from your data, and your question) that we are talking about a webapp with user created content (like a log) that occasionally gets uploaded to the server.

So just for sports, have you considered using a primary key that is built by several fields? That way you could have auto incrementation in your local database, dump the data to the server and it wouldn't collide with other users data. An example for the SalamanderData table would be something in the line of:

CREATE TABLE SalamanderData
(
    SID int NOT NULL,
    SalamanderName varchar(255),
    Length int,
    ... ...,
    TID int NOT NULL,
    CONSTRAINT pk_SDataEntry PRIMARY KEY (SID,TID)
)

Which then would create an PK out of SID and TID.

Reading through my post and checking the other answers, I realize that this is what Andrew has suggested, so even if this helps you, you should really accept his answer

keeping the answer for the purpose of possibly clarifying the solution and to give a code example

HTML5 App Database Syncing

3 Answers3