Namespace

Class/Module Index [+]

Quicksearch

JdbcSpec::PostgreSQL

Public Class Methods

adapter_matcher(name, *) click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 21
def self.adapter_matcher(name, *)
  name =~ /postgre/ ? self : false
end
column_selector() click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 25
def self.column_selector
  [/postgre/, lambda {|cfg,col| col.extend(::JdbcSpec::PostgreSQL::Column)}]
end
jdbc_connection_class() click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 29
def self.jdbc_connection_class
  ::ActiveRecord::ConnectionAdapters::PostgresJdbcConnection
end

Public Instance Methods

_execute(sql, name = nil) click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 346
def _execute(sql, name = nil)
    case sql.strip
    when /\A\(?\s*(select|show)/ then
      @connection.execute_query(sql)
    else
      @connection.execute_update(sql)
    end
end
add_column(table_name, column_name, type, options = {}) click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 433
def add_column(table_name, column_name, type, options = {})
  execute("ALTER TABLE #{quote_table_name(table_name)} ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit])}")
  change_column_default(table_name, column_name, options[:default]) unless options[:default].nil?
  if options[:null] == false
    execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)} = '#{options[:default]}'") if options[:default]
    execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} SET NOT NULL")
  end
end
add_order_by_for_association_limiting!(sql, options) click to toggle source

ORDER BY clause for the passed order option.

PostgreSQL does not allow arbitrary ordering when using DISTINCT ON, so we work around this by wrapping the sql as a sub-select and ordering in that query.

# File lib/jdbc_adapter/jdbc_postgre.rb, line 380
def add_order_by_for_association_limiting!(sql, options)
  return sql if options[:order].blank?

  order = options[:order].split(',').collect { |s| s.strip }.reject(&:blank?)
  order.map! { |s| 'DESC' if s =~ /\bdesc$/ }
  order = order.zip((0...order.size).to_a).map { |s,i| "id_list.alias_#{i} #{s}" }.join(', ')

  sql.replace "SELECT * FROM (#{sql}) AS id_list ORDER BY #{order}"
end
change_column_null(table_name, column_name, null, default = nil) click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 461
def change_column_null(table_name, column_name, null, default = nil)
  unless null || default.nil?
    execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")
  end
  execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL")
end
columns(table_name, name=nil) click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 257
def columns(table_name, name=nil)
  schema_name = "public"
  if table_name =~ /\./
    parts = table_name.split(/\./)
    table_name = parts.pop
    schema_name = parts.join(".")
  end
  @connection.columns_internal(table_name, name, schema_name)
end
create_database(name, options = {}) click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 310
def create_database(name, options = {})
  execute "CREATE DATABASE \"#{name}\" ENCODING='#{options[:encoding] || 'utf8'}'"
end
default_sequence_name(table_name, pk = nil) click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 141
def default_sequence_name(table_name, pk = nil)
  default_pk, default_seq = pk_and_sequence_for(table_name)
  default_seq || "#{table_name}_#{pk || default_pk || 'id'}_seq"
end
distinct(columns, order_by) click to toggle source

SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause.

PostgreSQL requires the ORDER BY columns in the select list for distinct queries, and requires that the ORDER BY include the distinct column.

distinct("posts.id", "posts.created_at desc")
# File lib/jdbc_adapter/jdbc_postgre.rb, line 361
def distinct(columns, order_by)
  return "DISTINCT #{columns}" if order_by.blank?

  # construct a clean list of column names from the ORDER BY clause, removing
  # any asc/desc modifiers
  order_columns = order_by.split(',').collect { |s| s.split.first }
  order_columns.delete_if(&:blank?)
  order_columns = order_columns.zip((0...order_columns.size).to_a).map { |s,i| "#{s} AS alias_#{i}" }

  # return a DISTINCT ON() clause that's distinct on the columns we want but includes
  # all the required columns for the ORDER BY to work properly
  sql = "DISTINCT ON (#{columns}) #{columns}, "
  sql << order_columns * ', '
end
drop_database(name) click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 314
def drop_database(name)
  execute "DROP DATABASE \"#{name}\""
end
escape_bytea(s) click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 402
def escape_bytea(s)
  if s
    result = ''
    s.each_byte { |c| result << sprintf('\\%03o', c) }
    result
  end
end
indexes(table_name, name = nil) click to toggle source

From postgresql_adapter.rb

# File lib/jdbc_adapter/jdbc_postgre.rb, line 268
def indexes(table_name, name = nil)
  result = select_rows(        SELECT i.relname, d.indisunique, a.attname          FROM pg_class t, pg_class i, pg_index d, pg_attribute a         WHERE i.relkind = 'i'           AND d.indexrelid = i.oid           AND d.indisprimary = 'f'           AND t.oid = d.indrelid           AND t.relname = '#{table_name}'           AND a.attrelid = t.oid           AND ( d.indkey[0]=a.attnum OR d.indkey[1]=a.attnum              OR d.indkey[2]=a.attnum OR d.indkey[3]=a.attnum              OR d.indkey[4]=a.attnum OR d.indkey[5]=a.attnum              OR d.indkey[6]=a.attnum OR d.indkey[7]=a.attnum              OR d.indkey[8]=a.attnum OR d.indkey[9]=a.attnum )        ORDER BY i.relname, name)

  current_index = nil
  indexes = []

  result.each do |row|
    if current_index != row[0]
      indexes << ::ActiveRecord::ConnectionAdapters::IndexDefinition.new(table_name, row[0], row[1] == "t", [])
      current_index = row[0]
    end

    indexes.last.columns << row[2]
  end

  indexes
end
insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 220
def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
  # Extract the table from the insert sql. Yuck.
  table = sql.split(" ", 4)[2].gsub('"', '')

  # Try an insert with 'returning id' if available (PG >= 8.2)
  if supports_insert_with_returning? && id_value.nil? && false # FIXME:
    # Disabled, as it causes:
    # ActiveRecord::ActiveRecordError: A result was returned when none was expected
    # This was previously disabled because postgresql_version returned 0
    pk, sequence_name = *pk_and_sequence_for(table) unless pk
    if pk
      id_value = select_value("#{sql} RETURNING #{quote_column_name(pk)}")
      clear_query_cache #FIXME: Why now?
      return id_value
    end
  end

  # Otherwise, plain insert
  execute(sql, name)

  # Don't need to look up id_value if we already have it.
  # (and can't in case of non-sequence PK)
  unless id_value
    # If neither pk nor sequence name is given, look them up.
    unless pk || sequence_name
      pk, sequence_name = *pk_and_sequence_for(table)
    end

    # If a pk is given, fallback to default sequence name.
    # Don't fetch last insert id for a table without a pk.
    if pk && sequence_name ||= default_sequence_name(table, pk)
      id_value = last_insert_id(table, sequence_name)
    end
  end
  id_value
end
last_insert_id(table, sequence_name) click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 301
def last_insert_id(table, sequence_name)
  Integer(select_value("SELECT currval('#{sequence_name}')"))
end
modify_types(tp) click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 82
def modify_types(tp)
  tp[:primary_key] = "serial primary key"
  tp[:string][:limit] = 255
  tp[:integer][:limit] = nil
  tp[:boolean][:limit] = nil
  tp
end
postgresql_version() click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 90
def postgresql_version
  @postgresql_version ||=
    begin
      value = select_value('SELECT version()')
      if value =~ /PostgreSQL (\d+)\.(\d+)\.(\d+)/
        ($1.to_i * 10000) + ($2.to_i * 100) + $3.to_i
      else
        0
      end
    end
end
quote(value, column = nil) click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 390
def quote(value, column = nil)
  return value.quoted_id if value.respond_to?(:quoted_id)

  if value.kind_of?(String) && column && column.type == :binary
    "'#{escape_bytea(value)}'"
  elsif column && column.type == :primary_key
    return value.to_s
  else
    super
  end
end
quote_column_name(name) click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 410
def quote_column_name(name)
  %("#{name}")
end
quote_regclass(table_name) click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 166
def quote_regclass(table_name)
  table_name.to_s.split('.').map do |part| 
    part =~ /".*"/ ? part : quote_table_name(part)
  end.join('.')  
end
recreate_database(name) click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 305
def recreate_database(name)
  drop_database(name)
  create_database(name)
end
rename_table(name, new_name) click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 429
def rename_table(name, new_name)
  execute "ALTER TABLE #{name} RENAME TO #{new_name}"
end
structure_dump() click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 318
def structure_dump
  database = @config[:database]
  if database.nil?
    if @config[:url] =~ /\/([^\/]*)$/
      database = $1
    else
      raise "Could not figure out what database this url is for #{@config["url"]}"
    end
  end

  ENV['PGHOST']     = @config[:host] if @config[:host]
  ENV['PGPORT']     = @config[:port].to_s if @config[:port]
  ENV['PGPASSWORD'] = @config[:password].to_s if @config[:password]
  search_path = @config[:schema_search_path]
  search_path = "--schema=#{search_path}" if search_path

  @connection.connection.close
  begin
    definition = `pg_dump -i -U "#{@config[:username]}" -s -x -O #{search_path} #{database}`
    raise "Error dumping database" if $?.exitstatus == 1

    # need to patch away any references to SQL_ASCII as it breaks the JDBC driver
    definition.gsub(/SQL_ASCII/, 'UNICODE')
  ensure
    reconnect!
  end
end
supports_ddl_transactions?() click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 127
def supports_ddl_transactions?
  true
end
supports_insert_with_returning?() click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 123
def supports_insert_with_returning?
  postgresql_version >= 80200
end
supports_migrations?() click to toggle source

Does PostgreSQL support migrations?

# File lib/jdbc_adapter/jdbc_postgre.rb, line 103
def supports_migrations?
  true
end
supports_savepoints?() click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 131
def supports_savepoints?
  true
end
supports_standard_conforming_strings?() click to toggle source

Does PostgreSQL support standard conforming strings?

# File lib/jdbc_adapter/jdbc_postgre.rb, line 108
def supports_standard_conforming_strings?
  # Temporarily set the client message level above error to prevent unintentional
  # error messages in the logs when working on a PostgreSQL database server that
  # does not support standard conforming strings.
  client_min_messages_old = client_min_messages
  self.client_min_messages = 'panic'

  # postgres-pr does not raise an exception when client_min_messages is set higher
  # than error and "SHOW standard_conforming_strings" fails, but returns an empty
  # PGresult instead.
  has_support = select('SHOW standard_conforming_strings').to_a[0][0] rescue false
  self.client_min_messages = client_min_messages_old
  has_support
end
table_alias_length() click to toggle source

Returns the configured supported identifier length supported by PostgreSQL, or report the default of 63 on PostgreSQL 7.x.

# File lib/jdbc_adapter/jdbc_postgre.rb, line 137
def table_alias_length
  @table_alias_length ||= (postgresql_version >= 80000 ? select('SHOW max_identifier_length').to_a[0][0].to_i : 63)
end
tables() click to toggle source
# File lib/jdbc_adapter/jdbc_postgre.rb, line 488
def tables
  @connection.tables(database_name, nil, nil, ["TABLE"])
end

[Validate]

Generated with the Darkfish Rdoc Generator 2.