Database reference guide

Introduction

Interchange is database-independent, and perhaps more so than almost any other powerful content management system.

Interchange can use GDBM_File, DB_File, DBI/SQL (various flavours), LDAP and MEMORY data sources.  In most cases, these different sources should operate in the same manner when called by Interchange's access methods.

Most all of Interchange's core functions do not use hard-coded column names;  Almost every column can have a configurable name.

Interchange does not require an external SQL server.  If you have a small data set and do not want to integrate your own tool set, you could use Interchange's internal database.  However, the order management functions of Interchange will be slower, and not as robust without an SQL server.  SQL is strongly recommended for at least the "state, "country", "orderline", "transactions" and "userdb" tables.  Any other tables that will have programmatic updates, such as "inventory", will also be best managed by an SQL server.

If you plan on using the Interchange ecommerce Admin UI, you should make the move to SQL.  Using an SQL server also makes it easier to integrate Interchange with other external tools and systems.

Text source files

Interchange reads delimited text files to obtain its initial data.  The text files are not the database;  They are the initial source of data for the database tables.

All database source files are located in the DataDir directory.  In the supplied Standard Interchange ecommerce demo website, the text source for the DefaultTables is called "products.txt.

If you are using one of the internal DBM database methods (GDBM_File or DB_File), any changes made to an ASCII source data file will be reflected in the target table in the next user session.  If the DefaultTables table contains less than a thousand records, updates will be instantaneous.  If that table is larger, updates will take longer.  You can use the NoImport directive to prevent auto updates from the ASCII source data file to the target table.

All of the ASCII source data files are kept in the DataDir directory, The ASCII files can include "^M" (carriage return) characters, but must have a line feed character at the end of each line.

Warning Non-UNIX/Linux users uploading files must use ASCII mode, not binary mode, when uploading ASCII files to the Interchange server.

Column delimiters in text files

Columns in the ASCII data files must be separated using a defined delimiter style.  The default column delimiter is a single tab character.

The available delimiter styles are listed below:

TAB

Columns are separated from one another with a single tab (^I) character.  No whitespace is allowed at the beginning of the line.  For example:

PIPE

Columns are separated from one another with a single pipe (|) character.  No whitespace is allowed at the beginning of the line.  For example:

CSV

Columns are enclosed in quotes and separated from one another with a single comma (,).  No whitespace is allowed at the beginning of the line.  For example:

Database-specific text file import differences

In the following configuration directive:

the "tablename" table will obtain its source data from the "tablename.txt" file, in the DataDir directory.  What is done with the data depends upon the type of underlying database being used.

The different database types, and their treatment of ASCII source data files, are described below:

GDBM_File

The table's source file is checked to see if it is newer than the actual database file (tablename.gdbm).  If it is newer then the database table is re-imported from the file.

This behaviour can be changed in a few ways:  If files should not be imported unless the .gdbm file disappears then set the NoImport directive:

If the source data file is only to be imported at when Interchange is (re)started, or the website is reconfigured, then use the IMPORT_ONCE attribute:

GDBM is the default table type if the GDBM_File Perl module is installed (as it should be on Linux).

DB_File

The table's source file is checked to see if it is newer than the actual database file (tablename.db).  If it is newer then the database table is re-imported from the file.

You can change this behaviour in the same way as described in the GDBM_File section, above.

DB_File is the default database type if the GDBM_File Perl module is not installed.  This is common on FreeBSD.

You can request a DB_File table type with the following:

DBI/SQL

If a file named "tablename.sql" is in the DataDir directory then the database table will not be imported from the ASCII data source file.

If there is no "tablename.sql" file then the following will occur:

Note The "tablename.sql" file will be prefixed with a dot if the HIDE_AUTO_FILES attribute is set true.

• DBI/SQL imports will only happen when Interchange is (re)started, or the website is reconfigured.
• Interchange will connect to the SQL server using the specified DSN, which is a DBI parameter meaning "Database Source Name".
• The table will be dropped with "DROP TABLE tablename".  This will occur without warning and can be prevented in several ways - see NoImportExternal.
• The table will be created.  If there are COLUMN_DEF specifications, in local configuration, then they will be used.  Otherwise, the key (the first column in the text file, by default) will be created with a CHAR(16) datatype and all other columns will be created using CHAR(128).  The table creation statement will be written to the error logfile.
• If there are any INDEX specifications then Interchange will send the appropriate "CREATE INDEX" SQL commands to the database server.
• The text source file will be imported into the SQL database server, placing the data in the appropriate columns in the named table.  Data typing must be user-configured, which means that if "Kevin" is placed in a column which is defined with a numeric datatype, then the table import will fail.  If an import for a critical table (such as DefaultTables) does not succeed then the website will not become active.

LDAP

LDAP is supported by Interchange.  This will be documented later.

MEMORY

Every time Interchange starts, or the website is reconfigured, the "tablename.txt" file will be imported into memory, which is treated as the table.  Otherwise, the database is not changed.  MEMORY is the default database type if both the GDBM_File and DB_File Perl modules are not installed.

You can request a MEMORY table type with the following:

Database attributes

There are certain functions, especially in SQL databases, that can be set with additional table attributes.

AUTO_NUMBER

This attribute will be used to initialise a autonumber counter file.  Whenever a row is added to the table without a primary key value, the sequence number contained in the file will be used and incremented.

By default, the sequence number file will be named "tablename.autonumber", where "tablename" is the name of the table.  If the HIDE_AUTO_FILES attribute is set true then the file will be hidden by prefixing the filename with a dot (i.e. ".tablename.autonumber").

If the AUTO_NUMBER_FILE attribute is set then the autonumber file will be named as specified there.

Warning A primary key sequence number will not be generated if you use direct SQL to insert rows into the table. If you are using a DBI/SQL database then you will probably find it preferable to use the AUTO_SEQUENCE attribute as this will work with direct SQL as well as with the facilities provided by Interchange's database access methods.

AUTO_NUMBER_FILE

You can use this attribute to either modify the name or location of the AUTO_NUMBER file, or you can allow a single AUTO_NUMBER file to be shared between multiple tables.

Availability This attribute was introduced in version 5.5.1, and is therefore not available for use with any earlier Interchange version.

CONTINUE

For text import, the CONTINUE extended table import attribute allows additional control over the format of imported text.

Note CONTINUE applies to all types except CSV.  Do not use NOTES unless using type LINE.

This can be set to one of NONE, UNIX, DITTO, LINE and NOTES, as listed below:

NONE

The default (NONE) is to simply split the line/row/record, according to the preferred delimiter, with no possible spanning of records.

UNIX

Setting CONTINUE to UNIX appends the next line to the current when it encounters a backslash character (\) at the end of a line, just like many UNIX commands and shells.

DITTO

DITTO is invoked when the key column (the first column) is blank.  It adds the contents of following columns to the one above, separated by a new line character.  This allows additional text to be added to a field beyond the 255 characters available with most spreadsheets and flat-file databases.

Example local configuration:

The associated "tablename.asc" file is as follows:

The description for the 00-0011 product will contain the content of the description column on both lines, separated by a new line.

Note The columns in the above example are lined up for readability.  You should note that they would really be tab-separated in this case.

This will work for multiple columns on the same line.  If the column contains a non-empty value, it will be appended.

LINE

LINE is a special setting so a multi-line column can be used.  Normally, when using the LINE type, there is only data on one line separated by one blank line.  When using CONTINUE LINE, there may be a number of columns which are each on a line, while the last one spans multiple lines up until the first blank line.

Example local configuration:

The associated "tablename.asc" file is as follows:

NOTES

NOTES reads a Lotus Notes structured text file.  The format is any number of fields, all except one of which must have a field name followed by a colon (:) and then the data.  Whitespace after the colon is optional.

Records are separated with a settable delimiting character which goes on a line by itself, much like a Perl "here document".  By default, the delimiter is a form feed character (^L).  The final field begins at the first blank line and continues until the end of the record.  This final field is named "notes_field", unless set as described below.

Interchange reads the field names from the first paragraph of the file.  The key field should be first, followed by other fields in any order.  If one (and only one) field name has whitespace then its name is used for the "notes_field".  Any characters after a space or tab are used as the record delimiter.  If there are none, then the delimiter returns to the default form feed (^L) and the field name reverts to "notes_field".  The field in question will be discarded, but a second field with whitespace will cause an import error.  Following records are then read by name, and only fields with data in them need be set.

Only the "notes_field" may contain a new line.  It is always the last field in the record, and begins at the first blank line.

The following example sets the delimiter to a tilde (~) and renames the "notes_field" to "description".

The associated "tablename.asc" file is as follows:

DBI/SQL

ALTERNATE_DSN

If the primary DSN is unavailable then try this alternate DSN, along with the ALTERNATE_USER and ALTERNATE_PASS values.

ALTERNATE_PASS

Password to use with the ALTERNATE_DSN .

ALTERNATE_USER

Username to use with the ALTERNATE_DSN .

AUTO_SEQUENCE

Tells Interchange to use a SQL sequence to number new rows inserted into the table.

If you need Interchange to create the table and the sequence, then you will need the following:

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

AUTO_SEQUENCE_MAXVAL

Sets the maximum value allowed in an AUTO_SEQUENCE counter:

AUTO_SEQUENCE_MINVAL

Sets the minimum value allowed in an AUTO_SEQUENCE counter:

AUTO_SEQUENCE_START

Sets the starting value for an AUTO_SEQUENCE counter:

DSN

A specification of the DBI driver and its data source.  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.  For instance:

The following has the same effect:

Some other examples of DSN specs:

COLUMN_DEF

A comma-separated set of lines in the form NAME=TYPE(N), where NAME is the name of the column, TYPE is the SQL datatype and N is the length (if required).  Most Interchange columns should be a character type, along the lines of VARCHAR(255).  CHAR(128) this is the default if a datatype is not specified for a column.  There can be as many COLUM_DEF specifiers as needed to define all of the columns.  This is not a DBI parameter;  It is specific to Interchange.

COMPOSITE_KEY

If you are using a DBI table with composite keys, where two or more columns combine to make a unique identifier for a row, you must tell Interchange so that it can request, update and delete data correctly.  For example:

If you don't specify a POSTCREATE or INDEX attribute for the table then Interchange will create a unique index, with all of the composite key elements, when it creates the table.

CREATE_SQL

You may specify the SQL that you want to use to create this table, rather than let Interchange work it out for you.

For instance:

HAS_LIMIT

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

HAS_TRANSACTIONS

Informs Interchange that the SQL database server in use has COMMIT and ROLLBACK capability for transactions.  For PostgreSQL and Oracle this will default to true For MySQL and other databases you can specify this attribute when appropriate for the table type in use.

Warning If you're using MySQL then you must ensure that you select a table type that supports transactions (such as "InnoDB").  See the TABLE_TYPE attribute.

INDEX

See below.

NUMERIC

This tells Interchange not to quote values for this column, which allows for numeric datatypes, such as INTEGER.  This can be specified as a comma-separated list of column names for a table, in no particular order, or can be specified multiple times.

PASS

The password used to log into the database server.  This is the same as the DBI_PASS environment variable.  If a password is not required for access to the server then you don't need to set the PASS attribute.  Also see the USER attribute.

POSTCREATE

You can use this attribute to specify one or more SQL statements that should be performed after Interchange creates a table.

PRECREATE

You can use this attribute to specify one or more SQL statements that should be performed before Interchange creates a table.

TABLE_COMMENT

You can use this attribute to instruct Interchange to attach a short comment to the tables it creates.

Availability This attribute was introduced in version 5.5.0, and is therefore not available for use with any earlier Interchange version.

TABLE_TYPE

You can use this attribute to specify the type of table that Interchange should create.

Note This attribute will be ignored if the target database server is not MySQL.

Valid MySQL table types are as follows:

• MyISAM (MySQL default table type)
• MERGE
• ISAM
• HEAP
• InnoDB (Necessary if HAS_TRANSACTIONS is in use)
• BerkeleyDB or BDB (Support for this table type was dropped from MySQL version 5.1)

USER

The user name used to log into the database server.  This is the same as the DBI_USER environment variable.  If a user name is not required for access to the server then you don't need to set the USER attribute.  Also see the PASS attribute.

Miscellaneous DBI driver attributes

You can set miscellaneous DBI attributes by name.  One attribute of particular interest is "ChopBlanks", which should be set for DBI drivers (such as PostgreSQL) which, by default, return space-padded fixed-length character columns.  For example:

The supported DBI driver attribute list, as of the latest Interchange 5.7.0 (development) version, is:

AutoCommit ChopBlanks CompatMode InactiveDestroy LongReadLen

LongTruncOk PrintError RaiseError Warn

DELIMITER

An Interchange delimiter type for ASCII data files can be either TAB, PIPE or CSV.  The default for SQL databases is TAB.  Use DELIMITER if another type will be used to import.  This is not a DBI parameter;  It is specific to Interchange.

EXCEL

Microsoft Excel is a widely-used tool to maintain Interchange tables, for some reason, but has several problems with its standard tab-delimited export.  Problems include enclosing fields containing commas in quotes, generating extra carriage returns embedded in records, and not including trailing blank fields.  To avoid problems, use a text-qualifier of NONE.

You can set the EXCEL attribute to 1 to avoid these problems on import, as follows:

This is normally used only with tab-delimited files.

HIDE_AUTO_FILES

Normally, files with suffixes like ".autonumber", ".numeric" and ".sql" are automatically created in the DataDir directory.  These files contain information that Interchange uses to keep track of certain table information.

For instance, the ".sql" files are empty "marker" files that tell Interchange that a table should not be dropped and recreated when Interchange starts up.

If the HIDE_AUTO_FILES attribute is set true for a table then these "automatic" files will be prefixed with a dot ("."), thereby "hiding" the files and keeping the DataDir directory relatively clean.

You can, of course, set a default HIDE_AUTO_FILES value for all tables by using the DatabaseDefault directive, or may set this for only specific tables by using the Database directive.

Warning If you set HIDE_AUTO_FILES attribute true, where it was previously either false or unspecified, then be sure to rename all of the appropriate automatic files, listed above.  If HIDE_AUTO_FILES is toggled true then the automatic files must be renamed so that they are prefixed with a dot (".").  Similarly, if HIDE_AUTO_FILES is toggled false then the dot prefix must be removed from the appropriate automatic files. If you fail, for instance, to rename the ".sql" file(s) then you may find that Interchange will drop and recreate your table(s), and possibly import default data from the matching ".txt" file.

HIDE_FIELD

Normally, Interchange will return all rows that match the search specification.  The HIDE_FIELD attribute will prevent rows from being returned if they have a true (non-zero and non-blank) value in the named column.

Warning Results from direct SQL queries, using tags such as [query], will be unaffected by this attribute.

IMPORT_ONCE

The IMPORT_ONCE attribute tells Interchange not to re-import the table's data from the ASCII data file every time the file changes.  Normally, Interchange does a comparison of the DBM-based table file's modification time with the ASCII data file every time it is accessed.  If the ASCII source data file is newer it will re-import the file.

IMPORT_ONCE instructs Interchange to only to import data when Interchange is (re)restarted or the website is reconfigured.

SQL databases don't normally need this.  They will only be imported once in normal operation.  Also see NoImport and NoImportExternal local configuration directives for a way to guarantee that the table will never be imported.

IMPORT_ONCE is always in effect for MEMORY tables.  A website reconfiguration, or Interchange restart, is required to force an import.

INDEX

DBM files

Interchange will automatically build index files for a fast binary search of an individual column.  This type of search is useful for looking up the author of a book based upon the beginning of their last name, a book title based on its beginning or other similar situations.

Such a search requires a dictionary ordered index with the column to be searched contained in the first field and the table key (product code or whatever) in the second field.  If the INDEX field attribute is specified then Interchange will build the index upon database import:

If the "title" field is the fourth column in the "tablename" table, then a file called "tablename.txt.4" (or ".tablename.txt.4" if the HIDE_AUTO_FILES attribute is set true) will be built containing two tab-separated fields.  For example:

Options can be appended to the column name, after a colon character (:).  The most useful option is "f", which does a case-insensitive sort.  The "mv_dict_fold" option must be added to any search, in this case.

Another option is "c", which stands for "comma index".  Use this option to index on comma-separated sub-fields contained within a column.

This can get slow for larger tables and columns.  Interchange will split the field on a comma (stripping surrounding whitespace) and make index entries for each one.  This allows multiple categories in one column while retaining the fast category search mechanism.  It might also be useful for a keywords column.

Note There is currently no facility to create unique indices on columns in non-SQL tables.

Tables in a SQL database

If you are using a DBI/SQL-based database server for your table then indexing will be handled by your database server.

All you need to do is define which columns you want to index and Interchange will send the appropriate "CREATE INDEX" SQL to the database server after it has created the table.

Note In the above example, Interchange will send "CREATE UNIQUE INDEX" SQL to the database, instead of the default "CREATE INDEX" SQL, to create a unique index for the "ean" column.  The "category" column will get a default (non-unique) index.

KEY

By default, the primary key column is expected to be named "code".  If a different column name is required then the KEY attribute can be used to specify the column to use.  Don't use this unless you are prepared to alter all searches, imports and exports accordingly.  It is best to just accept the default and make the first column the key for any Interchange table.  If you are creating your own tables, rather than modifying the Standard ecommerce demo, then you can do whatever you like, of course.

LARGE

Interchange tables containing many rows can result in a noticeable slowdown when displayed by the Interchange Admin UI.

You can set the LARGE attribute to 1 to avoid this problem, as follows:

In this case the Interchange Admin UI supplies only input boxes to search rows in the database, instead of selecting all of the rows from the table, sorting them and then creating more lists.

MEMORY

Interchange's memory-based tables are the fastest possible way to organise and store frequently used data.  To force a database to be built in memory instead of DBM or SQL etc., use the MEMORY attribute:

Obviously, large tables will use a great deal of memory, and the data will need to be re-imported from the ASCII source file every time Interchange is (re)started or the website is reconfigured.  The big advantage of using MEMORY is that the table remains open at all times and does not need to be reinitialised upon every connect.  Use it for smaller tables that will be frequently accessed.

In-memory tables are read only.  The MEMORY attribute forces IMPORT_ONCE.

Also see the MIRROR attribute.

MIRROR

If you are using a MEMORY table then you might like to define two tables at once, as follows:

In the above example, the "country_memory" table could be used for quick in-memory data access, and the "country" table could be used for updates.  Updates wouldn't be reflected in the in-memory side of the mirror until either the website is reconfigured or Interchange is restarted.

NAME

A space-separated field of column names for a table.  This is normally not used;  Interchange should resolve the column names properly upon query, Set this if a website throws an error along the lines of "dbi: can't find field names".  The first column should be named "code", but that name can be overridden using the KEY setting.  This is not a DBI parameter;  It is specific to Interchange.  If this specifier is used then all columns must be listed, in order of their position in the table.

NO_ASCII_INDEX

See the INDEX attribute will create an ASCII index file, as described in the section, above.

ASCII index files are not useful when using SQL database servers, so this attribute can be used to prevent Interchange from creating them.

For more information, type "perldoc DBI" at a shell prompt.

NO_SEARCH

Normally, Interchange will allow you to search any table.  The NO_SEARCH attribute indicates that the table should not be searchable by default.

Warning Results from direct SQL queries, using tags such as [query], will be unaffected by this attribute.

REAL_NAME

Sometimes it may be convenient to have a table named with a consistent value in Interchange, despite its name in the underlying database server.  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 "real_products" table for SQL statements:

Of course if you have SQL queries that are passed verbatim to Interchange (when using the query tag, for instance) you must use the REAL_NAME in those.

UPPERCASE

Tells Interchange to force column names to UPPER CASE when using the [item-data] and [item-field] etc. This is typically used for Oracle, and some other SQL implementations.  It is not necessary when using MySQL or PostgreSQL.

WRITE_CONTROL

Interchange tables can be written in the normal course of events, either using the [import] tag, or with a tag such as [data table=table column=column key=key value="new value"].  To control writing of a global table, or to restrict writes to a certain website within a series of SubCatalogs, or make one read only, see the following:

To enable write control:

Once this is done, to make a table read-only, which won't allow writing even if [tag flag write]tablename[/tag] is specified:

To have control with [tag flag write]tablename[/tag]:

To limit write access to certain websites, set:

In the above example, the "rtfm_ic" website will not be allowed to update the "tablename" table, while "cursor" website will if [tag flag write]tablename[/tag] is enabled.  If a table is to always be writable, without having to use [tag flag write]...[/tag], then define the following:

The default behaviour of SQL tables is equivalent to WRITE_ALWAYS, while the default for GDBM_File, DB_File and MEMORY tables is equivalent to:

Examples

Here is an example of a DBI database table set up on a MySQL server.  This example sets up a few columns, a couple of indexes and some comment text for the table.

MySQL and the DBI and DBD::mysql Perl modules must be completely installed and tested, and the "rtfm_ic" database must be available to the "interchange" user for this to work.

To change to ODBC, the only changes required are most likely to be:

The DSN setting is specific to a ODBC setup.  The ChopBlanks DBI attribute takes care of the space-padding issues in Solid, and some other databases, and is not specific to ODBC.  Once again DBI, DBD::ODBC and the appropriate ODBC driver must be installed and tested before you an expect Interchange to connect to a database server via an ODBC link.

The products table(s)

In an ecommerce environment, each product being sold should be given a product code, usually referred to as an SKU.  A SKU (Stock Unit) is a short code that uniquely identifies a product.  The text file associated with each of the ProductFiles table(s) is an ASCII-delimited list of all the product codes, along with an arbitrary number of columns, which must contain at least the description and price (as named using the PriceField and DescriptionField local configuration directives).  Any additional data can be placed in any arbitrary column.

Note Column names may be case-sensitive, depending upon the underlying database type.

The product code, or SKU, must be the first column in the line, and must be unique.  Product codes must only contain A-Z, a-z and 0-9, along with hyphen (-), underscore (_), hash (#), slash (/) and period (.) characters.  Note that slash (/) will interfere with on-the-fly page references, so it's best to avoid using that character in product codes.

Columns should be separated by one of the defined delimiting schemes (TAB, PIPE or CSV), and are case-sensitive in some cases.

Warning Column names must be on the first line of the ASCII text file.  These names must exactly match the column names used by any [item-field] tags in your pages, otherwise Interchange will not be able to access them properly.  Column names must only contain A-Z, a-z, 0-9 and underscore (_) characters.

Warning Some SQL databases have reserved words which cannot be used as column names.  Interchange's other (non-SQL database) table types do not have this restriction.

More than one table may be used as a products database.  If the ProductFiles local configuration directive is set to a space-separated list of valid Interchange table identifiers, then those tables will be searched (in the order specified) for any products to be displayed or ordered, as well as for product information accessed via tags such as [price] and [PREFIX-*] etc.

The ProductFiles table(s) don't have to be used for products.  They could be used for anything.  In a CMS (content management system), the table(s) could be used to store page text, or any other information you'd like to access and display via the flypage mechanism.

Note If you're not using Interchange to handle products (this RTFM website is powered by Interchange and doesn't handle products), then it's best to use the DefaultTables directive (introduced with Interchange 5.4.0) instead. The ProductFiles and DefaultTables directives are aliases of one another, and therefore do the same job.  Using one directive or the other, depending upon the website type, helps keep your "catalog.cfg" configuration looking clean and fit for purpose.

Global tables

If a table is to be available to all websites on the Interchange server, then it may be defined in the global (interchange.cfg) configuration.  Any website running under that Interchange instance will be able to use any global tables.  Global tables will be writable by any website unless WRITE_CONTROL is used.

Category:  Interchange database

Last modified by: Kevin Walsh Modification date: Wednesday 12 March 2008 at 7:32 PM (EDT)