8 Replies Latest reply on Oct 2, 2014 10:19 AM by Mark Addleman

    Default schema

    Tom Arnold Novice

      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?

        • 1. Re: Default schema
          Ramesh Reddy Master

          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
            Mark Addleman Master

            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
              Ramesh Reddy Master

              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
                Mark Addleman Master

                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
                  Mark Addleman Master

                  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
                    Mark Addleman Master

                    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
                      Steven Hawkins Master

                      > 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
                        Mark Addleman Master

                        > 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.