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

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.

: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”.

: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.



68
69
70
71
72
73
74
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 68

def add_foreign_key(from_table, to_table, options = {})
  column = options[:column] || "#{to_table.to_s.singularize}_id"
  constraint_name = foreign_key_constraint_name(from_table, column, 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



192
193
194
195
196
197
198
199
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 192

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



164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 164

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:



76
77
78
79
80
81
82
83
84
85
86
87
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 76

def foreign_key_definition(to_table, options = {}) #:nodoc:
  column = options[:column] || "#{to_table.to_s.singularize}_id"
  primary_key = options[:primary_key] || "id"
  sql = "FOREIGN KEY (#{quote_column_name(column)}) REFERENCES #{quote_table_name(to_table)}(#{primary_key})"
  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



126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 126

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 primary_key
          ,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
  SQL

  fk_info.map do |row|
    options = {:column => oracle_downcase(row['column_name']), :name => oracle_downcase(row['name']),
      :primary_key => oracle_downcase(row['primary_key'])}
    case row['delete_rule']
    when 'CASCADE'
      options[:dependent] = :delete
    when 'SET NULL'
      options[:dependent] = :nullify
    end
    OracleEnhancedForeignKeyDefinition.new(table_name, oracle_downcase(row['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



98
99
100
101
102
103
104
105
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 98

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"



206
207
208
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 206

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



211
212
213
214
215
216
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements_ext.rb', line 211

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