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



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

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



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

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
 
# 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
  
  sql = "FOREIGN KEY (#{columns_sql}) REFERENCES #{quote_table_name(to_table)}(#{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



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

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



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

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"



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

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



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

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