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 = pairs with the same keys as the hash, or a URI with the format 'postgres://[user[:password]@][host][:port][/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 https://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 



469
470
471
472
473
474
475
476
477
478
479
480
481
 
# File 'lib/pg_conn.rb', line 469

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



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

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



1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
 
# File 'lib/pg_conn.rb', line 1245

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


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

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



565
566
567
568
 
# File 'lib/pg_conn.rb', line 565

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) 


1193
1194
1195
 
# File 'lib/pg_conn.rb', line 1193

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



990
991
992
993
994
995
996
997
998
999
1000
1001
 
# File 'lib/pg_conn.rb', line 990

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

FIXME: Columns with the same name are collapsed



1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
 
# File 'lib/pg_conn.rb', line 1307

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) 


555
556
557
558
 
# File 'lib/pg_conn.rb', line 555

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 selected or affected records in the last statement. 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. 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



1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
 
# File 'lib/pg_conn.rb', line 1034

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) 


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

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



1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
 
# File 'lib/pg_conn.rb', line 1068

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) 


546
547
548
 
# File 'lib/pg_conn.rb', line 546

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



644
645
646
647
648
649
 
# File 'lib/pg_conn.rb', line 644

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) 


652
653
654
655
656
657
658
 
# File 'lib/pg_conn.rb', line 652

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



664
665
666
667
668
 
# File 'lib/pg_conn.rb', line 664

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



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
954
955
 
# File 'lib/pg_conn.rb', line 887

def insert(*args, upsert: nil, **opts)
  # Add options to args except the special :conflict 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
  identifiers_sql = quote_identifiers(fields)
  values_sql = quote_tuples(tuples)
  upsert_sql = ""

  # On upsert
  if upsert
    other_identifiers_sql = quote_identifiers(fields.map { "excluded.#{_1}" })
    upsert_sql = %(
      on conflict (#{quote_identifiers(upsert)}) do update
        set (#{identifiers_sql}) = (#{other_identifiers_sql}))
  end

  # Execute SQL statement using either :value or :values depending on data arity
  self.send method, <<~SQL
    insert into #{table} (#{identifiers_sql})
      values #{values_sql}
      #{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



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

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



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

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



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

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



1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
 
# File 'lib/pg_conn.rb', line 1221

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



866
867
868
 
# File 'lib/pg_conn.rb', line 866

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



1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
 
# File 'lib/pg_conn.rb', line 1092

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 



1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
 
# File 'lib/pg_conn.rb', line 1200

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



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

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

#quote_identifiers(idents) ⇒ Object 



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

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

#quote_list(values, **opts) ⇒ Object

Quote an array as a list. Eg. ["1", 2] => ('1', 2)



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

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



515
516
517
 
# File 'lib/pg_conn.rb', line 515

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



521
522
523
 
# File 'lib/pg_conn.rb', line 521

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 



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

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

#quote_rows(rows, **opts) ⇒ Object 



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

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

#quote_tuple(tuple, **opts) ⇒ Object 



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

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

#quote_tuples(tuples, **opts) ⇒ Object 



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

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 



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

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

#quote_values(values, **opts) ⇒ Object 



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

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)



673
674
675
676
677
 
# File 'lib/pg_conn.rb', line 673

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) 


680
681
682
683
684
685
 
# File 'lib/pg_conn.rb', line 680

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



688
689
690
691
 
# File 'lib/pg_conn.rb', line 688

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 



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

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



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

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



532
533
534
535
 
# File 'lib/pg_conn.rb', line 532

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



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

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



1084
1085
1086
1087
 
# File 'lib/pg_conn.rb', line 1084

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"


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

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) 


709
710
711
712
713
 
# File 'lib/pg_conn.rb', line 709

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



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

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:



1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
 
# File 'lib/pg_conn.rb', line 1168

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



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

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

#terminated? ⇒ Boolean

TODO: Rename #closed?

Returns:

  • (Boolean) 


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

def terminated?() = @pg_connection&.finished?

#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



1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
 
# File 'lib/pg_conn.rb', line 1272

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) 


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

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

#transactions ⇒ Object

Returns number of transaction or savepoint levels



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

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



1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
 
# File 'lib/pg_conn.rb', line 1008

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



619
620
621
622
623
 
# File 'lib/pg_conn.rb', line 619

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) 


626
627
628
629
630
631
 
# File 'lib/pg_conn.rb', line 626

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



636
637
638
 
# File 'lib/pg_conn.rb', line 636

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

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

Update record(s)



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

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 instead of insert and resolve conflicts by updating the target record. Called like #insert but with an additional key argument that is an array key to check for conflicts, default [:id]

:call-seq:

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

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


968
969
970
971
972
 
# File 'lib/pg_conn.rb', line 968

def upsert(*args)
  key = args.pop
  key.is_a?(Array) && !key.empty? or raise ArgumentError, "Illegal value for key: #{key.inspect}"
  insert(*args, upsert: key)
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



584
585
586
587
588
589
 
# File 'lib/pg_conn.rb', line 584

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) 


593
594
595
596
597
598
599
 
# File 'lib/pg_conn.rb', line 593

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



607
608
609
610
611
 
# File 'lib/pg_conn.rb', line 607

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



1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
 
# File 'lib/pg_conn.rb', line 1122

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