Полезная информация

Interchange Databases



Table of Contents


1. Interchange Databases

Interchange is the industry's most widely distributed and implemented open source e-commerce platform. This document discusses databases, SQL support, the Interchange search engine, frequently asked questions, and usertags.

1.1. A Typical User Session

The customer discovers a merchant site using the Interchange catalog with a search engine, a link from another page, or a click-through from a banner ad. He or she clicks the link, which is a URL pointing to the Interchange CGI link program (generically called VLINK or TLINK). The link calls the Interchange server through a socket. The Interchange server detects the path information from the link, and loads the corresponding page.

The displayed page contains links to either locate or order items from the merchant's catalog. When the customer clicks a link to purchase an item, Interchange searches the product database, finds the item, and places it in the customer's shopping cart. (Each user has a separate shopping cart, which is attached to their session.)

Once the customer decides to purchase the items in their shopping cart, he or she checks out by completing a form with their name, address, payment information, shipping information, and any other information that may be required. Interchange has the capacity to compute sales taxes and shipping cost. The order is then submitted. The customer's payment can be taken online by real-time electronic payment, or their order information may be transmitted using encrypted email or FAX to a processing center.

The customer's order is saved to a file or to a database table as backup. In the case of fully automated systems, it can be sent directly to an order entry program or database link.

All of these operations are fully configurable. The Interchange demo program includes a sample store called Construct Something. Some users have actually customized the text and images inside Construct Something's sample catalogs, changed the database entries, and opened their store. Most users, however, have built their catalogs using their own distinctive look and feel.


2. Databases

As with most powerful shopping cart programs, Interchange is all about databases. Interchange can use GDBM, DB_File, SQL, LDAP, or in-memory database formats. In most cases, these different database formats should operate the same when called by Interchange's access methods.


Note: No other database besides Interchange's internal one is required.

Keeping a database in an SQL manager makes it easier to integrate Interchange with other tools. Interchange can be used to maintain a spreadsheet containing product information by only modifying the file products.txt as needed. References to SQL, DBI, and DBD can be ignored.

2.1. Text Source Files

Interchange reads delimited text files to obtain data. However, the text files are not the database. They are the source information for the database tables.

Note the following configuration directive:

   Database  products  products.txt   TAB

This says that 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:

GDBM

            NoImport  products
            Database products IMPORT_ONCE 1

DB_File

           Database  products  DB_FILE   1

DBI/SQL

In-Memory

           Database   products   MEMORY   1

2.2. Interchange Database Operation


Note: In the following descriptions, the following terms are used somewhat interchangeably:

key, code

field, column

table, database

If necessary, Interchange reads the data to place in tables from standard ASCII-delimited files. All of these ASCII source files are kept in the products directory, normally products in the catalog directory (where catalog.cfg is located). The ASCII files can have ^M (carriage return) characters if desired, but must have a new line character at the end of the line to work. NOTE: Mac users uploading files must use ASCII mode, not binary mode.

Interchange's default ASCII delimiter is TAB.


Note: The items must be separated by a single delimiter. The items are lined up for reading convenience.

TAB

            code    description             price   image
            SH543   Men's fine cotton shirt 14.95   shirts.jpg

PIPE

            code|description|price|image
            SH543|Men's fine cotton shirt|14.95|shirts.jpg

CSV

            "code","description","price","image"
            "SH543","Men's fine cotton shirt","14.95","shirts.jpg"


Note: Using the default TAB delimiter is highly recommended, if searching the ASCII source file of the database is planned. PIPE works fairly well, but CSV delimiter schemes cause problems with searching.


IMPORTANT NOTE: Field names are usually case-sensitive. Unless there is consistency in the names, there will be problems. All lower or all upper case names are recommended.

2.3. The Product Database

Each product being sold should be given a product code, usually referred to as SKU, 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.

Field names can be case-sensitive depending on the underlying database type. Unless there are fields with the names "description" and "price" field, set the PriceField and DescriptionField directives to use the [item-price] and [item-description] tags.

The product code, or SKU, must be the first field in the line, and must be unique. Product codes can contain the characters A-Za-z0-9, 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.

The words should be separated by one of the approved delimiting schemes (TAB, PIPE, or CSV), and are case-sensitive in some cases. If the case of the "description" or "price" fields have been modified, the PriceField and DescriptionField directives must be appropriately set.


Note: CSV is not recommended as the scheme for the products database. It is much slower than TAB- or PIPE-delimited, and dramatically reduces search engine functionality. No field-specific searches are possible. Using CSV for any small database that will not be searched is fine.


IMPORTANT NOTE: The 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 (_).

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).

When the database table source file (i.e., products.txt) changes after import or edit, a DBM database is re-built upon the next user access. No restart of the server is necessary.

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.

2.4. Multiple Database Tables

Interchange can manage an unlimited number of arbitrary database tables. They use the TAB delimiter by default, but several flexible delimiter schemes are available. These are defined by default:

   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 \n%%\n combination, records by
               \n%%%\n (where \n is a newline). Watch those carriage
               returns!
   Type 4      CSV
   Type 5      PIPE
   Type 6      TAB
   Type 7      reserved
   Type 8      SQL

The databases are specified in Database directives, as:

   Database    arbitrary arbitrary.csv CSV

This specifies a Type 4 database, the ASCII version of which is located in the file arbitrary.csv, and the identifier it will be accessed under in Interchange is "arbitrary." The DBM file, if any, will be created in the same directory if the ASCII file is newer, or if the DBM file does not exist. The files will be created as arbitrary.db or arbitrary.gdbm, depending on DBM type.

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. NOTE: Use of lower-case letters is strongly recommended.

If one of the first six types is specified, the database will automatically be built in the default Interchange DB style. The type can be specified with DB_FILE, GDBM, or MEMORY, if the type varies from that default. They will coexist with an unlimited number of DBI databases of different types.

In addition to the database, the session files will be kept in the default format, and are affected by the following actions.

The order of preference is:

GDBM

            perl -e 'require GDBM_File and print "I have GDBM.\n"'

DB_File (Berkeley DB)

            perl -e 'require DB_File and print "I have Berkeley DB.\n"'
            # csh or tcsh
            setenv MINIVEND_DBFILE 1
        
            # sh, bash, or ksh
            MINIVEND_DBFILE=1 ; export MINIVEND_DBFILE
            Database arbitrary  DB_FILE  1

In-memory

            Database arbitrary  MEMORY  1


Note: The use of memory databases is not recommendeded.

2.5. 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:

                                      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)

Some SQL databases have reserved words which cannot be used as field names; Interchange databases do not have this restriction.

For easy HTML 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="S/KU"].

2.6. Database Attributes

Especially in SQL databases, there are certain functions that can be set with additional database attributes. For text import, the CONTINUE extended database import attribute allows additional control over the format of imported text.


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

CONTINUE

          Database products products.txt  TAB
          Database products CONTINUE      DITTO
          code     price     description
          00-0011  500000    The Mona Lisa, one of the worlds great masterpieces.
                             Now at a reduced price!


Note: Fields are separated by tabs, formatted for reading convenience.

          Database products products.txt  LINE
          Database products CONTINUE      LINE
            code
            price
            description
        
            00-0011
            500000
            The Mona Lisa, one of the worlds great masterpieces.
            Now at a reduced price!
        
            00-0011a
            1000
            A special frame for the Mona Lisa.
          Database products products.txt  LINE
          Database products CONTINUE      NOTES
            code
            title
            price
            image
            description ~
            size
            color
        
            title: Mona Lisa
            price: 500000
            code: 00-0011
            image: 00-0011.jpg
        
            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
        
            Extra large 1.00 extra.
            ~

EXCEL

            Database products EXCEL 1

2.7. 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.

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 INDEX field modifier is specified, Interchange will build the index upon database import:

  Database  products  products.txt   TAB
  Database  products  INDEX          title

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:

   American Gothic   19-202
   Mona Lisa         00-0011
   Sunflowers        00-342
   The Starry Night  00-343

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.

Another option is c, which stands for "comma index." To index on comma-separated sub-fields within a field, use the :c option:

  Database  products  products.txt   TAB
  Database  products  INDEX          category:c

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.

The fast binary search is described in greater detail in THE SEARCH ENGINE below.

2.8. 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 DBM, use the MEMORY modifier:

  Database  country  country.asc   TAB
  Database  country  MEMORY        1

Obviously, large tables will use a great deal of memory, and the data will need to be re-imported from the ASCII source file at every catalog reconfiguration or Interchange restart. The big advantage of using MEMORY 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.

The MEMORY modifier forces IMPORT_ONCE.

2.9. IMPORT_ONCE

The IMPORT_ONCE modifier tells Interchange not to re-import the database from the ASCII file every time it changes. Normally, Interchange does a comparison of the database file modification time with the ASCII source every time it is accessed, and if the ASCII source is newer it will re-import the file.

IMPORT_ONCE tells it only to import on a server restart or catalog reconfiguration:

  Database  products  products.txt   TAB
  Database  products  IMPORT_ONCE    1

SQL 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.

IMPORT_ONCE is always in effect for MEMORY databases. A catalog reconfiguration is required to force a change.

2.10. 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.

[import table type*] RECORD [/import]

         [import table=table_name
                 file=filename*
                 type=(TAB|PIPE|CSV|%%|LINE)*
                 continue=(NOTES|UNIX|DITTO)*
                 separator=c*]

Import one or more records into a database. The type is any of the valid Interchange delimiter types, with the default being TAB. 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 SQL.

The import type selected need not match the type the database was specified. Different delimiters may be used.

The type of LINE and continue setting of NOTES 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:

   [import table=orders]
            code: [value mv_order_number]
   shipping_mode: [shipping-description]
          status: pending
   [/import]

   [import table=orders]
   shipping_mode: [shipping-description]
   status:        pending
   code:          [value mv_order_number]
   [/import]

The code or key must always be present, and is always named code. If NOTES mode is not used, the fields must be imported in the same order as they appear in the ASCII source file.

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="products/products.txt". If the NoAbsolute directive is set to Yes in minivend.cfg, only relative path names will be allowed.

The [import ....] TEXT [/import] region may contain multiple records. If using NOTES mode, a separator must be used, which, by default, is a form-feed character (^L). See Import Attributes for more information.

2.11. Exporting from a Database

To export an existing database to a file suitable for searching by Interchange, create a page that contains a [tag export ...][/tag] element. Perhaps a better method is to define the same sort of tags in an OrderProfile, and use forms and buttons to access the profile.

2.12. 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:

To enable write control:

   Database   products  WRITE_CONTROL  1

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

   Database   products  READ_ONLY  1

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

   Database   products  WRITE_TAGGED  1

To limit write to certain catalogs, set:

   Database   products  WRITE_CATALOG  simple=0, sample=1

The "simple" catalog will not be able to write, while "sample" 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:

   Database   products  WRITE_ALWAYS  1

The default behavior of SQL datbases is equivalent to WRITE_ALWAYS, while the default for GDBM_File, DB_File, and Memory databases is equivalent to:

   Database   products  WRITE_CONTROL 1
   Database   products  WRITE_TAGGED  1

2.13. Global Databases

If a database is to be available to all catalogs on the Interchange server, it may be defined in minivend.cfg. Any catalog running under that server will be able to use it. It is writable by any catalog unless WRITE_CONTROL is used.


3. SQL Support

Interchange can use any of a number of SQL databases through the powerful Perl DBI/DBD access methods. This allows transparent access to any database engine that is supported by a DBD module. The current list includes mSQL, MySQL, Solid, PostgreSQL, Oracle, Sybase, Informix, Ingres, Dbase, DB2, Fulcrum, and others. Any ODBC (with appropriate driver) should also be supported.

No SQL 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 SQL or DBI/DBD. Sufficient familiarity is assumed.

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 SQL distribution. But, if given a blank database and the permission to read and write it, Interchange can import ASCII files and bootstrap from there.

3.1. SQL Support via DBI

The configuration of the DBI 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 arbitrary as a DBI database, sets the data source (DSN) to an appropriate value for an mSQL database named minivend on port 1114 of the local machine:

   Database arbitrary arbitrary.asc SQL
   Database arbitrary DSN           dbi:mSQL:minivend:localhost:1114

As a shorthand method, include the DSN as the type:

   Database arbitrary arbitrary.asc dbi:mSQL:minivend:localhost:1114

Supported configuration attributes include (but are not limited to):

DSN

            dbi:mSQL:minivend:othermachine.my.com:1112

USER

PASS

COLUMN_DEF

NAME

NUMERIC

UPPERCASE

DELIMITER

KEY

ChopBlanks, LongReadLen, LongTruncOK, RaiseError, etc.

          ChopBlanks
          CompatMode
          LongReadLen
          LongTruncOk
          PrintError
          RaiseError
          Warn

Here is an example of a completely set up DBI database on MySQL, using a comma-separated value input, setting the DBI attribute LongReadLen to retrieve an entire field, and changing some field definitions from the default char(128):

 Database   products  products.csv  dbi:mysql:minivend
 Database   products  USER          minivend
 Database   products  PASS          nevairbe
 Database   products  DELIMITER     CSV

 # Set a DBI attribute
 Database   products  LongReadLen   128

 # 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 NUL 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

MySQL, DBI, and DBD::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.

To change to ODBC, the only changes required might be:

   Database products  DSN         dbi:ODBC:TCP/IP localhost 1313
   Database products  ChopBlanks  1

The DSN setting is specific to a ODBC setup. The ChopBlanks setting takes care of the space-padding in Solid and some other databases. It is not specific to ODBC. Once again, DBI, DBD::ODBC, and the appropriate ODBC driver must be installed and tested.

3.2. SQL Access Methods

An Interchange SQL database can be accessed with the same tags as any of the other databases can. Arbitrary SQL queries can be passed with the [query sql="SQL STATEMENT"] MML tag.

3.3. Importing from an ASCII File

When importing a file for SQL, Interchange by default uses the first column of the ASCII 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 COLUMN_DEF Database directive attribute:

 Database  products  COLUMN_DEF  price=char(20), nontaxable=char(3)

This can be set as many times as desired, if it will not fit on the line.

 Database  products  COLUMN_DEF  price=char(20), nontaxable=char(3)
 Database  products  COLUMN_DEF  description=char(254)

To create an index automatically, append the information when the value is in quotes:

 Database  products  COLUMN_DEF  "code=char(14) primary key"

The field delimiter to use is TAB by default, but can be changed with the Database DELIMITER directive:

 Database  products products.csv dbi:mSQL:minivend:localhost:1114
 Database  products DELIMITER  CSV

To create other secondary keys to speed sorts and searches, do so in the COLUMN_DEF:

 Database  products COLUMN_DEF  "author=char(64) secondary key"

Or use external database tools. NOTE: Not all SQL databases use the same index commands.

To use an existing SQL database instead of importing, set the NoImport directive in catalog.cfg to include any database identifiers not to be imported:

   NoImport  products inventory


WARNING: If 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 SQL database could be overwritten. In any case, always back up the database before enabling it for use by Interchange.


4. 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.

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].


Note: Examples of search forms and result pages are included in the demos.

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.

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.

The text-based search is capable of sophisticated field-specific searches with fully-independent case-sensitivity, substring, and negated matching.

4.1. 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.

Here is a simple search form:

 <FORM ACTION="[area search]" METHOD=POST>
 <INPUT TYPE="text" SIZE="30" NAME="mv_searchspec">
 <INPUT TYPE="submit" VALUE="Search">
 </FORM>

When the "Search" submit button is pressed (or <ENTER> 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.

The same search for a fixed string, say "shirt," could be performed with the use of a hot link, using the special scan URL:

 [page search="se=shirt"]See our shirt collection![/page]

The default is to search every field on the line. To match on the string "shirt" in the product database field "description," modify the search:

 <INPUT TYPE="hidden" NAME="mv_search_field" VALUE="description">

In the hot-linked URL search:

 [page search="
               se=shirt
               sf=category
           "]See our shirt collection![/page]

To let the user decide on the search parameters, use checkboxes or radiobox fields to set the fields:

   Search by author
      <INPUT TYPE="checkbox" NAME="mv_search_field" VALUE="author">
   Search by title
       <INPUT TYPE="checkbox" NAME="mv_search_field" VALUE="title">

Fields can be stacked. If more than one is checked, all checked fields will be searched.

4.2. 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 Interchange was installed in the default /usr/local/lib/minivend, the command line to build the index for the products file would be:

   chdir products
   glimpseindex -b -H . products.txt

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 split(1) UNIX/POSIX command. Then, index it with Glimpse:

   split -100 products.txt index.txt.
   glimpseindex -H /usr/local/lib/minivend/products index.txt.*

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.

To search for numbers, add the -n option to the Glimpse command line.


Note: A large catalog is one of more than several thousand items; smaller ones have acceptable speed in any of the search modes.

If the Glimpse executable is not found at Interchange startup, the Glimpse search will be disabled and the regular text-based search used instead.

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.

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).

4.3. 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.

The field to search is the first field in the file, the product code should be in the second field, delimited by TAB. Set the mv_return_fields=1 to return the product code in the search.

The search must be done on a dictionary-ordered pre-built index, which can be produced with the database INDEX modifier. See Dictionary indexing with INDEX.

If using the mv_dict_look parameter by itself, and the proper index file is present, Interchange will set the options:

   mv_return_fields=1
   mv_dict_limit=-1

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:

   Database   products   INDEX    title


Note: The ASCII source file must be "touched" to rebuild the index and the database.

Now, specify in a form:

   <FORM ACTION="[process href=search]" METHOD=POST>
   <INPUT TYPE=hidden NAME=mv_dict_limit VALUE=title>
   <INPUT NAME=mv_dict_look>
   </FORM>

or in a URL:

   [page search="dl=Van Gogh/di=title"]

This search is case-sensitive. To do the same thing case-insensitively:

   Database   products   INDEX    title:f

   <FORM ACTION="[process href=search]" METHOD=POST>
   <INPUT TYPE=hidden NAME=mv_dict_limit VALUE=title>
   <INPUT TYPE=hidden NAME=mv_dict_fold  VALUE=1>
   <INPUT NAME=mv_dict_look>
   </FORM>

   [page search="dl=Van Gogh/di=title/df=1"]

4.4. 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.

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:

   <INPUT TYPE=hidden NAME=mv_search_map VALUE="
       mv_searchspec=search1
       mv_searchspec=search2
       mv_searchspec=search3
       ">
   <INPUT TYPE=hidden NAME=mv_search_field VALUE=title>
   <INPUT TYPE=hidden NAME=mv_search_field VALUE=artist>
   <INPUT TYPE=hidden NAME=mv_search_field VALUE=category>
   Artist: <INPUT NAME=search1 VALUE="">
   Title:  <INPUT NAME=search2 VALUE="">
   Genre:  <INPUT NAME=search3 VALUE="">

Even if the user leaves one blank, the search will work.

Leading/trailing whitespace is stripped from all lines in the mv_search_map variable, so it can be positioned as shown for convenience.

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 OR search, the penalty should not be too great as only matching lines will cause an access to the database.

Individual field operations can then be specified with the mv_column_op (or op) parameter. The operations include:

   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

An example:

   [page scan
           co=yes
           sf=title
           se=Sunflowers
           op=em
           sf=artist
           se=Van Gogh
           op=rm          ] Sunflowers, Van Gogh [/page]

   [page search="
           co=yes

           sf=title
           se=Sunflowers
           nu=0
           op=!~

           sf=artist
           se=Van Gogh
           op=rm
           nu=0

           sf=inventory:qty
           se=1
           op=>=
           nu=1
       "] Any in stock except Sunflowers, Van Gogh [/page]

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:

   [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 [/page]

The co=yes is assumed when specifying a multiple search.

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.

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.

4.5. Specifying a Text-Based Search with SQL-Like Syntax

If Jochen Wiedmann's SQL::Statement module is installed, a SQL syntax can be specified for the text-based search. (This is not the same as the SQL search, treated below separately. It would work on an SQL table, but only on the ASCII text source file, not on the actual database.)

This syntax allows this form setup:

   Artist: <INPUT NAME="artist">
   Title:  <INPUT NAME="title">
   <INPUT TYPE=hidden NAME="mv_sql_query"
           VALUE="
               SELECT code FROM products
               WHERE artist LIKE artist
               AND    title LIKE title">

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.

   Search for: <INPUT NAME="searchstring"><BR>
   Search in   <INPUT TYPE="radio" NAME="column" VALUE="title"> title
       <INPUT TYPE="radio" NAME="column" VALUE="artist"> artist
       <INPUT TYPE=hidden NAME="mv_sql_query"
         VALUE="SELECT code FROM products WHERE 'column' LIKE searchstring">

Once again, this does not conduct a search on an SQL database, but formats a corresponding text-based search. Parentheses will have no effect, and an OR condition will cause all conditions to be OR. The searches above would be similar to:

   [page search="
               co=yes
               sf=artist
               op=rm
               se=[value artist]
               sf=title
               op=rm
               se=[value title]
           "  ]
       Search for [value artist], [value title]
   [/page]

   [page search="
               co=yes
               sf=[value column]
               op=rm
               se=[value searchstring]
           "  ]
   Search for [value searchstring]
          in  [value column]
   [/page]

4.6. Range Searching

Range searching allows qualification of search returns with a field that must be within a certain numeric or alphanumeric range. To use it, set the mv_range_look variable to the products database field, or a column/field number for another file. Then, set the corresponding mv_range_min and mv_range_max variables with a selectable field.

   <INPUT TYPE="hidden" NAME="mv_range_look" VALUE="price">
       Search on Price
   Min <SELECT NAME="mv_range_min">
            <OPTION value=0 SELECTED> Free
            <OPTION value=1000000> $1,000,000
            <OPTION value=10000000> $10,000,000
            <OPTION value=20000000> $20,000,000
            <OPTION value=40000000> $40,000,000
       </SELECT><BR>
   Max <SELECT NAME="mv_range_max">
           <OPTION value=0 SELECTED> no object
           <OPTION value=1000000> $1,000,000
           <OPTION value=10000000> $10,000,000
           <OPTION value=20000000> $20,000,000
           <OPTION value=40000000> $40,000,000
       </SELECT>

The value of 0 for mv_range_max is equivalent to infinity if doing a numeric search. This makes it impossible to search for a ceiling of 0 with a negative mv_range_min.

The fields are stackable, so more than one range to check can be set. The order is significant, in the sense that the array of field names and minimum/maximum values must be kept in order to achieve correspondence.

The optional mv_range_alpha specification allows alphanumeric range matching for the corresponding field. If it is set, and the fields are stacked, they must all be set. The mv_case field does apply if it is set. Otherwise, the comparison is without regard to case.

If ONLY a range search is required, all lines with mv_return_all=yes must be selected in order to make the search operate. Range-only searches will be quite slow for large databases since every line must be scanned. It should be quite usable for catalogs of less than 10,000 items in size on a fast machine. Using it in combination with another search technique (in the same query) will yield faster search returns.

4.7. One-Click Searches

Interchange allows a search to be passed in a URL, as shown above. Just specify the search with the special page parameter search or special page scan. Here is an example:

    [page search="
               se=Impressionists
               sf=category
           "]
       Impressionist Paintings
    [/page]

This is the same:

    [page scan se=Impressionists/sf=category]
       Impressionist Paintings
    [/page]

Here is the same thing from a home page (assuming /cgi-bin/vlink is the CGI path for Interchange's vlink):

    <A HREF="/cgi-bin/vlink/scan/se=Impressionists/sf=category">
       Impressionist Paintings
    </A>

The two-letter abbreviations are mapped with these letters:

 ac  mv_all_chars
 bd  mv_base_directory
 bs  mv_begin_string
 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
 lo  mv_list_only
 lr  mv_search_line_return
 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
 ra  mv_return_all
 rd  mv_return_delim
 rf  mv_return_fields
 rg  mv_range_alpha
 rl  mv_range_look
 rm  mv_range_min
 rn  mv_return_file_name
 rr  mv_return_reference
 rs  mv_return_spec
 rx  mv_range_max
 SE  mv_raw_searchspec
 se  mv_searchspec
 sf  mv_search_field
 sg  mv_search_group
 si  mv_search_immediate
 sp  mv_search_page
 sq  mv_sql_query
 sr  mv_search_relate
 st  mv_searchtype
 su  mv_substring_match
 tc  mv_sort_command
 td  mv_table_cell
 tf  mv_sort_field
 th  mv_table_header
 to  mv_sort_option
 tr  mv_table_row
 un  mv_unique
 va  mv_value

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 URL.

IMPORTANT NOTE: An incompatibility in earlier Interchange catalogs is specifying [page scan/se=searchstring]. This is interpreted by the parser as [page scan/se="searchstring"] and will cause a bad URL. Change this to [page scan se=searchstring], or perhaps better yet:

   [page search="
                   se=searchstring
           "]

cause a bad URL. Change this to [page scan se=searchstring].

A one-click search may be specified in three different ways.

Original

         [page scan se=Surreal/se=Gogh/os=yes/su=yes/sf=artist/sf=category]
            Van Gogh -- compare to surrealists
         [/page]

Multi-Line

            [page scan
                se="Van Gogh"
                sp=lists/surreal
                os=yes
                su=yes
                sf=artist
                sf=category
            ] Van Gogh -- compare to surrealists [/page]

Ampersand

         [page href=scan se="Van Gogh"&sp=lists/surreal&os=yes&su=yes&sf=artist&sf=category]
            Van Gogh -- compare to surrealists
         [/page]

4.8. 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:

   [page href=scan
         arg="se=Renaissance
              se=Impressionists
              va=category_name=Renaissance and Impressionist Paintings
              os=yes"]Renaissance and Impressionist Paintings[/page]

Display the appropriate category on the search results page with [value category_name].

4.9. 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.

The [loop ...] tag directly accepts a search parameter. To search for all products in the categories "Americana" and "Contemporary," do:

   [loop search="
       se=Americana
       se=Contemporary
       os=yes
       sf=category9
       "]
   Artist: [loop-field artist]<BR>
   Title: [loop-field title]<P>
   [/loop]

The advantage of the in-page search is that searches can be embedded within searches, and there can be straight unchanging links from static HTML pages.

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:

   [search-region  more=1
                   search="
                        se=Americana
                        sf=category
                        ml=2
                   "]
   [more-list][more][/more-list]
   [search-list]
   <A MV="page [item-code]" HREF="flypage.html">
       [item-field title]<A>, by [item-field artist]
   [/search-list]
   [no-match]
       Sorry, no matches for [value mv_searchspec].
   [/no-match]
   [/search-region]

To use the same page for search paging, make sure to set the sp=page parameter.

4.10. 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:

mv_search_field=artist
mv_search_field=category
mv_orsearch=yes

These correspond to the Interchange search variables that can be set on a form. Set it right on the page that contains the search.

[set artist_profile]
mv_search_field=artist
mv_search_field=category
mv_orsearch=yes
[/set]

This is the same:

[set artist_profile]
sf=artist
sf=category
os=yes
[/set]

Then, in the search form, set a variable with the name of the profile:

   <INPUT TYPE=hidden NAME=mv_profile VALUE=artist_profile>

In a one-click search, use the mp modifier:

[page scan se=Leonardo/mp=artist_profile]A left-handed artist[/page]

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:

 __NAME__ title_search

The __NAME__ must begin the line, and be followed by whitespace and the name.

The special variable mv_last stops interpretation of search variables. The following variables are always interpreted:

   mv_dict_look
   mv_searchspec
   mv_range_look
   mv_range_min
   mv_range_max

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.

To place multiple search profiles in the same file, separate them with __END__, which must be on a line by itself.

4.11. 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.


Note: The 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.

The field names can be specified in several ways.

ProductFiles

Other database files

Other files

Fields can also always be specified by an integer column number, with 0 as the first column.

mv_all_chars

mv_base_directory

            [set /directory/name]1[/set]

mv_begin_string

mv_case

mv_coordinate

mv_dict_end

mv_dict_fold


Note: This is the reverse sense from mv_case.

mv_dict_limit


Note: The order of this and the mv_dict_end variable is significant. Each will overwrite the other.

            <INPUT TYPE=hidden NAME=mv_dict_limit  VALUE=category>
            <INPUT TYPE=hidden NAME=mv_search_file VALUE="products.txt">
            <INPUT TYPE=hidden NAME=mv_dict_limit    VALUE="-1">
            <INPUT TYPE=hidden NAME=mv_search_file   VALUE="products.txt.category">
            <INPUT TYPE=hidden NAME=mv_return_fields VALUE="1">
            Search for
            <SELECT NAME=mv_dict_limit>
            <OPTION> author
            <OPTION> title
            </SELECT> beginning with <INPUT NAME=mv_dictlook>

mv_dict_look

mv_dict_order

mv_doit

mv_exact_match

mv_field_names


Note: Use 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].

mv_first_match

mv_head_skip

mv_index_delim

mv_matchlimit

mv_max_matches

mv_min_string

mv_negate

mv_orsearch

mv_profile

mv_range_alpha

mv_range_look

mv_range_max

mv_range_min

mv_record_delim

mv_return_fields

mv_return_spec

mv_search_field

mv_search_file

mv_search_match_count

mv_search_over_msg

mv_search_page

mv_searchspec

mv_searchtype

mv_sort_field


Note: If specifying a sort for the product database, mv_field_names must be specified if doing a fieldname-addressed post-sort.

mv_sort_option

mv_spelling_errors

mv_substring_match

mv_unique

mv_value

4.12. 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.

On the search page, some special Interchange tags are used to format the otherwise standard HTML. 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.

The basic structure looks like this:

[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]

TIP FOR MV3 PORTS: A [search-list][/search-list] must always be surrounded by a [search-region][/search-region] pair. This is a change from Interchange 3.

[search-list]

            [search-region prefix=my]
            [search-list]
                SKU:   [my-code]
                Title: [my-data products title]
            [/search-list]
            [/search-region]
                [item-alternate N] true [else] false [/else] [/item-alternate]
                [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]


Note: those that reference the shopping cart do not apply, i.e., [item-quantity], [item-modifier ...] and friends.

[/search-list]

[no-match]

[/no-match]

[sort database:field:option* database:field:option*]

[item-change marker]

         <TABLE>
         <TR><TH>Category</TH><TH>Subcategory</TH><TH>Product</TH></TR>
         [search-list]
         <TR>
            <TD>
                 [item-change cat]
        
                 [condition][item-field category][/condition]
        
                         [item-field category]
                 [else]
                         &nbsp;
                 [/else]
                 [/item-change cat]
            </TD>
            <TD>
                 [item-change subcat]
        
                 [condition][item-field subcategory][/condition]
        
                         [item-field subcategory]
                 [else]
                         &nbsp;
                 [/else]
                 [/item-change subcat]
            </TD>
            <TD> [item-field name] </TD>
         [/search-list]
         </TABLE>

[/item-change marker]

[matches]

[more-list next_img* prev_img* page_img* border* border_current*]

             Previous   <IMG SRC="prev.gif" Border=3>
             Page 1     <IMG SRC="/cgi-bin/page_num.cgi?1">
             Page 2     <IMG SRC="/cgi-bin/page_num.cgi?2">
             Next       <IMG SRC="next.gif" Border=3>
             Previous   <IMG SRC="prev.gif">
             Page 1     <IMG SRC="/cgi-bin/page_num.cgi?1">
             Page 2     <IMG SRC="/cgi-bin/page_num.cgi?2">
             Next       <IMG SRC="next.gif">
             Previous   <IMG SRC="prev.gif" Border=0>
             Page 1     <IMG SRC="/cgi-bin/page_num.cgi?1">
             Page 2     <IMG SRC="/cgi-bin/page_num.cgi?2">
             Next       <IMG SRC="next.gif" Border=0>
            [more-list 0 0 0]
            [next-anchor] Forward [/next-anchor]
            [prev-anchor] Back [/prev-anchor]
            [page-anchor] Page $PAGE$ [/page-anchor]
            [more]
            [/more-list]
          Previous 1 2 3 4 5 6 7 8 9 10 [more>>] Next
          Previous [<<more] 11 12 13 14 15 16 17 18 19 20 [more>>] Next
          Previous (lower) 11 12 13 14 15 16 17 18 19 20 (higher) Next

[/more-list]

[more]

            Previous 1 2 3 4 5 6 Next

[process-search]

[process-target frame]


5. 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:

   [loop 4 3 2 1]
   [sort -2 +2]
       [loop-code]
   [/loop]

   [search-list]
   [sort products:category:f]
       [item-price] [item-description]<BR>
   [/search-list]

   [item-list]
   [sort products:price:rn]
       [item-price] [item-code]<BR>
   [/item-list]

   [loop search="ra=yes"]
   [sort products:category products:title]
   [loop-field category] [loop-field title] <BR>
   [/loop]

All sort situations, [search list], [loop list], [tag each table], and [item-list], take options of the form:

 [sort database:field:option* -n +n =n-n ... ]

database

field

option

          f   case-insensitive sort (folded) (mutually exclusive of n)
          n   numeric order (mutually exclusive of f)
          r   reverse sort

-n

+n

=n-n

...

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.

Examples, all based on the simple demo:

Loop list

            [loop 00-0011 19-202 34-101 99-102]
            [sort products:title]
                [loop-code] [loop-field title]<BR>
            [/loop]
            34-101 Family Portrait
            00-0011 Mona Lisa
            19-202 Radioactive Cats
            99-102 The Art Store T-Shirt
            [loop 00-0011 19-202 34-101 99-102]
            [sort products:title -3 +2]
                [loop-code] [loop-field title]<BR>
            [/loop]
            19-202 Radioactive Cats
            99-102 The Art Store T-Shirt

Search list

            [search-list]
            [sort products:artist products:title:rf]
                [item-field artist] [item-field title]<BR>
            [/search-list]
            Gilded Frame
            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
            [search-list]
            [sort products:artist products:title:rf =6-10]
                [item-field artist] [item-field title]<BR>
            [/search-list]
            Sandy Skoglund Radioactive Cats
            The Art Store The Art Store T-Shirt
            Vincent Van Gogh The Starry Night
            Vincent Van Gogh Sunflowers

Shopping cart

            [item-list]
            [sort products:price:rn]
                [item-price] [item-code]<BR>
            [/item-list]

Complete database contents

            [tag each products]
            [sort products:category products:title]
            [loop-field category] [loop-field title] <BR>
            [/tag]

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.


6. Shipping

Interchange has a powerful custom shipping facility that performs UPS and other shipper lookups, as well as a flexible rule-based facility for figuring cost by other methods.

6.1. Shipping Cost Database

The shipping cost database (located in ProductDir/shipping.asc) is a tab-separated ASCII file with six fields: code, text description, criteria (quantity or weight, for example), minimum number, maximum number, and cost. None of the fields are case-sensitive.

To define the shipping database in a catalog configuration file, set the Variable MV_SHIPPING to what would be its contents.

To set the file to be something other than shipping.asc in the products directory, set the Special directive:

   Special  shipping.asc  /home/user/somewhere/shipping_defs

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:

default No shipping weight 0 99999999 0

upsg UPS Ground weight 0 0 e Nothing to ship! upsg UPS Ground weight 0 150 u Ground [default zip 98366] 3.00 upsg UPS Ground weight 150 999999 e @@TOTAL@@ lbs too heavy for UPS

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:

   upsg: UPS Ground
       criteria    weight
       min         0
       max         0
       cost        e Nothing to ship!

       min         0
       max         150
       cost        u
       table       2ndDayAir
       geo         zip
       default_geo 98366
       adder       3

       min         150
       max         999999
       cost        e @@TOTAL@@ lbs too heavy for UPS

The second format has several advantages. Multiple lines can be spanned with the <<HERE document format, like so:

   upsg: UPS Ground
       criteria    <<EOF
   [perl]
       return 'weight' if $Values->{country} eq 'US';
       return 'weight' if ! $Values->{country};
       # Return blank, don't want UPS
       return '';
   [/perl]
   EOF

The definable fields are, in order, for the tab-separated format:

MODE

DESCRIPTION

CRITERIA

MINIMUM

MAXIMUM

COST

           f       Formula (MML tags OK, evaluated as Perl)
           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)

NEXT

ZONE

QUERY

QUAL

PERL

TOTAL

OPT

6.2. 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.

The return must be one of:

quantity

o <field name> or <table>::<field name>

o n.nn


IMPORTANT NOTE: The above only applies to the first field that matches the shipping mode exactly. Following criteria fields contain qualifier matching strings.

6.3. 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.

N.NN (digits)

e

f

i

m

u

x

A-Z

6.4. How Shipping is Calculated

  1. 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.
    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.
  2. The modes are selected from the d
      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.
      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.


Note: The same mode name may be used for all lines in the same group, but the first one will contain the main criteria.

  1. The value of the accumulated criteria is examined. If it falls within the minimum and maximum, the cost is applied.
  2. If the cost is fixed, it is simply added.
  3. If the cost field begins with an x, the cost is multiplied by the accumulated criterion, i.e., price, weight, etc.
  4. If the cost field begins with f, the formula following is applied. Use @@TOTAL@@ as the value of the accumulated criterion.
  5. If the cost field begins with u or a single letter from A-Z, a UPS-style lookup is done.
  6. If the cost field begins with s, a Perl subroutine call is made.
  7. If the cost field begins with e, zero cost is returned and an error placed in the session ship_message field, available as [data session ship_message].

Here is an example shipping file using all of the methods of determining shipping cost.


Note: The columns are lined up for reading convenience. The actual entries should have ONE tab between fields.

global Option n/a 0 0 g PriceDivide

rpsg RPS quantity 0 0 R RPS products/rps.csv rpsg RPS quantity 0 5 7.00 rpsg RPS quantity 6 10 10.00 rpsg RPS quantity 11 150 x .95

usps US Post price 0 0 0 usps US Post price 0 50 f 7 + (1 * @@TOTAL@@ / 10) usps US Post price 50 100 f 12 + (.90 * @@TOTAL@@ / 10) usps US Post price 100 99999 f @@TOTAL@@ * .05

upsg UPS weight [value 0 e Nothing to ship state]0. upsg UPS AK HI 0 150 u upsg [default zip 980] 12.00 round upsg UPS 0 150 u Ground [default zip 980] 2.00 round upsg UPS 150 9999 e @@TOTAL@@ lb too heavy for UPS

upsca UPS/CA weight 0 0 c C UPS_Canada products/can.csv upsca UPS/CA weight -1 -1 o PriceDivide=0 upsca UPS/CA weight 0 150 C upsca [default zip A7G] 5.00 upsca UPS/CA weight 150 99999 e @@TOTAL@@ lb too heavy for UPS

global

rpsg

usps

upsg

            1. Weight
            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.
            UPSZoneFile  products/450.csv
            Database  Ground  Ground.csv  CSV
            @@COST@@  is replaced with whatever the UPS lookup returned
            @@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

upsca

            c X name file* length* multiplier*
            c U UPS products/450.csv 3 1

6.5. 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 UPS distributes for US shippers.

The zone file is a file that is usually specific to the originating location. For US shippers shipping to US locations, it is named for the first three digits of the originating zip code with a CSV extension. For example, 450.csv.

It has a format similar to:

   low - high, zone,zone,zone,zone

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.

The first operative row of the zone file (one without leading quotes) is used to determine the zone column name. In the US, it looks something like:

Dest. ZIP,Ground,3 Day Select,2nd Day Air,2nd Day Air A.M.,Next Day Air Saver,Next Day Air

Interchange strips all non-alpha characters and comes up with:

DestZIP,Ground,3DaySelect,2ndDayAir,2ndDayAirAM,NextDayAirSaver,NextDayAir

Therefore, the zone column (shipping type) that would be used for UPS ground would be "Ground," and that is what the database should be named. To support the above, use a shipping.asc line that reads:

   upsg  UPS Ground  weight  0  150  u Ground [default zip 983]

and a catalog.cfg database callout of:

   Database  Ground  Ground.csv  CSV

These column names can be changed as long as they correspond to the identifier of the rate database.

The rate database is a standard Interchange database. For U.S. shippers, UPS 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.

To adapt other shipper zone files to Interchange's lookup, they will need to fit the UPS US format. (Most of the UPS international files don't follow the U.S. format). For example, the 1998 Ohio-US to Canada file begins:

   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.

   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

It will need to be changed to:

   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

Match it with a canstnd CSV database that looks like this:

   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

It is called out in catalog.cfg with:

   Database canstnd canstnd.csv CSV

With the above, a 4-pound shipment to postal code E5C 4TL would yield a cost of 13.20.

6.6. 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:

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

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 AK or HI, UPS Ground with an adder of 12 will be selected. If it "falls through," UPS Ground with an adder of 3 will be selected.

6.7. 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. NOTE: This should not be done in an item-list unless the multiple setting of the variables is accounted for.

To only process a handling charge once, do the following:

   [item-list]
   [if-item-field very_heavy]
   [perl values]
       return '' if $Values->{mv_handling} =~ /very_heavy/;
       return "<INPUT TYPE=hidden NAME=mv_handling VALUE=very_heavy>";
   [/perl]
   [/if-item-field]
   [/item-list]

A non-blank/non-zero value in the database field will trigger Perl code which will only set mv_handling once.

6.8. Default Shipping Mode

If a default shipping mode other than default is desired, enter it into the DefaultShipping directive:

   DefaultShipping     upsg

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:

 <INPUT TYPE=RADIO NAME=mv_shipmode VALUE=upsg [checked mv_shipmode upsg]>

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.


7. 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.

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 availabe for display with [value address]. Similarly, the database value is available with [data table=userdb column=address key=username].

The ASCII 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 ASCII file, as it will overwrite the real data that is in the DBM table. User logins and changes would be lost. Note: This would not happen with SQL, but editing the ASCII file would have no effect. It is recommended that the NoImport configuration directive be set accordingly.

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:

   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

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.


Note: If not running with PGP 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.

The special database fields are:

   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

If not defined, the corresponding capability is not available.


Note: The fields accounts, address_book, carts, and preferences should be defined as a BLOB type, if using SQL. This is also suggested for the acl fields if those lists could be large.

Reserved fields include:

   code        The username (key for the database)
   password    Password storage
   time        Last time of login

7.1. The [userdb ...] Tag

Interchange provides a [userdb ...] tag to access the UserDB functions.

[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*
       ...
       ]

* Optional

It is normally called in an mv_click or mv_check setting, as in:

   [set Login]
   mv_todo=return
   mv_nextpage=welcome
   [userdb function=login]
   [/set]

   <FORM ACTION="[process-target]" METHOD=POST>
   <INPUT TYPE=hidden NAME=mv_click VALUE=Login>
   Username <INPUT NAME=mv_username SIZE=10>
   Password <INPUT NAME=mv_password SIZE=10>
   </FORM>

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 parens):

   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)
   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)

By default the system crypt() 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.

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:

   UserDB   default   crypt   0

Encryption can still be set on by passing crypt=1 with any call to a new_account, change_pass, or login call.

7.2. 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:

   UserDB   default   scratch  tickets

That makes every call to [userdb function=login] equivalent to [userdb function=login scratch=tickets].

To override that default for one call only, use [userdb function=login scratch="passes"].

To log failed access authorizations, set the UserDB profile parameter log_failed true:

   UserDB  default  log_failed 1

To disable logging of failed access authorizations (the default), set the UserDB profile parameter log_failed to 0:

   UserDB  default  log_failed 0

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:

   UserDB  crypt_case  <<EOF
   {
       'scratch'        => 'tickets',
       'crypt'          => '1',
       'ignore_case'    => '0',
   }
   EOF

   UserDB  default  <<EOF
   {
       'scratch'        => 'tickets',
       'crypt'          => '1',
       'ignore_case'    => '1',
   }
   EOF


Note: The usual here-document caveats apply. The "EOF" must be on a line by itself with no leading/trailing whitespace.

The last one to be set becomes the default.

The option profile selects the set to use. For usernames and passwords to be case sensitive with no encryption, pass this call:

   [userdb function=new_account profile=case_crypt]

The username and password will be stored as typed in, and the password will be encrypted in the database.

7.3. User Database Functions

The user database features are implemented as a series of functions attached to the userdb tag. The functions are:

login

logout

new_account

            [userdb function=new_account
                    username_mask="^[A-Z]*[0-9]"
                    ]
            [userdb function=new_account
                    username="[value mv_order_number]"
                    password="[value zip]"
                    verify="[value zip]"
                    database="orders"
                    ]

change_pass

set_shipping

            [userdb function=set_shipping nickname=Dad]

get_shipping

            [userdb function=get_shipping nickname=Dad]

get_shipping_names

            [set name=shipping_nicknames
                 interpolate=1]
              [userdb function=get_shipping_names show=1]
            [/set]

set_billing

            [userdb function=set_billing nickname=discover]

get_billing

            [userdb function=get_billing nickname=visa]

save

set_cart

            [userdb function=set_cart nickname=christmas]

get_cart

            [userdb function=get_cart nickname=mom_birthday]

set_acl

            [userdb function=set_acl location=cartcfg/editcart]
            [userdb function=set_acl location=cartcfg/editcart delete=1]
            [userdb function=set_acl location=cartcf/editcart show=1]

check_acl

            [if type=explicit
                compare="[userdb
                            function=check_acl
                            location=cartcfg/editcart]"
            ]
            [page cartcfg/editcart]Edit your cart configuration[/page]
            [/if]

set_file_acl, set_db_acl

            [userdb function=set_file_acl
                    mode=rw
                    location=products/inventory.txt]

check_file_acl, check_db_acl

            [userdb function=check_db_acl
                    mode=w
                    location=inventory]
            [if type=explicit
                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[/page]
            [else]
            [userdb function=set_acl location=cartcfg/edit_inventory delete=1]
            Sorry, you can't edit inventory.
            [/if]

7.4. 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:

  s_nickname
  name
  fname
  lname
  address
  address1
  address2
  address3
  city
  state
  zip
  country
  phone_day
  mv_shipmode

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 HTML select box or in a set of links.

To get the names of the addresses, use the get_shipping_names function:

   [userdb function=get_shipping_names]

By default, they are placed in the variable address_book. Here is a little snippet that builds a select box:

   <FORM ACTION="[process-target]" METHOD=POST>
   [userdb function=get_shipping_names]
   [if value address_book]
   <SELECT NAME="s_nickname">
   [loop arg="[value address_book]"] <OPTION> [loop-code] [/loop]
   </SELECT>
   <INPUT TYPE=submit NAME=mv_check VALUE="Recall Shipping">
   </FORM>

The same principle works with accounts, carts, and preferences.

To restore a cart based on the above, put in an mv_check routine:

   [set Recall Shipping]
   mv_todo=return
   mv_nextpage=ord/basket
   [userdb function=get_shipping nickname="[value s_nickname]"]
   [/set]

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.

To change the fields that are recalled or saved, use the shipping parameter:

   [userdb function=get_shipping
           nickname=city_and_state
           shipping="city state"]

Only the values of the city and state variables will be replaced.

7.5. Accounts Book

The accounts book saves information relevant to billing the order. By default these form values are included:

  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

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 HTML select box or in a set of links.

7.6. Preferences

Preferences are miscellaneous session information. They include, by default, the following fields:

   email
   fax
   phone_night
   fax_order
   email_copy

The field p_nickname acts as a key to select the preference set. To change the values that are included with the preferences parameter:

   [userdb function=set_preferences
           preferences="email_copy email fax_order fax"]

or in catalog.cfg:

   UserDB default preferences "mail_list email fax_order music_genre"

7.7. 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.

7.8. 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.)

Set the following variables in catalog.cfg:

   Variable   MV_USERDB_ACL_TABLE  userdb
   Variable   MV_USERDB_ACL_COLUMN acl

The MV_USERDB_ACL_TABLE is the table which controls access, and likewise the MV_USERDB_ACL_TABLE names the column in that database which will be checked for authorization.

The database entry should contain the complete Interchange-style page name of the page to be allowed. It will not match substrings.

For example, if the user flycat followed this link:

   <A HREF="[area cartcfg/master_edit]">Edit</A>

Access would be allowed if the contents of the userdb were:

   code    acl
   flycat  cartcfg/master_edit

and disallowed if it were:

   code    acl
   flycat  cartcfg/master_editor

Access can be enabled with:

   [userdb function=set_acl location="cartcfg/master_edit"]

Access can be disallowed with:

   [userdb function=set_acl
           delete=1
           location="cartcfg/master_edit"]

Of course, a pre-existing database with the ACL values will work as well. It need not be in the UserDB setup.


8. Back Office UI

8.1. [display] tag and mv_metadata

The Interchange back office UI uses the mv_metadata table to discover formatting information for field, table, and view display. The information is kept in fields in the mv_metadata table, and is used to select the display, the HTML input type, the size (height and width where appropriate), label, help text, additional help URL, and default value display.

It works in conjunction with the [display ...] usertag defined in the Interchange UI as well as in specific pages in the UI. The [display] tag has this syntax:

[display table=tablename column=fieldname key=key arbitrary=tag filter=op ...]

In the simplest use, the formatting information for a table form field is called with:

The mv_metadata table is scanned for the following keys:

If a row is found with one of those keys, then the information in the row is used to set the display widget. If no row is found, an INPUT TYPE=TEXT widget is displayed. If the data is all digits, a size of 8 is used, otherwise the size is 60.

If the following row were found (not all fields shown, would be tab-separated in the actual data):

  code                type   width height label     options
  products::category  text   20           Category

Then an input <INPUT TYPE=text SIZE=20 VALUE="*category*"> with a field label of Category would be output.

If the following row were found:

  code                type   width height label     options
  products::category  select              Category  =none, product=Hardware

Then the following would be output:

        <SELECT NAME=category>
        <OPTION VALUE="">none
        <OPTION VALUE="product">Hardware
        </SELECT>

The standard widget types are:

text

    width   size of input box

textarea

        width   COLS for textarea
        height  ROWS for textarea

select

  height          SIZE for select
  default         Default for SELECTED
  options         Options for a fixed list (or prepended to a lookup)
  lookup          signals a lookup (used as field name if "field" empty)
  field           field to look up possible values in
  db              table to look up in if not current table
  lookup_exclude  regular expression to exclude certain values from lookup

9. Standard Database Dictionary

You can alter any of the standard databases to suit your needs, add new databases, or remove unneeded ones. But the following dictionary lists each table and each of its fields, and explains its purpose.

9.1. 2ndDayAir.csv

Shipping table

9.2. 450.csv

Shipping table

9.3. Ground.csv

Shipping table

9.4. NextDayAir.csv

Shipping table

9.5. access.asc

Admin access table

    username
    password
    name
    last_login
    super
    yes_tables
    no_tables
    upload
    acl
    export
    edit
    pages
    files
    config
    reconfig
    groups
    meta

    no_functions
        Used From: none

    yes_functions
        Used From: dist/lib/UI/pages/admin/access_permissions.html

    table_control
        Used From: dist/lib/UI/Primitive.pm
                   dist/lib/UI/pages/admin/special/key_violation.html
                   dist/lib/UI/usertag/if_mm

    personal_css
        Used From: dist/lib/UI/pages/admin/preferences.html
        Used in the admin screens to make personal changes to the admin
        presentation, by creating your own personal CSS, appears to be used
        currently only in the preferences screen.

9.6. affiliate.txt

    affiliate
    name
        Affiliate Name
    campaigns
    coupon_amount
    join_date
        Join date
    url
        URL (Default URL to redirect to)
    timeout
        Timeout delay (in seconds, 0 to disable)
    active
    password
    image

9.7. area.txt

    code
    sel
    name
    which_page
    sort
    display_type
    image
    image_prop
    banner_image
    banner_text
    link_type
            none = No link
            external = External link (http://...)
            internal = Interchange page
            simple = Simple search
            complex = Complex search
    url
    tab
    page
    search
    selector
    link_template

9.8. banner.txt

    code
    category
    weight
    rotate
    banner

9.9. cat.txt

    code
    sel
    name
    which_page
    sort
    display_type
            name = Name
            url = URL only
            icon = Icon and name
            image = Image
    image
    image_prop
    banner_image
    banner_text
    link_type
        Type of link:
            none = No link
            external = External link (http://...)
            internal = Interchange page
            simple = Simple search
            complex = Complex search
    url
    tab
    page
    search
    selector
    link_template

9.10. component.txt

    code
    group
    name
    width
    height
    pieces
    label
    banner
    help
    help_url
    filter
    controls
    Variable

9.11. country.txt

    code
    sorder
    region
    selector
    shipmodes
    name

9.12. downloadable.txt

    sku
    dl_location
    dl_type

9.13. files.txt

9.14. gift_certs.txt

    code
    username
    order_date
    original_amount
    redeemed_amount
    available_amount
    passcode
    active
    redeemed
    update_date

9.15. icmenu.txt

    code
    mgroup
    msort
    next_line
    indicator
    exclude_on
    depends_on
    page
    form
    name
    super
    inactive
    special
    help_name

9.16. inventory.txt

    sku
        Quantity info
    quantity
    stock_message
        Out of stock message:
            In stock
            Ships in 3-5 days
            Ships in 4-6 weeks
            Special order
    account
        Accounting info
        Sales account
    cogs_account

9.17. locale.txt

    code
    en_US
    de_DE
    fr_FR

9.18. merchandising.txt

    sku
    featured
    banner_text
    banner_image
    blurb_begin
    blurb_end
        Closer (end text for feature display)
    timed_promotion
    start_date
        Start date
    finish_date
    upsell_to
        Cross-sell SKUs
    cross_sell
    cross_category
    others_bought
    times_ordered

9.19. mv_metadata.asc

    code
        Table::Column to be operated on.
        Database table
    type
        Widget type (Select the basic display type for the field)
            textarea = Textarea
            text = Text Entry (default)
            select = Select Box
            yesno = Yes/No (Yes=1)
            noyes = No/Yes (No=1)
            multiple = Multiple Select
            combo = Combo Select
            reverse_combo = Reverse Combo
            move_combo = Combo move
            display = Text of option
            hidden_text = Hidden(show text)
            radio = Radio box
            radio_nbsp = Radio (nbsp)
            checkbox = Checkbox
            check_nbsp = Checkbox (nbsp)
            imagedir = Image listing
            imagehelper = Image upload
            date = Date selector
            value = Value
            option_format = Option formatter
            show = Show all options
    width
        Width (SIZE for TEXT, COLS for TEXTAREA, Label limit for SELECT)
    height
        Height (SIZE for SELECT, ROWS for TEXTAREA)
    field
        Field for lookup (can be two comma separated fields, in which case
        second is used as the label text.  Both must be in the same table.)
    db
    name
        Variable name (normally left empty, changes variable name to send in
        form)
    outboard
        Select directory for image listing widget
    options
        options in the format <blockquote>value=label*</blockquote>
    attribute
        Column name (Do not set this.)
    label
    help
        Help (displays at top of page)
    lookup
        Lookup select (Whether lookup is performed to get options for a select
        type.  If nothing is in the field, then used as the name of the field
        to lookup in.  Use lookup table if you want to look up in a different
        table.
    filter
        Filters (Filters which can transform or constrain your data.  Some
        widgets require filters.)
    help_url
        Help URL (links below help text)
        A URL which will provide more help
    pre_filter
    lookup_exclude
        ADVANCED: regular expression that excludes certain keys from the lookup
    prepend
    append
        Append HTML (HTML to be appended to the widget.  Will substitute in the
        macros _UI_TABLE_, _UI_COLUMN_, _UI_KEY_, and _UI_VALUE_, and will
        resolve relative links with absolute links.)
    display_filter

9.20. options.txt

    code
    o_master
        Master item (Always included when the base SKU is equal to this.)
    sku
        Unique SKU (Associated item)
    o_group
        Option Group (scanned to see if it applies (or doesn't apply) to this
        product)
    o_sort
    phantom
        Phantom? (Whether a phantom for structuring)
    o_enable
        Follow? (Enable for next level) Sub-items
    o_matrix
        Matrix options (Matrix options allow you to maintain multiple option
        sets with inventory on each combination)
    o_modular
        Modular Options (Modular options allow you to attach multiple SKUs to
        the same item, possibly with attached options of their own.  Very
        complex but you can do most anything.
    o_default
        Default? (Yes if the default selection for the group)
    o_label
        Short name for option display
    o_value
        Possible Values (In Interchange option format: VALUE1=Label 1,
        VALUE2=Label 2 *=default selection)
    o_widget
    o_footer
    o_header
    o_height
        Height of widget (if applicable)
    o_width
        Width of widget
    description
        Option/Variant description (for description in display)
    price
        Price (Price of this option/variant)
    wholesale
        Dealer price
    differential
    weight
    volume
        Volume (if different, mostly for matrix/modular)
    mv_shipmode
    o_exclude
        Exclude (Only for modular options. Lists the option groups to exclude
        once the include has been done.  Takes the form of a number of wildcard
        atoms.
    o_include
        Include (Only for modular options. Lists the option groups to include
        with your item. Takes the form of a number of wildcard atoms.)

9.21. order_returns.txt

    code
    order_number
    session
    username
    rma_number
    nitems
    total
    return_date
    update_date

9.22. orderline.txt

    code
    store_id
    order_number
    session
    username
    shipmode
    sku
    quantity
    price
    subtotal
    shipping
    taxable
    mv_mi
    mv_si
    size
    color
    options
    order_date
    update_date
    status
            pending = Pending
            shipped = Shipped
            backorder = Back ordered
            credit = Waiting for credit check
            canceled = Cancelled
    parent
    affiliate
    campaign
    description
    mv_mp

9.23. pricing.txt

    sku
    price_group
    q2
    q5
    q10
    q25
    q100

9.24. products.txt

    sku
    description
        Short Description
    title
        Title
    template_page
    comment
        Detailed Description
    thumb
        Thumb
    image
        Image
    price
    wholesale
        Dealer Price
    prod_group
    category
        Category (Enter in box for new category.)
    nontaxable
    weight
        Weight in pounds
    size
    color
    gift_cert
        Gift certificate handling? (Yes if price should appear to be quantity)
    related
    featured
    download
    dl_type
    dl_location
    inactive
    url

9.25. route.txt

    code
    attach
    continue
    commit
    commit_tables
    counter
    credit_card
    cyber_mode
    email
    empty
    encrypt
    encrypt_program
    errors_to
    increment
    inline_profile
    individual_track
    individual_track_ext
    partial
    pgp_cc_key
    pgp_key
    profile
    receipt
    reply
    report
    rollback
    rollback_tables
    supplant
    track

9.26. salestax.asc

9.27. shipping.asc

Shipping methods table

9.28. state.txt

State/territory/county information

    code
    sorder
    country
    state
    name
    tax
    postcode
    shipmodes
    tax_name

9.29. transactions.txt

Order table, one entry per order, assoc with orderline

    code
    store_id
    order_number
    session
    username
    shipmode
    nitems
    subtotal
    shipping
    handling
    salestax
    total_cost
    fname
    lname
        Last Name
    company
    address1
    address2
        Address line 2
    city
    state
    zip
    country
    phone_day
        Daytime Phone
    phone_night
        Home Phone
    fax
    email
    b_fname
    b_lname
        Billing Last Name
    b_company
    b_address1
    b_address2
        Billing Address Line 2
    b_city
    b_state
        Billing State
    b_zip
        Billing Postcode
    b_country
        Billing Country
    b_phone
    order_date
    order_ymd
    order_wday
    payment_method
    po_number
    avs
    order_id
    update_date
    status
    affiliate
    campaign
    parent
    archived
    deleted
    complete
    comments

9.30. userdb.txt

Customer database

    username
    password
    acl
    mod_time
    s_nickname
    company
    fname
    lname
    address1
    address2
    address3
    city
    state
    zip
        Postcode
    country
        Country
    phone_day
    mv_shipmode
    b_nickname
    b_fname
    b_lname
    b_address1
    b_address2
    b_address3
    b_city
    b_state
    b_zip
    b_country
    b_phone
        Billing Phone
    mv_credit_card_type
    mv_credit_card_exp_month
    mv_credit_card_exp_year
    p_nickname
    email
    fax
    phone_night
    fax_order
        Payment method:
            (none) = Credit Card
            1 = Fax or Mail
            2 = Purchase order
            3 = COD
    address_book
    accounts
    preferences
    carts
    owner
    file_acl
    db_acl
    order_numbers
    email_copy
    mail_list
        Mailing lists the customer has joined:
            offer = Special offers
            newsletter = Newsletter
            alert = Alerts and Recalls
            upgrade = Upgrades
    project_id
    account_id
    order_dest
    credit_limit
    inactive
    dealer
        Dealer:
            (none) = No
            1 = Yes
    b_company
    feedback
        ???

9.31. variable.txt

Configuration database

    code
        Variable name
    Variable
    pref_group
        Preferences area

10. Frequently Asked Questions

10.1. Does Interchange require an SQL database?

No. Interchange does not require an external SQL database. If you have a small database with no particular desire to integrate your own tool set, you may be more comfortable using Interchange's internal database.

Bear in mind that the order management functions of Interchange will be slow and not as robust without SQL. SQL is strongly recommended for at least these three tables:

       orderline
       transactions
       userdb

10.2. I can't get SQL to work: Undefined subroutine &Vend::Table::DBI::create ...

This probably means one of the following:

No SQL database.

No DBI.

            perl -MCPAN -e 'install DBI'

No DBD.

            perl -MCPAN -e 'install DBD::XXXXX'
            Adabas
            DB2
            Informix
            Ingres
            ODBC
            Oracle
            Pg
            Solid
            Sybase
            Unify
            XBase
            mSQL
            mysql
            use DBI;
            use DBD::XXXXX;

I don't like the column types that Interchange defines!

I change the ASCII file, but the table is not updated. Why?

Why do I even need an ASCII file?

            Database products products.txt dbi:mysql:test_minivend
            Database pricing pricing.txt dbi:mysql:test_minivend

Interchange overwrites my predefined table!

10.3. How can I use Interchange with Microsoft Access?

Though Interchange has ODBC capability, the Microsoft Access ODBC driver is not a network driver. You cannot access it on a PC from your ISP or UNIX system.

However, you can turn it around. Once you have created a MySQL or other SQL database on the UNIX machine, you may then obtain the Windows ODBC driver for the database (MySQL has a package called myODBC) and use the UNIX database as a data source for your PC-based database program.

Here is a quick procedure that might get you started:

            http://www.mysql.com/
            http://www.mysql.com/rpm/
            mysqladmin shutdown
            safe_mysqld --skip-grant-tables &
            mysqladmin create test_odbc
            mysql test_odbc
            mysql> create table test_me ( code char(20), testdata char(20) );
            Query OK, 0 rows affected (0.29 sec)
        
            mysql> insert into test_me VALUES ('key1', 'data1');
            Query OK, 1 rows affected (0.00 sec)
        
            mysql> insert into test_me VALUES ('key2', 'data2');
            Query OK, 1 rows affected (0.00 sec)
        
            mysql>
            http://www.mysql.com/

11. Interchange Database Usertags

11.1. Data

           $Tag->data(
               {
                table => VALUE,
                field => VALUE,
                key => VALUE,
               }
           )
           $Tag->data($table, $field, $key, $ATTRHASH);
                   base ==> table
                   code ==> key
                   col ==> field
                   column ==> field
                   database ==> table
                   name ==> field
                   row ==> key
            accesses           Accesses within the last 30 seconds
            arg                The argument passed in a [page ...] or [area ...] tag
            browser            The user browser string
            cybercash_error    Error from last CyberCash operation
            cybercash_result   Hash of results from CyberCash (access with usertag)
            host               Interchange's idea of the host (modified by DomainTail)
            last_error         The last error from the error logging
            last_url           The current Interchange path_info
            logged_in          Whether the user is logged in (add-on UserDB feature)
            pageCount          Number of unique URLs generated
            prev_url           The previous path_info
            referer            HTTP_REFERER string
            ship_message       The last error messages from shipping
            source             Source of original entry to Interchange
            time               Time (seconds since Jan 1, 1970) of last access
            user               The REMOTE_USER string
            username           User name logged in as (UserDB feature)


Note: Databases will hide session values, so don't name a database `session''. or you won't be able to use the [data ...] tag to read them. Case is sensitive, so in a pinch you could call the database ``Session'', but it would be better not to use that name at all.

11.2. db_date

       # [db-date table format]
       #
       # This tag returns the last-modified time of a database table,
       # 'products' by default. Accepts a POSIX strftime value for
       # date format; uses '%A %d %b %Y' by default.
       #
       UserTag  db-date  Order table format
       UserTag  db-date  PosNumber 2
       UserTag  db-date  Routine <<EOF
       sub {
           my ($db, $format) = @_;
           my ($dbfile, $mtime);
    
           # use defaults if necessary
           $db = 'products' unless $db;
           $format = '%A %d %b %Y' unless $format;
    
           # build database file name
           $dbfile = $Vend::Cfg->{ProductDir} . '/'
                   . $Vend::Cfg->{Database}{$db}{'file'};
    
           # get last modified time
           $mtime = (stat ($dbfile))[9];
    
           if (defined ($mtime)) {
                   return POSIX::strftime($format, localtime($mtime));
           } else {
                   logError ("Couldn't stat $dbfile: $!\n");
           }
       }
       EOF

11.3. search_region

            $Tag->search_region(
                {
                 arg => VALUE,
                },
                BODY
            )
            $Tag->search_region($arg, $ATTRHASH, $BODY);
                    args ==> arg
                    params ==> arg
                    search ==> arg