With Stored Procs now a feature, what would you recommend for a methodology
of updating tables.

I'm using the versioning on the tables themselves.

Would you create a proc for each change to the DDL and have a simple table
that tells you what version you are at?

I.E.

1) If Database is not created, create it.

2) If Database is created then push all the procs and views to the database,
assuming that if the procs and views are already there, they would be
overwritten.

3) If VersionTable doesn't exist, then we have a new database. Run the proc
to set up the original schema

4) If VersionTable Exists, then we go up the ladder for each verion:
   a) If VersionTable.Version = 1 and AppVersion = 2 then call Stored Proc
"Update_1_to_2".
   b) If VersionTable.Version = 1 and AppVersion = 3 then call Stored Proc
"Update_1_to_2" and call "Update_2_to_3"


Any ideas are welcome.  I know there's a lot of ways to do this.  I'm hoping
not to do many DDL updates, but you never know.

Lance

Hello Lance,

<self-promotion>
Take a look at our Context Database Designer and Extensions at
http://www.contextsoft.com/products/dbdesign
& http://www.contextsoft.com/products/ctxdbext

They can do pretty much everything you have asked here:
1. Execute batch scripts
2. Import\reverse engineer schema from SQL or database
3. Create version checkpoints and generate ALTER DDL scripts
4. Update database on startup to correspond to your schema version
(the version is stored in one row table in database - one for
the whole database schema).

....not to mention diagramming, visual editor for schema,
schema comparison tool (between versions, schemas, databases).

</self-promotion>

Regards,
Michael

Lance R wrote:
> With Stored Procs now a feature, what would you recommend for a methodology
> of updating tables.
>
> I'm using the versioning on the tables themselves.
>
> Would you create a proc for each change to the DDL and have a simple table
> that tells you what version you are at?
>
> I.E.
>
> 1) If Database is not created, create it.
>
> 2) If Database is created then push all the procs and views to the database,
> assuming that if the procs and views are already there, they would be
> overwritten.
>
> 3) If VersionTable doesn't exist, then we have a new database. Run the proc
> to set up the original schema
>
> 4) If VersionTable Exists, then we go up the ladder for each verion:
>     a) If VersionTable.Version = 1 and AppVersion = 2 then call Stored Proc
> "Update_1_to_2".
>     b) If VersionTable.Version = 1 and AppVersion = 3 then call Stored Proc
> "Update_1_to_2" and call "Update_2_to_3"
>
>
> Any ideas are welcome.  I know there's a lot of ways to do this.  I'm hoping
> not to do many DDL updates, but you never know.
>
> Lance
>
>

Michael,

I've looked at your products in the past.  And they seem great.

I didn't see anything regarding EBD though, which I've migrated to.

The other issue that I saw is reaction time to updates Tim does.   I know
there's obviously going to be a lag time from when Tim releases an update to
when you will get it and accomodate whatever's needed.

Lance


"Michael Baytalsky" <mike@contextsoft.com> wrote in message
news:EB196C42-0A58-4033-825D-F4AFC4478634@news.elevatesoft.com...
> Hello Lance,
>
> <self-promotion>
> Take a look at our Context Database Designer and Extensions at
> http://www.contextsoft.com/products/dbdesign
> & http://www.contextsoft.com/products/ctxdbext
>
> They can do pretty much everything you have asked here:
> 1. Execute batch scripts
> 2. Import\reverse engineer schema from SQL or database
> 3. Create version checkpoints and generate ALTER DDL scripts
> 4. Update database on startup to correspond to your schema version
> (the version is stored in one row table in database - one for
> the whole database schema).
>
> ...not to mention diagramming, visual editor for schema,
> schema comparison tool (between versions, schemas, databases).
>
> </self-promotion>
>
> Regards,
> Michael
>
> Lance R wrote:
>> With Stored Procs now a feature, what would you recommend for a
>> methodology of updating tables.
>>
>> I'm using the versioning on the tables themselves.
>>
>> Would you create a proc for each change to the DDL and have a simple
>> table that tells you what version you are at?
>>
>> I.E.
>>
>> 1) If Database is not created, create it.
>>
>> 2) If Database is created then push all the procs and views to the
>> database, assuming that if the procs and views are already there, they
>> would be overwritten.
>>
>> 3) If VersionTable doesn't exist, then we have a new database. Run the
>> proc to set up the original schema
>>
>> 4) If VersionTable Exists, then we go up the ladder for each verion:
>>     a) If VersionTable.Version = 1 and AppVersion = 2 then call Stored
>> Proc "Update_1_to_2".
>>     b) If VersionTable.Version = 1 and AppVersion = 3 then call Stored
>> Proc "Update_1_to_2" and call "Update_2_to_3"
>>
>>
>> Any ideas are welcome.  I know there's a lot of ways to do this.  I'm
>> hoping not to do many DDL updates, but you never know.
>>
>> Lance
>>

Lance,

<< Would you create a proc for each change to the DDL and have a simple
table that tells you what version you are at? >>

Sure, that would work fine.  You can only do versioning for tables in EDB
currently, so you'll need an external indicator for the version of non-table
objects such as views, procedures, etc.    But, other than that, you should
be fine with the methods that you outlined.

--
Tim Young
Elevate Software
www.elevatesoft.com

Lance,

> I've looked at your products in the past.  And they seem great.
>
> I didn't see anything regarding EBD though, which I've migrated to.
It is supported since 2.13, you can find adapters (binary dll plug-in
for the designer) and VCL components on our download pages.

> The other issue that I saw is reaction time to updates Tim does. I know
> there's obviously going to be a lag time from when Tim releases an update to
> when you will get it and accomodate whatever's needed.
The components most likely won't need any major changes and are supplied
in source code, so you just rebild the package which links to EDB bpl.
The designer is supplied with adapter dll, so your code doesn't depend
on it. There's no tight connection to ElevateDB releases, so it should
be fine. We release versions almost every month, so the lag should not
be longer then that.

Regards,
Michael

>
>
> "Michael Baytalsky" <mike@contextsoft.com> wrote in message
> news:EB196C42-0A58-4033-825D-F4AFC4478634@news.elevatesoft.com...
>> Hello Lance,
>>
>> <self-promotion>
>> Take a look at our Context Database Designer and Extensions at
>> http://www.contextsoft.com/products/dbdesign
>> & http://www.contextsoft.com/products/ctxdbext
>>
>> They can do pretty much everything you have asked here:
>> 1. Execute batch scripts
>> 2. Import\reverse engineer schema from SQL or database
>> 3. Create version checkpoints and generate ALTER DDL scripts
>> 4. Update database on startup to correspond to your schema version
>> (the version is stored in one row table in database - one for
>> the whole database schema).
>>
>> ...not to mention diagramming, visual editor for schema,
>> schema comparison tool (between versions, schemas, databases).
>>
>> </self-promotion>
>>
>> Regards,
>> Michael
>>
>> Lance R wrote:
>>> With Stored Procs now a feature, what would you recommend for a
>>> methodology of updating tables.
>>>
>>> I'm using the versioning on the tables themselves.
>>>
>>> Would you create a proc for each change to the DDL and have a simple
>>> table that tells you what version you are at?
>>>
>>> I.E.
>>>
>>> 1) If Database is not created, create it.
>>>
>>> 2) If Database is created then push all the procs and views to the
>>> database, assuming that if the procs and views are already there, they
>>> would be overwritten.
>>>
>>> 3) If VersionTable doesn't exist, then we have a new database. Run the
>>> proc to set up the original schema
>>>
>>> 4) If VersionTable Exists, then we go up the ladder for each verion:
>>>     a) If VersionTable.Version = 1 and AppVersion = 2 then call Stored
>>> Proc "Update_1_to_2".
>>>     b) If VersionTable.Version = 1 and AppVersion = 3 then call Stored
>>> Proc "Update_1_to_2" and call "Update_2_to_3"
>>>
>>>
>>> Any ideas are welcome.  I know there's a lot of ways to do this.  I'm
>>> hoping not to do many DDL updates, but you never know.
>>>
>>> Lance
>>>
>