Re: Version/Change Management of functions? - Mailing list pgsql-general

On 7/7/06, Michael Loftis <mloftis@wgops.com> wrote:
> OK I know this is an odd question but I'm working on an app that will rely
> more and more on database driven functions, and while the app's source is
> in SVN, and I intend for the source of the SQL scripts to also be there, I
> was wondering...what are people doing for version control and change
> management on their custom (esp PL/pgSQL and say PL/Perl) functions?

Keeping your sql procs in cvs/svn is a great idea, and is (IMHO) the
only way to really do it right if you have a large project.  I have
some suggestions that may help you get started.

1. keep your database procedures different schemas in the database.
do not put tables or views in these schemas.  for example, if you are
building an accounting application, make a schema called, ar (accounts
recevable), ap (payables), gl, etc.  put all sql code in appropriate
schemas.  These should mirror your folder structure in your code
repository.  Since schemas can only go one level deep, try and
structure your code base to go only one level deep.

2. For each schema/folder, maintain a sql build file or some type if
make file which uploads the code to the database.  you could get fancy
with this, or just do a simple cat *.sql | psql yadda in a one line
shell script.  The important thing is to have an automatic way of
reconstructing your database.

3. ban your developers from editing directly in the database.  this
means no pgadmin (for ddl), and no direct ddl in the shell.  This
bypasses the souce control.  While it is fine for a development test
database, all uploads to production databse should go through the
build system.  It is ok to copy/paste from .sql files into
shell/pgadmin however.

4. an application code/database code, make a habit of fully qualifying
the function e.g.
select ar.update_invoices();

5. when you make updates to a production sysem, just include (\i) your
.sql files that have been updated with the change.  dml can be inlined
however.
e.g.
-- yadda_1.1.sql
-- converts yadda from 1.0 to 1.1
\i ../../ar/update_invoices.sql
\i ../../ap/delete_customer.sql
update foo set bar = 1;

6. I would suggest, for extra safety purposes, doing a full
schema-only dump on cron and inserting into svn on a daily basis.

7. views and other table dependant objets (triggers but not trigger
functions) should be stored in the same schema as the table(s) they
operate over.  Unlike functions they therefore can not match 1-1
fodler correspondence if you have multiple copies of same table in
different schemas.

Putting all this together, I would suggest a folder structure like
yadda
  ar
    funcs
      update_invoices.sql
    views
      achived_invoices.sql
    build_ar.sql
   ap
      funcs
      views
      build_ap.sql
   updates
      yadda_1.0.sql
      yadda_1.1.sql

merlin