WTM3.x数据库升级到WTM5.x

从wtm3.x升级到wtm5.x（mysql5.7），升级方式是生成一个新项目，执行新项目生成数据库，然后把旧项目业务代码复制到新项目，把旧数据库迁移到新数据库，代码就是复制过来然后编译，然后按5.x的写法对错误逐一修改。数据库的迁移通过比对新旧表结构写了系统表数据迁移的sql，需要的朋友可以省点时间。这样比原库升级稳妥一点。使用sql的时候把databasename换成新库名，databasename_old换成老库名。

-- 从3.x老库向5.x新库迁移数据，不需要考虑改动数据结构，只要比较新老的不同即可
-- 只迁移有对应关系的字段，有三种：名称类型都不变直接迁移，改名的对应迁移（如sex->gender)，改信息的转换后迁移（如userId->userCode)
-- 迁移过程是幂等的，只要数据源不变，可以重复多次，结果相同
-- 非数据性内容可以纯手动迁移，如视图、函数、存储过程等

 
SET FOREIGN_KEY_CHECKS = 0;

truncate table  databasename.fileattachments;

insert into databasename.fileattachments(ID,`FileName`,FileExt,`Path`,`Length`,UploadTime,FileData, SaveMode)
select ID,`FileName`,FileExt,`Path`,`Length`,UploadTime,FileData,'Local' 
from databasename_old.fileattachments;

truncate table  databasename.dataprivileges;

insert into databasename.dataprivileges(ID,TableName,RelateId,CreateTime,CreateBy,UpdateTime,UpdateBy,UserCode,GroupCode)
select ID,TableName,RelateId,CreateTime,CreateBy,UpdateTime,UpdateBy,(select itcode from databasename_old.frameworkusers where id = UserId),(select groupcode from databasename_old.frameworkgroups where id = GroupId)
from databasename_old.dataprivileges;

truncate table  databasename.frameworkgroups;

insert into databasename.frameworkgroups(ID,GroupCode,GroupName,GroupRemark,CreateTime,CreateBy,UpdateTime,UpdateBy)
select ID,GroupCode,GroupName,GroupRemark,CreateTime,CreateBy,UpdateTime,UpdateBy
from databasename_old.frameworkgroups;

truncate table databasename.frameworkmenus;

insert into databasename.frameworkmenus(ID,PageName,ActionName,ModuleName,FolderOnly,IsInherit,ClassName,MethodName,ShowOnMenu,IsPublic,DisplayOrder,IsInside,`Url`,Icon,ParentId)
select ID,PageName,ActionName,ModuleName,FolderOnly,IsInherit,ClassName,MethodName,ShowOnMenu,IsPublic,DisplayOrder,IsInside,`Url`,Icon,ParentId
from databasename_old.frameworkmenus;

truncate table databasename.frameworkroles;

insert into databasename.frameworkroles(ID,RoleCode,RoleName,RoleRemark,CreateTime,CreateBy,UpdateTime,UpdateBy)
select ID,RoleCode,RoleName,RoleRemark,CreateTime,CreateBy,UpdateTime,UpdateBy 
from databasename_old.frameworkroles;

truncate table  databasename.frameworkusergroups;

insert into databasename.frameworkusergroups(ID,CreateTime,CreateBy,UpdateTime,UpdateBy,UserCode,GroupCode)
select ID,CreateTime,CreateBy,UpdateTime,UpdateBy,(select itcode from databasename_old.frameworkusers where id = UserId),(select groupcode from databasename_old.frameworkgroups where id = GroupId)
from databasename_old.frameworkusergroup;

truncate table  databasename.frameworkuserroles;

insert into databasename.frameworkuserroles(ID,CreateTime,CreateBy,UpdateTime,UpdateBy,UserCode,RoleCode)
select ID,CreateTime,CreateBy,UpdateTime,UpdateBy,(select itcode from databasename_old.frameworkusers where id = UserId),(select rolecode from databasename_old.frameworkroles where id = RoleId)
from databasename_old.frameworkuserrole;

truncate table  databasename.frameworkusers;
  
insert into databasename.frameworkusers( ID,ITCode,`Password`,`Name`,IsValid,PhotoId,Email,CellPhone,HomePhone,`Address`,ZipCode,CreateTime,CreateBy,UpdateTime,UpdateBy,Gender,Discriminator) 
select ID,ITCode,`Password`,`Name`,IsValid,PhotoId,Email,CellPhone,HomePhone,`Address`,ZipCode,CreateTime,CreateBy,UpdateTime,UpdateBy,Sex,'FrameworkUser' 
from databasename_old.frameworkusers;    

truncate table  databasename.functionprivileges;

insert into databasename.functionprivileges( ID,MenuItemId,Allowed,CreateTime,CreateBy,UpdateTime,UpdateBy,RoleCode)
select ID,MenuItemId,Allowed,CreateTime,CreateBy,UpdateTime,UpdateBy,(select rolecode from databasename_old.frameworkroles where id = RoleId)
from databasename_old.functionprivileges;

truncate table  databasename.persistedgrants;

insert into databasename.persistedgrants(ID,`Type`,CreationTime,Expiration,RefreshToken,UserCode)
select ID,`Type`,CreationTime,Expiration,RefreshToken,(select itcode from databasename_old.frameworkusers where id = UserId)
from databasename_old.persistedgrants;

SET FOREIGN_KEY_CHECKS = 1;