The article below is a review published in the October 1996 issue of FoxTalk. Copyright information follows the article.

by Kelly G. Conway

With the release of Visual FoxPro, Microsoft added a data dictionary to our favorite development tool. Or did they? I suppose that depends upon your favorite definition of a data dictionary. Users of the Stonefield Data Dictionary for FoxPro 2.x would beg to differ. The Stonefield Database Toolkit fills a number of gaps in the VFP data dictionary.

SURE, the database container (DBC) is a welcome addition to Fox. It provides a foundation that is extensible so that we, Microsoft, or third-party developers can add other features to it. But the DBC leaves out several features that many of us would consider essential parts of a data dictionary -- especially those of us who use Stonefield's 2.x tools.

But we need not do without, because Stonefield has released a new data dictionary tool for VFP. The Stonefield Database Toolkit expands on Microsoft's DBC to provide the features that a data dictionary should have -- features that Stonefield's customers have taken for granted for the last few years. SDT adds to the DBC features such as database documentation, additional properties (such as field input masks), and the information needed to recreate table structures and index tags. SDT also provides several methods that your applications can call to use this information at runtime.

Getting started

Starting SDT is simple enough. The product comes with an .APP file that you can run from the command window or from a startup program such as VFPSTART.PRG. Either way, you'll see a splash screen and then notice that two options have been added to the VFP Tools menu pad. Those options allow you to start SDT and to change SDT settings to suit your preferences.

But, before you start up SDT, you'll need to create a VFP database. You might find this a bit annoying -- why wouldn't SDT go ahead and provide an option to do something as simple as creating the DBC? The answer is that Stonefield didn't try to reinvent anything that they felt VFP already handled well. So users of SDT will still find themselves using the native VFP tools to do some database management functions. Although I think this is a nice design from the standpoint of allowing me to continue to use VFP for things that it allows me to do, it would be nice if SDT contained some push buttons that allowed me to quickly call up those VFP tools (for example, MODIFY DATABASE, to see a graphical view of the DBC) without exiting SDT.

You start SDT by choosing the Tools/Stonefield Database Toolkit menu option from the VFP menu. You then see a form that lists all of the tables in the current DBC. The initial SDT form also contains several command buttons and a few fields for entering information, such as a caption and comments for each table.

A quick tour

A quick tour of the command buttons should give you an idea of the functionality they offer. The tool tips note that the command buttons allow you to add an existing table, create a new table, remove a table, copy a table, modify a table (structure), browse a table, reindex a table, update a table's structure, edit stored procedures, pack the database, refresh metadata, produce documentation, and set SDT preferences. Most of these descriptions are self-explanatory. But context-sensitive Windows help is available (along with printed documentation) to explain all of the features.

The controls that appear on the right side of the form allow you to change settings (properties) for each individual table. By clicking on a table in the list to the left, you can see and modify its properties on the right. The properties that you see are only those that Stonefield has predefined. One of SDT's most powerful features is the ability to create extended table properties. You'll find a command button for that purpose at the right of the screen.

These properties have no effect on VFP's use of the tables (for example, removing the mark from the Reportable check box for a table doesn't keep you from being able to define VFP reports for that table). But your application can read and set these properties at runtime and change its behavior accordingly. When you consider the fact that these extended properties can also be added to fields and index tags, the possibilities for extending the DBC boggle the mind.

For example, your application's reporting option might let the user create a quick report by selecting from a list of reportable tables. Or you might add a security-level extended property to all fields that your application could use to remove fields from forms based on the current user's security level. SDT also comes with some methods -- such as OpenAllTables() and SelectTag() -- that your application can call and that use the predefined properties to drive their appearance.

Most of SDT's functionality can be found by clicking the Modify Table button. This button opens a tabbed dialog that contains a list of the selected table's fields on the left and a plethora of properties on the right side of the first three tabs. The fourth tab contains a list of index tabs and their properties. The final tab contains table-level validation and trigger properties.

The controls on the field-level page frames of this form allow you to specify properties for each field such as the caption, comments, input mask, output mask, report headings, tool tip, and help text. You also can create extended field properties the same way I described for tables earlier. New fields can be added to the table and existing fields can be copied or deleted.

The page frame for indexes contains a list of the index tags defined in the table's structural index file (.CDX). Index properties include the ones that are accessible through VFP's MODIFY STRUCTURE command (Name, Type, Expression, Filter, and Descending), as well as Caption, Comments, and Selectable. The Selectable property is used by the built-in SelectTag() method so that the user can select a tag only from those that the developer has marked as Selectable. Of course, this page frame also contains a command button that brings up the extended property editor for index tags.

A very useful feature on the index tag page frame is a common button that calls the index wizard. This wizard can be used to create a tag on the DELETED() function, create a tag on every field, and create all character-field tags by including the UPPER() function. For most tables, it's easiest to run the index wizard with all of the options selected and then use the Remove Index command button to delete the tags you don't want.

If all of these features seem overwhelming, the SDT documentation contains a tutorial that walks you through their use. There's also a SAMPLE subdirectory that contains samples of using SDT extended properties and methods. Another aspect of SDT that I haven't touched on yet, is the library of reusable functions and methods that Stonefield provides. These include methods to open a table, open all tables, recreate index tags, repair corrupted table headers, get and set extended properties, and update table structures. I'll look at a few of these later.

Another feature of SDT that I also haven't discussed may be the best feature of the entire package, a builder named AutoSize. In short, AutoSize is a builder that uses field-level properties to help you create data-bound forms in an instant. I'll demonstrate AutoSize later.

OK, now what do I do with all of this?

So, now that you have all of this functionality, how can you use it? I'll walk you through the creation of a very small application and describe how I used the SDT tools to enhance and accelerate the development process.

This simple example application uses the VFP sample DBC and tables contained in the SAMPLES\DATA subdirectory. It contains a screen for maintaining customers and another for maintaining employees. It also contains utility options for rebuilding index tags and updating table structures. I won't use every SDT feature, but I hope you can get an idea of the kinds of things you can do with SDT.

Preliminary tasks

First, I create a new directory structure for my application. The top level, called \TESTSDT, contains a subdirectory for DATA. Then, I copy all of the files from the SAMPLE\DATA subdirectory of my VFP HOME() directory into \TESTSDT\DATA. To keep things simple, I'll create all of the application objects in the \TESTSDT directory.

Then, I create a simple menu to run the application's options. To keep things really simple, I put all of the options on one menu pad, named TestSDT, and select the option to append that menu to the VFP menu. The options for the menu (and the corresponding commands) are Customer Form (do form customer), Employee Form (do form employee), Rebuild Index Tags... (do RebuildIndexTags in MAIN), and Unload (release pad TestSDT of _msysmenu). I save my menu as MAIN in the OTHER subdirectory.

So that I can test these things while I build them, I'm going to create a program that puts up the menu, instantiates SDT (through DBCx -- we'll discuss DBCx more later), and opens the tables I need. Here's the code for MAIN.PRG:

In this main program, I instantiate the DBCx manager and make sure that it's alive and well. Then, I open the database and, if that's successful, also open all of the tables that I marked to be opened "automatically." After adding the Test SDT menu pad to the VFP menu, MAIN.PRG returns and leaves me to develop and test the application. The two procedures at the bottom of MAIN.PRG are there to wrap calls to SDT's Reindex() and Update() methods with code that opens the database for exclusive use, performs the required functionality, and then reopens the database for shared use.

Extending the DBC

After saving MAIN.PRG, I go back to the command window to OPEN DATABASE DATA\TESTDATA EXCLUSIVE, and MODIFY DATABASE. I'm going to set the field captions with VFP's table designer so that SDT will use those captions to set acceptable default values for several of its extended field properties. I right-click on the Customer table and select Modify... from the context menu. After setting the caption of each field in the customer table, I do the same thing for Employee.

Then, I use SDT to extend TESTDATA.DBC and its associated tables. I'll DO SDT.APP to ensure that the SDT options appear at the bottom of my VFP tools menu. Then I choose the Stonefield Database Toolkit... option from the Tools menu. Because this is the first time I've used SDT on this DBC, I'm prompted to make sure I want to create SDT extensions. I select "yes," and SDT spends a few seconds building the data dictionary tables.

Setting the table, field, and index properties

Now I want to modify each table to see that the field properties are properly set and to make sure I have all the index tags I want (and that they have descriptive captions, too). I select the customer table and click the Modify Table button. After a few seconds, the extended table designer form appears with the customer table information. By clicking on various field names and moving among the first three page frames, I notice that SDT did a nice job of filling several properties with the captions I just entered into the DBC. Some of these properties will be used immediately for things like tool tips and status bar messages. Others (dialog prompts and list prompts, for example) won't be used until I (or someone else) build some tools or controls that access them.

On the Index tab I notice that some index tabs already exist (Microsoft created them and the sample data that these tables contain). But there's no index tag on DELETED(), and none of the character index tags use the UPPER() function to aid in sorting and incremental searching. I decide to remove all the existing index tags (except for the CUST_ID primary tag) and use the SDT index wizard to create my tags. With three clicks the offending tags are gone, and with three more clicks I've created several new tags. I remove two or three of these new tags and leave the rest. I check the captions and notice that SDT carried over the corresponding field captions so that I don't need to update them. I also notice that each tag is marked as being selectable (by the user as the current table order), except the tag on DELETED(). That's just how I want it.

I go through the same steps for the employee table. As with the customer table, SDT takes the time to create my new index tags when I click the Save button (the disk icon). After setting the tables up the way I want them, I might want to generate some SDT reports to use as system documentation. Then I'm ready to create my first form.

Forms meet the AutoSize builder

I'll create some very simple forms, but they'll include the following features that use SDT properties and methods. Every data-bound control automatically will be sized and named appropriately for its data source. Each control also will be able to enforce an input mask as well as provide appropriate clues via status bar messages and tool tips. A button on each form will allow users to select, from a list of index tag descriptions, the order in which they wish to view the data.

Much of this functionality will be accomplished by using the AutoSize builder. Without going into the details of using a VFP builder, I'll just mention that SDT includes a program that registers the AutoSize builder with VFP for you. This makes using SDT a snap. Just to be sure to DO REGAUTO one time after you install SDT.

To create my customer form, I issue CREATE FORM CUSTOMER in the command window. I then open the data environment and add the customer table. I move the DE window so that I can see both the customer table and the customer form. Then, I click on the CUST_ID field and drag it onto my form. I right-click on the new control in the form and choose Builder... from the context menu. VFP presents me with a list of builders that are registered for the text box control. From that list, I choose AutoSize and see that my text box control has been sized correctly for the CUST_ID field and named txtCust_ID. A label with the caption Customer ID has been created to the left of the control.

But the common advice is to use your own subclass of the VFP base classes, right? In other words, I want to create a subclass of the VFP text box control (and the others) and drop that subclass onto my form. AutoSize left the control as a standard VFP text box, so maybe it isn't so cool after all. A quick check in the documentation (Chapter 13) makes everything cool again. AutoSize will let me create controls that are based on any class I want. I just have to do a little setup work first.

Running AUTOSIZE that first time created a table, AUTOSIZE.DBF, in my VFP home directory. BROWSEing that table, I see a dozen records -- one for each VFP-supported field type. Each record provides fields where I can enter a class (and its class library and location) that I want AUTOSIZE to use for controls that bound to that type of data. For example, I'll complete the character-type (C) record so that it uses the cTextBox control of my CBASE.VCX class library. If I had a custom class for entering dates, I could enter the information for that class in the data-type (D) record and automatically base all dates on that class. Very nice.

So, now that I have AUTOSIZE.DBF set up to use my classes, I'll try this again. I go back to my customer form and remove the controls I added earlier. With the DE window open, I again drag the CUST_ID field onto the form. I then right-click on the text box control, choose Builder..., then AutoSize. Things look pretty much the same as the first time. But on closer inspection I see that the new controls are based on the classes I specified (cTextBox for the text box control and cLabel for the label).

Looking back at the third tab on the extended table designer form, I now understand what the control labels named AutoSize Options: are for. If I leave them blank for a field, that field will use the default settings for the type of its bound data source when I run the AutoSize builder. But I can modify the settings for specific fields so that those fields override the defaults and use a specific class (for example, a cPhoneTextField that contains the InputMask setting and any other properties and methods I would want for a phone number field).

Now I can drag each field from the customer table onto the form and run the AutoSize builder on them. But there's a faster way. If I grab the icon that appears next to the word "Fields" in the customer table icon, I find that I can drag all of the fields from the customer table onto my form. Of course, since they're cascaded and overlapped (why, Microsoft, why?), I need to use the layout toolbar to line them up in a more useful manner. But then, when I run the AutoSize builder (with all of the fields still selected), all of the fields are processed at once.

"But that isn't all," he said, "no, that isn't all." If I turn on Builder Lock (the icon in the Form Controls toolbar that looks like a magic wand, not the one that looks like a lock), AutoSize processed each field as I drag it onto the form (or, if I drag and drop the Fields icon, all fields at once). If that aspect of VFP's builder interface doesn't impress you, nothing will.

Running the form, I see that AutoSize did a pretty good job of sizing the fields for me (and everything else). But there's only one minor problem. Fields bound to data that have a width of less than about three aren't wide enough. This is a minor annoyance because I can simply enlarge these controls after running AutoSize.

Setting control properties at runtime

I also notice that no tool tips or status bar messages appear for the controls. That's because, even though SDT allowed me to enter this information (actually, SDT entered default information for me from the captions that we entered), I still have to tell my controls to do something with the information.

Back in the form designer, I look at the properties of my txtCust_ID control. I see that there are properties for ToolTipText and for StatusBarText, both of which are empty. I probably could place a function call in those properties to return the appropriate character strings for these properties (or I could get the values from an SDT report and enter them in design mode). But I can do something much more reusable than that. I can enhance my data-bound control classes so that they dynamically fill these properties when they're instantiated.

I'll place the code to perform this functionality in the Init() method of each control class. But first, I'll save some time in testing by simply adding the code to one of the controls on my form. I open the Init() method for my txtCust_ID control and add the following code:

What on earth does all that mean? Don't worry. I'm no DBCx expert, but I picked up enough from SDT's documentation that I was able to write and debug this code in about 15 minutes. And I only had to do it once to gain this functionality for all of the controls I drop on a form. In a nutshell, we're using the control's data source to access the properties that we set for that field in SDT. The only really tricky part is figuring out the name of the property that we want to access. For example, the name of the tool tip property turns out to be CBmToolTip because it's part of the Codebook DBCx extension and Flash named it mToolTip. But that information is provided in nice tables in Appendix A of the SDT manual, so isn't that big of a deal.

I save my form and run it again and the status bar message shows up for the Cust_ID field (Enter Customer ID). Pausing the mouse over the text box control shows a similar tool tip (you probably wouldn't want to turn that on all the time, would you?). If your tool tips don't show up, make sure you set the ShowTips property of the form to .T. In fact, you could include a button on your form or toolbar so the user can toggle the form's ShowTips property and that would be all that's needed to turn tool tips on or off for all controls on the form.

Now all that's left is to cut the Init() code from txtCust_ID and paste it into the Init() method of my cTextBox class (and any other classes that need this functionality). Now all of the controls on my form display a helpful message in the status bar when they receive focus and each also displays helpful information in the form of a tool tip when I pause the mouse cursor over them.

OK, it isn't all that helpful yet because I'm just echoing the text that's in the control label. But I could modify the default settings of the Tool Tip and Message properties in the SDT extended table designer to provide more information to the user without cluttering the form with lengthy labels. Since the code I placed in the cTextBox.Init() method is executed at runtime, I can change these properties any time and see the new messages the next time I run the form.

May I please take your order?

Remember that I said that these forms were going to contain a command button that allows the users to select the order in which they wish to view the data? I'll do that now. All I have to do is drop a command button onto my form, set its caption to Set Order, and place the following command into that button's Click() method:

That's it. Clicking that command button in my running customer form brings up a dialog box that allows me to select an order from the customer table index tags that I marked as selectable (set the Selectable property to .T.). Back in design mode, I click on the cmdSetOrder control, choose Save as Class... from the File menu, and create a cmdSetOrder class in my CCONTROL.VCX class library.

Just to make sure this was no fluke, I CREATE FORM EMPLOYEE and follow the same steps that I took to create the customer form. I create a working employee form with all of the features of the customer form in less than one minute. This includes opening the CCONTROL library and dropping an instance of the cmdSetOrder class onto the form.

Other uses for SDT properties and methods

SDT properties and methods don't have to be used just from forms and classes. Reports can get in on the action, too. One sample that comes with SDT demonstrates this well. The customer report in the SAMPLES subdirectory includes fields in the page header that make calls to DBCxGetProp() to get the heading for each column from the data dictionary. Other than where the code is placed, the code to accomplish this is basically the same as the code I wrote to populate the tool tip and message properties of my text box control.

The simple application I built also contains examples of using SDT's database utility methods. The Rebuild Index Tags... and Update Table Structures... menu options (and the procedures that they call in Main.PRG) demonstrate how simple it is to use these features. With a couple of menu options and a few lines of code you can include these necessary features in any application that uses SDT as its data dictionary.

What is DBCx?

Now that I've talked about it so much, I feel compelled to elaborate a little on what DBCx is. A group of third-party developers (including Flash Creative Management, Neon Software, MicroMega Systems, and Stonefield Systems Group) collaborated to create a data dictionary scheme for Visual FoxPro. SDT's manager class is subclassed from a DBCx class. These classes, together with the Codebook manager class, cooperate to provide SDT users with the tools that allow us to do the types of things I've demonstrated.

By using DBCx, SDT has become a "Codebook compatible" product. That means that SDT can be used together with Codebook or other Codebook-compatible products without having to maintain separate data dictionaries. Additional products (such as Foxfire! from MicroMega Systems and Visual FoxExpress from Neon Software) are expected to use DBCx in the future. When that happens, developers should be able to use those products in an application while maintaining only one data dictionary.

The idea of being able to mix and match these third-party tools is a dream come true for many developers. In the past, FoxPro developers have had to maintain separate data dictionaries for each add-on product or write and run conversion programs to keep both dictionaries updated. Of course, few things in software development work out as easily as originally promised. I'll wait (optimistically) until other Codebook-compatible products are released before I declare DBCx to be the great add-on product integrator that it appears to be.

In fact, if you don't plan to utilize multiple Codebook-compatible add-on products, you may find that DBCx adds unwanted complexity to SDT. Modifying SDT (not that you'd want to or need to, but the source code is provided) and hunting down problems when an SDT error is generated are both complicated by the fact that SDT subclasses DBCx classes and uses them to read and write data-dictionary information.

Room for improvement

SDT is a great tool. But all tools have room for improvement, especially in their first version. Here're some items I would like to see added to or improved in SDT. With Stonefield's reputation for responding to customer feedback, don't be surprised if some of these features aren't already in the product by the time you pick it up.

First, a few features that are in Stonefield Data Dictionary 2.6 are missing from SDT. Most are documented in the manual, usually with a chunk of code that produces an equivalent result. But some SDD features are flat-out missing. For example, I needed to bring about 20 tables from a FoxPro 2.x/DOS application into a new VFP database. SDD has a feature that would have allowed me to batch add all these tables by specifying the directory that contains them. With SDT, I had to add each table one at a time (although I suppose I could have batch added the tables into SDD 2.6 and then used the supplied MIGRATE.PRG to convert the SDD tables into SDT tables).

Another possible area for improvement is speed. SDT is noticeably slower than SDD in some areas. Most noticeable on my machine is the time it takes to open the extended table designer from after I click on SDT's Modify Table Command button.

A nice improvement to the extended table designer interface would be to allow me to select a different table without closing that form and returning to the main SDT form. The table name already appears in a long text box at the top of the form. Turning that box into a combo box containing a list of all tables would make it easier when I need to make property changes to fields in multiple tables, or when I want to run the index wizard on each table in turn.

Possibly the biggest omission in the initial version of SDT was support for views. Version 3.0c now supports views, but they still are not treated as the equals of tables. Extended properties for views are added to the metatables and the Autosize builder will utilize those properties when you drag view fields onto a form. But since views still do not show up in the list of database tables within SDT's visual interface, these properties must be set for views programmatically.

Should you buy it?

If you think your Visual FoxPro development efforts could benefit from an extended data dictionary tool (few should think otherwise), I strongly suggest that you look at SDT. Even with the few warts mentioned here, I find SDT to be a welcome addition to my VFP toolbox. Stonefield has a reputation for providing superb support and for issuing timely upgrades that include both bug fixes and new features.

Speaking of support, Stonefield's is provided via CompuServe, fax, and phone. Product updates also are available on the Internet. Priority support, free for the first 60 days, is available for $199 per year. This plan includes version updates and allows customers to contact Stonefield by phone, fax, or CompuServe. Standard support is free and provided only on CompuServe. Standard support customers pay for version updates.

Kelly Conway works as a software developer at Dimico Manufacturing Company (Lee's Summit, Missouri) and spends all of his "spare" time climbing the VFP, OOP, and web development learning curves. He has developed software for 10-plus years, working with FoxPro since version 2.0. 816-525-5325, [email protected], [email protected]

Reprinted with permission from
©FoxTalk
All rights reserved
Pinnacle Publishing, Inc.
P.O. Box 888
Kent WA 98035-0888
Tel 206-251-1900, Fax 206-251-5057

Return to the Stonefield Database Toolkit Main Page
More Stonefield Database Toolkit Features
Version 6 Features
General Information about Stonefield Database Toolkit