baserunner 4.6

introduction | configuration | databases | expressions | sessions | templates | variables |
queries | email | data entry |  passwords | examples | license & registration

Databases

• About Tables
• About Indexes
• About Relations
• About basemaker
• About Database Maintenance
• About Importing Data

A database consists of one or more tables along with the memo and index files associated with those tables. If a database contains more than one table, the relationship(s) among those tables needs to be specified. baserunner uses standard FoxPro or dBase IV format tables (*.dbf). Most DBMS software, including MS Access (along with many spreadsheets and even word processors) can produce dbf format databases, either as a native format or through an export routine. In the documentation, "database" is often used interchangeably with "table."

The utility _basemaker script can create and and modify tables and their index files.

The main database for a baserunner script is determined by the table specified in the _dbf variable (along with the index tag selected through the _tag variable and and relations specified in the _relate variable). The main database is always used when browsing data; And it is the database used when generating queries (i.e. the _filter variable, created through a query action,  always refers to the main database). Other databases may be used through qualified list or seek blocks (i.e. they contain their own dbf attribute).

Tables

A table contains data stored in records containing any number of fields. Each field has a particular type with its own requirements and format. baserunner supports these field types:

In addition to these standard field types, baserunner will recognize these later FoxPro field types: Float, Integer, Double, Currency (all treated identically to Numeric), and DateTime. Memo fields supported by the FoxPro and dBaseIV versions of baserunner are not compatible with each other (and neither are compatible with older format dBase III/FoxPlus style memos). baserunner is available compiled to support either dBase IV or FoxPro format table, indexes, and memos. baserunner is optimized for FoxPro formats (from version 2.5 through the latest version of Visual FoxPro), and unless some compatibility issues with other applications exist, you should always prefer the FoxPro version of baserunner.

baserunner has no particular restriction on the size of a table it can use. Technically, you could use a table up to about 1GB in size; although in practical terms the size of the table is limited by performance considerations. The larger the table, the more memory and processor time baserunner will require. On a busy server, these issues are very important. The best rule of thumb is that the larger your table, the simpler your application should be.

Index files and tags

An index contains one or more tags that provide different ways of ordering the records in the table. Without an index tag selected the records in the table are available only in the physical order in which they appear in the table. An index tag resets that order to the expression contained in the tag. Index tags are necessary for seeking and for setting relations between tables. They can often make queries much faster.

While an index file is not required with a baserunner table, many features that make use of index files will be unavailable if no index is present. baserunner uses production index files. These files contain multiple index tags. Each index tag consists of a tag name, an expression (most often, simply the field name), an optional logical filter expression, a Boolean flag indicating whether the tag should contain only unique values (excluding all duplicates), and a Boolean flag indicating whether the order is ascending or descending . The index file always has the same name as the table (e.g. mydata.dbf/mydata.cdx).

• The expression for an index tag should evaluate to less than 100 characters (e.g., if you have a 254 character field, index a substr() of the field rather than the entire field).
• If you wish to hide deleted records, you may include ".not.deleted()" as part of the filter expression for the tag.
• You may use any logical expression in the filter (just as you would in a query).
• If you want the ordering of a character field to be case insensitive, you should create the tag using the upper() expression.
• Memo fields should not be indexed. Logical fields may be indexed (when using the FoxPro version of baserunner), but this is usually not of much value.
• A tag name may be no more than 10 characters.

The FoxPro version of baserunner used .cdx format index files. The dBaseIV version of baserunner uses .mdx format index files. baserunner does not support older index formats (.ntx or .idx). The current index tag is set through the _tag variable using the name of a tag in the index file as the value. With both FoxPro and dBaseIV tables, a flag is set within the dbf file if the production index file exists. If that flag is set the index file (*.cdx/*.mdx) is required to be in the data directory with the table. If is is missing, baserunner will be unable to open the table. When an valid index tag is selected, baserunner sets the _!indexed variable to true.

Unique tags are especially useful when creating query menus, and they may be used to restrict entry of new records that contain the same data as existing records.

baserunner will normally keep the index files up-to-date automatically. There may be occasions when you need to reindex the databases--which is easily accomplished using the *reindex administrative action.

Relations

Relations provide a means for using several tables as if they were single database. A relation can include any number of tables (limited mostly by performance considerations). Each node of a relation has a master table and a slave table. The information available from the slave is controlled by the master. The master and the slave must have some field (or evaluated expression) in common. That field (or expression) must be used as the expression of an index tag in the slave table. The relationship works by baserunner doing an automatic lookup of the common information from the master into the slave.

baserunner supports three types of relationships among tables:

exact: (the default type) a one-to-one relation where a record in the master table is related to one record in a slave table. 
approximate: a many-to-one relation where any number of records in the master table are related to a one record in a slave table.
scan: a one-to-many relation where a record in the master table is related to any number of records in a slave table.

The requirements for any relation between tables are that each of the tables must have a field that contains identical information and that that field in the slave table has an index tag on that field. The table specified in the _dbf tag is the top master table for the relation. Within a set of relations, a table that is a slave to the top master may be used as a master for another slave.

When a relation is active, either as the main database for the application or as the database for a list, expressions referring to fields in the master table are entered normally. Expressions referring to fields in any slave tables need to have the table name added along with the "->" characters, for instance, student->l_name (You may also use student.l_name, which may simplify some HTML issues).

To create a relation among two or more tables, you need to create several variables that will give baserunner the information it needs to construct the relations. The first variable is _relate. The value of the _relate variable contains the names of other variables (in a space delimited list, e.g. _relate_1 _relate_2), each of which gives the specific information needed for one node of the relation.

The variables that specify each node of the relation contain several attributes: these are:

For instance, for a very simple relation, say, you have 2 tables, authors.dbf and works.dbf. Authors has 3 fields, code, first, and last. Works has 3 fields, code, title, date. The relation between  these tables will be created on the code field. The first requirement is that the code field must contain identical information. Second, the slave table, in this case Works, must have an index tag on the code field (lets call it a_code). To create this relation, the setup block for the script will have the following:

_dbf=authors
_relate=_node1
_node1=slave="works" tag="a_code" expr="code" type="scan"

For our example, Authors.dbf contains:

In Works.dbf we find:

If we have the following code in a template:

<browse limit="1">
    <b>{trim(first)} {trim(last)}</b>
    <ol>
        <scan>
            <li><i>{trim(works->title)}</i>, {str(works->date)}</li>
        </scan>
    </ol>
</browse>
<a href="{_baserunner}?_act=back">&lt; Back &gt;</a>
<a href="{_baserunner}?_act=next">&lt; Next &gt;</a>

In this example 3 pages will be available, on for each record in the master table. Each page displayed will contain an author's name and an ordered list of his works, e.g.:

James Joyce

1. Ulysses, 1922
2. Dubliners, 1916
3. A Portrait of the Artist as a Young Man, 1916

< Back > < Next>

William Faulkner

1. Go Down Moses, 1940
2. Absalom, Absalom!, 1936
3. The Sound and the Fury, 1929

< Back > < Next>

Mark Twain

1. Life on the Mississippi, 1883
2. Tom Sawyer, 1876
3. Huckleberry Finn, 1884

< Back > < Next>

The relation created in the _classes example uses:

_dbf=classes
_relate=_node1 _node2
_node1=slave="enroll" tag="c_code_tag" expr="code" type="scan"
_node2=master="enroll" slave="student" tag="id_tag" expr="stu_id_tag"

In this case, classes.dbf is the master table for the relation. The first node relates the table enroll.dbf to the master table using the field "code" in classes.dbf and the index tag c_code_tag in enroll.dbf. Since this is a scan relationship, any number of records in enroll.dbf can match. The second node of the relation uses enroll.dbf as the master. It is related to the database student.dbf using the field "stu_id_tag" in enroll.dbf and the index tag "id_tag" in student.dbf. This node is (by default) an exact relationship. Each record in enroll.dbf will match only one record in student.dbf.

With this relation, when a record in classes.dbf is selected, a lookup is done in enroll.dbf that returns all the records that match the classes.dbf code field. A further lookup is done using each of the stu_id_tag fields in enroll to match a single record in student.dbf. The order of the relation nodes is important: If a "master" is specified for a node, that database must have been previously used as a slave.

A relation may also be used in a fully-qualified list. The list attribute "relate" is used to specify the tag that contains the tag names or the database node(s). So, for instance, in another application, you could create the same relationship for a list using:

<list limit="12" dbf="classes" relate="_testr" filter="'Intro'$title" fail="<br>Nothing here<br>">
    <b>{alltrim(title)}</b><br>
    <blockquote>
        <scan>
            {alltrim(student->f_name)} {student->l_name}<br>
        </scan>
    </blockquote>
    <stat age="student->age">
        {average(age,0)}
    </stat>
</list>

Where the following variables are specified in <setup>

_testr=_node1 _node2
_node1=slave="enroll" tag="c_code_tag" expr="code" type="scan"
_node2=master="enroll" slave="student" tag="id_tag" expr="stu_id_tag"

When a relationship is in effect, baserunner sets the variable _!related to true.

Note: A relation can only be set when data exists in each of the tables (masters and slaves). Any empty table will cause the relation to fail. When you are creating a script, you need to add some data to your tables before the relations will function.

basemaker

The basemaker script allows you to create and edit tables and indexes. You can start the basemaker script with a link to:

http://127.0.0.1/cgi-bin/baserunner.exe/_basemaker

Using basemaker require your administrative password. You can use basemaker to create a new table (and index) or to modify an existing table or index. Each time you edit an existing table, baserunner will backup the table and index you are working with.

basemaker provides two HTML forms to enter table and index information. The form used to create the table structure asks for the following information:

• Name: The name of the field. This name must start with a character (not a number or a punctuation mark), and must be no more than 10 characters long. Within the name you may use the underscore "_" character or numbers.
• Type: The type of field. This may be one of "Character," "Numeric," "Logical," "Date," or "Memo" selected from a drop down list.
• Length: The length of the field. Logical fields have a default length of 1; Memo fields have a default length of 10; Date fields have a default length of 8; Numeric fields may have a length of up to 20; Character fields may have a length up to 254.
• Decimal: The number of decimal places for a Numeric field. The number of decimals may be any number up to the length - 2 (e.g. if you have a Numeric field with a specified length of 10, you may have up to 8 decimal places: 1.12345678). You may specify no decimal places if your Numeric field will only contain integers.

The form used to create index tags asks for the following information:

• Name: The name of the index tag. Tag names have the same rules as field names. For simple index tags, you may enter the name of the field you wish to index as the tag name and leave all the other information blank.
• Expression: The xBase expression for the index tag. Normally, the expression is simply the name of a field. This may be a more complex xBase expression. For instance, if you have, say, firstname and lastname name fields, you can create a "fullname" tag using an expression like: trim(lastname)+trim(firstname). You need to take care that your expressions return less than 100 characters; otherwise the index may be unstable.
• Filter: The optional filter allows you to add a logical xBase expression that will filter the data contained in the tag. For instance, you could add .not.deleted() as a filter for the tag, and all records marked for deletion in your table would be excluded from the tag. Similarly, you can use this feature to create very fast standard "queries." Note that this feature is not very stable when using dBaseIV format files.
• Unique: The unique flag instructs baserunner to create a tag that contains only unique values. Unique index tags are very useful for creating query menus and lists. Unique index tags can also be used to restrict data entered by users to unique values.
• Descend: The descend flag instructs baserunner to order the tag in descending (Z-A, 9-1) rather than the default ascending (A-Z, 1-9) order.

If you use basemaker to modify the field structure of a table, and the table contains data, be aware that data will only be retained if the name and type of the field is the same. You may change a character field to a memo field (if the name remains the same). And under most circumstances, you may change the length of character or numeric fields without loosing data, but, for instance, you cannot change a character field to a date field without loosing the data in that field.

Maintaining databases

baserunner provides several administrative actions that will assist in maintaining an on-line database. All of these require you administrative password. These actions are: *reindex, *pack, *backup, *delete, and *import. A form to call one of these actions might look like this (with the appropriate _act variable):

<form action="{_baserunner}" method="post">
    <input type="hidden" name="_act" value="*reindex">
    <input type="text" name="_password">
    <input type="submit" value="Reindex">
</form>

You can also create a single template containing nothing more than a <setup> block to handle backups or any of the other maintenance actions, e.g.:

<setup>
    _dbf=mydata
    _tag=mytag
    _act=*backup
    _password=mypassword
</setup>

Because the maintenance actions produce their own output (not relying on a template), there is no need to provide a _tar variable.

The *reindex action forces baserunner to reindex the current index tags. Normally baserunner will keep the index tags current without any action on your part. If this automatic index maintenance has failed for some reason, the *reindex action can fix the trouble. If a relation is in effect when this action is called, the indexes of all the tables in the relation will be reindexed. You can call this action through a form that would look something like this:

When a record is "deleted" in an xBase table, the only thing that happens is that a flag is set in the record. The benefit of this is that data may be "recalled." To actually remove data from the table, the table must be "packed." When the *pack action is called, all records, including memo fields, marked for deletion are physically removed form the table. Once a table is packed, it is impossible to recover the deleted data.

The *backup action makes a copy of the table and its corresponding index. If a relation is in effect all the tables contained in the relation will be backed up. Each back up file is given the base name of the table with the current date and time appended (as a series of integers). The backup copies of the tables are written to the data directory.

Additionally, baserunner will recognize a *delete action. This action can be used to delete multiple records from a table, based on the value of the expression contained in the _filter variable. The *delete action will only work with the table specified in the _dbf variable.

Importing data

You can import data into a table from a standard comma delimited text file (CSV). The password protected action *import instructs baserunner to read the CSV file and copy its data into a table. Both the table and the CSV file must be located in baserunner's data directory. The *import action provides a great deal of flexibility for the format of the text file. The default format for the txt file is that each field value will be separated by a comma and that text field values will be enclosed in double quotes. Each record will be written on a single row of the text file.

• The delimiter may be set to another character using the _delimiter variable. The _delimiter variable may have any single character as a value or may use "space" or "tab" to specify space or tab delimited files. The quote character may be set to another character through the _quote variable. Changing these values may be necessary if your text data contains double quotes. Text data may contain commas (or whatever delimiter you use) so long as it is enclosed in double quotes (or whatever quote character you use).
• For date fields, you must specify the format of the dates in your text file in the _dateformat variable.
• Logical fields in the text file should contain either "T" or "F". If you wish to use another value to specify true and false, those values need to be contained in the variables _true and _false.
• The table that the data will be written to is specified in the _dbf variable. The text file that contains the data is specified in the variable _csvfile. Both those files must be located in baserunner's data directory.
• To use this action, the system password must be provided in the _password variable.

The *import action does not create a table. The table must exist before you call the *import action. The fields in the table must be in the same order as the fields in the text file and the table should have the at least the same number of fields as the text file.

Ok, say you have a spreadsheet that contains names and birth dates, and some code number (first name, last name, birthdate, code) and you export that file to a delimited text file. That text file will look like:

"Mark","Glover",01/15/1960,1000
"John","Smith",10/13/1975,1001
"Andrew","Berwick",12/25/1934,1002

To import this data into a table, first, you need to create the table using basemaker with the following structure:

first, character, 20
last, character, 20
birthday, date, 8
code, numeric, 4, 0

You can also create any index tags you need.

Now, you have a text file named "delim.txt" and an empty table named "names.dbf." To move the data from the text file to the table, you need to set up a template named, say "myimport.btml," that provides baserunner the information it needs to do the import:

<setup>
_dbf=names
_csvfile=delim.txt
_dateformat=MM/DD/CCYY
_act=*import
_password=mypassword
</setup>

Since we have no logical fields and we are using the default values for the quote and delimiter characters, we don't need to specify anything for those variables. To import the data, just call the import template with something like:

/cgi-bin/baserunner.exe/_myimport

Of course, you can also use a form to gather the information.

Copyright © 1998-2002 baserunner Software (Mark Glover)