sql_comments.rb

lib/sequel/extensions/sql_comments.rb
Last Update: 2016-11-23 09:48:29 -0800

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 probably attempt to 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