October 1997
Doug Hennig
Partner
Stonefield Systems Group Inc.
2055 Albert Street, Suite 420
Regina, SK Canada S4P 2T8
Phone: (306) 586-3341
Fax: (306) 586-5080
CompuServe: 75156,2326
E-mail: dhennig@stonefield.com
World Wide Web: www.stonefield.com
With Visual FoxPro®, Microsoft is finally giving us something Xbase developers have needed for years: a built-in data dictionary. Visual FoxPro's data dictionary is based on a database container, a table that contains definitions for tables, persistent relationships between tables, connections to remote data sources such as Microsoft® SQL Server™, and views (both for local and remote data).
This article will take a close look at the data dictionary, how it is supported with new and existing commands, and what strategies you should be considering for implementing the data dictionary in your applications.
FoxPro has always provided the developer with some wonderful tools (sometimes called "design surfaces") to make development faster and easier: the Screen Builder, the Project Manager, the Report Builder, and more. However, one tool that was always lacking was a data dictionary.
A data dictionary is a repository for some types of meta-data (a fancy word for "data about data"); specifically, that information regarding tables, their structures, indexes, and relationships with each other.
You may be wondering why a data dictionary is important when FoxPro already provides the display structure command to determine DBF structures. Here are several reasons:
By providing a complete description of the data elements in an application and implementing data integrity rules, a good data dictionary acts as both the definition of the database as well as its guardian.
The Xbase world has always been a little off the rest of the database community when it comes to what terms to use for a number of concepts.
In relational database systems, data is considered to be stored in two-dimensional structures called tables. In Xbase, we called these things databases. This gave rise to things like the DBF (DataBase File) extension used for a file containing data and the close database command which closes such a file. Unfortunately, "database" means something different to other database people; it means the set of all the data for an application, a system, or even an entire business. To other database users, tables consist of rows and columns. In Xbase lingo, we referred to these as records and fields.
Well, guess what? With Visual FoxPro (Visual FoxPro), we Xbasers are being brought into sync with the rest of the database community. The files we stored data in are now called "tables." A "database" in Visual FoxPro is now considered to be a set of tables (among other things) rather than just one file. "Records" and "rows" tend to be used interchangeably in Visual FoxPro, as do "fields" and "columns."
Visual FoxPro implements databases in a different manner than Microsoft's other database products. In Microsoft Access, for example, all of the data is contained in the database, which is a single file with an MDB extension. Tables and the data they contain are stored in different sections of the database.
Visual FoxPro, by comparison, keeps data in individual files, each table being a separate file with the familiar DBF extension. The database is stored in a database container, a file with a DBC extension. As you may guess, a DBC file is actually a Visual FoxPro table (the associated index and memo files have DCX and DCT extensions, respectively). Like other tables, it can be opened, browsed, etc.
The DBC file doesn't contain any of the data in the tables, nor does it contain the tables themselves. Instead, it contains information about the tables (among other things). For example, the DBC contains the name of each table in the database, along with each field and index that make up each table.
The Visual FoxPro database includes information on:
Interestingly, the database doesn't contain information stored in the header of the DBF and CDX files. In other words, you won't find metadata such as the type and size of each field, nor will you find the index expression for each tag. This means you cannot create a table or its indexes from the information stored in the DBC.
Before we go any further, you should know that just because Visual FoxPro supports databases, you aren't required to use them. You can continue to use tables that aren't part of a database (called free tables) just as you used to in FoxPro 2.x.
However, there are some very good reasons for using databases instead of free tables. A few of them are:
field() and afields().For all these advantages, there is one drawback: because Visual FoxPro stores the name and path for the database in the DBF header (called a backlink), a table can only belong to a single database. Thus, a database must be shared by all applications needing access to the tables contained in the database.
There are several new commands specific for databases. The more important ones are:
create database <Name> creates a DBC.open database <Name> opens a database and makes it current.set database to <Name> makes the specified database the current one (like tables, you can have many databases open at once, but only one is selected at a time).close database no longer closes all open tables. Instead, it closes the current database and all its tables. Other open databases and their tables are left open. A new close tables command closes just the tables for the current database, leaving the database itself open along with other databases and their tables. close database all does what the 2.x close database command does—it closes all open databases and tables.
modify database brings up the Database Designer, Visual FoxPro's visual tool for maintaining databases. The Database Designer shows all of the tables contained in the DBC graphically.
Using the Database Designer, you can create or add tables to the database, define relationships between tables, define views, and maintain stored procedures. Anything you can do in the Database Designer, you can do programmatically as well.
As I mentioned earlier, Visual FoxPro's database container is a table that can be opened, browsed, edited, etc. Here's the structure of a DBC:
Here's what each field in the DBC contains:
recno() for the record. Each table, field, index, relation, connection, and view is stored in a separate record.Tables can either be free or attached to a database. Because of the new backlink mentioned earlier (in free tables, the backlink area is blank), the DBF file structure is no longer backwardly compatible with earlier versions of FoxPro or other applications that can read DBF files.
Visual FoxPro supports several new data types: Binary Character and Memo, Currency, DateTime, Double, and Integer.
Two new data types, binary versions of Character and Memo fields, allow you to specify fields that contain non-ASCII data and are therefore not subject to automatic code page translation. Unlike FoxPro 2.x, since these data types are defined directly in the table structure, there's no danger in forgetting to specify which fields to not translate when a table is opened.
A Currency field, which has a type of Y, is stored as an eight-byte binary number. Because Currency fields take up eight bytes regardless of the number stored, they may require less storage space than normal Numeric fields if you need to store large numbers. Currency fields also provide better performance for mathematical operations. Since Numeric fields are actually stored as the ASCII characters that make up the digits, Visual FoxPro must convert the information into a real number before doing an operation on the field, then convert the result back to ASCII characters before storing it in the table. With Currency fields, neither conversion step is necessary.
Currency fields automatically have four decimal places. Currency fields have a range of -922,337,203,685,477.5807 to 922,337,203,685,477.5807.
A DateTime field, which has a type of T and requires 8 bytes, stores both date and time (to the second) in the same field. You can add a number of seconds to a DateTime to obtain another DateTime or subtract two DateTimes to obtain the number of seconds between them. Several new functions, similar to those for Date fields, allow you to control the entry and display of DateTime fields, obtain portions of the field (such as the minutes portion), and convert to and from other data types.
A Double field, like a Currency field, is stored as an eight-byte binary number. However, it's stored as a double-precision floating point number. Although Double fields are fixed at eight bytes in length, like Numeric fields, you must specify the number of decimals (up to 18) when you define a field. The number of decimals is used for display only, not as the number of decimals to use for internal calculations.
Double fields have a type of B. The range of negative values for a Double field is -1.79769313486232E+308 to -4.94065648541247E-324; the range of positive values is 4.94065648541247E-324 to 1.79769313486232E+308.
An Integer field, which has a type of I, is stored as a four-byte binary number. It can only store whole numbers (as you would probably guess from its name) in the range of -2,147,483,647 to 2,147,483,647. Integer fields are ideal for storing whole numbers up to a couple billion because they require less than half the storage of the equivalent Numeric field: four bytes versus ten. For example, Integer would be a good choice for a field storing a sequential value as the primary key for a table.
In FoxPro 2.x, a Memo or General field is stored in a DBF as a ten-byte pointer to a block in the FPT file where the actual text is stored. In Visual FoxPro, this pointer is now a four-byte binary pointer. This will reduce the size of your tables by six bytes per record for each Memo and General field used, and will have slightly faster performance as well. Visual FoxPro also now supports a block size of one byte.
Visual FoxPro provides support for null fields and values. Null is not a data type, but a value (or rather, lack of a value). A null is not a zero or a blank value; it represents the absence of data. The simplest way to think of a null value is "I don't know what the value is." Fields and memory variables of any type can contain a null value. For example, a Character field and Numeric memory variable can both be null.
In many designs, null and blank or zero may be synonymous, but a null is not involved in mathematical operations (like averaging) while zero is.
modify structure brings up the Table Designer, Visual FoxPro's new visual tool for maintaining the structure of tables.
In addition to a spiffier interface, the Table Designer has some new features that the 2.x version doesn't:
Several of these new features (Table Name, Database, and Table Properties) are only available for tables attached to a database.
There are several new properties available for fields: long name, caption, comments, validation rule and text, and default value.
A field long name can be up to 128 characters and can be used to refer to the field in place of the real field name. This isn't the same thing as a full-text description for a field since spaces aren't allowed, making it less presentable to the end user (that's what Caption is for). However, it does allow us to have more meaningful names for fields, such as Monthly_Posted_Balance instead of MONBAL. Comments can be used to describe the purpose or contents of the field.
You can define field validation rules and the text to display if the validation fails. This ability, along with support for the table-level validation rules and triggers we'll see later, is probably the most powerful feature of Visual FoxPro's data dictionary. By defining data validation rules that are enforced at the engine level, you prevent invalid data from getting into your tables, whether it be by users typing in a browse or a program that neglected to do proper data checking.
Default is the value that will be entered into a field when a record is added interactively (such as in a browse) or programmatically (with append blank, when the SQL insert command is used and a value for the field isn't specified, etc.). The default can be any expression, including a UDF, as long as it evaluates to the same type as the field. Probably the most useful default value is an automatically incrementing value. This is useful for all kinds of fields, such as invoice number, check number, or any field that's the primary key of its table.
There are several properties available for tables from the Table Designer: long name, comments, validation rule and text, and insert, delete, and update triggers.
A table long name can be up to 128 characters and can be used to refer to the field in place of the real table name. Comments can be used to describe the purpose or contents of the table.
You can define table-level validation rules that are evaluated when Visual FoxPro tries to write an edited record to the table (for example, the record pointer is moved or the table is closed). Table validation can be used to handle rules that field validation can't, such as dependencies between fields or updating other tables (such as updating summary information).
We'll discuss triggers a little later.
Except for a change to the user interface, the only difference between Visual FoxPro and FoxPro 2.x with respect to indexes is that you can now define an index type for each tag. For free tables, the types are:
For tables included in a database, another index type is available: primary. A primary index is essentially a candidate index (it automatically prevents null and duplicate key values) except that while you can define as many candidate indexes as you like, a table can only have one primary key. The distinction between primary and candidate keys is mainly for relationships.
Since the primary key for a table is stored in the database, when you remove the table from the database, Visual FoxPro automatically changes the index type to candidate. The reason for the name "candidate" is since it too will not accept duplicate or null values (and thus uniquely identifies a record), such an index is a "candidate" to be the primary key; it's just that something more suitable was chosen instead.
There are lots of new and improved commands regarding tables. A few of them are:
create table now supports many new clauses, such as primary key to define the primary key for the table, references to define a persistent relationship with another table, and null to permit null support for a field.use now automatically opens the database associated with a table if it isn't already.alter table is a new command that you must get to know. It allows you to alter the structure of a table programmatically, something that formerly required a lot of code. Here's an example of a single line of code that adds three new fields, deletes one field, removes the relationship between COMPANY and CATEGORY (but keeps the index on COMPCAT), and removes the table validation rule: alter table COMPANY add column ADDRESS C(30) ;
add column CITY C(30) ;
add column POSTCODE C(10) ;
drop column TEST ;
drop foreign key COMPCAT save ;
drop check
copy to now supports an new fox2x type so you can output records in a format older version of FoxPro can read.dbgetprop() is a new function that returns information from the database, such as the caption for a field or the validation rule for a table. Here's a routine called GETFIELD that uses dbgetprop() to display a popup of fields, but using the field caption (which is preferably something meaningful) instead of the field name: local lnI
define popup FIELD_POP from 0, 0 title 'Select Field'
for lnI = 1 to fcount()
define bar lnI of FIELD_POP prompt dbgetprop(alias() + '.' + ;
field(lnI), 'Field', 'Caption')
next lnI
on selection popup FIELD_POP deactivate popup
activate popup FIELD_POP
wait window 'You selected ' + field(bar())
A shortcoming that's always existed in Xbase systems is the lack of persistent relationships between tables. Unlike table structures, which are defined in the header of the DBF file, and index structures, which are defined within the CDX file, relationships between tables are only defined within program code. You specifically code set relation or SQL select with a join condition to relate or join tables together.
With Visual FoxPro, persistent relationships between tables can now be defined in the database. These relationships are not only useful for documentation purposes, but are also used as default relationships between tables added to the DataEnvironment of forms and reports, and in the Query and View Designers.
Persistent relationships are always defined from one index to another rather than from one field to another as you might expect.
Defining a relationship between two tables in the Database Designer is simple: drag an index from one table to another. There are some rules about which indexes get dragged where:
Since you can only drag a candidate or primary index to another index, you are always setting a relationship from the "one" table into the other table (either "many" or "one," depending on the type of index involved).
Relationships are shown as lines between tables. One end of the line has a bar across it; this end connects to the "one" table. The other end of the line has a "fork" symbol; this end connects to the "many" table.
Persistent relationships can also be defined programmatically using the create table and alter table commands. For example, the following command creates the COMPANY table and relates it to the CATEGORY table:
create table COMPANY (COMP_ID I primary key, NAME C(30), ADDRESS C(30), ;
CITY C(25), POSTAL_CODE C(10), CATCODE C(4) references CATEGORY)
Maintaining referential integrity (RI) is a very important issue for database developers to consider. RI means every child record has a matching parent. For example, if the employee table is a child of the department table, every employee must have a matching department. This means the department ID field mustn't be blank, nor must it contain an invalid department ID.
There are two ways to ensure RI is maintained. Some database management systems, such as Gupta's SQLBase Server, support declarative RI. This means by defining a relationship between the two tables and the rules to follow for delete, update, and insert events, you are telling the database to handle the events automatically.
FoxPro supports another type of RI: procedural. Instead of the database engine automatically performing RI actions, procedural code is written to perform the appropriate action. While declarative RI may seem preferable, it actually has the advantage in being more flexible, since you, not the database engine, decide exactly how the RI should be enforced.
Triggers are pieces of code automatically executed when a particular event occurs. For example, if you define a function as the delete trigger for a table, Visual FoxPro calls the function (the trigger is said to be fired) whenever an attempt is made to delete a record, either programmatically or interactively. If the trigger code returns .T., the record is deleted. If it returns .F., the record will not be deleted and a trappable error (1539) occurs.
Triggers are stored in databases, so you can't define them for free tables. You can define triggers for three events for each table: inserting, updating, and deleting records. Any program or interactive session (such as during a browse or in a form) causing an insert, update, or delete event to occur will cause the trigger to fire.
The most typical use for triggers is to maintain referential integrity. The update and delete triggers are often used for parent tables to prevent orphaning child records. The insert trigger is usually used for a child table to ensure a foreign key field matches a record in a parent table. Examples of actions taken by a delete trigger are preventing a record from being deleted if there are any child records (called a "restrict" delete rule) and deleting the child records (a "cascading" delete).
The fact that both programmatic and interactive events cause a trigger to fire provides us with a powerful mechanism for protecting our data. Together, triggers and the other rules defined in a database help you maintain both data integrity (preventing invalid data from getting into tables) and referential integrity (preventing invalid links between tables), whether it be by users typing in a browse or a program that neglected to do proper data checking.
Triggers can be nested; in fact, they're automatically nested if necessary. If the trigger code for one table causes an event to occur in another table, that other table's trigger code will fire. For example, cascading deletes from parent to child to grandchild are automatically performed by creating delete triggers for the parent and child tables. The parent table's trigger deletes the child record, which causes the child table's trigger to fire, which in turn deletes the grandchild record.
Triggers can be defined in the Table Designer by selecting the Table Properties button, or programmatically using create trigger on <Alias> for <Event> as <Expression>.
A stored procedure is a routine stored within the database itself: the source code is stored in the CODE field of the StoredProceduresSource record in the DBC and the compiled code is stored in the CODE field of the StoredProceduresObject record. Stored procedures are usually used for trigger code and any routines the trigger code must call.
Since the procedures are stored in a database, the database can protect itself from improper changes to the data without requiring an external program to do the work. It also means once the triggers and stored procedures are defined, the application programmer doesn't have to worry about knowing those rules or implementing them in every program that accesses the database. Another advantage is if the rules change, they change in one place rather than changing every program that accesses the database. Because the trigger code is stored in the database, and the only way to access a table is through the database, there's no way for users or programmers to circumvent the rules enforced through the triggers. This isn't exactly the same as object-orientation, but it does provide encapsulation of data and code into a database.
Stored procedures can be maintained from the Database Designer or programmatically. modify procedures brings up an edit window with the stored procedures for the current database. append procedures appends code from a file to the stored procedures in the current database.
What code should go in stored procedures and what should be in library routines? The approach I use is to put routines enforcing business rules specific to a database in the stored procedures for the database, and put generic, reusable code where it belongs: in library routines. The drawback to this approach is the database can't stand by itself; the library routines have to be available or the stored procedures calling them will fail. However, since I rarely allow users of applications I write to browse tables directly from the Command window, I don't consider this to be a major concern. Also, if someone does browse a table and tries to make changes, the trigger and validation routines will fail (an error trap can provide a more elegant interface than just having the program bomb) and no changes to the data will be permitted. In other words, if we can't ensure the data is valid, let's not allow it to be changed.
Microsoft provides an RI builder that automatically generate triggers and stored procedures that maintain RI according to rules you specify. To bring up the RI builder, open the Database Designer, double-click on a relationship line, and choose the Referential Integrity button in the dialog that appears.
The RI Builder has columns for the parent and child tables and their tags, as well as columns for the rules to use for update, delete, and insert events. Tabs below the list allow you to choose a particular event and the rule to use for the relationship selected in the list; you can also click in the appropriate cell in the list and choose the desired rule from the drop-down list that appears. The update and delete events pertain to the parent table and define what should happen to the child table. The insert event is for the child table and defines whether the foreign key must match a parent record or not.
FoxPro 2.x allowed you to access client-server databases if you installed the Connectivity Kit, an add-on library from Microsoft. With Visual FoxPro, access to other databases is built into the product. Visual FoxPro uses ODBC (Open Database Connectivity) to allow you to connect to any database for which an ODBC driver is available. For example, Access, Oracle, Watcom, and SQL Server databases are all accessible from Visual FoxPro now. Visual FoxPro does client-server!
Although one of the benefits of client-server development is that network traffic is reduced since only records matching a query are sent to the workstation, Visual FoxPro provides several features to help performance with backend databases even more: progressive fetching, delayed memo download, batch updating, and so on.
There are two aspects to accessing another database (called remote data, even if it exists on the same machine as Visual FoxPro): connections and views, both of which must exist in a database.
Connections describe how to connect to remote data, including the datasource, user ID and password, timeout intervals, and type of processing to be used.
Views are simply pre-defined SQL select statements to retrieve data. However, unlike the cursor or table that results from a regular select statement, views are updatable, meaning that changes made to the data in a view can update the original tables. Views can contain remote or local data, or a combination of both.
Views can be opened using use <ViewName>, which causes the select statement to be executed. The resulting cursor can be browsed or edited just like a regular table.
Views are defined visually with the new View Designer or programmatically using create sql view.
Visual FoxPro's database allows you to extend the information stored in the DBF and CDX file headers to contain other important repository information. However, there is certainly some information missing from the DBC that you might have expected:
There are some other limitations as well, such as: