Wednesday, August 5, 2015

Working with Queries - Constructing a SQL Statement

IBM FileNet P8, Version 5.2            

Working with Queries


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);
Refer to the Searching for Database Rows example code for an illustration of iterating through the RepositoryRowSet.
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);
Refer to the Searching for Database Rows example code for an illustration of iterating through the RepositoryRowSet.
See SQL Syntax Reference for detailed information about SQL statement syntax.

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.
See Content Searches and CBR Queries for more information.
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);
Refer to the Searching for Database Rows example code for an illustration of iterating through the RepositoryRowSet.
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);
See Constructing a SQL Statement for the general sequence and examples.
See Searching Multiple Repositories for the sequence and examples when searching multiple object stores.

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();                        
}
See Constructing a SQL Statement for the general sequence and examples.
See Searching Multiple Repositories for the sequence and examples.

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);
See Searching Multiple Repositories for the sequence and examples.

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'
For more information on searching with Administration Console for Content Platform Engine, see Finding objects with queries.
Querying a DB2® Database
If DB2 table overflow support is enabled on an object store, queries against the DB2 database can fail under certain conditions. For more information, see DB2 Table Overflow Support: Query Impact.
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 more information, see Avoid Non-Indexed Property Searching and Avoid Non-Indexed Ordering and Searching.
  • 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')
See SQL Syntax Reference for more information about the INFOLDER operator.
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')
For more information, see INTERSECTS Operator.
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.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)