Class: Skadi::Helpers::Sql

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

Class Method Summary collapse

Class Method Details

.day_of_date(_model, date_field) ⇒ Object 



54
55
56
 
# File 'app/models/skadi/helpers/sql.rb', line 54

def day_of_date(_model, date_field)
  "DATE(#{date_field})"
end

.ilike(model) ⇒ Object

Provides the keyword for case insensitive LIKE



17
18
19
20
21
22
23
24
25
26
 
# File 'app/models/skadi/helpers/sql.rb', line 17

def ilike(model)
  case model.connection.adapter_name
    when "PostgreSQL"
      "ILIKE"
    when "Mysql2", "SQLite"
      "LIKE"
    else
      raise ::Skadi::Dashboard::UnsupportedDatabaseError.new("The database adapter #{model.connection.adapter_name} is not supported")
  end
end

.month_of_date(model, date_field) ⇒ Object 



71
72
73
74
75
76
77
78
79
80
81
82
 
# File 'app/models/skadi/helpers/sql.rb', line 71

def month_of_date(model, date_field)
  case model.connection.adapter_name
    when "PostgreSQL"
      "DATE_TRUNC('month', #{date_field})::date"
    when "Mysql2"
      "DATE_FORMAT(#{date_field}, '%Y-%m-01')"
    when "SQLite"
      "DATE(#{date_field}, 'start of month')"
    else
      raise ::Skadi::Dashboard::UnsupportedDatabaseError.new("The database adapter #{model.connection.adapter_name} is not supported")
  end
end

.operator(model, operator) ⇒ Object 



5
6
7
8
9
10
11
12
13
14
 
# File 'app/models/skadi/helpers/sql.rb', line 5

def operator(model, operator)
  return case operator
    when "like"
      ilike(model)
    when "not like"
      "NOT #{ilike(model)}"
    else
      operator
  end
end

.string_before_separator(model, field, separator) ⇒ Object 



28
29
30
31
32
33
34
35
36
37
38
39
 
# File 'app/models/skadi/helpers/sql.rb', line 28

def string_before_separator(model, field, separator)
  case model.connection.adapter_name
    when "PostgreSQL"
      "SPLIT_PART(#{model.table_name}.#{field}, '#{separator}', 1)"
    when "Mysql2"
      "SUBSTRING_INDEX(#{model.table_name}.#{field}, '#{separator}', 1)"
    when "SQLite"
      "SUBSTR(#{model.table_name}.#{field}, 1, INSTR(#{model.table_name}.#{field}, '#{separator}') - 1)"
    else
      raise ::Skadi::Dashboard::UnsupportedDatabaseError.new("The database adapter #{model.connection.adapter_name} is not supported")
  end
end

.time_series(time_series, model, date_field) ⇒ Object 



41
42
43
44
45
46
47
48
49
50
51
52
 
# File 'app/models/skadi/helpers/sql.rb', line 41

def time_series(time_series, model, date_field)
  case time_series
    when "daily"
      day_of_date(model, date_field)
    when "weekly"
      week_of_date(model, date_field)
    when "monthly"
      month_of_date(model, date_field)
    else
      raise ::Skadi::Dashboard::DatasetConfigurationError.new("The time_series #{time_series} is invalid")
  end
end

.week_of_date(model, date_field) ⇒ Object 



58
59
60
61
62
63
64
65
66
67
68
69
 
# File 'app/models/skadi/helpers/sql.rb', line 58

def week_of_date(model, date_field)
  case model.connection.adapter_name
    when "PostgreSQL"
      "DATE_TRUNC('week', #{date_field})::date"
    when "Mysql2"
      "DATE_SUB(DATE(#{date_field}), INTERVAL WEEKDAY(#{date_field}) DAY)"
    when "SQLite"
      "DATE(#{date_field}, '-' || ((CAST(STRFTIME('%w', #{date_field}) AS INTEGER) + 6) % 7) || ' days')"
    else
      raise ::Skadi::Dashboard::UnsupportedDatabaseError.new("The database adapter #{model.connection.adapter_name} is not supported")
  end
end