6

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)

Improve this question
6
  • 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.
    – Adam Lear ♦
    Mar 31, 2011 at 3:33
  • 1
    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, 2011 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, 2011 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.
    – Adam Lear ♦
    Apr 1, 2011 at 0:06
  • It is horribly messy to generate an AST for Java or C, though. Print statements with string concatenation is in my experience the perfect level of abstractions if a lot of logic goes into the code. In case the code is almost entirely boilerplate with just a few single different names, a template becomes an alternative. In the case of SQL, I fully agree, though 😊
    – yeoman
    Jun 12, 2020 at 12:38

4 Answers 4

Reset to default
6

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.

Improve this answer
4
  • 2
    Plus debugging is harder for generated code, as what you see is not what you wrote.
    – user1249
    Sep 27, 2011 at 15:05
  • WYSINWYW, indeed :-)
    – Konamiman
    Sep 27, 2011 at 15:14
  • Unless you generate readable code, which does not create a lot of additional effort but makes an enormous difference 🙂 Most resistance against code generation I've experienced stems from the fact that people have seen horrible generated code, for which I have no tolerance whatsoever.You can make hundreds of thousands of code pretty or terrible in a single place. You have chosen automation, so reap at least the lowest hanging fruits among the benefits this choice provides 😅
    – yeoman
    Jun 12, 2020 at 12:19
  • Yeah... like proper indents and don't try to be clever and convert proper IF blocks into a nested ternary monster.
    – Nelson
    Mar 8, 2023 at 0:46
3

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.
Improve this answer
2

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++.

Improve this answer
5
  • Totally - code generation is a good idea, but why solve the problem again if it has already been done by others?
    – glenatron
    Mar 31, 2011 at 9:07
  • I'm happy to use an existing system, but what are the downsides?
    – JBRWilkinson
    Mar 31, 2011 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, 2011 at 13:40
  • +1 for suggesting a real O/R engine. There is a reason these exist.
    – user1249
    Sep 27, 2011 at 15:05
  • The problem with existing source code generators / abstractions is that you're not in control and it's like not easy and sustainable to extend the tool in case the need arises, e.g. for en exceptional case. Hibernate is so widely used that by now you should be able to basically everything you're going to need but a lot of tools are not as comprehensive, suddenly making a few corner cases create more work load that either writing all code manually, or by writing your own generator, putting you in full control 😎
    – yeoman
    Jun 12, 2020 at 12:26
2

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.

Improve this answer
4
  • 1
    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, 2011 at 15:01
  • But that is not always true, that code generation is an automated copy-paste. See some transpilers and compilers to C. (e.g. Chicken Scheme or Bigloo)
    – Basile Starynkevitch
    Mar 1, 2017 at 15:00
  • @BasileStagrynkevitch true, but that other kind of code generation is - at least in my understanding - not within the scope of the question.
    – user281377
    Mar 1, 2017 at 16:44
  • Generated code should never be messy because debugging and because it's so easy to make lots of pretty from a single place so there's really no excuse 😂
    – yeoman
    Jun 12, 2020 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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