grails - 导致多对多关系迁移

Question

您好，我有以下两个域类

class Users {

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

另一个类

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

这些类显然具有一对多关系，但是现在我想通过添加“ProjectMembership”类将其更改为多对多关系，但是我的问题是我的应用程序已经投入生产，并且已经有人在使用该应用程序。在这种情况下，数据库中已经有一个user-> many项目。在这种情况下，我该如何迁移此现有数据并更改产品应用程序以使其具有m2m关系，如下所示。

class Users {

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

另一个类

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

和

class ProjectMemberships{
    Users u
    Projects p
}

Answer 1

最好使用Liquibase之类的迁移工具完成此操作，而http://grails.org/plugin/database-migration插件可能是Grails中最好的选择，因为它使用Liquibase并与GORM紧密集成。但这很容易手工完成。

我不会使用hasMany，因为您可以轻松管理ProjectMemberships类中的所有内容，因此您的Users和Projects类将是

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

和

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

我将使用一个使用复合键的 ProjectMemberships类，它要求它实现 Serializable并具有良好的 hashCode和 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
   }
}

使用 ProjectMemberships.create()在用户和项目之间添加关系，并使用 ProjectMemberships.remove()删除它。

运行 grails schema-export以查看更新的DDL(它将在 target/ddl.sql中)。运行 project_memberships表的create table语句，例如

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

然后使用此SQL填充它(根据您的数据库，您可能需要稍微不同的语法):

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

最后从 project_lead_id表中删除 projects列。

当然，在进行任何更改之前，请先进行数据库备份。

您可以使用获取用户的项目

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

以及类似的项目用户

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