Account Management

Account Management

Tables: VY_ACCOUNT, VY_SYNCACCOUNT, VY_USERACCOUNT, VY_UTILACCOUNT

The account tables are used by the Vine Server to synchronize data between the server database and a local Vine for Windows database. With these tables it is possible to find out which objects are included in the user local database and, hence, which objects need to be synchronized.

SCN Sync

A new model of synchronization is based on Oracle’s SCN (system change number). (The old sync model will be still supported for compatibility for some time):

• A new table VY_SCN added. It includes SCN field for all synced tables.

• When data is changed in the db, a trigger (SIGNAL_VY_”object”) adds or modifies the record for this data (SCN field for this data is set to -1 for update and to 0 for insert) and alerts the UPDATE_SCN job.

• After alert signal arrives (and hence “commit” happens), the UPDATE_SCN job assigns ORA_ROWSCN to all VY_SCN fields with 0 or -1 .

• VY_SCN trigger modifies VY_SYNCACCOUNT and stores SCN there.

VY_USERACCOUNT

The VY_USERACCOUNT table keeps relations between Vine for Windows users and the user accounts, i.e. IDs of Vine for Windows local databases. Each local database file located on the user computer has one row entry in this table.

Column Name	Data Type		Description
ID	NUMBER	M	User account ID, i.e. the ID of a Vine for Windows local database.
NAME	NVARCHAR2(50)	M	User name.
STATUS	CHAR	M	Status of the entry: 'B' = normal status, ‘D’ = marked as deleted.
USERID	NUMBER	M	User ID, i.e. the ID of the corresponding Vine person.
SESSIONID	NUMBER	M	The number of Vine for Windows sessions run by the user up to the current moment.
LastSyncSCN	NUMBER(10)		The last SCN value for this local db (Added so that for any account it would be possible to detect the last sync time even if data in vy_syncaccount was removed in result of access rights change for the full resynchronization)
LastSyncDate	DATE		Time of the last sync (same as SCN)

VY_ACCOUNT

The VY_ACCOUNT table stores IDs of all objects included in local databases. Objects from a particular local database can be identified using the ACCOUNTID column, which stores the IDs of the local databases.

Column Name	Data Type		Description
ACCOUNTID	NUMBER	M	User account ID, i.e. the ID of a Vine for Windows local database.
OBJECTID	NUMBER	M	Object ID, i.e. the ID of a Vine for Windows object.
CONNECTIONS	NUMBER	M	This flag specifies whether the connections for the object should also be synchronized to the client.
SAVED	NUMBER	M	This flag is used by Sales Meeting Tool when syncing its local database with the server database 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
ID	NUMBER	M	Used for replication

VY_SYNCACCOUNT

The VY_SYNCACCOUNT table stores the last sync info for each table from user accounts.

Column Name	Data Type		Description
ACCOUNTID	NUMBER	M	User account, i.e. the ID of a Vine for Windows local database.
OBJECTTYPE	NVARCHAR2(20)	M	Table name, like VY_CONNECTIONTYPE, VY_PERSON, VY_ICON, VY_OBJECT, etc.
SYNCDATE	DATE	R	The date of the last synchronization of the table.
LASTCHANGEDATE	DATE	R	It’s the last change of any object that should be synced to current user
SYNCSCN	NUMBER	R	Now used in scn synchronization instead of syncdate, lastchangedate
LASTCHANGESCN	NUMBER	R	Now used in scn synchronization instead of syncdate, lastchangedate

VY_SCN

Column Name	Data Type		Description
ID	NUMBER	M	row id in objecttype table
OBJECTTYPE	NVARCHAR2(20)	M	table name
SCN	DATE		first set to 0(insert) or -1(update) by the trigger then number generated by UPDATE_SCN job
CHANGEDATE	DATE	M	date of the change

The VY_UTILACCOUNT table keeps record of all rows from utility Vine tables (vy_plugin, vy_window, vy_table, vy_clientsql, etc.) used by each user account.