Re: Make for PgSQL?

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]