Hi Malcolm,

(Don't want to force you, but you should really try our
extensions (see links in the post above).
The advantages are:
1. It already works almost the same way you describe
2. It works with current version of dbisam and will work with
the new one. So you don't have to wait.
3. It works with other databases as well.
4. You don't need to bother Tim with unnecessary features

Also, the method you described (below) is not the best way,
*especially* (!!!) with databases like EDB (will be) - i.e.
with server side referential integrity.

While it is possible in dbisam3\4 to redefine tables individually,
it will be impossible in EDB because of added RI features.

It is very important to understand, that if you have RI
on server you cannot upgrade tables one by one - you MUST
do it in a specific order and sometimes the operations
should be mixed. Example:
1. Drop constraint on table T1
2. Redefine fields in referred table T2
3. Recreate constraint on table T1.

This cannot be achieved the way you describe and cannot always
be achieved by automatically generated scripts - because this
sequence can get too complicated with real RI in place (trust
me, I worked for years on this .

Also, you don't want to execute any modifying SQL on your working
system without prior examining it by hands. One little mistake -
and you just wiped out some real data. And mind you, DDL is not
transactional, so no undo, just restore from backup if you are
lucky to notice the problem before entering more data...

Regards,
Michael


Malcolm wrote:
> 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.  
>

Michael

>While it is possible in dbisam3\4 to redefine tables individually,
>it will be impossible in EDB because of added RI features.

That is a mega downside as far as I'm concerned. It also means that repairing damaged tables will be bloody difficult. Even when I was using Paradox I stoped using its built in RI because it just made things so difficult.

What's needed is a simple switch to say turn off RI whilst I sort this mess out!

Roy Lambert

Hi Michael

I know it can get tricky, especially in EDB.  I also know
that this topic is a regular in these ngs.

That is why I think an expert like you or Tim should do the
job properly and save the rest of us from re-inventing this
wheel .. and making it square in the attempt!  

My present system will *not* work in EDB .. but I am not
sure I need the nice new EDB features for my current crop
of freeware applications.

Malcolm

> That is why I think an expert like you or Tim should do the
> job properly and save the rest of us from re-inventing this
> wheel .. and making it square in the attempt!  
That's exactly what we did in database extensions & designer


Malcolm

> That is a mega downside as far as I'm concerned. It also means that repairing
> damaged tables will be bloody difficult. Even when I was using Paradox I
> stoped using its built in RI because it just made things so difficult.
Hopefully, this will mean you won't have to repair damaged tables
or especially damaged RI - because the database maintains it for you.

> What's needed is a simple switch to say turn off RI whilst I sort this mess out!
That switch is called alter table X drop constraint Y

IMO, you should sort the mess out before migrating to RI database.
With RI, you will finally have means to design you database so there
will be no mess. Also, Paradox, never really had RI that it could
enforce. The only way you can have RI is if you have real C\S
database with rock solid transactions. In most such databases, if you
corrupt it, the only way to fix it is to restore from backup - you
will never have to repair anything manually. Also, forget about
copying individual tables back and forth. The database is a whole and
must be considered as such when you design it.

As an example of what I'm saying, consider Paradox or DBF indexes,
which we had to rebuild every so often and everyone has been writing
tools to rebuild them automatically. Once I switched to DBISAM, I
never ever had to rebuild an index, so that approach and concern
lost its value. Same true for corrupt tables and broken RI and
manually sorting the mess. In a good database server - that problem
just never occurs and if it does, you just know to restore from
backup. I never heard of anyone trying to repair corrupt Oracle
Interbase or MS SQL database - I wouldn't even know how to approach
such task (probably cracks of some sort exist, I'm not sure).


Regards,
Michael

Malcolm,

<< I know it can get tricky, especially in EDB.  I also know that this topic
is a regular in these ngs.

That is why I think an expert like you or Tim should do the job properly
and save the rest of us from re-inventing this wheel .. and making it square
in the attempt!   >>

It's not that simple in cases where the beginning and ending catalogs for a
database reflect N iterations of alterations to various catalog objects over
a period of time.  IOW, when it comes to catalog metadata, you can't always
get from point A to point B without repeating the steps entirely as they
were executed.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Harry de Boer" <harry@staaf.nl> 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

How we do it is like this ...
1. Maintain an ASCII file (data dictionary) that records the name, field defs, and indexdefs for every table. Fairly easy to write a program that extracts a data dictionary.
2. Have a routine that scans the ASCII file and restructures tables to match
Thus we loop through the list of tables in the dictionary, not the list of tables on disk.