|> Home > Documentation > Latest documentation > Interchange database > Database reference guide|
Database reference guide
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.
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.
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:
Columns are separated from one another with a single tab (^I) character. No whitespace is allowed at the beginning of the line. For example:
Columns are separated from one another with a single pipe (|) character. No whitespace is allowed at the beginning of the line. For example:
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:
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).
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:
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:
LDAP is supported by Interchange. This will be documented later.
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:
There are certain functions, especially in SQL databases, that can be set with additional table attributes.
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.
For text import, the CONTINUE extended table import attribute allows additional control over the format of imported text.
The default (NONE) is to simply split the line/row/record, according to the preferred delimiter, with no possible spanning of records.
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 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.
This will work for multiple columns on the same line. If the column contains a non-empty value, it will be appended.
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 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:
Password to use with the ALTERNATE_DSN .
Username to use with the ALTERNATE_DSN .
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.
Sets the maximum value allowed in an AUTO_SEQUENCE counter:
Sets the minimum value allowed in an AUTO_SEQUENCE counter:
Sets the starting value for an AUTO_SEQUENCE counter:
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:
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.
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:
You may specify the SQL that you want to use to create this table, rather than let Interchange work it out for you.
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.
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.
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.
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.
You can use this attribute to specify one or more SQL statements that should be performed after Interchange creates a table.
You can use this attribute to specify one or more SQL statements that should be performed before Interchange creates a table.
You can use this attribute to instruct Interchange to attach a short comment to the table it creates.
You can use this attribute to specify the type of table that Interchange should create.
Valid MySQL table types are as follows:
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 0.0.0 (development) version, is:
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Normally, Interchange will allow you to search any table. The NO_SEARCH attribute indicates that the table should not be searchable by default.
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.
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.
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:
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.
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.
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.
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.