Yes, you can use this, and it's in fact quite good to separate the sql code from the rest using a mechanism like this. I think that for a lot of applications, this aproach is in fact very good.
It's even cooler to have this in a repository too, in a structured format. Again, implementing a new platform would be a matter of writing a new generator, rather than rewriting each query individually. It´s cooler still to have a solution for the really rdbms specific stuff, the procedural extensions and trigger syntax.
Now, to store code like this in a repository, one could do two things:
- design a relational datamodel to store code
- store code as an xml blob
I opt for the second option. consider this:
<select>
<columns>
....
</columns>
<from>
<join>
<table name="film" alias="f"/>
<join>
<table name="film_actor" alias="fa"/>
<table name="actor" alias="a"/>
<on>
<equals>
<columnref name="actor_id" table="fa" />
<columnref name="actor_id" table="a" />
</equals>
</on>
</join>
<on>
<equals>
<columnref name="film_id" table="f" />
<columnref name="film_id" table="fa" />
</equals>
</on>
</join>
</from>
<where>
.....
</where
</select>
well, you'll get the idea. It's more work of course to get this started, but once you get going it will become easier and easier to build and maintain new applications.
This kind of platform independant code does pose an interesting challenge for the generative approach. Sometimes, it's best to write the code platform specific like you pointed out. So, a repository/generator solution should allow for manual code to enter, and be maintiained within the model. This is entirely possible!
For the XML/XSLT solution as presented before, you'd allow the repository to maintain XSLTs that are specific to an application system. These XSLT's would simply <xsl:import /> the XSLT's that make up the default implementation, and fill in those parts that need a specific aproach.
(I stress again that the repository aproach is useful for maintaining an app (or better still: more than one app) for an extended period of time, for more than one platform.)