Thread: Question: How do you manage version control?

Question: How do you manage version control?

From
Bryan Montgomery
Date:
Hello,
So we've been reviewing our processes and working on improving them. One area we've been lacking is a procedure to version control our database functions, table changes, static data etc.
 
I'm curious how others do it. Ideally, we want it to be part of our release / build process so that we deploy functions.
 
However, how do you handle dependancies?
 
We've also used patch files if we need to modify tables - these are typically written to only run once, ie alter table add column x int. Any thoughts on putting this in to a process so that it can be run mutliple times without any issue?
 
Thanks,
Bryan.

Re: Question: How do you manage version control?

From
Chris Angelico
Date:
On Sat, Jun 2, 2012 at 1:28 AM, Bryan Montgomery <monty@english.net> wrote:
> Hello,
> So we've been reviewing our processes and working on improving them. One
> area we've been lacking is a procedure to version control our database
> functions, table changes, static data etc.
>
> I'm curious how others do it. Ideally, we want it to be part of our release
> / build process so that we deploy functions.
>
> However, how do you handle dependancies?
>
> We've also used patch files if we need to modify tables - these are
> typically written to only run once, ie alter table add column x int. Any
> thoughts on putting this in to a process so that it can be run mutliple
> times without any issue?

At work, we have perfectly linear database versioning, so I
implemented a simple patchlevel system. The database has a singleton
config table with (among other things) PatchLevel INTEGER NOT NULL,
and every change to the database increments it. We have a single
autopatch script whose logic looks something like this:

level=query("select patchlevel from config");
query("begin");
switch (level)
{
  default: explode("Incompatible source code and database, terminating");
  case 1:
    say("AutoPatch: Adding FooBar column to Quuxification table");
    query("alter table quux add FooBar integer not null default 0")
  case 2:
    say("AutoPatch: Creating accountancy tables");
    query("create table ........");
    query("create table ....");

  //Add new patch levels here
    query("update config set patchlevel=3");
  case 3:
    break;
}
// ... other code here, which will notice if the transaction's broken
query("commit");


This script is safe to run multiple times, and is in fact quite
efficient for that case (as it does just one query and then
terminates). The transaction also protects against code bugs or other
issues, and will not half-way-patch a system.

ChrisA

Re: Question: How do you manage version control?

From
Thomas Kellerer
Date:
Bryan Montgomery wrote on 01.06.2012 17:28:
> So we've been reviewing our processes and working on improving them. One area we've been lacking is a procedure to
versioncontrol our database functions, table changes, static data etc. 
> I'm curious how others do it. Ideally, we want it to be part of our release / build process so that we deploy
functions.
> However, how do you handle dependancies?
> We've also used patch files if we need to modify tables - these are typically written to only run once, ie alter
tableadd column x int. Any thoughts on putting this in to a process so that it can be run mutliple times without any
issue?


We have quite good experience with Liquibase to manage the DB scripts

Regards
Thomas


Re: Question: How do you manage version control?

From
Ralf Schuchardt
Date:
Hello,

am 01.06.2012 um 17:28 schrieb Bryan Montgomery:

> So we've been reviewing our processes and working on improving them. One area we've been lacking is a procedure to
versioncontrol our database functions, table changes, static data etc. 

we use a very basic system since a few years, consisting mainly of shell scripts and sql scripts processed by psql.

> I'm curious how others do it. Ideally, we want it to be part of our release / build process so that we deploy
functions.
>
> However, how do you handle dependancies?

The code for every recreatable object (i.e. views, functions, maybe types) is stored in its own file. It includes also
adrop statement for these objects and formal comments to declare dependencies between the files. The files are
processedwith a small script that extracts the dependency declarations and writes a create and drop script for all
objectswhile maintaining the correct order. 

> We've also used patch files if we need to modify tables - these are typically written to only run once, ie alter
tableadd column x int. Any thoughts on putting this in to a process so that it can be run mutliple times without any
issue?

Our database has a "versions" table, containing the version (and date) of applied patch files. Every patch file checks
thecurrent version in the database and throws an exception, when it does not match its expected version. 
The directory with the recreatable objects is versioned along the patch files.


Regards
Ralf

Re: Question: How do you manage version control?

From
Robert Gravsjö
Date:

> -----Ursprungligt meddelande-----
> Från: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] För Ralf Schuchardt
> Skickat: den 1 juni 2012 18:24
> Till: pgsql-general
> Ämne: Re: [GENERAL] Question: How do you manage version control?

I was using a very simplistic approach a while back. I kept the DDL to create current version from scratch in one file,
the DDL to upgrade from last schema version in another and then a small shell script to run the upgrade.

I kept the version number in a table for the shell script to check and abort if schema version didn't match and then
update after successful upgrade.

All files were kept under version control and if I needed older versions of the upgrade file I just browsed the
history and got it from there.

Very simple, no special tools and easy to run and understand for all developers (even for the "I won't go near
sql"-ones).

Working with branches makes it a bit harder since the database patch has to be manually handled when
merging.

Regards,
roppert

>
> Hello,
>
> am 01.06.2012 um 17:28 schrieb Bryan Montgomery:
>
> > So we've been reviewing our processes and working on improving them.
> One area we've been lacking is a procedure to version control our database
> functions, table changes, static data etc.
>
> we use a very basic system since a few years, consisting mainly of shell scripts
> and sql scripts processed by psql.
>
> > I'm curious how others do it. Ideally, we want it to be part of our release /
> build process so that we deploy functions.
> >
> > However, how do you handle dependancies?
>
> The code for every recreatable object (i.e. views, functions, maybe types) is
> stored in its own file. It includes also a drop statement for these objects and
> formal comments to declare dependencies between the files. The files are
> processed with a small script that extracts the dependency declarations and
> writes a create and drop script for all objects while maintaining the correct
> order.
>
> > We've also used patch files if we need to modify tables - these are typically
> written to only run once, ie alter table add column x int. Any thoughts on
> putting this in to a process so that it can be run mutliple times without any
> issue?
>
> Our database has a "versions" table, containing the version (and date) of
> applied patch files. Every patch file checks the current version in the
> database and throws an exception, when it does not match its expected
> version.
> The directory with the recreatable objects is versioned along the patch files.
>
>
> Regards
> Ralf
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: Question: How do you manage version control?

From
Chris Travers
Date:
On Fri, Jun 1, 2012 at 8:28 AM, Bryan Montgomery <monty@english.net> wrote:
> Hello,
> So we've been reviewing our processes and working on improving them. One
> area we've been lacking is a procedure to version control our database
> functions, table changes, static data etc.
>
> I'm curious how others do it. Ideally, we want it to be part of our release
> / build process so that we deploy functions.

We use subversion.  The main schema and static data is in one file.
The procedures split into modules.

When a new version is rolled out, the schema and static data is not
touched, and the procedure modules are all reloaded in a particular
order specified in a text file (we call that file LOADORDER).  The
final module to run is named Fixes.sql and more on that below.  Fixes
is basically the cumulative set of database patches so we are
guaranteed to get into a specific state from any prior version.

>
> We've also used patch files if we need to modify tables - these are
> typically written to only run once, ie alter table add column x int. Any
> thoughts on putting this in to a process so that it can be run mutliple
> times without any issue?

Sure.  This does add some errors to database logs but with some
parameters I think it's possible.  The key thing is that every patch
will run in order on every upgrade.  So any patches that fail because
they have already been run we expect to fail.

Each patch runs in its own transaction.  Patches may change static
information but they need to do it in a way that is multi-run safe.
"update foo set bar = bar + 1" is not a good thing to put in a patch
of this sort.   Every patch MUST either fail and roll back if already
run or have no impact if already run.

This does add some errors into the log files, however it also
guarantees a consistent end-point regardless of where you begin along
the prior versions.

Best Wishes,
Chris Travers

Re: Question: How do you manage version control?

From
Brendaz
Date:
So we've been reviewing our processes and working on improving them. One area
we've been lacking is a procedure to version control our database functions,
table changes, static data etc I'm curious how others do it. Ideally, we
want it to be part of our release / build process so that we deploy
functions.


A true end to end Database Change Management best practice process should
include two aspects of database development; the act of version control and
the act of deployment and these steps must be in sync with each other. Just
make sure that your DCM process meets all the necessary best practice
requirements for DCM:
1.    Change Policy Enforcement – every change is documented. Locking mechanism
doesn’t allow for any object changes to be made outside of the check in
/check out.
2.    End to end change management solution that enables the deploy phase to
connect to the change history.
3.    All of the 3 database code types are managed and controlled.
a. Schema structure
b. Business Logic
c. Lookup or reference date


Read more here about DCM  http://bit.ly/KE7n9A http://bit.ly/KE7n9A



Robert Gravsjö wrote
>
>> -----Ursprungligt meddelande-----
>> Från: pgsql-general-owner@ [mailto:pgsql-general-
>> owner@] För Ralf Schuchardt
>> Skickat: den 1 juni 2012 18:24
>> Till: pgsql-general
>> Ämne: Re: [GENERAL] Question: How do you manage version control?
>
> I was using a very simplistic approach a while back. I kept the DDL to
> create current version from scratch in one file,
> the DDL to upgrade from last schema version in another and then a small
> shell script to run the upgrade.
>
> I kept the version number in a table for the shell script to check and
> abort if schema version didn't match and then
> update after successful upgrade.
>
> All files were kept under version control and if I needed older versions
> of the upgrade file I just browsed the
> history and got it from there.
>
> Very simple, no special tools and easy to run and understand for all
> developers (even for the "I won't go near sql"-ones).
>
> Working with branches makes it a bit harder since the database patch has
> to be manually handled when
> merging.
>
> Regards,
> roppert
>
>>
>> Hello,
>>
>> am 01.06.2012 um 17:28 schrieb Bryan Montgomery:
>>
>> > So we've been reviewing our processes and working on improving them.
>> One area we've been lacking is a procedure to version control our
>> database
>> functions, table changes, static data etc.
>>
>> we use a very basic system since a few years, consisting mainly of shell
>> scripts
>> and sql scripts processed by psql.
>>
>> > I'm curious how others do it. Ideally, we want it to be part of our
>> release /
>> build process so that we deploy functions.
>> >
>> > However, how do you handle dependancies?
>>
>> The code for every recreatable object (i.e. views, functions, maybe
>> types) is
>> stored in its own file. It includes also a drop statement for these
>> objects and
>> formal comments to declare dependencies between the files. The files are
>> processed with a small script that extracts the dependency declarations
>> and
>> writes a create and drop script for all objects while maintaining the
>> correct
>> order.
>>
>> > We've also used patch files if we need to modify tables - these are
>> typically
>> written to only run once, ie alter table add column x int. Any thoughts
>> on
>> putting this in to a process so that it can be run mutliple times without
>> any
>> issue?
>>
>> Our database has a "versions" table, containing the version (and date) of
>> applied patch files. Every patch file checks the current version in the
>> database and throws an exception, when it does not match its expected
>> version.
>> The directory with the recreatable objects is versioned along the patch
>> files.
>>
>>
>> Regards
>> Ralf
>> --
>> Sent via pgsql-general mailing list (pgsql-general@) To make
>> changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Sent via pgsql-general mailing list (pgsql-general@)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Question-How-do-you-manage-version-control-tp5710978p5711121.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.