xBase3

Miva Script supports three kinds of databases: xBase3 (dBaseIII compatible) databases, which are supported on all platforms, and MySQL databases on servers running MySQL, and MivaSQL as a front end to xBase3 databases. Previous versions of Miva Script supported ODBC data sources. That support has been dropped.

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, configured for your Miva Script installation. Typically this folder is called /mivadata or /data.

Some Miva Script database commands only apply to xBase3 databases. For MySQL databases <MvOPEN>, <MvSKIP> and <MvGO>, <MvQUERY> and <MvOPENVIEW> are usually used.

Note: When possible, use Miva Merchant Mia to perform time-consuming database operations locally 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 Merchant Mia's timeout value to be as high as you like.

Introduction to xBase3 Databases

Miva xBase3 databases organize data into database tables, a simple tabular format that consists of records (i.e. rows) that each have one or more fields (e.i. columns) of the table. Table here does not mean an HTML table, but instead 'table' refers to the ordering of the data. Miva Script enables you to manipulate, order and search the data stored in tables.

This example below 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

Creating a Database Table

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. If an existing database is created, it will be overwritten and the contents lost. You can use the fexists() function to determine if the file already exists.

Database Aliases

An important aspect of working with database tables is that they are always referred to in Miva Script tags using a database alias, rather than the actual physical filename of the database. An alias is simply the value you assign to the NAME attribute when you create or open it. Other tags will regerence the table buy this NAME. The same database table can be open two or more times simultaneously with different aliases, for example using different indexes. Alias names consist of the letters a through z (upper and lower case), the digits 0 through 9, or the underscore ( _ ). Alias names can not contain the '.' (period) character.

In this example "employees" is the alias for the physical file "workers.dbf"

<MvOPEN NAME="employees" DATABASE="/myfiles/workers.dbf" INDEXES="/myfiles/emp_names.mvx">
<MvFIND NAME="employees" VALUE="{ 'Jeeves' }" EXACT="EXACT">

Opening the Database

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. You can optionally use a different alias for the database.

The Primary Database

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>. A database may also have multiple indexes open at the same time. You can also use <MvPRIMARY> to make another index the primary index for an open table, in affect changing the sort order.

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.

When adding or updating records, database variables are automatically right-trimmed to the length of the database field. A 40 character string added to a 30 character field will cause the right 10 characters to be truncated. However, a 3 character string added to the same 30 character field will NOT result in a 30 character field record. The field will still be 3 characters long.

Reading Database Records

In order to read and display, or update 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. This example uses the employees database above. The same variables that you use to display the fields of the current record are also used when you want to update a record.

<MvGO NAME = "employees" ROW = "top">
<EVAL EXPR="{employees.d.name}">

Displays: Benson

<MvASSIGN NAME="employees.d.name" VALUE="{ 'Ray' }">
<MvUPDATE NAME = "employees">

Changed to: Ray

When you are working with a database, Miva Script maintains a record pointer to the current record. A record becomes the current record when you create it with <mvADD>, or you navigated to it using <MvFIND>, <MvGO>, or <MvSKIP>.

Miva Script creates database 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, DATE, BOOL, or MEMO, a variable in the form alias.d.fieldname is created. When a record becomes the current record, the variable alias.d.fieldname is populated with the field data. For example, employees.d.name contains the name field for the current record.

If you are referring to fields of the primary database, you can use d.fieldname instead of the longer form 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. 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.

Avoid this common error, by making it standard practice to explicitly declaring the alias ( alias.d.field_name) and variable scope l.variable_name.

Special Fields

Miva Script creates additional special fields for each database.

• db_alias.d.totrec: total number of records in the database
• db_alias.d.recno : current physical record number in the database
• db_alias.d.eof: Read as true if the end of the database has been reached.
• db_alias.d.deleted: Reads as true if the records is marked for deletion.

The physical record number of a record may change if the database is packed (i.e. 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 (e.g. alias.d.id) for each record.

In the example we use EOF to read to the end of the file.

<MvWHILE EXPR = "{ NOT employees.d.EOF }">
    <MvEVAL EXPR="{ employees.d.name }">, <MvEVAL EXPR="{ employees.d.salary ROUND 2 }"> <br>
    <MvSKIP NAME = "employees">
</MvWHILE>

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 want 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. Indexes are created using the <MvMAKEINDEX> tag. You can open multiple indexes when you open a database table. Alternatively you can open additional indexes on an open table using <MvSETINDEX>.

An index is a separate file that contains a logical 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 tags that operate on the database behave as if the ordering had changed. Specifically, <MvSKIP> and <MvFIND>. In fact, <MvFIND> requires an index to be open. Using indexes dramatically improves the speed of search operations.

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, but are not limited to, the values in a single field. They can also be the concatenated values in two or more fields, or some other manipulation of the field values.

A database can have multiple 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. If you have multiple indexes open on a table and want to change the sort order or search to another index, use <MvPRIMARY> on the current table to change the primary index.

When database records are added or updated all open indexes are updated at the same time. If an index was not open when a database was updated you can update it later using <MvREINDEX>.

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 with this database, so that they will be correctly updated. 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.

Database Navigation

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

Filtering the Database

You can use the <MvFILTER> tag to make 'visible' only those records that satisfy a specified criterion. For example, you can filter out records that have been marked for deletion. so that they are not processed.

Deleting Records

Deleting records is a two step process. You first navigate to the record. Then use the <MvDELETE> tag to mark the record for deletion. The <MvPACK> tag physically removes it. This method proved an opportunity to undelete a record using <MvUNDELETE> .

Database Structure

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