Special Edition Using Visual FoxPro 6

Chapter 4

Advanced Database Management Concepts

• Database Design Techniques
• Data Normalization
  • Functional Dependencies
  • First Normal Form
  • Second Normal Form
  • Third Normal Form
  • When to Break the Rules
• Naming Conventions
  • Naming Issues Within an Application
  • Naming Memory Variables
  • Naming Fields in Tables
  • Naming Objects
  • Naming Issues Across Related Applications
  • Using Enterprise-Wide Naming Conventions
• Other Advanced Features of Visual FoxProís Database Container
  • Using Record-Level Validations
  • Maintaining Referential Integrity
  • Forming Persistent Relations
  • Using the Referential Integrity Builder
  • Building Your Own Referential Integrity
  • Using Triggers

Database Design Techniques

No other single factor has a greater influence on the success of a database application than the design of the database itself. The way you organize individual data items in tables and then relate those tables with one another in a database forms the very foundation of the application. A poorly built foundation weakens the programs built on it by making them more difficult to write, more difficult to maintain, and more difficult to enhance as demands on the software grow. Furthermore, improper design might force the programmer to fall back on less efficient methods of coding, which thus require more time and are more susceptible to error.

Perhaps you learned programming from classroom courses that taught you good design methods right from the start. On the other hand, maybe you learned programming by doing it and learned good design methods by trial and error. In either case, this chapter should help you build on your existing skills to create better database designs. To do so, it examines several areas of database management.

Data Normalization

The most important thing you can do when you start a new application is design the structure of your tables carefully. A poorly structured database results in very inefficient code at best; at worst, it makes some features nearly impossible to implement. On the other hand, a well-designed set of tables will not only solve your current problem, but also provide the flexibility to answer questions that you don't yet anticipate. Perhaps even more important, you will write programs faster by taking advantage of queries and SQL SELECT statements to retrieve and maintain data. Finally, reports that might have required awkward manual coding under a denormalized structure almost write themselves when you use the report generator.

In general, the data structure of an application, more than any other factor, makes or breaks the application's success. Visual FoxPro is based on the relational database model proposed by E.F. Codd in 1970. Codd based his model on mathematical principles that govern relational set theory. By following only a few very specific rules defining the creation of sets, he proved that you can manipulate the data easily. His technique became known as data normalization.

All relational database theory revolves around the concept of using key fields to define relations between flat file tables. The more tables you have, the more relations FoxPro requires to connect them. Set theory does not demand, or even expect, that each table be connected directly to every other table. However, because each table is connected to at least one other, all tables in the database have direct or indirect relations with one another.

To examine the concepts of normalization, this section examines the Tasmanian Trader example provided with Visual FoxPro. However, it takes a view close to the beginning of the application-development process-just after establishing the data requirements.

Functional Dependencies

Assuming that you have decided what data fields you need, the next step is to divide them into tables. (Of course, you could put all the fields in a single table.) Even without normalization rules, it should be obvious that you do not want to repeat all the information about employees, customers, products, suppliers, and shippers for each item ordered. The only way to determine which fields belong together in each table is through functional dependency analysis. (It's not the same thing as taking away a computer terminal from a programmer; that's a functionally dependent analyst.)

Functional dependency defines the relationship between an attribute or a group of attributes in one table to another attribute or group of attributes in another table. In this discussion, the term attributes refers to fields. Therefore, you need to see which fields depend on other fields. A person's last name, for example, depends on his Social Security number (not originally, but at least according to the U.S. government). For any given Social Security number (person), there is only one corresponding name-not necessarily a unique name, but still only one name.

On the other hand, a Social Security number does not depend on a name. Given only a person's last name, there may be dozens, if not hundreds, of Social Security numbers. Even if you add a first name to the last, it still might not uniquely identify a single Social Security number. Imagine how many Bob Smiths there are, for example.

Thus, you can conclude that a last name is functionally dependent on a Social Security number, but not the other way around. You might even go the Orwellian route of referring to a person by his Social Security number.

Next, you might want to find other attributes that are functionally dependent on a Social Security number. Having gone through all the fields, you might have a list like the one shown in Table 4.1.

As a first pass at designing tables, you might group these fields into one table. Then, following similar logic, you might determine the functional dependencies in the remaining fields. Continue to group those attributes that have the same dependency in the same table. In effect, the number of functional dependencies determines the number of tables required.

Actually, if you follow this method of grouping fields, the resulting tables should be very close to a normalized form already. However, to guarantee that they are normalized, you should verify that they obey at least the first three rules of normalized data:

• First normal form: eliminates repeating fields and nonatomic values
• Second normal form: requires each column to be dependent on every part of the primary key
• Third normal form: requires that all nonprimary fields depend solely on the primary fields

First Normal Form

The first normal form eliminates repeating fields and nonatomic values. An atomic value means that the field represents a single thing, not a concatenation of values-just as an atom represents a single element.

In the early days of relational databases, there were some rather small limits on the number of fields allowed in a record. As a result, programmers concatenated fields to fit all the data into a single record. Thus, one field might contain something like the following:

12/03/9412/15/9401/05/95T

This value actually represents four fields: an order date, a start-production date, a completion date, and a flag to indicate whether the order was shipped.

Forming relations between fields, retrieving data, and performing other operations is not easy when a field contains multiple values. The need to perform substring searches and to parse the fields slows applications tremendously, not to mention adding extra complexity to the code. To bring this table into first normal form, you need to split this field into four separate fields: three Date fields and one Logical field.

Another common problem addressed by the first normal form is repeated fields. Again, it was not unusual for early database developers to hard-code the number of items that a customer could order. They did so by placing the ordered products' IDs in the same record as the general order information, as shown in the following table:

OrderId	OrderDate	ProdId1	ProdId2	ProdId3	ProdId4	Net
00006	08/04/94	A3426	B8483	C398		59.34

In this example, there is no problem as long as the customer never orders more than four items at a time. (Only three items were ordered in this example.) However, it would be difficult to search the database to determine how many units of each product have been sold because the program has to check each product column and then sum the results. Reports that display a list of customers who order specific products would be similarly difficult to produce. In fact, most reports need complex hand coding so that they can search each field. As a result, the reports tend to be more likely to generate errors and require more time to execute.

Of course, you could increase the number of possible products that a customer can buy, but how many is enough (5? 10? 20?)? If you select 20, what if most customers order only two or three items? The resulting database wastes a great deal of space. More important, depending on the way that the code reads these fields, it can spend a great deal of time processing empty fields.

One alternative is to define a database that has a variable number of fields. In fact, some database systems several years ago supported this feature; they even promoted it as the best solution, in their marketing department's opinion. Fortunately, FoxPro continued to support the true relational definition of tables and kept records fixed in length.

The first normal form replaces repeating fields with a single field. It then creates as many records as necessary (one per ordered item), as shown in the following table:

OrderId	OrderDate	ProductId	OrderNet
00006	08/04/94	A3426	59.34
00006	08/04/94	B8483	59.34
00006	08/04/94	C398	59.34

After performing this analysis on each table in the database, the preliminary relational model of the data is complete. This first normal form is called structural or syntactic normalization. However, it should never be your final goal. There can still be problems in the data that cause the code to be more complex than it needs to be.

Intuitively, you may not like the solution offered in the preceding example. For one thing, it repeats values-not within records, but across multiple records. And wherever repeated values occur, inconsistencies can occur. This problem is addressed in subsequent normal forms.

Second Normal Form

The second normal form requires that each column be dependent on every part of the primary key. Look again at the table that results from the first normal form:

ORDERS.DBF

OrderId	OrderDate	ProductId	OrderNet
00006	08/04/94	A3426	59.34
00006	08/04/94	B8483	59.34
00006	08/04/94	C398	59.34
00007	08/05/94	B8483	9.18

Because of the transformation performed by the first normal form, OrderId is no longer unique; neither is any other single field. However, the combination of OrderId and ProductId may be unique. Using this as a working assumption, you next need to examine the other fields to see whether they depend on the new primary key.

OrderDate depends only on OrderId, not on the combination of OrderId and ProductId. The same is true of OrderNet. Therefore, according to the second normal form, you need to remove these fields and place them in a separate table with a copy of the field on which they depend: OrderId. This results in two tables. Name the one that uses OrderId as the primary key, ORDERS.DBF; name the other, which contains a primary key on OrderId and ProductId, ORDITEMS.DBF. These new tables are as follows:

ORDERS.DBF

OrderId	OrderDate	OrderNet
00006	08/04/94	59.34
00007	08/05/94	9.18
ORDITEMS.DBF
OrderId	ProductId	LineNo
00006	A3426	0001
00006	B8483	0002
00006	C398	0003
00007	B8483	0001

Merely by following the rules of normalization, you have taken the original order data and derived a structure that consists of two tables: one table with information about the overall order and the other with details on each order. Notice that a new field has been added to ORDITEMS.DBF: LineNo. This additional field counts the number of items in the form. This field has a fixed size of four digits; thus, it enables up to 9,999 items to appear in the same order.

To associate the information in ORDERS.DBF with ORDITEMS.DBF, you form a relation between them based on OrderId. This relation is a one-to-many relation because, for every order in ORDERS.DBF, there can be more than one record in ORDITEMS.DBF. In fact, there is no limit to the number of items that the customer can order-one item or a million. (Well, actually, you set an arbitrary limit of 9,999 via the size of the field LineNo, but you can always increase the size of this field.) The program, when it is written to use related files, handles both situations equally well.

Third Normal Form

To reach the third normal form, the table must already be in first and second normal form. Then, you determine which field or combination of fields represents the primary key for the table. For the employee table, a logical choice would be either employees' Social Security numbers or their employee IDs. For the order table, OrderId makes a good choice.

For the order-items table, no single field uniquely defines a record. There can be more than one detail record for an order ID and ProductId can occur many times, both in the same order and across orders. OrderId also can occur many times within a single order. LineNo repeats the same sequence, beginning with 1 for each order. However, the combination of OrderId and LineNo is unique. Even if the same item appears more than once in a single order, its line-item value will be different. Thus, this file requires a composite primary key.

To illustrate third normal form, another field-ProdName-has been added. Suppose that the order-detail table includes the following fields:

ORDITEMS.DBF

OrderId	LineNo	ProductId	ProdName
00006	0001	A3426	Tape Drives
00006	0002	B8483	Modems
00006	0003	C398	Track Balls
00007	0001	B8483	Modems

To be in third normal form, all nonprimary fields must depend solely on the primary fields. First, determine whether ProductId depends solely on the key field combination OrderId and LineNo. The answer is yes, because there can be only one product ID for each combination of OrderId and LineNo.

Does product ID depend on the product name? This is a trick question. In some ways, it does, but product names may not be unique. Some products could have multiple sizes, colors, or other attributes. Each product has its own unique product ID but the same product name. Therefore, product ID does not depend solely on product name.

Does ProdName depend solely on the primary key fields? Not really. The product name is not a function of the order ID and line number; rather, it depends on the product ID. Remember that each product ID has one unique product name, although the product name might be assigned to more than one product ID. Therefore, this field fails the third normal form.

The solution in this case is to move the product name into a new file called PRODUCTS in which ProductId is the primary key. You might have reached this conclusion independently from your analysis of functional dependencies. Remember that normalization rules just reinforce functional analysis and common sense. The new table structure appears as follows:

ORDITEMS.DBF
OrderId	LineNo	ProductId
00006	0001	A3426
00006	0002	B8483
00006	0003	C398
00007	0001	B8483
PRODUCTS.DBF
ProductId	ProdName
A3426	Tape Drives
B8483	Modems
C398	Track Balls
B8483	Modems

Of course, you need to perform this same analysis on every table in the application. When the analysis is complete, you can say that the application is normalized. Although there are additional levels of normalization, the need for them is rare. If you practice creating tables in third normal form, you can avoid most data structure problems. You usually do not want to include fields that can be derived from other fields in the same or related tables. For example, you might not want to include an order-total field in the order file if the detail file also contains the price of each item ordered-it is safer to sum the individual prices to arrive at the order total. Of course, the amount actually paid might go on the order to compare against the total due. Think of it this way: The customer typically pays against an order but is billed based on individual items.

Perhaps you feel overwhelmed by these rules. Actually, with practice, you will begin creating normalized files right from the start. Some wise person once said that true understanding comes only when you see it in your dreams. When you look at the data for a new application and immediately visualize multiple files in your head, you truly understand normalization.

When to Break the Rules

Normalization rules are not laws; they are merely guidelines to help you avoid creating data structures that limit the flexibility of the application or reduce its efficiency. However, no one will knock on your door and arrest you for breaking normalization rules (except maybe your boss). The following examples are situations in which breaking normalization rules might make sense:

• You need to write a library system that prevents any patron from checking out more than five books at one time. You could write the system by normalizing the file that tracks the books checked out; it would have to ensure that no more than five records exist for each patron. However, a single record with five fields-one for each book-might make this application easier to develop. (An alternative is to add to the main patron table a field that simply counts the number of books that the patron has currently borrowed.)
• An order ID actually consists of the concatenation of two digits that represent the year plus a five-digit sequential number. Because the order-date field also reflects the year, you could, in theory, extract it or use the date in combination with the sequence number. However, the ease of referencing a single field in this case probably outweighs the strict avoidance of repeating data that can be derived from other fields.
• You have accepted a project to build a database for the National Association of Twins. Would you create a separate record for the name of each twin, or would you include twins in the same record? After all, you know by definition that there will always be exactly two names.

The intent here is to emphasize that normalization is a desired goal, but every once in a while, it makes sense to be a little abnormal.

Naming Conventions

Your first question might be, "Why do I need a naming convention? FoxPro does not require it." Although Visual FoxPro does not require a naming convention, using one makes code clearer. You will appreciate this benefit when you have to return to code that you wrote months or years earlier and try to remember what the variables mean. You might encounter similar problems when you try to work with code written by someone else. It can take considerable time just to determine the type, scope, and origin of variables. Using a naming convention solves these problems when you use the convention consistently and if you take into account the variable's type and scope. This practice can eliminate naming and scoping conflicts that are common in many large applications.

Attempts at implementing naming conventions in earlier versions of FoxPro met with limited success. FoxPro limited both table field and memory variable names to 10 characters. With so few characters available, using any of them detracted from a programmer's ability to assign meaningful names. Also, some proposed naming conventions limited users' abilities to transfer data between tables easily, especially when they were using commands such as SCATTER and GATHER. Thus, the issue became a trade-off; programmers felt that they had to choose between two conflicting goals. They could adopt a naming convention that identified a variable's source and type, or they could use all 10 characters to make meaningful names.

Now, with Visual FoxPro's support of long field and variable names, you can have both naming conventions and significant names. The following sections recommend naming conventions for different categories of variables. The sections also mention some possible variations that you might consider. Each section uses a slightly different method due to differences in the variables and objects that each variable attempts to name. However, implementing an overall naming convention for your applications will make supporting multiprogrammer development easier and result in fewer naming and scoping conflicts.

There is no one absolute naming convention. Rather, the one that you pick (or that your company standardizes on) becomes the right one. It is difficult, if not impossible, to switch to a new naming convention after you have worked with a product for some time. That is why the introduction of Visual FoxPro presents a rare opportunity to begin using a naming convention with your new applications, especially if you have never used one before.

Naming Issues Within an Application

Developers of independent applications do not often see the immediate advantages of implementing naming conventions. After all, they are the only ones who are working on the code and they know everything in it, right? Well, put aside an application for a few months and try to come back to it. How much do you really remember? The larger the application, the bigger the problem. It is easy to forget which variables each procedure uses. And what about the program that failed because Bill and Beth developed separate routines that use the same variables? Both situations lead to naming conflicts. If you reuse the same variable name, the program may accidentally overwrite values that are needed elsewhere. Suddenly, the application no longer works and users are on the phone, waiting patiently to politely inform you of a problem.

Visual FoxPro does not require that you adopt a naming convention. For many programmers, conforming to a convention might seem like wearing a straitjacket. Give it a try, though, and as you start developing Visual FoxPro projects, see whether things run more smoothly after you get used to the convention. Some working environments have a standard for programming, such as variable declarations and naming conventions. If you are a developer, you need to check with your client because it might have some requirements that you need to conform to. Otherwise, you should develop your own conventions because it is easier to use standards in your day-to-day operations.

The following sections describe some proposed naming convention rules for different variable types. Feel free to adopt what you like and discard the rest. Add additional rules if you need them. But when you have something that you like, stick with it for at least the duration of your current project. Also, keep an eye on the industry as naming conventions become more commonly used and more standardized.

Naming Memory Variables

You must follow some basic rules when naming a variable. A variable name:

• Must begin with a letter.
• Must contain only letters, numbers, and underscore characters. Spaces and special characters are not permitted, and underscores are frowned upon.
• Must not exceed 255 characters.
• Must not be a reserved word, or (if the variable is only four characters long) must not be the first four characters of a reserved word, that is, the first four characters of any Visual FoxPro command.

Microsoft's intent is to bring the object languages of its major products (such as Visual FoxPro, Access, Visual Basic, and C++) closer together. All these products support longer field names. Access currently supports up to 64 characters, and Visual Basic supports 40 characters. Other products that enable you to access their data via ODBC or OCX controls might have variable names of other lengths. If you intend to share data with one of these products, try to limit your variable-name sizes to match the smaller of them. In this way, you will eliminate potential name conflicts based on name size.

Variable Scope  The first attribute to consider when naming variables is scope identification. A variable's scope defines where and when it is known in a program. For example, a program can reference a public variable (global in other languages) from any line in the application when it is defined. The simple program shown in Listing 4.1 illustrates this concept.

* Main program
  DO SUB1
  ? abc

PROCEDURE SUB1
PUBLIC ABC
  ABC = 5
  DO SUB2
RETURN

PROCEDURE SUB2
  ? ABC
RETURN

This example defines variable ABC as a public variable and initializes it in procedure SUB1. Any lower procedure or function called directly or indirectly by SUB1 after defining ABC can use it. Similarly, any higher routine that calls SUB1 can also use ABC after calling SUB1. The capability to reference a variable value in both higher and lower procedures is the key distinguishing feature of public variables.

Many programmers use public variables extensively, so they do not have to worry about where they define or use those variables. They can even initialize every variable in the main program. Although this practice actually gives them a scope of private (meaning that the variables are known only to the current procedure and any called procedure), the fact that the variables have been defined in the initial procedure makes them available to all subsequent procedures. However, this type of coding often results in problems. Primarily, it is difficult to locate errors caused when a "new" variable in a lower subroutine has the same name as the public variable. The program simply appears to inexplicably change the variable's value.

Private variables offer more limited scope. When initialized, these variables are known to the current procedure and all procedures that it calls. However, they are not known to the procedure that calls it, to any other higher procedures, or to procedures that follow a different calling sequence. In other words, the scope travels in one direction only: down one branch of the procedure call stack.

To see the scope of a private variable in action, change the line PUBLIC ABC to PRIVATE ABC in the preceding example. Although Visual FoxPro recognizes the variable ABC in routines SUB1 and SUB2, it is not known in the main program. In fact, FoxPro generates an error when it attempts to use that variable.

The manual states that declaring a variable private does not create a new variable; it merely hides any previous variable with the same name from the current module. When the program exits the current module, it reveals the hidden variables again.

A new scope introduced in Visual FoxPro is the local variable. A variable declared local is known only in the routine that declares and defines it; higher- or lower-level routines cannot use it. If you replace the PUBLIC ABC line in the example with LOCAL ABC, ABC exists only in SUB1; neither SUB2 nor the main program can reference it.

Although not specifically defined as a variable scope, variables defined in class definitions have unique status. First, like local variables, they exist only in the class that defines them. Second, they retain their values between references to the class. This is different from a local variable, which must be redefined and initialized each time the program executes the routine that uses it. Other languages refer to variables with similar scope as static variables. Unfortunately, you cannot define static variables outside a class definition.

As programs grow, it becomes easier to forget or confuse the scope of each variable. A common naming convention identifies a variable's scope by adding a scope-prefix character to each variable's name. Table 4.2 lists the available scope levels and suggests appropriate prefixes.

In Chapter 13, "Introduction to Object-Oriented Programming," you learned how to store a reference to an object in a variable. Therefore, you might be tempted to use prefixes in these object reference variables. Strictly speaking, these variables might act like private variables in scope, but they are so different in use that they deserve their own naming convention. Those conventions are listed in Table 4.4 later in this chapter.

Variable Type  The next attribute of the variable that you can identify is its type. Knowing a variable's type can help prevent errors caused by using the wrong variable type in an expression. Suppose that you want to use a variable named START. By itself, the variable gives no indication whether it stores a date, character string, or number. Suppose that you want to use START in an expression such as the following:

? 'List records from ' + start + ' TO ' + end

If START stores anything other than character-type data, Visual FoxPro quickly responds with the following error message:

Operator/operand type mismatch

On the other hand, using a prefix that identifies the variable type can immediately warn you of the need to convert the variable to a different type before you use it. The following line combines two variables that have the prefix gd with text strings. The prefix indicates that the variables are global variables of type Date. Notice that the variable type information provided by the second character of the prefix alerts you to a potential problem: You cannot directly combine text with dates. Therefore, you know to use DTOC() before concatenating the variables with text, as the following code illustrates.

? 'List records from ' + DTOC(gdstart) + ;
            ' to ' + DTOC(gdend)

Table 4.3 lists the variable types in Visual FoxPro, along with suggested prefixes. These prefixes represent Microsoft's recommendations, based on internal representations of these variable types.

Using Case to Make Variables Readable  A variation on the preceding naming examples uses an underscore character between the prefix characters and the rest of the variable name. Often, underscores are included between individual words in the variable name. This personal preference of some developers is not part of the new Microsoft recommendation, but you might encounter it in the naming convention used by other applications. In fact, the recommended conventions proposed for Visual Basic, Access, and Visual FoxPro do not include the underscore anywhere in a variable name. The conventions rely on the fact that the first capitalized letter indicates the beginning of the unique portion of the variable name and the end of the prefix. Also, when the variable name consists of two or more words, the first letter of each word also begins with a capital letter. The rest of the characters are always lowercase.

The only time the recommended convention could conceivably pose a problem is if you use an external text editor that does not honor case. (That possibility is relatively remote these days.) On the other hand, Visual FoxPro does not yet completely honor the case of field names, which detracts from their readability. Most programmers define their variable names in many different ways. No matter how you do it, you should use the variable declarations and naming standards required by your client or customers. If these requirements do not exist, you should develop them for your own use. You should use names that mean something, such as fileAccountJuly or FileAccountJuly. However you choose to do it, you should have some organization and sensibility to your methods.

Keep in mind that although you can enter variable names with the uppercase and lowercase rule as suggested earlier in this chapter, Visual FoxPro is case insensitive. FoxPro really doesn't care whether the variable is called lsOrderEnter or lsorderenter-both variables represent the same data memory location. This means that the burden of following such a naming convention is solely on your shoulders. Visual FoxPro's Documenting Wizard not only supports all uppercase or all lowercase, but also uses the case of the first occurrence of a variable and matches all subsequent occurrences. Thus, it even supports mixed-case variable names. Using initial caps for each word in a variable name, however, makes the variable easier to read.

Remember, FoxPro does not require that you use a naming convention. But following any naming convention (even one that you create yourself) generates the following benefits:

• It makes identifying the scope and type of a variable easier.
• It reduces syntax errors and undefined variable errors.
• It makes it easier for other programmers to identify the purpose of a variable when they look at your code.

Places where you might not want to use these prefixes in variable names include:

• Constants created with #DEFINE. (In fact, the recommended convention for these variable names is all uppercase.)
• Class names, properties, or methods.

Naming Fields in Tables

If you need to use free tables in an application, Visual FoxPro continues to limit field names to 10 characters. Even in such cases, some developers have proposed using character prefixes. One three-character prefix convention uses the first two characters of the prefix to uniquely identify the table. These characters can be the first two characters of the table name, but they don't have to be. The third character is an underscore to clearly separate the prefix from the rest of the field name.

If the same field appears in multiple tables, you can continue to use a table prefix. However, you should make the remaining seven characters exactly the same in each table in which the field appears. Suppose that you have a Style field in several databases. If style has the same meaning in each database, you might have:

or_Style       for the style in the order file
pr_Style       for the style in the product file
in_Style       for the style in the inventory file

However, if style means something different in each file, the seven characters should uniquely reflect this difference, as follows:

or_CStyle      for customer style
pr_PStyle      for product style
in_ClStyle    for cloth style

The recommended Visual FoxPro naming convention used with table fields uses only the first character of a field name to identify its type (refer to Table 4.2). The remaining nine characters uniquely identify the field. Because the same field name can appear in more than one table, you should always precede it with the file alias when you use it in code, as shown in the following example:

customer.cLastName
order.dOrderDate

Never use just a letter to identify a work area, such as A.cLastName. Such a practice restricts programs to always opening tables in the same work area. When you write generalized code that more than one procedure can call, you cannot always guarantee a work area's availability for a table. Therefore, always reference fields by their table alias.

Applications rewritten with Visual FoxPro can take advantage of the enhanced table features of a database. (It is unlikely that anyone will modify existing FoxPro applications just to add long character names. As part of an application rewrite to take advantage of other VFP features, however, converting to longer character names makes sense.)

When you add a table to a database, you can define 128-character field names. As indicated earlier, 128 characters may be overkill. With this many available characters, there is no reason why you cannot adopt one of the naming conventions and still define significant names.

The trade-off in using a naming convention for table fields is the fact that some commands and procedures might not work with some conventions. Suppose that you include a table-prefix code in each field name. With 128 characters, you could include the entire table or alias name. But code that uses SCATTER and GATHER to transfer data between tables will not work because the prefix names would be different. Furthermore, Visual FoxPro will not automatically identify relations between tables. On the other hand, if you limit the prefix to a single field-type character in all tables, SCATTER, GATHER, and other table commands continue to work well.

Finally, even if you adopt a naming convention for your table variables, using commands such as SCATTER can lead to unexpected problems. When you scatter table fields to memory variables, Visual FoxPro creates a memory variable with the exact name as the field. When you use that variable without an alias identifier, Visual FoxPro makes the following assumptions about whether you mean the table variable or the memory variable:

• Any variable used with STORE is assumed to be a memory variable.
• A variable that is assigned a value with a REPLACE statement is assumed to be a table variable.
• The variable on the left side of an equation is assumed to be a memory variable.
• Any variable on the right side of an equation is first assumed to be a table variable and then a memory variable.

The following equation takes the table variable Quantity, adds sale quantity (SaleQty) to it, and then saves the sum in the memory variable Quantity:

Quantity = Quantity + SaleQty

If you have this statement in your code, you may wonder why Quantity never seems to increase. The following redefined statement, using the recommended naming convention, makes the assignment clearer:

m.nQuantity = m.nQuantity + m.lnSaleQty

This statement tells you that nQuantity is a numeric variable saved to a memory variable from a table (because the prefix has only a single character). It also clearly adds a local numeric memory variable, lnSaleQty, which represents the sales quantity.

Naming Objects

When you create an instance of a class, you store a pointer to it in a reference variable. You might want to include a special prefix to identify its class type. When you are looking at a property reference such as the following, it can be difficult to guess what class was used to create this object:

? Customer.City.Value

You may guess that Value represents the name of the city; therefore, the object probably is a text box within a form. The form is referenced by the object-reference name Customer, and the text box has the object-reference name City. But it could just as easily be a list or a combo box. The point is that you cannot be really sure to which class this object belongs. Why is this important? Knowing the class of an object tells you what properties, events, and methods to expect from it. Notice how much more information you know about the object when it includes an object prefix. Immediately, you know that it is a text box in a form such as the following:

? frmCustomer.txtCity.Value

This modified expression defines the object hierarchy. It says that a form named Customer contains a text box named City and that it returns the object's value. The recommended prefixes for naming object references are listed in Table 4.4.

This naming convention distinguishes object references by a three-character prefix.

Chapter 13, "Introduction to Object-Oriented Programming," describes ways to use a reference variable to shorten the full object reference. Because the complete reference can be rather lengthy to type, consider assigning aliases to recurring objects using the prefix for the lowest-level object, as follows:

txtCustCity = OrderForm.CustomerInfo.CityName
txtCustCity.Value = 'Redmond'

Naming Issues Across Related Applications

All the conventions in the preceding section apply whether you develop a single application or multiple applications. However, it is more common for problems to occur when different teams develop different modules of larger applications. There are more possibilities for naming, scoping, and typing inconsistencies. The following paragraphs describe a few of these possibilities:

• Suppose that two developers create two separate tables that share several common fields, but they name the fields differently and assign them different sizes. (In variations on this scenario, the developers name the fields consistently but assign them different sizes or assign the same sizes but different names.)
• Now, suppose that you have the opposite situation: Two developers create fields in separate tables that have exactly the same name and definition. The problem is that the fields actually represent two different things.
• Suppose that two developers create modules, each using his own data-naming conventions. If both developers use entirely different conventions, it may be possible to bring one module into alignment with the other by using a few replace statements. The real problem is when the naming conventions used by the two developers have common prefixes that do not mean the same thing. Suppose that one developer puts field type first and scope second and the other developer puts scope first and type second. In this case, does a variable labeled pl_thing represent a private Logical or a local Picture?
• Suppose that two developers create two separate applications and use the same variable name for different purposes. Each developer defines the variable as a different type, therefore causing confusion. However, a most difficult obstacle to resolve occurs when the same variables from the two separate applications have a different definition but are of the same data type. It takes longer to realize that the variables are actually different. If the applications are truly separate, this situation might not pose more than an academic question. But if the applications form two modules in a system, at some point a third developer might look at both applications and attempt to form a relation by using this field between the two tables.

Not many data dictionaries are products on the market yet, especially products that deal with cross-application development. But as Visual FoxPro begins to become more a corporate development tool than a department tool, the need for these tools will force their creation. In the meantime, you might want to consider assigning one person to be responsible for a list of field names and their definitions. Then, when anyone needs to create a new database, he must first consult this list to see whether the names that he wants are already in use, with the same or different meanings.

Another option is to create your own data dictionary. Our experience in developing and working with a data dictionary proved that it can quickly become an essential tool to your project.

Using Enterprise-Wide Naming Conventions

Enterprise issues expand on the cross-application issues, especially as development teams become separated over greater distances. In developing client/server applications, the focus on consistency switches to a central data repository. In some ways, this situation is a benefit because it becomes easier for everyone to determine what field names have already been used and how they have been defined. On the other hand, no system has the internal intelligence to prevent the types of situations mentioned earlier in this chapter.

One of the greatest potential areas for problems is development of applications in different departments of the same company, because there usually is little coordination between application developers in each department. As a result, the risk of naming inconsistencies increases dramatically. As long as each department operates independently of the other, these problems never surface. But as soon as two departments are required to work together on a common system, the naming inconsistencies create significant problems. A company-wide naming convention will not eliminate all these problems, but it will reduce them.

Other Advanced Features of Visual FoxProís Database Container

In Chapter 3 "Defining Databases, Tables, and Indexes," we mentioned a few of the advanced features that Visual FoxPro's data container offers at the field level in addition to its capability to assign 128-character field names. Although this situation tremendously improves the 10-character limitation of free tables, it comes with a price: When you begin using 128-character field names, going back to 10-character names is difficult. All programs, forms, reports, and other files that reference the table will require changes to referenced, shortened field names.

Using Record-Level Validations

You can define additional properties at table level by clicking the Table Properties button. Figure 4.1 shows the Table Properties dialog box with its six additional fields.

Figure 4.1 : The Table Properties dialog box shows a record-level validation rule.

The validation rule in this dialog box is a record-level validation. Visual FoxPro triggers this rule when you change any value in the record and attempt to move to another record. When you are in a browse window, form, or other interface feature that enables scanning of records, you can move between records without triggering the validation only if you don't make changes.

When you use record-level validation, the validation code cannot change any field in the current record or move the record pointer. It can, however, compare the values of one field with those of another, as follows:

(dHireDate - dBirthDate) > 18 * 365.25

The code can also perform a lookup validation against another file:

SEEK(cCustId, 'CUSTOMER')

In Figure 4.1, the record validation is more complex than a single expression. Therefore, a user-defined function (UDF) calls the validation code that is saved as a stored procedure in the database.

Any validation expression or function call must evaluate to a logical result. If the field validation returns .F., Visual FoxPro keeps the record pointer in the same record and does not save any changes made to it; it also displays the validation text. Following is the full text for the validation text that was cut off in Figure 4.1:

"Record changes will not be accepted!"

To validate the records, you need to use a new Visual FoxPro function called GETFLDSTATE. This function determines whether a field in a table or cursor has changed during the current command or whether the delete status has changed. The basic syntax of this command is

GETFLDSTATE(cFieldName|nFieldNumber [,cTableAlias|nWorkArea])

If you include a field name or number, GETFLDSTATE returns one of the values listed in Table 4.5 indicating the status of that field. The validation text in Listing 4.2 uses GETFLDSTATE to determine which error message to display based on whether the sales-total field has changed.

You can also return the information about all fields in the current table with GETFLDSTATE(-1). This command returns a string. Its first value represents the deletion-status flag, which is followed by one return value for each field in the record.

Calling GETFLDSTATE() with an argument of 0 returns a single value that represents the status of the deletion flag.

The code segment in Listing 4.2 shows the complete validation code associated with the record-validation function shown in Figure 4.2.

Figure 4.2 : The Edit Relationship dialog box, used by the Database Designer, defines which fields connect the tables.

FUNCTION PAYTYPE
* This function checks the payment type as a function
* of the total order amount to validate the record.
LOCAL llReturnCode, lnChangeCheck

* Check if any changes were made to either the sales total or
* the payment method.
* Check if customer attempts to pay < $10 by credit card.
  IF (MOD(GETFLDSTATE('nSalesTotal'),2) = 0 OR ;
      MOD(GETFLDSTATE('cPaymentMethod'),2) = 0) AND ;
      nSalesTotal < 10.00
  * Check for payment method of 'CA' - Cash
    IF cPaymentMethod # 'CA'
      = MESSAGEBOX('Orders of less than $10 must be cash')
      RETURN .F.
    ENDIF
  ENDIF

* If paid by credit card, Check if credit card was approved.
  IF (MOD(GETFLDSTATE('nSalesTotal'),2) = 0 OR ;
      MOD(GETFLDSTATE('cPaymentMethod'),2) = 0) AND ;
      cPaymentMethod # 'CA'
  * Ask if card was approved. If not reject record.
    IF MESSAGEBOX('Was card approved?', 36) = 7
      = MESSAGEBOX('Cannot accept a credit card ' + ;
        'order without approval')
      RETURN .F.
    ENDIF
  ENDIF

RETURN .T.

Notice that the function first checks to see whether the change that triggered the validation occurred to either the sales-total or payment-method field. Remember that a change to any field triggers the record validation. Therefore, you should determine whether to perform the validation because you certainly don't want to perform the validation for every field that changes.

Even if you don't move off the current record, but instead attempt to close the browse window or form after making a change to a field, Visual FoxPro still performs the record validation.

Maintaining Referential Integrity

In general, referential integrity defines which operations are permissible between tables that are connected with relations. The basic premise is that a primary key value in the parent table must have a corresponding lookup or foreign key in another table (called the child table). Referential integrity treats records that do not meet these criteria as invalid.

You can implement referential integrity in several ways; you need to decide what method best suits the data. Consider the basic relation between general order information and detailed order information, for example. The Tasmanian Trader example provided with Visual FoxPro represents these files as ORDERS.DBF and ORDITEMS.DBF, respectively.

The orders table contains information that is unique to the order as a whole. This information includes Order_Id, Order_Date, Customer_Id, and many other fields. The order detail table contains specifics on individual items ordered, such as Order_Id, Product_Id, Quantity, and Unit_Price. The relation that ties these two tables together is based on Order_Id.

When you add a record to ORDERS.DBF, you do so with the intent of adding details to ORDITEMS.DBF. After all, an order without details is not a complete order. Similarly, you would never think of adding details to ORDITEMS.DBF without also adding an order record to ORDERS.DBF. These files reference each other in a parent/child relation: ORDERS.DBF represents the parent and ORDITEMS.DBF is the child. The analogy is that you can have a parent without a child, but you cannot have a child without a parent.

Forming Persistent Relations

Persistent relations define relations between two tables, and are stored in the Database Designer. Visual FoxPro automatically uses them each time the tables are opened. This feature is especially useful for automatically setting the relations between tables in SQL statements and for creating lookups, validations, and the data environment of forms and reports.

Persistent relations are sometimes called permanent relations, as opposed to temporary relations created with the SET RELATION command. The reason that SET relations are temporary is that FoxPro dissolves them when you exit FoxPro or issue the SET RELATION command by itself. Persistent relations remain in place between applications.

Creating Relations Among Tables  To create a relation between tables, return to the Database Designer window. Figure 4.2 shows two tables between which you need to define relations.

Create the following indexes using the Table Designer dialog box, if you do not have them already:

In CUSTOMER.DBF:

TAG: CUSTID	PRIMARY	INDEX ON: cCustomerId
TAG: CUSTNAME	REGULAR	INDEX ON: cLastName + cFirstName
TAG: COMPANY	REGULAR	INDEX ON: cCompanyName + STR(cOutstandingBillsTotal, 9, 2)

In SALES.DBF:

TAG: ORDERID	PRIMARY	INDEX ON: cOrderId
TAG: CUSTID	REGULAR	INDEX ON: cCustomerId
TAG: SALESMAN	REGULAR	INDEX ON: cSalesmanId
TAG: SALES	CANDIDATE	INDEX ON: DTOC(dSaleDate)+cOrderId

In DETAILS.DBF:

TAG: DETAILS

PRIMARY

INDEX ON: cOrderId+cItemId

Use the scrollbars of each table to display the index list at the bottom. To form a relation, simply click the index name in one of the tables and drag it to an index in another table. Suppose that you want to find the sales information for each customer. Click CUSTID in the CUSTOMER table and drag it to CUSTID in the SALES table. FoxPro displays the Edit Relationship dialog box (refer to Figure 4.2).

Because you dragged the index from one table to another, FoxPro automatically fills in the customers' names. FoxPro also defines the relationship as one-to-many, because there can be many sales orders for each customer. Finally, to accept the relation, simply click OK.

FoxPro follows a few simple rules to define the relation type. First, it assumes that the start table, or "from" table, is on the "one" side and must be a candidate or primary index. (You cannot start a persistent relation from a unique or regular index.) If you then connect to a primary, or candidate, index in the related table, FoxPro knows that it must be a one-to-one relation because these are unique indexes that include every record in the table. Connecting to any other index (regular or unique) enables more than one record on the "to" side; therefore, FoxPro assumes a one-to-many relationship. Remember that a unique index does not prohibit multiple records that have the same index value, it merely keeps a pointer to only the first one.

Create a similar relation between ORDER_ID in SALES.DBF and ORDER_ID in DETAILS.DBF. The details side of this relation defines a many relation because each order can contain many ordered items.

In the Database Designer window, you now see a connecting line between the indexes. FoxPro displays the "one" side of a relation with a single line coming out of the table. The "many" side has three lines leading from the index.

Breaking a Relation  To break a relation that is no longer needed or defined incorrectly, merely click it and press the Delete key. You can also right-click and then choose Remove Relationship from the shortcut menu.

Creating Self-Referential Relations  A self-referential relation relates one field in a table to another field in the same table. An example that illustrates this concept involves the relation between a supervisor and an employee. An employee table contains a record for each company employee, identified by an employee ID. Each record has one field that identifies the ID of the employee's supervisor. That ID is also the supervisor's employee number. Thus, by referencing the employee's number, you can get the supervisor's name, the name of that supervisor's supervisor, and so on.

Figure 4.3 shows the Database Designer after the formation of the relation between two records within empl2. In this case, custid is defined as the primary index and superv is defined as a regular index.

Figure 4.3 : This Database Designer view shows a self-referencing relation.

Although queries have not been discussed yet, the following is a query that lists every employee and his or her supervisor's name using the self-referencing relation in EMPL2:

SELECT A.EmplId, ;
   A.LastName AS EMPLOYEE_LAST_NAME, ;
   A.FirstName AS EMPLOYEE_FIRST_NAME, ;
   B.LastName AS SUPERVISOR_LAST_NAME ;
   B.FirstName AS SUPERVISOR_FIRST_NAME ;
   FROM EMPL2 A, EMPL2 B ;
   WHERE A.Supervisor = B.EmplId

Using the Referential Integrity Builder

Visual FoxPro adds a powerful new feature by providing engine-based referential integrity. To access the Referential Integrity (RI) Builder:

• Open the database that contains the tables for which you want to define referential integrity rules.
• Right-click the persistent relation, or double-left-click the relation to display the Edit Relation box.

The first method displays a menu that contains the Referential Integrity option; the second method displays a command button. Choosing either option displays the builder shown in Figure 4.4.

Figure 4.4 : Referential Integrity Builder opened for database \VFP\SAMPLES\ TASTRADE\DATA\ TASTRADE.DBC, showing all the table relations and their current RI rules.

The bottom half of the builder lists each relation on a separate line or row. The columns across the row name the parent and child tables. Next are three columns for the Update, Delete, and Insert integrity rules. Initially, all these columns say Ignore. However, you can define rules for each relation and action. Finally, the last two columns define the parent and child tags involved in the relation.

Only the referential-rule columns can be modified. If you select any of these columns by clicking them, a drop-down-arrow button appears; when clicked, that button displays the referential options. These options are also defined in the page frame in the top half of the builder.

Each referential action has its own page, which lists the available options. The referential options available when you are updating the key value in a parent table include the following:

• Cascade. This option updates all child records with the new parent key value that had the same old parent key value.
• Restrict. This option checks to see whether any child records exist with the current parent key value. If so, FoxPro prohibits you from changing the parent key value.
• Ignore. This option performs no referential integrity and enables changes to the parent key without regard to any related child records.

As you can see, each of these options has an option (radio) button. Clicking the button changes the selected rule for the highlighted relation in the top half of the builder. Thus, you have two ways to select the referential rules.

The rules for deleting parent records are similar to those for updating. The rules for inserting records, however, apply from the child side of the relation. The two possible rules provided here are:

• Restrict. This rule prevents the insertion of a child record if a parent record with the same key value does not exist.
• Ignore. This option performs no referential-integrity checks.

After you define the referential integrity rules for each relation and action, click OK to exit the builder. You might want to cascade key updates made in the CUSTOMER table to the ORDERS table. On the other hand, you might want to restrict deletion of CUSTOMER records if ORDERS records exist. Finally, you want to restrict the entry of an ORDERS record if the customer key does not exist in CUSTOMER.

When you click OK, the builder displays a dialog box that asks you to save your changes, generates the RI code, and exits. This process creates a set of triggers and stored procedures in the database. If the database previously defined triggers or a stored procedure, it makes a backup copy before overwriting it. If you defined stored procedures for other features, such as validations, you need to manually copy them from the backup to the new stored procedures.

After the RI Builder completes this task, you can open the Table Designer and select Table Properties to view the added triggers. Alternatively, you can view the stored procedures by clicking the Edit Stored Procedure button on the Database Designer toolbar.

Although the Referential Integrity Builder is a fast and easy way to add common referential integrity rules to your database relations, you define referential integrity in many ways. Some of these ways are discussed in the following section.

Building Your Own Referential Integrity

To illustrate some of these principles, the Web site Que has set up to accompany this book contains a database called PTOFSALE (for point-of-sale). This database is a modification of the Tastrade files, which show one way of implementing referential integrity.

The following section examines referential integrity rules created for two files in the database: SALES.DBF and DETAILS.DBF. These files loosely resemble ORDERS.DBF and ORDITEMS.DBF, from Tasmanian Traders, but with fewer fields and records to help illustrate the points. By using separate files, you can experiment with triggers without worrying about potential damage to your Tasmanian Trader example. Table 4.6 displays the field structure for SALES.DBF; Table 4.7 displays the field structure for DETAILS.DBF.

Using Triggers

The next three options after Record Validations in the Table Properties dialog box are called triggers. Visual FoxPro executes these triggers when you insert, update, or delete a record in the table. As is true of record validation rules, you need to store the code for triggers as stored procedures in the database.

You can use triggers for additional calculations or validations when you perform any of these three operations. You could send an email message to Purchasing when the inventory of any item falls below a predefined stock value, for example. You could also log all changes made to a table or create your own referential integrity rules. However, you cannot do several things with them. You can't do the following:

• Move the record pointer in the current work area
• Change the value of any field in the current record
• Close the current work area or open another file in the same work area

The most common use for triggers is to implement referential integrity.

Initializing the Stored Procedures for Triggers  In this section, you learn how to define the triggers. Figure 4.1 shows the extended options for triggers in SALES.DBF, defined as follows:

Insert Trigger:          RISALES("I")
Update Trigger:        RISALES("U")
Delete Trigger:         RISALES("D")

Next, define a similar set of triggers for DETAILS.DBF:

Insert Trigger:          RIDETAILS("I")
Update Trigger:        RIDETAILS("U")
Delete Trigger:         RIDETAILS("D")

Notice that in both cases, triggers for each table call the same function, merely passing it a different single-character parameter to identify the operation. The reason is that referential integrity requires some common additional support, or housekeeping tasks. These tasks are called from RIDETAILS and RISALES, so they do not have to be repeated. Each of these requirements is examined later in this chapter; the following section starts by examining RISALES().

Defining Triggers for the Parent Table  Whenever Visual FoxPro senses the insertion, updating, or deletion of a record, it checks to see whether a trigger for that event exists. If it does, as in this case, VFP executes the trigger. All triggers for the table SALES.DBF call function RISALES(), as shown in Listing 4.3.

****************
FUNCTION RISALES
* This routine is called by the triggers in SALES.DBF
LPARAMETERS lcAction
LOCAL llReturnValue, Dummy

* Start Transaction
  Dummy         = IIF(TYPE("nLevel")<>"N" OR nLevel=0, ;
                  RISTART(), "")
  nLevel        = nLevel + 1
  llReturnValue = .F.

* Perform action
  DO CASE
    CASE TYPE('lcAction') # 'C' ;
         OR !UPPER(lcAction) $ 'DIU' ;
         OR LEN(lcAction)>1
      ERROR 'Invalid action code passed to RISALES'
    CASE UPPER(lcAction) == 'D'
      llReturnValue = SALEDEL()
    CASE UPPER(lcAction) == 'I'
      llReturnValue = SALEINS()
    CASE UPPER(lcAction) == 'U'
      llReturnValue = SALEMOD()
  ENDCASE

* End transaction
  nLevel = nLevel - 1
  Dummy  = IIF(nLevel = 0, RIEND(llReturnValue), 0)

RETURN llReturnValue

Notice that this routine begins by assigning the passed character to a local parameter (LPARAMETER) called lcAction. It then defines a local variable, which it uses to return a logical value to the trigger, llReturnValue.

This function itself has three parts. The first part initializes a transaction by checking the current value of variable nLevel. If you object that nLevel is not defined yet, you are correct. That is the purpose of the first conditional test in the IIF statement: It takes advantage of the way Visual FoxPro evaluates expressions. When a conditional statement has more than one expression, VFP evaluates them one at a time from left to right. As soon as VFP can evaluate the expression, it stops, even if it does not evaluate all conditions.

In this case, IIF() contains two conditions connected with OR. The logical result of this expression is true as long as one or the other condition is true. Visual FoxPro knows this. It evaluates the first expression, and because nLevel is undefined, it returns a type of "U". Therefore, the first condition is true. Because this is all that FoxPro needs to evaluate the entire expression, it never checks to see whether nLevel is equal to zero. If nLevel were equal to zero, it would generate an error because nLevel is undefined. Thus, FoxPro executes function RISTART() when the expression is true, as occurs at the start of the first trigger event.

This first section also increments the value of nLevel, which RISTART() initializes to zero. This variable tracks how many triggers have been set and which one is being processed. An nLevel value of 1 indicates that processing should execute code related to the primary trigger event. Higher values of nLevel evaluate cascade events.

The second portion of RISALES() uses a CASE statement to determine whether the parameter passed is valid and then to branch to the correct function. Notice, again, that a compound test is used in the first CASE statement. If the TYPE of lcAction is not Character, Visual FoxPro ignores the rest of the conditions-which only makes sense because lcAction should be Character.

Finally, the last section performs a cleanup. First, the section decreases the trigger level. If it has executed all the triggers (nLevel=0), it executes one final function: RIEND().

Now examine RISTART, shown in Listing 4.4.

****************
FUNCTION RISTART
* Call this program when starting Referential Integrity.
* It initializes several variables.
  IF TYPE("nLevel") <> 'N'
    PUBLIC nLevel
    nLevel = 0
  ENDIF

  IF TYPE("cCursors") <> "C"
    PUBLIC cCursors
  ENDIF

  IF nLevel = 0
    BEGIN TRANSACTION
    PUBLIC cOldError, nError
    cCursors  = ""
    cOldError = ON("ERROR")
    nError    = 0
    ON ERROR nError = ERROR()
  ENDIF
RETURN

As you can see, the routine initializes nLevel to zero and makes it a public variable. This routine also initializes two other variables. The first variable, cCursors, tracks the names of cursors opened by the code in the triggers. The second variable, cOldError, stores the current ON ERROR action.

The following statement is very important:

BEGIN TRANSACTION

This statement defines the beginning of a transaction and tells Visual FoxPro to buffer all record changes that are made until it executes an END TRANSACTION. END TRANSACTION writes the changes from the buffer permanently to the file. If you decide to cancel changes, you can use ROLLBACK or TABLEREVERT() to back out or discard changes. Function TABLEREVERT() has the following syntax:

TABLEREVERT([lAllRows][,cTableAlias|nWorkArea]

The first parameter, when it is set to true, discards changes made to all records in the current table or in the table referenced by an alias or work-area number. When it is set to false, the parameter discards only changes made in the current record.

Up to this point, the routines used have been fairly generic. In fact, you could use them in your own applications virtually unchanged, except for the functions called by the CASE statement in RISALES. The following section examines the first function that performs the real action of the trigger.

Defining a Cascade Delete Trigger  The SALEDEL() function defines the trigger action when Visual FoxPro senses the deletion of a record in SALES.DBF. The purpose of this routine is to cause a delete cascade to remove all order details associated with the deleted order record. After all, when the parent order record no longer exists, the child details are no longer valid. Listing 4.5 shows the code required to accomplish this task.

****************
FUNCTION SALEDEL
* If deleting a sales record, delete all details
LOCAL  llReturnValue, lcOrderId, lnDetArea

  llReturnValue = .T.
  IF nLevel = 1
    lcOrderId     = cOrderId
    lnDetArea     = RIOPEN('DETAILS')
    SELECT (lnDetArea)
    SCAN FOR cOrderId = lcOrderId
      llReturnValue = RIDELETE()
    ENDSCAN
    SELECT sales
  ENDIF

RETURN llReturnValue

This relatively short routine starts by storing the order ID from the sales record in lcOrderId and then uses RIOPEN() to safely open a copy of DETAILS. The routine opens DETAILS.DBF without setting a tag so that Rushmore can fully optimize the FOR clause. The code uses SCAN FOR to find all records with a matching order ID; it then deletes each matching record by calling the RIDELETE() function.

The SALEDEL() function introduces two new functions, both of which are generalized functions that you can use in any referential integrity situation. In fact, triggers from the DETAILS table also use them. The following section discusses RIOPEN().

Using a Common Procedure to Open Cursors  The RIOPEN() function supports two parameters: one for the table name and the other for an optional tag name. Listing 4.6 shows the RIOPEN() code.

***************
FUNCTION RIOPEN
* This procedure opens cursors for use during Referential
* Integrity checks since operations that perform record
* pointer moves are not allowed directly.
LPARAMETERS lcTable, lcTag
LOCAL lnNewArea, nInUseArea

  nInUseArea = ATC(lcTable+"*", cCursors)

* Open reference table
  IF nInUseArea = 0
    SELECT 0
    lnNewArea = SELECT()
    IF EMPTY(lcTag)
      USE (lcTable) ;
          ALIAS ("RI_"+LTRIM(STR(lnNewArea))) ;
          AGAIN SHARE
    ELSE
      USE (lcTable) ;
          ORDER (lcTag) ;
          ALIAS ("RI_"+LTRIM(STR(lnNewArea))) ;
          AGAIN SHARE
    ENDIF
    cCursors = cCursors + UPPER(lcTable) + "?" + ;
               STR(lnNewArea,5)
  ELSE
  * Retrieve work area of referential integrity cursor
    nNewArea = VAL(SUBSTR(cCursors, ;
               nInUseArea + LEN(lcTable) + 1, 5))
    cCursors = STRTRAN(cCursors, ;
               UPPER(lcTable) + "*" + STR(nNewArea,5), ;
               UPPER(lcTable) + "?" + STR(nNewArea,5))
  ENDIF

RETURN (lnNewArea)

This routine begins by checking variable cCursors to see whether the table has already been opened for use by the referential integrity routines. Variable cCursors has the following structure:

• Table name
• Character to identify whether the table is in use for another trigger
• Work-area number where table is open

Actually, cCursors is a string that repeats the preceding structure for each open table. If the table is already open, all that the routine needs to do is retrieve the work area that it is in and change the in-use character flag from * to ?. Then, RIOPEN() returns the work-area number. This tracking is required to keep track of which tables are open and can have their record pointer moved. Remember that you cannot move the record pointer of tables that are directly involved in the current trigger.

If the table is not open, RIOPEN() selects the next available work area with SELECT 0 and opens the table with the AGAIN and SHARE clauses. The AGAIN clause creates a cursor that enables you to open the table multiple times. The table could already be open, but referential integrity requires a separate copy with its own tag and record pointer. Systems developed for use on a network require the SHARE clause so that multiple users can have the same file open at the same time.

The last thing that RIOPEN() does is update variable cCursors with the following information:

• The name of the table
• The ? character, indicating that the file is in active use
• The work-area number

The other routine used by SALEDEL() is RIDELETE(), which performs the actual deletion of records referenced by triggers. In this case, SALEDEL() has already changed the default work area to DETAILS.DBF and points to a record to delete. However, RIDELETE() cannot simply delete the record, it first checks to see whether it can get a record lock. If not, REDELETE() begins a rollback process by returning a false value in llReturnValue. This value eventually works its way back up to the trigger, which cancels the deletion of the parent record and any child records deleted for this parent since the trigger began.

Listing 4.7 shows the code for RIDELETE().

*****************
FUNCTION RIDELETE
* Delete the current record in the current area
LOCAL llReturnValue

llReturnValue = .T.
* Attempt to get a record lock
  IF (UPPER(SYS(2011))='RECORD LOCKED' and !DELETED()) OR !RLOCK()
    llReturnValue = .F.
  ELSE
  * If not deleted, delete it.
    IF !DELETED()
      DELETE
      nError = 0
      UNLOCK RECORD (RECNO())
      IF nError <> 0
        = TABLEREVERT()
        UNLOCK RECORD (RECNO())
        llReturnValue = .F.
      ENDIF
    ENDIF
  ENDIF
RETURN llReturnValue

When it obtains a record lock, RIDELETE() checks to see whether the record has already been deleted. (It hardly makes sense to delete it twice.) The function then deletes the current record in the current table. If an error occurs, it executes a function called TABLEREVERT(), which cancels the delete. RIDELETE() passes the llReturnValue back to the trigger to cancel any related deletions.

If everything deletes successfully, llReturnValue passes a value of true back to the trigger and the event ends. Although the trigger on the SALES file can perform other actions, it is the programmer's responsibility to determine appropriate actions in each situation.

After a procedure is performed by means of a cursor, the procedure can close it. On the other hand, leaving it open until the end of the transaction enables other functions in the program to reuse it. The special character (?) after the table name in variable cCursors indicates that the cursor is currently in use. If so, subsequent functions cannot reuse the cursor because another routine might need the current record-pointer position. (Remember that you cannot move the record-pointer position of a table that is currently involved in a trigger or validation.) When you are done with the cursor, change this character to a different character (*) in variable cCursors to tell the program that it can reuse the cursor. That is the purpose of RIREUSE(): It changes the special character from a question mark (?) to an asterisk(*) to indicate that the cursor can be reused. Listing 4.8 illustrates the code to perform this action.

****************
FUNCTION RIREUSE
* This routine allows reuse of exiting cursor
LPARAMETERS lcTable, lcArea
  cCursors = STRTRAN(cCursors, ;
             UPPER(lcTable) + "?" + STR(lcArea,5), ;
             UPPER(lcTable) + "*" + STR(lcArea,5))
RETURN .T.

Triggers Performing No Action  The insert trigger for SALES calls the SALEINS() function, which simply returns a value of true as shown in the following code. When you are adding a new sales order, there is no reason to check DETAILS.

****************
FUNCTION SALEINS
* No referential action required when adding a parent
RETURN .T.

Performing a Cascade Modify  The last trigger in SALES.DBF occurs when you change any field value in a record. This routine begins by initializing the return variable to true, and then it checks to see whether it is in the first trigger level. This means that you are modifying a SALES record directly, not as a result of deleting DETAIL records for the same order ID.

Next, the routine stores the current order ID in a local variable, along with the "pre-change" order ID. When a transaction begins, think of Visual FoxPro as storing the changes to the current record in a buffer. You can access the changed values of any field by using the variable name directly. However, to access the original values of any field, you need to use the OLDVAL() function.

The SALEMOD() function retrieves both values to determine whether the relational fields (cOrderId, in this case) have changed. If they have not changed, the function exits with a return value of true. Otherwise, this function locates the matching records in DETAILS.DBF and updates the old key values to the new one. This portion of the routine is similar to SALEDEL(), with the exception that it uses a REPLACE statement instead of a call to RIDELETE().

Listing 4.9 shows the code for SALEMOD().

****************
FUNCTION SALEMOD
* If modifying a sales record, modify all details
LOCAL  llReturnValue, lcOrderId, lcOldValue, lnDetArea

  llReturnValue = .T.
  IF nLevel = 1
    lcOrderId     = cOrderId
    lcOldValue    = OLDVAL('cOrderId')

  * If key value changed, updated the child records
    IF lcOrderID <> lcOldValue
      lnDetArea = RIOPEN('DETAILS')
      SELECT (lnDetArea)
      SCAN FOR cOrderId = lcOldValue
        REPLACE cOrderId WITH lcOrderId
      ENDSCAN
      SELECT sales
    ENDIF
  ENDIF
RETURN llReturnValue

Another way to see whether the foreign key has changed uses the GETFLDSTATE() function. However, the function still needs OLDVAL() to find and replace the key value in the child records.

Using Triggers for the Child Table  DETAILS.DBF also has three triggers. Each trigger calls a single routine called RIDETAILS(), with a single-character parameter to identify the trigger. The code of RIDETAILS(), which appears in Listing 4.10, shows that except for the function calls in the CASE statement, it mirrors RISALES().

******************
FUNCTION RIDETAILS
* This routine is called by the triggers in DETAILS.DBF
LPARAMETERS lcAction
LOCAL llReturnValue, Dummy

* Start Transaction
  Dummy         = IIF(TYPE("nLevel")<>"N" OR nLevel=0, ;
                  RISTART(), "")
  nLevel        = nLevel + 1
  llReturnValue = .F.

* Perform action
  DO CASE
    CASE TYPE('lcAction') # 'C' ;
         OR !UPPER(lcAction) $ 'DIU' ;
         OR LEN(lcAction)>1
      ERROR 'Invalid action code passed to RIDETAILS'
    CASE UPPER(lcAction) == 'D'
      llReturnValue = DETAILDEL()
    CASE UPPER(lcAction) == 'I'
      llReturnValue = DETAILINS()
    CASE UPPER(lcAction) == 'U'
      llReturnValue = DETAILMOD()
  ENDCASE

* End transaction
  nLevel = nLevel - 1
  Dummy  = IIF(nLevel = 0, RIEND(llReturnValue), 0)
RETURN llReturnValue

Using a Trigger to Delete Parent When Last Child Is Deleted  When a user deletes an order detail record, you may want to know whether he deleted one of many detail records for the order or the last one. In this case, you should delete the parent record if you delete the last or only child record. However, not every application should delete the parent record. Just because you delete all the employee's projects, for example, does not mean that you delete the employee as well. So the developer must take an active role in determining the expected functionality of each trigger. The code shown in Listing 4.11 implements a trigger that deletes the parent record when the last child is deleted.

******************
FUNCTION DETAILDEL
* Check if all order details are deleted, then delete parent
LOCAL cnt, lcOrderId, lnCurrentArea, ;
      lnSalesArea, lnDetArea, llReturnValue

  llReturnValue   = .T.
  lcOrderId       = cOrderId
  lnCurrentArea   = SELECT()

  IF !EMPTY(lcOrderId)
    lnDetArea     = RIOPEN('DETAILS')
    cnt           = 0
    SELECT (lnDetArea)
    SCAN FOR cOrderId = lcOrderId AND !DELETED()
      cnt         = cnt + 1
    ENDSCAN

    IF cnt = 1 AND nLevel =1
      lnSaleArea  = RIOPEN('SALES', 'ORDERID')
      IF SEEK(lcOrderId)
        llReturnValue = RIDELETE()
      ENDIF
      = RIREUSE('SALES', lnSaleArea)
    ENDIF
    SELECT (lnCurrentArea)
  ENDIF
RETURN llReturnValue

This routine first checks to see whether the OrderId exists before proceeding so that the user can delete a blank record without going through the rest of the validation. Then, the routine opens a second occurrence of DETAILS and counts the number of active records that have the same OrderId. If this number is greater than 1, and if the trigger sequence began by deleting details, it looks for and deletes the parent record.

Why check for a count greater than 1 if the conditional string contains the function NOT DELETED()? During a transaction, the record is not recognized as deleted until you commit the transaction.

The reason why you have to check the level is that SALEDEL() can also call DETAILDEL(). When SALEDEL() initiates a DETAIL record delete, the function does not have to go back to SALEDEL() a second time, which could potentially lead to an endless loop. But when the trigger initiates the detail delete, you do want to check SALEDEL() when you delete the last child.

Using an Insert Trigger that Checks for a Parent Record  The second trigger for file DETAILS occurs when you add a record or recall a deleted one. Remember that Visual FoxPro triggers the insert trigger when you add a new record, not after you fill in its fields. Therefore, FoxPro triggers for a new, blank record. Because you do not want to check for a blank foreign key in SALES.DBF, the DETAILINS() function tests for an empty OrderId. When that field is empty, the function simply skips the check.

On the other hand, a recalled record should have a corresponding parent. Therefore, when OrderId exists, the following routine uses it to search a copy of SALES.DBF (see Listing 4.12). If the routine finds a matching order record, it completes the record recall; otherwise, the record remains deleted. When this happens, a message box appears telling the user that there is No corresponding order for this detail.

******************
FUNCTION DETAILINS
* Insert a detail record only if a sales record exists
LOCAL lcOrderId, lnSaleArea, llReturnValue, lnCurrentArea

  llReturnValue =  .T.
  lcOrderId     = cOrderId
  lnCurrentArea = SELECT()

  IF !EMPTY(lcOrderId)
    lnSaleArea    = RIOPEN('SALES', 'ORDERID')
    llReturnValue = SEEK(lcOrderId, lnSaleArea) AND ;
                    !DELETED(lnSaleArea)
    = RIREUSE('SALES', lnSaleArea)
    IF !llReturnValue
      = MESSAGEBOX('No corresponding order for this detail')
    ENDIF
    SELECT (lnCurrentArea)
  ENDIF
RETURN llReturnValue

If triggers cannot guarantee that a new detail record has a corresponding sales record, how can you guarantee it? One method uses the VALID clause on the cOrderId field, and it performs a SEEK against cOrderId in SALES. Barring that, consider checking for a SALES record in the update trigger of DETAILS. After all, filling in a blank field is still an update.

The following section examines the final trigger for DETAILS: the update trigger.

Using a Modify Trigger that Also Checks for a Parent Record  The update trigger, named DETAILMOD(), makes sure that cOrderId in DETAILS matches cOrderId in SALES. The trigger also checks to see whether any other detail records exist with the old cOrderId value. If not, it deletes the parent record that has the old ID.

Of course, there are other ways to handle an order ID modification. The first is to change all records with matching order IDs in DETAILS to the new value. Also, you can change the order ID in the parent record. If a corresponding parent record does not exist for the new order ID, find the SALES record with the preceding order ID and change it first. Then change all corresponding records in DETAILS to match the new order ID.

The code in Listing 4.13 shows the DETAILMOD() function.

******************
FUNCTION DETAILMOD
* Allow key value change if it matches another parent
* If no more child records for this parent, delete it
LOCAL cnt, lcOrderId, lcOldValue, ;
      lnSalesArea, llReturnValue, lnDetArea

  llReturnValue   = .T.

  IF nLevel=1
    lcOrderId       = cOrderId
    lcOldValue      = OLDVAL('cOrderId')

  * First check if new value is a valid parent key
    lnSaleArea      = RIOPEN('SALES', 'ORDERID')
    llReturnValue   = SEEK(lcOrderId, lnSaleArea) AND ;
                      !DELETED(lnSaleArea)
    IF !llReturnValue
      = MESSAGEBOX('No corresponding order for this detail')
    ENDIF

  * New order id is valid, check for other child records in old order
    IF llReturnValue
      cnt             = 0
      lnDetArea       = RIOPEN('DETAILS')
      SELECT (lnDetArea)
      SCAN FOR OLDVAL('cOrderId') = lcOldValue AND !DELETED()
        cnt = cnt + 1
      ENDSCAN
      = RIREUSE('DETAILS', lnDetArea)

    * If no other child records, delete the parent
      IF cnt = 1
        SELECT (lnSaleArea)
        IF SEEK(lcOldValue)
          llReturnValue = RIDELETE()
        ENDIF
      ENDIF
    ENDIF
    SELECT DETAILS
  ENDIF
  = RIREUSE('SALES', lnSaleArea)
RETURN llReturnValue

Again, this routine first checks to see whether DETAILS.cOrderId exists in the parent file SALES.DBF. If not, the routine displays a message and exits with a return value of false. However, if the order ID does exist, the routine first counts the number of active DETAIL records with the old order ID. If no others exist, the routine opens SALES.DBF and deletes the parent record.

Using a Cleanup Routine   Only one routine remains. When Visual FoxPro completes any of these six triggers, it returns to either RISALES() or RIDETAILS(). FoxPro then executes the RIEND() routine, which takes the return value from the trigger and determines whether to commit the transaction or roll it back. To commit the transaction, FoxPro executes END TRANSACTION.

You have a choice, however. You can use ROLLBACK, which discards every change made since the transaction began in the RISTART() function. Alternatively, you can roll back incrementally using TABLEREVERT(). This command rolls back individual (or all) records in one table at a time, as shown in Listing 4.14.

**************
FUNCTION RIEND
* Call this routine to exit the referential integrity check
* It saves changes or reverts to original values depending
* on the value passed to it.
LPARAMETER llSuccess
LOCAL lnXx

* Complete transaction or roll it back
  IF !llSuccess
    IF USED('SALES')
      = TABLEREVERT(.T., 'SALES')
    ENDIF
    IF USED('DETAILS')
      = TABLEREVERT(.T., 'DETAILS')
    ENDIF
  ENDIF
  END TRANSACTION
* or use this code:
*  IF llSuccess
*    END TRANSACTION
*  ELSE
*    ROLLBACK
*  ENDIF

* Reset on error
  IF EMPTY(cOldError)
    ON ERROR
  ELSE
    ON ERROR (cOldError)
  ENDIF

* Remove cursors and reset variables that track them
  FOR lnXx = 1 TO OCCURS("?", cCursors)
    cFound = ATC('?', cCursors, lnXx) + 1
    USE IN (VAL(SUBSTR(cCursors, cFound, 5)))
  ENDFOR
  FOR lnXx = 1 TO OCCURS("*", cCursors)
    cFound = ATC('*', cCursors, lnXx) + 1
    USE IN (VAL(SUBSTR(cCursors, cFound, 5)))
  ENDFOR
  STORE "" TO cCursors
RETURN .T.

This routine reads cCursors to remove the cursors created by referential integrity so that functions can move the record pointer.

In the past, you could easily have programmed these functions into forms. But you could not have done anything about users who directly edited the tables using browse windows or other commands directly from the Command window. Now, by adding triggers to critical tables, you can protect referential integrity even from users who prefer to edit files directly rather than to use forms.

As you can see from these routines, writing your own referential integrity rules is certainly possible-and even necessary, if you want to handle changes differently from the VFP defaults. If you can accept the rules defined by VFP's own Referential Integrity Builder, however, your task will be much easier and more error-proof.

© Copyright, Sams Publishing. All rights reserved.