Grails

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

Details

  • Type: Bug Bug
  • Status: Open Open
  • Priority: Major Major
  • Resolution: Unresolved
  • Affects Version/s: 1.3 RC2
  • Fix Version/s: 2.3-M2
  • 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

Ascending order - Click to sort in descending order
Hide
Permalink
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
Permalink
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
Permalink
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:
    Burt Beckwith
    Reporter:
    Michael Smolyak
Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Last Reviewed: