Module: ArJdbc::Oracle

Defined in:
lib/arjdbc/oracle/adapter.rb

Defined Under Namespace

Modules: Column

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.arel2_visitors(config) ⇒ Object 



128
129
130
 
# File 'lib/arjdbc/oracle/adapter.rb', line 128

def self.arel2_visitors(config)
  { 'oracle' => Arel::Visitors::Oracle }
end

.column_selector ⇒ Object 



40
41
42
 
# File 'lib/arjdbc/oracle/adapter.rb', line 40

def self.column_selector
  [/oracle/i, lambda {|cfg,col| col.extend(::ArJdbc::Oracle::Column)}]
end

.extended(mod) ⇒ Object 



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
# File 'lib/arjdbc/oracle/adapter.rb', line 7

def self.extended(mod)
  unless defined?(@lob_callback_added)
    ActiveRecord::Base.class_eval do
      def after_save_with_oracle_lob
        self.class.columns.select { |c| c.sql_type =~ /LOB\(|LOB$/i }.each do |c|
          value = self[c.name]
          if respond_to?(:unserializable_attribute?)
            value = value.to_yaml if unserializable_attribute?(c.name, c)
          else
            value = value.to_yaml if value.is_a?(Hash)
          end
          next if value.nil?  || (value == '')

          connection.write_large_object(c.type == :binary, c.name, self.class.table_name, self.class.primary_key, quote_value(id), value)
        end
      end
    end

    ActiveRecord::Base.after_save :after_save_with_oracle_lob
    @lob_callback_added = true
  end

  unless ActiveRecord::ConnectionAdapters::AbstractAdapter.instance_methods(false).detect {|m| m.to_s == "prefetch_primary_key?"}
    require 'arjdbc/jdbc/quoted_primary_key'
    ActiveRecord::Base.extend ArJdbc::QuotedPrimaryKeyExtension
  end

  (class << mod; self; end).class_eval do
    alias_chained_method :insert, :query_dirty, :ora_insert
    alias_chained_method :columns, :query_cache, :ora_columns
  end
end

.jdbc_connection_class ⇒ Object 



44
45
46
 
# File 'lib/arjdbc/oracle/adapter.rb', line 44

def self.jdbc_connection_class
  ::ActiveRecord::ConnectionAdapters::OracleJdbcConnection
end

Instance Method Details

#_execute(sql, name = nil) ⇒ Object 



203
204
205
206
207
208
209
210
 
# File 'lib/arjdbc/oracle/adapter.rb', line 203

def _execute(sql, name = nil)
  case sql.strip
    when /\A\(?\s*(select|show)/i then
      @connection.execute_query(sql)
    else
      @connection.execute_update(sql)
    end
end

#adapter_name ⇒ Object 



124
125
126
 
# File 'lib/arjdbc/oracle/adapter.rb', line 124

def adapter_name
  'Oracle'
end

#add_column_options!(sql, options) ⇒ Object

:nodoc:



244
245
246
247
248
249
250
 
# File 'lib/arjdbc/oracle/adapter.rb', line 244

def add_column_options!(sql, options) #:nodoc:
  # handle case  of defaults for CLOB columns, which would otherwise get "quoted" incorrectly
  if options_include_default?(options) && (column = options[:column]) && column.type == :text
    sql << " DEFAULT #{quote(options.delete(:default))}"
  end
  super
end

#add_limit_offset!(sql, options) ⇒ Object

:nodoc:



222
223
224
225
226
227
228
229
230
 
# File 'lib/arjdbc/oracle/adapter.rb', line 222

def add_limit_offset!(sql, options) #:nodoc:
  offset = options[:offset] || 0

  if limit = options[:limit]
    sql.replace "select * from (select raw_sql_.*, rownum raw_rnum_ from (#{sql}) raw_sql_ where rownum <= #{offset+limit}) where raw_rnum_ > #{offset}"
  elsif offset > 0
    sql.replace "select * from (select raw_sql_.*, rownum raw_rnum_ from (#{sql}) raw_sql_) where raw_rnum_ > #{offset}"
  end
end

#add_order_by_for_association_limiting!(sql, options) ⇒ Object

ORDER BY clause for the passed order option.

Uses column aliases as defined by #distinct.



337
338
339
340
341
342
343
344
345
 
# File 'lib/arjdbc/oracle/adapter.rb', line 337

def add_order_by_for_association_limiting!(sql, options)
  return sql if options[:order].blank?

  order = options[:order].split(',').collect { |s| s.strip }.reject(&:blank?)
  order.map! {|s| $1 if s =~ / (.*)/}
  order = order.zip((0...order.size).to_a).map { |s,i| "alias_#{i}__ #{s}" }.join(', ')

  sql << "ORDER BY #{order}"
end

#change_column(table_name, column_name, type, options = {}) ⇒ Object

:nodoc:



252
253
254
255
256
 
# File 'lib/arjdbc/oracle/adapter.rb', line 252

def change_column(table_name, column_name, type, options = {}) #:nodoc:
  change_column_sql = "ALTER TABLE #{table_name} MODIFY #{column_name} #{type_to_sql(type, options[:limit])}"
  add_column_options!(change_column_sql, options)
  execute(change_column_sql)
end

#change_column_default(table_name, column_name, default) ⇒ Object

:nodoc:



240
241
242
 
# File 'lib/arjdbc/oracle/adapter.rb', line 240

def change_column_default(table_name, column_name, default) #:nodoc:
  execute "ALTER TABLE #{table_name} MODIFY #{column_name} DEFAULT #{quote(default)}"
end

#create_table(name, options = {}) ⇒ Object

:nodoc:

Raises:

  • (ActiveRecord::StatementInvalid) 


144
145
146
147
148
149
150
 
# File 'lib/arjdbc/oracle/adapter.rb', line 144

def create_table(name, options = {}) #:nodoc:
  super(name, options)
  seq_name = options[:sequence_name] || default_sequence_name(name)
  start_value = options[:sequence_start_value] || 10000
  raise ActiveRecord::StatementInvalid.new("name #{seq_name} too long") if seq_name.length > table_alias_length
  execute "CREATE SEQUENCE #{seq_name} START WITH #{start_value}" unless options[:id] == false
end

#current_database ⇒ Object

:nodoc:



232
233
234
 
# File 'lib/arjdbc/oracle/adapter.rb', line 232

def current_database #:nodoc:
  select_one("select sys_context('userenv','db_name') db from dual")["db"]
end

#default_sequence_name(table, column = nil) ⇒ Object

:nodoc:



140
141
142
 
# File 'lib/arjdbc/oracle/adapter.rb', line 140

def default_sequence_name(table, column = nil) #:nodoc:
  "#{table}_seq"
end

#distinct(columns, order_by) ⇒ Object

SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause.

Oracle requires the ORDER BY columns to be in the SELECT list for DISTINCT queries. However, with those columns included in the SELECT DISTINCT list, you won’t actually get a distinct list of the column you want (presuming the column has duplicates with multiple values for the ordered-by columns. So we use the FIRST_VALUE function to get a single (first) value for each column, effectively making every row the same.

distinct("posts.id", "posts.created_at desc")



321
322
323
324
325
326
327
328
329
330
331
332
 
# File 'lib/arjdbc/oracle/adapter.rb', line 321

def distinct(columns, order_by)
  return "DISTINCT #{columns}" if order_by.blank?

  # construct a valid DISTINCT clause, ie. one that includes the ORDER BY columns, using
  # FIRST_VALUE such that the inclusion of these columns doesn't invalidate the DISTINCT
  order_columns = order_by.split(',').map { |s| s.strip }.reject(&:blank?)
  order_columns = order_columns.zip((0...order_columns.size).to_a).map do |c, i|
    "FIRST_VALUE(#{c.split.first}) OVER (PARTITION BY #{columns} ORDER BY #{c}) AS alias_#{i}__"
  end
  sql = "DISTINCT #{columns}, "
  sql << order_columns * ", "
end

#drop_database(name) ⇒ Object 



167
168
169
 
# File 'lib/arjdbc/oracle/adapter.rb', line 167

def drop_database(name)
  recreate_database(name)
end

#drop_table(name, options = {}) ⇒ Object

:nodoc:



157
158
159
160
161
 
# File 'lib/arjdbc/oracle/adapter.rb', line 157

def drop_table(name, options = {}) #:nodoc:
  super(name) rescue nil
  seq_name = options[:sequence_name] || default_sequence_name(name)
  execute "DROP SEQUENCE #{seq_name}" rescue nil
end

#indexes(table, name = nil) ⇒ Object 



199
200
201
 
# File 'lib/arjdbc/oracle/adapter.rb', line 199

def indexes(table, name = nil)
  @connection.indexes(table, name, @connection.connection.meta_data.user_name)
end

#modify_types(tp) ⇒ Object 



212
213
214
215
216
217
218
219
220
 
# File 'lib/arjdbc/oracle/adapter.rb', line 212

def modify_types(tp)
  tp[:primary_key] = "NUMBER(38) NOT NULL PRIMARY KEY"
  tp[:integer] = { :name => "NUMBER", :limit => 38 }
  tp[:datetime] = { :name => "DATE" }
  tp[:timestamp] = { :name => "DATE" }
  tp[:time] = { :name => "DATE" }
  tp[:date] = { :name => "DATE" }
  tp
end

#next_sequence_value(sequence_name) ⇒ Object 



171
172
173
174
 
# File 'lib/arjdbc/oracle/adapter.rb', line 171

def next_sequence_value(sequence_name)
  # avoid #select or #select_one so that the sequence values aren't cached
  execute("select #{sequence_name}.nextval id from dual").first['id'].to_i
end

#ora_columns(table_name, name = nil) ⇒ Object 



351
352
353
 
# File 'lib/arjdbc/oracle/adapter.rb', line 351

def ora_columns(table_name, name=nil)
  @connection.columns_internal(table_name, name, oracle_schema)
end

#ora_insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil, binds = []) ⇒ Object

:nodoc:



180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
 
# File 'lib/arjdbc/oracle/adapter.rb', line 180

def ora_insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil, binds = []) #:nodoc:
  if (id_value && !sql_literal?(id_value)) || pk.nil?
    # Pre-assigned id or table without a primary key
    # Presence of #to_sql means an Arel literal bind variable
    # that should use #execute_id_insert below
    execute sql, name, binds
  else
    # Assume the sql contains a bind-variable for the id
    # Extract the table from the insert sql. Yuck.
    table = sql.split(" ", 4)[2].gsub('"', '')
    sequence_name ||= default_sequence_name(table)
    id_value = next_sequence_value(sequence_name)
    log(sql, name) do
      @connection.execute_id_insert(sql,id_value)
    end
  end
  id_value
end

#prefetch_primary_key?(table_name = nil) ⇒ Boolean

Returns:

  • (Boolean) 


132
133
134
 
# File 'lib/arjdbc/oracle/adapter.rb', line 132

def prefetch_primary_key?(table_name = nil)
  columns(table_name).detect {|c| c.primary } if table_name
end

#quote(value, column = nil) ⇒ Object

:nodoc:



379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
 
# File 'lib/arjdbc/oracle/adapter.rb', line 379

def quote(value, column = nil) #:nodoc:
  # Arel 2 passes SqlLiterals through
  return value if sql_literal?(value)

  if column && [:text, :binary].include?(column.type)
    if /(.*?)\([0-9]+\)/ =~ column.sql_type
      %Q{empty_#{ $1.downcase }()}
    else
      %Q{empty_#{ column.sql_type.downcase rescue 'blob' }()}
    end
  else
    if column.respond_to?(:primary) && column.primary && column.klass != String
      return value.to_i.to_s
    end
    quoted = super
    if value.acts_like?(:date)
      quoted = %Q{DATE'#{quoted_date(value)}'}
    elsif value.acts_like?(:time)
      quoted = %Q{TIMESTAMP'#{quoted_date(value)}'}
    end
    quoted
  end
end

#quote_column_name(name) ⇒ Object

Camelcase column names need to be quoted. Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and “at” signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers. Source: download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements008.htm



371
372
373
 
# File 'lib/arjdbc/oracle/adapter.rb', line 371

def quote_column_name(name) #:nodoc:
  name.to_s =~ /^[a-z0-9_$#]+$/ ? name.to_s : "\"#{name}\""
end

#quote_string(string) ⇒ Object

:nodoc:



375
376
377
 
# File 'lib/arjdbc/oracle/adapter.rb', line 375

def quote_string(string) #:nodoc:
  string.gsub(/'/, "''")
end

#quote_table_name(name) ⇒ Object

See ACTIVERECORD_JDBC-33 for details – better to not quote table names, esp. if they have schemas.



361
362
363
 
# File 'lib/arjdbc/oracle/adapter.rb', line 361

def quote_table_name(name) #:nodoc:
  name.to_s
end

#quoted_false ⇒ Object

:nodoc:



407
408
409
 
# File 'lib/arjdbc/oracle/adapter.rb', line 407

def quoted_false #:nodoc:
  '0'
end

#quoted_true ⇒ Object

:nodoc:



403
404
405
 
# File 'lib/arjdbc/oracle/adapter.rb', line 403

def quoted_true #:nodoc:
  '1'
end

#recreate_database(name) ⇒ Object 



163
164
165
 
# File 'lib/arjdbc/oracle/adapter.rb', line 163

def recreate_database(name)
  tables.each{ |table| drop_table(table) }
end

#remove_column(table_name, column_name) ⇒ Object

:nodoc:



262
263
264
 
# File 'lib/arjdbc/oracle/adapter.rb', line 262

def remove_column(table_name, column_name) #:nodoc:
  execute "ALTER TABLE #{table_name} DROP COLUMN #{column_name}"
end

#remove_index(table_name, options = {}) ⇒ Object

:nodoc:



236
237
238
 
# File 'lib/arjdbc/oracle/adapter.rb', line 236

def remove_index(table_name, options = {}) #:nodoc:
  execute "DROP INDEX #{index_name(table_name, options)}"
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object

:nodoc:



258
259
260
 
# File 'lib/arjdbc/oracle/adapter.rb', line 258

def rename_column(table_name, column_name, new_column_name) #:nodoc:
  execute "ALTER TABLE #{table_name} RENAME COLUMN #{column_name} to #{new_column_name}"
end

#rename_table(name, new_name) ⇒ Object

:nodoc:



152
153
154
155
 
# File 'lib/arjdbc/oracle/adapter.rb', line 152

def rename_table(name, new_name) #:nodoc:
  execute "RENAME #{name} TO #{new_name}"
  execute "RENAME #{name}_seq TO #{new_name}_seq" rescue nil
end

#sql_literal?(value) ⇒ Boolean

Returns:

  • (Boolean) 


176
177
178
 
# File 'lib/arjdbc/oracle/adapter.rb', line 176

def sql_literal?(value)
  defined?(::Arel::SqlLiteral) && ::Arel::SqlLiteral === value
end

#structure_drop ⇒ Object

:nodoc:



301
302
303
304
305
306
307
308
309
 
# File 'lib/arjdbc/oracle/adapter.rb', line 301

def structure_drop #:nodoc:
  s = select_all("select sequence_name from user_sequences").inject("") do |drop, seq|
    drop << "drop sequence #{seq.to_a.first.last};\n\n"
  end

  select_all("select table_name from user_tables").inject(s) do |drop, table|
    drop << "drop table #{table.to_a.first.last} cascade constraints;\n\n"
  end
end

#structure_dump ⇒ Object

:nodoc:



266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
 
# File 'lib/arjdbc/oracle/adapter.rb', line 266

def structure_dump #:nodoc:
  s = select_all("select sequence_name from user_sequences").inject("") do |structure, seq|
    structure << "create sequence #{seq.to_a.first.last};\n\n"
  end

  select_all("select table_name from user_tables").inject(s) do |structure, table|
    ddl = "create table #{table.to_a.first.last} (\n "
    cols = select_all(%Q{
          select column_name, data_type, data_length, data_precision, data_scale, data_default, nullable
          from user_tab_columns
          where table_name = '#{table.to_a.first.last}'
          order by column_id
        }).map do |row|
      row = row.inject({}) do |h,args|
        h[args[0].downcase] = args[1]
        h
      end
      col = "#{row['column_name'].downcase} #{row['data_type'].downcase}"
      if row['data_type'] =='NUMBER' and !row['data_precision'].nil?
        col << "(#{row['data_precision'].to_i}"
        col << ",#{row['data_scale'].to_i}" if !row['data_scale'].nil?
        col << ')'
      elsif row['data_type'].include?('CHAR')
        col << "(#{row['data_length'].to_i})"
      end
      col << " default #{row['data_default']}" if !row['data_default'].nil?
      col << ' not null' if row['nullable'] == 'N'
      col
    end
    ddl << cols.join(",\n ")
    ddl << ");\n\n"
    structure << ddl
  end
end

#table_alias_length ⇒ Object 



136
137
138
 
# File 'lib/arjdbc/oracle/adapter.rb', line 136

def table_alias_length
  30
end

#tables ⇒ Object 



347
348
349
 
# File 'lib/arjdbc/oracle/adapter.rb', line 347

def tables
  @connection.tables(nil, oracle_schema)
end