Synchronization of data in REST tools

The REST service has special flags and methods needed for synchronization of data in tools working with REST (for example used in Sales Meeting Tool).

Flags

account – account ID. On query, insert found objects into VY_ACCOUNT (ACCOUNTID=account, CONNECTIONS=1, SAVED=0) and merge rows into VY_SYNCACCOUNT (ACCOUNTID=account, OBJECTTYPE=objecttype, LASTCHANGEDATE = GREATEST(LASTCHANGEDATE, MAX(CHANGEDATE))), LASTCHANGESCN = GREATEST(LASTCHANGESCN, MAX(SCN)))

syncdate – date of last sync for objects in VY_ACCOUNT set. If object exists in VY_ACCOUNT, query returns it just if it is changed after specified date. Deleted objects from VY_ACCOUNT are also returned. Requires account flag.

saved – flag limiting objects to subset of VY_ACCOUNT with specified SAVED value:

0 – client demanded object but did not confirm that it received it.
1 – client confirmed that it received the object(commit)
-1 – object has to be deleted from the local database

When query applied to VY_CONNECTION, a connection is returned if both connected objects exist in VY_ACCOUNT and one of them has the specified SAVED value. Requires account flag.

VY_PERSON/4?saved=1 – selects committed objects connected to persons with contype=4

VY_CONNECTION?saved=1

$saved – the same as saved but used for parameters inside query.

VY_PERSON([$SAVED]=1)/4([$SAVED]=0)?....

First, selects “committed” persons (confirmed loaded to the local db) and for such persons selects all objects connected with contype=4 and not “committed”(not confirmed loaded to the local database)

nodeName. Name of a database node in multi-node environment. Nodes are registered in the vs_cluster_node table.

scn parameter introduced to support SCN based synchronization. (Please refer to the SCN Sync chapter for more information).
By default, changedate is used for synchronization, but changedate is saved when data changes and not when commited. And if a sync happens while data is already changed but not yet commited then it won’t notice the change as it is not yet commited. But the next sync will think that the change is already in the local db as the previous sync date is later than the change date.

Depending on request type scn could take the following values:

Actual SCN number for COMMIT request, nodeName param is also mandatory.
Sync commit normally uses SCN returned by the first REST query in the current transaction, which confirms that at least all data changes before that SCN have been saved to the client database.
nodeName returned by the first query is also used to check if the nodes did not switch and data can be committed.
scn=load for Sync request. REST uses function Vineyarddb.VintageMainAPI.GetSyncScn to get last syncscn from vy_syncaccount and compares current scn values to find changed objects.
returns all data changed after last syncscn

VY_PERSON?account=….&scn=load

NOTE: Different database nodes can have different scn values, REST returns system SCN & node name in XML returned for each request:

<nodename>DEVOR12C1_DEVMYVIN</nodename>

Object scn is available when accessing object_sync resource, for example:

/vineyard/rest/v1.1/vy_person_sync([id]=[$user])

synccon. This flag is used to synchronize objects connected with new connections or objects connected to new objects. This flag contains connectiontype.

Update VY_ACCOUNT with SAVED = -1 for objects where in VY_TAB_SYNC_VIEW
STATUS=’D’ or SHOWGROUP=-2 (objects to delete from local DB)

Merge into VY_ACCOUNT with SAVED = 0 objects found by query:
select C.TARGETOBJECTID from vineyarddb.vy_connection_view c
join VINEYARDDB.VY_ACCOUNT_VIEW a on A.OBJECTID = C.FROMOBJECTID
where A.ACCOUNTID = account and C.CONNECTIONTYPE = synccon
and (c.CHANGEDATE > syncdate or a.saved = 0)
limited by given conditions
Swap FROMOBJECTID/TARGETOBJECTID depending from direction of the link
(objects to add to local DB – new connections or objects connected to new objects)

Return:
SELECT s.*
FROM vineyarddb.VY_TAB_SYNC_VIEW s
JOIN vineyarddb.VY_ACCOUNT a ON a.accountid = account AND a.objectid = s.id
WHERE a.SAVED < 1 or s.CHANGEDATE > syncdate

Example:

http://vineyard:8080/vineyard/rest/v1.1/VY_COMPANY()?username=lisa&password=demo&account=19484&syncdate=2012-10-16T14:41:43Z&synccon=16

http://vineyard :8080/vineyard/rest/v1.1/VY_EVENT()?username=lisa&password=demo&account=19484&syncdate=2012-10-17T07:37:56Z&saved=1&synccon=0

Queries:

COMMIT : Confirms that client successfully received requested data and saved it to local databse.

Delete from VY_ACCOUNT where SAVED<0

Update VY_ACCOUNT with SAVED=1

Update VY_SYNCACCOUNT with SYNCDATE=syncdate

ROLLBACK – Delete from VY_ACCOUNT where SAVED<1

EXECUTE Allows running commands from VY_CLIENTSQL.
EXECUTE/CommandName?$Parameter1 &$Parameter2&…

EXECUTE/GETSYNCOBJECTTYPES2?$ACCOUNTID=24380&$TABLES=VY_PERSON,VY_COMPANY

Operators:

in – allows to query by the set. Translated into IN clause in SQL.

SCN sync Example

Example of how synchronization implemented in the Sales Calls tool:

Initial login

*** Check that user name and password correct ***

GET USER?username=…&password=…

*** create new record in VY_USERACCOUNT for the new local database***

POST VY_USERACCOUNT

COMMIT

Initial data loading:
*** Load current user person ***

USER?account=…

*** Load needed collections ***

VY_USERSETTING([NAME] in ('VY_MY_CONTACTS_COLLECTION','VY_MY_CUSTOMERS_COLLECTION'))?idField=SETTINGID

VY_COLLECTION(…)?account=…

*** Load metainformation (without account) ***

VY_OBJECT_TABLE?idField=OBJECT_ID

VY_OBJECT_TABLE_FIELD([OBJECTFIELD_HIDDEN]=0 AND [TABLEFIELD_CLIENTFILTER]=0)?idField=OBJECTFIELD_ID

VY_CONNECTIONTYPE

*** Load events connected to current person ***

VY_EVENT([EVENTTYPE] in (-9,-10) AND [REPORTED]=-10 and $[4:[$user]])?account=…

*** Load persons and companies connected to loaded events, connections and persons ***

VY_PERSON?account=…&synccon=10

VY_COMPANY?account=…&synccon=11

VY_COMPANY?account=…&synccon=1

VY_PERSON?account=…&synccon=3

VY_COMPANY?account=…&synccon=16

*** Load questionnaires and questions ***

VY_QUESTIONNAIRE?account=…&synccon=336

VS_QUESTIONNAIRE([DEFAULTQUESTIONNAIRE]=-1 and $[335:[$user]])?account=…&synccon=335

VY_QUESTION?account=…&synccon=339

*** Load all connections between previously loaded objects ***

VY_CONNECTION([CONNECTIONTYPE] in (1,3,4,16,10,11,335,336,339))?saved=*&account=…

COMMIT?account=….&syncdate=…&scn=…&nodeName=…

*** Get phone country code ***

VP_COUNTRY_EX([AL2]='…')/PHONE_COUNTRY_CODE

Sync process:

*** Find all changed tables ***

EXECUTE/GETSYNCOBJECTTYPES2?$TABLES=…&$ACCOUNTID=36322

*** If connections were not changed (no changes in vy_connection) then get updates for all objects already in local database (saved=1) (for each changed table):***

VY_EVENT?saved=1&account=…&scn=load&nodeName=…

…

*** If connections were changed (there are changes in vy_connection) run all already executed queries where synccon IS NOT NULL:***

VY_EVENT([EVENTTYPE] in (-9,-10) AND [REPORTED]=-10 and $[4:[$user]])?account=….&scn=load&nodeName=…&synccon=4

…

VY_PERSON?account=….&scn=load&nodeName=…&synccon=10

…

*** now load all connections for just loaded objects: ***

VY_CONNECTION?account=…&scn=load&nodeName=…

VY_CONNECTION?saved=0&account=…

***in commit use SCN received in first query (vy_event in this sample):***

COMMIT?account=….&syncdate=…&scn=…&nodeName=…

Full resync:

Periodic full resync is done twice a day. Every query with connections run earlier(all executed REST queries are saved in the local db) has to be run again without using SCN to reload all data. This is required to add changed objects which are not yet in the local db. For example, an event not from the local db has changed its type and now has to be added to the local db. (Full resync is similar to initial data loading.)

*** for all executed queries where synccon IS NOT NULL do ***

VY_EVENT([EVENTTYPE] in (-9,-10) AND [REPORTED]=-10 and $[4:[$user]])?account=….

*** and then ***

VY_CONNECTION?account=…&scn=load&nodeName=…

Scn=load is needed in frist connection query to load changed connections

VY_CONNECTION?saved=0&account=…

Note: When query contains internal condition based on the same connection type as synccon (i.e. $[4:[$user]] for synccon=4) synccon parameter is removed from requests without scn parameter.

Articles in this section

Synchronization of data in REST tools