I think that most people either define their statement directly in code (ie. string sql("SELECT * FROM ORDERS");), or put the same string in a resource and load it. The application that I work on often builds complex statements from user input and requires a lot of code to create the correct sql statement. I would like something that delimits table and column names: "table 1" "column 1" => "[table 1].[column 1]"), inserts commas in lists (ie SELECT "c1" "c2" "c3" => "SELECT [c1], [c2], [c3]), and manages table aliases (ie SELECT table1.column FROM table1 AS t1 => "SELECT [t1].[column] FROM [table1] AS [t1]"). It would also be nice if it know what dialect of sql it is working with and thus abstract away from that so one can have one piece of code know how to make the sql statement correct for Microsoft jet, mysql, ANSI SQL92, etc. eg: stringstream << select << column("c1") << column("table1", "column2") << from << table("table1").as("t1"); => "SELECT [c1], [t1].[column2] FROM [table1] AS [t1]" I believe that a database library for boost should include something like this.
Andy Tompkins wrote:
eg: stringstream << select << column("c1") << column("table1", "column2") << from << table("table1").as("t1");
=> "SELECT [c1], [t1].[column2] FROM [table1] AS [t1]"
I believe that a database library for boost should include something like this.
A good idea, but IMO you haven't taken it far enough. First, consider the RTL, the relational template library (http://sdmg.developerpipeline.com/documents/s=9849/cuj0403vertleyb/), which gives you strong type-safety when applying relational operations to strongly-typed in-memory data. It uses expression templates. Next, consider xpressive, which allows you to author regular expressions either as expression templates OR as strings. It makes use of a generic expression template manipulation framework called proto, which makes it fairly straightword to define arbitrary transformations on a statically typed expression tree. Now, putting it all together, I imagine a relational library with a dual-interface like xpressive, but for SQL instead of regex. SQL statements could be expression templates, which when applied to in-memory data, yields strongly-typed results. OR, the exact same expression template, when used in a different context, might get transformed into the equivalent SQL string for remote (lazy?) execution with dynamic typing. Potentially very powerful. -- Eric Niebler Boost Consulting www.boost-consulting.com
participants (2)
-
Andy Tompkins -
Eric Niebler