This is part 3 of a 3-part series on how to query the SPF database.
In part 1, I gave an overview of the SPF database model, and showed how to write a simple query to return all objects of a specific type, starting from a given interface. This showed the DATAOBJ (object) and DATAOBJIF (interface) tables.
In part 2 I showed how to get two properties for those objects, using the DATAOBJPR (property) table.
In this post, part 3, I will show how to get a related object via the DATAREL (relationship) table.
First we start with the same query as we built up in parts 1 and 2.
To traverse a relationship to a related object, we need to join from the DATAOBJ table to the DATAREL table, and then do another join from DATAREL back to DATAOBJ, with a different alias to distinguish the two references to DATAOBJ.
First let’s join the DATAREL table using LEFT OUTER JOIN DATAREL.
/* Get a specific related object for each object. Continue with the query from the prior section. In this example we will get the current Version object related to each Revision object. /
/* Step 1. Join the DATAREL Relationships table and find the Revision to Version relationships.
-- Add another LEFT OUTER JOIN to DATAREL. LEFT OUTER JOIN again ensures we don't drop any rows from our result set that have no relationship. Join from DATAOBJ column OBJUID to DATAREL column UID1 first, and see if the relationship we want is in the list. If not then try joining to DATAREL column UID2 instead. Alternatively, you can use the SPF desktop client to find whether to join on UID1 or UID2, and the RelDef name.
-- Update the SELECT clasue to return all columns from this join table.
r_rev_ver.*
-- Now we run the query and look to see if we found the relationships we want. We look for the "Revision to Versions" relationship type. If not found we change the join to connect to DATAREL column UID2 instead.
*/
select i.interfacedefuid, o.objuid, o.objname, o.objdefuid, o.config,
p_major_rev.strvalue as SPFMajorRevision, /* choose the Major Revsion number column from the Property table alias */
p_minor_rev.strvalue as SPFMinorRevision, /* choose the Minor Revsion number column from the Property table alias */
r_rev_ver.*
from dataobjif i /* Use alias of "i" for the Interface table */
/* Join to the Object table to get the objects having this interface. */
join dataobj o
on i.objobid = o.obid /* The Interface table's OBJOBID column is the foreign key to the Object table's OBID primary key column. */
and o.terminationdate = '9999/12/31-23:59:59:999' /* include only current objects */
/* Join to the Property table to get the SPFMajorRevision property for these objects. */
left outer join dataobjpr p_major_rev
on o.obid = p_major_rev.objobid /* The Property table's OBJOBID column is the foreign key to the Object table's OBID primary key column. */
and p_major_rev.terminationdate = '9999/12/31-23:59:59:999' /* include only current objects */
and p_major_rev.propertydefuid = 'SPFMajorRevision'
/* Join to the Property table to get the SPFMinorRevision property for these objects. */
left outer join dataobjpr p_minor_rev
on o.obid = p_minor_rev.objobid /* The Property table's OBJOBID column is the foreign key to the Object table's OBID primary key column. */
and p_minor_rev.terminationdate = '9999/12/31-23:59:59:999' /* include only current objects */
and p_minor_rev.propertydefuid = 'SPFMinorRevision'
/* Join to the Relationship table (DATAREL) and get all relationships. Try End 1 first. */
left outer join datarel r_rev_ver /* Alias "r_rev_ver" means "Relationship from Revision to Version" */
on o.objuid = r_rev_ver.uid1 /* The DATAOBJ table joins from column OBJUID to the DATAREL table column UID1 or UID2, depending on the relationship type. */
and r_rev_ver.terminationdate = '9999/12/31-23:59:59:999' /* include only current data (ignore terminated relationships) */
/* Back to the main Interface table selection criteria */
where i.interfacedefuid = 'ISPFDocumentRevision' /* include only our desired interface */
and i.terminationdate = '9999/12/31-23:59:59:999' /* include only current data (ignore terminated records) */
and rownum <= 100 /* Just get a few records for speed during testing */
;
Here we can see the result of selecting R_REV_VER.* – we get all columns for all relationships where our objects are at end 1 of the relationship (DATAREL column UID1):
SPF Database Queries 3 – Step 1
Next we restrict this join to include only the type of relationship we want, and select only the relationship type column from the DATAREL table. We don’t really need it but we keep it for clarity.
/* Step 2. Update the join to the DATAREL Relationships table to filter to just the SPFRevisionVersions relationship.
-- Since we found the relationship objects we want, we copy the DEFUID value from the DATAREL table for us in our query.
-- Add
and r_rev_ver.defuid = 'SPFRevisionVersions'
to the DATAREL join to get just the Revision to Versions relationships.
-- Update the SELECT clause to choose only the DEFUID column.
r_rev_ver.defuid as RelDef_Revision_To_Version
In reality we would not likely need this column, but we include it here for clarity.
*/
select i.interfacedefuid, o.objuid, o.objname, o.objdefuid, o.config,
p_major_rev.strvalue as SPFMajorRevision, /* choose the Major Revsion number column from the Property table alias */
p_minor_rev.strvalue as SPFMinorRevision, /* choose the Minor Revsion number column from the Property table alias */
r_rev_ver.defuid as RelDef_Revision_To_Version
from dataobjif i /* Use alias of "i" for the Interface table */
/* Join to the Object table to get the objects having this interface. */
join dataobj o
on i.objobid = o.obid /* The Interface table's OBJOBID column is the foreign key to the Object table's OBID primary key column. */
and o.terminationdate = '9999/12/31-23:59:59:999' /* include only current objects */
/* Join to the Property table to get the SPFMajorRevision property for these objects. */
left outer join dataobjpr p_major_rev
on o.obid = p_major_rev.objobid /* The Property table's OBJOBID column is the foreign key to the Object table's OBID primary key column. */
and p_major_rev.terminationdate = '9999/12/31-23:59:59:999' /* include only current objects */
and p_major_rev.propertydefuid = 'SPFMajorRevision'
/* Join to the Property table to get the SPFMinorRevision property for these objects. */
left outer join dataobjpr p_minor_rev
on o.obid = p_minor_rev.objobid /* The Property table's OBJOBID column is the foreign key to the Object table's OBID primary key column. */
and p_minor_rev.terminationdate = '9999/12/31-23:59:59:999' /* include only current objects */
and p_minor_rev.propertydefuid = 'SPFMinorRevision'
/* Join to the Relationship table (DATAREL) on End 1 and get just SPFRevisionVersions relationships. */
left outer join datarel r_rev_ver /* Alias "r_rev_ver" means "Relationship from Revision to Version" */
on o.objuid = r_rev_ver.uid1 /* The SPFRevisionVersions Relationship defines End 1 as the Revsion, and End 2 as the Version. So first we join our Revisions on UID1.*/
and r_rev_ver.terminationdate = '9999/12/31-23:59:59:999' /* include only current data (ignore terminated relationships) */
and r_rev_ver.defuid = 'SPFRevisionVersions'
/* Back to the main Interface table selection criteria */
where i.interfacedefuid = 'ISPFDocumentRevision' /* include only our desired interface */
and i.terminationdate = '9999/12/31-23:59:59:999' /* include only current data (ignore terminated records) */
and rownum <= 100 /* Just get a few records for speed during testing */
;
And here we can see the results, showing the SPFRevisionVersions relationship type in the last column.
SPF Database Queries 3 – Step 2
Now to make this join useful, we need to join back to the DATAOBJ table again – but this time we “leave” the DATAREL table on UID2 since we “entered” it on UID1.
/* Step 3. Join back to the DATAOBJ table from End 2 (UID2) of the DATAREL table to the Version objects.
-- Add another join from DATAREL back to DATAOBJ with a new alias of "o_ver" (Object Version). I indent this under the initial join to DATAREL to show it's just another part of the same requirement.
-- Update the SELECT statement to include all columns from the new o_ver alias to DATAOBJ for the Version objects.
-- Now that we have joined to a related object, our result set could have more than one row per revision, if for example there are multiple versions for each revision then we will see the revision data duplicated.
*/
select i.interfacedefuid, o.objuid, o.objname, o.objdefuid, o.config,
p_major_rev.strvalue as SPFMajorRevision, /* choose the Major Revsion number column from the Property table alias */
p_minor_rev.strvalue as SPFMinorRevision, /* choose the Minor Revsion number column from the Property table alias */
r_rev_ver.defuid as RelDef_Revision_To_Version,
o_ver.objuid as Version_OBJUID, o_ver.objdefuid as Version_ObjDefUid, o_ver.objname as Version_Objname /* Version object columns */
from dataobjif i /* Use alias of "i" for the Interface table */
/* Join to the Object table to get the objects having this interface. */
join dataobj o
on i.objobid = o.obid /* The Interface table's OBJOBID column is the foreign key to the Object table's OBID primary key column. */
and o.terminationdate = '9999/12/31-23:59:59:999' /* include only current objects */
/* Join to the Property table to get the SPFMajorRevision property for these objects. */
left outer join dataobjpr p_major_rev
on o.obid = p_major_rev.objobid /* The Property table's OBJOBID column is the foreign key to the Object table's OBID primary key column. */
and p_major_rev.terminationdate = '9999/12/31-23:59:59:999' /* include only current objects */
and p_major_rev.propertydefuid = 'SPFMajorRevision'
/* Join to the Property table to get the SPFMinorRevision property for these objects. */
left outer join dataobjpr p_minor_rev
on o.obid = p_minor_rev.objobid /* The Property table's OBJOBID column is the foreign key to the Object table's OBID primary key column. */
and p_minor_rev.terminationdate = '9999/12/31-23:59:59:999' /* include only current objects */
and p_minor_rev.propertydefuid = 'SPFMinorRevision'
/* Join to the Relationship table (DATAREL) on End 1 and get just SPFRevisionVersions relationships. */
left outer join datarel r_rev_ver /* Alias "r_rev_ver" means "Relationship from Revision to Version" */
on o.objuid = r_rev_ver.uid1 /* The SPFRevisionVersions Relationship defines End 1 as the Revsion, and End 2 as the Version. So first we join our Revisions on UID1.*/
and r_rev_ver.terminationdate = '9999/12/31-23:59:59:999' /* include only current data (ignore terminated relationships) */
and r_rev_ver.defuid = 'SPFRevisionVersions'
left outer join dataobj o_ver
on r_rev_ver.uid2 = o_ver.objuid /* DATAREL column UID2 joins back to DATAOBJ column OBJUID for the SPFRevisionVersions relationship type */
and o_ver.terminationdate = '9999/12/31-23:59:59:999' /* include only current data (ignore terminated objects) */
/* Back to the main Interface table selection criteria */
where i.interfacedefuid = 'ISPFDocumentRevision' /* include only our desired interface */
and i.terminationdate = '9999/12/31-23:59:59:999' /* include only current data (ignore terminated records) */
and rownum <= 100 /* Just get a few records for speed during testing */
;
And now we can see our completed query, showing a few select columns from the Version objects in the DATAOBJ table that are related to each Revision object we started from.
SPF Database Queries 3 – Step 3
A likely enhancement to this query would be to join in the Superseded property from those Version objects, and include only the Versions where Superseded = False (i.e. get just the current Version for each Revision).