gitlabのMergeRequestにつけたコメントを取得する方法

実行した際の条件

• 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,