IMO, a generic sync process would be:
First stage : upload
1. client fetch local changes occured since last sync
2. client send the changes to server
3. server apply the changes and generate conflict report
4. client mark the upload success or process the conflict report
5. if no conflict, goto second stage
Second stage : download
1. client fetch remote changes occured since last sync
2. server send the changes to client
3. client apply the changes and generate conflict report
4. client mark the download success or process the conflict report
For each committed transaction, if any changes occured, Envers
will generate a revision record in database.
When upload, if no conflicts or errors, a client id should be
persisted on server with a new server revision number. So the
persisted revision info should including:
revisionNumber
clientId
When download, if no conflicts or errors, the server revision
number should be persisted on client with a new client revision
number. So the persisted revision info should including:
revisionNumber
serverRevisionNumber
To select the changes from client :
{code:title=Select last sync rev}
////////////////////////////////////////
// Select last sync rev:
////////////////////////////////////////
SELECT MAX(ri.revisionNumber) FROM revinfo ri
WHERE ri.serverRevisionNumber IS NOT NULL
////////////////////////////////////////
{code}
////////////////////////////////////////
// Select incremental Inserts:
////////////////////////////////////////
SELECT e FROM Entity e
WHERE e.id IN (
SELECT er.originalId.id FROM AuditEntity er
WHERE er.REVTYPE = ADD
AND er.originalId.REV.id > :lastSyncRev
)
////////////////////////////////////////
////////////////////////////////////////
// Select incremental Updates:
////////////////////////////////////////
SELECT e FROM Entity e
WHERE e.id IN (
SELECT er.originalId.id FROM AuditEntity er
WHERE er.REVTYPE = MOD
AND er.originalId.REV.id > :lastSyncRev
) AND e.id NOT IN(
SELECT er.originalId.id FROM AuditEntity er
WHERE er.REVTYPE = ADD
AND er.originalId.REV.id > :lastSyncRev
)
////////////////////////////////////////
////////////////////////////////////////
// Select incremental Deletes:
////////////////////////////////////////
SELECT er FROM AuditEntity er
WHERE er.REVEND = (
SELECT ier.originalId.REV.id FROM AuditEntity ier
WHERE ier.REVTYPE = DEL
AND ier.originalId.REV.id > :lastSyncRev
)
////////////////////////////////////////
To select the changes from server :
////////////////////////////////////////
// Select last sync rev:
////////////////////////////////////////
SELECT MAX(ri.revisionNumber) FROM revinfo ri
WHERE ri.clientId = :clientId
////////////////////////////////////////
////////////////////////////////////////
// Select max sync rev:
////////////////////////////////////////
SELECT MAX(ri.revisionNumber) FROM revinfo ri
WHERE ri.clientId != :clientId OR
ri.clientId IS NULL
////////////////////////////////////////
////////////////////////////////////////
// Select incremental Inserts:
////////////////////////////////////////
SELECT e FROM Entity e
WHERE e.id IN (
SELECT er.originalId.id FROM AuditEntity er
WHERE er.REVTYPE = ADD
AND er.originalId.REV.id > :lastSyncRev
AND er.originalId.REV.id <= maxSyncRev
AND (
er.originalId.REV.clientId != :clientId
OR er.originalId.REV.clientId IS NULL
)
)
////////////////////////////////////////
////////////////////////////////////////
// Select incremental Updates:
////////////////////////////////////////
SELECT e FROM Entity e
WHERE e.id IN (
SELECT er.originalId.id FROM AuditEntity er
WHERE er.REVTYPE = MOD
AND er.originalId.REV.id > :lastSyncRev
AND er.originalId.REV.id <= maxSyncRev
AND (
er.originalId.REV.clientId != :clientId
OR er.originalId.REV.clientId IS NULL
)
) AND e.id NOT IN(
SELECT er.originalId.id FROM AuditEntity er
WHERE er.REVTYPE = ADD
AND er.originalId.REV.id > :lastSyncRev
AND er.originalId.REV.id <= maxSyncRev
AND (
er.originalId.REV.clientId != :clientId
OR er.originalId.REV.clientId IS NULL
)
)
////////////////////////////////////////
////////////////////////////////////////
// Select incremental Deletes:
////////////////////////////////////////
SELECT er FROM AuditEntity er
WHERE er.REVEND = (
SELECT ier.originalId.REV.id FROM AuditEntity ier
WHERE ier.REVTYPE = DEL
AND ier.originalId.REV.id > :lastSyncRev
AND ier.originalId.REV.id <= maxSyncRev
AND (
ier.originalId.REV.clientId != :clientId
OR ier.originalId.REV.clientId IS NULL
)
)
////////////////////////////////////////
|
