Обсуждение: Version/Change Management of functions?
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? -- "Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds." -- Samuel Butler
# 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
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. > > -- > "Genius might be described as a supreme capacity for getting its > possessors > into trouble of all kinds." > -- Samuel Butler > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings
Вложения
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>
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:
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.
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.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.
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.