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]

Parameters:

  • from_table (String, Symbol)
  • to_table (String, Symbol)
  • options (Hash) (defaults to: {})

Options Hash (options):

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

Raises:



87
88
89
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
 
# File 'lib/pg_power/connection_adapters/postgresql_adapter/foreigner_methods.rb', line 87

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.



118
119
120
121
122
123
124
125
126
127
128
129
130
131
 
# File 'lib/pg_power/connection_adapters/postgresql_adapter/foreigner_methods.rb', line 118

def add_foreign_key_sql(from_table, to_table, options = {})
  foreign_key_name = foreign_key_name(from_table, options[: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(options[: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



57
58
59
60
61
62
63
64
 
# File 'lib/pg_power/connection_adapters/postgresql_adapter/foreigner_methods.rb', line 57

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.

Parameters:

  • table_name (String, Symbol) —

    name of table (e.g. “users”, “music.bands”)

Returns:

  • (Foreigner::ConnectionAdapters::ForeignKeyDefinition) 


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
 
# 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 "    SELECT nsp.nspname || '.' || t2.relname AS to_table,\n           a1.attname    AS column     ,\n           a2.attname    AS primary_key,\n           c.conname     AS name       ,\n           c.confdeltype AS dependency\n    FROM pg_constraint c\n    JOIN pg_class t1      ON c.conrelid     = t1.oid\n    JOIN pg_class t2      ON c.confrelid    = t2.oid\n    JOIN pg_attribute a1  ON a1.attnum      = c.conkey[1]  AND a1.attrelid = t1.oid\n    JOIN pg_attribute a2  ON a2.attnum      = c.confkey[1] AND a2.attrelid = t2.oid\n    JOIN pg_namespace t3  ON c.connamespace = t3.oid\n    JOIN pg_namespace nsp ON nsp.oid        = t2.relnamespace\n    WHERE c.contype = 'f'\n    AND t1.relname = '\#{relation}'\n    AND t3.nspname = \#{quoted_schema}\n    ORDER BY c.conname\n  SQL\n\n  fk_info.map do |row|\n    options = {:column => row['column'], :name => row['name'], :primary_key => row['primary_key']}\n\n    options[:dependent] = case row['dependency']\n      when 'c' then :delete\n      when 'n' then :nullify\n      when 'r' then :restrict\n    end\n\n    PgPower::ConnectionAdapters::ForeignKeyDefinition.new(table_name, row['to_table'], options)\n  end\nend\n"

#id_column_name_from_table_name(table) ⇒ Object

Build the foreign key column id from the referenced table.



172
173
174
 
# File 'lib/pg_power/connection_adapters/postgresql_adapter/foreigner_methods.rb', line 172

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)

Parameters:

  • from_table (String, Symbol)
  • to_table_or_options_hash (String, Hash) 


149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
 
# File 'lib/pg_power/connection_adapters/postgresql_adapter/foreigner_methods.rb', line 149

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
  remove_index(from_table, column) unless options[:exclude_index] || !index_exists?(from_table, column)
end

#remove_foreign_key_sql(foreign_key_name) ⇒ Object

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



167
168
169
 
# File 'lib/pg_power/connection_adapters/postgresql_adapter/foreigner_methods.rb', line 167

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

#supports_foreign_keys? ⇒ Boolean

Returns:

  • (Boolean) 


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

def supports_foreign_keys?
  true
end