Class: PgConn::Connection

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

Overview

All results from the database are converted into native Ruby types

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(*args) ⇒ Connection

:call-seq:

initialize(dbname = nil, user = nil, field_name_class: Symbol)
initialize(connection_hash, field_name_class: Symbol)
initialize(connection_string, field_name_class: Symbol)
initialize(host, port, dbname, user, password, field_name_class: Symbol)
initialize(array, field_name_class: Symbol)
initialize(pg_connection_object)

Initialize a connection object and connect to the database

The possible keys of the connection hash are :host, :port, :dbname, :user, and :password. The connection string can either be a space-separated list of <key>=<value> pairs with the same keys as the hash, or a URI with the format ‘postgres://[user@][host][/name]

If given an array argument, PgConn will not connect to the database and instead write its commands to the array. In this case, methods extracting values from the database (eg. #value) will return nil or raise an exception

The last variant is used to establish a PgConn from an existing connection. It doesn’t change the connection settings and is not recommended except in cases where you want to piggyback on an existing connection (eg. a Rails connection)

The :field_name_class option controls the Ruby type of column names. It can be Symbol (the default) or String. The :timestamp option is used internally to set the timestamp for transactions

Note that the connection hash and the connection string may support more parameters than documented here. Consult www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING for the full list

TODO: Change to ‘initialize(*args, **opts)’



142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
 
# File 'lib/pg_conn.rb', line 142

def initialize(*args)
  if args.last.is_a?(Hash)
    @field_name_class = args.last.delete(:field_name_class) || Symbol
    @timestamp = args.last.delete(:timestamp)
    @timestamptz = args.last.delete(:timestamptz)
    args.pop if args.last.empty?
  else
    @field_name_class = Symbol
  end

#     else # We assume that the current user is a postgres superuser
#       @db = PgConn.new("template0")

  using_existing_connection = false
  @pg_connection =
      if args.size == 0
        make_connection
      elsif args.size == 1
        case arg = args.first
          when PG::Connection
            using_existing_connection = true
            arg
          when String
            if arg =~ /=/
              make_connection arg
            elsif arg =~ /\//
              make_connection arg
            else
              make_connection dbname: arg
            end
          when Hash
            make_connection **arg
          when Array
            @pg_commands = arg
            nil
        else
          raise Error, "Illegal argument type: #{arg.class}"
        end
      elsif args.size == 2
        make_connection dbname: args.first, user: args.last
      elsif args.size == 5
        make_connection args[0], args[1], nil, nil, args[2], args[3], args[4]
      else
        raise Error, "Illegal number of parameters: #{args.size}"
      end

  if @pg_connection && !using_existing_connection
    # Set a dummy notice processor to avoid warnings on stderr
    @pg_connection.set_notice_processor { |message| ; } # Intentionally a nop

    # Auto-convert to ruby types
    type_map = PG::BasicTypeMapForResults.new(@pg_connection)

    # Use String as default type. Kills 'Warning: no type cast defined for
    # type "uuid" with oid 2950..' warnings
    type_map.default_type_map = PG::TypeMapAllStrings.new

    # Timestamp decoder
    type_map.add_coder PG::TextDecoder::Timestamp.new( # Timestamp without time zone
        oid: 1114,
        flags: PG::Coder::TIMESTAMP_DB_UTC | PG::Coder::TIMESTAMP_APP_UTC)

    # Decode anonymous records but note that this is only useful to convert the
    # outermost structure into an array, the elements are not decoded and are
    # returned as strings. It is best to avoid anonymous records if possible
    type_map.add_coder PG::TextDecoder::Record.new(
        oid: 2249
    )

    @pg_connection.type_map_for_results = type_map
    @pg_connection.field_name_type = @field_name_class.to_s.downcase.to_sym # Use symbol field names
    @pg_connection.exec "set client_min_messages to warning;" # Silence warnings
  end

  @schema = SchemaMethods.new(self)
  @role = RoleMethods.new(self)
  @rdbms = RdbmsMethods.new(self)
  @session = SessionMethods.new(self)
  @savepoints = nil # Stack of savepoint names. Nil if no transaction in progress
end

Instance Attribute Details

#error ⇒ Object (readonly)

PG::Error object of the first failed statement in the transaction; otherwise nil. It is cleared at the beginning of a transaction so be sure to save it before you run any cleanup code that may initiate new transactions



78
79
80
 
# File 'lib/pg_conn.rb', line 78

def error
  @error
end

#field_name_class ⇒ Object (readonly)

The class of column names (Symbol or String). Default is Symbol



43
44
45
 
# File 'lib/pg_conn.rb', line 43

def field_name_class
  @field_name_class
end

#pg_connection ⇒ Object (readonly)

The PG::Connection object



40
41
42
 
# File 'lib/pg_conn.rb', line 40

def pg_connection
  @pg_connection
end

#rdbms ⇒ Object (readonly)

Database manipulation methods: #exist?, #create, #drop, #list



54
55
56
 
# File 'lib/pg_conn.rb', line 54

def rdbms
  @rdbms
end

#role ⇒ Object (readonly)

Role manipulation methods: #exist?, #create, #drop, #list



57
58
59
 
# File 'lib/pg_conn.rb', line 57

def role
  @role
end

#schema ⇒ Object (readonly)

Schema manipulation methods: #exist?, #create, #drop, #list, and #exist?/#list for relations/tables/views/columns



61
62
63
 
# File 'lib/pg_conn.rb', line 61

def schema
  @schema
end

#session ⇒ Object (readonly)

Session manipulation methods: #list, #terminate, #disable, #enable



64
65
66
 
# File 'lib/pg_conn.rb', line 64

def session
  @session
end

#timestamp ⇒ Object (readonly)

The transaction timestamp of the most recent SQL statement executed by #exec or #transaction block. The timestamp is without time zone



68
69
70
 
# File 'lib/pg_conn.rb', line 68

def timestamp
  @timestamp
end

#timestamptz ⇒ Object (readonly)

The transaction timestamp of the most recent SQL statement executed by #exec or #transaction block. The timestamp includes the current time zone



72
73
74
 
# File 'lib/pg_conn.rb', line 72

def timestamptz
  @timestamptz
end

Class Method Details

.new(*args, **opts, &block) ⇒ Object 



228
229
230
231
232
233
234
235
236
237
238
239
240
 
# File 'lib/pg_conn.rb', line 228

def self.new(*args, **opts, &block)
  if block_given?
    begin
      object = Connection.allocate
      object.send(:initialize, *args, **opts)
      yield(object) # if object.pg_connection
    ensure
      object.terminate if object.pg_connection
    end
  else
    super(*args, **opts)
  end
end

Instance Method Details

#call(name, *args, elem_type: nil, proc: false) ⇒ Object

Return the value of calling the given function (which can be a String or a Symbol and can contain the schema of the function). It dynamically detects the structure of the result and return a value or an array of values if the result contained only one column (like #value or #values), a tuple if the record has multiple columns (like #tuple), and an array of of tuples if the result contained more than one record with multiple columns (like #tuples). If the :proc option is true the “function” is assumed to be a procedure



591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
 
# File 'lib/pg_conn.rb', line 591

def call(name, *args, elem_type: nil, proc: false) # :proc may interfere with hashes
  args_seq = quote_values(args, elem_type: elem_type)
  if proc
    pg_exec "call #{name}(#{args_seq})"
    return nil
  else
    r = pg_exec "select * from #{name}(#{args_seq})"
    if r.ntuples == 0
      raise Error, "No records returned"
    elsif r.ntuples == 1
      if r.nfields == 1
        r.values[0][0]
      else
        r.values[0]
      end
    elsif r.nfields == 1
      r.column_values(0)
    else
      r&.values
    end
  end
end

#cancel_transaction ⇒ Object

Does a rollback and empties the stack. This should be called in response to PG::Error exceptions because the whole transaction stack is invalid and the server is in an invalid state

It is not an error to call #cancel_transaction when no transaction is in progress, the method always succeeds



842
843
844
845
846
847
848
849
850
 
# File 'lib/pg_conn.rb', line 842

def cancel_transaction
  begin
    pg_exec("rollback")
  rescue PG::Error
    ;
  end
  @savepoints = nil
  true
end

#commit ⇒ Object

TODO: Move to TransactionMethods



773
774
775
776
777
778
779
 
# File 'lib/pg_conn.rb', line 773

def commit()
  if transaction?
    pop_transaction(fail: false)
  else
    pg_exec("commit")
  end
end

#count(arg, where_clause = nil) ⇒ Object

:call-seq:

count(query)
count(table_name, where_clause = nil)

The number of records in the table or in the query



343
344
345
346
347
348
349
 
# File 'lib/pg_conn.rb', line 343

def count(arg, where_clause = nil)
  if arg =~ /\s/
    value("select count(*) from (#{arg}) as inner_query")
  else
    value("select count(*) from #{arg}" + (where_clause ? " where #{where_clause}" : ""))
  end
end

#database_transaction? ⇒ Boolean

True if a database transaction is in progress

Returns:

  • (Boolean) 


791
792
793
 
# File 'lib/pg_conn.rb', line 791

def database_transaction?
  pg_exec("select transaction_timestamp() != statement_timestamp()", fail: false)
end

#delete(schema = nil, table, expr) ⇒ Object

Delete record(s)



686
687
688
689
690
691
692
693
694
695
696
697
 
# File 'lib/pg_conn.rb', line 686

def delete(schema = nil, table, expr)
  table = [schema, table].compact.join(".")
  constraint =
      case expr
        when String; expr
        when Integer; "id = #{quote_value(expr)}"
        when Array; "id in (#{quote_values(expr)})"
      else
        raise ArgumentError
      end
  exec %(delete from #{table} where #{constraint})
end

#empty?(arg, where_clause = nil) ⇒ Boolean

:call-seq:

count(query)
count(table, where_clause = nil)

Return true if the table or the result of the query is empty

Returns:

  • (Boolean) 


328
329
330
331
332
333
334
335
336
 
# File 'lib/pg_conn.rb', line 328

def empty?(arg, where_clause = nil)
  if arg =~ /\s/
    value "select count(*) from (#{arg} limit 1) as inner_query"
  elsif where_clause
    value "select count(*) from (select 1 from #{arg} where #{where_clause} limit 1) as inner_query"
  else
    value "select count(*) from (select 1 from #{arg} limit 1) as inner_query"
  end == 0
end

#err ⇒ Object

Tuple of error message, lineno, and charno of the error object where each element defaults to nil if not found



85
86
87
88
89
90
91
92
 
# File 'lib/pg_conn.rb', line 85

def err
  @err ||=
    if error&.message =~ /.*?ERROR:\s*(.*?)\n(?:.*?(\s*LINE\s+(\d+): ).*?\n(?:(\s+)\^\n)?)?/
      [$1.capitalize, $3&.to_i, $4 && ($4.size - $2.size + 1)]
    else
      [nil, nil, nil]
    end
end

#errchar ⇒ Object

The one-based character number of the error in the last PG::Error or nil if absent in the Postgres error message



105
 
# File 'lib/pg_conn.rb', line 105

def errchar = err[2]

#errline ⇒ Object

The one-based line number of the last error or nil if absent in the Postgres error message



101
 
# File 'lib/pg_conn.rb', line 101

def errline = err[1]

#errmsg ⇒ Object

Last error message. The error message is the first line of the PG error message that may contain additional info. It doesn’t contain a terminating newline



97
 
# File 'lib/pg_conn.rb', line 97

def errmsg = err[0]

#error? ⇒ Boolean

True if the transaction is in a error state

Returns:

  • (Boolean) 


81
 
# File 'lib/pg_conn.rb', line 81

def error?() !@error.nil? end

#exec(sql, commit: true, fail: true, silent: false) ⇒ Object

Execute SQL statement(s) in a transaction and return the number of affected records (if any). Also sets #timestamp unless a transaction is already in progress. The sql argument can be a command (String) or an arbitrarily nested array of commands. Note that you can’t have commands that span multiple lines. The empty array is a NOP but the empty string is not.

#exec pass Postgres exceptions to the caller unless :fail is false in which case it returns nil.

Note that postgres crashes the whole transaction stack if any error is met so if you’re inside a transaction, the transaction will be in an error state and if you’re also using subtransactions the whole transaction stack has collapsed

TODO: Make sure the transaction stack is emptied on postgres errors



715
716
717
 
# File 'lib/pg_conn.rb', line 715

def exec(sql, commit: true, fail: true, silent: false)
  transaction(commit: commit) { execute(sql, fail: fail, silent: silent) }
end

#exec?(sql, commit: true, silent: true) ⇒ Boolean

Like #exec but returns true/false depending on if the command succeeded. There is not a corresponding #execute? method because any failure rolls back the whole transaction stack. TODO: Check which exceptions that should be captured

Returns:

  • (Boolean) 


723
724
725
726
727
728
729
730
 
# File 'lib/pg_conn.rb', line 723

def exec?(sql, commit: true, silent: true)
  begin
    exec(sql, commit: commit, fail: true, silent: silent)
  rescue PG::Error
    return false
  end
  return true
end

#execute(sql, fail: true, silent: false) ⇒ Object

Execute SQL statement(s) without a transaction block and return the number of affected records (if any). This used to call procedures that may manipulate transactions. The sql argument can be a SQL command or an arbitrarily nested array of commands. The empty array is a NOP but the empty string is not. #execute pass Postgres exceptions to the caller unless :fail is false in which case it returns nil

TODO: Handle postgres exceptions wrt transaction state and stack



740
741
742
743
744
745
746
747
748
749
750
751
752
 
# File 'lib/pg_conn.rb', line 740

def execute(sql, fail: true, silent: false)
  if @pg_connection
    begin
      pg_exec(sql, silent: silent)&.cmd_tuples
    rescue PG::Error
      cancel_transaction
      raise if fail
      return nil
    end
  else
    pg_exec(sql, silent: silent)
  end
end

#exist?(*args) ⇒ Boolean

:call-seq:

exist?(query)
exist?(table, id)
eists?(table, where_clause)

Return true iff the query returns exactly one record. Use ‘!empty?’ to check if the query returns one or more records

Returns:

  • (Boolean) 


312
313
314
315
316
317
318
319
320
321
 
# File 'lib/pg_conn.rb', line 312

def exist?(*args)
  arg1, arg2 = *args
  query =
      case arg2
        when Integer; "select from #{arg1} where id = #{arg2}"
        when String; "select from #{arg1} where #{arg2}"
        when NilClass; arg1
      end
  count(query) == 1
end

#field(query) ⇒ Object

Return a single-element hash from column name to value. It is an error if the query returns more than one record or more than one column. Note that you will probably prefer to use #value instead when you expect only a single field



418
419
420
421
422
423
 
# File 'lib/pg_conn.rb', line 418

def field(query)
  r = pg_exec(query)
  check_1c(r)
  check_1r(r)
  r.tuple(0).to_h
end

#field?(query) ⇒ Boolean

Like #field but returns nil if no record was found

Returns:

  • (Boolean) 


426
427
428
429
430
431
432
 
# File 'lib/pg_conn.rb', line 426

def field?(query)
  r = pg_exec(query)
  check_1c(r)
  return nil if r.ntuples == 0
  check_1r(r)
  r.tuple(0).to_h
end

#fields(query) ⇒ Object

Return an array of single-element hashes from column name to value. It is an error if the query returns records with more than one column. Note that you will probably prefer to use #values instead when you expect only single-column records



438
439
440
441
442
 
# File 'lib/pg_conn.rb', line 438

def fields(query)
  r = pg_exec(query)
  check_1c(r)
  r.each.to_a.map(&:to_h)
end

#insert(*args) ⇒ Object

:call-seq:

insert(table, record|records)
insert(table, fields, record|records|tuples)
insert(schema, table, record|records)
insert(schema, table, fields, record|records|tuples)

Insert record(s) in table and return id(s)

There is no variant that takes a single tuple because it would then be impossible to have array or hash field values



624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
 
# File 'lib/pg_conn.rb', line 624

def insert(*args)
  # Add schema (=nil) if absent
  args.unshift nil if args.size == 2 || (args.size == 3 && args[1].is_a?(Array))

  # Add fields (=nil) if absent
  args.insert(-2, nil) if !args[-2].is_a?(Array)

  # Check number of arguments
  args.size == 4 or raise ArgumentError, "Illegal number of arguments"

  # Extract parameters
  schema, table, fields, data = args

  # Normalize table
  table = schema ? "#{schema}.#{table}" : table

  # Find method and normalize data
  if data.is_a?(Array) # Array of tuples
    method = :values
    if data.empty?
      return []
    elsif data.first.is_a?(Array) # Tuple (array) element. Requires the 'fields' argument
      fields or raise ArgumentError
      tuples = data
    elsif data.first.is_a?(Hash) # Hash element
      fields ||= data.first.keys
      tuples = data.map { |record| fields.map { |field| record[field] } }
    else
      raise ArgumentError
    end
  elsif data.is_a?(Hash)
    method = :value
    fields ||= data.keys
    tuples = [fields.map { |field| data[field] }]
  else
    raise ArgumentError
  end

  # Execute SQL statement using either :value or :values depending on data arity
  self.send method, %(
    insert into #{table} (#{quote_identifiers(fields)})
      values #{quote_tuples(tuples)}
      returning id
  )
end

#is_a?(klass) ⇒ Boolean

Make PgConn::Connection pretend to be an instance of the PgConn module

Returns:

  • (Boolean) 


37
 
# File 'lib/pg_conn.rb', line 37

def is_a?(klass) klass == PgConn or super end

#map(query, key = nil, symbol: false) ⇒ Object

Returns a hash from the first field to a tuple of the remaining fields. If there is only one remaining field then that value is used instead of a tuple. The optional key argument sets the mapping field and the symbol option convert key to Symbol objects when true



541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
 
# File 'lib/pg_conn.rb', line 541

def map(query, key = nil, symbol: false) # TODO Swap arguments
  r = pg_exec(query)
  begin
    key = (key || r.fname(0)).to_s
    key_index = r.fnumber(key.to_s)
    one = (r.nfields == 2)
  rescue ArgumentError
    raise Error, "Can't find column #{key}"
  end
  h = {}
  r.each_row { |row|
    key_value = row.delete_at(key_index)
    key_value = key_value.to_sym if symbol
    !h.key?(key_value) or raise Error, "Duplicate key: #{key_value}"
    h[key_value] = (one ? row.first : row)
  }
  h
end

#multimap(query, key = nil, symbol: false) ⇒ Object

Like #map but values of duplicate keys are concatenated. It acts as a group-by on the key and array_agg on the remaining values. The value is an array of tuples if the query has more than one value field and an array of values if there is only one value field



564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
 
# File 'lib/pg_conn.rb', line 564

def multimap(query, key = nil, symbol: false)
  r = pg_exec(query)
  begin
    key = (key || r.fname(0)).to_s
    key_index = r.fnumber(key.to_s)
    one = (r.nfields == 2)
  rescue ArgumentError
    raise Error, "Can't find column #{key}"
  end
  h = {}
  r.each_row { |row|
    key_value = row.delete_at(key_index)
    key_value = key_value.to_sym if symbol
    (h[key_value] ||= []) << (one ? row.first : row)
  }
  h
end

#name ⇒ Object Also known as: database

Name of database



50
 
# File 'lib/pg_conn.rb', line 50

def name() @pg_connection.db end

#pop_transaction(commit: true, fail: true, exception: true) ⇒ Object 



818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
 
# File 'lib/pg_conn.rb', line 818

def pop_transaction(commit: true, fail: true, exception: true)
  if transaction?
    if savepoint = @savepoints.pop
      if !commit
        pg_exec("rollback to savepoint #{savepoint}")
        pg_exec("release savepoint #{savepoint}")
      else
        pg_exec("release savepoint #{savepoint}")
      end
    else
      @savepoints = nil
      pg_exec(commit ? "commit" : "rollback")
    end
  else
    fail and raise Error, "No transaction in progress"
  end
end

#push_transaction ⇒ Object 



798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
 
# File 'lib/pg_conn.rb', line 798

def push_transaction
  if transaction?
    savepoint = "savepoint_#{@savepoints.size + 1}"
    @savepoints.push savepoint
    pg_exec("savepoint #{savepoint}")
  else
    @savepoints = []
    pg_exec("begin")
    @error = @err = nil
    # FIXME This special-cases the situation where commands are logged to a
    # file instead of being executed. Maybe remove logging (or execute always
    # and log as a side-effect)
    if @pg_connection
        @timestamp, @timestamptz = @pg_connection.exec(
            'select current_timestamp, current_timestamp::timestamp without time zone'
        ).tuple_values(0)
    end
  end
end

#quote_identifier(s) ⇒ Object

Quote argument as an identifier. The argument should be a non-nil string or a symbol



244
245
246
247
 
# File 'lib/pg_conn.rb', line 244

def quote_identifier(s)
  s = s.to_s if s.is_a?(Symbol)
  @pg_connection.escape_identifier(s)
end

#quote_identifiers(idents) ⇒ Object

Quote identifiers and concatenate them using ‘,’ as separator



250
 
# File 'lib/pg_conn.rb', line 250

def quote_identifiers(idents) = idents.map { |ident| quote_identifier(ident) }.join(", ")

#quote_tuple(tuple, elem_type: nil) ⇒ Object

Quote an array of values as a tuple. Just an alias for #quote_values



298
 
# File 'lib/pg_conn.rb', line 298

def quote_tuple(tuple, elem_type: nil) = quote_values(tuple, elem_type: elem_type)

#quote_tuples(tuples, elem_type: nil) ⇒ Object

Quote an array of tuples



301
302
303
 
# File 'lib/pg_conn.rb', line 301

def quote_tuples(tuples, elem_type: nil)
  tuples.map { |tuple| "(#{quote_values(tuple, elem_type: elem_type)})" }.join(", ")
end

#quote_value(value, elem_type: nil) ⇒ Object

Quote the value as a string. Emit ‘null’ if value is nil

The value can be of any type but is converted to a string using #to_s before quoting. This works by default for the regular types Integer, true/false, Time/Date/DateTime, and arrays. Other types may require special handling

Note that a tuple value (an array) must be quoted using #quote_tuple because #quote_value would quote the tuple as an array instead of a list of values

The :elem_type option can be a postgres type name (String or Symbol) or an array of type names. They are used as the required explicit element type when the argument is an empty array. The element types shoud be in the same order as the array arguments. Nested arrays is not supported



268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
 
# File 'lib/pg_conn.rb', line 268

def quote_value(value, elem_type: nil)
  case value
    when String; @pg_connection.escape_literal(value)
    when Integer, Float; value.to_s
    when true, false; value.to_s
    when nil; 'null'
    when Date, DateTime; "'#{value}'"
    when Time; "'#{value.strftime("%FT%T%:z")}'"
    when Array
      if value.empty?
        elem_type or raise Error, "Empty array without elem_type"
        "array[]::#{elem_type}[]"
      else
        "array[#{value.map { |elem| quote_value(elem) }.join(', ')}]"
      end
  else
    @pg_connection.escape_literal(value.to_s)
  end
end

#quote_values(values, elem_type: nil) ⇒ Object

Quote values and concatenate them using ‘,’ as separator



289
290
291
292
293
294
295
 
# File 'lib/pg_conn.rb', line 289

def quote_values(values, elem_type: nil)
  elem_types = Array(elem_type)
  values.map { |value|
    elem_type = value.is_a?(Array) ? elem_types&.shift : nil
    quote_value(value, elem_type: elem_type)
  }.join(", ")
end

#record(query) ⇒ Object

Return a hash from column name (a Symbol) to field value. It is an error if the query returns more than one record. It blows up if a column name is not a valid ruby symbol



447
448
449
450
451
 
# File 'lib/pg_conn.rb', line 447

def record(query)
  r = pg_exec(query)
  check_1r(r)
  r.tuple(0).to_h
end

#record?(query) ⇒ Boolean

Like #record but returns nil if no record was found

Returns:

  • (Boolean) 


454
455
456
457
458
459
 
# File 'lib/pg_conn.rb', line 454

def record?(query)
  r = pg_exec(query)
  return nil if r.ntuples == 0
  check_1r(r)
  r.tuple(0).to_h
end

#records(query) ⇒ Object

Return an array of hashes from column name to field value



462
463
464
465
 
# File 'lib/pg_conn.rb', line 462

def records(query)
  r = pg_exec(query)
  r.each.to_a.map(&:to_h)
end

#rollback ⇒ Object

Raises:



781
 
# File 'lib/pg_conn.rb', line 781

def rollback() raise Rollback end

#set(query, key_column: :id) ⇒ Object

Return a hash from the record id column to an OpenStruct representation of the record. If the :key_column option is defined it will be used instead of id as the key. It is an error if the id field value is not unique



518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
 
# File 'lib/pg_conn.rb', line 518

def set(query, key_column: :id)
  key_column = key_column.to_sym
  keys = {}
  r = pg_exec(query)
  begin
    r.fnumber(key_column.to_s) # Check that key column exists
  rescue ArgumentError
    raise Error, "Can't find column #{key_column}"
  end
  h = {}
  for i in 0...r.ntuples
    struct = OpenStruct.new(**r[i])
    key = struct.send(key_column)
    !h.key?(key) or raise Error, "Duplicate key: #{key.inspect}"
    h[key] = struct
  end
  h
end

#struct(query) ⇒ Object

Return a record as a OpenStruct object. It is an error if the query returns more than one record. It blows up if a column name is not a valid ruby symbol



470
471
472
 
# File 'lib/pg_conn.rb', line 470

def struct(query)
  OpenStruct.new(**record(query))
end

#struct?(query) ⇒ Boolean

Like #struct but returns nil if no record was found

Returns:

  • (Boolean) 


475
476
477
478
479
 
# File 'lib/pg_conn.rb', line 475

def struct?(query)
  args = record?(query)
  return nil if args.nil?
  OpenStruct.new(**args)
end

#structs(query) ⇒ Object

Return an array of OpenStruct objects



482
483
484
 
# File 'lib/pg_conn.rb', line 482

def structs(query)
  records(query).map { |record| OpenStruct.new(**record) }
end

#su(username, &block) ⇒ Object

Switch user to the given user and execute the statement before swithcing back to the original user

FIXME: The out-commented transaction block makes postspec fail for some reason TODO: Rename ‘sudo’ because it acts just like it.

Raises:



759
760
761
762
763
764
765
766
767
768
769
 
# File 'lib/pg_conn.rb', line 759

def su(username, &block)
  raise Error, "Missing block in call to PgConn::Connection#su" if !block_given?
  realuser = self.value "select current_user"
  result = nil
#     transaction(commit: false) {
    execute "set session authorization #{username}"
    result = yield
    execute "set session authorization #{realuser}"
#     }
  result
end

#table(query, key_column: :id) ⇒ Object

Return a hash from the record id column to record (hash from column name to field value) If the :key_column option is defined it will be used instead of id as the key It is an error if the id field value is not unique



490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
 
# File 'lib/pg_conn.rb', line 490

def table(query, key_column: :id)
  [String, Symbol].include?(key_column.class) or raise "Illegal key_column"
  key_column = (field_name_class == Symbol ? key_column.to_sym : key_column.to_s)
  r = pg_exec(query)
  begin
    r.fnumber(key_column.to_s) # FIXME: What is this?
  rescue ArgumentError
    raise Error, "Can't find column #{key_column}"
  end
  h = {}
  r.each { |record|
    key = record[key_column]
    !h.key?(key) or raise Error, "Duplicate key: #{key.inspect}"
    h[record[key_column]] = record.to_h
  }
  h
end

#terminate ⇒ Object

Close the database connection. TODO: Rename ‘close’



224
225
226
 
# File 'lib/pg_conn.rb', line 224

def terminate()
  @pg_connection.close if @pg_connection && !@pg_connection.finished?
end

#transaction(commit: true, &block) ⇒ Object

Start a transaction. If called with a block, the block is executed within a transaction that is auto-committed if the commit option is true (the default). #transaction returns the result of the block or nil if no block was given

The transaction can be rolled back inside the block by raising a PgConn::Rollback exception in which case #transaction returns nil. Note that the transaction timestamp is set to the start of the first transaction even if transactions are nested



861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
 
# File 'lib/pg_conn.rb', line 861

def transaction(commit: true, &block)
  if block_given?
    result = nil
    begin
      push_transaction
      result = yield
    rescue PgConn::Rollback
      pop_transaction(commit: false, fail: false)
      return nil
    rescue PG::Error
      cancel_transaction
      @savepoints = nil
      raise
    end
    pop_transaction(commit: commit, fail: false)
    result
  else
    push_transaction
    nil
  end
end

#transaction? ⇒ Boolean

True if a transaction is in progress

Note that this requires all transactions to be started using PgConn’s transaction methods; transactions started using raw SQL are not registered

Returns:

  • (Boolean) 


788
 
# File 'lib/pg_conn.rb', line 788

def transaction?() !@savepoints.nil? end

#transactions ⇒ Object

Returns number of transaction or savepoint levels



796
 
# File 'lib/pg_conn.rb', line 796

def transactions() @savepoints ? 1 + @savepoints.size : 0 end

#tuple(query) ⇒ Object

Return an array of column values. It is an error if the query returns more than one record. If :transaction is true, the query will be executed in a transaction and be committed it :commit is true (the default). This can be used in ‘insert … returning …’ statements



393
394
395
396
397
 
# File 'lib/pg_conn.rb', line 393

def tuple(query)
  r = pg_exec(query)
  check_1r(r)
  r.values[0]
end

#tuple?(query) ⇒ Boolean

Like #tuple but returns nil if no record was found

Returns:

  • (Boolean) 


400
401
402
403
404
405
 
# File 'lib/pg_conn.rb', line 400

def tuple?(query)
  r = pg_exec(query)
  return nil if r.ntuples == 0
  check_1r(r)
  r.values[0]
end

#tuples(query) ⇒ Object

Return an array of tuples. If :transaction is true, the query will be executed in a transaction and be committed it :commit is true (the default). This can be used in ‘insert … returning …’ statements



410
411
412
 
# File 'lib/pg_conn.rb', line 410

def tuples(query)
  pg_exec(query).values
end

#update(schema = nil, table, expr, hash) ⇒ Object

Update record(s)



671
672
673
674
675
676
677
678
679
680
681
682
683
 
# File 'lib/pg_conn.rb', line 671

def update(schema = nil, table, expr, hash)
  table = [schema, table].compact.join(".")
  assignments = hash.map { |k,v| "#{k} = #{quote_value(v)}" }.join(", ")
  constraint =
      case expr
        when String; expr
        when Integer; "id = #{quote_value(expr)}"
        when Array; "id in (#{quote_values(expr)})"
      else
        raise ArgumentError
      end
  exec %(update #{table} set #{assignments} where #{constraint})
end

#user ⇒ Object Also known as: username

Name of user



46
 
# File 'lib/pg_conn.rb', line 46

def user() @pg_connection.user end

#value(query) ⇒ Object

Return a single value. It is an error if the query doesn’t return a single record with a single column. If :transaction is true, the query will be executed in a transaction and also be committed if :commit is true (this is the default). It can also be used to execute ‘insert’ statements with a ‘returning’ clause



362
363
364
365
366
367
 
# File 'lib/pg_conn.rb', line 362

def value(query) #, transaction: false, commit: true)
  r = pg_exec(query)
  check_1c(r)
  check_1r(r)
  r.values[0][0]
end

#value?(query) ⇒ Boolean

Like #value but returns nil if no record was found. It is still an error if the query returns more than one column

Returns:

  • (Boolean) 


371
372
373
374
375
376
377
 
# File 'lib/pg_conn.rb', line 371

def value?(query) #, transaction: false, commit: true)
  r = pg_exec(query)
  check_1c(r)
  return nil if r.ntuples == 0
  check_1r(r)
  r.values[0][0]
end

#values(query) ⇒ Object

Return an array of values. It is an error if the query returns records with more than one column. If :transaction is true, the query will be executed in a transaction and be committed it :commit is true (the default). This can be used in ‘insert … returning …’ statements



383
384
385
386
387
 
# File 'lib/pg_conn.rb', line 383

def values(query)
  r = pg_exec(query)
  check_1c(r)
  r.column_values(0)
end