src/backend/blog.py

   1 #!/usr/bin/python
   2
   3 import datetime
   4 import feedparser
   5 import html2text
   6 import markdown
   7 import markdown.extensions
   8 import markdown.preprocessors
   9 import re
  10 import textile
  11 import unicodedata
  12
  13 from . import misc
  14 from .decorators import *
  15
  16 class Blog(misc.Object):
  17         def _get_post(self, query, *args):
  18                 res = self.db.get(query, *args)
  19
  20                 if res:
  21                         return Post(self.backend, res.id, data=res)
  22
  23         def _get_posts(self, query, *args):
  24                 res = self.db.query(query, *args)
  25
  26                 for row in res:
  27                         yield Post(self.backend, row.id, data=row)
  28
  29         def get_by_id(self, id):
  30                 return self._get_post("SELECT * FROM blog \
  31                         WHERE id = %s", id)
  32
  33         def get_by_slug(self, slug, published=True):
  34                 if published:
  35                         return self._get_post("SELECT * FROM blog \
  36                                 WHERE slug = %s AND published_at <= NOW()", slug)
  37
  38                 return self._get_post("SELECT * FROM blog \
  39                         WHERE slug = %s", slug)
  40
  41         def get_newest(self, limit=None):
  42                 posts = self._get_posts("SELECT * FROM blog \
  43                         WHERE published_at IS NOT NULL \
  44                                 AND published_at <= NOW() \
  45                         ORDER BY published_at DESC LIMIT %s", limit)
  46
  47                 return list(posts)
  48
  49         def get_by_tag(self, tag, limit=None):
  50                 return self._get_posts("SELECT * FROM blog \
  51                         WHERE published_at IS NOT NULL \
  52                                 AND published_at <= NOW() \
  53                                 AND %s = ANY(tags) \
  54                         ORDER BY published_at DESC LIMIT %s", tag, limit)
  55
  56         def get_by_author(self, author, limit=None):
  57                 return self._get_posts("SELECT * FROM blog \
  58                         WHERE (author = %s OR author_uid = %s) \
  59                                 AND published_at IS NOT NULL \
  60                                 AND published_at <= NOW() \
  61                         ORDER BY published_at DESC LIMIT %s",
  62                         author.name, author.uid, limit)
  63
  64         def get_by_year(self, year):
  65                 return self._get_posts("SELECT * FROM blog \
  66                         WHERE EXTRACT(year FROM published_at) = %s \
  67                                 AND published_at IS NOT NULL \
  68                                 AND published_at <= NOW() \
  69                         ORDER BY published_at DESC", year)
  70
  71         def get_drafts(self, author=None, limit=None):
  72                 if author:
  73                         return self._get_posts("SELECT * FROM blog \
  74                                 WHERE author_uid = %s \
  75                                         AND (published_at IS NULL OR published_at > NOW()) \
  76                                 ORDER BY COALESCE(updated_at, created_at) DESC LIMIT %s",
  77                                 author.uid, limit)
  78
  79                 return self._get_posts("SELECT * FROM blog \
  80                         WHERE (published_at IS NULL OR published_at > NOW()) \
  81                         ORDER BY COALESCE(updated_at, created_at) DESC LIMIT %s", limit)
  82
  83         def search(self, query, limit=None):
  84                 posts = self._get_posts("SELECT blog.* FROM blog \
  85                         LEFT JOIN blog_search_index search_index ON blog.id = search_index.post_id \
  86                         WHERE search_index.document @@ websearch_to_tsquery('english', %s) \
  87                                 ORDER BY ts_rank(search_index.document, websearch_to_tsquery('english', %s)) DESC \
  88                         LIMIT %s", query, query, limit)
  89
  90                 return list(posts)
  91
  92         def has_had_recent_activity(self, **kwargs):
  93                 t = datetime.timedelta(**kwargs)
  94
  95                 res = self.db.get("SELECT COUNT(*) AS count FROM blog \
  96                         WHERE published_at IS NOT NULL AND published_at BETWEEN NOW() - %s AND NOW()", t)
  97
  98                 if res and res.count > 0:
  99                         return True
 100
 101                 return False
 102
 103         def create_post(self, title, text, author, tags=[], lang="markdown"):
 104                 """
 105                         Creates a new post and returns the resulting Post object
 106                 """
 107                 # Pre-render HTML
 108                 html = self._render_text(text, lang=lang)
 109
 110                 return self._get_post("INSERT INTO blog(title, slug, text, html, lang, author_uid, tags) \
 111                         VALUES(%s, %s, %s, %s, %s, %s, %s) RETURNING *", title, self._make_slug(title), text,
 112                         html, lang, author.uid, list(tags))
 113
 114         def _make_slug(self, s):
 115                 # Remove any non-ASCII characters
 116                 try:
 117                         s = unicodedata.normalize("NFKD", s)
 118                 except TypeError:
 119                         pass
 120
 121                 # Remove excessive whitespace
 122                 s = re.sub(r"[^\w]+", " ", s)
 123
 124                 slug = "-".join(s.split()).lower()
 125
 126                 while True:
 127                         e = self.db.get("SELECT 1 FROM blog WHERE slug = %s", slug)
 128                         if not e:
 129                                 break
 130
 131                         slug += "-"
 132
 133                 return slug
 134
 135         def _render_text(self, text, lang="markdown"):
 136                 if lang == "markdown":
 137                         return markdown.markdown(text,
 138                                 extensions=[
 139                                         PrettyLinksExtension(),
 140                                         "codehilite",
 141                                         "fenced_code",
 142                                         "footnotes",
 143                                         "nl2br",
 144                                         "sane_lists",
 145                                         "tables",
 146                                         "toc",
 147                                 ])
 148
 149                 elif lang == "textile":
 150                         return textile.textile(text)
 151
 152                 return text
 153
 154         def refresh(self):
 155                 """
 156                         Needs to be called after a post has been changed
 157                         and updates the search index.
 158                 """
 159                 self.db.execute("REFRESH MATERIALIZED VIEW blog_search_index")
 160
 161         @property
 162         def years(self):
 163                 res = self.db.query("SELECT DISTINCT EXTRACT(year FROM published_at)::integer AS year \
 164                         FROM blog WHERE published_at IS NOT NULL AND published_at <= NOW() \
 165                         ORDER BY year DESC")
 166
 167                 for row in res:
 168                         yield row.year
 169
 170         @property
 171         def authors(self):
 172                 res = self.db.query("""
 173                         SELECT
 174                                 author_uid,
 175                                 MAX(published_at) AS published_at
 176                         FROM
 177                                 blog
 178                         WHERE
 179                                 author_uid IS NOT NULL
 180                         AND
 181                                 published_at IS NOT NULL
 182                         AND
 183                                 published_at <= NOW()
 184                         GROUP BY
 185                                 author_uid
 186                         ORDER BY
 187                                 published_at DESC
 188                         """,
 189                 )
 190
 191                 return [self.backend.accounts.get_by_uid(row.author_uid) for row in res]
 192
 193         async def announce(self):
 194                 posts = self._get_posts("SELECT * FROM blog \
 195                         WHERE (published_at IS NOT NULL AND published_at <= NOW()) \
 196                                 AND announced_at IS NULL")
 197
 198                 for post in posts:
 199                         await post.announce()
 200
 201         async def update_feeds(self):
 202                 """
 203                         Updates all enabled feeds
 204                 """
 205                 for feed in self.db.query("SELECT * FROM blog_feeds WHERE enabled IS TRUE"):
 206                         try:
 207                                 f = feedparser.parse(feed.url)
 208                         except Exception as e:
 209                                 raise e
 210
 211                         with self.db.transaction():
 212                                 # Update name
 213                                 self.db.execute("UPDATE blog_feeds SET name = %s \
 214                                         WHERE id = %s", f.feed.title, feed.id)
 215
 216                                 # Walk through all entries
 217                                 for entry in f.entries:
 218                                         # Skip everything without the "blog.ipfire.org" tag
 219                                         try:
 220                                                 tags = list((t.term for t in entry.tags))
 221
 222                                                 if not "blog.ipfire.org" in tags:
 223                                                         continue
 224                                         except AttributeError:
 225                                                 continue
 226
 227                                         # Get link to the posting site
 228                                         link = entry.links[0].href
 229
 230                                         # Check if the entry has already been imported
 231                                         res = self.db.get("SELECT id, (updated_at < %s) AS needs_update \
 232                                                         FROM blog WHERE feed_id = %s AND foreign_id = %s",
 233                                                         entry.updated, feed.id, entry.id)
 234                                         if res:
 235                                                 # If the post needs to be updated, we do so
 236                                                 if res.needs_update:
 237                                                         self.db.execute("UPDATE blog SET title = %s, author = %s, \
 238                                                                 published_at = %s, updated_at = %s, html = %s, link = %s, \
 239                                                                 tags = %s WHERE id = %s", entry.title, entry.author,
 240                                                                 entry.published, entry.updated, entry.summary, link,
 241                                                                 feed.tags + tags, res.id)
 242
 243                                                 # Done here
 244                                                 continue
 245
 246                                         # Insert the new post
 247                                         self.db.execute("INSERT INTO blog(title, slug, author, \
 248                                                 published_at, html, link, tags, updated_at, feed_id, foreign_id) \
 249                                                 VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
 250                                                 entry.title, self._make_slug(entry.title), entry.author,
 251                                                 entry.published, entry.summary, link, feed.tags + tags,
 252                                                 entry.updated, feed.id, entry.id)
 253
 254                                 # Mark feed as updated
 255                                 self.db.execute("UPDATE blog_feeds SET last_updated_at = CURRENT_TIMESTAMP \
 256                                         WHERE id = %s" % feed.id)
 257
 258                 # Refresh the search index
 259                 with self.db.transaction():
 260                         self.refresh()
 261
 262
 263 class Post(misc.Object):
 264         def init(self, id, data=None):
 265                 self.id   = id
 266                 self.data = data
 267
 268         # Title
 269
 270         @property
 271         def title(self):
 272                 return self.data.title
 273
 274         @property
 275         def slug(self):
 276                 return self.data.slug
 277
 278         @lazy_property
 279         def author(self):
 280                 if self.data.author_uid:
 281                         return self.backend.accounts.get_by_uid(self.data.author_uid)
 282
 283                 return self.data.author
 284
 285         @property
 286         def created_at(self):
 287                 return self.data.created_at
 288
 289         @property
 290         def lang(self):
 291                 return self.data.lang
 292
 293         # Published?
 294
 295         @property
 296         def published_at(self):
 297                 return self.data.published_at
 298
 299         def is_published(self):
 300                 """
 301                         Returns True if the post is already published
 302                 """
 303                 return self.published_at and self.published_at <= datetime.datetime.now()
 304
 305         def publish(self, when=None):
 306                 if self.is_published():
 307                         return
 308
 309                 self.db.execute("UPDATE blog SET published_at = COALESCE(%s, CURRENT_TIMESTAMP) \
 310                         WHERE id = %s", when, self.id)
 311
 312                 # Update search indices
 313                 self.backend.blog.refresh()
 314
 315         # Updated?
 316
 317         @property
 318         def updated_at(self):
 319                 return self.data.updated_at
 320
 321         # Text
 322
 323         @property
 324         def text(self):
 325                 return self.data.text
 326
 327         # HTML
 328
 329         @lazy_property
 330         def html(self):
 331                 """
 332                         Returns this post as rendered HTML
 333                 """
 334                 return self.data.html or self.backend.blog._render_text(self.text, lang=self.lang)
 335
 336         @lazy_property
 337         def plaintext(self):
 338                 h = html2text.HTML2Text()
 339                 h.ignore_links = True
 340
 341                 return h.handle(self.html)
 342
 343         # Excerpt
 344
 345         @property
 346         def excerpt(self):
 347                 paragraphs = self.plaintext.split("\n\n")
 348
 349                 excerpt = []
 350
 351                 for paragraph in paragraphs:
 352                         excerpt.append(paragraph)
 353
 354                         # Add another paragraph if we encountered a headline
 355                         if paragraph.startswith("#"):
 356                                 continue
 357
 358                         # End if this paragraph was long enough
 359                         if len(paragraph) >= 40:
 360                                 break
 361
 362                 return "\n\n".join(excerpt)
 363
 364         # Tags
 365
 366         @property
 367         def tags(self):
 368                 return self.data.tags
 369
 370         # Link
 371
 372         @property
 373         def link(self):
 374                 return self.data.link
 375
 376         @lazy_property
 377         def release(self):
 378                 return self.backend.releases._get_release("SELECT * FROM releases \
 379                         WHERE published IS NOT NULL AND published <= NOW() AND blog_id = %s", self.id)
 380
 381         def is_editable(self, user):
 382                 # Anonymous users cannot do anything
 383                 if not user:
 384                         return False
 385
 386                 # Admins can edit anything
 387                 if user.is_admin():
 388                         return True
 389
 390                 # User must have permission for the blog
 391                 if not user.is_blog_author():
 392                         return False
 393
 394                 # Authors can edit their own posts
 395                 return self.author == user
 396
 397         def update(self, title, text, tags=[]):
 398                 """
 399                         Called to update the content of this post
 400                 """
 401                 # Update slug when post isn't published yet
 402                 slug = self.backend.blog._make_slug(title) \
 403                         if not self.is_published() and not self.title == title else self.slug
 404
 405                 # Render and cache HTML
 406                 html = self.backend.blog._render_text(text, lang=self.lang)
 407
 408                 self.db.execute("UPDATE blog SET title = %s, slug = %s, text = %s, html = %s, \
 409                         tags = %s, updated_at = CURRENT_TIMESTAMP WHERE id = %s",
 410                         title, slug, text, html, list(tags), self.id)
 411
 412                 # Update cache
 413                 self.data.update({
 414                         "title" : title,
 415                         "slug"  : slug,
 416                         "text"  : text,
 417                         "html"  : html,
 418                         "tags"  : tags,
 419                 })
 420
 421                 # Update search index if post is published
 422                 if self.is_published():
 423                         self.backend.blog.refresh()
 424
 425         def delete(self):
 426                 self.db.execute("DELETE FROM blog WHERE id = %s", self.id)
 427
 428                 # Update search indices
 429                 self.backend.blog.refresh()
 430
 431         async def announce(self):
 432                 # Get people who should receive this message
 433                 group = self.backend.groups.get_by_gid("promotional-consent")
 434                 if not group:
 435                         return
 436
 437                 with self.db.transaction():
 438                         # Generate an email for everybody in this group
 439                         for account in group:
 440                                 self.backend.messages.send_template("blog/messages/announcement",
 441                                         account=account, post=self)
 442
 443                         # Mark this post as announced
 444                         self.db.execute("UPDATE blog SET announced_at = CURRENT_TIMESTAMP \
 445                                 WHERE id = %s", self.id)
 446
 447
 448 class PrettyLinksExtension(markdown.extensions.Extension):
 449         def extendMarkdown(self, md):
 450                 md.preprocessors.register(BugzillaLinksPreprocessor(md), "bugzilla", 10)
 451                 md.preprocessors.register(CVELinksPreprocessor(md), "cve", 10)
 452
 453
 454 class BugzillaLinksPreprocessor(markdown.preprocessors.Preprocessor):
 455         regex = re.compile(r"(?:#(\d{5,}))", re.I)
 456
 457         def run(self, lines):
 458                 for line in lines:
 459                         yield self.regex.sub(r"[#\1](https://bugzilla.ipfire.org/show_bug.cgi?id=\1)", line)
 460
 461
 462 class CVELinksPreprocessor(markdown.preprocessors.Preprocessor):
 463         regex = re.compile(r"(?:CVE)[\s\-](\d{4}\-\d+)")
 464
 465         def run(self, lines):
 466                 for line in lines:
 467                         yield self.regex.sub(r"[CVE-\1](https://cve.mitre.org/cgi-bin/cvename.cgi?name=\1)", line)