You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@subversion.apache.org by Giulio Troccoli <Gi...@uk.linedata.com> on 2010/10/29 13:11:01 UTC
Moving to Subversion for PL-SQL development
First of all let me tell you that I don't know much of how PL-SQL development works so I might say something really obvious to you or more likely just wrong. Please forgive me.
I have a team that uses StarTeam as their VCS and we are now working on moving the project to Subversion. We are planning to use an importer for the initial load of the repository which seems to do what they want (I'm not looking after that part).
I have a problem though with their releasing process.
As I understand it, a major release is formed by all the packages and scripts, plus some table initialisation and sometime some data (I presume for defaults and stuff like that). Minor releases are done with patches which included only the packages that have changed from the previous patch.
So, if I want 5.4.0 (major release), I get everything. I unpack the kit, install it, run it, whatever it take and I'm done. If I am already on 5.4.0 and I want 5.4.3 (a minor release) I will be sent 3 patches: to 5.4.1, then 5.4.2 and finally 5.4.3. Apparently I just need to unzip them and I'm done.
Now, I might not be clear in the above process, so if someone with more experience with PL-SQL development and release wants to correct me, please do. I know there isn't one way to do things, but it's more likely that I understood wrong than we are doing it in a special way.
Anyway, if I am right, I'm struggling to come up with a process using Subversion. It seems they do not want to tag everything in trunk because that would be like a major release (apparently it would include those table and data things). Maybe we could re-organised the code to separate the packages from the data and then we could tag the packages, which is more what they want. And this way, to go to 5.4.3 I won't need 5.4.1 and 5.4.2 at all, which in my opinion is even better.
In the end what I am looking for with this email is some advice on how to proceed from people with more experience than me in projects using PL-SQL.
Thanks
Giulio Troccoli
Linedata Limited
Registered Office: 85 Gracechurch St., London, EC3V 0AA
Registered in England and Wales No 3475006 VAT Reg No 710 3140 03
RE: Moving to Subversion for PL-SQL development
Posted by Dieter Oberkofler <do...@gmail.com>.
Luiz,
> Sounds perfect! But how do you manage daily work on Oracle packages?
> Does your developers work in local Oracle instances or a shared
> development Oracle instance?
There is no simple answer to this and it really depends on how "different" a
database version gets.
I'm not sure, if I'm able to describe this but let me try:
Typically every developer has its local Database Instance that in our case
are schemas on a single server and there is a master schema that always hold
a "consistent snapshot" and most often is the same as the tagged revision.
If there are major changes that may be needed or affect other developers in
the team, there is a branch that hold the individual changes and the changes
will be merged into the main trunk when they should be made available to
others.
Our update application has a special "development" update option that
instead of moving from one tagged revision to the next one also allows to
"just" run a set of update scripts currently available in the trunk of our
database repository and this would then allow the team members to eventually
(if needed) upgrade their local database to some "intermediate" revision.
> Does your PL/SQL development tool integrates to SVN in some way?
We mostly use a simple editor (or PlEdit) to edit SQL and PL/SQL scripts and
then run all our scripts with good old SQL*Plus, so there is no SVN
integration.
We have spend quite some time to figure out how to integrate some kind of
locking mechanism in our toolset but at the end found out that there is
little out there and we do not really need it as much as we originally would
have expected. There is a lot less conflicts that we anticipated because the
database design is (at least in our organization) a rather centralized type
of work.
We generally do not use any tools that integrate SVN because we very much
like using the SVN command line or TortoiseSVN.
Cheers,
Dieter
> -----Original Message-----
> From: Luiz Guilherme Kimel [mailto:lkimel@dba.com.br]
> Sent: Friday, October 29, 2010 21:25
> To: users@subversion.apache.org
> Subject: RES: Moving to Subversion for PL-SQL development
>
> Dieter,
>
> "For an update we then use a custom developed application that
> determines the source revision of the database and to witch revision to
> update, extracts all the needed tags and executes the appropriate
> update scripts in each tagged revision in the proper order"
>
> Sounds perfect! But how do you manage daily work on Oracle packages?
> Does your developers work in local Oracle instances or a shared
> development Oracle instance? Does your PL/SQL development tool
> integrates to SVN in some way?
>
> Could you describe it briefly? I'm interested in how you manage it.
>
> Thank you!
>
>
> -----Mensagem original-----
> De: Dieter Oberkofler [mailto:doberkofler.lists@gmail.com]
> Enviada em: sexta-feira, 29 de outubro de 2010 15:07
> Para: 'Giulio Troccoli'
> Cc: users@subversion.apache.org
> Assunto: RE: Moving to Subversion for PL-SQL development
>
> Giulio,
>
> I'm not sure if I correctly understood all the details of your
> questions but I have been using Subversion in an environment that
> mostly consists of C/C++, Java and an Oracle Database for quite some
> time and would be happy to share my experience.
>
> From my general perspective managing PL/SQL in a version control system
> is quite the same as managing any other sources.
>
> The real complex thing is how to manage and upgrade the different
> revisions/version of the database structure itself.
> In our environment each "shippable" (alpha, beta, production) version
> of our database structure (including PL/SQL) is tagged in Subversion.
> The tag contains a snapshot of the scripts (SQL and PL/SQL) needed to
> create a new database and additionally contains the appropriate update
> scripts (typically a set of SQL Scripts executed in SQL*Plus) needed to
> update the last revision to this one.
> For an update we then use a custom developed application that
> determines the source revision of the database and to witch revision to
> update, extracts all the needed tags and executes the appropriate
> update scripts in each tagged revision in the proper order.
>
> I hope this helps a little.
>
> Cheers,
> Dieter
>
>
> > -----Original Message-----
> > From: Giulio Troccoli [mailto:Giulio.Troccoli@uk.linedata.com]
> > Sent: Friday, October 29, 2010 15:11
> > To: users@subversion.apache.org
> > Subject: Moving to Subversion for PL-SQL development
> >
> > First of all let me tell you that I don't know much of how PL-SQL
> > development works so I might say something really obvious to you or
> > more likely just wrong. Please forgive me.
> >
> > I have a team that uses StarTeam as their VCS and we are now working
> > on moving the project to Subversion. We are planning to use an
> > importer for the initial load of the repository which seems to do
> what
> > they want (I'm not looking after that part).
> >
> > I have a problem though with their releasing process.
> >
> > As I understand it, a major release is formed by all the packages and
> > scripts, plus some table initialisation and sometime some data (I
> > presume for defaults and stuff like that). Minor releases are done
> > with patches which included only the packages that have changed from
> > the previous patch.
> >
> > So, if I want 5.4.0 (major release), I get everything. I unpack the
> > kit, install it, run it, whatever it take and I'm done. If I am
> > already on 5.4.0 and I want 5.4.3 (a minor release) I will be sent 3
> patches:
> > to 5.4.1, then 5.4.2 and finally 5.4.3. Apparently I just need to
> > unzip them and I'm done.
> >
> > Now, I might not be clear in the above process, so if someone with
> > more experience with PL-SQL development and release wants to correct
> > me, please do. I know there isn't one way to do things, but it's more
> > likely that I understood wrong than we are doing it in a special way.
> >
> > Anyway, if I am right, I'm struggling to come up with a process using
> > Subversion. It seems they do not want to tag everything in trunk
> > because that would be like a major release (apparently it would
> > include those table and data things). Maybe we could re-organised the
> > code to separate the packages from the data and then we could tag the
> > packages, which is more what they want. And this way, to go to 5.4.3
> I
> > won't need
> > 5.4.1 and 5.4.2 at all, which in my opinion is even better.
> >
> > In the end what I am looking for with this email is some advice on
> how
> > to proceed from people with more experience than me in projects using
> > PL-SQL.
> >
> > Thanks
> > Giulio Troccoli
> >
> >
> > Linedata Limited
> > Registered Office: 85 Gracechurch St., London, EC3V 0AA Registered in
> > England and Wales No 3475006 VAT Reg No 710 3140 03
> >
> >
>
RES: Moving to Subversion for PL-SQL development
Posted by Luiz Guilherme Kimel <lk...@dba.com.br>.
Dieter,
"For an update we then use a custom developed application that determines
the source revision of the database and to witch revision to update,
extracts all the needed tags and executes the appropriate update scripts in
each tagged revision in the proper order"
Sounds perfect! But how do you manage daily work on Oracle packages? Does
your developers work in local Oracle instances or a shared development
Oracle instance? Does your PL/SQL development tool integrates to SVN in some
way?
Could you describe it briefly? I'm interested in how you manage it.
Thank you!
-----Mensagem original-----
De: Dieter Oberkofler [mailto:doberkofler.lists@gmail.com]
Enviada em: sexta-feira, 29 de outubro de 2010 15:07
Para: 'Giulio Troccoli'
Cc: users@subversion.apache.org
Assunto: RE: Moving to Subversion for PL-SQL development
Giulio,
I'm not sure if I correctly understood all the details of your questions but
I have been using Subversion in an environment that mostly consists of
C/C++, Java and an Oracle Database for quite some time and would be happy to
share my experience.
RES: Moving to Subversion for PL-SQL development
Posted by Luiz Guilherme Kimel <lk...@dba.com.br>.
Dieter,
"For an update we then use a custom developed application that determines
the source revision of the database and to witch revision to update,
extracts all the needed tags and executes the appropriate update scripts in
each tagged revision in the proper order"
Sounds perfect! But how do you manage daily work on Oracle packages? Does
your developers work in local Oracle instances or a shared development
Oracle instance? Does your PL/SQL development tool integrates to SVN in some
way?
Could you describe it briefly? I'm interested in how you manage it.
Thank you!
-----Mensagem original-----
De: Dieter Oberkofler [mailto:doberkofler.lists@gmail.com]
Enviada em: sexta-feira, 29 de outubro de 2010 15:07
Para: 'Giulio Troccoli'
Cc: users@subversion.apache.org
Assunto: RE: Moving to Subversion for PL-SQL development
Giulio,
I'm not sure if I correctly understood all the details of your questions but
I have been using Subversion in an environment that mostly consists of
C/C++, Java and an Oracle Database for quite some time and would be happy to
share my experience.
>From my general perspective managing PL/SQL in a version control system is
quite the same as managing any other sources.
The real complex thing is how to manage and upgrade the different
revisions/version of the database structure itself.
In our environment each "shippable" (alpha, beta, production) version of our
database structure (including PL/SQL) is tagged in Subversion.
The tag contains a snapshot of the scripts (SQL and PL/SQL) needed to create
a new database and additionally contains the appropriate update scripts
(typically a set of SQL Scripts executed in SQL*Plus) needed to update the
last revision to this one.
For an update we then use a custom developed application that determines the
source revision of the database and to witch revision to update, extracts
all the needed tags and executes the appropriate update scripts in each
tagged revision in the proper order.
I hope this helps a little.
Cheers,
Dieter
> -----Original Message-----
> From: Giulio Troccoli [mailto:Giulio.Troccoli@uk.linedata.com]
> Sent: Friday, October 29, 2010 15:11
> To: users@subversion.apache.org
> Subject: Moving to Subversion for PL-SQL development
>
> First of all let me tell you that I don't know much of how PL-SQL
> development works so I might say something really obvious to you or
> more likely just wrong. Please forgive me.
>
> I have a team that uses StarTeam as their VCS and we are now working on
> moving the project to Subversion. We are planning to use an importer
> for the initial load of the repository which seems to do what they want
> (I'm not looking after that part).
>
> I have a problem though with their releasing process.
>
> As I understand it, a major release is formed by all the packages and
> scripts, plus some table initialisation and sometime some data (I
> presume for defaults and stuff like that). Minor releases are done with
> patches which included only the packages that have changed from the
> previous patch.
>
> So, if I want 5.4.0 (major release), I get everything. I unpack the
> kit, install it, run it, whatever it take and I'm done. If I am already
> on 5.4.0 and I want 5.4.3 (a minor release) I will be sent 3 patches:
> to 5.4.1, then 5.4.2 and finally 5.4.3. Apparently I just need to unzip
> them and I'm done.
>
> Now, I might not be clear in the above process, so if someone with more
> experience with PL-SQL development and release wants to correct me,
> please do. I know there isn't one way to do things, but it's more
> likely that I understood wrong than we are doing it in a special way.
>
> Anyway, if I am right, I'm struggling to come up with a process using
> Subversion. It seems they do not want to tag everything in trunk
> because that would be like a major release (apparently it would include
> those table and data things). Maybe we could re-organised the code to
> separate the packages from the data and then we could tag the packages,
> which is more what they want. And this way, to go to 5.4.3 I won't need
> 5.4.1 and 5.4.2 at all, which in my opinion is even better.
>
> In the end what I am looking for with this email is some advice on how
> to proceed from people with more experience than me in projects using
> PL-SQL.
>
> Thanks
> Giulio Troccoli
>
>
> Linedata Limited
> Registered Office: 85 Gracechurch St., London, EC3V 0AA Registered in
> England and Wales No 3475006 VAT Reg No 710 3140 03
>
>
RES: Moving to Subversion for PL-SQL development
Posted by Luiz Guilherme Kimel <lk...@dba.com.br>.
Wow! This sounds really very good. I will try it here!
PS: OpenNMS also looks a very nice finding.
Thank you! (twice)
-----Mensagem original-----
De: Les Mikesell [mailto:lesmikesell@gmail.com]
Enviada em: sexta-feira, 29 de outubro de 2010 15:19
Para: users@subversion.apache.org
Assunto: Re: Moving to Subversion for PL-SQL development
There's something called liquibase (http://www.liquibase.org) that is
supposed to manage this for you. I don't know much about it other than
that it is used by the OpenNMS project and run as an update step to fix
schema changes between versions.
--
Les Mikesell
lesmikesell@gmail.com
Re: Moving to Subversion for PL-SQL development
Posted by Les Mikesell <le...@gmail.com>.
On 10/29/2010 1:06 PM, Dieter Oberkofler wrote:
> Giulio,
>
> I'm not sure if I correctly understood all the details of your questions but
> I have been using Subversion in an environment that mostly consists of
> C/C++, Java and an Oracle Database for quite some time and would be happy to
> share my experience.
>
> From my general perspective managing PL/SQL in a version control system is
> quite the same as managing any other sources.
>
> The real complex thing is how to manage and upgrade the different
> revisions/version of the database structure itself.
> In our environment each "shippable" (alpha, beta, production) version of our
> database structure (including PL/SQL) is tagged in Subversion.
> The tag contains a snapshot of the scripts (SQL and PL/SQL) needed to create
> a new database and additionally contains the appropriate update scripts
> (typically a set of SQL Scripts executed in SQL*Plus) needed to update the
> last revision to this one.
> For an update we then use a custom developed application that determines the
> source revision of the database and to witch revision to update, extracts
> all the needed tags and executes the appropriate update scripts in each
> tagged revision in the proper order.
>
There's something called liquibase (http://www.liquibase.org) that is
supposed to manage this for you. I don't know much about it other than
that it is used by the OpenNMS project and run as an update step to fix
schema changes between versions.
--
Les Mikesell
lesmikesell@gmail.com
RE: Moving to Subversion for PL-SQL development
Posted by Dieter Oberkofler <do...@gmail.com>.
Giulio,
I'm not sure if I correctly understood all the details of your questions but
I have been using Subversion in an environment that mostly consists of
C/C++, Java and an Oracle Database for quite some time and would be happy to
share my experience.
RE: Moving to Subversion for PL-SQL development
Posted by Dieter Oberkofler <do...@gmail.com>.
Giulio,
I'm not sure if I correctly understood all the details of your questions but
I have been using Subversion in an environment that mostly consists of
C/C++, Java and an Oracle Database for quite some time and would be happy to
share my experience.
>From my general perspective managing PL/SQL in a version control system is
quite the same as managing any other sources.
The real complex thing is how to manage and upgrade the different
revisions/version of the database structure itself.
In our environment each "shippable" (alpha, beta, production) version of our
database structure (including PL/SQL) is tagged in Subversion.
The tag contains a snapshot of the scripts (SQL and PL/SQL) needed to create
a new database and additionally contains the appropriate update scripts
(typically a set of SQL Scripts executed in SQL*Plus) needed to update the
last revision to this one.
For an update we then use a custom developed application that determines the
source revision of the database and to witch revision to update, extracts
all the needed tags and executes the appropriate update scripts in each
tagged revision in the proper order.
I hope this helps a little.
Cheers,
Dieter
> -----Original Message-----
> From: Giulio Troccoli [mailto:Giulio.Troccoli@uk.linedata.com]
> Sent: Friday, October 29, 2010 15:11
> To: users@subversion.apache.org
> Subject: Moving to Subversion for PL-SQL development
>
> First of all let me tell you that I don't know much of how PL-SQL
> development works so I might say something really obvious to you or
> more likely just wrong. Please forgive me.
>
> I have a team that uses StarTeam as their VCS and we are now working on
> moving the project to Subversion. We are planning to use an importer
> for the initial load of the repository which seems to do what they want
> (I'm not looking after that part).
>
> I have a problem though with their releasing process.
>
> As I understand it, a major release is formed by all the packages and
> scripts, plus some table initialisation and sometime some data (I
> presume for defaults and stuff like that). Minor releases are done with
> patches which included only the packages that have changed from the
> previous patch.
>
> So, if I want 5.4.0 (major release), I get everything. I unpack the
> kit, install it, run it, whatever it take and I'm done. If I am already
> on 5.4.0 and I want 5.4.3 (a minor release) I will be sent 3 patches:
> to 5.4.1, then 5.4.2 and finally 5.4.3. Apparently I just need to unzip
> them and I'm done.
>
> Now, I might not be clear in the above process, so if someone with more
> experience with PL-SQL development and release wants to correct me,
> please do. I know there isn't one way to do things, but it's more
> likely that I understood wrong than we are doing it in a special way.
>
> Anyway, if I am right, I'm struggling to come up with a process using
> Subversion. It seems they do not want to tag everything in trunk
> because that would be like a major release (apparently it would include
> those table and data things). Maybe we could re-organised the code to
> separate the packages from the data and then we could tag the packages,
> which is more what they want. And this way, to go to 5.4.3 I won't need
> 5.4.1 and 5.4.2 at all, which in my opinion is even better.
>
> In the end what I am looking for with this email is some advice on how
> to proceed from people with more experience than me in projects using
> PL-SQL.
>
> Thanks
> Giulio Troccoli
>
>
> Linedata Limited
> Registered Office: 85 Gracechurch St., London, EC3V 0AA Registered in
> England and Wales No 3475006 VAT Reg No 710 3140 03
>
>
RES: Moving to Subversion for PL-SQL development
Posted by Luiz Guilherme Kimel <lk...@dba.com.br>.
Hi Giulio,
I'm curious about how you manage your PL/SQL development and identify
packages and its versions and how do you manage concurrency. Until were I
know Oracle won't manage concurrency when two developers edit the same
package and once broke, the entire package stop working.
I used, in a past project, to ask the team to save packages as text files in
the repository and lock them to signalize someone is working on it. The live
package being edited should be edited with a different name like
"package_name_plus_developers_user_name" so that the package won't be broken
for other developers. Once the developer finished to edit the package and
make his own tests he would increase its version number (a simple comment in
the package interface and body), write a small changelog as a comment,
submit it to the repository, release de lock, update package with its real
name and delete the temporary one.
Sounds a little troublesome, but I couldn't think of a better process. Later
I included a version function inside each package so I could check packages
versions using an sql script.
"It seems they do not want to tag everything in trunk because that would be
like a major release (apparently it would include those table and data
things). Maybe we could re-organised the code to separate the packages from
the data and then we could tag the packages, which is more what they want."
Yes, you can have a BTT (branch, tags, trunk) structure for the database DDL
including packages, another BTT for documentation and another for source
code and manage their evolution separately if it fits better to your team.
Instead of one big configuration item now you have 3 to care about. The
database definition versions can have patches to update a live database from
version a to version b.
But anyway you will need to build your high level configuration item, which
will represent your entire software package including database, code,
documentation etc in its specific compatible version. This set, you can
release software.
Your installer can check each configuration item version to decide what to
do (database version updates, documentation version). There are things that
can be just deleted and overwritten (like binaries and help), but database
will usually need to be patched. Having the database and plsql code as an
independent configuration item sounds good to me.
I'm not sure I'm helping... lol
Luiz Guilherme M. Kimel
-----Mensagem original-----
De: Giulio Troccoli [mailto:Giulio.Troccoli@uk.linedata.com]
Enviada em: sexta-feira, 29 de outubro de 2010 10:11
Para: users@subversion.apache.org
Assunto: Moving to Subversion for PL-SQL development
First of all let me tell you that I don't know much of how PL-SQL
development works so I might say something really obvious to you or more
likely just wrong. Please forgive me.
I have a team that uses StarTeam as their VCS and we are now working on
moving the project to Subversion. We are planning to use an importer for the
initial load of the repository which seems to do what they want (I'm not
looking after that part).
I have a problem though with their releasing process.
As I understand it, a major release is formed by all the packages and
scripts, plus some table initialisation and sometime some data (I presume
for defaults and stuff like that). Minor releases are done with patches
which included only the packages that have changed from the previous patch.
So, if I want 5.4.0 (major release), I get everything. I unpack the kit,
install it, run it, whatever it take and I'm done. If I am already on 5.4.0
and I want 5.4.3 (a minor release) I will be sent 3 patches: to 5.4.1, then
5.4.2 and finally 5.4.3. Apparently I just need to unzip them and I'm done.
Now, I might not be clear in the above process, so if someone with more
experience with PL-SQL development and release wants to correct me, please
do. I know there isn't one way to do things, but it's more likely that I
understood wrong than we are doing it in a special way.
Anyway, if I am right, I'm struggling to come up with a process using
Subversion. It seems they do not want to tag everything in trunk because
that would be like a major release (apparently it would include those table
and data things). Maybe we could re-organised the code to separate the
packages from the data and then we could tag the packages, which is more
what they want. And this way, to go to 5.4.3 I won't need 5.4.1 and 5.4.2 at
all, which in my opinion is even better.
In the end what I am looking for with this email is some advice on how to
proceed from people with more experience than me in projects using PL-SQL.
Thanks
Giulio Troccoli
Linedata Limited
Registered Office: 85 Gracechurch St., London, EC3V 0AA
Registered in England and Wales No 3475006 VAT Reg No 710 3140 03