begin
  require 'postgres'
rescue Object => ex
  Logger.error 'Ruby-PostgreSQL bindings are not installed!'
  Logger.error ex
end

require 'og/store/sql'

# Customize the standard postgres resultset to make
# more compatible with Og.

class PGresult
  def blank?
    0 == num_tuples
  end
  
  def next
    self
  end

  def each_row
    for row in (0...num_tuples)
      yield(self, row)
    end
  end
  
  def first_value
    val = getvalue(0, 0)
    clear
    return val
  end
  
  alias_method :close, :clear
end

module Og

module PsqlUtils
  include SqlUtils
  
  def escape(str)
    return nil unless str
    return PGconn.escape(str)
  end

  # TODO, mneumann:
  #
  # Blobs are actually a lot faster (and uses up less storage) for large data I
  # think, as they need not to be encoded and decoded. I'd like to have both ;-)
  # BYTEA is easier to handle than BLOBs, but if you implement BLOBs in a way
  # that they are transparent to the user (as I did in Ruby/DBI), I'd prefer that
  # way.
  
  def blob(val)
    val.gsub(/[\000-\037\047\134\177-\377]/) do |b|
      "\\#{ b[0].to_s(8).rjust(3, '0') }" 
    end
  end  
  
  def parse_blob(val)
    val.gsub(/\\(\\|'|[0-3][0-7][0-7])/) do |s|
      if s.size == 2 then s[1,1] else s[1,3].oct.chr end
    end
  end   
     
end

# A Store that persists objects into a PostgreSQL database.
# To read documentation about the methods, consult the documentation
# for SqlStore and Store.
# 
# === Design
#
# The getvalue interface is used instead of each for extra
# performance.

class PsqlStore < SqlStore
  extend PsqlUtils
  include PsqlUtils

  def self.create(options)
    # gmosx: system is used to avoid shell expansion.
    system 'createdb', options[:name], '-U', options[:user]
    super
  end

  def self.destroy(options)
    system 'dropdb', options[:name], '-U', options[:user]
    super
  end

  def initialize(options)
    super
  
    @typemap.update(Og::Blob => 'bytea')

    @conn = PGconn.connect(
      options[:address], 
      options[:port], nil, nil, 
      options[:name], 
      options[:user].to_s, 
      options[:password].to_s
    )
    
    schema_order = options[:schema_order]
    encoding = options[:encoding]
    min_messages = options[:min_messages]
    
    @conn.exec("SET search_path TO #{schema_order}") if schema_order
    @conn.exec("SET client_encoding TO '#{encoding}'") if encoding
    @conn.exec("SET client_min_messages TO '#{min_messages}'") if min_messages
  rescue => ex
    # gmosx: any idea how to better test this?
    if ex.to_s =~ /database .* does not exist/i
      Logger.info "Database '#{options[:name]}' not found!"
      self.class.create(options)
      retry
    end
    raise
  end

  def close
    @conn.close
    super
  end

  def enchant(klass, manager)
    klass.const_set 'OGSEQ', "#{table(klass)}_oid_seq"
    klass.property :oid, Fixnum, :sql => 'serial PRIMARY KEY'
    super
  end

  def query(sql)
    Logger.debug sql if $DBG
    return @conn.exec(sql)
  rescue => ex
    handle_sql_exception(ex, sql)
  end

  def exec(sql)
    Logger.debug sql if $DBG
    @conn.exec(sql).clear
  rescue => ex
    handle_sql_exception(ex, sql)
  end

  def sql_update(sql)
    Logger.debug sql if $DBG  
    res = @conn.exec(sql)
    changed = res.cmdtuples
    res.clear
    changed
  end

  # Start a new transaction.
  
  def start  
    # neumann: works with earlier PSQL databases too.
    exec('BEGIN TRANSACTION') if @transaction_nesting < 1
    @transaction_nesting += 1
  end

private

  def create_table(klass)
    fields = fields_for_class(klass)

    sql = "CREATE TABLE #{klass::OGTABLE} (#{fields.join(', ')}"
    
    # Create table constrains.
    
    if klass.__meta and constrains = klass.__meta[:sql_constrain]
      sql << ", #{constrains.join(', ')}"
    end
    
    sql << ") WITHOUT OIDS;"
    
    # Create indices.
    
    if klass.__meta and indices = klass.__meta[:index]
      for data in indices
        idx, options = *data
        idx = idx.to_s
        pre_sql, post_sql = options[:pre], options[:post]
        idxname = idx.gsub(/ /, "").gsub(/,/, "_").gsub(/\(.*\)/, "")
        sql << " CREATE #{pre_sql} INDEX #{klass::OGTABLE}_#{idxname}_idx #{post_sql} ON #{klass::OGTABLE} (#{idx});"  
      end
    end

    begin
      @conn.exec(sql).clear
      Logger.info "Created table '#{klass::OGTABLE}'."
    rescue Object => ex
      # gmosx: any idea how to better test this?
      if ex.to_s =~ /relation .* already exists/i
        Logger.debug 'Table already exists' if $DBG
        return
      else
        raise
      end
    end

    # Create join tables if needed. Join tables are used in
    # 'many_to_many' relations.
    
     if klass.__meta and join_tables = klass.__meta[:join_tables] 
      for info in join_tables
        begin
          create_join_table_sql(info).each do |sql|
            @conn.exec(sql).clear
          end
        rescue Object => ex
          # gmosx: any idea how to better test this?
          if ex.to_s =~ /relation .* already exists/i
            Logger.debug 'Join table already exists' if $DBG
          else
            raise
          end
        end
      end
    end
  end

  def drop_table(klass)
    super
    exec "DROP SEQUENCE #{klass::OGSEQ}"    
  end

  def create_field_map(klass)
    res = @conn.exec "SELECT * FROM #{klass::OGTABLE} LIMIT 1"
    map = {}
  
    for field in res.fields
      map[field.intern] = res.fieldnum(field)
    end
  
    return map
  ensure
    res.clear if res
  end

  def read_prop(p, col)
    if p.klass.ancestors.include?(Integer)
      return "#{self.class}.parse_int(res.getvalue(row, #{col} + offset))"
    elsif p.klass.ancestors.include?(Float)
      return "#{self.class}.parse_float(res.getvalue(row, #{col} + offset))"
    elsif p.klass.ancestors.include?(String)
      return "res.getvalue(row, #{col} + offset)"
    elsif p.klass.ancestors.include?(Time)
      return "#{self.class}.parse_timestamp(res.getvalue(row, #{col} + offset))"
    elsif p.klass.ancestors.include?(Date)
      return "#{self.class}.parse_date(res.getvalue(row, #{col} + offset))"
    elsif p.klass.ancestors.include?(TrueClass)
      return %|('t' == res.getvalue(row, #{col} + offset))|
    elsif p.klass.ancestors.include?(Og::Blob)
      return "#{self.class}.parse_blob(res.getvalue(row, #{col} + offset))"
    else 
      return "YAML.load(res.getvalue(row, #{col} + offset))"
    end    
  end

  #--
  # TODO: create stored procedure.
  #++

  def eval_og_insert(klass)
    props = klass.properties
    values = props.collect { |p| write_prop(p) }.join(',') 

    if klass.metadata.superclass or klass.metadata.subclasses
      props << Property.new(:ogtype, String)
      values << ", '#{klass}'"
    end
    
    sql = "INSERT INTO #{klass::OGTABLE} (#{props.collect {|p| p.symbol.to_s}.join(',')}) VALUES (#{values})"

    klass.class_eval %{ 
      def og_insert(store)
        #{Aspects.gen_advice_code(:og_insert, klass.advices, :pre) if klass.respond_to?(:advices)}
        res = store.conn.exec "SELECT nextval('#{klass::OGSEQ}')"
        @#{klass.pk_symbol} = res.getvalue(0, 0).to_i
        res.clear
        store.conn.exec("#{sql}").clear
        #{Aspects.gen_advice_code(:og_insert, klass.advices, :post) if klass.respond_to?(:advices)}
      end
    }
  end

  def read_row(obj, res, res_row, row)
    res.fields.each_with_index do |field, idx|
      obj.instance_variable_set "@#{field}", res.getvalue(row, idx)
    end
  end

end

end

# * George Moschovitis <gm@navel.gr>
# * Michael Neumann <mneumann@ntecs.de>
# * Ysabel <deb@ysabel.org>