Grails many to many relationship migration

Hello I have two domain classes as following

class Users {

    String password
    String firstName
    String lastName
    String emailAddress
    String username
    Company company
.....
    static hasMany = [projects:Projects];
}

Another class

class Projects {
    String projectName
    String description
    Users projectLead
    Date dateCreated
    Date lastUpdated
    static belongsTo = Users
}

These classes obviously has one to many relationship but now I want to change it to many to many relationship by adding "ProjectMembership" class but the problem I have is that my application has already gone into production and there are people who are already using the app. In such a case they already have one user->many projects in the the db. In such a case how can I migrate this existing data and change my prod app to开发者_JAVA百科 have m2m relationship which will looks like following.

class Users {

    String password
    String firstName
    String lastName
    String emailAddress
    String username
    Company company
.....
    static hasMany = [projectMemberships:ProjectMemberships];
}

Another class

class Projects {
    String projectName
    String description
    Users projectLead
    Date dateCreated
    Date lastUpdated
    static hasMany = [projectMemberships:ProjectMemberships];
}

and

class ProjectMemberships{
    Users u
    Projects p
}

This is best done with a migration tool like Liquibase, and the http://grails.org/plugin/database-migration plugin is probably your best be in Grails since it uses Liquibase and is tightly integrated with GORM. But this one's easy enough to do by hand.

I wouldn't use hasMany since you can easily manage everything from the ProjectMemberships class, so your Users and Projects classes would be

class Users {
   String password
   String firstName
   String lastName
   String emailAddress
   String username
   Company company
.....
}

and

class Projects {
   String projectName
   String description
   Date dateCreated
   Date lastUpdated
}

I'd go with a ProjectMemberships class that uses a composite key, which requires that it implement Serializable and have a good hashCode and equals:

import org.apache.commons.lang.builder.HashCodeBuilder

class ProjectMemberships implements Serializable {
   Users u
   Projects p

   boolean equals(other) {
      if (!(other instanceof ProjectMemberships)) {
         return false
      }

      other.u?.id == u?.id && other.p?.id == p?.id
   }

   int hashCode() {
      def builder = new HashCodeBuilder()
      if (u) builder.append(u.id)
      if (p) builder.append(p.id)
      builder.toHashCode()
   }

   static ProjectMemberships get(long userId, long projectId) {
      find 'from ProjectMemberships where u.id=:userId and p.id=:projectId',
         [userId: userId, projectId: projectId]
   }

   static ProjectMemberships create(Users u, Projects p, boolean flush = false) {
      new ProjectMemberships(u: u, p: p).save(flush: flush, insert: true)
   }

   static boolean remove(Users u, Projects p, boolean flush = false) {
      ProjectMemberships instance = ProjectMemberships.findByUsersAndProjects(u, p)
      if (!instance) {
         return false
      }

      instance.delete(flush: flush)
      true
   }

   static void removeAll(Users u) {
      executeUpdate 'DELETE FROM ProjectMemberships WHERE u=:u', [u: u]
   }

   static void removeAll(Projects p) {
      executeUpdate 'DELETE FROM ProjectMemberships WHERE p=:p', [p: p]
   }

   static mapping = {
      id composite: ['p', 'u']
      version false
   }
}

Use ProjectMemberships.create() to add a relationship between a user and a project, and ProjectMemberships.remove() to remove it.

Run grails schema-export to see the updated DDL (it'll be in target/ddl.sql). Run the create table statement for the project_memberships table, e.g.

create table project_memberships (
   p_id bigint not null,
   u_id bigint not null,
   primary key (p_id, u_id)
)

Then populate it with this SQL (depending on your database you might need a slightly different syntax):

insert into project_memberships(p_id, u_id) select id, project_lead_id from projects

and finally drop the project_lead_id column from the projects table.

Of course do a database backup before making any changes.

You can get a user's projects with

def projects = ProjectMemberships.findAllByUsers(user)*.p

and similarly a project's users with

def users = ProjectMemberships.findAllByProjects(project)*.u