Playing with SQL Generation

[Jehu Galeahsa]

Hello:

I am working on a pet project. I am playing around with some database
code. One of the things I am working on is generating SQL dynamically.
I have a pretty good architecture at this point. You can check it out
at earthworm.codeplex.com.

Does anyone have any ideas on a good way to handle provider-specific
extensions to SQL? For instance, T-SQL has a TOP N modifier and a way
of retrieving the AUTO INCREMENT value after an insert. These aren't
part of the ANSI standard. However, I don't want to prevent someone
using SQL Server from finding my library useful. Essentially, I want
to allow arbitrary modifications to the SQL without requiring code
that needs to search within the generated SQL or a lot of string
manipulating.

It would be nice to provide access to the columns, etc. that can be
used when modifying the SQL. That way the correct aliases are used.

I was thinking of breaking out each clause in the select statment
(projection, from, where, order by, group by) and providing a
"before", and "after" event that allowed some text to be manually
added in. For instance, someone might attach to a "ProjectionBuilding"
event and add a TOP 3 to the SQL before any columns are listed. But
how can I do this in a user-friendly manner? Perhaps I can't.

Here is an example of what the code looks like now:

Table table = new Table("test");
QueryBuilder builder = new QueryBuilder(table);

Column nameColumn = table.CreateColumn("name");
builder.AddProjection(column1);
Column titleColumn = table.CreateColumn("title");
builder.AddProjection(column2);

Column idColumn = table.CreateColumn("id");
builder.Where.AddFilter(new EqualToFilter(idColumn, new
NumericLiteral(1234)));

builder.AddOrderBy(new OrderBy(name, Order.Ascending,
NullPlacement.First));

string commandText = builder.CommandText;

It will generate SQL like this:
SELECT test.name, test.title
FROM test
WHERE test.id = 1234
ORDER BY test.name ASC NULLS FIRST

I'd appreciate any ideas. It is fun playing around with this stuff.

 

[Jeff Johnson]

I am working on a pet project. I am playing around with some database
code. One of the things I am working on is generating SQL dynamically.

Cool. I did something like that years ago. The code to generate a statement
looks absolutely Byzantine, but it sure can generate complex queries! (Not
that I've ever really used it....) So good luck to you.

 

[Jehu Galeahsa]

Cool. I did something like that years ago. The code to generate a statement
looks absolutely Byzantine, but it sure can generate complex queries! (Not
that I've ever really used it....) So good luck to you.

This will be about the 4th time I've written code similar to this.
Each time it gets a little more flexible and a lot more complicated. I
am trying to come up with an architecture that is flexible and simple.

My first big goal is to create a simple IQueryable that can generate
SQL dynamically. There are already a TON of existing libraries that do
that. But, I want to do it myself for educational purposes. There is
_some_ benefit in reinventing the wheel.

My big problem with a lot of the production-grade SQL generators these
days is that they are usually gigantic methods with mind-boggling if/
else statements. Typically, 80% of the logic is duplicated for the
different database dialects. I want to open up that logic so that it
is "easy" to plug in new dialects. I am fighting the battle between
complex procedural logic and complex object-oriented logic; there has
to be a middle ground.

The big goal of this project is to provide the guts of a good data
access layer without going so far as to make it an all-in-wonder ORM
solution. I hate most ORMs today because they are black boxes; when
all you want is some little feature, like state management, it is damn
near impossible to find the exact code that does it. It is hidden deep
down and typically not object-oriented. Most are made-to-work and
aren't general purpose enough.

What I want to do is provide a library where someone who wants to
build SQL dynamically can do so without ties in from the rest of the
library. If someone wants to do simple SQL-to-object mapping, they can
do that without using the rest of the system. If someone wants to
track the state of an object, they can do it. I want to provide
services that _can_ communicate together, but don't have to. In large
applications, ORMs start to lose their charm because they can quickly
lock a system into using them. That's probably why in many large
applications today, manually implementing a data access layer is still
common practice.

Really, this library is going to be a repository of code I have been
building for the past 4 years. I plan on revisiting it all, unit
testing it and giving it one good polishing before making it
available. Eventually, I want to replace my old code sets with my
project.

But, hey, I need to figure out how to make it all happen first.
Getting feedback from folks is what I really need right now.