From fa7e1a0a16083c8a7b4a6bc4af7c841caa6b302a Mon Sep 17 00:00:00 2001
From: Michael Tremer
Date: Wed, 16 Apr 2014 17:49:31 +0200
Subject: [PATCH] planet: Add proper full-text search.
---
templates/modules/planet/search-box.html | 26 +++++++++++++++
templates/planet/index.html | 27 +---------------
templates/planet/search.html | 4 +++
webapp/__init__.py | 5 +--
webapp/backend/planet.py | 41 +++++++-----------------
webapp/handlers_planet.py | 19 +----------
webapp/ui_modules.py | 12 +++++++
7 files changed, 56 insertions(+), 78 deletions(-)
create mode 100644 templates/modules/planet/search-box.html
diff --git a/templates/modules/planet/search-box.html b/templates/modules/planet/search-box.html
new file mode 100644
index 0000000..6d4f36b
--- /dev/null
+++ b/templates/modules/planet/search-box.html
@@ -0,0 +1,26 @@
+
diff --git a/templates/planet/index.html b/templates/planet/index.html
index 15cebd6..6e585e5 100644
--- a/templates/planet/index.html
+++ b/templates/planet/index.html
@@ -10,32 +10,7 @@
-
+ {% module PlanetSearchBox() %}
diff --git a/templates/planet/search.html b/templates/planet/search.html
index a3e79ab..e221109 100644
--- a/templates/planet/search.html
+++ b/templates/planet/search.html
@@ -7,6 +7,10 @@
{% end block %}
{% block body %}
+ {% module PlanetSearchBox(query=query) %}
+
+
+
{% if entries %}
{% for entry in entries %}
{% module PlanetEntry(entry) %}
diff --git a/webapp/__init__.py b/webapp/__init__.py
index 36bd58e..d890424 100644
--- a/webapp/__init__.py
+++ b/webapp/__init__.py
@@ -49,6 +49,7 @@ class Application(tornado.web.Application):
"NewsTable" : NewsTableModule,
"NewsYearNavigation" : NewsYearNavigationModule,
"PlanetEntry" : PlanetEntryModule,
+ "PlanetSearchBox" : PlanetSearchBoxModule,
"ReleaseItem" : ReleaseItemModule,
"SidebarBanner" : SidebarBannerModule,
"SidebarRelease" : SidebarReleaseModule,
@@ -138,9 +139,6 @@ class Application(tornado.web.Application):
(r"/search", PlanetSearchHandler),
(r"/year/(\d+)", PlanetYearHandler),
- # API
- (r"/api/planet/search/autocomplete", PlanetAPISearchAutocomplete),
-
# RSS
(r"/rss", RSSPlanetAllHandler),
(r"/user/([a-z0-9_-]+)/rss", RSSPlanetUserHandler),
@@ -244,7 +242,6 @@ class Application(tornado.web.Application):
(r"/downloads", AdminDownloadsHandler),
(r"/downloads/mirrors", AdminDownloadsMirrorsHandler),
# API
- (r"/api/planet/search/autocomplete", PlanetAPISearchAutocomplete),
(r"/api/planet/render", AdminApiPlanetRenderMarkupHandler)
] + static_handlers)
diff --git a/webapp/backend/planet.py b/webapp/backend/planet.py
index 0c75656..47036a1 100644
--- a/webapp/backend/planet.py
+++ b/webapp/backend/planet.py
@@ -268,33 +268,14 @@ class Planet(Object):
return id
def search(self, what):
- # Split tags.
- tags = what.split()
-
- query = "SELECT planet.* FROM planet INNER JOIN ( \
- SELECT post_id FROM planet_tags \
- INNER JOIN planet ON planet_tags.post_id = planet.id \
- WHERE %s GROUP BY post_id HAVING COUNT(post_id) = %%s \
- ) pt ON planet.id = pt.post_id ORDER BY published DESC"
-
- args = (tags, len(tags))
-
- clauses, args = [], tags
- for tag in tags:
- clauses.append("planet_tags.tag = %s")
- args.append(len(tags))
-
- entries = self.db.query(query % " OR ".join(clauses), *args)
- return [PlanetEntry(self.backend, e) for e in entries]
-
- def search_autocomplete(self, what):
- tags = what.split()
- last_tag = tags.pop()
-
- res = self.db.query("SELECT tag, COUNT(tag) AS count FROM planet_tags \
- WHERE tag LIKE %s GROUP BY tag ORDER BY count DESC", "%s%%" % last_tag)
-
- if tags:
- return ["%s %s" % (" ".join(tags), row.tag) for row in res]
-
- return [row.tag for row in res]
+ res = self.db.query("WITH \
+ q AS (SELECT plainto_tsquery(%s, %s) AS query), \
+ ranked AS (SELECT id, query, ts_rank_cd(to_tsvector(%s, markdown), query) AS rank \
+ FROM planet, q WHERE markdown @@ query ORDER BY rank DESC) \
+ SELECT *, ts_headline(markup, ranked.query, 'MinWords=100, MaxWords=110') AS markup FROM planet \
+ JOIN ranked ON planet.id = ranked.id \
+ WHERE status = %s AND published IS NOT NULL AND published <= NOW() \
+ ORDER BY ranked DESC LIMIT 10",
+ "english", what, "english", "published")
+
+ return [PlanetEntry(self.backend, e) for e in res]
diff --git a/webapp/handlers_planet.py b/webapp/handlers_planet.py
index 4a6fe5c..18f2d42 100644
--- a/webapp/handlers_planet.py
+++ b/webapp/handlers_planet.py
@@ -16,10 +16,8 @@ class PlanetMainHandler(PlanetBaseHandler):
limit = int(self.get_argument("limit", 4))
entries = self.planet.get_entries(offset=offset, limit=limit)
- years = self.planet.get_years()
- self.render("planet/index.html", entries=entries, years=years,
- offset=offset + limit, limit=limit)
+ self.render("planet/index.html", entries=entries, offset=offset + limit, limit=limit)
class PlanetUserHandler(PlanetBaseHandler):
@@ -76,18 +74,3 @@ class PlanetYearHandler(PlanetBaseHandler):
months.sort(reverse=True)
self.render("planet/year.html", months=months, year=year)
-
-
-class PlanetAPISearchAutocomplete(PlanetBaseHandler):
- def get(self):
- query = self.get_argument("q")
- if not query:
- raise tornado.web.HTTPError(400)
-
- results = self.planet.search_autocomplete(query)
-
- res = {
- "query" : query,
- "results" : results,
- }
- self.write(res)
diff --git a/webapp/ui_modules.py b/webapp/ui_modules.py
index 3cf68d8..da69711 100644
--- a/webapp/ui_modules.py
+++ b/webapp/ui_modules.py
@@ -47,6 +47,10 @@ class UIModule(tornado.web.UIModule):
def news(self):
return self.handler.news
+ @property
+ def planet(self):
+ return self.handler.planet
+
class AdvertisementModule(UIModule):
def render(self, where):
@@ -148,6 +152,14 @@ class NewsYearNavigationModule(UIModule):
active=active, years=self.news.years)
+class PlanetSearchBoxModule(UIModule):
+ def render(self, query=None):
+ years = self.planet.get_years()
+
+ return self.render_string("modules/planet/search-box.html",
+ query=query, years=years)
+
+
class SidebarItemModule(UIModule):
def render(self):
return self.render_string("modules/sidebar-item.html")
--
2.39.2