Grails
  1. Grails
  2. GRAILS-6195

GORM list() query for inherited domain objects does not use the schema names specified in object mapping

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.3 RC2
    • Fix Version/s: None
    • Component/s: Persistence
    • Labels:
      None

      Description

      I have three domain objects in my model, ThesisDocument extending SimpleDocument, which in turn extends Document.

      The objects in my domain model come from two schemas, "document_schema" and "user_schema". In the data source configuration I provide the URL that refers to one of the schemas (document_schema).

      The code for the objects uses explicit schema reference:

      static mapping = {
          table name:"thesis_document", schema_name:"document_schema"
      }
      

      When I query ThesisDocument (using list()), GORM generates the following SQL:

      select
          this_.id as id28_0_,
          this_2_.version as version28_0_,
      ...
      from
          thesis_document this_
      inner join
          simple_document this_1_
              on this_.id=this_1_.id
      inner join
          document_schema.document this_2_
              on this_.id=this_2_.id limit ?
      

      Note that only "document" table has a fully qualified reference (document_schema.document). The other two tables (thesis_document and simple_document) are referred to using unqualified names.

      Thus, if I point to "user_schema" in DataSource.groovy (there are problems related to many-to-many join tables requiring me to do that), the query fails (looking for user_schema.thesis_document).

      A workaround I found is instead of

      static mapping = {
          table name:"thesis_document", schema_name:"document_schema"
      }
      

      to use

      static mapping = {
          table name:"document_schema.thesis_document"
      }
      

      This works, but it is a hack. The query should use fully qualified table names for all table involved.

        Activity

        Hide
        Burt Beckwith added a comment -

        Could you post more of the domain class mapping?

        Show
        Burt Beckwith added a comment - Could you post more of the domain class mapping?
        Hide
        Michael Smolyak added a comment -

        I see that my original description was a bit cryptic. Let me give a more detailed explanation.

        In my domain models with two schemas, document_schema and user_schema, I have to deal with two issues related to the multi-schema situation. One is generating schema names in queries against subclasses. The example in the issue description illustrates that. Despite the fact that the schema name is explicitly provided, for Document, SimpleDocument (subclass of Document) and ThesisDocument (subclass of SimpleDocument), the query generates the explicit schema qualifier only for the document table, not for simple_document or thesis_document.

        With ThesisDocument mapping of

         
        static mapping = {
            table name:"thesis_document", schema_name:"document_schema"
        }
        

        and SimpleDocument mapping of

         
        static mapping = {
            table name:"simple_document", schema_name:"document_schema"
        }
        

        the query should be

         
        select
            this_.id as id28_0_,
            this_2_.version as version28_0_,
        ...
        from
            document_schema.thesis_document this_
        inner join
            document_schema.simple_document this_1_
                on this_.id=this_1_.id
        inner join
            document_schema.document this_2_
                on this_.id=this_2_.id limit ?
        

        not

         
        select
            this_.id as id28_0_,
            this_2_.version as version28_0_,
        ...
        from
            thesis_document this_
        inner join
            simple_document this_1_
                on this_.id=this_1_.id
        inner join
            document_schema.document this_2_
                on this_.id=this_2_.id limit ?
        

        On top of that there is another issue, this one having to do with mapping tables. I am using Spring Security plugin, which uses objects User and Role. These two objects belong to the user_schema. There is a M-M relationship between Users and Roles, which requires Grails to generate a mapping table, user_roles. Both User and Role domain objects have their schemas explicitly defined, just like Document and its subclasses have. However, there is no way in Grails (that I know of) to define the schema for the mapping table. It seems, Grails does not use the explicit schema defined for the User and Role objects to generate queries against the user_roles table. Instead, it uses an unqualified name for the mapping table.

        I have two choices for defining the default schema - either document_schema or user_schema. If I use the former, my queries for user_roles will fails, since Hibernate will be looking for it in the wrong (default) schema. If I define user_schema as the default, the queries against ThesisDocument will fail, since, again, the query does not use the explicitly specified schema name for ThesisDocument table.

        My inability to specify the schema explicitly for user_roles table looks like an omission. Grails ignoring the explicitly specified schema name for ThesisDocument is definitely a bug.

        Hope this helps,

        Michael

        Show
        Michael Smolyak added a comment - I see that my original description was a bit cryptic. Let me give a more detailed explanation. In my domain models with two schemas, document_schema and user_schema, I have to deal with two issues related to the multi-schema situation. One is generating schema names in queries against subclasses. The example in the issue description illustrates that. Despite the fact that the schema name is explicitly provided, for Document, SimpleDocument (subclass of Document) and ThesisDocument (subclass of SimpleDocument), the query generates the explicit schema qualifier only for the document table, not for simple_document or thesis_document. With ThesisDocument mapping of static mapping = { table name: "thesis_document" , schema_name: "document_schema" } and SimpleDocument mapping of static mapping = { table name: "simple_document" , schema_name: "document_schema" } the query should be select this_.id as id28_0_, this_2_.version as version28_0_, ... from document_schema.thesis_document this_ inner join document_schema.simple_document this_1_ on this_.id=this_1_.id inner join document_schema.document this_2_ on this_.id=this_2_.id limit ? not select this_.id as id28_0_, this_2_.version as version28_0_, ... from thesis_document this_ inner join simple_document this_1_ on this_.id=this_1_.id inner join document_schema.document this_2_ on this_.id=this_2_.id limit ? On top of that there is another issue, this one having to do with mapping tables. I am using Spring Security plugin, which uses objects User and Role. These two objects belong to the user_schema. There is a M-M relationship between Users and Roles, which requires Grails to generate a mapping table, user_roles. Both User and Role domain objects have their schemas explicitly defined, just like Document and its subclasses have. However, there is no way in Grails (that I know of) to define the schema for the mapping table. It seems, Grails does not use the explicit schema defined for the User and Role objects to generate queries against the user_roles table. Instead, it uses an unqualified name for the mapping table. I have two choices for defining the default schema - either document_schema or user_schema. If I use the former, my queries for user_roles will fails, since Hibernate will be looking for it in the wrong (default) schema. If I define user_schema as the default, the queries against ThesisDocument will fail, since, again, the query does not use the explicitly specified schema name for ThesisDocument table. My inability to specify the schema explicitly for user_roles table looks like an omission. Grails ignoring the explicitly specified schema name for ThesisDocument is definitely a bug. Hope this helps, Michael
        Hide
        Jamie Jones added a comment -

        Is there any workaround or notes on this issue? It's been open for 18 months and has just been continually pushed to further releases. I'm experiencing the same symptoms as described in this bug, but legacy database architecture forces the fix to have to be in the GRAILS realm.

        Show
        Jamie Jones added a comment - Is there any workaround or notes on this issue? It's been open for 18 months and has just been continually pushed to further releases. I'm experiencing the same symptoms as described in this bug, but legacy database architecture forces the fix to have to be in the GRAILS realm.

          People

          • Assignee:
            Unassigned
            Reporter:
            Michael Smolyak
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Last Reviewed:

              Development