<MvMAKEINDEX>

In an index, records are arranged in order according to the value of the key expression in the EXPR attribute, plus any flags that may be present. INDEXFILE and EXPR are required.

The database to which the index applies must exist and be open under the NAME alias when the index is created, though it need not contain any records.

The newly-created index becomes the main index for that database alias. We recommend using the file extension ".mvx" for index files.

Attributes

• NAME: Database alias. When a database is opened with MvOPEN, it is referenced in all other database commands by the alias. If  omitted, the index is created for the primary database. The database must be open.
• INDEXFILE: Required. Contains the path and filename to be created for the database in the NAME alias.
• EXPR_TYPE: Optional, expression (default) | variable.
• EXPR: Required key expression based on the field values in the database. It may not exceed 500 characters in length, and may not evaluate to more than 100 characters. The EXPR value will be evaluated for each record in the database and the records will be ordered in the index according to the results of these evaluations. The key expression can be any Miva Script expression. Except in unusual circumstances, the key expression will be based on field values in the database.
• FLAGS: Optional: description. Comma delimited list of flags. (e.g. ascending, nonunique)
  • ascending (default) | descending : Indicates sorting in ascending or descending order. *
  • unique | nonunique (default):  Unique allows only one record containing the results of the key expression to be stored in the index file. Duplicate entries will result in an error. Non unique allows any record to be added to the database and the key field(s) indexed
  • string : force string (rather than numeric) comparisons of key expressions when ordering the index.

* By default, indexed records are arranged in ascending order, with lowest key values at the beginning of the index. For CHAR keys, 'A' is first; for NUMBER keys, the lowest numbers are first; and for DATE keys, the earliest date is first. Within an index having a key expression of type CHAR, records are not ordered alphabetically, but rather according to the ASCII codes of the characters in the key. Therefore 'A' through 'Z' precede 'a' through 'z'.

Examples

A common choice would simply be a variable corresponding to one of the fields in the database.

Suppose the database records are entered in this order:

<MvMAKEINDEX NAME="employees" INDEXFILE="{ g.path $ 'emp_salary.mvx' }" EXPR="{ employees.d.salary }">

After indexing, the apparent order of records will be:

At the end of an indexing operation, the index file is active and the record pointer is positioned at the top record of the indexed database.

Primary Database

A variable corresponding to a field in a key expression can be written in the form db_alias.d.fieldname or d.fieldname. The first form refers only to the database with alias db_alias, and the other two refer to the current primary database.

An advantage to using the d.fieldname (or fieldname) form is that if the same physical database is opened under a different alias, the index will still be calculated correctly. However, if you use this form you must ensure that when you're updating the database in question, either (1) it is the primary database, or (2) the primary database does not also have a field called fieldname: if it does, then the value for d.fieldname will come from the current record in the primary database, rather than from the database being updated. It's up to you which form of the variable you choose to use, but in each case you need to take care to avoid unintended results.

Index on Multiple Fields

You can index a database on more than one field by concatenating the values of these fields in the key expression. If you have a database called users that has fields firstname and lastname, you could index on the full name.

Here the key expression concatenates the firstname with the lastname and the index is built according to the results of these expressions. If two last names are the same they will be sub-indexed based on the first name.

<MvMAKEINDEX NAME="employees"
             INDEXFILE="emp_names.mvx"
             EXPR="{employees.d.lname $ employees.d.fname}" FLAGS="unique"> 

The FLAG unique ensures the index contains no duplicates and will result in a runtime error if the database already has duplicates. Once the index is created and open, attempting to add duplicate database entries will again result in an error. You can test for the error and provide an informational message indicating to the user that the database could not be updated.  For indexes without the "unique" flag, no records will be excluded from the database.

Tip: Because <MvMAKEINDEX> can be quite resource-intensive for large databases, and does not automatically lock the database and index files that it uses, it could be used off-line, with Miva Merchant Mia. You can then uploaded to your database on your server. If you do create the index on-line, you should use <MvLOCKFILE> to request a lock on the database and index files while <MvMAKEINDEX> is executing.