Implement a database median strategy for MySQL.

1. Dispatch between the two strategies automatically based on the
   current database type.

2. The MySQL version needs to run multiple statements, so the
   `cycle_analytics` model is modified to support this.

1 files changed, 43 insertions, 12 deletions

diff --git a/app/models/cycle_analytics.rb b/app/models/cycle_analytics.rb
index 561ddd258db..ef3f1bbcab5 100644
--- a/app/models/cycle_analytics.rb
+++ b/app/models/cycle_analytics.rb
@@ -61,18 +61,10 @@ class CycleAnalytics
     cte_table = Arel::Table.new("cte_table_for_#{name}")
 
     # Add a `SELECT` for (end_time - start-time), and add an alias for it.
-    # Note: We use COALESCE to pick up the first non-null column for end_time / start_time.
-    query = Arel::Nodes::As.new(
-      cte_table,
-      base_query.project(
-        Arel::Nodes::Subtraction.new(
-        Arel::Nodes::NamedFunction.new("COALESCE", Array.wrap(end_time_attrs)),
-        Arel::Nodes::NamedFunction.new("COALESCE", Array.wrap(start_time_attrs))
-      ).as(name.to_s)))
-
-    query = median_datetime(cte_table, query, name)
-    median = ActiveRecord::Base.connection.execute(query.to_sql).first['median']
-    median.to_f if median.present?
+    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
@@ -96,4 +88,43 @@ class CycleAnalytics
     # 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