[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

Re: Version control of Databases

From: Ryan Schmidt <subversion-2005_at_ryandesign.com>
Date: 2005-06-13 14:51:13 CEST

On 12.06.2005, at 14:30, erymuzuan wrote:

> Yeah you're right, it's not as simple as it seem

I've thought about this too and agree. :-) In particular, using a
tool that compares the "before" state of the database with the
"after" and generates the SQL statements to get from one to the other
is not viable, in the same way that a script to examine your working
copy "before" and "after" and generate the add and delete commands
isn't viable -- the working copy analyzer can't know whether that
unversioned file is new or is a versioned file that was renamed or
copied from somewhere, and the DB analyzer can't know whether that
new field or table is new or whether it was renamed from an existing
one, or if it's new, whether its data was copied from somewhere.

I believe that the developer needs to manually keep track of the SQL
statements necessary to get from A to B. We are currently doing this
in different ways in different projects: in one, we've put all SQL
statements into a single SQL file in the project's root in the
repository. Best idea here I think is to put a text marker into the
file to indicate where each version of the database is. For example,
the structure of the database used by the live version of the system
corresponds to the SQL statements done up to line 200 of the SQL
file, so after line 200, there's a line "-- live server." And on the
development server all the rest of the statements have also been done
already, so at the end of the file, there's another marker "-- dev
server." In another project, we keep the SQL statements in the bug
tracker records for the bugs (or feature requests) they fix (or
implement) -- completely separate from the repository. This has also
worked well.

Another solution I've thought of is this: SQL statements that belong
with a commit should be added as a revprop (say "foo:sql") to that
revision. When you want to deploy a new release of the system, you
gather, in order, all the foo:sql properties from all those revisions
and execute the SQL. Ideally you would also store a second property
(like "foo:sql-undo") which could undo whatever it was that the SQL
did. This way, if you need to downgrade to an older release, you can
pull all the foo:sql-undo properties, in reverse order, and get back
to more or less the state you were in before. Depending on your
confidence in these SQL statements this could even be automated to a
greater or lesser degree in your deployment process.

I'm not sure how best to handle SQL statements that do not relate to
a commit.

To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Received on Mon Jun 13 14:54:15 2005

This is an archived mail posted to the Subversion Users mailing list.