> everything is—symbolically—a file

How are you going to make the result of a join in a relational database into a file, symbolically or otherwise?

On plan 9, you'd do something like:

     ctlfd = open("/mnt/sql/ctl", OREAD|OWRITE);
     write(fd, "your query");
     read(fd, resultpath);


     resultfd = open(resultpath, OREAD);
     read(fd, result);
     close(resultfd);

This is similar to the patterns used to open network connections or create new windows.

And how would you use that in a makefile?

Something like this would be ideal.

    /mnt/sql/myjoin:
      echo "<sql query>" > /mnt/sql/myjoin

It's just representing writing and reading a database as file operations, they map pretty cleanly. Keep in mind that Plan 9 has per process views of the namespace so you don't have to worry about other processes messing up your /mnt/sql.

I think you're missing the point. I have a workflow where I have to perform some action if the result of a join on a DB meets some criterion. How does "make" help in that case?

In that case, it doesn't help much. For Plan 9 mk, there's a way to use a custom condition to decide if the action should be executed:

    rule:P check_query.rc: prereq rules
        doaction

Where check_query may be a small shell script:

     #!/bin/rc

     # redirect stdin/stdout to /mnt/sql/ctl
     <> /mnt/sql/ctl {
            # send the query to the DB
            echo query
            # print the response, check it for
            # your condition.
            cat `{sed 1q} | awk '$2 != "condition"{exit(1)}'
     }

But I'm not familiar with an alternative using Make. You'd have to do something like:

    .PHONY: rule
     rule: prereq rules
          check_query.sh && doaction

> You'd have to do something like:

That's exactly right, but notice that you are not actually using make at all any more at this point, except as a vehicle to run

check_query.sh && doaction

which is doing all work.

It's being used to manage the ordering of that with other rules, and to run independent steps in parallel.

OK. So here's a scenario: I have a DB table that keeps track of email notifications sent out. There is a column for the address that the email was sent to, and another for the time at which the email was sent. A second table keeps track of replies (e.g. clicks on an embedded link in the email). Feel free to assume additional columns (e.g. unique ids) as needed. When some particular event occurs, I want the following to happen:

1. An email gets sent to a user

2. If there is no reply within a certain time frame, the email gets sent again

3. The above is repeated 3 times. If there is still no reply, a separate notification is sent to an admin account.

That is a common scenario, and trivial to implement as code. Show me how make would help here.

I wouldn't; I don't think that make is a great fit for that kind of long running job. It's a great tool for managing DAGs of dependent, non-interactive, idempotent actions.

You have no DAG, and no actions that can be considered fresh/stale, so there's nothing for make to help with. SQL doesn't have much to do with that.

> How are you going to make the result of a join in a relational database into a file, symbolically or otherwise?

A file that represents the temporary table that has been created. Naming it is harder, unless the SQL query writer was feeling nice and verbose.

You would probably need another query language, but that would come with time, after people had gotten used to the idea.

With that said, there are NoSQL databases these days whose query language is easily expressed as file paths. CouchDB, for example.

A very simple approach is to use empty marker files to make such changes visible in the filesystem. Say,

    dbjoin.done: database.db
        sqlite3 $< <<< "your query"
        touch $@

you could mount the database as a filesystem

With respect to Make, does the database (mounted as a filesystem) retain accurate information that Make needs to operate as designed (primarily the timestamps). To what level of granularity is this data present within the database, and what is the performance of the database accessed in this way? Will it tell you that the table was updated at 08:40:33.7777, or will it tell you only that the whole database was altered at a specific time?

You're talking about a theoretical implementation of a filesystem with a back-end in a relational database. The question is only whether the information is available.

Say directories map to databases and files map to tables and views. You can create new tables and views by either writing data or an appropriate query. Views and result files would be read-only while data files would be writable. Writing to a data file would be done with a query which modifies the table and the result could be retrieved by then reading the file -- the modification time would be the time of the last update which is known.

Views and queries could be cached results from the last time they were ran which could be updated/rerun by touching them or they could be dynamic and update whenever a table they reference is updated.