The Art of Mapping Globals to Classes (2 of 3)
If you are looking to breathe new life into an old MUMPS application follow these steps to map your globals to classes and expose all that beautiful data to Objects and SQL.
This example is going to cram in 4 or 5 different things beyond what was covered in Part 1
All that is left after this is the Parent Child mapping example and then you will be on your way.
Same disclaimer: If you can’t make heads or tails of your globals after reviewing these articles please contact the WRC and we will try to help you out: Support@InterSystems.com
Steps for Mapping a Global to a Class:
- Identify a repeating pattern in the global data.
- Identify what makes up a unique key.
- Identify the properties and their types.
- Define the properties in the class (don’t forget the properties from the variable subscripts).
- Defined the IdKey index.
- Define the Storage Definition:
- Define the Subscripts up to and including the IdKey.
- Define the Data section.
- Ignore the Row ID section. 99% of the time the default is what you want so let the system fill that in.
- Compile and test your class / table.
Say you have 2 globals that look something like this:
^mapping("Less Simple",1,1,"Activity")="Rock Climbing"
^mapping("Less Simple",1,3,"Activity")="Lighting Design"
^mapping("Less Simple",1,4,"Activity")="Marching Band"
^mapping("Less Simple",1,6,"Activity")="Marching Band"
Yes it is a requirement that you learn about my family as you learn about mapping globals. I am not creative enough to invent data.
This time it looks like the repeating data is spread out over 2 global nodes instead of 1.
Hard to tell for sure with just this bit of data. Either the first or the first and second subscripts are constants. For this example we are going to assume that the second subscript is variable, FamilyId, and the third subscript is the PersonId.
So we have the two variable subscripts FamilyId and PersonId, and then we have the name, relation, and activity. Looking at the index global again for a hint I am going to map the name to two properties, FirstName and LastName. So that is a total of 6 properties we need to define.
A couple of things are new here. All the properties have an SQL Field Name and FirstName has a collation of UPPER. Defining SQL Field Names will make it clearer when we are talking about a Property and when we are talking about an SQL Field. The collation we will talk about when we look at mapping the index.
Property FamilyId As %Integer [ SqlFieldName = Family_Id ];
Property PersonId As %Integer[ SqlFieldName = Person_Id ];
Property FirstName As %String(COLLATION = "UPPER")[ SqlFieldName = First_Name ];
Property LastName As %String[ SqlFieldName = Last_Name ];
Property Relation As %String[ SqlFieldName = Relation ];
Property Activity As %String[ SqlFieldName = Activity ];
There are 2 variable subscripts so that means the IdKey will be based on two properties:
Index Master On (FamilyId, PersonId)[IdKey];
Again we have 1 index defined on 1 property, FirstName.
Index FNameIndex On FirstName;
First thing to do is to create the storage definition. Click on the Storage icon or use the Inspector, Storage, right click, New Storage. This time I did leave the default value for the Map Name, just filling in the Global Name, ^mapping.
One thing I did not show in the first example is how to get back to the wizard after you have created a storage definition. From the Inspector select Storage, then pick the Storage Name (in this case NewStorage1) click on SQL Storage Map, and then click on the box to the far right to open the wizard.
Starting with the Subscripts section again. First we will need 3 subscript levels to define everything up to and including the IdKey: 1 constant and 2 fields. The fields that are listed in this window need to match the properties that are defined for the IdKey index.
Note: In the storage definition we are always referencing the SQL field name, Family_Id, not the property name, FamilyId.
For the Data section we have 3 fields stored in 1 global node and then we have 1 field stored at a lower subscript level ( “Activity”). To get at the first name and the last name as 2 different fields we will need to use multiple pieces and delimiters corresponding to a nested $PIECE(). In ObjectScript this would look like: set FirstName=$PIECE($PIECE(^mapping(“Less Simple”,1,1),”^”,1),”,”,2). In the wizard we need to list all the Delimiters and Pieces starting on the innermost $PIECE() and working our way out. Activity is on a different global node so we add the constant to Node. Piece and Delimiter default to 1 and “^” so I just left them there.
Still nothing to see here.
OK I know you are dying to see an example of when you need to define the Row ID section. When I post the zip file with all the examples there will be one called Mapping.RowIdSpec that will show a case where this is needed.
Now we can repeat Step 6 for the index map.
Create Map2 and set the Global Name to ^index.
The index global has 5 subscripts: 2 constants, 2 IdKey fields, and the indexed field, First_Name. Looking at the ^mapping global and the ^index global the values for First_Name are different. In the ^mapping global the names are mixed case while in the ^index global the names are all UPPER case. Caché has several built in collation functions you can use to transform string data. Check out this link for info on the different functions you can use:
For Caché SQL Storage there is a good chance you might need to go to the legacy collation types:
It is very important that the collation function used here matches the collation in the property definition. Having these not match is the most common reason an index map is not used by the query optimizer. Remember %String has a default collation of SQLUPPER while not providing a function in the mapping would be the same as EXACT collation.
The ^index global has no data so nothing to define here.
By now you know, nothing here either.
Compilation started on 08/22/2016 15:42:16 with qualifiers 'fck /checkuptodate=expandedonly'
Compiling class Mapping.Example2
Compiling table Mapping.Example2
Compiling routine Mapping.Example2.1
Compilation finished successfully in 0.144s.
SELECT Family_ID, Person_ID, First_Name, Last_Name, Relation, Activity
Family_Id Person_Id First_Name Last_Name Relation Activity
1 1 Brendan Bannon Father Rock Climbing
1 2 Sharon Bannon Mother Yoga
1 3 Kaitlin Bannon Daughter Lighting Design
1 4 Melissa Bannon Daughter Marching Band
1 5 Robin Bannon Daughter Reading
1 6 Kieran Bannon Son Marching Band
OK this time I am not going to bother with the storage definition. If you want to look at that you can load the xml and look at it there: mapping.example2.zip