Re: Database schema version control techniques

In article <CH06t4.6yM@jabba.ess.harris.com> spw@pls.hisd.harris.com writes: > >What aides are there to maintain some version control on the database schemas >as this application develops?? At our site, we have separate development and production databases. All developers have DBA privilege in the development db and can alter it as needed. This poses no problems since our shop is small. Potential changes to the db are discussed beforehand. Each table has a "source" file containing the SQL CREATE TABLE statement that creates the table. This file is treated like any other source file and checked into SCCS. When a table's layout is changed, its CREATE script is changed to reflect the new layout and delta'ed into SCCS. This provides us with a convenient place to document each table's layout. We have one person who acts as a Configuration Manager. Only the CM can change the production environment, which includes being able to alter the production db or install production executables and control files. It is the CM's responsibility to insure synchronization between SCCS and the production db's and executables. In actual implementation, we have a separate login-ID for production configuration activities. Even though the primary CM is also a developer, he must switch to the CM/DBA login-ID to change the production environment. Working in a GUI, that's not too much of a hassle since you can just pop open the DBA window. Having a separate CM/DBA login-ID causes you to think a little bit more before changing the production environment. It also allows you to pass configuration duties easily to a secondary CM when the primary is away. We do mainly I4GL development using make/SCCS/c4gl. The one tool that I wish we had was some way to automatically determine all executables that need to be recompiled whenever a given table is altered. I've thought about trying to kludge something up using make dependencies on the CREATE script files, but given the subtleties of the way make/SCCS works here, I'm not sure it would work. Someone posted something long ago saying they had developed such a utility, but I haven't seen anything recently. If anyone has something like that they'd be willing to share, please let me know. Hope this helps, Walt. -- Walt Hultgren Internet: walt@rmy.emory.edu (IP 128.140.8.1) Emory University UUCP: {...,gatech,rutgers,uunet}!emory!rmy!walt 954 Gatewood Road, NE BITNET: walt@EMORY Atlanta, GA 30329 USA Voice: +1 404 727 0648