I'm full of questions today. Sorry.

In previous DBISam appilcations, I've had a wide variety of 'versions'
out there. In fact, my two largest applications get modified almost on a
daily basis. These modifications can include new fields, or new tables
to the application (and database).

I've found the easiest way (for me) to handle these in the past is to
have a form where I place a Table component on there for each table, and
Load all the persistent fields. If I add a new table to my database /
application, I add the persistent field (or new table component) to this
form and change a DBVer variable within the program.

When the end user updates, it checks the DBVer variable, and if his is
older, it executes a function that goes through each TDBISamTable
component. If the table doesn't exist, it calls createtable. If the
table does exist, it scans through each field and compares with the
TPersistentfield in the table. If a field doesn't exist in his database,
it then adds that field.

It took a bit to write this procedure, but it has served me very well. I
don't need to worry about where each client is upto with their database,
and adding new fields to my application is a very simple task. Just add
in DBSys, and add the persistent field (or table) to the form, and I'm
done...

However - now that I'm working on an application in EDB - I'm finding
that it's going to be far more difficult for me to achieve the same
function as I did in EDB. Probably not impossible, but before I start
work and spend a lot of time, I wanted to see how others handle this,
and whether I should be considering another option / method?


Cheers

Adam.

Adam,

I would suggest that you open EDBManager and review data returned from the query:

SELECT * FROM Information.TableColumns

Adding

WHERE TableName = 'YourTableName'

will give you a valid field-list of the existing fields.

You can even use the new EDB-SQL command:

SELECT LIST(Name) FROM Information.TableColumns

WHERE TableName = 'YourTableName'

To generate a simple comma-list you could plug into a TStringList.CommaText

--

It would be possible to write routines in Delphi which compared this list to your data-tables fields, and then run from here to a SQL statement which adds and removes columns.

--

I have a second suggestion:

EDBManager has a "create upgrade script" option. This creates a full script to update between 2 versions of a database. It is simple and effective, though under-documented You select the db you want to upgrade then click on "Migrate database in the "Tasks Panel" and work through the "Upgrade" steps thereafter.

If you used EDBManager to generate such a script You could then incorporate this update script into a "run updates" method in your new executable and it could run the first time the exe ran.

The reason I think you should think about this is because there is so much more to EDB than just tables, and table columns. There are triggers, views, procedures ...

Once you start to use EDB you will want to use at least some these features, and the update method you describe will not carry over these additional features, as it only creates new columns.

Hi Adam,

Thanks for your suggestion...

> I would suggest that you open EDBManager and review data returned from the query:
>
> SELECT * FROM Information.TableColumns
<Snip>
> It would be possible to write routines in Delphi which compared this
list to your data-tables fields, and then run from here to a SQL
statement which adds and removes columns.

Thanks for that suggestion. In the end, it looks like this would follow
a similar process to what I'm currently doing. (ie, storing a list of
'current' fields and indexes, either in a TDataset as I am now, or in a
TStringList. I'm guessing that I just need to work on converting my
script across...


> I have a second suggestion:
>
> EDBManager has a "create upgrade script" option.

I did see this, and got excited for a moment - but unfortunately I don't
think it's going to accomplish what I need. This is fine for upgrading
one 'known' database to my current database, but it's not going to help
where I distribute an update, and I have 40 clients that are all running
different older versions that update to the current.

It looks like I may need to continue to work on the original idea. I
just thought that this might be a common situation where others may have
already done something similar, but I guess most people have 'hands on'
access to the databases they're maintaining to do it manually?

Thanks again for your help!

Adam.

Adam,

I have a problem similar to yours where users can have versions 1, 2, 3, 4 ... of a product and be upgrading to a new version.

I use the EDB Generate "Upgrade Script" for each distinct version & then have a method "UpgradeDatabase" which takes a VersionID (held in an INI)

There are then a series of upgrade scripts, if the user is coming from version 2 the script for 3 + 4 will run ... etc.

It is a bit clunky, and means having really long text-based SQL scripts built into the EXE, but it is secure & works, so long as each part of the script is closely tested and accurate.

It is easier & more flexible than it perhaps sounds from the above ...

Adam,

<< I did see this, and got excited for a moment - but unfortunately I don't
think it's going to accomplish what I need. This is fine for upgrading one
'known' database to my current database, but it's not going to help where I
distribute an update, and I have 40 clients that are all running different
older versions that update to the current. >>

The next minor release is going to have a new COMPARE DATABASE SQL statement
that will generate a special system information table that contains the
CREATE/ALTER/DROP statements that will be necessary to upgrade one database
to another (in a minimal fashion and a dependency-sensitive order).

Tim Young
Elevate Software
www.elevatesoft.com

Tim

>The next minor release is going to have a new COMPARE DATABASE SQL statement
>that will generate a special system information table that contains the
>CREATE/ALTER/DROP statements that will be necessary to upgrade one database
>to another (in a minimal fashion and a dependency-sensitive order).

Nice

Roy

Hi Adam,

> I have a problem similar to yours where users can have versions 1, 2, 3, 4 ... of a product and be upgrading to a new version.
>
> I use the EDB Generate "Upgrade Script" for each distinct version & then have a method "UpgradeDatabase" which takes a VersionID (held in an INI)
>
> There are then a series of upgrade scripts, if the user is coming from version 2 the script for 3 + 4 will run ... etc.
>
> It is a bit clunky, and means having really long text-based SQL scripts built into the EXE, but it is secure & works, so long as each part of the script is closely tested and accurate.
>
> It is easier & more flexible than it perhaps sounds from the above ...

Thanks for that. It's an option I was weighing up, and now I've just
seen Tim's response about a compare database sql statement which sounds
very inviting indeed.

Cheers

Adam.

On 2/02/2013 5:06 AM, Roy Lambert wrote:
> Tim
>
>> The next minor release is going to have a new COMPARE DATABASE SQL statement
>> that will generate a special system information table that contains the
>> CREATE/ALTER/DROP statements that will be necessary to upgrade one database
>> to another (in a minimal fashion and a dependency-sensitive order).
>
> Nice

Very Nice indeed. I'll wait for this release and see if it'll do what
I'm needing.

Thanks Tim!

Adam.

>Roy Lambert wrote:

>Tim

>>The next minor release is going to have a new COMPARE DATABASE SQL statement
>>that will generate a special system information table that contains the
>>CREATE/ALTER/DROP statements that will be necessary to upgrade one database
>>to another (in a minimal fashion and a dependency-sensitive order).

>Nice

>Roy

Plus one!