Removing SQL From Templates
dcr...@gmail.com
on how to go about it.
I do *not* like templates having the ability to execute SQL. If I don't
have the data once its past my view (as this is MVC, control doesnt
need to get the data, it just needs to control it), I don't want it.
It is hell optimizing content when you don't know where some of the
queries are executing from, and if I was missing information, it'd be
much easier to see that, than it would be to see that I've got X query
executing in X template.
Adrian Holovaty
Templates don't have the "ability to execute SQL" -- it just happens
that the Django database API executes SQL via attribute access in some
cases (e.g., in the case of related objects via a foreign key) and I
assume you're using that attribute access in the template. The
template system itself knows nothing about SQL.
So, I'm not sure what you're proposing here...
Adrian
--
Adrian Holovaty
holovaty.com | djangoproject.com
James Bennett
> queries are executing from, and if I was missing information, it'd be
> much easier to see that, than it would be to see that I've got X query
> executing in X template.
The debug cursor will show you every query executed, so you can find
out how many queries you're running.
Though I'm curious as to what sorts of queries you're having issues
with; from the template system, the only ways you can get SQL to
execute are
* Attribute access of a related object which hadn't previously been
accessed (and select_related will work around that for you so you get
all the queries up-front).
* Method accesses which fetch data (these should be documented in your models).
* Template tags which execute SQL (these should be documented in the
template tags which are executing the SQL).
As far as I know, there are no other ways to execute SQL from a
template; everything else should be taking place in the view (even
context processors, because they're applied up-front). Are you finding
database accesses coming from somewhere else?
--
"May the forces of evil become confused on the way to your house."
-- George Carlin
Jacob Kaplan-Moss
> Templates don't have the "ability to execute SQL" -- it just happens
> that the Django database API executes SQL via attribute access in some
> cases (e.g., in the case of related objects via a foreign key) and I
> assume you're using that attribute access in the template. The
> template system itself knows nothing about SQL.
To piggy-back on here:
It's incredibly simple to prevent the templates from being able to "execute
SQL": just don't pass model objects into the template.
Done, and done!
Jacob
David Cramer
sending models to the templates is not a good solution for us :)
I want to setup some kind of blocking mechanism so that while rendering
a template the models will not do any SQL queries that have not already
been executed, or at least block the related field queries.
On Jan 9, 6:21 am, Jacob Kaplan-Moss <j...@jacobian.org> wrote:
> On 1/8/07 4:53 PM, Adrian Holovaty wrote:
>
> > Templates don't have the "ability to execute SQL" -- it just happens
> > that the Django database API executes SQL via attribute access in some
> > cases (e.g., in the case of related objects via a foreign key) and I
> > assume you're using that attribute access in the template. The
> > template system itself knows nothing about SQL.To piggy-back on here:
Waylan Limberg
>
> I'm talking completely about the related objects accessors, and not
> sending models to the templates is not a good solution for us :)
>
> I want to setup some kind of blocking mechanism so that while rendering
> a template the models will not do any SQL queries that have not already
> been executed, or at least block the related field queries.
Well, if you don't want the related objects to be queried at a later
time that the initial object, select_related [1] will do that for you.
Simply put, all related objects are queried at the same time as the
initial object.
I don't know why you would want to block those queries completely. If
your template is asking for them and they're blocked, you'll never get
that info to display in your template. Unless that is what you want,
but then I suggest removing those calls from the template. Seems to me
more like an issue of *when* the SQL queries are executed and
select_related takes care of that.
[1]: http://www.djangoproject.com/documentation/db_api/#select-related
--
----
Waylan Limberg
way...@gmail.com
James Bennett
> a template the models will not do any SQL queries that have not already
> been executed, or at least block the related field queries.
Unfortunately I think this would be viewed as gigantic "breakage" by
many users of Django; being able to access attributes of an object --
even if those attributes involve database queries -- is simply too
convenient to have in the template.
Your best options are to either start using select_related (so that
all data access happens immediately) or enforce some rules on your
template authors; the automatic model documentation will tell you
which attributes access data, so you will be able to isolate those and
tell template authors not to use them.
Failing that, serious hacking inside Django would be necessary; if
you're willing and able to delve into the template system, you might
be able to implement something akin to 'alters_data'; then you'd need
a way to mark every data-accessing attribute of every model with a
'fetches_data' attribute.
David Cramer
haven't managed to implement the functionality I want in it yet.
I'd like to come up with an optional, or simply, a solution for our
framework, where designers do not get access to do the SQL through the
template on related attributes.
Yes, we have debugging that shows us every query. Yes, it's a HUGE pain
in the ass to figure out what's actually executing these queries.
I'll happily modify django for what I want, I just need some help on
where to start... as I dug around for an hour or two the other day and
couldn't see where exactly the related objects execute the lazy sql. If
nothing else, I guess I could just modify the cursor routines to
implement the blocking, and then the template.render method.
James Bennett
> where to start... as I dug around for an hour or two the other day and
> couldn't see where exactly the related objects execute the lazy sql. If
> nothing else, I guess I could just modify the cursor routines to
> implement the blocking, and then the template.render method.
As I said before, probably your best bet, if you can't use
select_related, is to document obsessively and enforce a list of
'forbidden' attributes. Hacking away at Django to deny access to those
attributes is a last-ditch effort to be used only if you have no
chance whatsoever of trusting your template authors not to use the
things you tell them not to use.
But if you must go that route, look at how the template system handles
the 'alters_data' attribute on methods and attributes; probably the
cleanest thing would be to implement a similar 'fetches_data'
attribute and hack the template system to refuse to resolve such
attributes.
James Bennett
> framework, where designers do not get access to do the SQL through the
> template on related attributes.
Also, bear in mind that this option locks you in to the unpleasant
situation of never being able to use certain data or to present them
the way you'd like; any method of getting that data at some other part
of the request/response cycle would just re-introduce the queries
somewhere else and you'd be back to figuring out how to deal with
them.
Judging from what you've said, your database simply is not capable of
handling the level of traffic you're imposing on it, and no amount of
shuffling will help, so you seem to be stuck giving up on the data --
trying to do the queries "up front" in the view makes no difference,
because template rendering technically happens in the view; at best,
you'd just be making the same number of queries, just some of them
would happen a fraction of a second sooner.
And, of course, the best solution of all is not to hack away on
anything until you've figured out exactly what data you want and
exactly which queries you need to execute to get it, and explored
whether you can optimize or cache any of them you haven't already
cached or optimized. Remember that there are a *lot* of options for
caching, and they often go a long way.
David Cramer
execute any queries that have not already been done by the time it's
hit the render phase. If it hasnt been executed, we don't want it
accessible. The reason for this is to stop queries going outside of our
cache and/or view.
Yes, this may limit templates, but it's a good limitation, there should
be no reason a template should have to pull information from the
database. Why? Because you can't cache the data within the template, it
is hit EVERY SINGLE TIME the page is loaded. So let's say for example,
we have a page, that displays 100 search results, and for some reason
or another, we didn't have the user field selected in these results,
and the template is calling the user field. We now have a template
executing 100 select queries EVERY SINGLE VIEW.
Do you understand why this needs changed?
On Jan 10, 4:22 am, "James Bennett" <ubernost...@gmail.com> wrote:
> On 1/9/07, David Cramer <dcra...@gmail.com> wrote:
>
> > I'd like to come up with an optional, or simply, a solution for our
> > framework, where designers do not get access to do the SQL through the
> > template on related attributes.Also, bear in mind that this option locks you in to the unpleasant
James Bennett
> execute any queries that have not already been done by the time it's
> hit the render phase. If it hasnt been executed, we don't want it
> accessible. The reason for this is to stop queries going outside of our
> cache and/or view.
They're still happening "in the view", though -- the view doesn't
return until *after* the template is rendered. If you use full-page
caching, then those DB hits will be cached. Similarly, if you use the
fine-grained caching to store the rendered template, those DB hits
will be cached.
You can even cache inside a template tag if you feel a need;
*anywhere* that you're dealing in Python with a valid Python value
(and a rendered template is just a Python string), you can stuff that
value into the cache. There's nothing special about templates which
exempts them from being cached.
> Yes, this may limit templates, but it's a good limitation, there should
> be no reason a template should have to pull information from the
> database. Why? Because you can't cache the data within the template, it
> is hit EVERY SINGLE TIME the page is loaded.
No, you *can* cache data in the template. You can cache as much or as
little of it as you like.
> So let's say for example,
> we have a page, that displays 100 search results, and for some reason
> or another, we didn't have the user field selected in these results,
> and the template is calling the user field. We now have a template
> executing 100 select queries EVERY SINGLE VIEW.
Or once every n seconds, where n is your cache timeout.
David Cramer
most pages, as things vary such as moderation options.
On Jan 10, 5:26 am, "James Bennett" <ubernost...@gmail.com> wrote:
> On 1/9/07, David Cramer <dcra...@gmail.com> wrote:
>
> > I don't think you understand. We do not want the template to be able to
> > execute any queries that have not already been done by the time it's
> > hit the render phase. If it hasnt been executed, we don't want it
> > accessible. The reason for this is to stop queries going outside of our
> > cache and/or view.They're still happening "in the view", though -- the view doesn't
> return until *after* the template is rendered. If you use full-page
> caching, then those DB hits will be cached. Similarly, if you use the
> fine-grained caching to store the rendered template, those DB hits
> will be cached.
>
> You can even cache inside a template tag if you feel a need;
> *anywhere* that you're dealing in Python with a valid Python value
> (and a rendered template is just a Python string), you can stuff that
> value into the cache. There's nothing special about templates which
> exempts them from being cached.
>
> > Yes, this may limit templates, but it's a good limitation, there should
> > be no reason a template should have to pull information from the
> > database. Why? Because you can't cache the data within the template, it
> > is hit EVERY SINGLE TIME the page is loaded.No, you *can* cache data in the template. You can cache as much or as
> little of it as you like.
>
> > So let's say for example,
> > we have a page, that displays 100 search results, and for some reason
> > or another, we didn't have the user field selected in these results,
> > and the template is calling the user field. We now have a template
> > executing 100 select queries EVERY SINGLE VIEW.Or once every n seconds, where n is your cache timeout.
James Bennett
> most pages, as things vary such as moderation options.
There are still plenty of ways you can make use of the cache. From
what you've said, I think two in particular are worth looking at:
1. It'd be pretty easy to write a template tag -- say, {% cache %}{%
endcache %} -- which caches all the nodes within itself; this way you
can isolate parts of the template which hit the DB, and cache those
results without having to cache the entire rendered template.
2. If you have a significant number of users who browse while not
logged in, the CACHE_MIDDLEWARE_ANONYMOUS_ONLY setting will just cache
everything they see, no questions asked.
I'm not saying that you shouldn't necessarily try to cut the number of
data accesses going on in your templates, it just seems to me that
there are a lot of options to explore which don't involve lots of time
spent rewriting Django's template system...
Gustavo Picon
those to your templates instead of data objects?
--
Gustavo Picon
David Cramer
> those to your templates instead of data objects?
get_absolute_url, we use things like this quite a bit in our templates
> 1. It'd be pretty easy to write a template tag -- say, {% cache %}{%
endcache %} -- which caches all the nodes within itself; this way you
can isolate parts of the template which hit the DB, and cache those
results without having to cache the entire rendered template.
I may look into this, but I'd still prefer something as to where I
could block it in the template. It'd just be easier to find the loose
queries/etc if I could do that.
George Davis
> get_absolute_url, we use things like this quite a bit in our templates
You could relatively painlessly 'flatten' the attributes of the model
instances in place before rendering template, i.e.
spam.get_absolute_url = spam.get_absolute_url()
Or programatically
for attr, val in spam.__dict__ :
if type(val).__name__ == 'function' :
setattr( spam, attr, val() )
Clearly, related object members would have to be handled slightly
differently, and to save expense on SQL queries you might only want to
evaluate some subset of the object's attributes while deleting others.
But it seems to me that at some point you must specify precisely which
object members will be available to the template, and you might as well
generate a restricted version of the object when doing so.
G