From: Michael Tremer Date: Wed, 16 Apr 2014 15:49:31 +0000 (+0200) Subject: planet: Add proper full-text search. X-Git-Url: http://git.ipfire.org/?p=people%2Fshoehn%2Fipfire.org.git;a=commitdiff_plain;h=fa7e1a0a16083c8a7b4a6bc4af7c841caa6b302a planet: Add proper full-text search. --- 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 @@ +
+
+
+
+ + + + +
+ + {{ _("All posts from") }} + + +
    + {% for y in years %} +
  • {{ y }}
    • + {% end %} +
+
+
+
+
+
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 @@


-
-
-
-
- - - - -
- - {{ _("All posts from") }} - - -
    - {% for y in years %} -
  • {{ y }}
    • - {% end %} -
-
-
-
-
-
+ {% 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")