5 Replies Latest reply on Jun 11, 2014 4:00 PM by virtualdatabase

What's the intersect table to tie columns back to their corresponding table via modeshape.vdb query?

virtualdatabase Dec 18, 2013 4:31 PM

I'm trying to piece together the a relational model from TeiiD designer (i.e. Tables and their columns; columns and their properties etc) via the modeshape.vdb

I'm not able to discern the higher level component Tables to columns and Tables/views to schemas (models), for that matter.

1. Re: What's the intersect table to tie columns back to their corresponding table via modeshape.vdb query?

rhauch Dec 18, 2013 4:37 PM (in response to virtualdatabase)

There is no intersect table like in a relational database. Instead, you still need to use JCR's "functions" like ISSAMENODE, ISDESCENDANT, ISCHILDNODE, etc.
Actions
2. Re: What's the intersect table to tie columns back to their corresponding table via modeshape.vdb query?

virtualdatabase Dec 19, 2013 10:26 AM (in response to rhauch)

Using squirrel to connect to the modeshape vdb I do not see such 'functions' exposed through the vdb (as relational functions), is that correct?
If so then such functionality cannot be performed through the vdb?
I've searched for documentation specific to the VDB but haven't found it. If such exists please direct me so I wont ask silly questions.

thanks!!
Actions
3. Re: What's the intersect table to tie columns back to their corresponding table via modeshape.vdb query?

van.halbert Dec 19, 2013 11:29 AM (in response to virtualdatabase)

You are correct, the documentation on this isn't the best. However, in the Teiid reference guide, under translators, you will see how the modeshape translator operates:
modeshape
For use with Modeshape 2.2.1 or later. The PATH, NAME, LOCALNODENAME, DEPTH, and SCORE functions should be accessed as pseudo-columns, e.g. "nt:base"."jcr:path". Teiid UFDs (prefixed by JCR_) are available for CONTIANS, ISCHILDNODE, ISDESCENDENT, ISSAMENODE, REFERENCE - see the JCRFunctions.xmi. If a selector name is needed in a JCR function, you should use the pseudo-column "jcr:path", e.g. JCR_ISCHILDNODE(foo.jcr_path, 'x/y') would become ISCHILDNODE(foo, 'x/y') in the ModeShape query. An additional pseudo-column "mode:properties" should be imported by setting the ModeShape JDBC connection property teiidsupport=true. The column "mode:properties" should be used by the JCR_REFERENCE and other functions that expect a .* selector name, e.g. JCR_REFERENCE(nt_base.jcr_properties) would become REFERENCE("nt:base".*) in the ModeShape query.
Additionally, here is an example query:
SELECT jcr_name FROM nt_base AS children WHERE JCR_ISCHILDNODE(children.jcr_path, '/pathtoparent')
1 of 1 people found this helpful
Actions
4. Re: What's the intersect table to tie columns back to their corresponding table via modeshape.vdb query?

virtualdatabase Dec 19, 2013 1:17 PM (in response to van.halbert)

Thanks Van!

So a couple questions...
1) These JCR_% functions don't appear to be exposed in via the databasemetadata() methods of the JDBC driver, is that expected?
2) when attempting a query such as your example with my relevant pathtoparentI get the following error:

Error: Function JCR_ISCHILDNODE is marked in the function metadata as a function that must be evaluated at the source.
SQLState: 38000
ErrorCode: 0
Actions
5. Re: What's the intersect table to tie columns back to their corresponding table via modeshape.vdb query?

virtualdatabase Jun 11, 2014 4:00 PM (in response to virtualdatabase)

Just re-tried this and get the following:

Error: TEIID30068 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30068 The function 'JCR_ISCHILDNODE(children.jcr_path, '/derived/teiid/models/agTraits/views/ViewModel.xmi/assaySample/analysis')' is an unknown form. Check that the function name and number of arguments is correct.
SQLState: 50000
ErrorCode: 30068

Am I using the JCR_ISCHILDNODE function wrong?
Actions

Go to original post