Link Refactoring Overview

Sometimes you may need to change your database structure (schema). This section describes possible changes of the database schema related to changes of Links.

We name this section “Link Refactoring” just to use a modern term. Refactoring means - CHANGES of code that do not change its functionality but improve its quality. Besides “Link Refactoring” is a more short-term than “Database Structure Changes of Link”.

One of the main reasons to do a link refactoring can be the wish to change from RELATIONAL model and its RBD-link based on Foreign Key and model “link by value” to Valentina's links - such as ObjectPtr or BinaryLink. Such relational database design you can get if you migrate some database from MSSQL, Accesss, mySQL, …

The following table shows the most interesting (and often used) schemas with links and ways to transform between them.

Legend

• S - means “SAME”, i.e. kind of link is not changed on refactoring.
• C - means “CHANGE”, i.e. kind of link is changed, e.g. ObjectPtr link becomes BinaryLink.

• FK - Foreign Key
• OP - ObjectPtr
• BL - Binary Link

• “=” - means that power of link is not changed.
• Array Up - means that power of link go up, e.g. it was 1:1 and becomes 1:M or even M:M.
• Array Down - means that power of link go down, e.g. it was 1:M and becomes 1:1.

• [FK M:M] or [OP M:M] means classic design when a third table with two fields is used to link tables T1 and T2 as M:M.

• You can see that exists 9 * 9 - 9 = 72 main ways to transform link of one kind to another. Some transformations change only power of link up or down but not affect its kind. Other transformations can change only kind but not change power of link. And third set of transformations change both kind and power of a link.

• You can note that exists squeres 3*3 that have the same repeated pictures, e.g. see squere 1.1 - 3.3.

• Blue cells show transfromations that do not change kind of link, i.e. only power of link is changed.

The Valentina database engine provides API methods and SQL commands to support such kind of transformations.

(m1) VLink.CopyLinksFrom( inOtherLink as VLink )
(m2) VLink.CopyLinksFrom( inLeftLink as VLink, inRightLink as VLink )
(m3) VLink.CopyLinksTo  ( inLeftLink as VLink, inRightLink as VLink )

The general steps of link refactoring are:

• you prepare a new empty link(s) of kind and power as you need.
• call one of the above methods to COPY LINKS from the old link(s) into new link(s).
• drop the old link.

Method (m1) is used when you transform ONE existed link into ONE new link, for example:

ObjectPtr 1:M      =>   FK 1:M
ObjectPtr 1:M      =>   BinaryLink 1:M

Method (m2) is used when we have two old links and transform them into a single link. Usually this means that we did have MM Table with two ObjectPtr or FK fields and we transform it into BinaryLink.

Method (m3) is used when we have a single link and transform it into two links. Usually, this is the transformation from single Binary Link into MM Table design.

Valentina also provides SQL commands to do Link Refactoring. These commands are extensions to SQL particular to Valentina SQL.

COPY LINKS FROM src_link_name 
           TO dest_link_name
 
COPY LINKS FROM src_link_name AND src_link_name 
           TO dest_link_name 
 
COPY LINKS FROM src_link_name 
           TO dest_link_name AND dest_link_name2

You can see that these three SQL commands follow the same rules as above m1 - m3 API methods.

Both API and SQL commands work with links, even when you are working with a MM Table. In the MM table, you can see that tables T1 and T2 are linked with help of two links. See picture:

Refactoring algorithms are implemented internally ONLY and ONLY with API of VLink class, i.e. they do not use VTable.AddRecord() methods never.

This fact allows even such exotic structure designs as MM table with ObjectPtr to the left table and FK-link to the right table. Or MM Table with pointer fields at all, but two binary links T1 - MMTable and MMTable - T2.

All such designs still will work with Refactoring methods.