Module: Niceql::Prettifier

Defined in:
lib/niceql.rb

Constant Summary collapse

INLINE_VERBS = 
%w(WITH ASC (IN\s) COALESCE AS WHEN THEN ELSE END AND UNION ALL ON DISTINCT INTERSECT EXCEPT EXISTS NOT COUNT ROUND CAST).join('| ')
NEW_LINE_VERBS = 
'SELECT|FROM|WHERE|CASE|ORDER BY|LIMIT|GROUP BY|(RIGHT |LEFT )*(INNER |OUTER )*JOIN( LATERAL)*|HAVING|OFFSET|UPDATE'
POSSIBLE_INLINER = 
/(ORDER BY|CASE)/
VERBS = 
"#{NEW_LINE_VERBS}|#{INLINE_VERBS}"
STRINGS = 
/("[^"]+")|('[^']+')/
BRACKETS = 
'[\(\)]'
SQL_COMMENTS = 
/(\s*?--.+\s*)|(\s*?\/\*[^\/\*]*\*\/\s*)/
SQL_COMMENTS_CLEARED =

only newlined comments will be matched

/(\s*?--.+\s{1})|(\s*$\s*\/\*[^\/\*]*\*\/\s{1})/
COMMENT_CONTENT = 
/[\S]+[\s\S]*[\S]+/

Class Method Summary collapse

Class Method Details

.colorize_err_line(line) ⇒ Object 



181
182
183
184
 
# File 'lib/niceql.rb', line 181

def colorize_err_line( line )
  line.gsub(/#{VERBS}/ ) { |verb| StringColorize.colorize_verb(verb) }
    .gsub(STRINGS) { |str| StringColorize.colorize_str(str) }
end

.config ⇒ Object 



33
34
35
 
# File 'lib/niceql.rb', line 33

def config
  Niceql.config
end

.extract_err_caret_line(err_address_line, err_line, sql_body, err) ⇒ Object 



186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
 
# File 'lib/niceql.rb', line 186

def extract_err_caret_line( err_address_line, err_line, sql_body, err )
  # LINE could be quoted ( both sides and sometimes only from one ):
  # "LINE 1: ...t_id\" = $13 AND \"products\".\"carrier_id\" = $14 AND \"product_t...\n",
  err_quote = (err_address_line.match(/\.\.\.(.+)\.\.\./) || err_address_line.match(/\.\.\.(.+)/) )&.send(:[], 1)

  # line[2] is original err caret line i.e.: '      ^'
  # err_address_line[/LINE \d+:/].length+1..-1 - is a position from error quote begin
  err_caret_line = err.lines[2][err_address_line[/LINE \d+:/].length+1..-1]

  # when err line is too long postgres quotes it in double '...'
  # so we need to reposition caret against original line
  if err_quote
    err_quote_caret_offset = err_caret_line.length - err_address_line.index( '...' ).to_i + 3
    err_caret_line =  ' ' * ( err_line.index( err_quote ) + err_quote_caret_offset ) + "^\n"
  end

  # older versions of ActiveRecord were adding ': ' before an original query :(
  err_caret_line.prepend('  ') if sql_body[0].start_with?(': ')
  # if mistake is on last string than err_line.last != \n then we need to prepend \n to caret line
  err_caret_line.prepend("\n") unless err_line[-1] == "\n"
  err_caret_line
end

.indent_multiline(verb, indent) ⇒ Object 



173
174
175
176
177
178
179
 
# File 'lib/niceql.rb', line 173

def indent_multiline( verb, indent )
  if verb.match?(/.\s*\n\s*./)
    verb.lines.map!{|ln| ln.prepend(' ' * indent)}.join("\n")
  else
    verb.prepend(' ' * indent)
  end
end

.prettify_err(err, original_sql_query = nil) ⇒ Object 



37
38
39
 
# File 'lib/niceql.rb', line 37

def prettify_err(err, original_sql_query = nil)
  prettify_pg_err( err.to_s, original_sql_query )
end

.prettify_multiple(sql_multi, colorize = true) ⇒ Object 



161
162
163
164
165
166
167
168
169
170
 
# File 'lib/niceql.rb', line 161

def prettify_multiple( sql_multi, colorize = true )
  sql_multi.split( /(?>#{SQL_COMMENTS})|(\;)/ ).inject(['']) { |queries, pattern|
    queries.last << pattern
    queries << '' if pattern == ';'
    queries
  }.map!{ |sql|
    # we were splitting by comments and ;, so if next sql start with comment we've got a misplaced \n\n
    sql.match?(/\A\s+\z/) ? nil : prettify_sql( sql, colorize )
  }.compact.join("\n\n")
end

.prettify_pg_err(err, original_sql_query = nil) ⇒ Object

prettify_pg_err parses ActiveRecord::StatementInvalid string, but you may use it without ActiveRecord either way: prettify_pg_err( err + “n” + sql ) OR prettify_pg_err( err, sql ) don’t mess with original sql query, or prettify_pg_err will deliver incorrect results



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
 
# File 'lib/niceql.rb', line 63

def prettify_pg_err(err, original_sql_query = nil)
  return err if err[/LINE \d+/].nil?
  # LINE 2: ... -> err_line_num = 2
  err_line_num = err.match(/LINE (\d+):/)[1].to_i
  # LINE 1: SELECT usr FROM users ORDER BY 1
  err_address_line = err.lines[1]

  sql_start_line_num = 3 if err.lines.length <= 3
  # error not always contains HINT
  sql_start_line_num ||= err.lines[3][/(HINT|DETAIL)/] ? 4 : 3
  sql_body_lines = sql_start_line_num < err.lines.length ? err.lines[sql_start_line_num..-1] : original_sql_query&.lines

  # this means original query is missing so it's nothing to prettify
  return err unless sql_body_lines

  # this is an SQL line with an error.
  # we need err_line to properly align the caret in the caret line
  # and to apply a full red colorizing schema on an SQL line with error
  err_line = sql_body_lines[err_line_num - 1]

  #colorizing verbs, strings and error line
  err_body = sql_body_lines.map { |ln| ln == err_line ? StringColorize.colorize_err( ln ) : colorize_err_line(ln) }

  err_caret_line = extract_err_caret_line( err_address_line, err_line, sql_body_lines, err )
  err_body.insert( err_line_num, StringColorize.colorize_err( err_caret_line ) )

  err.lines[0..sql_start_line_num-1].join + err_body.join
end

.prettify_sql(sql, colorize = true) ⇒ Object 



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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
 
# File 'lib/niceql.rb', line 92

def prettify_sql( sql, colorize = true )
  indent = 0
  parentness = []

  sql = sql.split( SQL_COMMENTS ).each_slice(2).map{ | sql_part, comment |
    # remove additional formatting for sql_parts but leave comment intact
    [sql_part.gsub(/[\s]+/, ' '),
     # comment.match?(/\A\s*$/) - SQL_COMMENTS gets all comment content + all whitespaced chars around
     # so this sql_part.length == 0 || comment.match?(/\A\s*$/) checks does the comment starts from new line
     comment && ( sql_part.length == 0 || comment.match?(/\A\s*$/) ? "\n#{comment[COMMENT_CONTENT]}\n" : comment[COMMENT_CONTENT] ) ]
  }.flatten.join(' ')

  sql.gsub!(/ \n/, "\n")

  sql.gsub!(STRINGS){ |str| StringColorize.colorize_str(str) } if colorize

  first_verb  = true
  prev_was_comment = false

  sql.gsub!( /(#{VERBS}|#{BRACKETS}|#{SQL_COMMENTS_CLEARED})/) do |verb|
    if 'SELECT' == verb
      indent += config.indentation_base if !config.open_bracket_is_newliner || parentness.last.nil? || parentness.last[:nested]
      parentness.last[:nested] = true if parentness.last
      add_new_line = !first_verb
    elsif verb == '('
      next_closing_bracket = Regexp.last_match.post_match.index(')')
      # check if brackets contains SELECT statement
      add_new_line = !!Regexp.last_match.post_match[0..next_closing_bracket][/SELECT/] && config.open_bracket_is_newliner
      parentness << { nested: add_new_line }
    elsif verb == ')'
      # this also covers case when right bracket is used without corresponding left one
      add_new_line = parentness.last.nil? || parentness.last[:nested]
      indent -= ( parentness.last.nil? ? 2 * config.indentation_base : (parentness.last[:nested] ? config.indentation_base : 0) )
      indent = 0 if indent < 0
      parentness.pop
    elsif verb[POSSIBLE_INLINER]
      # in postgres ORDER BY can be used in aggregation function this will keep it
      # inline with its agg function
      add_new_line = parentness.last.nil? || parentness.last[:nested]
    else
      add_new_line = verb[/(#{INLINE_VERBS})/].nil?
    end

    # !add_new_line && previous_was_comment means we had newlined comment, and now even
    # if verb is inline verb we will need to add new line with indentation BUT all
    # inliners match with a space before so we need to strip it
    verb.lstrip! if !add_new_line && prev_was_comment

    add_new_line = prev_was_comment unless add_new_line
    add_indent = !first_verb && add_new_line

    if verb[SQL_COMMENTS_CLEARED]
      verb = verb[COMMENT_CONTENT]
      prev_was_comment = true
    else
      first_verb = false
      prev_was_comment = false
    end

    verb = StringColorize.colorize_verb(verb) if !%w[( )].include?(verb) && colorize

    subs = ( add_indent ? indent_multiline(verb, indent) : verb)
    !first_verb && add_new_line ? "\n" + subs : subs
  end

  # clear all spaces before newlines, and all whitespaces before strings endings
  sql.tap{ |slf| slf.gsub!( /\s+\n/, "\n" ) }.tap{ |slf| slf.gsub!(/\s+\z/, '') }
end