-
1. Re: Default schema
rareddy Oct 1, 2014 2:28 PM (in response to tom9729)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.. -
2. Re: Default schema
markaddleman Oct 1, 2014 2:57 PM (in response to rareddy)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.
-
3. Re: Default schema
rareddy Oct 1, 2014 3:31 PM (in response to markaddleman)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.
-
4. Re: Default schema
markaddleman Oct 1, 2014 3:41 PM (in response to rareddy)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.
-
5. Re: Default schema
markaddleman Oct 2, 2014 9:11 AM (in response to rareddy)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
-
6. Re: Re: Default schema
markaddleman Oct 2, 2014 9:12 AM (in response to markaddleman)Shoot, it looks like I can't attach unless I open a new thread. Test case is here: gist:1f75493bbc2bcc968ea2
-
7. Re: Re: Default schema
shawkins Oct 2, 2014 9:55 AM (in response to markaddleman)> 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.
-
8. Re: Re: Default schema
markaddleman Oct 2, 2014 10:19 AM (in response to shawkins)> 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.