Examples

Of course, for fixing all those use cases we were influenced by checking what other adapters do in similar situations. Still, we had to conduct a research on our own: there's no universal recipe for how to fix things.

For the beginning, take a look at the modify_types method of the adapter. This is the place where you can code the data types that your database supports. Most of the time this information will be used during DDL execution, that is, during db:migrate for example. As always, you can check to see what other adapters do here.

The create_table method is the place to tell what should be done when a new table is being created. You may need to do something more besides the table creation: in the case of the MaxDB adapter we create a corresponding sequence as well. It is needed for generating primary key values for that table.

A simple example was related with quoting: Sybase ASE doesn't allow you to create a column which has a reserved word for a name, like 'limit' ... unless you quote it:

# We need column name quoting since some identifiers like the string 'public' are keywords
# in Sybase ASE and therefore would not be allowed as valid column names.
def quote_column_name(name)
  "[#{name}]"
end

Taking a look that the MS SQL adapter does the same thing, it was pretty clear that this was our fix. Actually, Sybase ASE and SQL Server have some common background, and are still similar in some ways.

In the case of MaxDB we specified the logic for how to retrieve the next value from the sequence - this is completely vendor specific code (and Sybase ASE doesn't even support sequences):

def next_sequence_value(sequence_name)
  execute("select #{sequence_name}.nextval id from dual").first['id'].to_i
end

The add_index method is the place to modify the SQL code which is executed when a new index is created. We used it to control the identifier name for the new index, because MaxDB has some limitations for the identifier length.

Next, we had an issue with Sybase ASE adapter when executing a SELECT query with a LIMIT specified. After checking with the documentation it turned out that Sybase ASE doesn't use the LIMIT keyword, but TOP instead. A perfect place to apply these findings was the Arel visitor:

# Sybase doesn't recognize LIMIT keyword but uses
# a TOP one instead.
def visit_Arel_Nodes_Top o
  "TOP #{visit o.expr}"
end
def visit_Arel_Nodes_Limit o
  ""
end

The next is a tricky one. We observed that sometimes when the application saves its model into the database, it cannot retrieve it later. After some debugging we found out the problem: the primary key value that was generated in the database during the INSERT statement (we use such an automatic pk generating), was being not returned to the application. After some research we were lucky to find that the RubyJdbcConnection class offers such a method. So the fix was to call this method in the adapter:

# In case we execute an INSERT statement we need to get the auto-generated primary key value.
# So we call the corresponding _insert method exposed by the RubyJdbcConnection class.
# This code relies that all inserts will be on tables that have IDENTITY primary keys.
def _execute(sql, name = nil)
  if sql.lstrip =~ /\Ainsert/i
    @connection.execute_insert(sql)
  else
    @connection.execute(sql)
  end
end

Next, we got issues with the difference between Sybase ASE and other database systems with regards to some defaults. Sybase ASE uses by default non nullable columns, while most other databases use nullable columns. This is a db level setting, and can be easily changed by executing some commands to the db (most probably they will need admin rights), but in our adapter we assumed the database to be a black box: the application has to find a way to work with it, assuming that no admin control can be applied. That's why in the adapter we introduced the following logic:

def create_table(name, options = {})
  super(name, options)
  change_non_default_columns_to_be_nullable(name)
end

# This is needed because of the default Sybase ASE setting, which is to have
# NOT NULL on all table columns - the opposite to most other databases.
#
# After having created a particular table, we retrieve all the columns in
# it. We modify each such column, which is not a primary key and which
# does not have a default value, to be nullable.
def change_non_default_columns_to_be_nullable(table_name)
  primary_keys = primary_keys(table_name)

  @connection.columns(table_name).each { |column|
    if !column.has_default? && !primary_keys.include?(column.name)
      change_column_null(table_name, column.name, true)
    end
  }
end

def change_column_null(table_name, column_name, null)
  if null
    execute "ALTER TABLE #{quote_table_name(table_name)} MODIFY #{quote_column_name(column_name)} NULL"
  else
    execute "ALTER TABLE #{quote_table_name(table_name)} MODIFY #{quote_column_name(column_name)} NOT NULL"
  end
end

The next problem was with the MaxDB adapter: we got an error in a SQL statement, which contained the COALESCE operator. MaxDB doesn't support this conversion operator, but has the VALUE one:

# MaxDB does not support COALESCE function/operator. We use VALUE instead.
def exec_update(sql, name, binds)
  sql.gsub!(/COALESCE/i, 'VALUE')
  super(sql, name, binds)
end

MaxDB has its own way of specifying LIMIT and OFFSET keywords. We had to reflect this - after checking the Arel API it seemed to be perfect for this:

# Add logic for handling the OFFSET keyword. In MaxDB's SQL dialect, the offset 
# value is just appended after the limit one with a comma, aka: LIMIT 10, 8
# instead of the core Arel behaviour: LIMIT 10 OFFSET 8. So we override core Arel
# behaviour here.
# See http://maxdb.sap.com/doc/7_8/45/4c3b2746991798e10000000a1553f6/content.htm
# 
# Drawback: This code obviously relies on the fact that the core Arel library 
# visits the limit and offset nodes in this exact order.
	      
def visit_Arel_Nodes_Offset o
  ", #{visit o.expr}"
  end
end

One of the hardest times we had was related with a failing application scenario, because of wrong default values. It was in the MaxDB adapter, which defined the following mapping in modify_types:

tp[:primary_key] = "INTEGER NOT NULL DEFAULT SERIAL PRIMARY KEY"

Somehow the default value for such columns was 0, and we checked with the MySQL adapter (where the scenario worked fine) that the default value was nil. After some debugging we verified that we really needed nil, otherwise ActiveRecord doesn't even think that value syncing with the db is needed. We checked with a very small and simple JDBC example that what the database returns as a default value for a primary key column (in terms of the JDBC metadata API) was that "DEFAULT SERIAL"-alike string. And it is clear that the above mapping is causing this (and ActiveRecord was treating this as 0). This was the fix that worked for us:

module ActiveRecord
  module ConnectionAdapters
    class MaxDBColumn < JdbcColumn
      def default_value(val)
        if String === val && val.include?("DEFAULT SERIAL")
          nil
        else
          val
        end
      end
    end

    class MaxDBSQLAdapter < JdbcAdapter
      include ArJdbc::MaxDB
      def jdbc_column_class
        ActiveRecord::ConnectionAdapters::MaxDBColumn
      end
    end
  end
end