Activiti开发案例之创建用户表视图实现关联查询

//查询待办任务
taskService.createTaskQuery().taskCandidateOrAssigned(userId);

SELECT
  g.*
FROM ACT_ID_GROUP g,
  ACT_ID_MEMBERSHIP membership
WHERE g.ID_ = membership.GROUP_ID_
    AND membership.USER_ID_ = ?

SELECT DISTINCT
  RES.*
FROM ACT_RU_TASK RES
  LEFT JOIN ACT_RU_IDENTITYLINK I
    ON I.TASK_ID_ = RES.ID_
WHERE (RES.ASSIGNEE_ = ?
        OR (RES.ASSIGNEE_ IS NULL
            AND (I.USER_ID_ = ?
                  OR I.GROUP_ID_ IN(SELECT
                                      g.GROUP_ID_
                                    FROM ACT_ID_MEMBERSHIP g
                                    WHERE g.USER_ID_ = ?))))

SELECT DISTINCT
  RES.*
FROM ACT_RU_TASK RES
  LEFT JOIN ACT_RU_IDENTITYLINK I
    ON I.TASK_ID_ = RES.ID_
WHERE (RES.ASSIGNEE_ = ?
        OR (RES.ASSIGNEE_ IS NULL
            AND (I.USER_ID_ = ?
                  AND I.GROUP_ID_  IS NULL )))

CREATE TABLE `sys_user` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `username` varchar(255) NOT NULL,
   `password` varchar(255) NOT NULL,
   `email` varchar(255) NOT NULL,
   `org_id` int(11) NOT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

CREATE TABLE `sys_role` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
   `role_code` varchar(255) NOT NULL COMMENT '角色代码',
   `role_name` varchar(255) NOT NULL COMMENT '角色名称',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

CREATE TABLE `sys_user_role` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `role_id` int(11) NOT NULL,
   `user_id` int(11) NOT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

-- 删除自带的用户信息表    

DROP TABLE `act_id_membership`;
DROP TABLE `act_id_group`;
DROP TABLE `act_id_user`;
 
-- 如果之前存在视图则删除
    
DROP VIEW  IF EXISTS act_id_membership;  
DROP VIEW  IF EXISTS act_id_user;  
DROP VIEW  IF EXISTS act_id_group;
 
-- 用户视图
 
CREATE OR REPLACE VIEW act_id_user  AS   
  SELECT  
    u.username AS ID_,  
    0          AS REV_,  
    u.username   AS FIRST_,  
    '' AS LAST_,  
    u.email      AS EMAIL_,  
    u.password   AS PWD_,  
    ''          AS PICTURE_ID_  
  FROM sys_user u;  

-- 角色实体
 
CREATE VIEW act_id_group   
AS  
  SELECT r.role_name AS ID_,
         NULL AS REV_,
         r.role_name AS NAME_,
         'assignment' AS TYPE_
  FROM sys_role r;  

-- 用户-角色对应视图

CREATE VIEW act_id_membership  
AS  
   SELECT user_id AS USER_ID_,
       (SELECT r.role_name FROM sys_role r WHERE r.id=ur.role_id) AS GROUP_ID_  
   FROM sys_user_role ur;

# 检测身份信息表是否存在
spring.activiti.db-identity-used=false