LS

What's the best way to programmaticly test on startup of an app that:
-all tables are there
-all fields are there
-all fields have the correct length, type and constraints
-all indexes are there
-all blobfiles are there

is there an example somewhere (we dont'own the source)
-dbIsam 3.3  D6

Regards, Harry

Harry


If you don't have the source you'll need a second app to check things out before you start the first app (I'd probably use ShellExecute, but there are other ways of launching apps from within another app).

I don't have an example but two ways suggest themselves

FIRST IDEA
1. Make sure you have a complete, empty copy of all the tables stored in another directory.
2. Create an app with 2 TDBISAMTables on it
3. Use FindFirst/FindNext to generate a list of the .dat files in the known complete directory (or create a list of the tables that should be there)
4. For each table in the known good directory assign the directory to table1's databasename, and its name to the tablename, assign the apps directory to table2's databasename and the same name to tablename (remember to close table1 and table2 first)
5. Check table2's existance using Exists, if it does proceed
6. open and refresh fielddefs and indexdefs for both tables
7. loop round fielddefs in table1 and compare to fielddefs in table2 (as a quick check just compare counts)
8. ditto for index defs
9. if everythings OK shellexecute(app) and close your check app

Sorry no example but this shouldn't take more than 30 mins to set up.


Roy Lambert

"Harry de Boer" <harry@staaf.nl> wrote in message
news:5BAC9E77-C6B5-4F27-84E9-3D757AC687F4@news.elevatesoft.com...
> LS
>
> What's the best way to programmaticly test on startup of an app that:

1. Update user xx version every time you upgrade the table structure.
2. Have your program test version before you attempt to open the tables. You
can do that without opening the table.

I don't think there is a need to set the user version on all tables, just
one of the tables is OK. As to determining that all tables are there, I
guess one could loop thru all the tables and do an if exists.

Robert

Harry


Woops I forgot the second idea. Its similar to the first but rather than keep a copy of the tables you embed the information into your code. Better in that the tables can't be corrupted but more difficult to maintain.

Roy Lambert

Robert,

Is there a *quick* way to change the table version for every table in the db
(in SQL if possible)?

Regards, Harry


"Robert" <ngsemail2005withoutthis@yahoo.com.ar> schreef in bericht
news:16D010D6-931C-486C-8261-CA51092DCF6E@news.elevatesoft.com...
>
> "Harry de Boer" <harry@staaf.nl> wrote in message
> news:5BAC9E77-C6B5-4F27-84E9-3D757AC687F4@news.elevatesoft.com...
> > LS
> >
> > What's the best way to programmaticly test on startup of an app that:
>
> 1. Update user xx version every time you upgrade the table structure.
> 2. Have your program test version before you attempt to open the tables.
You
> can do that without opening the table.
>
> I don't think there is a need to set the user version on all tables, just
> one of the tables is OK. As to determining that all tables are there, I
> guess one could loop thru all the tables and do an if exists.
>
> Robert
>
>
>

"Harry de Boer" <harry@staaf.nl> wrote in message
news:D936816C-A96F-4273-97E8-841CDE5C32AA@news.elevatesoft.com...
> Robert,
>
> Is there a *quick* way to change the table version for every table in the
> db
> (in SQL if possible)?
>

The command is

ALTER TABLE "XXXX.DAT"
USER MAJOR VERSION 400
USER MINOR VERSION 1;

I don't think there is a way to do it for the whole database, I think you
need to fire a command for each table.

In V4, you can get all the database tables in a stringlist, and then just
loop thru the stringlist. I don't think that is available in V3. If you have
tTables defined in a datamodule, you can basically accomplish the same thing
by looping thru all the components in the datamodule and testing for class =
tDBISAMTable. Otherwise, it is a hardocoded list, I'm afraid

maxtables = 3;
const tname : array [1..MaxTables] of string = 'table1, table2, table3';

for j := 1 to maxtables do begin
query.sql.add(
'alter table ' + tname[j] + etc
// you could use this same loop to test for table exists
end;
query.execSQL;

Robert

Harry,

we do it programatically before opening database(s).

We have a separate module in which we have results of Tim's reverse
engineering code for each table. After that for each table the version is
tested and if the anderlaying table has different verion number all
necesssary table restructure actions take place. Here is code snippet if you
need it:

// Tim's Reverse Engineering Code, which we replace each time when we
restructure a table:

   with TableToCreate do begin
     DatabaseName := DB.databaseName;   // DB is a TDBISAMDatabase
component
     sessionName  := DB.sessionName;
     TableName := 'kart';
     Exclusive := True;
     if (not Exists) then begin
       with FieldDefs do begin
         Clear;
         Add('Ident', ftInteger, 0, False, '', '', '', '', fcNoChange, 0);
         Add('Class', ftInteger, 0, False, '', '', '', '', fcNoChange, 0);
         Add('DatDoc', ftDate, 0, False, '', '', '', '', fcNoChange, 0);
         Add('IdProm', ftInteger, 0, False, '', '', '', '', fcNoChange, 0);
         Add('Stran', ftSmallint, 0, False, '', '', '', '', fcNoChange, 0);
         Add('DatPre', ftDate, 0, False, '', '', '', '', fcNoChange, 0);
         Add('DatZap', ftDate, 0, False, '', '', '', '', fcNoChange, 0);
       end;
       with IndexDefs do begin
         Clear;
         Add('', 'Ident;Class;DatDoc;IdProm;Stran', [ixPrimary, ixUnique],
'', icNone);
         Add('promet', 'IdProm', [], '', icNone);
       end;
       CreateTable(1060, 1, 6, False, '',                       // if there
is no such table we add one with highest version number
                  'sample Table', 4096, 512, 0, '',
                   StopWords,spaceChars,includeChars);  // we have
Stopwords and spaceChars and includeChars in separate module
     end;

// end of Tim's Reverse Engineering Code
// here we add our code to restructure each table structure version:

     active := false;
     if (userMajorVersion = 1) and (userMinorVersion = 1) then begin
       FieldDefs.Update;
       IndexDefs.update;                                               //
adding a new field
       FieldDefs.Insert(0,6,'Class',ftInteger,0,false,'','','','',fcNoChange);
       AlterTable(1060, 1, 2, False, '',                         // upgrade
to version 1.2
                  'sample Table', 4096, 512, -1, '',
                   StopWords,spaceChars,includeChars);
     end;
     if (userMajorVersion = 1) and (userMinorVersion = 2) then begin
       FieldDefs.Update;
       IndexDefs.update;
       if FieldDefs.IndexOf('archive') <> -1 then          // this field is
no longer used
         FieldDefs.Delete(FieldDefs.IndexOf('archive'));
       AlterTable(1060, 1, 3, False, '',                          //
upgrade to version 1.3
                  'sample Table', 4096, 512, -1, '',
                   StopWords,spaceChars,includeChars);
     end;
     :::
     :::

// and so on for every table structure version

We found this method very handy because there is not much to program each
time after table restructuring. And the important think to consider is also
that user can restore data (for whatever reason) from an old archive and
with this method he/she has always up to date table structures. There is no
need to have empty reference tables stored in a separate directory - if you
have, than there is a need to update them too.

Regards
Iztok Lajovic

Hi Harry,

I recommend you to take a look at our Database Extensions Suite.
http://www.contextsoft.com/products/ctxdbext/
download trial: http://www.contextsoft.com/products/ctxdbext/downloads.jsp

What you can do with it is:
1. Have database schema stored in your DataModule (dfm) in TDatabaseSchema
component
2. Design it in a very powerful designer, with schema version control
(which allows you to compare or rollback to prior versions)
3. Test if the database is correct version on startup and update
it automatically by executing pre-generated SQL scripts (scripts
are generated by Designer, but you can also generate on run-time)
or
4. Reverse engineer database and compare with your stored schema and
automatically generate SQL ALTER to update it (this approach is not
recommended - you should never execute any SQL without prior
examining it manually).

The suite relies on storing database version number in a single
row "system" table. The scripts to upgrade from any version to any
version is stored in schema in your dfm. Alternatively could be
stored in files (dsd or xml). The startup code, that I use in this
case is:
  if not Database.IsVersionCurrent(True {or newer}) then
    Database.UpdateDatabase;

The example can be found in Demos\EZBooks under DBISAM3 or 4.

Lots of databases supported, including DBISAM 3 & 4.

Regards,
Michael

Harry de Boer wrote:
> LS
>
> What's the best way to programmaticly test on startup of an app that:
> -all tables are there
> -all fields are there
> -all fields have the correct length, type and constraints
> -all indexes are there
> -all blobfiles are there
>
> is there an example somewhere (we dont'own the source)
> -dbIsam 3.3  D6
>
> Regards, Harry
>
>

Harry:

   Keeping a version number on all of the tables is time consuming. What we
did is similar to what Michael Did. We put the versioning into the exe file.
Every time we load a new exe file into the client site, we run our
restructure utility. The restructure utility takes a "snapshot" of the
database that matches the exe - we store the "snapshot into 2 DBISAM files.
When a new update is run, we download the exe, the 2 "snapshot" files, then
we run our restructure utility. This has worked great for maintaining a
customer base of 150+ customers.

   Our next version will be a windows update like version. It will attempt
to download and install new versions at night.


Neil Huhta


"Harry de Boer" <harry@staaf.nl> wrote in message
news:5BAC9E77-C6B5-4F27-84E9-3D757AC687F4@news.elevatesoft.com...
> LS
>
> What's the best way to programmaticly test on startup of an app that:
> -all tables are there
> -all fields are there
> -all fields have the correct length, type and constraints
> -all indexes are there
> -all blobfiles are there
>
> is there an example somewhere (we dont'own the source)
> -dbIsam 3.3  D6
>
> Regards, Harry
>
>

I vote we build up the pressure until Tim adds the
structures and methods into ElevateDB to allow it to
examine the physical structure, compare it to the current
application version's catalog, generate the restructuring
SQL and execute it.

Just think - on connecting to the darabase..

with each table do
begin
 if not Exists then CreateTable
 else if not CompareTable then UpgradeTable;
end;

That might just tempt me to upgrade.