src/backend/blog.py

   1 #!/usr/bin/python
   2
   3 import datetime
   4 import feedparser
   5 import re
   6 import textile
   7 import unicodedata
   8
   9 from . import misc
  10
  11 class Blog(misc.Object):
  12         def _get_post(self, query, *args):
  13                 res = self.db.get(query, *args)
  14
  15                 if res:
  16                         return Post(self.backend, res.id, data=res)
  17
  18         def _get_posts(self, query, *args):
  19                 res = self.db.query(query, *args)
  20
  21                 for row in res:
  22                         yield Post(self.backend, row.id, data=row)
  23
  24         def get_by_id(self, id):
  25                 return self._get_post("SELECT * FROM blog \
  26                         WHERE id = %s", id)
  27
  28         def get_by_slug(self, slug, published=True):
  29                 if published:
  30                         return self._get_post("SELECT * FROM blog \
  31                                 WHERE slug = %s AND published_at <= NOW()", slug)
  32
  33                 return self._get_post("SELECT * FROM blog \
  34                         WHERE slug = %s", slug)
  35
  36
  37         def get_newest(self, limit=None):
  38                 return self._get_posts("SELECT * FROM blog \
  39                         WHERE published_at IS NOT NULL \
  40                                 AND published_at <= NOW() \
  41                         ORDER BY published_at DESC LIMIT %s", limit)
  42
  43         def get_by_tag(self, tag, limit=None):
  44                 return self._get_posts("SELECT * FROM blog \
  45                         WHERE published_at IS NOT NULL \
  46                                 AND published_at <= NOW() \
  47                                 AND %s = ANY(tags) \
  48                         ORDER BY published_at DESC LIMIT %s", tag, limit)
  49
  50         def get_by_author(self, author, limit=None):
  51                 return self._get_posts("SELECT * FROM blog \
  52                         WHERE (author = %s OR author_uid = %s) \
  53                                 AND published_at IS NOT NULL \
  54                                 AND published_at <= NOW() \
  55                         ORDER BY published_at DESC LIMIT %s",
  56                         author.name, author.uid, limit)
  57
  58         def get_by_year(self, year):
  59                 return self._get_posts("SELECT * FROM blog \
  60                         WHERE EXTRACT(year FROM published_at) = %s \
  61                                 AND published_at IS NOT NULL \
  62                                 AND published_at <= NOW() \
  63                         ORDER BY published_at DESC", year)
  64
  65         def get_drafts(self, author=None, limit=None):
  66                 if author:
  67                         return self._get_posts("SELECT * FROM blog \
  68                                 WHERE author_uid = %s \
  69                                         AND (published_at IS NULL OR published_at > NOW()) \
  70                                 ORDER BY COALESCE(updated_at, created_at) DESC LIMIT %s",
  71                                 author.uid, limit)
  72
  73                 return self._get_posts("SELECT * FROM blog \
  74                         WHERE (published_at IS NULL OR published_at > NOW()) \
  75                         ORDER BY COALESCE(updated_at, created_at) DESC LIMIT %s", limit)
  76
  77         def search(self, query, limit=None):
  78                 return self._get_posts("SELECT blog.* FROM blog \
  79                         LEFT JOIN blog_search_index search_index ON blog.id = search_index.post_id \
  80                         WHERE search_index.document @@ to_tsquery('english', %s) \
  81                                 ORDER BY ts_rank(search_index.document, to_tsquery('english', %s)) DESC \
  82                         LIMIT %s", query, query, limit)
  83
  84         def create_post(self, title, text, author, tags=[]):
  85                 """
  86                         Creates a new post and returns the resulting Post object
  87                 """
  88                 return self._get_post("INSERT INTO blog(title, slug, text, author_uid, tags) \
  89                         VALUES(%s, %s, %s, %s, %s) RETURNING *", title, self._make_slug(title),
  90                         text, author.uid, list(tags))
  91
  92         def _make_slug(self, s):
  93                 # Remove any non-ASCII characters
  94                 try:
  95                         s = unicodedata.normalize("NFKD", s)
  96                 except TypeError:
  97                         pass
  98
  99                 # Remove excessive whitespace
 100                 s = re.sub(r"[^\w]+", " ", s)
 101
 102                 slug = "-".join(s.split()).lower()
 103
 104                 while True:
 105                         e = self.db.get("SELECT 1 FROM blog WHERE slug = %s", slug)
 106                         if not e:
 107                                 break
 108
 109                         slug += "-"
 110
 111                 return slug
 112
 113         def refresh(self):
 114                 """
 115                         Needs to be called after a post has been changed
 116                         and updates the search index.
 117                 """
 118                 self.db.execute("REFRESH MATERIALIZED VIEW blog_search_index")
 119
 120         @property
 121         def years(self):
 122                 res = self.db.query("SELECT DISTINCT EXTRACT(year FROM published_at)::integer AS year \
 123                         FROM blog WHERE published_at IS NOT NULL AND published_at <= NOW() \
 124                         ORDER BY year DESC")
 125
 126                 for row in res:
 127                         yield row.year
 128
 129         def update_feeds(self):
 130                 """
 131                         Updates all enabled feeds
 132                 """
 133                 for feed in self.db.query("SELECT * FROM blog_feeds WHERE enabled IS TRUE"):
 134                         try:
 135                                 f = feedparser.parse(feed.url)
 136                         except Exception as e:
 137                                 raise e
 138
 139                         with self.db.transaction():
 140                                 # Update name
 141                                 self.db.execute("UPDATE blog_feeds SET name = %s \
 142                                         WHERE id = %s", f.feed.title, feed.id)
 143
 144                                 # Walk through all entries
 145                                 for entry in f.entries:
 146                                         # Skip everything without the "blog.ipfire.org" tag
 147                                         try:
 148                                                 tags = list((t.term for t in entry.tags))
 149
 150                                                 if not "blog.ipfire.org" in tags:
 151                                                         continue
 152                                         except AttributeError:
 153                                                 continue
 154
 155                                         # Get link to the posting site
 156                                         link = entry.links[0].href
 157
 158                                         # Check if the entry has already been imported
 159                                         res = self.db.get("SELECT id, (updated_at < %s) AS needs_update \
 160                                                         FROM blog WHERE feed_id = %s AND foreign_id = %s",
 161                                                         entry.updated, feed.id, entry.id)
 162                                         if res:
 163                                                 # If the post needs to be updated, we do so
 164                                                 if res.needs_update:
 165                                                         self.db.execute("UPDATE blog SET title = %s, author = %s, \
 166                                                                 published_at = %s, updated_at = %s, html = %s, link = %s, \
 167                                                                 tags = %s WHERE id = %s", entry.title, entry.author,
 168                                                                 entry.published, entry.updated, entry.summary, link,
 169                                                                 feed.tags + tags, res.id)
 170
 171                                                 # Done here
 172                                                 continue
 173
 174                                         # Insert the new post
 175                                         self.db.execute("INSERT INTO blog(title, slug, author, \
 176                                                 published_at, html, link, tags, updated_at, feed_id, foreign_id) \
 177                                                 VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
 178                                                 entry.title, self._make_slug(entry.title), entry.author,
 179                                                 entry.published, entry.summary, link, feed.tags + tags,
 180                                                 entry.updated, feed.id, entry.id)
 181
 182                                 # Mark feed as updated
 183                                 self.db.execute("UPDATE blog_feeds SET last_updated_at = CURRENT_TIMESTAMP \
 184                                         WHERE id = %s" % feed.id)
 185
 186                 # Refresh the search index
 187                 with self.db.transaction():
 188                         self.refresh()
 189
 190
 191 class Post(misc.Object):
 192         def init(self, id, data=None):
 193                 self.id   = id
 194                 self.data = data
 195
 196         # Title
 197
 198         def get_title(self):
 199                 return self.data.title
 200
 201         def set_title(self, title):
 202                 self.db.execute("UPDATE blog SET title = %s \
 203                         WHERE id = %s", title, self.id)
 204
 205                 # Update slug if post is not published, yet
 206                 if not self.is_published():
 207                         self.db.execute("UPDATE blog SET slug = %s \
 208                                 WHERE id = %s", self.backend.blog._make_slug(title), self.id)
 209
 210         title = property(get_title, set_title)
 211
 212         @property
 213         def slug(self):
 214                 return self.data.slug
 215
 216         # XXX needs caching
 217         @property
 218         def author(self):
 219                 if self.data.author_uid:
 220                         return self.backend.accounts.get_by_uid(self.data.author_uid)
 221
 222                 return self.data.author
 223
 224         @property
 225         def created_at(self):
 226                 return self.data.created_at
 227
 228         # Published?
 229
 230         @property
 231         def published_at(self):
 232                 return self.data.published_at
 233
 234         def is_published(self):
 235                 """
 236                         Returns True if the post is already published
 237                 """
 238                 return self.published_at and self.published_at <= datetime.datetime.now()
 239
 240         def publish(self):
 241                 if self.is_published():
 242                         return
 243
 244                 self.db.execute("UPDATE blog SET published_at = NOW() \
 245                         WHERE id = %s", self.id)
 246
 247                 # Update search indices
 248                 self.backend.blog.refresh()
 249
 250         # Updated?
 251
 252         @property
 253         def updated_at(self):
 254                 return self.data.updated_at
 255
 256         def updated(self):
 257                 self.db.execute("UPDATE blog SET updated_at = NOW() \
 258                         WHERE id = %s", self.id)
 259
 260                 # Update search indices
 261                 self.backend.blog.refresh()
 262
 263         # Text
 264
 265         def get_text(self):
 266                 return self.data.text
 267
 268         def set_text(self, text):
 269                 self.db.execute("UPDATE blog SET text = %s \
 270                         WHERE id = %s", text, self.id)
 271
 272         text = property(get_text, set_text)
 273
 274         # HTML
 275
 276         @property
 277         def html(self):
 278                 """
 279                         Returns this post as rendered HTML
 280                 """
 281                 return self.data.html or textile.textile(self.text.decode("utf-8"))
 282
 283         # Tags
 284
 285         def get_tags(self):
 286                 return self.data.tags
 287
 288         def set_tags(self, tags):
 289                 self.db.execute("UPDATE blog SET tags = %s \
 290                         WHERE id = %s", list(tags), self.id)
 291
 292         tags = property(get_tags, set_tags)
 293
 294         @property
 295         def link(self):
 296                 return self.data.link
 297
 298         # XXX needs caching
 299         @property
 300         def release(self):
 301                 return self.backend.releases._get_release("SELECT * FROM releases \
 302                         WHERE published IS NOT NULL AND published <= NOW() AND blog_id = %s", self.id)