[MySQL][Gerrit] 產出三個report紀錄

※備註-Gerrit內建所需表格說明：
● [accounts]：帳號id及name
● [changes]：列出所有changes的資料(RD上到gerrit的code)
● [changes_messages]：列出reviewer、gerrit系統回覆change的資料

※說明：
● 產出三個表格，Wei會將此表格轉成excel
● [report_sheet1]：列出reviewer花了多久時間回覆RD上的code
● [report_sheet2]：列出被reviewer reject的change及狀態
● [report_sheet3]：列出沒有commit rule的關鍵字

//=========================================================

1). [report_sheet1]

//新增change_messages1
//join author_id及name
CREATE TABLE `reviewdb`.`change_messages1` (
`author_id` INT( 11 ) NULL ,
`reviewer_name` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL ,
`written_on` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL ,
`message` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL ,
`change_id` INT( 11 ) NOT NULL ,
`uuid` VARCHAR( 40 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,
PRIMARY KEY ( `change_id` , `uuid` )
) ENGINE = MYISAM ;

insert into `change_messages1` (`author_id`,`reviewer_name`,`written_on`,`message`,`change_id`,`uuid`)
select `change_messages`.`author_id`,`accounts`.`full_name`,`change_messages`.`written_on`,`change_messages`.`message`,`change_messages`.`change_id`,`change_messages`.`uuid`
from `change_messages`
left join `accounts`
on `change_messages`.`author_id`=`accounts`.`account_id`

//產生report_sheet1
CREATE TABLE `reviewdb`.`report_sheet1` (
`num` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`change_id` INT( 11 ) NOT NULL ,
`change_key` VARCHAR( 60 ) NOT NULL ,
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`review_time` TIMESTAMP NOT NULL ,
`review_time_spent` TIME NOT NULL ,
`owner_name` VARCHAR( 255 ) NULL ,
`reviewer_name` VARCHAR( 255 ) NULL ,
`commit_subject` VARCHAR( 255 ) NOT NULL ,
`project_name` VARCHAR( 255 ) NOT NULL ,
`branch_name` VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM ;

INSERT INTO `report_sheet1` (change_id,change_key,create_time,review_time,review_time_spent,owner_name,reviewer_name,commit_subject,project_name,branch_name)
SELECT `changes`.`change_id`,
`changes`.`change_key`,
`changes`.`created_on`,
`change_messages1`.`written_on`,
SEC_TO_TIME( TimeStampDiff(second,`created_on`,`written_on`) ),
`accounts`.`full_name`,
`change_messages1`.`reviewer_name`,
`changes`.`subject`,
`changes`.`dest_project_name`,
`changes`.`dest_branch_name`
FROM `changes`
LEFT JOIN `change_messages1` ON `changes`.`change_id`=`change_messages1`.`change_id`
LEFT JOIN `accounts` ON `changes`.`owner_account_id`=`accounts`.`account_id`
WHERE (`changes`.`owner_account_id`!=`change_messages1`.`athor_id`) and (TimeStampDiff(second,`created_on`,`written_on`) >= 86400)


//=========================================================

2). [report_sheet2]

//新增change_messages2
//join author_id及name
//搜尋reviewer_message有'not be merged'
//刪除重複的change_id
CREATE TABLE `reviewdb`.`change_messages2` (
`author_id` INT( 11 ) NULL ,
`reviewer_name` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL ,
`written_on` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL ,
`message` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL ,
`change_id` INT( 11 ) NOT NULL ,
`uuid` VARCHAR( 40 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,
PRIMARY KEY ( `change_id` , `uuid` )
) ENGINE = MYISAM ;

INSERT INTO `change_messages2` (`author_id`,`reviewer_name`,`written_on`,`message`,`change_id`,`uuid`)
SELECT `change_messages`.author_id,
`accounts`.`full_name` AS reviewer_name,
`change_messages`.written_on,
`change_messages`.message,
`change_messages`.change_id,
`change_messages`.uuid
FROM `change_messages`
LEFT JOIN `accounts`
ON `change_messages`.`author_id`=`accounts`.`account_id`
WHERE `message` LIKE '%not be merged%'
OR `message` LIKE '%Abandoned%'
OR `message` LIKE '%Do not submit%'
OR `message` LIKE '%Fails%';
ALTER IGNORE TABLE `change_messages2` ADD UNIQUE INDEX(`change_id`);


//建立changes1
//關聯change及review
//增加一個num(AUTO_INCREMENT)
CREATE TABLE `reviewdb`.`changes1` (
`num` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`change_key` VARCHAR(60) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
`created_on` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_updated_on` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`sort_key` VARCHAR(16) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
`owner_account_id` INT(11) NOT NULL DEFAULT '0',
`owner_name` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
`dest_project_name` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
`dest_branch_name` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
`open` CHAR(1) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'N',
`status` CHAR(1) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
`nbr_patch_sets` INT(11) NOT NULL DEFAULT '0',
`current_patch_set_id` INT(11) NOT NULL DEFAULT '0',
`subject` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
`topic` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
`row_version` INT(11) NOT NULL DEFAULT '0',
`change_id` INT(11) NOT NULL DEFAULT '0',
`written_on` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ,
`reviewer_name` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
`reviewer_message` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL
) ENGINE = MyISAM;

insert into changes1(`change_key`,`created_on`,`last_updated_on`,`sort_key`,`owner_account_id`,`owner_name`,`dest_project_name`,`dest_branch_name`,`open`,`status`,`nbr_patch_sets`,`current_patch_set_id`,`subject`,`topic`,`row_version`,`change_id`,`written_on`,`reviewer_name`,`reviewer_message`)
select `changes`.`change_key`,
`changes`.`created_on`,
`changes`.`last_updated_on`,
`changes`.`sort_key`,
`changes`.`owner_account_id`,
`accounts`.`full_name`,
`changes`.`dest_project_name`,
`changes`.`dest_branch_name`,
`changes`.`open`,
`changes`.`status`,
`changes`.`nbr_patch_sets`,
`changes`.`current_patch_set_id`,
`changes`.`subject`,
`changes`.`topic`,
`changes`.`row_version`,
`changes`.`change_id`,
`change_messages1`.`written_on`,
`change_messages1`.`reviewer_name`,
`change_messages1`.`message`
from `changes`
left join `accounts`
on `changes`.`owner_account_id`=`accounts`.`account_id`
left join `change_messages1`
on `changes`.`change_id`=`change_messages1`.`change_id`


//產生report_sheet2
CREATE TABLE `reviewdb`.`report_sheet2` (
`num` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`change_id` INT( 11 ) NOT NULL ,
`change_key` VARCHAR( 60 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,
`open` CHAR( 1 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,
`status` CHAR( 1 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,
`written_on` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL ,
`owner_name` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL ,
`reviewer_name` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL ,
`reviewer_message` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL ,
`commit_subject` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,
`project_name` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,
`branch_name` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL
) ENGINE = MYISAM ;

INSERT INTO `report_sheet2` (`change_id`,`change_key`,`open`,`status`,`written_on`,`owner_name`,`reviewer_name`,`reviewer_message`,`commit_subject`,`project_name`,`branch_name`)
SELECT `change_messages2`.`change_id`,
`changes1`.`change_key`,
`changes1`.`open`,
`changes1`.`status`,
`changes1`.`written_on`,
`changes1`.`owner_name`,
`changes1`.`reviewer_name`,
`changes1`.`reviewer_message`,
`changes1`.`subject`,
`changes1`.`dest_project_name`,
`changes1`.`dest_branch_name`
FROM `change_messages2`
LEFT JOIN `changes1`
ON `change_messages2`.`change_id`=`changes1`.`change_id`
ORDER BY `change_id` ASC, `written_on` ASC



//=========================================================

3). [report_sheet3]

//找出commit裡沒有topic, release note, involve, comment
CREATE TABLE `reviewdb`.`report_sheet3` (
`num` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`change_id` INT( 11 ) NOT NULL ,
`change_key` VARCHAR( 60 ) NOT NULL ,
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`review_time` TIMESTAMP NOT NULL ,
`review_time_spent` TIME NOT NULL ,
`owner_name` VARCHAR( 255 ) NULL ,
`reviewer_name` VARCHAR( 255 ) NULL ,
`commit_subject` VARCHAR( 255 ) NOT NULL ,
`project_name` VARCHAR( 255 ) NOT NULL ,
`branch_name` VARCHAR( 255 ) NOT NULL ,
`comment` TEXT NULL
) ENGINE = MYISAM ;

INSERT INTO `report_sheet3` (change_id,change_key,create_time,review_time,review_time_spent,owner_name,reviewer_name,commit_subject,project_name,branch_name)
SELECT `changes`.`change_id`,
`changes`.`change_key`,
`changes`.`created_on`,
`change_messages1`.`written_on`,
SEC_TO_TIME( TimeStampDiff(second,`created_on`,`written_on`) ),
`accounts`.`full_name`,
`change_messages1`.`reviewer_name`,
`changes`.`subject` AS `commit_subject`,
`changes`.`dest_project_name`,
`changes`.`dest_branch_name`
FROM `changes`
LEFT JOIN `change_messages1` ON `changes`.`change_id`=`change_messages1`.`change_id`
LEFT JOIN `accounts` ON `changes`.`owner_account_id`=`accounts`.`account_id`
WHERE `subject` NOT LIKE '%topic%'
AND `subject` NOT LIKE '%release note%'
AND `subject` NOT LIKE '%involve%'
AND `subject` NOT LIKE '%comment%';