class Ensql::SQL
Encapsulates a plain-text SQL
statement and optional parameters to interpolate. Interpolation is indicated by one of the four placeholder formats:
-
Literal: `%{param}`
-
Interpolates `param` as a quoted string or a numeric literal depending on the class.
-
`nil` is interpolated as `'NULL'`.
-
Other objects depend on the database and the adapter, but most (like `Time`) are serialised as a quoted
SQL
string.
-
-
**List Expansion:** `%{(param)}`
-
Expands an array to a list of quoted literals.
-
Mostly useful for `column IN (1,2)` or postgres row literals.
-
Empty arrays are interpolated as `(NULL)` for
SQL
conformance. -
The parameter will be converted to an Array.
-
-
**Nested List:** `%{param(nested sql)}`
-
**SQL Fragment:** `%{!sql_param}`
Any placeholders in the SQL
must be present in the params hash or a KeyError will be raised during interpolation. Interpolation occurs just before the SQL
is executed.
@example
# Interpolate a literal Ensql.sql('SELECT * FROM users WHERE email > %{date}', date: Date.today) # SELECT * FROM users WHERE email > '2021-02-22' # Interpolate a list Ensql.sql('SELECT * FROM users WHERE name IN %{(names)}', names: ['user1', 'user2']) # SELECT * FROM users WHERE name IN ('user1', 'user2') # Interpolate a nested VALUES list Ensql.sql('INSERT INTO users (name, created_at) VALUES %{users( %{name}, now() )}', users: [{ name: "Claudia Buss" }, { name: "Lundy L'Anglais" }] ) # INSERT INTO users VALUES ('Claudia Buss', now()), ('Lundy L''Anglais', now()) # Interpolate a SQL fragement Ensql.sql('SELECT * FROM users ORDER BY %{!orderby}', orderby: Ensql.sql('name asc')) # SELECT * FROM users ORDER BY name asc
Constants
- LIST
- LITERAL
- NESTED_LIST
- SQL_FRAGMENT
Attributes
Public Class Methods
@!visibility private
# File lib/ensql/sql.rb, line 58 def initialize(sql, params = {}, name = "SQL") @sql = sql @name = name.to_s @params = params end
Public Instance Methods
(see Adapter.fetch_count)
# File lib/ensql/sql.rb, line 85 def count adapter.fetch_count(to_sql) end
(see Adapter.fetch_each_row)
# File lib/ensql/sql.rb, line 96 def each_row(&block) adapter.fetch_each_row(to_sql, &block) nil end
(see Adapter.fetch_first_column
)
# File lib/ensql/sql.rb, line 75 def first_column adapter.fetch_first_column(to_sql) end
(see Adapter.fetch_first_field
)
# File lib/ensql/sql.rb, line 80 def first_field adapter.fetch_first_field(to_sql) end
(see Adapter.fetch_first_row
)
# File lib/ensql/sql.rb, line 70 def first_row adapter.fetch_first_row(to_sql) end
(see Adapter.fetch_rows)
# File lib/ensql/sql.rb, line 65 def rows adapter.fetch_rows(to_sql) end
(see Adapter.run)
# File lib/ensql/sql.rb, line 90 def run adapter.run(to_sql) nil end
Private Instance Methods
# File lib/ensql/sql.rb, line 158 def adapter Ensql.adapter end
# File lib/ensql/sql.rb, line 118 def interpolate(sql, params) params = params.transform_keys(&:to_s) sql .gsub(NESTED_LIST) { interpolate_nested_list params.fetch($1), $2 } .gsub(LIST) { interpolate_list params.fetch($1) } .gsub(SQL_FRAGMENT) { interpolate_sql params.fetch($1) } .gsub(LITERAL) { literalize params.fetch($1) } rescue => e raise Error, "failed interpolating `#{$1}` into #{name}: #{e}" end
# File lib/ensql/sql.rb, line 138 def interpolate_list(array) return "(NULL)" if Array(array).empty? "(" + Array(array).map { |v| literalize v }.join(", ") + ")" end
# File lib/ensql/sql.rb, line 129 def interpolate_nested_list(array, nested_sql) raise Error, "array must not be empty" if Array(array).empty? Array(array) .map { |attrs| interpolate(nested_sql, Hash(attrs)) } .map { |sql| "(#{sql})" } .join(", ") end
# File lib/ensql/sql.rb, line 144 def interpolate_sql(sql) return if sql.nil? raise "SQL fragment interpolation requires #{self.class}, got #{sql.class}" unless sql.is_a?(self.class) sql.to_sql end
# File lib/ensql/sql.rb, line 152 def literalize(value) adapter.literalize value rescue => e raise "error serialising #{value.class} into a SQL literal: #{e}" end