New data sources for Teiid, part 2: we write a translator

In the previous section, we briefly examined how to describe a data source using DDL. But what if the source uses a specific protocol? If direct data display is not enough? If we want to add data preprocessing or an embedded procedure? There is a way: write a translator.

In general, a translator consists of 4 parts (classes). It is a successor of the ExecutionFactory class and implementations of the ResultSetExecution , ProcedureExecution and UpdateExecution ; In addition, Teiid's standard translators, for convenience, distinguish the metadata processor in a separate class, although it does not implement any interface and is not inherited from anything. Consider everything in order.

Components of the translator

• ExecutionFactory is the starting point of the translator, the place where its name and description are specified, its properties (properties), SQL constructs that it supports are described, metadata are generated (that is, descriptions of tables and procedures that the translator implements are created), and if necessary, instances of the above interfaces are created, directly performing the processing of SELECT, INSERT / UPDATE / DELETE queries and calls to embedded procedures.
• ResultSetExecution is an interface whose implementations are used to process SELECT queries. Implementations may not be available in the translator.
• ProcedureExecution - an interface whose implementations are used to handle calls to embedded procedures. Implementations may not be available in the translator.
• UpdateExecution is an interface whose implementations are used to process INSERT / UPDATE / DELETE requests. Implementations may not be available in the translator.

Further we will move on these points.
')

Basic settings

In order for Teiid to see our translator, we need a META-INF \ services \ org.teiid.translator.ExecutionFactory file in which we add the line in the resulting jar'E to which we add:

 ru.habrahabr.HabrExecutionFactory 

In addition, for JBoss 7 in the file % JBOSS_HOME% / standalone / configuration / standalone.xml (or [..] domain [..] ) you need to add this:

 <subsystem xmlns="urn:jboss:domain:teiid:1.0"> [..] <translator name="habrahabr" module="ru.habrahabr"/> [..] 

Here we indicate to the server in which module our translator is located. It is clear that the jar with our translator needs to be placed in the ru.habrahabr module.

And here, as in the previous article, we need to register the data source for the translator.

 <subsystem xmlns="urn:jboss:domain:resource-adapters:1.0"> [...] <resource-adapters> <resource-adapter> <archive>teiid-connector-ws.rar</archive> <transaction-support>NoTransaction</transaction-support> <connection-definitions> <connection-definition class-name="org.teiid.resource.adapter.ws.WSManagedConnectionFactory" jndi-name="java:/habrDS" enabled="true" use-java-context="true" pool-name="habr-ds"> <config-property name="EndPoint">http://habrahabr.ru/api/profile/</config-property> </connection-definition> </connection-definitions> </resource-adapter> </resource-adapters> [...] 

Model Description

In the simplest case, when no parameters are set for either the model or the translator, the model description in the vdb file will look like this:

  <model name="habr"> <source name="habr" translator-name="habrahabr" connection-jndi-name="java:/habrDS"/> </model> 

Everything.

If you need to set the properties of the translator, you will have to describe the translator-heir:

  <model name="habr"> <source name="habr" translator-name="habrahabr2" connection-jndi-name="java:/habrDS"/> </model> <translator name="habrahabr2" type="habrahabr"> <property name="defaultUser" value="elfuegobiz"/> </translator> 

And if you also need to specify the parameters for the metadata collector, one more line will be added:

  <model name="habr"> <source name="habr" translator-name="habrahabr2" connection-jndi-name="java:/habrDS"/> <property name="importer.convertToUppercase" value="true"/> </model> <translator name="habrahabr2" type="habrahabr"> <property name="defaultUser" value="elfuegobiz"/> </translator> 

ExecutionFactory

Here we can do this:

1. Set the name and description of the translator.
2. Describe the properties of the translator.
3. Describe the SQL constructs that we will support.
4. Create metadata of supported tables and procedures.
5. Create instances of classes to process requests.

So let's go.

The name and description of the translator

 package ru.habrahabr; import javax.resource.cci.ConnectionFactory; import org.teiid.translator.ExecutionFactory; import org.teiid.translator.Translator; import org.teiid.translator.WSConnection; @Translator(name = "habrahabr", description = "A translator for Habrahabr API") public class HabrExecutionFactory extends ExecutionFactory<ConnectionFactory, WSConnection> { } 

The Translator annotation marks the class as a translator class, the name parameter specifies the name under which it will be visible in the system, and description - an arbitrary text description. We set the parameters for inheritance based on the fact that we use the standard WS connector Teiid.

Translator properties

Above, in the “Model Description” chapter , we specified the defaultUser property value for the translator. Now we implement it in the translator code.

 import org.teiid.translator.TranslatorProperty; [..] private String defaultUser; @TranslatorProperty(description="Default user name", display="Default user name to use for table queries", required=true) public String getDefaultUser() { return defaultUser; } public void setDefaultUser(String defaultUser) { this.defaultUser = defaultUser; } 

The getter TranslatorProperty annotation provides a short and detailed description of the property. If this property is required, you can add the parameter required=true to the annotation.

When instantiating a translator, Teiid will automatically fill this field with the value from the translator description in the vdb file.

Support for SQL constructs

The ExecutionFactory class has many methods with a supports* mask that return a boolean value indicating whether the translator supports this feature or not. Features that the translator does not support, Teiid implements by its own means. For example, if the translator does not know how to handle the function aggregate sum() , then Teiid will request data from the translator, and then it will calculate the sum itself.

To change these settings, almost all of them need to overlap, only for a few there are corresponding setters that can be called in the constructor. For example, if we want to implement support for count(*) queries and the limit <> constructions, then we will block the corresponding methods and return true to them:

  @Override public boolean supportsAggregatesCountStar() { return true; } @Override public boolean supportsRowLimit() { return true; } 

It makes sense to implement all these possibilities if the data source itself has an API for their implementation. However, our translator will be quite simple - we might write a more complex translator in the next article if the audience expresses interest in the topic at all.

Metadata tables and procedures

To describe the tables and procedures implemented in the translator, the ExecutionFactory class has a special getMetadata(MetadataFactory metadataFactory, WSConnection conn) method getMetadata(MetadataFactory metadataFactory, WSConnection conn) that needs to be overridden and implemented. We receive a copy of the MetadataFactory class, which has methods for creating tables, procedures, and much more, as well as an object of an already established connection to our web service: if we need to request data about, for example, the number and names of tables from our source. In our case, we know in advance what we want to implement:

  @Override public void getMetadata(MetadataFactory metadataFactory, WSConnection conn) throws TranslatorException { Table table = metadataFactory.addTable("habr"); metadataFactory.addColumn("login", DefaultDataTypes.STRING, table); metadataFactory.addColumn("karma", DefaultDataTypes.FLOAT, table); metadataFactory.addColumn("rating", DefaultDataTypes.FLOAT, table); metadataFactory.addColumn("ratingposition", DefaultDataTypes.LONG, table); Procedure proc = metadataFactory.addProcedure("getHabr"); metadataFactory.addProcedureParameter("username", TypeFacility.RUNTIME_NAMES.STRING, Type.In, proc); metadataFactory.addProcedureParameter("ratingposition", TypeFacility.RUNTIME_NAMES.LONG, Type.ReturnValue, proc); metadataFactory.addProcedureParameter("rating", TypeFacility.RUNTIME_NAMES.FLOAT, Type.ReturnValue, proc); metadataFactory.addProcedureParameter("karma", TypeFacility.RUNTIME_NAMES.FLOAT, Type.ReturnValue, proc); metadataFactory.addProcedureParameter("login", TypeFacility.RUNTIME_NAMES.STRING, Type.ReturnValue, proc); } 

We described a table with four fields and a procedure with one parameter and four return values. Because we cannot pass a parameter to the table; the table will return data for the defaultUser parameter, which we have assigned to the translator during configuration. Everything is simpler with the procedure, here we can both pass a parameter and get several values ​​in response. I note in parentheses that procedures can return not only a fixed number of returned values, but also a whole table, like ResultSetExecution : for this, when describing parameters, you need to use Type.Result type, and also implement next() method in the handler.

Handlers

To handle calls to our procedure and requests to our table, we must provide handlers. For example, in this way.

  public ResultSetExecution createResultSetExecution(QueryExpression command, ExecutionContext executionContext, RuntimeMetadata metadata, WSConnection connection) throws TranslatorException { return new HabrResultSetExecution((Select) command, connection); } public ProcedureExecution createProcedureExecution(Call command, ExecutionContext executionContext, RuntimeMetadata metadata, WSConnection connection) throws TranslatorException { return new HabrProcedureExecution(command, connection); } 

If we have several tables or procedures and want to provide different handlers for different ones, we can do checks in these methods:

  public ResultSetExecution createResultSetExecution(QueryExpression command, ExecutionContext executionContext, RuntimeMetadata metadata, WSConnection connection) throws TranslatorException { String tableName = ((NamedTable) command.getProjectedQuery().getFrom().get(0)). getMetadataObject().getName(); if ("habr".equalsIgnoreCase(tableName)) return new HabrResultSetExecution((Select) command, connection); if ("hrenabr".equalsIgnoreCase(tableName)) return new HrenabrResultSetExecution((Select) command, connection); return null; } public ProcedureExecution createProcedureExecution(Call command, ExecutionContext executionContext, RuntimeMetadata metadata, WSConnection connection) throws TranslatorException { if ("getHabr".equalsIgnoreCase(command.getProcedureName())) return new HabrProcedureExecution(command, connection); if ("getHrenabr".equalsIgnoreCase(command.getProcedureName())) return new HrenabrProcedureExecution(command, connection); return null; } 

But in our translator, we will not do that: there is no need.

Oh yes ... little street magic

The attentive reader probably noticed that so far we have never used the property of the importer.convertToUppercase model, which we have registered in the configuration. The Teiid code uses this nice feature, and we use it:

 [..] private boolean convertToUppercase; public boolean isConvertToUppercase() { return convertToUppercase; } public void setConvertToUppercase(boolean convertToUppercase) { this.convertToUppercase = convertToUppercase; } [..] @Override public void getMetadata(MetadataFactory metadataFactory, WSConnection conn) throws TranslatorException { [..] PropertiesUtils.setBeanProperties(this, metadataFactory.getImportProperties(), "importer"); } 

As you can easily guess by the code, the PropertiesUtils.setBeanProperties method can prefix the properties that are passed from the model configuration to the MetadataFactory and are available via the metadataFactory.getImportProperties() , and fill the specified POJO with them, taking into account type conversion and checking for the presence of properties in general. So we get a convenient way to transfer configuration parameters to the code.

ResultSetExecution

To begin with, we will write a simple method that will parse the input data and form a table row for output, and add it to our HabrExecutionFactory class.
It also takes into account the value of our property convertToUppercase and, if it is set to true , the login is converted to uppercase letters.

  protected List<Object> extractResult(DataSource dataSource) throws TranslatorException { List<Object> results = new ArrayList<Object>(); try { DocumentBuilderFactory xmlFact = DocumentBuilderFactory.newInstance(); DocumentBuilder builder; builder = xmlFact.newDocumentBuilder(); Document doc = builder.parse(dataSource.getInputStream()); dataSource.getInputStream().close(); final XPath xpath = XPathFactory.newInstance().newXPath(); Node node = (Node) xpath.compile("/habrauser/login").evaluate(doc, XPathConstants.NODE); String login = node.getTextContent(); if (convertToUppercase) login = login.toUpperCase(); results.add(login); node = (Node) xpath.compile("/habrauser/karma").evaluate(doc, XPathConstants.NODE); results.add(Float.valueOf(node.getTextContent())); node = (Node) xpath.compile("/habrauser/rating").evaluate(doc, XPathConstants.NODE); results.add(Float.valueOf(node.getTextContent())); node = (Node) xpath.compile("/habrauser/ratingPosition").evaluate(doc, XPathConstants.NODE); results.add(Long.valueOf(node.getTextContent())); } catch (Exception e) { throw new TranslatorException(e); } return results; } 

Now you can write the class HabrResultSetExecution :

  public class HabrResultSetExecution implements ResultSetExecution { private final WSConnection conn; private boolean closed; private DataSource dataSource; public HabrResultSetExecution(Select query, WSConnection conn) { this.conn = conn; } @Override public void execute() throws TranslatorException { closed = false; try { Dispatch<DataSource> dispatch = conn.createDispatch(HTTPBinding.HTTP_BINDING, defaultUser, DataSource.class, Mode.MESSAGE); dispatch.getRequestContext().put(MessageContext.HTTP_REQUEST_METHOD, "GET"); dataSource = dispatch.invoke(null); } catch (Exception e) { throw new TranslatorException(e); } } @Override public List<?> next() throws TranslatorException, DataNotAvailableException { if (closed) return null; closed = true; return extractResult(dataSource); } @Override public void close() { closed = true; } @Override public void cancel() throws TranslatorException { closed = true; } } 

The execute() method is called by the system to execute the request. In our implementation, it performs a request to the service, using the connection passed to it, and stores an instance of the DataSource in the field. After that, the system calls the next() method until it returns null . In the first call, the method passes the saved DataSource to the extractResult() method, which does all the work and also generates a data row: List<?> , Each of whose elements corresponds to a field in the DB row. Because we have only one line of data; on the second and subsequent calls we return null .

ProcedureExecution

This class works in a similar way. There are only two differences:

• The execute() method uses the user name passed in the parameter procedure to call the service.
• instead of the next() method, the system selects data through the getOutputParameterValues() method (which is called only once per call); The next() method, as I mentioned earlier, can be called if the procedure can return a result set

  public class HabrProcedureExecution implements ProcedureExecution { private final Call procedure; private final WSConnection conn; private DataSource dataSource; public HabrProcedureExecution(Call procedure, WSConnection conn) { this.procedure = procedure; this.conn = conn; } @Override public void execute() throws TranslatorException { List<Argument> arguments = this.procedure.getArguments(); String username = (String) arguments.get(0).getArgumentValue().getValue(); try { Dispatch<DataSource> dispatch = conn.createDispatch(HTTPBinding.HTTP_BINDING, username, DataSource.class, Mode.MESSAGE); dispatch.getRequestContext().put(MessageContext.HTTP_REQUEST_METHOD, "GET"); dataSource = dispatch.invoke(null); } catch (Exception e) { throw new TranslatorException(e); } } @Override public List<?> getOutputParameterValues() throws TranslatorException { return extractResult(dataSource); } @Override public List<?> next() throws TranslatorException, DataNotAvailableException { return null; } @Override public void close() { } @Override public void cancel() throws TranslatorException { } } 

UpdateExecution

We will probably implement this handler in the next article if the audience shows interest in the topic at all.

Conclusion

So we got to this point. Now we only have two things to do:

1. execute these queries here:
   

    select * from habr.habr; select w.* from (call habr.getHabr(username=>'elfuegobiz')) w; 

2. say: it was cool =)