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 121 def server_version(server=nil) 122 return @server_version if @server_version 123 @server_version = synchronize(server) do |conn| 124 (conn.server_version rescue nil) if conn.respond_to?(:server_version) 125 end 126 unless @server_version 127 @server_version = if m = /(\d+)\.(\d+)\.?(\d+)?\.?(\d+)?/.match(fetch("select version from PRODUCT_COMPONENT_VERSION where lower(product) like 'oracle%'").single_value) 128 (m[1].to_i*1000000) + (m[2].to_i*10000) + (m[3].to_i*100) + m[4].to_i 129 else 130 0 131 end 132 end 133 @server_version 134 end
Oracle
supports deferrable constraints.
# File lib/sequel/adapters/shared/oracle.rb 138 def supports_deferrable_constraints? 139 true 140 end
Oracle
supports transaction isolation levels.
# File lib/sequel/adapters/shared/oracle.rb 143 def supports_transaction_isolation_levels? 144 true 145 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
Whether a view with a given name exists. By default, looks in all schemas other than system schemas. If the :current_schema option is given, looks in the schema for the current user.
# File lib/sequel/adapters/shared/oracle.rb 108 def view_exists?(name, opts=OPTS) 109 ds = metadata_dataset.from(:all_views).where(:view_name=>input_identifier_meth.call(name)) 110 111 if opts[:current_schema] 112 ds = ds.where(:owner=>Sequel.function(:SYS_CONTEXT, 'userenv', 'current_schema')) 113 else 114 ds = ds.exclude(:owner=>IGNORE_OWNERS) 115 end 116 117 ds.count > 0 118 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 149 def alter_table_sql(table, op) 150 case op[:op] 151 when :add_column 152 if op[:primary_key] 153 sqls = [] 154 sqls << alter_table_sql(table, op.merge(:primary_key=>nil)) 155 if op[:auto_increment] 156 seq_name = default_sequence_name(table, op[:name]) 157 sqls << drop_sequence_sql(seq_name) 158 sqls << create_sequence_sql(seq_name, op) 159 sqls << "UPDATE #{quote_schema_table(table)} SET #{quote_identifier(op[:name])} = #{seq_name}.nextval" 160 end 161 sqls << "ALTER TABLE #{quote_schema_table(table)} ADD PRIMARY KEY (#{quote_identifier(op[:name])})" 162 sqls 163 else 164 "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}" 165 end 166 when :set_column_null 167 "ALTER TABLE #{quote_schema_table(table)} MODIFY #{quote_identifier(op[:name])} #{op[:null] ? 'NULL' : 'NOT NULL'}" 168 when :set_column_type 169 "ALTER TABLE #{quote_schema_table(table)} MODIFY #{quote_identifier(op[:name])} #{type_literal(op)}" 170 when :set_column_default 171 "ALTER TABLE #{quote_schema_table(table)} MODIFY #{quote_identifier(op[:name])} DEFAULT #{literal(op[:default])}" 172 else 173 super(table, op) 174 end 175 end
# File lib/sequel/adapters/shared/oracle.rb 177 def auto_increment_sql 178 '' 179 end
Support min/max integer values on Oracle
only if they use a NUMBER column with a fixed precision and no scale.
# File lib/sequel/adapters/shared/oracle.rb 184 def column_schema_integer_min_max_values(column) 185 super if column[:db_type] =~ /NUMBER\(\d+\)/i || (column[:db_type] == 'NUMBER' && column[:column_size].is_a?(Integer) && column[:scale] == 0) 186 end
# File lib/sequel/adapters/shared/oracle.rb 188 def create_sequence_sql(name, opts=OPTS) 189 "CREATE SEQUENCE #{quote_identifier(name)} start with #{opts [:start_with]||1} increment by #{opts[:increment_by]||1} nomaxvalue" 190 end
# File lib/sequel/adapters/shared/oracle.rb 192 def create_table_from_generator(name, generator, options) 193 drop_statement, create_statements = create_table_sql_list(name, generator, options) 194 swallow_database_error{execute_ddl(drop_statement)} if drop_statement 195 create_statements.each{|sql| execute_ddl(sql)} 196 end
# File lib/sequel/adapters/shared/oracle.rb 198 def create_table_sql_list(name, generator, options=OPTS) 199 statements = [create_table_sql(name, generator, options)] 200 drop_seq_statement = nil 201 generator.columns.each do |c| 202 if c[:auto_increment] 203 c[:sequence_name] ||= default_sequence_name(name, c[:name]) 204 unless c[:create_sequence] == false 205 drop_seq_statement = drop_sequence_sql(c[:sequence_name]) 206 statements << create_sequence_sql(c[:sequence_name], c) 207 end 208 unless c[:create_trigger] == false 209 c[:trigger_name] ||= "BI_#{name}_#{c[:name]}" 210 trigger_definition = <<-end_sql 211 BEGIN 212 IF :NEW.#{quote_identifier(c[:name])} IS NULL THEN 213 SELECT #{c[:sequence_name]}.nextval INTO :NEW.#{quote_identifier(c[:name])} FROM dual; 214 END IF; 215 END; 216 end_sql 217 statements << create_trigger_sql(name, c[:trigger_name], trigger_definition, {:events => [:insert]}) 218 end 219 end 220 end 221 [drop_seq_statement, statements] 222 end
# File lib/sequel/adapters/shared/oracle.rb 224 def create_trigger_sql(table, name, definition, opts=OPTS) 225 events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete] 226 sql = <<-end_sql 227 CREATE#{' OR REPLACE' if opts[:replace]} TRIGGER #{quote_identifier(name)} 228 #{opts[:after] ? 'AFTER' : 'BEFORE'} #{events.map{|e| e.to_s.upcase}.join(' OR ')} ON #{quote_schema_table(table)} 229 REFERENCING NEW AS NEW FOR EACH ROW 230 #{definition} 231 end_sql 232 sql 233 end
# File lib/sequel/adapters/shared/oracle.rb 243 def database_error_regexps 244 DATABASE_ERROR_REGEXPS 245 end
# File lib/sequel/adapters/shared/oracle.rb 247 def default_sequence_name(table, column) 248 "seq_#{table}_#{column}" 249 end
# File lib/sequel/adapters/shared/oracle.rb 251 def drop_sequence_sql(name) 252 "DROP SEQUENCE #{quote_identifier(name)}" 253 end
# File lib/sequel/adapters/shared/oracle.rb 255 def remove_cached_schema(table) 256 Sequel.synchronize{@primary_key_sequences.delete(table)} 257 super 258 end
# File lib/sequel/adapters/shared/oracle.rb 270 def sequence_for_table(table) 271 return nil unless autosequence 272 Sequel.synchronize{return @primary_key_sequences[table] if @primary_key_sequences.has_key?(table)} 273 274 begin 275 sch = schema(table) 276 rescue Sequel::Error 277 return nil 278 end 279 280 pk = sch.select{|k, v| v[:primary_key]} 281 pks = if pk.length == 1 282 seq = "seq_#{table}_#{pk.first.first}" 283 seq.to_sym unless from(:user_sequences).where(:sequence_name=>input_identifier_meth.call(seq)).empty? 284 end 285 Sequel.synchronize{@primary_key_sequences[table] = pks} 286 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 266 def set_transaction_isolation_sql(level) 267 "SET TRANSACTION ISOLATION LEVEL #{TRANSACTION_ISOLATION_LEVELS[level]}" 268 end
Oracle
supports CREATE OR REPLACE VIEW.
# File lib/sequel/adapters/shared/oracle.rb 289 def supports_create_or_replace_view? 290 true 291 end
SQL
fragment for showing a table is temporary
# File lib/sequel/adapters/shared/oracle.rb 313 def temporary_table_sql 314 'GLOBAL TEMPORARY ' 315 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 296 def type_literal_generic_bignum_symbol(column) 297 :integer 298 end
Oracle
doesn't have a time type, so use timestamp for all time columns.
# File lib/sequel/adapters/shared/oracle.rb 302 def type_literal_generic_only_time(column) 303 :timestamp 304 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 308 def type_literal_generic_trueclass(column) 309 :'char(1)' 310 end
Oracle
uses clob for text types.
# File lib/sequel/adapters/shared/oracle.rb 318 def uses_clob_for_text? 319 true 320 end
Oracle
supports views with check option, but not local.
# File lib/sequel/adapters/shared/oracle.rb 323 def view_with_check_option_support 324 true 325 end