Transmitting new or changed layouts and scripts to a client's site is straightforward and easily accomplished. However, structural changes to tables are not as easily done. Your choices are:

•
A trip to the clients site to do this yourself.

•
Log in to the clients site remotely and manually make the structural changes yourself.

•
Depend upon the client to make the changes manually.

•
Close the clients site down, transferred the data to your site, make the changes, and transfer back.

•
Hope a Table.add_fields() method is added to Alpha Five version 5!

On a client-by-client basis, one of the above choices will probably be an acceptable fit. But what happens when you have multiple clients running your application? The approach to this problem that I outline here will help you solve this problem. With a small amount of preparation at your site, you can transmit a small self-extracting zip file to your client's site that will allow the client to update a table with the push of a button.

Outline of how the table update process works:

At your end, you modify the table (only add fields to the end of the table, don't even think about deleting existing fields). I'll call this table "OrigionalTable".

Duplicate the table, adding all field rules, layouts, and indexes etc., AND the records. For some reason, if you duplicate the table without the records, Alpha does not duplicate the indexes exactly like the original table. When duplicated WITH records, it appears to duplicate indexes correctly. This makes me nervous, so always check the indexes in the duplicate table and make sure they are the same as the original table. I'll call the new duplicate table, "TempTable". As an alternative to duplicating the tables, you could copy the modified table with its associated data dictionary files to a different location. Then rename the files "TempTable.xxx". Now copy the renamed files back to the original location and add the table to the database (you could just add the renamed table to the database from its new location, just don't forget where you are keeping everything). I actually prefer this method because of the index issue mentioned above.

Turn on the script recorder, make sure 'Recording' is checked.

Create a new Append Operation, and append the records from the OriginalTable to the new TempTable. When defining the append operation make sure you do NOT have your new fields included in the definitions. These fields, of course, do not exist at your clients site.

Go to the script recorder and copy the Xbasic syntax. Paste this into the UpDateTable script at the appropriate location. In the first section of the script you will need to put the name of the existing table and the name of the temporary table. It is indicated where you enter the two names. The append operation that you created in the Operations tab automatically opens the appropriate table, much like when a form is opened, and loads the underlying table. Because of this, the first line of the append routine will be:
a_tbl = table.current() As we are running this within a script, this first line MUST be changed to: a_tbl = table.open("TempTable") The second line of the copied Xbasic routine will also have the complete path statement to the temporary table. Similar to this: append.t_db = "c:\a5apps\a5writings\xbasic\invoice\invoice_header.dbf" This will be the path name to the temporary table ON YOUR SYSTEM. As this routine assumes all files are in the data path, remove all path information, so the above line should read: append.t_db = "invoice_header.dbf" Also, because there will be Xbasic running after the append operation that will rename the tables, you MUST close the table at the end of the append operation, with this line: a_tbl.close() These three changes should be the only changes you need to make to the captured append Xbasic.

Empty the new TempTable of all the records. This will be the empty table you will transmit to your client.

Transmit the empty TempTable.dbf along with all its related files (dbf, ddd, ddm, ddx, cdx, fpt) and the script to your clients location. Make sure the table files are located in the data directory. The TempTable files do not need to be added to the database, they just need to be present in the data directory.

Have your client backup all files.

Run the script.

What To Watch Out For

This script is not for the feint at heart. Although it works well, you are messing with files in a multi-user environment.

•
Make sure your client has made a backup.

•
Make sure everyone is out of the database before attempting to do this.

•
Make sure the update is not being run in a shadow directory.

•
Make sure you test it on your end BEFORE you send it to your client.

•
There is no error checking in this script. Make sure the tables exist at your clients site, with the field structure that you expect, ie: has the client changed/added fields to the table.

•
Make sure you make the corrections to the copied append routine.

If the script were to be inadvertently run a second time, it will break before it does any damage (assuming it ran successfully the first time), because the TempTable will no longer exist.

If the append operation breaks (bad data in the clients tables) it should leave your client in the same state as before the routine was run. It is not until after the append operation completes that the tables are renamed. This routine does not delete the old tables on the clients system. It renames them with a numerical suffix. If the existing table is named, 'Invoice_Header', it will be renamed to 'Invoice_Header1' as long as that name does not exist in the data path. If it does, the script will keep incrementing up the numerical suffix until it finds a unique name in the data path. You could easily modify the script to delete the old table and related data dictionary files, I just felt safer by leaving them.

How to Automate This Process for Your Client

You should have a dummy table used for your menu forms. On a Utilities Menu form, add a button I'll call 'UpDateButton'. This button will look for a text file I call 'UpDate.txt' in the data path. If it does not find this file, the UpDateButton script will give the user a message, 'No Current Update Was Found', and end. The UpDate.txt file is a file you send with your updated files. If the button script finds the text file, it opens it and reads the name of the update script to play. The button then deletes the text file (so the update cannot be run a second time), and then plays the script that it was directed to by the text file.

A script can be attached to any table's data dictionary file. I feel it is preferable to have a separate table attached to your database expressly for the purpose of transmitting scripts, however you can attach scripts to any table's data dictionary file. If you have a dummy table you use for menu forms, this table would make a good choice to transmit scripts with. I'll assume you have a specific table to transmit scripts with and I will refer to this table as the 'ScriptsTransmit' table. When you create a new script, you are asked to name the script. At this prompt window you can also define an alternate library (data dictionary) location. You must type in the entire path to the desired *.ddd file. So assuming that my data path is: "c:\A5apps\invoice", you would enter the following path statement to attach a new script to the ScriptsTransmit table:

C:\A5apps\invoice\ScriptsTransmit.ddd

Now you can send your client this table and related files and the script will go with it. However, if this table is not attached to the database at your clients site, then it will have to be added to the database before the script will be available for use. The table can be easily added via Xbasic by running the following syntax:

file_add_to_db("YourTableName.dbf")

To set this up for automation you will have to transmit to your client:

•
A new set of data dictionary files with the update button added to a menu form.

•
The new empty temporary database files with the added field/s.

•
The updateTable script with the new append routine pasted in via an existing table's data dictionary files, or via a new added ScriptsTransmit table's data dictionary files (if you transmit via this method, you will have to add the ScriptsTransmit table to the database using "file_add_to_db()" syntax.

•
The upDate.txt file, containing the name of the script you want run to perform the update.

I choose to zip these files up using ZipCentral, a freeware zip utility, but any good zip utility will do. ZipCentral, as will most zip utilities, will create a small self-extracting executable file that the client can easily run. Now when the client opens your application, after running the self-extracting zip file, the update button you placed on the form will be available. Pushing this button will cause the button script to look for the existence of the upDate.txt file. If it, upDate.txt, does not exist, the script will inform the user and end. If it exists, the script will open the file, and extract the name of the script to run. Then it will delete the text file, so it can not accidentally be run a second time. The button script will then play the update script which performs the actual update on the desired table.

The UpDateTable Script (syntax reproduced here):

'*******************************************************************
'* Routine to update a table at a clients site. You must supply
'* the append routine. Get the append routine by running an append
'* operation at your site with the script recorder running and
'* capture the Xbasic. Make sure that you do NOT append the new
'* fields in this routine. Make sure you make the necessary change
'* to the captured Xbasic, ie: change the first line of Xbasic from
'* "a_tbl = table.current()", to
'* "a_tbl = table.open("YourTemporaryTableName"). Also at the end
'* of the append routine, you must add "a_tbl.close()". This must
'* be done otherwise later in the script when the files are renamed,
'* the open table will break the script. Enter the name of your
'* existing table, and the temporary table on the two variable
'* assignment statements following:
'*******************************************************************

'******* The name of the existing table to be updated

ExistingTableName = "Replace_with_Existing_Table_Name"

'******* The name of the temporary table containing the new field/s

TempTableName = "Replace_with_Temporary_Table_Name"

'*******************************************************************
'* Your captured Xbasic append routine goes here
'* Remember to correct the first 2 lines
'*******************************************************************
'Replace with captured Xbasic
a_tbl.close() 'remember to make sure this line is added to the end
'of the append routine

'*******************************************************************
'* End of the captured Xbasic append routine
'* The following section contains the embedded function NewName. This
'* function selects a new name to rename your existing old table to.
'* It makes sure to give the old table a unique name in the directory.
'* It will add a numerical suffice to the table name, incrementing up
'* until a unique name is found.
'*******************************************************************


Function NewName as c(vName as c) 
   vcount = 0
   vpath =:a5.get_path()+ chr(92)
   vLen = len(vName)
   
   Again:
   
   if file.exists(vpath+vName+".dbf").or.file.exists(vpath+vName+".ddd").or.\
      file.exists(vpath+vName+".ddm").or.file.exists(vpath+vName+".ddx").or.\
      file.exists(vpath+vName+".cdx").or.file.exists(vpath+vName+".fpt")
      vcount = vcount + 1
      vName = substr(vName,1,vLen)+alltrim(str(vcount))
      goto Again	
   end if
NewName = vpath+vName	
End Function

'*******************************************************************
'* Beginning of ReName function. This function is called twice. The
'* first time it renames the old table files, after the append
'* routine, to the new unique name returned by the NewName function.
'* The second time it is called it renames the temporary table files
'* to the origional name of the old table files.
'*******************************************************************


function ReName as l(ET as c, vNewName as c)
   vlist = "dbf,ddd,ddm,ddx,cdx,fpt"
   vpath =:a5.get_path()+ chr(92)
   ET = vpath+alltrim(ET)
   vExists = ""
   for i = 1 to 6
      if file.exists(ET+"."+word(vlist,i))
          vExists = vExists + word(vlist,i)+","
      end if 
   next
   if vExists = ""
      ui_msg_box("Files not found","Routine Aborted",UI_STOP_SYMBOL)
      goto finish 
   end if
   for i = 1 to w_count(vExists)
      file.rename(ET+"."+word(vExists,i),vNewName+"."+word(vExists,i))
   next    
   ReName = .t.
   finish:
end function

'*******************************************************************
'* Xbasic that calls the embedded functions with the names of the
'* original and temporary tables '*******************************************************************


vRenamed = newName(ExistingTableName)
ReName(ExistingTableName,vRenamed)
ReName(TempTableName,ExistingTableName)
'ui_msg_box("A Message Goes Here","Do you want to leave a message?")
end

'* Button Script

this.disable()

if  ui_msg_box("WARNING","All users MUST be out of the database \
   to run an update. Do you want to continue?", UI_STOP_SYMBOL+UI_YES_NO)
   = UI_NO_SELECTED then
   ui_msg_box("PROCESS ABORTED", "The update was not procesed",\
   UI_ATTENTION_SYMBOL)
   end
end if

if .not. (:a5.get_master_path() = "")
   ui_msg_box("WARNING, Processing Halted",\ 
   "Network Optimization present. This update must be run at the server.",\
   UI_STOP_SYMBOL)            
   end
end if

vpath = :a5.get_path() + chr(92)

vFound = file.exists(vpath+"upDate.txt")

if .not. vFound
   ui_msg_box("","No current update found",UI_INFORMATION_SYMBOL)
   end 
end if

on error goto OpenFile
vfile = file.open(vpath +"upDate.txt",FILE_RO_EXCLUSIVE)
on error goto 0
vText = ""
vText = alltrim(vFile.read_line())
vfile.close()
'file.remove(vpath +"upDate.txt")
script_play(vText)


end

OpenFile:
ui_msg_box("Process Aborted", \ 
   "Update file currently open by another process. \ 
   Report to tech support.", UI_STOP_SYMBOL)
end

'curious construct
while .not. vfile.eof() 
   vText = vFile.read_line()
   eval(vText)
end while

General Updating of Client Files (no structural change to tables)

When I have made a number of changes to a clients database, right or wrong, in practice I transmit all database files, EXCEPT *.dbf and *.fpt files. I overwrite the client's files and always do a database compact to update the indexes. This has always worked for me. If you just make a change to a layout, report, or something similar, just send the data dictionary files associated with the underlying table or set.

One of the simplest and easiest ways to transmit these files is to use one of the several available self-extracting zip utilities. I use ZipCentral. It is free, it will create a compact self-extracting executable file. You can even include a text file in the zip and tell ZipCentral to open and display it to the client after extracting. This way you can present the client valuable information, such as "SEND MONEY NOW".

Alpha Five's File Types

<tbl>.dbf
This file contains the actual data. If you transmit this to your client, this will overwrite the client's data with whatever data is contained in the file you transmitted. Usually not a thing to do unless you KNOW that you have all the current data in your files.

<tbl>.fpt
This is the memo field file for a table. The same circumstances apply here as with *.dbf files.

<tbl>.cdx
This is the composite index file. All the individual indexes for a table are stored in this file. Although this file contains data, in the form of key values, these values are derived from the tables. A database compact, a table pack, or an index rebuild will restore these files from the table's data. As long as you make sure you update the indexes, you can overwrite these files. If you don't update the indexes after overwriting these files, the client will be on the phone to you.

<tbl>.ddd, .ddm, .ddx
These files are the data dictionary files for a table. This is where the field rules, forms, reports, letters, etc. are stored. These files are in the dbf format. The *.ddd is the dbf file, the *.ddm is the fpt file, and the *.ddx is the index file. These files are the one that you will almost always transmit to your client. All changes and additions to field rules, forms, reports, etc. will be transmitted with these files.

<set>.set, .sem, .sex
These files are the data dictionary files for a set. Similar to the above, these files store the forms, reports, indexes, etc. for the set. However, a sets index file (*.sex) is based upon (identical to) the parent table's index file. Changes to the parent table's indexes are automatically cascaded to the set's index file. Remember, a set is just a virtual construct to link and view your data from different perspectives. The 'real' data resides in the tables. Sets allow us to view the table data in different ways.

<database>.adb, <database>.alb, <database>.alm, <database>.alx
The database files themselves. These contain information about the database, ie: what tables and sets are attached, path info, and script storage.

Appendix A

As an aside to this approach to updating a table, you could, using the same techniques, run any type of update. By transmitting a script and text file to the client, you are limited only by your knowledge of Xbasic.

As a curiosity only, you can also 'embed' the Xbasic syntax directly in the text file. Consider the following:

vfile = file.open(vpath +"upDate.txt",FILE_RO_EXCLUSIVE) while .not. vfile.eof() vText = vFile.read_line() eval(vText) end while vfile.close()

If each line of the text file is a stand-alone Xbasic command or routine, you can execute it directly from the text file. But as I said, I would consider this a curiosity, rather than a serious approach. However, with Alpha Five version 5 close to release, we will have serious tools to implement code indirection, using the evaluate_template() function and Xdialog, PLUS, the whole new world of Packages..............So many tools...........So little time...............

•	A trip to the clients site to do this yourself.
•	Log in to the clients site remotely and manually make the structural changes yourself.
•	Depend upon the client to make the changes manually.
•	Close the clients site down, transferred the data to your site, make the changes, and transfer back.
•	Hope a Table.add_fields() method is added to Alpha Five version 5!

•	Make sure your client has made a backup.
•	Make sure everyone is out of the database before attempting to do this.
•	Make sure the update is not being run in a shadow directory.
•	Make sure you test it on your end BEFORE you send it to your client.
•	There is no error checking in this script. Make sure the tables exist at your clients site, with the field structure that you expect, ie: has the client changed/added fields to the table.
•	Make sure you make the corrections to the copied append routine.

•	A new set of data dictionary files with the update button added to a menu form.
•	The new empty temporary database files with the added field/s.
•	The updateTable script with the new append routine pasted in via an existing table's data dictionary files, or via a new added ScriptsTransmit table's data dictionary files (if you transmit via this method, you will have to add the ScriptsTransmit table to the database using "file_add_to_db()" syntax.
•	The upDate.txt file, containing the name of the script you want run to perform the update.