Using a Makefile during database development

[Bill Moran]

Hello again,

I'm developing a program based on PostgreSQL. It's consists of tables,
constraints, _many_ stored procedures, 56M of test data, and a client
app written in C.

To help me with all of this, I obviously have a Makefile. I can do
"make" to rebuild the C app (assuming I haven't added any bugs) and I
can do "make database" to create the database tables, and "make funcitons"
to add the stored procedures to the database, and I'll soon be adding
"make test" to install the test data. When I get to the constraints,
I'll make a target for it as well.

There's probably no reason for me to explain the advantages of these
targets during development :)

However, in order to do "make database" I have to first drop the current
database. In order to do "make functions" I (currently) have to drop
and recreate the database. I figure I'll have to do the same for
"make test" once I've added that target to the makefile.

I'm wondering if anyone has some examples making targets like this
more automatic. For example, if I do "make db-all" would test the various
files that have the table, constraint, stored procedure, and test data
in them and if any had been updated, only then rebuild that portion of
the database. Frankly, there's enough stuff already that it's getting time
consuming to rebuild everything when I make changes, and I get
interrupted in my work too often to easily keep track of what I've
updated since I last did it.

The problem I'm hitting is this: how can I teach make to know when a
particular file is newer than the data in the database? Until I can
figure out a system for that, I can't really automate any more of the
process.

Anyone have a technique they've been using?

--
Bill Moran
Potential Technologies
http://www.potentialtech.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Tom Lane]

Bill Moran <wm****@potentialtech.com> writes:

The problem I'm hitting is this: how can I teach make to know when a
particular file is newer than the data in the database?

AFAIK there's no direct way to do that; all of make's decisions are
based on existence and mod times of files, so you can't persuade it to
test directly for SQL-level conditions.

However, this sort of problem comes up in many contexts, and make users
have developed a standard solution: you create or touch an empty
"timestamp" file when you do an action such as updating the database
from a particular collection of source files. The mod time of the
timestamp file can then serve as the comparison value telling make
whether to do it again. A typical rule would look like:

db_update.stamp: somefile.sql someotherfile.sql
psql mydb -f somefile.sql
psql mydb -f someotherfile.sql
touch db_update.stamp

You make one stamp file for each action you might or might not need to
do, and then user-level targets look like

update: db_update.stamp ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Bill Moran]

Tom Lane wrote:

Bill Moran <wm****@potentialtech.com> writes:

The problem I'm hitting is this: how can I teach make to know when a
particular file is newer than the data in the database?

AFAIK there's no direct way to do that; all of make's decisions are
based on existence and mod times of files, so you can't persuade it to
test directly for SQL-level conditions.

However, this sort of problem comes up in many contexts, and make users
have developed a standard solution: you create or touch an empty
"timestamp" file when you do an action such as updating the database
from a particular collection of source files. The mod time of the
timestamp file can then serve as the comparison value telling make
whether to do it again. A typical rule would look like:

db_update.stamp: somefile.sql someotherfile.sql
psql mydb -f somefile.sql
psql mydb -f someotherfile.sql
touch db_update.stamp

You make one stamp file for each action you might or might not need to
do, and then user-level targets look like

update: db_update.stamp ...

Thanks, Tom.

I had considered this approach, but it seemed rather clunky. The way you
describe it doesn't make it sound so bad, though.

I was hoping there was some way to track this using a backticked command
or something.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org