One of the most rewarding parts of back-end web application development is re-writing the same database routines and same JSON export routines over and over. Then changing the requirements and starting over. It's what makes web application developers such well-balanced folks, right?

Unfortunately, in the flux of user requirements—each addition or modification of a table column changing select routines, insertions, validation, exporting, regression tests, and even (especially?) front-end JavaScript—we make mistakes. What BCHS tools beyond the usual can help in this perennial test of our patience?


I wrote openradtool after the tenth hundredth time I started a new BCHS web application roughly as follows:

  1. design an initial table schema (data layout) from a specification;
  2. pull down kcgi-framework as a starting point;
  3. create a set of C structures mirroring the data layout (e.g., kcgi-framework's extern.h);
  4. grind on INSERT, UPDATE, SELECT and DELETE routines using the same tired way of chaining together column schemas with fill and unfill routines (e.g., db.c) connected to the C API;
  5. construct the application logic (e.g., main.c connecting these components and driving my application; and finally
  6. make typo mistakes in SQL, have inconsistent documentation between the SQL and C API, forget parameters, incorrectly validate, and so on—problems that only arise in actually testing the individual parts or having a colleague scratch her head over mismatches.

All of this takes time, which is something in high demand; and my colleagues' patience, which is even more scarce. In case we forget where the real work's at, what happens in the commonplace event that our specification changes during the review phase?

The circle of life work.
  1. Add an additional column to (or modify an existing column of) a database table;
  2. create an additional SQL upgrade script between the last version and the current version (of course, you're using cvs(1)'s tagging facility or other VCS to track your production and development releases);
  3. update the C structures, column definitions, fill and unfill routines (naturally, forgetting to update the documentation);
  4. change the business logic accepting the new field, validating it, and passing it back into the database backend and JSON export; then
  5. change the front-end code (JavaScript, HTML5) to include the new value.

Folks programming in Java or Python have all sorts of fancy tools and frameworks to help them suck data out and send it to the client. (I guess.) When I'm writing in C, I don't. openradtool was a way to automate a lot of the steps above into a single one when using my tools: kcgi, ksql (that is, SQLite), and of course OpenBSD.

Read this and see if it makes sense:

    1 struct user {
    2   field email text limit gt 3 limit le 128 unique comment
    3     "E-mail address.";
    4   field hash password limit gt 6 limit le 128 comment
    5     "Hashed password.";
    6   field id int rowid;
    7   comment "A user within our system.";
    8   search email, hash: name creds comment
    9     "Search for a user by email/password.";
   10 };
   12 struct sess {
   13   field user struct userid comment
   14     "User attached to session.";
   15   field userid:user.id int comment
   16     "User identifier attached to session.";
   17   field token int comment 
   18     "Random token for authentication.";
   19   field id int rowid;
   20   comment "A logged-in user.";
   21   delete id: name id comment 
   22     "Delete session.";
   23   search token, id: name creds comment
   24     "Search by token and identifier.";
   25 };

Hopefully it does. We have two objects: a user and a user's session. As C programmers, you're interpreting them as structs; or if you're more SQL-minded, as tables. Both are correct. Moreover, the snippet defines not only the relationship between two objects as might envision in an SQL database, but also limitations on input data (the limit clause) and documentation (the comment clause). It also specifies some terms to search on, e.g., email and hash for users—passwords are automatically hashed using the crypt_checkpass(3) family.

When run through openradtool, it automates the production of many of the elements in our above graph:

  • SQL: the SQL schema required by the data definitions;
  • C API: not only the header (structures, functions), but the full implementation acting upon the SQL definitions;
  • REST API: a set of functions for exporting the C API into JSON (included in the C API header and implementation); and
  • JavaScript: a set of objects for filling in DOM trees with the defined data.
The simplified process.

The SQL is managed by openradtool—not just generatively, but within the update cycle. It's able to generate the difference between configurations (in this example, one without the foreign key on the session)—allowing you to see how your database has changed between versions. This is particularly useful when rolling out incremental releases. (Of course, not all data layout changes can be realised with SQLite's SQL!)

You'll still need to update your front-end logic, of course, to reflect your new data. And your application logic—obviously. But the rest of the tedious cycle—C API, validators, JSON exporters, etc.—are all handled by openradtool.

For an example of how a backend might look, consider main.c. This handles logging in, logging out, and a simple homepage using the given example. Take, for example, the component where it processes a request's cookies using the openradtool functions for reading from the database:

    1   if (r.page == PAGE_HOME) {
    2     if (r.cookiemap[VALID_SESS_ID] != NULL &&
    3         r.cookiemap[VALID_SESS_TOKEN] != NULL)
    4       us = db_sess_get_creds(r.arg, 
    5         r.cookiemap[VALID_SESS_TOKEN]->parsed.i,
    6         r.cookiemap[VALID_SESS_ID]->parsed.i);
    7     if (us == NULL) {
    8       http_open(&r, KHTTP_403);
    9       json_emptydoc(&r);
   10       db_close(r.arg);
   11       khttp_free(&r);
   12       return EXIT_SUCCESS;
   13     }
   14   }

Upon login, it uses the openradtool-generated output functions to emit JSON:

    1 /*
    2  * Homepage for users.
    3  * Returns 403 if not logged in or not in experiment state.
    4  * Returns 200 otherwise with empty document.
    5  */
    6 static void
    7 sendhome(struct kreq *r, const struct sess *u)
    8 {
    9   struct kjsonreq   req;
   11   http_open(r, KHTTP_200);
   12   kjson_open(&req, r);
   13   kjson_obj_open(&req);
   14   json_sess_obj(&req, u);
   15   kjson_obj_close(&req);
   16   kjson_close(&req);
   17 }

All of the kcgi(3) functions are fully documented, and the generated functions are also documented in kwebapp.db.h. Much easier!

where is openradtool going

Short answer: wherever we want. Longer answer? Most of my immediate intentions are in the TODO.

What will I not do myself in the near-term? Expand to other databases, namely PostgreSQL—though that's really a job for ksql. (I think ksql is due for some care on having a privilege-separated model of operation for easier pledging.) I'd also like it to document the JSON export, for example, with JSON Schema.

Meanwhile, if you're using openradtool for any projects, keep me in the loop!