Grails - Create database view

Question

I am woirking with Grails 2.4.4 (hibernate4 and MySQL) and I am trying to create a database view to see data from different tables.

I followed this page: http://www.slideshare.net/gr8conf/gorm-burt-beckwith2011

The problem is that Gorm is creating a table instead of a view. Furthemore, it creates the table with only the column id.

I have defined the next files:

DataSource.groovy

dataSource {
    pooled = true
    jmxExport = true
    driverClassName = "com.mysql.jdbc.Driver"
    username = "--"
    password = "--"
    configClass = gr8conf.DdlFilterConfiguration <--
}

gr8conf.DdlFilterConfiguration

 package gr8conf;

import java.util.ArrayList; 
import java.util.List; 

import org.codehaus.groovy.grails.orm.hibernate.cfg.GrailsAnnotationConfiguration; 
import org.hibernate.HibernateException; 
import org.hibernate.dialect.Dialect; 
import org.hibernate.dialect.HSQLDialect; 
import org.hibernate.tool.hbm2ddl.DatabaseMetadata; 

public class DdlFilterConfiguration extends GrailsAnnotationConfiguration {
    private static final String [] IGNORED_NAMES = {"v_convocatorias"};

     @Override 
     public String[] generateSchemaCreationScript(Dialect dialect) 
                     throws HibernateException { 
             return prune(super.generateSchemaCreationScript(dialect), dialect); 
     } 

     @Override 
     public String[] generateDropSchemaScript(Dialect dialect) 
                     throws HibernateException { 
             return prune(super.generateDropSchemaScript(dialect), dialect); 
     } 

     @Override 
     public String[] generateSchemaUpdateScript(Dialect dialect, 
                     DatabaseMetadata databaseMetadata) throws HibernateException { 
             return prune( 
                             super.generateSchemaUpdateScript(dialect, databaseMetadata), 
                             dialect); 
     } 

     private String[] prune(String[] script, Dialect dialect) { 
             if (dialect instanceof HSQLDialect) { 
                     // do nothing for test env 
                     return script; 
             } 

             List<String> pruned = new ArrayList<String>(); 
             for (String command : script) { 
                     if (!isIgnored(command)) { 
                             pruned.add(command); 
                     } 
             } 

             return pruned.toArray(new String[pruned.size()]); 
     } 

    private boolean isIgnored(String command) {
        command = command.toLowerCase();
        for(String table: IGNORED_NAMES) {
            if(command.startsWith("create table " + table + " ")||
                command.startsWith("alter table " + table + " ")||
                command.startsWith("drop table " + table)||
                command.startsWith("drop table if exists " + table)) {

                return true;
            }
        }
        return false;
    }
}

hibernate/hibernate.cfg.xml

    <?xml version='1.0' encoding='utf-8'?>
    <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
    <hibernate-configuration>
    <session-factory>
        <mapping resource='misc.mysql.innodb.hbm.xml' />
    </session-factory>
</hibernate-configuration>

hibernate/misc.mysql.innodb.hbm.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mappin DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <database-object>
        <create>
            CREATE OR REPLACE VIEW v_convocatorias AS
                SELECT c.titulo, c.descripcion, d.nombre nombre_departamento
                FROM convocatoria c, departamento d
                WHERE d.id = c.departamento_id
        </create>

        <drop>DROP VIEW IF EXISTS v_convocatorias</drop>

        <dialect-scope
            name='org.hibernate.dialect.MySQLInnoDBDialect' />

    </database-object>
</hibernate-mapping>

And the domain class that should take the info from the view:

Convocatorias

class Convocatorias {

    def titulo
    def descripcion
    def nombreDepartamento

    static mapping = {
        table 'v_convocatorias'
        version false
        titulo column:'titulo'
        descripcion column:'descripcion'
        nombreDepartamento column:'nombre_departamento'
    }
}

Answer 1

Finally, I couldn´t make it directly with Hibernate mapping. So I found another way, with the migration plugin: http://grails.org/plugin/database-migration

It allows you to change your database after being created.

More info: http://www.javacodegeeks.com/2014/01/using-database-views-in-grails.html