GOCDB/PROM

From EGIWiki
Jump to: navigation, search
Main EGI.eu operations services Support Documentation Tools Activities Performance Technology Catch-all Services Resource Allocation Security


GOC DB menu: Home Documentation Index


This page is part of the GOCDB4 Architecture Documentation

PROM (Pseudo-Relational Object Model)

PROM is a proprietary Object Relational Mapping (ORM) style solution for persisting data as objects in a relational database and, importantly, for recording parent/child relationships (links) between those objects without defining DDL schema constraints at the database-schema level (relationships are traditionally enforced in a relational database using PK/FK constraints). By excluding DDL schema constraints, a PROM database can accommodate schema changes without affecting existing software or data. For GOCDB, the intention is to allow NGIs to add their own custom data objects to a local installation whilst leaving the core GOCDB objects intact. While the PROM methodology certainly does provide flexibility, it also increases complexity. This is especially true when querying for object relationships. This is because queries must repeatedly join across the core ‘admin’ PROM tables in order to determine those relationships before those objects can be returned as linked object-graphs.

A PROM database requires a standard set of core ‘admin’ tables that are specifically used to record information about the different types of data objects, and which of those objects are related (linked). For example, for every new data object, a new entry is recorded in the PROM ‘object list’ table (recording the object id and other meta-data such as the object’s table name). Similarly, parent/child relationships between objects are stored as individual entries in the PROM ‘object link’ table (each link entry associates a parent object of a particular type to a child object of a particular type). The rules for linking objects are defined in a separate ‘link type’ table. The core PROM admin tables include TOBJECTS, TOBJECTTYPES, TOBJECTLINKS, and TLINK_TYPES. For GOCDB, the custom ‘data’ tables include USER, SITE, SERVICE_ENDPOINT, DOWNTIME GROUP etc (see the logical and physical entity model diagrams).

To ease working with a PROM database, a database agnostic ‘IPromAPI’ interface has been defined (introduced in GOCDB v4.2) which can be implemented for different databases as required (currently only implemented in PHP for Oracle). The IPromAPI interface is a CRUD style API and simplifies insertion/deletion/linking/updating of objects. Importantly, it also enforces the relationship rules as defined in the ‘link type’ table. Since object relationships are not defined in DDL, the ‘Physical PROM DDL’ is distinctly different from the ‘Logical Object Model.’

GOCDB Logical Entity Model

The relationships shown below are logical – they are not actually defined in a DDL schema with PK/FK constraints. In a PROM database, relationships are enforced by the PROM API and the available link types recorded in the PROM admin tables (admin tables not shown, see the GOCDB physical entity model).

x

GOCDB Physcial Entity Model

PK/FK relationships do not exist between tables in a PROM database. Instead, they are enforced by the application using the PROM API. The tables in the upper half of the diagram are the GOCDB specific data tables, while the tables in the lower half are the PROM admin tables.

x

Objects: Groups of Data

PROM group data together in objects, rather than in tables. Each object is of a specific type and each object from this type is stored in a specified database table. There is no limit to the number of different object types that can be stored in a single table provided that the objects contain identically formatted fields. A complete list of all the objects stored in the database can be found in a table named #TOBJECTS. Each object type is associated with a unique type number. A list of object types, descriptions of the type and associated type numbers can be found in the table #TOBJECT_TYPES.

Each object is assigned a global ID in the local database known as an object ID. This ID is stored alongside the individual data for each object in the associated data table.

An example from the GOCDB4 schema is as follows:

Example

TOBJECTS Table

This table stores a complete list of all the objects in the system by their unique object ID. It also contains a database ID to identify the object as belonging to this particular local database (allowing for distributed databases, more on that later), as well as an object type number. This table also stores the database ID that the type belongs to as well as date on and date off fields (more on these later too).

The following record refers to data about a particular user. This example will show how to find the type of an object and the associated data stored for this particular object.

COBJECTID CGRIDID CTYPEID CTYPEGRID CDATEON CDATEOFF
270 0 105  0 17-APR-09 09.31.16.000000 AM -

This record shows an object with an objectID of 270 of type 105. This object type identifier can be used to find information relating to the storage of this particular type of object by searching the TOBJECT_TYPES table.

TOBJECTS_TYPES Table
CTYPEID CGRIDID CDESCRIPTION COWNER CDBLINK CTABLENAME CDATEON CDATEOFF
105 0 Registered users, operators and contacts  GRIDCORE - GOCDB_USERS  01-JAN-09 01.01.01.000000 AM -

In this table there is a tuple with a type ID of "105" with a description of "Registered users, operators and contacts". One of the fields of this tuple is CTABLENAME indicating the name of the table in which to store this type of data. The CTABLENAME field for this particular record contains "GOCDB_USERS", indicating the table name where user data is stored.

Notice that the object type has fields to store "date on" and "date off". These are used to help with schema changes and is discussed in more detail further on in this page.

The corresponding specific object table holding the user data (GOCDB_USERS) is as follows:

GOCDB_USERS Table
COBJECTID CGRIDID FORENAME SURNAME CERTIFICATE_DN WORKINGHOURS_START WORKINGHOURS_END
270 0 John Casson N/A 09:00 17:00

Here you can see a record with the object ID initially found in the TOBJECTS table along with the associated user record data.

Links between objects

In order to represent relations between objects, a parent-child link is created. A list of all of the parent-child links in the PROM database are stored in the table "TOBJECTLINKS". A link can only appear in this table if it's parameters (parent object type, child object type) appear in a list of link types. These link types are stored in a separate table named "TLINK_TYPES".

Example

TOBJECTLINKS Table

Here is an example record from the TOBJECTLINKS table:

COBJPARENT CGRIDPARENT COBJCHILD CGRIDCHILD CLINKID CGRIDLINKID CDATEON CDATEOFF
336 0 337 0 8 76 17-APR-09 03.27.39.000000 PM -

This example shows a link between two objects where the parent object has the object ID 336 and the child object has the object ID 337. The database ID (called GRID ID in GOCDB4) that each object belongs to is also present. Note that this table doesn't store any information about what type the objects are.

The "CLINKID" field specifies a link type value that corresponds to an entry in the "TLINK_TYPES" table.

TLINK_TYPES Table

What follows is the corresponding record from the TLINK_TYPES table:

CLINKTYPE CPARENTTYPE CPARENTGRID CCHILDTYPE CCHILDGRID CALLOWABLE CLINKDESCRIPTION CDATEON CDATEOFF
8 105 0 101 0 Y user to site - homesite of a given user  01-JAN-09 01.01.01.000000 AM -

This entry shows the data used for link type 8. It specifies that the parent object must be of type 105 and that the child object type must be 101. A description of this link is given, in this case the link describes a user to site relationship. Note that the link includes a date on and date off field, more on this later.

The object IDs listed in the TOBJECTLINKS table correspond to a user object (the parent of the link) and a site object (the child of the link). Note that the API doesn't allow links to be created if an appropriate link type entry is not present in the TLINK_TYPES table.

Date On / Date Off

In order to facilitate schema changes, each object, object type, link and link type have associated date on and date off fields. These fields are used to indicate a time period during which the data stored is valid. By querying with an appropriate time stamp, the schema of the database at that particular time can be recalled while at the same time allowing queries to be executed on newer schema by specifying a different time stamp.

Further Information

More information about this underlying model can be found in the Media:PROM_paper.pdf and the corresponding Media:PROM slides.pdf.