This is an adapter for sybase 12.5. It uses the ctlib library available from
http://raa.ruby-lang.org/project/sybase-ctlib/0.2.7
I have made a small change to support text objects in that library by setting the SYB_CTLIB_VERSION to CS_VERSION_125. This allows the text objects containing the session state to function correctly since the standard 100 varchar binding will only allow 255 characters. This change will support 32K. I have mailed the maintainer with this change...
This adapter has been tested and works. I wouldn't say it is optimal since it does not handle limits very well, but there doesn't seem to be any way to do this in an efficient manor in Sybase. We'll need to wait until version 15 for that.
There is one additional feature here that I use. I have special handling of ENUM type:
sp_addtype ENUM, "VARCHAR(16)"
;
I have also created a subclass of the Column class to handle the values in the check constraint. With a little imagination and some code I have created a helper (not in contributable form yet) that creates drop-downs and some validation support, all for free. Please take a look and tell me what you think.
I'm sorry I cannot test this code with the latest version because I no longer have access to a Sybase instance. If anyone does or can help, it would be greatly appreciated. I have tested this with 12.1 as that is the latest version we are currently using in production.
Hope this helps,
Will Sobel
# sybase_adaptor.rb
# author: Will Sobel
# notes:
require 'active_record/connection_adapters/abstract_adapter'
require 'parsedate'
require 'sybsql'
module ActiveRecord
module ConnectionAdapters
class SybaseAdapterContext < SybSQLContext
DEADLOCK = 1205
attr_reader :message
def init(logger = nil)
@deadlocked = false
@failed = false
@logger = logger
@message = nil
end
def srvmsgCB(con, msg)
# Do not log change of context messages.
if msg['severity'] == 10 or msg['severity'] == 0
return true
end
if msg['msgnumber'] == DEADLOCK
@deadlocked = true
else
@logger.info "SQL Command failed!" if @logger
@failed = true
end
if @logger
@logger.error "** SybSQLContext Server Message: **"
@logger.error " Message number #{msg['msgnumber']} Severity #{msg['severity']} State #{msg['state']} Line #{msg['line']}"
@logger.error " Server #{msg['srvname']}"
@logger.error " Procedure #{msg['proc']}"
@logger.error " Message String: #{msg['text']}"
end
@message = msg['text']
true
end
def deadlocked?
@deadlocked
end
def failed?
@failed
end
def reset
@deadlocked = false
@failed = false
@message = nil
end
def cltmsgCB(con, msg)
return true unless ( msg.kind_of?(Hash) )
unless ( msg[ "severity" ] ) then
return true
end
if @logger
@logger.error "** SybSQLContext Client-Message: **"
@logger.error " Message number: LAYER=#{msg[ 'layer' ]} ORIGIN=#{msg[ 'origin' ]} SEVERITY=#{msg[ 'severity' ]} NUMBER=#{msg[ 'number' ]}"
@logger.error " Message String: #{msg['msgstring']}"
@logger.error " OS Error: #{msg['osstring']}"
@message = msg['msgstring']
end
@failed = true
# Not retry , CS_CV_RETRY_FAIL( probability TimeOut )
if( msg[ 'severity' ] == "RETRY_FAIL" ) then
@timeout_p = true
return false
end
return true
end
end
class SybaseAdapter < AbstractAdapter # :nodoc:
class Column < ::ActiveRecord::ConnectionAdapters::Column
attr_reader :values
def initialize(name, default, sql_type = nil, values = nil)
super(name, default, sql_type)
# puts "#{@name} : #{sql_type} => #{@type}"
@values = values
@default = type_cast(default)
end
def klass
if @type == :enum
Symbol
else
super
end
end
def simplified_type(type)
case type
when /enum/i
:enum
else
super
end
end
def type_cast(value)
return nil if value.nil?
case @type
when :enum
if Symbol === value
value
elsif String === value and !value.empty?
value.intern
else
nil
end
when :boolean
value == 1 || value == true || value == '1' || value == 'true'
else
super
end
end
end
# Sybase adapter
def initialize(connection, database, logger = nil)
super(connection, logger)
context = connection.context
context.init(logger)
@limit = @offset = 0
unless connection.sql_norow("USE #{database}")
raise "Cannot USE #{database}"
end
end
# Check for a limit statement and parse out the limit and
# offset if specified. Remove the limit from the sql statement
# and call select.
def select_all(sql, name = nil)
select(sql, name)
end
# Remove limit clause from statement. This will almost always
# contain LIMIT 1 from the caller. set the rowcount to 1 before
# calling select.
def select_one(sql, name = nil)\
result = select(sql, name)
result.nil? ? nil : result.first
end
def columns(table_name, name = nil)
table_structure(table_name).inject([]) do |columns, column|
name, default, type, values = column
columns << Column.new(name, default, type, values)
columns
end
end
def insert(sql, name = nil, pk = nil, id_value = nil)
execute(sql, name = nil)
return id_value || last_insert_id
end
def execute(sql, name = nil)
log(sql, name, @connection) do |connection|
@connection.context.reset
connection.set_rowcount(@limit)
@limit = @offset = 0
connection.sql_norow(sql)
if connection.cmd_fail? or @connection.context.failed?
raise "SQL Command Failed for #{name}: #{sql}\nMessage: #{@connection.context.message}"
end
end
end
alias_method :update, :execute
alias_method :delete, :execute
def begin_db_transaction() execute "BEGIN TRAN" end
def commit_db_transaction() execute "COMMIT TRAN" end
def rollback_db_transaction() execute "ROLLBACK TRAN" end
# Handle quoting of enumerated types and override default
# boolean handling.
def quote(value, column = nil)
unless column
# Special hack for numbers. We need to make sure they
# are not quoted.
if value =~ /^[+-]?[0-9]+$/o
value
else
super
end
else
unless value.nil?
case column.type
when :boolean
case value
when String
value =~ /^[ty]/o ? 1 : 0
when true
1
when false
0
else
value.to_i
end
when :integer
value.to_i
when :float
value.to_f
when :text, :string, :enum
case value
when String, Symbol, Fixnum, Float, Bignum,
TrueClass, FalseClass
"'#{quote_string(value.to_s)}'"
else
"'#{quote_string(value.to_yaml)}'"
end
when :date, :datetime, :time
case value
when Time, DateTime
"'#{value.strftime("%Y-%m-%d %H:%M:%S")}'"
else
"'#{quote_string(value)}'"
end
else
"'#{quote_string(value.to_yaml)}'"
end
else
'NULL'
end
end
end
def quote_string(s)
s.gsub(/'/, "''") # ' (for ruby-mode)
end
def quote_column_name(name)
if name == 'role' or name == 'output'
name + '_'
else
name
end
end
def add_limit_with_offset!(sql, limit, offset)
@offset = offset
@limit = offset + limit
@logger.debug "Limit: #{@limit} Offset: #{@offset}"
end
def add_limit_without_offset!(sql, limit)
@limit = limit.to_i
@offset = 0
@logger.debug "Limit: #{@limit} Offset: #{@offset}"
end
private
# Return the last value of the identity global value.
def last_insert_id
@connection.sql("SELECT @@IDENTITY")
unless @connection.cmd_fail?
id = @connection.top_row_result.rows.first.first
if id
id = id.to_i
id = nil if id == 0
end
else
id = nil
end
# puts "@@identity => #{id.inspect}"
id
end
def affected_rows(name = nil)
@connection.sql("SELECT @@ROWCOUNT")
unless @connection.cmd_fail?
count = @connection.top_row_result.rows.first.first
count = count.to_i if count
else
0
end
end
# Select limit number of rows starting at optional offset.
def select(sql, name = nil)
@connection.context.reset
dump = @offset
log(sql, name, @connection) do |connection|
@logger.debug "Setting row count to #{@limit}" if @logger
connection.set_rowcount(@limit)
@limit = @offset = 0
connection.sql(sql)
end
rows = []
if @connection.context.failed? or @connection.cmd_fail?
raise "SQL Command Failed for #{name}: #{sql}\nMessage: #{@connection.context.message}"
else
results = @connection.top_row_result
if results.rows.length > 0
fields = fixup_column_names(results.columns)
results.rows.each do |row|
if dump > 0
dump -= 1
next
end
hashed_row = {}
row.zip(fields) { |cell, column| hashed_row[column] = cell }
rows << hashed_row
end
end
end
return rows
end
# Remove trailing _ from names.
def fixup_column_names(columns)
columns.map { |column| column.sub(/_$/, '') }
end
def table_structure(table_name)
sql = " SELECT col.name AS name, type.name AS type, col.prec, col.scale, col.length," +
"com.text AS \"Constraint\", def.text \"Default\" FROM " +
"sysobjects obj JOIN (syscolumns col JOIN systypes type ON col.usertype = type.usertype " +
"LEFT JOIN (sysconstraints con JOIN syscomments com ON com.id = con.constrid) " +
"ON col.colid = con.colid AND col.id = con.tableid " +
"LEFT JOIN syscomments def ON def.id = col.cdefault) " +
"ON obj.id = col.id " +
"WHERE obj.type = 'U' AND obj.name = '#{table_name}'"
log(sql, "Get Column Info ", @connection) do |connection|
connection.set_rowcount(0)
connection.sql(sql)
end
if @connection.context.failed?
raise "SQL Command for table_structure for #{table_name} failed\nMessage: #{@connection.context.message}"
elsif !@connection.cmd_fail?
columns = []
results = @connection.top_row_result
results.rows.each do |row|
name, type, prec, scale, length, const, default = row
type = normalize_type(type, prec, scale, length)
default_value = values = nil
name.sub!(/_$/o, '')
if type =~ /enum/i and const and const =~ /check\s+\([a-z0-9_]+\s+in\s+\((.+?)\)\)/imo
values = $1.split(',').map { |s| s.strip[1...-1].intern }
end
if default =~ /DEFAULT\s+(.+)/o
default_value = $1.strip
default_value = default_value[1...-1] if default_value =~ /^['"]/o
default_value = default_value.intern if values
end
columns << [name, default_value, type, values]
end
columns
else
nil
end
end
def normalize_type(field_type, prec, scale, length)
if field_type =~ /numeric/i and (scale.nil? or scale == 0)
type = 'int'
elsif field_type =~ /money/i
type = 'numeric'
else
type = field_type
end
size = ''
if prec
size = "(#{prec})"
elsif length
size = "(#{length})"
end
return type + size
end
def default_value(value)
end
end
end
class Base
# Establishes a connection to the database that's used by all Active Record objects
def self.sybase_connection(config) # :nodoc:
symbolize_strings_in_hash(config)
host = config[:host]
username = config[:username].to_s
password = config[:password].to_s
if config.has_key?(:database)
database = config[:database]
else
raise ArgumentError, "No database specified. Missing argument: database."
end
ConnectionAdapters::SybaseAdapter.new(
SybSQL.new({'S' => host, 'U' => username, 'P' => password},
ConnectionAdapters::SybaseAdapterContext),
database,
logger)
end
end
end
