1
2
3
4
5
6
7
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
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
91
92
93
94
95
96
|
# frozen-string-literal: true
#
# The sql_comments extension adds Dataset#comment to the datasets,
# allowing you to set SQL comments in the resulting query. These
# comments are appended to the end of the SQL query:
#
# ds = DB[:table].comment("Some Comment").all
# # SELECT * FROM table -- Some Comment
# #
#
# As you can see, this uses single line SQL comments (--) suffixed
# by a newline. This plugin transforms all consecutive whitespace
# in the comment to a single string:
#
# ds = DB[:table].comment("Some\r\nComment Here").all
# # SELECT * FROM table -- Some Comment Here
# #
#
# The reason for the prefixing and suffixing by newlines is to
# work correctly when used in subqueries:
#
# ds = DB[:table].comment("Some\r\nComment Here")
# ds.where(id: ds).all
# # SELECT * FROM table WHERE (id IN (SELECT * FROM table -- Some Comment Here
# # )) -- Some Comment Here
# #
#
# In addition to working on SELECT queries, it also works when
# inserting, updating, and deleting.
#
# Due to the use of single line SQL comments and converting all
# whitespace to spaces, this should correctly handle even
# malicious input. However, it would be unwise to rely on that,
# you should ensure that the argument given
# to Dataset#comment is not derived from user input.
#
# You can load this extension into specific datasets:
#
# ds = DB[:table]
# ds = ds.extension(:sql_comments)
#
# Or you can load it into all of a database's datasets, which
# is probably the desired behavior if you are using this extension:
#
# DB.extension(:sql_comments)
#
# Note that Microsoft Access does not support inline comments,
# and attempting to use comments on it will result in SQL syntax
# errors.
#
# Related module: Sequel::SQLComments
#
module Sequel
module SQLComments
# Return a modified copy of the dataset that will use the given comment.
# To uncomment a commented dataset, pass nil as the argument.
def comment(comment)
clone(:comment=>(format_sql_comment(comment) if comment))
end
%w'select insert update delete'.each do |type|
define_method(:"#{type}_sql") do |*a|
sql = super(*a)
if comment = @opts[:comment]
# This assumes that the comment stored in the dataset has
# already been formatted. If not, this could result in SQL
# injection.
#
# Additionally, due to the use of an SQL comment, if any
# SQL is appened to the query after the comment is added,
# it will become part of the comment unless it is preceded
# by a newline.
if sql.frozen?
sql += comment
sql.freeze
else
sql << comment
end
end
sql
end
end
private
# Format the comment. For maximum compatibility, this uses a
# single line SQL comment, and converts all consecutive whitespace
# in the comment to a single space.
def format_sql_comment(comment)
" -- #{comment.to_s.gsub(/\s+/, ' ')}\n"
end
end
Dataset.register_extension(:sql_comments, SQLComments)
end
|
