Version controlling stored procedures

Little_Idea

I recently inherited an data warehouse project built with packages in Oracle 10g.

My background is mainly application development, primarily using C/C++ and some java.

It drives me nuts that the packages and procedures are not version controlled.

The best idea I came up with so far is to extract everything out and put it into svn.

Does anyone have any tips or insight on versioning pl/sql packages?

ammoQ

@Little Idea said:

I recently inherited an data warehouse project built with packages in Oracle 10g.
My background is mainly application development, primarily using C/C++ and some java.
It drives me nuts that the packages and procedures are not version controlled.
The best idea I came up with so far is to extract everything out and put it into svn.
Does anyone have any tips or insight on versioning pl/sql packages?

This is a very common problem; programmers write their packages in Toad and never ever write them to files, except for deployment, so no version control takes place. It's sooooooo convenient, isn't it?
Obviously it would be desireable that people (your coworkers and you) immediately start saving all PL/SQL code ins files, so it can be put into svn. If you can't convice your coworkers to do it (or if your are even to lazy to do that yourself), at least start exporting changed sources in a daily job, so at least you have one version per day of changed packages. In case you don't know, USER_OBJECTS is the view you have to query to find out which packages have been changed.
To do it "right", our new policy is to have a "development" database and a "testing" database (along with all the databases the customers have, most have at least "testing" and "production");
(our) development is to be used without restrictions, but to get packages into testing, they have to be checked into svn; the "build process" checks out those packages and compiles them in "testing". Only programs tested in "testing" may be deployed, of course.
I've put a collection of two little programs that might help you here; one extracts sources from the database and the other one shows errors in procedure, calculating the line number in the file. This is especially usefull if you have package declaration and package body in one source file. Maybe those progs can help you in automating your process. If not, you can still send them to Alex to be posted on the main page.

savar

@Little Idea said:

The best idea I came up with so far is to extract everything out and put it into svn.

Does anyone have any tips or insight on versioning pl/sql packages?

I don't see anything wrong with your idea. If it were possible I would suggest you send out an e-mail to all developers saying if they have original copies of the code to check them in. That should really be part of the development standards. I can't imagine how you write a large application without any version control.

Where I work, most of the code is written as stored packages. We store them in text files and check them into a VCS and then have a build system which compiles* them into actual stored packages.

*compile=open a sqlplus session and execute the DDLs

ammoQ

@savar said:

Where I work, most of the code is written as stored packages. We store them in text files and check them into a VCS and then have a build system which compiles* them into actual stored packages.

How do you prevent your programmers to code directly to the database in Toad? Are they clever enough to understand that version control is good for them or do you have technical barriers to prevent that?

Chris_F

@Little Idea said:

I recently inherited an data warehouse project built with packages in Oracle 10g.

My background is mainly application development, primarily using C/C++ and some java.

It drives me nuts that the packages and procedures are not version controlled.

The best idea I came up with so far is to extract everything out and put it into svn.

Does anyone have any tips or insight on versioning pl/sql packages?

Version controlling stored procedures is absolutely vital and must be done. Not only this, but any database transformation should be scripted out and checked in. No modification should be performed in production unless it's in source control.

I've enforced version controlled database changes by associating every change script with the work item. When it comes time to promote a development work item to QA, only those associated scripts are promoted. Thus if it isn't in the version control system, the change never makes it to QA. And if it doesn't pass QA, it never makes it to production.

RyuO

@Little Idea said:

Does anyone have any tips or insight on versioning pl/sql packages?

AmmoQ has covered the file-management solutions, so I'll mention a fairly common architecture for development databases. As AmmoQ said, there is no telling what developers will install in Oracle's memory if left to their own devices. One way to impose a degree of CM on the database is:

Separate schema for DDL (mostly tables and objects subordinate to tables, such as indexes). Only you have the password to this schema.
Different schema for the code. That's a moderately good idea for the production system because of the extra layer of security, but it is extra-nice for development. Your rule for development should be that the only code that goes here has passed CM tool checkin and some degree of unit-testing. OK, so maybe you settle for code that compiles.
Personal schemas for each developer. A module in your own schema takes precedence over a module with the same name somewhere else, so it is a good place for in-process code.
Periodically refresh the code schema (#2) from SVN or whatever. Easiest method is to drop and recreate - don't try to reconcile anything. That will flush the inevitable weird fragments that creep in.

I did not go into much detail - the biggest detail being that you implement schema integration with a slew of grants and synonyms. You'd need to generate them automatically to make it worth the effort. Fortunately, many DBAs have similar code you could adapt into what makes sense in your environment.

http://www.aquafold.com/ has a good inexpensive product that is useful to extract the whole schema into text files.

ammoQ

@Rick said:

http://www.aquafold.com/ has a good inexpensive product that is useful to extract the whole schema into text files.

It also makes relatively good ER diagrams out of the existing scheme. The only drawback of DataStudio is the user interface, which is a bit clumsy, you won't give up Toad resp. Tora for that. Anyway, it's sold at a price where you can afford it as a secondary tool.

mike_gain

All of our PL/SQL , Schema Creation scripts, update scripts , system and test data scripts are all version controlled using CVS. The best way we have found to make sure all developers save the actual PL/SQL files is to enforce unit testsing in all our tiers and run a continous build process using Cruise Control which builds the database from scrach every 2 hours or so then runs all unit tests against them.

If someone has just been compiling their pl/sql on their local database build, it won't appear in our Cruise Control database and at some point a unit test is is going to fail.

Each team also has thier own database which they can rebuild from scratch and are encouraged to do this every week or so. Again this would highlight that there is a package missing and the rest of the team will start making a fuss to the DB programmer saying that their code has stopped working.

If by some chance or review process hasn't worked an there have been no unit tests written in any tier that will highlight there is a missing package or the team database has never been rebuilt we have a pre-testing QA that checks that the code written matches the requirements. This again builds the databse from scratch, unit tests are written and a run through the new functionality is done. This will catch out any db programmers who do not save and check their pl/sql into CVS.

It should also be noted that we use iterative development and we run through the development cycle every 3 weeks and so have a new test build every 3 weeks. So any mistakes can be easily found and fixed. We can build our whole database to any version in the past right from it's first version.

We have yet to have any problems with our PL/SQL source control but i would echo the statements made by others by saying that it is very , very important to record all changes, who did them, when and why. The database schema will probably outlive all the other tiers in a system so it pays to keep a track of what has changed.

Hope that provides some pointers.