Class: PgHelper::QueryHelper

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

Overview

Main api class

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(params) ⇒ QueryHelper

Creates a new instance of the QueryHelper



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

def initialize(params)
  if params.kind_of? 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



26
27
28
 
# File 'lib/pg_helper/query_helper.rb', line 26

def connection_params
  @connection_params
end

#pg_connection ⇒ PGconn

Active database connection

Returns:



30
31
32
 
# File 'lib/pg_helper/query_helper.rb', line 30

def pg_connection
  @pg_connection
end

Instance Method Details

#csv(query) ⇒ Object

Returns String csv representation of query result with csv header.

Parameters:

  • query (String) —

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

  • params (Array<String>) —

    query arguments to be passed on to PostgreSql

Returns:

  • String csv representation of query result with csv header



95
96
97
98
99
100
101
102
 
# File 'lib/pg_helper/query_helper.rb', line 95

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

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

Returns Array containing Array of values for each row.

Parameters:

  • query (String) —

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

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

    query arguments to be passed on to PostgreSql

Returns:

  • (Array<Array>) —

    Array containing Array of values for each row



77
78
79
80
81
 
# File 'lib/pg_helper/query_helper.rb', line 77

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

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

Returns Array containing hash of column_name => row_value for each row.

Parameters:

  • query (String) —

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

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

    query arguments to be passed on to PostgreSql

Returns:

  • (Array<Hash>) —

    Array containing hash of column_name => row_value for each row



86
87
88
89
90
 
# File 'lib/pg_helper/query_helper.rb', line 86

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

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

Returns Values of selected column.

Parameters:

  • query (String) —

    SQL select that should return one column, may include $1, $2 etc to be replaced by arguments

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

    query arguments to be passed on to PostgreSql

Returns:

  • (Array<String>) —

    Values of selected column



57
58
59
60
61
62
 
# File 'lib/pg_helper/query_helper.rb', line 57

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

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

Returns Hash of column_name => row_value for resulting row.

Parameters:

  • query (String) —

    SQL select that should return one row, may include $1, $2 etc to be replaced by arguments

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

    query arguments to be passed on to PostgreSql

Returns:

  • (Hash) —

    Hash of column_name => row_value for resulting row



67
68
69
70
71
72
 
# File 'lib/pg_helper/query_helper.rb', line 67

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

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

Returns Number of rows changed.

Parameters:

  • query (String) —

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

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

    query arguments to be passed on to PostgreSql

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.

Raises:



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

def rollback!
  raise PgHelperErrorInvalidOutsideTransaction if connection_idle?
  raise PgHelperErrorRollback.new
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

Parameters:

  • query (String) —

    SQL select that should return one cell, may include $1, $2 etc to be replaced by arguments

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

    query arguments to be passed on to PostgreSql

Returns:

  • (String) 


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

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