module Sequel::Oracle::DatabaseMethods
Constants
- DATABASE_ERROR_REGEXPS
- IGNORE_OWNERS
- TRANSACTION_ISOLATION_LEVELS
Attributes
Public Instance Methods
# File lib/sequel/adapters/shared/oracle.rb 19 def create_sequence(name, opts=OPTS) 20 self << create_sequence_sql(name, opts) 21 end
# File lib/sequel/adapters/shared/oracle.rb 23 def create_trigger(*args) 24 self << create_trigger_sql(*args) 25 end
# File lib/sequel/adapters/shared/oracle.rb 27 def current_user 28 @current_user ||= metadata_dataset.get{sys_context('USERENV', 'CURRENT_USER')} 29 end
# File lib/sequel/adapters/shared/oracle.rb 35 def database_type 36 :oracle 37 end
# File lib/sequel/adapters/shared/oracle.rb 31 def drop_sequence(name) 32 self << drop_sequence_sql(name) 33 end
# File lib/sequel/adapters/shared/oracle.rb 39 def foreign_key_list(table, opts=OPTS) 40 m = output_identifier_meth 41 im = input_identifier_meth 42 schema, table = schema_and_table(table) 43 ds = metadata_dataset. 44 from{[all_cons_columns.as(:pc), all_constraints.as(:p), all_cons_columns.as(:fc), all_constraints.as(:f)]}. 45 where{{ 46 f[:table_name]=>im.call(table), 47 f[:constraint_type]=>'R', 48 p[:owner]=>f[:r_owner], 49 p[:constraint_name]=>f[:r_constraint_name], 50 pc[:owner]=>p[:owner], 51 pc[:constraint_name]=>p[:constraint_name], 52 pc[:table_name]=>p[:table_name], 53 fc[:owner]=>f[:owner], 54 fc[:constraint_name]=>f[:constraint_name], 55 fc[:table_name]=>f[:table_name], 56 fc[:position]=>pc[:position]}}. 57 select{[p[:table_name].as(:table), pc[:column_name].as(:key), fc[:column_name].as(:column), f[:constraint_name].as(:name)]}. 58 order{[:table, fc[:position]]} 59 ds = ds.where{{f[:schema_name]=>im.call(schema)}} if schema 60 61 fks = {} 62 ds.each do |r| 63 if fk = fks[r[:name]] 64 fk[:columns] << m.call(r[:column]) 65 fk[:key] << m.call(r[:key]) 66 else 67 fks[r[:name]] = {:name=>m.call(r[:name]), :columns=>[m.call(r[:column])], :table=>m.call(r[:table]), :key=>[m.call(r[:key])]} 68 end 69 end 70 fks.values 71 end
# File lib/sequel/adapters/shared/oracle.rb 73 def freeze 74 current_user 75 server_version 76 @conversion_procs.freeze 77 super 78 end
Oracle
namespaces indexes per table.
# File lib/sequel/adapters/shared/oracle.rb 81 def global_index_namespace? 82 false 83 end
The version of the Oracle
server, used for determining capability.
# File lib/sequel/adapters/shared/oracle.rb 115 def server_version(server=nil) 116 return @server_version if @server_version 117 @server_version = synchronize(server) do |conn| 118 (conn.server_version rescue nil) if conn.respond_to?(:server_version) 119 end 120 unless @server_version 121 @server_version = if m = /(\d+)\.(\d+)\.?(\d+)?\.?(\d+)?/.match(fetch("select version from PRODUCT_COMPONENT_VERSION where lower(product) like 'oracle%'").single_value) 122 (m[1].to_i*1000000) + (m[2].to_i*10000) + (m[3].to_i*100) + m[4].to_i 123 else 124 0 125 end 126 end 127 @server_version 128 end
Oracle
supports deferrable constraints.
# File lib/sequel/adapters/shared/oracle.rb 132 def supports_deferrable_constraints? 133 true 134 end
Oracle
supports transaction isolation levels.
# File lib/sequel/adapters/shared/oracle.rb 137 def supports_transaction_isolation_levels? 138 true 139 end
# File lib/sequel/adapters/shared/oracle.rb 87 def tables(opts=OPTS) 88 m = output_identifier_meth 89 metadata_dataset.from(:all_tables). 90 server(opts[:server]). 91 where(:dropped=>'NO'). 92 exclude(:owner=>IGNORE_OWNERS). 93 select(:table_name). 94 map{|r| m.call(r[:table_name])} 95 end
# File lib/sequel/adapters/shared/oracle.rb 106 def view_exists?(name) 107 m = input_identifier_meth 108 metadata_dataset.from(:all_views). 109 exclude(:owner=>IGNORE_OWNERS). 110 where(:view_name=>m.call(name)). 111 count > 0 112 end
# File lib/sequel/adapters/shared/oracle.rb 97 def views(opts=OPTS) 98 m = output_identifier_meth 99 metadata_dataset.from(:all_views). 100 server(opts[:server]). 101 exclude(:owner=>IGNORE_OWNERS). 102 select(:view_name). 103 map{|r| m.call(r[:view_name])} 104 end
Private Instance Methods
# File lib/sequel/adapters/shared/oracle.rb 143 def alter_table_sql(table, op) 144 case op[:op] 145 when :add_column 146 if op[:primary_key] 147 sqls = [] 148 sqls << alter_table_sql(table, op.merge(:primary_key=>nil)) 149 if op[:auto_increment] 150 seq_name = default_sequence_name(table, op[:name]) 151 sqls << drop_sequence_sql(seq_name) 152 sqls << create_sequence_sql(seq_name, op) 153 sqls << "UPDATE #{quote_schema_table(table)} SET #{quote_identifier(op[:name])} = #{seq_name}.nextval" 154 end 155 sqls << "ALTER TABLE #{quote_schema_table(table)} ADD PRIMARY KEY (#{quote_identifier(op[:name])})" 156 sqls 157 else 158 "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}" 159 end 160 when :set_column_null 161 "ALTER TABLE #{quote_schema_table(table)} MODIFY #{quote_identifier(op[:name])} #{op[:null] ? 'NULL' : 'NOT NULL'}" 162 when :set_column_type 163 "ALTER TABLE #{quote_schema_table(table)} MODIFY #{quote_identifier(op[:name])} #{type_literal(op)}" 164 when :set_column_default 165 "ALTER TABLE #{quote_schema_table(table)} MODIFY #{quote_identifier(op[:name])} DEFAULT #{literal(op[:default])}" 166 else 167 super(table, op) 168 end 169 end
# File lib/sequel/adapters/shared/oracle.rb 171 def auto_increment_sql 172 '' 173 end
# File lib/sequel/adapters/shared/oracle.rb 175 def create_sequence_sql(name, opts=OPTS) 176 "CREATE SEQUENCE #{quote_identifier(name)} start with #{opts [:start_with]||1} increment by #{opts[:increment_by]||1} nomaxvalue" 177 end
# File lib/sequel/adapters/shared/oracle.rb 179 def create_table_from_generator(name, generator, options) 180 drop_statement, create_statements = create_table_sql_list(name, generator, options) 181 (execute_ddl(drop_statement) rescue nil) if drop_statement 182 create_statements.each{|sql| execute_ddl(sql)} 183 end
# File lib/sequel/adapters/shared/oracle.rb 185 def create_table_sql_list(name, generator, options=OPTS) 186 statements = [create_table_sql(name, generator, options)] 187 drop_seq_statement = nil 188 generator.columns.each do |c| 189 if c[:auto_increment] 190 c[:sequence_name] ||= default_sequence_name(name, c[:name]) 191 unless c[:create_sequence] == false 192 drop_seq_statement = drop_sequence_sql(c[:sequence_name]) 193 statements << create_sequence_sql(c[:sequence_name], c) 194 end 195 unless c[:create_trigger] == false 196 c[:trigger_name] ||= "BI_#{name}_#{c[:name]}" 197 trigger_definition = <<-end_sql 198 BEGIN 199 IF :NEW.#{quote_identifier(c[:name])} IS NULL THEN 200 SELECT #{c[:sequence_name]}.nextval INTO :NEW.#{quote_identifier(c[:name])} FROM dual; 201 END IF; 202 END; 203 end_sql 204 statements << create_trigger_sql(name, c[:trigger_name], trigger_definition, {:events => [:insert]}) 205 end 206 end 207 end 208 [drop_seq_statement, statements] 209 end
# File lib/sequel/adapters/shared/oracle.rb 211 def create_trigger_sql(table, name, definition, opts=OPTS) 212 events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete] 213 sql = <<-end_sql 214 CREATE#{' OR REPLACE' if opts[:replace]} TRIGGER #{quote_identifier(name)} 215 #{opts[:after] ? 'AFTER' : 'BEFORE'} #{events.map{|e| e.to_s.upcase}.join(' OR ')} ON #{quote_schema_table(table)} 216 REFERENCING NEW AS NEW FOR EACH ROW 217 #{definition} 218 end_sql 219 sql 220 end
# File lib/sequel/adapters/shared/oracle.rb 230 def database_error_regexps 231 DATABASE_ERROR_REGEXPS 232 end
# File lib/sequel/adapters/shared/oracle.rb 234 def default_sequence_name(table, column) 235 "seq_#{table}_#{column}" 236 end
# File lib/sequel/adapters/shared/oracle.rb 238 def drop_sequence_sql(name) 239 "DROP SEQUENCE #{quote_identifier(name)}" 240 end
# File lib/sequel/adapters/shared/oracle.rb 242 def remove_cached_schema(table) 243 Sequel.synchronize{@primary_key_sequences.delete(table)} 244 super 245 end
# File lib/sequel/adapters/shared/oracle.rb 257 def sequence_for_table(table) 258 return nil unless autosequence 259 Sequel.synchronize{return @primary_key_sequences[table] if @primary_key_sequences.has_key?(table)} 260 261 begin 262 sch = schema(table) 263 rescue Sequel::Error 264 return nil 265 end 266 267 pk = sch.select{|k, v| v[:primary_key]} 268 pks = if pk.length == 1 269 seq = "seq_#{table}_#{pk.first.first}" 270 seq.to_sym unless from(:user_sequences).where(:sequence_name=>input_identifier_meth.call(seq)).empty? 271 end 272 Sequel.synchronize{@primary_key_sequences[table] = pks} 273 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 253 def set_transaction_isolation_sql(level) 254 "SET TRANSACTION ISOLATION LEVEL #{TRANSACTION_ISOLATION_LEVELS[level]}" 255 end
Oracle
supports CREATE OR REPLACE VIEW.
# File lib/sequel/adapters/shared/oracle.rb 276 def supports_create_or_replace_view? 277 true 278 end
SQL
fragment for showing a table is temporary
# File lib/sequel/adapters/shared/oracle.rb 300 def temporary_table_sql 301 'GLOBAL TEMPORARY ' 302 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 283 def type_literal_generic_bignum_symbol(column) 284 :integer 285 end
Oracle
doesn't have a time type, so use timestamp for all time columns.
# File lib/sequel/adapters/shared/oracle.rb 289 def type_literal_generic_only_time(column) 290 :timestamp 291 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 295 def type_literal_generic_trueclass(column) 296 :'char(1)' 297 end
Oracle
uses clob for text types.
# File lib/sequel/adapters/shared/oracle.rb 305 def uses_clob_for_text? 306 true 307 end
Oracle
supports views with check option, but not local.
# File lib/sequel/adapters/shared/oracle.rb 310 def view_with_check_option_support 311 true 312 end