Class: PostgreSQLCursor::Cursor
- Inherits:
-
Object
- Object
- PostgreSQLCursor::Cursor
- Includes:
- Enumerable
- Defined in:
- lib/postgresql_cursor/cursor.rb
Instance Attribute Summary collapse
-
#connection ⇒ Object
readonly
Returns the value of attribute connection.
-
#count ⇒ Object
readonly
Returns the value of attribute count.
-
#options ⇒ Object
readonly
Returns the value of attribute options.
-
#result ⇒ Object
readonly
Returns the value of attribute result.
-
#sql ⇒ Object
readonly
Returns the value of attribute sql.
Instance Method Summary collapse
- #cast_types(row) ⇒ Object
-
#close ⇒ Object
Public: Closes the cursor.
- #column_types ⇒ Object
-
#each(&block) ⇒ Object
Public: Yields each row of the result set to the passed block.
- #each_array(&block) ⇒ Object
- #each_instance(klass = nil, &block) ⇒ Object
- #each_row(&block) ⇒ Object
-
#each_tuple(&block) ⇒ Object
:nodoc:.
-
#fetch(options = {}) ⇒ Object
Public: Returns the next row from the cursor, or empty hash if end of results.
-
#fetch_block(block_size = nil) ⇒ Object
Private: Fetches the next block of rows into @block.
-
#initialize(sql, options = {}) ⇒ Cursor
constructor
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.
-
#iterate_type(type = nil) ⇒ Object
Specify the type to instantiate, or reset to return a Hash.
-
#open ⇒ Object
Public: Opens (actually, “declares”) the cursor.
-
#pluck(*cols) ⇒ Object
Returns an array of columns plucked from the result rows.
-
#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.
-
#with_optional_transaction ⇒ Object
Private: Open transaction unless with_hold option, specified.
Constructor Details
#initialize(sql, options = {}) ⇒ Cursor
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
with_hold - Allows the query to remain open across commit points.
Examples
PostgreSQLCursor::Cursor.new("select ....")
Returns the cursor object when called with new.
40 41 42 43 44 45 46 |
# File 'lib/postgresql_cursor/cursor.rb', line 40 def initialize(sql, ={}) @sql = sql @options = @connection = @options.fetch(:connection) { ::ActiveRecord::Base.connection } @count = 0 @iterate = [:instances] ? :each_instance : :each_row end |
Instance Attribute Details
#connection ⇒ Object (readonly)
Returns the value of attribute connection.
23 24 25 |
# File 'lib/postgresql_cursor/cursor.rb', line 23 def connection @connection end |
#count ⇒ Object (readonly)
Returns the value of attribute count.
23 24 25 |
# File 'lib/postgresql_cursor/cursor.rb', line 23 def count @count end |
#options ⇒ Object (readonly)
Returns the value of attribute options.
23 24 25 |
# File 'lib/postgresql_cursor/cursor.rb', line 23 def @options end |
#result ⇒ Object (readonly)
Returns the value of attribute result.
23 24 25 |
# File 'lib/postgresql_cursor/cursor.rb', line 23 def result @result end |
#sql ⇒ Object (readonly)
Returns the value of attribute sql.
23 24 25 |
# File 'lib/postgresql_cursor/cursor.rb', line 23 def sql @sql end |
Instance Method Details
#cast_types(row) ⇒ Object
155 156 157 |
# File 'lib/postgresql_cursor/cursor.rb', line 155 def cast_types(row) row end |
#close ⇒ Object
Public: Closes the cursor
211 212 213 |
# File 'lib/postgresql_cursor/cursor.rb', line 211 def close @connection.execute("close cursor_#{@cursor}") end |
#column_types ⇒ Object
159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 |
# File 'lib/postgresql_cursor/cursor.rb', line 159 def column_types return nil if ::ActiveRecord::VERSION::MAJOR < 4 return @column_types if @column_types types = {} fields = @result.fields fields.each_with_index do |fname, i| ftype = @result.ftype i fmod = @result.fmod i types[fname] = @connection.get_type_map.fetch(ftype, fmod) { |oid, mod| warn "unknown OID: #{fname}(#{oid}) (#{sql})" OID::Identity.new } end @column_types = types 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
67 68 69 70 71 72 73 74 75 |
# File 'lib/postgresql_cursor/cursor.rb', line 67 def each(&block) if @iterate == :each_row self.each_row(&block) elsif @iterate == :each_array self.each_array(&block) else self.each_instance(@type, &block) end end |
#each_array(&block) ⇒ Object
84 85 86 87 88 89 90 91 92 93 94 95 |
# File 'lib/postgresql_cursor/cursor.rb', line 84 def each_array(&block) old_iterate = @iterate @iterate = :each_array begin rv = self.each_tuple do |row| block.call(row) end ensure @iterate = old_iterate end rv end |
#each_instance(klass = nil, &block) ⇒ Object
97 98 99 100 101 102 103 104 105 106 107 108 |
# File 'lib/postgresql_cursor/cursor.rb', line 97 def each_instance(klass=nil, &block) klass ||= @type self.each_tuple do |row| if ::ActiveRecord::VERSION::MAJOR < 4 model = klass.send(:instantiate,row) else @column_types ||= column_types model = klass.send(:instantiate, row, @column_types) end block.call(model) end end |
#each_row(&block) ⇒ Object
77 78 79 80 81 82 |
# File 'lib/postgresql_cursor/cursor.rb', line 77 def each_row(&block) self.each_tuple do |row| row = row.symbolize_keys if @options[:symbolize_keys] block.call(row) end end |
#each_tuple(&block) ⇒ Object
:nodoc:
131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 |
# File 'lib/postgresql_cursor/cursor.rb', line 131 def each_tuple(&block) #:nodoc: has_do_until = @options.has_key?(:until) has_do_while = @options.has_key?(:while) @count = 0 @column_types = nil with_optional_transaction do begin open while (row = fetch) do break if row.size==0 @count += 1 rc = block.call(row) break if has_do_until && rc == @options[:until] break if has_do_while && rc != @options[:while] end rescue Exception => e raise e ensure close if @block end end @count end |
#fetch(options = {}) ⇒ 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,…
190 191 192 193 194 195 196 |
# File 'lib/postgresql_cursor/cursor.rb', line 190 def fetch(={}) open unless @block fetch_block if @block.size==0 row = @block.shift row = row.symbolize_keys if row && [:symbolize_keys] row end |
#fetch_block(block_size = nil) ⇒ Object
Private: Fetches the next block of rows into @block
199 200 201 202 203 204 205 206 207 208 |
# File 'lib/postgresql_cursor/cursor.rb', line 199 def fetch_block(block_size=nil) block_size ||= @block_size ||= @options.fetch(:block_size) { 1000 } @result = @connection.execute("fetch #{block_size} from cursor_#{@cursor}") if @iterate == :each_array @block = @result.each_row.collect {|row| row } else @block = @result.collect {|row| row } end end |
#iterate_type(type = nil) ⇒ Object
Specify the type to instantiate, or reset to return a Hash
49 50 51 52 53 54 55 56 57 58 59 |
# File 'lib/postgresql_cursor/cursor.rb', line 49 def iterate_type(type=nil) if type.nil? || type == Hash @iterate = :each_row elsif type == Array @iterate = :each_array else @iterate = :each_instance @type = type end self end |
#open ⇒ Object
Public: Opens (actually, “declares”) the cursor. Call this before fetching
178 179 180 181 182 183 184 185 |
# File 'lib/postgresql_cursor/cursor.rb', line 178 def open set_cursor_tuple_fraction #@cursor = Digest::MD5.hexdigest(@sql) @cursor = SecureRandom.uuid.gsub("-","") hold = @options[:with_hold] ? 'with hold ' : '' @result = @connection.execute("declare cursor_#{@cursor} cursor #{hold}for #{@sql}") @block = [] end |
#pluck(*cols) ⇒ Object
Returns an array of columns plucked from the result rows. Experimental function, as this could still use too much memory and negate the purpose of this libarary. Should this return a lazy enumerator instead?
114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 |
# File 'lib/postgresql_cursor/cursor.rb', line 114 def pluck(*cols) = cols.last.is_a?(Hash) ? cols.pop : {} @options.merge!() @options[:symbolize_keys] = true self.iterate_type([:class]) if [:class] cols = cols.map {|c| c.to_sym } result = [] self.each() do |row| row = row.symbolize_keys if row.is_a?(Hash) result << cols.map { |c| row[c] } end result.flatten! if cols.size == 1 result 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.
228 229 230 231 232 233 234 |
# File 'lib/postgresql_cursor/cursor.rb', line 228 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 |
#with_optional_transaction ⇒ Object
Private: Open transaction unless with_hold option, specified
216 217 218 219 220 221 222 |
# File 'lib/postgresql_cursor/cursor.rb', line 216 def with_optional_transaction if @options[:with_hold] yield else @connection.transaction { yield } end end |