Special Edition Using Visual FoxPro 6
Special Edition Using Visual FoxPro 6
Advanced Database Management Concepts
- Database Design Techniques
- Data Normalization
- Naming Conventions
- Other Advanced Features of Visual FoxProís Database Container
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.
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.
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
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
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:
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.
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:
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:
OrderId OrderDate OrderNet 00006 08/04/94 59.34 00007 08/05/94 9.18
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.
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:
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
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.
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.
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.
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.
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.
You probably think that the third rule is a misprint. It's not. A memory variable can have up to 255 characters. Using that many characters means:
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.
Listing 4.1 04CODE01.PRG-Public Variables Can Be Referenced Anywhere in Your Program
* 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.
If you use a variable in a program without declaring its scope first, you cannot later declare it public without generating an error.
Declaring variables at the start of a PRG makes them available throughout the PRG, but private only to the PRG. If the PRG is called by another program, these variables will not be known in the calling program. On the other hand, a variable declared public will be known in all PRGs involved in the application after it is declared and defined.
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.
If you need a variable to be known across multiple branches of the procedure call stack, declare it public.
Declaring a variable private in the main routine has almost the same effect as declaring it public. The difference is that the private variable is known only within the current .PRG file. Of course, this makes a difference only when you have one .PRG call another.
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
|Static (Class Variable)||snCounter|
The need for a prefix in static variables that are used to define properties of a class or in event code is less obvious than for other variable scopes because these variables cannot be used anywhere else anyway. This convention does not apply to property or method names, just to other variables used with event method code.
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.
Because naming conventions are optional, you might decide to use different prefix letters for scope. However, Visual FoxPro and Microsoft recommend these characters. Using them will help you read other programmers' code if those programmers follow the same recommended conventions.
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 this naming convention requires that a variable have a two-character prefix. The first prefix character always denotes the scope and the second denotes the type. Although you could define these characters in reverse order, you should never switch the order of these characters after you start an application. If you do, the resulting confusion will make you wish that you never heard of naming conventions-and it will not make you popular with other programmers who need to read your code, if they follow the standard prefix order.
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.
Because naming conventions are optional, many variations exist. To some extent, this situation is good because experimentation sometimes leads to better methods. Many leading FoxPro developers have developed their own naming conventions over the years, some of which are better than others. At this writing, it is too early to determine whether these conventions will be abandoned for a common naming convention. Actually, global acceptance would be a surprise. Part of the problem is that the naming convention proposed for FoxPro is not completely consistent with other development languages yet. Another problem is that Visual FoxPro does not fully support cases in all the places where variables and field names appear. Finally, developers might use naming conventions for different purposes.
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.
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:
Despite the recommendation that you use uppercase and lowercase, the Visual FoxPro Table Designer supports only lowercase. To make matters more confusing, commands such as DISPLAY STRUCTURE list field names in uppercase. The Browse and Edit commands label column headings by displaying field names with initial caps only. Thus, there is no way to differentiate case in a table name. This is another reason for some developers to use the underscore character after the prefix, or even between major words. Would the field C_RAP, for example, make more sense in a table that lists types of music, or would you prefer CRAP?
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.
Even though Visual FoxPro may not honor case in field names, there is no reason not to use case when you are coding field names.
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.
You can override some of these assumptions by prefixing the variable with the table alias. If the variable is a memory variable, use m. as the prefix.
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.
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:
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:
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.
|Grid column header||grhProductFieldName|
|OLE bound control||olbEmployeePicture|
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'
The recommended convention does not prefix characters to object names. However, you can use prefixes on object references.
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.
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.
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.
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.
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:
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.
Before you can enter a UDF for a validation rule, the stored procedure must exist. Otherwise, Visual FoxPro rejects the UDF reference. Therefore, choose Edit Stored Procedure from the Database pull-down menu before adding the validation clause to the table structure. You can also open the stored procedures anytime the database is open by typing MODIFY PROCEDURE in the Command window.
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
Row or table buffering must be enabled with CURSORSETPROP() before GETFLDSTATE() can operate on local tables.
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.
|Field has not changed value and deletion-status flag has not changed.|
|Either field or deletion-status flag has changed.|
|Field in appended record has not been changed and its deletion status has not changed.|
|Field in appended record has changed or the deletion-status flag has changed.|
Visual FoxPro returns this information only for tables in a database.
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.
When you are evaluating return values from GETFLDSTATE(), all options other than -1 return a numeric value. Option -1 returns a string.
The code segment in Listing 4.2 shows the complete validation code associated with the record-validation function shown in Figure 4.2.
Listing 4.2 04CODE02.PRG-A Record Validation Function Called by the Valid Event of the Total Order Amount Field
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.
While you are in the validation-rule code, do not attempt to move the record pointer for the current table. Any change could result in a series of recursive calls that could create more error conditions. For this reason, VFP prohibits changes to any field in the current table. Therefore, you cannot use the record validation to "correct" an error.
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.
Visual FoxPro stores all validation code and text in the database. Freeing a table from a database removes the link to these definitions. The stored procedures remain in the database, but the links to the table are broken.
You can add, modify, or delete a record validation with the CHECK or DROP CHECK clause in ALTER TABLE.
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.
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:
TAG: CUSTID PRIMARY INDEX ON: cCustomerId TAG: CUSTNAME REGULAR INDEX ON: cLastName + cFirstName TAG: COMPANY REGULAR INDEX ON: cCompanyName + STR(cOutstandingBillsTotal, 9, 2)
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
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.
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
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.
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.
If you change any of the tables that are involved in referential integrity, their indexes (or persistent relations) rerun the RI Builder. This action revises the code as appropriate due to the changes made.
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.
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
These two files use the standard recommended naming convention for table variables, which includes a single-character type prefix. The convention also uses initial caps for words within the field name where Visual FoxPro recognizes them, such as within code to help make reading the names easier.
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.
Listing 4.3 04CODE03.PRG-Referential Integrity Code Generated by the RI Builder Called on by VFP in the Event of a Delete, Insert, or Modify
**************** 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.
When defining conditional expressions, define the evaluation order so as to minimize the work that Visual FoxPro must do. Put the most critical condition first. This action saves VFP time; it also enables you to include in the second part of the test conditions that are not otherwise valid.
When you are connecting two expressions with OR, put the one that is most likely to pass first. When you are connecting two expressions with AND, put the one that is most likely to fail first.
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.
Listing 4.4 04CODE04.PRG-The RISTART Code
**************** 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:
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:
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.
Listing 4.5 04CODE05.PRG-Referential Integrity Code Generated by the RI Builder Called on the Event of a Delete
**************** 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.
Listing 4.6 04CODE06.PRG-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().
Listing 4.7 04CODE07.PRG-The RIDELETE Code
***************** 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.
Listing 4.8 04CODE08.PRG-Referential Integrity Code Generated by the RI Builder Called at the End of Every Trigger
**************** 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.
Actually, there may be a reason for adding code to the insert trigger of SALES. Visual FoxPro executes this trigger not only when you add a new record to the table, but also when you recall a deleted one. When a sales order is recalled, you may want to recall its details automatically.
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().
Listing 4.9 04CODE09.PRG-Referential Integrity Called on a Modify to Modify All Related Records Automatically
**************** 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().
Listing 4.10 04CODE10.PRG-Referential Integrity Code Generated by the RI Builder Called on a Modification to Update All Related Records
****************** 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.
Listing 4.11 04CODE11.PRG-Code 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.
Listing 4.12 04CODE12.PRG-Referential Integrity Can Insert a Detail Record on the Insert of a Parent Record Automatically
****************** 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.
Listing 4.13 04CODE13.PRG-Referential Integrity Can Check for a Matching Parent Key Value on the Modification of a Child Key Value and Delete the Child if the Child's Key Value Has No Match to a Parent
****************** 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.
Listing 4.14 04CODE14.PRG-Referential Integrity Can Call a Routine to Perform the Appropriate Action After a Check
************** 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.