What is the recommended way to upgrade an EDB database structure? Things like adding new fields and modifying indexes.

A long time ago, when I was using a FoxPro engine, I used to create an empty copy of the database, copy each record from the current database to the new database, and then when that's done, copy the new database over the top of the current database. I used to send out a collection of table definition files (FoxPro doesn't have SQL, so I created my own format), one for each table, and tell the customer to "rebuild" the database. It was very simple and easy.

When I moved on to DBISAM, I continued with the same process, but this time I would send out a collection of SQL files for the new database structure.

Now, with EDB, the situation is different. Things are a bit more complicated. I can probably keep doing what I have been doing for all these years, but because EDB is more comprehensive than my FoxPro engine or DBISAM, I was wondering if there was another quick and easy way to upgrade the structure.

I'm not using a server, just single user and shared.

Richard,

There is no need for such a complicated way, i think.
I use SQL DDL statements, called with TEDBDatabase.Execute(), TEDBScrips along with a set of auxiliary functions I wrote to test for the existence of tables, indexes and other db objects or to check the current version.

--
Fernando Dias
[Team Elevate]

Richard


I do pretty much the same as Fernando. Write and debug the sql (either query or script) in EDBManager, then encrypt it (nothing sophisticated - just enough to mean some "enquiring mind" can't mess things up) distribute and its run through a utility in the app.

With scripts you can do sophisticated things testing version numbers, existence of columns etc but I chose a simple route - the encrypted files are given a name of the date generated in yyyy-mm-dd.sua format and people are just told to run them in sequence. One day I'll get round to doing it properly.


Roy Lambert [Team Elevate]

Richard,

You can also use the EDB Manager to reverse-engineer the
database...then in the "General" tab select the "Upgrade" radio button.
It will ask you for the specific database and it will create the SQL
statements for you.

--
Regards,
Jan Ferguson [Team Elevate]


Richard wrote:

> What is the recommended way to upgrade an EDB database structure?
> Things like adding new fields and modifying indexes.

Richard,

I have an "old" update strategy a bit like yours, which has grown up over the years, but has some useful features.

One of the things I do is to keep an DatabaseUpdates table in my Configuration database. All my databases are constructed with a Main database and a Configuration database. This is a hang-over from my own DBISAM days & EDB makes it a bit more complicated as it now has its own Configuration database!

However, I keep ALL update scripts in DatabaseUpdates & I only run update statements using a script which calls the SQLScript field of this table. I don't just write a statement & then run it & perhaps lose it.

The plus about this method is simply that you have a rock-solid record of any change you have made, which makes it easier to track what was changed when & how to unravel it if the changes cause problems.

A second thing I do is to have a Programming version of the database on which I run the changes first & then a batch of tests which I can run on this database after the test to check nothing is broken before I apply the change script to the real db. I guess that is very standard/basic good practice ...

--

I have spent a lot of time writing update & change scripts & I am amazed to hear that EDBManager can write them for me! I have had a brief look at this seeing Jan's post & will definitely be exploring this potentially brilliant hidden feature of EDBMgr!

>Adam Brett wrote:
>
>Richard,
>
>I have an "old" update strategy a bit like yours, which has grown up over the years, but has some useful features.

I have now implemeted my regular FoxPro/DBISAM update scheme for ElevateDB and it works beautifully (I love the "UseLocalSessionEngineSettings" property). I have one script to create my empty database, then I copy the records over to the new database. When that's done, I copy the new database into my database directory. I just needed to issue and ALTER DATABASE statement to change the path of the database files.

This scheme has the added advantage of being able to be run at any time. I don't need to test if tables, fields or indexes exist, because I am recreating everything from scratch. Fortunately TEDBTable and TDBISAMTable are similar enough that I didn't have to change hardly anything.

> Richard
>
>
> I do pretty much the same as Fernando. Write and debug the sql (either query or script) in EDBManager, then encrypt it (nothing sophisticated - just enough to mean some "enquiring mind" can't mess things up) distribute and its run through a utility in the app.
>
> With scripts you can do sophisticated things testing version numbers, existence of columns etc but I chose a simple route - the encrypted files are given a name of the date generated in yyyy-mm-dd.sua format and people are just told to run them in sequence. One day I'll get round to doing it properly.
>
>
> Roy Lambert [Team Elevate]
>

Hi Roy,

I'm wanting to know an efficient way to upgrade my users database
structure each time they get a new upgrade installation exe...

Currently I have the version of the app being stored in registry from
the previous version, also an update.sql file is placed into a certain
folder during the installation of the upgrade, when the program is run,
it checks the registry version with its own version and if difference it
looks for the update.sql script (which it deletes after a successful run)...

That works OK, but my SCRIPT knowledge is no where...

Currently I'm putting BEING / EXCEPTION blocks for each line, so that if
the version already has the change / field then it wont fall over...I'm
sure that's not the best way, but I'm sure you can point me in the right
direction... I was thinking I could make use of the stored version
number in registry and jump to a certain section in the script based on
the version number, dunno if it's worth the hassle or not, but either
way I'm sure the script could be improved. (see example below)

Cheers,

Phil.

SCRIPT
BEGIN

BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE "master" ADD COLUMN "test1field" BOOLEAN
DEFAULT false';
EXCEPTION
SET STATUS MESSAGE TO 'test1field already excists"';
END;

BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE "master" ADD COLUMN "test2field" BOOLEAN
DEFAULT false';
EXCEPTION
SET STATUS MESSAGE TO 'test1field already excists"';
END;

BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE "master" ADD COLUMN "test3field" BOOLEAN
DEFAULT false';
EXCEPTION
SET STATUS MESSAGE TO 'test1field already excists"';
END;

END

Phil

>I'm wanting to know an efficient way to upgrade my users database
>structure each time they get a new upgrade installation exe...
>
>Currently I have the version of the app being stored in registry from
>the previous version, also an update.sql file is placed into a certain
>folder during the installation of the upgrade, when the program is run,
>it checks the registry version with its own version and if difference it
>looks for the update.sql script (which it deletes after a successful run)...

<RANT ON>
I know it works BUT I hate storing things in the registry. In my opinion its the cause of more problems on a PC than anything else (apart from the users of course) and much prefer a small text file in the same directory as the executable.
</RANT MODE>

>That works OK, but my SCRIPT knowledge is no where...

When I look at some of the things posted here I feel much the same so you've got lots of company. Also, like yourself, I miss the IF EXISTS syntax.

You've chosen one of the valid approaches to table alteration. EXCEPTION blocks work. I don't think they're particularly nice and tend to use the alternative which is to query the catalog / configuration tables. It sort of gets back to the IF EXISTS approach. You could use it in one of two ways - as you're doing with the EXCEPTION block or to build an SQL statement which you then execute.

This example posted by David Cornelius shows how to do it for tables and could easily be adapted for columns

http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_extensions&msg=38&page=1

Since its not something I do every day, and I operate on a get it working and forget it basis, I'll have to wade through and find a proper example for you. It may take a couple of days.

Roy Lambert [Team Elevate]

Phil


First create this function in the database you're targeting


CREATE FUNCTION "FieldExists" (IN "Tbl" VARCHAR COLLATE ANSI_CI, IN "Fld" VARCHAR COLLATE ANSI_CI)
RETURNS BOOLEAN
BEGIN
DECLARE Source CURSOR FOR Cat;
DECLARE Checker VARCHAR COLLATE ANSI_CI;
DECLARE Result BOOLEAN DEFAULT FALSE;


PREPARE Cat FROM 'SELECT Name FROM Information.TableColumns WHERE TableName = ? AND Name = ?';
OPEN Source USING Tbl,Fld;
FETCH FIRST FROM Source('Name') INTO Checker;

IF Checker = Fld THEN
SET Result = TRUE;
END IF;

RETURN Result;
END



Then you use a script like

SCRIPT
BEGIN
DECLARE Moder VARCHAR DEFAULT '';

IF NOT FieldExists('adam','Test') THEN
SET Moder = Moder +'ADD COLUMN Test DECIMAL(19,2)';
END IF;

/*
lots more like above
I'd probably create a small function to add commas onto the end where necessary
*/
IF Moder <> '' THEN
SET Moder = 'ALTER TABLE ADAM '+Moder;
END IF;

EXECUTE IMMEDIATE Moder;

END


I think I have a better way but I can't get it working - I'm going to ask advice from the oracle (aka Tim) and I'll post back shortly

Roy Lambert [Team Elevate]

Phil


And here's the "better way"

SCRIPT
BEGIN
DECLARE Moder VARCHAR DEFAULT '';
SET Moder = AddFieldIfNotExists('Adam','Test2',Moder,'BOOLEAN');
SET Moder = Moder + AddFieldIfNotExists('Adam','Test3',Moder,'INTEGER');
SET Moder = Moder + AddFieldIfNotExists('Adam','Test4',Moder,'VARCHAR(17) COLLATE ANSI');
IF Moder <> '' THEN
EXECUTE IMMEDIATE 'ALTER TABLE Adam ' + Moder;
END IF;
END


ALTER FUNCTION "AddFieldIfNotExists" (IN "Tbl" VARCHAR COLLATE ANSI_CI, IN "Fld" VARCHAR COLLATE ANSI_CI, IN Current VARCHAR COLLATE ANSI_CI, IN NewFldDef VARCHAR COLLATE ANSI_CI)
RETURNS VARCHAR COLLATE ANSI_CI
BEGIN
DECLARE Source CURSOR FOR Cat;
DECLARE Checker VARCHAR COLLATE ANSI_CI;
DECLARE Result VARCHAR COLLATE ANSI_CI DEFAULT NULL;
PREPARE Cat FROM 'SELECT Name FROM Information.TableColumns WHERE TableName = ? AND Name = ?';
OPEN Source USING Tbl,Fld;
FETCH FIRST FROM Source('Name') INTO Checker;
IF Checker IS NULL THEN
IF Current <> '' THEN
 SET Result = ', ';
END IF;
SET Result = Result +'ADD COLUMN '+Fld+' '+ NewFldDef;
END IF;
RETURN Result;
END


From this you should be able to work out how to delete columns or alter them if they're already there.

Roy Lambert [Team Elevate]