Module: ArJdbc::DB2

Defined in:
lib/arjdbc/db2/adapter.rb

Defined Under Namespace

Modules: Column

Constant Summary collapse

HAVE_LIMIT =

only record precision and scale for types that can set them via CREATE TABLE: 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)

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.column_selector ⇒ Object 



3
4
5
6
 
# File 'lib/arjdbc/db2/adapter.rb', line 3

def self.column_selector
  [ /(db2|as400)/i,
    lambda { |cfg, column| column.extend(::ArJdbc::DB2::Column) } ]
end

Instance Method Details

#_execute(sql, name = nil) ⇒ Object 



89
90
91
92
93
94
95
96
97
 
# File 'lib/arjdbc/db2/adapter.rb', line 89

def _execute(sql, name = nil)
  if ActiveRecord::ConnectionAdapters::JdbcConnection::select?(sql)
    @connection.execute_query(sql)
  elsif ActiveRecord::ConnectionAdapters::JdbcConnection::insert?(sql)
    (@connection.execute_insert(sql) or last_insert_id(sql)).to_i
  else
    @connection.execute_update(sql)
  end
end

#adapter_name ⇒ Object 



137
138
139
 
# File 'lib/arjdbc/db2/adapter.rb', line 137

def adapter_name
  'DB2'
end

#add_limit_offset!(sql, options) ⇒ Object 



141
142
143
144
145
146
147
148
149
150
151
152
153
154
 
# File 'lib/arjdbc/db2/adapter.rb', line 141

def add_limit_offset!(sql, options)
  limit, offset = options[:limit], options[:offset]
  if limit && !offset
    if limit == 1
      sql << " FETCH FIRST ROW ONLY"
    else
      sql << " FETCH FIRST #{sanitize_limit(limit)} ROWS ONLY"
    end
  elsif limit && offset
    sql.gsub!(/SELECT/i, 'SELECT B.* FROM (SELECT A.*, row_number() over () AS internal$rownum FROM (SELECT')
    sql << ") A ) B WHERE B.internal$rownum > #{offset} AND B.internal$rown
um <= #{sanitize_limit(limit) + offset}"
  end
end

#add_quotes(name) ⇒ Object 



309
310
311
312
 
# File 'lib/arjdbc/db2/adapter.rb', line 309

def add_quotes(name)
  return name unless name
  %Q{"#{name}"}
end

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



243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
 
# File 'lib/arjdbc/db2/adapter.rb', line 243

def change_column(table_name, column_name, type, options = {})
  data_type = type_to_sql(type, options[:limit], options[:precision], options[:scale])
  sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET DATA TYPE #{data_type}"
  as400? ? execute_and_auto_confirm(sql) : execute(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, 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, 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, options[:null])
  end
end

#change_column_default(table_name, column_name, default) ⇒ Object 



234
235
236
237
238
239
240
241
 
# File 'lib/arjdbc/db2/adapter.rb', line 234

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 #{quote(default)}"
  end
  reorg_table(table_name)
end

#change_column_null(table_name, column_name, null) ⇒ Object 



225
226
227
228
229
230
231
232
 
# File 'lib/arjdbc/db2/adapter.rb', line 225

def change_column_null(table_name, column_name, null)
  if null
    execute_and_auto_confirm "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} DROP NOT NULL"
  else
    execute_and_auto_confirm "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET NOT NULL"
  end
  reorg_table(table_name)
end

#columns(table_name, name = nil) ⇒ Object 



290
291
292
293
294
295
296
297
298
299
300
301
302
303
 
# File 'lib/arjdbc/db2/adapter.rb', line 290

def columns(table_name, name = nil)
  cols = @connection.columns(table_name, name, db2_schema)

  # scrub out sizing info when CREATE TABLE doesn't support it
  # but JDBC reports it (doh!)
  for col in cols
    base_sql_type = col.sql_type.sub(/\(.*/, "").upcase
    col.limit = nil unless HAVE_LIMIT.include?(base_sql_type)
    col.precision = nil unless HAVE_PRECISION.include?(base_sql_type)
    #col.scale = nil unless HAVE_SCALE.include?(base_sql_type)
  end

  cols
end

#execute_and_auto_confirm(sql) ⇒ Object

holy moly batman! all this to tell AS400 “yes i am sure”



100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
# File 'lib/arjdbc/db2/adapter.rb', line 100

def execute_and_auto_confirm(sql)
  begin
    @connection.execute_update "call qsys.qcmdexc('QSYS/CHGJOB INQMSGRPY(*SYSRPYL)',0000000031.00000)"
    @connection.execute_update "call qsys.qcmdexc('ADDRPYLE SEQNBR(9876) MSGID(CPA32B2) RPY(''I'')',0000000045.00000)"
  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
    @connection.execute_update "call qsys.qcmdexc('QSYS/CHGJOB INQMSGRPY(*DFT)',0000000027.00000)"
    @connection.execute_update "call qsys.qcmdexc('RMVRPYLE SEQNBR(9876)',0000000021.00000)"
  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

#expand_double_quotes(name) ⇒ Object 



320
321
322
323
 
# File 'lib/arjdbc/db2/adapter.rb', line 320

def expand_double_quotes(name)
  return name unless name && name['"']
  name.gsub(/"/,'""')
end

#indexes(table_name, name = nil) ⇒ Object 



305
306
307
 
# File 'lib/arjdbc/db2/adapter.rb', line 305

def indexes(table_name, name = nil)
  @connection.indexes(table_name, name, db2_schema)
end

#last_insert_id(sql) ⇒ Object 



121
122
123
124
125
126
127
 
# File 'lib/arjdbc/db2/adapter.rb', line 121

def last_insert_id(sql)
  table_name = sql.split(/\s/)[2]
  result = select(ActiveRecord::Base.send(:sanitize_sql,
      %[select IDENTITY_VAL_LOCAL() as last_insert_id from #{table_name}],
      nil))
  result.last['last_insert_id']
end

#modify_types(tp) ⇒ Object 



129
130
131
132
133
134
135
 
# File 'lib/arjdbc/db2/adapter.rb', line 129

def modify_types(tp)
  tp[:primary_key] = 'int not null generated by default as identity (start with 1) primary key'
  tp[:string][:limit] = 255
  tp[:integer] = {:limit => nil}
  tp[:boolean] = {:name => "smallint"}
  tp
end

#pk_and_sequence_for(table) ⇒ Object 



156
157
158
159
160
161
162
163
164
 
# File 'lib/arjdbc/db2/adapter.rb', line 156

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

#quote(value, column = nil) ⇒ Object

:nodoc:



170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
 
# File 'lib/arjdbc/db2/adapter.rb', line 170

def quote(value, column = nil) # :nodoc:
  if column && column.respond_to?(:primary) && column.primary && column.klass != String
    return value.to_i.to_s
  end
  if column && (column.type == :decimal || column.type == :integer) && value
    return value.to_s
  end
  case value
  when String
    if column && column.type == :binary
      "BLOB('#{quote_string(value)}')"
    else
      "'#{quote_string(value)}'"
    end
  else super
  end
end

#quote_column_name(column_name) ⇒ Object 



166
167
168
 
# File 'lib/arjdbc/db2/adapter.rb', line 166

def quote_column_name(column_name)
  column_name
end

#quote_string(string) ⇒ Object 



188
189
190
 
# File 'lib/arjdbc/db2/adapter.rb', line 188

def quote_string(string)
  string.gsub(/'/, "''") # ' (for ruby-mode)
end

#quoted_false ⇒ Object 



196
197
198
 
# File 'lib/arjdbc/db2/adapter.rb', line 196

def quoted_false
  '0'
end

#quoted_true ⇒ Object 



192
193
194
 
# File 'lib/arjdbc/db2/adapter.rb', line 192

def quoted_true
  '1'
end

#recreate_database(name) ⇒ Object 



206
207
208
 
# File 'lib/arjdbc/db2/adapter.rb', line 206

def recreate_database(name)
  tables.each {|table| drop_table("#{db2_schema}.#{table}")}
end

#remove_column(table_name, column_name) ⇒ Object

publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/t0020132.html



266
267
268
269
270
271
 
# File 'lib/arjdbc/db2/adapter.rb', line 266

def remove_column(table_name, column_name) #:nodoc:
  sql = "ALTER TABLE #{table_name} DROP COLUMN #{column_name}"

  as400? ? execute_and_auto_confirm(sql) : execute(sql)
  reorg_table(table_name)
end

#remove_index(table_name, options = { }) ⇒ Object 



210
211
212
 
# File 'lib/arjdbc/db2/adapter.rb', line 210

def remove_index(table_name, options = { })
  execute "DROP INDEX #{quote_column_name(index_name(table_name, options))}"
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object

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



216
217
218
219
220
221
222
223
 
# File 'lib/arjdbc/db2/adapter.rb', line 216

def rename_column(table_name, column_name, new_column_name) #:nodoc:
  if as400?
    raise NotImplementedError, "rename_column is not supported on IBM i"
  else
    execute "ALTER TABLE #{table_name} RENAME COLUMN #{column_name} TO #{new_column_name}"
    reorg_table(table_name)
  end
end

#rename_table(name, new_name) ⇒ Object

publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000980.html



274
275
276
277
 
# File 'lib/arjdbc/db2/adapter.rb', line 274

def rename_table(name, new_name) #:nodoc:
  execute "RENAME TABLE #{name} TO #{new_name}"
  reorg_table(new_name)
end

#reorg_table(table_name) ⇒ Object 



200
201
202
203
204
 
# File 'lib/arjdbc/db2/adapter.rb', line 200

def reorg_table(table_name)
  unless as400?
    @connection.execute_update "call sysproc.admin_cmd ('REORG TABLE #{table_name}')"
  end
end

#strip_quotes(str) ⇒ Object 



314
315
316
317
318
 
# File 'lib/arjdbc/db2/adapter.rb', line 314

def strip_quotes(str)
  return str unless str
  return str unless /^(["']).*\1$/ =~ str
  str[1..-2]
end

#structure_dump ⇒ Object

:nodoc:



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
 
# File 'lib/arjdbc/db2/adapter.rb', line 325

def structure_dump #:nodoc:
  definition=""
  rs = @connection.connection.meta_data.getTables(nil,db2_schema.upcase,nil,["TABLE"].to_java(:string))
  while rs.next
    tname = rs.getString(3)
    definition << "CREATE TABLE #{tname} (\n"
    rs2 = @connection.connection.meta_data.getColumns(nil,db2_schema.upcase,tname,nil)
    first_col = true
    while rs2.next
      col_name = add_quotes(rs2.getString(4));
      default = ""
      d1 = rs2.getString(13)
      # IBM i (as400 toolbox driver) will return an empty string if there is no default
      if @config[:url] =~ /^jdbc:as400:/
        default = !d1.blank? ? " DEFAULT #{d1}" : ""
      else
        default = d1 ? " DEFAULT #{d1}" : ""
      end

      type = rs2.getString(6)
      col_precision = rs2.getString(7)
      col_scale = rs2.getString(9)
      col_size = ""
      if HAVE_SCALE.include?(type) and col_scale
        col_size = "(#{col_precision},#{col_scale})"
      elsif (HAVE_LIMIT + HAVE_PRECISION).include?(type) and col_precision
        col_size = "(#{col_precision})"
      end
      nulling = (rs2.getString(18) == 'NO' ? " NOT NULL" : "")
      create_col_string = add_quotes(expand_double_quotes(strip_quotes(col_name))) +
        " " +
        type +
        col_size +
        "" +
        nulling +
        default
      if !first_col
        create_col_string = ",\n #{create_col_string}"
      else
        create_col_string = " #{create_col_string}"
      end

      definition << create_col_string

      first_col = false
    end
    definition << ");\n\n"
  end
  definition
end

#tables ⇒ Object 



279
280
281
 
# File 'lib/arjdbc/db2/adapter.rb', line 279

def tables
  @connection.tables(nil, db2_schema, nil, ["TABLE"])
end