1.11. SQL/DBI parameters
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.
Sets the MAXVAL to have in an AUTO_SEQUENCE counter:
Database foo AUTO_SEQUENCE_MAXVAL 1000000
Sets the MINVAL to have in an AUTO_SEQUENCE counter:
Database foo AUTO_SEQUENCE_MINVAL 10
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.
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
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.
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.
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)"
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"
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.