Class: PostgreSQLCursor

Inherits:
Object
  • Object
show all
Includes:
Enumerable
Defined in:
lib/postgresql_cursor.rb

Overview

PostgreSQLCursor: library class provides postgresql cursor for large result set processing. Requires ActiveRecord, but can be adapted to other DBI/ORM libraries. If you don’t use AR, this assumes #connection and #instantiate methods are available.

options - Hash to control operation and loop breaks

connection: instance  - ActiveRecord connection to use
fraction: 0.1..1.0    - The cursor_tuple_fraction (default 1.0)
block_size: 1..n      - The number of rows to fetch per db block fetch
while: value          - Exits loop when block does not return this value.
until: value          - Exits loop when block returns this value.

Exmaples:

PostgreSQLCursor.new("select ...").each { |hash| ... }
ActiveRecordModel.where(...).each_row { |hash| ... }
ActiveRecordModel.each_row_by_sql("select ...") { |hash| ... }
ActiveRecordModel.each_instance_by_sql("select ...") { |model| ... }

Constant Summary collapse

@@cursor_seq = 
0

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(sql, options = {}) ⇒ PostgreSQLCursor

Public: Start a new PostgreSQL cursor query sql - The SQL statement with interpolated values options - hash of processing controls

while: value    - Exits loop when block does not return this value.
until: value    - Exits loop when block returns this value.
fraction: 0.1..1.0    - The cursor_tuple_fraction (default 1.0)
block_size: 1..n      - The number of rows to fetch per db block fetch
                        Defaults to 1000

Examples

PostgreSQLCursor.new("select ....")

Returns the cursor object when called with new.



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

def initialize(sql, options={})
  @sql        = sql
  @options    = options
  @connection = @options.fetch(:connection) { ActiveRecord::Base.connection }
  @count      = 0
end

Instance Attribute Details

#connection ⇒ Object (readonly)

Returns the value of attribute connection.



20
21
22
 
# File 'lib/postgresql_cursor.rb', line 20

def connection
  @connection
end

#count ⇒ Object (readonly)

Returns the value of attribute count.



20
21
22
 
# File 'lib/postgresql_cursor.rb', line 20

def count
  @count
end

#options ⇒ Object (readonly)

Returns the value of attribute options.



20
21
22
 
# File 'lib/postgresql_cursor.rb', line 20

def options
  @options
end

#result ⇒ Object (readonly)

Returns the value of attribute result.



20
21
22
 
# File 'lib/postgresql_cursor.rb', line 20

def result
  @result
end

#sql ⇒ Object (readonly)

Returns the value of attribute sql.



20
21
22
 
# File 'lib/postgresql_cursor.rb', line 20

def sql
  @sql
end

Instance Method Details

#close ⇒ Object

Public: Closes the cursor



100
101
102
 
# File 'lib/postgresql_cursor.rb', line 100

def close
  @connection.execute("close cursor_#{@cursor}")
end

#each(&block) ⇒ Object

Public: Yields each row of the result set to the passed block

Yields the row to the block. The row is a hash with symbolized keys.

{colname: value, ....}

Returns the count of rows processed



51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
# File 'lib/postgresql_cursor.rb', line 51

def each(&block)
  has_do_until = @options.has_key?(:until)
  has_do_while = @options.has_key?(:while)
  @count      = 0
  @connection.transaction do
    begin
      open
      while (row = fetch) do
        break if row.size==0
        @count += 1
        row = row.symbolize_keys
        rc = yield row
        # TODO: Handle exceptions raised within block
        break if has_do_until && rc == @options[:until]
        break if has_do_while && rc != @options[:while]
      end
    rescue Exception => e
      raise e
    ensure
      close
    end
  end
  @count
end

#fetch ⇒ Object

Public: Returns the next row from the cursor, or empty hash if end of results

Returns a row as a hash of ‘colname’=>value,…



87
88
89
90
 
# File 'lib/postgresql_cursor.rb', line 87

def fetch
  fetch_block if @block.size==0
  @block.shift
end

#fetch_block(block_size = nil) ⇒ Object

Private: Fetches the next block of rows into @block



93
94
95
96
97
 
# File 'lib/postgresql_cursor.rb', line 93

def  fetch_block(block_size=nil)
  block_size ||= @block_size ||= @options.fetch(:block_size) { 1000 }
  @result = @connection.execute("fetch #{block_size} from cursor_#{@cursor}")
  @block = @result.collect {|row| row } # Make our own
end

#open ⇒ Object

Public: Opens (actually, “declares”) the cursor. Call this before fetching



77
78
79
80
81
82
 
# File 'lib/postgresql_cursor.rb', line 77

def open
  set_cursor_tuple_fraction
  @cursor = @@cursor_seq += 1
  @result = @connection.execute("declare cursor_#{@cursor} cursor for #{@sql}")
  @block = []
end

#set_cursor_tuple_fraction(frac = 1.0) ⇒ Object

Private: Sets the PostgreSQL cursor_tuple_fraction value = 1.0 to assume all rows will be fetched This is a value between 0.1 and 1.0 (PostgreSQL defaults to 0.1, this library defaults to 1.0) used to determine the expected fraction (percent) of result rows returned the the caller. This value determines the access path by the query planner.



108
109
110
111
112
113
114
 
# File 'lib/postgresql_cursor.rb', line 108

def set_cursor_tuple_fraction(frac=1.0)
  @cursor_tuple_fraction ||= @options.fetch(:fraction) { 1.0 }
  return @cursor_tuple_fraction if frac == @cursor_tuple_fraction
  @cursor_tuple_fraction = frac
  @result = @connection.execute("set cursor_tuple_fraction to  #{frac}")
  frac
end