Module: Sequel::Oracle::DatabaseMethods

Defined in:
lib/sequel/oracle_extensions/schemata.rb

Constant Summary collapse

IMPLICIT_FLAG_ATTRIBUTES =

Specifies flag attributes which are considered implicit and, thus, do not require DDL clauses.

{
  :parallel=>false, :compress=>false, :logging=>true, :visible=>true,
  :rely=>false, :enabled=>true, :validate=>true
}

Instance Method Summary collapse

Instance Method Details

#alter_table(name, generator = nil, options = nil, &block) ⇒ Object

Overridden to support various Oracle-specific options.



286
287
288
289
290
291
292
293
294
295
296
 
# File 'lib/sequel/oracle_extensions/schemata.rb', line 286

def alter_table(name, generator=nil, options=nil, &block)
  if Hash === options
    generator ||= Schema::AlterTableGenerator.new(self, &block)
  alter_table_sql_list(name, generator.operations, options).
    flatten.each {|sql| execute_ddl(sql)}
  remove_cached_schema(name)
  nil
  else
   super(name, generator, &block)
  end
end

#foreign_keys(table, options = {}) ⇒ Object

# } }



252
253
254
 
# File 'lib/sequel/oracle_extensions/schemata.rb', line 252

def foreign_keys(table, options={})
	table_constraints table, 'R', options
end

#indexes(table, opts = {}) ⇒ Object

Options:

:valid

Filter by status: true => only VALID indexes, false => only UNUSABLE indexes

:all

Return all indexes, including the primary key index.

Example(s):

DB.indexes(:people)
# { :person_gender=>{
#     :unique=>false,
#     :valid=>true,
#     :db_type=>'BITMAP',
#     :tablespace=>:users,
#     :partitioned=>false,
#     :visible=>true,
#     :compress=>false,
#     :columns=>[:gender]
#   },
#   :person_name=>{
#     :unique=>false,
#     :valid=>true,
#     :db_type=>'NORMAL',
#     :tablespace=>:users,
#     :partitioned=>false,
#     :visible=>true,
#     :compress=>false,
#     :columns=>[:last_name, :first_name]
# } }

# NOTE: Passing :all=>true so we can get the primary key index.
DB.indexes(:employees, :all=>true)
# { :employee_pk=>{
#     :unique=>true,
#     :valid=>true,
#     :db_type=>'NORMAL',
#     :tablespace=>:users,
#     :partitioned=>false,
#     :visible=>true,
#     :compress=>false,
#     :columns=>[:id]
#   },
#   :employee_dept=>{
#     :unique=>false,
#     :valid=>true,
#     :db_type=>'BITMAP',
#     :type=>:bitmap,
#     :join=>[:dept_id],
#     :tablespace=>:users,
#     :partitioned=>false,
#     :visible=>true,
#     :compress=>false,
#     :columns=>[:departments__id]
# } }



114
115
116
117
118
119
120
121
122
123
124
125
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
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
 
# File 'lib/sequel/oracle_extensions/schemata.rb', line 114

def indexes(table, opts={})
	ds, result    = metadata_dataset, []
				outm          = sql_ident_to_sym_proc ds
	schema, table = ds.schema_and_table(table).map{|k| k.to_s.send(ds.identifier_input_method) if k} 
	who           = schema.nil? ? 'user' : 'all'
	
	# Build the dataset and apply filters for introspection of indexes.
	ds = ds.select(:i__index_name, :i__index_type, :i__join_index, :i__partitioned, :i__status,
	               :i__uniqueness, :i__visibility, :i__compression, :i__tablespace_name,
	               :i__logging, :i__degree, :i__instances, :ic__column_name).
       from(:"#{who}_indexes___i").
       join(:"#{who}_ind_columns___ic", [ [:index_name,:index_name] ]).
			where(:i__table_name=>table, :i__dropped=>'NO').
          order(:status.desc, :index_name, :ic__column_position)
				ds = ds.where :i__owner => schema, :c__index_owner => schema  unless schema.nil?
				ds = ds.where :i__status => (opts[:valid] ? 'VALID' : 'UNUSABLE') unless opts[:valid].nil?

				# Collect the indexes as a hash of subhashes, including a column list.
				hash, join_indexes = {}, []
				ds.each do |row|
key = outm[row[:index_name]]
unless subhash = hash[key]
	# Unconditional attributes
	subhash = hash[key] = {
		:columns=>[], :unique=>(row[:uniqueness]=='UNIQUE'),
		:db_type=>row[:index_type], :logging=>(row[:logging]=='YES'),
		:parallel=>(row[:degree]!='1' || row[:instances]!='1'),
		:tablespace=>outm[row[:tablespace_name]], :partitioned=>(row[:partitioned]=='YES'),
		:visible=>(row[:visibility]=='VISIBLE'), :compress=>(row[:compression]!='DISABLED')
	}
	
	# Conditional attributes
	subhash[:type] = $1.downcase.intern if subhash[:db_type] =~ /\b(BITMAP|NORMAL|DOMAIN)$/i
       subhash[:function_based] = true if /^FUNCTION-BASED\b/ === subhash[:db_type]
       subhash[:valid] = (row[:status]=='VALID') unless row[:status]=='N/A'
	join_indexes << row[:index_name] and subhash[:join] = [] if row[:join_index]=='YES'
end
subhash[:columns] << outm[row[:column_name]]
				end
				
				# Exclude the primary key index, if required and applicable.
				# NOTE:  Disabled primary keys do not use any indexes, so they are not applicable here.
				if ! opts[:all] and pk = primary_key(table)
if pk[:using_index]  then hash.delete pk[:using_index]
elsif pk[:enabled]   then hash.delete_if {|k,v| v[:columns]==pk[:columns] }
end					
				end
				
				# Collect any additional metadata about the indexes (such as bitmap join columns).
				unless join_indexes.empty?
ds = metadata_dataset.from(:"#{who}_join_ind_columns").where(:index_name=>join_indexes)
ds = ds.where :index_owner => schema unless schema.nil?
ds.each do |row|
	subhash    = hash[outm[row[:index_name]]]
	ref_column = outm[row[:outer_table_column]]
	pos        = subhash[:columns].index ref_column
	subhash[:columns][pos] = outm["#{row[:outer_table_name]}__#{ref_column}"]
	subhash[:join][pos]    = outm[row[:inner_table_column]]
end
				end
				
				# Done.
				hash
end

#primary_key(table, options = {}) ⇒ Object

Example:

DB.primary_key(:people)
# { :person_id=>{
#     :rely=>false,
#     :enabled=>true,
#     :validated=>true,
#     :using_index=>:person_pk,
#     :columns=>[:id]
# } }



194
195
196
197
198
 
# File 'lib/sequel/oracle_extensions/schemata.rb', line 194

def primary_key(table, options={})
	result = table_constraints table, 'P', options
				return unless result and not result.empty?
				result.values.first.tap{|pk| pk[:name] = result.keys.first }
end

#references(table, options = {}) ⇒ Object

Return a hash containing foreign key information for keys that refer to this table. Hash keys are constraint name symbols and values are subhashes. Foreign keys for this table are not returned by this method (unless they are self-referential). Options:

:enabled

Filter by status: true => only ENABLED foreign keys, false => only DISABLED foreign keys

:validated

Filter by validation: true => only VALIDATED foreign keys, false => only NOT VALIDATED foreign keys

Example:

DB.references(:employees)
# { :assignment_employee_fk=>{
#     :rely=>false,
#     :enabled=>true,
#     :validated=>true,
#     :columns=>[:employee_id],
#     :ref_constraint=>:employee_pk,
#     :table=>:assignments
#   },
#   :bonus_recipient_fk=>{
#     :rely=>false,
#     :enabled=>true,
#     :validated=>true,
#     :columns=>[:recipient_id],
#     :ref_constraint=>:employee_pk,
#     :table=>:bonuses
# } }



281
282
283
 
# File 'lib/sequel/oracle_extensions/schemata.rb', line 281

def references(table, options={})
	table_constraints table, 'R', options.merge(:table_name_column=>:t__table_name)
end

#table_metadata(table, options = {}) ⇒ Object

Returns a hash containing expanded table metadata that exposes Oracle-specific table attributes.

Basic Attributes:

:columns

a columns subhash derived from a call to the #schema(table,options={}) method

:schema_name

the name of the schema that owns this table

Extended Attributes: (NOTE: some of the following attributes may be nil with older OCI clients)

:index_only

is this an index-organized table?

:clustered

is this a clustered table?

:partitioned

is this a partitioned table?

:temporary

is this a global temporary table?

:typed

is this a … typed table? ( not sure what that means :-/ )



28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
# File 'lib/sequel/oracle_extensions/schemata.rb', line 28

def table_metadata(table,options={})
columns    = schema table, options
  attributes = columns.instance_variable_get :@features
attributes[:columns] = columns

# Collect table partitioning information, if applicable.
if attributes[:partitioning]
	ds, result    = metadata_dataset, []
					outm          = sql_ident_to_sym_proc ds
  schema, table = ds.schema_and_table(table).map{|k| k.to_s.send(ds.identifier_input_method) if k} 
    who           = schema.nil? ? 'user' : 'all'
					ds = ds.where :owner => schema unless schema.nil?
					
					# Basic partitioning info.
					attributes[:partitioning] = Hash[
ds.select(:partitioning_type.as(:type), :interval, :subpartitioning_type.as(:subtype)).
	    from(:"#{who}_part_tables").first(:table_name=>table).
	    map{|k,v| [k, k==:interval ? v : v.downcase.to_sym] }
	]
	
	# Partitioning key column info. 
					attributes[:partitioning][:key] =
ds.select(:column_name).from(:"#{who}_part_key_columns").order(:column_position).
	    where(:object_type=>'TABLE', :name=>table).map{|r| outm[r.values.first] }
				end
    
attributes
end

#unique_keys(table, options = {}) ⇒ Object

# } }



223
224
225
 
# File 'lib/sequel/oracle_extensions/schemata.rb', line 223

def unique_keys(table, options={})
	table_constraints table, 'U', options
end