Version/Change Management of functions?

Michael Loftis 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?

We went for generating all server-side code out of a data dictionary.
This makes for a significant change in the way change management is handled.

In this scenario change management becomes the analysis of "before" and
"after" data dictionaries. If the changes are all valid, build the code.

Kenneth Downs <ken@secdat.com> writes:

We went for generating all server-side code out of a data dictionary. This
makes for a significant change in the way change management is handled.

In this scenario change management becomes the analysis of "before" and
"after" data dictionaries. If the changes are all valid, build the code.

Ken, could you explain it a bit better? I think this is an interesting idea.

--
Jorge Godoy <jgodoy@gmail.com>

# mloftis@wgops.com / 2006-07-06 22:41:27 -0600:

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?

Well, "people" use a version control system.

Do you have any specific questions?

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

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

Jorge Godoy wrote:

Kenneth Downs <ken@secdat.com> writes:

We went for generating all server-side code out of a data dictionary. This
makes for a significant change in the way change management is handled.

In this scenario change management becomes the analysis of "before" and
"after" data dictionaries. If the changes are all valid, build the code.

Ken, could you explain it a bit better? I think this is an interesting idea.

Sure. To start off I'd say I'm one of those "biz rules belong in the
server" guys. My guess is we are on the same page there so we'll take
that as a given.

So anyway, some years ago I joined an existing project and was
eventually promoted to systems architect. Along the way I developed
their change management system from scratch (we had more salary dollars
than tools dollars). The "Aha!" moment came when I realized what may
seem obvious to many, which was that you can never, nohow, noway, never
prove ahead of time that any particular piece of code was not going to
break something. You can't even prove it will do what anybody claims.

I wanted a way to know by analysis, just by looking, that any particular
change to a spec would work. That is, it would do what it was supposed
to do, without stopping other things from doing what they were supposed
to do.

It so happens you can have this if you generate your code out of a spec
that is itself data. The spec has to be comprehensive, it can't just be
columns and tables. You need to be able to specify security and
derivations all in one place, that is the only way to specify all
business rules in a single place.

There are two major things you can do to make sure a spec is workable
before you start generating DDL and triggers.

First, you look for mistakes in the spec itself, such as duplicate
column names in tables, references to non-existent tables, and so forth.

Second, you look for mistakes or impossibilities in the delta-spec, the
changes to the spec. For instance, if column COL1 is char(7) and the
new spec has it listed as INT, you can stop there and tell the person
the change is not valid.

Futhermore, you can then do really cool things like generate a report of
what *would* happen if you did an upgrade, such as the creation of new
tables, changes in formulas for existing columns, new cascades, changes
in definitions of keys (added a delete cascade, removed a delete
cascade), and then give it to the customer to sign. Ha! I love that one :)

What falls out of all of this for free is that once you have that data
dictionary you don't have to code maintenance forms anymore, because a
library file can generate any maintenance from from the dictionary
description of a particular table.

So anyway, that's the tip of the iceberg on that. Once you go to a
dictionary-based generation system, it actually changes a lot of how you
do things, not just change management.

--On July 7, 2006 12:35:53 PM +0000 Roman Neuhauser <neuhauser@sigpipe.cz>
wrote:

# mloftis@wgops.com / 2006-07-06 22:41:27 -0600:

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?

Well, "people" use a version control system.

Do you have any specific questions?

Yes, how exactly do you use it. Since there's no way to directly control
whats in the DB via a VCS, further, how do you verify that what is in the
DB is also in the VCS, etc? (I'm intentionally asking a bit of a 'dumb'
question because I really want to find out exactly what others are doing).

On Friday 07 July 2006 13:08, Michael Loftis wrote:

--On July 7, 2006 12:35:53 PM +0000 Roman Neuhauser <neuhauser@sigpipe.cz>

wrote:

# mloftis@wgops.com / 2006-07-06 22:41:27 -0600:

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?

Well, "people" use a version control system.

Do you have any specific questions?

Yes, how exactly do you use it. Since there's no way to directly control
whats in the DB via a VCS, further, how do you verify that what is in the
DB is also in the VCS, etc? (I'm intentionally asking a bit of a 'dumb'
question because I really want to find out exactly what others are doing).

I take text based schema dumps using the same file name each time and commit
them so that subversion can tell me the differences.

Joshua D. Drake

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

# mloftis@wgops.com / 2006-07-07 14:08:08 -0600:

--On July 7, 2006 12:35:53 PM +0000 Roman Neuhauser <neuhauser@sigpipe.cz>
wrote:

# mloftis@wgops.com / 2006-07-06 22:41:27 -0600:

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?

Well, "people" use a version control system.

Do you have any specific questions?

Yes, how exactly do you use it. Since there's no way to directly control
whats in the DB via a VCS, further, how do you verify that what is in the
DB is also in the VCS, etc?

Simply: there's no "the DB". Developers have no access to the
customer installation(s), and putting things into the VCS is the
only way for them to produce code, which includes DDL. What's not
in the VCS (or generaged during the release process from tools that
are tracked) cannot be installed from the tarball.

Whether you produce releases for general consumption like
the PostgreSQL project does or your installed base consists of
a single web server, whether you produce tarballs or run "svn up"
on the single Apache box: that's an irrelevant detail of the release
process.

Each release should contain DDL/DML needed to upgrade the database
from the previous release. The developer who wants to change the
schema must provide the ALTER statements.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

On Fri, Jul 07, 2006 at 02:08:08PM -0600,
Michael Loftis <mloftis@wgops.com> wrote
a message of 28 lines which said:

Since there's no way to directly control whats in the DB via a VCS,
further, how do you verify that what is in the DB is also in the
VCS, etc?

This is not a PostgreSQL-specific problem, not even a DB-specific
problem. How to you verify that the code installed in /usr/local/bin
is also in the VCS? You rely on procedures, automated procedures
(make, scons, ant), firing of the violators, etc.