[an error occurred while processing this directive]

15. Using Databases

Introduction

Miva Script supports two kinds of databases: xbase3 (dBaseIII compatible) databases, which are supported on all platforms, and ODBC databases which are supported only on Microsoft Windows.

Miva Script has tags that let you perform database operations, such as: creating, opening, and closing a database; adding, deleting, and updating records; searching for records; and creating and using database indexes. Any database files or database indexes that you use must be located in your data directory.

The full Miva database feature set is currently supported only for xbase3 databases.

Access to ODBC data sources is currently supported only with Miva Mia.

Note: If possible, use Miva Mia to perform time-consuming database operations such as converting a flat file to a database, and creating a new index for a large database. This can avoid heavy consumption of web server resources, and having your program time out. You can set Miva Mia's timeout value to be as high as you like.

Using Xbase3 Databases

With the exception <MvOPEN>, <MvSKIP> and <MvGO>, the Miva Script database tags described below are supported only for xbase3 databases. The optional attribute VIEW, available with several database tags, is currently supported only when accessing OBDC databases with <MvSKIP> and <MvGO>.

Introduction to Databases

Miva xbase3 databases organize data into a simple tabular format that consists of records (which you can think of as the rows of the table) that each have one or (usually) more fields (which you can think of as the columns of the table). Note that when we refer to a 'table' here, we don't mean an HTML table (although it is very easy to extract data from a database and put it in such a table). Here 'table' refers to the ordering of the data--the way that the Miva Script language enables you to manipulate the data.

The following is a small example:

Benson
President
250000
19930630
1
Former V P sales
Rochester
V P Finance
120000
19921015
1
Weird ties
Jeeves
V P Technology
150000
19880710
1
Funny hats
Hudson
V P Sales
130000
19971201


This example is from an employee database. Each employee is represented by a record (a row), that has fields for data such as name, title, salary, start date, status, and comments.

Summary of Database Operations

Databases are flexible enough that it is not really possible to give a simple set of steps for using them. However, here are some database operations that apply to almost every database.

Creating the Database

A database must be created before it can be used. When you create a database, you are just defining its structure: a database file is created on your system, but it doesn't contain any data yet. This step needs to be done only once for each database. Miva Script uses the <MvCREATE> tag to create a database.

Database aliases

An important aspect of working with Miva databases that you need to understand is that databases are almost always referred to in Miva Script tags using a database alias, rather than the actual physical filename of the database. An alias is simply a name that you assign to the database when you open it. The same database file can be open two or more times simultaneously with different aliases. If the database has only one current alias, then the alias and the database can be thought of as identical; sometimes this manual will, for convenience, and if the intention is clear, use the term 'database' when strictly speaking 'database alias' is correct.

Opening the Database, <MvOPEN>

A database must be open before you can use it. When a database is created, it is opened automatically. Usually, however, the script that you use to create the database will be run only once, so if you use other scripts to manipulate the database, you have to explicitly open the database, using the <MvOPEN> tag. A database stays open until the script terminates, or until you close it explicitly with <MvCLOSE>. The next time you use the database, you have to open it again. If you want, you can use a different alias for the database the next time you open it.

The Primary Database, <MvPRIMARY>

Miva Script allows several database aliases to be open at the same time, and each of them can be accessed in Miva Script tags by referring to the alias. As a convenience, Miva Script allows one alias at a time to be designated the 'primary' database alias. This means that if a database tag does not explicitly name an alias, the primary alias is implied. An alias becomes the primary alias automatically when it is created or opened (subsequent create or open operations override this); an alias can also be made the primary alias explicitly using <MvPRIMARY>.

Adding and Updating Records

Often you will want to add records or change one or more fields in an existing record. The way you do this in Miva Script is to assign values to a special set of variables corresponding to the database fields, and then use the <MvADD> tag to add a record, or <MvUPDATE> to update the current record.

Indexing the Database

When records are added to a database, they are simply added at the bottom of the 'logical' table structure. Unless you explicitly added them in a specific order, the records will not be ordered in any way. Often you will want to be able to group or order records according to the values of certain fields (for example, sort names alphabetically, or group together all employees with the same job title). For this you can create an index: instead of physically reordering the records, a database index changes the way the order appears to various Miva Script database commands.

Database Navigation

There are several ways of moving from one record to another. You can find (<MvFIND>) the first record in the database index that contains a specific value; you can go (<MvGO>) to a specific record in physical order; and you can skip (<MvSKIP>) to the next record in physical or indexed order.

Filtering the Database

You can use the <MvFILTER> tag to make 'visible' only those records that satisfy a specified criterion.

Reading and displaying Database Records

In order to read and display the contents of a record, you first navigate to the record. At that point the contents of each of the record's fields will be available in a set of special database variables.

Deleting Records, <MvDELETE>

To delete a record, you first navigate to the record. The <MvDELETE> tag marks the record for deletion; the <MvPACK> tag physically removes it.

Obtaining the Database Structure, <MvREVEALSTRUCTURE>

The <MvREVEALSTRUCTURE> tag is used to find out information about the kinds of fields in a database record for a specific database.

Creating a Database

<MvCREATE>

<MvCREATE> creates a new database, with filename db_file, having the data structure specified by FIELDS. The NAME, DATABASE, and FIELDS attributes are all required. Fields in the database can be of type CHAR, NUMBER, DATE, BOOL, and MEMO. For CHAR fields, the maximum number of characters must be specified; for NUMBER fields, the numbers of digits allowed before and after the decimal point can be specified. If db_file already exists, it will be overwritten with an empty database with the specified structure. When a database is created it is implicitly opened (just as with <MvOPEN>) with the alias db_alias. db_alias cannot contain the '.' (period) character. <MvCREATE> is an empty tag.

Syntax:

<MvCREATE
NAME = "db_alias"
DATABASE = "db_file"
FIELDS = "fieldname1 CHAR(max_chars),
fieldname2 NUMBER(digits_before.digits_after),
fieldname3 DATE,
fieldname4 BOOL,
fieldname5 MEMO
..."
TYPE="xbase3">

Note: The TYPE attribute of <MvCREATE> has the default value xbase3. This attribute can be omitted, since <MvCREATE> is currently supported only for xbase3 databases.

A database must be created before it can be used. When you create a database, you are just defining its structure; a database file is created on your system, but it doesn't contain any data yet. This step needs to be done only once for each database.

Example Database

Here is an example that creates a database; this database will be used in other examples below.

<MvCREATE
NAME="worker_db"
DATABASE="hr/dbs/workers.dbf"
FIELDS="employee CHAR(40),
title CHAR(20),
salary NUMBER(7.2),
started DATE,
fulltime BOOL,
comments MEMO">

Identifying the Database

The NAME attribute specifies an alias for the database. An alias is simply a name that you assign to the database when you open it. Databases are almost always referred to in Miva Script tags using a database alias, rather than the actual physical filename of the database. The same database file can be open twice or more simultaneously with different aliases. The alias in the example above is 'worker_db'. If you want, you can use a different alias for the database the next time you open it.

The DATABASE attribute contains the actual filename of the new database to be created, possibly prepended with a directory path. This location is relative to the data directory for the current user. If you are creating the database in a subdirectory of the data directory, you must create the subdirectory first, using the fmkdir built-in function or explicitly through your operating system. You can use any file extension for the database file, but .dbf is recommended. The database file in the example above is hr/dbs/workers.dbf.

Defining Database Fields

The FIELDS attribute contains a comma-separated list of field names with their data types and parameters. Database field names can be up to 10 characters in length.

Fields can be assigned one of five different data types: CHAR (character), NUMBER, DATE, BOOL (boolean/logical), and MEMO (dynamic length).

CHAR field definitions must specify the maximum length of the data in that field. For example, 'CHAR(40)' specifies a maximum length of 40 characters. The maximum length of a character field is 254 characters.

NUMBER field definitions can specify the number of digits allowed in the number. This specification can have two formats: 'NUMBER(N)' indicates that the number can have N digits, but cannot contain a decimal point; 'NUMBER(N.M)' indicates that the number can have N digits before the decimal point and M digits after the decimal point. For example, 'NUMBER(7.2)' specifies 7 digits before the decimal point and 2 digits after. If no digit specification is provided, then NUMBER(19) is assumed.

Numeric values with more than the specified number of decimal places will be truncated. Numeric values with more than the specified number of digits before the decimal place will not be entered in the database.

DATE fields have a fixed length. Dates have the form YYYYMMDD.

BOOL fields have a fixed length. These fields have the value '0' (false) or '1' (true).

MEMO fields have a dynamic length. MEMO data is stored in a separate file that has the same name as the database file, but with the .dbt file extension. All of the MEMO fields for all of the records in the database are stored in this file, but they are accessed individually, just like any other type of field. You should not delete this file.

The example above creates five fields: two CHAR fields named employee and title with maximum lengths of 40 and 20 characters, respectively; a NUMBER field called salary whose values can have 7 digits before the decimal and 2 after; a DATE field called started; a BOOL field called fulltime; and a MEMO field called comments.

Specifying the Primary Database Alias

<MvPRIMARY>

Several database aliases can be open at the same time, but one alias is said to be the primary alias. This is the default alias for all database operations (except creating and opening a database). If the NAME attribute is not specified by a database tag (except <MvCREATE> and <MvOPEN>), that tag's action will be performed on the database pointed to by the primary alias. By default, the primary alias is the last open to be opened with <MvOPEN> or <MvCREATE>. If more than one alias is open, <MvPRIMARY> makes the alias db_alias the primary one (this value is required). db_alias must currently be open. <MvPRIMARY> is an empty tag.

Syntax:

<MvPRIMARY NAME="db_alias">

Note: For convenience, this manual will refer to "the database pointed to by the primary alias" as "the primary database".

Accessing Fields in a Record

When you are working with a database, Miva Script maintains the notion of a current record, or of a record pointer that points at the current record. A record can become the current record because you've just created it with <mvADD>, or because you've navigated to it using <MvFIND>, <MvGO>, or <MvSKIP>.

Miva Script creates variables that correspond to the fields of the current record in each open database, and it is through these variables that your program can read and write the data in the record.

For each database field of type CHAR, NUMBER, BOOL, or MEMO, a variable named db_alias.d.fieldname is created, where db_alias is the name under which the database is aliased, and fieldname is the field name. When a particular record is the current record, the variable db_alias.d.fieldname automatically contains the value of that record's fieldname field. For example, if the database worker_db has a field called title, then the following expression will display the current record's title field:

<MvEVAL EXPR="{worker_db.d.title}">

For each field fieldname of type DATE, special variables are created with the following values:

fieldname_day
Contains the day of the month (as a two digit number)
fieldname_month
Contains the month of the year (as a two digit number)
fieldname_year
Contains the year (as a four digit number)
fieldname_raw
contains an exact date in yyyymmdd string format

Suppose the database with alias worker_db has a DATE field called started, and the date stored in the started field for the current record is April 16, 1998.

<P>Date: <MvEVAL EXPR="{worker_db.d.started_day$'/'$worker_db.d.started_month$'/'$worker_db.started_year}"></P>
<P>Raw date: <MvEVAL EXPR="{worker_db.d.started_raw}"></P>

This code will display:

Date: 16/04/1998
Raw date: 19980416

See the section, "<MvADD>" for more examples of using database records.

Note: Database variables are automatically right-trimmed. The length of the contents of a database variable is equal to the actual length of the contents assigned to the corresponding field, minus any extra spaces on the right. For example:

<MvASSIGN NAME="db.d.name" VALUE=" cow ">
<MvADD>
<MvEVAL EXPR="{len(db.d.name}">

This gives the result '4'.

In general, the length of a database variable is not equal to the length of the corresponding database field, as defined with <MvCREATE>.

If you are referring to fields of the primary database, you can use d.fieldname instead of the longer form db_alias.d.fieldname. If the primary database does not have a field called fieldname, then the value of d.fieldname will come from the open database that does have a field fieldname and was most recently the primary database (a database becomes the primary database when it is created, opened, or made primary using <MvPRIMARY>). If no open database has the field fieldname, then d.fieldname is undefined. If the variables s.fieldname and l.fieldname do not exist, then the variable fieldname (that is, without a prefix) is equivalent to d.fieldname.

Tip: Avoid this common error: because of Miva Script's variable scope rules, if the variable vname exists and the primary database db_alias has a field called vname, the value of the variable vname will be interpreted as the value of db_alias.d.vname. Consider the following assignment of a value that will be stored in the database:

<MvASSIGN NAME="db_alias.d.title" VALUE="{title}">

This will have the effect of assigning db_alias.d.title its current value, because title is interpreted as db_alias.d.title. To avoid this, do one of the following:

Specify the global variable:

<MvASSIGN NAME="db_alias.d.title" VALUE="{g.title}">

( g.title must be specified both when it is assigned a value itself, and when its value is assigned to the database variable db_alias.d.title)

Use a different variable name:

<MvASSIGN NAME="db_alias.d.title" VALUE="{f_title}">

The same variables that you use to display the fields of the current record are also used when you want to update a record with <MvUPDATE> or add a record with <MvADD>.

Values for the special variables db_alias.d.totrec (total number of records in the database with alias db_alias) and db_alias.d.recno (current physical record number in the database with alias db_alias) are also set automatically when a database is open. The boolean variable db_alias.d.eof is true if the end of the database with alias db_alias has been reached. When referring to the primary database, d.totrec, d.recno, and d.eof; totrec,recno, and eof (without the prefix) are equivalent to the d. variables if no s. (system) or l. (local) variable with the same name exists.

Note: The physical record number of a record may change if the database is packed (that is, some records are physically deleted). For this reason, you should not code in a way that uses the physical record number as a unique identifier by which a record can be referred to. You may instead wish to define a database field in which you store a unique identifier for each record.

Opening and Closing Databases

The <MvOPEN> and <MvCLOSE> tags are used to open and close databases.

<MvOPEN>

Opens the database with filename db_file, under the alias db_alias, and makes it the primary database. The record pointer is positioned at the first record. NAME and DATABASE are required. If indexes have been created for the database, they can optionally be loaded when the database is opened. TYPE is optional for xbase3 databases; it must have the value 'odbc' if you are opening an ODBD database. <MvOPEN> is an empty tag.

Syntax:

<MvOPEN
NAME="db_alias"
DATABASE="db_file"
TYPE="xbase3|odbc"
INDEXES="index1.mvx,index2.mvx,...">

A database must be open before you can use it. When a database is created, it is opened automatically. Usually, however, the script that you used to create the database will be run only once, so if you use other scripts to manipulate the database, you have to explicitly open the database, using the <MvOPEN> tag. A database stays open until the script terminates, or until you close it explicitly with <MvCLOSE>. The next time you use the database, you have to open it again. If the current script makes a call to another script (or even the same script) the database will have to be opened again if operations are to be performed on it.

The following is an example:

<MvOPEN
NAME="worker_db"
DATABASE="hr/dbs/workers.dbf"
INDEXES="idx1.mvx,idx2.mvx">

When you open a database it becomes the primary database for the program regardless of how many other databases were previously opened or the order in which they were opened. A database becomes the primary database by default if it is the one most recently opened. However, you can specify a new primary database with <MvPRIMARY>.

NAME and DATABASE are required. The alias specified by NAME doesn't have to be the same one used when the database was created. You can also have the database open multiple times simultaneously under different aliases, and update it under any of these aliases.

The optional INDEXES attribute contains a comma-separated list of index files that will be associated with the database being opened. All indexes are updated when records are added or updated, or when the database is packed. The first index file in the list will become the main index; it governs the way that the database will be sorted for navigation.

If the program will be modifying the database that is being opened, all relevant index files should be loaded so that they will be properly updated (you can also update the indexes at a later time using <MvREINDEX>). If a database is being opened only for reading information, an index is not necessary (unless you are using <MvFIND>), although using one will speed up access.

Closing a Database Alias

<MvCLOSE>

Makes the database alias db_alias unavailable; the database file may still be open under a different alias. If NAME is not specified, the primary database (the one most recently opened with <MvCREATE> or <MvOPEN>) is closed. <MvCLOSE> is an empty tag.

All open databases are closed automatically when a Miva Script program finishes executing, however, you may wish to close a database explicitly when you've finished with it, to prevent the database being used accidentally.

Syntax:

<MvCLOSE NAME="db_alias">

Adding, Updating, and Deleting Records

The following sections explain how to modify the data in a database. Database and index files are locked at the record level automatically when records are added or updated to prevent data corruption (that is, two programs cannot update the same record at the same time). Entire files can be locked using <MvLOCKFILE>.

Adding a Record to a Database

<MvADD>

<MvADD> adds a record to the end of the database that has alias db_alias and positions the record pointer at that record. If NAME is omitted, the record is added to the primary database. Before <MvADD> is executed, the information to be stored in each field that you want to insert in the record must be assigned to variables (described below) corresponding to each field. <MvADD> locks the record that is being added. <MvADD> is an empty tag.

Syntax:

<MvADD NAME="db_alias">

The following is an example that adds a record to a database:

<MvOPEN
NAME="worker_db"
DATABASE="hr/dbs/workers.dbf"
INDEXES="idx1.mvx,idx2.mvx">
<MvASSIGN NAME="worker_db.d.employee" VALUE="Walter Skinner">
<MvASSIGN NAME="worker_db.d.title" VALUE="Assistant Director">
<MvASSIGN NAME="worker_db.d.salary" VALUE="150000">
<MvADD>

Instead of explicitly assigning values to database fields, <MvADD> obtains values implicitly, from variables whose names are the same as the field names (except possibly for prefixes).

In this example, the database has fields called employee, title, and salary (also started, fulltime, and comments, but we're not using those here). The three <MvASSIGN> tags assign values to three variables whose names are of the form db_alias.d.field_name, where db_alias is a database alias ('worker_db' in this case), d is a special prefix standing for 'database', and field_name is the name of a database field. Since 'worker_db' matches the name of the current primary database, and the three field_names match the names of fields in the primary database, <MvADD> stores the three assigned values in the new record.

The index files idx1.mvx and idx2.mvx associated with the database are updated when an <MvADD> is executed.

Note: Assigning values to variables for addition to a database must be done after the database is opened.

The db_alias and d prefixes are optional for variables whose values are to be stored in the database, provided that the values are to be stored in the primary database. For example, the following two blocks of code would have worked the same as the one in the example above:

<MvASSIGN NAME="d.employee" VALUE="Walter Skinner">
<MvASSIGN NAME="d.title" VALUE="Assistant Director">
<MvASSIGN NAME="d.salary" VALUE="150000">
<MvADD>
<MvASSIGN NAME="employee" VALUE="Walter Skinner">
<MvASSIGN NAME="title" VALUE="Assistant Director">
<MvASSIGN NAME="salary" VALUE="150000">
<MvADD>

Variables of the form d.field_name or just field_name can be used to assign values to be stored in any database. By contrast, values of variables of the form db_alias.d.field_name will be stored only in the database with alias db_alias.

The longer form of the variable name is used to make your code more readable and easier to maintain. If you use the db_alias prefix there is no doubt about what the variable is being used for.

Date Fields

Recall that for a date field fieldname, Miva Script creates variables fieldname_year, fieldname_month, fieldname_day, and fieldname_raw. fieldname_raw contains the date's value in yyyymmdd format. You can assign a value to a date field in three ways:

(In each case you must follow the variable assignment(s) with <MvADD>.)

The following two blocks of code have the same effect (assume that the active database worker_db has a date field called started):

<MvASSIGN NAME="worker_db.d.started_year" VALUE="1992">
<MvASSIGN NAME="worker_db.d.started_month" VALUE="12">
<MvASSIGN NAME="worker_db.d.started_day" VALUE="05">
<MvADD>

Or:

<MvASSIGN NAME="worker_db.d.started_raw" VALUE="19921205">
<MvADD>

Dates can also be assigned in time_t format. time_t is the number of seconds elapsed since the beginning of Coordinated Universal Time (C. U. T), at 00:00:00 on January 1, 1970. Miva supplies this value via the time_t and dyn_time_t built-in variables; time_t represents the time the script started running; dyn_time_t is updated dynamically when it is used in the script. The built-in function mktime_t generates a time_t value, given the date, time, and timezone. If you know the time_t value for the date you want to assign, you can assign it to the database variable directly. For example:

<MvASSIGN NAME="worker_db.d.started" VALUE="{time_t}">
<MvADD>

(Notice that a value is assigned directly to the started variable.)

Note: When you make an assignment of this kind, the value actually stored in the date variable (and the database) is the time_t value as of 00:00:00 on the date in question, not the exact time_t value that was assigned. The following code will display two different values (unless the script is run exactly at midnight!):

<MvEVAL EXPR="{time_t}">
<MvASSIGN NAME="d.datevbl" VALUE="{time_t}">
<MvEVAL NAME="{d.datevbl}">

The same set of variables is used for both reading and writing to a record. Since these variables automatically adopt the values of the fields of the current record, any variables in this set that you do not explicitly assign values to before doing an <MvADD> will still have the values for the current record, and these will be written to the new record. For this reason, you should assign all relevant variables a value before using <MvADD>. Variables for fields to which you do not want to assign an explicit value at this time should be assigned null values.

Updating Records

<MvUPDATE>

Modifies the current record of the database that has alias db_alias, with the contents of the variables corresponding to the database fields. If NAME is omitted, the current record in the primary database is updated. <MvUPDATE> locks the record that is being updated. <MvUPDATE> is an empty tag.

Syntax:

<MvUPDATE NAME="db_alias">

Here is an example that modifies a database record:

<MvOPEN
NAME="worker_db"
DATABASE="hr/dbs/workers.dbf"
INDEXES="nameidx.mvx">
<MvFIND VALUE="Skinner">
<MvASSIGN NAME="worker_db.d.title" VALUE="Director"
<MvASSIGN NAME="worker_db.d.salary" VALUE="250000">
<MvUPDATE>

In this example, the database is opened with <MvOPEN> and the desired record is found with <MvFIND>. Then values are assigned to two variables corresponding to the title and salary fields in the worker_db database: worker_db.d.title is set to 'Director' and worker_db.d.salary is set to '250000'. Finally <MvUPDATE> stores these values in the current record.

Deleting a Record

<MvDELETE>, <MvUNDELETE> and <MvPACK>

<MvDELETE> marks for deletion the current record in the database that has alias db_alias. Records marked for deletion accumulate until they are actually deleted from the database with <MvPACK>. These records are also visible to database searching and navigation until the database is packed. If the current record has been marked for deletion, the boolean variable db_alias.d.deleted is set to true (deleted or d.deleted can be used when referring to the primary database).

<MvUNDELETE> removes a mark set with <MvDELETE> from the current record in the database that has alias db_alias, stopping the record from being deleted by the next <MvPACK>.

<MvPACK> permanently removes all records in the database that have alias db_alias that have been marked for deletion with <MvDELETE>. Changes in database file size and in record counts resulting from <MvPACK> are not recorded until the active database is closed. All open index files are automatically updated after <MvPACK >is executed. Use <MvREINDEX> to update closed indexes.

For each of these tags, if NAME is omitted, the action is applied to the primary database. All of these tags are empty tags.

Syntax:

<MvDELETE NAME="db_alias">
<MvUNDELETE NAME="db_alias">
<MvPACK NAME="db_alias">

Tip: When a database is packed, physical record numbers (as provided by the recno built-in variable) are reset as required. For this reason, you should not code in a way that uses the physical record number as a unique identifier by which a record can be referred to. You may instead wish to define a database field in which you store a unique identifier for each record.

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

Using Database Indexes

When records are added to a database, they are simply added at the bottom of the 'logical' table structure. Unless you explicitly added them in a specific order, the records will not be ordered in any way. Often you will want to be able to group or order records according to the values of certain fields (for example, to sort names alphabetically, or group together all employees with the same job title). For this you can create an index: instead of physically reordering the records, a database index changes the way the order appears to various Miva Script database commands.

An index performs a logical (rather than physical) reordering of the records in a database. This means that the order of the records in the database file doesn't actually change; however, if an index is open, some of the commands that operate on the database behave as if the ordering had changed: specifically, <MvSKIP> and <MvFIND> (in fact, <MvFIND> requires an index).

The reordering is based on the values returned by a key expression that is evaluated when the index is created or updated: these values can be, and often are, the values in a single field, but they can also be the concatenated values in two or more fields, or some other manipulation of the field values.

A database can have one or more indexes. You would create more than one index if you wanted to be able to switch indexes to search on different index key expressions. Even though a database can have several indexes open at the same time, only one of these will be the main or controlling index. The main index is the index that is used when you are searching the database.

All open indexes are updated automatically and immediately if the contents of the database are changed. In particular, if the indexed field of the current record changes, the record's position in indexed order may change, but the record pointer will continue to point to that record. This has an impact on the results given by the database navigation tags <MvFIND> and <MvSKIP>.

If you are opening a database only for searching, there is no need to open more than one index. If you are opening a database for updating, you should open all indexes that you may wish to use at any point with this database, so that they will be correctly updated (they can also be re-generated later). You can open indexes for a database (and make one of them the main index) using the <MvOPEN> and <MvSETINDEX> tags.

You should not attempt to modify index files directly, as this is likely to corrupt the index.

Creating a Database Index

<MvMAKEINDEX>

Summary

<MvMAKEINDEX> creates an index file filename for the database with alias db_alias. If NAME is omitted, the index is created for the primary database. In this index, records are arranged in order according to the value of the key expression key_expr, and any flags that are present. INDEXFILE and EXPR are required. The EXPRESSION_TYPE attribute is optional, having a value of either "expression" (the default) or "variable".

The database to which the index applies must exist and be open under the alias db_alias when the index is created, though it need not contain any records. The newly-created index becomes the main index for that database. We recommend using the extension.mvx for index files. <MvMAKEINDEX> is an empty tag.

Syntax:

<MvMAKEINDEX
NAME="db_alias"
INDEXFILE="filename.mvx"
EXPR_TYPE ="expression"
EXPR="{key_expr}"
FLAGS="[ascending|descending],[unique|nounique],[string]" >

OR:

<MvASSIGN name = "variablename" value ="(db_alias.d.field)">
<MvMAKEINDEX = "db_alias" INDEXFILE = "filename.mvx"
EXPR_TYPE ="variable" EXPR = "variablename"

A key expression is a Miva Script expression that 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. A key expression can be any Miva Script expression; except in unusual circumstances, the key expression will be in some way based on field values in the database. A common choice would simply be a variable corresponding to one of the fields in the database.

The key expression may not exceed 500 characters in length, and may not evaluate to more than 100 characters.

If you have a database aliased as worker_db that has a field called salary, you could create an index that orders the record according to the value of the salary field, as follows:

<MvMAKEINDEX
NAME="worker_db"
INDEXFILE="salary.mvx"
EXPR="{worker_db.d.salary}">

Suppose the sequence of records before indexing was:

Childs
50000
Kostick
30000
Kerr
100000
Yan
15000

After indexing, the apparent sequence of records will be:

Yan
15000
Kostick
30000
Childs
50000
Kerr
100000

For each record, the variable worker_db.d.salary is evaluated and the records ordered accordingly.

Note: Remember to put curly brackets, '{' and '}', around index key expressions.

A variable corresponding to a field in a key expression can be written in the form db_alias.d.fieldname, d.fieldname, or just 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.

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 as follows:

<MvMAKEINDEX
NAME="users"
INDEXFILE="names.mvx"
EXPR="{users.d.lname $ users.d.fname}">

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

Flags

The FLAGS attribute lets you supply further instructions for creating the index file.

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.

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 should be used off-line, with Miva Mia. You can then re-install 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.

Setting a New Main Index

<MvSETINDEX>

The specified index files become the index files for the database opened with alias db_alias. If NAME is omitted, the index files are opened for the primary database. The first file listed becomes the main index for the database. <MvSETINDEX> closes any open index files for this database before opening the specified index files. The record pointer moves to the first record in the database (in indexed order) after <MvSETINDEX> is executed. <MvSETINDEX> is an empty tag.

<MvOPEN> and <MvMAKEINDEX> will also open listed index files for the specified or primary database.

Syntax:

<MvSETINDEX
NAME="db_alias"
INDEXES="f1.mvx,f2.mvx,...">

Recreating Index Files

<MvREINDEX>

<MvREINDEX> recreates all open index files for the database with alias db_alias, updating them to reflect the current contents of the database. If NAME is omitted, the index files for the primary database are recreated. Use <MvREINDEX> to incorporate all changes made to the database into the open index file(s), if they were not open when the database was updated. <MvREINDEX> is an empty tag.

Syntax:

<MvREINDEX
NAME="db_alias">

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

Moving Around in a Database

This section explains several methods for navigating to specific records in a database. See the section "Configuring runtime error handling" for information on how to test the results of a database navigation tag.

Finding Records

<MvFIND>

<MvFIND> performs a case-sensitive search for search_value in the index of the database with alias db_alias, and moves the record pointer to the first matching record in the database. If NAME is omitted, the primary database's index will be searched. To succeed, the search must match starting at the first character in the indexed value. The first record found by <MvFIND> will differ depending on which index is the controlling index. At least one index must be open for the database being searched. <MvFIND> is an empty tag.

If the EXACT flag is set, the search will succeed only if search_value matches the entire indexed value, rather than just a substring starting at the first character. When searching for a number, set the EXACT flag.

Syntax

<MvFIND
NAME="db_alias"
VALUE="search_value"
EXACT="EXACT">

In the example below, if the index file names.mvx contains an index based on the employee field, the <MvFIND> will search for the first record in the database for which the employee name starts with 'Mary'.

<MvOPEN
NAME="worker_db"
DATABASE="hr/dbs/workers.dbf"
INDEXES="names.mvx">
<MvFIND NAME="worker_db" VALUE="Mary">

Since <MvFIND> always finds the first matching record, subsequent calls to <MvFIND> will find the same record again. Therefore, if you want to find all records matching a particular value, you should use other techniques (such as <MvSKIP> in conjunction with an <MvWHILE> loop) to locate the rest of the records in the database that match. The advantage of starting with an <MvFIND> is that you can quickly locate the first such record. Since the database is indexed, you can be certain that other matching records will directly follow this one in indexed order. (<MvSKIP> will skip in indexed order if the database is indexed.)

This example uses <MvFIND> to locate the first record in which the name starts with 'Mary', and then uses an <MvWHILE> loop to find the others.

<MvCOMMENT>Open the database.</MvCOMMENT>
<MvOPEN NAME="worker_db" DATABASE="workers.dbf">
<MvCOMMENT>Create an index based on the employee (employee name) field
</MvCOMMENT>
<MvMAKEINDEX NAME="worker_db" INDEXFILE="names.mvx" EXPR="{d.employee}">
<MvCOMMENT>Find the first 'Mary': MvFIND searches in the index.</MvCOMMENT>
<MvFIND NAME="worker_db" VALUE="Mary">
<MvCOMMENT>Now loop through all the records starting with 'Mary'
by testing that the first four characters of worker_db.d.employee
consist of the string 'Mary'. Since the database is indexed, we
know that all such records are adjacent.</MvCOMMENT>
<MvWHILE
EXPR="{NOT worker_db.d.eof
AND substring(worker_db.d.employee, 1, 4) EQ 'Mary'}">
<P><B><MvEVAL EXPR="{worker_db.d.employee$' - '$worker_db.d.title}"></B></P>
<MvSKIP>
</MvWHILE>

Tip: To check whether an <MvFIND> was successful, test the value of the variable db_alias.d.eof (you can use d.eof or eof for the primary database); if it has the value 1 (true), the end of the database file was reached, and the search was unsuccessful. After an unsuccessful <MvFIND>, the record pointer points at the first record in the index. You can also test the value of the variable mvfind_error; it will have the value 'EOF' if the <MvFIND> did not succeed, and null otherwise.

Tip: <MvFIND> is always case-sensitive. If you want to do a case-insensitive search, you can create an index whose key expression converts all values to uppercase or lowercase, and then search using values in the desired case. The built-in string function toupper and tolower will help you do case conversion.

Interaction Between Indexing and Navigation

All open indexes are updated automatically and immediately if the contents of the database are changed. In particular, if the indexed field of the current record changes, the record's position in indexed order may change, but the record pointer will continue to point to that record. This has an impact on the results given by <MvFIND> and <MvSKIP>.

Suppose a database index contains the following entries, corresponding to the database's name field:

Aardvark

Dog

Giraffe

Zebra

Consider the following code:

<MvFIND VALUE="Dog">
<MvSKIP>
<MvEVAL EXPR="db_alias.d.name">

This will display the value 'Giraffe', since the <MvSKIP> skips to the record following 'Dog' in the index. Now let's see what happens if a record is changed:

<MvFIND VALUE="Dog">
<MvASSIGN NAME="db_alias.d.name" VALUE="Octopus">
<MvUPDATE>
<MvSKIP>
<MvEVAL EXPR="db_alias.d.name">

This will display the value 'Zebra'. Remember that when the name field of the 'Dog' record is changed, the index is immediately updated, as follows:

Aardvark

Giraffe

Octopus

Zebra

However, the record pointer still points to the same record, even though that record is at a different position in the index. Since the <MvSKIP> skips to the record following the current record ('Octopus') in the index, it now skips to the 'Zebra' record.

Filtering Records

<MvFILTER>

Makes all records in the database with alias db_alias that do not match the FILTER condition invisible to <MvFIND>, <MvSKIP>, and other database navigation tags; these tags will then find only the records that match both their own search condition and the filter condition. If NAME is omitted, the specified filter applies to the primary database.

If you have several open database aliases, each one can have a filter associated with it. However, a single alias can have only one filter applied to it at a time; a subsequent <MvFILTER> referring to that alias will replace the current filter. To remove a filter and not replace it, omit the FILTER attribute. (If both NAME and FILTER are omitted, the current filter for the primary database will be removed.)

<MvFILTER> is an empty tag.

The variables totrec and recno will continue to refer to the complete, unfiltered database.

Syntax

<MvFILTER
NAME="db_alias"
FILTER_TYPE="expression"
FILTER="{expression}">

OR

<MvASSIGN name ="variablename" value ="db_alias.d.field ne `' ">
<MvFILTER
NAME="db_alias"
FILTER_TYPE="variable"
FILTER="variablename">
</MvASSIGN>

The expression is retrieved from the variable once, and reexecuted for the life of the MvFILTER.

If you want to find a series of records that all match a certain criterion, you have two basic options:

Create an index sorted on whatever field(s) have the value you want to find, use <MvFIND> to locate the first record that matches your criterion, then use <MvWHILE> (using the same criterion that you used with <MvFIND>) to loop through the database records until you've read all the matching records.

If the value you want to find does not match at the beginning of a field value (it might appear anywhere in the field), you can use <MvFILTER>.

Here is a small example that illustrates the use of <MvFILTER>. Suppose the database contains the following records, in indexed order, ordered by the value of the salary field:
Name
Title
Salary
Diamond
Bottle Washer
12000
Novak
Plongeur
15000
Maloney
Dish Washer
15000
Barr
Maitre d'
35000
Tchobanian
Chief Cook
40000
Rabinovitch
Window Washer
45000

If <MvFIND VALUE="15000"> is executed, it will move the record pointer to the 'Novak' record.

Now apply a filter:

<MvFILTER FILTER="{'Washer' CIN db_alias.d.title}">

All of the records that do not satisfy the filter condition ("Title contains 'Washer'") will become 'invisible' (even though they're still in the database, and in the same locations). The database now effectively looks like:

Name
Title
Salary
Diamond
Bottle Washer
12000
Maloney
Dish Washer
15000
Rabinovitch
Window Washer
45000

Run the <MvFIND> again:

<MvFIND VALUE="15000">

Now the record pointer will be at the 'Maloney' record. Since the 'Novak' record does not match the filter condition, it is invisible to <MvFIND>.

This example uses an indexed database, but <MvFILTER> itself can be used with indexed or un-indexed databases.

Note: The variable recno cannot be used in a filter expression.

The effect on the currently applied filter of accessing a record that the filter does not apply to is undefined.

Moving to a Specific Record Number

<MvGO>

Moves the record pointer to the record whose position in physical order is row_number, in the database with alias db_alias, regardless of how the database is indexed. If NAME is omitted, the record pointer for the primary database is moved. The special values 'top' and 'bottom' for row_number move the record pointer to the first and last database record, respectively. <MvGO> is an empty tag.

Syntax:

<MvGO
NAME="db_alias"
ROW="row_number"
VIEW="odbc_db_view">

In general, the ROW value specified with <MvGO> refers to the row number in physical order, not indexed order. There are two exceptions to this: if the database is indexed, the special values 'top' and 'bottom' will move the record pointer to the first and last records in indexed order. It follows from this that 'top' and 'bottom' do not move the record pointer to the physical top and bottom of an indexed database. In this situation, you can achieve the same effect via the following:

<MvGO ROW="1">
<MvGO ROW="{totrec}">

Note: <MvGO> is supported for ODBC databases. A value for the VIEW attribute is not required when working with non-ODBC databases.

Skipping to a Record

<MvSKIP>

<MvSKIP> moves the record pointer of the database with alias db_alias a specific number of records forward or backward relative to its current position. If NAME is omitted, the record pointer for the primary database is moved. If the database is not indexed, the pointer moves according to physical record number. If the database is indexed, the pointer moves according to the order in which records are indexed. If ROWS is omitted, the pointer moves one record forward. If ROWS is negative, the pointer moves backward. <MvSKIP> is typically used for constructing loops to sequentially access file records. <MvSKIP> is an empty tag.

Syntax:

<MvSKIP
NAME="db_alias"
ROWS="number"
VIEW="odbc_db_view">

Note: <MvSKIP> is supported for ODBC databases. A value for the VIEW attribute is not required when working with non-ODBC databases.

Obtaining a Database's Structure

<MvREVEALSTRUCTURE>

<MvREVEALSTRUCTURE> creates a database with filename filename (required) that contains information about the structure (as defined by <MvCREATE>) of the source database with alias db_alias. If NAME is omitted, the primary database is assumed. The newly created database contains one record for each field in the source database. The source database must be open when <MvREVEALSTRUCTURE> is executed. <MvREVEALSTRUCTURE> is an empty tag.

Syntax:

<MvREVEALSTRUCTURE
NAME="db_alias"
DATABASE="filename">

where:

NAME is the alias of the source database (optional)

DATABASE is the full path of the database being created

<MvREVEALSTRUCTURE> creates a new database with information about the open source database (db_alias). The records in this database correspond to the field definitions used in the <MvCREATE> tag that created the source database; there will be one record for each field definition for the source database. <MvREVEALSTRUCTURE> does not open this new database. If you want to access information in the new "MvREVEALSTRUCTURE" database, use <MvOPEN> to open it.

Each record will contain the following fields:

field_name: The name of a field in the source database.

field_type - A letter that specifies the type of field:

C - Character data (CHAR)

N - Numeric data (NUMBER)

D - Date (DATE)

L - Boolean data (BOOL)

M - Memo data (MEMO)

field_len - The number of characters allowed in the field; for CHAR fields, this is the number of characters specified in the field definition; for NUMBER fields, this is the sum of the number of characters before and after the decimal, plus 1 (for the decimal); the other types of fields have fixed values for field_len: 8 for DATE, 1 for BOOL, and 10 for MEMO.

field_dec - For numeric data only, the number of decimal places allowed to the right of the decimal point.

Accessing ODBC Data sources

Miva Script access to ODBC data sources is currently supported only with Miva Mia.

Connecting to an ODBC Data source

The string in the following example will connect to an ODBC data source called 'Sample', using the user id (uid) 'dba' and the password 'sqlpwd'. Some ODBC drivers may allow or require additional values.