Hi!
When I distribute big updates of my programs the clients has an option of
keeping their existing database. In these cases only the program files get
installed. I then need to make sure that the existing database is compatible
with the existing program version.
Today I use a special program for this. This program is automatically run
after the installation, and it is executing a series og hard coded SQL calls
which is checking every field in every table in the database and. if
neccessary it is doing an ALTER TABLE / REDEFINE command.

I would like to make the update program a bit more flexible. In stead of
doing hard coded SQL calls I would like it to load and execute SQL files
that I supply. Do I need one SQL file for each table or is it possible to do
the process on all tables from one big SQL file?

I also understand that the ALTER TABLE / REDEFINE command does nothing if
the table already has the correct format. Can somebody confirm this?

Kind regards
TorB.

TorB. wrote:
....
> I would like to make the update program a bit more flexible. In stead
> of doing hard coded SQL calls I would like it to load and execute SQL
> files that I supply. Do I need one SQL file for each table or is it
> possible to do the process on all tables from one big SQL file?

ne way to do so would be DB Extensions. The Schema component has versioning
capability, so you can define what SQL statement must be executed to go from
2.01 to 2.02. I use that very successful in a huge system.

Another way I use for an older application: I have all SQL statements to
update eg. the Article table from 1.0 to 1.1 in another table, called
Updates. The columns: Table name, Minor version, Major version, SQL
statement, BLOB value (if I have to insert BLOBs), description. That table
is compressed and streamed into a file by a small utility I wrote. That
update file is distributed with each new version of the application, At
startup, the application looks for the file. If it exists, the application
loads and uncompress it, streams it in a table and compares the version
numbers for each record. If the record has a higher version number than the
existing table, the SQL statement is executed. After that, the user gets a
message if something went wrong, and the update file is renamed. Works since
years without problems.

Ralf

Tor,

we are using a bit different approach as Ralf does. Regarding possibility
that the user wants to load data from archives with different creation date
(in our case that could be a year or more) which could possibly contain data
tables with different major and minor version numbers we have to dinamically
convert table structure from any version number to today's version number.

Each of our applications has a module with structure definition of all
tables which are used by application. After restructuring particular table
we use DBSYS's Reverse Engineer Table function and put in this module the
latest structure of a table. After that we add manually code for description
of structure difference from previous table version and at apply the
difference with AlterTable function.

For example:

   with TableToCreate do begin
     DatabaseName := baza.databaseName;  // 'baza' is function parameter,
ie actual DBISAMDatabase
     sessionName  := baza.sessionName;
     TableName := 'trans';
     Exclusive := True;
     if (not Exists) then begin
       with FieldDefs do begin
         Clear;
         Add('Ident', ftAutoInc, 0, False, '', '', '', '', fcNoChange, 0);
         Add('Logic', ftInteger, 0, False, '0', '', '', '', fcNoChange, 0);
         ::::::                                                  // more
than 40 fields are inserted here
         Add('OznArh', ftString, 2, False, '', '', '', '', fcNoChange, 0);
       end;
       with IndexDefs do begin
         Clear;
         Add('', 'Ident', [ixPrimary, ixUnique], '', icNone);
         Add('logic', 'Logic;Ident', [], '', icNone);
       end;
       CreateTable(1060, 1, 11, False, '', 'transactions', 4096, 512, 0,
'',
                   StopWords,spaceChars,includeChars);  // we have
StopWords etc. defined separately
     end;
     active := false;
// 1. structure change
     FieldDefs.Update;
     IndexDefs.update;
     if (userMajorVersion = 1) and (userMinorVersion = 0) then begin
       FieldDefs.insert(28,37,'Param',ftInteger,0,False,'0','','','',fcNoChange);
       AlterTable(1060, 1, 1, False, '', 'transactions', 4096, 512, -1, '',
                   StopWords,spaceChars,includeChars);
       FieldDefs.Update;
       IndexDefs.update;
     end;

// 2. structure change
      ::::::      // for each table structure change here should be
inserted code between 1. and 2. structure change
                   // with appropriate code for concrete structure change


When new user starts application for first time, the table is created, at
subsequent runs only version numbers are tested: if user reloaded data from
time before particular table structure change, the above code is applied and
table structure is updated to today's structure. If there are more than one
structure changes all of them will applied subsequently.

HTH

Iztok Lajovic

Hi Ralf!
Thank you for your feedback, it sounds interesting. I'll look into this.
Kind regards
TorB.

****

"Ralf Mimoun" <nospam@rad-on.de> skrev i melding
news:C77F357B-3054-4E07-A2C3-D18B12620DF7@news.elevatesoft.com...
> TorB. wrote:
> ...
>> I would like to make the update program a bit more flexible. In stead
>> of doing hard coded SQL calls I would like it to load and execute SQL
>> files that I supply. Do I need one SQL file for each table or is it
>> possible to do the process on all tables from one big SQL file?
>
> ne way to do so would be DB Extensions. The Schema component has
> versioning capability, so you can define what SQL statement must be
> executed to go from 2.01 to 2.02. I use that very successful in a huge
> system.
>
> Another way I use for an older application: I have all SQL statements to
> update eg. the Article table from 1.0 to 1.1 in another table, called
> Updates. The columns: Table name, Minor version, Major version, SQL
> statement, BLOB value (if I have to insert BLOBs), description. That table
> is compressed and streamed into a file by a small utility I wrote. That
> update file is distributed with each new version of the application, At
> startup, the application looks for the file. If it exists, the application
> loads and uncompress it, streams it in a table and compares the version
> numbers for each record. If the record has a higher version number than
> the existing table, the SQL statement is executed. After that, the user
> gets a message if something went wrong, and the update file is renamed.
> Works since years without problems.
>
> Ralf

Hi Iztok!
Thank you for your feedback.
My point is that I do not want to hard code the ALTER TABLE process.
I want to supply SQL files that are loaded and then executed.
That gives me a possibility to only supply the SQL files in some cases.
Kind regards
TorB.

*****

"Iztok Lajovic" <iztok.lajovic@amis.net> skrev i melding
news:0ABE4EAB-D03B-4B27-A72C-15BCF02C6A03@news.elevatesoft.com...
> Tor,
>
> we are using a bit different approach as Ralf does. Regarding possibility
> that the user wants to load data from archives with different creation
> date (in our case that could be a year or more) which could possibly
> contain data tables with different major and minor version numbers we have
> to dinamically convert table structure from any version number to today's
> version number.
>
> Each of our applications has a module with structure definition of all
> tables which are used by application. After restructuring particular table
> we use DBSYS's Reverse Engineer Table function and put in this module the
> latest structure of a table. After that we add manually code for
> description of structure difference from previous table version and at
> apply the difference with AlterTable function.
>
> For example:
>
>    with TableToCreate do begin
>      DatabaseName := baza.databaseName;  // 'baza' is function parameter,
> ie actual DBISAMDatabase
>      sessionName  := baza.sessionName;
>      TableName := 'trans';
>      Exclusive := True;
>      if (not Exists) then begin
>        with FieldDefs do begin
>          Clear;
>          Add('Ident', ftAutoInc, 0, False, '', '', '', '', fcNoChange, 0);
>          Add('Logic', ftInteger, 0, False, '0', '', '', '', fcNoChange,
> 0);
>          ::::::                                                  // more
> than 40 fields are inserted here
>          Add('OznArh', ftString, 2, False, '', '', '', '', fcNoChange, 0);
>        end;
>        with IndexDefs do begin
>          Clear;
>          Add('', 'Ident', [ixPrimary, ixUnique], '', icNone);
>          Add('logic', 'Logic;Ident', [], '', icNone);
>        end;
>        CreateTable(1060, 1, 11, False, '', 'transactions', 4096, 512, 0,
> '',
>                    StopWords,spaceChars,includeChars);  // we have
> StopWords etc. defined separately
>      end;
>      active := false;
> // 1. structure change
>      FieldDefs.Update;
>      IndexDefs.update;
>      if (userMajorVersion = 1) and (userMinorVersion = 0) then begin
>
> FieldDefs.insert(28,37,'Param',ftInteger,0,False,'0','','','',fcNoChange);
>        AlterTable(1060, 1, 1, False, '', 'transactions', 4096, 512, -1,
> '',
>                    StopWords,spaceChars,includeChars);
>        FieldDefs.Update;
>        IndexDefs.update;
>      end;
>
> // 2. structure change
>       ::::::      // for each table structure change here should be
> inserted code between 1. and 2. structure change
>                    // with appropriate code for concrete structure change
>
>
> When new user starts application for first time, the table is created, at
> subsequent runs only version numbers are tested: if user reloaded data
> from time before particular table structure change, the above code is
> applied and table structure is updated to today's structure. If there are
> more than one structure changes all of them will applied subsequently.
>
> HTH
>
> Iztok Lajovic
>
>
>