Hi all,
I originally posted this in django-users as the tail end of a longer
discussion, but in retrospect, this is probably a better forum.
I have been looking at the code for function_get_sql_clause() recently
while working on a patch for quoting behaviour for subselects in
"tables" clauses (now submitted as #967). In working up that patch,
something occurred to me.
The purpose of the function_get_sql_clause() is to turn a kwargs
dictionary into an SQL query. At the moment, the assumption is that all
the kwargs keys (select, tables, etc) are tied to lists or dictionaries
that contain strings. If the string is a table/column name, it is
quoted; otherwise, it has a space in it, it gets used verbatim where a
single table/column name would be valid - so if your string is a block
of SQL, you can get more specific results. The purpose of
quote_only_if_word is to protect these potentially useful SQL
statements.
It occurred to me that it should be possible to modify
quote_only_if_word() to use function_get_sql_clause() recursively. If
you provide a string to quote_only_if_word(), then insert it (either
quoted or verbatim). However, if you find a dictionary, then use its
values as the values for opts and kwargs in a recursive call to
function_get_sql_clause(). The recursive call returns a string, which
can get inserted as required. This would allow you to build almost
arbitrarily complex nested SQL queries using Django DB syntax.
For example, the tutorial example for the 'select' keyword in the
database model documentation would no longer need the inner SQL clause
- the same SQL could be generated using a recursive kwarg dictionary,
something like :
polls.get_list(
select={
'choice_count': {
db_table='polls_choices',
db_columns=['COUNT(*)']
kwargs={
where=['poll_id=polls.id']
}
}
}
)
(Note that I'm not completely happy with the syntax for
db_table/db_columns/kwargs - I'm open to suggestions on better ways to
handle that bit).
Using this approach, existing queries would be unaffected; if it's
currently defined as a string, it will continue to be interpreted as a
string. However, if you provide a dictionary, it gets rolled out as a
subquery.
Am I on to a good idea here, or have I missed something? If I was to
work up a patch to implement this, would it be looked upon favourably?
Russ Magee
