Class: ActiveRecord::ConnectionAdapters::SQLAnywhereAdapter

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

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(connection, logger, connection_string = "") ⇒ SQLAnywhereAdapter

:nodoc:



137
138
139
140
141
142
143
144
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 137

def initialize( connection, logger, connection_string = "") #:nodoc:
  super(connection, logger)
  @auto_commit = true
  @affected_rows = 0
  @connection_string = connection_string
  @visitor = Arel::Visitors::SQLAnywhere.new self
  connect!
end

Class Method Details

.visitor_for(pool) ⇒ Object 



146
147
148
149
150
151
152
153
154
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 146

def self.visitor_for(pool)
  config = pool.spec.config
  
  if config.fetch(:prepared_statements) {true}
    Arel::Visitors::SQLAnywhere.new pool
  else
    BindSubstitution.new pool
  end
end

Instance Method Details

#active? ⇒ Boolean

Returns:

  • (Boolean) 


168
169
170
171
172
173
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 168

def active?
  # The liveness variable is used a low-cost "no-op" to test liveness
  SA.instance.api.sqlany_execute_immediate(@connection, "SET liveness = 1") == 1
rescue
  false
end

#adapter_name ⇒ Object

:nodoc:



156
157
158
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 156

def adapter_name #:nodoc:
  'SQLAnywhere'
end

#add_lock!(sql, options) ⇒ Object

:nodoc:



380
381
382
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 380

def add_lock!(sql, options) #:nodoc:
  sql
end

#base_tables(name = nil) ⇒ Object 



419
420
421
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 419

def base_tables(name = nil)
  list_of_tables(['base'], name)
end

#begin_db_transaction ⇒ Object

:nodoc:



366
367
368
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 366

def begin_db_transaction #:nodoc:   
  @auto_commit = false;
end

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

:nodoc:



477
478
479
480
481
482
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 477

def change_column(table_name, column_name, type, options = {}) #:nodoc:         
  add_column_sql = "ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
  add_column_options!(add_column_sql, options)
  add_column_sql << ' NULL' if options[:null]
  execute(add_column_sql)
end

#change_column_default(table_name, column_name, default) ⇒ Object

:nodoc:



466
467
468
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 466

def change_column_default(table_name, column_name, default) #:nodoc:
  execute "ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} DEFAULT #{quote(default)}"
end

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



470
471
472
473
474
475
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 470

def change_column_null(table_name, column_name, null, default = nil)
  unless null || default.nil?
    execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")
  end
  execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? '' : 'NOT'} NULL")
end

#columns(table_name, name = nil) ⇒ Object

:nodoc:



428
429
430
431
432
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 428

def columns(table_name, name = nil) #:nodoc:
  table_structure(table_name).map do |field|
    SQLAnywhereColumn.new(field['name'], field['default'], field['domain'], (field['nulls'] == 1))
  end
end

#commit_db_transaction ⇒ Object

:nodoc:



370
371
372
373
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 370

def commit_db_transaction #:nodoc:
  SA.instance.api.sqlany_commit(@connection)
  @auto_commit = true;
end

#delete_sql(sql, name = nil) ⇒ Object

The database delete function.



317
318
319
320
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 317

def delete_sql(sql, name = nil) #:nodoc:
  execute( sql, name )
  return @affected_rows
end

#disconnect! ⇒ Object 



175
176
177
178
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 175

def disconnect!
  result = SA.instance.api.sqlany_disconnect( @connection )
  super
end

#distinct(columns, order_by) ⇒ Object

This function (distinct) is based on the Oracle Enhacned ActiveRecord driver maintained by Raimonds Simanovskis (2010) (github.com/rsim/oracle-enhanced)



250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 250

def distinct(columns, order_by) #:nodoc:
  return "DISTINCT #{columns}" if order_by.blank?

  # construct a valid DISTINCT clause, ie. one that includes the ORDER BY columns, using
  # FIRST_VALUE such that the inclusion of these columns doesn't invalidate the DISTINCT
  order_columns = if order_by.is_a?(String)
    order_by.split(',').map { |s| s.strip }.reject(&:blank?)
  else # in latest ActiveRecord versions order_by is already Array
    order_by
  end
  order_columns = order_columns.zip((0...order_columns.size).to_a).map do |c, i|
    # remove any ASC/DESC modifiers
    value = c =~ /^(.+)\s+(ASC|DESC)\s*$/i ? $1 : c
    "FIRST_VALUE(#{value}) OVER (PARTITION BY #{columns} ORDER BY #{c}) AS alias_#{i}__"
  end
  sql = "DISTINCT #{columns}, "
  sql << order_columns * ", "
end

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



333
334
335
336
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 333

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

#execute(sql, name = nil) ⇒ Object

The database execution function



270
271
272
273
274
275
276
277
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 270

def execute(sql, name = nil) #:nodoc:
  if name == :skip_logging
    r = SA.instance.api.sqlany_execute_immediate(@connection, sql)
    sqlanywhere_error_test(sql) if r==0
  else
    log(sql, name) { execute(sql, :skip_logging) }
  end        
end

#indexes(table_name, name = nil) ⇒ Object

:nodoc:



434
435
436
437
438
439
440
441
442
443
444
445
446
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 434

def indexes(table_name, name = nil) #:nodoc:
  if @major_version <= 11 # the sql doesn't work in older databases.
    return []
  end
  sql = "SELECT DISTINCT index_name, \"unique\" FROM SYS.SYSTABLE INNER JOIN SYS.SYSIDXCOL ON SYS.SYSTABLE.table_id = SYS.SYSIDXCOL.table_id INNER JOIN SYS.SYSIDX ON SYS.SYSTABLE.table_id = SYS.SYSIDX.table_id AND SYS.SYSIDXCOL.index_id = SYS.SYSIDX.index_id WHERE table_name = '#{table_name}' AND index_category > 2"
  select(sql, name).map do |row|
    index = IndexDefinition.new(table_name, row['index_name'])
    index.unique = row['unique'] == 1
    sql = "SELECT column_name FROM SYS.SYSIDX INNER JOIN SYS.SYSIDXCOL ON SYS.SYSIDXCOL.table_id = SYS.SYSIDX.table_id AND SYS.SYSIDXCOL.index_id = SYS.SYSIDX.index_id INNER JOIN SYS.SYSCOLUMN ON SYS.SYSCOLUMN.table_id = SYS.SYSIDXCOL.table_id AND SYS.SYSCOLUMN.column_id = SYS.SYSIDXCOL.column_id WHERE index_name = '#{row['index_name']}'"	
    index.columns = select(sql).map { |col| col['column_name'] }
    index
  end
end

#insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) ⇒ Object

The database insert function. ActiveRecord requires that insert_sql returns the primary key of the row just inserted. In most cases, this can be accomplished by immediatly querying the @@identity property. If the @@identity property is 0, then passed id_value is used



325
326
327
328
329
330
331
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 325

def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) #:nodoc:
  execute(sql, name)
  
  retval =  last_inserted_id(nil)
  retval = id_value if retval == 0
  return retval
end

#last_inserted_id(result) ⇒ Object

Raises:

  • (ActiveRecord::StatementInvalid) 


339
340
341
342
343
344
345
346
347
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 339

def last_inserted_id(result)
  identity = SA.instance.api.sqlany_execute_direct(@connection, 'SELECT @@identity')
  raise ActiveRecord::StatementInvalid.new("#{SA.instance.api.sqlany_error(@connection)}:#{sql}") if identity.nil?
  SA.instance.api.sqlany_fetch_next(identity)
  retval = SA.instance.api.sqlany_get_column(identity, 0)[1]
  SA.instance.api.sqlany_free_stmt(identity)

  return retval
end

#native_database_types ⇒ Object

Maps native ActiveRecord/Ruby types into SQLAnywhere types TINYINTs are treated as the default boolean value ActiveRecord allows NULLs in boolean columns, and the SQL Anywhere BIT type does not As a result, TINYINT must be used. All TINYINT columns will be assumed to be boolean and should not be used as single-byte integer columns. This restriction is similar to other ActiveRecord database drivers



198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 198

def native_database_types #:nodoc:
  {
    :primary_key => 'INTEGER PRIMARY KEY DEFAULT AUTOINCREMENT NOT NULL',
    :string      => { :name => "varchar", :limit => 255 },
    :text        => { :name => "long varchar" },
    :integer     => { :name => "integer", :limit => 4 },
    :float       => { :name => "float" },
    :decimal     => { :name => "decimal" },
    :datetime    => { :name => "datetime" },
    :timestamp   => { :name => "datetime" },
    :time        => { :name => "time" },
    :date        => { :name => "date" },
    :binary      => { :name => "binary" },
    :boolean     => { :name => "tinyint", :limit => 1}
  }
end

#primary_key(table_name) ⇒ Object

:nodoc:



448
449
450
451
452
453
454
455
456
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 448

def primary_key(table_name) #:nodoc:
  sql = "SELECT cname from SYS.SYSCOLUMNS where tname = '#{table_name}' and in_primary_key = 'Y'"
  rs = exec_query(sql)
  if !rs.nil? and !rs.first.nil?
    rs.first['cname']
  else
    nil
  end
end

#purge_database ⇒ Object 



509
510
511
512
513
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 509

def purge_database
  base_tables.each do |base_table_name|
    drop_table(base_table_name)
  end
end

#quote(value, column = nil) ⇒ Object

Handles special quoting of binary columns. Binary columns will be treated as strings inside of ActiveRecord. ActiveRecord requires that any strings it inserts into databases must escape the backslash (). Since in the binary case, the (x) is significant to SQL Anywhere, it cannot be escaped.



225
226
227
228
229
230
231
232
233
234
235
236
237
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 225

def quote(value, column = nil)
  case value
    when String, ActiveSupport::Multibyte::Chars
      value_S = value.to_s
      if column && column.type == :binary && column.class.respond_to?(:string_to_binary)
        "'#{column.class.string_to_binary(value_S)}'"
      else
         super(value, column)
      end
    else
      super(value, column)
  end
end

#quote_column_name(name) ⇒ Object

Applies quotations around column names in generated queries



218
219
220
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 218

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

#quoted_false ⇒ Object 



243
244
245
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 243

def quoted_false
  '0'
end

#quoted_true ⇒ Object 



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

def quoted_true
  '1'
end

#reconnect! ⇒ Object 



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

def reconnect!
  disconnect!
  connect!
end

#remove_column(table_name, *column_names) ⇒ Object 



494
495
496
497
498
499
500
501
502
503
504
505
506
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 494

def remove_column(table_name, *column_names)
  column_names = column_names.flatten
  column_names.zip(columns_for_remove(table_name, *column_names)).each do |unquoted_column_name, column_name|
    sql = <<-SQL
      SELECT "index_name" FROM SYS.SYSTAB join SYS.SYSTABCOL join SYS.SYSIDXCOL join SYS.SYSIDX
      WHERE "column_name" = '#{unquoted_column_name}' AND "table_name" = '#{table_name}'
    SQL
    select(sql, nil).each do |row|
      execute "DROP INDEX \"#{table_name}\".\"#{row['index_name']}\""      
    end
    execute "ALTER TABLE #{quote_table_name(table_name)} DROP #{column_name}"
  end
end

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

:nodoc:



458
459
460
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 458

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

#rename_column(table_name, column_name, new_column_name) ⇒ Object

:nodoc:



484
485
486
487
488
489
490
491
492
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 484

def rename_column(table_name, column_name, new_column_name) #:nodoc:
  if column_name.downcase == new_column_name.downcase
    whine = "if_the_only_change_is_case_sqlanywhere_doesnt_rename_the_column"
    rename_column table_name, column_name, "#{new_column_name}#{whine}"
    rename_column table_name, "#{new_column_name}#{whine}", new_column_name
  else
    execute "ALTER TABLE #{quote_table_name(table_name)} RENAME #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}"
  end
end

#rename_table(name, new_name) ⇒ Object 



462
463
464
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 462

def rename_table(name, new_name)
  execute "ALTER TABLE #{quote_table_name(name)} RENAME #{quote_table_name(new_name)}"
end

#requires_reloading? ⇒ Boolean

Returns:

  • (Boolean) 


164
165
166
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 164

def requires_reloading?
  true
end

#rollback_db_transaction ⇒ Object

:nodoc:



375
376
377
378
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 375

def rollback_db_transaction #:nodoc:
  SA.instance.api.sqlany_rollback(@connection)
  @auto_commit = true;
end

#select(sql, name = nil, binds = []) ⇒ Object

:nodoc:



515
516
517
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 515

def select(sql, name = nil, binds = []) #:nodoc:
   exec_query(sql, name, binds).to_a
end

#select_rows(sql, name = nil) ⇒ Object

Returns a query as an array of arrays

Raises:

  • (ActiveRecord::StatementInvalid) 


350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 350

def select_rows(sql, name = nil)
  rs = SA.instance.api.sqlany_execute_direct(@connection, sql)
  raise ActiveRecord::StatementInvalid.new("#{SA.instance.api.sqlany_error(@connection)}:#{sql}") if rs.nil?
  record = []
  while SA.instance.api.sqlany_fetch_next(rs) == 1
    max_cols = SA.instance.api.sqlany_num_cols(rs)
    result = Array.new(max_cols)
    max_cols.times do |cols|
      result[cols] = SA.instance.api.sqlany_get_column(rs, cols)[1]
    end
    record << result
  end
  SA.instance.api.sqlany_free_stmt(rs)
  return record
end

#sqlanywhere_error(code, message, sql) ⇒ Object

Raises:



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

def sqlanywhere_error(code, message, sql)        
  raise SQLAnywhereException.new(message, code, sql)
end

#sqlanywhere_error_test(sql = '') ⇒ Object 



279
280
281
282
283
284
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 279

def sqlanywhere_error_test(sql = '')
  error_code, error_message = SA.instance.api.sqlany_error(@connection)
  if error_code != 0
    sqlanywhere_error(error_code, error_message, sql)
  end
end

#supports_autoincrement? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


189
190
191
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 189

def supports_autoincrement? #:nodoc:
  true
end

#supports_count_distinct? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


185
186
187
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 185

def supports_count_distinct? #:nodoc:
  true
end

#supports_migrations? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


160
161
162
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 160

def supports_migrations? #:nodoc:
  true
end

#tables(name = nil) ⇒ Object

Do not return SYS-owned or DBO-owned tables or RS_systabgroup-owned



424
425
426
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 424

def tables(name = nil) #:nodoc:
  list_of_tables(['base', 'view'])
end

#translate_exception(exception, message) ⇒ Object 



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

def translate_exception(exception, message)
  return super unless exception.respond_to?(:errno)
  case exception.errno
    when -143
      if exception.sql !~ /^SELECT/i then
        raise ActiveRecord::ActiveRecordError.new(message)
      else
        super
      end
    when -194
      raise InvalidForeignKey.new(message, exception)
    when -196
      raise RecordNotUnique.new(message, exception)
    when -183
      raise ArgumentError, message
    else
      super
  end
end

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

SQL Anywhere does not support sizing of integers based on the sytax INTEGER(size). Integer sizes must be captured when generating the SQL and replaced with the appropriate size.



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

def type_to_sql(type, limit = nil, precision = nil, scale = nil) #:nodoc:
  type = type.to_sym
  if native = native_database_types[type]
    if type == :integer
      case limit
        when 1
          column_type_sql = 'tinyint'
        when 2
          column_type_sql = 'smallint'  
        when 3..4
          column_type_sql = 'integer'
        when 5..8
          column_type_sql = 'bigint'
        else
          column_type_sql = 'integer'
      end
         column_type_sql
    elsif type == :string and !limit.nil?
       "varchar (#{limit})"
    elsif type == :boolean
      column_type_sql = 'tinyint'
    else 
      super(type, limit, precision, scale)
    end
  else
    super(type, limit, precision, scale)
  end
end

#update_sql(sql, name = nil) ⇒ Object

The database update function.



311
312
313
314
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 311

def update_sql(sql, name = nil)
  execute( sql, name )
  return @affected_rows
end

#viewed_tables(name = nil) ⇒ Object 



415
416
417
 
# File 'lib/active_record/connection_adapters/sqlanywhere_adapter.rb', line 415

def viewed_tables(name = nil)
  list_of_tables(['view'], name)
end