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" }, :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 = {}) ⇒ Object
- #drop_database(name = nil) ⇒ 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
-
#runstats_for_table(tablename, priority = 10) ⇒ Object
deprecated
Deprecated.
seems not sued nor tested ?!
- #schema ⇒ Object
- #schema=(schema) ⇒ Object
- #select(sql, name, binds) ⇒ Object
- #supports_views? ⇒ Boolean
- #table_definition(*args) ⇒ Object
- #tables ⇒ 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
40 41 42 |
# File 'lib/arjdbc/db2/adapter.rb', line 40 def self.arel2_visitors(config) { 'db2' => arel_visitor_type } end |
.arel_visitor_type(config = nil) ⇒ Object
34 35 36 |
# File 'lib/arjdbc/db2/adapter.rb', line 34 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.
53 |
# File 'lib/arjdbc/db2/adapter.rb', line 53 def self.emulate_booleans; @@emulate_booleans; end |
.emulate_booleans=(emulate) ⇒ Object
55 |
# File 'lib/arjdbc/db2/adapter.rb', line 55 def self.emulate_booleans=(emulate); @@emulate_booleans = emulate; end |
.emulate_booleans? ⇒ Boolean
Boolean emulation can be disabled using :
ArJdbc::DB2.emulate_booleans = false
51 |
# File 'lib/arjdbc/db2/adapter.rb', line 51 def self.emulate_booleans?; @@emulate_booleans; end |
.jdbc_connection_class ⇒ Object
24 25 26 |
# File 'lib/arjdbc/db2/adapter.rb', line 24 def self.jdbc_connection_class ::ActiveRecord::ConnectionAdapters::DB2JdbcConnection end |
.update_lob_values=(update) ⇒ Object
68 |
# File 'lib/arjdbc/db2/adapter.rb', line 68 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
66 |
# File 'lib/arjdbc/db2/adapter.rb', line 66 def self.update_lob_values?; @@update_lob_values; end |
Instance Method Details
#adapter_name ⇒ Object
90 91 92 |
# File 'lib/arjdbc/db2/adapter.rb', line 90 def adapter_name ADAPTER_NAME end |
#add_column(table_name, column_name, type, options = {}) ⇒ Object
353 354 355 356 357 358 |
# File 'lib/arjdbc/db2/adapter.rb', line 353 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
360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 |
# File 'lib/arjdbc/db2/adapter.rb', line 360 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
443 444 445 446 447 448 449 450 451 452 453 454 455 |
# File 'lib/arjdbc/db2/adapter.rb', line 443 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.
386 387 388 |
# File 'lib/arjdbc/db2/adapter.rb', line 386 def add_limit_offset!(sql, ) replace_limit_offset!(sql, [:limit], [:offset]) end |
#change_column(table_name, column_name, type, options = {}) ⇒ Object
487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 |
# File 'lib/arjdbc/db2/adapter.rb', line 487 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
478 479 480 481 482 483 484 485 |
# File 'lib/arjdbc/db2/adapter.rb', line 478 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
469 470 471 472 473 474 475 476 |
# File 'lib/arjdbc/db2/adapter.rb', line 469 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
544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 |
# File 'lib/arjdbc/db2/adapter.rb', line 544 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
83 84 85 86 |
# File 'lib/arjdbc/db2/adapter.rb', line 83 def configure_connection schema = self.schema set_schema(schema) if schema && schema != config[:username] end |
#create_table(name, options = {}) ⇒ Object
190 191 192 193 194 195 196 |
# File 'lib/arjdbc/db2/adapter.rb', line 190 def create_table(name, = {}) if zos? zos_create_table(name, ) else super(name, ) end end |
#drop_database(name = nil) ⇒ Object
571 572 573 |
# File 'lib/arjdbc/db2/adapter.rb', line 571 def drop_database(name = nil) tables.each { |table| drop_table("#{table}") } end |
#execute_table_change(sql, table_name, name = nil) ⇒ Object (protected)
578 579 580 581 582 |
# File 'lib/arjdbc/db2/adapter.rb', line 578 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
563 564 565 |
# File 'lib/arjdbc/db2/adapter.rb', line 563 def indexes(table_name, name = nil) @connection.indexes(table_name, name, schema) end |
#jdbc_column_class ⇒ Object
29 30 31 |
# File 'lib/arjdbc/db2/adapter.rb', line 29 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.
596 597 598 |
# File 'lib/arjdbc/db2/adapter.rb', line 596 def last_insert_id @connection.identity_val_local end |
#modify_types(types) ⇒ Object
339 340 341 342 343 344 345 346 |
# File 'lib/arjdbc/db2/adapter.rb', line 339 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
115 116 117 118 119 120 121 |
# File 'lib/arjdbc/db2/adapter.rb', line 115 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
186 187 188 |
# File 'lib/arjdbc/db2/adapter.rb', line 186 def next_sequence_value(sequence_name) select_value("SELECT NEXT VALUE FOR #{sequence_name} FROM sysibm.sysdummy1") end |
#pk_and_sequence_for(table) ⇒ Object
250 251 252 253 254 255 256 257 258 |
# File 'lib/arjdbc/db2/adapter.rb', line 250 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
177 178 179 180 181 182 183 184 |
# File 'lib/arjdbc/db2/adapter.rb', line 177 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.
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 |
# File 'lib/arjdbc/db2/adapter.rb', line 264 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
335 336 337 |
# File 'lib/arjdbc/db2/adapter.rb', line 335 def quote_column_name(column_name) column_name.to_s end |
#quote_time(value) ⇒ Object
329 330 331 332 333 |
# File 'lib/arjdbc/db2/adapter.rb', line 329 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
319 320 321 322 323 324 325 326 327 |
# File 'lib/arjdbc/db2/adapter.rb', line 319 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
567 568 569 |
# File 'lib/arjdbc/db2/adapter.rb', line 567 def recreate_database(name = nil, = {}) drop_database(name) end |
#remove_column(table_name, *column_names) ⇒ Object
510 511 512 |
# File 'lib/arjdbc/db2/adapter.rb', line 510 def remove_column(table_name, column_name, type = nil, = {}) db2_remove_column(table_name, column_name) end |
#remove_index!(table_name, index_name) ⇒ Object
458 459 460 |
# File 'lib/arjdbc/db2/adapter.rb', line 458 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
464 465 466 467 |
# File 'lib/arjdbc/db2/adapter.rb', line 464 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
528 529 530 531 |
# File 'lib/arjdbc/db2/adapter.rb', line 528 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 |
#runstats_for_table(tablename, priority = 10) ⇒ Object
seems not sued nor tested ?!
434 435 436 |
# File 'lib/arjdbc/db2/adapter.rb', line 434 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
640 641 642 |
# File 'lib/arjdbc/db2/adapter.rb', line 640 def schema db2_schema end |
#schema=(schema) ⇒ Object
644 645 646 |
# File 'lib/arjdbc/db2/adapter.rb', line 644 def schema=(schema) set_schema(@db2_schema = schema) if db2_schema != schema end |
#select(sql, name, binds) ⇒ Object
438 439 440 441 |
# File 'lib/arjdbc/db2/adapter.rb', line 438 def select(sql, name, binds) # DB2 does not like "= NULL", "!= NULL", or "<> NULL". exec_query(to_sql(sql.gsub(/(!=|<>)\s*null/i, "IS NOT NULL").gsub(/=\s*null/i, "IS NULL"), binds), name, binds) end |
#supports_views? ⇒ Boolean
576 |
# File 'lib/arjdbc/db2/adapter.rb', line 576 def supports_views?; true end |
#table_definition(*args) ⇒ Object
173 174 175 |
# File 'lib/arjdbc/db2/adapter.rb', line 173 def table_definition(*args) new_table_definition(TableDefinition, *args) end |
#tables ⇒ Object
533 534 535 |
# File 'lib/arjdbc/db2/adapter.rb', line 533 def tables @connection.tables(nil, schema) end |
#type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object
348 349 350 351 |
# File 'lib/arjdbc/db2/adapter.rb', line 348 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
72 73 74 |
# File 'lib/arjdbc/db2/adapter.rb', line 72 def update_lob_value?(value, column = nil) DB2.update_lob_values? && ! prepared_statements? # && value end |