Module: Sequel::Oracle::DatasetMethods
- Included in:
- JDBC::Oracle::Dataset, Dataset
- Defined in:
- lib/sequel/adapters/shared/oracle.rb
Constant Summary collapse
- ROW_NUMBER_EXPRESSION =
LiteralString.new('ROWNUM').freeze
- BITAND_PROC =
lambda{|a, b| Sequel.lit(["CAST(BITAND(", ", ", ") AS INTEGER)"], a, b)}
Instance Method Summary collapse
- #complex_expression_sql_append(sql, op, args) ⇒ Object
-
#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.
-
#empty? ⇒ Boolean
Use a custom expression with EXISTS to determine whether a dataset is empty.
-
#except(dataset, opts = OPTS) ⇒ Object
Oracle uses MINUS instead of EXCEPT, and doesn’t support EXCEPT ALL.
-
#recursive_cte_requires_column_aliases? ⇒ Boolean
Oracle requires recursive CTEs to have column aliases.
-
#requires_sql_standard_datetimes? ⇒ Boolean
Oracle requires SQL standard datetimes.
-
#select_sql ⇒ Object
Handle LIMIT by using a unlimited subselect filtered with ROWNUM, unless Oracle 12 is used.
-
#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.
-
#server_version ⇒ Object
The version of the database server.
- #supports_cte?(type = :select) ⇒ Boolean
-
#supports_derived_column_lists? ⇒ Boolean
Oracle does not support derived column lists.
-
#supports_fetch_next_rows? ⇒ Boolean
Oracle supports FETCH NEXT ROWS since 12c, but it doesn’t work when locking or when skipping locked rows.
-
#supports_group_cube? ⇒ Boolean
Oracle supports GROUP BY CUBE.
-
#supports_group_rollup? ⇒ Boolean
Oracle supports GROUP BY ROLLUP.
-
#supports_grouping_sets? ⇒ Boolean
Oracle supports GROUPING SETS.
-
#supports_intersect_except_all? ⇒ Boolean
Oracle does not support INTERSECT ALL or EXCEPT ALL.
-
#supports_is_true? ⇒ Boolean
Oracle does not support IS TRUE.
-
#supports_limits_in_correlated_subqueries? ⇒ Boolean
Oracle does not support limits in correlated subqueries.
-
#supports_merge? ⇒ Boolean
Oracle supports MERGE.
-
#supports_nowait? ⇒ Boolean
Oracle supports NOWAIT.
-
#supports_offsets_in_correlated_subqueries? ⇒ Boolean
Oracle does not support offsets in correlated subqueries.
-
#supports_regexp? ⇒ Boolean
Oracle 10+ supports pattern matching via regular expressions.
-
#supports_select_all_and_column? ⇒ Boolean
Oracle does not support SELECT *, column.
-
#supports_skip_locked? ⇒ Boolean
Oracle supports SKIP LOCKED.
-
#supports_timestamp_timezones? ⇒ Boolean
Oracle supports timezones in literal timestamps.
-
#supports_where_true? ⇒ Boolean
Oracle does not support WHERE ‘Y’ for WHERE TRUE.
-
#supports_window_functions? ⇒ Boolean
Oracle supports window functions.
Instance Method Details
#complex_expression_sql_append(sql, op, args) ⇒ Object
336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 336 def complex_expression_sql_append(sql, op, args) case op when :& complex_expression_arg_pairs_append(sql, args, &BITAND_PROC) when :| complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["(", " - ", " + ", ")"], a, complex_expression_arg_pairs([a, b], &BITAND_PROC), b)} when :^ complex_expression_arg_pairs_append(sql, args) do |*x| s1 = complex_expression_arg_pairs(x){|a, b| Sequel.lit(["(", " - ", " + ", ")"], a, complex_expression_arg_pairs([a, b], &BITAND_PROC), b)} s2 = complex_expression_arg_pairs(x, &BITAND_PROC) Sequel.lit(["(", " - ", ")"], s1, s2) end when :~, :'!~', :'~*', :'!~*' raise InvalidOperation, "Pattern matching via regular expressions is not supported in this Oracle version" unless supports_regexp? if op == :'!~' || op == :'!~*' sql << 'NOT ' end sql << 'REGEXP_LIKE(' literal_append(sql, args[0]) sql << ',' literal_append(sql, args[1]) if op == :'~*' || op == :'!~*' sql << ", 'i'" end sql << ')' when :%, :<<, :>>, :'B~' complex_expression_emulate_append(sql, op, args) 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.
371 372 373 374 375 376 377 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 371 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.
387 388 389 390 391 392 393 394 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 387 def empty? if @opts[:sql] naked.each{return false} true else db[:dual].where(@opts[:offset] ? exists : unordered.exists).get(1) == nil end end |
#except(dataset, opts = OPTS) ⇒ Object
Oracle uses MINUS instead of EXCEPT, and doesn’t support EXCEPT ALL
380 381 382 383 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 380 def except(dataset, opts=OPTS) 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.
444 445 446 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 444 def recursive_cte_requires_column_aliases? true end |
#requires_sql_standard_datetimes? ⇒ Boolean
Oracle requires SQL standard datetimes
397 398 399 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 397 def requires_sql_standard_datetimes? true end |
#select_sql ⇒ Object
Handle LIMIT by using a unlimited subselect filtered with ROWNUM, unless Oracle 12 is used.
410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 410 def select_sql return super if @opts[:sql] return super if supports_fetch_next_rows? o = @opts[:offset] if o && o != 0 columns = clone(:append_sql=>String.new, :placeholder_literal_null=>true).columns dsa1 = dataset_alias(1) rn = row_number_column limit = @opts[:limit] ds = unlimited. from_self(:alias=>dsa1). select_append(ROW_NUMBER_EXPRESSION.as(rn)). from_self(:alias=>dsa1). select(*columns). where(SQL::Identifier.new(rn) > o) ds = ds.where(SQL::Identifier.new(rn) <= Sequel.+(o, limit)) if limit sql = @opts[:append_sql] || String.new subselect_sql_append(sql, ds) sql elsif limit = @opts[:limit] ds = unlimited # 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] || String.new 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.
404 405 406 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 404 def sequence(s) clone(:sequence=>s) end |
#server_version ⇒ Object
The version of the database server
534 535 536 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 534 def server_version db.server_version(@opts[:server]) end |
#supports_cte?(type = :select) ⇒ Boolean
448 449 450 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 448 def supports_cte?(type=:select) type == :select end |
#supports_derived_column_lists? ⇒ Boolean
Oracle does not support derived column lists
453 454 455 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 453 def supports_derived_column_lists? false end |
#supports_fetch_next_rows? ⇒ Boolean
Oracle supports FETCH NEXT ROWS since 12c, but it doesn’t work when locking or when skipping locked rows.
459 460 461 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 459 def supports_fetch_next_rows? server_version >= 12000000 && !(@opts[:lock] || @opts[:skip_locked]) end |
#supports_group_cube? ⇒ Boolean
Oracle supports GROUP BY CUBE
464 465 466 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 464 def supports_group_cube? true end |
#supports_group_rollup? ⇒ Boolean
Oracle supports GROUP BY ROLLUP
469 470 471 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 469 def supports_group_rollup? true end |
#supports_grouping_sets? ⇒ Boolean
Oracle supports GROUPING SETS
474 475 476 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 474 def supports_grouping_sets? true end |
#supports_intersect_except_all? ⇒ Boolean
Oracle does not support INTERSECT ALL or EXCEPT ALL
479 480 481 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 479 def supports_intersect_except_all? false end |
#supports_is_true? ⇒ Boolean
Oracle does not support IS TRUE.
484 485 486 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 484 def supports_is_true? false end |
#supports_limits_in_correlated_subqueries? ⇒ Boolean
Oracle does not support limits in correlated subqueries.
489 490 491 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 489 def false end |
#supports_merge? ⇒ Boolean
Oracle supports MERGE
494 495 496 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 494 def supports_merge? true end |
#supports_nowait? ⇒ Boolean
Oracle supports NOWAIT.
499 500 501 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 499 def supports_nowait? true end |
#supports_offsets_in_correlated_subqueries? ⇒ Boolean
Oracle does not support offsets in correlated subqueries.
504 505 506 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 504 def false end |
#supports_regexp? ⇒ Boolean
Oracle 10+ supports pattern matching via regular expressions
539 540 541 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 539 def supports_regexp? server_version >= 10010002 end |
#supports_select_all_and_column? ⇒ Boolean
Oracle does not support SELECT *, column
509 510 511 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 509 def supports_select_all_and_column? false end |
#supports_skip_locked? ⇒ Boolean
Oracle supports SKIP LOCKED.
514 515 516 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 514 def supports_skip_locked? true end |
#supports_timestamp_timezones? ⇒ Boolean
Oracle supports timezones in literal timestamps.
519 520 521 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 519 def true end |
#supports_where_true? ⇒ Boolean
Oracle does not support WHERE ‘Y’ for WHERE TRUE.
524 525 526 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 524 def supports_where_true? false end |
#supports_window_functions? ⇒ Boolean
Oracle supports window functions
529 530 531 |
# File 'lib/sequel/adapters/shared/oracle.rb', line 529 def supports_window_functions? true end |