src/catalogue/models/book.py

   1 # This file is part of Wolnelektury, licensed under GNU Affero GPLv3 or later.
   2 # Copyright © Fundacja Nowoczesna Polska. See NOTICE for more information.
   3 #
   4 from collections import OrderedDict
   5 import json
   6 from datetime import date, timedelta
   7 from random import randint
   8 import os.path
   9 import re
  10 from urllib.request import urlretrieve
  11 from django.conf import settings
  12 from django.db import connection, models, transaction
  13 import django.dispatch
  14 from django.contrib.contenttypes.fields import GenericRelation
  15 from django.urls import reverse
  16 from django.utils.translation import ugettext_lazy as _, get_language
  17 from django.utils.deconstruct import deconstructible
  18 from fnpdjango.storage import BofhFileSystemStorage
  19
  20 from librarian.cover import WLCover
  21 from librarian.html import transform_abstrakt
  22 from newtagging import managers
  23 from catalogue import constants
  24 from catalogue.fields import EbookField
  25 from catalogue.models import Tag, Fragment, BookMedia
  26 from catalogue.utils import create_zip, gallery_url, gallery_path, split_tags, get_random_hash
  27 from catalogue.models.tag import prefetched_relations
  28 from catalogue import app_settings
  29 from catalogue import tasks
  30 from wolnelektury.utils import makedirs, cached_render, clear_cached_renders
  31
  32 bofh_storage = BofhFileSystemStorage()
  33
  34
  35 @deconstructible
  36 class UploadToPath(object):
  37     def __init__(self, path):
  38         self.path = path
  39
  40     def __call__(self, instance, filename):
  41         return self.path % instance.slug
  42
  43
  44 _cover_upload_to = UploadToPath('book/cover/%s.jpg')
  45 _cover_thumb_upload_to = UploadToPath('book/cover_thumb/%s.jpg')
  46 _cover_api_thumb_upload_to = UploadToPath('book/cover_api_thumb/%s.jpg')
  47 _simple_cover_upload_to = UploadToPath('book/cover_simple/%s.jpg')
  48 _cover_ebookpoint_upload_to = UploadToPath('book/cover_ebookpoint/%s.jpg')
  49
  50
  51 def _ebook_upload_to(upload_path):
  52     return UploadToPath(upload_path)
  53
  54
  55 class Book(models.Model):
  56     """Represents a book imported from WL-XML."""
  57     title = models.CharField(_('title'), max_length=32767)
  58     sort_key = models.CharField(_('sort key'), max_length=120, db_index=True, editable=False)
  59     sort_key_author = models.CharField(
  60         _('sort key by author'), max_length=120, db_index=True, editable=False, default='')
  61     slug = models.SlugField(_('slug'), max_length=120, db_index=True, unique=True)
  62     common_slug = models.SlugField(_('slug'), max_length=120, db_index=True)
  63     language = models.CharField(_('language code'), max_length=3, db_index=True, default=app_settings.DEFAULT_LANGUAGE)
  64     description = models.TextField(_('description'), blank=True)
  65     abstract = models.TextField(_('abstract'), blank=True)
  66     created_at = models.DateTimeField(_('creation date'), auto_now_add=True, db_index=True)
  67     changed_at = models.DateTimeField(_('change date'), auto_now=True, db_index=True)
  68     parent_number = models.IntegerField(_('parent number'), default=0)
  69     extra_info = models.TextField(_('extra information'), default='{}')
  70     gazeta_link = models.CharField(blank=True, max_length=240)
  71     wiki_link = models.CharField(blank=True, max_length=240)
  72     print_on_demand = models.BooleanField(_('print on demand'), default=False)
  73     recommended = models.BooleanField(_('recommended'), default=False)
  74     audio_length = models.CharField(_('audio length'), blank=True, max_length=8)
  75     preview = models.BooleanField(_('preview'), default=False)
  76     preview_until = models.DateField(_('preview until'), blank=True, null=True)
  77     preview_key = models.CharField(max_length=32, blank=True, null=True)
  78     findable = models.BooleanField(_('findable'), default=True, db_index=True)
  79
  80     # files generated during publication
  81     cover = EbookField(
  82         'cover', _('cover'),
  83         null=True, blank=True,
  84         upload_to=_cover_upload_to,
  85         storage=bofh_storage, max_length=255)
  86     cover_etag = models.CharField(max_length=255, editable=False, default='', db_index=True)
  87     # Cleaner version of cover for thumbs
  88     cover_thumb = EbookField(
  89         'cover_thumb', _('cover thumbnail'),
  90         null=True, blank=True,
  91         upload_to=_cover_thumb_upload_to,
  92         max_length=255)
  93     cover_thumb_etag = models.CharField(max_length=255, editable=False, default='', db_index=True)
  94     cover_api_thumb = EbookField(
  95         'cover_api_thumb', _('cover thumbnail for mobile app'),
  96         null=True, blank=True,
  97         upload_to=_cover_api_thumb_upload_to,
  98         max_length=255)
  99     cover_api_thumb_etag = models.CharField(max_length=255, editable=False, default='', db_index=True)
 100     simple_cover = EbookField(
 101         'simple_cover', _('cover for mobile app'),
 102         null=True, blank=True,
 103         upload_to=_simple_cover_upload_to,
 104         max_length=255)
 105     simple_cover_etag = models.CharField(max_length=255, editable=False, default='', db_index=True)
 106     cover_ebookpoint = EbookField(
 107         'cover_ebookpoint', _('cover for Ebookpoint'),
 108         null=True, blank=True,
 109         upload_to=_cover_ebookpoint_upload_to,
 110         max_length=255)
 111     cover_ebookpoint_etag = models.CharField(max_length=255, editable=False, default='', db_index=True)
 112     ebook_formats = constants.EBOOK_FORMATS
 113     formats = ebook_formats + ['html', 'xml']
 114
 115     parent = models.ForeignKey('self', models.CASCADE, blank=True, null=True, related_name='children')
 116     ancestor = models.ManyToManyField('self', blank=True, editable=False, related_name='descendant', symmetrical=False)
 117
 118     cached_author = models.CharField(blank=True, max_length=240, db_index=True)
 119     has_audience = models.BooleanField(default=False)
 120
 121     objects = models.Manager()
 122     tagged = managers.ModelTaggedItemManager(Tag)
 123     tags = managers.TagDescriptor(Tag)
 124     tag_relations = GenericRelation(Tag.intermediary_table_model)
 125
 126     html_built = django.dispatch.Signal()
 127     published = django.dispatch.Signal()
 128
 129     SORT_KEY_SEP = '$'
 130
 131     class AlreadyExists(Exception):
 132         pass
 133
 134     class Meta:
 135         ordering = ('sort_key_author', 'sort_key')
 136         verbose_name = _('book')
 137         verbose_name_plural = _('books')
 138         app_label = 'catalogue'
 139
 140     def __str__(self):
 141         return self.title
 142
 143     def get_extra_info_json(self):
 144         return json.loads(self.extra_info or '{}')
 145
 146     def get_initial(self):
 147         try:
 148             return re.search(r'\w', self.title, re.U).group(0)
 149         except AttributeError:
 150             return ''
 151
 152     def authors(self):
 153         return self.tags.filter(category='author')
 154
 155     def epochs(self):
 156         return self.tags.filter(category='epoch')
 157
 158     def genres(self):
 159         return self.tags.filter(category='genre')
 160
 161     def kinds(self):
 162         return self.tags.filter(category='kind')
 163
 164     def tag_unicode(self, category):
 165         relations = prefetched_relations(self, category)
 166         if relations:
 167             return ', '.join(rel.tag.name for rel in relations)
 168         else:
 169             return ', '.join(self.tags.filter(category=category).values_list('name', flat=True))
 170
 171     def tags_by_category(self):
 172         return split_tags(self.tags.exclude(category__in=('set', 'theme')))
 173
 174     def author_unicode(self):
 175         return self.cached_author
 176
 177     def kind_unicode(self):
 178         return self.tag_unicode('kind')
 179
 180     def epoch_unicode(self):
 181         return self.tag_unicode('epoch')
 182
 183     def genre_unicode(self):
 184         return self.tag_unicode('genre')
 185
 186     def translators(self):
 187         translators = self.get_extra_info_json().get('translators') or []
 188         return [
 189             '\xa0'.join(reversed(translator.split(', ', 1))) for translator in translators
 190         ]
 191
 192     def translator(self):
 193         translators = self.get_extra_info_json().get('translators')
 194         if not translators:
 195             return None
 196         if len(translators) > 3:
 197             translators = translators[:2]
 198             others = ' i inni'
 199         else:
 200             others = ''
 201         return ', '.join('\xa0'.join(reversed(translator.split(', ', 1))) for translator in translators) + others
 202
 203     def cover_source(self):
 204         return self.get_extra_info_json().get('cover_source', self.parent.cover_source() if self.parent else '')
 205
 206     @property
 207     def isbn_pdf(self):
 208         return self.get_extra_info_json().get('isbn_pdf')
 209
 210     @property
 211     def isbn_epub(self):
 212         return self.get_extra_info_json().get('isbn_epub')
 213
 214     @property
 215     def isbn_mobi(self):
 216         return self.get_extra_info_json().get('isbn_mobi')
 217
 218
 219     def save(self, force_insert=False, force_update=False, **kwargs):
 220         from sortify import sortify
 221
 222         self.sort_key = sortify(self.title)[:120]
 223         self.title = str(self.title)  # ???
 224
 225         try:
 226             author = self.authors().first().sort_key
 227         except AttributeError:
 228             author = ''
 229         self.sort_key_author = author
 230
 231         self.cached_author = self.tag_unicode('author')
 232         self.has_audience = 'audience' in self.get_extra_info_json()
 233
 234         if self.preview and not self.preview_key:
 235             self.preview_key = get_random_hash(self.slug)[:32]
 236
 237         ret = super(Book, self).save(force_insert, force_update, **kwargs)
 238
 239         return ret
 240
 241     def get_absolute_url(self):
 242         return reverse('book_detail', args=[self.slug])
 243
 244     def gallery_path(self):
 245         return gallery_path(self.slug)
 246
 247     def gallery_url(self):
 248         return gallery_url(self.slug)
 249
 250     def get_first_text(self):
 251         if self.html_file:
 252             return self
 253         child = self.children.all().order_by('parent_number').first()
 254         if child is not None:
 255             return child.get_first_text()
 256
 257     def get_last_text(self):
 258         if self.html_file:
 259             return self
 260         child = self.children.all().order_by('parent_number').last()
 261         if child is not None:
 262             return child.get_last_text()
 263
 264     def get_prev_text(self):
 265         if not self.parent:
 266             return None
 267         sibling = self.parent.children.filter(parent_number__lt=self.parent_number).order_by('-parent_number').first()
 268         if sibling is not None:
 269             return sibling.get_last_text()
 270         return self.parent.get_prev_text()
 271
 272     def get_next_text(self):
 273         if not self.parent:
 274             return None
 275         sibling = self.parent.children.filter(parent_number__gt=self.parent_number).order_by('parent_number').first()
 276         if sibling is not None:
 277             return sibling.get_first_text()
 278         return self.parent.get_next_text()
 279
 280     def get_siblings(self):
 281         if not self.parent:
 282             return []
 283         return self.parent.children.all().order_by('parent_number')
 284
 285     @property
 286     def name(self):
 287         return self.title
 288
 289     def language_code(self):
 290         return constants.LANGUAGES_3TO2.get(self.language, self.language)
 291
 292     def language_name(self):
 293         return dict(settings.LANGUAGES).get(self.language_code(), "")
 294
 295     def is_foreign(self):
 296         return self.language_code() != settings.LANGUAGE_CODE
 297
 298     def set_audio_length(self):
 299         length = self.get_audio_length()
 300         if length > 0:
 301             self.audio_length = self.format_audio_length(length)
 302             self.save()
 303
 304     @staticmethod
 305     def format_audio_length(seconds):
 306         """
 307         >>> Book.format_audio_length(1)
 308         '0:01'
 309         >>> Book.format_audio_length(3661)
 310         '1:01:01'
 311         """
 312         if seconds < 60*60:
 313             minutes = seconds // 60
 314             seconds = seconds % 60
 315             return '%d:%02d' % (minutes, seconds)
 316         else:
 317             hours = seconds // 3600
 318             minutes = seconds % 3600 // 60
 319             seconds = seconds % 60
 320             return '%d:%02d:%02d' % (hours, minutes, seconds)
 321
 322     def get_audio_length(self):
 323         total = 0
 324         for media in self.get_mp3() or ():
 325             total += app_settings.GET_MP3_LENGTH(media.file.path)
 326         return int(total)
 327
 328     def has_media(self, type_):
 329         if type_ in Book.formats:
 330             return bool(getattr(self, "%s_file" % type_))
 331         else:
 332             return self.media.filter(type=type_).exists()
 333
 334     def has_audio(self):
 335         return self.has_media('mp3')
 336
 337     def get_media(self, type_):
 338         if self.has_media(type_):
 339             if type_ in Book.formats:
 340                 return getattr(self, "%s_file" % type_)
 341             else:
 342                 return self.media.filter(type=type_)
 343         else:
 344             return None
 345
 346     def get_mp3(self):
 347         return self.get_media("mp3")
 348
 349     def get_odt(self):
 350         return self.get_media("odt")
 351
 352     def get_ogg(self):
 353         return self.get_media("ogg")
 354
 355     def get_daisy(self):
 356         return self.get_media("daisy")
 357
 358     def media_url(self, format_):
 359         media = self.get_media(format_)
 360         if media:
 361             if self.preview:
 362                 return reverse('embargo_link', kwargs={'key': self.preview_key, 'slug': self.slug, 'format_': format_})
 363             else:
 364                 return media.url
 365         else:
 366             return None
 367
 368     def html_url(self):
 369         return self.media_url('html')
 370
 371     def pdf_url(self):
 372         return self.media_url('pdf')
 373
 374     def epub_url(self):
 375         return self.media_url('epub')
 376
 377     def mobi_url(self):
 378         return self.media_url('mobi')
 379
 380     def txt_url(self):
 381         return self.media_url('txt')
 382
 383     def fb2_url(self):
 384         return self.media_url('fb2')
 385
 386     def xml_url(self):
 387         return self.media_url('xml')
 388
 389     def has_description(self):
 390         return len(self.description) > 0
 391     has_description.short_description = _('description')
 392     has_description.boolean = True
 393
 394     def has_mp3_file(self):
 395         return self.has_media("mp3")
 396     has_mp3_file.short_description = 'MP3'
 397     has_mp3_file.boolean = True
 398
 399     def has_ogg_file(self):
 400         return self.has_media("ogg")
 401     has_ogg_file.short_description = 'OGG'
 402     has_ogg_file.boolean = True
 403
 404     def has_daisy_file(self):
 405         return self.has_media("daisy")
 406     has_daisy_file.short_description = 'DAISY'
 407     has_daisy_file.boolean = True
 408
 409     def get_audiobooks(self):
 410         ogg_files = {}
 411         for m in self.media.filter(type='ogg').order_by().iterator():
 412             ogg_files[m.name] = m
 413
 414         audiobooks = []
 415         projects = set()
 416         for mp3 in self.media.filter(type='mp3').iterator():
 417             # ogg files are always from the same project
 418             meta = mp3.get_extra_info_json()
 419             project = meta.get('project')
 420             if not project:
 421                 # temporary fallback
 422                 project = 'CzytamySłuchając'
 423
 424             projects.add((project, meta.get('funded_by', '')))
 425
 426             media = {'mp3': mp3}
 427
 428             ogg = ogg_files.get(mp3.name)
 429             if ogg:
 430                 media['ogg'] = ogg
 431             audiobooks.append(media)
 432
 433         projects = sorted(projects)
 434         return audiobooks, projects
 435
 436     def wldocument(self, parse_dublincore=True, inherit=True):
 437         from catalogue.import_utils import ORMDocProvider
 438         from librarian.parser import WLDocument
 439
 440         if inherit and self.parent:
 441             meta_fallbacks = self.parent.cover_info()
 442         else:
 443             meta_fallbacks = None
 444
 445         return WLDocument.from_file(
 446             self.xml_file.path,
 447             provider=ORMDocProvider(self),
 448             parse_dublincore=parse_dublincore,
 449             meta_fallbacks=meta_fallbacks)
 450
 451     @staticmethod
 452     def zip_format(format_):
 453         def pretty_file_name(book):
 454             return "%s/%s.%s" % (
 455                 book.get_extra_info_json()['author'],
 456                 book.slug,
 457                 format_)
 458
 459         field_name = "%s_file" % format_
 460         books = Book.objects.filter(parent=None).exclude(**{field_name: ""}).exclude(preview=True).exclude(findable=False)
 461         paths = [(pretty_file_name(b), getattr(b, field_name).path) for b in books.iterator()]
 462         return create_zip(paths, app_settings.FORMAT_ZIPS[format_])
 463
 464     def zip_audiobooks(self, format_):
 465         bm = BookMedia.objects.filter(book=self, type=format_)
 466         paths = map(lambda bm: (None, bm.file.path), bm)
 467         return create_zip(paths, "%s_%s" % (self.slug, format_))
 468
 469     def search_index(self, book_info=None, index=None, index_tags=True, commit=True):
 470         if not self.findable:
 471             return
 472         if index is None:
 473             from search.index import Index
 474             index = Index()
 475         try:
 476             index.index_book(self, book_info)
 477             if index_tags:
 478                 index.index_tags()
 479             if commit:
 480                 index.index.commit()
 481         except Exception as e:
 482             index.index.rollback()
 483             raise e
 484
 485     # will make problems in conjunction with paid previews
 486     def download_pictures(self, remote_gallery_url):
 487         gallery_path = self.gallery_path()
 488         # delete previous files, so we don't include old files in ebooks
 489         if os.path.isdir(gallery_path):
 490             for filename in os.listdir(gallery_path):
 491                 file_path = os.path.join(gallery_path, filename)
 492                 os.unlink(file_path)
 493         ilustr_elements = list(self.wldocument().edoc.findall('//ilustr'))
 494         if ilustr_elements:
 495             makedirs(gallery_path)
 496             for ilustr in ilustr_elements:
 497                 ilustr_src = ilustr.get('src')
 498                 ilustr_path = os.path.join(gallery_path, ilustr_src)
 499                 urlretrieve('%s/%s' % (remote_gallery_url, ilustr_src), ilustr_path)
 500
 501     def load_abstract(self):
 502         abstract = self.wldocument(parse_dublincore=False).edoc.getroot().find('.//abstrakt')
 503         if abstract is not None:
 504             self.abstract = transform_abstrakt(abstract)
 505         else:
 506             self.abstract = ''
 507
 508     @classmethod
 509     def from_xml_file(cls, xml_file, **kwargs):
 510         from django.core.files import File
 511         from librarian import dcparser
 512
 513         # use librarian to parse meta-data
 514         book_info = dcparser.parse(xml_file)
 515
 516         if not isinstance(xml_file, File):
 517             xml_file = File(open(xml_file))
 518
 519         try:
 520             return cls.from_text_and_meta(xml_file, book_info, **kwargs)
 521         finally:
 522             xml_file.close()
 523
 524     @classmethod
 525     def from_text_and_meta(cls, raw_file, book_info, overwrite=False, dont_build=None, search_index=True,
 526                            search_index_tags=True, remote_gallery_url=None, days=0, findable=True):
 527         if dont_build is None:
 528             dont_build = set()
 529         dont_build = set.union(set(dont_build), set(app_settings.DONT_BUILD))
 530
 531         # check for parts before we do anything
 532         children = []
 533         if hasattr(book_info, 'parts'):
 534             for part_url in book_info.parts:
 535                 try:
 536                     children.append(Book.objects.get(slug=part_url.slug))
 537                 except Book.DoesNotExist:
 538                     raise Book.DoesNotExist(_('Book "%s" does not exist.') % part_url.slug)
 539
 540         # Read book metadata
 541         book_slug = book_info.url.slug
 542         if re.search(r'[^a-z0-9-]', book_slug):
 543             raise ValueError('Invalid characters in slug')
 544         book, created = Book.objects.get_or_create(slug=book_slug)
 545
 546         if created:
 547             book_shelves = []
 548             old_cover = None
 549             book.preview = bool(days)
 550             if book.preview:
 551                 book.preview_until = date.today() + timedelta(days)
 552         else:
 553             if not overwrite:
 554                 raise Book.AlreadyExists(_('Book %s already exists') % book_slug)
 555             # Save shelves for this book
 556             book_shelves = list(book.tags.filter(category='set'))
 557             old_cover = book.cover_info()
 558
 559         # Save XML file
 560         book.xml_file.save('%s.xml' % book.slug, raw_file, save=False)
 561         if book.preview:
 562             book.xml_file.set_readable(False)
 563
 564         book.findable = findable
 565         book.language = book_info.language
 566         book.title = book_info.title
 567         if book_info.variant_of:
 568             book.common_slug = book_info.variant_of.slug
 569         else:
 570             book.common_slug = book.slug
 571         book.extra_info = json.dumps(book_info.to_dict())
 572         book.load_abstract()
 573         book.save()
 574
 575         meta_tags = Tag.tags_from_info(book_info)
 576
 577         for tag in meta_tags:
 578             if not tag.for_books:
 579                 tag.for_books = True
 580                 tag.save()
 581
 582         book.tags = set(meta_tags + book_shelves)
 583         book.save()  # update sort_key_author
 584
 585         cover_changed = old_cover != book.cover_info()
 586         obsolete_children = set(b for b in book.children.all()
 587                                 if b not in children)
 588         notify_cover_changed = []
 589         for n, child_book in enumerate(children):
 590             new_child = child_book.parent != book
 591             child_book.parent = book
 592             child_book.parent_number = n
 593             child_book.save()
 594             if new_child or cover_changed:
 595                 notify_cover_changed.append(child_book)
 596         # Disown unfaithful children and let them cope on their own.
 597         for child in obsolete_children:
 598             child.parent = None
 599             child.parent_number = 0
 600             child.save()
 601             if old_cover:
 602                 notify_cover_changed.append(child)
 603
 604         cls.repopulate_ancestors()
 605         tasks.update_counters.delay()
 606
 607         if remote_gallery_url:
 608             book.download_pictures(remote_gallery_url)
 609
 610         # No saves beyond this point.
 611
 612         # Build cover.
 613         if 'cover' not in dont_build:
 614             book.cover.build_delay()
 615             book.cover_thumb.build_delay()
 616             book.cover_api_thumb.build_delay()
 617             book.simple_cover.build_delay()
 618             book.cover_ebookpoint.build_delay()
 619
 620         # Build HTML and ebooks.
 621         book.html_file.build_delay()
 622         if not children:
 623             for format_ in constants.EBOOK_FORMATS_WITHOUT_CHILDREN:
 624                 if format_ not in dont_build:
 625                     getattr(book, '%s_file' % format_).build_delay()
 626         for format_ in constants.EBOOK_FORMATS_WITH_CHILDREN:
 627             if format_ not in dont_build:
 628                 getattr(book, '%s_file' % format_).build_delay()
 629
 630         if not settings.NO_SEARCH_INDEX and search_index and findable:
 631             tasks.index_book.delay(book.id, book_info=book_info, index_tags=search_index_tags)
 632
 633         for child in notify_cover_changed:
 634             child.parent_cover_changed()
 635
 636         book.update_popularity()
 637         tasks.update_references.delay(book.id)
 638
 639         cls.published.send(sender=cls, instance=book)
 640         return book
 641
 642     def get_master(self):
 643         master_tags = [
 644             'opowiadanie',
 645             'powiesc',
 646             'dramat_wierszowany_l',
 647             'dramat_wierszowany_lp',
 648             'dramat_wspolczesny', 'liryka_l', 'liryka_lp',
 649             'wywiad',
 650         ]
 651         from librarian.parser import WLDocument
 652         wld = WLDocument.from_file(self.xml_file.path, parse_dublincore=False)
 653         root = wld.edoc.getroot()
 654         for master in root.iter():
 655             if master.tag in master_tags:
 656                 return master
 657
 658     def update_references(self):
 659         from references.models import Entity, Reference
 660         master = self.get_master()
 661         found = set()
 662         for i, sec in enumerate(master):
 663             for ref in sec.findall('.//ref'):
 664                 href = ref.attrib.get('href', '')
 665                 if not href or href in found:
 666                     continue
 667                 found.add(href)
 668                 entity, created = Entity.objects.get_or_create(
 669                     uri=href
 670                 )
 671                 ref, created = Reference.objects.get_or_create(
 672                     book=self,
 673                     entity=entity
 674                 )
 675                 ref.first_section = 'sec%d' % (i + 1)
 676                 entity.populate()
 677                 entity.save()
 678         Reference.objects.filter(book=self).exclude(entity__uri__in=found).delete()
 679
 680     @classmethod
 681     @transaction.atomic
 682     def repopulate_ancestors(cls):
 683         """Fixes the ancestry cache."""
 684         # TODO: table names
 685         cursor = connection.cursor()
 686         if connection.vendor == 'postgres':
 687             cursor.execute("TRUNCATE catalogue_book_ancestor")
 688             cursor.execute("""
 689                 WITH RECURSIVE ancestry AS (
 690                     SELECT book.id, book.parent_id
 691                     FROM catalogue_book AS book
 692                     WHERE book.parent_id IS NOT NULL
 693                     UNION
 694                     SELECT ancestor.id, book.parent_id
 695                     FROM ancestry AS ancestor, catalogue_book AS book
 696                     WHERE ancestor.parent_id = book.id
 697                         AND book.parent_id IS NOT NULL
 698                     )
 699                 INSERT INTO catalogue_book_ancestor
 700                     (from_book_id, to_book_id)
 701                     SELECT id, parent_id
 702                     FROM ancestry
 703                     ORDER BY id;
 704                 """)
 705         else:
 706             cursor.execute("DELETE FROM catalogue_book_ancestor")
 707             for b in cls.objects.exclude(parent=None):
 708                 parent = b.parent
 709                 while parent is not None:
 710                     b.ancestor.add(parent)
 711                     parent = parent.parent
 712
 713     def clear_cache(self):
 714         clear_cached_renders(self.mini_box)
 715         clear_cached_renders(self.mini_box_nolink)
 716
 717     def cover_info(self, inherit=True):
 718         """Returns a dictionary to serve as fallback for BookInfo.
 719
 720         For now, the only thing inherited is the cover image.
 721         """
 722         need = False
 723         info = {}
 724         for field in ('cover_url', 'cover_by', 'cover_source'):
 725             val = self.get_extra_info_json().get(field)
 726             if val:
 727                 info[field] = val
 728             else:
 729                 need = True
 730         if inherit and need and self.parent is not None:
 731             parent_info = self.parent.cover_info()
 732             parent_info.update(info)
 733             info = parent_info
 734         return info
 735
 736     def related_themes(self):
 737         return Tag.objects.usage_for_queryset(
 738             Fragment.objects.filter(models.Q(book=self) | models.Q(book__ancestor=self)),
 739             counts=True).filter(category='theme')
 740
 741     def parent_cover_changed(self):
 742         """Called when parent book's cover image is changed."""
 743         if not self.cover_info(inherit=False):
 744             if 'cover' not in app_settings.DONT_BUILD:
 745                 self.cover.build_delay()
 746                 self.cover_thumb.build_delay()
 747                 self.cover_api_thumb.build_delay()
 748                 self.simple_cover.build_delay()
 749             for format_ in constants.EBOOK_FORMATS_WITH_COVERS:
 750                 if format_ not in app_settings.DONT_BUILD:
 751                     getattr(self, '%s_file' % format_).build_delay()
 752             for child in self.children.all():
 753                 child.parent_cover_changed()
 754
 755     def other_versions(self):
 756         """Find other versions (i.e. in other languages) of the book."""
 757         return type(self).objects.filter(common_slug=self.common_slug, findable=True).exclude(pk=self.pk)
 758
 759     def parents(self):
 760         books = []
 761         parent = self.parent
 762         while parent is not None:
 763             books.insert(0, parent)
 764             parent = parent.parent
 765         return books
 766
 767     def pretty_title(self, html_links=False):
 768         names = [(tag.name, tag.get_absolute_url()) for tag in self.authors().only('name', 'category', 'slug')]
 769         books = self.parents() + [self]
 770         names.extend([(b.title, b.get_absolute_url()) for b in books])
 771
 772         if html_links:
 773             names = ['<a href="%s">%s</a>' % (tag[1], tag[0]) for tag in names]
 774         else:
 775             names = [tag[0] for tag in names]
 776         return ', '.join(names)
 777
 778     def publisher(self):
 779         publisher = self.get_extra_info_json()['publisher']
 780         if isinstance(publisher, str):
 781             return publisher
 782         elif isinstance(publisher, list):
 783             return ', '.join(publisher)
 784
 785     @classmethod
 786     def tagged_top_level(cls, tags):
 787         """ Returns top-level books tagged with `tags`.
 788
 789         It only returns those books which don't have ancestors which are
 790         also tagged with those tags.
 791
 792         """
 793         objects = cls.tagged.with_all(tags)
 794         return objects.filter(findable=True).exclude(ancestor__in=objects)
 795
 796     @classmethod
 797     def book_list(cls, book_filter=None):
 798         """Generates a hierarchical listing of all books.
 799
 800         Books are optionally filtered with a test function.
 801
 802         """
 803
 804         books_by_parent = {}
 805         books = cls.objects.filter(findable=True).order_by('parent_number', 'sort_key').only('title', 'parent', 'slug', 'extra_info')
 806         if book_filter:
 807             books = books.filter(book_filter).distinct()
 808
 809             book_ids = set(b['pk'] for b in books.values("pk").iterator())
 810             for book in books.iterator():
 811                 parent = book.parent_id
 812                 if parent not in book_ids:
 813                     parent = None
 814                 books_by_parent.setdefault(parent, []).append(book)
 815         else:
 816             for book in books.iterator():
 817                 books_by_parent.setdefault(book.parent_id, []).append(book)
 818
 819         orphans = []
 820         books_by_author = OrderedDict()
 821         for tag in Tag.objects.filter(category='author').iterator():
 822             books_by_author[tag] = []
 823
 824         for book in books_by_parent.get(None, ()):
 825             authors = list(book.authors().only('pk'))
 826             if authors:
 827                 for author in authors:
 828                     books_by_author[author].append(book)
 829             else:
 830                 orphans.append(book)
 831
 832         return books_by_author, orphans, books_by_parent
 833
 834     _audiences_pl = {
 835         "SP": (1, "szkoła podstawowa"),
 836         "SP1": (1, "szkoła podstawowa"),
 837         "SP2": (1, "szkoła podstawowa"),
 838         "SP3": (1, "szkoła podstawowa"),
 839         "P": (1, "szkoła podstawowa"),
 840         "G": (2, "gimnazjum"),
 841         "L": (3, "liceum"),
 842         "LP": (3, "liceum"),
 843     }
 844
 845     def audiences_pl(self):
 846         audiences = self.get_extra_info_json().get('audiences', [])
 847         audiences = sorted(set([self._audiences_pl.get(a, (99, a)) for a in audiences]))
 848         return [a[1] for a in audiences]
 849
 850     def stage_note(self):
 851         stage = self.get_extra_info_json().get('stage')
 852         if stage and stage < '0.4':
 853             return (_('This work needs modernisation'),
 854                     reverse('infopage', args=['wymagajace-uwspolczesnienia']))
 855         else:
 856             return None, None
 857
 858     def choose_fragment(self):
 859         fragments = self.fragments.order_by()
 860         fragments_count = fragments.count()
 861         if not fragments_count and self.children.exists():
 862             fragments = Fragment.objects.filter(book__ancestor=self).order_by()
 863             fragments_count = fragments.count()
 864         if fragments_count:
 865             return fragments[randint(0, fragments_count - 1)]
 866         elif self.parent:
 867             return self.parent.choose_fragment()
 868         else:
 869             return None
 870
 871     def fragment_data(self):
 872         fragment = self.choose_fragment()
 873         if fragment:
 874             return {
 875                 'title': fragment.book.pretty_title(),
 876                 'html': re.sub('</?blockquote[^>]*>', '', fragment.get_short_text()),
 877             }
 878         else:
 879             return None
 880
 881     def update_popularity(self):
 882         count = self.tags.filter(category='set').values('user').order_by('user').distinct().count()
 883         try:
 884             pop = self.popularity
 885             pop.count = count
 886             pop.save()
 887         except BookPopularity.DoesNotExist:
 888             BookPopularity.objects.create(book=self, count=count)
 889
 890     def ridero_link(self):
 891         return 'https://ridero.eu/%s/books/wl_%s/' % (get_language(), self.slug.replace('-', '_'))
 892
 893     def like(self, user):
 894         from social.utils import likes, get_set, set_sets
 895         if not likes(user, self):
 896             tag = get_set(user, '')
 897             set_sets(user, self, [tag])
 898
 899     def unlike(self, user):
 900         from social.utils import likes, set_sets
 901         if likes(user, self):
 902             set_sets(user, self, [])
 903
 904     def full_sort_key(self):
 905         return self.SORT_KEY_SEP.join((self.sort_key_author, self.sort_key, str(self.id)))
 906
 907     def cover_color(self):
 908         return WLCover.epoch_colors.get(self.get_extra_info_json().get('epoch'), '#000000')
 909
 910     @cached_render('catalogue/book_mini_box.html')
 911     def mini_box(self):
 912         return {
 913             'book': self
 914         }
 915
 916     @cached_render('catalogue/book_mini_box.html')
 917     def mini_box_nolink(self):
 918         return {
 919             'book': self,
 920             'no_link': True,
 921         }
 922
 923 def add_file_fields():
 924     for format_ in Book.formats:
 925         field_name = "%s_file" % format_
 926         # This weird globals() assignment makes Django migrations comfortable.
 927         _upload_to = _ebook_upload_to('book/%s/%%s.%s' % (format_, format_))
 928         _upload_to.__name__ = '_%s_upload_to' % format_
 929         globals()[_upload_to.__name__] = _upload_to
 930
 931         EbookField(
 932             format_, _("%s file" % format_.upper()),
 933             upload_to=_upload_to,
 934             storage=bofh_storage,
 935             max_length=255,
 936             blank=True,
 937             default=''
 938         ).contribute_to_class(Book, field_name)
 939         if format_ != 'xml':
 940             models.CharField(max_length=255, editable=False, default='', db_index=True).contribute_to_class(Book, f'{field_name}_etag')
 941
 942
 943 add_file_fields()
 944
 945
 946 class BookPopularity(models.Model):
 947     book = models.OneToOneField(Book, models.CASCADE, related_name='popularity')
 948     count = models.IntegerField(default=0, db_index=True)