Class: PgHelper::QueryHelper

Inherits:
Object
  • Object
show all
Defined in:
lib/pg_helper/query_helper.rb

Overview

Main api class

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(params) ⇒ QueryHelper

Returns a new instance of QueryHelper.



23
24
25
26
27
28
29
30
31
 
# File 'lib/pg_helper/query_helper.rb', line 23

def initialize(params)
  if params.is_a? PGconn
    @pg_connection = params
    @connection_params = nil
  else
    @connection_params = params
    reconnect
  end
end

Instance Attribute Details

#connection_params ⇒ Hash

Returns connection params.

Returns:

  • (Hash) —

    connection params



6
7
8
 
# File 'lib/pg_helper/query_helper.rb', line 6

def connection_params
  @connection_params
end

#pg_connection ⇒ PGconn

Active database connection

Returns:

  • (PGconn) —

    connection see gem



10
11
12
 
# File 'lib/pg_helper/query_helper.rb', line 10

def pg_connection
  @pg_connection
end

Class Method Details

.using_pool(pool, &_block) ⇒ Object

Creates a new instance of the QueryHelper



13
14
15
16
17
18
19
20
21
 
# File 'lib/pg_helper/query_helper.rb', line 13

def self.using_pool(pool, &_block)
  helper = nil
  pool.with_connection do |conn|
    helper = new(conn)
    yield helper
  end
ensure
  helper = nil
end

Instance Method Details

#csv(query, params = []) ⇒ Object

may include $1, $2 etc to be replaced by query arguments

Parameters:

  • query (String) —

    SQL select

  • params (Array<String>) (defaults to: []) —

    query arguments

Returns:

  • String csv representation of query result with csv header



90
91
92
93
94
95
96
97
98
99
100
101
 
# File 'lib/pg_helper/query_helper.rb', line 90

def csv(query, params = [])
  csv_query = "COPY (#{query}) TO STDOUT with CSV HEADER"
  exec(csv_query, params) do
    csv_data = ''
    buf = @pg_connection.get_copy_data(true)
    while buf
      csv_data += buf
      buf = @pg_connection.get_copy_data(true)
    end
    csv_data
  end
end

#get_all(query, params = []) ⇒ Array<Array>

may include $1, $2 etc to be replaced by query arguments

Parameters:

  • query (String) —

    SQL select,

  • params (Array<String>) (defaults to: []) —

    query arguments

Returns:

  • (Array<Array>) —

    Array containing Array of values for each row



70
71
72
73
74
 
# File 'lib/pg_helper/query_helper.rb', line 70

def get_all(query, params = [])
  exec(query, params) do |pg_result|
    pg_result.values
  end
end

#get_all_hashes(query, params = []) ⇒ Array<Hash>

may include $1, $2 etc to be replaced by query arguments

Parameters:

  • query (String) —

    SQL select,

  • params (Array<String>) (defaults to: []) —

    query arguments

Returns:

  • (Array<Hash>) —

    Array of row hashes column_name => row_value



80
81
82
83
84
 
# File 'lib/pg_helper/query_helper.rb', line 80

def get_all_hashes(query, params = [])
  exec(query, params) do |pg_result|
    pg_result.to_a
  end
end

#get_column(query, params = []) ⇒ Array<String>

may include $1, $2 etc to be replaced by query arguments

Parameters:

  • query (String) —

    SQL select that should return one column,

  • params (Array<String>) (defaults to: []) —

    query arguments

Returns:

  • (Array<String>) —

    Values of selected column



48
49
50
51
52
53
 
# File 'lib/pg_helper/query_helper.rb', line 48

def get_column(query, params = [])
  exec(query, params) do |pg_result|
    ValidationHelper.require_single_column!(pg_result)
    pg_result.column_values(0)
  end
end

#get_hash(query, params = []) ⇒ Hash

may include $1, $2 etc to be replaced by query arguments

Parameters:

  • query (String) —

    SQL select that should return one row,

  • params (Array<String>) (defaults to: []) —

    query arguments

Returns:

  • (Hash) —

    Hash of column_name => row_value for resulting row



59
60
61
62
63
64
 
# File 'lib/pg_helper/query_helper.rb', line 59

def get_hash(query, params = [])
  exec(query, params) do |pg_result|
    ValidationHelper.require_single_row!(pg_result)
    pg_result[0]
  end
end

#modify(query, params = []) ⇒ Integer

may include $1, $2 etc to be replaced by query arguments

Parameters:

  • query (String) —

    SQL update,

  • params (Array<String>) (defaults to: []) —

    query arguments

Returns:

  • (Integer) —

    Number of rows changed



107
108
109
110
111
 
# File 'lib/pg_helper/query_helper.rb', line 107

def modify(query, params = [])
  exec(query, params) do |pg_result|
    pg_result.cmd_tuples
  end
end

#rollback! ⇒ void

This method returns an undefined value.

Aborts current transaction, or raises exception if invoked outside transaction.



123
124
125
126
 
# File 'lib/pg_helper/query_helper.rb', line 123

def rollback!
  fail PgHelperErrorInvalidOutsideTransaction if connection_idle?
  fail PgHelperErrorRollback
end

#transaction {|QueryHelper| ... } ⇒ Object

Executes content of given block inside database transaction

Yields:



115
116
117
118
 
# File 'lib/pg_helper/query_helper.rb', line 115

def transaction(&block)
  verify_transaction_possible!(&block)
  perform_transaction(&block)
end

#value(query, params = []) ⇒ String

may include $1, $2 etc to be replaced by query arguments

Parameters:

  • query (String) —

    SQL select that should return one cell,

  • params (Array<String>) (defaults to: []) —

    query arguments

Returns:

  • (String) 


37
38
39
40
41
42
 
# File 'lib/pg_helper/query_helper.rb', line 37

def value(query, params = [])
  exec(query, params) do |pg_result|
    ValidationHelper.verify_single_cell!(pg_result)
    pg_result.getvalue(0, 0)
  end
end