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'm in a position where we've got some brittle code that constructs SQL-like queries via text concatenation with parameters for inputs. The data source that it queries is fast and scalable but lacking tool support. Over time, addition entities and properties have been added to the data source that need changing or have obsoleted others, so the queries need changing.

I can see that this will happen again in a few months and then again.

In order to reduce errors introduced into the text queries, I suggested writing the queries into a separate e.g. .SQL file and then running some kind of code generator tool that could get the schema from the data source and generate a code wrapper around the SQL-like query which was easy to re-generate at any time and would give compile errors for any out-of-date client code.

This idea was met with some skepticism and resistance, even when I offered to fund the development myself.

What are the reasons against doing this? and, for balance, the reasons to go ahead and do it?

(I already saw this post with a couple of answers, but its' not comprehensive)

share|improve this question
1  
Possible duplicate of programmers.stackexchange.com/questions/9346/…. If the question already exists but has unsatisfactory answers, improving it and posting a bounty on it to attract better answers is a better approach than posting it again. –  Anna Lear Mar 31 '11 at 3:33
    
Just a general code generation tip - avoid string concatenation till the very last moment. Operate on trees, it is very easy to prove that your code transformations are correct if you're transforming well-specified trees. And only when you're done, pretty-print a tree into a text form. An AST for SQL is not that complicated at all. –  SK-logic Mar 31 '11 at 10:04
    
@Anna: I'm happy to do that, but it feels like i'd be hijacking the other posts' very specific point and broadening it for my purposes. Is that a wise move? –  JBRWilkinson Mar 31 '11 at 12:33
    
I think it could go either way. I didn't think that other question was significantly different (and perhaps a bit too restrictive in its original scope), but others might disagree. –  Anna Lear Apr 1 '11 at 0:06
add comment

4 Answers 4

up vote 5 down vote accepted

Reasons for:

  1. Lots of boilerplate code can be generated (getters/setters, toString(), clear)
  2. Automated solution is less likely to miss schema changes if you're reading the schema to generate the code. In a large set of tables/POJOs this can prevent bugs.
  3. Ability to generate API/schema documentation from your code.
  4. Time saving in the future maintenance of your code base because you can generate new items quickly.

Reasons against:

  1. Takes time to write a code generator (and you still have to write code for the requirements). My argument against this is the time I save in maintenance will make up for it.
  2. Secondary set of code outside of your requirements to maintain.
  3. You can never cover every case in your generator (so you end up with something that allows you to inject custom code)
  4. If the project is small (less than 25 tables), it may be a overkill and the time savings may not be as great as expected.

EDIT: I have written 3 different code generators for projects and the greatest factor in deciding whether to do it was the size of the project. I did it for a smaller project and it wasn't as effective as the generator for maintaining the larger projects (maybe that's obvious, but I thought I would throw it out there). If the project is small to medium, I would lean toward not using one.

share|improve this answer
    
Plus debugging is harder for generated code, as what you see is not what you wrote. –  user1249 Sep 27 '11 at 15:05
    
WYSINWYW, indeed :-) –  Konamiman Sep 27 '11 at 15:14
add comment

In some respects, it's already been done in other languages. See Hibernate and NHibernate, two libraries which are in use throughout the Java and .NET Enterprise respectively, and which generate SQL.

They do this on the fly, rather than by producing code files; however, they're solving the same problem.

I would be surprised if something similar hadn't already been created for C++.

share|improve this answer
    
Totally - code generation is a good idea, but why solve the problem again if it has already been done by others? –  glenatron Mar 31 '11 at 9:07
    
I'm happy to use an existing system, but what are the downsides? –  JBRWilkinson Mar 31 '11 at 12:28
2  
It's an extra layer of abstraction between the thing you want to achieve and how to achieve it, which involves learning how to configure, use, debug, tune, and generally leverage the abstraction. If the abstraction is useful, good stuff. If it isn't, don't use it. Unfortunately some developers and architects can't tell the difference, and that's the downside. –  Lunivore Mar 31 '11 at 13:40
    
+1 for suggesting a real O/R engine. There is a reason these exist. –  user1249 Sep 27 '11 at 15:05
add comment

Because I created and maintain a code generation framework (ABSE) my answers are probably biased, but here go my arguments:

For:

  • You can "reuse" yourself. Create one generator, use it over and over.
  • You can repeatedly update the generator and generate code. Change in one place, update everywhere.
  • Custom code is usually seen as a deterrent, but if your generator supports the inclusion of custom code, this "mix" can suddenly become a good thing.
  • An expert creates a code generator, the rest of the team can then use it. Everyone becomes as good as the expert. The expert may not like it though :).

Against:

  • Not suitable to newbies. Raising abstraction requires good thinking and some experience.
  • Not suitable for one-off development. If you are creating, say, a PHP script just this once, a code generator is never a good idea. Still, you can still generate common language patterns if you use one continuously.
share|improve this answer
add comment

Code generation has a tendency to become something like an automated copy-paste; therefore, before you generate code, you should always consider writing a piece of code that does the same thing on the fly; e.g. instead of generating code that creates a CRUD form for a table, write something that generates the form at runtime. If this is possible and feasible, it will result in (by far) less code and you avoid all the headaches that copy-paste comes with.

But in the real world, we have to admit that it is not always possible or feasible; in such a situation it's still much better to generate code than to write the same thing by hand. A decision that has to be made very early is whether or not you plan to manually edit the generated code. If so, the output should be simple and straight-forward, so it's easy to change. If not, you must include extension points that allow for customizations without editing the code. Doing that right is much harder, and if you can really do that, check if you cannot avoid code generation completely and create something that constructs the desired object at runtime.

share|improve this answer
    
Copy&Paste is good when done right (i.e. not done by a human). IMHO a code generator enforces a "good" copy&paste... –  Rui Curado Sep 27 '11 at 15:01
add comment

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.