2

The problem is Galera cluster requires that all tables have a ID PK. Grails mapping tables have no ID, no PK, and are not in 1st normal form even. It doesn't seem to even stop duplicate entries.

e.g.:

Class Role {
  String description
  static hasMany [permissions :Permission]
}

Class Permission {
   String name
}

This creates a table role_permission with the following columns:

  • role_permissions_id
  • permission_id

No ID!

We tried using liquibase to put an ID on the table, but every time we do a DB diff with an update generated version, it takes it off again - we need a more permanent fix.

I assume we could create our own mapping table:

class role_permission_custom {
    Permission permission
    Role role
}

But how do we get grails 2.5 to use this?

We dont need any auto delete cascade stuff, but we would like the existing UIs to work...

We tried the following:

Class Role {
  String description
  static hasMany [permissions :Permission]
  static mapping = {
     permissions joinTable: [name: "role_permission_map", key: "role_id"]
  }
}

class RolePermissionMap {
   Permission permission
   Role role
}

But this does not work at all. Firstly, it does not save the mappings when we create them (and does not give any kind of error):

            portalPermission = new Permission(...)
            new Role(description: "user",
                    permissions: [portalPermission, playerViewPermission, transactionsPlayerView]).save(failOnError: true)

Secondly, the project now gives: "HeuristicCompletionException: Heuristic completion: outcome state is mixed" and wont start, which we have not figured out yet.

I also tried this:

class RolePermissionMap {
   Permission permission
   Role rolePermissions
   static mapping = {
       version false
   }
}

Interestingly, some time later in the bootstrap, I get this:

Field 'role_permissions_id' doesn't have a default value
John Little
  • 10,707
  • 19
  • 86
  • 158

2 Answers2

2

OK, I have found a fully working solution, and it turns out to be easy and works with spring security and our custom authentication!!!

Class Role {
   String description
   static hasMany [permissions :Permission]
   static mapping = {
       permissions joinTable: [name: "role_permission_map", key: "role_permissions_id"]
   }
 }

class RolePermissionMap {
   Permission permission
   Role rolePermissions

   static mapping = {
       version false
       rolePermissions unique: 'permission'
   }
}

Now we just have to change the way we were assigning permissions to roles in bootstrap to this:

portalPermission = new Permission(...).save(failOnError: true)
Role role = new Role(description: "user").save(failOnError: true)
role.addToPermissions(portalPermission).save(failOnError: true)

and the app works unmodified, with a proper ID column on the mapping table for Galera and no duplicates.

John Little
  • 10,707
  • 19
  • 86
  • 158
0

This approach with join_table won't work will work with other settings (: But you must create a class which implements Serializable.

A junction table without an proper ID is a legacy that we unfortunately should handle, but there's a nice solution for it in this post.

We use it in our application and we can do things like Foo.create(Bar.get(it)) or .removeAll, or even objBar.getFoos(), etc.

Community
  • 1
  • 1
victorf
  • 978
  • 2
  • 17
  • 35