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

Constant Summary collapse

DEFAULT_OPTIONS = 
{ silent: false, warning: true, notice: false, info: false, debug: false }

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(*args) ⇒ Connection

:call-seq:

initialize(dbname = nil, user = nil, **options)
initialize(connection_hash, **options)
initialize(connection_string, **options)
initialize(host, port, dbname, user, password, **options)
initialize(array, **options)
initialize(pg_connection_object, **options)

options can be :notice, :warning, :field_name_class, :timestamp, :timestamptz

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]. TODO Also allow :database and :username

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. TODO: Remove

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

The :notice and :warning options sets the default output handling for this connection (FIXME fails on copied connections). Default is to suppress notices and lower - this is diffent from postgres that by default include notices

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)’



338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
 
# File 'lib/pg_conn.rb', line 338

def initialize(*args)
  # Extract connection level options and leave other options (that should
  # be the postgres authentication hash - postgres will emit an error if
  # not)
  if args.last.is_a?(Hash)
    opts = args.last
    @field_name_class = opts.delete(:field_name_class) || Symbol

    # Extract options from arguments
    options = DEFAULT_OPTIONS.to_h { |k,v|
      r = if opts.key?(k)
        value = opts.delete(k)
        value.nil? ? v : value
      else
        v
      end
      [k, r]
    }

    # FIXME: Is this used?
    @timestamp = opts.delete(:timestamp)
    @timestamptz = opts.delete(:timestamptz)

    args.pop if opts.empty?
  else
    @field_name_class = Symbol
    options = DEFAULT_OPTIONS.dup
  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 arguments: #{args.size}"
      end

  if @pg_connection #&& !using_existing_connection
    # Set a notice processor that separates notices and warnings
    @pg_connection.set_notice_processor { |message| message_processor(message) }

    # 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. FIXME What is this? Why only Timestamp and not
    # Timestamptz?
    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
  end

  # Set options. The initial options also serves as default values and are
  # themselves initialized using DEFAULT_VALUES
  #
  # Note that options is initialized even if there is no connection to
  # avoid special casing
  @default_options = options
  @options = {}
  @producers = {} # Map from message level to Proc or nil
  set_options(@default_options) if @pg_connection

  @default_json_type = :jsonb
  @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
  @log = nil
end

Instance Attribute Details

#default_json_type ⇒ Object (readonly)

Default postgres JSON type (either ‘json’ or ‘jsonb’). Default is ‘jsonb’



151
152
153
 
# File 'lib/pg_conn.rb', line 151

def default_json_type
  @default_json_type
end

#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



266
267
268
 
# File 'lib/pg_conn.rb', line 266

def error
  @error
end

#field_name_class ⇒ Object (readonly)

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



148
149
150
 
# File 'lib/pg_conn.rb', line 148

def field_name_class
  @field_name_class
end

#pg_connection ⇒ Object (readonly)

The PG::Connection object



145
146
147
 
# File 'lib/pg_conn.rb', line 145

def pg_connection
  @pg_connection
end

#rdbms ⇒ Object (readonly)

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



162
163
164
 
# File 'lib/pg_conn.rb', line 162

def rdbms
  @rdbms
end

#role ⇒ Object (readonly)

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



165
166
167
 
# File 'lib/pg_conn.rb', line 165

def role
  @role
end

#schema ⇒ Object (readonly)

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



169
170
171
 
# File 'lib/pg_conn.rb', line 169

def schema
  @schema
end

#session ⇒ Object (readonly)

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



172
173
174
 
# File 'lib/pg_conn.rb', line 172

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 and WRONG in most cases



177
178
179
 
# File 'lib/pg_conn.rb', line 177

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



181
182
183
 
# File 'lib/pg_conn.rb', line 181

def timestamptz
  @timestamptz
end

Class Method Details

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



465
466
467
468
469
470
471
472
473
474
475
476
477
 
# File 'lib/pg_conn.rb', line 465

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, silent: self.silent, proc: false, **opts) ⇒ Object

Return the value of calling the given postgres 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).

The name argument can be a String or a Symbol that may contain the schema of the function. If the :proc option is true the “function” is assumed to be a procedure



830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
 
# File 'lib/pg_conn.rb', line 830

def call(name, *args, silent: self.silent, proc: false, **opts) # :proc may interfere with hashes
  args_seq = quote_values(args, **opts)
  if proc
    pg_exec "call #{name}(#{args_seq})", silent: silent
    return nil
  else
    r = pg_exec "select * from #{name}(#{args_seq})", silent: silent
    if r.ntuples == 0
      raise Error, "No value 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



1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
 
# File 'lib/pg_conn.rb', line 1196

def cancel_transaction
  begin
    # The transaction may be invalid to we can't use #set_option to silence
    # warnings when the transaction is rolled back. Instead we manipulate the
    # procudure method
    saved_producer = @producers[:warning]
    @producers[:warning] = nil
    pg_exec("rollback", silent: true)
  rescue PG::Error
    ;
  ensure
    @producers[:warning] = saved_producer
  end
  @savepoints = nil
  true
end

#commit ⇒ Object

The flow to global transactions is

conn.transaction # Starts a transaction
...
if ok
  conn.commit
else
  conn.rollback
end



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

def commit() pop_transaction(fail: false) end

#count(*query) ⇒ Object

:call-seq:

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

The number of records in the table or in the query



561
562
563
564
 
# File 'lib/pg_conn.rb', line 561

def count(*query)
  inner_query = parse_query *query
  value("select count(*) from (#{inner_query}) as inner_query")
end

#database_transaction? ⇒ Boolean

True if a database transaction is in progress

Returns:

  • (Boolean) 


1144
1145
1146
 
# File 'lib/pg_conn.rb', line 1144

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

#debug ⇒ Object

Controls debug messages. It can be assigned true, false, nil, or a Proc object that recieves the message. True causes the message to be printed to standard error, false ignores it, and nil resets the state to the default given when the connection was initialized. Default false



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

def debug() @options[:debug] end

#debug=(value) ⇒ Object 



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

def debug=(value) set_option(:debug, value) end

#debug? ⇒ Boolean

Returns:

  • (Boolean) 


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

def debug?() !debug.nil? end

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

Delete record(s). See also #truncate



967
968
969
970
971
972
973
974
975
976
977
978
 
# File 'lib/pg_conn.rb', line 967

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

#dump(*query) ⇒ Object 



1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
 
# File 'lib/pg_conn.rb', line 1257

def dump(*query)
  records = self.records(*query)

  if records.empty?
    puts "No records found"
  else
    headers = records.first.keys
    column_widths = headers.map(&:size)
    column_signs = [nil] * headers.size

    records.each { |r|
      r.values.each.with_index { |v, i|
        value_width = v.to_s.size
        column_widths[i] = [column_widths[i], value_width].max

        column_signs[i] ||=
          case v
            when nil; nil
            when Integer; ""
          else
            "-"
          end
      }
    }

    header_format = column_widths.map { |w,t| "%-#{w}s" }.join(" ")
    body_format = column_widths.zip(column_signs).map { |w,s| "%#{s}#{w}s" }.join(" ")

    printf "#{header_format}\n", *headers
    printf "#{header_format}\n", *column_widths.map { |w| "-" * w }
    records.each { |r| printf "#{body_format}\n", *r.values }
  end
end

#empty?(*query) ⇒ 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) 


551
552
553
554
 
# File 'lib/pg_conn.rb', line 551

def empty?(*query)
  inner_query = parse_query *query
  self.value("select count(*) from (#{inner_query} limit 1) as \"inner_query\"") == 0
end

#err ⇒ Object

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



273
274
275
276
277
278
279
280
 
# File 'lib/pg_conn.rb', line 273

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



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

def errchar = err[2]

#errline ⇒ Object

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



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

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



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

def errmsg = err[0]

#error? ⇒ Boolean

True if the transaction is in a error state

Returns:

  • (Boolean) 


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

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

#exec(sql, commit: true, fail: true, silent: self.silent) ⇒ 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 if an error occurred

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



1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
 
# File 'lib/pg_conn.rb', line 1013

def exec(sql, commit: true, fail: true, silent: self.silent)
  return nil if sql.nil? || Array[sql].empty?
  transaction(commit: commit) {
    begin
      execute(sql, fail: fail, silent: silent)
    rescue PG::Error
      cancel_transaction
      raise
    end
  }
end

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

Like #exec but returns true/false depending on if the command succeeded, error messages are suppressed by default. There is no corresponding #execute? method because any failure rolls back the whole transaction stack. TODO: Check which exceptions that should be captured

Returns:

  • (Boolean) 


1029
1030
1031
1032
1033
1034
1035
1036
1037
 
# File 'lib/pg_conn.rb', line 1029

def exec?(sql, commit: true, silent: true)
  return nil if sql.nil? || Array[sql].empty?
  begin
    exec(sql, commit: commit, fail: true, silent: silent)
  rescue PG::Error
    return false
  end
  return true
end

#execute(sql, fail: true, silent: self.silent) ⇒ 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



1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
 
# File 'lib/pg_conn.rb', line 1047

def execute(sql, fail: true, silent: self.silent)
  return nil if sql.nil? || Array[sql].empty?
  if @pg_connection
    begin
      pg_exec(sql, silent: silent)&.cmd_tuples
    rescue PG::Error
      raise if fail
      return nil
    end
  else
    pg_exec(sql, silent: silent)
  end
end

#exist?(*query) ⇒ 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

TODO: Rename #present? and use #exists? to query schema objects

Returns:

  • (Boolean) 


542
543
544
 
# File 'lib/pg_conn.rb', line 542

def exist?(*query)
  !empty?(*query)
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 query a single field



640
641
642
643
644
645
 
# File 'lib/pg_conn.rb', line 640

def field(*query)
  r = pg_exec(parse_query *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) 


648
649
650
651
652
653
654
 
# File 'lib/pg_conn.rb', line 648

def field?(*query)
  r = pg_exec(parse_query *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



660
661
662
663
664
 
# File 'lib/pg_conn.rb', line 660

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

#info ⇒ Object

Controls info messages. It can be assigned true, false, nil, or a Proc object that recieves the message. True causes the message to be printed to standard output, false ignores it, and nil resets the state to the default given when the connection was initialized. Default false. Note that #info is the only level that outputs to standard output



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

def info() @options[:info] end

#info=(value) ⇒ Object 



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

def info=(value) set_option(:info, value) end

#info? ⇒ Boolean

Returns:

  • (Boolean) 


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

def info?() !info.nil? end

#insert(*args, upsert: nil, **opts) ⇒ Object

:call-seq:

insert(table, struct|structs|record|records)
insert(table, fields, struct|structs|record|records|tuples|values)

insert(schema, table, struct|structs|record|records)
insert(schema, table, fields, struct|structs|record|records|tuples|values)

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

TODO

insert(table, [fields], field-name-map, object|objects)
field-name-map:
  { database-column-name: object-method-name } # calls method on object



875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
 
# File 'lib/pg_conn.rb', line 875

def insert(*args, upsert: nil, **opts)
  # Normalize arguments

  # Add options to args except the special :upsert option
  args << opts if !opts.empty?

  # 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 # The pg_conn method when multiple records are inserted
    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) # Record (Hash) element
      fields ||= data.first.keys
      tuples = data.map { |record| fields.map { |field| record[field] } }
    elsif data.first.is_a?(OpenStruct)
      fields ||= data.first.to_h.keys
      tuples = data.map { |struct| hash = struct.to_h; fields.map { |field| hash[field] } }
    else
      fields.size == 1 or raise ArgumentError, "Illegal number of fields, expected exactly one"
      tuples = data.map { |e| [e] }
    end
  elsif data.is_a?(Hash)
    method = upsert ? :value? : :value # The pg_conn method when only one record is inserted
    fields ||= data.keys
    tuples = [fields.map { |field| data[field] }]
  elsif data.is_a?(OpenStruct)
    method = upsert ? :value? : :value # The pg_conn method when only one record is inserted
    hash = data.to_h
    fields ||= hash.keys
    tuples = [fields.map { |field| hash[field] }]
  else
    raise ArgumentError, "Illegal argument '#{data.inspect}'"
  end

  # On-conflict clause
  upsert_sql =
      case upsert
        when true; "on conflict do nothing"
        when String; "on conlict #{upsert}"
        when false, nil; ""
      else
        raise ArgumentError, "Illegal value for :upsert option: #{upsert.inspect}"
      end

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

#is_a?(klass) ⇒ Boolean

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

Returns:

  • (Boolean) 


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

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

#literal(arg) ⇒ Object

Mark string argument as already being quoted. This is done automatically by all quote_* methods



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

def literal(arg) Literal.new(arg) end

#log(sql) ⇒ Object

Write a Sql statement to the logger if defined. Return the given sql



184
185
186
187
188
189
190
191
192
193
 
# File 'lib/pg_conn.rb', line 184

def log(sql)
  case @logger
    when nil; # do nothing
    when IO, StringIO; @logger.puts sql
    when Proc; @logger.call sql
  else
    raise ArgumentError
  end
  sql # for convenience in #pg_exec
end

#log? ⇒ Boolean

Return true if logging

Returns:

  • (Boolean) 


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

def log?() = @logger != false

#logger ⇒ Object

Return current logger or nil if not logging. Note that the logger object is equal to $stdout if the logger is set to true and nil if it is set to false



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

def logger() = @logger

#logger=(logger) ⇒ Object

Control logging of SQL commands. It can be assigned true, false, nil, an unary Proc object, or a IO or StringIO object. True causes the message to be printed to standard error, false and nil ignores it



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

def logger=(logger) @logger = (logger == true ? $stdout : logger || nil) 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

The query is a single-argument query expression (either a full SQL query string or the name of a table/view). TODO: Make key an option so we can use full query expressions



769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
 
# File 'lib/pg_conn.rb', line 769

def map(query, key = nil, symbol: false) # TODO Swap arguments
  r = pg_exec(parse_query 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.inspect}"
    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



793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
 
# File 'lib/pg_conn.rb', line 793

def multimap(query, key = nil, symbol: false)
  r = pg_exec(parse_query 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



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

def name() @pg_connection.db end

#notice ⇒ Object

Controls notice messages. It can be assigned true, false, nil, or a Proc object that recieves the message. True causes the message to be printed to standard error, false ignores it, and nil resets the state to the default given when the connection was initialized. Default false



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

def notice() @options[:notice] end

#notice=(value) ⇒ Object 



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

def notice=(value) set_option(:notice, value) end

#notice? ⇒ Boolean

Returns:

  • (Boolean) 


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

def notice?() !notice.nil? end

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

FIXME :exception is unused



1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
 
# File 'lib/pg_conn.rb', line 1172

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

#proc(name, *args, json_type: self.default_json_type, silent: self.silent) ⇒ Object

Like #call with :proc set to true



854
855
856
 
# File 'lib/pg_conn.rb', line 854

def proc(name, *args, json_type: self.default_json_type, silent: self.silent)
  call(name, *args, silent: silent, proc: true, json_type: json_type)
end

#push_transaction ⇒ Object 



1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
 
# File 'lib/pg_conn.rb', line 1151

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::timestamp without time zone, current_timestamp'
      ).tuple_values(0)
    end
  end
end

#quote_identifier(s) ⇒ Object

Connection member method variations of the PgConn quote class methods with at least a default value for :json_type



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

def quote_identifier(s) = PgConn.quote_identifier(s)

#quote_identifiers(idents) ⇒ Object 



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

def quote_identifiers(idents) = PgConn.quote_identifiers(idents)

#quote_list(values, **opts) ⇒ Object

Quote an array as a list. Eg. [“1”, 2] => (‘1’, 2)



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

def quote_list(values, **opts) = quote_tuples([values], **opts)

#quote_record(data, schema_name = nil, type, **opts) ⇒ Object

Quote a record and cast it into the given type, the type can also be a table or view. ‘data’ is an array, hash, or struct representation of the record

Note that the fields are retrived from the database so this method is not as fast as the other quote-methods. It is however very convenient when you’re testing and need a composite type because record-quoting by hand can easily become unwieldly

Also note that there is no class-method variant of this method because it requires a connection



511
512
513
 
# File 'lib/pg_conn.rb', line 511

def quote_record(data, schema_name = nil, type, **opts)
  quote_record_impl(data, schema_name, type, array: false, **opts)
end

#quote_records(data, schema_name = nil, type, **opts) ⇒ Object

Quote an array of records. The type is the record type, not the type of the enclosing array



517
518
519
 
# File 'lib/pg_conn.rb', line 517

def quote_records(data, schema_name = nil, type, **opts)
  quote_record_impl(data, schema_name, type, array: true, **opts)
end

#quote_row(row, **opts) ⇒ Object 



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

def quote_row(row, **opts) = PgConn.quote_row(row, json_type: self.default_json_type, **opts)

#quote_rows(rows, **opts) ⇒ Object 



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

def quote_rows(rows, **opts) = PgConn.quote_rows(rows, json_type: self.default_json_type, **opts)

#quote_tuple(tuple, **opts) ⇒ Object 



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

def quote_tuple(tuple, **opts) = PgConn.quote_tuple(tuple, json_type: self.default_json_type, **opts)

#quote_tuples(tuples, **opts) ⇒ Object 



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

def quote_tuples(tuples, **opts) = PgConn.quote_tuples(tuples, json_type: self.default_json_type, **opts)

#quote_value(value, **opts) ⇒ Object Also known as: quote 



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

def quote_value(value, **opts) = PgConn.quote_value(value, json_type: self.default_json_type, **opts)

#quote_values(values, **opts) ⇒ Object 



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

def quote_values(values, **opts) = PgConn.quote_values(values, json_type: self.default_json_type, **opts)

#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 (eg. contains blanks)



669
670
671
672
673
 
# File 'lib/pg_conn.rb', line 669

def record(*query)
  r = pg_exec(parse_query *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) 


676
677
678
679
680
681
 
# File 'lib/pg_conn.rb', line 676

def record?(*query)
  r = pg_exec(parse_query *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



684
685
686
687
 
# File 'lib/pg_conn.rb', line 684

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

#reset ⇒ Object

Reset connection but keep noise level (TODO: How about the other per-session settings in #initialize? Are they cleared by #reset too?)



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

def reset
  @pg_connection.reset
  self.warning = false
  self.notice = false
  @pg_connection.exec "set client_min_messages to warning;" # Silence warnings
end

#rollback ⇒ Object 



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

def rollback() pop_transaction(commit: false, fail: false) end

#search_path ⇒ Object

Return current search path. Note that the search path is part of the transaction



523
524
525
 
# File 'lib/pg_conn.rb', line 523

def search_path
  self.value("show search_path").split(/,\s*/) - %w("$user" pg_temp)
end

#search_path=(schemas) ⇒ Object

Set search path. It accepts a schema or an array of schema names



528
529
530
531
 
# File 'lib/pg_conn.rb', line 528

def search_path=(schemas)
  schema_array = Array(schemas).flatten - %w("$user" pg_temp) + %w(pg_temp)
  self.exec "set search_path to #{schema_array.join(', ')}"
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



742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
 
# File 'lib/pg_conn.rb', line 742

def set(*query, key_column: :id)
  key_column = key_column.to_sym
  keys = {}
  r = pg_exec(parse_query *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

#silent ⇒ Object

Controls error messages. It can be assigned true, false, nil, or a Proc object that recieves the message. True causes the message to be printed to standard error, false ignores it, and nil resets the state to the default given when the connection was initialized. #silent? returns true if #silent is false or a Proc object and should be used instead #silent to check the state because #silent returns truish when output is redirected to a Proc

Note that #silent=, #notice=, and warning= only controls the error message, the exception is passed through unaltered



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

def silent() @options[:silent] end

#silent=(value) ⇒ Object 



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

def silent=(value) set_option(:silent, value) end

#silent? ⇒ Boolean

silent == false/Proc is true

Returns:

  • (Boolean) 


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

def silent?() @producers[:silent].nil? 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



692
693
694
 
# File 'lib/pg_conn.rb', line 692

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

#struct?(*query) ⇒ Boolean

Like #struct but returns nil if no record was found

Returns:

  • (Boolean) 


697
698
699
700
701
 
# File 'lib/pg_conn.rb', line 697

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

#structs(*query) ⇒ Object

Return an array of OpenStruct objects



704
705
706
 
# File 'lib/pg_conn.rb', line 704

def structs(*query)
  records(parse_query *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. Note that user-switches lives within transactions

TODO: Rename ‘sudo’ because it acts just like it.

Raises:



1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
 
# File 'lib/pg_conn.rb', line 1119

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) {
  begin
    execute "set session authorization #{username}"
    result = yield
  ensure
    execute "set session authorization #{realuser}"
  end
#     }
  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



712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
 
# File 'lib/pg_conn.rb', line 712

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’



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

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). The transaction is rolled back automatically if :commit is false or if a PgConn::Rollback is raised with the block. #transaction returns the result of the block or nil if no block was given. Note that the transaction timestamp is set to the start of the first transaction even if transactions are nested

FIXME: There is some strange problem in rspec where #insert handles an exception correctly while #exec, #execute, and #transaction does not



1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
 
# File 'lib/pg_conn.rb', line 1223

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
      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) 


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

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

#transactions ⇒ Object

Returns number of transaction or savepoint levels



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

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

#truncate(*args) ⇒ Object

:call-seq:

truncate(qual_table_name...)
truncate(schema, table_name...)

Empty a table using Sql ‘truncate’. Arguments are flattened before processing



985
986
987
988
989
990
991
992
993
994
995
 
# File 'lib/pg_conn.rb', line 985

def truncate(*args)
  args = args.flatten
  if args.first =~ /\./
    tables = args
  else
    schema = args.shift
    !args.empty? or raise ArgumentError, "Table argument expected"
    tables = args.map { |table| [schema, table].compact.join('.') }
  end
  exec %(truncate #{quote_identifiers(tables)})
end

#tuple(*query) ⇒ Object

Return an array of column values. It is an error if the query returns more than one record.

TODO 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



615
616
617
618
619
 
# File 'lib/pg_conn.rb', line 615

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

#tuple?(*query) ⇒ Boolean

Like #tuple but returns nil if no record was found

Returns:

  • (Boolean) 


622
623
624
625
626
627
 
# File 'lib/pg_conn.rb', line 622

def tuple?(*query)
  r = pg_exec(parse_query *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



632
633
634
 
# File 'lib/pg_conn.rb', line 632

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

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

Update record(s)



952
953
954
955
956
957
958
959
960
961
962
963
964
 
# File 'lib/pg_conn.rb', line 952

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

#upsert(*args) ⇒ Object

Use upsert. Currently only ‘on conflict do nothing’ is supported



947
948
949
 
# File 'lib/pg_conn.rb', line 947

def upsert(*args)
  insert(*args, upsert: true)
end

#user ⇒ Object Also known as: username

Name of user



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

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.

TODO 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



580
581
582
583
584
585
 
# File 'lib/pg_conn.rb', line 580

def value(*query) #, transaction: false, commit: true)
  r = pg_exec(parse_query *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) 


589
590
591
592
593
594
595
 
# File 'lib/pg_conn.rb', line 589

def value?(*query) #, transaction: false, commit: true)
  r = pg_exec(parse_query *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.

TODO 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



603
604
605
606
607
 
# File 'lib/pg_conn.rb', line 603

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

#warning ⇒ Object

Controls warnings. It can be assigned true, false, nil, or a Proc object that recieves the message. True causes the message to be printed to standard error, false ignores it, and nil resets the state to the default given when the connection was initialized



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

def warning() @options[:warning] end

#warning=(value) ⇒ Object 



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

def warning=(value) set_option(:warning, value) end

#warning? ⇒ Boolean

Returns:

  • (Boolean) 


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

def warning?() !warning.nil? end

#with(**options, &block) ⇒ Object

Execute block with the given set of global or local options and reset them afterwards

Global options are :silent, :notice and :warning, they’re very useful in RSpec tests

Local options are :search_path that runs the block with the given schemas, :username that runs the block as the given user, :commit that runs the block in a transaction if true or false; true commits the transaction and false rolls it back (very rarely useful). It is not run in a transaction if :commit is nil. :log controls logging like #logger= but nil (the default) is a nop



1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
 
# File 'lib/pg_conn.rb', line 1073

def with(**options, &block)
  search_path = options.delete(:search_path)
  username = options.delete(:username)
  log = options.delete(:log)
  commit = options.delete(:commit)

  saved_options = @options.dup
  saved_search_path = self.search_path if search_path
  saved_logger = self.logger if log

  begin
    set_options(options)
    self.search_path = search_path if search_path
    self.logger = log if !log.nil?

    inner = lambda {
      if !commit.nil?
        self.transaction(commit: commit) {
          block.yield
        }
      else
        block.yield
      end
    }

    if username
      self.su(username, &inner)
    else
      inner.call
    end
  ensure
    self.logger = saved_logger if log
    self.search_path = saved_search_path if search_path
    set_options(saved_options)
  end
end