class MicroSql::PgAdapter

Constants

TRANSACTION_STATUSES

Public Class Methods

new(url) click to toggle source
# File lib/micro_sql/pg_adapter.rb, line 2
def initialize(url)
  require 'pg'

  uri = URI.parse(url)
  
  @impl = PG.connect :host     => uri.host, 
                     :port     => uri.port || 5433, 
                     :user     => uri.user, 
                     :password => uri.password, 
                     :dbname   => uri.path[1..-1]

  @impl.set_notice_receiver { |result| MicroSql.logger.info(result.error_message) }
end

Public Instance Methods

connection() click to toggle source
# File lib/micro_sql/pg_adapter.rb, line 18
def connection
  @impl
end
execute_batch(sql) click to toggle source
# File lib/micro_sql/pg_adapter.rb, line 94
def execute_batch(sql)
  sql.split(";").each { |part|
    exec!(part) 
  }
end
primary_key(table) click to toggle source
# File lib/micro_sql/pg_adapter.rb, line 80
def primary_key(table)
  keys = primary_keys(table)
  raise(Error, "No support for primary key (in table #{table})") if keys.length > 1
  keys.first || raise(Error, "No primary key in table #{table}")
end
primary_keys(table) click to toggle source
# File lib/micro_sql/pg_adapter.rb, line 65
  def primary_keys(table)
    sql = <<-SQL
    SELECT  pg_attribute.attname
    FROM    pg_index, pg_class, pg_attribute 
    WHERE 
      pg_class.oid = ?::regclass AND
      indrelid = pg_class.oid AND
      pg_attribute.attrelid = pg_class.oid AND 
      pg_attribute.attnum = any(pg_index.indkey) AND 
      indisprimary
    SQL
    
    exec(sql, table).map(&:first)
  end
tables() click to toggle source
# File lib/micro_sql/pg_adapter.rb, line 61
def tables
  exec("SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%' AND tablename NOT LIKE 'sql_%'").map(&:first)
end
transaction() { || ... } click to toggle source
# File lib/micro_sql/pg_adapter.rb, line 31
def transaction(&block)
  return savepoint_transaction(&block) if transaction?

  begin
    r = nil
    @impl.transaction do
      r = yield
    end
    r
  rescue RollbackException
    nil
  end
end
transaction?() click to toggle source
# File lib/micro_sql/pg_adapter.rb, line 26
def transaction?
  status = @impl.transaction_status
  status == PG::Connection::PQTRANS_INTRANS || status == PG::Connection::PQTRANS_INERROR
end
transaction_status() click to toggle source
# File lib/micro_sql/pg_adapter.rb, line 22
def transaction_status
  TRANSACTION_STATUSES[@impl.transaction_status]
end

Private Instance Methods

conversion_for(ftype, fmod) click to toggle source
# File lib/micro_sql/pg_adapter.rb, line 150
def conversion_for(ftype, fmod)
  sym = case ftype
  when 16                 then :boolean
  when 17                 then :bytea
  when 19                 then :nop
  when 20, 21, 22, 23, 26 then :integer
  when 25                 then :nop           # "text"
  when 700, 701           then :float
  when 790, 1700          then :big_decimal
  when 1083, 1266         then :string_to_time
  end

  return sym if sym && Conversion.respond_to?(sym)
  raise "Unsupported conversion #{sym.inspect}" if sym
  
  typename = @impl.exec( "SELECT format_type($1,$2)", [ftype, fmod] ).getvalue( 0, 0 )
  
  return :nop if typename == "unknown"
  raise "Unsupported format_type #{typename.inspect} (ftype, fmod: #{ftype}, #{fmod})"
end
convert_records(result, records) click to toggle source
# File lib/micro_sql/pg_adapter.rb, line 171
def convert_records(result, records)
  # Get the type of the result columns
  converters = (0 ... result.num_fields).map do |i| 
    conversion = conversion_for result.ftype(i), result.fmod(i)
    [ conversion, i ] unless conversion == :nop
  end.compact

  records.map do |record|
    converters.each do |type, idx|
      record[idx] = Conversion.send type, record[idx]
    end
    record
  end
end
execute(flag, sql, *args) click to toggle source
# File lib/micro_sql/pg_adapter.rb, line 102
def execute(flag, sql, *args)
  execute_(flag, sql, *args)
rescue PG::Error
  raise Error, $!.message
end
execute_(flag, sql, *args) click to toggle source
# File lib/micro_sql/pg_adapter.rb, line 108
def execute_(flag, sql, *args)
  adjusted_sql = replace_placeholders(sql)

  result = if adjusted_sql && (flag == :prepare || flag == :ask) && prepared_statement = prepare(adjusted_sql) 
    @impl.exec_prepared(prepared_statement, args)
  else
    @impl.exec(adjusted_sql || sql, args)
  end

  case sql
  when /^\s*UPDATE\b/
    return result.cmd_tuples
  when /^\s*DELETE\b/
    return result.cmd_tuples
  when /^\s*INSERT\b/
    # postgresql has a different way of returning newly inserted ids; e.g.
    #   "INSERT ... RETURNING id"
    # The returned id value is returned to the pg driver the same as
    # a SELECT would be. We therefore evaluate only the first record.
    flag = :insert
    records = result.values
  else
    records = result.values
    records = records[0,1] if flag == :ask
  end

  result = convert_records result, records

  flag == :insert ? format_results_for_ask(result) : result
end
insert_sql(table_name, *args) click to toggle source
# File lib/micro_sql/pg_adapter.rb, line 88
def insert_sql(table_name, *args)
  "#{super} RETURNING #{table(table_name).primary_key}"
end
prepare_query(key, sql) click to toggle source
# File lib/micro_sql/pg_adapter.rb, line 196
def prepare_query(key, sql)
  @impl.prepare(key, sql)
  key
end
replace_placeholders(sql) click to toggle source
# File lib/micro_sql/pg_adapter.rb, line 186
def replace_placeholders(sql)
  idx = 0
  query = sql.gsub("?") do 
    idx += 1
    "$#{idx}" 
  end
  
  idx == 0 ? nil : query
end
savepoint_transaction() { || ... } click to toggle source
# File lib/micro_sql/pg_adapter.rb, line 47
def savepoint_transaction(&block)
  savepoint_name = "sqdb_#{object_id}"

  exec! "SAVEPOINT #{savepoint_name}"
  r = yield
  exec! "RELEASE SAVEPOINT #{savepoint_name}"
  r
rescue RollbackException
  exec! "ROLLBACK TO SAVEPOINT #{savepoint_name}"
  nil
end