Module: ActiveRecord::ConnectionAdapters::OracleEnhancedSchemaStatementsExt

Defined in:
lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb

Instance Method Summary collapse

Instance Method Details

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

Adds a new foreign key to the from_table, referencing the primary key of to_table (syntax and partial implementation taken from github.com/matthuhiggins/foreigner)

The foreign key will be named after the from and to tables unless you pass :name as an option.

Examples

Creating a foreign key

add_foreign_key(:comments, :posts)

generates

ALTER TABLE comments ADD CONSTRAINT
   comments_post_id_fk FOREIGN KEY (post_id) REFERENCES posts (id)

Creating a named foreign key

add_foreign_key(:comments, :posts, :name => 'comments_belongs_to_posts')

generates

ALTER TABLE comments ADD CONSTRAINT
   comments_belongs_to_posts FOREIGN KEY (post_id) REFERENCES posts (id)

Creating a cascading foreign_key on a custom column

add_foreign_key(:people, :people, :column => 'best_friend_id', :dependent => :nullify)

generates

ALTER TABLE people ADD CONSTRAINT
   people_best_friend_id_fk FOREIGN KEY (best_friend_id) REFERENCES people (id)
   ON DELETE SET NULL

Creating a composite foreign key

add_foreign_key(:comments, :posts, :columns => ['post_id', 'author_id'], :name => 'comments_post_fk')

generates

ALTER TABLE comments ADD CONSTRAINT
   comments_post_fk FOREIGN KEY (post_id, author_id) REFERENCES posts (post_id, author_id)

Supported options

:column

Specify the column name on the from_table that references the to_table. By default this is guessed to be the singular name of the to_table with “_id” suffixed. So a to_table of :posts will use “post_id” as the default :column.

:columns

An array of column names when defining composite foreign keys. An alias of :column provided for improved readability.

:primary_key

Specify the column name on the to_table that is referenced by this foreign key. By default this is assumed to be “id”. Ignored when defining composite foreign keys.

:name

Specify the name of the foreign key constraint. This defaults to use from_table and foreign key column.

:dependent

If set to :delete, the associated records in from_table are deleted when records in to_table table are deleted. If set to :nullify, the foreign key column is set to NULL.



76
77
78
79
80
81
82
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 76

def add_foreign_key(from_table, to_table, options = {})
  columns = options[:column] || options[:columns] || "#{to_table.to_s.singularize}_id"
  constraint_name = foreign_key_constraint_name(from_table, columns, options)
  sql = "ALTER TABLE #{quote_table_name(from_table)} ADD CONSTRAINT #{quote_column_name(constraint_name)} "
  sql << foreign_key_definition(to_table, options)
  execute sql
end

#add_primary_key_trigger(table_name, options = {}) ⇒ Object

Create primary key trigger (so that you can skip primary key value in INSERT statement). By default trigger name will be “table_name_pkt”, you can override the name with :trigger_name option (but it is not recommended to override it as then this trigger will not be detected by ActiveRecord model and it will still do prefetching of sequence value).

add_primary_key_trigger :users

You can also create primary key trigger using create_table with :primary_key_trigger option:

create_table :users, :primary_key_trigger => true do |t|
  # ...
end



24
25
26
27
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 24

def add_primary_key_trigger(table_name, options={})
  # call the same private method that is used for create_table :primary_key_trigger => true
  create_primary_key_trigger(table_name, options)
end

#add_synonym(name, table_name, options = {}) ⇒ Object

Add synonym to existing table or view or sequence. Can be used to create local synonym to remote table in other schema or in other database Examples:

add_synonym :posts, "blog.posts"
add_synonym :posts_seq, "blog.posts_seq"
add_synonym :employees, "hr.employees@dblink", :force => true



226
227
228
229
230
231
232
233
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 226

def add_synonym(name, table_name, options = {})
  sql = "CREATE"
  if options[:force] == true
    sql << " OR REPLACE"
  end
  sql << " SYNONYM #{quote_table_name(name)} FOR #{quote_table_name(table_name)}"
  execute sql
end

#disable_referential_integrity(&block) ⇒ Object

REFERENTIAL INTEGRITY ====================================



198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 198

def disable_referential_integrity(&block) #:nodoc:
  sql_constraints = <<-SQL
  SELECT constraint_name, owner, table_name
    FROM user_constraints
    WHERE constraint_type = 'R'
    AND status = 'ENABLED'
  SQL
  old_constraints = select_all(sql_constraints)
  begin
    old_constraints.each do |constraint|
      execute "ALTER TABLE #{constraint["table_name"]} DISABLE CONSTRAINT #{constraint["constraint_name"]}"
    end
    yield
  ensure
    old_constraints.each do |constraint|
      execute "ALTER TABLE #{constraint["table_name"]} ENABLE CONSTRAINT #{constraint["constraint_name"]}"
    end
  end
end

#foreign_key_definition(to_table, options = {}) ⇒ Object

:nodoc:



84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 84

def foreign_key_definition(to_table, options = {}) #:nodoc:
  columns = Array(options[:column] || options[:columns])
  
  if columns.size > 1
    # composite foreign key
    columns_sql = columns.map {|c| quote_column_name(c)}.join(',')
    references = options[:references] || columns
    references_sql = references.map {|c| quote_column_name(c)}.join(',')
  else
    columns_sql = quote_column_name(columns.first || "#{to_table.to_s.singularize}_id")
    references = options[:references] ? options[:references].first : nil
    references_sql = quote_column_name(options[:primary_key] || references || "id")
  end

  table_name = ActiveRecord::Migrator.proper_table_name(to_table)

  sql = "FOREIGN KEY (#{columns_sql}) REFERENCES #{quote_table_name(table_name)}(#{references_sql})"

  case options[:dependent]
  when :nullify
    sql << " ON DELETE SET NULL"
  when :delete
    sql << " ON DELETE CASCADE"
  end
  sql
end

#foreign_keys(table_name) ⇒ Object

get table foreign keys for schema dump



151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 151

def foreign_keys(table_name) #:nodoc:
  (owner, desc_table_name, db_link) = @connection.describe(table_name)

  fk_info = select_all(<<-SQL, 'Foreign Keys')
    SELECT r.table_name to_table
          ,rc.column_name references_column
          ,cc.column_name
          ,c.constraint_name name
          ,c.delete_rule
      FROM user_constraints#{db_link} c, user_cons_columns#{db_link} cc,
           user_constraints#{db_link} r, user_cons_columns#{db_link} rc
     WHERE c.owner = '#{owner}'
       AND c.table_name = '#{desc_table_name}'
       AND c.constraint_type = 'R'
       AND cc.owner = c.owner
       AND cc.constraint_name = c.constraint_name
       AND r.constraint_name = c.r_constraint_name
       AND r.owner = c.owner
       AND rc.owner = r.owner
       AND rc.constraint_name = r.constraint_name
       AND rc.position = cc.position
    ORDER BY name, to_table, column_name, references_column
  SQL

  fks = {}

  fk_info.map do |row|
    name = oracle_downcase(row['name'])
    fks[name] ||= { :columns => [], :to_table => oracle_downcase(row['to_table']), :references => [] }
    fks[name][:columns] << oracle_downcase(row['column_name'])
    fks[name][:references] << oracle_downcase(row['references_column'])
    case row['delete_rule']
    when 'CASCADE'
      fks[name][:dependent] = :delete
    when 'SET NULL'
      fks[name][:dependent] = :nullify
    end
  end
  
  fks.map do |k, v|
    options = {:name => k, :columns => v[:columns], :references => v[:references], :dependent => v[:dependent]}
    OracleEnhancedForeignKeyDefinition.new(table_name, v[:to_table], options)
  end
end

#remove_foreign_key(from_table, options) ⇒ Object

Remove the given foreign key from the table.

Examples
Remove the suppliers_company_id_fk in the suppliers table.
remove_foreign_key :suppliers, :companies
Remove the foreign key named accounts_branch_id_fk in the accounts table.
remove_foreign_key :accounts, :column => :branch_id
Remove the foreign key named party_foreign_key in the accounts table.
remove_foreign_key :accounts, :name => :party_foreign_key



120
121
122
123
124
125
126
127
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 120

def remove_foreign_key(from_table, options)
  if Hash === options
    constraint_name = foreign_key_constraint_name(from_table, options[:column], options)
  else
    constraint_name = foreign_key_constraint_name(from_table, "#{options.to_s.singularize}_id")
  end
  execute "ALTER TABLE #{quote_table_name(from_table)} DROP CONSTRAINT #{quote_column_name(constraint_name)}"
end

#remove_synonym(name) ⇒ Object

Remove existing synonym to table or view or sequence Example:

remove_synonym :posts, "blog.posts"



240
241
242
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 240

def remove_synonym(name)
  execute "DROP SYNONYM #{quote_table_name(name)}"
end

#supports_foreign_keys? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


6
7
8
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 6

def supports_foreign_keys? #:nodoc:
  true
end

#synonyms ⇒ Object

get synonyms for schema dump



245
246
247
248
249
250
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 245

def synonyms #:nodoc:
  select_all("SELECT synonym_name, table_owner, table_name, db_link FROM user_synonyms").collect do |row|
    OracleEnhancedSynonymDefinition.new(oracle_downcase(row['synonym_name']),
      oracle_downcase(row['table_owner']), oracle_downcase(row['table_name']), oracle_downcase(row['db_link']))
  end
end