RE: CVS best practice

[Rick Genter]

An interesting idea. How would you handle the situation where two
different components make different (but compatible) changes to the same
table? We would normally have two scripts for this. For a more concrete
example:

Assume we start with the given table

CREATE TABLE Foo (
  FooID INTEGER PRIMARY KEY,
  BaseValue VARCHAR(100)
)

The next version of component A wants to add an INTEGER column named
"Bar" to the table. The next version of component B wants to add a
DATETIME column named "Quux" to the table. During the development of A
there would be a script that basically says:


AlterFooForA.sql:

IF <column Bar does not already exist in table Foo>
  ALTER TABLE Foo ADD Bar INTEGER


while during B's development there would be a similar script:


AlterFooForB.sql:

IF <column Quux does not already exist in table Foo>
  ALTER TABLE Foo ADD Quux DATETIME


I have a module named A which contains A's source, and a module named B
which contains B's source. It seems like what I would want to do is use
"regular" modules instead of "ampersand" modules. The documentation is
not clear, though; can I do the following in the modules file?


A/SQL Database/Tables/Foo AlterFooForA.sql
A/SQL Database/Tables/Foo2 AlterFoo2ForA.sql

B/SQL Database/Tables/Foo AlterFooForB.sql
B/SQL Database/Tables/Foo2 AlterFoo2ForB.sql


Then I think I can do what I need to do: the submodule SQL under A would
contain the scripts I need to alter the database for A, while the
submodule SQL under B would contain the scripts I need to alter the
database for B.

Otherwise I don't see how we could adopt both structures...

-----Original Message-----
From: Jim.Hyslop [mailto:address@hidden 
Sent: Thursday, July 22, 2004 3:27 PM
To: Rick Genter; address@hidden
Subject: RE: CVS best practice

Rick Genter wrote:
> The system I work on consists of a number components. Each component 
> is a separately built Java application, all referencing portions of a 
> common database. Different components use different tables; some 
> tables are shared by many components, while some are used by a single 
> component. Over the life of the system, schema changes on the same 
> table may have been required by different applications for different 
> purposes.
> 
> We script all of our database changes and store the scripts in our 
> repository, but this has led to some "spirited" debate about 
> repository structure. Our DBA wants to have a database-centric 
> repository hierarchy where all database scripts are stored in a CVS 
> module named for the database to which the script applies. There would

> be submodules for Tables, Stored Procedures, Indexes, etc. I, on the 
> other hand, want scripts associated with a particular application to 
> be stored in "SQL"
> or "Schema" submodule under the module for that application.
In this case, you can have your cake and eat it, too!

Follow the DBA's suggestion: create a completely independent CVS module
that contains all the schema scripts. This keeps all the scripts in one
place, and ensures that the schema is consistent. Whether you go to the
extent of having a subdirectory for Tables, another for Stored Procs,
etc. is between you and your DBA.

In each of the projects that uses the database, add an "ampersand
module"
that points to the schema module to each project in CVSROOT/modules file
that needs it. See the documentation for more details:
https://www.cvshome.org/docs/manual/cvs-1.11.17/cvs_18.html#SEC159

End result: The DBA gets to see all the files in one location, and you
get to see the schema files with each project. If one project changes
the schema, the other projects automatically inherit it. Everyone goes
home a winner!

--
Jim Hyslop
Senior Software Designer
Leitch Technology International Inc. (http://www.leitch.com) Columnist,
C/C++ Users Journal (http://www.cuj.com/experts)