----------------------------------------
Despite
the best planning, it may become necessary to add fields to tables in your
application. If existing users have
data in their application that you wish to preserve, you will need a method to
access that data and also use your new fields.
There are
three basic solutions to this problem:
·
Plan
ahead, and add blank fields to your application. However it is unlikely that
you will add just the right number of fields and the right datatypes. In addition, the names of the fields may
bear no relationship to the data you will place in them, making for
non-intuitive development and coding.
This has the advantage of not needing to add fields, at least initially,
and might be a reasonable choice for some applications.
·
Add
fields and update via the PC.
·
Add
fields and update via the Palm (stand alone application).
There are
numerous methods available to update your app if you want to use a PC
application, and the reader is referred to the archives of sf-talk for further
discussion. This has the obvious
advantage of complete control over all aspects of your app and the disadvantage
of needing to know how to write such an install/conversion PC program.
The
following is an outline for one method.
Instead of merging the
old data on the device, the merging will be done on the PC during HotSync.
·
Application A1
is version 1 that uses table B1 and C1
·
App A1 is
upgraded to A2 (doing this will change all the PDB names).
·
Write a VBA
procedure in Access that can be called when A2 is synced for the first time.
The code can do the following during HotSync:
For each "old" table
Get "old" table from Palm
Merge "old" table into new table
Put new table back into Palm
Next
One missing step from the above routine is
how to delete the old tables. There isn't an API that can be called during
HotSync for this, so users or the app will need to remove them manually.
There are
at least two methodologies that will allow you to add fields and update your
application with a stand-alone application.
·
Add new
table based on unique identifier.
o
Advantages: not touching old data
o
Disadvantages: not clean, complex code for initializing,
deleting, and
o
manipulating
records, may require complex filter management
o
Access: add new table, rewrite many queries
·
Add fields
as needed in AppDesigner
o
Advantages: clean, easy to design
o
Disadvantages: manipulates user data
o
Access: add new fields to existing table, rewrite
some queries
The first method is fine if you
plan a one time fix, but the rest of this document will concentrate on the
second method, as it is more conducive to complex, long-term development. Note that there are probably multiple ways
to accomplish this task, but hopefully this will provide a solid methodology
that can others can build upon.
There are two ways to accomplish
this goal on the Palm, which I will call the one-step and two-step processes.
Step 1: Take an inventory of your project. It is helpful to make a listing of the following elements:
· The fields in your tables
· The linked table for each form
· All filters
Step
2: Copy, paste and rename the
table. The original table will stay the
same and the new table will change its name.
Keep in mind that the tables are all truncated to 8 characters in the
DBF and PRC files, and you will need to create unique names for your old and new
tables. I.e.: You cannot have "MyReallyLongTableName" and
"MyReallyLongTableName_02", as they will both truncate to
"MYREALLY.DBF" and
"ESMSF0100.MYREALLY.PRC". In
this example, I use Table1 and NewTable1
Step
3: Change all linked forms, filters and
code that use Table1 to reflect NewTable1. This is potentially time
consuming and prone to error. However,
there are a number of ways to use the AppDesigner to help with this task:
·
Make
a copy of Table1 (copy and paste it so it reads Copy(1) of Table1). You will need this later.
·
Before
your have changed the linked forms and filters, start to delete Table1. You will see a prompt and a list of any
remaining linked forms and filters.
Write them down so you can change them and check them off. Be sure not to delete Table1, however.
·
Change
the filters first. It is helpful to add
the new filters, make sure they are the same as the old filters, then delete
the old filters.
·
Before
changing the linked forms, write down all necessary information for the
associated controls. Then relink the
form to the new table and relink and reformat the controls in that form.
·
Once
you have changed the linked forms, controls, and filters, then delete Table1.
If you see a prompt and a list of any remaining linked forms and filters, go
back and change any that remain and try again.
·
Use
global search for all scripts for Table1 and change to NewTable1 (and lobby
PUMATECH for a Search and Replace function.).
·
Rebuild
your application to detect any errors in syntax.
·
When
you have successfully deleted Table1, then restore the table by renaming
Copy(1) of Table1 to Table1.
Step
4: Add or delete fields from your new
table and enhance your application. This is the
"easy" part. It might be a
good idea not to delete fields, but this is up to the developer.
Step
5: Write code to recognize the new
version after it is installed. One way to do this is to
keep track of your version number in the Palm Preference table using
SFPocketUtils SetAppPref and GetAppPref functions. Note that when you update your app, you are distributing just the
ECRID0100$APPNAME.PRC, ECRID0100#APPNAME.PRC, and the
ECRID0100_NEWTABLE1.PRC. The original
table (ECRID0100_TABLE1.PRC) is already on the Palm and you are not changing it
in the new version of your app. (Critical point!)
Step
6: Transfer records record by record
and field by field from Table1 to NewTable1.
Step
7: Delete all records from Table
(optional). Technically this is not necessary,
but it conserves space.
Step
8: Delete Table1 from Palm using
SFPocketUtils DeletePalmFile function (optional).
Theoretically, it is
safe to delete the tables that are no longer attached to your application. When
the runtime engine opens your application, it cycles through all the tables. If
any of the needed tables cannot be found, the runtime engine will complain, and your application will not
open.
This
step is optional and I am not sure this
is a good idea. It can cause your
application to crash on startup, as noted below. Also note that you will need to keep the original table in the
AppDesigner as long as you have outstanding applications that need to be
updated (perhaps forever, in the case of a web-distributed application.)
It is my
understanding that the engine looks for linked forms and filters. You will need script to transfer your
records from old to new, but this will be detected at run-time. If you constrtuct the script so it can only
run if the table exists, and then delete it after transferring data, you should
be ok.
Step
9: If you have a PC application, change
the HotSync code to reflect NewTable1 and add and/or delete fields to your PC
database as necessary. Update scripts/queries/forms as
necessary. Note: describing how to this
is beyond the scope of this paper.
Step
10. Document your changes.
You will need some form of version control to track your code so that
any version of your application will be updated by this procedure.
The
following is a from a discussion from SF-Talk in January 2003.
Sean Severson:
The
conversion application would contain two sets of tables. One set of tables would be identical to the
current set of tables in the old version of the application. This is required in order to use
SFRecordUtils and the DupeTable function to copy the data from the old
application into these tables. I will
refer to them as "temp" tables. The other set of tables are identical
to the tables that will be in the new version of the software. You can loop through the temp tables and
copy the data into these new tables field by field. Then use SFArchiver to create a "backup" of these
tables.
The conversion application
will have the same creator id as the new version of your application. This creator id must be different than the
creator id of the old version of your application. This is required so the new tables, which you now have a backup
of, will work in the new version of your application.
After the conversion
process, the user would delete the conversion application, install the new
version of your application, and then extract the tables from the Backup
file. They will now have your new
application without losing their data.
This is a great way to allow
users of your application to upgrade if you do not have a desktop companion
where the user can synchronize or where you do have a desktop application, but
it runs in Windows and some of your users are Mac users.
David Thacker:
I think
that a two-stage upgrade process is pretty much the best way to go. SFRecordUtils DupeTable allows you to dupe
records from an external table to a table in your application, as long as they
both have the same structure. Sean's upgrader app is taking advantage of that.
1. Install upgrader app
(which doesn't wipe out existing data-filled app). Upgrader app uses DupeTable
to bring in data from existing tables to temp table. Data is copied field by field via script to second level temp
tables. Could archive existing tables
with SFArchiver for good measure.
2. User installs new version
of app, overwriting old app and data with blank tables. New app runs upgrade routine that uses
DupeTable to bring data into new tables from external second level temp
tables. New apps then deletes the
upgrade app and all temp tables, could delete old table archives too if
desired.
Result, new app with new
table structure with old data imported.
The information in this paper is accurate to the best of my ability. If you note an error, please contact me so I can update the document.
Jon Blackman, MD, MS
www.pocketinformatics.com
Revised 01/07/2003