Indices
Perhaps most readers already know indices; anyway, I find it convenient to include a brief summary of the topic, since this is a requisite to understand the remainder of the topics.
Indices (created with the INDEX command) serve several purposes: to sort data, to find information quickly, to avoid duplicate data, and for referential integrity (explained later). Normally, you create an index based on a field or on a combination of fields. (You can also create indices on any expression, which may, or may not, involve fields.)
To ensure there are no duplicates, you define the index as primary, or as candidate. (A regular index is one that does allow duplicates. In actual practice, most indices are of the regular type. There is another kind of index, "unique", which I don't recommend. Visual FoxPro maintains it for backward compatibility.)
You may have a field that should have no duplicates (example: order number), or a combination of fields, that should have no duplicates (example, for telephone numbers: area code, combined with the telephone number). To create an index on multiple fields, normally you would convert everything to text (character), and concatenate (combine with the "+" operator). To convert an expression to text, you can use the following conversion functions:
Numeric: Although you can use the well-known str() function, in Visual FoxPro 5 and later it is usually convenient to use bintoc(), to save space. For instance, a field of type Integer uses 10 bytes if you convert it to character with str(), and 4 bytes if you convert it with bintoc().
Date: The appropriate function for indices is dtos(MyDate) (equivalent to dtoc(MyDate, 1)). This function returns a text in the format yyyymmdd. This has two important advantages over the function dtoc() (used without the second parameter): 1) the format is independent of options like SET DATE and SET CENTURY, and 2) dates are sorted correctly.
DateTime: The function ttoc(MyDateTime, 1) returns a text which is non-ambiguous, and adequate for sortings.
Logical: Usually an expression like iif(MyLogicalValue, "1", "0") is used.
I want to remind the reader that in Visual FoxPro, any index expression should be of constant width. Therefore, you should not use functions like alltrim(), whose result is of variable size. If you use variable-sized expressions, Visual FoxPro can have some unexpected behaviors. Sometimes you have to use padr() in indices, to ensure a constant size.
Primary keys and foreign keys
Normally, each table as a primary key - a field, or a combination of fields, that uniquely identifies each record. For this purpose, many programmers use fields that are not visible to the end-user, and that are generated automatically; for instance, "auto-incremented" fields, or GUIDs. Visual FoxPro doesn't have auto-incremented fields, but it is relatively easy to obtain the same result invoking a function from the default value of the field you want to auto-increment (the sequence is stored in a table). On the other hand, there are also many programmers who prefer to use, for their primary keys, a field, or a combination of fields, that is visible to the end-user - for instance, for clients, a client number assigned by the company, or the number of some legal document. (In my previous article, in this magazine, I compared the advantages and disadvantages of both approaches, and included functions to generate auto-incremented fields and GUIDs.)
The values of the primary key appear in other tables, to identify the corresponding records in the main table. In the other tables, this is called foreign key. For instance, the client code is the primary key in the client table. The same code appears in the invoices table, where it is a "foreign key", and identifies the corresponding client (the remaining client information can be obtained from the client table, and is usually not stored in the invoices table). In the other tables, obviously, the value can repeat (there can be several invoices for the same client). client there can be many invoices. Another term used is "parent-child" relation - in our example, client is the parent, and invoice is the child.
Referential integrity
The term referential integrity refers to certain rules that must be fulfilled, and which involve more than one table. For instance, you can't erase a client while there are invoices for that client. If the system would permit this, the invoices would remain with "orphaned" data - codes of clients who no longer exist on the system.
After creating the tables and indices, in the database designer you drag, with the mouse, the index that corresponds to the primary key, to the index that corresponds to the foreign key in the other table.
To complete the referential integrity definition, in the "Database" menu you give the command "Edit Referential Integrity", and define the restrictions. Here, for each relation between two tables, you place rules for three cases: Update (the user tries to change the primary key value in the parent table), Delete (the user tries to delete a record from the parent table), and Insert (the user tries to add a record to the child table, or to change its foreign key value).
The most common case is to put "R" (restrict) in the three columns, with the following important exceptions: a) If you want cascading deletes, you put the corresponding option, "Cascade", in the second column. For instance, when the user deletes an invoice from the invoice header table, the corresponding records from the invoice detail table would automatically also be deleted. b) If the field in the second table is optional, you leave it at "I" (ignore). For instance, in the client - invoice relationship, perhaps you don't want to insist that each invoice has a client. c) If you use primary keys that are visible to the end-user, you can also use cascading updates in some cases (if you use primary keys which are not visible to the end-user, there is no requirement to change the primary key values).
Instead of using the referential integrity builder that comes with Visual FoxPro, you can use other builders. I recommend to install TaxRI, written by Walter Meester. This program offers some interesting additional options. One option that I find very interesting is "Allow blanks", for the "Insert" case. The idea is that you can leave a field blank, but if you write a value, the referential integrity functions do the verification in the other table, to see whether it is a valid value. Another interesting feature is the possibility to disable referential integrity temporarily - very useful when updating a database structure (using APPEND FROM). Another interesting detail is the fact that the code generated by TaxRI takes approximately 10 times less lines of code than the one generated by the native Visual FoxPro builder - and is much more readable (useful if you ever want to debug it). TaxRI can be obtained from the download section of the Universal Thread.
Triggers
To enforce referential integrity, Visual FoxPro uses triggers. A trigger is a rule that has to be fulfilled before a change can be done. There are three types of triggers in Visual FoxPro: for adding a new record, for modifying an existing record, or for deleting a record. After setting the referential integrity options, you can see, in the definition of the tables involved (with the MODIFY STRUCTURE command) that the triggers invoke certain functions. The functions themselves are stored in the database stored procedures.
It should be noted that if you use buffering (as you should, usually), the trigger function is only invoked when you try to save changes with TableUpdate(). Thus, you can do several changes to a record, then revert changes with TableRevert(), and the trigger never fires. (TableUpdate() is invoked implicitly in some cases.)
The programmer can create additional triggers, that are executed, for instance, when the user tries to modify a record. The trigger can return true or false (meaning that the requested action is permissible, or not), and, for instance, avoid deleting records under certain circumstances. But the programmer can also use triggers to do changes to other tables.
Examples of additional triggers:
1) Enforce a rule that doesn't allow the user to modify an invoice which has already been declared to the tax office. You can have a logical field, "declared"; the Update and Delete triggers would simply include "... and not declared". It is a little more complicated to avoid changes in a separate invoice detail table - you would have to do a lookup in the invoice table to see whether the invoice has the field declared = .T. In both cases the trigger would return .F. to avoid the change or deletion. changes, and when. The list of changes if stored in a special audit table. For example, the Visual Extend framework maintains a variable for the current user. In case the variable doesn't exist (changes are done through the command window), the corresponding audit functions obtain the user name from the Windows login.
In each table, a function is added to the three triggers.
3) Data update: when updating a sale, update the product stock, for the articles sold.
To achieve this result, in the table "sales detail" (the table that contains the articles sold), the same function is place in the three triggers: UpdateStock("I"), UpdateStock("U"), and UpdateStock("D"). The parameter tells the function what type of trigger called it (Insert, Update or Delete). If Visual FoxPro already placed trigger functions in the table definition (for referential integrity), you should use a syntax similar to the following, to ensure that all trigger functions are invoked: __ri_insert_sales_detail() and UpdateStock("I").
To make the trigger function as complete as possible, it should not only decrease the stock when a record with an article is created; it should also consider the case that the user erases or modifies a record. Finally, let's suppose it is also permissible for the user to change the article.
For this purpose, in the case of modifying a record, the update function (which is also stored in stored procedures) should obtain the current value of the article and of the amount sold, search for the article in the article table, and decrease the stock. It should also obtain the old values of the fields article and amount sold, using the function oldval(), and return it to the warehouse (increase the stock). When deleting a record, it should only return to the warehouse; when adding a new record, it should only take from the warehouse.
The advantage of using triggers to save changes, instead of placing the same commands into a form, is that the update will be done no matter where the change is done - this can be from more than one form, or interactively, from the command window, a BROWSE window, etc.
Now I will present a version of the update function I outlined. The function opens the article table, but doesn't close it again. This makes updates much faster.
********************************************************************** FUNCTION UpdateStock(tcTriggerType) if tcTriggerType $ "IU" && decrease new quantity from stock UpdateStock2(Article, -Quantity) endif if tcTriggerType $ "UD" && return old quantity to stock UpdateStock2(oldval("Article"), oldval("Quantity")) endif ENDFUNC ***** FUNCTION UpdateStock2(txArticle, tnQuantity) local lnSelect lnSelect = select() && Save work area if not used("Article_Update_Stock") use Article; order Article; again in 0; alias Article_Update_Stock endif select Article_Update_Stock seek txArticle replace CurrentStock with CurrentStock + tnQuantity select (lnSelect) && Go back to the current work area ENDFUNC
Problems with triggers and referential integrity
In practice, I found the following potential problems:
1) In BROWSE mode, as soon as we add a record with Ctrl-Y, the empty record is validated. If we have triggers, we will probably receive an error message (trigger failed). The solution is to use the command "View | Append mode". With this command, the record update (and validation) is postponed until we go to the next record.
2) Referential integrity in Visual FoxPro has a flaw in re-opening tables for referential integrity - it can wrongly detect that the table is already open, when really a different table is open. I had this problem in the days of VFP 3, with the following three tables: article, supplier, articlesupplier. articlesupplier had a list of suppliers for each article. The flaw appears here because "articlesupplier" ends with "supplier" - and to open "supplier", VFP wrongly detects that it is already open - when actually, articlesupplier is open. If I remember correctly, the problem is in the line lnInUseSpot=atc(tcTable+"*",pcRIcursors. By giving my tables names like ArticleMain, SupplierMain y ArticleSupplier, I have avoided this problem (since now, the name of no table appears at the end of another table). Walter Meester wrote that the current version of TaxRI has the same problem.
3) Data updates (with APPEND FROM) can be quite slow, since each record is validated. Solution: if you are sure your data is valid, deactivate referential integrity temporarily. This is easy if you use TaxRI.
4) The error message "trigger failed" can be difficult to understand for the end-user. Even if you tell them that they can't delete a record when there is data in another table, the question arises: which table doesn't allow me to delete? The solution is to intercept (validate) most errors before TableUpdate() - and showing the end-user clearer error messages. Then, if we do all this programming anyway, we don't really need referential integrity, right? I would say, we do need it. Referential integrity is easy to define, and it is a powerful protection, in case we forget validation in some form. And let's not forget that referential integrity also works in interactive mode, for instance, when modifying data with the BROWSE command.
Summary
I want to finish with a summary of the most important points:
