<< 4General Usage
(Quick Reference)

5 Tutorial - Reference Documentation

Authors: Burt Beckwith

Version: 3.0.0

5 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 by adding a dependency in BuildConfig.groovy:

plugins {
   …
   compile ':db-reverse-engineer:3.0.0'
   …
}

3. Configure the development environment for MySQL.

Set these property values in the development section of grails-app/conf/DataSource.groovy

dataSource {
   …
   dialect = org.hibernate.dialect.MySQL5InnoDBDialect
   driverClassName = 'com.mysql.jdbc.Driver'
   password = 'reveng'
   url = 'jdbc:mysql://localhost/reveng'
   username = 'reveng'
   …
}

Also add a dependency for the MySQL JDBC driver in grails-app/conf/BuildConfig.groovy:

dependencies {
   runtime 'mysql:mysql-connector-java:5.1.38'
}

4. 4. Create the database.

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(20) NOT NULL AUTO_INCREMENT,
  version bigint(20) NOT NULL,
  name varchar(255) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE book (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  version bigint(20) NOT NULL,
  title varchar(255) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE author_books (
  author_id bigint(20) NOT NULL,
  book_id bigint(20) NOT NULL,
  PRIMARY KEY (author_id,book_id),
  KEY FK24C812F6183CFE1B (book_id),
  KEY FK24C812F6DAE0A69B (author_id),
  CONSTRAINT FK24C812F6183CFE1B FOREIGN KEY (book_id) REFERENCES book (id),
  CONSTRAINT FK24C812F6DAE0A69B FOREIGN KEY (author_id) REFERENCES author (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE compos (
  first_name varchar(255) NOT NULL,
  last_name varchar(255) NOT NULL,
  version bigint(20) NOT NULL,
  other varchar(255) NOT NULL,
  PRIMARY KEY (first_name,last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE compound_unique (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  version bigint(20) 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 KEY prop4 (prop4,prop3,prop2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE library (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  version bigint(20) NOT NULL,
  name varchar(255) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE visit (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  library_id bigint(20) NOT NULL,
  person varchar(255) NOT NULL,
  visit_date datetime NOT NULL,
  PRIMARY KEY (id),
  KEY FK6B04D4BE8E8E739 (library_id),
  CONSTRAINT FK6B04D4BE8E8E739 FOREIGN KEY (library_id) REFERENCES library (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE other (
  username varchar(255) NOT NULL,
  nonstandard_version_name bigint(20) NOT NULL,
  PRIMARY KEY (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE user (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  version bigint(20) NOT NULL,
  account_expired bit(1) NOT NULL,
  account_locked bit(1) NOT NULL,
  enabled bit(1) NOT NULL,
  password varchar(255) NOT NULL,
  password_expired bit(1) NOT NULL,
  username varchar(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE role (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  version bigint(20) NOT NULL,
  authority varchar(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY authority (authority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE user_role (
  role_id bigint(20) NOT NULL,
  user_id bigint(20) NOT NULL,
  date_updated datetime NOT NULL,
  PRIMARY KEY (role_id,user_id),
  KEY FK143BF46A667AF6FB (role_id),
  KEY FK143BF46ABA5BADB (user_id),
  CONSTRAINT FK143BF46A667AF6FB FOREIGN KEY (role_id) REFERENCES role (id),
  CONSTRAINT FK143BF46ABA5BADB FOREIGN KEY (user_id) REFERENCES user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE thing (
  thing_id bigint(20) NOT NULL AUTO_INCREMENT,
  version bigint(20) NOT NULL,
  email varchar(255) NOT NULL,
  float_value float NOT NULL,
  name varchar(123) DEFAULT NULL,
  PRIMARY KEY (thing_id),
  UNIQUE KEY email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

6. Configure the reverse engineering process.

Add these configuration options to grails-app/conf/Config.groovy:

grails.plugin.reveng.packageName = 'com.revengtest'
grails.plugin.reveng.versionColumns = [other: 'nonstandard_version_name']
grails.plugin.reveng.manyToManyTables = ['user_role']
grails.plugin.reveng.manyToManyBelongsTos = ['user_role': 'role', 'author_books': 'book']

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(20) NOT NULL AUTO_INCREMENT,
  version bigint(20) NOT NULL,
  name varchar(255) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE book (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  version bigint(20) NOT NULL,
  title varchar(255) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE author_books (
  author_id bigint(20) NOT NULL,
  book_id bigint(20) NOT NULL,
  PRIMARY KEY (author_id,book_id),
  KEY FK24C812F6183CFE1B (book_id),
  KEY FK24C812F6DAE0A69B (author_id),
  CONSTRAINT FK24C812F6183CFE1B FOREIGN KEY (book_id) REFERENCES book (id),
  CONSTRAINT FK24C812F6DAE0A69B FOREIGN KEY (author_id) REFERENCES author (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

After running the script you'll have classes similar to these:

class Author {
   String name
   static hasMany = [books: Book]
}

and

class Book {
   String title
   static hasMany = [authors: Author]
   static belongsTo = [Author]
}

Book has the line static belongsTo = Author because we specified this in Config.groovy with the grails.plugin.reveng.manyToManyBelongsTos property.

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(20) NOT NULL,
  other varchar(255) NOT NULL,
  PRIMARY KEY (first_name,last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and it generates this domain class:

import org.apache.commons.lang.builder.EqualsBuilder
import org.apache.commons.lang.builder.HashCodeBuilder
class Compos implements Serializable {
   String firstName
   String lastName
   String other
   int hashCode() {
      def builder = new HashCodeBuilder()
      builder.append firstName
      builder.append lastName
      builder.toHashCode()
   }
   boolean equals(other) {
      if (other == null) return false
      def builder = new EqualsBuilder()
      builder.append firstName, other.firstName
      builder.append lastName, other.lastName
      builder.isEquals()
   }
   static mapping = {
      id composite: ["firstName", "lastName"]
   }
}

Since it has a composite primary key, the class is its own primary key so it has to implement Serializable and implement hashCode and equals.

CompoundUnique domain class.

The compound_unique table has five properties, three of which are in a compound unique index:

CREATE TABLE compound_unique (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  version bigint(20) 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 KEY prop4 (prop4,prop3,prop2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and it generates this domain class:

class CompoundUnique {
   String prop1
   String prop2
   String prop3
   String prop4
   String prop5
   static constraints = {
      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(20) NOT NULL AUTO_INCREMENT,
  version bigint(20) NOT NULL,
  name varchar(255) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE visit (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  library_id bigint(20) NOT NULL,
  person varchar(255) NOT NULL,
  visit_date datetime NOT NULL,
  PRIMARY KEY (id),
  KEY FK6B04D4BE8E8E739 (library_id),
  CONSTRAINT FK6B04D4BE8E8E739 FOREIGN KEY (library_id) REFERENCES library (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and they generate these domain classes:

class Library {
   String name
   static hasMany = [visits: Visit]
}

class Visit {
   String person
   Date visitDate
   Library library
   static belongsTo = [Library]
   static mapping = {
      version false
   }
}

visit has no version column, so the Visit has optimistic lock checking disabled (version false).

Other domain class.

The other table has a string primary key, and an optimistic locking column that's not named version. Since we configured this with the grails.plugin.reveng.versionColumns property, the column is resolved correctly:

CREATE TABLE other (
  username varchar(255) NOT NULL,
  nonstandard_version_name bigint(20) NOT NULL,
  PRIMARY KEY (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

class Other {
   String username
   static mapping = {
      id name: "username", generator: "assigned"
      version "nonstandard_version_name"
   }
}

User and Role domain classes.

The user and role tables have a many-to-many relationship, which uses the user_role join table:

CREATE TABLE user (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  version bigint(20) NOT NULL,
  account_expired bit(1) NOT NULL,
  account_locked bit(1) NOT NULL,
  enabled bit(1) NOT NULL,
  password varchar(255) NOT NULL,
  password_expired bit(1) NOT NULL,
  username varchar(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE role (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  version bigint(20) NOT NULL,
  authority varchar(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY authority (authority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE user_role (
  role_id bigint(20) NOT NULL,
  user_id bigint(20) NOT NULL,
  date_updated datetime NOT NULL,
  PRIMARY KEY (role_id,user_id),
  KEY FK143BF46A667AF6FB (role_id),
  KEY FK143BF46ABA5BADB (user_id),
  CONSTRAINT FK143BF46A667AF6FB FOREIGN KEY (role_id) REFERENCES role (id),
  CONSTRAINT FK143BF46ABA5BADB FOREIGN KEY (user_id) REFERENCES user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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 {
   String authority
   static hasMany = [users: User]
   static belongsTo = [User]
   static constraints = {
      authority unique: true
   }
}

class User {
   Boolean accountExpired
   Boolean accountLocked
   Boolean enabled
   String password
   Boolean passwordExpired
   String username
   static hasMany = [roles: Role]
   static constraints = {
      username unique: true
   }
}

Thing domain class.

The thing table has a non-standard primary key column (thing_id) and a unique constraint on the email column. The name column is nullable, and is defined as varchar(123):

CREATE TABLE thing (
  thing_id bigint(20) NOT NULL AUTO_INCREMENT,
  version bigint(20) NOT NULL,
  email varchar(255) NOT NULL,
  float_value float NOT NULL,
  name varchar(123) DEFAULT NULL,
  PRIMARY KEY (thing_id),
  UNIQUE KEY email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and it generates this domain class:

class Thing {
   String email
   Float floatValue
   String name
   static mapping = {
      id column: "thing_id"
   }
   static constraints = {
      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 grails.plugin.reveng.destDir property in grails-app/conf/Config.groovy:

grails.plugin.reveng.destDir = 'temp_reverse_engineer'

Also change the configuration to only include the thing table:

grails.plugin.reveng.includeTables = ['thing']

Re-eun the db-reverse-engineer script:


$ grails db-reverse-engineer

The script will generate this domain class in the temp_reverse_engineer/com/revengtest folder:

class Thing {
   String email
   Float floatValue
   String name
   Boolean newColumn
   static mapping = {
      id column: "thing_id"
   }
   static constraints = {
      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.

<< 4General Usage
Quick Reference (hide)

Scripts

db-reverse-engineer