Module: Simple::SQL::ConnectionAdapter

Included in:
Connection
Defined in:
lib/simple/sql/connection_adapter.rb

Overview

This module implements an adapter between the Simple::SQL interface (i.e. ask, all, first, transaction) and a raw connection.

This module can be mixed onto objects that implement a raw_connection method, which must return a Pg::Connection.

Constant Summary collapse

Logging = 
::Simple::SQL::Logging

Instance Method Summary collapse

Instance Method Details

#all(sql, *args, into: nil, &block) ⇒ Object

Runs a query, with optional arguments, and returns the result. If the SQL query returns rows with one column, this method returns an array of these values. Otherwise it returns an array of arrays.

Example:

  • Simple::SQL.all("SELECT id FROM users") returns an array of id values

  • Simple::SQL.all("SELECT id, email FROM users") returns an array of

    arrays `[ <id>, <email> ]`.

Simple::SQL.all “SELECT id, email FROM users” do |id, email|

# do something

end

Raises:

  • (ArgumentError) 


33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
# File 'lib/simple/sql/connection_adapter.rb', line 33

def all(sql, *args, into: nil, &block)
  raise ArgumentError, "all no longer support blocks, use each instead." if block

  rows = []
  my_pg_source_oid = nil

  each_without_conversion(sql, *args, into: into) do |row, pg_source_oid|
    rows << row
    my_pg_source_oid = pg_source_oid
  end

  record_set = convert_rows_to_result rows, into: into, pg_source_oid: my_pg_source_oid

  # [TODO] - resolve associations. Note that this is only possible if the type
  # is not an Array (i.e. into is nil)

  if sql.is_a?(::Simple::SQL::Connection::Scope) && sql.paginated?
    record_set.send(:set_pagination_info, sql)
  end

  record_set
end

#ask(sql, *args, into: nil) ⇒ Object

Runs a query and returns the first result row of a query.

Examples:

  • Simple::SQL.ask "SELECT id FROM users WHERE email=$?", "foo@local" returns a number (or nil)

  • Simple::SQL.ask "SELECT id, email FROM users WHERE email=$?", "foo@local" returns an array [ <id>, <email> ] (or nil)



85
86
87
88
89
90
 
# File 'lib/simple/sql/connection_adapter.rb', line 85

def ask(sql, *args, into: nil)
  catch(:ok) do
    each(sql, *args, into: into) { |row| throw :ok, row }
    nil
  end
end

#costs(sql, *args) ⇒ Object

returns an Array [min_cost, max_cost] based on the database’s estimation



93
94
95
96
97
98
99
100
 
# File 'lib/simple/sql/connection_adapter.rb', line 93

def costs(sql, *args)
  explanation_first = Simple::SQL.ask "EXPLAIN #{sql}", *args
  unless explanation_first =~ /cost=(\d+(\.\d+))\.+(\d+(\.\d+))/
    raise "Cannot determine cost"
  end

  [Float($1), Float($3)]
end

#each(sql, *args, into: nil) ⇒ Object

Raises:

  • (ArgumentError) 


56
57
58
59
60
61
62
63
64
65
 
# File 'lib/simple/sql/connection_adapter.rb', line 56

def each(sql, *args, into: nil)
  raise ArgumentError, "Missing block" unless block_given?

  each_without_conversion sql, *args, into: into do |row, pg_source_oid|
    record = convert_row_to_record row, into: into, pg_source_oid: pg_source_oid
    yield record
  end

  self
end

#exec(sql) ⇒ Object

execute one or more sql statements. This method does not allow to pass in arguments - since the pg client does not support this - but it allows to run multiple sql statements separated by “;”



13
14
15
16
17
 
# File 'lib/simple/sql/connection_adapter.rb', line 13

def exec(sql)
  Logging.with_logged_query sql do
    raw_connection.exec sql
  end
end

#locked(lock_id) ⇒ Object

Executes a block, usually of db insert code, while holding an advisory lock.

Examples:

  • <tt>Simple::SQL.locked(4711) { puts ‘do work while locked’ }



108
109
110
111
112
113
 
# File 'lib/simple/sql/connection_adapter.rb', line 108

def locked(lock_id)
  ask("SELECT pg_advisory_lock(#{lock_id})")
  yield
ensure
  ask("SELECT pg_advisory_unlock(#{lock_id})")
end

#print(sql, *args, into: nil) ⇒ Object

Runs a query and prints the results via “table_print”

Raises:

  • (ArgumentError) 


68
69
70
71
72
73
74
75
 
# File 'lib/simple/sql/connection_adapter.rb', line 68

def print(sql, *args, into: nil)
  raise ArgumentError, "You cannot call Simple::SQL.print with into: #{into.inspect}" unless into.nil?

  require "table_print"
  records = all sql, *args, into: Hash
  tp records
  records
end

#resolve_type(ftype, fmod) ⇒ Object 



163
164
165
166
 
# File 'lib/simple/sql/connection_adapter.rb', line 163

def resolve_type(ftype, fmod)
  @resolved_types ||= {}
  @resolved_types[[ftype, fmod]] ||= raw_connection.exec("SELECT format_type($1,$2)", [ftype, fmod]).getvalue(0, 0)
end