Class: Simple::SQL::Connection

Inherits:
Object
  • Object
show all
Extended by:
Forwardable
Defined in:
lib/simple/sql/connection.rb,
lib/simple/sql/connection.rb,
lib/simple/sql/connection/base.rb,
lib/simple/sql/connection/scope.rb,
lib/simple/sql/connection/insert.rb,
lib/simple/sql/connection/duplicate.rb,
lib/simple/sql/connection/type_info.rb,
lib/simple/sql/connection/reflection.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.

Direct Known Subclasses

ActiveRecordConnection, RawConnection

Defined Under Namespace

Classes: ActiveRecordConnection, Duplicator, Inserter, RawConnection, Reflection, Scope, TypeInfo

Constant Summary collapse

Logging = 
::Simple::SQL::Logging

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.create(database_url = :auto) ⇒ Object 



24
25
26
27
28
29
30
31
32
33
34
35
 
# File 'lib/simple/sql/connection.rb', line 24

def self.create(database_url = :auto)
  case database_url
  when :auto
    if defined?(::ActiveRecord)
      ActiveRecordConnection.new
    else
      RawConnection.new Simple::SQL::Config.determine_url
    end
  else
    RawConnection.new database_url
  end
end

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) 


35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
# File 'lib/simple/sql/connection/base.rb', line 35

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

  rows, pg_source_oid, column_info = each_without_conversion(sql, *args, into: into)

  result = convert_rows_to_result rows, into: into, pg_source_oid: pg_source_oid

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

  result.pagination_scope = sql if sql.is_a?(::Simple::SQL::Connection::Scope) && sql.paginated?
  result.column_info      = column_info
  result
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)



81
82
83
84
85
86
 
# File 'lib/simple/sql/connection/base.rb', line 81

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



89
90
91
92
93
94
95
96
 
# File 'lib/simple/sql/connection/base.rb', line 89

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

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

#duplicate(table, ids, overrides = {}) ⇒ Object

Creates duplicates of record in a table.

This method handles timestamp columns (these will be set to the current time) and primary keys (will be set to NULL.) You can pass in overrides as a third argument for specific columns.

Parameters:

  • ids: (Integer, Array<Integer>) primary key ids

  • overrides: Hash[column_names => SQL::Fragment]



14
15
16
17
18
19
 
# File 'lib/simple/sql/connection/duplicate.rb', line 14

def duplicate(table, ids, overrides = {})
  ids = Array(ids)
  return [] if ids.empty?

  Duplicator.new(self, table, overrides).call(ids)
end

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

Raises:

  • (ArgumentError) 


50
51
52
53
54
55
56
57
58
59
60
61
 
# File 'lib/simple/sql/connection/base.rb', line 50

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

  rows, pg_source_oid, _column_info = each_without_conversion sql, *args, into: into

  rows.each do |row|
    record = convert_rows_to_result([row], into: into, pg_source_oid: pg_source_oid).first
    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 “;”



15
16
17
18
19
 
# File 'lib/simple/sql/connection/base.rb', line 15

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

#insert(table, records, on_conflict: nil, into: nil) ⇒ Object

  • table_name - the name of the table

  • records - a single hash of attributes or an array of hashes of attributes

  • on_conflict - uses a postgres ON CONFLICT clause to ignore insert conflicts if true



11
12
13
14
15
16
17
18
19
20
21
 
# File 'lib/simple/sql/connection/insert.rb', line 11

def insert(table, records, on_conflict: nil, into: nil)
  if records.is_a?(Hash)
    inserted_records = insert(table, [records], on_conflict: on_conflict, into: into)
    return inserted_records.first
  end

  return [] if records.empty?

  inserter = inserter(table_name: table.to_s, columns: records.first.keys, on_conflict: on_conflict, into: into)
  inserter.insert(records: records)
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’ }



104
105
106
107
108
109
 
# File 'lib/simple/sql/connection/base.rb', line 104

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) 


64
65
66
67
68
69
70
71
 
# File 'lib/simple/sql/connection/base.rb', line 64

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

#reflection ⇒ Object 



6
7
8
 
# File 'lib/simple/sql/connection/reflection.rb', line 6

def reflection
  @reflection ||= Reflection.new(self)
end

#reset_reflection ⇒ Object 



2
3
4
 
# File 'lib/simple/sql/connection/reflection.rb', line 2

def reset_reflection
  @reflection = nil
end

#scope(sql, args = []) ⇒ Object

Build a scope object

This call supports a few variants:

Simple::SQL.scope("SELECT * FROM mytable")
Simple::SQL.scope(table: "mytable", select: "*")

The second option also allows one to pass in more options, like the following:

Simple::SQL.scope(table: "mytable", select: "*", where: { id: 1, foo: "bar" }, order_by: "id desc")



21
22
23
 
# File 'lib/simple/sql/connection/scope.rb', line 21

def scope(sql, args = [])
  ::Simple::SQL::Connection::Scope.new sql, args, connection: self
end

#type_info ⇒ Object 



12
13
14
 
# File 'lib/simple/sql/connection/type_info.rb', line 12

def type_info
  @type_info ||= TypeInfo.new(self)
end