module Sequel::Oracle::DatabaseMethods
Constants
- AUTOINCREMENT
- DATABASE_ERROR_REGEXPS
- TEMPORARY
- TRANSACTION_ISOLATION_LEVELS
Attributes
Public Instance Methods
# File lib/sequel/adapters/shared/oracle.rb, line 13 def create_sequence(name, 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
Oracle namespaces indexes per table.
# File lib/sequel/adapters/shared/oracle.rb, line 35 def global_index_namespace? false end
Oracle supports deferrable constraints.
# File lib/sequel/adapters/shared/oracle.rb, line 55 def supports_deferrable_constraints? true end
DB2 supports transaction isolation levels.
# File lib/sequel/adapters/shared/oracle.rb, line 60 def supports_transaction_isolation_levels? true end
# File lib/sequel/adapters/shared/oracle.rb, line 39 def tables(opts={}) m = output_identifier_meth metadata_dataset.from(:tab).server(opts[:server]).select(:tname).filter(:tabtype => 'TABLE').map{|r| m.call(r[:tname])} end
# File lib/sequel/adapters/shared/oracle.rb, line 49 def view_exists?(name) m = input_identifier_meth metadata_dataset.from(:tab).filter(:tname =>m.call(name), :tabtype => 'VIEW').count > 0 end
# File lib/sequel/adapters/shared/oracle.rb, line 44 def views(opts={}) m = output_identifier_meth metadata_dataset.from(:tab).server(opts[:server]).select(:tname).filter(:tabtype => 'VIEW').map{|r| m.call(r[:tname])} end
Private Instance Methods
Handle Oracle specific ALTER TABLE SQL
# File lib/sequel/adapters/shared/oracle.rb, line 67 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 95 def auto_increment_sql AUTOINCREMENT end
# File lib/sequel/adapters/shared/oracle.rb, line 99 def create_sequence_sql(name, 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 103 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 109 def create_table_sql_list(name, generator, options={}) 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 135 def create_trigger_sql(table, name, definition, 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 153 def database_error_regexps DATABASE_ERROR_REGEXPS end
# File lib/sequel/adapters/shared/oracle.rb, line 157 def default_sequence_name(table, column) "seq_#{table}_#{column}" end
# File lib/sequel/adapters/shared/oracle.rb, line 161 def drop_sequence_sql(name) "DROP SEQUENCE #{quote_identifier(name)}" end
# File lib/sequel/adapters/shared/oracle.rb, line 165 def remove_cached_schema(table) @primary_key_sequences.delete(table) super end
# File lib/sequel/adapters/shared/oracle.rb, line 180 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 176 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 192 def supports_create_or_replace_view? true end
SQL fragment for showing a table is temporary
# File lib/sequel/adapters/shared/oracle.rb, line 216 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 199 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 205 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 211 def type_literal_generic_trueclass(column) :'char(1)' end
Oracle uses clob for text types.
# File lib/sequel/adapters/shared/oracle.rb, line 221 def uses_clob_for_text? true end