Handling Object Connections

Handling Object Connections

There is a special package VY_CONNECTIONAPI that exposes a set of functions for handling database connections. The most important functions are InsertConnection() and Delete().

InsertConnection

Syntax:

[VINEYARDDB].VY_ConnectionAPI.InsertConnection (
AccountIDINNUMBER,
CreateDateINDATE,
FromObjectTypeINVARCHAR2,
FromObjectIDINNUMBER,
TargetObjectIDINNUMBER,
TargetObjectTypeINVARCHAR2,
ConnectionTypeINNUMBER,
ConnectionIDOUTNUMBER
);

Return type: none

Description: The procedure creates a connection between the "From" and "Target" objects. Note that it checks only for an existing similar connection. If it exists then the function returns and the ConnectionID parameter has the ID of that connection. All other checks should be done by the programmer:

• Direction: Each type of connection has a definite From-Target order (see Object Connections Management in the Overview Vine Server Database chapter). Connections must be saved in correct From-Target order.

• "Limited" connections: The "From" and "Target" ends of a connection type can have count options that can be equal to 1 - only single connection is permitted or 0 - any number of connections are permitted. See also Object Connections Management in the Overview Vine Server Database chapter.

• "Excluding" Collections: If an object is connected to a collection, it should be checked that the corresponding parent collection is not "excluding". If an object is connected to a collection whose parent is an "excluding" collection, then it cannot be connected to other child collections of that collection. For details, refer to the Collection Tree Hierarchy section in the Overview Vine Server Database chapter and the examples below.

NOTE: Connections must not be redirected. The only way to reconnect an object is to delete an existing connection and to create a new one.

Parameters:

AccountID: Normally should be -1.

CreateDate: The date which will be stored into the Createdate field of the new connection.

FromObjectType: The type of the "From" object, such as 'VY_COMPANY'.

FromObjectID: The ID of the "From" object.

TargetObjectID: The ID of the "Target" object.

TargetObjectType: The type of the "Target" object, such as 'VY_PERSON'.

ConnectionType: The type of the new connection.

ConnectionID: The returned ID of the new connection.

Example 1: For this example the server database should include one 'ACME' company and one 'Customer' collection with Contents set to "Company Belongs".

The example creates a connection between the 'ACME' company and the 'Customer' collection. If the type of the parent collection is "excluding" and if there is already a connection of the ACME company to any collection of that type, then that connection is deleted and a new connection created.

set serveroutput on;

declare

CompanyIDnumber;

CompanyNamevarchar2(50):='ACME';

ConnectionIDnumber;

CollectionNamevarchar2(50):='Customer';

CollectionIDnumber;

ParentCollectionIDnumber;

ExcludingFlag     number;

ExcludingConIDnumber;

-- given a company's and a collection's Ids checks for existing connections

-- between the company and child collections of the collection

cursor z(CompanyID number, ParentCollectionID number) is

select id from vineyarddb.vy_connection_view where
fromobjectid=Companyid
and targetobjectid in
(select fromobjectid from vineyarddb.vy_connection_view where targetobjectid = ParentCollectionID and connectiontype = 21 and status='B')
and status='B';

begin

--Get the company ID

select id into CompanyID from vineyarddb.vy_company_view where name = CompanyName and status='B';

--Get the collection ID

select id into CollectionID from vineyarddb.vy_collection_view where name = CollectionName and status='B';

dbms_output.put_line('CollectionID= ' || CollectionID);

--find the parent of the 'Customer' collection

select targetobjectid into ParentCollectionID from vineyarddb.vy_connection_view where fromobjectid = CollectionID and connectiontype = 21

and status = 'B';

--Check whether parent of the Customer collection is excluding

select excluding into ExcludingFlag from vineyarddb.vy_collection_view where id=ParentCollectionID and status='B';

dbms_output.put_line('Excluding= ' || ExcludingFlag);

-- if excluding then find an existing connection to any child collection of the same level with the 'Customer'

if ExcludingFlag = 1 then

open z(CompanyID, ParentCollectionID);

fetch z into ExcludingConID;

if (z%found) then

        dbms_output.put_line('Excluding Connection id= ' || ExcludingConID);

         -- delete that interfering connection

        VINEYARDDB.VY_CONNECTIONAPI.Delete(ExcludingConID);

      end if; 

end if;

--Call InsertConnection

VINEYARDDB.VY_CONNECTIONAPI.InsertConnection(-1,SYSDATE, 'VY_COMPANY', CompanyID, CollectionID,'VY_COLLECTION', 11, ConnectionID);

-- print the connection's ID

dbms_output.put_line('ConnectionID= ' || ConnectionID);

end;

Delete

Syntax:

[VINEYARDDB].VY_ConnectionAPI.Delete (
ConnectionIDINNUMBER,
);

Return type: none

Description: The procedure deletes a connection. Actually, it marks the connection as deleted by changing its status to 'D'.

Parameters:

ConnectionID: The ID of the connection that will be deleted.

Example: See the example for the InsertConnection procedure.