require "pg"
require 'ostruct'

require_relative "pg_conn/version"
require_relative "pg_conn/role_methods"
require_relative "pg_conn/schema_methods"
require_relative "pg_conn/rdbms_methods"
require_relative "pg_conn/session_methods"

module PgConn
  class Error < StandardError; end

  # Can be raised in #transaction blocks to rollback changes
  class Rollback < Error; end

  # Return a PgConn::Connection object. TODO: A block argument
  def self.new(*args, &block) Connection.new(*args, &block) end

  # Make the PgConn module pretend it has PgConn instances
  def self.===(element) element.is_a?(PgConn::Connection) or super end

  # Returns a PgConn::Connection object (aka. a PgConn object). It's arguments
  # can be an existing connection that will just be returned or a set of
  # PgConn::Connection#initialize arguments that will be used to create a new
  # PgConn::Connection object
  def self.ensure(*args)
    if args.size == 1 && args.first.is_a?(PgConn::Connection)
      args.first
    else
      PgConn::Connection.new(*args)
    end
  end

  # All results from the database are converted into native Ruby types
  class Connection
    # Make PgConn::Connection pretend to be an instance of the PgConn module
    def is_a?(klass) klass == PgConn or super end

    # The PG::Connection object
    attr_reader :pg_connection

    # The class of column names (Symbol or String). Default is Symbol
    attr_reader :field_name_class

    # Name of user
    def user() @pg_connection.user end
    alias_method :username, :user # Obsolete FIXME Is it?

    # Name of database
    def name() @pg_connection.db end
    alias_method :database, :name # Obsolete FIXME Is it?

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

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

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

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

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

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

    # 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
    attr_reader :error

    # True if the transaction is in a error state
    def error?() !@error.nil? end

    # Tuple of error message, lineno, and charno of the error object where each
    # element defaults to nil if not found
    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

    # 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
    def errmsg = err[0]

    # The one-based line number of the last error or nil if absent in the
    # Postgres error message
    def errline = err[1]

    # The one-based character number of the error in the last PG::Error or nil
    # if absent in the Postgres error message
    def errchar = err[2]

    # :call-seq:
    #     initialize(dbname = nil, user = nil, field_name_class: Symbol)
    #     initialize(connection_hash, field_name_class: Symbol)
    #     initialize(connection_string, field_name_class: Symbol)
    #     initialize(host, port, dbname, user, password, field_name_class: Symbol)
    #     initialize(array, field_name_class: Symbol)
    #     initialize(pg_connection_object)
    #
    # Initialize a connection object and connect to the database
    #
    # The possible keys of the connection hash are :host, :port, :dbname, :user,
    # and :password. The connection string can either be a space-separated list
    # of <key>=<value> pairs with the same keys as the hash, or a URI with the
    # format 'postgres[ql]://[user[:password]@][host][:port][/name]
    #
    # If given an array argument, PgConn will not connect to the database and
    # instead write its commands to the array. In this case, methods extracting
    # values from the database (eg. #value) will return nil or raise an
    # exception
    #
    # The last variant is used to establish a PgConn from an existing
    # connection. It doesn't change the connection settings and is not
    # recommended except in cases where you want to piggyback on an existing
    # connection (eg. a Rails connection)
    #
    # The :field_name_class option controls the Ruby type of column names. It can be
    # Symbol (the default) or String. The :timestamp option is used
    # internally to set the timestamp for transactions
    #
    # Note that the connection hash and the connection string may support more
    # parameters than documented here. Consult
    # https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
    # for the full list
    #
    # TODO: Change to 'initialize(*args, **opts)'
    def initialize(*args)
      if args.last.is_a?(Hash)
        @field_name_class = args.last.delete(:field_name_class) || Symbol
        @timestamp = args.last.delete(:timestamp)
        @timestamptz = args.last.delete(:timestamptz)
        args.pop if args.last.empty?
      else
        @field_name_class = Symbol
      end

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

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

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

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

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

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

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

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

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

    # Close the database connection. TODO: Rename 'close'
    def terminate()
      @pg_connection.close if @pg_connection && !@pg_connection.finished? 
    end

    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

    # Quote value as an identifier. Value should be a non-nil string or a symbol
    def quote_identifier(s)
      s = s.to_s if s.is_a?(Symbol)
      @pg_connection.escape_identifier(s)
    end

    # Quote the value as a string. Emit 'null' if value is nil
    #
    # The value can be of any type but is converted to a string using #to_s
    # before quoting. This works by default for the regular types Integer,
    # true/false, Time/Date/DateTime, and arrays. Other types may require
    # special handling
    def quote_value(value) 
      case value
        when String; @pg_connection.escape_literal(value)
        when Integer, Float; value.to_s
        when true, false; value.to_s
        when nil; 'null'
        when Date, DateTime; "'#{value}'"
        when Time; "'#{value.strftime("%FT%T%:z")}'"
        when Array; "array[#{value.map { |elem| quote_value(elem) }.join(', ')}]"
      else
        @pg_connection.escape_literal(value.to_s)
      end
    end

    # Quote array as a comma-separated sequence of identifiers
    def quote_identifier_seq(identifiers) = identifiers.map { quote_identifier(_1) }.join(', ')

    # Quote array as a parenthesis-enclosed sequence of identifiers. TODO: Rename quote_identifier_tuple
    def quote_identifier_list(identifiers) = "(#{quote_identifier_seq(identifiers)})"

    # Quote array as a bracket-enclosed sequence of identifiers
#   def quote_identifier_array(identifiers) = "(#{quote_identifier_seq(identifiers)})"

    # Quote array as a curly-bracket-enclosed sequence of identifiers
#   def quote_identifier_hash(identifiers) = "(#{quote_identifier_seq(identifiers)})"

    # Quote array as a comma-separated sequence of quoted values
    def quote_value_seq(values) = values.map { quote_literal(_1) }.join(', ')

    # Quote array as a parenthesis-enclosed list of quoted values. TODO: Rename to quote_value_tuple
    def quote_value_list(values) = "(#{quote_value_seq(values)})"

    # Quote array as a bracket-enclosed sequence of values
#   def quote_identifier_array(values) = "(#{quote_identifier_seq(values)})"

    # Quote array as a curly-bracket-enclosed sequence of values
#   def quote_identifier_hash(values) = "(#{quote_identifier_seq(values)})"

    # Old aliases. TODO Remove
    def quote_literal(value) = quote_value(value)
    def quote_literal_list(values) = quote_value_list(values)

    # :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
    def exist?(*args)
      arg1, arg2 = *args
      query = 
          case arg2
            when Integer; "select from #{arg1} where id = #{arg2}"
            when String; "select from #{arg1} where #{arg2}"
            when NilClass; arg1
          end
      count(query) == 1
    end

    # :call-seq:
    #   count(query)
    #   count(table, where_clause = nil)
    #
    # Return true if the table or the result of the query is empty
    def empty?(arg, where_clause = nil) 
      if arg =~ /\s/
        value "select count(*) from (#{arg} limit 1) as inner_query"
      elsif where_clause
        value "select count(*) from (select 1 from #{arg} where #{where_clause} limit 1) as inner_query"
      else
        value "select count(*) from (select 1 from #{arg} limit 1) as inner_query"
      end == 0
    end

    # :call-seq:
    #   count(query)
    #   count(table_name, where_clause = nil)
    #
    # The number of records in the table or in the query
    def count(arg, where_clause = nil)
      if arg =~ /\s/
        value("select count(*) from (#{arg}) as inner_query")
      else
        value("select count(*) from #{arg}" + (where_clause ? " where #{where_clause}" : ""))
      end
    end

    # TODO
    #   Query variants
    #     (sql) - simple SQL statement
    #     (schema = nil, table, id-or-where-clause = nil, field-or-fields)
    #

    # Return a single value. It is an error if the query doesn't return a
    # single record with a single column. If :transaction is true, the query
    # will be executed in a transaction and also be committed if :commit is
    # true (this is the default). It can also be used to execute 'insert'
    # statements with a 'returning' clause
    def value(query) #, transaction: false, commit: true)
      r = pg_exec(query)
      check_1c(r)
      check_1r(r)
      r.values[0][0]
    end

    # Like #value but returns nil if no record was found. It is still an error
    # if the query returns more than one column
    def value?(query) #, transaction: false, commit: true)
      r = pg_exec(query)
      check_1c(r)
      return nil if r.ntuples == 0
      check_1r(r)
      r.values[0][0]
    end

    # Return an array of values. It is an error if the query returns records
    # with more than one column. If :transaction is true, the query will be
    # executed in a transaction and be committed it :commit is true (the
    # default). This can be used in 'insert ... returning ...' statements
    def values(query)
      r = pg_exec(query)
      check_1c(r)
      r.column_values(0)
    end

    # Return an array of column values. It is an error if the query returns
    # more than one record. If :transaction is true, the query will be executed
    # in a transaction and be committed it :commit is true (the default). This
    # can be used in 'insert ... returning ...' statements
    def tuple(query)
      r = pg_exec(query)
      check_1r(r)
      r.values[0]
    end

    # Like #tuple but returns nil if no record was found
    def tuple?(query)
      r = pg_exec(query)
      return nil if r.ntuples == 0
      check_1r(r)
      r.values[0]
    end

    # 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
    def tuples(query)
      pg_exec(query).values
    end

    # Return a single-element hash from column name to value. It is an error
    # if the query returns more than one record or more than one column. Note
    # that you will probably prefer to use #value instead when you expect only
    # a single field
    def field(query)
      r = pg_exec(query)
      check_1c(r)
      check_1r(r)
      r.tuple(0).to_h
    end

    # Like #field but returns nil if no record was found
    def field?(query)
      r = pg_exec(query)
      check_1c(r)
      return nil if r.ntuples == 0
      check_1r(r)
      r.tuple(0).to_h
    end

    # 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
    def fields(query)
      r = pg_exec(query)
      check_1c(r)
      r.each.to_a.map(&:to_h)
    end

    # 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
    def record(query)
      r = pg_exec(query)
      check_1r(r)
      r.tuple(0).to_h
    end

    # Like #record but returns nil if no record was found
    def record?(query)
      r = pg_exec(query)
      return nil if r.ntuples == 0
      check_1r(r)
      r.tuple(0).to_h
    end

    # Return an array of hashes from column name to field value
    def records(query)
      r = pg_exec(query)
      r.each.to_a.map(&:to_h)
    end

    # Return a record as a OpenStruct object. It is an error if the query
    # returns more than one record. It blows up if a column name is not a valid
    # ruby symbol
    def struct(query)
      OpenStruct.new(**record(query))
    end

    # Like #struct but returns nil if no record was found
    def struct?(query)
      args = record?(query)
      return nil if args.nil?
      OpenStruct.new(**args)
    end

    # Return an array of OpenStruct objects
    def structs(query)
      records(query).map { |record| OpenStruct.new(**record) }
    end

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

    # TODO: #group - same as table but partitions a table on the given keys
    #       returning a map from key to array of records

    # TODO: An #array method that returns a map from id to tuple. Hmm... almost
    #       the same as #map

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

    # 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 of that value. The optional +key+ argument sets the mapping field
    def map(query, key = nil) # TODO Swap arguments
      r = pg_exec(query)
      begin
        key = (key || r.fname(0)).to_s
        key_index = r.fnumber(key.to_s)
        one = (r.nfields == 2)
      rescue ArgumentError
        raise Error, "Can't find column #{key}"
      end
      h = {}
      r.each_row { |row|
        key_value = row.delete_at(key_index)
        !h.key?(key_value) or raise Error, "Duplicate key: #{key_value}"
        h[key_value] = (one ? row.first : row)
      }
      h
    end

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

    # Return the value of calling the given function (which can be a String or
    # a Symbol and can contain the schema of the function). It dynamically
    # detects the structure of the result and return a value or an array of
    # values if the result contained only one column (like #value or #values),
    # a tuple if the record has multiple columns (like #tuple), and an array of
    # of tuples if the result contained more than one record with multiple
    # columns (like #tuples)
    #
    def call(name, *args, proc: false) # :proc may interfere with hashes
      args_sql = args.map { |arg| # TODO: Use pg's encoder
        case arg
          when NilClass; "null"
          when String; "'#{arg}'"
          when Integer; arg
          when TrueClass, FalseClass; arg
          when Array; "Array['#{arg.join("', '")}']" # Quick and dirty # FIXME
          when Hash; raise NotImplementedError
        else
          raise ArgumentError, "Unrecognized value: #{arg.inspect}"
        end
      }.join(", ")
      if proc
        pg_exec "call #{name}(#{args_sql})"
        return nil
      else
        r = pg_exec "select * from #{name}(#{args_sql})"
        if r.ntuples == 0
          raise Error, "No records returned"
        elsif r.ntuples == 1
          if r.nfields == 1
            r.values[0][0]
          else
            r.values[0]
          end
        elsif r.nfields == 1
          r.column_values(0)
        else
          r&.values
        end
      end
    end

    # :call-seq*
    #   insert(table, record|records)
    #   insert(table, fields, record|records|tuples)
    #   insert(schema, table, record|records)
    #   insert(schema, table, fields, record|records|tuples)
    #
    # Insert record(s) in table and return id(s)
    #
    # There is no variant that takes a single tuple because it would then be
    # impossible to have array or hash field values
    def insert(*args)
      # Add schema (=nil) if absent
      args.unshift nil if args.size == 2 || (args.size == 3 && args[1].is_a?(Array))

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

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

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

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

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

      # Build and execute SQL statement
      columns = quote_identifier_list(fields)
      values = tuples.map { |tuple| quote_literal_list(tuple) }.join(', ')
      self.send method, %(insert into #{table} #{columns} values #{values} returning id)
    end

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

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

    # Execute SQL statement(s) in a transaction and return the number of
    # affected records (if any). Also sets #timestamp unless a transaction is
    # already in progress. The +sql+ argument can be a command (String) or an
    # arbitrarily nested array of commands. Note that you can't have commands
    # that span multiple lines. The empty array is a NOP but the empty string
    # is not.
    #
    # #exec pass Postgres exceptions to the caller unless :fail is false in which case
    # it returns nil. 
    #
    # Note that postgres crashes the whole transaction stack if any error is
    # met so if you're inside a transaction, the transaction will be in an
    # error state and if you're also using subtransactions the whole
    # transaction stack has collapsed
    #
    # TODO: Make sure the transaction stack is emptied on postgres errors
    def exec(sql, commit: true, fail: true, silent: false)
      transaction(commit: commit) { execute(sql, fail: fail, silent: silent) }
    end

    # Like #exec but returns true/false depending on if the command succeeded.
    # There is not a corresponding #execute? method because any failure rolls
    # back the whole transaction stack. TODO: Check which exceptions that
    # should be captured
    def exec?(sql, commit: true, silent: true) 
      begin
        exec(sql, commit: commit, fail: true, silent: silent)
      rescue PG::Error
        return false
      end
      return true
    end

    # Execute SQL 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
    def execute(sql, fail: true, silent: false)
      if @pg_connection
        begin
          pg_exec(sql, silent: silent)&.cmd_tuples
        rescue PG::Error
          cancel_transaction
          raise if fail
          return nil
        end
      else
        pg_exec(sql, silent: silent)
      end
    end

    # Switch user to the given user and execute the statement before swithcing
    # back to the original user
    #
    # FIXME: The out-commented transaction block makes postspec fail for some reason
    # TODO: Rename 'sudo' because it acts just like it. 
    def su(username, &block)
      raise Error, "Missing block in call to PgConn::Connection#su" if !block_given?
      realuser = self.value "select current_user"
      result = nil
#     transaction(commit: false) {
        execute "set session authorization #{username}"
        result = yield
        execute "set session authorization #{realuser}"
#     }
      result
    end

    # TODO: Move to TransactionMethods

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

    def rollback() raise Rollback end

    # 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
    def transaction?() !@savepoints.nil? end

    # True if a database transaction is in progress
    def database_transaction?
      pg_exec("select transaction_timestamp() != statement_timestamp()", fail: false)
    end

    # Returns number of transaction or savepoint levels
    def transactions() @savepoints ? 1 + @savepoints.size : 0 end

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

    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

    # 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
    def cancel_transaction
      begin
        pg_exec("rollback")
      rescue PG::Error
        ;
      end
      @savepoints = nil
      true
    end

    # Start a transaction. If called with a block, the block is executed within
    # a transaction that is auto-committed if the commit option is true (the
    # default). #transaction returns the result of the block or nil if no block
    # was given
    #
    # The transaction can be rolled back inside the block by raising a
    # PgConn::Rollback exception in which case #transaction returns nil. Note
    # that the transaction timestamp is set to the start of the first
    # transaction even if transactions are nested
    def transaction(commit: true, &block)
      if block_given?
        result = nil
        begin
          push_transaction
          result = yield
        rescue PgConn::Rollback
          pop_transaction(commit: false, fail: false)
          return nil
        rescue PG::Error
          cancel_transaction
          @savepoints = nil
          raise
        end
        pop_transaction(commit: commit, fail: false)
        result
      else
        push_transaction
        nil
      end
    end

  private
    # Wrapper around PG::Connection.new that switches to the postgres user
    # before connecting if the current user is the root user
    def make_connection(*args, **opts)
      if Process.euid == 0
        begin
          postgres_uid = Process::UID.from_name "postgres"
        rescue ArgumentError
          raise Error, "Can't find 'postgres' user"
        end
        begin
          postgres_gid = Process::GID.from_name "postgres"
        rescue ArgumentError
          raise Error, "Can't find 'postgres' group"
        end

        begin
          Process::Sys.seteuid postgres_uid
          Process::Sys.setegid postgres_gid
          PG::Connection.new *args, **opts
        ensure
          Process::Sys.seteuid 0
          Process::Sys.setguid 0
        end
      else
        PG::Connection.new *args, **opts
      end
    end

    # :call-seq:
    #   pg_exec(string)
    #   pg_exec(array)
    #
    # Execute statement(s) on the server. If the argument is an array of
    # commands, the commands are concatenated with ';' before being sent to the
    # server. #pg_exec returns a PG::Result object or nil if +arg+ was empty
    #
    # Postgres errors are passed through and #error and #err set to the last
    # statement's SQL errors or nil if it succeeded
    #
    # FIXME: Error message prints the last statement but what if another
    # statement failed?
    #
    # TODO: Connsider executing statements one-by-one so we're able to
    # pin-point Postgres errors without a line number. This may be expensive,
    # though
    #
    # TODO: Fix silent by not handling exceptions
    def pg_exec(arg, silent: false)
      if @pg_connection
        begin
          last_stmt = nil # To make the current SQL statement visible to the rescue clause. FIXME Not used?
          if arg.is_a?(String)
            return nil if arg == ""
            last_stmt = arg
            @pg_connection.exec(last_stmt)
          else
            stmts = arg.flatten.compact
            return nil if stmts.empty?
#           stmts.unshift("set on_error_exit stop")
            last_stmt = stmts.last
            @pg_connection.exec(stmts.join(";\n"))
          end
        rescue PG::Error => ex
          if @error.nil?
            @error = ex
            @err = nil
          end
          if !silent # FIXME Why do we handle this? 
            $stderr.puts arg
            $stderr.puts
            $stderr.puts ex.message
            $stderr.flush
          end
          raise
        end

      # For dump of SQL statements
      else # @pg_commands is defined
        if arg.is_a?(String)
          @pg_commands << arg if arg != ""
        else
          @pg_commands.concat(arg)
        end
        nil
      end
    end

    def check_1c(r) 
      case r.nfields
        when 0; raise Error, "No columns returned"
        when 1; 
      else
        raise Error, "More than one column returned" 
      end
    end

    def check_1r(r) 
      if r.ntuples == 0
        raise Error, "No records returned"
      elsif r.ntuples > 1 
        raise Error, "More than one record returned" 
      end
    end
  end

  def self.sql_values(values) "'" + values.join("', '") + "'" end
  def self.sql_idents(values) '"' + values.join('", "') + '"' end
end