Class: Skadi::DashboardQuery

Inherits:
Object
  • Object
show all
Defined in:
app/models/skadi/dashboard_query.rb

Constant Summary collapse

ITEMS_PER_PAGE =

Mimics app/frontend/dashboard/dashboardElements/DynamicTable.svelte:13

10
PAGES_PER_CHUNK = 
10
POSITIVE_OPERATORS = 
[ "=", ">", ">=", "<=", "<", "like" ].freeze
NEGATED_OPERATORS = 
[ "!=", "not like" ].freeze

Class Method Summary collapse

Class Method Details

.chart_query(chart, untrusted_param_filters) ⇒ Object 



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
 
# File 'app/models/skadi/dashboard_query.rb', line 8

def chart_query(chart, untrusted_param_filters)
  queries = chart["datasets"].filter_map do |dataset|
    if dataset["type"] == "sql"
      build_sql_query(dataset, chart, untrusted_param_filters)
    elsif dataset["type"] == "percentage"
      # Percentage charts are handled by the frontend using other datasets
      next
    elsif Schema.database_schema.key?(dataset["type"].to_sym)
      schema = Schema.database_schema[dataset["type"].to_sym]

      build_query_from_schema(dataset, chart, schema, untrusted_param_filters)
    else
      raise ::Skadi::Dashboard::DatasetConfigurationError.new("Unknown dataset type #{dataset["type"]}")
    end
  end

  return { data: [] } unless queries.any?

  result = {
    data: [],
  }
  Skadi::ApplicationRecord.connection.transaction do
    if chart["type"] == "table"
      # Note that the page starts with 1
      page = if untrusted_param_filters["page"].is_a?(Integer) && untrusted_param_filters["page"] > 0
        untrusted_param_filters["page"]
      else
        1
      end

      # We cannot union more than one query when selecting multiple fields for the table dataset so just take the first one as a precaution
      query = queries.first

      result[:resultCount] = Skadi::Visit.connection.select_one("
          SELECT COUNT(*) as count FROM (#{query}) q
      ")["count"]

      last_page = [ 1, (result[:resultCount].to_f / ITEMS_PER_PAGE).ceil ].max
      offset = ([ page, last_page ].min - 1) * ITEMS_PER_PAGE
      limit = ITEMS_PER_PAGE * PAGES_PER_CHUNK

      result[:resultOffset] = offset

      result[:data] = Skadi::Visit.connection.select_all("
          SELECT * FROM (#{query}) q LIMIT #{limit} OFFSET #{offset}
      ")
    else
      result[:data] = Skadi::ApplicationRecord.connection.unprepared_statement do
        Skadi::Visit.connection.select_all(queries.join(" UNION ALL "))
      end
    end

    # Rollback after the SQL is run, preventing some side effects
    raise ActiveRecord::Rollback
  end

  return result
end