LINK/UNLINK Records

Valentina SQL has commands to Link and Unlink existing records of two tables using a Link.

These comands can be used for a Link of any kind: FOREIGN KEY, ObjectPtr and BinaryLink. However only BinaryLinks require this method. FK and ObjectPtr can link records using regular INSERT / UPDATE syntax - this puts values into Table Fields. But using this new syntax you get common syntax for all kinds of VLinks, and if future you will change kind of link (e.g. from ObjectPtr to BinaryLink), these commands still will be valid.

Note: Link commands can link only existing records of tables. If you specify RecID of non-existing records, you will receive an error.

v_link_records
    :    LINK   RECORD[S] v_list_of_records OF table_name WITH 
         [EACH] RECORD[S] v_list_of_records OF table_name 
         [USING link_name]
         [IF NOT EXISTS] 

v_unlink_records
    :    UNLINK RECORD[S] v_list_of_records OF table_name FROM 
         [EACH] RECORD[S] v_list_of_records OF table_name 
         [USING link_name]
         [IF EXISTS] 

v_list_of_records
    :    ( v_link_value [, v_link_value ...] )
    |    subquery 

v_link_value
    :    UINT
    |    dynamic_parameter_specification 

These commands are needed for Valentina Database Model to allow establish link between two records using Binary Link, or break such link. For other kinds of links as ObjectPtr and ForeignKey there is no need in such command, because they use value of PTR field as fact of link. But for consistency, these commands work for all types of links. This is also good, because if you later change the type of a link between tables, your code that uses LINK/UNLINK commands still will be valid.

By default, the LINK command throws error if you try to link a pair of records that already exist. This behavior is similar to other commands, e.g. when you try create table with name that already exists.

UNLINK command in similar way throws error if you try unlink pair of records that are not linked.

Example:

LINK RECORD(1) OF T1 WITH RECORD(3) OF T2;
LINK RECORD(1) OF T1 WITH RECORD(3) OF T2;        >>> error here: records already linked.

If you want to suppress such error, you can use suffix IF NOT EXISTS. This can be especially useful if you link to a few records.

Example:

LINK RECORD(1) OF T1 WITH RECORD(3) OF T2;
 
// ONLY records 1-2 will be linked. 1-3 pair cause error AND stops execution OF command.
LINK RECORD(1) OF T1 WITH RECORD(2,3,5,8,9) OF T2;        

LINK RECORD(1) OF T1 WITH RECORD(3) OF T2;
 
// ALL pairs will be established. 1-3 already EXISTS, okay, silent skip. 
LINK RECORD(1) OF T1 WITH RECORD(2,3,5,8,9) OF T2 IF NOT EXISTS;  

The following example shows SQL command that allows you to link one record of table Person to one record of table Phone.

LINK RECORD(1) OF Person WITH RECORD(10) OF Phone

If tables Person and Phone have only single VLink between them, then Valentina will use it to link/unlink records. Otherwise, you must explicitly specify what link to use to avoid error message about ambiguity:

LINK RECORD (1) OF Person WITH RECORD (10) OF Phone USING PersonHasPone

This is example of UNLINK commands that correspond to LINK commands in the previous example. Future in this article we may give examples with LINK commands only to save space.

UNLINK RECORD (1) OF Person FROM RECORD (10) OF Phone

You can link N records of the Left Table with N records of the Right Table with a single SQL command. For this you can just specify several RecIDs in the list.

LINK RECORDS (1,2,3) OF Person WITH RECORDS (24,98,16) OF Phones
 
This command will produce 3 pairs OF records: 
  * (1,24), (2,98), (3,16).

Note that in this command we use plural form RECORDS, although this is just for nice reading. SQL Parser will accept both RECORD and RECORDS without complains.

Although link is 1:M, in most cases in real life we still link at once only single pair of objects, exactly as described above for Link 1:1.

LINK RECORD(1) OF Person WITH RECORD(10) OF Phone

To link few pairs using a single SQL command you can specify this explicitly using lists of the same length on the left and right sides:

LINK RECORD (1,1,1,1,1) OF Person WITH RECORD (10,11,12,13,14) OF Phone
 
This command will produce 5 links:
  => (1,10), (1,11), (1,12), (1,13), (1,14).

You can specify even different records in the left list, but still important to have lists of the same length:

LINK RECORD (1,1,1,1,1,2,3) OF Person WITH RECORD (10,11,12,13,14,15,16) OF Phone
 
This command will produce 7 links:
  => (1,10), (1,11), (1,12), (1,13), (1,14) AND (2,15) AND (3,16)

Using word EACH you can get more natural syntax to link ONE record of the Left table to MANY records of the RIGHT table for case when only one record on the left side:

LINK RECORD (1) OF Person WITH EACH RECORD (10,11,12,13,14) OF Phone
 
This command will produce 5 links:
  => (1,10), (1,11), (1,12), (1,13), (1,14).

• If you forget to specify EACH and lists have different length, then you get error.
• It is obvious that for 1:M link command with EACH keyword can contain only one RecID in the left list.

This command gives us the natural way to unlink ONE record of the Left table from MANY records of the RIGHT table.

UNLINK RECORD(1) OF Person FROM EACH RECORD(10,11,12,13,14) OF Phone

In the v4.7 was added ability to unlink a record from all linked records. Example below shows few forms of the same command:

UNLINK RECORD (1) OF Person FROM EACH RECORD OF Phone
UNLINK RECORD (1) OF Person FROM RECORDS OF Phone

UPDATE tblPhone SET fldPersonPtr = NULL 
WHERE fldPersonPtr = 1

or even for few records at once:

UNLINK RECORDS (1,3,5) OF Person FROM EACH RECORD OF Phone
UNLINK RECORDS (1,3,5) OF Person FROM RECORDS OF Phone

UPDATE tblPhone SET fldPersonPtr = NULL 
WHERE fldPersonPtr IN (1,3,5)

In v5.0 we have even more extend this feature. Now you can do

UNLINK RECORD (1) OF Person FROM EACH RECORD
UNLINK RECORD (1) OF Person FROM RECORDS

to unlink record RecID=1 of Person table from all records linked to it in all tables linked to Person table.

All the commands work with RecID values of linked Tables. You may want to link some records using values of table fields. You can do this using form of LINK/UNLINK with sub-query.

-- This command will link all employers with sales lower 10000 
-- to Manager with recid = 1.
LINK RECORD (1) OF Manager 
WITH RECORDS (SELECT recid FROM Employers WHERE SalesTotal < 10000) OF Employers

You can also specify a sub-query for LEFT table also.

-- This command will link all employers with sales lower 10000 to TopManager.
LINK RECORDS (SELECT recid FROM Manager WHERE POSITION = 'top') OF Manager
WITH RECORDS (SELECT recid FROM Employers WHERE SalesTotal < 10000) OF Employers

Note that subquery should SELECT only one field that produce RecID values. Query of any other kind will generate error message.

For M:M Link you can do all the same as above with 1:1 and 1:M links plus:

With M:M link you can specify few records on the left side using EACH keyword. In this case each record from the left list, will be linked to each record in the right list.

LINK RECORD (1,2) OF Person WITH EACH RECORD (10,11,12,13,14) OF Phone
 
This command will produce 10 links: 
  * (1,10), (1,11), (1,12), (1,13), (1,14).
  * (2,10), (2,11), (2,12), (2,13), (2,14).

Example of EACH usage:

LINK RECORDS (SELECT recid FROM Manager WHERE POSITION = 'top') OF Manager
WITH EACH RECORD (SELECT recid FROM Employers WHERE SalesTotal < 10000) OF Employers

Starting from 3.1 version of Valentina engine you can use SQL Binding for Link/Unlink commands.

Example:

query = "LINK RECORD (:1) OF Person WITH RECORD(:2) OF Phone"
db.SqlExecute( query, [25, 687] )

Benefit is that query string now have the same form (comparing byte to byte) and SQL Parser is able to reuse parsed tree from the pool of queries. This increase speed because time of parsing become zero.

Let we have record 10 of Person linked to record 8 of Phone. Let we want now UPDATE this link-pair, so record 10 of Person will be linked to e.g. 56. This operation can be done with help of UNLINK then LINK:

UNLINK RECORD (10) OF Person FROM RECORD (8) OF Phone;
LINK   RECORD (10) OF Person TO RECORD (56) OF Phone;

Instead of this, we can made single SQL call: (NOT IMPLEMENTED!)

LINK RECORD (10) OF Person TO RECORD (56) OF Phone INSTEAD OF (8);