In the SQL for the queries that you submit to Content Platform Engine, you can reference Content Platform Engine properties and classes
in the following clauses: SELECT, FROM, WHERE, ORDER
BY, and JOIN. Content Platform Engine parses the SQL in your
submitted query into SQL that is known as database SQL. This parsing
occurs in the following manner: classes are mapped to tables, properties
are mapped to columns, and system columns are added to the SELECT, WHERE,
and ORDER BY clauses as necessary.
The following table shows an example of how
Content Platform Engine translates your query
SQL into database SQL.
Table 1. Query SQL to database SQL translation
exampleContent Platform Engine SQL |
Corresponding database SQL |
SELECT Id, Creator, DocumentTitle
FROM DocSubClass
WHERE SiteLocation = 'site123'
ORDER BY DocumentTitle
|
SELECT object_id, object_class_id, security_id, creator, ..., u1708_documenttitle
FROM DocVersion
WHERE object_class_id IN (?,?)
AND home_id Is Null
AND u7E24_sitelocation = ?
ORDER BY u1708_documenttitle, object_id
|
The following aspects of database SQL might require some explanation:
Table 2. Database SQLItem |
Comment |
Bind markers |
The "?" symbols that Content Platform Engine adds to the database
SQL are bind markers. Because of bind markers, a query execution plan
does not have to be recompiled for each execution of the same search
with different search values. Bind markers are required for fast performance
in a high concurrency system. These markers or markers such as the
following ones can be seen in a FileNet® P8 Server
trace or database trace: ":1", ":2", and so on. |
Property column prefixes |
The prefix on property columns in the database
takes the form of uxy_, in which xy is a set of four digits. In the
previous example, for instance, the following prefixes are shown:
u1708_ and u7E24_. The uxy_ prefix varies from system to system and
from property to property; it designates a user property column as
opposed to a system column. For more information, see Custom
table and Column Names. |
WHERE clause: object_class_id
IN (?,?) |
The object_class_id WHERE clause
is always added to a search on a subclass to restrict the search to
that class. If the WITH INCLUDESUBCLASSES clause
(the default) is specified as part of the Content Platform Engine SQL, the search is
restricted to the class and subclasses. In the case of this query,
the Document subclass is "DocSubClass". |
WHERE clause: home_id
Is Null |
The home_id clause is added to restrict the
search to objects that are not associated with cross-object store
references. |
ORDER BY clause: u1708_documenttitle,
object_id |
The ORDER BY object_id is added
by the Content Platform Engine for
any continuable query. |