Make notes and draft notes positions queriable
Problem
The table notes
has columns original_position
, position
and change_position
with type text
, same happens with the newest draft_notes
. In DB level it persist a "Ruby object" (YAML I assume?) string:
gitlabhq_development=# SELECT position FROM "draft_notes" limit 1;
position
--------------------------------------------------------
--- !ruby/object:Gitlab::Diff::Position +
attributes: +
:base_sha: 1c59dfa64afbea8c721bb09a06a9d326c952ea19 +
:start_sha: 1c59dfa64afbea8c721bb09a06a9d326c952ea19+
:head_sha: af14638a616cd93a0757e35b1fc69c3611065b72 +
:old_path: ".gitlab-ci.yml" +
:new_path: ".gitlab-ci.yml" +
:position_type: text +
:old_line: +
:new_line: 27 +
(1 row)
In general that makes it impossible to query with postgres ->
syntax as it's not a json
or jsonb
column. It limits optimizations where we'd like to query for specific file paths and should keep biting us in other places.
We're not able to answer (with queries) how many comments are there in the latest diff version, or how many draft notes. Everything needs to be made in memory now.
Proposals
Either converting all these records to jsonb
, or create a diff_positions
table to store this data. notes
and draft_notes
could instead refer to position_id
, original_position_id
and change_position_id
.