Class: Ensql::SQL

Inherits:
Object
  • Object
show all
Defined in:
lib/ensql/sql.rb

Overview

Encapsulates a plain-text SQL statement and optional parameters to interpolate. Interpolation is indicated by one of the four placeholder formats:

  1. 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.

  2. 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.

  3. Nested List: %{param(nested sql)}

    • Takes an array of parameter hashes and interpolates the nested SQL for each Hash in the Array.
    • Raises an error if param is nil or a non-hash array.
    • Primary useful for SQL VALUES () clauses.

  4. SQL Fragment: %{!sql_param}

    • Interpolates the parameter without quoting, as a SQL fragment.
    • The parameter must be an SQL object or this will raise an error.
    • nil will not be interpolated.
    • Allows composition of SQL via subqueries.

Any placeholders in the SQL must be present in the params hash or a KeyError will be raised during interpolation.

Examples:

# 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

Instance Method Summary collapse

Instance Method Details

#count ⇒ Object

Execute the statement and return the number of rows affected. Typically used for DELETE, UPDATE, INSERT, but will work with SELECT on some databases.



84
85
86
 
# File 'lib/ensql/sql.rb', line 84

def count
  adapter.fetch_count(to_sql)
end

#each_row {|Hash| ... } ⇒ Object

Execute the query and yield each resulting row. This should provide a more efficient method of iterating through large datasets.

Yields:

  • (Hash) —

    row



95
96
97
 
# File 'lib/ensql/sql.rb', line 95

def each_row(&block)
  adapter.fetch_each_row(to_sql, &block)
end

#first_column ⇒ Object

Execute the query and return only the first column of the result.



74
75
76
 
# File 'lib/ensql/sql.rb', line 74

def first_column
  adapter.fetch_first_column(to_sql)
end

#first_field ⇒ Object

Execute the query and return only the first field of the first row of the result.



79
80
81
 
# File 'lib/ensql/sql.rb', line 79

def first_field
  adapter.fetch_first_field(to_sql)
end

#first_row ⇒ Object

Execute the query and return only the first row of the result.



69
70
71
 
# File 'lib/ensql/sql.rb', line 69

def first_row
  adapter.fetch_first_row(to_sql)
end

#rows ⇒ Object

Execute the query and return an array of rows represented by { column => field } hashes. Fields should be deserialised depending on the column type.



64
65
66
 
# File 'lib/ensql/sql.rb', line 64

def rows
  adapter.fetch_rows(to_sql)
end

#run ⇒ Object

Execute the statement on the database without returning any result. This can avoid the overhead of other fetch_* methods.



89
90
91
92
 
# File 'lib/ensql/sql.rb', line 89

def run
  adapter.run(to_sql)
  nil
end

#to_sql ⇒ String

Interpolate the params into the SQL statement.

Raises:

  • if any param is missing or invalid.



103
104
105
 
# File 'lib/ensql/sql.rb', line 103

def to_sql
  interpolate(sql, params)
end