Default schema

[tom9729]

Does Teiid have the concept of a default schema? I'd like be able to mark a model as default and have any unqualified table references be resolved against that. For example:

-------------------------

<vdb>

  <model name="foo" default="true"> ... tables one, two, three ... </model>

  <model name="bar"> ... tables four, five, six ... </model>

</vdb>

-- This would work.

select * from one;

select * from foo.one;

select * from bar.four;

-- This would not work.

select * from four;

-------------------------

Thoughts?

[rareddy]

There is no "default" model concept. However from your "select" statements all of them work. The namespace resolution works backwards like

select * from vdb.foo.one;
select * from foo.one;
select * from one;

Only when the "one" is defined in more than single model, then

select * from one

will fail with ambiguous group definition, unless qualified as above.

Ramesh..

[markaddleman]

Extending the name resolution rule, I believe that column references may only be one or two levels:

• select a from vdb.foo.one
• select one.a from vdb.foo.one

Of course, source aliasing is allowed:

• select t.a from vdb.foo.one as t

I'd like to propose that fully qualified names be allowed in column references.  For example:

• select vdb.foo.one.a from ...
• select foo.one.a from ....

Obviously, under simple cases, the from clause may be redundant with the column reference but I don't really care about that.  Allowing fully qualified (and thus unambiguous) column references would make it easier to programmatically generate queries.

[rareddy]

Yes, it should already be working the way you are saying Mark. Is there an indication it does not? VDB designation is verbose, as all queries execute in the context of the VDB.

[markaddleman]

I think I tried it in the past and it failed but I may be remembering incorrectly.  I'll double check when I get back to my development system.

[markaddleman]

I think the  complicating factor is that the H2 translator (and probably other JDBC translators) pick up the schema name from the H2 data source and project that into the VDB.  So, if I have an H2 table T in H2's PUBLIC schema that is projected into the vdb in a model named M, the following query works:

• SELECT COL FROM T
• SELECT T.COL FROM T.COL
• SELECT PUBLIC.T.COL FROM PUBLIC.T

but the following does not:

• SELECT M.T.COL FROM M.T
• SELECT M.PUBLIC.T.COL FROM M.PUBLIC.T

I've tried playing around with the relevant model properties (useFullSchemaName, useCatalogName, useQualifiedName) but I can't seem to affect the name qualification behavior.  Attached is my test case

[markaddleman]

Shoot, it looks like I can't attach unless I open a new thread.  Test case is here: gist:1f75493bbc2bcc968ea2

[shawkins]

> SELECT M.T.COL FROM M.T

That is expected.  The Teiid model/schema qualification needs to use the full table name.

> SELECT M.PUBLIC.T.COL FROM M.PUBLIC.T

More than likely there is one more name part, the catalog, to the table name.  This may work if useCatalogName is turned off.

> I've tried playing around with the relevant model properties (useFullSchemaName, useCatalogName, useQualifiedName) but I can't seem to affect the name qualification behavior.

The naming rules are:

catalog name = vdb name

schema name = model name

table name = imported table name - controlled by import options

To restate from above, the use of a vdb name and/or model name assumes that rest of the name will be fully qualified.

[markaddleman]

> More than likely there is one more name part, the catalog, to the table name.  This may work if useCatalogName is turned off.

My test case was failing because I didn't qualify the importer properties.  Correct gist https://gist.github.com/anonymous/313fbb8b64ab15c654bc

I can confirm that Teiid accepts fully qualified column references.  As I indicated above, this is a big deal for us because it greatly simplifies the logic to generated queries.