I read down the thread as it stands now, and the basic problem is this. Write the following function:
Compose("SELECT a, b.revenue, b.name
FROM table
INNER JOIN b ON whatever
WHERE name LIKE 'b%'",
"revenue > 10000")
to yield a new valid SQL statement that composes these two fragments together in the way that it is obvious that I want. You basically can't. If you can in this small example, I can easily complexify the SQL until you can't anymore. Note how "revenue" in my supplemental WHERE clause may or may not be that "b.revenue", both in the sense that I may be asking for some other revenue entirely, and in the sense that that particular fragment may have come from somewhere that has no way to know about the "b" part of the name; one of the major issues is namespacing like this, though it is far from the only one!
It is almost certainly impossible to sanely implement this function literally in terms of strings. (If such a thing was possible, it would almost certainly have trash performance.) You need a richer set of data types and operations to permit this.
It has been done. You can hold on to a symbolic representation of the SQL in some internal library representation. However, to "bind" to SQL in this manner requires a binding to every single feature of the SQL you want to use at a pretty deep level (more than just strings, you need to understand the full syntax tree), and speaking from experience, no matter how cleverly you try to start writing it at first it gets more complicated than you think. It is certainly a thing that has been done, but it is a huuuuuuge project. Seriously. It would be a lot easier if we were using an underlying representation designed to do this sort of thing from the beginning.
I like what you can do with SQL, but I kinda hate the way it was juuust good enough to get ensconced and apparently permanently ensure that no improvement on it can ever get off the ground because no conceivable improvement can overcome the entrenched advantages SQL has.
It is almost certainly impossible to sanely implement this function literally in terms of strings. (If such a thing was possible, it would almost certainly have trash performance.) You need a richer set of data types and operations to permit this.
It has been done. You can hold on to a symbolic representation of the SQL in some internal library representation. However, to "bind" to SQL in this manner requires a binding to every single feature of the SQL you want to use at a pretty deep level (more than just strings, you need to understand the full syntax tree), and speaking from experience, no matter how cleverly you try to start writing it at first it gets more complicated than you think. It is certainly a thing that has been done, but it is a huuuuuuge project. Seriously. It would be a lot easier if we were using an underlying representation designed to do this sort of thing from the beginning.
I like what you can do with SQL, but I kinda hate the way it was juuust good enough to get ensconced and apparently permanently ensure that no improvement on it can ever get off the ground because no conceivable improvement can overcome the entrenched advantages SQL has.