実行した際の条件
- gitlabのバージョンは8.13.1
- MergeRequestのコメント記載時にレビューは#RV、レビューに対するコメントには#REをつける前提
- 結果の加工は気合
gitlabのPostgresに接続
gitlab-psql -d gitlabhq_production
テーブルを眺めまわして最終的にこんな感じのSQLを実行
with comment as (
select
projects.id,
line_code,
note,
users.name,
notes.created_at,
notes.noteable_id,
notes.discussion_id
from notes, users, projects
where author_id=users.id
and ( noteable_type='Commit' or noteable_type='MergeRequest' )
and projects.id=notes.project_id
and projects.name='<プロジェクト名>'
order by notes.created_at
)
select
'"' || review.line_code || '"',
'"' || review.created_at || '"',
'"' || review.name || '"',
'"' || replace(review.note,'"','""') || '"',
'"' || recomment.created_at || '"',
'"' || recomment.name || '"',
'"' || replace(recomment.note,'"','""') || '"'
from
(select * from comment where note like '%#RV%') as review
left join (select * from comment where note like '%#RE%') as recomment
on (review.discussion_id = recomment.discussion_id);
最終的にはこんな感じで標準出力
comment.bash
#!/bin/sh
gitlab-psql -d gitlabhq_production -c " \
with comment as ( \
select \
projects.id, \
line_code, \
note, \
users.name, \
notes.created_at, \
notes.noteable_id, \
notes.discussion_id \
from notes, users, projects \
where author_id=users.id \
and ( noteable_type='Commit' or noteable_type='MergeRequest' ) \
and projects.id=notes.project_id \
and projects.name='<プロジェクト名>' \
order by notes.created_at \
) \
select \
'\"' || review.line_code || '\"', \
'\"' || review.created_at || '\"', \
'\"' || review.name || '\"', \
'\"' || replace(review.note,'\"','\"\"') || '\"', \
'\"' || recomment.created_at || '\"', \
'\"' || recomment.name || '\"', \
'\"' || replace(recomment.note,'\"','\"\"') || '\"' \
from \
(select * from comment where note like '%#RV%') as review \
left join (select * from comment where note like '%#RE%') as recomment \
on (review.discussion_id = recomment.discussion_id) " -A -F,