Using the OpenLink Rails ODBC Adapter with Progress

Table of Contents

Progress 9/10 (SQL-92 engine)

The ODBC adapter assumes that connections to Progress 9 or later are made through the SQL-92 interface. Consequently, ensure that the underlying ODBC driver is also written to use this engine, as opposed to the SQL-89 engine.

Type Mappings

ActiveRecord defines a number of abstract data types. These and the corresponding Ruby data types are listed in the table below. Which native database types the abstract types map to will depend to some extent on the underlying ODBC driver used by the OpenLink Rails ODBC Adapter. The native type mappings for the OpenLink Progress 9/10 (SQL-92) agents/Lite drivers are shown below.

Active Record abstract type Ruby type Progress SQL-92 type
:primary_key - INTEGER NOT NULL PRIMARY KEY
:string String VARCHAR(255)
:boolean Object BIT
:datetime Time TIMESTAMP
:time Time TIME
:timestamp Time TIMESTAMP
:date Date DATE
:text String VARCHAR(31982)
:integer Fixnum INTEGER
:float Float FLOAT
:binary String VARBINARY(31982)

It is suggested you use these type mappings when creating tables to support your Rails models.

User/Schema Creation

Ideally, you should create a separate user/schema for your Rails application. e.g.


  CREATE USER 'RAILS','RAILS'
  GRANT DBA TO RAILS

Both these command must be issued in the same database session. Once executed, it is no longer possible to connect to the database as the database owner in a subsequent session.

Test Table Creation

The ODBC adapter distribution includes some SQL scripts for creating test tables for use with the ActiveRecord test suite contained in the file base_test.rb. These scripts are: progress.sql, progress.drop.sql, progress2.sql and progress2.drop.sql. They can be run using Progress's SQL Explorer tool. When running the scripts, ensure autocommit is turned on. This can be done through the View > Options menu command.

Migration Support

Limited support for migrations is available. Because of limitations in Progress SQL, several migration related ActiveRecord methods are not supported, specifically:

  • change_column
  • remove_column
  • rename_column
  • rename_table

Consequently, in the ActiveRecord test suite, many of the tests in migration_test.rb fail. Several tests add columns to a table. The tests then attempt to remove these columns during the 'teardown' phase after each test completes. Because remove_column is not supported against Progress, the added columns are not removed and subsequent tests may return a 'duplicate column name' error when attempting to add columns of the same name.


Progress 8 (SQL-89 engine)

The notes below describe some of the implementation details of the Rails ODBC Adapter's support for Progress 8. The database imposes some restrictions which you should be aware of when using the adapter. These restrictions are largely specific to the SQL89 engine in Progress version 8 or earlier. Progress version 9 onwards includes a SQL92 engine which is free from these limitations.

Configuration

Because Progress does not allow numeric literals to be bound to numeric columns, you must set the connection option :convert numeric_literals to true to avoid errors similar to:

'Incompatible data types in expression or assignment.
 (223)'
e.g. In your Rails application's database.yml file:
development:
  adapter: odbc
  dsn: a610_pro83c_ripley_rails3
  #username: 
  #password: 
  trace: true
  convert_numeric_literals: true

If convert numeric_literals is off, the following query generates the above error

SELECT * FROM accounts WHERE (accounts.id = '10000') 

Turning on convert numeric_literals changes the query to

SELECT * FROM accounts WHERE (accounts.id = 10000) 

which Progress accepts.

Type Mappings

ActiveRecord defines a number of abstract data types. These and the corresponding Ruby data types are listed in the table below. Which native database types the abstract types map to will depend to some extent on the underlying ODBC driver used by the OpenLink Rails ODBC Adapter. The native type mappings for the OpenLink Progress 8 agents/Lite drivers are shown below. Progress 8 provides only a very limited range of types. As a result, several of the ActiveRecord abstract data types cannot be supported.

Active Record abstract type Ruby type Progress SQL-89 type
:primary_key - INTEGER NOT NULL UNIQUE
:string String CHARACTER(255)
:boolean Object LOGICAL
:datetime Time Not supported
:time Time Not supported
:timestamp Time Not supported
:date Date DATE
:text String CHARACTER(31000)
:integer Fixnum INTEGER
:float Float DOUBLE PRECISION
:binary String Not supported

It is suggested you use these type mappings when creating tables to support your Rails models.

DDL and Single User Mode

Progress 8 restricts DDL operations to single-user mode. Schema objects can only be created by a particular user when that user is the only one connected to the database. This restriction is enforced through a schema lock. The schema lock has two modes, shared and exclusive. Each Progress connection holds a schema share lock while the connection exists. In order for a connection to update the schema, it must acquire a schema exclusive lock. For this to be possible, it must be the only connection to the database. (Note that this restriction was lifted in Progress 9.)

Sequence Simulation

Although Progress 8 does support sequences, it appears a native Progress sequence can only be created using the Data Dictionary Tool, not through SQL89 DDL. Also, there appears to be no way to retrieve a native sequence's current or next value through SQL89. Consequently, instead of using Progress's built-in sequences, the ODBC Adapter simulates them.

The adapter uses a table, railsseqs, to maintain a sequence for each table created with an id column. Each such table has an entry in railsseqs. The railsseqs table itself is defined as:


RAILSSEQS (
SEQ_NAME CHARACTER(32) NOT NULL UNIQUE,
SEQ_NEXT_VAL INTEGER NOT NULL DEFAULT 10000
)

When you create a table using the #create_table method, the method creates an entry in railsseqs. For instance, #create_table('accounts') creates an entry ['ACCOUNTS_SEQ', 10000]. Each table created through #create_table has a corresponding sequence in railsseqs, named <table_name>_seq, unless the option :id => false is specified. The table's id column will typically be a primary key column declared as "INTEGER NOT NULL UNIQUE".

The sequence value is retrieved and incremented when #next sequence_value is called and the sequence dropped by the #drop_table method. #next_sequence_value is not generally called directly by an application. It is used by ActiveRecord to get the next primary key value when inserting a new database record.

The railsseqs table and a table's sequence entry in railsseq are both created automatically by the adapter if the adapter detects one or other doesn't exist while servicing a call to #next sequence_value or #create_table. This safeguard in #next_sequence_value is necessary because tables might not always be created through #create_table. They could, for instance, have been created through #execute('CREATE TABLE ...') instead.

Although the adapter is capable of creating the railsseqs table as necessary, it is recommended that you create this table beforehand along with all other tables required by your Rails application because all Progress DDL commands must be issued in single-user mode.

Transaction Isolation

At present Christian Werner's Ruby ODBC module offers no way to set the desired transaction isolation level explicitly. With OpenLink's Progress ODBC driver, the default isolation level is "dirty read" unless the -Q2 startup parameter is specified. The -Q2 option enforces serializable transaction isolation. However, its use is not recommended because of the reduction in concurrency and potential for locking conflicts.

When the Rails ODBC Adapter retrieves and updates a sequence value maintained in railsseqs, the sequence record is SELECTed with a 'FOR UPDATE' clause, ensuring repeatable-read isolation for the duration of the transaction. Repeatable-read isolation will prevent two Rails applications or connections from simultaneously updating a sequence. It does not guard against phantom inserts. Ideally, serializable isolation would be used to prevent phantoms. But as explained above, the use of the -Q2 option and serializable transactions is not recommended. Repeatable-read isolation should nevertheless be sufficient. It is very unlikely that two connections would attempt to create a sequence simultaneously for the same table, particularly as DDL operations in Progress 8 are restricted to single-user mode and as a consequence all tables (and hence sequences) required by a Rails application would be created beforehand. Even if two connections were to attempt to create a sequence for the same table simultaneously, the UNIQUE constraint on the SEQ_NAME column would prevent this. But Rails applications will have to cope with the resulting exception.

Migration Support

Limited support for migrations is available. It is possible to create and drop tables as part of a migration. However migrations, like all DDL operations, are restricted to single-user mode We suggest that all tables required by a multi-user Rails application should be defined beforehand. The application should not attempt to create or drop tables itself in multi-user mode.

Because of limitations in Progress SQL89, several migration related ActiveRecord methods are not supported, specifically:

  • change_column
  • remove_column
  • rename_column
  • rename_table

Although the Rails ODBC Adapter does support migrations against Progress 8, obviously the above methods cannot be used. Consequently, in the ActiveRecord test suite, many of the tests in migration_test.rb fail. Several tests add columns to a table. The tests then attempt to remove these columns during the 'teardown' phase after each test completes. Because remove_column is not supported against Progress 8, the added columns are not removed and subsequent tests may return a 'duplicate column name' error when attempting to add columns of the same name.

SQL-89 DDL vs Data Dictionary Tool

If you choose to create your tables manually, rather than using migrations, be consistent in your choice of method. Use either SQL/DDL or the Data Dictionary Tool, but don't mix the two. Tables created by the Data Dictionary Tool cannot be dropped via SQL/DDL (ie. via ODBC). Attempts to do so may result in the error: "<table_name> is not a SQL-managed table or view (970)".

Progress SQL-89 Limitations

Reserved Words

Several of the test tables used by the ActiveRecord test suite use "type" as a column name. The test tables are defined in .sql files held in <ACTIVE_RECORD>/test/fixtures/db_definitions. Any test tables definitions for Progress which use "type" as a column name must quote the column name because "type" is a reserved word in Progress SQL.

Column Aliases

Progress SQL-89 does not support column aliases. The ODBC adapter strips any 'as <column_alias>' clauses from SELECT statements it receives before submission to Progress. e.g. SELECT ENAME AS EMPLOYEE_NAME FROM EMP becomes SELECT ENAME FROM EMP.

Rails Test Database

During development, a Rails application will typically be configured to include both a development and a test database. Prior to running a test, the Rails testing infrastructure starts with a 'clean' test database and recreates the development database schema in the test database. If you use the -Q2 startup option, Rails tests will not run, e.g. if a "rake test_units" or "rake test_functional" command is issued. It appears that when setting up a test, Rails uses one connection to recreate the development database schema in the test database. A second connection is then used to run the test. However, when the second connection is made, the Progress client first attempts to read the information schema. To do so, it must acquire an information schema share lock; but it is unable to, because the first connection still holds an exclusive schema lock after modifying the test database schema. The second connection then hangs, waiting for a lock. In short, the tests will only run if run in single-user mode without the -Q2 option.

ODBC-Rails and the ODBC-Rails website are Copyright (C) OpenLink Software 2006-2008