Using the OpenLink Rails ODBC Adapter with Ingres
Table of Contents
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.
To match the formats of Ruby's Time and DateTime types, the following Ingres settings were used during testing:
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|
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.
- 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.
The ODBC adapter supports quoted identifiers if the
database is configured to support them. You can check this
by issuing the query
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
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.