Database Views and Grails Domain Objects

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Database Views and Grails Domain Objects

bendoerr
Hi Everyone,

There have been a few posts on how to map a grails domain object to a database view. So far none of them have helped me 100%.

What I have so far is a hibernate.cfg.xml which has a "<mapping resource='views.hbm.xml'/>" and in that I define the view table
<hibernate-mapping>
    <database-object>
        <create>
            CREATE VIEW ...
        </create>
        <drop>drop view ...</drop>
    </database-object>
</hibernate-mapping>

So far so good. I run "grails run-app" check my database and I have my view. Everything there works great. Now I need to figure out how to map this to a domain object.

I have tried creating a new domain and mapping the table name to the database view but GORM creates the table from the domain object before the view.hbm.xml runs.

I get the feeling that I need to use the hibernate hbm mapping file to map this to a grails domain but I haven't the slightest idea how do to that.

I am currently lost as to what to try next.

Our project currently lets GORM create our schema based on the domain objects, and we then bootstrap some data in. We would like to continue doing this for the time being but are considering something like the liquibase plugin.

Ben
Reply | Threaded
Open this post in threaded view
|

Re: Database Views and Grails Domain Objects

Juri Kuehn
Hi,

i remember reading about it in the documentation, either grails or hibernate, but cant find it right now. I think you can create a usual domain. If there is a view instead of a table in the db with that name, then hibernate will use it.

Maybe these links can help:
http://stackoverflow.com/questions/425294/sql-database-views-in-grails
http://timezra.blogspot.com/2009/05/mapping-hibernate-entities-to-views.html

Best regards,
Juri

Benjamin Doerr wrote
Hi Everyone,

There have been a few posts on how to map a grails domain object to a database view. So far none of them have helped me 100%.

What I have so far is a hibernate.cfg.xml which has a "<mapping resource='views.hbm.xml'/>" and in that I define the view table
<hibernate-mapping>
    <database-object>
        <create>
            CREATE VIEW ...
        </create>
        <drop>drop view ...</drop>
    </database-object>
</hibernate-mapping>

So far so good. I run "grails run-app" check my database and I have my view. Everything there works great. Now I need to figure out how to map this to a domain object.

I have tried creating a new domain and mapping the table name to the database view but GORM creates the table from the domain object before the view.hbm.xml runs.

I get the feeling that I need to use the hibernate hbm mapping file to map this to a grails domain but I haven't the slightest idea how do to that.

I am currently lost as to what to try next.

Our project currently lets GORM create our schema based on the domain objects, and we then bootstrap some data in. We would like to continue doing this for the time being but are considering something like the liquibase plugin.

Ben
Reply | Threaded
Open this post in threaded view
|

Re: Database Views and Grails Domain Objects

bendoerr
It seems like for this to work, we have to move to a point where we no longer allow grails to generate our schema for us.

The problem with the hibernate mapping hbm.xml files is that they are not loaded until after GORM has done its schema generation. What worked was having gorm generate our schema and then setting dbCreate="". My domain class with static mapping = { table 'view' } worked then. But this is not currently possible in our environment.

Is it that most people manage their own schema or is it that no one uses views?

Anymore advice would be wonderful. I really need this view as a bean to use with our current filtering and sorting plugins.

Ben

Juri Kuehn wrote
Hi,

i remember reading about it in the documentation, either grails or hibernate, but cant find it right now. I think you can create a usual domain. If there is a view instead of a table in the db with that name, then hibernate will use it.

Maybe these links can help:
http://stackoverflow.com/questions/425294/sql-database-views-in-grails
http://timezra.blogspot.com/2009/05/mapping-hibernate-entities-to-views.html

Best regards,
Juri

Benjamin Doerr wrote
Hi Everyone,

There have been a few posts on how to map a grails domain object to a database view. So far none of them have helped me 100%.

What I have so far is a hibernate.cfg.xml which has a "<mapping resource='views.hbm.xml'/>" and in that I define the view table
<hibernate-mapping>
    <database-object>
        <create>
            CREATE VIEW ...
        </create>
        <drop>drop view ...</drop>
    </database-object>
</hibernate-mapping>

So far so good. I run "grails run-app" check my database and I have my view. Everything there works great. Now I need to figure out how to map this to a domain object.

I have tried creating a new domain and mapping the table name to the database view but GORM creates the table from the domain object before the view.hbm.xml runs.

I get the feeling that I need to use the hibernate hbm mapping file to map this to a grails domain but I haven't the slightest idea how do to that.

I am currently lost as to what to try next.

Our project currently lets GORM create our schema based on the domain objects, and we then bootstrap some data in. We would like to continue doing this for the time being but are considering something like the liquibase plugin.

Ben
Reply | Threaded
Open this post in threaded view
|

Re: Database Views and Grails Domain Objects

Juri Kuehn
A hack could be to drop the table before creating the view to make it work in the dev environment with dbCreate = "create-drop":

<hibernate-mapping>
    <database-object>
        <create>
            DROP TABLE ...
        </create>
        <drop></drop>
    </database-object>
    <database-object>
        <create>
            CREATE VIEW ...
        </create>
        <drop>drop view ...</drop>
    </database-object>
</hibernate-mapping> 

Or something like that. This should work for dbCreate = "create-drop". Clearly on changes with dbCreate = "update" the view has to be updated manually. Since queries from views.hbm.xml are not executed with the "update" setting, anything else shouldn't be a problem.

I'm also curious about how to implement this cleanly. Maybe someone else has a clue? I don't have a better guess, sorry.

Greetings,
Juri

Benjamin Doerr wrote
It seems like for this to work, we have to move to a point where we no longer allow grails to generate our schema for us.

The problem with the hibernate mapping hbm.xml files is that they are not loaded until after GORM has done its schema generation. What worked was having gorm generate our schema and then setting dbCreate="". My domain class with static mapping = { table 'view' } worked then. But this is not currently possible in our environment.

Is it that most people manage their own schema or is it that no one uses views?

Anymore advice would be wonderful. I really need this view as a bean to use with our current filtering and sorting plugins.

Ben

Juri Kuehn wrote
Hi,

i remember reading about it in the documentation, either grails or hibernate, but cant find it right now. I think you can create a usual domain. If there is a view instead of a table in the db with that name, then hibernate will use it.

Maybe these links can help:
http://stackoverflow.com/questions/425294/sql-database-views-in-grails
http://timezra.blogspot.com/2009/05/mapping-hibernate-entities-to-views.html

Best regards,
Juri

Benjamin Doerr wrote
Hi Everyone,

There have been a few posts on how to map a grails domain object to a database view. So far none of them have helped me 100%.

What I have so far is a hibernate.cfg.xml which has a "<mapping resource='views.hbm.xml'/>" and in that I define the view table
<hibernate-mapping>
    <database-object>
        <create>
            CREATE VIEW ...
        </create>
        <drop>drop view ...</drop>
    </database-object>
</hibernate-mapping>

So far so good. I run "grails run-app" check my database and I have my view. Everything there works great. Now I need to figure out how to map this to a domain object.

I have tried creating a new domain and mapping the table name to the database view but GORM creates the table from the domain object before the view.hbm.xml runs.

I get the feeling that I need to use the hibernate hbm mapping file to map this to a grails domain but I haven't the slightest idea how do to that.

I am currently lost as to what to try next.

Our project currently lets GORM create our schema based on the domain objects, and we then bootstrap some data in. We would like to continue doing this for the time being but are considering something like the liquibase plugin.

Ben
Reply | Threaded
Open this post in threaded view
|

Re: Database Views and Grails Domain Objects

bendoerr
Juri,

What a great hack. It worked.

If we come up with an elegant solution, we will defiantly let you know.

Ben


Juri Kuehn wrote
A hack could be to drop the table before creating the view to make it work in the dev environment with dbCreate = "create-drop":

<hibernate-mapping>
    <database-object>
        <create>
            DROP TABLE ...
        </create>
        <drop></drop>
    </database-object>
    <database-object>
        <create>
            CREATE VIEW ...
        </create>
        <drop>drop view ...</drop>
    </database-object>
</hibernate-mapping> 

Or something like that. This should work for dbCreate = "create-drop". Clearly on changes with dbCreate = "update" the view has to be updated manually. Since queries from views.hbm.xml are not executed with the "update" setting, anything else shouldn't be a problem.

I'm also curious about how to implement this cleanly. Maybe someone else has a clue? I don't have a better guess, sorry.

Greetings,
Juri
Reply | Threaded
Open this post in threaded view
|

Re: Database Views and Grails Domain Objects

lighter
Hi everyone,

This thread is old but has anyone found a cleaner solution ?

The problem is that my application depends on GORM to handle DDL but it needs a database view which can be mapped to a domain class.

However, as GORM's DDL generation runs prior to Hibernate's DDL, view is never populated.

Apart from the hack mentioned above, which is letting GORM to create a table and then dropping it and creating a view in Hibernate's DDL, is there any other solution ?

Maybe in the domain class:
    static mapping = {
           // flag to bypass GORM's DDL generation for this class without affecting whole application ?
    }

Any comment would be appreciated.
Reply | Threaded
Open this post in threaded view
|

Re: Database Views and Grails Domain Objects

exto
Having the same issue here, I would really like a elegant solution to this problem.

Seems like static { manageDDL = no } or something to that extent would be useful.