by Dr. Peter Wayne
| Alpha Five has no built-in Xbasic commands to let you restructure a table. But that doesn't mean we can't find ones to do the job! |
Imagine this scenario: You have installed your pizza delivery application in every independent pizza parlor in the continental U.S., but one morning you awake to find a legal notice that mozzarella cheese has been linked to tooth decay and premature wrinkles. All pizzas sold for home delivery will need to carry a medical disclaimer, and you must record the date of the warning and the name of the person who receives the warning. You chuckle until you realize that you have the maintenance nightmare of modifying the table structure at each of 853,109 copies of your software. You can't just mail them new copies of the tables, because each and every one of those installations has live customer data.
I know what you're saying: "I should only be so lucky - right now I'm still working on installing the 3rd copy of my app." But even with only a few copies of an application, you may find the need to add fields to tables with pre-existing data.
Interactively, we know it's not a problem: all we have to do is add fields in Table Restructure mode. But if you have carefully kept your clients away from the mysteries of the Control Panel, or if you have given them the run-time version of A5, you can't interactively restructure the table.
Although Alpha Five does not provide any Xbasic methods to restructure a table, it supplies methods to create a new table. Instead of restructuring the existing table, we'll create a new table with the structure that we want and will copy the records, indexes, and data dictionary of the old table to the new table. Step by step we will:
Looks easy, doesn't it? Let's get started! For this example, we'll use the Customer table in the Invoice.adb database provided in your \Samples directory.
Alpha Five provides the Table.create_begin(), table.field_add(), and table_pointer.create_end() methods to create a new table. Here is the first part of the script to restructure the table, along with comments:
dim oldtbl as p
dim newtbl as p
dim nfields as n
dim i as n
dim j as n
oldtbl=table.open("customer",file_rw_exclusive)
nfields=oldtbl.fields_get() 'get total number of fields in the table
dim fldnames[nfields] as c ' name of each field
dim fldtypes[nfields] as c ' type of each field
dim fldwidths[nfields] as n ' width of each field
dim flddec[nfields] as n ' decimal width, if applicable
for i=1 to nfields
fld=oldtbl.field_get(i)
fldnames[i]=fld.name_get()
fldtypes[i]=fld.type_get()
fldwidths[i]=fld.width_get()
flddec[i]=fld.dec_get()
next
dir_put(a5.get_path())
' sets the working directory to the current table's folder
if file.exists("restruct_temp.dbf") then
table.erase("restruct_temp",.t.)
end if
' begin the new table with the first field
table.create_begin(fldnames[1],fldtypes[1],fldwidths[1],flddec[1])
' add the remaining preexisting fields
for i=2 to nfields
table.field_add(fldnames[i],fldtypes[i],fldwidths[i],flddec[i])
next
' now add the 2 new fields
table.field_add("person_receiving_warning","C",20)
table.field_add("date_of_warning","D",8)
' and finally, finish creating the new table
newtbl=table.create_end("restruct_temp.dbf")
Script Fragment 1. Create the new table.
We've created the new table but it is still empty. Let's copy over the records from the old table. We can't use a record clone() method because the new table's structure differs from the old table. We can, however, ignore some of the details of the table structure by just using the value_get() and value_put() methods of a field. Here comes Section 2:
dim nrecs as n dim j as n dim fld as p nrecs=oldtbl.records_get()+oldtbl.records_deleted() for i=1 to nrecs oldtbl.fetch_goto(i) newtbl.enter_begin() for j=1 to nfields fld=newtbl.field_get(j) fld.value_put(oldtbl.field_get(j).value_get()) next newtbl.enter_end(.t.) next
Script Fragment 2. Copying the records from the old table to the new
Notice that in Script 2 I stepped through each record of the old table, even deleted records. Most likely the user will not need any deleted records but since we never know, there's no reason why this table restructuring should have an unintended side effect of packing the table!
The methods for adding a new index are similar to those for creating a new table, and the methods for determining the indexes of a pre-existing table are documented clearly in the Xbasic reference manual:
dim indxname as c dim ix as p dim indxfilter as c dim indxorder as c dim indxflags as c dim idx as p indxname="any" i=0 while indxname<>"" i=i+1 indxname=oldtbl.index_name_get(i) if indxname<>"" then ix=oldtbl.index_get(indxname) indxfilter=ix.filter_get() indxorder=ix.order_get() indxflags=ix.flags_get() if i=1 then table.index_create_begin(indxname,indxorder,indxfilter,indxflags) idx=newtbl.index_create_end() else newtbl.index_tag_add(indxname,indxorder,indxfilter,indxflags) end if end if end while
Script Fragment 3. Find out what indexes are present in the old table and re-create them for the new table.
This next step gets a little sneaky. Alpha Five maintains 3 data dictionary files for each table:
The data dictionary files contain the field rules, layouts, and the long field names for the fields in the table (the .dbf file will internally store a 10-character field name, but if the field name is longer than 10 characters, the extra characters are stored in the data dictionary.) The .ddd file is just a .dbf file, the .ddm file is a memo file, and the .ddx file is an index file, but we can't manipulate the dictionary until we rename the files as a type that Alpha Five believes it is safe to read and write to. Here we are, then:
' close the open tables
oldtbl.close()
newtbl.close()
' copy the data dictionary files to the new table
file.copy("customer.ddm","restruct_temp.ddm")
file.copy("customer.ddd","restruct_temp.ddd")
file.copy("customer.ddx","restruct_temp.ddx")
' rename the data dictionary files so we can open and update them
file.rename("restruct_temp.ddm","restruct_dict.fpt")
file.rename("restruct_temp.ddd","restruct_dict.dbf")
file.rename("restruct_temp.ddx","restruct_dict.cdx")
' now open the data dictionary
dict=table.open("restruct_dict")
dict.fetch_first()
' and add the "long field names" for the 2 new fields
dict.change_begin()
dict.contents_m=dict.contents_m+"PERSON_RECEIVING_WARNING;DATE_OF_WARNING;"
dict.change_end(.t. )
dict.close()
' restore the proper file types to the data dictionary files
file.rename("restruct_dict.fpt","restruct_temp.ddm")
file.rename("restruct_dict.dbf","restruct_temp.ddd")
file.rename("restruct_dict.cdx","restruct_temp.ddx")
' and just for good measure, clean up the dictionary
dictionary.update("restruct_temp.dbf")
Script Fragment 4. Copy and Update the Data Dictionary.
We've finished the hard part. Now all we have to do is erase the old table from our hard drive and rename the temporary table to the name of the original. To be absolutely safe we'll do all the renaming before we erase the old table:
oldtbl=table.open("customer.dbf")
oldtbl.rename("old customer.dbf")
oldtbl.close()
newtbl=table.open("restruct_temp.dbf")
newtbl.rename("customer.dbf")
newtbl.close()
table.erase("old customer.dbf",.t.)
Script Fragment 5. Rename tables and erase the old one.
The full script is reproduced here in one piece:
''XBasic
dim oldtbl as p
dim newtbl as p
dim nfields as n
dim i as n
dim j as n
oldtbl=table.open("customer",file_rw_exclusive)
nfields=oldtbl.fields_get()
dim fldnames[nfields] as c
dim fldtypes[nfields] as c
dim fldwidths[nfields] as n
dim flddec[nfields] as n
for i=1 to nfields
fld=oldtbl.field_get(i)
fldnames[i]=fld.name_get()
fldtypes[i]=fld.type_get()
fldwidths[i]=fld.width_get()
flddec[i]=fld.dec_get()
next
dir_put(a5.get_path())
if file.exists("restruct_temp.dbf") then
table.erase("restruct_temp",.t.)
end if
table.create_begin(fldnames[1],fldtypes[1],fldwidths[1],flddec[1])
for i=2 to nfields
table.field_add(fldnames[i],fldtypes[i],fldwidths[i],flddec[i])
next
table.field_add("person_receiving_warning","C",20)
table.field_add("date_of_warning","D",8)
newtbl=table.create_end("restruct_temp.dbf")
dim nrecs as n
dim j as n
dim fld as p
nrecs=oldtbl.records_get()+oldtbl.records_deleted()
for i=1 to nrecs
oldtbl.fetch_goto(i)
newtbl.enter_begin()
for j=1 to nfields
fld=newtbl.field_get(j)
fld.value_put(oldtbl.field_get(j).value_get())
next
newtbl.enter_end(.t.)
next
dim indxname as c
dim ix as p
dim indxfilter as c
dim indxorder as c
dim indxflags as c
dim idx as p
indxname="any"
i=0
while indxname<>""
i=i+1
indxname=oldtbl.index_name_get(i)
if indxname<>"" then
ix=oldtbl.index_get(indxname)
indxfilter=ix.filter_get()
indxorder=ix.order_get()
indxflags=ix.flags_get()
if i=1 then
table.index_create_begin(indxname,indxorder,indxfilter,indxflags)
idx=newtbl.index_create_end()
else
newtbl.index_tag_add(indxname,indxorder,indxfilter,indxflags)
end if
end if
end while
oldtbl.close()
newtbl.close()
file.copy("customer.ddm","restruct_temp.ddm")
file.copy("customer.ddd","restruct_temp.ddd")
file.copy("customer.ddx","restruct_temp.ddx")
file.rename("restruct_temp.ddm","restruct_dict.fpt")
file.rename("restruct_temp.ddd","restruct_dict.dbf")
file.rename("restruct_temp.ddx","restruct_dict.cdx")
dict=table.open("restruct_dict")
dict.fetch_first()
dict.change_begin()
dict.contents_m=dict.contents_m+"PERSON_RECEIVING_WARNING;DATE_OF_WARNING;"
dict.change_end(.t. )
dict.close()
file.rename("restruct_dict.fpt","restruct_temp.ddm")
file.rename("restruct_dict.dbf","restruct_temp.ddd")
file.rename("restruct_dict.cdx","restruct_temp.ddx")
'file_add_to_db("restruct_temp.dbf")
dictionary.update("restruct_temp.dbf")
oldtbl=table.open("customer.dbf")
oldtbl.rename("old customer.dbf")
oldtbl.close()
newtbl=table.open("restruct_temp.dbf")
newtbl.rename("customer.dbf")
newtbl.close()
table.erase("old customer.dbf",.t.)
end
Full script to add 2 fields to the customer table.
Of course, you're not done now - you still have to get this update out to all your remote sites. The simplest way to perform the update is to put this script on a button attached to a "main menu" or dummy table, and send the data dictionary files for the Main Menu table to your pizza clients. We can modify Script Fragment 1 to make sure that the update is only done once by including the lines,
fldnames[i]=fld.name_get() 'this line already in the script
' add these lines
if fldnames[i]="person_receiving_warning" then
ui_msg_box("Error","Customer table already updated!")
end
end if
Script Fragment 6. Prevent the user from repeatedly adding the fields.
After the fields have been installed, you can then send the clients new layouts for the Customer table that will use the new fields by just sending new .ddm, .ddd and .ddx files.
Pizza lovers from throughout the U.S. will be grateful!
If you prefer, you can download this entire article in HTML format.
8/1/99
Don't forget, we need your feedback to make this site better!