Populate relationship property on existing data

Question

Question

Craig Bishop · Jun 22, 2022

Hello,

I am wondering if anyone can help me with an issue we are facing. We are trying to add a parent-child relationship between 2 tables in the database that already contain data. We are able to add the new relationship property to the required data classes, and set the desired cardinality. We have also included code that will add the relationship using the Insert() method to new data that comes in.

The problem we have is how do we add the relationship to data that already exists in the database?

After the relationship properties were deployed, the data in the children table is no longer visible in the db table. We know the data is still there, because it is still in the Data Global, and if we remove the relationship properties we can once again see it in the db table. We tried to use a %NOINDEX clause in a Select statement, to see if it would display the data, but that didn't help.

Once we are able to display the records that have null relationships, we are planning to employ the logic we have in place for new data: open object of parent, set the relationship between the two, save. However, without being able to view the existing data, we are stuck how to proceed.

Would love any help that could be provided.

Thank you!

Craig Bishop

Product version: IRIS 2019.1

$ZV: Windows 2018.1.2

Discussion (3)1

Log in or sign up to continue

Vitaliy Serdtsev · Jun 23, 2022

See Parent-Child Relationships and Storage

Demonstration:

Class dc.child Extends %Persistent
{
Property name;
Property parent As dc.parent;
// Relationship parent As dc.parent [ Cardinality = parent, Inverse = child ];
}

Class dc.parent Extends %Persistent
{
Property name;
// Relationship child As dc.child [ Cardinality = children, Inverse = parent ];
ClassMethod Test()
{
  d ..%KillExtent()
  d ##class(child).%KillExtent()
  
  &sql(insert into dc.parent(name) values('parent1'))
  &sql(insert into dc.parent(name) values('parent2'))

  &sql(insert into dc.child(name,parent) values('child11',1))
  &sql(insert into dc.child(name,parent) values('child12',1))
  &sql(insert into dc.child(name,parent) values('child21',2))
  &sql(insert into dc.child(name,parent) values('child22',2))
  
  zw ^dc.parentD,^dc.childD
}
}

USER>d ##class(dc.parent).Test()
^dc.parentD=2
^dc.parentD(1)=$lb("","parent1")
^dc.parentD(2)=$lb("","parent2")
^dc.childD=4
^dc.childD(1)=$lb("","child11",1)
^dc.childD(2)=$lb("","child12",1)
^dc.childD(3)=$lb("","child21",2)
^dc.childD(4)=$lb("","child22",2)

Important: do not touch the existing Storages in both classes!!!

Class dc.child Extends %Persistent
{
Property name;
//Property parent As dc.parent;
Relationship parent As dc.parent [ Cardinality = parent, Inverse = child ];
Storage Default
{
...
}
}

Class dc.parent Extends %Persistent
{
Property name;
Relationship child As dc.child [ Cardinality = children, Inverse = parent ];
ClassMethod Test()
{
 ...
}
Storage Default
{
...
}
}

USER>d ##class(dc.parent).Test()
^dc.parentD=2
^dc.parentD(1)=$lb("","parent1")
^dc.parentD(2)=$lb("","parent2")
^dc.childD=4
^dc.childD(1,1)=$lb("","child11",1)
^dc.childD(1,2)=$lb("","child12",1)
^dc.childD(2,3)=$lb("","child21",2)
^dc.childD(2,4)=$lb("","child22",2)

Important: now remove Storage from dc.child class and recompile both classes. Note that now the Storage of the dc.child class has changed.

USER>d ##class(dc.parent).Test()
^dc.parentD=2
^dc.parentD(1)=$lb("","parent1")
^dc.parentD(1,"child",1)=$lb("","child11")
^dc.parentD(1,"child",2)=$lb("","child12")
^dc.parentD(2)=$lb("","parent2")
^dc.parentD(2,"child",3)=$lb("","child21")
^dc.parentD(2,"child",4)=$lb("","child22")
^dc.childD=4
^dc.childD(1,1)=$lb("","child11",1)
^dc.childD(1,2)=$lb("","child12",1)
^dc.childD(2,3)=$lb("","child21",2)
^dc.childD(2,4)=$lb("","child22",2)

Now the data in ^dc.childD from the previous test/step is hanging in the air and cannot be accessed via SQL

Based on the above, the answer to your question will depend on what and how exactly you changed in your classes.

PS: for simplicity, I would advise you to create a clone of your dc.child class (without Relationship) and already take the "disappeared" data from it. After linking the tables (possibly with subsequent copying of data from the old Storage to the new one), the clone with the data can be deleted.

0 0

score 0 · Answer 1 · 2022-06-23T16:36:14-04:00

Hello Vitaliy,

Thank you for the advice you provided. You're explanation is pretty much where we are at. After we added the relationships the data is no longer visible in the SQL table. We had previously discussed the possibility of cloning the db prior to adding the relationships, and using it to import the data back in. We still may go that route, but we are trying to get this working another way first.

I mentioned that the data for this class is still visible in the Data Global. So, we're trying to capture the data from the global, create a new object, copy the data into the class properties, set the relationship, and then save the object.

This is almost all working now, but one part is still causing problems. We are looping through a list of the class properties to capture the name and sequence number. The idea to do this came from this article,

https://community.intersystems.com/post/listing-all-properties-class-and...

We are doing this so that we know where to store the data from the global in the correct class property. The problem is that for a couple of the properties the Sequence Number listed is not correct. One option is to hard-code the set commands to save the data correctly. However, we will be using this code on several different environments and it needs to be dynamic so that it will work for each one. As soon as we identify how to return the correct sequence number we should be good.

Thanks again for your help!

score 0 · Answer 2 · 2022-06-27T15:30:22-04:00

We identified how to resolve this issue, and I wanted to post the method we implemented.

I welcome any suggestions or ideas that may improve our logic so that we may gain further understanding of the entire process.

I’ll give a high-level explanation of what we did, and then I’ll include the method we used.

Add parent-child relationship properties to the 2 class definitions.

This will leave the parent class untouched, but it will immediately clear out any records that exist in the child class from displaying in a SQL query window.

Start of UpdateRelationship() method:

Loop through the class definition to identify all the properties that exist including their sequence in the storage definition.
Create a new child object
Loop through the data global and populate the child properties from data within this global entry.
Open a parent object and set the relationship by calling the Insert() function from the parent passing in the child object.
Save the parent object.

End of UpdateRelationship() method.

Run the UpdateRelationship() method manually from the Output panel within Studio.

There are a few more checks to verify the objects are valid, and the child has no existing parent object.

We are also deleting the old Index Global entry, as well as logging various information to a global.

The method we used to populate the relationship is outlined below.

Hopefully this will help if you are facing the same issue we were, or at least give you some additional ideas to try.

ClassMethod UpdateRelationshipOrders() As %Status

{

#define GW(%w) Set ^UpdateRelationship($$$CurrentMethod,$I(^UpdateRelationship($$$CurrentMethod)),%w)=$ZDT($H,3,1)

Try{

s tempcount=0

s tSC=1

s tSuccessfulRows =0,tFailedRows = 0, tParentExist=0, tNoParent=0

s tKey="", tPropertyName="", tPropertyListLocation=""

s tDel=",", tDelSub=":"

s tStorageName="Default"

//Pull oddCOM storage order information

s tStorageNameKey="", tStorage=""

//Grab the First Storage Definition Name (Usually 'Default')

//Grab DefaultData identifier

set tStorageNameKey = $O(^oddCOM($classname(),$$$cCLASSstorage,tStorageNameKey))

set:tStorageNameKey'="" tStorageName = tStorageNameKey

set tStorage = ^oddCOM($classname(),$$$cCLASSstorage,tStorageName,$$$cSDEFdatalocation)

set tStorageDefaultData = ^oddCOM($classname(),$$$cCLASSstorage,tStorageName,$$$cSDEFdefaultdata)

//Create list of Property names and their correlated storage subscript location

For {

set tKey = $O(^oddCOM($classname(),$$$cCLASSstorage,tStorageName,$$$cSDEFdata,tStorageDefaultData,$$$cSDEFDATAvalue,tKey))

Quit:tKey=""

set tPropertyName = ^oddCOM($classname(),$$$cCLASSstorage,tStorageName,$$$cSDEFdata,tStorageDefaultData,$$$cSDEFDATAvalue,tKey,$$$cSDEFDATAVALvalue)

//Add to Delimited List

set tPropertyListLocation = $S(tPropertyListLocation="":tPropertyName_tDelSub_tKey,1:tPropertyListLocation_tDel_tPropertyName_tDelSub_tKey)

}

//Debug

$$$GW(tPropertyListLocation)

//w $L(tPropertyListLocation,tDel)_" <-- How many items in this list",!

s tKey="",tValue="",j=0

For {

//Quit:tempcount=5

s tKey= $O(@tStorage@(tKey),1,tValue)

Quit:tKey=""

s j=j+1

//W i_": "_tValue

//Confirm this Global is not already a Child of another Parent

s tChildKey="", tChildValue=""

s tChildValue = $O(@tStorage@(tKey,tChildKey))

If tChildValue'=""

{

s tParentExist=tParentExist+1

Continue

}

s tData = $LISTTOSTRING(tValue)

//w tData,!

s tOBJ = $CLASSMETHOD($$$CurrentClass,"%New")

//Loops through the List of Properties of the Object

For i=1:1:$L(tPropertyListLocation,tDel) {

s tFieldProperty = $P(tPropertyListLocation,tDel,i)

s tProperty = $P(tFieldProperty,tDelSub,1)

s tLocation = $P(tFieldProperty,tDelSub,2)

//first %%CLASSNAME we can ignore

Continue:tLocation=1

s tLocationValue = $P(tData,tDel,tLocation)

//w tProperty_" = "_tLocationValue_" "_tLocation,!

//Set the Objects Property from the stored Global

s $PROPERTY(tOBJ,tProperty)=tLocationValue

}

//Debug

//zw tOBJ

//Open Parent

set tParentOBJ = ##class(Package.Class for Parent).%OpenId(tOBJ.ParentID)

$$$GW(j_": "_$ISOBJECT(tParentOBJ)_": "_tOBJ.PatientRecordID_" <-- "_tKey)

If '$ISOBJECT(tParentOBJ)

{

s tNoParent=tNoParent+1

Continue

}

S tCheckID=""

&sql(SELECT ID into :tCheckID FROM Schema_Table for Child WHERE PatientRecordID = :tOBJ.PatientRecordID

AND SiteCode = :tOBJ.SiteCode

AND OrderID = :tOBJ.OrderID)

$$$GW(tCheckID_" "_SQLCODE)

If '(tCheckID="")

{

//W "Exists already: "_tCheckID,!

//Kill Existing Data Global, it's already in the parent child relationship

CONTINUE

}

//Follow Parent-Child process, Insert Child into Parent object and save parent

Do tParentOBJ.[Relationship Property].Insert(tOBJ)

//W $D(^Global for Child Index("PatOrdersIdx"," "_tOBJ.PatientRecordID," "_tOBJ.SiteCode," "_tOBJ.OrderID))

//W " <--Data Results",!

Kill ^Global for Child Index("PatOrdersIdx"," "_tOBJ.PatientRecordID," "_tOBJ.SiteCode," "_tOBJ.OrderID)

s tSC = tParentOBJ.%Save()

s tempcount=tempcount+1

If tSC {

s tSuccessfulRows=tSuccessfulRows+1

} else {

$$$GW("Failed on Patient Record ID of ID: "_tOBJ.PatientRecordID_"|"_tOBJ.SiteCode_"|"_tOBJ.OrderID_" | "_tSC)

s tFailedRows=tFailedRows+1

}

Catch e

{

s tSC = e.AsStatus()

W "Not Right: "_tSC,!

}

W "Encounter Completed",!

W "Success: "_tSuccessfulRows_" | Failed: "_tFailedRows_" | ParentExist: "_tParentExist_" | NoParent: "_tNoParent,!

$$$GW("Success: "_tSuccessfulRows_" | Failed: "_tFailedRows_" | ParentExist: "_tParentExist_" | NoParent: "_tNoParent)

Quit tSC

}