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

.config ⇒ Object 



48
49
50
 
# File 'lib/niceql.rb', line 48

def config
  Niceql.config
end

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



202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
 
# File 'lib/niceql.rb', line 202

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(/\.\.\.(.+)/) ).try(:[], 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 



193
194
195
196
197
198
199
 
# File 'lib/niceql.rb', line 193

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 



52
53
54
 
# File 'lib/niceql.rb', line 52

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 



181
182
183
184
185
186
187
188
189
190
 
# File 'lib/niceql.rb', line 181

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



78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
 
# File 'lib/niceql.rb', line 78

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

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

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

  # err line will be painted in red completely, so we just remembering it and use
  # to replace after painting the verbs
  err_line = sql_body[err_line_num - 1]


  #colorizing verbs and strings
  colorized_sql_body = sql_body.join.gsub(/#{VERBS}/ ) { |verb| StringColorize.colorize_verb(verb) }
    .gsub(STRINGS){ |str| StringColorize.colorize_str(str) }

  #reassemling error message
  err_body = colorized_sql_body.lines
  # replacing colorized line contained error and adding caret line
  err_body[err_line_num - 1]= StringColorize.colorize_err( err_line )

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

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

.prettify_sql(sql, colorize = true) ⇒ Object 



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
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
 
# File 'lib/niceql.rb', line 112

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