/
oracle_enhanced_adapter.rb
1131 lines (979 loc) · 43.6 KB
/
oracle_enhanced_adapter.rb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
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
234
235
236
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
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
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
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
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
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
# oracle_enhanced_adapter.rb -- ActiveRecord adapter for Oracle 8i, 9i, 10g, 11g
#
# Authors or original oracle_adapter: Graham Jenkins, Michael Schoen
#
# Current maintainer: Raimonds Simanovskis (http://blog.rayapps.com)
#
#########################################################################
#
# See History.md for changes added to original oracle_adapter.rb
#
#########################################################################
#
# From original oracle_adapter.rb:
#
# Implementation notes:
# 1. Redefines (safely) a method in ActiveRecord to make it possible to
# implement an autonumbering solution for Oracle.
# 2. The OCI8 driver is patched to properly handle values for LONG and
# TIMESTAMP columns. The driver-author has indicated that a future
# release of the driver will obviate this patch.
# 3. LOB support is implemented through an after_save callback.
# 4. Oracle does not offer native LIMIT and OFFSET options; this
# functionality is mimiced through the use of nested selects.
# See http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:127412348064
#
# Do what you want with this code, at your own peril, but if any
# significant portion of my code remains then please acknowledge my
# contribution.
# portions Copyright 2005 Graham Jenkins
require "active_record/connection_adapters/abstract_adapter"
require "active_record/connection_adapters/oracle_enhanced/connection"
require "active_record/connection_adapters/oracle_enhanced/database_statements"
require "active_record/connection_adapters/oracle_enhanced/schema_statements"
require "active_record/connection_adapters/oracle_enhanced/schema_statements_ext"
require "active_record/connection_adapters/oracle_enhanced/column_dumper"
require "active_record/connection_adapters/oracle_enhanced/context_index"
require "active_record/connection_adapters/oracle_enhanced/column"
require "active_record/connection_adapters/oracle_enhanced/quoting"
require "digest/sha1"
ActiveRecord::Base.class_eval do
class_attribute :custom_create_method, :custom_update_method, :custom_delete_method
end
module ActiveRecord
class Base
# Get table comment from schema definition.
def self.table_comment
connection.table_comment(self.table_name)
end
def self.lob_columns
columns.select do |column|
column.respond_to?(:lob?) && column.lob?
end
end
# After setting large objects to empty, select the OCI8::LOB
# and write back the data.
before_update :record_changed_lobs
after_update :enhanced_write_lobs
private
def enhanced_write_lobs
if self.class.connection.is_a?(ConnectionAdapters::OracleEnhancedAdapter) &&
!(
(self.class.custom_create_method || self.class.custom_create_method) ||
(self.class.custom_update_method || self.class.custom_update_method)
)
self.class.connection.write_lobs(self.class.table_name, self.class, attributes, @changed_lob_columns)
end
end
def record_changed_lobs
@changed_lob_columns = self.class.lob_columns.select do |col|
self.attribute_changed?(col.name) && !self.class.readonly_attributes.to_a.include?(col.name)
end
end
end
end
module ActiveRecord
module ConnectionHandling #:nodoc:
# Establishes a connection to the database that's used by all Active Record objects.
def oracle_enhanced_connection(config) #:nodoc:
if config[:emulate_oracle_adapter] == true
# allows the enhanced adapter to look like the OracleAdapter. Useful to pick up
# conditionals in the rails activerecord test suite
require "active_record/connection_adapters/emulation/oracle_adapter"
ConnectionAdapters::OracleAdapter.new(
ConnectionAdapters::OracleEnhancedConnection.create(config), logger, config)
else
ConnectionAdapters::OracleEnhancedAdapter.new(
ConnectionAdapters::OracleEnhancedConnection.create(config), logger, config)
end
end
end
module ConnectionAdapters #:nodoc:
# Oracle enhanced adapter will work with both
# Ruby 1.8/1.9 ruby-oci8 gem (which provides interface to Oracle OCI client)
# or with JRuby and Oracle JDBC driver.
#
# It should work with Oracle 9i, 10g and 11g databases.
# Limited set of functionality should work on Oracle 8i as well but several features
# rely on newer functionality in Oracle database.
#
# Usage notes:
# * Key generation assumes a "${table_name}_seq" sequence is available
# for all tables; the sequence name can be changed using
# ActiveRecord::Base.set_sequence_name. When using Migrations, these
# sequences are created automatically.
# Use set_sequence_name :autogenerated with legacy tables that have
# triggers that populate primary keys automatically.
# * Oracle uses DATE or TIMESTAMP datatypes for both dates and times.
# Consequently some hacks are employed to map data back to Date or Time
# in Ruby. Timezones and sub-second precision on timestamps are
# not supported.
# * Default values that are functions (such as "SYSDATE") are not
# supported. This is a restriction of the way ActiveRecord supports
# default values.
#
# Required parameters:
#
# * <tt>:username</tt>
# * <tt>:password</tt>
# * <tt>:database</tt> - either TNS alias or connection string for OCI client or database name in JDBC connection string
#
# Optional parameters:
#
# * <tt>:host</tt> - host name for JDBC connection, defaults to "localhost"
# * <tt>:port</tt> - port number for JDBC connection, defaults to 1521
# * <tt>:privilege</tt> - set "SYSDBA" if you want to connect with this privilege
# * <tt>:allow_concurrency</tt> - set to "true" if non-blocking mode should be enabled (just for OCI client)
# * <tt>:prefetch_rows</tt> - how many rows should be fetched at one time to increase performance, defaults to 100
# * <tt>:cursor_sharing</tt> - cursor sharing mode to minimize amount of unique statements, defaults to "force"
# * <tt>:time_zone</tt> - database session time zone
# (it is recommended to set it using ENV['TZ'] which will be then also used for database session time zone)
#
# Optionals NLS parameters:
#
# * <tt>:nls_calendar</tt>
# * <tt>:nls_comp</tt>
# * <tt>:nls_currency</tt>
# * <tt>:nls_date_format</tt> - format for :date columns, defaults to <tt>YYYY-MM-DD HH24:MI:SS</tt>
# * <tt>:nls_date_language</tt>
# * <tt>:nls_dual_currency</tt>
# * <tt>:nls_iso_currency</tt>
# * <tt>:nls_language</tt>
# * <tt>:nls_length_semantics</tt> - semantics of size of VARCHAR2 and CHAR columns, defaults to <tt>CHAR</tt>
# (meaning that size specifies number of characters and not bytes)
# * <tt>:nls_nchar_conv_excp</tt>
# * <tt>:nls_numeric_characters</tt>
# * <tt>:nls_sort</tt>
# * <tt>:nls_territory</tt>
# * <tt>:nls_timestamp_format</tt> - format for :timestamp columns, defaults to <tt>YYYY-MM-DD HH24:MI:SS:FF6</tt>
# * <tt>:nls_timestamp_tz_format</tt>
# * <tt>:nls_time_format</tt>
# * <tt>:nls_time_tz_format</tt>
#
class OracleEnhancedAdapter < AbstractAdapter
# TODO: Use relative
include ActiveRecord::ConnectionAdapters::OracleEnhanced::DatabaseStatements
include ActiveRecord::ConnectionAdapters::OracleEnhanced::SchemaStatements
include ActiveRecord::ConnectionAdapters::OracleEnhanced::SchemaStatementsExt
include ActiveRecord::ConnectionAdapters::OracleEnhanced::ColumnDumper
include ActiveRecord::ConnectionAdapters::OracleEnhanced::ContextIndex
include ActiveRecord::ConnectionAdapters::OracleEnhanced::Quoting
def schema_creation
OracleEnhanced::SchemaCreation.new self
end
##
# :singleton-method:
# By default, the OracleEnhancedAdapter will consider all columns of type <tt>NUMBER(1)</tt>
# as boolean. If you wish to disable this emulation you can add the following line
# to your initializer file:
#
# ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_booleans = false
cattr_accessor :emulate_booleans
self.emulate_booleans = true
##
# :singleton-method:
# OracleEnhancedAdapter will use the default tablespace, but if you want specific types of
# objects to go into specific tablespaces, specify them like this in an initializer:
#
# ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.default_tablespaces =
# {:clob => 'TS_LOB', :blob => 'TS_LOB', :index => 'TS_INDEX', :table => 'TS_DATA'}
#
# Using the :tablespace option where available (e.g create_table) will take precedence
# over these settings.
cattr_accessor :default_tablespaces
self.default_tablespaces = {}
##
# :singleton-method:
# If you wish that CHAR(1), VARCHAR2(1) columns or VARCHAR2 columns with FLAG or YN at the end of their name
# are typecasted to booleans then you can add the following line to your initializer file:
#
# ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_booleans_from_strings = true
cattr_accessor :emulate_booleans_from_strings
self.emulate_booleans_from_strings = false
##
# :singleton-method:
# By default, OracleEnhanced adapter will use Oracle12 visitor
# if database version is Oracle 12.1.
# If you wish to use Oracle visitor which is intended to work with Oracle 11.2 or lower
# for Oracle 12.1 database you can add the following line to your initializer file:
#
# ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.use_old_oracle_visitor = true
cattr_accessor :use_old_oracle_visitor
self.use_old_oracle_visitor = false
class StatementPool
include Enumerable
def initialize(connection, max = 300)
@connection = connection
@max = max
@cache = {}
end
def each(&block); @cache.each(&block); end
def key?(key); @cache.key?(key); end
def [](key); @cache[key]; end
def length; @cache.length; end
def delete(key); @cache.delete(key); end
def []=(sql, key)
while @max <= @cache.size
@cache.shift.last.close
end
@cache[sql] = key
end
def clear
@cache.values.each do |cursor|
cursor.close
end
@cache.clear
end
end
def initialize(connection, logger = nil, config = {}) # :nodoc:
super(connection, logger, config)
@quoted_column_names, @quoted_table_names = {}, {}
@statements = StatementPool.new(connection, config.fetch(:statement_limit) { 250 })
@enable_dbms_output = false
end
ADAPTER_NAME = "OracleEnhanced".freeze
def adapter_name #:nodoc:
ADAPTER_NAME
end
def arel_visitor # :nodoc:
if supports_fetch_first_n_rows_and_offset?
Arel::Visitors::Oracle12.new(self)
else
Arel::Visitors::Oracle.new(self)
end
end
def supports_migrations? #:nodoc:
true
end
def supports_primary_key? #:nodoc:
true
end
def supports_savepoints? #:nodoc:
true
end
def supports_transaction_isolation? #:nodoc:
true
end
def supports_foreign_keys?
true
end
def supports_foreign_keys_in_create?
supports_foreign_keys?
end
def supports_views?
true
end
def supports_fetch_first_n_rows_and_offset?
if !use_old_oracle_visitor && @connection.database_version == [12, 1]
true
else
false
end
end
def supports_datetime_with_precision?
true
end
def supports_comments?
true
end
def supports_multi_insert?
@connection.database_version.to_s >= [11, 2].to_s
end
#:stopdoc:
DEFAULT_NLS_PARAMETERS = {
nls_calendar: nil,
nls_comp: nil,
nls_currency: nil,
nls_date_format: "YYYY-MM-DD HH24:MI:SS",
nls_date_language: nil,
nls_dual_currency: nil,
nls_iso_currency: nil,
nls_language: nil,
nls_length_semantics: "CHAR",
nls_nchar_conv_excp: nil,
nls_numeric_characters: nil,
nls_sort: nil,
nls_territory: nil,
nls_timestamp_format: "YYYY-MM-DD HH24:MI:SS:FF6",
nls_timestamp_tz_format: nil,
nls_time_format: nil,
nls_time_tz_format: nil
}
#:stopdoc:
NATIVE_DATABASE_TYPES = {
primary_key: "NUMBER(38) NOT NULL PRIMARY KEY",
string: { name: "VARCHAR2", limit: 255 },
text: { name: "CLOB" },
integer: { name: "NUMBER", limit: 38 },
float: { name: "BINARY_FLOAT" },
decimal: { name: "DECIMAL" },
datetime: { name: "TIMESTAMP" },
timestamp: { name: "TIMESTAMP" },
time: { name: "TIMESTAMP" },
date: { name: "DATE" },
binary: { name: "BLOB" },
boolean: { name: "NUMBER", limit: 1 },
raw: { name: "RAW", limit: 2000 },
bigint: { name: "NUMBER", limit: 19 }
}
# if emulate_booleans_from_strings then store booleans in VARCHAR2
NATIVE_DATABASE_TYPES_BOOLEAN_STRINGS = NATIVE_DATABASE_TYPES.dup.merge(
boolean: { name: "VARCHAR2", limit: 1 }
)
#:startdoc:
def native_database_types #:nodoc:
emulate_booleans_from_strings ? NATIVE_DATABASE_TYPES_BOOLEAN_STRINGS : NATIVE_DATABASE_TYPES
end
# maximum length of Oracle identifiers
IDENTIFIER_MAX_LENGTH = 30
def table_alias_length #:nodoc:
IDENTIFIER_MAX_LENGTH
end
# the maximum length of a table name
def table_name_length
IDENTIFIER_MAX_LENGTH
end
# the maximum length of a column name
def column_name_length
IDENTIFIER_MAX_LENGTH
end
# Returns the maximum allowed length for an index name. This
# limit is enforced by rails and Is less than or equal to
# <tt>index_name_length</tt>. The gap between
# <tt>index_name_length</tt> is to allow internal rails
# opreations to use prefixes in temporary opreations.
def allowed_index_name_length
index_name_length
end
# the maximum length of an index name
# supported by this database
def index_name_length
IDENTIFIER_MAX_LENGTH
end
# the maximum length of a sequence name
def sequence_name_length
IDENTIFIER_MAX_LENGTH
end
# To avoid ORA-01795: maximum number of expressions in a list is 1000
# tell ActiveRecord to limit us to 1000 ids at a time
def in_clause_length
1000
end
alias ids_in_list_limit in_clause_length
# CONNECTION MANAGEMENT ====================================
#
# If SQL statement fails due to lost connection then reconnect
# and retry SQL statement if autocommit mode is enabled.
# By default this functionality is disabled.
attr_reader :auto_retry #:nodoc:
@auto_retry = false
def auto_retry=(value) #:nodoc:
@auto_retry = value
@connection.auto_retry = value if @connection
end
# return raw OCI8 or JDBC connection
def raw_connection
@connection.raw_connection
end
# Returns true if the connection is active.
def active? #:nodoc:
# Pings the connection to check if it's still good. Note that an
# #active? method is also available, but that simply returns the
# last known state, which isn't good enough if the connection has
# gone stale since the last use.
@connection.ping
rescue OracleEnhancedConnectionException
false
end
# Reconnects to the database.
def reconnect! #:nodoc:
super
@connection.reset!
rescue OracleEnhancedConnectionException => e
@logger.warn "#{adapter_name} automatic reconnection failed: #{e.message}" if @logger
end
def reset!
clear_cache!
super
end
# Disconnects from the database.
def disconnect! #:nodoc:
super
@connection.logoff rescue nil
end
# use in set_sequence_name to avoid fetching primary key value from sequence
AUTOGENERATED_SEQUENCE_NAME = "autogenerated".freeze
# Returns the next sequence value from a sequence generator. Not generally
# called directly; used by ActiveRecord to get the next primary key value
# when inserting a new database record (see #prefetch_primary_key?).
def next_sequence_value(sequence_name)
# if sequence_name is set to :autogenerated then it means that primary key will be populated by trigger
return nil if sequence_name == AUTOGENERATED_SEQUENCE_NAME
# call directly connection method to avoid prepared statement which causes fetching of next sequence value twice
@connection.select_value("SELECT #{quote_table_name(sequence_name)}.NEXTVAL FROM dual")
end
@@do_not_prefetch_primary_key = {}
# Returns true for Oracle adapter (since Oracle requires primary key
# values to be pre-fetched before insert). See also #next_sequence_value.
def prefetch_primary_key?(table_name = nil)
return true if table_name.nil?
table_name = table_name.to_s
do_not_prefetch = @@do_not_prefetch_primary_key[table_name]
if do_not_prefetch.nil?
owner, desc_table_name, db_link = @connection.describe(table_name)
@@do_not_prefetch_primary_key[table_name] = do_not_prefetch =
!has_primary_key?(table_name, owner, desc_table_name, db_link) ||
has_primary_key_trigger?(table_name, owner, desc_table_name, db_link)
end
!do_not_prefetch
end
# used just in tests to clear prefetch primary key flag for all tables
def clear_prefetch_primary_key #:nodoc:
@@do_not_prefetch_primary_key = {}
end
def reset_pk_sequence!(table_name, primary_key = nil, sequence_name = nil) #:nodoc:
return nil unless data_source_exists?(table_name)
unless primary_key && sequence_name
# *Note*: Only primary key is implemented - sequence will be nil.
primary_key, sequence_name = pk_and_sequence_for(table_name)
# TODO This sequence_name implemantation is just enough
# to satisty fixures. To get correct sequence_name always
# pk_and_sequence_for method needs some work.
begin
sequence_name = table_name.classify.constantize.sequence_name
rescue
sequence_name = default_sequence_name(table_name)
end
end
if @logger && primary_key && !sequence_name
@logger.warn "#{table_name} has primary key #{primary_key} with no default sequence"
end
if primary_key && sequence_name
new_start_value = select_value("
select NVL(max(#{quote_column_name(primary_key)}),0) + 1 from #{quote_table_name(table_name)}
", new_start_value)
execute "DROP SEQUENCE #{quote_table_name(sequence_name)}"
execute "CREATE SEQUENCE #{quote_table_name(sequence_name)} START WITH #{new_start_value}"
end
end
# Writes LOB values from attributes for specified columns
def write_lobs(table_name, klass, attributes, columns) #:nodoc:
# is class with composite primary key>
is_with_cpk = klass.respond_to?(:composite?) && klass.composite?
if is_with_cpk
id = klass.primary_key.map { |pk| attributes[pk.to_s] }
else
id = quote(attributes[klass.primary_key])
end
columns.each do |col|
value = attributes[col.name]
# changed sequence of next two lines - should check if value is nil before converting to yaml
next if value.blank?
if klass.attribute_types[col.name].is_a? ActiveRecord::Type::Serialized
value = klass.attribute_types[col.name].serialize(value)
end
uncached do
sql = is_with_cpk ? "SELECT #{quote_column_name(col.name)} FROM #{quote_table_name(table_name)} WHERE #{klass.composite_where_clause(id)} FOR UPDATE" :
"SELECT #{quote_column_name(col.name)} FROM #{quote_table_name(table_name)} WHERE #{quote_column_name(klass.primary_key)} = #{id} FOR UPDATE"
unless lob_record = select_one(sql, "Writable Large Object")
raise ActiveRecord::RecordNotFound, "statement #{sql} returned no rows"
end
lob = lob_record[col.name]
@connection.write_lob(lob, value.to_s, col.type == :binary)
end
end
end
# Current database name
def current_database
select_value("SELECT SYS_CONTEXT('userenv', 'con_name') FROM dual")
rescue ActiveRecord::StatementInvalid
select_value("SELECT SYS_CONTEXT('userenv', 'db_name') FROM dual")
end
# Current database session user
def current_user
select_value("SELECT SYS_CONTEXT('userenv', 'session_user') FROM dual")
end
# Current database session schema
def current_schema
select_value("SELECT SYS_CONTEXT('userenv', 'current_schema') FROM dual")
end
# Default tablespace name of current user
def default_tablespace
select_value("SELECT LOWER(default_tablespace) FROM user_users WHERE username = SYS_CONTEXT('userenv', 'current_schema')")
end
def tables(name = nil) #:nodoc:
ActiveSupport::Deprecation.warn(<<-MSG.squish)
#tables currently returns both tables and views.
This behavior is deprecated and will be changed with Rails 5.1 to only return tables.
Use #data_sources instead.
MSG
if name
ActiveSupport::Deprecation.warn(<<-MSG.squish)
Passing arguments to #tables is deprecated without replacement.
MSG
end
data_sources
end
def data_sources
select_values(
"SELECT DECODE(table_name, UPPER(table_name), LOWER(table_name), table_name) FROM all_tables WHERE owner = SYS_CONTEXT('userenv', 'current_schema') AND secondary = 'N'",
"SCHEMA")
end
def table_exists?(table_name)
ActiveSupport::Deprecation.warn(<<-MSG.squish)
#table_exists? currently checks both tables and views.
This behavior is deprecated and will be changed with Rails 5.1 to only check tables.
Use #data_source_exists? instead.
MSG
data_source_exists?(table_name)
end
# Will return true if database object exists (to be able to use also views and synonyms for ActiveRecord models)
# Needs to consider how to support synonyms in Rails 5.1
def data_source_exists?(table_name)
(_owner, table_name, _db_link) = @connection.describe(table_name)
true
rescue
false
end
def views # :nodoc:
select_values("SELECT LOWER(view_name) FROM all_views WHERE owner = SYS_CONTEXT('userenv', 'session_user')")
end
def materialized_views #:nodoc:
select_values("SELECT LOWER(mview_name) FROM all_mviews WHERE owner = SYS_CONTEXT('userenv', 'current_schema')")
end
cattr_accessor :all_schema_indexes #:nodoc:
# This method selects all indexes at once, and caches them in a class variable.
# Subsequent index calls get them from the variable, without going to the DB.
def indexes(table_name, name = nil) #:nodoc:
(owner, table_name, db_link) = @connection.describe(table_name)
unless all_schema_indexes
default_tablespace_name = default_tablespace
result = select_all(<<-SQL.strip.gsub(/\s+/, " "))
SELECT LOWER(i.table_name) AS table_name, LOWER(i.index_name) AS index_name, i.uniqueness,
i.index_type, i.ityp_owner, i.ityp_name, i.parameters,
LOWER(i.tablespace_name) AS tablespace_name,
LOWER(c.column_name) AS column_name, e.column_expression,
atc.virtual_column
FROM all_indexes#{db_link} i
JOIN all_ind_columns#{db_link} c ON c.index_name = i.index_name AND c.index_owner = i.owner
LEFT OUTER JOIN all_ind_expressions#{db_link} e ON e.index_name = i.index_name AND
e.index_owner = i.owner AND e.column_position = c.column_position
LEFT OUTER JOIN all_tab_cols#{db_link} atc ON i.table_name = atc.table_name AND
c.column_name = atc.column_name AND i.owner = atc.owner AND atc.hidden_column = 'NO'
WHERE i.owner = '#{owner}'
AND i.table_owner = '#{owner}'
AND NOT EXISTS (SELECT uc.index_name FROM all_constraints uc
WHERE uc.index_name = i.index_name AND uc.owner = i.owner AND uc.constraint_type = 'P')
ORDER BY i.index_name, c.column_position
SQL
current_index = nil
self.all_schema_indexes = []
result.each do |row|
# have to keep track of indexes because above query returns dups
# there is probably a better query we could figure out
if current_index != row["index_name"]
statement_parameters = nil
if row["index_type"] == "DOMAIN" && row["ityp_owner"] == "CTXSYS" && row["ityp_name"] == "CONTEXT"
procedure_name = default_datastore_procedure(row["index_name"])
source = select_values(<<-SQL).join
SELECT text
FROM all_source#{db_link}
WHERE owner = '#{owner}'
AND name = '#{procedure_name.upcase}'
ORDER BY line
SQL
if source =~ /-- add_context_index_parameters (.+)\n/
statement_parameters = $1
end
end
all_schema_indexes << OracleEnhanced::IndexDefinition.new(
row["table_name"],
row["index_name"],
row["uniqueness"] == "UNIQUE",
[],
nil,
nil,
nil,
row["index_type"] == "DOMAIN" ? "#{row['ityp_owner']}.#{row['ityp_name']}" : nil,
nil,
row["parameters"],
statement_parameters,
row["tablespace_name"] == default_tablespace_name ? nil : row["tablespace_name"])
current_index = row["index_name"]
end
# Functional index columns and virtual columns both get stored as column expressions,
# but re-creating a virtual column index as an expression (instead of using the virtual column's name)
# results in a ORA-54018 error. Thus, we only want the column expression value returned
# when the column is not virtual.
if row["column_expression"] && row["virtual_column"] != "YES"
all_schema_indexes.last.columns << row["column_expression"]
else
all_schema_indexes.last.columns << row["column_name"].downcase
end
end
end
# Return the indexes just for the requested table, since AR is structured that way
table_name = table_name.downcase
all_schema_indexes.select { |i| i.table == table_name }
end
# check if table has primary key trigger with _pkt suffix
def has_primary_key_trigger?(table_name, owner = nil, desc_table_name = nil, db_link = nil)
(owner, desc_table_name, db_link) = @connection.describe(table_name) unless owner
trigger_name = default_trigger_name(table_name).upcase
pkt_sql = <<-SQL
SELECT trigger_name
FROM all_triggers#{db_link}
WHERE owner = '#{owner}'
AND trigger_name = '#{trigger_name}'
AND table_owner = '#{owner}'
AND table_name = '#{desc_table_name}'
AND status = 'ENABLED'
SQL
select_value(pkt_sql, "Primary Key Trigger") ? true : false
end
##
# :singleton-method:
# Cache column description between requests.
# Could be used in development environment to avoid selecting table columns from data dictionary tables for each request.
# This can speed up request processing in development mode if development database is not on local computer.
#
# ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.cache_columns = true
cattr_accessor :cache_columns
self.cache_columns = false
def columns(table_name, name = nil) #:nodoc:
if @@cache_columns
@@columns_cache ||= {}
@@columns_cache[table_name] ||= columns_without_cache(table_name, name)
else
columns_without_cache(table_name, name)
end
end
def columns_without_cache(table_name, name = nil) #:nodoc:
table_name = table_name.to_s
(owner, desc_table_name, db_link) = @connection.describe(table_name)
# reset do_not_prefetch_primary_key cache for this table
@@do_not_prefetch_primary_key[table_name] = nil
table_cols = <<-SQL.strip.gsub(/\s+/, " ")
SELECT cols.column_name AS name, cols.data_type AS sql_type,
cols.data_default, cols.nullable, cols.virtual_column, cols.hidden_column,
cols.data_type_owner AS sql_type_owner,
DECODE(cols.data_type, 'NUMBER', data_precision,
'FLOAT', data_precision,
'VARCHAR2', DECODE(char_used, 'C', char_length, data_length),
'RAW', DECODE(char_used, 'C', char_length, data_length),
'CHAR', DECODE(char_used, 'C', char_length, data_length),
NULL) AS limit,
DECODE(data_type, 'NUMBER', data_scale, NULL) AS scale,
comments.comments as column_comment
FROM all_tab_cols#{db_link} cols, all_col_comments#{db_link} comments
WHERE cols.owner = '#{owner}'
AND cols.table_name = #{quote(desc_table_name)}
AND cols.hidden_column = 'NO'
AND cols.owner = comments.owner
AND cols.table_name = comments.table_name
AND cols.column_name = comments.column_name
ORDER BY cols.column_id
SQL
# added deletion of ignored columns
select_all(table_cols, name).to_a.map do |row|
limit, scale = row["limit"], row["scale"]
if limit || scale
row["sql_type"] += "(#{(limit || 38).to_i}" + ((scale = scale.to_i) > 0 ? ",#{scale})" : ")")
end
if row["sql_type_owner"]
row["sql_type"] = row["sql_type_owner"] + "." + row["sql_type"]
end
is_virtual = row["virtual_column"] == "YES"
# clean up odd default spacing from Oracle
if row["data_default"] && !is_virtual
row["data_default"].sub!(/^(.*?)\s*$/, '\1')
# If a default contains a newline these cleanup regexes need to
# match newlines.
row["data_default"].sub!(/^'(.*)'$/m, '\1')
row["data_default"] = nil if row["data_default"] =~ /^(null|empty_[bc]lob\(\))$/i
# TODO: Needs better fix to fallback "N" to false
row["data_default"] = false if (row["data_default"] == "N" && OracleEnhancedAdapter.emulate_booleans_from_strings)
end
type_metadata = fetch_type_metadata(row["sql_type"])
new_column(oracle_downcase(row["name"]),
row["data_default"],
type_metadata,
row["nullable"] == "Y",
table_name,
is_virtual,
false,
row["column_comment"]
)
end
end
def new_column(name, default, sql_type_metadata = nil, null = true, table_name = nil, virtual = false, returning_id = false, comment = nil) # :nodoc:
OracleEnhancedColumn.new(name, default, sql_type_metadata, null, table_name, virtual, returning_id, comment)
end
# used just in tests to clear column cache
def clear_columns_cache #:nodoc:
@@columns_cache = nil
@@pk_and_sequence_for_cache = nil
end
# used in migrations to clear column cache for specified table
def clear_table_columns_cache(table_name)
if @@cache_columns
@@columns_cache ||= {}
@@columns_cache[table_name.to_s] = nil
end
end
##
# :singleton-method:
# Specify default sequence start with value (by default 10000 if not explicitly set), e.g.:
#
# ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.default_sequence_start_value = 1
cattr_accessor :default_sequence_start_value
self.default_sequence_start_value = 10000
# Find a table's primary key and sequence.
# *Note*: Only primary key is implemented - sequence will be nil.
def pk_and_sequence_for(table_name, owner = nil, desc_table_name = nil, db_link = nil) #:nodoc:
if @@cache_columns
@@pk_and_sequence_for_cache ||= {}
if @@pk_and_sequence_for_cache.key?(table_name)
@@pk_and_sequence_for_cache[table_name]
else
@@pk_and_sequence_for_cache[table_name] = pk_and_sequence_for_without_cache(table_name, owner, desc_table_name, db_link)
end
else
pk_and_sequence_for_without_cache(table_name, owner, desc_table_name, db_link)
end
end
def pk_and_sequence_for_without_cache(table_name, owner = nil, desc_table_name = nil, db_link = nil) #:nodoc:
(owner, desc_table_name, db_link) = @connection.describe(table_name) unless owner
seqs = select_values(<<-SQL.strip.gsub(/\s+/, " "), "Sequence")
select us.sequence_name
from all_sequences#{db_link} us
where us.sequence_owner = '#{owner}'
and us.sequence_name = upper(#{quote(default_sequence_name(desc_table_name))})
SQL
# changed back from user_constraints to all_constraints for consistency
pks = select_values(<<-SQL.strip.gsub(/\s+/, " "), "Primary Key")
SELECT cc.column_name
FROM all_constraints#{db_link} c, all_cons_columns#{db_link} cc
WHERE c.owner = '#{owner}'
AND c.table_name = #{quote(desc_table_name)}
AND c.constraint_type = 'P'
AND cc.owner = c.owner
AND cc.constraint_name = c.constraint_name
SQL
warn <<-WARNING.strip_heredoc if pks.count > 1
WARNING: Active Record does not support composite primary key.
#{table_name} has composite primary key. Composite primary key is ignored.
WARNING
# only support single column keys
pks.size == 1 ? [oracle_downcase(pks.first),
oracle_downcase(seqs.first)] : nil
end
# Returns just a table's primary key
def primary_key(table_name)
pk_and_sequence = pk_and_sequence_for(table_name)
pk_and_sequence && pk_and_sequence.first
end
def has_primary_key?(table_name, owner = nil, desc_table_name = nil, db_link = nil) #:nodoc:
!pk_and_sequence_for(table_name, owner, desc_table_name, db_link).nil?
end
def primary_keys(table_name) # :nodoc:
(owner, desc_table_name, db_link) = @connection.describe(table_name) unless owner
pks = select_values(<<-SQL.strip_heredoc, "Primary Keys")
SELECT cc.column_name
FROM all_constraints#{db_link} c, all_cons_columns#{db_link} cc
WHERE c.owner = '#{owner}'
AND c.table_name = '#{desc_table_name}'
AND c.constraint_type = 'P'
AND cc.owner = c.owner
AND cc.constraint_name = c.constraint_name
order by cc.position
SQL
pks.map { |pk| oracle_downcase(pk) }
end
def columns_for_distinct(columns, orders) #:nodoc:
# construct a valid columns name for 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
#
# It does not construct DISTINCT clause. Just return column names for distinct.
order_columns = orders.reject(&:blank?).map { |s|
s = s.to_sql unless s.is_a?(String)
# remove any ASC/DESC modifiers
s.gsub(/\s+(ASC|DESC)\s*?/i, "")
}.reject(&:blank?).map.with_index { |column, i|
"FIRST_VALUE(#{column}) OVER (PARTITION BY #{columns} ORDER BY #{column}) AS alias_#{i}__"
}
[super, *order_columns].join(", ")
end
def temporary_table?(table_name) #:nodoc:
select_value("SELECT temporary FROM all_tables WHERE table_name = '#{table_name.upcase}' and owner = SYS_CONTEXT('userenv', 'session_user')") == "Y"
end
def valid_type?(type)
!native_database_types[type].nil?
end
def combine_bind_parameters(
from_clause: [],
join_clause: [],
where_clause: [],
having_clause: [],
limit: nil,
offset: nil
) # :nodoc:
result = from_clause + join_clause + where_clause + having_clause
if RUBY_ENGINE == "jruby" && !supports_fetch_first_n_rows_and_offset? && offset && limit
result << offset
result << limit
result << offset
else
if offset
result << offset
end
if limit
result << limit
end
end
result
end
protected
def initialize_type_map(m)
super
# oracle
register_class_with_limit m, %r(raw)i, ActiveRecord::OracleEnhanced::Type::Raw
register_class_with_limit m, %r(char)i, ActiveRecord::OracleEnhanced::Type::String
register_class_with_limit m, %r(clob)i, ActiveRecord::OracleEnhanced::Type::Text
m.register_type "NCHAR", ActiveRecord::OracleEnhanced::Type::NationalCharacterString.new
m.alias_type %r(NVARCHAR2)i, "NCHAR"
m.register_type(%r(NUMBER)i) do |sql_type|
scale = extract_scale(sql_type)
precision = extract_precision(sql_type)
limit = extract_limit(sql_type)
if scale == 0
ActiveRecord::OracleEnhanced::Type::Integer.new(precision: precision, limit: limit)
else
Type::Decimal.new(precision: precision, scale: scale)
end
end
if OracleEnhancedAdapter.emulate_booleans
if OracleEnhancedAdapter.emulate_booleans_from_strings
m.register_type %r(^VARCHAR2\(1\))i, ActiveRecord::OracleEnhanced::Type::Boolean.new
else
m.register_type %r(^NUMBER\(1\))i, Type::Boolean.new
end
end
end
def extract_limit(sql_type) #:nodoc:
case sql_type
when /^bigint/i
19
when /\((.*)\)/
$1.to_i
end
end
def translate_exception(exception, message) #:nodoc:
case @connection.error_code(exception)
when 1
RecordNotUnique.new(message)
when 942, 955