-
Notifications
You must be signed in to change notification settings - Fork 4
Developers Guide
[TOC]
What has been provided in this intial release is software that will allow multiple instances of Elasticsearch search-engines to be federated. The intention is that by replacing the two stored procedures DESCRIBE_REPO and OPENSEARCH many other types of search-engine will be able to join the Open Federatated Search club. This page provides guidance on developing these two stored procedures.
Of course, in addition to this guidance, readers can review the procedures provided for Elasticsearch and attempt to provide equivalent functionality.
These two stored procedures (for Elasticsearch) are in a java class called OFS_Elasticsearch, and within that class there are the two Java methods that implement the stored procedures, namely: "describeRepo" and "OpenSearch". The aim here is to provide an equivalent of this class with a name of the form "OFS_someotherSearchEngine" -- which will provide new methods with exactly the same names: "describeRepo" and "OpenSearch". (Of course on any given GaianDB node you can only store the pair of procedures for one particular search engine because the stored procedures for each type of search engine all have the same names - which is necessary to make the Federated Searching work with heterogeneous search engines, by calling a single stored procedure name for all search engines.)
The way the GaianDB system is being used here is not the standard method, which usually involves querying a "virtual table" which is mapped at each Gaian node to a local table name (and has to be configured locally to record this mapping). Open Federated Search does not operate by querying a virtual table, instead it uses GaianDB to distribute a call to a stored procedure, as follows:
To get a decription of each searchable repository:
SELECT * FROM
NEW com.ibm.db2j.GaianQuery('call DESCRIBE_REPO('' '')','with_provenance') GQ
To perform a federated search:
SELECT * FROM NEW com.ibm.db2j.GaianQuery('call OPENSEARCH(''q=hello+world&norepo=&noindex=&size=10&pw=1'')','with_provenance') GQ
Note that the argument passed to each stored procedure is surrounded by two single quotes. One signifying a string, the other being an escape character (since this all appears as an argument of the method com.ibm.db2j.GaianQuery, which itself has a string argument surrounded by a single quote).
To allow for future extension the DESCRIBE_REPO stored procedure has an input parameter - but this is currently not used. Hence we see DESCRIBE_REPO(' ' ' ')) ...indicating a blank input parameter.
For this to work across a federation, the stored procedures must all have the same table structures that they use to create the resultSets that are returned. And of course the stored procedures on each node must have the same names as the stored procedures on every other node (i.e. DESCRIBE_REPO and OPENSEARCH).
In order to interoperate with the GaianDB, each stored procedure must return an array of ResultSets, and put the results into the first entry in that array. Thus the Java method declaration of the two stored procedures and the statements that generate the results in them must be:
public static void describeRepo(String query,ResultSet[] rs){
...
rs[0]=s.executeQuery("SELECT * FROM DB_REPO_TEMP_TABLE ORDER BY LEVEL1 ASC");
}
public static void OpenSearch(String query,ResultSet[] rs){
...
rs[0]=s.executeQuery("SELECT * FROM DB_SEARCH_RESULTS");
}
Note there is no "return" parameter as such (they are class of return type "void"). The results must be put in rs[0] as shown above.
The internal name of the Java methods does not need to be identical to the name of the stored procedures. This mapping is defined when the stored procedures are created in the Derby database, using the following commands:
CREATE PROCEDURE DESCRIBE_REPO(COL1 VARCHAR(10000)) PARAMETER STYLE JAVA MODIFIES SQL DATA LANGUAGE JAVA DYNAMIC RESULT SETS 1 EXTERNAL NAME 'uk.gov.dstl.gaiandb.OFS_Elasticsearch.describeRepo';
CREATE PROCEDURE OPENSEARCH(COL1 VARCHAR(10000)) PARAMETER STYLE JAVA MODIFIES SQL DATA LANGUAGE JAVA DYNAMIC RESULT SETS 1 EXTERNAL NAME 'uk.gov.dstl.gaiandb.OFS_Elasticsearch.OpenSearch';
Of course, for a new pair of stored procedures, OFS_Elasticsearch would be replaced by the new Java class name, "OFS_someotherSearchEngine", and the new method names (preferably the same as above) would be inserted. Note in passing that the above SQL defines each stored procedure as having one argument, whereas in fact there is another argument - the ResultSet array which is automatically added.
The DESCRIBE_REPO procedure must internally use a table structure as shown below to generate its resultSet (it could be called anything so long as it has these columns):
CREATE TABLE GAIANDB.DB_REPO_TEMP_TABLE (
REPO_NAME VARCHAR(100) NOT NULL,
LEVEL1 VARCHAR(100) NOT NULL,
LEVEL2 VARCHAR(100) NOT NULL,
CONSTRAINT PK_REPO PRIMARY KEY (REPO_NAME,LEVEL1,LEVEL2)
);
What the DESCRIBE_REPO stored procedure must do is populate the DB_REPO_TEMP_TABLE as follows:
-
The first column (REPO_NAME) is the name assigned to this repository in DB_FEDSEARCH_CONFIG, in the column of the same name in that table for the respective node's IP address (which it must get from the Operating System not be hard-wired).
-
The names of the second and third columns, LEVEL1, LEVEL2, are used to describe, in a generic manner, the structure of the search engine's indexes. In Elasticsearch LEVEL1 is what they call the "index", and LEVEL2 is what they call the "type". This is a hierachy. Each "index" can operate over multiple "types" (hence LEVEL1 and LEVEL2). In other search engines different terms may be used to define the structure. In the present implementation, the LEVEL1 and LEVEL2 information is displayed to users (because the index and type-names are often indicative of the content ) but only the LEVEL 1 information can be used to restrict searches.
-
If a search engine does not have the capability to expose its LEVEL1, LEVEL2 internal structure then the DESCRIBE_REPO stored procedure shall insert a single row into this table as follows:
| REPO_NAME | LEVEL1 | LEVEL2 | | someName |not available|not available| The words "not available" must be inserted exactly as shown (lower case, one space between words)
The OPENSEARCH procedure must internally use a table structure shown below to generate its resultSet (it could be called anything so long as it has these columns):
CREATE TABLE GAIANDB.DB_SEARCH_RESULTS (
TITLE VARCHAR(500),
URL VARCHAR(500),
SOURCE_URL VARCHAR(500),
SNIPS VARCHAR(5000),
RATING INTEGER,
HIT_COUNT BIGINT,
DATE_STRING VARCHAR(30),
FROM_REPO VARCHAR(100)
);
What the OPENSEARCH stored procedure must do is:
- Accept a single argument of the OPENSEARCH stored procedure which is presented in OFS OpenSearch format. This is the same as the request format in the OFS REST API (defined here), except that the "format" parameter is omitted.
e.g. q=hello+world&noindex=&size=10&pw=1
- Parse the input argument and translate its contents into the search request that is appropriate for the search engine that is being used. Note the input argument will be URL encoded and may need to be URL decoded.
- By way of example, for Elasticsearch and a particular set of meta-data fields, the above input may translate to:
{"source": ["dc:title","dcterms:valid","URL"],"from":0, "size":10, "query":{"bool":{"must":{"simple_query_string":{"query": "hello,world","fields": ["dc:title^5","all"],"default_operator": "and"}}}},"highlight":{"fields":{"*":{"require_field_match":false,"fragment_size":120}}}}
- Parse the results of the search request to populate a row of the table GAIANDB.DB_SEARCH_RESULTS for each "hit". The meaning of the columns of this table are defined below. By way of example, after each definition is shown how this information is derived in the case of Elasticsearch; either by specifying fields to be returned in the search request, or by all responses having certain fields as standard.
TITLE: The title of the document -- "source": ["dc:title"] ;
URL: A URL, stored in the index, that the end-user can use to access the source document
-- "source":["URL"];
SOURCE_URL:In the case where all the text and meta-data extracted from a document is stored inside the index (without original formatting) provide the URL which when passed to the search engine will return the source document. This is used as a surrogate for the URL value in cases where the index does not contain a URL of the original document. In the case of Elasticsearch every "hit" has an identified "_index", "_type" and "_id". The combination of these, along with the search engine URL, can be used to construct the SOURCE_URL as follows:
http://searchHost:9200/$_index/$_type/$_id;
SNIPS: Snippets of the matching document that contain the search terms --
"highlight":{"fields"{"*":"require_field_match":false,"fragment_size":120}}};
RATING: An integer, showing the "score" that this hit achieved according to the search engine's internal method of rating the search results (the higher the better) -- Each Elasticsearch "hit" has a "score" parameter, which is a decimal. It is multiplied by 1000 and then the integer part returned. Those providing plug-ins for other search engines should scale the equivalent score such that the RATING integer is comparable to the Elasticsearch value for a similar document set and search terms. (This is easier said than done but that is the aim.)
HIT_COUNT: The number of hits -- in Elasticsearch the search response contains a parameter called "total", under the "hits" field.
DATE_STRING: A date associated with the document, as a String in ISO 8601 format --"source": ["dcterms:valid]";
FROM_REPO: The name of the repository from which the hit was returned, followed by the name of the "index" and "type", with forward slash separators, viz: repo/index/type. The name of the repository is a configuration parameter in virtual table "SEARCH_CONFIG" which must be queried by the OPENSEARCH stored procedure using its IPv4 address as the key to select the correct row. (See README.md for further information about the use of this virtual table.) Elasticsearch returns parameters called "_index" and "_type" for each hit.