Module: JdbcSpec::Oracle

Defined in:
lib/jdbc_adapter/jdbc_oracle.rb

Defined Under Namespace

Modules: Column

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.adapter_matcher(name) ⇒ Object 



36
37
38
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 36

def self.adapter_matcher(name, *)
  name =~ /oracle/i ? self : false
end

.column_selector ⇒ Object 



40
41
42
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 40

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

.extended(mod) ⇒ Object 



12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 12

def self.extended(mod)
  unless @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]
          value = value.to_yaml if unserializable_attribute?(c.name, c)
          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
  ActiveRecord::Base.extend JdbcSpec::QuotedPrimaryKeyExtension
  mod.class.class_eval do
    alias_chained_method :insert, :query_dirty, :insert
    alias_chained_method :columns, :query_cache, :columns
  end
end

Instance Method Details

#_execute(sql, name = nil) ⇒ Object 



174
175
176
177
178
179
180
181
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 174

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 



112
113
114
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 112

def adapter_name
  'Oracle'
end

#add_column_options!(sql, options) ⇒ Object

:nodoc:



215
216
217
218
219
220
221
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 215

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:



193
194
195
196
197
198
199
200
201
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 193

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.



308
309
310
311
312
313
314
315
316
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 308

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:



223
224
225
226
227
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 223

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:



211
212
213
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 211

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

#columns(table_name, name = nil) ⇒ Object 



322
323
324
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 322

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

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

:nodoc:

Raises:

  • (ActiveRecord::StatementInvalid) 


124
125
126
127
128
129
130
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 124

def create_table(name, options = {}) #:nodoc:
  super(name, options)
  seq_name = options[:sequence_name] || "#{name}_seq"
  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:



203
204
205
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 203

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:



120
121
122
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 120

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")



292
293
294
295
296
297
298
299
300
301
302
303
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 292

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 



147
148
149
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 147

def drop_database(name)
  recreate_database(name)
end

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

:nodoc:



137
138
139
140
141
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 137

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

#indexes(table, name = nil) ⇒ Object 



170
171
172
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 170

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

#insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) ⇒ Object

:nodoc:



151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 151

def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) #:nodoc:
  if (id_value && !id_value.respond_to?(:to_sql)) || 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
  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 = select_one("select #{sequence_name}.nextval id from dual")['id'].to_i
    log(sql, name) do
      @connection.execute_id_insert(sql,id_value)
    end
  end
  id_value
end

#modify_types(tp) ⇒ Object 



183
184
185
186
187
188
189
190
191
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 183

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

#quote(value, column = nil) ⇒ Object

:nodoc:



350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 350

def quote(value, column = nil) #:nodoc:
  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) || value.acts_like?(:time)
      quoted = "#{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



342
343
344
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 342

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

#quote_string(string) ⇒ Object

:nodoc:



346
347
348
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 346

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.



332
333
334
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 332

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

#quoted_date(value) ⇒ Object 



369
370
371
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 369

def quoted_date(value)
  %Q{TIMESTAMP'#{super}'}
end

#quoted_false ⇒ Object

:nodoc:



377
378
379
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 377

def quoted_false #:nodoc:
  '0'
end

#quoted_true ⇒ Object

:nodoc:



373
374
375
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 373

def quoted_true #:nodoc:
  '1'
end

#recreate_database(name) ⇒ Object 



143
144
145
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 143

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

#remove_column(table_name, column_name) ⇒ Object

:nodoc:



233
234
235
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 233

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:



207
208
209
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 207

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:



229
230
231
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 229

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:



132
133
134
135
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 132

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

#structure_drop ⇒ Object

:nodoc:



272
273
274
275
276
277
278
279
280
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 272

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:



237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 237

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 



116
117
118
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 116

def table_alias_length
  30
end

#tables ⇒ Object 



318
319
320
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 318

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