Switch to: V12V11V10V9V8V7V6V5

Convert or Copy Foreign Key to ObjectPtr

In this section you will learn how to:

  • Convert Foreign Keys to ObjectPtr
  • Use the COPY LINK SQL Command

Initinal State

Let you have:

person_phone_foreignkey.jpg

  • Two tables Person and Phone with some records linked as 1:M.
  • Table Person has some Primary Key based on the column with name “ID”.
  • Table Phone has Foreign Key based on the field with name “PersonPtr”.
  • This Foreign Key constraint is named as “linkPhonePerson_M1”.

i.e. you have an usual design for Relational Model.

Final State

Assume you want to replace PK+FK link between tables Person and Phone with an ObjectPtr based link. So you want to get:

person_phone_objectptr.jpg

  • Two tables Person and Phone with some records linked as 1:M.
  • Table Person has neither ID field nor PK constraint.
  • Table Phone has neither Person_ptr field nor FK constraint.
  • Instead, table Phone has ObjectPtr field with the name PersonPtr and associated link name “linkPhonePerson_M1”.

NOTE 1: ObjectPtr field has the same field name and the same link name as Foreign Key. But during refactoring we cannot have two fields/links with the same name. So you need at first use other names and rename later.

NOTE 2: Generally speaking, ObjectPtr field may contain different values compared with FK-field, although on the picture you can see no difference.

Transition Steps

For that you need to do the following steps:

Create ObjectPtr

  • Create in the table Phone a new ObjectPtr field. At this moment it is filled with ZERO values (no pointed records to Person).
  • Do loop by all records of Person as:
    • Go to the Nth record of Person.
    • Find child records in Phone as Set.
    • Update Phone.ObjectPtr with the current Person.RecID value for all records in that Set.

This step is natural but optional.

  • Drop both - Foreign Key constraint and field Person_ptr itself, because it is not required anymore (Records are linked by ObjectPtr field now).
  • Drop the PK field (Person.ID) - especially if it is synthetical field.

It is easy to see that the most efficient way to do above loop is to do it inside of Valentina engine. So Valentina offers a special method for that:

VLink2.CopyLinksFrom( inSrcLink as VLink2 )

API Example

The following example shows how to use CopyLinks() method.

dim fldObjectPtr as VObjectPtr
dim link2Ptr as VLink2
dim link2FK as VLink2
 
' STEP 1: Create ObjectPtr:
fldObjectPtr = tblPhone.CreateObjectPtr( 
                  "fldObjectPtrToPerson", 
                  tblPerson, 
                  kCascade, 
                  "linkObjectPtrToPerson" )
 
' STEP 2: copy links from RDB-link to ObjectPtr link.
' For this we need obtain pointers to link objects of VLink2 type.
link2Ptr = VLink2(fldObjectPtr)
link2FK = VLink2( tblPhone.Link( "linkPhonePerson_M1" ) )
 
link2Ptr.CopyLinksFrom( link2FK )
 
' STEP 3: We DROP FK and PK fields of Person table:
link2FK = nil  ' Forget local pointer to FK field.
tblPhone.DropField( "Person_ptr" )
 
tblPerson.DropField( "ID" )
 
' STEP 4: optionally rename ObjectPtr name and its link name:
fldObjectPtr.name = "Person_ptr"
link2Ptr.name = "linkPhonePerson_M1"
 
db.Flush()

SQL Command

Valentina also offers the SQL command that is analog of VLink2.CopyLinks().

COPY LINKS FROM src_link_name TO dest_link_name

SQL Example

-- STEP 1: Create ObjectPtr:
ALTER TABLE tblPhone 
    ADD COLUMN fldObjectPtrToPerson ObjectPtr CONSTRAINT linkObjectPtrToPerson REFERENCES tblPerson ON DELETE CASCADE
 
-- STEP 2: copy links from RDB-link to ObjectPtr link.
COPY LINKS FROM linkPersonPhone TO linkObjectPtrToPerson
 
-- STEP 3: We DROP FK and PK fields of Person table:
ALTER TABLE Phone
    DROP COLUMN Person_ptr
 
ALTER TABLE Person
    DROP COLUMN ID