Class: IBRuby::InterBaseMetaFunctions

Inherits:
Object
  • Object
show all
Defined in:
lib/ibmeta.rb

Overview

InterBaseMetaFunctions Rather than requiring Ruby on Rails for access to these useful functions, I decided to move them here instead.

Constant Summary collapse

ASCENDING = 
:ASCENDING
DESCENDING = 
:DESCENDING

Class Method Summary collapse

Class Method Details

.db_type_cast(conn, column_type, column_value) ⇒ Object

def table_constraints



487
488
489
490
491
492
493
494
495
 
# File 'lib/ibmeta.rb', line 487

def self.db_type_cast( conn, column_type, column_value )
  sql = "SELECT CAST(#{column_value} AS #{column_type}) FROM RDB$DATABASE ROWS 1 TO 1"
  #puts "db_type_cast: #{sql}"
  retVal = nil
  conn.execute_immediate(sql) do |row|
    retVal = row[0]
  end
  retVal
end

.indices(conn, table_name) ⇒ Object 



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
 
# File 'lib/ibmeta.rb', line 309

def self.indices(conn, table_name)
  indices = []
  
  # we must make sure the current transaction is committed, otherwise this won't work!
  indicesSQL = <<-END_SQL
  select rdb$index_name, rdb$unique_flag,RDB$INDEX_INACTIVE,RDB$INDEX_TYPE  from rdb$indices
     where rdb$relation_name = '#{table_name.to_s.upcase}' and rdb$index_name not starting 'RDB$'
  END_SQL
  
  #~ puts "SQL"
  #~ puts indicesSQL
  
  conn.execute_immediate(indicesSQL) do |row|
    #puts "index #{ib_to_ar_case(row[0].to_s.rstrip)}"
    indices << InterBaseIndex.new(table_name, row[0].to_s.rstrip, 
           row[1].to_i == 1, [], 
           (row[3] == 1)?InterBaseMetaFunctions::DESCENDING : InterBaseMetaFunctions::ASCENDING, 
           (row[2] == 1)?InterBaseIndex::INDEX_INACTIVE : InterBaseIndex::INDEX_ACTIVE)
  end
  
  #puts "Indices size #{indices.size}"
  
  if !indices.empty? 
    indices.each() do |index|
      sql = "select rdb$field_name from rdb$index_segments where rdb$index_name "\
             "= '#{index.name.upcase}' order by rdb$index_name, rdb$field_position"
      
      #puts "index SQL: #{sql}"
      
      conn.execute_immediate(sql) do |row|
        index.columns << table_fields(conn, table_name, true, row[0].to_s.rstrip )
      end # each row in the index and get the InterBaseColumn
    end # each index
  end # if we found indices
  
  indices
end

.quote(value, column_meta_data) ⇒ Object 



288
289
290
291
292
293
294
295
296
 
# File 'lib/ibmeta.rb', line 288

def self.quote( value, column_meta_data )
  if column_meta_data.expects_quoting
    "\'#{value}\'"
  elsif ((column_meta_data.type == InterBaseColumn::BLOB) && (column_meta_data.sub_type != 1 ) )
    raise IBRubyException.new("'#{value}' is not a valid default for this column #{column_meta_data.name}.")
  else
    value
  end
end

.remove_index(conn, index_name) ⇒ Object 



347
348
349
 
# File 'lib/ibmeta.rb', line 347

def self.remove_index(conn, index_name)
  conn.execute_immediate( "DROP INDEX #{index_name}" )
end

.table_constraints(conn, table_name) ⇒ Object

table_fields



440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
 
# File 'lib/ibmeta.rb', line 440

def self.table_constraints(conn, table_name)
  sql = "select rdb$constraint_name, rdb$constraint_type, rdb$index_name "\
  "from rdb$relation_constraints "\
    "where rdb$constraint_type in ('FOREIGN KEY', 'PRIMARY KEY' ) "\
    "and rdb$relation_name = '#{table_name.to_s.upcase}'"
    
  constraints = []
  
  conn.execute_immediate(sql) do |constraint|
    constraint_name = constraint[0].strip
    constraint_type = ( constraint[1].strip == 'PRIMARY KEY' ) ? 
      InterBaseConstraint::PRIMARY_KEY : InterBaseConstraint::FOREIGN_KEY;
    representing_index = constraint[2]
    # now we need to get the columns that are being keyed on on this table, that is the same
    # for PK and FK
    columns = []
    conn.execute_immediate( "select rdb$field_name "\
          "from rdb$index_segments where rdb$index_name='#{representing_index}'") do |col|
      columns << col[0].to_s.strip
    end
    # and now for foreign keys, we need to find out what the foreign key index name is
    if constraint_type == InterBaseConstraint::FOREIGN_KEY
      fk_columns = []
      foreign_key_index = nil
      foreign_key_table = nil
      conn.execute_immediate( "select rdb$foreign_key from rdb$indices "\
            "where rdb$index_name='#{representing_index}'") do |fk|
        foreign_key_index = fk[0].strip
      end
      conn.execute_immediate( "select rdb$relation_name from rdb$indices "\
            "where rdb$index_name='#{foreign_key_index}'") do |fk|
        foreign_key_table = fk[0].strip
      end
      conn.execute_immediate( "select rdb$field_name "\
          "from rdb$index_segments where rdb$index_name='#{foreign_key_index}'") do |col|
        fk_columns << col[0].to_s.strip
      end
      
      constraints << InterBaseConstraint.new( constraint_type, columns, fk_columns, foreign_key_table )
    else
      constraints << InterBaseConstraint.new( constraint_type, columns  )
    end #if constraints_type
  end #conn.execute_immediate
  
  constraints
end

.table_fields(connection, table, extract_ordered = false, column_name = nil) ⇒ Object

This class method fetches the type details for a named table. The method returns a hash that links column names to InterBaseColumn objects.

Parameters

table

A string containing the name of the table.

connection

A reference to the connection to be used to determine the type information.

extract_ordered: if true then returns an ordered array of columns otherwise returns hash with col names column_name: if true then returns a single column (not array or hash)

Exception

IBRubyException

Generated if an invalid table name is specified

or an SQL error occurs.



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
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
 
# File 'lib/ibmeta.rb', line 364

def self.table_fields(connection, table, extract_ordered = false, column_name = nil)
  # Check for naughty table names.
  if /\s+/ =~ table.to_s
    raise IBRubyException.new("'#{table.to_s}' is not a valid table name.")
  end
  
  extract_ordered = true if !column_name.nil?
  
  types     = extract_ordered ? [] : {}
  begin
    # r.rdb$field_source,
    sql = "SELECT r.rdb$field_name, f.rdb$field_type, "\
             "f.rdb$field_length, f.rdb$field_precision, f.rdb$field_scale * -1, "\
             "f.rdb$field_sub_type, "\
             "COALESCE(r.rdb$default_source, f.rdb$default_source) rdb$default_source, "\
             "COALESCE(r.rdb$null_flag, f.rdb$null_flag) rdb$null_flag, rdb$character_length "\
      "FROM rdb$relation_fields r "\
      "JOIN rdb$fields f ON r.rdb$field_source = f.rdb$field_name "\
      "WHERE r.rdb$relation_name = '#{table.to_s.upcase}'";
    
    if !column_name.nil?
      sql << " AND r.rdb$field_name = '#{column_name.to_s.upcase}'"
    elsif extract_ordered
      sql << " ORDER BY r.rdb$field_position"
    end
    
    #puts "sql: #{sql}"
    
    #            sql = "SELECT RF.RDB$FIELD_NAME, F.RDB$FIELD_TYPE, "\
    #                  "F.RDB$FIELD_LENGTH, F.RDB$FIELD_PRECISION, "\
    #                  "F.RDB$FIELD_SCALE * -1, F.RDB$FIELD_SUB_TYPE "\
    #                  "FROM RDB$RELATION_FIELDS RF, RDB$FIELDS F "\
    #                  "WHERE RF.RDB$RELATION_NAME = UPPER('#{table}') "\
    #                  "AND RF.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME"
    
    #connection.start_transaction do |tx|
    #  tx.execute(sql) 
    connection.execute_immediate(sql) do |row|
        sql_type   = InterBaseColumn.to_base_type(row[1], row[5])
        type       = nil
        field_name = row[0].strip
        
        #column_name, table_name, type, default_source=nil, null_flag=false, length=nil, precision=nil, scale=nil, sub_type=nil )
        #row[0], row
        case sql_type
        when InterBaseColumn::BLOB
          type = InterBaseColumn.new(field_name, table.to_s, sql_type, row[6], !row[7].nil?, nil, nil, nil, row[5] )
        when InterBaseColumn::CHAR, InterBaseColumn::VARCHAR
          type = InterBaseColumn.new(field_name, table.to_s, sql_type, row[6], !row[7].nil?, row[8] )
          # row[8] is the real length, field_length depends on the character set being used
        when InterBaseColumn::DECIMAL, InterBaseColumn::NUMERIC
          type = InterBaseColumn.new(field_name, table.to_s, sql_type, row[6], !row[7].nil?, nil, row[3], row[4] )
        else
          type = InterBaseColumn.new(field_name, table.to_s, sql_type, row[6], !row[7].nil? )
        end
        
        if extract_ordered
          types << type
        else
          types[field_name] = type
        end
        
        #puts "col: #{type.to_sql}"
      end
      
    #end
  end
  if ( types.size > 1 || column_name.nil? )
    types
  elsif (types.size == 1)
    types[0]
  else
    nil
  end
end

.table_names(conn) ⇒ Object 



299
300
301
302
303
304
305
306
307
 
# File 'lib/ibmeta.rb', line 299

def self.table_names(conn)
  tables = []
  
  conn.execute_immediate("select rdb$relation_name from rdb$relations where rdb$relation_name not starting 'RDB$' and rdb$flags=1") do |row|
    tables << row[0].to_s.rstrip
  end
  
  tables
end