Re: [GENERAL] Version/Change Management of functions?
On Fri, Jul 07, 2006 at 02:08:08PM -0600, Michael Loftis <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Version/Change Management of functions?
# [EMAIL PROTECTED] / 2006-07-07 14:08:08 -0600: > --On July 7, 2006 12:35:53 PM + Roman Neuhauser <[EMAIL PROTECTED]> > wrote: > > ># [EMAIL PROTECTED] / 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Version/Change Management of functions?
On Friday 07 July 2006 13:08, Michael Loftis wrote: > --On July 7, 2006 12:35:53 PM + Roman Neuhauser <[EMAIL PROTECTED]> > > wrote: > ># [EMAIL PROTECTED] / 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/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Version/Change Management of functions?
--On July 7, 2006 12:35:53 PM + Roman Neuhauser <[EMAIL PROTECTED]> wrote: # [EMAIL PROTECTED] / 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). ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Version/Change Management of functions?
Jorge Godoy wrote: Kenneth Downs <[EMAIL PROTECTED]> 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. begin:vcard fn:Kenneth Downs n:Downs;Kenneth email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Version/Change Management of functions?
On 7/7/06, Michael Loftis <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Version/Change Management of functions?
Kenneth Downs <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 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. -- "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 begin:vcard fn:Kenneth Downs n:Downs;Kenneth email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Version/Change Management of functions?
# [EMAIL PROTECTED] / 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
