Hi Tim,

I have some questions about using update scripts that have been created using reverse engineering.

Say I have Version 1 of my database.  I then produce Version 2.  I reverse engineer Version 2 so that users with a Version 1 database can update it to Version 2 using the generated update script.

Next I produce Version 3 of the database.  Do I create two update scripts: one for Version-1-to-Version-3 and the other for Version-2-to-Version-3?  Or can I just use the Version-1-to-Version-3 script for both?

Or do I generate Version-1-to-Version-2 and Version-2-to-Version-3, and then run them in sequence?

What's the best way to determine if an update script has already been applied?  Or can I just run the latest update script anyway?  Does EDB throw an error if the changes have already been made, or does it just skip them?

Thanks.

= Steve

Steve


I haven't used that myself (I have used the create functionality) so all I can suggest is have a look at the sql produced. From a quick play here I'd say you should be able to produce a 1->2 and a 1->3 script.

A lot will depend on how you apply updates, how you record version and how good your user are. A 1->2 and a 2->3 may be a safer alternative.

Roy Lambert

Hi Steve

Further to Roy's comments I have found it best for my needs to do it on
a sequential chain.

As for detecting the database version I simply use a Table version.  
Rather then versioning each table, I have been able to select one Table
that I am most unlikely to modify, so I bump its version number when
there is any schema change.

As my users commonly restore old backups, I perform a version check
when opening a database.  I first open the 'marker' table and read its
version number.  That tells me whether any udating is needed and if so,
at what point in the chain of updates I need to start.

Malcolm

Steve,

<< Next I produce Version 3 of the database.  Do I create two update scripts: one for Version-1-to-Version-3 and the other for Version-2-to-Version-3?  Or can I just use the Version-1-to-Version-3 script for both?

Or do I generate Version-1-to-Version-2 and Version-2-to-Version-3, and then run them in sequence? >>

You can do 1-3 or 1-2/2-3 sequentially, but you don't want to run a 1-3 script on a version 2 database because it's a static SQL diff, not a dynamic diff.

<< What's the best way to determine if an update script has already been applied?  Or can I just run the latest update script anyway?  Does EDB throw an error if the changes have already been made, or does it just skip them? >>

As long as you don't violate the above rule, you can keep running the same script and it will effectively be a non-op if the script was already applied.  However, I would certainly consider using version numbers on your databases (you can use the database attributes for this) and do a simple check before executing the upgrade scripts.  It will avoid any unnecessary locking or updates to the database catalog.

Tim Young
Elevate Software
www.elevatesoft.com

Thank you Tim, Roy and Malcolm.

= Steve

Steve


I agree with Tim's post but remember - if you apply the script a second time it will crash. If you have a case where one of the update statements will cause a crash but is followed by ones that should be applied you will have a problem.

If you want conditional updates it would be possible by creating the necessary update statements in a script but its a tad more difficult without the IF EXISTS syntax.

Roy Lambert

Roy Lambert wrote:

> I agree with Tim's post but remember - if you apply the script a
> second time it will crash. If you have a case where one of the update
> statements will cause a crash but is followed by ones that should be
> applied you will have a problem.

I'm not too experienced at all on these SQL scripts for updates, but
this makes me think my method of updating databases may be worth
mentioning. I started in DBISAM days, but it works just as well with
EDB. It may not be appropriate for everyone, as it depends on the
ability to have a defined time when only one thing is accessing the
database, but then I expect the scripts are the same there.

Basically, I start with a moment where all databases are at some
defined state - when I have the software working okay in code. As soon
as it is on another machine that will have data I care about, it goes
into the new mode where I never manually do anything with the tables.
Instead, on starting the software, it checks to see if any changed or
added fields are present, and if not, it adds them before it gets
going. It takes only a moment usually, but it ensures that all
instances of the database match the needs of the running software. I
think that if there was a shared environment, you could easily have it
just stop and not do anything more if it couldn't do the upgrade -
forcing the users to close other instances and let it work.

Over time I've done some complex things, like removing or changing
fields and indexes. Of course the harder one is when you have to go
over a table to calculate or set some new field appropriately, but
again you know that has to be done quite easily.

So my code just ends up with lines like:

 CheckFieldPresent(dbChecker, 'CustomerOrder', 'coOrderRef',
                  ftInteger, 0);
 CheckIndexPresent(dbChecker, 'CustomerOrder', 'idxOrderRef',
                  'coOrderRef');

The code for these came from these newsgroups, and I think I posted
something recently, but I could post again if not found. That said,
this is a "start this way" philosophy, so perhaps hard to apply after
the fact.

--

Matthew Jones

Roy,

<< I agree with Tim's post but remember - if you apply the script a second time it will crash. >>

Yes, thank you, I forgot about that and was assuming minimal ALTER statements everywhere.

The caveat here is if there are CREATE DDL statements present in the upgrade script, then you're going to need to be careful.

Tim Young
Elevate Software
www.elevatesoft.com

<< I'm not too experienced at all on these SQL scripts for updates, but
this makes me think my method of updating databases may be worth
mentioning. I started in DBISAM days, but it works just as well with
EDB. It may not be appropriate for everyone, as it depends on the
ability to have a defined time when only one thing is accessing the
database, but then I expect the scripts are the same there.  ......>>

Thanks Matthew, that's a good idea.  I did something similar with DBISAM.  I had an app called DBAnalyser which analysed the differences between two databases and wrote it to an INI file.  The INI file was then distributed with an application along with another app called DBUpdate.  When run, DBUpdate would load the INI file and compare the data structure with the database about to be updated.  It would then make the required changes if needed.  It could also create a database from scratch for new installations.

It's fine for table structures but doesn't really help with changes to stored procedures and functions. My new app is client/server only and it makes extensive use of stored procedures (over 400).

= Steve

<< Yes, thank you, I forgot about that and was assuming minimal ALTER statements everywhere.

The caveat here is if there are CREATE DDL statements present in the upgrade script, then you're going to need to be careful.  >>

Thanks Tim and Roy.  I think I will build database changes into my custom database server and use database versioning as you suggested previously Tim.

= Steve