From 60c3eb6ecbe5c000aa4d24344e85d2ed00d8983c Mon Sep 17 00:00:00 2001 From: Jan Szejko Date: Fri, 22 Apr 2016 15:48:22 +0200 Subject: [PATCH] optimize db usage in tagged object list --- src/api/handlers.py | 15 +++------------ src/catalogue/models/book.py | 10 +++++++++- src/catalogue/models/tag.py | 16 ++++++++++++++++ src/catalogue/templatetags/catalogue_tags.py | 1 + src/catalogue/utils.py | 16 ++++++++-------- src/catalogue/views.py | 8 ++++++-- src/picture/models.py | 8 +++++++- 7 files changed, 50 insertions(+), 24 deletions(-) diff --git a/src/api/handlers.py b/src/api/handlers.py index 01430cbcb..827cd7c5f 100644 --- a/src/api/handlers.py +++ b/src/api/handlers.py @@ -6,7 +6,6 @@ import json from django.contrib.sites.models import Site from django.core.urlresolvers import reverse -from django.db.models import Prefetch from django.utils.functional import lazy from piston.handler import AnonymousBaseHandler, BaseHandler from piston.utils import rc @@ -14,7 +13,7 @@ from sorl.thumbnail import default from catalogue.forms import BookImportForm from catalogue.models import Book, Tag, BookMedia, Fragment, Collection -from catalogue.models.tag import TagRelation +from catalogue.models.tag import prefetch_relations from picture.models import Picture from picture.forms import PictureImportForm @@ -210,13 +209,7 @@ class AnonymousBooksHandler(AnonymousBaseHandler, BookDetails): books = books.only('slug', 'title', 'cover', 'cover_thumb') for category in book_tag_categories: - books = books.prefetch_related( - Prefetch( - 'tag_relations', - queryset=TagRelation.objects.filter(tag__category=category) - .select_related('tag').only('tag__name_pl', 'object_id'), - to_attr='%s_relations' % category)) - + books = prefetch_relations(books, category) if books: return books else: @@ -260,9 +253,7 @@ def _tags_getter(category): def _tag_getter(category): @classmethod def get_tag(cls, book): - if hasattr(book, '%s_relations' % category): - return ', '.join(rel.tag.name for rel in getattr(book, '%s_relations' % category)) - return ', '.join(book.tags.filter(category=category).values_list('name', flat=True)) + return book.tag_unicode(category) return get_tag diff --git a/src/catalogue/models/book.py b/src/catalogue/models/book.py index 0ed97161b..5ac999eeb 100644 --- a/src/catalogue/models/book.py +++ b/src/catalogue/models/book.py @@ -22,6 +22,7 @@ from catalogue import constants from catalogue.fields import EbookField from catalogue.models import Tag, Fragment, BookMedia from catalogue.utils import create_zip, gallery_url, gallery_path +from catalogue.models.tag import prefetched_relations from catalogue import app_settings from catalogue import tasks from wolnelektury.utils import makedirs @@ -109,8 +110,15 @@ class Book(models.Model): def authors(self): return self.tags.filter(category='author') + def tag_unicode(self, category): + relations = prefetched_relations(self, category) + if relations: + return ', '.join(rel.tag.name for rel in relations) + else: + return ', '.join(self.tags.filter(category=category).values_list('name', flat=True)) + def author_unicode(self): - return ", ".join(self.authors().values_list('name', flat=True)) + return self.tag_unicode('author') def save(self, force_insert=False, force_update=False, **kwargs): from sortify import sortify diff --git a/src/catalogue/models/tag.py b/src/catalogue/models/tag.py index 153197307..00bdcb55b 100644 --- a/src/catalogue/models/tag.py +++ b/src/catalogue/models/tag.py @@ -7,6 +7,7 @@ from django.core.cache import caches from django.contrib.auth.models import User from django.db import models from django.db.models import permalink +from django.db.models.query import Prefetch from django.dispatch import Signal from django.utils.translation import ugettext_lazy as _ @@ -246,3 +247,18 @@ class Tag(TagBase): # Pickle complains about not having this. TagRelation = Tag.intermediary_table_model + + +def prefetch_relations(objects, category, only_name=True): + queryset = TagRelation.objects.filter(tag__category=category).select_related('tag') + if only_name: + queryset = queryset.only('tag__name_pl', 'object_id') + return objects.prefetch_related( + Prefetch('tag_relations', queryset=queryset, to_attr='%s_relations' % category)) + + +def prefetched_relations(obj, category): + if hasattr(obj, '%s_relations' % category): + return getattr(obj, '%s_relations' % category) + else: + return None diff --git a/src/catalogue/templatetags/catalogue_tags.py b/src/catalogue/templatetags/catalogue_tags.py index 6f50ad77c..c02a5d74f 100644 --- a/src/catalogue/templatetags/catalogue_tags.py +++ b/src/catalogue/templatetags/catalogue_tags.py @@ -325,6 +325,7 @@ def tag_list(tags, choices=None, category=None, list_type='books'): other = other.filter(items__content_type=ct).distinct() if list_type == 'audiobooks': other = other.filter(id__in=get_audiobook_tags()) + other = other.only('name', 'slug', 'category') else: other = [] diff --git a/src/catalogue/utils.py b/src/catalogue/utils.py index e94c67d0b..60a56714c 100644 --- a/src/catalogue/utils.py +++ b/src/catalogue/utils.py @@ -2,24 +2,24 @@ # This file is part of Wolnelektury, licensed under GNU Affero GPLv3 or later. # Copyright © Fundacja Nowoczesna Polska. See NOTICE for more information. # -from collections import defaultdict import hashlib import os.path import random import re import time from base64 import urlsafe_b64encode - -from django.http import HttpResponse -from django.core.files.uploadedfile import UploadedFile -from django.core.files.storage import DefaultStorage -from django.utils.encoding import force_unicode -from django.conf import settings -from os import mkdir, path, unlink +from collections import defaultdict from errno import EEXIST, ENOENT from fcntl import flock, LOCK_EX +from os import mkdir, path, unlink from zipfile import ZipFile +from django.conf import settings +from django.core.files.storage import DefaultStorage +from django.core.files.uploadedfile import UploadedFile +from django.http import HttpResponse +from django.utils.encoding import force_unicode + from reporting.utils import read_chunks # Use the system (hardware-based) random number generator if it exists. diff --git a/src/catalogue/views.py b/src/catalogue/views.py index 2aa98f721..6782e5369 100644 --- a/src/catalogue/views.py +++ b/src/catalogue/views.py @@ -12,7 +12,7 @@ from django.template.loader import render_to_string from django.shortcuts import render_to_response, get_object_or_404, render, redirect from django.http import HttpResponse, HttpResponseRedirect, Http404, HttpResponsePermanentRedirect, JsonResponse from django.core.urlresolvers import reverse -from django.db.models import Q +from django.db.models import Q, QuerySet from django.contrib.auth.decorators import login_required, user_passes_test from django.utils.http import urlquote_plus from django.utils import translation @@ -29,6 +29,7 @@ from catalogue import forms from catalogue.helpers import get_top_level_related_tags from catalogue.models import Book, Collection, Tag, Fragment from catalogue.utils import split_tags +from catalogue.models.tag import prefetch_relations staff_required = user_passes_test(lambda user: user.is_staff) @@ -103,7 +104,10 @@ def object_list(request, objects, fragments=None, related_tags=None, tags=None, else: fragments = Fragment.objects.filter(book__in=objects) related_tag_lists.append( - Tag.objects.usage_for_queryset(fragments, counts=True).filter(category='theme').exclude(pk__in=tag_ids)) + Tag.objects.usage_for_queryset(fragments, counts=True).filter(category='theme').exclude(pk__in=tag_ids) + .only('name', 'sort_key', 'category', 'slug')) + if isinstance(objects, QuerySet): + objects = prefetch_relations(objects, 'author') categories = split_tags(*related_tag_lists) diff --git a/src/picture/models.py b/src/picture/models.py index e10d2faf1..6d8def377 100644 --- a/src/picture/models.py +++ b/src/picture/models.py @@ -12,6 +12,8 @@ from django.core.files.storage import FileSystemStorage from django.utils.datastructures import SortedDict from fnpdjango.utils.text.slughifi import slughifi from ssify import flush_ssi_includes + +from catalogue.models.tag import prefetched_relations from picture import tasks from StringIO import StringIO import jsonfield @@ -128,7 +130,11 @@ class Picture(models.Model): return self.tags.filter(category='author') def tag_unicode(self, category): - return ", ".join(self.tags.filter(category=category).values_list('name', flat=True)) + relations = prefetched_relations(self, category) + if relations: + return ', '.join(rel.tag.name for rel in relations) + else: + return ', '.join(self.tags.filter(category=category).values_list('name', flat=True)) def author_unicode(self): return self.tag_unicode('author') -- 2.20.1