Reverse-engineers a database to Grails domain classes.
Database Reverse Engineering Plugin - Reference
Documentation
Authors: Burt Beckwith Version: 0.5.1
Table of Contents
1 Introduction to the DB Reverse Engineering Plugin 1.1 Getting Started
2 Configuration 3 General Usage 4 Tutorial
1 Introduction to the DB Reverse Engineering Plugin
The DB Reverse Engineering plugin reads database table information using JDBC and uses the schema data to create domain classes. This is a complex problem and the plugin is unlikely to get things 100% correct. But it should save you a lot of work and hopefully not require too much tweaking after the domain classes are generated.
The plugin uses the Hibernate Tools library, with custom code to generate GORM domain classes instead of Java POJOs.
As of version 0.5, the plugin only works with Grails 2.0 and above. Use version 0.3 (not 0.4) for Grails 1.3.x.
1.1 Getting Started
The first step is installing the plugin. Add a plugin dependency in BuildConfig.groovy:
plugins {
runtime ':db-reverse-engineer:0.5.1' }
There are no required initialization steps but there are some configuration options described in the next .
2 Configuration
There are several configuration options for the plugin.
Core properties
These configuration options are the one you're most likely need to set. They include the package name of the generated files (grails.plugin.reveng.packageName), and information about which s i d e o f a m a n y - t o - m a n y i s t h e ' b e l o n g s T o ' s i d e (
). grails.plugin.reveng.manyToManyBelongsTos
Property Default Meaning
grails.plugin.reveng.packageName application name
package name for the generated domain classes
grails.plugin.reveng.manyToManyBelongsTos none
a Map of join table name -> belongsTo table name to specify which of the two domain classes is the 'belongsTo' side of the relationship
Inclusion/exclusion properties
These configuration options let you define which tables and columns to include in processing. By default all tables and columns are processed.
If you specify tables to include (g.p.r.includeTables g.p.r.includeTableRegexes, , and/or g.p.r.includeTableAntPatterns) then only those will be used. If you specify any of these options then all of the table exclusion options are ignored; this is useful when you have already run the reverse engineer script but want to re-run it for only a subset of tables.
If you specify tables to exclude (g.p.r.excludeTables g.p.r.excludeTableRegexes, , and/or g.p.r.excludeTableAntPatterns) then any matching tables will be ignored.
You can also specify columns to exclude (g.p.r.excludeColumns,
, and/or ) and any
g.p.r.excludeColumnRegexes g.p.r.excludeColumnAntPatterns
matching columns will be ignored. These options can be used with table include rules or table exclude rules - any tables that are included or not excluded will have their columns included or excludede based on these rules.
One addition property, grails.plugin.reveng.manyToManyTables, doesn't affect whether a table is processed, but rather determines whether a table that won't look like a many-to-many table to the Hibernate Tools library (because it has more than two columns) is considered a many-to-many table.
Property Default Meaning
grails.plugin.reveng.includeTables none a List of table names to include for processing
grails.plugin.reveng.includeTableRegexes none a List of table name regex patterns to include for processing
grails.plugin.reveng.includeTableAntPatterns none a List of table name Ant-style patterns to include for processing
grails.plugin.reveng.excludeTables none a List of table names to exclude from processing
grails.plugin.reveng.excludeTableRegexes none a List of table name regex patterns to exclude from processing
grails.plugin.reveng.excludeTableAntPatterns none a List of table name Ant-style patterns to exclude from processing
grails.plugin.reveng.excludeColumns none a Map of table name -> List of column names to ignore
grails.plugin.reveng.excludeColumnRegexes none a Map of table name -> List of column name regex patterns to ignore
grails.plugin.reveng.excludeColumnAntPatterns none a Map of table name -> List of column name Ant-style patterns to ignore
grails.plugin.reveng.manyToManyTables none
a List of table names that should be considered many-to-many join tables; needed for join tables that have more columns than the two foreign keys
Other properties
These remaining configuration options allow you to specify the folder where the domain classes are generated (g.p.r.destDir), whether to overwrite existing classes ( ) and non-standard 'version' column names ( g.p.r.overwriteExisting
). g.p.r.versionColumns
There are also properties to set the default schema name (g.p.r.defaultSchema) and catalog ( ) name which are useful when working with Oracle.
Property Default Meaning
grails.plugin.reveng.destDir 'grails-app/domain' destination folder for the generated classes, relative to the project root
grails.plugin.reveng.versionColumns none
a Map of table name -> version column name, for tables with an optimistic locking column that's not named 'version'
grails.plugin.reveng.overwriteExisting true whether to overwrite existing domain classes
grails.plugin.reveng.defaultSchema none the default database schema name grails.plugin.reveng.defaultCatalog none the default database catalog name
3 General Usage
It's most convenient when creating an application to also create the database at the same time. When creating a "greenfield" application like this you can let Hibernate create your tables for you (e.g. using a dbCreate value of create-drop or update). But often the database already exists, and creating GORM domain classes from them can be a tedious process.
This plugin will save you a lot of time by using the JDBC metadata API to inspect your database schema and create domain classes for you, using some assumptions and augmented by configuration options that you set.
The core of the plugin is the db-reverse-engineer script. There are too many configuration options to support specifying them when running the script, so it takes no arguments and uses configuration options set in grails-app/conf/Config.groovy. These are described in section 2.
Environments
You can choose which database to read from by specifying the environment when running the script (like any Grails script). For example to use the development environment settings, just run
grails db-reverse-engineer
To use the production environment settings, run
grails prod db-reverse-engineer
And if you want to use a custom 'staging' environment configured in DataSource.groovy, run
grails -Dgrails.env=staging db-reverse-engineer
Re-running the reverse engineering script
If you have new or changed tables you can re-run the db-reverse-engineer script to pick up changes and additions. This is not an incremental process though, so existing classes will be overwritten and you will lose any changes you made since the last run. But it's simple to define which tables to include or exclude.
As described in section 2, you can use a combination of the , g r a i l s . p l u g i n . r e v e n g . i n c l u d e T a b l e s , g r a i l s . p l u g i n . r e v e n g . i n c l u d e T a b l e R e g e x e s , grails.plugin.reveng.includeTableAntPatterns , g r a i l s . p l u g i n . r e v e n g . e x c l u d e T a b l e s , and grails.plugin.reveng.excludeTableRegexes
properties to define which tables to grails.plugin.reveng.excludeTableAntPatterns
By default all tables are included, and the plugin assumes you're more likely to exclude than include. So you can specify one or more table names to explicitly exclude using
, one or more regex patterns for exclusion using grails.plugin.reveng.excludeTables
, and one or more Ant-style patterns for grails.plugin.reveng.excludeTableRegexes
exclusion using grails.plugin.reveng.excludeTableAntPatterns. For example, using this configuration
grails.plugin.reveng.excludeTables = ['clickstream', 'error_log'] grails.plugin.reveng.excludeTableRegexes = ['temp.+']
grails.plugin.reveng.excludeTableAntPatterns = ['audit_*']
you would process all tables except clickstream and error_log, and any tables that start with 'temp' (e.g. tempPerson tempOrganization, , etc.) and any tables that start with 'audit_' (e.g. 'audit_orders', 'audit_order_items', etc.)
If you only want to include one or a few tables, it's more convenient to specify inclusion rules rather than exclusion rules, so you use grails.plugin.reveng.includeTables,
, and
grails.plugin.reveng.includeTableRegexes
for that. If any of these properties are grails.plugin.reveng.includeTableAntPatterns
set, the table exclusion rules are ignored. For example, using this configuration
grails.plugin.reveng.includeTables = ['person', 'organization']
you would process (or re-process) just the person and organization tables. You can also use The and
grails.plugin.reveng.includeTableRegexes
properties to include tables based on grails.plugin.reveng.includeTableAntPatterns
patterns.
You can further customize the process by specifying which columns to exclude per-table. For example, this configuration
grails.plugin.reveng.excludeColumns = ['some_table': ['col1', 'col2'], 'another_table': ['another_column']]
will exclude columns col1 and col2 from table some_table, and column another_column from table another_table.
You can also use the grails.plugin.reveng.excludeColumnRegexes and properties to define patterns for grails.plugin.reveng.excludeColumnAntPatterns
By default the db-reverse-engineer script will overwrite existing classes. You can set the property to to override this behavior grails.plugin.reveng.overwriteExisting false
and not overwrite existing files.
Many-to-many tables
Typically many-to-many relationships are implemented using a join table which contains just two columns which are foreign keys referring to the two related tables. It's possible for join tables to have extra columns though, and this will cause problems when trying to infer relationships. By default Hibernate will only consider tables that have two foreign key columns to be join tables. To get the script to correctly use join tables with extra columns, you can specify the table names with the
property. This is demonstrated in .
grails.plugin.reveng.manyToManyTables the tutorial
Another problem with many-to-many relationships is that one of the two GORM classes needs to be the 'owning' side and the other needs to be the 'owned' side, but this cannot be reliably inferred from the database. Both classes need a hasMany declaration, but the 'owned' domain class also needs a
declaration. So all of your many-to-many related tables need to have the tables that will belongsTo
c r e a t e t h e b e l o n g s T o c l a s s e s s p e c i f i e d i n t h e property. This is demonstrated in
grails.plugin.reveng.manyToManyBelongsTos the
. tutorial
Optimistic locking columns
Hibernate assumes that columns used for optimistic lock detection are called version. If you have customized one or more column names, you can direct the script about what the custom names are with the grails.plugin.reveng.versionColumns property. This is demonstrated in the tutorial.
Logging
All of the plugin classes are in the grails.plugin.reveng package, so you can configure that package for logging to see generated messages.
4 Tutorial
In this tutorial we'll create a MySQL database and generate domain classes from it.
1. Create your Grails application.
$ grails create-app reveng-test $ cd reveng-test
2. Install the plugin.
$ grails install-plugin db-reverse-engineer
3. Configure the development environment for MySQL.
Set these property values in the development section of grails-app/conf/DataSource.groovy dataSource { url = 'jdbc:mysql://localhost/reveng' driverClassName = 'com.mysql.jdbc.Driver' username = 'reveng' password = 'reveng' dialect = org.hibernate.dialect.MySQL5InnoDBDialect } A l s o a d d a d e p e n d e n c y f o r t h e M y S Q L J D B C d r i v e r i n : grails-app/conf/BuildConfig.groovy dependencies { runtime 'mysql:mysql-connector-java:5.1.33' }
and be sure to uncomment the mavenCentral() repo:
repositories { grailsPlugins() grailsHome()
As the root user or another user that has rights to create a database and configure grants, run
create database reveng;
grant all on reveng.* to reveng@localhost identified by 'reveng';
5. Create the database tables.
Run these create and alter statements in the reveng database:
use reveng;
create table author (
id bigint not null auto_increment, version bigint not null,
name varchar(255) not null, primary key (id)
) ENGINE=InnoDB;
create table author_books ( author_id bigint not null, book_id bigint not null,
primary key (author_id, book_id) ) ENGINE=InnoDB;
create table book (
id bigint not null auto_increment, version bigint not null,
title varchar(255) not null, primary key (id)
) ENGINE=InnoDB;
create table compos (
first_name varchar(255) not null, last_name varchar(255) not null, version bigint not null,
other varchar(255) not null,
primary key (first_name, last_name) ) ENGINE=InnoDB;
create table compound_unique (
id bigint not null auto_increment, version bigint not null,
prop1 varchar(255) not null, prop2 varchar(255) not null, prop3 varchar(255) not null, prop4 varchar(255) not null, prop5 varchar(255) not null, primary key (id),
unique (prop4, prop3, prop2) ) ENGINE=InnoDB;
create table library (
id bigint not null auto_increment, version bigint not null,
name varchar(255) not null, primary key (id)
) ENGINE=InnoDB; create table other (
username varchar(255) not null,
nonstandard_version_name bigint not null, primary key (username)
create table role (
id bigint not null auto_increment, version bigint not null,
authority varchar(255) not null unique, primary key (id)
) ENGINE=InnoDB;
create table thing (
thing_id bigint not null auto_increment, version bigint not null,
email varchar(255) not null unique, float_value float not null,
name varchar(123), primary key (thing_id) ) ENGINE=InnoDB;
create table user (
id bigint not null auto_increment, version bigint not null,
account_expired bit not null, account_locked bit not null, enabled bit not null,
password varchar(255) not null, password_expired bit not null,
username varchar(255) not null unique, primary key (id)
) ENGINE=InnoDB;
create table user_role ( role_id bigint not null, user_id bigint not null,
date_updated datetime not null, primary key (role_id, user_id) ) ENGINE=InnoDB;
create table visit (
id bigint not null auto_increment, library_id bigint not null,
person varchar(255) not null, visit_date datetime not null, primary key (id)
) ENGINE=InnoDB;
alter table author_books add index FK24C812F63FA913A (book_id),
add constraint FK24C812F63FA913A foreign key (book_id) references book (id);
alter table author_books add index FK24C812F6CD85EDFA (author_id),
add constraint FK24C812F6CD85EDFA foreign key (author_id) references author (id);
alter table user_role add index FK143BF46A52388A1A (role_id),
add constraint FK143BF46A52388A1A foreign key (role_id) references role (id);
alter table user_role add index FK143BF46AF7634DFA (user_id),
add constraint FK143BF46AF7634DFA foreign key (user_id) references user (id); alter table visit add index FK6B04D4B4AEC8BBA (library_id),
add constraint FK6B04D4B4AEC8BBA foreign key (library_id) references library (id);
7. Run the db-reverse-engineer script.
grails db-reverse-engineer
8. Look at the generated domain classes.
Author and Book domain classes.
The author and book tables have a many-to-many relationship, which uses the author_books join table:
create table author (
id bigint not null auto_increment, version bigint not null,
name varchar(255) not null, primary key (id)
) ENGINE=InnoDB;
create table author_books ( author_id bigint not null, book_id bigint not null,
primary key (author_id, book_id) ) ENGINE=InnoDB;
create table book (
id bigint not null auto_increment, version bigint not null,
title varchar(255) not null, primary key (id)
) ENGINE=InnoDB;
alter table author_books add index FK24C812F63FA913A (book_id),
add constraint FK24C812F63FA913A foreign key (book_id) references book (id);
alter table author_books add index FK24C812F6CD85EDFA (author_id),
add constraint FK24C812F6CD85EDFA foreign key (author_id) references author (id);
After running the script you'll have classes similar to these (I've removed empty mapping and blocks):
constraints
class Author { String name
static hasMany = [books: Book] }
and
class Book { String title
static hasMany = [authors: Author] static belongsTo = Author
has the line because we specified this in
Book static belongsTo = Author
with the property.
Config.groovy grails.plugin.reveng.manyToManyBelongsTos
Compos domain class.
The compos table has a composite primary key (made up of the first_name and last_name columns):
create table compos (
first_name varchar(255) not null, last_name varchar(255) not null, version bigint not null,
other varchar(255) not null,
primary key (first_name, last_name) ) ENGINE=InnoDB;
and it generates this domain class:
import org.apache.commons.lang.builder.EqualsBuilder org.apache.commons.lang.builder.HashCodeBuilder
import
class Compos implements Serializable { firstName String String lastName String other hashCode() { int
def builder = new HashCodeBuilder() builder.append firstName builder.append lastName builder.toHashCode() } equals(other) { boolean
if (other == null) return false
def builder = new EqualsBuilder()
builder.append firstName, other.firstName builder.append lastName, other.lastName builder.isEquals()
}
mapping = {
static
id composite: ["firstName", "lastName"] }
}
Since it has a composite primary key, the class is its own primary key so it has to implement
and implement and .
Serializable hashCode equals
create table compound_unique (
id bigint not null auto_increment, version bigint not null,
prop1 varchar(255) not null, prop2 varchar(255) not null, prop3 varchar(255) not null, prop4 varchar(255) not null, prop5 varchar(255) not null, primary key (id),
unique (prop4, prop3, prop2) ) ENGINE=InnoDB;
and it generates this domain class:
class CompoundUnique { prop1 String String prop2 String prop3 String prop4 String prop5 constraints = { static
prop2 unique: ["prop3", "prop4"] }
}
Library and Visit domain classes.
The library and visit tables have a one-to-many relationship:
create table library (
id bigint not null auto_increment, version bigint not null,
name varchar(255) not null, primary key (id)
) ENGINE=InnoDB;
create table visit (
id bigint not null auto_increment, library_id bigint not null,
person varchar(255) not null, visit_date datetime not null, primary key (id)
) ENGINE=InnoDB;
alter table visit add index FK6B04D4B4AEC8BBA (library_id),
add constraint FK6B04D4B4AEC8BBA foreign key (library_id) references library (id);
and they generate these domain classes:
class Library { String name
static hasMany = [visits: Visit] }
class Visit {
person String
Date visitDate
belongsTo = [library: Library]
static mapping = { static version false } }
has no column, so the has optimistic lock checking disabled (
visit version Visit version
). false
Other domain class.
The other table has a string primary key, and an optimistic locking column that's not named . Since we configured this with the
version grails.plugin.reveng.versionColumns
property, the column is resolved correctly:
create table other (
username varchar(255) not null,
nonstandard_version_name bigint not null, primary key (username)
) ENGINE=InnoDB; class Other { username String mapping = { static
id name: "username", generator: "assigned"
version "nonstandard_version_name"
} }
User and Role domain classes.
create table role (
id bigint not null auto_increment, version bigint not null,
authority varchar(255) not null unique, primary key (id)
) ENGINE=InnoDB; create table user (
id bigint not null auto_increment, version bigint not null,
account_expired bit not null, account_locked bit not null, enabled bit not null,
password varchar(255) not null, password_expired bit not null,
username varchar(255) not null unique, primary key (id)
) ENGINE=InnoDB;
create table user_role ( role_id bigint not null, user_id bigint not null,
date_updated datetime not null, primary key (role_id, user_id) ) ENGINE=InnoDB;
alter table user_role add index FK143BF46A52388A1A (role_id),
add constraint FK143BF46A52388A1A foreign key (role_id) references role (id); alter table user_role add index FK143BF46AF7634DFA (user_id),
add constraint FK143BF46AF7634DFA foreign key (user_id) references user (id);
The user_role table has an extra column (date_updated) and would be ignored by default, but since we configured it with the grails.plugin.reveng.manyToManyTables property it's resolved correctly:
class Role { authority String
hasMany = [users: User]
static
static belongsTo = User constraints = {
static
authority unique: true
} } class User { accountExpired boolean boolean accountLocked boolean enabled String password boolean passwordExpired String username
hasMany = [roles: Role]
static
constraints = {
static
username unique: true
} }
Thing domain class.
The thing table has a non-standard primary key column (thing_id) and a unique constraint on the column. The column is nullable, and is defined as :
email name varchar(123)
create table thing (
thing_id bigint not null auto_increment, version bigint not null,
email varchar(255) not null unique, float_value float not null,
name varchar(123), primary key (thing_id) ) ENGINE=InnoDB;
and it generates this domain class:
class Thing { email String float floatValue String name mapping = { static id column: "thing_id" } constraints = { static
email unique: true
name nullable: true, maxSize: 123 }
}
9. Update a table and re-run the script.
Add a new column to the thing table:
alter table thing add new_column boolean;
We'll re-run the script but need to configure it to generate the updated domain class in a different directory from the default so we can compare with the original. To configure this, set the value of the
property in :
grails.plugin.reveng.destDir grails-app/conf/Config.groovy
grails db-reverse-engineer
The script will generate this domain class in the temp_reverse_engineer/com/revengtest folder:
class Thing { email String Float floatValue String name Boolean newColumn mapping = { static id column: "thing_id" } constraints = { static
email unique: true
name nullable: true, maxSize: 123 newColumn nullable: true
} }
The domain class has a new Boolean newColumn field and a nullable constraint. Since this generated the correct changes it's safe to move replace the previous domain class with this one.