.\" Automatically generated by Pod::Man version 1.15
.\" Wed May 5 11:41:28 2004
.\"
.\" Standard preamble:
.\" ======================================================================
.de Sh \" Subsection heading
.br
.if t .Sp
.ne 5
.PP
\fB\\$1\fR
.PP
..
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Ip \" List item
.br
.ie \\n(.$>=3 .ne \\$3
.el .ne 3
.IP "\\$1" \\$2
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings. \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote. | will give a
.\" real vertical bar. \*(C+ will give a nicer C++. Capital omega is used
.\" to do unbreakable dashes and therefore won't be available. \*(C` and
.\" \*(C' expand to `' in nroff, nothing in troff, for use with C<>
.tr \(*W-|\(bv\*(Tr
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
. ds -- \(*W-
. ds PI pi
. if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
. if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch
. ds L" ""
. ds R" ""
. ds C` ""
. ds C' ""
'br\}
.el\{\
. ds -- \|\(em\|
. ds PI \(*p
. ds L" ``
. ds R" ''
'br\}
.\"
.\" If the F register is turned on, we'll generate index entries on stderr
.\" for titles (.TH), headers (.SH), subsections (.Sh), items (.Ip), and
.\" index entries marked with X<> in POD. Of course, you'll have to process
.\" the output yourself in some meaningful fashion.
.if \nF \{\
. de IX
. tm Index:\\$1\t\\n%\t"\\$2"
..
. nr % 0
. rr F
.\}
.\"
.\" For nroff, turn off justification. Always turn off hyphenation; it
.\" makes way too many mistakes in technical documents.
.hy 0
.if n .na
.\"
.\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2).
.\" Fear. Run. Save yourself. No user-serviceable parts.
.bd B 3
. \" fudge factors for nroff and troff
.if n \{\
. ds #H 0
. ds #V .8m
. ds #F .3m
. ds #[ \f1
. ds #] \fP
.\}
.if t \{\
. ds #H ((1u-(\\\\n(.fu%2u))*.13m)
. ds #V .6m
. ds #F 0
. ds #[ \&
. ds #] \&
.\}
. \" simple accents for nroff and troff
.if n \{\
. ds ' \&
. ds ` \&
. ds ^ \&
. ds , \&
. ds ~ ~
. ds /
.\}
.if t \{\
. ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u"
. ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u'
. ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u'
. ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u'
. ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u'
. ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u'
.\}
. \" troff and (daisy-wheel) nroff accents
.ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V'
.ds 8 \h'\*(#H'\(*b\h'-\*(#H'
.ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#]
.ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H'
.ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u'
.ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#]
.ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#]
.ds ae a\h'-(\w'a'u*4/10)'e
.ds Ae A\h'-(\w'A'u*4/10)'E
. \" corrections for vroff
.if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u'
.if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u'
. \" for low resolution devices (crt and lpr)
.if \n(.H>23 .if \n(.V>19 \
\{\
. ds : e
. ds 8 ss
. ds o a
. ds d- d\h'-1'\(ga
. ds D- D\h'-1'\(hy
. ds th \o'bp'
. ds Th \o'LP'
. ds ae ae
. ds Ae AE
.\}
.rm #[ #] #H #V #F C
.\" ======================================================================
.\"
.IX Title "icdatabase 8"
.TH icdatabase 8 "Interchange 5.2.0" "2004-05-05" "Interchange"
.UC
.SH "NAME"
icdatabase \- Interchange Databases
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
.SH "Databases and Interchange"
.IX Header "Databases and Interchange"
Interchange is database-independent, perhaps more so than almost any
other powerful content management system.
.PP
Interchange can use \s-1GDBM\s0, DB_File, \s-1SQL\s0, \s-1LDAP\s0, or in-memory databases.
In most cases, these different database formats should operate the
same when called by Interchange's access methods.
.PP
Also, most all of Interchange's core functions do not use hard-coded
field names; virtually every field can have a configurable name.
.PP
Interchange does not require an external \s-1SQL\s0 database. If you have a
small data set and do not want to integrate your own tool set, you
cound use Interchange's internal database. However, the order
management functions of Interchange will be slower and not as robust
without an \s-1SQL\s0 database. \s-1SQL\s0 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 be best placed in \s-1SQL\s0.
.PP
If you plan on using Interchange Admin \s-1UI\s0, you should make the move to
\&\s-1SQL\s0. It provides easy import routines for text files that should
replace text-file uploads.
.PP
Keeping a database in an \s-1SQL\s0 manager makes it easier to integrate
Interchange with other tools. Interchange can be used to maintain a
spreadsheet containing product information through modifying the file
products.txt as needed. References to \s-1SQL\s0, \s-1DBI\s0, and \s-1DBD\s0 can be
ignored.
.Sh "Text Source Files"
.IX Subsection "Text Source Files"
Interchange reads delimited text files to obtain its initial data.
However, the text files are not the database. They are the source
information for the database tables.
.PP
By default, all database source files are located in the products
subdirectory of the catalog directory. The main products database is
in the products/products.txt file in the supplied demo catalog.
.PP
\&\fBNote: \fRIf you are using one of the internal database methods, any
changes made to the \s-1ASCII\s0 source file will be reflected in the
database in the next user session. If the product database contains
less than a thousand records, updates will be instantaneous. If the
product database is larger, updates will take longer. Use the
NoImport reference tag to stop auto updating.
.PP
In the following configuration directive:
.PP
.Vb 1
\& Database products products.txt TAB
.Ve
the products table will obtain its source information from the file
products.txt. What is done with it depends on the type of
underlying database being used. The different types and their behavior
are described below:
.Ip "\s-1GDBM\s0" 4
.IX Item "GDBM"
The database source file is checked to see if it is newer than the
actual database file, products.gdbm. If it is, the database table
is re-imported from the file.
.Sp
This behavior can be changed in a few ways. If files should not be
imported unless the .gdbm file disappears, set the NoImport
directive:
.Sp
.Vb 1
\& NoImport products
.Ve
If the database source file is only to be imported at catalog start-up
time, use the \s-1IMPORT_ONCE\s0 modifier:
.Sp
.Vb 1
\& Database products IMPORT_ONCE 1
.Ve
\&\s-1GDBM\s0 is the default database type if the GDBM_File Perl module is
installed (as it is on \s-1LINUX\s0).
.Ip "DB_File" 4
.IX Item "DB_File"
The database source file is checked to see if it is newer than the
actual database file, products.db. If it is, the database table is
re-imported from the file. You can change this behavior in the same
way as GDBM_File, described above.
.Sp
DB_File is the default database type if the GDBM_File Perl module is
not installed. This is common on FreeBSD. To specify DB_File as your
database type, set it in catalog.cfg with a Database directive:
.Sp
.Vb 1
\& Database products DB_FILE 1
.Ve
.Ip "\s-1DBI/SQL\s0" 4
.IX Item "DBI/SQL"
If a file named products.sql is in the same directory as
products.txt, the database table will not be imported from the
\&\s-1ASCII\s0 source. If there is no products.sql, the following will
occur:
.Sp
\&\s-1DBI/SQL\s0 imports will only happen at catalog configuration time.
.RS 4
.Ip "" 8
Interchange will connect to the \s-1SQL\s0 database using the specified \s-1DSN\s0.
(\s-1DBI\s0 parameter meaning \*(L"Database Source Name\*(R".)
.Sp
The table will be dropped with \*(L"\s-1DROP\s0 \s-1TABLE\s0 products;\*(R". This will occur
without warning. \s-1NOTE:\s0 This can be prevented in several ways. See
NoImport External or the \s-1SQL\s0 documentation for more information.
.Sp
The table will be created. If there are \s-1COLUMN_DEF\s0 specifications in
catalog.cfg, they will be used. Otherwise, the key (first field in
the text file by default) will be created with a char(16) type and
all other fields will be created as char(128). The table creation
statement will be written to the error.log file.
.Sp
The text source file will be imported into the \s-1SQL\s0 database.
Interchange will place the data in the columns. Data typing must be
user-configured. This means that if \*(L"none\*(R" is placed in a field, and
it is defined as a numeric type, the database import will not succeed.
And if it does not succeed, the catalog will not become active.
.RE
.RS 4
.RE
.Ip "In-Memory" 4
.IX Item "In-Memory"
Every time the catalog is configured, the products.txt file is
imported into memory and forms the database. Otherwise, the database
is not changed. The in-memory database is the default database if
there is no GDBM_File or DB_File Perl module installed; specify it
with:
.Sp
.Vb 1
\& Database products MEMORY 1
.Ve
.Sh "Interchange Database Conventions"
.IX Subsection "Interchange Database Conventions"
This section describes naming and file usage conventions used with
Interchange.
.PP
\&\fBNote: \fRThroughout the documentation, the following terms and their
definitions are used interchangeably:
.Ip "key, code" 4
.IX Item "key, code"
A reference to the database key. In Interchange, this is usually the
product code or \s-1SKU\s0, which is the part number for the product. Other
key values may be used to generate relationships to other database
tables.
.Sp
It is recommended that the key be the first column of the \s-1ASCII\s0 source
file, since Interchange's import, export, and search facilities rely
on this practice.
.Ip "field, column" 4
.IX Item "field, column"
The vertical row of a database. One of the columns is always the key
and it is usually the first one.
.Ip "table, database" 4
.IX Item "table, database"
A table in the database. Because Interchange has evolved from a
single-table database to an access method for an unlimited number of
tables (and databases, for that matter), a table will occasionally be
referred to as a database. The only time the term database refers to
something different is when describing the concept as it relates to
\&\s-1SQL\s0, where a database contains a series of tables. While Interchange
cannot create \s-1SQL\s0 databases, it can drop and create tables with that
database if given the proper permissions.
.PP
If necessary, Interchange can read the data to be placed in tables
from a standard ASCII-delimited file. All of the \s-1ASCII\s0 source files
are kept in the products directory, which is normally in the catalog
directory (where catalog.cfg is located). The \s-1ASCII\s0 files can have ^M
(carriage return) characters, but must have a new line character at
the end of the line to work. \s-1NOTE:\s0 Mac users uploading files must use
\&\s-1ASCII\s0 mode, not binary mode.
.PP
Interchange's default \s-1ASCII\s0 delimiter is \s-1TAB\s0.
.PP
\&\fBNote: \fRThe items must be separated by a single delimiter. The items
in this document are lined up for reading convenience.
.Ip "\s-1TAB\s0" 4
.IX Item "TAB"
Fields are separated by ^I characters. No whitespace is allowable
at the beginning of the line.
.Sp
.Vb 1
\& code description price SH543 Men's fine cotton shirt 14.95 shirts.jpg
.Ve
image
.Ip "\s-1PIPE\s0" 4
.IX Item "PIPE"
Fields are separated by pipe | characters. No whitespace is
allowable at the beginning of the line.
.Sp
.Vb 1
\& code|description|price SH543|Men's fine cotton shirt|14.95|shirts.jpg
.Ve
image
.Ip "\s-1CSV\s0" 4
.IX Item "CSV"
Fields are enclosed in quotes, separated by commas. No whitespace
should be at the beginning of the line.
.Sp
.Vb 1
\& "code","description","price"," "SH543","Men's fine cotton shirt","14.95","shirts.jpg"
.Ve
mage"
.PP
\&\fBNote: \fRUsing the default \s-1TAB\s0 delimiter is recommended if you plan on
searching the \s-1ASCII\s0 source file of the database. \s-1PIPE\s0 works fairly
well, but \s-1CSV\s0 delimiter schemes might cause problems with searching.
.PP
\&\fB\s-1IMPORTANT\s0 \s-1NOTE:\s0 \fRField names are usually case-sensitive. Use
consistency when naming or you might encounter problems. All lower or
all upper case names are recommended.
.Ip "" 4
Interchange uses one mandatory database, which is referred to as the
products database. In the supplied demo catalog, it is called products
and the \s-1ASCII\s0 source is kept in the file products.txt in the
products directory. This is also the default file for searching with
the \s-1THE\s0 \s-1SEARCH\s0 \s-1ENGINE\s0.
.Sp
Interchange also has a two of standard, but optional, databases that
are in fixed formats:
.RS 4
.Ip "shipping.asc" 8
.IX Item "shipping.asc"
The database of shipping options that is accessed if the
CustomShipping directive is in use. This is a fixed-format
database, and must be created as specified. For more information, see
the Shipping \s-1ITL\s0 tag in the \fIInterchange Tag Reference Guide\fR.
.Ip "salestax.asc" 8
.IX Item "salestax.asc"
The database of sales tax information if the [salestax] tag is to
be used. A default is supplied. \s-1NOTE:\s0 Caution, these things change!
This is a fixed-format database, and must be created as specified. See
Sales Tax.
.RE
.RS 4
.Sp
These are never stored in \s-1SQL\s0 or \s-1DBM\s0.
.RE
.Sh "The Product Database"
.IX Subsection "The Product Database"
Each product being sold should be given a product code, usually
referred to as \s-1SKU\s0, a short code that identifies the product on the
ordering page and in the catalog. The products.txt file is a
ASCII-delimited list of all the product codes, along with an arbitrary
number of fields which must contain at least the fields description
and price (or however the PriceField and DescriptionField
directives have been set). Any additional information needed in the
catalog can be placed in any arbitrary field. See Interchange Database
Capability for details on the format.
.PP
Field names can be case-sensitive depending on the underlying database
type. Unless there are fields with the names \*(L"description\*(R" and \*(L"price\*(R"
field, set the PriceField and DescriptionField directives to use
the [item-price] and [item-description] tags.
.PP
The product code, or \s-1SKU\s0, must be the first field in the line, and
must be unique. Product codes can contain the characters \fBA-Za-z0\-9\fR,
along with hyphen (\-), underscore (_), pound sign/hash mark
(#), slash (/), and period (.). Note that slash (/) will
interfere with on-the-fly page references. Avoid if at all possible.
.PP
The words should be separated by one of the approved delimiting
schemes (\s-1TAB\s0, \s-1PIPE\s0, or \s-1CSV\s0), and are case-sensitive in some cases. If
the case of the \*(L"description\*(R" or \*(L"price\*(R" fields have been modified,
the PriceField and DescriptionField directives must be
appropriately set.
.PP
\&\fBNote: \fR\s-1CSV\s0 is not recommended as the scheme for the products
database. It is much slower than \s-1TAB-\s0 or PIPE-delimited, and
dramatically reduces search engine functionality. No field-specific
searches are possible. Using \s-1CSV\s0 for any small database that will not
be searched is fine.
.PP
\&\fB\s-1IMPORTANT\s0 \s-1NOTE:\s0 \fRThe field names must be on the first line of the
products.txt file. These field names must match exactly the field
names of the [item-field] tags in the catalog pages, or the
Interchange server will not access them properly. Field names can
contain the characters A-Za-z0\-9 and underscore (_).
.PP
More than one database may be used as a products database. If the
catalog directive, ProductFiles, is set to a space-separated list of
valid Interchange database identifiers, those databases will be
searched (in the order specified) for any items that are ordered, or
for product information (as in the [price code] and [field code]
tags).
.PP
When the database table source file (i.e., products.txt) changes after
import or edit, a \s-1DBM\s0 database is re-built upon the next user access.
No restart of the server is necessary.
.PP
If changing the database on-the-fly, it is recommended that the file
be locked while it is being modified. Interchange's supplied import
routines do this.
.Sh "Multiple Database Tables"
.IX Subsection "Multiple Database Tables"
Interchange can manage an unlimited number of arbitrary database
tables. They use the \s-1TAB\s0 delimiter by default, but several flexible
delimiter schemes are available. These are defined by default:
.PP
.Vb 16
\& Type 1 DEFAULT - uses default TAB delimiter
\& Type 2 LINE
\& Each field on its own line, a blank line
\& separates the record. Watch those carriage
\& returns! Also has a special format when CONTINUE
\& is set to be NOTES.
\& Type 3 %%
\& Fields separated by a \en%%\en combination, records by
\& \en%%%\en (where \en is a newline). Watch those carriage
\& returns!
\& Type 4 CSV
\& Type 5 PIPE
\& Type 6 TAB
\& Type 7 reserved
\& Type 8 SQL
\& Type 9 LDAP
.Ve
The databases are specified in Database directives, as:
.PP
.Vb 1
\& Database arbitrary arbitrary.csv CSV
.Ve
This specifies a Type 4 database, the \s-1ASCII\s0 version of which is
located in the file arbitrary.csv, and the identifier it will be
accessed under in Interchange is \*(L"arbitrary.\*(R" The \s-1DBM\s0 file, if any,
will be created in the same directory if the \s-1ASCII\s0 file is newer, or
if the \s-1DBM\s0 file does not exist. The files will be created as
arbitrary.db or arbitrary.gdbm, depending on \s-1DBM\s0 type.
.PP
The identifier is case sensitive, and can only contain characters
in the class [A-Za-z0\-9_]. Fields are accessed with the [item_data
identifier field] or [data identifier field key] elements. \s-1NOTE:\s0
Use of lower-case letters is strongly recommended.
.PP
If one of the first six types is specified, the database will
automatically be built in the default Interchange \s-1DB\s0 style. The type
can be specified with \s-1DB_FILE\s0, \s-1GDBM\s0, or \s-1MEMORY\s0, if the type varies
from that default. They will coexist with an unlimited number of \s-1DBI\s0
databases of different types.
.PP
In addition to the database, the session files will be kept in the
default format, and are affected by the following actions.
.PP
The order of preference is:
.Ip "\s-1GDBM\s0" 4
.IX Item "GDBM"
This uses the Perl GDBM_File module to build a \s-1GDBM\s0 database. The
following command will indicate if \s-1GDBM\s0 is in Perl:
.Sp
.Vb 1
\& perl -e 'require GDBM_File and print "I have GDBM.\en"'
.Ve
Installing GDBM_File requires rebuilding Perl after obtaining the \s-1GNU\s0
\&\s-1GDBM\s0 package, and is beyond the scope of this document. \s-1LINUX\s0 will
typically have this by default; most other operating systems will need
to specifically build in this capability.
.Ip "DB_File (Berkeley \s-1DB\s0)" 4
.IX Item "DB_File (Berkeley DB)"
This uses the DB_File module to build a Berkeley \s-1DB\s0 (hash)
database. The following command will indicate if DB_File is in Perl:
.Sp
.Vb 1
\& perl -e 'require DB_File and print "I have Berkeley DB.\en"'
.Ve
Installing DB_File requires rebuilding Perl after obtaining the
Berkeley \s-1DB\s0 package, and is beyond the scope of this document. \s-1BSDI\s0,
FreeBSD, and \s-1LINUX\s0 will typically have it by default; most other
operating systems will need to specifically build this in.
.Sp
If using DB_File, even though GDBM_File is in Perl, set the
environment variable \s-1MINIVEND_DBFILE\s0 to a true (non-zero, non-blank)
value:
.Sp
.Vb 1
\& # csh o setenv MINIVEND_DBFILE 1
.Ve
.Vb 2
\& # sh, bash, or ksh
\& MINIVEND_DBFILE=1 ; export MINIVEND_DBFILE
.Ve
.Vb 1
\& tcsh
.Ve
Then, re-start the server.
.Sp
Or, to set a particular table to use Berkeley \s-1DB\s0, the \s-1DB_FILE\s0 class in
catalog.cfg can be specified:
.Sp
.Vb 1
\& Database arbitrary DB_FILE 1
.Ve
.Ip "In-memory" 4
.IX Item "In-memory"
This uses Perl hashes to store the data directly in memory. Every time
the Interchange server is restarted, it will re-import all in-memory
databases for every catalog.
.Sp
If this is used, despite the presence of GDBM_File or DB_File,
set the environment variable \s-1MINIVEND_NODBM\s0 as above or specify the
memory type in the Database directive:
.Sp
.Vb 1
\& Database arbitrary MEMORY 1
.Ve
.PP
\&\fBNote: \fRThe use of memory databases is not recommended.
.Sh "Character Usage Restrictions"
.IX Subsection "Character Usage Restrictions"
To review, database identifiers, field names, and product codes
(database keys) are restricted in the characters they may use. The
following table shows the restrictions:
.PP
.Vb 6
\& Legal characters
\& ---------------------
\& Database identifiers A-Z a-z 0-9 _
\& Field names A-Z a-z 0-9 _
\& Database keys (product code/SKU) A-Z a-z 0-9 _ # - . /
\& Database values Any (subject to field/record delimiter)
.Ve
Some \s-1SQL\s0 databases have reserved words which cannot be used as field
names; Interchange databases do not have this restriction.
.PP
For easy \s-1HTML\s0 compatibility, it is not recommended that a / be used in
a part number if using the flypage capability. It can still be called
[page href=flypage arg=\*(L"S/KU\*(R"].
.Sh "Database Attributes"
.IX Subsection "Database Attributes"
Especially in \s-1SQL\s0 databases, there are certain functions that can be
set with additional database attributes. For text import, the \s-1CONTINUE\s0
extended database import attribute allows additional control over the
format of imported text.
.PP
\&\fBNote: \fR\s-1CONTINUE\s0 applies to all types except \s-1CSV\s0. (Do not use \s-1NOTES\s0
unless using type \s-1LINE\s0.)
.Ip "\s-1CONTINUE\s0" 4
.IX Item "CONTINUE"
One of \s-1UNIX\s0, \s-1DITTO\s0, \s-1LINE\s0, \s-1NONE\s0, or \s-1NOTES\s0. The default, \s-1NONE\s0, is to
simply split the line/record according to the delimiter, with no
possible spanning of records. Setting \s-1CONTINUE\s0 to \s-1UNIX\s0 appends the
next line to the current when it encounters a backslash (\e) at the
end of a record, just like many \s-1UNIX\s0 commands and shells.
.Sp
\&\s-1DITTO\s0 is invoked when the key field is blank. It adds the contents of
following fields 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.
.Sp
Example in catalog.cfg:
.Sp
.Vb 1
\& Database products products.tx Database products CONTINUE DITTO
.Ve
.Vb 1
\& TAB
.Ve
Products.asc file:
.Sp
.Vb 2
\& code price descr 00-0011 500000 The Mona Lisa, one of the worlds great masterpieces.
\& Now at a reduced price!
.Ve
ption
.Sp
The description for product 00\-0011 will contain the contents of the
description field on both lines, separated by a new line.
.PP
\&\fBNote: \fRFields are separated by tabs, formatted for reading
convenience.
.Ip "" 4
This will work for multiple fields in the same record. If the field
contains any non-empty value, it will be appended.
.Sp
\&\s-1LINE\s0 is a special setting so a multi-line field can be used. Normally,
when using the \s-1LINE\s0 type, there is only data on one line separated by
one blank line. When using \s-1CONTINUE\s0 \s-1LINE\s0, there may be some number of
fields which are each on a line, while the last one spans multiple
lines up until the first blank line.
.Sp
Example in catalog.cfg:
.Sp
.Vb 1
\& Database products products.txt Database products CONTINUE LINE
.Ve
.Vb 1
\& LINE
.Ve
Products.asc file:
.Sp
.Vb 2
\& price
\& description
.Ve
.Vb 4
\& 00-0011
\& 500000
\& The Mona Lisa, one of the worlds great masterpieces.
\& Now at a reduced price!
.Ve
.Vb 3
\& 00-0011a
\& 1000
\& A special frame for the Mona Lisa.
.Ve
.Vb 1
\& code
.Ve
\&\s-1NOTES\s0 reads a Lotus Notes \*(L"structured text\*(R" 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. There is optional whitespace
after the colon.
.Sp
Records are separated by a settable delimiting character which goes on
a line by itself, much like a \*(L"here document.\*(R" By default, it is a
form feed (^L) character. The final field begins at the first blank
line and continues to the end of the record. This final field is named
notes_field, unless set as mentioned below.
.Sp
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 \s-1TAB\s0
are used as the record delimiter.
.Sp
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 \fBfirst\fR blank line.
.Sp
The following example sets the delimiter to a tilde (~) and renames
the notes_field to description.
.Sp
Example in catalog.cfg:
.Sp
.Vb 1
\& Database products products.txt Database products CONTINUE NOTES
.Ve
.Vb 1
\& LINE
.Ve
Products.asc file:
.Sp
.Vb 6
\& title
\& price
\& image
\& description ~
\& size
\& color
.Ve
.Vb 4
\& title: Mona Lisa
\& price: 500000
\& code: 00-0011
\& image: 00-0011.jpg
.Ve
.Vb 8
\& The Mona Lisa, one of the worlds great masterpieces.
\& Now at a reduced price!
\& ~
\& title: The Art Store T-Shirt
\& code: 99-102
\& size: Medium, Large*, XL=Extra Large
\& color: Green, Blue, Red, White*, Black
\& price: 2000
.Ve
.Vb 2
\& Extra large 1.00 extra.
\& ~
.Ve
.Vb 1
\& code
.Ve
.Ip "\s-1EXCEL\s0" 4
.IX Item "EXCEL"
Microsoft Excel is a widely-used tool to maintain Interchange
databases, but has several problems with its standard TAB-delimited
export, like 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.
.Sp
Set the \s-1EXCEL\s0 attribute to 1 to fix these problems on import:
.Sp
.Vb 1
\& Database products EXCEL 1
.Ve
This is normally used only with TAB-delimited files.
.Ip "\s-1LARGE\s0" 4
.IX Item "LARGE"
Interchange databases containing many records can result in a
noticeable slowdown when displayed by the \s-1UI\s0.
.Sp
Set the \s-1LARGE\s0 attribute to 1 to avoid this problem:
.Sp
.Vb 1
\& Database transactions LARGE 1
.Ve
.Ip "" 4
In this case the \s-1UI\s0 supplies only input boxes to search records in the
database instead of drawing all the records from the database, sorting
them and creating more lists.
.Sh "Dictionary Indexing With \s-1INDEX\s0"
.IX Subsection "Dictionary Indexing With INDEX"
Interchange will automatically build index files for a fast binary
search of an individual field. This type of search is useful for
looking up the author of a book based on the beginning of their last
name, a book title based on its beginning, or other similar
situations.
.PP
Such a search requires a dictionary ordered index with the field to be
searched contained in the first field and the database key (product
code) in the second field. If the \s-1INDEX\s0 field modifier is
specified, Interchange will build the index upon database import:
.PP
.Vb 2
\& Database products products.txt TAB
\& Database products INDEX title
.Ve
If the title field is the fourth column in the products database
table, a file products.txt.4 will be built, containing two
tab-separated fields something like:
.PP
.Vb 4
\& American Gothic 19-202
\& Mona Lisa 00-0011
\& Sunflowers 00-342
\& The Starry Night 00-343
.Ve
Options can be appended to the field name after a colon (:). The most
useful will be f, which does a case-insensitive sort. The
mv_dict_fold option must be added to the search in this case.
.PP
Another option is c, which stands for \*(L"comma index.\*(R" To index on
comma-separated sub-fields within a field, use the :c option:
.PP
.Vb 2
\& Database products products.txt TAB
\& Database products INDEX category:c
.Ve
This can get slow for larger databases and fields. Interchange will
split the field on a comma (stripping surrounding whitespace) and make
index entries for each one. This allows multiple categories in one
field while retaining the fast category search mechanism. It might
also be useful for a keywords field.
.PP
The fast binary search is described in greater detail in \s-1THE\s0 \s-1SEARCH\s0
\&\s-1ENGINE\s0 below.
.Sh "\s-1MEMORY\s0 for Memory-Only Databases"
.IX Subsection "MEMORY for Memory-Only Databases"
Interchange's memory-based databases are the fastest possible way to
organize and store frequently used data. To force a database to be
built in memory instead of \s-1DBM\s0, use the \s-1MEMORY\s0 modifier:
.PP
.Vb 2
\& Database country country.asc TAB
\& Database country MEMORY 1
.Ve
Obviously, large tables will use a great deal of memory, and the data
will need to be re-imported from the \s-1ASCII\s0 source file at every
catalog reconfiguration or Interchange restart. The big advantage of
using \s-1MEMORY\s0 is that the database remains open at all times and does
not need to be reinitialized at every connect. Use it for smaller
tables that will be frequently accessed.
.PP
Memory tables are read only \*(-- the \s-1MEMORY\s0 modifier forces \s-1IMPORT_ONCE\s0.
.Sh "\s-1IMPORT_ONCE\s0"
.IX Subsection "IMPORT_ONCE"
The \s-1IMPORT_ONCE\s0 modifier tells Interchange not to re-import the
database from the \s-1ASCII\s0 file every time it changes. Normally,
Interchange does a comparison of the database file modification time
with the \s-1ASCII\s0 source every time it is accessed, and if the \s-1ASCII\s0
source is newer it will re-import the file.
.PP
\&\s-1IMPORT_ONCE\s0 tells it only to import on a server restart or catalog
reconfiguration:
.PP
.Vb 2
\& Database products products.txt TAB
\& Database products IMPORT_ONCE 1
.Ve
\&\s-1SQL\s0 databases don't normally need this. They will only be imported
once in normal operation. Also see NoImport for a way to guarantee
that the table will never be imported.
.PP
\&\s-1IMPORT_ONCE\s0 is always in effect for \s-1MEMORY\s0 databases. A catalog
reconfiguration is required to force a change.
.Sh "\s-1MIRROR\s0"
.IX Subsection "MIRROR"
Additionally, you can have two tables, the regular table and the
memory table by adding to the definition files:
.PP
.Vb 3
\& Database country_memory country_memory.txt TAB
\& Database country_memory MIRROR country
\& Database country_memory MEMORY 1
.Ve
.Sh "\s-1SQL/DBI\s0 parameters"
.IX Subsection "SQL/DBI parameters"
\&\s-1AUTO_SEQUENCE\s0
.PP
Tells Interchange to use a \s-1SQL\s0 sequence to number new database items
inserted into the database.
.PP
If you have Interchange create the table, then you need to do:
.PP
.Vb 2
\& Database foo foo.txt dbi:mysql:test
\& Database foo AUTO_SEQUENCE foo_seq
.Ve
Then on MySQL, Pg, or Oracle, Interchange will create an integer key
type and a sequence (or \s-1AUTO_INCREMENT\s0 in MySQL) to maintain the
count.
.PP
\&\s-1AUTO_SEQUENCE_MAXVAL\s0
.PP
Sets the \s-1MAXVAL\s0 to have in an \s-1AUTO_SEQUENCE\s0 counter:
.PP
.Vb 1
\& Database foo AUTO_SEQUENCE_MAXVAL 1000000
.Ve
\&\s-1AUTO_SEQUENCE_MINVAL\s0
.PP
Sets the \s-1MINVAL\s0 to have in an \s-1AUTO_SEQUENCE\s0 counter:
.PP
.Vb 1
\& Database foo AUTO_SEQUENCE_MINVAL 10
.Ve
\&\s-1AUTO_SEQUENCE_START\s0
.PP
Sets the starting value for an \s-1AUTO_SEQUENCE\s0 counter:
.PP
.Vb 1
\& Database foo AUTO_SEQUENCE_START 1000
.Ve
\&\s-1COMPOSITE_KEY\s0
.PP
If you are using a \s-1DBI\s0 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:
.PP
.Vb 4
\& 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)"
.Ve
If you want to create a custom index for the table, do so. If you
don't specify a \s-1POSTCREATE\s0 or \s-1INDEX\s0 parameter for the table,
Interchange will create a unique index with all composite key elements
at table creation time.
.PP
\&\s-1DSN\s0
.PP
The data source name (\s-1DSN\s0) for the database. It is beyond the scope of
this document to describe this in detail.
.PP
Normally this is set as the type in the initial Database
configuration line, i.e.
.PP
.Vb 1
\& Database foo foo.txt dbi:mysql:foobase
.Ve
This has the same effect:
.PP
.Vb 2
\& Database foo foo.txt SQL
\& Database foo DSN dbi:mysql:foobase
.Ve
Some other examples of \s-1DSN\s0 specs:
.PP
.Vb 3
\& 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
.Ve
\&\s-1HAS_TRANSACTIONS\s0
.PP
Informs Interchange that the \s-1SQL\s0 database in use has \fIcommit()\fR and
\&\fIrollback()\fR for transactions. For PostgreSQL and Oracle this should be
set properly to 1 \*(-- for MySQL and other databases you have to set it.
.PP
\&\s-1HAS_LIMIT\s0
.PP
Informs Interchange that the \s-1SQL\s0 database in use has as the \s-1LIMIT\s0
extension to \s-1SQL\s0 to limit return from queries. Should be set properly
by default for MySQL, PostgreSQL, and Oracle.
.PP
\&\s-1POSTCREATE\s0
.PP
One or more \s-1SQL\s0 statements that should be performed after Interchange
creates a table.
.PP
.Vb 2
\& Database foo POSTCREATE "create unique index foo_idx on foo(key1,key2)"
\& Database foo POSTCREATE "create index mulkey_idx on foo(mulkey)"
.Ve
\&\s-1PRECREATE\s0
.PP
One or more \s-1SQL\s0 statements that should be performed before Interchange
creates a table.
.PP
.Vb 2
\& Database foo POSTCREATE "drop table foobackup"
\& Database foo POSTCREATE "alter table foo rename to foobackup"
.Ve
\&\s-1REAL_NAME\s0
.PP
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 \s-1SQL\s0 statements:
.PP
.Vb 1
\& Database products REAL_NAME products_a
.Ve
Of course if you have \s-1SQL\s0 queries that are passed verbatim to
Interchange (i.e. the [query ...] tag) you must use the \s-1REAL_NAME\s0 in
those.
.Sh "Importing in a Page"
.IX Subsection "Importing in a Page"
To add a data record to a database as a result of an order or other
operation, use Interchange's [import ...] tag.
.Ip "[import table type*] \s-1RECORD\s0 [/import]" 4
.IX Item "[import table type*] RECORD [/import]"
Named parameters:
.Sp
.Vb 4
\& [import table=tabl file=filename*
\& type=(TAB|PIPE|CSV|%%|LINE)*
\& continue=(NOTES|UNIX|DITTO)*
\& separator=c*]
.Ve
_name
.PP
Import one or more records into a database. The type is any of the
valid Interchange delimiter types, with the default being \s-1TAB\s0. The
table must already be a defined Interchange database table. It cannot
be created on-the-fly. If on-the-fly functionality is need, it is time
to use \s-1SQL\s0.
.PP
The import type selected need not match the type the database was
specified. Different delimiters may be used.
.PP
The type of \s-1LINE\s0 and continue setting of \s-1NOTES\s0 is
particularly useful, for it allows fields to be named and not have to
be in any particular order of appearance in the database. The
following two imports are identical in effect:
.PP
.Vb 5
\& [import table=orders]
\& code: [value mv_order_number]
\& shipping_mode: [shipping-description]
\& status: pending
\& [/import]
.Ve
.Vb 5
\& [import table=orders]
\& shipping_mode: [shipping-description]
\& status: pending
\& code: [value mv_order_number]
\& [/import]
.Ve
The code or key must always be present, and is always named
code. If \s-1NOTES\s0 mode is not used, the fields must be imported in
the same order as they appear in the \s-1ASCII\s0 source file.
.PP
The file option overrides the container text and imports directly
from a named file based in the catalog directory. To import from
products.txt, specify file=\*(L"products/products.txt\*(R". If the
NoAbsolute directive is set to Yes in interchange.cfg, only
relative path names will be allowed.
.PP
The [import ....] \s-1TEXT\s0 [/import] region may contain multiple
records. If using \s-1NOTES\s0 mode, a separator must be used, which, by
default, is a form-feed character (^L). See Import Attributes for more
information.
.Sh "Exporting from a Database"
.IX Subsection "Exporting from a Database"
To export an existing database to a file to its text file, suitable
for full-text search by Interchange, use Interchange's \s-1UI\s0 create a
page that contains a [export table=TABLENAME] \s-1ITL\s0 tag (ExportTag).
.Sh "Write Control"
.IX Subsection "Write Control"
Interchange databases can be written in the normal course of events,
either using the [import ...] tag or with a tag like [data
table=table column=field key=code value=new-value]. To control
writing of a global database, or to a certain catalog within a series
of subcatalogs, or make one read only, see the following:
.PP
To enable write control:
.PP
.Vb 1
\& Database products WRITE_CONTROL 1
.Ve
Once this is done, to make a database read only, which won't allow
writing even if [tag flag write]products[/tag] is specified:
.PP
.Vb 1
\& Database products READ_ONLY 1
.Ve
To have control with [tag flag write]products[/tag]:
.PP
.Vb 1
\& Database products WRITE_TAGGED 1
.Ve
To limit write to certain catalogs, set:
.PP
.Vb 1
\& Database products WRITE_CATALOG simple=0, sample=1
.Ve
The \*(L"simple\*(R" catalog will not be able to write, while \*(L"sample\*(R" will if
[tag flag write]products[/tag] is enabled. If a database is to
always be writable, without having to specify [tag flag write] ...
[/tag], then define:
.PP
.Vb 1
\& Database products WRITE_ALWAYS 1
.Ve
The default behavior of \s-1SQL\s0 databases is equivalent to \s-1WRITE_ALWAYS\s0,
while the default for GDBM_File, DB_File, and Memory databases is
equivalent to:
.PP
.Vb 2
\& Database products WRITE_CONTROL 1
\& Database products WRITE_TAGGED 1
.Ve
.Sh "Global Databases"
.IX Subsection "Global Databases"
If a database is to be available to all catalogs on the Interchange
server, it may be defined in interchange.cfg. Any catalog running
under that server will be able to use it. It is writable by any
catalog unless \s-1WRITE_CONTROL\s0 is used.
.SH "SQL Support"
.IX Header "SQL Support"
Interchange can use any of a number of \s-1SQL\s0 databases through the
powerful Perl \s-1DBI/DBD\s0 access methods. This allows transparent access
to any database engine that is supported by a \s-1DBD\s0 module. The current
list includes mSQL, MySQL, Solid, PostgreSQL, Oracle, Sybase,
Informix, Ingres, Dbase, \s-1DB2\s0, Fulcrum, and others. Any \s-1ODBC\s0 (with
appropriate driver) should also be supported.
.PP
No \s-1SQL\s0 database is included with Interchange, but there are a number
widely available on the Internet. Most commonly used with Interchange
are PostgreSQL, MySQL, and Oracle. It is beyond the scope of this
document to describe \s-1SQL\s0 or \s-1DBI/DBD\s0. Sufficient familiarity is
assumed.
.PP
In most cases, Interchange cannot perform administrative functions,
like creating a database or setting access permissions. This must be
done with the tools provided with a \s-1SQL\s0 distribution. But, if given a
blank database and the permission to read and write it, Interchange
can import \s-1ASCII\s0 files and bootstrap from there.
.Sh "\s-1SQL\s0 Support via \s-1DBI\s0"
.IX Subsection "SQL Support via DBI"
The configuration of the \s-1DBI\s0 database is accomplished by setting
attributes in additional Database directives after the initial
defining line as described above. For example, the following defines
the database \fBarbitrary\fR as a \s-1DBI\s0 database, sets the data source
(\s-1DSN\s0) to an appropriate value for an mSQL database named minivend
on port 1114 of the local machine:
.PP
.Vb 2
\& Database arbitrary arbitrary.asc SQL
\& Database arbitrary DSN dbi:mSQL:minivend:localhost:1114
.Ve
As a shorthand method, include the \s-1DSN\s0 as the type:
.PP
.Vb 1
\& Database arbitrary arbitrary.asc dbi:mSQL:minivend:localhost:1114
.Ve
Supported configuration attributes include (but are not limited to):
.Ip "\s-1DSN\s0" 4
.IX Item "DSN"
A specification of the \s-1DBI\s0 driver and its data source. To use the
\&\s-1DBD:\s0:mSQL driver for \s-1DBI\s0, use:
.Sp
.Vb 1
\& dbi:mSQL:minivend:othermachine.my.com:1112
.Ve
where mSQL selects the driver (case \s-1IS\s0 important), minivend selects
the database, othermachine.my.com selects the host, and 1112 is the
port. On many systems, dbi:mSQL:minivend will work fine. Of course,
the minivend database must already exist.
.Sp
This is the same as the \s-1DBI_DSN\s0 environment variable, if the \s-1DSN\s0
parameter is not set. Then, the value of \s-1DBI_DSN\s0 will be used to try
and find the proper database to connect to.
.Ip "\s-1USER\s0" 4
.IX Item "USER"
The user name used to log into the database. It is the same as the
environment variable \fB\s-1DBI_USER\s0\fR. If a user name is not needed, just
don't set the \s-1USER\s0 directive.
.Ip "\s-1PASS\s0" 4
.IX Item "PASS"
The password used to log into the database. It is the same as the
environment variable \fB\s-1DBI_PASS\s0\fR. If a password is not needed, just
don't set the \s-1PASS\s0 directive.
.Ip "\s-1COLUMN_DEF\s0" 4
.IX Item "COLUMN_DEF"
A comma-separated set of lines in the form NAME=\fITYPE\fR\|(N), where \s-1NAME\s0 is
the name of the field/column, \s-1TYPE\s0 is the \s-1SQL\s0 data type reference, and
N is the length (if needed). Most Interchange fields should be the
fixed-length character type, something like char(128). In fact, this
is the default if a type is not chosen for a column. There can be as
many lines as needed. This is not a \s-1DBI\s0 parameter, it is specific to
Interchange.
.Ip "\s-1NAME\s0" 4
.IX Item "NAME"
A space-separated field of column names for a table. Normally not
used. Interchange should resolve the column names properly upon query.
Set this if a catalog errors out with \*(L"dbi: can't find field names\*(R" or
the like. The first field should always be \fBcode\fR. This is not a \s-1DBI\s0
parameter, it is specific to Interchange. All columns must be listed,
in order of their position in the table.
.Ip "\s-1NUMERIC\s0" 4
.IX Item "NUMERIC"
Tells Interchange not to quote values for this field. It allows
numeric data types for \s-1SQL\s0 databases. It is placed as a
comma-separated field of column names for a table, in no particular
order. This should be defined if a numeric value is used because many
\&\s-1DBD\s0 drivers do not yet support type queries.
.Ip "\s-1UPPERCASE\s0" 4
.IX Item "UPPERCASE"
Tells Interchange to force field names to \s-1UPPER\s0 case for row accesses
using the [item-data ...], [loop-data ...], [item-field ...,
etc. Typically used for Oracle and some other \s-1SQL\s0 implementations.
.Ip "\s-1DELIMITER\s0" 4
.IX Item "DELIMITER"
A Interchange delimiter type, either \s-1TAB\s0,CSV,PIPE,%%,LINE or the
corresponding numeric type. The default for \s-1SQL\s0 databases is \s-1TAB\s0. Use
\&\s-1DELIMITER\s0 if another type will be used to import. This is not a \s-1DBI\s0
parameter. It is specific to Interchange.
.Ip "\s-1KEY\s0" 4
.IX Item "KEY"
The keying default of code in the first column of the database can
be changed with the \s-1KEY\s0 directive. Don't use this unless 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 database.
.Ip "ChopBlanks, LongReadLen, LongTruncOK, RaiseError, etc." 4
.IX Item "ChopBlanks, LongReadLen, LongTruncOK, RaiseError, etc."
Sets the corresponding \s-1DBI\s0 attribute. Of particular interest is
ChopBlanks, which should be set on drivers which by default return
space-padded fixed-length character fields (Solid is an example).
.Sp
The supported list as of this release of Interchange is:
.Sp
.Vb 6
\& Chop CompatMode
\& LongReadLen
\& LongTruncOk
\& PrintError
\& RaiseError
\& Warn
.Ve
lanks
.Sp
Issue the shell command perldoc \s-1DBI\s0 for more information.
.PP
Here is an example of a completely set up \s-1DBI\s0 database on MySQL, using
a comma-separated value input, setting the \s-1DBI\s0 attribute
LongReadLen to retrieve an entire field, and changing some field
definitions from the default char(128):
.PP
.Vb 4
\& Database products products.csv dbi:mysql:minivend
\& Database products USER minivend
\& Database products PASS nevairbe
\& Database products DELIMITER CSV
.Ve
.Vb 2
\& # Set a DBI attribute
\& Database products LongReadLen 128
.Ve
.Vb 10
\& # change some fields from the default field type of char(128)
\& # Only applies if Interchange is importing from ASCII file
\& # If you set a field to a numeric type, you must set the
\& # NUMERIC attribute
\& Database products COLUMN_DEF "code=char(20) NOT NULL primary key"
\& Database products COLUMN_DEF price=float, discount=float
\& Database products COLUMN_DEF author=char(40), title=char(64)
\& Database products COLUMN_DEF nontaxable=char(3)
\& Database products NUMERIC price
\& Database products NUMERIC discount
.Ve
MySQL, \s-1DBI\s0, and \s-1DBD:\s0:mysql must be completely installed and tested,
and have created the database minivend, for this to work.
Permissions are difficult on MySQL. if having trouble, try starting
the MySQL daemon with safe_mysqld \-\-skip-grant-tables & for testing
purposes.
.PP
To change to \s-1ODBC\s0, the only changes required might be:
.PP
.Vb 2
\& Database products DSN dbi:ODBC:TCP/IP localhost 1313
\& Database products ChopBlanks 1
.Ve
The \s-1DSN\s0 setting is specific to a \s-1ODBC\s0 setup. The ChopBlanks setting
takes care of the space-padding in Solid and some other databases. It
is not specific to \s-1ODBC\s0. Once again, \s-1DBI\s0, \s-1DBD::ODBC\s0, and the
appropriate \s-1ODBC\s0 driver must be installed and tested.
.Sh "\s-1SQL\s0 Access Methods"
.IX Subsection "SQL Access Methods"
An Interchange \s-1SQL\s0 database can be accessed with the same tags as any
of the other databases can. Arbitrary \s-1SQL\s0 queries can be passed with
the [query sql=\*(L"\s-1SQL\s0 \s-1STATEMENT\s0\*(R"] \s-1ITL\s0 tag.
.PP
.Vb 27
\& [query
\& ml=10
\& more=1
\& type=list
\& sp="@@MV_PAGE@@"
\& sql=|
\& SELECT sku, description
\& FROM products
\& WHERE somecol
\& BETWEEN '[filter sql][cgi from][/filter]'
\& AND '[filter sql][cgi to][/filter]'
\& AND someothercol = '[filter sql][cgi whatever][/filter]'
\& ORDER BY sku
\& |]
\& [list]
\& sku=[sql-code] - desc=[sql-param description]
\& [/list]
\& [on-match]
\& Something was found
\& [/on-match]
\& [no-match]
\& Nothing was found
\& [/no-match]
\& [more-list]
\& [matches]
\& [/more-list]
\& [/query]
.Ve
Not the filter for [cgi foo] values, which prevent single quotes
(') from destroying the query.
.Sh "Importing from an \s-1ASCII\s0 File"
.IX Subsection "Importing from an ASCII File"
When importing a file for \s-1SQL\s0, Interchange by default uses the first
column of the \s-1ASCII\s0 file as the primary key, with a char(16) type,
and assigns all other columns a char (128) definition. These
definitions can be changed by placing the proper definitions in
\&\s-1COLUMN_DEF\s0 Database directive attribute:
.PP
.Vb 1
\& Database products COLUMN_DEF price=char(20), nontaxable=char(3)
.Ve
This can be set as many times as desired, if it will not fit on the
line.
.PP
.Vb 2
\& Database products COLUMN_DEF price=char(20), nontaxable=char(3)
\& Database products COLUMN_DEF description=char(254)
.Ve
To create an index automatically, append the information when the
value is in quotes:
.PP
.Vb 1
\& Database products COLUMN_DEF "code=char(14) primary key"
.Ve
The field delimiter to use is \s-1TAB\s0 by default, but can be changed with
the Database \s-1DELIMITER\s0 directive:
.PP
.Vb 2
\& Database products products.csv dbi:mSQL:minivend:localhost:1114
\& Database products DELIMITER CSV
.Ve
To create other secondary keys to speed sorts and searches, do so in
the \s-1COLUMN_DEF:\s0
.PP
.Vb 1
\& Database products COLUMN_DEF "author=char(64) secondary key"
.Ve
Or use external database tools. \s-1NOTE:\s0 Not all \s-1SQL\s0 databases use the
same index commands.
.PP
To use an existing \s-1SQL\s0 database instead of importing, set the NoImport
directive in catalog.cfg to include any database identifiers not to be
imported:
.PP
.Vb 1
\& NoImport products inventory
.Ve
\&\fB\s-1WARNING:\s0 \fRIf Interchange has write permission on the products
database, be careful to set the NoImport directive or create the
proper .sql file. If that is not done, and the database source file is
changed, the \s-1SQL\s0 database could be overwritten. In any case, always
back up the database before enabling it for use by Interchange.
.SH "Managing DBM Databases"
.IX Header "Managing DBM Databases"
.Sh "Making the Database"
.IX Subsection "Making the Database"
The \s-1DBM\s0 databases can be built offline with the offline command.
The directory to be used for output is specified either on the command
line with the \-d option, or is taken from the catalog.cfg
directive OfflineDir \*(-- offline in the catalog directory by
default. The directory must exist. The source \s-1ASCII\s0 files should be
present in that directory, and the \s-1DBM\s0 files are created there.
Existing files will be overwritten.
.PP
.Vb 1
\& offline -c catalog [-d offline_dir]
.Ve
Do a perldoc VENDROOT/bin/offline for full documentation.
.Sh "Updating Individual Records"
.IX Subsection "Updating Individual Records"
If it takes a long time to build a very large \s-1DBM\s0 database, consider
using the bin/update script to change just one field in a record,
or to add from a corrections list.
.PP
The database is specified with the \-n option, or is 'products' by
default.
.PP
The following updates the products database price field for item
19\-202 with the new value 25.00:
.PP
.Vb 1
\& update -c catalog -f price 25.00
.Ve
More than one field can be updated on a single command line.
.PP
.Vb 1
\& update -c catalog -f price -f comment 25.00 "That pitchfork couple"
.Ve
The following takes input from file, which must be formatted
exactly like the original database, and adds/corrects any records
contained therein.
.PP
.Vb 1
\& update -c catalog -i file
.Ve
Invoke the command without any arguments for a usage message
describing the options.
.SH "Other Database Capabilities"
.IX Header "Other Database Capabilities"
Interchange has a number of other options that can affect operation of
or operations on a defined database.
.Sh "Search Modification"
.IX Subsection "Search Modification"
Normally, Interchange can search any database and will return all
records that match the search specification. Some attributes affect
this.
.PP
\&\s-1HIDE_FIELD\s0
.PP
When set to a field name, i.e.:
.PP
.Vb 1
\& Database sometable HIDE_FIELD inactive
.Ve
Interchange will not return records that have that field (in the
example, c) set to a true (non-blank, non-zero) value.
.PP
\&\s-1NO_SEARCH\s0
.PP
An indication that the database should not be searchable by default.
Used to determine the default search files for a product searc.
.PP
.Vb 1
\& Database sometable NO_SEARCH 1
.Ve
In the foundation demo, this is used to prevent the options
table from being searched for products.
.Sh "Indexing"
.IX Subsection "Indexing"
You can indicate that a database should be indexed on a field with the
\&\s-1INDEX\s0 modifier:
.PP
.Vb 1
\& Database sometable INDEX category
.Ve
This will create an \s-1ASCII\s0 index on every import, and will also create
an index on the field at \s-1SQL\s0 creation time.
.PP
If you wish to create \s-1SQL\s0 indices at table creation time \fIwithout\fR
creating an \s-1ASCII\s0 index, use the \s-1NO_ASCII_INDEX\s0 parameter:
.PP
.Vb 1
\& Database sometable NO_ASCII_INDEX 1
.Ve
Of course you can create a \s-1SQL\s0 index manually at any time via your \s-1SQL\s0
toolset.
.SH "The Search Engine"
.IX Header "The Search Engine"
Interchange implements a search engine which will search the product
database (or any other file) for items based on customer input. It
uses either forms or link-based searches that are called with the
special page name scan. The search engine uses many special
Interchange tags and variables.
.PP
If the search is implemented in a link or a form, it will always
display formatted results on the results page, an Interchange page
that uses some combination of the [search-region],
[search-list], [more-list], [more], and other Interchange
tags to format and display the results. The search results are usually
a series of product codes/SKUs or other database keys, which are then
iterated over similar to the [item-list].
.PP
\&\fBNote: \fRExamples of search forms and result pages are included in the
demos.
.PP
Two search engine interfaces are provided, and five types of searching
are available. The default is a text-based search of the first
products database source file (i.e., products.txt). A binary search of
a dictionary-ordered file can be specified. An optional Glimpse search
is enabled by placing the command specification for Glimpse in the
catalog.cfg directive Glimpse. There is a range-based search,
used in combination with one of the above. And finally, there is a
fully-coordinated search with grouping.
.PP
The default, a text based search, sequentially scans the lines in the
target file. By default it returns the first field (delineated by the
delimiter for that database) for every line matching the search
specification. This corresponds to the product code, which is then
used to key specific accesses to the database.
.PP
The text-based search is capable of sophisticated field-specific
searches with fully-independent case-sensitivity, substring, and
negated matching.
.Sh "The Search Form"
.IX Subsection "The Search Form"
A number of variables can be set on search forms to determine which
search will be used, what fields in the database it will search, and
what search behavior will be.
.PP
Here is a simple search form:
.PP
.Vb 4
\&
\&
\&
\&
.Ve
When the \*(L"Search\*(R" submit button is pressed (or is pressed),
Interchange will search the products.txt file for the string
entered into the text field mv_searchspec, and return the product
code pertaining to that line.
.PP
The same search for a fixed string, say \*(L"shirt,\*(R" could be performed
with the use of a hot link, using the special scan \s-1URL:\s0
.PP
.Vb 1
\& [page search="se=shirt"]See our shirt collection!
.Ve
The default is to search every field on the line. To match on the
string \*(L"shirt\*(R" in the product database field \*(L"description,\*(R" modify the
search:
.PP
.Vb 1
\&
.Ve
In the hot-linked \s-1URL\s0 search:
.PP
.Vb 4
\& [page search="
\& se=shirt
\& sf=category
\& "]See our shirt collection!
.Ve
To let the user decide on the search parameters, use checkboxes or
radiobox fields to set the fields:
.PP
.Vb 4
\& Search by author
\&
\& Search by title
\&
.Ve
Fields can be stacked. If more than one is checked, all checked fields
will be searched.
.Sh "Glimpse"
.IX Subsection "Glimpse"
To use the Glimpse search, the Glimpse index must be built based on
files in the ProductDir, or wherever the files to be searched will be
located. If the catalog is in /var/lib/interchange/foundation, the
command line to build the index for the products file would be:
.PP
.Vb 2
\& chdir /var/lib/interchange/foundation/products
\& glimpseindex -b -H . products.txt
.Ve
There are several ways to improve search speed for large catalogs. One
method that works well for large products.txt files is to split the
products.txt file into small index files (in the example, 100
lines) with the \fIsplit\fR\|(1) \s-1UNIX/POSIX\s0 command. Then, index it with
Glimpse:
.PP
.Vb 2
\& split -100 products.txt index.txt.
\& glimpseindex -H /var/lib/interchange/foundation/products index.txt.*
.Ve
This will dramatically increase search speeds for large catalogs, at
least if the search term is relatively unique. If it is a common
string, in a category search, for example, it is better to use the
text-based search.
.PP
To search for numbers, add the \-n option to the Glimpse command
line.
.PP
\&\fBNote: \fRA large catalog is one of more than several thousand items;
smaller ones have acceptable speed in any of the search modes.
.PP
If the Glimpse executable is not found at Interchange startup, the
Glimpse search will be disabled and the regular text-based search used
instead.
.PP
There are several things to watch for while using Glimpse, and a
liberal dose of the Glimpse documentation is suggested. In particular,
the spelling error capability will not work in combination with the
field-specific search. Glimpse selects the line, but Interchange's
text-based search routines disqualify it when checking to see if the
search string is within one of the specified fields.
.PP
To use field-specific searching on Glimpse, tell it what the field
names are. If the search is on the products database (file), nothing
is needed for the default is to use the field names from the products
database. If it is some other field layout, specify the file to get
the field names from with mv_field_file (ff).
.Sh "Fast Binary Search"
.IX Subsection "Fast Binary Search"
Fast binary searching is useful for scanning large databases for
strings that match the beginning of a line. They use the standard Perl
module Search::Dict, and are enabled through use of the
mv_dict_look, mv_dict_end, mv_dict_limit, mv_dict_fold,
and mv_dict_order variables.
.PP
The field to search is the first field in the file, the product code
should be in the second field, delimited by \s-1TAB\s0. Set the
mv_return_fields=1 to return the product code in the search.
.PP
The search must be done on a dictionary-ordered pre-built index, which
can be produced with the database \s-1INDEX\s0 modifier. See Dictionary
indexing with \s-1INDEX\s0.
.PP
If using the mv_dict_look parameter by itself, and the proper index
file is present, Interchange will set the options:
.PP
.Vb 2
\& mv_return_fields=1
\& mv_dict_limit=-1
.Ve
This will make the search behave much like the simple search described
above, except it will be much faster on large files and will match
only from the beginning of the field. Here is an example. A title
index has been built by including in catalog.cfg:
.PP
.Vb 1
\& Database products INDEX title
.Ve
\&\fBNote: \fRThe \s-1ASCII\s0 source file must be \*(L"touched\*(R" to rebuild the index
and the database.
.PP
Now, specify in a form:
.PP
.Vb 4
\&
\&
\&
\&
.Ve
or in a \s-1URL:\s0
.PP
.Vb 1
\& [page search="dl=Van Gogh/di=title"]
.Ve
This search is case-sensitive. To do the same thing
case-insensitively:
.PP
.Vb 1
\& Database products INDEX title:f
.Ve
.Vb 5
\&
\&
\&
\&
\&
.Ve
.Vb 1
\& [page search="dl=Van Gogh/di=title/df=1"]
.Ve
.Sh "Coordinated and Joined Searching"
.IX Subsection "Coordinated and Joined Searching"
Interchange will do a complete range of tests on individual columns in
the database. To use this function, set mv_coordinate to Yes
(co=yes in the one-click syntax). In order to use coordinated
searching, the number of search fields must equal the number of search
strings.
.PP
To make sure that is the case, use the mv_search_map variable. It
allows variables to be mapped to others in the search specification.
For example:
.PP
.Vb 11
\&
\&
\&
\&
\& Artist:
\& Title:
\& Genre:
.Ve
Even if the user leaves one blank, the search will work.
.PP
Leading/trailing whitespace is stripped from all lines in the
mv_search_map variable, so it can be positioned as shown for
convenience.
.PP
Coordinated searches may be joined with the output of another table if
set one of the mv_search_field values is set to a table:column
pair. Note that this will slow down large searches considerably unless
there is another search specification, as the database must be
accessed for every search line If there is a search field that
qualifies for a regular expression search function, or conducting a
binary search with mv_dict_look, or are not doing an \s-1OR\s0 search,
the penalty should not be too great as only matching lines will cause
an access to the database.
.PP
Individual field operations can then be specified with the
mv_column_op (or op) parameter. The operations include:
.PP
.Vb 13
\& operation string numeric equivalent
\& ---------
\& equal to eq == =
\& not equal ne != <>
\& greater than gt >
\& less than lt <
\& less than/equal to le <=
\& greater than/equal to ge >=
\& regular expression rm =~ , LIKE
\& regular expression NOT rn !~
\& exact match em
\& Text::Query::Advanced aq
\& Text::Query::Simple tq
.Ve
An example:
.PP
.Vb 9
\& [page search="
\& co=yes
\& sf=title
\& se=Sunflowers
\& op=em
\& sf=artist
\& se=Van Gogh
\& op=rm
\& "] Sunflowers, Van Gogh
.Ve
.Vb 2
\& [page search="
\& co=yes
.Ve
.Vb 4
\& sf=title
\& se=Sunflowers
\& nu=0
\& op=!~
.Ve
.Vb 4
\& sf=artist
\& se=Van Gogh
\& op=rm
\& nu=0
.Ve
.Vb 5
\& sf=inventory:qty
\& se=1
\& op=>=
\& nu=1
\& "] Any in stock except Sunflowers, Van Gogh
.Ve
Note that in the second example, nu=0 must be specified even though
that is the default. This is to set the proper correspondence. To
avoid having to do this, use Interchange's option array feature:
.PP
.Vb 16
\& [page search.0="
\& sf=title
\& se=Sunflowers
\& op=!~
\& "
\& search.1="
\& sf=artist
\& se=Van Gogh
\& "
\& search.2="
\& sf=inventory:qty
\& se=1
\& op=>=
\& nu=1
\& "
\& ] Any in stock except Sunflowers, Van Gogh
.Ve
The co=yes is assumed when specifying a multiple search.
.PP
The second search will check the stock status of the painting provided
there is an inventory table as in some of the Interchange demo
catalogs. If the qty field is greater than or equal to 1, the
product will be picked. If out of stock, it will not be found.
.PP
It always helps to have an rm type included in the search. This is
used to pre-screen records so that database accesses only need be made
for already-matching entries. If accesses must be made for every
record, large searches can get quite slow.
.PP
The special aq and tq query types only operate if the
Text::Query \s-1CPAN\s0 module is installed. This allows Altavista-style
searches on the field, using \s-1AND\s0, \s-1OR\s0, \s-1NOT\s0, and \s-1NEAR\s0 with arbitrarily
complex parentheses.
.PP
A useful form for the aq type would be:
.PP
.Vb 10
\&
\&
\&
\&
\&
\&
\&
\&
\&
\&
.Ve
This searches the sku, description, comment, and category fields in
the default products file with Text::Query syntax. Try the term
\&\*(L"painters \s-1NEAR\s0 set\*(R" in the default foundation example.
.Sh "Custom search operators"
.IX Subsection "Custom search operators"
You can write your own search operator with Interchange's CodeDef. In
interchange.cfg, or in the code directory tree, you can put:
.PP
.Vb 11
\& CodeDef find_mirrored SearchOp
\& CodeDef find_mirrored Routine <
\& [/loop]
.Ve
The passed parameters are:
.RS 4
.Ip "\(bu" 8
The search object ($self)
.Ip "\(bu" 8
The index into coordinated search array ($i)
.Ip "\(bu" 8
The pattern to match
.Ip "\(bu" 8
The name of the op (find_hammer in this case)
.RE
.RS 4
.RE
.PP
Must return a sub which receives the data to match and returns 1 if it
matches. \s-1DOES\s0 \s-1NOT\s0 \s-1HONOR\s0 mv_negate \s-1UNLESS\s0 you tell it to.
.PP
See Vend::Search::create_text_query for an example of how to return a
proper routine and look in search object for the associated params.
.Sh "Specifying a Text-Based Search with \s-1SQL\s0 Syntax"
.IX Subsection "Specifying a Text-Based Search with SQL Syntax"
If the Perl \s-1SQL:\s0:Statement module is installed, \s-1SQL\s0 syntax can be
specified for the text-based search. This is not the same as the
external \s-1SQL\s0 database search, treated below separately. This works on
the \s-1ASCII\s0 text source file, not on the actual database.
.PP
This syntax allows this form setup:
.PP
.Vb 7
\& Artist:
\& Title:
\&
.Ve
If the right hand side of an expression looks like a column, i.e., is
not quoted, the appropriate form variable is substituted. (If used in
a one-click, the corresponding scratch variable is used instead.) The
assumption is reversed for the left-hand side. If it is a quoted
string, the column name is read from the passed values. Otherwise, the
column name is literal.
.PP
.Vb 5
\& Search for:
\& Search in title
\& artist
\&
.Ve
Once again, this does not conduct a search on an \s-1SQL\s0 database, but
formats a corresponding text-based search. Parentheses will have no
effect, and an \s-1OR\s0 condition will cause all conditions to be \s-1OR\s0. The
searches above would be similar to:
.PP
.Vb 11
\& [page search="
\& co=yes
\& sf=artist
\& op=rm
\& se=[value artist]
\& sf=title
\& op=rm
\& se=[value title]
\& " ]
\& Search for [value artist], [value title]
\&
.Ve
.Vb 9
\& [page search="
\& co=yes
\& sf=[value column]
\& op=rm
\& se=[value searchstring]
\& " ]
\& Search for [value searchstring]
\& in [value column]
\&
.Ve
.Sh "One-Click Searches"
.IX Subsection "One-Click Searches"
Interchange allows a search to be passed in a \s-1URL\s0, as shown above.
Just specify the search with the special page parameter search or
special page scan. Here is an example:
.PP
.Vb 6
\& [page search="
\& se=Impressionists
\& sf=category
\& "]
\& Impressionist Paintings
\&
.Ve
This is the same:
.PP
.Vb 3
\& [page scan se=Impressionists/sf=category]
\& Impressionist Paintings
\&
.Ve
Here is the same thing from a home page (assuming /cgi-bin/vlink is
the \s-1CGI\s0 path for Interchange's vlink):
.PP
.Vb 3
\&
\& Impressionist Paintings
\&
.Ve
The two-letter abbreviations are mapped with these letters:
.PP
.Vb 62
\& ac mv_all_chars
\& bd mv_base_directory
\& bs mv_begin_string
\& ck mv_cache_key
\& co mv_coordinate
\& cs mv_case
\& cv mv_verbatim_columns
\& de mv_dict_end
\& df mv_dict_fold
\& di mv_dict_limit
\& dl mv_dict_look
\& DL mv_raw_dict_look
\& do mv_dict_order
\& dr mv_record_delim
\& em mv_exact_match
\& er mv_spelling_errors
\& ff mv_field_file
\& fi mv_search_file
\& fm mv_first_match
\& fn mv_field_names
\& hs mv_head_skip
\& ix mv_index_delim
\& lb mv_search_label
\& lf mv_like_field
\& lo mv_list_only
\& lr mv_search_line_return
\& ls mv_like_spec
\& ma mv_more_alpha
\& mc mv_more_alpha_chars
\& md mv_more_decade
\& ml mv_matchlimit
\& mm mv_max_matches
\& MM mv_more_matches
\& mp mv_profile
\& ms mv_min_string
\& ne mv_negate
\& ng mv_negate
\& np mv_nextpage
\& nu mv_numeric
\& op mv_column_op
\& os mv_orsearch
\& pf prefix
\& ra mv_return_all
\& rd mv_return_delim
\& rf mv_return_fields
\& rn mv_return_file_name
\& rr mv_return_reference
\& rs mv_return_spec
\& se mv_searchspec
\& sf mv_search_field
\& sg mv_search_group
\& si mv_search_immediate
\& sm mv_start_match
\& sp mv_search_page
\& sq mv_sql_query
\& sr mv_search_relate
\& st mv_searchtype
\& su mv_substring_match
\& tf mv_sort_field
\& to mv_sort_option
\& un mv_unique
\& va mv_value
.Ve
These can be treated just the same as form variables on the page,
except that they can't contain a new line. If using the multi-line
method of specification, the characters will automatically be escaped
for a \s-1URL\s0.
.PP
\&\s-1IMPORTANT\s0 \s-1NOTE:\s0 An incompatibility in earlier Interchange catalogs is
specifying [page scan/se=searchstring]. This is interpreted by the
parser as [page scan/se=\*(L"searchstring\*(R"] and will cause a bad \s-1URL\s0.
Change this to [page scan se=searchstring], or perhaps better yet:
.PP
.Vb 3
\& [page search="
\& se=searchstring
\& "]
.Ve
A one-click search may be specified in three different ways.
.Ip "Original" 4
.IX Item "Original"
To do an \s-1OR\s0 search on the fields category and artist for the strings
\&\*(L"Surreal\*(R" and \*(L"Gogh,\*(R" while matching substrings, do:
.Sp
.Vb 2
\& [page scan se=Surreal/se=Gogh/os=yes/su=yes/sf=artist/sf=cat Van Gogh -- compare to surrealists
\&
.Ve
gory]
.Sp
In this method of specification, to replace a / (slash) in a file name
(for the sp, bd, or fi parameter), the shorthand of :: must be used,
i.e., sp=results::standard. (This may not work for some browsers, so
put the page in the main pages directory or define the page in a
search profile.)
.Ip "Multi-Line" 4
.IX Item "Multi-Line"
Specify parameters one to a line, as well.
.Sp
.Vb 7
\& [pag se="Van Gogh"
\& sp=lists/surreal
\& os=yes
\& su=yes
\& sf=artist
\& sf=category
\& ] Van Gogh -- compare to surrealists
.Ve
.Vb 1
\& scan
.Ve
Any \*(L"unsafe\*(R" characters will be escaped. To search for trailing spaces
(unlikely), quote.
.Ip "Ampersand" 4
.IX Item "Ampersand"
Substitute & for / in the specification and be able to use / and
quotes and spaces in the specification.
.Sp
.Vb 2
\& [page href=scan se="Van Gogh"&sp=lists/surreal&os=yes&su=yes&sf=a Van Gogh -- compare to surrealists
\&
.Ve
tist]
.Sp
Any \*(L"unsafe\*(R" characters will be escaped.
.Sh "Setting Display Options with mv_value"
.IX Subsection "Setting Display Options with mv_value"
A value can be specified that will be set in the link with the
mv_value parameter. It takes an argument of var=value, just as
setting a normal variable in an Interchange profile. Actually
mv_value is a misnomer, it will almost never be used in a form
where variable values can be set. Always specify it in a one-click
search with va=var=value. Example:
.PP
.Vb 5
\& [page href=scan
\& arg="se=Renaissance
\& se=Impressionists
\& va=category_name=Renaissance and Impressionist Paintings
\& os=yes"]Renaissance and Impressionist Paintings
.Ve
Display the appropriate category on the search results page with
[value category_name].
.Sh "In-Page Searches"
.IX Subsection "In-Page Searches"
To specify a search inside a page with the [search-region
parameters*] tag. The parameters are the same as the one-click
search, and the output is always a newline-separated list of the
return objects, by default, a series of item codes.
.PP
The [loop ...] tag directly accepts a search parameter. To search
for all products in the categories \*(L"Americana\*(R" and \*(L"Contemporary,\*(R" do:
.PP
.Vb 9
\& [loop search="
\& se=Americana
\& se=Contemporary
\& os=yes
\& sf=category9
\& "]
\& Artist: [loop-field artist]
\& Title: [loop-field title]

\& [/loop]
.Ve
The advantage of the in-page search is that searches can be embedded
within searches, and there can be straight unchanging links from
static \s-1HTML\s0 pages.
.PP
To place an in-page search with the full range of display in a normal
results page, use the [search-region] tag the same as above, except
that [search-list], [more-list], and [more] tags can be
placed within it. Use them to display and format the results,
including paging. For example:
.PP
.Vb 15
\& [search-region more=1
\& search="
\& se=Americana
\& sf=category
\& ml=2
\& "]
\& [more-list][more][/more-list]
\& [search-list]
\& [page [item-code]]
\& [item-field title], by [item-field artist]
\& [/search-list]
\& [no-match]
\& Sorry, no matches for [value mv_searchspec].
\& [/no-match]
\& [/search-region]
.Ve
\&\fBNote: \fRThe [item-code] above does not need to be quoted because it
is replaced before the [page ...] tag is interpolated. If building
large lists, this is worth doing because unquoted tags are twice as
fast to parse.
.PP
To use the same page for search paging, make sure to set the
sp=page parameter.
.Sh "Search Profiles"
.IX Subsection "Search Profiles"
An unlimited number of search profiles can be predefined that reside
in a file or files. To use this, make up a series of lines like:
.PP
.Vb 3
\& mv_search_field=artist
\&mv_search_field=category
\&mv_orsearch=yes
.Ve
These correspond to the Interchange search variables that can be set
on a form. Set it right on the page that contains the search.
.PP
.Vb 5
\& [set artist_profile]
\&mv_search_field=artist
\&mv_search_field=category
\&mv_orsearch=yes
\&[/set]
.Ve
This is the same:
.PP
.Vb 5
\& [set artist_profile]
\&sf=artist
\&sf=category
\&os=yes
\&[/set]
.Ve
Then, in the search form, set a variable with the name of the profile:
.PP
.Vb 1
\&
.Ve
In a one-click search, use the mp modifier:
.PP
.Vb 1
\& [page scan se=Leonardo/mp=artist_profile]A left-handed artist
.Ve
They can also be placed in a file. Define the file name in the
SearchProfile directive. The catalog must be reconfigured for
Interchange to read it. The profile is named by placing a name
following a _\|_NAME_\|_ pragma:
.PP
.Vb 1
\& __NAME__ title_search
.Ve
The _\|_NAME_\|_ must begin the line, and be followed by whitespace and
the name.
.PP
The special variable mv_last stops interpretation of search
variables. The following variables are always interpreted:
.PP
.Vb 2
\& mv_dict_look
\& mv_searchspec
.Ve
Other than that, if mv_last is set in a search profile, and there
are other variables on the search form, they will not be interpreted.
.PP
To place multiple search profiles in the same file, separate them with
_\|_END_\|_, which must be on a line by itself.
.Sh "Search Reference"
.IX Subsection "Search Reference"
The supplied simple/srchform.html and simple/results.html pages
show example search forms. Modify them to present the search in any
way desired. Be careful to use the proper variable names for passing
to Interchange. It is also necessary to copy the hidden variables
as-is. They are required to interpret the request as a search.
.PP
\&\fBNote: \fRThe following definitions frequently refer to field name and
column and column number. All are the references to the columns of a
searched text file as separated by delimiter characters.
.PP
The field names can be specified in several ways.
.Ip "ProductFiles" 4
.IX Item "ProductFiles"
If the file to be searched is left empty in the search form or
definition (it is set with mv_search_file (fi)), the text files
associated with the products databases will be searched, and field
names are already available as named in the first line of the \fIfile\fR\|(s).
This is defined to be products.txt in the Interchange demo
catalogs.
.Sp
Be careful if using \s-1SQL\s0! If the database is changed and not exported
with [tag export products][/tag], searches will not be successful.
.Ip "Other database files" 4
.IX Item "Other database files"
If the file or files to be searched are \s-1ASCII\s0 delimited files, and
have field names specified on the first line of the file, Interchange
will read the first line (of the first file) and determine the field
names.
.Ip "Other files" 4
.IX Item "Other files"
If the file or files to be searched are \s-1ASCII\s0 delimited files, but
don't have field names specified on the first line of the file, set
the variable mv_field_names to a comma-separated list of field
names as they will be referenced.
.PP
Fields can also always be specified by an integer column number, with
0 as the first column.
.Ip "mv_all_chars" 4
.IX Item "mv_all_chars"
Scan abbreviation: ac=[1|0]. Set this if searching is anticipated for
lots of punctuation characters that might be special characters for
Perl. The characters ()[]\e$^ are included.
.Ip "mv_base_directory" 4
.IX Item "mv_base_directory"
Scan abbreviation: bd=/directory/name. In the text search, set to the
directory from which to base file searches. File names without leading
/ characters will be based from there. In the Glimpse search, passed
to Glimpse with the \-H option, and Glimpse will look for its
indices there. Default is ProductDir.
.Sp
If an absolute path directory is used, for security enable it in the
users session with:
.Sp
.Vb 1
\& [set /directory/name]1[/set]
.Ve
This prevents users from setting an arbitrary value and viewing
arbitrary files.
.Ip "mv_begin_string" 4
.IX Item "mv_begin_string"
If this is set, the string will only match if it is at the beginning
of a field. The handling is a bit different for the default \s-1AND\s0 search
compared to the \s-1OR\s0 search. With \s-1OR\s0 searches all words are searched for
from the beginning of the field, with \s-1AND\s0 searches all are.
.Sp
This is a multiple parameter. If mv_coordinate is in force, it
should be set as many times as necessary to match the
field/searchstring combination. If set only once, it applies to all
fields. If set more than once but not as many times as the fields, it
will default to off.
.Ip "mv_cache_key" 4
.IX Item "mv_cache_key"
Not normally set by the user. It is a value that provides a pointer to
the search reference by the more function.
.Ip "mv_case" 4
.IX Item "mv_case"
If this item is set to No, the search will return items without
regard to upper or lower case. This is the default. Set to Yes if
case should be matched. Implement with a checkbox field.
.Sp
If stacked to match the mv_search_field and mv_searchspec
variables, and mv_coordinate is set, it will operate only for the
corresponding field.
.Sp
Scan abbreviation: cs
.Ip "mv_column_op" 4
.IX Item "mv_column_op"
In the coordinated search, the operation that will be performed to
check the field for a search match. These operations are supported:
.Sp
.Vb 1
\& != Not equal to
.Ve
.Ip "" 4
If stacked to match the mv_search_field and mv_searchspec
variables, and mv_coordinate is set, it will operate only for the
corresponding field.
.PP
Note that several of the operators are the same. They do either
numeric or string comparisons based on the status of mv_numeric
(alias nu) for that column.
.Ip "mv_coordinate" 4
.IX Item "mv_coordinate"
If this item is set to Yes, and the number of search fields equals
the number of search specs, the search will return only items that
match field to spec. (The search specifications are set by stacked
mv_searchspec and mv_search_field variables.)
.Sp
Case sensitivity, substring matching, and negation all work on a
field-by field basis according to the following:
.RS 4
.Ip "" 8
If only one instance of the option is set, it will affect all fields.
.Sp
If the number of instances of the option is greater than or equal to
the number of search specs, all will be used independently. Trailing
instances will be ignored.
.Sp
If more than one instance of the options are set, but fewer than the
number of search specifications, the default setting will be used for
the trailing unset options.
.Sp
If a search specification is blank, it will be removed and all
case-sensitivity/negation/substring options will be adjusted
accordingly. If you need a blank string to match on, use quotes
("").
.RE
.RS 4
.RE
.Ip "mv_dict_end" 4
.IX Item "mv_dict_end"
If the string at the beginning of a line lexically exceeds this value,
matching will stop. Ignored without mv_dict_look.
.Ip "mv_dict_fold" 4
.IX Item "mv_dict_fold"
Make dictionary matching case-insensitive. Ignored without
mv_dict_look.
.PP
\&\fBNote: \fRThis is the reverse sense from mv_case.
.Ip "mv_dict_limit" 4
.IX Item "mv_dict_limit"
Automatically set the limiting string (mv_dict_end) to be one
character greater than the mv_dict_look variable, at the character
position specified. A value of 1, for instance, will set the limiting
string to \*(L"fprsythe\*(R" if the value of mv_dict_look is \*(L"forsythe\*(R". A
useful value is \-1, which will increment the last character (setting
the mv_dict_end to \*(L"forsythf\*(R" in our example). This prevents having to
scan the whole file once a unique match is found.
.PP
\&\fBNote: \fRThe order of this and the mv_dict_end variable is
significant. Each will overwrite the other.
.Ip "" 4
If this is set to a non-numeric value, an automatic mode is entered
which looks for a dictionary-indexed file that corresponds to the file
name plus .field, where field is whatever mv_dict_limit is
set to. The actual value of mv_dict_limit is set to \-1. If the file
does not exist, the original file is silently used. Also, the value of
mv_return_fields is set to 1 to correspond to the location of
the key in the auto-indexed file.
.Sp
To illustrate:
.Sp
.Vb 1
\&
.Ve
gory>
.Sp
is equal to:
.Sp
.Vb 2
\&
\&
.Ve
\&\*(L"\-1\*(R">
.Sp
The real utility would be in a form construct like
.Sp
.Vb 4
\& Sear beginning with
.Ve
h for
.Sp
which would allow automatic binary search file selection.
.Sp
Combined with the \s-1INDEX\s0 attribute to the Database directive, this
allows fast binary search qualification combined with regular
mv_searchspec text searches.
.Ip "mv_dict_look" 4
.IX Item "mv_dict_look"
The string at which to begin matching at in a dictionary-based search.
If not set, the mv_dict_end, mv_dict_fold, and mv_dict_case
variables will be ignored. May be set in a search profile based on
other form variables.
.Ip "mv_dict_order" 4
.IX Item "mv_dict_order"
Make dictionary matching follow dictionary order, where only word
characters and whitespace matter. Ignored without mv_dict_look.
.Ip "mv_doit" 4
.IX Item "mv_doit"
This can be set to search to make a form with a process action
be a search page by default. The mv_todo variable takes precedence.
.Ip "mv_exact_match" 4
.IX Item "mv_exact_match"
Normally Interchange searches match words, as opposed to sentences.
This behavior can be overridden with mv_exact_match, which when set
will place quotes around any value in mv_searchspec or
mv_dict_look.
.Ip "mv_field_file" 4
.IX Item "mv_field_file"
If you want to search a file which has no field header on the first
line, you can specify a file to get the field names from. It expects a
single line with the field names separated by \s-1TAB\s0 characters.
.Ip "mv_field_names" 4
.IX Item "mv_field_names"
Deprecated in favor of in-list sorting. Defines the field names for
the file being searched. This guarantees that they will be available,
and prevents a disk access if using named fields on a search file
(that is not the product database \s-1ASCII\s0 source, where field names are
already known). This must be exactly correct, or it will result in
anomalous search operation. Usually passed in a hidden field or search
profile as a comma-separated list.
.PP
\&\fBNote: \fRUse this on the product database only if planning on both
pre-sorting with mv_sort_field and then post-sorting with
[sort]field:opt[/sort].
.Ip "mv_first_match" 4
.IX Item "mv_first_match"
Normally Interchange will return the first page of a search. If this
variable is set, it will start the search return at the match
specified, even if there is only one page. If set to a value greater
than the number of matches, it will act as if no matches were found.
.Ip "mv_head_skip" 4
.IX Item "mv_head_skip"
Normally Interchange searches all lines of an index/product file but
the first. Set this to the number of lines to skip at the beginning of
the index. Default is 1 for the text search, which skips the header
line in the product file. Default is 0 for a Glimpse search.
.Ip "mv_index_delim" 4
.IX Item "mv_index_delim"
Sets the delimiter for counting fields in a search index. The default
is \s-1TAB\s0. It should rarely be changed unless you are searching a
pipe-delimited or colon-delimited file.
.Ip "mv_like_field" 4
.IX Item "mv_like_field"
Specifies a field in a database search which should be used for a
screening function based on the \s-1SQL\s0 like function. Needs
mv_like_spec.
.Ip "mv_like_spec" 4
.IX Item "mv_like_spec"
The string that should be searched for in mv_like_field. The
behavior of the % character and case-sensitivity depends on your \s-1SQL\s0
implementation.
.Ip "mv_matchlimit" 4
.IX Item "mv_matchlimit"
Function depends upon context. When the search results display is
handled by one of the mechanisms which works with [more] lists
(such as [search-region]), mv_matchlimit determines the number of
results per page. If more matches than mv_matchlimit are found, the
search paging mechanism will be employed if the proper [more-list]
is present. When the search results are displayed as one continuous
list (i.e.: with [loop search=\*(L"...\*(R"]), mv_matchlimit is equivalent
in function to mv_max_matches.
.Sp
To have no matchlimit, use \fBnone\fR instead of a number. \fBall\fR does
the same thing (since returning \*(L"all\*(R" is just anothing way of looking
at no matchlimit).
.Sp
If no matchlimit is provided, or an invalid setting (some other string
or 0) the default is taken from catalog variable
\&\s-1MV_DEFAULT_MATCHLIMIT\s0, and if that's not set, is 50.
.Ip "mv_max_matches" 4
.IX Item "mv_max_matches"
The maximum number of records that will be returned in a search.
Default is unlimited. If search results paging with [more-list] is
to be employed, Use mv_matchlimit to set the number of results per
page.
.Ip "mv_min_string" 4
.IX Item "mv_min_string"
Sets the minimum size of a search string for a search operation.
Default is 4 for the Glimpse search, and 1 for the text search.
.Ip "mv_negate" 4
.IX Item "mv_negate"
Specifies that records \s-1NOT\s0 matching the search criteria will be
returned. Default is no. It is not operative for the Glimpse search.
.Sp
If stacked to match the mv_search_field and mv_searchspec
variables, and mv_coordinate is set, it will operate only for the
corresponding field.
.Ip "mv_orsearch" 4
.IX Item "mv_orsearch"
If this item is set to Yes, the search will return items matching
any of the words in searchspec. The default is No.
.Ip "mv_profile" 4
.IX Item "mv_profile"
Selects one of the pre-defined search specifications set by the
SearchProfile directive. If the special variable within that file,
mv_last, is defined, it will prevent the scanning of the form input
for further search modifications. The values of mv_searchspec and
mv_dict_look are always scanned, so specify this to do the
equivalent of setting multiple checkboxes or radioboxes with one
click, while still reading the search input text.
.Ip "mv_record_delim" 4
.IX Item "mv_record_delim"
Sets the delimiter for counting records in a search index. The default
is newline, which works for the products and most line-based index
files.
.Ip "mv_return_fields" 4
.IX Item "mv_return_fields"
The \fIfield\fR\|(s) that should be returned by the match, specified either by
field name or by column number, separated by commas. Do not list the
same field more than once per search. Specify 0 as the first field to
be returned if searching the products database, since that is the key
for accessing database fields.
.PP
As with \s-1SQL\s0 queries, you can use the '*' shortcut to return all
fields. For example:
.PP
.Vb 1
\& [loop search="fi=nation/ra=yes/rf=*"]
.Ve
when used with a hypothetical 'nation' table would be equivalent to:
.PP
.Vb 5
\& [loop search="
\& fi=nation
\& ra=yes
\& rf=code,sorder,region,name,tax
\& "]
.Ve
as well as:
.PP
.Vb 1
\& [loop search="fi=nation/ra=yes/rf=0,1,2,3,4"]
.Ve
and:
.PP
.Vb 1
\& [query sql="select * from nation"][/query]
.Ve
However, you probably rarely need to use every single field in a row.
For maximum maintainability and execution speed the best practice is
to list by name only the fields you want returned.
.Ip "mv_return_spec" 4
.IX Item "mv_return_spec"
Returns the string specified as the search (i.e., the value of
mv_searchspec) as the one and only match. Typically used in a
SKU/part number search.
.Ip "mv_search_field" 4
.IX Item "mv_search_field"
The \fIfield\fR\|(s) to be searched, specified either by column name or by
column number.
.Sp
If the number of instances matches the number of fields specified in
the mv_searchspec variable and mv_coordinate is set to true,
each search field (in order specified on the form) will be matched
with each search spec (again in that order).
.Ip "mv_search_file" 4
.IX Item "mv_search_file"
In the text search, set this variable to the \fIfile\fR\|(s) to be scanned for
a match. The default, if not set, is to scan the default ProductFiles
(i.e., products.txt). If set multiple times in a form (for a text
search), will cause a search all the files. One file name per
instance.
.Sp
In the Glimpse search, follows the Glimpse wildcard-based file name
matching scheme. Use with caution and a liberal dose of the Glimpse
man page.
.Ip "mv_search_match_count" 4
.IX Item "mv_search_match_count"
Set by the search to indicate the total number of matches found.
.Ip "mv_search_page" 4
.IX Item "mv_search_page"
The Interchange-style name of the page that should display the search
results. This overrides the default value of search.
.Ip "mv_searchspec" 4
.IX Item "mv_searchspec"
The actual search string that is typed in by the customer. It is a
text \s-1INPUT\s0 TYPE=TEXT field, or can be put in a select (drop-down) list
to enable category searches. If multiple instances are found, they
will be concatenated just as if multiple words had been placed in a
text field.
.Sp
The user can place quotes around words to specify that they match as a
string. To enable this by default, use the mv_exact_match variable.
.Sp
If mv_dict_look has a value, and mv_searchspec does not, then
mv_searchspec will be set to the value of mv_dict_look.
.Sp
If the number of instances matches the number of fields specified in
the mv_search_field variable and mv_coordinate is set to true,
each search field (in order specified on the form) will be matched
with each search spec (again in that order).
.Ip "mv_searchtype" 4
.IX Item "mv_searchtype"
If set to Glimpse, selects the Glimpse search (if Glimpse is defined).
.Sp
If set to db, iterates over every row of the database (not the
associated text source file).
.Sp
If set to sql, same as db.
.Sp
If set to text, selects the text-based search.
.Sp
When using st=db, returned keys may be affected by
TableRestrict. See \s-1CATALOG\s0.CFG.
.Sp
Defaults to text if Glimpse is not defined; defaults to Glimpse if it
is defined. This can allow use of both search types if that is
desirable. For instance, searching for very common strings is better
done by the text-based search. An example might be searching for
categories of items instead of individual items.
.Ip "mv_small_data" 4
.IX Item "mv_small_data"
.PP
Tells the search engine that there is a small amount of data in the
file and that it should perform the search function on every line.
.PP
Normally, when Interchange can find a fixed search expression it
produces a \*(L"screening\*(R" function which will allow records to be quickly
rejected when they don't match. If there are less than 50 records in
the file or database, this may be counterproductive.
.Ip "mv_sort_field" 4
.IX Item "mv_sort_field"
The file \fIfield\fR\|(s) the search is to be sorted on, specified in one of
two ways. If the \fIfile\fR\|(s) to be searched have a header line (the first
line) that contains delimiter-separated field names, it can be
specified by field name. It can also be specified by column number
(the code or key is specified with a value of 0, for both types).
These can be stacked if coming from a form or placed in a single
specification separated by commas.
.PP
\&\fBNote: \fRIf specifying a sort for the product database,
mv_field_names must be specified if doing a fieldname-addressed
post-sort.
.Ip "mv_sort_option" 4
.IX Item "mv_sort_option"
The way that each field should be sorted. The flags are r, n,
and f, reverse, numeric, and case-insensitive respectively. These
can be stacked if coming from a form or placed in a single
specification separated by commas. The stacked options will be applied
to the sort fields as they are defined, presuming those are stacked.
.Ip "mv_spelling_errors" 4
.IX Item "mv_spelling_errors"
The number of spelling errors that will be tolerated. Ignored unless
using Glimpse. For a large table, limit this to two.
.Ip "mv_substring_match" 4
.IX Item "mv_substring_match"
If mv_substring_match is set to Yes, matches on substrings as
well as whole words. Typically set this for dictionary-based searches.
.Sp
If stacked to match the mv_search_field and mv_searchspec
variables and mv_coordinate is set, it will operate only for the
corresponding field.
.Ip "mv_unique" 4
.IX Item "mv_unique"
If set to a true value, causes the sort to return only unique results.
This operates on whatever the search return is, as defined by
mv_return_fields.
.Ip "mv_value" 4
.IX Item "mv_value"
This is normally only used in the one-click search (va=var=value). It
allows setting of a session variable based on the clicked link, which
makes for easy definition of headers and other display choices. (If
had trouble using mv_searchspec for this before, this is what is
needed.)
.Sh "The Results Page"
.IX Subsection "The Results Page"
Once a search has been completed, there needs to be a way of
presenting the output. By default, the SpecialPage search is used.
It is set to results in the distribution demo, but any number of
search pages can be specified by passing the value in the search form
specified in the variable mv_search_page.
.PP
On the search page, some special Interchange tags are used to format
the otherwise standard \s-1HTML\s0. Each of the iterative tags is applied to
every code returned from the search. This is normally the product
code, but could be a key to any of the arbitrary databases. The value
placed by the [item-code] tag is set to the first field returned
from the search.
.PP
The basic structure looks like this:
.PP
.Vb 11
\& [search-region]
\&[search-list]
\& your iterating code, once for each match
\&[/search-list]
\&[no-match]
\& Text / tags to be output if no matches found (optional but recommended)
\&[/no-match]
\&[more-list]
\& More / paging area (optional)
\&[/more-list]
\&[/search-region]
.Ve
Tip for catalogs upgraded from Minivend 3: A
[search-list][/search-list] must always be surrounded by a
[search-region][/search-region] pair. This is a change from
Minivend 3.
.Ip "[search-list]" 4
.IX Item "[search-list]"
Starts the representation of a search list. Interchange tags can be
embedded in the search list, yielding a table or formatted list of
items with part number, description, price, and hyperlinks to order or
go to its catalog page.
.Sp
The example tags shown have an item- prefix, which is the default.
Set any prefix desired with the prefix parameter to
[search-region]:
.Sp
.Vb 5
\& [search-region pref [search-list]
\& SKU: [my-code]
\& Title: [my-data products title]
\& [/search-list]
\& [/search-region]
.Ve
x=my]
.Sp
The standard set of Interchange iterative \s-1ITL\s0 tags are available. They
are interpolated in this order:
.Sp
.Vb 21
\& [item-alternate N] true [else] false [/else] [/item-alte [if-item-param named_field] true [else] false [/else] [/if-item-param]
\& [item-param named_field]
\& [if-item-pos N] true [else] false [/else] [/if-item-pos]
\& [item-pos N]
\& [if-item-field products_field] true [else] false [/else] [/if-item-field]
\& [item-field products_column]
\& [item-increment]
\& [item-accessories]
\& [item-code]
\& [item-description]
\& [if-item-data table column] true [else] false [/else] [/if-item-data]
\& [item-data table column]
\& [item-price N* noformat=1*]
\& [item-calc] [/item-calc]
\& [item-change marker]
\& [condition]variable text[/condition]
\& true
\& [else] false [/else]
\& [/item-change marker]
\& [item-last] condition [/item-last]
\& [item-next] condition [/item-next]
.Ve
nate]
.PP
\&\fBNote: \fRthose that reference the shopping cart do not apply, i.e.,
[item-quantity], [item-modifier ...] and friends.
.Ip "[/search-list]" 4
.IX Item "[/search-list]"
Ends the search list.
.Ip "[no-match]" 4
.IX Item "[no-match]"
Starts the region of the search results page that should be returned
if there is no match (and no error) for the search. If this is not on
the page, the special page nomatch will be displayed instead.
.Ip "[/no-match]" 4
.IX Item "[/no-match]"
Ends the no match region.
.Ip "[sort database:field:option* database:field:option*]" 4
.IX Item "[sort database:field:option* database:field:option*]"
Sorts the search list return based on database fields. If no options
are supplied, sorts according to the return code. See \s-1SORTING\s0.
.Sp
This is slow, and it is far better to pre-sort the return in the
search specification.
.Ip "[item-change marker]" 4
.IX Item "[item-change marker]"
Active only within [search-list][/search-list].
.Sp
Along with the companion [/item-change marker], surrounds a region
which should only be output when a field (or other repeating value)
changes its value. This allows indented lists similar to database
reports to be easily formatted. The repeating value must be a tag
interpolated in the search process, such as [item-field field] or
[item-data database field].
.Sp
Of course, this will only work as expected when the search results are
properly sorted.
.Sp
The marker field is mandatory, and is also arbitrary, meaning that
any marker can be selected as long as it matches the marker associated
with [/item-change marker]. The value to be tested is contained
within a [condition]value[/condition] tag pair. The [item-change
marker] tag also processes an [else] [/else] pair for output when
the value does not change. The tags may be nested as long as the
markers are different.
.Sp
The following is a simple example for a search list that has a field
category and subcategory associated with each item:
.Sp
.Vb 5
\& <

\& [/search-list]
\&
.Ve
\&\s-1ABLE\s0>
.Sp
The above should output a table that only shows the category and
subcategory once, while showing the name for every product. (The
will prevent blanked table cells if using a border.)
.Ip "[/item-change marker]" 4
.IX Item "[/item-change marker]"
Companion to [item-change marker].
.Ip "[matches]" 4
.IX Item "[matches]"
Replaced with the range of match numbers displayed by the search page.
Looks something like \*(L"1\-50\*(R". Make sure to insert this item between a
[more-list] and [/more-list] element pair.
.Ip "[match-count]" 4
.IX Item "[match-count]"
Replaced with the total number of matches. This tag works even on
[query] searches where [value mv_search_match_count] isn't set
unless the query is applied to a non-SQL database. Make sure to insert
this item between a [more-list] and [/more-list] element pair.
.Ip "[more-list next_img* prev_img* page_img* border* border_current*]" 4
.IX Item "[more-list next_img* prev_img* page_img* border* border_current*]"
Starts the section of the search page which is only displayed if there
are more matches than specified in mv_matchlimit. If there are less
matches than the number in mv_matchlimit, all text/html between the
[more_list] and [/more_list] elements is stripped.
.Sp
Use in conjunction with the [more] element to place pointers to
additional pages of matches.
.Sp
If the optional arguments next_img, prev_img, and/or page_img
are present, they represent image files that will be inserted instead
of the standard 'Next,' 'Previous,' and page number. If prev_img is
none, then no previous link will be output. If page_img is
none, then no links to pages of matches will be output. These are
URLs, are substituted for with \fIImageDir\fR and friends, and will be
encased in \s-1IMG\s0 tags. Lastly, border is the border number to put.
.Sp
In addition, if page_img is used, it will be passed an argument of
the digit that is to be represented. This would allow an image
generator program to be used, generating page numbers on the fly. The
border and border_selected values are integers indicating the
border that should be put around images in the page_img selection.
The is used for the current page if set.
.Sp
\&\eExamples:
.Sp
[more-list next.gif prev.gif page_num.cgi 3] causes anchors of:
.Sp
.Vb 3
\& Previous
\& Page 2
\& Next
.Ve
er=3>
.Sp
[more-list next.gif prev.gif page_num.cgi] causes anchors of:
.Sp
.Vb 3
\& Previous
\& Page 2
\& Next
.Ve
gif">
.Sp
[more-list next.gif prev.gif 0 0] causes anchors of:
.Sp
.Vb 3
\& Previous
\& Page 2
\& Next
.Ve
er=0>
.Sp
To set custom text for the \*(L"Previous\*(R" and \*(L"Next\*(R" usually used, define
the next_img, prev_img, and page_img with
[next-anchor][/next-anchor], [prev-anchor][/prev-anchor],
[first-anchor][/first-anchor], [last-anchor][/last-anchor] and
[page-anchor][/page-anchor]. The string \f(CW$PAGE\fR$ will be replaced
with the page number in the latter. The same example:
.Sp
.Vb 7
\& [more [first-anchor] First [/first-anchor]
\& [next-anchor] Forward | [/next-anchor]
\& [prev-anchor] Back [/prev-anchor]
\& [last-anchor] Last [/last-anchor]
\& [page-anchor] Page $PAGE$ (matches $MINPAGE$-$MAXPAGE$) | [/page-anchor]
\& [more]
\& [/more-list]
.Ve
list]
.Sp
will display Forward | Page 1 (matches 1\-50) | Page 2 (matches
51\-77) | Back for 2 pages. Note that the following anchors are
replaced with the page number, the minimum match on the page, and the
maximum match on the page:
.Sp
.Vb 2
\& $PAGE$ Page $MINPAGE$ Minimum match on page
\& $MAXPAGE$ Maximum match on page
.Ve
umber
.Sp
You can customize the \s-1HTML\s0 hyperlink with [link-template]
[/link-template]. This is useful for adding a JavaScript onclick
attribute, or setting the link target to a different window, etc.
.Sp
.Vb 1
\& [link-template]$ANCHOR$[/link-template]
.Ve
There are two tokens you can use as many times as needed in
[link-template], which will be replaced as follows:
.Sp
.Vb 2
\& $URL$ The URL for the 'more' page in qu $ANCHOR$ The page number or the word "Next" or "Previous"
\& for the link in question.
.Ve
stion
.Sp
If have many pages of matches and don't wish to have all displayed at
once, set [decade-next][/decade-next] and
[decade-prev][/decade-prev]. If set them empty, a search with 31
pages will display pages 21\-30 like:
.Sp
.Vb 1
\& Previous 1 2 3 4 5 6 7 8 9 10 [more>>] Next
.Ve
and pages 11\-20 like:
.Sp
.Vb 1
\& Previous [<>] Next
.Ve
If set to [decade-next](higher)[/decade-next] and
[decade-prev](lower)[/decade-prev], the following will be
displayed:
.Sp
.Vb 1
\& Previous (lower) 11 12 13 14 15 16 17 18 19 20 (higher) Next
.Ve
Of course, image-based anchors can be used as well.
.Ip "[/more-list]" 4
.IX Item "[/more-list]"
Companion to [more-list].
.Ip "[more]" 4
.IX Item "[more]"
Inserts a series of hyperlinks that will call up the next matches in a
series. They look like this:
.Sp
.Vb 1
\& Previous 1 2 3 4 5 6 Next
.Ve
The current page will not be a hyperlink. Every time the new link is
pressed, the list is re-built to correspond to the current page. If
there is no Next or Previous page, that link will not be shown.
.Sp
See the search.html file for examples. Make sure to insert this
item between a [more-list] and [/more-list] element pair.
.Ip "[process-search]" 4
.IX Item "[process-search]"
Outputs the complete \s-1URL\s0 for a search, including Interchange session
tags. Used as the \s-1ACTION\s0 value for the search form. This is exactly
the same as [area search].
.SH "Sorting"
.IX Header "Sorting"
Interchange has standard sorting options for sorting the search lists,
loop lists, and item lists based on the contents of database fields.
In addition, it adds list slices for limiting the displayed entries
based on a start value and chunk size (or start and end value, from
which a chunk size is determined). All accept a standard format sort
tag which must be directly after the list call:
.PP
.Vb 4
\& [loop 4 3 2 1]
\& [sort -2 +2]
\& [loop-code]
\& [/loop]
.Ve
.Vb 4
\& [search-list]
\& [sort products:category:f]
\& [item-price] [item-description]
\& [/search-list]
.Ve
.Vb 4
\& [item-list]
\& [sort products:price:rn]
\& [item-price] [item-code]
\& [/item-list]
.Ve
.Vb 4
\& [loop search="ra=yes"]
\& [sort products:category products:title]
\& [loop-field category] [loop-field title]
\& [/loop]
.Ve
All sort situations, [search list], [loop list], [tag each
table], and [item-list], take options of the form:
.PP
.Vb 1
\& [sort database:field:option* -n +n =n-n ... ]
.Ve
.Ip "database" 4
.IX Item "database"
The Interchange database identifier. This must be supplied and should
normally be 'products' if using the default name for the database.
.Ip "field" 4
.IX Item "field"
The field (column) of the database to be sorted on.
.Ip "option" 4
.IX Item "option"
None, any, or combinations of the options:
.Sp
.Vb 2
\& f case-insensitive sort (folded) (mutually exclusive n numeric order (mutually exclusive of f)
\& r reverse sort
.Ve
of n)
.Ip "\-n" 4
.IX Item "-n"
The starting point of the list to be displayed, beginning at 1 for the
first entry.
.Ip "+n" 4
.IX Item "+n"
The number of entries to display in this list segment.
.Ip "=n-n" 4
.IX Item "=n-n"
The starting and ending point of the list display. This is an
alternative to \-n and +n. They should be specified in only one
form. If both are specified, the last one will take effect.
.Ip "..." 4
Don't really put ... in. This means that many sort levels are
specified. Lots of sort levels with large databases will be quite
slow.
.PP
Multiple levels of sort are supported, and database boundaries on
different sort levels can be crossed. Cross-database sorts on the same
level are not supported. If using multiple product databases, they
must be sorted with embedded Perl. This is actually a feature in some
cases, all items in a used database can be displayed before or
after new ones in products.
.PP
Examples, all based on the simple demo:
.Ip "Loop list" 4
.IX Item "Loop list"
.Vb 3
\& [loop 00-0011 19-202 34-101 9 [sort products:title]
\& [loop-code] [loop-field title]
\& [/loop]
.Ve
\&\-102]
.Sp
Will display:
.Sp
.Vb 3
\& 34-101 Family Po 00-0011 Mona Lisa
\& 19-202 Radioactive Cats
\& 99-102 The Art Store T-Shirt
.Ve
trait
.Sp
\&\eAlternatively:
.Sp
.Vb 3
\& [loop 00-0011 19-202 34-101 9 [sort products:title -3 +2]
\& [loop-code] [loop-field title]
\& [/loop]
.Ve
\&\-102]
.Sp
\&\eDisplays:
.Sp
.Vb 1
\& 19-202 Radioactiv 99-102 The Art Store T-Shirt
.Ve
.Vb 1
\& Cats
.Ve
The tag [sort products:title =3\-4] is equivalent to the above.
.Ip "Search list" 4
.IX Item "Search list"
A search of all products (i.e.,
http://yoursystem.com/cgi-bin/simple/scan/ra=yes):
.Sp
.Vb 3
\& [search [sort products:artist products:title:rf]
\& [item-field artist] [item-field title]
\& [/search-list]
.Ve
list]
.Sp
will display:
.Sp
.Vb 8
\& Gilded Grant Wood American Gothic
\& Jean Langan Family Portrait
\& Leonardo Da Vinci Mona Lisa
\& Salvador Dali Persistence of Memory
\& Sandy Skoglund Radioactive Cats
\& The Art Store The Art Store T-Shirt
\& Vincent Van Gogh The Starry Night
\& Vincent Van Gogh Sunflowers
.Ve
Frame
.Sp
Note the reversed order of the title for Van Gogh and the presence of
the accessory item Gilded Frame at the front of the list. It has no
artist field and, as such, sorts first).
.Sp
Adding a slice option:
.Sp
.Vb 3
\& [search [sort products:artist products:title:rf =6-10]
\& [item-field artist] [item-field title]
\& [/search-list]
.Ve
list]
.Sp
will display:
.Sp
.Vb 3
\& Sandy Skoglund Radioactiv The Art Store The Art Store T-Shirt
\& Vincent Van Gogh The Starry Night
\& Vincent Van Gogh Sunflowers
.Ve
.Vb 1
\& Cats
.Ve
If the end value/chunk size exceeds the size of the list, only the
elements that exist will be displayed, starting from the start value.
.Ip "Shopping cart" 4
.IX Item "Shopping cart"
.Vb 3
\& [item [sort products:price:rn]
\& [item-price] [item-code]
\& [/item-list]
.Ve
list]
.Sp
will display the items in the shopping cart sorted on their price,
with the most expensive shown first. \s-1NOTE:\s0 This is based on the
database field and doesn't take quantity price breaks or discounts
into effect. Modifier values or quantities cannot be sorted.
.Ip "Complete database contents" 4
.IX Item "Complete database contents"
.Vb 3
\& [tag each pro [sort products:category products:title]
\& [loop-field category] [loop-field title]
\& [/tag]
.Ve
ucts]
.Sp
A two level sort that will sort products based first on their
category, then on their title within the category.
.PP
Note that large lists may take some time to sort. If a product
database contains many thousands of items, using the [tag each
products] sort is not recommended unless planning on caching or
statically building pages.
.SH "Shipping"
.IX Header "Shipping"
Interchange has a powerful custom shipping facility that performs \s-1UPS\s0
and other shipper lookups, as well as a flexible rule-based facility
for figuring cost by other methods.
.Sh "Shipping Cost Database"
.IX Subsection "Shipping Cost Database"
The shipping cost database (located in ProductDir/shipping.asc) is a
tab-separated \s-1ASCII\s0 file with eight fields: code, text description,
criteria (quantity or weight, for example), minimum number, maximum
number, and cost, query, and options. None of the fields are
case-sensitive.
.PP
To define the shipping database in a catalog configuration file, set
the Variable \s-1MV_SHIPPING\s0 to what would be its contents.
.PP
To set the file to be something other than shipping.asc in the
products directory, set the Special directive:
.PP
.Vb 1
\& Special shipping.asc /home/user/somewhere/shipping_defs
.Ve
There are two styles of setting which can be mixed in the same file.
The first is line-based and expects six or more TAB-separated fields.
They would look like:
.PP
.Vb 1
\& default No shipping weight 0 99999999 0
.Ve
upsg \s-1UPS\s0 Ground weight 0 0 e Nothing to ship!
upsg \s-1UPS\s0 Ground weight 0 150 u Ground [default zip 98366] 3.00
upsg \s-1UPS\s0 Ground weight 150 999999 e @@TOTAL@@ lbs too heavy for \s-1UPS\s0
.PP
The second is a freeform method with a mode: Description text
introducing the mode line. The special encoding is called out by
indented parameters. The below is identical to the above:
.PP
.Vb 5
\& upsg: UPS Ground
\& criteria weight
\& min 0
\& max 0
\& cost e Nothing to ship!
.Ve
.Vb 7
\& min 0
\& max 150
\& cost u
\& table 2ndDayAir
\& geo zip
\& default_geo 98366
\& adder 3
.Ve
.Vb 3
\& min 150
\& max 999999
\& cost e @@TOTAL@@ lbs too heavy for UPS
.Ve
The second format has several advantages. Multiple lines can be
spanned with the <{country} eq 'US';
\& return 'weight' if ! $Values->{country};
\& # Return blank, don't want UPS
\& return '';
\& [/perl]
\& EOF
.Ve
The definable fields are, in order, for the tab-separated format:
.Ip "\s-1MODE\s0" 4
.IX Item "MODE"
The unique identifier for that shipping method. It may be repeated as
many times as needed.
.Ip "\s-1DESCRIPTION\s0" 4
.IX Item "DESCRIPTION"
Text to describe the method (can be accessed on a page with the
[shipping-description] element).
.Ip "\s-1CRITERIA\s0" 4
.IX Item "CRITERIA"
Whether shipping is based on weight, quantity, price, etc. Valid
Interchange tags can be placed in the field to do a dynamic lookup. If
a number is returned, that is used as the accumulated criteria. That
is, the total of weight, quantity, or price as applied to all items in
the shopping cart.
.Sp
See Criteria Determination below.
.Ip "\s-1MINIMUM\s0" 4
.IX Item "MINIMUM"
The low bound of quantity/weight/criteria this entry applies to.
.Ip "\s-1MAXIMUM\s0" 4
.IX Item "MAXIMUM"
The high bound of quantity/weight/criteria this entry applies to. The
first found entry is used in case of ties.
.Ip "\s-1COST\s0" 4
.IX Item "COST"
The method of developing cost. It can be a number which will be used
directly as the shipping cost, or a function, determined by a single
character at the beginning of the field:
.Sp
.Vb 4
\& f Formula (ITL tags OK, evaluated as x Multiplied by a number
\& [uA-Z] UPS-style lookup
\& m Interchange chained cost lookup (all items summed together)
\& i Interchange chained cost lookup (items summed individually)
.Ve
Perl)
.Ip "\s-1NEXT\s0" 4
.IX Item "NEXT"
The next field supplies an alternative shipping mode to substitute
if the cost of the current one is zero.
.Ip "\s-1ZONE\s0" 4
.IX Item "ZONE"
The \s-1UPS\s0 zone that is being defined.
.Ip "\s-1QUERY\s0" 4
.IX Item "QUERY"
Interchange tags which will return a \s-1SQL\s0 query to select lines
matching this specification. The current mode is replaced with this
selection. If there is a query parameter of ?, it will be replaced
with the mode name.
.Ip "\s-1QUAL\s0" 4
.IX Item "QUAL"
The geographic qualification (if any) for this mode.
.Ip "\s-1PERL\s0" 4
.IX Item "PERL"
Perl code that is read and determines the criterion, not the cost. Use
the cost option with \*(L"f\*(R" as the prelim to supply Perl code to
determine cost.
.Ip "\s-1TOTAL\s0" 4
.IX Item "TOTAL"
Set to the accumulated criterion before passing to Perl.
.Ip "\s-1OPT\s0" 4
.IX Item "OPT"
Used to maintain \s-1UPS\s0 and freeform options. Normally these are set by
separate lines in the shipping definition.
.Sh "Criteria Determination"
.IX Subsection "Criteria Determination"
The criteria field varies according to whether it is the first field
in the shipping file exactly matching the mode identifier. In that
case, it is called the main criterion. If it is in subsidiary shipping
lines matching the mode (with optional appended digits), it is called
a qualifying criterion. The difference is that the main criterion
returns the basis for the calculation (i.e., weight or quantity),
while the qualifying criterion determines whether the individual line
may match the conditions.
.PP
The return must be one of:
.Ip "quantity" 4
.IX Item "quantity"
The literal value quantity as the main criterion will simply count the
number of items in the shopping cart and return it as the accumulated
criteria. If using a database table field named quantity, use the
table::field notation.
.Ip "o or

::" 4
.IX Item " or

::"
A valid database field (column) name as main criterion will cause the
number of items in the shopping cart to be multiplied by the value of
the field for each item to obtain the accumulated criteria. If the
table is not supplied, defaults to the first ProductFiles table.
.Ip "o n.nn" 4
.IX Item "n.nn"
Where \fBn.nn\fR is any number, it will be directly used as the
accumulated criteria. This can be effectively returned from a Perl
subroutine or Interchange [calc][item-list] ... [/item-list][/calc]
to create custom shipping routines.
.PP
\&\fB\s-1IMPORTANT\s0 \s-1NOTE:\s0 \fRThe above only applies to the first field that
matches the shipping mode exactly. Following criteria fields contain
qualifier matching strings.
.Sh "Shipping Calculation Modes"
.IX Subsection "Shipping Calculation Modes"
There are eight ways that shipping cost may be calculated. The method
used depends on the first character of the cost field in the
shipping database.
.Ip "N.NN (digits)" 4
.IX Item "N.NN (digits)"
If the first character is a digit, a number is assumed and read
directly as the shipping cost.
.Ip "e" 4
.IX Item "e"
If the first character is an e, a cost of zero is returned and an
error message is placed in the session value ship_message (i.e.,
[data session ship_message] or \f(CW$Session\fR->{ship_message}).
.Ip "f" 4
.IX Item "f"
If the character f is the first, Interchange will first interpret
the text for any Interchange tags and then interpret the result as a
formula. It is read as Perl code; the entire set of Interchange
objects may be referenced with the code.
.Ip "i" 4
.IX Item "i"
Specifies a chained shipping lookup which will be applied to each item
in the shopping cart.
.Ip "m" 4
.IX Item "m"
Specifies a chained shipping lookup which will be applied to the
entire shopping cart.
.Ip "u" 4
.IX Item "u"
Calls the UPS-style lookup. Can pre-define as many as desired. Though
if want to do the hundreds available, it is best done on-the-fly.
.Ip "x" 4
.IX Item "x"
If an x is first, a number is expected and is applied as a fixed
multiplier for the accumulated criterion (@@TOTAL@@).
.Ip "A-Z" 4
.IX Item "A-Z"
If the first character is a capital letter, calls one of the 26
secondary UPS-style lookup zones. (Deprecated now that zones can be
named directly).
.Sh "How Shipping is Calculated"
.IX Subsection "How Shipping is Calculated"
.Ip "1." 4
The base code is selected by reading the value of mv_shipmode in
the user session. If it has not been explicitly set, either by means
of the DefaultShipping directive or by setting the variable on a form
(or in an order profile), it will be default.
.Sp
The mv_shipmode must be in the character class [A-Za-z0\-9_]. If there
are spaces, commas, or nulls in the value, they will be read as
multiple shipping modes.
.RS 4
.Ip "" 8
The criterion field is found. If it is quantity, it is the total
quantity of items on the order form. If it is any other name, the
criterion is calculated by multiplying the return value from the
product database field for each item in the shopping cart, multiplied
by its quantity. If the lookup fails due to the column or row not
existing, a zero cost will be returned and an error is sent to the
catalog error log. If a number is returned from an Interchange tag,
that number is used directly.
.Sp
Entries in the shipping database that begin with the same string as
the shipping mode are examined. If none is found, a zero cost is
returned and an error is sent to the catalog error log.
.RE
.RS 4
.RE
.PP
\&\fBNote: \fRThe same mode name may be used for all lines in the same
group, but the first one will contain the main criteria.
.Ip "2." 4
The value of the accumulated criteria is examined. If it falls within
the minimum and maximum, the cost is applied.
.Ip "3." 4
If the cost is fixed, it is simply added.
.Ip "4." 4
If the cost field begins with an x, the cost is multiplied by the
accumulated criterion, i.e., price, weight, etc.
.Ip "5." 4
If the cost field begins with f, the formula following is applied.
Use @@TOTAL@@ as the value of the accumulated criterion.
.Ip "6." 4
If the cost field begins with u or a single letter from A-Z, a
UPS-style lookup is done.
.Ip "7." 4
If the cost field begins with s, a Perl subroutine call is made.
.Ip "8." 4
If the cost field begins with e, zero cost is returned and an error
placed in the session \fBship_message\fR field, available as [data
session ship_message].
.PP
Here is an example shipping file using all of the methods of
determining shipping cost.
.PP
\&\fBNote: \fRThe columns are lined up for reading convenience. The actual
entries should have \fBone\fR tab between fields.
.PP
.Vb 1
\& global Option n/a 0 0 g PriceDivide
.Ve
rpsg \s-1RPS\s0 quantity 0 0 R \s-1RPS\s0 products/rps.csv
rpsg \s-1RPS\s0 quantity 0 5 7.00
rpsg \s-1RPS\s0 quantity 6 10 10.00
rpsg \s-1RPS\s0 quantity 11 150 x .95
.PP
usps \s-1US\s0 Post price 0 0 0
usps \s-1US\s0 Post price 0 50 f 7 + (1 * @@TOTAL@@ / 10)
usps \s-1US\s0 Post price 50 100 f 12 + (.90 * @@TOTAL@@ / 10)
usps \s-1US\s0 Post price 100 99999 f @@TOTAL@@ * .05
.PP
upsg \s-1UPS\s0 weight [value state] 0 0 e Nothing to ship.
upsg \s-1UPS\s0 \s-1AK\s0 \s-1HI\s0 0 150 u upsg [default zip 980] 12.00 round
upsg \s-1UPS\s0 0 150 u Ground [default zip 980] 2.00 round
upsg \s-1UPS\s0 150 9999 e @@TOTAL@@ lb too heavy for \s-1UPS\s0
.PP
upsca \s-1UPS/CA\s0 weight 0 0 c C UPS_Canada products/can.csv
upsca \s-1UPS/CA\s0 weight \-1 \-1 o PriceDivide=0
upsca \s-1UPS/CA\s0 weight 0 150 C upsca [default zip A7G] 5.00
upsca \s-1UPS/CA\s0 weight 150 99999 e @@TOTAL@@ lb too heavy for \s-1UPS\s0
.Ip "global" 4
.IX Item "global"
This is a global option setting, called out by the g at the
beginning. PriceDivide tells the shipping routines to multiply all
shipping settings by the PriceDivide factor, except those explicitly
set differently with the o individual modifier. This allows
currency conversion. (Currently the only option is PriceDivide.)
.Ip "rpsg" 4
.IX Item "rpsg"
If the user selected \s-1RPS\s0, (code rpsg) and the quantity on the order
was 3, the cost of 7.00 from the second rpsg line would be applied. If
the quantity were 7, the next entry from the third rpsg line would be
selected for a cost of 10.00. If the quantity were 15, the last rpsg
would be selected and the quantity of 15 multiplied by 0.95, for a
total cost of 14.25.
.Ip "usps" 4
.IX Item "usps"
The next mode, usps, is a more complicated formula using price as
the criteria. If the total price of all items in the shopping cart
(same as [subtotal] without quantity price breaks in place) is from
1 to 50, the cost will be 7.00 plus 10 percent of the order. If the
total is from 50.01 to 100, the cost will be 12.00 plus 9 percent of
the order total. If the cost is 100.01 or greater, 5 percent of the
order total will be used as the shipping cost.
.Ip "upsg" 4
.IX Item "upsg"
The next, upsg, is a special case. It specifies a \s-1UPS\s0 lookup based
on the store's \s-1UPS\s0 zone and two required values (and two optional
arguments):
.Sp
.Vb 6
\& 1. 2. The zip/postal code of the recipient of which only
\& the first three digits are used.
\& 3. A fixed amount to add to the cost found in the UPS
\& tables (use 0 as a placeholder if specifying roundup)
\& 4. If set to 'round,' will round the cost up to the next
\& integer monetary unit.
.Ve
eight
.Sp
If the cost returned is zero, the reason will be placed as an error
message in the session variable ship_message (available as [data
session ship_message]).
.Sp
\&\s-1UPS\s0 weights are always rounded up if any fraction is present.
.Sp
The routines use standard \s-1UPS\s0 lookup tables. First, the \s-1UPS\s0 Zone file
must be present. That is a standard \s-1UPS\s0 document specific to the
retailer's area that must be obtained from \s-1UPS\s0. It is entered into and
made available to Interchange in TAB-delimited format. (As of March
1997, use the standard .csv file distributed by \s-1UPS\s0 on their Web site
at www.ups.com.) Specify it with the UpsZoneFile directive. It is
usually named something like \s-1NNN\s0.csv, where \s-1NNN\s0 is the first three
digits of the originating zip code. If placed in the products
directory, the directive would look like:
.Sp
.Vb 1
\& UPSZoneFile products/450.csv
.Ve
Second, obtain the cost tables from \s-1UPS\s0 (again, get them from
www.ups.com) and place them into an Interchange database. That
database, its identifier specified with the first argument (Ground in
the example) of the cost specification, is consulted to determine the
\&\s-1UPS\s0 cost for that weight and rate schedule.
.Sp
In the example below, use a database specification like:
.Sp
.Vb 1
\& Database Ground Ground.csv CSV
.Ve
.PP
A simple shipping cost qualification can be appended to a \s-1UPS\s0 lookup.
If any additional parameters are present after the five usual ones
used for \s-1UPS\s0 lookup, they will be interpreted as a Perl subroutine
call. The syntax is the same as if it was encased in the tag [perl]
[/perl], but the following substitutions are made prior to the call:
.PP
.Vb 4
\& @@COST@@ is replaced with whatever the UPS lo @@GEO@@ is replaced with the zip (or other geo code)
\& @@ADDER@@ is replaced with the defined adder
\& @@TYPE@@ is replaced with the UPS shipping type
\& @@TOTAL@@ is replaced with the total weight
.Ve
kup retThe example above also illustrates geographic qualification. If the
value of the form variable state on the checkout form is \s-1AK\s0 or \s-1HI\s0, the
U.S. states Alaska and Hawaii, a \f(CW$10\fR.00 additional charge (over and
above the normal \f(CW$2\fR.00 handling charge) is made. This can also be used
to select on country, product type, or any other qualification that
can be encoded in the file.
.Ip "upsca"
.IX Item "upsca"
The next entry is just like the \s-1UPS\s0 definition except it defines a
different lookup zone file (products/can.csv) and uses a different
database, upsca. It also disables the global PriceDivide option for
itself only, not allowing currency conversion. Otherwise, the process
is the same.
.PP
urned
.PP
Up to 27 different lookup zones can be defined in the same fashion,
allowing for multiple zone files. If one of the cost lines (the last
field) in the shipping.asc file begins with a c, it configures
another lookup zone which must be lettered from A to Z. It takes
the format:
.PP
.Vb 7
\& c X name file* length* multipwhere X is the letter from A-Z. The name is used internally as an
\&identifier and must be present. The optional file is relative to
\&the catalog root (like UpsZoneFile is). If it is not present, the
\&file equal to name in the products directory (ProductDir) will be
\&used as the zone file. If the optional digit length is present,
\&that determines the number of significant digits in the passed
\&postal/geo code.
.Ve
When the optional multiplier is present, the weight is multiplied
by it before doing the table lookup. This allows shipping weights in
pounds or kilograms to be adapted to a table using the opposite as the
key. Remember, the match on weight must be exact, and Interchange
rounds the weight up to the next even unit.
.PP
To define the exact equivalent of the \s-1UPS\s0 lookup zone, do the
following:
.PP
.Vb 1
\& c U UPS products/450.csv 3 1
.Ve
The only difference is that the beginning code to call the lookup is
upper-case U instead of lower-case u.
.PP
lier*
.Sh "More On UPS-Style Lookup"
.IX Subsection "More On UPS-Style Lookup"
The UPS-style lookup uses two files for its purposes, both of which
need to be in a format like \s-1UPS\s0 distributes for \s-1US\s0 shippers.
.PP
The zone file is a file that is usually specific to the originating
location. For \s-1US\s0 shippers shipping to \s-1US\s0 locations, it is named for
the first three digits of the originating zip code with a \s-1CSV\s0
extension. For example, 450.csv.
.PP
It has a format similar to:
.PP
.Vb 1
\& low - high, zone,zone,zone,zone
.Ve
The low entry is the low bound of the geographic location; high
is the high bound. (By geographic location, the zip code is meant.) If
the first digits of the zip code, compared alphanumerically, fall
between the low and high values, that zone is used as the column name
for a lookup in the rate database. The weight is used as the row key.
.PP
The first operative row of the zone file (one without leading quotes)
is used to determine the zone column name. In the \s-1US\s0, it looks
something like:
.PP
.Vb 2
\& Dest. ZIP,Ground,3 Day Select,2nd Day Air,2nd Day Air A.M.,\e
\& Next Day Air Saver,Next Day Air
.Ve
Interchange strips all non-alpha characters and comes up with:
.PP
.Vb 1
\& DestZIP,Ground,3DaySelect,2ndDayAir,2ndDayAirAM,NextDayAirSaver,NextDayAir
.Ve
Therefore, the zone column (shipping type) that would be used for \s-1UPS\s0
ground would be \*(L"Ground,\*(R" and that is what the database should be
named. To support the above, use a shipping.asc line that reads:
.PP
.Vb 1
\& upsg UPS Ground weight 0 150 u Ground [default zip 983]
.Ve
and a catalog.cfg database callout of:
.PP
.Vb 1
\& Database Ground Ground.csv CSV
.Ve
These column names can be changed as long as they correspond to the
identifier of the rate database.
.PP
The rate database is a standard Interchange database. For U.S.
shippers, \s-1UPS\s0 distributes their rates in a fairly standard
comma-separated value format, with weight being the first (or key)
column and the remainder of the columns corresponding to the zone
which was obtained from the lookup in the zone file.
.PP
To adapt other shipper zone files to Interchange's lookup, they will
need to fit the \s-1UPS\s0 \s-1US\s0 format. (Most of the \s-1UPS\s0 international files
don't follow the U.S. format). For example, the 1998 Ohio-US to Canada
file begins:
.PP
.Vb 4
\& Canada Standard Zone Charts from Ohio
\& Locate the zone by cross-referencing the first three
\& characters of the destination Postal Code in the Postal
\& Range column.
.Ve
.Vb 9
\& Postal Range Zone
\& A0A A9Z 54
\& B0A B9Z 54
\& C0A C9Z 54
\& E0A E9Z 54
\& G0A G0A 51
\& G0B G0L 54
\& G0M G0S 51
\& G0T G0W 54
.Ve
It will need to be changed to:
.PP
.Vb 9
\& Destination,canstnd
\& A0A-A9Z, 54
\& B0A-B9Z, 54
\& C0A-C9Z, 54
\& E0A-E9Z, 54
\& G0A-G0A, 51
\& G0B-G0L, 54
\& G0M-G0S, 51
\& G0T-G0W, 54
.Ve
Match it with a canstnd \s-1CSV\s0 database that looks like this:
.PP
.Vb 10
\& Weight,51,52,53,54,55,56
\& 1,7.00,7.05,7.10,11.40,11.45,11.50
\& 2,7.55,7.65,7.75,11.95,12.05,12.10
\& 3,8.10,8.15,8.40,12.60,12.70,12.85
\& 4,8.65,8.70,9.00,13.20,13.30,13.55
\& 5,9.20,9.25,9.75,13.85,13.85,14.20
\& 6,9.70,9.85,10.35,14.45,14.50,14.90
\& 7,10.25,10.40,11.10,15.15,15.15,15.70
\& 8,10.80,10.95,11.70,15.70,15.75,16.35
\& 9,11.35,11.55,12.30,16.40,16.45,17.20
.Ve
It is called out in catalog.cfg with:
.PP
.Vb 1
\& Database canstnd canstnd.csv CSV
.Ve
With the above, a 4\-pound shipment to postal code E5C 4TL would yield
a cost of 13.20.
.Sh "Geographic Qualification"
.IX Subsection "Geographic Qualification"
If the return value in the main criterion includes whitespace, the
remaining information in the field is used as a qualifier for the
subsidiary shipping modes. This can be used to create geographic
qualifications for shipping, as in:
.PP
.Vb 3
\& upsg UPS Ground weight [value state] 0 0 e No items selected
\&upsg UPS Ground AK HI 0 150 u Ground [value zip] 12.00
\&upsg UPS Ground 0 150 u Ground [value zip] 3.00
.Ve
If upsg is the mode selected, the value of the user session
variable state is examined to see if it matches the geographic
qualification on a whole-word boundary. If it is \s-1AK\s0 or \s-1HI\s0, \s-1UPS\s0
Ground with an adder of 12 will be selected. If it \*(L"falls through,\*(R"
\&\s-1UPS\s0 Ground with an adder of 3 will be selected.
.Sh "Handling Charges"
.IX Subsection "Handling Charges"
Additional handling charges can be defined in the shipping file by
setting the form variable mv_handling to a space, comma, or
null-separated set of valid shipping modes. The lookup and charges are
created in the same fashion, and the additional charges are added to
the order. (The user is responsible for displaying the charge on the
order report or receipt with a [shipping handling] tag, or the
like.) All of the shipping modes found in mv_handling will be applied.
If multiple instances are found on a form, the accordingly
null-separated values will all be applied. \s-1NOTE:\s0 This should not be
done in an item-list unless the multiple setting of the variables is
accounted for.
.PP
To only process a handling charge once, do the following:
.PP
.Vb 8
\& [item-list]
\& [if-item-field very_heavy]
\& [perl values]
\& return '' if $Values->{mv_handling} =~ /very_heavy/;
\& return "";
\& [/perl]
\& [/if-item-field]
\& [/item-list]
.Ve
A non-blank/non-zero value in the database field will trigger Perl
code which will only set mv_handling once.
.Sh "Default Shipping Mode"
.IX Subsection "Default Shipping Mode"
If a default shipping mode other than default is desired, enter it
into the DefaultShipping directive:
.PP
.Vb 1
\& DefaultShipping upsg
.Ve
This will make the entry on the order form checked by default when the
user starts the order process, if it is put in the form:
.PP
.Vb 1
\&
.Ve
To force a choice by the user, make mv_shipmode a required form
variable (with RequiredFields or in an order profile) and set
DefaultShipping to zero.
.SH "User Database"
.IX Header "User Database"
Interchange has a user database function which allows customers to
save any pertinent values from their session. It also allows the
setting of database or file access control lists for use in
controlling access to pages and databases on a user-by-user basis.
.PP
The database field names in the user database correspond with the form
variable names in the user session. If there is a column named
address, when the user logs in the contents of that field will be
placed in the form variable address, and will be available for
display with [value address]. Similarly, the database value is
available with [data table=userdb column=address key=username].
.PP
The \s-1ASCII\s0 file for the database will not reflect changes unless the
file is exported with [tag export userdb][/tag]. It is not
advisable to edit the \s-1ASCII\s0 file, as it will overwrite the real data
that is in the \s-1DBM\s0 table. User logins and changes would be lost. Note:
This would not happen with \s-1SQL\s0, but editing the \s-1ASCII\s0 file would have
no effect. It is recommended that the NoImport configuration directive
be set accordingly.
.PP
The field names to be used are not set in concrete. They may be
changed with options. Fields may be added or subtracted at any time.
Most users will choose to keep the default demo fields for simplicity
sake, as they cover most common needs. As distributed in the demo, the
fields are:
.PP
.Vb 38
\& code
\& accounts
\& acl
\& address
\& address_book
\& b_address
\& b_city
\& b_country
\& b_name
\& b_nickname
\& b_phone
\& b_state
\& b_zip
\& carts
\& city
\& country
\& db_acl
\& email
\& email_copy
\& fax
\& fax_order
\& file_acl
\& mv_credit_card_exp_month
\& mv_credit_card_exp_year
\& mv_credit_card_info
\& mv_credit_card_type
\& mv_shipmode
\& name
\& order_numbers
\& p_nickname
\& password
\& phone_day
\& phone_night
\& preferences
\& s_nickname
\& state
\& time
\& zip
.Ve
A few of those fields are special in naming, though all can be changed
via an option. A couple of the fields are reserved for Interchange's
use.
.PP
\&\fBNote: \fRIf not running with \s-1PGP\s0 or other encryption for credit card
numbers, which is never recommended, it is important that the
mv_credit_card_info field be removed from the database.
.PP
The special database fields are:
.PP
.Vb 10
\& accounts Storage for billing accounts book
\& address_book Storage for shipping address book
\& b_nickname Nickname of current billing account
\& carts Storage for shopping carts
\& p_nickname Nickname for current preferences
\& preferences Storage for preferences
\& s_nickname Nickname for current shipping address
\& db_acl Storage for database access control lists
\& file_acl Storage for file access control lists
\& acl Storage for simple integrated access control
.Ve
If not defined, the corresponding capability is not available.
.PP
\&\fBNote: \fRThe fields accounts, address_book, carts, and
preferences should be defined as a \s-1BLOB\s0 type, if using \s-1SQL\s0. This is
also suggested for the acl fields if those lists could be large.
.PP
Reserved fields include:
.PP
.Vb 3
\& code The username (key for the database)
\& password Password storage
\& time Last time of login
.Ve
.Sh "The [userdb ...] Tag"
.IX Subsection "The [userdb ...] Tag"
Interchange provides a [userdb ...] tag to access the UserDB
functions.
.PP
.Vb 18
\& [userdb
\& function=function_name
\& username="username"*
\& assign_username=1
\& username_mask=REGEX*
\& password="password"*
\& verify="password"*
\& oldpass="old password"*
\& crypt="1|0"*
\& shipping="fields for shipping save"
\& billing="fields for billing save"
\& preferences="fields for preferences save"
\& ignore_case="1|0"*
\& force_lower=1
\& param1=value*
\& param2=value*
\& ...
\& ]
.Ve
* Optional
.PP
It is normally called in an mv_click or mv_check setting, as in:
.PP
.Vb 5
\& [set Login]
\& mv_todo=return
\& mv_nextpage=welcome
\& [userdb function=login]
\& [/set]
.Ve
.Vb 5
\&
\&
\& Username
\& Password
\&
.Ve
There are several global parameters that apply to any use of the
userdb functions. Most importantly, by default, the database table
is set to be userdb. If another table name must be used, include a
database=table parameter with any call to userdb. The global
parameters (default in parentheses):
.PP
.Vb 21
\& database Sets user database table (userdb)
\& show Show the return value of certain functions
\& or the error message, if any (0)
\& force_lower Force possibly upper-case database fields
\& to lower case session variable names (0)
\& billing Set the billing fields (see Accounts)
\& shipping Set the shipping fields (see Address Book)
\& preferences Set the preferences fields (see Preferences)
\& bill_field Set field name for accounts (accounts)
\& addr_field Set field name for address book (address_book)
\& pref_field Set field name for preferences (preferences)
\& cart_field Set field name for cart storage (carts)
\& pass_field Set field name for password (password)
\& time_field Set field for storing last login time (time)
\& outboard Set fields that live in another table
\& outboard_key_col Set field providing key for outboard tables
\& expire_field Set field for expiration date (expire_date)
\& acl Set field for simple access control storage (acl)
\& file_acl Set field for file access control storage (file_acl)
\& db_acl Set field for database access control storage (db_acl)
\& indirect_login Log in field if different than real username ('')
.Ve
By default the system \fIcrypt()\fR call will be used to compare the
password. This is best for security, but the passwords in the user
database will not be human readable.
.PP
If no critical information is kept and Interchange administration is
not done via the UserDB capability, use the UserDB directive
(described below) to set encryption off by default:
.PP
.Vb 1
\& UserDB default crypt 0
.Ve
Encryption can still be set on by passing crypt=1 with any call to
a new_account, change_pass, or login call.
.PP
If you are encrypting, and you wish to use \s-1MD5\s0 to encrypt the
passwords, set the md5 parameter:
.PP
UserDB default md5 1
.Sh "Setting Defaults with the UserDB Directive"
.IX Subsection "Setting Defaults with the UserDB Directive"
The UserDB directive provides a way to set defaults for the user
database. For example, to save and recall the scratch variable
tickets in the user database instead of the form variable
tickets, set:
.PP
.Vb 1
\& UserDB default scratch tickets
.Ve
That makes every call to [userdb function=login] equivalent to
[userdb function=login scratch=tickets].
.PP
To override that default for one call only, use [userdb
function=login scratch=\*(L"passes\*(R"].
.PP
To log failed access authorizations, set the UserDB profile
parameter log_failed true:
.PP
.Vb 1
\& UserDB default log_failed 1
.Ve
To disable logging of failed access authorizations (the default), set
the UserDB profile parameter log_failed to 0:
.PP
.Vb 1
\& UserDB default log_failed 0
.Ve
The UserDB directive uses the same key-value pair settings as the
Locale and Route directives. If there are more than one set of
defaults, set them in a hash structure:
.PP
.Vb 7
\& UserDB crypt_case < 'tickets',
\& 'crypt' => '1',
\& 'ignore_case' => '0',
\& }
\& EOF
.Ve
.Vb 7
\& UserDB default < 'tickets',
\& 'crypt' => '1',
\& 'ignore_case' => '1',
\& }
\& EOF
.Ve
\&\fBNote: \fRThe usual here-document caveats apply. The \*(L"\s-1EOF\s0\*(R" must be on a
line by itself with no leading/trailing whitespace.
.PP
The last one to be set becomes the default.
.PP
The option profile selects the set to use. For usernames and
passwords to be case sensitive with no encryption, pass this call:
.PP
.Vb 1
\& [userdb function=new_account profile=case_crypt]
.Ve
The username and password will be stored as typed in, and the password
will be encrypted in the database.
.Sh "User Database Functions"
.IX Subsection "User Database Functions"
The user database features are implemented as a series of functions
attached to the userdb tag. The functions are:
.Ip "login" 4
.IX Item "login"
Active parameters: username, password, crypt, md5, pass_field,
ignore_case, indirect_login
.Sp
Log in to Interchange. By default, the username is contained in the
form variable mv_username and the password in mv_password. If
the login is successful, the session value username ([data
session username]) will be set to the user name. If indirect_login
is used, it should be set to a field name which can be used as a
lookup for the real username. This also causes a new_account
operation to create a user account based on an assigned username, and
assign_username should always be set when using indirect login.
.Sp
This will recall the values of all non-special fields in the user
database and place them in their corresponding user form variables.
.Sp
The CookieLogin directive (catalog.cfg) allows users to save their
username/password in a cookie. Expiration time is set by
SaveExpire, renewed every time they log in. To cause the cookie to
be generated originally, the form variable mv_cookie_password or
mv_cookie_username must be set in the login form. The former causes
both username and password to be saved, the latter just the username.
.Ip "logout" 4
.IX Item "logout"
Log out of Interchange. No additional parameters are needed.
.Ip "new_account" 4
.IX Item "new_account"
Active parameters: username, password, verify, assign_username,
username_mask, ignore_case,indirect_login
.Sp
Create a new account. It requires the username, password, and
verify parameters, which are by default contained in the form
variables mv_username, mv_password, mv_verify respectively.
.Sp
If the assign_username parameter is set, UserDB will assign a
sequential username. The counter parameter can be used to set the
filename (must be absolute), or the default of
CATALOG_DIR/etc/username.counter can be accepted. The first username
will be \*(L"U0001\*(R" if the counter doesn't exist already.
.Sp
If assign_username is used, you can choose to have a
pseudo-username that is different from the real username. (Email
address is commonly used.) The field name is contained in the
indirect_login parameter. When the user logs in this field name
will also be used to find the real username. The value must be unique
in the database or a \*(L"user already exists\*(R" error will be thrown.
.Sp
The ignore_case parameter forces the username and password to lower
case in the database, in effect rendering the username and password
case-insensitive. This is recommended if using email address as a
login.
.Sp
If username_mask is set to a valid Perl regular expression (without
the surrounding / /), then any username containing a matching string
will not be allowed for use. For example, to screen out order numbers
from being used by a random user:
.Sp
.Vb 2
\& [userdb function=new_a username_mask="^[A-Z]*[0-9]"
\& ]
.Ve
count
.Sp
The CookieLogin directive (catalog.cfg) allows users to save their
username/password in a cookie. Expiration time is set by
SaveExpire, renewed every time they log in. To cause the cookie to
be generated originally, the form variable mv_cookie_password or
mv_cookie_username must be set in the login form. The former causes
both username and password to be saved, the latter just the username.
.Sp
To automatically create an account for every order, set the following
in the OrderReport file:
.Sp
.Vb 5
\& [userdb function=new_a username="[value mv_order_number]"
\& password="[value zip]"
\& verify="[value zip]"
\& database="orders"
\& ]
.Ve
count
.Sp
This would be coupled with a login form that asks for order number and
zip code, thereupon allowing the display of the contents of a
transaction database with (presumably updated) order status
information or a shipping company tracking number.
.Ip "change_pass" 4
.IX Item "change_pass"
Active parameters: username, password, verify, oldpass
.Sp
Change the password on the currently logged-in account. It requires
the username, password, verify, and oldpass parameters,
which are by default contained in the form variables mv_username,
mv_password, mv_verify, mv_password_old respectively.
.Ip "set_shipping" 4
.IX Item "set_shipping"
Active parameters: nickname, shipping, ship_field
.Sp
Place an entry in the shipping Address book. For example:
.Sp
.Vb 1
\& [userdb function=set_shipping nickname=Dad]
.Ve
See Address Book below.
.Ip "get_shipping" 4
.IX Item "get_shipping"
Active parameters: nickname, shipping, ship_field
.Sp
Recall an entry from the shipping Address book. For example:
.Sp
.Vb 1
\& [userdb function=get_shipping nickname=Dad]
.Ve
See Address Book below.
.Ip "get_shipping_names" 4
.IX Item "get_shipping_names"
Active parameters: ship_field
.Sp
Gets the names of shipping address book entries and places them in the
variable address_book. By default, it does not return the values.
To have the values returned, set the parameter show to 1, as in:
.Sp
.Vb 3
\& [set name=shipping_nic interpolate=1]
\& [userdb function=get_shipping_names show=1]
\& [/set]
.Ve
names
.Ip "set_billing" 4
.IX Item "set_billing"
Active parameters: nickname, billing, bill_field
.Sp
Place an entry in the billing accounts book. For example:
.Sp
.Vb 1
\& [userdb function=set_billing nickname=discover]
.Ve
See Accounts Book below.
.Ip "get_billing" 4
.IX Item "get_billing"
Active parameters: nickname, billing, bill_field
.Sp
Recall an entry from the billing accounts book. For example:
.Sp
.Vb 1
\& [userdb function=get_billing nickname=visa]
.Ve
See Accounts Book below.
.Ip "save" 4
.IX Item "save"
Saves all non-special form values that have columns in the user
database. If a field is defined as scratch, it retrieves the field
from the Scratch storage area; otherwise from Values. If the field is
one of the outboard fields, it will save it in the outboard table
with the value of outboard_key_col as the key.
.Ip "set_cart" 4
.IX Item "set_cart"
Save the contents of a shopping cart.
.Sp
.Vb 1
\& [userdb function=set_cart nickname=christmas]
.Ve
See Carts below.
.Ip "get_cart" 4
.IX Item "get_cart"
Active parameters: nickname, carts_field, target
.Sp
Recall a saved shopping cart.
.Sp
.Vb 1
\& [userdb function=get_cart nickname=mom_birthday]
.Ve
Setting target saves to a different shopping cart than the default
main cart. The carts_field controls the database field used for
storage.
.Ip "set_acl" 4
.IX Item "set_acl"
Active parameters: location, acl_field, delete
.Sp
Set a simple acl. For example:
.Sp
.Vb 1
\& [userdb function=set_acl location=cartcfg/editcart]
.Ve
This allows the current user to access the page \*(L"cartcfg/editcart\*(R" if
it is access-protected.
.Sp
To delete access, do:
.Sp
.Vb 1
\& [userdb function=set_acl location=cartcfg/editcart delete=1]
.Ve
To display the setting at the same time as setting, use the show
attribute:
.Sp
.Vb 1
\& [userdb function=set_acl location=cartcf/editcart show=1]
.Ve
.Ip "check_acl" 4
.IX Item "check_acl"
Active parameters: location, acl_field
.Sp
Checks the simple access control listing for a location, returning 1
if allowed and the empty string if not allowed.
.Sp
.Vb 6
\& [if type=ex compare="[userdb
\& function=check_acl
\& location=cartcfg/editcart]"
\& ]
\& [page cartcfg/editcart]Edit your cart configuration
\& [/if]
.Ve
licit
.Ip "set_file_acl, set_db_acl" 4
.IX Item "set_file_acl, set_db_acl"
Active parameters: location, mode, db_acl_field, file_acl_field,
delete
.Sp
Sets a complex access control value. Takes the form:
.Sp
.Vb 2
\& [userdb function=set_fi mode=rw
\& location=products/inventory.txt]
.Ve
e_acl
.Sp
where mode is any value to be checked with check_file_acl. As with
the simple \s-1ACL\s0, use delete=1 to delete the location entirely.
.Ip "check_file_acl, check_db_acl" 4
.IX Item "check_file_acl, check_db_acl"
Active parameters: location, mode, db_acl_field, file_acl_field
.Sp
Checks a complex access control value and returns a true/false (1/0)
value. Takes the form:
.Sp
.Vb 2
\& [userdb function=check_ mode=w
\& location=inventory]
.Ve
b_acl
.Sp
where mode is any value to be checked with check_file_acl. It will
return true, if the mode string is contained within the entry for that
location. For example:
.Sp
.Vb 11
\& [if type=ex compare="[userdb
\& function=check_db_acl
\& mode=w
\& location=inventory]"
\& ]
\& [userdb function=set_acl location=cartcfg/edit_inventory]
\& [page cartcfg/edit_inventory]You may edit the inventory database
\& [else]
\& [userdb function=set_acl location=cartcfg/edit_inventory delete=1]
\& Sorry, you can't edit inventory.
\& [/if]
.Ve
licit
.Sh "Address Book"
.IX Subsection "Address Book"
Address_book is a shipping address book. The shipping address book
saves information relevant to shipping the order. In its simplest
form, this can be the only address book needed. By default these form
values are included:
.PP
.Vb 14
\& s_nickname
\& name
\& fname
\& lname
\& address
\& address1
\& address2
\& address3
\& city
\& state
\& zip
\& country
\& phone_day
\& mv_shipmode
.Ve
The first field is always the name of the form variable that contains
the key for the entry. The values are saved with the [userdb
function=set_shipping] tag call, and are recalled with [userdb
function=get_shipping]. A list of the keys available is kept in the
form value address_book, suitable for iteration in an \s-1HTML\s0 select
box or in a set of links.
.PP
To get the names of the addresses, use the get_shipping_names
function:
.PP
.Vb 1
\& [userdb function=get_shipping_names]
.Ve
By default, they are placed in the variable address_book. Here is a
little snippet that builds a select box:
.PP
.Vb 8
\&
\& [userdb function=get_shipping_names]
\& [if value address_book]
\&
\&
\&
.Ve
The same principle works with accounts, carts, and preferences.
.PP
To restore a cart based on the above, put in an mv_check routine:
.PP
.Vb 5
\& [set Recall Shipping]
\& mv_todo=return
\& mv_nextpage=ord/basket
\& [userdb function=get_shipping nickname="[value s_nickname]"]
\& [/set]
.Ve
When the mv_check variable is encountered, the contents of the
scratch variable Recall Shipping are processed and the shipping
address information inserted into the user form values. This is
destructive of any current values of those user session variables, of
course.
.PP
To change the fields that are recalled or saved, use the shipping
parameter:
.PP
.Vb 3
\& [userdb function=get_shipping
\& nickname=city_and_state
\& shipping="city state"]
.Ve
Only the values of the city and state variables will be
replaced.
.Sh "Accounts Book"
.IX Subsection "Accounts Book"
The accounts book saves information relevant to billing the order. By
default these form values are included:
.PP
.Vb 18
\& b_nickname
\& b_name
\& b_fname
\& b_lname
\& b_address
\& b_address1
\& b_address2
\& b_address3
\& b_city
\& b_state
\& b_zip
\& b_country
\& b_phone
\& purchase_order
\& mv_credit_card_type
\& mv_credit_card_exp_month
\& mv_credit_card_exp_year
\& mv_credit_card_info
.Ve
The values are saved with the [userdb function=set_billing] tag
call, and are recalled with [userdb function=get_billing]. A list
of the keys available is kept in the form value accounts, suitable
for iteration in an \s-1HTML\s0 select box or in a set of links.
.Sh "Preferences"
.IX Subsection "Preferences"
Preferences are miscellaneous session information. They include, by
default, the following fields:
.PP
.Vb 5
\& email
\& fax
\& phone_night
\& fax_order
\& email_copy
.Ve
The field p_nickname acts as a key to select the preference set. To
change the values that are included with the preferences parameter:
.PP
.Vb 2
\& [userdb function=set_preferences
\& preferences="email_copy email fax_order fax"]
.Ve
or in catalog.cfg:
.PP
.Vb 1
\& UserDB default preferences "mail_list email fax_order music_genre"
.Ve
.Sh "Carts"
.IX Subsection "Carts"
The contents of shopping carts may be saved or recalled in much the
same fashion. See the Simple demo application ord/basket.html page
for an example.
.Sh "Controlling Page Access With UserDB"
.IX Subsection "Controlling Page Access With UserDB"
Interchange can implement a simple access control scheme with the user
database. Controlled pages must reside in a directory which has a file
named .access that is zero bytes in length. (If it is more than 0
bytes, only the RemoteUser or MasterHost may access files in that
directory.)
.PP
Set the following variables in catalog.cfg:
.PP
.Vb 2
\& Variable MV_USERDB_ACL_TABLE userdb
\& Variable MV_USERDB_ACL_COLUMN acl
.Ve
The \s-1MV_USERDB_ACL_TABLE\s0 is the table which controls access, and
likewise the \s-1MV_USERDB_ACL_TABLE\s0 names the column in that database
which will be checked for authorization.
.PP
The database entry should contain the complete Interchange-style page
name of the page to be allowed. It will not match substrings.
.PP
For example, if the user flycat followed this link:
.PP
.Vb 1
\& Edit
.Ve
Access would be allowed if the contents of the userdb were:
.PP
.Vb 2
\& code acl
\& flycat cartcfg/master_edit
.Ve
and disallowed if it were:
.PP
.Vb 2
\& code acl
\& flycat cartcfg/master_editor
.Ve
Access can be enabled with:
.PP
.Vb 1
\& [userdb function=set_acl location="cartcfg/master_edit"]
.Ve
Access can be disallowed with:
.PP
.Vb 3
\& [userdb function=set_acl
\& delete=1
\& location="cartcfg/master_edit"]
.Ve
Of course, a pre-existing database with the \s-1ACL\s0 values will work as
well. It need not be in the UserDB setup.
.Sh "Using more than one table"
.IX Subsection "Using more than one table"
You can save/retrieve userdb information from more than one table with
the outboard specifier. It is a quoted key-value comma-separated
series of field specifications. For instance, if the billing address
is to be stored in a separate table named \*(L"billing\*(R", you would do:
.PP
.Vb 7
\& UserDB default outboard <{UserDB}{scratch};
\& for(@s_fields) {
\& $Values->{$_} = $Scratch->{$_};
\& }
\& return;
\& [/calc]
.Ve
If the fields in the outboard table use another key besides
username, you can specify the column in the userdb that contains
the key value:
.PP
.Vb 1
\& UserDB default outboard_key_col account_id
.Ve
.SH "Tracking and Back-End Order Entry"
.IX Header "Tracking and Back-End Order Entry"
Interchange allows the entry of orders into a system through one of
several methods. Orders can be written to an \s-1ASCII\s0 file or formatted
precisely for email-based systems. Or they can go directly into an \s-1SQL\s0
or \s-1DBM\s0 database. Finally, embedded Perl allows completely flexible
order entry, including real-time credit card verification and
settlement.
.Sh "\s-1ASCII\s0 Backup Order Tracking"
.IX Subsection "ASCII Backup Order Tracking"
If AsciiTrack is set to a legal file name (based in VendRoot unless
it has a leading \*(L"/\*(R"), a copy of the order is saved and sent in an
email.
.PP
If the file name string begins with a pipe \*(L"|\*(R", a program will be run
and the output \*(L"piped\*(R" to that program. This allows easy back-end
entry of orders with an external program.
.Sh "Database Tracking"
.IX Subsection "Database Tracking"
Once the order report is processed, the order is complete. Therefore,
it is the ideal place to put Interchange tags that make order entries
in database tables.
.PP
A good model is to place a single record in a database summarizing the
order and a series of lines that correspond to each line item in the
order. This can be in the same database table. If the order number
itself is the key for the summary, a line number can be appended to
the order number to show each line of the order.
.PP
The following would summarize a sample order number S00001 for part
number 00\-0011 and 99\-102:
.PP
.Vb 4
\& code order_number part_number quantity price shipping tax
\& S00001 S00001 3 2010 12.72 100.50
\& S00001-1 S00001 00-0011 2 1000 UPS yes
\& S00001-2 S00001 99-102 1 10 UPS yes
.Ve
Fields can be added where needed, perhaps with order status, shipping
tracking number, address, customer number, or other information.
.PP
The above is accomplished with Interchange's [import ....] tag
using the convenient \s-1NOTES\s0 format:
.PP
.Vb 2
\& [set import_status]
\& [import table=orders type=LINE continue=NOTES]
.Ve
.Vb 6
\& code: [value mv_order_number]
\& order_number: [value mv_order_number]
\& quantity: [nitems]
\& price: [subtotal noformat=1]
\& shipping: [shipping noformat=1]
\& tax: [salestax noformat=1]
.Ve
.Vb 1
\& [/import]
.Ve
.Vb 2
\& [item-list]
\& [import table=orders type=LINE continue=NOTES]
.Ve
.Vb 6
\& code: [value mv_order_number]-[item-increment]
\& order_number: [value mv_order_number]
\& quantity: [item-quantity]
\& price: [item-price noformat=1]
\& shipping: [shipping-description]
\& tax: [if-item-field nontaxable]No[else]Yes[/else][/if]
.Ve
.Vb 1
\& [/import][/item-list]
.Ve
.Sh "Order Routing"
.IX Subsection "Order Routing"
Interchange can send order emails and perform custom credit card
charges and/or logging for each item. The Route directive is used to
control this behavior, along with the mv_order_route item
attribute.
.PP
If no Route is in the catalog, Interchange uses a default \*(L"mail out
the order and show a receipt\*(R" model.
.PP
Routes are established with the Route directive, which is similar
to the Locale directive. Each route is like a locale, so that
key-value pairs can be set. Here is an example setting:
.PP
.Vb 6
\& Route mail pgp_key 0x67798115
\& Route mail email orders@akopia.com
\& Route mail reply service@akopia.com
\& Route mail encrypt 1
\& Route mail encrypt_program "/usr/bin/pgpe -fat -q -r %s"
\& Route mail report etc/report_mail
.Ve
\&\fBNote: \fRValues with whitespace in them must be quoted.
.PP
You can also set the route in a valid Perl hash reference string:
.PP
.Vb 10
\& Route mail < '0x67798115',
\& email => 'orders@akopia.com',
\& reply => 'service@akopia.com',
\& encrypt => 1,
\& encrypt_program => q{/usr/bin/gpg -e -a -r '%s' --batch},
\& report => 'etc/report_mail',
\& }
\& EOR
.Ve
This route would be used whenever the \fImail\fR route was called by one
of the three possible methods:
.Ip "route called from master route" 4
.IX Item "route called from master route"
Called via the cascade parameter from the master route. This is the
way that most routes are called in Interchange's the Foundation manpage demo.
These routes treat the order as a whole.
.Ip "route set in item" 4
.IX Item "route set in item"
An item in the shopping cart has mail as the value in the attribute
mv_order_route. This method is item-specific to this item (or
group of items in route mail).
.Ip "route set in the form variable mv_order_route" 4
.IX Item "route set in the form variable mv_order_route"
By setting a value in the mv_order_route form variable, you can
specify one or more routes to run. This is the deprecated method used
in earlier Interchange 4.6.x and Minivend 4 routes. It will still work
fine.
.PP
The last route that is defined is the master route, by convention
named \fImain\fR. Besides setting the global behavior of the routing, it
provides some defaults for other routes. For example, if
encrypt_program is set there, then the same value will be the
default for all routes. Most settings do not fall through.
.PP
The attributes that can be set are:
.Ip "attach" 4
.IX Item "attach"
Determines whether the order report should be attached to the main
order report e-mail. This is useful if certain items must be printed
separately from others, perhaps for \s-1FAX\s0 to a fulfillment house.
.Sp
cascade
.PP
A list of routes which should be pushed on the stack of routes to run,
\&\fIafter all currently scheduled routes are done\fR. \s-1NOTE:\s0 cascades can
cause endless loops, so only one setting is recommended, that being
the main route.
.Ip "commit" 4
.IX Item "commit"
Perl code which should be performed on a route commit.
.Ip "commit_tables" 4
.IX Item "commit_tables"
Tables that are to be pre-opened before running the Perl commit code.
.Ip "counter" 4
.IX Item "counter"
The location of a counter file which should be used instead of
OrderCounter for this route. It will generate a different value for
mv_order_number for the route. This is normally used to obtain
unique order references for multi-vendor routing.
.Ip "credit_card" 4
.IX Item "credit_card"
Determines whether credit card encryption should be done for this
order. Either this or encrypt should always be set.
.Ip "dynamic_routes" 4
.IX Item "dynamic_routes"
If set in the the master manpage route, will cause the the RouteDatabase manpage to be
checked for a route. If it exists, it will be read in and the database
copy used instead of the static copy build at catalog configuration
time. If set in a subsidiary route, that route will be ignored during
catalog.cfg, and dynamic_routes must be active for it to be seen.
.Ip "email" 4
.IX Item "email"
The email address(es) where the order should be sent. Set just like
the MailOrderTo directive, which is also the default.
.Ip "empty" 4
.IX Item "empty"
.PP
This should be set if neither attach or email is set.
.Ip "encrypt" 4
.IX Item "encrypt"
Whether the entire order should be encrypted with the
\&\fBencrypt_program\fR. If credit_card is set, the credit card will
first be encrypted, then the entire order encrypted.
.Ip "encrypt_program" 4
.IX Item "encrypt_program"
The encryption program incantation which should be used. Set
identically to the EncryptProgram directive, except that \f(CW%s\fR will be
replaced with the pgp_key. Default is empty.
.Ip "errors_to" 4
.IX Item "errors_to"
Sets the Errors-To: e-mail header so that bounced orders will go to
the proper address. Default is the same as MailOrderTo.
.Ip "expandable" 4
.IX Item "expandable"
If set in the the master manpage route, route settings will be expanded for \s-1ITL\s0
tags. No effect if the route is not the master.
.Ip "extended" 4
.IX Item "extended"
Extended route settings that take the form of an Interchange option
list; normally a Perl hash reference that will be read. These settings
always overwrite any that currently exist, regardless of the order in
which they are specified. For example:
.Sp
.Vb 1
\& Route main extended { email => 'milton@akopia.com' }
.Ve
.PP
The ultimate setting of email will be milton@akopia.com.
.Ip "increment" 4
.IX Item "increment"
Whether the order number should be incremented as a result of this
result. Default is not to increment, as the order number should
usually be the same for different routes within the same customer
order.
.Ip "individual_track" 4
.IX Item "individual_track"
A directory where individual order tracking files will be placed. The
file name will correspond to the value of mv_order_number. This can
be useful for batching orders via download.
.Ip "individual_track_ext" 4
.IX Item "individual_track_ext"
The extension that will be added to the file name for
individual_track. Must contain a period (.), if that is desired.
.Sp
.Vb 1
\& individual_track_ext .pgp
.Ve
.Ip "individual_track_mode" 4
.IX Item "individual_track_mode"
A number representing the final permission mode for the
individual_track file. Usually expressed in octal:
.Sp
.Vb 1
\& individual_track_mode 0444
.Ve
.Ip "master" 4
.IX Item "master"
If set, this route becomes the master route for supplant,
dynamic_routes, errors_to, and expandable, and supplies the
setting for receipt and the attach report. Switching master
in midstream is unlikely to be successful \*(-- it should certainly be
the first route in a cascade.
.Ip "payment_mode" 4
.IX Item "payment_mode"
If this is set, enables a payment mode for the route. (Payment modes
are also set in the Route directive.)
.Ip "pgp_cc_key" 4
.IX Item "pgp_cc_key"
The \s-1PGP/GPG\s0 key selector that is used to determine which public key is
used for encryption of credit cards only. With \s-1PGP\s0 5 and 6, see
appropriate values by using the command pgpk \-l. For \s-1GPG\s0, use gpg
\&\-\-list-keys. Defaults to the value of the pgp_key manpage.
.Ip "pgp_key" 4
.IX Item "pgp_key"
The \s-1PGP\s0 key selector that is used to determine which public key is
used for encryption. If pgp_cc_key is set, that key will be used
for credit card encryption instead of pgp_key. With \s-1PGP\s0 5 and 6,
see appropriate values by using the command pgpk \-l. For \s-1GPG\s0, use
gpg \-\-list-keys. Defaults to the value of the pgp_key manpage.
.Ip "profile" 4
.IX Item "profile"
The custom order profile which should be performed to check the order
\&\fIprior\fR to actually running the route. If it fails, the route will
not be performed. See OrderProfile and mv_order_profile.
.Ip "receipt" 4
.IX Item "receipt"
The receipt page that should be used for this routing. This only
applies if supplant is set for the route, and that normally would only
be in the default route.
.Ip "report" 4
.IX Item "report"
The report page that should be used for this routing. If attach is
defined, the contents of the report will be placed in a \s-1MIME\s0
attachment in the main order report.
.Ip "reply" 4
.IX Item "reply"
The Reply-To header that should be set. Default is the same as
email.
.Sp
If there are only word characters (A-Za-z0\-9 and underscore), it
describes an Interchange variable name where the address can be found.
.Ip "rollback" 4
.IX Item "rollback"
Perl code which should be performed on a route rollback.
.Ip "rollback_tables" 4
.IX Item "rollback_tables"
Tables that are to be pre-opened before running the Perl rollback
code.
.Ip "supplant" 4
.IX Item "supplant"
Whether the master route should supplant the main order report. If
set, the AsciiTrack operation will use this route and the normal
Interchange order e-mail sequence will not be performed. This is
normally set in the master route.
.Ip "track" 4
.IX Item "track"
The name of a file which should be used for tracking. If the
supplant attribute is set, the normal order tracking will be used
as well.
.Ip "track_mode" 4
.IX Item "track_mode"
A number representing the final permission mode for the track file.
Usually expressed in octal:
.Sp
.Vb 1
\& track_mode 0444
.Ve
.Ip "transactions" 4
.IX Item "transactions"
A list of tables to put in transactions mode at the beginning of the
route. Used to ensure that orders get rolled back if another route
fails.
.Sp
The \fIfirst\fR route to open a table must have this parameter, otherwise
transactions will not work. If any route fails (except ones marked
error_ok) then a rollback will be done on these tables. If all routes
succeed, a commit will be performed at the end of all order routes.
.PP
Individual item routing causes all items labeled with that route to be
placed in a special sub-cart that will be used for the order report.
This means that the [item-list] \s-1LIST\s0 [/item-list] will only contain
those items, allowing operations to be performed on subsets of the
complete order. The [subtotal], [salestax], [shipping],
[handling], and [total-cost] tags are also affected.
.PP
Here is an example of an order routing:
.PP
.Vb 5
\& Route HARD pgp_key 0x67798115
\& Route HARD email hardgoods@akopia.com
\& Route HARD reply service@akopia.com
\& Route HARD encrypt 1
\& Route HARD report etc/report_mail
.Ve
.Vb 3
\& Route SOFT email ""
\& Route SOFT profile create_download_link
\& Route SOFT empty 1
.Ve
.Vb 5
\& Route mail pgp_key 0x67798115
\& Route mail email orders@akopia.com
\& Route mail reply service@akopia.com
\& Route mail encrypt 1
\& Route mail report etc/report_all
.Ve
.Vb 4
\& Route user error_ok 1
\& Route user email email
\& Route user reply service@akopia.com
\& Route user report etc/user_copy
.Ve
.Vb 4
\& Route log empty 1
\& Route log report etc/log_transaction
\& Route log transactions "transactions orderline inventory"
\& Route log track logs/log
.Ve
.Vb 5
\& Route main supplant 1
\& Route main receipt etc/receipt.html
\& Route main master log mail user
\& Route main cascade log mail user
\& Route main encrypt_program "/usr/bin/gpg -e -a r '%s' --batch"
.Ve
This will have the following behavior:
.Ip "Order" 4
.IX Item "Order"
The master order route is \fImain\fR, the last one defined. It cascades
the routes \fIlog\fR, \fImail\fR, and \fIuser\fR, which means they will run in
that order at the completion of the \fImain\fR route. The individual item
routes \s-1HARD\s0 and \s-1SOFT\s0, if applicable, will run before those.
.Ip "Transactions" 4
.IX Item "Transactions"
The route \fIlog\fR specifies the tables that will be put in transaction
mode, in this case transactions orderline, and inventory.
.Ip "Failure" 4
.IX Item "Failure"
All order routes must succeed except \fIuser\fR, which has error_ok
set to 1.
.Ip "Encryption The \fImail\fR order route and the \s-1HARD\s0 order route will be sent by email, and encrypted against different \s-1GPG\s0 key IDs. They will get their encrypt_program setting from the main route." 4
.IX Item "Encryption The mail order route and the HARD order route will be sent by email, and encrypted against different GPG key IDs. They will get their encrypt_program setting from the main route."
.PP
To set the order routing for individual items, some method of
determining their status must be made and the mv_order_route
attribute must be set. This could be set at the time of the item being
placed in the basket, or have a database field called goods_type
set to the appropriate value. The following example uses a Perl
routine on the final order form:
.PP
.Vb 10
\& [perl table=products]
\& my %route;
\& my $item;
\& foreach $item (@{$Items}) {
\& my $code = $item->{code};
\& my $keycode = $Tag->data('products', 'goods_type', $code);
\& $item->{mv_order_route} = $keycode;
\& }
\& return;
\& [/perl]
.Ve
Now the individual items are labeled with a mv_order_route value
which causes their inclusion in the appropriate order routing.
.PP
Upon submission of the order form, any item labeled \s-1HARD\s0 will be
accumulated and sent to the e-mail address hardgoods@akopia.com,
where the item will be pulled from inventory and shipped.
.PP
Any item labeled \s-1SOFT\s0 will be passed to the order profile
create_download_link, which will place it in a staging area for
customer download. (This would be supported by a link on the receipt,
possibly by reading a value set in the profile).
.SH "SSL Support"
.IX Header "SSL Support"
Interchange has several features that enable secure ordering via \s-1SSL\s0
(Secure Sockets Layer). Despite their mystique, \s-1SSL\s0 servers are
actually quite easy to operate. The difference between the standard
\&\s-1HTTP\s0 server and the \s-1SSL\s0 \s-1HTTPS\s0 server, from the standpoint of the user,
is only in the encryption and the specification of the \s-1URL\s0; https:
is used for the \s-1URL\s0 protocol specification instead of the usual http:
designation.
.PP
\&\fB\s-1IMPORTANT\s0 \s-1NOTE:\s0 \fRInterchange attempts to perform operations
securely, but no guarantees or warranties of any kind are made! Since
Interchange comes with source code, it is fairly easy to modify the
program to create security problems. One way to minimize this
possibility is to record digital signatures, using \s-1MD5\s0 or \s-1PGP\s0 or
GnuPG, of interchange, interchange.cfg, and all modules included
in Interchange. Check them on a regular basis to ensure they have not
been changed.
.PP
Interchange uses the SecureURL directive to set the base \s-1URL\s0 for
secure transactions, and the VendURL directive for normal
non-secure transactions. Secure URLs can be enabled for forms through
a form action of [process secure=1]. An individual page can be
displayed via \s-1SSL\s0 with [page href=mvstyle_pagename secure=1]. A
certain page can be set to be always secure with the AlwaysSecure
catalog.cfg directive.
.PP
Interchange incorporates additional security for credit card numbers.
The field mv_credit_card_number will not ever be written to disk.
.PP
To enable automated encryption of the credit card information, the
directive CreditCardAuto needs to be defined as Yes.
EncryptProgram also needs to be defined with some value, one which
will, hopefully, encrypt the number. \s-1PGP\s0 is now recommended above all
other encryption program. The entries should look something like:
.PP
.Vb 2
\& CreditCardAuto Yes
\& EncryptProgram /usr/bin/pgpe -fat -r sales@company.com
.Ve
See CreditCardAuto for more information on how to set the form
variables.
.SH "Frequently Asked Questions"
.IX Header "Frequently Asked Questions"
.Sh "I can't get \s-1SQL\s0 to work: Undefined subroutine &Vend::Table::DBI::create ..."
.IX Subsection "I can't get SQL to work: Undefined subroutine &Vend::Table::DBI::create ..."
This probably means one of the following:
.Ip "No \s-1SQL\s0 database." 4
.IX Item "No SQL database."
Interchange doesn't include a \s-1SQL\s0 database. You must select one and
install it.
.Ip "No \s-1DBI\s0." 4
.IX Item "No DBI."
You must install Perl's \s-1DBI\s0 module before using Interchange with \s-1SQL\s0.
You can see where to get it at http://www.cpan.org, or try:
.Sp
.Vb 1
\& perl -MCPAN -e 'install DBI'
.Ve
.Ip "No \s-1DBD\s0." 4
.IX Item "No DBD."
You must install the specific Perl \s-1DBD\s0 module for your database before
using Interchange with \s-1SQL\s0. You can see where to get it at
http://www.cpan.org, or try:
.Sp
.Vb 1
\& perl -MCPAN -e 'install DBD::XXXXX'
.Ve
where \s-1XXXXX\s0 is the name of your module. Some of them are:
.Sp
.Vb 12
\& DB2
\& Informix
\& Ingres
\& ODBC
\& Oracle
\& Pg
\& Solid
\& Sybase
\& Unify
\& XBase
\& mSQL
\& mysql
.Ve
dabas
.Sp
If you can't make this script run without error:
.Sp
.Vb 1
\& us use DBD::XXXXX;
.Ve
.Vb 1
\& DBI;
.Ve
Then you don't have one of the above, and Interchange can't use an \s-1SQL\s0
database until you get one installed.
.Ip "I don't like the column types that Interchange defines!" 4
.IX Item "I don't like the column types that Interchange defines!"
They can be changed. See the foundation/dbconf/mysql directory for
some examples under MySQL.
.Ip "I change the \s-1ASCII\s0 file, but the table is not updated. Why?" 4
.IX Item "I change the ASCII file, but the table is not updated. Why?"
Interchange writes an empty file \s-1TABLE\s0.sql (where \s-1TABLE\s0 is the name
of the table). When this is present, Interchange will never update the
table from disk.
.Sp
Also, if you have changed the field names in the file, you must
restart the catalog (Apply Changes) before they will be picked up.
.Ip "Why do I even need an \s-1ASCII\s0 file?" 4
.IX Item "Why do I even need an ASCII file?"
Interchange wants some source for column names initially. If you don't
want to have one, just create a \s-1TABLENAME\s0.sql file in the
products directory. For example, if you have this:
.Sp
.Vb 1
\& Database products products.txt dbi:mysql:test_minivend
.Ve
Then create a file products/products.sql.
.Sp
\&\eFor:
.Sp
.Vb 1
\& Database pricing pricing.txt dbi:mysql:test_minivend
.Ve
Create a file products/pricing.sql. .
.Ip "Interchange overwrites my predefined table!" 4
.IX Item "Interchange overwrites my predefined table!"
Yes, it will if you don't create a file called \s-1TABLENAME\s0.sql, where
\&\s-1TABLENAME\s0 is the name of the Interchange table. If you want this to
happen by default, then set NoImport \s-1TABLENAME\s0.
.Sh "How can I use Interchange with Microsoft Access?"
.IX Subsection "How can I use Interchange with Microsoft Access?"
Though Interchange has \s-1ODBC\s0 capability, the Microsoft Access \s-1ODBC\s0
driver is not a network driver. You cannot access it on a \s-1PC\s0 from your
\&\s-1ISP\s0 or \s-1UNIX\s0 system.
.PP
However, you can turn it around. Once you have created a MySQL or
other \s-1SQL\s0 database on the \s-1UNIX\s0 machine, you may then obtain the
Windows \s-1ODBC\s0 driver for the database (MySQL has a package called
myODBC) and use the \s-1UNIX\s0 database as a data source for your PC-based
database program.
.PP
Here is a quick procedure that might get you started:
.Ip "\(bu" 4
Get MySQL from:
.Sp
.Vb 1
\& http://www.mysql.com/
.Ve
Install it on your \s-1UNIX\s0 box. On \s-1LINUX\s0, it is as easy as getting the
\&\s-1RPM\s0 distribution:
.Sp
.Vb 1
\& http://www.mysql.com/rpm/
.Ve
You install it by typing, as root, rpm \-i mysql-3.XX.XX.rpm. If you
are not root, you will have to build the source distribution.
.Ip "\(bu" 4
To avoid permissions problems for your testing, stop the MySQL daemon
and allow global read-write access with:
.Sp
.Vb 1
\& mysqladmin sh safe_mysqld --skip-grant-tables &
.Ve
tdown
.Sp
Obviously, you will want to study MySQL permissions and set up some
security pretty quickly. It has excellent capability in that area, and
the \s-1FAQ\s0 will help you get over the hurdles.
.Ip "\(bu" 4
Set up a database for testing on the \s-1UNIX\s0 machine:
.Sp
.Vb 1
\& mysqladmin create tes mysql test_odbc
.Ve
_odbc
.Sp
Make an \s-1SQL\s0 query to set up a table, for example:
.Sp
.Vb 1
\& mysql> create table test_me ( code char(20), testdata char( Query OK, 0 rows affected (0.29 sec)
.Ve
.Vb 2
\& mysql> insert into test_me VALUES ('key1', 'data1');
\& Query OK, 1 rows affected (0.00 sec)
.Ve
.Vb 2
\& mysql> insert into test_me VALUES ('key2', 'data2');
\& Query OK, 1 rows affected (0.00 sec)
.Ve
.Vb 1
\& mysql>
.Ve
0) );
.Ip "\(bu" 4
Get and install myODBC, also from the MySQL site:
.Sp
.Vb 1
\& http://www.mysql.com/
.Ve
You install this package on your Windows 95 or \s-1NT\s0 box. It is a simple
setup.exe process which leads you to the control panel for setting up
an \s-1ODBC\s0 data source. Set up a data source named test_odbc that
points to the database test_odbc on the \s-1UNIX\s0 box. You will need to
know the host name and the port (usually 3306).
.Ip "\(bu" 4
With Microsoft Access, you can then open a blank database and select:
File/Get External Data/Link Tables. Select File Type of '\s-1ODBC\s0
databases' and the proper data source, and you should have access to
the database residing on the \s-1UNIX\s0 side.
.PP
.Vb 1
\& ________________________________________
.Ve
Copyright 2002\-2004 Interchange Development Group. Copyright 2001\-2002
Red Hat, Inc. Freely redistributable under terms of the \s-1GNU\s0 General
Public License.