Class: ActiveRecord::ConnectionAdapters::IBM_DB2_I5

Inherits:
IBM_DB2 show all
Includes:
HostedDataServer
Defined in:
lib/active_record/connection_adapters/ibm_db_adapter.rb

Overview

class IBM_DB2_ZOS_8

Constant Summary collapse

TIME_SEP_CHAR =
{
  "#{IBM_DB::SQL_SEP_COLON}" => ':',
  "#{IBM_DB::SQL_SEP_PERIOD}" => '.',
  "#{IBM_DB::SQL_SEP_COMMA}" => ',',
  "#{IBM_DB::SQL_SEP_BLANK}" => ' ',
  "#{IBM_DB::SQL_SEP_JOB}" => ':' # TODO get separator for job, although Rails is the job
}
@@open_classing_done =

T032: add column with name of id to table with no index fails this strategy is an attempt to keep PowerRuby’s open classing to IBM_DB2_I5

false

Constants included from HostedDataServer

HostedDataServer::RESERVED_WORDS

Class Attribute Summary collapse

Instance Method Summary collapse

Methods included from HostedDataServer

#check_reserved_words

Methods inherited from IBM_DB2

#get_datetime_mapping, #get_double_mapping, #get_time_mapping, #last_generated_id, #primary_key_definition, #query_offset_limit, #query_offset_limit!, #rename_column, #set_binary_default, #set_binary_value, #set_case, #set_text_default

Methods inherited from IBM_DataServer

#check_reserved_words, #create_index_after_table, #execute, #get_datetime_mapping, #get_double_mapping, #get_time_mapping, #last_generated_id, #limit_not_supported_types, #prepare, #query_offset_limit, #query_offset_limit!, #reorg_table, #select, #select_rows, #set_binary_default, #set_binary_value, #set_case, #set_schema, #set_text_default, #setup_for_lob_table

Constructor Details

#initialize(adapter, ar3) ⇒ IBM_DB2_I5

Returns a new instance of IBM_DB2_I5.


3198
3199
3200
3201
3202
3203
3204
3205
3206
3207
3208
3209
3210
3211
3212
3213
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3198

def initialize(adapter, ar3)
  super(adapter, ar3)
  return if self.class.open_classing_done

  # Rails 4 added SchemaCreation
  if defined? ActiveRecord::ConnectionAdapters::AbstractAdapter::SchemaCreation
    ActiveRecord::ConnectionAdapters::AbstractAdapter::SchemaCreation.class_eval do
      def visit_AddColumn(o)
        sql_type = type_to_sql(o.type.to_sym, o.limit, o.precision, o.scale)
        sql = "ADD COLUMN #{quote_column_name(o.name)} #{sql_type}"
        add_column_options!(sql, column_options(o))
      end
    end
  end
  self.class.open_classing_done = true
end

Class Attribute Details

.open_classing_doneObject

Returns the value of attribute open_classing_done.


3196
3197
3198
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3196

def open_classing_done
  @open_classing_done
end

Instance Method Details

#change_column(table_name, column_name, type, options = {}) ⇒ Object


3362
3363
3364
3365
3366
3367
3368
3369
3370
3371
3372
3373
3374
3375
3376
3377
3378
3379
3380
3381
3382
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3362

def change_column(table_name, column_name, type, options = {})
  data_type = @adapter.type_to_sql(type, options[:limit], options[:precision], options[:scale])
  sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET DATA TYPE #{data_type}"
  execute_and_auto_confirm(sql)
  reorg_table(table_name)

  if options.include?(:default) and options.include?(:null)
    # which to run first?
    if options[:null] or options[:default].nil?
      change_column_null(table_name, column_name, type, options[:null])
      change_column_default(table_name, column_name, options[:default])
    else
      change_column_default(table_name, column_name, options[:default])
      change_column_null(table_name, column_name, type, options[:null])
    end
  elsif options.include?(:default)
    change_column_default(table_name, column_name, options[:default])
  elsif options.include?(:null)
    change_column_null(table_name, column_name, type, options[:null])
  end
end

#change_column_default(table_name, column_name, default) ⇒ Object


3353
3354
3355
3356
3357
3358
3359
3360
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3353

def change_column_default(table_name, column_name, default)
  if default.nil?
    execute_and_auto_confirm "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} DROP DEFAULT"
  else
    execute_and_auto_confirm "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET WITH DEFAULT #{@adapter.quote(default)}"
  end
  reorg_table(table_name)
end

#change_column_null(table_name, column_name, null, default) ⇒ Object


3334
3335
3336
3337
3338
3339
3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3334

def change_column_null(table_name, column_name, null, default)
  if null && !default.nil?
    change_column_default(table_name, column_name, default)
  end 

  if !null.nil?
    if null
      change_column_sql = "ALTER TABLE #{table_name} ALTER #{column_name} DROP NOT NULL"
    else
      change_column_sql = "ALTER TABLE #{table_name} ALTER #{column_name} SET NOT NULL with DEFAULT #{default}"
    end
    stmt = execute_and_auto_confirm (change_column_sql)
    reorg_table(table_name)
  end

  ensure
    IBM_DB.free_stmt(stmt) if stmt
end

#columns(table_name, name = nil) ⇒ Object


3284
3285
3286
3287
3288
3289
3290
3291
3292
3293
3294
3295
3296
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3284

def columns(table_name, name = nil)
  columns = super(table_name, name)
  # need a each modified version of below ...
  # IBM i old PTF IBM i returns 'TOAD,
  # but Arabic, Hebrew need single quote start, 
  # so hack below not work (maybe add anyway?)  
  # unless column_default_value.nil?
  #   if column_default_value[0] == "'"
  #     column_default_value = column_default_value[1..-1]
  #   end
  # end
  columns
end

#columns_for_distinct(columns, orders) ⇒ Object

T009: IBM DB requires the ORDER BY columns in the select list for distinct queries, and requires that the ORDER BY include the distinct column.


3227
3228
3229
3230
3231
3232
3233
3234
3235
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3227

def columns_for_distinct(columns, orders)
  order_columns = orders.map{ |s|
      # Convert Arel node to string
      s = s.to_sql unless s.is_a?(String)
      # Remove any ASC/DESC modifiers
      s.gsub(/\s+(ASC|DESC)\s*(NULLS\s+(FIRST|LAST)\s*)?/i, '')
    }.reject(&:blank?).map.with_index { |column, i| "#{column} AS alias_#{i}" }
  [columns, *order_columns].join(', ')
end

#create_database(dbName, codeSet = nil, mode = nil) ⇒ Object


3404
3405
3406
3407
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3404

def create_database(dbName, codeSet=nil, mode=nil)
  execute("CREATE COLLECTION #{dbName}")
  true
end

#drop_database(dbName) ⇒ Object


3384
3385
3386
3387
3388
3389
3390
3391
3392
3393
3394
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3384

def drop_database(dbName)
  qcmdexc2 "QSYS/CHGJOB INQMSGRPY(*SYSRPYL)"
  qcmdexc2 "ADDRPYLE SEQNBR(4321) MSGID(CPA7025) RPY(''I'')"
  isolation = IBM_DB.getoption(@adapter.connection,IBM_DB::SQL_ATTR_TXN_ISOLATION,1)
  IBM_DB.setoption(@adapter.connection,{IBM_DB::SQL_ATTR_TXN_ISOLATION=>IBM_DB::SQL_TXN_NO_COMMIT},1)
  execute "DROP COLLECTION #{dbName}"
  qcmdexc2 "QSYS/CHGJOB INQMSGRPY(*DFT)"
  qcmdexc2 "RMVRPYLE SEQNBR(4321)"
  IBM_DB.setoption(@adapter.connection,{IBM_DB::SQL_ATTR_TXN_ISOLATION=>isolation},1)
  true
end

#error_number(messsage) ⇒ Object

:nodoc:


3249
3250
3251
3252
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3249

def error_number(messsage) # :nodoc:
  m = /(SQLCODE=)(-\d{1,6})/.match(messsage)
  return m[2].to_i if m
end

#execute_and_auto_confirm(sql) ⇒ Object


3313
3314
3315
3316
3317
3318
3319
3320
3321
3322
3323
3324
3325
3326
3327
3328
3329
3330
3331
3332
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3313

def execute_and_auto_confirm(sql)
  begin
    qcmdexc2 "QSYS/CHGJOB INQMSGRPY(*SYSRPYL)"
    qcmdexc2 "ADDRPYLE SEQNBR(9876) MSGID(CPA32B2) RPY(''I'')"
  rescue Exception => e
    raise "Could not call CHGJOB INQMSGRPY(*SYSRPYL) and ADDRPYLE SEQNBR(9876) MSGID(CPA32B2) RPY('I').\n" +
          "Do you have authority to do this?\n\n" + e.to_s
  end

  r = execute sql

  begin
    qcmdexc2 "QSYS/CHGJOB INQMSGRPY(*DFT)'"
    qcmdexc2 "RMVRPYLE SEQNBR(9876)"
  rescue Exception => e
    raise "Could not call CHGJOB INQMSGRPY(*DFT) and RMVRPYLE SEQNBR(9876).\n" +
          "Do you have authority to do this?\n\n" + e.to_s
  end
  r
end

#indexes(table_name, name = nil) ⇒ Object

Returns an array of non-primary key indexes for a specified table name


3271
3272
3273
3274
3275
3276
3277
3278
3279
3280
3281
3282
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3271

def indexes(table_name, name = nil)
  # IBM i issue with fetch lob when autocommit=off (DB2 PTF not applied all machines)
  hackibmi = IBM_DB.autocommit(@adapter.connection) == IBM_DB::SQL_AUTOCOMMIT_OFF
  if hackibmi == true
    IBM_DB.autocommit @adapter.connection, IBM_DB::SQL_AUTOCOMMIT_ON
  end
  indexes = super(table_name, name)
  if hackibmi == true
    IBM_DB.autocommit @adapter.connection, IBM_DB::SQL_AUTOCOMMIT_OFF
  end
  indexes
end

#qcmdexc(cmd) ⇒ Object


3298
3299
3300
3301
3302
3303
3304
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3298

def qcmdexc(cmd)
  occur = cmd.count("''")
  occur = occur/2
  len = cmd.length - occur
  sql = "CALL QSYS2.QCMDEXC('#{cmd}',#{len})"
  execute sql
end

#qcmdexc2(cmd) ⇒ Object


3306
3307
3308
3309
3310
3311
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3306

def qcmdexc2(cmd)
  begin
    qcmdexc(cmd)
  rescue Exception => e
  end
end

#quote_date_time_for_pstmt(value, column = nil) ⇒ Object


3254
3255
3256
3257
3258
3259
3260
3261
3262
3263
3264
3265
3266
3267
3268
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3254

def quote_date_time_for_pstmt(value, column=nil)
  if column && column.sql_type == 'time' # T006 time format
    sep = TIME_SEP_CHAR[@adapter.time_sep]
    value = value.to_s[/\d\d#{sep}\d\d#{sep}\d\d/] 
  elsif value.acts_like?(:date)
    if column && column.sql_type == 'timestamp' # T013 timestamp format
      value = value.to_time
    end
    value = @adapter.quoted_date(value)
  elsif value.acts_like?(:time)
    value = @adapter.quoted_date(value)
  else
    value.to_yaml
  end
end

#remove_column(table_name, column_name) ⇒ Object


3396
3397
3398
3399
3400
3401
3402
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3396

def remove_column(table_name,column_name)
  qcmdexc2 "QSYS/CHGJOB INQMSGRPY(*SYSRPYL)"
  qcmdexc2 "ADDRPYLE SEQNBR(9876) MSGID(CPA32B2) RPY(''I'')"
  super(table_name,column_name)
  qcmdexc2 "QSYS/CHGJOB INQMSGRPY(*DFT)"
  qcmdexc2 "RMVRPYLE SEQNBR(9876)"
end

#task_charset(task) ⇒ Object


3436
3437
3438
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3436

def task_charset(task)
  @adapter.connection.charset
end

#task_collation(task) ⇒ Object


3439
3440
3441
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3439

def task_collation(task)
  @adapter.connection.collation
end

#task_create(task) ⇒ Object


3418
3419
3420
3421
3422
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3418

def task_create(task)
  filename = task_schema(task)
  create_database filename
  task.log("CREATE SCHEMA #{filename}")
end

#task_drop(task) ⇒ Object


3423
3424
3425
3426
3427
3428
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3423

def task_drop(task)
  filename = task_schema(task)
  task_structure_dump(task,filename)
  drop_database filename
  task.log("DROP SCHEMA #{filename}")
end

#task_purge(task) ⇒ Object


3429
3430
3431
3432
3433
3434
3435
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3429

def task_purge(task)
  begin
    task_drop(task)
  rescue Exception => e
  end
  task_create(task)
end

#task_schema(task) ⇒ Object

Unit testing (SQLLiteDatabaseTasks, MySQLDatabaseTasks, PostgreSQLDatabaseTasks)


3412
3413
3414
3415
3416
3417
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3412

def task_schema(task)
  if task.configuration.has_key?('schema')
    return task.configuration['schema']
  end
  task.configuration['username'];
end

#task_structure_dump(task, filename = nil) ⇒ Object


3442
3443
3444
3445
3446
3447
3448
3449
3450
3451
3452
3453
3454
3455
3456
3457
3458
3459
3460
3461
3462
3463
3464
3465
3466
3467
3468
3469
3470
3471
3472
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3442

def task_structure_dump(task,filename=nil)
  if task.configuration.has_key?('ibm_i_dump_savf')
    if !task.configuration['ibm_i_dump_savf']
      return
    end
  end
  if filename.nil?
    filename = task_schema(task)
  end
  savlibtime = Time.now
  lastyear = Time.new(Time.now.year.to_i - 1)
  savf = "RB" + (Time.now.to_i - lastyear.to_i).to_s
  # create savlib
  begin
    cmd = "CRTSAVF FILE(QGPL/#{savf}) TEXT(''#{filename} #{savlibtime}'')"
    qcmdexc cmd
    task.log(cmd)
  rescue Exception => e
    puts e.message
    task.log("#{cmd} : #{e.message}")
  end
  # save the library
  begin
    cmd = "SAVLIB LIB(#{filename}) DEV(*SAVF) SAVF(QGPL/#{savf}) CLEAR(*ALL)"
    qcmdexc cmd
    task.log(cmd)
  rescue Exception => e
    puts e.message
    task.log("#{cmd} : #{e.message}")
  end
end

#task_structure_load(task, filename = nil) ⇒ Object


3473
3474
3475
3476
3477
3478
3479
3480
3481
3482
3483
3484
3485
3486
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3473

def task_structure_load(task,filename=nil)
  if filename.nil?
    filename = task_schema(task)
  end
  # restore the library
  begin
    cmd = "RSTLIB SAVLIB(#{filename}) DEV(*SAVF) SAVF(QGPL/#{filename}) MBROPT(*ALL) ALWOBJDIF(*ALL)"
    qcmdexc cmd
    task.log(cmd)
  rescue Exception => e
    puts e.message
    task.log("#{cmd} : #{e.message}")
  end
end

#translate_exception(exception, message) ⇒ Object

T017|T023: raise the appropriate error


3238
3239
3240
3241
3242
3243
3244
3245
3246
3247
3248
# File 'lib/active_record/connection_adapters/ibm_db_adapter.rb', line 3238

def translate_exception(exception, message)
  case error_number(message)
  when -803
    RecordNotUnique.new(message, exception)
  when -530
    InvalidForeignKey.new(message, exception)
  else
    # would just be "super(x,y)" if this code was in IBM_DBAdapter
    @adapter.class.superclass.instance_method(:translate_exception).bind(@adapter).call(message, exception)
  end
end