Class: ActiveRecord::ConnectionAdapters::IngresAdapter

Inherits:
AbstractAdapter
  • Object
show all
Defined in:
lib/active_record/connection_adapters/ingres_adapter.rb

Overview

This Ingres adapter works with the Ruby/Ingres driver written by Jared Richardson

Options:

  • :username - Optional-Defaults to nothing

  • :password - Optional-Defaults to nothing

  • :database - The name of the database. No default, must be provided.

Author: [email protected] Maintainer: [email protected] Maintainer: [email protected]

Defined Under Namespace

Classes: BindSubstitution, StatementPool

Constant Summary collapse

ADAPTER_NAME = 
'Ingres'.freeze
NATIVE_DATABASE_TYPES = 
{
  :primary_key => "integer NOT NULL PRIMARY KEY",
  :string      => { :name => "varchar", :limit => 255 },
  :text        => { :name => "varchar", :limit => 32000 },
  :integer     => { :name => "integer" },
  :float       => { :name => "float" },
  :datetime    => { :name => "ingresdate" },
  :timestamp   => { :name => "timestamp" },
  :time        => { :name => "time" },
  :date        => { :name => "ansidate" }, #"date" pre-ansidate
  :binary      => { :name => "blob" },
  :boolean     => { :name => "tinyint" },
  :decimal     => { :name => "decimal" }
}.freeze
PARAMETERS_TYPES = 
{
  "byte"         => "b",
  "long_byte"    => "B",
  "char"         => "c",
  "date"         => "d",
  "decimal"      => "D",
  "float"        => "f",
  "integer"      => "i",
  "nchar"        => "n",
  "nvarchar"     => "N",
  "text"         => "t",
  "long_text"    => "T",
  "varchar"      => "v",
  "long_varchar" => "V",
}.freeze

Instance Method Summary collapse

Constructor Details

#initialize(connection, logger, connection_parameters, config) ⇒ IngresAdapter

Returns a new instance of IngresAdapter.



164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 164

def initialize(connection, logger, connection_parameters, config)
  super(connection, logger)

  if config.fetch(:prepared_statements) { true }
    @visitor = Arel::Visitors::Ingres.new self
  else
    @visitor = BindSubstitution.new self
  end

  @connection_parameters, @config = connection_parameters, config

  connect
  @statements = StatementPool.new(@connection,
                                  config.fetch(:statement_limit) { 1000 })
end

Instance Method Details

#active? ⇒ Boolean

CONNECTION MANAGEMENT ====================================

Returns:

  • (Boolean) 


217
218
219
220
221
222
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 217

def active?
  @connection.exec 'SELECT 1'
  true
rescue Exception
  false
end

#adapter_name ⇒ Object 



180
181
182
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 180

def adapter_name
  ADAPTER_NAME
end

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



563
564
565
566
567
568
569
570
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 563

def add_column(table_name, column_name, type, options = {})
  add_column_sql = "ALTER TABLE #{table_name} ADD #{column_name} #{type_to_sql(type)}"
  if( (type.to_s=="string") && (options[:limit]!=nil) ) then
    add_column_sql.gsub!("varchar(255)", "varchar(#{options[:limit]})")
    # puts ("\ntype.to_s.class is #{type.to_s.class}")
  end
  execute(add_column_sql)
end

#add_column_options!(sql, options) ⇒ Object

Ingres 9.1.x and earlier require ‘COLNAME TYPE DEFAULT NULL WITH NULL’ as part of the column definition for CREATE TABLE TODO : add a server version check so as to only do this for Ingres 9.1.x and earlier.



551
552
553
554
555
556
557
558
559
560
561
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 551

def add_column_options!(sql, options) #:nodoc:
  sql << " DEFAULT #{quote(options[:default], options[:column])}" if options_include_default?(options)
  # must explcitly check for :null to allow change_column to work on migrations
  if options.has_key? :null
    if options[:null] == false
      sql << " NOT NULL"
    else
      sql << " WITH NULL"
    end
  end
end

#begin_db_transaction ⇒ Object

:nodoc:



429
430
431
432
433
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 429

def begin_db_transaction #:nodoc:
  execute "START TRANSACTION"
rescue Exception
  # Transactions aren't supported
end

#clear_cache! ⇒ Object

DATABASE STATEMENTS ======================================



286
287
288
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 286

def clear_cache!
  @statements.clear
end

#columns(table_name, name = nil) ⇒ Object

:nodoc:



523
524
525
526
527
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 523

def columns(table_name, name = nil) #:nodoc:
  column_definitions(table_name).collect do |row|
    new_column(row["column_name"], row["column_default_val"], row["column_datatype"])
  end
end

#commit_db_transaction ⇒ Object

:nodoc:



435
436
437
438
439
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 435

def commit_db_transaction #:nodoc:
  execute "COMMIT"
rescue Exception
  # Transactions aren't supported
end

#create_database(name, options = {}) ⇒ Object

Create an Ingres database



450
451
452
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 450

def create_database(name, options = {})
  #`createdb #{name} -i -fnofeclients`
end

#disconnect! ⇒ Object 



235
236
237
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 235

def disconnect!
  @connection.disconnect rescue nil
end

#drop_database(name) ⇒ Object

Drops an Ingres database



455
456
457
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 455

def drop_database(name) #:nodoc:
  #`destroydb #{name}`
end

#exec_delete(sql, name = 'SQL', binds = []) ⇒ Object Also known as: exec_update 



315
316
317
318
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 315

def exec_delete(sql, name = 'SQL', binds = [])
  exec_query(sql, name, binds)
  @connection.rows_affected
end

#exec_query(sql, name = 'SQL', binds = []) ⇒ Object 



290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 290

def exec_query(sql, name = 'SQL', binds = [])
  log(sql, name, binds) do
    #TODO Aiming to do prepared statements but we'll have to do changes to the C API
    #result = binds.empty? ? exec_no_cache(sql, binds) :
    #                        exec_cache(sql, binds)
    result = binds.empty? ? @connection.execute(sql) :
                            @connection.execute(sql, *binds.map { |bind| [PARAMETERS_TYPES[bind[0].sql_type.downcase], bind[1]] }.flatten)

    if @connection.rows_affected
      # Dirty hack for ASCII-8BIT strings
      result.each do |row|
        row.each_with_index do |column, index|
          if String === column && column.encoding == Encoding::ASCII_8BIT
            row[index] = column.unpack("C*").pack("U*")
          end
        end
      end

      ActiveRecord::Result.new(@connection.column_list_of_names, result)
    else
      ActiveRecord::Result.new([], [])
    end
  end
end

#execute(sql, name = nil) ⇒ Object 



328
329
330
331
332
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 328

def execute(sql, name = nil)
  log(sql, name) do
    @connection.execute(sql)
  end
end

#execute_without_adding_ordering(sql, name = nil) ⇒ Object 



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
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 391

def execute_without_adding_ordering(sql, name=nil)
  # TODO: clean up this hack
  sql.gsub!(" = NULL", " is NULL")
  sql.gsub!(" IN (NULL)", " is NULL")

  begin
    rs = @connection.execute(sql)
  rescue
    puts "\nAn error occurred!\n"
  end
  col_names = @connection.column_list_of_names
  data_type = @connection.data_types

  rows=[]
  rs.each do |row|
    index = 0
    this_column = Hash.new
    row.each do |item |

      col_name = col_names[index].rstrip
      col_val = item.to_s.rstrip

      if (col_val=="NULL") then
        col_val = nil
      end
      this_column[ col_name] = col_val
      index += 1
    end
    rows << this_column
    index = 0
  end
  if(@offset) then
    rows = apply_limit_and_offset!(rows)
  end

  return rows
end

#force_numeric?(column) ⇒ Boolean

QUOTING ==================================================

Returns:

  • (Boolean) 


251
252
253
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 251

def force_numeric?(column)
  (column.nil? || [:integer, :float, :decimal].include?(column.type))
end

#get_data_size(id) ⇒ Object 



344
345
346
347
348
349
350
351
352
353
354
355
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 344

def get_data_size(id)
  column_names = @connection.column_list_of_names
  index = column_names.index(id)

  if(index) then
    data_sizes = @connection.data_sizes
    res = data_sizes.at(index)
  else
    res = 0
  end
  res
end

#indexes(table_name, name = nil) ⇒ Object

:nodoc:



465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 465

def indexes(table_name, name = nil)#:nodoc:
  @primary_key = nil
  sql =  "SELECT base_name, index_name, unique_rule "
  sql <<    "FROM "
  sql <<    "iiindexes "
  sql <<    "WHERE  "
  sql <<    "base_name = '#{table_name}' "

  indexes = []
  result_set = execute_without_adding_ordering(sql, name)

  if(result_set) then
    result_set.each do | column |
      base_name  = column.values_at("base_name")[0]
      index_name = column.values_at("index_name")[0]
      unique_rule =column.values_at("unique_rule")[0]=='U'
      column_name = column.values_at("column_name")[0]

      # get a copy of the  primary index
      # Ingres uses the first 5 characters of a table name for the primary key index
      if table_name.length < 5 then
        short_name = table_name
      else
        short_name = table_name[0..4]
      end
      if (index_name.starts_with?("$#{short_name}")) then
        # we have the name of the pointer to the index
        # now let's fetch the actual name of the primary key
        sql2="select column_name from iiindex_columns where index_name='#{index_name}'"
        result_set_2 = execute_without_adding_ordering(sql2)
        @primary_key = result_set_2[0].fetch('column_name')
      end

      # ignore any index with a $pk or ii prefix. It's a system index
      prefix1 = "$#{short_name}"
      prefix2 = "ii"
      if ( (index_name.starts_with?(prefix1)) || (index_name.starts_with?(prefix2)) ) then
        #puts "\nSkipping #{index_name}\n"
      else
        #puts "\nAdding >>#{index_name}<<\n"

        # now fetch the column names and store them in an Array
        column_names = []
        sql = "SELECT column_name from iiindex_columns "
        sql << " WHERE index_name = '#{index_name}' "
        rs2 = execute_without_adding_ordering(sql, name)
        rs2.each do | col2 |
          this_col_name = col2.values_at("column_name").first
          column_names << this_col_name
        end
        # class IndexDefinition < Struct.new(:table, :name, :unique, :columns) #:nodoc:
        indexes << IndexDefinition.new( table_name, index_name, unique_rule, column_names)
      end
    end
  end
  indexes
end

#insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) ⇒ Object Also known as: create

:nodoc:



334
335
336
337
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 334

def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) #:nodoc:
  super
  id_value
end

#last_inserted_id(table) ⇒ Object

Get the last generate identity/auto_increment/sequence number generated for a given table



323
324
325
326
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 323

def last_inserted_id(table)
  r = exec_query("SELECT max(#{primary_key(table)}) FROM #{table}")
  Integer(r.first.first)
end

#native_database_types ⇒ Object 



239
240
241
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 239

def native_database_types
  NATIVE_DATABASE_TYPES
end

#new_column(column_name, default, type) ⇒ Object

HELPER METHODS ===========================================



245
246
247
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 245

def new_column(column_name, default, type)
  IngresColumn.new(column_name, default, type)
end

#next_sequence_value(sequence_name) ⇒ Object 



357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 357

def next_sequence_value(sequence_name)

  ary = sequence_name.split(' ')

  if use_table_sequence? then
    next_value = next_identity_for_table(ary[0])
  else
    if (!ary[1]) then
      ary[0] =~ /(\w+)_nonstd_seq/
      ary[0] = $1
      if( ary[1]== nil) then
        last_identity = last_identity_for_table($1) 
      end
    else
      last_identity = last_identity_for_table(ary[0]) 
    end
    if last_identity == "NULL"
      next_value = 1
    else
      next_value = last_identity + 1
    end
  end
  next_value
end

#prefetch_primary_key?(table_name = nil) ⇒ Boolean

Should primary key values be selected from their corresponding sequence before the insert statement? If true, next_sequence_value is called before each insert to set the record’s primary key.

Returns:

  • (Boolean) 


211
212
213
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 211

def prefetch_primary_key?(table_name = nil)
  true
end

#primary_key(table) ⇒ Object

Returns just a table’s primary key



530
531
532
533
534
535
536
537
538
539
540
541
542
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 530

def primary_key(table)
  row = @connection.execute(<<-end_sql).first
    SELECT column_name
    FROM iicolumns, iiconstraints
    WHERE iiconstraints.table_name = '#{table}'
    AND iiconstraints.constraint_name = iicolumns.table_name
    AND iiconstraints.constraint_type = 'P'
    AND iicolumns.column_name != 'tidp'
    ORDER BY iicolumns.column_sequence
  end_sql

  row && row.first
end

#quote(value, column = nil) ⇒ Object 



255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 255

def quote(value, column = nil)
  case value
  when String
    if column && column.type == :binary && column.class.respond_to?(:string_to_binary)
      "'#{@connection.insert_binary(value)}'"
    else
      "'#{quote_string(value)}'"
    end
  when TrueClass then '1'
  when FalseClass then '0'
  when Fixnum, Bignum then force_numeric?(column) ? value.to_s : "'#{value.to_s}'"
  when NilClass then "NULL"
  when Time, DateTime        then "'#{value.strftime("%Y-%m-%d %H:%M:%S")}'"
  else                       super
  end

end

#quote_column_name(name) ⇒ Object

Quotes column names for use in SQL queries.



280
281
282
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 280

def quote_column_name(name) #:nodoc:
  %("#{name}")
end

#quote_string(s) ⇒ Object

Quotes a string, escaping any ‘ (single quote) and \ (backslash) characters.



275
276
277
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 275

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

#reconnect! ⇒ Object 



224
225
226
227
228
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 224

def reconnect!
  disconnect!
  clear_cache!
  connect
end

#remove_index!(table_name, index_name) ⇒ Object 



544
545
546
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 544

def remove_index!(table_name, index_name)
  execute "DROP INDEX #{quote_table_name(index_name)}"
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object

Ingres does not support ALTER TABLE RENAME COL so we have to do it another way

!!!!Note!!!! This method only handles tables and primary keys as generated by ActiveRecord If someone has modified the table structure or added additional indexes these will be lost. TODO - handle secondary indexes and alternate base table structures



579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 579

def rename_column(table_name, column_name, new_column_name) #:nodoc:
  table_columns = columns(table_name)
  #Strip the leading :
  old_column_name = "#{column_name}"
  no_table_columns = table_columns.size
  count = 0
  column_sql = ""
  table_columns.each do |col|
    count = count + 1 
    if col.name == old_column_name then
      column_sql << " #{col.name} as #{new_column_name}"
    else
      column_sql << " #{col.name}"
    end
    if (count < no_table_columns) then
      column_sql << ", " 
    end
  end
  pk_col = primary_key(table_name)
  # Backup the current table renaming the chosen column
  execute( <<-SQL_COPY, nil)
      DECLARE GLOBAL TEMPORARY TABLE session.table_copy AS
        SELECT #{column_sql}
        FROM #{table_name}
      ON COMMIT PRESERVE ROWS
      WITH NORECOVERY
        SQL_COPY

        #Drop table_name
        execute( <<-SQL_COPY, nil)
      DROP TABLE #{table_name}
      SQL_COPY

      #Re-create table_name based on session.table_copy
      execute( <<-SQL_COPY, nil)
      CREATE TABLE #{table_name} AS
        SELECT * FROM session.table_copy
      SQL_COPY

      #Add the Primary key back
      execute( <<-SQL_COPY, nil)
      ALTER TABLE #{table_name} ADD CONSTRAINT #{table_name[0..4]}_#{pk_col}_pk PRIMARY KEY (#{pk_col})
      SQL_COPY

      #Drop the GTT session.table_copy
      execute( <<-SQL_COPY, nil)
      DROP TABLE session.table_copy
      SQL_COPY
end

#reset! ⇒ Object 



230
231
232
233
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 230

def reset!
  clear_cache!
  super
end

#rollback_db_transaction ⇒ Object

:nodoc:



441
442
443
444
445
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 441

def rollback_db_transaction #:nodoc:
  execute "ROLLBACK"
rescue Exception
  # Transactions aren't supported
end

#select_rows(sql, name = nil) ⇒ Object 



340
341
342
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 340

def select_rows(sql, name = nil)
  execute(sql, name).to_a
end

#supports_ddl_transactions? ⇒ Boolean

Does this adapter support DDL rollbacks in transactions? That is, would CREATE TABLE or ALTER TABLE get rolled back by a transaction?

Returns:

  • (Boolean) 


193
194
195
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 193

def supports_ddl_transactions?
  true
end

#supports_migrations? ⇒ Boolean

Returns:

  • (Boolean) 


204
205
206
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 204

def supports_migrations?
  true
end

#supports_primary_key? ⇒ Boolean

Can this adapter determine the primary key for tables not attached to an ActiveRecord class, such as join tables? Backend specific, as the abstract adapter always returns false.

Returns:

  • (Boolean) 


187
188
189
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 187

def supports_primary_key?
  true
end

#supports_savepoints? ⇒ Boolean

Does this adapter support savepoints? We do but there is no delete/release savepoint feature which is needed by ActiveRecord

Returns:

  • (Boolean) 


200
201
202
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 200

def supports_savepoints?
  false
end

#tables(name = nil) ⇒ Object

Return the list of all tables in the schema search path.



460
461
462
463
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 460

def tables(name = nil) #:nodoc:
  tables = @connection.tables.flatten
  tables
end

#type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object

Ingres supports a different SQL syntax for column type definitions For example with other vendor DBMS engines it’s possible to specify the number of bytes in an integer column - e.g. INTEGER(3)

In addition it would appear that the following syntax is not valid for Ingres colname DECIMAL DEFAULT xx.yy where as colname DECIMAL is valid. It would appear we need to supply the precision and scale when providing a default value.



639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 639

def type_to_sql(type, limit = nil, precision = nil, scale = nil)
  case type.to_sym
  when :integer;
    case limit
    when 1..2;      return 'smallint'
    when 3..4, nil; return 'integer'
    when 5..8;      return 'bigint'
    else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.")
    end
  when :boolean; return 'tinyint'
  when :decimal;
    if precision.nil? then
      # Ingres requires the precision/scale be defined
      return 'decimal (39,15)'
    else
      return "decimal (#{precision},#{scale})"
    end
  else
    return super
  end
end

#use_table_sequence? ⇒ Boolean

Should we use sequence defined in the default value

Returns:

  • (Boolean) 


383
384
385
386
387
388
389
 
# File 'lib/active_record/connection_adapters/ingres_adapter.rb', line 383

def use_table_sequence?
  if @config.has_key? :use_sequence_for_identity then
    return @config[:use_sequence_for_identity] 
  else
    return FALSE
  end
end