CREATE TABLE `company_field`(
|
`id` BIGINT(11) AUTO_INCREMENT,
|
`name` VARCHAR(50) NOT NULL,
|
`manager` VARCHAR(50) NULL,
|
`tel` VARCHAR (50) NULL,
|
`email` VARCHAR (255) NULL,
|
`memo` VARCHAR(255) NULL,
|
`register_id` BIGINT(20) NOT NULL,
|
`register_date` TIMESTAMP NULL,
|
`modify_id` BIGINT(20) NOT NULL,
|
`modify_date` TIMESTAMP NULL,
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
CREATE TABLE `isp_field`(
|
`id` BIGINT(11) AUTO_INCREMENT,
|
`code` varchar (50) NOT NULL,
|
`name` VARCHAR(50) NOT NULL,
|
`manager` VARCHAR(50) NULL,
|
`tel` VARCHAR (50) NULL,
|
`email` VARCHAR (255) NULL,
|
`memo` VARCHAR(255) NULL,
|
`register_id` BIGINT(20) NOT NULL,
|
`register_date` TIMESTAMP NULL,
|
`modify_id` BIGINT(20) NOT NULL,
|
`modify_date` TIMESTAMP NULL,
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
CREATE TABLE `hosting_field`(
|
`id` BIGINT(11) AUTO_INCREMENT,
|
`code` VARCHAR(50) NOT NULL,
|
`name` VARCHAR(50) NOT NULL,
|
`manager` VARCHAR(50) NULL,
|
`tel` VARCHAR (50) NULL,
|
`email` VARCHAR (255) NULL,
|
`memo` VARCHAR(255) NULL,
|
`register_id` BIGINT(20) NOT NULL,
|
`register_date` TIMESTAMP NULL,
|
`modify_id` BIGINT(20) NOT NULL,
|
`modify_date` TIMESTAMP NULL,
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
CREATE TABLE `user_department`(
|
`id` BIGINT(11) AUTO_INCREMENT,
|
`department_id` BIGINT(11),
|
`user_id` BIGINT(11),
|
`register_id` BIGINT(20) NOT NULL,
|
`register_date` TIMESTAMP NULL,
|
`modify_id` BIGINT(20) NOT NULL,
|
`modify_date` TIMESTAMP NULL,
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
-- api 관련
|
ALTER TABLE `user` ADD COLUMN `insert_type` VARCHAR(1) NOT NULL DEFAULT 'N';
|
|
CREATE TABLE `api_token`(
|
`id` BIGINT(11) AUTO_INCREMENT,
|
`user_id` BIGINT(50) NOT NULL,
|
`app_name` VARCHAR(50) NOT NULL,
|
`token` VARCHAR(1024) NOT NULL,
|
`register_id` BIGINT(20) NOT NULL,
|
`register_date` TIMESTAMP NULL,
|
`modify_id` BIGINT(20) NOT NULL,
|
`modify_date` TIMESTAMP NULL,
|
PRIMARY KEY (`id`) USING BTREE,
|
INDEX `userIdIndex` (`user_id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
CREATE TABLE `issue_api_default`(
|
`id` BIGINT(11) AUTO_INCREMENT,
|
`user_id` BIGINT(11) NOT NULL,
|
`issue_type_id` bigint(11) NOT NULL,
|
`issue_status_id` bigint(20) DEFAULT NULL,
|
`project_id` bigint(20) DEFAULT NULL,
|
`priority_id` bigint(20) DEFAULT NULL,
|
`severity_id` bigint(20) DEFAULT NULL,
|
`title` varchar(300) DEFAULT NULL,
|
`description` mediumtext COMMENT 'description',
|
`reverse_index` bigint(20) DEFAULT NULL,
|
`issue_number` bigint(20) DEFAULT NULL,
|
`start_date` varchar(20) DEFAULT NULL,
|
`complete_date` varchar(20) DEFAULT NULL,
|
`register_id` BIGINT(20) NOT NULL,
|
`register_date` TIMESTAMP NULL,
|
`modify_id` BIGINT(20) NOT NULL,
|
`modify_date` TIMESTAMP NULL,
|
PRIMARY KEY (`id`) USING BTREE,
|
INDEX `userIdIndex` (`user_id`) USING BTREE,
|
INDEX `issueTypeIdIndex` (`issue_type_id`) USING BTREE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
CREATE TABLE `custom_field_api_default`(
|
`id` BIGINT(11) AUTO_INCREMENT,
|
`user_id` BIGINT(11) NOT NULL,
|
`issue_type_id` BIGINT(11) NOT NULL,
|
`custom_field_id` BIGINT(11) NOT NULL,
|
`custom_field_value` varchar(300) NOT NULL,
|
`register_id` BIGINT(20) NOT NULL,
|
`register_date` TIMESTAMP NULL,
|
`modify_id` BIGINT(20) NOT NULL,
|
`modify_date` TIMESTAMP NULL,
|
PRIMARY KEY (`id`) USING BTREE,
|
INDEX `userIdIndex` (`user_id`) USING BTREE,
|
INDEX `issueTypeIdIndex` (`issue_type_id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
CREATE TABLE `custom_field_api_overlap`(
|
`id` BIGINT(11) AUTO_INCREMENT,
|
`user_id` BIGINT(11) NOT NULL,
|
`issue_type_id` BIGINT(11) NOT NULL,
|
`custom_field_id` BIGINT(11) NOT NULL,
|
`register_id` BIGINT(20) NOT NULL,
|
`register_date` TIMESTAMP NULL,
|
`modify_id` BIGINT(20) NOT NULL,
|
`modify_date` TIMESTAMP NULL,
|
PRIMARY KEY (`id`) USING BTREE,
|
INDEX `userIdIndex` (`user_id`) USING BTREE,
|
INDEX `issueTypeIdIndex` (`issue_type_id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
-- 이슈 타입 프로젝트
|
ALTER TABLE `issue_type` ADD COLUMN `project_id` BIGINT(11) NULL;
|
|
-- 상위 이슈
|
ALTER TABLE `issue` ADD COLUMN `parent_issue_id` BIGINT(11) NULL;
|
|
CREATE TABLE `issue_company` (
|
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
|
`issue_id` bigint(20) NOT NULL,
|
`company_id` bigint(20) DEFAULT NULL,
|
`name` VARCHAR(50) NULL,
|
`manager` VARCHAR(50) NULL,
|
`tel` VARCHAR (50) NULL,
|
`email` VARCHAR (255) NULL,
|
`memo` VARCHAR(255) NULL,
|
`register_id` bigint(20) NOT NULL COMMENT 'register_id',
|
`register_date` timestamp NULL DEFAULT NULL COMMENT 'register_date',
|
`modify_id` bigint(20) NOT NULL COMMENT 'modify_id',
|
`modify_date` timestamp NULL DEFAULT NULL COMMENT 'modify_date',
|
PRIMARY KEY (`id`) USING BTREE,
|
INDEX `companyIdIndex` (`company_id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
CREATE TABLE `issue_hosting` (
|
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
|
`issue_id` bigint(20) NOT NULL,
|
`hosting_id` bigint(20) DEFAULT NULL,
|
`name` VARCHAR(50) NULL,
|
`code` VARCHAR(50) NULL,
|
`manager` VARCHAR(50) NULL,
|
`tel` VARCHAR (50) NULL,
|
`email` VARCHAR (255) NULL,
|
`memo` VARCHAR(255) NULL,
|
`register_id` bigint(20) NOT NULL COMMENT 'register_id',
|
`register_date` timestamp NULL DEFAULT NULL COMMENT 'register_date',
|
`modify_id` bigint(20) NOT NULL COMMENT 'modify_id',
|
`modify_date` timestamp NULL DEFAULT NULL COMMENT 'modify_date',
|
PRIMARY KEY (`id`) USING BTREE,
|
INDEX `hostingIdIndex` (`hosting_id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
CREATE TABLE `issue_isp` (
|
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
|
`issue_id` bigint(20) NOT NULL,
|
`isp_id` bigint(20) DEFAULT NULL,
|
`name` VARCHAR(50) NULL,
|
`code` VARCHAR(50) NULL,
|
`manager` VARCHAR(50) NULL,
|
`tel` VARCHAR (50) NULL,
|
`email` VARCHAR (255) NULL,
|
`memo` VARCHAR(255) NULL,
|
`register_id` bigint(20) NOT NULL COMMENT 'register_id',
|
`register_date` timestamp NULL DEFAULT NULL COMMENT 'register_date',
|
`modify_id` bigint(20) NOT NULL COMMENT 'modify_id',
|
`modify_date` timestamp NULL DEFAULT NULL COMMENT 'modify_date',
|
PRIMARY KEY (`id`) USING BTREE,
|
INDEX `ispIdIndex` (`isp_id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
-- 이슈 테이블 타입 설정(1: 메인, 2: 연관, 3: 하위)
|
ALTER TABLE `issue_table_config` ADD COLUMN `issue_id` BIGINT(20) NOT NULL;
|
ALTER TABLE `issue_table_config` ADD COLUMN `issue_table_type` INT(11) NOT NULL;
|
ALTER TABLE `issue_table_config` ADD INDEX `issueTableTypeIndex`(`issue_table_type`);
|
|
-- issue_risk 테이블 부서변경 카운트 컬럼 추가
|
ALTER TABLE `issue_risk` ADD COLUMN `change_department_count` BIGINT(20) NOT NULL DEFAULT '0';
|
|
-- 워크 플로우 상태 담당부서 지정
|
CREATE TABLE `workflow_department`(
|
`id` BIGINT(11) AUTO_INCREMENT,
|
`workflow_id` BIGINT(11),
|
`issue_status_id` BIGINT(11),
|
`department_id` BIGINT(11),
|
`register_id` BIGINT(20) NOT NULL,
|
`register_date` TIMESTAMP NULL,
|
`modify_id` BIGINT(20) NOT NULL,
|
`modify_date` TIMESTAMP NULL,
|
PRIMARY KEY (`id`) USING BTREE,
|
INDEX `workflowIdIndex` (`workflow_id`) USING BTREE,
|
INDEX `issueStatusIdIndex` (`issue_status_id`) USING BTREE,
|
INDEX `departmentIdIndex` (`department_id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
-- issue_type 테이블 업체,ISP,호스팅 컬럼 추가
|
ALTER TABLE `issue_type` ADD COLUMN `company_yn` VARCHAR(1) NOT NULL DEFAULT 'N';
|
ALTER TABLE `issue_type` ADD COLUMN `isp_yn` VARCHAR(1) NOT NULL DEFAULT 'N';
|
ALTER TABLE `issue_type` ADD COLUMN `hosting_yn` VARCHAR(1) NOT NULL DEFAULT 'N';
|
