Constructing
a SQL Statement
The SearchSQL class
provides helper methods to construct the SQL statement, or you can
pass an existing SQL statement in to a SearchSQL instance.
The helper methods are supplied for assistance in building SQL statements,
and cannot provide the level of specification you can achieve with
an independently constructed statement.
Note: You
cannot combine these two manners of construction. The SQL statement
must be specified in its entirety using either the SearchSQL
helper methods or an independently constructed statement. However,
for the purposes of development, you can use the helper methods to
build the SQL statement, then use the SearchSQL.toString method
to get the SQL statement string and further refine the SQL statement
manually before passing it to SearchSQL.setQueryString.
Using the SearchSQL Methods
The general sequence for constructing a SQL statement using
the SearchSQL methods is as follows:
Java™ Example
// Create the SearchSQL object.
SearchSQL sqlObject = new SearchSQL();
// (Optional) Specify the maximum number of records to be returned. This
defaults to the
// value of ServerCacheConfiguration.NonPagedQueryMaxSize, if unspecified.
sqlObject.setMaxRecords(150);
// Specify the SELECT list using the setSelectList method.
String select = "r.Title, s.Title";
sqlObject.setSelectList(select);
// Specify the FROM clause using the setFromClauseInitialValue method.
// Symbolic name of class.
String myClassName1 = "Requirement";
// Alias name.
String myAlias1 = "r";
// Indicates whether subclasses are included.
boolean subclassesToo = false;
sqlObject.setFromClauseInitialValue(myClassName1, myAlias1, subclassesToo);
// For joins, specify an additional FROM clause using the
setFromClauseAdditionalJoin
// method. Symbolic name of class.
String myClassName2 = "Specification";
// Alias name.
String myAlias2 = "s";
// The property on the class specified for the setFromClauseInitialValue
// method. This is one constituent in the underlying ON clause. The other
constituent is
// prop2.
String prop1 = "s.ApplicationName";
// The property on the class specified for this method (myClassName2).
This is the other
// constituent of the underlying ON clause for the join.
String prop2 = "r.ApplicationName";
// Indicates whether subclasses are included.
subclassesToo = false;
sqlObject.setFromClauseAdditionalJoin(
JoinOperator.INNER, myClassName2, myAlias2, prop1,
JoinComparison.EQUAL, prop2, subclassesToo);
// Specify the WHERE clause using the setWhereClause method.
String whereClause = "r.Title LIKE '%P8%'";
sqlObject.setWhereClause(whereClause);
// Specify the ORDER BY clause using the setOrderByClause method.
String orderClause = "r.Title";
sqlObject.setOrderByClause(orderClause);
// Check the SQL statement constructed.
System.out.println("SQL: " + sqlObject.toString());
// Create a SearchScope instance and test the SQL statement.
SearchScope searchScope = new SearchScope(os);
// Uses fetchRows to test the SQL statement.
RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null,
new Boolean(true));
C# Example
// Create the SearchSQL object.
SearchSQL sqlObject = new SearchSQL();
// (Optional) Specify the maximum number of records to be returned.
This defaults to the
// value of ServerCacheConfiguration.NonPagedQueryMaxSize, if unspecified.
sqlObject.SetMaxRecords(150);
// Specify the SELECT list using the setSelectList method.
string select = "r.Title, s.Title";
sqlObject.SetSelectList(select);
// Specify the FROM clause using the setFromClauseInitialValue method.
// Symbolic name of class.
string myClassName1 = "Requirement";
// Alias name.
string myAlias1 = "r";
// Indicates whether subclasses are included.
bool subclassesToo = false;
sqlObject.SetFromClauseInitialValue(myClassName1, myAlias1, subclassesToo);
// For joins, specify an additional FROM clause using the
setFromClauseAdditionalJoin
// method. Symbolic name of class.
string myClassName2 = "Specification";
// Alias name.
string myAlias2 = "s";
// The property on the class specified for the setFromClauseInitialValue
// method. This is one constituent in the underlying ON clause. The other
constituent is
// prop2.
string prop1 = "s.ApplicationName";
// The property on the class specified for this method (myClassName2).
This is the other
// constituent of the underlying ON clause for the join.
string prop2 = "r.ApplicationName";
// Indicates whether subclasses are included.
subclassesToo = false;
sqlObject.SetFromClauseAdditionalJoin(
JoinOperator.INNER, myClassName2, myAlias2, prop1,
JoinComparison.EQUAL, prop2, subclassesToo);
// Specify the WHERE clause using the setWhereClause method.
string whereClause = "r.Title LIKE '%P8%'";
sqlObject.SetWhereClause(whereClause);
// Specify the ORDER BY clause using the setOrderByClause method.
string orderClause = "r.Title";
sqlObject.SetOrderByClause(orderClause);
// Check the SQL statement constructed.
System.Console.WriteLine("SQL: " + sqlObject.ToString());
// Create a SearchScope instance and test the SQL statement.
SearchScope searchScope = new SearchScope(os);
// Uses fetchRows to test the SQL statement.
IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);
The
SearchSQL instance
(sqlObject) can then be specified in the
SearchScope parameter
list to execute the search. See
SQL
Syntax Reference for detailed information about SQL statement
syntax.
Using an Independently Constructed
Statement
A SQL statement that conforms to IBM® FileNet® standards can be passed to SearchSQL
in string format. You can use either the constructor SearchSQL(String),
or the setQueryString method.
Java Example
// The SQL statement "SELECT DocumentTitle Id FROM Document WHERE DocumentTitle
LIKE
// '%Acct%'" retrieves documents by ID (GUID) that have a document
// title containing the character pattern "Acct".
// You could store this statement in a String variable and pass it to the
// setQueryString method, similar to this:
String mySQLString = "SELECT DocumentTitle, Id FROM Document WHERE DocumentTitle
LIKE '%Acct%'";
SearchSQL sqlObject = new SearchSQL();
sqlObject.setQueryString(mySQLString);
// Alternatively, you could use the SearchSQL(String) constructor:
// String mySQLString = "SELECT DocumentTitle, Id FROM Document WHERE
DocumentTitle
// LIKE '%Acct%'";
// SearchSQL sqlObject = new SearchSQL(mySQLString);
// The SearchSQL instance (sqlObject) can then be specified in the
// SearchScope parameter list to execute the search. Uses fetchRows to test the
SQL
// statement.
SearchScope searchScope = new SearchScope(os);
RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null, new
Boolean(true));
C# Example
// The SQL statement "SELECT DocumentTitle Id FROM Document WHERE DocumentTitle
LIKE
// '%Acct%'" retrieves documents by ID (GUID) that have a document
// title containing the character pattern "Acct".
// You could store this statement in a String variable and pass it to the
// SetQueryString method, similar to this:
String mySQLString = "SELECT DocumentTitle, Id FROM Document WHERE DocumentTitle
LIKE '%Acct%'";
SearchSQL sqlObject = new SearchSQL();
sqlObject.SetQueryString(mySQLString);
// The SearchSQL instance (sqlObject) can then be specified in the
// SearchScope parameter list to execute the search. Uses fetchRows to test the
SQL
// statement.
SearchScope searchScope = new SearchScope(os);
IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);
Searching
for Content
You can include content searches
(full-text searches) in queries constructed using either the SearchSQL helper
methods or independently constructed SQL statements. The CONTAINS
function performs the content search functions. CONTAINS is used for
explicit or fuzzy full-text queries, and can search for content in
all CBR-enabled properties on the object, or a single CBR-enabled
property.
Content
Searches using the SearchSQL Methods
You can specify
a content search by using the SearchSQL helper method, setContainsRestriction.
The following example uses setContainsRestriction to
search for the specified content in all IsCBREnabled properties
of the Document class:
Note: The setContainsRestriction helper
method does not provide the same level of functionality as the CONTAINS
function can in an independently constructed SQL statement. The setContainsRestriction method
does not accept a property parameter to constrain the full-text search
to a specific property.
Java Example
SearchSQL sqlObject = new SearchSQL();
String myClassName = "Document";
sqlObject.setSelectList("DocumentTitle");
sqlObject.setFromClauseInitialValue(myClassName, null, false);
String containsExpression = "'FileNet'";
sqlObject.setContainsRestriction(myClassName, containsExpression);
// Displays the SQL statement.
System.out.println("SQL: " + sqlObject.toString());
// Executes the content search.
SearchScope searchScope = new SearchScope(os);
RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null,
new Boolean(true));
C# Example
SearchSQL sqlObject = new SearchSQL();
string myClassName = "Document";
sqlObject.SetSelectList("DocumentTitle");
sqlObject.SetFromClauseInitialValue(myClassName, null, false);
string containsExpression = "'FileNet'";
sqlObject.SetContainsRestriction(myClassName, containsExpression);
// Displays the SQL statement.
System.Console.WriteLine("SQL: " + sqlObject.ToString());
// Executes the content search.
SearchScope searchScope = new SearchScope(os);
IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);
Content
Searches using Independently Constructed Statements
To specify a content search in an independently constructed
SQL statement, add the CONTAINS function within the WHERE clause.
Only one CONTAINS function can be used in a single SQL statement.
The following examples illustrate the different ways these
content searches can be constructed:
Single
Property Search
In this example, only content
in the AccountName property will be searched (assuming IsCBREnabled
is true for this property).
Java Example
String mySQLString = "SELECT DocumentTitle Id FROM Document d "
+ "INNER JOIN ContentSearch cs ON d.This = cs.QueriedObject "
+ "WHERE CONTAINS(Name, 'FileNet')";
SearchSQL sqlObject = new SearchSQL(mySQLString);
// Executes the content search.
SearchScope searchScope = new SearchScope(os);
RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null,
new Boolean(true));
C# Example
String mySQLString = "SELECT DocumentTitle Id FROM Document d "
+ "INNER JOIN ContentSearch cs ON d.This = cs.QueriedObject "
+ "WHERE CONTAINS(Name, 'FileNet')";
SearchSQL sqlObject = new SearchSQL(mySQLString);
// Executes the content search.
SearchScope searchScope = new SearchScope(os);
IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);
All Properties Search
In this example, the content of all properties on the object
will be searched for a semantic correlation to "company" (assuming
IsCBREnabled is true for all of these properties).
Java Example
String mySQLString = "SELECT DocumentTitle Id FROM Document d "
+ "INNER JOIN ContentSearch cs ON d.This = cs.QueriedObject "
+ "WHERE CONTAINS(*, 'company')";
SearchSQL sqlObject = new SearchSQL(mySQLString);
// Executes the content search.
SearchScope searchScope = new SearchScope(os);
RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null,
new Boolean(true));
C# Example
String mySQLString = "SELECT DocumentTitle Id FROM Document d "
+ "INNER JOIN ContentSearch cs ON d.This = cs.QueriedObject "
+ "WHERE CONTAINS(*, 'company')";
SearchSQL sqlObject = new SearchSQL(mySQLString);
// Executes the content search.
SearchScope searchScope = new SearchScope(os);
IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);
Searching
Multiple Repositories
Any of the SearchScope methods
can be used to search multiple repositories. The general sequence
for specifying multiple repositories is as follows:
Java Example
// Create the ObjectStore array required as the initial SearchScope
// parameter. (Assumes you have the object store objects.)
ObjectStore[] osArray = new ObjectStore[]{os1,os2};
// Create the SearchScope instance using the constructor for multiple object
// stores, and specifying the merge mode.
SearchScope searchMultiple = new SearchScope(osArray, MergeMode.INTERSECTION);
C# Example
// Create the ObjectStore array required as the initial SearchScope
// parameter. (Assumes you have the object store objects.)
IObjectStore[] osArray = new IObjectStore[]{os1,os2};
SearchScope searchMultiple = new SearchScope(osArray, MergeMode.INTERSECTION);
See
Merge
Mode for more information about merge mode settings.
Searching
for Objects
Use the SearchScope.fetchObjects method
to search for objects in a repository. The fetchObjects method
returns a collection of IndependentObject objects.
The general sequence for performing a query for objects is as follows:
Java Example
// Create a SearchSQL instance and specify the SQL statement (using the
// helper methods).
SearchSQL sqlObject = new SearchSQL();
sqlObject.setSelectList("d.DocumentTitle, d.Id");
sqlObject.setMaxRecords(20);
sqlObject.setFromClauseInitialValue("Document", "d", false);
// Check the SQL statement.
System.out.println("SQL: " + sqlObject.toString());
// Create a SearchScope instance. (Assumes you have the object store
// object.)
SearchScope search = new SearchScope(os);
// Set the page size (Long) to use for a page of query result data.
This value is passed
// in the pageSize parameter. If null, this defaults to the value of
// ServerCacheConfiguration.QueryPageDefaultSize.
Integer myPageSize = new Integer(100);
// Specify a property filter to use for the filter parameter, if needed.
// This can be null if you are not filtering properties.
PropertyFilter myFilter = new PropertyFilter();
int myFilterLevel = 1;
myFilter.setMaxRecursion(myFilterLevel);
myFilter.addIncludeType(new FilterElement(null, null, null,
FilteredPropertyType.ANY, null));
// Set the (Boolean) value for the continuable parameter. This indicates
// whether to iterate requests for subsequent pages of result data when the
end of the
// first page of results is reached. If null or false, only a single page of
results is
// returned.
Boolean continuable = new Boolean(true);
// Execute the fetchObjects method using the specified parameters.
IndependentObjectSet myObjects = search.fetchObjects(sqlObject, myPageSize,
myFilter, continuable);
C# Example
// Create a SearchSQL instance and specify the SQL statement (using the
// helper methods).
SearchSQL sqlObject = new SearchSQL();
sqlObject.SetSelectList("d.DocumentTitle, d.Id");
sqlObject.SetMaxRecords(20);
sqlObject.SetFromClauseInitialValue("Document", "d", false);
// Check the SQL statement.
System.Console.WriteLine("SQL: " + sqlObject.ToString());
// Create a SearchScope instance. (Assumes you have the object store
// object.)
SearchScope search = new SearchScope(os);
// Set the page size (Long) to use for a page of query result data.
This value is passed
// in the pageSize parameter. If null, this defaults to the value of
// ServerCacheConfiguration.QueryPageDefaultSize.
int myPageSize = 100;
// Specify a property filter to use for the filter parameter, if needed.
// This can be null if you are not filtering properties.
PropertyFilter myFilter = new PropertyFilter();
int myFilterLevel = 1;
myFilter.SetMaxRecursion(myFilterLevel);
myFilter.AddIncludeType(new FilterElement(null, null, null,
FilteredPropertyType.ANY, null));
// Set the (boolean) value for the continuable parameter. This indicates
// whether to iterate requests for subsequent pages of result data when the
end of the
// first page of results is reached. If null or false, only a single page of
results is
// returned.
bool continuable = true;
// Execute the fetchObjects method using the specified parameters.
IIndependentObjectSet myObjects = search.FetchObjects(sqlObject, myPageSize,
myFilter, continuable);
Searching
for Database Rows
Use the SearchScope.fetchRows method
to search rows in the Content Engine database.
The fetchRows method returns a collection of RepositoryRow objects.
The general sequence for performing a query for database rows is as
follows:
Java Example
// Create a SearchSQL instance and specify the SQL statement (using the
helper methods).
SearchSQL sqlObject = new SearchSQL();
sqlObject.setSelectList("d.DocumentTitle, d.Id");
sqlObject.setMaxRecords(20);
sqlObject.setFromClauseInitialValue("Document", "d", false);
// Check the SQL statement.
System.out.println("SQL: " + sqlObject.toString());
// Create a SearchScope instance. (Assumes you have the object store object.)
SearchScope search = new SearchScope(os);
// Set the page size (Long) to use for a page of query result data. This value
is passed
// in the pageSize parameter. If null, this defaults to the value of
// ServerCacheConfiguration.QueryPageDefaultSize.
Integer myPageSize = new Integer(100);
// Specify a property filter to use for the filter parameter, if needed.
// This can be null if you are not filtering properties.
PropertyFilter myFilter = new PropertyFilter();
int myFilterLevel = 1;
myFilter.setMaxRecursion(myFilterLevel);
myFilter.addIncludeType(new FilterElement(null, null, null,
FilteredPropertyType.ANY, null));
// Set the (Boolean) value for the continuable parameter. This indicates
// whether to iterate requests for subsequent pages of result data.
Boolean continuable = new Boolean(true);
// Execute the fetchRows method using the specified parameters.
RepositoryRowSet myRows = search.fetchRows(sqlObject, myPageSize, myFilter,
continuable);
// You can then iterate through the collection of rows to access the properties.
int rowCount = 0;
Iterator iter = myRows.iterator();
while (iter.hasNext()) {
RepositoryRow row = (RepositoryRow) iter.next();
String docTitle = row.getProperties().get("DocumentTitle").getStringValue();
Id docId = row.getProperties().get("Id").getIdValue();
rowCount++;
System.out.print(" row " + rowCount + ":");
System.out.print(" Id=" + docId.toString());
if (docTitle != null) {
System.out.print(" DocumentTitle=" + docTitle);
}
System.out.println();
}
C# Example
// Create a SearchSQL instance and specify the SQL statement (using the helper
methods).
SearchSQL sqlObject = new SearchSQL();
sqlObject.SetSelectList("d.DocumentTitle, d.Id");
sqlObject.SetMaxRecords(20);
sqlObject.SetFromClauseInitialValue("Document", "d", false);
// Check the SQL statement.
System.Console.WriteLine("SQL: " + sqlObject.ToString());
// Create a SearchScope instance. (Assumes you have the object store object.)
SearchScope search = new SearchScope(os);
// Set the page size (Long) to use for a page of query result data. This value
is passed
// in the pageSize parameter. If null, this defaults to the value of
// ServerCacheConfiguration.QueryPageDefaultSize.
int myPageSize = 100;
// Specify a property filter to use for the filter parameter, if needed.
// This can be null if you are not filtering properties.
PropertyFilter myFilter = new PropertyFilter();
int myFilterLevel = 1;
myFilter.SetMaxRecursion(myFilterLevel);
myFilter.AddIncludeType(new FilterElement(null, null, null,
FilteredPropertyType.ANY, null));
// Set the (boolean) value for the continuable parameter. This indicates
// whether to iterate requests for subsequent pages of result data.
bool continuable = true;
// Execute the fetchRows method using the specified parameters.
IRepositoryRowSet myRows = search.FetchRows(sqlObject, myPageSize, myFilter,
continuable);
// You can then iterate through the collection of rows to access the properties.
int rowCount = 0;
foreach(IRepositoryRow row in myRows)
{
string docTitle = row.Properties.GetProperty("DocumentTitle").GetStringValue();
Id docId = row.Properties.GetProperty("Id").GetIdValue();
rowCount++;
System.Console.WriteLine(" row " + rowCount + ":");
System.Console.WriteLine(" Id=" + docId.ToString());
if (docTitle != null)
{
System.Console.WriteLine(" DocumentTitle=" + docTitle);
}
System.Console.WriteLine();
}
Searching
for Metadata
Use the SearchScope.fetchSearchableClassDescriptions method
to search for metadata. The fetchSearchableClassDescriptions method
returns a collection of ClassDescription objects.
There is no SearchSQL parameter for this method.
The general sequence for performing a query for metadata is as follows:
Java Example
// Create a SearchScope instance. (Assumes you have the object store
// object.)
SearchScope search = new SearchScope(os);
// Specify the names of the classes containing the metadata you want to find.
This must
// be a String array of the identifiers (symbolic names, display names,
or object IDs).
String[] myClassNames = new String[]{"Document", "Annotation"};
// Specify a property filter to use for the filter parameter, if needed.
// This can be null if you are not filtering properties.
PropertyFilter myFilter = new PropertyFilter();
int myFilterLevel = 1;
myFilter.setMaxRecursion(myFilterLevel);
myFilter.addIncludeType(new FilterElement(null, null, null,
FilteredPropertyType.ANY, null));
// Execute the fetchSearchableClassDescriptions method using the specified
// parameters.
ClassDescriptionSet myMetadata =
search.fetchSearchableClassDescriptions(myClassNames, myFilter);
C# Example
// Create a SearchScope instance. (Assumes you have the object store
// object.)
SearchScope search = new SearchScope(os);
// Specify the names of the classes containing the metadata you want to
find. This must
// be a String array of the identifiers (symbolic names, display names,
or object IDs).
string[] myClassNames = new string[] { "Document", "Annotation" };
// Specify a property filter to use for the filter parameter, if needed.
// This can be null if you are not filtering properties.
PropertyFilter myFilter = new PropertyFilter();
int myFilterLevel = 1;
myFilter.SetMaxRecursion(myFilterLevel);
myFilter.AddIncludeType(new FilterElement(null, null, null,
FilteredPropertyType.ANY, null));
// Execute the fetchSearchableClassDescriptions method using the specified
// parameters.
IClassDescriptionSet myMetadata = search.FetchSearchableClassDescriptions
(myClassNames, myFilter);
Searching for Objects Using a Stored Search
To use a stored search to query for objects in
a repository, call the SearchScope.fetchObjects method
having StoredSearch, rather than SearchSQL as
the initial argument in its signature. The fetchObjects method
returns a collection of IndependentObject objects.
The example executes a stored search existing in the repository.
Java Example
// Create a SearchScope instance. (Assumes you have the object store
// object.)
SearchScope search = new SearchScope(os);
// Get the StoredSearch object.
StoredSearch ss=Factory.StoredSearch.fetchInstance(os, new
(Id( "{9FEC3C69-57B2-4E29-872A-0EE452881555}"), null);
// Set the search parameters. Search template parameters only need to be
provided if the persisted stored search needs to be modified at runtime.
SearchTemplateParameters searchParams = new SearchTemplateParameters();
searchParams.setContent(null);
searchParams.setMaximumRecords(50);
SearchTemplateWhereProperty whereProp = new SearchTemplateWhereProperty();
whereProp.setLiteral("application/vnd.oasis.opendocument.text");
whereProp.setItemId("35");
ArrayList alWhereProps = new ArrayList();
alWhereProps.add(whereProp);
searchParams.setWhereProperties(alWhereProps);
SearchTemplateSelectProperty selectProp = new SearchTemplateSelectProperty();
selectProp.setSymbolicName ("DocumentTitle");
selectProp.setItemId("1");
selectProp.setSortLevel(0));
selectProp.setSortOrder(SortOrder.DESCENDING);
ArrayList alSelectProps = new ArrayList();
alSelectProps.add(selectProp);
searchParams.setSelectProperties(alSelectProps);
// Set the page size (Long) to use for a page of query result data.
This value is passed
// in the pageSize parameter. If null, this defaults to the value of
// ServerCacheConfiguration.QueryPageDefaultSize.
Integer myPageSize = new Integer(100);
// Specify a property filter to use for the filter parameter, if needed.
// This can be null if you are not filtering properties.
PropertyFilter myFilter = new PropertyFilter();
myFilter.setMaxRecursion(1);
myFilter.addIncludeType(new FilterElement(null, null, null,
FilteredPropertyType.SINGLETON_STRING, null));
// Set the (Boolean) value for the continuable parameter. This indicates
// whether to iterate requests for subsequent pages of result data when the
end of the
// first page of results is reached. If null or false, only a single page of
results is
// returned.
Boolean continuable = Boolean.TRUE;
// Execute the fetchObjects method using the specified parameters.
IndependentObjectSet myObjects = search.fetchObjects(ss, "document",
searchParams, myPageSize, myFilter, continuable);
// You can then iterate through the collection of objects to access the properties.
Iterator iterObjects = myObjects.iterator();
while (iterObjects.hasNext())
{
IndependentObject object = (IndependentObject) iterObjects.next();
Properties props = object.getProperties();
Iterator iterProps = props.iterator();
while (iterProps.hasNext() )
{
Property prop = (Property)iterProps.next();
System.out.print("\nProperty: " + prop.getPropertyName() );
if ( prop.getObjectValue() != null )
System.out.print(" Value: " + prop.getObjectValue().toString() );
if (prop.getPropertyName().equalsIgnoreCase("FoldersFiledIn"))
{
if ( prop.getObjectValue() != null )
{
FolderSet fs = (FolderSet)prop.getIndependentObjectSetValue();
Iterator iterFs = fs.iterator();
while (iterFs.hasNext())
{
Folder folder = (Folder)iterFs.next();
System.out.print("\r\tFolder Name: " + folder.get_FolderName() +
" Folder Path: " + folder.get_PathName());
}
}
}
}
}
System.out.println("\nFinished searchForObjects");
C# Example
// Create a SearchScope instance. (Assumes you have the object store object.)
SearchScope search = new SearchScope(os);
// Get the StoredSearch object.
IStoredSearch ss = Factory.StoredSearch.FetchInstance
(os, new Id( "{9FEC3C69-57B2-4E29-872A-0EE452881555}"), null);
// Set the search parameters. Search template parameters only need to
be provided if the persisted stored search needs to be modified at runtime.
SearchTemplateParameters searchParams = new SearchTemplateParameters();
searchParams.Content=null;
searchParams.MaximumRecords=25;
SearchTemplateWhereProperty whereProp = new SearchTemplateWhereProperty();
whereProp.Literal="application/vnd.oasis.opendocument.spreadsheet";
whereProp.ItemId="35";
IList<SearchTemplateWhereProperty> ListWhereProps =
new List<SearchTemplateWhereProperty>();
ListWhereProps.Add(whereProp);
searchParams.WhereProperties = ListWhereProps;
SearchTemplateSelectProperty selectProp = new SearchTemplateSelectProperty();
selectProp.SymbolicName = "DocumentTitle";
selectProp.ItemId="1";
selectProp.SortLevel=0;
selectProp.SortOrder=SortOrder.NONE;
IList<SearchTemplateSelectProperty> ListSelectProps = new List<SearchTemplateSelectProperty>();
ListSelectProps.Add(selectProp);
searchParams.SelectProperties = ListSelectProps;
// Set the page size (Long) to use for a page of query result data.
This value is passed
// in the pageSize parameter. If null, this defaults to the value of
// ServerCacheConfiguration.QueryPageDefaultSize.
int myPageSize = 100;
// Specify a property filter to use for the filter parameter, if needed.
// This can be null if you are not filtering properties.
PropertyFilter myFilter = new PropertyFilter();
myFilter.SetMaxRecursion(1);
myFilter.AddIncludeType(new FilterElement(null, null, null,
FilteredPropertyType.SINGLETON_STRING, null));
// Set the (boolean) value for the continuable parameter. This indicates
// whether to iterate requests for subsequent pages of result data when the
end of the
// first page of results is reached. If null or false, only a single page of
results is
// returned.
bool continuable = true;
// Execute the fetchObjects method using the specified parameters.
IIndependentObjectSet myObjects = search.FetchObjects(ss, "document", searchParams myPageSize, myFilter, continuable);
// You can then iterate through the collection of rows to access the properties.
foreach (IIndependentObject obj in myObjects)
{
IProperties props = obj.Properties;
foreach (IProperty prop in props)
{
System.Console.Write("\nProperty: " + prop.GetPropertyName());
if ( prop.GetObjectValue() != null )
System.Console.Write(" Value: " + prop.GetObjectValue().ToString());
if (prop.GetPropertyName().Equals("FoldersFiledIn"))
{
if ( prop.GetObjectValue() != null)
{
IFolderSet fs = (IFolderSet)prop.GetIndependentObjectSetValue();
foreach (IFolder folder in fs)
{
System.Console.WriteLine("\n\tFolder Name: "
+ folder.FolderName +
" Folder Path: " + folder.PathName);
}
}
}
}
}
Querying Partitions
IBM Content
Search Services support
partitioning by date to address high-volume indexing of date-sensitive
documents, such as e-mail messages archived in a Content Engine repository. IBM Content
Search Services also supports partitioning
documents that have specific values of certain string properties,
as well as partitioning documents by both date and string. By using
partitioning, you can reduce the number of IBM Content
Search Services indexes that need to
be searched, thus reducing query retrieval time.
Querying Using Date Partitions
To
search IBM Content
Search Services index
partitions using date partitioning, the WHERE clause of a query must
contain conditions on the date/time partitioning property; otherwise,
all indexes or collections will be searched. For example, assume that
a class has a custom property called "receivedDate" on which to partition
documents, and the object store on which the class resides is configured
to use "receivedDate" as the partitioned property, then the following
query statement invokes a search on partitioned indexes or collections.
SELECT … FROM Document D INNER JOIN ContentSearch CS ON
D.This=CS.QueriedObject WHERE CONTAINS(*,'dog') AND
D.receivedDate >= 2008-10-26 AND D.receivedDate < 2009-03-25
The above query narrows the search to those
indexes or collections with date ranges that overlap the specified
range of values for the receivedDate property. If no indexes or collections
cover the specified range of dates, then the query would return zero
results.
For the search to be optimized, you must specify
the conditions referencing the partitioning property (receivedDate
in the above example) following the CONTAINS clause; otherwise, the
parsing of the query can result in a search that scans the entire
database, rather than the CBR index data identified in the CONTAINS
clause.
Querying
Using String Partitions
To search IBM Content
Search Services index partitions, the
WHERE clause of a query must contain conditions on the string property;
otherwise, all indexes will be searched. For example, assume that
a class has a custom property called "tenant" on which to partition
documents, and the object store on which the class resides is configured
to use "tenant" as the partitioned property, then the following query
statement invokes a search on partitioned indexes.
SELECT ... FROM Document D INNER JOIN ContentSearch CS ON
D.This=CS.QueriedObject WHERE CONTAINS(*,'dog') AND
D.tenant='IBM'
Best
Practices for Searches
Using Administration
Console
Use the Administration Console for Content Platform
Engine's Search tool to construct
your query or to quickly validate that your query works as intended.
Be sure to include the object reference "this" in the SELECT clause
when attempting to execute any query examples (included in this documentation).
For example, this query:
SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc'
must be entered into Query Builder
in this form:
SELECT d.this, d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc'
Querying
a DB2® Database
Limit Rows Returned
Setting the values
of certain properties can prevent a user from allocating excessive
memory when making requests to the Content Engine server — a situation that
could cause the server to become slow due to memory limitations.
Limit the number of rows returned from your query to a usable
number, either by specifying your query's row selection criteria to
achieve that result, or by deciding on that number in advance by setting
the following
ServerCacheConfiguration properties: QueryPageMaxSize,
QueryPageDefaultSize, and NonPagedQueryMaxSize.
For
queries that use the SQL option of
COUNT_LIMIT to
request a search result count, limit the number of rows that the
Content Engine counts with the following
ServerCacheConfiguration properties:
QueryCountDefaultSize and QueryCountMaxSize.
In addition,
the
ObjectStore interface provides properties that
limit the maximum amount of time that a query can consume. The DefaultQueryTimeLimit
and the MaxQueryTimeLimit properties impose time limits on client-to-server
query RPCs, and the QueryDatabaseTimeout property limits query execution
at the database level.
Avoid Non-Indexed Ordering and
Searching
Avoid referencing any non-indexed
column in a JOIN, WHERE, or ORDER BY clause. For example, optimize
the following query by creating an index on Document.DocumentTitle
(database column DocVersion.xxx_documenttitle):
SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc'
Note: The Document class
maps to the DocVersion table.
Also, avoid WHERE
clauses with the LIKE operator when the searched-for column value
does not permit the effective use of indexes. For example:
SELECT d.Id FROM Document d WHERE d.DocumentTitle LIKE '%abc'
In this example, even if an index exists for
the DocumentTitle column, the query cannot use it due to the leading
wildcard ("%") in the searched-for column value. Code your query to
return a relatively small number of rows by placing a sufficient number
of characters before the wildcard. In search dialogs, allow end users
to perform "Starting with" searches (such as for "abc%") but not "Contains"
searches (such as for "%abc%").
Queries that do not
make effective use of indexes can potentially cause the database to
lock the table. Other users can then be blocked from updating the
table for the duration of the query, causing checkins to time out,
and other such problems.
Avoid
Non-Function-Indexed Case Insensitive Comparisons (Oracle/DB2)
Avoid any column value comparison resulting from a
JOIN, WHERE, or ORDER BY clause for any non-indexed column, or for
any column belonging to an index, that does not directly or indirectly
use the LOWER function. You should follow this guideline in these
circumstances:
- You are working with an object store that uses Oracle or DB2 as the database engine.
- You have forced case insensitive searches by setting the ObjectStore.ForceCaseInsensitiveSearch
property to true, and the Oracle or DB2 database has not been natively
configured for case insensitivity (and so setting ForceCaseInsensitiveSearch
to true has a practical effect).
For DB2,
generate a column by applying the LOWER function to a pre-existing
column, and then create an index on the generated column. For Oracle,
create a function based index. For example, the index LOWER(DocVersion.xxx_documenttitle)
makes the following queries more efficient:
SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc'
SELECT d.Id, d.DocumentTitle, d.Creator FROM Document
d ORDER BY d.DocumentTitle
Note: The Document class
maps to the DocVersion table.
Avoid Non-Indexed Property Searching
Ensure that at least one WHERE condition property
is selective (it restricts the rows returned) and is indexed. For
example, optimize the following query by creating an index on Document.DocumentTitle:
SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc'
Note: The Document class maps
to the DocVersion table.
Also, avoid WHERE clauses
with the LIKE operator when the searched-for column value does not
permit the effective use of indexes. For example:
SELECT d.Id FROM Document d WHERE d.DocumentTitle LIKE '%abc'
In this example, even if an index exists for
the DocumentTitle column, the query cannot use the index due to the
leading wildcard ("%") in the searched-for column value. Code your
query to return a relatively small number of rows by placing a sufficient
number of characters before the wildcard. In search dialogs, allow
end users to perform "Starting with" searches (such as for "abc%")
but not "Contains" searches (such as for "%abc%").
Queries
that do not make effective use of indexes can cause the database to
escalate locks to the page and table level. Other users can then be
blocked from updating the table for the duration of the query, causing
checkins to time out and other related problems.
Avoid Unnecessary Object Type Searches
Avoid unnecessarily searching for subclass types by adding the
EXCLUDESUBCLASSES operator to your query (a query has an implicit
INCLUDESUBCLASSES operator by default).
For example,
as a result of the implicit INCLUDESUBCLASSES, the following query
can return objects belonging to a Document subclass, in addition to
those belonging to the Document class:
SELECT d.Id FROM Document d WHERE DocumentTitle = 'MyDoc'
Presuming only objects from the Document class
are needed, instead of writing the query as:
SELECT d.Id FROM Document d WHERE DocumentTitle = 'MyDoc' AND ISCLASS(d,
Document)
use the EXCLUDESUBCLASSES operator in this
manner:
SELECT d.Id FROM Document d WITH EXCLUDESUBCLASSES WHERE DocumentTitle =
'MyDoc'
See
SQL
Syntax Reference for more information about the EXCLUDESUBCLASSES
and INCLUDESUBCLASSES operators.
Avoid
Unnecessary Column Returns
Avoid returning all
of the columns in a table when only some are needed. For example,
instead of:
SELECT d.* FROM Document d WHERE d.DocumentTitle = 'MyDoc'
specify the needed columns, as in this example:
SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc'
This minimizes the amount of data transmitted
over the network. Also, when all of the columns in the SELECT clause
are indexed, the data for the query might be retrieved directly from
the index, making the physical row lookup unnecessary.
Avoid Complex
Table Linkages
Avoid referencing three or more
tables in a query. More tables can degrade query performance, and
complicate performance tuning efforts.
Avoid
Unnecessary Result Row Ordering
Avoid an unnecessary
ORDER BY clause. Explicit row ordering may be unnecessary when you
can rely on your query returning its results in a particular order
as a side effect of an indexed search. For example, the ORDER BY clause
in the following query may be unnecessary since a composite index
exists for the Container table on (parent_container_id, name):
SELECT f.FolderName FROM Folder f WHERE f.Parent = Object('/sub1/sub1a')
AND f.IsHiddenContainer = false ORDER BY f.FolderName
Note: The Folder class maps to the
Container table.
With a non-continuable search,
it is best to remove either the ORDER BY, or ORDER BY f.Parent, F.FolderName
clause on the existing columns in the composite index.
With a continuable search, it is best to use the ORDER BY f.Parent,
F.FolderName clause; otherwise, when the ORDER BY Id is automatically
appended to the search, a sort operation will be necessary.
As another example, rather than basing a search on the indexed
property OrderDate:
select D.OrderDate, D.OrderNumber from Document d where d.OrderDate < ?
ORDER BY D.OrderNumber
If the search results do not require a sort
on OrderNumber, search on OrderDate instead:
select D.OrderDate, D.OrderNumber from Document d where d.OrderDate < ?
ORDER BY D.OrderDate
By using OrderDate for the ORDER BY clause:
- You avoid a sort step in the query, because the query will be
using the OrderDate index.
- The database query optimizer will tend to use the index on OrderDate.
- With continuable searches, when there is a composite index on
"OrderDate, Id" the ORDER BY D.OrderDate clause is required to ensure
the SORT operation is removed. The Content Engine automatically appends an
ORDER BY Id to the search.
Avoid Property Searches using the LIKE operator and Force
Case-Insensitive Search (DB2 LUW)
Enabling Force
Case-Insensitive Search (FCIS) for an object store causes Content
Engine queries to be issued with the
LOWER operator on string
columns and values in the
WHERE clause. In order for the
search to be fast, an index must be created on a generated column
which uses the
LOWER function. For instructions on indexing
properties when using FCIS, see
Creating database indexes for case-insensitive search.
Note, however, that for a DB2 LUW database, searches
will not use an index if the LIKE operator is used and FCIS
is enabled. This is a limitation of DB2 LUW. DB2 cannot rewrite
a query condition that uses LOWER to access the generated index
for that column. This limitation results in slow searches when using
the LIKE operator and FCIS.
For example, this
Content Engine SQL cannot use a generated column index on DB2 LUW:
WHERE myProperty LIKE 'abc%'
Therefore, whenever possible, avoid using LIKE on
DB2 LUW with FCIS, for single-value property searches and especially
for multi- valued property searches.
Regardless of the
database type and the FCIS setting on the object store, keep in mind
that poorly constructed property searches result in poor retrieval
performance. For example:
- Avoid property searches that cannot leverage column indexes.
A database index cannot be used in a query using the
LIKE operator
with a wildcard at the front, for example:
WHERE myListProperty LIKE '%abc%'
A much faster search avoids
LIKE, for example:
WHERE 'abc' IN myListProperty
When
using the IBM FileNet Workplace XT or IBM Content
Navigator client application,
which provides predefined lists of SQL operators, avoid
the CONTAINS operator or the LIKE operator with a wildcard
at the front. Instead, use the INCLUDES or INCLUDE ALL operator.
- For multiple list property conditions separated by the OR operator,
use the INTERSECTS operator. See Use INTERSECTS Operator
for queries with multivalued properties.
Avoid Subqueries (Oracle)
Avoid using
subqueries and operators that indirectly generate subqueries whenever
you are querying an object store that uses Oracle as the database
engine. Potential subquery related issues exist with the Oracle optimizer.
Specifically, use an INNER JOIN instead of a subquery (however, see
also the guideline on
avoid complex
table linkages). For example, rewrite the potentially slow
query:
SELECT d.Id FROM Document d WHERE d.Id IN (SELECT b.Bp8ObjectGuid FROM Bp8Attachment b)
in this functionally equivalent form:
SELECT d.Id FROM Document d INNER JOIN Bp8Attachment b ON d.Id = b.Bp8ObjectGuid
Also, because the Content Engine uses a subquery to implement
the INFOLDER operator, avoid using that operator. For example, rewrite
the query:
SELECT f.FolderName FROM Folder f WHERE f.this INFOLDER '/sub1/sub1a' AND f.IsHiddenContainer = false
in this manner:
SELECT f.FolderName FROM Folder f WHERE f.Parent = OBJECT('/sub1/sub1a') AND f.IsHiddenContainer = false
Also, rewrite a query like this:
SELECT d.id FROM Document d WHERE d.This INFOLDER '/sub1/sub1a'
in this more efficient form:
SELECT d.Id FROM Document d INNER JOIN ReferentialContainmentRelationship r ON d.This = r.Head
WHERE r.Tail = OBJECT('/sub1/sub1a')
Use INTERSECTS Operator for queries
with multivalued properties
Avoid queries
that contain two or more multivalued property conditions in an OR
clause. Such queries can result in query timeouts or unacceptable
query performance because it can be difficult for the database engine
to create an optimal query plan.
For example, do not
use a query like the following:
SELECT
d.Id FROM Document WHERE 'value1' IN ListPropertyString OR 'value2'
IN ListPropertyString OR 'value3' IN ListPropertyString
Instead, use the INTERSECTS operator, as follows.
It greatly improves performance.
SELECT
d.Id FROM Document WHERE ListPropertyString INTERSECTS ('value1',
'value2', 'value3')
Avoid
search delays caused by security filtering
Avoid
searches with large result sets that filter documents on security
to a small number of rows.
Content Engine applies security and removes
a user's access to objects after the objects are retrieved
from the database engine. When a search finds a large number of rows
(more than a few thousand) in the database, the total time to retrieve
all of the rows (the database-to-Content Engine round
trip time) can be considerable. If the search is continuable and there
is little security filtering, the first page can be returned quickly,
but if security filtering greatly limits the rows, many rows will
have to be retrieved in order to fill the page.
To
avoid this situation, design your application such that the search
criteria includes a limiting property or other criteria (such as belonging
to a particular folder) on which to filter. As an example, you could
create a custom property, such as myRole, and add the property as
additional criteria to your query in the form of "WHERE myProperty
= 'myRole'". You could also choose to restrict the document
search to a specific folder, such as "WHERE ... INFOLDER 'myFolder'".
Query Syntax
See the
SQL
Syntax Reference documentation for information on how to construct
SQL statements. SQL statements need to follow the IBM FileNet standard,
which generally conforms to SQL-92, with extensions for IBM FileNet specific
constructs.
