Friday, July 18, 2008

Acegi Table Configuration

One of the main issues I had from the Grails site was using the often reserved words "user" and "role" for the main table names.

While I was working with Oracle, "user" was an issue but "role" was not. So, generated user but mapped to a table called "app_user".

Behind the scenes I had 4 tables:
1. app_user
2. role
3. role_app_user
4. requestmap

Also, generated a sequence called hibernate_sequence

I ran into intermittent problems where hibernate would not be able to identify the intersection table "role_app_user".

I decided to rename the "role" table to "app_role" and tell Grails the specific name of the intersection table. By adding the table name and the "joinTable" option to the mapping, we identify the link between the classes and their intersection table.

File: Role.groovy
class Role {
static hasMany = [people: User]

String description
String authority = 'ROLE_'

static constraints = {
authority(blank: false)
description()
}

static mapping = {
table "app_role"
people joinTable:[name:'ROLE_APP_USER', key:'id', column:'people_id']
}

}

File: User.groovy
class User {
static transients = ['pass']
static hasMany = [authorities: Role]
static belongsTo = Role

String username
String userRealName
String passwd
boolean enabled
String email
boolean emailShow
String description = ''
String pass = '[secret]'

static constraints = {
username(blank: false, unique: true)
userRealName(blank: false)
passwd(blank: false)
enabled()
description (nullable: true)
}

static mapping = {
table "app_user"
authorities joinTable:[name:'ROLE_APP_USER', key:'id', column:'authorities_id']
}
}

In preparation of moving this code to testing and production regions describe (Oracle's "desc object") to extract the tables and sequence for your DBA's convenience.

No comments: