Download your favorite Linux distribution at LQ ISO.
Go Back > Forums > Non-*NIX Forums > Programming
User Name
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.


  Search this Thread
Old 02-25-2009, 11:44 PM   #1
Senior Member
Registered: Sep 2004
Distribution: FreeBSD 9.1, Kubuntu 12.10
Posts: 3,078

Rep: Reputation: Disabled
SQL statement assembler for C++?

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.

Last edited by ta0kira; 02-25-2009 at 11:52 PM.
Old 02-27-2009, 06:33 PM   #2
Registered: Dec 2007
Location: Slightly left of center
Distribution: slackware
Posts: 276
Blog Entries: 2

Rep: Reputation: 44
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.
Old 02-27-2009, 09:41 PM   #3
Senior Member
Registered: Sep 2004
Distribution: FreeBSD 9.1, Kubuntu 12.10
Posts: 3,078

Original Poster
Rep: Reputation: Disabled
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.

Last edited by ta0kira; 02-27-2009 at 09:50 PM.
Old 03-07-2009, 02:16 AM   #4
Senior Member
Registered: Sep 2004
Distribution: FreeBSD 9.1, Kubuntu 12.10
Posts: 3,078

Original Poster
Rep: Reputation: Disabled
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.

Last edited by ta0kira; 03-07-2009 at 03:57 AM.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Complex SQL Statement 0.o Programming 7 05-21-2008 06:57 AM
help optimizing this SQL statement hedpe Programming 1 05-30-2007 07:06 AM
help with simple sql statement sekelsenmat Programming 6 08-06-2005 12:01 PM
mysql sql statement help mrtwice Programming 4 12-02-2003 10:04 AM
SQL statement glj Programming 1 10-12-2001 09:29 AM > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 10:01 AM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration