Views

Normally, persistence-capable classes in TJDO are backed in the database by base tables. Each class has a corresponding base table, plus one additional base table for each normal (i.e. non-inverse) Collection or Map field.

It is possible to define, via JDO metadata, that a class is to be backed by a database view rather than a base table. Views in JDO are useful for leveraging some SQL capabilities that would otherwise not be accessible through the JDO interface. The most common uses for views are:

1. To define an object made up of "report-like" results produced by SQL aggregate functions, SQL's GROUP BY mechanism, or other SQL features inaccessible through JDO.
2. To define, as a performance enhancement, an object comprised of a subset of the fields of a larger, or a combination of larger, persistence-capable object(s).

Instances of view objects behave very much differently than normal JDO instances. View objects:

• Are read-only. Persistent instances cannot be created (makePersistent()), deleted (deletePersistent()), nor can any of their fields be written.
• Are accessible only via their corresponding Extent. However, an Extent of view objects can be queried to further filter or order the view data.
• Cannot declare a persistence-capable superclass.
• Cannot contain Collection or Map fields.
• Use so-called non-durable identity , and must be so defined in their JDO metadata. This is accomplished by setting identity-type="nondurable" on the <class> element. Non-durable identity in JDO means that each instance has a unique ID, but that ID is not guaranteed to be the same from one access to the next, nor can objects be retrieved by ID (getObjectById()).

Also, instances of view objects do not employ the "fetch-on-demand" strategy normally used for fields of persistent objects, nor is the default fetch group used; all persistent fields are always loaded. The InstanceCallbacks jdoPostLoad() and jdoPreClear() will be called at the expected times, but jdoPreStore() and jdoPreDelete() will never be called as they do not apply.

View Definition

A class is defined to use a view by including a view-definition extension in its JDO metadata. The text content of the extension element should contain the SQL CREATE VIEW statement(s) needed to create the view. For example:

public class AvgSalaryByManager
{
    private Manager manager;
    private double avgSalary;

    ...
}

<class name="AvgSalaryByManager" identity-type="nondurable">
    <extension vendor-name="triactive" key="view-definition" value="
        CREATE VIEW AVG_SALARY_BY_MANAGER
        (
          MANAGER_ID,
          AVG_SALARY
        ) AS SELECT MANAGER_ID, AVG(SALARY)
               FROM EMPLOYEE
           GROUP BY MANAGER_ID" />
</class>

If necessary, the view definition text can contain multiple SQL statements separated by semi-colons. This might be needed e.g. if a view were defined in terms of other sub-views.

TJDO O-R Mapping Details

Given that a view definition makes direct SQL reference to other JDO base tables, it helps to know a little about how TJDO maps persistence-capable classes to database tables (see also schema management). The strategy is fairly simple:

• Every persistence-capable class has its own base table, whose name is derived from the class name. The table is made up of:
  • An ID column, which is the primary key.
  • One column per persistent field, excluding Collection or Map fields. Only fields declared in the class have columns in the table; fields inherited from any superclasses live in the table(s) for those superclasses. Fields referring to persistence-capable objects are represented by a column storing an object ID.
• Every "normal" (i.e. non-inverse) Collection field has its own base table, whose name is derived from a concatenation of the class name and the field name. The table stores one row per element in the collection, and is made up of:
  • An owner ID column, indicating the object that "owns" the collection (i.e. the object in whose Collection field this element belongs).
  • An element column, which may be an ID if the element type is a persistence-capable class, or the element data itself for "embedded" types such as primitives, Strings, etc.
  • The primary key is {owner,element}.
• Every "normal" (i.e. non-inverse) Map field has its own base table, whose name is derived from a concatenation of the class name and the field name. The table stores one row per entry in the map, and is made up of:
  • An owner ID column, indicating the object that "owns" the map (i.e. the object in whose Map field this entry belongs).
  • A key column which, like a Collection's element column, may be an ID or embedded data.
  • A value column, which also may be an ID or embedded data.
  • The primary key is {owner,key}.

Table/Column Name Substitution Macros

Defining views obviously requires making direct reference to table and column names of JDO-managed base tables. However, the actual table and column names generated by TJDO can vary for a variety of reasons, including:

• The existence of non-JDO tables in the schema.
• The order in which tables are created.
• The maximum allowed length for table/column identifiers.

Therefore, view definitions support a rudimentary "macro" mechanism in order to substitute (at view creation time) the actual generated table/column names into the CREATE VIEW statement(s). For example, the preferred way to write the above view example would be:

<class name="AvgSalaryByManager" identity-type="nondurable">
    <extension vendor-name="triactive" key="view-definition" value="
        CREATE VIEW {this}
        (
          {this.manager},
          {this.avgSalary}
        ) AS SELECT {Employee.manager}, AVG({Employee.salary})
               FROM {Employee}
           GROUP BY {Employee.manager}" />
</class>

A macro consists of a list of one, two, or three identifiers, separated by periods, and enclosed in braces {}. The supported macros are:

View Imports

In substitution macros, className can be a fully-qualified class name, or an unqualified name if the class is within the same package as the view being defined. An "import" mechanism is also available, which works similarly to the way declareImports() works in a JDO query. Imports are declared using an additional view-imports extension. For example, if class Employee were in a different package we could write:

<class name="AvgSalaryByManager" identity-type="nondurable">
    <extension vendor-name="triactive" key="view-imports" value="
        import com.triactive.jdo.examples.Employee;" />
    <extension vendor-name="triactive" key="view-definition" value="
        CREATE VIEW {this}
        ..." />

and still use the unqualified class name Employee in substitution macros.

Vendor-Specific View Definitions

Depending on the database vendor, a given view may need to be defined by different SQL statements. The definition given by the view-definition extension constitutes the default, or "generic" definition of that view. Additional vendor-specific view definitions can be provided by including additional extensions of the form view-definition-vendorID. TJDO currently recognizes the following vendor IDs:

• cloudscape
• db2
• db2j
• firebird
• hsqldb
• mysql
• oracle
• pointbase
• postgresql
• sapdb
• sqlserver

Vendor-specific definitions are frequently needed e.g. for Oracle, whose outer join syntax differs drastically from most others. For example:

<class name="EmployeeCountByManager" identity-type="nondurable">
    <extension vendor-name="triactive" key="view-definition" value="
        <!-- The "generic" definition. -->
        CREATE VIEW {this}
        (
          {this.managerName},
          {this.employeeCount}
        ) AS SELECT MGR.{Employee.name},
                    COUNT(EMP.{Employee.this})
               FROM {Employee} AS EMP
                    INNER JOIN {Employee} AS MGR
                      ON EMP.{Employee.manager} = MGR.{Employee.this}
           GROUP BY EMP.{Employee.manager}" />
    <extension vendor-name="triactive" key="view-definition-oracle" value="
        <!-- The Oracle-specific definition. -->
        CREATE VIEW {this}
        (
          {this.managerName},
          {this.employeeCount}
        ) AS SELECT MGR.{Employee.name},
                    COUNT(EMP.{Employee.this})
               FROM {Employee} EMP,
                    {Employee} MGR
              WHERE EMP.{Employee.manager} = MGR.{Employee.this}
           GROUP BY EMP.{Employee.manager}" />