Re: Make for PgSQL?

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