Articles in this section

See more

Standard Objects

Vine-Tuki
Follow

Standard Objects and Fields

Tables:  VY_COMPANY, VY_PERSON, VY_PROJECT, VY_EVENT, VY_COLLECTION, VY_RESOURCE, VY_DOCUMENT, VY_URL, VY_MAP, VY_SALESCASE, VS_SALESACCOUNT, VS_SERVICEREQUEST, VS_KNOWLEDGEBASE

Each standard Vine object type has a corresponding database table - an object table. An object's table contains records (rows) which represent Vine objects of the same type.

For convenience, the fields of the standard objects can be roughly divided into three groups:

  • Object Specific fields, i.e. fields which carry information specific to an object's type. As a rule, these fields can be directly edited by the user.
  • Generic fields, i.e. special fields which must be present in any Vine database object: ID, STATUS, CREATEDATE, CREATEUSER, CHANGEDATE, CHANGEUSER, COLOR, OBJECTIMAGE.
  • Protection fields. These are the SHOWGROUP and EDITGROUP fields. It is convenient to distinguish these fields from the other two types of fields.
    In Vine database 4.3 the following protection fields have been added:
  • DELETEGROUP – Only members of the specified group can delete this object.
  • ADDCONNECTIONGROUP - Only members of the specified group can add connections to this object.
  • DELETECONNECTIONGROUP – Only members of the specified group can delete connections of this object.

Object Specific Fields

Object specific fields include two fields identical for all objects. These are the "Name" and "Description" fields. Companies and persons have two name fields:

  • Companies: “NAME" and “REALNAME”
  • Persons: “LASTNAME" and "FIRSTNAME" fields.

The "Name" fields are absolutely required for any object.

Examples of object specific fields:

  • for a Company - Name, Location, Phone, Fax, Description and so on;
  • for a Person - LastName, FirstName, Title, Department, and so on;
  • for a Project  - Name, StartDate, ProjectLength and so on.

NOTE: Do not confuse the database table fields with the object fields shown in the user interface of the client applications, e.g. the user sees the contents of the NAME field of the VY_COMPANY table as "Use Name" and the REALNAME field as "Name".

Generic Fields

The following generic fields are present in every object table:

Table 3. Generic fields of standard objects

Column Name

Data Type

 

Description

ID

NUMBER

M

Unique numeric identifier

STATUS

CHAR(1)

M

Status of the object. Normally it is 'B'; it changes to 'D' when the user deletes the object.

Events can have special status:

  • “S” – used in web Active Sync synchronization to represent a series of repeating events and it should not be made visible in Vine clients.
  • “H” - History, left to the database, but not want to see it in user interface any more

CREATEDATE

DATE

R

Timestamp for the creation of the object. By default = SYSDATE

CREATEUSER

VARCHAR2(32)

R

The name (in uppercase) of the user who has created the object.

CHANGEDATE

DATE

M

Timestamp for the last modification of the object. By default = SYSDATE

CHANGEUSER

VARCHAR2(32)

R

The name (in uppercase) of the user who last saved the object.

OBJECTIMAGE

BLOB

 

To store an image.

XMLDATA

XMLTYPE

 

  • allows storing extra data without creating new database fields
  • Synchronized to local Vine database
  • XML fields should be converted to nvarchar with to_nchar-function before retrieving to client

Note: The top level element must be <ROOT> and all data must be inside it. (to prevent multiple roots added by different tools)

LOCKEDFIELDS

NVARCHAR2(1000)

 

Comma separated list of fields which special triggers do not allow to change. LOCKEDFIELDSGROUP must be set for this to work.

LOCKEDFIELDSGROUP

NUMBER(10)

 

Group of users who can change the fields in LOCKEDFIELDS. No fields are locked if LOCKEDFIELDSGROUP is empty.

ACTIVEUNTIL

DATE

 

this field should be used in all tools to filter non-active objects out from regular usage

COLOR

NUMBER

 

Used to set object’s color attribute. Vine tools such as Calendar, can check this attribute to apply color formatting to the object.

COLOR Field

Color Attribute

Format: OLE color (ABGR). It is stored in the database as a 32 bit unsigned integer.

  • To convert a color value stored in the database to the .NET color format ( Color structure), use:

ColorTranslator.FromOle(oleColor);

  • To convert the color format in .NET to the value to be stored in the database, use:

ColorTranslator.ToOle(Color);

Standard Vine Colors

There is a number of standard colors used in Vine and selectable in Vine Web Administrator’s console:

Color

RGB code

Yellow

251, 226, 20

Red

255, 76, 86

Orange

251, 173, 25

Green

155, 207, 36

Magenta

234, 110, 173

Blue

45, 168, 225

Gray

153, 159, 157

Parameter Based Color Codes

Objects can have parameters which can be assigned color codes. Note that the selected parameter’s color code will be copied to the object when it is saved. The object color will not automatically change if the corresponding parameter color code changes. The object has to be saved to change its color code.

Object parameter color codes can be set in Web Admin console:

Protection Fields

The protection fields are used to protect an object and its connections from unauthorized access. For example, an object can be assigned a specific "show group" or "edit group". Only users included in these groups can view or edit the object, respectively.

Note: The user which is specified in an object’s CHANGEUSER field always has full access to the object and its connections.

Table 4. Protection fields of standard objects

Column Name

Data Type

 

Description

SHOWGROUP

NUMBER

M

The object's visibility setting. It can be:

- 0 (zero), means that any user can see the object;

- ID of a user or a user group that can see the object.

- -3 means that object is visible to all users/persons connected to the object

- 5, means that no user can access the object;

In the object's view -1 is returned when the ID of the current database user is equal to the object's showgroup ID.

EDITGROUP

NUMBER

M

The object's editing setting. It can be:

- 0 (zero), means that any user can edit the object;

- ID of a user or a user group that can edit the object.

- -3 means that object can be edited by all users/persons connected to the object.

- 5, means that no user can access the object;

In the object's view -1 is returned when the ID of the current database user is equal to the object's editgroup ID.
–2 is returned when the user has no right to edit the object.

DELETEGROUP

NUMBER

M

Only members of the specified group can delete this object.

It can be:

- 0 (zero), means that any user can delete the object;

- ID of a user or a user group that can delete the object.

- -3 means that object can be deleted by all users/persons connected to the object.

- 5, means that no user can access the object;

In the object's view -1 is returned when the ID of the current database user is equal to the object's editgroup ID.
–2 is returned when the user has no right to delete the object.

ADDCONNECTIONGROUP

NUMBER

M

Only members of the specified group can add connections to this object.

It can be:

- 0 (zero), means that any user can add connections;

- ID of a user or a user group that can add connections.

- -3 means that new connections to the object can be added only by users/persons connected to the object (this field needs to be set first otherwise there is no point to use other connection protection settings).

In the object's view -1 is returned when the ID of the current database user is equal to the object's editgroup ID.
–2 is returned when the user has no right to edit the object.

DELETECONNECTIONGROUP

NUMBER

M

Only members of the specified group can delete connections of this object.

It can be:

- 0 (zero), means that any user can delete connections;

- ID of a user or a user group that can delete connections.

- -3 means that connections can be deleted by connected users/persons.

In the object's view -1 is returned when the ID of the current database user is equal to the object's editgroup ID.
–2 is returned when the user has no right to edit the object.

For example, when editing an object, the user can limit access to the object by choosing a desired setting from a protection menu (the Visibility or Editing menu). In accordance with the user choice the following values can be saved into these fields:

  • A zero value is saved when the user chooses All from a protection menu.
  • The User ID is saved when the user chooses Self from a protection menu.
  • The Group ID is saved when the user chooses a group name from a protection menu.
  • -3 can be saved to protect object connections.
  • - 5, means that no user can access the object. Currently used in the deletegroup field of user persons which means that it is not accessible to anyone and that is why cannot be deleted. When the user is removed the person's deletegroup is set to "0"

A zero value of the SHOWGROUP or EDITGROUP field corresponds to the 'ALL' setting in Vine. -1 value (returned in the views) corresponds to the 'Self' setting.

When a Vine user (who is normally granted the SELECT privilege only on object views) tries to see an object's data through its view; the SELECT command with the following structure is executed:

SELECT id, …

VYUtilApi.DBToClientGroupID(showgroup, changeUser, 'VY_EVENT', id) showgroup,

VYUtilApi.DBToClientGroupID(editgroup, changeUser, 'VY_EVENT', id) editgroup

…,

FROM VY_EVENT /*or any other object*/
WHERE showGroup = 0

or VintageUtilAPI.IsUserInGroup(showgroup, changeUser, id, 'VY_EVENT') = 1

It means that:

    Only those objects will be selected in which ShowGroup values have a zero value (everybody has access) or if the current user is in the group specified by the ShowGroup value.

    The protection field values shown to the user will be returned by the VYUtilApi.DBToClientGroupID function.

For example,

VYUtilApi.DBToClientGroupID(editgroup, changeUser, 'VY_EVENT', id) editgroup

If the EditGroup value is equal to the current user ID then -1 will be returned, otherwise it will be checked if the current user is in the group specified by the EditGroup and, if yes, the EditGroup value will be returned. A special case is when EditGroup is equal to -3. In that case, the function will check whether the user is connected to the object already or is the object’s current CHANGEUSER. In this case the function will return -3.

If all the checks fail then -2 will be returned, which indicates that the user has no right to edit the object or its connections.

Comments

0 comments

Please sign in to leave a comment.