Thread: Make for PgSQL?

Hi all.
As I need to maintain a rather large set of PgSQL scripts, I'd like to
use something like "make" in order to track changes and apply the
"proper" variations to the database.

Scripts have been named so that the lexicographical order of filenames brings
the information about dependencies.

I've been playing with the GNU Make itself but it's quite hard to keep track
of changes and to re-load a single SQL file that changed.

Is there any hint about "best prectices", software tools and the likes?

Many thanks to you all.

--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]

On Thursday 31 May 2007 09:01, Vincenzo Romano wrote:
| As I need to maintain a rather large set of PgSQL scripts, I'd like to
| use something like "make" in order to track changes and apply the
| "proper" variations to the database.
|
| Scripts have been named so that the lexicographical order of filenames
| brings the information about dependencies.
|
| I've been playing with the GNU Make itself but it's quite hard to keep
| track of changes and to re-load a single SQL file that changed.
|
| Is there any hint about "best prectices", software tools and the likes?
|
| Many thanks to you all.

A version control system like Subversion comes to my mind. From there
you might be able to collect all relevant information. But I might be
wrong...

Ciao,
Thomas

--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----

On Thu, 2007-05-31 at 09:01 +0200, Vincenzo Romano wrote:
> Scripts have been named so that the lexicographical order of filenames
> brings
> the information about dependencies.
>
> I've been playing with the GNU Make itself but it's quite hard to keep
> track
> of changes and to re-load a single SQL file that changed.

Expressing the dependencies is the hardest part. Once you have the
dependencies, the make part ought to be straightforward (except for
learning the bugaboos of make). "Keeping track of the changes" might
look something like this make snippet:

.DELETE_ON_ERROR:
.SUFFIXES:
%.log: %.sql
    psql ... -f $< >$@

(Bless the pg folks with mountains of chocolate and mountain dew for
returning meaningful exit codes even for DDL changes, as opposed to,
say, sqlplus.)

The you need to express your dependencies in a way that make can
understand. The most general way to do this is with a list like:

common.log: utils.log
fx1.log: common.log utils.log
fx2.log: fx1.log
etc.

Finally, you'll want a list of all log targets so that you can type
something like "make update" or whatever to reload as needed. You can
get that with, for example:

SQL_FILES:=$(wildcard sqldir/*.sql)
TARGETS:=$(SQL_FILES:.sql=.log)
.PHONY: update
update: ${TARGETS}


> Is there any hint about "best prectices", software tools and the
> likes?

I don't know anything about best practices (ahem). However, it occurs to
me that it wouldn't be hard to move your dependency encoding into the
SQL itself, such as

-- requires: utils.sql common.sql
create or replace function ...

Then you'd automatically generate a file of sql dependencies using a
perl one-liner (or whatever).

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Good points: I was struggling this very direction.

I think that the really good point to encode the dependencies
as comments into the SQL files themselves.

The hard parto is to let "make" follow the dependencies.
If I change a single SQL script I'd need to:
1. drop all objects that are in that file, possibly cascading;
2. drop all objects into files that require the modified one;
3. reload everything that needs reloading.

All automagically. I'll try it right now.

On Thursday 31 May 2007 15:43:55 Reece Hart wrote:
> On Thu, 2007-05-31 at 09:01 +0200, Vincenzo Romano wrote:
> > Scripts have been named so that the lexicographical order of filenames
> > brings
> > the information about dependencies.
> >
> > I've been playing with the GNU Make itself but it's quite hard to keep
> > track
> > of changes and to re-load a single SQL file that changed.
>
> Expressing the dependencies is the hardest part. Once you have the
> dependencies, the make part ought to be straightforward (except for
> learning the bugaboos of make). "Keeping track of the changes" might
> look something like this make snippet:
>
> .DELETE_ON_ERROR:
> .SUFFIXES:
> %.log: %.sql
>     psql ... -f $< >$@
>
> (Bless the pg folks with mountains of chocolate and mountain dew for
> returning meaningful exit codes even for DDL changes, as opposed to,
> say, sqlplus.)
>
> The you need to express your dependencies in a way that make can
> understand. The most general way to do this is with a list like:
>
> common.log: utils.log
> fx1.log: common.log utils.log
> fx2.log: fx1.log
> etc.
>
> Finally, you'll want a list of all log targets so that you can type
> something like "make update" or whatever to reload as needed. You can
> get that with, for example:
>
> SQL_FILES:=$(wildcard sqldir/*.sql)
> TARGETS:=$(SQL_FILES:.sql=.log)
> .PHONY: update
> update: ${TARGETS}
>
> > Is there any hint about "best prectices", software tools and the
> > likes?
>
> I don't know anything about best practices (ahem). However, it occurs to
> me that it wouldn't be hard to move your dependency encoding into the
> SQL itself, such as
>
> -- requires: utils.sql common.sql
> create or replace function ...
>
> Then you'd automatically generate a file of sql dependencies using a
> perl one-liner (or whatever).
>
> -Reece

--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]