Visual FoxPro's data structures

Visual FoxPro's data structures

Visual FoxPro traces its heritage to the late 1970s, when an engineer named C. Wayne Ratliff created a program to help him manage football pool statistics at work. The fundamental data structure in his fledgling database management program was a file that held the contents of a single table, but he used an extension of ".DBF", signifying "data base file." As a result, people took to calling each of these ".DBF" files a "database" (instead of the proper handle, "table"), and the misnomer stuck. As a result, two decades of desktop database developers have referred to a single table as a "database" and a collection of tables as "a group of databases."

When Visual FoxPro was introduced, one added feature was a second data structure that contained pointers to .DBF files. This data structure is referred to as a "database container," and it's a more appropriate, if not perfectly accurate, name.

So there we have it-the two fundamental data structures in Visual FoxPro are tables and database containers. There are more, however, so let's examine each of them in turn.

Tables

I'm going to start with tables instead of databases, because tables can exist without a database container. Tables existed before the database container was created, so VFP has to be able to work with them as stand-alone objects.

A Visual FoxPro table can consist of up to three physical files. The table itself has a .DBF extension and contains all of the fixed-length data in the table. An associated memo file has the same name as the table and a .FPT extension, and contains all of the variable-length data in the table. A structural index file has the same name as the table and a .CDX extension, and contains one or more sets of index pointers for the table. A fourth type of file, a stand-alone index file, has an .IDX extension and contains only one set of index pointers.

A table is limited to 2 billion bytes (2 gigabytes), or 1 billion records, whichever limit comes first. In a practical sense, a billion-record table wouldn't be of much interest to people, because the total size is still limited to 2 billion bytes, and so each record would be, er, small. A record can have up to 255 fields and can be up to 64,000 characters in length.

The .DBF file is made up of two pieces: (1) a header record and (2) zero or more data records. The header contains information about the table, such as the number of records in the table, the name of the database it's connected to (if applicable), and flags that indicate whether or not there are associated memo and structural index files. The header also contains a definition of each field in the table, including the name, size, and data type.

Each data record contains a fixed amount of space for each field in the table, plus an additional byte that stores a flag indicating whether or not the record is flagged for deletion, plus one or more bytes to hold information about nulls (which I'll discuss later). As a result, a record with three 10-character fields will actually be 31 bytes (or 32 bytes if nulls are allowed) long. Because the header record takes up a couple hundred bytes as well, you can't calculate the exact size of a .DBF file simply by multiplying the record length by the number of records in it; you have to add the result of the HEADER() function to account for the header size.

Visual FoxPro has two special types of fields-memo and general-whose data is not physically kept in the .DBF file itself. Both of these fields actually contain pointers to locations in the .FPT file for records that have memo or general data, not the data itself. As a result, these two field types can contain very large variable amounts of data-in contrast to the fixed-length fields found in the .DBF file itself. When data is added or modified, it's simply appended to the end of the .FPT file, and the pointer is adjusted as required.

Note that the existence of the memo file or structural index bytes in the table's header record does not prevent you from renaming, moving, or erasing those files from the location on disk. However, doing so will cause error conditions in Visual FoxPro. If you try to access a free table with a missing or corrupted memo file, you will be greeted with a message indicating so, and you will not be able to open the table until the condition is repaired. On the other hand, if you try to access a table with a missing or corrupted structural index file, you will be warned and offered the option to change the structural index byte in the header record. You'll have to rebuild the index, but you can still access the table's data, whereas a bad or missing memo file likely means you'll be missing some of your data. If, however, the table belongs to a database and has a primary key defined, and the index is missing or damaged, you'll get an error and you won't be able open the table at all. In this situation, you've got three choices: (1) Rely on your backup (you did make one, didn't you?), (2) Try to hack the binary structure of the .DBF file yourself, or (3) Pick up a copy of the Stonefield Database Toolkit, which will take care of this and other database-related problems for you.

Fields

A table can have up to 255 fields. A field can be one of 13 types. The first seven-character, numeric, float, logical, date, memo, and general-were available in the previous version of FoxPro, while the last six-integer, currency, double, datetime, character binary, and memo binary-are new to Visual FoxPro.

Character fields can contain any type of data that can be typed on a keyboard: characters, punctuation, numbers, and so on. The maximum character field width is 254.

Numeric fields can contain numeric data only. Float fields are the same as numeric fields; they were added for dBASE IV compatibility. The maximum width of a numeric or float field is 20 places-and that number includes the decimal point. For example, you could define a numeric field to look like

1,000,000,000,000,000,000. (19 places and a decimal)

The Fundamentals of Visual FoxPro 6.0

or

.0000000000000000000 (a decimal and 19 places)

or

1,000,000,000.000000000 (10 places, a decimal, and 9 more places)

However, this does not mean you'll get 20 places of precision. If you try to fill the above fields completely, you'll get the following results:

1,234,567,890,123,400,000 (19 places and a decimal)0.123456789012346000,0 (a decimal and 19 places)1,234,567,890.123456000 (10 places, a decimal, and 9 more places)

Logical fields contain a logical True or False. You can enter T, F, Y, or N (all case-insensitive). T or F will be displayed if you enter Y or N, respectively.

Date fields contain a date displayed according to user-specified settings but containing a string value formatted as YYYYMMDD. You can manipulate a date by adding and subtracting "numbers of days" to the date to return different dates. You can also extract the various components of the date, such as the month or day of the week, and convert the date value to a text string using native FoxPro functions. Date fields always store the four-digit year even if the display of that date shows only the last two digits, but which century is stored depends on a variety of settings.

Memo fields are variable-length fields that can contain ASCII or binary data. Examples of character data often stored in memo fields include strings that either are longer than the maximum 254 bytes allowed in a character field or whose length varies significantly. Suppose you have to store a long text description in a table, but many records won't have a description at all. If you created a 220-character-wide field, each record would use 220 bytes for that field, whether it contained data or not. Instead of doing so and potentially wasting a lot of space, you could use a memo field to store the description. Each record will require four bytes for the memo field pointer in the table itself, and only records with descriptions will have actual data stored in the memo file.

Examples of binary data stored in memo fields include encrypted data files, binary files such as original copies of programs and reports, and image files. This capability is handy if you want to provide a "copy" of something for the user to manipulate, but don't want them to accidentally (or purposely) modify the original. Storing the original in a memo field and then extracting a copy for the user to work with keeps the original away from harm.

It's important to note that accessing and manipulating data in memo fields isn't as straightforward as using regular fields in a table, but in many cases the flexibility is worth the extra work.

General fields are similar to memo fields in that they can store variable-length data, including binary data. The difference is that the contents of general fields are OLE aware-that is, the data can be understood by another OLE-capable Windows application. For example, a .WAV file created by a sound system recorder would be the data needed to generate a tune, and would be accessed by a sound player to play it.

The new fields are generally extensions of existing data types.

Currency fields contain a value that automatically contains 16 integer positions and four decimal positions. The internal representation is different so they only take 8 bytes per record. Double fields allow you to store data that requires a high degree of precision and are most often used in scientific and engineering applications.

Datetime fields contain a combination of the date and time in a single field. The field works like the date field, but to a resolution of seconds. For instance, adding the value 17 to the contents of a datetime field will increase incrementally that field's value by 17 seconds. As with the date field, the entry and display of the date and time in a datetime field can be modified to suit your individual preferences.

The character binary and memo binary fields work similarly to the ordinary character and memo fields except that the data is not translated for different code pages. A code page is a translation table for the high ASCII values according to different languages. For instance, the capital letter "A" is represented by the ASCII code 65. This "65" is stored internally in the computer, and all versions of FoxPro will then display a capital "A" when the "65" code is encountered. However, the codes above ASCII 127 have different meanings for different languages. In English, they are used to display graphic and typographic symbols specific to the English language. In other languages, however, some of those codes between ASCII 128 and ASCII 255 are used for alphabetic symbols specific to other languages-the German umlaut and the French circumflex, for instance.

If you create a table in one code page and then open it in another code page, the character data will be translated automatically. A field containing the ASCII code 204 will display one character for the English code page but a different character for the German code page. Numbers and logicals aren't a problem-they're the same everywhere. But character data is different and will be changed. If you don't want it to be changed, put the data in a binary field instead of the regular field. Examples of fields where you wouldn't want to change code pages are character fields that contain a binary value representing a key or a memo field containing binary information, such as a Word document or an image.

Nulls

Until this version of FoxPro, handling fields that didn't contain data was somewhat nebulous. FoxPro didn't differentiate between a field that contained no data and a field that contained a zero value. For instance, if you added a blank record but didn't insert data into the fields, a numeric field would be treated as if it had a zero in it. However, this is not technically correct. Because 0 is a value-just like 13 or -219.44-treating a field that didn't contain a value as if the field contained the value 0 was incorrect. Similar circumstances happened with the character and logical data types-logical fields that didn't contain data returned a logical false, and character fields returned an empty string-both of which are different than fields containing no data. Furthermore, testing to determine whether a field had a value was done with the native FoxPro EMPTY() function. However, EMPTY() returned true for numeric fields that contained a 0 and logical fields that contained a logical false.

Visual FoxPro has resolved this situation by providing the ability to designate a field as capable of containing a NULL as well as a value. This designation is part of the field's definition just as its name and type are. NULL is not a data type or a value; rather, it is an

The Fundamentals of Visual FoxPro 6.0

attribute that indicates the presence or absence of data. With NULLs, we have a way to determine if a field has a value or not.

This is often an ethereal concept, so an example might help here. One oft-used analogy is the weather map. The weatherman is making you feel bad by saying it's 78 and sunny in San Diego, 83 and sunny in Orlando, 81 and sunny in Little Rock, but there's no temperature reported in Phoenix. In the database of cities and current temperatures, the value next to Phoenix is not zero, because that would be a valid, if uncomfortable, temperature. Instead, we need to be able to indicate we don't know what the temperature in Phoenix is today-so we use a NULL.

This means when you determine the average temperature, you add 79, 83, and 81, and divide by three, instead of adding 79, 83, 81, and 0, and dividing by four.

When you create a blank record, fields with NULL designations are treated just as fields without NULL designations-character fields are empty, numeric fields contain zero, logical fields contain a logical false, and dates contain an empty date.

However, a field that allows NULLs can have a NULL placed into it. As a result, records with NULLs can be handled differently than records that contain empty strings, zeros, or logical falses. You can think of a NULL as "I don't know what this value is." This has ramifications for doing operations on fields that might contain nulls. For example, if you add a value to the contents of a field that contains a NULL, what do you get? You get a NULL, because "something" plus "I don't know what this value is" is still "I don't know what this value is." Sort of like infinity, I suppose, except that maybe it's smaller. Some operations, like aggregate functions (SUM(), AVG(), and so on) skip records with NULL values.

Indexes

A table contains records in the order they're added to the table. This order is known as "physical record order" and generally has no meaning in or relationship to the real world. Usually, however, we want to view the records in the table in a specific order-by last name, zip code, date of invoice, or whatever. Instead of changing the physical order of the records, Visual FoxPro uses a second file called an Index file that acts as a mechanism to present the records to us in a different order.

The index file consists of two "fields." The first field is simply a list of numbers from 1 to the number of records in the table. The second field contains a "pointer" to the record in the table that corresponds to the order in which the records are to be viewed. In the following illustration, the actual records are in order by name, and the date of birth of each individual is in no particular order. The index file enables us to view the records in date-of-birth order without actually changing the physical order of the table.

Original table

Table

Record # 1 Alexandra 01/01/83 F 2 Bob 12/16/54 M 3 Carl 07/13/27 M 4 Donna 11/01/21 F 5 Ed 11/13/60 M 6 Frank 03/06/70 M 7 George 09/30/34 M 8 Heather 08/05/58 F

Index file (for order by Birth Date)

Index # Table Record # 1 4 2 3 3 7 4 2 5 8 6 5 7 6 8 1

Table displayed according to Birth Date index

Table

Index # Record # 1 4 2 3 3 7 4 2 5 8 6 5 7 6 8 1 Donna 11/01/21 FCarl 07/13/27 MGeorge 09/30/34 MBob 12/16/54 MHeather 08/05/58 FEd 11/13/60 MFrank 03/06/70 MAlexandra 01/01/83 F

The advantages of this separate index file are numerous. For example, it's extremely fast to view records in this order. Instead of physically sorting the file, which would take lots of processing time and possibly a significant amount of disk space, we can use this relatively small index file. Furthermore, changes in the table-for example, a change in a birth date due to a data entry error-can be handled by modifying the pointers in the index table instead of having to physically reorder the entire file. And because the index file is significantly smaller than the table, disk overhead will be smaller. In some cases, the entire index can be cached or brought into memory, making retrieval nearly instantaneous. It's important, however, to note that the index file must be kept up to date as the table data is changed. An index file that isn't in sync with its table will cause more problems than it solves.

More often than not, we'll want to view the table in different orders according to different circumstances. One time we'll want to see the table in date of birth order, another time we'll

The Fundamentals of Visual FoxPro 6.0

want to view the table in order by gender and then alphabetically within each gender. It would seem that we'd need a second index file with a new set of pointers to the record numbers in the table, organized by our new sorting order.

However, there are a number of problems with this approach. First, notice that half the data in each of these separate index files will be identical, because we'll need the Index # (from 1 to 8) each time, and to duplicate the data in each index file would be redundant and wasteful. Second, maintaining separate files means additional work on the part of FoxPro as well as the operating system, which would undoubtedly hurt performance. And additional code in a program rarely makes it more robust.

The solution is to use a single file with a column for Index #, and multiple columns for each set of pointers. In the example above, where indexes are needed for both Birth Date and Sex/Name, our index file would look like this:

Index file

Setting the index to the second column of pointers would result in a view of the table with all females first, in order by name, and then all males, also in alphabetical order by name.

Table displayed according to Sex/Name tag

Table

Record # 1 Alexandra 01/01/83 F 4 Donna 11/01/21 F 8 Heather 08/05/58 F 2 Bob 12/16/54 M 3 Carl 07/13/27 M 5 Ed 11/13/60 M 6 Frank 03/06/70 M 7 George 09/30/34 M

Each of these columns that contains a pointer to a table is called a tag, and the expression used to determine the order of the records (in the first case, simply the date of birth, and in the second case, the sex plus the name) is called the index expression.

A single file that contains multiple indexes-or tags-is called a compound index file and has the extension of .CDX. If a compound index file has the same name as the table, it is called a structural index file and is automatically opened and maintained when the table is accessed. Nonstructural compound index files must be opened and maintained manually, but still afford the benefits of improved performance and easier maintenance than multiple single index files.

You might be wondering why, because a structural index file automatically handles a lot of the details, you'd ever want to use a nonstructural index file. Because an index file is updated on the fly as records are modified, added, and deleted, more tags in an index file will cause more overhead, and might at some point slow performance to an unacceptable level. Instead of suffering through sluggish response time due to an enormous number of tags, it might be desirable to create a series of compound index files for use in different situations.

Indexes and Rushmore

In addition to providing the capability to view tables in various orders, indexes perform another extremely important function-finding specific data. In order to understand how this works, imagine a file cabinet with a separate folder for each company. It would be reasonable to assume that these folders were filed in alphabetical order by company name to facilitate finding a specific company quickly. It would be extremely fast to find the InterGalactic Widgets folder-you'd just skip to the I drawer. However, if the folders were ordered by company name and you wanted to find a company with a specific tax ID number, you'd have to search through every folder until you found it-considerably more time-consuming.

Suppose, however, you knew that you were going to have to look for tax ID numbers as well as company names. You could keep a separate sheet of paper that listed all the tax ID numbers in numeric order, and the company name next to the number. Sort of like our index file, right?

Index files-specifically, tags in index files-are used to rapidly search tables. With some commands, you'll set up the table for searching on a specific tag; with other commands, Visual FoxPro automatically handles the use of tags in each table, as long as the tag exists. The internal mechanism that provides this capability to rapidly search through tables via index tags is called Rushmore, and it's what gives Visual FoxPro its incredible speed and performance.

It's important to mention that tags that can take advantage of Rushmore need to be constructed in a special way. I'll cover how to create Rushmore-optimizable index tags later in this chapter, but the differences between Rushmore searches and non-Rushmore searches are so great that mentioning this several times is justified.

Databases

A database is a table that contains pointers to the data structures in the database-such as tables and indexes. (A database also has other information in it, which I'll bring up when appropriate.) The database table has an extension of .DBC (DataBase Container), the corresponding memo file has the same name as the database but has a .DCT extension, and the structural index has the same name as the database but uses a .DCX extension.

The database table contains records for the database, for each table in the database, for each field in each table, for each index tag, for each persistent relation between the tables (we'll discuss the term "persistent" later), for views, and for connections to external databases. A record for a table contains the path to the table, not simply the table name itself. The entire path, including drive specification, is included if the table is on a drive other than the drive containing the .DBC, but only the relative path is included if the table is on the same drive as the .DBC. Variable-length information about these entities, such as the code associated with a specific database operation, is contained in the database's memo fields.

The Fundamentals of Visual FoxPro 6.0

Because the .DBC is a regular Visual FoxPro table, you cannot access the .DBC table as a regular table with an earlier version of FoxPro. Also, exercise extreme caution when attempting to access the .DBC as a regular table with Visual FoxPro. You can physically access the table, but the contents of the various records and fields have specific meanings and relationships that are maintained by Visual FoxPro when you access the database properly. Inadvertently changing the data might corrupt or invalidate the database and render it unusable.

Once an application's data tables are added to the database, the tables cannot be accessed interactively without opening the database first. When a table is added to a database, a byte in the header record of the table changes to indicate that it now belongs to a database and thus can't be opened without the use of the database. Fortunately, opening a table will also automatically open the database it's part of.

.MEM files

Data can also be stored in .MEM files. These are stand-alone binary files that were used in the past to hold series of memory variables that had to carry over from session to session. Current practice tends toward using a table instead to contain that information. In the past, one tried to minimize the number of tables needed in an application because of the limits to the number of tables that could be open at one time. Now, with restrictions on work areas virtually limitless, it's easier to store, access, and, if necessary, reconstruct those memory variables if a table is used.

Other FoxPro files

A full-featured Visual FoxPro application can contain files with several dozen different file extensions. At first glance, the various extensions seem to be random-or, in some cases, named in order to cause deliberate confusion. While I'm not going to explain in depth what each file does, it's appropriate to list all file extensions and how they relate to each other.

Many components of a Visual FoxPro application use tables as a storage mechanism. In general, you'll find that each component, such as a database, project, report, screen, label, and so on, is contained in a pair of files-a table and a related memo file. The table contains records for each instance of a particular entity, and the memo file contains variable-length information, such as source code or fully qualified file names.

If you're familiar with earlier versions of FoxPro, you'll notice that there is no .SPR or .SPX in the following list. This is not a misprint. Menus still go through the generation process, with the result being an .MPR file that is then compiled into an .MPX, but screens are not handled the same way.