Class: ActiveRecord::ConnectionAdapters::IBM_DB2Adapter

Inherits:
AbstractAdapter show all
Defined in:
lib/connection_adapters/ibm_db2_adapter.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(connection, logger, config) ⇒ IBM_DB2Adapter

Returns a new instance of IBM_DB2Adapter.



184
185
186
187
188
189
190
 
# File 'lib/connection_adapters/ibm_db2_adapter.rb', line 184

def initialize(connection, logger, config)
  if (config[:platform] == "iseries")
     @iseries = true
     logger.info("DRYSQL >> iSeries platform detected")
  end
  base_initialize(connection, logger, config)
end

Instance Attribute Details

#iseries ⇒ Object (readonly)

Returns the value of attribute iseries.



181
182
183
 
# File 'lib/connection_adapters/ibm_db2_adapter.rb', line 181

def iseries
  @iseries
end

Instance Method Details

#base_columns ⇒ Object 



193
 
# File 'lib/connection_adapters/ibm_db2_adapter.rb', line 193

alias :base_columns :columns

#base_indexes ⇒ Object 



242
 
# File 'lib/connection_adapters/ibm_db2_adapter.rb', line 242

alias :base_indexes :indexes

#base_initialize ⇒ Object 



183
 
# File 'lib/connection_adapters/ibm_db2_adapter.rb', line 183

alias :base_initialize :initialize

#base_last_generated_id ⇒ Object 



412
 
# File 'lib/connection_adapters/ibm_db2_adapter.rb', line 412

alias :base_last_generated_id :last_generated_id

#columns(table_name, name = nil) ⇒ Object

Returns an array of Column objects for the table specified by table_name This method re-definition is valid only for iSeries



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
 
# File 'lib/connection_adapters/ibm_db2_adapter.rb', line 196

def columns(table_name, name = nil)
  # to_s required because it may be a symbol.
  table_name = table_name.to_s.upcase
  # Checks if a blank table name has been given. 
  # If so it returns an empty array
  return [] if table_name.strip.empty?
  # +columns+ will contain the resulting array
  columns = []
  if @iseries
    sql = "SELECT column_name, dftvalue as column_default, coltype as type, length, nulls, generated FROM SYSCOLUMNS WHERE TBNAME = '#{table_name}' AND DBNAME = '#{@schema.upcase}' ORDER BY colno"
  else
    sql = "SELECT colname as column_name, default as column_default, typename as type, length, nulls, generated FROM SYSCAT.COLUMNS WHERE TABNAME = '#{table_name}' AND TABSCHEMA = '#{@schema.upcase}' ORDER BY colno"
  end
  # Statement required to access all the columns information
  stmt = execute(sql, name)
    # Fetches all the columns and assigns them to col.
    # +col+ is an hash with keys/value pairs for a column
    while col = DB2::fetch_assoc(stmt)
        column_name = col["column_name"].downcase
        # Assigns the column default value.
        column_default_value = col["column_default"]
        # If there is no default value, it assigns NIL
        column_default_value = nil if (column_default_value && column_default_value.upcase == 'NULL')
        # Removes single quotes from the default value
        column_default_value.gsub!(/^'(.*)'$/, '\1') unless column_default_value.nil?
        # Assigns the column type
        column_type = col["type"].downcase
        # Assigns the field length (size) for the column
        column_length = col["length"]
        # The initializer of the class Column, requires the +column_length+ to be declared between brackets after
        # the datatype(e.g VARCHAR(50)) for :string and :text types. If it's a "for bit data" field it does a subsitution in place, if not
        # it appends the (column_length) string on the supported data types
        unless column_length.nil? || column_length == '' || column_type.sub!(/ \(\) for bit data/i,"(#{column_length}) FOR BIT DATA") || !column_type =~ /char|lob|graphic/i
          column_type << "(#{column_length})"
        end
        # col["NULLABLE"] is 1 if the field is nullable, 0 if not.
        column_nullable = col["nulls"] == 'Y' ? true : false
        generated = col["generated"]
        # Pushes into the array the *IBM_DB2Column* object, created by passing to the initializer
        # +column_name+, +default_value+, +column_type+ and +column_nullable+.
        columns << IBM_DB2Column.new(column_name, column_default_value, generated, column_type, column_nullable)
    end
  # Returns the columns array
  columns
end

#constraints(table_name, name = nil) ⇒ Object

DrySQL queries the current schema’s System Catalog Views, which contain metadata only for those constraints that are defined in the current schema.

If you have inter-schema referential constraints, and I’m not sure why you would or whether DB2 even supports this, DrySQL will not detect them and will raise an error.



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
 
# File 'lib/connection_adapters/ibm_db2_adapter.rb', line 297

def constraints(table_name, name = nil)#:nodoc:
  constraints = []  
  if @iseries
    return iseries_constraints(table_name, name)
  else
    sql = "select CST.tabschema, CST.constname, CST.tabname, CST.type, COL.colname, \
      REF.constname as foreign_constraint_name, REF.tabname as foreign_table_name, \
      REF.refkeyname, REF.reftabname, REF.deleterule, REF.fk_colnames as foreign_columns, \
      REF.pk_colnames as referenced_columns from \
      ((select * from SYSCAT.TABCONST where tabname='#{table_name}') as CST \
      inner join (select colname, constname from SYSCAT.KEYCOLUSE where tabname='#{table_name}') \
      as COL on (CST.constname=COL.constname)) left outer join SYSCAT.REFERENCES REF on \
      (CST.constname=REF.refkeyname or CST.constname=REF.constname)"
  end       
  results = execute(sql, name)
  constraint_name_hash = {}
    
  # Note that column names in constraint objects are downcased in order to
  # be comparable with the column names produced by IBM_DB2Adapter.columns  
  while row = DB2::fetch_assoc(results)
    constraint_name = row['constname']
    foreign_constraint_name = row['foreign_constraint_name']

    # Process constraints local to this table
    if !constraint_name_hash.has_key?(constraint_name)
      current_constraint = IBM_DB2Constraint.new(row['tabschema'], constraint_name, row['type'], 
        row['tabname'], row['colname'], row['refkeyname'], row['reftabname'], 
        row['referenced_columns'], row['deleterule'])
      constraints << current_constraint
      constraint_name_hash[constraint_name] = current_constraint
    # This key is a composite
    else
      current_constraint = constraint_name_hash[constraint_name]
      # Unique Keys are currently the only type of composite keys supported
      if current_constraint.component_of_unique_key? 
        current_constraint.column_name.add(row['colname']) 
      end
    end
    
    # Process constraints that reference this table's local constraints
    if !foreign_constraint_name.nil? && !constraint_name_hash.has_key?(foreign_constraint_name)
      current_foreign_constraint = IBM_DB2Constraint.new(row['tabschema'], foreign_constraint_name, IBM_DB2Constraint::FOREIGN_KEY_TYPE,
        row['foreign_table_name'], row['foreign_columns'], constraint_name, row['tabname'], row['colname'],
        row['deleterule'])
        constraints << current_foreign_constraint
        constraint_name_hash[foreign_constraint_name] = current_foreign_constraint
    # Composite FKs are currently not supported 
    # else
    #  constraint_name_hash[foreign_constraint_name].column_name.add(row['foreign_column_name'])
    end        
  end
  constraints
end

#indexes(table_name, name = nil) ⇒ Object

Returns an array of indexes for the given table, skipping the primary key. The current implementation accesses SYSINDEXES and QSYS2.SYSKEYS. This method is valid only for iSeries



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
 
# File 'lib/connection_adapters/ibm_db2_adapter.rb', line 246

def indexes(table_name, name = nil)
  if !@iseries then return base_indexes(table_name, name) end
  
  # to_s required because +table_name+ may be a symbol.
  table_name = table_name.to_s
  # Checks if a blank table name has been given. 
  # If so it returns an empty array of columns.
  return [] if table_name.strip.empty?
  # +indexes+ will contain the resulting array
  indexes = []
  # Query used to retrieve all the indexes from the given table
  sql = "select * from ((SELECT index_name, is_unique FROM SYSINDEXES WHERE table_name = '#{table_name.upcase}' AND table_schema = '#{@schema.upcase}') \
    as IND inner join (select index_name, column_name from QSYS2.SYSKEYS where index_schema='#{@schema.upcase}') as KEYS on (IND.index_name=KEYS.index_name))"
  stmt = execute(sql, name)
  klass = instance_eval(ActiveRecord::Base.class_name(table_name))
  primary_key = klass.primary_key
  index_hash = {}
  # Fetches all the records available
  while table_index = DB2::fetch_assoc(stmt)
    # Gets the lowercased index name
    index_name = table_index["index_name"].downcase
    # Is the index a primary key?
    unless index_name.upcase == primary_key.upcase
      # Is the index unique?
      index_unique = table_index["is_unique"] == 'U'
      if index_hash[index_name].nil?
        index_hash[index_name] = [index_unique, [column_name]]
      else
        index_hash[index_name][1] << column_name
      end
    end
  end
  # Creates IndexDefinition objects and adds them to the indexes array
  index_hash.keys.each do |key|
    current_index = index_hash[key]
    indexes << IndexDefinition.new(table_name, key, current_index[0], current_index[1])
  end
          
  # Returns the indexes array
  return indexes      
  # Ensures to free the resources associated with the statement     
  ensure
    DB2::free_result(stmt) if stmt
end

#iseries_constraints(table_name, name = nil) ⇒ Object

This method retrieves constraint information from the iSeries information schema views, which DB2 generates automatically for a schema when it (the schema) is created.

Note that the old-style iSeries “libraries” that some people consider to by synonymous with “schemas” do not get the benefit of the information schema views (or journaling, for that matter). DrySQL will not play nicely with iSeries libraries unless you create the information schema views.



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
 
# File 'lib/connection_adapters/ibm_db2_adapter.rb', line 360

def iseries_constraints(table_name, name = nil)#:nodoc:
  constraints = []  
  sql = "select CST.constraint_schema, CST.constraint_name, CST.table_name, CST.constraint_type, COL.column_name, \
    REF.constraint_name as foreign_constraint_name, REF.unique_constraint_name as referenced_constraint_name, \
    REF.delete_rule, COLREF.table_name as foreign_table_name, COLREF.column_name as foreign_column_name from \
    ((select * from SYSCST where table_name='#{table_name}') as CST \
 inner join (select column_name, constraint_name from SYSCSTCOL where table_name='#{table_name}') \
 as COL on (CST.constraint_name=COL.constraint_name) left outer join SYSREFCST REF on \
 (CST.constraint_name=REF.unique_constraint_name or CST.constraint_name=REF.constraint_name) \
 left join SYSCSTCOL AS COLREF on (NOT COLREF.table_name='#{table_name}' AND \
 (REF.unique_constraint_name=COLREF.constraint_name or REF.constraint_name=COLREF.constraint_name)))"     
  results = execute(sql, name)
  constraint_name_hash = {}
    
  # Note that column names in constraint objects are downcased in order to
  # be comparable with the column names produced by IBM_DB2Adapter.columns  
  while row = DB2::fetch_assoc(results)
    constraint_name = row['constraint_name']
    foreign_constraint_name = row['foreign_constraint_name']

    # Process constraints local to this table
    if !constraint_name_hash.has_key?(constraint_name)
      current_constraint = IBM_DB2Constraint.new(row['constraint_schema'], constraint_name, row['constraint_type'], 
        row['table_name'], row['column_name'], row['referenced_constraint_name'], row['foreign_table_name'], 
        row['foreign_column_name'], row['delete_rule'])
      constraints << current_constraint
      constraint_name_hash[constraint_name] = current_constraint
    # This key is a composite
    else
      current_constraint = constraint_name_hash[constraint_name]
      # Unique Keys are currently the only type of composite keys supported
      if current_constraint.component_of_unique_key? 
        current_constraint.column_name.add(row['column_name']) 
      end
    end
    
    # Process constraints that reference this table's local constraints
    if !foreign_constraint_name.nil? && !constraint_name_hash.has_key?(foreign_constraint_name)
      current_foreign_constraint = IBM_DB2Constraint.new(row['constraint_schema'], constraint_name, IBM_DB2Constraint::FOREIGN_KEY_TYPE,
        row['foreign_table_name'], row['foreign_column_name'], foreign_constraint_name, row['table_name'], row['column_name'],
        row['delete_rule'])
        constraints << current_foreign_constraint
        constraint_name_hash[foreign_constraint_name] = current_foreign_constraint
    # Composite FKs are currently not supported 
    # else
    #  constraint_name_hash[foreign_constraint_name].column_name.add(row['foreign_column_name'])
    end        
  end
  constraints
end