module Sequel::Oracle::DatabaseMethods
Constants
- AUTOINCREMENT
- DATABASE_ERROR_REGEXPS
- IGNORE_OWNERS
- TEMPORARY
- TRANSACTION_ISOLATION_LEVELS
Attributes
Public Instance Methods
# File lib/sequel/adapters/shared/oracle.rb, line 13 def create_sequence(name, opts=OPTS) self << create_sequence_sql(name, opts) end
# File lib/sequel/adapters/shared/oracle.rb, line 17 def create_trigger(*args) self << create_trigger_sql(*args) end
# File lib/sequel/adapters/shared/oracle.rb, line 21 def current_user @current_user ||= metadata_dataset.get{sys_context('USERENV', 'CURRENT_USER')} end
Oracle uses the :oracle database type
# File lib/sequel/adapters/shared/oracle.rb, line 30 def database_type :oracle end
# File lib/sequel/adapters/shared/oracle.rb, line 25 def drop_sequence(name) self << drop_sequence_sql(name) end
# File lib/sequel/adapters/shared/oracle.rb, line 34 def foreign_key_list(table, opts=OPTS) m = output_identifier_meth im = input_identifier_meth schema, table = schema_and_table(table) ds = metadata_dataset. from(:all_cons_columns___pc, :all_constraints___p, :all_cons_columns___fc, :all_constraints___f). where(:f__table_name=>im.call(table), :f__constraint_type=>'R', :p__owner=>:f__r_owner, :p__constraint_name=>:f__r_constraint_name, :pc__owner=>:p__owner, :pc__constraint_name=>:p__constraint_name, :pc__table_name=>:p__table_name, :fc__owner=>:f__owner, :fc__constraint_name=>:f__constraint_name, :fc__table_name=>:f__table_name, :fc__position=>:pc__position). select(:p__table_name___table, :pc__column_name___key, :fc__column_name___column, :f__constraint_name___name). order(:table, :fc__position) ds = ds.where(:f__schema_name=>im.call(schema)) if schema fks = {} ds.each do |r| if fk = fks[r[:name]] fk[:columns] << m.call(r[:column]) fk[:key] << m.call(r[:key]) else fks[r[:name]] = {:name=>m.call(r[:name]), :columns=>[m.call(r[:column])], :table=>m.call(r[:table]), :key=>[m.call(r[:key])]} end end fks.values end
Oracle namespaces indexes per table.
# File lib/sequel/adapters/shared/oracle.rb, line 58 def global_index_namespace? false end
Oracle supports deferrable constraints.
# File lib/sequel/adapters/shared/oracle.rb, line 92 def supports_deferrable_constraints? true end
Oracle supports transaction isolation levels.
# File lib/sequel/adapters/shared/oracle.rb, line 97 def supports_transaction_isolation_levels? true end
# File lib/sequel/adapters/shared/oracle.rb, line 64 def tables(opts=OPTS) m = output_identifier_meth metadata_dataset.from(:all_tables). server(opts[:server]). where(:dropped=>'NO'). exclude(:owner=>IGNORE_OWNERS). select(:table_name). map{|r| m.call(r[:table_name])} end
# File lib/sequel/adapters/shared/oracle.rb, line 83 def view_exists?(name) m = input_identifier_meth metadata_dataset.from(:all_views). exclude(:owner=>IGNORE_OWNERS). where(:view_name=>m.call(name)). count > 0 end
# File lib/sequel/adapters/shared/oracle.rb, line 74 def views(opts=OPTS) m = output_identifier_meth metadata_dataset.from(:all_views). server(opts[:server]). exclude(:owner=>IGNORE_OWNERS). select(:view_name). map{|r| m.call(r[:view_name])} end
Private Instance Methods
Handle Oracle specific ALTER TABLE SQL
# File lib/sequel/adapters/shared/oracle.rb, line 104 def alter_table_sql(table, op) case op[:op] when :add_column if op[:primary_key] sqls = [] sqls << alter_table_sql(table, op.merge(:primary_key=>nil)) if op[:auto_increment] seq_name = default_sequence_name(table, op[:name]) sqls << drop_sequence_sql(seq_name) sqls << create_sequence_sql(seq_name, op) sqls << "UPDATE #{quote_schema_table(table)} SET #{quote_identifier(op[:name])} = #{seq_name}.nextval" end sqls << "ALTER TABLE #{quote_schema_table(table)} ADD PRIMARY KEY (#{quote_identifier(op[:name])})" sqls else "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}" end when :set_column_null "ALTER TABLE #{quote_schema_table(table)} MODIFY #{quote_identifier(op[:name])} #{op[:null] ? 'NULL' : 'NOT NULL'}" when :set_column_type "ALTER TABLE #{quote_schema_table(table)} MODIFY #{quote_identifier(op[:name])} #{type_literal(op)}" when :set_column_default "ALTER TABLE #{quote_schema_table(table)} MODIFY #{quote_identifier(op[:name])} DEFAULT #{literal(op[:default])}" else super(table, op) end end
# File lib/sequel/adapters/shared/oracle.rb, line 132 def auto_increment_sql AUTOINCREMENT end
# File lib/sequel/adapters/shared/oracle.rb, line 136 def create_sequence_sql(name, opts=OPTS) "CREATE SEQUENCE #{quote_identifier(name)} start with #{opts [:start_with]||1} increment by #{opts[:increment_by]||1} nomaxvalue" end
# File lib/sequel/adapters/shared/oracle.rb, line 140 def create_table_from_generator(name, generator, options) drop_statement, create_statements = create_table_sql_list(name, generator, options) (execute_ddl(drop_statement) rescue nil) if drop_statement create_statements.each{|sql| execute_ddl(sql)} end
# File lib/sequel/adapters/shared/oracle.rb, line 146 def create_table_sql_list(name, generator, options=OPTS) statements = [create_table_sql(name, generator, options)] drop_seq_statement = nil generator.columns.each do |c| if c[:auto_increment] c[:sequence_name] ||= default_sequence_name(name, c[:name]) unless c[:create_sequence] == false drop_seq_statement = drop_sequence_sql(c[:sequence_name]) statements << create_sequence_sql(c[:sequence_name], c) end unless c[:create_trigger] == false c[:trigger_name] ||= "BI_#{name}_#{c[:name]}" trigger_definition = <<-end_sql BEGIN IF :NEW.#{quote_identifier(c[:name])} IS NULL THEN SELECT #{c[:sequence_name]}.nextval INTO :NEW.#{quote_identifier(c[:name])} FROM dual; END IF; END; end_sql statements << create_trigger_sql(name, c[:trigger_name], trigger_definition, {:events => [:insert]}) end end end [drop_seq_statement, statements] end
# File lib/sequel/adapters/shared/oracle.rb, line 172 def create_trigger_sql(table, name, definition, opts=OPTS) events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete] sql = <<-end_sql CREATE#{' OR REPLACE' if opts[:replace]} TRIGGER #{quote_identifier(name)} #{opts[:after] ? 'AFTER' : 'BEFORE'} #{events.map{|e| e.to_s.upcase}.join(' OR ')} ON #{quote_schema_table(table)} REFERENCING NEW AS NEW FOR EACH ROW #{definition} end_sql sql end
# File lib/sequel/adapters/shared/oracle.rb, line 190 def database_error_regexps DATABASE_ERROR_REGEXPS end
# File lib/sequel/adapters/shared/oracle.rb, line 194 def default_sequence_name(table, column) "seq_#{table}_#{column}" end
# File lib/sequel/adapters/shared/oracle.rb, line 198 def drop_sequence_sql(name) "DROP SEQUENCE #{quote_identifier(name)}" end
# File lib/sequel/adapters/shared/oracle.rb, line 202 def remove_cached_schema(table) @primary_key_sequences.delete(table) super end
# File lib/sequel/adapters/shared/oracle.rb, line 217 def sequence_for_table(table) return nil unless autosequence @primary_key_sequences.fetch(table) do |key| pk = schema(table).select{|k, v| v[:primary_key]} @primary_key_sequences[table] = if pk.length == 1 seq = "seq_#{table}_#{pk.first.first}" seq.to_sym unless from(:user_sequences).filter(:sequence_name=>input_identifier_meth.call(seq)).empty? end end end
Oracle doesn't support READ UNCOMMITTED OR REPEATABLE READ transaction isolation levels, so upgrade to the next highest level in those cases.
# File lib/sequel/adapters/shared/oracle.rb, line 213 def set_transaction_isolation_sql(level) "SET TRANSACTION ISOLATION LEVEL #{TRANSACTION_ISOLATION_LEVELS[level]}" end
Oracle supports CREATE OR REPLACE VIEW.
# File lib/sequel/adapters/shared/oracle.rb, line 229 def supports_create_or_replace_view? true end
SQL fragment for showing a table is temporary
# File lib/sequel/adapters/shared/oracle.rb, line 253 def temporary_table_sql TEMPORARY end
Oracle's integer/:number type handles larger values than most other databases's bigint types, so it should be safe to use for Bignum.
# File lib/sequel/adapters/shared/oracle.rb, line 236 def type_literal_generic_bignum(column) :integer end
Oracle doesn't have a time type, so use timestamp for all time columns.
# File lib/sequel/adapters/shared/oracle.rb, line 242 def type_literal_generic_time(column) :timestamp end
Oracle doesn't have a boolean type or even a reasonable facsimile. Using a char(1) seems to be the recommended way.
# File lib/sequel/adapters/shared/oracle.rb, line 248 def type_literal_generic_trueclass(column) :'char(1)' end
Oracle uses clob for text types.
# File lib/sequel/adapters/shared/oracle.rb, line 258 def uses_clob_for_text? true end
Oracle supports views with check option, but not local.
# File lib/sequel/adapters/shared/oracle.rb, line 263 def view_with_check_option_support true end