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/lock.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.

Defined Under Namespace

Classes: Duplicator, Inserter, Reflection, Scope, TypeInfo

Constant Summary collapse

ConnectionManager = 
::Simple::SQL::ConnectionManager
Logging = 
::Simple::SQL::Logging

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(connection_class) ⇒ Connection

Returns a new instance of Connection.



46
47
48
 
# File 'lib/simple/sql/connection.rb', line 46

def initialize(connection_class)
  @connection_class = connection_class
end

Class Method Details

.connection_class(database_url) ⇒ Object 



29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
# File 'lib/simple/sql/connection.rb', line 29

def self.connection_class(database_url)
  if database_url.nil?
    ::ActiveRecord::Base
  elsif database_url.is_a?(String)
    ConnectionManager.connection_class(database_url)
  elsif ::ActiveRecord::Base.connected?
    # database_url is :auto, and we are connected. This happens, for example,
    # within a rails controller. IT IS IMPORTANT NOT TO CONNECT AGAINST THE
    # ::Simple::SQL::Config.determine_url! Only so we can make sure that
    # simple-sql and ActiveRecord can be mixed freely together, i.e. they are
    # sharing the same connection.
    ::ActiveRecord::Base
  else
    ConnectionManager.connection_class(::Simple::SQL::Config.determine_url)
  end
end

.create(database_url = :auto) ⇒ Object 



24
25
26
27
 
# File 'lib/simple/sql/connection.rb', line 24

def self.create(database_url = :auto)
  expect! database_url => [nil, :auto, String]
  new connection_class(database_url)
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)



83
84
85
86
87
88
89
 
# File 'lib/simple/sql/connection/base.rb', line 83

def ask(sql, *args, into: nil)
  # rubocop:disable Lint/UnreachableLoop
  catch(:ok) do
    each(sql, *args, into: into) { |row| throw :ok, row }
    nil
  end
end

#disconnect! ⇒ Object 



58
59
60
61
62
 
# File 'lib/simple/sql/connection.rb', line 58

def disconnect!
  return unless @connection_class && @connection_class != ::ActiveRecord::Base

  @connection_class.remove_connection
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

#estimate_cost(sql, *args) ⇒ Object

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



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

def estimate_cost(sql, *args)
  explanation = ask "EXPLAIN (FORMAT JSON) #{sql}", *args
  explanation.first.dig "Plan", "Total Cost"
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

#in_transaction? ⇒ Boolean

Returns true if we are inside a transaction.

Returns:

  • (Boolean) 


18
19
20
21
22
23
24
25
 
# File 'lib/simple/sql/connection/lock.rb', line 18

def in_transaction?
  # This works because each SELECT is run either inside an existing transaction
  # or inside a temporary, for this statement only, transaction which gets
  # deleted after the statement - and which results in different txids.
  txid1 = ask "select txid_current()"
  txid2 = ask "select txid_current()"
  txid1 == txid2
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



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

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

#lock!(key, key2 = nil, timeout: nil) ⇒ Object

Acquire an advisory lock.

This uses the pg_*_xact_lock locks - that once acquired, cannot be released, but will release automatically once the transaction ends. This is safer than the pg_advisory_lock group of functions.

For ease of use the key argument can also be a string - in which case we use a hash function to derive a key value. Usage example:

Simple::SQL.lock! "products", 12
Simple::SQL.ask "UPDATE products SET ... WHERE id=12"

Note that passing in a timeout value sets timeouts for all lock! invocations in this transaction.



41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 
# File 'lib/simple/sql/connection/lock.rb', line 41

def lock!(key, key2 = nil, timeout: nil)
  unless in_transaction?
    raise "You cannot use lock! outside of a transaction"
  end

  if key.is_a?(String)
    # calculate a 31 bit checksum < 0
    key = Digest::CRC32.hexdigest(key).to_i(16) # get a 32-bit stable checksum
    key &= ~0x80000000 # reset bit 31
    key = -key # make it negative
  end

  # shorten key, key2 to the allowed number of bits
  if key2
    key  = apply_bitmask(key, MASK_31_BITS)
    key2 = apply_bitmask(key2, MASK_31_BITS)
  else
    key  = apply_bitmask(key, MASK_63_BITS)
  end

  if timeout
    lock_w_timeout(key, key2, timeout)
  else
    lock_wo_timeout(key, key2)
  end
end

#locked(lock_id) ⇒ Object

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

This code is deprecated; one should use lock! instead.

Examples:

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



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

def locked(lock_id)
  lock! lock_id
  yield
end

#print(sql, *args, io: STDOUT, width: :auto) ⇒ Object

Runs a query and prints the results via “table_print”



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

def print(sql, *args, io: STDOUT, width: :auto)
  if sql.is_a?(Array) && args.empty?
    records = sql
  else
    records = all sql, *args, into: Hash
  end

  ::Simple::SQL.table_print(records, width: width, io: io)
  records
end

#raw_connection ⇒ Object 



50
51
52
 
# File 'lib/simple/sql/connection.rb', line 50

def raw_connection
  @connection_class.connection.raw_connection
end

#reflection ⇒ Object 



10
11
12
 
# File 'lib/simple/sql/connection/reflection.rb', line 10

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

#reset_reflection ⇒ Object 



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

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")



23
24
25
 
# File 'lib/simple/sql/connection/scope.rb', line 23

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

#transaction(&block) ⇒ Object 



54
55
56
 
# File 'lib/simple/sql/connection.rb', line 54

def transaction(&block)
  @connection_class.connection.transaction(&block)
end

#type_info ⇒ Object 



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

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