| Subject | RE: [firebird-support] Distributing/deploying Stored Procedures |
|---|---|
| Author | Louis van Alphen |
| Post date | 2015-03-12T07:15:04Z |
Just in terms of DDL upgrades and releases, I take the following approach:
- All DDL updates are scripted. Absolutely no click-click in a DB tool. This way I can also put my scripts in version control
- I have only one stored proc in my DB. It is called GET_DATABASE_VERSION. It simply returns a string that indicates the DDL version of the DB. E.g. ‘2.3’ where 2 is the DDL version and 3 is the customer specific DDL version for e.g. reports and data fixes that have been run, etc.
- With an update, say V2, I have one script file that runs all the others in correct sequence. It also updates the version SP to indicate the new DB DDL version
An automated upgrade tool can then determine the actual DDL version of the DB (it could be different at various customers) and then run the appropriate updates to bring the DB to the correct version
I would recommend against dropping / creating SPs to update because you will likely have dependencies. Just use CREATE OR ALTER PROCEDURE
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: 12 March 2015 04:30 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Distributing/deploying Stored Procedures
I’m a bit of a FB noob and am after some advice about Stored Procedures and the best way to distribute them to client sites.
We are in the process of redeveloping our software using FB as the back end (it was previously ISAM). We have a reasonably large client base and our application is installed on many sites which run independently.
We intend to make use of Stored Procedures, Triggers etc and between releases (during development) these objects will be created, dropped, modified etc. I have been tasked with finding a solution to distribute the update/upgrade SP.
At the client end they will access the data via our application (via ODBC). They will have no direct access to the FB server and no ISQL command.
I can pump SQL statements through the application to make certain changes the first time a new release is run (alter table etc) but am concerned at how I am supposed to update SPs (potentially a large number) without access to the FB server. I considered extracting the metadata(FlameRobin), deleting superfluous statements and pumping what is left through the app but that seems like A LOT of work.
Also, do I bother with ‘alter procedure …..’ or do I simply drop them and (re)create them (after all, at release time, the SP is the SP)
I am trying to automate the task as much as possible, I don’t want our support staff to have to intervene in an upgrade.
Surely I’m not the only one that has needed to do such a thing. Does such a mechanism exist? Perhaps some suitable suggestions?
Cheers for now,
Andrew
I hope I explained my predicament sufficiently, basically I want to be able to duplicate SP etc from a development system to a production system easily with as much automation as possible.
[Non-text portions of this message have been removed]
- All DDL updates are scripted. Absolutely no click-click in a DB tool. This way I can also put my scripts in version control
- I have only one stored proc in my DB. It is called GET_DATABASE_VERSION. It simply returns a string that indicates the DDL version of the DB. E.g. ‘2.3’ where 2 is the DDL version and 3 is the customer specific DDL version for e.g. reports and data fixes that have been run, etc.
- With an update, say V2, I have one script file that runs all the others in correct sequence. It also updates the version SP to indicate the new DB DDL version
An automated upgrade tool can then determine the actual DDL version of the DB (it could be different at various customers) and then run the appropriate updates to bring the DB to the correct version
I would recommend against dropping / creating SPs to update because you will likely have dependencies. Just use CREATE OR ALTER PROCEDURE
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: 12 March 2015 04:30 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Distributing/deploying Stored Procedures
I’m a bit of a FB noob and am after some advice about Stored Procedures and the best way to distribute them to client sites.
We are in the process of redeveloping our software using FB as the back end (it was previously ISAM). We have a reasonably large client base and our application is installed on many sites which run independently.
We intend to make use of Stored Procedures, Triggers etc and between releases (during development) these objects will be created, dropped, modified etc. I have been tasked with finding a solution to distribute the update/upgrade SP.
At the client end they will access the data via our application (via ODBC). They will have no direct access to the FB server and no ISQL command.
I can pump SQL statements through the application to make certain changes the first time a new release is run (alter table etc) but am concerned at how I am supposed to update SPs (potentially a large number) without access to the FB server. I considered extracting the metadata(FlameRobin), deleting superfluous statements and pumping what is left through the app but that seems like A LOT of work.
Also, do I bother with ‘alter procedure …..’ or do I simply drop them and (re)create them (after all, at release time, the SP is the SP)
I am trying to automate the task as much as possible, I don’t want our support staff to have to intervene in an upgrade.
Surely I’m not the only one that has needed to do such a thing. Does such a mechanism exist? Perhaps some suitable suggestions?
Cheers for now,
Andrew
I hope I explained my predicament sufficiently, basically I want to be able to duplicate SP etc from a development system to a production system easily with as much automation as possible.
[Non-text portions of this message have been removed]