Interchange Databases: SQL/DBI parameters

1.11. SQL/DBI parameters

1.11.1. AUTO_SEQUENCE

Tells Interchange to use a SQL sequence to number new database items inserted into the database.

If you have Interchange create the table, then you need to do:

  Database foo foo.txt dbi:mysql:test
  Database foo AUTO_SEQUENCE foo_seq

Then on MySQL, Pg, or Oracle, Interchange will create an integer key type and a sequence (or AUTO_INCREMENT in MySQL) to maintain the count.

1.11.2. AUTO_SEQUENCE_MAXVAL

Sets the MAXVAL to have in an AUTO_SEQUENCE counter:

  Database foo AUTO_SEQUENCE_MAXVAL  1000000

1.11.3. AUTO_SEQUENCE_MINVAL

Sets the MINVAL to have in an AUTO_SEQUENCE counter:

  Database foo AUTO_SEQUENCE_MINVAL  10

1.11.4. AUTO_SEQUENCE_START

Sets the starting value for an AUTO_SEQUENCE counter:

  Database foo AUTO_SEQUENCE_START  1000

If you are using a DBI table with composite keys, where two or more fields combine to make the unique identifier for a record, you must tell Interchange so it can request data in the right way. To do this, set:

  Database  product_spec product_spec.asc dbi:mysql:foobase
  Database  product_spec COMPOSITE_KEY sku feature
  Database  product_spec COLUMN_DEF    "sku=varchar(32)"
  Database  product_spec COLUMN_DEF    "feature=varchar(128)"

If you want to create a custom index for the table, do so. If you don't specify a POSTCREATE or INDEX parameter for the table, Interchange will create a unique index with all composite key elements at table creation time.

1.11.6. DSN

The data source name (DSN) for the database. It is beyond the scope of this document to describe this in detail.

Normally this is set as the type in the initial Database configuration line, i.e.

  Database  foo  foo.txt  dbi:mysql:foobase

This has the same effect:

  Database  foo  foo.txt  SQL
  Database  foo  DSN      dbi:mysql:foobase

Some other examples of DSN specs:

 Database  foo  DSN  dbi:mysql:host=db.you.com;database=foobase
 Database  foo  DSN  dbi:Pg:dbname=foobase
 Database  foo  DSN  dbi:Oracle:host=myhost.com;sid=ORCL

1.11.7. HAS_TRANSACTIONS

Informs Interchange that the SQL database in use has commit() and rollback() for transactions. For PostgreSQL and Oracle this should be set properly to 1 -- for MySQL and other databases you have to set it.

1.11.8. HAS_LIMIT

Informs Interchange that the SQL database in use has as the LIMIT extension to SQL to limit return from queries. Should be set properly by default for MySQL, PostgreSQL, and Oracle.

1.11.9. POSTCREATE

One or more SQL statements that should be performed after Interchange creates a table.

 Database foo POSTCREATE "create unique index foo_idx on foo(key1,key2)"
 Database foo POSTCREATE "create index mulkey_idx on foo(mulkey)"

1.11.10. PRECREATE

One or more SQL statements that should be performed before Interchange creates a table.

 Database foo POSTCREATE "drop table foobackup"
 Database foo POSTCREATE "alter table foo rename to foobackup"

1.11.11. REAL_NAME

Sometimes it may be convenient to have a table named a consistent value in Interchange despite its name in the underlying database. For instance, two divisions of a company may share orders but have different products tables. You can tell Interchange to name the table products for its purposes, but use the products_a table for SQL statements:

 Database products REAL_NAME products_a

Of course if you have SQL queries that are passed verbatim to Interchange (i.e. the [query ...] tag) you must use the REAL_NAME in those.