[ipfire.org.git] / src / backend / blog.py

#!/usr/bin/python

import datetime
import feedparser
import markdown2
import re
import textile
import unicodedata

from . import misc
from .decorators import *

# Used to automatically link some things
link_patterns = (
	# Find bug reports
	(re.compile(r"(?:#(\d+))", re.I), r"https://bugzilla.ipfire.org/show_bug.cgi?id=\1"),

	# Email Addresses
	(re.compile(r"([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+)"), r"mailto:\1"),

	# CVE Numbers
	(re.compile(r"(?:CVE)[\s\-](\d{4}\-\d+)"), r"https://cve.mitre.org/cgi-bin/cvename.cgi?name=\1"),
)

class Blog(misc.Object):
	def _get_post(self, query, *args):
		res = self.db.get(query, *args)

		if res:
			return Post(self.backend, res.id, data=res)

	def _get_posts(self, query, *args):
		res = self.db.query(query, *args)

		for row in res:
			yield Post(self.backend, row.id, data=row)

	def get_by_id(self, id):
		return self._get_post("SELECT * FROM blog \
			WHERE id = %s", id)

	def get_by_slug(self, slug, published=True):
		if published:
			return self._get_post("SELECT * FROM blog \
				WHERE slug = %s AND published_at <= NOW()", slug)

		return self._get_post("SELECT * FROM blog \
			WHERE slug = %s", slug)

	def get_newest(self, limit=None):
		return self._get_posts("SELECT * FROM blog \
			WHERE published_at IS NOT NULL \
				AND published_at <= NOW() \
			ORDER BY published_at DESC LIMIT %s", limit)

	def get_by_tag(self, tag, limit=None):
		return self._get_posts("SELECT * FROM blog \
			WHERE published_at IS NOT NULL \
				AND published_at <= NOW() \
				AND %s = ANY(tags) \
			ORDER BY published_at DESC LIMIT %s", tag, limit)

	def get_by_author(self, author, limit=None):
		return self._get_posts("SELECT * FROM blog \
			WHERE (author = %s OR author_uid = %s) \
				AND published_at IS NOT NULL \
				AND published_at <= NOW() \
			ORDER BY published_at DESC LIMIT %s",
			author.name, author.uid, limit)

	def get_by_year(self, year):
		return self._get_posts("SELECT * FROM blog \
			WHERE EXTRACT(year FROM published_at) = %s \
				AND published_at IS NOT NULL \
				AND published_at <= NOW() \
			ORDER BY published_at DESC", year)

	def get_drafts(self, author=None, limit=None):
		if author:
			return self._get_posts("SELECT * FROM blog \
				WHERE author_uid = %s \
					AND (published_at IS NULL OR published_at > NOW()) \
				ORDER BY COALESCE(updated_at, created_at) DESC LIMIT %s",
				author.uid, limit)

		return self._get_posts("SELECT * FROM blog \
			WHERE (published_at IS NULL OR published_at > NOW()) \
			ORDER BY COALESCE(updated_at, created_at) DESC LIMIT %s", limit)

	def search(self, query, limit=None):
		query = self._parse_search_query(query)

		return self._get_posts("SELECT blog.* FROM blog \
			LEFT JOIN blog_search_index search_index ON blog.id = search_index.post_id \
			WHERE search_index.document @@ to_tsquery('english', %s) \
				ORDER BY ts_rank(search_index.document, to_tsquery('english', %s)) DESC \
			LIMIT %s", query, query, limit)

	def _parse_search_query(self, query):
		q = []
		for word in query.split():
			# Is this lexeme negated?
			negated = word.startswith("!")

			# Remove any special characters
			word = re.sub(r"\W+", "", word, flags=re.UNICODE)
			if not word:
				continue

			# Restore negation
			if negated:
				word = "!%s" % word

			q.append(word)

		return " & ".join(q)

	def create_post(self, title, text, author, tags=[], lang="markdown"):
		"""
			Creates a new post and returns the resulting Post object
		"""
		# Pre-render HTML
		html = self._render_text(text, lang=lang)

		return self._get_post("INSERT INTO blog(title, slug, text, html, lang, author_uid, tags) \
			VALUES(%s, %s, %s, %s, %s, %s, %s) RETURNING *", title, self._make_slug(title), text,
			html, lang, author.uid, list(tags))

	def _make_slug(self, s):
		# Remove any non-ASCII characters
		try:
			s = unicodedata.normalize("NFKD", s)
		except TypeError:
			pass

		# Remove excessive whitespace
		s = re.sub(r"[^\w]+", " ", s)

		slug = "-".join(s.split()).lower()

		while True:
			e = self.db.get("SELECT 1 FROM blog WHERE slug = %s", slug)
			if not e:
				break

			slug += "-"

		return slug

	def _render_text(self, text, lang="markdown"):
		if lang == "markdown":
			return markdown2.markdown(text, link_patterns=link_patterns,
				extras=[
					"code-friendly",
					"cuddled-lists",
					"fenced-code-blocks",
					"footnotes",
					"header-ids",
					"link-patterns",
					"tables",
				])

		elif lang == "textile":
			return textile.textile(text)

		return text

	def refresh(self):
		"""
			Needs to be called after a post has been changed
			and updates the search index.
		"""
		self.db.execute("REFRESH MATERIALIZED VIEW blog_search_index")

	@property
	def years(self):
		res = self.db.query("SELECT DISTINCT EXTRACT(year FROM published_at)::integer AS year \
			FROM blog WHERE published_at IS NOT NULL AND published_at <= NOW() \
			ORDER BY year DESC")

		for row in res:
			yield row.year

	def update_feeds(self):
		"""
			Updates all enabled feeds
		"""
		for feed in self.db.query("SELECT * FROM blog_feeds WHERE enabled IS TRUE"):
			try:
				f = feedparser.parse(feed.url)
			except Exception as e:
				raise e

			with self.db.transaction():
				# Update name
				self.db.execute("UPDATE blog_feeds SET name = %s \
					WHERE id = %s", f.feed.title, feed.id)

				# Walk through all entries
				for entry in f.entries:
					# Skip everything without the "blog.ipfire.org" tag
					try:
						tags = list((t.term for t in entry.tags))

						if not "blog.ipfire.org" in tags:
							continue
					except AttributeError:
						continue

					# Get link to the posting site
					link = entry.links[0].href

					# Check if the entry has already been imported
					res = self.db.get("SELECT id, (updated_at < %s) AS needs_update \
							FROM blog WHERE feed_id = %s AND foreign_id = %s",
							entry.updated, feed.id, entry.id)
					if res:
						# If the post needs to be updated, we do so
						if res.needs_update:
							self.db.execute("UPDATE blog SET title = %s, author = %s, \
								published_at = %s, updated_at = %s, html = %s, link = %s, \
								tags = %s WHERE id = %s", entry.title, entry.author,
								entry.published, entry.updated, entry.summary, link,
								feed.tags + tags, res.id)

						# Done here
						continue

					# Insert the new post
					self.db.execute("INSERT INTO blog(title, slug, author, \
						published_at, html, link, tags, updated_at, feed_id, foreign_id) \
						VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
						entry.title, self._make_slug(entry.title), entry.author,
						entry.published, entry.summary, link, feed.tags + tags,
						entry.updated, feed.id, entry.id)

				# Mark feed as updated
				self.db.execute("UPDATE blog_feeds SET last_updated_at = CURRENT_TIMESTAMP \
					WHERE id = %s" % feed.id)

		# Refresh the search index
		with self.db.transaction():
			self.refresh()


class Post(misc.Object):
	def init(self, id, data=None):
		self.id   = id
		self.data = data

	# Title

	@property
	def title(self):
		return self.data.title

	@property
	def slug(self):
		return self.data.slug

	@lazy_property
	def author(self):
		if self.data.author_uid:
			return self.backend.accounts.get_by_uid(self.data.author_uid)

		return self.data.author

	@property
	def created_at(self):
		return self.data.created_at

	@property
	def lang(self):
		return self.data.lang

	# Published?

	@property
	def published_at(self):
		return self.data.published_at

	def is_published(self):
		"""
			Returns True if the post is already published
		"""
		return self.published_at and self.published_at <= datetime.datetime.now()

	def publish(self, when=None):
		if self.is_published():
			return

		self.db.execute("UPDATE blog SET published_at = COALESCE(%s, CURRENT_TIMESTAMP) \
			WHERE id = %s", when, self.id)

		# Update search indices
		self.backend.blog.refresh()

	# Updated?

	@property
	def updated_at(self):
		return self.data.updated_at

	# Text

	@property
	def text(self):
		return self.data.text

	# HTML

	@lazy_property
	def html(self):
		"""
			Returns this post as rendered HTML
		"""
		return self.data.html or self.backend.blog._render_text(self.text, lang=self.lang)

	# Tags

	@property
	def tags(self):
		return self.data.tags

	# Link

	@property
	def link(self):
		return self.data.link

	@lazy_property
	def release(self):
		return self.backend.releases._get_release("SELECT * FROM releases \
			WHERE published IS NOT NULL AND published <= NOW() AND blog_id = %s", self.id)

	def is_editable(self, editor):
		# Authors can edit their own posts
		return self.author == editor

	def update(self, title, text, tags=[]):
		"""
			Called to update the content of this post
		"""
		# Update slug when post isn't published yet
		slug = self.backend.blog._make_slug(title) \
			if not self.is_published() and not self.title == title else self.slug

		# Render and cache HTML
		html = self.backend.blog._render_text(text, lang=self.lang)

		self.db.execute("UPDATE blog SET title = %s, slug = %s, text = %s, html = %s, \
			tags = %s, updated_at = CURRENT_TIMESTAMP WHERE id = %s",
			title, slug, text, html, list(tags), self.id)

		# Update cache
		self.data.update({
			"title" : title,
			"slug"  : slug,
			"text"  : text,
			"html"  : html,
			"tags"  : tags,
		})

		# Update search index if post is published
		if self.is_published():
			self.backend.blog.refresh()

	def delete(self):
		self.db.execute("DELETE FROM blog WHERE id = %s", self.id)

		# Update search indices
		self.backend.blog.refresh()
Commit	Line	Data
0a6875dc MT	1	#!/usr/bin/python
0a6875dc MT	2
541c952b	3	import datetime
c70a7c29	4	import feedparser
2de5ad8a	5	import markdown2
c70a7c29	6	import re
7e64f6a3	7	import textile
c70a7c29	8	import unicodedata
7e64f6a3	9
0a6875dc	10	from . import misc
a3a850a4	11	from .decorators import *
0a6875dc	12
2de5ad8a MT	13	# Used to automatically link some things
	14	link_patterns = (
	15	# Find bug reports
	16	(re.compile(r"(?:#(\d+))", re.I), r"https://bugzilla.ipfire.org/show_bug.cgi?id=\1"),
	17
	18	# Email Addresses
	19	(re.compile(r"([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+)"), r"mailto:\1"),
	20
	21	# CVE Numbers
5c6aa47c	22	(re.compile(r"(?:CVE)[\s\-](\d{4}\-\d+)"), r"https://cve.mitre.org/cgi-bin/cvename.cgi?name=\1"),
2de5ad8a MT	23	)
2de5ad8a MT	24
0a6875dc MT	25	class Blog(misc.Object):
	26	def _get_post(self, query, *args):
	27	res = self.db.get(query, *args)
	28
	29	if res:
	30	return Post(self.backend, res.id, data=res)
	31
	32	def _get_posts(self, query, *args):
	33	res = self.db.query(query, *args)
	34
	35	for row in res:
	36	yield Post(self.backend, row.id, data=row)
	37
487417ad MT	38	def get_by_id(self, id):
	39	return self._get_post("SELECT * FROM blog \
	40	WHERE id = %s", id)
	41
df157ede MT	42	def get_by_slug(self, slug, published=True):
	43	if published:
	44	return self._get_post("SELECT * FROM blog \
	45	WHERE slug = %s AND published_at <= NOW()", slug)
	46
0a6875dc	47	return self._get_post("SELECT * FROM blog \
df157ede MT	48	WHERE slug = %s", slug)
df157ede MT	49
0a6875dc MT	50	def get_newest(self, limit=None):
	51	return self._get_posts("SELECT * FROM blog \
	52	WHERE published_at IS NOT NULL \
	53	AND published_at <= NOW() \
	54	ORDER BY published_at DESC LIMIT %s", limit)
	55
	56	def get_by_tag(self, tag, limit=None):
	57	return self._get_posts("SELECT * FROM blog \
	58	WHERE published_at IS NOT NULL \
	59	AND published_at <= NOW() \
	60	AND %s = ANY(tags) \
4bde7f18	61	ORDER BY published_at DESC LIMIT %s", tag, limit)
0a6875dc	62
cdf85ee7	63	def get_by_author(self, author, limit=None):
0a6875dc	64	return self._get_posts("SELECT * FROM blog \
cdf85ee7	65	WHERE (author = %s OR author_uid = %s) \
0a6875dc MT	66	AND published_at IS NOT NULL \
0a6875dc MT	67	AND published_at <= NOW() \
cdf85ee7 MT	68	ORDER BY published_at DESC LIMIT %s",
cdf85ee7 MT	69	author.name, author.uid, limit)
0a6875dc	70
7e64f6a3 MT	71	def get_by_year(self, year):
	72	return self._get_posts("SELECT * FROM blog \
	73	WHERE EXTRACT(year FROM published_at) = %s \
	74	AND published_at IS NOT NULL \
	75	AND published_at <= NOW() \
	76	ORDER BY published_at DESC", year)
	77
0b342a05 MT	78	def get_drafts(self, author=None, limit=None):
	79	if author:
	80	return self._get_posts("SELECT * FROM blog \
	81	WHERE author_uid = %s \
	82	AND (published_at IS NULL OR published_at > NOW()) \
	83	ORDER BY COALESCE(updated_at, created_at) DESC LIMIT %s",
	84	author.uid, limit)
	85
	86	return self._get_posts("SELECT * FROM blog \
	87	WHERE (published_at IS NULL OR published_at > NOW()) \
	88	ORDER BY COALESCE(updated_at, created_at) DESC LIMIT %s", limit)
	89
0a6875dc	90	def search(self, query, limit=None):
a47ddc3d MT	91	query = self._parse_search_query(query)
a47ddc3d MT	92
0a6875dc MT	93	return self._get_posts("SELECT blog.* FROM blog \
	94	LEFT JOIN blog_search_index search_index ON blog.id = search_index.post_id \
	95	WHERE search_index.document @@ to_tsquery('english', %s) \
	96	ORDER BY ts_rank(search_index.document, to_tsquery('english', %s)) DESC \
	97	LIMIT %s", query, query, limit)
	98
a47ddc3d MT	99	def _parse_search_query(self, query):
	100	q = []
	101	for word in query.split():
	102	# Is this lexeme negated?
	103	negated = word.startswith("!")
	104
	105	# Remove any special characters
	106	word = re.sub(r"\W+", "", word, flags=re.UNICODE)
	107	if not word:
	108	continue
	109
	110	# Restore negation
	111	if negated:
	112	word = "!%s" % word
	113
	114	q.append(word)
	115
	116	return " & ".join(q)
	117
694c4f08	118	def create_post(self, title, text, author, tags=[], lang="markdown"):
541c952b MT	119	"""
	120	Creates a new post and returns the resulting Post object
	121	"""
694c4f08 MT	122	# Pre-render HTML
	123	html = self._render_text(text, lang=lang)
	124
	125	return self._get_post("INSERT INTO blog(title, slug, text, html, lang, author_uid, tags) \
	126	VALUES(%s, %s, %s, %s, %s, %s, %s) RETURNING *", title, self._make_slug(title), text,
	127	html, lang, author.uid, list(tags))
541c952b	128
c70a7c29 MT	129	def _make_slug(self, s):
	130	# Remove any non-ASCII characters
	131	try:
	132	s = unicodedata.normalize("NFKD", s)
	133	except TypeError:
	134	pass
	135
	136	# Remove excessive whitespace
	137	s = re.sub(r"[^\w]+", " ", s)
	138
	139	slug = "-".join(s.split()).lower()
	140
	141	while True:
	142	e = self.db.get("SELECT 1 FROM blog WHERE slug = %s", slug)
	143	if not e:
	144	break
	145
	146	slug += "-"
	147
	148	return slug
	149
2de5ad8a MT	150	def _render_text(self, text, lang="markdown"):
	151	if lang == "markdown":
	152	return markdown2.markdown(text, link_patterns=link_patterns,
e0ef6d39 MT	153	extras=[
	154	"code-friendly",
	155	"cuddled-lists",
	156	"fenced-code-blocks",
	157	"footnotes",
	158	"header-ids",
	159	"link-patterns",
	160	"tables",
	161	])
2de5ad8a MT	162
	163	elif lang == "textile":
	164	return textile.textile(text)
	165
	166	return text
	167
0a6875dc MT	168	def refresh(self):
	169	"""
	170	Needs to be called after a post has been changed
	171	and updates the search index.
	172	"""
	173	self.db.execute("REFRESH MATERIALIZED VIEW blog_search_index")
	174
7e64f6a3 MT	175	@property
	176	def years(self):
	177	res = self.db.query("SELECT DISTINCT EXTRACT(year FROM published_at)::integer AS year \
	178	FROM blog WHERE published_at IS NOT NULL AND published_at <= NOW() \
	179	ORDER BY year DESC")
	180
	181	for row in res:
	182	yield row.year
	183
c70a7c29 MT	184	def update_feeds(self):
	185	"""
	186	Updates all enabled feeds
	187	"""
	188	for feed in self.db.query("SELECT * FROM blog_feeds WHERE enabled IS TRUE"):
	189	try:
	190	f = feedparser.parse(feed.url)
	191	except Exception as e:
	192	raise e
	193
	194	with self.db.transaction():
	195	# Update name
	196	self.db.execute("UPDATE blog_feeds SET name = %s \
	197	WHERE id = %s", f.feed.title, feed.id)
	198
	199	# Walk through all entries
	200	for entry in f.entries:
	201	# Skip everything without the "blog.ipfire.org" tag
	202	try:
	203	tags = list((t.term for t in entry.tags))
	204
	205	if not "blog.ipfire.org" in tags:
	206	continue
	207	except AttributeError:
	208	continue
	209
	210	# Get link to the posting site
	211	link = entry.links[0].href
	212
	213	# Check if the entry has already been imported
	214	res = self.db.get("SELECT id, (updated_at < %s) AS needs_update \
	215	FROM blog WHERE feed_id = %s AND foreign_id = %s",
	216	entry.updated, feed.id, entry.id)
	217	if res:
	218	# If the post needs to be updated, we do so
	219	if res.needs_update:
	220	self.db.execute("UPDATE blog SET title = %s, author = %s, \
	221	published_at = %s, updated_at = %s, html = %s, link = %s, \
	222	tags = %s WHERE id = %s", entry.title, entry.author,
	223	entry.published, entry.updated, entry.summary, link,
	224	feed.tags + tags, res.id)
	225
	226	# Done here
	227	continue
	228
	229	# Insert the new post
	230	self.db.execute("INSERT INTO blog(title, slug, author, \
	231	published_at, html, link, tags, updated_at, feed_id, foreign_id) \
	232	VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
	233	entry.title, self._make_slug(entry.title), entry.author,
	234	entry.published, entry.summary, link, feed.tags + tags,
	235	entry.updated, feed.id, entry.id)
	236
20277bf5 MT	237	# Mark feed as updated
	238	self.db.execute("UPDATE blog_feeds SET last_updated_at = CURRENT_TIMESTAMP \
	239	WHERE id = %s" % feed.id)
	240
c70a7c29 MT	241	# Refresh the search index
	242	with self.db.transaction():
	243	self.refresh()
	244
0a6875dc MT	245
	246	class Post(misc.Object):
	247	def init(self, id, data=None):
	248	self.id = id
	249	self.data = data
	250
541c952b MT	251	# Title
541c952b MT	252
93725180 MT	253	@property
93725180 MT	254	def title(self):
0a6875dc MT	255	return self.data.title
	256
	257	@property
	258	def slug(self):
	259	return self.data.slug
	260
a3a850a4	261	@lazy_property
0a6875dc MT	262	def author(self):
	263	if self.data.author_uid:
	264	return self.backend.accounts.get_by_uid(self.data.author_uid)
	265
cdf85ee7 MT	266	return self.data.author
cdf85ee7 MT	267
0a6875dc MT	268	@property
	269	def created_at(self):
	270	return self.data.created_at
	271
2de5ad8a MT	272	@property
	273	def lang(self):
	274	return self.data.lang
	275
541c952b MT	276	# Published?
541c952b MT	277
0a6875dc MT	278	@property
	279	def published_at(self):
	280	return self.data.published_at
	281
541c952b MT	282	def is_published(self):
	283	"""
	284	Returns True if the post is already published
	285	"""
	286	return self.published_at and self.published_at <= datetime.datetime.now()
	287
9ea64cef	288	def publish(self, when=None):
541c952b MT	289	if self.is_published():
	290	return
	291
9ea64cef MT	292	self.db.execute("UPDATE blog SET published_at = COALESCE(%s, CURRENT_TIMESTAMP) \
9ea64cef MT	293	WHERE id = %s", when, self.id)
541c952b MT	294
	295	# Update search indices
	296	self.backend.blog.refresh()
	297
	298	# Updated?
	299
7e64f6a3	300	@property
541c952b MT	301	def updated_at(self):
	302	return self.data.updated_at
	303
541c952b MT	304	# Text
541c952b MT	305
93725180 MT	306	@property
93725180 MT	307	def text(self):
541c952b MT	308	return self.data.text
541c952b MT	309
541c952b	310	# HTML
7e64f6a3	311
a3a850a4	312	@lazy_property
0a6875dc MT	313	def html(self):
	314	"""
	315	Returns this post as rendered HTML
	316	"""
2de5ad8a	317	return self.data.html or self.backend.blog._render_text(self.text, lang=self.lang)
8ebc98d4	318
541c952b MT	319	# Tags
541c952b MT	320
93725180 MT	321	@property
93725180 MT	322	def tags(self):
8ebc98d4	323	return self.data.tags
1e76fec4	324
93725180	325	# Link
541c952b	326
1e76fec4 MT	327	@property
	328	def link(self):
	329	return self.data.link
984e4e7b	330
a3a850a4	331	@lazy_property
984e4e7b MT	332	def release(self):
	333	return self.backend.releases._get_release("SELECT * FROM releases \
	334	WHERE published IS NOT NULL AND published <= NOW() AND blog_id = %s", self.id)
e8a81a70 MT	335
	336	def is_editable(self, editor):
	337	# Authors can edit their own posts
	338	return self.author == editor
93725180 MT	339
	340	def update(self, title, text, tags=[]):
	341	"""
	342	Called to update the content of this post
	343	"""
	344	# Update slug when post isn't published yet
baa294fb MT	345	slug = self.backend.blog._make_slug(title) \
baa294fb MT	346	if not self.is_published() and not self.title == title else self.slug
93725180	347
694c4f08 MT	348	# Render and cache HTML
694c4f08 MT	349	html = self.backend.blog._render_text(text, lang=self.lang)
93725180	350
694c4f08	351	self.db.execute("UPDATE blog SET title = %s, slug = %s, text = %s, html = %s, \
93725180	352	tags = %s, updated_at = CURRENT_TIMESTAMP WHERE id = %s",
694c4f08	353	title, slug, text, html, list(tags), self.id)
93725180 MT	354
	355	# Update cache
	356	self.data.update({
	357	"title" : title,
	358	"slug" : slug,
	359	"text" : text,
d73edae7	360	"html" : html,
93725180 MT	361	"tags" : tags,
	362	})
	363
	364	# Update search index if post is published
	365	if self.is_published():
	366	self.backend.blog.refresh()
914238a5 MT	367
	368	def delete(self):
	369	self.db.execute("DELETE FROM blog WHERE id = %s", self.id)
	370
	371	# Update search indices
	372	self.backend.blog.refresh()