Using the OpenLink Rails ODBC Adapter with Ingres

Table of Contents

Ingres Sequences

ActiveRecord relies heavily on the ability of databases to generate integer primary key values automatically, typically through an autoincrementing column type or a sequence associated with each table. As Ingres does not have an autoincrementing column type, the OpenLink adapter assumes that each table has an associated sequence, named <tablename>_seq. This must be created manually, unless the table is created through the ActiveRecord *create_table* method, in which case the sequence is created automatically.

Sequences were introduced in Ingres r3. Consequently, the OpenLink adapter does not support earlier Ingres releases.

Configuration

To match the formats of Ruby's Time and DateTime types, the following Ingres settings were used during testing:

• II_DATE_FORMAT=SWEDEN

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 Ingres agents/Lite drivers are shown below.

Active Record abstract type	Ruby type	Ingres type
:primary_key	-	INTEGER PRIMARY KEY NOT NULL
:string	String	TEXT(255)
:boolean	Object	INTEGER1
:datetime	Time	DATE
:time	Time	DATE
:timestamp	Time	DATE
:date	Date	DATE
:text	String	LONG VARCHAR
:integer	Fixnum	INTEGER
:float	Float	FLOAT
:binary	String	LONG BYTE
:decimal	BigDecimal	DECIMAL

It is suggested you use these native database types when creating tables to support your Rails models. Example table definitions are attached below:

• ingres.sql These are the table definitions used for creating fixtures during testing - similar fixture definitions can be found for other databases in the ActiveRecord sources under <active-record-root>/test/fixtures/db_definitions.

Restrictions

Restrictions imposed by Ingres page size

• Some ALTER TABLE commands are only supported by Ingres for page sizes over 2KB. e.g.
  • ALTER TABLE ... ALTER <column_name>
  • ALTER TABLE ... ADD|DROP <column_name>
• These restrictions will in turn affect some methods in the ODBCAdapter, for instance #change_column, #add_column and #remove_column.
• It is suggested that you create your tables with a page size of 8KB. e.g.
  

  
  
           CREATE TABLE accounts (
             id INTEGER PRIMARY KEY NOT NULL,
             firm_id INTEGER DEFAULT NULL,
             credit_limit INTEGER DEFAULT NULL
             ) WITH PAGE_SIZE=8192;
  

  
  Tables created using the *create_table* method are created with a page size of 8KB.

Quoted Identifiers

The ODBC adapter supports quoted identifiers if the database is configured to support them. You can check this by issuing the queryselect dbmsinfo('db_delimited_case'). If the database returns 'MIXED', then it is configured to support quoted identifiers. As far as I know SQL92 conformance (and quoted identifier support) is only configurable at installation time (through the II_SQL92 environment variable). The default Ingres configuration seems different on different platforms. Ingres r3 on Windows appears to enable quoted identifiers by default, whereas Ingres r3 on Linux does not.

Ingres r3 on Windows:
select dbmsinfo('db_name_case') # returns UPPER
select dbmsinfo('db_delimited_case') # returns MIXED

Ingres r3 on Linux:
select dbmsinfo('db_name_case') # returns LOWER
select dbmsinfo('db_delimited_case') # returns LOWER

The SQL script used to create fixtures (test/fixtures/db_definitions/ingres.sql) for testing ActiveRecord assumes that quoted identifier support is available. If this is not the case, you should edit the ingres.sql file, removing any quotes around the extendedWarranty column of the computers table definition.