Module: PgPower::ConnectionAdapters::PostgreSQLAdapter::ForeignerMethods

Included in:
PgPower::ConnectionAdapters::PostgreSQLAdapter
Defined in:
lib/pg_power/connection_adapters/postgresql_adapter/foreigner_methods.rb

Overview

Provides methods to extend ActiveRecord::ConnectionAdapters::PostgreSQLAdapter to support foreign keys feature.

Instance Method Summary collapse

Instance Method Details

#add_foreign_key(from_table, to_table, options = {}) ⇒ Object

Add foreign key.

Ensures that an index is created for the foreign key, unless :exclude_index is true.

Options:

  • :column

  • :primary_key

  • :dependent

  • :exclude_index [Boolean]

  • :concurrent_index [Boolean]

Options Hash (options):

  • :column (String, Symbol)
  • :primary_key (String, Symbol)
  • :dependent (Hash)
  • :exclude_index (Boolean)
  • :concurrent_index (Boolean)

Raises:



90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
 
# File 'lib/pg_power/connection_adapters/postgresql_adapter/foreigner_methods.rb', line 90

def add_foreign_key(from_table, to_table, options = {})
  options[:column]             ||= id_column_name_from_table_name(to_table)
  options[:exclude_index]      ||= false

  if index_exists?(from_table, options[:column]) && !options[:exclude_index]
    raise PgPower::IndexExistsError,
      "The index, #{index_name(from_table, options[:column])}, already exists." \
      "  Use :exclude_index => true when adding the foreign key."
  end

  sql = "ALTER TABLE #{quote_table_name(from_table)} #{add_foreign_key_sql(from_table, to_table, options)}"
  execute(sql)

  # GOTCHA:
  #   Index can not be created concurrently inside transaction in PostgreSQL.
  #   So, in case of concurrently created index with foreign key only
  #   foreign key will be created inside migration transaction and after
  #   closing transaction queries for index creation will be send to database.
  #   That's why I prevent here normal index creation in case of
  #   `concurrent_index` option is given.
  #   NOTE: Index creation after closing migration transaction could lead
  #   to weird effects when transaction moves smoothly, but index
  #   creation with error. In that case transaction will not be rolled back.
  #   As it was closed before even index was attempted to create.
  #   -- zekefast 2012-09-12
  unless options[:exclude_index] || options[:concurrent_index]
    add_index(from_table, options[:column])
  end
end

#add_foreign_key_sql(from_table, to_table, options = {}) ⇒ Object

Return the SQL code fragment to add the foreign key based on the table names and options.



121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
 
# File 'lib/pg_power/connection_adapters/postgresql_adapter/foreigner_methods.rb', line 121

def add_foreign_key_sql(from_table, to_table, options = {})
  column           = options[:column]
  options_options  = options[:options]
  foreign_key_name = foreign_key_name(from_table, column, options)
  primary_key      = options[:primary_key] || "id"
  dependency       = dependency_sql(options[:dependent])

  sql =
    "ADD CONSTRAINT #{quote_column_name(foreign_key_name)} " +
    "FOREIGN KEY (#{quote_column_name(column)}) " +
    "REFERENCES #{quote_table_name(ActiveRecord::Migrator.proper_table_name(to_table))}(#{primary_key})"
  sql << " #{dependency}"      if dependency.present?
  sql << " #{options_options}" if options_options

  sql
end

#drop_table(*args) ⇒ Object

Drop table and optionally disable triggers. Changes adapted from github.com/matthuhiggins/foreigner/blob/e72ab9c454c156056d3f037d55e3359cd972af32/lib/foreigner/connection_adapters/sql2003.rb NOTE: Disabling referential integrity requires superuser access in postgres.

Default AR behavior is just to drop_table.

Options:

  • :force - force disabling of referential integrity

Note: I don’t know a good way to test this -mike 20120420



60
61
62
63
64
65
66
67
 
# File 'lib/pg_power/connection_adapters/postgresql_adapter/foreigner_methods.rb', line 60

def drop_table(*args)
  options = args.clone.extract_options!
  if options[:force]
    disable_referential_integrity { super }
  else
    super
  end
end

#foreign_keys(table_name) ⇒ Foreigner::ConnectionAdapters::ForeignKeyDefinition

Fetch information about foreign keys related to the passed table.



12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
# File 'lib/pg_power/connection_adapters/postgresql_adapter/foreigner_methods.rb', line 12

def foreign_keys(table_name)
  relation, schema = table_name.to_s.split('.', 2).reverse
  quoted_schema = schema ? "'#{schema}'" : "ANY (current_schemas(false))"

  fk_info = select_all <<-SQL
    SELECT nsp.nspname || '.' || t2.relname AS to_table,
           a1.attname    AS column     ,
           a2.attname    AS primary_key,
           c.conname     AS name       ,
           c.confdeltype AS dependency
    FROM pg_constraint c
    JOIN pg_class t1      ON c.conrelid     = t1.oid
    JOIN pg_class t2      ON c.confrelid    = t2.oid
    JOIN pg_attribute a1  ON a1.attnum      = c.conkey[1]  AND a1.attrelid = t1.oid
    JOIN pg_attribute a2  ON a2.attnum      = c.confkey[1] AND a2.attrelid = t2.oid
    JOIN pg_namespace t3  ON c.connamespace = t3.oid
    JOIN pg_namespace nsp ON nsp.oid        = t2.relnamespace
    WHERE c.contype = 'f'
    AND t1.relname = '#{relation}'
    AND t3.nspname = #{quoted_schema}
    ORDER BY c.conname
  SQL

  fk_info.map do |row|
    options = { :column      => row['column'],
                :name        => row['name'],
                :primary_key => row['primary_key'] }

    options[:dependent] =
      case row['dependency']
      when 'c' then :delete
      when 'n' then :nullify
      when 'r' then :restrict
      end

    PgPower::ConnectionAdapters::ForeignKeyDefinition.new(table_name, row['to_table'], options)
  end
end

#id_column_name_from_table_name(table) ⇒ Object

Build the foreign key column id from the referenced table.



179
180
181
 
# File 'lib/pg_power/connection_adapters/postgresql_adapter/foreigner_methods.rb', line 179

def id_column_name_from_table_name(table)
  "#{table.to_s.split('.').last.singularize}_id"
end

#remove_foreign_key(from_table, to_table_or_options_hash, options = {}) ⇒ Object

TODO Determine if we can refactor the method signature

remove_foreign_key(from_table, to_table_or_options_hash, options={}) => remove_foreign_key(from_table, to_table, options={})

Remove the foreign key. The flexible method signature allows calls of two principal forms. Examples:

remove_foreign_key(from_table, options_hash)

or:

remove_foreign_key(from_table, to_table, options_hash)



154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
 
# File 'lib/pg_power/connection_adapters/postgresql_adapter/foreigner_methods.rb', line 154

def remove_foreign_key(from_table, to_table_or_options_hash, options={})
  if Hash === to_table_or_options_hash
    options          =   to_table_or_options_hash
    column           =   options[:column]
    foreign_key_name =   foreign_key_name(from_table, column, options)
    column           ||= id_column_name_from_foreign_key_metadata(from_table, foreign_key_name)
  else
    column           =   id_column_name_from_table_name(to_table_or_options_hash)
    foreign_key_name =   foreign_key_name(from_table, column)
  end

  execute "ALTER TABLE #{quote_table_name(from_table)} #{remove_foreign_key_sql(foreign_key_name)}"

  options[:exclude_index] ||= false
  unless options[:exclude_index] || !index_exists?(from_table, column) then
    remove_index(from_table, column)
  end
end

#remove_foreign_key_sql(foreign_key_name) ⇒ Object

Return the SQL code fragment to remove foreign key based on table name and options.



174
175
176
 
# File 'lib/pg_power/connection_adapters/postgresql_adapter/foreigner_methods.rb', line 174

def remove_foreign_key_sql(foreign_key_name)
  "DROP CONSTRAINT #{quote_column_name(foreign_key_name)}"
end

#supports_foreign_keys? ⇒ Boolean 



5
6
7
 
# File 'lib/pg_power/connection_adapters/postgresql_adapter/foreigner_methods.rb', line 5

def supports_foreign_keys?
  true
end