sql_comments.rb

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)

Loading the sql_comments extension into the database also adds support for block-level comment support via Database#with_comments. You call with_comments with a hash. Queries inside the hash will include a comment based on the hash (assuming they are inside the same thread):

DB.with_comments(model: Album, action: :all) do
  DB[:albums].all
  # SELECT * FROM albums -- model:Album,action:all
end

You can nest calls to with_comments, which will combine the entries from both calls:

DB.with_comments(application: App, path: :scrubbed_path) do
  DB.with_comments(model: Album, action: :all) do
    ds = DB[:albums].all
    # SELECT * FROM albums
    # -- application:App,path:scrubbed_path,model:Album,action:all
  end
end

You can override comment entries specified in earlier blocks, or remove entries specified earlier using a nil value:

DB.with_comments(application: App, path: :scrubbed_path) do
  DB.with_comments(application: Foo, path: nil) do
    ds = DB[:albums].all
    # SELECT * FROM albums # -- application:Foo
  end
end

You can combine block-level comments with dataset-specific comments:

DB.with_comments(model: Album, action: :all) do
  DB[:table].comment("Some Comment").all
  # SELECT * FROM albums -- model:Album,action:all -- Some Comment
end

Note that Microsoft Access does not support inline comments, and attempting to use comments on it will result in SQL syntax errors.

Related modules: Sequel::SQLComments, Sequel::Database::SQLComments