Module: Whoop::Formatters::SqlFormatter

Defined in:
lib/whoop/formatters/sql_formatter.rb

Constant Summary collapse

PATTERNS_TO_PRESERVE =

Hash of patterns to preserve in the SQL. The key is the expected pattern, the value is the pattern after it has been “broken” by anbt formatting. Instances of the value are replaced by the key. Patterns are jsonb column operators from www.postgresql.org/docs/15/functions-json.html

{
  # jsonb operators without surrounding spaces
  # IE, the first one replaces " : : " with "::"
  "::" => /\s?: :\s?/,
  "->>" => /\s?- > >\s?/,
  "->" => /\s?- >\s?/,
  "#>>" => /\s?# > >\s?/,
  "#>" => /\s?# >\s?/,

  # jsonb operators with surrounding spaces
  # IE, the first one replaces " @ > " with " @> "
  " @> " => /\s?@ >\s?/,
  " <@ " => /\s?< @\s?/,
  " ?| " => /\s?\? \|\s?/,
  " ?& " => /\s?\? &\s?/,
  " || " => /\s?\| \|\s?/,
  " #- " => /\s?# -\s?/,

  # Additional broken patterns
  "[" => /\[\s?/,
  "]" => /\s?\]/
}

Class Method Summary collapse

Class Method Details

.exec_explain(sql) ⇒ String

Execute the ‘EXPLAIN` query

Parameters:

  • sql (String) —

    The SQL query

Returns:

  • (String) —

    The formatted query plan



88
89
90
91
92
93
94
95
96
97
98
99
 
# File 'lib/whoop/formatters/sql_formatter.rb', line 88

def self.exec_explain(sql)
  result = ActiveRecord::Base.connection.exec_query("EXPLAIN #{sql}")
  lines = result.rows.map(&:first)

  pretty_explain = []
  pretty_explain += lines.map { |line| " #{line}" }
  nrows = result.rows.length
  rows_label = (nrows == 1) ? "row" : "rows"
  pretty_explain << "\n(#{nrows} #{rows_label})"

  pretty_explain.join("\n")
end

.format(sql, colorize: false, explain: false) ⇒ String

Format the SQL query

Parameters:

  • sql (String) —

    The SQL query

  • colorize (Boolean) (defaults to: false) —

    • colorize the SQL query (default: false)

  • explain (Boolean) (defaults to: false) —

    • also run ‘EXPLAIN` on the query (default: false)

Returns:

  • (String) —

    The formatted SQL query



41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
# File 'lib/whoop/formatters/sql_formatter.rb', line 41

def self.format(sql, colorize: false, explain: false)
  pretty_sql = generate_pretty_sql(sql)
  return pretty_sql unless colorize

  formatter = Rouge::Formatters::TerminalTruecolor.new
  lexer = Rouge::Lexers::SQL.new

  formatted_sql = formatter.format(lexer.lex(pretty_sql))
  return formatted_sql unless explain

  raw_explain = exec_explain(sql)
  formatted_explain = formatter.format(lexer.lex(raw_explain))

  [
    "sql:\n\n".colorize(:light_black).underline,
    formatted_sql,
    "\n\n",
    "query plan:\n\n".colorize(:light_black).underline,
    formatted_explain
  ].join
end

.generate_pretty_sql(sql) ⇒ String

Generate a pretty SQL query

Parameters:

  • sql (String) —

    The SQL query

Returns:

  • (String) —

    The formatted SQL query



66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
 
# File 'lib/whoop/formatters/sql_formatter.rb', line 66

def self.generate_pretty_sql(sql)
  rule = AnbtSql::Rule.new
  rule.indent_string = "  "

  formatter = AnbtSql::Formatter.new(rule)
  formatted_string = formatter.format(sql.dup)

  # Anbt injects additional spaces into joined symbols.
  # This removes them by generating the "broken" collection
  # of symbols, and replacing them with the original.
  PATTERNS_TO_PRESERVE.each do |correct_pattern, incorrect_pattern|
    next unless incorrect_pattern.match?(formatted_string)

    formatted_string.gsub!(incorrect_pattern, correct_pattern)
  end

  formatted_string
end