SQL Table Name: Ens.CustomSearchTable
Base class for custom search tables that can implement alternative indexing strategies to those offered by the standard SearchTable structure. Note that this class does NOT support collection properties. Users wishing to create their own custom SearchTable must extend this class and can add any properties and indices they see fit. Users should consider which fields they wish to store on disk as properties of the CustomSearchTable, and which fields they want to retrieve at query time as "virtual" fields. See the documentation for GetPropertyList()() for more details regarding the behaviour of standard properties. If any virtual fields are desired, then the GetVirtualPropertyList()() and GetVirtualProperty()() methods need to be overridden to list and retrieve named virtual properties, respectively. Note that the default approach for querying on non-indexed fields is to display them in the query results without applying any conditions to the values. However, users may choose to modify the generated SQL clauses for each condition by overriding the OnProcessCondition()() method to implement more complex logic. Irrespective of any other decisions, the user MUST override the OnIndexDoc()() method, which populates the CustomSearchTable object.
For even more control over the generated SQL, users can override the PROCESSTUPLES parameter to ensure that the OnProcessTuple()() callback is invoked during query generation. The OnProcessTuple()() callback gives users a tree of related conditions for a given SearchTable so that users can generate custom SQL conditions for groups of conditions, rather than the per-property approach available via the OnProcessCondition()() callback. See the documentation for OnProcessTuple() to get more details about this tree-based approach.
Helper method to allow developers to inform users about possibly problematic search criteria. If the current custom search table has been specified in any criteria, then this method will be called once by the UI. If the requested search may be slow, then the developer can append any number of message strings to pWarnings. If any strings are returned, all the returned strings will be displayed to the user, and the user can make a decision as to whether he or she wishes to continue with the search as constructed.
Note: this method is called before we attempt to generate any SQL to ensure that the user gets close to immediate feedback on his/her search.
Method to list the properties and indexed properties of a given CustomSearchTable. By default, this is code generated, but this method may be overridden in subclasses to implement different behaviour. Both output variables should return a Caché list of properties. pIndexedProperties should contain a list of indexed properties available in the CustomSearchTable, and pProperties should contain a list of available properties in the CustomSearchTable. The distinction is to allow the UI to list indexed properties before non-indexed properties.
Note that virtual properties are also supported, but should be listed using GetVirtualPropertyList()(). Such a construct permits the retrieval of arbitrary data from the database either by constructing custom SQL for the term based on the value, or by invoking a callback function after the initial SQL results have been produced.
Callback invoked by the Message Viewer UI whenever a condition for a CustomSearchTable is selected AND the condition has not been marked as complete by the OnProcessTuple()() callback. The arguments for the method are as follows:
- pProperty specifies the property name to be retrieved.
Note: pProperty may be the empty string when adding a SearchTable for the first time. Ensure that the code in this method handles this case.
- pDisplayOnly indicates whether the user has selected the property as a display-only field. If pDisplayOnly is true, the values for the value and operator supplied in pValue and pOperator, respectively, should be ignored and no conditions should be added to the WHERE clause. If pDisplayOnly is false, users should make use of the GetSQLCondition() API in EnsPortal.MsgFilter.Assistant to produce valid SQL WHERE conditions based on the supplied arguments.
- pTableName specifies the name of the table to use in the FROM clause.
- pTableInFrom indicates whether the table is already present in the FROM clause.
- pSelectAsName is the alias which should be used for the column in the eventual resultset.
- The pSelect, pFrom and pWhere arguments are strings which determine which subclauses (if any) should be added to the corresponding sections of the overall SQL query, and can be modified as needed while in this callback.
- The pSQLFetch flag indicates whether the value will be completely fetched by the supplied SQL terms. By default, ALL virtual properties will only be retrieved using ObjectScript AFTER the main SQL results have been produced. The purpose of this is to allow the post-SQL filter code to load all encountered virtual properties using the GetVirtualProperties()() API. Users should override this behaviour when indices are available in this class to improve the fetch performance of the generated query. In a similar vein, users may want to delay retrieval of non-indexed standard properties until after the SQL phase is complete, though this is not necessarily more efficient.
Callback method invoked to process a single CustomSearchTable EnsPortal.MsgFilter.Term that contains one or more EnsPortal.MsgFilter.Condition objects. The structure of the Term object is converted into the pTuple array, which represents the logical structure of the Term and contains Condition objects as leaves.
pTuple has the following contents:
The "join" subscript of pTuple is either "AND" or "OR", and indicates how the data in the
numeric subscripts of the current node in pTuple should be combined.
Each numeric subscript is then either a full-fledged condition OR a join. If the node is a join, it has the following structure:
pTuple(n,"join") : = "AND" || "OR" pTuple(n,"sqlFetch") = 0 pTuple(n,"done") = 0If a node is a join, it may contain further child joins to reflect complex combinations of AND and ORs entered through the portal. However, in most simple cases, the join will contain numeric subscripts with full conditions. Users should set the "sqlFetch" subscript to 1 if the property will be fetched using SQL, and the "done" subscript should be set to 1 if no further processing of that condition should take place using the OnProcessCondition()() callback. Users should ensure that they update the "sqlFetch" and "done" values for ALL nodes in pTuple that are handled in this callback, including the "join" nodes.
If the node is a condition it has the following structure:
pTuple(n) := [EnsLib.MsgFilter.Condition] pTuple(n,"sqlFetch") = 0 pTuple(n,"done") = 0 pTuple(n,"path") = [path in original parse tree]As is the case for "join" nodes, users should set the "sqlFetch" subscript to 1 if the property will be fetched using SQL, and the "done" subscript should be set to 1 if no further processing of that condition should take place (i.e. OnProcessCondition()() will not be called). Note that the "Prop", "OpVal", "Op" and "DisplayOnly" values of the Condition objects are populated; the "JoinOp" property is NOT populated when in a node of pTuple. pTableName is the name of the SQL table for the CustomSearchTable.
pTableInFrom is a flag to indicate whether the table has already been added to the FROM clause.
pSelectPrefix is the prefix to use for the column names added to the SELECT clause to help avoid collisions.
pSelect, pFrom and pWhere are strings that initially contain the expected SQL SELECT, FROM and WHERE subclauses for the tuple. Users should modify these values as needed for use in the generated SQL query.
pSqlFetch is a flag to indicate whether the main Term specifying the CustomSearchTable should be fetched using SQL. This value is set to 1 by default.
Note: this index prevents multiple CustomSearchTable objects in the same extent from indexing a single document.