Building SQL DDL statements: which patterns are appropriate?

Question

I had seen somewhere the builder pattern used to create DML SQL statements. I was wondering, which pattern(s) would be (more) appropriate for building SQL DDL statements.

I am thinking about simple program (DB tool, solely for self-education purposes), that would create (dynamically) simple SQL DDL statements. I am not sure which patterns should I consider.

The factory pattern allows me to decouple client code from concrete database provider classes library. I suppose it's a clear choice here (please correct me if I am wrong). The decorator pattern was my first choice for building sql statements, but after coding some examples and then reading this answer I am almost sure, I shouldn't be using decorator, as I am building objects and not decorating already created objects.

So.. which patterns should I consider and why are they good/better in this case?

Updated for clarification.

It is relatively rare that your application needs to dynamically write DDL, unless you are writing a DB tool. Are you sure this is the most appropriate design? — Marc Gravell, Sep 01 '11 at 19:38
Yes, I was thinking about DB tool, and yes, DDL statements will be created dynamically. — Krzysztof Szynter, Sep 01 '11 at 19:43

score 0 · Answer 1 · answered Sep 01 '11 at 21:29

The "core" of your DB tool needs to represent the knowledge it possesses in a DB-independent form, and when the time comes a database-specific code takes over and translates that knowledge into DB-specific DDL.

You'll probably need some sort of dependency injection to accomplish that. The basic idea is this: the core of your application works with interfaces only and never knows anything beyond what is declared in these interfaces. At run-time, DB-specific objects implementing these interfaces are instantiated and "injected" into the core. Create than blindly "calls" them as it would any other set of objects implementing the same set of interfaces.

If another DB needs to be supported, juts make another set of classes that implement these interfaces and instantiate them in run-time.

Of course this is all just a theory. You'll find that there are many nuances between different database systems and I suspect it will be hard for you to represent all that in a completely generalized way...

Building SQL DDL statements: which patterns are appropriate?

1 Answers1