1

Short Version

Like the question title says, I want a tool like make which will take a recipe file and use it to automatically rebuild a collection of interdependent tables in a relational database. Does such a tool exist?

Longer Version

I'm working on a project that involves many people generating (and regenerating) many different tables in a relational database. During the development process, we are frequently regenerating some of these tables from scratch. Many of these tables are dependent on each other, so that when one table is regenerated, all of its dependent tables also need to be regenerated - which can be very time-consuming.

This situation feels analogous to a typical situation in software development, where code needs to be recompiled. In the software development world, there are automated build systems which keep track of these dependencies and speed up the recompilation process - make being (possibly) the most well-known of these.

The make tool takes a "recipe" file which lists the dependencies between different files, checks the state of the files to see which ones have been modified since the last compilation, and updates (recompiles) only those files which need to be. I want something like that for the generation of database tables.

Does such a tool exist?

1

Well, you could use make itself, I guess. You haven't specified what database you are using, but I assume you have a command that you can execute from your operating system shell and that parses a SQL file, regenerating the table(s).

If that's the case, assuming (just for example) that you run MySQL on Linux, you can create a bash script like this:

#!/bin/bash
SQLFILE="$1"
cat "$SQLFILE" | mysql
touch "$SQLFILE.regenerated"

Now you should write your table definitions one per SQLFILE, and define your rules and dependencies in a common Makefile that uses your script as "compiler" and *.regenerated files as "compiled" stub files that make looks at in order to figure out which "source" files need to be "recompiled" (executed again).

Please note that I've not debugged my simple script, but you get the idea.

  • 1
    Thanks for the response! This approach wouldn't take into account changes that were made to the tables, though - right? – igal May 16 at 19:59
  • Correct, but I did not understand that requirement. Maybe you can automate the deletion of sometable.regenerated file when sometable changes in a way that requires it to be regenerated? – Lucio Crusca May 16 at 20:03
  • You're right. This wasn't a perfect analogy. The way I was thinking about it, the tables correspond both to source files and to targets. In any event, maybe just using make like this would work. I need to think about this more carefully. – igal May 16 at 20:11
  • When something changes that makes the table dirty, you probably need to touch "$SQLFILE" too, in order to convince make that other files that depend on it need to be recompiled as well. Maybe you can even touch "$SQLFILE" and nothing else. Deleting the "$SQLFILE.regenerated as I suggested is actually useless. – Lucio Crusca May 16 at 20:19
  • Yeah. The thought of using make itself actually occurred to me. I did something like this with a filesystem-based data store awhile ago. That situation was simpler, because of one of the data files changed then make would know about it in the usual way. But in this case what I would want is for make to ask the database to check the modification timestamp on the table. Is there a way to have make do that? – igal May 16 at 20:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.