Programming with dBASE - application updates

Author

application updates - tables

Robert Bravery

2005-11-08, 9:24 am

Hi all,

There has been some recent post regarding appliation updates and the best
way to achieve that. I would like to add another dimension to that and would
like some ideas.
Say you need to update a clients application with added features that have
been programmd in. Easy enought. But say that those extra features required
you to change some table structures. How would you programmably update the
application, including the table structures but preserving the clients
current data

Thank
Robert

Rick Gearardo

2005-11-08, 9:24 am

Alter table

> There has been some recent post regarding appliation updates and the best
> way to achieve that. I would like to add another dimension to that and
> would
> like some ideas.
> Say you need to update a clients application with added features that have
> been programmd in. Easy enought. But say that those extra features
> required
> you to change some table structures. How would you programmably update the
> application, including the table structures but preserving the clients
> current data

Dan Bernard

2005-11-08, 9:24 am

Robert,

I have been doing this long enough to know that clients always want
something more. I add some extra fields to my tables of different types
just for this reason. Now, before everyone gets upset I realize that this
is not third normal form but it is practical and with the speed of today's
processors and available memory the overhead is negligible.

Just one idea...

Dan

"Robert Bravery" <me@u.com> wrote in message
news:RU3qzNG5FHA.1236@news-server...
> Hi all,
>
> There has been some recent post regarding appliation updates and the best
> way to achieve that. I would like to add another dimension to that and
> would
> like some ideas.
> Say you need to update a clients application with added features that have
> been programmd in. Easy enought. But say that those extra features
> required
> you to change some table structures. How would you programmably update the
> application, including the table structures but preserving the clients
> current data
>
> Thank
> Robert
>
>

Paul Van House

2005-11-08, 9:24 am

In article <iqSqCaG5FHA.1240@news-server>, dbase@NOSPAMsecurity
corp.com
says...[color=darkred]
> Alter table
>

As Rick points out, Alter Table will work.
If you're adding fields it is straightforward....you need to be sure
everyone is out of your system, then use alter table to add the fields.
If you're changing the length of a field, there are a couple of
steps. Assume we're changing the "name" field from 15 characters to 30
characters.
1) Alter table to create a temp field (temp_Name)
2) A loop to set temp_name to equal name (replace all temp_name with
name)
3) Alter table to drop the name field
4) Alter table to add the name field as 30 characters.
5) A loop to set name to equal temp_name (replace all name with
temp_name)
6) Alter table to drop the temp_name field.

--
Paul Van House
remove ".removeme" for e-mail replies
Radio/TV Software and Baseball Stat Software:
http://www.binxsoftware.com
Family Home Page: http://vanhouse.binxsoftware.com
Church Home Page: http://www.ashfordumc.org

Robert Bravery

2005-11-08, 3:58 pm

HI Paul,
Yes I am aware of alter table.
What I am looking at is a way to make it more automatic and more general.
For instance, the program would not know what version of the table is on the
clients PC or what version it is upgrading to.
It has to compare and make the needed changes.
I had thought of using a combination of tabledef and alter table. But there
are just so many things to check. For instance, is the field there, has its
name change, if it has, is it the same field or has it changed position, has
it change type, has it changed length, does it have a default, has it
changed, does it have a lookupsql, etc etc
The is a lot of things to compare, even with one field

Does this make sense

Thanks
Robert

"Paul Van House" <pvanhouse1.removeMe@houston.rr.com> wrote in message
news:MPG. 1dda65b05a94dfbd9896
a9@news.dbase.com...
> In article <iqSqCaG5FHA.1240@news-server>, dbase@NOSPAMsecurity
corp.com
> says...
best[color=darkred]
>
>
> As Rick points out, Alter Table will work.
> If you're adding fields it is straightforward....you need to be sure
> everyone is out of your system, then use alter table to add the fields.
> If you're changing the length of a field, there are a couple of
> steps. Assume we're changing the "name" field from 15 characters to 30
> characters.
> 1) Alter table to create a temp field (temp_Name)
> 2) A loop to set temp_name to equal name (replace all temp_name with
> name)
> 3) Alter table to drop the name field
> 4) Alter table to add the name field as 30 characters.
> 5) A loop to set name to equal temp_name (replace all name with
> temp_name)
> 6) Alter table to drop the temp_name field.
>
> --
> Paul Van House
> remove ".removeme" for e-mail replies
> Radio/TV Software and Baseball Stat Software:
> http://www.binxsoftware.com
> Family Home Page: http://vanhouse.binxsoftware.com
> Church Home Page: http://www.ashfordumc.org

David Kerber

2005-11-08, 3:58 pm

In article <ZE5#ZcJ5FHA.1236@news-server>, me@u.com says...
> HI Paul,
> Yes I am aware of alter table.
> What I am looking at is a way to make it more automatic and more general.
> For instance, the program would not know what version of the table is on the
> clients PC or what version it is upgrading to.
> It has to compare and make the needed changes.
> I had thought of using a combination of tabledef and alter table. But there
> are just so many things to check. For instance, is the field there, has its
> name change, if it has, is it the same field or has it changed position, has
> it change type, has it changed length, does it have a default, has it
> changed, does it have a lookupsql, etc etc
> The is a lot of things to compare, even with one field
>
> Does this make sense

Yes, but I would say that if you are changing field names, there is
essentially no reasonable way to handle this unless you supply something
that tells the update program what to look for, something like a
dictionary. What if you change FIELD1 to FIELD21 and then add a new
FIELD1? If you are running into cases like that, I'd say you're SOL.

If the names are the same, it's not too difficult to check for data
types, lengths, etc. If you have sufficient documentation from earlier
versions of the program, then the way I'd do it would be to keep track
of the fields which existed with each version, and some kind of
indicator of their purpose, so you could map something like:

DO CASE
CASE prog_ver = "1.2.1"
DO CASE
case field_purpose = "USER_GIVEN_NAME"
old_fieldname = "FNAME"
new_fieldname = "GIVEN_NAME"
field_length = 50
case field_purpose = "USER_LAST_NAME"
etc....
END CASE
CASE prog_ver = "4.3.7"
DO CASE
case field_purpose = "USER_GIVEN_NAME"
old_fieldname = "GNAME"
new_fieldname = "GIVEN_NAME"
case field_purpose = "USER_LAST_NAME"
etc....
END CASE
etc....

Then use the information in those variables to build the appropriate
ALTER TABLE statement.

It's a real pain to code and verify, but if your fields have changed as
much as you describe, I don't see any alternative.

FWIW, I never change a field name once an application is deployed, even
if the field's use has changed over time. The only thing I allow myself
to do is add new fields, and possibly lengthen character fields and
change numeric types to larger ones (never smaller).

.....

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

evilaro

2005-11-08, 8:23 pm

Robert:

Not that this can be the best sistem buy
the way we do it with EvilFOTO is to Import
the old data into the new one.

So .. if in the new version I add 3 fields and delete 1
and enlarge 2... the important structure will be with the
new instaled one.. then you import (append) the values.

This has its own problems... but considering the ammounts
of versións I have done to the program ... it has always worked
okay.

As a security you could make sure you are not importing from a more
recent version of data.

Still the alter table looks good... but at the time I decided the import
it seemed to me the most safe.

Hope it helps

Emilio

"Robert Bravery" <me@u.com> escribió en el mensaje
news:ZE5%23ZcJ5FHA.1236@news-server...
> HI Paul,
> Yes I am aware of alter table.
> What I am looking at is a way to make it more automatic and more general.
> For instance, the program would not know what version of the table is on
the
> clients PC or what version it is upgrading to.
> It has to compare and make the needed changes.
> I had thought of using a combination of tabledef and alter table. But
there

> are just so many things to check. For instance, is the field there, has
its
> name change, if it has, is it the same field or has it changed position,
has
> it change type, has it changed length, does it have a default, has it
> changed, does it have a lookupsql, etc etc
> The is a lot of things to compare, even with one field
>
> Does this make sense
>
> Thanks
> Robert
>
> "Paul Van House" <pvanhouse1.removeMe@houston.rr.com> wrote in message
> news:MPG. 1dda65b05a94dfbd9896
a9@news.dbase.com...
> best
and[color=darkred]
>
>

2005-11-09, 7:23 am

HI,

Yes I have thought of the append method. Copying data to a temp folder,
copying the new structure over, then appending the data. This has problems
with autoinc fields and indexes. You have to reset the autoingc field before
copying to the right number, and check that all the index tags are correct.

Each method has its downside, I guess I am looking for which one has the
least and works the best.

I can't believe that people here have never had the need to update client
table structures. Either their apps are very small, that it is
insignificant, or they are extreemley good in ther design that their tables
are100% perfect. And I hve never come across a 100% perfect design,
expecially in a large corporate application

Robert

"evilaro" <buzon@evil.es> wrote in message
news:zJztacL5FHA.1236@news-server...
> Robert:
>
> Not that this can be the best sistem buy
> the way we do it with EvilFOTO is to Import
> the old data into the new one.
>
> So .. if in the new version I add 3 fields and delete 1
> and enlarge 2... the important structure will be with the
> new instaled one.. then you import (append) the values.
>
> This has its own problems... but considering the ammounts
> of versións I have done to the program ... it has always worked
> okay.
>
> As a security you could make sure you are not importing from a more
> recent version of data.
>
> Still the alter table looks good... but at the time I decided the import
> it seemed to me the most safe.
>
> Hope it helps
>
> Emilio
>
>
>
>
> "Robert Bravery" <me@u.com> escribió en el mensaje
> news:ZE5%23ZcJ5FHA.1236@news-server...
general.[color=darkred]
> the
> there
> its
> has
dbase@NOSPAMsecurity
corp.com[color=darkred]
the[color=darkred]
> and
sure[color=darkred]
fields.[color=darkred]
30[color=darkred]
>
>

Marilyn Price

2005-11-09, 7:23 am

In article <RU3qzNG5FHA.1236@news-server>, me@u.com says...
>
> There has been some recent post regarding appliation updates and the best
> way to achieve that. I would like to add another dimension to that and would
> like some ideas.
> Say you need to update a clients application with added features that have
> been programmd in. Easy enought. But say that those extra features required
> you to change some table structures. How would you programmably update the
> application, including the table structures but preserving the clients
> current data
>

Copied from a program I'm in the process of adding features to. I've
gotten in the habit of adding the code to the main.wfm open or onopen
method and modify my own test tables this way. That way, I don't forget
to modify the tables at the client location.

// structure change to jobtypes table - 11/8/05
q = new query()
q.sql := 'select * from "jobtypes.dbf"'
q.active := true
try
ll = q.rowset.fields['pocode'].value
catch( exception e)
q.active := false
alter table "jobtypes.dbf" add pocode char(1)
q.active := true
if q.rowset.first()
do
q.rowset.fields['pocode'].value := 'A'
q.rowset.save()
until not q.rowset.next()
endif
endtry

That said, most of my clients are small businesses and I know the times
of day when my programs are usually idle. I normally have to kick
everyone out of them for the update <g>, so, after updating, I run the
program the first time (ensuring the tables can be opened exclusively
for these steps), then everyone else is let back in.

This may not work in your situation.

--
Marilyn Price
M. P. Data

Garry Christensen

2005-11-09, 7:23 am

G'day,
I do this regularly. This process is not the most efficient but it is
reliable.

First, rename the table to a backup folder
_app.databases[ 1 ].renameTable( "Client Table", "Backup of Client Table" )

Copy your new files to the data location, then open a query for each table
and look through the rows in the backup table and append the fields to the
new one:
do while not qSource.rowset.endOfSet
qDestination.rowset.beginAppend()
for i = 1 to qSource.rowset.fields.size
try
qDestination.rowset.fields[qSource.rowset.fields[i].fieldname].value
= qSource.rowset.fields[i].value
catch (exception e)
endtry
endfor
qDestination.rowset.save()
enddo

In fact, I use my own deployment application that allows me to select all
the files I want to send to the client. I can flag databases to overwrite
the original or append the data as outlined above. It then creates a
project file and 'builds' an executable containing all the files to be
deployed, along with the form that does all the unpacking and database
building.

See Ya,
Garry

"rb" <me@u.com> wrote in message news:s82ARfR5FHA.1244@news-server...
> HI,
>
> Yes I have thought of the append method. Copying data to a temp folder,
> copying the new structure over, then appending the data. This has problems
> with autoinc fields and indexes. You have to reset the autoingc field
> before
> copying to the right number, and check that all the index tags are
> correct.
>
> Each method has its downside, I guess I am looking for which one has the
> least and works the best.
>
> I can't believe that people here have never had the need to update client
> table structures. Either their apps are very small, that it is
> insignificant, or they are extreemley good in ther design that their
> tables
> are100% perfect. And I hve never come across a 100% perfect design,
> expecially in a large corporate application
>
> Robert
>
> "evilaro" <buzon@evil.es> wrote in message
> news:zJztacL5FHA.1236@news-server...
> general.
> dbase@NOSPAMsecurity
corp.com
> the
> sure
> fields.
> 30
>
>

Sponsored Links