Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am working on my .NET open source project for generating SQL...

Initially, I wrote the project so it spit out exactly one SQL format... ugly. I started using this project in the real world and decided it would be nice if I could generate pretty SQL in debug mode and ugly SQL in release mode.

My first go involved a lot of if/else loops within my command generation code. It quickly got to be too much to handle. So, I started looking for another approach that would allow me to plug-in different formatting strategies. I also looked at other SQL formatting libraries but most were specific to one dialect.

Right now, I am playing with the idea of generating a tree of tokens, similar to an abstract syntax tree (AST) that a compiler works with. I was hoping to navigate the tree and generate text based on where I am in the tree. Now that I've built these trees, I'm not sure how to write the code to visit these nodes.

I feel like I'm going backwards. Instead of parsing SQL, generating an AST and spitting out SQL objects, I am allowing someone to build SQL objects, then generate an AST and spitting out SQL. When someone uses my library to build a SELECT statement, they specify the values to select, the tables to select from, filters, ordering, etc. But once I have generated an AST... I have less information than I originally started with! Only by asking "Is the next token the keyword FROM?" can I get a picture of where I'm at. I have to write a bunch of if/elif/else statements to figure out what comes next. At least when I had the SQL objects I could use polymorphism to avoid nasty branching.

So, perhaps I missed a day in compiler theory or perhaps it's because I'm trying to go backwards. But, for whatever reason, my next step is really unclear to me. I think the AST route was in the right direction even if it does mean losing information. I was able to write a really stupid formatter that puts spaces between every token for now. It's really when it comes to getting fancy that I'm overwhelmed.

If anyone has some good resources for going backwards, it would probably be really helpful.

share|improve this question
1  
Off topic: I looked at your site and all of your examples involved a lot more code than just typing out the SQL. Curious: what is the benefit of this library? –  gahooa Jan 30 '13 at 3:16
    
So what does your AST actually look like? I'm unclear about your description of a "tree of tokens", because tokens don't normally appear in an AST. –  Greg Hewgill Jan 30 '13 at 4:10
    
@gahooa I've worked on a lot of projects where someone justified to themselves that it made sense to write a micro ORM. Usually, this involved using a DataTable schema or attributes and reflection. They often make the mistake of combining the logic for describing the schema and generating the SQL. The resulting SQL is usually full of minor bugs, like whitespace issues, SQL injection vulnerabilities and ambiguous column names. This libraries goal is to break out the common logic for generating SQL based on an existing schema. It's generic enough that someone can use it with their own ORM. –  Travis Parks Jan 30 '13 at 13:39
    
@GregHewgill My tree is made up of expressions and tokens. An expression can have other expressions and tokens in it. Each expression is typed (SelectCommand, Join, OrderByList, OrderBy). When I encounter an UPDATE command, I expect to see something like this: <UpdateCommand> => "UPDATE" <TableDeclaration> "SET" <SetterList> [ "WHERE" <Filter> ]. Here, UPDATE, SET and WHERE are keyword tokens and <UpdateCommand>, <TableDeclaration>, <SetterList> and <Filter> are other expressions. –  Travis Parks Jan 30 '13 at 13:45

2 Answers 2

I had a chance to look at your actual code. It looks like you're most of the way there but this "tree of tokens" concept is getting in the way. It turns out you probably don't need it at all (specifically, I'm referring to the stuff in the Expressions/ folder).

For example, in Join, you have a method called GetDeclarationExpression that returns an IExpressionItem that is a tree of tokens. What I'm suggesting is that you can simplify this and instead directly return a String which is the actual SQL generated from the join. In concrete terms, something like the following:

    String IJoinItem.GetDeclarationExpression(CommandOptions options)
    {
        // [ "(" ] <Left> <Combiner> <Right> [ "ON" <Filter> ] [ ")" ]
        StringBuilder expression = new StringBuilder();
        if (WrapInParentheses ?? options.WrapJoinsInParentheses)
        {
            expression.Append("(");
        }
        expression.Append(_leftHand.GetDeclarationExpression(options));
        expression.Append(" ")
        expression.Append(GetJoinNameExpression(options));
        expression.Append(" ")
        expression.Append(_rightHand.GetDeclarationExpression(options));
        expression.Append(" ")
        expression.Append(GetOnExpression(options));
        if (WrapInParentheses ?? options.WrapJoinsInParentheses)
        {
            expression.Append(")");
        }
        return expression.ToString();
    }

Of course, all other methods used during the AST traversal would also need to be modified to return String instead of IExpressionItem as needed. Each method that is called during the tree traversal would be responsible for generating the actual SQL string, which will all eventually get concatenated together.

share|improve this answer
    
This is close to what I had originally. But it was intense changing the formatting strategy. –  Travis Parks Jan 30 '13 at 23:08
    
This gave me another idea that relates to your idea and @TMN's. I could create an abstract factory. Then I could create multiple classes for formatting one type of expression, based on the sub-classes that are returned by the factory. Then I don't need an AST anymore. The only draw back is that I might need to expose some internals of the builders to the formatters... –  Travis Parks Jan 31 '13 at 1:23

Have you considered having a separate formatter for each expression type? You could have a select-statement-formatter, a column-list-formatter, a from-clause-formatter, etc. I don't know specifically how your AST is structured, but you should be able to identify the various clauses and expressions and pass them to specific formatters.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.