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.



85
86
87
88
89
90
91
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 85

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



235
236
237
238
239
240
241
242
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 235

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 ====================================



207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 207

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:



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/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 93

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



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
195
196
197
198
199
200
201
202
203
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 160

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



129
130
131
132
133
134
135
136
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 129

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"



249
250
251
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 249

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



254
255
256
257
258
259
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 254

def synonyms #:nodoc:
  select_all("SELECT synonym_name, table_owner, table_name, db_link FROM all_synonyms WHERE owner = SYS_CONTEXT('userenv', 'current_schema')").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

#table_definition_tablespace ⇒ Object 



29
30
31
32
33
34
35
36
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 29

def table_definition_tablespace
  # TODO: Support specifying an :index_tablespace option in create_table?
  tablespace_sql = ''
  if tablespace = default_tablespace_for(:index)
    tablespace_sql << " USING INDEX TABLESPACE #{tablespace}"
  end
  tablespace_sql
end