Module: Sequel::Oracle::DatasetMethods

Includes:

EmulateOffsetWithRowNumber

Included in:

JDBC::Oracle::Dataset, Dataset

Defined in:

lib/sequel/adapters/shared/oracle.rb

Constant Summary

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)

• - (Object) complex_expression_sql_append(sql, op, args)

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

• - (Object) constant_sql_append(sql, c)

  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.

• - (Boolean) empty?

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

• - (Object) except(dataset, opts = {})

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

• - (Boolean) recursive_cte_requires_column_aliases?

  Oracle requires recursive CTEs to have column aliases.

• - (Boolean) requires_sql_standard_datetimes?

  Oracle requires SQL standard datetimes.

• - (Object) select_sql

  Handle LIMIT by using a unlimited subselect filtered with ROWNUM.

• - (Object) sequence(s)

  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.

• - (Boolean) supports_group_cube?

  Oracle supports GROUP BY CUBE.

• - (Boolean) supports_group_rollup?

  Oracle supports GROUP BY ROLLUP.

• - (Boolean) supports_intersect_except_all?

  Oracle does not support INTERSECT ALL or EXCEPT ALL.

• - (Boolean) supports_is_true?

  Oracle does not support IS TRUE.

• - (Boolean) supports_select_all_and_column?

  Oracle does not support SELECT *, column.

• - (Boolean) supports_timestamp_timezones?

  Oracle supports timezones in literal timestamps.

• - (Boolean) supports_where_true?

  Oracle does not support WHERE 'Y' for WHERE TRUE.

• - (Boolean) supports_window_functions?

  Oracle supports window functions.

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

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

236
237
238
239
240
241
242

# File 'lib/sequel/adapters/shared/oracle.rb', line 236

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

253
254
255

# File 'lib/sequel/adapters/shared/oracle.rb', line 253

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

245
246
247
248
249

# File 'lib/sequel/adapters/shared/oracle.rb', line 245

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

285
286
287

# File 'lib/sequel/adapters/shared/oracle.rb', line 285

def recursive_cte_requires_column_aliases?
  true
end

258
259
260

# File 'lib/sequel/adapters/shared/oracle.rb', line 258

def requires_sql_standard_datetimes?
  true
end

270
271
272
273
274
275
276
277
278
279
280
281
282

# File 'lib/sequel/adapters/shared/oracle.rb', line 270

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

265
266
267

# File 'lib/sequel/adapters/shared/oracle.rb', line 265

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

290
291
292

# File 'lib/sequel/adapters/shared/oracle.rb', line 290

def supports_group_cube?
  true
end

295
296
297

# File 'lib/sequel/adapters/shared/oracle.rb', line 295

def supports_group_rollup?
  true
end

300
301
302

# File 'lib/sequel/adapters/shared/oracle.rb', line 300

def supports_intersect_except_all?
  false
end

305
306
307

# File 'lib/sequel/adapters/shared/oracle.rb', line 305

def supports_is_true?
  false
end

310
311
312

# File 'lib/sequel/adapters/shared/oracle.rb', line 310

def supports_select_all_and_column?
  false
end

315
316
317

# File 'lib/sequel/adapters/shared/oracle.rb', line 315

def supports_timestamp_timezones?
  true
end

320
321
322

# File 'lib/sequel/adapters/shared/oracle.rb', line 320

def supports_where_true?
  false
end

325
326
327

# File 'lib/sequel/adapters/shared/oracle.rb', line 325

def supports_window_functions?
  true
end