Database Structure

Database Structure

Tables: VY_CLIENTSQL, VY_HOLIDAY, VY_ICON, VY_OBJECT, VY_OBJECTFIELD, VY_PACKAGEVERSION, VY_PLUGIN, VY_TABLE, VY_TABLEFIELD, VY_WINDOW

The Vine database's structure is logically organized into Vine structure object sets that can be separately distributed between different user groups which use the same Vine server. Physically the database structure is maintained by a number of tables in accordance with the following scheme:

Figure 10. Database structure tables

Structure of an Object Set

An object set contains a number of objects and SQL stored procedures (SQL API) specifically adjusted to work with the objects of the set. The connection types and plug-ins do not belong to any set and you can access them separately from any set. The Vine database has a default API set, which is always locked and cannot be changed in any way.

An object is stored in a database table, but one table can store various objects from different sets. Usually these objects are similar but not identical, i.e. some of them include table fields that are not included by others.

Objects and object set relations are maintained by:

• A number of entries in the structure tables of the Vine Server; it is a number of entries in those tables that ties together the data dispersed among database tables into a single logical unit - an object set.

• Table views. Each object table has a corresponding view, such as VY_SALESCASE_VIEW. Note that there is only one view for a table. When custom fields are added to the table for a new custom object then these new fields are added to the object's view.

• Object SQL packages. Each object from each set has its own SQL package. The VINEYARDMGRIMPORT package is updated whenever any database table changes.

Table 5. Specific fields of VY_PACKAGE_VERSION

Column Name	Data Type		Description
...	...	...	Generic fields are skipped.
SHOWGROUP	Number	M	Defines which group can use the set. A zero means that any group can use the set; The ID of a user group means that users from only that group can use the set; -3 specifies that no user groups can use the set.
NAME	VARCHAR2(50)	M	Name of the set
Locked	Number(1)		0 = not locked 1 = locked. The API set is always locked regardless of its LOCKED setting. None of its tables or table fields can be deleted or altered in any way.

Each entry (row) in the VY_PACKAGE_VERSION table corresponds to a separate Vine object set. Besides the default API set, additional sets can be created using Vine Object Manager. Object Manager permits either creating a new set directly in the database or exporting it to a file, which can be later imported to any Vine database.

The Vine server is able to hold several sets that can be assigned to different user groups.

Note: A user can have access to several object sets and some of those can include the same object type. In this case the user gets the object type with the lowest id.

Table 6. Specific fields of VY_CLIENTSQL

Column Name	Data Type		Description
...	...	...	Generic fields are skipped.
NAME	VARCHAR2(50)		The name of an SQL command, e.g. “SAVE_VY_COMPANY"
PACKAGENAME	VARCHAR2(50)		The name of a Vine object set. New sets can be created by Vine Object Manager
SqlStr	VARCHAR2(2000)		SQL clause

The VY_CLIENTSQL table contains SQL clauses that can be executed by Vine clients.

Most of SQL commands stored in this table are just interfaces for SQL procedures stored in the server database, though in some of them (for instance the SYNCTOCLIENT_VY_PLUGIN command) whole SELECT commands are stored.

In order to use an SQL command Vine for Windows retrieves its text, binds actual values to input parameters (parameters formatted as "@?id_") and executes the resulting SQL clause. Note that if the SQL command executes a stored procedure then it should set proper output parameters, formatted as ":id_", of the same data type as that of the stored procedure.

Vine for Windows stores command results in a table-like memory structure with column names specified by fields located at the end of the command which are formatted as "@editgroup_". Here "editgroup" will be the name of a corresponding column.

For instance:

"GET_VY_COMPANY"

"begin

VINEYARDDB.VY_COMPANYAPI.Get(@?id_,@?AccountID,

:id_,:status_,:createDate_,:createUser_,:changeDate_,:changeUser_,:showgroup_,:editgroup_,:name_,:realname_,:location_,:phone_,:fax_,:email_,:WWWPage_,:address_,:zip_,:city_,:state_,:streetAddress_,:country_,:Description_);

end;

@id_ @status_ @createDate_ @createUser_ @changeDate_ @changeUser_ @showgroup_ @editgroup_ @name_ @realname_ @location_ @phone_ @fax_ @email_ @WWWPage_ @address_ @zip_ @city_ @state_ @streetAddress_ @country_ @Description_

"

Here:

@?id_,@?AccountID - are command input parameters and also the input parameters of the GET procedure from the VY_COMPANYAPI stored package.

:id_,:status_,...,:state_,:streetAddress_,:country_,:Description_ - output parameters of the GET stored procedure.

@id_ @status_ ... @streetAddress_ @country_ @Description_ - the names of the command output columns.