This is part 1 of a 3-part series on how to query the SPF database.

In this post, I give an overview of the SPF database model, and show how to write a simple query to return all objects of a specific type, starting from a given interface. This shows the DATAOBJ (object) and DATAOBJIF (interface) tables.

In part 2, I show how to get two properties for those objects, using the DATAOBJPR (property) table.

In part 3, I show how to get a related object via the DATAREL (relationship) table.

If you are new to SPF, directly accessing the database can be a little confusing. Most of us are used to database schemas where each “business object” is stored in a dedicated table with properties of those objects being columns in that table, and relationships between business objects being either foreign keys in one of the tables, or perhaps a separate “join” table.

SPF is designed to be highly configurable by the client. Intergraph chose to make the database more abstract, so the configuration of the application schema does not require changes to the physical database schema. The SPF application schema is stored as data in the SCHEMA* tables. Classes, interfaces, properties and relationships are modelled in SPF, and they are all stored in the database as records; with no changes to the physical database schema. And object instances of those classes and relationships are similarly stored as records in the DATA* tables, with columns denoting what type of object, interface, property or relationship it is. For better or for worse this can make the database a little confusing when you access it directly with database query tools.

SPF Tables

The first thing you will notice is that the database tables appear in several groups of five tables, with similar names. Common ones are:

• DATA*
• SCHEMA*

and you may see others like

• PUB*
• AUDIT*

Each group of tables is called a Domain Group in SPF (you can search in SPF under Find – Administration – Domain Group).

The DATA Domain Group holds most of your business data. The SCHEMA Domain Group holds the SPF application schema: definitions of your Document and Tag Classes, Interfaces, Properties, Relationships, etc. For now we will focus on the DATA domain group as the most interesting, as this is where your tags, documents and other business data objects are. But the rules for how these tables hang together also applies to the SCHEMA… and other groups of tables.

There are five DATA… tables. The tables with “OBJ” hold your Object data (actual data records like tags and documents). The “REL” table holds the relationships between your Objects.

• DATAOBJ … (OBJ = Object) This is the base table where objects instances are stored: e.g. Documents, Tags, etc. There is oe record in this table for each basic object (e.g. Tag, Document Master, Document Revision, etc.)
• DATAOBJIF … (IF = Interface) This is where the interfaces held by objects are stored. This means actual concrete interfaces implemented by a given object instance, not interface definitions (InterfaceDefs) attached to classes. See below.
• DATAOBJPR … (PR = Property) This is where all the property names and values for objects are stored, in a name/value pair type of pattern.
• DATAOBJPRDETAIL … (PRDETAIL = Property Detail) This is where property values go if they exceed 4000 bytes and hence do not fit in the PR table. This is a table of BLOBs.
• DATAREL … (REL = Relationship) This is where relationships between objects are stored.

Table Relationships

Every table has a primary key column named OBID. This is simply the ID of that row in that table.

The DATAOBJ table is the main table, and the other DATA* tables can be considered child tables to it. The DATAOBJIF table is an immediate child. The DATAOBJPR table is a child to DATAOBJIF, and a grandchild to DATAOBJ.

The DATAOBJIF interface table has a foreign key named OBJOBID. This is the OBID of the parent object in the DATAOBJ table. Think OBJOBID = Object (parent’s) OBID.

The DATAOBJPR property table similarly has a foreign key named OBJOBID, that links to the OBID of the grandparent object in the DATAOBJ table. The DATAOBPR property table also has another foreign key named INTERFACEOBID, that links to the OBID of the immediate parent interface in the DATAOBJIF table that exposes this property.

Whereas the OBID column is used to link an object to it’s interfaces and properties; the OBJUID column is used to relate objects to other objects via the DATAREL relationship table.

DATAREL has 2 foreign keys back to the DATAOBJ table: UID1 and UID2.  These columns join to the OBJUID column in the DATAOBJ table.

Here is a simplified diagram showing the relationships.

A Special Note on SPF Interfaces

The SPF concept of an interface is slightly different from traditional interfaces.

In traditional Java and .NET programming, a Class (a type of object) may implement one or more Interfaces (loosely, a set of properties and methods). All object instances of that class also then have all the properties and methods of all the interfaces that the Class implements. Also, a Class might implement no interfaces, all the properties and methods might be directly defined by the Class. Hence a class model can be defined without use of interfaces.

In SPF, interfaces are required for a class model. Properties and methods can only be attached to an interface; never directly to to a class. Hence a class is really not much more than a collection of interfaces; as it is the interfaces that expose all the methods and properties. Also, a class can implement an interface as optional, meaning that each object instance of that class may or may not implement that interface. This means each object instance needs a list of the interfaces that particular instance implements. This is the purpose of the DATAOBJIF (interface) table.

Writing SQL queries to the SPF database

Now we will walk through several examples of the main types of queries you are likely to need.

Get All Objects With a Specific Interface

To write this query we need to get all objects of a certain type. In SPF, there are two ways to identify the type of an object:

• The Class Definition of the object
• The Interfaces an object has

The Class Definition is useful in a query if you are retrieving exactly one specific type of object. But often objects in SPF are modeled such that there are several classes of objects you want, but they all share a common interface; e.g. selecting all Tag objects will be simplest if you select all objects that implement the ITag interface, rather than all objects that are class SPFThisTag and SPFThatTag and SPFSomeOtherTag. Selecting all objects that have a common interface is what I show here.

Let’s say we want to return all document revisions in the system. First we select all instances of the interface ISPFDocumentRevision. Hence we start with the DATAOBJIF (interface) table.

/* Get all objects with interface ISPFDocumentRevision */
/* Step 1. First we select all instances of the interface.
-- Here I use an alias of "i" for the DATAOBJIF table. I recommend using aliases for all tables since in future queries we will access the same table multiple times, for different purposes. Aliases allow us to distinguish separate uses of the same table.
-- Every table access needs to have WHERE TERMINATIONDATE = '9999/12/31-23:59:59:999' to exclude terminated / historical records. */

select *
from dataobjif i    /* Use alias of "i" for the Interface table */
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) */
;

The query above returns all the ISPFDocumentRevision interfaces attached to data objects (i.e. implemented by object instances).

Now we need to join those interface instances to the actual object instances in the DATAOBJ table (with JOIN DATAOBJ O). This will get the the actual Document Revision objects that have those interfaces. Here is the query from above, enhanced to give us all objects that have the ISPFDocumentRevision interface.

/* Step 2. Join in the DATAOBJ table to get the actual objects that have this interface.
-- The join from Interface (DATAOBJIF) to Object (DATAOBJ) uses the Interface table's OBJOBID column as the foreign key to the Object table's OBID primary key column.
-- Here we can do an inner join (just JOIN for brevity), since we should never have an interface without a parent object.
-- We also start SELECTing specific columns instead of * for the tables we already got what we wanted from. For the Interface table we select just the interface name, but we get all the columns for the Object table for now.
-- As you add more columns the query will become slow. On Oracle databases, you can use ROWNUM <= 10 to get just the first 10 rows of the result set. On SQL Server use TOP 10 in the SELECT clause. */

select i.interfacedefuid, o.*
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 */
/* 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 <= 10    /* Just get a few records for speed during testing */
;

This now returns the object rows from which we can get additional data.

Now we finalize the query by selecting just the columns we want.

/* Step 3. Finalize the query.
-- Now we can SELECT just the columns we want in the result set. Using SELECT * in earlier versions of the query makes it easy to see the columns available with sample data.
-- We remove our ROWNUM <= 10 so we get all the data. Here we commented it out with a leading double dash -- .     */

select i.interfacedefuid, o.objuid, o.objname, o.objdefuid, o.config
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 */
/* 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 <= 10    /* Now we comment this out to get all records */
;

And now we have a finished query getting all objects that implement a specific interface.