Module: Sequel::Oracle::DatasetMethods

Includes:
EmulateOffsetWithRowNumber
Included in:
JDBC::Oracle::Dataset, Dataset
Defined in:
lib/sequel/adapters/shared/oracle.rb

Constant Summary collapse

SELECT_CLAUSE_METHODS = 
Dataset.clause_methods(:select, %w'with select distinct columns from join where group having compounds order lock')
ROW_NUMBER_EXPRESSION = 
LiteralString.new('ROWNUM').freeze
SPACE = 
Dataset::SPACE
APOS = 
Dataset::APOS
APOS_RE = 
Dataset::APOS_RE
DOUBLE_APOS = 
Dataset::DOUBLE_APOS
FROM = 
Dataset::FROM
BITCOMP_OPEN = 
"((0 - ".freeze
BITCOMP_CLOSE = 
") - 1)".freeze
ILIKE_0 = 
"(UPPER(".freeze
ILIKE_1 = 
") ".freeze
ILIKE_2 = 
' UPPER('.freeze
ILIKE_3 = 
"))".freeze
LIKE = 
'LIKE'.freeze
NOT_LIKE = 
'NOT LIKE'.freeze
TIMESTAMP_FORMAT = 
"TIMESTAMP '%Y-%m-%d %H:%M:%S%N %z'".freeze
TIMESTAMP_OFFSET_FORMAT = 
"%+03i:%02i".freeze
BOOL_FALSE = 
"'N'".freeze
BOOL_TRUE = 
"'Y'".freeze
HSTAR = 
"H*".freeze
DUAL = 
['DUAL'.freeze].freeze

Instance Method Summary collapse

Instance Method Details

#complex_expression_sql_append(sql, op, args) ⇒ Object

Oracle needs to emulate bitwise operators and ILIKE/NOT ILIKE operators.



204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 204

def complex_expression_sql_append(sql, op, args)
  case op
  when :&
    sql << complex_expression_arg_pairs(args){|a, b| "CAST(BITAND(#{literal(a)}, #{literal(b)}) AS INTEGER)"}
  when :|
    sql << complex_expression_arg_pairs(args){|a, b| "(#{literal(a)} - #{complex_expression_sql(:&, [a, b])} + #{literal(b)})"}
  when :^
    sql << complex_expression_arg_pairs(args){|*x| "(#{complex_expression_sql(:|, x)} - #{complex_expression_sql(:&, x)})"}
  when :'B~'
    sql << BITCOMP_OPEN
    literal_append(sql, args.at(0))
    sql << BITCOMP_CLOSE
  when :<<
    sql << complex_expression_arg_pairs(args){|a, b| "(#{literal(a)} * power(2, #{literal b}))"}
  when :>>
    sql << complex_expression_arg_pairs(args){|a, b| "(#{literal(a)} / power(2, #{literal b}))"}
  when :%
    sql << complex_expression_arg_pairs(args){|a, b| "MOD(#{literal(a)}, #{literal(b)})"}
  when :ILIKE, :'NOT ILIKE'
    sql << ILIKE_0
    literal_append(sql, args.at(0))
    sql << ILIKE_1
    sql << (op == :ILIKE ? LIKE : NOT_LIKE)
    sql<< ILIKE_2
    literal_append(sql, args.at(1))
    sql << ILIKE_3
  else
    super
  end
end

#constant_sql_append(sql, c) ⇒ Object

Oracle doesn’t support CURRENT_TIME, as it doesn’t have a type for storing just time values without a date, so use CURRENT_TIMESTAMP in its place.



238
239
240
241
242
243
244
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 238

def constant_sql_append(sql, c)
  if c == :CURRENT_TIME
    super(sql, :CURRENT_TIMESTAMP)
  else
    super
  end
end

#empty? ⇒ Boolean

Use a custom expression with EXISTS to determine whether a dataset is empty.

Returns:

  • (Boolean) 


255
256
257
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 255

def empty?
  db[:dual].where(@opts[:offset] ? exists : unordered.exists).get(1) == nil
end

#except(dataset, opts = {}) ⇒ Object

Oracle uses MINUS instead of EXCEPT, and doesn’t support EXCEPT ALL

Raises:



247
248
249
250
251
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 247

def except(dataset, opts={})
  opts = {:all=>opts} unless opts.is_a?(Hash)
  raise(Sequel::Error, "EXCEPT ALL not supported") if opts[:all]
  compound_clone(:minus, dataset, opts)
end

#recursive_cte_requires_column_aliases? ⇒ Boolean

Oracle requires recursive CTEs to have column aliases.

Returns:

  • (Boolean) 


287
288
289
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 287

def recursive_cte_requires_column_aliases?
  true
end

#requires_sql_standard_datetimes? ⇒ Boolean

Oracle requires SQL standard datetimes

Returns:

  • (Boolean) 


260
261
262
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 260

def requires_sql_standard_datetimes?
  true
end

#select_sql ⇒ Object

Handle LIMIT by using a unlimited subselect filtered with ROWNUM.



272
273
274
275
276
277
278
279
280
281
282
283
284
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 272

def select_sql
  if (limit = @opts[:limit]) && !@opts[:sql]
    ds = clone(:limit=>nil)
    # Lock doesn't work in subselects, so don't use a subselect when locking.
    # Don't use a subselect if custom SQL is used, as it breaks somethings.
    ds = ds.from_self unless @opts[:lock]
    sql = @opts[:append_sql] || ''
    subselect_sql_append(sql, ds.where(SQL::ComplexExpression.new(:<=, ROW_NUMBER_EXPRESSION, limit)))
    sql
  else
    super
  end
end

#sequence(s) ⇒ Object

Create a copy of this dataset associated to the given sequence name, which will be used when calling insert to find the most recently inserted value for the sequence.



267
268
269
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 267

def sequence(s)
  clone(:sequence=>s)
end

#supports_group_cube? ⇒ Boolean

Oracle supports GROUP BY CUBE

Returns:

  • (Boolean) 


292
293
294
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 292

def supports_group_cube?
  true
end

#supports_group_rollup? ⇒ Boolean

Oracle supports GROUP BY ROLLUP

Returns:

  • (Boolean) 


297
298
299
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 297

def supports_group_rollup?
  true
end

#supports_intersect_except_all? ⇒ Boolean

Oracle does not support INTERSECT ALL or EXCEPT ALL

Returns:

  • (Boolean) 


302
303
304
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 302

def supports_intersect_except_all?
  false
end

#supports_is_true? ⇒ Boolean

Oracle does not support IS TRUE.

Returns:

  • (Boolean) 


307
308
309
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 307

def supports_is_true?
  false
end

#supports_select_all_and_column? ⇒ Boolean

Oracle does not support SELECT *, column

Returns:

  • (Boolean) 


312
313
314
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 312

def supports_select_all_and_column?
  false
end

#supports_timestamp_timezones? ⇒ Boolean

Oracle supports timezones in literal timestamps.

Returns:

  • (Boolean) 


317
318
319
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 317

def supports_timestamp_timezones?
  true
end

#supports_where_true? ⇒ Boolean

Oracle does not support WHERE ‘Y’ for WHERE TRUE.

Returns:

  • (Boolean) 


322
323
324
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 322

def supports_where_true?
  false
end

#supports_window_functions? ⇒ Boolean

Oracle supports window functions

Returns:

  • (Boolean) 


327
328
329
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 327

def supports_window_functions?
  true
end