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

※說明：
● 將原本建table改成建view
● 因view無法建key，原本在change_messages2建index key，刪掉重複的資料
● 改成下group by，每筆change_id只要列一筆資料就好

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

1). [report_sheet1]

//新增change_messages1
//join author_id及name

CREATE VIEW `change_messages1` AS
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`

//產生report_sheet1
//刪掉SI_num
CREATE VIEW `report_sheet1` AS
SELECT `changes`.`change_id`,
`changes`.`change_key`,
`changes`.`created_on` AS `create_time`,
`change_messages1`.`written_on` AS `review_time`,
SEC_TO_TIME( TimeStampDiff(second,`created_on`,`written_on`) ) AS `review_time_spent`,
`accounts`.`full_name` AS `owner_name`,
`change_messages1`.`reviewer_name`,
`changes`.`subject` AS `commit_subject`,
`changes`.`dest_project_name` AS `project_name`,
`changes`.`dest_branch_name` AS `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`.`author_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 - 用group by，只列出相同change_id的其中一筆
CREATE VIEW `change_messages2` AS
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%'
group by `change_messages`.`change_id`


//建立changes1
//關聯change及review
//增加一個num(AUTO_INCREMENT)
CREATE VIEW `changes1` AS
SELECT `changes`.`change_key`,
`changes`.`created_on`,
`changes`.`last_updated_on`,
`changes`.`sort_key`,
`changes`.`owner_account_id`,
`accounts`.`full_name` AS `owner_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 VIEW `report_sheet2` AS
SELECT `change_messages2`.`change_id`,
`changes1`.`change_key`,
`changes1`.`open`,
`changes1`.`status`,
`changes1`.`written_on`,
`changes1`.`owner_name`,
`changes1`.`reviewer_name`,
`changes1`.`message` AS `reviewer_message`,
`changes1`.`subject` AS `commit_subject`,
`changes1`.`dest_project_name` AS `project_name`,
`changes1`.`dest_branch_name` AS `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 VIEW `report_sheet3` AS
SELECT `changes`.`change_id`,
`changes`.`change_key`,
`changes`.`created_on` AS `create_time`,
`change_messages1`.`written_on` AS `review_time`,
SEC_TO_TIME( TimeStampDiff(second,`created_on`,`written_on`) ) AS `review_time_spent`,
`accounts`.`full_name` AS `owner_name`,
`change_messages1`.`reviewer_name`,
`changes`.`subject` AS `commit_subject`,
`changes`.`dest_project_name` AS `project_name`,
`changes`.`dest_branch_name` AS `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%';