Move translation of news to an extra table

We won't release any news in non-English any more
but of course we want to preserve the old ones.

diff --git a/webapp/backend/news.py b/webapp/backend/news.py
index 51dfd4487fdcef6f8a06fcf3f463b1f844cfd714..dbdffde89b455e5f9510d288161a191226c91ffa 100644 (file)
--- a/webapp/backend/news.py
+++ b/webapp/backend/news.py
@@ -3,14 +3,35 @@
 from misc import Object
 
 class News(Object):
-       def get(self, uuid, lang="en"):
-               return self.db.get("SELECT * FROM news WHERE uuid = %s AND lang = %s \
-                       AND published IS NOT NULL AND published <= NOW()", uuid, lang)
+       def get(self, uuid, locale=None):
+               res = self.db.get("SELECT * FROM news WHERE uuid = %s \
+                       AND published IS NOT NULL AND published <= NOW()", uuid)
 
-       def get_by_slug(self, slug):
-               return self.db.get("SELECT * FROM news WHERE slug = %s \
+               if res and locale:
+                       res = self._translate_news_one(res, locale=locale)
+
+               return res
+
+       def get_by_id(self, id, locale=None):
+               res = self.db.get("SELECT * FROM news WHERE id = %s \
+                       AND published IS NOT NULL AND published <= NOW()", id)
+
+               if res and locale:
+                       res = self._translate_news_one(res, locale=locale)
+
+               return res
+
+       def get_by_slug(self, slug, locale=None):
+               res = self.db.get("SELECT * FROM news WHERE slug = %s \
                        AND published IS NOT NULL AND published <= NOW()", slug)
 
+               if res:
+                       return res
+
+               res = self.db.get("SELECT news_id FROM news_translations WHERE slug = %s", slug)
+               if res:
+                       return self.get_by_id(res.news_id, locale=locale)
+
        def get_latest(self, author=None, locale=None, limit=1, offset=0):
                query = "SELECT * FROM news WHERE published IS NOT NULL AND published <= NOW()"
                args = []
@@ -19,10 +40,6 @@ class News(Object):
                        query += " AND author_id = %s"
                        args.append(author)
 
-               if locale:
-                       query += " AND lang = %s"
-                       args.append(locale.code[:2])
-
                query += " ORDER BY published DESC"
 
                if limit:
@@ -33,20 +50,42 @@ class News(Object):
                                query += " OFFSET %s"
                                args.append(offset)
 
-               return self.db.query(query, *args)
+               news = self.db.query(query, *args)
+
+               if locale:
+                       news = self._translate_news(news, locale=locale)
+
+               return news
 
        def get_by_year(self, year, locale=None):
-               query = "SELECT * FROM news WHERE published IS NOT NULL AND published <= NOW() \
-                       AND EXTRACT(YEAR FROM published) = %s"
-               args  = [year,]
+               res = self.db.query("SELECT * FROM news \
+                       WHERE published IS NOT NULL AND published <= NOW() \
+                       AND EXTRACT(YEAR FROM published) = %s ORDER BY published DESC", year)
 
-               if locale:
-                       query += " AND lang = %s"
-                       args.append(locale.code[:2])
+               if res and locale:
+                       res = self._translate_news(res, locale=locale)
 
-               query += " ORDER BY published DESC"
+               return res
+
+       def _translate_news(self, news, locale):
+               ret = []
+
+               for n in news:
+                       n = self._translate_news_one(n, locale)
+                       ret.append(n)
+
+               return ret
+
+       def _translate_news_one(self, news, locale):
+               lang = locale.code[:2]
+
+               res = self.db.get("SELECT * FROM news_translations \
+                       WHERE news_id = %s AND lang = %s", news.id, lang)
+
+               if res:
+                       news.update(res)
 
-               return self.db.query(query, *args)
+               return news
 
        @property
        def years(self):

diff --git a/webapp/handlers.py b/webapp/handlers.py
index aef63b74fac5be089259cb1e58fe34ad3adf9227..8a2ca66f028fbb77697d04f369617ae0603a3509 100644 (file)
--- a/webapp/handlers.py
+++ b/webapp/handlers.py
@@ -74,7 +74,7 @@ class IndexHandler(BaseHandler):
        def get_latest_news(self, count=5):
                latest_news = []
 
-               for news in self.news.get_latest(limit=count, locale=self.locale):
+               for news in self.news.get_latest(limit=count):
                        latest_news.append(("news", news))
 
                for post in self.planet.get_entries(limit=count):

diff --git a/webapp/handlers_news.py b/webapp/handlers_news.py
index 18fd7f70f134a17dad8f3e48938127ae9bc47dc3..26a05cac75e535ac21a9e6fcf816fb801670497a 100644 (file)
--- a/webapp/handlers_news.py
+++ b/webapp/handlers_news.py
@@ -30,7 +30,7 @@ class NewsItemHandler(BaseHandler):
                This handler displays a whole page full of a single news item.
        """
        def get(self, slug):
-               news = self.news.get_by_slug(slug)
+               news = self.news.get_by_slug(slug, locale=self.locale)
                if not news:
                        raise tornado.web.HTTPError(404)