I'm working on the initial infrastructure design for a data-mining project backed by PostgreSQL. It's going to be split into a series of modules with the actual database interaction wrapped by a generic API to translate project-specific information into statements (e.g. select_by_coord(double, double), which would convert the data and assemble/send a SELECT statement.) I'll have to regularly assemble somewhat complex statements and I'd rather not deal directly with strings here.

My initial idea is to create some sort of heterogenous binary-tree system that could be traversed to compile it into a statement. For example, one might start with a "SELECT" node as the base and branch with additional nodes for the possible conditions applied to SELECT statements. The base could then be fed to a traversal function to compile it into a string (or maybe just operator std::string, etc.)

I'm looking into libpqxx; however, it looks like it's just a memory-management abstraction layer with the same old text-based statements. On the one hand, I don't want to go implementing the SQL language in what's essentially a code generator, but I'd nearly do that trying to develop a maintainable system of assembling statements from API calls.

Hopefully someone here has tried this or knows of a good project under a non-GNU license. Otherwise, I have a C++ binary-tree-like library that would work well with this (setting aside the whole extent-of-the-language barrier.) If anyone else is interested, I'd be willing to go in on a new project, although time is getting more and more sparse these days.
Kevin Barry

PS The objective is to not only make my life easier, but to convince the maintainers of the subcomponents that their lives are easier, and that anyone who takes over for me as lead developer is happy they don't have a mess to maintain. I'd try to externalize any statement assembler I came up with, but I don't like the idea of building in dependencies with uncertain futures.

PPS Actually, the more I think about it the easier it sounds. A std::ostream passed recursively can go a long way, and I suppose I don't have to provide validation initially, if ever.

An ambitious undertaking, to be sure. Did you consider some sort of template approach?
I'm just sort of winging it here, I'm not sure how I would tackle this.

I was thinking about using my hierarchy framework to start with. It's essentially a binary-tree library meant for automated assembly and exporting the tree as formatted data. Each node is derived from a base class, so I'd derive a new node type for each of the "stackable" statements. Each one would have a variable for its required/optional parameters (e.g. table name, column name) and a list of names of other statements that can be embedded. The framework allows simple, pre-insertion checks so that a node can reject insertion of an another node as a child. Lastly, the framework allows export of the tree via an interface that passes an output object recursively to the nodes; each node would export its name and data, then its children (or whatever order the statement requires,) and the result would be a text string ready to pass to an SQL API.

That shouldn't be too difficult a task; just time consuming while I find the time to track down and implement specifications for each command. But once I know what's unique to e.g. SELECT, I can just specify what "add-ons" it allows, and define those separately. What I ponder at this point is how to hide this with an interface; I want to be able to assemble statements one step at a time. My first thought is to use a global-function interface and provide a handle to an instance of a class defined internally, then have it passed to modification functions to add branches to the tree corresponding to sub-statements. The majority of the work is already done by the library.
Kevin Barry

PS There's also the matter of quoting, escaping, and validating user-provide strings, which I don't know if I want to do. Right now the plan is to fill a requirement (mine) and later include third-party considerations if anyone expresses interest.

It looks like lisp might be of help here, although I might just use it directly with PostgreSQL rather than make it generic.
Kevin Barry

PS Does anyone use postmodern?

PPS Looks like cffi will be helpful for interfacing with C.