module Sequel::Oracle::DatabaseMethods
Constants
- DATABASE_ERROR_REGEXPS
- IGNORE_OWNERS
- TRANSACTION_ISOLATION_LEVELS
Attributes
Public Instance Methods
Source
# File lib/sequel/adapters/shared/oracle.rb 20 def create_sequence(name, opts=OPTS) 21 self << create_sequence_sql(name, opts) 22 end
Source
# File lib/sequel/adapters/shared/oracle.rb 24 def create_trigger(*args) 25 self << create_trigger_sql(*args) 26 end
Source
# File lib/sequel/adapters/shared/oracle.rb 28 def current_user 29 @current_user ||= metadata_dataset.get{sys_context('USERENV', 'CURRENT_USER')} 30 end
Source
# File lib/sequel/adapters/shared/oracle.rb 36 def database_type 37 :oracle 38 end
Source
# File lib/sequel/adapters/shared/oracle.rb 32 def drop_sequence(name) 33 self << drop_sequence_sql(name) 34 end
Source
# File lib/sequel/adapters/shared/oracle.rb 40 def foreign_key_list(table, opts=OPTS) 41 m = output_identifier_meth 42 im = input_identifier_meth 43 schema, table = schema_and_table(table) 44 ds = metadata_dataset. 45 from{[all_cons_columns.as(:pc), all_constraints.as(:p), all_cons_columns.as(:fc), all_constraints.as(:f)]}. 46 where{{ 47 f[:table_name]=>im.call(table), 48 f[:constraint_type]=>'R', 49 p[:owner]=>f[:r_owner], 50 p[:constraint_name]=>f[:r_constraint_name], 51 pc[:owner]=>p[:owner], 52 pc[:constraint_name]=>p[:constraint_name], 53 pc[:table_name]=>p[:table_name], 54 fc[:owner]=>f[:owner], 55 fc[:constraint_name]=>f[:constraint_name], 56 fc[:table_name]=>f[:table_name], 57 fc[:position]=>pc[:position]}}. 58 select{[p[:table_name].as(:table), pc[:column_name].as(:key), fc[:column_name].as(:column), f[:constraint_name].as(:name)]}. 59 order{[:table, fc[:position]]} 60 ds = ds.where{{f[:schema_name]=>im.call(schema)}} if schema 61 62 fks = {} 63 ds.each do |r| 64 if fk = fks[r[:name]] 65 fk[:columns] << m.call(r[:column]) 66 fk[:key] << m.call(r[:key]) 67 else 68 fks[r[:name]] = {:name=>m.call(r[:name]), :columns=>[m.call(r[:column])], :table=>m.call(r[:table]), :key=>[m.call(r[:key])]} 69 end 70 end 71 fks.values 72 end
Source
# File lib/sequel/adapters/shared/oracle.rb 74 def freeze 75 current_user 76 server_version 77 @conversion_procs.freeze 78 super 79 end
Source
# File lib/sequel/adapters/shared/oracle.rb 82 def global_index_namespace? 83 false 84 end
Oracle
namespaces indexes per table.
Source
# File lib/sequel/adapters/shared/oracle.rb 122 def server_version(server=nil) 123 return @server_version if @server_version 124 @server_version = synchronize(server) do |conn| 125 (conn.server_version rescue nil) if conn.respond_to?(:server_version) 126 end 127 unless @server_version 128 @server_version = if m = /(\d+)\.(\d+)\.?(\d+)?\.?(\d+)?/.match(fetch("select version from PRODUCT_COMPONENT_VERSION where lower(product) like 'oracle%'").single_value) 129 (m[1].to_i*1000000) + (m[2].to_i*10000) + (m[3].to_i*100) + m[4].to_i 130 else 131 0 132 end 133 end 134 @server_version 135 end
The version of the Oracle
server, used for determining capability.
Source
# File lib/sequel/adapters/shared/oracle.rb 139 def supports_deferrable_constraints? 140 true 141 end
Oracle
supports deferrable constraints.
Source
# File lib/sequel/adapters/shared/oracle.rb 144 def supports_transaction_isolation_levels? 145 true 146 end
Oracle
supports transaction isolation levels.
Source
# File lib/sequel/adapters/shared/oracle.rb 88 def tables(opts=OPTS) 89 m = output_identifier_meth 90 metadata_dataset.from(:all_tables). 91 server(opts[:server]). 92 where(:dropped=>'NO'). 93 exclude(:owner=>IGNORE_OWNERS). 94 select(:table_name). 95 map{|r| m.call(r[:table_name])} 96 end
Source
# File lib/sequel/adapters/shared/oracle.rb 109 def view_exists?(name, opts=OPTS) 110 ds = metadata_dataset.from(:all_views).where(:view_name=>input_identifier_meth.call(name)) 111 112 if opts[:current_schema] 113 ds = ds.where(:owner=>Sequel.function(:SYS_CONTEXT, 'userenv', 'current_schema')) 114 else 115 ds = ds.exclude(:owner=>IGNORE_OWNERS) 116 end 117 118 ds.count > 0 119 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.
Source
# File lib/sequel/adapters/shared/oracle.rb 98 def views(opts=OPTS) 99 m = output_identifier_meth 100 metadata_dataset.from(:all_views). 101 server(opts[:server]). 102 exclude(:owner=>IGNORE_OWNERS). 103 select(:view_name). 104 map{|r| m.call(r[:view_name])} 105 end
Private Instance Methods
Source
# File lib/sequel/adapters/shared/oracle.rb 150 def alter_table_sql(table, op) 151 case op[:op] 152 when :add_column 153 if op[:primary_key] 154 sqls = [] 155 sqls << alter_table_sql(table, op.merge(:primary_key=>nil)) 156 if op[:auto_increment] 157 seq_name = default_sequence_name(table, op[:name]) 158 sqls << drop_sequence_sql(seq_name) 159 sqls << create_sequence_sql(seq_name, op) 160 sqls << "UPDATE #{quote_schema_table(table)} SET #{quote_identifier(op[:name])} = #{seq_name}.nextval" 161 end 162 sqls << "ALTER TABLE #{quote_schema_table(table)} ADD PRIMARY KEY (#{quote_identifier(op[:name])})" 163 sqls 164 else 165 "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}" 166 end 167 when :set_column_null 168 "ALTER TABLE #{quote_schema_table(table)} MODIFY #{quote_identifier(op[:name])} #{op[:null] ? 'NULL' : 'NOT NULL'}" 169 when :set_column_type 170 "ALTER TABLE #{quote_schema_table(table)} MODIFY #{quote_identifier(op[:name])} #{type_literal(op)}" 171 when :set_column_default 172 "ALTER TABLE #{quote_schema_table(table)} MODIFY #{quote_identifier(op[:name])} DEFAULT #{literal(op[:default])}" 173 else 174 super(table, op) 175 end 176 end
Source
# File lib/sequel/adapters/shared/oracle.rb 178 def auto_increment_sql 179 '' 180 end
Source
# File lib/sequel/adapters/shared/oracle.rb 185 def column_schema_integer_min_max_values(column) 186 super if column[:db_type] =~ /NUMBER\(\d+\)/i || (column[:db_type] == 'NUMBER' && column[:column_size].is_a?(Integer) && column[:scale] == 0) 187 end
Support min/max integer values on Oracle
only if they use a NUMBER column with a fixed precision and no scale.
Source
# File lib/sequel/adapters/shared/oracle.rb 189 def create_sequence_sql(name, opts=OPTS) 190 "CREATE SEQUENCE #{quote_identifier(name)} start with #{opts [:start_with]||1} increment by #{opts[:increment_by]||1} nomaxvalue" 191 end
Source
# File lib/sequel/adapters/shared/oracle.rb 193 def create_table_from_generator(name, generator, options) 194 drop_statement, create_statements = create_table_sql_list(name, generator, options) 195 swallow_database_error{execute_ddl(drop_statement)} if drop_statement 196 create_statements.each{|sql| execute_ddl(sql)} 197 end
Source
# File lib/sequel/adapters/shared/oracle.rb 199 def create_table_sql_list(name, generator, options=OPTS) 200 statements = [create_table_sql(name, generator, options)] 201 drop_seq_statement = nil 202 generator.columns.each do |c| 203 if c[:auto_increment] 204 c[:sequence_name] ||= default_sequence_name(name, c[:name]) 205 unless c[:create_sequence] == false 206 drop_seq_statement = drop_sequence_sql(c[:sequence_name]) 207 statements << create_sequence_sql(c[:sequence_name], c) 208 end 209 unless c[:create_trigger] == false 210 c[:trigger_name] ||= "BI_#{name}_#{c[:name]}" 211 trigger_definition = <<-end_sql 212 BEGIN 213 IF :NEW.#{quote_identifier(c[:name])} IS NULL THEN 214 SELECT #{c[:sequence_name]}.nextval INTO :NEW.#{quote_identifier(c[:name])} FROM dual; 215 END IF; 216 END; 217 end_sql 218 statements << create_trigger_sql(name, c[:trigger_name], trigger_definition, {:events => [:insert]}) 219 end 220 end 221 end 222 [drop_seq_statement, statements] 223 end
Source
# File lib/sequel/adapters/shared/oracle.rb 225 def create_trigger_sql(table, name, definition, opts=OPTS) 226 events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete] 227 sql = <<-end_sql 228 CREATE#{' OR REPLACE' if opts[:replace]} TRIGGER #{quote_identifier(name)} 229 #{opts[:after] ? 'AFTER' : 'BEFORE'} #{events.map{|e| e.to_s.upcase}.join(' OR ')} ON #{quote_schema_table(table)} 230 REFERENCING NEW AS NEW FOR EACH ROW 231 #{definition} 232 end_sql 233 sql 234 end
Source
# File lib/sequel/adapters/shared/oracle.rb 244 def database_error_regexps 245 DATABASE_ERROR_REGEXPS 246 end
Source
# File lib/sequel/adapters/shared/oracle.rb 248 def default_sequence_name(table, column) 249 "seq_#{table}_#{column}" 250 end
Source
# File lib/sequel/adapters/shared/oracle.rb 252 def drop_sequence_sql(name) 253 "DROP SEQUENCE #{quote_identifier(name)}" 254 end
Source
# File lib/sequel/adapters/shared/oracle.rb 256 def remove_cached_schema(table) 257 Sequel.synchronize{@primary_key_sequences.delete(table)} 258 super 259 end
Source
# File lib/sequel/adapters/shared/oracle.rb 271 def sequence_for_table(table) 272 return nil unless autosequence 273 Sequel.synchronize{return @primary_key_sequences[table] if @primary_key_sequences.has_key?(table)} 274 275 begin 276 sch = schema(table) 277 rescue Sequel::Error 278 return nil 279 end 280 281 pk = sch.select{|k, v| v[:primary_key]} 282 pks = if pk.length == 1 283 seq = "seq_#{table}_#{pk.first.first}" 284 seq.to_sym unless from(:user_sequences).where(:sequence_name=>input_identifier_meth.call(seq)).empty? 285 end 286 Sequel.synchronize{@primary_key_sequences[table] = pks} 287 end
Source
# File lib/sequel/adapters/shared/oracle.rb 267 def set_transaction_isolation_sql(level) 268 "SET TRANSACTION ISOLATION LEVEL #{TRANSACTION_ISOLATION_LEVELS[level]}" 269 end
Oracle
doesn’t support READ UNCOMMITTED OR REPEATABLE READ transaction isolation levels, so upgrade to the next highest level in those cases.
Source
# File lib/sequel/adapters/shared/oracle.rb 290 def supports_create_or_replace_view? 291 true 292 end
Oracle
supports CREATE OR REPLACE VIEW.
Source
# File lib/sequel/adapters/shared/oracle.rb 314 def temporary_table_sql 315 'GLOBAL TEMPORARY ' 316 end
SQL
fragment for showing a table is temporary
Source
# File lib/sequel/adapters/shared/oracle.rb 297 def type_literal_generic_bignum_symbol(column) 298 :integer 299 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.
Source
# File lib/sequel/adapters/shared/oracle.rb 303 def type_literal_generic_only_time(column) 304 :timestamp 305 end
Oracle
doesn’t have a time type, so use timestamp for all time columns.
Source
# File lib/sequel/adapters/shared/oracle.rb 309 def type_literal_generic_trueclass(column) 310 :'char(1)' 311 end
Oracle
doesn’t have a boolean type or even a reasonable facsimile. Using a char(1) seems to be the recommended way.
Source
# File lib/sequel/adapters/shared/oracle.rb 319 def uses_clob_for_text? 320 true 321 end
Oracle
uses clob for text types.
Source
# File lib/sequel/adapters/shared/oracle.rb 324 def view_with_check_option_support 325 true 326 end
Oracle
supports views with check option, but not local.