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. Interpolation occurs just before the SQL is executed.

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 ⇒ Integer

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

Returns:

  • (Integer) —

    the number of rows affected by the statement



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

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



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

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

#first_column ⇒ Array

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

Returns:

  • (Array) 


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

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.



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

def first_field
  adapter.fetch_first_field(to_sql)
end

#first_row ⇒ Hash

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

Returns:

  • (Hash) 


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

def first_row
  adapter.fetch_first_row(to_sql)
end

#rows ⇒ Array<Hash>

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

Returns:

  • (Array<Hash>) —

    rows as hashes keyed by column name



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

def rows
  adapter.fetch_rows(to_sql)
end

#run ⇒ void

This method returns an undefined value.

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



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

def run
  adapter.run(to_sql)
  nil
end

#to_sql ⇒ String

Interpolate the params into the SQL statement.

Returns:

  • (String) —

    a SQL string with parameters interpolated.

Raises:



105
106
107
 
# File 'lib/ensql/sql.rb', line 105

def to_sql
  interpolate(sql, params)
end