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 



34
35
36
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 34

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

.column_selector ⇒ Object 



38
39
40
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 38

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
 
# 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
  mod.class_eval do
    alias_chained_method :insert, :query_dirty, :jdbc_oracle_insert
  end
end

Instance Method Details

#_execute(sql, name = nil) ⇒ Object 



160
161
162
163
164
165
166
167
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 160

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 



103
104
105
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 103

def adapter_name
  'oracle'
end

#add_column_options!(sql, options) ⇒ Object

:nodoc:



201
202
203
204
205
206
207
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 201

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:



179
180
181
182
183
184
185
186
187
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 179

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.



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

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:



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

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:



197
198
199
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 197

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 



308
309
310
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 308

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

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

:nodoc:

Raises:

  • (ActiveRecord::StatementInvalid) 


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

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

#current_database ⇒ Object

:nodoc:



189
190
191
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 189

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:



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

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



278
279
280
281
282
283
284
285
286
287
288
289
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 278

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 



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

def drop_database(name)
  recreate_database(name)
end

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

:nodoc:



127
128
129
130
131
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 127

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 



156
157
158
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 156

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

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

:nodoc:



141
142
143
144
145
146
147
148
149
150
151
152
153
154
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 141

def jdbc_oracle_insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) #:nodoc:
  if id_value || pk.nil? # Pre-assigned id or table without a primary key
    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 



169
170
171
172
173
174
175
176
177
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 169

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:



330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 330

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



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

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

#quote_string(string) ⇒ Object

:nodoc:



326
327
328
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 326

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

#quoted_date(value) ⇒ Object 



349
350
351
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 349

def quoted_date(value)
  %Q{TIMESTAMP'#{value.strftime("%Y-%m-%d %H:%M:%S")}'}
end

#quoted_false ⇒ Object

:nodoc:



357
358
359
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 357

def quoted_false #:nodoc:
  '0'
end

#quoted_true ⇒ Object

:nodoc:



353
354
355
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 353

def quoted_true #:nodoc:
  '1'
end

#recreate_database(name) ⇒ Object 



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

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

#remove_column(table_name, column_name) ⇒ Object

:nodoc:



219
220
221
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 219

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:



193
194
195
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 193

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:



215
216
217
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 215

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:



122
123
124
125
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 122

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:



258
259
260
261
262
263
264
265
266
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 258

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:



223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 223

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 



107
108
109
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 107

def table_alias_length
  30
end

#tables ⇒ Object 



304
305
306
 
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 304

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