Generate a database automatically from a schema document

Use this utility to convert a Word document to VFP program code

If you're a typical VFP programmer, you've probably got a favourite method of automatically documenting your database structures. You might own a utility that does the job for you. We've seen dozens of these programs over the years. They typically take a batch of DBF files, then generate a document - sometimes a Word document, sometimes a plain text file - that lists the table structures, index tags and similar information. Alternatively, you might use nothing more sophisticated than the LIST STRUCTURE command to achieve that same goal.

In this article, we'd like to tell you about an alternative approach. Instead of manually creating the table structures (or manually writing code to create the structures) then automatically generating the documentation, our approach is the reverse: we create the documentation first, then automatically generate the tables from it.

Why would you want to do that?

Sometimes, it makes good sense to document your table structures before you create them. In our present project, three team members are responsible for designing the database. Before actually creating the database, we wanted to produce a Word document describing the schema, and to pass it around the team for comment and revision. Other staff members, who weren’t necessarily VFP programmers, also needed to review the document.

Once the document had been approved, we wanted to be able to create the table structures (and the associated indexes) with the least possible effort. So we wrote a utility to that reads the Word document and generates a PRG file from it. The PRG file contains the code needed to create the database. This mainly consists of CREATE TABLE and INDEX ON statements (to create the tables and indexes respectively), along with calls to DBSETPROP() to set certain field properties.

If this approach appeals to you, you can download a free copy of our utility - see the download instructions at the end of the article.

How to format the Word document

For our utility to work, the Word document has to meet certain requirements. In summary, for each table in the database, the document must contain a Word table. This table must list the fields in the corresponding database table. If the table has one or more associated indexes, the document must contain a second table, containing details of those indexes. Note that, although we talk about database tables in this article, the utility works equally well with free tables.

In our case, we also wanted the document to include, for each field in each table, a Caption and a Comment property. We weren’t interested in other field properties, such as validation rules or input masks. If these are important to you, you can easily extend the utility to support them.

The first of the two Word tables (the one containing the table’s structure) must have at least four columns. In addition, it must have one row for each of the fields, plus exactly two header rows. In the first header row, the first column must contain the word Table (and nothing else); the second column must contain the name of the table that you wish to create. The second row in the Word table may contain column headers or anything else you like.

The rest of the Word table should contain your table structure, with one row for each field. The columns in these rows should contain, respectively:

The field’s name
The field’s data specification, in standard CREATE TABLE syntax (e.g. C(10) for a 10-character string, I AUTOINC for an autoinc integer).
The field’s Caption property, if any.
The field’s Comment property, If any.

Leave the last two columns blank if you’re not interested in captions or comments. Any other columns in the table will be ignored. The utility will also ignore any blank rows in the table.

Figure 1 shows how this table might look. As you can see, we added some shading in the two header rows to make the table more readable; this has no significance to the utility.

Figure 1: This Word table contains the table structure ...

The second of the two Word tables (Figure 2) contains details of the indexes. This table is optional; if the target database table has no indexes, just leave it out.

If the indexes table is present, it should follow the same general rules as the structure table, except that it should contain one row for each index (plus the two header rows). The table should have a minimum of three columns, as follows:

The index expression
The index’s tag name
If the index is a candidate key, the third column should contain the letter Y.

The first column in the first row of this table should contain the word Indexes (and nothing else). The second column in the first row should contain the target table name, just as in the structure table.

Figure 2: ... and this one contains a list of indexes

A word about primary keys

Throughout our database, the first field in a table is always the primary key. The utility knows that, and always generates a PRIMARY KEY clause for the first field within the CREATE TABLE statement. If this is not the case in your own database, modify the utility’s code to remove or alter that behaviour (the relevant code is clearly flagged with a comment).

If you don’t change this behaviour, you should be sure not to include the primary key in the indexes table. That’s because the PRIMARY KEY clause in CREATE TABLE generates the required index automatically.

Using the utility

To use the utility, simply call the Doc_To_Tables program, passing two parameters: the filename of the Word document, and the name of a program file where the utility will write its output (that is, the CREATE TABLE and related statements). Either filename may be qualified with a path. For example:

DO Doc_To_Tables WITH ;
  "c:\projects\documentation\TableSchemas.Doc", ;
  "c"\dev\code\NewTables.PRG"

Note that if the file specified in the second parameter already exists, the utility will overwrite it.

You can call the program either from the command window or from within another program. When it finishes, a code editing window will open containing the program that the utility has generated. Figure 3 shows an example.

Figure 3: The generated code opens in a program editing window.

Take care

The utility has a sparse user interface and does very little error checking. It is, above all, a tool for programmers working in the VFP development environment. We haven’t tried to produce a fully-polished piece of software. For that reason, we suggest that you check the source code before you use it, and modify it as necessary to meet your needs

You should also check the generated code before your run it. We don’t claim that it will be bullet-proof. If there are any errors in your Word tables, these could be propagated to the generated code. For example, if you are creating free tables and any field name exceeds ten characters, an error will be generated when you run the code.

Despite these rough edges, we’ve found this utility extremely useful in our work, and we hope you do too. Its great advantage is that it lets you focus your attention on the design of the database rather than the mechanics of creating it. Once you’ve nailed down the design, actually creating the database will be a breeze.

Download it now

To download the utility, simply click on the link below. The download file is Doc_To_Tables.zip. It contains the utility itself (Doc_To_Tables.prg) and a sample Word document you can test it with.

Download now

The download file is just 12 KB. The utility requires VFP 9.0 or above.

Mike Lewis Consultants Ltd. December 2006.

FOXSTUFF