The most important aspect of our work as database programmers, is… data! Many developers are too quick to start building forms and reports, often before the data structures have been stabilized. I feel very strongly that if the data is not properly designed; if the integrity suffers, then nothing else matters. It doesn't matter how pretty the application is, or how fast it is, or whether our environment is object-oriented or not. If the client cannot count on the integrity of the data, then nothing else matters.
This session will focus on the Database Container aspect of data design… how to most effectively use it and the Database Rules and Triggers and how to avoid the most common pitfalls. It starts off after the basic design has been determined and properly Normalized. Several tips and tricks will be presented, as well as some important performance-enhancing techniques.
Prior to Visual FoxPro… more accurately, prior to the Database Container, many corporations looked at FoxPro and thought it was a nice product: Blazingly fast, relatively easy and flexible to work with, lots of available programmers to choose from, but one weakness brought them up short. The fact that if anyone opened the data from outside of the application, all validations and data integrity were lost. The reason was because once the developer identified what made the data valid, and how the tables related to each other and actually wrote the code to enforce that integrity, there was only one place it could be placed. And that was in the actual application. Meaning that if someone bypassed the application and got to the data directly, all guarantees of integrity were gone.
Visual FoxPro's Database Container goes a long way towards changing this. Now programmers have the ability to place data validation and integrity code where it really belongs: With the data! Code is placed in the "Stored Procedures" area of the Database Container and then is available no matter how the data is accessed, even without the application running! We now also have the ability to attach "default values" to any field, or to have validation code run on a field-by-field (or record-by-record) basis. In addition, we now have the same "Triggers" that were long the domain of higher-end back-end database products such as SQL Server, Oracle, etc. We have Update, Delete and Insert Triggers which run (or fire, hence the name "trigger") whenever someone updates, deletes or inserts a record. There is no way to turn off or bypass these triggers. So any code attached to them will automatically be run.
As far as data goes, Folks, Visual FoxPro is now in the big leagues!
This session will help you get comfortable with the various rules and triggers and show you some interesting ways to do things.
Everything that follows is based on the developer having enabled data buffering. This is a new feature that is vaguely reminiscent of using memory variables for editing data with older versions of FoxPro (and SCATTER and GATHER commands). Buffering makes our life easier but most importantly it affects the way field-level rules fire. We're going to look at field-level rules in detail in the next section, but for the moment you should be aware that without buffering enabled, you cannot even add a record unless the field-level validation passes. If it fails, you have no ability to change the value to an acceptable one. With buffering enabled, the validation only fires when we attempt to move the buffered data back to the table (with a TABLEUPDATE() function call).
There is one exception to that last sentence. If you have a newly added record, the behavior is as described above. However, if it is an existing record that is being edited, even with buffering enabled, the rule fires immediately as we try to leave the field. Not only when we leave or save the record.
This is one reason that many of us prefer to do all our editing in Updateable Views. See the section on Views for more information on this.
Just one additional note on using buffering… if like many developers, you prefer to use table buffering, be aware that you cannot then create any indexes if needed. To be able to create indexes on a table (or view for that matter) it must be either row buffered or not buffered at all. (If using table buffering when you need to create an index, you would have to switch to row buffering temporarily, create the index, and then switch back. But you have to watch out in case there are any pending uncommitted changes or if you're in the middle of a transaction. Can get complicated.)
Each field has the possibility of a default value being assigned to it. This can be very handy… You could set the default City for example, to "Amsterdam" if the majority of entries were for that city. The interesting thing about default values is that not only can you put a constant value there (character, numeric… any of VFP's data types) but you can put a variable or a function call. This allows you to place things like a nextid() function call to automatically populate a primary key field with the next key value. (Such routines are beyond the scope of this session but there have been many published over the last few years and a rather good one, if I do say so myself, is available for download from my web site.)
Row-level rules have less limitations than field-level rules but they only fire only on a tableupdate() call, or if the record pointer is moved. You can also modify any field in the current record using a row-level rule. When you apply a row-level rule you have the option of having the rule checked for all the existing data (this is the default) or only for newly added or edited records. If you turn off the checking for existing data, the rule will not fire for the fields in those records until they are next edited.
Visual FoxPro has the same three triggers today as other serious back-end database storage products such as Oracle, SQL Server, etc. Namely Update, Delete and Insert Triggers. These Triggers fire without fail whenever one of those operations takes place and there is no way of turning them off. So any code you place in those Triggers (function calls most likely) would automatically execute.
Triggers cannot be used to change values in the record that has caused them to fire, but they can be used to make changes in other tables. And of course to allow or disallow an Update, Delete or Insert operation in the first place.
Just about the only limitation of triggers is that they cannot be used to modify the record that caused the trigger to fire. Triggers can be used for many interesting things (as we'll discuss later) but most commonly they are used to enforce Referential Integrity rules.
You can have more than one function call attached to a trigger; using the AND or OR operators you determine whether they all need to return true or just one needs to. I am often asked how to apply business rules in conjunction with RI rules. Suppose you had a simple customers and invoices system where you decided that deleting a customer should cascade the deletions through the invoices table, deleting any invoices for the customer. This is easily accomplished with standard RI code (that the RI Builder can generate for you). But what if you said only customers with closed and paid invoices should be allowed to be deleted. How to accomplish this? Easy! Create another function to return true or false to tell you if the invoice is closed and paid and only allow the deletion if it is. The allpaid() function might look like this (simplified):
Now you can call it before the RI function call, using a logical AND to join both function calls like so:
The cascading delete to invoices will not execute unless the allpaid() function returns true first.
Another interesting use of triggers is to create an audit trail of all changes to the data. This is easily accomplished by adding the call to your audit function after the call to any RI-enforcing function using the AND operator. (You probably don't want your audit code to run unless the update, delete or insert operation actually succeeds.) For example if your audit trail routine was called logit(), and you were using code generated by VFP's RI Builder, the following is how you could attach it:
An excellent commercial audit trail utility is called "FoxAudit" by TakeNote Computer Consulting and information is available at www.takenote.com. Stored Procedures
There is only one place where procedure code can be placed with the data - that is in the Stored Procedures area of the DBC. Of course you could place such code in the application (APP or EXE) but this would mean the data is not self-sufficient. This may or may not be a good idea, depending on what kinds of access to the data will be taking place… only from within your application or from other ODBC-compliant applications.
One note in passing, in case you plan on accessing VFP data via ODBC drivers from another application and you have Stored Procedures: Not all of VFP's commands are supported by the ODBC driver (supporting everything would require that the driver knew all about VFP and the size would be enormous). So if you limit yourself to only those commands the ODBC driver understands, you will have much greater success. There is a help file included with VFP that lists supported commands, and it's a great thing to check before you get in trouble. The file is called DRVVFP.HLP and on my system (new clean install) I have two copies of this file, in different locations and of slightly different sizes. Both have the same dates however. So, my advice is to check both and then test.
VFP'S RI Builder has a great thing going for it… it's free! Yes, it's included with Visual FoxPro and does a remarkably good job. You can set up RI rules for Update, Delete and Insert operations and can choose from Ignore (which is worthless in most situations), Cascade (which cascades the changes down through the child table) and Restrict (which prohibits the change if the parent record has any child records). However it is not without problems.
The first problem occurs if you have a parent, child and grandchild RI scenario. Let's say you have a Cascade rule on Delete for the parent and a Restrict rule on the child. VFP will delete the parent but not delete the child or grandchild, which of course is wrong. In that scenario it should not delete anything. An unusual circumstance to be sure, but one you should be aware of.
The second problem only occurs with large (many persistent relations) systems. It is possible that the code generated by the RI Builder will exceed VFP's 64k limit for compiled object code. This is because the RI Builder does not write generic code, but instead creates individual procedures for every persistent relationship with RI. A little bit of thought shows that having a rule on Delete is really the same regardless of which tables are being used… the only things that change are the names of the parent and child tables, the primary and foreign keys and whether we want Cascade or Restrict behavior.
In addition to these problems, if you are not using surrogate keys for your tables and have a compound key with some kind of expression, the generated RI code really does not like it. I won't go into the specifics, but the conventional wisdom of using surrogate keys will save you lots of frustration here.
(Also don't ever run Beautify on the Stored Procedure code if you have used VFP's RI Builder, as it will cause your code to break. There are numerous lines where inline comments have been used after closing structures like ENDIF, ENDDO, etc. without the double ampersand && characters. Beautify doesn't understand this and will really mess up those lines and break the code. Note that this is finally fixed with VFP 7.0, but since most of you are not yet using that version, this point is worth mentioning.)
All the problems mentioned above can be solved by the use of 3rd party RI routines. Several choices come to mind: I use a framework called Visual MaxFrame Professional, and it supplies its own RI features which work very well. Information about VMP is available at www.visualmaxframe.com. XCase, (www.xcase.com) which many developers use to model data structures for their applications also has the ability to generate excellent RI code. Finally, the least expensive solution is to use RI code that Steve Sawyer developed. I don't believe it is available for download, but it is included in the book that Steve co-authored with Jim Booth called "Effective Techniques for Application Development with Visual FoxPro 6.0" The publisher is Hentzenwerke Publishing (www.hentzenwerke.com) and the ISBN number is 0-96550-937-0. And just because this is the least expensive solution does not make it any less worthwhile. Steve has done an excellent job with that code. Very nice indeed.
This session is not meant to teach you all about Views, but we will mostly talk about them in terms of the database Rules and Triggers we are focusing on. Using the View Designer you are able to add field-level rules, field-level message text and default values. It might appear you are unable to have row-level rules. Happily that is not correct! You are able to apply row-level rules to a view, but you must do so by hand, using something like:
This installs a row-level rule (function call) called allvalid() on the lv_cust view.
If you use views (local or remote) in your system and you have a lot of users, pay particular attention to the tips in the performance section of these notes.
One requirement which comes up from time to time is that of preventing unauthorized changes to data, especially from outside of your VFP application. Perhaps a user has a full copy of VFP and you want to keep the user from inadvertently making changes. Prior to the DBC this was a very tough issue to solve. Now it's very easy. (Even easier with 7.0, thanks to the new Database Events. See below.)
As the simplest solution, all that is needed is for you to put code into the triggers of each table which returns False if the application is not running. How to tell if the application is running? Just create a variable, and check for its existence in the trigger code. If it exists, the application is running, otherwise it's not. (You could even check to see if your application object has been instantiated if you prefer not creating an unnecessary variable.) Something like this could be used:
Your trigger code might then look like this:
VFP 7.0 give us another way this could be done: By using the new Database Events. See further in these notes for more details about Database Events.
In team development it is generally better to make changes to the program which creates the DBC than to the DBC directly, and then migrate that program to each developer. (This is also easier to manage with a source control program such as Visual SourceSafe.)
If you do not know about (and use!) GENDBC, you really should. Think of GENDBC as an insurance policy for your database design, including everything that goes into it (tables, indexes, relations, rules, triggers, etc.). Just as you must pay your insurance premiums if you want your insurance policy to be in effect, you must run GENDBC yourself from time to time. Running it creates program code which can recreate everything for you in the event of a catastrophe. It won't put the data back in, but it sure will recreate everything for you. I have used this to set up a clean system for testing and I have also used it to ship a new application to a client with no DBC or tables at all. But during setup the code GENDBC generated for me was run, and everything was cleanly created at the client site. GENDBC.PRG is installed for you with VFP in a subdirectory somewhere beneath the VFP install directory. I always make it a habit to find GENDBC after a new VFP installation and move it to VFP's home directory so that I can call it more easily. Simply open your DBC (make sure tables and views are not open) and say:
There is actually a GenDBCX floating around which was quite cool and desirable, but VFP 6.0 has improved what GENDBC does so that in my opinion, GenDBCX is no longer necessary. But if you want to take a look at it, it's written by Steve Arnott and can be found at: www.stevearnott.com/advscripts/GenDBCX.asp.
If you use many Views in your DBC (local or remote) there can be a real performance bottleneck with enough users banging away at the system: It seems that whenever you open a View, the DBC that it belongs to is locked briefly and simply opening views can bring the system to a crawl. Not only that, but if there is a conflict of two users trying to open the same view at the same instant, one will get an error message. For this reason some developers who have run into the problem now use an error trap to catch error 1709 "Database object is being used by someone else" and then reissue the USE command for the view in a loop.
Another (better in my opinion) solution is one first found by Steven Black and Andy Kramek and the group they were working with on a large project recently. What they did to improve performance dramatically and eliminate the chance of getting that 1709 error was that whenever someone logged onto the application, a local DBC was created on their machine containing only the view definitions. The tables were still in the main DBC on the server, but since each user effectively had his/her own local copy for the views, there was never a contention or performance problem. Creating those local DBCs is an extremely fast operation, as only the DBC and views are brought down, not the data. A couple of steps are required for this approach. First create a local DBC with code along these lines:
Doing this will result in a table - not a DBC (in spite of the extension). The next step is necessary with VFP 7.0 (and 6.0, both of which are stricter than 5.0 was):
Of course you need to design your data-aware form classes with this technique in mind so that they happily use the local DBC and the views in it. The reports I have heard were that performance was increased so drastically that the application went from just about being rejected in the prototype stage to thrilled acceptance. It is also my understanding that no one (not even the VFP team at Microsoft) was aware of this issue before Steve and Andy encountered it.
In versions of VFP prior to 7.0, if we suspected corruption in our Database Container, we issued the command:
(Optionally using the RECOVER keyword). This worked, but the problem was that you needed to be in the development environment of VFP when issuing the command. It did not work in a runtime environment. Now with VFP 7.0, this command works at runtime as well. Exclusive use of the database is required.
This command really only does an audit of the database, making sure that the proper locations of tables and indexes are specified, that tables in the database contain the proper fields, and that index tags in the database exist. With the RECOVER keyword, you can then locate tables or indexes which have been moved elsewhere.
A new and exciting feature of the Database Container in VFP 7.0 is that we now can attach code to events associated with the DBC. One neat thing that can be done with this is to disallow data access when inappropriate, such as when you want the app to be running. (See Natalie Mengel's D-DATA session for more information about database events.)
Since I am using a beta version of VFP 7.0 while writing this session, it is possible that changes and/or improvements will be made to this session. (Especially as the documentation becomes more complete.) When that happens, I will be posting updates on my web site.
© 2000 by ELGEM Associates - All Rights Reserved. Eldor Gemst of ELGEM Associates is based in Montreal Canada, and provides training, consulting services and custom programming around the world. He specializes in Visual FoxPro and SQL Server. You can reach Eldor by email at eldor@elgem.com or on his web site at www.elgem.com.