Ideas stemming from TEIID-196: support parameters and loopback connections

[markaddleman]

I was thinking more about mapping some of our use cases to TEIID-196.  One of the things I'd like to do is use the temp table to buffer events that may come in from a JMS queue.  Conceptually, there are three parameters to the CREATE TEMP TABLE command:

1. The schema
2. The size of the buffer
3. The JNDI name for the queue

I can see using using (abusing?) the AS 'native sql' syntax to pass those parameters down.  Perhaps something like:

CREATE FOREIGN TEMPORARY TABLE jms_buffer (...) ON jms_buffer_ds

AS SELECT 'size' AS param_name, '1000' AS param_value UNION SELECT 'jndi', 'blah blah'

I like the idea of passing the parameters down as query.  What are your thoughts on this approach?

Of course, in order to get the result set, the translator will have to connect back into Teiid and issue the query.  I find that we use this pattern a lot:  A translator connecting back into Teiid (the same or different VDB) to get some data.  In most cases, we want to connect back into Teiid with the same secrity context as the client query.

What do you think of providing an API to obtain a loopback connection to the VDB?  Something like

public Connection CommandContext.getClientConnection()

Less often, the translator would need to connect to a second VDB.  In this scenario, the loopback connection should still be under the same security context.  I believe that if the second VDB is in the same security domain, there is no need to reauthenticate the connection.  What about also providing an API like

public Connection CommandContext.connect(String vdbName, int version) throws SecurityException (thrown if vdb is not in the same security domain)

[shawkins]

> I like the idea of passing the parameters down as query.  What are your thoughts on this approach?

The intent of the AS clasue with a foreign temp table would be to pass native sql.  It would be up to the translator to interpret what the sql means.  With customization you can use whatever syntax makes the most sense.

>  What do you think of providing an API to obtain a loopback connection to the VDB?  Something like

public Connection CommandContext.getClientConnection()

I think that would have some usefulness, but with the caveat that it wouldn't exactly be the same as the client connection (connection properties and other client state may not be propogated).  Would you be doing this primarily to by-pass using a connection pool?  When using local connections (either through a pool or by just creating a new local connection) that should preserve the security context.  Usage of such connections outside of normal translator operations must be done carefully as to not tie up a processing pool thread on blocking jdbc operations.

> Less often, the translator would need to connect to a second VDB.  In this scenario, the loopback connection should still be under the same security context.

That should still be the case as security-domains are configured at the transport level and not yet at the vdb level.  Here again just creating a new local connection is a possible workaround.  The consideration for promoting this into the api proper would be to give the resulting connection the same sessionid as the parent and possibly for better connection management.

[markaddleman]

I think that would have some usefulness, but with the caveat that it wouldn't exactly be the same as the client connection (connection properties and other client state may not be propogated).  Would you be doing this primarily to by-pass using a connection pool?  When using local connections (either through a pool or by just creating a new local connection) that should preserve the security context.

It's fine that it's not exactly the same connection.  I really care about the maintaining the security context in a local connection.  Since it's a local connection, we don't use connection pooling. 

How would we obtain a local connection under the same security context?  Something like: TeiidDriver.getInstance().connect("jdbc:teiid:vdb@mm://localhost")

[rareddy]

By using "PassthroughAuthentication" on the url?

[shawkins]

This has to be used with a local/non-socket connection:

TeiidDriver.getInstance().connect("jdbc:teiid:vdb;PassthroughAuthentication=true");

Steve

[markaddleman]

My quick testing of this fails in embedded teiid due because of a check for a JBoss class: org.jboss.modules.Module.  Looking at the EmbeddedProfile, it seems to rely on the JBoss module classloader for some thread context loader stuff.  Looks like this problem exists in both 8.1 and 8.2. I tried a quick hack of supplying my own ConnectionProfile that sets the context classloader myself but ran into a problem in LocalServerConnection.getClientServiceRegistry.  It cannot find the TEIID_RUNTIME_CONTEXT in JNDI.  I couldn't quickly find how to ensure that gets set.  Any suggestions?

[shawkins]

Mark,

Where are you trying to make the connection back into Teiid from - a UDF or translator?  The issue is likely with the classloader for the given component.  The Teiid client module is not available by default.  For example with a UDF the classloader is specified by the vdb lib.  From there if the UDF accesses the TeiidDriver, then you would need the UDF module to reference org.jboss.teiid.client as a dependency - simply adding org.jboss.teiid.client to the VDB lib won't work as the classloader for the UDF class will be actually be its module loader and not the full vdb lib classloader.  Similarly if you have a custom translator its module will need to add org.jboss.teiid.client as a dependency.

Steve

[markaddleman]

I'm making a connection from a translator.  My test case is using embedded teiid and everything is on the system loader. 

I think my problem is that the teiid-client's pom declares that jboss-modules as provided dependency but, for some reason, Eclispe m2e didn't put it on my launcher's classpath.

I'm a little surprised that the EmbeddedProfile has a dependency on jboss-modules.  From what I can tell, it's used to determine the caller's security context?

[shawkins]

Ah, there's the disconnect.  Embedded Teiid would not yet be a supported path for making a local connection beyond the TeiidDriver supplied by the EmbeddedServer.  Just as you are seeing that path does assume the use of JBoss modules and then for the engine to be registered in JNDI.  An embedded specific workaround would be to set the EmbeddedServer/TeiidDriver instance on your Translator when you register it with the server.

Steve

[rareddy]

Actually if the translator is set with dependency on "org.jboss.teiid" it should be work. The LocalConnection using "jdbc:teiid:<vdb-name>" url requires the LocalServerConnection, which is in the teiid-runtime project. EmbeddedTeiid would yet another, but would be available in Teiid Embedded only.

[shawkins]

Ramesh, what do you mean?  Translator module dependencies (or any module concept) are typically meaningless in Teiid Embedded as it's up to the caller, not an AS instance, to associate the translator with EmbeddedServer.

[rareddy]

Yes, I agree. Your comment was little confusing as you are talking about the Embedded deployment, where as Mark I believe was using the Server based AS. I wanted to say that use the LocalConnection with dependency set on the translator to "org.jboss.teiid", to avoid the issue

Ramesh..

[shawkins]

Just to make sure there is no confusion.  My initial comments were based on the assumption of server deployment and instructions to include the client dependency for udfs/translators in comment 6.  However in comment 7 Mark indicates that he is using embedded Teiid.  My reply in comment 8 is that a normal local connection will not work for Teiid Embedded.  From there it seems your comments are based upon the same thought as comment 6.

Also just to make sure in the server deployment case, the recommended dependecy would be org.jboss.teiid.client rather than org.jboss.teiid correct?

Steve

[rareddy]

Sorry, I missed that.  Re-reading his comment 6, looks like his test case is using the Embedded. I saw as why one use embedded in the server environment, and embedded was working and not the server one. So, I will not comment on how to make the Embedded work, as it should not need any module stuff etc.

Yes, he would need dependency on "org.jboss.teiid" as the required LocalServerConnection class is in "teiid-runtime" project for making the LocalConnection.

Ramesh..

[shawkins]

> Yes, he would need dependency on "org.jboss.teiid" as the required LocalServerConnection class is in "teiid-runtime" project for making the LocalConnection.

To clarify further, as I see it the EmbeddedProfile (which should probably be renamed LocalProfile) automatically loads the org.jboss.teiid module, thus the issue in server deployment making local connections is for the calling code to have the org.jboss.teiid.client (or possibly org.jboss.teiid, but that is a wider dependency) to properly load the TeiidDriver and associated classes as org.jboss.teiid.client which is not automatically in the Translator/VDB lib classloader.