Hi,

As a bit of a background, in my existing DBISam databases, I have a form
where I have a TDBISamTable component for every table I have in the
database, which is loaded with persistent fields. I also have a DBVer
variable stored within a 'setup' table to say what is the latest version of
the 'database'.

When my application loads at my clients end, it compares the version of the
DBVer variable/value stored in my setup table along with one I have
hardcoded into my software, and if they do not match, it runs a procedure
that checks for new fields in the datastructure, and alters the existing
tables as required.

I've had it pretty easy with the way I've designed my app with DBISam. All I
had to do was make sure that I had the TTable components on the form loaded
with all the required persistent fields at designtime, and regardless of
what previous version my clients had of the program, the required fields
were added if they upgraded to match the latest database requirements.

When this procedure runs it does as follows:

a) Checks to see if TTable.Exists is true. If not, it calls the
TTable.Create command to create the new table...

b) Scans through all TTable fields. If there is a persistent field within
one of the TTable components on the form that is not in the table stored on
the disk, it goes ahead and adds the field to the table on the disk.

In doing this with DBISam, I didn't have to keep a good track on things. If
the field didn't exist - it was created for me automatically. I just had to
make sure the TTable components on this one form were loaded with all the
persistent fields before building and distributing my app.

However, writing a new application in EDB, I'm wondering what would be the
correct method to address the above situation? Should I just try to
replicate what I did in DBISam, or is there a better method of doing this.
(ie, Is there some sort of catalogue I can store within my application that
is automatically checked with the catalogue of the database, and updated
respectively?), or what are some recommendations as to how I should handle
this situation?

Thanks & Regards

Adam.

No replies? Have I stumped you'z all?  (Or did I explain poorly, which
is more probabal .

Just wondering what is the best practise when one needs to distribute and
update to their application regulary to multiple end clients that contains
new update fields. (So after 30 updates, various clients might be on version
1 through version 29 of the app, and their database needs to be updated with
all the new fields in version 30)?

Is the standard practise to have a unit full of "if field not exists alter
table" commands, or is there something a little more streamlined?

Cheers

Adam.

Adam,

> No replies? Have I stumped you'z all?

Not stumped, but I'm in the same boat as you are and was waiting to see if
anyone else came up with a drop in solution.  

I'm trying to decide if I'm going to tackle this with my own code or try to
bastardize something like the Context Database Extensions (CDE) components
which already does a good portion of what I need.   My problem is that in
addition to the standard add a field, change the field size, etc, I need
something that will also handle the following:

1. The ability to standardize field sizes across the entire schema.  (CDE
does this well with domains).

2. Allow for the end user to override the standardized field sizes, but not
allow them to decrease the size below the default value that I define in the
application.

3. Allow for the end user to add and delete custom fields within the tables,
but prevent them from deleting fields that they did not create.

4. Allow for the merging of schemas to include additional tables for add-on
products.

5. Allow for dynamically generated triggers based upon the structure of the
table when it changes.

6. Only touch the tables or indexes that need modification.  I've looked at
the code within EDB to compare schemas and generate a update script, but it
drops every index, every trigger, etc, restructures the tables, and then
adds everything back in.  Rather not do all of that work when there are
tables with potentially millions of rows just to change the length of a
non-indexed field from 10 to 12.

I had originally tried to implement what I had for DBISAM, but got hung up
on the fact that there is now RI, triggers, and other related things that
make simple structure changes much more challenging.

It's getting down to crunch time for me to find something that will handle
these requirements or to start designing my own management class.


--

---------------------------------------
 Terry Swiers
 Millennium Software, Inc.
 http://www.1000years.com
 http://www.atrex.com

 Atrex Inventory Control/POS -
    Big business features without spending big business bucks!

Atrex Electronic Support Options:
 Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
 Email: mailto:support@atrex.com
 Newsgroup: news://news.1000years.com/millennium.atrex
 Fax: 1-925-829-1851
 Phone: 1-925-828-5892 (M-F, 9a-5p Pacific)
 ---------------------------------------

Adam,

I do updates differently.  I am interested to know how other people do this.

What I do is along the lines of:

1.  The database updates are script files which are named "Updatenmm.sql" where n is the
major version number and mm is the minor version number.  The installation copies these
files into the designated directory.
2. In the After Connect event for the database, it places each of the SQL file names in
the SQL install directory into a sorted TStringList and executes the script file if it has
not previously been executed.
3. If the script executes successfully, the program writes the date/time into the
[Installation History] section of an INI file for the each of the file.
For example,
[Installation History]
Update229.sql=18/05/07 15:22:16
Update230.sql=03/12/07 10:00:05
Update230b.sql=24/01/08 08:20:13
Update231.sql=25/07/08 18:52:05
Update232.sql=05/07/09 17:29:16
Update232b.sql=05/07/09 17:29:16


Seems to work.

Richard Harding

Adam,

I have put a small project in ..elevatedb.extensions group to show how I do
reverse engineering and all subsequent actions for automatic table upgrade
in ElevateDB. Maybe you can find some ideas there that you can use in your
case.

Regards
Iztok Lajovic




"Adam H." <ahairsub5@jvxp_removeme.com> je napisal v sporocilo
news:5DE8CD68-FAEA-4F2C-815F-1ABC85AA732A@news.elevatesoft.com ...
> No replies? Have I stumped you'z all?  (Or did I explain poorly, which
> is more probabal .
>
> Just wondering what is the best practise when one needs to distribute and
> update to their application regulary to multiple end clients that contains
> new update fields. (So after 30 updates, various clients might be on
> version 1 through version 29 of the app, and their database needs to be
> updated with all the new fields in version 30)?
>
> Is the standard practise to have a unit full of "if field not exists alter
> table" commands, or is there something a little more streamlined?
>
> Cheers
>
> Adam.

For my DBISAM based applications, I run a program before a doing a release that analyzes all of the tables in a database and creates an INI file that contains
definitions for all of the tables, field names, indexes, etc.  When the customer installs the new release a program reads the INI file, compares it with the customer's
database and then modifies the database structure accordingly.  It's worked perfectly for years and many releases, however, I suspect I'm going to have to heavily
modify it to work with ElevateDB.

Regards,

Steve

Hi Richard and Iztok,

Thanks for your replies...

At this stage I'm looking for something a little more 'automated' than
multiple update SQL files. (In my existing app, all I needed to do was make
sure that the TDBISamTable components on a particular form had all the
possible fields loaded as persistent fields, and my application worked out
the rest. (created table if it didn't exist, and add any fields that were
missing).

Iztok, thanks for your example. I'll take a look at this and see what I can
do with it.

Best Regards

Adam.

Hi Adam,

I use the Context Database Designer for that stuff. You design the tables, indexes,
triggers etc. in the Database Designer and you can then create Changes SQL Scripts. The
Database Designer inserts one specific SysTable, that holds the version number of the
database, as you did in your DBIsam apps. This field will be compared with the
requirements of your application exe and all needed update steps will be processed.

Works perfect in my applications.

Greets
Michael

Adam,

sorry forget to give the link to Database Designer:

http://www.contextsoft.com

Michael

Can Tim please respond to this thread? Certainly in the near future there will be many db updates as the application is deployed, and I would like his input re: deploying new application versions and updating the already installed database. We can not guarantee the current version being used, for what ever reason, so generating scripts for each update is not as useful.

Tim: your advice or suggestions on how best to handle this scenario would be much appreciated.

Thanks
Aaron