1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
class CycleAnalytics
include DatabaseMedian
def initialize(project, from:)
@project = project
@from = from
@summary = Summary.new(project, from: from)
end
def summary
@summary
end
def issue
calculate_metric!(:issue,
TableReferences.issues[:created_at],
[TableReferences.issue_metrics[:first_associated_with_milestone_at],
TableReferences.issue_metrics[:first_added_to_board_at]])
end
def plan
calculate_metric!(:plan,
[TableReferences.issue_metrics[:first_associated_with_milestone_at],
TableReferences.issue_metrics[:first_added_to_board_at]],
TableReferences.issue_metrics[:first_mentioned_in_commit_at])
end
def code
calculate_metric!(:code,
TableReferences.issue_metrics[:first_mentioned_in_commit_at],
TableReferences.merge_requests[:created_at])
end
def test
calculate_metric!(:test,
TableReferences.merge_request_metrics[:latest_build_started_at],
TableReferences.merge_request_metrics[:latest_build_finished_at])
end
def review
calculate_metric!(:review,
TableReferences.merge_requests[:created_at],
TableReferences.merge_request_metrics[:merged_at])
end
def staging
calculate_metric!(:staging,
TableReferences.merge_request_metrics[:merged_at],
TableReferences.merge_request_metrics[:first_deployed_to_production_at])
end
def production
calculate_metric!(:production,
TableReferences.issues[:created_at],
TableReferences.merge_request_metrics[:first_deployed_to_production_at])
end
private
def calculate_metric!(name, start_time_attrs, end_time_attrs)
cte_table = Arel::Table.new("cte_table_for_#{name}")
# Add a `SELECT` for (end_time - start-time), and add an alias for it.
query = Arel::Nodes::As.new(cte_table, subtract_datetimes(base_query, end_time_attrs, start_time_attrs, name.to_s))
queries = Array.wrap(median_datetime(cte_table, query, name))
results = queries.map { |query| run_query(query) }
extract_median(results).presence
end
# Join table with a row for every <issue,merge_request> pair (where the merge request
# closes the given issue) with issue and merge request metrics included. The metrics
# are loaded with an inner join, so issues / merge requests without metrics are
# automatically excluded.
def base_query
arel_table = TableReferences.merge_requests_closing_issues
# Load issues
query = arel_table.join(TableReferences.issues).on(TableReferences.issues[:id].eq(arel_table[:issue_id])).
join(TableReferences.issue_metrics).on(TableReferences.issues[:id].eq(TableReferences.issue_metrics[:issue_id])).
where(TableReferences.issues[:project_id].eq(@project.id)).
where(TableReferences.issues[:deleted_at].eq(nil)).
where(TableReferences.issues[:created_at].gteq(@from))
# Load merge_requests
query = query.join(TableReferences.merge_requests, Arel::Nodes::OuterJoin).on(TableReferences.merge_requests[:id].eq(arel_table[:merge_request_id])).
join(TableReferences.merge_request_metrics).on(TableReferences.merge_requests[:id].eq(TableReferences.merge_request_metrics[:merge_request_id]))
# Limit to merge requests that have been deployed to production after `@from`
query.where(TableReferences.merge_request_metrics[:first_deployed_to_production_at].gteq(@from))
end
# Note: We use COALESCE to pick up the first non-null column for end_time / start_time.
def subtract_datetimes(query_so_far, end_time_attrs, start_time_attrs, as)
diff_fn = case ActiveRecord::Base.connection.adapter_name
when 'PostgreSQL'
Arel::Nodes::Subtraction.new(
Arel::Nodes::NamedFunction.new("COALESCE", Array.wrap(end_time_attrs)),
Arel::Nodes::NamedFunction.new("COALESCE", Array.wrap(start_time_attrs)))
when 'Mysql2'
Arel::Nodes::NamedFunction.new(
"TIMESTAMPDIFF",
[Arel.sql('second'),
Arel::Nodes::NamedFunction.new("COALESCE", Array.wrap(start_time_attrs)),
Arel::Nodes::NamedFunction.new("COALESCE", Array.wrap(end_time_attrs))])
else
raise NotImplementedError, "Cycle analytics doesn't support your database type."
end
query_so_far.project(diff_fn.as(as))
end
def run_query(query)
if query.is_a? String
ActiveRecord::Base.connection.execute query
else
ActiveRecord::Base.connection.execute query.to_sql
end
end
def extract_median(results)
result = results.compact.first
case ActiveRecord::Base.connection.adapter_name
when 'PostgreSQL'
result.first['median'].to_f
when 'Mysql2'
result.to_a.flatten.first
end
end
end
|
