Is there a tool that takes two table structures, compares them, and
generates SQL statements to make the second table structure's identical
to the first?

On a related question, what's the best way to handle updating a client's
DB when you need to restructure some tables, and the DB resides on the
client's machine.  The client is not running a server on his machine
(it's strictly a local app/local DB), so no remote admin access.

DB <DB@invalid.net> wrote on Thu, 03 Aug 2006 19:03:09 -0800

>Is there a tool that takes two table structures, compares them, and
>generates SQL statements to make the second table structure's identical
>to the first?

Not that I know of ...  It has been requested on these ng's but I don't think it exists as part of DBISAM.  However, look at http://www.contextsoft.com/products/ctxdbext/ for a component suite and tools that might do what you want.


>On a related question, what's the best way to handle updating a client's
>DB when you need to restructure some tables, and the DB resides on the
>client's machine. The client is not running a server on his machine
>(it's strictly a local app/local DB), so no remote admin access.
DB


1.  Include in your application a utility program that runs SQL - like a cut down version of DBSYS.  Send the user the SQL to alter table structures as and when required.

2.  Same as (1) above but build it into your main application.  (I have this).

3.  Build in a "Version Up" module into your application that check the version of tables by some means on each program start up and dynamically apply the table changes with SQL.  You can also include Delphi code to populate new fields if the task is beyond SQL (or just too hard!).  (I have this too and is my usual way of doing things.  I use an entry in the Project's Version info for the database version to tell what the program expects and a value in an INI file in the data directory to say what the current db version.  If I was doing it all again I'd use some constants in the program to say what version of each table was expected and compare this with the existing tables Major Version number)

HTH

Jeff
--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

DB <DB@invalid.net> wrote in news:79386218953kje83hj2z38932
@mail.elevatesoft.com:

> Is there a tool that takes two table structures, compares them, and
> generates SQL statements to make the second table structure's identical
> to the first?

Context Database Designer

>
> On a related question, what's the best way to handle updating a
client's DB when you need to restructure some tables

Context Database Extensions Suite

You'll find it here:

http://www.contextsoft.com/products

including a fully working downloadable version that you're expected to
pay for when you reckon it's beginning to earn you some money !

(I've just bought it, and it's EXACTLY what every database programmer
needs)

--
Chris

DB <DB@invalid.net> wrote:

Is there a tool that takes two table structures, compares them, and
generates SQL statements to make the second table structure's identical
to the first?

On a related question, what's the best way to handle updating a client's
DB when you need to restructure some tables, and the DB resides on the
client's machine.  The client is not running a server on his machine
(it's strictly a local app/local DB), so no remote admin access.

>> I have written a component which does exactly what you want. You choose a source folder
and ONE OR MORE TARGET FOLDERS that you wish to compare your source against. The system
then looks for the target folders and performs an sql based table restructure,compare
and/or merge.  It exists as a component which you can drop into any delphi app and call a
compare/restructure each time your application starts. It has full event driven
restructuring options allowing you to display progress, obtain/modify the sql's it
generated and abort. Options also exist for backing existing files before
compare/restructure. I highly recommend you look at it, it has been used in three
commercial products very successfully for this type of use. It is also widely used for
mass source target operations for inhouse datastores when multiple datasets need to be
upgraded with the touch of a button. It can also completely reorganise your field ordering
based on source, target , alphabetic or index optimised/alphabetic.

You will need to contact me at support@bwsoftwaretools.com should you require the source.

Home: www.bwsoftwaretools.com
Download: http://www.bwsoftwaretools.com/f/setup.exe

Kind Regards,

Damien Collings.
BW Software Tools.