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



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
450
 
# File 'lib/pg_conn.rb', line 339

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’



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

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



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

def error
  @error
end

#field_name_class ⇒ Object (readonly)

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



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

def field_name_class
  @field_name_class
end

#pg_connection ⇒ Object (readonly)

The PG::Connection object



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

def pg_connection
  @pg_connection
end

#rdbms ⇒ Object (readonly)

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



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

def rdbms
  @rdbms
end

#role ⇒ Object (readonly)

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



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

def role
  @role
end

#schema ⇒ Object (readonly)

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



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

def schema
  @schema
end

#session ⇒ Object (readonly)

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



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

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



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

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



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

def timestamptz
  @timestamptz
end

Class Method Details

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



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

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



839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
 
# File 'lib/pg_conn.rb', line 839

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



1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
 
# File 'lib/pg_conn.rb', line 1233

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



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

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



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

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) 


1181
1182
1183
 
# File 'lib/pg_conn.rb', line 1181

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



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

def debug() @options[:debug] end

#debug=(value) ⇒ Object 



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

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

#debug? ⇒ Boolean

Returns:

  • (Boolean) 


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

def debug?() !debug.nil? end

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

Delete record(s). See also #truncate



976
977
978
979
980
981
982
983
984
985
986
987
 
# File 'lib/pg_conn.rb', line 976

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 



1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
 
# File 'lib/pg_conn.rb', line 1294

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) 


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

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



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

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



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

def errchar = err[2]

#errline ⇒ Object

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



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

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



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

def errmsg = err[0]

#error? ⇒ Boolean

True if the transaction is in a error state

Returns:

  • (Boolean) 


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

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



1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
 
# File 'lib/pg_conn.rb', line 1022

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) 


1038
1039
1040
1041
1042
1043
1044
1045
1046
 
# File 'lib/pg_conn.rb', line 1038

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



1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
 
# File 'lib/pg_conn.rb', line 1056

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) 


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

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



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

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) 


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

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



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

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



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

def info() @options[:info] end

#info=(value) ⇒ Object 



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

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

#info? ⇒ Boolean

Returns:

  • (Boolean) 


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

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



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
945
946
947
948
949
950
951
952
953
 
# File 'lib/pg_conn.rb', line 884

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) 


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

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



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

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

#log(sql) ⇒ Object

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



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

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) 


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

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



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

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



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

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



778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
 
# File 'lib/pg_conn.rb', line 778

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



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

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



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

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



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

def notice() @options[:notice] end

#notice=(value) ⇒ Object 



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

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

#notice? ⇒ Boolean

Returns:

  • (Boolean) 


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

def notice?() !notice.nil? end

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

FIXME :exception is unused



1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
 
# File 'lib/pg_conn.rb', line 1209

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



863
864
865
 
# File 'lib/pg_conn.rb', line 863

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

#psqlexec(file, fail: true, silent: self.silent) ⇒ Object

Execute file using psql(1) so that special psql meta commands (eg. gset) can be used. Return the output from the SQL file. The file is executed with ON_ERROR_STOP on



1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
 
# File 'lib/pg_conn.rb', line 1080

def psqlexec(file, fail: true, silent: self.silent)
  begin
    output, errors, status = Open3.capture3 %(
        psql -U #{username} -d #{database} --no-psqlrc -c '\\set ON_ERROR_STOP on' -f #{file}
    )
    status.success? or raise PG::Error.new(errors)
    output
  rescue PG::Error => ex
    if @error.nil?
      @error = ex
      @err = nil
    end
    $stderr.puts errors if !silent
    raise if fail
    nil
  end
end

#push_transaction ⇒ Object 



1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
 
# File 'lib/pg_conn.rb', line 1188

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



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

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

#quote_identifiers(idents) ⇒ Object 



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

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

#quote_list(values, **opts) ⇒ Object

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



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

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



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

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



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

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 



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

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

#quote_rows(rows, **opts) ⇒ Object 



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

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

#quote_tuple(tuple, **opts) ⇒ Object 



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

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

#quote_tuples(tuples, **opts) ⇒ Object 



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

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 



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

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

#quote_values(values, **opts) ⇒ Object 



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

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)



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

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) 


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

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



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

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



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

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 



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

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



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

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



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

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, klass: OpenStruct) ⇒ 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



751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
 
# File 'lib/pg_conn.rb', line 751

def set(*query, key_column: :id, klass: OpenStruct)
  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 = klass.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 ignored, false prints it to standard error, 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



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

def silent() @options[:silent] end

#silent=(value) ⇒ Object 



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

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

#silent? ⇒ Boolean

silent == false/Proc is true

Returns:

  • (Boolean) 


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

def silent?() @producers[:silent].nil? end

#sqlexec(file, **opts) ⇒ Object

Execute SQL or pgsql file. The file must be pure SQL or pgsql statements, not psql meta commands. Any output from the file is ignored



1072
1073
1074
1075
 
# File 'lib/pg_conn.rb', line 1072

def sqlexec(file, **opts)
  source = IO.read(file) or error "Can't read #{file}"
  exec(source, **opts)
end

#struct(*query, klass: OpenStruct) ⇒ Object

Return a record as a OpenStruct object or as a :klass object if present. It is an error if the query returns more than one record and it blows up if a column name is not a valid ruby symbol. The :klass argument should be a class derived from OpenStruct. Eg.

class Person
  def name = first_name + " " + last_name
end
person = db.struct "persons", 42, klass: Person
puts person.name => "Alice Brock"



701
702
703
 
# File 'lib/pg_conn.rb', line 701

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

#struct?(*query, klass: OpenStruct) ⇒ Boolean

Like #struct but returns nil if no record was found

Returns:

  • (Boolean) 


706
707
708
709
710
 
# File 'lib/pg_conn.rb', line 706

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

#structs(*query, klass: OpenStruct) ⇒ Object

Return an array of OpenStruct objects



713
714
715
 
# File 'lib/pg_conn.rb', line 713

def structs(*query, klass: OpenStruct)
  records(parse_query *query).map { |record| klass.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:



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

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



721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
 
# File 'lib/pg_conn.rb', line 721

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’



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

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



1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
 
# File 'lib/pg_conn.rb', line 1260

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) 


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

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

#transactions ⇒ Object

Returns number of transaction or savepoint levels



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

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



994
995
996
997
998
999
1000
1001
1002
1003
1004
 
# File 'lib/pg_conn.rb', line 994

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



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

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) 


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

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



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

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

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

Update record(s)



961
962
963
964
965
966
967
968
969
970
971
972
973
 
# File 'lib/pg_conn.rb', line 961

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



956
957
958
 
# File 'lib/pg_conn.rb', line 956

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

#user ⇒ Object Also known as: username

Name of user



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

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



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

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) 


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

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



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

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



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

def warning() @options[:warning] end

#warning=(value) ⇒ Object 



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

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

#warning? ⇒ Boolean

Returns:

  • (Boolean) 


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

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



1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
 
# File 'lib/pg_conn.rb', line 1110

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