Re: pl/sql code maintenance

meinhfxGARBAGE_at_hotmail.com (tony) writes:

> We have what is probably a typical development, test & production
> set-up, ie., a separate box and database for dev, one box for both
> test & prod each with separate databases. My question has to do with
> how best to maintain code in all areas where there are several
> developers / support people all working in each area. Since we can
> retrieve the source from the databases it seems to me that there is
> really no need to keep a current version of source thereby avoiding
> the situation where developer a moves source from test to prod but
> forgets to compile then developer b takes the source from prod back to
> test to work on a bug. Pretty fundamental stuff, but how do you
> handle pl/sql code?
>

Certainly not as relaxed as your senario seems to indicate!

Actually, we are in the middle of analysis of our procedures at the moment. Currently, we have a similar setup - development and test box with different databases and a production box. When developers finish something, it is passed to the testers who evaluate and do qa. Once they are happy, the developer creates a "promote" job which specifies what files need to be moved to production and what scripts need to be run to perform the production. This job is handed to the DBA who is the ONLY person able to install new code into the production database. On a semi-regular basis, the development and testing systems are "refreshed" from the production system.

The main part missing from our process at present is some well defined version control processes. I've been pushing for something like CVS, but have been facing resistance from others who believe its not necessary because the code is stored in the database. My response to this is to ask what happens when you find a bug which has existed for at least two promotes of the source code or you realise changes made at least two promotes ago have introduced something undesirable and you need to revert to the version which was running prior to the two promotes? Without some form of version control, that earlier version is lost. Another possible senario is that it has become evident that some change has occured which is not desirable, possibly from a side effect, and you need to determine when this change occured. In both these situations, a good version control mechanism which keeps a history of changes is vital - you can revert to any prior version or you can perform analysis of what has changed to determine when some feature/side-effect or whatever was introduced/lost etc.

You might say that all of the above is the result of inadequate qa testing etc. Thats possibly true - but you would have to be the worlds biggest optimust if you think qa will always be sufficient and will never miss something. The bottom line, with any code, you should have some sort of version control happening. Its pretty much the same principle as having multiple historical backups - only a complete idiot would run a production system which only has a single backup and the same goes for code development IMHO.

I thik a lot of people avoid version control because they either think it is too expensive or too complicated. However, there are some very good open source version control systems (e.g. CVS, RCS) and it only needs to be as complicated as you need it. In later versions of oracle (9i,possibly 8i) you can create triggers which fire whenever you do a create or replace operation. Such a trigger could extract a copy of the existing code and place it in a version control table, timestamp it you have a simple, but fairly effective version control of much of your code (though it does not have the full functionality of something like CVS.).

We are thinking of adopting CVS with different "branches" for development, testing and production (possibly just development/production, possibly just production - not sure yet). The process could be something like

1. Production branch contains all code which is in production.
2. Development branch contains code which developers have completed and which is waiting for qa testing
3. Testing contains code which has been qa tested and is waiting for promotion to production.

The main problem with developing good version control is making sure its not too complicated and that it is easily accessable to all developers, testers and dbas. If its too complicated or not easily accessibly, procedures whill break down - code not being checked in properly or developers/testers not working on the corect version etc. Possibly the only thing worse than not having some form of version control is working under the mistaken belief that everyone is using version control and the sources in the repositry are an accurate representation of your code.

Rant over - not too hard to tell work is winding up here already :-)

tim

-- 
Tim X.
tcross (at) northnet com au