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