Hi,

 I've just discovered Context Database Extensions after beginning to add
some onDelete database events to my emerging first-ever application using
DBISAM.

It would seem to be the bee's knees as far as painlessly managing:

- database structure evolution over time, particularly from version x1.y1
to version x2.y2 where all 4 variables can take any intermediate values,
depending on frequency of upgrading for the various users.

- Relational integrity

I need some help, though, in getting my neurones around how to implement
it, if anyone has the time.

I have a beta version (v1.3) out with a client (my daughter, as it
happens, so she won't be my first PAYING client

I have been developing since installing it in her workshop, so the
database has already evolved a bit. (v1.5)

I want to upgrade her database to the new version, so I used ContExt to
reverse_engineer v1.3.

I added the existing relationships, up to now handled by my database
events, and removed my handlers.

I saved the version in ContExt.

Now I want to tell ContExt to pick up v1.5 (added tables, changed fields
and indexes) by reverse-engineering it.

1/ How do I do that without obliterating v1.3? Or do I have to "catch
up" manually?

2/ How do I then set up things so that when opening v1.3 ContExt
automatically upgrades the database to v1.5?

Thanks for any pointers, and in particular, is there a straightforward
tutorial around - the help file is pretty good as far as it goes, but the
juicy bits are still "under construction", unfortunately.

--
Chris

Hi Chris,

I assume you're using 2.x version of extensions & designer.

> Now I want to tell ContExt to pick up v1.5 (added tables, changed fields
> and indexes) by reverse-engineering it.
>
> 1/ How do I do that without obliterating v1.3? Or do I have to "catch
> up" manually?
If you do reverse engineering you will not overwrite 1.3. Did you
create a version checkpoint for 1.3? I presume you did. Each version
checkpoint stores difference between new and prev. version. So your
sequence of actions normally should look like this:
1. Reverse engineer older database
2. Create version checkpoint 1.3 (this will produce create database
script for all table, since this is the first version. You can manually
clean up that script if you want to or leave it as is).
3. Reverse engineer newer database
4. Create version checpoint - it will be 1.4. It will again produce
update script, only this time you will need it to evolve schema.
Both versions (their difference to be precise) will be stored
in schema history. You will be able to compare them or revert to
the older one, etc. (see right mouse menu in Checkpoints window).

If your database already has version 1.5 structure and you don't want
to apply any updates, you will have to "catch up" manually, by
forcing its version to 1.5.
You can do that in SQL console by using setversion command.
setversion 1.5
This will set the version of database to 1.5, so the new update will
no be applied to it, until you have 1.6 and up.
You can do this also at run-time by using SetVersion method of
TDBISAMDatabaseExt.

The version label is stored in system.dat table - this is how
the system knows which version your database is and which updates
should be applied.

> 2/ How do I then set up things so that when opening v1.3 ContExt
> automatically upgrades the database to v1.5?
I assume you need to do that at runtime.
In the designer you can do so manually by using Change\Update
Database structure from Tools menu. At run-time, you should
use the following code when opening the database for the
first time:
  Database.Open;
  // If version is not current (or newer), run update scripts stored in schema
  if not Database.IsVersionCurrent(True) then
    Database.UpdateDatabase;

Your database component (TDBISAMDatabaseExt) should be connected
to TDatabaseSchema, which should contain the latest schema. The
command above will execute all database scripts from current
database version to the latest version in schema.

> Thanks for any pointers, and in particular, is there a straightforward
> tutorial around - the help file is pretty good as far as it goes, but the
> juicy bits are still "under construction", unfortunately.
Unfortunately, yes, we're a bit behind on documentation and tutorials
at this point ;^). But it's getting there. I'm working on finishing help
for the designer and as soon as it's done will work on updating help
for components.

Regards,
Michael

Hi Michael,

Thanks for your full and prompt reply. I'll follow up in-line:

Michael Baytalsky <mike@contextsoft.com> wrote in
news:4958B5B3-0AED-4A4C-9F86-6D6E812E24DE@news.elevatesoft.com:

> Hi Chris,
>
> I assume you're using 2.x version of extensions & designer.

Yes.

> 1. Reverse engineer older database
> 2. Create version checkpoint 1.3 (this will produce create database
> script for all table, since this is the first version. You can
> manually clean up that script if you want to or leave it as is).

I had to change NUMERIC(0) to NUMERIC(0,4) for it to create a new
database from scratch.

Is this where I should use a TDBEngineProfile component?

At this point I added the relationships.

Can I copy-and-paste them to the next reverse-engineered version, or do I
need to start again?

> 3. Reverse engineer newer database

I tried this from the D7 IDE and got an exception after agreeing to
overwrite my current schema definitions, at 400340F47 in module
rtl70.bpl. Read of address 00000008

followed by a blinking catastrophic error box saying:

Access violation at 0043F512 in module CTXDBD~1.EXE. Read of address
00000058

at which point I had to kill CTXDBD~1.EXE

so I tried again using the Database Designer directly, with the same
result.

> 4. Create version checpoint - it will be 1.4. It will again produce
> update script, only this time you will need it to evolve schema.

This is where I was wondering about how to recover the relationships from
v1.3. But first I've got to get past the access violation.

From here onwards, I suppose one should never touch the database
structure outside your Database Designer, otherwise manual intervention
is required to catch up?

> Both versions (their difference to be precise) will be stored
> in schema history. You will be able to compare them or revert to
> the older one, etc. (see right mouse menu in Checkpoints window).
> ...
> ...
> The version label is stored in system.dat table - this is how
> the system knows which version your database is and which updates
> should be applied.
> ...
> ... At run-time, you should
> use the following code when opening the database for the
> first time:
>    Database.Open;
>    // If version is not current (or newer), run update scripts stored
>    in schema
>
> if not Database.IsVersionCurrent(True) then
>      Database.UpdateDatabase;
>
> Your database component (TDBISAMDatabaseExt) should be connected
> to TDatabaseSchema, which should contain the latest schema. The
> command above will execute all database scripts from current
> database version to the latest version in schema.
>

That's the magic bit - it just upgrades whatever is required? And I don't
have to spend hours writing, checking, testing and rewriting all the
different variations from v X1.y1 to v X2.y2 ?

> Unfortunately, yes, we're a bit behind on documentation and tutorials
> at this point ;^). But it's getting there. I'm working on finishing
> help for the designer and as soon as it's done will work on updating
> help for components.

don't forget to announce it here when it's ready.

And what do you have planned for upgrading to ElevateDB? Will it be
straightforward?

> Regards,
> Michael

Thanks. Have a good weekend.
--
Chris

Hi Chris,

From your replies, I see that I'm not sure that I understood
precisely what you're doing. How about e-mailing me your
schema so I can see better what you're trying to accomplish.

I will try answering inline, but some of the things I don't
seem to understand.

>> 1. Reverse engineer older database
>> 2. Create version checkpoint 1.3 (this will produce create database
>> script for all table, since this is the first version. You can
>> manually clean up that script if you want to or leave it as is).
>
> I had to change NUMERIC(0) to NUMERIC(0,4) for it to create a new
> database from scratch.
I don't understand this bit, unfortunately.

> Is this where I should use a TDBEngineProfile component?
Generally, you never need to use that component, unless you're planning
to generate SQL - which is the purpose of that component - on run-time.
It is highly advisable to generate all SQL at design-time, unless
you're trying to write some sort of database management application.

> At this point I added the relationships.
There's no need to restructure database (with DBISAM at least) after
adding RI, because DBISAM 3-4 doesn't support RI, so it works
from schema on client side and no updates to the database structure
is necessary.

> Can I copy-and-paste them to the next reverse-engineered version, or do I
> need to start again?
No, you cannot copy-paste schema objects, only parts of diagram.
Are you saying, that reverse engineering destroyed (override) your RI?
I'll need to test that and let you know.

>> 3. Reverse engineer newer database
>
> I tried this from the D7 IDE and got an exception after agreeing to
> overwrite my current schema definitions, at 400340F47 in module
> rtl70.bpl. Read of address 00000008
I have strong suspicion, that you're using different version of
DBExt in IDE, then the designer. This problem should not happen even
if you change things in IDE, although it's not advisable and I might
prohibit this in future releases.

>> 4. Create version checpoint - it will be 1.4. It will again produce
>> update script, only this time you will need it to evolve schema.
>
> This is where I was wondering about how to recover the relationships from
> v1.3. But first I've got to get past the access violation.
Please, send me your module (pas + dfm) with the schema component and
the dsd file (diagram + schema) located in the same folder.
I shell test it and see why you're getting the AV.

> From here onwards, I suppose one should never touch the database
> structure outside your Database Designer, otherwise manual intervention
> is required to catch up?
No, it really doesn't matter how you change schema, cause you can
only change its current state and not the version history (later
is not accessible from IDE anyway), so the designer will pickup
whatever you changed and compare to the latest checkpoint to
produce update script, i.e. catch up with database.

> That's the magic bit - it just upgrades whatever is required? And I don't
> have to spend hours writing, checking, testing and rewriting all the
> different variations from v X1.y1 to v X2.y2 ?
There's no magic there It stores version number in system table.
When you check the database version against the version of schema it
goes through all Updates and executes the ones with Update.Version >
Database.Version. After each execute it sets Database.Version := Update.Version.
There's no mystery there, except for generating those scripts,
which is done in designer when you create next checkpoint and you
can adjust them manually if you like.

> don't forget to announce it here when it's ready.
I sure won't forget that

> And what do you have planned for upgrading to ElevateDB? Will it be
> straightforward?
It will be totally straightforward. All you need is to change target
database to ElevateDB (once it's out) and it will generate SQL for
all foreign keys, etc.


Regards,
Michael

Michael,

Thanks for trying to drag me into the starting-blocks!

I'll try again, step by step. (Ignoring relationships for the time being)

I have a version of the database in "production" with a beta version of
my program, which manages the rental of violins to families with musical
kids.

That version is unknown to Context Database Extensions.

I have set up a test area on my PC.

Using the Reverse Engineer function of Elevate Software's DBSys I
generate and save a SQL script, from that earlier version of the
database, called newData1.sql.

With that script I create an empty copy of the database in the test
directory Data1.

I repeat the process for the current development version of the database
saving another script called newData2.sql, and an empty copy of the new
version of that database in the test directory Data2.

What I'm trying to obtain is a Schema containing the initial version and
the ability to upgrade it to the current version when I next deploy it to
my beta-tester's site.

I open your Database Designer and Reverse Engineer Data1, create a
Version Checkpoint, and save the result to "Reverse engineer Data1.dsd".

I close Database Designer (since I can't see a "clear everything" choice
in the File menu) and repeat the operation with Data2, saving it to
"Reverse engineer Data2.dsd".

I close Database Designer again and reopen it. I attempt to import the
SQL script newData1.sql, but I get an error:

Syntax error near "AUTOINC" at line 5 in pos 21.

the first part of the script I was trying to import is:
--------------------------------------------------------
/* SQL-92 Table Creation Script with DBISAM Extensions */

CREATE TABLE IF NOT EXISTS "Achats"
(
  "achNum" AUTOINC,
  "achFournisseur" INTEGER NOT NULL,
  "achFacture" VARCHAR(20),
  "achDateFacture" DATE NOT NULL,
  "achmontant HT" DECIMAL(0,4) NOT NULL,
  "achmontantTVA" DECIMAL(0,4),
  "achDateCompta" DATE,
PRIMARY KEY ("achNum") COMPRESS NONE
DESCRIPTION 'Factures d''Achat'
LOCALE CODE 1036
USER MAJOR VERSION 1
USER MINOR VERSION 2
);
--------------------------------------------------------

So I forget about importing DBSys-generated SQL, and reopen the dsd file

"Reverse engineer Data1.dsd".

I double-click on Versions / 1.1, and the first part of the SQL script is

--------------------------------------------------------
-- ## TargetDB: DBISAM4;

CREATE TABLE IF NOT EXISTS "Achats" (
 "achNum" AUTOINC,
 "achFournisseur" INT NOT NULL,
 "achFacture" VARCHAR(20),
 "achDateFacture" DATE NOT NULL,
 "achmontant HT" NUMERIC(0,4) NOT NULL,
 "achmontantTVA" NUMERIC(0,4),
 "achDateCompta" DATE,
 PRIMARY KEY ("achNum")
);
--------------------------------------------------------

You will see I have removed the [..] around "IF NOT EXISTS" etc in your
dbisam4.dpb file, and it was here that I replaced something (but I'm not
exactly sure what, as I didn't save the default version) so that NUMERIC
fields are always forced to NUMERIC(0,4) since by default I was being
given NUMERIC(0) which was rejected on execution.

I now want to add the move up to the new version, so I use the function
"Import Database Schema" and open "Reverse engineer Data1.dsd".

I select all objects, and get a warning that "Achats" already exists. I
click "Yes to all", and am returned to the main window after what seems
to be a successful import.

I right-click on Versions to choose "Create Version Checkpoint...", but
the SQL is not what I expected:
--------------------------------------------------------
-- ## TargetDB: DBISAM4;

ALTER TABLE "Achats"
 DROP COLUMN IF EXISTS "achNum",
 DROP COLUMN IF EXISTS "achFournisseur",
 DROP COLUMN IF EXISTS "achFacture",
 DROP COLUMN IF EXISTS "achDateFacture",
 DROP COLUMN IF EXISTS "achmontant HT",
 DROP COLUMN IF EXISTS "achmontantTVA",
 DROP COLUMN IF EXISTS "achDateCompta",
 ADD COLUMN IF NOT EXISTS "achNum" AUTOINC AT 1,
 ADD COLUMN IF NOT EXISTS "achFournisseur" INT AT 2 NOT NULL,
 ADD COLUMN IF NOT EXISTS "achFacture" VARCHAR(20) AT 3,
 ADD COLUMN IF NOT EXISTS "achDateFacture" DATE AT 4 NOT NULL,
 ADD COLUMN IF NOT EXISTS "achmontant HT" NUMERIC(0,4) AT 5 NOT NULL,
 ADD COLUMN IF NOT EXISTS "achmontantTVA" NUMERIC(0,4) AT 6,
 ADD COLUMN IF NOT EXISTS "achDateCompta" DATE AT 7,
 ADD PRIMARY KEY ("achNum");

 ...etc.
--------------------------------------------------------

I close the Database Designer, reopen it, and load
"Reverse engineer Data1.dsd".
I choose "Tools / Reverse Engineer Database..." and open Data2.

I agree to overwrite everything, and am returned to the main window, but
my Version 1.1 has disappeared. However it is still visible in the
Chekpoints window, bottom left.

So I right-click on Versions and choose "Create Version Checkpoint...",
but the script generated starts by DROP every table, then recreates them,
except the Achats table for wich it generates the following rather
surprising code:

------------------------------------------------------------
RENAME TABLE IF EXISTS "TVATaux" TO "Achats";
ALTER TABLE "Achats"
 DROP COLUMN IF EXISTS "tvtNum",
 DROP COLUMN IF EXISTS "tvtTVA",
 DROP COLUMN IF EXISTS "tvtDateEffet",
 DROP COLUMN IF EXISTS "tvtTaux",
 ADD COLUMN IF NOT EXISTS "achNum" AUTOINC AT 1,
 ADD COLUMN IF NOT EXISTS "achFournisseur" INT AT 2 NOT NULL,
 ADD COLUMN IF NOT EXISTS "achFacture" VARCHAR(20) AT 3,
 ADD COLUMN IF NOT EXISTS "achDateFacture" DATE AT 4 NOT NULL,
 ADD COLUMN IF NOT EXISTS "achmontant HT" NUMERIC(0,4) AT 5 NOT NULL,
 ADD COLUMN IF NOT EXISTS "achmontantTVA" NUMERIC(0,4) AT 6,
 ADD COLUMN IF NOT EXISTS "achDateCompta" DATE AT 7,
 ADD PRIMARY KEY ("achNum");
------------------------------------------------------------


so I cancel, and choose File / Revert to saved.

I now choose Tools / Compare Schemas, and select

Current Schema / Version 1.1 on the left, and
Browse / "Reverse engineer Data2.dsd" / Version 1.1 on the right.

This gives a script which seems fine:

--------------------------------------------------------
-- ## TargetDB: DBISAM4;


ALTER TABLE "Clients"
 REDEFINE PRIMARY KEY ("cliNum");

DROP INDEX IF EXISTS "Clients"."ixClients";

ALTER TABLE "Fabrication"
 REDEFINE PRIMARY KEY ("fabNum");

ALTER TABLE "Instruments"
 REDEFINE PRIMARY KEY ("insNum");

DROP INDEX IF EXISTS "Instruments"."ixInstruments";


DROP INDEX IF EXISTS "Locations"."ixLocDateDebut";

CREATE INDEX IF NOT EXISTS "ixLocDateDebut" ON
"Locations"("locDateDebut");

ALTER TABLE "Reglements"
 REDEFINE PRIMARY KEY ("rglNum");

ALTER TABLE "ReleveBancaire"
 REDEFINE COLUMN IF EXISTS "TRNAMT" NUMERIC(0,4);

ALTER TABLE "TarifsLocation"
 REDEFINE PRIMARY KEY ("tarLocation","tarDateDebut");
--------------------------------------------------------

, but when I click on Import I get the message:

Syntax error near "4" at line 26 in pos 50.

So perhaps my modifications to the dbp file to force NUMERIC(0,4) have
upset the script generation process at this point?

I hope this is clear to you, and look forward to your comments, if you
have time.
--
Chris

Michael,

Thanks for trying to drag me into the starting-blocks!

I'll try again, step by step. (Ignoring relationships for the time being)

I have a version of the database in "production" with a beta version of
my program, which manages the rental of violins to families with musical
kids.

That version is unknown to Context Database Extensions.

I have set up a test area on my PC.

Using the Reverse Engineer function of Elevate Software's DBSys I
generate and save a SQL script, from that earlier version of the
database, called newData1.sql.

With that script I create an empty copy of the database in the test
directory Data1.

I repeat the process for the current development version of the database
saving another script called newData2.sql, and an empty copy of the new
version of that database in the test directory Data2.

What I'm trying to obtain is a Schema containing the initial version and
the ability to upgrade it to the current version when I next deploy it to
my beta-tester's site.

I open your Database Designer and Reverse Engineer Data1, create a
Version Checkpoint, and save the result to "Reverse engineer Data1.dsd".

I close Database Designer (since I can't see a "clear everything" choice
in the File menu) and repeat the operation with Data2, saving it to
"Reverse engineer Data2.dsd".

I close Database Designer again and reopen it. I attempt to import the
SQL script newData1.sql, but I get an error:

Syntax error near "AUTOINC" at line 5 in pos 21.

the first part of the script I was trying to import is:
--------------------------------------------------------
/* SQL-92 Table Creation Script with DBISAM Extensions */

CREATE TABLE IF NOT EXISTS "Achats"
(
  "achNum" AUTOINC,
  "achFournisseur" INTEGER NOT NULL,
  "achFacture" VARCHAR(20),
  "achDateFacture" DATE NOT NULL,
  "achmontant HT" DECIMAL(0,4) NOT NULL,
  "achmontantTVA" DECIMAL(0,4),
  "achDateCompta" DATE,
PRIMARY KEY ("achNum") COMPRESS NONE
DESCRIPTION 'Factures d''Achat'
LOCALE CODE 1036
USER MAJOR VERSION 1
USER MINOR VERSION 2
);
--------------------------------------------------------

So I forget about importing DBSys-generated SQL, and reopen the dsd file

"Reverse engineer Data1.dsd".

I double-click on Versions / 1.1, and the first part of the SQL script is

--------------------------------------------------------
-- ## TargetDB: DBISAM4;

CREATE TABLE IF NOT EXISTS "Achats" (
 "achNum" AUTOINC,
 "achFournisseur" INT NOT NULL,
 "achFacture" VARCHAR(20),
 "achDateFacture" DATE NOT NULL,
 "achmontant HT" NUMERIC(0,4) NOT NULL,
 "achmontantTVA" NUMERIC(0,4),
 "achDateCompta" DATE,
 PRIMARY KEY ("achNum")
);
--------------------------------------------------------

You will see I have removed the [..] around "IF NOT EXISTS" etc in your
dbisam4.dpb file, and it was here that I replaced something (but I'm not
exactly sure what, as I didn't save the default version) so that NUMERIC
fields are always forced to NUMERIC(0,4) since by default I was being
given NUMERIC(0) which was rejected on execution.

I now want to add the move up to the new version, so I use the function
"Import Database Schema" and open "Reverse engineer Data2.dsd".

I select all objects, and get a warning that "Achats" already exists. I
click "Yes to all", and am returned to the main window after what seems
to be a successful import.

I right-click on Versions to choose "Create Version Checkpoint...", but
the SQL is not what I expected:
--------------------------------------------------------
-- ## TargetDB: DBISAM4;

ALTER TABLE "Achats"
 DROP COLUMN IF EXISTS "achNum",
 DROP COLUMN IF EXISTS "achFournisseur",
 DROP COLUMN IF EXISTS "achFacture",
 DROP COLUMN IF EXISTS "achDateFacture",
 DROP COLUMN IF EXISTS "achmontant HT",
 DROP COLUMN IF EXISTS "achmontantTVA",
 DROP COLUMN IF EXISTS "achDateCompta",
 ADD COLUMN IF NOT EXISTS "achNum" AUTOINC AT 1,
 ADD COLUMN IF NOT EXISTS "achFournisseur" INT AT 2 NOT NULL,
 ADD COLUMN IF NOT EXISTS "achFacture" VARCHAR(20) AT 3,
 ADD COLUMN IF NOT EXISTS "achDateFacture" DATE AT 4 NOT NULL,
 ADD COLUMN IF NOT EXISTS "achmontant HT" NUMERIC(0,4) AT 5 NOT NULL,
 ADD COLUMN IF NOT EXISTS "achmontantTVA" NUMERIC(0,4) AT 6,
 ADD COLUMN IF NOT EXISTS "achDateCompta" DATE AT 7,
 ADD PRIMARY KEY ("achNum");

 ...etc.
--------------------------------------------------------

I close the Database Designer, reopen it, and load
"Reverse engineer Data1.dsd".
I choose "Tools / Reverse Engineer Database..." and open Data2.

I agree to overwrite everything, and am returned to the main window, but
my Version 1.1 has disappeared. However it is still visible in the
Chekpoints window, bottom left.

So I right-click on Versions and choose "Create Version Checkpoint...",
but the script generated starts by DROP every table, then recreates them,
except the Achats table for wich it generates the following rather
surprising code:

------------------------------------------------------------
RENAME TABLE IF EXISTS "TVATaux" TO "Achats";
ALTER TABLE "Achats"
 DROP COLUMN IF EXISTS "tvtNum",
 DROP COLUMN IF EXISTS "tvtTVA",
 DROP COLUMN IF EXISTS "tvtDateEffet",
 DROP COLUMN IF EXISTS "tvtTaux",
 ADD COLUMN IF NOT EXISTS "achNum" AUTOINC AT 1,
 ADD COLUMN IF NOT EXISTS "achFournisseur" INT AT 2 NOT NULL,
 ADD COLUMN IF NOT EXISTS "achFacture" VARCHAR(20) AT 3,
 ADD COLUMN IF NOT EXISTS "achDateFacture" DATE AT 4 NOT NULL,
 ADD COLUMN IF NOT EXISTS "achmontant HT" NUMERIC(0,4) AT 5 NOT NULL,
 ADD COLUMN IF NOT EXISTS "achmontantTVA" NUMERIC(0,4) AT 6,
 ADD COLUMN IF NOT EXISTS "achDateCompta" DATE AT 7,
 ADD PRIMARY KEY ("achNum");
------------------------------------------------------------


so I cancel, and choose File / Revert to saved.

I now choose Tools / Compare Schemas, and select

Current Schema / Version 1.1 on the left, and
Browse / "Reverse engineer Data2.dsd" / Version 1.1 on the right.

This gives a script which seems fine:

--------------------------------------------------------
-- ## TargetDB: DBISAM4;


ALTER TABLE "Clients"
 REDEFINE PRIMARY KEY ("cliNum");

DROP INDEX IF EXISTS "Clients"."ixClients";

ALTER TABLE "Fabrication"
 REDEFINE PRIMARY KEY ("fabNum");

ALTER TABLE "Instruments"
 REDEFINE PRIMARY KEY ("insNum");

DROP INDEX IF EXISTS "Instruments"."ixInstruments";


DROP INDEX IF EXISTS "Locations"."ixLocDateDebut";

CREATE INDEX IF NOT EXISTS "ixLocDateDebut" ON
"Locations"("locDateDebut");

ALTER TABLE "Reglements"
 REDEFINE PRIMARY KEY ("rglNum");

ALTER TABLE "ReleveBancaire"
 REDEFINE COLUMN IF EXISTS "TRNAMT" NUMERIC(0,4);

ALTER TABLE "TarifsLocation"
 REDEFINE PRIMARY KEY ("tarLocation","tarDateDebut");
--------------------------------------------------------

, but when I click on Import I get the message:

Syntax error near "4" at line 26 in pos 50.

So perhaps my modifications to the dbp file to force NUMERIC(0,4) have
upset the script generation process at this point?

I hope this is clear to you, and look forward to your comments, if you
have time.
--
Chris

Hi Chris,

Okey, now I understand I'm attaching a corrected version
of dbisam4.dbp file (for DBISAM 4, please copy it over your
version and, please do backup before changing it again .
This version fixes the problem with AUTOINC parsing
and NUMERIC/DECIMAL output (I actually knew about them, but
was under the impression, that I will be releasing the new
update soon, so I didn't issue a quick fix .

I will not comment on your post, instead I will try to explain
how this should be done and why you didn't succeed.

You didn't succeed because importing and overwriting a table
in 2.02 causes the table to be... well overwritten .. so the
generator assumes, that it needs to drop and recreate all fields.
This is not what you intended to do and you should never override
a table when importing from other schema, because it will naturally
result in drop and create (although, this has been changed, so
in the next release it will recreate the whole table, rather then
just individual fields). This behavior is intended currently.
If I decide to change it in future, I will make sure to put it
into Release Notes.

Also, you will not be currently able to import relations into the
schema with DBISAM, because dbisam doesn't have foreign keys defined
in SQL and the only way to import things is by using SQL.

-- step 1: creating initial schema for version 1.3

0. Run the designer from IDE by double clicking on your schema
component.
1. Use the profile attached to import the structure from SQL file
produced by dbsys. I recommend always using SQL import, as opposed
to reverse engineering of physical databases. If you come across any
problems, please e-mail me your sql file - we'll be fighting all issues
with sql import until it works 100% of the time
2. Produce version checkpoint. It will be 1.1 by default, but you can
mark it 1.3 if you like to. Save and close the designer.

-- step 2: creating separate temporary schema for version 1.5

3. Run the designer from outside of IDE, because you need to
operate with another schema (you can also have two instances opened).
(The Designer has SDI interface, so naturally, there's no Close or
Clear option, but there's File/New command instead).
4. Import second SQL file (ver 1.5) into a separate schema and save
it to disk as schema2.dsd. Close the designer.

-- step 3: compare two schemas and produce difference

5. Open the designer from IDE (for our main schema) and go to
Tools\Compare Schemas.
6. Load previously saved schema2.dsd as the destination schema.
Press compare to view differences.
7. Press view SQL, to see the SQL, that alters 1.3 -> 1.5.
Press Import button, to import these changes into your current schema.
Note, that if you select MS SQL 2000 for target database, it
should allow you to import relations if you already have them
in schema2.dsd. Otherwise, you should use DBISAM.

-- step 4: creating 1.5 checkpoint
8. Close Compare dialog. We'll no longer need schema2.dsd file -
it has been used temporarily to see what's changed between versions.
9. Produce new checkpoint 1.5. Save the schema, close the designer. We're
done working with the schema. One thing, that you should always do though,
is running Update SQL against demo databases to ensure that it
actually works. You can do so from the Designer, using Tool/Create Update
Database. You can try creating new database or updating existing
1.3 database. In order to mark existing 1.3 database as having 1.3
version, open SQL Console and execute command:
setversion 1.3

This is only to test that your 1.5 script works. See below on
how to do this at runtime.

-- step 5: making your runtime code recognize database
The database, distributed to your clients, wasn't marked by any version,
so reading its version will return -1.-1. However, it's not empty,
so you don't want to execute your first update script, because it
contains create table statements and should only be executed to
create initial database if necessary. The easiest way to handle this
is to open the update item (double click on that item under Versions in
schema treeview) and clean up all the SQL. This will work fine, assuming
all databases you will ever encounter are either 1.3 or marked, i.e.
you never encounter an empty database.

If you're planning to use this scripts to create databases from scratch,
you should instead mark your existing databases with version 1.3
on startup if it has no version, but is not empty (e.g. contains one
of the tables you can recognize).

For example:

Database.Open;

if (Database.Version.Major < 0) and tblMyTable.Exists then
  Database.Version := SchemaVersion(1, 3);

if not Database.IsVersionCurrent(True) then
  Database.UpdateDatabase;

-----------------------
All of the above may seem more complex, than it actually is once you
get more familiar with the tool. You will only have to do the
importing once, so it shouldn't be a big deal. In the long run it
will save you hours and hours not having to think about what has
changed between versions 1.5 and 2.34 - you will always be able
to see the different in Compare dialog and produce SQL, etc.

I think your example shows, that there's certain lack of import
feature (both in importing and in compare tool) and I will address
it in one of the nearest releases.


Regards,
Michael



Chris Erdal wrote:
> Michael,
>
> Thanks for trying to drag me into the starting-blocks!
>
> I'll try again, step by step. (Ignoring relationships for the time being)
>
> I have a version of the database in "production" with a beta version of
> my program, which manages the rental of violins to families with musical
> kids.
>
> That version is unknown to Context Database Extensions.
>
> I have set up a test area on my PC.
>
> Using the Reverse Engineer function of Elevate Software's DBSys I
> generate and save a SQL script, from that earlier version of the
> database, called newData1.sql.
>
> With that script I create an empty copy of the database in the test
> directory Data1.
>
> I repeat the process for the current development version of the database
> saving another script called newData2.sql, and an empty copy of the new
> version of that database in the test directory Data2.
>
> What I'm trying to obtain is a Schema containing the initial version and
> the ability to upgrade it to the current version when I next deploy it to
> my beta-tester's site.
>
> I open your Database Designer and Reverse Engineer Data1, create a
> Version Checkpoint, and save the result to "Reverse engineer Data1.dsd".
>
> I close Database Designer (since I can't see a "clear everything" choice
> in the File menu) and repeat the operation with Data2, saving it to
> "Reverse engineer Data2.dsd".
>
> I close Database Designer again and reopen it. I attempt to import the
> SQL script newData1.sql, but I get an error:
>
> Syntax error near "AUTOINC" at line 5 in pos 21.
>
> the first part of the script I was trying to import is:
> --------------------------------------------------------
> /* SQL-92 Table Creation Script with DBISAM Extensions */
>
> CREATE TABLE IF NOT EXISTS "Achats"
> (
>    "achNum" AUTOINC,
>    "achFournisseur" INTEGER NOT NULL,
>    "achFacture" VARCHAR(20),
>    "achDateFacture" DATE NOT NULL,
>    "achmontant HT" DECIMAL(0,4) NOT NULL,
>    "achmontantTVA" DECIMAL(0,4),
>    "achDateCompta" DATE,
> PRIMARY KEY ("achNum") COMPRESS NONE
> DESCRIPTION 'Factures d''Achat'
> LOCALE CODE 1036
> USER MAJOR VERSION 1
> USER MINOR VERSION 2
> );
> --------------------------------------------------------
>
> So I forget about importing DBSys-generated SQL, and reopen the dsd file
>
> "Reverse engineer Data1.dsd".
>
> I double-click on Versions / 1.1, and the first part of the SQL script is
>
> --------------------------------------------------------
> -- ## TargetDB: DBISAM4;
>
> CREATE TABLE IF NOT EXISTS "Achats" (
>   "achNum" AUTOINC,
>   "achFournisseur" INT NOT NULL,
>   "achFacture" VARCHAR(20),
>   "achDateFacture" DATE NOT NULL,
>   "achmontant HT" NUMERIC(0,4) NOT NULL,
>   "achmontantTVA" NUMERIC(0,4),
>   "achDateCompta" DATE,
>   PRIMARY KEY ("achNum")
> );
> --------------------------------------------------------
>
> You will see I have removed the [..] around "IF NOT EXISTS" etc in your
> dbisam4.dpb file, and it was here that I replaced something (but I'm not
> exactly sure what, as I didn't save the default version) so that NUMERIC
> fields are always forced to NUMERIC(0,4) since by default I was being
> given NUMERIC(0) which was rejected on execution.
>
> I now want to add the move up to the new version, so I use the function
> "Import Database Schema" and open "Reverse engineer Data1.dsd".
>
> I select all objects, and get a warning that "Achats" already exists. I
> click "Yes to all", and am returned to the main window after what seems
> to be a successful import.
>
> I right-click on Versions to choose "Create Version Checkpoint...", but
> the SQL is not what I expected:
> --------------------------------------------------------
> -- ## TargetDB: DBISAM4;
>
> ALTER TABLE "Achats"
>   DROP COLUMN IF EXISTS "achNum",
>   DROP COLUMN IF EXISTS "achFournisseur",
>   DROP COLUMN IF EXISTS "achFacture",
>   DROP COLUMN IF EXISTS "achDateFacture",
>   DROP COLUMN IF EXISTS "achmontant HT",
>   DROP COLUMN IF EXISTS "achmontantTVA",
>   DROP COLUMN IF EXISTS "achDateCompta",
>   ADD COLUMN IF NOT EXISTS "achNum" AUTOINC AT 1,
>   ADD COLUMN IF NOT EXISTS "achFournisseur" INT AT 2 NOT NULL,
>   ADD COLUMN IF NOT EXISTS "achFacture" VARCHAR(20) AT 3,
>   ADD COLUMN IF NOT EXISTS "achDateFacture" DATE AT 4 NOT NULL,
>   ADD COLUMN IF NOT EXISTS "achmontant HT" NUMERIC(0,4) AT 5 NOT NULL,
>   ADD COLUMN IF NOT EXISTS "achmontantTVA" NUMERIC(0,4) AT 6,
>   ADD COLUMN IF NOT EXISTS "achDateCompta" DATE AT 7,
>   ADD PRIMARY KEY ("achNum");
>
>   ...etc.
> --------------------------------------------------------
>
> I close the Database Designer, reopen it, and load
> "Reverse engineer Data1.dsd".
> I choose "Tools / Reverse Engineer Database..." and open Data2.
>
> I agree to overwrite everything, and am returned to the main window, but
> my Version 1.1 has disappeared. However it is still visible in the
> Chekpoints window, bottom left.
>
> So I right-click on Versions and choose "Create Version Checkpoint...",
> but the script generated starts by DROP every table, then recreates them,
> except the Achats table for wich it generates the following rather
> surprising code:
>
> ------------------------------------------------------------
> RENAME TABLE IF EXISTS "TVATaux" TO "Achats";
> ALTER TABLE "Achats"
>   DROP COLUMN IF EXISTS "tvtNum",
>   DROP COLUMN IF EXISTS "tvtTVA",
>   DROP COLUMN IF EXISTS "tvtDateEffet",
>   DROP COLUMN IF EXISTS "tvtTaux",
>   ADD COLUMN IF NOT EXISTS "achNum" AUTOINC AT 1,
>   ADD COLUMN IF NOT EXISTS "achFournisseur" INT AT 2 NOT NULL,
>   ADD COLUMN IF NOT EXISTS "achFacture" VARCHAR(20) AT 3,
>   ADD COLUMN IF NOT EXISTS "achDateFacture" DATE AT 4 NOT NULL,
>   ADD COLUMN IF NOT EXISTS "achmontant HT" NUMERIC(0,4) AT 5 NOT NULL,
>   ADD COLUMN IF NOT EXISTS "achmontantTVA" NUMERIC(0,4) AT 6,
>   ADD COLUMN IF NOT EXISTS "achDateCompta" DATE AT 7,
>   ADD PRIMARY KEY ("achNum");
> ------------------------------------------------------------
>
>
> so I cancel, and choose File / Revert to saved.
>
> I now choose Tools / Compare Schemas, and select
>
> Current Schema / Version 1.1 on the left, and
> Browse / "Reverse engineer Data2.dsd" / Version 1.1 on the right.
>
> This gives a script which seems fine:
>
> --------------------------------------------------------
> -- ## TargetDB: DBISAM4;
>
>
> ALTER TABLE "Clients"
>   REDEFINE PRIMARY KEY ("cliNum");
>
> DROP INDEX IF EXISTS "Clients"."ixClients";
>
> ALTER TABLE "Fabrication"
>   REDEFINE PRIMARY KEY ("fabNum");
>
> ALTER TABLE "Instruments"
>   REDEFINE PRIMARY KEY ("insNum");
>
> DROP INDEX IF EXISTS "Instruments"."ixInstruments";
>
>
> DROP INDEX IF EXISTS "Locations"."ixLocDateDebut";
>
> CREATE INDEX IF NOT EXISTS "ixLocDateDebut" ON
> "Locations"("locDateDebut");
>
> ALTER TABLE "Reglements"
>   REDEFINE PRIMARY KEY ("rglNum");
>
> ALTER TABLE "ReleveBancaire"
>   REDEFINE COLUMN IF EXISTS "TRNAMT" NUMERIC(0,4);
>
> ALTER TABLE "TarifsLocation"
>   REDEFINE PRIMARY KEY ("tarLocation","tarDateDebut");
> --------------------------------------------------------
>
> , but when I click on Import I get the message:
>
> Syntax error near "4" at line 26 in pos 50.
>
> So perhaps my modifications to the dbp file to force NUMERIC(0,4) have
> upset the script generation process at this point?
>
> I hope this is clear to you, and look forward to your comments, if you
> have time.

Michael Baytalsky <mike@contextsoft.com> wrote in news:5DEC0112-05DC-4ED6-
A61B-EAD4FC13279A@news.elevatesoft.com:

> Okey, now I understand I'm attaching a corrected version
> of dbisam4.dbp file (for DBISAM 4, please copy it over your
> version and, please do backup before changing it again .
> This version fixes the problem with AUTOINC parsing
> and NUMERIC/DECIMAL output (I actually knew about them, but
> was under the impression, that I will be releasing the new
> update soon, so I didn't issue a quick fix .
>
Thanks Michael,

> I will not comment on your post, instead I will try to explain
>

I'll do the same.

Thanks for the new dbisam4.dbp file. I've backed it up (!) and modified it
to create BCD fields for NUMERIC SQL fields, as I need the exact decimals
for accounting purposes:
----------------------------------------------------------------------
datatype.NUMERIC=NUMERIC[(<Precision!=0><Precision>[,<Scale!=><Scale>])]
<fieldtype=BCD>
datatype.DECIMAL=DECIMAL[(<Precision!=0><Precision>[,<Scale!=><Scale>])]
<fieldtype=BCD>
----------------------------------------------------------------------

To simplify things, I'm using version 1.1 as the pre-existing version out
there, and version 1.2 as my development version.

I've done as you said, and now have a schema component with both versions
saved in it on my DataModule. I decided to leave out all relationships and
recreate them on the schema once the SQL is fixed.

I added your code to set the version to 1.1 if not defined:

----------------------------------------------------------------------
 CJRentalDB.Open;
 if (CJRentalDB.Version.Major <= 0) and tblClients.Exists then
    CJRentalDB.Version := SchemaVersion(1, 1);
 if not CJRentalDB.IsVersionCurrent(True) then
 begin
   if FlagUpdatingDB then
     exit;
   FlagUpdatingDB := true;
   try
     CJRentalDB.UpdateDatabase;
   finally
     FlagUpdatingDB := false;
   end;
 end;
----------------------------------------------------------------------

but now I get 2 consecutive errors on 2 consecutive startups.

First, I get "The table System already exists", which leaves the database
in version 1.1 when I close the application.

(Presumably because setting the version number creates the previously
inexistant System table which wasn't there in the version 1.1 script).

Next (after restarting the application) I get "Table or backup file Xxxxx
does not exist" - now this is a table that I have removed in the
development version, and DROP TABLE Xxxxx is the first operation in the SQL
for going from version 1.1 to 1.2:

----------------------------------------------------------------------
-- ## TargetDB: DBISAM4;

DROP TABLE "SophieClients";

CREATE TABLE "System" (
 "SchemaName" VARCHAR(127),
 "ReplicationID" INTEGER,
 "SnapshotID" INTEGER,
 "MinorVersion" INTEGER,
 "MajorVersion" INTEGER,
 "Schema" BLOB,
 PRIMARY KEY ("RecordID") COMPRESS NONE

 LOCALE CODE 1036

 USER MAJOR VERSION 1

 USER MINOR VERSION 2
);
....
----------------------------------------------------------------------

As the system is supposed to know exactly what is where at every stage in
the evolution of the database, I'm loath to activate "IF NOT EXISTS" etc,
so that should something catastrophic occur (e.g. someone removing or
altering a table outside my program), the error is not just ignored.

So how do I add a failsafe mechanism to the UpdateDatabase method?

Should I backup the whole database before launching the UpdateDatabase, and
in the event of an exception revert the schema to the current version and
restore the backed-up database (and send myself an email with a trace file
attached so that I can tell the client what's happened and send him a new
version of the program)?

Or should this be automatically integrated into the UpdateDatabase method?


One last thing - I had added descriptions to the tables such as "Familles
d'articles pour comptabilité" and this gave an error because the apostrophe
broke the text value. You'll need to make this failsafe.

--
Chris

Hi Chris,


> I've done as you said, and now have a schema component with both versions
> saved in it on my DataModule. I decided to leave out all relationships and
> recreate them on the schema once the SQL is fixed.
Just to clarify: only the current schema (1.2) is saved in your data module.
1.1 checkpoint is saved in dsd file together with the diagram, cause you
don't need it at run-time, so no reason to store it in dfm. The updates
collection contains only scripts and not the checkpoint itself.

> I added your code to set the version to 1.1 if not defined:
>
> ----------------------------------------------------------------------
>   CJRentalDB.Open;
>   if (CJRentalDB.Version.Major <= 0) and tblClients.Exists then
>      CJRentalDB.Version := SchemaVersion(1, 1);
>   if not CJRentalDB.IsVersionCurrent(True) then
>   begin
>     if FlagUpdatingDB then
>       exit;
>     FlagUpdatingDB := true;
>     try
>       CJRentalDB.UpdateDatabase;
>     finally
>       FlagUpdatingDB := false;
>     end;
>   end;
> ----------------------------------------------------------------------
>
> but now I get 2 consecutive errors on 2 consecutive startups.
>
> First, I get "The table System already exists", which leaves the database
> in version 1.1 when I close the application.
I'm not sure I understand this problem. At which point this error happens?
Your database update script is not supposed to create, drop or
alter system table in any way. You should delete System table from
schema and make sure it never appears there. System table should be
invisible to schema.

> (Presumably because setting the version number creates the previously
> inexistant System table which wasn't there in the version 1.1 script).
If the table was inexistent, then it should be created successfully and
not raise the above error.

> Next (after restarting the application) I get "Table or backup file Xxxxx
> does not exist" - now this is a table that I have removed in the
> development version, and DROP TABLE Xxxxx is the first operation in the SQL
> for going from version 1.1 to 1.2:
This is a normal conflict where your database does not have expected
structure. If you do the first step correctly then upgrading should go
without errors.

> CREATE TABLE "System" (
This code should never be there in the

>   PRIMARY KEY ("RecordID") COMPRESS NONE
If you're working with DBISAM v.4 make sure you don't have Primary
key defined for RecordID field - this is not supported. Just delete
that index in your schema, it should not be there. This is not related
to System table, because that table should never be created or altered
in script. The reason you have it is because you have probably
imported it using SQL or something.

> As the system is supposed to know exactly what is where at every stage in
> the evolution of the database, I'm loath to activate "IF NOT EXISTS" etc,
> so that should something catastrophic occur (e.g. someone removing or
> altering a table outside my program), the error is not just ignored.
I agree with you on this one. It is possible to activate IF NOT EXISTS
(but don't remove square brackets [], use [<!>IF NOT EXISTS] instead
-<!> sign means, that this "optional" statement will always be produced).
Removing square brackets will break importing function.

> So how do I add a failsafe mechanism to the UpdateDatabase method?
There's not way. The only way to do so is backup your database
before applying update. Generally, DBISAM produces ?BK files for all
updated tables, but I'd still rather backup the database. This is
not physically possible, cause transactional DDL is not supported
(not only by dbisam, but by most engines).

> Should I backup the whole database before launching the UpdateDatabase, and
> in the event of an exception revert the schema to the current version and
> restore the backed-up database (and send myself an email with a trace file
> attached so that I can tell the client what's happened and send him a new
> version of the program)?
That would be a correct approach.

> Or should this be automatically integrated into the UpdateDatabase method?
>
This is not very easy thing to do, alas. Maybe we'll add this in future.

> One last thing - I had added descriptions to the tables such as "Familles
> d'articles pour comptabilité" and this gave an error because the apostrophe
> broke the text value. You'll need to make this failsafe.
This is a bug, I'll see how this can be corrected.

If you still have problems, please, contact me by e-mail and attach your
dsd file.

Regards,
Michael

Michael Baytalsky <mike@contextsoft.com> wrote in
news:3BDDB5D6-060D-4444-8A6B-D01816932A15@news.elevatesoft.com:

> I'm not sure I understand this problem. At which point this error
> happens? Your database update script is not supposed to create, drop
> or alter system table in any way. You should delete System table from
> schema and make sure it never appears there. System table should be
> invisible to schema.
> ...
>> CREATE TABLE "System" (
> This code should never be there
> ...
> . This is not related
> to System table, because that table should never be created or altered
> in script. The reason you have it is because you have probably
> imported it using SQL or something.

I understand now. I created my first two scripts with DBSys from the
databases I had in the two places - my archive copy of the working
version "out there" (v1.1) and my current development version (v1.2)
after attempting to add Database Extensions.

So of course DBSys saw the System table in the development version as it
had already been created by ContExt, but if I had never attempted to use
Database Extensions it wouldn't have been.

> (but don't remove square brackets [], use [<!>IF NOT EXISTS] instead
> -<!> sign means, that this "optional" statement will always be
> produced). Removing square brackets will break importing function.
>

I hadn't found any in-depth explanation of how to modify the dbp file -
thanks for that info.

>> Or should this be automatically integrated into the UpdateDatabase
>> method?
> This is not very easy thing to do, alas. Maybe we'll add this in
> future.

I don't expect it'll be possible - you should simply suggest a BEST WAY
of doing it in your help file, I think.

> If you still have problems, please, contact me by e-mail and attach
> your dsd file.

Thanks very much for your detailed and very prompt explanations. I feel
much happier about ContExt now, especially as I also see from yesterday's
thread about "DisplayName for SQL columns" in the dbisam newsgroup that
another problem I was just starting to address is automatically handled
by this great addon!

--
Chris