Module: ArJdbc::DB2
- Included in:
- AS400
- Defined in:
- lib/arjdbc/db2/adapter.rb,
lib/arjdbc/db2/column.rb
Overview
This adapter doesn't support explain config.active_record.auto_explain_threshold_in_seconds
should be commented (Rails < 4.0)
Defined Under Namespace
Modules: Column
Constant Summary collapse
- ADAPTER_NAME =
'DB2'.freeze
- NATIVE_DATABASE_TYPES =
{ :string => { :name => "varchar", :limit => 255 }, :integer => { :name => "integer" }, :bigint => { :name => 'bigint' }, :float => { :name => "real" }, # :limit => 24 :double => { :name => "double" }, # :limit => 53 :text => { :name => "clob" }, :binary => { :name => "blob" }, :xml => { :name => "xml" }, :decimal => { :name => "decimal" }, # :limit => 31 :char => { :name => "char" }, # :limit => 254 :date => { :name => "date" }, :datetime => { :name => "timestamp" }, :timestamp => { :name => "timestamp" }, :time => { :name => "time" }, :boolean => { :name => "smallint" }, # no native boolean type #:rowid => { :name => "rowid" }, # rowid is a supported datatype on z/OS and i/5 #:serial => { :name => "serial" }, # supported datatype on Informix Dynamic Server #:graphic => { :name => "graphic", :limit => 1 }, # :limit => 127 }
- HAVE_LIMIT =
only record precision and scale for types that can set them via CREATE TABLE: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000927.html
%w(FLOAT DECFLOAT CHAR VARCHAR CLOB BLOB NCHAR NCLOB DBCLOB GRAPHIC VARGRAPHIC)
- HAVE_PRECISION =
TIMESTAMP
%w(DECIMAL NUMERIC)
- HAVE_SCALE =
%w(DECIMAL NUMERIC)
- DRIVER_NAME =
'com.ibm.db2.jcc.DB2Driver'.freeze
Class Method Summary collapse
-
.arel2_visitors(config) ⇒ Object
deprecated
Deprecated.
no longer used
- .arel_visitor_type(config = nil) ⇒ Object
- .column_selector ⇒ Object
-
.emulate_booleans ⇒ Object
deprecated
Deprecated.
Use #emulate_booleans? instead.
- .emulate_booleans=(emulate) ⇒ Object
-
.emulate_booleans? ⇒ Boolean
Boolean emulation can be disabled using :.
- .jdbc_connection_class ⇒ Object
- .update_lob_values=(update) ⇒ Object
-
.update_lob_values? ⇒ Boolean
Updating records with LOB values (binary/text columns) in a separate statement can be disabled using :.
Instance Method Summary collapse
- #adapter_name ⇒ Object
- #add_column(table_name, column_name, type, options = {}) ⇒ Object
- #add_column_options!(sql, options) ⇒ Object
- #add_index(table_name, column_name, options = {}) ⇒ Object
- #add_limit_offset!(sql, options) ⇒ Object
- #change_column(table_name, column_name, type, options = {}) ⇒ Object
- #change_column_default(table_name, column_name, default) ⇒ Object
- #change_column_null(table_name, column_name, null) ⇒ Object
- #columns(table_name, name = nil) ⇒ Object
- #configure_connection ⇒ Object
- #create_table(name, options = {}, &block) ⇒ Object
- #drop_database(name = nil) ⇒ Object
- #empty_insert_statement_value ⇒ Object
- #execute_table_change(sql, table_name, name = nil) ⇒ Object protected
- #indexes(table_name, name = nil) ⇒ Object
- #jdbc_column_class ⇒ Object
-
#last_insert_id ⇒ Fixnum
Returns the value of an identity column of the last INSERT statement made over this connection.
- #modify_types(types) ⇒ Object
- #native_database_types ⇒ Object
- #next_sequence_value(sequence_name) ⇒ Object
- #pk_and_sequence_for(table) ⇒ Object
- #prefetch_primary_key?(table_name = nil) ⇒ Boolean
-
#quote(value, column = nil) ⇒ Object
Properly quotes the various data types.
- #quote_column_name(column_name) ⇒ Object
- #quote_time(value) ⇒ Object
- #quoted_date(value) ⇒ Object
- #recreate_database(name = nil, options = {}) ⇒ Object
- #remove_column(table_name, *column_names) ⇒ Object
- #remove_index!(table_name, index_name) ⇒ Object
-
#rename_column(table_name, column_name, new_column_name) ⇒ Object
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/t0020130.html ...not supported on IBM i, so we raise in this case.
- #rename_table(name, new_name) ⇒ Object
- #reset_column_information ⇒ Object
-
#runstats_for_table(tablename, priority = 10) ⇒ Object
deprecated
Deprecated.
seems not sued nor tested ?!
- #schema ⇒ Object
- #schema=(schema) ⇒ Object
- #select(sql, name = nil, binds = []) ⇒ Object
- #supports_views? ⇒ Boolean
- #table_definition(*args) ⇒ Object
- #tables ⇒ Object
- #truncate(table_name, name = nil) ⇒ Object
- #type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object
- #update_lob_value?(value, column = nil) ⇒ Boolean
Class Method Details
.arel2_visitors(config) ⇒ Object
no longer used
64 65 66 |
# File 'lib/arjdbc/db2/adapter.rb', line 64 def self.arel2_visitors(config) { 'db2' => arel_visitor_type } end |
.arel_visitor_type(config = nil) ⇒ Object
58 59 60 |
# File 'lib/arjdbc/db2/adapter.rb', line 58 def self.arel_visitor_type(config = nil) require 'arel/visitors/db2'; ::Arel::Visitors::DB2 end |
.column_selector ⇒ Object
5 6 7 |
# File 'lib/arjdbc/db2/column.rb', line 5 def self.column_selector [ /(db2|zos)/i, lambda { |config, column| column.extend(Column) } ] end |
.emulate_booleans ⇒ Object
Use #emulate_booleans? instead.
77 |
# File 'lib/arjdbc/db2/adapter.rb', line 77 def self.emulate_booleans; @@emulate_booleans; end |
.emulate_booleans=(emulate) ⇒ Object
79 |
# File 'lib/arjdbc/db2/adapter.rb', line 79 def self.emulate_booleans=(emulate); @@emulate_booleans = emulate; end |
.emulate_booleans? ⇒ Boolean
Boolean emulation can be disabled using :
ArJdbc::DB2.emulate_booleans = false
75 |
# File 'lib/arjdbc/db2/adapter.rb', line 75 def self.emulate_booleans?; @@emulate_booleans; end |
.jdbc_connection_class ⇒ Object
48 49 50 |
# File 'lib/arjdbc/db2/adapter.rb', line 48 def self.jdbc_connection_class ::ActiveRecord::ConnectionAdapters::DB2JdbcConnection end |
.update_lob_values=(update) ⇒ Object
92 |
# File 'lib/arjdbc/db2/adapter.rb', line 92 def self.update_lob_values=(update); @@update_lob_values = update; end |
.update_lob_values? ⇒ Boolean
This only applies when prepared statements are not used.
Updating records with LOB values (binary/text columns) in a separate statement can be disabled using :
ArJdbc::DB2.update_lob_values = false
90 |
# File 'lib/arjdbc/db2/adapter.rb', line 90 def self.update_lob_values?; @@update_lob_values; end |
Instance Method Details
#adapter_name ⇒ Object
114 115 116 |
# File 'lib/arjdbc/db2/adapter.rb', line 114 def adapter_name ADAPTER_NAME end |
#add_column(table_name, column_name, type, options = {}) ⇒ Object
455 456 457 458 459 460 |
# File 'lib/arjdbc/db2/adapter.rb', line 455 def add_column(table_name, column_name, type, = {}) # The keyword COLUMN allows to use reserved names for columns (ex: date) add_column_sql = "ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, [:limit], [:precision], [:scale])}" (add_column_sql, ) execute(add_column_sql) end |
#add_column_options!(sql, options) ⇒ Object
462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 |
# File 'lib/arjdbc/db2/adapter.rb', line 462 def (sql, ) # handle case of defaults for CLOB columns, # which might get incorrect if we write LOBs in the after_save callback if () column = [:column] if column && column.type == :text sql << " DEFAULT #{quote(.delete(:default))}" end if column && column.type == :binary # quoting required for the default value of a column : value = .delete(:default) # DB2 z/OS only allows NULL or "" (empty) string as DEFAULT value # for a BLOB column. non-empty string and non-NULL, return error! if value.nil? sql_value = "NULL" else sql_value = zos? ? "#{value}" : "BLOB('#{quote_string(value)}'" end sql << " DEFAULT #{sql_value}" end end super end |
#add_index(table_name, column_name, options = {}) ⇒ Object
555 556 557 558 559 560 561 562 563 564 565 566 567 |
# File 'lib/arjdbc/db2/adapter.rb', line 555 def add_index(table_name, column_name, = {}) if ! zos? || ( table_name.to_s == ActiveRecord::Migrator.schema_migrations_table_name.to_s ) column_name = column_name.to_s if column_name.is_a?(Symbol) super else statement = 'CREATE' statement << ' UNIQUE ' if [:unique] statement << " INDEX #{ActiveRecord::Base.table_name_prefix}#{[:name]} " statement << " ON #{table_name}(#{column_name})" execute statement end end |
#add_limit_offset!(sql, options) ⇒ Object
Only used with (non-AREL) ActiveRecord 2.3.
488 489 490 491 |
# File 'lib/arjdbc/db2/adapter.rb', line 488 def add_limit_offset!(sql, ) limit = [:limit] replace_limit_offset!(sql, limit, [:offset]) if limit end |
#change_column(table_name, column_name, type, options = {}) ⇒ Object
599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 |
# File 'lib/arjdbc/db2/adapter.rb', line 599 def change_column(table_name, column_name, type, = {}) data_type = type_to_sql(type, [:limit], [:precision], [:scale]) sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET DATA TYPE #{data_type}" execute_table_change(sql, table_name, 'Change Column') if .include?(:default) and .include?(:null) # which to run first? if [:null] or [:default].nil? change_column_null(table_name, column_name, [:null]) change_column_default(table_name, column_name, [:default]) else change_column_default(table_name, column_name, [:default]) change_column_null(table_name, column_name, [:null]) end elsif .include?(:default) change_column_default(table_name, column_name, [:default]) elsif .include?(:null) change_column_null(table_name, column_name, [:null]) end end |
#change_column_default(table_name, column_name, default) ⇒ Object
590 591 592 593 594 595 596 597 |
# File 'lib/arjdbc/db2/adapter.rb', line 590 def change_column_default(table_name, column_name, default) if default.nil? sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} DROP DEFAULT" else sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET WITH DEFAULT #{quote(default)}" end execute_table_change(sql, table_name, 'Change Column') end |
#change_column_null(table_name, column_name, null) ⇒ Object
581 582 583 584 585 586 587 588 |
# File 'lib/arjdbc/db2/adapter.rb', line 581 def change_column_null(table_name, column_name, null) if null sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} DROP NOT NULL" else sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET NOT NULL" end execute_table_change(sql, table_name, 'Change Column') end |
#columns(table_name, name = nil) ⇒ Object
656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 |
# File 'lib/arjdbc/db2/adapter.rb', line 656 def columns(table_name, name = nil) columns = @connection.columns_internal(table_name.to_s, nil, schema) # catalog == nil if zos? # Remove the mighty db2_generated_rowid_for_lobs from the list of columns columns = columns.reject { |col| "db2_generated_rowid_for_lobs" == col.name } end # scrub out sizing info when CREATE TABLE doesn't support it # but JDBC reports it (doh!) for column in columns base_sql_type = column.sql_type.sub(/\(.*/, "").upcase column.limit = nil unless HAVE_LIMIT.include?(base_sql_type) column.precision = nil unless HAVE_PRECISION.include?(base_sql_type) #column.scale = nil unless HAVE_SCALE.include?(base_sql_type) end columns end |
#configure_connection ⇒ Object
107 108 109 110 |
# File 'lib/arjdbc/db2/adapter.rb', line 107 def configure_connection schema = self.schema set_schema(schema) if schema && schema != config[:username] end |
#create_table(name, options = {}, &block) ⇒ Object
287 288 289 290 291 292 293 |
# File 'lib/arjdbc/db2/adapter.rb', line 287 def create_table(name, = {}, &block) if zos? zos_create_table(name, , &block) else super end end |
#drop_database(name = nil) ⇒ Object
683 684 685 |
# File 'lib/arjdbc/db2/adapter.rb', line 683 def drop_database(name = nil) tables.each { |table| drop_table("#{table}") } end |
#empty_insert_statement_value ⇒ Object
451 452 453 |
# File 'lib/arjdbc/db2/adapter.rb', line 451 def empty_insert_statement_value VALUES_DEFAULT # won't work as DB2 needs to know the column count end |
#execute_table_change(sql, table_name, name = nil) ⇒ Object (protected)
694 695 696 697 698 |
# File 'lib/arjdbc/db2/adapter.rb', line 694 def execute_table_change(sql, table_name, name = nil) outcome = execute(sql, name) reorg_table(table_name, name) outcome end |
#indexes(table_name, name = nil) ⇒ Object
675 676 677 |
# File 'lib/arjdbc/db2/adapter.rb', line 675 def indexes(table_name, name = nil) @connection.indexes(table_name, name, schema) end |
#jdbc_column_class ⇒ Object
53 54 55 |
# File 'lib/arjdbc/db2/adapter.rb', line 53 def jdbc_column_class ::ActiveRecord::ConnectionAdapters::DB2Column end |
#last_insert_id ⇒ Fixnum
Check the IDENTITY_VAL_LOCAL function for documentation.
Returns the value of an identity column of the last INSERT statement made over this connection.
712 713 714 |
# File 'lib/arjdbc/db2/adapter.rb', line 712 def last_insert_id @connection.identity_val_local end |
#modify_types(types) ⇒ Object
433 434 435 436 437 438 439 440 |
# File 'lib/arjdbc/db2/adapter.rb', line 433 def modify_types(types) super(types) types[:primary_key] = 'int not null generated by default as identity (start with 1) primary key' types[:string][:limit] = 255 types[:integer][:limit] = nil types[:boolean] = {:name => "decimal(1)"} types end |
#native_database_types ⇒ Object
212 213 214 215 216 217 218 |
# File 'lib/arjdbc/db2/adapter.rb', line 212 def native_database_types # NOTE: currently merging with what JDBC gives us since there's a lot # of DB2-like stuff we could be connecting e.g. "classic", Z/OS etc. # types = super types = super.merge(NATIVE_DATABASE_TYPES) types end |
#next_sequence_value(sequence_name) ⇒ Object
283 284 285 |
# File 'lib/arjdbc/db2/adapter.rb', line 283 def next_sequence_value(sequence_name) select_value("SELECT NEXT VALUE FOR #{sequence_name} FROM sysibm.sysdummy1") end |
#pk_and_sequence_for(table) ⇒ Object
344 345 346 347 348 349 350 351 352 |
# File 'lib/arjdbc/db2/adapter.rb', line 344 def pk_and_sequence_for(table) # In JDBC/DB2 side, only upcase names of table and column are handled. keys = super(table.upcase) if keys && keys[0] # In ActiveRecord side, only downcase names of table and column are handled. keys[0] = keys[0].downcase end keys end |
#prefetch_primary_key?(table_name = nil) ⇒ Boolean
274 275 276 277 278 279 280 281 |
# File 'lib/arjdbc/db2/adapter.rb', line 274 def prefetch_primary_key?(table_name = nil) # TRUE if the table has no identity column names = table_name.upcase.split(".") sql = "SELECT 1 FROM SYSCAT.COLUMNS WHERE IDENTITY = 'Y' " sql << "AND TABSCHEMA = '#{names.first}' " if names.size == 2 sql << "AND TABNAME = '#{names.last}'" select_one(sql).nil? end |
#quote(value, column = nil) ⇒ Object
Properly quotes the various data types.
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 |
# File 'lib/arjdbc/db2/adapter.rb', line 358 def quote(value, column = nil) return value.quoted_id if value.respond_to?(:quoted_id) return value if sql_literal?(value) if column if column.respond_to?(:primary) && column.primary && column.klass != String return value.to_i.to_s end if value && (column.type.to_sym == :decimal || column.type.to_sym == :integer) return value.to_s end end column_type = column && column.type.to_sym case value when nil then 'NULL' when Numeric # IBM_DB doesn't accept quotes on numeric types # if the column type is text or string, return the quote value if column_type == :text || column_type == :string "'#{value}'" else value.to_s end when String, ActiveSupport::Multibyte::Chars if column_type == :binary && column.sql_type !~ /for bit data/i if update_lob_value?(value, column) value.nil? ? 'NULL' : BLOB_VALUE_MARKER # '@@@IBMBINARY@@@'" else "BLOB('#{quote_string(value)}')" end elsif column && column.sql_type =~ /clob/ # :text if update_lob_value?(value, column) value.nil? ? 'NULL' : CLOB_VALUE_MARKER # "'@@@IBMTEXT@@@'" else "'#{quote_string(value)}'" end elsif column_type == :xml value.nil? ? 'NULL' : "'#{quote_string(value)}'" # "'<ibm>@@@IBMXML@@@</ibm>'" else "'#{quote_string(value)}'" end when Symbol then "'#{quote_string(value.to_s)}'" when Time # AS400 doesn't support date in time column if column_type == :time quote_time(value) else super end else super end end |
#quote_column_name(column_name) ⇒ Object
429 430 431 |
# File 'lib/arjdbc/db2/adapter.rb', line 429 def quote_column_name(column_name) column_name.to_s end |
#quote_time(value) ⇒ Object
423 424 425 426 427 |
# File 'lib/arjdbc/db2/adapter.rb', line 423 def quote_time(value) value = ::ActiveRecord::Base.default_timezone == :utc ? value.getutc : value.getlocal # AS400 doesn't support date in time column "'#{value.strftime("%H:%M:%S")}'" end |
#quoted_date(value) ⇒ Object
413 414 415 416 417 418 419 420 421 |
# File 'lib/arjdbc/db2/adapter.rb', line 413 def quoted_date(value) if value.acts_like?(:time) && value.respond_to?(:usec) usec = sprintf("%06d", value.usec) value = ::ActiveRecord::Base.default_timezone == :utc ? value.getutc : value.getlocal "#{value.strftime("%Y-%m-%d %H:%M:%S")}.#{usec}" else super end end |
#recreate_database(name = nil, options = {}) ⇒ Object
679 680 681 |
# File 'lib/arjdbc/db2/adapter.rb', line 679 def recreate_database(name = nil, = {}) drop_database(name) end |
#remove_column(table_name, *column_names) ⇒ Object
622 623 624 |
# File 'lib/arjdbc/db2/adapter.rb', line 622 def remove_column(table_name, column_name, type = nil, = {}) db2_remove_column(table_name, column_name) end |
#remove_index!(table_name, index_name) ⇒ Object
570 571 572 |
# File 'lib/arjdbc/db2/adapter.rb', line 570 def remove_index!(table_name, index_name) execute "DROP INDEX #{quote_column_name(index_name)}" end |
#rename_column(table_name, column_name, new_column_name) ⇒ Object
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/t0020130.html ...not supported on IBM i, so we raise in this case
576 577 578 579 |
# File 'lib/arjdbc/db2/adapter.rb', line 576 def rename_column(table_name, column_name, new_column_name) #:nodoc: sql = "ALTER TABLE #{table_name} RENAME COLUMN #{column_name} TO #{new_column_name}" execute_table_change(sql, table_name, 'Rename Column') end |
#rename_table(name, new_name) ⇒ Object
640 641 642 643 |
# File 'lib/arjdbc/db2/adapter.rb', line 640 def rename_table(name, new_name) # http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000980.html execute_table_change("RENAME TABLE #{name} TO #{new_name}", new_name, 'Rename Table') end |
#reset_column_information ⇒ Object
207 208 209 |
# File 'lib/arjdbc/db2/adapter.rb', line 207 def reset_column_information initialize_type_map(type_map) end |
#runstats_for_table(tablename, priority = 10) ⇒ Object
seems not sued nor tested ?!
522 523 524 |
# File 'lib/arjdbc/db2/adapter.rb', line 522 def runstats_for_table(tablename, priority = 10) @connection.execute_update "call sysproc.admin_cmd('RUNSTATS ON TABLE #{tablename} WITH DISTRIBUTION AND DETAILED INDEXES ALL UTIL_IMPACT_PRIORITY #{priority}')" end |
#schema ⇒ Object
756 757 758 |
# File 'lib/arjdbc/db2/adapter.rb', line 756 def schema db2_schema end |
#schema=(schema) ⇒ Object
760 761 762 |
# File 'lib/arjdbc/db2/adapter.rb', line 760 def schema=(schema) set_schema(@db2_schema = schema) if db2_schema != schema end |
#select(sql, name = nil, binds = []) ⇒ Object
528 529 530 |
# File 'lib/arjdbc/db2/adapter.rb', line 528 def select(sql, name = nil, binds = []) exec_query(to_sql(suble_null_test(sql), binds), name, binds) end |
#supports_views? ⇒ Boolean
692 |
# File 'lib/arjdbc/db2/adapter.rb', line 692 def supports_views?; true end |
#table_definition(*args) ⇒ Object
270 271 272 |
# File 'lib/arjdbc/db2/adapter.rb', line 270 def table_definition(*args) new_table_definition(TableDefinition, *args) end |
#tables ⇒ Object
645 646 647 |
# File 'lib/arjdbc/db2/adapter.rb', line 645 def tables @connection.tables(nil, schema) end |
#truncate(table_name, name = nil) ⇒ Object
687 688 689 |
# File 'lib/arjdbc/db2/adapter.rb', line 687 def truncate(table_name, name = nil) execute "TRUNCATE TABLE #{quote_table_name(table_name)} IMMEDIATE", name end |
#type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object
442 443 444 445 |
# File 'lib/arjdbc/db2/adapter.rb', line 442 def type_to_sql(type, limit = nil, precision = nil, scale = nil) limit = nil if type.to_sym == :integer super(type, limit, precision, scale) end |
#update_lob_value?(value, column = nil) ⇒ Boolean
96 97 98 |
# File 'lib/arjdbc/db2/adapter.rb', line 96 def update_lob_value?(value, column = nil) DB2.update_lob_values? && ! prepared_statements? # && value end |