Re: Database Development w/ SVN

Daniel Becroft wrote:
> On Thu, Nov 12, 2009 at 9:53 PM, Tony Sweeney <tsweeney_at_omnifone.com> wrote:
>>
>>> -----Original Message-----
>>> From: Felix Gilcher [mailto:felix.gilcher_at_bitextender.com]
>>> Sent: 12 November 2009 09:19
>>> To: Thomas Harold
>>> Cc: Daniel Becroft; users_at_subversion.tigris.org
>>> Subject: Re: Database Development w/ SVN
>>>
>>> On Nov 12, 2009, at 4:12 AM, Thomas Harold wrote:
>>>
>>>> On 11/10/2009 3:44 PM, Daniel Becroft wrote:
>>>>> Hi all,
>>>>>
>>>>> This is more of a best or general practice question than anything
>>>>> else. How do you manage/version external database schema
>>> and static
>>>>> data changes in an SVN environment?
>>>> Small static tables could be dumped to a .sql type plain text file
>>>> (which is usually just a big SQL INSERT statement) and kept in SVN.
>>>> Figure one file per table.
>>>>
>>>> You could probably put the create table queries in .sql
>>> files as well.
>>>
>>> I usually keep a set of scripts that does the required
>>> changes in svn. This seems to be a pretty standard practice,
>>> you might want to have a look at activerecord migrations
>>> (ruby on rails) or doctrine migrations (php) for an example.
>>> Basically it's a set of sequentially numbered scripts that
>>> each update the database from the previous version to the
>>> next version, doing all table changes and data manipulation
>>> that are required. Usually there's a table in the database
>>> that somehow stores the current version. Migrations may
>>> incorporate the ability to revert the changes if required.
>>> There's some communication involved if multiple persons are
>>> changing the database, the solution to that really depends on
>>> your workflow.
>> There is an open source tool called 'dbdeploy' that we use for the same task. It's not especially branch-aware, but it gets the job done for what we need.
>>
>> http://dbdeploy.com/
>
> Thanks, Tony, that tool looks interesting. Unfortunately, it looks
> like it's SQL only.
>
> With this concept on an SQL system, though, do you try and minimize
> the number of schema updates (as opposed to data updates) that are
> made? Especially with regards to new indexes on existing tables, would
> you create two different changes? Or determine all index changes
> required on such a table, and apply them at once?

Here's another similar tool. I think it is also sql only but it
supports many different database versions and mostly just needs a jdbc
connection. There is some 'best practices' information on the site that
might be helpful even if you can't use the tool:
http://www.liquibase.org/home

-- 
   Les Mikesell
    lesmikesell_at_gmail.com
------------------------------------------------------
http://subversion.tigris.org/ds/viewMessage.do?dsForumId=1065&dsMessageId=2417297
To unsubscribe from this discussion, e-mail: [users-unsubscribe_at_subversion.tigris.org].